如何用tidyverse在R中统计各ID在过往观测中的累计出现次数？

原数据定义 #

d <- structure(list(date = structure(c(19649, 19653, 19654, 19655, 
19654, 19655, 19653, 19654, 19655, 19643, 19654, 19649, 19650, 
19654, 19649, 19653, 19636, 19640, 19646, 19647, 19653, 19649, 
19650, 19653, 19654, 19655), class = "Date"), id = c(1L, 1L, 
2L, 2L, 3L, 3L, 4L, 4L, 4L, 5L, 6L, 7L, 7L, 8L, 9L, 9L, 10L, 
10L, 11L, 11L, 11L, 12L, 12L, 12L, 13L, 14L)), row.names = c(NA, 
-26L), class = "data.frame")

原数据预览：

date id
1  2023-10-19  1
2  2023-10-23  1
3  2023-10-24  2
4  2023-10-25  2
5  2023-10-24  3
6  2023-10-25  3
7  2023-10-23  4
8  2023-10-24  4
9  2023-10-25  4
10 2023-10-13  5
11 2023-10-24  6
12 2023-10-19  7
13 2023-10-20  7
14 2023-10-24  8
15 2023-10-19  9
16 2023-10-23  9
17 2023-10-06 10
18 2023-10-10 10
19 2023-10-16 11
20 2023-10-17 11
21 2023-10-23 11
22 2023-10-19 12
23 2023-10-20 12
24 2023-10-23 12
25 2023-10-24 13
26 2023-10-25 14

解决方案 #

完全可以用dplyr（tidyverse核心组件）实现需求，核心逻辑如下：

• 按id分组，确保每个ID的计数独立计算
• 分组内按date排序，保证计数严格按照日期顺序生成
• 生成累计计数，row_number()或cumsum(1)均可实现

代码实现 #

如果不需要保留原数据的行顺序，直接执行以下代码：

library(dplyr)

result <- d %>%
  group_by(id) %>%
  arrange(date, .by_group = TRUE) %>%
  mutate(count = row_number()) %>%
  ungroup()

print(result)

如果需要完全匹配示例的原行顺序，可以先标记行号，处理后再还原：

result_with_original_order <- d %>%
  mutate(row_id = row_number()) %>%
  group_by(id) %>%
  arrange(date, .by_group = TRUE) %>%
  mutate(count = row_number()) %>%
  ungroup() %>%
  arrange(row_id) %>%
  select(-row_id)

print(result_with_original_order)

输出结果 #

执行后会得到与示例完全一致的结果：

date id count
1  2023-10-19  1     1
2  2023-10-23  1     2
3  2023-10-24  2     1
4  2023-10-25  2     2
5  2023-10-24  3     1
6  2023-10-25  3     2
7  2023-10-23  4     1
8  2023-10-24  4     2
9  2023-10-25  4     3
10 2023-10-13  5     1
11 2023-10-24  6     1
12 2023-10-19  7     1
13 2023-10-20  7     2
14 2023-10-24  8     1
15 2023-10-19  9     1
16 2023-10-23  9     2
17 2023-10-06 10     1
18 2023-10-10 10     2
19 2023-10-16 11     1
20 2023-10-17 11     2
21 2023-10-23 11     3
22 2023-10-19 12     1
23 2023-10-20 12     2
24 2023-10-23 12     3
25 2023-10-24 13     1
26 2023-10-25 14     1

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