如何折叠Pandas DataFrame，按就诊日期生成病史宽格式列

我有一个存储患者病史的Pandas DataFrame，每条记录对应一次就诊的检查结果。需要将每位患者的过往就诊记录折叠到当前就诊行中，以宽格式展示，要求：

• 保留原始列的顺序
• 新增的列以mh_为前缀（如mh_result1），且按历史时间顺序填充（较早的记录先填充到序号小的列）

目前用groupby的agg方法只能把多列合并成单列，没法生成独立的历史列。其中patientId、apptDate、age、bmi是非透视、非分组索引列。

原始DataFrame #

| patientId | apptDate   | age | bmi | examinationId | result     | category       | comment                                     |
|-----------|------------|-----|-----|---------------|------------|----------------|---------------------------------------------|
| 1         | 2024-07-08 | 45  | 22  | 45            | Long Term  | Cardiovascular | Cardiovascular defect finding, fup required |
| 1         | 2024-02-01 | 45  | 22  | 33            | None       | None           | None                                        |
| 1         | 2023-11-14 | 45  | 22  | 12            | Short Term | Respiratory    | Shortness of breath, med prescribed         |
| 2         | 2023-12-23 | 32  | 12  | 18            | Short Term | Gastro         | Recorded malnutrition                       |
| 2         | 2022-12-11 | 32  | 13  | 21            | Short Term | Gastro         | None                                        |

期望输出DataFrame #

| patientId | apptDate   | age | bmi | examinationId | result     | category       | comment                                     | mh_result1 | mh_category1 | mh_comment1                         | mh_result2 | mh_category2 | mh_comment2 |
|-----------|------------|-----|-----|---------------|------------|----------------|---------------------------------------------|------------|--------------|-------------------------------------|------------|--------------|-------------|
| 1         | 2024-07-08 | 45  | 22  | 45            | Long Term  | Cardiovascular | Cardiovascular defect finding, fup required | Short Term | Respiratory  | Shortness of breath, med prescribed | None       | None         | None        |
| 1         | 2024-02-01 | 45  | 22  | 33            | None       | None           | None                                        | Short Term | Respiratory  | Shortness of breath, med prescribed | None       | None         | None        |
| 1         | 2023-11-14 | 45  | 22  | 12            | Short Term | Respiratory    | Shortness of breath, med prescribed         | None       | None         | None                                | None       | None         | None        |
| 2         | 2023-12-23 | 32  | 12  | 18            | Short Term | Gastro         | Recorded malnutrition                       | Short Term | Gastro       | None                                | None       | None         | None        |
| 2         | 2022-12-11 | 32  | 13  | 21            | Short Term | Gastro         | None                                        | None       | None         | None                                | None       | None         | None        |

解决方案 #

步骤说明 #

1. 按patientId分组，每组内按apptDate升序排序，确保较早就诊记录在前
2. 对每条就诊记录，提取该患者当前记录之前的所有历史数据
3. 将历史数据转成宽格式，列名加上mh_前缀和序号，补全缺失列后合并回原数据

代码实现 #

import pandas as pd

# 构造原始数据
data = {
    'patientId': [1,1,1,2,2],
    'apptDate': ['2024-07-08','2024-02-01','2023-11-14','2023-12-23','2022-12-11'],
    'age': [45,45,45,32,32],
    'bmi': [22,22,22,12,13],
    'examinationId': [45,33,12,18,21],
    'result': ['Long Term','None','Short Term','Short Term','Short Term'],
    'category': ['Cardiovascular','None','Respiratory','Gastro','Gastro'],
    'comment': ['Cardiovascular defect finding, fup required','None','Shortness of breath, med prescribed','Recorded malnutrition','None']
}

df = pd.DataFrame(data)
df['apptDate'] = pd.to_datetime(df['apptDate'])

# 1. 按患者分组并按就诊日期升序排序
df_sorted = df.sort_values(['patientId', 'apptDate']).reset_index(drop=True)

# 定义需要提取的历史列
history_cols = ['result', 'category', 'comment']
# 计算最大需要保留的历史记录数
max_history = df_sorted.groupby('patientId').size().max() - 1

# 2. 生成每条记录对应的历史数据
history_dfs = []
for pid, group in df_sorted.groupby('patientId'):
    for idx in range(len(group)):
        # 获取当前记录之前的所有历史数据
        history = group.iloc[:idx][history_cols].reset_index(drop=True)
        # 重命名历史列
        history.columns = [f'mh_{col}{i+1}' for i, col in enumerate(history.columns)]
        # 补全缺失的历史列，填充None
        for i in range(len(history), max_history):
            for col in history_cols:
                history[f'mh_{col}{i+1}'] = None
        # 合并当前记录和历史数据
        merged_row = pd.concat([group.iloc[idx:idx+1], history], axis=1)
        history_dfs.append(merged_row)

# 3. 合并所有结果并调整列顺序
final_df = pd.concat(history_dfs).reset_index(drop=True)
original_cols = df.columns.tolist()
# 按序号顺序排列历史列
ordered_history_cols = []
for i in range(1, max_history+1):
    for col in history_cols:
        ordered_history_cols.append(f'mh_{col}{i}')

final_df = final_df[original_cols + ordered_history_cols]

# 打印结果
print(final_df.to_string(index=False))

代码说明 #

• 先排序保证历史记录的时间顺序正确
• 遍历每条记录时，只提取当前记录之前的就诊数据，避免混入后续就诊记录
• 补全缺失的历史列确保所有行的列数一致，最后调整列顺序符合需求

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