Pandas dataframe two column condition and replace-CodePudding

I have a data-frame in that I want to put filter or condition for particularly for two column want change the values if values not pass the threshold change to zero, I know that I can do it with convert to separate dataframe do the filter and merge is there any other efficient way I can do, please suggest me.

import pandas as pd

df = pd.DataFrame({"User": ["user1", "user2", "user2", "user3", "user2", "user1"],
                  "Amount": [10.0, 1.0, 8.0, 2, 7.5, 8.0],
                  "Amount2": [1, 5.0, 8.0, 10.5, 0, 8.0]})

output i want >2 threshold

User  Amount  Amount2

user1    10.0      0.0
user2     0.0      5.0
user2     8.0      8.0
user3     0.0     10.5
user2     7.5      0.0
user1     8.0      8.0

CodePudding user response：

You can clip value below 2 to 2 then replace 2 to 0

df[['Amount', 'Amount2']] = df[['Amount', 'Amount2']].clip(lower=2).replace(2, 0)

print(df)

    User  Amount  Amount2
0  user1    10.0      0.0
1  user2     0.0      5.0
2  user2     8.0      8.0
3  user3     0.0     10.5
4  user2     7.5      0.0
5  user1     8.0      8.0

CodePudding user response：

You can use numpy.where to handle all desired columns at once:

# select desired columns (here based on name)
cols = df.filter(like='Amount').columns
# it's also possible to manually set them
# cols = ['Amount', 'Amount2']

df[cols] = np.where(df[cols].le(2), 0, df[cols])  # or .lt(2) for <

updated df:

    User  Amount  Amount2
0  user1    10.0      0.0
1  user2     0.0      5.0
2  user2     8.0      8.0
3  user3     0.0     10.5
4  user2     7.5      0.0
5  user1     8.0      8.0

CodePudding user response：

threshold = 2
df.loc[(df['Amount'] < threshold),'Amount'] = 0
df.loc[(df['Amount2'] < threshold),'Amount2'] = 0

CodePudding user response：

You can use np.where:

import numpy as np
df['Amount'] = np.where(df['Amount'] < 2,0, df['Amount'])
df['Amount2'] = np.where(df['Amount2'] < 2,0, df['Amount2'])

Or if you have only these columns in your dataframe:

df = df.where(df < 2, 0)