将列中以逗号分隔的字符串拆分为单独的行

4 回答

• 27

  这个老问题经常被用作欺骗目标（用 r-faq 标记） . 截至今天，已经回答了三次，提供了6种不同的方法，但是 is lacking a benchmark 作为指导哪种方法最快1 .

  基准测试解决方案包括

  • Matthew Lundberg's base R approach但根据Rich Scriven's comment进行了修改，

  • Jaap's两个 data.table 方法和两个 dplyr / tidyr 方法，

  • Ananda's splitstackshapesolution，

  • 和Jaap的 data.table 方法的另外两个变种 .

  总共8种不同的方法使用 microbenchmark 包对6种不同大小的数据帧进行了基准测试（参见下面的代码） .

  OP给出的样本数据仅包含20行 . 为了创建更大的数据帧，这20行简单地重复1次，10次，100次，1000次，10000次和100000次，这给出了最多200万行的问题大小 .

  基准测试结果

  基准测试结果表明，对于足够大的数据帧，所有 data.table 方法都比任何其他方法更快 . 对于具有超过约5000行的数据帧，Jaap的 data.table 方法2和变体 DT3 是最快的，比最慢的方法更快 .

  值得注意的是，两个 tidyverse 方法和 splistackshape 解决方案的时间非常相似，因此难以对图表中的曲线进行分类 . 它们是所有数据帧大小中最慢的基准测试方法 .

  对于较小的数据帧，Matt的基本R解决方案和 data.table 方法4似乎比其他方法具有更少的开销 .

  代码

  director <- 
    c("Aaron Blaise,Bob Walker", "Akira Kurosawa", "Alan J. Pakula", 
      "Alan Parker", "Alejandro Amenabar", "Alejandro Gonzalez Inarritu", 
      "Alejandro Gonzalez Inarritu,Benicio Del Toro", "Alejandro González Iñárritu", 
      "Alex Proyas", "Alexander Hall", "Alfonso Cuaron", "Alfred Hitchcock", 
      "Anatole Litvak", "Andrew Adamson,Marilyn Fox", "Andrew Dominik", 
      "Andrew Stanton", "Andrew Stanton,Lee Unkrich", "Angelina Jolie,John Stevenson", 
      "Anne Fontaine", "Anthony Harvey")
  AB <- c("A", "B", "A", "A", "B", "B", "B", "A", "B", "A", "B", "A", 
          "A", "B", "B", "B", "B", "B", "B", "A")
  
  library(data.table)
  library(magrittr)
  

  为问题大小n的基准运行定义函数

  run_mb <- function(n) {
    # compute number of benchmark runs depending on problem size `n`
    mb_times <- scales::squish(10000L / n , c(3L, 100L)) 
    cat(n, " ", mb_times, "\n")
    # create data
    DF <- data.frame(director = rep(director, n), AB = rep(AB, n))
    DT <- as.data.table(DF)
    # start benchmarks
    microbenchmark::microbenchmark(
      matt_mod = {
        s <- strsplit(as.character(DF$director), ',')
        data.frame(director=unlist(s), AB=rep(DF$AB, lengths(s)))},
      jaap_DT1 = {
        DT[, lapply(.SD, function(x) unlist(tstrsplit(x, ",", fixed=TRUE))), by = AB
           ][!is.na(director)]},
      jaap_DT2 = {
        DT[, strsplit(as.character(director), ",", fixed=TRUE), 
           by = .(AB, director)][,.(director = V1, AB)]},
      jaap_dplyr = {
        DF %>% 
          dplyr::mutate(director = strsplit(as.character(director), ",")) %>%
          tidyr::unnest(director)},
      jaap_tidyr = {
        tidyr::separate_rows(DF, director, sep = ",")},
      cSplit = {
        splitstackshape::cSplit(DF, "director", ",", direction = "long")},
      DT3 = {
        DT[, strsplit(as.character(director), ",", fixed=TRUE),
           by = .(AB, director)][, director := NULL][
             , setnames(.SD, "V1", "director")]},
      DT4 = {
        DT[, .(director = unlist(strsplit(as.character(director), ",", fixed = TRUE))), 
           by = .(AB)]},
      times = mb_times
    )
  }
  

  针对不同的问题规模运行基准

  # define vector of problem sizes
  n_rep <- 10L^(0:5)
  # run benchmark for different problem sizes
  mb <- lapply(n_rep, run_mb)
  

  准备绘图数据

  mbl <- rbindlist(mb, idcol = "N")
  mbl[, n_row := NROW(director) * n_rep[N]]
  mba <- mbl[, .(median_time = median(time), N = .N), by = .(n_row, expr)]
  mba[, expr := forcats::fct_reorder(expr, -median_time)]
  

  创建图表

  library(ggplot2)
  ggplot(mba, aes(n_row, median_time*1e-6, group = expr, colour = expr)) + 
    geom_point() + geom_smooth(se = FALSE) + 
    scale_x_log10(breaks = NROW(director) * n_rep) + scale_y_log10() + 
    xlab("number of rows") + ylab("median of execution time [ms]") +
    ggtitle("microbenchmark results") + theme_bw()
  

  会话信息和包版本（摘录）

  devtools::session_info()
  #Session info
  # version  R version 3.3.2 (2016-10-31)
  # system   x86_64, mingw32
  #Packages
  # data.table      * 1.10.4  2017-02-01 CRAN (R 3.3.2)
  # dplyr             0.5.0   2016-06-24 CRAN (R 3.3.1)
  # forcats           0.2.0   2017-01-23 CRAN (R 3.3.2)
  # ggplot2         * 2.2.1   2016-12-30 CRAN (R 3.3.2)
  # magrittr        * 1.5     2014-11-22 CRAN (R 3.3.0)
  # microbenchmark    1.4-2.1 2015-11-25 CRAN (R 3.3.3)
  # scales            0.4.1   2016-11-09 CRAN (R 3.3.2)
  # splitstackshape   1.4.2   2014-10-23 CRAN (R 3.3.3)
  # tidyr             0.6.1   2017-01-10 CRAN (R 3.3.2)
  

  ---

  这个充满激情的评论Brilliant激起了一股好奇心！秩序更快！对作为这个问题的副本而被关闭的问题的一个整体回答 .

  回复于 2024-05-03T08:14:11+08:00
