join in two pandas dataframe based on substring of one dataframe

Question

I am trying to merge two data frames where df1 column A contains substring of df2 column B.

for example

df1:
columnA Actual
AB      Apple
CD      Straw
EF      Ele

df2:
ColumnB  Values
UserAB    1
UserCD    2
UserEF    3
UserGH    4 
UserIJ    5

I am looking for output like below, can't able to identify how can I get this done.

Output:
   ColumnB  Values  ColumnA  Actual
    UserAB    1      AB       Apple
    UserCD    2      CD       Straw
    UserEF    3      EF       Ele
    UserGH    4      NaN      NaN
    UserIJ    5      NaN      NaN

Are the substrings in ColumnA and strings in ColumnB fixed length or they vary? — rafaelc
– rafaelc, Commented Aug 8, 2019 at 4:07

anky · Accepted Answer · 2019-08-08 04:09:16Z

1

You can try with str.extract():

p='({})'.format('|'.join(df1.columnA))
d=df1.set_index('columnA')['Actual']
s=df2.ColumnB.str.extract(p,expand=False)

df2=df2.assign(ColumnA=s,Actual=s.map(d))

  ColumnB  Values ColumnA Actual
0  UserAB       1      AB  Apple
1  UserCD       2      CD  Straw
2  UserEF       3      EF    Ele
3  UserGH       4     NaN    NaN
4  UserIJ       5     NaN    NaN

answered Aug 8, 2019 at 4:09

anky

75.3k11 gold badges46 silver badges76 bronze badges

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

Comments

mk_ · Accepted Answer · 2019-08-08 04:24:16Z

0

You can use df.append:

df1 = df1.append(pd.DataFrame([[float('nan'), float('nan')], [float('nan'), float('nan')]], columns = ["ColumnA", "Actual"])).reset_index(drop=True)
df2 = df2.merge(df1, how = "left", on = df2.index).drop("key_0", axis = 1)

output:

  ColumnB Values ColumnA Actual
0  UserAB      1      AB  Apple
1  UserCD      2      CD  Straw
2  UserEF      3      EF    Ele
3  UserGH      4     NaN    NaN
4  UserIJ      5     NaN    NaN

answered Aug 8, 2019 at 4:24

mk_

1221 silver badge9 bronze badges

Collectives™ on Stack Overflow

join in two pandas dataframe based on substring of one dataframe

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related