Sample data:
| Plan | Run | Status | Tested On |
|------|----------|----------------|------------|
| P0 | Agent | Passed | 2018-08-11 |
| | Customer | Failed | 2018-08-01 |
| P1 | A | Passed | 2018-08-02 |
| | B | Untested | 2018-08-11 |
| P2 | C | Not Applicable | 2018-08-05 |
My script:
msft = pd.read_excel(
"C:\\Users\\gomat\\Desktop\\Py TR\\week_06(1).xlsx")
msft = pd.pivot_table(
msft, index = ['Plan'],
values = ['Passed', 'Failed', 'Blocked', 'Not_Implemented',
'Clarification Opened', 'In Progress', 'Not Applicable' ],
columns =['Plan'],aggfunc = 'count')
msft.to_excel("C:\\Users\\gomat\\Desktop\\Py TR\\week_06.xlsx")
Actual result:
C:\Program Files (x86)\Python36-32\lib\site-packages\pandas\core\reshape\pivot.py in pivot_table(data, values, index, columns, aggfunc, fill_value, margins, dropna, margins_name)
56 for i in values:
57 if i not in data:
---> 58 raise KeyError(i)
59
60 to_filter = []
KeyError: 'Passed'
` Expected result: I need the count of value in the status
You need first filter by boolean indexing or query:
msft = pd.read_excel("C:\\Users\\gomat\\Desktop\\Py TR\\week_06(1).xlsx")
L = ['Passed', 'Failed', 'Blocked', 'Not_Implemented', 'Clarification Opened',
'In Progress', 'Not Applicable' ]
msft = msft[msft['Status'].isin(L)]
#alternative for filtering
#msft = msft.query('Status in @L')
msft = msft.groupby(['Plan', 'Status']).size().reset_index(name='count')
print (msft)
Plan Status count
0 P0 Failed 1
1 P0 Passed 1
2 P1 Passed 1
3 P2 Not Applicable 1
msft.to_excel("C:\\Users\\gomat\\Desktop\\Py TR\\week_06.xlsx")