user1375481 user1375481 - 3 months ago 15
Linux Question

linux-bash read data from csv and sum up values conditionally

Im new to shell scriptingand been working on reading data from csv. I have a csv file which contain data from line 10. The data is like


2016-12-12,22.5,56
2016-12-13,23.1,62.1
2016-12-14,16.3,76.6
2016-12-15,18.8,44.7
2016-12-16,17.6,53.2



I would like to get average of 2nd and 3rd column for only weekends [ when first column date is either sat or sun]. In this case i need to get averages for 2016-07-2 and 2016-07-3.

I have written a script which uses awk , but however its failing near looping inside first bracket

idealminTemp=15 #set ideal minimum
idealMaxTemp=25 #set ideal maximum
weekends=( "2016-07-2" "2016-07-3" "2016-07-9" "2016-07-10" "2016-07-16" "2016-07-17" "2016-07-23" "2016-07-24" "2016-07-30" "2016-07-31" )

awk -F"," 'NR > 9 {for i in ${weekends[@]} i= $1 ? MinTemp+=$3; MaxTemp+=$4 : MinTemp+=0; MaxTemp+=0 ; } END { ( MinTemp/(NR-9) >= $idealminTemp && MaxTemp/(NR-9) <= $idealMaxTemp ?
print "Ideal" : print "Not Ideal" }' ./InputFile.csv

Answer

I didn't understand your script but you can follow this template to restrict the logic to any day of week

$ awk -F, '{date=$2;                              # copy of date field
            gsub("-"," ",date);                   # format date for mktime
            if(strftime("%u",mktime(date " 00 00 00"))>5)  # Saturday:6, Sunday:7
                  print $2,($3+$4)/2}' file       # print original date field and average

2016-07-2 13.55
2016-07-3 11.45