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

user_12708740 0
Viewed 214

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.

data-wrangling dataframe r
3 Answers

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
user_64614620

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
user_37322710

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
user_33582720
Related