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/
Before you buy.



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/
Before you buy.



Fri, 30 Aug 2002 03:00:00 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Using expand and filling rows and columns with sums

2. Summing columns of matrices

3. Help Summing a Column of Times

4. Summing 2 columns to a 3rd in a browsebox

5. sum columns on tbrowse

6. how to sum elements of a column in a mixed matrix

7. Help: using hidden columns with columns resizable in a list box

8. Column selection in multi-column report.

9. CW5: Column selection in multi-column report.

10. Plotting 1st column vs 2nd column in Excel from LabVIEW

11. New to FORTRAN - Reformatting One Column to Multiple Columns

12. how do i convert a multi column into a single column in the output in fortran

 

 
Powered by phpBB® Forum Software