average of one wrt another or averageifs in python-CodePudding

I have a pandas df as displayed I would like to calculate Avg Rate by DC by Brand column which is a similar to averageif in excel , I have tried methods like groupby mean() but that does not give correct results

CodePudding user response：

Your question is not clear but you may be looking for:

df.groupby(['DC','Brand'])['Rate'].mean()

CodePudding user response：

AVERAGEIF in excel returns a column which is the same size as your original data. So I think you're looking for pandas.transform():

# Sample DF

   Brand  Rate
0      A    45
1      B   100
2      C    28
3      A    92
4      B     2
5      C    79
6      A    48
7      B    97
8      C    72
9      D    14
10     D    16
11     D    64
12     E    85
13     E    22

Result:

df['Avg Rate by Brand'] = df.groupby('Brand')['Rate'].transform('mean')

print(df)

   Brand  Rate  Avg Rate by Brand
0      A    45          61.666667
1      B   100          66.333333
2      C    28          59.666667
3      A    92          61.666667
4      B     2          66.333333
5      C    79          59.666667
6      A    48          61.666667
7      B    97          66.333333
8      C    72          59.666667
9      D    14          31.333333
10     D    16          31.333333
11     D    64          31.333333
12     E    85          53.500000
13     E    22          53.500000