如何对含列表结构的Pandas DataFrame列做Groupby，找出Top3高平均ROI制片方

现有如下Pandas DataFrame，其中Producers列的条目为列表结构：

import pandas as pd
import numpy as np

table = pd.DataFrame({
    'Movie_title':['Hot Tub Time Machine 2','The Princess Diaries 2: Royal Engagement','Whiplash','Kahaani','마린보이'],
    'Producers':[['Andrew Panay','Jason Blum'],['Whitney Houston', 'Mario Iscovich', 'Michel Litvak'],['David Lancaster', 'Michel Litvak', 'Jason Blum', 'Helen Estabrook'],['Sujoy Ghosh'],[]],
    'Directors':[['Steve Pink'],['Garry Marshall'],['Damien Chazelle'],['Sujoy Ghosh'],['Jong-seok Yoon']],
    'ROI':[-12.038207142857143,137.8735875,296.72727272727275,1233.3333333333333,-76.14607902735563]
})

需求是找出平均ROI最高的前3位制片方，但直接执行以下groupby代码会报错：

table.groupby('Producers')[['Movie Title','ROI','Directors']].mean('ROI')

报错原因 #

• 列表属于不可哈希类型，无法直接作为groupby的分组键；
• 单条电影记录对应多个制片方，直接分组无法将每个制片方与对应电影的ROI正确关联。

解决步骤 #

1. 展开Producers列 #

使用explode方法将列表中的每个制片方拆分为单独行，同时保留对应电影的其他信息，之后过滤掉空列表生成的NaN值：

# 展开Producers列，过滤空值
expanded_table = table.explode('Producers').dropna(subset=['Producers'])

2. 分组计算平均ROI #

按Producers分组，仅对ROI列计算均值（Movie_title和Directors列因对应多部电影，均值无实际意义，无需保留）：

producer_roi = expanded_table.groupby('Producers')['ROI'].mean().reset_index()

3. 排序取前3位 #

按平均ROI降序排序，提取前3条记录：

top3_producers = producer_roi.sort_values(by='ROI', ascending=False).head(3)

完整执行代码 #

import pandas as pd
import numpy as np

table = pd.DataFrame({
    'Movie_title':['Hot Tub Time Machine 2','The Princess Diaries 2: Royal Engagement','Whiplash','Kahaani','마린보이'],
    'Producers':[['Andrew Panay','Jason Blum'],['Whitney Houston', 'Mario Iscovich', 'Michel Litvak'],['David Lancaster', 'Michel Litvak', 'Jason Blum', 'Helen Estabrook'],['Sujoy Ghosh'],[]],
    'Directors':[['Steve Pink'],['Garry Marshall'],['Damien Chazelle'],['Sujoy Ghosh'],['Jong-seok Yoon']],
    'ROI':[-12.038207142857143,137.8735875,296.72727272727275,1233.3333333333333,-76.14607902735563]
})

# 展开制片方列并过滤空值
expanded_table = table.explode('Producers').dropna(subset=['Producers'])
# 分组计算平均ROI
producer_roi = expanded_table.groupby('Producers')['ROI'].mean().reset_index()
# 排序取前3
top3_producers = producer_roi.sort_values(by='ROI', ascending=False).head(3)

print(top3_producers)

执行后输出结果：

Producers          ROI
3      Sujoy Ghosh  1233.333333
2  Michel Litvak    217.300430
1    Mario Iscovich 137.873588

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