如何将随访医疗数据转换为匹配随访编号的长格式数据框？

我有个体的4次随访医疗数据，需要研究其中3个变量的效应，要把宽格式数据转成特定长格式：

• 将COMP_MEM_FU1/FU2/FU3合并为单列comp_mem
• 将bp_FU1/FU2/FU3合并为单列BP
• 将heart_rate_FU1/FU2/FU3合并为单列heart_rate
• 同时生成对应随访编号的follow_up列

但我多次单独用pivot_longer处理每个变量后，始终无法让3个变量和正确的随访编号匹配。

尝试的代码 #

df_TIME <- df %>%
  mutate(time_years = as.numeric(HR_FU1) - as.numeric(HR_FU3))

df_long_final <- df_TIME %>%
       mutate(across(starts_with("COMP_MEM"), as.numeric)) %>%
       pivot_longer(cols = starts_with("COMP_MEM"), names_to = "follow_up", values_to = "comp_mem") %>%
       mutate(follow_up = gsub("^COMP_MEM_FU", "", follow_up))
 
# 处理缺失值
df_long_final$CRP[df_long_final$BP=="-96"] <- NA
df_long_final$CRP[df_long_final$BP=="-99"] <- NA
df_long_final$CRP[df_long_final$BP=="83"] <- NA

df_long_final1 <- df_long_final %>%
  pivot_longer(cols = starts_with("HR_FU"),
               names_to = "temp",
               values_to = "HR_FU",
               names_pattern = "HR_FU(\\d+)") %>%
  select(-temp) %>%
  arrange(famnr, follow_up)

df_long_final1 <- df_long_final %>%
  pivot_longer(cols = starts_with("BP"),
               names_to = "temp",
               values_to = "BP",
               names_pattern = "BP(\\d+)") %>%
  select(-temp) %>%
  arrange(famnr, follow_up)

数据样本 #

> dput(head(df))
structure(list(COMP_MEM_FU1 = c(-0.1278462, 0.651491203, -0.523100556, 
-0.577777305, -1108359738, -0.623475318), COMP_MEM_FU2 = c("-0.08989273", 
"0.89857704", "-0.073899931", "0.15776524", NA, NA), COMP_MEM_FU3 = c("-0.10930318", 
"0.033529036", "0.116955388", "-0.356199591", NA, NA), bp_FU1 = c(1, 
1, 1, 1, 3, 1), bp_FU2 = c(1, 1, 1, 1, NA, NA), bp_FU3 = c(1.1, 
0.5, 0.4, 1, NA, NA), AGE = c("71.0", "71.0", "65.5", "65.5", 
"78.1", "78.1"), heart_rate = c(70.9, 70.9, 65.5, 65.5, 77.9, 
77.9), heart_rate_FU1 = c(73.1, 73.1, 67.7, 67.7, 80.2, 80.2), 
   heart_rate_FU2 = c(75.3, 75.3, 69.9, 69.9, NA, NA), heart_rate_FU3 = c(77.7, 
    77.7, 72.3, 72.3, NA, NA), GENDER = c(0, 0, 1, 1, 1, 1), 
    EDU_YEARS = c(13, 17, 9, 9, 8, 9), famnr = c(1, 1, 
    2, 2, 3, 3), binding = c("0.116957518", "0.134414065", 
    "0.040922909", "0.058799312", "0.273736362", "0.065468945"
    ), id = c("id_301", "id_302", "id_303", "id_304", "id_305", 
    "id_306"), Twin_Number = c(1, 2, 1, 2, 1, 2)), row.names = c(NA, 
6L), class = "data.frame")

你之前的问题在于多次单独使用pivot_longer会导致数据行数重复或随访编号不匹配，正确的做法是一次性处理所有需要转换的随访变量，利用pivot_longer的names_pattern参数拆分变量名，同时生成变量类型和随访编号，确保三者一一对应。

步骤1：清理数据类型 #

先统一COMP_MEM系列变量的数值类型（样本中FU2/FU3为字符型）：

df_clean <- df %>%
  mutate(across(starts_with("COMP_MEM"), as.numeric)) %>%
  mutate(AGE = as.numeric(AGE)) # 可选：将AGE转为数值型方便后续分析

步骤2：一次性转换为目标长格式 #

使用正则表达式拆分变量名，一次完成所有变量的合并与随访编号提取：

df_long <- df_clean %>%
  pivot_longer(
    cols = matches("(COMP_MEM|bp|heart_rate)_FU\\d+"), # 匹配所有需转换的随访变量
    names_to = c(".value", "follow_up"), # .value保留变量名前缀为新列名，follow_up存储随访编号
    names_pattern = "(.*)_FU(\\d+)" # 正则拆分：第一组是变量前缀，第二组是随访编号
  ) %>%
  rename(
    comp_mem = COMP_MEM, # 重命名为你需要的列名
    BP = bp
  ) %>%
  arrange(id, follow_up) # 按个体ID和随访编号排序

步骤3：处理特定缺失值（可选） #

如果需要将BP的特定值转为NA，直接在转换后操作：

df_long <- df_long %>%
  mutate(
    CRP = case_when(
      BP %in% c(-96, -99, 83) ~ NA_real_,
      TRUE ~ CRP # 若原数据无CRP列可删除此行
    )
  )

关键逻辑解释 #

• names_pattern = "(.*)_FU(\\d+)"：将COMP_MEM_FU1拆分为变量前缀COMP_MEM和随访编号1
• names_to = c(".value", "follow_up")：.value会自动将相同前缀的变量合并为一列，follow_up存储对应的随访编号，从根源保证三个变量与随访编号的匹配性
• 仅一次pivot_longer操作，避免了多次转换导致的行数膨胀或匹配错位问题

最终数据结构示例 #

转换后的数据会呈现如下格式（部分列）：

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