Python pandas如何将日期列宽表转换为多笔付款日期金额结构

import pandas as pd
import numpy as np
from datetime import datetime

# 构造示例原始DataFrame，你如果已经导入好数据可以跳过这步
df = pd.DataFrame({
    datetime(2021, 8, 1, 00, 00, 00): [120, np.nan, np.nan, np.nan, 300],
    datetime(2021, 9, 1, 00, 00, 00): [np.nan, np.nan, 50, np.nan, np.nan],
    datetime(2021, 10, 1, 00, 00, 00): [np.nan, 40, np.nan, 100, np.nan],
    datetime(2021, 11, 1, 00, 00, 00): [80, np.nan, 50, np.nan, np.nan],
    datetime(2021, 12, 1, 00, 00, 00): [np.nan, 20, np.nan, np.nan, np.nan]
})

# 核心转换逻辑
def parse_single_subject(row):
    # 提取当前主体所有非空付款记录，按日期升序排序
    valid_payments = sorted(
        [(pay_date, amount) for pay_date, amount in row.items() if pd.notna(amount)],
        key=lambda x: x[0]
    )
    # 生成前2笔付款的字段，不足则按要求填充空值
    result = {}
    for idx in range(2):
        if idx < len(valid_payments):
            result[f"PayDate{idx+1}"] = valid_payments[idx][0]
            result[f"Amount{idx+1}"] = valid_payments[idx][1]
        else:
            result[f"PayDate{idx+1}"] = ""
            result[f"Amount{idx+1}"] = np.nan
    return pd.Series(result)

# 按行执行转换得到目标结构
result_df = df.apply(parse_single_subject, axis=1)

# 打印验证结果
print(result_df)

如果后续需要扩展取前N笔付款，只需要把range(2)里的2改成对应数值即可。

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