removing and summing up the csv file elements using awk

user_7018468 0
Viewed 58

I have a big csv file like this example:

9,AGCTGTCCTCTT,SCPL
9,AGCTGTCATTTTGTTTCG,SCHFVS
9,AGCTGTAATTGTCGTAAGAGGTCTGCTTATTGTTATTTGTGTGGCCCG,SCNCRKRSAYCYLCGP
9,AGCTGTCCTCTT,SCPL
9,AGCTGTAGTCATTTGGGTTTTATTTGTTTTCATGGCATTGTGGCCCG,

1 - In some lines the 3rd column is empty , so they should be removed

2 - Some lines have similar characters in the 3rd column. If that is the case, I want to sum up the values in the 1st column and put it in the 4th column. If the characters in the 3rd column is not repeated in other rows, I want to copy the value of the 1st column into the 4th column.

Expected output:

9,AGCTGTCCTCTT,SCPL,18
9,AGCTGTCATTTTGTTTCG,SCHFVS,9
9,AGCTGTAATTGTCGTAAGAGGTCTGCTTATTGTTATTTGTGTGGCCCG,SCNCRKRSAYCYLCGP,9

To do so, I made the following code in awk but it does not return the expected output.

awk -F "," '{ if(($3 == '') && ({t=3;for(i=2;i<NF;i++){t+=$i==$(i+1)}}t==NF)) { print } }' infile.csv > outfile.csv

Do you know how I can change the code to get the expected output?

awk
3 Answers

With your shown samples, please try following awk program. Simple explanation would be, sorting Input_file as per 1st, 2nd and 3rd fields making , as a field separator first. Then passing its standard output to awk program as an input. In awk program setting field separator and output field separator as ,. Creating variable sum which is keep adding 1st field value to it, until current line is different from previous line.

sort -t, -r -k1 -k2 -k3 file | 
awk '
BEGIN{
  FS=OFS=","
}
prev!=$0 && prev{
  print prev,sum
  sum=prev=""
}
NF>=3 && $3!=""{
  sum+=$1
  prev=$0
}
END{
  if(sum){
   print prev,sum
  }
}
'
user_58665800

Still not sure why the fourth record was removed from the sample output but this one removes all but the first instance of each $3:

$ awk -F, -v OFS=, '
NR==FNR {              # on the 1st of 2 runs for the file
    if($3!="")         # remove if $3 empty
        a[$3]+=$1      # sum the $1s
    next
}
($3 in a) {            # on the 2nd run
    print $0,a[$3]     # output
    delete a[$3]       # only first of unique $3s is output
}' file file           # make that 2 runs

Output:

9,AGCTGTCCTCTT,SCPL,18
9,AGCTGTCATTTTGTTTCG,SCHFVS,9
9,AGCTGTAATTGTCGTAAGAGGTCTGCTTATTGTTATTTGTGTGGCCCG,SCNCRKRSAYCYLCGP,9
user_41623560

Something like

BEGIN  { FS="," }
NF < 3 || $3 == "" { next; }
       { score[$3] += $1; orig[$3] = $0; } 
END    { for (g in score) { print orig[g] "," score[g]; } }

Should work:

ronald:~/tmp$ awk '
BEGIN { FS="," }
NF < 3 || $3 == "" { next; }
       { score[$3] += $1; orig[$3] = $0; } 
END { for (g in score) { print orig[g] "," score[g]; } }' < infile
9,AGCTGTCCTCTT,SCPL,18
9,AGCTGTAATTGTCGTAAGAGGTCTGCTTATTGTTATTTGTGTGGCCCG,SCNCRKRSAYCYLCGP,9
9,AGCTGTCATTTTGTTTCG,SCHFVS,9

Instead of sorting the input, a hash is used. If your input is exceptionally large, this might lead to out of memory situations.

user_31938760
Related