wesm/pandas — Issue #351: .join works incorrectly with MultiIndex (data is not actually alligned)
Author: rsamson
Created: 2011-11-08 21:34:02+00:00
State: open

- When you use .join with a MultiIndex, it combines the DataFrames improperly.
- When you use .join(how='outer'), the index is destroyed (turned into integers).

Example below.
Thanks,
Ryan

index1=MultiIndex.from_arrays([['a','a','a','b','b','b'], [1,2,3,1,2,3]])

index2=MultiIndex.from_arrays([['b','b','b','c','c','c'], [1,2,3,1,2,3]])

index1

MultiIndex([('a', 1), ('a', 2), ('a', 3), ('b', 1), ('b', 2), ('b', 3)], dtype=object)

index2

MultiIndex([('b', 1), ('b', 2), ('b', 3), ('c', 1), ('c', 2), ('c', 3)], dtype=object)

df1 = DataFrame(data=numpy.random.randn(6), index=index1, columns=['var X'])

df2 = DataFrame(data=numpy.random.randn(6), index=index2, columns=['var Y'])

df1=df1.sortlevel(0)

df2=df2.sortlevel(0)

df1

var X
a 1 0.8658
2 -0.9211
3 -0.6389
b 1 -0.6716
2 -0.6799
3 1.98

df2

var Y
b 1 -0.6978
2 -0.6186
3 0.117
c 1 -0.4577
2 0.7812
3 -1.691

df1.join(df2) #(a1, a2, a3 should be Nan for 'var Y', the values shown under a1, a2, a3 should appear under b1, b2, b3)

var X var Y
a 1 0.8658 -0.6978
2 -0.9211 -0.6186
3 -0.6389 0.117
b 1 -0.6716 -0.4577
2 -0.6799 0.7812
3 1.98 -1.691

df1.join(df2, how='outer') #The index is destroyed, the index is not expanded, and the data is incorrectly alligned

var X var Y
0 0.8658 -0.6978
1 -0.9211 -0.6186
2 -0.6389 0.117
3 -0.6716 -0.4577
4 -0.6799 0.7812
5 1.98 -1.691