CODEWITHSUNDEEP
pythonsortingcsv
user3651150
user3651150

Reputation: 13

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.

Upvotes: 1

Views: 1146

Answers (2)

user764357
user764357

Reputation:

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])))

Upvotes: 1

Tal Yarkoni
Tal Yarkoni

Reputation: 2564

You could do this very easily with pandas:

import pandas as pd

header = data.pop(0)
df = pd.DataFrame(data, columns=header)
print df.sort('Date')

Output:

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

Upvotes: 0

Related Questions