分组变量执行lag/lead时间序列操作时出现NA值的技术求助

在按分组执行滞后（lag）和超前（lead）操作时，所有结果都返回NA。尝试过dplyr::lag和data.table的分组操作，相关数据、代码及问题如下：

测试数据 #

test = structure(list(cnty90 = c(1005, 1005, 1005, 1005, 1005, 1005, 
                                 1005, 1005, 1005, 1005, 1005, 1005, 1005, 1005, 1005, 1005, 1015, 
                                 1015, 1015, 1015, 1015, 1015, 1015, 1015, 1015, 1015, 1015, 1015, 
                                 1015, 1015, 1015, 1015, 1031, 1031, 1031, 1031, 1031, 1031, 1031, 
                                 1031, 1031, 1031, 1031, 1031, 1031, 1031, 1031, 1031, 1039, 1039), 
                      year = c(1868, 1872, 1876, 1880, 1884, 1888, 1892, 1896, 1900,
                               1904, 1908, 1912, 1916, 1920, 1924, 1928, 1868, 1872, 1876, 1880,
                               1884, 1888, 1892, 1896, 1900, 1904, 1908, 1912, 1916, 1920, 1924,
                               1928, 1868, 1872, 1876, 1880, 1884, 1888, 1892, 1896, 1900, 1904,
                               1908, 1912, 1916, 1920, 1924, 1928, 1868, 1872), 
                      numdailies = c(0, 0, 0, 0, 2, 2, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 0, 0, 0, 0, 
                                     0, 2,2, 1, 1, 2, 2, 2, 1, 1, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 
                                     0, 0, 0, 0, 1, 0, 0, 0, 0, 0)), row.names = c(NA, 50L), class = "data.frame")

原错误代码及结果 #

dplyr版本 #

test %>% 
    group_by(cnty90, year) %>% 
    mutate(numdailies_l1 = lag(numdailies, n = 1),
           changedailies_for=lead(numdailies, n=1))

执行后所有滞后和超前列均为NA：

# A tibble: 50 x 6
# Groups:   cnty90, year [50]
   cnty90  year numdailies numdailies_l1 changedailies changedailies_for
    <dbl> <dbl>      <dbl>         <dbl>         <dbl>             <dbl>
 1   1005  1868          0            NA            NA                NA
 2   1005  1872          0            NA            NA                NA
 3   1005  1876          0            NA            NA                NA
 4   1005  1880          0            NA            NA                NA
 5   1005  1884          2            NA            NA                NA
 6   1005  1888          2            NA            NA                NA
 7   1005  1892          2            NA            NA                NA
 8   1005  1896          1            NA            NA                NA
 9   1005  1900          1            NA            NA                NA
10   1005  1904          1            NA            NA                NA
# ... with 40 more rows

data.table版本 #

test = data.table::data.table(test)
test[, ":=" (numdailies_l1 = lag(numdailies, n = 1),
             changedailies=numdailies-numdailies_l1,
             changedailies_for=lead(numdailies, n=1)), by = .(cnty90, year)]

期望结果 #

# A tibble: 20 x 6
   cnty90  year numdailies numdailies_l1 changedailies changedailies_for
    <int> <int>      <int>         <int>         <int>             <int>
 1   1005  1868          0            NA            NA                 0
 2   1005  1872          0             0             0                 0
 3   1005  1876          0             0             0                 0
 4   1005  1880          0             0             0                 2
 5   1005  1884          2             0             2                 0
 6   1005  1888          2             2             0                 0
 7   1005  1892          2             2             0                -1
 8   1005  1896          1             2            -1                 0
 9   1005  1900          1             1             0                 0
10   1005  1904          1             1             0                 0
11   1005  1908          1             1             0                 0
12   1005  1912          1             1             0                 0
13   1005  1916          1             1             0                 0
14   1005  1920          1             1             0                 0
15   1005  1924          1             1             0                 0
16   1005  1928          1             1             0                NA
17   1015  1868          0            NA            NA                 0
18   1015  1872          0             0             0                 0
19   1015  1876          0             0             0                 0
20   1015  1880          0             0             0                 0

问题原因及修正方案 #

问题核心 #

错误地将year加入分组条件，每个cnty90+year组合仅对应一行数据，导致lag()和lead()无法找到同组内的其他行，因此返回NA。正确的分组逻辑应该是仅按地区cnty90分组，同时确保每个分组内的数据按year排序（保证滞后/超前的顺序正确）。

修正后的dplyr代码 #

library(dplyr)

test %>%
  arrange(cnty90, year) %>%  # 显式按地区和年份排序，确保顺序正确
  group_by(cnty90) %>%       # 仅按cnty90分组
  mutate(
    numdailies_l1 = lag(numdailies, n = 1),
    changedailies = numdailies - numdailies_l1,
    changedailies_for = lead(numdailies, n = 1)
  ) %>%
  ungroup()  # 可选，取消分组状态

修正后的data.table代码 #

library(data.table)

setDT(test)
test[order(cnty90, year),  # 先按地区和年份排序
     `:=`(
       numdailies_l1 = lag(numdailies, n = 1),
       changedailies = numdailies - numdailies_l1,
       changedailies_for = lead(numdailies, n = 1)
     ),
     by = cnty90]  # 仅按cnty90分组

执行上述修正代码后，即可得到符合预期的滞后/超前计算结果。

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