如何在dplyr中按患者ID计算N-1累积平均值与前次预约值？

数据准备 #

library(dplyr)

my_data = data.frame(patient_id = c(1,1,1,1, 2,2,2), 
                     age = c(43, 43, 44, 44, 21, 21, 21), 
                     gender = c("M", "M", "M", "M", "F", "F", "F"), 
                     appointment_number = c(1,2,3,4,1,2,3), 
                     missed = c(0, 0, 1, 1, 1, 1, 1))

需求说明 #

按patient_id分组后，需创建两个变量：

• previous_apt：取当前行前一次预约的missed值
• cummean_prev：取当前行前N-1次预约的missed值累积平均值（N为当前行的预约次数，即仅计算当前行之前所有行的平均值）

原尝试代码及问题 #

尝试代码 #

my_data_final <- my_data %>% 
  group_by(patient_id) %>% 
  mutate(cummean = cumsum(missed)/(row_number() - 1)) %>% 
  mutate(previous_apt = lag(missed))

错误结果 #

# A tibble: 7 x 7
# Groups:   patient_id [2]
  patient_id   age gender appointment_number missed cummean previous_apt
       <dbl> <dbl> <chr>               <dbl>  <dbl>   <dbl>        <dbl>
1          1    43 M                       1      0 NaN               NA
2          1    43 M                       2      0   0                0
3          1    44 M                       3      1   0.5              0
4          1    44 M                       4      1   0.667            1
5          2    21 F                       1      1 Inf               NA
6          2    21 F                       2      1   2                1
7          2    21 F                       3      1   1.5              1

问题原因 #

cumsum(missed)计算的是当前行及之前所有行的missed总和，而需求是仅计算前N-1次（即当前行之前的行）的平均值，因此分子多包含了当前行的missed值，导致结果超出0-1的合理范围。

修正方案 #

正确代码 #

my_data_final <- my_data %>% 
  group_by(patient_id) %>% 
  mutate(
    previous_apt = lag(missed),
    # 前N-1次总和 = 累计总和 - 当前行missed值
    # 前N-1次数量 = 当前行号 - 1
    cummean_prev = (cumsum(missed) - missed) / (row_number() - 1)
  )

正确结果 #

# A tibble: 7 x 7
# Groups:   patient_id [2]
  patient_id   age gender appointment_number missed previous_apt cummean_prev
       <dbl> <dbl> <chr>               <dbl>  <dbl>        <dbl>        <dbl>
1          1    43 M                       1      0           NA         NaN
2          1    43 M                       2      0            0           0
3          1    44 M                       3      1            0           0
4          1    44 M                       4      1            1           0.5
5          2    21 F                       1      1           NA         Inf
6          2    21 F                       2      1            1           1
7          2    21 F                       3      1            1           1

自行尝试代码的验证 #

你编写的代码是正确的：

my_data %>% 
  group_by(patient_id) %>% 
  mutate(previous_apt = lag(missed)) %>% 
  mutate(cummean = (cumsum(missed) - missed) / (row_number() - 1)) %>%  
  mutate(previous_apt_2 = lag(missed, 2))

其中(cumsum(missed) - missed)/(row_number()-1)准确实现了前N-1次累积平均值的计算，previous_apt_2 = lag(missed, 2)是取当前行往前第2次的missed值，若有相关需求可保留，不影响核心逻辑。

补充：首次预约（row_number=1）时row_number()-1=0，会产生NaN或Inf，可根据需求用replace_na替换，例如：

mutate(cummean_prev = replace_na((cumsum(missed) - missed)/(row_number()-1), 0))

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