Replace negative values in single DataFrame column

Question

If I have the following DataFrame:

>>> df_1 = pd.DataFrame({'A':[-1, 2,  3],
                         'B':[4, -5,  6],
                         'C':[7,  8, -9]}, 
                          index=pd.to_datetime(['2017-01-01 00:01:00', '2017-01-01 00:02:00', '2017-01-02 00:01:00']))

>>> df_1
                      A   B   C
2017-01-01 00:01:00  -1   4   7
2017-01-01 00:02:00   2  -5   8
2017-01-02 00:01:00   3   6  -9

How would I replace all of the negative values in a specific column with something else? For example, if I want to replace all of the negative values in the 'B' column but none of the others with, say 0, the following would be my result.

>>> df_2
                      A   B   C
2017-01-01 00:01:00  -1   4   7
2017-01-01 00:02:00   2   0   8
2017-01-02 00:01:00   3   6  -9

Does df_1['B'][df_1['B'] < 0] = 0 work for you?

Daniel Lenz
– Daniel Lenz

2018-04-05 21:01:52 +00:00
Commented Apr 5, 2018 at 21:01 — Daniel Lenz
– Daniel Lenz, Commented Apr 5, 2018 at 21:01

BENY · Accepted Answer · 2018-04-05 22:36:58Z

10

I think you can using mask

df_1.B=df_1.B.mask(df_1.B.lt(0),0)
df_1
Out[1437]: 
                     A  B  C
2017-01-01 00:01:00 -1  4  7
2017-01-01 00:02:00  2  0  8
2017-01-02 00:01:00  3  6 -9

If we combine with fillna ()Assuming different columns should fill will different value)

df_1.mask(df_1.lt(0)).fillna({'A':9999,'B':0,'C':-9999})
Out[1440]: 
                          A    B       C
2017-01-01 00:01:00  9999.0  4.0     7.0
2017-01-01 00:02:00     2.0  0.0     8.0
2017-01-02 00:01:00     3.0  6.0 -9999.0

answered Apr 5, 2018 at 22:36

BENY

324k22 gold badges176 silver badges250 bronze badges

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

Comments

Abhiram · Accepted Answer · 2021-11-29 11:59:10Z

9

If the values in column B are to be replaced with 0:

df_1.loc[df_1['B']<0,'B']=0

answered Nov 29, 2021 at 11:59

Abhiram

911 silver badge1 bronze badge

Comments

Rahul P · Accepted Answer · 2021-04-27 14:17:41Z

3

You can use numpy.where for this. Add the following to your code to change values of column B for example:

df_1.B = np.where(df_1.B < 0, 0, df_1.B)

print(df_1)

edited Apr 27, 2021 at 14:17

answered Apr 5, 2018 at 21:10

Rahul P

2,7032 gold badges21 silver badges35 bronze badges

Comments

sacuL · Accepted Answer · 2018-04-05 21:34:27Z

2

Another way, very similar to what @bhansa 's answer was going for, but using pandas.Series.lt:

df_1.B[df_1.B.lt(0)] = 0

>>> df_1
                     A  B  C
2017-01-01 00:01:00 -1  4  7
2017-01-01 00:02:00  2  0  8
2017-01-02 00:01:00  3  6 -9

answered Apr 5, 2018 at 21:34

sacuL

51.6k9 gold badges88 silver badges115 bronze badges

Comments

bhansa · Accepted Answer · 2018-04-05 21:11:37Z

-2

Normal conditional will work for you:

df_1[df_1['B'] < 0] = 0


                     A  B   C
2017-01-01 00:01:00 -1  4   7
2017-01-01 00:02:00  2  0   8
2017-01-02 00:01:00  3  6   -9

answered Apr 5, 2018 at 21:11

bhansa

7,5763 gold badges35 silver badges58 bronze badges

2 Comments

mitchute Over a year ago

This will not work, I believe. If I understand correctly, this will mask the second row, then set all of the entries in that row to 0, instead of just the 'B' column.

sacuL Over a year ago

df_1['B'][df_1['B'] < 0] = 0 would fix it

Collectives™ on Stack Overflow

Replace negative values in single DataFrame column

5 Answers 5

Comments

Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related