This page includes an interactive code editor. Try modifying and running the examples!

← MultiIndex & Hierarchical Data Time Series →

Pandas Window Functions

Window functions are powerful tools in Pandas that allow you to perform calculations across a set of table rows that are somehow related to the current row. They are essential for time series analysis, running totals, moving averages, and ranking operations.

1. Types of Window Functions

Window Type	Description	Key Methods	Use Cases
Rolling Windows	Fixed-size moving window	`rolling()`	Moving averages, trends
Expanding Windows	Window grows from start to current row	`expanding()`	Cumulative calculations
Exponential Weighted	Recent values have more weight	`ewm()`	Smoothing, technical analysis
Group-wise Windows	Windows within groups	`groupby().rolling()`	Category-specific trends

2. Common Window Operations

Aggregation Functions

mean() - Average
sum() - Total
std() - Standard deviation
var() - Variance
min()/max() - Extremes

Statistical Functions

quantile() - Percentiles
corr() - Correlation
cov() - Covariance
apply() - Custom functions
agg() - Multiple aggregations

Basic Window Functions

Basic Window Operations

import pandas as pd
import numpy as np

# Create sample sales data
data = {
    'Date': pd.date_range('2024-01-01', periods=12, freq='M'),
    'Salesperson': ['Alice'] * 3 + ['Bob'] * 3 + ['Charlie'] * 3 + ['Diana'] * 3,
    'Region': ['North', 'South', 'East'] * 4,
    'Sales': [1200, 1500, 1100, 1300, 1400, 1250, 1600, 1450, 1350, 1400, 1550, 1200],
    'Customers': [15, 18, 12, 16, 20, 14, 22, 19, 16, 18, 21, 13]
}

df = pd.DataFrame(data)
print("Original Sales Data:")
print(df)

# 1. Basic Rolling Window - Moving Average
print("\n1. 3-Month Moving Average:")
df['Sales_MA_3'] = df['Sales'].rolling(window=3).mean()
print(df[['Date', 'Sales', 'Sales_MA_3']])

# 2. Expanding Window - Cumulative Sum
print("\n2. Cumulative Sales:")
df['Cumulative_Sales'] = df['Sales'].expanding().sum()
print(df[['Date', 'Sales', 'Cumulative_Sales']])

# 3. Rolling Window with Minimum Periods
print("\n3. Moving Average (min_periods=1):")
df['Sales_MA_min1'] = df['Sales'].rolling(window=3, min_periods=1).mean()
print(df[['Date', 'Sales', 'Sales_MA_3', 'Sales_MA_min1']])

# 4. Different Window Functions
print("\n4. Various Window Calculations:")
df['Rolling_Sum'] = df['Sales'].rolling(window=2).sum()
df['Rolling_Max'] = df['Sales'].rolling(window=3).max()
df['Rolling_Min'] = df['Sales'].rolling(window=3).min()
df['Rolling_Std'] = df['Sales'].rolling(window=3).std()

print(df[['Date', 'Sales', 'Rolling_Sum', 'Rolling_Max', 'Rolling_Min', 'Rolling_Std']].round(2))

# 5. Group-wise Rolling Windows
print("\n5. Rolling Average by Salesperson:")
df['Salesperson_MA'] = df.groupby('Salesperson')['Sales'].rolling(window=2).mean().reset_index(level=0, drop=True)
print(df[['Date', 'Salesperson', 'Sales', 'Salesperson_MA']])

3. Advanced Window Techniques

Technique	Description	Example
Custom Functions	Apply user-defined functions to windows	`rolling().apply(custom_func)`
Center Alignment	Center the window on current row	`center=True`
Time-based Windows	Windows based on time periods	`window='7D'`
Minimum Periods	Control NaN values in results	`min_periods=1`

Advanced Window Operations

Advanced Window Functions

import pandas as pd
import numpy as np

# Advanced window functions
np.random.seed(42)
dates = pd.date_range('2024-01-01', periods=90, freq='D')
products = ['Laptop', 'Tablet', 'Phone', 'Monitor']

sales_data = {
    'Date': dates,
    'Product': np.random.choice(products, 90),
    'Units_Sold': np.random.randint(1, 20, 90),
    'Revenue': np.random.randint(100, 2000, 90)
}

df = pd.DataFrame(sales_data)
df['Price'] = (df['Revenue'] / df['Units_Sold']).round(2)

print("Sales Data (first 10 rows):")
print(df.head(10))

# 1. Custom Rolling Functions
print("\n1. Custom Rolling Function - Percentage Change:")

def percentage_change(window):
    if len(window) < 2:
        return np.nan
    return ((window.iloc[-1] - window.iloc[0]) / window.iloc[0] * 100).round(2)

df['Revenue_Pct_Change'] = df.groupby('Product')['Revenue'].rolling(window=7).apply(percentage_change).reset_index(level=0, drop=True)

