R语言中按公司与CEO ID分组计算带滞后的元素差值（含NA处理）

步骤1：数据准备与需求明确 #

你需要基于已计算三年滚动和的data列，按公司（C）和CEO ID分组，计算当前年份值与3年前同组对应年份值的差值；无法回溯到对应年份时结果设为NA，且当data为NA时差值也设为NA。

先加载工具包并导入数据：

library(dplyr)

# 原始数据
ID <- c(1,1,1,1,1,1,3,3,3,5,5,4,4,4,4,4,4,4)
C <- c('a','a','a','a','a','a','b','b','b','b','b','c','c','c','c','c','c','c')
fyear <- c(2000, 2001, 2002,2003,2004,2005,2000, 2001,2002,2003,2004,2000, 2001, 2002,2003,2004,2005,2006)
data <- c(30,50,22,44,68,100,5,3,7,6,9,3,5,6,7,44,33,NA)
df1 <- data.frame(ID,C,fyear, data)

步骤2：计算三年滚动和（若未提前处理） #

如果你的data列还未计算三年滚动和（当年+去年+前年的data之和），可以用zoo包的rollsum函数实现：

library(zoo)

df_with_rollsum <- df1 %>%
  arrange(C, fyear) %>%
  group_by(C) %>%
  mutate(
    # align="right"表示窗口以当前年份为终点，计算当年+去年+前年的和
    data = rollsum(data, k = 3, align = "right", fill = NA)
  ) %>%
  ungroup()

步骤3：分组计算3年前差值 #

使用left_join匹配同组内3年前的data值，确保年份匹配准确（即使年份不连续也能正确对应）：

# 创建辅助表，存储每个观测3年后对应的匹配年份数据
df_3y_match <- df_with_rollsum %>%
  mutate(fyear_target = fyear + 3) %>%
  select(ID, C, fyear_target, data_3y_ago = data)

# 连接原表与辅助表，计算差值并处理NA情况
df_result <- df_with_rollsum %>%
  left_join(df_3y_match, by = c("ID", "C", "fyear" = "fyear_target")) %>%
  mutate(
    diff = case_when(
      is.na(data) ~ NA_real_,
      is.na(data_3y_ago) ~ NA_real_,
      TRUE ~ data - data_3y_ago
    )
  ) %>%
  arrange(C, ID, fyear) %>%
  select(ID, C, fyear, data, diff)

问题排查说明 #

你之前得到全0结果，大概率是以下原因：

• 分组逻辑错误：仅按公司分组，未加入CEO ID，导致跨CEO的数据被错误匹配
• 年份匹配方式错误：使用固定行偏移（如lag）而非年份精准匹配，当组内年份不连续时会出错

输出示例（基于原始数据计算三年滚动和后） #

ID C fyear data diff
1   1 a  2000   NA   NA
2   1 a  2001   NA   NA
3   1 a  2002  102   NA
4   1 a  2003  116   NA
5   1 a  2004  134   NA
6   1 a  2005  212  110
7   3 b  2000   NA   NA
8   3 b  2001   NA   NA
9   3 b  2002   15   NA
10  5 b  2003   NA   NA
11  5 b  2004   NA   NA
12  4 c  2000   NA   NA
13  4 c  2001   NA   NA
14  4 c  2002   14   NA
15  4 c  2003   18    3
16  4 c  2004   57   43
17  4 c  2005   84   70
18  4 c  2006   NA   NA

内容的提问来源于stack exchange，提问作者Erika Dal Cortivo