• One table per $candid
• One row per $zip
• Three columns ($zip, $amt, $candid)
• One $amt subcolumn per $date

Your data structure should reflect that.

use strict;
use warnings;

my %data;
my %dates;
my %zips;
while (<DATA>) {
   chomp;
   my ($zip, $amt, $date, $candid) = split;

   $zips {$zip } = 1;
   $dates{$date} = 1;

   $data{$candid}{$zip}{amts}{$date} += $amt;
}

my @candids = sort keys %data;
my @dates   = sort keys %dates;
my @zips    = sort keys %zips;

local $, = "\t";
local $\ = "\n";
for my $candid (@candids) {
   my $table = $data{$candid};

   print('CANDID', 'ZIP', (map "MONEY ($_)", @dates));

   for my $zip (@zips) {
      my $row = $table->{$zip};
      my @amts = map { $_ || 0 } @{ $row->{amts} }{ @dates };
      print($candid, $zip, @amts);
   }

   print('');
}

__DATA__
12345  200 11062000 C1234
12345   50 11062000 C1234
67890   50 11072000 D5555
38401  250 11062000 C00003418
77024  200 11062000 C00003418
75711 1000 11072000 C00003418
33480 5000 11072000 C00003418
[download]

CANDID       ZIP   MONEY (11062000)  MONEY (11072000)
C00003418  12345                  0                 0
C00003418  33480                  0              5000
C00003418  38401                250                 0
C00003418  67890                  0                 0
C00003418  75711                  0              1000
C00003418  77024                200                 0

CANDID       ZIP   MONEY (11062000)  MONEY (11072000)
C1234      12345                250                 0
C1234      33480                  0                 0
C1234      38401                  0                 0
C1234      67890                  0                 0
C1234      75711                  0                 0
C1234      77024                  0                 0

CANDID       ZIP   MONEY (11062000)  MONEY (11072000)
D5555      12345                  0                 0
D5555      33480                  0                 0
D5555      38401                  0                 0
D5555      67890                  0                50
D5555      75711                  0                 0
D5555      77024                  0                 0
[download]

A minor change would cut out "blank" rows and columns if that's what you prefer. Specifically, collect a lists of zips and dates per cand instead one one global list.

Thanks for the help. This does exactly what I needed. However, I think I was mistaken about the way I wanted the output structured. I am analyzing the FEC data set for individual campaign contributions, which has approximately 1.2 million entries. When I attempted to use your code to parse the file it became overwhelming very quickly. Given that, I am going to change the desired output table, to weeks instead of days.

So it looks something like this:

ZIP, WEEK1 (CANDID 1234), WEEK2 (CANDID 4567)...
64521, 500, 400, ...

Where WEEK1 (CANDID 1234) = the total money donated to that
candidate from each zip code for that week. I will toy around with this some this weekend and I may post again in the future.

Thanks again for all the help!

Easy! After

   my ($zip, $amt, $date, $candid) = split;
[download]

just add

   $date = days_to_week($date);
[download]

Writing days_to_week is left as an exercise for the reader :) (I'd use DateTime.)

Hey-

I was wondering if I could get some additional advice. I have been working with your code, and I have ran into a slight problem. It seems as though this routine is creating a new date column for each zipcode entry instead of summing the amounts of money for each date and zipcode. The end results is a symmetric matrix, with the columns equaling the number of zipcodes. This is not what should happen. Instead, I was envisioning a matrix with the rows equaling the number of zip codes and the columns equaling the number of dates. I have limited the data set to 4000 zipcodes and 60 days, so the matrix should be 4000 X 60, with each cell representing the total amount of money for that zipcode at that day. So (1,1) => Zipcode 12345 total contribution for 09012000, (1,2) => zipcode 12345 total contribution for 09022000, (2,1) => zipcode 12346 total contribution for 09012000, you get the idea. Instead what I get is a matrix 4000 X 4000, which seems to create new dates for each new zipcode entry. I think what is going on is a keying problem with the hash. Instead of recognizing 09012000 = 09012000 it assumes 09012000 != 09012000 and it is creating a new column 09012000.1 instead of grouping the dates together. Do you have any thoughts on this matter? Thanks.

I was envisioning a matrix with the rows equaling the number of zip codes and the columns equaling the number of dates.

That's what it does. From my earlier post,

CANDID       ZIP   MONEY (11062000)  MONEY (11072000)
C1234      12345                250                 0
C1234      33480                  0                 0
C1234      38401                  0                 0
C1234      67890                  0                 0
C1234      75711                  0                 0
C1234      77024                  0                 0
[download]

6 zips, so 6 rows.
2 dates, so 2 cols.

Note that 250 is the sum of two different amounts.

Instead what I get is a matrix 4000 X 4000

Not using my code, you didn't. As for the problem with your code, I don't know what you're expecting from me without showing me the code.

I was writing up a bit more, but then noticed the number of posts that appeared. So just three quick notes:

1. You have a CSV file, so rather than using substr with fixed-width columns, perhaps you should consider using Text::CSV. It will make you code more resilient and you'll spend less time thinking how to parse the file.
2. Given you want to organize by an arbitrary number of dates by zip code, it seems like you should use a hash of hashes keyed on ZIP (as others have suggested) - see perllol for operational details.
3. From the content of your files, it sounds like these are pulled from a database. Is there a reason you are using local files as an intermediary rather than DBI?

Thanks for the info (DBI looks useful). I actually misspoke in my original post. The data that I am using is fixed-width, so substr is the way to go. Sorry for the confusion. I am a first time poster here. Next time I will be more precise with my language.

#!/usr/bin/env perl

use strict;
use warnings;

my %data;
while (<DATA>) {
    next if /ZIP/;
    chomp;
    my ($zip, $money, $date, $id) = split /\s*,\s*/;
    $data{$zip}{total} += $money;
    $data{$zip}{date}   = $date;
    $data{$zip}{id}     = $id;
}

my @dates;
for my $zip (keys %data) {
    push @dates, $data{$zip}{date} if exists $data{$zip}{date};
}

print "ZIP";
for (@dates) {print ",Total($_)"}
print ",ID\n";

for my $zip (keys %data) {
    print "$zip, ";
    for (@dates) {
        if ($data{$zip}{date} eq $_) {
            print "$data{$zip}{total},";
        }
        else {
            print "0,";
        }
    }
    print "$data{$zip}{id}\n";
}

__DATA__
ZIP, Money, Date, ID
12345, 200, 11062000, C1234
12345, 50, 11062000, C1234
67890, 50, 11072000, D5555
[download]

prints:

ZIP,Total(11062000),Total(11072000),ID
12345, 250,0,C1234
67890, 0,50,D5555
[download]

This matches your desired output, based on the input example.

Thanks for the help! See my post below for an explanation of where I am at on this project. Thanks again.