Selecting Records and Averaging Fields 1

[thunderkid]

Need help! I am trying to calculate the average for the 5th column values for records when value in third column changes. A sample of data is given below.
thanks
thunderkid

data
-----
short 2 1 3 2 0
short 2 1 3 2 0
short 2 1 3 2 0
short 2 1 3 2 0
short 2 1 3 2 0
short 2 1 3 2 0
short 2 1 3 2 0
short 2 1 3 2 0
short 2 1 3 2 0 <-- select this record
short 2 2 0 8 0
short 2 2 0 8 0
short 2 2 0 8 0
short 2 2 0 8 0
short 2 2 0 8 0
short 2 2 0 8 0
short 2 2 0 8 0
short 2 2 0 8 0
short 2 2 0 6 2 <-- select this record

desired results
Col1 Col2 Col5avg
short 2 4

 

[futurelet]

Your example indicates that you always want to select
the last record.

col3 && col3 != $3 { record() }

{ col1 = $1
  col2 = $2
  col3 = $3
  col5 = $5
}

END { record()
  fmt = "%-9s%5s%9s\n"
  printf fmt, "Col1", "Col2", "Col5avg"
  for (k in totals)
    printf fmt, k, column2[k], totals[k]/counts[k]
}

function record()
{ totals[ col1 ] += col5
  column2[ col1 ] = col2
  counts[ col1 ]++
}

Does this do what you want?

 

[thunderkid]

Perfect futurelet! Thanks a million. Have a star!

thunderkid

 

[futurelet]

Thanks, thunderkid. If you want to start writing your own simple Awk scripts, see faq271-5564.