First, the definition: It occurs to me that people refer to lots of things as "pivot tables" including the ability to drag certain rows to column headers for filtering and all that datawarehousing stuff.
This isn't that.
I have an unsettlingly frequent need to take "few column, many row" files and query outputs and line them up next to each other. I can't use sql because they are frequently bridges between databases.
and our perl environments are stuck with core modules, and we have a distressing mix of unixen and windows environments, so things like sqlite are out.
What I have here is some code I've banged together that will take a 2-dimensional list (of the "list of list references" variety) and produce a similarly structured list where columns and rows switch places. That is to say this:
will become this:1,a 2,b
On it's own, I don't think that would really even bare mention in CB. But what I did add was padding and handling for irregular field counts and blank rows. Meaning that this:1,2 a,b
(note the leading blank line) will be converted to:1,2,3 4,5
Note that it's not remarkably efficient code. It probably couldn't even be considered particularly 'perlish'. (I find I write remarkably C-ish perl.),1,,4 ,2,,5 ,3,,
I'm definitely up for suggestions (and recommendations on where to put usage examples, test cases, etc. This is getting a bit long.) The next version (still in frustration) uses external buffering for an intermediate stage and doesn't do the whole schmeggegy in ram.
To use, just call the pivot function with a list of listrefs.
It's not earth shattering code, but I save a lot of time and get a lot of use out of it.sub pivot { my (@srcgrid) = @_; my $max_row = $#srcgrid; my $max_col = get_max_cols(@srcgrid); my @pivoted; for my $col (0..$max_col) { my @new_row; for my $row (0..$max_row) { my $value; if (defined $srcgrid[$row][$col]) { $value = $srcgrid[$row][$col]; } else { $value = ''; } push @new_row,$value; } push @pivoted,\@new_row; } return @pivoted; } sub get_max_cols { my (@grid) = @_; my $max= 0; for my $rowref (@grid) { my @row = @$rowref; if ($max < $#row) { $max = $#row; } } return $max; }
EDIT: Ok, it's not 'pivoting' it's "transposing" (title changed accordingly. Thanks to tye and ambrus for the clarification.
|
|---|
| Replies are listed 'Best First'. | |
|---|---|
|
Re: Pivoting 2 dimensional array refs
by wind (Priest) on May 13, 2011 at 01:13 UTC | |
by John M. Dlugosz (Monsignor) on May 14, 2011 at 10:53 UTC | |
by Voronich (Hermit) on May 14, 2011 at 14:11 UTC | |
by John M. Dlugosz (Monsignor) on May 14, 2011 at 15:43 UTC | |
by Voronich (Hermit) on May 18, 2011 at 15:32 UTC | |
| |
by BrowserUk (Patriarch) on May 14, 2011 at 14:24 UTC | |
by Voronich (Hermit) on May 14, 2011 at 16:41 UTC | |
by Anonymous Monk on May 14, 2011 at 14:31 UTC | |
by choroba (Cardinal) on May 13, 2011 at 08:38 UTC | |
by Voronich (Hermit) on May 13, 2011 at 13:07 UTC | |
|
Re: Pivoting 2 dimensional array refs
by ambrus (Abbot) on May 13, 2011 at 07:13 UTC | |
by Voronich (Hermit) on May 13, 2011 at 13:13 UTC | |
by John M. Dlugosz (Monsignor) on May 14, 2011 at 10:44 UTC |