Replace column if equal to a specific value

Question

I want to replace the fourth column in CSV if it is equal N/A. I'm trying to change it to -1.
I can't get this to work.

awk -F , '{ if($4 == "N/A") {$4 = -1} }' test.csv

Answer 1

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 in ,to save the separators in your csv file
• We check the fourth field, if it is equal to "N / A", then we assign a value to it -1, if we do not store the value as is.
• 1 4- , .
• ($4=="N/A"?-1:$4) - , , $4=="N/A" . true ?, -1, false :, .

:

$ 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

Answer 2

awk ( 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

Answer 3

sed,

$ sed -r 's/^([^,]*),([^,]*),([^,]*),N\/A,(.*)$/\1,\2,\3,-1,\4/g' 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

Answer 4

awk sed: , .

If your file is split into a tab, you can use \tinstead ,.

sed 's/,N\/A,/,-1,/g' infile.csv >  outfile.csv

Replace with the same file, good for large files, check before use!

sed -i 's/,N\/A,/,-1,/g' infile.csv