[SOLVED] Muliply a range of numbers by a factor

Bartonsen · 10-16-2018, 11:39 AM

I have a CSV file "test.txt" that looks like this:

201801 ; Rose ; 12 ; 7; 4 ; 0 ; 281 ; 32 ; etc
201801 ; Edwin ; 10 ; 8; 1 ; 9 ; 9 ; 60 ; etc
201802 ; Pearl ; 1 ; 1; 1 ; 39 ; 608 ; ; ; 7 etc

I want to modify this file so that all numbers from position 3 until end of line (28..31 numbers, ie days of month) are multiplied by a factor 9.5, so that the output looks like the below. Note that some days might lack a value. In that case output shall be 0.

Output:
201801 ; Rose ; 114 ; 66.5; 38 ; 0 ; 2669.5 ; 304 ; etc ...
201801 ; Edwin ; 95 ; 76; 9.5 ; 85.5 ; 85.5 ; 570 ; etc ...
201802 ; Pearl ; 9.5 ; 9.5; 9.5 ; 370.5 ; 5776 ; 0 ; 0 ; 66.5 etc

Any ideas?

bradvan · 10-16-2018, 12:27 PM

I think this should do it:

Code:

#!/usr/bin/perl
use strict;
use warnings;
my $file = 'test.txt';
my $outfile = 'test-out.txt';
open (my $fh, $file) or die "Could not open file, '$file' $!";
open (my $wfh, '>', $outfile) or die "Could not open file, '$outfile' $!";
while (my $row = <$fh>) {
   my @values = split(';', $row );
   for (my $i=2; $i<8; $i++) {
      if ( $values[$i] eq '' ){
         $values[$i] = 0;
      }
      $values[$i] = $values[$i] * 9.5;
   }
   for (my $i=0; $i<8; $i++) {
      printf $wfh "$values[$i] ; ";
   }
   printf $wfh "$values[8]";
}
close $fh;
close $wfh;

rtmistler · 10-16-2018, 12:40 PM

Moved: This thread is more suitable in Programming and has been moved accordingly to help your thread/question get the exposure it deserves.

rtmistler · 10-16-2018, 12:42 PM

My first reply would have been "What have you tried? And what avenue (or language) did you wish to pursue to solve this? Along with what is your expertise with scripting languages or other?"

But it appears that a proposed solution has been posted.

Please try that out and update your fellow forum members if you feel that solved your problem, or if you have further questions.

Bartonsen · 10-16-2018, 02:54 PM

Thanks for the replies.
The reason I didn't say anything about what I've tried here, is that I didn't know how to approach this problem.
I'm a little bit familiar with awk and sed, and that would have been my avenue to pursue to solve this, if it would have been do-able.
But perl will work as well, so thanks a lot for the perl suggestion.

I did try it out, and found a few things I would like to comment:

1) I replaced the value 8 in the script with a higher value (38), since the number of words on each line is "2 + the number of days in month", ie. between 30 and 33 numbers in total per line. Is it ok with a high number like 38, that is higher than the exact number of words in the line?

2) An empty value (ie only space between to semicolons) seems not to be acceptable? Got error:
Argument " " isn't numeric in multiplication (*) at ./script.pl line 14, <$fh> line 3.
(I think I can find ways to get around this error, e.g. by search and replace "; ;" with ";0;".

3) The output test-out.txt seems to all come on 1 line? How can I get the output on 3 lines (as in the input file)? I have of course more than 3 lines, but you get the idea...

Thanks!

individual · 10-16-2018, 05:09 PM

Here is my version that updates the input file.

Code:

#!/usr/bin/env perl

use strict;
use warnings;
use constant FILE => 'file.csv';

my @rows;

open my $fh, '<', FILE or die $!;
while (my $row = <$fh>) {
    my @columns = split(/\s?;\s?/, $row);
    my @values = map { /^[0-9]+$/ ? ($_ or 0) * 9.5 : $_ } splice(@columns, 2);
    unshift @values, $columns[1];
    unshift @values, $columns[0];
    push @rows, join(';', @values);
}
close $fh;

open $fh, '>', FILE or die $!;
for my $row (@rows) {
    print $fh "$row\n";
}
close $fh;

The output is:

Code:

201801;Rose;114;66.5;38;0;2669.5;304
201801;Edwin;95;76;9.5;85.5;85.5;570
201802;Pearl;9.5;9.5;9.5;370.5;5776;;;66.5

EDIT: You can also get fancy and modify the row in-place. This version was just for my amusement, but you can still use it for your purpose.

Code:

#!/usr/bin/env perl

use strict;
use warnings;
use constant FILE => 'file.csv';

my @rows;

open my $fh, '<', FILE or die $!;
while (my $row = <$fh>) {
    my @columns = split(/\s?;\s?/, $row);
    @columns[2 .. $#columns] = map { /^[0-9]+$/ ? ($_ or 0) * 9.5 : $_ } @columns[2 .. $#columns];
    push @rows, join(' ; ', @columns);
}
close $fh;

open $fh, '>', FILE or die $!;
for my $row (@rows) {
    print $fh "$row\n";
}
close $fh;

danielbmartin · 10-16-2018, 09:36 PM

With this InFile ...

Code:

201801 ; Rose ; 12 ; 7; 4 ; 0 ; 281 ; 32
201801 ; Edwin ; 10 ; 8; 1 ; 9 ; 9 ; 60
201802 ; Pearl ; 1 ; 1; 1 ; 39 ; 608 ; ; ; 7

... this awk ...

Code:

awk -F";" '{for (j=3;j<=NF;j++) $j=9.5*$j;
  gsub(/ +/," ; "); print}' $InFile >$OutFile

... produced this OutFile ...

Code:

201801 ; Rose ; 114 ; 66.5 ; 38 ; 0 ; 2669.5 ; 304
201801 ; Edwin ; 95 ; 76 ; 9.5 ; 85.5 ; 85.5 ; 570
201802 ; Pearl ; 9.5 ; 9.5 ; 9.5 ; 370.5 ; 5776 ; 0 ; 0 ; 66.5

Daniel B. Martin

.

Bartonsen · 10-17-2018, 01:52 AM

Fantastic awk!

Thanks Daniel!!!

danielbmartin · 10-17-2018, 09:06 AM

The gsub used in the solution shown in post #7 is not necessary.
This is a streamlined version ...

Code:

awk '{for (j=3;j<=NF;j++) $j=9.5*$j}1' FS=OFS=" ; " $InFile >$OutFile

Daniel B. Martin

.

Bartonsen · 10-17-2018, 10:42 AM

Thanks Daniel!
I'll try it out tomorrow.

teckk · 10-17-2018, 12:14 PM

With bash and friends, modify however you want.

Code:

data="
201801 ; Rose ; 12 ; 7; 4 ; 0 ; 281 ; 32 ; etc 
201801 ; Edwin ; 10 ; 8; 1 ; 9 ; 9 ; 60 ; etc
201802 ; Pearl ; 1 ; 1; 1 ; 39 ; 608 ; ; ; 7 etc
"

for words in $data; do
    words=$(cut -d ";" -f1 <<< "$words")
    if [[ "$words" =~ ^2018 ]]; then
        printf "%s" ""$words" ;"
    elif [[ "$words" =~ ^[1-9] ]]; then
        math=$((${words}00 * 95 / 10))
        printf "%s" "${math:0:-2}.${math: -2} ;"
    elif [[ "$words" == "etc" ]]; then
        echo "$words"
    else
        printf "%s" ""$words" "
    fi
done <<< "$data"

bradvan · 10-18-2018, 04:29 AM

Isn't comp sci great? Always more than one way to skin a cat!