CODEWITHSUNDEEP
rdplyraggregate
Science11
Science11

Reputation: 883

r collapsing data from multiple columns into one

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

Upvotes: 6

Views: 16210

Answers (4)

SymbolixAU
SymbolixAU

Reputation: 26258

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

Upvotes: 1

www
www

Reputation: 39174

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
  )

Upvotes: 2

G. Grothendieck
G. Grothendieck

Reputation: 270348

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"

Upvotes: 2

Maurits Evers
Maurits Evers

Reputation: 50738

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)

    ))

Upvotes: 7

Related Questions