How to merge pandas dataframe based on substring of column elements python

Question

I have two dataframes A and B. Dataframe A looks like:

col1    col2   col3
 a_low   5      6
 a_low   3      10
 a_high  4      4

Dataframe B looks like:

col1 colB 
 a    90

Now, I want to merge df A and B on the substring a in col1 from df A and col1 from df B. Hence, the result should be:

col1    col2   col3  colB
 a_low   5      6     90
 a_low   3      10    90
 a_high  4      4     90

Anyone knows how to do this using merge?

Anurag Dabas · Accepted Answer · 2021-04-19 15:59:57Z

2

You can also use assign() and merge() method:

result=dfA.assign(col1=dfA['col1'].str.split('_').str[0]).merge(dfB).assign(col1=dfA['col1'])

Now if you print result you will get your desired output:

    col1    col2    col3    colB
0   a_low   5       6       90
1   a_low   3       10      90
2   a_high  4       4       90

answered Apr 19, 2021 at 15:40

Anurag Dabas

24.3k9 gold badges25 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Quang Hoang · Accepted Answer · 2021-04-19 15:38:36Z

2

You need to extract the part of string from col1, e.g. with str.split or str.extract(), then either merge or map:

dfA['colB'] = (dfA['col1'].str.split('_').str[0]
                  .map(dfB.set_index('col1')['colB']
              )

answered Apr 19, 2021 at 15:38

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

azal · Accepted Answer · 2021-04-19 15:40:19Z

1

You can use str.extract + insert:

pat = "|".join(df_a.col1)
df_b.insert(0, 'a', df_b['col1'].str.extract("(" + pat + ')', expand=False))

answered Apr 19, 2021 at 15:40

azal

1,2726 gold badges25 silver badges49 bronze badges