Convert columns to comma separated list if 1

user_9195104 0
Viewed 1193

I am trying to convert some columns containing either 1 or 0 to a comma separated list with its column names.

More specific, with an example:

df <- data.frame(id = c(1, 2, 3, 4), A = c("0", "1", "1", "0"), B = c("1", "1", "0", "0"), C = c("0", "0", "1", "1"))

If columns A, B and C are equal to 1, a new "list" column containing all comma separated "tags" (column names) should be created.

id  A   B   C
1   0   1   0
2   1   1   0
3   1   0   1
4   0   0   1

Desired result:

id  A   B   C   list
1   0   1   0   B
2   1   1   0   A, B
3   1   0   1   A, C
4   0   0   1   C
dplyr r
3 Answers

Here is an option with apply from base R

df$new <- apply(df[-1], 1, function(x) toString(names(x)[x==1]))

Here, we are creating a string column, but if the intention is to create a list column

df$new <- apply(df[-1], 1, function(x) names(x)[x==1])
user_37322710

Since question is tagged with dplyr, here's a solution using that and tidyr:

library(dplyr)
library(tidyr)

df1 %>% 
  gather(col, val, A:C) %>% 
  group_by(id) %>% 
  filter(val==1) %>% 
  select(-val) %>% 
  nest(list = col) %>% 
  right_join(df1, .) %>% 
  arrange(id)

#>   id A B C list
#> 1  1 0 1 0    B
#> 2  2 1 1 0 A, B
#> 3  3 1 0 1 A, C
#> 4  4 0 0 1    C

Update:

If you want the column to be class of character, then following works:

df1 %>% 
  gather(col, val, A:C) %>% 
  group_by(id) %>% 
  filter(val==1) %>% 
  select(-val) %>% 
  summarise_each(list(~paste(., collapse = ", "))) %>% 
  right_join(df1, .) %>% 
  as_tibble()

#>      id A     B     C     col  
#>   <dbl> <fct> <fct> <fct> <chr>
#> 1     1 0     1     0     B    
#> 2     2 1     1     0     A, B 
#> 3     3 1     0     1     A, C 
#> 4     4 0     0     1     C
user_64614620

Below is the simple for loop based solution using base R



df[1,]<-c(0,1,0)
df[2,]<-c(1,1,0)
df[3,]<-c(1,0,1)
df[4,]<-c(0,0,1)

for (i in 1:ncol(df)){
  x1<-which(df[i,]==1)
  y<-colnames(df)
  print(y[x1])
}

#output
[1] "B"
[1] "A" "B"
[1] "A" "C"
[1] "C"
user_77021200
Related