awk Moving Average Post: 303001496

Sponsored Content

Top Forums Shell Programming and Scripting awk Moving Average Post 303001496 by durden_tyler on Saturday 5th of August 2017 03:34:09 AM

08-05-2017

Registered User

Code:

$ 
$ cat data.txt
H1,1.2
H1,2.3
H1,5.5
H1,6.6
H1,8.7
H1,4.1
H1,6.4
H1,7.8
H1,9.6
H1,3.2
H5,50.1
H5,54.2
H5,58.8
H5,60.9
H5,54.3
H5,52.7
H5,53.8
H5,60.1
H5,59.9
H5,59.4
H5,57.1
$ 
$ awk -F, '
    BEGIN {window=5; half=1+int(window/2); iter=1; cnt=1; n=1; off=1; ptr=1}
    { if ($1 != prev) {
          while (cnt > half) {
              cnt--
              split(line[off], delta, ",")
              sum -= delta[2]
              off++
              avg = sum / cnt
              printf("%s,%.1f\n", line[ptr], avg)
              ptr++
          }
          delete line
          sum = 0; iter = 1; cnt = 1;
          off = 1; ptr = 1; n = 1
      }
      line[n] = $0
      n++
      sum += $2
      if (iter >= half) {
          if (iter == half) {
              cnt = half
          } else {
              cnt++
              if (cnt > window) {
                  cnt = window
                  split(line[off], delta, ",")
                  sum -= delta[2]
                  off++
              }
          }
          avg = sum / cnt
          printf("%s,%.1f\n", line[ptr], avg)
          ptr++
      }
      prev = $1
      iter++
    }
    END {
        while (cnt > half) {
            cnt--
            split(line[off], delta, ",")
            sum -= delta[2]
            off++
            avg = sum / cnt
            printf("%s,%.1f\n", line[ptr], avg)
            ptr++
        }
    }
' data.txt
H1,1.2,3.0
H1,2.3,3.9
H1,5.5,4.9
H1,6.6,5.4
H1,8.7,6.3
H1,4.1,6.7
H1,6.4,7.3
H1,7.8,6.2
H1,9.6,6.8
H1,3.2,6.9
H5,50.1,54.4
H5,54.2,56.0
H5,58.8,55.7
H5,60.9,56.2
H5,54.3,56.1
H5,52.7,56.4
H5,53.8,56.2
H5,60.1,57.2
H5,59.9,58.1
H5,59.4,59.1
H5,57.1,58.8
$ 
$

---------- Post updated at 03:34 AM ---------- Previous update was at 02:46 AM ----------

Calculating moving average appears to be pretty convoluted in imperative languages; however a declarative language like (modern) SQL provides syntactic sugar for solving this problem.

By any chance if you are extracting your data from a database, then you may want to check and leverage the power of SQL (specifically, "window" functions) to calculate moving average.
Here's an example with PostgreSQL 9.6, although window functions are available in Oracle, DB2, SQL Server, MySQL and MariaDB as well.

Code:

mydb=> 
mydb=> SELECT VERSION();
                                              version                                              
---------------------------------------------------------------------------------------------------
 PostgreSQL 9.6.3 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18) 6.3.0 20170516, 64-bit
(1 row)

mydb=> 
mydb=> SELECT * FROM data;
 id | val  
----+------
 H1 |  1.2
 H1 |  2.3
 H1 |  5.5
 H1 |  6.6
 H1 |  8.7
 H1 |  4.1
 H1 |  6.4
 H1 |  7.8
 H1 |  9.6
 H1 |  3.2
 H5 | 50.1
 H5 | 54.2
 H5 | 58.8
 H5 | 60.9
 H5 | 54.3
 H5 | 52.7
 H5 | 53.8
 H5 | 60.1
 H5 | 59.9
 H5 | 59.4
 H5 | 57.1
(21 rows)

