CODEWITHSUNDEEP
pandas
aozkan
aozkan

Reputation: 919

Pandas Inter-row calculations

I have a DataFrame with daily OHLCV data.

I can calculate the range with:

s['Range'] = s['High'] - s['Low']

Simple. Now I would like to calculate a new column which I've called s['OIR'] (OIR = Open-In-Range)

The ['OIR'] column checks to see if we opened in range and it does this by testing if we opened above yesterdays low and below yesterday's high. I need to reference the previous rows and I'm not quite sure how to do it. The return values would be True/False.

Thanks.

edit: I'm new to StackExchange and Python. Not sure where to drop sample data. Here's an image of the dataframe.

http://i47.tinypic.com/142eb2a.png

Sample Data: Dictionary convert to DataFrame

{'High': {<Timestamp: 2007-03-02 00:00:00>: 1384.5,
  <Timestamp: 2007-03-05 00:00:00>: 1373.0},
 'Last': {<Timestamp: 2007-03-02 00:00:00>: 1365.0,
  <Timestamp: 2007-03-05 00:00:00>: 1351.5},
 'Low': {<Timestamp: 2007-03-02 00:00:00>: 1364.25,
  <Timestamp: 2007-03-05 00:00:00>: 1350.5},
 'OIR': {<Timestamp: 2007-03-02 00:00:00>: False,
  <Timestamp: 2007-03-05 00:00:00>: False},
 'Open': {<Timestamp: 2007-03-02 00:00:00>: 1378.5,
  <Timestamp: 2007-03-05 00:00:00>: 1356.75},
 'Range': {<Timestamp: 2007-03-02 00:00:00>: 20.25,
 <Timestamp: 2007-03-05 00:00:00>: 22.5},
 'Volume': {<Timestamp: 2007-03-02 00:00:00>: 1706906,
 <Timestamp: 2007-03-05 00:00:00>: 1984041}}

Answer: 

s['OIR'] = ((s['Open'] < s['High'].shift(1)) & (s['Open'] > s['Low'].shift(1)))

Upvotes: 4

Views: 912

Answers (1)

Zelazny7
Zelazny7

Reputation: 40628

Referencing previous rows in the manner you suggest is best accomplished with the Series.shift() function:

In [1]: df = DataFrame(randn(10,3),columns=['O','L','H'])

In [2]: df
Out[2]:
          O         L         H
0  0.605412  0.739866 -0.280222
1 -0.707852  0.785651  0.855183
2 -0.087119  0.518924  0.932167
3 -0.913352  0.369825  1.277771
4  0.434593 -2.942903  0.802413
5  0.075669 -0.135914  1.374454
6  1.112062  0.314946  0.882468
7 -0.706078 -0.202243  0.838088
8 -1.668152  0.414585  0.809932
9  1.452937 -0.048245  0.635499

In [3]: df['OIR'] = ((df.L.shift() <= df.O) & (df.O <= df.H.shift()))

In [4]: df
Out[4]:
          O         L         H    OIR
0  0.605412  0.739866 -0.280222  False
1 -0.707852  0.785651  0.855183  False
2 -0.087119  0.518924  0.932167  False
3 -0.913352  0.369825  1.277771  False
4  0.434593 -2.942903  0.802413   True
5  0.075669 -0.135914  1.374454   True
6  1.112062  0.314946  0.882468   True
7 -0.706078 -0.202243  0.838088  False
8 -1.668152  0.414585  0.809932  False
9  1.452937 -0.048245  0.635499  False

Upvotes: 7

Related Questions