如何用pivot wider与spread实现患者检测数据的宽表转换？

R语言（tidyverse）实现 #

先给每位患者的检测记录添加分组内序号，再用pivot_wider完成转换：

library(tidyverse)

# 构造示例数据
df <- tibble(
  `Patient ID` = c("Patient 1", "Patient 1", "Patient 2", "Patient 2", "Patient 2", "Patient 3"),
  `test date` = c("Jan 1 2020", "February 12, 2022", "April 5, 2022", "February 9, 2023", "August 22, 2021", "December 4, 2022"),
  `test result` = c("100 cps/mL", "49 cps/mL", "99 cps/mL", "29000 cps/mL", "800 cps/mL", "500000 cps/mL")
)

# 1. 为每组患者的检测记录添加序号
df_with_num <- df %>%
  group_by(`Patient ID`) %>%
  mutate(test_num = row_number()) %>%
  ungroup()

# 2. 转宽格式并生成目标列名
wide_df <- df_with_num %>%
  pivot_wider(
    id_cols = `Patient ID`,
    names_from = test_num,
    values_from = c(`test date`, `test result`),
    names_sep = " #"
  )

print(wide_df)

关键说明 #

• 分组加序号是核心：只有给同一位患者的每条检测记录分配唯一序号，pivot_wider才能区分不同的检测项，避免列名冲突或数据丢失。
• names_sep = " #"直接将列名组合成test date #1、test result #1的样式，无需额外修改列名。

Python pandas实现 #

同样先添加分组序号，再通过透视完成转换：

import pandas as pd

# 构造示例数据
data = {
    "Patient ID": ["Patient 1", "Patient 1", "Patient 2", "Patient 2", "Patient 2", "Patient 3"],
    "test date": ["Jan 1 2020", "February 12, 2022", "April 5, 2022", "February 9, 2023", "August 22, 2021", "December 4, 2022"],
    "test result": ["100 cps/mL", "49 cps/mL", "99 cps/mL", "29000 cps/mL", "800 cps/mL", "500000 cps/mL"]
}
df = pd.DataFrame(data)

# 1. 为每组患者的检测记录添加序号
df["test_num"] = df.groupby("Patient ID").cumcount() + 1  # 从1开始计数

# 2. 透视转宽格式
wide_df = df.pivot(
    index="Patient ID",
    columns="test_num",
    values=["test date", "test result"]
)

# 3. 调整列名为目标格式
wide_df.columns = [f"{col[0]} #{col[1]}" for col in wide_df.columns]

# 重置索引，让Patient ID成为普通列
wide_df = wide_df.reset_index()

print(wide_df)

关键说明 #

• cumcount() +1生成组内序号，解决透视时同患者多条记录的区分问题。
• 透视后得到多级列名，通过列表推导式合并成test date #1的格式，最后重置索引得到预期结构。

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