This is a test version of Biostars. For the public version, visit https://www.biostars.org.
Remove rows with duplicate SNP identifiers in first column

I have a large database of SNPs (>53 million). Each SNP has a identifier in column 1. The remaining columns (483) contain genotype data (0,1,-1). Some SNPs are duplicated in the database. I have tried to remove the duplicates but can't seem to weed them out with common unix commands such as:

awk '{a[NR]=$0; a[NR,"k"]=$1; k[$1]++} END {for (i=1; i<=NR; i++) if (k[a[i,"k"]] > 1) print a[i]}'

or

awk '!a[$1]++'

The SNP identifiers look like this Contig0_50, so the awk commands finds non exact duplicates e.g. Contig0_500

Can someone suggested how to remove duplicate identifiers in the database?, i.e. if identifier 1 is exactly the same as identifier 2, remove the entire row which contains identifier 2. The resulting database will only have unique identifiers.

Thanks,

James

grep unix awk
• 1,948 views • link
updated by Matthias Zepper Matthias Zepper • written by bsp017 • bsp017

What file format is your database in?

• 0 views • link
written by Jeremy • Jeremy

It's a flat text file

• 0 views • link
written by bsp017 • bsp017

This is odd. In particular, the second awk command would be the textbook example of how to achieve the deduplication of a file, and I also can't reproduce this bug with the example SNP names you have given. Admittedly, I don't think that the similar identifiers are the problem.

Rather, I fear, that the file might be too big for your memory, because what you are essentially doing here is writing the whole file into the array a. Given that you say it is a file of 53 million records and 484 columns, awk or your memory might just be unable to handle this.

You could try to use sort -u db.txt > outdb.txt, but that will only remove duplicated lines and not solely duplicated IDs and might also run into memory issues.

Other than that, you would need to find a way how to remove duplicates without keeping the whole file in memory. This here could work:

sort -k 1 db.txt > dbsorted.txt
awk '{a[NR]=$1; delete a[NR-2]}; {if (a[NR-1]!=$1){print $0}}' dbsorted.txt > dbdedup.txt

This will only compare the current identifier to the identifier in the previous line, without keeping any more than 2 identifiers in memory at a given time, and evidently requires a properly sorted input.

I haven't thoroughly tested it, though, so please at least also run the cross-control with if (a[NR-1]==$1) to print only the duplicates...

• 0 views • link
written by Matthias Zepper Matthias Zepper

That fixes it:

wc -l dbsorted.txt 
53650725 dbsorted.txt

    wc -l dbdedup.txt
51456321 dbdedup.txt

awk '{a[NR]=$1; delete a[NR-2]}; {if (a[NR-1]==$1){print $0}}' dbsorted.txt > onlydedups.txt
wc -l onlydedups.txt 
2194404 onlydedups.txt

Thanks!

• 0 views • link
written by bsp017 • bsp017

You're welcome!

PS: To print out duplicates, including the first occurrences:

sort -k 1 db.txt > dbsorted.txt
awk '{a[NR]=$1; b[NR]=$0; delete a[NR-2]; delete b[NR-2]}; {if (a[NR-1]==$1){print b[NR-1]"\n"$0}}' dbsorted.txt > allduplicatedentries.txt
• 0 views • link
written by Matthias Zepper Matthias Zepper

0 answers

No answers yet.

Log in to answer this question.

More posts like this