tidyr use separate_rows over multiple columns

Question

I have a data.frame where some cells contain strings of comma separate values:

d <- data.frame(a=c(1:3), 
       b=c("name1, name2, name3", "name4", "name5, name6"),
       c=c("name7","name8, name9", "name10" ))

I want to separate those strings where each name is split into its own cell. This is easy with

tidyr::separate_rows(d, b, sep=",") 

if it is done for one column a time. But I can't do this for both columns "b" and "c" at the same time, since it requires that the number of names in each string is the same. Instead of writing

tidyr::separate_rows(d, b, sep=",") 
tidyr::separate_rows(d, c, sep=",") 

Is there a way to do this in a one-liner, for e.g. with apply? Something like

apply(d, 2, separate_rows(...)) 

Not sure how to pass the arguments to the separate_rows() function.

Adductor

Answer 1

You can use a pipe. Note that sep = ", " is automatically detected.

d %>% separate_rows(b) %>% separate_rows(c)
#   a     b      c
# 1 1 name1  name7
# 2 1 name2  name7
# 3 1 name3  name7
# 4 2 name4  name8
# 5 2 name4  name9
# 6 3 name5 name10
# 7 3 name6 name10

Note: Using tidyr version 0.6.0, where the %>% operator is included in the package.

---

Update: Using @doscendodiscimus comment, we could use a for() loop and reassign d in each iteration. This way we can have as many columns as we like. We will use a character vector of column names, so we'll need to switch to the standard evaluation version, separate_rows_.

cols <- c("b", "c")
for(col in cols) {
    d <- separate_rows_(d, col)
}

which gives the updated d

  a     b      c
1 1 name1  name7
2 1 name2  name7
3 1 name3  name7
4 2 name4  name8
5 2 name4  name9
6 3 name5 name10
7 3 name6 name10

As an update: In tidyr1.2.0 separate_rows_ is deprecated. Instead, we can use the following code:

cols <- c("b", "c")
for (col in cols) {
  d <- separate_rows(d, all_of(col))
}

Lambency

Yes, I could just write the same line for each row, but is there a way to do this for n columns? It gets tedious if you have 10 or more columns. Regarding the cols argument, if you include both columns the same time, you get an error. – Adductor

@user23413, you could try a loop like for(col in c("b", "c")) d <- separate_rows_(d, col, sep = ",") if you want to stick with tidyr – Cellarer

@docendodiscimus - Do you want to post that? I agree that's the way to go. – Lambency

@RichScriven, no, you can add that to your post if you like it :) – Cellarer

Ok, if that's the way to go. I just wanted to make sure I don't miss the one-liner apply here. I'm using a for loop currently. – Adductor

Can also try this variation: d %>% Reduce(f = separate_rows_, x = c("b", "c")) – Telescope

Answer 2

Here's an alternative approach using splitstackshape::cSplit and zoo::na.locf.

library(splitstackshape)
library(zoo)

df <- cSplit(d, 1:ncol(d), "long", sep = ",")
na.locf(df[rowSums(is.na(df)) != ncol(df),])
#    a     b      c
#1:  1 name1  name7
#2:  1 name2  name7
#3:  1 name3  name7
#4:  2 name4  name8
#5:  2 name4  name9
#6:  3 name5 name10
#7:  3 name6 name10

Picky

Answer 3

With tidyr version 1.2.0, we can use everything to select all columns to separate the rows on , . As mentioned by @RichScriven, the default separator is sep = ", ".

library(tidyr)

d %>% 
  separate_rows(everything())

Output

      a b     c     
  <int> <chr> <chr> 
1     1 name1 name7 
2     1 name2 name7 
3     1 name3 name7 
4     2 name4 name8 
5     2 name4 name9 
6     3 name5 name10
7     3 name6 name10

Alternatively, we can specify the columns that we want to separate the rows, or we can simply exclude the columns that we don't want.

d %>% 
  separate_rows(b, c)


d %>% 
  separate_rows(-a)

Hurty