How to parse then paste in the same line in awk

Question

I have a file like this A.txt (field separator = ,) :

Kit Batch Export
Software Version = NO_v1
Date And Time of Export =
Experiment Name =
Instrument Software Version =
Instrument Type = Cji
Instrument Serial Number =
Run Start Date =
Run End Date =
Run Operator =
Batch Status = VALID
Method = Nov
Date And Time of Export,Batch ID,Sample Name,Well,Sample Type,Status,Interpretive Result,Action*,Curve analysis,EC,CH
,novaprime-ct044032-TB_2034,2061571293,A01,Unkn-01,VALID,,,
,novaprime-ct044032-TB_2034,2061584371,A02,Unkn-09,VALID,,,

And B.csv (field separator = \t ; first column is empty ) :

    Well    Fluor   Target  Content Sample  Cq  SQ
    A01 Cy5 EC  Unkn-01 2060563935  26  NaN
    A02 Cy5 CH  Unkn-09 2060565055  37  NaN
    A01 Cy5 CH  Unkn-01 2060565888  54  NaN
    A02 Cy5 EC  Unkn-09 2060565465  NaN NaN

And I want to add the value (column Cq) of each line for Well/Target (example here : A01/EC; A01/CH; A02/EC; A02/CH) of B.txt in the corresponding line/column of A.txt like this :

Kit Batch Export
Software Version = NO_v1
Date And Time of Export =
Experiment Name =
Instrument Software Version =
Instrument Type = Cji
Instrument Serial Number =
Run Start Date =
Run End Date =
Run Operator =
Batch Status = VALID
Method = Nov
Date And Time of Export,Batch ID,Sample Name,Well,Sample Type,Status,Interpretive Result,Action*,Curve analysis,EC,CH
,novaprime-ct044032-TB_2034,2061571293,A01,Unkn-01,VALID,,,,26,54
,novaprime-ct044032-TB_2034,2061584371,A02,Unkn-09,VALID,,,,NaN,37

To do that I try that :

awk -F"\t" 'FNR==NR{if (a[$2]) {a[$2]=a[$2] "," $7} else {a[$2]=$7}} NR>FNR{split($0,f,","); if (a[f[4]]) $0=$0 "," a[f[4]]; print}' B.txt A.txt > C.txt

It kinda works but it paste the value when it encounter the first iteration, not when it recognise if it's EC or CH. So do you have a different method to properly do that ? Thanks

Answer 1

The following will work as long as no comma can appear in the "Header" lines:

awk -F'\t' 'FNR==NR{if ($4=="EC") ec[$2]=$7; else if ($4=="CH") ch[$2]=$7; next}
            NR>FNR&&NF>1 {if (!f) f=1; else {$10=ec[$4]; $11=ch[$4];}}1' B.txt FS=',' OFS=',' A.txt

This will first parse B.txt and create an "EC-to-Well"-map and a "CH-to-Well"-map, which is then used when parsing A.txt. We set the field separators to , for A.txt and ensure we only process lines with more than one field (i.e. at least one ,), but not the first one which contains the column titles.

Update

Since you state in a comment that sometimes B.txt can contain empty fields where you want to ensure they are replaced with NaN, we need an additional check:

awk -F'\t' 'FNR==NR{if ($4=="EC") ec[$2]=$7; else if ($4=="CH") ch[$2]=$7; next}
            NR>FNR&&NF>1 {if (!f) f=1; else {$10=ec[$4]?ec[$4]:"NaN"; $11=ch[$4]?ch[$4]:"NaN";}}1' B.txt FS=',' OFS=',' A.txt

This is very "golfed", but basically

$10=ec[$4] ? ec[$4] : "NaN"

means

if (ec[$4]) $10=ec[$4]; else $10="NaN"