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.

3
  • 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? Commented Jul 11, 2016 at 7:36
  • @Kusalananda Yes they are.
    – DiscoA
    Commented Jul 11, 2016 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
    Commented Jul 11, 2016 at 8:32

2 Answers 2

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.

4
  • +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
    Commented Jul 11, 2016 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
    Commented Jul 11, 2016 at 8:50
  • 1
    @DiscoA The usual suspect: DOS line endings in the data file? Commented Jul 11, 2016 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
    Commented Jul 11, 2016 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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .