i have dataframe dd1:

     A   B   C  D    E  
  0  V  10   5  18  20       
  1  W   9  18  11  13      
  2  X   8   7  12   5      
  3  Y   7   9   7   8       
  4  Z   6   5   3  90       

I want to add a column 'Result' in dd1 which should return zero if value in column 'E' is greater than values in dd1 columns A,B,C and D else return that value which is greater than dd1 column E

   A   B   C   D   E  Result
0  V  10   5  18  20       0
1  W   9  18  11  13      18
2  X   8   7  12   5      12
3  Y   7   9   7   8       9
4  Z   6   5   3  90       0

2 Answers

3
jezrael On Best Solutions

You can compare by DataFrame.lt columns selected by positions by DataFrame.iloc or list of columns names, check if all Trues per rows by DataFrame.all and set values by numpy.where to new column:

df1 = df.iloc[:, 1:-1]
#for select by columns names
#df1 = df[['B','C','D']]
df['Result'] = np.where(df1.lt(df['E'], axis=0).all(axis=1), 0, df1.max(axis=1))

Another idea is compare by Series.gt with max values per selected columns and then multiple by Series.mul:

s = df.iloc[:, 1:-1].max(axis=1)
df['Result'] = s.gt(df['E']).mul(s)
print (df)
   A   B   C   D   E  Result
0  V  10   5  18  20       0
1  W   9  18  11  13      18
2  X   8   7  12   5      12
3  Y   7   9   7   8       9
4  Z   6   5   3  90       0
0
Erfan On

You can get the max value of each row (axis=1), and compare that to the E column. If this condition is True, we return 0, else we return the max value of that row:

df['Result'] = np.where(df.iloc[:, 1:].max(axis=1).eq(df['E']), # our condition
                        0,                                      # value if true
                        df.iloc[:, 1:].max(axis=1))             # value if false


print(df)
   A   B   C   D   E  Result
0  V  10   5  18  20       0
1  W   9  18  11  13      18
2  X   8   7  12   5      12
3  Y   7   9   7   8       9
4  Z   6   5   3  90       0

Explanation
numpy.where works as followed:

np.where(condition, value if true, value if false)