awk transpose specific row to column and group them-CodePudding

I have the below data in a stdout:

09:13:32 19.2 cpu(1)
09:13:32 15.6 cpu(2)
09:13:32 16.7 cpu(3)
09:13:32 17.1 cpu(6)
09:13:32 17.1 cpu(7)
09:13:32 16.9 cpu(8)
09:13:32 16.7 cpu(9)
09:13:39 13.0 cpu(1)
09:13:39 9.2 cpu(2)
09:13:39 9.1 cpu(3)
09:13:39 7.1 cpu(6)
09:13:39 27.1 cpu(7)
09:13:39 46.9 cpu(8)
09:13:39 36.7 cpu(9)

Trying to convert this to something like below.

['Time', 'cpu(1)', 'cpu(2)', 'cpu(3)', 'cpu(6)', 'cpu(7)', 'cpu(8)', 'cpu(9)'],
['09:13:32',  19.2, 15.6, 16.7, 17.1, 17.1, 16.9, 16.7],
['09:13:39', 13.0, 9.2, 9.1, 7.1, 27.1, 46.9, 36.7]

In other words, I need the original data to be aligned with Google visualization line chart format as stated here: https://developers.google.com/chart/interactive/docs/gallery/linechart

I am trying to achieve this using awk and need some inputs.

awk '{ for(N=1; N<=NF; N =2) print $N, $(N 1); }' | awk 'BEGIN{q="\047"; printf "["q"Time"q","q"CPU"q"],"}/master/{q="\047"; printf "["q$10q"," $3"],"}' | sed 's/,$//'"

Note: I can change the original data columns like below

Time CPU(%) CPU Number

CPU(%) CPU Number Time

CodePudding user response：

Using any awk for any number of times and any number of cpus, and will work even if you don't have data for some time cpu combinations, as long as the input isn't so massive that it can't all fit in memory:

$ cat tst.awk
BEGIN {
    OFS = ", "
}
!seenTimes[$1]   {
    times[  numTimes] = $1
}
!seenCpus[$3]   {
    cpus[  numCpus] = $3
}
{
    vals[$1,$3] = $2
}
END {
    printf "[\047%s\047%s", "Time", OFS
    for ( cpuNr=1; cpuNr<=numCpus; cpuNr   ) {
        cpu = cpus[cpuNr]
        printf "\047%s\047%s", cpu, (cpuNr<numCpus ? OFS : "]")
    }

    for ( timeNr=1; timeNr<=numTimes; timeNr   ) {
        time = times[timeNr]
        printf ",%s[\047%s\047%s", ORS, time, OFS
        for ( cpuNr=1; cpuNr<=numCpus; cpuNr   ) {
            cpu = cpus[cpuNr]
            val = vals[time,cpu]
            printf "%s%s", val, (cpuNr<numCpus ? OFS : "]")
        }
    }
    print ""
}

$ awk -f tst.awk file
['Time', 'cpu(1)', 'cpu(2)', 'cpu(3)', 'cpu(6)', 'cpu(7)', 'cpu(8)', 'cpu(9)'],
['09:13:32', 19.2, 15.6, 16.7, 17.1, 17.1, 16.9, 16.7],
['09:13:39', 13.0, 9.2, 9.1, 7.1, 27.1, 46.9, 36.7]