Spread and sort concatenated factor variable in data.frame

Question

I have a data.frame that looks as follows:

df <- data.frame(A = NA, B = NA, C = c("a,b,c", "c,b", "d,a"), stringsAsFactors = FALSE)
df
   A  B     C
1 NA NA a,b,c
2 NA NA   c,b
3 NA NA   d,a

Columns A and B (and some more in my real data) are set to NA to indicate that their entries are not necessary for the question.

The goal is it should look like:

df_goal <- data.frame(A = NA, B = NA, a = c(TRUE, FALSE, TRUE), b = c(TRUE, 
TRUE, FALSE), c = c(TRUE, TRUE, FALSE), d = c(FALSE, FALSE, TRUE))
df_goal

   A  B     a     b     c     d
1 NA NA  TRUE  TRUE  TRUE FALSE
2 NA NA FALSE  TRUE  TRUE FALSE
3 NA NA  TRUE FALSE FALSE  TRUE

I achieved this doing:

df <- cbind(df[, 1:2], as.data.frame(t(apply(read.table(text = df$C, sep = ",", as.is = TRUE, fill = TRUE, na.strings = "")
                                             , 1, 
                                             FUN = function(x) sort(x, decreasing= FALSE, na.last = TRUE))), stringsAsFactors = FALSE))
df <- cbind(df[, 1:2], as.data.frame(sapply(c("a", "b", "c", "d"), function(y) {sapply(1:nrow(df), function(x) {ifelse(y %in% df[x, ], TRUE, FALSE)})})))
df
   A  B     a     b     c     d
1 NA NA  TRUE  TRUE  TRUE FALSE
2 NA NA FALSE  TRUE  TRUE FALSE
3 NA NA  TRUE FALSE FALSE  TRUE

identical(df, df_goal)
# [1] TRUE

Are there more concise options to achieve what I want?

Edit after @Sonny's comment:

I also thought about a tidyr option, but could not get there:

library(tidyr)
df %>% separate(C, c("a", "b", "c", "d"))
   A  B a b    c    d
1 NA NA a b    c <NA>
2 NA NA c b <NA> <NA>
3 NA NA d a <NA> <NA>

This is still unsorted and so spread doesn't really work..

What am I missing?

You can use `tidyr::separate` to split C into multiple fields and then use `spread` — Sonny, Mar 06 '19 at 13:57
@Sonny thx for your hint. I also thought that `tidyr` should provide an easy solution but could not get there, see my edit. What am I missing? — symbolrush, Mar 06 '19 at 14:13

IceCreamToucan · Accepted Answer · 2019-03-06T14:21:05.060

library(tidyverse)

df %>% 
  mutate(C = map(C, ~strsplit(., ',')[[1]] %>% sort),
         I = row_number()) %>% 
  unnest(C) %>%
  spread(C, C) %>% 
  mutate_at(-(1:3), ~!is.na(.)) %>% 
  select(-I)
#    A  B     a     b     c     d
# 1 NA NA  TRUE  TRUE  TRUE FALSE
# 2 NA NA FALSE  TRUE  TRUE FALSE
# 3 NA NA  TRUE FALSE FALSE  TRUE

Or with data.table (+purrr)

library(data.table)
library(purrr)

setDT(df)
# Split strings and sort them 
df[, C := map(C, ~ strsplit(., ',')[[1]] %>% sort)][]
# add row number column
df[, I := .I]
# unlist C and dcast (spread) to wide
df[, .(C = unlist(C)), setdiff(names(df), 'C')] %>% 
  dcast(A + B  + I ~ C) %>% 
  # convert to logical
  .[, lapply(.SD, Negate(is.na)), .(A, B)] %>% 
  # remove row number column
  .[, -'I'] 

#     A  B     a     b     c     d
# 1: NA NA  TRUE  TRUE  TRUE FALSE
# 2: NA NA FALSE  TRUE  TRUE FALSE
# 3: NA NA  TRUE FALSE FALSE  TRUE

Spread and sort concatenated factor variable in data.frame

1 Answers1