Resampling with groupby for time data in Pandas - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Output of resampling with groupby on time data

What is the output of this code snippet that groups data by 'category' and resamples the time series to daily frequency, summing the values?

Pandas

import pandas as pd

data = {
    'category': ['A', 'A', 'B', 'B', 'A', 'B'],
    'value': [10, 20, 30, 40, 50, 60],
    'date': pd.to_datetime(['2024-01-01', '2024-01-02', '2024-01-01', '2024-01-03', '2024-01-03', '2024-01-02'])
}
df = pd.DataFrame(data)
df.set_index('date', inplace=True)
result = df.groupby('category').resample('D').sum()
print(result)

A

category  date      value
category date             
A        2024-01-01     10
         2024-01-02     20
         2024-01-03     50
B        2024-01-01     30
         2024-01-02     60
         2024-01-03     40

B

category  date      value
A         2024-01-01     10
          2024-01-02     20
          2024-01-03     0
B         2024-01-01     30
          2024-01-02     60
          2024-01-03     40

C

category  date      value
A         2024-01-01     10
          2024-01-02     20
          2024-01-03     50
B         2024-01-01     30
          2024-01-02      0
          2024-01-03     40

D

category  date      value
A         2024-01-01     10
          2024-01-02     20
          2024-01-03     50
B         2024-01-01     30
          2024-01-02     60
          2024-01-03      0

💡 Hint

Remember that resample fills missing dates with zeros only if you use .sum() after resampling on groups.

The groupby groups by 'category', then resample creates daily frequency for each group. The sum aggregates values on those dates. Missing dates for a group get zero sum, but since all dates exist for each group, sums are as expected.

❓ data_output

intermediate

Number of rows after groupby and resample

Given a DataFrame with 3 categories and dates spanning 4 days, what is the number of rows in the result after grouping by 'category' and resampling daily?

Pandas

import pandas as pd
import numpy as np

np.random.seed(0)
categories = ['X', 'Y', 'Z']
dates = pd.date_range('2024-01-01', periods=4)
data = {'category': np.repeat(categories, 4), 'value': np.random.randint(1, 10, 12), 'date': list(dates)*3}
df = pd.DataFrame(data).set_index('date')
result = df.groupby('category').resample('D').sum()

A 9

B 15

C 12

D 16

💡 Hint

Each category has 4 days, so total rows = categories * days.

There are 3 categories and 4 days each, so 3 * 4 = 12 rows after resampling.

🔧 Debug

advanced

Identify the error in resampling after groupby

What error does this code produce when trying to resample after grouping by 'category'?

Pandas

import pandas as pd

data = {'category': ['A', 'A'], 'value': [1, 2], 'date': ['2024-01-01', '2024-01-02']}
df = pd.DataFrame(data)
df.set_index('date', inplace=True)
df.groupby('category').resample('D').mean()

A AttributeError: 'DataFrameGroupBy' object has no attribute 'resample'

B KeyError: 'date'

C No error, returns mean values

D TypeError: Only valid with DatetimeIndex, TimedeltaIndex or PeriodIndex, but got an instance of 'Index'

💡 Hint

Check the index type after setting it.

The index is string type, not datetime. Resample requires a datetime-like index, so it raises a TypeError.

🚀 Application

advanced

Calculate weekly average sales per store

You have sales data with columns 'store', 'sales', and 'date'. How do you calculate the weekly average sales per store using groupby and resample?

Pandas

import pandas as pd

data = {'store': ['S1', 'S1', 'S2', 'S2', 'S1', 'S2'],
        'sales': [100, 150, 200, 250, 130, 300],
        'date': pd.to_datetime(['2024-01-01', '2024-01-08', '2024-01-01', '2024-01-08', '2024-01-15', '2024-01-15'])}
df = pd.DataFrame(data).set_index('date')

A df.groupby('store').resample('W').mean()

B df.resample('W').groupby('store').mean()

C df.groupby('store').resample('W').sum()

D df.groupby('store').resample('D').mean()

💡 Hint

Resample should be called after groupby to apply on each group separately.

Grouping by 'store' then resampling weekly ('W') and taking mean calculates weekly average sales per store.

🧠 Conceptual

expert

Why use groupby before resample for time series data?

Why is it important to use groupby before resample when working with time series data that has multiple categories?

A Because resample automatically groups data by all columns if groupby is not used.

B Because resample only works on the index, so grouping first allows resampling within each category's time series separately.

C Because resample can only be applied to columns, so groupby moves the index to columns.

D Because groupby changes the data type of the index to datetime, enabling resample to work.

💡 Hint

Think about how resample works on time indexes and how grouping affects that.

Resample works on the time index. Grouping first splits data by category, so resample applies separately to each category's time series.

Practice

1. What is the main purpose of using resample in pandas when working with time series data?

easy

A. To change the frequency of time-based data, like converting daily data to monthly data.

B. To sort data alphabetically by column values.

C. To merge two DataFrames based on a common key.

D. To filter rows based on a condition.

