How do you calculate this type of difference in dataframe?

user_14250953 0
Viewed 46

So if I have a data-frame like this,

Index  Date       Open     High      Low     Close      
                                                                   
1    2017-04-25  2378.50  2389.25  2377.50  2385.00  
2    2017-04-26  2384.75  2394.75  2382.00  2382.75   
3    2017-04-27  2386.50  2388.75  2378.75  2386.25   
4    2017-04-28  2388.00  2388.25  2378.25  2380.50    
5    2017-05-01  2386.00  2390.75  2380.75  2387.00   
6    2017-05-02  2387.50  2388.75  2381.75  2386.00

How can I calculate Open[2]-Close[1] and do this for every row, i.e. Open[i]-Close[i-1]?

Index  Date       Open     High      Low     Close   DIFF(O-C)    
                                                                   
1    2017-04-25  2378.50  2389.25  2377.50  2385.00  
2    2017-04-26  2384.75  2394.75  2382.00  2382.75   -0.25
3    2017-04-27  2386.50  2388.75  2378.75  2386.25    3.75
..................

I would like a column like above.

I'm not sure if my google-fu is weak or what, but couldn't find an answer to this. Do I need to use a loop for this kind of analysis?

pandas numpy python
2 Answers

Use Series.sub for subtracted shifted rows by Series.shift:

df['DIFF(O-C)'] = df['Open'].sub(df['Close'].shift())
print (df)
         Date     Open     High      Low    Close  DIFF(O-C)
1  2017-04-25  2378.50  2389.25  2377.50  2385.00        NaN
2  2017-04-26  2384.75  2394.75  2382.00  2382.75      -0.25
3  2017-04-27  2386.50  2388.75  2378.75  2386.25       3.75
4  2017-04-28  2388.00  2388.25  2378.25  2380.50       1.75
5  2017-05-01  2386.00  2390.75  2380.75  2387.00       5.50
6  2017-05-02  2387.50  2388.75  2381.75  2386.00       0.50
user_29010020

You can simply add a new column and shift the close time by shift command:

df['shiftedclose'] = df['Close'].shift(periods=1, fill_value=0)

Then you can subtract Open from the new shifted close.

df = df.assign(DIFF= lambda x:['open'] - x['shiftedclose']).drop('shiftedclose', 1)
user_39588780
Related