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