Replace value in a pandas dataframe column by the previous one

Viewed 1002

My code detects outliers in a time series. Which I want to do is to replace the outliers in de first dataframe column with the previous value which is not an outlier.

This code just detect outliers, creating a boolean array where:

  • True means that a value in the dataframe is an outlier
  • False means that a value in the dataframe is not an outlier
series = read_csv('horario_completo.csv', header=None,  squeeze=True)
df=pd.DataFrame(series)
from pandas import rolling_median

consumos=df.iloc[:,0]
df['rolling_median'] = rolling_median(consumos, window=48, center=True).fillna(method='bfill').fillna(method='ffill')
threshold =50
difference = np.abs(consumos - df['rolling_median'])
outlier = difference > threshold

Up to this point, everything works.

The next step I have thought is to create a mask to replace the Truevalues with the previous value of the same column (if this was possible, it would be much faster than making a loop).

I'll try to explain it with a little example:

This is what I have:

index consumo

  0      54
  1      67
  2      98


index outlier 

  0    False
  1    False
  2    True

And this is what I want to do:

index consumo

  0     54
  1     67
  2     67

I think I should create a mask like this:

df.mask(outlier, df.columns=[[0]][i-1],axis=1)

obviosly this IS NOT the way to write it. It just an explanation about how I think it could be done (I'm talking about the [i-1]).

1 Answers
Related