Mapping elements from one dataframe to another based on matching column values

Question

I have 2 dataframes:

Reference_DF:

root_cause_level3   root_cause_level2   root_cause_level1
NA                  NA                  NA
Natural Disaster    Natural Disaster    Natural Disaster
Other               Other               Other
Communications      Misunderstood       Long Message

Actual_DF:

incident_id        root_cause_level3       root_cause_level2   root_cause_level1
XXXXXXXX-1         Communications       
XXXXXXXX-2         NA       
XXXXXXXX-3         NA       
XXXXXXXX-4         Other        
XXXXXXXX-1         Natural Disaster

My goal is to map from Reference DF to Actual DF the level2 and level1 root causes. I want to map it based on matching level3 root causes. I don't know how to do that, any suggestions on how to solve this problem?

Expected output:

incident_id        root_cause_level3       root_cause_level2   root_cause_level1
XXXXXXXX-1         Communications          Misunderstood       Long Message
XXXXXXXX-2         NA                      NA                  NA
XXXXXXXX-3         NA                      NA                  NA       
XXXXXXXX-4         Other                   Other               Other        
XXXXXXXX-1         Natural Disaster        Natural Disaster    Natural Disaster

you have two `root_cause_level2` columns in your `Actual_DF`. is one of those supposed to be `root_cause_level3`? — wpercy, Feb 27 '19 at 19:44
@ScottBoston, thanks for the feedback, I added the expected output — Sam, Feb 27 '19 at 19:46

score 0 · Accepted Answer · answered Feb 27 '19 at 19:50

0

You should be able to use .merge() like this:

new_df = pandas.merge(actual_df, reference_df, on='root_cause_level3', how='left')

a cleaner way could be to do something like:

new_df = pandas.merge(actual_df[['incident_id','root_cause_level3']], reference_df, on='root_cause_level3', how='left')

then you don't get duplicated column names in the new_df

answered Feb 27 '19 at 19:50

wpercy

9,636
4
33
45

Blessed, thank you! – Sam Feb 27 '19 at 19:53

Mapping elements from one dataframe to another based on matching column values

1 Answers1