Successive averaging of repeating data but different number of lines

user_1798797 0
Viewed 71

I have the following format of data:

         1        3
   1.723608      0.8490000
   1.743011      0.8390000
   1.835833      0.7830000
         2        5
   1.751377      0.8350000
   1.907603      0.7330000
   1.780053      0.8190000
   1.601427      0.9020000
   1.950540      0.6970000
         3        2
   1.993951      0.6610000
   1.796519      0.8090000
         4        4
   1.734961      0.8430000
   1.840741      0.7800000
   1.818444      0.7950000
   1.810717      0.7980000
         5        1
   2.037940      0.6150000
         6        7
   1.738221      0.8330000
   1.767678      0.8260000
   1.788517      0.8140000
   2.223586      0.4070000
   1.667492      0.8760000
   2.039232      0.6130000
   1.758823      0.8300000
...

Data consists of data blocks. Each data block has the same format as follows:

  1. The very first line is the header line. The header line contains the ID number and the total number of lines of each data block. For example, the first data block's ID is 1, and the total number of lines is 3. For the third data block, ID is 3, and the total number of lines is 2. All data blocks have this header line.

  2. Then, the "real data" follows. As I explained, the number of lines of "real data" is designated in the second integer of the header line.

  3. Accordingly, the total number of lines for each data block will be number_of_lines+1. In this example, the total number of lines for data block 1 is 4, and data block 2 costs 6 lines...

This format repeats all the way up to 10000 number of data blocks in my current data, but I can provide this 10000 as a variable in the bash or awk script as an input value. I know the total number of data blocks.

Now, what I wish to do is, I want to get the average of data of each two columns and print it out with data block ID number and a total number of lines. The output text will have:

ID_number number_of_lines average_of_column_1 average_of_column_2

using 5 spaces between columns with 6 decimal places format. The result will have 10000 lines, and each line will have ID, number of lines, avg of column 1 of data, and avg of column 2 of data for each data block. The result of this example will look like

1     3     1.767484     0.823666
2     5     1.798200     0.797200
3     2     1.895235     0.735000
...

I know how to get the average of a simple data column in awk and bash. These are already answered in StackOverflow a lot of times. For example, I really favor using

awk '{ total += $2; count++ } END { print total/count }' data.txt

So, I wish to this using awk or bash. But I really have no clue how can I approach and even start to get this kind of average of multiple repeating data blocks, but with a different number of lines for each data block.

I was trying based on awk, following Awk average of n data in each column and https://www.unix.com/shell-programming-and-scripting/135829-partial-average-column-awk.html

But I'm not sure how can I use NR or FNR for the average of data with a varying number of total lines of data, for each data block.

awk average
3 Answers

You may try this awk:

awk -v OFS='\t' '$2 ~ /\./ {s1 += $1; s2 += $2; next} {if (id) {print id, num, s1/num, s2/num; s1=s2=0} id=$1; num=$2} END {print id, num, s1/num, s2/num}' file

1  3  1.76748  0.823667
2  5  1.7982   0.7972
3  2  1.89524  0.735
4  4  1.80122  0.804
5  1  2.03794  0.615
6  7  1.85479  0.742714

If you have gnu awk then use OFMT for getting fixed size decimal numbers like this:

awk -v OFMT="%.6f" -v OFS='\t' '$2 ~ /\./ {s1 += $1; s2 += $2; next} {if (id) {print id, num, s1/num, s2/num; s1=s2=0} id=$1; num=$2} END {print id, num, s1/num, s2/num}' file
1   3   1.767484    0.823667
2   5   1.798200    0.797200
3   2   1.895235    0.735000
4   4   1.801216    0.804000
5   1   2.037940    0.615000
6   7   1.854793    0.742714

An expanded form:

awk OFMT='%.6f' -v OFS='\t' '
$2 ~ /\./ {
   s1 += $1
   s2 += $2
   next
}
{
   if (id) {
      print id, num, s1/num, s2/num
      s1 = s2 = 0
   }
   id = $1
   num = $2
}
END {
   print id, num, s1/num, s2/num
}' file
user_5482250

And yet another one:

awk -v num_blocks=10000 '
BEGIN {
  OFS = "\t"
  OFMT = "%.6f"
}
num_lines == 0 {
  id = $1
  num_lines = $2
  sum1 = sum2 = 0
  next
}
lines_read < num_lines {
  sum1 += $1
  sum2 += $2
  lines_read++
}
lines_read >= num_lines {
  print id, num_lines, 
    sum1 / num_lines,
    sum2 / num_lines
  num_lines = lines_read = 0
  num_blocks--;
}
num_blocks <= 0 {
  exit
}' file
user_102486780

You could try

awk -v qnt=none 'qnt == "none" {id = $1; qnt = $2; s1 = s2 = line = 0;next}{s1 += $1; s2 += $2; ++line} line == qnt{printf "%d     %d     %.6f     %.6f\n", id, qnt, s1/qnt, s2/qnt; qnt="none"}'

The above is expanded as follows:

qnt == "none" 
{
  id = $1;
  qnt = $2;
  s1 = s2 = line = 0;
  next
}
{
  s1 += $1;
  s2 += $2;
  ++line
}
line == qnt
{
  printf "%d     %d     %.6f     %.6f\n", id, qnt, s1/qnt, s2/qnt; 
  qnt="none"
}

After a data block is processed (or at the beginning), record header info. Otherwise, add to sum and print the result when we've done with all lines in this block.

user_52232230
Related