2
$\begingroup$

I would like to make a simple backtest for one of my short-selling strategies. I am using pandas dataframes. So I have a dataframe like the following, that indicates how many positions to open/close every day.

                position_change position_total
2018-01-03      1               1
2018-01-04      0               1
2018-01-05      0               1
2018-01-08      0               1
2018-01-09      0               1
2018-01-10      1               2
2018-01-11      0               2
2018-01-12      0               2
2018-01-16      0               2

I also have a dataframe with the prices of the asset:

            price       short_sell_change       accum_change
2018-01-03  10          1                       1
2018-01-04  9           1,1111111111            1,1111111111
2018-01-05  8           1,125                   1,25
2018-01-08  7           1,1428571429            1,4285714286
2018-01-09  6           1,1666666667            1,6666666667
2018-01-10  5           1,2                     2
2018-01-11  4           1,25                    2,5
2018-01-12  3           1,3333333333            3,3333333333
2018-01-16  2,5         1,2                     4 

The final (net amount) dataframe should be:

            net_amount
2018-01-03  10
2018-01-04  11,1111111111
2018-01-05  12,5
2018-01-08  14,2857142857
2018-01-09  16,6666666667
2018-01-10  25
2018-01-11  31,25
2018-01-12  41,6666666667
2018-01-16  50

This is easy to do with Excel accumulating the previous net_amount with a reference to the above cell and adding the position_chage info: enter image description here

How can this be done in a pandas-way? (Unfortunately I guess the only possible way is iterating over the rows)

$\endgroup$
1
  • 1
    $\begingroup$ There are a few ways this could be done. Could you show your excel formulas that you want to replicate in the net_amount dataframe? $\endgroup$ – amdopt Jul 23 '20 at 13:24
3
$\begingroup$

Assuming your dataframe looks something like this and the name of the dataframe is your_dataframe (I left out one of your columns that wasn't needed for this):

   position  pos_total  price  ss_change
0         1          1   10.0   1.000000
1         0          1    9.0   1.111111
2         0          1    8.0   1.125000
3         0          1    7.0   1.142857
4         0          1    6.0   1.166667
5         1          2    5.0   1.200000
6         0          2    4.0   1.250000
7         0          2    3.0   1.333333
8         0          2    2.5   1.200000
9         0          2    1.0   2.500000

Next, to replicate your Excel formula I defined a function that takes a dataframe and your initial value as inputs and returns the dataframe with a new column and the values you are looking for:

def net_amount(df, initial_value):
    df['net_amount'] = float(initial_value)
    for row in range(1, len(df)):
        df['net_amount'][row] = (df['net_amount'][row-1] *
                                 df['ss_change'][row] +
                                 df['position'][row] *
                                 df['price'][row])
    return df

This is an iterative way of doing it and with large amounts of data it will take a long time. If you have a ton of data there are better ways.

Next just call the function:

net_amount(your_dataframe, 10)

This is what is returned:

   position  pos_total  price  ss_change  net_amount
0         1          1   10.0   1.000000   10.000000
1         0          1    9.0   1.111111   11.111111
2         0          1    8.0   1.125000   12.500000
3         0          1    7.0   1.142857   14.285714
4         0          1    6.0   1.166667   16.666667
5         1          2    5.0   1.200000   25.000000
6         0          2    4.0   1.250000   31.250000
7         0          2    3.0   1.333333   41.666667
8         0          2    2.5   1.200000   50.000000
9         0          2    1.0   2.500000  125.000000

There are plenty of ways to improve upon this and to make it more dynamic if needed. The way it sits relies upon the columns being named a specific way but you can use it as a template and make changes.

Edit:

For additional speed you can incorporate the use numba like this:

import pandas as pd
from numba import jit

@jit
def numba_calc(net, ss, pos, price):
    for i in range(1, len(net)):
        net[i] = (net[i-1] * ss[i] + pos[i] * price[i])
    return net

def net_amount_numba(df, initial_value):
    df['net_amount'] = float(initial_value)
    net = df['net_amount'].to_numpy()
    ss = df['ss_change'].to_numpy()
    pos = df['position'].to_numpy()
    price = df['price'].to_numpy()

    df['net_amount'] = numba_calc(net, ss, pos, price)

    return df

On my machine, this improves performance by ~8x from ~2.4 milliseconds down to ~300 microseconds. Pandas/Numba documentation

$\endgroup$
3
  • $\begingroup$ Thanks for this. I guess there is not any option to get this without iterate over the rows :(. Any example of these "better ways" you mentioned (because yes, my example df is a really simplified version of my real df with multiple colums and tons of rows)? $\endgroup$ – Alfonso_MA Jul 23 '20 at 16:46
  • $\begingroup$ Well, being that you need the prior value to be updated before you can compute the next value, it is an iterative process. When I said better ways, I was referring to having a lot of data. To deal with a data set that is too large for your memory, you may opt for a generator that you can loop over on an 'as needed' basis. $\endgroup$ – amdopt Jul 23 '20 at 17:16
  • $\begingroup$ You could also move it out of pandas into numpy and use numba. Then consolidate it back into a dataframe after you are done with the compuatations. What I showed is just a pandas way of doing it because that is what you asked for. $\endgroup$ – amdopt Jul 23 '20 at 17:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.