Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

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

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Get maximum per column from CSV file, based on date column
# 1  
Old 06-29-2017
Get maximum per column from CSV file, based on date column
Hello everyone,

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

Code:
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:
Code:
1,0,0,1,1,2,1,58,58,2

Thank you!
# 2  
Old 06-29-2017
I would approach this problem using AWK.
What have you tried?
# 3  
Old 06-30-2017
I'll take yesterday's date with the following. Should be ok, as I don't intend to run the script close to midnight:

Code:
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.

Code:
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  
Old 06-30-2017
Try
Code:
awk -F, -vYD=$(date -d"-1day" +%Y%m%d) '$0 ~ "^" YD {for (i=2; i<=NF; i++) if (MAX[i] <= $i) MAX[i] = $i} END {for (i=2; i<=NF; i++) printf "%s%s", MAX[i], (i<NF)?",":ORS}' file
1,0,0,1,1,2,1,58,58,2

# 5  
Old 07-03-2017
date doesn't like
Code:
date -d"-1day"

So I've changed your code to
Code:
awk -F, -vYD=20170702 '$0 ~ "^" YD {for (i=2; i<=NF; i++) if (MAX[i] <= $i) MAX[i] = $i} END {for (i=2; i<=NF; i++) printf "%s%s", MAX[i], (i<NF)?",":ORS}' file

And I get the following:
Code:
awk: syntax error near line 1
awk: bailing out near line 1

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

Quote:
Originally Posted by Don Cragun
If you are using a Solaris/SunOS system, use /usr/xpg4/bin/awk or nawk instead of awk.
# 7  
Old 07-10-2017
Using awk I don't get any output.

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

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

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

How to align/sort the column pairs of an csv file, based on keyword word specified in another file?

I have a csv file as shown below, xop_thy 80 avr_njk 50 str_nyu 60 avr_irt 70 str_nhj 60 avr_ngt 50 str_tgt 80 xop_nmg 50 xop_nth 40 cyv_gty 40 cop_thl 40 vir_tyk 80 vir_plo 20 vir_thk 40 ijk_yuc 70 cop_thy 70 ijk_yuc 80 irt_hgt 80 I need to align/sort the csv file based... (7 Replies)
Discussion started by: dineshkumarsrk
7 Replies

2. UNIX for Beginners Questions & Answers

Filtering records of a csv file based on a value of a column

Hi, I tried filtering the records in a csv file using "awk" command listed below. awk -F"~" '$4 ~ /Active/{print }' inputfile > outputfile The output always has all the entries. The same command worked for different users from one of the forum links. content of file I was... (3 Replies)
Discussion started by: sunilmudikonda
3 Replies

3. Shell Programming and Scripting

awk to select lines with maximum value of each record based on column value

Hello, I want to get the maximum value of each record separated by empty line based on the 3rd column of each row within each record? Input: A1 chr5D 634 7 82 707 A2 chr5D 637 6 82 713 A3 chr5D 637 5 82 713 A4 chr5D 626 1 82 704... (4 Replies)
Discussion started by: yifangt
4 Replies

4. Linux

To get all the columns in a CSV file based on unique values of particular column

cat sample.csv ID,Name,no 1,AAA,1 2,BBB,1 3,AAA,1 4,BBB,1 cut -d',' -f2 sample.csv | sort | uniq this gives only the 2nd column values Name AAA BBB How to I get all the columns of CSV along with this? (1 Reply)
Discussion started by: sanvel
1 Replies

5. Shell Programming and Scripting

Fetching values in CSV file based on column name

input.csv: Field1,Field2,Field3,Field4,Field4 abc ,123 ,xyz ,000 ,pqr mno ,123 ,dfr ,111 ,bbb output: Field2,Field4 123 ,000 123 ,111 how to fetch the values of Field4 where Field2='123' I don't want to fetch the values based on column position. Instead want to... (10 Replies)
Discussion started by: bharathbangalor
10 Replies

6. Linux

Filter a .CSV file based on the 5th column values

I have a .CSV file with the below format: "column 1","column 2","column 3","column 4","column 5","column 6","column 7","column 8","column 9","column 10 "12310","42324564756","a simple string with a , comma","string with or, without commas","string 1","USD","12","70%","08/01/2013",""... (2 Replies)
Discussion started by: dhruuv369
2 Replies

7. Shell Programming and Scripting

Script for extracting data from csv file based on column values.

Hi all, I am new to shell script.I need your help to write a shell script. I need to write a shell script to extract data from a .csv file where columns are ',' separated. The file has 5 columns having values say column 1,column 2.....column 5 as below along with their valuesm.... (3 Replies)
Discussion started by: Vivekit82
3 Replies

8. Shell Programming and Scripting

Pick the column value based on another column from .csv file

My scenario is that I need to pick value from third column based on fourth column value, if fourth column value is 1 then first value of third column.Third column (2|3|4|6|1) values are cancatenated. Main imp point, in my .csv file, third column is having price value with comma (1,20,300), it has... (2 Replies)
Discussion started by: Ganesh L
2 Replies

9. Shell Programming and Scripting

Read CSV column value based on column name

Hi All, I am newbie to Unix I ve got assignment to work in unix can you please help me in this regard There is a sample CSV file "Username", "Password" "John1", "Scot1" "John2", "Scot2" "John3", "Scot3" "John4", "Scot4" If i give the column name as Password and row number as 4 the... (3 Replies)
Discussion started by: JohnGG
3 Replies

10. Shell Programming and Scripting

sorting csv file based on column selected

Hi all, in my csv file it'll look like this, and of course it may have more columns US to UK;abc-hq-jcl;multimedia UK to CN;def-ny-jkl;standard DE to DM;abc-ab-klm;critical FD to YM;la-yr-tym;standard HY to MC;la-yr-ytm;multimedia GT to KJ;def-ny-jrt;critical I would like to group... (4 Replies)
Discussion started by: tententen
4 Replies
Login or Register to Ask a Question