AWK for data smoothing

I need some help to smooth some data with awk olny of column 4 and next. This is a data example:

``````Date;time;Time_ms;A;B;C;D
23.11.2012;15:03:00;41236627696;1;2;2;3
23.11.2012;15:04:00;41236628391;2;3;3;11
23.11.2012;15:06:00;41236629097;1;23;7;15
24.11.2012;15:07:00;41236627696;1;4;5;3
24.11.2012;15:08:00;41236628391;3;12;1;2
24.11.2012;15:09:00;41236629097;2;23;71;15;8
23.11.2012;15:10:00;41236627696;7;1;2;2;3
23.11.2012;15:11:00;41236628391;2;3;12;1;
23.11.2012;15:12:00;41236629097;22;2;7;15
``````

The output should not modify Date;time;Time_ms and print the average of A with the A field of n row before and the A field of n row after. The same should be done for the B, C, D... column. For example if n=1 the second row will be:

``````23.11.2012;15:04:00;41236628391;(1+2+1)/3;(2+3+23)/3;(2+3+7)/3;(3+11+15)/3
``````

Maybe something like this can be a startpoint

``````BEGIN { WIDTH=3 }

{       DATA[(++N)%WIDTH]=\$0    }

(N>=WIDTH) {
V=0
for(X=(N+1); X<=(N+WIDTH); X++)
V+=DATA[X%WIDTH];

print V/WIDTH;
}
``````

Source

For statistical data processing, R is usually easier to use.

It is easier to read all the data into memory. Awk isn't the greatest language for that (though it is certainly possible). Here's a quickie Python script.

``````#!/usr/bin/env python
import sys
n = int(sys.argv[1])     # the smooth parameter n must be passed as the first argument to the script
def split_line(line):  # split line into first 3 fields as string, then list of numbers
fields = line[:-1].split(";")
return [";".join(fields[:3])] + map(float, fields[3:7])
def avg(i, j):
return (rows[i-n][j] + rows[i][j] + rows[i+n][j]) / 3
for i in xrange(n, len(rows) - n):
print ";".join([rows[i][0]] + [str(avg(i, j-2)) for j in xrange(3, 7)])
``````

If your data is really huge, here's a script that I think does what you're asking. It reads 2*n+1 lines, stores the values in `prev[2*n]` through `prev[1]` plus `\$0`, then prints the average for the (n+1)th line.

``````awk -F ';' -v OFS=';' -v n="\${1-1}" '
function avg(i) { return (prev[2*n, i] + prev[n, i] + \$i) / 3; }
NR == 1 { print; next }         # title line: print and skip the rest
NR >= 2*n+2 {
# fourth line on: print the average values from the past 3 lines
# with the labels from the previous line
print labels[n], avg(4), avg(5), avg(6), avg(7);
}
{
# shift the saved averages by 1 position
for (i=4; i<=7; i++) {
for (k=2*n; k>1; k--) prev[k, i] = prev[k-1, i];
prev[1, i] = \$i;
}
# save the labels of this line to print in the next round
for (k=n; k>1; k--) labels[k] = labels[k-1];
labels[1] = \$1 ";" \$2 ";" \$3;
}
'
``````
• Bonjour @Gilles, I tried you awk script but as reslult I obtain a file that is the copy of the original... no vaerage is made. No errors are showed on terminal. I changed only these two things : FS=OFS=";" RS="\r\n" . Have you tried the script? Is working well with you? – Ludovico Feb 13 '13 at 12:23
• @Ludovico Sorry, I made some tweaks in my browser after I'd tested the code. Here are working versions. – Gilles Feb 13 '13 at 12:45
• I tried your script in python... Works fine!! Thank you!!! Works fine on the example but fails if there is a different number of column in the input. My input files doesn't have always the same number of column. How Can I calculate the number of culumn and execute the average on column from 4 to last column? The script fails also if one field is empty; Is it possible to consider empty fields as 0? The script in AWK don't work, but maybe I made some mistake... I'll verify – Ludovico Feb 13 '13 at 20:50

this is the script that I wrote after that I found this

``````#!/usr/bin/bash
awk 'BEGIN { FS=OFS=";"
RS="\n"}
{
gsub(/\n/,"",\$0)
if (max_nf<NF)
max_nf=NF
max_nr=NR
for(x=1; x<=NF; ++x)
vector[NR,x]=\$x
}
END {
row=1
printf vector[row,1] OFS vector[row,2] OFS vector[row,3]
for(col=4; col<max_nf; ++col)
printf OFS "Average(" vector[row,col] ")";
printf ORS

for(row=2; row<max_nr; ++row) {
printf vector[row,1] OFS vector[row,2] OFS vector[row,3]
for(col=4; col<max_nf; ++col)
printf OFS (vector[row,col]+vector[row-1,col]+vector[row+1,col])/3 ;
printf ORS
}
}' File_IN.csv>File_OUT.csv
``````

This script store the csv file a two dimensional array, then print the first line. For the column >4 print average(HEADER). From the second row to the end of file print the first, the second and the third column and the average of column >4. Even if the python script of @gilles works fine I chose this script as answer because uses AWK as asked.