CODEWITHSUNDEEP
pythonpandas
S N
S N

Reputation: 600

Python pandas pivot_table category column position

I'm trying to pivot a Python pandas dataframe, its working but the problem is that the order of columns is distorted. Let's say my dataframe is as following 

 --------
>>> df = pd.DataFrame({"A": ["foo", "foo", "foo", "foo", "foo",
...                          "bar", "bar", "bar", "bar"],
...                    "B": ["one", "one", "one", "two", "two",
...                          "one", "one", "two", "two"],
...                    "C": ["small", "large", "large", "small",
...                          "small", "large", "small", "small",
...                          "large"],
...                    "D": [1, 2, 2, 3, 3, 4, 5, 6, 7],
...                    "E": [2, 4, 5, 5, 6, 6, 8, 9, 9]})
>>> df
     A    B      C  D  E
0  foo  one  small  1  2
1  foo  one  large  2  4
2  foo  one  large  2  5
3  foo  two  small  3  5
4  foo  two  small  3  6
5  bar  one  large  4  6
6  bar  one  small  5  8
7  bar  two  small  6  9
8  bar  two  large  7  9

after pivoting its like following

 >>> table = pd.pivot_table(df, values='D', index=['A', 'B'],
...                     columns=['C'], aggfunc=np.sum)
>>> table
C        large  small
A   B
bar one    4.0    5.0
    two    7.0    6.0
foo one    4.0    1.0
    two    NaN    6.0

In the above pivoted output, I'd like to see "small" first and then "large"

C        small  large
A   B
bar one    5.0    5.0
    two    6.0    7.0
foo one    1.0    4.0
    two    6.0    NaN

I am unable to find any option online so far. Category column values (Column C in above sample df) are sorted in alphabetical order as per some posts. Can anyone please tell how to achieve it? Our underlying data from database is in certain order and users want to see similar order in pivoted form.

Thank you.

Upvotes: 1

Views: 869

Answers (3)

ansev
ansev

Reputation: 30930

Use DataFrame.sort_index with axis=1 and ascending=False: 

 table = table.sort_index(axis=1, ascending=False)

or 

table = table.loc[:, ['small', 'large']]

or

table = table.reindex(columns = ['small', 'large'])

but I think with sort_index you don't need write the name of the axes

Upvotes: 2

B. Christian Kamgang
B. Christian Kamgang

Reputation: 6509

After you have pivoted, you can change the order:

table.loc[:, ['small', 'large']]

C        small  large
A   B                
bar one    5.0    4.0
    two    6.0    7.0
foo one    1.0    4.0
    two    6.0    NaN

Upvotes: 0

David Erickson
David Erickson

Reputation: 16683

@SN would a manual method suffice?

table = table[['small','large']]
table

Upvotes: 0

Related Questions