Why does using merge function in two different dataframes results me more rows?

Question

I have two dataframes of the shape: (4000,3) (2000,3) , with the bellow info and cols:

df1:

imo	speed	length
1	1	4
1	2	4
2	10	10
2	12	10

df2:

imo	dwt	name
1	52	test1
2	62	test2
3	785	test3
4	353	test4

i would like to add column dwt of df2 to df1 based on the same imo.

imo	speed	length	dwt
1	1	4	52
1	2	4	52
2	10	10	62
2	12	10	62

but when i am trying to do pd.merge(df1,df2, on = 'imo', how = 'inner') , the result is much more rows than the original shape of df1 how is that possible?

I cannot replicate the issue. Please provide a reproducible sample with more information: python version, pandas version, etc. — It_is_Chris, Jun 23 '21 at 12:39
Is imo in df2 unique? If it is not the case then you get a merge on the cross-product for each non unique key. — Viktor, Jun 23 '21 at 12:45
then after `pd.merge(df1,df2, on = 'imo', how = 'inner')` you can use `drop_duplicates()` method — Anurag Dabas, Jun 24 '21 at 08:26
[Check out merge validation](https://pandas.pydata.org/pandas-docs/stable/user_guide/merging.html#checking-for-duplicate-keys) — Nic Moetsch, Jun 24 '21 at 14:43
Try a left merge as follows: df=pd.merge(df1,df2,on='imo' , how='left') — The Oracle, Jun 28 '21 at 19:25
@JHk1821 Please make sure that the values in imo are unique, because the code snippet that you provided should work fine. The only thing that can go wrong is the column 'imo' in df2. — Akash Dubey, Jun 29 '21 at 09:21
@AkashDubey the values in imo at df1 there are in multiple rows as you can see from the above table — JHk1821, Jun 29 '21 at 09:33
@JHk1821 Of course, I did notice that. What I meant was, as long as there are unique values in 'imo' in df2. You should good with both "inner" and "left" merge. — Akash Dubey, Jun 29 '21 at 09:40
Check cannonical answer - https://stackoverflow.com/q/53010406 — jezrael, Jun 29 '21 at 12:42

score 1 · Accepted Answer · answered Jun 29 '21 at 10:30

1

You can use an alternate way, without merge function:

dwt_lst = []
for imo in df1.imo.values:
   dwt = df2[df2.imo == imo].dwt.values[0]
   dwt_lst.append(dwt)
df1['dwt'] = dwt_lst

answered Jun 29 '21 at 10:30

JohnV

96
5

score 0 · Answer 2 · answered Jun 29 '21 at 12:41

Just use merge in this way:

pd.merge(df1, df2, how='left', on='imo').drop(columns='name')

Example:

>>> df1 = pd.DataFrame({'imo': [1,1,2,2], 'speed': [1,2,10,12], 'length': [4,4,10,10]})
>>> df2 = pd.DataFrame({'imo': [1,2,3,4], 'dwt': [52,62,785,353], 'name': ['test1','test2','test3','test4']})
>>> pd.merge(df1, df2, how='left', on='imo').drop(columns='name')
   imo  speed  length  dwt
0    1      1       4   52
1    1      2       4   52
2    2     10      10   62
3    2     12      10   62

Why does using merge function in two different dataframes results me more rows?

2 Answers2