CODEWITHSUNDEEP
pythonpandas
Mary
Mary

Reputation: 163

How to average certain values of a column based on other columns condition in pandas

I have a data frame like:

Index    Date      Type  Value
0      01/01/2010    A    10
1      01/01/2010    B    15
2      01/01/2010    B    25
3      01/01/2010    A    12
4      01/02/2010    A    9
5      01/02/2010    B    17
6      01/02/2010    B    20
7      01/02/2010    A    8

I want to create a new column such that for each row it average the values based on Type and based on Date so for a given the date all the rows with Type A will have the same average value and same for Type B. For example for 01/01/2010 all tows with type A will be (10+12)/2 = 11 and all rows with type B will be (15+25)/2=20:

Index    Date      Type  Value  Value2
0      01/01/2010    A    10    11
1      01/01/2010    B    15    20
2      01/01/2010    B    25    20
3      01/01/2010    A    12    11
4      01/02/2010    A    9    8.5
5      01/02/2010    B    17   13.5
6      01/02/2010    B    20   13.5
7      01/02/2010    A    8    8.5

Upvotes: 1

Views: 1347

Answers (1)

sacuL
sacuL

Reputation: 51335

You probably want groupby and transform (though I'm not sure in your desired output why type B for 01/02/2010 is 13.5, I think it should be 18.5, i.e. the average of 17 and 20):

df['Value2'] = df.groupby(['Type','Date']).Value.transform('mean')
>>> df
   Index        Date Type  Value  Value2
0      0  01/01/2010    A     10    11.0
1      1  01/01/2010    B     15    20.0
2      2  01/01/2010    B     25    20.0
3      3  01/01/2010    A     12    11.0
4      4  01/02/2010    A      9     8.5
5      5  01/02/2010    B     17    18.5
6      6  01/02/2010    B     20    18.5
7      7  01/02/2010    A      8     8.5

Upvotes: 1

Related Questions