如何在每个HbA1c_Date_列前添加#Days变量？

我有一份1年期患者数据集，原始数据包含record_id、age、race、lab test、HbA1c日期、数值等50个变量。样本数据集包含10列：record_id、index_date、HbA1c_Date_1、HbA1c_1、HbA1c_Date_2、HbA1c_2、HbA1c_Date_3、HbA1c_3、HbA1c_Date_4、HbA1c_4。其中record_id是患者唯一标识，每次就诊会检测HbA1c，HbA1c_Date_1为首次就诊日期，HbA1c_Date_2为第二次就诊日期。

样本数据集结构 #

structure(list(record_id = c("134844", "134845", "122062", "134846", "110934", "12345","13456", "17890", "190078", "181234", "15843", "18790", "12675"), 
               index.date = c("8/16/2018", "3/28/2019", "2/14/2019", "8/1/2018","8/1/2018", "8/2/2018", "10/4/2018", "6/19/2019", "12/5/2018", "8/23/2018", "3/12/2020", "8/18/2018", "3/10/2020"),
               HbA1c_Date_1 = c("10/2/2019", "8/28/2019", "7/19/2019", "7/10/2019", "4/2/2019", "10/15/2019", "12/4/2019", "10/2/2019",  "2/12/2019", "5/7/2019", "9/11/2019", "5/8/2019", "2/6/2019"),
              HbA1c_1= c("6.6", "8.6", "6.1", "6.9", "10.5", "6.3", "5.5", "5.2", "6.2", "5.7", "5.3", "5.1", "5.4"),
              HbA1c_Date_2 = c("3/5/2019", "10/19/2019", "NA", "3/25/2020", "6/24/2019", "1/9/2019", "11/1/2019", "NA", "12/13/2018", "NA", "NA", "5/26/2020", "5/9/2019"),
              HbA1c_2 = c("7.9", "6.7", "NA", "6.7", "7", "5.8", "5.6", "NA", "6.6", "NA", "NA", "5", "5.5"),
              HbA1c_Date_3 = c("10/8/2019", "NA", "NA", "NA", "1/8/2020", "10/30/2019", "NA", "NA", "3/13/2019", "NA", "NA", "11/23/2019"),
              HbA1c_3 = c("6", "NA", "NA", "NA", "7.5", "5.8", "NA", "NA", "6.6", "NA", "NA", "NA", "5.7"),
              HbA1c_Date_4 = c("1/24/2020","NA", "NA", "NA","NA", "NA", "NA","NA","8/2/2019", "NA","NA", "NA", "2/29/2020"),
              HbA1c_4 = c("7.6", "NA", "NA", "NA", "NA", "NA", "NA", "NA", "6.6", "NA", "NA", "NA", "5.6")), class = "data.frame", row.names = c(NA, -13L))

期望输出结构 #

我需要在每个HbA1c_Date_列前添加#Days列（计算该HbA1c日期与index.date的天数差），最终表格结构示例如下：

尝试的错误代码 #

我曾尝试用names()函数写循环，但未成功，代码如下：

# Get the column names of the dataset
column_names <- names(dataraw)

# Loop through the column names
for (i in seq_along(column_names)) {
  if (grepl("^HbA1c_Date_", column_names[i])) {
    # Add "days" before the current hba1c_date_ variable
    new_name <- c("days", column_names[i])
    
    # Update the column names
    names(dataraw)[i:(i+1)] <- new_name
  }
}

你的现有代码只修改了列名，没有实际计算并插入#Days列。正确的思路是：先将日期转为日期格式，然后对每个HbA1c_Date_列计算天数差，再插入到对应位置。

步骤1：转换日期格式 #

首先把index.date和所有HbA1c_Date_列转为R的日期类型：

library(dplyr)
library(lubridate)

# 转换日期列
dataraw <- dataraw %>%
  mutate(
    index.date = mdy(index.date),
    across(starts_with("HbA1c_Date_"), mdy)
  )

步骤2：插入#Days列并计算天数差 #

我们可以遍历所有HbA1c_Date_列，计算其与index.date的天数差，然后插入到该列之前：

# 获取所有HbA1c_Date列的位置
date_cols <- grep("^HbA1c_Date_", names(dataraw))

# 从后往前插入，避免位置偏移
for (col_pos in rev(date_cols)) {
  # 提取当前HbA1c_Date列的编号（如1、2、3、4）
  num <- sub("HbA1c_Date_", "", names(dataraw)[col_pos])
  # 计算天数差
  days_col <- dataraw[[col_pos]] - dataraw$index.date
  # 插入到当前HbA1c_Date列之前
  dataraw <- dataraw %>%
    mutate(!!paste0("#Days_", num) := days_col) %>%
    relocate(!!paste0("#Days_", num), .before = all_of(col_pos))
}

# 查看结果
names(dataraw)

说明 #

• 使用rev(date_cols)从后往前处理，防止插入列后前面的列位置发生偏移，导致后续计算出错。
• !!和:=是tidyeval语法，用于动态生成列名。
• relocate()函数用于调整列的位置，将计算好的#Days_*列放到对应的HbA1c_Date_*列之前。
• 如果不需要区分#Days的编号，也可以统一命名为#Days，但建议添加编号避免列名重复。

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