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

ilpomo 0
Viewed 60

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.

dataframe pandas python
1 Answers

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

enter image description here

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: enter image description here

userCDbL0
Related