compare two files on the basis of first columns of two file and get matching and non-matching rows

Question

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?

Answer 1

Simply feed the awk-script with both files. While reading the first file fill an array that stores the values for the IDs.

While reading the second file print out each ID along with the previously stored value. If there is no stored value just print out a 0.

To distinguish which file is currently read, use the awk-Vaiable FILENAME that contains the name of the currently processed file.

awk '
        FILENAME == "file1.txt" {
                # just fill id to value array
                id = $1
                val[id] = $2
        }
        FILENAME == "file2.txt" {
                id = $1
                # default content for unset array entries is an empty string
                if (val[id] == "") {
                        # we have no value stored within val[]
                        print id "\t" 0
                } else {
                        print id "\t" val[id]
                }
        }
' file1.txt file2.txt

(Note that the example above may be optimized but I wanted it to be easily understood.)

Answer 2

for file in f1 f2 f3 f4
do
awk 'NR==FNR{a[$1];next}($1 in a){print $0}' file2  $file >>output.txt
awk 'NR==FNR{a[$1];next}!($1 in a){print $0"\t""0" }' $file file2 >>output.txt
done

Here Every iteration output is saved in the file output.txt

Suppose every iteration output should be saved in seperate file then we need to follow below steps

for file in f1 f2 f3 f4
do
awk 'NR==FNR{a[$1];next}($1 in a){print $0}' file2  $file >$file_file2
awk 'NR==FNR{a[$1];next}!($1 in a){print $0"\t""0" }' $file file2 >>$file_file2
done