Extracting day of week and hour in Pandas

Introduction

We extract the day of the week and hour from dates to understand when events happen. This helps us find patterns in time-based data.

You want to see which days of the week have the most sales.

You need to analyze what hours of the day users visit a website.

You want to group data by weekday or hour to find trends.

You are working with timestamps and want to simplify them to day or hour.

You want to create charts showing activity by day or hour.

Syntax

Pandas

df['day_of_week'] = df['date_column'].dt.dayofweek
# Monday=0, Sunday=6

df['hour'] = df['date_column'].dt.hour

Use .dt.dayofweek to get the day number (Monday=0 to Sunday=6).

Use .dt.hour to get the hour from a datetime column.

Examples

This adds a new column with numbers 0-6 for the day of the week.

Pandas

df['day_of_week'] = df['timestamp'].dt.dayofweek

This adds a new column with the hour (0-23) from the timestamp.

Pandas

df['hour'] = df['timestamp'].dt.hour

This adds a column with the full weekday name like 'Monday'.

Pandas

df['weekday_name'] = df['timestamp'].dt.day_name()

Sample Program

This code creates a small table with timestamps, converts them to datetime, then extracts the day of week and hour into new columns.

Pandas

import pandas as pd

data = {'timestamp': ['2024-06-01 08:30:00', '2024-06-02 15:45:00', '2024-06-03 23:10:00']}
df = pd.DataFrame(data)
df['timestamp'] = pd.to_datetime(df['timestamp'])
df['day_of_week'] = df['timestamp'].dt.dayofweek
df['hour'] = df['timestamp'].dt.hour
print(df)

Important Notes

Remember Monday is 0 and Sunday is 6 when using dayofweek.

You can also get the weekday name with dt.day_name() if you want words instead of numbers.

Make sure your column is in datetime format before extracting day or hour.

Summary

Extract day of week using .dt.dayofweek for numbers 0-6.

Extract hour using .dt.hour for hours 0-23.

Convert your column to datetime first with pd.to_datetime().

Practice

1. What does the .dt.dayofweek attribute return when applied to a pandas datetime column?

easy

A. An integer from 0 (Monday) to 6 (Sunday) representing the day of the week

B. The name of the day as a string like 'Monday' or 'Tuesday'

C. The hour of the day from 0 to 23

D. The month number from 1 to 12

Solution

1. Step 1: Understand the .dt accessor in pandas

   The .dt accessor allows extraction of datetime properties from a pandas datetime column.

2. Step 2: Identify what .dt.dayofweek returns

   .dt.dayofweek returns an integer where Monday is 0 and Sunday is 6, representing the day of the week.

3. Final Answer:

   An integer from 0 (Monday) to 6 (Sunday) representing the day of the week -> Option A

4. Quick Check:

   .dt.dayofweek = 0 to 6 [OK]

Hint: Remember Monday starts at 0 for dayofweek [OK]

Common Mistakes:

• Thinking it returns day names instead of numbers
• Confusing dayofweek with day or dayofyear
• Expecting Sunday to be 0 instead of Monday

2. Which of the following is the correct way to extract the hour from a pandas datetime column named df['time']?

easy

A. df['time'].hour

B. df['time'].dt.hour()

C. df['time'].dt.hour

D. df['time'].dt['hour']

Solution

1. Step 1: Recall the pandas datetime accessor syntax

   To extract datetime components, use the .dt accessor followed by the attribute without parentheses.

2. Step 2: Identify the correct syntax for hour extraction

   The correct syntax is df['time'].dt.hour, which returns the hour as an integer from 0 to 23.

3. Final Answer:

   df['time'].dt.hour -> Option C

4. Quick Check:

   Use .dt.hour without parentheses [OK]

Hint: Use .dt.hour without parentheses to get hour [OK]

Common Mistakes:

• Using .hour directly without .dt
• Adding parentheses after .hour like a function
• Trying to access hour with dictionary syntax

3. Given the following code:

import pandas as pd

df = pd.DataFrame({
    'timestamp': ['2024-06-01 14:30:00', '2024-06-02 09:15:00']
})
df['timestamp'] = pd.to_datetime(df['timestamp'])
df['day'] = df['timestamp'].dt.dayofweek
df['hour'] = df['timestamp'].dt.hour
print(df[['day', 'hour']].to_dict())

What is the output?

medium

