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,
[download]

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
[download]

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 );
}
[download]

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
[download]

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:

• http://learn.perl.org, with first steps
• The documentation
• Tutorials->Getting Started with Perl
• Modern Perl (see also perl.org/library)

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.

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.