How to specify year,month,day in datetime from Dataframe?

Question

I have this dataframe:

7542  08/01/2015 00:09:19
7543  08/01/2015 01:01:07
7544  08/01/2015 01:05:42
7545  08/01/2015 01:53:52
7546  08/01/2015 02:28:56

I want to convert it to datetime format, but when I use pd.to_datetime I get this error:

ValueError: to assemble mappings requires at least that [year, month, day] be specified: [day,month,year] is missing

I've tried pd.to_datetime(dataf.stack()).unstack() and I got a NaT.

try passing `infer_datetime_format=True` to `pd.to_datetime`. It's one of the examples here on the documentation: https://pandas.pydata.org/pandas-docs/stable/generated/pandas.to_datetime.html — Jordan A., Jun 07 '18 at 04:02
I have no problems converting this to datetime using `pd.to_datetime` — user3483203, Jun 07 '18 at 04:03
Is there any chance the day/month/year is getting read as part of the index? — user3483203, Jun 07 '18 at 04:04
I printed df.index and the output was 7542, 7543, etc, and df.columns and got this Index(['date'], dtype='object') @HarvIpan — Carolina, Jun 07 '18 at 04:08
@Carolina - Do you try `dataf['date'] = pd.to_datetime(dataf['date'])` ? — jezrael, Jun 07 '18 at 05:02

score 2 · Answer 1 · answered Jun 08 '18 at 02:07

After some playing around, I figured an easy way to do it:

sample_df = pd.DataFrame({'datetime':['08/01/2015 00:09:19', '08/01/2015 01:01:07', '08/01/2015 01:05:42']})
pd.to_datetime(sample_df.datetime, infer_datetime_format=True)

Yeah, it's that simple. I think to_datetime needs the data to be in a certain format, and passing the specific column of the dataframe helps it get that format.

score 1 · Answer 2 · answered Jun 08 '18 at 02:57

In addition to infer_datetime_format=True, you can also pass a format explicitly:

>>> pd.to_datetime(sample_df['datetime'], format='%m/%d/%Y %H:%M:%S') 
0   2015-08-01 00:09:19
1   2015-08-01 01:01:07
2   2015-08-01 01:05:42
Name: datetime, dtype: datetime64[ns]

I asked a question about this a little while back and there's a great answer from @AlexRiley: Inferring date format versus passing a parser.

jezrael · Answer 3 · 2018-06-07T05:59:46.400

Error means your code select DataFrame, so pd.to_datetime looking for year, month and day columns:

print (pd.to_datetime(dataf))

ValueError: to assemble mappings requires at least that [year, month, day] be specified: [day,month,year] is missing

Solution is select column date with parameter dayfirst=True if first number is day in to_datetime:

dataf['date'] = pd.to_datetime(dataf['date'], dayfirst=True)
print (dataf)
                    date
7542 2015-01-08 00:09:19
7543 2015-01-08 01:01:07
7544 2015-01-08 01:05:42
7545 2015-01-08 01:53:52
7546 2015-01-08 02:28:56

print (dataf.dtypes)
date    datetime64[ns]
dtype: object

Setup:

d = {'date': {7542: '08/01/2015 00:09:19', 7543: '08/01/2015 01:01:07', 7544: '08/01/2015 01:05:42', 7545: '08/01/2015 01:53:52', 7546: '08/01/2015 02:28:56'}}
dataf = pd.DataFrame(d)
print (dataf)
                     date
7542  08/01/2015 00:09:19
7543  08/01/2015 01:01:07
7544  08/01/2015 01:05:42
7545  08/01/2015 01:53:52
7546  08/01/2015 02:28:56

How to specify year,month,day in datetime from Dataframe?

3 Answers3