My data frame df1:
ID Date
0 90 02/01/2021
1 101 01/01/2021
2 30 12/01/2021
My data frame df2:
ID City 01/01/2021 02/01/2021 12/01/2021
0 90 A 20 14 22
1 101 B 15 10 5
2 30 C 12 9 13
I need to create a column in df1 'New'. It should contain data from df2 with respect to 'ID' and 'Date' of df1. I am finding difficult in merging data. How could I do it?
CodePudding user response:
Use DataFrame.melt
with DataFrame.merge
:
df22 = df2.drop('City', 1).melt(['ID'], var_name='Date', value_name='Val')
df = df1.merge(df22, how='left')
print (df)
ID Date Val
0 90 02/01/2021 14
1 101 01/01/2021 15
2 30 12/01/2021 13
CodePudding user response:
You can melt
and merge
:
df1.merge(df2.melt(id_vars=['ID', 'City'], var_name='Date'), on=['ID', 'Date'])
output:
ID Date City value
0 90 02/01/2021 A 14
1 101 01/01/2021 B 15
2 30 12/01/2021 C 13
Alternative:
df1.merge(df2.melt(id_vars='ID',
value_vars=df2.filter(regex='/'),
var_name='Date'),
on=['ID', 'Date'])
output:
ID Date value
0 90 02/01/2021 14
1 101 01/01/2021 15
2 30 12/01/2021 13