CODEWITHSUNDEEP
bashawksed
Iceman
Iceman

Reputation: 385

average columns based on one column

This is sample IOstat data running for a period of time.(I have removed empty lines,spaces etc) I need the average of each column for each disk.

Any idea how to do it using awk/sed ?

  125.7   39.9  979.3  394.4  0.0  2.2    0.1 13.462   0  27 c1t5000CCA01BBC5A7Cd0
  127.1   39.8  977.6  394.5  0.0  2.2    0.0 13.441   0  27 c1t5000CCA01BBC59ACd0
  126.7   39.9  975.8  394.0  0.0  2.2    0.1 12.926   0  26 c1t5000CCA01BBC59DCd0
  126.2   40.0  970.5  394.0  0.0  2.2    0.0 13.115   0  26 c1t5000CCA01BBC5A2Cd0
    7.9    0.0   10.9    0.0  0.0  0.0    0.0  0.152   0   0 c3t0d0
  124.7  183.0 1236.0 2075.1  0.0  7.6    0.0 24.838   0  35 c1t5000CCA01BBC5A2Cd0
    0.0  148.4    0.0  322.4  0.0  6.6    0.0 44.482   0  38 c3t0d0
   32.0    9.7  178.1    3.9  0.0  0.3    0.0  7.822   0  15 c1t5000CCA01BBC5980d0
   62.1    0.0   31.0    0.0  0.0  0.0    0.0  0.000   0   0 c1t5000CCA01BBC5950d0
   37.8    9.7  346.8    3.9  0.0  0.3    0.0  6.821   0  15 c1t5000CCA01BBC5A44d0
   54.3    4.8  649.4    0.0  0.0  1.6    0.0 27.885   0  19 c1t5000CCA01BBC58E4d0
   50.4    9.7  622.2    3.9  0.0  0.7    0.0 11.007   0  19 c1t5000CCA01BBC5A48d0
   90.2    4.8 1922.2    0.0  0.0  2.8    0.0 29.611   0  25 c1t5000CCA01BBC5AA8d0
   62.1  161.0   31.0  354.4  0.0  0.8    0.0  3.777   0   5 c1t5000CCA01BBC5918d0
  102.8  430.6 1897.1 26280.3  0.0  1.7    0.0  3.281   0  11 c1t5000CCA01324687Cd0
  496.5 20587.8 1988.1 96156.7  0.1 13.4    0.0  0.637   6  46 c1t5000CCA0132475ACd0
  487.8 20588.8 1961.0 96160.6  0.1 13.5    0.0  0.640   6  46 c1t5000CCA0132477ECd0

EDIT:

Input file 

c1 c2 c3 c4 diskname

1  1   1 1   d1
1  2   3 2   d2
2  1   4 3   d3
3  2   3 2   d1
1  2   3 2   d2
1  2   3 2   d3

Expected Output 

Avg_c1 Avg_c2 Avg_c3 Avg_c4    diskname
  2     1.5     2      1.5    d1
  1      2      3       2     d2
 1.5    1.5    3.5     2.5    d3

Where

 X={d1,d2,d3}
 Y={c1,c2,c3,c4}

Avg_[Y] for X = sum of column Y where diskname is X/number of entries of X

I need something that works for any number of columns and disknames.

Upvotes: 0

Views: 68

Answers (2)

Ed Morton
Ed Morton

Reputation: 203169

Here's how to get the data:

$ cat tst.awk        
NR==1 {
   key = $NF
   $NF = ""
   gsub(/[^[:space:]]+/,"Avg_&")
   print $0 key
   next
}
NF {
   for (i=1;i<NF;i++) {
       sum[$NF,i] += $i
   }
   cnt[$NF]++
}
END {
   for (key in cnt) {
       for (i=1;i<NF;i++) {
           printf "%s%s", sum[key,i] / cnt[key], OFS
       }
       print key
   }
} 
$ awk -f tst.awk file
Avg_c1 Avg_c2 Avg_c3 Avg_c4 diskname
2 1.5 2 1.5 d1
1 2 3 2 d2
1.5 1.5 3.5 2.5 d3

Let us know if you have trouble formatting the output using printf or piping to column or whatever...

Upvotes: 1

Wintermute
Wintermute

Reputation: 44023

With awk:

awk '{ fields[$NF] = NF; ++ctr[$NF]; for(i = 1; i < NF; ++i) { sum[$NF,i] += $i } } END { PROCINFO["sorted_in"] = "@ind_str_asc"; for(c in ctr) { for(i = 1; i < fields[c]; ++i) { printf("%.3f\t", sum[c,i] / ctr[c]) } print c } }' filename

That is:

{
  ++ctr[$NF]                   # in every line, count how often the disk
  for(i = 1; i < NF; ++i) {    # appeared and sum up the fields
    sum[$NF,i] += $i
  }
}
END {                          # in the end, print the tallies
                               # divided by the counter (the averages)

  # PROCINFO["sorted_in"] is GNU-specific, for sorted traversal. If that is a
  # problem or not necessary, it is safe to remove it.
  PROCINFO["sorted_in"] = "@ind_str_asc"

  for(c in ctr) {
    for(i = 1; i < NF; ++i) { 
      printf("%.3f\t", sum[c,i] / ctr[c])
    }
    print c
  }
}

To skip a header line, put

NR > 1

before the first { (to skip two, use NR > 2. I'm not sure if that's supposed to be part of your input).

Upvotes: 1

Related Questions