0

column file in the following format and I am using macOS:

 ./tmp/7000_CNE_lig752_cl_0.5| -10.1000| 58
./tmp/7000_CNE_lig1029_cl_0.5| -8.0200| 40
./tmp/7000_CNE_lig183_cl_0.5| -8.0400| 32
./tmp/7000_CNE_lig247_cl_0.5| -8.3000| 28
./tmp/7000_CNE_lig331_cl_0.5| -8.9400| 31
./tmp/7000_CNE_lig578_cl_0.5| -8.2400| 76
./tmp/7000_CNE_lig587_cl_0.5| -8.5800| 35
./tmp/7000_CNE_lig659_cl_0.5| -8.0600| 31
./tmp/7000_CNE_lig679_cl_0.5| -8.7500| 36
./tmp/7000_CNE_lig767_cl_0.5| -8.9600| 86
./tmp/7000_CNE_lig830_cl_0.5| -8.2700| 32
./tmp/7000_CNE_lig841_cl_0.5| -8.1600| 32
./tmp/7000_CNE_lig842_cl_0.5| -8.5700| 33
./tmp/7000_CNE_lig900_cl_0.5| -8.1400| 61
./tmp/7000_CNE_lig931_cl_0.5| -8.9400| 40
./tmp/7000_CNE_lig933_cl_0.5| -8.6200| 101
./tmp/7000_CNE_lig987_cl_0.5| -8.6200| 55
./tmp/7000_CNE_lig996_cl_0.5| -8.2300| 31
./tmp/7000_CNE_lig998_cl_0.5| -8.6800| 48
./tmp/7000_CNE_lig999_cl_0.5| -8.0000| 29
./tmp/7000_CNE_lig1002_cl_0.5| -7.0900| 32
./tmp/7000_CNE_lig1009_cl_0.5| -7.4600| 22
./tmp/7000_CNE_lig1023_cl_0.5| -7.8700| 25
./tmp/7000_CNE_lig1032_cl_0.5| -7.1200| 20
./tmp/7000_CNE_lig1033_cl_0.5| -7.4400| 26
./tmp/7000_CNE_lig118_cl_0.5| -7.4400| 11
./tmp/7000_CNE_lig179_cl_0.5| -7.8000| 26
./tmp/7000_CNE_lig180_cl_0.5| -7.3800| 38
./tmp/7000_CNE_lig24_cl_0.5| -7.3600| 32
./tmp/7000_CNE_lig25_cl_0.5| -7.0600| 26

I need some one line SORT command which will sort the values according to the second column from lowest to highest. Here what I have already tried using sort command:

sort -t'|' -nk2 7000_CNE_TOP.csv
7000_CNE_lig752_cl_0.5| -10.1000| 58
7000_CNE_lig1029_cl_0.5| -8.0200| 40
7000_CNE_lig183_cl_0.5| -8.0400| 32
7000_CNE_lig247_cl_0.5| -8.3000| 28
7000_CNE_lig331_cl_0.5| -8.9400| 31
7000_CNE_lig578_cl_0.5| -8.2400| 76
7000_CNE_lig587_cl_0.5| -8.5800| 35
7000_CNE_lig659_cl_0.5| -8.0600| 31
7000_CNE_lig679_cl_0.5| -8.7500| 36
7000_CNE_lig767_cl_0.5| -8.9600| 86
7000_CNE_lig830_cl_0.5| -8.2700| 32
7000_CNE_lig841_cl_0.5| -8.1600| 32
7000_CNE_lig842_cl_0.5| -8.5700| 33
7000_CNE_lig900_cl_0.5| -8.1400| 61
7000_CNE_lig931_cl_0.5| -8.9400| 40
7000_CNE_lig933_cl_0.5| -8.6200| 101
7000_CNE_lig987_cl_0.5| -8.6200| 55
7000_CNE_lig996_cl_0.5| -8.2300| 31
7000_CNE_lig998_cl_0.5| -8.6800| 48
7000_CNE_lig999_cl_0.5| -8.0000| 29
7000_CNE_lig1002_cl_0.5| -7.0900| 32
7000_CNE_lig1009_cl_0.5| -7.4600| 22
7000_CNE_lig1023_cl_0.5| -7.8700| 25
7000_CNE_lig1032_cl_0.5| -7.1200| 20
7000_CNE_lig1033_cl_0.5| -7.4400| 26
7000_CNE_lig118_cl_0.5| -7.4400| 11
7000_CNE_lig179_cl_0.5| -7.8000| 26
7000_CNE_lig180_cl_0.5| -7.3800| 38
7000_CNE_lig24_cl_0.5| -7.3600| 32

