sort -- following question: How to rearrange columns

[Shiping Wang]

Hello,

With this sample data set, I have a different question. How can I rearrange
columns such as this:

before:
col1 col4 col5 col2 col6 col3
Abc 12.8 8 left 1 15.7
Def 13.8 9 top 0 19.7
gef 14.8 9 left 0 19.7
Dgf 12.3 9 right 4 99.6
cef 16.8 4 right 0 89.7
baf 32.8 7 bottom 5 79.8
efg 16.8 5 right 0 56.7
etg 12.8 2 left 7 34.7

after:
col1 col2 col3 col4 col5 col6
Abc left 15.7 12.8 8 1
Def top 19.7 13.8 9 0
gef left 19.7 14.8 9 0
Dgf right 99.6 12.3 9 4
cef right 89.7 16.8 4 0
baf bottom 79.8 32.8 7 5
efg right 56.7 16.8 5 0
etg left 34.7 12.8 2 7

I thought one way to do it is to transpose the array, sort _ColumnName_,
then transpose it back. Is this right way to do it?

Thanks,

Shiping

At 07:50 PM 1/29/2004 -0500, Hanson, Rob wrote:

>I have to run, otherwise I would elaborate a bit.
>
>The code is below. Check out the "perldoc perlreftut" for what the
>"\@cols", "@{$row}", and "$a->[2]" means. Check out "perldoc -f sort" for
>what the "sort {...} @rows" means. And of course ask questions if you get
>stuck (but take a look at the docs first).
>
>############ THE CODE ############
>
>my @rows;
>
>while (my $line = <DATA>) {
> chomp($line);
> my @cols = split(/\s+/, $line);
> push @rows, \@cols;
>}
>
>print "Sorted on 5th column:\n";
>@rows = sort {$a->[4] <=> $b->[4]} @rows;
>
>foreach my $row (@rows) {
> print "@{$row}\n";
>}
>
>print "Sorted on 3rd column:\n";
>@rows = sort {$a->[2] <=> $b->[2]} @rows;
>
>foreach my $row (@rows) {
> print "@{$row}\n";
>}
>
>__DATA__
>Abc 12.8 8 "left" 1 15.7
>Def 13.8 9 "top" 0 19.7
>gef 14.8 9 "left" 0 19.7
>Dgf 12.3 9 "right" 4 99.6
>cef 16.8 4 "right" 0 89.7
>baf 32.8 7 "bottom" 5 79.8
>efg 16.8 5 "right" 0 56.7
>etg 12.8 2 "left" 7 34.7
>
>############ THE OUTPUT ############
>
>$ perl sort.pl
>Sorted on 5th column:
>efg 16.8 5 "right" 0 56.7
>Def 13.8 9 "top" 0 19.7
>gef 14.8 9 "left" 0 19.7
>cef 16.8 4 "right" 0 89.7
>Abc 12.8 8 "left" 1 15.7
>Dgf 12.3 9 "right" 4 99.6
>baf 32.8 7 "bottom" 5 79.8
>etg 12.8 2 "left" 7 34.7
>Sorted on 3rd column:
>etg 12.8 2 "left" 7 34.7
>cef 16.8 4 "right" 0 89.7
>efg 16.8 5 "right" 0 56.7
>baf 32.8 7 "bottom" 5 79.8
>Abc 12.8 8 "left" 1 15.7
>Dgf 12.3 9 "right" 4 99.6
>Def 13.8 9 "top" 0 19.7
>gef 14.8 9 "left" 0 19.7
>
>
>-----Original Message-----
>From: Boon Chong Ang [mailto:BCANG@altera.com]
>Sent: Thursday, January 29, 2004 7:38 PM
>To: [email]beginners@perl.org[/email]
>Subject: sort
>
>
>Hi,
>
>I want to write a perl script to do something like this
>
>
>
>Abc 12.8 8 "left" 1 15.7
>
>Def 13.8 9 "top" 0 19.7
>
>gef 14.8 9 "left" 0 19.7
>
>Dgf 12.3 9 "right" 4 99.6
>
>cef 16.8 4 "right" 0 89.7
>
>baf 32.8 7 "bottom" 5 79.8
>
>efg 16.8 5 "right" 0 56.7
>
>etg 12.8 2 "left" 7 34.7
>
>
>
>
>
>Just say I want to sort the row based on the value on fifth or third column,
>any advice how to do so?
>
>
>
>
>
>Thank you & best regards,
>
>ABC
>
>
>
>
>--
>To unsubscribe, e-mail: [email]beginners-unsubscribe@perl.org[/email]
>For additional commands, e-mail: [email]beginners-help@perl.org[/email]
><http://learn.perl.org/> <http://learn.perl.org/first-response>

