Resampling time series data in Pandas - Deep Dive

Overview - Resampling time series data

What is it?

Resampling time series data means changing the frequency of your data points. You can make data points less frequent (downsampling) or more frequent (upsampling). This helps to analyze trends over different time scales or fill in missing data. It is done by grouping data by time intervals and applying calculations like sums or averages.

Why it matters

Without resampling, it is hard to compare data recorded at different time intervals or to see patterns over longer or shorter periods. For example, daily sales data might be noisy, but weekly totals show clearer trends. Resampling helps clean, summarize, and prepare time data for better decisions and predictions.

Where it fits

Before learning resampling, you should understand basic time series data and how to work with dates and times in pandas. After mastering resampling, you can explore time series forecasting, rolling windows, and advanced time-based feature engineering.

Mental Model

Core Idea

Resampling groups time series data into new time intervals and summarizes or fills data to change its frequency.

Think of it like...

Imagine you have a photo album with pictures taken every day. Resampling is like making a new album with one picture per week by choosing the best photo or combining them, or making a new album with hourly snapshots by guessing what happened between pictures.

Time Series Data
┌─────────────┬─────────────┬─────────────┐
│ 2024-01-01 │ 2024-01-02 │ 2024-01-03 │ ... Original daily data
├─────────────┼─────────────┼─────────────┤
│     10      │     15      │     20      │
└─────────────┴─────────────┴─────────────┘

Resample to Weekly
┌─────────────┬─────────────┐
│ 2024-01-01 │ 2024-01-08 │ ... Weekly sums or averages
├─────────────┼─────────────┤
│     45      │     ...     │
└─────────────┴─────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding time series basics

Concept: Learn what time series data is and how pandas stores dates and times.

Time series data is a sequence of data points recorded at specific times. In pandas, dates and times are stored using the DateTimeIndex or datetime columns. This allows pandas to understand the order and spacing of data points.

Result

You can load data with dates and pandas recognizes the time order.

Understanding how pandas handles dates is essential because resampling depends on knowing the time intervals between data points.

2

Foundation

Basic pandas grouping by time

Concept: Learn how to group data by time intervals using pandas.

You can group data by time periods using pandas' Grouper or by resampling. For example, grouping daily data by month sums all days in each month. This is the first step to changing data frequency.

Result

Data grouped by month with sums or averages.

Grouping by time intervals is the foundation of resampling, letting you summarize data over new periods.

3

Intermediate

Downsampling with aggregation

🤔Before reading on: do you think downsampling keeps all original data points or reduces them? Commit to your answer.

Concept: Downsampling reduces data frequency by combining multiple data points into one using functions like sum or mean.

Using pandas' resample method with a lower frequency (e.g., 'W' for weekly), you can aggregate daily data into weekly sums or averages. For example, daily sales summed into weekly totals.

Result

A smaller dataset with one value per week representing combined daily data.

Knowing downsampling helps you simplify data and see bigger trends by reducing noise and volume.

4

Intermediate

Upsampling and filling missing data

🤔Before reading on: when upsampling, do you think pandas automatically fills new data points or leaves gaps? Commit to your answer.

Concept: Upsampling increases data frequency by creating new time points and filling missing values using methods like forward fill or interpolation.

When you resample to a higher frequency (e.g., from daily to hourly), pandas creates new rows for missing times. You can fill these gaps by copying the last known value (forward fill) or estimating values (interpolation).

Result

A larger dataset with more frequent time points and no missing values after filling.

Understanding upsampling and filling is key to preparing data for models that need consistent time intervals.

5

Intermediate

Custom aggregation functions

Concept: You can use your own functions to summarize data during resampling.

Instead of just sum or mean, pandas lets you pass any function to resample's agg method. For example, you can calculate the median, max, or a custom calculation for each time group.

Result

Resampled data summarized by your chosen function.

Custom aggregation lets you tailor resampling to your specific analysis needs.

6

Advanced

Handling irregular time series

🤔Before reading on: do you think resampling works the same on irregular time series as on regular ones? Commit to your answer.

Concept: Resampling can handle data with irregular time gaps but requires careful filling and aggregation choices.

If your data points are not evenly spaced, resampling still groups by fixed intervals but may create many missing points. You must choose how to fill or aggregate carefully to avoid misleading results.

Result

Resampled data with consistent intervals, possibly with filled or NaN values where data was missing.

Knowing how irregular data affects resampling prevents errors and misinterpretation in real-world messy datasets.

7

Expert

Performance and memory considerations

🤔Before reading on: do you think resampling large datasets is always fast and memory efficient? Commit to your answer.

Concept: Resampling large time series can be slow and memory-heavy; understanding pandas internals and chunking helps optimize performance.

Pandas resample creates new indexes and copies data, which can be costly for big data. Using categorical indexes, downcasting data types, or processing data in chunks can improve speed and reduce memory use.

Result

Faster resampling with less memory use on large datasets.

Knowing performance tricks is crucial for applying resampling in production or big data scenarios.

