pandas获取各事件最新年份及对应所有月份的数据

原始数据 #

Event       Month       Year
Event1      January     2012
Event1      February    2013
Event1      March       2014
Event1      April       2017
Event1      May         2017
Event1      June        2017
Event2      May         2018
Event2      May         2019
Event3      February    2012
Event3      March       2012
Event3      April       2012

筛选规则 #

• Event1最新年份为2017，保留该年份下April、May、June共3条记录
• Event2最新年份为2019，保留该年份下May共1条记录
• Event3最新年份为2012，保留该年份下February、March、April共3条记录

实现代码 #

import pandas as pd

# 加载/构造原始数据集
df = pd.DataFrame(
    [
        ["Event1", "January", 2012],
        ["Event1", "February", 2013],
        ["Event1", "March", 2014],
        ["Event1", "April", 2017],
        ["Event1", "May", 2017],
        ["Event1", "June", 2017],
        ["Event2", "May", 2018],
        ["Event2", "May", 2019],
        ["Event3", "February", 2012],
        ["Event3", "March", 2012],
        ["Event3", "April", 2012]
    ],
    columns=["Event", "Month", "Year"]
)

# 核心筛选逻辑：逐行匹配所属Event分组的最大年份
result = df[df["Year"] == df.groupby("Event")["Year"].transform("max")].reset_index(drop=True)

核心逻辑说明：groupby("Event")["Year"].transform("max")会为每一行返回其所属Event对应的最大年份值，直接做布尔索引筛选即可，无需写循环遍历分组，执行效率更高。

输出结果 #

执行代码后得到的result内容完全匹配需求：

Event     Month  Year
0  Event1     April  2017
1  Event1       May  2017
2  Event1      June  2017
3  Event2       May  2019
4  Event3  February  2012
5  Event3     March  2012
6  Event3     April  2012

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