Python处理CSV/Excel电影数据：按年份提取高热度电影，需补充title字段

Hey there! Looks like you're close to getting the exact DataFrame you want—you just need to adjust how you're grouping your data to keep the title tied to the highest popularity value per year. The common pitfall here is using a simple aggregation (like max()) which won't map the correct title to the top popularity score. Let's fix this with two reliable approaches:

方法1：获取每个年份热度最高的单部电影 #

如果你的需求是每个年份只取热度最高的那一部（即使有并列，取第一个出现的），可以用idxmax()来定位对应行的索引，这样就能完整提取包含title的整行数据：

import pandas as pd

# 读取你的Excel数据集（如果是CSV文件，替换成pd.read_csv("your_movies.csv")）
df = pd.read_excel("movies_dataset.xlsx")

# 可选：清理缺失值（避免处理空的年份、标题或热度数据）
df = df.dropna(subset=["year", "title", "popularity"])

# 获取每个年份中热度最高的行的索引
top_pop_indices = df.groupby("year")["popularity"].idxmax()

# 根据索引提取对应行，生成包含year/title/popularity的目标DataFrame
top_movies_per_year = df.loc[top_pop_indices, ["year", "title", "popularity"]].sort_values("year")

# 按年份打印结果
for _, row in top_movies_per_year.iterrows():
    print(f"年份 {row['year']}: 热门电影《{row['title']}》，热度评分 {row['popularity']:.2f}")

方法2：保留同一年份热度并列最高的所有电影 #

如果有多个电影在同一年份热度相同且都是最高值，用排名法可以保留所有并列的电影：

import pandas as pd

df = pd.read_excel("movies_dataset.xlsx")
df = df.dropna(subset=["year", "title", "popularity"])

# 给每个年份的电影按热度降序排名，method='min'让并列热度的电影获得相同排名
df["popularity_rank"] = df.groupby("year")["popularity"].rank(ascending=False, method="min")

# 筛选出排名为1的所有电影
top_movies_per_year = df[df["popularity_rank"] == 1][["year", "title", "popularity"]].sort_values("year")

# 按年份打印所有并列热门电影
for year, movie_group in top_movies_per_year.groupby("year"):
    print(f"\n年份 {year}:")
    for _, row in movie_group.iterrows():
        print(f"  《{row['title']}》，热度评分 {row['popularity']:.2f}")

为什么之前只得到year和popularity？ #

如果之前你用了类似df.groupby('year').agg({'popularity': 'max'})的代码，这只会对popularity字段做聚合，没有关联对应的title。上面的两种方法都是通过定位完整行来确保title和最高popularity正确匹配，而不是单独聚合字段。

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