0

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 - i dont mention the 2nd and 3rd row because i want it sorted via the 1st column

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

here is the file: http://www.mediafire.com/file/v0azlrb3sx9r5x7/example.csv/file

2

3 Answers 3

1

Try this,

awk 'BEGIN{FS=OFS=","} NR==FNR{s[$1]++} NR>FNR{print s[$1],$0}' csvfile csvfile \
| sort -n | cut -d, -f2-
  • awk will count number of occurrences of first field (s[$1]++) and then adds it (print s[$1],$0 to the rows). (Note, that awk needs to read the file twice).
  • sort -n will sort the result
  • cut -d, -f2- will remove the number again.
3
  • didnt work. the sorting gets messed up
    – Jim Hat
    Sep 24, 2020 at 10:30
  • 1
    it works fine with the example provided. Please see my comment on your question.
    – pLumo
    Sep 24, 2020 at 10:30
  • just uploaded it to mediafire.
    – Jim Hat
    Sep 24, 2020 at 10:39
0

Using Miller (https://github.com/johnkerl/miller) you can apply this command to your TSV

mlr --tsv count-similar -g type -o count then sort -n count ./example.csv >./output.tsv

to have something like

+------+-------+-----------+-------+
| type | value | condition | count |
+------+-------+-----------+-------+
| 66   | 0     | hela      | 2     |
| 66   | 0     | hela      | 2     |
| 23   | 1     | hela      | 6     |
| 23   | 1     | hela      | 6     |
| 23   | 1     | hela      | 6     |
| 23   | 1     | hela      | 6     |
| 23   | 1     | hela      | 6     |
| 23   | 1     | hela      | 6     |
| 27   | 1     | hela      | 9     |
| 27   | 1     | hela      | 9     |
| 27   | 1     | hela      | 9     |
| 27   | 1     | hela      | 9     |
| 27   | 1     | hela      | 9     |
| 27   | 1     | hela      | 9     |
| 27   | 1     | hela      | 9     |
| 27   | 1     | hela      | 9     |
| 27   | 1     | hela      | 9     |
| 34   | 0     | hela      | 9     |
| 34   | 0     | hela      | 9     |
| 34   | 0     | hela      | 9     |
| 34   | 0     | hela      | 9     |
| 34   | 0     | hela      | 9     |
| 34   | 0     | hela      | 9     |
| 34   | 0     | hela      | 9     |
| 34   | 0     | hela      | 9     |
| 34   | 0     | hela      | 9     |
| 2    | 0     | hela      | 14    |
| 2    | 0     | hela      | 14    |
| 2    | 0     | hela      | 14    |
| 2    | 0     | hela      | 14    |
| ..   | ..    | ..        | ..    |
  • count-similar -g type to count and group by type;
  • -o count to set the output count field;
  • sort -n count to sort by count field.
0

Aside from writing those amazing queries, another way of sorting the columns of CSV files is to utilize a Relational Database Management System(RDBMS), such as Acho.

  1. Upload your CSV as a Resource

    You can upload your CSV file using Single CSV or Multiple CSV. Please note that all the column names, column data types, and the number of columns need to be the same if you are uploading multiple CSV files as MCSV.

    If the CSV upload is not successful, then just try out the Safe mode, it can automatically turn all the columns into String data type.

  2. Add the resource into a project

    After uploading the CSV, just add the CSV file into a project, and go to the workspace to view the table. In order to sort the column, you can simply click the Sort button under Column Dropdown to sort the column you need.

The GUI is easy to understand and maneuver.

enter image description here

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.