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

Hi, I have a question regarding excel related. I want to change my numerical values of @output5b from zero into 1. However, when I use the substitute method (last line of code), all the values of 0 including the numbers that include 0 will change into 1. However, I only want those cells that contain only a "0" to be "1". How do I go about it? Thanks :)
for(my $i = 0; $i < $originalfilecount; $i++) { if($first) { $first = 0; #print OUT5 "\t$resultarray[3]"; $outputb = "\t$resultarray[3]"; } } #print OUT5 "\n"; push(@output5a, $outputa); push(@output5b, $outputb); for(@output5b){s/0/1/g}; #convert output from 0 to 1

Replies are listed 'Best First'.
Re: Changing the numerical value from 0 to 1
by Kenosis (Priest) on Jan 21, 2014 at 01:35 UTC

    You could match w/word boundaries:

    for (@output5b) { s/\b0\b/1/ };

    Or you could match from start to end:

    for (@output5b) { s/^0$/1/ }

    Or w/o a regex:

    for (@output5b) { $_ = 1 if $_ == 0 }

    Edit: I believe that ww and kcott provided sufficient reasons below to keep only option #2.

      Word boundaries work !:) Thanks!

        You're most welcome.

Re: Changing the numerical value from 0 to 1
by kcott (Archbishop) on Jan 21, 2014 at 15:34 UTC

    G'day hellohello1,

    Welcome to the monastery.

    Here's a short script that tests solutions offered:

    #!/usr/bin/env perl use strict; use warnings; my @tests = ('0', '1', '000', '101', '010', '1 * 0 = 0'); my $format = '%9s | ' x 4 . "\n"; printf $format => qw{initial boundary start/end assign}; for (@tests) { my ($init, $bound, $start, $assign) = ($_) x 4; $bound =~ s/\b0\b/1/; $start =~ s/^0$/1/; $assign = 1 if $assign == 0; printf $format => $init, $bound, $start, $assign; }

    Output:

    initial | boundary | start/end | assign | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 000 | 000 | 000 | 1 | 101 | 101 | 101 | 101 | 010 | 010 | 010 | 010 | Argument "1 * 0 = 0" isn't numeric in numeric eq (==) at ./pm_example. +pl line 16. 1 * 0 = 0 | 1 * 1 = 0 | 1 * 0 = 0 | 1 * 0 = 0 |

    Results:

    • Word boundaries fail with input like '1 * 0 = 0'
    • Anchoring to the start and end of the string is successful in all instances
    • Assignment conditional on equality to zero fails in two ways:
      1. A string that is not '0' but is evaluated as being numerically equal to zero (i.e. '000')
      2. A string that cannot be evaluated as being numeric generates a warning (i.e. '1 * 0 = 0')

    So, with the data and solutions used here, only s/^0$/1/ works consistently.

    You can change @tests to a list of representative examples from your data to get more realistic output. You can add tests for any other solutions that may present themselves.

    Also, if you find more than one solution that works, and processing speed is important to you, you can compare them with Benchmark.

    -- Ken

      Hi, I'm back again. Somehow, the word boundaries doesn't seem to work for some files, which some "0" remains as "0". I have tried
      push(@output5b, $outputb); for (@output5b) { s/^0$/1/};
      But the zero does not change to 1 at all. I am not sure why is that so and whether there might have something that I missed out.

        You'll need to show the data in order for us to provide a meaningful response.

        If you actually print your values, you may be able to see why the substitution isn't working for yourself.

        -- Ken

Re: Changing the numerical value from 0 to 1
by ww (Archbishop) on Jan 21, 2014 at 13:19 UTC
    If you find word boundaries a bit hard to rely on (ie, like me, probably don't understand all the possible implications) you probably should use the more robust regex (Kenosis's 2nd example]) to dodge the bullet in some of the cases in this code (Updated to include both of Kenosis' other suggestions):
    #!/usr/bin/perl use 5.016; use warnings; # 1071410 change ONLY numbers which are (one digit only) 0 my @arr = qw(20 14 007 0 1203 '' 7900130 43.02 0.03 0.0 ); for my $elem(@arr) { if ( $elem=~ /^0{1}$/ ){ say $elem; my $elem1 = $elem =~ s/^0{1}$/1/; say $elem1; } } say "above, results from Ln1-14; below: Kenosis' 3: \n"; my @arr3 = qw(20 14 007 0 1203 '' 7900130 43.02 0.03 0.0 ); for (@arr3) { print "Original \@arr3 element: |$_| becomes (or remains as) "; $_ = 1 if $_ == 0; say $_; } say "\n Kenosis' 1 (boundary version): "; my @arr1 = qw(20 14 007 0 1203 '' 7900130 43.02 0.03 0.0 ); for (@arr1) { s/\b0\b/1/; print "Original \@arr1 element: |$_| becomes (or remains as) "; say $_; }

    Execution:

    C:\> 1071410.pl 0 1 above, results from Ln1-14; below: Kenosis' 3: Original @arr3 element: |20| becomes (or remains as) 20 Original @arr3 element: |14| becomes (or remains as) 14 Original @arr3 element: |007| becomes (or remains as) 007 Original @arr3 element: |0| becomes (or remains as) 1 Original @arr3 element: |1203| becomes (or remains as) 1203 Argument "''" isn't numeric in numeric eq (==) at 1071410.pl line 20. Original @arr3 element: |''| becomes (or remains as) 1 Original @arr3 element: |7900130| becomes (or remains as) 7900130 Original @arr3 element: |43.02| becomes (or remains as) 43.02 Original @arr3 element: |0.03| becomes (or remains as) 0.03 Original @arr3 element: |0.0| becomes (or remains as) 1 Kenosis' 1 (boundary version): Original @arr1 element: |20| becomes (or remains as) 20 Original @arr1 element: |14| becomes (or remains as) 14 Original @arr1 element: |007| becomes (or remains as) 007 Original @arr1 element: |1| becomes (or remains as) 1 Original @arr1 element: |1203| becomes (or remains as) 1203 Original @arr1 element: |''| becomes (or remains as) '' Original @arr1 element: |7900130| becomes (or remains as) 7900130 Original @arr1 element: |43.02| becomes (or remains as) 43.02 Original @arr1 element: |1.03| becomes (or remains as) 1.03 Original @arr1 element: |1.0| becomes (or remains as) 1.0

    Anchoring a single zero -- as in the match and the substitution above -- seems to me the clearest way of ensuring you substitute only when the spreadsheet cells holds ONLY a single zero.

    CAUTIONARY NOTE: I have not tested this with data from a spreadsheet utilizing formatted color, etc) data... and it still seems likely to fail if the column is formatted with leading zeros or a fixed number of fractional (decimalized) values (for example, if "1/2" were presented as ".50" by formatting the cell to require 2 digits after the decimal point but without requiring a leading "0" before the point).

    Come, let us reason together: Spirit of the Monastery

      You've made some very good points. I now think it would have been best to only offer s/^0$/1/--even though the OP said "Word boundaries work !", evidencing that s/\b0\b/1/ met his/her needs.

      I'm not sure about your array elements. Cells don't contain the 007, 0.03 or 0.0 that you placed into an array (unless, of course, it's text, but the OP seems to suggest that s/he's dealing with numeric values). Those elements are the formatting results of the numeric values 7, .03 and 0, respectively.