R语言如何基于月度环比(MoM)数据计算base_effect和new_price_effect

前提说明 #

我们有示例数据框df，为模拟CPI数据，包含date和MoM（环比）两列，需要基于这两列计算两个指标：

• base_effect：翘尾因素
• new_price_effect：新涨价因素

示例数据预览：

date    MoM  base_effect  new_price_effect
0   2019-01  1.010          NA               NA
1   2019-02  1.010          NA               NA
2   2019-03  1.010          NA               NA
3   2019-04  1.010          NA               NA
4   2019-05  1.010          NA               NA
5   2019-06  1.010          NA               NA
6   2019-07  1.010          NA               NA
7   2019-08  1.010          NA               NA
8   2019-09  1.010          NA               NA
9   2019-10  1.010          NA               NA
10  2019-11  1.010          NA               NA
11  2019-12  1.010          NA               NA
12  2020-01  1.015     1.115668          1.015000
13  2020-02  1.015     1.104622          1.030225
14  2020-03  1.015     1.093685          1.045678
15  2020-04  1.015     1.082857          1.061364
16  2020-05  1.015     1.072135          1.077284
17  2020-06  1.015     1.061520          1.093443
18  2020-07  1.015     1.051010          1.109845
19  2020-08  1.015     1.040604          1.126493
20  2020-09  1.015     1.030301          1.143390
21  2020-10  1.015     1.020100          1.160541
22  2020-11  1.015     1.010000          1.177949
23  2020-12  1.015     1.000000          1.195618

示例数据的R结构定义：

structure(list(date = c("2019-1-1", "2019-2-1", "2019-3-1", "2019-4-1", 
"2019-5-1", "2019-6-1", "2019-7-1", "2019-8-1", "2019-9-1", "2019-10-1", 
"2019-11-1", "2019-12-1", "2020-1-1", "2020-2-1", "2020-3-1", 
"2020-4-1", "2020-5-1", "2020-6-1", "2020-7-1", "2020-8-1", "2020-9-1", 
"2020-10-1", "2020-11-1", "2020-12-1"), MoM = c(1.01, 1.01, 1.01, 
1.01, 1.01, 1.01, 1.01, 1.01, 1.01, 1.01, 1.01, 1.01, 1.015, 
1.015, 1.015, 1.015, 1.015, 1.015, 1.015, 1.015, 1.015, 1.015, 
1.015, 1.015)), class = "data.frame", row.names = c(NA, -24L))

指标计算逻辑 #

翘尾因素（base_effect） #

计算规则：以上一年12月的MoM为固定终点，取值为上年对应月份次月到上年12月的MoM乘积，每年12月的翘尾因素恒为1.00，第一年数据无上年基数，取值为NA。
示例：

• 计算2020-06的base_effect时，取值为MoM(2019-07) * MoM(2019-08) * MoM(2019-09) * MoM(2019-10) * MoM(2019-11) * MoM(2019-12) = 1.061520
• 计算2020-09的base_effect时，取值为MoM(2019-10) * MoM(2019-11) * MoM(2019-12) = 1.030301

新涨价因素（new_price_effect） #

计算规则：以当年1月的MoM为固定起点，取值为当年1月到当前月份的MoM累计乘积，第一年数据取值为NA。
示例：

• 计算2020-04的new_price_effect时，取值为MoM(2020-01) * MoM(2020-02) * MoM(2020-03) * MoM(2020-04) = 1.061364
• 计算2020-06的new_price_effect时，取值为MoM(2020-01) * MoM(2020-02) * MoM(2020-03) * MoM(2020-04) * MoM(2020-05) * MoM(2020-06) = 1.093443

R实现代码 #

# 加载依赖包
library(dplyr)
library(lubridate)

# 1. 导入/构造数据
df <- structure(list(date = c("2019-1-1", "2019-2-1", "2019-3-1", "2019-4-1", 
"2019-5-1", "2019-6-1", "2019-7-1", "2019-8-1", "2019-9-1", "2019-10-1", 
"2019-11-1", "2019-12-1", "2020-1-1", "2020-2-1", "2020-3-1", 
"2020-4-1", "2020-5-1", "2020-6-1", "2020-7-1", "2020-8-1", "2020-9-1", 
"2020-10-1", "2020-11-1", "2020-12-1"), MoM = c(1.01, 1.01, 1.01, 
1.01, 1.01, 1.01, 1.01, 1.01, 1.01, 1.01, 1.01, 1.01, 1.015, 
1.015, 1.015, 1.015, 1.015, 1.015, 1.015, 1.015, 1.015, 1.015, 
1.015, 1.015)), class = "data.frame", row.names = c(NA, -24L))

# 2. 日期字段预处理
df <- df %>%
  mutate(
    date = ymd(date),
    year = year(date),
    month = month(date)
  )

# 3. 计算新涨价因素：按年分组正向累计乘积
df <- df %>%
  group_by(year) %>%
  mutate(new_price_effect = cumprod(MoM)) %>%
  ungroup()

# 4. 计算翘尾因素：上年数据反向累计乘积后匹配到本年
# 4.1 计算上年各月的反向累计乘积
prev_year_cum <- df %>%
  group_by(year) %>%
  arrange(desc(month)) %>%
  mutate(rev_cum = cumprod(MoM)) %>%
  ungroup() %>%
  select(year, month, rev_cum) %>%
  mutate(year = year + 1) # 年份+1匹配下一年

# 4.2 匹配翘尾因素值
df <- df %>%
  left_join(prev_year_cum, by = c("year", "month")) %>%
  mutate(
    # 12月翘尾固定为1
    base_effect = ifelse(month == 12, 1, rev_cum),
    # 首年无上年基数，两个指标设为NA
    base_effect = ifelse(year == min(year), NA, base_effect),
    new_price_effect = ifelse(year == min(year), NA, new_price_effect)
  ) %>%
  # 清理辅助字段
  select(-year, -month, -rev_cum)

# 输出结果验证
print(df, digits = 7)

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