pivot and preserve column/row order in pandas-CodePudding

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

CodePudding user response：

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()))