pandas: unexpected join behavior results in NaN

Question

I have two dataframes that I'm trying to join in pandas (version 0.18.1).

test1 = pd.DataFrame({'id': range(1,6), 'place': ['Kent','Lenawee','Washtenaw','Berrien','Ottawa']})



  id_1      place

0   1       Kent

1   2    Lenawee

2   3   Montreal

3   4    Berrien

4   5     Ottawa





test2 = pd.DataFrame({'id_2': range(6,11), 'id_parent': range(1,6)})



   id_2  id_parent

0     6          1

1     7          2

2     8          3

3     9          4

4    10          5

Yet when I join the two tables, the last row doesn't join properly and, because it's a left join, results in NaN.

df = test2.join(test1,on='id_parent',how='left')



   id_2  id_parent  id_1      place

0     6          1     2    Lenawee

1     7          2     3   Montreal

2     8          3     4    Berrien

3     9          4     5     Ottawa

4    10          5   NaN        NaN

This doesn't make sense to me-- id_parent and id_1 are the keys on which to join the two tables, and they both have the same value. Both columns have the same dtype (int64). What's going on here?

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

join joins primarily on indices, use merge for this:

In [18]:
test2.merge(test1,left_on='id_parent', right_on='id')

Out[18]:
   id_2  id_parent  id      place
0     6          1   1       Kent
1     7          2   2    Lenawee
2     8          3   3  Washtenaw
3     9          4   4    Berrien
4    10          5   5     Ottawa

You get the NaN because the rhs will use the rhs index and there is no entry for 0 and 5 so you get NaN

pandas: unexpected join behavior results in NaN

Sign up