[David]

Shiping Wang wrote:

> Hello,
>
> With this sample data set, I have a different question. How can I
> rearrange columns such as this:
>
> before:
> col1 col4 col5 col2 col6 col3
> Abc 12.8 8 left 1 15.7
> Def 13.8 9 top 0 19.7
> gef 14.8 9 left 0 19.7
> Dgf 12.3 9 right 4 99.6
> cef 16.8 4 right 0 89.7
> baf 32.8 7 bottom 5 79.8
> efg 16.8 5 right 0 56.7
> etg 12.8 2 left 7 34.7

[snip]

>
> I thought one way to do it is to transpose the array, sort _ColumnName_,
> then transpose it back. Is this right way to do it?

if the columns are always out in this order, you can easily reorder them
without sort:

#!/usr/bin/perl -w
use strict;

while(<DATA>){

print join("\t",map {(split)[0,3,5,1,2,4]} $_),"\n";
}

__DATA__
col1 col4 col5 col2 col6 col3
Abc 12.8 8 left 1 15.7
Def 13.8 9 top 0 19.7
gef 14.8 9 left 0 19.7
Dgf 12.3 9 right 4 99.6
cef 16.8 4 right 0 89.7
baf 32.8 7 bottom 5 79.8
efg 16.8 5 right 0 56.7
etg 12.8 2 left 7 34.7

__END__

prints:

col1 col2 col3 col4 col5 col6
Abc left 15.7 12.8 8 1
Def top 19.7 13.8 9 0
gef left 19.7 14.8 9 0
Dgf right 99.6 12.3 9 4
cef right 89.7 16.8 4 0
baf bottom 79.8 32.8 7 5
efg right 56.7 16.8 5 0
etg left 34.7 12.8 2 7

david
--
sub'_{print"@_ ";* \ = * __ ,\ & \}
sub'__{print"@_ ";* \ = * ___ ,\ & \}
sub'___{print"@_ ";* \ = * ____ ,\ & \}
sub'____{print"@_,\n"}&{_+Just}(another)->(Perl)->(Hacker)

[R. Joseph Newton]

Shiping Wang wrote:

> Hello,
>
> With this sample data set, I have a different question. How can I rearrange
> columns such as this:
>
> before:
> col1 col4 col5 col2 col6 col3
> Abc 12.8 8 left 1 15.7
> Def 13.8 9 top 0 19.7
> gef 14.8 9 left 0 19.7
> Dgf 12.3 9 right 4 99.6
> cef 16.8 4 right 0 89.7
> baf 32.8 7 bottom 5 79.8
> efg 16.8 5 right 0 56.7
> etg 12.8 2 left 7 34.7
>
> after:
> col1 col2 col3 col4 col5 col6
> Abc left 15.7 12.8 8 1
> Def top 19.7 13.8 9 0
> gef left 19.7 14.8 9 0
> Dgf right 99.6 12.3 9 4
> cef right 89.7 16.8 4 0
> baf bottom 79.8 32.8 7 5
> efg right 56.7 16.8 5 0
> etg left 34.7 12.8 2 7
>
> I thought one way to do it is to transpose the array, sort _ColumnName_,
> then transpose it back. Is this right way to do it?

Okay, it looks like you have two major pieces of data contained in this file.
The first is the first line, which provides metadata. The other is the rest of
the file, which provides the actual data, Egads! How did this file get in this
shape to begin with?

What you will have to do first is to create a mapping of position to target
position, which can be done using the first line.
You can then use the mapping to decide in what context to take the elements of
each line.

Greetings! E:\d_drive\perlStuff\giffy>perl -w
my %column_index;
my $header_line = <DATA>;
my @column_names = split /\s+/, $header_line;
my $position = 0;
foreach my $column_name (@column_names) {
$column_name =~ s/col//;
$column_index{$column_name - 1} = $position++;
}

my @output_lines;
my $line = <DATA>;
chomp $line;
while ($line) {
my @elements = split /\s+/, $line;
my @sorted_elements;
push @sorted_elements, sprintf("%-10s", $elements[$column_index{$_}])
foreach sort {$a <=> $b} keys %column_index;
push @output_lines, join(' ', @sorted_elements);
$line = <DATA>;
chomp $line if $line;
}

print "$_\n" foreach @output_lines;

