Column manipulation and counter

Shell Programming and Scripting
1

Hello,

I was wondering how I could change this input:

cat f1

to produce this output:

As you can tell, I have several unsorted repeating strings in col1 of the input file. Each of these strings has an associated word in col2. Col2 has a total of 4 possible words (go, stop, yield, halt).

The output should have different columns. It should produce the number of times each string repeats in col1. In col2 the output should have the name of the string that is repeating (for ex: DOG5 repeated twice in the input so the output is now displaying the number "2" in col1 and the name "DOG5" in col2).

The other columns in the output should have a column for each of the words that the input col2 had (go, stop, yield, halt). For each string that is now only being shown once in col2, the output should mark how many "go" "stop" "yield" and "halt" each of the col2 strings had. For ex: DOG5 had a total of 2 "go" from the input. DOG5 is also blank for the remaining words because there were no DOG5's in the input that had stop or yield or halt. DOG24 had 1 stop 1 yield and 1 halt from the input and that is now being shown in different columns in the output.

So far, I have this.

 cat f1 | awk '{print $1}'|sort -k 1,1 | uniq -c | sort -k 1,1 -n

Any ideas?

---------- Post updated at 11:58 AM ---------- Previous update was at 11:55 AM ----------

Apologizes for the formatting issue. The output below may be more readable.

2 
awk '
NR == 1 {
        print "col1","col2","go","stop","yield","halt"
} NR > 1 {
        $1 = $1
        F[$1]++
        if ( $2 ~ "go" )
                go[$1]++
        if ( $2 ~ "stop" )
                stop[$1]++
        if ( $2 ~ "yield" )
                yield[$1]++
        if ( $2 ~ "halt" )
                halt[$1]++
} END {
        for ( c in F )
                print F[c], c, (go[c]==""?0:go[c]), (stop[c]==""?0:stop[c]), (yield[c]==""?0:yield[c]), (halt[c]==""?0:halt[c])
} ' OFS='\t'  file
3

Thank you. This works nicely. Can you explain what the For c in F section means?

4

That is how we scan all elements of an Array

Check GNU AWK Scanning an Array for further reference.