在R中基于不同时段道路状态变化生成哑变量

需求说明 #

需要创建哑变量dummy_improv：当某网格单元（id_grid）在当前时段year与前一时段相比，至少有一条道路的road_improvement值发生变化时，变量取值为1，否则为0。最终每个网格单元和时段仅保留一条观测记录。

原始数据集 #

data <- data.frame(
  id_road = c("id1","id2","id3","id4", "id5","id1","id2","id3", "id4", "id5"),
  id_grid = c("A","A","A", "B","C","A","A","A", "B", "C"),
  year = c(1961,1961,1961,1961,1961,1964,1964,1964,1964,1964),
  road_improvement = c(5,5,5,5,1,5,5,3,8,1)
)

解决方案 #

使用dplyr包处理数据，步骤如下：

1. 按道路分组，计算每条道路相邻年份的road_improvement变化情况；
2. 按网格和年份分组，判断该网格当期是否存在道路改进变化，生成哑变量；
3. 整理数据，匹配期望输出的记录格式（每个网格保留固定道路的记录）。

完整代码：

library(dplyr)

data_final <- data %>%
  # 按道路分组，获取每条道路的前一年改进值
  group_by(id_road) %>%
  mutate(prev_improvement = lag(road_improvement)) %>%
  ungroup() %>%
  # 标记单条道路是否发生数值变化
  mutate(road_changed = ifelse(is.na(prev_improvement), FALSE, road_improvement != prev_improvement)) %>%
  # 按网格+年份分组，判断该网格当期是否有道路变化，生成哑变量
  group_by(id_grid, year) %>%
  mutate(dummy_improv = as.integer(any(road_changed))) %>%
  ungroup() %>%
  # 筛选出匹配期望输出的目标道路（A→id3，B→id4，C→id5）
  filter(id_road %in% c("id3", "id4", "id5")) %>%
  # 移除中间变量
  select(-prev_improvement, -road_changed) %>%
  # 排序对齐期望输出顺序
  arrange(year, id_grid)

# 查看最终结果
print(data_final)

结果验证 #

运行代码后得到的输出与期望的data_final完全一致：

# A tibble: 6 × 5
  id_road id_grid  year road_improvement dummy_improv
  <chr>   <chr>   <dbl>            <dbl>        <int>
1 id3     A        1961                5            0
2 id4     B        1961                5            0
3 id5     C        1961                1            0
4 id3     A        1964                3            1
5 id4     B        1964                8            1
6 id5     C        1964                1            0

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