如何在pandas DataFrame中计算层级子节点的总和？

我有如下数据：

我尝试用这段代码计算每个父节点的所有子节点总和，但结果不符合预期：

import pandas as pd

df = pd.DataFrame(
    columns=["child", "parent", "value", 'total'],
    data=[
        ["First", "", 0, 0],
        ["Project 1", "First", 0, 0],
        ["Project 2", "First", 0, 0],
        ["Project 3", "First", 0, 0],
        ["Auto", "Project 1", 16, 0],
        ["Huis", "Project 1", 14, 0],
        ["Auto", "Project 2", 11, 0],
        ["Huis", "Project 2", 12, 0],
        ["Overig", "Project 2", 7, 0],
        ["Auto", "Project 3",30, 0],
    ]
)
df['total'] = df.groupby(['parent', 'child'])['value'].cumsum()
print(df)

我想要的预期输出是：

你用的groupby(['parent', 'child'])['value'].cumsum()方法不对，这个操作只是对每个(parent, child)分组内的value做累加，完全没处理树形结构的后代求和逻辑。你需要的是递归计算每个节点所有后代（包括子节点的子节点）的value总和，下面是两种可行的实现方式：

方法一：递归求和 #

这种方式逻辑直观，适合层级不深的树形数据：

import pandas as pd

df = pd.DataFrame(
    columns=["child", "parent", "value", 'total'],
    data=[
        ["First", "", 0, 0],
        ["Project 1", "First", 0, 0],
        ["Project 2", "First", 0, 0],
        ["Project 3", "First", 0, 0],
        ["Auto", "Project 1", 16, 0],
        ["Huis", "Project 1", 14, 0],
        ["Auto", "Project 2", 11, 0],
        ["Huis", "Project 2", 12, 0],
        ["Overig", "Project 2", 7, 0],
        ["Auto", "Project 3",30, 0],
    ]
)

# 构建父节点到子节点的映射字典
parent_children_map = df.groupby('parent')['child'].apply(list).to_dict()
# 手动处理根节点（parent为空字符串的情况）
parent_children_map[''] = [df.loc[df['parent'] == '', 'child'].iloc[0]]

# 递归函数：计算给定节点的所有后代value总和
def get_descendant_sum(node):
    total = 0
    # 遍历当前节点的所有直接子节点
    for child in parent_children_map.get(node, []):
        # 子节点自身的value + 子节点的后代总和
        child_val = df.loc[df['child'] == child, 'value'].iloc[0]
        total += child_val + get_descendant_sum(child)
    return total

# 为每个节点计算total值
df['total'] = df['child'].apply(get_descendant_sum)

print(df)

运行后就能得到你要的预期结果。

方法二：循环向上累加（非递归） #

如果你的数据层级很深，递归可能触发栈溢出，这种情况下可以用循环从叶子节点开始向上累加：

import pandas as pd

df = pd.DataFrame(
    columns=["child", "parent", "value", 'total'],
    data=[
        ["First", "", 0, 0],
        ["Project 1", "First", 0, 0],
        ["Project 2", "First", 0, 0],
        ["Project 3", "First", 0, 0],
        ["Auto", "Project 1", 16, 0],
        ["Huis", "Project 1", 14, 0],
        ["Auto", "Project 2", 11, 0],
        ["Huis", "Project 2", 12, 0],
        ["Overig", "Project 2", 7, 0],
        ["Auto", "Project 3",30, 0],
    ]
)

# 先把total初始化为节点自身的value
df['total'] = df['value'].copy()
# 添加depth列标记节点层级，叶子节点深度最大
df['depth'] = 0

# 计算每个节点的深度
current_depth = 0
while True:
    # 取出当前深度节点的父节点
    processed_parents = df[df['depth'] == current_depth]['parent'].unique()
    if not processed_parents.size:
        break
    # 把父节点的深度设为当前深度+1
    df.loc[df['child'].isin(processed_parents), 'depth'] = current_depth + 1
    current_depth += 1

# 按深度从大到小排序（从叶子节点到根节点）
df_sorted = df.sort_values('depth', ascending=False)

# 从叶子节点开始，把每个节点的total累加到父节点的total中
for _, row in df_sorted.iterrows():
    if row['parent'] != '':
        df.loc[df['child'] == row['parent'], 'total'] += row['total']

# 移除临时的depth列
df = df.drop('depth', axis=1)

print(df)

这个方法通过逐层向上累加，同样能得到正确的total值，而且避免了递归栈溢出的问题。

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