Join pandas dataframe on substring

Question

I have two pandas dataframes like below:

df1:

first_11_id spending
94838485868 50
28394958384 25
93482329832 734

df2:

first_15_id      savings
948384858684932  43
283949583845488  342
934823298324312  584

I would like to join them on id, and as you can see the first_15_id contains the first 11 digits of first_11_id

Desired output:

first_15_id      savings spending
948384858684932  43      50
283949583845488  342     25
934823298324312  584     734

Something like result = df2.join(df1, how = 'inner', on = ?)

rafaelc · Accepted Answer · 2019-09-12 03:38:31Z

2

You may

df2.assign(first_11_id=df2.first_15_id.astype(str).str[:11].astype(float))\
   .merge(df, on='first_11_id')\
   .drop('first_11_id', 1)

       first_15_id  savings  spending
0  948384858684932       43        50
1  283949583845488      342        25
2  934823298324312      584       734

answered Sep 12, 2019 at 3:38

rafaelc

59.4k15 gold badges64 silver badges87 bronze badges

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

Collectives™ on Stack Overflow

Join pandas dataframe on substring

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related