1

The input txt file looks like (there are more columns in the actual txt file):

target_id   length  eff_length  tot_counts  uniq_counts est_counts  eff_counts
mthl7   61  0   0   0   0   0
loqs    72  0   0   0   0   0
CG45085 58  0   0   0   0   0
CG18317 4978    1430.739479 91  0   30.333333   105.539363
CG18317 4978    1430.739479 91  0   30.333333   105.539363
CG18317 4978    1430.739479 91  0   30.333333   105.539363

for column 1 when they have the same id (e.g.CG18317) I want to add up the values in the rest of the columns. So the output would look like:

target_id   length  eff_length  tot_counts  uniq_counts est_counts  eff_counts
mthl7   61  0   0   0   0   0
loqs    72  0   0   0   0   0
CG45085 58  0   0   0   0   0
CG18317 14934   4292.218437 273 0   90.999999   316.618089

I tried to use command like:

awk -F" "
'{a[$1]+=$4;b[$1]+=$5;c[$1]+=$6;d[$1]+=$7;e[$1]+=$8;f[$1]+=$9;g[$1]+=$10;h[$1]+=$11;i[$1]+=$12;j[$1]+=$14;}END{for (i in a) print i" "a[i]" "b[i]" "c[i]" "d[i]" "e[i]" "f[i]" "g[i]" "h[i]" "i[i]" "j[i]}' temp2.txt

Error message is:

awk: can't assign to i; it's an array name.
 input record number 7, file temp2.txt
 source line number 1

Is this due to the header? How should I bypass the 1st row?

I tried answers for similar question found here but also did not work.

  • 1
    You can't use i for the iteration variable for (i in a) since it already refers to an array called i - for example, what do you expect i[i] to return? – steeldriver Oct 8 '14 at 2:31
2
$ awk 'NR==1{print;next} {for (i=2;i<=NF;i++) {a[$1][i]+=$i}} END{ \
    for (j in a) {s=j; for (i=2;i<=NF;i++) {s=s" "a[j][i]}; print s}}' file
target_id   length  eff_length  tot_counts  uniq_counts est_counts  eff_counts
mthl7 61 0 0 0 0 0
loqs 72 0 0 0 0 0
CG18317 14934 4292.22 273 0 91 316.618
CG45085 58 0 0 0 0 0

If you want to keep the lines in the same order, it requires a little bit more code:

$ awk 'NR==1{print;next} {if ($1 in seen); else b[c++]=$1; seen[$1]=1; \
    for (i=2;i<=NF;i++) {a[$1][i]+=$i}} END{for (j=0;j<c;j++) {s=b[j]; \
    for (i=2;i<=NF;i++){s=s" "a[b[j]][i]}; print s}}' file | column -t
target_id  length  eff_length  tot_counts  uniq_counts  est_counts  eff_counts
mthl7      61      0           0           0            0           0
loqs       72      0           0           0            0           0
CG45085    58      0           0           0            0           0
CG18317    14934   4292.22     273         0            91          316.618

Above, we also piped the output to column -t to get aligned columns.

Commands in form suitable for copying and pasting

The above commands have been spread over multiple for easier viewer. If you want to copy and past the commands, use these versions instead:

awk 'NR==1{print;next} {for (i=2;i<=NF;i++) {a[$1][i]+=$i}} END{ for (j in a) {s=j; for (i=2;i<=NF;i++) {s=s" "a[j][i]}; print s}}' file

And:

awk 'NR==1{print;next} {if ($1 in seen); else b[c++]=$1; seen[$1]=1; for (i=2;i<=NF;i++) {a[$1][i]+=$i}} END{for (j=0;j<c;j++) {s=b[j]; for (i=2;i<=NF;i++){s=s" "a[b[j]][i]}; print s}}' file | column -t

Non-GNU awk

Try:

awk 'NR==1{print;next} {if ($1 in seen); else b[c++]=$1; seen[$1]=1; for (i=2;i<=NF;i++) {a[$1","i]+=$i}} END{for (j=0;j<c;j++) {s=b[j]; for (i=2;i<=NF;i++){s=s" "a[b[j]","i]}; print s}}' file | column -t
  • Thanks again! I tried the second one. I got an error said: awk: syntax error at source line 1 context is NR==1{print;next} {if ($1 in seen); else b[c++]=$1; seen[$1]=1; for (i=2;i<=NF;i++) >>> {a[$1][ <<< – Karli Oct 8 '14 at 18:08
  • And the first one gave the same error. – Karli Oct 8 '14 at 18:12
  • @Karli Are you on OSX or another BSD platform? The array-of-arrays feature seems to be what is causing that error and it is a GNU extension. I will look for a work-around. – John1024 Oct 8 '14 at 18:18
  • @Karli See updated answer. – John1024 Oct 8 '14 at 18:25
  • I am on OSX, using bash4.2 – Karli Oct 8 '14 at 18:25

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.