在R中按ID基于eventdate填充order列的实现方法问询

需求说明 #

按ID分组后：

• eventdate非NA的行，order值设为0
• 该行上方的行依次填充连续递减的负整数
• 该行下方的行依次填充连续递增的正整数

原始数据表 #

+----+------------+-------+
| ID | eventdate  | Value |
+----+------------+-------+
|  1 | NA         |    10 |
|  1 | NA         |    11 |
|  1 | NA         |    12 |
|  1 | NA         |    11 |
|  1 | 2011-03-18 |    15 |
|  1 | NA         |    17 |
|  1 | NA         |    18 |
|  1 | NA         |    15 |
|  2 | NA         |     5 |
|  2 | NA         |     6 |
|  2 | NA         |     7 |
|  2 | 2011-05-28 |     9 |
|  2 | NA         |    10 |
|  2 | NA         |    11 |
|  2 | NA         |    15 |
|  2 | NA         |    16 |
|  3 | NA         |    20 |
|  3 | NA         |    22 |
|  3 | NA         |    23 |
|  3 | NA         |    24 |
|  3 | 2012-05-28 |    28 |
|  3 | NA         |    29 |
|  3 | NA         |    25 |
|  3 | NA         |    24 |
|  3 | NA         |    26 |
|  3 | NA         |    24 |
+----+------------+-------+

目标数据表 #

+----+------------+-------+-------+
| ID | eventdate  | Value | order |
+----+------------+-------+-------+
|  1 | NA         |    10 |    -4 |
|  1 | NA         |    11 |    -3 |
|  1 | NA         |    12 |    -2 |
|  1 | NA         |    11 |    -1 |
|  1 | 2011-03-18 |    15 |     0 |
|  1 | NA         |    17 |     1 |
|  1 | NA         |    18 |     2 |
|  1 | NA         |    15 |     3 |
|  2 | NA         |     5 |    -3 |
|  2 | NA         |     6 |    -2 |
|  2 | NA         |     7 |    -1 |
|  2 | 2011-05-28 |     9 |     0 |
|  2 | NA         |    10 |     1 |
|  2 | NA         |    11 |     2 |
|  2 | NA         |    15 |     3 |
|  2 | NA         |    16 |     4 |
|  3 | NA         |    20 |    -4 |
|  3 | NA         |    22 |    -3 |
|  3 | NA         |    23 |    -2 |
|  3 | NA         |    24 |    -1 |
|  3 | 2012-05-28 |    28 |     0 |
|  3 | NA         |    29 |     1 |
|  3 | NA         |    25 |     2 |
|  3 | NA         |    24 |     3 |
|  3 | NA         |    26 |     4 |
|  3 | NA         |    24 |     5 |
+----+------------+-------+-------+

实现方法 #

方法1：使用dplyr（tidyverse生态） #

假设你的数据框名为df，代码如下：

library(dplyr)

df <- df %>%
  group_by(ID) %>%
  mutate(order = row_number() - which(!is.na(eventdate))) %>%
  ungroup()

原理：

• group_by(ID)按ID分组处理
• row_number()生成组内每行的序号
• which(!is.na(eventdate))定位组内eventdate非NA的行的序号
• 两者相减后，非NA行的结果为0，上方行得到递减负整数，下方行得到递增正整数，完全匹配需求

方法2：使用data.table（适合大数据场景） #

如果数据量较大，用data.table效率更高，代码如下：

library(data.table)

setDT(df)
df[, order := seq_len(.N) - which(!is.na(eventdate)), by = ID]

原理：

• setDT(df)将普通数据框转为data.table格式
• by = ID按ID分组
• seq_len(.N)生成组内每行的序号（.N是组内的行数）
• 后续逻辑和dplyr方法一致，通过序号相减得到目标order值

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