正则表达式——将一列拆分为多列，在 R 中没有明确的分隔符

我的数据集中有一列名为“Market.Pair”，其中包含有关某些航类的出发点和目的地点的信息。例如:

input <- data.frame(Market.Pair = c("US to/from CA", "HOU to/from DFW/DAL", "EWR/JFK to/from LAX/SFO", "US-NYC to/from FR-PAR", "US to/from Asia"))
input

所有两个字母的单词都代表国家(例如美国、加利福尼亚州)。
所有三个字母的词(或多个以“/”分隔的三个字母的词)代表机场(例如 HOU、DFW/DAL)。
XX-XXX 形式的所有单词都代表城市(例如 US-NYC)。
其他词代表区域，例如亚洲或欧洲。

我想将此列拆分为多列:

output<- data.frame(Air.1 = c("HOU", "EWR/JFK", "", "", ""), Air.2 = c("DFW/DAL", "LAX/SFO", "", "", ""), City.1 = c("","","US-NYC", "", ""), City.2 = c("","","FR-PAR", "", ""), Country.1 = c("","","","US", "US"), Coutry.2 = c("","","","CA", ""), Region.1 = c("","","", "", "Asia"), Region.2 = c("","","", "", ""))
output

我是正则表达式的新手，所以任何帮助将不胜感激!

最佳答案

input <- data.frame(Market.Pair = c("US to/from CA", "HOU to/from DFW/DAL",
                                    "EWR/JFK to/from LAX/SFO", "US-NYC to/from FR-PAR",
                                    "US to EMEA/India"))

sp <- strsplit(as.character(input$Market.Pair), '\\s+to(/from)?\\s+')

f <- Vectorize(function(x)
  if (grepl('\\-', x)) 'City' else if (nchar(x) == 2) 'Country' else
    if (grepl('^[A-Z]+/[A-Z]+$|^[A-Z]+$', x)) 'Air' else 'Region')

dd <- lapply(sp, function(x) {
  ## set up output matrix
  cn <- sort(levels(interaction(c('Air','City','Country','Region'), 1:2)))
  m <- matrix('', 1, length(cn), dimnames = list(NULL, cn))
  ## use f above and add the suffix
  xx <- f(x)
  nn <- setNames(x, paste(xx, ave(xx, xx, FUN = seq_along), sep = '.'))
  ## match
  m[, names(nn)] <- nn
  m
})
do.call('rbind.data.frame', dd)

#     Air.1   Air.2 City.1 City.2 Country.1 Country.2   Region.1 Region.2
# 1                                      US        CA
# 2     HOU DFW/DAL
# 3 EWR/JFK LAX/SFO
# 4                 US-NYC FR-PAR
# 5                                      US           EMEA/India

关于正则表达式——将一列拆分为多列，在 R 中没有明确的分隔符，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/36001436/