0

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
2
$ 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.

  • Oh this is nice Kusalananda!!!! Especially explaining that really helped me to understand and not just copy and paste......much appreciated!!!!!! – Kam Feb 22 at 18:23
0
$ 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
}
  • Shouldn't it be NR>1? – finswimmer Feb 22 at 17:51
  • This worked (had to add a closing " on the word THROUGH). Can you break it down and explain what each section of your command does? I end up using the alternative command provided by DopeGhoti. The explanation would be great for me to learn. – Kam Feb 22 at 18:09
  • Hi DopeGhoti, following your logic, I had to make changes to the csv file, where "xcoord" column is no longer the first of the column. Instead, the "xcoord" column is on the 24th column, while the "BMT" column is at the end and last column (column 28th). I used your logic to continue to get the same results to show up on the "BMT" column but it broke. I modified your awk command to the following – Kam Feb 22 at 19:38
  • awk -F, 'BEGIN { OFS="," } NR==1 { print $28, "BMT" } NR>1 { if( $24 < 0 ) { $24="BEHIND" }; if( $24==0 ) { $24="MID" }; if( $24>0 ) { $24="THROUGH" }; print }' sourcefile.csv > outfile.csv – Kam Feb 22 at 19:39
  • I also tried to with NR==28 (instead of 1), but that also broke...... – Kam Feb 22 at 19:41
0

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
0

You can use sed :

sed '/^[^-0]/s/$/,THROUGH/;/^-/s/$/,BEHIND/;/^0/s/$/,MID/;1s/[^,]*$/BMT/' infile
  • interesting, ctac, what happens if my xcoord column moves and no longer on the first column. Which values tells the sed command to read into which column number. It seems my spreadsheet has changed now, where my xcoord column is now on the 24th column (instead the first column on my original post), and my BMT column is now the last column (column 28th). – Kam Feb 22 at 19:49
  • @Kam If the column move, the answer move too.You can catch the 24th column with (([^,]*,)\{23\})(...) but it became more complicated with sed. – ctac_ Feb 22 at 20:22
0
 $ 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.