CSstudent
CSstudent

Reputation: 768

How to make a pivot table by month and sum hours using pandas?

I'm trying to make a pivot table to show the sum of time worked separated by month. I am successful at making a pivot table but I'm stuck trying to figure out how to separate on month as well.

This is going to be exported to an excel file that is given to clients to show how much work we did for over x months and to show that we are fulfilling our agreement of how many x hours of work we are providing for them.

I am using python pandas to house my data. This is my attempt to get a pivot table.

This is the code I'm currently using: pivot = WorkTypeT.pivot_table(index=['Agreement','work_type'], values=['MONTH','hours_actual'], aggfunc=['sum'])

This is the table:

      Agreement     work_type         hours_actual   MONTH
0     Proactive     Client Management 5.25           May 
0     Proactive     Managed Services  2.25           May 
0     Proactive     Onsite            15.50          May 
0     Proactive     Remote            9.25           May 
0     Proactive     Client Management 5.00           June 
0     Proactive     Managed Services  2.25           June 
0     Proactive     Onsite            15.00          June 
0     Proactive     Remote            9.25           June 

What the output currently looks like

                                        sum
                                        hours_actual
Agreement  work_type
Proactive  Client Management            10.25 
           Managed Services             4.50 
           Onsite                       30.50 
           Remote                       18.50 

How do I change it so the current output would look like or similar to

                                        MONTH
                                        hours_actual
                                        May      June
Agreement  work_type
Proactive  Client Management            5.25     5.00
           Managed Services             2.25     2.25
           Onsite                       15.50    15.00
           Remote                       9.25     9.25

Upvotes: 1

Views: 1170

Answers (2)

Benoit Drogou
Benoit Drogou

Reputation: 969

Try this out:

pivot = pd.pivot_table(WorkTypeT, values='hours_actual', index=['Agreement', 'work_type'], columns=['MONTH'], aggfunc='sum')

Upvotes: 1

galaxyan
galaxyan

Reputation: 6121

pivot_table could give the result

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.pivot_table.html

pd.pivot_table(df, values='hours_actual', index=['Agreement', 'work_type'], columns=['MONTH'], aggfunc=np.sum, fill_value=0)



MONTH   June    May
Agreement   work_type       
Proactive   Client Management   5.00    5.25
Managed Services    2.25    2.25
Onsite  15.00   15.50
Remote  9.25    9.25

Upvotes: 3

Related Questions