pivot and preserve column/row order in pandas

user_2635863 0
Viewed 39
df = pd.DataFrame({'foo': ['two', 'two', 'two', 'one', 'one','one'],
                   'bar': ['B', 'A', 'C', 'B', 'A', 'C'],
                   'baz': [1, 2, 3, 4, 5, 6],
                   'zoo': ['x', 'y', 'z', 'q', 'w', 't']})

df.pivot(index='foo', columns='bar', values='baz')

bar  A  B  C
foo         
one  5  4  6
two  2  1  3

How can I prevent pivot from sorting rows and columns? Desired output:

bar  B  A  C
foo         
two  1  2  3
one  4  5  6
pandas python
1 Answers

First idea is use ordered Categorical, so pivoting return correct ordering:

df['foo'] = pd.Categorical(df['foo'], ordered=True, categories=df['foo'].unique())
df['bar'] = pd.Categorical(df['bar'], ordered=True, categories=df['bar'].unique())
df1 = df.pivot(index='foo', columns='bar', values='baz')
print (df1)
bar  B  A  C
foo         
two  1  2  3
one  4  5  6

Or is possible change order by DataFrame.reindex:

df1 = (df.pivot(index='foo', columns='bar', values='baz')
         .reindex(index=df['foo'].unique(), columns=df['bar'].unique()))
user_29010020
Related