shia1717 shia1717 - 23 days ago 9
Perl Question

Add all values in array for each ID in Perl



I have this table:

NAME |12/31/2016|VALUE
AAA |1/31/2017 |10
AAA |2/1/2017 |20
AAA |2/2/2017 |30
AAA |2/3/2017 |40
AAA |2/4/2017 |50
NAME |2/9/2017 |VALUE
BBB |2/10/2017 |20
BBB |2/11/2017 |30
BBB |2/12/2017 |40
BBB |2/13/2017 |50
BBB |2/14/2017 |60


and this would be my desired output:

NAME |DATE |VALUE
AAA |12/31/2016 |150
AAA |1/31/2017 |140
AAA |2/1/2017 |120
NAME |DATE |VALUE
BBB |2/9/2017 |200
BBB |2/10/2017 |180
BBB |2/11/2017 |150


What I want to do is, for each of the valid symbols, (
AAA
,
BBB
) I want to have three rows.

For the first row of each column, I want all the values added,

For example, row 1 value for AAA:

10+20+30+40+50 = 150


then for row 2 I want to just add from the second value to the last.

For example row 2 value for
AAA


20+30+40+50 = 140


and so on same goes for
BBB
.

I want to shift the dates down so that
12/31/2016
would match
AAA
, then get the first three dates for each row.

I currently have this code. but this doesn't do much. it just gives me a bunch of numbers.

use strict;
use warnings;

use Scalar::Util qw(looks_like_number);
use Data::Dumper;

sub uniq {
my %seen;
grep !$seen{$_}++, @_;
}

my %cashflow;
my %fields = (
ID => 0,
DATES => 1,
VALUE => 2,
);

my @total;
my @IDs;
my @uniqueIDs;
my @dates;
my @add;
my $i = 0;
my @values;

my $counter = 3;

open( FILE, "try.CSV" );

while ( my $line = <FILE> ) {
chomp( $line );
my @lineVals = split( /\|/, $line );

if ( $lineVals[ $fields{ID} ] !~ /^SYMBOL$/i ) {
push @IDs, $lineVals[ $fields{ID} ];
}
@uniqueIDs = uniq( @IDs );

#push all CASH FLOW AMOUNTS to @cashflow
if ( looks_like_number( $lineVals[ $fields{VALUE} ] ) ) {
$lineVals[ $fields{VALUE} ] =~ s/\r//;
push @total, $lineVals[ $fields{VALUE} ];
}

if ( $lineVals[ $fields{DATES} ] =~ /(\d{1,2})\/(\d{1,2})\/(\d{4})/ ) {
$lineVals[ $fields{DATES} ] = sprintf( '%04d%02d%02d', $3, $2, $1 );
}

$cashflow{ uc $lineVals[ $fields{ID} ] }{DATES} = $lineVals[ $fields{DATES} ];
$cashflow{ uc $lineVals[ $fields{ID} ] }{VALUE} = $lineVals[ $fields{VALUE} ];

foreach my $ID ( @uniqueIDs ) {

foreach my $symb ( keys %cashflow ) {

if ( $ID = $symb ) {

if ( looks_like_number( $lineVals[ $fields{VALUE} ] ) ) {

$lineVals[ $fields{VALUE} ] =~ s/\r//;
push @total, $lineVals[ $fields{VALUE} ];

my $i = 0;
my $grand = 0;

foreach my $val ( @total ) {

while ( $i < $counter ) {

$grand += $val;
print "$grand \n";
$i++;
}

shift @total;
}
}
}
}
}
}

close FILE;


I'm really stuck with this. I don't know what to do with the problem.

Answer Source

A possible solution:

#!perl
use strict;
use warnings;

sub trim {
    my ($str) = @_;
    s!\A\s+!!, s!\s+\z!! for $str;
    $str
}

my $file = 'try.CSV';    
open my $fh, '<', $file or die "$0: $file: $!\n";

