0

How can I unique-sort a .csv file first by ID then by PRICE, and then, if possible, by DATE. Here is an example file:

"Date","other","Id","other","Price"
"01/01/2016","3","6452312546-232","a","4.5"
"01/03/2016","1","4375623416-345","b","56.25"
"01/03/2016","6","4375623416-345","c","0"
"01/03/2016","5","4375623416-345","d","0"
"02/01/2016","4","6452312546-232","e","34.21"

I want the output to sort by ID first, so that everything is grouped by ID first, then once they're grouped, sort the individual groups by PRICE, and then sort the now PRICE-sorted groups by most recent date in the group. So I'd get this as output:

"Date","other","Id","other","Price"
"02/01/2016","4","6452312546-232","e","34.21"
"01/01/2016","3","6452312546-232","a","4.5"
"01/03/2016","1","4375623416-345","b","56.25"
"01/03/2016","6","4375623416-345","c","0"
"01/03/2016","5","4375623416-345","d","0"

Is that clear? Let me know if it's not.

Forwarding
  • 245
  • 1
  • 12
  • I tried [this](http://stackoverflow.com/questions/28942306/sort-row-of-a-csv-file-by-first-value), but I get an error: `block (2 levels) in shift': Illegal quoting in line 2` Which is just this line: `my_csv = CSV.read 'file.csv'` – Forwarding Sep 29 '16 at 18:02
  • Are your dates "day/month/year"? – Cary Swoveland Sep 29 '16 at 18:20
  • @CarySwoveland, I'm running ruby 2.0.0, and yes they are dd/mm/yyyy. – Forwarding Sep 29 '16 at 18:24
  • I I deleted my answer because after posting it I noticed @dlachasse's earlier answer, which takes the same approach as I did, and also includes the reading of the CSV file (which I did not address). – Cary Swoveland Sep 29 '16 at 19:51
  • @CarySwoveland, no problem, just have to update ruby and I'm good. – Forwarding Sep 29 '16 at 20:28

1 Answers1

1

Assuming this CSV file is small enough to safely load into memory, you can read the file into a hash and sort it from there. 

require 'csv'

table = CSV.read('file.csv', headers: true).map { |row| Hash[row] }
table.sort_by { |k, _| [k["id"], k["price"].to_f, Date.parse(k["date"])] }

Although, if you're initially sorting by ID you're going to lose any granularity of the price and date fields further down the line.

dlachasse
  • 540
  • 6
  • 11
  • That is what I was thinking! But I didn't know hashes could do that. – Forwarding Sep 29 '16 at 18:06
  • I suggest you leave the id's as strings, as they contain hyphens, so `"12-34".to_i #=> 12`, and for all we know two ids could be the same before the hyphen but different after. In any event, as long as all the ids are of the same length, sorting them as strings is fine. – Cary Swoveland Sep 29 '16 at 19:36
  • 2
    We got [Array#to_h](http://ruby-doc.org/core-2.1.0/Array.html#method-i-to_h) with Ruby v2.1. Forwarding states in a comment that he/she is using v2.0.0. So that's the problem. Instead use [Hash::[\]](http://ruby-doc.org/core-2.3.0/Hash.html#method-c-5B-5D), or, better, upgrade to at least v2.1. (Upgrade in any event, as there were problems with v2.0.0.) – Cary Swoveland Sep 29 '16 at 20:00