BUG: sort_index/sortlevel fails MultiIndex after columns are added.

[8one6]

I have a DataFrame with a MultiIndex on the columns. The first level of the MultiIndex contains strings. The second, floats (though the problem persists if the second level is ints). I add a column to the DataFrame (which should not come last if the columns are sorted). I try to sort the DataFrame. The result does not seem to be sorted. The behavior is fine if the columns are simply an Index (even after adding columns). And the sort works fine in the MultiIndex case as long as no columns have been added since the DataFrame was created.

MWE:

import pandas as pd
import numpy as np

np.random.seed(0)
data = np.random.randn(3,4)

df_multi_float = pd.DataFrame(data, index=list('def'), columns=pd.MultiIndex.from_tuples([('red', i) for i in [1., 3., 2., 5.]]))

print df_multi_float

#OUTPUT
        red                              
          1         3         2         5
d  1.764052  0.400157  0.978738  2.240893
e  1.867558 -0.977278  0.950088 -0.151357
f -0.103219  0.410599  0.144044  1.454274

This sorts just fine as it isnow:

print df_multi_float.sort_index(axis=1)

#OUTPUT
        red                              
          1         2         3         5
d  1.764052  0.978738  0.400157  2.240893
e  1.867558  0.950088 -0.977278 -0.151357
f -0.103219  0.144044  0.410599  1.454274

But if I add columns to both this `DataFrame and then show it sorted, I get what looks to be a wrong result (the new column remains last, rather than being placed second-to-last as it should be):

df_multi_float[('red', 4.0)] = 'world'

print df_multi_float.sort_index(axis=1)

#OUTPUT
        red                                  red
          1         2         3         5      4
d  1.764052  0.978738  0.400157  2.240893  world
e  1.867558  0.950088 -0.977278 -0.151357  world
f -0.103219  0.144044  0.410599  1.454274  world

I'm able to produce this behavior on two systems. The first runs Pandas 0.14.0 and Numpy 1.8.1 and the second runs Pandas 0.14.1 and Numpy 1.8.2. This issue is described here: http://stackoverflow.com/questions/25287130/pandas-sort-index-fails-with-multiindex-containing-floats-as-one-level-when-col?noredirect=1#comment39408150_25287130

[jreback]

@8one6

just make the example ONLY the muli_float and just run it on one. simplier/shorter is much better

[8one6]

Sure. I'll make the change above. I had thought it was important to show that it worked fine with a float index that was not a multiindex. But no problem. (Done.)

[jreback]

@8one6 thanks, your title says it all though

[8one6]

@jreback Just one note on your title update. The problem is still there if all of the floats in my example are replaced by ints.

[jreback]

yeh I think has to do with the adding, will look

[8one6]

I think the fix for this will be much deeper in the bowels of Pandas indices than I'm able to handle. Is there any other way I could help toward a patch for this bug?

[jreback]

np. would appreciate a pull-request on any other issue. thanks!

[8one6]

In an attempt to get around this issue, I started sticking a character at the end of some of my column names (to turn them from numbers into strings). While the sort_index function seems to work fine when the column names are purely letters, this bug seems to come back when they are a mix of numerals and letters.

import pandas as pd
import numpy as np

np.random.seed(0)
data = np.random.randn(3, 4)
df_all_string_indices = pd.DataFrame(data,
                                   columns=pd.MultiIndex.from_product([['2b', '1b'], ['2c', '1c']], names=['one', 'two']),
                                   index=list('xyz'))

print df_all_string_indices

one        2b                  1b          
two        2c        1c        2c        1c
x    1.764052  0.400157  0.978738  2.240893
y    1.867558 -0.977278  0.950088 -0.151357
z   -0.103219  0.410599  0.144044  1.454274

which looks fine. and sorts fine:

print df_all_string_indices.sort_index(axis=1)

one        1b                  2b          
two        1c        2c        1c        2c
x    2.240893  0.978738  0.400157  1.764052
y   -0.151357  0.950088 -0.977278  1.867558
z    1.454274  0.144044  0.410599 -0.103219

But when I add a new column and try to sort, this still goes wrong:

df_all_string_indices[('1a', 'new')] =  'hello'
print df_all_string_indices.sort_index(axis=1)

one        1b                  2b               1a
two        1c        2c        1c        2c    new
x    2.240893  0.978738  0.400157  1.764052  hello
y   -0.151357  0.950088 -0.977278  1.867558  hello
z    1.454274  0.144044  0.410599 -0.103219  hello

(I.e. I think that after the sort_index, the ('1a', 'new') column should come first.)