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

score 2 · Answer 1 · answered Mar 20 '22 at 10:59

You may get the dataset in a long format by splitting on comma fruits and counts variable, do a full_join with df2, adjust the NA values and for each central_fruit collapse the values.

library(dplyr)
library(tidyr)

df1 %>%
  separate_rows(fruits, counts, convert = TRUE) %>%
  full_join(df2, by = c('fruits' = 'fruit')) %>%
  transmute(central_fruit = ifelse(is.na(central_fruit), fruits, central_fruit), 
            fruits = ifelse(is.na(counts.x), NA, fruits), 
            counts = coalesce(counts.x, counts.y)) %>%
  group_by(central_fruit) %>%
  summarise(across(.fns = toString))

# 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

score 2 · Accepted Answer · answered Mar 20 '22 at 12:58

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)}

score 1 · Answer 3 · answered Mar 20 '22 at 10:59

You can just take the set of fruits present in your df1 and use them to filter df2, then bind them together.

library(tidyverse)

present <- df1$fruits |> 
  str_split(",") |> 
  unlist()

df2 |> 
  rename(central_fruit = fruit) |> 
  filter(! central_fruit %in% present) |> 
  mutate(counts = as.character(counts)) |> 
  bind_rows(df1)
#> # A tibble: 3 × 3
#>   central_fruit counts   fruits             
#>   <chr>         <chr>    <chr>              
#> 1 PEACH         1000     <NA>               
#> 2 ananas        100,10,1 ananas,anan,anannas
#> 3 apple         50,20,2  apple,appl,appless

Thank you @shs for the help! I highly appreciate – LDT Mar 20 '22 at 11:02 — LDT, Mar 20 '22 at 11:02

match data frames based on multiple columns in R

3 Answers3