.join works incorrectly with MultiIndex (data is not actually alligned)

[rsamson]

• 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

[wesm]

Thanks a lot for catching and reporting this. Fixed in the above commit, will be part of 0.5.1 release

[rsamson]

Great - thank you as always for all the great development work.

On Wed, Nov 9, 2011 at 10:17 PM, Wes McKinney <
[email protected]>wrote:

Thanks a lot for catching and reporting this. Fixed in the above commit,
will be part of 0.5.1 release

---

Reply to this email directly or view it on GitHub:
https://github.com/wesm/pandas/issues/351#issuecomment-2691013