Pandas pivot table for multiple columns at once

user_5061557 0
Viewed 74479

Let's say I have a DataFrame:

   nj  ptype  wd  wpt
0   2      1   2    1
1   3      2   1    2
2   1      1   3    1
3   2      2   3    3
4   3      1   2    2

I would like to aggregate this data using ptype as the index like so:

             nj             wd            wpt
       1.0  2.0  3.0  1.0  2.0  3.0  1.0  2.0  3.0
ptype    
    1    1    1    1    0    2    1    2    1    0
    2    0    1    1    1    0    1    0    1    1

You could build each one of the top level columns for the final value by creating a pivot table with aggfunc='count' and then concatenating them all, like so:

nj = df.pivot_table(index='ptype', columns='nj', aggfunc='count').ix[:, 'wd']
wpt = df.pivot_table(index='ptype', columns='wpt', aggfunc='count').ix[:, 'wd']
wd = df.pivot_table(index='ptype', columns='wd', aggfunc='count').ix[:, 'nj']
out = pd.concat([nj, wd, wpt], axis=1, keys=['nj', 'wd', 'wpt']).fillna(0)
out.columns.names = [None, None]
print(out)
        nj             wd            wpt
         1    2    3    1    2    3    1    2    3
ptype
1      1.0  1.0  1.0  0.0  2.0  1.0  2.0  1.0  0.0
2      0.0  1.0  1.0  1.0  0.0  1.0  0.0  1.0  1.0

But I really dislike this and it feels wrong. I would like to know if there is a way to do this in a simpler fashion preferably with a builtin method. Thanks in advance!

multi-index pandas pivot-table python
3 Answers

An easier solution is 

employee.pivot_table(index= ‘Title’, values= “Salary”, aggfunc= [np.mean, np.median, min, max, np.std], fill_value=0)

In this case, for the salary column we are using different aggregate functions

user_99882820
Related