Converting columns with multiple values into binary variables

Question

Goal: Clean a data frame that has a column (let's call it, v1) containing one or (often) more than one value in each cell. I would like to generate multiple binary variables (say: v1_1, v1_2, v1_3) based on values contained in the cells in v1. (Reality: I have a very large, ugly excel dataset from elsewhere with many cells that have multiple values and would like to efficiently sort them into binary columns, ideally with tidyverse tools, but base works too).

Reproducible example:

df <- data.frame(caseID = c(1:5),
                 v1 = c(2, 1, "1,3", 1, "2, 3"))
df
desired_df <- data.frame(caseID = c(1:5),
                      v1_1 = c(0, 1, 1, 1, 0),
                      v1_2 = c(1, 0, 0, 0, 1),
                      v1_3 = c(0, 0, 1, 0, 1))
desired_df

Try cbind(df[1], as.data.frame.matrix(table(stack(setNames(strsplit(as.character(df$v1), ",\\s*"), df$caseID))[2:1]))) — akrun
– akrun, Commented Nov 17, 2017 at 17:32

www · Accepted Answer · 2017-11-17 17:53:04Z

2

A solution using dplyr and tidyr.

library(dplyr)
library(tidyr)


df2 <- df %>%
  separate_rows(v1) %>%
  mutate(Value = 1) %>%
  spread(v1, Value, fill = 0) %>%
  rename_at(vars(-caseID), funs(paste0("v1_", .)))
df2 
#   caseID v1_1 v1_2 v1_3
# 1      1    0    1    0
# 2      2    1    0    0
# 3      3    1    0    1
# 4      4    1    0    0
# 5      5    0    1    1

answered Nov 17, 2017 at 17:53

www

39.3k12 gold badges52 silver badges93 bronze badges

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

Collectives™ on Stack Overflow

Converting columns with multiple values into binary variables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related