为什么Python Pandas的pd.crosstab未输出预期结果？

问题背景 #

我有两个DataFrame：

• df1包含KPI和Context列，数据示例：

KPI                                                 Context 
0   Does the company have a policy in place to man...   Anti-Bribery Policy\nBroadridge does not toler...   
1   Does the company have a supplier code of conduct?   Vendor Code of Conduct Our vendors play an imp...   
2   Does the company have a grievance/complaint ha...   If you ever have a question or wish to report ...   
3   Does the company have a human rights policy ?   Human Rights Statement of Commitment Broadridg...   
4   Does the company have a policies consistent wi...   Anti-Bribery Policy\nBroadridge does not toler...

• df2仅含Keyword列，数据示例：

Keyword
0   1.5 degree
1   1.5°
2   2 degree
3   2°
4   accident

我需要生成新DataFrame，统计每个KPI对应的Context中，df2各关键词的出现次数。尝试用pd.crosstab()但结果不符合预期。

尝试的代码 #

new_df = df1.explode('Context')
new_df1 = df2.explode('Keyword')
new_df = pd.crosstab(new_df['KPI'], new_df1['Keyword'], values=new_df['Context'], aggfunc='count').reset_index().rename_axis(columns=None)
print(new_df.head())

当前错误输出 #

KPI                                                       1.5 degree  1.5°  
0  Does the Supplier code of conduct cover one or...         NaN   NaN   
1  Does the companies have sites/operations locat...         NaN   NaN   
2  Does the company have a due diligence process ...         NaN   NaN   
3  Does the company have a grievance/complaint ha...         NaN   NaN   
4  Does the company have a grievance/complaint ha...         NaN   NaN   

   2 degree  2°          accident  
0       NaN NaN           NaN              
1       NaN NaN           NaN             
2       NaN NaN           NaN          
3       1.0 NaN           NaN              
4       NaN NaN           NaN

期望输出 #

0   KPI                                            1.5 degree  1.5°    2 degree    2°  accident
1   Does the company have a policy in place to man... 44        2      3          5        9

问题原因 #

你的代码存在两个核心错误：

1. 误用explode()：explode()是用来拆分可迭代类型的列（比如列表），但你的Context和Keyword都是单一字符串，拆分后没有任何变化，属于无效操作。
2. 错误使用pd.crosstab()：这个函数是用来统计两个分类变量的交叉频数，但你并没有将每个KPI的Context与关键词做匹配统计，反而把两个无关的DataFrame硬凑交叉，自然无法得到正确的关键词出现次数。

正确实现方法 #

核心逻辑是：对每个KPI对应的Context文本，遍历所有关键词，统计每个关键词的出现次数，再将结果整合成目标格式的DataFrame。

方法1：逐行处理（简单直观） #

import pandas as pd

# 提取所有关键词列表
keywords = df2['Keyword'].tolist()

# 定义统计函数：计算单个文本中各关键词的出现次数
def count_keywords(context):
    return pd.Series({kw: context.count(kw) for kw in keywords})

# 对df1每行应用统计函数，合并结果
result_df = df1.join(df1['Context'].apply(count_keywords)).fillna(0).astype(int)

print(result_df)

方法2：向量化处理（高效适合大数据） #

import pandas as pd
import numpy as np

# 转为numpy数组，利用广播机制批量统计
contexts = df1['Context'].to_numpy()
keywords = df2['Keyword'].to_numpy()

# 批量计算每个关键词在所有Context中的出现次数
counts = np.array([np.char.count(contexts, kw) for kw in keywords]).T

# 构造结果DataFrame
result_df = pd.DataFrame(counts, columns=keywords)
result_df.insert(0, 'KPI', df1['KPI'])

print(result_df)

额外说明 #

如果需要忽略大小写匹配，可以在统计前统一转为小写：

# 方法1中修改统计逻辑
def count_keywords(context):
    context_lower = context.lower()
    return pd.Series({kw: context_lower.count(kw.lower()) for kw in keywords})

# 方法2中修改数组
contexts = df1['Context'].str.lower().to_numpy()
keywords = df2['Keyword'].str.lower().to_numpy()

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