awk to split and count number in field

user_4668368 0
Viewed 99

In the below awk I am trying to skip the header, extract the number between each : and , in $2, and then print $1 and the count of $2, and put the header back in the output. My current output seems to be be duplicating each line and printing the line as is. The input may have empty columns in each line but it will always be tab-delimited. Thank you :).

awk

awk 'BEGIN{FS=OFS="\t"}; NR>1 {gsub(/:,/,"",$2); {count[$2]++} print $1,$count} FNR>1' file

also tried:

awk -F'\t' '{gsub(/:,/,"",$2); {count[$2]++}
 END{print "id","string"; 
  print $1,count}}' file | column -t

file tab-delimited

id string
a1a B:80,V:2,Z:0
b2b B:100,V:1,Z:3

current tab-delimited

a1a
a1a B:80,V:2,Z:0
b2b
b2b B:100,V:1,Z:3

desired tab-delimited

id sting
a1a 82
b2b 104
awk
5 Answers

With your shown samples, please try following awk code.

awk '
BEGIN { FS=OFS="\t" }
FNR==1{
  print
  next
}
{
  sum=0
  num=split($2,arr,"[:,]")
  for(i=2;i<=num;i+=2){
    sum+=arr[i]
  }
  print $1,sum
}
'  Input_file

Output will be as follows with your shown samples:

id string
a1a 82
b2b 104

Explanation: Adding detailed explanation for above code.

awk '                         ##Starting awk program from here.
BEGIN { FS=OFS="\t" }         ##In BEGIN section setting FS and OFS to \t here.
FNR==1{                       ##checking if this is first line then do following.
  print                       ##Printing current line here.
  next                        ##next will skip all further statements from here.
}
{
  sum=0                       ##Nullifying sum here.
  num=split($2,arr,"[:,]")    ##Splitting 2nd field into array arr with delimiter of : ;
  for(i=2;i<=num;i+=2){       ##Running for loop from i=2 to till NF with difference of 2
    sum+=arr[i]               ##Adding arr[i] value to sum and keep adding it.
  }
  print $1,sum                ##Printing $1 and sum here.
}
' Input_file                  ##Mentioning Input_file name here.
user_58665800 
$ awk '
    BEGIN { FS=OFS="\t" }
    NR>1 {
        n = split($2,a,/[:,]/)
        sum = 0
        for ( i=2; i<=n; i+=2 ) {
            sum += a[i]
        }
        $2 = sum
    }
    { print }
' file
id      string
a1a     82
b2b     104
user_17450010

You may use this awk:

awk '
BEGIN{FS=OFS="\t"}
NR == 1 {
   print
   next
}
n = split($2, a, /,/) {
   s = 0
   for (i=1; i<=n; ++i) {
      sub(/[^:]*:/, "", a[i])
      s += a[i]+0
   }
   print $1, s
}' file

id  string
a1a 82
b2b 104
user_5482250 
awk -F'[ :,]' 'NR==1{print}NF==7{print$1,$3+$5+$7}' input_file|column -t
id   string
a1a  82
b2b  104
user_41778280

an awk solution that requires neither arrays nor loops :

< input_file.txt | 

{m,g}awk 'BEGIN { FS = "[,]?["(OFS = "\t")" ]*([A-Z][:])?"
         _+=++_ } NR<_ || NF=_^($_+= $(_+!!_) + $NF )^!_'


id  string
a1a 82
b2b 104

The idea is to use FS to collect away as much as possible, leaving fields already looking like this before the filtering logic :

id    string            # <intermediary view>
a1a   80       2    0
b2b   100      1    3

Then just add $3 and $4 back into $2

Just for gawk, one can even do ::

gawk '
BEGIN {
    FS = "[,]?[" (OFS = "\t") " ]*([A-Z][:])?"
    print $(_ * (getline))
} $NF += $NF + $--NF + ! --NF '

id  string
a1a 82
b2b 104

mawk equivalent to that would be :

  • (discrepancy being how each of them track increments and decrements to NF)
mawk 'BEGIN { FS="[,]?["(OFS="\t")" ]*([A-Z][:])?"
      print \
           $(getline*!(_+=++_)) } $_+=$NF+$--NF+!—NF'

id  string
a1a 82
b2b 104
user_146721140
Related