Why window functions matter in Pandas - See It in Action

Why window functions matter

📖 Scenario: Imagine you work in a sales team. You have daily sales data for a store. You want to see how sales change day by day and understand trends better.

🎯 Goal: You will create a small sales dataset, set a window size, calculate the rolling average sales using a window function, and print the result. This helps smooth out daily ups and downs to see trends.

📋 What You'll Learn

Create a pandas DataFrame with exact sales data

Create a variable for the window size

Use pandas rolling window function to calculate the rolling average

Print the resulting DataFrame with the rolling average column

💡 Why This Matters

🌍 Real World

Rolling averages are used in sales, finance, and weather data to smooth out short-term fluctuations and highlight longer-term trends.

💼 Career

Data analysts and scientists use window functions like rolling averages to prepare data for reports and decision-making.

1

Create the sales data

Create a pandas DataFrame called sales_data with two columns: 'date' and 'sales'. Use these exact values for 'date': '2024-01-01', '2024-01-02', '2024-01-03', '2024-01-04', '2024-01-05'. Use these exact values for 'sales': 100, 120, 130, 90, 110. Convert the 'date' column to datetime type.

Pandas

# Import pandas and create the sales_data DataFrame with the exact dates and sales
# Your code here

Hint

Use pd.DataFrame to create the data and pd.to_datetime to convert the date column.

2

Set the window size

Create a variable called window_size and set it to 3. This will be the number of days to look back when calculating the rolling average.

Pandas

import pandas as pd

sales_data = pd.DataFrame({
    'date': ['2024-01-01', '2024-01-02', '2024-01-03', '2024-01-04', '2024-01-05'],
    'sales': [100, 120, 130, 90, 110]
})
sales_data['date'] = pd.to_datetime(sales_data['date'])

# Create window_size variable and set it to 3
# Your code here

Hint

Just create a variable named window_size and assign the number 3.

3

Calculate the rolling average sales

Create a new column in sales_data called 'rolling_avg'. Use the pandas rolling function on the 'sales' column with the window size window_size and calculate the mean for each window.

Pandas

import pandas as pd

sales_data = pd.DataFrame({
    'date': ['2024-01-01', '2024-01-02', '2024-01-03', '2024-01-04', '2024-01-05'],
    'sales': [100, 120, 130, 90, 110]
})
sales_data['date'] = pd.to_datetime(sales_data['date'])

window_size = 3

# Calculate rolling average and add it as 'rolling_avg' column
# Your code here

Hint

Use sales_data['sales'].rolling(window=window_size).mean() to get the rolling average.

4

Print the sales data with rolling average

Print the sales_data DataFrame to see the original sales and the new rolling average column.

Pandas

import pandas as pd

sales_data = pd.DataFrame({
    'date': ['2024-01-01', '2024-01-02', '2024-01-03', '2024-01-04', '2024-01-05'],
    'sales': [100, 120, 130, 90, 110]
})
sales_data['date'] = pd.to_datetime(sales_data['date'])

window_size = 3

sales_data['rolling_avg'] = sales_data['sales'].rolling(window=window_size).mean()

# Print the sales_data DataFrame
# Your code here

Hint

Use print(sales_data) to show the DataFrame with the rolling average.

Practice

1. What is the main benefit of using window functions in pandas?

easy

A. They allow calculations over a subset of rows without collapsing the data.

B. They delete rows that do not meet a condition.

C. They convert DataFrames into lists.

D. They only work on numeric columns.

Solution

1. Step 1: Understand window function purpose

   Window functions perform calculations across a set of rows related to the current row without reducing the number of rows.

2. Step 2: Compare options

   They allow calculations over a subset of rows without collapsing the data. correctly describes this benefit. Options A, B, and D describe unrelated or incorrect behaviors.

3. Final Answer:

   They allow calculations over a subset of rows without collapsing the data. -> Option A

4. Quick Check:

   Window functions keep all rows while calculating [OK]

Hint: Window functions keep all rows while calculating [OK]

Common Mistakes:

• Thinking window functions remove rows
• Confusing window functions with filtering
• Assuming window functions only work on numbers

