Bash script - How to loop through rows in a CSV file

Question

I am working with a huge CSV file (filename.csv) that contains a single column. From column 1, I wanted to read current row and compare it with the value of the previous row. If it is greater OR equal, continue comparing and if the value of the current cell is smaller than the previous row - divide the value of the current cell by the value of the previous cell and exit by printing the value of the division. For example from the following example: i wanted my bash script to divide 327 by 340 and print 0.961765 to the console and exit.

I tried it with the following awk and it works perfectly fine.

awk '$1 < val {print $1/val; exit} {val=$1}' filename.csv

However, since i want to include around 7 conditional statements (if-else's), I wanted to do it with a bit cleaner bash script and here is my approach. I am not that used to awk to be honest and that's why i prefer to use bash.

#!/bin/bash

FileName="filename.csv"
# Test when to stop looping
STOP=1

# to find the number of columns
NumCol=`sed 's/[^,]//g' $FileName | wc -c`; let "NumCol+=1"

# Loop until the current cell is less than the count+1

while [ "$STOP" -lt "$NumCol" ]; do

        cat $FileName | cut -d, -f$STOP
        let "STOP+=1"
done

How can we loop through the values and add conditional statements?

PS: the criteria for my if-else statement is (if the value ($1/val) is >=0.85 and <=0.9, print A, else if the value ($1/val) is >=0.7 and <=0.8, print B, if the value ($1/val) is >=0.5 and <=0.6 print C otherwise print D).

You say if the value is 0.9, print A. Does this refer to the value of $1/val ? — George Vasiliou
– George Vasiliou, Commented May 4, 2017 at 10:23
OK.. One more question : The value of 0.9 is a rounded value? The result 0.961765 should print "A" or should print "D"? — George Vasiliou
– George Vasiliou, Commented May 4, 2017 at 10:35
In my solution i do not make "mathematical" rounding. I just take the first three chars from the $1/val result using substring function of awk. So if $1/val = 0.961765 with substr we get 0.9 . If you want solutions to be adjusted for 0.96 you need to redifine your criteria also. — George Vasiliou
– George Vasiliou, Commented May 4, 2017 at 11:36

James Brown · Accepted Answer · 2017-05-04 11:46:42Z

2

Here's one in GNU awk using switch, because I haven't used it in a while:

awk '
$1<p { 
    s=sprintf("%.1f",$1/p)
    switch(s) {
    case "0.9":            # if comparing to values ranged [0.9-1.0[ use /0.9/
        print "A"          # ... in which case (no pun) you don't need sprintf
        break
    case "0.8":
        print "B"
        break
    case "0.7":
        print "c"
        break
    default:
        print "D"
    }
    exit
}
{ p=$1 }' file
D

Other awks using if:

awk '
$1<p { 
#    s=sprintf("%.1f",$1/p)  # s is not rounded anymore
    s=$1/p                  
#    if(s==0.9)              # if you want rounding, 
#        print "A"           # uncomment and edit all ifs to resemble
    if(s~/0.9/) 
        print "A"
    else if(s~/0.8/) 
        print "B"
    else if(s~/0.7/) 
        print "c"
    else
        print "D"
    exit
}
{ p=$1 }' file
D

edited May 4, 2017 at 11:46

answered May 4, 2017 at 10:35

James Brown

37.7k8 gold badges52 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

15 Comments

Mahsolid Over a year ago

Why 6 A's? I want to print only one value and then exit.

George Vasiliou Over a year ago

You can probably move the exit outside of switch instead having exit in each case.

James Brown Over a year ago

It's for GNU awk only. Which awk are you using?

Mahsolid Over a year ago

I am using awk on Mac.

James Brown Over a year ago

OK, then the ifs should be if(s>=0.85 && s<=0.9) etc.

|

George Vasiliou · Accepted Answer · 2017-05-04 12:24:09Z

2

This is an alternative approach,based on previous input data describing comparison of $1/val with fixed numbers 0.9 , 0.7 and 0.6.
This solution will not work with ranges like ($1/val) >=0.85 and <=0.9 as clarified later.

awk 'BEGIN{crit[0.9]="A";crit[0.7]="B";crit[0.6]="C"} \
$1 < val{ss=substr($1/val,1,3);if(ss in crit) {print crit[ss]} else {print D};exit}{val=$1}' file
A

This technique is based on checking if rounded value $1/val belongs to a predefined array loaded with corresponding messages.

Let me expand the code for better understanding:

awk 'BEGIN{crit[0.9]="A";crit[0.7]="B";crit[0.6]="C"} \   #Define the criteria array. Your criteria values are used as keys and values are the messages you want to print.
$1 < val{
         ss=substr($1/val,1,3);          #gets the first three chars of the result $1/val
         if(ss in crit) {                #checks if the first three chars is a key of the array crit declared in begin
                         print crit[ss]  #if it is, print it's value
                        } 
          else {
                print D                  #If it is not, print D
                };
          exit
        }
{val=$1}' file

Using substr we get the first three chars of the result $1/val:
for $1/val = 0.961765 using substr($1/val,1,3) returns 0.9

If you want to make comparisons based on two decimals like 0.96 then change substr like substr($1/val,1,4).
In this case you need to accordingly provide the correct comparison entries in crit array i.e crit[0.96]="A"

edited May 4, 2017 at 12:24

answered May 4, 2017 at 10:59

George Vasiliou

6,3652 gold badges23 silver badges33 bronze badges

3 Comments

James Brown Over a year ago

That's neat also.

Mahsolid Over a year ago

This is so cool too but what if we want to say a value >=0.85 and a value <=0.9 to be A?

George Vasiliou Over a year ago

@Mahsolid And this is why questions need to expressed as clearly as possible! This is a new condition! Anyway, i don't think that my solution can work in such case. You probably have to go with James approach. I will let you know if i figure out something cool.

Collectives™ on Stack Overflow

Bash script - How to loop through rows in a CSV file

2 Answers 2

15 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

15 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related