Using awk to group specific column from the file

Question

I have the data exported from Sql query into a file temp.log.

Below is the sample data. The entries after the header line are sorted on the first column.

Also, consider the nodes (36,54,69,76) as fixed list

<Name> <Status> <Node> <Requests>
AString1 Success 36 1
AString1 Success 54 4
AString1 Success 69 3
AString1 Success 76 4
BString2 Success 36 1
BString2 Success 54 2
BString2 Success 69 3
BString2 Success 76 1
CString3 Success 36 8
CString3 Success 54 7
CString3 Success 76 8
DString4 Success 36 8
DString4 Success 54 4
DString4 Success 69 12
DString4 Success 76 7

I need output as below

<Name> <Nodes> <Count of Requests>
Astring1 36 54 69 76 12
BString2 36 54 69 76 7
CString3 36 54 NA 76 23
DString4 36 54 69 76 31

Here the Last Column (<Count of Requests>) indicates the Total Count of requests on each node of the Name.

NA should be printed if a node didn't receive any requests.(Can see for CString3 row in o/p, it has one row missing for node value 69, in that case it should be NA, like CString3 36 54 NA 76 23

In success case DString4 - DString4 36 54 69 76 31

Can this be done with awk command?

Answer 1

$ cat tst.awk
NR==1 {
    print "<Name>", "<Nodes>", "<Count of Requests>"
    next
}
$1 != prev {
    if ( NR > 2 ) {
        prt()
    }
    prev = $1
}
{
    reqs[$3]
    numReqs += $4
}
END { prt() }

function prt(   i, n, node, nodes) {
    n = split("36 54 69 76", nodes)

    printf "%s ", prev
    for (i=1; i<=n; i++) {
        node = nodes[i]
        printf "%s ", (node in reqs ? node : "NA")
    }
    print numReqs

    delete reqs
    numReqs = 0
}

$ awk -f tst.awk file
<Name> <Nodes> <Count of Requests>
AString1 36 54 69 76 12
BString2 36 54 69 76 7
CString3 36 54 NA 76 23
DString4 36 54 69 76 31

Answer 2

Here's another awk solution:

$ awk '{if(NR==1){printf "%s\t%s\t%s\n", "<Name>","<Nodes>","<Count of Requests>"} else{nodes[$3]=1; a[$1][$3]++; sum[$1]+=$4}}END{for(string in a){ printf "%s\t", string; for(i in nodes){ (a[string][i]) ? val=i : val="NA"; printf "%s ",val }; printf "\t%d\n",sum[string];}}' file
<Name>  <Nodes> <Count of Requests>
CString3    36 54 NA 76     23
DString4    36 54 69 76     31
AString1    36 54 69 76     12
BString2    36 54 69 76     7
    

I am adding a tab character between each entry ("Name", "Nodes" and "Count of Requests") to allow you to easily separate them later.

And here's the same script in a more legible form (you can still copy/paste this directly into a terminal):

awk '{ 
        ## If this is the first line
        if(NR==1){
            ## Set up the nodes array with the desired values
            nodes[36]=1;
            nodes[54]=1;
            nodes[69]=1;
            nodes[76]=1; 
            ## Print the header
            printf "%s\t%s\t%s\n", "<Name>","<Nodes>","<Count of Requests>"
        } 
        ## For all except the first line
        else{
            ## Save this node in the nodes array
            nodes[$3]=1;
            ## add this node to the values found for this string
            a[$1][$3]++; 
            ## add the number of requests
            sum[$1]+=$4
        }
     }
    ## After we have finished reading the file
    END{
        ## a holds all the first fields, the various strings
        for(string in a){ 
            ## Print the current string and a tab    
            printf "%s\t", string; 
            ## For each target node
            for(i in nodes){ 
                ## If this node has a value for this string, use the value. 
                ## If it does not, use "NA".
                (a[string][i]) ? val=i : val="NA"; 
                ## Print the value for the node
                printf "%s ",val 
            }; 
            ## Print the sum
            printf "\t%d\n",sum[string];
        }
    }' file

Answer 3

You can use two indexed arrays to store the count of requests and the existing nodes for each name, and before printing, you can compare the array of existing nodes with your fixed array of nodes, for example:

awk 'BEGIN {
       totalNodes="36 54 69 76"
     }
     NR==1 {print "<Name> <Nodes> <Count of Requests>"};
     NR>1 {
         count[$1]+=$4; nodesTmp[$1]=nodesTmp[$1]" "$3;
         }
     END{
         for (i in count) {
             if(totalNodes!=nodesTmp[i]) {
                 split(totalNodes,tmp);
                 for(j in tmp){
                     if(nodesTmp[i]!~tmp[j]){
                         nodes[i]=nodes[i]" NA"
                     }
                     else {
                         nodes[i]=nodes[i]" "tmp[j]
                     }
                 }
             }
             else{
                 nodes[i]=nodesTmp[i]
             }; 
             print i, nodes[i], count[i] | "sort"
         };
     }' temp.log

Answer 4

Assuming GNU awk for PROCINFO and true double-index arrays, the following will work:

gawk 'BEGIN{PROCINFO["sorted_in"]="@ind_str_asc"}
      FNR>1{nodes[$3]=1; total[$1]+=$4; sgl_nodes[$1][$3]=1}
      END{
        printf "<Name> <Nodes> <Count of Requests>\n"
        for (f in sgl_nodes) {
          printf "%s%s",f,OFS;
          PROCINFO["sorted_in"]="@ind_num_asc";
          for (g in nodes) {
            if (sgl_nodes[f][g]) printf "%s%s",g,OFS; else printf "NA%s",OFS
          };
          PROCINFO["sorted_in"]="@ind_str_asc";
          printf "%d%s",total[f],ORS
        }
      }' temp.log 

This will register the actually encountered node numbers in an array nodes, and those associated with every single name in the two-dimensional array sgl_nodes, stored with the value of <Name> as first index. While processing, it will also sum the total request count in the array total, under array index corresponding to the <Name> value.

At end-of-file, we first print the header line, and then iterate over the sgl_nodes array to print for each <Name> value (first index of sgl_nodes) the name, and then iterate over all nodes registered in nodes to print either the node number (if encountered for that particular <Name>) or NA (if that node was missing), and finally the total request count.

<Name> values and are printed in ascending order thanks to the PROCINFO setting performed in the BEGIN section, which is overriden to numerical sorting for iterating over the nodes array.

This approach does not rely on the temp.log entries being sorted, and it recognized the node numbers automatically.

---

The advantage of recognizing the node numbers automatically turns into a problem if one of the node numbers happens to be missing from all entries. Since you stated that the node list can be considered fixed, however, we can resort to hard-coding that list to avoid this problem:

gawk 'BEGIN{PROCINFO["sorted_in"]="@ind_str_asc";split("36 54 69 76",nodes)}
      FNR>1{total[$1]+=$4; sgl_nodes[$1][$3]=1}
      END{
        printf "<Name> <Nodes> <Count of Requests>\n"
        for (f in sgl_nodes) {
          printf "%s%s",f,OFS;
          for (g=1;g<=4;g++) {
            if (sgl_nodes[f][nodes[g]]) printf "%s%s",nodes[g],OFS; else printf "NA%s",OFS
          };
          printf "%d%s",total[f],ORS
        }
      }' temp.log