23499

Sorting a csv object by dates in python

Question:

I am trying to read and sort a csv file that has data that looks like

Date Open High Low Close Volume 27-Mar-12 8.25 8.35 8.17 8.19 9801989 26-Mar-12 8.16 8.25 8.12 8.24 8694416 23-Mar-12 8.05 8.12 7.95 8.09 8149170

I do this with

import csv data = csv.reader(open('data.csv','r'))

To sort data by Date. I do:

sorteddata = sorted(data,key=operator.itemgetter(1),reverse=False)

The problem is, that it sorted the dates by reading them as String and not as dates. So the data is sorted like so,

['3-Aug-11', '7.06', '7.23', '6.84', '7.16', '31583617'] ['3-Feb-12', '7.02', '7.12', '6.98', '7.08', '15318044'] ['3-Jan-12', '5.53', '5.59', '5.44', '5.48', '12678923'] ['3-Jun-11', '8.09', '8.17', '7.92', '7.97', '21273812'] ['3-May-11', '9.00', '9.04', '8.63', '8.80', '17356005']

Does anybody know how to sort by dates?

Answer1:

Use <a href="http://docs.python.org/library/datetime.html#datetime.datetime.strptime" rel="nofollow">datetime.strptime</a> to get a datetime from the date field:

from datetime import datetime data = sorted(data, key = lambda row: datetime.strptime(row[0], "%d-%b-%y"))

Answer2:

Use the time module for time format conversions, and convert your time strings (3-Aug-11) into numbers you can sort.

Here's some food for thought:

>>> t = time.strptime("3-Aug-11","%d-%b-%y") >>> t time.struct_time(tm_year=2011, tm_mon=8, tm_mday=3, tm_hour=0, tm_min=0, tm_sec=0, tm_wday=2, tm_yday=215, tm_isdst=-1) >>> time.mktime(t) 1312300800.0

<a href="http://docs.python.org/library/time.html#time.strftime" rel="nofollow">Documentation for time module.</a>

Recommend

  • Finding minimum values in a list of dicts
  • Shuffle groups of sublists in Python
  • Merge duplicates list of dictionaries item value
  • group values in intervals
  • Getting directory of input file (Applescript)
  • Finding parents in a tree hierarchy for a given child LINQ (lambda expression)
  • How to sort things out in ListView?
  • Python find continuous interesctions of intervals
  • python - calculate orthographic similarity between words of a list
  • Iterate twice through a DataReader
  • Python to parent/child JSON
  • How to turn (A, B, C) into (AB, AC, BC) with Pig?
  • Invalid Date on validation Date of js
  • Ajax Upload File: $_FILES is empty but files exists in request header
  • RxJava debounce by arbitrary value
  • Sort List of Strings By Version
  • How to suppress a dialog
  • Why value captured by reference in lambda is broken? [duplicate]
  • Illegal mix of collations for operation for date/time comparison
  • How to redirect a user to a different server and include HTTP basic authentication credentials?
  • Running a C# exe file
  • Join two tables and save into third-sql
  • Can I make an Android app that runs a web view in Chrome 39?
  • How to model a transition system with SPIN
  • Release, debug version and Authorization Google?
  • ORA-29908: missing primary invocation for ancillary operator
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • Python: how to group similar lists together in a list of lists?
  • Hits per day in Google Big Query
  • Why joiner is not used after Sequence generator or Update statergy
  • LevelDB C iterator
  • Linking SubReports Without LinkChild/LinkMaster
  • Bitwise OR returns boolean when one of operands is nil
  • sending mail using smtp is too slow
  • Django query for large number of relationships
  • Recursive/Hierarchical Query Using Postgres
  • costura.fody for a dll that references another dll
  • Binding checkboxes to object values in AngularJs
  • UserPrincipal.Current returns apppool on IIS
  • Converting MP3 duration time