Pandas - How to replace those exception values with NaN

user_7104897 0
Viewed 75

There is a DataFrame like this:

           cost
0   8762.000000
1   -1
2   7276.000000
3   9574.000000
4   -1
..          ...
59  5508.000000
60  7193.750000
61  5927.333333
62  -1
63  4972.000000

The -1 is the exception value in this case, so how to replace -1 with NaN. And then how to interpolate NaN for replacement.

After that, the DataFrame was cleaned.But there may be some abnormal high and low values of the DataFrame, and then how to interpolate abnormal high and low values for replacement.

data-science pandas python
1 Answers

For replace -1 to interpolate values use replacement to NaNs with Series.interpolate:

df['cost'] = df['cost'].replace(-1, np.nan).interpolate()

If need remove also outliers (abnormal high and low values) you can identify them by Series.quantile and Series.between and replace them to NaNs in Series.where (first replace -1):

print (df)
             cost
0     8762.000000
1       -1.000000
2     7276.000000
3   957400.000000
4       -1.000000
59    5508.000000
60    7193.750000
61      59.333333
62      -1.000000
63    4972.000000

df['cost'] = df['cost'].replace(-1, np.nan)

q_low = df["cost"].quantile(0.01)
q_hi  = df["cost"].quantile(0.99)

m = df["cost"].between(q_low, q_hi, inclusive=False)

df['cost'] = df['cost'].where(m).interpolate()
print (df)
           cost
0   8762.000000
1   8019.000000
2   7276.000000
3   6686.666667
4   6097.333333
59  5508.000000
60  7193.750000
61  6453.166667
62  5712.583333
63  4972.000000
user_29010020
Related