Separate character string variable into several variables

user_12992972 0
Viewed 57

I have data (a column in a dataframe) of type character. I want to separate these characters and, depending on the content, fill separate variables with 0s and 1s. The column can be recreated with:

df <- data.frame(var = c("1;2", NA, "1;2;3;4;5", "3;5", "1", "1;4", "3", NA, "4", "1;5"))

For example, the characters can range from 1 to 5. I want to create six variables: var_1, var_2, var_3, var_4, var_5, and var_NA. I want var_1 to contain a 1 if that row has a 1 within the character string, and 0 if it does not. Thank you!

character r arrays
2 Answers

Perhaps, using cSplit_e would be an option

library(splitstackshape)  
library(dplyr)
cSplit_e(df, 'var', sep=";", type = 'character', fill = 0, drop = TRUE)%>%
     mutate(var_NA = +(is.na(df$var)))
#    var_1 var_2 var_3 var_4 var_5 var_NA
#1      1     1     0     0     0      0
#2      0     0     0     0     0      1
#3      1     1     1     1     1      0
#4      0     0     1     0     1      0
#5      1     0     0     0     0      0
#6      1     0     0     1     0      0
#7      0     0     1     0     0      0
#8      0     0     0     0     0      1
#9      0     0     0     1     0      0
#10     1     0     0     0     1      0

Or using base R

t(sapply(strsplit(df$var, "[:;]"), function(x) +(1:5 %in% x)))
user_37322710

In tidyverse , we can get the data in long format by splitting on ";", create a column with "var", change all values to 1 and get the data in wide format.

library(dplyr)
library(tidyr)

df %>%
  mutate(row = row_number()) %>%
  separate_rows(var, sep = ";") %>%
  mutate(col = paste0('var_', var), 
         var = 1) %>%
  pivot_wider(names_from = col, values_from = var, values_fill = 0) %>%
  ungroup %>%
  select(-row)

# A tibble: 10 x 6
#   var_1 var_2 var_NA var_3 var_4 var_5
#   <dbl> <dbl>  <dbl> <dbl> <dbl> <dbl>
# 1     1     1      0     0     0     0
# 2     0     0      1     0     0     0
# 3     1     1      0     1     1     1
# 4     0     0      0     1     0     1
# 5     1     0      0     0     0     0
# 6     1     0      0     0     1     0
# 7     0     0      0     1     0     0
# 8     0     0      1     0     0     0
# 9     0     0      0     0     1     0
#10     1     0      0     0     0     1
user_39629140
Related