How to subtract two comma separated columns in R?

Question

I have a small problem that I can't seem to solve. Given two columns:

dt <- data.table(ColumnA = c("A,B,C,A,A,A", "A,B,C"), ColumnB = c("A,C,A", "C"))

I would like to "subtract" columnB from columnA, which will result in:

data.table(Result = c("B,A,A", "A,B"))

How would one achieve this fact without first transforming it into a list and then try to subtract the list? In addition, since the dataset is quite big, it cannot be done using a for loop in R.

Every item in the comma seperated string should be treated as one item and should be subtracted only once if it occurs once. Hence not all A's are gone in the first row.

Answer 1

Another option leveraging the function vecsets::vsetdiff which doesn't remove duplicates:

library(dplyr)
library(tidyr)
library(purrr)
library(vecsets)

dt %>% 
  mutate(x = strsplit(ColumnA,","),
         y = strsplit(ColumnB,",")) %>% 
  mutate(z = map2(x,y,vecsets::vsetdiff))

      ColumnA ColumnB                x       y       z
1 A,B,C,A,A,A   A,C,A A, B, C, A, A, A A, C, A B, A, A
2       A,B,C       C          A, B, C       C    A, B

Note that you end up with list columns here (which I created on purpose for this to work), but the data might be easier to work with that way anyway.

Answer 2

sapply(1:nrow(dt), function(i){
    a = dt$ColumnA[i]
    b = unlist(strsplit(dt$ColumnB[i], ","))
    for (x in b){
        a = sub(paste0(x, ",?"), "", a)
    }
    sub(",$", "", a)
})
#[1] "B,A,A" "A,B"

Answer 3

Not sure if using string split fails this criteria:

How would one achieve this fact without first transforming it into a list and then try to subtract the list?

Will delete this post if OP decides that this violates OP's criteria.

Here is an option using data.table's anti-join. It takes about 7 seconds for 2 million rows:

library(data.table)
library(stringi) #for fast string processing

dt <- data.table(ColumnA = c("A,B,C,A,A,A", "A,B,C"), ColumnB = c("A,C,A", "C"))
DT_big <- dt[rep(seq(dt[, .N]), 1e6)]

f <- function(DT, coln) {
    res <- DT[, {
        s <- stri_split_fixed(get(coln), ',')
        .(rn=rep(seq_along(s), lengths(s)), S=unlist(s))
    }]
    res[, n := rowid(S)]
}

system.time({
    DTA <- f(DT_big, "ColumnA")
    DTB <- f(DT_big, "ColumnB")
    ans <- DTA[!DTB, on=.(rn, S, n)][, .(Result=paste(S, collapse=",")), .(rn)][, rn := NULL][]
})
ans

timing:

   user  system elapsed 
   7.56    0.33    7.20 

output:

            Result
      1:     B,A,A
      2:       A,B
      3: A,B,A,A,A
      4:       A,B
      5: A,B,A,A,A
     ---          
1999996:       A,B
1999997: A,B,A,A,A
1999998:       A,B
1999999: A,B,A,A,A
2000000:       A,B