Check if values of one dataframe exist in another dataframe in exact order

Question

I have 1 dataframe of data and multiple "reference" dataframes. I'm trying to automate checking if values of the dataframe match the values of the reference dataframes. Importantly, the values must also be in the same order as the values in the reference dataframes. These columns are of the columns of importance, but my real dataset contains many more columns.

Below is a toy dataset.

Dataframe

group   type    value
1       A       Teddy
1       A       William
1       A       Lars
2       B       Dolores
2       B       Elsie
2       C       Maeve
2       C       Charlotte
2       C       Bernard


Reference_A

type    value
A       Teddy
A       William
A       Lars

Reference_B

type    value
B       Elsie
B       Dolores

Reference_C

type    value
C       Maeve
C       Hale
C       Bernard

For example, in the toy dataset, group1 would score 1.0 (100% correct) because all its values in A match the values and order of values of An in reference_A. However, group2 would score 0.0 because the values in B are out of order compared to reference_B and 0.66 because 2/3 values in C match the values and order of values in reference_C.

Desired output

group   type    score
1       A       1.0
2       B       0.0
2       C       0.66

This was helpful, but does not take order into account: Check whether values in one data frame column exist in a second data frame

Update: Thank you to everyone that has provided solutions! These solutions are great for the toy dataset, but have not yet been adaptable to datasets with more columns. Again, like I wrote in my post, the columns that I've listed above are of importance — I'd prefer to not drop the unneeded columns if necessary.

What if the number of rows is different? – r2evans Jul 24 '20 at 17:57 — r2evans, Jul 24 '20 at 17:57

score 2 · Answer 1 · answered Jul 24 '20 at 19:00

This is another tidyverse solution. Here, I am adding a counter (i.e. rowname) to both reference and data. Then I join them together on type and rowname. At the end, I summarize them on type to get the desired output.

library(dplyr)
library(purrr)
library(tibble)

list(`Reference A`, `Reference B`, `Reference C`) %>% 
  map(., rownames_to_column) %>% 
  bind_rows %>% 
 left_join({Dataframe %>%
             group_split(type) %>% 
             map(., rownames_to_column) %>% 
             bind_rows}, 
             . , by=c("type", "rowname")) %>% 
  group_by(type) %>% 
  dplyr::summarise(group = head(group,1),
            score = sum(value.x == value.y)/n())

#> # A tibble: 3 x 3
#>   type  group score
#>   <chr> <int> <dbl>
#> 1 A         1 1    
#> 2 B         2 0    
#> 3 C         2 0.667

score 2 · Accepted Answer · answered Jul 24 '20 at 21:14

2

We may also do this with mget to return a list of data.frames, bind them together, and do a group by mean of logical vector

library(dplyr)
mget(ls(pattern = '^Reference_[A-Z]$')) %>%
    bind_rows() %>% 
    bind_cols(df1) %>% 
    group_by(group, type = type...1) %>% 
    summarise(score = mean(value...2 == value...5))
# Groups:   group [2]
#  group type  score
#  <int> <chr> <dbl>
#1     1 A     1    
#2     2 B     0    
#3     2 C     0.667

answered Jul 24 '20 at 21:14

akrun

874,273
37
540
662

thank you! What does `value...2` and `value...5` mean? Specifically, why choose the numbers 2 and 5? Same for `type...`, please – psychcoder Jul 28 '20 at 00:53

score 1 · Answer 3 · answered Jul 24 '20 at 18:19

1

Here's a "tidy" method:

library(dplyr)
# library(purrr) # map2_dbl
Reference <- bind_rows(Reference_A, Reference_B, Reference_C) %>%
  nest_by(type, .key = "ref") %>%
  ungroup()
Reference
# # A tibble: 3 x 2
#   type                 ref
#   <chr> <list<tbl_df[,1]>>
# 1 A                [3 x 1]
# 2 B                [2 x 1]
# 3 C                [3 x 1]

Dataframe %>%
  nest_by(group, type, .key = "data") %>%
  left_join(Reference, by = "type") %>%
  mutate(
    score = purrr::map2_dbl(data, ref, ~ {
      if (length(.x) == 0 || length(.y) == 0) return(numeric(0))
      if (length(.x) != length(.y)) return(0)
      sum((is.na(.x) & is.na(.y)) | .x == .y) / length(.x)
    })
  ) %>%
  select(-data, -ref) %>%
  ungroup()
# # A tibble: 3 x 3
#   group type  score
#   <int> <chr> <dbl>
# 1     1 A     1    
# 2     2 B     0    
# 3     2 C     0.667

answered Jul 24 '20 at 18:19

r2evans

141,215
6
77
149

thanks so much! This is reproducible on my toy dataset, but not on my larger, real dataset which has many more columns. Do you have advice on how I can adapt your code? The columns of importance are similar to my toy dataset but I can ignore the other columns. It would be nice if I didn't need to drop any columns from the actual dataset and reference datasets. – psychcoder Jul 28 '20 at 01:18
It depends on the data. Have you tried this on your larger data? Did something join incorrectly or produce warnings/errors? It's hard to advise without representative data. – r2evans Jul 28 '20 at 01:54
I know :( I apologize that I can't pass along more representative data. Here's my error message for the larger dataset: ``Error: Can't re-group while nesting ℹ Either `ungroup()` first or don't supply arguments to `nest_by()``. – psychcoder Jul 28 '20 at 01:56

Check if values of one dataframe exist in another dataframe in exact order

3 Answers3

Linked