I have a dataframe(df1). each cell means row, column indexes:
| c1 | c2 | c3 | |
|---|---|---|---|
| 0 | 1,1 | 2,1 | 3,2 |
| 1 | 1,1 | 2,2 | 3,1 |
How to perform search by row, column within the following dataframe(df2)
| c1 | c2 | c3 | c4 | |
|---|---|---|---|---|
| 0 | 1 | 2 | 10 | x |
| 1 | 3 | 4 | 20 | y |
| 2 | 5 | 6 | 30 | x |
for example 1,1 of df1 is 1, 2,1 is 3, 3,2 is 6.
Final output for 3,2 should be: 180, x: (6(which is df2 3rd row, 2nd column)*30(3rd row, 3rd column),x = 180, x)
>Solution :
First set index and columns values to range starting by 1, so possible select by splitted values converted to integers by DataFrame.loc elementwise by DataFrame.applymap:
df22 = df2.rename(index = lambda x: x + 1)
.set_axis(np.arange(1, len(df2.columns) + 1), inplace=False, axis=1)
f = lambda x: df22.loc[tuple(map(int, x.split(',')))]
df = df1.applymap(f)
print (df)
c1 c2 c3
0 1 3 6
1 1 4 5