Correct reading of datetime with AM/PM format

user_1502668 0
Viewed 49

I'm trying to read some csv files that contain a column called 'timestamp' with this format: 7/6/2022 7:30:00 PM which should translate to (mm/dd/YYYY hh:mm:ss). What I tried was after reading the csv file using:

df['timestamp']= pd.to_datetime(df['timestamp'],format='%m/%d/%Y %I:%M:%S %p')

And it renders a totally different thing with this error:

ValueError: time data '07-06 19:30' does not match format '%m/%d/%Y %I:%M:%S %p' (match)

'07-06 19:30' This value is the same that appears when reading the csv directly with no formatting which is strange as when I open the csv the full date is there. I'm a bit lost on this case as it appears as I cannot convert the date.

Thanks

pandas csv python
1 Answers

The format='%m/%d/%Y %I:%M:%S %p' should work, make sure you read your data as string.

That said, pandas is advanced enough to figure out the format semi-automatically, the only ambiguity to resolve is to specify that the first digits are not days:

df['new_timestamp'] = pd.to_datetime(df['timestamp'], dayfirst=False)

example:

             timestamp       new_timestamp
0  7/6/2022 7:30:00 PM 2022-07-06 19:30:00
user_163434640
Related