CODEWITHSUNDEEP
pythonpandas
learn_all
learn_all

Reputation: 25

How to get the row and column number based on lookup dataset in Python Pandas?

I have a question on filing value in one Pandas data frame based on the row and column number from another data frame, from example Df1:

ID Cell
A 97
A 98
A 99
B 10
B 11
B 12

Df2:

Row Col1 Col2 Col3
1 97 98 99
2 10 11 12

So using Df2, I want to update df1 as follows:- Df1:

ID Cell Row Col
A 97 1 Col1
A 98 1 Col2
A 99 1 Col3
B 10 2 Col1
B 11 2 Col2
B 12 2 Col

What would be the best way to do this in Python to make it generic as the df1 and df2 are having many rows?

Upvotes: 1

Views: 189

Answers (1)

BENY
BENY

Reputation: 323366

Let us try melt then merge

out = df1.merge(df2.melt('Row'),left_on='Cell',right_on='value')
Out[159]: 
  ID  Cell  Row variable  value
0  A    97    1     Col1     97
1  A    98    1     Col2     98
2  A    99    1     Col3     99
3  B    10    2     Col1     10
4  B    11    2     Col2     11
5  B    12    2     Col3     12

Upvotes: 2

Related Questions