CODEWITHSUNDEEP
pythonpandasdataframejoinmerge
maaniB
maaniB

Reputation: 605

Merge two dataframes if data of a column in first dataframe exists in any of the columns of another dataframe in python

I have two data frames I need to merge. The first one is:

page            value
shoes           554
sneakers        226
sandals         114
boots           821
T-shirt         213
mobile-phone    284
laptop          361

The second data frame is:

path1            path2            path3              path4
fashion          footwear         shoes-and-other    shoes
fashion          footwear         shoes-and-other    sneakers
fashion          footwear         sandals            NaN
fashion          footwear         shirts             T-shirt
electronic       devices          mobile-and-tablet  mobile-phone 
electronic       devices          laptop             NaN

My expected output will be:

path1        path2      path3              path4        page         value
fashion      footwear   shoes-and-other    shoes        shoes        554
fashion      footwear   shoes-and-other    sneakers     sneakers     226
fashion      footwear   sandals            NaN          sandals      114
fashion      footwear   shirts             T-shirt      T-shirt      213
electronic   devices    mobile-and-tablet  mobile-phone mobile-phone 284 
electronic   devices    laptop             NaN          laptop       361

I want to join these two data frames if any of the page strings in the first data frame exists in the path1 or path2, or path3, or path4 columns of the second data frame. Notice that page of the first data frame might be matched with path1 of the second data frame and I have a variety of situations.

Is there a simple pythonic way?

Upvotes: 1

Views: 1281

Answers (1)

BENY
BENY

Reputation: 323226

Let us try where with ffill create the merge key , then merge

df1['page'] = df1.where(df1.isin(df.page.tolist())).ffill(1).iloc[:,-1]
df1 = df1.merge(df, how='left')
df1
Out[131]: 
        path1     path2              path3         path4          page  value
0     fashion  footwear    shoes-and-other         shoes         shoes    554
1     fashion  footwear    shoes-and-other      sneakers      sneakers    226
2     fashion  footwear            sandals           NaN       sandals    114
3     fashion  footwear             shirts       T-shirt       T-shirt    213
4  electronic   devices  mobile-and-tablet  mobile-phone  mobile-phone    284
5  electronic   devices             laptop           NaN        laptop    361

Upvotes: 3

Related Questions