Best way to backtest trading bots in pandas, for large DataFrames, without testing the strategy row by row?

user_15073029 0
Viewed 276

What is the fastest way to turn

df[["Strat1", "Close"]]

    Strat1  Close
0   Sell    14185.250000
1   Sell    14185.150391
2   Sell    14157.320312
3   Sell    14184.709961
4   Sell    14185.139648
5   Buy     14171.000000
6   Buy     14166.919922
7   Buy     14150.009766
8   Buy     14136.209961
9   Sell    14131.889648
10  Sell    14129.969727
11  Buy     14135.500000
12  Buy     14135.500000
13  Buy     14135.500000
14  Sell    14135.500000
15  Buy     14135.500000

Into

df[["Strat1", "Close"]]

    Strat1  Close
0   Sell    14185.250000
1           14185.150391
2           14157.320312
3           14184.709961
4           14185.139648
5   Buy     14171.000000
6           14166.919922
7           14150.009766
8           14136.209961
9   Sell    14131.889648
10          14129.969727
11  Buy     14135.500000
12          14135.500000
13          14135.500000
14  Sell    14135.500000
15  Buy     14135.500000

Do you have to iterate through the DataFrame line by line? My dataframe has 1.4 million rows of data so iterating line by line will inevitably take a while, also I have lots of columns "Strat2", "Strat3" that I want to test, further increasing the length of time taken to fix this problem.

pandas dataframe python loops
2 Answers

Try:

df["Strat1"] = (
    df["Strat1"]
    .groupby((df["Strat1"] != df["Strat1"].shift()).cumsum())
    .transform(lambda x: [x.iat[0], *[""] * (len(x) - 1)])
)
print(df)

Prints:

   Strat1         Close
0    Sell  14185.250000
1          14185.150391
2          14157.320312
3          14184.709961
4          14185.139648
5     Buy  14171.000000
6          14166.919922
7          14150.009766
8          14136.209961
9    Sell  14131.889648
10         14129.969727
11    Buy  14135.500000
12         14135.500000
13         14135.500000
14   Sell  14135.500000
15    Buy  14135.500000
user_100359850

if you only need the first buy/sell entry, you can do:

df[df.Strat1 != df.Strat1.shift()]

# if you need the in between value to calculate pnl
df['signal'] = df.Strat1[df.Strat1 != df.Strat1.shift()].reindex(df.index)
user_74607020
Related