Pandas Rolling Time Window Custom Functions with Multiple Columns

As a data analyst or scientist, working with time series data can be a challenging task. One common problem when dealing with time series data is calculating correlations and distances between different variables within a given time window. In this article, we will explore how to create custom functions for rolling time windows in pandas DataFrames that support multiple columns.

Background

Pandas provides an efficient way to calculate the rolling mean, median, or standard deviation of a column within a specified time window using the rolling function. However, this function does not return a DataFrame but rather a Series (or array). This limitation can make it difficult to perform certain calculations that require multiple columns.

Problem Statement

The problem presented in the question is to calculate the Pearson correlation factor between two columns (tp and device_tp) within a rolling time window of 12 hours. The Dynamic Time Warping algorithm (DTW) also needs to be applied on each data point using this rolling time window.

The original code attempts to solve this problem by iterating over each row in the DataFrame, calculating the distance between the two columns for the current row and its corresponding indices within the last 12 hours. However, this approach is not efficient due to the high number of iterations required.

Solution

To efficiently calculate the Pearson correlation factor and DTW distances between tp and device_tp, we can utilize a custom function that utilizes pandas’ rolling feature in conjunction with the fastdtw library for DTW distance calculation.

Rolling Time Window Function

We define a custom function called rolling_dtw that takes in several parameters:

• df: The input DataFrame containing the time series data.
• win: The size of the rolling time window (default is 12 hours).
• center: Whether to center the rolling window or not (default is False).
• min_periods: The minimum number of observations required in the window for calculation (default is 2).

The function first extracts the indices from the ’ts’ column and then creates a new Series containing the values of tp and device_tp. It defines an inner function called rolldist that calculates the DTW distance between these two Series for the current set of indices.

from fastdtw import fastdtw 

def rolling_dtw(df, win=12, center=False, min_periods=2,
                col0="ts", col1="tp", col2="device_tp"):
    indices = df[col0].values  # convert to numpy array for efficient indexing
    
    a = df[col1].values
    b = df[col2].values

    def rolldist(inds):  
        inds = inds.astype(int)  # manual type-cast is needed here
        return fastdtw(a[inds], b[inds])[0]
        
    return indices.rolling(win, center=center,
                           min_periods=min_periods).apply(rolldist)

Calculating Pearson Correlation Factor

To calculate the Pearson correlation factor between tp and device_tp, we can use pandas’ built-in rolling feature along with the corr method.

# assuming df['tp'] and df['device_tp'] are the columns of interest
df["device_tp"].rolling(12, min_periods=2).corr(other=df["tp"])

Example Usage

Here’s an example usage of our custom function:

import pandas as pd

# sample data
data = {
    'ts': pd.date_range(start='2023-01-01 00:00:00', periods=100, freq='h'),
    'tp': [1, 2, 3, 4, 5],
    'device_tp': [10, 20, 30, 40, 50]
}

df = pd.DataFrame(data)

# apply rolling dtw function
rolling_dtw_result = rolling_dtw(df, win=12, col0='ts', col1='tp', col2='device_tp')

# apply pearson correlation factor calculation
correlation_factor = df["device_tp"].rolling(12, min_periods=2).corr(other=df["tp"])

print("Rolling DTW Distances:")
print(rolling_dtw_result)

print("\nPearson Correlation Factor:")
print(correlation_factor)

This example demonstrates how to utilize our custom function rolling_dtw along with pandas’ built-in features to efficiently calculate the Pearson correlation factor and DTW distances between two columns within a rolling time window.

Last modified on 2024-11-18