Lookup values in other DataFrame based on two columns

January 14, 2022

I try to create a new column X2 in DataFrame df2 by mapping two dimensions (columns) Dates & ID with df1. So it is kind of a lookup based on two conditions. So far, I only know how to map based on one dimension.

df1:
            01K 02K 03K 04K
Dates               
2021-01-01  4.2 3.5 4.2 NaN
2021-01-02  2.3 0.1 5.2 2.6
2021-01-03  0.3 NaN 2.5 8.2
2021-01-04  0.4 NaN 3.0 4.2

df2:
            ID  X1
Dates       
2021-01-01  01K 3.5
2021-01-01  02K 1.1
2021-01-02  02K 2.1
2021-01-03  03K 4.2
2021-01-03  04K 3.1
2021-01-04  04K 2.7

df2_new:
            ID  X1  X2
Dates           
2021-01-01  01K 3.5 4.2
2021-01-01  02K 1.1 3.5
2021-01-02  02K 2.1 0.1
2021-01-03  03K 4.2 2.5
2021-01-03  04K 3.1 8.2
2021-01-04  04K 2.7 4.2

For reproducibility:

import pandas as pd
import numpy as np

df1 = pd.DataFrame({
    'Dates':['2021-01-01', '2021-01-02', '2021-01-03', '2021-01-04'],
    '01K':[4.2, 2.3, 0.3, 0.4], 
    '02K':[3.5, 0.1, 'NaN', 'NaN'], 
    '03K':[4.2, 5.2, 2.5, 3.0], 
    '04K':['NaN', 2.6, 8.2, 4.2]})
df1 = df1.replace('NaN',np.nan)
df1 = df1.set_index('Dates')

df2 = pd.DataFrame({
    'Dates':['2021-01-01', '2021-01-01', '2021-01-02', '2021-01-03', '2021-01-03', '2021-01-04'],
    'ID':['01K', '02K', '02K', '03K', '04K', '04K'], 
    'X1':[3.5, 1.1, 2.1, 4.2, 3.1, 2.7]})
df2 = df2.set_index('Dates')

Thanks a lot!

>Solution :

You can melt df1 to reshape it to a long format, and left merge the output to df2 on "Date" and "ID":

df1_melt = df1.reset_index().melt(id_vars='Dates', var_name='ID', value_name='X2')
df2.merge(df1_melt, on=['Dates', 'ID'], how='left').set_index('Dates')

output:

             ID   X1   X2
Dates                    
2021-01-01  01K  3.5  4.2
2021-01-01  02K  1.1  3.5
2021-01-02  02K  2.1  0.1
2021-01-03  03K  4.2  2.5
2021-01-03  04K  3.1  8.2
2021-01-04  04K  2.7  4.2