在R中基于多列条件向前填充数据框的issue_*列

数据集 #

有一个名为issue_termi_episode的数据框，结构如下：

issue_termi_episode <- structure(
  list(
    new_conflictep_id = c(20504, 20505, 20506, 20507, 20508, 20902, 20903, 20904, 22003, 22101, 22102, 22103, 22104, 22105, 22202),
    conflict_id = c(205, 205, 205, 205, 205, 209, 209, 209, 220, 221, 221, 221, 221, 221, 222),
    location = c("Iran", "Iran", "Iran", "Iran", "Iran", "Philippines", "Philippines", "Philippines", "Paraguay", "Myanmar (Burma)", "Myanmar (Burma)", "Myanmar (Burma)", "Myanmar (Burma)", "Myanmar (Burma)", "Myanmar (Burma)"),
    incompatibility = c(1, 1, 1, 1, 1, 2, 2, 2, 2, 1, 1, 1, 1, 1, 2),
    conflict = c("Iran: Kurdistan", "Iran: Kurdistan", "Iran: Kurdistan", "Iran: Kurdistan", "Iran: Kurdistan", "Philippines", "Philippines", "Philippines", "Paraguay", "Myanmar (Burma): Karen", "Myanmar (Burma): Karen", "Myanmar (Burma): Karen", "Myanmar (Burma): Karen", "Myanmar (Burma): Karen", "Myanmar (Burma)"),
    conflictepisode = c(4, 5, 6, 7, 8, 2, 3, 4, 3, 1, 2, 3, 4, 5, 2),
    outcome = c(5, 5, 5, 5, 5, 2, 2, NA, 4, 5, 5, 5, 2, 5, 5),
    version = c(3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3),
    intensity_level = c(1, 1, 1, 1, 1, 2, 1, 1, 1, 2, 1, 1, 1, 1, 1),
    region = c("2", "2", "2", "2", "2", "3", "3", "3", "5", "3", "3", "3", "3", "3", "3"),
    first_year_active = c(1990, 1993, 1996, 2016, 2018, 1989, 1997, 1999, 1989, 1989, 1994, 1997, 2000, 2013, 1990),
    last_year_active = c(1990, 1993, 1996, 2016, 2018, 1995, 1997, 2020, 1989, 1992, 1995, 1998, 2011, 2013, 1992),
    issue_territory = c(1, 1, 1, 1, 0, 0, 0, 0, 0, 1, 1, 1, 1, 1, 1),
    issue_statestruc = c(1, 1, 1, 1, 0, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1),
    issue_gov = c(1, 0, 1, 1, 0, 1, 1, 1, 1, 1, 0, 1, 1, 0, 1),
    issue_polrights = c(1, 1, 0, 1, 0, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1),
    issue_distrib = c(1, 0, 0, 1, 0, 1, 1, 1, 0, 1, 1, 1, 1, 0, 1)
  ),
  row.names = c(NA, -15L),
  class = c("tbl_df", "tbl", "data.frame")
)

该数据框最后5个issue_*变量为二进制（0或1），new_conflictep_id为每行唯一值，conflict_id为分组变量。

目标 #

按conflict_id分组，满足以下两个条件时，将前一行的issue_*列数据填充到当前行：

• 当前行的first_year_active与前一行的last_year_active间隔≤5年；
• 当前行的所有issue_*变量均为0。

尝试的方法 #

使用dplyr多次尝试均未成功：

library(dplyr)
issue_termi_episode <- issue_termi_episode %>%
arrange(conflict_id, first_year_active) %>%
group_by(conflict_id) %>%
mutate(across(starts_with("issue_"), ~ ifelse(first_year_active - lag(last_year_active, default = first_year_active[1] + 10) <= 5 & all(. == 0), lag(.), .)))

此代码随机生成NA值，未达到预期效果。后续尝试通过标记行处理：

issue_termi_episode <- issue_termi_episode %>%
  arrange(conflict_id, first_year_active) %>%
  group_by(conflict_id) %>%
  mutate(row_fill = ifelse(first_year_active - lag(last_year_active, default = first_year_active[1] + 10) <= 5 &all(c_across(starts_with("issue_")) == 0), 
                                 333, 0)) %>%
  ungroup() %>%
  mutate(across(starts_with("issue_"), ~ ifelse(row_fill == 333, lag(.), .)))

但生成的row_fill全为0，条件未被正确识别。

问题分析 #

1. 第一个尝试中，all(. == 0)是对单个issue_*列判断是否全为0，而非当前行所有issue_*列都为0，条件逻辑错误。
2. 第二个尝试中，ungroup()后使用lag()会跨组取前一行值，不符合分组处理要求；同时lag(last_year_active)的默认值设置可能导致首行条件误判。

正确代码 #

library(dplyr)

issue_termi_episode_filled <- issue_termi_episode %>%
  # 按分组和年份排序，保证组内顺序正确
  arrange(conflict_id, first_year_active) %>%
  group_by(conflict_id) %>%
  # 计算填充条件：首行无前置行直接排除，非首行同时满足年份间隔和全0要求
  mutate(
    fill_condition = case_when(
      row_number() == 1 ~ FALSE,
      first_year_active - lag(last_year_active) <= 5 & 
        all(c_across(starts_with("issue_")) == 0) ~ TRUE,
      TRUE ~ FALSE
    )
  ) %>%
  # 对每个issue列，满足条件时用组内前一行的值填充
  mutate(across(starts_with("issue_"), ~ ifelse(fill_condition, lag(.), .))) %>%
  # 可选：删除辅助判断列
  select(-fill_condition) %>%
  ungroup()

代码说明 #

• arrange(conflict_id, first_year_active)确保每个conflict_id组内按年份顺序排列，保证lag()取到同组的前一行数据。
• fill_condition单独计算填充逻辑，首行直接排除；非首行同时验证年份间隔和当前行所有issue_*是否为0，避免逻辑混乱。
• 分组内执行lag()操作，仅取同组前一行的对应issue_*值，不会跨组错误取值。

验证结果：以conflict_id=205的第5行（new_conflictep_id=20508）为例，其所有issue_*均为0，且first_year_active(2018)与前一行last_year_active(2016)间隔为2年≤5，会被前一行的issue_*值正确填充。

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