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
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
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
Method 2
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
Method 3
df =df['Date'].dt.dayofweek
dayofweek is in numeric format
Method 4
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
Method 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()
Method 6
When date is the index
…and only the first three letters are required:
df['day'] = df.index.day_name().str[:3]
Method 7
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.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0