Rolling mean and sum in Pandas - Mini Project: Build & Apply

Rolling mean and sum

📖 Scenario: You work as a data analyst for a small store. You have daily sales data for a week. Your manager wants to see the average and total sales over the last 3 days for each day to understand trends better.

🎯 Goal: Build a program that calculates the rolling mean and rolling sum of sales over a 3-day window using pandas.

📋 What You'll Learn

Create a pandas DataFrame with exact sales data for 7 days.

Set a window size variable for rolling calculations.

Calculate rolling mean and rolling sum using pandas.

Print the resulting DataFrame showing original sales, rolling mean, and rolling sum.

💡 Why This Matters

🌍 Real World

Rolling averages and sums help smooth out daily fluctuations in sales data, making it easier to spot trends and patterns over time.

💼 Career

Data analysts and scientists often use rolling calculations to analyze time series data in finance, sales, weather, and many other fields.

1

Create the sales DataFrame

Create a pandas DataFrame called sales_data with a column 'sales' containing these exact values: [10, 20, 15, 30, 25, 40, 35].

Pandas

import pandas as pd
# Create the sales_data DataFrame with the given sales values
# Your code here

Hint

Use pd.DataFrame and pass a dictionary with key 'sales' and the list of values.

2

Set the rolling window size

Create a variable called window_size and set it to 3 to represent the 3-day rolling window.

Pandas

import pandas as pd
sales_data = pd.DataFrame({'sales': [10, 20, 15, 30, 25, 40, 35]})
# Set the window_size variable to 3
# Your code here

Hint

Just assign the number 3 to the variable window_size.

3

Calculate rolling mean and sum

Use the rolling method on sales_data['sales'] with the window size window_size to create two new columns: 'rolling_mean' and 'rolling_sum' that store the rolling mean and rolling sum respectively.

Pandas

import pandas as pd
sales_data = pd.DataFrame({'sales': [10, 20, 15, 30, 25, 40, 35]})
window_size = 3
# Calculate rolling mean and rolling sum and add as new columns
# Your code here

Hint

Use .rolling(window=window_size).mean() and .rolling(window=window_size).sum() on the sales column.

4

Print the final DataFrame

Print the sales_data DataFrame to display the original sales, rolling mean, and rolling sum columns.

Pandas

import pandas as pd
sales_data = pd.DataFrame({'sales': [10, 20, 15, 30, 25, 40, 35]})
window_size = 3
sales_data['rolling_mean'] = sales_data['sales'].rolling(window=window_size).mean()
sales_data['rolling_sum'] = sales_data['sales'].rolling(window=window_size).sum()
# Print the sales_data DataFrame
# Your code here

Hint

Use print(sales_data) to show the DataFrame.

Practice

1. What does the rolling(window=3).mean() function do on a pandas Series?

easy

A. Calculates the average of the current and previous 2 values for each position.

B. Calculates the sum of all values in the Series.

C. Calculates the difference between consecutive values.

D. Sorts the Series in ascending order.

Solution

1. Step 1: Understand rolling window size

   The window=3 means we look at 3 values at a time: the current and previous 2.

2. Step 2: Apply mean function on each window

   For each position, the mean is calculated over these 3 values, smoothing the data.

3. Final Answer:

   Calculates the average of the current and previous 2 values for each position. -> Option A

4. Quick Check:

   Rolling mean = average over window [OK]

Hint: Rolling mean averages over the window size including current [OK]

Common Mistakes:

• Thinking it sums all values, not just window
• Confusing mean with difference
• Assuming it sorts data

2. Which of the following is the correct syntax to calculate a rolling sum with a window size of 4 on a pandas DataFrame column named 'sales'?

easy

A. rolling(df['sales'], window=4).sum()

B. df.rolling('sales', 4).sum()

C. df['sales'].rolling(4).sum()

D. df['sales'].sum().rolling(4)

Solution

1. Step 1: Identify correct method chaining

   In pandas, rolling is called on the Series, then sum() is applied.

