Search code examples
pandasdataframepivot

Pandas pivot table to show equal number of rows for each entry

A data frame records staffs’ scores along 3 months (1987-08, 1987-09, 1987-10), some of the month details are missing, some of the months have several different records. I want to achieve a pivot table to all 3 months for each stuff, like:

enter image description here

I've tried below however it is not right.

What would be the right way to write it?

import pandas as pd
from io import StringIO

csvfile = StringIO("""
Town,Department,Staff,Month,Project,Score
East,Produce,Ethan,1987-08,A814,27
East,Produce,Ethan,1987-09,A848,27
East,Produce,Ethan,1987-10,A736,29
East,Meat,Harry,1987-08,A813,26
East,Seafood,Lucas,1987-08,A664,23
East,Seafood,Lucas,1987-08,A590,42
East,Seafood,Lucas,1987-08,A778,19
East,Seafood,Lucas,1987-10,A616,30
East,Beer and Wine,Pia,1987-08,A718,17
East,Beer and Wine,Pia,1987-09,A841,10
East,Beer and Wine,Pia,1987-10,A637,14
East,Health and Beauty,Amy,1987-08,A614,30
East,Health and Beauty,Amy,1987-08,A797,64
East,Health and Beauty,Amy,1987-08,A576,21
East,Health and Beauty,Amy,1987-10,A672,18
South,Produce,Sophia,1987-08,A768,12
South,Produce,Sophia,1987-10,A811,21
South,Meat,Ava,1987-08,A741,20
South,Meat,Ava,1987-09,A698,22
South,Meat,Ava,1987-10,A651,12
South,Health and Beauty,Benjamin,1987-08,A661,22
South,Health and Beauty,Benjamin,1987-09,A837,11""")

df = pd.read_csv(csvfile, sep = ',', engine='python')
df['Month'] = df['Month'].astype('category')

pt = pd.pivot_table(df, values='Score', index=['Town','Department', 'Staff', 'Month'], aggfunc='sum', fill_value=0)
m = pd.MultiIndex.from_product([df['Town'].unique(),df['Department'].unique(),df['Staff'].unique(),df['Month'].unique()], names=pt.index.names)
pt = pt.reindex(m)

print(pt)
Solution

  • Try pivot_table:

    df.pivot_table(index=['Town','Department','Staff'], 
               columns=['Month'], values='Score',
               aggfunc='sum', fill_value=0
              ).stack(dropna=False).reset_index(name='Sum of Score')

    Output:

         Town         Department     Staff    Month  Sum of Score
0    East      Beer and Wine       Pia  1987-08            17
1    East      Beer and Wine       Pia  1987-09            10
2    East      Beer and Wine       Pia  1987-10            14
3    East  Health and Beauty       Amy  1987-08           115
4    East  Health and Beauty       Amy  1987-09             0
5    East  Health and Beauty       Amy  1987-10            18
6    East               Meat     Harry  1987-08            26
7    East               Meat     Harry  1987-09             0
8    East               Meat     Harry  1987-10             0
9    East            Produce     Ethan  1987-08            27
10   East            Produce     Ethan  1987-09            27
11   East            Produce     Ethan  1987-10            29
12   East            Seafood     Lucas  1987-08            84
13   East            Seafood     Lucas  1987-09             0
14   East            Seafood     Lucas  1987-10            30
15  South  Health and Beauty  Benjamin  1987-08            22
16  South  Health and Beauty  Benjamin  1987-09            11
17  South  Health and Beauty  Benjamin  1987-10             0
18  South               Meat       Ava  1987-08            20
19  South               Meat       Ava  1987-09            22
20  South               Meat       Ava  1987-10            12
21  South            Produce    Sophia  1987-08            12
22  South            Produce    Sophia  1987-09             0
23  South            Produce    Sophia  1987-10            21