conditional based find and replace values in fields of csv file

Question

I want to find & replace values in column 5 & 6 based on condition given to column 1. If first column has value 2159 then it should replace value in column 5 by 13.49694 if it has value 13.512034 and replace value in column 6 by 78.22772 if it has value 78.226233. I tried with following command but it replacing all occurrences of 78.226233 by 78.22772 in column 6. What I want is it should replace only for 2159 value in column 1.

awk -F ',' -v OFS=',' '$1 ~ /^2159/ && $5=="13.512034"{$5="13.49694"}1 && $6=="78.226233"{$6="78.22772"}1' 14update1.csv > 14update2.csv

Is there any way to update the change in same file? I am pretty new to awk and shell scripting so I apologize if this is an easy fix.

The datafile I have is something like:

2159,KOLAR,SRINIVASAPURA,GAUNIPALLI (KODIPALLI) (GP),13.49694,78.22772,14-08-17,22,0
2159,KOLAR,SRINIVASAPURA,GAUNIPALLI (KODIPALLI) (GP),13.49694,78.22772,14-08-17,23,0
2159,KOLAR,SRINIVASAPURA,GAUNIPALLI (KODIPALLI) (GP),13.49694,78.22772,14-08-17,23,0
2159,KOLAR,SRINIVASAPURA,GAUNIPALLI (KODIPALLI) (GP),13.49694,78.22772,14-08-17,23,0
2159,KOLAR,SRINIVASAPURA,GAUNIPALLI (KODIPALLI) (GP),13.49694,78.22772,14-08-17,23,0
3358,KOLAR,SRINIVASAPURA,Gownipalli (GP),13.512034,78.22772,14-08-17,0,0
3358,KOLAR,SRINIVASAPURA,Gownipalli (GP),13.512034,78.22772,14-08-17,0,0
3358,KOLAR,SRINIVASAPURA,Gownipalli (GP),13.512034,78.22772,14-08-17,0,0
3358,KOLAR,SRINIVASAPURA,Gownipalli (GP),13.512034,78.22772,14-08-17,0,0
3317,KOLAR,SRINIVASAPURA,Hodali (GP),13.358552,78.269189,14-08-17,0,0
3317,KOLAR,SRINIVASAPURA,Hodali (GP),13.358552,78.269189,14-08-17,0,0
3317,KOLAR,SRINIVASAPURA,Hodali (GP),13.358552,78.269189,14-08-17,0,0
3317,KOLAR,SRINIVASAPURA,Hodali (GP),13.358552,78.269189,14-08-17,0,0
3317,KOLAR,SRINIVASAPURA,Hodali (GP),13.358552,78.269189,14-08-17,1,0
3317,KOLAR,SRINIVASAPURA,Hodali (GP),13.358552,78.269189,14-08-17,1,0
3317,KOLAR,SRINIVASAPURA,Hodali (GP),13.358552,78.269189,14-08-17,1,0
3317,KOLAR,SRINIVASAPURA,Hodali (GP),13.358552,78.269189,14-08-17,1,0

Jotne · Accepted Answer · 2019-09-26 06:40:58Z

2

This awk should do:

cat file
2159,23,45,45,13.512034,78.226233

awk -F, -v OFS="," '$1==2159 && $5==13.512034 {$5="13.49694"} $1==2159 && $6==78.226233 {$6="78.22772"} 1' file
2159,23,45,45,13.49694,78.22772

This $1 ~ /^2159/ does starts with, not equal to. $1=2159 or $1~/^2159$/

edited Sep 26, 2019 at 6:40

answered Sep 26, 2019 at 6:27

Jotne

41.7k13 gold badges54 silver badges58 bronze badges

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

3 Comments

Ajay Over a year ago

Is there any way to update the output into same file instead of directing console output to new file.

Jotne Over a year ago

@Ajay Update file is done like this awk 'comands' file > tmp && mv tmp file

James Brown Over a year ago

@Ajay Also, if you are using recent enough GNU awk version: stackoverflow.com/a/16531920/4162356 .

RavinderSingh13 · Accepted Answer · 2019-09-26 08:15:23Z

1

Using sub():

$ awk '
BEGIN {
    FS=OFS=","
}  
$1==2159 {                             # only one condition 
    sub(/^13\.512034$/,"13.49694",$5)   # or if($5=="13.512034") $5="13.49694"
    sub(/^78\.226233$/,"78.22772",$6)   # ditto
} 
1' file

Output:

2159,23,45,45,13.49694,78.22772

edited Sep 26, 2019 at 8:15

RavinderSingh13

135k14 gold badges61 silver badges100 bronze badges

answered Sep 26, 2019 at 6:36

James Brown

37.7k8 gold badges52 silver badges64 bronze badges

Collectives™ on Stack Overflow

conditional based find and replace values in fields of csv file

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related