10

I have a data frame with a categorical variable holding lists of strings, with variable length (it is important because otherwise this question would be a duplicate of this or this), e.g.:

df <- data.frame(x = 1:5)
df$y <- list("A", c("A", "B"), "C", c("B", "D", "C"), "E")
df
  x       y
1 1       A
2 2    A, B
3 3       C
4 4 B, D, C
5 5       E

And the desired form is a dummy variable for each unique string seen anywhere in df$y, i.e.:

data.frame(x = 1:5, A = c(1,1,0,0,0), B = c(0,1,0,1,0), C = c(0,0,1,1,0), D = c(0,0,0,1,0), E = c(0,0,0,0,1))
  x A B C D E
1 1 1 0 0 0 0
2 2 1 1 0 0 0
3 3 0 0 1 0 0
4 4 0 1 1 1 0
5 5 0 0 0 0 1

This naive approach works:

> uniqueStrings <- unique(unlist(df$y))
> n <- ncol(df)
> for (i in 1:length(uniqueStrings)) {
+   df[,  n + i] <- sapply(df$y, function(x) ifelse(uniqueStrings[i] %in% x, 1, 0))
+   colnames(df)[n + i] <- uniqueStrings[i]
+ }

However it is very ugly, lazy and slow with big data frames.

Any suggestions? Something fancy from the tidyverse?


UPDATE: I got 3 different approaches below. I tested them using system.time on my (Windows 7, 32GB RAM) laptop on a real dataset, comprising of 1M rows, each row containing a list of length 1 to 4 strings (out of ~350 unique string values), overall 200MB on disk. So the expected result is a data frame with dimensions 1M x 350. The tidyverse (@Sotos) and base (@joel.wilson) approaches took so long I had to restart R. The qdapTools (@akrun) approach however worked fantastic:

> system.time(res1 <- mtabulate(varsLists))
   user  system elapsed 
  47.05   10.27  116.82

So this is the approach I'll mark accepted.

Community
  • 1
  • 1
Giora Simchoni
  • 3,487
  • 3
  • 34
  • 72

3 Answers3

7

Another idea,

library(dplyr)
library(tidyr)

df %>% 
 unnest(y) %>% 
 mutate(new = 1) %>% 
 spread(y, new, fill = 0) 

#  x A B C D E
#1 1 1 0 0 0 0
#2 2 1 1 0 0 0
#3 3 0 0 1 0 0
#4 4 0 1 1 1 0
#5 5 0 0 0 0 1

Further to the cases you mentioned in comments, we can use dcast from reshape2 as it is more flexible than spread,

df2 <- df %>% 
        unnest(y) %>% 
        group_by(x) %>% 
        filter(!duplicated(y)) %>% 
        ungroup()

reshape2::dcast(df2, x ~ y, value.var = 'y', length)

#  x A B C D E
#1 1 1 0 0 0 0
#2 2 1 1 0 0 0
#3 3 0 0 1 0 0
#4 4 0 1 1 1 0
#5 5 0 0 0 0 1

#or with df$x <- c(1, 1, 2, 2, 3)

#  x A B C D E
#1 1 1 1 0 0 0
#2 2 0 1 1 1 0
#3 3 0 0 0 0 1

#or with df$x <- rep(1,5)

#  x A B C D E
#1 1 1 1 1 1 1
Sotos
  • 51,121
  • 6
  • 32
  • 66
6

We can use mtabulate

library(qdapTools)
cbind(df[1], mtabulate(df$y))
#  x A B C D E
#1 1 1 0 0 0 0
#2 2 1 1 0 0 0
#3 3 0 0 1 0 0
#4 4 0 1 1 1 0
#5 5 0 0 0 0 1
akrun
  • 874,273
  • 37
  • 540
  • 662
  • That's impressive and super fast (a few seconds for a ~1M rows with ~350 unique values on my PC). Do you have an answer not requiring a whole new package? Thanks. – Giora Simchoni Jan 16 '17 at 09:19
  • @GioraSimchoni Looks like somebody else answered it without a package – akrun Jan 16 '17 at 11:00
  • 2
    @GioraSimchoni too; I guess a base alternative is `table(rep(df$x, lengths(df$y)), unlist(df$y))`? – alexis_laz Jan 16 '17 at 12:44
  • Doesn't work with `df$x = rep(1,5)` or `df$x = c(1,1,2,2,3)`. It shouldn't matter what `df$x` is. – Giora Simchoni Jan 17 '17 at 06:42
  • @GioraSimchoni I am not sure what you meant by doesn't work? It does give an output where the first column is just 1 (for `df$x = rep(1,5)`) – akrun Jan 17 '17 at 06:45
  • 1
    Sorry @akrun, I was referring to alexis_laz comment. – Giora Simchoni Jan 17 '17 at 07:23
  • @GioraSimchoni : (hadn't notice the comment) I misunderstood what you wanted -- in that case, simply use `table(rep(seq_along(df$y), lengths(df$y)), unlist(df$y))` and `cbind` `df$x` as in akrun's answer. (@akrun sorry for the unnecessary notification) – alexis_laz Jan 22 '17 at 13:19
2

this involves no external packages,

# thanks to Sotos for suggesting to use `unique(unlist(df$y))` instead of `LETTERS[1!:5]`
sapply(unique(unlist(df$y)), function(j) as.numeric(grepl(j, df$y)))
#     A B C D E
#[1,] 1 0 0 0 0
#[2,] 1 1 0 0 0
#[3,] 0 0 1 0 0
#[4,] 0 1 1 1 0
#[5,] 0 0 0 0 1
joel.wilson
  • 8,243
  • 5
  • 28
  • 48