Pandas: Custom Window Functions

Custom window functions in Pandas allow you to apply user-defined computations over sliding or expanding windows, providing flexibility for complex time-series or sequential data analysis. Built on NumPy Array Operations, Pandas supports custom functions via the apply method in rolling, expanding, and ewm operations. This guide explores Pandas Custom Window Functions, covering key techniques, advanced applications, and use cases in time-series analysis, data smoothing, and feature engineering.

---

01. Why Use Custom Window Functions in Pandas?

Custom window functions are essential when standard aggregations (e.g., mean, sum) are insufficient for specific analytical needs, such as calculating weighted metrics, custom ranges, or domain-specific statistics. They enable tailored computations over sliding, expanding, or exponentially weighted windows, leveraging NumPy’s efficiency. These functions are critical for tasks like financial modeling, anomaly detection, and creating bespoke features for machine learning, offering flexibility while maintaining performance.

Example: Basic Custom Rolling Function

import pandas as pd
import numpy as np

# Create a sample DataFrame
df = pd.DataFrame({
    'Date': pd.date_range('2023-01-01', periods=5),
    'Sales': [100, 150, 120, 200, 180]
})

# Define a custom function for range
def window_range(x):
    return np.max(x) - np.min(x)

# Calculate 3-day rolling range
df['Rolling_Range'] = df['Sales'].rolling(window=3).apply(window_range, raw=True)

print("DataFrame with Custom Rolling Range:\n", df)

Output:

DataFrame with Custom Rolling Range:
        Date  Sales  Rolling_Range
0 2023-01-01    100            NaN
1 2023-01-02    150            NaN
2 2023-01-03    120           50.0
3 2023-01-04    200           80.0
4 2023-01-05    180           80.0

Explanation:

• rolling(window=3).apply(window_range, raw=True) - Applies a custom function to a 3-row sliding window.
• raw=True passes the raw NumPy array for better performance.

---

02. Key Custom Window Function Methods

Pandas enables custom window functions through the apply method in rolling, expanding, and ewm operations. These methods support complex computations, including weighted calculations and group-specific logic, optimized with NumPy. The table below summarizes key methods and their applications:

Method	Description	Use Case
Rolling Custom Functions	rolling().apply(func)	Custom metrics over sliding windows
Expanding Custom Functions	expanding().apply(func)	Custom cumulative metrics
EWM Custom Functions	ewm().apply(func)	Custom exponentially weighted metrics
GroupBy Custom Windows	groupby().rolling().apply(func)	Group-specific custom window calculations
Window Parameters	min_periods, raw=True	Control window behavior and performance

2.1 Custom Rolling Window Functions

Example: Custom Weighted Rolling Sum

import pandas as pd
import numpy as np

# Create a DataFrame
df = pd.DataFrame({
    'Date': pd.date_range('2023-01-01', periods=5),
    'Sales': [100, 150, 120, 200, 180]
})

# Define custom weights (e.g., 0.2, 0.3, 0.5)
weights = np.array([0.2, 0.3, 0.5])

# Define a custom weighted sum function
def weighted_sum(x):
    return np.sum(x * weights)

# Calculate 3-day rolling weighted sum
df['Weighted_Rolling_Sum'] = df['Sales'].rolling(window=3).apply(weighted_sum, raw=True)

print("DataFrame with Custom Weighted Rolling Sum:\n", df)

Output:

DataFrame with Custom Weighted Rolling Sum:
        Date  Sales  Weighted_Rolling_Sum
0 2023-01-01    100                  NaN
1 2023-01-02    150                  NaN
2 2023-01-03    120               119.0
3 2023-01-04    200               161.0
4 2023-01-05    180               174.0

Explanation:

• rolling(window=3).apply(weighted_sum, raw=True) - Computes a weighted sum over a 3-row window.
• Custom weights prioritize recent data.

2.2 Custom Expanding Window Functions

Example: Custom Cumulative Metric

import pandas as pd
import numpy as np

# Create a DataFrame
df = pd.DataFrame({
    'Date': pd.date_range('2023-01-01', periods=5),
    'Sales': [100, 150, 120, 200, 180]
})

