Resampling time series data in Pandas - Mini Project: Build & Apply

Resampling Time Series Data

📖 Scenario: You work as a data analyst for a weather station. You have collected temperature data every hour for a few days. Now, you want to analyze the data by looking at daily average temperatures instead of hourly values.

🎯 Goal: Learn how to resample hourly time series data to daily averages using pandas.

📋 What You'll Learn

Create a pandas DataFrame with hourly temperature data and datetime index

Create a variable for the resampling frequency

Use pandas resample method to calculate daily average temperatures

Print the resulting daily average temperature DataFrame

💡 Why This Matters

🌍 Real World

Weather stations and many industries collect data frequently. Resampling helps summarize and analyze data at different time scales.

💼 Career

Data analysts and scientists often resample time series data to find trends and patterns over days, weeks, or months.

1

Create hourly temperature data

Create a pandas DataFrame called df with a datetime index from '2024-01-01 00:00' to '2024-01-03 23:00' hourly. Add a column 'temperature' with these exact values: [22, 21, 20, 19, 18, 17, 16, 15, 14, 13, 12, 11, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1, 0, -1, 22, 21, 20, 19, 18, 17, 16, 15, 14, 13, 12, 11, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1, 0, -1].

Pandas

import pandas as pd
# Create the DataFrame df with datetime index and temperature column
# Your code here

Hint

Use pd.date_range to create the datetime index with hourly frequency. Then create the DataFrame with the temperature column.

2

Set resampling frequency

Create a variable called freq and set it to the string 'D' to represent daily frequency for resampling.

Pandas

import pandas as pd

dates = pd.date_range(start='2024-01-01 00:00', end='2024-01-03 23:00', freq='H')
temperatures = [22, 21, 20, 19, 18, 17, 16, 15, 14, 13, 12, 11, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1, 0, -1, 22, 21, 20, 19, 18, 17, 16, 15, 14, 13, 12, 11, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1, 0, -1]
df = pd.DataFrame({'temperature': temperatures}, index=dates)
# Create a variable freq and set it to 'D'
# Your code here

Hint

Set freq to the string 'D' which means daily frequency in pandas.

3

Resample to daily average temperature

Create a new DataFrame called daily_avg by resampling df using the freq variable and calculating the mean temperature for each day.

Pandas

import pandas as pd

dates = pd.date_range(start='2024-01-01 00:00', end='2024-01-03 23:00', freq='H')
temperatures = [22, 21, 20, 19, 18, 17, 16, 15, 14, 13, 12, 11, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1, 0, -1, 22, 21, 20, 19, 18, 17, 16, 15, 14, 13, 12, 11, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1, 0, -1]
df = pd.DataFrame({'temperature': temperatures}, index=dates)
freq = 'D'
# Resample df using freq and calculate mean temperature
# Your code here

Hint

Use df.resample(freq).mean() to get daily average temperatures.

4

Print daily average temperatures

Print the daily_avg DataFrame to display the daily average temperatures.

Pandas

import pandas as pd

dates = pd.date_range(start='2024-01-01 00:00', end='2024-01-03 23:00', freq='H')
temperatures = [22, 21, 20, 19, 18, 17, 16, 15, 14, 13, 12, 11, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1, 0, -1, 22, 21, 20, 19, 18, 17, 16, 15, 14, 13, 12, 11, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1, 0, -1]
df = pd.DataFrame({'temperature': temperatures}, index=dates)
freq = 'D'
daily_avg = df.resample(freq).mean()
# Print daily_avg
# Your code here

Hint

Use print(daily_avg) to show the daily average temperatures.

Practice

1. What does resampling a time series in pandas usually do?

easy

A. Sort the data by values in ascending order

B. Remove all missing values from the dataset

C. Change the time frequency of the data to summarize or expand it

D. Convert the data into a different file format

Solution

1. Step 1: Understand the purpose of resampling

   Resampling changes how often data points appear in time, either by grouping or expanding.

2. Step 2: Identify what resampling affects

   It changes the time frequency, not sorting or file formats.

3. Final Answer:

   Change the time frequency of the data to summarize or expand it -> Option C

4. Quick Check:

   Resampling = Change frequency [OK]

Hint: Resampling changes how often data points appear in time [OK]

Common Mistakes:

• Confusing resampling with sorting data
• Thinking resampling removes missing values automatically
• Believing resampling changes file formats

2. Which of the following is the correct syntax to downsample a pandas DataFrame df with a datetime index to monthly frequency and calculate the mean?

