CODEWITHSUNDEEP
pythonpandas
Thomas
Thomas

Reputation: 12107

How to follow changes in a dataframe, but only in one direction

I am trying to simulate a trailing stop, used in trading.

Some data:

(input) (output)
price   peg
1000    0995  set a price - 5
1001    0996  following price up
1002    0997  following price up
1001    0997  not following price down
1010    1005  following price up
1012    1007  following price up
1010    1007  not following price down
1006    STOP  the price went below the last peg

The logic is the following:

Is there a Pandas idiomatic way to do this? I've implemented it as a loop, but it is very slow.

This is some code I've done for the loop:

# i is the index at which we take a trade in
# and I want to go through the rest of the dataframe to see if it would
# hit a trailing stop

if direction == +1:  # only long trades in this example
    peg_price = entry_price -5
    for j in range(i + 1, len(df)):
        low = df['low'][j]
        if low <= peg_price:
            date = df['date'][i]

            trade_date.append(df['date'][i])
            trade_exit_date.append(df['date'][j])
            trade_price.append(entry_price)
            trade_exit.append(peg_price)
            trade_profit.append(peg_price - entry_price)
            skip_to = j + 1
        else:
            low = df['high'][j]
            peg_price = max(high - 5, peg_price)

The example is a bit more complex because I need to compare the peg with the 'low' price but update it with the 'high' price; but the idea is there.

Upvotes: 2

Views: 83

Answers (2)

Henry Yik
Henry Yik

Reputation: 22493

IIUC:

data = {"price":[1000,1001,1002,1001,1010,1012,1010,1006]}

df = pd.DataFrame(data)

# first make a column of price-5
df["peg"] = df["price"]-5

# use np.where to check whether price dropped or increased
df["peg"] = np.where(df["price"].shift()>df["price"],df["peg"].shift(),df["peg"])

print (df)

   price     peg
0   1000   995.0
1   1001   996.0
2   1002   997.0
3   1001   997.0
4   1010  1005.0
5   1012  1007.0
6   1010  1007.0
7   1006  1005.0

# Get the index of STOP
print (df[df["peg"].shift()>df["peg"]])

   price     peg
7   1006  1005.0

Upvotes: 1

Umar.H
Umar.H

Reputation: 23099

Here is one way,

the idea is to pass all your logical conditions into true false booleans, we can then iteratively step through the assignments and pass them in. Once we have done that we can find the row where the peg is greater then the price we can then assign STOP if you have data that you need to NA after this you can easily to a logical .loc and assign any values after stop to NA.

for this example I've used your peg column name as counter so we can compare. 

import pandas as pd
peg1 = df['price'].sub(df['price'].shift(1)) == 1 # rolling cumcount

peg2 = df['price'].sub(df['price'].shift(1)) > 1 # -5 these vals

peg3 = df['price'].sub(df['price'].shift(1)) <= -1 # keep as row above.
#assignments

df.loc[peg1,'counter'] = df['counter'].ffill() + peg1.cumsum()
df.loc[peg2,'counter'] = df['price'] - 5
df.loc[peg3,'counter'] = df['counter'].ffill()

df.loc[df['counter'] > df['price'], 'counter'] = 'STOP'

print(df)
   price   peg counter
0   1000  0995     995
1   1001  0996     996
2   1002  0997     997
3   1001  0997     997
4   1010  1005    1005
5   1012  1007    1007
6   1010  1007    1007
7   1006  STOP    STOP

Upvotes: 0

Related Questions