I have three lists (really columns in a pandas dataframe) one with data of interest, one with x array coordinates, and one with y array coordinates. All lists are the same length and their order in the list associated with the coordinates (so L1: "Apple" coincides with L2:"1", and L3:"A"). I would like to make an array with the dimensions provided by the two coordinate lists with data from the data list. What is the best way to do this?
The expected output would be in the form of a numpy array or something like:
array = [[0,0,0,3,0,0,2,3][0,0,0,0,0,0,0,3]] #databased on below
Where in this example the array has the dimensions of y = 2 from y.unique() and x = 8 from x.unique().
The following is example input data for what I am talking about:
| array_x | array_y | Data |
|---|---|---|
| 1 | a | 0 |
| 2 | a | 0 |
| 3 | a | 0 |
| 4 | a | 3 |
| 5 | a | 0 |
| 6 | a | 0 |
| 7 | a | 2 |
| 8 | a | 3 |
| 1 | b | 0 |
| 2 | b | 0 |
| 3 | b | 0 |
| 4 | b | 0 |
| 5 | b | 0 |
| 6 | b | 0 |
| 7 | b | 0 |
| 8 | b | 3 |
You may be looking for pivot:
out = df.pivot(values=['Data'], columns=['array_y'], index=['array_x']).to_numpy()
Output:
array([[0, 0],
[0, 0],
[0, 0],
[3, 0],
[0, 0],
[0, 0],
[2, 0],
[3, 3]], dtype=int64)
Supposing you have a dataframe like that:
import pandas as pd
import numpy as np
myDataframe = pd.DataFrame([[1,2],[3,4],[5,6]], columns=['x','y'])
Then you can select the columns you want and creat an array from it
my_array = np.array(myDataframe[['x','y']])
>>> my_array
array([[1, 2],
[3, 4],
[5, 6]], dtype=int64)
You could do a zip (note: I'm shorthand-ing some of your example data):
data_x = [1, 2, 3, 4, 5, 6, 7, 8] * 2
data_y = ['a'] * 8 + ['b'] * 8
data_vals = [0,0,0,3,0,0,2,3,0,0,0,0,0,0,0,3]
coll = dict()
for (x, y, val) in zip(data_x, data_y, data_vals):
if coll.get(y) is None:
coll[y] = []
if x > len(coll[y]):
coll[y].extend([0] * (x - len(coll[y])))
coll[y][x - 1] = val
result = []
for k in sorted(coll):
result.append(coll[k])
print coll
print result
Output:
{'a': [0, 0, 0, 3, 0, 0, 2, 3], 'b': [0, 0, 0, 0, 0, 0, 0, 3]}
[[0, 0, 0, 3, 0, 0, 2, 3], [0, 0, 0, 0, 0, 0, 0, 3]]