__DATA__
col1 col4 col5 col2 col6 col3
Abc 12.8 8 left 1 15.7
Def 13.8 9 top 0 19.7
gef 14.8 9 left 0 19.7
Dgf 12.3 9 right 4 99.6
cef 16.8 4 right 0 89.7
baf 32.8 7 bottom 5 79.8
efg 16.8 5 right 0 56.7
etg 12.8 2 left 7 34.7
^Z
Abc left 15.7 12.8 8 1
Def top 19.7 13.8 9 0
gef left 19.7 14.8 9 0
Dgf right 99.6 12.3 9 4
cef right 89.7 16.8 4 0
baf bottom 79.8 32.8 7 5
efg right 56.7 16.8 5 0
etg left 34.7 12.8 2 7


Joseph

[Shiping Wang]

Hi David, thanks for your response.

My concern is that if the data has hundreds columns (which the order
information I can get from another array or file) need to be rearranged,
how can I do it.

Here is my test program, it seems working. Any suggestion or concern???

Shiping
__________________________________________________ __________________________________________________ ___________
#!/usr/bin/perl
# sortColumns.pl
use warnings;
use strict;
use Math::Matrix;
# use Data::Dumper;

my @rows;

while (my $line = <DATA>) {
chomp($line);
my @cols = split(/\s+/, $line);
push @rows, \@cols;
}

foreach my $o (@rows) {
print join "\t",@{$o},"\n";
}

my $c = new Math::Matrix(@rows);
$c -> print("Bring original data in matrix\nWhy not show chars???\n");
my $tr = $c ->transpose;
$tr -> print("Transposed data\n");

my @trans = @{$tr};
foreach my $t (@trans) {
print join "\t",@{$t},"\n";
}

print "Sorted on 1st column:\n";
@trans = sort {$a->[0] cmp $b->[0]} @trans;
my $ret = new Math::Matrix(@trans);
$ret -> print("sorted data\n");
my $srt = $ret ->transpose;
$srt -> print("back to A\n");
my @srtrows = @{$srt};

foreach my $l (@srtrows) {
print join "\t",@{$l},"\n";
}

print "It seems that final result gets what I want.\nAny
suggestions???\n";

__DATA__
c2 c1 c4 c3 c6 c5
Abc 12.8 8 "left" 1 15.7
Def 13.8 9 "top" 0 19.7
gef 14.8 9 "left" 0 19.7
Dgf 12.3 9 "right" 4 99.6
cef 16.8 4 "right" 0 89.7
baf 32.8 7 "bottom" 5 79.8
efg 16.8 5 "right" 0 56.7
etg 12.8 2 "left" 7 34.7
__END__



At 11:06 AM 2/5/2004 -0800, david wrote:

>Shiping Wang wrote:
>

> > Hello,
> >
> > With this sample data set, I have a different question. How can I
> > rearrange columns such as this:
> >
> > before:
> > col1 col4 col5 col2 col6 col3
> > Abc 12.8 8 left 1 15.7
> > Def 13.8 9 top 0 19.7
> > gef 14.8 9 left 0 19.7
> > Dgf 12.3 9 right 4 99.6
> > cef 16.8 4 right 0 89.7
> > baf 32.8 7 bottom 5 79.8
> > efg 16.8 5 right 0 56.7
> > etg 12.8 2 left 7 34.7

>
>[snip]
>

> >
> > I thought one way to do it is to transpose the array, sort _ColumnName_,
> > then transpose it back. Is this right way to do it?

>
>if the columns are always out in this order, you can easily reorder them
>without sort:
>
>#!/usr/bin/perl -w
>use strict;
>
>while(<DATA>){
>
> print join("\t",map {(split)[0,3,5,1,2,4]} $_),"\n";
>}
>
>__DATA__

[David]

Shiping Wang wrote:

> Hi David, thanks for your response.
>
> My concern is that if the data has hundreds columns (which the order
> information I can get from another array or file) need to be rearranged,
> how can I do it.
>
> Here is my test program, it seems working. Any suggestion or concern???
>
> Shiping
>

__________________________________________________ __________________________________________________ ___________

> #!/usr/bin/perl
> # sortColumns.pl
> use warnings;
> use strict;
> use Math::Matrix;

[snip]

i have never used Math::Matrix so i am not sure how it behave or works but
your code seems overly complicated for such a simple problem. even there
are unknown number of columns, as long as the first line (header) is there
to suggest the right order, you don't need to sort anything or do matrix
operation. for example, the following sorts the file and it doesn't need to
know how many columns to sort. you can have hundreds or thousands of
columns:

#!/usr/bin/perl -w
use strict;

my $j = 0;

my %i = map{$_ => $j++} grep $_, split(/\D+/,my $line = <DATA>);

