Extracting day of week and hour in Pandas - Step-by-Step Execution

Concept Flow - Extracting day of week and hour

Start with datetime column

↓

Extract day of week

↓

Extract hour

↓

Add new columns to DataFrame

↓

Use extracted info for analysis

We start with a datetime column, extract the day of week and hour, then add these as new columns for easier analysis.

Execution Sample

Pandas

import pandas as pd

df = pd.DataFrame({'date': pd.to_datetime(['2024-06-01 08:30', '2024-06-02 15:45'])})
df['day_of_week'] = df['date'].dt.day_name()
df['hour'] = df['date'].dt.hour
print(df)

This code creates a DataFrame with datetime values, extracts the day name and hour, and adds them as new columns.

Execution Table

Step	Action	Input	Output
1	Create DataFrame with datetime	['2024-06-01 08:30', '2024-06-02 15:45']	DataFrame with 'date' column as datetime
2	Extract day of week	df['date']	['Saturday', 'Sunday']
3	Add 'day_of_week' column	['Saturday', 'Sunday']	DataFrame with 'day_of_week' column
4	Extract hour	df['date']	[8, 15]
5	Add 'hour' column	[8, 15]	DataFrame with 'hour' column
6	Print final DataFrame	DataFrame with all columns	date day_of_week hour 2024-06-01 08:30:00 Saturday 8 2024-06-02 15:45:00 Sunday 15

💡 All datetime values processed, new columns added successfully.

Variable Tracker

Variable	Start	After Step 1	After Step 3	After Step 5	Final
df	empty	date column with datetime	added day_of_week column	added hour column	final DataFrame with date, day_of_week, hour

Key Moments - 2 Insights

Why do we use dt.day_name() instead of just day?

dt.day_name() gives the weekday name like 'Monday', 'Tuesday' which is easier to read, as shown in execution_table step 2 and 3.

What type of data is in the 'hour' column?

The 'hour' column contains integers representing the hour part of the datetime, as seen in execution_table step 4 and 5.

Visual Quiz - 3 Questions

Look at the execution table, what is the output of extracting day of week at step 2?

A [8, 15]

B ['Monday', 'Tuesday']

C ['Saturday', 'Sunday']

D ['2024-06-01', '2024-06-02']

💡 Hint

Check the 'Output' column in execution_table row for step 2.

At which step is the 'hour' column added to the DataFrame?

A Step 3

B Step 5

C Step 4

D Step 6

💡 Hint

Look for the action 'Add hour column' in execution_table.

If the datetime was '2024-06-01 23:59', what would the 'hour' value be?

A 23

B 59

C 0

D 12

💡 Hint

Refer to how hour is extracted in execution_table step 4.

Concept Snapshot

Use pandas dt accessor to extract parts of datetime:
- day_of_week = df['date'].dt.day_name() gives weekday name
- hour = df['date'].dt.hour gives hour as integer
Add these as new columns for easy analysis.

Full Transcript

We start with a DataFrame containing datetime values. Using pandas dt accessor, we extract the day of week as a name string and the hour as an integer. These are added as new columns to the DataFrame. This helps us analyze data by weekday or hour easily. The execution table shows each step: creating the DataFrame, extracting day names, adding the column, extracting hours, adding that column, and printing the final DataFrame. Variables change as columns are added. Common confusions include why day_name() is used and what data type hour is. The quiz tests understanding of these steps and outputs.

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