Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Pandas DataFrame replace negative values with latest preceding positive value

Consider a DataFrame such as

df = pd.DataFrame({'a': [1,-2,0,3,-1,2], 
                   'b': [-1,-2,-5,-7,-1,-1], 
                   'c': [-1,-2,-5,4,5,3]})

For each column, how to replace any negative value with the last positive value or zero ? Last here refers from top to bottom for each column. The closest solution noticed is for instance df[df < 0] = 0.

The expected result would be a DataFrame such as

df_res = pd.DataFrame({'a': [1,1,0,3,3,2], 
                       'b': [0,0,0,0,0,0], 
                       'c': [0,0,0,4,5,3]})
like image 281
iris Avatar asked Feb 26 '21 10:02

iris


People also ask

How do you convert a negative number to positive in a data frame?

This can be done by using abs function. For example, if we have a data frame df with many columns and each of them having some negative values then those values can be converted to positive values by just using abs(df).

How do I turn a negative number positive in Python?

In Python, positive numbers can be changed to negative numbers with the help of the in-built method provided in the Python library called abs (). When abs () is used, it converts negative numbers to positive.


3 Answers

You can use DataFrame.mask to convert all values < 0 to NaN then use ffill and fillna:

df = df.mask(df.lt(0)).ffill().fillna(0).convert_dtypes()
   a  b  c
0  1  0  0
1  1  0  0
2  0  0  0
3  3  0  4
4  3  0  5
5  2  0  3
like image 106
Erfan Avatar answered Oct 23 '22 14:10

Erfan


Use pandas where

df.where(df.gt(0)).ffill().fillna(0).astype(int)



   a  b  c
0  1  0  0
1  1  0  0
2  1  0  0
3  3  0  4
4  3  0  5
5  2  0  3
like image 45
wwnde Avatar answered Oct 23 '22 15:10

wwnde


Expected result may obtained with this manipulations:

mask = df >= 0 #creating boolean mask for non-negative values
df_res = (df.where(mask, np.nan) #replace negative values to nan
          .ffill() #apply forward fill for nan values 
          .fillna(0)) # fill rest nan's with zeros
like image 3
Alexander S Avatar answered Oct 23 '22 16:10

Alexander S