Linux sed replace comma at certain position

user979974 0
Viewed 71

I have a csv with a weird format, I would like to change decimal values by a point and keep the comma as separator. The issue is that sometimes I have 3 commas and sometimes 4 commas.

For example:

30,-4,098511E-02
30,05,-4,098511E-02
66,7,-1,865433

I need to transform to:

30,-4.098511E-02
30.05,-4,098511E-02
66.7,-1.865433

I don't know if that's possible to do it with sed or awk. I tried something like sed -i 's/\(([0-9]+,?){1,2}),/\1./g' P7.csv > P7_test.csv with no success

awk sed csv linux
4 Answers

Assuming you know there are only 2 columns and the second column is always a floating-point number (when there are any), you can use this:

awk -F, 'NF == 3 { $0 = $1FS$2"."$3 } NF == 4 { $0 = $1"."$2FS$3"."$4 } 1' infile
userNnA50

You could easily do this tsk in awk. Please try following code written and tested in GNU awk.

awk '
BEGIN{ FS=OFS="," }
NF==3{
  match($0,/(^[^,]*),([^,]*),(.*$)/,arr)
  print arr[1],arr[2]"."arr[3]
  next
}
NF==4{
  match($0,/(^[^,]*),([^,]*),([^,]*),(.*)$/,arr)
  print arr[1]"."arr[2],arr[3]"."arr[4]
}
'   Input_file

NOTE: In case you want to save output into Input_file itself then append > temp && mv temp Input_file to above code.

Explanation: Adding detailed explanation for above awk code.

awk '                                            ##Starting awk program from here.
BEGIN{ FS=OFS="," }                              ##Setting FS and OFS as comma in BEGIN section of this awk program.
NF==3{                                           ##Checking if there are 3 number of fields in current line then do following.
  match($0,/(^[^,]*),([^,]*),(.*$)/,arr)         ##Using match to match regex (^[^,]*),([^,]*),(.*$) to create 3 capturing group to be saved into arr array.
  print arr[1],arr[2]"."arr[3]                   ##Printing 1st element of arr followed by comma followed by 2nd element followed by DOT followed by 3rd element of arr.
  next                                           ##next will skip all further statements from here.
}
NF==4{                                           ##Checking if there are 4 number of fields in current line then do following.
  match($0,/(^[^,]*),([^,]*),([^,]*),(.*)$/,arr) ##Using match to match regex (^[^,]*),([^,]*),([^,]*),(.*)$ to create 4 capturing group to be saved into arr array.
  print arr[1]"."arr[2],arr[3]"."arr[4]          ##Printing 1st element of arr followed by dot followed by 2nd element followed by comma followed by 3rd element of arr, followed by DOT followed by 4th element of arr.
}
'  Input_file                                    ##Mentioning Input_file name here.
RavinderSingh130

The question is internally inconsistent. I will assume if a line has 3 commas then we want to change the first and last comma to a period. Otherwise the line has 2 commas and we only want to change the last comma to a period:

$ sed -E '/(,[^,]+){3}/ { s/^([^,]+),/\1./ }; s/,([^,]+)$/.\1/' P7.csv
30,-4.098511E-02
30.05,-4.098511E-02
66.7,-1.865433

I suggest you regenerate the data from the original source in an unambitious format if at all possible. The heuristic you asked us to implement which may introduce (data) errors.

This invocation you mention will modify the file P7.csv and write a blank file P7_test.csv. This is probably not what you want (where ... is some script):

sed -i ... P7.csv > P7_test.csv
Allan Wind0

You can try this sed:

cat file
30,-4,098511E-02
66,7,-1,865433
42,35,18,15199
30,05,-4,098511E-02

sed -E 's/^([0-9]*),([0-9])/\1.\2/; s/([0-9]),([0-9E-]*)$/\1.\2/' file

30,-4,098511E-02
66.7,-1.865433
42.35,18.15199
30.05,-4.098511E-02

Here ^([0-9]*),([0-9]) matches 2 digits at start separated by a , in capture groups 1 and 2. In replacement we place a dot between captured values.

anubhava0
Related