如何在R中按条件将多列拆分为多行航班航段数据

原始数据 #

AT_ID <- c(1,2,3)
DEPARTURE_AIRPORT <- c("ZRH","ZRH","ZRH")
STOPOVER_1 <- c(NA, "BEL", "DUB")
STOPOVER_2 <- c(NA, "RUO", NA)
ARRIVAL_AIRPORT <- c("IAD", "LAX","BUD")
intinerary_id <- c(NA,NA,NA)

test_df <- data.frame(AT_ID, DEPARTURE_AIRPORT, STOPOVER_1, STOPOVER_2, ARRIVAL_AIRPORT, intinerary_id)
print(test_df)

目标结构 #

AT_ID <- c(1,2,3,4,5,6)
DEPARTURE_AIRPORT <- c("ZRH","ZRH","BEL","RUO","ZRH","DUB")
ARRIVAL_AIRPORT <- c("IAD", "BEL","RUO", "LAX","DUB","BUD")
intinerary_id <- c(1,2,2,2,3,3)

test_df_target <- data.frame(AT_ID, DEPARTURE_AIRPORT, ARRIVAL_AIRPORT, intinerary_id)
print(test_df_target)

方法1：使用tidyverse工具链 #

适合熟悉tidyverse语法的用户，代码简洁易读：

library(tidyverse)

result <- test_df %>%
  # 为每个原始行程分配唯一的itinerary_id（复用原始AT_ID）
  mutate(intinerary_id = AT_ID) %>%
  # 按行整理机场序列，过滤NA值
  rowwise() %>%
  mutate(airports = list(na.omit(c(DEPARTURE_AIRPORT, STOPOVER_1, STOPOVER_2, ARRIVAL_AIRPORT)))) %>%
  ungroup() %>%
  # 生成每个航段的起止机场对
  mutate(
    DEPARTURE_AIRPORT = map(airports, ~ head(.x, -1)),
    ARRIVAL_AIRPORT = map(airports, ~ tail(.x, -1))
  ) %>%
  # 将航段列表展开为多行
  unnest(c(DEPARTURE_AIRPORT, ARRIVAL_AIRPORT)) %>%
  # 生成新的自增AT_ID，调整列顺序
  select(intinerary_id, DEPARTURE_AIRPORT, ARRIVAL_AIRPORT) %>%
  mutate(AT_ID = row_number()) %>%
  select(AT_ID, DEPARTURE_AIRPORT, ARRIVAL_AIRPORT, intinerary_id)

print(result)

方法2：Base R实现 #

无需额外安装包，适合偏好原生R语法的用户：

# 为每个行程分配itinerary_id
test_df$intinerary_id <- test_df$AT_ID

# 提取每行的机场序列并过滤NA
airport_list <- apply(test_df[, c("DEPARTURE_AIRPORT", "STOPOVER_1", "STOPOVER_2", "ARRIVAL_AIRPORT")], 
                      1, function(x) na.omit(x))

# 生成每个行程的航段数据
segments <- lapply(seq_along(airport_list), function(i) {
  ap <- airport_list[[i]]
  # 只有单个机场的行程（无航段）跳过
  if (length(ap) < 2) return(NULL)
  data.frame(
    intinerary_id = test_df$intinerary_id[i],
    DEPARTURE_AIRPORT = head(ap, -1),
    ARRIVAL_AIRPORT = tail(ap, -1)
  )
})

# 合并航段数据，生成新AT_ID并调整列顺序
result_base <- do.call(rbind, segments)
result_base$AT_ID <- seq(nrow(result_base))
result_base <- result_base[, c("AT_ID", "DEPARTURE_AIRPORT", "ARRIVAL_AIRPORT", "intinerary_id")]

print(result_base)

两种方法均可生成与目标结构完全一致的结果，处理过程自动适配不同数量的经停航段和NA值。

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