R语言：基于id与日期列条件计算DataFrame行间差值

原始数据结构 #

date     id     ...  sum     grouped_sum
<int>   <chr>       <dbl>     <dbl>
202310    1          500       500
202311    1          300       600
202311    1          300       600
202311    2          200       200
202310    3           25        50
202310    3           25        50
202311    4           75       100
202310    4           75        75
202311    4           25       100

尝试过的无效代码 #

df %>% group_by(id, date) %>% 
  mutate(grouped_sum = sum(sum)) %>% 
  mutate(
    diff = case_when(
      id == lag(id) & date != lag(date) ~ grouped_sum - lag(grouped_sum),
      id != lag(id) & date == 202311 ~ grouped_sum,
      id != lag(id) & date == 202310 ~ -grouped_sum
    )
  )

需求说明 #

• 同一ID且包含两个日期时：计算202311的grouped_sum与202310的grouped_sum的差值，仅在202311的行显示该差值，202310的行显示NA
• 仅存在202311的grouped_sum时：diff直接取该grouped_sum值
• 仅存在202310的grouped_sum时：diff取该grouped_sum的相反数

正确的dplyr解决方案 #

核心思路是先按id分组，提取每个ID下两个日期对应的grouped_sum值，再通过case_when匹配场景赋值：

library(dplyr)

df %>%
  # 按id和日期分组计算grouped_sum（若原始数据已有该列可跳过此步）
  group_by(id, date) %>%
  mutate(grouped_sum = sum(sum)) %>%
  ungroup() %>%
  # 按id分组，获取每个id下两个日期的grouped_sum
  group_by(id) %>%
  mutate(
    sum_202310 = first(grouped_sum[date == 202310]),
    sum_202311 = first(grouped_sum[date == 202311]),
    diff = case_when(
      # 同一id有两个日期，当前为202311时计算差值
      !is.na(sum_202310) & !is.na(sum_202311) & date == 202311 ~ sum_202311 - sum_202310,
      # 同一id有两个日期，当前为202310时显示NA
      !is.na(sum_202310) & !is.na(sum_202311) & date == 202310 ~ NA_real_,
      # 仅存在202311时取grouped_sum
      is.na(sum_202310) & !is.na(sum_202311) ~ grouped_sum,
      # 仅存在202310时取相反数
      !is.na(sum_202310) & is.na(sum_202311) ~ -grouped_sum
    )
  ) %>%
  # 移除临时辅助列
  select(-sum_202310, -sum_202311) %>%
  ungroup()

输出结果 #

date     id     ...  sum     grouped_sum   diff
<int>   <chr>       <dbl>     <dbl>        <dbl>
202310    1          500       500         NA
202311    1          300       600         100
202311    1          300       600         100
202311    2          200       200         200
202310    3           25        50         -50
202310    3           25        50         -50
202311    4           75       100          25
202310    4           75        75          NA
202311    4           25       100          25

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