我有一个时间索引的 pandas DataFrame,其中一对数字由一个或多个 NaN 分隔:
Time
1970-01-01 00:00:00.000 0.0186458125
1970-01-01 00:00:00.066 -0.0165843889
1970-01-01 00:00:00.068 NaN
1970-01-01 00:00:00.116 NaN
1970-01-01 00:00:00.118 -0.0113886875
1970-01-01 00:00:00.166 -0.0117582778
1970-01-01 00:00:00.168 NaN
1970-01-01 00:00:00.216 NaN
1970-01-01 00:00:00.218 -0.0122501875
1970-01-01 00:00:00.232 -0.0122501875
Name: X, dtype: float64
现在我想要实现的是计算这些数字对的平均值并将结果放入中间时间段,以便结果如下所示:
Time
1970-01-01 00:00:00.000 NaN
1970-01-01 00:00:00.033 0.0010307118
1970-01-01 00:00:00.066 NaN
1970-01-01 00:00:00.068 NaN
1970-01-01 00:00:00.116 NaN
1970-01-01 00:00:00.118 NaN
1970-01-01 00:00:00.142 -0.0115734826
1970-01-01 00:00:00.166 NaN
1970-01-01 00:00:00.168 NaN
1970-01-01 00:00:00.216 NaN
1970-01-01 00:00:00.225 -0.0122501875
1970-01-01 00:00:00.232 NaN
我还计划将时间采样率降低到 1/500 秒的固定频率,所以如果中间 NaN 比上面显示的更多,那也没关系。是否有或多或少简单的 Pandas 方法来做到这一点?
谢谢!
请您参考如下方法:
这更像是一步一步的解决方案,我已经把它分解了
df=df.to_frame('value')
df['key']=df.isnull().cumsum()
df['time']=df.index.map(lambda x : x.timestamp())# make datetime to numeric get the average
newdf=df.groupby('key').agg({'value':'mean','time':'mean'})# using groupby with agg
newdf.time=pd.to_datetime(newdf.time,unit='s')# convert float type datetime back to datetime format
newdf=newdf.set_index('time').value
df.value=np.nan
df=df.value.combine_first(newdf)# combine_frist with new df with older one
df
1970-01-01 00:00:00.000000 NaN
1970-01-01 00:00:00.033000 0.001031
1970-01-01 00:00:00.066000 NaN
1970-01-01 00:00:00.068000 NaN
1970-01-01 00:00:00.116000 NaN
1970-01-01 00:00:00.118000 NaN
1970-01-01 00:00:00.133333 -0.011573
1970-01-01 00:00:00.166000 NaN
1970-01-01 00:00:00.168000 NaN
1970-01-01 00:00:00.216000 NaN
1970-01-01 00:00:00.218000 NaN
1970-01-01 00:00:00.222000 -0.012250
1970-01-01 00:00:00.232000 NaN
Name: value, dtype: float64
