基于演唱会Top3场次年份筛选Top2场馆的正确Pandas实现方法问询

问题拆分 #

• 找出举办演唱会场次最多的前3个年份；
• 基于这些Top年份，找出该时段内每个年份举办演唱会场次最多的2个场馆

数据集 #

首先需要将字典转换为pandas DataFrame：

import pandas as pd

df_dict = {"year":["2017","2018","2018","2018","2018","2018","2019","2019","2019",'2019',"2019","2020","2020","2020","2020","2020","2021","2021","2021","2021","2021","2022",'2022','2023'],
        "venue":["V2","V1","V2","V2","V2","V2","V1","V1","V2","V2","V5","V1","V2","V2","V3","V3","V3","V3","V4","V4","V5","V1","V6","V3"]}
df = pd.DataFrame(df_dict)

问题1的实现与结果 #

统计各年份演唱会场次，并筛选出场次最多的年份：

# 按年份分组统计场次，并重命名列名更直观
stadium_count_per_year = df.groupby('year').agg({'venue':'count'}).rename(columns={'venue':'concert_count'})
# 获取场次最大值
max_concert_count = stadium_count_per_year['concert_count'].max()
# 筛选出等于最大值的年份
highest_concert_yr = stadium_count_per_year[stadium_count_per_year["concert_count"] == max_concert_count].index.tolist()

结果：2018、2019、2020、2021年为场次最多的年份（均为5场）

问题2的正确实现 #

你之前的groupby("venue","year")写法错误，pandas的groupby需要传入列名列表。正确步骤是先筛选Top年份的数据，再按年份+场馆分组统计，最后提取每个年份内场次最多的场馆。

完整代码

# 筛选出Top年份的所有数据
top_year_subset = df[df['year'].isin(highest_concert_yr)]

# 按年份和场馆分组，统计每个场馆在对应年份的场次
venue_year_count = top_year_subset.groupby(['year', 'venue']).agg({'venue':'count'}).rename(columns={'venue':'concert_count'}).reset_index()

# 对每个年份，按场次降序排序，取前2个场馆
top_venues = venue_year_count.groupby('year').apply(
    lambda group: group.nlargest(2, 'concert_count')[['year', 'venue']]
).reset_index(drop=True)

# 打印结果
print(top_venues)

输出结果

year venue
0  2018    V2
1  2018    V1
2  2019    V1
3  2019    V2
4  2020    V2
5  2020    V3
6  2021    V3
7  2021    V4

如果只需要每个年份场次最多的1个场馆（与你给出的示例输出匹配），只需将nlargest(2, 'concert_count')改为nlargest(1, 'concert_count')，输出为：

year venue
0  2018    V2
1  2019    V1
2  2020    V2
3  2021    V3

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