Skip to main content
 首页 » 编程设计

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

2025年01月19日10sky-heaven

示例数据:

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

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

请您参考如下方法:

您需要先按 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")