在R语言中按多分组条件计算滚动差值的实现方法

数据 #

以下是使用R语言构造的数据集：

structure(list(country = c("Australia", "Australia", "Australia", 
"Australia", "Australia", "Australia", "Australia", "Australia", 
"Australia", "New Zealand", "New Zealand", "New Zealand", "New Zealand", 
"New Zealand", "New Zealand", "New Zealand", "New Zealand", "New Zealand"
), year = c(1999L, 1999L, 1999L, 2005L, 2005L, 2005L, 2018L, 
2018L, 2018L, 1999L, 1999L, 1999L, 2005L, 2005L, 2005L, 2018L, 
2018L, 2018L), class = c("Big fish", "Big fish", "Small fish", 
"Big fish", "Big fish", "Small fish", "Big fish", "Big fish", 
"Small fish", "Big fish", "Big fish", "Small fish", "Big fish", 
"Big fish", "Small fish", "Big fish", "Big fish", "Small fish"
), location = c("AAA", "B", "AAA", "AAA", "B", "AAA", "AAA", 
"B", "AAA", "AAA", "B", "AAA", "AAA", "B", "AAA", "AAA", "B", 
"AAA"), value = c(20, 7, 0.1, 15, 14, 0.1, 30, 28, 0.1, 250, 
1000, 30, 100, 2000, 60, 0, 2500, 90)), class = "data.frame", row.names = c(NA, 
-18L))

数据表格展示：

country     year    class       location  value
Australia   1999    Big fish    AAA       20
Australia   1999    Big fish    B         7
Australia   1999    Small fish  AAA       0.1
Australia   2005    Big fish    AAA       15
Australia   2005    Big fish    B         14
Australia   2005    Small fish  AAA       0.1
Australia   2018    Big fish    AAA       30
Australia   2018    Big fish    B         28
Australia   2018    Small fish  AAA       0.1
New Zealand 1999    Big fish    AAA       250
New Zealand 1999    Big fish    B         1000
New Zealand 1999    Small fish  AAA       30
New Zealand 2005    Big fish    AAA       100
New Zealand 2005    Big fish    B         2000
New Zealand 2005    Small fish  AAA       60
New Zealand 2018    Big fish    AAA       0
New Zealand 2018    Big fish    B         2500
New Zealand 2018    Small fish  AAA       90

需求 #

在每个国家内，针对每个class*location分组，计算非连续年份间的年份差值和value滚动差值，期望输出如下：

country     year difference class       location    value difference
Australia   6               Big fish    AAA         -5
Australia   6               Big fish    B           7
Australia   6               Small fish  AAA         0
Australia   13              Big fish    AAA         15
Australia   13              Big fish    B           14
Australia   13              Small fish  AAA         0
New Zealand 6               Big fish    AAA         -150
New Zealand 6               Big fish    B         1000
New Zealand 6               Small fish  AAA         30
New Zealand 13              Big fish    AAA         -100
New Zealand 13              Big fish    B           500
New Zealand 13              Small fish  AAA         30

尝试使用dplyr::group_by()未得到预期结果，需实现上述需求。

通过正确设置分组维度结合dplyr的窗口函数即可实现，具体步骤如下：

1. 按country、class、location分组，确保每个分组内的年份有序；
2. 计算年份差值：当前年份减去上一个年份（year - lag(year)）；
3. 计算value差值：当前value减去上一个value（value - lag(value)）；
4. 过滤掉无前置年份的行（即每个分组的第一行记录）。

代码实现 #

library(dplyr)

# 读取数据（数据框命名为df）
df <- structure(list(country = c("Australia", "Australia", "Australia", 
"Australia", "Australia", "Australia", "Australia", "Australia", 
"Australia", "New Zealand", "New Zealand", "New Zealand", "New Zealand", 
"New Zealand", "New Zealand", "New Zealand", "New Zealand", "New Zealand"
), year = c(1999L, 1999L, 1999L, 2005L, 2005L, 2005L, 2018L, 
2018L, 2018L, 1999L, 1999L, 1999L, 2005L, 2005L, 2005L, 2018L, 
2018L, 2018L), class = c("Big fish", "Big fish", "Small fish", 
"Big fish", "Big fish", "Small fish", "Big fish", "Big fish", 
"Small fish", "Big fish", "Big fish", "Small fish", "Big fish", 
"Big fish", "Small fish", "Big fish", "Big fish", "Small fish"
), location = c("AAA", "B", "AAA", "AAA", "B", "AAA", "AAA", 
"B", "AAA", "AAA", "B", "AAA", "AAA", "B", "AAA", "AAA", "B", 
"AAA"), value = c(20, 7, 0.1, 15, 14, 0.1, 30, 28, 0.1, 250, 
1000, 30, 100, 2000, 60, 0, 2500, 90)), class = "data.frame", row.names = c(NA, 
-18L))

# 计算差值
result <- df %>%
  # 按国家、类别、地点分组
  group_by(country, class, location) %>%
  # 按年份排序，确保分组内年份顺序正确
  arrange(year, .by_group = TRUE) %>%
  # 计算年份差值和value差值
  mutate(
    `year difference` = year - lag(year),
    `value difference` = value - lag(value)
  ) %>%
  # 过滤掉无前置年份的记录
  filter(!is.na(`year difference`)) %>%
  # 选择并调整输出列顺序
  select(country, `year difference`, class, location, `value difference`) %>%
  ungroup()

# 查看完整结果
print(result, n = Inf)

输出结果 #

# A tibble: 12 × 5
   country     `year difference` class       location `value difference`
   <chr>                   <int> <chr>       <chr>                 <dbl>
 1 Australia                   6 Big fish    AAA                     -5
 2 Australia                   6 Big fish    B                        7
 3 Australia                   6 Small fish  AAA                      0
 4 Australia                  13 Big fish    AAA                     15
 5 Australia                  13 Big fish    B                       14
 6 Australia                  13 Small fish  AAA                      0
 7 New Zealand                 6 Big fish    AAA                   -150
 8 New Zealand                 6 Big fish    B                     1000
 9 New Zealand                 6 Small fish  AAA                     30
10 New Zealand                13 Big fish    AAA                   -100
11 New Zealand                13 Big fish    B                      500
12 New Zealand                13 Small fish  AAA                     30

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