Add pivot table in excel with python

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 combinationapproach 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.

Answer

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

First, it has three different inputs:

1. Values -> the values on which the aggregation happens. In this case, it is SLA.
2. Columns -> the new columns that are to be created. In this case, it’s SLA’s values.
3. 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