Pandas多条件筛选+分组求和：新增指定统计列的实现问题

原始数据 #

现有如下Pandas DataFrame：

需求说明 #

需新增一列Total Unexcused Absents for week 4 Reported by Parent，要求：

• 对Absents字段求和
• 按Student Name分组
• 筛选条件：Excused or Unexcused = Unexcused、Reported by = Parent、Absent Week = week 4

问题分析 #

你尝试的代码逻辑错误：loc内的条件拼接类型不匹配，直接将分组求和的Series与布尔条件做&运算，无法生成正确的目标列。正确思路应该是先筛选符合条件的行，分组求和后再合并回原DataFrame。

可行代码 #

import pandas as pd

# 构造原始DataFrame
data = [
    ["Henry", 2, "week 4", "Unexcused", "Excused"],
    ["Elizabeth", 2, "week 4", "Unexcused", "Parent"],
    ["Elizabeth", 3, "week 4", "Unexcused", "Parent"],
    ["Sara", 4, "week 4", "Unexcused", "Parent"],
    ["Elizabeth", 5, "week 3", "Excused", "Parent"],
    ["Henry", 1, "week 1", "Unexcused", "Student"],
    ["Sara", 2, "week 4", "Unexcused", "Parent"]
]
df = pd.DataFrame(data, columns=["Student Name", "Absents", "Absent Week", "Excused or Unexcused", "Reported by"])

# 1. 筛选符合条件的行并分组求和
sum_result = df.loc[
    (df["Absent Week"] == "week 4") &
    (df["Excused or Unexcused"] == "Unexcused") &
    (df["Reported by"] == "Parent")
].groupby("Student Name")["Absents"].sum().reset_index(
    name="Total Unexcused Absents for week 4 Reported by Parent"
)

# 2. 合并回原DataFrame，未匹配到的行填充0
df = df.merge(sum_result, on="Student Name", how="left").fillna(0)

# 若需实现你理想输出中Henry行显示"Student"的特殊需求，可添加以下代码（逻辑上该列应为数值，此为特殊场景处理）
# df["Total Unexcused Absents for week 4 Reported by Parent"] = df.apply(
#     lambda row: row["Reported by"] if row["Reported by"] != "Parent" else row["Total Unexcused Absents for week 4 Reported by Parent"],
#     axis=1
# )

print(df)

输出结果 #

运行代码后得到的正确结果（注：你提供的理想输出中Sara的总和应为6而非4，以下为逻辑正确的输出）：

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