Convert date to day in pandas-CodePudding

I have a dataframe and I want to have a column for weekdays based on dates. My data frame is like this:

     Location        Date_Time          Volume
0  Perkins-YMCA, 2017-07-01 00:00:00      34
1  Perkins-YMCA, 2017-07-01 00:15:00      30
2  Perkins-YMCA, 2017-07-01 00:30:00      23
3  Perkins-YMCA, 2017-07-01 00:45:00      26
4  Perkins-YMCA, 2017-07-01 01:00:00      21

I first separate the dates and time and created a separate column as below:

df['Date'] = pd.to_datetime(df['Date_Time']).dt.date

but when I use

df['Weekdays']=df['Date'].dt.dayofweek

I receive this error:

Can only use .dt accessor with datetimelike values. Did you mean: 'at'?

I'm not sure what is the problem.

CodePudding user response：

dt.date returns a series of object dtype with cells being Python's datetime.datetime type, so you can't do dt.dayofweek. You can do:

df['Date'] = pd.to_datetime(df['Date_Time']).dt.floor('D')

and you get a Datetime dtype, and the subsequent df['Date'].dt.daysofweek should work.

On the other note, if you insist on datetime.datetime type, you can convert your Date_Time column to datetime type:

df['Date_Time'] = pd.to_datetime(df['Date_Time'])
df['Date'] = df['Date_Time'].dt.date
df['Weekdays'] = df['Date_Time'].dt.daysofweek

CodePudding user response：

You can use .dt.dayofweek after pd.to_datetime when values convert to datetime. When you store .dt.date you change type of dtype: datetime64[ns] to dtype: object. for applying dt.(anythings) you need to have values as dtype: datetime64[ns].

df['Weekdays'] = pd.to_datetime(df['Date_Time']).dt.dayofweek
print(df)

       Location            Date_Time  Volume  Weekdays
0  Perkins-YMCA  2017-07-01 00:00:00      34         5
1  Perkins-YMCA  2017-07-01 00:15:00      30         5
2  Perkins-YMCA  2017-07-01 00:30:00      23         5
3  Perkins-YMCA  2017-07-01 00:45:00      26         5
4  Perkins-YMCA  2017-07-01 01:00:00      21         5