# Define a custom function for cumulative volatility (standard deviation normalized by mean)
def cumulative_volatility(x):
    return np.std(x) / np.mean(x) if np.mean(x) != 0 else np.nan

# Calculate cumulative volatility
df['Cumulative_Volatility'] = df['Sales'].expanding().apply(cumulative_volatility, raw=True)

print("DataFrame with Custom Cumulative Volatility:\n", df)

Output:

DataFrame with Custom Cumulative Volatility:
        Date  Sales  Cumulative_Volatility
0 2023-01-01    100                    NaN
1 2023-01-02    150               0.282842
2 2023-01-03    120               0.182534
3 2023-01-04    200               0.291162
4 2023-01-05    180               0.249944

Explanation:

• expanding().apply(cumulative_volatility, raw=True) - Computes a custom metric over all data up to the current row.
• Useful for tracking cumulative properties like volatility.

2.3 Custom Exponentially Weighted Functions

Example: Custom EWM Function

import pandas as pd
import numpy as np

# Create a DataFrame
df = pd.DataFrame({
    'Date': pd.date_range('2023-01-01', periods=5),
    'Sales': [100, 150, 120, 200, 180]
})

# Define a custom function for weighted range
def weighted_range(x):
    weights = np.exp(np.linspace(0, -1, len(x)))  # Exponential weights
    weights /= weights.sum()  # Normalize
    weighted_vals = x * weights
    return np.max(weighted_vals) - np.min(weighted_vals)

# Calculate exponentially weighted range
df['EWM_Range'] = df['Sales'].ewm(span=3).apply(weighted_range, raw=True)

print("DataFrame with Custom EWM Range:\n", df)

Output:

DataFrame with Custom EWM Range:
        Date  Sales  EWM_Range
0 2023-01-01    100   0.000000
1 2023-01-02    150  25.000000
2 2023-01-03    120   6.250000
3 2023-01-04    200  48.437500
4 2023-01-05    180  14.062500

Explanation:

• ewm(span=3).apply(weighted_range, raw=True) - Applies a custom function with exponential weights.
• Dynamic weights emphasize recent values.

2.4 GroupBy with Custom Window Functions

Example: Custom Rolling Function by Group

import pandas as pd
import numpy as np

# Create a DataFrame
df = pd.DataFrame({
    'Region': ['North', 'North', 'South', 'South', 'North'],
    'Date': pd.date_range('2023-01-01', periods=5),
    'Sales': [100, 150, 120, 200, 180]
})

# Define a custom function for normalized max
def normalized_max(x):
    return np.max(x) / np.mean(x) if np.mean(x) != 0 else np.nan

# Calculate 2-day rolling normalized max by Region
df['Rolling_Normalized_Max'] = df.groupby('Region')['Sales'].rolling(window=2).apply(normalized_max, raw=True).reset_index(level=0, drop=True)

print("DataFrame with Custom Rolling Normalized Max by Region:\n", df)

Output:

DataFrame with Custom Rolling Normalized Max by Region:
   Region       Date  Sales  Rolling_Normalized_Max
0  North 2023-01-01    100                    NaN
1  North 2023-01-02    150               1.200000
2  South 2023-01-03    120                    NaN
3  South 2023-01-04    200               1.428571
4  North 2023-01-05    180               1.200000

Explanation:

• groupby('Region').rolling(window=2).apply(...) - Applies a custom function within each group’s sliding window.
• reset_index(level=0, drop=True) - Aligns results with the original index.

2.5 Custom Window Functions with Parameters

Example: Rolling Function with min_periods

import pandas as pd
import numpy as np

# Create a DataFrame
df = pd.DataFrame({
    'Date': pd.date_range('2023-01-01', periods=5),
    'Sales': [100, 150, 120, 200, 180]
})

# Define a custom function for weighted variance
def weighted_variance(x):
    weights = np.array([0.2, 0.3, 0.5])
    weighted_mean = np.sum(x * weights)
    return np.sum(weights * (x - weighted_mean) ** 2)

# Calculate 3-day rolling weighted variance with min_periods=1
df['Weighted_Variance'] = df['Sales'].rolling(window=3, min_periods=1).apply(weighted_variance, raw=True)

