如何为每位人员生成全年月度Pandas DataFrame并维护累计Sum值

步骤说明与代码实现 #

1. 导入依赖并读取原始数据 #

先导入Pandas，读取原始数据并将日期列转为标准格式：

import pandas as pd

# 原始数据
data = [
    ["12/31/22", "CASH", 3512, "", 23],
    ["12/31/22", "CASH", 3513, "Mike", 3],
    ["12/31/22", "CASH", 3514, "Mo", 4],
    ["12/31/22", "CASH", 3515, "Mary", 5],
    ["12/31/22", "CASH", 3516, "Mel", 10],
    ["12/31/22", "CASH", 3517, "Mop", 2],
    ["12/31/22", "CASH", 3518, "Me", 7],
    ["1/31/23", "CASH", 3512, "", 0],
    ["1/31/23", "CASH", 3514, "Mo", 0],
    ["1/31/23", "CASH", 3515, "Mary", -2],
    ["1/31/23", "CASH", 3516, "Mel", 0],
    ["1/31/23", "CASH", 3517, "Mop", 2],
    ["3/30/23", "CASH", 3512, "", 6],
    ["3/30/23", "CASH", 3518, "Me", 0],
    ["3/30/23", "CASH", 3514, "Mo", 3],
    ["3/30/23", "CASH", 3515, "Mary", 0],
    ["3/30/23", "CASH", 3516, "Mel", 0],
    ["3/30/23", "CASH", 3517, "Mop", 2],
    ["5/31/23", "CASH", 3512, "", -2],
    ["5/31/23", "CASH", 3518, "Me", 3],
    ["5/31/23", "CASH", 3514, "Mo", 0],
    ["5/31/23", "CASH", 3515, "Mary", 0],
    ["5/31/23", "CASH", 3516, "Mel", 1],
    ["5/31/23", "CASH", 3517, "Mop", 0],
    ["7/31/23", "CASH", 3512, "", 0],
    ["7/31/23", "CASH", 3518, "Me", 3],
    ["7/31/23", "CASH", 3514, "Mo", 0],
    ["7/31/23", "CASH", 3515, "Mary", 1],
    ["7/31/23", "CASH", 3516, "Mel", 0],
    ["7/31/23", "CASH", 3517, "Mop", 0],
    ["8/31/23", "CASH", 3512, "", 2],
    ["8/31/23", "CASH", 3518, "Me", -3],
    ["8/31/23", "CASH", 3514, "Mo", 0],
    ["11/30/23", "CASH", 3512, "", 0],
    ["12/31/23", "CASH", 3518, "Me", 3]
]

df = pd.DataFrame(data, columns=["M_Yr", "Type", "ID", "Name", "Sum"])
# 转换日期列为datetime类型，方便后续处理
df["M_Yr"] = pd.to_datetime(df["M_Yr"], format="%m/%d/%y")

2. 生成完整月度日期序列 #

创建从12/31/22到12/31/23的所有月末日期：

# 生成目标时间段内的所有月末日期
date_range = pd.date_range(start="2022-12-31", end="2023-12-31", freq="M")
date_df = pd.DataFrame({"M_Yr": date_range})

3. 提取唯一用户实体 #

获取所有独立的ID/Type/Name组合，确保每个ID对应固定属性：

unique_ids = df[["ID", "Type", "Name"]].drop_duplicates().reset_index(drop=True)

4. 生成全量记录框架 #

通过笛卡尔积创建所有日期与所有ID的完整组合：

# 生成所有月份×所有ID的全量记录框架
full_df = date_df.merge(unique_ids, how="cross")

5. 合并原始变动数据并填充缺失值 #

将原始数据中的月度变动Sum值合并到全量框架，缺失的变动值填充为0：

# 合并原始数据的变动Sum值
full_df = full_df.merge(df[["M_Yr", "ID", "Sum"]], on=["M_Yr", "ID"], how="left")
# 当月无变动的记录填充0
full_df["Sum"] = full_df["Sum"].fillna(0)

6. 计算累积更新的Sum值 #

按ID分组，以初始值为起点累加月度变动值，得到每个月的当前Sum：

# 提取每个ID的初始值（2022-12-31的Sum）
initial_values = df[df["M_Yr"] == "2022-12-31"][["ID", "Sum"]].rename(columns={"Sum": "Initial_Sum"})
full_df = full_df.merge(initial_values, on="ID", how="left")

# 计算每个ID从第二个月开始的累积变动量
full_df["Total_Change"] = full_df.groupby("ID")["Sum"].cumsum() - full_df["Sum"]
# 最终Sum = 初始值 + 累积变动量
full_df["Sum"] = full_df["Initial_Sum"] + full_df["Total_Change"]

# 清理临时列
full_df = full_df.drop(columns=["Initial_Sum", "Total_Change"])

7. 还原日期格式并排序 #

将日期列转回原始显示格式，按日期和ID排序：

# 转换日期格式为mm/dd/yy
full_df["M_Yr"] = full_df["M_Yr"].dt.strftime("%m/%d/%y")
# 按日期和ID排序，匹配期望结果顺序
full_df = full_df.sort_values(by=["M_Yr", "ID"]).reset_index(drop=True)

最终结果 #

执行上述代码后，full_df即为符合要求的完整DataFrame，包含所有月份的记录，且Sum列按规则累积更新。

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