awk desired data data from file and print column wise

Question

I have a text file like below.

mark     10 20 30
lawrence 40 22 60
mark     11 12 13
mike     15 16 17
lawrence 21 22 23
mike     31 32 33
mike     41 42 47

I want to process it and produce output to conform to these conditions:

• One line for each unique name (first column).  So, for the above example, there should be three output lines; one each for "mark", "lawrence" and "mike".
• Output looks superficially like the input:
  • Four columns.
  • First column of the output is name (first column of the input).
  • Second, third and fourth columns are integers.
• The second column is the number of occurrences of the name in the first column.
• The third column is the number of occurrences of the name in the first column with a value of 20 in the third column of the input.
• The fourth column is the number of occurrences of the name in the first column with a value of 22 in the third column of the input.

Expected output:

mark     2  1  0
mike     3  0  0
lawrence 2  0  2

I've written

... | awk '{ c[$1]++ } END { for (name in c) print name, c[name] }'

My code only outputs

mark     2 
mike     3 
lawrence 2 

Answer 1

awk '
    {name[$1]++}      #Counts how many times each name appear
    $3==20{x20[$1]++} #Counts how many times 20 appears in third column
    $3==22{x22[$1]++} #Counts how many times 22 appears in third column
    END{
        for (i in name){
            printf "%s %d %d %d\n",i,name[i],x20[i],x22[i]
        }
    }
' file

Output:

mike 3 0 0
lawrence 2 0 2
mark 2 1 0

Answer 2

Similar concept to @Quasimodo's answer but using the values 20 and 22 in one place rather than 3 and without hard-coding the values into the names of arrays:

awk '
    { namesCnt[$1]++; pairsCnt[$1,$3]++ }
    END {
        for (name in namesCnt) {
            print name, namesCnt[name], pairsCnt[name,20]+0, pairsCnt[name,22]+0
        }
    }
' file

Answer 3

To get the output lined up into columns, as shown in the question, take either of the other answers (Quasímodo’s or Ed Morton’s) and pipe it into column -t.