or alternatively using -nk 2,2, which gives the same results

sort -t'|' -nk2,2 7000_CNE_TOP.csv
7000_CNE_lig752_cl_0.5| -10.1000| 58
7000_CNE_lig1029_cl_0.5| -8.0200| 40
7000_CNE_lig183_cl_0.5| -8.0400| 32
7000_CNE_lig247_cl_0.5| -8.3000| 28
7000_CNE_lig331_cl_0.5| -8.9400| 31
7000_CNE_lig578_cl_0.5| -8.2400| 76
7000_CNE_lig587_cl_0.5| -8.5800| 35
7000_CNE_lig659_cl_0.5| -8.0600| 31
7000_CNE_lig679_cl_0.5| -8.7500| 36
7000_CNE_lig767_cl_0.5| -8.9600| 86
7000_CNE_lig830_cl_0.5| -8.2700| 32
7000_CNE_lig841_cl_0.5| -8.1600| 32
7000_CNE_lig842_cl_0.5| -8.5700| 33
7000_CNE_lig900_cl_0.5| -8.1400| 61
7000_CNE_lig931_cl_0.5| -8.9400| 40
7000_CNE_lig933_cl_0.5| -8.6200| 101
7000_CNE_lig987_cl_0.5| -8.6200| 55
7000_CNE_lig996_cl_0.5| -8.2300| 31
7000_CNE_lig998_cl_0.5| -8.6800| 48
7000_CNE_lig999_cl_0.5| -8.0000| 29
7000_CNE_lig1002_cl_0.5| -7.0900| 32
7000_CNE_lig1009_cl_0.5| -7.4600| 22
7000_CNE_lig1023_cl_0.5| -7.8700| 25
7000_CNE_lig1032_cl_0.5| -7.1200| 20
7000_CNE_lig1033_cl_0.5| -7.4400| 26
7000_CNE_lig118_cl_0.5| -7.4400| 11
7000_CNE_lig179_cl_0.5| -7.8000| 26
7000_CNE_lig180_cl_0.5| -7.3800| 38
4
  • 1
    You still haven't told us your operating system. Are you maybe on macOS?
    – terdon
    Oct 9, 2020 at 9:40
  • And why have you now completely changed your input? You had ./tmp and now you have /Users/gleb/Desktop/DOcking/clusterizator/tmp making all answers obsolete. Which one is it? What input are you running the sort -t'|' -nk2,2 command on? It can't be on the file you show, the output has no /Users... so you must have done something else. We cannot help you if you don't explain what you are doing.
    – terdon
    Oct 9, 2020 at 9:40
  • sorry I took another log but I adapted your SED script to remove PATH so please ignore the begining, I will remove it from the post, since there is no more problem with 1) (sed part). And yes, I am using MAC !! Before I used LINUX and the sorting comman worked perfectly btw! Oct 9, 2020 at 9:58
  • Yes, the BSD sort that macOS uses is different. Does it work if you do this: sed 's/| */|/g' file.csv | sort -nk2,2? That will remove the spaces after the |.
    – terdon
    Oct 9, 2020 at 10:05

2 Answers 2

1

Use sed for 1 and switch off locale to sort the floats.

sed 's=^\./tmp/\([^|]*\)\.txt|=\1|=' 1 | LC_ALL=C sort -t'|' -k2,2n

The sed expression removes ./tmp/ from the start of each line, but also the .txt before the first | - that's why the capture matches non-pipes.

