CODEWITHSUNDEEP
python-3.xpandasdataframejoinconcatenation
Mr JaZ
Mr JaZ

Reputation: 31

Join on a second column if there is not a match on the first column of a pandas dataframe

I need to be able to match on a second column if there is not a match on the first column of a pandas dataframe (Python 3.x).

Ex.


table_df = pd.DataFrame ( {
                    'Name': ['James','Tim','John','Emily'],
                    'NickName': ['Jamie','','','Em'],
                    'Colour': ['Blue','Black','Red','Purple']                          
                          })

lookup_df = pd.DataFrame ( {
                    'Name': ['Tim','John','Em','Jamie'],
                    'Pet': ['Cat','Dog','Fox','Dog']   
                    })

table_df

        Name    NickName    Colour
0       James   Jamie        Blue
1       Tim                  Black
2       John    Red
3       Emily   Em           Purple

lookup_df

    Name    Pet
0   Tim     Cat    
1   John    Dog
2   Em      Fox
3   Jamie   Dog

The result I need:

    Name    NickName    Colour  Pet
0   James   Jamie       Blue    Dog
1   Tim                 Black   Cat
2   John                Red     Dog
3   Emily   Em          Purple  Fox

which is matching on the Name column, and if there is no match, match on the Nickname column, I tried many different things, including:

pd.merge(table_df,lookup_df, how='left', left_on='Name', right_on='Name')
if Nan -> pd.merge(table_df,lookup_df, how='left', left_on='NickName', right_on='Name')

but it does not do what I need and I want to avoid having a nested loop. Has anyone an idea on how to do this? Any feedback is really appreciated.

Thanks!

Upvotes: 3

Views: 860

Answers (1)

Henry Yik
Henry Yik

Reputation: 22503

You can map on Name and fillna on NickName:

s = lookup_df.set_index("Name")["Pet"]

table_df["pet"] = table_df["Name"].map(s).fillna(table_df["NickName"].map(s))

print (table_df)

    Name NickName  Colour  pet
0  James    Jamie    Blue  Dog
1    Tim            Black  Cat
2   John              Red  Dog
3  Emily       Em  Purple  Fox

Upvotes: 3

Related Questions