如何用R基础函数或dplyr生成长格式纵向数据的转移频率表

要从长格式纵向数据（每个个体包含起始和结束状态）生成完整的状态转移频率表（涵盖所有可能的状态转移组合，包括频率为0的情况），可以用以下两种方法实现：

现有数据集 #

首先定义示例数据：

id <- c(1,1,2,2,3,3,4,4)
state <- c("C","A", "A", "A", "B", "A", "C", "A")
period <- rep(c("Start", "End"), 4)
df <- data.frame(id, state, period)

对应的数据集结构：

id state period
1  1      C  Start
2  1      A    End
3  2      A  Start
4  2      A    End
5  3      B  Start
6  3      A    End
7  4      C  Start
8  4      A    End

预期输出 #

需要生成包含所有状态转移组合的频率表，即使某些转移没有发生（频率为0）：

transition freq
1     A to A    1
2     A to B    0
3     A to C    0
4     B to B    0
5     B to A    1
6     B to C    0
7     C to C    0
8     C to A    2
9     C to B    0

方法一：R基础函数实现 #

1. 转换为宽格式：将每个个体的起始、结束状态放到同一行

wide_df <- reshape(df, idvar = "id", timevar = "period", direction = "wide")
colnames(wide_df) <- c("id", "start_state", "end_state")

转换后的数据：

id start_state end_state
1  1           C         A
3  2           A         A
5  3           B         A
7  4           C         A

2. 生成所有可能的转移组合：基于所有唯一状态生成笛卡尔积，构建转移标签

states <- unique(df$state)
all_transitions <- expand.grid(from = states, to = states, stringsAsFactors = FALSE)
all_transitions$transition <- paste(all_transitions$from, "to", all_transitions$to)

3. 统计频率并合并填充0：先统计实际发生的转移频率，再与全组合合并，把缺失的频率补为0

actual_counts <- table(paste(wide_df$start_state, "to", wide_df$end_state))
actual_df <- as.data.frame(actual_counts, stringsAsFactors = FALSE)
colnames(actual_df) <- c("transition", "freq")

result_df <- merge(all_transitions[, "transition", drop=FALSE], actual_df, by = "transition", all.x = TRUE)
result_df$freq[is.na(result_df$freq)] <- 0
result_df <- result_df[, c("transition", "freq")]

方法二：dplyr + tidyr实现 #

用tidyverse工具链可以更简洁地完成整个流程：

library(dplyr)
library(tidyr)

# 统计实际发生的转移频率
actual_counts <- df %>%
  pivot_wider(id_cols = id, names_from = period, values_from = state) %>%
  mutate(transition = paste(Start, "to", End)) %>%
  count(transition, name = "freq")

# 生成所有可能的转移组合
states <- unique(df$state)
all_transitions <- expand.grid(from = states, to = states, stringsAsFactors = FALSE) %>%
  mutate(transition = paste(from, "to", to))

# 合并并填充0值
result_df <- all_transitions %>%
  left_join(actual_counts, by = "transition") %>%
  mutate(freq = replace_na(freq, 0)) %>%
  select(transition, freq)

运行上述代码后，result_df就是符合预期的状态转移频率表。

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