Thank you for your help. Yes, it works. However, it prints three columns from file1 and four columns from file2. Can I increase the columns in print such as print four columns of file1 and six columns of file2.
Hi, I have two large files, one is the list of SNPs (file1) and another its annotation file (file2). Please help me to write a code for the following analysis. I am looking to fetch matching data in two files if the data in column first and second are found to match and print the entire row of files (first and second).
I tried the following command but it just prints the data of the second file. However, I want to print matching data of both files (first and second).
awk -F'|' 'NR==FNR{c[$1,$2]++;next};c[$1,$2] > 0' file1.txt file2.txt >out.txt
For example:
File 1:
chr1 9133639 T CMD
chr2 6134363 C FFP
chr4 6344639 A FFP
File 2:
chr1 9133639 T GI_02334
chr2 6134363 C GI_02338
chr4 6344639 A GI_02365
chr1 7133739 A GI_02339
chr2 5134763 C GI_02389
chr4 4344639 T GI_04365
Expected Output:
chr1 9133639 T CMD chr1 9133639 T GI_02334
chr2 6134363 C FFP chr2 6134363 C GI_02338
chr4 6344639 A FFP chr4 6344639 A GI_02365
1 answer
Via Unix join:
% join -t $'\t' -11 -21 -o 1.2,1.3,1.4,2.2,2.3,2.4,2.5 <(awk -v FS="\t" -v OFS="\t" '{ print $1"_"$2, $0 }' A.txt | sort -k1) <(awk -v FS="\t" -v OFS="\t" '{ print $1"_"$2, $0 }' B.txt | sort -k1)
chr1 9133639 T chr1 9133639 T GI_02334
chr2 6134363 C chr2 6134363 C GI_02338
chr4 6344639 A chr4 6344639 A GI_02365
This works by creating a unique identifier key from the chromosome and position of each file with awk, sorting on that key, and joining the two files on that key (-11 -21). The -o 1.2,1.3,1.4,2.2,2.3,2.4,2.5 option prints out non-key columns.
Using sorted data will add runtime, but it may be preferable to reading both files into associative arrays in awk (or dictionaries in Python, hash tables in Perl, etc.) as the latter approach can use a lot of memory, which could be troublesome if your second file is very large.
Here's a dictionary approach in Python, using a tuple of the first and second column values as a key:
#!/usr/bin/env python
import sys
f1_fn = "A.txt"
f2_fn = "B.txt"
f1 = {}
with open(f1_fn, 'r') as f1_fh:
for l in f1_fh:
e = l.rstrip().split('\t')
f1[(e[0], e[1])] = e
f2 = {}
with open(f2_fn, 'r') as f2_fh:
for l in f2_fh:
e = l.rstrip().split('\t')
f2[(e[0], e[1])] = e
# inner join
k1 = f1.keys()
k2 = f2.keys()
for k in k1:
if k in k2:
sys.stdout.write('{}\n'.format('\t'.join(f1[k] + f2[k])))
Log in to answer this question.

How large of files are we talking? And does it need to be performed in the shell?
My solution to this would be to import the files into R and combine the data together with a
left_join()by chr, position, and nucleotide.Otherwise you might be able to do what you want with the
joincommand, but it might take some extra work sincejoinrequires that you only join by one field and that the files are sorted by the key columnThank you for your reply. A total number 1500 of records in SNPs (file 1) and 593337 records are in annotation file (file2). It is not mandatory to be performed in shell script. However, if there is a shell or python/perl script, it would be best. Could you please elaborate the left_join() to use in R.