1

I have some data that I need to iterate multipliers over. In essence I have three parameters that need to be applied at a High(=1) Medium(=0) and Low(=-1) level. The value of the parameters that are iterated are:

par = 0.25
Skew = 0.20
TS = 0.1

I need to iterate over every combination of p,t and s where each of them are in[-1,0,1] 

25% Par     -1  -1  -1  -1  -1  -1  -1  -1  -1   0   0   0   0  0   0   0   0   0    1   1  1   1   1   1   1   1   1
20% Skew    -1  -1  -1   0   0   0   1   1   1  -1  -1  -1   0  0   0   1   1   1   -1  -1  -1  0   0   0   1   1   1
10% TS      -1   0   1  -1   0   1  -1   0   1  -1  0    1  -1  0   1   -1  0   1   -1   0  1   -1  0   1   -1  0   1

I have been able to get the desired results for p,t,s at permutation(1,1,1) which is the last one in the nested for-loop, i just need to be able to do this for each permutation and also have the particular permutation that was applied be included. I think I am close . . .but that gets me nowhere.

In [3]:

df
Out[3]:
   Quant    Vol
0     13  30.00
1      4  27.50
2    -19  26.00
3     14  23.50
4      4  21.00
5     18  20.00
6     -1  19.00
7     16  18.50
8     -5  18.75
9    -18  19.00
In [4]:

df['par']=.25
df['skew']=.2
df['TS']=.1
In [5]:

df
Out[5]:
   Quant    Vol   par  skew   TS
0     13  30.00  0.25   0.2  0.1
1      4  27.50  0.25   0.2  0.1
2    -19  26.00  0.25   0.2  0.1
3     14  23.50  0.25   0.2  0.1
4      4  21.00  0.25   0.2  0.1
5     18  20.00  0.25   0.2  0.1
6     -1  19.00  0.25   0.2  0.1
7     16  18.50  0.25   0.2  0.1
8     -5  18.75  0.25   0.2  0.1
9    -18  19.00  0.25   0.2  0.1
In [6]:

    for p in [-1,0,1]:
        df['p'] =p
        for s in [-1,0,1]:
            df['s'] = s
            for t in [-1,0,1]:
                df['t'] = t
In [7]:

def iterate(df):
    df['Mod_Vol'] = df['Vol']*(1+df['par']*p)*(1+df['skew']*s)*(1+df['TS']*t)
    return(df)
In [8]:

df = df.groupby([df['Vol']]).apply(iterate)
In [9]:

df
Out[9]:
   Quant    Vol   par  skew   TS  p  s  t  Mod_Vol
0     13  30.00  0.25   0.2  0.1  1  1  1  49.5000
1      4  27.50  0.25   0.2  0.1  1  1  1  45.3750
2    -19  26.00  0.25   0.2  0.1  1  1  1  42.9000
3     14  23.50  0.25   0.2  0.1  1  1  1  38.7750
4      4  21.00  0.25   0.2  0.1  1  1  1  34.6500
5     18  20.00  0.25   0.2  0.1  1  1  1  33.0000
6     -1  19.00  0.25   0.2  0.1  1  1  1  31.3500
7     16  18.50  0.25   0.2  0.1  1  1  1  30.5250
8     -5  18.75  0.25   0.2  0.1  1  1  1  30.9375
9    -18  19.00  0.25   0.2  0.1  1  1  1  31.3500

