Calculate average by grouping using pandas df-CodePudding

I would like to get average of amount by date i.e., for eg:- my dataframe is like

Date        amount
2021-12-13  234.89
2021-12-06  456.9
2021-11-26  453.56
2021-11-19  453
2021-11-12  222.4
2021-10-29  123.4
2021-10-22  433.99
2021-10-15  784.99
2021-10-06  678.99

average should be sum of all amount values/count of months using pandas dataframe grouping. in the above example average=sum of amount/3.

CodePudding user response：

First of all, let's treat your "Date" column as a datetime:

>> df["Date"] = pd.to_datetime(df["Date"])

You can use pandas.Grouper to group by month of each date (freq="M"), select the "amount" column and calculate the mean of each group using .mean()

>> df.groupby(pd.Grouper(key="Date", freq="M"))["amount"].mean()
Date
2021-12-13    234.89
2021-12-06    456.90
2021-11-26    453.56
2021-11-19    453.00
2021-11-12    222.40
2021-10-29    123.40
2021-10-22    433.99
2021-10-15    784.99
2021-10-06    678.99
Name: amount, dtype: float64

CodePudding user response：

Assuming you want to groupby month and take mean(Due to ambiguity in questions upper part and lower part):

df["Month"] = pd.to_datetime(df["Date"]).dt.month

df_result = df.groupby("Month").agg(mean_amount = pd.NamedAgg("amount", "mean"))

If you want to groupby dates only, then :

df["Date"] = pd.to_datetime(df["Date"])

df_result = df.groupby("Date").agg(mean_amount = pd.NamedAgg("amount", "mean"))