0

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

0

1 Answer 1

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"
4
  • @nstatam You're welcome. If you ever encounter a situation where , can occur in the "header" lines, you could try to modify the NF>1 condition to something like $0 !~ /.*=.*/ to explicitly exclude all lines containing a =.
    – AdminBee
    Oct 6, 2020 at 8:53
  • Hi, sorry to re-open this issue but I wich to add something to this command; If the value of what I have to paste is empty, I want to replace it by NaN. Do you have a clue how to change your previous solution ? Thanks
    – nstatam
    Oct 30, 2020 at 11:01
  • @nstatam Added instructions and explanations
    – AdminBee
    Oct 30, 2020 at 14:12
  • Thanks a lot for the full explication !
    – nstatam
    Oct 30, 2020 at 14:27

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .