Get maximum per column from CSV file, based on date column

Shell Programming and Scripting
1

Hello everyone,

I am using ksh on Solaris 10 and I'm gathering data in a CSV file that looks like this:

20170628-23:25:01,1,0,0,1,1,1,1,55,55,1
20170628-23:30:01,1,0,0,1,1,1,1,56,56,1
20170628-23:35:00,1,0,0,1,1,2,1,57,57,2
20170628-23:40:00,1,0,0,1,1,1,1,58,58,2
20170628-23:45:00,1,0,0,1,1,1,1,58,58,1
20170628-23:50:00,1,0,0,1,1,2,1,58,58,1
20170628-23:55:00,1,0,0,1,1,1,1,58,58,1
20170629-00:00:15,1,0,0,1,1,1,1,58,58,1
20170629-00:05:00,1,0,0,1,1,1,1,58,58,2
20170629-00:10:00,1,0,0,1,1,1,1,58,58,1
20170629-00:15:00,1,0,0,1,1,1,1,58,58,5
20170629-00:20:00,1,0,0,1,1,4,1,58,58,3
20170629-00:25:00,1,0,0,1,1,1,1,59,59,1

What is the best approach on getting the maximum for each column, but only for yesterday's date?

Desired output:

1,0,0,1,1,2,1,58,58,2

Thank you!

2

I would approach this problem using AWK.
What have you tried?

3

I'll take yesterday's date with the following. Should be ok, as I don't intend to run the script close to midnight:

YESTERDAY=`TZ=GMT+24 date +%Y%m%d`;

Then I want to pass YESTERDAY to awk, but nothing I tried worked for me so far.

Then I need to calculate the maximum.
The following is not at all elegant but it works.

awk -F, '{if ($1~/20170629/) {print $9}}' file.txt | sort -nr | head -1

I'll need to do a lot more reading to do it all in awk.

4

Try

awk -F, -vYD=$(date -d"-1day" +%Y%m%d) '$0 ~ "^" YD {for (i=2; i<=NF; i++) if (MAX <= $i) MAX = $i} END {for (i=2; i<=NF; i++) printf "%s%s", MAX, (i<NF)?",":ORS}' file
1,0,0,1,1,2,1,58,58,2
5

date doesn't like

date -d"-1day"

So I've changed your code to

awk -F, -vYD=20170702 '$0 ~ "^" YD {for (i=2; i<=NF; i++) if (MAX <= $i) MAX = $i} END {for (i=2; i<=NF; i++) printf "%s%s", MAX, (i<NF)?",":ORS}' file

And I get the following:

awk: syntax error near line 1
awk: bailing out near line 1
6

I missed your mentioning Solaris 10 in post#1, so yes, your date doesn't have -d , and:

7

Using awk I don't get any output.

Using /usr/xpg4/bin/awk I get the following:

/usr/xpg4/bin/awk -F, -vYD=20170629 '$0 ~ "^" YD {for (i=2; i<=NF; i++) if (MAX <= $i) MAX = $i} END {for (i=2; i<=NF; i++) printf "%s%s", MAX, (i<NF)?",":ORS}' values.txt
Invalid form for variable assignment: $0 ~ "^" YD {for (i=2; i<=NF; i++) if (MAX <= $i) MAX = $i} END {for (i=2; i<=NF; i++) printf "%s%s", MAX, (i<NF)?",":ORS}