0

I'm trying to create a new column with Monthly weighted average. I tried a few options but the weighted average calculation is taking total of column Amount. for example in March 2021 because there is just one value, WA should be 10*(110/100), most code calculate 10*(110/total Amount column)

Any idea how to do this?

My Data:

Date Column 1 Amount (Expected Output) Monthly WA
01-Jan-2021 20 100 16.78
01-Jan-2021 25 110 16.78
01-Jan-2021 15 120 16.78
01-Jan-2021 30 135 16.78
02-Jan-2021 10 150 16.78
02-Jan-2021 12 160 16.78
02-Jan-2021 11 175 16.78
01-Feb-2021 13 225 20.09
01-Feb-2021 15 220 20.09
01-Feb-2021 17 158 20.09
02-Feb-2021 20 185 20.09
02-Feb-2021 25 170 20.09
02-Feb-2021 35 160 20.09
01-Mar-2021 10 110 10

Here's the code I've tried:

def weight_avg(df, value, weights):
    x = df[value]  
    y = df[weights]  
    return (x*y).sum()/y.sum() <br />

df.groupby(df['Date']).apply(weight_avg, 'Column 1', 'Amount')
ross jim
  • 11
  • 3
  • Does this answer your question? [groupby weighted average and sum in pandas dataframe](https://stackoverflow.com/questions/31521027/groupby-weighted-average-and-sum-in-pandas-dataframe) – Yaakov Bressler Jul 03 '22 at 18:10
  • Your dataset has "01-Jan-2021" and "02-Jan-2021" and hence the groupby is happening on them individual. You need to extract the month and apply a group on it. – teedak8s Jul 03 '22 at 18:10
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. – Community Jul 03 '22 at 22:38
  • I figure it out to get the correct WA values. I created a Month_Year column df['month_year'] = pd.to_datetime(df['Date']).dt.to_period('M') and then applied the groub by with df.groupby(df['month_year']).apply(weight_avg, 'Column 1', 'Amount') any idea how to add this values as a new column? – ross jim Jul 04 '22 at 13:36

1 Answers1

0

If your Date column is not datetime then you can convert it and group by month like this:

df.groupby(df['Date'].to_datetime().dt.month).apply(weight_avg, 'Column 1', 'Amount')
Anynamer
  • 334
  • 1
  • 6