CODEWITHSUNDEEP
pythonpandasdataframejoin
Programmer
Programmer

Reputation: 1294

Join pandas dataframe on substring

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 = ?)

Upvotes: 1

Views: 339

Answers (1)

rafaelc
rafaelc

Reputation: 59274

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

Upvotes: 2

Related Questions