topic Convert columns to rows in Operating System - HP-UX

Convert columns to rows

Christian Aguilar Varga — Fri, 24 Oct 2003 10:06:08 GMT

Hi,
I need a help!
I'm trying to convert columns to rows in the way following:

I want to convert:

18 Service_A 25
18 Service_B 38
19 Service_A 48
19 Service_B 68
19 Service_C 19
20 Service_A 47
20 Service_D 58

In:

Date Service_A Service_B Service_C Service_D
18 25 38 0 0
19 48 68 19 0
20 47 58 0 0

Please, Are there commands or scripts to get this goal.

Greetings,

Re: Convert columns to rows

Stuart Abramson_2 — Fri, 24 Oct 2003 11:31:21 GMT

Try something like this:

awk '{print $1}' file | sort -u | while read DATE
do
...printf "\n %3d" $DATE
...grep $DATE file | awk '{printf "%3d", $3}'
done

This isn't quite right, but it will get you off of the ground.

Re: Convert columns to rows

Graham Cameron_1 — Mon, 27 Oct 2003 08:30:54 GMT

Christian.
Typo in last line of your results, I think.
Try this:

cat file | awk '
{
results[$1,$2]=$3
services[$2]=1
dates [$1]=1
}
END {
printf "Date "
for (s in services)
printf ("%s ", s)
print
for (d in dates) {
printf ("%d ", d)
for (s in services)
printf ("%d ", results [d,s])
print
}
}'

-- Graham

Re: Convert columns to rows

Hein van den Heuvel — Mon, 27 Oct 2003 10:08:54 GMT

Sample Perl solution:

#!/bin/perl
while (<>) {
($date,$service,$value) = split;
$x{$date.$service} = $value;
$services{$service} += $value;
$dates{$date}++;
}
print "date ";
foreach $s (sort keys %services) { printf ("%15s", $s) };
foreach $d (sort keys %dates ) {
printf ("\n%5s", $d);
foreach $s (sort keys %services) {
printf ("%15d", $x{$d.$s} ) ;
}
}
print "\n";

and a little prettier:

#!/bin/perl
while (<>) {
($date,$service,$value) = split;
$x{$date.$service} = $value;
$services{$service} += $value;
$dates{$date}++;
}

$header = " Date ";
$seperator = "----- ";
$total = "Total ";

foreach $s (sort keys %services) {
$header .= sprintf ("%12s ", $s);
$seperator .= "------------ ";
$total .= sprintf ("%12d ", $services{$s});
}

print $header . "\n" . $seperator;
foreach $d (sort keys %dates ) {
printf ("\n%5s ", $d);
foreach $s (sort keys %services) {
printf ("%12d ", $x{$d.$s} ) ;
}
}
print "\n" . $seperator . "\n" . $total . "\n";

Giving:

Date Service_A Service_B Service_C Service_D
----- ------------ ------------ ------------ ------------
18 25 38 0 0
19 48 68 19 0
20 47 0 0 58
----- ------------ ------------ ------------ ------------
Total 120 106 19 58