pandas: Create a 'Weekday' DataFrame column by converting date (mm/dd/yy) to weekday

Question

I'm attempting to convert a date to a weekday - a least the weekday number, 0 for Monday, 1 for Tuesday etc. The date is an object data type.

When I try to find information/help to convert date to weekday, or adding a new dataframe column called 'weekday' and doing the conversion into there, I can't find anything on Python

I found this:

data.loc[:,'weekday'] = data.index.weekday

but it doesn't seem to work, as I get this error:

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

There are no easy to find examples. Can anyone help?

can you please some more of your code? Where is `data` coming from? — WreckeR, Apr 24 '16 at 04:17
That's all I have at the moment; the code is what I'm looking for.. The data is hundreds of thousands of records.. — Theant6118, Apr 24 '16 at 04:20
I am guessing you are using `pandas`. I have added that tag so hopefully somebody who knows this module will be able to help. — WreckeR, Apr 24 '16 at 04:29
Ah.. yes I am. Apologies.. Should have mentioned that. Thanks very much.. — Theant6118, Apr 24 '16 at 04:30
looks like my edit is under review. It would be better if you edit your question and add tags or update the title. — WreckeR, Apr 24 '16 at 04:31

score 2 · Answer 1 · answered Apr 24 '16 at 05:21

2

Not sure about the structure of your data, but if you turn them to datetime this should work:

import pandas as pd

df = pd.DataFrame({'date': ['2014/06/01', '2014/01/01', '2014/08/01','2014/09/01','2014/10/01']})
df = pd.to_datetime(df['date'])

df_weekdays = df.dt.dayofweek

answered Apr 24 '16 at 05:21

Glrs

1,060
15
26

No, it's not working.. Not really sure where to go from here.. Thanks though – Theant6118 Apr 24 '16 at 07:07

score 2 · Accepted Answer · edited May 23 '17 at 10:27

2

looking at provided pieces of code and error message one can see that you are trying to extract weekday from integer index, which is difficult. ;)

Abbas and Tasos have already shown how to do that using proper data types (datetime like types)

PS one hint for future: when asking questions always try to provide a Minimal, Complete, and Verifiable example. In case of pandas questions please provide sample input and output data sets (5-7 rows in CSV/dict/JSON/Python code format as text, so one could use it when coding an answer for you).

This will help to avoid situations like: "your code isn't working for me" or "it doesn't work with my data". etc.

edited May 23 '17 at 10:27

Community

1
1

answered Apr 24 '16 at 09:41

MaxU - stand with Ukraine

205,989
36
386
419

Thanks MaxU.. I only just figured out your first statement - I'm new to the game :-) . And thanks also for the advice.. much appreciated – Theant6118 Apr 24 '16 at 10:06
@Theant6118, always glad to help :) – MaxU - stand with Ukraine Apr 24 '16 at 10:10

score 1 · Answer 3 · answered Apr 24 '16 at 07:24

Here is an example of creating a calendar kind of representation using pandas, shall help you with manipulating date in pandas:

Output would look like this:

Code

import pandas as pd
cal = pd.DataFrame(pd.date_range('2016-01-01','2016-01-31'),columns=['DATE'])
#Refer to https://docs.python.org/2/library/time.html for detailed list of format specifiers
cal['DAY'] = cal.DATE.dt.strftime('%a')
cal['day'] = cal.DATE.dt.dayofweek
cal['WEEK'] = cal.DATE.dt.week
cal['DT'] = cal.DATE.dt.day
cal.loc[cal['WEEK'] > 52, 'WEEK'] = 0
cal.set_index('WEEK',inplace=True)
calg = cal.groupby(['day','DAY']).apply(lambda x: x.DT)
calg.unstack(level=[0,1])

Output

day    0   1   2   3   4   5   6
DAY  Mon Tue Wed Thu Fri Sat Sun
WEEK
0    NaN NaN NaN NaN   1   2   3
1      4   5   6   7   8   9  10
2     11  12  13  14  15  16  17
3     18  19  20  21  22  23  24
4     25  26  27  28  29  30  31

pandas: Create a 'Weekday' DataFrame column by converting date (mm/dd/yy) to weekday

3 Answers3