Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

I want to sum the values of the rows of my data that have the same ID in the first column. My data looks like

data.txt

Id    a    b    c    d    e
1     1    2    3    4    5
1     2    3    4    5    6
1     3    4    5    6    7
2     4    5    6    7    8
2     5    6    7    8    9
2     6    7    8    9    10
3     7    8    9   10    11
3     8    9    10  11    12
3     9    10   11  12    13
3     10   11   12  13    14
4     11   12   13  14    15
4     12   13   14  15    16
5     13   14   15  16    17
5     14   15   16  17    18

Required results

out.txt

Id    a     b   c   d   e
1     6     9   12  15  18
2     15    18  21  24  27
3     34    38  42  46  50
4     23    25  27  29  31
5     27    29  31  33  35
share|improve this question
    
The output values for row #3 are incorrect. –  jlliagre Jan 3 at 12:00
add comment

3 Answers 3

This GNU awk script should do the job:

$ awk 'NR==1 { size=NF;$1=$1;OFS="\t";print;next } 
{ for(i=2;i<=NF;i++) {id[$1]=$1; record[$1,i-1]+=$i} } 
END { PROCINFO["sorted_in"]="@ind_num_asc"
  for(i in id){ printf("%s\t",i);
    for(j=1;j<size;j++) printf("%s\t",record[i,j]);
    printf("\n");
  }
} ' data.txt > out.txt
$ cat out.txt
Id  a   b   c   d   e
1   6   9   12  15  18  
2   15  18  21  24  27  
3   34  38  42  46  50  
4   23  25  27  29  31  
5   27  29  31  33  35

Edit:

Here is a version that preserves the original row ordering instead of sorting the ids:

$ awk 'NR==1 { size=NF;$1=$1;OFS="\t";print;next }
{ if(o[$1]==0) o[$1]=NR
  for(i=2;i<=NF;i++) {record[$1,i-1]+=$i} }
END { PROCINFO["sorted_in"]="@val_num_asc"
  for(i in o){ printf("%s\t",i)
    for(j=1;j<size;j++) printf("%s\t",record[i,j])
    printf("\n") }
}'
share|improve this answer
    
Nice one, +1. What's the PROCINFO for? You don't seem to be using it. Is it to sort the ids? What if they're not numerical as in the simple example in the OP? –  terdon Jan 3 at 12:06
    
@terdon PROCINFO is indeed here to sort the Ids. There is no particular problem is some or all of them are not numerical, they will just be sorted alphabetically. –  jlliagre Jan 3 at 12:15
    
@terdon The order is not preserved with your awk solution. –  jlliagre Jan 3 at 12:21
    
No it isn't (my bad) though they do come out sorted on my system for some reason, irrespective of what order they're found in the input file. –  terdon Jan 3 at 12:23
add comment
awk '
    NR==1 {print; n=NF; next} 
    {
        id[$1]=1
        for (i=2; i<=n; i++) 
            sum[$1,i] += $i
    } 
    END {
        m = asorti(id, id_s);   # sort the ids
        for (i=1; i<=m; i++) {
            printf "%d", id_s[i]
            for (j=2; j<=n; j++)
                printf " %d", sum[id_s[i],j]
            print ""
        }
    }
' data.txt | column -t > out.txt

out.txt now contains

Id  a   b   c   d   e
1   6   9   12  15  18
2   15  18  21  24  27
3   34  38  42  46  50
4   23  25  27  29  31
5   27  29  31  33  35
share|improve this answer
add comment

A Perl way:

$ perl -ane '
    if($.==1){s/\s+/\t/g; print "$_\n"; @a=@F; shift(@a); }
    else{
         push @vals,$F[0] unless defined($sum{$F[0]});
         for($i=0; $i<=$#a;$i++){
           $sum{$F[0]}{$a[$i]}+=$F[$i+1]; 
         }
    }
    END{
     for $f (@vals){ 
      print "$f\t"; 
      print "$sum{$f}{$_}\t" for @a; 
      print "\n" 
     }
    } ' file 

An awk way:

$ awk 'BEGIN{OFS="\t"}
       (NR==1){
         printf "%s%s",$1,OFS; 
         for(i=2;i<=NF;i++){ k[i]=$(i); printf "%s%s",$(i),OFS;} 
         printf "\n"; next;
       }{for(i=2;i<=NF;i++){s[$1][k[i]]+=$(i); names[$1]++;}}
       END{for(i in names){
           printf "%s%s",i,OFS; 
           for(l in s[i]){printf "%s%s", s[i][l],OFS;}
           printf "\n";}
       }' file

Both of these will change spaces to tabs in order to keep the columns aligned. Their output is:

Id  a   b   c   d   e   
1   6   9   12  15  18  
2   15  18  21  24  27  
3   34  38  42  46  50  
4   23  25  27  29  31  
5   27  29  31  33  35  
share|improve this answer
    
I'm afraid none of these scripts generate the expected output. –  jlliagre Jan 3 at 11:54
    
@jlliagre yes, there was a bug in the Perl one, should work now. The awk one works fine here, both give the same output as yours (only difference being that mine adds a trailing tab to the 1st line as well while yours only adds it to the others). –  terdon Jan 3 at 12:05
    
+1 The perl version now works fine and maintain the original id order. The awk version still output randomly ordered rows for me. –  jlliagre Jan 3 at 12:19
    
@jlliagre really? Random ordered? Weird, they come out sorted on my system (even if I change the order in the original file which is confusing). I'm using GNU Awk 4.1.0. –  terdon Jan 3 at 12:22
    
I'm using GNU Awk 4.0.1, they might have changed the default behavior although the 4.1 documentation still states: As a side note, sorting the array indices before traversing the array has been reported to add 15% to 20% overhead to the execution time of awk programs. For this reason, sorted array traversal is not the default. –  jlliagre Jan 3 at 12:54
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.