Interpolate function in Pandas Dataframe

user_12297666 0
Viewed 88

Which are the equations used to interpolate a DataFrame in Pandas?

Reading the following link, I couldn't find anything related to them.

https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.interpolate.html

I need exactly this:

enter image description here

But I'm not sure if the interpolate() function is doing the same thing. If that's the case, is there anyway I can change it to work like that?

EDIT: Example of dataframe:

df = pd.DataFrame([[np.nan, 10, np.nan, 20, 17, np.nan, np.nan, 14, np.nan, 10, np.nan],
                  [5, np.nan, 0, np.nan, np.nan, np.nan, 5, np.nan, 10, np.nan, np.nan],
                  [3, np.nan, np.nan, np.nan, np.nan, np.nan, 2, np.nan, np.nan, np.nan, np.nan],
                  [np.nan, np.nan, np.nan, 3, 4, 5, np.nan, 7, 8, 9, np.nan]],
                  columns=['0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10'])
pandas dataframe interpolation
1 Answers

Unfortunately, the interpolate method is NOT doing exactly that. However, it is still possible to achieve what you want.

Short answer

df.interpolate(limit=1).mul(~(df.shift(-1).isna() & df.isna())).fillna(0)

Step by step explanation

By default, the interpolate method treats the values as equally spaced. So if you input [0,NaN,10,NaN,NaN,16] for instance, you'll get [0,5,10,12,14,16]. This behavior is controlled by the method parameter of the interpolate function. You don't have to change it in your case.

>>> df = pd.DataFrame([np.nan, 10, np.nan, 20, 17, np.nan, np.nan, 14, np.nan, 10, np.nan], columns=["value"])

>>> df
    value
0     NaN
1    10.0
2     NaN
3    20.0
4    17.0
5     NaN
6     NaN
7    14.0
8     NaN
9    10.0
10    NaN

>>> df.interpolate()
    value
0     NaN
1    10.0
2    15.0
3    20.0
4    17.0
5    16.0
6    15.0
7    14.0
8    12.0
9    10.0
10   10.0

Now, the default behavior will replace any NaN, but you don't want consecutives NaNs to be replaced, so you need to use the limit parameter.

This parameter limits the number of consecutives NaN that will be replaced, but crucially, if you set the limit to 1, the first NaN of the consecutive NaNs will still be replaced; you don't want that!

>>> df.interpolate(limit=1)
    value
0     NaN
1    10.0
2    15.0
3    20.0
4    17.0
5    16.0
6     NaN
7    14.0
8    12.0
9    10.0
10   10.0

To get rid of those first values, you need to know which values are NaN and directly followed by another NaN. Use this :

>>> df.shift(-1).isna() & df.isna()
        value 
0   False 
1   False 
2   False 
3   False 
4   False 
5    True 
6   False 
7   False 
8   False 
9   False 
10   True

You can then multiply your dataframe by the negation (~) of this expression. (Note that n*False = 0 and n*True = n)`

>>> df.interpolate(limit=1).mul(~(df.shift(-1).isna() & df.isna()))
    value
0     NaN
1    10.0
2    15.0
3    20.0
4    17.0
5     0.0
6     NaN
7    14.0
8    12.0
9    10.0
10    0.0

Finally, replace the remaining NaN values with 0, using fillna

>>> df.interpolate(limit=1).mul(~(df.shift(-1).isna() & df.isna())).fillna(0)
    value
0     0.0
1    10.0
2    15.0
3    20.0
4    17.0
5     0.0
6     0.0
7    14.0
8    12.0
9    10.0
10    0.0
user_28467110
Related