Pandas筛选指定年份列表中出现2次及以上事件的全量数据

需求规则 #

• 给定目标年份列表为[2011,2012,2013,2014]
• 统计每个事件在上述目标年份范围内的发生次数，仅保留次数≥2次的事件
• 符合要求的事件需保留所有行记录，事件发生在非目标年份的记录不能剔除

原始数据 #

Event   Year    Month
Event1  2011    January
Event1  2012    January
Event1  2013    February
Event1  2014    January
Event1  2015    March
Event2  2011    January
Event2  2014    April
Event3  2012    January
Event3  2015    March
Event4  2013    February

实现代码 #

import pandas as pd

# 加载/构造原始数据表
raw_data = [
    ["Event1", 2011, "January"],
    ["Event1", 2012, "January"],
    ["Event1", 2013, "February"],
    ["Event1", 2014, "January"],
    ["Event1", 2015, "March"],
    ["Event2", 2011, "January"],
    ["Event2", 2014, "April"],
    ["Event3", 2012, "January"],
    ["Event3", 2015, "March"],
    ["Event4", 2013, "February"]
]
df = pd.DataFrame(raw_data, columns=["Event", "Year", "Month"])

target_years = [2011, 2012, 2013, 2014]
# 第一步：筛选目标年份范围内的记录，按事件分组计数，提取次数≥2的事件名
valid_event_list = df[df["Year"].isin(target_years)]\
    .groupby("Event")["Year"].count()\
    [lambda x: x >= 2].index.tolist()
# 第二步：从全量数据中提取符合要求事件的所有记录
final_result = df[df["Event"].isin(valid_event_list)].reset_index(drop=True)

输出结果 #

运行代码后得到的结果和预期完全匹配：

Event  Year    Month
0  Event1  2011  January
1  Event1  2012  January
2  Event1  2013  February
3  Event1  2014  January
4  Event1  2015    March
5  Event2  2011  January
6  Event2  2014    April

注意不要提前过滤掉非目标年份的数据，先在目标年份范围内统计出符合次数要求的事件名单，再从全量表匹配对应事件的所有记录，就能避免误删非目标年份的有效数据。

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