Pandas DataFrame resample() and aggregate() with MultiIndex columns

Question

I have a pandas DataFrame with a DatetimeIndex (1 level) and a MultiIndex columns (2 levels). I am trying to resample over the DatetimeIndex while applying different aggregation functions over different columns.

Here's a code example:

df.groupby([Grouper(freq="5Min"),
            df.columns.unique(level=1)]).agg({"sub_col_0_name": "min",
                                              "sub_col_1_name": "max",
                                              "sub_col_2_name": "mean",
                                              "sub_col_3_name": "std"})

I am getting the following error:

ValueError: Grouper and axis must be same length

Can someone explain me how to aggregate over the DatetimeIndex and aggregate different columns with different functions? Thank you.

Laurent · Accepted Answer · 2022-09-21 08:55:34Z

1

With the following toy dataframe:

import numpy as np
import pandas as pd

arrays = [
    ["foo", "foo", "foo", "foo", "bar", "bar", "bar", "bar"],
    ["one", "two", "three", "four", "one", "two", "three", "four"],
]

tuples = list(zip(*arrays))

index = pd.MultiIndex.from_tuples(tuples, names=["first", "second"])

df = pd.DataFrame(
    np.random.randint(10, size=(10, 8)),
    index=pd.date_range("2022-09-17", periods=10, freq="1min"),
    columns=index,
)
# Output

Here is one way to do it:

df.groupby(pd.Grouper(freq="5min")).agg(
    {
        (col_lvl_0, col_lvl_1): func
        for (col_lvl_0, col_lvl_1), func in zip(
            df.columns, (min, max, np.mean, np.std) * df.columns.levshape[0]
        )
    }
)

Output:

edited Sep 21, 2022 at 8:55

answered Sep 17, 2022 at 17:48

Laurent

13.7k7 gold badges30 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ilpomo Over a year ago

Thank you! Do you think it's possibile to accomplish the same result without using concat and by only concatenating pandas functions over the starting dataframe?

Laurent Over a year ago

I've given some more thought to your question and found a solution that, I think, meets your expectations, see my updated answer. Cheers.

Collectives™ on Stack Overflow

Pandas DataFrame resample() and aggregate() with MultiIndex columns

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related