I need to have each one of these values of vol calculated 27 times and I need to have a value called "Scenario" that contains the identifier of the specific permutation of p,s,t (for example permutation #6 is p=-1,s=0,t=1) so that I can later examine the scenario that gives the greatest impact below are the first 2 values of Vol to illustrate the desired outcome (generated in excel):

Scenario    Quant   Vol     par     skew    TS      p       s       t       Mod_Vol
        1   13.00   30.00   0.25    0.20    0.10    -1.00   -1.00   -1.00   16.20
        2   13.00   30.00   0.25    0.20    0.10    -1.00   -1.00   0.00    18.00
        3   13.00   30.00   0.25    0.20    0.10    -1.00   -1.00   1.00    19.80
        4   13.00   30.00   0.25    0.20    0.10    -1.00   0.00    -1.00   20.25
        5   13.00   30.00   0.25    0.20    0.10    -1.00   0.00    0.00    22.50
        6   13.00   30.00   0.25    0.20    0.10    -1.00   0.00    1.00    24.75
        7   13.00   30.00   0.25    0.20    0.10    -1.00   1.00    -1.00   24.30
        8   13.00   30.00   0.25    0.20    0.10    -1.00   1.00    0.00    27.00
        9   13.00   30.00   0.25    0.20    0.10    -1.00   1.00    1.00    29.70
       10   13.00   30.00   0.25    0.20    0.10    0.00    -1.00   -1.00   21.60
       11   13.00   30.00   0.25    0.20    0.10    0.00    -1.00   0.00    24.00
       12   13.00   30.00   0.25    0.20    0.10    0.00    -1.00   1.00    26.40
       13   13.00   30.00   0.25    0.20    0.10    0.00    0.00    -1.00   27.00
       14   13.00   30.00   0.25    0.20    0.10    0.00    0.00    0.00    30.00
       15   13.00   30.00   0.25    0.20    0.10    0.00    0.00    1.00    33.00
       16   13.00   30.00   0.25    0.20    0.10    0.00    1.00    -1.00   32.40
       17   13.00   30.00   0.25    0.20    0.10    0.00    1.00    0.00    36.00
       18   13.00   30.00   0.25    0.20    0.10    0.00    1.00    1.00    39.60
       19   13.00   30.00   0.25    0.20    0.10    1.00    -1.00   -1.00   27.00
       20   13.00   30.00   0.25    0.20    0.10    1.00    -1.00   0.00    30.00
       21   13.00   30.00   0.25    0.20    0.10    1.00    -1.00   1.00    33.00
       22   13.00   30.00   0.25    0.20    0.10    1.00    0.00    -1.00   33.75
       23   13.00   30.00   0.25    0.20    0.10    1.00    0.00    0.00    37.50
       24   13.00   30.00   0.25    0.20    0.10    1.00    0.00    1.00    41.25
       25   13.00   30.00   0.25    0.20    0.10    1.00    1.00    -1.00   40.50
       26   13.00   30.00   0.25    0.20    0.10    1.00    1.00    0.00    45.00
       27   13.00   30.00   0.25    0.20    0.10    1.00    1.00    1.00    49.50
        1   -4.00   27.50   0.25    0.20    0.10    -1.00   -1.00   -1.00   14.85
        2   -4.00   27.50   0.25    0.20    0.10    -1.00   -1.00   0.00    16.50
        3   -4.00   27.50   0.25    0.20    0.10    -1.00   -1.00   1.00    18.15
        4   -4.00   27.50   0.25    0.20    0.10    -1.00   0.00    -1.00   18.56
        5   -4.00   27.50   0.25    0.20    0.10    -1.00   0.00    0.00    20.63
        6   -4.00   27.50   0.25    0.20    0.10    -1.00   0.00    1.00    22.69
        7   -4.00   27.50   0.25    0.20    0.10    -1.00   1.00    -1.00   22.28
        8   -4.00   27.50   0.25    0.20    0.10    -1.00   1.00    0.00    24.75
        9   -4.00   27.50   0.25    0.20    0.10    -1.00   1.00    1.00    27.23
       10   -4.00   27.50   0.25    0.20    0.10    0.00    -1.00   -1.00   19.80
       11   -4.00   27.50   0.25    0.20    0.10    0.00    -1.00   0.00    22.00
       12   -4.00   27.50   0.25    0.20    0.10    0.00    -1.00   1.00    24.20
       13   -4.00   27.50   0.25    0.20    0.10    0.00    0.00    -1.00   24.75
       14   -4.00   27.50   0.25    0.20    0.10    0.00    0.00    0.00    27.50
       15   -4.00   27.50   0.25    0.20    0.10    0.00    0.00    1.00    30.25
       16   -4.00   27.50   0.25    0.20    0.10    0.00    1.00    -1.00   29.70
       17   -4.00   27.50   0.25    0.20    0.10    0.00    1.00    0.00    33.00
       18   -4.00   27.50   0.25    0.20    0.10    0.00    1.00    1.00    36.30
       19   -4.00   27.50   0.25    0.20    0.10    1.00    -1.00   -1.00   24.75
       20   -4.00   27.50   0.25    0.20    0.10    1.00    -1.00   0.00    27.50
       21   -4.00   27.50   0.25    0.20    0.10    1.00    -1.00   1.00    30.25
       22   -4.00   27.50   0.25    0.20    0.10    1.00    0.00    -1.00   30.94
       23   -4.00   27.50   0.25    0.20    0.10    1.00    0.00    0.00    34.38
       24   -4.00   27.50   0.25    0.20    0.10    1.00    0.00    1.00    37.81
       25   -4.00   27.50   0.25    0.20    0.10    1.00    1.00    -1.00   37.13
       26   -4.00   27.50   0.25    0.20    0.10    1.00    1.00    0.00    41.25
       27   -4.00   27.50   0.25    0.20    0.10    1.00    1.00    1.00    45.38

Thanks for any assistance in this .

John

Improve this question

1 Answer 1

Reset to default
1 
import pandas as pd
import numpy as np

# your data
# ===============================
print(df)

   Quant    Vol
0     13  30.00
1      4  27.50
2    -19  26.00
3     14  23.50
4      4  21.00
5     18  20.00
6     -1  19.00
7     16  18.50
8     -5  18.75
9    -18  19.00


# processing
# ====================================

def func(group):
    # some constants
    par = 0.25
    skew = 0.2
    TS = 0.1
    multi_level_index = pd.MultiIndex.from_product([[-1,0,1],[-1,0,1], [-1,0,1]], names=['p', 's', 't'])
    # construct dataframe
    frame = pd.DataFrame(index=multi_level_index)
    frame['Quant'] = group['Quant'].values[0]
    frame['Vol'] = group['Vol'].values[0]
    frame = frame.reset_index()
    frame['Scenario'] = np.arange(1, 1+len(frame))
    frame = frame.set_index('Scenario')
    # do calculations
    frame['Mod_Vol'] = frame.Vol*(1+par*frame.p)*(1+skew*frame.s)*(1+TS*frame.t)      
    return frame

# do you really want to groupby on `Vol`
# because I saw there are duplicated value of Vol, say 19.00 on row 6 and 9
# below use groupby on the default integer index, which is equivalent to iterating over each row
result = df.groupby(level=0, as_index=False).apply(func).reset_index(level=0, drop=True)

          p  s  t  Quant  Vol  Mod_Vol
Scenario                              
1        -1 -1 -1     13   30   16.200
2        -1 -1  0     13   30   18.000
3        -1 -1  1     13   30   19.800
4        -1  0 -1     13   30   20.250
5        -1  0  0     13   30   22.500
6        -1  0  1     13   30   24.750
7        -1  1 -1     13   30   24.300
8        -1  1  0     13   30   27.000
9        -1  1  1     13   30   29.700
10        0 -1 -1     13   30   21.600
...      .. .. ..    ...  ...      ...
18        0  1  1    -18   19   25.080
19        1 -1 -1    -18   19   17.100
20        1 -1  0    -18   19   19.000
21        1 -1  1    -18   19   20.900
22        1  0 -1    -18   19   21.375
23        1  0  0    -18   19   23.750
24        1  0  1    -18   19   26.125
25        1  1 -1    -18   19   25.650
26        1  1  0    -18   19   28.500
27        1  1  1    -18   19   31.350

[270 rows x 6 columns]


# to select scenario 1, use .loc
result.loc[1]


          p  s  t  Quant    Vol  Mod_Vol
Scenario                                
1        -1 -1 -1     13  30.00   16.200
1        -1 -1 -1      4  27.50   14.850
1        -1 -1 -1    -19  26.00   14.040
1        -1 -1 -1     14  23.50   12.690
1        -1 -1 -1      4  21.00   11.340
1        -1 -1 -1     18  20.00   10.800
1        -1 -1 -1     -1  19.00   10.260
1        -1 -1 -1     16  18.50    9.990
1        -1 -1 -1     -5  18.75   10.125
1        -1 -1 -1    -18  19.00   10.260
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I have a lot to learn . . you make it look easy my friend. Thanks again. John

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.