Output to last column depending on first column if is greater, less than or equal to 0

Question

I have a large .csv (comma separated, with over 90,000 rows), the format looks like this:

xcoord,m1,m2,m3
9,TRUE,FALSE,FALSE
-1,TRUE,FALSE,FALSE
0,TRUE,FALSE,FALSE

I was able to add an extra column at the end with a header of AMT to allow writing to the last field:

Source_File="/test/Orders_ALL.csv"
Output_File="/test/Orders_ALL_MODIFIED.csv"
awk -v d="BMT" -F"," 'BEGIN { OFS = "," } {$5=d; print}' "$Source_File" > "$Output_File"

The output after the above yields:

xcoord,m1,m2,m3,BMT
9,TRUE,FALSE,FALSE,BMT
-1,TRUE,FALSE,FALSE,BMT
0,TRUE,FALSE,FALSE,BMT

What I am trying accomplish, is to read the first column value starting from the 2nd row and onwards, if is less than 0 it will write the word "BEHIND", if is 0 than print the word "MID" and if is anything greater than 0, than print the word "THROUGH"

Here is the desired result:

xcoord,m1,m2,m3,BMT
9,TRUE,FALSE,FALSE,THROUGH
-1,TRUE,FALSE,FALSE,BEHIND
0,TRUE,FALSE,FALSE,MID

Answer 1

$ awk 'BEGIN { OFS=FS="," } $1<0 { t="BEHIND" } $1==0 { t="MID" } $1>0 { t="THROUGH" } NR==1 { t="BMT" } { print $0, t }' file.csv
xcoord,m1,m2,m3,BMT
9,TRUE,FALSE,FALSE,THROUGH
-1,TRUE,FALSE,FALSE,BEHIND
0,TRUE,FALSE,FALSE,MID

The code starts by setting the input and output field delimiters to a comma. It then acts on the value of the first column, setting the variable t to the correct value. If we're looking at the first line of input, t is then set to BMT (regardless of what it was previously set to). We then print the current line with t as a new field at the end.

With slightly nicer formatting:

awk 'BEGIN   { OFS = FS = "," }
     $1 <  0 { t = "BEHIND"   }
     $1 == 0 { t = "MID"      }
     $1 >  0 { t = "THROUGH"  }
     NR == 1 { t = "BMT"      }
             { print $0, t    }' file.csv

Using Perl:

$ perl -lane 'BEGIN { @t=(qw(BEHIND MID THROUGH)) } printf("%s,%s\n", $_, $. == 1 ? "BMT" : $t[1+($F[-1] <=> 0)])' file.csv
xcoord,m1,m2,m3,BMT
9,TRUE,FALSE,FALSE,THROUGH
-1,TRUE,FALSE,FALSE,BEHIND
0,TRUE,FALSE,FALSE,MID

The Perl <=> operator (sometimes known as the "spaceship operator") does a three way arithmetic comparison. When the left hand side is strictly less than the right hand side, it returns -1, if they are equal, it returns 0, and it otherwise it returns 1.

Answer 2

$ awk -F, 'BEGIN { OFS="," } NR==1 { print $0, "BMT" } NR>1  && $1>0 { print $0, "THROUGH" } NR>1 && $1==0 { print $0, "MID" } NR>1 && $1<0 { print $0, "BEHIND" }' inputfile > outputfile

Or, alternatively:

$ awk -F, 'BEGIN { OFS="," } NR==1 { print $0, "BMT" } NR>1 { if( $1 < 0 ) { $5="BEHIND" }; if( $1==0 ) { $5="MID" }; if( $1>0 ) { $5="THROUGH" }; print }' inputfile > outputfile

The actual awk script reformatted for easier reading (I here will expand upon the latter version):

BEGIN { 
    OFS="," # Set the Output Field Separator.  
            # This is the complement to `F,` which 
            # sets the Input Field Separator.
} 

NR==1 {  # For only the first record
    print $0, "BMT" # Print exactly what we got, but add a new field, BMT
} 

NR>1 {   # For all records _after_ the first:
    if( $1 < 0 ) {    # This and the other if statements check
        $5="BEHIND"   # the value of the first field, and set
    };                # the value of the to-be-newly-added fifth 
    if( $1==0 ) {     # field accordingly. 
        $5="MID" 
    }; 
    if( $1>0 ) { 
        $5="THROUGH" 
    }; 
    print             # Finally, print all our fields
}

Answer 3

With Miller (http://johnkerl.org/miller/doc)

mlr --csv put 'if ($xcoord < 0) {$BMT="BEHIND"};
if ($xcoord == 0) {$BMT="MID"};
if ($xcoord > 0) {$BMT="THROUGH"}' input >./output

You have

xcoord,m1,m2,m3,BMT
9,TRUE,FALSE,FALSE,THROUGH
-1,TRUE,FALSE,FALSE,BEHIND
0,TRUE,FALSE,FALSE,MID

As input I have used

xcoord,m1,m2,m3
9,TRUE,FALSE,FALSE
-1,TRUE,FALSE,FALSE
0,TRUE,FALSE,FALSE

Answer 4

You can use sed :

sed '/^[^-0]/s/$/,THROUGH/;/^-/s/$/,BEHIND/;/^0/s/$/,MID/;1s/[^,]*$/BMT/' infile

Answer 5

 $ perl -F, -pale '$_ .= "," . ("BMT", qw/MID THRU BEHIND/[$F[0] <=> 0])[$. > 1]' csvfile

In case xcoord is in the 23rd column, replace $F[0] by $F[22]

For explanation see Kusalananda's answer where I wrote a brief on the Perl solution.

Result:

 xcoord,m1,m2,m3,BMT
 9,TRUE,FALSE,FALSE,THRU
 -1,TRUE,FALSE,FALSE,BEHIND
 0,TRUE,FALSE,FALSE,MID

xcol=1; # the column number of xcoord
sed -e '
  1s/$/,BMT/;1b
  s/,/\n/'"$xcol"'
  s/.*/,&,/
  /,-[1-9][0-9]*\n/s/$/BEHIND/
  /,0\n/s/$/MID/
  /,[1-9][0-9]*\n/s/$/THRU/
  s/\n/,/;s/,//
 '  csvfile