Division between two numbers in a Dataframe

Question

I am trying to calculate a percent change between 2 numbers in one column when a signal from another column is triggered.

The trigger can be found with np.where() but what I am having trouble with is the percent change. .pct_change does not work because if you .pct_change(-5) you get 16.03/20.35 and I want the number the opposite way 20.35/16.03. See table below. I have tried returning the array from the index in the np.where and adding it to an .iloc from the 'Close' column but it says I can't use that array to get an .iloc position. Can anyone help me solve this problem. Thank you.

IdxNum |  Close |  Signal (1s)
==============================      
0          21.45        0       
1          21.41        0       
2          21.52        0       
3          21.71        0       
4          20.8         0       
5          20.35        0       
6          20.44        0       
7          16.99        0       
8          17.02        0       
9          16.69        0       
10         16.03    1<<  26.9% <<< 20.35/16.03-1 (df.Close[5]/df.Close[10]-1)

11         15.67        0           
12         15.6         0

So you want to create another column that stores the difference? — Aakash Makwana, Dec 03 '16 at 05:53
yes sorry that was not too clear. I want a new dataframe column that will do that division whenever there is a one 1 in the signal column. pct_change does not work because it calculates df.Close[10]/df.Close[5] and I want to opposite >> df.Close[5]/df.Close[10]-1. thank you — J Westwood, Dec 03 '16 at 15:02
I have updated the answer to match up your requirement, kindly mark it as correct if it serves your requirement — Aakash Makwana, Dec 03 '16 at 15:16

Aakash Makwana · Accepted Answer · 2016-12-04T04:46:58.577

0

You can try this code block:

#Create DataFrame
df = pd.DataFrame({'IdxNum':range(13),
                   'Close':[21.45,21.41,21.52,21.71,20.8,20.35,20.44,16.99,17.02,16.69,16.03,15.67,15.6],
                  'Signal':[0] * 13})
df.ix[10,'Signal']=1

#Create a function that calculates the reqd diff
def cal_diff(row):
    if(row['Signal']==1):
        signal_index = int(row['IdxNum'])
        row['diff'] = df.Close[signal_index-5]/df.Close[signal_index]-1
    return row

#Create a column and apply that difference
df['diff'] = 0
df = df.apply(lambda x:cal_diff(x),axis=1)

In case you don't have IdxNum column, you can use the index to calculate difference

#Create DataFrame
df = pd.DataFrame({
                   'Close':[21.45,21.41,21.52,21.71,20.8,20.35,20.44,16.99,17.02,16.69,16.03,15.67,15.6],
                  'Signal':[0] * 13})
df.ix[10,'Signal']=1

#Calculate the reqd difference
df['diff'] = 0
signal_index = df[df['Signal']==1].index[0]
df.ix[signal_index,'diff'] =  df.Close[signal_index-5]/df.Close[signal_index]-1

edited Dec 04 '16 at 04:46

answered Dec 03 '16 at 06:05

Aakash Makwana

734
5
9

This works well. thanks for your help. Just wondering if I did not have the IdxNum column in the dataframe could you actually create the signal_index location number from the actual Index itself in the DataFrame. Is it possible to turn an Index position into an integer so you could use this to calculate the signal_index. thanks again – J Westwood Dec 03 '16 at 20:43
Yes you can, plz check the edit. Also, mark the answer as correct if it serves your requirement. – Aakash Makwana Dec 04 '16 at 04:44
Yes this works. I clicked on the green check mark which I think means answered as correct. If I am missing something add another comment and I can adjust. thanks for helping me – J Westwood Dec 04 '16 at 16:23

Division between two numbers in a Dataframe

1 Answers1