Check if Column exceeding specific value and replace

user_14019928 0
Viewed 46

I use the long list of codes similar to below codes, to check data frame with multiple columns

I need to check if the column has any values greater than Eg. 1000. If >1000 its error value, so make it '0'

b=1000
a = np.array(df['E8'].values.tolist()); df['E8'] = np.where(a > b, 0, a).tolist()
a = np.array(df['E9'].values.tolist()); df['E9'] = np.where(a > b, 0, a).tolist()
a = np.array(df['E10'].values.tolist()); df['E10'] = np.where(a > b, 0, a).tolist()
a = np.array(df['E11'].values.tolist()); df['E11'] = np.where(a > b, 0, a).tolist()
a = np.array(df['E12'].values.tolist()); df['E12'] = np.where(a > b, 0, a).tolist()
a = np.array(df['E13'].values.tolist()); df['E13'] = np.where(a > b, 0, a).tolist()
a = np.array(df['E20'].values.tolist()); df['E20'] = np.where(a > b, 0, a).tolist()
a = np.array(df['E21'].values.tolist()); df['E21'] = np.where(a > b, 0, a).tolist()
a = np.array(df['E29'].values.tolist()); df['E29'] = np.where(a > b, 0, a).tolist()
a = np.array(df['E28'].values.tolist()); df['E28'] = np.where(a > b, 0, a).tolist()
a = np.array(df['E30'].values.tolist()); df['E30'] = np.where(a > b, 0, a).tolist()
a = np.array(df['E31'].values.tolist()); df['E31'] = np.where(a > b, 0, a).tolist()
a = np.array(df['E32'].values.tolist()); df['E32'] = np.where(a > b, 0, a).tolist()
a = np.array(df['E36'].values.tolist()); df['E36'] = np.where(a > b, 0, a).tolist()
a = np.array(df['E37'].values.tolist()); df['E37'] = np.where(a > b, 0, a).tolist()

Is there a simple and efficient way to do it.

pandas numpy python
2 Answers

You can simplify your solution with list of columns names:

np.random.seed(2021)

cols = ['E8','E9','E10', 'E37']
df = pd.DataFrame(np.random.randint(0, 2000, size=(10, 4)), columns=cols) 

b = 1000
df[cols] = np.where(df[cols] > b, 0, df[cols])

print (df)
    E8   E9  E10  E37
0    0    0   57    0
1  621    0   44    0
2  830  669    0  349
3  152    0  502  198
4    0   70  545  613
5    0  257    0    0
6    0    0  410    0
7  944    0   63    0
8  656    0  822  379
9    0    0  632    0
user_29010020

You can use df.mask here.

b = 1000
cols = ['E8','E9','E10', ..., 'E37']
df[cols] = df[cols].mask(df[cols].gt(b), 0)

If you want to do this on entire df

df = df.mask(df.gt(b), 0)
user_124164530
Related