r collapsing data from multiple columns into one

Question

I know there are many questions on this topic so I apologize if this is a duplicate question. I'm trying to collapse multiple columns in a data set into one column:

Assuming this is the structure of the dataset I am working with,

df <- data.frame(
      cbind(
      variable_1 = c('Var1', NA, NA,'Var1'),
      variable_2 = c('Var2', 'No', NA, NA),
      variable_3 = c(NA, NA, 'Var3', NA),
      variable_4 = c(NA, 'Var4', NA, NA),
      variable_5 = c(NA, 'No', 'Var5', NA),
      variable_6 = c(NA, NA, 'Var6', NA)
      
    ))

 variable_1  variable_2  variable_3  variable_4  variable_5  variable_6 
 Var1        Var2        NA          NA          NA          NA
 NA          No          NA          Var4        No          NA
 NA          NA          Var3        NA          Var5        Var6
 Var1        NA          NA          NA          NA          NA

What I am expecting is a one column variable_7 like this

 variable_1  variable_2  variable_3  variable_4  variable_5  variable_6  variable_7
 Var1        Var2        NA          NA          NA          NA          Var1, Var2
 NA          No          NA          Var4        No          NA          Var4
 NA          NA          Var3        NA          Var5        Var6        Var3, Var5, Var6
 Var1        NA          NA          NA          NA          NA          Var1

Maurits Evers · Accepted Answer · 2017-12-12T00:17:04.453

df$variable_7 <- apply(df, 1, function(x) paste(x[!is.na(x) & x != "No"], collapse = ", "));
df;
#  variable_1 variable_2 variable_3 variable_4 variable_5 variable_6
#1       Var1       Var2       <NA>       <NA>       <NA>       <NA>
#2       <NA>         No       <NA>       Var4         No       <NA>
#3       <NA>       <NA>       Var3       <NA>       Var5       Var6
#4       Var1       <NA>       <NA>       <NA>       <NA>       <NA>
#        variable_7
#1       Var1, Var2
#2             Var4
#3 Var3, Var5, Var6
#4             Var1

Explanation: Use apply and paste(..., collapse = ", ") to concatenate all row entries (except NAs and "No"s) and store in new column variable_7.

Sample data

df <- data.frame(
      cbind(
      variable_1 = c('Var1', NA, NA,'Var1'),
      variable_2 = c('Var2', 'No', NA, NA),
      variable_3 = c(NA, NA, 'Var3', NA),
      variable_4 = c(NA, 'Var4', NA, NA),
      variable_5 = c(NA, 'No', 'Var5', NA),
      variable_6 = c(NA, NA, 'Var6', NA)

    ))

that is exactly what I was expecting, Thx a ton. – Science11 Dec 12 '17 at 00:22 — Science11, Dec 12 '17 at 00:22
Great, glad to help @Science11 – Maurits Evers Dec 12 '17 at 00:22 — Maurits Evers, Dec 12 '17 at 00:22

G. Grothendieck · Answer 2 · 2017-12-12T00:18:41.240

I gather that if there are n rows then objective is to create a an n-vector of comma-separated character strings of those values in each row that contain the characters Var. (If you intended some other criterion for separating the desired and undesired values then change the grep accordingly.)

apply(df, 1, function(x) toString(grep("Var", x, value = TRUE)))
## [1] "Var1, Var2"       "Var4"             "Var3, Var5, Var6" "Var1"

score 2 · Answer 3 · answered Dec 12 '17 at 00:58

A solution using dplyr. df4 is the final output. Please see how I created the data frame df. The cbind is not required, and it would be great to add stringsAsFactors = FALSE to prevent the creation of factor columns.

library(dplyr)
library(tidyr)

df2 <- df %>% mutate(ID = 1:n()) 

df3 <- df2 %>%
  gather(Variable, Value, -ID, na.rm = TRUE) %>%
  filter(!Value %in% "No") %>%
  group_by(ID) %>%
  summarise(variable_7 = toString(Value))

df4 <- df2 %>% 
  left_join(df3, by = "ID") %>%
  select(-ID) 

df4
#   variable_1 variable_2 variable_3 variable_4 variable_5 variable_6       variable_7
# 1       Var1       Var2       <NA>       <NA>       <NA>       <NA>       Var1, Var2
# 2       <NA>         No       <NA>       Var4         No       <NA>             Var4
# 3       <NA>       <NA>       Var3       <NA>       Var5       Var6 Var3, Var5, Var6
# 4       Var1       <NA>       <NA>       <NA>       <NA>       <NA>             Var1

DATA

df <- data.frame(
    variable_1 = c('Var1', NA, NA,'Var1'),
    variable_2 = c('Var2', 'No', NA, NA),
    variable_3 = c(NA, NA, 'Var3', NA),
    variable_4 = c(NA, 'Var4', NA, NA),
    variable_5 = c(NA, 'No', 'Var5', NA),
    variable_6 = c(NA, NA, 'Var6', NA),
    stringsAsFactors = FALSE
  )

SymbolixAU · Answer 4 · 2017-12-12T01:59:53.657

1

Using a data.table 'reshap'-ing approach rather than a loop/apply

library(data.table)
setDT(df)

df[, id := .I][
    melt(df, id.vars = "id")[grepl("Var", value), .(variable_7 = paste0(value, collapse = ",")), by = .(id)]
    , on = "id"
    , nomatch = 0
    ][order(id)]


#    variable_1 variable_2 variable_3 variable_4 variable_5 variable_6 id     variable_7
# 1:       Var1       Var2         NA         NA         NA         NA  1      Var1,Var2
# 2:         NA         No         NA       Var4         No         NA  2           Var4
# 3:         NA         NA       Var3         NA       Var5       Var6  3 Var3,Var5,Var6
# 4:       Var1         NA         NA         NA         NA         NA  4           Var1

edited Dec 12 '17 at 01:59

answered Dec 12 '17 at 00:31

SymbolixAU

25,502
4
67
139

Nice solution, but it seems like `No` was removed during the process. – www Dec 12 '17 at 00:59
@www - I (maybe incorrectly) _thought_ that was part of the requirement – SymbolixAU Dec 12 '17 at 01:03
OP's expected output still has "No", but it is something minor and it does not affect the effectiveness of your solution. – www Dec 12 '17 at 01:08
@www - in `variable_7` column? I've maintained them in `variable_5` and `variable_2` – SymbolixAU Dec 12 '17 at 01:10
I think it is good to keep `No` on the second row of both 'variable_5' and 'variable_2', which is their original positions. – www Dec 12 '17 at 01:22
1

@www - I think there's some confusion, but I've added `order(id)` to the chain to keep the ordering :) – SymbolixAU Dec 12 '17 at 01:57

r collapsing data from multiple columns into one

4 Answers4

Sample data

Linked