Use pivot_wider to create unique columns from comma separated vector without values_from column

user_9349302 0
Viewed 517

I have the following use case for pivot_wider:

I have a data set with a comma separated string. I want to create unique columns for each comma separated value like a dummy variable taking 1s (value was present) and 0s (value was not present).

I can do this using the approach shown below. However, I consider this to be a workaround, since I need to add a column with value = 1 which I then use in pivot_widers values_from argument. I tried using values_from = 1 without creating a new column first (I thought pivot_wider could create the values on the fly), but it turns out values_from uses tidyeval and selects the first column instead. I also tried not specifying the argument at all, but that doesn't work neither.

Is there a better way to use pivot_wider without creating a column taking the value 1 for all rows? Since I really use this "workaround" a lot, I just wonder if there is a more official way to reach the same result.

library(dplyr)
library(tidyr)

# data generating function
create_codes <- function(inp, len) {
  
  size <- round(runif(len, 1, 5))
  
  res <- vapply(seq_len(len),
                FUN.VALUE = character(1),
                FUN = function(x) {
                  paste(sample(inp, size[x]), collapse = ", ")
                })
  
}

# toy data
set.seed(123)
dat <- tibble(id = 1:100,
              codes = create_codes(10:25, 100))

# transform codes to unique columns
dat %>% 
  mutate(codes2 = strsplit(codes, ", "),
         # can pivot_wider work without this 'workaround' => 'value = 1'?
         value = 1) %>% 
  unnest(codes2) %>%
  arrange(codes2) %>% 
  pivot_wider(names_from = codes2,
              names_prefix = "code_",
              names_repair = "universal",
              values_from = value,
              values_fill = 0) 

#> # A tibble: 100 x 18
#>       id codes code_10 code_11 code_12 code_13 code_14 code_15 code_16 code_17
#>    <int> <chr>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>
#>  1    11 13, …       1       0       1       1       0       1       0       0
#>  2    13 23, …       1       0       0       0       0       0       0       1
#>  3    25 10, …       1       0       0       1       0       0       0       1
#>  4    30 15, …       1       0       0       0       0       1       0       0
#>  5    37 14, …       1       0       0       0       1       0       1       0
#>  6    47 20, …       1       0       0       0       0       0       0       0
#>  7    59 20, …       1       0       0       0       0       0       0       0
#>  8    60 19, …       1       0       0       0       0       0       0       0
#>  9    66 10, …       1       0       0       0       1       0       0       0
#> 10    67 13, …       1       0       1       1       0       0       0       0
#> # … with 90 more rows, and 8 more variables: code_18 <dbl>, code_19 <dbl>,
#> #   code_20 <dbl>, code_21 <dbl>, code_22 <dbl>, code_23 <dbl>, code_24 <dbl>,
#> #   code_25 <dbl>

Created on 2021-02-16 by the reprex package (v0.3.0)

tidyr r
2 Answers

We can use values_fn with length and this would bypass the need to create a column 'value'. Note, here we assume that the OP's question is about to bypass the creation of value column and not about changing the strsplit

library(dplyr)
library(tidyr)
dat %>% 
     mutate(codes2 = strsplit(codes, ", ")) %>%
     unnest(codes2) %>% 
     arrange(codes2) %>%  
     pivot_wider(names_from = codes2,
            names_prefix = "code_",
            names_repair = "universal", values_from = codes2, 
        values_fill = 0, values_fn = length)

-output

# A tibble: 100 x 18
      id codes code_10 code_11 code_12 code_13 code_14 code_15 code_16 code_17 code_18 code_19 code_20
   <int> <chr>   <int>   <int>   <int>   <int>   <int>   <int>   <int>   <int>   <int>   <int>   <int>
 1    11 13, …       1       0       1       1       0       1       0       0       0       0       0
 2    13 23, …       1       0       0       0       0       0       0       1       0       0       0
 3    25 10, …       1       0       0       1       0       0       0       1       0       0       0
 4    30 15, …       1       0       0       0       0       1       0       0       0       0       0
 5    37 14, …       1       0       0       0       1       0       1       0       0       0       0
 6    47 20, …       1       0       0       0       0       0       0       0       0       0       1
 7    59 20, …       1       0       0       0       0       0       0       0       0       1       1
 8    60 19, …       1       0       0       0       0       0       0       0       0       1       0
 9    66 10, …       1       0       0       0       1       0       0       0       1       0       0
10    67 13, …       1       0       1       1       0       0       0       0       1       0       0
# … with 90 more rows, and 5 more variables: code_21 <int>, code_22 <int>, code_23 <int>, code_24 <int>,
#   code_25 <int>

In case there are duplicates, then we could pass a lambda function as well

dat %>% 
     mutate(codes2 = strsplit(codes, ", ")) %>%
     unnest(codes2) %>% 
     arrange(codes2) %>%  
     pivot_wider(names_from = codes2,
            names_prefix = "code_",
            names_repair = "universal", values_from = codes2, 
        values_fill = 0, values_fn = list(codes2 = ~ +(length(.) > 0)))

Or it can be done more easily with cSplit_e

library(splitstackshape)
cSplit_e(dat, "codes", sep=",", type = 'character', fill = 0, drop = TRUE)
user_37322710

One approach could be:

dat %>%
 separate_rows(codes) %>%
 pivot_wider(values_from = "codes",
             names_from = "codes",
             names_prefix = "code_") %>%
 mutate(across(starts_with("code"), ~ +is.na(.))) 

      id code_19 code_14 code_17 code_24 code_16 code_21 code_15 code_12 code_10 code_22
   <int>   <int>   <int>   <int>   <int>   <int>   <int>   <int>   <int>   <int>   <int>
 1     1       0       0       1       1       1       1       1       1       1       1
 2     2       1       1       0       0       0       0       1       1       1       1
 3     3       0       1       1       0       1       1       0       1       1       1
 4     4       1       0       1       0       1       0       1       0       0       1
 5     5       1       1       0       1       1       0       1       0       1       0
 6     6       1       1       1       1       0       1       1       1       1       1
 7     7       1       0       1       0       1       1       1       1       1       1
 8     8       1       1       1       1       1       1       0       0       1       1
 9     9       1       1       1       1       1       1       1       0       1       1
10    10       1       1       1       0       1       1       1       1       1       1
# … with 90 more rows, and 6 more variables: code_23 <int>, code_20 <int>, code_13 <int>,
#   code_11 <int>, code_18 <int>, code_25 <int>
user_59645570
Related