[FIXED] How to match two data frames based on substrings within groups

Issue

I want to merge two dataframes which are grouped by the same identifier. The variable in the first dataframe (valueA) should match with a substring of a variable in the second data frame (valueB), but only within groups.

I could manage to match the matching variables, but I'm struggling to limit the matching to the grouping variable. Here are the sample data frames and the matching code:

df1 <- data.frame(report = c('Report1','Report1','Report1','Report1','Report1','Report1'),
            identifier = c('Abraham', 'Abraham', 'Abraham','Barack', 'Barack','Barack'),
            variableA = c('V1','V2','V3','V1','V2', 'V3'),
            value = c('CDKN2A/B','PALB2','KRAS','TP53','RB1','KRAS'))

df2 <- data.frame(report = c('Report1','Report1','Report1','Report1','Report1','Report1','Report1'),
            identifier = c('Abraham', 'Abraham', 'Abraham','Abraham','Barack', 'Barack','Barack'),
            variableB = c('F1','F2','F3','F4','F1','F2', 'F3'),
            valueB = c('CDKN2A/B LOSS','PALB2 P1111FS*13','KRAS G12R','PALB2 N540FS*1','RB1 SPLICE SITE 2325+1G>A','KRAS G13C','TP53 C238F'))

Here is the code I've tried, but which is not working for groups

idx2 <- sapply(df1$value, grep, df2$valueB)
idx1 <- sapply(seq_along(idx2), function(i) rep(i, length(idx2[[i]])))
idx3 <- cbind(df1[unlist(idx1),,drop=F], df2[unlist(idx2),,drop=F])

Expected output is (code for data frame)

df3 <- data.frame(report=c('Report1','Report1','Report1','Report1','Report1','Report1','Report1'),
                  identifier = c('Abraham', 'Abraham', 'Abraham','Abraham','Barack', 'Barack','Barack'),
                  variableA = c('V1','V2','V3','V2','V1','V2', 'V3'),
                  value = c('CDKN2A/B','PALB2','KRAS','PALB2','TP53','RB1','KRAS'),
                  variableB = c('F1','F2','F3','F4','F1','F2', 'F3'),
                  valueB = c('CDKN2A/B LOSS','PALB2 P1111FS*13','KRAS G12R','PALB2 N540FS*1','TP53 C238F','RB1 SPLICE SITE 2325+1G>A','KRAS G13C'))

resulting dataframe

report  identifier  variableA   value   variableB   valueB
Report1 Abraham     V1      CDKN2A/B    F1  CDKN2A/B LOSS
Report1 Abraham     V2      PALB2   F2  PALB2   P1111FS*13
Report1 Abraham     V3      KRAS    F3  KRAS    G12R
Report1 Abraham     V2      PALB2   F4  PALB2   N540FS*1
Report1 Barack      V1      TP53    F1  TP53    C238F
Report1 Barack      V2      RB1 F2  RB1 SPLICE SITE 2325+1G>A
Report1 Barack      V3      KRAS    F3  KRAS    G13C

Hope this makes sense, many thanks for your help!

Solution

You can use the fuzzyjoin-package for this:

fuzzy_inner_join(df2, df1, by = c("valueB" = "valueA", "identifier" = "identifier"), match_fun = list(str_detect, `==`)) %>%
  select(report.x, identifier.x, variableA, valueA, variableB, valueB)

  report.x identifier.x variableA   valueA variableB                    valueB
1  Report1      Abraham        V1 CDKN2A/B        F1             CDKN2A/B LOSS
2  Report1      Abraham        V2    PALB2        F2          PALB2 P1111FS*13
3  Report1      Abraham        V3     KRAS        F3                 KRAS G12R
4  Report1      Abraham        V2    PALB2        F4            PALB2 N540FS*1
5  Report1       Barack        V2      RB1        F1 RB1 SPLICE SITE 2325+1G>A
6  Report1       Barack        V3     KRAS        F2                 KRAS G13C
7  Report1       Barack        V1     TP53        F3                TP53 C238F

This way you can apply different matching functions for different columns. In this case we used str_detect() for your fuzzy matching column and == for your grouping column.

Answered By - Humpelstielzchen

Answer Checked By - Katrina (PHPFixing Volunteer)