如何在R中通过链式法调整价格指数的基准参考年

我有一个包含20年、400个品类月度价格指数的大型DataFrame，需要通过链式法跨年度调整指数，统一基准参考期——即每年1月的指数以上一年1月为基准计算。例如：

• 2018年1月的new_index = 2018年1月的item_index × 2017年12月的new_index / 100
• 2018年2月的new_index = 2018年2月的item_index × 2018年1月的new_index / 100
  依此类推。

我尝试了结合group_by和first的代码，但未得到预期结果，请求编写高效的R实现代码。

尝试的代码 #

df <- df %>% 
  mutate(index_date = ym(index_date)) %>% 
  mutate(out = ifelse(year(index_date)==2017, item_index, item_index * first(new_index) / 100), .by = base_date)

数据集结构 #

df <- structure(list(index_date = structure(c(17167, 17198, 17226, 
17257, 17287, 17318, 17348, 17379, 17410, 17440, 17471, 17501, 
17532, 17563, 17591, 17622, 17652, 17683, 17713, 17744, 17775, 
17805, 17836, 17866, 17897, 17928, 17956, 17987, 18017, 18048, 
18078, 18109, 18140, 18170, 18201, 18231, 18262, 18293, 18322, 
18353, 18383, 18414, 18444, 18475, 18506, 18536, 18567, 18597
), class = "Date"), item_id = c(310405, 310405, 310405, 310405, 
310405, 310405, 310405, 310405, 310405, 310405, 310405, 310405, 
310405, 310405, 310405, 310405, 310405, 310405, 310405, 310405, 
310405, 310405, 310405, 310405, 310405, 310405, 310405, 310405, 
310405, 310405, 310405, 310405, 310405, 310405, 310405, 310405, 
310405, 310405, 310405, 310405, 310405, 310405, 310405, 310405, 
310405, 310405, 310405, 310405), base_date = c(201601, 201701, 
201701, 201701, 201701, 201701, 201701, 201701, 201701, 201701, 
201701, 201701, 201712, 201801, 201801, 201801, 201801, 201801, 
201801, 201801, 201801, 201801, 201801, 201801, 201812, 201901, 
201901, 201901, 201901, 201901, 201901, 201901, 201901, 201901, 
201901, 201901, 201912, 202001, 202001, 202001, 202001, 202001, 
202001, 202001, 202001, 202001, 202001, 202001), item_index = c(98.258, 
99.397, 99.947, 96.607, 98.417, 102.261, 101.719, 102.018, 99.88, 
100.447, 95.759, 95.334, 103.718, 100.758, 97.906, 93.305, 98.987, 
96.349, 93.586, 100.091, 97.8, 95.633, 93.759, 92.471, 101.023, 
97.782, 99.697, 94.008, 97.942, 98.874, 95.886, 99.385, 97.472, 
95.792, 98.138, 95.18, 101.098, 99.525, 98.032, 99.571, 96.245, 
93.816, 95.445, 99.266, 97.008, 99.151, 96.824, 92.32), new_index = c(98.258, 
99.397, 99.947, 96.607, 98.417, 102.261, 101.719, 102.018, 99.88, 
100.447, 95.759, 95.334, 98.87851812, 99.6280172873496, 96.8080019505672, 
92.258601331866, 97.8768787314444, 95.2684634234388, 92.5364499677832, 
98.9684975714892, 96.70319072136, 94.5604932336996, 92.7075098041308, 
91.4339544907452, 92.3693238451855, 90.3205722422993, 92.0894447939346, 
86.834553960382, 90.4683631604516, 91.3292452586887, 88.5692498621946, 
91.8012525035377, 90.0342273383792, 88.4824226977801, 90.6494070351882, 
87.9171224358476, 88.8824524401932, 88.4602607911023, 87.1332457761702, 
88.5011467192248, 85.544916351064, 83.3859615812917, 84.8338567315424, 
88.2300552392822, 86.2230894631826, 88.127840418976, 86.0595457506927, 
82.0562800927864)), row.names = c(NA, -48L), class = "tbl_df", 
"tbl", "data.frame"))

链式调整的核心是按品类分组，按时间顺序计算累积调整因子，每一步的new_index依赖于上一期的结果。以下是高效的实现代码，基于dplyr和lubridate，适合大型数据集：

library(dplyr)
library(lubridate)

df_adjusted <- df %>%
  # 按品类分组，单独处理每个品类的时间序列
  group_by(item_id) %>%
  # 按日期排序，保证时间顺序正确
  arrange(index_date, .by_group = TRUE) %>%
  # 计算链式调整后的new_index
  mutate(
    # 每个品类的首个观测值直接用item_index作为基准起点
    new_index = ifelse(row_number() == 1, item_index, NA),
    # 从第二期开始，用上一期的new_index乘以当前item_index/100，逐行累积计算
    new_index = accumulate(new_index, ~ ifelse(is.na(.y), .x * (.y / 100), .y))
  ) %>%
  ungroup()

代码说明 #

1. 分组与排序：先按item_id分组，再按index_date排序，确保每个品类的时间序列连续，这是链式调整的基础。
2. 初始值设定：每个品类最早日期的new_index直接等于item_index，作为基准起点。
3. 累积计算：使用accumulate函数逐行计算后续的new_index，每一步都以上一期结果为基准，乘以当前item_index除以100，完全匹配链式调整规则。

如果需要更精准地控制年度切换逻辑（确保每年1月严格以上一年12月的new_index为基准），可以使用以下版本：

df_adjusted <- df %>%
  group_by(item_id) %>%
  arrange(index_date, .by_group = TRUE) %>%
  mutate(
    year = year(index_date),
    month = month(index_date)
  ) %>%
  # 提取每年12月的new_index，作为下一年的基准调整因子
  left_join(
    df %>%
      group_by(item_id) %>%
      arrange(index_date) %>%
      filter(month(index_date) == 12) %>%
      mutate(next_year = year(index_date) + 1) %>%
      select(item_id, next_year, prev_year_dec_index = new_index),
    by = c("item_id", "year" = "next_year")
  ) %>%
  # 分阶段计算链式调整
  mutate(
    # 第一年的new_index直接使用item_index
    new_index = ifelse(year == min(year), item_index, NA),
    # 后续年份1月的new_index = 当前item_index × 上一年12月的new_index / 100
    new_index = ifelse(month == 1 & year != min(year), item_index * prev_year_dec_index / 100, new_index),
    # 非1月的月份用上一期的new_index × 当前item_index / 100
    new_index = accumulate(new_index, ~ ifelse(is.na(.y), .x * (.y / 100), .y))
  ) %>%
  select(-year, -month, -prev_year_dec_index) %>%
  ungroup()

这个版本明确区分了年度切换点的调整逻辑，完全符合你“每年1月以上一年1月为基准”的需求。

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