Pandas apply combined with shift

Question

I am trying to find the relative movement of a currency for each time interval.

I have a table like this:

Date        USD_NOK EUR_USD EUR_NOK  
2020-08-09  9.03267 1.17732 10.60526 
2020-08-10  8.97862 1.17749 10.58188

And a function like this:

def RelativeStrength(table):
    f1 = table.iloc[0][2]
    f2 = table.iloc[0][0]
    t1 = table.iloc[1][2]
    t2 = table.iloc[1][0]
    n = pow(((f1*f2)/(t1*t2)),1/3)
    n1 = t1 * (n/f1)
    n2 = t2 * (n/f2)
    return n, n1, n2

However, the tables contain decades of data with a lot shorter intervals than per day, and I have a lot of these.

The function needs to be run on each row, where the row is to be compared to the row before.

I could easily complete this by a for-loop, but that would probably take days with the datasets I have. Hence, I hope to use apply or something similar.

I have tried something like this:

table.apply(lambda x: [x[0].shift()], axis=1, result_type='expand')

And hoping to get a result looking like this, which I do not:

Date        USD_NOK EUR_USD EUR_NOK  NOK    USD    EUR
2020-08-09  9.03267 1.17732 10.60526 1.0021 0.0876 0.9923
2020-08-10  8.97862 1.17749 10.58188 1.0027 1.0005 0.9967

(The results for 2020-08-09 are made up, as they are dependent on the fx-crosses for 2020-08-08, which I have not posted)

However, it seems like apply only looks at one line at a time. Is it possible to use apply to make calculations on each row, including considering the row before?

Answer 1

Since all your operations (*, / and **) has built-in vectorized support by default, I'd suggest you to do the calculation directly without .apply().

df["f1_t1"] = df["EUR_NOK"].shift() / df["EUR_NOK"]  # f1 over t1
df["f2_t2"] = df["USD_NOK"].shift() / df["USD_NOK"]  # f2 over t2
df["NOK"] = (df["f1_t1"] * df["f2_t2"])**(1/3)
df["USD"] = df["NOK"] / df["f1_t1"]
df["EUR"] = df["NOK"] / df["f2_t2"]

# output
df
         Date  USD_NOK  EUR_USD  ...       NOK       USD       EUR
0  2020-08-09  9.03267  1.17732  ...       NaN       NaN       NaN
1  2020-08-10  8.97862  1.17749  ...  1.002740  1.000529  0.996740

Note that intermediate variables that can be reused multiple times are created instead of simple shift. This part is specific to your formula. The number of operations can thus be further reduced.