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

user_2998077 0
Viewed 58

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? Thank you.

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)
pandas dataframe pivot
1 Answers

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
user_42384080
Related