Separate two columns delineated by semicolons into rows (R)

user_14117190 0
Viewed 68

So, I have two columns, each with multiple items within them. They are separated by semicolons.

I'd like to turn them into multiple rows, with the new rows matched up based on the order in the original rows.

If I just had one column, I'd use separate_rows, but I don't know how to do it with two columns that need to be matched. It's easier to explain with an example:

Reproducible example:

AU <- c("Ali, PB; Naylor, JC", "Warren, EW; Stephens, D")
EM <- c("PatAllen@fake.com; JNaylor@fake.edu","LizWarren@fake.edu.au; Doug@fake.net")
question <- data.frame(AU, EM)

I'd like the data frame to be this:

1 Ali, PB PatAllen@fake.com
2 Naylor, JC JNaylor@fke.edu
3 Warren, EW LizWarren@fake.edu.au
4 Stephens, D Doug@fake.net
tidyr r
2 Answers

tidyr package to the rescue! separate_rows() is a new (?) function that does exactly what you want.

tidyr::separate_rows(question, AU, EM, sep = ";", convert = T)
user_53108880

If you don't want to use the pretty tidyverse formula from Ben, and in case your data always matches name and email by the same position, you could do this also with a for loop

AU <- c("Ali, PB; Naylor, JC", "Warren, EW; Stephens, D")
EM <- c("PatAllen@fake.com; JNaylor@fake.edu","LizWarren@fake.edu.au; Doug@fake.net")
question <- data.frame(AU, EM, stringsAsFactors = FALSE)

df <- data.frame(name=c(), email=c())
for(r in 1:nrow(question)){
  a <- strsplit(question[r,1], "; ")[[1]]
  e <- strsplit(question[r,2], "; ")[[1]]
  
  df <- rbind(df, data.frame(name=a, email=e))
}
df

or even vectorize it if you need to make things faster:

x <- apply(question, 2, strsplit, "; ")
matrix(unlist(x), ncol=2)

Also, watch out to separate by "; " and not ";", because the second part of each cell in your data is led by a whitespace character

user_74242750
Related