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
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
    Feb 18 '21 at 14:50
  • Unfortunately the edit don't print in the order and don't print the header
    – nstatam
    Feb 19 '21 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. Feb 19 '21 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
    Feb 19 '21 at 8:14
  • @nstatam so the simplification data you showed in the question might be differ from your real data format. Feb 19 '21 at 8:48

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.