在R中动态生成列并从exit_r偏移后的vz列赋值的实现方法

问题描述 #

需要在R中为数据集动态创建4个新列（IncAfterRepas1到IncAfterRepas4），规则如下：

• 对每行的exit_r日期，分别往后推1-4个月
• 找到数据集中以vz开头、对应推后月份（格式YYYYMM）的列
• 将该列对应行的值赋值给新列

例如：当exit_r为2015-03-22时，推后1个月是2015-04，对应vz201504列，该行的值就赋值给IncAfterRepas1。

原尝试的循环代码无法运行，需要修正实现方式。

原代码问题分析 #

原循环代码的核心问题是：

df[, paste0("vz", format((df$exit_r %m+% months(m)), "%Y%m"))]

这段代码生成的是长度等于行数的列名字符向量，直接用df[, 向量]会尝试按列索引提取整列，得到的是矩阵而非每行对应的值，导致赋值错误。我们需要逐行匹配对应的列并提取值。

解决方案 #

方法1：基础R循环（逐行处理） #

需要先加载lubridate包（用于日期运算）：

library(lubridate)

# 初始化数据集
exit_r <- as.Date(c("2015-03-22","2015-04-29","2015-05-23","2015-05-03","2015-04-29","2015-03-18","2015-07-14","2015-04-08","2015-02-11","2015-04-27"))
vz201502  <- as.numeric(c("0.00","3.00","5.00","0.00","0.00","21","0.00","0.00","0.00","0.00"))
vz201503  <- as.numeric(c("0.00","2.00","0.00","0.00","0.00","21","21.00","0.00","0.00","0.00"))
vz201504 <- as.numeric(c("500.00","5.00","8.00","0.00","0.00","21.00","0.00","0.00","0.00","482"))
vz201505 <- as.numeric(c("0.00","6.00","9.00","10.00","12.00","0.00","5.00","0.00","8.00","664 "))
vz201506 <- as.numeric(c("3.00","4.00","5.00","0.00","9.00","8.00","1.00","4.00","0.00","0.00"))
vz201507 <- as.numeric(c("0.00","4.00","3.00","5.00","0.00","2.00","0.00","0.00","1.00","2.00"))
df <- data.frame(exit_r,vz201502,vz201503, vz201504, vz201505,vz201506,vz201507)

# 循环创建4个新列
for (m in 1:4) {
  # 生成每行对应的目标列名
  target_cols <- paste0("vz", format(df$exit_r %m+% months(m), "%Y%m"))
  # 逐行提取对应列的值，列不存在则返回NA
  df[[paste0("IncAfterRepas", m)]] <- sapply(1:nrow(df), function(i) {
    col_name <- target_cols[i]
    if (col_name %in% colnames(df)) df[i, col_name] else NA
  })
}

方法2：tidyverse 高效实现（适合大数据） #

用tidyr的长格式转换+匹配，避免循环，代码更简洁：

library(lubridate)
library(tidyverse)

# 初始化数据集
exit_r <- as.Date(c("2015-03-22","2015-04-29","2015-05-23","2015-05-03","2015-04-29","2015-03-18","2015-07-14","2015-04-08","2015-02-11","2015-04-27"))
vz201502  <- as.numeric(c("0.00","3.00","5.00","0.00","0.00","21","0.00","0.00","0.00","0.00"))
vz201503  <- as.numeric(c("0.00","2.00","0.00","0.00","0.00","21","21.00","0.00","0.00","0.00"))
vz201504 <- as.numeric(c("500.00","5.00","8.00","0.00","0.00","21.00","0.00","0.00","0.00","482"))
vz201505 <- as.numeric(c("0.00","6.00","9.00","10.00","12.00","0.00","5.00","0.00","8.00","664 "))
vz201506 <- as.numeric(c("3.00","4.00","5.00","0.00","9.00","8.00","1.00","4.00","0.00","0.00"))
vz201507 <- as.numeric(c("0.00","4.00","3.00","5.00","0.00","2.00","0.00","0.00","1.00","2.00"))
df <- data.frame(exit_r,vz201502,vz201503, vz201504, vz201505,vz201506,vz201507)

df_new <- df %>%
  # 保留exit_r，将vz开头的列转成长格式
  pivot_longer(cols = starts_with("vz"), names_to = "month_col", values_to = "value") %>%
  # 提取vz列中的年份月份并转成日期
  mutate(month_date = ymd(paste0(str_remove(month_col, "vz"), "01"))) %>%
  # 为每行生成1-4个月后的目标日期
  crossing(month_offset = 1:4) %>%
  mutate(target_month = exit_r %m+% months(month_offset)) %>%
  # 匹配目标月份和vz列的月份
  filter(floor_date(target_month, "month") == month_date) %>%
  # 生成新列名
  mutate(new_col = paste0("IncAfterRepas", month_offset)) %>%
  # 转回宽格式
  pivot_wider(id_cols = exit_r, names_from = new_col, values_from = value) %>%
  # 和原数据集合并
  right_join(df, by = "exit_r") %>%
  # 调整列顺序（可选）
  select(exit_r, starts_with("vz"), starts_with("IncAfterRepas"))

结果验证 #

运行后IncAfterRepas1列的值会和你提供的预期示例一致，比如第7行（exit_r=2015-07-14）推后1个月是2015-08，数据集中没有vz201508列，所以对应值为NA，符合预期。

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