Assuming you have two files, one contains IDs in the first column, the second contains several rows, each starting with the ID, in place of real example data consider the following ids.csv:
1,
2,
3,
interactions.csv:
1,10,20,30,40,
1,15,25,35,45,
2,3,5,6,7,8,
3,100,200,300,400
2,10,4,-8,16,32
3,300,400,500,600
Something to get you started, this is not perfect, and I'll expand on that later. In order to run the code you'll need the Text::CSV module, I also use List::Util which has been a core module (included with perl) since v5.7.3. You can install modules using the cpan tool using cpan Text::CSV, if you're going to use perl a lot I suggest you use cpanm (which you'll need to install first) to deal with module instillation.
The perl code:
#!/usr/bin/perl
use strict;
use warnings;
use feature 'say';
use Text::CSV;
use List::Util qw 'sum';
my @IDs;
my $IDfile = 'ids.csv';
my $Intfile = 'interactions.csv';
my $csv = Text::CSV->new() or die "Can't use CSV: " . Text::CSV->error
+_diag();
# Open file containing IDs
# our example has id's in the first column
my $fh;
open( $fh, '<', $IDfile ) or die "Can't open $IDfile: $!";
# build an array of IDs for processing;
while ( my $row = $csv->getline( $fh ) ){
push @IDs, $row->[0];
}
# close csv
$csv->eof or $csv->error_diag();
close $fh;
# parse interactions
open ( $fh, '<', $Intfile ) or die "Can't open $Intfile: $!";
my $data = $csv->getline_all($fh);
$csv->eof or $csv->error_diag();
close $fh;
# for each ID in our array
foreach my $ID ( @IDs ){
my @total;
# loop through interactions data
foreach my $row ( @$data ){
# if the fist column is equal to the value of $ID
if ( $row->[0] == $ID ){
# add the value of the fourth column to the array
push @total, $row->[3];
}
}
# display totals
say "Total for ID $ID: " . sum( @total ) if ( @total );
}
Running the code above with the example data provided returns:
Total for ID 1: 65
Total for ID 2: -2
Total for ID 3: 800
What this does. Opens the ids.csv file, parses the file contents with the Text::CSV module and adds the value of the each first column into an array of IDs. Opens and parses the interations.csv file. For each ID we have in the array, loop through the rows in interactions.csv, check if the value in the first column is the same as the value of $ID and add the value of the third column to the @totals array. Print the sum of the @totals array.
If you're new to perl check out:
Hopefully this has been some help. Perfect, I doubt it, I threw this together very quickly, enough to get you started, I hope so. Reading the perl and module documentation linked to above will be beneficial to you. Please ensure you take the time to do this before replying with any questions about the code above. | 