Matching the column values to the row values and write in the new column-CodePudding

My first dataframe looks like this:

df1:

ID      Name    Result
ABC1    John     A
ABC1    Mac      B
ABC1    Kat      C
DEF1    John     D
DEF1    Mac      E
DEF1    Kat      F
XYZ1    John     G
XYZ1    Mac      H
XYZ1    Kat      I

df2:

ID      John    Mac    Kat
ABC1    22      33     11
DEF1    10      12     2
XYZ2    11      12     36

The result df3: (It should match the ID and name of df1 with the ID and names (John, Mac and Kat) with the column of df2 and then give their respective values.

ID      Name    Result  Value
ABC1    John    A       22
ABC1    Mac     B       33
ABC1    Kat     C       11
DEF1    John    D       10
DEF1    Mac     E       12
DEF1    Kat     F        2
XYZ1    John    G        0
XYZ1    Mac     H        0
XYZ1    Kat     I        0

There are multiple questions:

Use column headers to find matching value and get value in matching column for the row

Replace the Row Values with Matching row values with column names

But it works only with one dataframe so I thought to merge the dataframes but doens't seem to work.

CodePudding user response：

Use DataFrame.join with DataFrame.stack:

df = (df1.join(df2.set_index('ID').stack().rename('Value'), on=['ID','Name'])
         .fillna({'Value':0}))

Or DataFrame.merge with DataFrame.melt:

df = (df1.merge(df2.melt('ID', var_name='Name', value_name='Value'), 
                on=['ID','Name'], how='left')
         .fillna({'Value':0}))

print (df)
     ID  Name Result  Value
0  ABC1  John      A   22.0
1  ABC1   Mac      B   33.0
2  ABC1   Kat      C   11.0
3  DEF1  John      D   10.0
4  DEF1   Mac      E   12.0
5  DEF1   Kat      F    2.0
6  XYZ1  John      G    0.0
7  XYZ1   Mac      H    0.0
8  XYZ1   Kat      I    0.0