Vfrdtyky

So, I have data table in R which looks like follows:

Claim failure Part code matchcode

23     F1     P1    A   B,A,C

23     F1     P1    D   B,A,C

23     F2     P2    D   B,A,C

23     F2     P2    E   B,A,C

45     F1     P4    X   Y,Z,X

45     F1     P4    Y   Y,Z,X

45     F1     P4    A   Y,Z,X

45     F1     P1    F   Y,Z,X

45     F1     P1    H   Y,Z,X

So the idea here is for every group (claim, failure and part) if 'any' of the code in 'code' matches with list of the codes in 'matchcode' column then than group should have Match column as True.

So expected output should be as following:

Claim failure Part Match 

23     F1     P1    TRUE   

23     F2     P2    FALSE  

45     F1     P4    TRUE

45     F1     P1    FALSE

The size of this data.table is huge so will be needing a optimized solution.
please help :)

... and a solution using data.table.

library(data.table)

grepl_v <- Vectorize(grepl)

DT[, .(Match = any(grepl_v(code, matchcode))), by = .(Claim, failure, Part)]

#   Claim failure Part Match

#1:    23      F1   P1  TRUE

#2:    23      F2   P2 FALSE

#3:    45      F1   P4  TRUE

#4:    45      F1   P1 FALSE

data

DT <- fread("Claim failure Part code matchcode

23     F1     P1    A   B,A,C

23     F1     P1    D   B,A,C

23     F2     P2    D   B,A,C

23     F2     P2    E   B,A,C

45     F1     P4    X   Y,Z,X

45     F1     P4    Y   Y,Z,X

45     F1     P4    A   Y,Z,X

45     F1     P1    F   Y,Z,X

45     F1     P1    H   Y,Z,X")

Here is a dplyr solution.

library(dplyr)



dat %>%

  rowwise() %>%

  mutate(Match = grepl(code, matchcode)) %>%

  group_by(Claim, failure, Part) %>%

  mutate(Match = any(Match)) %>%

  select(-code, -matchcode) %>%

  unique

## A tibble: 4 x 4

## Groups:   Claim, failure, Part [4]

#  Claim failure Part  Match

#  <int> <fct>   <fct> <lgl>

#1    23 F1      P1    TRUE 

#2    23 F2      P2    FALSE

#3    45 F1      P4    TRUE 

#4    45 F1      P1    FALSE

#Warning message:

#Grouping rowwise data frame strips rowwise nature 

Don't worry about the warning, it simply tells you that after grep'ing rowwise, the pipe groups by certain variables and therefore the processing is no longer row by row.

Data.

dat <- read.table(text = "

Claim failure Part code matchcode

23     F1     P1    A   B,A,C

23     F1     P1    D   B,A,C

23     F2     P2    D   B,A,C

23     F2     P2    E   B,A,C

45     F1     P4    X   Y,Z,X

45     F1     P4    Y   Y,Z,X

45     F1     P4    A   Y,Z,X

45     F1     P1    F   Y,Z,X

45     F1     P1    H   Y,Z,X                  

", header = TRUE)

base solution:

d$match <- apply(d, 1, function(x) { return(grepl(x[['code']], x['matchcode']))})



# Claim   failure Part code matchcode match

# 1    23      F1   P1    A     B,A,C     1

# 2    23      F1   P1    D     B,A,C     0

# 3    23      F2   P2    D     B,A,C     0

# 4    23      F2   P2    E     B,A,C     0

# 5    45      F1   P4    X     Y,Z,X     1

# 6    45      F1   P4    Y     Y,Z,X     1

# 7    45      F1   P4    A     Y,Z,X     0

# 8    45      F1   P1    F     Y,Z,X     0

# 9    45      F1   P1    H     Y,Z,X     0

Original version of this answer used grep(); thanks to markus for suggesting grepl()