easy

A. df.resample('M').mean()

B. df.resample('M').sum()

C. df.resample('D').mean()

D. df.resample('M').fillna()

Solution

1. Step 1: Identify the correct resample frequency for monthly data

   'M' stands for month-end frequency in pandas resampling.

2. Step 2: Choose the correct aggregation function

   To calculate the mean after downsampling, use .mean().

3. Final Answer:

   df.resample('M').mean() -> Option A

4. Quick Check:

   Monthly mean = df.resample('M').mean() [OK]

Hint: Use 'M' for monthly and .mean() to aggregate [OK]

Common Mistakes:

• Using 'D' instead of 'M' for monthly frequency
• Using sum() when mean() is asked
• Calling fillna() without upsampling

3. Given the following code, what will be the output of print(resampled)?

import pandas as pd
idx = pd.date_range('2023-01-01', periods=4, freq='D')
data = [10, 20, 30, 40]
df = pd.DataFrame({'value': data}, index=idx)
resampled = df.resample('2D').sum()

medium

A. value
2023-01-01 30
2023-01-03 70

B. value
2023-01-01 10
2023-01-02 20
2023-01-03 30
2023-01-04 40

C. value
2023-01-01 15
2023-01-03 35

D. {'2023-01-01': 30, '2023-01-03': 70}

Solution

1. Step 1: Understand resampling with '2D' and sum aggregation

   Resampling with '2D' groups data every 2 days and sums values in each group.

2. Step 2: Calculate sums for each 2-day period

   First period: 2023-01-01 + 2023-01-02 = 10 + 20 = 30; Second period: 2023-01-03 + 2023-01-04 = 30 + 40 = 70.

3. Final Answer:

   value 2023-01-01 30 2023-01-03 70 -> Option A

4. Quick Check:

   Sum every 2 days = 30, 70 [OK]

Hint: Sum values in 2-day groups after resampling [OK]

Common Mistakes:

• Confusing sum with mean aggregation
• Expecting original daily data instead of grouped
• Misreading the index labels after resampling

4. What is wrong with this code snippet for upsampling daily data to hourly data?

df = pd.DataFrame({'value': [1, 2]}, index=pd.date_range('2023-01-01', periods=2, freq='D'))
upsampled = df.resample('H').mean()

medium

A. The frequency string 'H' is invalid for resampling

B. Upsampling without filling missing values results in NaNs

C. Using mean() is not allowed after resampling

D. The DataFrame index must be integers, not datetime

Solution

1. Step 1: Identify the effect of upsampling daily to hourly

   Upsampling creates new rows for each hour, but original data has only daily points.

2. Step 2: Recognize missing values after upsampling

   New hourly rows have no data, so mean() returns NaN unless filled.

3. Final Answer:

   Upsampling without filling missing values results in NaNs -> Option B

4. Quick Check:

   Upsampling needs fillna() to avoid NaNs [OK]

Hint: Upsampling creates gaps; fill missing values to avoid NaNs [OK]

Common Mistakes:

• Thinking 'H' frequency is invalid
• Believing mean() cannot be used after resampling
• Ignoring datetime index requirement

5. You have minute-level temperature data in a DataFrame df with a datetime index. You want to create a new DataFrame with hourly average temperatures but only for hours where at least 45 minutes of data exist. Which approach correctly achieves this?

hard

A. df.resample('H').mean().dropna()

B. df.resample('H').mean()

C. df.resample('H').sum().where(lambda x: x >= 45).dropna()

D. df.resample('H').apply(lambda x: x.mean() if len(x) >= 45 else None).dropna()

Solution

1. Step 1: Understand the requirement for minimum data points per hour

   We want hourly averages only if 45 or more minutes of data exist (i.e., at least 45 rows per hour).

2. Step 2: Use a custom function with resample and apply

   Using apply with a lambda checks the length of each group and calculates mean only if condition met; else returns None.

3. Step 3: Remove hours with insufficient data

   Calling dropna() removes hours where the condition failed (null values).

4. Final Answer:

   df.resample('H').apply(lambda x: x.mean() if len(x) >= 45 else None).dropna() -> Option D

5. Quick Check:

   Custom apply filters groups by size before averaging [OK]

Hint: Use apply with condition on group size, then dropna() [OK]

Common Mistakes:

• Using mean() without checking group size
• Using sum() instead of mean() for temperature
• Dropping NaNs without filtering groups first