Sorting csv file by date and rewriting them on the same file

Question

I want to sort the csv file by date but there's this error

ValueError: time data '.' does not match format '%d/%m/%Y'

I'm not able to figure out what I'm doing wrong.

csv file:

T.No,Date,Task,Project,Context,Message,Status
1,12/07/2019,meet @sam and @jack,python,@sam,hello,Incomplete
1,11/07/2019,meet @sam and @jack,python,@jack,hello,Incomplete
3,15/07/2019,meet @sam and @ jack,python,@sam & @,at room 12,Incomplete
4,13/07/2019,meet @sam and @jack,python,@sam & @jack,at room 12,Incomplete
5,15/07/2019,meet sam,python,,at room 12,Incomplete

    def sort():
        data = open("csv.csv", 'r')
        stdata = sorted(data, key=lambda row: datetime.strptime(row[1], "%d/%m/%Y"))
        with open("csv.csv", 'w') as f1:
            writer = csv.writer(f1)
            for eachline in stdata:
                writer.writerows(eachline)

please add the in post not in google drive – Deadpool Jul 15 '19 at 06:25 — Deadpool, Jul 15 '19 at 06:25
can you copy-paste some of your data here? – SM Abu Taher Asif Jul 15 '19 at 06:25 — SM Abu Taher Asif, Jul 15 '19 at 06:25
done i copy-pasted the csv file contents here – ZephyrX Jul 15 '19 at 06:36 — ZephyrX, Jul 15 '19 at 06:36

Alderven · Accepted Answer · 2019-07-15T08:18:27.413

3

It is easier to use pandas.DataFrame for reading CSV and sorting operations:

import pandas as pd

df = pd.read_csv('csv.csv', index_col=False)
df['Date'] = pd.to_datetime(df['Date']).dt.strftime('%d/%m/%Y')
df.sort_values('Date', inplace=True)
df.to_csv('result.csv', index=False)

Output:

T.No,Date,Task,Project,Context,Message,Status
1,07/11/2019,meet @sam and @jack,python,@jack,hello,Incomplete
1,07/12/2019,meet @sam and @jack,python,@sam,hello,Incomplete
4,13/07/2019,meet @sam and @jack,python,@sam & @jack,at room 12,Incomplete
3,15/07/2019,meet @sam and @ jack,python,@sam & @,at room 12,Incomplete
5,15/07/2019,meet sam,python,,at room 12,Incomplete

edited Jul 15 '19 at 08:18

answered Jul 15 '19 at 07:11

Alderven

6,120
4
22
35

also a nice way :) – Fabian Jul 15 '19 at 07:17
This is a great way but I'm preferring to not use pandas – ZephyrX Jul 15 '19 at 07:42
also if I use this is there a way to get the sorted data in the same format? – ZephyrX Jul 15 '19 at 07:50
Thanks @Alderven it really helped – ZephyrX Jul 15 '19 at 09:35

score 2 · Answer 2 · answered Jul 15 '19 at 07:06

2

You could use headers = next(reader) to remove the first row not including a date.

from datetime import datetime
import csv

with open('data.csv', newline='') as csv_file:  
    reader = csv.reader(csv_file)
    headers = next(reader) #removing the first row from your reader 

    sorted_reader = sorted(reader, key = lambda row: datetime.strptime(str(row[1]), "%d/%m/%Y"))

    for row in sorted_reader:
        print(row)

answered Jul 15 '19 at 07:06

Fabian

1,047
8
24

This works but what if I want to write it to the same csv file or a different one – ZephyrX Jul 15 '19 at 07:41

score 0 · Answer 3 · edited Jul 15 '19 at 07:12

0

This works for me:

datetime.strptime("2009/10/2", "%Y/%m/%d")

edited Jul 15 '19 at 07:12

jkdev

10,404
15
54
77

answered Jul 15 '19 at 06:45

mahtab

76
1
6

Hey, I know this works but I'm reading from a csv file so need something more – ZephyrX Jul 15 '19 at 07:00
print row[1] and tell me about type and value – mahtab Jul 15 '19 at 07:07

Sorting csv file by date and rewriting them on the same file

3 Answers3

Linked