Replace value in particular columns in csv file

Question

I would like to replace the values which are > than 20 in columns 5 and 7 to AAA

input file

9179,22.4,-0.1,22.4,2.6,0.1,2.6,39179
9179,98.1,-1.7,98.11,1.9,1.7,2.55,39179
9179,-48.8,0.5,48.8,-1.2,-0.5,1.3,39179
6121,25,0,25,50,0,50,36121
6123,50,0,50,50,0,50,36123
6125,75,0,75,50,0,50,36125

output desired

9179,22.4,-0.1,22.4,2.6,0.1,2.6,39179
9179,98.1,-1.7,98.11,1.9,1.7,2.55,39179
9179,-48.8,0.5,48.8,-1.2,-0.5,1.3,39179
6121,25,0,25,AAA,0,AAA,36121
6123,50,0,50,AAA,0,AAA,36123
6125,75,0,75,AAA,0,AAA,36125

I tried

With this command I replace the values in column 5, how to do it for column 7 too.

awk -F ',' -v OFS=',' '$1 { if ($5>20) $5="AAA"; print}' file

Thanks in advance

karakfa · Accepted Answer · 2019-02-22 20:19:40Z

2

here is another take for making the columns set configurable

$ awk -v cols="5,7" 'BEGIN {FS=OFS=","; split(cols,a)} 
                           {for(i in a) if($a[i]>20) $a[i]="AAA"}1' file 

9179,22.4,-0.1,22.4,2.6,0.1,2.6,39179
9179,98.1,-1.7,98.11,1.9,1.7,2.55,39179
9179,-48.8,0.5,48.8,-1.2,-0.5,1.3,39179
6121,25,0,25,AAA,0,AAA,36121
6123,50,0,50,AAA,0,AAA,36123
6125,75,0,75,AAA,0,AAA,36125

answered Feb 22, 2019 at 20:19

karakfa

67.8k8 gold badges45 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

P.... · Accepted Answer · 2019-02-22 20:10:07Z

2

awk 'BEGIN{FS=OFS=","} $5>20{$5="AAA"} $7>20{$7="AAA"}1' file
9179,22.4,-0.1,22.4,2.6,0.1,2.6,39179
9179,98.1,-1.7,98.11,1.9,1.7,2.55,39179
9179,-48.8,0.5,48.8,-1.2,-0.5,1.3,39179
6121,25,0,25,AAA,0,AAA,36121
6123,50,0,50,AAA,0,AAA,36123
6125,75,0,75,AAA,0,AAA,36125

You can use two {..} for multiple checks and action

edited Feb 22, 2019 at 20:10

answered Feb 22, 2019 at 19:55

P....

18.6k3 gold badges36 silver badges55 bronze badges

Collectives™ on Stack Overflow

Replace value in particular columns in csv file

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related