2

I have to compare two tab-delimited files on the basis of 1st column and I have to print the entire row of the first file if the first columns match with the second file. Also, I need to extract those entries which are absent in the first file but present in the second file.

file1.txt:

chr1_45795114_G_A   0.004251803
chr1_45796900_G_C   0.019459893
chr1_45796909_C_T   0.003777726
chr1_45797139_G_A   0.006444843
chr12_56487682_T_A  0.003622254

file2.txt:

chr12_56487682_T_A
chr19_9088697_T_C
chr11_108180929_T_-
chr1_45796909_C_T
chr19_9002597_C_T
chr1_45797139_G_A
chr1_45796900_G_C
chr2_48030697_T_G
chr7_151927025_A_G
chr1_45795114_G_A
chr1_211542902_G_-

Desired output:

chr1_45795114_G_A   0.004251803
chr1_45796900_G_C   0.019459893
chr1_45796909_C_T   0.003777726
chr1_45797139_G_A   0.006444843
chr12_56487682_T_A  0.003622254
chr19_9088697_T_C   0
chr11_108180929_T_- 0
chr19_9002597_C_T   0
chr2_48030697_T_G   0
chr7_151927025_A_G  0
chr1_211542902_G_-  0

I tried to achieve my desired output with the following codes.

Code 1 to compare file1 and file2 and extract the matching rows from file1:

awk -F "\t" 'FNR==NR {a[$1]=$0; next}; $1 in a {print a[$1]}' file1 file2 > matching_rows.txt

Code 2 to compare file1 and file2 and extract the non-matching rows from file2:

awk 'NR==FNR{a[$1]++;next;}!($0 in a)' file1 file2 > nonmatching_rows.txt

Next, I concatenate matching_rows.txt and nonmatching_rows.txt using

cat matching_rows.txt nonmatching_rows.txt > combined.txt

Now to replace blank space from 2nd column I used following code

awk 'NF && $2==""{$2=0}1' FS="\t" OFS="\t" combined.txt > desired_output.txt

My problem is that I have to compare more than 100 files with file2. Is it possible to combine all the above codes in one script and run it using for loop on the basis of the file name to get the desired output file?

0

Yes, you can do it in a single awk.

awk -F '\t' '
    FNR==NR {a[$1]=$0; next}
    $1 in a {print a[$1]; next}
    {rest=rest $1 FS 0 ORS}     #Here non-matching lines. FS is tab; ORS is newline
    END {printf "%s",rest}      #Only executed when all files have been read
' file1.txt file2.txt > desired_output.txt

Output:

chr12_56487682_T_A      0.003622254
chr1_45796909_C_T       0.003777726
chr1_45797139_G_A       0.006444843
chr1_45796900_G_C       0.019459893
chr1_45795114_G_A       0.004251803
chr19_9088697_T_C       0
chr11_108180929_T_-     0
chr19_9002597_C_T       0
chr2_48030697_T_G       0
chr7_151927025_A_G      0
chr1_211542902_G_-      0
| improve this answer | |
  • it is not working properly it prints all over 0 in the second column – user6681622 May 20 at 6:57
  • @user6681622 Are you sure your file is tab-delimited as you said? Test the same awk without -F '\t' and see if it works. – Quasímodo May 20 at 11:57
  • Yes, my file is tab-delimited but after removing ` -F '\t' ` it prints like this chr12_56487682_T_A 0.003622254 as I expect but for non-matching rows, it prints chr19_9088697_T_C 0 i.e it prints 0 in the first column for non-matching rows – user6681622 May 20 at 12:37
  • @user6681622 OK, do you see any \t when you issue od -c file1.txt? If not, your file is not tab-delimited, and that would match the wrong behavior you see, and then you need either to change your question or to edit the file1.txt in your system so that it does have tabs. The solution I provide is for a file1.txt that is tab separated. – Quasímodo May 20 at 13:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.