Data.frame Merge and Selection of values which are common in 2 Data.frames

Solution 1:

Here is a way with combining the three data frames. After combining all three, we find the values that appear more than once. With that index we can aggregate the data frame with the function max:

d <- do.call(rbind, list(ON1, ON2, ON3))
d1 <- do.call(paste, d[1:3])
tbl <- table(d1) > 1L
indx <- d1 %in% names(tbl[tbl])
aggregate(Tag.Count ~., d[indx,], FUN=max)
#   Entrez.ID Nearest.Refseq Gene.Name Tag.Count
# 1     11303      NM_013454     Abca1    118.09
# 2     11305      NM_007379     Abca2    103.45
# 3     11306      NM_009592     Abcb7     95.32
# 4     11308      NM_007380      Abi1    410.73
# 5     11350      NM_009594      Abl1    150.37

Solution 2:

You can do this in dplyr by binding the three together, filtering out groups with one element, and then picking the top Tag.Count in each group.

library(dplyr)

F <- bind_rows(ON1, ON2, ON3) %>%
  group_by(Entrez.ID) %>%    # elements are in same group if same Entrez.ID
  filter(n() > 1) %>%        # filter out groups with 1 element
  top_n(1, Tag.Count)        # pick highest Tag.Count from each