Implementing Non-Overlapping Rolling Functionality on MultiIndex DataFrame

Introduction

When working with MultiIndex DataFrames, it can be challenging to implement rolling functionality in a non-overlapping manner. The standard rolling function in pandas slides through the values instead of stepping through them, making it difficult to achieve non-overlapping results. However, by utilizing custom resampling and manipulation of the index, we can overcome this limitation.

In this article, we will explore how to implement non-overlapping rolling functionality on a MultiIndex DataFrame using groupby with custom resample functions.

Problem Statement

We have a MultiIndex DataFrame with different amounts of data for each outer level. We want to sum the last two values for each outer level in a non-overlapping manner, starting from the last value. For example, for the ‘A’ outer level, we want to sum the inner levels 3 and 4, and for the ‘B’ outer level, we want to sum the inner levels 4 and 5.

Solution Overview

To achieve this, we will follow these steps:

1. Remove all MultiIndexing to deal with regular column groupbys.
2. Use groupby and apply a custom function to each group.
3. In the custom function:
   • Determine the even length of the group.
   • Select that length backwards.
   • Convert the index into seconds.
   • Resample the DataFrame every two samples by summing.
   • Resample the Inner column every two by last() to preserve original index numbers.
   • Convert index back to Inner.

Custom Resampling Function

The custom function is where the magic happens. We will define a Python function f that takes a group as input and returns the resampled DataFrame.

def f(g):
    even_length = int(2.0 * math.floor(len(g) / 2.0))
    every_two_backwards = g.iloc[-even_length:]
    every_two_backwards.index = pd.TimedeltaIndex(every_two_backwards.index * 1000000000.0)
    resample_via_sum = every_two_backwards.resample('2s').sum().dropna()
    resample_via_sum['Inner'] = every_two_backwards.resample('2s').last()
    resample_via_sum = resample_via_sum.set_index('Inner')

    return resample_via_sum

Groupby and Custom Resampling

We will use groupby to group the DataFrame by the ‘Outer’ column, and then apply our custom function f to each group.

resampled_df = df.groupby(['Outer']).apply(f)

Resulting DataFrame

The resulting DataFrame will have the desired non-overlapping rolling functionality applied.

             Value
Outer Inner       
A     2.0      6.0
      4.0     14.0
B     3.0     15.0
      5.0     27.0

Discussion

The custom resampling function works by selecting every two samples from the backwards direction, converting the index into seconds, and then summing or last()ing as needed. This approach allows us to step through the values in a non-overlapping manner.

Using resample instead of rolling is key to achieving this non-overlapping behavior. While rolling slides through the values, resample steps through them, making it possible to preserve the original index structure.

Example Use Case

This solution can be applied to various use cases where non-overlapping rolling functionality is required, such as:

• Financial analysis: calculating daily moving averages without overlapping data points.
• Time series analysis: analyzing time-series data with non-overlapping windows.

By following this approach, you can implement non-overlapping rolling functionality on a MultiIndex DataFrame and unlock new insights in your data analysis.

Last modified on 2023-07-02