Move file/table by averaging a column

Question

I have the following program that allows me to move a file (actually a table) if the value of the first line of the fourth column is the highest one (in comparison with 8 other files having the same naming style. Here is the script:

#! /bin/bash
river=mississippi
highest=1
for model in H08 MPI-HM WBM PCR-GLOBWB
do
  for gcm in GFDL-ESM2M HadGEM2-ES IPSL-CM5A-LR MIROC-ESM-CHEM NorESM1-M
  do
    for scenario in hist rcp8p5 rcp4p5
    do
      RESULT=$(awk 'FNR==1 {print $4, FILENAME}' ${model}_${gcm}_${scenario}_${river}[1-9]/${model}_${gcm}_${scenario}_${river}[1-9].txt | sort -n -r| head -1) 
      highest="$(echo $RESULT | cut -d ' ' -f1 )"
      hifile="$(echo $RESULT | cut -d ' ' -f2 )"
      echo "highest was $highest in $hifile"
      cp "$hifile" "/home/stevens/SUMARIO/Fred/highest_discharge/${river}/${model}_${gcm}_${scenario}_${river}.txt"
    done
  done
done

Then, I would like to adapt this script in such a way that instead of copying the file having the highest value (in first row, fourth column), I would like to copying the file having the highest average value at the fourth column, in comparison with the other files. Any hints or advice is greatly appreciated!

Answer 1

Just change how you calculate RESULT:

RESULT=$(awk '{x+=$4} END{print x/NR, FILENAME}' ${model}_${gcm}_${scenario}_${river}[1-9]/${model}_${gcm}_${scenario}_${river}[1-9].txt | sort -n -r| head -1) 

What the above does: it sums up all values from fourth row and prints at the end the result divided by number of rows.

Because it divides by the number of rows, you will get unexpected results if you have empty lines in your files: the sum will not take those into consideration, but the divide will be by the number of rows.

Edit:

I had an error in my first attempt which caused the script to only take into consideration the max on the last file only (because END is executed after ALL files are processed)

Correct version:

awk 'FNR==1 && NR>1 {print x/nr, file;x=0}{x+=$4; nr=FNR; file=FILENAME} END{print x/nr, file, x, nr}' ${model}_${gcm}_${scenario}_${river}[1-9]/${model}_${gcm}_${scenario}_${river}[1-9].txt | sort -n -r| head -1

Explanation:

• we print each time the previous file parsed. Because FNR is reset each time a new file is read, we check if we began a new file (FNR == 1) and we are not at the first file (NR > 1). If so, we print the average and the filename saved from the previous file. We also reset x (where we keep the average)
• otherwise we start calculating x. We also save the number of records in this file (FNR) and the filename
• at the end we print the average for the last file

Answer 2

It looks like you already have figured out how to target the columns themselves. Now all you need to do is get an average of the fourth column of all 4 files, and copy the one with the highest average (just sort of re-stating what you've already said).

Basically what you need to do is total the output of column 4 of each of the 4 files, and divide by the number of rows of column 4 in order to get the average. Then copy the file with the highest average.

I expect you are just wanting to know how to get the average, correct?

It's easy to get the number of rows by doing something like echo "$file1_col4" | wc -l or wc -l <<< "$file1_col4", which you should already have. Store each one in a variable to divide by later. So we'll just say this:

file1_col4_rows=$(echo "$file1_col4" | wc -l) 

To get the total of the column, you would do the following:

file1_col4_total=$(echo "$file1_col4" | awk '{total = total + $1}END{print total}')

You would do this for all 4 files and divide by the total number of rows in the column that you set as the variable earlier. Bash has a built-in to handle this, but I prefer using perl:

file1_avg=$(perl -e "print $file1_col4_total/$file1_col4_rows")

By doing this for all 4 files, you could easily see which file had the highest average 4th column by referencing the $fileN_avg variables, and then copy the file which has the variable with the highest value:

file_to_copy="$(echo "file1 $file1_avg
file2 $file2_avg
file3 $file3_avg
file4 $file4_avg" |
sort -nrk2 |
head -1 |
awk '{print $1}')"

Then simply cp "$file_to_copy" /path/where/it/belongs/