如何用Pandas将多行数据合并为一行？附数据处理示例

需求是将分散在多行的交易数据合并为单行，比如把描述的补充行（如"CAHAYA"）合并到对应交易的描述中，同时将日期和时间拼接成完整的日期时间字段。

输入数据复现代码 #

import pandas as pd

data = {
    "Date & Time\nValue Date": [
        "01/12/2022", "08:35:27", "01/12/2022", "08:35:28", "", "01/12/2022", "08:35:29", "",
    ],
    "Description": [
        "", "MCM InhouseTrf KE TARMIN", "", "MCM InhouseTrf KE UTAMA RADAR", "CAHAYA", "", "MCM InhouseTrf KE UTAMA RADAR", "CAHAYA",
    ],
    "Reference No.": [
        "", "", "", "", "", "", "", "",
    ],
    "Debit": [
        "74,000.00", "", "650,000.00", "", "", "20,000.00", "", "",
    ],
    "Credit": [
        "0.00", "", "0.00", "", "", "0.00", "", "",
    ],
    "Balance": [
        "34,984,307.42", "", "34,334,307.42", "", "", "34,314,307.42", "", "",
    ]
}

df = pd.DataFrame(data)

解决方案代码 #

# 生成分组标识：以Debit非空行作为每笔交易的起始点，生成递增分组号
df['group'] = df['Debit'].ne('').cumsum()

# 分组聚合，合并对应字段
result = df.groupby('group').agg({
    "Date & Time\nValue Date": lambda x: ' '.join(filter(None, x)),
    "Description": lambda x: ' '.join(filter(None, x)),
    "Reference No.": 'first',
    "Debit": lambda x: next(filter(None, x)),
    "Credit": lambda x: next(filter(None, x)),
    "Balance": lambda x: next(filter(None, x))
}).reset_index(drop=True)

# 调整索引从1开始，匹配期望输出格式
result.index = result.index + 1

print(result)

代码说明 #

1. 分组标识生成：利用Debit列的非空值判断每笔交易的起始行，通过cumsum()为每一组交易分配唯一编号。
2. 分组聚合：
   • 日期时间列和描述列：用filter(None, x)过滤空值后拼接，得到完整的日期时间和描述文本。
   • 数值列（Debit、Credit、Balance）：用next(filter(None, x))取分组内的非空值，保证每笔交易只保留一个有效数值。
3. 索引调整：重置索引并从1开始，与期望输出的索引格式一致。

输出结果 #

Date & Time\nValue Date                                Description Reference No.       Debit Credit        Balance
1                01/12/2022 08:35:27               MCM InhouseTrf KE TARMIN                       74,000.00    0.00  34,984,307.42
2                01/12/2022 08:35:28  MCM InhouseTrf KE UTAMA RADAR CAHAYA                      650,000.00    0.00  34,334,307.42
3                01/12/2022 08:35:29  MCM InhouseTrf KE UTAMA RADAR CAHAYA                       20,000.00    0.00  34,314,307.42

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