Shell script - loop through values in multiple columns of a csv file

Question

I am working with a huge CSV file (filename.csv) that contains two columns. 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 - then i wanted to jump to the second column and take the value in the current row (of the second column). Next I wanted to divided the value larger value we got in column 1 by the value in the same cell of column two. For example in the following table: the smaller value we will get depending on my requirement is 327 (because 327 is smaller than the previous value 340) - and then we take 500 (which is the corresponding cell value on column two). Finally we divide 340 by 500 and get the value 0.68. My bash script should exit right after we print the value to the console.

In the following script, it works only for column 1 but i want to extend it for two columns based on my criteria i mentioned above.

awk '$1<p{ 
    val=$1/p                   
    if(val>=0.8 && val<=0.9)
        {
            print "value is:" $1/p
            print "A"
        }
    else if(val==0.8)
        {
            print "B"
        }
    else if(val>=0.6 && val <=0.7)
        {
            print "C" 

        }
    else if(val==0.5)
        {
            print "E"
        }
    else
        {
            print "D" 
        }
    exit
    }
    { 
        p=$1 
    }' filetest.csv

How can we loop through the values in two columns and perform control statement?

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

your first expected value 0.654 is bigger than 0.65, that's why it doesn't fit any of your conditions — RomanPerekhrest
– RomanPerekhrest, Commented May 5, 2017 at 8:41

RomanPerekhrest · Accepted Answer · 2017-05-05 09:22:59Z

1

Your first expected value 0.654 is bigger than 0.65(the 3rd condition as the closest one), that's why it doesn't fit any of your conditions.

Extend your script as shown below:

awk '$1<p{ 
    val=$1/$2  # to divide the first column value on the second column value              
    if(val>=0.85 && val<=0.9)
        {
            print "value is:" $1/p
            print "A"
        }
    else if(val==0.8)
        {
            print "B"
        }
    else if(val>=0.5 && val <=0.7)
        {
            print "C" 

        }
    else if(val==0.5)
        {
            print "E"
        }
    else
        {
            print "D" 
        }
    exit
    }
    { 
        p=$1 
    }' filetest.csv

The output:

The crucial line I've modified is:

val=$1/$2  # to divide the first column value on the second column value

edited May 5, 2017 at 9:22

answered May 5, 2017 at 8:43

RomanPerekhrest

93.1k4 gold badges75 silver badges112 bronze badges

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

14 Comments

Mahsolid Over a year ago

shouldn't the expression for "p" be changed (or modified)?

RomanPerekhrest Over a year ago

@Mahsolid, why should it? I have fixed your script according to your input description

Mahsolid Over a year ago

oh, sorry. i got it now and thanks. Accepted answer!

Mahsolid Over a year ago

by the way, when i try to run it - i got this error awk: cmd. line:2: (FILENAME=filetest.csv FNR=23) fatal: division by zero attempted

RomanPerekhrest Over a year ago

That could be. We can debug that moment. Let's print those values before the division: print $1, $2; val=$1/$2. What is the debug output?

|

Collectives™ on Stack Overflow

Shell script - loop through values in multiple columns of a csv file

1 Answer 1

14 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

14 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related