R语言如何将长格式患者检查数据转换为指定规则的宽格式

以下提供R语言（tidyverse生态）和Python（pandas）两种主流实现方案：

R 实现方案 #

依赖tidyverse套件，核心逻辑是先按患者ID聚合所有季度指标生成患者级宽表，再关联回原始表保留所有Exam_Report行：

library(tidyverse)

# 构造示例数据
df <- tibble(
  Exam_Report = c("Lorem ipsum1", "Lorem ipsum2", "Lorem ipsum3", "Lorem ipsum4"),
  PatientID = c(101, 101, 102, 102),
  ExamDate = c("1stQuarter", "3rdQuarter", "2ndQuarter", "3rdQuarter"),
  LVEF = c(55, 58, 39, 49),
  LA_size = c(20, 20, 24, 24)
)

# 转换逻辑
df_wide <- df %>%
  # 生成季度缩写
  mutate(quarter_abbr = str_replace(ExamDate, "Quarter", "Q")) %>%
  # 生成患者级指标宽表（每个患者一行，包含所有季度指标）
  select(PatientID, quarter_abbr, LVEF, LA_size) %>%
  pivot_wider(
    names_from = quarter_abbr,
    values_from = c(LVEF, LA_size),
    names_glue = "{quarter_abbr}_{.value}",
    values_fill = NA
  ) %>%
  # 关联回原始表保留所有Exam_Report行
  left_join(df %>% select(Exam_Report, PatientID), by = "PatientID") %>%
  # 调整列顺序匹配需求
  select(Exam_Report, PatientID, starts_with("1stQ"), starts_with("2ndQ"), starts_with("3rdQ"))

Python 实现方案 #

依赖pandas，逻辑和R方案完全一致：

import pandas as pd
import re

# 构造示例数据
df = pd.DataFrame({
    "Exam_Report": ["Lorem ipsum1", "Lorem ipsum2", "Lorem ipsum3", "Lorem ipsum4"],
    "PatientID": [101, 101, 102, 102],
    "ExamDate": ["1stQuarter", "3rdQuarter", "2ndQuarter", "3rdQuarter"],
    "LVEF": [55, 58, 39, 49],
    "LA_size": [20, 20, 24, 24]
})

# 转换逻辑
# 1. 生成季度缩写
df["quarter_abbr"] = df["ExamDate"].apply(lambda x: re.sub("Quarter", "Q", x))
# 2. 生成患者级宽表
patient_wide = df.pivot_table(
    index="PatientID",
    columns="quarter_abbr",
    values=["LVEF", "LA_size"],
    aggfunc="first"
).reset_index()
# 3. 调整列名格式为「季度_指标」
patient_wide.columns = [
    f"{col[1]}_{col[0]}" if col[0] != "PatientID" else col[0] 
    for col in patient_wide.columns.to_flat_index()
]
# 4. 关联回原始表保留所有Exam_Report行
df_wide = df[["Exam_Report", "PatientID"]].merge(patient_wide, on="PatientID", how="left")

两种方案输出完全匹配需求：

• 保留所有原始Exam_Report行，允许PatientID重复
• 自动生成所有季度前缀的指标列，缺失季度自动填充NA
• 新增其他超声指标时，只需将指标名加入值列列表即可，无需修改其他逻辑

内容的提问来源于stack exchange，提问作者Fábio Nunes