在R中通过筛选三列数值生成两列新数据的方法

我有如下R DataFrame：

df<-structure(list(estado = c("Aguascalientes", "Aguascalientes", 
"Aguascalientes", "Aguascalientes", "Aguascalientes", "Aguascalientes", 
"Aguascalientes", "Aguascalientes"), municipio = c("Aguascalientes", 
"Aguascalientes", "Aguascalientes", "Aguascalientes", "Aguascalientes", 
"Aguascalientes", "Aguascalientes", "Aguascalientes"), `Presidente Municipal` = c("C. ZEFERINO MUÑOZ", 
"C. AURELIO PADILLA", "C. FELIPE RUIZ", "C. GABRIEL CARMONA", 
"C. EVARISTO FEMAT", "C. FRANCISCO ARMENGOL", "LIC. MARIA TERESA JIMENEZ ESQUIVEL", 
"C. LEONARDO MONTAÑEZ  CASTRO"), Sexo = c("H", "H", "H", "H", 
"H", "H", "M", "H"), Partido = c(NA, NA, NA, NA, NA, NA, "PAN", 
"COAL. POR AGUASCALIE"), a = c(NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_), b = c(1903, 
1927, 1900, 1925, 1906, 1907, 15, 15), c = c(NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_), 
    d = c(NA, NA, NA, NA, 1913, 1908, 2019, 2021), e = c(NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_), 
    f = c(NA, NA, NA, NA, NA, NA, 14, 14), g = c(NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_), 
    h = c(NA, NA, NA, NA, NA, NA, 2021, 2024), id_estado = c(1, 
1, 1, 1, 1, 1, 1, 1), id_municipio = c(1, 1, 1, 1, 1, 1, 
1, 1), cve_inegi = c(1001, 1001, 1001, 1001, 1001, 1001, 
1001, 1001)), row.names = c(NA, -8L), class = "data.frame")

需要新增两列X和Y，从b、d、h三列中提取≥1900的数值（有效数值均≥1900或为NA）：

• 每行的第一个有效数值放入X列
• 第二个有效数值放入Y列
• 保留原数据所有列，最终得到目标DataFrame df2。

方法1：基础R原生实现 #

无需额外安装包，通过apply逐行处理目标列：

# 逐行过滤b、d、h列中≥1900的有效数值，每行保留前两个（不足补NA）
valid_rows <- t(apply(df[, c("b", "d", "h")], 1, function(row) {
  filtered <- row[!is.na(row) & row >= 1900]
  c(filtered[1], if (length(filtered) >= 2) filtered[2] else NA)
}))

# 将处理结果转为数据框并命名列
new_columns <- data.frame(X = valid_rows[, 1], Y = valid_rows[, 2])

# 合并原数据与新列，得到最终结果
df2 <- cbind(df, new_columns)

方法2：tidyverse 工具链实现 #

如果习惯使用dplyr和tidyr，可以用管道语法更直观地完成：

library(dplyr)
library(tidyr)
library(purrr)

df2 <- df %>%
  rowwise() %>%
  # 收集当前行b、d、h中的有效数值，存储为列表列
  mutate(valid_vals = list(c_across(c(b, d, h))[!is.na(c_across(c(b, d, h))) & c_across(c(b, d, h)) >= 1900])) %>%
  ungroup() %>%
  # 从列表中提取第一个和第二个值作为X、Y
  mutate(X = map_dbl(valid_vals, ~ ifelse(length(.x) >= 1, .x[1], NA)),
         Y = map_dbl(valid_vals, ~ ifelse(length(.x) >= 2, .x[2], NA))) %>%
  # 删除临时辅助列
  select(-valid_vals)

结果验证 #

运行上述任意一种方法后，得到的df2与目标结构完全一致，可通过print(df2)查看最终结果。

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