linux - How to merge two files with different fields based on one matching columns?

Question

I have two files as below and I want to merge them into one file based on common IDs:

File1:

ARS     8.0   8.0
BBL     1.1   1.2
CCL     1.9   1.8

File2:

ARS     2.3   2.4
ARS     2.6   2.4
ARS     2.5   2.3
BBL     1.9   1.8
EDE     1.4   1.6

Desired output:

ARS     8.0   8.0    ARS     2.3   2.4
ARS     8.0   8.0    ARS     2.6   2.4   
ARS     8.0   8.0    ARS     2.5   2.3
BBL     1.1   1.2    BBL     1.9   1.8
CCL     1.9   1.8    NA
NA                   EDE     1.4   1.6

Answer 1

$ cat tst.awk
BEGIN { FS=OFS="	" }
FNR==1 {
    na = $0
    gsub("[^"FS"]","",na)
    nas[++numFiles] = "NA" na
}
NR==FNR { file1[$1] = $0; next }
$1 in file1 { print file1[$1], $0 }
{ file2[$1] = $0 }
END {
    for (key in file1) {
        if ( !(key in file2) ) {
            print file1[key], nas[2]
        }
    }
    for (key in file2) {
        if ( !(key in file1) ) {
            print nas[1], file2[key]
        }
    }
}

.

$ awk -f tst.awk file1 file2
ARS     8.0     8.0     ARS     2.3     2.4
ARS     8.0     8.0     ARS     2.6     2.4
ARS     8.0     8.0     ARS     2.5     2.3
BBL     1.1     1.2     BBL     1.9     1.8
CCL     1.9     1.8     NA
NA                      EDE     1.4     1.6

There's various ways it could be optimized to not store the whole contents of both files in arrays but I like the simplicity and symmetry of the above and it's trivial to optimize later if that proves to be necessary due to massive input files.