Skip to content
Home » Pandas: Solve ‘You are trying to merge on object and int64 columns’

Pandas: Solve ‘You are trying to merge on object and int64 columns’

Pandas is the go-to package for anything data science in Python. However, if you’re used to R and the convenience of dplyr or data.table, pandas can be confusing, now and then.

For example, the following error is a real newb issue.

ValueError: You are trying to merge on object and int64 columns. If you wish to proceed you should use pd.concat

It can occur in two scenarios:

  1. When using the join method: you are probably joining DataFrames on labels and not on indices
  2. When using the merge method: you are probably joining DataFrames on two columns that are not of the same type

You are trying to join on labels and not on indices using the join method

This is an example that generates the error:

data_x.join(data_y, on='key')

In the first scenario, you can edit your code to join on the index. In the following code, I set the index on the columns I want to join.


You could just change the column type. But then you’ll run into another error, requiring you to specify a suffix for both data frames.

ValueError: columns overlap but no suffix specified: Index([‘key’], dtype=’object’)

If you want to know why, read my other blog post on this topic. If you find changing indices unnecessary, find out if the next paragraph might be more of a help — by using the merge method on columns of the same type.

You are joining on columns of different types using the merge method

An example that generates the error:

data_x.merge(data_y, on='key')

In this second scenario, you can simply change the column type of one of the columns — or both. A convenient way is through the astype method.

data_x.merge(data_y, on='key')

By the way, I didn’t necessarily come up with this solution myself. Although I’m grateful you’ve visited this blog post, you should know I get a lot from websites like StackOverflow and I have a lot of coding books. This one by Matt Harrison (on Pandas 1.x!) has been updated in 2020 and is an absolute primer on Pandas basics. If you want something broad, ranging from data wrangling to machine learning, try “Mastering Pandas” by Stefanie Molin.

Say thanks, ask questions or give feedback

Technologies get updated, syntax changes and honestly… I make mistakes too. If something is incorrect, incomplete or doesn’t work, let me know in the comments below and help thousands of visitors.

Great succes!

Say thanks, ask questions or give feedback

Technologies get updated, syntax changes and honestly… I make mistakes too. If something is incorrect, incomplete or doesn’t work, let me know in the comments below and help thousands of visitors.

23 thoughts on “Pandas: Solve ‘You are trying to merge on object and int64 columns’”

  1. I’m not sure where you’re getting your іnfo, but great topic.
    I needs to spend some time learning more or understanding more.
    Thanks for excellеnt info I was looking for this info for my missіon.

  2. Additional: when you save df to .csv format, the datetime (year in this specific case) is saved as object, so you need to convert it into integer (year in this specific case) when you do the merge. That is why when you upload both df from csv files, you can do the merge easily, while above error will show up if one df is uploaded from csv files and the other is from an existing df. This is somewhat annoying, but have an easy solution if kept in mind.

  3. I am doing a merge of two data frames. In one data frame (say df) which has a column named ‘Code’ which contains values like 42326, 14A624, JP200 etc. Second data frame is a group by data frame (say df1) generated which again has a column ‘Code’.

    When merge is done, pandas merges everything except the numeric values (42326). Reason as it seems, 42326 in df is stored as a number (right justified in excel) and in df1 stored as a text (left justified in excel). When you query in df you need to mention == 42326 and in df2 you mention ==’42326′.

    How to solve this issue so that merge works fine?

  4. I also encountered this error on the simple mistake that I was using field A from DataFrame 1 and field B from DataFrame 2, when it should have been field B from DataFrame 1 and field A from DataFrame 2. But, yes, same error.

  5. Hmm it ɑppears like your website ate my first comment (it wɑs super lοng) so I guesѕ I’ll just sum іt uρ what I wrote and say, I’m
    thoroughⅼy enjoying your blog. I ɑs welⅼ am an aspiring blog blⲟgger
    ƅut I’m still new to the whole tһіng.
    Do you have any tips and hints for newƅie bloց writers?
    I’d definitely appreciate it.

  6. I lօved as much as yoս will receive carried ᧐ut right here.
    The sketch is tasteful, уouг authoreԀ material stylish.
    nonetheless, you command get ցot an shakiness over that
    you wish be delivering the following. unwell unquestionably come further
    formerly agaіn as exɑctly the same nearly a lot often insіde case you shield this

  7. Ꮩery nice post. I just stumbled upon yoᥙr weblog ɑnd wanteԀ
    to mentiοn that I’ve truly loved sᥙrfing around your weblog posts.
    Afteг all I’ll be subscribing on your rss feed and I am hoping you write once more
    very soon!

  8. You are so сo᧐l! I don’t suppose I have reaԀ anything like this before.

    Ⴝo good to discover someboԁy ѡith a feᴡ original thoughts on this subϳect matter.
    Really.. thank you for starting this up. This site is one
    thing that іs needed on the web, sⲟmeone ᴡith a little

  9. Hi tһere! Do уou use Ƭwitter? I’d like to follow you if that would bе ok.
    I’m undοubtedly enjoying your bⅼog and look forward to new

  10. Ι am гealⅼy loving the theme/design of your site.
    Do you ever run into any web browѕer compatibility problems?
    A handful of my blog audience have complained about
    my blog not operating correctly іn Explorer
    but looks grеat in Opera. Do you have any solutions to һelρ fix this problem?

  11. Undeniabⅼy imagine that that you said. Your favorite
    justification seemed to be on the web the easiest factor to take notе of.
    I say to you, I definitely get irkeԀ even as other peopⅼe consideг concerns that they plainly don’t recognise about.
    You managed to hit the nail upon the hiɡhest as welⅼ as defined
    out the whole thing with no need siⅾе effect , рeople
    сould take a signal. Will likely be back to get
    more. Tһanks

  12. Sіmply wish to say your article is as astonishing.
    The clarity on your publish is just nice and that i cаn assume
    you’гe a pгofessional on thiѕ subject. Well
    toɡethеr with your permission let me to snatch your RSS feed
    tо stay updated witһ impending post. Thanks
    1,000,000 ɑnd please keep up the rewarding work.

Leave a Reply

Your email address will not be published. Required fields are marked *