find matching rows based on snp ids

Question

[gulcek]

I have 2 files, one is about 7 MB and other is 1 GB. The first file seems:

rs58108140    A/G    chr1    10582
rs10218492    A/G    chr1    10827
rs10218493    A/G    chr1    10903

and the second one is:

rs58108140
rs10218493
rs11240777

I need to search second files snps in the first file and return the matching rows. It should be like that:

rs58108140    A/G    chr1    10582
rs10218493    A/G    chr1    10903

I tried to put all them in a database and take their inner join based on snpids but it is still working. Do you know a fast solution?

[Manuel Landesfeind]

I strongly recommend alessia's solution! But just for the record: have you build an index over the columns that you join on (i.e., the columns containing the IDs)? Otherwise, it is not surprising that the query takes forever ;-)

Answer 1

[alesssia]

What you want to do is to extract the strings in file1 that matches (grep, in Unix) exactly (-F) the strings in file2 (-f) and save the result in file3, that is:

grep -F -f file2 file1 > file3

[george.ry]

Yup, like this, but make sure LC_ALL=C is set when you're using large files, or you'll be there all day! Either add export LC_ALL=C to your ~/.bashrc and re-source the file (source ~/.bashrc) or put in manually in the terminal.

LC_ALL=C grep -wFf file1 file2 > file3

[Pierre Lindenbaum]

You mean LC_ALL=C, not LS_ALL

[george.ry]

I do indeed. Brain not engaged fully ...

[dariober]

Isn't grep going to do a "all vs all" comparison? With 7M and 1GB files it might take sometime...

Answer 2

[dariober]

Not tested for speed but this should be quite fast:

join <(sort -k1,1 aa.txt) <(sort -k1,1 bb.txt) | uniq > ab.txt

Skip the sort commands if files are already sorted of course.