Perl output matrix from mysql data. Output across 2 axis with counts for each

Question

I'm a new member but long time reader; I've learned so much from the folks here.

I'm trying to get some mysql output to format using perl.

I've got 2 output columns, cartypes & manufacturers, and a third output which is counts.

I've been trying to use a hash reference to basically list the cartypes down the y axis, and car manufacturers across the x, and loop through basically populating a matrix with the counts.

So count of Chevy 4x4 trucks. And hopefully include 0's if there is no match. The timeframe is handled in the sql and not affecting the output other than the counts.

And, if possible, how to total the row and column counts would be ideal. CSV would be the likely end product.

I just don't know how to start. Any guidance would be greatly appreciated. Thank you.

Sample data

cartype Manufacturer    count(*)
Convertible Audi    3
Convertible Chrysler    1
Coupe   Aston   5
Coupe   Audi    2
Coupe   Chrysler    2
Etc     Etc     0
Minivan Chevy   2
Minivan Chrysler    6
Minivan Dodge   3
Minivan Ford    4
SUV Audi    2
SUV Buick   1
SUV Dodge   2
SUV Ford    5
Sedan   Audi    3
Sedan   Buick   2
Sedan   Chevy   3
Sedan   Chrysler    2
Sedan   Dodge   2
Sedan   Ford    5

Hopeful output

    Aston   Audi    Buick   Chevy   Chrysler    Dodge   Ford    etc TOTAL
Convertible 0   3   0   0   1   0   0   0   4
Coupe   5   2   0   0   2   0   0   0   9
Minivan 0   0   0   2   6   3   4   0   15
SUV 0   2   1   0   0   2   5   0   10
Sedan   0   3   2   3   2   2   5   0   17
etc 0   0   0   0   0   0   0   0   0
TOTAL   5   10  3   5   11  7   14  0   55

Thank you Oesor for the reformatting. Much obliged.

This sounds like a crosstab or pivot. Unfortunately MYSQL doesn't have a direct solution. Have a look [here](http://stackoverflow.com/questions/7674786/mysql-pivot-table) and at [this answer](http://stackoverflow.com/questions/15997090/crosstab-view-in-mysql) which have some example approaches in the answers and other links in the comments. — wwkudu, Mar 17 '14 at 07:22
Thank you for the assistance, the Perl answer below is more what I was looking for. I wasn't clear with my request. — bowden, Mar 18 '14 at 09:56

Chris Charley · Accepted Answer · 2014-03-19T15:42:06.467

Here is a solution using Text::Table to format the output.

#!/usr/bin/perl
use strict;
use warnings;
use List::Util 'sum';
use Text::Table;

my (%data, %makes);
while (<DATA>) {
    my ($type, $make, $cnt) = split;
    $data{$type}{$make} += $cnt;
    $makes{$make} += $cnt;
}

my @makes_list = sort keys %makes;
my $tb = Text::Table->new('', @makes_list, 'Total');

for my $type (sort keys %data) {
    my $total = sum values $data{$type};
    $tb->load([ $type, map {$_ // 0} @{$data{$type}}{@makes_list}, $total ]);
}

$tb->load([ 'TOTAL', @makes{@makes_list}, sum values %makes ]);

print $tb;
__DATA__
Convertible Audi    3
Convertible Chrysler    1
Coupe   Aston   5
Coupe   Audi    2
Coupe   Chrysler    2
Minivan Chevy   2
Minivan Chrysler    6
Minivan Dodge   3
Minivan Ford    4
Etc     Etc     0
SUV Audi    2
SUV Buick   1
SUV Dodge   2
SUV Ford    5
Sedan   Audi    3
Sedan   Buick   2
Sedan   Chevy   3
Sedan   Chrysler    2
Sedan   Dodge   2
Sedan   Ford    5

Output was

            Aston Audi Buick Chevy Chrysler Dodge Etc Ford Total
Convertible 0     3    0     0     1        0     0   0     4
Coupe       5     2    0     0     2        0     0   0     9
Etc         0     0    0     0     0        0     0   0     0
Minivan     0     0    0     2     6        3     0   4    15
SUV         0     2    1     0     0        2     0   5    10
Sedan       0     3    2     3     2        2     0   5    17
TOTAL       5     10   3     5     11       7     0   14   55

Update program using Text::CSV

#!/usr/bin/perl
use strict;
use warnings;
use List::Util 'sum';
use Text::CSV_XS;

my (%data, %makes);
while (<DATA>) {
    my ($type, $make, $cnt) = split;
    $data{$type}{$make} += $cnt;
    $makes{$make} += $cnt;
}

my $csv = Text::CSV_XS->new ({ binary => 1, eol => $/ }) or
     die "Cannot use CSV: ".Text::CSV_XS->error_diag ();

open my $fh, ">", 'o33.csv' or die $!;

my @makes_list = sort keys %makes;

$csv->print($fh, ['', @makes_list, 'Total'] );

for my $type (sort keys %data) {
    my $total = sum values $data{$type};
    $csv->print($fh, [ $type,
                        map {$_ // 0} @{$data{$type}}{@makes_list},
                        $total ]);
}

$csv->print($fh, [ 'TOTAL', @makes{@makes_list}, sum values %makes ]);
close $fh or die $!;

__DATA__
Convertible Audi    3
Convertible Chrysler    1
Coupe   Aston   5
Coupe   Audi    2
Coupe   Chrysler    2
Minivan Chevy   2
Minivan Chrysler    6
Minivan Dodge   3
Minivan Ford    4
Etc     Etc     0
SUV Audi    2
SUV Buick   1
SUV Dodge   2
SUV Ford    5
Sedan   Audi    3
Sedan   Buick   2
Sedan   Chevy   3
Sedan   Chrysler    2
Sedan   Dodge   2
Sedan   Ford    5

This is just what I was looking for. I don't understand the in the while loop, it gives errors: Name "main::DATA" used only once: possible typo at test.pl line 50. readline() on unopened filehandle DATA at test.pl line 50. Thank you so much for your assistance. — bowden, Mar 18 '14 at 10:00
@Bowden in the while loop is a pretty basic thing you should know - I've edited my code to show where that is reading DATA from. It read like a file from within the program file. If you would copy again my program *including* the **__DATA__** into a file and run, you should see the results. — Chris Charley, Mar 19 '14 at 15:08
@Bowden Don't forget to include the actual data after the **__DATA__** token in your file. — Chris Charley, Mar 19 '14 at 15:15
Figured those results would go into the %data and %makes hashes. Wasn't sure how to call them in the while loop. — bowden, Mar 19 '14 at 15:29

Perl output matrix from mysql data. Output across 2 axis with counts for each

1 Answers1