Creating a custom groupby in pandas with overlapping

Question

I am aggregating dataframes that contain data evenly sampled using pd.Grouper to group the data and then apply a function. Internally, once a group is defined by time (imagine groups of 1 second), the consecutive group starts at the following second without overlapping.

Imagine that the data is this table:

| time | col1 |
|:----:|:----:|
| 0.0s | 0.15 |
| 0.1s | 0.21 |
| 0.2s | 0.05 |
| ...  | ...  |

Then I used this dataframe to aggregate the data with groupby and grouper to aggregate by 1 second:

grouped = df.groupby([pd.Grouper(level=df.time, freq='1s', dropna=True) ])

If the data on the dataframe has a constant sampling (0.1 Hz), the result will be an object of type in which each group will have a dataframe with 10 rows. However, I cannot specify that I want groups with 10 rows but with an advance of 5 rows between groups (something similar to what Welch algorithm does to compute FFT, but with a DataFrame).

My question is: Is it possible to define the groupby() with an overlap between groups? I know that it is better to do it witouth the groupby(), but I want that my result is of the same type pandas.core.groupby.generic.SeriesGroupBy

What I tried was to create a generator in the form:

def custom_grouper(df, bucket, overlap):
    start= 0
    step = bucket - overlap

    while start+bucket < df.shape[0]:
        yield start, df.iloc[start:start+bucket]
        start += step

However, I cannot use the functions of a groupby object. Can I transform this generator into a custom groupby?

Rawson · Answer

To be able to do as you want to in a regular groupby, you effectively want to make the groupby not work, as rows are not in a single group but multiple. This is not something that I think is possible.

As a way around this, to complete your specific need, you could grouping twice: first to get group categories, then again to get the type.

# give a unique number to each group
df["groups"] = df.groupby(pd.Grouper(key="time", freq='1s', dropna=True)).ngroup()
# add a second group number that is shifted 5 up (to include the previous 5 values) to the list
df["groups"] = pd.concat([df["groups"], df["groups"].shift(-5)], ignore_index=True, axis=1).values.tolist()
# explode this column to give individual rows for each item in the list
# and delete duplicates (this will be for those that are not in the last five of a given group)
df = df.explode("groups").drop_duplicates()
# map to the pd.Grouper groups
df["groups"] = df["groups"].map(pd.DataFrame(
    list(df.groupby(pd.Grouper(
        key="time", freq='1s', dropna=True)).groups.keys())).to_dict()[0])

# your groupby
grouped = df.set_index("groups")["col1"].groupby("groups")

It is slightly convoluted, as you most likely require the index to be time-based, not integers for groups.

Note: because this is grouping on the already-grouped column, the actual time values are lost. This will not be a problem is an aggregation is to be used, but if you require the actual times then this does not work.

Creating a custom groupby in pandas with overlapping

1 Answers