pandas: Join two data frames on columns of different types

Question

I am trying to join (merge) two pandas data frames: df_A and df_B. I want to join on df_A.a_number = df_B.b_number

However, df_A.a_number is of type int64, df_B.b_number is of type object. I tried to convert both of them to str before merge like below, but doesn't seem to work ...

df_A.a_number.astype('str')
df_A.dtypes

a_number       int64
a_set    object
dtype: object

and here is df_B:

df_B.b_number.astype('str')
df_B.dtypes

b_number    object
dtype: object

Then I try to join df_A and df_B:

df_C = pd.merge(df_A,df_B, how ='inner', left_on = ['a_number'], right_on = ['b_number'])
df_C

However, df_C is empty (which should't be). I am guess maybe because a_number and b_number are of different type and were not convert to str properly? Could anyone please advise? Thanks!

joelostblom · Accepted Answer · 2017-04-04 00:36:22Z

4

Try keeping df_A.a_number as Int64 and convert the key in df_B.b_number to Int64 as well.

df_B.b_number.astype('int')
df_C = pd.merge(df_A,df_B, how ='inner', left_on = ['a_number'], right_on = ['b_number'])

answered Apr 4, 2017 at 0:36

joelostblom

49.8k20 gold badges166 silver badges179 bronze badges

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

Comments

Tamjid Rayhan · Accepted Answer · 2021-08-24 12:27:03Z

2

As df_A.a_number is int64 and you want to convert it to str, you can do the following-

df_A.a_number = df_A.a_number.astype('str')

You were missing this assignment of the changed column I guess. After you assigned the transformed df_A.a_number you can do the join and it should work.

answered Aug 24, 2021 at 12:27

Tamjid Rayhan

211 bronze badge

Collectives™ on Stack Overflow

pandas: Join two data frames on columns of different types

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