COMPGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### How to transpose selected rows into columns and sort by one column

• Follow

```Hi,

I have the following output:
Number of executions               = 437
Number of compilations             = 1
Worst preparation time (ms)        = 1
Best preparation time (ms)         = 1
Rows deleted                       = 0

Number of executions               = 1
Number of compilations             = 1
Worst preparation time (ms)        = 4
Best preparation time (ms)         = 4
Rows deleted                       = 0

Number of executions               = 29
Number of compilations             = 1
Worst preparation time (ms)        = 1
Best preparation time (ms)         = 1
Rows deleted                       = 0

..........

How can I tranpose certain rows into columns and sort by one of the
column? Like the following which is sorted by :Number of executions":
Number of executions   Number of compilations     Rows deleted
437                    1                          0
29                     1                          0
29                     1                          0
....

Thanks
Weidong
```
 0

```Weidong wrote:
> Hi,
>
> I have the following output:
>  Number of executions               = 437
>  Number of compilations             = 1
>  Worst preparation time (ms)        = 1
>  Best preparation time (ms)         = 1
>  Rows deleted                       = 0
>
>  Number of executions               = 1
>  Number of compilations             = 1
>  Worst preparation time (ms)        = 4
>  Best preparation time (ms)         = 4
>  Rows deleted                       = 0
>
>
>  Number of executions               = 29
>  Number of compilations             = 1
>  Worst preparation time (ms)        = 1
>  Best preparation time (ms)         = 1
>  Rows deleted                       = 0
>
> .........
>
> How can I tranpose certain rows into columns and sort by one of the
> column? Like the following which is sorted by :Number of executions":
> Number of executions   Number of compilations     Rows deleted
> 437                    1                          0
> 29                     1                          0
> 29                     1                          0

awk '
/executions/{ E = \$NF }
/compilations/ { C = \$NF }
/deleted/ { A[++i] = sprintf("%10s%10s%10s", E, C, \$NF)  }
END { for (; i > 0; i--) print A[i] | \
"sort -r -k" COL "," COL }' COL=1 file

--
Regards,

---Robert
```
 0

```Robert Katz wrote:
> Weidong wrote:
>
>> Hi,
>>
>> I have the following output:
>>  Number of executions               = 437
>>  Number of compilations             = 1
>>  Worst preparation time (ms)        = 1
>>  Best preparation time (ms)         = 1
>>  Rows deleted                       = 0
>>
>>  Number of executions               = 1
>>  Number of compilations             = 1
>>  Worst preparation time (ms)        = 4
>>  Best preparation time (ms)         = 4
>>  Rows deleted                       = 0
>>
>>
>>  Number of executions               = 29
>>  Number of compilations             = 1
>>  Worst preparation time (ms)        = 1
>>  Best preparation time (ms)         = 1
>>  Rows deleted                       = 0
>>
>> .........
>>
>> How can I tranpose certain rows into columns and sort by one of the
>> column? Like the following which is sorted by :Number of executions":
>> Number of executions   Number of compilations     Rows deleted
>> 437                    1                          0
>> 29                     1                          0
>> 29                     1                          0
>
>
>
> awk '
> /executions/{ E = \$NF }
> /compilations/ { C = \$NF }
> /deleted/ { A[++i] = sprintf("%10s%10s%10s", E, C, \$NF)  }
> END { for (; i > 0; i--) print A[i] | \
>    "sort -r -k" COL "," COL }' COL=1 file

Why save it in an array instead of...

/executions/{ E = \$NF }
/compilations/ { C = \$NF }
/deleted/ { printf("%10s%10s%10s\n", E, C, \$NF) | "sort -nr" }

Wouldn't any buffering problem also accur in the END clause?

Janis
```
 0

