dplyr - How to collapse binary and mutually exclusive columns into 1?

user_2192578 0
Viewed 245

Suppose I have a dataframe with the following structure:

|id  | segment1 | segment 2|
|----|----------|----------|
|1   | 1        |  0       |   
|2   | 1        |  0       |
|3   | 0        |  1       |
|4   | 1        |  0       |
|5   | 0        |  1       |

How do I pivot the data such that, instead of having the 2 "segment" columns, I just have 1 column that says "segment1" or "segment2"? I was thinking about pivot_longer but I don't think we're actually making the data frame any longer. It seems like there is definitely a quicker way than using mutate with an if_else or creating a new column and manually assigning the values.

Note: This transformation should not yield a longer dataframe though, as we're just collapsing columns

dplyr r
4 Answers

We can use max.col from base R to get the index of max value per each row of the 2nd and 3rd column, use that index to get the corresponding column names of the data

cbind(df1[1], segment = names(df1)[-1][max.col(df1[-1], 'first')])
#  id  segment
#1  1 segment1
#2  2 segment1
#3  3 segment2
#4  4 segment1
#5  5 segment2

Or using tidyverse

library(dplyr)
library(tidyr)
df1 %>%
      pivot_longer(cols = -id, names_to = 'segment') %>%
      filter(as.logical(value)) %>%
      select(-value)
# A tibble: 5 x 2
#     id segment 
#  <int> <chr>   
#1     1 segment1
#2     2 segment1
#3     3 segment2
#4     4 segment1
#5     5 segment2

data

df1 <- structure(list(id = 1:5, segment1 = c(1, 1, 0, 1, 0), segment2 = c(0, 
0, 1, 0, 1)), class = "data.frame", row.names = c(NA, -5L))
user_37322710 
      A = data.frame(id = c(1, 2, 3), 
                     segment1 = c(1, 1, 0), 
                     segment2 = c(0, 0, 1))

tidyr::pivot_longer(A, c(segment1, segment2)) %>% 
                           filter(value == 1) %>% 
                            select(-value)
# A tibble: 3 x 2
     id name    
  <dbl> <chr>   
1     1 segment1
2     2 segment1
3     3 segment2
user_75518160

This is how I would do this, you want to pivot_longer and then drop the 0 values I believe.

library(tidyr)
df <- data.frame(id = c(1, 2, 3, 4, 5),
                 segment1 = c(1, 1, 0, 1, 0),
                 segment2 = c(0, 0, 1, 0, 1))
df %>%
  pivot_longer(cols = c("segment1", "segment2")) %>%
  filter(value == 1) %>%
  select(id, name)
user_81620470

Use ifelse

with(DF, ifelse(segment1 == 1, "segment1", "segment2"))

library(dplyr)
DF %>%
  mutate(segment = if_else(segment1 == 1, "segment1", "segment2"))
user_113550660
Related