averaging column 1

[rhnaeco]

Hi,

I have a data file which looks like this:

12.44342 45.42285 -6.63031 49170 243 159.083
12.44332 45.4228 -6.66277 49180 105 142.417
12.44315 45.42279 -6.66277 49190 255 133.25
12.44297 45.42279 -6.64404 49200 120 109.25
12.4428 45.42277 -6.66027 49210 231 103.583
12.44263 45.42272 -6.64404 49220 157 110.25
12.44247 45.4227 -6.59535 49230 255 86.5833
12.44232 45.42267 -6.62781 49240 129 74.4167
12.44215 45.42264 -6.62781 49250 162 33.5833
12.442 45.4226 -6.59535 49260 255 91.4167
12.44183 45.42259 -6.57911 49270 175 85.5833
12.44167 45.42255 -6.59535 49280 106 102.417
12.44075 45.42242 -6.54666 49336 182 37.9167

i would like to average each record on the last field with the following 5 records so the last field looks like:

A (command in excel)
129.5166 =Average(A1:A5)
119.75 =Average(A2:A6)
108.58326 =Average(A3:A7)
96.8166 =Average(A4:A8)
81.68326
79.25
74.31666
77.4834
70.1834
70.35008
54.91674
86.25008
80.71668
87.98334

other answers on averaging seem very long and complicated, but i find it hard to believe that excel could be easier!!

thanks in advance
rhnaeco

 

[feherke]

Hi

As far as I understood, you need the following, but probably not, as it does not match your output :

awk 'NF{s=0;for(i=1;i<=NF;i++)s+=$i;$(NF+1)=s/NF}1' /input/file

other answers on averaging seem very long and complicated, but i find it hard to believe that excel could be easier!!

Compare also the size, speed, CPU and memory requirements of [tt]awk[/tt] and Excel.

Feherke.

http://rootshell.be/~feherke/

 

[rhnaeco]

I need it so that :

original last column
159.083
142.417
109.25
103.583
110.25
86.5833
74.4167

(159.083 + 142.417 + 109.25 + 103.583 + 110.25)/5 = 129.516
(142.417 + 109.25 + 103.583 + 110.25 + 86.583)/5 = 119.75
(109.25 + 103.583 + 110.25 + 86.5833 + 74.416)/5 = 108.583

print $0 of original file + new column
I hope this makes it clearer

thanks

 

[feherke]

Hi

awk '{v[NR%5]=$NF}NR>=5{s=0;for(i=0;i<5;i++)s+=v[i];print s/5}' /input/file

Tested with [tt]gawk[/tt] and [tt]mawk[/tt].

Feherke.

http://rootshell.be/~feherke/

 

[rhnaeco]

thank you, thank you, thank you!!
what does % do? (google is no help)

this has been very frustrating for me as i had lots of components to this answer but couldn't for the life of me work out how they would all fit together!

thanks again

rhnaeco

 

[PHV]

what does % do?
This is the modulo operator.
Tip: man awk

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[feherke]

Hi

Sad, I also saw that not all man pages explains it. Is modulus, the remainder of division.

The output of this command will make clear why I used it :

seq 25 | awk '{print $1%5}'

Feherke.

http://rootshell.be/~feherke/

 

[risby]

Ah, I see this has moved on while I was producing an answer.

I thought the average had to be appended on the line so my answer is different and not so brief

I'm sure feherke could show me how to do this in one line

#! /bin/awk -f

BEGIN {
 i = 0
}

{
# save current line, and separately its
# sixth column, in circular buffers
#
  line[i] = $0
  split($0, column, " ");
  sixth_col[i] = column[6]

# from fifth line onward print first from
# current set of five lines and average
# of the set's final column
#
  if (NR > 4) {
    sum = 0
    for (j=0; j<5; j++) {
      sum += sixth_col[j]
    }
    print line[(i + 1) % 5] " " (sum / 5)
  }
  i = (i + 1) % 5
}

# don't know how you wanted to handle the
# last four lines so I just printed them
# from the buffer appending warning stars
#
END {
  for (j=1; j<=4; j++) {
    i = (i + 1) % 5
    print line[i] " ***"
  }
}

produces

12.44342 45.42285 -6.63031 49170 243 159.083 129.517
12.44332 45.4228 -6.66277 49180 105 142.417 119.75
12.44315 45.42279 -6.66277 49190 255 133.25 108.583
12.44297 45.42279 -6.64404 49200 120 109.25 96.8166
12.4428 45.42277 -6.66027 49210 231 103.583 81.6833
12.44263 45.42272 -6.64404 49220 157 110.25 79.25
12.44247 45.4227 -6.59535 49230 255 86.5833 74.3167
12.44232 45.42267 -6.62781 49240 129 74.4167 77.4834
12.44215 45.42264 -6.62781 49250 162 33.5833 70.1834
12.442 45.4226 -6.59535 49260 255 91.4167 ***
12.44183 45.42259 -6.57911 49270 175 85.5833 ***
12.44167 45.42255 -6.59535 49280 106 102.417 ***
12.44075 45.42242 -6.54666 49336 182 37.9167 ***

==========================================

Some cause happiness wherever they go; others whenever they go.

 

[rhnaeco]

Thanks Feherke, that explained it nicely- i was still none the wiser after wading through the awk man page!

and thanks risby- i was definately on the right track if your answer is anything to go by, but i just couldnt 'join the dots ' so to speak.
thanks again

rhnaeco