CODEWITHSUNDEEP
pythonpandaspivot-table
Jerry Smith
Jerry Smith

Reputation: 51

Pandas pivot table with hierarchical columns

I need to create a pivot table. My data frame has the following structure:

data structure

print (df)
  company team person project unit start   end  num
0     ABC  Dev   John     pr1   BE  date  date    3
1     ABC  Dev    Tim     pr1   FE  date  date    4
2     ABC  Dev  James     pr2   FE  date  date    3

I tried to use the following pandas functions:

table = pd.pivot_table(df,
                index=["company","team","person"],
                columns=["project", 'unit'],
                values=["start","end","num"],
                aggfunc={"start": np.min,
                       "end": np.max ,
                       "num": np.sum},
                fill_value=0)
table.columns = table.columns.swaplevel(2, 0).swaplevel(1, 0)

Data were transformed into following pivot table:

pivot

And I ended up with desired data outcome, but formating is an issue. I’d like the data frame to be in the following format:

desired ourcome

Is there a way to transform columns into hierarchical columns with the pandas pivot table function?

Upvotes: 1

Views: 2481

Answers (1)

jezrael
jezrael

Reputation: 862511

Use DataFrame.reorder_levels with DataFrame.sort_index and change order by DataFrame.reindex with list:

table = pd.pivot_table(df,
                index=["company","team","person"],
                columns=["project", 'unit'],
                values=["start","end","num"],
                aggfunc={"start": np.min,
                       "end": np.max ,
                       "num": np.sum},
                fill_value=0)

vals = ['start','end','num']
table = table.reorder_levels([1,2,0], axis=1).sort_index(axis=1).reindex(vals, level=2, axis=1)
print (table)
project               pr1                             pr2          
unit                   BE              FE              FE          
                    start   end num start   end num start   end num
company team person                                                
ABC     Dev  James      0     0   0     0     0   0  date  date   3
             John    date  date   3     0     0   0     0     0   0
             Tim        0     0   0  date  date   4     0     0   0

Upvotes: 5

Related Questions