2

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
  • The output values for row #3 are incorrect. – jlliagre Jan 3 '14 at 12:00
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") }
}'
  • 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 '14 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 '14 at 12:15
  • @terdon The order is not preserved with your awk solution. – jlliagre Jan 3 '14 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 '14 at 12:23
1
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
1

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  
  • I'm afraid none of these scripts generate the expected output. – jlliagre Jan 3 '14 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 '14 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 '14 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 '14 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 '14 at 12:54
1

With gnu datamash:

{ head -n 1; datamash -s -g 1 sum 2 sum 3 sum 4 sum 5 sum 6; } <infile

use -W if the fields are separated by more than one blank:

{ head -n 1; datamash -Wsg 1 sum 2 sum 3 sum 4 sum 5 sum 6; } <infile

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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