python - Pandas: can only use .dt accessor with datetimelike values

Question

I have a Pandas dataframe that looks as follows:

key      system      impl_date
1        madison     2021-01-27T13:16:18.000-0600
2        madison     2021-01-27T13:15:04.000-0600
3        lexington   2021-01-27T13:08:27.000-0600
4        park        2021-01-27T13:05:42.000-0600

The impl_date column is (I think!) a string because earlier in the script I apply the following:

df = df.applymap(str)

I want to take the impl_date column and strip the time element, resulting in a date that takes the following form:

yyyy-mm-dd

To do so, I use the following:

df['impl_date'] = pd.to_datetime(df['impl_date']).dt.strftime('%Y-%m-%d')

But, this fails with the following error message:

AttributeError:  Can only use .dt accessor with datetimelike values

So, I tried the following:

df['impl_date'] = pd.to_datetime(df['impl_date'], errors='coerce').dt.strftime('%Y-%m-%d')

This fails with the same error message.

df.dtypes gives the following:

key         object
system      object
impl_date   object
type:  object

type(df) gives:

pandas.core.series.Series

And, df.info() gives:

#  Column      Non-Null Count   Dtype
-  ------      --------------   -----
0  key         6453 non-null    object
1  system      6453 non-null    object
2  impl_date   6453 non-null    object

Given that (I think!) the impl_date is represented as a string, what's the best way to transform this column to a yyyy-dd-mm format?

Thanks!

question from:https://stackoverflow.com/questions/65948188/pandas-can-only-use-dt-accessor-with-datetimelike-values

Answer 1

Your data may contain different timezones, like this:

key      system      impl_date
1        madison     2021-01-27T13:16:18.000-0600
2        madison     2021-01-27T13:15:04.000-0600
3        lexington   2021-01-27T13:08:27.000-0600
5        park        2021-01-27T13:05:42.000-0500   # here

Option is to pass utc=True to to_datetime:

pd.to_datetime(df['impl_date'], errors='coerce', utc=True).dt.strftime('%Y-%m-%d')

And you get:

0    2021-01-27
1    2021-01-27
2    2021-01-27
3    2021-01-27
Name: impl_date, dtype: object