# 2. Exponential Weighted Moving Average
print("\n2. Exponential Weighted Moving Average:")
df['Revenue_EWMA'] = df.groupby('Product')['Revenue'].ewm(span=5).mean().reset_index(level=0, drop=True)

# 3. Rolling Window with Center Alignment
print("\n3. Centered Moving Average:")
df['Revenue_Centered_MA'] = df['Revenue'].rolling(window=5, center=True).mean()

# 4. Multiple Aggregations in One Pass
print("\n4. Multiple Rolling Aggregations:")
rolling_agg = df.groupby('Product')['Revenue'].rolling(window=7).agg(['mean', 'std', 'min', 'max']).round(2)
rolling_agg.columns = ['Rolling_Mean', 'Rolling_Std', 'Rolling_Min', 'Rolling_Max']
df = df.join(rolling_agg)

# 5. Time-based Rolling Windows
print("\n5. 7-Day Rolling Window (Time-based):")
df_time = df.set_index('Date').sort_index()
df_time['Revenue_7D_Mean'] = df_time['Revenue'].rolling(window='7D').mean()

print("Data with Advanced Window Functions (first 15 rows):")
print(df_time.head(15).round(2))

# 6. Expanding Window with Multiple Functions
print("\n6. Expanding Window Aggregations:")
expanding_stats = df.groupby('Product')['Revenue'].expanding().agg(['mean', 'sum', 'std']).round(2)
expanding_stats.columns = ['Expanding_Mean', 'Expanding_Sum', 'Expanding_Std']
df = df.join(expanding_stats)

print("Final DataFrame with All Window Functions:")
print(df[['Date', 'Product', 'Revenue', 'Revenue_EWMA', 'Revenue_Centered_MA', 
          'Rolling_Mean', 'Expanding_Mean']].head(10).round(2))

Ranking and Analytical Functions

Ranking and Lead/Lag Functions

import pandas as pd
import numpy as np

# Ranking and analytical window functions
data = {
    'Department': ['Sales'] * 6 + ['Marketing'] * 6 + ['IT'] * 6,
    'Employee': ['Alice', 'Bob', 'Charlie', 'Diana', 'Eve', 'Frank'] * 3,
    'Month': ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun'] * 3,
    'Sales': [1200, 1500, 1100, 1300, 1400, 1250, 
              800, 950, 1100, 900, 1000, 850,
              600, 750, 800, 700, 900, 950],
    'Commission': [120, 150, 110, 130, 140, 125, 
                   80, 95, 110, 90, 100, 85,
                   60, 75, 80, 70, 90, 95]
}

df = pd.DataFrame(data)
print("Employee Performance Data:")
print(df)

# 1. Ranking within Groups
print("\n1. Sales Ranking within Each Department:")
df['Rank_Overall'] = df['Sales'].rank(method='dense', ascending=False)
df['Rank_in_Dept'] = df.groupby('Department')['Sales'].rank(method='dense', ascending=False)

# 2. Percentile Rankings
print("\n2. Percentile Rankings:")
df['Percentile_Overall'] = df['Sales'].rank(pct=True).round(2)
df['Percentile_in_Dept'] = df.groupby('Department')['Sales'].rank(pct=True).round(2)

# 3. Lead and Lag Functions (Shift)
print("\n3. Month-over-Month Change:")
df['Sales_Previous_Month'] = df.groupby('Employee')['Sales'].shift(1)
df['Sales_MoM_Change'] = df['Sales'] - df['Sales_Previous_Month']
df['Sales_MoM_Pct_Change'] = ((df['Sales'] - df['Sales_Previous_Month']) / df['Sales_Previous_Month'] * 100).round(2)

# 4. Cumulative Distribution
print("\n4. Cumulative Distribution:")
df['Cumulative_Sales'] = df.groupby('Employee')['Sales'].cumsum()
df['Cumulative_Avg_Sales'] = df.groupby('Employee')['Sales'].expanding().mean().reset_index(level=0, drop=True)

# 5. First and Last Values in Window
print("\n5. First and Last Values in Sequence:")
df['First_Sales_in_Dept'] = df.groupby('Department')['Sales'].transform('first')
df['Last_Sales_in_Dept'] = df.groupby('Department')['Sales'].transform('last')
df['Sales_Range_in_Dept'] = df['Last_Sales_in_Dept'] - df['First_Sales_in_Dept']

# 6. N-largest and N-smallest per Group
print("\n6. Top Performers in Each Department:")

def top_n_sales(group, n=2):
    return group.nlargest(n, 'Sales')

top_performers = df.groupby('Department').apply(top_n_sales)
print("Top 2 Performers per Department:")
print(top_performers[['Department', 'Employee', 'Month', 'Sales', 'Rank_in_Dept']])