```
Weidong wrote:
> Hi,
>
> I have the following output:
>  Number of executions               = 437
>  Number of compilations             = 1
>  Worst preparation time (ms)        = 1
>  Best preparation time (ms)         = 1
>  Rows deleted                       = 0
>
>  Number of executions               = 1
>  Number of compilations             = 1
>  Worst preparation time (ms)        = 4
>  Best preparation time (ms)         = 4
>  Rows deleted                       = 0
>
>
>  Number of executions               = 29
>  Number of compilations             = 1
>  Worst preparation time (ms)        = 1
>  Best preparation time (ms)         = 1
>  Rows deleted                       = 0
>
> .........
>
> How can I tranpose certain rows into columns and sort by one of the
> column? Like the following which is sorted by :Number of executions":
> Number of executions   Number of compilations     Rows deleted
> 437                    1                          0
> 29                     1                          0
> 29                     1                          0
> ...
>
> Thanks
> Weidong

If you really have a blank line between each record, then this will do
it all in gawk, including printing the headings and sorting the output:

gawk -vRS="" -F"\n" 'BEGIN{ fields = "1 3 5"; key = "3" }
{
for (i=1; i<=NF;i++) {
split(\$i,f,"=")
sub(/[[:blank:]]*\$/,"",f[1])
title[i]=f[1]
value[i]=f[2]+0
fmt[i]="%-"(length(title[i])+3)"s"
}
numflds = split(fields,flds," ")
lines[NR] = sprintf("%020s ",value[key])
for (i=1; i<=numflds; i++) {
lines[NR] = lines[NR] sprintf(fmt[flds[i]], value[flds[i]])
}
}
END {
for (i=1; i<=numflds; i++) {
printf fmt[flds[i]], title[flds[i]]
}
print ""
asort(lines)
for (i=1; i<=NR; i++) {
sub("[[:digit:]]* ","",lines[i])
print lines[i]
}
}'

Setting fields and key at the beginning obvious dictates which fields to
be printed and which key to sort on. The only thing it assumes about
field sizes is that your key fields values won't be more than 20
just seemed like I'd have to use it myself some day....

Ed.
```
 0

```Janis Papanagnou wrote:
> Robert Katz wrote:
>
>> Weidong wrote:
>>
>>> Hi,
>>>
>>> I have the following output:
>>>  Number of executions               = 437
>>>  Number of compilations             = 1
>>>  Worst preparation time (ms)        = 1
>>>  Best preparation time (ms)         = 1
>>>  Rows deleted                       = 0
>>>
>>>  Number of executions               = 1
>>>  Number of compilations             = 1
>>>  Worst preparation time (ms)        = 4
>>>  Best preparation time (ms)         = 4
>>>  Rows deleted                       = 0
>>>
>>>
>>>  Number of executions               = 29
>>>  Number of compilations             = 1
>>>  Worst preparation time (ms)        = 1
>>>  Best preparation time (ms)         = 1
>>>  Rows deleted                       = 0
>>>
>>> .........
>>>
>>> How can I tranpose certain rows into columns and sort by one of the
>>> column? Like the following which is sorted by :Number of executions":
>>> Number of executions   Number of compilations     Rows deleted
>>> 437                    1                          0
>>> 29                     1                          0
>>> 29                     1                          0
>>
>>
>>
>>
>> awk '
>> /executions/{ E = \$NF }
>> /compilations/ { C = \$NF }
>> /deleted/ { A[++i] = sprintf("%10s%10s%10s", E, C, \$NF)  }
>> END { for (; i > 0; i--) print A[i] | \
>>    "sort -r -k" COL "," COL }' COL=1 file
>
>
> Why save it in an array instead of...
>
> /executions/{ E = \$NF }
> /compilations/ { C = \$NF }
> /deleted/ { printf("%10s%10s%10s\n", E, C, \$NF) | "sort -nr" }

I didn't realize that would work.

>
> Wouldn't any buffering problem also accur in the END clause?

I don't know.

--
Regards,

---Robert
```
 0

```In comp.lang.awk Weidong <wdding@hotmail.com>:
> Hi,

> I have the following output:
> Number of executions               = 437
> Number of compilations             = 1
> Worst preparation time (ms)        = 1
> Best preparation time (ms)         = 1
> Rows deleted                       = 0

> Number of executions               = 1
> Number of compilations             = 1
> Worst preparation time (ms)        = 4
> Best preparation time (ms)         = 4
> Rows deleted                       = 0

> Number of executions               = 29
> Number of compilations             = 1
> Worst preparation time (ms)        = 1
> Best preparation time (ms)         = 1
> Rows deleted                       = 0
>
> .........

> How can I tranpose certain rows into columns and sort by one of the
> column? Like the following which is sorted by :Number of executions":
> Number of executions   Number of compilations     Rows deleted
> 437                    1                          0
> 29                     1                          0
> 29                     1                          0
> ...

awk '/executions/||/compilations/||/deleted/{
printf "%10s",\$NF}NF==0{print ""}END{print ""}' infile

Should be on one line, left piping through 'sort' and adding the

--
Michael Heiming (X-PGP-Sig > GPG-Key ID: EDD27B94)
mail: echo zvpunry@urvzvat.qr | perl -pe 'y/a-z/n-za-m/'
#bofh excuse 446: Mailer-daemon is busy burning your message
in hell.
```
 0

