How to change an incorrect datetime format from a raw csv file to a proper datetime format in Python

Question

I have a Python Dataframe like this

Index Name Dateofbirth

0      A.     12JAN1980:00:00:00.000000
1      B.   17JUN1954:00:00:00.000000
...
1250000  X.  09DEC1957:00:00:00.0000

The problem is that in the raw data csv file, my dates are stored in this format %d%m%Y:00:00:00.000000

So, the issue arises when I read this csv file into Python and convert Date of birth column into datetime with the following code

df['Dateofbirth'] =pd.to_datetime(df['Dateofbirth'])

I get the following error :

raise ValueError("Unknown string format:", timestr) ValueError: ('Unknown string format:', '12JAN1980:00:00:00.000000

How can I change this format into the acceptable datetime format of %Y%m%d %H%M%S ? Changing the raw csv file is out of the question as there are over 1000000 rows.

Please help! I apologize for any lack of text formatting.

No it is stored with Date first, year end, but you are using `%Y%M%D` — Phung Duy Phong, Feb 11 '20 at 05:41
But I'm not specifying anywhere that I need a Y first format. Either way is fine. How should I modify my code then? — coffee-raid, Feb 11 '20 at 05:51
Hi yes, you can specify the format, example: `pd.to_datetime('13000101', format='%Y%m%d', errors='ignore')`, — Phung Duy Phong, Feb 11 '20 at 05:52
But now that entire column is an object instead of datetime64[ns] — coffee-raid, Feb 11 '20 at 06:07
I suppose that did not work. Because the entire column is still of a string type (object) instead of the required datetime64[ns] — coffee-raid, Feb 11 '20 at 06:17
`pd.to_datetime` work on string and convert datetime type, is that what you want — Phung Duy Phong, Feb 11 '20 at 06:19
Thank you for your help. I think the main issue was because I kept writing %m when it should have been %b for the months. — coffee-raid, Feb 11 '20 at 07:30

loginmind · Answer 1 · 2020-02-11T06:31:56.933

0

You can try this, it will return Dateofbirth with type object:

df['Dateofbirth'] = pd.to_datetime(df.Dateofbirth)
df['Dateofbirth'] = df['Dateofbirth'].dt.strftime('%Y%m%d %H%M%S')

or using this if you want Dateofbirth as type datetime:

df['Dateofbirth'] = pd.to_datetime(df['Dateofbirth'])
df['Dateofbirth'] = pd.to_datetime(df['Dateofbirth'].dt.strftime('%Y%m%d %H%M%S'))

edited Feb 11 '20 at 06:31

answered Feb 11 '20 at 06:18

loginmind

563
5
11

Thank you for your help, also the issue was because I kept writing %m when it should have been %b for the months. – coffee-raid Feb 11 '20 at 07:31
Oh yes, I didnt recognize that, sorry, your part say change into, but think you want to use data with that format and convert :D – Phung Duy Phong Feb 20 '20 at 04:51

How to change an incorrect datetime format from a raw csv file to a proper datetime format in Python

1 Answers1