Chandan Kumar Chandan Kumar - 2 months ago 29
Python Question

Function to calculate value from first row in Python Pandas

Is there any function in pandas to simulate excel formula like '=sum($A$1:A10'(for 10th row), i.e. the formula should take rolling data from 1st row.

Pandas rolling function needs a integer value as window argument.

Answer

The equivalent of =SUM($A$1:A1) in pandas is .expanding().sum() (requires pandas 0.18.0):

ser = pd.Series([1, 2, 3, 4])

ser
Out[3]: 
0    1
1    2
2    3
3    4
dtype: int64

ser.expanding().sum()
Out[4]: 
0     1.0
1     3.0
2     6.0
3    10.0

You can also apply a generic function via apply:

ser.expanding().apply(lambda x: np.percentile(x, 0.9))
Out[12]: 
0    1.000
1    1.009
2    1.018
3    1.027
dtype: float64

Or directly with quantile:

ser.expanding().quantile(0.9)
Out[15]: 
0    1.0
1    1.0
2    2.0
3    3.0
dtype: float64

Note that 90th percentile is equal to 0.9th quantile.