CODEWITHSUNDEEP
pythonpandas
FrankC
FrankC

Reputation: 11433

Python Pandas - Cummulative Columns

We have this code:

import pandas as pd
depth = {"lastUpdateId":{"0":121305065,"1":121305065,"2":121305065,"3":121305065,"4":121305065,"5":121305065,"6":121305065,"7":121305065,"8":121305065,"9":121305065},"bids":{"0":["0.00152230","8.12000000",[]],"1":["0.00152220","15.74000000",[]],"2":["0.00152210","102.00000000",[]],"3":["0.00152200","59.61000000",[]],"4":["0.00152110","4.44000000",[]],"5":["0.00152100","7.00000000",[]],"6":["0.00152090","165.20000000",[]],"7":["0.00152060","1.92000000",[]],"8":["0.00152030","0.72000000",[]],"9":["0.00152020","267.36000000",[]]},"asks":{"0":["0.00152330","9.86000000",[]],"1":["0.00152460","13.73000000",[]],"2":["0.00152470","109.14000000",[]],"3":["0.00152480","55.54000000",[]],"4":["0.00152500","5.24000000",[]],"5":["0.00152520","5.00000000",[]],"6":["0.00152530","137.45000000",[]],"7":["0.00152550","20.63000000",[]],"8":["0.00152770","892.00000000",[]],"9":["0.00152780","267.36000000",[]]}}
depthdf = pd.DataFrame(depth)
depthdf["CummBidsUSD"] = "??"
depthdf["CummAsksUSD"] = "??"
USDprice = 7000
print(depthdf)

Returning this:

   lastUpdateId                            bids                            asks CummBidsUSD CummAsksUSD
0     121305065    [0.00152230, 8.12000000, []]    [0.00152330, 9.86000000, []]          ??          ??
1     121305065   [0.00152220, 15.74000000, []]   [0.00152460, 13.73000000, []]          ??          ??
2     121305065  [0.00152210, 102.00000000, []]  [0.00152470, 109.14000000, []]          ??          ??
3     121305065   [0.00152200, 59.61000000, []]   [0.00152480, 55.54000000, []]          ??          ??
4     121305065    [0.00152110, 4.44000000, []]    [0.00152500, 5.24000000, []]          ??          ??
5     121305065    [0.00152100, 7.00000000, []]    [0.00152520, 5.00000000, []]          ??          ??
6     121305065  [0.00152090, 165.20000000, []]  [0.00152530, 137.45000000, []]          ??          ??
7     121305065    [0.00152060, 1.92000000, []]   [0.00152550, 20.63000000, []]          ??          ??
8     121305065    [0.00152030, 0.72000000, []]  [0.00152770, 892.00000000, []]          ??          ??
9     121305065  [0.00152020, 267.36000000, []]  [0.00152780, 267.36000000, []]          ??          ??

We want to add the cummulative Bids and Asks in USD.

For that we need to first multiply: assetprice * qty * USDprice

Example for bids in row 0: 0.00152230 * 8.12000000 * 7000

And then add the Cummulative.

How do we do it?

Upvotes: 1

Views: 65

Answers (2)

cs95
cs95

Reputation: 402852

Assuming bids and asks is calculated the same way, call applymap + cumsum:

depthdf[['bids', 'asks']].applymap(
    lambda x: float(x[0]) * float(x[1]) * usdprice).cumsum()

          bids          asks
0    86.527532    105.138166
1   254.243528    251.667472
2  1341.022928   1416.507778
3  1976.107868   2009.319522
4  2023.383656   2065.256522
5  2097.912656   2118.638522
6  3856.681416   3586.205917
7  3877.118280   3806.503372
8  3884.780592  13345.462172
9  6729.865296  16204.770428

Upvotes: 4

Haskar P
Haskar P

Reputation: 420

depthdf['bids'].map(lambda x : float(x[0])*float(x[1])*USDprice) should return a Series for the required calculation for the column bids.

Similarly , the function can be mapped onto the 'asks' column and the cumulative could be calculated as required.

Upvotes: 2

Related Questions