```
Ed Morton wrote:
>
>
> Weidong wrote:
>
>> Hi,
>>
>> I have the following output:
>>  Number of executions               = 437
>>  Number of compilations             = 1
>>  Worst preparation time (ms)        = 1
>>  Best preparation time (ms)         = 1
>>  Rows deleted                       = 0
>>
>>  Number of executions               = 1
>>  Number of compilations             = 1
>>  Worst preparation time (ms)        = 4
>>  Best preparation time (ms)         = 4
>>  Rows deleted                       = 0
>>
>>
>>  Number of executions               = 29
>>  Number of compilations             = 1
>>  Worst preparation time (ms)        = 1
>>  Best preparation time (ms)         = 1
>>  Rows deleted                       = 0
>>
>> .........
>>
>> How can I tranpose certain rows into columns and sort by one of the
>> column? Like the following which is sorted by :Number of executions":
>> Number of executions   Number of compilations     Rows deleted
>> 437                    1                          0
>> 29                     1                          0
>> 29                     1                          0
>> ...
>>
>> Thanks
>> Weidong
>
>
> If you really have a blank line between each record, then this will do
> it all in gawk, including printing the headings and sorting the output:
>
> gawk -vRS="" -F"\n" 'BEGIN{ fields = "1 3 5"; key = "3" }
> {
>         for (i=1; i<=NF;i++) {
>                 split(\$i,f,"=")
>                 sub(/[[:blank:]]*\$/,"",f[1])
>                 title[i]=f[1]
>                 value[i]=f[2]+0
>                 fmt[i]="%-"(length(title[i])+3)"s"
>         }
>         numflds = split(fields,flds," ")
>         lines[NR] = sprintf("%020s ",value[key])
>         for (i=1; i<=numflds; i++) {
>                 lines[NR] = lines[NR] sprintf(fmt[flds[i]], value[flds[i]])
>         }
> }
> END {
>         for (i=1; i<=numflds; i++) {
>                 printf fmt[flds[i]], title[flds[i]]
>         }
>         print ""
>         asort(lines)
>         for (i=1; i<=NR; i++) {
>                 sub("[[:digit:]]* ","",lines[i])
>                 print lines[i]
>         }
> }'
>
> Setting fields and key at the beginning obvious dictates which fields to
> be printed and which key to sort on. The only thing it assumes about
> field sizes is that your key fields values won't be more than 20
> just seemed like I'd have to use it myself some day....
>
>     Ed.

It occurs to me the above isn't exactly obvious in what it's doing or
why, so here's a commented version (with one tweak - moving setting of
"flds" to the BEGIN section):

gawk -vRS="" -F"\n" 'BEGIN{ fields = "1 2 5"; key = "1"
numflds = split(fields,flds," ")
}
{
for (i=1; i<=NF;i++) {
split(\$i,f,"=")
# Get rid of all spaces from the end of the title text
sub(/[[:blank:]]*\$/,"",f[1])
title[i]=f[1]
# Get rid of all spaces from the value field
value[i]=f[2]+0
# Determine the width for this column based on the width
# of the title text plus 3 for spacing. Left-justify (%-).
fmt[i]="%-"(length(title[i])+3)"s"
}
# We will want to sort on the key column so we need to create a
# string at the start of each line to sort on later. Take the key
# columns value and pad it with zeros up to 20 chars followed by
# a space to separate it fromthe first real column. Conversion of
# "7" to "0007" and "17" to "0017" is necessary because asort()
# is alphabetical not numerical so all numeric fields must be the
# same width to compare alphabetically.
lines[NR] = sprintf("%020s ",value[key])

# Now add the real columns, formatted as determined earlier.
for (i=1; i<=numflds; i++) {
lines[NR] = lines[NR] sprintf(fmt[flds[i]], value[flds[i]])
}
}
END {
# Print the title line
for (i=1; i<=numflds; i++) {
printf fmt[flds[i]], title[flds[i]]
}
print ""
# Sort the lines alphabetically, i.e. by the value of the key column
# added above to the front of each line.
asort(lines)
# Print each line
for (i=1; i<=NR; i++) {
# strip out the first numeric value, the key value added above
sub("[[:digit:]]* ","",lines[i])
print lines[i]
}
}'

Regards,

Ed.
```
 0

6 Replies
560 Views

Similiar Articles:

7/20/2012 12:43:13 PM