counting and moving groups

[malaga]

Hello,

I've been trying to figure out the logic on this one and i can't quite get there. i don't know if perl can handle it alone of if i need mysql. the data does not need to be stored - it's just a list clean up for a specific one time purpose. so: i'm stuck on which tools to use and the logic in general - any help is appreciated.

I have 2 csv tables with a common id key.
One is account name and information so each record is unique.
The other is purchase information, so there are as many as 40 records with the same id key.
Accounts have 1 to 40 purchases at the moment but this could grow.

The end result is to have 5 purcases per line per account, then skip to the next line. like this:

CURRENT:


account table:
id,acctname,address
1,joe,123 First St.
2,mary,234 Second St.


purchase table:
id,productnum,prodtype,prodexp
1,222,veg,01/05/05
1,444,fruit,02/04/05
1,555,bread,03/09/06
1,777,butter,05/07/05
1,888,spice,09/06/07
1,999,coffee,02/06/05
2,444,veg,01/01/06
etc.

NEED:

id,acctname,address,productnum1,prodtype1,prodexp1,productnum2,prodtyp
+e2,prodexp2,productnum3,prodtype3,prodexp3,productnum4,prodtype4,prod
+exp4,productnum5,prodtype5,prodexp5
1,joe,123 First St.,222,veg,01/05/05,444,fruit,02/04/05,555,bread,03/0
+9/06,777,butter,05/07/05,888,spice,09/06/07


only five purchases per line, then the next five go on the next line:


id,acctname,address,productnum1,prodtype1,prodexp1,productnum2,prodtyp
+e2,prodexp2,productnum3,prodtype3,prodexp3,productnum4,prodtype4,prod
+exp4,productnum5,prodtype5,prodexp5
1,joe,123 First St.,999,coffee,02/06/05


and so on, then on to the next account


id,acctname,address,productnum1,prodtype1,prodexp1,productnum2,prodtyp
+e2,prodexp2,productnum3,prodtype3,prodexp3,productnum4,prodtype4,prod
+exp4,productnum5,prodtype5,prodexp5
2,mary,234 Second St.,444,veg,01/01/06

Thanks for your time!

[saintmike]

split, join, and splice are your friends:

my @accounts = split /\n/, <<EOT;
1,joe,123 First St.
2,mary,234 Second St.
EOT

my @purchases =  split /\n/, <<EOT;
1,222,veg,01/05/05
1,444,fruit,02/04/05
1,555,bread,03/09/06
1,777,butter,05/07/05
1,888,spice,09/06/07
1,999,coffee,02/06/05
2,444,veg,01/01/06
EOT

my $accounts_by_id = {};

for (@accounts) {
    my($id, $rest) = split /,/, $_, 2;
    $accounts_by_id->{$id}->{info} = $rest;
}

for (@purchases) {
    my($id, $rest) = split /,/, $_, 2;
    push @{$accounts_by_id->{$id}->{purchases}}, $rest;
}

for (@accounts) {
    my($id, $rest) = split /,/, $_, 2;

    {
        my @five = splice
               @{$accounts_by_id->{$id}->{purchases}}, 0, 5;
        last unless @five;
        print "$id,$accounts_by_id->{$id}->{info},",
              join(',', @five), "\n";
        redo if @five == 5;
    }
}

[malaga]

thank you! much much appreciated.