python - 如何使用 pandas.pivot_table 获取 status 中的值？

Question

示例数据:

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

我的脚本:

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")

实际结果:

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'

`预期结果:我需要状态中的值计数

Answer 1

您需要先按 boolean indexing 过滤或 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")