2. Step 2: Check syntax correctness

   df['sales'].rolling(4).sum() correctly uses df['sales'].rolling(4).sum() which is valid syntax.

3. Final Answer:

   df['sales'].rolling(4).sum() -> Option C

4. Quick Check:

   Correct rolling sum syntax = df['sales'].rolling(4).sum() [OK]

Hint: Use Series.rolling(window).sum() for rolling sums [OK]

Common Mistakes:

• Passing column name as string to rolling()
• Calling sum() before rolling()
• Using undefined rolling() function

3. Given the pandas Series data = pd.Series([2, 4, 6, 8, 10]), what is the output of data.rolling(window=3).sum()?

medium

A. [2, 6, 12, 18, 24]

B. [NaN, 6, 12, 18, 24]

C. [2, 4, 6, 8, 10]

D. [NaN, NaN, 12, 18, 24]

Solution

1. Step 1: Understand rolling sum with window=3

   For the first two positions, there are not enough values to fill the window, so result is NaN.

2. Step 2: Calculate sums for positions 3 to 5

   Position 3 sum: 2+4+6=12, position 4 sum: 4+6+8=18, position 5 sum: 6+8+10=24.

3. Final Answer:

   [NaN, NaN, 12, 18, 24] -> Option D

4. Quick Check:

   Rolling sum with window=3 pads start with NaN [OK]

Hint: Rolling sum outputs NaN until window fills [OK]

Common Mistakes:

• Expecting sums from first element
• Confusing rolling sum with cumulative sum
• Ignoring NaN for incomplete windows

4. What is wrong with this code snippet to calculate rolling mean on column 'temp' with window 2?

df = pd.DataFrame({'temp': [10, 20, 30]})
df['rolling_mean'] = df.rolling(2).mean()['temp']

medium

A. The code is correct and will run without errors.

B. The window size must be 3 or more.

C. mean() cannot be used after rolling().

D. The rolling() should be called on the column, not the whole DataFrame.

Solution

1. Step 1: Analyze the code execution

   df.rolling(2).mean() applies the rolling mean to the 'temp' column, producing a DataFrame with values [NaN, 15.0, 25.0].

2. Step 2: Column access and assignment

   df.rolling(2).mean()['temp'] extracts the Series correctly, and assignment to df['rolling_mean'] succeeds without errors.

3. Final Answer:

   The code is correct and will run without errors. -> Option A

4. Quick Check:

   df.rolling(2).mean()['temp'] works for column rolling mean [OK]

Hint: df.rolling(window).mean()['col'] is valid for single-column rolling [OK]

Common Mistakes:

• Believing rolling() must only be called on Series
• Thinking window size must be 3 or more
• Assuming mean() cannot be used after rolling()

5. You have daily sales data in a pandas DataFrame df with column 'sales'. You want to create a new column 'weekly_avg' that shows the average sales over the last 7 days, but only if at least 5 days of data are available in the window. Which code correctly achieves this?

hard

A. df['weekly_avg'] = df['sales'].rolling(7).mean(min_periods=5)

B. df['weekly_avg'] = df['sales'].rolling(window=7, min_periods=5).mean()

C. df['weekly_avg'] = df['sales'].rolling(window=5).mean()

D. df['weekly_avg'] = df['sales'].rolling(window=7).sum() / 5

Solution

1. Step 1: Understand rolling with min_periods

   min_periods=5 means the rolling function will compute mean only if at least 5 values exist in the window.

2. Step 2: Check correct syntax for rolling mean with conditions

   df['weekly_avg'] = df['sales'].rolling(window=7, min_periods=5).mean() correctly uses rolling(window=7, min_periods=5).mean() to get average over 7 days with minimum 5 days data.

3. Final Answer:

   df['weekly_avg'] = df['sales'].rolling(window=7, min_periods=5).mean() -> Option B

4. Quick Check:

   Use min_periods to require minimum data in rolling [OK]

Hint: Use min_periods in rolling() to set minimum data points [OK]

Common Mistakes:

• Passing min_periods to mean() instead of rolling()
• Using wrong window size
• Dividing sum by fixed number ignoring data count