6

I try to sort a csv contain temperatures by the 4th column.

Sort -n -k4 temperature.csv

As a result I get this:

2017-06-24 11:20,23.57,19.0,16.7,0.087,3.615
2017-06-24 11:25,23.51,19.0,16.7,0.087,3.689
2017-06-24 12:45,22.03,19.0,17.1,0.096,4.152
2017-06-24 13:00,21.92,19.0,17.1,0.096,4.229
2017-06-24 14:00,22.22,19.0,17.4,0.197,4.639
2017-06-24 14:25,22.21,19.0,17.5,0.197,4.774
2017-06-24 15:10,22.30,19.0,17.1,0.134,5.472
2017-06-24 16:00,22.42,19.0,17.3,0.134,5.93
2017-06-24 17:45,22.07,21.0,17.0,0.144,6.472
2017-06-24 18:25,21.90,21.0,16.9,0.15,6.814
2017-06-24 19:40,23.01,21.0,16.9,0.318,8.503

As you can see the 4th column isn't sorted correctly. I would expect 17.5 in tbe first line and 16.7 in the last line.

I also tried this:

sort -n -t. -k4,1n temperature.csv

The result is exactly the same as the previous example. Can anyone give me a hint?

Improve this question

2 Answers 2

Reset to default
9

Use the following sort command:

sort -t, -k4,4 -nr temperature.csv

The output:

2017-06-24 14:25,22.21,19.0,17.5,0.197,4.774
2017-06-24 14:00,22.22,19.0,17.4,0.197,4.639
2017-06-24 16:00,22.42,19.0,17.3,0.134,5.93
2017-06-24 15:10,22.30,19.0,17.1,0.134,5.472
2017-06-24 13:00,21.92,19.0,17.1,0.096,4.229
2017-06-24 12:45,22.03,19.0,17.1,0.096,4.152
2017-06-24 17:45,22.07,21.0,17.0,0.144,6.472
2017-06-24 19:40,23.01,21.0,16.9,0.318,8.503
2017-06-24 18:25,21.90,21.0,16.9,0.15,6.814
2017-06-24 11:25,23.51,19.0,16.7,0.087,3.689
2017-06-24 11:20,23.57,19.0,16.7,0.087,3.615

  • -t, - field delimiter

  • -k4,4 - sort by 4th field only

  • -nr - sort numerically in reverse order

Improve this answer
2
  • 1
    A correct, useful answer (+1) but you could mention that sort can fail if the data file contains "quoted strings, that may contain commas (or even newlines)". A more general solution should involve the parsing of the data.
    – gboffi
    Commented Jun 25, 2017 at 9:13
  • 1
    @gboffi, for ideal and complex inputs there would be another complex solution. For the current input, simple sort will do the job
    – RomanPerekhrest
    Commented Jun 25, 2017 at 9:24
1

While the sort command has some hacks to partially handle CSV files, it won't handle all CSV format features. csvsort is a great option:

csvsort -c 4 temperature.csv
Improve this answer
1
  • 1
    Good. This is what I need.
    – charlesz
    Commented Aug 7, 2023 at 18:02

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