R语言按指定值筛选并重组相邻dist/z关联列对的方法

• 待处理数据规模：200万行、约400列，数据内distX与zX为固定一一对应的关联列对
• 处理规则：仅保留distX取值为-10、-5、0、5、10的单元格及对应z值，不可直接删除整行（目标值对存在列位置偏移，整行删除会丢失有效数据）
• 输出结构要求：
  • 取值为-10的dist值统一归集到dist1列，对应匹配的z值放入右侧相邻的z1列
  • 取值为-5的dist值归集到dist2列，对应z值放入z2列
  • 取值0、5、10按顺序分别对应dist3/z3、dist4/z4、dist5/z5列对

dist1 <- c('-10','-10','-10','-10','-10','-10','-9','-9','-9','-9','-9','-10','-10','10','-10','-10','-10','-10','-10','-10','-10')
z1 <- c('5','5.1','5.1','5.4','5.3','5.5','5.8','5.7','5.7','5.8','5.7','5.9','5.8','6','5.9','6','5.9','5.8','5.7','6','5.9')
dist2 <- c('-5','-5','-5','-4','-4','-5','-5','-5','-4','-4','-4','-4','-4','-5','-5','-5','-5','-5','-5','-5','-5')
z2 <- c('5','5.1','5.1','5.4','5.3','5.5','5.8','5.7','5.7','5.8','5.7','5.9','5.8','6','5.9','6','5.9','5.8','5.7','6','5.9')
dist3 <- c('0','0','0','0','0','1','1','0','0','0','1','1','1','1','1','1','1','1','1','0','0')
z3 <- c('5','5.1','5.1','5.4','5.3','5.5','5.8','5.5','5.7','5.8','5.7','5.9','5.8','6','5.9','6','5.9','5.8','5.7','6','5.9')
dist4 <- c('5','5','5','5','6','5','5','6','6','6','6','6','6','6','6','5','5','5','5','5','5')
z4 <- c('6','6.1','6.1','6.4','6.3','6.6','6.8','6.7','6.7','6.8','6.7','6.5','6.8','6','6.9','6','6.9','6.8','6.7','6','6.9')
dist5 <- c('10','10','10','10','10','9','9','10','10','10','10','10','10','10','10','10','10','10','10','10','10')
z5 <- c('6','6.1','6.1','6.4','6.3','6.6','6.8','6.7','6.7','6.8','6.7','6.5','6.8','6','6.9','6','6.9','6.8','6.7','6','6.9')

test <- data.frame(dist1,z1,dist2,z2,dist3,z3,dist4,z4,dist5,z5)
tibble(test)

编写的循环代码运行后无输出、无报错，无法实现列配对筛选需求：

for (i in 1:ncol(test)){
  combn(ncol(test),2, FUN=function(i)
    apply(test[,i], 1, paste0, collapse=""))
}

基于tidyverse的长宽转换实现，内存占用和运算效率可支撑200万行规模数据处理，逻辑如下：

1. 为每行添加唯一行号作为标识，避免行数据错位
2. 宽表转长表，自动识别配对distX/zX列对
3. 过滤仅保留dist取值为-10、-5、0、5、10的有效记录
4. 按目标输出规则匹配列编号，长表转回宽表得到最终结构

完整代码：

library(tidyverse)

# 定义目标dist值与输出列序号的映射
dist_map <- c("-10" = 1, "-5" = 2, "0" = 3, "5" = 4, "10" = 5)

result <- test %>%
  mutate(row_id = row_number()) %>%
  pivot_longer(
    cols = -row_id,
    names_to = c(".value", "tmp_id"),
    names_pattern = "(dist|z)(\\d+)"
  ) %>%
  filter(dist %in% names(dist_map)) %>%
  mutate(out_col_id = dist_map[dist]) %>%
  # 同一行同一输出列出现多个匹配值时保留第一个，避免重复
  distinct(row_id, out_col_id, .keep_all = TRUE) %>%
  pivot_wider(
    id_cols = row_id,
    names_from = out_col_id,
    values_from = c(dist, z),
    names_sep = ""
  ) %>%
  select(-row_id)

运行后result即为符合要求的输出结构，非目标取值的位置自动为空，不会误删同行其他位置的有效数据。

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