CODEWITHSUNDEEP
awk
OXXO
OXXO

Reputation: 724

Count value in column by hours and add header

In column 3 the value is hour, I will like to print the header from 0 to 23 hrs and count in column 3 the times repeated by hr. If not value for hr is found then print 0.

Input file

123 3 3
122 3 3
122 4 4
122 3 4
122 4 4
122 5 5
122 3 12
122 4 15
122 5 20
122 5 20

Output desired

First row = Header 0 to 23 hrs separated by,

Second row = Values found for each hr., If not value is found print 0.

0,1,2,3,4,5,6,7,8,9,10,11,12,13,15,16,17,18,19,20,21,22,23
0,0,0,2,3,1,0,0,0,0,0,0,1,0,0,1,0,0,0,0,2,0,0,0

To count by hr, I tried

awk '{a[$3]++} END {for(i in a) print i, a[i]}'

Thanks in advance.

Upvotes: 3

Views: 65

Answers (4)

karakfa
karakfa

Reputation: 67507

another awk

$ awk '{a[$3]++} 
    END{while(i<24) 
          {h1=h1 s i+0; 
           h2=h2 s a[i++]+0; 
           s=","} 
        print h1 ORS h2}' file

0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23
0,0,0,2,3,1,0,0,0,0,0,0,1,0,0,1,0,0,0,0,2,0,0,0

ps. looks like a variation of @JamesBrown's answer.

Upvotes: 3

Thor
Thor

Reputation: 47109

Change the for-loop slightly:

for(i in a) print i, a[i]

To:

for(i=0; i<=23; i++) print i, a[i]+0

Transpose the output with rs:

awk ... | rs -c' ' -T

Output:

0   1   2   3   4   5   6   7   8   9   10  11  12  13  14  15  16  17  18  19  20  21  22  23
0   0   0   2   3   1   0   0   0   0   0   0   1   0   0   1   0   0   0   0   2   0   0   0

If you are certain about the csv output, use tr at the end:

awk '{a[$3]++} END {for(i=0;i<=23;i++) print i, a[i]+0}' | rs -c' ' -T | tr -s ' ' ,

Output:

0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23
0,0,0,2,3,1,0,0,0,0,0,0,1,0,0,1,0,0,0,0,2,0,0,0

Upvotes: 2

James Brown
James Brown

Reputation: 37404

$ awk '
{ a[$3]++ }                              # hash them
END {
    for(i=0;i<=23;i++) {                 # loop the hours
        b=b (b==""?"":",") i             # collect hours to b
        c=c (c==""?"":",") (a[i]?a[i]:0) # and counts to c
    }
    print b ORS c                        # output them
}' file
0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23
0,0,0,2,3,1,0,0,0,0,0,0,1,0,0,1,0,0,0,0,2,0,0,0

Upvotes: 3

RavinderSingh13
RavinderSingh13

Reputation: 133538

Could you please try following.

awk '
BEGIN{
  OFS=","
  for(i=0;i<=23;i++){
     printf("%d%s",i,i==23?ORS:OFS)
  }
}
{
  a[$3]++
}
END{
  for(j=0;j<=23;j++){
     printf("%d%s",a[j],j==23?ORS:OFS)
  }
}'   Input_file

Output will be as follows.

0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23
0,0,0,2,3,1,0,0,0,0,0,0,1,0,0,1,0,0,0,0,2,0,0,0

Upvotes: 2

Related Questions