How to reorder indexed rows based on a list in Pandas data frame
PythonPandasDataframePython Problem Overview
I have a data frame that looks like this:
company Amazon Apple Yahoo
name
A 0 130 0
C 173 0 0
Z 0 0 150
It was created using this code:
import pandas as pd
df = pd.DataFrame({'name' : ['A', 'Z','C'],
'company' : ['Apple', 'Yahoo','Amazon'],
'height' : [130, 150,173]})
df = df.pivot(index="name", columns="company", values="height").fillna(0)
What I want to do is to sort the row (with index name) according to a predefined list:
["Z", "C", "A"]`
Resulting in this :
company Amazon Apple Yahoo
name
Z 0 0 150
C 173 0 0
A 0 130 0
How can I achieve that?
Python Solutions
Solution 1 - Python
You could set index on predefined order using reindex like
In [14]: df.reindex(["Z", "C", "A"])
Out[14]:
company Amazon Apple Yahoo
Z 0 0 150
C 173 0 0
A 0 130 0
However, if it's alphabetical order, you could use sort_index(ascending=False)
In [12]: df.sort_index(ascending=False)
Out[12]:
company Amazon Apple Yahoo
name
Z 0 0 150
C 173 0 0
A 0 130 0
Like pointed below, you need to assign it to some variable
In [13]: df = df.sort_index(ascending=False)
Solution 2 - Python
We could also use loc:
lst = ["Z", "C", "A"]
df = df.loc[lst]
Output:
company Amazon Apple Yahoo
name
Z 0 0 150
C 173 0 0
A 0 130 0
Note that if there are values in lst that does not exist in df.index (e.g. if lst=['Z','C','A','D']), then loc throws a KeyError (whereas reindex creates a new row 'D' full of NaNs).
MultiIndex
If df is MultiIndex, such as:
C3
C1 C2
2 evelen 0
ten 1
twelve 2
1 evelen 3
ten 4
twelve 5
and if you want to sort the second level by ten,eleven andtwelve, then using loc:
out = df.loc[:, ['ten','evelen', 'twelve'],:]
Output:
C3
C1 C2
2 evelen 0
ten 1
twelve 2
1 evelen 3
ten 4
twelve 5
and for both levels:
out = df.loc[[1,2], ['ten','evelen','twelve'], :]
Output:
C3
C1 C2
1 ten 4
evelen 3
twelve 5
2 ten 1
evelen 0
twelve 2