如何在R中根据列名自动分配数值，适配新增月份

数据集加载 #

首先加载你提供的数据集：

library(tidyverse)

df <- structure(
  list(
    `2021-05-01.y` = c(0, 0, 1000, 0, 0, 0, 0, 0, 0, 0),
    `2021-06-01.y` = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
    `2021-07-01.y` = c(5000, 0, 4000, 0, 0, 0, 0, 0, 0, 0),
    `2021-08-01.y` = c(0, 0, 12000, 0, 0, 0, 0, 0, 0, 0),
    `2021-09-01.y` = c(3000, 0, 10000, 0, 0, 0, 0, 0, 0, 0),
    `2021-10-01.y` = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
    `2021-11-01.y` = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
    `2021-12-01.y` = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
    `2022-01-01.y` = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
    `2022-02-01.y` = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
    `2022-03-01.y` = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
    `2022-04-01.y` = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
    `2022-05-01.y` = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
  ),
  row.names = c(NA, -10L),
  class = c("data.table", "data.frame")
)

1. 自动重命名日期列为reaload_xx格式 #

无需手动指定每一列，通过字符串匹配自动提取月份并生成新列名：

df_renamed <- df %>%
  rename_with(
    .cols = ends_with(".y"),
    .fn = ~ str_c("reaload_", str_extract(., "\\d{2}(?=-)"))
  )

• 逻辑：str_extract(., "\\d{2}(?=-)")从列名（如2021-05-01.y）中提取月份部分（05），拼接成reaload_05格式；
• 新增月份只要符合YYYY-MM-DD.y命名规则，会自动被处理。

2. 自动生成months_before_reloading列 #

通过宽转长格式的方式，按行自动查找最新的非零值并计算间隔：

# 先按时间顺序整理所有reaload列的顺序
reaload_cols <- df_renamed %>%
  select(starts_with("reaload_")) %>%
  colnames() %>%
  tibble(col = .) %>%
  mutate(
    month = str_extract(col, "\\d{2}$"),
    year = ifelse(month %in% c("01", "02", "03", "04"), 2022, 2021),
    date = ymd(str_c(year, "-", month, "-01"))
  ) %>%
  arrange(date) %>%
  pull(col)

# 转长格式处理间隔计算，再转回宽格式
df_final <- df_renamed %>%
  mutate(row_id = row_number()) %>%
  pivot_longer(
    cols = all_of(reaload_cols),
    names_to = "reload_month",
    values_to = "amount",
    names_transform = list(reload_month = ~ str_extract(., "\\d{2}$"))
  ) %>%
  arrange(row_id, desc(reload_month)) %>% # 按行分组，从最新月份开始排序
  group_by(row_id) %>%
  mutate(
    months_before_reloading = case_when(
      any(amount != 0) ~ which(amount != 0)[1] - 1,
      TRUE ~ as.integer(NA)
    ),
    months_before_reloading = ifelse(is.na(months_before_reloading), "no reload", as.character(months_before_reloading))
  ) %>%
  pivot_wider(
    names_from = "reload_month",
    values_from = "amount",
    names_prefix = "reaload_"
  ) %>%
  ungroup() %>%
  select(-row_id)

• 逻辑：将每行的多月份列转为多行后，按最新月份优先排序，找到第一个非零值的位置，位置减1即为间隔数；全零则标记为no reload；
• 新增月份时，只需保证列名格式正确，代码无需手动调整。

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