Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
524 views
in Technique[技术] by (71.8m points)

python - 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/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'])
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...