[Python] Assign multi-index variable values based on the number of elements in a dataframe...

I have a large csv dataset the looks like the following:

id,x,y,z
34295,695.117,74.0177,70.6486
20915,800.784,98.5225,19.3014
30369,870.428,98.742,23.9953
48151,547.681,53.055,174.176
34026,1231.02,73.7678,203.404
34797,782.725,73.9831,218.592
15598,983.502,82.9373,314.081
34076,614.738,86.3301,171.316
20328,889.016,98.9201,13.3068
...


If I consider each of these lines an element, I would like to have a data structure where I can easily divide space into x,y,z ranges (3-d blocks of space) and determine how many elements are within a given block.

For instance if I divided into cubes of 100 x 100 x 100:

counts[900][100][100] = 3


because id's 20915, 30369, and 20328 from the excerpt of the csv above are all within the range x = 800-900, y = 0-100, and z = 0-100.

The brute force way to create something like this is to create a multi-level dictionary as follows:

import numpy
import pandas

df = pandas.read_csv("test.csv")

xs = numpy.linspace(0, 1300, 14, endpoint=True)
ys = numpy.linspace(0, 1000, 11, endpoint=True)
zs = numpy.linspace(0, 1000, 11, endpoint=True)

c = {}
for x_index, x in enumerate(xs[:-1]):
c[xs[x_index + 1]] = {}
for y_index, y in enumerate(ys[:-1]):
c[xs[x_index + 1]][ys[y_index + 1]] = {}
for z_index, z in enumerate(zs[:-1]):
c[xs[x_index + 1]][ys[y_index + 1]][zs[z_index + 1]] = df[(df["x"] > xs[x_index]) & (df["x"] <= xs[x_index + 1]) & (df["y"] > ys[y_index]) & (df["y"] <= ys[y_index + 1]) & (df["z"] > zs[z_index]) & (df["z"] <= zs[z_index + 1])]["id"].count()
if (c[xs[x_index + 1]][ys[y_index + 1]][zs[z_index + 1]] > 0):
print("c[" + str(xs[x_index + 1]) + "][" + str(ys[y_index + 1]) + "][" + str(zs[z_index + 1]) + "] = " + str(c[xs[x_index + 1]][ys[y_index + 1]][zs[z_index + 1]]))


This gives the expected output of:

c[600.0][100.0][200.0] = 1
c[700.0][100.0][100.0] = 1
c[700.0][100.0][200.0] = 1
c[800.0][100.0][300.0] = 1
c[900.0][100.0][100.0] = 3
c[1000.0][100.0][400.0] = 1
c[1300.0][100.0][300.0] = 1


but since the actual production csv file is very large, it is quite slow. Any suggestions for how to make it fast and a little less clunky? Thank you.

Continue reading...