unstack columns 1

[KingVodka]

Hi,

I hope somebody can help me refining the awk code below. I use this code to unstack the following columns. It is relatively straightforwardatientnumber, time and observation columns
1 0 0
1 10 0
1 45 4
1 75 8
1 105 9
1 122 9
1 165 9
1 210 8
1 270 8
1 330 4
1 390 4
1 450 4
1 490 3
2 0 0
2 10 5
2 45 7
2 75 7
2 105 8
2 130 4
2 165 2
2 210 0
2 270 1
2 330 0
2 390 1
2 450 1
2 490 0
3 0 0
3 10 7
3 45 8
3 90 8
3 130 8
3 165 7
3 210 6
3 270 6
3 330 4
3 390 4
3 450 2
4 0 0
4 10 8
4 30 8
4 60 8
4 90 8
4 130 7
4 165 8
4 210 8
4 270 7
4 330 7
4 390 7
4 450 5

I would like to have the following output:
1 1 2 2 3 3 4 4
0 0 0 0 0 0 0 0
10 0 10 5 10 7 10 8
45 4 45 7 45 8 30 8
75 8 75 7 90 8 60 8
105 9 105 8 130 8 90 8
122 9 130 4 165 7 130 7
165 9 165 2 210 6 165 8
210 8 210 0 270 6 210 8
270 8 270 1 330 4 270 7
330 4 330 0 390 4 330 7
390 4 390 1 450 2 390 7
450 4 450 1 450 5
490 3 490 0
But using this code:
{if ($1 in col) {
cnt=++col[$1];
} else {
col[$1]=1;
row[0]=row[0] " " $1 " " $1;
cnt=1;
};
row[cnt]=row[cnt] " "$2 " "$3;
if (cnt>max)
max=cnt;
} END {
for(x=0;x<=max;x++)
print row[x]
}

yields this

1 1 2 2 3 3 4 4
0 0 0 0 0 0 0 0
10 0 10 5 10 7 10 8
45 4 45 7 45 8 30 8
75 8 75 7 90 8 60 8
105 9 105 8 130 8 90 8
122 9 130 4 165 7 130 7
165 9 165 2 210 6 165 8
210 8 210 0 270 6 210 8
270 8 270 1 330 4 270 7
330 4 330 0 390 4 330 7
390 4 390 1 450 2 390 7
450 4 450 1 450 5
490 3 490 0

where time point 450 and observation 5 (last pair column 5,6) are placed wrong. Should be in column 7 and 8.

Suggestions would be very helpful.

best regards,

Ashraf

 

[Annihilannic]

How about this:

BEGIN { r=0; c=0 }
$1 != prev { c++; r=0 }
{
        prev=$1
        a[++r,c]=$2
        b[r,c]=$3
        if (r > maxr) maxr=r
        if (c > maxc) maxc=c
}
END {
        for (c=1;c<=maxc;c++) { printf "%d %d ",c,c }
        print ""
        for(r=1;r<=maxr;r++) {
                for (c=1;c<=maxc;c++) {
                        printf "%s %s ",a[r,c],b[r,c]
                }
                print ""
        }
}

Annihilannic.

 

[KingVodka]

Thank you for your reply. Your code generates the same "wrong" output as the one I use.

Best regards,

Ashraf

 

[Annihilannic]

No, it doesn't, at least, not on my system:

[tt]1 1 2 2 3 3 4 4
0 0 0 0 0 0 0 0
10 0 10 5 10 7 10 8
45 4 45 7 45 8 30 8
75 8 75 7 90 8 60 8
105 9 105 8 130 8 90 8
122 9 130 4 165 7 130 7
165 9 165 2 210 6 165 8
210 8 210 0 270 6 210 8
270 8 270 1 330 4 270 7
330 4 330 0 390 4 330 7
390 4 390 1 450 2 390 7
450 4 450 1 450 5
490 3 490 0[/tt]

Note the extra spaces before "450 5". This is presuming that you require one space separator before each column?

Annihilannic.