How do I select non-unique combination of columns?

Admin 1
Viewed 37

My data looks like this:

counts <- data.frame(
  pos = c(101, 101, 101, 102, 102, 102, 103, 103, 103, 101, 101, 101),
  chr = c(1, 1, 1, 2, 2, 2, 3, 3, 3, 4, 4, 4),
  subj = c("A", "B", "C", "A", "B", "C", "A", "B", "C", "A", "B", "C")
)

pos is supposed to belong to only one unique chr, but here pos 101 belongs to both chr 1 and 4.

I can detect this case like:

counts %>% select(pos, chr) %>%
  group_by(pos) %>%
  summarise(n_chrs = length(unique(chr))) %>%
  filter(n_chrs > 1)

This returns pos which has more than to chr values:

 A tibble: 1 x 2
    pos n_chrs
  <dbl>  <int>
1   101      2

What I'd like is to know which chr values are implicated, something like:

  pos chr
1 101   1
2 101   4

Thanks!

dplyr r
3 Answers

You could do:

library(dplyr)

counts %>%
  group_by(pos) %>%
  distinct(chr) %>%
  filter(n() > 1)

Output:

# A tibble: 2 x 2
# Groups:   pos [1]
    pos   chr
  <dbl> <dbl>
1   101     1
2   101     4
user_83890030

An option using data.table

library(data.table) 
unique(setDT(counts), by = 'chr')[, .(chr = chr[.N > 1]), pos]
#   pos chr
#1: 101   1
#2: 101   4
user_37322710

Instead of summarize, you could just use mutate to create the group-wise count. This will make sure you keep chr, which you're interested in:

counts %>% select(pos, chr) %>%
  group_by(pos) %>%
  mutate(n_chrs = length(unique(chr))) %>%
  filter(n_chrs > 1) %>%
  unique()

Result:

# A tibble: 2 x 3
# Groups:   pos [1]
    pos   chr n_chrs
  <dbl> <dbl>  <int>
1   101     1      2
2   101     4      2
user_104836920
Related