How to compare all columns with one column in pandas?

user_825707 0
Viewed 3550

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 ?

pandas dataframe numpy python
4 Answers

I needed to compare some columns to one column (changing some columns and keeping some columns unchanged). I used cs95's answer above and set an index.

  • The columns that you want to keep go in the index (assume col1 and col2).
  • If any column not in the index is greater than col2 then it gets a 1, otherwise 0.

data:

df=pd.DataFrame({'col1':range(10,15), 'col2':range(1,6), 'col3':np.random.randn(5)+3,'col4':np.random.randn(5)+3,'col5':np.random.randn(5)})

    col1    col2    col3        col4        col5
0   10      1       2.741873    2.402274    -1.208714
1   11      2       3.328949    2.692367    -0.813730
2   12      3       5.074692    3.155199    -0.721969
3   13      4       2.725135    3.393867    -2.452344
4   14      5       3.626220    3.002514    -0.897204

code:

import numpy as np

df['col2_copy'] = df['col2']
df=df.set_index(['col1','col2'])
df=df.apply(lambda x: np.where(x > df['col2_copy'], 1, 0), axis=0).reset_index().drop(['col2_copy'],axis = 1)

output:

    col1    col2    col3    col4    col5
0   10      1       1       1       0
1   11      2       1       1       0
2   12      3       1       1       0
3   13      4       0       0       0
4   14      5       0       0       0
user_60443120
Related