使用dplyr的summarize()时如何保留日期列的正确值

问题 #

我用summarize()按new_dyadep_id分组汇总数据框cid_term，生成每个分组一行的row_1dyadep。多数变量取最后一个非NA值（这些变量要么全NA要么值一致），但日期列ependdate格式出错——原数据里的"1991-10-10"汇总后变成了7952。

另外，尝试用last()+na.omit()的组合会丢失ependdate全为NA的分组，我需要保留所有分组。

现有代码如下：

row_1dyadep <- cid_term %>%
  group_by(new_dyadep_id) %>%
  summarize(
    new_dyadep_id = last(new_dyadep_id),
    dyad_id = ifelse(all(is.na(dyad_id)), NA, last(dyad_id[!is.na(dyad_id)], default = NA)),
    location = ifelse(all(is.na(location)), NA, last(location[!is.na(location)], default = NA)),
    first_year_active = ifelse(all(is.na(first_year_active)), NA, last(first_year_active[!is.na(first_year_active)], default = NA)),
    last_year_active = ifelse(all(is.na(last_year_active)), NA, last(last_year_active[!is.na(last_year_active)], default = NA)),
    ependdate = ifelse(all(is.na(ependdate)), NA, last(ependdate[!is.na(ependdate)], default = NA)),
    incompatibility = ifelse(all(is.na(incompatibility)), NA, last(incompatibility[!is.na(incompatibility)], default = NA)),
    conflict_id = ifelse(all(is.na(conflict_id)), NA, last(conflict_id[!is.na(conflict_id)], default = NA)),
    side_a = ifelse(all(is.na(side_a)), NA, last(side_a[!is.na(side_a)], default = NA)),
    side_b = ifelse(all(is.na(side_b)), NA, last(side_b[!is.na(side_b)], default = NA)),
    conflict = ifelse(all(is.na(conflict)), NA, last(conflict[!is.na(conflict)], default = NA)),
    dyadepisode = ifelse(all(is.na(dyadepisode)), NA, last(dyadepisode[!is.na(dyadepisode)], default = NA)),
    outcome = ifelse(all(is.na(outcome)), NA, last(outcome[!is.na(outcome)], default = NA)),
    territory_name = ifelse(all(is.na(territory_name)), NA, last(territory_name[!is.na(territory_name)], default = NA)),
    region = ifelse(all(is.na(region)), NA, last(region[!is.na(region)], default = NA)),
    last_dyadcount = ifelse(all(is.na(dyadcount)), NA, last(dyadcount[!is.na(dyadcount)], default = NA)),
    max_dyadcount = max(dyadcount, na.rm = TRUE),
    last_intensity_level = ifelse(all(is.na(intensity_level)), NA, last(intensity_level[!is.na(intensity_level) & active_year == 1], default = NA)),
    max_intensity_level = max(intensity_level, na.rm = TRUE),
    last_type_of_conflict = ifelse(all(is.na(type_of_conflict)), NA, last(type_of_conflict[!is.na(type_of_conflict)], default = NA)),
    max_type_of_conflict = max(type_of_conflict, na.rm = TRUE),
    avg_issue_count_per_year = mean(issue_count_per_year, na.rm = TRUE),
    obs_years_per_episode = n(), # nrows per episode
    across(all_of(issue_columns), ~ sum(.x, na.rm = TRUE), .names = "freq_{col}"),
    across(starts_with("ethnicity"), ~ ifelse(all(is.na(.x)), NA, last(.x[!is.na(.x)], default = NA)), .names = "{col}"),
    across(starts_with("geography"), ~ ifelse(all(is.na(.x)), NA, last(.x[!is.na(.x)], default = NA)), .names = "{col}"),
    across(starts_with("ideology"), ~ ifelse(all(is.na(.x)), NA, last(.x[!is.na(.x)], default = NA)), .names = "{col}"),
    across(starts_with("religion"), ~ ifelse(all(is.na(.x)), NA, last(.x[!is.na(.x)], default = NA)), .names = "{col}")
  )

解决方案 #

问题根源

日期变成数字7952是因为基础R的ifelse()会强制统一返回值类型：当你用ifelse()返回日期和普通NA时，日期会被自动转换为数值（R中日期本质是距离1970-01-01的天数，1991-10-10正好是第7952天）。

之前用last(na.omit(ependdate))丢分组是因为当分组全为NA时，na.omit()会返回空向量，无default参数的last()会报错，导致该分组被丢弃。

修复步骤

把ependdate对应的行替换成以下两种写法之一：

写法1：用dplyr::if_else()保留日期类型

dplyr::if_else()会严格检查类型一致性，需要给NA指定日期类型的NA_Date_：

ependdate = if_else(all(is.na(ependdate)), NA_Date_, last(ependdate[!is.na(ependdate)], default = NA_Date_)),

写法2：更简洁的last()+default写法

直接用last()的na_rm和default参数，无需额外判断全NA情况：

ependdate = last(ependdate, na_rm = TRUE, default = NA_Date_),

• na_rm = TRUE会自动跳过NA，取最后一个非NA值
• default = NA_Date_确保当全NA时返回日期类型的NA，不会丢分组

额外优化建议

你的代码里大量重复ifelse(all(is.na(x)), NA, last(x[!is.na(x)], default = NA))，可以用across()统一处理这些变量，简化代码：

row_1dyadep <- cid_term %>%
  group_by(new_dyadep_id) %>%
  summarize(
    # 统一处理需要取最后非NA值的变量
    across(c(new_dyadep_id, dyad_id, location, first_year_active, last_year_active, 
             incompatibility, conflict_id, side_a, side_b, conflict, dyadepisode,
             outcome, territory_name, region, last_dyadcount),
           ~ last(.x, na_rm = TRUE, default = NA)),
    # 单独处理日期列，指定日期类型的NA
    ependdate = last(ependdate, na_rm = TRUE, default = NA_Date_),
    # 其余原有逻辑保留
    max_dyadcount = max(dyadcount, na.rm = TRUE),
    last_intensity_level = ifelse(all(is.na(intensity_level)), NA, last(intensity_level[!is.na(intensity_level) & active_year == 1], default = NA)),
    max_intensity_level = max(intensity_level, na.rm = TRUE),
    last_type_of_conflict = last(type_of_conflict, na_rm = TRUE, default = NA),
    max_type_of_conflict = max(type_of_conflict, na.rm = TRUE),
    avg_issue_count_per_year = mean(issue_count_per_year, na.rm = TRUE),
    obs_years_per_episode = n(),
    across(all_of(issue_columns), ~ sum(.x, na.rm = TRUE), .names = "freq_{col}"),
    across(c(starts_with("ethnicity"), starts_with("geography"), 
             starts_with("ideology"), starts_with("religion")),
           ~ last(.x, na_rm = TRUE, default = NA))
  )

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