AWK: If two key columns match between files, add the 16th column of one file to the matching row of the other while keeping non-matching rows

Question

I have two tab separated files (FileA.tsv and FileB.tsv).

FileA.tsv

id	graph	circle	several columns...	length
196-0	196	0	----	12874
195-1	195	1	----	12874
56-0	56	0	----	3349
115-1	115	1	----	5297

File A has hundreds of lines and 12 columns, not all depicted here. Each value of 2 and 3 is not unique, but their specific combination is. As such, event_id is an unique identifier, made of the values in 2 and 3 concatenated.

FileB.tsv

Column 1	Column 2	Column 3	several columns...	Column 16
195	1	coverage	----	CTTGCTTGAGCTGCTCTGCAA...
196	0	coverage	----	TTCTAAAGTATAAAGCCTGTC...
196	9	coverage	---	TTCTAAAGTATAAAGCCTGTC ...
196	11	coverage	---	ACATTTAAAGAATTGCTTAAG...

FileB does not have headers.

Columns 2 and 3 match some of the columns 1 and 2 of file A. Once again the values in columns 1 and 2 are not unique but their specific combination is. All the rows appearing in FileB always have matches in FileA, but the opposite is not true.

Using awk, I would like to check if each row of FileA has $2 and $3 matching $1 and $2 of FileB, and if so,print the full FileA row and add the corresponding $16 value of FIleB to the end of that row. If not, print the FIleA row as it is.

Expected Output (FileC):

id	graph	circle	several columns	length	Column 16
196-0	196	0	----	12874	TTCTAAAGTATAAAGCCTGTC...
195-1	195	1	----	12874	CTTGCTTGAGCTGCTCTGCAA...
56-0	56	0	----	3349	----
115-1	115	1	----	5297	----

So far, I have:

awk -F "\t" 'NR==FNR {a[$1,$2]=($16); next} ($2,$3) in a {print $0, a[$16]}' FileB.tsv FileA.tsv > FileC.tsv

This code does give me only the matched rows, however, it does not append $16 to the end of the matching rows:

Empty	Empty	Empty	Empty	Empty
196-0	196	0	----	12874
195-1	195	1	----	12874

If I try adding the If-Else statement:

awk -F "\t" 'NR==FNR {a[$1,$2]=($16); next} { if (($2,$3) in a) {print $0, a[$16]} else {print $0}}' FileB.tsv FileA.tsv > FileC.tsv

So as to keep the headers and non-matching rows of FileA, the output is simply FileA.

I'm new to awk, however I have researched a lot and found many examples of manipulation similar to this being done, and my code seems very similar to other examples I've seen.

However, I have yet to find an example in which there are two corresponding key rows between files, they are not in the same position AND the non-matching columns are also kept.

This is being run on several directories, each with their own set of FileA and FileB, using a Bash loop. No problems in that regard, all directories have their own outputting FileC, however wrong its contents may be:

set -euo pipefail
IFS=$'\n\t'
for D in ~/Path/to/directories/with/tables/*; do
    if [ -d "${D}" ]; then
        cd "$D"
        awk -F "\t" 'NR==FNR {a[$1,$2]=($16); next} { if (($2,$3) in a) {print $0, a[$16]} else {print $0}}' *_FileB.tsv  *_FileA.tsv > "${D}".FileC.tsv
    fi
done ```

Any help or correction will be greatly appreciated.

Answer 1

Your awk script is a good start but there's a few fairly small things you need to fix:

1. you need to set the output field separator (OFS) to be a tab, same as FS.
2. you need to print the new column header
3. you need to print a[$2,$3], not a[$16] on matching rows
4. to keep the non-matching rows, you need to print them too, preferably with an empty field appended so that all output rows have the same number of columns.

For example:

$ awk -F "\t" -v OFS='\t' '
  NR == FNR { a[$1,$2] = $5; next };

  FNR == 1     { c = "column 16" };
  ($2,$3) in a { c = a[$2,$3] };

  {
    print $0, c;
    c = ""
  }' FileB.tsv  FileA.tsv 
id      graph   circle  several columns...      length  column 16
196-0   196     0       ----    12874   TTCTAAAGTATAAAGCCTGTC...
195-1   195     1       ----    12874   CTTGCTTGAGCTGCTCTGCAA...
56-0    56      0       ----    3349
115-1   115     1       ----    5297

I've used a[$1,$2]=$5 here because your FileB sample data only has 5 fields. Change that to $16 for your real data.

This uses variable c to hold the value of the column to be appended. It will contain either the new column name, an empty string, or the value of the column to be appended in matching rows. It is reset to the empty string after each output row is printed.

BTW, while the empty fields are normally invisible, you can verify that empty fields are being appended to the non-matching rows by piping the output to cat -T - you'll see ^I (a tab) at the end of those lines.

---

Alternate version that gets the column name from the first line of FileB.tsv instead of hard-coding it:

$ awk -F "\t" -v OFS='\t' '
  NR == 1      { c = $5 ; next };
  NR == FNR    { a[$1,$2] = $5; next };
  ($2,$3) in a { c = a[$2,$3] };

  { print $0, c; c = "" }' FileB.tsv  FileA.tsv 

Answer 2

Using any awk:

$ cat tst.awk
BEGIN {
    FS = OFS = "\t"
}
NR == FNR {
    val = $NF
    if ( FNR == 1 ) {
        hdr = val
    }
    else {
        map[$1 FS $2] = val
    }
    next
}
{
    if ( FNR == 1 ) {
        val = hdr
    }
    else {
        key = $2 FS $3
        val = (key in map ? map[key] : "----")
    }
    print $0, val
}

$ awk -f tst.awk FileB.tsv FileA.tsv
id      graph   circle  several columns...      length  Column16
196-0   196     0       ----    12874   TTCTAAAGTATAAAGCCTGTC...
195-1   195     1       ----    12874   CTTGCTTGAGCTGCTCTGCAA...
56-0    56      0       ----    3349    ----
115-1   115     1       ----    5297    ----

Change $NF to $16 if $16 isn't the last column in the input.