1

I have a data file looks like:

c1.11   SNP1    -6.73098    0.764833    Chr1:1
c1.21   SNP2    -4.871  0.00393002  Chr1:101
c1.12   SNP3    -0.766822   0.0891227   Chr1:201
c1.22   SNP4    11.7499 0.141861    Chr1:301
c2.11   SNP5    8.38008 0.741379    Chr1:401
c2.21   SNP6    -0.86974    0.00824037  Chr1:501
c2.12   SNP7    -0.181898   0.00494422  Chr1:601
c2.22   SNP8    -7.32856    0.245436    Chr1:701
c3.11   SNP9    -12.0418    0.369929    Chr1:801
c3.12   SNP10   11.2833 0.357378    Chr1:901
c3.22   SNP11   -0.0308993  0.270918    Chr1:1001
c4.121  SNP12   1.51586 0.0770791   Chr1:1101
c4.122  SNP13   0.118888    0.0742901   Chr1:1201

What I want to do is to accumulate values within third column in which are belong to the same group from the first column. for example the first 4 values are belonged to the group c1, while the next 4 values are belonged to group c2 and ... . So the new output should look like:

 Output :
c1 -0.61778
c2  0.00845
c3 -0.7875
c4 1.6347

Any suggestion please? (please consider that the real data is huge.

4 Answers 4

3

With GNU datamash (after replacing the period with whitespace using sed)

sed 's/\./ /' data | datamash -W groupby 1 sum 4
c1      -0.618902
c2      -0.000118
c3      -0.7893993
c4      1.634748
3
  • just downloaded and tried it out.. fyi: on version 1.0.7, had to use -g1 instead of groupby 1
    – Sundeep
    Oct 8, 2016 at 5:14
  • -bash: datamash: command not found I got this error. why is that?
    – zara
    Oct 11, 2016 at 19:02
  • @zara presumably the datamash software is not installed, or is not in your executable search PATH? It isn't (yet) a standard Unix utility. Oct 11, 2016 at 20:23
1

perl solution, assumes input file is sorted by c1, c2, etc.. So, saving in hash/array is not required

$ perl -lane '
$F[0] =~ s/\..*//;
if($F[0] ne $p && $. > 1)
{
    print "$p $sum";
    $sum = 0;
}
$sum += $F[2];
$p = $F[0];
END { print "$p $sum" }' ip.txt
c1 -0.618902
c2 -0.000118000000001395
c3 -0.7893993
c4 1.634748
  • -la strip newlines from input and add while printing, split input line on spaces and save to @F array
  • $F[0] =~ s/\..*// delete all characters from . for first field
  • if($F[0] ne $p && $. > 1) if input line number is not first line and first field is not the same as previous one
    • print field name and accumulated sum, clear sum variable
  • At end, print again to account for last entry


Another way would be to not split the input line and use regex to extract required key and value:

$ perl -lne '
($k, $v) = /^([^.]+)(?:\S+\s+){2}(\S+)/;
if($k ne $p && $. > 1)
{
    print "$p $sum";
    $sum = 0;
}
$sum += $v;
$p = $k;
END { print "$p $sum" }' ip.txt
c1 -0.618902
c2 -0.000118000000001395
c3 -0.7893993
c4 1.634748
1

With GNU awk:

awk '{grp = gensub("^([^.]+).*", "\\1", 1, $1); \
              arr[grp]+=$3} END {for (i in arr) print i, arr[i]}' file.txt
  • gensub("^([^.]+).*", "\\1", 1, $1) gets the portion before first . from first field, we are storing it as variable grp

  • arr[grp]+=$3 generates array with key as grp, and values are accumulated from third column of each line

  • The chunk in END segment will iterate over the array elements, and print key-values in desired format

For consistent input, with POSIX awk:

awk '{sub("\\..*", "", $1); arr[$1]+=$3} END {for (i in arr) print i, arr[i]}'
  • sub("\\..*", "", $1) modifies the first field in place to truncate the portion after ., and array arr is created with keys as the (modified) first field

Example:

% cat file.txt
c1.11   SNP1    -6.73098    0.764833    Chr1:1
c1.21   SNP2    -4.871  0.00393002  Chr1:101
c1.12   SNP3    -0.766822   0.0891227   Chr1:201
c1.22   SNP4    11.7499 0.141861    Chr1:301
c2.11   SNP5    8.38008 0.741379    Chr1:401
c2.21   SNP6    -0.86974    0.00824037  Chr1:501
c2.12   SNP7    -0.181898   0.00494422  Chr1:601
c2.22   SNP8    -7.32856    0.245436    Chr1:701
c3.11   SNP9    -12.0418    0.369929    Chr1:801
c3.12   SNP10   11.2833 0.357378    Chr1:901
c3.22   SNP11   -0.0308993  0.270918    Chr1:1001
c4.121  SNP12   1.51586 0.0770791   Chr1:1101
c4.122  SNP13   0.118888    0.0742901   Chr1:1201

% awk '{grp = gensub("^([^.]+).*", "\\1", 1, $1); arr[grp]+=$3} END {for (i in arr) print i, arr[i]}' file.txt
c1 -0.618902
c2 -0.000118
c3 -0.789399
c4 1.63475

% awk '{sub("\\..*", "", $1); arr[$1]+=$3} END {for (i in arr) print i, arr[i]}' file.txt
c1 -0.618902
c2 -0.000118
c3 -0.789399
c4 1.63475
1
  • this method changes the order of groups in output for the real data.
    – zara
    Oct 11, 2016 at 19:11
0

this is my solution, try it and let me know if it works.

#!/bin/bash


awk '
BEGIN{group="c1"
sum=0}
{
    if(substr($1,1,2)==group) 
    {sum+=$3
    print group " " sum} 
    else {
        group=substr($1,1,2)
        sum=$3
        print group " " sum}
    }'  file.txt > tmp.txt





awk 'BEGIN{group="c1"}
     $1!=group {print group " " sum
     group=$1} {sum=$2}
     END{print $1 " " $2}'  tmp.txt >finalResult.txt

rm tmp.txt

And the result should appear in finalResult.txt. You can copy it into a bash script and test it.

You must log in to answer this question.

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