# How to calculate the sum of the data that have the same ID in the first column?

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

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
``````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
``````
-

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
``````{ 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
``````
-