sum all columns based on column 1
Author Message
sum all columns based on column 1

Hallo,

I have some sorted files.  The total columns for every lines vary. Lines
also varies for every file.  How can I add all columns except column 1
if column 1 is the same.

sample input:

Brix    1  2  2
James   3  4
James   5  1  4
Jenny   1  0  4  6  4
Pete    0  5
Pete    2  1  0  3

Sample output:

Brix    1  2  2
James   8  5  4
Jenny   1  0  4  6  4
Pete    2  6  0  3

Thank you.

jen

Sent via Deja.com http://www.*-*-*.com/

Mon, 26 Aug 2002 03:00:00 GMT
sum all columns based on column 1

Quote:

>Hallo,

>I have some sorted files.  The total columns for every lines vary. Lines
>also varies for every file.  How can I add all columns except column 1
>if column 1 is the same.

>sample input:

>Brix    1  2  2
>James   3  4
>James   5  1  4
>Jenny   1  0  4  6  4
>Pete    0  5
>Pete    2  1  0  3

>Sample output:

>Brix    1  2  2
>James   8  5  4
>Jenny   1  0  4  6  4
>Pete    2  6  0  3

Use awk's associative array capability, then print the arrays in an end
section if the file is not sorted.

Your file seems to be sorted, so you can do it on the fly.

awk 'NR==1{prior=\$1;for(i=2;i<=NF;i++){a[i]=\$i};next}
prior==\$1{for(i=2;i<=NF;i++){a[i]+=\$i};next}
{printf("%-6s",prior);
for(i=2;a[i]!="";i++){printf("%3d",a[i]);a[i]=""}; print "";
prior=\$1;for(i=2;i<=NF;i++){a[i]=\$i}}
END {printf("%-6s",prior);
for(i=2;a[i]!="";i++){printf("%3d",a[i])};
print ""}' infile

This produces the desired output, but I think the fields might
be better if they were wider.  Adjust as required.

I'll leave it for you or someone else to show how to do it if the
file isn't sorted.  :-)  It just becomes a bit trickier.  :-)

Chuck Demas
Needham, Mass.

--
Eat Healthy    |   _ _   | Nothing would be done at all,

Die Anyway     |    v    | That no one could find fault with it.

Mon, 26 Aug 2002 03:00:00 GMT
sum all columns based on column 1

Quote:

>Hallo,

>I have some sorted files.  The total columns for every lines vary. Lines
>also varies for every file.  How can I add all columns except column 1
>if column 1 is the same.

>sample input:

>Brix    1  2  2
>James   3  4
>James   5  1  4
>Jenny   1  0  4  6  4
>Pete    0  5
>Pete    2  1  0  3

>Sample output:

>Brix    1  2  2
>James   8  5  4
>Jenny   1  0  4  6  4
>Pete    2  6  0  3

>Thank you.

>jen

Try:

awk ' BEGIN { name = ""
previous = "" }
{ if (\$1 == name) { m = split(previous,a)
n = split(\$0,b)
if (m < n) m=n
previous = \$1
for (i=2; i<=m; i++)
previous = previous " " a[i]+b[i]
}
else { if (name != "") print previous
previous = \$0 }
name = \$1 }
END { print previous }'

Thomas baruchel ;-)

Mon, 26 Aug 2002 03:00:00 GMT
sum all columns based on column 1

Quote:

>I have some sorted files.  The total columns for every lines vary. Lines
>also varies for every file.  How can I add all columns except column 1
>if column 1 is the same.
>sample input:

>Brix    1  2  2
>James   3  4
>James   5  1  4
>Jenny   1  0  4  6  4
>Pete    0  5
>Pete    2  1  0  3

>Sample output:
>Brix    1  2  2
>James   8  5  4
>Jenny   1  0  4  6  4
>Pete    2  6  0  3

Use awk's associative array
{ for (i=2;i<=NF,i++) a[\$1 "," i]+=\$i}
END {
for (i in a) print i, a[i]}

I leave it as an exercise for the user to 'join' the lines

Mark
--
Mark Katz
Mark-it, London - IT/Internet solutions that work
Tel: (44) 20-8731 7516, Fax: (44) 20-8458 9554
For information about ISPC/ITE - see http://www.e-tabs.com

Mon, 26 Aug 2002 03:00:00 GMT
sum all columns based on column 1
Hi,

A million thanks to you Charles, Thomas and Mark for your help.

Take care.

Regards,
jen

Sent via Deja.com http://www.deja.com/