Get common elements (from the same row) between to columns of comma separated strings, preserving rownames

user_12467726 0
Viewed 74

I have a dataframe with some column containing comma separated strings:

     colA     colB

1    a,b,c,ñ  d,b,e
2    f,g,h    f,g,m,p
3    i,j,k    f,o,j

I would like to get common elements between both columns corresponding to the same row. So my desired output is:

    colA    colB
1     b       b
2    f,g     f,g
3     j       j

I was trying to transform this columns to lists of lists to perform an intersection after that, but I am having some problems, so I would like to know if there is something easier. How can I get this?

r
3 Answers

we can use this

 df[,1:2] <- apply(df,1, function(X) paste(unlist(strsplit(X[1],","))[unlist(strsplit(X[1],",")) %in% unlist(strsplit(X[2],","))],collapse=",") )


   > df
  colA colB
1    b    b
2  f,g  f,g
3    j    j

Data:

df <- structure(list(colA = structure(1:3, .Label = c("a,b,c,ñ", "f,g,h", 
"i,j,k"), class = "factor"), colB = structure(1:3, .Label = c("d,b,e", 
"f,g,m,p", "f,o,j"), class = "factor")), class = "data.frame", row.names = c(NA, 
-3L))
user_104424230

If you're dealing with a larger dataset, you can try cSplit_l from "splitstackshape":

library(splitstackshape)
temp <- cSplit_l(df, names(df), ",", stripWhite = TRUE, type.convert = FALSE, drop = TRUE)
temp[, 1:2] <- vapply(Map(intersect, temp[[1]], temp[[2]]), toString, character(1L))
setnames(temp, names(df))[]
##    colA colB
## 1:    b    b
## 2: f, g f, g
## 3:    j    j

It's not clear why you would want the same content in both these columns.

user_12706950

Another option is str_extract

library(stringr)
library(dplyr)
library(purrr)
df %>% 
   transmute(colA = map_chr(str_extract_all(colA, 
              str_replace_all(colB, ",", "|")), toString), 
          colB = colA)
#   colA colB
#1    b    b
#2 f, g f, g
#3    j    j
user_37322710
Related