There's no real need to write such a complicated script for this though, simple use of unix command line tools are enough.
Firstly, verify that the files are indeed sorted on the third and second column resp with these commands.
sort -ck3 file1.db
sort -ck2 file2.query
We get no error, so they are sorted. (Note that we're doing a textual comparision, not a numerical one, because join can't handle the latter.)
Then let's use the join command to join the two files.
join -a 2 -1 3 -2 2 -e - -o 2.1,2.2,2.3,2.4,2.5,2.6,2.7,2.8,1.1 file1.
+db file2.query | tee result
Admittedly the command-line looks complicated because we use just about any option join has, but we're lucky because we don't need any extra features join doesn't have. (If you have numbers in the join column that aren't of the same width, then you're out of luck here and likely need at least a simple perl script.)
The output is this.
1190 31277 A > T 1 0 0 -
1190 31607 C > A 0 3 1 -
1190 31629 C > T 0 2 0 -
1190 31789 A > G 1 2 5 zm1829427
1190 31882 A > C 0 4 0 -
1190 31883 T > A 0 4 0 zm445312
1190 31883 T > C 2 2 5 zm445312
1190 32199 C > T 0 1 1 -
1190 32487 T > C 0 1 1 -
1190 32496 A > G 0 3 0 -
The whitespaces separating the fields are destroyed, but if that bothers you you can fix it up easily with this command.
(sed 's/^\([^ ]*\) \([^ ]*\) \([^ ]* [^ ]* [^ ]*\) \([^ ]*\) \([^ ]*\)
+ \([^ ]*\)\(.*\)/\1 \2 \3 \4 \5 \6 \7/' result; ech
+o; echo -n "Total number of HITS: "; grep -cv ' -$' result) |tee resu
+lt.fmt
The result is this:
1190 31277 A > T 1 0 0 -
1190 31607 C > A 0 3 1 -
1190 31629 C > T 0 2 0 -
1190 31789 A > G 1 2 5 zm1829427
1190 31882 A > C 0 4 0 -
1190 31883 T > A 0 4 0 zm445312
1190 31883 T > C 2 2 5 zm445312
1190 32199 C > T 0 1 1 -
1190 32487 T > C 0 1 1 -
1190 32496 A > G 0 3 0 -
Total number of HITS: 3
Update 2009 sep 2.
See Re^2: Joining two files on common field for a list of other nodes where unix textutils is suggested to merge files.
|