Chandan Kumar - 1 year ago 98

Python Question

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 Source

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.