[Python] why using resample().interpolate('spine') create all nan?

I have a dataframe with date gap, for example, 2023 10-02 10:00, 2023 10-02 10:10, then jump to next day of 2023 10-03 10:00,2023 10-03 10:10. Now I am using resample('10min') to create time series every 10min from 2021 01-01 to 2021 01-02, then using interpolate(method="slinear", fill_value="extrapolate", limit_direction="both",order=2) to fill missing date gap, however, the result is all of data become NaN.

so I am trying to understand why interpolate() create all NaN while mean() doesn't?

Here's the code

test_file='test_resample.csv'
df=pd.read_csv(test_file,sep='\t').set_index('Date')
df.tail(13)
100 102 104
Date
10/2/2023 9:43 202.7250 202.9100 203.3800
10/2/2023 9:52 203.3000 203.2250 203.5100
10/2/2023 10:01 203.2875 203.4000 203.7050
10/2/2023 10:10 202.7450 202.8000 203.7125
10/2/2023 10:19 203.0075 202.9925 202.8875
10/3/2023 7:49 205.7625 205.7000 206.1250
10/3/2023 7:59 205.4600 205.8675 205.8925
10/3/2023 8:08 205.7925 205.7325 205.9850
10/3/2023 8:17 205.5525 206.0225 205.8250
10/3/2023 8:26 205.7925 205.8000 206.2675
10/3/2023 8:35 205.8400 205.9250 205.9475
10/3/2023 8:44 204.1450 204.8400 205.0275
10/3/2023 8:53 204.2175 204.0425 204.3575


Now I am doing resampling and use interpolate to fill gap, you can see I got all NaN, why?

rule_1='20min'
df.index=pd.to_datetime(df.index)
df_2=df.resample(rule=rule_1).interpolate(method="slinear", limit_direction="both",fill_value="extrapolate", order=2)
df_2.tail(13)

100 102 104
Date
2023-10-03 04:40:00 NaN NaN NaN
2023-10-03 05:00:00 NaN NaN NaN
2023-10-03 05:20:00 NaN NaN NaN
2023-10-03 05:40:00 NaN NaN NaN
2023-10-03 06:00:00 NaN NaN NaN
2023-10-03 06:20:00 NaN NaN NaN
2023-10-03 06:40:00 NaN NaN NaN
2023-10-03 07:00:00 NaN NaN NaN
2023-10-03 07:20:00 NaN NaN NaN
2023-10-03 07:40:00 NaN NaN NaN
2023-10-03 08:00:00 NaN NaN NaN
2023-10-03 08:20:00 NaN NaN NaN
2023-10-03 08:40:00 NaN NaN Na


if I use mean() instead of interpolate(), some of data are still kept like this

rule_1='20min'
df.index=pd.to_datetime(df.index)
df_1=df.resample(rule=rule_1).mean()
df_1.tail(13)
100 102 104
Date
2023-10-03 04:40:00 NaN NaN NaN
2023-10-03 05:00:00 NaN NaN NaN
2023-10-03 05:20:00 NaN NaN NaN
2023-10-03 05:40:00 NaN NaN NaN
2023-10-03 06:00:00 NaN NaN NaN
2023-10-03 06:20:00 NaN NaN NaN
2023-10-03 06:40:00 NaN NaN NaN
2023-10-03 07:00:00 NaN NaN NaN
2023-10-03 07:20:00 NaN NaN NaN
2023-10-03 07:40:00 205.61125 205.78375 206.00875
2023-10-03 08:00:00 205.67250 205.87750 205.90500
2023-10-03 08:20:00 205.81625 205.86250 206.10750
2023-10-03 08:40:00 204.18125 204.44125 204.69250


So why does interpolate() create all NaN, while mean() is different?

Continue reading...