Resampling data from 6 min to 5min with nan

Question

I have a linear interpolation problem with nans in my data. I have instantaneous measurements that I want to resample from 6 min intervals to 5 min intervals.

df = pd.DataFrame(zip(['10:00','10:06','10:12','10:18','10:24'],
                     [1, 2, 3, 0.5, 2.5], [0, np.nan, 5, 2.5, 10]),
                 columns=['date','column_a','column_b'])
df['date'] = pd.to_datetime(df['date'], infer_datetime_format=True)
df = df.set_index('date')

print(df)

                    column_a  column_b
date                                   
2023-07-19 10:00:00       1.0       0.0
2023-07-19 10:06:00       2.0       NaN
2023-07-19 10:12:00       3.0       5.0
2023-07-19 10:18:00       0.5       2.5
2023-07-19 10:24:00       2.5      10.0

I used this code but at 10:05 there is supposed to be nan instead of value. Thanks for helping.

print(df.resample('1Min').interpolate(method='linear', limit=5).resample('5Min').asfreq())

                     column_a  column_b
date                                  
2023-07-19 10:00:00  1.000000  0.000000
2023-07-19 10:05:00  1.833333  2.083333 <--- here should be nan
2023-07-19 10:10:00  2.666667       NaN
2023-07-19 10:15:00  1.750000  3.750000
2023-07-19 10:20:00  1.166667  5.000000

Answer 1

Here df_6min is df

df_5min = []

# create list of 5 minute values in an hour
list_min5 = list(range(0, 60, 5))

# for each column get pandas data series of 5 min interval resampled with linear interpolation
for column in df_6min.columns:
    ds_5min = df_6min[column].resample('1Min').interpolate(method='linear').resample('5Min').asfreq()

    # for each index of 6 min interval check if there is nan
    for i in df_6min.index:
        if np.isnan(df_6min.loc[i, column]) == True:

            # if yes replace upper and lower index of 5 min by nan  
            for j in range(len(list_min5)):

                if list_min5[j-1] < i.minute < list_min5[j]:
                    ds_5min.loc[i.replace(minute=list_min5[j-1])] = np.nan
                    ds_5min.loc[i.replace(minute=list_min5[j])] = np.nan

                # if index is 0 minute or 30 minute, get j+1 index instead of j
                if list_min5[j-1] == i.minute:
                    ds_5min.loc[i.replace(minute=list_min5[j-1])] = np.nan
                    ds_5min.loc[i.replace(minute=list_min5[j+1])] = np.nan

    df_5min.append(ds_5min)  

df_5min = pd.concat(df_5min, axis=1)