如何在R中统计指定年份个体在历年的留存数量？

核心思路 #

先提取目标学年（20-21）的所有唯一学生ID，再分别统计这些ID在各前序学年的出现数量，最终整理成需求格式的结果表。

方法一：分步筛选统计 #

步骤清晰，适合新手理解：

1. 加载依赖包（需提前安装）

library(tidyverse)

2. 构造示例数据集（替换为你的实际数据集即可）

df <- tibble(
  id = c(1,1,1,1,2,2,2,3,3,4,4,4,4),
  grade = c(9,10,11,12,9,10,11,11,12,9,10,11,12),
  `school year` = c("17-18","18-19","19-20","20-21","17-18","18-19","20-21","20-21","21-22","17-18","18-19","19-20","20-21")
)

3. 提取20-21学年的唯一ID

ids_2021 <- df %>% filter(`school year` == "20-21") %>% pull(id) %>% unique()

4. 统计各前序学年的留存数量并生成结果表

result <- tibble(
  "# of IDs in 20-21" = length(ids_2021),
  "#of IDs from 20-21 that were present in 19-20" = df %>% filter(`school year` == "19-20", id %in% ids_2021) %>% pull(id) %>% unique() %>% length(),
  "#of IDs from 20-21 that were present in 18-19" = df %>% filter(`school year` == "18-19", id %in% ids_2021) %>% pull(id) %>% unique() %>% length(),
  "#of IDs from 20-21 that were present in 17-18" = df %>% filter(`school year` == "17-18", id %in% ids_2021) %>% pull(id) %>% unique() %>% length()
)

# 查看结果
print(result)

运行后示例结果：

方法二：宽表转换后统计 #

更高效，适合大数据集：

1. 将长格式数据转为宽格式（每个ID一行，各学年标记是否存在）

df_wide <- df %>% 
  select(id, `school year`) %>% 
  distinct() %>% 
  pivot_wider(
    names_from = `school year`, 
    values_from = `school year`, 
    values_fn = ~1,  # 存在标记为1
    values_fill = 0  # 不存在标记为0
  )

2. 筛选20-21学年的ID，统计各前序学年的留存数

result_wide <- df_wide %>% 
  filter(`20-21` == 1) %>% 
  summarise(
    "# of IDs in 20-21" = n(),
    "#of IDs from 20-21 that were present in 19-20" = sum(`19-20`),
    "#of IDs from 20-21 that were present in 18-19" = sum(`18-19`),
    "#of IDs from 20-21 that were present in 17-18" = sum(`17-18`)
  )

# 查看结果
print(result_wide)

输出结果和方法一完全一致。

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