sorting a csv file with 3 columns

Question

[dimitrischat]

Hello all,

I am trying to sort a csv file containing 3 values. The first one is the one that matters. It contains many times the number 1, 2, 3, 4 etc etc. But each number doesnt appear the same number of times as the other number. So, i wanna sort the csv file from its 1st column, from less times of appearance of each number to the highest number of appearces.

for example

1
1
1
2
2
2
2
2
3
4
4

->

3
4
4
1
1
1
2
2
2
2

using mac but i can get access to a linux

[Pierre Lindenbaum]

how is it related to bioinformatics ?

[Jorge Amigo]

we could give him the chance that the numbers are autosomal chromosomes ;)

[dimitrischat]

yes they are, i just changed the chr"x" to number for convenience.

Answer 1

[Jorge Amigo]

There are multiple ways you could do it. Here's my suggestion:

perl -ne 'BEGIN { open IN, "input.csv" or die $!;
while (<IN>) { /^(\S+)/ and $d{$1}++ } close IN }
/^(\S+)/ and print "$d{$1}\t$_"' input.csv \
| sort -k1,1n -k2,2V | cut -f2-

The idea behind this code is to read the file once to count the number of times the first column appears, and to read the file twice to create a new first column with those previous counts. You just then need to sort numerically by that new first column and remove it at the end.

[dimitrischat]

is there any way to do this without perl? thanks a lot for your help!!

[Dunois]

What's your problem with using Perl? (Not being rude or anything.)

[Jorge Amigo]

I see. Would you find yourself more confortable by implementing that same rationale on Excel perhaps?

How to sort a column by occurrence count in Excel?

[dimitrischat]

i ll try in excel. but the command above doesnt work for my file : http://www.mediafire.com/file/v0azlrb3sx9r5x7/example.csv/file

[Dunois]

Do you have R available? If yes, I can offer you a small R script that'll do the sorting for you.

[dimitrischat]

yea i do, i use R studio (if that helps)

Answer 2

[Dunois]

Here this script should help then. (Let me know if you can't access it.)

Looks like I've also reached my post limit for the next six hours, so if you need help with the script, just reply to this comment, and I'll edit my replies in here.

[dimitrischat]

yes, finally!!! worked! thank you!!