Create a dataframe column based on values that are columns in another dataframe-CodePudding

I have two dataframes, the first one looks like:

Age	200	300	400
1	34	32	50
2	42	73	20

The second dataframe looks like

ID	Age	Score
10	2	200
23	1	300

My goal is to create another column in the second dataframe which fetches the value from the first dataframe by the corresponding values of both the column Age and Score. The Score's are the columns in the first dataframe.

The resulting dataframe:

ID	Age	Score	Count
10	2	200	42
23	1	300	32

CodePudding user response：

Try with melt and merge

tomerge = df1.melt('Age',var_name = 'Score',value_name='Count')

tomerge['Score'] = tomerge['Score'].astype(int)
out = df2.merge(tomerge)
out
Out[988]: 
   ID  Age  Score  Count
0  10    2    200     42
1  23    1    300     32

CodePudding user response：

You can create a pd.MultiIndex.from_arrays from df2 and map it onto a series created with 'Age' index followed by a stack from df1.

    df2.Count = pd.Series(
        pd.MultiIndex.from_arrays([df2.Age, df2.Score]).map(df1.set_index('Age').stack())
    )

Intermediate outputs:

    df1.set_index('Age').stack()
    Age     
    1    200    34
         300    32
         400    50
    2    200    42
         300    73
         400    20
    dtype: int64
    
    pd.MultiIndex.from_arrays([df2.Age, df2.Score])
    
    MultiIndex([(2, 200),
                (1, 300)],
               names=['Age', 'Score'])

print(df2):

       ID  Age  Score  Count
    0  10    2    200     42
    1  23    1    300     32