Mapping Values And Merging Dataframes Based On Multiple Column Values

I have the following dataframes: df1: dataframe with patient critical notes AREA DATE_TIME CRITICAL ISSUE NOTES 0013 11/6/

Solution 1:

If you put the columns in the same order for both left/right_on (area/zip then date time/time noted) it should work. I also changed the merge to an inner, so you just get records with the same zip/area and date time/time noted.

new_df = pd.merge(df1, df2,  how='inner', left_on = ['AREA','DATE_TIME'], right_on = ['ZIP','TIME_NOTED'])

Another potential solution would be creating an "ID" column and merging on that.

df1['ID'] = df1['AREA'].astype(str) + '_' + df1['DATE_TIME'].astype(str)
df2['ID'] = df2['ZIP'].astype(str) + '_' + df2['TIME_NOTED'].astype(str)

Now merge on the IDs

new_df = pd.merge(df1, df2, how = 'inner',left_on = ['ID'], right_on = ['ID'])

This should yield the same table (with the addition of an ID column).