I have a data file that contains dates and corresponding measurements of some quantity. The full file stretches over 13 years and has ~4500 data points-- I have given an example of the format below. There is not necessarily a measurement on each day, i.e. some dates correspond to a blank value in the data column, titled "pbl".
I want to compute, for each year of my dataset, the number of observations (i.e. a count of non-zero values) and also the sum. Then I'll repeat this, but for each month. Sort-of cheating, I have added columns with the full dates reduced to "year" and "month-year" to make this easier.
I am using awk and can sum correctly. The problem is counting. Currently my line of awk counts the number of days per year (or per month) and not the number of observations. So, for the full dataset, I get "365" or "366" i.e. the total number of entries listed in the date column; what I want to get is the number of these dates that have a corresponding measurement in the "pbl" column.
My hunch is that I need to include a "for non-zero term" clause somewhere or count on $4 instead of $2, but I can't work out how to do this. Any tips appreciated!
My code:
awk -F, '{a[$2]++; count[$2]+=$NF}END{for (i in a) print i,a[i],count[i]}' data.csv
My data (sample):
"date","year","month-year","pbl"
05/12/04,2004,"12-2004",960.295
06/12/04,2004,"12-2004",
13/12/04,2004,"12-2004",911.774
29/12/04,2004,"12-2004",2804.06
30/12/04,2004,"12-2004",4453.56
01/01/05,2005,"01-2005",1326.38
07/01/05,2005,"01-2005",
08/01/05,2005,"01-2005",604.919
09/01/05,2005,"01-2005",755.006
10/01/05,2005,"01-2005",
11/01/05,2005,"01-2005",978.818
12/01/05,2005,"01-2005",
13/01/05,2005,"01-2005",1644.87
29/04/05,2005,"04-2005",3630.62
30/04/05,2005,"04-2005",3780.11
01/05/05,2005,"05-2005",1262.66
02/05/05,2005,"05-2005",
14/02/06,2006,"02-2006",5667.58
15/02/06,2006,"02-2006",2696.56
23/07/07,2007,"07-2007",1276.06
24/07/07,2007,"07-2007",