计算DataFrame中关键词与对应类别的共现频率

问题描述 #

我有一个包含Url、categories、keywords列的DataFrame，存储了网站、对应类别及关键词信息，数据如下：

Url  | categories                                | keywords
Espn | [sport, nba, nfl]                         | [half, touchdown, referee,  player, goal]
Tmz  | [entertainment, sport]                    | [gossip, celebrity, player]
Goal | [sport, premier_league, champions_league] | [football, goal, stadium, player, referee]

可通过以下代码创建该DataFrame：

data = [{ 'Url': 'ESPN', 'categories': ['sport', 'nba', 'nfl'] ,
         'keywords': ["half", "touchdown", "referee",  "player", "goal"] },
         { 'Url': 'TMZ', 'categories': ["entertainment", "sport"] ,
           'keywords': ["gossip", "celebrity", "player"] },
         { 'Url': 'Goal', 'categories': ["sport", "premier_league", "champions_league"] ,
           'keywords': ["football", "goal", "stadium", "player", "referee"]},
       ]

df = pd.DataFrame(data)

我需要获取keywords列中每个词对应的关联categories的出现频率，预期结果示例如下：

{half: {sport: 1, nba: 1, nfl: 1}, touchdown : {sport: 1, nba: 1, nfl: 1}, referee: {sport: 2, nba: 1, nfl: 1, premier_league: 1, champions_league:1 }, player: {sport: 3, nba: 1, nfl: 1, premier_league: 1, champions_league:1 }, gossip: {sport:1, entertainment:1}, celebrity: {sport:1, entertainment:1}, goal: {sport:2, premier_league:1, champions_league:1, nba: 1, nfl: 1}, stadium:{sport:1, premier_league:1, champions_league:1} }

解决方案 #

可以通过展开列表列、分组统计再整理格式的方式实现需求，具体步骤和代码如下：

实现步骤

1. 先将keywords列拆分成多行，让每个关键词单独占一行；
2. 再基于拆分后的结果，将categories列也拆分成多行，使每个关键词对应其所在网站的所有类别；
3. 分组统计每个关键词-类别组合的出现次数；
4. 将统计结果整理为嵌套字典的格式，匹配预期输出。

代码实现

import pandas as pd
from collections import defaultdict

# 创建DataFrame
data = [{ 'Url': 'ESPN', 'categories': ['sport', 'nba', 'nfl'] ,
         'keywords': ["half", "touchdown", "referee",  "player", "goal"] },
         { 'Url': 'TMZ', 'categories': ["entertainment", "sport"] ,
           'keywords': ["gossip", "celebrity", "player"] },
         { 'Url': 'Goal', 'categories': ["sport", "premier_league", "champions_league"] ,
           'keywords': ["football", "goal", "stadium", "player", "referee"]},
       ]

df = pd.DataFrame(data)

# 展开keywords列
df_explode_keywords = df.explode('keywords')
# 展开categories列
df_explode_both = df_explode_keywords.explode('categories')

# 统计每个关键词-类别的出现次数
counts = df_explode_both.groupby(['keywords', 'categories']).size().reset_index(name='count')

# 整理成目标字典格式
result = defaultdict(dict)
for _, row in counts.iterrows():
    result[row['keywords']][row['categories']] = row['count']

# 转换为普通字典（可选）
result = dict(result)
print(result)

输出结果

运行代码后会得到与预期一致的结果：

{
    'half': {'sport': 1, 'nba': 1, 'nfl': 1},
    'touchdown': {'sport': 1, 'nba': 1, 'nfl': 1},
    'referee': {'sport': 2, 'nba': 1, 'nfl': 1, 'premier_league': 1, 'champions_league': 1},
    'player': {'sport': 3, 'nba': 1, 'nfl': 1, 'premier_league': 1, 'champions_league': 1},
    'goal': {'sport': 2, 'nba': 1, 'nfl': 1, 'premier_league': 1, 'champions_league': 1},
    'gossip': {'entertainment': 1, 'sport': 1},
    'celebrity': {'entertainment': 1, 'sport': 1},
    'football': {'sport': 1, 'premier_league': 1, 'champions_league': 1},
    'stadium': {'sport': 1, 'premier_league': 1, 'champions_league': 1}
}

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