How to use awk to edit multiple columns of csv-file

Question

I would like to use awk to edit a .csv-file with different type of values, for example round all values of several distinct columns to the second decimal place. The other rows may contain float-values as well, but those shall be handled in a different way.

Assuming one row looks like the one below and I would like to edit the 7th, 8th, and 9th column (in fact they are much longer, but all relevant type of values are in this example, so it's just a matter of scale):

L ,P_005 ,250.092 ,20.0 ,-0.80 ,0.803443 ,0.23342 ,0.83728329 ,0.0 ,0.0

The output should be

L ,P_005 , 250.092, 20.0, -0.80, 0.80, 0.23, 0.84, 0.0, E2=0.0

Up to now I've always round the value of one column "x" using:

#/bin/sh
OLDIFS=$IFS
IFS = ","
file=$1
...
awk '{printf "%.2f",$x}' $1
...   
IFS=$OLDIFS

So how do I adapt this concept in a way, where I can

edit multiple columns and
display the entire csv-file

without writing down each column with a specific option?

edit your question to contain a minimal reproducible example including concise, testable sample input and expected output. Use the {} editor button for your script, input, and output. What you have shown as the script you currently use is not valid awk syntax - please don't show us one thing and tell us it's something else as that just obfuscates your question and makes it harder for us to help you. — Ed Morton
– Ed Morton, Commented Jun 30, 2016 at 13:28
Thank you for the advice. I hope my edits suit the requirements — krouch
– krouch, Commented Jun 30, 2016 at 13:53
When you post 1 line of sample input/output you're likely to get a solution that will only work for that 1 line of input. The more effort you put into asking your question (e.g. include any rainy day and/or edge cases you could envision occuring in your input) the more likely you are to get a robust solution. — Ed Morton
– Ed Morton, Commented Jun 30, 2016 at 15:36

James Brown · Accepted Answer · 2016-06-30 14:58:32Z

2

If I got your guestion right:

awk -F\; -v OFS=\; '
function foo(str) {
  if(match(str, /[0-9]+\.[0-9]+/, arr)) {
    gsub(/[0-9]+\.[0-9]+/, sprintf("%.2f",arr[0]), str)
  } 
  return str
} 
{
  for(i=1; i<=NF; i++) 
    printf "%s%s", foo($i),(i<NF?OFS:ORS)
}' test.in

It produced:

L;P_005;X 250; Y 20; Z 0; A 0.80; B 0.23; C 0.84; E1=0.00; E2=0.00 ,

.

answered Jun 30, 2016 at 14:58

James Brown

37.7k8 gold badges52 silver badges64 bronze badges

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

2 Comments

karakfa Over a year ago

if you change + to {2,} in the regex, you'll get the desired output format L;P_005;X 250; Y 20; Z 0; A 0.803443; B 0.23342; C 0.83728329; E1=0.0; E2=0.0 ,

Ed Morton Over a year ago

You should mention that only works for GNU awk due to the 3rd arg for match(). More importantly, this is a good example of a script that will work for the posted sample input but will fail for other potential inputs. Try changing P_005 to P_00.5 for example and you'll find it gets output as P_0.50.

Collectives™ on Stack Overflow

How to use awk to edit multiple columns of csv-file

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related