Merge two dataframes by respecting the values in specific columns-CodePudding

I have two such data frames:

df1:

                     DayYear_Count      Time
0                                           
2018-03-26 00:00:00             84  00:00:00
2018-03-26 01:00:00             84  01:00:00
2018-03-26 02:00:00             84  02:00:00
2018-03-26 03:00:00             84  03:00:00
2018-03-26 04:00:00             84  04:00:00

df2:

       Time       Temp  DayYear_Count
0      00:00:00   6.065167             84
1      01:00:00   5.692167             84
2      02:00:00   5.151917             84
3      03:00:00   4.636500             84
4      04:00:00   4.277417             84

I want to merge the two data frames into a single data frame in which the 'Time' and 'DayYear_Count' columns coincide. So I would only have the values in the 'Temp' column at those positions. Something like:

                   DayYear_Count      Time     Temp  
0                                           
2018-03-26 00:00:00             84  00:00:00   6.065
2018-03-26 01:00:00             84  01:00:00   3.055
2018-03-26 02:00:00             84  02:00:00   "Nan"

I have entered random values to give the idea.

CodePudding user response：

Use left join with DataFrame.merge and for avoid MultiIndex convert it to column 0:

df1['Time'] = pd.to_datetime(df1['Time']).dt.time
df2['Time'] = pd.to_datetime(df2['Time']).dt.time

df1['DayYear_Count'] = df1['DayYear_Count'].astype(int)
df2['DayYear_Count'] = df2['DayYear_Count'].astype(int)

df = df1.reset_index().merge(df2, on=['DayYear_Count','Time'], how='left').set_index(0)