Datetime format not accepted, did I mean .at?-CodePudding

I'm trying to sort an excel file by datetime. This is my code:

import pandas as pd
from datetime import datetime
from datetime import date

dagStart = datetime.strptime(str(date.today()) ' 06:00:00', '%Y-%m-%d %H:%M:%S')
dagEind = datetime.strptime(str(date.today()) ' 23:00:00', '%Y-%m-%d %H:%M:%S')

data = pd.read_excel('inzamelbestand.xlsx', index_col=9) 

data = data.loc[data['ingezameldop'].dt.time.between(dagStart.time(), dagEind.time())]

data.to_excel("oefenexcel.xlsx")

When I run it, it gives the following message:

Traceback (most recent call last):
  File "C:\Users\Inzamelhelden\orden.py", line 10, in <module>
    data = data.loc[data['ingezameldop'].dt.time.between(dagStart.time(), dagEind.time())]
  File "C:\Users\Inzamelhelden\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\core\generic.py", line 5907, in __getattr__
    return object.__getattribute__(self, name)
  File "C:\Users\Inzamelhelden\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\core\accessor.py", line 183, in __get__
    accessor_obj = self._accessor(obj)
  File "C:\Users\Inzamelhelden\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\core\indexes\accessors.py", line 513, in __new__
    raise AttributeError("Can only use .dt accessor with datetimelike values")
AttributeError: Can only use .dt accessor with datetimelike values. Did you mean: 'at'?

It seems to me that the datetime format is not accepted, but how would i go about solving this?

CodePudding user response：

You likely have strings and should convert to_datetime:

data = data.loc[pd.to_datetime(data['ingezameldop'])
                  .dt.time.between(dagStart.time(), dagEind.time())]

Or if you don't mind having datetime type in the output:

data['ingezameldop'] = pd.to_datetime(data['ingezameldop'])
# then as you did
data = data.loc[data['ingezameldop'].dt.time.between(dagStart.time(), dagEind.time())]

CodePudding user response：

First is necessary test what is type of column ingezameldop:

print (type(data['ingezameldop'].iat[0]))

If it is time use:

df = data[pd.to_datetime(data['ingezameldop'].between(dagStart.time(), dagEind.time())]

If object or time with DataFrame.between_time for DatetimeIndex use:

data['ingezameldop'] = pd.to_datetime(data['ingezameldop'].astype(str))
df = data.set_index('ingezameldop').between_time('06:00:00', '23:00:00')