在R中基于不同列实现迭代计算生成dataframe新列

需求说明 #

• 新列dia_year生成规则：
  • 首行值为对应行的dia列值
  • 从第二行开始，值为上一行的dia_year值减去当前行的wd列值
  • 数据需保持年份降序排列

当前DataFrame示例 #

> df
   year      id    wd      dia
1  2018 A218t05 0.443 403.1944
2  2017 A218t05 0.422       NA
3  2016 A218t05 0.178       NA
4  2015 A218t05 0.323       NA
5  2014 A218t05 0.132       NA
6  2013 A218t05 0.538       NA
7  2012 A218t05 0.236       NA
8  2011 A218t05 0.244       NA
9  2010 A218t05 0.157       NA
10 2009 A218t05 0.207       NA
11 2008 A218t05 0.306       NA
12 2007 A218t05 0.157       NA
13 2006 A218t05 0.207       NA
14 2005 A218t05 0.145       NA
15 2004 A218t05 0.240       NA
16 2003 A218t05 0.232       NA
17 2002 A218t05 0.344       NA
18 2001 A218t05 0.600       NA
19 2000 A218t05 0.559       NA

期望输出 #

> desired
   year      id    wd      dia dia_year
1  2018 A218t05 0.443 403.1944 403.1944
2  2017 A218t05 0.422        . 402.7724
3  2016 A218t05 0.178        . 402.5944
4  2015 A218t05 0.323        . 402.2714
5  2014 A218t05 0.132        . 402.1394
6  2013 A218t05 0.538        . 401.6014
7  2012 A218t05 0.236        . 401.3654
8  2011 A218t05 0.244        . 401.1214
9  2010 A218t05 0.157        . 400.9644
10 2009 A218t05 0.207        . 400.7574
11 2008 A218t05 0.306        . 400.4514
12 2007 A218t05 0.157        . 400.2944
13 2006 A218t05 0.207        . 400.0874
14 2005 A218t05 0.145        . 399.9424
15 2004 A218t05 0.240        . 399.7024
16 2003 A218t05 0.232        . 399.4704
17 2002 A218t05 0.344        . 399.1264
18 2001 A218t05 0.600        . 398.5264
19 2000 A218t05 0.559        . 397.9674

尝试的代码及问题 #

使用dplyr代码后，除首行外dia_year均为NA：

df.test <- df %>%
  dplyr::group_by(id) %>%
  dplyr::arrange(id, -year) %>%
  dplyr::mutate(dia_year = ifelse(year=="2018", dia, dia - lead(wd, default=first(wd))))
df.test
    year id         wd   dia dia_year
   <int> <chr>   <dbl> <dbl>    <dbl>
 1  2018 A218t05 0.443  403.     403.
 2  2017 A218t05 0.422   NA       NA 
 3  2016 A218t05 0.178   NA       NA 
 4  2015 A218t05 0.323   NA       NA 
 5  2014 A218t05 0.132   NA       NA 
 6  2013 A218t05 0.538   NA       NA 
 7  2012 A218t05 0.236   NA       NA 
 8  2011 A218t05 0.244   NA       NA 
 9  2010 A218t05 0.157   NA       NA 
10  2009 A218t05 0.207   NA       NA 
11  2008 A218t05 0.306   NA       NA 
12  2007 A218t05 0.157   NA       NA 
13  2006 A218t05 0.207   NA       NA 
14  2005 A218t05 0.145   NA       NA 
15  2004 A218t05 0.24    NA       NA 
16  2003 A218t05 0.232   NA       NA 
17  2002 A218t05 0.344   NA       NA 
18  2001 A218t05 0.6     NA       NA 
19  2000 A218t05 0.559   NA       NA 

原始数据集包含293个唯一id，约57k行，优先使用dplyr实现，其他方法也可。

附DataFrame结构：

df <- structure(list(year = 2018:2000, id = c("A218t05", "A218t05", 
"A218t05", "A218t05", "A218t05", "A218t05", "A218t05", "A218t05", 
"A218t05", "A218t05", "A218t05", "A218t05", "A218t05", "A218t05", 
"A218t05", "A218t05", "A218t05", "A218t05", "A218t05"), wd = c(0.443, 
0.422, 0.178, 0.323, 0.132, 0.538, 0.236, 0.244, 0.157, 0.207, 
0.306, 0.157, 0.207, 0.145, 0.24, 0.232, 0.344, 0.6, 0.559), 
    dia = c(403.1944, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, NA)), row.names = c(NA, -19L), class = "data.frame") 

解决方案 #

方法1：dplyr结合累积计算 #

核心思路是用cumsum()对wd列做累积求和，再用首行的dia值减去该累积和（首行累积和为0，后续行对应前面所有wd的总和）：

library(dplyr)

df_result <- df %>%
  group_by(id) %>%
  arrange(-year) %>% # 确保年份降序，通用场景需保留
  mutate(
    # 生成累积wd：首行0，后续为前n-1行wd的和
    cumulative_wd = c(0, cumsum(wd[-n()])),
    dia_year = first(dia) - cumulative_wd
  ) %>%
  ungroup()

方法2：purrr包的accumulate()递推 #

如果需要更直观的递推逻辑，可使用accumulate()实现逐行计算：

library(dplyr)
library(purrr)

df_result <- df %>%
  group_by(id) %>%
  arrange(-year) %>%
  mutate(
    dia_year = accumulate(
      .x = wd,
      .f = ~ .x - .y,
      .init = first(dia)
    ) %>% tail(-1) # 去掉初始值，匹配数据行数
  ) %>%
  ungroup()

验证结果 #

两种方法均能生成符合期望的输出，且对57k行、293个分组的大规模数据处理效率友好。

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