Python: How To Loop The Code, So It Picks Up The Columns From The Csv File One After The Other?

The question I need help with is: How to loop the code, so it picks up the columns from the csv file one after the other? My excel files, both r and m have 1 column each at the mom

Solution 1:

Since you mention that each column may have varying lengths, then I propose a solution where you read the r and m files line by line, rather than column by column. The reason why is because iterating by varying-lengthed columns would be problematic, but more importantly, it also means we have to load the entire CSV into memory and then iterate on the columns. When we read line by line, we utilize less memory, and we don't have to worry about the varying length of elements per line.

Since we are reading line-by-line, we no longer have to depend on the csv package. We can simply load our files as text files and have our values separated by spaces, commas, or whatever other punctuation you see fit. For the purposes of this example, I will use commas to separate the values.

Let's suppose our r_values file is below, where each line in the file represents an array of values to feed to your functions:

1.22,3.33,3.24,0.32,0.13
2.42,35.43,2.43,87.77,0.98,0.32,32.43,9.56,74.32,2.32
8.78,0.23,64.61,7.23,8.77,76.77

And our m_values file is:

4.23,7.56,98.65,4.87,9.32
3.34,9.45,0.32,86.44,9.45,3.53,0.65,0.43,1.43,65.54
3.34,89.54,8.43,7.54,83.2,8.43

Now in our __name__ == '__main__' block, we load the files, and iterate through the lines, while passing them into the test_risk_metrics and test_risk_adjusted_metrics functions:

if __name__ == "__main__":
    withopen(r'C:\path\to\r_values.csv') as r_file, \
         open(r'C:\path\to\m_values.csv') as m_file:
        for r, m inzip(r_file, m_file):
            # since our lines are separated by commas, we use `split` function# we also cast our values as float
            r = numpy.array([float(x) for x in r.split(',')])
            m = numpy.array([float(x) for x in m.split(',')])

            # diagnostic checkprint(r)  # comment outprint(m)  # comment out# pass to `test_risk_metrics` and `test_risk_adjusted_metrics`
            test_risk_metrics(r, m)
            test_risk_adjusted_metrics(r, m)

Finally, here's the output:

[1.223.333.240.320.13]
[ 4.237.5698.654.879.32]
vol = 1.3866996790942157
beta = 0.9980359303098474
hpm(0.0)_1 = 1.6480000000000001
lpm(0.0)_1 = 0.0
VaR(0.05) = 0.13
test.py:68: RuntimeWarning: divide by zero encountered in double_scalars
  return abs(sum_var / index)
CVaR(0.05) = inf
Drawdown(5) = 0.1299999999999999MaxDrawdown=0.7390300230946882TreynorRatio=1.591125080543938SharpeRatio=1.145165044703315InformationRatio= -0.6443354312329719ExcessVaR=12.215384615384616
Conditional SharpeRatio=0.0
test.py:162: RuntimeWarning: divide by zero encountered in double_scalars
  return(er - rf) / lpm(returns, target, 1)
OmegaRatio= inf
test.py:166: RuntimeWarning: divide by zero encountered in double_scalars
  return(er - rf) / math.sqrt(lpm(returns, target, 2))
SortinoRatio= inf
test.py:170: RuntimeWarning: divide by zero encountered in double_scalars
  return(er - rf) / math.pow(lpm(returns, target, 3), float(1/3))
Kappa 3 Ratio = inf
test.py:174: RuntimeWarning: divide by zero encountered in double_scalars
  return hpm(returns, target, 1) / lpm(returns, target, 1)
Gain LossRatio= inf
test.py:178: RuntimeWarning: divide by zero encountered in double_scalars
  return hpm(returns, target, 1) / math.sqrt(lpm(returns, target, 2))
Upside PotentialRatio= inf
CalmarRatio=2.1487625SterlingRatio=2.993751401271527BurkeRatio=2.647015918149671
[ 2.4235.432.4387.770.980.3232.439.5674.322.32]
[ 3.349.450.3286.449.453.530.650.431.4365.54]
vol = 30.812687581579116
beta = 14.103506402406339
hpm(0.0)_1 = 24.798
lpm(0.0)_1 = 0.0
VaR(0.05) = 0.32
CVaR(0.05) = inf
Drawdown(5) = 0.6140350877192983MaxDrawdown=0.9851301115241635TreynorRatio=1.7540318906636725SharpeRatio=0.8028510961435648InformationRatio=0.20592426973227423ExcessVaR=77.30624999999999
Conditional SharpeRatio=0.0OmegaRatio= inf
SortinoRatio= inf
Kappa 3 Ratio = inf
Gain LossRatio= inf
Upside PotentialRatio= inf
CalmarRatio=25.111403773584907SterlingRatio=78.07671376290729BurkeRatio=50.392183664218216
[ 8.780.2364.617.238.7776.77]
[ 3.3489.548.437.5483.28.43]
vol = 30.714112074998287
beta = -18.831320000339733
hpm(0.0)_1 = 27.731666666666666
lpm(0.0)_1 = 0.0
VaR(0.05) = 0.23
CVaR(0.05) = inf
Drawdown(5) = 6.9519427402863MaxDrawdown=6.9519427402863TreynorRatio= -1.4694491233842049SharpeRatio=0.9009430778626281InformationRatio= -0.09563177846201822ExcessVaR=120.31159420289855
Conditional SharpeRatio=0.0OmegaRatio= inf
SortinoRatio= inf
Kappa 3 Ratio = inf
Gain LossRatio= inf
Upside PotentialRatio= inf
CalmarRatio=3.9804221209001316SterlingRatio=73.39338628531124BurkeRatio=50.28169156965575

Solution 2:

Note sure if this is what you are looking for, but I hope it solves your problem:

# Loop over columns
for i in range(r.shape[1]):
    test_risk_metrics(r[:,i],m[:,i])
    test_risk_adjusted_metrics(r[:,i],m[:,i])

Assuming r and m have the same number of columns.