Replace Column if equal to a specific value
14,522
Solution 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 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
Solution 2
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
Author by
cloud36
Updated on September 20, 2022Comments
-
cloud36 over 1 year
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
-
mklement0 almost 10 years+1 for the great explanation (though it's probably overkill to rebuild every input line by always assigning to
$4
). Can I suggest putting a space before the final1
to better indicate that it is a separate command (pattern with implied action)? -
Timo about 6 years
ofs
is a built in variable and the output field separator, see here -
Timo about 6 yearsI like the
else
possibility, +1