GertMT has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks,

Working my way through some spreadsheet-like calculations with the use of Data::Table I have a problem with "empty cells". It's best described with the sample below. I did put an empty value in the 3 row of the 2nd column. In the extra created 3rd column I'm creating a row-total. If I run the code I get

Argument "" isn't numeric in addition (+) at...

I understand that, as there is no value in the 3 row of the second column.

Is there a way (except for leaving out use strict, warnings) to work around this? Or how can I make it numeric?
Thanks,
Gert 
#!/usr/bin/perl -w
use strict;
use diagnostics;
use Data::Table;

my $t = new Data::Table( [ [ 1, 2, 3 ], [ 10, 20, " " ] ], [ 'A', 'B' 
+], 1 );

# create emtpy column
my @newCol = ();
$newCol[ $t->nofRow - 1 ] = undef;
$t->addCol( \@newCol, 'adding_up' );

# Put the formula in the empty column
$t->colsMap( sub { $_->[2] = $_->[0] + $_->[1] } );
print $t->csv;
exit;

[download]

Replies are listed 'Best First'.
Re: Data::Table - empty values - avoid warnings
by andreas1234567 (Vicar) on Oct 24, 2007 at 16:22 UTC
    One possible solution would be to make sure you check that the cell value is in fact numeric (or an integer as shown below) before you use in a calculation: 
    $ perl
use strict;
use warnings;
use Data::Table;

my $t = new Data::Table([ [ 1, 2, 3 ], [ 10, 20, " " ] ], [ 'A', 'B' ]
+, 1);

# create emtpy column
my @newCol = ();
$newCol[ $t->nofRow - 1 ] = undef;
$t->addCol(\@newCol, 'adding_up');

# Put the formula in the empty column
$t->colsMap(
  sub {
    $_->[2] =
      ((defined($_->[0]) && $_->[0] =~ m/^\d+$/) ? $_->[0] : 0) +
      ((defined($_->[1]) && $_->[1] =~ m/^\d+$/) ? $_->[1] : 0);
  }
);
print $t->csv;

__END__
A,B,adding_up
1,10,11
2,20,22
3, ,3

    [download]
    See also How do I determine whether a scalar is a number/whole/integer/float?.
    --
    Andreas
[reply]
[d/l]
      ((defined($_->[0]) && $_->[0] =~ m/^\d+$/) ? $_->[0] : 0)

      [download]
      Am I correct if I assume that here $_ is defined as 'one single digit or maybe a few digits (d+, no spaces and stuff like that in between the digits) and then if this is not the case it should be a 0 (zero)?
      Almost. It means that $_->[0] is
      • defined, and
      • one or more digits 0-9
      I would have made it a function to make it more readable: 
      $ perl
use strict;
use warnings;
use Data::Table;

my $t = new Data::Table([ [ 1, 2, 3 ], [ 10, 20, " " ] ], [ 'A', 'B' ]
+, 1);

# create emtpy column
my @newCol = ();
$newCol[ $t->nofRow - 1 ] = undef;
$t->addCol(\@newCol, 'adding_up');

# Return input if input is an integer, otherwise zero
sub to_int_or_zero {
  my $value = shift;
  if (defined($value) &&
      $value =~ m/^\d+$/) {
    return $value;
  } else {
    return 0;
  }
}

# Put the formula in the empty column
$t->colsMap(
  sub {
    $_->[2] =
      to_int_or_zero($_->[0]) +
      to_int_or_zero($_->[1]);
  }
);
print $t->csv;

__END__
A,B,adding_up
1,10,11
2,20,22
3, ,3

      [download]
      --
      Andreas
[reply]
[d/l]
[select]
        okay thanks a lot. I'm almost there but found out that actually the columns I'm working on have a lot of numbers that have as a decimal separator a 'comma'. For example in the blank spot number like 2,3 as opposed to the 'space'. To work around that I thought to do 
        sub to_int_or_zero {
  my $value = shift;
$value =~ s/,/\./;
  if (defined($value) &&
      $value =~ m/^\d+$/) {
    return $value;
  } else {
    return 0;
  }
}

        [download]
        but it doesn't work. The 2,3 nicely changes into 2.3 but I'm obviously making a very elementary mistake that I can't understand myself at this moment as the row adds up to ... 3
        : (
[reply]
[d/l]
      thanks for your reply. I'm a bit puzzled with the regex. 
            ((defined($_->[0]) && $_->[0] =~ m/^\d+$/) ? $_->[0] : 0)

      [download]

      Am I correct if I assume that here $_ is defined as 'one single digit or maybe a few digits (d+, no spaces and stuff like that in between the digits) and then if this is not the case it should be a 0 (zero)? Never used this ternary operator and just want to make sure that's what it is.
      thanks
[reply]
[d/l]
        Am I correct if I assume that here $_ is defined as 'one single digit or maybe a few digits ...

        No, $_ is array reference not a scalar. $_ is not being tested for simple number-ness, but the deferenced value, $_->[0], is. Other than that, you are correct in your reading about which value is eventually going to be added.

        - parv

[reply]
[d/l]
[select]
Re: Data::Table - empty values - avoid warnings
by moritz (Cardinal) on Oct 24, 2007 at 16:05 UTC
    You can always say no warnings 'uninitialized'; no warnings 'numeric'; to disable this warning in the current scope. And do try to keep this scope small.

    Thanks to ikegami for the correction.

[reply]
[d/l]
[select]

      Wrong warning category. 

      >perl -wle"no warnings 'uninitialized'; print '' + 4"
Argument "" isn't numeric in addition (+) at -e line 1.
4

>perl -wle"no warnings 'numeric'; print '' + 4"
4

      [download]

      When in doubt use diagnostics or consult perldiag. 

      >perl -Mdiagnostics -wle"print '' + 4"
Argument "" isn't numeric in addition (+) at -e line 1 (#1)
    (W numeric) The indicated string was fed as an argument to an oper
+ator
    that expected a numeric value instead.  If you're fortunate the me
+ssage
    will identify which operator was so unfortunate.

4

      [download]
[reply]
[d/l]
[select]
        Thanks, often it directs me to the solution as well but couldn't think of a 'generic' enough fix. I tried something like:

        $t->colsMap(sub { $_->[2] = $_->[0] + $_->[1] if ( length( $_->[1] ) > 0 ) } );

        but that only worked in case of blanks. Happy to have found some alternatives.
        thanks again.
[reply]
[d/l]

Back to Seekers of Perl Wisdom