I have data in a ‘spreadsheet’ form. I wanted to be able to sort on the values in the columns in a similar way that can be done on a spreadsheet. I wanted this to be flexible so that I could have either text or numeric data in any column and I could choose which columns to use for sorting.
I looked in the Monks files and other internet sites but could not find exactly what I wanted, which did not require modules I did not have etc.
Therefore I wrote the test Perl below.
It does do exactly what I wanted but I suspect that it is not the most elegant or efficient way of using Perl. However, in case others may find some use for it I am supplying to the ‘Cool Uses’ section.
use strict "vars";

my (%spv, $row, $col, $jrow, $ja, $jb, $jc, $jd);
my ($jcol, $v_max, $col_hd_total, @av, @bv, @cv, @dv, @row_ord, @row_o
+rd1, @order_req);
my (%col_desc, $return_code, $return_message);

# this defines the headins for each column and the type of data - n nu
+meric, t text
$col_desc{A} = 'n';
$col_desc{B} = 't';
$col_desc{C} = 't';
$col_desc{D} = 'n';

# values for the columns for testing purposes
@av = qw(1 2 3);
@bv = qw(A B C);
@cv = qw(M N O);
@dv = qw(21 22 23);

#============================
#
# sub op_hash(\%spv, \@row_ordm, 'intial');
#
# this prints out the hash in csv form
# arguments
# 1 referecne to the hash
# 2 referecne to the array with the order
# 3 title assicarted with the output
#
#===========================

sub op_hash($$$$) {

my ($ref_spv, $ref_col_desc, $ref_row_ord, $reason) = @_;

my ($jr, $jc);

print "\n\nOutput of hash for $reason\n";
print "Row";

foreach $jc (sort {$a cmp $b} keys %$ref_col_desc) {
    print ",$jc";
}
print "\n";

foreach $jr (@$ref_row_ord) {
    print "$jr";
    foreach $jc (sort {$a cmp $b} keys %$ref_col_desc) {
        print ",$ref_spv->{$jr}{$jc}";
    }
    print "\n";
}
print "\nend of $reason\n\n";
}

#====================================
#
# sort_spsh
#
# this sorts the given hash of form $hs{$row}{$col} by given columns
#
# arguments
#
# 1 reference to the hash to be sorted structure
#                    {numeric_key}{column heading} = value
# 2 reference to hash holding key of column headings value of type - m
+ust be t or n 
# 3 reference to array holding the column headings in the order in whi
+ch the sort is to be done
# 4 reference to array holding rows ordered by the columns given
# 5 reference to the return code 0 failed 1 success
# 6 reference to the return message when failure occurs
#
#===================================

sub sort_spsh($$$$$$) {

my ($ref_spv, $ref_col_desc, $ref_order_req, $ref_new_order, $ref_retu
+rn_code, $ref_return_message) = @_;

my ($jrow, $js, $sort_total, $sort_str, $logor, $cur_col);

# set default ok returns
$$ref_return_code = 1;
$$ref_return_message = '';

$sort_total = scalar(@$ref_order_req);

# check that the required sort headings are in the given hash
for ($js = 0; $js < $sort_total; $js ++ ) {

    if(! exists($ref_spv->{1}{$ref_order_req->[$js]})) {

        $$ref_return_code = 0;
        $$ref_return_message = "sort column heading <$ref_order_req->[
+$js]> does not exist in the given hash\n";
    }
}

if($$ref_return_code == 0) {return}

$logor = " \|\| ";

# form the sort string
for ($js = 0; $js < $sort_total; $js ++ ) {

# add the || for 2nd and subsequent sorts

    if($js > 0) {

        $sort_str .= $logor;
    }

# decide on type of sort and add to sort string
    $cur_col = $ref_order_req->[$js];

    if($ref_col_desc->{$cur_col} eq 't') {

        $sort_str .= "\$ref_spv->{\$a}->{$cur_col} cmp \$ref_spv->{\$b
+}->{$cur_col}";

    } elsif($ref_col_desc->{$cur_col} eq 'n') {

        $sort_str .= "\$ref_spv->{\$a}->{$cur_col} <=> \$ref_spv->{\$b
+}->{$cur_col}";

    } else {

        print "\n[sort_spsh] for column heading <$cur_col> invalid sor
+t type of <$ref_col_desc->{$cur_col}> given - must be 't' or 'n'\n";

        $$ref_return_code = 0;
        $$ref_return_message = "for column heading <$cur_col> invalid 
+sort type of <$ref_col_desc->{$cur_col}> given - must be 't' or 'n'";
    }
}

print "[sort_spsh] total <$sort_total> logor <$logor>\n$sort_str\n\n";

if($$ref_return_code == 0) {return}

foreach $jrow (sort {
        eval($sort_str);
    } keys %$ref_spv) {

    push(@$ref_new_order, $jrow);
}
}

#============main==================

# load the table with initial values

$jrow = 0;
foreach $ja (@av) {
    foreach $jb (@bv) {
        foreach $jc (@cv) {
            foreach $jd (@dv) {
                $jrow +=1;
                $spv{$jrow}{A} = $ja;
                $spv{$jrow}{B} = $jb;
                $spv{$jrow}{C} = $jc;
                $spv{$jrow}{D} = $jd;
                push (@row_ord, $jrow);
            }
        }                                                        
    }
}

op_hash(\%spv, \%col_desc, \@row_ord, 'intial');

# enter data for the order in which the columns are to be sorted
# all the columns do NOT have to be given
$order_req[0] = 'D';
$order_req[1] = 'C';
$order_req[2] = 'A';
$order_req[3] = 'B';

sort_spsh(\%spv, \%col_desc, \@order_req, \@row_ord1, \$return_code, \
+$return_message);

print "\nafter sort_spsh return code <$return_code> message <$return_m
+essage>\n";

if($return_code == 1) {
    op_hash(\%spv, \%col_desc, \@row_ord1, 'current order req');
}

[download]

In reply to Sorting Spreadsheet Like Data by merrymonk

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.