Pandas Grouper with multiple categorical columns

user_8382707 0
Viewed 68

I have a pandas dataframe that looks like this

          Cat1  Cat2    Cat3    Amount  Offset  Adjust
2018-01-01   A     X       P    12.0    15.0    20.0
2018-01-02   B     Z       Q    42.0    43.0    67.0
2018-01-03   C     Y       R    80.0    15.0    40.0
2018-01-04   B     Z       R    15.0    55.0    30.0
2018-01-05   A     X       P    11.0    20.0    22.0
2018-01-06   A     Z       Q    13.0    10.0    30.0

What I am trying to achieve:

  1. Resample the TS at weekly level but also
  2. Groupby Cat1 and Cat2
  3. Keep the first item for the remaining categorical columns
  4. Sum up the aggregated values for the numerical columns

The final dataframe will look like this:

2018-01-06   A  X   P  23.0  35.0  42.0
                Z   Q  13.0  10.0  30.0
             B  Z   Q  57.0  98.0  107.0
             ...
pandas data-analysis time-series dataframe python
1 Answers

With the following dataframe:

import pandas as pd

df = pd.DataFrame(
    {
        "Cat1": {"2018-01-01": "A", "2018-01-02": "B", "2018-01-03": "C", "2018-01-04": "B", "2018-01-05": "A", "2018-01-06": "A"},
        "Cat2": {"2018-01-01": "X", "2018-01-02": "Z", "2018-01-03": "Y", "2018-01-04": "Z", "2018-01-05": "X", "2018-01-06": "Z"},
        "Cat3": {"2018-01-01": "P", "2018-01-02": "Q", "2018-01-03": "R", "2018-01-04": "R", "2018-01-05": "P", "2018-01-06": "Q"},
        "Amount": {"2018-01-01": 12.0, "2018-01-02": 42.0, "2018-01-03": 80.0, "2018-01-04": 15.0, "2018-01-05": 11.0, "2018-01-06": 13.0},
        "Offset": {"2018-01-01": 15.0, "2018-01-02": 43.0, "2018-01-03": 15.0, "2018-01-04": 55.0, "2018-01-05": 20.0, "2018-01-06": 10.0},
        "Adjust": {"2018-01-01": 20.0, "2018-01-02": 67.0, "2018-01-03": 40.0, "2018-01-04": 30.0, "2018-01-05": 22.0, "2018-01-06": 30.0},
    }
)

Here is one way to do it:

# Setup
df = df.reset_index()
df["index"] = pd.to_datetime(df["index"], format="%Y-%m-%d")

# Find target week day and update other values
df.loc[df["index"].dt.dayofweek != 5, "index"] = pd.NA
df["index"] = df["index"].fillna(method="bfill")

# Group values
df = df.groupby(["index", "Cat1", "Cat2"]).agg(
    {"Cat3": lambda x: x, "Amount": sum, "Offset": sum, "Adjust": sum}
)
df["Cat3"] = df["Cat3"].apply(lambda x: x[0])

So that:

print(df)
# Output
                     Cat3  Amount  Offset  Adjust
index      Cat1 Cat2
2018-01-06 A    X       P    23.0    35.0    42.0
                Z       Q    13.0    10.0    30.0
           B    Z       Q    57.0    98.0    97.0
           C    Y       R    80.0    15.0    40.0
user_112460560
Related