Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

Re^2: Parsing an SQL table using an array of hashes?

by chareTX (Initiate)
on Jun 29, 2013 at 00:53 UTC ( #1041378=note: print w/ replies, xml ) Need Help??


in reply to Re: Parsing an SQL table using an array of hashes?
in thread Parsing an SQL table using an array of hashes?

well, I was going to iterate through the data from SQL - I can already retrieve that. I was just confused on how to store and then retrieve the data. And yes, the next part of the problem will be how to arrange the data into the various series to give to GD::Graph.


Comment on Re^2: Parsing an SQL table using an array of hashes?
Re^3: Parsing an SQL table using an array of hashes?
by LanX (Canon) on Jun 29, 2013 at 01:04 UTC
    I never used GD::Graph, but if you need help you should

    a) read the docs / tutorials /search results

    b) show what you already tried

    c) identify your problems

    d) ask concrete questions, instead for complete solutions.

    We love teaching how to fish ... =)

    Cheers Rolf

    ( addicted to the Perl Programming Language)

      Can I get a free rod and hook?
        Mail him and ask! =)

        Cheers Rolf

        ( addicted to the Perl Programming Language)

Re^3: Parsing an SQL table using an array of hashes?
by poj (Priest) on Jun 29, 2013 at 06:53 UTC

    If you are likely to plot across years or over a period of say the last x months, then it may be convenient to convert your year and months into a 'period number'. Then you can store the data in a hash like this ;

    $data{'category'}[period]

    Storing a string of month/year in another array using the same period numbers as the index makes it easy to build the first record of your plot data.

    Here is an example of what I mean with your data expanded into 2014.

    #perl use strict; use GD::Graph; use GD::Graph::bars; my $YEAR1 = 2013; my @period=(); my %y_data=(); my @x_legend=(); # stroe data while (<DATA>){ chomp; my ($m,$y,$cat,$n) = split ',',$_; # convert year/month to period number my $pd = ym_to_pd($y,$m); $period[$pd] = "$m/$y"; $y_data{$cat}[$pd] = $n; } # graph data my @y_plotdata; push @y_plotdata,[@period]; for my $cat (sort keys %y_data){ push @x_legend,$cat; push @y_plotdata,[@{$y_data{$cat}}]; } # create graph my $my_graph = GD::Graph::bars->new(800,600); $my_graph->set( x_label => 'X Label', y_label => 'Y label', title => 'Title', ); $my_graph->set_legend(@x_legend); my $img = $my_graph->plot(\@y_plotdata) or die $my_graph->error; # save image open(IMG,'>','plot.gif') or die "$!"; binmode IMG; print IMG $img->png; close IMG; # convert year,month to period sub ym_to_pd { my ($yr,$mth) = @_; return ($yr - $YEAR1) * 12 + $mth; } # convert period to year.month sub pd_to_ym { my $ix = shift; my $yr = int(($ix-1)/12)+$YEAR1; my $mth = $ix-12*($yr-$YEAR1); return ($yr,$mth); } #test_periods(); sub test_periods { for my $y (2013..2015){ for my $m (1..12){ my $pd = ym_to_pd($y,$m); my ($yr,$mth) = pd_to_ym($pd); print "$y $m => $pd => $yr $mth\n"; } } } __DATA__ 3,2013,Address,1 2,2013,Equipment,13 3,2013,Equipment,18 4,2013,Equipment,17 5,2013,Equipment,8 3,2013,Database Reconciliation,3 5,2013,Database Reconciliation,3 2,2013,Design/Process,123 3,2013,Design/Process,74 4,2013,Design/Process,42 5,2013,Design/Process,30 1,2014,Design/Process,30
    poj
      Thank you very much -- this was very helpful. Two questions:

      1. Can you explain this line for me please? I am just not sure I get it.

       push @y_plotdata,[@{$y_data{$cat}}];

      2. I need to do the same thing, but reversed -- instead of category being the legend and dates on the x-axis, I need to do the categories on the a-axis, and the months in the legend.

      I haven't started experimenting with it yet, but I wanted to say thanks for your help.

        [@{$y_data{$cat}}] is a ref to a copy of the data for one $cat.

        It is equivalent to [ $y_data{$cat}[0],$y_data{$cat}[1],$y_data{$cat}[2],etc ]

        For a plot of all periods this would work equally as well

        push @y_plotdata,$y_data{$cat};
        poj

      So, thanks for the excellent example. But, I now need to do it the other way. instead of having the categories in the legend, i need the categories on the x-axis and the periods in the legend. I think I can do that with the code you provided, but honestly, we are getting to the edge of my PERL knowledge.

        Little more complicated because you have to define an order for the categories, I have chosen a alphabetic sort.
        #perl use strict; use GD::Graph; use GD::Graph::bars; my $YEAR1 = 2013; my @period=(); my %y_data=(); my @x_legend=(); my %x_data=(); # store data while (<DATA>){ chomp; my ($m,$y,$cat,$n) = split ',',$_; # convert year/month to period number my $pd = ym_to_pd($y,$m); $period[$pd] = "$m/$y"; $y_data{$cat}[$pd] = $n; ++$x_data{$pd}; } # graph data my @y_plotdata; my @categories = sort keys %y_data; push @y_plotdata,[@categories]; for my $pd (sort {$a<=>$b} keys %x_data){ push @x_legend,$period[$pd]; my @y_data=(); for my $cat (@categories){ push @y_data,$y_data{$cat}[$pd]; } push @y_plotdata,[ @y_data ]; } # create graph my $my_graph = GD::Graph::bars->new(800,600); $my_graph->set( x_label => 'X Label', y_label => 'Y label', title => 'Title', ); $my_graph->set_legend(@x_legend); my $img = $my_graph->plot(\@y_plotdata) or die $my_graph->error; # save image open(IMG,'>','plot.gif') or die "$!"; binmode IMG; print IMG $img->png; close IMG; # convert year,month to period sub ym_to_pd { my ($yr,$mth) = @_; return ($yr - $YEAR1) * 12 + $mth; } # convert period to year.month sub pd_to_ym { my $ix = shift; my $yr = int(($ix-1)/12)+$YEAR1; my $mth = $ix-12*($yr-$YEAR1); return ($yr,$mth); } #test_periods(); sub test_periods { for my $y (2013..2015){ for my $m (1..12){ my $pd = ym_to_pd($y,$m); my ($yr,$mth) = pd_to_ym($pd); print "$y $m => $pd => $yr $mth\n"; } } } __DATA__ 3,2013,Address,1 2,2013,Equipment,13 3,2013,Equipment,18 4,2013,Equipment,17 5,2013,Equipment,8 3,2013,Database Reconciliation,3 5,2013,Database Reconciliation,3 2,2013,Design/Process,123 3,2013,Design/Process,74 4,2013,Design/Process,42 5,2013,Design/Process,30 1,2014,Design/Process,30
        Updated : period loop uses new %x_data :
        for my $pd (sort {$a<=>$b} keys %x_data){ push @x_legend,$period[$pd];
        poj

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1041378]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (4)
As of 2014-12-21 00:44 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (100 votes), past polls