CODEWITHSUNDEEP
pandasdataframe
Benjo
Benjo

Reputation: 147

Pandas - Calculate mean of a selection of cells from another column

Im trying to make a Pandas DataFrame column that has the mean value of the last 10 cells in another column. The example below shows what im trying to achieve (assuming Col1 is 100 up to entry 12):

 Index  Col1    Mean
 .      .       .
 .      .       .
 .      .       .
10      100     100
11      100     100
12      200     110

I've tried this sort of thing, inline and with lambda, but the problem is i can't seem to refernce the 'current' cell that im in:

df['Mean'] = np.mean(df['Col1'].iloc[-10:])

Obviously this code only refers to the same cells. In Excel I would do something like this and the range of 10 values being referenced would move with the cell that Im trying to populate:

=AVERAGE(A1:A10)

Should I be trying to use groupby in some way? Not sure what direction I should be going here.

Upvotes: 0

Views: 137

Answers (1)

dm2
dm2

Reputation: 4275

You can use rolling mean:

df['Mean'] = df['Col1'].rolling(10).mean()

This will leave first 9 rows empty (NaN) as they don't have 10 values to take a mean of, so you you might need to tidy it up depending on how you want these values to be treated.

    Col1    Mean
0   100 
1   100 
2   100 
3   100 
4   100 
5   100 
6   100 
7   100 
8   100 
9   100 100.0
10  100 100.0
11  100 100.0
12  200 110.0
13  200 120.0
14  200 130.0

Upvotes: 1

Related Questions