1

I have a CSV file which has the following format:

Date,Towns,Count1,Count2
6/26/2015,HYDERABAD,234552,0
         ,KARIM NAGAR,7520,0
         ,RAJAMUNDRY,7248,256
         ,KURNOOL,5848,32,0
         ,TIRUPATI,8560,64,0
         ,VIJAYAWADA,16776,96,0,0,16872
         ,VIZAG,30264,624,8,0.0128205128205128,30888
         ,WARANGAL,14112,880,0,0,14992
         ,GUNTUR,10304,224,0,0,10528
6/27/2015,HYDERABAD,0,6960,0,0,6960
         ,KARIM NAGAR,0,0,0,ERROR,0
         ,RAJAMUNDRY,0,256,0,0,256
         ,KURNOOL,0,32,0,0,32
         ,TIRUPATI,0,64,0,0,64
         ,VIJAYAWADA,0,96,0,0,96
         ,VIZAG,0,528,0,0,528
         ,WARANGAL,0,880,0,0,880
         ,GUNTUR,0,224,0,0,224

This CSV has number of columns and rows. The rows are grouped based on dates in such a way that each Town has its separate counters and the CSV has the row values for each town which are separated by the first column (date).

Is there a way to read this CSV file using a shell script in such a way that the code identifies the start and end of each day (which can be based on either the date column or the town column) and groups the rows according to that? Say, i want the find the total of Count2 from 6/26/2015 (which will be the sum of all the towns from HYDERABAD to GUNTUR on 6/26/2015)?

2

The lines in your CSV don't have the same number of fields. Happily ignoring that issue however, you could do something like this:

awk -F, -v OFS=, '{ if($1 ~ /^[ \t]*$/) $1 = old; else old = $1 } 1' file.csv | \
    datamash -H -t, -g 1 sum 4

datamash above is GNU datamash. awk normalises the first column, and datamash does the grouping and summing.

Output:

GroupBy(Date),sum(Count2)
6/26/2015,2176
6/27/2015,9040

Finding an awk-only solution is left as an (easy) exercise to the reader. :)

  • Thanks Icd047, i partially achieved what i wanted to get here. Could you please explain a little bit about this awk command awk -F, -v OFS=, '{ if($1 ~ /^[ \t]*$/) $1 = old; else old = $1 } 1' Actually i am a novice to scripting and awk and if you could break this command and explain what it is doing, it would be really helpful for me in the future. – Ankit Vashistha Jul 14 '15 at 5:50
  • Also, is there a way to replace the datamash sum command with the general command where i can replace the sum operation with any other operation i want to perform? I have the requirement to perform various operations (like sum, average, calculate formulas) on the column values in each row in the CSV file which has multiple counter values in it. With the awk command you mentioned, i was able to repeat the date values in the empty columns which will really be useful to me. – Ankit Vashistha Jul 14 '15 at 6:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.