CODEWITHSUNDEEP
pythonpandas
Kiki
Kiki

Reputation: 7

pandas custom merge with condition key1 contains key2

I'm trying to merge two dataframes with the condition that key1 contains key2, here is the example

df1

name         info
Bill Gates   Microsoft
Steve Jobs   Apple
Elon Musk    Tesla

df2

name        info
Bill        aaa
Kate        bbb
Steve       ccc

I'm merging on the key 'name' column,the result I want to get is

df3

name          info         name     info
Bill Gates    Microsoft    Bill     aaa
Steve Jobs    Apple        Steve    ccc

and in my case df1 is relative small with about 600 records, but df2 is pretty large with about 2million records, so efficiency is also a concern, thanks a lot.

Upvotes: 1

Views: 92

Answers (1)

piRSquared
piRSquared

Reputation: 294506

Rename stuff and merge

rename_dict = {'name': 'full', 'info': 'company'}
df1.rename(columns=rename_dict).assign(
    name=lambda d: d.full.str.split().str[0]).merge(df2)

         full    company   name info
0  Bill Gates  Microsoft   Bill  aaa
1  Steve Jobs      Apple  Steve  ccc

Upvotes: 1

Related Questions