表：聚合函数上的过滤器

Question

我试图将一个条件传递给聚合函数到表，但我无法确定如何将该条件传递给aggfunc。我有一个数据表，它被转换为df

输入表数据：

^{tb1}$

我希望获得col7数据的最大值，但仅当最大值大于100时。如果任何col7数据大于用户定义的标准，则无论数据是否满足标准，都需要填充所有其他列数据

尝试了以下操作：

columns = ['col1', 'col2', 'col3', 'col4', 'col5', 'col6', 'col7']

df = pd.DataFrame({
    'col1': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16],
    'col2': ['test1', 'test1', 'test1', 'test1', 'test2', 'test2', 'test2',
             'test2', 'test3', 'test3', 'test3', 'test3', 'test4', 'test5',
             'test1', 'test1'],
    'col3': ['t1', 't1', 't1', 't1', 't1', 't1', 't1', 't1', 't1', 't1', 't1',
             't1', 't1', 't1', 't1', 't1'],
    'col4': ['Dummy1', 'Dummy2', 'Dummy3', 'Dummy4', 'Dummy1', 'Dummy2',
             'Dummy3', 'Dummy4', 'Dummy1', 'Dummy2', 'Dummy3', 'Dummy5',
             'Dummy2', 'Dummy6', 'Dummy1', 'Dummy1'],
    'col5': ['result1', 'result2', 'result3', 'result4', 'result1', 'result2',
             'result3', 'result4', 'result1', 'result2', 'result3', 'result4',
             'result2', 'result1', 'result2', 'result6'],
    'col6': [10, 20, 30, 40, 10, 20, 30, 40, 10, 20, 30, 50, 20, 100, 10, 10],
    'col7': [100.2, 101.2, 102.3, 101.4, 100.0, 103.0, 104.0, 105.0, 102.0,
             87.0, 107.0, 110.2, 120.0, 88.0, 106.2, 101.1]
})

res=df.pivot_table(values = 'col7', index = ['col4', 'col5', 'col6'], columns = ['col2'], fill_value = '', aggfunc = 'max' >= 100)

TypeError: '>=' not supported between instances of 'str' and 'int'

输出应如下所示：

不带col5的最大枢轴输出：

^{tb2}$

最大枢轴输出，包括第5列：

^{tb3}$

非常感谢您的指导

谢谢

Answer 1

或者您可以尝试：

res = df.assign(col7 = df.col7.where(df.col7 > 100)).pivot_table(values='col7', index=['col4', 'col5', 'col6'],
                     columns=['col2'], aggfunc='max', fill_value= '')

Answer 2

无法通过>=（aggfunc = 'max' >= 100）将单词“max”与100进行比较：

我建议不要将填充值设置为字符串，屏蔽数据帧，以消除不需要的行，然后通过^{}替换为空字符串：

columns = ['col1', 'col2', 'col3', 'col4', 'col5', 'col6', 'col7']
res = df.pivot_table(values='col7', index=['col4', 'col5', 'col6'],
                     columns=['col2'], aggfunc='max')

col2                 test1  test2  test3  test4  test5
col4   col5    col6                                   
Dummy1 result1 10    102.2  100.0  102.0    NaN    NaN
       result2 10    106.2    NaN    NaN    NaN    NaN
       result6 10    101.1    NaN    NaN    NaN    NaN
Dummy2 result2 20    101.2  103.0   87.0  120.0    NaN
Dummy3 result3 30    102.3  104.0  107.0    NaN    NaN
Dummy4 result4 40    101.4  105.0    NaN    NaN    NaN
Dummy5 result4 50      NaN    NaN  110.2    NaN    NaN
Dummy6 result1 100     NaN    NaN    NaN    NaN   88.0

任何值的掩码，其中^{}res >= 100和^{}：

res = res[(res >= 100).any(1)].fillna('')

col2                 test1  test2  test3  test4 test5
col4   col5    col6                                  
Dummy1 result1 10    102.2  100.0  102.0             
       result2 10    106.2                           
       result6 10    101.1                           
Dummy2 result2 20    101.2  103.0   87.0  120.0      
Dummy3 result3 30    102.3  104.0  107.0             
Dummy4 result4 40    101.4  105.0                    
Dummy5 result4 50                  110.2                       

可选^{}清除多索引，可选^{}清除轴名称：

res[(res >= 100).any(1)].fillna('').reset_index().rename_axis(None, axis=1)

     col4     col5  col6  test1  test2  test3  test4 test5
0  Dummy1  result1    10  102.2  100.0  102.0             
1  Dummy1  result2    10  106.2                           
2  Dummy1  result6    10  101.1                           
3  Dummy2  result2    20  101.2  103.0   87.0  120.0      
4  Dummy3  result3    30  102.3  104.0  107.0             
5  Dummy4  result4    40  101.4  105.0                    
6  Dummy5  result4    50                110.2             

---

完整的工作示例：

import pandas as pd

df = pd.DataFrame({
    'col1': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16],
    'col2': ['test1', 'test1', 'test1', 'test1', 'test2', 'test2', 'test2',
             'test2', 'test3', 'test3', 'test3', 'test3', 'test4', 'test5',
             'test1', 'test1'],
    'col3': ['t1', 't1', 't1', 't1', 't1', 't1', 't1', 't1', 't1', 't1', 't1',
             't1', 't1', 't1', 't1', 't1'],
    'col4': ['Dummy1', 'Dummy2', 'Dummy3', 'Dummy4', 'Dummy1', 'Dummy2',
             'Dummy3', 'Dummy4', 'Dummy1', 'Dummy2', 'Dummy3', 'Dummy5',
             'Dummy2', 'Dummy6', 'Dummy1', 'Dummy1'],
    'col5': ['result1', 'result2', 'result3', 'result4', 'result1', 'result2',
             'result3', 'result4', 'result1', 'result2', 'result3', 'result4',
             'result2', 'result1', 'result2', 'result6'],
    'col6': [10, 20, 30, 40, 10, 20, 30, 40, 10, 20, 30, 50, 20, 100, 10, 10],
    'col7': [102.2, 101.2, 102.3, 101.4, 100.0, 103.0, 104.0, 105.0, 102.0,
             87.0, 107.0, 110.2, 120.0, 88.0, 106.2, 101.1]
})

columns = ['col1', 'col2', 'col3', 'col4', 'col5', 'col6', 'col7']
res = df.pivot_table(values='col7', index=['col4', 'col5', 'col6'],
                     columns=['col2'], aggfunc='max')
res = (
    res[(res >= 100).any(1)].fillna('').reset_index().rename_axis(None, axis=1)
)
print(res)

---

要获取不带col5的值，请将其从pivot_table的index中删除：

columns = ['col1', 'col2', 'col3', 'col4', 'col5', 'col6', 'col7']
res = df.pivot_table(values='col7', index=['col4', 'col6'],
                     columns=['col2'], aggfunc='max')
res = (
    res[(res >= 100).any(1)].fillna('').reset_index().rename_axis(None, axis=1)
)

     col4  col6  test1  test2  test3  test4 test5
0  Dummy1    10  106.2  100.0  102.0             
1  Dummy2    20  101.2  103.0   87.0  120.0      
2  Dummy3    30  102.3  104.0  107.0             
3  Dummy4    40  101.4  105.0                    
4  Dummy5    50                110.2