如何按日期和client_ID计算account A与B的value差值并处理缺失？

问题背景 #

现有表格数据如下：

需要实现：

• 按date和client_ID分组，计算account A的value减去account B的value
• 输出包含date、client_ID、差值三列的结果
• 兼容部分分组下缺少A或B账户的场景

实现方案（Python Pandas） #

Pandas是处理这类表格分组计算的首选工具，以下是完整实现步骤：

1. 准备数据 #

先将原始数据转为DataFrame：

import pandas as pd

# 构造示例数据（实际场景可通过pd.read_csv/pd.read_excel读取）
data = [
    ["A", "31/01/2023", 1, 10],
    ["B", "31/01/2023", 1, 2],
    ["C", "31/01/2023", 1, 50],
    ["A", "28/02/2023", 1, 15],
    ["B", "28/02/2023", 1, 11],
    ["C", "28/02/2023", 1, 50],
    ["A", "31/01/2023", 2, 7],
    ["B", "31/01/2023", 2, 10]
]

df = pd.DataFrame(data, columns=["account", "date", "client_ID", "value"])

2. 筛选目标账户并重组数据 #

只保留A、B账户的数据，再按date+client_ID分组，将A、B的value转为列：

# 过滤出A、B账户，排除无关的C账户
ab_df = df[df["account"].isin(["A", "B"])].copy()

# 重组数据：行是(date, client_ID)，列是A/B，值对应value
pivoted_df = ab_df.pivot(
    index=["date", "client_ID"],
    columns="account",
    values="value"
).reset_index()

3. 计算差值并处理缺失值 #

计算A-B的差值，针对缺失A或B的情况，可选择用0填充（或保留NaN，按需调整）：

# 方案1：缺失A/B时用0替代，确保差值可计算
pivoted_df["差值"] = pivoted_df["A"].fillna(0) - pivoted_df["B"].fillna(0)

# 方案2：保留缺失值（缺失A/B时差值为NaN），只需去掉fillna(0)
# pivoted_df["差值"] = pivoted_df["A"] - pivoted_df["B"]

# 提取最终需要的列
result_df = pivoted_df[["date", "client_ID", "差值"]]

4. 查看结果 #

运行代码后，输出结果如下：

date  client_ID  差值
0  28/02/2023          1  4.0
1  31/01/2023          1  8.0
2  31/01/2023          2 -3.0

缺失场景测试 #

如果某分组只有A账户（比如["A", "31/03/2023", 3, 20]），方案1会计算出20-0=20；如果只有B账户（比如["B", "31/03/2023", 4, 5]），方案1会得到0-5=-5，完全满足缺失场景的处理需求。

内容的提问来源于stack exchange，提问作者t.pellegrom