How to write a function that returns an interpolated value (pandas dataframe)?

user_16962327 0
Viewed 268

I have a xls file that I imported as a pandas dataframe. It has NaN values; how do I set-up a function that replaces NaN with the interpolation between the adjacent values? I can't use pd.DataFrame.interpolate or any existing interpolation function since I'm supposed to make my own function.

Here's what I have but I think this is very wrong. Sorry, still very new to Python :(

import pandas as pd
file = pd.read_excel("xls file")

def interpolate(x):
  for i in range(len(x)):
    if x.iloc[i, -1].isnull():
      x.iloc[i,-1] = (((x.iloc[i-1, -1]) + (x.iloc[i+1, -1]))/2)
    else:
      x.iloc[i,-1] = x.iloc[i, -1]

interpolate(file)

So for example the dataframe would originally look like this:

0   1.04
1   0.99
2   NaN
3   1.05
4   1.05

I want it to return:

0   1.04
1   0.99
2   1.02
3   1.05
4   1.05

For this, assume that there are no consecutive NaN entries

pandas interpolation python
1 Answers

This solutions recreates the behaviour of pd.Series.interpolate with default arguments. This is not a beginner-friendly solution and if your question was homework, I am curious to see what your professor was expecting.

I'm using a dataframe with leading, trailing and consecutive nan values. I added a column with interpolated values to compare to my solution. A Series with default sorted range index is required.

import pandas as pd
import numpy as np

np.random.seed(11)
a = np.where(np.random.rand(20) > .5, np.random.uniform(0,10, 20), np.nan)
df = pd.DataFrame({
    'x': a
})
df['x_interp'] = df.x.interpolate()
df

Output

           x  x_interp
0        NaN       NaN
1        NaN       NaN
2        NaN       NaN
3   3.187988  3.187988
4        NaN  2.661738
5        NaN  2.135487
6        NaN  1.609237
7        NaN  1.082987
8   0.556737  0.556737
9   4.797973  4.797973
10  4.016765  4.016765
11       NaN  5.597628
12  7.178492  7.178492
13  6.020641  6.020641
14       NaN  7.755832
15  9.491024  9.491024
16       NaN  9.491024
17       NaN  9.491024
18       NaN  9.491024
19       NaN  9.491024

The approach is to find the slices with nan and the surrounding values. Then fill those slices with linear steps between those surrounding values. The ffill parameter controls if trailing nan will be filled with the last available value.

def interp(ser, ffill=True):
    ser = ser[df.x.notna().idxmax():].copy()
    start = ser.notna() & ser.shift(-1, fill_value=0).isna()
    end = ser.notna() & ser.shift(1, fill_value=0).isna()
    
    for x,y in zip(ser.index[start],ser.index[end]):
        step = (ser.loc[y] - ser.loc[x])/(y - x)
        ser.loc[x:y] = [ser.loc[x] + i * step for i in range(y-x)] + [ser.loc[y]]

    if ffill:
        ser = ser.ffill()
        
    return ser

df['x_new_interp'] = interp(df.x, False)
df['x_new_interp_ffill'] = interp(df.x)
df

Output

           x  x_interp  x_new_interp  x_new_interp_ffill
0        NaN       NaN           NaN                 NaN
1        NaN       NaN           NaN                 NaN
2        NaN       NaN           NaN                 NaN
3   3.187988  3.187988      3.187988            3.187988
4        NaN  2.661738      2.661738            2.661738
5        NaN  2.135487      2.135487            2.135487
6        NaN  1.609237      1.609237            1.609237
7        NaN  1.082987      1.082987            1.082987
8   0.556737  0.556737      0.556737            0.556737
9   4.797973  4.797973      4.797973            4.797973
10  4.016765  4.016765      4.016765            4.016765
11       NaN  5.597628      5.597628            5.597628
12  7.178492  7.178492      7.178492            7.178492
13  6.020641  6.020641      6.020641            6.020641
14       NaN  7.755832      7.755832            7.755832
15  9.491024  9.491024      9.491024            9.491024
16       NaN  9.491024           NaN            9.491024
17       NaN  9.491024           NaN            9.491024
18       NaN  9.491024           NaN            9.491024
19       NaN  9.491024           NaN            9.491024
user_142777220
Related