python - 对包含时间值的序列进行插值

Question

我有以下数据框:

df = pd.DataFrame(data={
    'time': ['15/04/2019 21:37'] + [-99] * 2 +
      ['15/04/2019 21:40', '16/04/2019 20:00'] + [-99] * 2 + ['16/04/2019 20:03', '16/04/2019 20:04']
            })


0    15/04/2019 21:37
1                 -99
2                 -99
3    15/04/2019 21:40
4    16/04/2019 20:00
5                 -99
6                 -99
7    16/04/2019 20:03
8    16/04/2019 20:04
Name: time, dtype: object

我想要的是一个可以用插值时间值替换缺失值(-99)的函数，以获得:

0    15/04/2019 21:37
1    15/04/2019 21:38
2    15/04/2019 21:39
3    15/04/2019 21:40
4    16/04/2019 20:00
5    15/04/2019 20:01
6    15/04/2019 20:02
7    16/04/2019 20:03
8    16/04/2019 20:04
Name: time, dtype: object

Answer 1

想法是将值转换为 native 格式纳秒，插值并转换回日期时间:

df['time'] = pd.to_datetime(df['time'], format='%d/%m/%Y %H:%M',  errors='coerce')

mask = df['time'].isna()
#or
#mask = df['time'] == -99

arr = np.where(mask, np.nan, df['time'].astype(np.int64))
df['new'] = pd.to_datetime(pd.Series(arr, index=df.index).interpolate(), unit='ns')
print (df)
                 time                 new
0 2019-04-15 21:37:00 2019-04-15 21:37:00
1                 NaT 2019-04-15 21:38:00
2                 NaT 2019-04-15 21:39:00
3 2019-04-15 21:40:00 2019-04-15 21:40:00
4 2019-04-16 20:00:00 2019-04-16 20:00:00
5                 NaT 2019-04-16 20:01:00
6                 NaT 2019-04-16 20:02:00
7 2019-04-16 20:03:00 2019-04-16 20:03:00
8 2019-04-16 20:04:00 2019-04-16 20:04:00