Compare one col with another col in another df containing multiple entries

user_10342689 0
Viewed 29

We are trying to find if the char in A$symbol matches any of the char in B$symbol. Result should be 3 df+ one with matches, one with only in A, one with only in B.

Data example:

### task dfs

A = data.frame(
  symbol_A = c("ABC", "ABB", "ACC", "BCG", "AAG"),
  id_A = c("1", "2", "3", "4", "5"))

B = data.frame(
  symbol_B = c("XXC", "XCT ABB", "TTG WHO ACC", "AAG", "TTR, YHD"),
  id_B = c("ab", "dy", "hu", "uh", "yz"))


### expected solution 

solution_overlaps <- data.frame(
  symbol_A = c("ABB", "ACC", "AAG"),
  symbol_B = c("XCT ABB", "TTG WHO ACC", 'AAG'),
  id_x = c("2", "3", "5"),
  id_y = c('dy', 'hu', "uh" )
)

solution_only_in_A <- data.frame(
  symbol_A = c("ABC", "BCG"),
  id_A = c('ab', '4')
)

solution_only_in_B <- data.frame(
  symbol_B = c("XXC", "TTR, YHD"),
  id_B = c('ab', "yz")
)

Thanks a lot for helping, hope this is relevant for others as well! (as always, dplyr appreciated!)

Sebastian

dplyr dataframe r
1 Answers

You can use the fuzzyjoin and dplyr packages for this.

overlaps <- fuzzyjoin::regex_inner_join(B, A, by = c("symbol_B" = "symbol_A"))
overlaps
#      symbol_B id_B symbol_A id_A
# 1     XCT ABB   dy      ABB    2
# 2 TTG WHO ACC   hu      ACC    3
# 3         AAG   uh      AAG    5

only_A <- dplyr::anti_join(A, overlaps, by = "id_A")
only_A
#   symbol_A id_A
# 1      ABC    1
# 2      BCG    4

only_B <- dplyr::anti_join(B, overlaps, by = "id_B")
only_B
#   symbol_B id_B
# 1      XXC   ab
# 2 TTR, YHD   yz

The latter two can be done without dplyr using:

subset(A, !id_A %in% overlaps$id_A)
subset(B, !id_B %in% overlaps$id_B)

but overlaps is really done best with fuzzyjoin.

You can get all three "at once" by doing a full-join and splitting on the id fields NA-status:

fuzzyjoin::regex_full_join(B, A, by = c("symbol_B" = "symbol_A")) %>%
  with(., split(., list(is.na(id_B), is.na(id_A)), drop = TRUE)) %>%
  setNames(., c("AB", "A_only", "B_only"))
# $AB
#      symbol_B id_B symbol_A id_A
# 1     XCT ABB   dy      ABB    2
# 2 TTG WHO ACC   hu      ACC    3
# 3         AAG   uh      AAG    5
# $A_only
#   symbol_B id_B symbol_A id_A
# 6     <NA> <NA>      ABC    1
# 7     <NA> <NA>      BCG    4
# $B_only
#   symbol_B id_B symbol_A id_A
# 4      XXC   ab     <NA> <NA>
# 5 TTR, YHD   yz     <NA> <NA>

though the latter two frames might benefit from some cleanup.

user_33582720
Related