Trying to understand how pandas merge works, right join specifically

Viewed 127

I ran into a confusing result with a larger dataframe, have made a toy one that captures some of what's confusing me:

import pandas as pd
big_index = [123, 124, 125, 126, 127, 128, 129, 130]
big_dat = {'year': pd.Series([2000, 2000, 2000, 2001, 2002, 2002, 2002, 2004], index=big_index),
      'other': pd.Series(['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h'], index=big_index)}
big_df = pd.DataFrame(big_dat)

year_index = [2003, 2000, 2001, 2002]
year_dat = {'a': pd.Series([1, 2, 3, 4], index=year_index),
        'b': pd.Series([5, 6, 7, 8], index=year_index)}
year_df = pd.DataFrame(year_dat)

left, and inner merge work as I'd expect, but right and outer produce odd results:

merged_right = pd.merge(
    big_df,
    year_df,
    how='right',
    left_on='year',
    right_index=True
    )
merged_right
    other  year  a  b
123     a  2000  2  6
124     b  2000  2  6
125     c  2000  2  6
126     d  2001  3  7
127     e  2002  4  8
128     f  2002  4  8
129     g  2002  4  8
130   NaN  2003  1  5

merged_outer = pd.merge(
    big_df,
    year_df,
    how='outer',
    left_on='year',
    right_index=True
    )
merged_outer
    other  year    a    b
123     a  2000  2.0  6.0
124     b  2000  2.0  6.0
125     c  2000  2.0  6.0
126     d  2001  3.0  7.0
127     e  2002  4.0  8.0
128     f  2002  4.0  8.0
129     g  2002  4.0  8.0
130     h  2004  NaN  NaN
130   NaN  2003  1.0  5.0

In both cases index 130 gets associated with the 2003 year entry, for no apparent reason. I get that there's no "good" way to handle this since I'm assuming the index itself can't have NaN in it. I'd have expected this to just throw an error though, rather than returning that incorrect last column. I'm probably misunderstanding what pandas is doing under the hood. Tips to resources to figure out why this is going wrong would be as appreciated as code showing how to do it right.

0 Answers
Related