match data frames based on multiple columns in R

Question

I have two huge datasets that look like this.

there is one fruit from df2, PEACH, which is missing for any reason from df1. I want to add in df1 the fruits that are missing.

library(tidyverse)

df1 <- tibble(central_fruit=c("ananas","apple"),
              fruits=c("ananas,anan,anannas",("apple,appl,appless")),
              counts=c("100,10,1","50,20,2"))
df1
#> # A tibble: 2 × 3
#>   central_fruit fruits              counts  
#>   <chr>         <chr>               <chr>   
#> 1 ananas        ananas,anan,anannas 100,10,1
#> 2 apple         apple,appl,appless  50,20,2

df2 <- tibble(fruit=c("ananas","anan","anannas","apple","appl","appless","PEACH"),
              counts=c(100,10,1,50,20,2,1000))
df2
#> # A tibble: 7 × 2
#>   fruit   counts
#>   <chr>    <dbl>
#> 1 ananas     100
#> 2 anan        10
#> 3 anannas      1
#> 4 apple       50
#> 5 appl        20
#> 6 appless      2
#> 7 PEACH     1000

^{Created on 2022-03-20 by the reprex package (v2.0.1)}

I want my data to look like this

df1 
   central_fruit fruits              counts  
   <chr>         <chr>               <chr>   
 1 ananas        ananas,anan,anannas 100,10,1
 2 apple         apple,appl,appless  50,20,2
 3 PEACH            NA               1000

any help or advice are highly appreciated

Answer 1

Please find below one possible data.table approach.

Reprex

• Code

library(tidyverse) # to read your tibbles
library(data.table)

setDT(df1)
setDT(df2)

df1[df2, on = .(central_fruit = fruit)
    ][, `:=` (counts = fcoalesce(counts, as.character(i.counts)), i.counts = NULL)
      ][central_fruit %chin% c(df1$central_fruit, setdiff(df2$fruit, unlist(strsplit(df1$fruit, ","))))][]

• Output

#>    central_fruit              fruits   counts
#> 1:        ananas ananas,anan,anannas 100,10,1
#> 2:         apple  apple,appl,appless  50,20,2
#> 3:         PEACH                <NA>     1000

^{Created on 2022-03-20 by the reprex package (v2.0.1)}