How to create a sed script with extended regular expressions

Question

I have two sed commands that work perfectly fine when executed separately on a csv file from the command line. Both of them are designed to fix a format of decimals in one of the csv file columns and round the decimals to two.

- this one adds a 0 to the numbers with only one decimal:

sed -r -e  's/[0-9]\;[0-9]+\.[0-9]/&0/'

- this one rounds the numbers with more than two decimals:

sed -re 's/([0-9]+\.[0-9]{2})[0-9]+/\1/' 

Now I would like to put them together in a sed script to be able to apply them at the same time. I have created this script named fixed_floats.sed

    #!/bin/sed

s/[0-9]\;[0-9]+\.[0-9]/&0/
s/(\[0-9]+\.[0-9]{2}\)[0-9]+/\1/

When I try to execute it with this command:

sed -f fix_floats.sed titanic-passengers.csv

the output does not present any changes (the regular expressions in the sed commands are extended so I think the reason why it does not work is that I do not specify this when executing the script)

When I try to execute it with this command:

sed -E -f fix_floats.sed titanic-passengers.csv

I get the following error:

sed: file fix_floats.sed line 5: invalid reference \1 on `s' command's RHS

Any advises on how to create a sed script with extended regular expressions to be able to apply it on a csv effectively?

Original Output: (The column of interest is the 3rd one form the end of the row)

356;No;3;Vanden Steen, Mr. Leo Peter;male;28.0;0;0;345783;9.5;;S
546;No;1;Nicholson, Mr. Arthur Ernest;male;64.0;0;0;693;26.0;;S

Desired Output:

356;No;3;Vanden Steen, Mr. Leo Peter;male;28.0;0;0;345783;9.50;;S
546;No;1;Nicholson,Mr.Arthur Ernest;male;64.0;0;0;693;26.00;;S

Answer 1

The last line of your script is not identical to the one that works on the command line. There is no reason to add those two backslashes.

s/(\[0-9]+\.[0-9]{2}\)[0-9]+/\1/

should be

s/([0-9]+\.[0-9]{2})[0-9]+/\1/

(also, you don't need to escape the semi-colon, in your first s/// command, but this is not an issue, as it is just ignored)

Answer 2

This like these are easier to de with a language that allows for formatting floating point numbers:

$ awk -F ';' 'BEGIN { OFS=FS } { $(NF-2) = sprintf("%.2f", $(NF-2)) }; 1' file
356;No;3;Vanden Steen, Mr. Leo Peter;male;28.0;0;0;345783;9.50;;S
546;No;1;Nicholson, Mr. Arthur Ernest;male;64.0;0;0;693;26.00;;S

This awk program is rewriting the 3rd ;-delimited field from the end as a floating point number with two decimals. This will perform rounding, so that 0.009 becomes 0.01. Use int($(NF-2)*100)/100) in place of just $(NF-2) in the sprintf() call to do truncation instead.

---

Your sed expression has too many backslashes. In particular, you use \) where you should have ) to end a capture group in an extended regular expression, and you have \[ in place of [ which disables an important bracket expression, and a needless \; in place of ;.

With standard sed expressions:

s/\(\.[0-9]\)\(\(;[^;]*\)\{2\}\)$/\10\2/
s/\(\.[0-9][0-9]\)[0-9]\{1,\}\(\(;[^;]*\)\{2\}\)$/\1\2/

The same thing as extended regular expressions (currently non-standard to use with sed -E):

s/(\.[0-9])((;[^;]*){2})$/\10\2/
s/(\.[0-9][0-9])[0-9]+((;[^;]*){2})$/\1\2/

These should be safer than your expressions as they explicitly match the last two fields after the field we modify. We therefore run less risk of accidentally modifying random floating point values.