如何用Pandas的groupby按站点统计逾期调查数量？

方法一：通过标记列求和统计 #

先为每条记录添加逾期标记，再按站点分组求和：

import pandas as pd

# 构造原始数据
data = {
    "site_name": ["Puerto Rico", "Paris", "Puerto Rico", "Paris", "Puerto Rico", "Paris"],
    "overdue": ["Overdue", "On Time", "On Time", "Overdue", "On Time", "Overdue"]
}
df = pd.DataFrame(data)

# 生成统计结果
result = df.assign(overdue_flag=df["overdue"] == "Overdue") \
           .groupby("site_name", as_index=False)["overdue_flag"] \
           .sum() \
           .rename(columns={"overdue_flag": "overdue_count"})

print(result)

方法二：先筛选逾期记录再分组计数 #

直接筛选出逾期的行，再按站点统计数量：

import pandas as pd

# 构造原始数据
data = {
    "site_name": ["Puerto Rico", "Paris", "Puerto Rico", "Paris", "Puerto Rico", "Paris"],
    "overdue": ["Overdue", "On Time", "On Time", "Overdue", "On Time", "Overdue"]
}
df = pd.DataFrame(data)

# 生成统计结果
result = df[df["overdue"] == "Overdue"] \
           .groupby("site_name", as_index=False) \
           .size() \
           .rename(columns={"size": "overdue_count"})

print(result)

输出结果 #

两种方法都会得到符合预期的统计结果：

site_name  overdue_count
0        Paris              2
1  Puerto Rico              1

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