Check if Column exceeding specific value and replace

42
March 03, 2021, at 1:10 PM

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.

Answer 1

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)
Answer 2

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
READ ALSO
Radio and checkbox cannot be checked when inside BootStrap Modal

Radio and checkbox cannot be checked when inside BootStrap Modal

I have a bootstrap modal that needs to have both radio┬┤s and checkboxes, and when I place the below code outside the Modal they work as expected, but as soon as I move them inside the modal I am not able to check/choose themI am using Bootstrap v

47
linear-gradient over Gatsby-Background-Image

linear-gradient over Gatsby-Background-Image

I have an image that I reference from with a Graphql query in my codeOver this image I want to have a linear-gradient, which is located in my css module

79
Tomcat 9 class loading issue with Jaxb-impl& jaxb-runtime

Tomcat 9 class loading issue with Jaxb-impl& jaxb-runtime

Jaxb-api and jaxb-runtime has same package with same class name called RUNTIMEMODELBUILDERJAVA and same interface with same package Navigator interface

62