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?
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.