• 45

  命名原始data.frame v ，我们有：

  > s <- strsplit(as.character(v$director), ',')
  > data.frame(director=unlist(s), AB=rep(v$AB, sapply(s, FUN=length)))
                        director AB
  1                 Aaron Blaise  A
  2                   Bob Walker  A
  3               Akira Kurosawa  B
  4               Alan J. Pakula  A
  5                  Alan Parker  A
  6           Alejandro Amenabar  B
  7  Alejandro Gonzalez Inarritu  B
  8  Alejandro Gonzalez Inarritu  B
  9             Benicio Del Toro  B
  10 Alejandro González Iñárritu  A
  11                 Alex Proyas  B
  12              Alexander Hall  A
  13              Alfonso Cuaron  B
  14            Alfred Hitchcock  A
  15              Anatole Litvak  A
  16              Andrew Adamson  B
  17                 Marilyn Fox  B
  18              Andrew Dominik  B
  19              Andrew Stanton  B
  20              Andrew Stanton  B
  21                 Lee Unkrich  B
  22              Angelina Jolie  B
  23              John Stevenson  B
  24               Anne Fontaine  B
  25              Anthony Harvey  A
  

  注意使用 rep 来构建新的AB列 . 这里， sapply 返回每个原始行中的名称数 .

  回复于 2024-05-03T08:14:11+08:00
• 67

  几种选择：

  1) two ways with data.table:

  library(data.table)
  # method 1 (preferred)
  setDT(v)[, lapply(.SD, function(x) unlist(tstrsplit(x, ",", fixed=TRUE))), by = AB
           ][!is.na(director)]
  # method 2
  setDT(v)[, strsplit(as.character(director), ",", fixed=TRUE), by = .(AB, director)
           ][,.(director = V1, AB)]
  

  2) a dplyr/tidyr combination: 或者，您也可以使用 dplyr / tidyr 组合：

  library(dplyr)
  library(tidyr)
  v %>% 
    mutate(director = strsplit(as.character(director), ",")) %>%
    unnest(director)
  

  3) with tidyr only: 使用tidyr 0.5.0（及更高版本），您也可以使用 separate_rows ：

  separate_rows(v, director, sep = ",")
  

  您可以使用 convert = TRUE 参数自动将数字转换为数字列 .

  4) with base R:

  # if 'director' is a character-column:
  stack(setNames(strsplit(df$director,','), df$AB))
  
  # if 'director' is a factor-column:
  stack(setNames(strsplit(as.character(df$director),','), df$AB))
  

  回复于 2024-05-03T08:14:11+08:00
• 55

  晚到派对，但另一个通用的替代方法是使用"splitstackshape"包来自"splitstackshape"包含 direction 参数 . 将其设置为 "long" 以获取您指定的结果：

  library(splitstackshape)
  head(cSplit(mydf, "director", ",", direction = "long"))
  #              director AB
  # 1:       Aaron Blaise  A
  # 2:         Bob Walker  A
  # 3:     Akira Kurosawa  B
  # 4:     Alan J. Pakula  A
  # 5:        Alan Parker  A
  # 6: Alejandro Amenabar  B
  

  回复于 2024-05-03T08:14:11+08:00