print("\nFinal DataFrame with All Ranking Functions:")
result_cols = ['Department', 'Employee', 'Month', 'Sales', 'Rank_in_Dept', 
               'Percentile_in_Dept', 'Sales_MoM_Pct_Change', 'Cumulative_Sales']
print(df[result_cols].round(2))

Real-World Stock Market Analysis

Technical Analysis with Window Functions

import pandas as pd
import numpy as np

# Real-world example: Stock market analysis
np.random.seed(42)
dates = pd.date_range('2024-01-01', periods=252, freq='B')  # Business days
tickers = ['AAPL', 'GOOGL', 'MSFT', 'TSLA', 'AMZN']

# Generate realistic stock data
stock_data = []
for date in dates:
    for ticker in tickers:
        stock_data.append({
            'Date': date,
            'Ticker': ticker,
            'Price': np.random.normal(150, 50),  # Random price around 150
            'Volume': np.random.randint(1000000, 10000000)
        })

df = pd.DataFrame(stock_data)
df['Price'] = df.groupby('Ticker')['Price'].cumsum()  # Make prices trend

print("Stock Data (first 15 rows):")
print(df.head(15))

# 1. Technical Indicators using Window Functions
print("\n1. Technical Analysis Indicators:")

# Simple Moving Averages
df['SMA_20'] = df.groupby('Ticker')['Price'].rolling(window=20).mean().reset_index(level=0, drop=True)
df['SMA_50'] = df.groupby('Ticker')['Price'].rolling(window=50).mean().reset_index(level=0, drop=True)

# Bollinger Bands
df['Rolling_Std_20'] = df.groupby('Ticker')['Price'].rolling(window=20).std().reset_index(level=0, drop=True)
df['Bollinger_Upper'] = df['SMA_20'] + (df['Rolling_Std_20'] * 2)
df['Bollinger_Lower'] = df['SMA_20'] - (df['Rolling_Std_20'] * 2)

# Relative Strength Index (RSI)
def calculate_rsi(series, window=14):
    delta = series.diff()
    gain = (delta.where(delta > 0, 0)).rolling(window=window).mean()
    loss = (-delta.where(delta < 0, 0)).rolling(window=window).mean()
    rs = gain / loss
    rsi = 100 - (100 / (1 + rs))
    return rsi

df['RSI_14'] = df.groupby('Ticker')['Price'].apply(calculate_rsi).reset_index(level=0, drop=True)

# 2. Price Momentum and Trends
print("\n2. Momentum Indicators:")

# Rate of Change
df['ROC_10'] = df.groupby('Ticker')['Price'].pct_change(periods=10) * 100

# Moving Average Convergence Divergence (MACD)
df['EMA_12'] = df.groupby('Ticker')['Price'].ewm(span=12).mean().reset_index(level=0, drop=True)
df['EMA_26'] = df.groupby('Ticker')['Price'].ewm(span=26).mean().reset_index(level=0, drop=True)
df['MACD'] = df['EMA_12'] - df['EMA_26']
df['MACD_Signal'] = df.groupby('Ticker')['MACD'].ewm(span=9).mean().reset_index(level=0, drop=True)

# 3. Volume Analysis
print("\n3. Volume-based Indicators:")

# Volume Moving Average
df['Volume_MA_20'] = df.groupby('Ticker')['Volume'].rolling(window=20).mean().reset_index(level=0, drop=True)

# Volume Price Trend
df['VPT'] = df.groupby('Ticker').apply(
    lambda x: (x['Volume'] * (x['Price'].pct_change())).cumsum()
).reset_index(level=0, drop=True)

# 4. Performance Ranking
print("\n4. Performance Analysis:")

# Daily returns
df['Daily_Return'] = df.groupby('Ticker')['Price'].pct_change()

# Rolling volatility (20-day)
df['Volatility_20D'] = df.groupby('Ticker')['Daily_Return'].rolling(window=20).std().reset_index(level=0, drop=True) * np.sqrt(252)

# Cumulative returns
df['Cumulative_Return'] = df.groupby('Ticker')['Daily_Return'].apply(lambda x: (1 + x).cumprod() - 1).reset_index(level=0, drop=True)

# Display results for one stock
aapl_data = df[df['Ticker'] == 'AAPL'].tail(20)
print("AAPL Technical Analysis (last 20 days):")
display_cols = ['Date', 'Price', 'SMA_20', 'Bollinger_Upper', 'Bollinger_Lower', 
                'RSI_14', 'MACD', 'Volume_MA_20', 'Volatility_20D']
print(aapl_data[display_cols].round(2))

Performance Optimization

Window Functions Performance

import pandas as pd
import numpy as np
import time

# Performance comparison of window functions
print("=== WINDOW FUNCTIONS PERFORMANCE COMPARISON ===\n")

