shell: processing of multi column file

Question

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

You still haven't told us your operating system. Are you maybe on macOS? — terdon
– terdon ♦, Commented 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
– terdon ♦, Commented 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! — user3470313
– user3470313, Commented 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
– terdon ♦, Commented Oct 9, 2020 at 10:05

choroba · Accepted Answer · 2020-10-09 10:49:15Z

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).

edited Oct 9, 2020 at 10:49

answered Oct 9, 2020 at 8:31

choroba

49.7k7 gold badges92 silver badges119 bronze badges

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 ?

user3470313
– user3470313

2020-10-09 08:52:28 +00:00
Commented Oct 9, 2020 at 8:52
@user3470313: Sorry, updated.

choroba
– choroba

2020-10-09 08:55:54 +00:00
Commented 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

user3470313
– user3470313

2020-10-09 08:57:52 +00:00
Commented Oct 9, 2020 at 8:57
What values does it sort wrongly?

choroba
– choroba

2020-10-09 09:25:51 +00:00
Commented 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

user3470313
– user3470313

2020-10-09 10:01:56 +00:00
Commented Oct 9, 2020 at 10:01

| Show 3 more comments

terdon · Accepted Answer · 2020-10-09 08:54:13Z

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

answered Oct 9, 2020 at 8:54

terdon♦

253k69 gold badges481 silver badges719 bronze badges

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

user3470313
– user3470313

2020-10-09 08:59:27 +00:00
Commented 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
– terdon ♦

2020-10-09 09:01:12 +00:00
Commented 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)'

user3470313
– user3470313

2020-10-09 09:08:09 +00:00
Commented 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
– terdon ♦

2020-10-09 09:24:02 +00:00
Commented 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 ..

user3470313
– user3470313

2020-10-09 09:28:54 +00:00
Commented Oct 9, 2020 at 9:28

| Show 2 more comments

Stack Exchange Network

shell: processing of multi column file

2 Answers 2

You must log in to answer this question.

Hot Network Questions

shell: processing of multi column file

2 Answers 2

You must log in to answer this question.

Related

Hot Network Questions