awk: math operations of multi-column data in multiple CSV files

Question

I am working on bash script that loops multi-column data filles and executes integrated AWK code to operate with the multi-column data.

#!/bin/bash
home="$PWD"
# folder with the outputs
rescore="${home}"/rescore 
# folder with the folders to analyse
storage="${home}"/results
    

while read -r d; do
awk -F ", *" '                  # set field separator to comma, followed by 0 or more whitespaces
FNR==1 {
   if (n) {                     # calculate the results of previous file
     f=                   # apply this equation to rescore data using values of $3 and $2
      f[suffix] = f          # store the results in the array
      n=$1 # take ID of the column
   }
   prefix=suffix=FILENAME
   sub(/_.*/, "", prefix)
   sub(//[^/]+$/, "", suffix)
   sub(/^.*_/, "", suffix)
   n = 1                        # count of samples
   min = 0                      # lowest value of $3 (assuming all $3 < 0)
}
FNR > 1 {
   s += $3
   s2 += $3 * $3
   ++n
   if ($3 < min) min = $3       # update the lowest value
}
   print "ID" prefix, rescoring
   for (i in n)
      printf "%s %.2f
", i, f[i]
}'  "${d}_"*/input.csv > "${rescore}/"${d%%_*}".csv"
done < <(find . -maxdepth 1 -type d -name '*_*_*' | awk -F '[_/]' '!seen[$2]++ {print $2}')

Briefly, the workflow should process each line of the input.csv located inside ${d} folder that correctly has been identified by my bash script:

# input.csv located in the folder 10V1_cne_lig12
ID, POP, dG
1, 142, -5.6500 # this is dG(min)
2, 10, -5.5000
3, 2, -4.9500
4, 150, -4.1200

My AWK script is expected to process each line of each CSV file in order to reduce them to the two columns, keeping in the output: i) the number from the first column of the input.csv (contained ID of the processed line) + the name of the folder ($d) contained the CSV file as well as ii) the result of the math operation (f) applied on the numbers in POP and dG columns of the input.csv:

f(ID)= sqrt(((dG(ID)+10)/10)^2+((POP(ID)-240)/240))^2)

where dG(ID) is the value of dG ($3) of the "rescored" line of input.csv, and POP(ID) is its POP value ($2).Eventually output.csv contained information regarding 1 input.csv should be in the following format:

# output.csv
ID,             rescore value
1 10V1_cne_lig12,  f(ID1)
2 10V1_cne_lig12,  f(ID2)
3 10V1_cne_lig12,  f(ID3)
4 10V1_cne_lig12,  f(ID4)

While bash part of my code (dealing with the looping of CSVs in the distinct directories) works correctly I am stuck with the AWK code, which does not assign correctly ID of the lines in order that I could apply demonstrated math operations using $2 and $3 columns of the line with precised ID.

user_1435869 · Answer

given the input file: folder/file

ID, POP, dG
1, 142, -5.6500 
2, 10, -5.5000
3, 2, -4.9500
4, 150, -4.1200

this script

$ awk -F', *' -v OFS=', ' '
        FNR==1 {path=FILENAME; sub(/\/[^/]+$/,"",path); print $1,"rescore value"; next} 
               {print $1" "path, sqrt((($3+10)/10)^2+(($2-240)/240)^2)}' folder/file

will produce

ID, rescore value
1 folder, 0.596625
2 folder, 1.05873
3 folder, 1.11285
4 folder, 0.697402

Not sure what the rest of your code does, but I guess you can integrate it in.

awk: math operations of multi-column data in multiple CSV files

1 Answers