2. Which of the following is the correct syntax to calculate a rolling mean over 3 rows in pandas?

easy

A. df.rolling['col'](3).mean()

B. df['col'].rolling(3).mean()

C. df['col'].mean(rolling=3)

D. df.rolling.mean(3)['col']

Solution

1. Step 1: Recall rolling mean syntax

   The correct syntax uses the rolling() method on the Series, then calls mean(): df['col'].rolling(3).mean()

2. Step 2: Check each option

   df['col'].rolling(3).mean() matches the correct syntax. Options B, C, and D misuse method calls or order.

3. Final Answer:

   df['col'].rolling(3).mean() -> Option B

4. Quick Check:

   rolling() then mean() is correct order [OK]

Hint: Use rolling() before mean() on the column [OK]

Common Mistakes:

• Using square brackets with rolling
• Calling mean() before rolling()
• Passing rolling window size inside mean()

3. Given the DataFrame:

import pandas as pd
df = pd.DataFrame({'value': [10, 20, 30, 40, 50]})
df['rolling_sum'] = df['value'].rolling(3).sum()
print(df)

What is the output of the rolling_sum column?

medium

A. [NaN, 30.0, 60.0, 90.0, 120.0]

B. [10, 30, 60, 90, 120]

C. [NaN, NaN, 60.0, 90.0, 120.0]

D. [10, 20, 30, 40, 50]

Solution

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

   Rolling sum with window 3 sums current and previous 2 rows. First two rows have less than 3 values, so result is NaN.

2. Step 2: Calculate sums

   Row 3: 10+20+30=60, Row 4: 20+30+40=90, Row 5: 30+40+50=120

3. Final Answer:

   [NaN, NaN, 60.0, 90.0, 120.0] -> Option C

4. Quick Check:

   Rolling sum skips incomplete windows [OK]

Hint: Rolling sums start producing values after full window [OK]

Common Mistakes:

• Expecting sums for first two rows
• Confusing rolling sum with cumulative sum
• Ignoring NaN for incomplete windows

4. Which of the following is true about this code snippet?

df['rank'] = df['score'].rolling(3).rank()

medium

A. The rolling window size must be larger than 3.

B. rank() requires a window size argument.

C. The code should use groupby before rolling.

D. rolling() supports rank() directly.

Solution

1. Step 1: Check rolling and rank compatibility

   The rolling() method returns a Rolling object that supports aggregation functions like sum(), mean(), rank(), etc.

2. Step 2: Identify correct statement

   rolling() supports rank() directly. The other options describe incorrect requirements or unrelated behaviors.

3. Final Answer:

   rolling() supports rank() directly. -> Option D

4. Quick Check:

   rank() is valid on rolling objects [OK]

Hint: rank() works directly on rolling objects [OK]

Common Mistakes:

• Thinking rank() is not supported directly on rolling
• Trying to pass window size to rank()
• Confusing rolling with groupby

5. You have daily sales data in a DataFrame and want to calculate a 7-day moving average of sales but keep all days in the result. Which pandas method should you use and why?

hard

A. Use rolling(7).mean() because it calculates moving averages without dropping rows.

B. Use groupby('day').mean() because it groups days and averages sales.

C. Use resample('7D').mean() because it resamples data into 7-day periods.

D. Use cumsum() because it gives cumulative sums over 7 days.

Solution

1. Step 1: Understand the goal

   You want a moving average over 7 days that keeps all original rows (days) in the DataFrame.

2. Step 2: Evaluate methods

   rolling(7).mean() calculates the moving average using a sliding window and keeps all rows. groupby() aggregates and reduces rows. resample() changes frequency and may drop days. cumsum() is cumulative sum, not average.

3. Final Answer:

   Use rolling(7).mean() because it calculates moving averages without dropping rows. -> Option A

4. Quick Check:

   rolling().mean() keeps all rows with moving average [OK]

Hint: rolling().mean() keeps all rows with moving average [OK]

Common Mistakes:

• Using groupby which reduces rows
• Using resample which changes data frequency
• Confusing cumulative sum with moving average