user3651150 user3651150 - 2 months ago 9
Python Question

Sorting csv column by date

I have a list similar to this

Date,dataA,dataB,dataC,dataD,dataE
1/1/2013,45.817,0.000,0.352,5.880,39.585
2/1/2013,21.154,0.000,0.348,6.562,14.244
3/1/2013,16.901,0.000,0.344,4.765,11.792
4/1/2013,54.324,1.051,7.597,10.896,34.780
5/1/2013,45.223,1.483,0.379,7.602,35.759
6/1/2013,25.140,1.336,0.402,5.678,17.724
...


It goes all the way to 31/12/2013.

This is my code:

for row in data:
row[0]=row[0].strftime('%d/%m/%y')
month=int(row[0].split('/')[1])
if month== 1:
print (row[0])


The output:

01/01/13
13/01/13
14/01/13
15/01/13
16/01/13
17/01/13
18/01/13
19/01/13
20/01/13
21/01/13
22/01/13
23/01/13
24/01/13
25/01/13
26/01/13
27/01/13
28/01/13
29/01/13
30/01/13
31/01/13
02/01/13
03/01/13
04/01/13
05/01/13
06/01/13
07/01/13
08/01/13
09/01/13
10/01/13
11/01/13
12/01/13


My question is how do I sort the list so that it is in order (01/01/2013,02/01/2013...), or sort it before appending it to a list?

What I want is to create a chart for a single selected month(e.g jan/feb...) using the data (one of the column). Plotting the data without sorting it would be confusing.

Answer

Sorting of lists accepts a key parameter to dictate how to sort the items in the list. The only challenge is yours is based on a date rather than a simple comparator.

firstItemDate = lambda x:date(*map(int,(x.split(",",1)[0].split("/"))[::-1]))
print sorted(data[1:],key=firstItemDate)

The key firstItemDate can be broken down like so:

  • lambda x: - an anonymous function that takes an argument (each line of the array)
  • date( - create a datetime.date object (this takes 3 arguments)
  • * - unpack the argument list into the date function
  • map(int,( - map a function, in this case the int casting to a list
  • x.split(",",1)[0] - split the line by the commas (as its a csv) and take the 0th element - as per Jons comment we can limit it to just 1 split as we only the first column.
  • .split("/") - split the 0th element on the / as its a date
  • )[::-1])) - reverse the list using array slicing, so its year, month, day

Because date objects define how they are sorted, this will ensure that dates are valid and sorted correctly.

For brevity, if you were never going to use the firstItemDate function again, you can insert it into the sorted call, like so:

print sorted(data[1:],key=lambda x:date(*map(int,(x.split(",")[0].split("/"))[::-1])))
Comments