How to combine two files by matching two parameters with awk

First solution using GNU awk or POSIX awk

Edit: As Ed Morton wrote in his comment, the original answer was wrong about what is supported by GNU awk only. (The wording in the GNU documentation vs. the POSIX documentation is a bit confusing.)

What the GNU awk documentation calls Multidimensional arrays is supported by POSIX compatible awk. See https://pubs.opengroup.org/onlinepubs/000095399/utilities/awk.html and search for "multi-dimensional" or SUBSEP. These arrays are in fact one-dimensional.

GNU awk also supports Arrays of Arrays which are real multi-dimensional arrays.

This version of the command requires GNU awk:

awk -F"\t" 'NR == FNR { a[$2][$4] = $5; next } { print $0, a[$2][$4] }' B.txt A.txt > C.txt

The POSIX compatible variant (*), which should work with any awk is

awk -F"\t" 'NR == FNR { a[$2,$4] = $5; next } { print $0, a[$2,$4] }' B.txt A.txt > C.txt

Both print

Cycle   Well    Value   Target 
1   A1  5.07368111264623    EC Unkn-01
1   A1  3.06982862746599    FT Unkn-09
1   A1  2.46545646544623    EC Unkn-01

The data from file B.txt is saved into array a because according to the question the key Well/Target is unique in this file. Then this data is appended to data from file A.txt.

The field separator must be explicitly specified. Otherwise awk would ignore empty columns/values.

This solution uses fixed column numbers to identify the columns to match or to print.

Edit: The following solution solution which explicitly combines the index expressions with \t as separator has no advantage compared to the POSIX compatible solution (*) shown above.

awk -F"\t" 'NR == FNR { a[$2 "\t" $4] = $5; next } { print $0, a[$2 "\t" $4] }' B.txt A.txt > C.txt

This is equivalent to setting SUBSEP = "\t" and using the syntax a[$2, $4].

Second solution using q

The tool q can be used to perform database like queries on CSV files.

See http://harelba.github.io/q/ or https://github.com/harelba/q

This solution has a problem with the empty column title in B.txt. As a workaround I added a title Empty to the heading row of this file.

So I use these files:

A.txt

Cycle   Well    Value   Target
1   A1  5.07368111264623    EC
1   A1  3.06982862746599    FT
1   A1  2.46545646544623    EC

B.txt

Empty   Well    Fluor   Target  Content Sample
    A1  Cy5 EC  Unkn-01 2060563935
    A1  Cy5 FT  Unkn-09 2156515156

The command

q -H -t "select a.Cycle,a.Well,a.Value,a.Target,b.Content from A.txt as a inner join B.txt as b on a.Well=b.Well and a.Target=b.Target"

prints

1   A1  5.07368111264623    EC  Unkn-01
1   A1  3.06982862746599    FT  Unkn-09
1   A1  2.46545646544623    EC  Unkn-01

To print the header you can add a printf or echo command.

printf "Cycle\tWell\tValue\tTarget\tContent\n" > C.txt
q -H -t "select a.Cycle,a.Well,a.Value,a.Target,b.Content from A.txt as a inner join B.txt as b on a.Well=b.Well and a.Target=b.Target" >> C.txt

To automate the modification of file B.txt you can use

printf "Empty" > B1.txt
cat B.txt >> B1.txt
printf "Cycle\tWell\tValue\tTarget\tContent\n" > C.txt
q -H -t "select a.Cycle,a.Well,a.Value,a.Target,b.Content from A.txt as a inner join B1.txt as b on a.Well=b.Well and a.Target=b.Target" >> C.txt

This solution used named columns from the header row to identify the columns to match or to print.