How can I expand regexp matches from a list into binary fields without a for loop in R?

Question

I have a dataframe that contains fields with strings, such as "fish, birds, animals", etc. I have collapsed them into a list, and iterate over them in order to create logical fields within same dataframe. Update: The question is now updated with a more elaborate example.

However, this is slow and does not feel optimal. It's not an operation I have to do more than once, so I wasn't that bothered, but think there might be a better way, using dplyr, perhaps.

This code does create new fields for every match of each element in my_list within the field items.

no <- seq(1:3)
items <- c('fish,cat,dog', 'horse,elephant,dog', 'hamster,pig')

df <- data.frame(no, items)
df$items <- as.character(df$items)

df

Will create the following data frame:

  no              items
1  1       fish,cat,dog
2  2 horse,elephant,dog
3  3        hamster,pig

Running this code will harvest the field items and expand it into logical fields

items <- paste(df$items, collapse = ",")
item_list <- unlist(unique(strsplit(items, ",")))

for (i in 1:length(item_list)) {
    lt <- item_list[i]
    df <- df %>% rowwise() %>% mutate(!!lt := grepl(lt, items))
}

data.frame(df)

Resulting in this data frame:

  no              items  fish   cat   dog horse elephant hamster   pig
1  1       fish,cat,dog  TRUE  TRUE  TRUE FALSE    FALSE   FALSE FALSE
2  2 horse,elephant,dog FALSE FALSE  TRUE  TRUE     TRUE   FALSE FALSE
3  3        hamster,pig FALSE FALSE FALSE FALSE    FALSE    TRUE  TRUE

I believe this question has been asked before. So, there should be duplicates. However, you may try `library(data.table); setDT(df)[, strsplit(items, ","), by = .(no, items)][, dcast(.SD, no + items ~ V1, function(x) length(x) > 0)]` — Uwe, Sep 07 '17 at 09:33
It reproduces the result, but I find it hard to understand what goes on. If there is a more elaborate answer in a different question, I would obviously study it. — user1603472, Sep 07 '17 at 09:40
I would like also to note that I don't want to split a column into rows, but splitting a column into more columns, so the questions are, perhaps, equivalent, but they are not duplicates. — user1603472, Sep 08 '17 at 15:06

Martin Morgan · Answer 1 · 2017-09-07T14:17:45.780

This will be fairly fast

f1 = function(df, column_name) {
    ## pre-process words
    words = strsplit(df[[column_name]], ",")
    uwords = unlist(words)
    colnames = unique(uwords)

    ## pre-allocate result matrix of 'FALSE' values
    m = matrix(FALSE, nrow(df), length(colnames), dimnames = list(NULL, colnames))

    ## update rows and columns of result matrix containing matches to TRUE
    row = rep(seq_len(nrow(df)), lengths(words))
    col = match(uwords, colnames)
    m[cbind(row, col)] = TRUE

    ## return the final result
    cbind(df, m)
}

The trickiest part is that a matrix subset by a two-column matrix treats the first column of the two-column matrix as a row index, and the second column as the column index. So the rows and columns that you want to set to TRUE are

row = rep(seq_len(nrow(df)), lengths(words))
col = match(uwords, colnames)

and the matrix is updated with

m[ cbind(row, col) ] = TRUE

There is no iteration (e.g., sapply()), so the match() function is called once rather than nrow(df) times.

For 3M rows, I have

> idx = rep(1:3, 1000000)
> df1 = df[idx,]
> system.time(f1(df1, "items"))
   user  system elapsed 
 13.304   0.112  13.421

For the other solution (at time of writing) by Christoph:

f0 = function(df, column_name) {
    categories_per_row <- strsplit(df[[column_name]], split=",")
    categories <- unique(unlist(categories_per_row))
    categoryM <- t(sapply(categories_per_row, function(y) categories %in% y))
    colnames(categoryM) <- categories
    cbind(df, categoryM)
}

and the data.table solution by Uwe (watch out, reference semantics change the value of dt! Also, I don't know how to pass the column name as a function argument):

library(data.table)
dt = df1
dt$no = seq_len(nrow(dt))
f2 = function(dt) {
    setDT(dt)
    dt1 = dt[, strsplit(items, ","), by = .(no, items)]
    dt1[, dcast(.SD, no + items ~ V1, function(x) length(x) > 0)] 
}

with times

> system.time(res0 <- f0(df1, "items"))
   user  system elapsed 
 23.776   0.000  23.786 
> system.time(res2 <- f2(dt, "items"))
Using 'V1' as value column. Use 'value.var' to override
   user  system elapsed 
 45.668   0.072  45.593

About 1/2 the time of f1() is used by strsplit(); stringr::str_split() is about two times faster, but since the pattern used to split is fixed (not a regular expression) it makes sense to use strsplit(fixed=TRUE), which is about 3x faster. Probably some data.table pro will come up with a very fast solution (but then you need to be a data.table pro...).

It's tempting to do things like 'collapse them [words shared by an item] into a list [actually a vector!]', but it will often be sensible to leave words in a list

> df1$items = strsplit(df1$items, ",", fixed=TRUE)
> head(df1)
  no                items
1  1       fish, cat, dog
2  2 horse, elephant, dog
3  3         hamster, pig
4  4       fish, cat, dog
5  5 horse, elephant, dog
6  6         hamster, pig

and save yourself the time / trouble required to re-split. The tidyverse way would be to create an extended version of the table

tidyr::unnest(df1)

(or the other approaches in the so-called 'duplicate' question). This would probably cause one to re-think what the role of the columns of logicals is in subsequent manipulations.

score 0 · Answer 2 · answered Sep 07 '17 at 10:17

Here's a step by step solution; Uwe's is probably much faster but I hope this is easier to understand:

categories_per_row <- strsplit(df$items, split=",")
categories <- unique(unlist(strsplit(df$items, ",")))
categoryM <- t(sapply(categories_per_row, function(y) categories %in% y))
colnames(categoryM) <- categories
cbind(df, categoryM)

How can I expand regexp matches from a list into binary fields without a for loop in R?

2 Answers2