Pandas透视表Lambda函数合并列值异常问题求助

示例数据 #

import pandas as pd

df = pd.DataFrame({'API': ['16101030580000', '16101030580000', '16129057600000','16013006300000'], 
                   'Date': ['0000/00/00','6/15/2007', '5/25/2020', '7/31/2014'],
                   'Annual_Oil':[300,'nan',150, 360],
                   'State':['KY','None', 'None', 'KY']})

需求与问题 #

需要按API字段创建透视表，合并同一API下的非唯一值，但要排除以下无效值：

list_none = ['none', 'nan', 'NAN','None', '0000/00/00','000']

当前使用的透视表代码未过滤掉这些无效值，结果仍包含0000/00/00、nan、None等内容。

问题原因 #

原代码中的lambda函数判断逻辑错误：x not in list_none是将整个Series对象与列表比较，而非逐个检查元素是否属于无效值列表，导致过滤条件从未触发。

解决方案 #

方案1：先清理数据再生成透视表（推荐） #

先将所有无效值替换为pd.NA，再在聚合时过滤掉空值：

import pandas as pd

df = pd.DataFrame({'API': ['16101030580000', '16101030580000', '16129057600000','16013006300000'], 
                   'Date': ['0000/00/00','6/15/2007', '5/25/2020', '7/31/2014'],
                   'Annual_Oil':[300,'nan',150, 360],
                   'State':['KY','None', 'None', 'KY']})

list_none = ['none', 'nan', 'NAN','None', '0000/00/00','000']

# 替换无效值为pd.NA
df = df.replace(list_none, pd.NA)

# 生成透视表，聚合时过滤空值并合并唯一值
df1 = pd.pivot_table(df, index='API', 
                     aggfunc=lambda x: ','.join(x.dropna().unique().astype(str)), 
                     sort=False)

print(df1)

输出结果：

Date Annual_Oil State
API                                 
16101030580000  6/15/2007       300    KY
16129057600000  5/25/2020       150      
16013006300000  7/31/2014       360    KY

方案2：直接修改透视表的聚合函数 #

在lambda函数中逐个过滤无效值，再合并剩余的唯一值：

import pandas as pd

df = pd.DataFrame({'API': ['16101030580000', '16101030580000', '16129057600000','16013006300000'], 
                   'Date': ['0000/00/00','6/15/2007', '5/25/2020', '7/31/2014'],
                   'Annual_Oil':[300,'nan',150, 360],
                   'State':['KY','None', 'None', 'KY']})

list_none = {'none', 'nan', 'NAN','None', '0000/00/00','000'}  # 用集合提升查找效率
df1 = pd.pivot_table(df, index='API',
                     aggfunc=lambda x: ','.join([str(val) for val in x.unique() if str(val) not in list_none]),
                     sort=False)

print(df1)

输出结果与方案1一致。

说明 #

方案1的优势在于先统一清理数据，后续其他操作也能直接使用清理后的DataFrame，逻辑更清晰；方案2适合仅在透视表中临时过滤的场景。

内容的提问来源于stack exchange，提问作者Jay Antonio Oliver