CODEWITHSUNDEEP
pythonpandaspivot-table
ryzhiy
ryzhiy

Reputation: 343

Arranging columns in pivot_table, Pandas

I have a question regarding rearrangement of the columns in a pivot_table. I want to group columns by month, but with the arrangement as follows:

JAN      FEB  
X,Y,X/Y  X,Y,X/Y ....

The current output is:

JAN FEB      JAN  FEB    JAN ...

X   X   ...  Y     Y ...  X/Y ...

I've noticied the same behavior implemented in Excel when building a pivot table with multiple columns.

See an example below. The output has the first format. Thanks

from pandas import DataFrame,pivot_table
import numpy as np
from datetime import datetime 

names=["a","b","c","a","b"]
dates=["20/01/2013","21/01/2013","22/02/2013", "01/03/2013","01/03/2013"]
dico={"x":[1,3,5,7,9], "y":[2,4,6,8,10], "date":dates, "name":names}

df=DataFrame(dico)
df["month"]=[datetime.strptime(x,'%d/%m/%Y').month for x in dates ]

print df
mpivot=pivot_table(df, values=["x","y"],cols=["month"], rows="name",aggfunc=np.sum)
print mpivot

Upvotes: 2

Views: 4213

Answers (1)

Andy Hayden
Andy Hayden

Reputation: 375515

You could do this once this pivot table has been created:

In [11]: p = pivot_table(df, values=["x","y"], cols=["month"], 
                             rows="name", aggfunc=np.sum)

In [12]: p
Out[12]:
        x           y
month   1   2   3   1   2   3
name
a       1 NaN   7   2 NaN   8
b       3 NaN   9   4 NaN  10
c     NaN   5 NaN NaN   6 NaN

First by switching the column levels, then sorting by columns:

In [13]: p.reorder_levels([1, 0], axis=1).sort_index(axis=1)
Out[13]:
month   1       2       3
        x   y   x   y   x   y
name
a       1   2 NaN NaN   7   8
b       3   4 NaN NaN   9  10
c     NaN NaN   5   6 NaN NaN

Upvotes: 4

Related Questions