使用pandas DataFrame pivot_table函数时触发KeyError及ValueError如何解决

• 原因1：透视表生成了多级列索引，无法直接访问reach列
  你调用pivot_table时values参数传入了列表['reach']，pandas默认会生成两级列索引：第一级为字段名reach，第二级为聚合函数名sum。在reach函数中尝试用row['reach']访问字段时找不到对应列，触发初始的KeyError。
• 原因2：空透视表赋值失败
  遍历月份时，部分月份经过rslt_df[rslt_df.activity_month_name == x]筛选后没有符合条件的数据，生成的pivot1是空DataFrame。对空表执行apply得到的是空序列，赋值给新列reach_type时长度不匹配，触发后续的ValueError: Wrong number of items passed 0, placement implies 1。

最简修复代码 #

直接调整透视表写法，增加空表判断即可：

import pandas as pd

# 前面计算reach、engage、筛选rslt_df的代码保持不变
df['reach'] = df['aim_reached_flag'] + df['email_reached_flag'] + df['rep_reached_flag'] + df['sp_reached_flag'] + df['third_party_reached_flag'] + df['display_reached_flag']
df['engage'] = df['aim_engaged_flag'] + df['email_engaged_flag'] + df['rep_engaged_flag'] + df['sp_engaged_flag'] + df['third_party_engaged_flag'] + df['display_engaged_flag']
rslt_df = df[df['target_audience'] == 'Yes']
mnth = rslt_df.activity_month_name.unique()

def reach(row):
    if row['reach'] > 0 and row['reach'] < 100:
        reach_t = 'reach1'
    elif (row['reach'] > 99 and row['reach'] < 1000 and row['reach']%100 == 0):
        reach_t = 'reach1'
    elif (row['reach'] > 999 and row['reach'] < 10000 and row['reach']%1000 == 0):
        reach_t = 'reach1'
    elif (row['reach'] > 9999 and row['reach'] < 100000 and row['reach']%10000 == 0):
        reach_t = 'reach1'
    elif (row['reach'] > 99999 and row['reach'] < 1000000 and row['reach']%10000 == 0):
        reach_t = 'reach1'
    elif (row['reach'] > 999999 and row['reach'] < 10000000 and row['reach']%10000 == 0):
        reach_t = 'reach1'
    elif row['reach'] != 0:
        reach_t ='reach2'
    else:
        reach_t = 'not_reached'
    return reach_t

rows = []
for x in mnth:
    # 调整1：values传字符串而非列表，避免生成多级索引
    pivot1 = rslt_df[rslt_df.activity_month_name == x].pivot_table(index=['hcp_mdm_id'], values='reach', aggfunc='sum')
    # 调整2：增加空表判断，无数据时跳过
    if pivot1.empty:
        continue
    pivot1['reach_type'] = pivot1.apply(reach, axis=1)
    cnt1 = len(pivot1[pivot1['reach_type'].str.contains('reach1')])
    cnt2 = len(pivot1[pivot1['reach_type'].str.contains('reach2')])
    rows.append([x, cnt1, cnt2])

优化建议（可选） #

可以用向量化的pd.cut或者np.where条件判断代替apply操作，大幅提升大数据量下的运行效率，避免逐行遍历的性能损耗。

内容的提问来源于stack exchange，提问作者slayer1996