How to convert multiple binary columns into a single character column?

Question

I would like to convert data frame df1 into data frame df2.

id <- c(1,2,3)
outcome_1 <- c(1,0,1)
outcome_2 <- c(1,1,0)
df1 <- data.frame(id,outcome_1,outcome_2)

id <- c(1,2,3)
outcome <- c("1,2","2","1")
df2 <- data.frame(id,outcome)

The answers to the following question almost do what I want, but in my case a row can have more than one positive outcome (e.g. first row needs to be "1,2"). Also, I would like the resulting column to be a character column.

R: Converting multiple binary columns into one factor variable whose factors are binary column names

Please kindly help. Thank you.

jay.sf · Accepted Answer · 2022-07-03 14:50:14Z

2

Subset the substrings of the outcomes with their binary values coerced as.logical.

apply(df1[-1], 1, \(x) toString(substring(names(df1)[-1], 9)[as.logical(x)]))
# [1] "1, 2" "2"    "1"

or

apply(df1[-1], 1, \(x) paste(substring(names(df1)[-1], 9)[as.logical(x)], collapse=','))
# [1] "1,2" "2"   "1"

Using the first method:

cbind(df1[1], outcome=apply(df1[-1], 1, \(x) toString(substring(names(df1)[-1], 9)[as.logical(x)])))
#   id outcome
# 1  1    1, 2
# 2  2       2
# 3  3       1

If you want a nested list you may use list2DF.

l <- list2DF(c(df1[1],
               outcome=list(apply(df1[-1], 1, \(x) 
                                  as.numeric(substring(names(df1)[-1], 9))[as.logical(x)]))))
l
#   id outcome
# 1  1    1, 2
# 2  2       2
# 3  3       1

where

str(l)
# 'data.frame': 3 obs. of  2 variables:
#   $ id     : num  1 2 3
# $ outcome:List of 3
# ..$ : num  1 2
# ..$ : num 2
# ..$ : num 1

Data:

df1 <- structure(list(id = c(1, 2, 3), outcome_1 = c(1, 0, 1), outcome_2 = c(1, 
1, 0)), class = "data.frame", row.names = c(NA, -3L))

edited Jul 3, 2022 at 14:50

answered Jul 3, 2022 at 14:05

jay.sf

76.3k8 gold badges66 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

marc.th Over a year ago

This is giving me an error message: Error: unexpected input in "cbind(df1[1], outcome=apply(df1[-1], 1, \" It might be that I am missing something obvious as I am still relatively new to R.

jay.sf Over a year ago

@marc.th Oh, you are probably newer than your R version. Since R4.1 we can write shorthand \(x) for function(x), so if you change that in the code and it will work. I recommend to always use the newest R, though. Cheers!

TarJae · Accepted Answer · 2022-07-03 14:43:47Z

0

Here is one more tidyverse approach:

library(dplyr)
library(tidyr)

df1 %>% 
  mutate(across(-id, ~case_when(. == 1 ~ cur_column()), .names = 'new_{col}'), .keep="unused") %>% 
  unite(outcome, starts_with('new'), na.rm = TRUE, sep = ', ') %>% 
  mutate(outcome = gsub('outcome_', '', outcome))

  id outcome
1  1    1, 2
2  2       2
3  3       1

answered Jul 3, 2022 at 14:43

TarJae

80.2k6 gold badges30 silver badges94 bronze badges

Comments

Fursham Hamid · Accepted Answer · 2022-07-03 15:57:16Z

0

How many outcome_ columns are there? If just 2, this will work fine.

library(dplyr) 

df1 %>% 
    rowwise() %>% 
    summarise(id = id, 
              outcome = paste(which(c(outcome_1,outcome_2)==1), collapse =",")) 

# A tibble: 3 x 2
     id outcome
  <dbl> <chr>  
1     1 1,2    
2     2 2      
3     3 1

If there are more than 2, try this:

df1 %>% 
    rowwise() %>% 
    summarise(id=id, 
              outcome = paste(which(c_across(-id)== 1), collapse =","))

edited Jul 3, 2022 at 15:57

answered Jul 3, 2022 at 15:54

Fursham Hamid

215 bronze badges

Comments

PaulS · Accepted Answer · 2022-07-03 22:48:24Z

0

Another possible solution, based on dplyr and purrr::pmap:

library(tidyverse)

df1 %>%
  transmute(id, outcome = pmap(., ~ c(1*..2, 2*..3) %>% .[. != 0] %>% toString))

#>   id outcome
#> 1  1    1, 2
#> 2  2       2
#> 3  3       1

Or simply:

library(tidyverse)

pmap_dfr(df1, ~ data.frame(id = ..1, outcome = c(1*..2, 2*..3) %>% .[. != 0]
   %>% toString))

#>   id outcome
#> 1  1    1, 2
#> 2  2       2
#> 3  3       1

edited Jul 3, 2022 at 22:48

answered Jul 3, 2022 at 15:12

PaulS

27.1k3 gold badges19 silver badges40 bronze badges

Comments

hello_friend · Accepted Answer · 2022-07-04 15:45:48Z

-1

outcome_col_idx <- grepl("outcome", colnames(df1))
cbind(
  df1[,!outcome_col_idx, drop = FALSE],
  outcome = apply(
    replace(df1, df1 == 0, NA)[,outcome_col_idx],
    1,
    function(x){
      as.factor(
        toString(
          gsub(
            "outcome_", 
            "", 
            names(x)[complete.cases(x)]
          )
        )
      )
    }
  )
)

edited Jul 4, 2022 at 15:45

answered Jul 3, 2022 at 14:22

hello_friend

5,8381 gold badge13 silver badges18 bronze badges

1 Comment

guasi Over a year ago

outcome_col_idx is not defined in this solution

Collectives™ on Stack Overflow

How to convert multiple binary columns into a single character column?

5 Answers 5

2 Comments

Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related