CODEWITHSUNDEEP
pythonsortingpandasdataframecategorical-data
alvas
alvas

Reputation: 122208

How to sort an indexed dataframe

Unlike sort pandas dataframe based on list, I have an indexed dataframe like this:

$ echo -e 'abc\txyz\t0.9\nefg\txyz\t0.3\nlmn\topq\t0.23\nabc\tjkl\t0.5\n' > test.txt
$ cat test.txt
abc xyz 0.9
efg xyz 0.3
lmn opq 0.23
abc jkl 0.5
$ python

>>> import pandas as pd
>>> df = pd.read_csv('test.txt', delimiter='\t', header=None, dtype={0:unicode, 1:unicode, 2:float})
>>> df = df.pivot(index=0, columns=1, values=2)
>>> df = df.fillna(0)
>>> df
1    jkl   opq  xyz
0                  
abc  0.5  0.00  0.9
efg  0.0  0.00  0.3
lmn  0.0  0.23  0.0

I couldn't figure how to use Categorical in this case:

# Desired row order.
>>> row_order = ['efg', 'abc', 'lmn']
# Desired column roder.
>>> col_order = ['xyz', 'jkl', 'opq']
>>> pd.Categorical(df[0], categories=row_order, ordered=True)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python2.7/dist-packages/pandas/core/frame.py", line 2059, in __getitem__
    return self._getitem_column(key)
  File "/usr/local/lib/python2.7/dist-packages/pandas/core/frame.py", line 2066, in _getitem_column
    return self._get_item_cache(key)
  File "/usr/local/lib/python2.7/dist-packages/pandas/core/generic.py", line 1386, in _get_item_cache
    values = self._data.get(item)
  File "/usr/local/lib/python2.7/dist-packages/pandas/core/internals.py", line 3541, in get
    loc = self.items.get_loc(item)
  File "/usr/local/lib/python2.7/dist-packages/pandas/indexes/base.py", line 2136, in get_loc
    return self._engine.get_loc(self._maybe_cast_indexer(key))
  File "pandas/index.pyx", line 139, in pandas.index.IndexEngine.get_loc (pandas/index.c:4443)
  File "pandas/index.pyx", line 161, in pandas.index.IndexEngine.get_loc (pandas/index.c:4289)
  File "pandas/src/hashtable_class_helper.pxi", line 732, in pandas.hashtable.PyObjectHashTable.get_item (pandas/hashtable.c:13733)
  File "pandas/src/hashtable_class_helper.pxi", line 740, in pandas.hashtable.PyObjectHashTable.get_item (pandas/hashtable.c:13687)
KeyError: 0

I need to achieve an indexed dataframe in this order:

1    xyz  jkl   opq  
0                  
efg  0.3  0.00  0.0
abc  0.9  0.50  0.0
lmn  0.0  0.00  0.23

Upvotes: 0

Views: 433

Answers (1)

unutbu
unutbu

Reputation: 880499

df.reindex can reorder rows and columns:

In [261]: df.reindex(index=row_order, columns=col_order)
Out[261]: 
1    xyz  jkl   opq
0                  
efg  0.3  0.0  0.00
abc  0.9  0.5  0.00
lmn  0.0  0.0  0.23

Upvotes: 1

Related Questions