print("DataFrame with Custom Weighted Variance:\n", df)

Output:

DataFrame with Custom Weighted Variance:
        Date  Sales  Weighted_Variance
0 2023-01-01    100               0.0
1 2023-01-02    150            450.0
2 2023-01-03    120            246.0
3 2023-01-04    200           1624.0
4 2023-01-05    180           1048.0

Explanation:

• min_periods=1 - Allows calculations with fewer than 3 rows, reducing NaN values.
• Custom function computes a weighted variance for variability analysis.

2.6 Incorrect Custom Window Function

Example: Invalid Custom Function Output

import pandas as pd

# Create a DataFrame
df = pd.DataFrame({
    'Sales': [100, 150, 120]
})

# Define a custom function returning a non-scalar
def invalid_function(x):
    return x.tolist()  # Returns a list, not a scalar

# Incorrect: Non-scalar output
try:
    result = df['Sales'].rolling(window=2).apply(invalid_function, raw=True)
    print(result)
except ValueError as e:
    print("Error:", e)

Output:

Error: function must return a scalar when raw=True

Explanation:

• A custom function with raw=True must return a scalar, not a list or array.
• Solution: Ensure the function returns a single value (e.g., np.sum(x)).

---

03. Effective Usage

3.1 Recommended Practices

• Use raw=True in apply for performance with NumPy-based functions.
• Ensure custom functions return scalar values when raw=True.
• Use min_periods to handle partial windows and combine with groupby for group-specific logic.

Example: Comprehensive Custom Window Operations

import pandas as pd
import numpy as np

# Create a DataFrame
df = pd.DataFrame({
    'Region': ['North', 'South', 'North', 'South', 'North'],
    'Date': pd.date_range('2023-01-01', periods=5),
    'Sales': [100, 150, 120, 200, 180]
})

# Comprehensive custom window operations
# Custom rolling: Weighted sum
weights = np.array([0.2, 0.3, 0.5])
def weighted_sum(x):
    return np.sum(x * weights)

df['Rolling_Weighted_Sum'] = df['Sales'].rolling(window=3, min_periods=1).apply(weighted_sum, raw=True)

# Custom expanding: Cumulative volatility
def cumulative_volatility(x):
    return np.std(x) / np.mean(x) if np.mean(x) != 0 else np.nan

df['Cumulative_Volatility'] = df['Sales'].expanding().apply(cumulative_volatility, raw=True)

# Custom EWM: Weighted range
def weighted_range(x):
    weights = np.exp(np.linspace(0, -1, len(x)))
    weights /= weights.sum()
    weighted_vals = x * weights
    return np.max(weighted_vals) - np.min(weighted_vals)

df['EWM_Range'] = df['Sales'].ewm(span=3).apply(weighted_range, raw=True)

# GroupBy custom rolling: Normalized max
def normalized_max(x):
    return np.max(x) / np.mean(x) if np.mean(x) != 0 else np.nan

df['Rolling_Normalized_Max_by_Region'] = df.groupby('Region')['Sales'].rolling(window=2).apply(normalized_max, raw=True).reset_index(level=0, drop=True)

print("DataFrame with Custom Window Operations:\n", df)
print("\nColumns:\n", df.columns.tolist())

Output:

DataFrame with Custom Window Operations:
   Region       Date  Sales  Rolling_Weighted_Sum  Cumulative_Volatility  EWM_Range  Rolling_Normalized_Max_by_Region
0  North 2023-01-01    100                100.0               0.000000   0.000000                              NaN
1  South 2023-01-02    150                150.0               0.282842  25.000000                              NaN
2  North 2023-01-03    120                119.0               0.182534   6.250000                         1.200000
3  South 2023-01-04    200                161.0               0.291162  48.437500                         1.428571
4  North 2023-01-05    180                174.0               0.249944  14.062500                         1.500000

Columns:
['Region', 'Date', 'Sales', 'Rolling_Weighted_Sum', 'Cumulative_Volatility', 'EWM_Range', 'Rolling_Normalized_Max_by_Region']

