Replacing all negative values in all columns by zero in python

Question

i have below df

date              T1   T2  T3  T4
1-1-2010 00:10    20   -5  4   3
1-1-2010 00:20    85  -78  34  21
1-1-2010 00:30    -45  22  31  75 
1-1-2010 00:40    -6   5   7  -28

I would like to replace negative value into zero from 1st(from T1 columns) columns onwards.

i tried below code:

df.iloc[:,1:].mask(df, 0)

but its also showing '0' values of date columns.

final output should be:

date              T1   T2  T3  T4
1-1-2010 00:10    20   0   4   3
1-1-2010 00:20    85   0   34  21
1-1-2010 00:30    0    22  31  75 
1-1-2010 00:40    0    5   7    0

Welcome to StackOverflow! can you update the df like a list of values? — sam
– sam, Commented Oct 25, 2019 at 6:57

Chris · Accepted Answer · 2019-10-25 08:19:37Z

2

Use pandas.DataFrame.clip:

df.iloc[:, 1:] = df.iloc[:, 1:].clip(0)
print(df)

Output:

             date  T1  T2  T3  T4
0  1-1-2010 00:10  20   0   4   3
1  1-1-2010 00:20  85   0  34  21
2  1-1-2010 00:30   0  22  31  75
3  1-1-2010 00:40   0   5   7   0

Not only clip is faster than mask in your sample, but also in the larger dataset:

# Your sample -> 3x faster
%timeit df.iloc[:, 1:].clip(0)
# 1.74 ms ± 115 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

%timeit df.iloc[:,1:].mask(df.iloc[:,1:] < 0, 0)
# 5.25 ms ± 573 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

# Large Sample -> 1,000,000 elements --> about 30x
large_df = pd.DataFrame(pd.np.random.randint(-5, 5, (1000, 1000)))

%timeit large_df.clip(0)
# 17.2 ms ± 2.44 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

%timeit large_df.mask(large_df< 0, 0)
# 498 ms ± 47 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

answered Oct 25, 2019 at 8:19

Chris

29.8k3 gold badges34 silver badges56 bronze badges

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

1 Comment

henrywongkk Over a year ago

a smart way to use clip !

henrywongkk · Accepted Answer · 2019-10-25 07:15:49Z

1

Try to replace df with df.iloc[:,1:] < 0

df.iloc[:,1:] = df.iloc[:,1:].mask(df.iloc[:,1:] < 0, 0)

edited Oct 25, 2019 at 7:15

answered Oct 25, 2019 at 7:11

henrywongkk

1,9083 gold badges17 silver badges27 bronze badges

Comments

VJAYSLN · Accepted Answer · 2019-10-25 07:16:47Z

0

df = df1.iloc[:,1:]
df = df.mask(df<0 , 0)

Hope this will Helps.!:)

answered Oct 25, 2019 at 7:16

VJAYSLN

4834 silver badges13 bronze badges

1 Comment

henrywongkk Over a year ago

it removes date column from df

Collectives™ on Stack Overflow

Replacing all negative values in all columns by zero in python

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related