如何在Pandas中合并具有父子关系的行？

示例DataFrame #

case_number parent_case_number              name                role    paid                            notes
0  NYC-22-1234               None      Bob Cratchit          Accountant   50000   Scrooge's favorite accountant.
1  LON-22-1446               None  Ebenezer Scrooge             Partner  950000  Charles Dickens would be proud.
2  CHI-21-0115               None        Bob Marley  Partner (Deceased)  425000                        Shackled.
3  NYC-22-1235        NYC-22-1234      Bob Cratchit          Accountant   30000    One of Scrooge's accountants.

可通过以下代码构建：

import pandas as pd

sample_data = [
    {
        "case_number": "NYC-22-1234",
        "parent_case_number": None,
        "name": "Bob Cratchit",
        "role": "Accountant",
        "paid": 50000,
        "notes": "Scrooge's favorite accountant.",
    },
    {
        "case_number": "LON-22-1446",
        "parent_case_number": None,
        "name": "Ebenezer Scrooge",
        "role": "Partner",
        "paid": 950000,
        "notes": "Charles Dickens would be proud.",
    },
    {
        "case_number": "CHI-21-0115",
        "parent_case_number": None,
        "name": "Bob Marley",
        "role": "Partner (Deceased)",
        "paid": 425000,
        "notes": "Shackled.",
    },
    {
        "case_number": "NYC-22-1235",
        "parent_case_number": "NYC-22-1234",
        "name": "Bob Cratchit",
        "role": "Accountant",
        "paid": 30000,
        "notes": "One of Scrooge's accountants.",
    },
]

df = pd.DataFrame(sample_data)

合并需求 #

需要将子行（parent_case_number不为空的行）合并到对应的父行（case_number匹配子行parent_case_number的行），合并规则如下：

• 字段为空时，取非空值（父/子行均可）；
• 字段值相同时，保留一个即可；
• 数值型字段（如paid）取最大值；
• notes字段将子行内容追加到父行内容后；
• 新增child_case_numbers列，记录被合并的子行case_number。

预期输出：

case_number parent_case_number              name                role    paid                                                         notes child_case_numbers
0  NYC-22-1234               None      Bob Cratchit          Accountant   50000  Scrooge's favorite accountant. One of Scrooge's accountants.        NYC-22-1235
1  LON-22-1446               None  Ebenezer Scrooge             Partner  950000                               Charles Dickens would be proud.                NaN
2  CHI-21-0115               None        Bob Marley  Partner (Deceased)  425000                                                     Shackled.                NaN

实现方案 #

通过拆分父子数据+自定义合并逻辑实现，步骤如下：

import pandas as pd

# 构建数据
sample_data = [
    {"case_number": "NYC-22-1234", "parent_case_number": None, "name": "Bob Cratchit", "role": "Accountant", "paid": 50000, "notes": "Scrooge's favorite accountant."},
    {"case_number": "LON-22-1446", "parent_case_number": None, "name": "Ebenezer Scrooge", "role": "Partner", "paid": 950000, "notes": "Charles Dickens would be proud."},
    {"case_number": "CHI-21-0115", "parent_case_number": None, "name": "Bob Marley", "role": "Partner (Deceased)", "paid": 425000, "notes": "Shackled."},
    {"case_number": "NYC-22-1235", "parent_case_number": "NYC-22-1234", "name": "Bob Cratchit", "role": "Accountant", "paid": 30000, "notes": "One of Scrooge's accountants."},
]
df = pd.DataFrame(sample_data)

# 拆分父行和子行
parent_df = df[df["parent_case_number"].isna()].copy().set_index("case_number")
child_df = df[df["parent_case_number"].notna()].copy()

# 按父case分组整理子行数据
child_groups = child_df.groupby("parent_case_number").agg(
    child_case_numbers=("case_number", lambda x: " ".join(x)),
    paid=("paid", "max"),
    notes=("notes", lambda x: " ".join(x)),
    name=("name", "first"),
    role=("role", "first")
)

# 合并父行与子组数据
merged_df = parent_df.join(child_groups, how="left").reset_index()

# 按规则处理各字段合并
merged_df["paid"] = merged_df.apply(lambda row: max(row["paid_x"], row["paid_y"]) if pd.notna(row["paid_y"]) else row["paid_x"], axis=1)
merged_df["notes"] = merged_df.apply(lambda row: f"{row['notes_x']} {row['notes_y']}" if pd.notna(row["notes_y"]) else row["notes_x"], axis=1)
merged_df["name"] = merged_df["name_x"].fillna(merged_df["name_y"])
merged_df["role"] = merged_df["role_x"].fillna(merged_df["role_y"])

# 清理临时列并调整格式
merged_df = merged_df.drop(columns=["paid_x", "paid_y", "notes_x", "notes_y", "name_x", "name_y", "role_x", "role_y"])
merged_df["child_case_numbers"] = merged_df["child_case_numbers"].fillna(pd.NA)
merged_df = merged_df[["case_number", "parent_case_number", "name", "role", "paid", "notes", "child_case_numbers"]]

print(merged_df)

代码说明 #

1. 拆分数据：将父行（parent_case_number为空）和子行（parent_case_number非空）分开处理；
2. 子行聚合：按父case编号分组，提前整理子行的关键信息（收集子case编号、取paid最大值、拼接notes）；
3. 字段合并：针对不同字段应用对应规则：
   • paid取父子行的最大值；
   • notes将子行内容追加到父行；
   • 字符串字段（name/role）优先保留父行值，父行空则用子行值；
4. 格式整理：清理临时列，调整列顺序并处理空值，得到最终结果。

该方案支持一个父行对应多个子行的场景，所有子行数据会按规则聚合后合并到父行。

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