Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Replace Column if equal to a specific value

Tags:

linux

unix

sed

awk

I'm looking to replace the fourth column in a CSV if it equals N/A. I'm trying to change it to -1.
I can't seem to get this to work.

awk -F , '{ if($4 == "N/A") {$4 = -1} }' test.csv
like image 570
cloud36 Avatar asked May 23 '14 15:05

cloud36


2 Answers

You can use the following awk:

awk -F, '{ $4 = ($4 == "N/A" ? -1 : $4) } 1' OFS=, test.csv
  • We set the input and output field separators to , to preserve the delimiters in your csv file
  • We check the forth field if it is equal to "N/A" then we assign it the value -1 if not we retain the value as is.
  • 1 at the end prints your line with or without modified 4th column depending if our test was successful or not.
  • ($4=="N/A"?-1:$4) is a ternary operator that checks if the condition $4=="N/A" is true or not. If true ? then we assign -1 and if false : we keep the field as is.

Test run on sample file:

$ cat file
a,b,c,d,e,f
1,2,3,4,5,6
44,2,1,N/A,4,5
24,sdf,sdf,4,2,254,5
a,f,f,N/A,f,4

$ awk -F, '{ $4 = ($4 == "N/A" ? -1 : $4) } 1' OFS=, file
a,b,c,d,e,f
1,2,3,4,5,6
44,2,1,-1,4,5
24,sdf,sdf,4,2,254,5
a,f,f,-1,f,4
like image 147
jaypal singh Avatar answered Nov 04 '22 00:11

jaypal singh


Here is another awk (using example data from jaypal)

awk -F, '$4=="N/A" {$4=-1}1' OFS=, file
a,b,c,d,e,f
1,2,3,4,5,6
44,2,1,-1,4,5
24,sdf,sdf,4,2,254,5
a,f,f,-1,f,4
like image 38
Jotne Avatar answered Nov 03 '22 22:11

Jotne