使用自定义公式计算基于上年及季度的累加值

问题描述 #

现有如下tibble数据框：

tibble(
  company = rep(x = "google", each = 15),
  date = c(
    "2019q1",
    "2019q2",
    "2019q3",
    "2019q4",
    "2019end",
    "2020q1",
    "2020q2",
    "2020q3",
    "2020q4",
    "2020end",
    "2021q1",
    "2021q2",
    "2021q3",
    "2021q4",
    "2021end"
  ),
  values = c(1:15)
)

需要新增new_value列，计算规则为：当前行values值 + 上一年end对应的values值 + 上年同期（同季度/end）的values值。具体示例：

• 2019q1因无2018年数据，new_value为NA；
• 2020q1的new_value = 2020q1值 + 2019end值 + 2019q1值；
• 2020end的new_value = 2020end值 + 2019end值 + 2019end值；

解决方案 #

通过拆分日期提取年份和周期类型，结合分组匹配获取上年数据，最终计算目标列，步骤如下：

1. 加载依赖包 #

library(tidyverse)

2. 数据处理与计算 #

# 原始数据
df <- tibble(
  company = rep(x = "google", each = 15),
  date = c(
    "2019q1",
    "2019q2",
    "2019q3",
    "2019q4",
    "2019end",
    "2020q1",
    "2020q2",
    "2020q3",
    "2020q4",
    "2020end",
    "2021q1",
    "2021q2",
    "2021q3",
    "2021q4",
    "2021end"
  ),
  values = c(1:15)
)

# 计算new_value
result_df <- df %>%
  # 拆分日期为年份和周期（季度/end）
  mutate(
    year = as.integer(str_extract(date, "\\d{4}")),
    period = str_remove(date, "\\d{4}")
  ) %>%
  # 按公司分组，适配多公司场景
  group_by(company) %>%
  # 获取上年同期的values值
  mutate(prev_period_value = values[match(paste(year - 1, period, sep = ""), date)]) %>%
  # 获取上一年end的values值
  mutate(prev_end_value = values[match(paste(year - 1, "end", sep = ""), date)]) %>%
  # 按规则计算new_value
  mutate(
    new_value = case_when(
      year == 2019 ~ NA_integer_,
      TRUE ~ values + prev_end_value + prev_period_value
    )
  ) %>%
  # 移除中间辅助列
  select(-year, -period, -prev_period_value, -prev_end_value) %>%
  ungroup()

# 查看结果
print(result_df)

结果验证 #

运行后结果符合预期：

• 2019年所有行new_value为NA；
• 2020q1的new_value = 6 + 5 + 1 = 12；
• 2020end的new_value = 10 + 5 + 5 = 20；
• 2021q1的new_value = 11 + 10 + 6 = 27。

内容的提问来源于stack exchange，提问作者Beans On Toast