Sorting Timestamps inside a CSV file with Python

Question

I'm trying to sort the content of a csv file by the given timestamps but it just doesn't seem to work for me. They are given in such a way:

2021-04-16 12:59:26+02:00

My current code:

from datetime import datetime
import csv
from csv import DictReader

with open('List_32_Data_New.csv', 'r') as read_obj:
    csv_dict_reader = DictReader(read_obj)

    csv_dict_reader = sorted(csv_dict_reader, key = lambda row: datetime.strptime(row['Timestamp'], "%Y-%m-%d %H:%M:%S%z"))

    writer = csv.writer(open("Sorted.csv", 'w'))

    for row in csv_dict_reader:
        writer.writerow(row)

However it always throws the error: time data '2021-04-16 12:59:26+02:00' does not match format '%Y-%m-%d %H:%M:%S%z'

I tried already an online compiler at apparently it works there.

Any help would be much appreciated.

Andreas · Accepted Answer · 2021-04-30 08:52:25Z

1

If you use pandas as a library it could be a bit easier (Credits to: MrFuppes).

import pandas as pd

df = pd.read_csv(r"path/your.csv")

df['new_timestamps'] = pd.to_datetime(df['timestamps'], format='%Y-%m-%d %H:%M:%S%z')
df = df.sort_values(['new_timestamps'], ascending=True)

df.to_csv(r'path/your.csv')

If you still have errors you can also try to parse the date like this (Credits to: Zerox):

from dateutil.parser import parse
df['new_timestamps'] = df['timestamps'].map(lambda x: datetime.strptime((parse(x)).strftime('%Y-%m-%d %H:%M:%S%z'), '%Y-%m-%d %H:%M:%S%z'))

Unsure about the correct datetime-format? You can try auto-detection infer_datetime_format=True:

df['new_timestamps'] = pd.to_datetime(df['timestamps'], infer_datetime_format=True)

Tested with following sample:

df = pd.DataFrame(['2021-04-15 12:59:26+02:00','2021-04-13 12:59:26+02:00','2021-04-16 12:59:26+02:00'], columns=['timestamps'])

edited Apr 30, 2021 at 8:52

answered Apr 28, 2021 at 9:20

Andreas

9,2854 gold badges20 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Zerox Over a year ago

Thank you very much for your answer! It worked for me now. For some reason it still gave me the same error, however when I changed it to

df['new_timestamps'] = df['timestamps'].map(lambda x: datetime.strptime((parser.parse(x)).strftime('%Y-%m-%d %H:%M:%S%z'), '%Y-%m-%d %H:%M:%S%z'))

it worked. Just wanted to add this bit of information in case someone runs into the same problem.

Andreas Over a year ago

Glad I could help. I updated my answer to include your findings for future readers. Please let me know if that is fine by you. Happy Coding!

FObersteiner Over a year ago

You should not need the datetime lib for parsing these strings (nice ISO8601), and neither dateutil ! pd.to_datetime will do the trick more readable and more efficiently.

Collectives™ on Stack Overflow

Sorting Timestamps inside a CSV file with Python

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related