Create a day-of-week column in a Pandas dataframe using Python
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
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
In version 0.18.1 is added dt.weekday_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.weekday_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.weekday_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
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
df =df['Date'].dt.dayofweek
dayofweek is in numeric format
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()
