0

I am trying to put together a small bash code to prepare phenotypic files for stats and mapping. My goal here is to average the values in the WD column for the same PLOT number in a file of this nature:

PLOT BLOC NAME CANE# WD
1001 1 A 1 38.8889
1001 1 A 2 33.3333
1001 1 A 3 
1002 1 B 1 
1002 1 B 2 
1002 1 B 3 
1003 1 C 1 63.1579
1003 2 C 2 95
1003 1 C 3 
[...]

My first approach was to create a new file in which I only stored the plot and WD columns, delete all the rows for which WD data was missing and I applied the formula awk '{seen[$1]+=$2; count[$1]++} END{for (x in seen)print x, seen[x]/count[x]}' input > output

It works fine. However, under this scenario, some plots for which I would like to display 'missing data' are completely missing in the output file (such as plot 1002 in this example).

I am wondering how I can obtain a similar output while keeping the missing data and taking them into account in the average. In fact, I have conducted some attempts but I obtain wrong results, for example, in the case of plot 1001, I obtained an average of 24 instead of 36 (the 3rd data point is missing and should not be seen as 0). In addition, it would help to apply the command on the original file in order to keep the columns bloc and name.

#Create a file WD with only plot and WD column 
# Delete missing values (Stems that did not exist, for which measurements were not collected)
awk '$2!=""' WD.txt > WD1.txt
# Average WD for each plot
awk '{seen[$1]+=$2; count[$1]++} END{for (x in seen)print x, seen[x]/count[x]}' WD.txt > WD1.txt
sed -i '1d' WD1.txt
sed -i '1i PLOT WD%' WD1.txt

Thank you for your help. C

2

You could only add to the count if the WD field is non-empty ex.

$ awk '
    NR>1 {sum[$1] += $5; count[$1] += $5=="" ? 0 : 1} 
    END {for (i in sum) print i, (count[i] > 0 ? sum[i]/count[i] : "-")}
  ' WD.txt
1001 36.1111
1002 -
1003 79.0789

If you don't want to re-invent the wheel, then you could use Miller, whose stats1 appears to treat empty fields the way you want:

$ mlr --pprint stats1 -g PLOT -a mean -f WD WD.txt 
PLOT WD_mean
1001 36.111100
1002 -
1003 79.078950

Miller is available in Ubuntu from the universe repository.

| improve this answer | |
  • Thank you so much. Both options work well. I just used ` join` in order to bring back the BLOC and NAME columns into the output file. – Cindy May 29 at 15:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.