CODEWITHSUNDEEP
linuxunixawksedaverage
Aaron Perry
Aaron Perry

Reputation: 1041

Average Row/Column Data in a Text File

I need to average three columns of data that are in a row in a text file.

Data:

VALID_TIME STN   CDF05 CDF10 CDF20 CDF30 CDF40 CDF50 CDF60 CDF70 CDF80 CDF90 CDF95  MEAN    SD
2015031018 KMGM   50.3  51.5  52.9  54.0  54.9  55.8  56.7  57.6  58.6  60.1  61.3  55.8   3.3 
2015031106 KMGM   75.7  76.8  78.2  79.2  80.0  80.8  81.6  82.4  83.4  84.8  85.9  81.0   4.0 
2015031118 KMGM   54.0  55.1  56.5  57.5  58.4  59.3  60.1  61.0  62.1  63.6  64.8  59.3   3.9
2015031206 KMGM   71.1  72.3  73.9  75.1  76.1  77.0  77.9  78.9  80.1  81.6  82.9  77.0   4.4
2015031218 KMGM   55.5  56.8  58.4  59.5  60.5  61.5  62.4  63.5  64.7  66.3  67.7  61.5   4.4

The columns that I'm interested in averaging together are CDF80, CDF90, and CDF95. The end format should be, for example:

VALID_TIME STN   CDF05 CDF10 CDF20 CDF30 CDF40 CDF50 CDF60 CDF70 CDF80 CDF90 CDF95  MEAN   NEWAVG
2015031018 KMGM   50.3  51.5  52.9  54.0  54.9  55.8  56.7  57.6  58.6  60.1  61.3  55.8   xx.x
2015031106 KMGM   75.7  76.8  78.2  79.2  80.0  80.8  81.6  82.4  83.4  84.8  85.9  81.0   xx.x

Upvotes: 0

Views: 90

Answers (2)

Ben Grimm
Ben Grimm

Reputation: 4371

Averaging the columns is trivial, and you can retain most of the original formatting using printf:

$ awk 'NR==1 { printf "%s %8s\n", $0,"NEWAVG"} NR>1 { printf "%s %7.2f\n", $0,($11+$12+$13)/3 }' /tmp/data
VALID_TIME STN   CDF05 CDF10 CDF20 CDF30 CDF40 CDF50 CDF60 CDF70 CDF80 CDF90 CDF95  MEAN    SD   NEWAVG
2015031018 KMGM   50.3  51.5  52.9  54.0  54.9  55.8  56.7  57.6  58.6  60.1  61.3  55.8   3.3    60.00
2015031106 KMGM   75.7  76.8  78.2  79.2  80.0  80.8  81.6  82.4  83.4  84.8  85.9  81.0   4.0    84.70
2015031118 KMGM   54.0  55.1  56.5  57.5  58.4  59.3  60.1  61.0  62.1  63.6  64.8  59.3   3.9   63.50
2015031206 KMGM   71.1  72.3  73.9  75.1  76.1  77.0  77.9  78.9  80.1  81.6  82.9  77.0   4.4   81.53
2015031218 KMGM   55.5  56.8  58.4  59.5  60.5  61.5  62.4  63.5  64.7  66.3  67.7  61.5   4.4   66.23

Upvotes: 2

Lungang Fang
Lungang Fang

Reputation: 1527

$ cat test.txt | awk -v OFS='\t' 'NR==1{$16="NEWAVG"}; NR!=1{$16=($11+$12+$13)/3};{print $0}'
VALID_TIME      STN     CDF05   CDF10   CDF20   CDF30   CDF40   CDF50   CDF60   CDF70   CDF80   CDF90   CDF95   MEAN    SD      NEWAVG
2015031018      KMGM    50.3    51.5    52.9    54.0    54.9    55.8    56.7    57.6    58.6    60.1    61.3    55.8    3.3     60
2015031106      KMGM    75.7    76.8    78.2    79.2    80.0    80.8    81.6    82.4    83.4    84.8    85.9    81.0    4.0     84.7
2015031118      KMGM    54.0    55.1    56.5    57.5    58.4    59.3    60.1    61.0    62.1    63.6    64.8    59.3    3.9     63.5
2015031206      KMGM    71.1    72.3    73.9    75.1    76.1    77.0    77.9    78.9    80.1    81.6    82.9    77.0    4.4     81.5333
2015031218      KMGM    55.5    56.8    58.4    59.5    60.5    61.5    62.4    63.5    64.7    66.3    67.7    61.5    4.4     66.2333

Upvotes: 3

Related Questions