如何基于INT1:INT4与INTX1:INTX4列生成ING1:ING4新列？

我有包含INT1:INT4和INTX1、INTX2、INTX173、INTX4共8列的R数据集，每行最多有4个非NA值。需要按行从这8列中提取非NA值，依次赋值给新创建的ING1:ING4列：第一个非NA值存入ING1，第二个存入ING2，第三个存入ING3，第四个存入ING4。例如第一行预期结果如下：

数据集如下：

DATA <- structure(list(ID = c("101", "101", "101", "101", "101", "101","101", "101", "101", "101"), IDA = c("1", "1", "2", "3", "4","5", "5", "1859", "1860", "1861"), DATE = structure(c(1300928400,1277946000, 1277946000, 1278550800, 1278550800, 1453770000, 1329958800,1506474000, 1485133200, 1485133200), tzone = "UTC", class = c("POSIXct","POSIXt")), NR = c("CH-0001", "CH-0001","CH-0002", "CH-0003", "CH-0004", "CH-0005","CH-0005", "CH-1859", "CH-1860", "CH-1861"), PAT = c("101-1", "101-1", "101-2", "101-3", "101-4", "101-5","101-5", "101-1859", "101-1860", "101-1861"), INT1 = c(245005,280040, 280040, 280040, 280040, 240040, 240040, NA, NA, NA), INT2 = c(NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,NA_real_, NA_real_, NA_real_, NA_real_, NA_real_), INT3 = c(NA_real_,NA_real_, 280010, NA_real_, NA_real_, NA_real_, NA_real_,NA_real_, 245035, NA_real_), INT4 = c(NA_real_, NA_real_,NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,NA_real_, NA_real_), INTX1 = c(NA_real_, 275040, NA_real_,NA_real_, NA_real_, NA_real_, 240080, NA_real_, NA_real_,NA_real_), INTX2 = c(276790, NA_real_, 7612645, NA_real_,NA_real_, NA_real_, 5078219, NA_real_, NA_real_, NA_real_), INTX173 = c(NA_real_, NA_real_, NA_real_, 3456878,NA_real_, NA_real_, 3289778, NA_real_, NA_real_, NA_real_), INTX4 = c(NA_real_, NA_real_, 11198767, NA_real_,NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 7025676), KAT = c(0, 0, 0, 0, 0, 0, 0, 1, 1, 1)), row.names = c(NA,-10L), class = c("tbl_df", "tbl", "data.frame"))

方法1：使用tidyverse工具链 #

结合dplyr和tidyr实现，步骤清晰易读：

library(tidyverse)

DATA_processed <- DATA %>%
  # 添加临时行号用于分组
  mutate(row_id = row_number()) %>%
  # 将目标列转为长格式
  pivot_longer(cols = c(INT1:INT4, INTX1, INTX2, INTX173, INTX4),
               names_to = "col_name", values_to = "value") %>%
  # 过滤NA值
  filter(!is.na(value)) %>%
  # 按行给非NA值编序号
  group_by(row_id) %>%
  mutate(ing_col = paste0("ING", row_number())) %>%
  ungroup() %>%
  # 转回宽格式生成ING列
  pivot_wider(id_cols = row_id, names_from = ing_col, values_from = value) %>%
  # 合并回原数据集
  right_join(DATA %>% mutate(row_id = row_number()), by = "row_id") %>%
  # 调整列顺序并移除临时行号
  select(-row_id, everything(), ING1:ING4)

# 查看核心结果
select(DATA_processed, ID, ING1, ING2, ING3, ING4)

方法2：使用base R逐行处理 #

通过apply函数快速完成，无需加载额外包：

# 指定需要处理的目标列
target_cols <- c("INT1", "INT2", "INT3", "INT4", "INTX1", "INTX2", "INTX173", "INTX4")

# 逐行提取非NA值并补全至4个元素
ing_vals <- t(apply(DATA[target_cols], 1, function(x) {
  non_na_vals <- na.omit(x)
  c(non_na_vals, rep(NA, 4 - length(non_na_vals)))
}))

# 赋值给新列
DATA$ING1 <- ing_vals[, 1]
DATA$ING2 <- ing_vals[, 2]
DATA$ING3 <- ing_vals[, 3]
DATA$ING4 <- ing_vals[, 4]

# 查看结果
head(DATA[, c("ID", "ING1", "ING2", "ING3", "ING4")])

结果验证 #

处理后第一行的ING1=245005、ING2=276790，ING3和ING4为NA，完全符合预期；其他行的非NA值也会按顺序填充到对应ING列中。

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