How to compare all columns with one column in pandas?

Question

For the following df

                A       B       ..... THRESHOLD             
DATE                                       
2011-01-01       NaN       NaN  .....      NaN   
2012-01-01 -0.041158 -0.161571  ..... 0.329038   
2013-01-01  0.238156  0.525878  ..... 0.110370   
2014-01-01  0.606738  0.854177  ..... -0.095147   
2015-01-01  0.200166  0.385453  ..... 0.166235 

I have to compare N number of columns like A,B,C .... with THRESHOLD and output the result like

df['A_CALC'] = np.where(df['A'] > df['THRESHOLD'], 1, -1)
df['B_CALC'] = np.where(df['B'] > df['THRESHOLD'], 1, -1)

How to apply the above for all columns (A,B,C ... ) without explicitly writing one statement per column ?

Answer 1

You can use df.apply:

In [670]: df.iloc[:, :-1]\
            .apply(lambda x: np.where(x > df.THRESHOLD, 1, -1), axis=0)\
            .add_suffix('_CALC')
Out[670]: 
            A_CALC  B_CALC
Date                      
2011-01-01      -1      -1
2012-01-01      -1      -1
2013-01-01       1       1
2014-01-01       1       1
2015-01-01       1       1

If THRESHOLD is not your last column, you'd be better off using

df[df.columns.difference(['THRESHOLD'])].apply(lambda x: np.where(x > df.THRESHOLD, 1, -1), axis=0).add_suffix('_CALC')

Answer 2

Or maybe you can try this , by using subtract, should be faster than apply

(df.drop(['THRESHOLD'],axis=1).subtract(df.THRESHOLD,axis=0)>0)\
    .astype(int).replace({0:-1}).add_suffix('_CALC')