How to Separate Comma Separated Values in R in a New Row

Split delimited strings in a column and insert as new rows

Here is another way of doing it..

df <- read.table(textConnection("1|a,b,c\n2|a,c\n3|b,d\n4|e,f"), header = F, sep = "|", stringsAsFactors = F)

df
##   V1    V2
## 1  1 a,b,c
## 2  2   a,c
## 3  3   b,d
## 4  4   e,f

s <- strsplit(df$V2, split = ",")
data.frame(V1 = rep(df$V1, sapply(s, length)), V2 = unlist(s))
##   V1 V2
## 1  1  a
## 2  1  b
## 3  1  c
## 4  2  a
## 5  2  c
## 6  3  b
## 7  3  d
## 8  4  e
## 9  4  f

How to separate comma separated values in R in a new row?

You could try cSplit from my "splitstackshape" package:

library(splitstackshape)
cSplit(as.data.table(mydf)[, col2 := gsub("[][\"]", "", col2)], 
       "col2", ",", "long")
#    col1 col2
# 1:    a    1
# 2:    a    2
# 3:    a    3
# 4:    b    1
# 5:    b    2
# 6:    c    4

---

Of course, I'm highly partial to cSplit, but you can also use "dplyr" and unnest from "tidyr":

library(dplyr)
library(tidyr)

mydf %>%
  mutate(col2 = strsplit(gsub("[][\"]", "", col2), ",")) %>%
  unnest(col2)

Or just with "data.table":

library(data.table)
as.data.table(mydf)[, list(
  col2 = unlist(strsplit(gsub("[][\"]", "", col2), ","))), 
  by = col1]

Split comma-separated strings in a column into separate rows

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 fastest¹.

The benchmarked solutions include

• Matthew Lundberg's base R approach but modified according to Rich Scriven's comment,
• Jaap's two data.table methods and two dplyr / tidyr approaches,
• Ananda's splitstackshapesolution,
• and two additional variants of Jaap's data.table methods.

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

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)

---

¹_{My 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.}

How to break comma-separated values in different rows in r

We can use separate_rows

library(dplyr)
library(tidyr)
separate_rows(df1, values, sep=",") %>%
      mutate(names = make.unique(names, sep="_"))

---

Or we create a sequence column by group and then paste

separate_rows(df1, values, sep=",") %>% 
       group_by(names) %>%
       mutate(names1 = if(n()>1) paste(names, row_number(), sep="_") else names) %>%
       ungroup() %>%
       select(names1, values) %>% 
       rename(names= names1)

Splitting a string into new rows in R

Try the cSplit function (as you already using @Anandas package). Note that is will return a data.table object, so make sure you have this package installed. You can revert back to data.frame (if you want to) by doing something like setDF(df2)

library(splitstackshape)
df2 <- cSplit(df1, "Item.Code", sep = "/", direction = "long")
df2
#     Country Region Molecule      Item.Code
#  1:     IND     NA    PB102    FR206985511
#  2:    THAI     AP    PB103      BA-107603 
#  3:    THAI     AP    PB103     F000113361 
#  4:    THAI     AP    PB103         107603
#  5:    LUXE     NA    PB105        1012701 
#  6:    LUXE     NA    PB105    SGP-1012701 
#  7:    LUXE     NA    PB105     F041701000
#  8:     IND     AP    PB106    AU206985211 
#  9:     IND     AP    PB106  CA-F206985211
# 10:    THAI     HP    PB107     F034702000 
# 11:    THAI     HP    PB107        1010701 
# 12:    THAI     HP    PB107    SGP-1010701
# 13:    BANG     NA    PB108     F000007970
# 14:    BANG     NA    PB108          25781
# 15:    BANG     NA    PB108       20009021

Splitting a string into new rows in R

Try the cSplit function (as you already using @Anandas package). Note that is will return a data.table object, so make sure you have this package installed. You can revert back to data.frame (if you want to) by doing something like setDF(df2)

library(splitstackshape)
df2 <- cSplit(df1, "Item.Code", sep = "/", direction = "long")
df2
#     Country Region Molecule      Item.Code
#  1:     IND     NA    PB102    FR206985511
#  2:    THAI     AP    PB103      BA-107603 
#  3:    THAI     AP    PB103     F000113361 
#  4:    THAI     AP    PB103         107603
#  5:    LUXE     NA    PB105        1012701 
#  6:    LUXE     NA    PB105    SGP-1012701 
#  7:    LUXE     NA    PB105     F041701000
#  8:     IND     AP    PB106    AU206985211 
#  9:     IND     AP    PB106  CA-F206985211
# 10:    THAI     HP    PB107     F034702000 
# 11:    THAI     HP    PB107        1010701 
# 12:    THAI     HP    PB107    SGP-1010701
# 13:    BANG     NA    PB108     F000007970
# 14:    BANG     NA    PB108          25781
# 15:    BANG     NA    PB108       20009021

Split strings by commas into columns

Here are several different ways to extract and use the data sticking with read.table().

I started with two fake sets of data. In one with nothing of value in the column name (real column name is in row one).

df1 <- data.frame("V1" = c("A,B,C,D", 
                           "AA,D,E,F3", 
                           "Car1,Car2,Car3,Car4",
                           "a,b,c,d",
                           "a1,b1,c1,d1"))
