By RoyalTS


2012-12-08 02:13:44 8 Comments

I have a data frame, like so:

data.frame(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'))

As you can see, some entries in the director column are multiple names separated by commas. I would like to split these entries up into separate rows while maintaining the values of the other column. As an example, the first row in the data frame above should be split into two rows, with a single name each in the director column and 'A' in the AB column.

4 comments

@Jaap 2015-07-20 10:56:48

Several alternatives:

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: Alternatively, you can also use the dplyr / tidyr combination:

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

3) with tidyr only: With tidyr 0.5.0 (and later), you can also just use separate_rows:

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

You can use the convert = TRUE parameter to automatically convert numbers into numeric columns.

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))

@Reilstein 2019-01-19 01:39:55

Is there any way to do this for multiple columns at once? For instance 3 columns that each have strings separated by ";" with each column having the same number of strings. i.e. data.table(id= "X21", a = "chr1;chr1;chr1", b="123;133;134",c="234;254;268") becoming data.table(id = c("X21","X21",X21"), a=c("chr1","chr1","chr1"), b=c("123","133","134"), c=c("234","254","268")) ?

@Reilstein 2019-01-19 01:50:18

wow just realized it already works for multiple columns at once - this is amazing!

@Uwe 2017-04-15 22:21:42

This old question frequently is being used as dupe target (tagged with r-faq). As of today, it has been answered three times offering 6 different approaches but is lacking a benchmark as guidance which of the approaches is the fastest1.

The benchmarked solutions include

Overall 8 different methods were benchmarked on 6 different sizes of data frames using the microbenchmark package (see code below).

The sample data given by the OP consists only of 20 rows. To create larger data frames, these 20 rows are simply repeated 1, 10, 100, 1000, 10000, and 100000 times which give problem sizes of up to 2 million rows.

Benchmark results

enter image description here

The benchmark results show that for sufficiently large data frames all data.table methods are faster than any other method. For data frames with more than about 5000 rows, Jaap's data.table method 2 and the variant DT3 are the fastest, magnitudes faster than the slowest methods.

Remarkably, the timings of the two tidyverse methods and the splistackshape solution are so similar that it's difficult to distiguish the curves in the chart. They are the slowest of the benchmarked methods across all data frame sizes.

For smaller data frames, Matt's base R solution and data.table method 4 seem to have less overhead than the other methods.

Code

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)

Define function for benchmark runs of problem size 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
  )
}

Run benchmark for different problem sizes

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

Prepare data for plotting

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)]

Create chart

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()

Session info & package versions (excerpt)

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)

1My curiosity was piqued by this exuberant comment Brilliant! Orders of magnitude faster! to a tidyverse answer of a question which was closed as a duplicate of this question.

@Frank 2017-04-15 22:59:29

Nice! Looks like room for improvement in cSplit and separate_rows (which are specifically designed to do this). Btw, cSplit also takes a fixed= arg and is a data.table-based package, so might as well give it DT instead of DF. Also fwiw, I don't think conversion from factor to char belongs in the benchmark (since it should be char to begin with). I checked and none of these changes does anything to the results qualitatively.

@Uwe 2017-04-15 23:49:53

@Frank Thank you for your suggestions to improve the benchmarks and for checking the effect on results. Will pick this up when doing an update after release of the next versions of data.table, dplyr, etc.

@Ferroao 2017-06-26 20:08:15

I think the approaches are not comparable, at least not in all occasions, because the datatable approaches only produce tables with the "selected" columns, while dplyr produces a result with all the columns (including the ones not involved in the analysis and without having to write their names in the function).

@Tensibai 2017-06-27 08:37:39

@Ferroao That's wrong, the data.tables approaches modify the "table" in place, all columns are kept, of course if you don't modify in place you get a filtered copy of only what you have asked for. In brief data.table approach is to not produce a resulting dataset but to update the dataset, that's the real difference between data.table and dplyr.

@Matthew Lundberg 2012-12-08 02:36:20

Naming your original data.frame v, we have this:

> 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

Note the use of rep to build the new AB column. Here, sapply returns the number of names in each of the original rows.

@42- 2013-01-03 20:44:13

I'm wondering if ` AB=rep(v$AB, unlist(sapply(s, FUN=length )))` might be easier to grasp than the more obscure vapply? Is there anything that makes vapply more appropriate here?

@Rich Scriven 2017-02-21 01:06:04

Nowadays sapply(s, length) could be replaced with lengths(s).

@A5C1D2H2I1M1N2O1R2T1 2015-02-03 04:34:37

Late to the party, but another generalized alternative is to use cSplit from my "splitstackshape" package that has a direction argument. Set this to "long" to get the result you specify:

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

Related Questions

Sponsored Content

48 Answered Questions

[SOLVED] How to replace all occurrences of a string in JavaScript

59 Answered Questions

[SOLVED] How do I read / convert an InputStream into a String in Java?

81 Answered Questions

[SOLVED] How do I make the first letter of a string uppercase in JavaScript?

47 Answered Questions

24 Answered Questions

[SOLVED] Case insensitive 'Contains(string)'

19 Answered Questions

[SOLVED] Why is char[] preferred over String for passwords?

17 Answered Questions

[SOLVED] Does Python have a string 'contains' substring method?

57 Answered Questions

[SOLVED] What is the difference between String and string in C#?

76 Answered Questions

[SOLVED] How do I iterate over the words of a string?

  • 2008-10-25 08:58:21
  • Ashwin Nanjappa
  • 2088125 View
  • 2781 Score
  • 76 Answer
  • Tags:   c++ string split

33 Answered Questions

[SOLVED] How to split a string in Java

  • 2010-08-14 03:01:53
  • riyana
  • 3443976 View
  • 1430 Score
  • 33 Answer
  • Tags:   java string

Sponsored Content