Get weekday/day-of-week for Datetime column of DataFrame

Question

I have a DataFrame df like the following (excerpt, 'Timestamp' are the index):

Timestamp              Value 2012-06-01 00:00:00     100 2012-06-01 00:15:00     150 2012-06-01 00:30:00     120 2012-06-01 01:00:00     220 2012-06-01 01:15:00      80 ...and so on. 

I need a new column df['weekday'] with the respective weekday/day-of-week of the timestamps.

How can I get this?

Answer 1

Use the new dt.dayofweek property:

In [2]:  df['weekday'] = df['Timestamp'].dt.dayofweek df Out[2]:             Timestamp  Value  weekday 0 2012-06-01 00:00:00    100        4 1 2012-06-01 00:15:00    150        4 2 2012-06-01 00:30:00    120        4 3 2012-06-01 01:00:00    220        4 4 2012-06-01 01:15:00     80        4 

In the situation where the Timestamp is your index you need to reset the index and then call the dt.dayofweek property:

In [14]:  df = df.reset_index() df['weekday'] = df['Timestamp'].dt.dayofweek df Out[14]:             Timestamp  Value  weekday 0 2012-06-01 00:00:00    100        4 1 2012-06-01 00:15:00    150        4 2 2012-06-01 00:30:00    120        4 3 2012-06-01 01:00:00    220        4 4 2012-06-01 01:15:00     80        4 

Strangely if you try to create a series from the index in order to not reset the index you get NaN values as does using the result of reset_index to call the dt.dayofweek property without assigning the result of reset_index back to the original df:

In [16]:  df['weekday'] = pd.Series(df.index).dt.dayofweek df Out[16]:                      Value  weekday Timestamp                           2012-06-01 00:00:00    100      NaN 2012-06-01 00:15:00    150      NaN 2012-06-01 00:30:00    120      NaN 2012-06-01 01:00:00    220      NaN 2012-06-01 01:15:00     80      NaN In [17]:  df['weekday'] = df.reset_index()['Timestamp'].dt.dayofweek df Out[17]:                      Value  weekday Timestamp                           2012-06-01 00:00:00    100      NaN 2012-06-01 00:15:00    150      NaN 2012-06-01 00:30:00    120      NaN 2012-06-01 01:00:00    220      NaN 2012-06-01 01:15:00     80      NaN 

EDIT

As pointed out to me by user @joris you can just access the weekday attribute of the index so the following will work and is more compact:

df['Weekday'] = df.index.weekday