my ($group_name, @dates, @values);
my $sum = 0;

my $print_group = sub {
    return if !defined $group_name;
    my $format = "    %-6s|%-11s|%s\n";
    printf $format, 'NAME', 'DATE', 'VALUE';
    for my $date (@dates) {
        printf $format, $group_name, $date, $sum;
        $sum -= shift @values if @values;
    }
};

while (my $line = readline $fh) {
    my ($name, $date, $value) = map trim($_), split /\|/, $line;
    if ($name eq 'NAME') {
        $print_group->();
        $group_name = undef;
        @dates = $date;
        @values = ();
        $sum = 0;
        next;
    }
    $group_name ||= $name;
    push @dates, $date if @dates < 3;
    push @values, $value if @values < 2;
    $sum += $value;
}
$print_group->();

Let's go over it.

sub trim {
    my ($str) = @_;
    s!\A\s+!!, s!\s+\z!! for $str;
    $str
}

A helper function for removing leading/trailing whitespace from a string. We're using ! as the s delimiter here because / breaks SO's syntax highlighting. Shrug.

my $file = 'try.CSV';    
open my $fh, '<', $file or die "$0: $file: $!\n";

Open our input file. Note: We use a lexical variable ($fh) instead of a bareword filehandle, and we use 3-argument open. This is strongly recommended. We also check open's return value and produce a nice error message in case of failure, including both the name of the file that couldn't be opened ($file) and the reason for failing ($!).

my ($group_name, @dates, @values);
my $sum = 0;

We set up some state variables that we want to preserve across loop iterations. $group_name is the name of the group we're currently processing, @dates is the saved dates we've seen so far, @values is the saved values we've seen so far. $sum is a running sum of all the values in the current group, and it starts at 0.

my $print_group = sub {
    return if !defined $group_name;
    my $format = "    %-6s|%-11s|%s\n";
    printf $format, 'NAME', 'DATE', 'VALUE';
    for my $date (@dates) {
        printf $format, $group_name, $date, $sum;
        $sum -= shift @values if @values;
    }
};

A helper function for printing the output for a single group. If $group_name isn't set, we haven't processed any input for the current group yet, so we do nothing and return. Otherwise we print a NAME | DATE | VALUE header, followed by a row of data for each element in @dates. For each $date we output the current group name (e.g. AAA), $date, and the sum of values (all nicely formatted using printf). Initially $sum is the sum of all group values, but after the first iteration we start subtracting the values from @values: If the list of values in the input was x1, x2, x3, x4, ..., then $sum is initially x1 + x2 + x3 + x4 + ..., and that's what's printed in the first line of output. After that we subtract x1, so the next line gets x1 + x2 + x3 + x4 + ... - x1, which is x2 + x3 + x4 + .... After that we subtract x2, so the third row of data gets x3 + x4 + ....

while (my $line = readline $fh) {
    my ($name, $date, $value) = map trim($_), split /\|/, $line;

Our main loop. We read a line of input, split it on |, and trim each field.

    if ($name eq 'NAME') {
        $print_group->();
        $group_name = undef;
        @dates = $date;
        @values = ();
        $sum = 0;
        next;
    }

If $name is 'NAME', this is the start of a new group. Print the output for the current group if any ($print_group->() does nothing if there is no current group), then reset our state variables back to initial values, except for @dates, which is filled with the $date value from the header row. Then start the next iteration of the loop because we're done with this line.

    $group_name ||= $name;
    push @dates, $date if @dates < 3;
    push @values, $value if @values < 2;
    $sum += $value;

If we get here, this line is not the start of a new group. We set $group_name if it hasn't been set yet. We add $date to our list of saved dates (but we only need 3 dates, so do nothing if we already have 3). We add $value to our list of saved values (but we only need 2 of them). Finally we add $value to our total $sum within the group.

}
$print_group->();

At the end of the loop we've also just finished processing a group, so we need to call $print_group here as well.