CODEWITHSUNDEEP
pythonpandasmergeinner-join
mannaroth
mannaroth

Reputation: 1543

Pandas inner merge/join returning all rows

I'm trying to merge two data frames based on a column present in both, keeping only the intersection of the two sets.

The desired result is:

 foo           bar            foobar
x y z        x j i           x y z j i
a 1 2        a 9 0           a 1 2 9 0
b 3 4        b 9 0           b 3 4 9 0 
c 5 6        c 9 0           c 5 6 9 0
d 7 8        e 9 0           
             f 9 0

My code that does not produce the desired result is:

pd.merge(foo, bar, how='inner', on='x')

Instead, the code seems to return:

 foo           bar            foobar
x y z        x j i           x y z j i
a 1 2        a 9 0           a 1 2 9 0
b 3 4        b 9 0           b 3 4 9 0 
c 5 6        c 9 0           c 5 6 9 0
d 7 8        e 9 0           e * * 9 0
             f 9 0           f * * 9 0

(where * represents an NaN)

Where am I going wrong? I've already reached the third Google page trying to fix this an nothing works. Whatever I do I get an outer join, with all rows in both sets.

Upvotes: 2

Views: 452

Answers (1)

MaxU - stand with Ukraine
MaxU - stand with Ukraine

Reputation: 210982

Usually it means that you have duplicates in the column(s) used for joining, resulting in cartesian product.

Demo:

In [35]: foo
Out[35]:
   x  y  z
0  a  1  2
1  b  3  4
2  c  5  6
3  d  7  8

In [36]: bar
Out[36]:
   x  j  i
0  a  9  0
1  b  9  0
2  a  9  0
3  a  9  0
4  b  9  0

In [37]: pd.merge(foo, bar)
Out[37]:
   x  y  z  j  i
0  a  1  2  9  0
1  a  1  2  9  0
2  a  1  2  9  0
3  b  3  4  9  0
4  b  3  4  9  0

Upvotes: 2

Related Questions