如何在R中按组计算不同月份间变量的差值

需求说明 #

针对数据集里的comp_disc_price和iek_disc_price变量，按competitor_id + MDM_Key分组，计算**相邻月份（大月份数值减小月份数值）**的价格差值，同时生成对应的月份区间标签（如202009-202107）。

示例数据集 #

mydata <- structure(list(
  competitor_id = c(26L, 26L, 26L, 26L, 26L, 26L, 27L, 27L, 27L, 27L, 27L, 27L),
  comp_article_id = c("989EFD73-35F1-8E94-8B77-91478BD1041B", "989EFD73-35F1-8E94-8B77-91478BD1041B", "989EFD73-35F1-8E94-8B77-91478BD1041B", "989EFD73-35F1-8E94-8B77-91478BD1041B", "989EFD73-35F1-8E94-8B77-91478BD1041B", "989EFD73-35F1-8E94-8B77-91478BD1041B", "989EFD73-35F1-8E94-8B77-91478BD1041B", "989EFD73-35F1-8E94-8B77-91478BD1041B", "989EFD73-35F1-8E94-8B77-91478BD1041B", "989EFD73-35F1-8E94-8B77-91478BD1041B", "989EFD73-35F1-8E94-8B77-91478BD1041B", "989EFD73-35F1-8E94-8B77-91478BD1041B"),
  MDM_Key = c(40715L, 40715L, 40715L, 40715L, 40715L, 40715L, 40715L, 40715L, 40715L, 40715L, 40715L, 40715L),
  subgroup_id = c("05.07.2002", "05.07.2002", "05.07.2002", "05.07.2002", "05.07.2002", "05.07.2002", "05.07.2002", "05.07.2002", "05.07.2002", "05.07.2002", "05.07.2002", "05.07.2002"),
  month_id = c(202009L, 202107L, 202204L, 202206L, 202208L, 202210L, 202009L, 202107L, 202204L, 202206L, 202208L, 202210L),
  comp_disc_price = c(1999.2, 2100, 2100, 2940, 2940, 2940, 1999.2, 2100, 2100, 2940, 2940, 2940),
  iek_disc_price = c(1436.63, 1709.09, 1611.12, 1611.12, 1611.12, 1611.12, 1436.63, 1709.09, 1611.12, 1611.12, 1611.12, 1611.12)
), class = "data.frame", row.names = c(NA, -12L))

实现代码 #

使用dplyr包完成分组、排序、差值计算和格式整理：

library(dplyr)

result <- mydata %>%
  # 按指定字段分组
  group_by(competitor_id, MDM_Key) %>%
  # 确保每个组内按月份升序排列
  arrange(month_id, .by_group = TRUE) %>%
  # 计算相邻月份的价格差值（后一个月减前一个月）
  mutate(
    diff_comp_disc_price = lead(comp_disc_price) - comp_disc_price,
    diff_iek_disc_price = lead(iek_disc_price) - iek_disc_price,
    # 生成月份区间标签
    month = paste(month_id, lead(month_id), sep = "-")
  ) %>%
  # 移除最后一行（无后续月份的记录）
  drop_na() %>%
  # 选择并调整输出列的顺序
  select(competitor_id, MDM_Key, subgroup_id, month, diff_comp_disc_price, diff_iek_disc_price) %>%
  # 取消分组状态
  ungroup()

# 查看结果
print(result)

输出结果 #

运行代码后会得到符合需求的输出：

# A tibble: 10 × 6
   competitor_id MDM_Key subgroup_id month         diff_comp_disc_price diff_iek_disc_price
           <int>   <int> <chr>       <chr>                       <dbl>               <dbl>
 1            26   40715 05.07.2002  202009-202107                100.8                272.46
 2            26   40715 05.07.2002  202107-202204                  0                 -97.97
 3            26   40715 05.07.2002  202204-202206                840                   0
 4            26   40715 05.07.2002  202206-202208                  0                   0
 5            26   40715 05.07.2002  202208-202210                  0                   0
 6            27   40715 05.07.2002  202009-202107                100.8                272.46
 7            27   40715 05.07.2002  202107-202204                  0                 -97.97
 8            27   40715 05.07.2002  202204-202206                840                   0
 9            27   40715 05.07.2002  202206-202208                  0                   0
10            27   40715 05.07.2002  202208-202210                  0                   0

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