Lookup values in other DataFrame based on two columns-CodePudding

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!

CodePudding user response：

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

CodePudding user response：

Given the DataFrames,

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]})

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]})

we can set df1's index to Dates and simply lookup for corresponding values by index and column:

df1 = df1.set_index('Dates')
df1 = df1.replace('NaN',np.nan)
df['X2'] = df2.apply(lambda x: df1.loc[x['Dates'], x['ID']], axis=1)

Another option is to unstack df1 and create a Series. Then set_index of df2 to Dates and ID as a MultiIndex and using that MultiIndex filter the relevant data from the unstacked Series:

df1_data = df1.unstack().swaplevel(1,0)
df['X2'] = df1_data[df2.set_index(['Dates','ID']).index].to_numpy()

Output:

   A  B  C   X2
0  a  1  2  4.2
1  b  3  4  3.5
2  c  5  6  0.1
3  a  1  2  2.5
4  b  3  4  8.2
5  c  5  6  4.2

CodePudding user response：

you could use melt , without playing with the index :

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