2

I'm wondering if there is any sed or awk command that can sum all columns for the rows with an identical identifier. For example, my file data.txt is set out as below, except that it contains ~1800 columns of data and ~1400 rows.

ABCD:1234  1.23  0.23  0.83   0
ABCD:1234    0    1.10  0.21   0 
EFGH:5678    0    1.90  0.12  8.21
IJKL:9999    1.22  0    1.84  9.21
IJKL:9999    1.44  0   12.94   0
IJKL:9999    1.32  0   24.12   2.43

And how I would like it to be after the command would be like this:

ABCD:1234  1.23  1.33  1.04  0
EFGH:5678    0    1.90  0.12  8.21
IJKL:9999   3.98   0   38.9  11.64

I'm not sure if this is even possible with awk or sed (I'm a biologist and still learning the basics of Unix). Any help would be much appreciated.

  • So, you want to sum the values on each column if the ID in the first column is identical? Are the rows with the same ID always next to each other? – Satō Katsura Jul 11 '16 at 7:36
  • @Kusalananda Yes they are. – DiscoA Jul 11 '16 at 8:03
  • About sed: sed is a stream text editor, its purpose is to edit its input stream, row by row. It has very poor capabilities for doing any arithmetics (and even less for doing floating point arithmetics). Its field of application is mainly row-based text manipulations. – Kusalananda Jul 11 '16 at 8:32
4

An awk script that doesn't hold either the input file, or the entire result table in memory:

FNR == 1   { for(i = 1; i <= NF; i++) a[i] = $i;  next }
$1 == a[1] { for(i = 2; i <= NF; i++) a[i] += $i; next }
{
    printf "%s", a[1]; a[1] = $1;
    for(i = 2; i <= NF; i++) { printf "\t%s", a[i]; a[i] = $i };
    printf "\n";
}
END {
    printf "%s", a[1];
    for(i = 2; i <= NF; i++) printf "\t%s", a[i];
    printf "\n";
}

To run it:

awk -f script.awk data.txt

Results:

ABCD:1234       1.23    1.33    1.04    0
EFGH:5678       0       1.90    0.12    8.21
IJKL:9999       3.98    0       38.9    11.64

On a side note: it's actually possible to do it with sed. You won't be doing it any time soon though. See here to get an idea why.

  • +1 for not storing the file in memory! (that's exactly why I asked the OP about consecutive lines, but you were quicker with the answer). In general, data in bioinformatics (and other fields of research) can be absolutely huge, and slurping it into awk variables is simply not doable. – Kusalananda Jul 11 '16 at 8:43
  • @SatoKatsura Thanks for the help. Unfortunately this script and command just replicated my data.txt file. Could it possibly be something wrong with my data.txt file? – DiscoA Jul 11 '16 at 8:50
  • 1
    @DiscoA The usual suspect: DOS line endings in the data file? – Satō Katsura Jul 11 '16 at 8:55
  • I re-made the file and it worked fine. I'm currently using a combination of excel and textwrangler to remove a few columns from the beginning so I must have introduced an error doing so, but it works fine now. Many thanks. – DiscoA Jul 11 '16 at 9:17
0

Alternate solution with perl

$ perl -nale '
if(!$seen{$F[0]}++)
{
    print join "\t", @a if @a;
    @a = @F[0..$#F];
}
else
{
    $a[$_] += $F[$_] foreach(1..$#F);
}
print join "\t", @a if eof;
' data.txt 
ABCD:1234   1.23    1.33    1.04    0
EFGH:5678   0       1.90    0.12    8.21
IJKL:9999   3.98    0       38.9    11.64
  • -a splits input line on spaces and saves them to @F array
  • The first field of line is used as a key to hash variable %seen, if the key is not found, print contents of @a array provided it is not empty and assign the array with fields of new line
  • If the key already exists, increment contents of array (from 2nd field to end) with corresponding contents from current line
  • To handle last entry, print contents of @a array again when end of file is reached


For the duplicate question: Add all columns separately in linux if the first column has same entries

$ perl -nale '
if(!$seen{$F[0]}++)
{
    print join "\t", @a if @a;
    @a = @F[0..$#F];
}
else
{
    $a[$_] += $F[$_] foreach(1..$#F);
}
print join "\t", @a if eof;
' filename.txt 
AC1481523   6   6   6   6
AC1481676   6   5   6   8


Solution with building an hash of arrays and printing the hash at end:

$ perl -nale '
if($h{$F[0]})
{
    $h{$F[0]}[$_] += $F[$_] foreach (1..$#F)
}
else
{
    $h{$F[0]} = [@F]
}
END { print join "\t",@{$h{$_}} foreach sort keys %h }
' data.txt
ABCD:1234   1.23    1.33    1.04    0
EFGH:5678   0       1.90    0.12    8.21
IJKL:9999   3.98    0       38.9    11.64

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.