mydb=> 
mydb=> SELECT id,
mydb->        val,
mydb->        ROUND(
mydb(>            AVG(val) OVER (PARTITION BY id
mydb(>                           ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
mydb(>                          )::NUMERIC,
mydb(>            1
mydb(>        ) AS moving_avg
mydb->   FROM data
mydb-> ;
 id | val  | moving_avg 
----+------+------------
 H1 |  1.2 |        3.0
 H1 |  2.3 |        3.9
 H1 |  5.5 |        4.9
 H1 |  6.6 |        5.4
 H1 |  8.7 |        6.3
 H1 |  4.1 |        6.7
 H1 |  6.4 |        7.3
 H1 |  7.8 |        6.2
 H1 |  9.6 |        6.8
 H1 |  3.2 |        6.9
 H5 | 50.1 |       54.4
 H5 | 54.2 |       56.0
 H5 | 58.8 |       55.7
 H5 | 60.9 |       56.2
 H5 | 54.3 |       56.1
 H5 | 52.7 |       56.4
 H5 | 53.8 |       56.2
 H5 | 60.1 |       57.2
 H5 | 59.9 |       58.1
 H5 | 59.4 |       59.1
 H5 | 57.1 |       58.8
(21 rows)

mydb=> 
mydb=>

durden_tyler

View Public Profile for durden_tyler

Find all posts by durden_tyler

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

how to average in awk

Hi, I have the data like this $1 $2 1 12 2 13 3 14 4 12 5 12 6 12 7 13 8 14 9 12 10 12 i want to compute average of $1 and $2 every 5th line (1-5 and 6-10) Please help me with awk Thank you

2. Shell Programming and Scripting

Average in awk

Hi I am looking for an awk script which can compute average of all the fields every 5th line. The file looks: A B C D E F G H I J K L M 1 18 13 14 12 14 13 11 12 12 15 15 15 2 17 17 13 13 13 12 12 11 12 14 15 14 3 16 16 12 12 12 11 11 12 11 16 14 13 4 15 15 11 11 11 12 11 12 11...

3. Shell Programming and Scripting

Partial average of a column with awk

Hello, Let's assume I have 100 files FILE_${m} (0<m<101). Each of them contains 100 lines and 10 columns. I'd like to get in a file called "result" the average value of column 3, ONLY between lines 11 and 17, in order to plot that average as a function of the parameter m. So far I can compute...

4. Shell Programming and Scripting

AWK novice - calculate the average

Hi, I have the following data in a file for example: P1 XXXXXXX.1 YYYYYYY.1 ZZZ.1 P1 XXXXXXX.2 YYYYYYY.2 ZZZ.2 P1 XXXXXXX.3 YYYYYYY.3 ZZZ.3 P1 XXXXXXX.4 YYYYYYY.4 ZZZ.4 P1 XXXXXXX.5 YYYYYYY.5 ZZZ.5 P1 XXXXXXX.6 YYYYYYY.6 ZZZ.6 P1 XXXXXXX.7 YYYYYYY.7 ZZZ.7 P1 XXXXXXX.8 YYYYYYY.8 ZZZ.8 P2...

5. Shell Programming and Scripting

Calculate Average AWK

I want to calculate the average line by line of some files with several lines on them, the files are identical, just want to average the 3rd columns of those files.:wall: Example file: File 1 001 0.046 0.667267 001 0.047 0.672028 001 0.048 0.656025 001 0.049 ...

6. Shell Programming and Scripting

Calculating average with awk

I need to find the average from a file like: data => BW:123 M:30 RTD:0 1 0 1 0 0 1 1 1 1 0 0 1 1 0' data => BW:123 N:30 RTD:0 1 0 1 0 0 1 1 1 1 0 0 1 1 0' data => BW:123 N:30 RTD:0 1 0 1 0 0 1 1 1 1 0 0 1 1 0' data => BW:123 N:30 RTD:0 1 0 1 0 0 1 1 1 1 0 0 1 1 0' data => BW:123 N:30 RTD:0 1...

7. Shell Programming and Scripting

awk or Bash: Cumulative average

For the data I would like to parse down and for each parsing I want a cumulative averaging, stored in an array that can be output. I.e. 546/NR = 546 (546+344)/NR=(546+344)/2 = etc. For N record input I want N values of the average (a block averaging effectively) Any...

8. Shell Programming and Scripting

awk to combine by field and average by another

In the below awk I am trying to combine all matching $4 into a single $5 (up to the -), and count the lines in $6 and average all values in $7. The awk is close but it seems to only be using the last line in the file and skipping all others. The posted input is a sample of the file that is over...

9. Shell Programming and Scripting

Average across multiple columns - awk

Hi forum members, I'm trying to get an average of multiple columns in a csv file using awk. A small example of my input data is as follows: cu,u3o8,au,ag -9,20,-9,3.6 0.005,30,-9,-9 0.005,50,10,3.44 0.021,-9,8,3.35 The following code seems to do most of what I want gawk -F","...

10. Shell Programming and Scripting

SMA (Single Moving Average) and Standard Deviation

Hello Team, I am using the following awk script to calculate the SMA (Single Moving Average) for an specific period but now I would like to include the standard deviation output. Could you please help me to modify this awk shell script awk -F, -v points=5 ' { a = $2; ...