awk script problem

Hi All,

I have the following input data:

That I'd like to look like this ($2 is the column I'd like it to appear in) where the entries are grouped by date:

The code I have at present is:

awk 'BEGIN {}
{
        dt = $1
        if (dt == dt_prev) {
                pp[$2] = $3
        } else {
                if (dt_prev) {
                        print pp[1]","pp[2]","pp[3]","pp[4]","dt_prev
                }
        dt_prev = dt
        for (i in pp) pp = 0
        }
}
END {
        print pp[1]","pp[2]","pp[3]","pp[4]","dt
}' $1

I think I'm pretty close but there's a flaw in the above code - any help would be much appreciated

Thanks

Another solution.

awk ' {  B[$2]=$3;
 for(i=0;i<=4;i++) { if (!B) B=0; }    
 A[$1]=B[1]","B[2]","B[3]","B[4]; } 
END { for (i in A) { print A","i; } }' file

Thanks Dennis,

Unfortunately my input comes out like this when I try your code:

Scratch that! It works Thanks!!

I just misread the output - it's not sorted and that confused me (not difficult )

Brill, thanks again

Hi Pondlife,

Did you test the script throughly , i think the group by is missing in the script suggested by Dennis(you can observe the duplicate values in the fourthe column).

Output from the script(input taken from your first script)

 
0,0,151,17004,2008-02-02
36,0,151,17004,2008-02-03
95,0,168,7,2008-02-05
383,88,168,7,2008-02-06
86,88,168,7,2008-02-07
86,88,168,33,2008-02-09

You can try something like this:

awk ' {  if(pr==$1){B[$2]=$3;for(i=0;i<=4;i++) { if(!B) B=0; }} else {for(i=0;i<=4;i++){B=0}; B[$2]=$3};pr=$1; A[$1]=B[1]","B[2]","B[3]","B[4]; }
        END { for (i in A) { print A","i; } }' input_file

Corrected it.

awk ' {  if(!A[$1]) { B[1]=B[2]=B[3]=B[4]=0; }
           B[$2]=$3;
           for(i=0;i<=4;i++) { if (!B) B=0; }
          A[$1]=B[1]","B[2]","B[3]","B[4]; }
         END { for (i in A) { print A","i; } }' file

output:

0,0,151,17004,2008-02-02
36,0,0,0,2008-02-03
95,0,168,7,2008-02-05
383,88,0,0,2008-02-06
86,0,0,0,2008-02-07
0,0,0,33,2008-02-09

This should work

# awk 'END{for(x in a){z=w;for(y=0;++y<5;){z=z ((b[x y])?b[x y]:0)","}print z x}}{a[$1];b[$1 $2]=$NF}' infile
0,0,151,17004,2008-02-02
36,0,0,0,2008-02-03
95,0,168,7,2008-02-05
383,88,0,0,2008-02-06
86,0,0,0,2008-02-07
0,0,0,33,2008-02-09

You guys are awsome

Thanks

using perl...

perl -wlane '$,="," ; @a=(0,0,0,0),$pr=$F[0] if ($. == 1); 
if ($F[0] eq $pr) {$a[$F[1]-1]=$F[2]} 
else {print @a,$pr ;@a=(0,0,0,0);$pr=$F[0];$a[$F[1]-1]=$F[2]};
END{print @a,$pr}' infile.txt

;);)