Calculate Price Change (in last 2 mins) and Price Range (before 2 mins)

Question

I have a dataframe as mentioned below:

row_no,last_price,time
01,110.50,10:09
02,111.60,10:09
03,111.50,10:09
04,112.00,10:09
05,112.00,10:10
06,112.60,10:10
07,112.50,10:10
08,113.10,10:10
09,114.30,10:11
10,114.50,10:11
11,115.70,10:11
12,116.50,10:12
13,116.30,10:12
14,116.20,10:12
15,116.50,10:13
16,117.80,10:13
17,117.90,10:13
18,117.50,10:14
19,118.70,10:14
20,118.90,10:14
21,118.30,10:14
22,118.50,10:15
23,119.60,10:15
24,119.50,10:15
25,119.80,10:15

Row_No Column is added for understanding purpose.

Example for Last Row = 25: Values should be calculated as below:

Need to Calculate "Price_Change_in_last_2mins" in new Column i.e. (row 25 last_price - row 15 last_price)*100/ (row 15 last_price)
Need to Calculate "Price_Range_before_2mins" (period 2 min) in new Column i.e. (max value of last_price from row 05 to row 14) - (min value of last_price from row 05 to row 14)*100/ (min value of last_price from row 05 to row 14)

I want the result like:

row_no,last_price,time,Price_Change_in_last_2mins,Price_Range_before_2mins
01,110.50,10:09,NaN,NaN
02,111.60,10:09,NaN,NaN
03,111.50,10:09,NaN,NaN
04,112.00,10:09,NaN,NaN
05,112.00,10:10,NaN,NaN
06,112.60,10:10,NaN,NaN
07,112.50,10:10,NaN,NaN
08,113.10,10:10,NaN,NaN
09,114.30,10:11,NaN,NaN
10,114.50,10:11,NaN,NaN
11,115.70,10:11,NaN,NaN
12,116.50,10:12,NaN,NaN
13,116.30,10:12,NaN,NaN
14,116.20,10:12,NaN,NaN
15,116.50,10:13,NaN,NaN
16,117.80,10:13,NaN,NaN
17,117.90,10:13,,NaN,NaN
18,117.50,10:14,0.85,4.49
19,118.70,10:14,1.88,4.49
20,118.90,10:14,2.06,4.49
21,118.30,10:14,1.55,4.49
22,118.50,10:15,1.72,3.86
23,119.60,10:15,2.66,3.86
24,119.50,10:15,2.57,3.86
25,119.80,10:15,2.83,3.86

Have you tried to solve these yourself? Can you share with us some attempts at solving it? — yatu, Nov 23 '18 at 10:50
I think it will help you https://stackoverflow.com/questions/12376863/adding-calculated-columns-to-a-dataframe-in-pandas use a.apply(command) — Jony Kim, Nov 23 '18 at 10:56
Sir, I am new to python and unable to handle data with calculating Time Difference. — Pravat, Nov 23 '18 at 11:06
I see what you mean. You might want to specify that the example of rows 15 and 25 that you provide is the calculation for row 25... — yatu, Nov 23 '18 at 11:07
Sir, its calculated as (row 24 last_price - row 15 last_price)*100/ (row 15 last_price) = 0.0258 (round to 2 digits) — Pravat, Nov 23 '18 at 11:56
If i'm not mistaken that is (119.50-116.50)*100/116.5 = 2.575 — yatu, Nov 23 '18 at 11:58
Looks like you are forgetting to multiply by 100 in some cases — yatu, Nov 23 '18 at 12:01
@Pravat, and about Price_Range_before_2mins? Would it not be for row 25 (119.80-116.50)*100/116.5=2.83? As 116.5 is the minimum value of that 2min interval? — yatu, Nov 23 '18 at 12:28

yatu · Accepted Answer · 2018-11-23T14:38:31.950

Here's a way to do it:

def last_2mins(x, df):
    from_time = x.name - datetime.timedelta(minutes=2)
    slice_2min = df.loc[from_time:x.name,:]
    first = slice_2min.iloc[0].last_price
    return (x.last_price - first)*100/first

def before_2mins(x, df):
    from_time = x.name - datetime.timedelta(minutes=2)
    slice_2min = df.loc[from_time:x.name,:]
    max_val = slice_2min.last_price.max()
    min_val = slice_2min.last_price.min()
    return (max_val - min_val)*100/min_val

df = df.set_index(df.time).drop(['time'], axis = 1)
indices = df.loc[df.index.max() - datetime.timedelta(minutes=1):].index.unique()

df_ = df.reset_index()
df_.loc[df_.time.isin(indices), 'last_2mins  '] = \
        df.loc[indices].apply(lambda x: last_2mins(x, df), axis = 1).values
df_.loc[df_.time.isin(indices), 'before_2mins'] = \
        df.loc[indices].apply(lambda x: before_2mins(x, df), axis = 1).values

        time               last_price  last_2mins  before_2mins
0  2018-11-23 10:09:00       110.5         NaN           NaN
1  2018-11-23 10:09:00       111.6         NaN           NaN
2  2018-11-23 10:09:00       111.5         NaN           NaN
3  2018-11-23 10:09:00       112.0         NaN           NaN
4  2018-11-23 10:10:00       112.0         NaN           NaN
5  2018-11-23 10:10:00       112.6         NaN           NaN
6  2018-11-23 10:10:00       112.5         NaN           NaN
7  2018-11-23 10:10:00       113.1         NaN           NaN
8  2018-11-23 10:11:00       114.3         NaN           NaN
9  2018-11-23 10:11:00       114.5         NaN           NaN
10 2018-11-23 10:11:00       115.7         NaN           NaN
11 2018-11-23 10:12:00       116.5         NaN           NaN
12 2018-11-23 10:12:00       116.3         NaN           NaN
13 2018-11-23 10:12:00       116.2         NaN           NaN
14 2018-11-23 10:13:00       116.5         NaN           NaN
15 2018-11-23 10:13:00       117.8         NaN           NaN
16 2018-11-23 10:13:00       117.9         NaN           NaN
17 2018-11-23 10:14:00       117.5    0.858369      2.323580
18 2018-11-23 10:14:00       118.7    1.888412      2.323580
19 2018-11-23 10:14:00       118.9    2.060086      2.323580
20 2018-11-23 10:14:00       118.3    1.545064      2.323580
21 2018-11-23 10:15:00       118.5    1.716738      2.832618
22 2018-11-23 10:15:00       119.6    2.660944      2.832618
23 2018-11-23 10:15:00       119.5    2.575107      2.832618
24 2018-11-23 10:15:00       119.8    2.832618      2.832618

I will dedicate some time later on to add comments. Let me know if you are sure about the your content in Price_Range_before_2mins. On the meantime, hope this helps.

Thanks for the code. Yea I want the percentage range of the price before 2 mins for a period of 2 mins as per the the question i.e. (max value of last_price from row 05 to row 14) - (min value of last_price from row 05 to row 14)*100/ (min value of last_price from row 05 to row 14) — Pravat, Nov 23 '18 at 12:44
Just realized there a change of var name that is did not update. Will edit later — yatu, Nov 23 '18 at 12:50
Sir, in last_2mins function getting error on "first" - Not Defined — Pravat, Nov 23 '18 at 13:10
Yes it's what I was mentioning, will fix as soon as I get to use my laptop — yatu, Nov 23 '18 at 13:18
Okay @Pravat should be okay now. See if you can tweek the function `before_2mins`if it is not doing what you want, as I didn't quite understand what you were asking for — yatu, Nov 23 '18 at 14:39

Calculate Price Change (in last 2 mins) and Price Range (before 2 mins)

1 Answers1