Solved

# Sorting two columns in a csv file

Posted on 2007-08-09
2,360 Views
What is the best way to sort on two columns in a .csv file? Here is an example:

serverone, a1
serverone, cpu
serverone, a2
servertwo, cpu
servertwo, a2
servertwo, a1
etc....

What I would like to do is sort it like so:

serverone, a1
serverone, a2
serverone, cpu
servertwo, a1
servertwo, a2
servertwo, cpu
etc...

I got the first column using 'sort', but can't figure out the best way to sort the second.

Thanks!
David
0
Question by:David Aldridge

LVL 31

Expert Comment

You could concatenate the contents of the two columns together and sort on that.

Or, probably the more correct thing to do would be to perform your comparison on the first column, just like you're doing now, but in the event that they are equal, do a comparison on the second column as the tiebreaker.
0

LVL 31

Accepted Solution

Perl's Sort() function can be passed the name of a user defined subroutine that it will call to perform the comparison, if none of perl's default comparison methods will work in this case. The user defined subroutine is passed the two parameters \$a and \$b and it is the subroutine's job to compare them and return -1, 0 or 1 based on if \$a is less than, equal to or greater than \$b.

Basic Sorting With Sort
ex:
@x = ("the", "bob", "Bob");
@y = sort (@x);           # @y is ("Bob", "bob", "the")

- The subroutine is written to compare the two scalar values,
\$a and \$b, and return the following:
-1 if \$a should appear BEFORE \$b in the sorted list
0 if \$a and \$b are equal in the sort order
1 if \$a should appear AFTER \$b in the sorted list

e.g:

sub by_number_ascending
{
\$a <=> \$b;
}

@x = (25, 14, -3);
@y = sort (by_number_ascending @x);             # @y is (-3, 14, 25)

So you'd write your subroutine to compare the first column, then, if they're equal, compare the second column for the tiebreaker.

http://www.research.umbc.edu/~tarr/perl/perl4/ch17-sorting.html
0

LVL 39

Assisted Solution

print map {"\$_->[0],\$_->[1]"} sort {(\$a->[0] cmp \$b->[0]) or (\$a->[1] cmp \$b->[1])} map{[split /,\s*/]} <>;
0

LVL 17

Assisted Solution

#!/usr/bin/perl -w
use strict;
my %server_data;

while (<DATA>) {
next if /^\s*\$/;
chomp;
my (\$server, \$data) = split /,\s*/;
\$server_data{\$server}{\$data}++;
}

foreach my \$server (sort keys %server_data) {
foreach my \$data (sort keys %{\$server_data{\$server}}) {
print "\$server, \$data\n";
}
}

__DATA__
serverone, a1
servertwo, cpu
serverone, a2
servertwo, a2
serverone, cpu
servertwo, a1
0

LVL 84

Assisted Solution

print sort <DATA>;
__DATA__
serverone, a1
serverone, cpu
serverone, a2
servertwo, cpu
servertwo, a2
servertwo, a1

prints
serverone, a1
serverone, a2
serverone, cpu
servertwo, a1
servertwo, a2
servertwo, cpu

did you want somethinng else?
0

LVL 1

Author Comment

Ozo is absolutely correct. After doing some more research, I found out that I had some corrupted data in the csv file which was causing the problem. I apologize for wasting everyone's time. I think the fair thing is to split the points between the four responders.

Thanks everyone,
David
0

## Featured Post

I've just discovered very important differences between Windows an Unix formats in Perl,at least 5.xx.. MOST IMPORTANT: Use Unix file format while saving Your script. otherwise it will have ^M s or smth likely weird in the EOL, Then DO NOT use m…
I have been pestered over the years to produce and distribute regular data extracts, and often the request have explicitly requested the data be emailed as an Excel attachement; specifically Excel, as it appears: CSV files confuse (no Red or Green h…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…