Python/Pandas: How do I convert from datetime64[ns] to datetime

Question

I have a script that processes an Excel file. The department that sends it has a system that generated it, and my script stopped working.

I suddenly got the error Can only use .str accessor with string values, which use np.object_ dtype in pandas for the following line of code:

df['DATE'] = df['Date'].str.replace(r'[^a-zA-Z0-9\._/-]', '')

I checked the type of the date columns in the file from the old system (dtype: object) vs the file from the new system (dtype: datetime64[ns]).

How do I change the date format to something my script will understand?

I saw this answer but my knowledge about date formats isn't this granular.

You can convert dates back to strings with `df['Date'].dt.strftime('%Y-%m-%d')`. — IanS, Aug 29 '16 at 13:38

score 9 · Answer 1 · answered Nov 01 '17 at 17:25

You can use apply function on the dataframe column to convert the necessary column to String. For example:

df['DATE'] = df['Date'].apply(lambda x: x.strftime('%Y-%m-%d'))

Make sure to import datetime module.

apply() will take each cell at a time for evaluation and apply the formatting as specified in the lambda function.

score 7 · Answer 2 · edited Aug 18 '20 at 11:41

7

pd.to_datetime returns a Series of datetime64 dtype, as described here:

df['DATE'] = df['Date'].dt.date

or this:

df['Date'].map(datetime.datetime.date)

edited Aug 18 '20 at 11:41

theletz

answered Aug 18 '20 at 09:34

Jose

score 2 · Accepted Answer · edited May 07 '19 at 14:54

2

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

edited May 07 '19 at 14:54

Andrei Savin

answered May 07 '19 at 14:34

Amandeep Singh

52

I do not know why this answer is accepted as I believe it maintains type datetime64[ns] – xxyjoel Aug 31 '20 at 05:17

score 2 · Answer 4 · edited May 30 '23 at 11:00

Most of the answers here are outdated. I stumbled up on this issue and thought it is a bug in Pandas and raised an issue here.

Turns out, datetime64 is not a valid dtype at all. So, we have to convert it to any of the valid dtypes below.

In pandas below version 2, s.astype('datetime64') doesn't throw any error, simply ignores it. Starting with pandas 2.0.0, it throws an error.

4 Answers4