sort_index behavior differs for the same DataFrame?

[tlmaloney]

I feel like I've encountered a bug. In the following scenario, the first sort_index call behaves as expected, but the second does not. Does someone know what the difference is here?

In [1]: import pandas as pd

In [2]: pd.__version__
Out[2]: '0.15.2'

In [3]: tuples = [(' foo', 'bar'), ('foo', 'bar'), (' foo ()', 'bar')]

In [4]: cols = pd.MultiIndex.from_tuples(tuples)

In [5]: df = pd.DataFrame(index=cols, data={'baz': [0, 1, 2]})

In [6]: df
Out[6]: 
             baz
 foo    bar    0
foo     bar    1
 foo () bar    2

In [7]: df.sort_index()
Out[7]: 
             baz
 foo    bar    0
 foo () bar    2
foo     bar    1

In [8]: tuples = [(' foo', 'bar'), ('foo', 'bar')]

In [9]: cols = pd.MultiIndex.from_tuples(tuples)

In [10]: df = pd.DataFrame(index=cols, data={'baz': [0, 1]})

In [11]: df
Out[11]: 
          baz
 foo bar    0
foo  bar    1

In [12]: df.ix[(' foo ()', 'bar'), 'baz'] = 2

In [13]: df
Out[13]: 
             baz
 foo    bar    0
foo     bar    1
 foo () bar    2

In [14]: df.sort_index()
Out[14]: 
             baz
 foo    bar    0
foo     bar    1
 foo () bar    2

[tlmaloney]

FWIW:

~
tmaloney@aal-lpc-2-03$  locale
LANG=en_US.UTF-8
LANGUAGE=
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=

[jreback]

under the hood this uses something like this: http://docs.scipy.org/doc/numpy/reference/generated/numpy.argsort.html

this is kind=quicksort by default, which does not preserve the original order for ties, while mergesort does. It is not possible to pass thru the option for kind in sort_index, you can specify it for sort though.

So i'll mark this an enhancement if you'd like to put forth a PR.

[tlmaloney]

I'll give a crack at it, thanks.

[tlmaloney]

I do see that kind is a variable that can be passed into sort_index, see docs here. Although it says that it does not apply to a MultiIndex.

I'm not sure kind is the reason for the issue. I was able to get the behavior I want, although through a roundabout way, still using quicksort:

In [1]: import pandas as pd

In [2]: tuples = [(' foo', 'bar'), ('foo', 'bar')]

In [3]: cols = pd.MultiIndex.from_tuples(tuples)

In [4]: df = pd.DataFrame(index=cols, data={'baz': [0, 1]})

In [5]: df.ix[(' foo ()', 'bar'), 'baz'] = 2

In [6]: df
Out[6]: 
             baz
 foo    bar    0
foo     bar    1
 foo () bar    2

In [7]: df.reset_index()
Out[7]: 
   level_0 level_1  baz
0      foo     bar    0
1      foo     bar    1
2   foo ()     bar    2

In [8]: df.reset_index().sort(columns=['level_0', 'level_1'])
Out[8]: 
   level_0 level_1  baz
0      foo     bar    0
2   foo ()     bar    2
1      foo     bar    1

In [9]: df.reset_index().sort(columns=['level_0', 'level_1']).set_index(['level_0', 'level_1'])
Out[9]: 
                 baz
level_0 level_1     
 foo    bar        0
 foo () bar        2
foo     bar        1

[tlmaloney]

This is also odd behavior. Adding a row doesn't simply append it to the bottom, it also switches the labels in the original two rows of the index.

In [1]: import pandas as pd

In [2]: tuples = [('foo', 'bar'), (' foo', 'bar')]

In [3]: cols = pd.MultiIndex.from_tuples(tuples)

In [4]: df = pd.DataFrame(index=cols, data={'baz': [0, 1]})

In [5]: df
Out[5]: 
          baz
foo  bar    0
 foo bar    1

In [6]: df.index
Out[6]: 
MultiIndex(levels=[[u' foo', u'foo'], [u'bar']],
           labels=[[1, 0], [0, 0]])

In [7]: df.ix[(' foo ()', 'bar'), 'baz'] = 2

In [8]: df
Out[8]: 
             baz
 foo    bar    1
