aydj
December 11, 2017, 10:41am
1
I am trying to format the table below to the output
input:
cand week sub1 sub2 sub3 sub4
joe 1 94.19 70.99 43.93 60.14
joe 2 94.07 51.02 41.07 38.92
joe 3 26.24 30.95 44.56 67.67
joe 4 72.36 60.92 40.78 83.25
joe 5 51 70.01 44.66 82.22
jane 1 10.00 51.75 24.72 79.97
jane 2 11.01 94.73 24.28 42.35
jane 3 92.70 55.80 23.11 10.83
jane 4 88.88 22.74 22.09 71.56
jane 5 100.0 56.7 88.79 98.01
output
1 2 3 4 5
joe sub1 94.19 94.07 26.24 72.36 51
joe sub2 70.99 51.02 30.95 60.92 70.01
joe sub3 43.93 41.07 44.56 40.78 44.66
joe sub4 60.14 38.92 67.67 83.25 82.22
jane sub1 10.00 11.01 92.70 88.88 100.0
jane sub2 51.75 94.73 55.80 22.74 56.7
jane sub3 24.72 24.28 23.11 22.09 88.79
jane sub4 79.97 42.35 10.83 71.56 98.01
tried this, but not working:
awk '
{
for (p=3; p<=NF; p++) {
a[$1][$2][p] = $p
}
}
END {
for (i in a){
for (j in a){
for (k in a[j]) {
print a[j][k]
}
}
}
}'
RudiC
December 11, 2017, 11:14am
2
Looks like your awk version (gawk?) allows for real multidimensional arrays - mine doesn't. Still, you might use this as a starting point:
awk '
function PRT() {for (i=3; i<=MXC; i++) {printf "%s\t%s", LAST, SUB
for (j=1; j<=LNR; j++) printf "\t%s", DATA[i,j]
printf RS
}
}
NR == 1 {for (i=3; i<=NF; i++) SUB = $i
MXC = NF
next
}
LAST &&
$1 != LAST {if (!HD) {printf "\t"
for (i=1; i<=LNR; i++) printf "\t%s", i
printf RS
}
HD = 1
PRT()
}
END {PRT()
}
{for (i=3; i<=NF; i++) DATA [i,$2] = $i
LAST = $1
LNR = $2
}
' file
Please be aware that Jane's sub3's week 5 value (88 79) is interpreted as TWO columns and thus disturbes the corresponding output .
aydj
December 13, 2017, 1:01pm
4
rudic:
Looks like your awk version (gawk?) allows for real multidimensional arrays - mine doesn't. Still, you might use this as a starting point:
awk '
function PRT() {for (i=3; i<=MXC; i++) {printf "%s\t%s", LAST, SUB
for (j=1; j<=LNR; j++) printf "\t%s", DATA[i,j]
printf RS
}
}
NR == 1 {for (i=3; i<=NF; i++) SUB = $i
MXC = NF
next
}
LAST &&
$1 != LAST {if (!HD) {printf "\t"
for (i=1; i<=LNR; i++) printf "\t%s", i
printf RS
}
HD = 1
PRT()
}
END {PRT()
}
{for (i=3; i<=NF; i++) DATA [i,$2] = $i
LAST = $1
LNR = $2
}
' file
Please be aware that Jane's sub3's week 5 value (88 79) is interpreted as TWO columns and thus disturbes the corresponding output .
Can anyone suggest GNU Awk 4.2.0 implementation?
In what way does the code RudiC suggested not work with gawk
version 4.2.0?
What diagnostics does it produce when you run it?
Does it produce output, but the wrong output? If so, show us the output it produced and the output you were hoping to get!
Or, are you saying it works perfectly, but you want us to spend time writing a non-portable version that will work only on the version of gawk
that you're running?
aydj
December 14, 2017, 6:01am
6
In what way does the code RudiC suggested not work with gawk
version 4.2.0?
What diagnostics does it produce when you run it?
Does it produce output, but the wrong output? If so, show us the output it produced and the output you were hoping to get!
Or, are you saying it works perfectly, but you want us to spend time writing a non-portable version that will work only on the version of gawk
that you're running?
It does not work when the week is changed to dates, like below:
cand date sub1 sub2 sub3 sub4
joe 10122017 94.19 70.99 43.93 60.14
joe 11122017 94.07 51.02 41.07 38.92
joe 12122017 26.24 30.95 44.56 67.67
joe 13122017 72.36 60.92 40.78 83.25
joe 14122017 51 70.01 44.66 82.22
jane 10122017 10.00 51.75 24.72 79.97
jane 11122017 11.01 94.73 24.28 42.35
jane 12122017 92.70 55.80 23.11 10.83
jane 13122017 88.88 22.74 22.09 71.56
jane 14122017 100.0 56.7 88.79 98.01
RudiC
December 14, 2017, 6:04am
7
And how do you hope GNU Awk 4.2.0
will handle that? And, what should the output look like?
aydj
December 14, 2017, 6:11am
8
Output will look like this:
10122017 11122017 2122017 13122017 14122017
joe sub1 94.19 94.07 26.24 72.36 51
joe sub2 70.99 51.02 30.95 60.92 70.01
joe sub3 43.93 41.07 44.56 40.78 44.66
joe sub4 60.14 38.92 67.67 83.25 82.22
jane sub1 10.00 11.01 92.70 88.88 100.0
jane sub2 51.75 94.73 55.80 22.74 56.7
jane sub3 24.72 24.28 23.11 22.09 88.79
jane sub4 79.97 42.35 10.83 71.56 98.01