A. {'day': {0: 5, 1: 6}, 'hour': {0: 14, 1: 9}}

B. {'day': {0: 6, 1: 0}, 'hour': {0: 14, 1: 9}}

C. {'day': {0: 5, 1: 6}, 'hour': {0: 14, 1: 15}}

D. {'day': {0: 4, 1: 5}, 'hour': {0: 14, 1: 9}}

Solution

1. Step 1: Convert strings to datetime and extract dayofweek

   '2024-06-01' is a Saturday, which is day 5 (Monday=0). '2024-06-02' is Sunday, day 6.

2. Step 2: Extract hour from timestamps

   The hours are 14 and 9 respectively, matching the times given.

3. Final Answer:

   {'day': {0: 5, 1: 6}, 'hour': {0: 14, 1: 9}} -> Option A

4. Quick Check:

   Saturday=5, Sunday=6, hours 14 and 9 [OK]

Hint: Check weekday numbers: Monday=0, Saturday=5, Sunday=6 [OK]

Common Mistakes:

• Mixing up dayofweek numbering
• Incorrect hour extraction
• Not converting strings to datetime first

4. The following code throws an error. What is the main issue?

import pandas as pd

df = pd.DataFrame({'time': ['2024-06-01 14:30:00']})
df['hour'] = df['time'].hour

medium

A. The DataFrame is missing an index

B. Incorrect use of .hour instead of .dt.hour with parentheses

C. The 'time' column should be a list of integers, not strings

D. Missing conversion of 'time' column to datetime before accessing .hour

Solution

1. Step 1: Check the data type of 'time' column

   The 'time' column contains strings, not datetime objects, so .hour attribute is not available.

2. Step 2: Identify the fix

   Convert 'time' to datetime using pd.to_datetime() before accessing .dt.hour.

3. Final Answer:

   Missing conversion of 'time' column to datetime before accessing .hour -> Option D

4. Quick Check:

   Convert strings to datetime first [OK]

Hint: Always convert to datetime before .dt.hour [OK]

Common Mistakes:

• Trying to use .hour on string columns
• Using .hour without .dt accessor
• Ignoring data type conversion errors

5. You have a pandas DataFrame df with a column 'datetime' containing timestamps. You want to create a new column 'work_hour' that is True if the timestamp is on a weekday (Monday to Friday) and between 9 AM and 5 PM, otherwise False. Which code snippet correctly does this?

hard

A. df['work_hour'] = ((df['datetime'].dt.dayofweek >= 1) & (df['datetime'].dt.dayofweek <= 5) & (df['datetime'].dt.hour > 9) & (df['datetime'].dt.hour < 17))

B. df['work_hour'] = ((df['datetime'].dt.dayofweek >= 0) & (df['datetime'].dt.dayofweek <= 4) & (df['datetime'].dt.hour >= 9) & (df['datetime'].dt.hour < 17))

C. df['work_hour'] = (df['datetime'].dt.dayofweek > 0) & (df['datetime'].dt.dayofweek < 5) & (df['datetime'].dt.hour >= 9) & (df['datetime'].dt.hour <= 17)

D. df['work_hour'] = ((df['datetime'].dt.dayofweek >= 0) & (df['datetime'].dt.dayofweek <= 4) & (df['datetime'].dt.hour > 9) & (df['datetime'].dt.hour <= 17))

Solution

1. Step 1: Define weekdays correctly

   Weekdays are Monday (0) through Friday (4), so dayofweek should be between 0 and 4 inclusive.

2. Step 2: Define work hours correctly

   Work hours are from 9 AM (hour 9) up to but not including 5 PM (hour 17), so hour >= 9 and hour < 17.

3. Step 3: Combine conditions with & and parentheses

   Use & to combine all conditions and wrap each condition in parentheses for correct evaluation.

4. Final Answer:

   df['work_hour'] = ((df['datetime'].dt.dayofweek >= 0) & (df['datetime'].dt.dayofweek <= 4) & (df['datetime'].dt.hour >= 9) & (df['datetime'].dt.hour < 17)) -> Option B

5. Quick Check:

   Weekdays 0-4 and hours 9 to 16 inclusive [OK]

Hint: Use dayofweek 0-4 and hour >=9 and <17 for work hours [OK]

Common Mistakes:

• Using wrong dayofweek range (e.g., 1-5)
• Including 17 as work hour instead of less than 17
• Not using parentheses around conditions