foo     bar    0
 foo () bar    2

In [9]: df.index
Out[9]: 
MultiIndex(levels=[[u' foo', u'foo', u' foo ()'], [u'bar']],
           labels=[[0, 1, 2], [0, 0, 0]])

[behzadnouri]

This is a bug, and the problem is when the frame is modified, df.index.lexsort_depth becomes invalid, and that breaks .sort_index:

>>> pd.__version__
'0.15.2-68-ge2b014c'
>>> df = pd.DataFrame([['b', 'c', 1]],
...                   columns=['1st', '2nd', '3rd']).set_index(['1st', '2nd'])
>>> df
         3rd
1st 2nd     
b   c      1
>>> df.ix[('a', 'b'), '3rd'] = 0
>>> df
         3rd
1st 2nd     
b   c      1
a   b      0
>>> df.index.lexsort_depth  #  <<< this is the BUG
2
>>> df.sort_index()
         3rd
1st 2nd     
b   c      1
a   b      0

[jreback]

hmm, looks like sortorder needs to be invalidated. (however, then we de-facto have a new index at that point, so may need to copy the index in this case).

[tlmaloney]

It looks like this has been an issue at least going back to v0.13.1:

In [1]: import pandas as pd

In [2]: pd.__version__
Out[2]: '0.13.1'

In [3]: df = pd.DataFrame([['b', 'c', 1]], columns=['1st', '2nd', '3rd']).set_index(['1st', '2nd'])

In [4]: df.ix[('a', 'b'), '3rd'] = 0

In [5]: df.index.is_lexsorted()
Out[5]: True

In [6]: df
Out[6]: 
         3rd
1st 2nd     
b   c      1
a   b      0

In v0.12.0, setting values was not working with .ix[...] =, but using set_value to set the new row, sort_index does work as expected.

In [1]: import pandas as pd

In [2]: pd.__version__
Out[2]: '0.12.0'

In [3]: df = pd.DataFrame([['b', 'c', 1]], columns=['1st', '2nd', '3rd']).set_index(['1st', '2nd'])

In [4]: df.ix[('a', 'b'), '3rd'] = 0
---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
<ipython-input-4-b91d80d789ee> in <module>()
----> 1 df.ix[('a', 'b'), '3rd'] = 0

/home/tmaloney/vedev/pandas-test-02/lib/python2.7/site-packages/pandas-0.12.0-py2.7-linux-x86_64.egg/pandas/core/indexing.pyc in __setitem__(self, key, value)
     82                 raise IndexingError('only tuples of length <= %d supported',
     83                                     self.ndim)
---> 84             indexer = self._convert_tuple(key)
     85         else:
     86             indexer = self._convert_to_indexer(key)

/home/tmaloney/vedev/pandas-test-02/lib/python2.7/site-packages/pandas-0.12.0-py2.7-linux-x86_64.egg/pandas/core/indexing.pyc in _convert_tuple(self, key)
     94         keyidx = []
     95         for i, k in enumerate(key):
---> 96             idx = self._convert_to_indexer(k, axis=i)
     97             keyidx.append(idx)
     98         return tuple(keyidx)

/home/tmaloney/vedev/pandas-test-02/lib/python2.7/site-packages/pandas-0.12.0-py2.7-linux-x86_64.egg/pandas/core/indexing.pyc in _convert_to_indexer(self, obj, axis)
    608                 mask = check == -1
    609                 if mask.any():
--> 610                     raise KeyError('%s not in index' % objarr[mask])
    611 
    612                 return indexer

KeyError: "['a'] not in index"

In [5]: df.set_value(('a', 'b'), '3rd', 0)
Out[5]: 
     3rd
b c    1
a b    0

In [6]: df = df.set_value(('a', 'b'), '3rd', 0)

In [7]: df
Out[7]: 
     3rd
b c    1
a b    0

In [8]: df.sort_index()
Out[8]: 
     3rd
a b    0
b c    1

[tlmaloney]

Looks like GH4039 discussion is relevant. @jreback Should this issue be tagged as Bug instead of Enhancement?

[jreback]

The main issue I have with this is that you cannot simply resort the index after an append. It will be completely non-performant. So you can simply try to invalidate sortorder which will recompute the sort depth when it is called for.