CODEWITHSUNDEEP
pythonpandasaggregationpandas-resample
Phil P.
Phil P.

Reputation: 7

How to resample DAILY data to MONTHLY and then resample MONTHLY to ANNUAL when working with multi-indexed columns in Pandas

So I'm analysing my budget based on daily expenses that I aggregate monthly "DFM" (OK), but then I want to aggregate DFM on a yearly basis (DFM_Y) to obtain "monthly_min", "monthly_max" and "monthly_mean", but I can't find the right way to handle the multi-indexed columns in the agg( ) context.

My current code with DFY gives the "sum" alright, but the rest is all daily ("daily_min", "daily_max" and "daily_mean", which is not super informative)

Any suggestions on how to create DFM_Y ?

Here's an example of DFM

import pandas as pd

# Create a sample dataframe
df = pd.DataFrame({'date' : pd.date_range(start='1/1/2023', end='12/31/2023', freq='D'),
                   'code' : [1,2,3,4,5] * 73,
                   'value': range(365)})

# Set index 'date' for "resample" 
# --> Resample by MONTH, YEAR and YEAR based on MONTLY VALUES
df = df.set_index('date')

# Le GROUPBY de SOMMES par MOIS sert pour le GROUPBY par ANNÉE pcq on veut
# les MIN, MAX, MOY et SUM par mois et non par transactions
dfm = df.groupby('code').resample("M").agg({
    'value': ['sum', 'min', 'mean', 'max'], 
}).round()

dfy = df.groupby('code').resample("Y").agg({
    'value': ['sum', 'min', 'mean', 'max']
}).round()


dfm_y = ??????????????????????????????????

Upvotes: 0

Views: 94

Answers (0)

Related Questions