1

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",

Improve this question

1 Answer 1

Reset to default
3

You need to process only those lines with a value in $4:

awk -F, '$4 {a[$2]++; count[$2]+=$NF}END{for (i in a) print i,a[i],count[i]}' data.csv

This works because

$4 {a[$2]++; count[$2]+=$NF}

causes the block to only be executed on lines where $4 evaluates to true, i.e. where $4 is non-empty and non-zero.

Improve this answer
5
  • Cool! I didn't know about this trick-- can you explain a little more why it works? Could two columns be specified in front like this? What's the syntax in that case?
    – brendanvolc
    Nov 7, 2017 at 14:35
  • 1
    I added an explanation to the answer. You could specify two columns by writing $2 && $4 for example for columns two and four.
    – Stephen Kitt
    Nov 7, 2017 at 14:42
  • One further query, if I may: this is currently summing the final column ($4): if there were multiple columns of data, e.g. $5 and $6 as well, how do I modify the command to count those instead? To put it another way, is there a way to modify the current line of awk that forces it to count and sum for a chosen column, not just the final one?
    – brendanvolc
    Nov 7, 2017 at 15:36
  • 1
    Use the field number instead of NF: count[$2]+=$4 etc. (And you can use multiple arrays to sum different columns separately.)
    – Stephen Kitt
    Nov 7, 2017 at 16:04
  • Ah, ok. Thus... awk -F, '$5 {a[$3]++; b[$3]+=$5; c[$3]+=$6; d[$3]+=$7}END{for (i in a) print i,a[i],b[i],c[i],d[i]}' data.csv | sort Thanks!
    – brendanvolc
    Nov 7, 2017 at 16:10

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .