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.
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]])
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.