I used -k2,2 to sort based on the second column exclusively. Using -k2 would sort based on the following columns if the values in the second column are the same (I'm not sure what you want in such a case).

8
  • your sollution is good, but SED removed also I separator after the first column (after ,txt) so the first two column were mixed and sorting was based on the third column. is it possible to fix SED statement ? Oct 9, 2020 at 8:52
  • @user3470313: Sorry, updated.
    – choroba
    Oct 9, 2020 at 8:55
  • thank you! I've just retested. However sort does the job still not accurately. i don't see any difference between sort -t'|' -k2,2g anf sort -t'|' -k2 Oct 9, 2020 at 8:57
  • What values does it sort wrongly?
    – choroba
    Oct 9, 2020 at 9:25
  • sorry I just want to mentio that I use MAC now (not LINuX) and mb it cause the problem with sort function, since it works correctly with the same things on LINUX Oct 9, 2020 at 10:01
0

If the ./tmp/ and .txt each always appear only once per line, you could just do:

sed 's|\./tmp/||; s|\.txt||' file | sort -t'|' -nk2,2

The trick is, as Choroba also mentions, to use -k2,2 so as to only sort on the second field.

Alternatively, you could just do the whole thing in Perl:

$ perl -F'\|' -lane 's|\./tmp/||; ## remove the .tmp
                     s|\.txt||;   ## remove the .txt
                     $k{$F[1]}=$_;  ## Use the 2nd field as the key for a hash 
                                    ## and the line as the value
                     }{  ## after the end of the file...
                    ## sort the hash's keys and print its values
                    print $k{$_} for sort {$a<=>$b} keys(%k)' file
7000_CNE_lig1036_cl_0.5| -10.1000| 58
7000_CNE_lig1029_cl_0.5| -8.0200| 40
7000_CNE_lig1023_cl_0.5| -7.8700| 25
7000_CNE_lig1009_cl_0.5| -7.4600| 22
7000_CNE_lig1033_cl_0.5| -7.4400| 26
7000_CNE_lig1032_cl_0.5| -7.1200| 20
7000_CNE_lig1002_cl_0.5| -7.0900| 32
7000_CNE_lig1035_cl_0.5| -6.4100| 22
7000_CNE_lig1027_cl_0.5| -6.2000| 21
7000_CNE_lig1016_cl_0.5| -6.0700| 22
7000_CNE_lig1024_cl_0.5| -5.9800| 35
7000_CNE_lig1019_cl_0.5| -5.9300| 19
7000_CNE_lig1034_cl_0.5| -5.8600| 21
7000_CNE_lig1020_cl_0.5| -5.8000| 18
7000_CNE_lig101_cl_0.5| -5.6200| 19
7000_CNE_lig1003_cl_0.5| -5.4600| 24
7000_CNE_lig1017_cl_0.5| -5.3300| 18
7000_CNE_lig1010_cl_0.5| -5.3000| 21
7000_CNE_lig1001_cl_0.5| -5.2900| 13
7000_CNE_lig1015_cl_0.5| -5.2300| 26
7000_CNE_lig1028_cl_0.5| -5.0700| 20
7000_CNE_lig1018_cl_0.5| -4.9500| 20
7000_CNE_lig1025_cl_0.5| -4.9100| 29
7000_CNE_lig100_cl_0.5| -4.5300| 13
7
  • thank you! I've just retested. Sed works fine, however sort does the job still not accurately. i don't see any difference between sort -t'|' -k2,2 and sort -t'|' -k2 Oct 9, 2020 at 8:59
  • @user3470313 isn't the output I show correct? If not, please update your question and show us input data that reproduces the problem.
    – terdon
    Oct 9, 2020 at 9:01
  • your's output is OK! btw I've tried also your perl script and what he sent me ./clusterizator.sh: line 39: syntax error near unexpected token (' ./clusterizator.sh: line 39: print $k{$_} for sort {$a<=>$b} keys(%k)' Oct 9, 2020 at 9:08
  • @user3470313 what is ./clusterizator.sh? That's a perl script, not a shell scipt. Just copy/paste the perl command directly into your terminal.
    – terdon
    Oct 9, 2020 at 9:24
  • Right, I gotcha! Actually I am dealing with some shell script which loop the files etc so probably perl script may not work there ... I've just did the test trying sorting values on my output using sort -t'|' -k2,2 or sort -t'|' -k2 sort -t'|' -k2,2g etc but there has not been any difference .. Oct 9, 2020 at 9:28

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .