This is a test version of Biostars. For the public version, visit https://www.biostars.org.
Remove rows with duplicate values based on two columns

Hi, I have output from snp-dists (https://github.com/tseemann/snp-dists) in molten format, e.g.:

seq1    seq2    1
seq1    seq3    2
seq2    seq1    1
seq2    seq3    3
seq3    seq1    2
seq3    seq2    3

The third column gives the number of SNPs between the pair of sequences given in columns 1 and 2. As you can see, these values are duplicated, as it shows both the combination seq1 seq2 and seq2 seq1. How can I (in R or bash preferably) remove the duplicate values?

r bash snp-dists snp
• 1,925 views • link
updated by zx8754 zx8754 • written by marit.hetland • marit.hetland

3 answers

Using awk:

$ awk '!(seen[$1,$2]++ || seen[$2,$1]++)' test.txt
seq1 seq2 1
seq1 seq3 2
seq2 seq3 3

Using R:

# example data
x <- read.table(text = "seq1    seq2    1
seq1    seq3    2
seq2    seq1    1
seq2    seq3    3
seq3    seq1    2
seq3    seq2    3")

# sort column values, then get unique
unique(data.frame(c1 = pmin(x$V1, x$V2), c2 = pmax(x$V1, x$V2), value = x$V3))
#     c1   c2 value
# 1 seq1 seq2     1
# 2 seq1 seq3     2
# 4 seq2 seq3     3

Using R again, a bit simpler and scales better when we have more than 2 columns, (Related StackOverflow post):

x[ !duplicated(apply(x[, 1:2], 1, sort), MARGIN = 2), ]
• 0 views • link
written by zx8754 zx8754

even better and I learnt about pmin!

• 0 views • link
written by Friederike Friederike

So you saying you knew about pmax and didn't know pmin :)

• 0 views • link
written by zx8754 zx8754

nope. just saving key strokes :)

• 0 views • link
written by Friederike Friederike

This is great! Thank you so much, and it's very nice to have both R and awk solutions :)

• 0 views • link
written by marit.hetland • marit.hetland

Let's do code golf with benchmarks, here is my Python version if we are at it:

import sys
seen = set()
for line in sys.stdin:
    a,b,c = line.split()
    if (a,b) not in seen:
       seen.add((a,b))
       seen.add((b,a))
       print (line, end='')

Benchmark: a file with 1 million entries (file size 1.7MB)

  1. Python code above took 0.1 seconds and 18MB RAM.

  2. The awk version took 0.3 seconds and used about 14 MB RAM

  3. First version of the R code took 0.5 seconds and used about 400MB of RAM.

  4. Simpler R code took 3 seconds and used about 400MB of RAM.

• 0 views • link
written by Istvan Albert Istvan Albert

in R, one way could be this:

>library(magrittr)
>df <- data.frame(one = sort(paste0("seq", rep(1:3,2))),
                 two = c(paste0("seq", c(2,3,1,2,1,2))),
                 SNP = c(1,2,1,3,2,3))
# add a column with a composite label that will always have the smaller seq first
>df$three <- apply(df, 1, function(x) paste(sort(c(x[["one"]], x[["two"]])), collapse=","))
# determine duplicates based on the composite label and the SNP entry
>df$duplicate <- df[, c("three","SNP")] %>% duplicated
# subset to include only those rows where $duplicate is FALSE (= opposite of TRUE)
>df[!df$duplicate,]
    one  two SNP     three duplicate
1 seq1 seq2   1 seq1,seq2     FALSE
2 seq1 seq3   2 seq1,seq3     FALSE
4 seq2 seq2   3 seq2,seq2     FALSE
6 seq3 seq2   3 seq2,seq3     FALSE
• 0 views • link
written by Friederike Friederike

a python solution

file = map(lambda x:x.strip().split("\t"),open("yourTabDelimitedColumnFile.txt").readlines())
result = {}
for seq1,seq2,snp in file:
  if seq1+seq2 in result or (seq2+seq1 in result and result[seq2+seq1][-1] == snp):
    continue 
  result[seq1+seq2] = [seq1,seq2,snp]
with open("result.txt","w") as file :
  for line in result.values():
    file.write("\t".join(line)+"\n")
• 0 views • link
written by mohammadhassanj mohammadhassanj

Log in to answer this question.

More posts like this