Pandas Get Day of Week from date type column

Question

I'm using Python 3.6 and Pandas 0.20.3.

I have a column that I've converted to date type from datetime. All I need is the date. I have it as a derived column for ease of use. But I'm looking to do some further operations via a day of the week calculation. I can get the day of week from a datetime type but not from the date. It seems to me that this should be possible but I've tried multiple variations and not found success.

Here is an example:

import numpy as np
import pandas as pd
df = pd.DataFrame({'date':['2017-5-16','2017-5-17']})
df['trade_date']=pd.to_datetime(df['date'])

I can get the day of the week from the datetime column 'trade_date'.

df['dow']=df['trade_date'].dt.dayofweek
df
    date    trade_date  dow
0   2017-5-16   2017-05-16  1
1   2017-5-17   2017-05-17  2

But if I have a date, rather than a datetime, no dice: For instance:

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

And then:

df['dow_2']=df['trade_date_2'].dt.dayofweek

I get (at the end):

AttributeError: Can only use .dt accessor with datetimelike values

I've tried various combinations of dayofweek(), weekday, weekday() which, I realize, highlight my ignorance of exactly how Pandas works. So ... any suggestions besides adding another column which is the datetime version of column trade_date? I'll also welcome explanations of why this is not working.

Answer 1

There is problem it is difference between pandas datetime (timestamps) where are implemented .dt methods and python date where not.

#return python date
df['trade_date_2']= pd.to_datetime(df['date']).dt.date

print (df['trade_date_2'].apply(type))
0    <class 'datetime.date'>
1    <class 'datetime.date'>
Name: trade_date_2, dtype: object

#cannot work with python date
df['dow_2']=df['trade_date_2'].dt.dayofweek

Need convert to pandas datetime:

df['dow_2']= pd.to_datetime(df['trade_date_2']).dt.dayofweek

print (df)
        date trade_date_2  dow_2
0  2017-5-16   2017-05-16      1
1  2017-5-17   2017-05-17      2

So the best is use:

df['date'] = pd.to_datetime(df['date'])
print (df['date'].apply(type))
0    <class 'pandas._libs.tslib.Timestamp'>
1    <class 'pandas._libs.tslib.Timestamp'>
Name: date, dtype: object

df['trade_date_2']= df['date'].dt.date
df['dow_2']=df['date'].dt.dayofweek
print (df)
        date trade_date_2  dow_2
0 2017-05-16   2017-05-16      1
1 2017-05-17   2017-05-17      2

EDIT:

Thank you Bharath shetty for solution working with python date - failed with NaT:

df = pd.DataFrame({'date':['2017-5-16',np.nan]})

df['trade_date_2']= pd.to_datetime(df['date']).dt.date
df['dow_2'] = df['trade_date_2'].apply(lambda x: x.weekday()) 

AttributeError: 'float' object has no attribute 'weekday'

Comparing solutions:

df = pd.DataFrame({'date':['2017-5-16','2017-5-17']})
df = pd.concat([df]*10000).reset_index(drop=True)

def a(df):
    df['trade_date_2']= pd.to_datetime(df['date']).dt.date
    df['dow_2'] = df['trade_date_2'].apply(lambda x: x.weekday()) 
    return df

def b(df):
    df['date1'] = pd.to_datetime(df['date'])
    df['trade_date_21']= df['date1'].dt.date
    df['dow_21']=df['date1'].dt.dayofweek
    return (df)

def c(df):
    #dont write to column, but to helper series 
    dates = pd.to_datetime(df['date'])
    df['trade_date_22']= dates.dt.date
    df['dow_22']=        dates.dt.dayofweek
    return (df)

In [186]: %timeit (a(df))
10 loops, best of 3: 101 ms per loop

In [187]: %timeit (b(df))
10 loops, best of 3: 90.8 ms per loop

In [188]: %timeit (c(df))
10 loops, best of 3: 91.9 ms per loop