rearrange data on a file (NOT a direct transpose)

Question

I have a file (more than 2.5k lines) like this:

NAME YEAR A B C
JOHN Y1 10,00 19,00 65,00
JOHN Y2 11,00 23,00 64,00
JOHN Y3 12,00 33,00 34,00
JOHN Y4 13,00 34,00 32,00
PAUL Y1 14,00 43,00 23,00
PAUL Y2 15,00 90,00 34,00
PAUL Y3 16,00 32,00 56,00
PAUL Y4 20,00 45,00 65,00
RINGO Y1 25,00 60,00 87,00
RINGO Y2 24,00 30,00 23,00
RINGO Y3 31,00 20,00 54,00
RINGO Y4 75,00 10,00 12,00

As you can see, each name repeats 4 times (4 lines) in order to "store" the 4 years values, and for each year there are 3 values (A, B and C).

I need to rearrange the data, so that each name will be shown in just ONE LINE. So the 4 years that originaly are shown in lines must be shown in new columns, like this:

NAME A/Y1 A/Y2 A/Y3 A/Y4 B/Y1 B/Y2 B/Y3 B/Y4 C/Y1 C/Y2 C/Y3 C/Y4
JOHN 10,00 11,00 12,00 13,00 19,00 23,00 33,00 34,00 65,00 64,00 34,00 32,00
PAUL 14,00 15,00 16,00 20,00 43,00 90,00 32,00 45,00 23,00 34,00 56,00 65,00
RINGO 25,00 24,00 31,00 75,00 60,00 30,00 20,00 10,00 87,00 23,00 54,00 12,00

Also, an acceptable output format could be:

NAME Y1/A Y1/B Y1/C Y2/A Y2/B Y2/C Y3/A Y3/B Y3/C Y4/A Y4/B Y4/C

I'm not shure which one would be "easier" to be implemented but, both output formats are OK.

As far as I can see, its not a "direct transpose", and I have not found any similar question, and thats why I made the question again with more details.

Answer 1

Using GNU awk for true multi-dimensional arrays:

$ cat tst.awk
NR==1 { split($0,hdr); next }
{
    idx = (NR-2)%4+1
    val[idx][0]
    split($0,val[idx])
}
NR==5 {
    printf "%s", hdr[1]
    for (j=3; j in hdr; j++) {
        for (i=1; i<=idx; i++) {
            printf "%s%s", OFS, hdr[j]"/"val[i][2]
        }
    }
    print ""
}
idx==4 {
    printf "%s", $1
    for (j=3; j<=NF; j++) {
        for (i=1; i<=idx; i++) {
            printf "%s%s", OFS, val[i][j]
        }
    }
    print ""
}

$ awk -f tst.awk file
NAME A/Y1 A/Y2 A/Y3 A/Y4 B/Y1 B/Y2 B/Y3 B/Y4 C/Y1 C/Y2 C/Y3 C/Y4
JOHN 10,00 11,00 12,00 13,00 19,00 23,00 33,00 34,00 65,00 64,00 34,00 32,00
PAUL 14,00 15,00 16,00 20,00 43,00 90,00 32,00 45,00 23,00 34,00 56,00 65,00
RINGO 25,00 24,00 31,00 75,00 60,00 30,00 20,00 10,00 87,00 23,00 54,00 12,00