Converting multiple dummy variables that are not mutually exclusive into single categorical variable, adding new rows

Question

I have the following data frame:

data <- data.frame(id=c(1, 1, 2, 2), 
task=c(1, 2, 1, 2),
strategy1=c("1", "1", "0", "1"),
strategy2=c("0", "0", "1", "1"),
strategy3=c("0", "1", "0", "1"))

My aim is to combine the dummy variables for the different strategies into a single categorical variable, 'strategy'. If multiple strategies were used by a participant during a task, new rows with the same 'id' and 'task' numbers must be created accordingly, as there should be only one 'strategy' variable.

For the given example, the data frame should finally look like this:

data_single <- data.frame(id=c(1, 1, 1, 2, 2, 2, 2),
task=c(1, 2, 2, 1, 2, 2, 2),
strategy=c("1", "1", "3", "2", "1", "2", "3"))

Can anyone show me how I can achieve this?

score 1 · Accepted Answer · answered Apr 27 '20 at 14:40

1

library(tidyr)
library(dplyr)
tidyr::pivot_longer(
  data, 
  cols = starts_with("strategy"),
  names_prefix = "strategy", 
  names_to = "strategy"
) %>%
  filter(value == 1) %>%
  select(-value)
# # A tibble: 7 x 3
#      id  task strategy
#   <dbl> <dbl> <chr>   
# 1     1     1 1       
# 2     1     2 1       
# 3     1     2 3       
# 4     2     1 2       
# 5     2     2 1       
# 6     2     2 2       
# 7     2     2 3

answered Apr 27 '20 at 14:40

Gregor Thomas

136,190
20
167
294

Thank you, this works great. How would I accomplish the reverse (from one categorical variable to multiple dummy variables)? I've tried using fastDummies::dummy_cols. However, I couldn't figure out how to merge rows with the same 'id' and 'task'. I would like the format to be like the one provided above, such that multiple dummy variables can take on a value of 1 in each row. – GFG May 01 '20 at 13:31
See `?tidy::pivot_wider` for the inverse operation in this package. The FAQ on [reshaping data from long to wide](https://stackoverflow.com/q/5890584/903061) will provide examples for several different methods. – Gregor Thomas May 01 '20 at 14:57

score 0 · Answer 2 · answered Apr 27 '20 at 18:16

0

We can also use data.table

library(data.table)
melt(setDT(data), measure = patterns('^strategy'), 
     variable.name = 'strategy')[value == 1, .(id, task, strategy)]

answered Apr 27 '20 at 18:16

akrun

874,273
37
540
662

Converting multiple dummy variables that are not mutually exclusive into single categorical variable, adding new rows

2 Answers2