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.

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
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
  • just downloaded and tried it out.. fyi: on version 1.0.7, had to use -g1 instead of groupby 1 – Sundeep Oct 8 '16 at 5:14
  • -bash: datamash: command not found I got this error. why is that? – zara Oct 11 '16 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. – steeldriver Oct 11 '16 at 20:23
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
  • this method changes the order of groups in output for the real data. – zara Oct 11 '16 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.

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.