4

I have a dataframe with three columns (timestamp, temperature and waterlevel). What I want to do is to replace all NaN values in the waterlevel column with interpolated values. For example:

enter image description here

The waterlevel value is always decreasing till it is 0. Therefore, the waterlevel cannot be negative. Also, if the waterlevel is staying the same, the interpolated values should also be the same. Ideally, the stepsize between the interpolated values (within two available waterlevel values) should be the same.

What I have tried so far was:

df['waterlevel'].interpolate(method ='linear', limit_direction ='backward')  # backwards because the waterlevel value is always decreasing.

This does not work. After executing this line, every NaN value has turned to a 0 with the parameter 'forward' and stays NaN with the parameter 'backward'.

and 

df = df['waterlevel'].assign(InterpolateLinear=df.target.interpolate(method='linear'))

Any suggestions on how to solve this?

user7335295
  • 401
  • 2
  • 7
  • 31

1 Answers1

0

I assume NaN is np.nan Object 

import pandas as pd
import numpy as np

df = pd.DataFrame({"waterlevel": ['A',np.nan,np.nan,'D'],"interpolated values":['Ai','Bi','Ci','D']})
print(df)

df.loc[df['waterlevel'].isnull(),'waterlevel'] = df['interpolated values']
print(df)

O/P:

 waterlevel interpolated values
0          A                  Ai
1        NaN                  Bi
2        NaN                  Ci
3          D                   D

  waterlevel interpolated values
0          A                  Ai
1         Bi                  Bi
2         Ci                  Ci
3          D                   D
bharatk
  • 4,202
  • 5
  • 16
  • 30
  • 1
    I think you kind of misunderstood my question. The column 'interpolated values' is NOT part of the dataframe and should just show how the result of the waterlevel should look like :) – user7335295 Jul 12 '19 at 19:26