Solution

1. Step 1: Understand the function of resample

   Resample is used to change the time frequency of data, such as converting daily data into monthly or weekly data.

2. Step 2: Compare with other options

   Sorting, merging, and filtering are different pandas operations unrelated to resampling.

3. Final Answer:

   To change the frequency of time-based data, like converting daily data to monthly data. -> Option A

4. Quick Check:

   Resample changes time frequency = A [OK]

Hint: Resample changes time intervals, not sorting or filtering [OK]

Common Mistakes:

• Confusing resample with sort or filter
• Thinking resample merges data
• Using resample without datetime index

2. Which of the following is the correct way to resample a DataFrame df by month after grouping by a column 'category' and assuming 'date' is the datetime index?

easy

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

B. df.groupby('category').resample('M')

C. df.groupby('category').resample('M').mean()

D. df.resample('M').mean().groupby('category')

Solution

1. Step 1: Understand groupby and resample order

   To resample within each group, first group by the column, then resample the datetime index inside each group.

2. Step 2: Check syntax correctness

   df.groupby('category').resample('M').mean() correctly uses df.groupby('category').resample('M').mean(). Other options have wrong order or missing aggregation.

3. Final Answer:

   df.groupby('category').resample('M').mean() -> Option C

4. Quick Check:

   Group then resample = B [OK]

Hint: Group first, then resample on datetime index [OK]

Common Mistakes:

• Resampling before grouping
• Missing aggregation after resample
• Not setting datetime as index before resampling

3. Given the following code, what will be the output?

import pandas as pd

data = {'category': ['A', 'A', 'B', 'B'],
        'date': pd.to_datetime(['2023-01-01', '2023-01-15', '2023-01-01', '2023-01-20']),
        'value': [10, 20, 30, 40]}
df = pd.DataFrame(data)
df = df.set_index('date')
result = df.groupby('category').resample('M').sum()
print(result)

medium

A. An error because resample cannot be used after groupby.

B. A DataFrame with daily sums of 'value' for each category.

C. A DataFrame grouped by category only, ignoring dates.

D. A DataFrame with monthly sums of 'value' for each category, indexed by category and month end date.

Solution

1. Step 1: Understand the code flow

   The DataFrame is grouped by 'category', then resampled monthly on the datetime index, summing 'value' within each month and category.

2. Step 2: Analyze the output structure

   The result will have a MultiIndex with 'category' and the resampled month-end dates, showing sums per group per month.

3. Final Answer:

   A DataFrame with monthly sums of 'value' for each category, indexed by category and month end date. -> Option D

4. Quick Check:

   Group + monthly resample sum = C [OK]

Hint: Group then resample sums per month per group [OK]

Common Mistakes:

• Expecting daily sums instead of monthly
• Thinking resample causes error after groupby
• Ignoring the datetime index requirement

4. You run this code but get an error:

df.groupby('category').resample('W').mean()

What is the most likely cause of the error?

medium

A. The 'category' column contains null values.

B. The 'date' column is not set as the DataFrame index before resampling.

C. The resample frequency 'W' is invalid.

D. You cannot use mean() after resample.

Solution

1. Step 1: Check resample requirements

   Resample requires a datetime index. If 'date' is a column but not index, resample fails.

2. Step 2: Analyze error cause

   Most common error is forgetting to set 'date' as index before resampling.

3. Final Answer:

   The 'date' column is not set as the DataFrame index before resampling. -> Option B

4. Quick Check:

   Datetime index missing causes resample error = A [OK]

Hint: Set datetime column as index before resample [OK]

Common Mistakes:

• Not setting datetime index
• Assuming resample frequency is wrong
• Thinking aggregation like mean() is invalid

5. You have a DataFrame df with columns 'store', 'date' (datetime), and 'sales'. You want to find the total weekly sales per store, but only for weeks where the total sales exceed 1000. Which code snippet correctly does this?

hard

A. df.set_index('date').groupby('store').resample('W').sum().query('sales > 1000')

B. df.groupby('store').resample('W').sum().query('sales > 1000')

C. df.groupby('store').resample('W').sum().filter(lambda x: x['sales'] > 1000)

D. df.set_index('date').resample('W').groupby('store').sum().loc[lambda x: x['sales'] > 1000]

Solution

1. Step 1: Set datetime index before resampling

   Resample requires the datetime column as index, so df.set_index('date') is needed.

2. Step 2: Group by 'store' then resample weekly and sum sales

   Use groupby('store').resample('W').sum() to get weekly sales per store.

3. Step 3: Filter weeks where sales exceed 1000

   Use query('sales > 1000') to keep only those weeks.

4. Final Answer:

   df.set_index('date').groupby('store').resample('W').sum().query('sales > 1000') -> Option A

5. Quick Check:

   Set index, group, resample, filter sales > 1000 = D [OK]

Hint: Set datetime index, group, resample, then filter [OK]

Common Mistakes:

• Not setting datetime as index
• Wrong order of groupby and resample
• Using filter incorrectly instead of query