ryzhiy - 10 months ago 51

Python Question

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

Answer Source

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
```