Re: manipulating a data table

Just for the hack of it, can I peddle a postgres version of a 'solution'?

Assuming a postgres instance, with module file_fdw installed, and your 5 text lines tab-separated saved under '/tmp/table.txt':

create server abc_server foreign data wrapper file_fdw;
create foreign table file_table ( c1 text, i2 int, i3 int)
    server abc_server
    options ( filename '/tmp/table.txt', format 'csv', delimiter E'\t'
+ );
select c1, i2, i3 from file_table order by c1; 
select c1, i2     from file_table order by i2 desc;
select c1, i3     from file_table order by i3 desc;
select c1
     , rank() over (order by i2 desc)  
     , rank() over (order by i3 desc)
from file_table
order by c1;
[download]

Output (mimicking yours):

$ psql -qXf table.sql 
 c1 | i2 | i3 
----+----+----
 a  | 23 | 11
 b  | 24 | 15
 c  | 16 | 19
 d  |  8 |  2
 e  | 40 | 41
(5 rows)

 c1 | i2 
----+----
 e  | 40
 b  | 24
 a  | 23
 c  | 16
 d  |  8
(5 rows)

 c1 | i3 
----+----
 e  | 41
 c  | 19
 b  | 15
 a  | 11
 d  |  2
(5 rows)

 c1 | rank | rank 
----+------+------
 a  |    3 |    4
 b  |    2 |    3
 c  |    4 |    2
 d  |    5 |    5
 e  |    1 |    1
(5 rows)
[download]

Not entirely serious as a solution but I thought is was cute all the same: it is also the shortest till now, with only 3 lines :)

Comment on Re: manipulating a data table Select or Download Code

Replies are listed 'Best First'.
Re^2: manipulating a data table by afoken (Chancellor) on Aug 16, 2016 at 18:14 UTC
OK, who can present a solution that at least involves ... Web Services a NoSQL database a Linux cluster two different ORM Java C# Oracle Bonus points for using MUMPS or FORTRAN. SCNR ;-) Alexander -- Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)	[reply]
Re^3: manipulating a data table by kevbot (Vicar) on Aug 17, 2016 at 04:54 UTC
I was tempted to go for a Fortran web service with SQL (http://fortran.io), but I settled for a solution using Data::Table. #!/usr/bin/env perl use strict; use warnings; use Data::Table; # Giving '0' as the second arg to Data::Table::fromTSV, # results in a Data::Table with column names of col1, col2 and col3 my $t = Data::Table::fromTSV("data.tsv", 0); my $nrow = $t->nofRow; my @row_numbers = 1..$nrow; # Sort by 'col2' as numeric values in decending order $t->sort( 'col2', 0, 1 ); # Add a new column that contains the row indices for the 'col2' sort o +rder $t->addCol( \@row_numbers, 'col2order' ); # Sort by 'col3' as numeric values in decending order $t->sort( 'col3', 0, 1 ); # Add a new column that contains the row indices for the 'col3' sort o +rder $t->addCol( \@row_numbers, 'col3order' ); # Sort by 'col1' as string values in ascending order $t->sort( 'col1', 1, 0 ); # Get a subtable with the columns of interest my $result = $t->subTable( undef, [ 'col1', 'col2order', 'col3order' ] + ); print $result->tsv; exit; [download] This input is in data.tsv: `a 23 11 b 24 15 c 16 19 d 8 2 e 40 41` [download] The output is: `col1 col2order col3order a 3 4 b 2 3 c 4 2 d 5 5 e 1 1` [download]	[reply] [d/l] [select]