#                    V1
# 1             A,B,C,D
# 2           AA,D,E,F3
# 3 Car1,Car2,Car3,Car4
# 4             a,b,c,d
# 5         a1,b1,c1,d1 

In the other, the string that is listed as the column name is a list of the would-be names.

df2 <- data.frame("A,B,C,D" = c("AA,D,E,F3", 
                                "Car1,Car2,Car3,Car4",
                                "a,b,c,d",
                                "a1,b1,c1,d1"), 
                  check.names = F)
#               A,B,C,D
# 1           AA,D,E,F3
# 2 Car1,Car2,Car3,Car4
# 3             a,b,c,d
# 4         a1,b1,c1,d1 

To extract the names and values delimited by a comma, where the would-be headings are in row 1 (using df1).

# single data.frame with headers concatenated in the first row
df.noHeader <- read.table(col.names = unlist(strsplit(df1[1,], 
                                                      split = "[,]")),
                          sep = ",",
                          skip = 1, # since the headers were in row 1
                          text = unlist(df1, use.names = F)) 
#      A    B    C    D
# 1   AA    D    E   F3
# 2 Car1 Car2 Car3 Car4
# 3    a    b    c    d
# 4   a1   b1   c1   d1 

For clarity, this is what works for when the names are in the column name of the original data frame.

# splitting the original header when splitting the data
df.header <- read.table(col.names = unlist(strsplit(names(df2), 
                                                    split = "[,]")),
                        sep = ",", 
                        text = unlist(df2))
#      A    B    C    D
# 1   AA    D    E   F3
# 2 Car1 Car2 Car3 Car4
# 3    a    b    c    d
# 4   a1   b1   c1   d1 

If you had the headings in some other row, you only need to change the value in the call to strsplit(), like this:

# if the headers were in row 2
df.noHeader <- read.table(col.names = unlist(strsplit(df1[2,], # <- see 2 here
                                                      split = "[,]")),
                          sep = ",",
                          skip = 2,  # since the headers were in row 2
                          text = unlist(df1, use.names = F))
#     AA    D    E   F3
# 1 Car1 Car2 Car3 Car4
# 2    a    b    c    d
# 3   a1   b1   c1   d1 

# if the headers were in row 3
df.noHeader <- read.table(col.names = unlist(strsplit(df1[3,], # <- see 3 here
                                                      split = "[,]")),
                          sep = ",",
                          skip = 3, # since the headers were in row 3
                          text = unlist(df1, use.names = F))
#   Car1 Car2 Car3 Car4
# 1    a    b    c    d
# 2   a1   b1   c1   d1 

Separate Comma Delimited Cells To New Rows

Here's an approach that should work for you. I'm assuming that your three input vectors are in different objects. We are going to create a list of those inputs and write a function that process each object and returns them in the form of a data.frame with plyr.

The things to take note of here are the splitting of the character vector into it's component parts, then using as.numeric to convert the numbers from the character form when they were split. Since R fills matrices by column, we define a 2 column matrix and let R fill the values for us. We then retrieve the Name column and put it all together in a data.frame. plyr is nice enough to process the list and convert it into a data.frame for us automatically.

library(plyr)

a <- paste("A",1, 2,3,4,5,6, sep = ",", collapse = "")
b <- paste("B",1, 2,4,5, sep = ",", collapse = "")
c <- paste("C",1, 2,3,4,6,7,8,9, sep = ",", collapse = "")

input <- list(a,b,c)

splitter <- function(x) {
    x <- unlist(strsplit(x, ","))
    out <- data.frame(x[1], matrix(as.numeric(x[-1]), ncol = 2))
    colnames(out) <- c("Name", "Start", "End")
    return(out)
}


ldply(input, splitter)

And the output:

> ldply(input, splitter)
 Name Start End
1    A     1   4
2    A     2   5
3    A     3   6
4    B     1   4
5    B     2   5
6    C     1   6
7    C     2   7
8    C     3   8
9    C     4   9

R separate comma separated cells into rows and Cartesian product

There are times when a for loop is totally fine to work with in R. This is one of those times. Try:

library(splitstackshape)
cols <- c("name", "new")
for (i in cols) {
  mydf <- cSplit(mydf, i, ",", "long")
}

mydf
##     name AB new
##  1:   AB  A   1
##  2:   AB  A   2
##  3:   AB  A   3
##  4:   BW  A   1
##  5:   BW  A   2
##  6:   BW  A   3
##  7:    x  B   4
##  8:    x  B   5
##  9:    x  B   6
## 10:    x  B   7
## 11:    y  B   4
## 12:    y  B   5
## 13:    y  B   6
## 14:    y  B   7
## 15:    z  B   4
## 16:    z  B   5
## 17:    z  B   6
## 18:    z  B   7

---

Here's a small test using slightly bigger data:

# concat.test = sample data from "splitstackshape"
test <- do.call(rbind, replicate(5000, concat.test, FALSE))

fun1 <- function() {
  cols <- c("Likes", "Siblings")
  for (i in cols) {
    test <- cSplit(test, i, ",", "long")
  }
  test
}

fun2 <- function() {
  test %>%
    separate_rows("Likes") %>%
    separate_rows("Siblings")
}

system.time(fun1())
#   user  system elapsed 
#  3.205   0.056   3.261 
system.time(fun2())
#   user  system elapsed 
# 11.598   0.066  11.662

---

Related Topics