Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

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
print sys.stdin.readline()  # print the header line
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])
rows = map(split_line, sys.stdin.readlines())
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;
    }
'
share|improve this answer
    
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
up vote 0 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.