my @c = sort {$a <=> $b} keys %i;

#--
#-- header
#--
print join("\t",(split /\s+/,$line)[@i{@c}]),"\n";

#--
#-- the rest
#--
while(<DATA>){
print join("\t",(split)[@i{@c}]),"\n";
}

__DATA__
col1 col4 col5 col2 col6 col3
Abc 12.8 8 left 1 15.7
Def 13.8 9 top 0 19.7
gef 14.8 9 left 0 19.7
Dgf 12.3 9 right 4 99.6
cef 16.8 4 right 0 89.7
baf 32.8 7 bottom 5 79.8
efg 16.8 5 right 0 56.7
etg 12.8 2 left 7 34.7

__END__

prints:

col1 col2 col3 col4 col5 col6
Abc left 15.7 12.8 8 1
Def top 19.7 13.8 9 0
gef left 19.7 14.8 9 0
Dgf right 99.6 12.3 9 4
cef right 89.7 16.8 4 0
baf bottom 79.8 32.8 7 5
efg right 56.7 16.8 5 0
etg left 34.7 12.8 2 7

david
--
sub'_{print"@_ ";* \ = * __ ,\ & \}
sub'__{print"@_ ";* \ = * ___ ,\ & \}
sub'___{print"@_ ";* \ = * ____ ,\ & \}
sub'____{print"@_,\n"}&{_+Just}(another)->(Perl)->(Hacker)

[Shiping Wang]

At 03:31 PM 2/5/2004 -0800, david wrote:

>Shiping Wang wrote:
>

> > Hi David, thanks for your response.
> >
> > My concern is that if the data has hundreds columns (which the order
> > information I can get from another array or file) need to be rearranged,
> > how can I do it.
> >
> > Here is my test program, it seems working. Any suggestion or concern???
> >
> > Shiping
> >

>_________________________________________________ __________________________________________________ ____________

> > #!/usr/bin/perl
> > # sortColumns.pl
> > use warnings;
> > use strict;
> > use Math::Matrix;

>
>[snip]
>
>i have never used Math::Matrix so i am not sure how it behave or works but
>your code seems overly complicated for such a simple problem. even there
>are unknown number of columns, as long as the first line (header) is there
>to suggest the right order, you don't need to sort anything or do matrix
>operation. for example, the following sorts the file and it doesn't need to
>know how many columns to sort. you can have hundreds or thousands of
>columns:
>
>#!/usr/bin/perl -w
>use strict;
>
>my $j = 0;
>
>my %i = map{$_ => $j++} grep $_, split(/\D+/,my $line = <DATA>);
>
>my @c = sort {$a <=> $b} keys %i;
>
>#--
>#-- header
>#--
>print join("\t",(split /\s+/,$line)[@i{@c}]),"\n"; # Although I can guess
>this piece of code do the sorting then print out, but don't quite
>understand how it works, especially [@i{@c}].

@c = (1 2 3 4 5 6)
and
@i{@c} = ( 0 3 5 1 2 4) -------- get all the values of %i ? Something
like ($i{1} $i{2} ....${6})
then it likes
print join("\t", @_[@i{@c}]),"\n"; which become to: print join("\t", @_[
0, 3, 5, 1, 2, 4]),"\n";

perl automatically sort array before printing?

Did I understand this right?

Thank you very much,

Shiping

>#--
>#-- the rest
>#--
>while(<DATA>){
> print join("\t",(split)[@i{@c}]),"\n";
>}
>
>__DATA__
>col1 col4 col5 col2 col6 col3
>Abc 12.8 8 left 1 15.7
>Def 13.8 9 top 0 19.7
>gef 14.8 9 left 0 19.7
>Dgf 12.3 9 right 4 99.6
>cef 16.8 4 right 0 89.7
>baf 32.8 7 bottom 5 79.8
>efg 16.8 5 right 0 56.7
>etg 12.8 2 left 7 34.7
>
>__END__
>
>prints:
>
>col1 col2 col3 col4 col5 col6
>Abc left 15.7 12.8 8 1
>Def top 19.7 13.8 9 0
>gef left 19.7 14.8 9 0
>Dgf right 99.6 12.3 9 4
>cef right 89.7 16.8 4 0
>baf bottom 79.8 32.8 7 5
>efg right 56.7 16.8 5 0
>etg left 34.7 12.8 2 7
>
>david
>--
>sub'_{print"@_ ";* \ = * __ ,\ & \}
>sub'__{print"@_ ";* \ = * ___ ,\ & \}
>sub'___{print"@_ ";* \ = * ____ ,\ & \}
>sub'____{print"@_,\n"}&{_+Just}(another)->(Perl)->(Hacker)
>
>--
>To unsubscribe, e-mail: [email]beginners-unsubscribe@perl.org[/email]
>For additional commands, e-mail: [email]beginners-help@perl.org[/email]
><http://learn.perl.org/> <http://learn.perl.org/first-response>

