如何在R语言dplyr中按ID分组计算指定规则的Fee差值

需求说明 #

我有如下df数据框，希望按ID计算Fee的差值，规则如下：

• 先执行df %>% arrange(ID, Date) %>% group_by(ID)进行排序和分组；
• 当Test != lag(Test)时，Diff = Fee - lag(Fee)；
• 当Test == lag(Test)时，Diff等于当前Fee与最近一次不同Test对应的Fee的差值。

目前不清楚如何实现第三条规则，恳请指导。

数据结构 #

df<- structure(list(ID = c("Tom", "Tom", "Tom", "Tom", "Jerry", "Jerry", 
"Jerry", "Jerry"), Date = c(20220901, 20220902, 20220903, 20220904, 
20220905, 20220906, 20220907, 20220908), Test = c("ELA", "Art", 
"ELA", "ELA", "Art", "ELA", "ELA", "Math"), Fee = c(78, 98, 66, 
85, 78, 58, 96, 88)), row.names = c(NA, -8L), class = c("tbl_df", 
"tbl", "data.frame"))

实现方案 #

可以通过标记Test的连续分组，再结合fill函数获取最近一次不同Test的Fee值，具体代码如下：

library(dplyr)
library(tidyr)

df_processed <- df %>%
  arrange(ID, Date) %>%
  group_by(ID) %>%
  # 标记连续相同Test的分组：Test与前一行不同时，分组号递增
  mutate(test_group = cumsum(Test != lag(Test, default = first(Test)))) %>%
  # 仅在分组变化时记录前一行的Fee，其余位置设为NA
  mutate(prev_diff_fee = ifelse(test_group != lag(test_group), lag(Fee), NA)) %>%
  # 向下填充NA，让同一连续Test组的所有行拿到最近不同Test的Fee
  fill(prev_diff_fee, .direction = "down") %>%
  # 计算Diff：第一行无前置值设为NA，其余行用当前Fee减去prev_diff_fee
  mutate(Diff = case_when(
    row_number() == 1 ~ NA_real_,
    TRUE ~ Fee - prev_diff_fee
  )) %>%
  # 清理中间变量（可选操作）
  select(-test_group, -prev_diff_fee) %>%
  ungroup()

print(df_processed)

代码解释 #

1. 生成连续Test分组：test_group = cumsum(Test != lag(Test, default = first(Test)))会为连续相同的Test生成同一分组ID，每次Test变化时分组号自动加1，方便后续批量处理同一连续Test的行。
2. 提取基准Fee：prev_diff_fee只在Test分组切换时记录前一行的Fee，确保拿到的是最近一次不同Test的Fee值。
3. 填充基准Fee：fill函数把同一连续Test组内的NA值替换为上一个有效的基准Fee，让组内所有行都能复用这个值。
4. 计算差值：通过case_when处理第一行无前置值的情况，其余行直接用当前Fee减去基准Fee，满足所有规则要求。

运行结果 #

最终输出的数据框如下：

# A tibble: 8 × 4
  ID    Date     Test    Fee  Diff
  <chr>   <dbl> <chr> <dbl> <dbl>
1 Tom   20220901 ELA      78    NA
2 Tom   20220902 Art      98    20
3 Tom   20220903 ELA      66  -32
4 Tom   20220904 ELA      85  -13
5 Jerry 20220905 Art      78    NA
6 Jerry 20220906 ELA      58  -20
7 Jerry 20220907 ELA      96   18
8 Jerry 20220908 Math     88   10

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