How to calculate running total using pandas-CodePudding

I have the data as below.

|stock  |datetime               |AVG_Price      |MF     |NetMF
 ------- ----------------------- --------------- ------- --------
|ABCD   |2022-12-06 09:15:00    |234.50         |237864 |237864
|ABCD   |2022-12-06 09:16:00    |236.55         |357784 |595648
|ABCD   |2022-12-06 09:17:00    |233.23         |334959 |260689
|ABCD   |2022-12-06 09:18:00    |233.23         |498756 |759445
|ABCD   |2022-12-06 09:19:00    |225.23         |456789 |302656
|ABCD   |2022-12-06 09:20:00    |222.23         |678546 |-375890
|ABCD   |2022-12-06 09:21:00    |222.23         |567483 |-943373
|ABCD   |2022-12-06 09:22:00    |220.23         |67483  |-1010856

The desired output in NetMF

I want to calculate Net MF using below conditions.

At datetime 9:15, I will return MF value into Net MF From 9:16 onwards below is the logic i want to follow:

If AVG_Price at 9:16 > AVG_Price at 9:15 then Net MF at 9:16 = Net MF at 9:15 MF at 9:16

If AVG_Price at 9:16 < AVG_Price at 9:15 then Net MF at 9:16 = Net MF at 9:15 - MF at 9:16

If AVG_Price at 9:16 = AVG_Price at 9:15 and Net MF at 9:15 < 0 then Net MF at 9:16 = Net MF at 9:15 - MF at 9:16

If AVG_Price at 9:16 = AVG_Price at 9:15 and Net MF at 9:15 >= 0 then Net MF at 9:16 = Net MF at 9:15 MF at 9:16

This logic continues till the end of the time period.

I need this logic to work for Multiple stock codes and multiple datetimes.

Currently I do not know how to do it in python using pandas data frame

CodePudding user response：

Assuming:

datetime is 1 second apart, and the updates are just based upon the previous row

Code

def compute_net_mf(df):
    net_mf = []    # computed net_mf values

    for avg_price, mf in zip(df['AVG_Price'], df['MF']):   # looping over rows of AVG_Price and MF columns
        if not net_mf:                                     # net_mf is empty list (first iteration)
            net_mf = [mf]                                  # Initialize to first mf value
        else:
            if avg_price > prev_avg_price:                 # AVG_Price < AVG_Price previous
                net_mf.append(net_mf[-1]   mf)
            elif avg_price < prev_avg_price:               # AVG_Price > Avg_Price previous
                net_mf.append(net_mf[-1] - mf)
            elif net_mf[-1] < 0:                           # AVG_Price == prev_avg_price since reached this point and last net_mf < 0
                net_mf.append(net_mf[-1] - mf)
            else:                                          # # AVG_Price == prev_avg_price since reached this point and last net_mf >= 0
                net_mf.append(net_mf[-1]   mf)

        prev_avg_price = avg_price
        
    # Return net_mf column
    return net_mf

Example Create Test Dataframe

import pandas as pd
from io import StringIO

s = '''stock  |datetime               |AVG_Price      |MF
ABCD   |2022-12-06 09:15:00    |234.50         |237864
ABCD   |2022-12-06 09:16:00    |236.55         |357784
ABCD   |2022-12-06 09:17:00    |233.23         |334959
ABCD   |2022-12-06 09:18:00    |233.23         |498756
ABCD   |2022-12-06 09:19:00    |225.23         |456789
ABCD   |2022-12-06 09:20:00    |222.23         |678546
ABCD   |2022-12-06 09:21:00    |222.23         |567483
ABCD   |2022-12-06 09:22:00    |220.23         |67483'''

df = pd.read_csv(StringIO(s),  skipinitialspace = True, sep="\s*\|\s*", engine = "python")

df['net_mf'] = compute_net_mf(df)
print(df)

Output

    stock   datetime            AVG_Price   MF  net_mf
0   ABCD    2022-12-06 09:15:00 234.50  237864  237864
1   ABCD    2022-12-06 09:16:00 236.55  357784  595648
2   ABCD    2022-12-06 09:17:00 233.23  334959  260689
3   ABCD    2022-12-06 09:18:00 233.23  498756  759445
4   ABCD    2022-12-06 09:19:00 225.23  456789  302656
5   ABCD    2022-12-06 09:20:00 222.23  678546  -375890
6   ABCD    2022-12-06 09:21:00 222.23  567483  -943373
7   ABCD    2022-12-06 09:22:00 220.23  67483   -1010856