Create 2D array from Pandas dataframe

Question

Probably a very simple question, but I couldn't come up with a solution. I have a data frame with 9 columns and ~100000 rows. The data was extracted from an image, such that two columns ('row' and 'col') are referring to the pixel position of the data. How can I create a numpy array A such that the row and column points to another data entry in another column, e.g. 'grumpiness'?

A[row, col]
#  0.1232

I want to avoid a for loop or something similar.

Solution 1

You could do something like this -

# Extract row and column information
rowIDs = df['row']
colIDs = df['col']

# Setup image array and set values into it from "grumpiness" column
A = np.zeros((rowIDs.max()+1,colIDs.max()+1))
A[rowIDs,colIDs] = df['grumpiness']

Sample run -

>>> df
   row  col  grumpiness
0    5    0    0.846412
1    0    1    0.703981
2    3    1    0.212358
3    0    2    0.101585
4    5    1    0.424694
5    5    2    0.473286
>>> A
array([[ 0.        ,  0.70398113,  0.10158488],
       [ 0.        ,  0.        ,  0.        ],
       [ 0.        ,  0.        ,  0.        ],
       [ 0.        ,  0.21235838,  0.        ],
       [ 0.        ,  0.        ,  0.        ],
       [ 0.84641194,  0.42469369,  0.47328598]])

Solution 2

One very quick and straightforward way to do this is to use a pivot_table:

>>> df
   row  col  grumpiness
0    5    0    0.846412
1    0    1    0.703981
2    3    1    0.212358
3    0    2    0.101585
4    5    1    0.424694
5    5    2    0.473286

>>> df.pivot_table('grumpiness', 'row', 'col', fill_value=0)
col         0         1         2
row                              
0    0.000000  0.703981  0.101585
3    0.000000  0.212358  0.000000
5    0.846412  0.424694  0.473286

Note that if any full rows/cols are missing, it will leave them out, and if any row/col pair is repeated, it will average the results. That said, this will generally be much faster for larger datasets than an indexing-based approach.