Value error when merging 2 dataframe with identical number of row

Question

I have a dataframe like this:

+-----+-------+---------+
|  id | Time  | Name    |
+-----+-------+---------+
| 1   | 1     | John    |
+-----+-------+---------+
| 2   | 2     | David   |
+-----+-------+---------+
| 3   | 4     | Rebecca |
+-----+-------+---------+
| 4   | later | Taylor  |
+-----+-------+---------+
| 5   | later | Li      |
+-----+-------+---------+
| 6   | 8     | Maria   |
+-----+-------+---------+

I want to merge with another table based on 'id' and time:

  data1=pd.merge(data1, data2,left_on=['id', 'time'],right_on=['id', 'time'], how='left')

The other table data

+-----+-------+--------------+
|  id | Time  | Job          |
+-----+-------+--------------+
| 2   | 2     | Doctor       |
+-----+-------+--------------+
| 1   | 1     | Engineer     |
+-----+-------+--------------+
| 4   | later | Receptionist |
+-----+-------+--------------+
| 3   | 4     | Professor    |
+-----+-------+--------------+
| 5   | later | Lawyer       |
+-----+-------+--------------+
| 6   | 8     | Trainer      |
+-----+-------+--------------+

It raised error:

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

What I tried:

data1['time']=data1['time'].astype(str)
data2['time']=data2['time'].astype(str)

Did not work. What can I do? PS: in this example Id are different, but in my data Id can be the same so I need to merge both on Time and Id

Possible duplicate of [Trying to merge 2 dataframes but get ValueError](https://stackoverflow.com/questions/50649853/trying-to-merge-2-dataframes-but-get-valueerror) — Kostas Charitidis, Oct 04 '19 at 10:06
Read the post carefully and you will see why its not a duplicate — FARRAF, Oct 04 '19 at 10:14
The `id` column is probably of type int64, therefore you should transform this one instead — 00schneider, Oct 04 '19 at 10:20

score 1 · Accepted Answer · answered Oct 04 '19 at 10:18

1

Have you tried also casting 'id' column to either str or int?

Sorry but I have not enough reputation for just comment your question.

answered Oct 04 '19 at 10:18

Alfonso

103
1
7

Hi, thank you, I did change both column to str now, hopefully it works – FARRAF Oct 04 '19 at 10:26

Value error when merging 2 dataframe with identical number of row

1 Answers1