collapse rows into one for repeated observations and concatenate multiple non-repeating variables into single variable in r data frame

Question

Pretty sure this hasn't been asked.

> have
     x1     x2        x3
1 Apple Banana Potassium
2 Apple Banana  Thiamine

> want
     x1     x2                   x3
1 Apple Banana Potassium / Thiamine

x1 and x2 are like ID variables and x3 is a categorical value. A similar question has been asked here: Condensing multiple observations on the same individual into a single row, adding multiples as new columns but it results in additional columns with NA values for observations where categorical values are unavailable. I tried to convert NA values to " " and pasting them together. The result is not desirable. Looks like this when there are multiple NA values replaced by blanks. "Potassium / / / "

Answer 1

Here is my attempt. I modified your data; I added two rows with NAs. I converted x3 to character and replaced NA with "". Using toString and summarise, I combined all elements in x3. Finally, I changed , to / as described in your question.

mydf <- structure(list(x1 = structure(c(1L, 1L, 1L, 1L), .Label = "Apple", class = "factor"), 
x2 = structure(c(1L, 1L, 1L, 1L), .Label = "Banana", class = "factor"), 
x3 = structure(c(1L, 2L, NA, NA), .Label = c("Potassium", 
"Thiamine"), class = "factor")), .Names = c("x1", "x2", "x3"
), class = "data.frame", row.names = c("1", "2", "3", "4"))

#     x1     x2        x3
#1 Apple Banana Potassium
#2 Apple Banana  Thiamine
#3 Apple Banana      <NA>
#4 Apple Banana      <NA>

library(dplyr)
mutate(group_by(mydf, x1, x2), 
       x3 = replace(as.character(x3), !complete.cases(x3), "")) %>%
summarise(x3 = paste(x3, collapse = " / "))

#     x1     x2                         x3
#1 Apple Banana Potassium / Thiamine /  /