Add pivot table in excel with python

Question

I am trying to add a pivot table in excel using python script with pandas but not able to do so. I won't to count number of missed and met entries for each priority.

Excel script:

import pandas as pd
df = pd.DataFrame({'Priority': ['p1','p2','p3','p2','p3'],'SLA': ['Met','Missed','Missed','Met','Missed']})

Excel data:

Priority	SLA
p1	Met
p2	Missed
p3	Missed
p2	Missed
p3	Missed

desired output:

Priority	Met	Missed
p1	1	0
p2	1	1
p3	0	2

I tried different combination\approach with

table = pd.pivot_table(df,index='Priority',columns=['SLA'])

but couldn't get it right. I am struggling hard for this. I am trying this first time.

Mohammad Ayoub · Accepted Answer · 2022-03-03 17:13:04Z

1

We need to understand how pandas.DataFrame.pivot_table works in order to solve this problem.

First, it has three different inputs:

Values -> the values on which the aggregation happens. In this case, it is SLA.
Columns -> the new columns that are to be created. In this case, it's SLA's values.
Index -> the rows that are to be kept. In this case, it's Priority.

Let's convert this into code.

df.pivot_table(
  # SLA's values as the columns
  columns=df['SLA'].values, 

  # Priority as the rows.
  index=['Priority'], 

  # SLA's values as the values to be aggregated upon (counted).
  values=['SLA'], 

  # Count is our aggregate function
  aggfunc='count'
).fillna(0).astype('int') # Then we fill NaN values with 0, and convert the df -> int

answered Mar 3, 2022 at 17:13

Mohammad Ayoub

3792 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

A Jain Over a year ago

Thanks @Mohammad Ayoub, this solution works as required.

Collectives™ on Stack Overflow

Add pivot table in excel with python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related