[David]

Shiping Wang wrote:

[snip]

>>
>>#--
>>#-- header
>>#--
>>print join("\t",(split /\s+/,$line)[@i{@c}]),"\n"; # Although I can guess
>>this piece of code do the sorting then print out, but don't quite
>>understand how it works, especially [@i{@c}].

no sorting is happening in this line, the sorting is done above it and only
once. here is the originally code and explanation:

my $j = 0;

#--
#-- here we are building a hash %i that look like:
#--
#-- $i{1} = 0; #-- column 1 should become column 1
#-- $i{4} = 1; #-- column 2 should become column 4
#-- $i{5} = 2; #-- column 3 should become column 5
#-- $i{2} = 3; #-- column 4 should become column 2
#-- $i{6} = 4; #-- column 5 should become column 6
#-- $i{3} = 5; #-- column 6 should become column 3
#--
my %i = map{$_ => $j++} grep $_, split(/\D+/,my $line = <DATA>);

#--
#-- here we are building an array @c that look like:
#--
#-- $c[0] = 1;
#-- $c[1] = 2;
#-- $c[2] = 3;
#-- $c[3] = 4;
#-- $c[4] = 5;
#-- $c[5] = 6;
#--
#-- you don't really need this @c array
#--
my @c = sort {$a <=> $b} keys %i;

#--
#-- header
#--
print join("\t",(split /\s+/,$line)[@i{@c}]),"\n";

#--
#-- @i{...} is called a hash slice in Perl. @c expands to:
#--
#-- (1,2,3,4,5,6);
#--
#-- @i{@c} then become:
#--
#-- @i{1,2,3,4,5,6}
#--
#-- which is translated into:
#--
#-- ($i{1},$i{2},$i{3},$i{4},$i{5},$i{6});
#--
#-- which is then translated into:
#--
#-- (0,3,5,1,2,4);
#--
#-- finally:
#--
#-- (split /\s+/,$line)[0,3,5,1,2,4]
#--
#-- created an annoy. list which we then immediately
#-- extract in this order. this is the right order
#-- for your data file.
#--

#--
#-- the rest
#--
while(<DATA>){

#--
#-- same thing as above
#--
print join("\t",(split)[@i{@c}]),"\n";
}

__END__

> perl automatically sort array before printing?

no. if you need to sort it, you need to sort it manually. Perl does not sort
the array for you before printing.

david
--
sub'_{print"@_ ";* \ = * __ ,\ & \}
sub'__{print"@_ ";* \ = * ___ ,\ & \}
sub'___{print"@_ ";* \ = * ____ ,\ & \}
sub'____{print"@_,\n"}&{_+Just}(another)->(Perl)->(Hacker)

[cobbaptw]

Hello All,

i am having trouble re-arrange this file.
the problem is that i have one column(NBANSW) that is not properly located.
i want it to be as shown in the printout below and be able to update my database with the re-arranged file.

i really need your help guys.
thanks in anticipation for your assistance.
DATA

use strict;

while(<DATA>){

print join("\t",map {(split)[0,1,2,3,4,5,6,7]} $_),"\n";
}

__DATA__

R TRAFF NBIDS CCONG NDV ANBLO MHTIME
NBANSW
ARPT1O 38.3 887 0.0 369 0.5 155.3
388
ARPT1I 18.1 543 369 0.5 119.6
220
ARPT2O 34.1 876 0.0 340 0.0 139.9
377
ARPT2I 17.1 519 340 0.0 118.2
191
CCPBX1O 0.0 0 0.0 0 0.0 0.0
0
CCPBX1I 0.0 0 0 0.0 0.0
0
IVR2O 3.7 1006 0.0 248 0.0 13.2
1004



prints:

R TRAFF NBIDS CCONG NDV ANBLO MHTIME NBANSW
ARPT1O 38.3 887 0.0 369 0.5 155.3 388
ARPT1I 18.1 543 369 0.5 119.6 220
ARPT2O 34.1 876 0.0 340 0.0 139.9 377
ARPT2I 17.1 519 340 0.0 118.2 191
CCPBX1O 0.0 0 0.0 0 0.0 0.0 0
CCPBX1I 0.0 0 0 0.0 0.0 0
IVR2O 3.7 1006 0.0 248 0.0 13.2 1004