Extracting day of week and hour in Pandas - Mini Project: Build & Apply

Extracting Day of Week and Hour from Timestamps

📖 Scenario: You work at a delivery company. You have a list of delivery times. You want to find out which day of the week and hour of the day most deliveries happen.

🎯 Goal: Create a pandas DataFrame with delivery timestamps. Then extract the day of the week and hour from these timestamps. Finally, display the new data.

📋 What You'll Learn

Create a pandas DataFrame called df with a column 'delivery_time' containing the exact timestamps given.

Create a variable called time_column and set it to the string 'delivery_time'.

Use pandas datetime properties to create two new columns in df: 'day_of_week' and 'hour', extracted from time_column.

Print the updated DataFrame df.

💡 Why This Matters

🌍 Real World

Delivery companies analyze delivery times to optimize routes and staffing. Extracting day and hour helps find busy times.

💼 Career

Data analysts and scientists often work with timestamps to find patterns in time-based data.

1

Create the DataFrame with delivery timestamps

Create a pandas DataFrame called df with one column named 'delivery_time'. The column should contain these exact timestamps as strings: '2024-06-01 08:30:00', '2024-06-02 14:45:00', '2024-06-03 19:00:00', '2024-06-04 09:15:00', and '2024-06-05 22:10:00'.

Pandas

# Create the DataFrame df with the delivery_time column and the exact timestamps
# Your code here

Hint

Use pd.DataFrame and pass a dictionary with the key 'delivery_time' and the list of timestamps as values.

2

Set the time column variable

Create a variable called time_column and set it to the string 'delivery_time'.

Pandas

import pandas as pd

df = pd.DataFrame({
    'delivery_time': [
        '2024-06-01 08:30:00',
        '2024-06-02 14:45:00',
        '2024-06-03 19:00:00',
        '2024-06-04 09:15:00',
        '2024-06-05 22:10:00'
    ]
})

# Create the variable time_column and set it to 'delivery_time'
# Your code here

Hint

Just assign the string 'delivery_time' to the variable time_column.

3

Extract day of week and hour from timestamps

Convert the time_column in df to datetime type. Then create two new columns in df: 'day_of_week' which holds the day of the week as an integer (Monday=0), and 'hour' which holds the hour of the day as an integer.

Pandas

import pandas as pd

df = pd.DataFrame({
    'delivery_time': [
        '2024-06-01 08:30:00',
        '2024-06-02 14:45:00',
        '2024-06-03 19:00:00',
        '2024-06-04 09:15:00',
        '2024-06-05 22:10:00'
    ]
})

time_column = 'delivery_time'

# Convert df[time_column] to datetime and create 'day_of_week' and 'hour' columns
# Your code here

Hint

Use pd.to_datetime() to convert the column. Then use .dt.dayofweek and .dt.hour to get the new columns.

4

Print the updated DataFrame

Print the DataFrame df to see the new columns 'day_of_week' and 'hour'.

Pandas

import pandas as pd

df = pd.DataFrame({
    'delivery_time': [
        '2024-06-01 08:30:00',
        '2024-06-02 14:45:00',
        '2024-06-03 19:00:00',
        '2024-06-04 09:15:00',
        '2024-06-05 22:10:00'
    ]
})

time_column = 'delivery_time'

df[time_column] = pd.to_datetime(df[time_column])
df['day_of_week'] = df[time_column].dt.dayofweek
df['hour'] = df[time_column].dt.hour

# Print the DataFrame df
# Your code here

Hint

Use print(df) to show the DataFrame with the new columns.

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