73

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?

smci
  • 32,567
  • 20
  • 113
  • 146
EP1986
  • 853
  • 1
  • 7
  • 14

5 Answers5

119

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

Artyom Krivolapov
  • 3,161
  • 26
  • 32
EdChum
  • 376,765
  • 198
  • 813
  • 562
8

If the Timestamp column is a datetime value, then you can just use:
df['weekday'] = df['Timestamp'].apply(lambda x: x.weekday())

or

df['weekday'] = pd.to_datetime(df['Timestamp']).apply(lambda x: x.weekday())

Community
  • 1
  • 1
QM.py
  • 652
  • 1
  • 8
  • 16
8

You can get with this way:

import datetime
df['weekday'] = pd.Series(df.index).dt.day_name()
berkayln
  • 935
  • 1
  • 8
  • 14
3

As of pandas 1.1.0 dt.dayofweek is deprecated, so instead of:

df['weekday'] = df['Timestamp'].dt.dayofweek

from @EdChum and @Artyom Krivolapov

you can now use:

df['weekday'] = df['Timestamp'].dt.isocalendar().day
NHarding
  • 31
  • 1
1

In case somebody else has the same issue with a multiindexed dataframe, here is what solved it for me, based on @joris solution:

df['Weekday'] = df.index.get_level_values(1).weekday

for me date was the get_level_values(1) instead of get_level_values(0), which would work for the outer index.

TiTo
  • 833
  • 2
  • 7
  • 28