rounding csv values

[erbrose]

Hey all,
still a newbie to perl and programming and still needing some advice.. I have a ".CSV" table that I need to do some (rounding) manipulation on. the table looks something like this.. (the real table is actually comma delimited)

DD | H1 | H2 | H3 | H4
---------------------------------------------------
01 | 69.6 | -1 | 55.215 | 25.1231
02 | -1 | 1.23 | 1.45 | -1

I need it to look like this

DD | H1 | H2 | H3 | H4
---------------------------------------------------
01 | 69.9 | -1 | 55.2 | 25.1
02 | -1 | 1.2 | 1.5 | -1

basically I need to round to the tenth ONLY if the value contains more then 1 value after the decimal and i need the values that are "-1" to stay as "-1"

I've read a bunch of tutorials and still just don't get it.. i can get as far as opening the table but not parsing through it...

#!/usr/bin/perl
use strict;
use warnings;
 
my $fileInput="d:/temp/test.csv";
my $fieldnames = 1;
 
open (READ, ">>$fileInput") or die "Can't open $fileInput: $!";
 
my $header;
$header = <READ> if $fieldnames;
 
my @data = <READ>;
 
print READ $header;

i've also read that this will work for rounding

my($number) = shift;
return int($number + .5)

just not sure how to get to $number

thanks immensely ahead of time!
Cheers,
Eric

running locally on windows XP

[miller]

Greetings Eric,

Now there are a lot of way to do this. Some rather perlish ways would actually take only 1 line of code. However, the following script is an example of how to program this responsibly. It uses two modules that will require installation, but this is how I would do what you proposed:

use Math::Round::Var;
use Text::CSV;
use Tie::File;
 
use strict;
 
my $file = 'test.csv';
 
tie my @array, 'Tie::File', $file or die "Can't open $file: $!";
 
my $csv = Text::CSV->new();
my $rnd = Math::Round::Var->new(0.1);
 
for my $i (1..$#array) { # Note, skip 0 index with Header
    # Parse CSV
    $csv->parse($array[$i]) or die "parse() failed: ", $csv->error_input;
 
    # Round number fields.
    my @fields = $csv->fields;
    for my $j (0..$#fields) {
        # Decimal with 2 digit precision
        if ($fields[$j] =~ m{^-?\d*\.\d{2,}$}) {
            $fields[$j] = $rnd->round($fields[$j]);
        }
    }
 
    # Resave CSV data
    $csv->combine(@fields) or die "combine() failed: ", $csv->error_input;
    $array[$i] = $csv->string;
}
 
1;
 
__END__
 

This takes in the following input file:

"DD","H1","H2","H3","H4"
"01","69.6","-1","55.215","25.1231"
"02","-1","1.23","1.45","-1"
 

And changes that file to the following:

"DD","H1","H2","H3","H4"
"01","69.6","-1","55.2","25.1"
"02","-1","1.2","1.5","-1"
 

- Miller

[KevinADC]

Miller,

why not use "sprintf" and "%.1f" ? I realize this will add a .0 (dotzero) to whole numbers but it seems like that would easy to fix if necessary.

[miller]

Kevin,

You mean actually search perldoc before answering a question. Why would I do that? :)

perldoc search "round"
returns:
perldoc perlfaq4 Does perl have a round() function?

I simply didn't remember that sprintf had that functionality, because from my viewpoint it should simply truncate. However, actually testing it on data proves that it does in fact round, and amusingly enough that is the implementation method that the Math::Round::Var module uses for decimal rounding. Which probably proves why it's only in version .03 and is likely little used.

That changes my code only slightly, but does clean it up a little.

use Text::CSV;
use Tie::File;
 
use strict;
 
my $file = 'test.csv';
 
tie my @array, 'Tie::File', $file or die "Can't open $file: $!";
 
my $csv = Text::CSV->new();
 
for my $i (1..$#array) { # Note, skip 0 index with Header
    # Parse CSV
    $csv->parse($array[$i]) or die "parse() failed: ", $csv->error_input;
 
    # Round number fields.
    my @fields = $csv->fields;
    for my $j (0..$#fields) {
        # Decimal with 2 digit precision
        if ($fields[$j] =~ m{^-?\d*\.\d{2,}$}) {
            $fields[$j] = sprintf "%.1f", $fields[$j];
        }
    }
 
    # Resave CSV data
    $csv->combine(@fields) or die "combine() failed: ", $csv->error_input;
    $array[$i] = $csv->string;
}
 
1;
 
__END__
 

Note, for the "Round number fields" section, I personally would use the following code. But I figure the former is easier to understand for someone new to perl.

    my @fields = map {
        s{^(-?\d*\.\d{2,})$}{sprintf "%.1f", $1}e; $_
    } $csv->fields;
 

Thanks for the little reminder Kevin,
- Miller

[erbrose]

Thanks again for taking the time to enlighten a newbie! I did just get "programming perl" so hopefully my questions will come less and less and perhaps my replies more and more..
One more thing though.. the input .csv file does not contain 'double quotes' around each data value, but the script output does. The only problem with this is the text files I am processing on are >~200mb as inputs then >~300mb as outputs and we were hoping to reduce the files sizes just a bit by rounding the numbers. Im going to keep searching and hopefully find a solution! but some more insight will always be appreciated!
Cheers,
Eric

[miller]

There's just no making you happy is there? No matter, your question made me learn something. A better module to use is Text::CSV_XS as it uses a C implementation of the previous module and is therefore much faster. It also has been extended with a lot of additional functionality, including only quoting values that truly require it. They are stylistically the same, so only a renaming of the used module is required.

Final version:

use Text::CSV_XS;
use Tie::File;
 
use strict;
 
my $file = 'test.csv';
 
tie my @array, 'Tie::File', $file or die "Can't open $file: $!";
 
my $csv = Text::CSV_XS->new();
 
for my $i (1..$#array) { # Note, skip 0 index with Header
    # Parse CSV
    $csv->parse($array[$i]) or die "parse() failed: ", $csv->error_input;
 
    # Round number fields.
    my @fields = $csv->fields;
    for my $j (0..$#fields) {
        # Decimal with 2 digit precision
        if ($fields[$j] =~ m{^-?\d*\.\d{2,}$}) {
            $fields[$j] = sprintf "%.1f", $fields[$j];
        }
    }
 
    # Resave CSV data
    $csv->combine(@fields) or die "combine() failed: ", $csv->error_input;
    $array[$i] = $csv->string;
}
 
1;
 
__END__
 

[KevinADC]

You mean actually search perldoc before answering a question. Why would I do that? :)

LOL @ Miller

[erbrose]

There's just no making you happy is there?
Im happy now :)
i was reading about the text::csv_xs too but didn't see where it would remove the quotes.... got a long way to go!!
Thanks again Miller and Kevin!
Cheers,
Eric

[KevinADC]

You're welcome


.....