0

I have a file A.csv (field separator = ,):

Lane,SampleID,Index,# Reads,# Perfect Index Reads,# One Mismatch Index Reads,# of >= Q30 Bases (PF),Mean Quality Score (PF)
1,4331099,AACCATAGAA-CCATCTCGCC,301713,291343,10370,10159753,33.99
1,4360506,GCCGCACTCT-CGAGGTCGGA,271546,261268,10278,9107606,33.91
2,4331099,AACCATAGAA-CCATCTCGCC,297033,286679,10354,10036901,34.06
2,4360506,GCCGCACTCT-CGAGGTCGGA,266222,255838,10384,8954558,33.96
3,4331099,AACCATAGAA-CCATCTCGCC,293499,281393,12106,9838039,33.89
3,4360506,GCCGCACTCT-CGAGGTCGGA,262214,249940,12274,8741308,33.78
4,4331099,AACCATAGAA-CCATCTCGCC,289896,277519,12377,9735725,33.93
4,4360506,GCCGCACTCT-CGAGGTCGGA,255337,243245,12092,8530109,33.82

And I want to concatenate the line based on the SampleID , print the Index column and sum the others column (# Reads,# Perfect Index Reads,# One Mismatch Index Reads,# of >= Q30 Bases (PF),Mean Quality Score (PF)) and remove the column Lane . And put the result in another file Like this :

SampleID,Index,# Reads,# Perfect Index Reads,# One Mismatch Index Reads,# of >= Q30 Bases (PF),Mean Quality Score (PF)
4331099,AACCATAGAA-CCATCTCGCC,1182141,1136934,45207,39770418,135.87
4360506,GCCGCACTCT-CGAGGTCGGA,1055319,1010291,45028,35333581,135.47

Do you have some awk command (or else) in order to do that?

I've done some test like this but nothing close to a start:

awk '{
      if(NR!=2){a[$1]=$2";"a[$1]}
      else print $0}
    END{
      n = asorti(a, b);
      for (n in b) {
      print b[n],a[b[n]]
      }
    }'

Thanks in advance

1 Answer 1

1
gawk -F, -v s=4 '
    NR>1{ for(c=s; c<=NF; c++) sum_[c][$2 FS $3] +=$c; next }
        { sub(/^[^,]*,/ ,""); print }
END{ for(r in sum_[s]){
         printf ("%s", r);
         for(i=s; i<=NF; i++) printf (",%s", sum_[i][r]);
         print ""
     };
}' infile

Keep the order as original file:

gawk -F, -v s=5 '
    NR>1{ 
          if(!seen[$3 FS $4]) seen[$3 FS $4]=$1;
          for(c=s; c<=NF; c++) sum_[c][$3 FS $4] +=$c; next 
        }
        { sub(/^([^,]*,){2}/ ,""); print "1 "$0 }
END{ for(r in sum_[s]){
         printf ("%s %s", seen[r], r);
         for(i=s; i<=NF; i++) printf (",%s", sum_[i][r]);
         print ""
     };
}' <(nl -s ',' -w 1 infile) |sort |cut -d' ' -f2-

or with GNU datamash which you may prefer it:

datamash -H  -t,  -s  -g2,3  sum 4-8 <infile
7
  • Thanks for your answer ! it works well. Do you know a solution for printing the line in the same order that the original file ?
    – nstatam
    Commented Feb 18, 2021 at 14:50
  • Unfortunately the edit don't print in the order and don't print the header
    – nstatam
    Commented Feb 19, 2021 at 8:04
  • are you working on Windows env? do dos2unix filename to convert the Windows line ending to Unix type, based on your given data, command works perfectly with no issues. Commented Feb 19, 2021 at 8:06
  • I've try to dos2unix first and then run your script and the header is here but mixed with all the other line that are not in order
    – nstatam
    Commented Feb 19, 2021 at 8:14
  • @nstatam so the simplification data you showed in the question might be differ from your real data format. Commented Feb 19, 2021 at 8:48

You must log in to answer this question.

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