[SOLVED] Analysis on the basis of comparison of 1st column of 1 files with 1st column of N number of files and print all files based of column 1

Issue

I have tab separated files and need to compare FILE_1 with N (10) files, If the IDS of column 1 of first file match with the 1st column of other files print file 1 and value of the other files and if the IDS not presnt , first file and NA to the column of other file. The example of the input and expected output file are given below.

File 1

A   1.1  0.2    0.3     1.1
B   1.3  2.1    0.2     0.1
C   1.8  0.5    2.6     3.8
D   1.2  5.1    1.7     0.1
E   1.9  4.3    2.8     1.6
F   1.6  5.1    2.9     7.1
G   1.8  2.8    0.3     3.7
H   1.9  3.6    3.7     0.1
I   1.0  2.4    4.9     2.5
J   1.1  2.0    0.1     0.4

File 2

A   d1   Q2    Q.3  E.1
B   a.3  S.1   A.2  R.1
J   a.1  2.0   031  4a4

File 3

E    1d9    4a3  2A8    1D6
F    1a.6   5a1  2W9    7Q1
J    QA8    1.8  0W3    3E7

File 4

F   1aa  5a   2Q    7WQ
G   ac   UW   0QW   3aQ
A   QQ   aws  AW    qw

I have tried the following code with two file initially but not getting the expected output

awk  '
        FILENAME == "File_2" {
                id = $0
                val[id] = $2","$3","$5
        }
        FILENAME == "File_1" {
                id = $1
                string
                if (val[id] == "") {
                        print id " " "NA"
                } else {
                        print id " " val[id]
                }
        }
' File_2 File_1 

The above code print the File_2 and NA at the end of each line.

My expected output is looks like below

Final Expected Output

A  1.1  0.2  0.3  1.1  d1   Q2   Q.3  E.1  NA    NA   NA   NA   QQ   aws  AW   qw
B  1.3  2.1  0.2  0.1  a.3  S.1  A.2  R.1  NA    NA   NA   NA   NA   NA   NA   NA
C  1.8  0.5  2.6  3.8  NA   NA   NA   NA   NA    NA   NA   NA   NA   NA   NA   NA
D  1.2  5.1  1.7  0.1  NA   NA   NA   NA   NA    NA   NA   NA   NA   NA   NA   NA
E  1.9  4.3  2.8  1.6  NA   NA   NA   NA   1d9   4a3  2A8  1D6  NA   NA   NA   NA
F  1.6  5.1  2.9  7.1  NA   NA   NA   NA   1a.6  5a1  2W9  7Q1  1aa  5a   2Q   7WQ
G  1.8  2.8  0.3  3.7  NA   NA   NA   NA   NA    NA   NA   NA   ac   UW   0QW  3aQ
H  1.9  3.6  3.7  0.1  NA   NA   NA   NA   NA    NA   NA   NA   NA   NA   NA   NA
I  1.0  2.4  4.9  2.5  NA   NA   NA   NA   NA    NA   NA   NA   NA   NA   NA   NA
J  1.1  2.0  0.1  0.4  a.1  2.0  031  4a4  QA8   1.8  0W3  3E7  NA   NA   NA   NA

Solution

Using GNU awk for arrays of arrays, ARGIND, and gensub():

$ cat tst.awk
BEGIN { FS=OFS="\t" }
ARGIND < (ARGC-1) {
    key = $1
    sub("[^"FS"]+"FS"?","")
    fileNrsKeys2vals[ARGIND][key] = $0
    fileNrs2numFlds[ARGIND] = NF
    next
}
{
    printf "%s", $0
    for ( fileNr=1; fileNr<ARGIND; fileNr++ ) {
        if ( fileNr in fileNrs2numFlds ) {
            numFlds = fileNrs2numFlds[fileNr]
            printf "%s", ( $1 in fileNrsKeys2vals[fileNr] ?
                OFS fileNrsKeys2vals[fileNr][$1] :
                gensub(/ /,OFS"NA","g",sprintf("%*s",numFlds,"")) )
        }
    }
    print ""
}

$ awk -f tst.awk file2 file3 file4 file1
A       1.1     0.2     0.3     1.1     d1      Q2      Q.3     E.1     NA      NA      NA      NA      QQ      aws     AW      qw
B       1.3     2.1     0.2     0.1     a.3     S.1     A.2     R.1     NA      NA      NA      NA      NA      NA      NA      NA
C       1.8     0.5     2.6     3.8     NA      NA      NA      NA      NA      NA      NA      NA      NA      NA      NA      NA
D       1.2     5.1     1.7     0.1     NA      NA      NA      NA      NA      NA      NA      NA      NA      NA      NA      NA
E       1.9     4.3     2.8     1.6     NA      NA      NA      NA      1d9     4a3     2A8     1D6     NA      NA      NA      NA
F       1.6     5.1     2.9     7.1     NA      NA      NA      NA      1a.6    5a1     2W9     7Q1     1aa     5a      2Q      7WQ
G       1.8     2.8     0.3     3.7     NA      NA      NA      NA      NA      NA      NA      NA      ac      UW      0QW     3aQ
H       1.9     3.6     3.7     0.1     NA      NA      NA      NA      NA      NA      NA      NA      NA      NA      NA      NA
I       1.0     2.4     4.9     2.5     NA      NA      NA      NA      NA      NA      NA      NA      NA      NA      NA      NA
J       1.1     2.0     0.1     0.4     a.1     2.0     031     4a4     QA8     1.8     0W3     3E7     NA      NA      NA      NA

Answered By - Ed Morton

Answer Checked By - Marilyn (WPSolving Volunteer)