5

I have a file that consists of lines like this (other numbers included). This is part of the output of

$ grep 1848 filename.csv

How can I find the top 5 lines which have the lowest third column in the .csv file given that 1848 is either in the first or second column?

1848,2598,11.310694021273559
1848,2599,10.947275955606203
1848,2600,10.635270124233982
1848,2601,11.916564552040725
1848,2602,12.119810736845844
1848,2603,12.406661156256154
1848,2604,10.636275056472996
1848,2605,12.549890992708612
1848,2606,9.783802450936204
1848,2607,11.253697489670264
1848,2608,12.16385432290674
1848,2609,10.30355814063016
1848,2610,12.102525596913923
1848,2611,11.636595992818505
1848,2612,10.741178028606866
1848,2613,11.352414275107423
1848,2614,12.204860161717253
1848,2615,12.959915468475387
1848,2616,11.320652192610872

Unfortunately 1848 sometimes appears in third column as well and I need to ignore that:

6687,8963,9.241848677632822
6687,9111,10.537325656184889
6687,9506,11.315629894841848
  • this is a question for stackoverflow. – Phillip -Zyan K Lee- Stockmann Nov 11 '16 at 20:36
  • 1
    @Phillip-ZyanKLee-Stockmann this question is perfectly on topic – Zanna Nov 11 '16 at 20:43
  • @Phillip-ZyanKLee-Stockmann What makes you think that this is a question for stackoverflow? – heemayl Nov 11 '16 at 20:55
  • 1
    ok, my wording might have been a little to strong: What I meant was, it is not directly related to ubuntu and the answers on stackoverflow (and perhaps unix, too) might be more diverse and present even better solutions. This is based on my personal experience, though. – Phillip -Zyan K Lee- Stockmann Nov 11 '16 at 20:56
  • you are right, though, it is not off-topic. – Phillip -Zyan K Lee- Stockmann Nov 11 '16 at 20:59
8

With GNU sort:

grep -E '(^1848|^[0-9]{4},1848)' file | sort -t, -k3n | head -n 5

(if the first column may have less or more than exactly 4 digits, replace {4} with +)

Output:

1848,2606,9.783802450936204
1848,2609,10.30355814063016
1848,2600,10.635270124233982
1848,2604,10.636275056472996
1848,2612,10.741178028606866
  • 2
    OP mentions that 1848 may occur in the second column, so maybe we need something like grep -E '(^1848|^[0-9]{4},1848)' – Zanna Nov 11 '16 at 20:50
  • @cyrus there are cases that 1848 starts in the second column and I want to include them as well 2606,1848, 8.4507745 for example – Mona Jalal Nov 11 '16 at 21:08
  • @Zanna what's the complete command? $ grep '(^5785|^[0-9]{4},5785)' imgur_merged_graph.csv | sort -t, -k3n | head -n5 didn't work – Mona Jalal Nov 11 '16 at 21:12
  • 1
    More concisely, use grep -E '(^[0-9]+,|^)1848,' file. No need to repeat the 1848 part. Also, nothing in that is specific to GNU sort; it's POSIX standard. – Wildcard Nov 12 '16 at 1:21
  • 1
    @MonaJalal is there a comma? grep -E '(^[0-9]+,|^)202,' imgur_merged_graph.csv | sort -t, -k3n | head -n5? – Zanna Nov 17 '16 at 23:28
6

With just awk:

awk -F, 'BEGIN{PROCINFO["sorted_in"]="@ind_num_asc"} \
          $1==1848||$2==1848 {a[$3]=$0} END {for(i in a) print a[i]}' file.csv
  • BEGIN{PROCINFO["sorted_in"]="@ind_num_asc"} sets the order of any array that will be created according to the index, according to the digits, in an ascending style

  • $1==1848||$2==1848 {a[$3]=$0} checks if the first or the second field is 1848, if so an then the third field ($3) is taken as an array a index, with the value being the whole record ($0)

  • In the END {for(i in a) print a[i]}, we are simple iterating over the keys and printing the values

To get only the 5 records, add head -5 at end:

awk ... | head -5

Just for the sake of completeness, you can obviously get only the first 5 records by incorporating a tiny break logic in the END looping, no need for tail:

awk -F, 'BEGIN{PROCINFO["sorted_in"]="@ind_num_asc"} \
          $1==1848||$2==1848 {a[$3]=$0} END {j=0; for(i in a) \
           {print a[i]; j++; if(j==5) break}}' file.csv

Example:

% cat file.txt
1848,2598,11.310694021273559
1848,2599,10.947275955606203
1848,2600,10.635270124233982
1848,2601,11.916564552040725
1848,2602,12.119810736845844
1848,2603,12.406661156256154
1848,2604,10.636275056472996
1848,2605,12.549890992708612
1848,2606,9.783802450936204
1848,2607,11.253697489670264
1848,2608,12.16385432290674
1848,2609,10.30355814063016
1848,2610,12.102525596913923
1848,2611,11.636595992818505
1848,2612,10.741178028606866
1848,2613,11.352414275107423
1848,2614,12.204860161717253
1848,2615,12.959915468475387
1848,2616,11.320652192610872

% awk -F, 'BEGIN{PROCINFO["sorted_in"]="@ind_num_asc"} $1==1848||$2==1848 {a[$3]=$0} END {for(i in a) print a[i]}' file.txt
1848,2606,9.783802450936204
1848,2609,10.30355814063016
1848,2600,10.635270124233982
1848,2604,10.636275056472996
1848,2612,10.741178028606866
1848,2599,10.947275955606203
1848,2607,11.253697489670264
1848,2598,11.310694021273559
1848,2616,11.320652192610872
1848,2613,11.352414275107423
1848,2611,11.636595992818505
1848,2601,11.916564552040725
1848,2610,12.102525596913923
1848,2602,12.119810736845844
1848,2608,12.16385432290674
1848,2614,12.204860161717253
1848,2603,12.406661156256154
1848,2605,12.549890992708612
1848,2615,12.959915468475387

% awk -F, 'BEGIN{PROCINFO["sorted_in"]="@ind_num_asc"} $1==1848||$2==1848 {a[$3]=$0} END {j=0; for(i in a) {print a[i]; j++; if(j==5) break}}' file.txt 
1848,2606,9.783802450936204
1848,2609,10.30355814063016
1848,2600,10.635270124233982
1848,2604,10.636275056472996
1848,2612,10.741178028606866

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.