R语言百万行数据集提取唯一共享航班信息的高效实现问询

问题背景 #

我有一个百万行级的数据集，结构如下：
| operating aln | operating fltnum | aln1 | flt1 | aln2| flt2| aln3| flt3|...|aln8|flt8|
每条运营航司及航班最多对应8组共享航班（aln1-8与flt1-8），部分列存在空值。目标是从所有列中提取唯一的航司与航班号组合。

现有低效实现代码 #

#separate each airline and flight number into its own dataframe
t1 <- df[,c("aln1","flt1")]
t2 <- df[,c("aln2","flt2")]
t3 <- df[,c("aln3","flt3")]
t4 <- df[,c("aln4","flt4")]
t5 <- df[,c("aln5","flt5")]
t6 <- df[,c("aln6","flt6")]
t7 <- df[,c("aln7","flt7")]
t8 <- df[,c("aln8","flt8")]

# get rid of all rows with NA in them
t1 <- na.omit(t1)
t2 <- na.omit(t2)
t3 <- na.omit(t3)
t4 <- na.omit(t4)
t5 <- na.omit(t5)
t6 <- na.omit(t6)
t7 <- na.omit(t7)
t8 <- na.omit(t8)

# rename all variables so I may combine them together in one dataframe
t1 <- rename(t1,aln = aln1, fltnum = flt1)
t2 <- rename(t2,aln = aln2, fltnum = flt2)
t3 <- rename(t3,aln = aln3, fltnum = flt3)
t4 <- rename(t4,aln = aln4, fltnum = flt4)
t5 <- rename(t5,aln = aln5, fltnum = flt5)
t6 <- rename(t6,aln = aln6, fltnum = flt6)
t7 <- rename(t7,aln = aln7, fltnum = flt7)
t8 <- rename(t8,aln = aln8, fltnum = flt8)

# combine and get a unique sort
cshares_010 <- rbind(t1,t2,t3,t4,t5,t6,t7,t8)
cshares_010<- distinct(cshares_010, aln, fltnum)

循环重构的错误尝试 #

尝试用循环重构代码（类似SAS宏思路），但运行时出现错误more elements supplied than they are to replace，示例代码如下：

names <- list("t1,","t2","t3","t4","t5","t6","t7","t8")
pos <- list(1,2,3,4,5,6,7,8)

for (j in 1:8)
{
t[[j]] <- df[, c(3+(pos[[j]]*2),(4+(pos[[j]]*2)] # so that I get a c(5,6) for t1, c(7,8) for t2,etc..
}

高效实现方案 #

方案1：tidyverse宽表转长表（简洁高效） #

利用tidyr::pivot_longer直接将多组aln/flt列转成长表，一步完成拆分、去NA、去重，代码简洁且性能远优于手动拆分：

library(tidyverse)

cshares_optimized <- df %>%
  # 筛选所有aln和flt开头的列
  select(starts_with("aln"), starts_with("flt")) %>%
  # 按列名规则转长表：将aln1-aln8合并为aln列，flt1-flt8合并为fltnum列
  pivot_longer(
    cols = everything(),
    names_to = c(".value", "group"),
    names_pattern = "(aln|flt)(\\d+)"
  ) %>%
  # 移除含NA的航司/航班行
  drop_na(aln, fltnum) %>%
  # 提取唯一组合
  distinct(aln, fltnum)

方案2：data.table（百万级数据极致性能） #

data.table在内存管理和运算速度上更适合超大规模数据集，能显著降低运行时间：

library(data.table)

setDT(df)
# 生成每组aln/flt的列名对
col_pairs <- lapply(1:8, function(i) paste0(c("aln", "flt"), i))
# 批量合并所有分组，过滤NA，去重
cshares_optimized <- rbindlist(lapply(col_pairs, function(pair) df[, ..pair]), use.names = FALSE)[
  !is.na(V1) & !is.na(V2), 
  .(aln = V1, fltnum = V2)
][, unique(.SD)]

循环代码错误排查与修正 #

你的循环代码存在3个核心问题：

1. 语法错误：索引向量末尾缺少右括号，导致代码解析失败
2. 索引计算错误：目标取aln1（第3列）、flt1（第4列），但当前计算得到的是第5、6列，完全偏离预期
3. 未初始化列表：t未提前创建为列表，直接使用t[[j]]会触发赋值错误

修正后的循环代码：

library(tidyverse)

# 提前初始化空列表
t_list <- vector("list", 8)

for (j in 1:8) {
  # 用列名匹配更可靠，避免索引计算出错
  current_cols <- c(paste0("aln", j), paste0("flt", j))
  # 提取子集并去NA
  t_list[[j]] <- df[, current_cols] %>% na.omit()
  # 重命名列
  t_list[[j]] <- rename(t_list[[j]], aln = !!current_cols[1], fltnum = !!current_cols[2])
}

# 合并所有子集并去重
cshares_010 <- bind_rows(t_list) %>% distinct(aln, fltnum)

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