python pandas resample how to let last fill last null value?

Question

I have a time series like below,

ts
Out[20]: 
time
2023-08-01 10:31:40.110    6.22
2023-08-01 10:31:43.110    6.23
2023-08-01 10:31:46.111    6.23
2023-08-01 10:31:49.111    6.24
2023-08-01 10:31:52.111    6.24
2023-08-01 10:31:55.117    6.25
2023-08-01 10:31:58.112     NaN
2023-08-01 10:32:01.112     NaN
2023-08-01 10:32:04.117     NaN
2023-08-01 10:32:07.095     NaN

when I do ts.resample('6S', closed='left', label='right').last()

ts.resample('6S', closed='left', label='right').last()
Out[21]: 
time
2023-08-01 10:31:42    6.22
2023-08-01 10:31:48    6.23
2023-08-01 10:31:54    6.24
2023-08-01 10:32:00    6.25
2023-08-01 10:32:06     NaN
2023-08-01 10:32:12     NaN

The problem is I actually want 2023-08-01 10:32:00 to be filled with whatever value previous, no matter it is a numeric or null, in this case, should be NaN How to do that? Great thanks.

To get series above,

pd.Series( [6.22, 6.23, 6.23, 6.24, 6.24, 6.25, np.nan, np.nan, np.nan, np.nan], index = pd.DatetimeIndex(['2023-08-01 10:31:40.110000', '2023-08-01 10:31:43.110000',
               '2023-08-01 10:31:46.111000', '2023-08-01 10:31:49.111000',
               '2023-08-01 10:31:52.111000', '2023-08-01 10:31:55.117000',
               '2023-08-01 10:31:58.112000', '2023-08-01 10:32:01.112000',
               '2023-08-01 10:32:04.117000', '2023-08-01 10:32:07.095000'],
              dtype='datetime64[ns]', name='exchange_time', freq=None))

mozway · Accepted Answer · 2023-08-03T12:08:00.917

last is designed to get the last non-NA value.

Compute the last non-null entry of each column

It looks like you need agg/apply:

ts.resample('6S', closed='left', label='right').agg(lambda x: x.iloc[-1])

ts.resample('6S', closed='left', label='right').apply(lambda x: x.iloc[-1])

Output:

exchange_time
2023-08-01 10:31:42    6.22
2023-08-01 10:31:48    6.23
2023-08-01 10:31:54    6.24
2023-08-01 10:32:00     NaN
2023-08-01 10:32:06     NaN
2023-08-01 10:32:12     NaN
Freq: 6S, dtype: float64

If you want to handle gaps:

(ts.resample('6S', closed='left', label='right')
   .agg(lambda x: x.iloc[-1] if len(x) else np.nan)
)

# or
import numpy as np

(ts.resample('6S', closed='left', label='right')
   .agg(lambda x: np.r_[np.nan, x][-1])
)

# or
(ts.resample('6S', closed='left', label='right')
   .agg(lambda x: next(iter(x.iloc[-1:]), np.nan))
)

Example:

exchange_time
2023-08-01 10:31:42    6.22
2023-08-01 10:31:48    6.23
2023-08-01 10:31:54    6.24
2023-08-01 10:32:00     NaN
2023-08-01 10:32:06     NaN
2023-08-01 10:32:12     NaN
2023-08-01 10:32:18     NaN
2023-08-01 10:32:24     NaN
2023-08-01 10:32:30     NaN
2023-08-01 10:32:36     NaN
2023-08-01 10:32:42     NaN
2023-08-01 10:32:48     NaN
2023-08-01 10:32:54     NaN
2023-08-01 10:33:00     NaN
2023-08-01 10:33:06     NaN
2023-08-01 10:33:12     NaN
Freq: 6S, dtype: float64

Alternative input:

exchange_time
2023-08-01 10:31:40.110    6.22
2023-08-01 10:31:43.110    6.23
2023-08-01 10:31:46.111    6.23
2023-08-01 10:31:49.111    6.24
2023-08-01 10:31:52.111    6.24
2023-08-01 10:31:55.117    6.25
2023-08-01 10:31:58.112     NaN
2023-08-01 10:32:01.112     NaN
2023-08-01 10:32:04.117     NaN
2023-08-01 10:32:07.095     NaN
2023-08-01 10:33:07.095     NaN
dtype: float64

it seems if i change my data a bit to produce a gap in time, `'2023-08-01 10:32:07.095000` to `'2023-08-01 10:34:07.095000` the code will give `IndexError: single positional indexer is out-of-bounds` but I still want the gap be fill with null value every `6s` — tesla1060, Aug 03 '23 at 10:32

python pandas resample how to let last fill last null value?

1 Answers1