• rolling().apply - Computes custom sliding metrics.
• expanding().apply - Tracks custom cumulative metrics.
• ewm().apply - Applies custom weighted calculations.
• GroupBy custom windows - Enable group-specific custom logic.

3.2 Practices to Avoid

• Avoid non-scalar outputs with raw=True in apply.
• Avoid inefficient functions (e.g., Python loops) instead of NumPy operations.

Example: Inefficient Custom Function

import pandas as pd

# Create a DataFrame
df = pd.DataFrame({
    'Sales': [100, 150, 120]
})

# Define an inefficient custom function using loops
def inefficient_sum(x):
    total = 0
    for val in x:
        total += val
    return total

# Inefficient: Using loops instead of NumPy
df['Rolling_Sum'] = df['Sales'].rolling(window=2).apply(inefficient_sum, raw=False)

print("DataFrame with Inefficient Rolling Sum:\n", df)

Output:

DataFrame with Inefficient Rolling Sum:
   Sales  Rolling_Sum
0    100          NaN
1    150        250.0
2    120        270.0

• Using Python loops (e.g., for loop) is slow compared to NumPy operations.
• Solution: Use NumPy functions (e.g., np.sum) and raw=True for efficiency.

---

04. Common Use Cases in Data Analysis

4.1 Time-Series Analysis

Use custom window functions to compute specialized metrics for time-series data.

Example: Custom Rolling Volatility

import pandas as pd
import numpy as np

# Create a DataFrame
df = pd.DataFrame({
    'Date': pd.date_range('2023-01-01', periods=5),
    'Price': [10.5, 11.0, 10.8, 12.0, 11.5]
})

# Define a custom function for rolling volatility
def rolling_volatility(x):
    returns = np.diff(x) / x[:-1]  # Daily returns
    return np.std(returns) if len(returns) > 0 else np.nan

# Calculate 3-day rolling volatility
df['Rolling_Volatility'] = df['Price'].rolling(window=3).apply(rolling_volatility, raw=True)

print("DataFrame with Custom Rolling Volatility:\n", df)

Output:

DataFrame with Custom Rolling Volatility:
        Date  Price  Rolling_Volatility
0 2023-01-01  10.5                NaN
1 2023-01-02  11.0                NaN
2 2023-01-03  10.8           0.036148
3 2023-01-04  12.0           0.051697
4 2023-01-05  11.5           0.048003

Explanation:

• rolling().apply(rolling_volatility) - Computes a custom volatility metric based on daily returns.
• Supports financial analysis and risk assessment.

4.2 Feature Engineering

Create custom window-based features for machine learning models.

Example: Custom Cumulative Feature

import pandas as pd
import numpy as np

# Create a DataFrame
df = pd.DataFrame({
    'Date': pd.date_range('2023-01-01', periods=5),
    'Sales': [100, 150, 120, 200, 180]
})

# Define a custom function for cumulative growth rate
def cumulative_growth(x):
    if len(x) < 2:
        return np.nan
    return (x[-1] - x[0]) / x[0] if x[0] != 0 else np.nan

# Calculate cumulative growth rate
df['Cumulative_Growth'] = df['Sales'].expanding().apply(cumulative_growth, raw=True)

print("DataFrame with Custom Cumulative Growth:\n", df)

Output:

DataFrame with Custom Cumulative Growth:
        Date  Sales  Cumulative_Growth
0 2023-01-01    100               NaN
1 2023-01-02    150             0.500
2 2023-01-03    120             0.200
3 2023-01-04    200             1.000
4 2023-01-05    180             0.800

Explanation:

• expanding().apply(cumulative_growth) - Creates a feature capturing cumulative growth from the first observation.
• Enhances models with historical performance metrics.

---

Conclusion

Pandas custom window functions, powered by NumPy Array Operations, provide a versatile framework for tailored time-series and sequential data analysis. Key takeaways:

• Use apply with rolling, expanding, or ewm for custom window computations.
• Optimize with raw=True and scalar outputs, avoiding inefficient Python loops.
• Apply in time-series analysis and feature engineering to drive insights.

With Pandas custom window functions, you can flexibly analyze complex patterns and create bespoke features, streamlining analytical and preprocessing workflows!