How to convert list of characters to columns

user_9579974 0
Viewed 163

I have a data frame, column C is character

df <- data.frame(A = c(13, 15, 17), B = c("yes", "no", "yes"), C = c("Mon, Thu, Sun", "Thu, Tue, Fri", "Sat, Mon, Wen"))

    A   B   C
1   13  yes Mon, Thu, Sun
2   15  no  Thu, Tue, Fri
3   17  yes Sat, Mon, Wen

How can I convert data.frame column C to:

    A   B   Sun Mon Tue Wen Thu Fri Sat
1   13  yes 1   1   0   0   1   0   0
2   15  no  0   0   1   0   1   1   0
3   17  yes 0   1   0   1   0   0   1
data-manipulation r
3 Answers

Convert it to long form using separate_rows, add a value column and convert C to factor and then spread it back to wide form.

library(dplyr)
library(tidyr)

days.abb <- c("Sun", "Mon", "Tue", "Wen", "Thu", "Fri", "Sat")
df %>%
  separate_rows(C) %>%
  mutate(value = 1, C = factor(C, days.abb)) %>%
  spread(C, value, fill = 0)

giving:

   A   B Sun Mon Tue Wen Thu Fri Sat
1 13 yes   1   1   0   0   1   0   0
2 15  no   0   0   1   0   1   1   0
3 17 yes   0   1   0   1   0   0   1
user_5165480

One dplyr and tidyr option could be:

df %>%
 mutate(C = strsplit(as.character(C), ", ", fixed = TRUE)) %>%
 unnest(C) %>%
 mutate(C_val = 1) %>%
 pivot_wider(names_from = C, values_from = C_val, values_fill = list(C_val = 0))

      A B       Mon   Thu   Sun   Tue   Fri   Sat   Wen
  <dbl> <fct> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1    13 yes       1     1     1     0     0     0     0
2    15 no        0     1     0     1     1     0     0
3    17 yes       1     0     0     0     0     1     1

Or altenatively:

df %>%
 separate_rows(C) %>%
 mutate(C_val = 1) %>%
 pivot_wider(names_from = C, values_from = C_val, values_fill = list(C_val = 0))
user_59645570

An approach with base R:

  1. Applied to the C column, substitute ",\\s" (comma followed by a white-space) by a "|"
  2. Use grepl with the resulting regular expressions to check which days in days.abb are present
  3. Combine the resulting binary vectors by rows and cbind to the existing A and B column
## data
df <- data.frame(
    A = c(13, 15, 17), 
    B = c("yes", "no", "yes"), 
    C = c("Mon, Thu, Sun", "Thu, Tue, Fri", "Sat, Mon, Wen")
)

## abbreviated weekdays
days.abb <- c("Sun", "Mon", "Tue", "Wen", "Thu", "Fri", "Sat")

## find weekday indices for each character in column C
df1 <- cbind(df[, -3], t(sapply(df[, 3], function(x) 1 * grepl(gsub(",\\s", "|", x), days.abb))))

## update column names
setNames(df1, c("A", "B", days.abb)) 
#>    A   B Sun Mon Tue Wen Thu Fri Sat
#> 1 13 yes   1   1   0   0   1   0   0
#> 2 15  no   0   0   1   0   1   1   0
#> 3 17 yes   0   1   0   1   0   0   1
user_107665900
Related