104

Create a day-of-week column in a Pandas dataframe using Python

I’d like to read a csv file into a pandas dataframe, parse a column of dates from string format to a date object, and then generate a new column that indicates the day of the week.

This is what I’m trying:

What I’d like to do is something like:

import pandas as pd

import csv

df = pd.read_csv('data.csv', parse_dates=['date']))

df['day-of-week'] = df['date'].weekday()


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

Thank you for your help. James

Mike Müller
  • 82,630
  • 20
  • 166
  • 161
James Eaves
  • 1,587
  • 3
  • 17
  • 22

7 Answers7

192

Pandas 0.23+

Use pandas.Series.dt.day_name(), since pandas.Timestamp.weekday_name has been deprecated:

import pandas as pd


df = pd.DataFrame({'my_dates':['2015-01-01','2015-01-02','2015-01-03'],'myvals':[1,2,3]})
df['my_dates'] = pd.to_datetime(df['my_dates'])

df['day_of_week'] = df['my_dates'].dt.day_name()

Output:

    my_dates  myvals day_of_week
0 2015-01-01       1    Thursday
1 2015-01-02       2      Friday
2 2015-01-03       3    Saturday

Pandas 0.18.1+

As user jezrael points out below, dt.weekday_name was added in version 0.18.1 Pandas Docs

import pandas as pd

df = pd.DataFrame({'my_dates':['2015-01-01','2015-01-02','2015-01-03'],'myvals':[1,2,3]})
df['my_dates'] = pd.to_datetime(df['my_dates'])
df['day_of_week'] = df['my_dates'].dt.weekday_name

Output:

    my_dates  myvals day_of_week
0 2015-01-01       1    Thursday
1 2015-01-02       2      Friday
2 2015-01-03       3    Saturday

Original Answer:

Use this:

http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.dt.dayofweek.html

See this:

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

If you want a string instead of an integer do something like this:

import pandas as pd

df = pd.DataFrame({'my_dates':['2015-01-01','2015-01-02','2015-01-03'],'myvals':[1,2,3]})
df['my_dates'] = pd.to_datetime(df['my_dates'])
df['day_of_week'] = df['my_dates'].dt.dayofweek

days = {0:'Mon',1:'Tues',2:'Weds',3:'Thurs',4:'Fri',5:'Sat',6:'Sun'}

df['day_of_week'] = df['day_of_week'].apply(lambda x: days[x])

Output:

    my_dates  myvals day_of_week
0 2015-01-01       1       Thurs
1 2015-01-02       2         Fri
2 2015-01-01       3       Thurs
Peque
  • 13,638
  • 11
  • 69
  • 105
Liam Foley
  • 7,432
  • 2
  • 26
  • 24
  • 1
    Liam, I've imported the datetime module as dt, but I'm receiving this new error. Do you know what's going on? Thanks again! `df['day_of_week'] = df[‘my_dates'].dt.dayofweek` AttributeError: 'Series' object has no attribute 'dt' – James Eaves May 13 '15 at 18:58
  • 1
    When you run `df.dtypes` do you get `my_dates datetime64[ns]`? – Liam Foley May 13 '15 at 19:07
  • Yes, I get = datetime64[ns] for my date column. – James Eaves May 13 '15 at 19:13
  • Strange. What do you get when you run `pd.__version__` (AKA, what version are you on?) – Liam Foley May 13 '15 at 19:24
  • 1
    @JamesEaves the `dt` accessor was added in `0.15.0` can you upgrade? – EdChum May 13 '15 at 19:52
  • 1
    Yes, i updated and it works perfectly. Thank you all for your help! – James Eaves May 13 '15 at 20:45
  • Now that is a nifty use of the lambdas – SARose Jan 09 '16 at 20:16
  • The string names aren't built in somewhere? – endolith Jul 04 '16 at 00:28
  • 1
    answering self: `from calendar import day_abbr` and then `data.index = [day_abbr[x] for x in data.index]` – endolith Jul 04 '16 at 01:06
  • 1
    @LiamFoley I updated your answer with the latest API updates. Hope you do not mind. Feel free to revert it if you want. I thought it would be good to have an updated answer as the most voted. ^^ – Peque Aug 30 '18 at 16:03
  • I suggest following optimization to make the result more memory-efficient: `df['day_of_week'] = df['my_dates'].dt.day_name().astype('category')` - ie. 'cast' the series to the category type, as this consumes much less memory than the original strings. – Daddy32 Sep 30 '19 at 14:06
15

Use dt.day_name:

print df
    my_dates  myvals
0 2015-01-01       1
1 2015-01-02       2
2 2015-01-03       3

print df.dtypes
my_dates    datetime64[ns]
myvals               int64
dtype: object

df['day_of_week'] = df['my_dates'].dt.day_name()
print df
    my_dates  myvals day_of_week
0 2015-01-01       1    Thursday
1 2015-01-02       2      Friday
2 2015-01-03       3    Saturday

Another solution with assign:

print df.assign(day_of_week = df['my_dates'].dt.day_name())
    my_dates  myvals day_of_week
0 2015-01-01       1    Thursday
1 2015-01-02       2      Friday
2 2015-01-03       3    Saturday
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • 1
    This is [`dt.day_name`](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.dt.day_name.html) now (since 0.23.0) – vk1011 Apr 24 '23 at 23:36
11
df =df['Date'].dt.dayofweek

dayofweek is in numeric format

Catruc Iurie
  • 109
  • 1
  • 6
8

Using dt.weekday_name is deprecated since pandas 0.23.0, instead, use dt.day_name():

df = pd.DataFrame({'my_dates':['2015-01-01','2015-01-02','2015-01-03'],'myvals':[1,2,3]})
df['my_dates'] = pd.to_datetime(df['my_dates'])

df['my_dates'].dt.day_name()

0    Thursday
1      Friday
2    Saturday
Name: my_dates, dtype: object
user3483203
  • 50,081
  • 9
  • 65
  • 94
5

Just in case if .dt doesn't work for you. Trying .DatetimeIndex might help. Hope the code and our test result here help you fix it. Regards,

import pandas as pd
import datetime

df = pd.DataFrame({'Date':['2015-01-01','2015-01-02','2015-01-03'],'Number':[1,2,3]})

df['Day'] = pd.DatetimeIndex(df['Date']).day_name() # week day name
df.head()

enter image description here

Denis Rasulev
  • 3,744
  • 4
  • 33
  • 47
Pinkmei
  • 69
  • 1
  • 5
1

When date is the index

…and only the first three letters are required:

df['day'] = df.index.day_name().str[:3]
Serge Stroobandt
  • 28,495
  • 9
  • 107
  • 102
0

data['Day_Of_Week'] = pd.DatetimeIndex(data['Birth_Date']).day_name()

Command appends new column/feature as data['Day_Of-Week'] from data['Birth_Date'] column which is present in dataset/csv previously.

suraj kakade
  • 1
  • 1