This is a test version of Biostars. For the public version, visit https://www.biostars.org.
sorting a csv file with 3 columns

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

next-gen
• 3,372 views • link
updated by Jorge Amigo Jorge Amigo • written by dimitrischat dimitrischat

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

• 0 views • link
written by Jorge Amigo Jorge Amigo

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

• 0 views • link
written by dimitrischat dimitrischat

2 answers

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.

• 0 views • link
written by Jorge Amigo Jorge Amigo

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

• 0 views • link
written by dimitrischat dimitrischat

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

• 0 views • link
written by Dunois • Dunois

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

• 0 views • link
written by Dunois • Dunois

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

• 0 views • link
written by dimitrischat dimitrischat

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.

• 0 views • link
written by Dunois • Dunois

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

• 0 views • link
written by dimitrischat dimitrischat

Log in to answer this question.

More posts like this