Replace values in specified list of columns based on a condition

Question

The actual use case is that I want to replace all of the values in some named columns with zero whenever they are less than zero, but leave other columns alone. Let's say in the dataframe below, I want to floor all of the values in column a and b to zero, but leave column d alone.

df = pd.DataFrame({'a': [0, -1, 2], 'b': [-3, 2, 1],
                       'c': ['foo', 'goo', 'bar'], 'd' : [1,-2,1]})
df 
   a  b    c  d
0  0 -3  foo  1
1 -1  2  goo -2
2  2  1  bar  1

The second paragraph in the accepted answer to this question: How to replace negative numbers in Pandas Data Frame by zero does provide a workaround, I can just set the datatype of column d to be non-numeric, and then change it back again afterwards:

df['d'] = df['d'].astype(object)
num = df._get_numeric_data()
num[num <0] = 0
df['d'] = df['d'].astype('int64')
df
   a  b    c  d
0  0  0  foo  1
1  0  2  goo -2
2  2  1  bar  1

but this seems really messy, and it means I need to know the list of the columns I don't want to change, rather than the list I do want to change.

Is there a way to just specify the column names directly

Scott Boston · Accepted Answer · 2018-07-29 21:34:21Z

2

You can use mask and column filtering:

df[['a','b']] = df[['a','b']].mask(df<0, 0)
df

Output

   a  b    c  d
0  0  0  foo  1
1  0  2  goo -2
2  2  1  bar  1

answered Jul 29, 2018 at 21:34

Scott Boston

154k15 gold badges160 silver badges207 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

rafaelc · Accepted Answer · 2018-07-29 21:43:00Z

0

Using np.where

cols_to_change = ['a', 'b', 'd']

df.loc[:, cols_to_change] = np.where(df[cols_to_change]<0, 0, df[cols_to_change])

    a   b   c   d
0   0   0   foo 1
1   0   2   goo 0
2   2   1   bar 1

answered Jul 29, 2018 at 21:43

rafaelc

59.4k15 gold badges64 silver badges87 bronze badges

Collectives™ on Stack Overflow

Replace values in specified list of columns based on a condition

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related