# Create large dataset
np.random.seed(42)
n_points = 100000
groups = 1000

large_data = {
    'group': np.random.randint(0, groups, n_points),
    'value': np.random.randn(n_points) * 100 + 500,
    'timestamp': pd.date_range('2024-01-01', periods=n_points, freq='min')
}

df_large = pd.DataFrame(large_data)
print(f"Dataset size: {len(df_large):,} rows")
print(f"Number of groups: {df_large['group'].nunique()}")

# Performance tests
results = {}

# 1. Rolling vs Expanding
print("\n1. Rolling vs Expanding Windows:")

start = time.time()
df_large['rolling_mean'] = df_large.groupby('group')['value'].rolling(window=10).mean().reset_index(level=0, drop=True)
results['rolling_10'] = time.time() - start

start = time.time()
df_large['expanding_mean'] = df_large.groupby('group')['value'].expanding().mean().reset_index(level=0, drop=True)
results['expanding'] = time.time() - start

# 2. Different Window Sizes
print("\n2. Different Window Sizes:")

window_sizes = [5, 20, 50, 100]
for window in window_sizes:
    start = time.time()
    df_large[f'rolling_{window}'] = df_large.groupby('group')['value'].rolling(window=window).mean().reset_index(level=0, drop=True)
    results[f'rolling_{window}'] = time.time() - start

# 3. EWM vs Rolling
print("\n3. Exponential vs Simple Moving Average:")

start = time.time()
df_large['ewm_10'] = df_large.groupby('group')['value'].ewm(span=10).mean().reset_index(level=0, drop=True)
results['ewm_10'] = time.time() - start

# 4. Optimized vs Naive approach
print("\n4. Optimized GroupBy vs Naive:")

# Naive approach (without groupby)
start = time.time()
df_large['naive_rolling'] = df_large['value'].rolling(window=10).mean()
results['naive_rolling'] = time.time() - start

# Display results
print("\n=== PERFORMANCE RESULTS ===\n")
for operation, duration in results.items():
    print(f"{operation:15}: {duration:.4f} seconds")

# Memory usage comparison
print("\n=== MEMORY USAGE ===\n")
memory_usage = df_large.memory_usage(deep=True)
print("Memory usage by column (MB):")
for col, usage in memory_usage.items():
    print(f"{col:15}: {usage / 1024 / 1024:.2f} MB")

# Best practices demonstration
print("\n=== BEST PRACTICES ===\n")

# Good: Filter first, then apply window
start = time.time()
filtered_groups = df_large[df_large['group'] < 10]  # Work with subset
result_good = filtered_groups.groupby('group')['value'].rolling(window=10).mean()
time_good = time.time() - start

# Bad: Apply window to entire dataset, then filter
start = time.time()
result_bad = df_large.groupby('group')['value'].rolling(window=10).mean()
result_bad = result_bad[df_large['group'] < 10]
time_bad = time.time() - start

print(f"Filter-then-window: {time_good:.4f} seconds")
print(f"Window-then-filter: {time_bad:.4f} seconds")
print(f"Performance improvement: {((time_bad - time_good) / time_bad * 100):.1f}%")

Best Practices

Use min_periods to control NaN values
Filter data before applying window functions
Use appropriate window sizes for your data
Consider using ewm() for recent trends
Validate results with known calculations

Performance Tips

Avoid custom functions in apply()
Use built-in methods when possible
Consider window size impact on performance
Use reset_index(drop=True) after groupby
Pre-filter data to reduce computation

Common Pitfalls:

Forgetting to handle NaN values from windows
Using inappropriate window sizes
Not resetting index after groupby operations
Applying windows to unsorted time series data
Ignoring performance with large datasets

Pro Tip: For time series data, always ensure your data is sorted by time before applying window functions. Use df.sort_values('timestamp') to avoid incorrect calculations.

Financial Analysis: Window functions are essential for technical analysis in finance. Common indicators like Moving Averages, RSI, MACD, and Bollinger Bands all rely on window operations.

← MultiIndex & Hierarchical Data Time Series →

Frontend

JavaScript & Frameworks

Backend

Artificial Intelligence

Database

CSS Frameworks

Data Analytics

Digital Marketing

Frontend

Backend

Artificial Intelligence

DevOps

Database

Cyber Security

Pandas Tutorial

Pandas Window Functions

1. Types of Window Functions

2. Common Window Operations

Aggregation Functions

Statistical Functions

Basic Window Functions

Basic Window Operations

3. Advanced Window Techniques

Advanced Window Operations

Advanced Window Functions

Ranking and Analytical Functions

Ranking and Lead/Lag Functions

Real-World Stock Market Analysis

Technical Analysis with Window Functions

Performance Optimization

Window Functions Performance

Best Practices

Performance Tips

Follow Us

Our Tools

Our Company

Special Tools