Under the Hood

Pandas resampling works by creating a new time index at the target frequency. It then groups original data points that fall into each new time bin. For downsampling, it applies aggregation functions to combine grouped points. For upsampling, it inserts new time points and fills missing values using specified methods. Internally, pandas uses efficient Cython code to handle grouping and aggregation.

Why designed this way?

Resampling was designed to handle the common need to analyze time series at different scales. Grouping by fixed time bins is intuitive and flexible. Aggregation functions allow summarizing data meaningfully. Filling methods for upsampling address the problem of missing data in higher frequency views. Alternatives like manual looping would be slower and error-prone.

Original Data (daily) ──▶ Group by Time Bins ──▶ Aggregate or Fill ──▶ Resampled Data

┌─────────────┐     ┌─────────────┐     ┌─────────────┐     ┌─────────────┐
│ 2024-01-01 │     │ 2024-01-01  │     │ Aggregate  │     │ 2024-01-01  │
│ 2024-01-02 │ ──▶ │ to 2024-01-07│ ──▶ │ or Fill   │ ──▶ │ to 2024-01-07│
│ 2024-01-03 │     │ (weekly bin) │     │ Function   │     │ (weekly data)│
└─────────────┘     └─────────────┘     └─────────────┘     └─────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does resampling always keep the original number of data points? Commit to yes or no.

Common Belief: Resampling keeps all original data points but just changes their labels.

Reality: Downsampling reduces the number of data points by grouping multiple points into one. Upsampling increases points but adds new ones with missing or filled values.

Why it matters: Expecting the same number of points can cause confusion and errors in analysis or plotting.

Quick: When upsampling, does pandas automatically fill missing values? Commit to yes or no.

Common Belief: Upsampling automatically fills all new time points with meaningful data.

Reality: Upsampling creates new time points but leaves them empty (NaN) unless you explicitly fill them using methods like forward fill or interpolation.

Why it matters: Not filling missing data can lead to wrong calculations or errors in downstream processing.

Quick: Is resampling only useful for regular time series? Commit to yes or no.

Common Belief: Resampling only works well if data points are evenly spaced in time.

Reality: Resampling can be applied to irregular time series but requires careful handling of missing data and aggregation choices.

Why it matters: Ignoring irregularity can cause misleading summaries or excessive missing values.

Quick: Does resampling change the original data values? Commit to yes or no.

Common Belief: Resampling changes the original data values directly.

Reality: Resampling creates a new dataset with aggregated or filled values but does not modify the original data unless explicitly assigned.

Why it matters: Understanding this prevents accidental data loss or confusion about data integrity.

Expert Zone

1

Resampling with time zones requires careful alignment to avoid shifting data incorrectly.

2

Choosing the right aggregation function affects the meaning of resampled data; sum vs mean can tell very different stories.

3

Upsampling with interpolation methods can introduce artificial trends if not chosen carefully.

When NOT to use

Avoid resampling when your analysis depends on exact original timestamps or when data irregularity is meaningful. Instead, use time-aware models or irregular time series methods that do not require fixed intervals.

Production Patterns

In production, resampling is used to prepare data for machine learning models that require fixed time steps, to generate reports at business-relevant intervals, and to align multiple time series with different frequencies before merging.

Connections

Aggregation functions

Resampling builds on aggregation by applying it over time groups.

Understanding aggregation helps grasp how resampling summarizes data over new time intervals.

Interpolation in numerical analysis

Upsampling uses interpolation methods similar to those in numerical analysis to estimate missing values.

Knowing interpolation techniques improves how you fill missing time points during upsampling.

Signal processing

Resampling in time series is similar to changing sampling rates in signal processing.

Recognizing this connection helps understand the effects of downsampling and upsampling on data quality and information loss.

Common Pitfalls

#1 Not specifying an aggregation function when downsampling.

Wrong approach:

df.resample('W')

Correct approach:

df.resample('W').sum()

Root cause:

Pandas requires an aggregation method to combine multiple data points; omitting it causes errors or unexpected results.

#2 Upsampling without filling missing values.

Wrong approach:

df.resample('H').asfreq()

Correct approach:

df.resample('H').ffill()

Root cause:

Upsampling creates new time points with NaN values; not filling them leads to missing data in analysis.

#3 Applying resampling on non-datetime index.

Wrong approach:

df.resample('M').sum() # when index is not datetime

Correct approach:

df.set_index('date_column').resample('M').sum()

Root cause:

Resampling requires a datetime-like index to group data by time intervals.

Key Takeaways

Resampling changes the frequency of time series data by grouping or expanding time points.

Downsampling reduces data points by aggregating over larger time intervals, while upsampling increases points by inserting new times and filling missing values.

Choosing the right aggregation and filling methods is crucial for meaningful resampled data.

Resampling works best with a datetime index and requires careful handling of irregular data and missing values.

Understanding resampling prepares you to analyze time series data at different scales and prepare it for modeling.

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