Wsrtjtyk

Please consider the series:

series = pd.Series(np.random.normal(0, 1, 100))

Here's a moving average df that contains moving averages of this series, with each column corresponding to moving averages using one value in window = [2, 5, 10, 15, 20, 25]

ma_df = pd.DataFrame()

for i in window:

    ma_df['ma'+ str(i)] = series.rolling(window = i).mean()



df = pd.concat([ma_df, series], axis =1)



df.tail()

     ma2   ma5  ma10  ma15  ma20  ma25  series

95 -0.66 -0.15  0.15 -0.02 -0.09 -0.16    0.11

96  0.28 -0.09  0.11  0.02 -0.05 -0.14    0.46

97  0.76 -0.09  0.28  0.08 -0.04 -0.07    1.06

98  1.10  0.27  0.32  0.19  0.02 -0.01    1.13

99  1.03  0.74  0.39  0.32  0.13  0.01    0.94

Question: I wish to create a column df['relative_positions'] which takes different values depending on the relative magnitude of different columns of df. For example,df['positions']=0 if the following conditions are met:

df['relative_positions'] [(df.series > df.ma5) & (df.series > df.ma10) & (df.series < df.ma15) & (df.series > df.ma25) & (df.ma10 > df.ma25) & ....] = 0

or df['positions']= 1 if:

df['relative_positions'] [(df.series < df.ma5) & (df.series< df.ma10) & (df.ma25 < df.ma15) & (df.ma10 < df.ma25) & (df.series < df.ma25) & ....] = 1

or df['positions']= 2 if:

df['relative_positions'] [(df.ma20 < df.ma5) & (df.series > df.ma20) & (df.ma20 < df.ma15) & (df.ma25 < df.series) & (df.series < df.ma5) & ....] = 2

and so on so forth. In each set of conditions, all columns must be compared to all other columns, with both < and > signs.
In short, df['relative_positions'] takes different values (don't matter what) depending on the magnitude of the columns relative to one another (larger or smaller).

Issue:
As you can see, writing these conditions by hand is close to impossible. Is there any way I can automatically populate such lists of permutations of conditions?

I'm sorry if the question is vague. Will be happy to clarify anything.

Your comparisons are yielding nothing, because df.series is always higher than the rolling means in the corresponding columns. Nevertheless, you might need np.where to do your comparisons as:

df['relative_positions'] = np.where((df.series > df.ma5) & (df.series > df.ma10) & (**df.series > df.ma15**) & (df.series > df.ma25) & (df.ma10 > df.ma25), 0, 

                                   np.where((df.series < df.ma5) & (df.series< df.ma10) & (df.ma25 < df.ma15) & (df.ma10 < df.ma25) & (df.series < df.ma25), 1, np.where((df.ma20 < df.ma5) & (df.series > df.ma20) & (df.ma20 < df.ma15) & (df.ma25 < df.series) & (df.series < df.ma5), 2, 'empty')))

For testing purposes I changed (**df.series > df.ma15**) to larger instead of lower. Otherwise you get empty's only.

df.tail()



     ma2   ma5  ma10  ma15  ma20  ma25  series relative_positions

95  94.5  93.0  90.5  88.0  85.5  83.0      95                  0

96  95.5  94.0  91.5  89.0  86.5  84.0      96                  0

97  96.5  95.0  92.5  90.0  87.5  85.0      97                  0

98  97.5  96.0  93.5  91.0  88.5  86.0      98                  0

99  98.5  97.0  94.5  92.0  89.5  87.0      99                  0