Convert a Pandas DataFrame to a multidimensional ndarray

Question

I have a DataFrame with columns for the x, y, z coordinates and the value at this position and I want to convert this to a 3-dimensional ndarray.

To make things more complicated, not all values exist in the DataFrame (these can just be replaced by NaN in the ndarray).

Just a simple example:

df = pd.DataFrame({'x': [1, 2, 1, 3, 1, 2, 3, 1, 2], 
                   'y': [1, 1, 2, 2, 1, 1, 1, 2, 2],
                   'z': [1, 1, 1, 1, 2, 2, 2, 2, 2],
                   'value': [1, 2, 3, 4, 5, 6, 7, 8, 9]})

Should result in the ndarray:

array([[[  1.,   2.,  nan],
        [  3.,  nan,   4.]],

       [[  5.,   6.,   7.],
        [  8.,   9.,  nan]]])

For two dimensions, this is easy:

array = df.pivot_table(index="y", columns="x", values="value").as_matrix()

However, this method can not be applied to three or more dimensions.

Could you give me some suggestions?

Bonus points if this also works for more than three dimensions, handles multiple defined values (by taking the average) and ensures that all x, y, z coordinates are consecutive (by inserting row/columns of NaN when a coordinate is missing).

EDIT: Some more explanations:

I read data from a CSV file which has the columns for x, y, z coordinates, optionally the frequency and the measurement value at this point and frequency. Then I round the coordinates to a specified precision (e.g. 0.1m) and want to get an ndarray which contains the averaged measurement values at each (rounded) coordinates. The indizes of the values do not need to coincide with the location. However they need to be in the correct order.

EDIT: I just ran a quick performance test:

The solution of jakevdp takes 1.598s, Divikars solution takes 7.405s, JohnE's solution takes 7.867s and Wens solution takes 6.286s to complete.

Answer 1

Here's one NumPy approach -

def dataframe_to_array_averaged(df):
    arr = df[['z','y','x']].values
    arr -= arr.min(0)
    out_shp = arr.max(0)+1

    L = np.prod(out_shp)

    val = df['value'].values
    ids = np.ravel_multi_index(arr.T, out_shp)

    avgs = np.bincount(ids, val, minlength=L)/np.bincount(ids, minlength=L)
    return avgs.reshape(out_shp)

Note that that this shows a warning because for places with no x,y,z triplets would have zero counts and hence the average values would be 0/0 = NaN, but since that's the expected output for those places, you can ignore the warning there. To avoid this warning, we can employ indexing, as discussed in the second method (Alternative method).

Sample run -

In [106]: df
Out[106]: 
   value  x  y  z
0      1  1  1  1  # <=== this is repeated
1      2  2  1  1
2      3  1  2  1
3      4  3  2  1
4      5  1  1  2
5      6  2  1  2
6      7  3  1  2
7      8  1  2  2
8      9  2  2  2
9      4  1  1  1  # <=== this is repeated

In [107]: dataframe_to_array_averaged(df)
__main__:42: RuntimeWarning: invalid value encountered in divide
Out[107]: 
array([[[ 2.5,  2. ,  nan],
        [ 3. ,  nan,  4. ]],

       [[ 5. ,  6. ,  7. ],
        [ 8. ,  9. ,  nan]]])

Alternative method

To avoid warning, an alternative way would be like so -

out = np.full(out_shp,  np.nan)
sums = np.bincount(ids, val)
unq_ids, count = np.unique(ids, return_counts=1)
out.flat[:unq_ids[-1]] = sums
out.flat[unq_ids] /= count

Answer 2

You can use a groupby followed by the approach from Transform Pandas DataFrame with n-level hierarchical index into n-D Numpy array:

grouped = df.groupby(['z', 'y', 'x'])['value'].mean()

# create an empty array of NaN of the right dimensions
shape = tuple(map(len, grouped.index.levels))
arr = np.full(shape, np.nan)

# fill it using Numpy's advanced indexing
arr[grouped.index.labels] = grouped.values.flat

print(arr)
# [[[  1.   2.  nan]
#   [  3.  nan   4.]]
# 
#  [[  5.   6.   7.]
#   [  8.   9.  nan]]]

Answer 3

Another solution is to use the xarray package:

import pandas as pd
import xarray as xr
df = pd.DataFrame({'x': [1, 2, 1, 3, 1, 2, 3, 1, 2], 
                   'y': [1, 1, 2, 2, 1, 1, 1, 2, 2],
                   'z': [1, 1, 1, 1, 2, 2, 2, 2, 2],
                   'value': [1, 2, 3, 4, 5, 6, 7, 8, 9]})
df = pd.pivot_table(df, values='value', index=['x', 'y', 'z'])
xrTensor = xr.DataArray(df).unstack("dim_0")
array = xrTensor.values[0].T
print(array)

Output:

array([[[ 1.,  2., nan],
        [ 3., nan,  4.]],

       [[ 5.,  6.,  7.],
        [ 8.,  9., nan]]])

Note that the xrTensor object is very handy since xarray's DataArrays contain the labels so you may just go on with that object rather pulling out the ndarray:

print(xrTensor)

Output:

<xarray.DataArray (dim_1: 1, x: 3, y: 2, z: 2)>
array([[[[ 1.,  5.],
         [ 3.,  8.]],

        [[ 2.,  6.],
         [nan,  9.]],

        [[nan,  7.],
         [ 4., nan]]]])
Coordinates:
  * dim_1    (dim_1) object 'value'
  * x        (x) int64 1 2 3
  * y        (y) int64 1 2
  * z        (z) int64 1 2