python - Mapping values and merging dataframes based on multiple column values

Question

I have the following dataframes:

df1: dataframe with patient critical notes

AREA                      DATE_TIME                 CRITICAL ISSUE NOTES
0013                      11/6/2017 2:25:00 P.M     Nurse attended to the patient 
1121                      10/23/2017 6:43:00 A.M    Completed an ER
1121                      10/2/2017 9:30:00 P.M     Admitted 

df2: Patient other details

ZIP                TIME_NOTED   NAME    OCCUPIED    STATE
4568    10/1/2017 10:04:00 A.M  Chris          Y    NORMAL
1121    10/23/2017 6:43:00 A.M  Nancy          Y    CRITICAL
1121    10/2/2017 9:30:00 P.M   Derek          N    CRITICAL

I have to map the records in df2 using DATE_TIME and AREA code from df1 and also retain all other columns in both dataframes. I tried merging on multiple columns but didnt work as expected.

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

Answer 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).