How do i create a new column in a python dataframe using the equivalent of excel averageifs

Question

I have a python dataframe with the following columns : Year Month Date DayofWeek SaleVolume How do i create a new column AvgbyDay which is the average SaleVolume (avg by Year, month and DayofWeek). I can do this using groupby but i would like to display it in the dataframe. I am looking for the equivalent of the excel averageifs($E:$E,$A:$A,A[i],$B:$B,B[i],$D:$D,D[i])

Answer 1

if you want to add a column with groupby, one way is to use transform:

df['AvgbyDay'] = df.groupby('DayofWeek')['SaleVolume'].transform('mean')
df['AvgbyDay'] = df.groupby(['Year','Month','Date','Day'])['SaleVolume'].transform('mean')

If you want to create a separate column with the average sales volume for Year, Month, Date and Day, you can use a for loop and do:

time_cols = ['Year','Month','Date','DayofWeek']
for col in df[time_cols]:
    df[col+'_average_sales'] = df.groupby(col)['DayofWeek'].transform('mean')

Pick what you need!