Pandas 2500s时间滚动窗口未按预期生成NaN值问题排查

问题现象 #

使用Pandas处理多组三轴加速度时序传感数据时，将rolling滚动计算窗口设置为2500s，预期数据框前2500秒区间、末尾不足窗口长度区间的滚动均值计算结果返回NaN，但实际输出的rolling_mean列仅出现1个NaN值。手动添加min_periods参数后，可实现前2500秒返回NaN的效果，但按逻辑该效果本无需显式设置该参数即可生效，判断当前代码中2500s时间窗口的计算逻辑未正确运行。

完整处理流程如下：

• 批量读取指定路径下8个CSV格式的传感数据
• 按预设时间区间做做掩码筛选
• 重命名列后通过pd.merge_asof按时间索引做30s容差的横向匹配合并
• 基于三轴数据调用np.gradient计算梯度绝对值之和
• 对梯度列执行2500s窗口的滚动均值计算

原始数据样例 #

df_00样例 #

x_axis  y_axis  z_axis
datetime                                       
2022-05-16 12:20:03.719   0.220  -0.010  -0.936
2022-05-16 12:20:28.719   0.209  -0.018  -0.927
2022-05-16 12:20:53.719   0.213  -0.026  -0.936
2022-05-16 12:21:18.719   0.224  -0.022  -0.944

运行结果样例 #

00_x_axis  00_y_axis  ...  07_gradient  07_rolling_mean
datetime                                       ...                              
2022-06-08 16:45:03.719     -0.035     -0.541  ...          NaN              NaN
2022-06-08 16:45:28.719     -0.043     -0.549  ...          NaN              NaN
2022-06-08 16:45:53.719      0.035     -0.549  ...       0.0160         0.016000
2022-06-08 16:46:18.719     -0.024     -0.584  ...       0.0160         0.016000
2022-06-08 16:46:43.719     -0.047     -0.584  ...       0.0000         0.010667

完整实现代码 #

A_start_01 = datetime.datetime(2022,6,8, 16,45,00)
A_end_01 = datetime.datetime(2022,6,8, 23,10,0)
S_start_01 = datetime.datetime(2022,6,9, 6,00,0)
S_end_01 = datetime.datetime(2022,6,11, 6,00,0)
D_start_01 = datetime.datetime(2022,6,11,   17,00,00)
D_end_01 = datetime.datetime(2022,6, 12,   21,0,0)


path = r'C:\Users\#\Pyth\data\flume' 
all_files = glob.glob(path + "/*.csv")

li = []


for filename in all_files:
    df = pd.read_csv(filename, index_col=(0), header=0, skiprows=(30), delimiter = ';', names = ['datetime', 'x_axis', 'y_axis', 'z_axis'], parse_dates=['datetime'])
    li.append(df)

  
df_00 = li[0] 
df_01 = li[1]
df_02 = li[2]
df_03 = li[3]
df_04 = li[4]
df_05 = li[5]
df_06 = li[6]
df_07 = li[7]
   
mask = (df_00.index > A_start_01) & (df_00.index <= D_end_01)
df_00 = df_00.loc[mask] 

mask = (df_01.index > A_start_01) & (df_01.index <= D_end_01)
df_01 = df_01.loc[mask] 

mask = (df_02.index > A_start_01) & (df_02.index <= D_end_01)
df_02 = df_02.loc[mask] 

mask = (df_03.index > A_start_01) & (df_03.index <= D_end_01)
df_03 = df_03.loc[mask] 

mask = (df_04.index > A_start_01) & (df_04.index <= D_end_01)
df_04 = df_04.loc[mask] 

mask = (df_05.index > A_start_01) & (df_05.index <= D_end_01)
df_05 = df_05.loc[mask] 

mask = (df_06.index > A_start_01) & (df_06.index <= D_end_01)

df_06 = df_06.loc[mask]

mask = (df_07.index > A_start_01) & (df_07.index <= D_end_01) 
df_07 = df_07.loc[mask]

df_00.columns = ['00_x_axis', '00_y_axis', '00_z_axis']
df_01.columns = ['01_x_axis', '01_y_axis', '01_z_axis']
df_02.columns = ['02_x_axis', '02_y_axis', '02_z_axis']
df_03.columns = ['03_x_axis', '03_y_axis', '03_z_axis']
df_04.columns = ['04_x_axis', '04_y_axis', '04_z_axis']
df_05.columns = ['05_x_axis', '05_y_axis', '05_z_axis']
df_06.columns = ['06_x_axis', '06_y_axis', '06_z_axis']
df_07.columns = ['07_x_axis', '07_y_axis', '07_z_axis']

df = pd.merge_asof(df_00, df_01, left_index = True, right_index = True, tolerance=pd.Timedelta("30s"))
df = pd.merge_asof(df, df_02, left_index = True, right_index = True, tolerance=pd.Timedelta("30s"))
df = pd.merge_asof(df, df_03, left_index = True, right_index = True, tolerance=pd.Timedelta("30s"))
df = pd.merge_asof(df, df_04, left_index = True, right_index = True, tolerance=pd.Timedelta("30s"))
df = pd.merge_asof(df, df_05, left_index = True, right_index = True, tolerance=pd.Timedelta("30s"))
df = pd.merge_asof(df, df_06, left_index = True, right_index = True, tolerance=pd.Timedelta("30s"))
df = pd.merge_asof(df, df_07, left_index = True, right_index = True, tolerance=pd.Timedelta("30s"))


window = '2500s'
 
df['00_gradient']= np.abs(np.gradient(df['00_x_axis']))+np.abs(np.gradient(df['00_y_axis']))+np.abs(np.gradient(df['00_z_axis']))
df['00_rolling_mean'] = df['00_gradient'].rolling(window).mean()
 
df['01_gradient']= np.abs(np.gradient(df['01_x_axis']))+np.abs(np.gradient(df['01_y_axis']))+np.abs(np.gradient(df['01_z_axis']))
df['01_rolling_mean'] = df['01_gradient'].rolling(window).mean()
 
df['02_gradient']= np.abs(np.gradient(df['02_x_axis']))+np.abs(np.gradient(df['02_y_axis']))+np.abs(np.gradient(df['02_z_axis']))
df['02_rolling_mean'] = df['02_gradient'].rolling(window).mean()
 
df['03_gradient']= np.abs(np.gradient(df['03_x_axis']))+np.abs(np.gradient(df['03_y_axis']))+np.abs(np.gradient(df['03_z_axis']))
df['03_rolling_mean'] = df['03_gradient'].rolling(window).mean()
 
df['04_gradient']= np.abs(np.gradient(df['04_x_axis']))+np.abs(np.gradient(df['04_y_axis']))+np.abs(np.gradient(df['04_z_axis']))
df['04_rolling_mean'] = df['04_gradient'].rolling(window).mean()

df['05_gradient']= np.abs(np.gradient(df['05_x_axis']))+np.abs(np.gradient(df['05_y_axis']))+np.abs(np.gradient(df['05_z_axis']))
df['05_rolling_mean'] = df['05_gradient'].rolling(window).mean()

df['06_gradient']= np.abs(np.gradient(df['06_x_axis']))+np.abs(np.gradient(df['06_y_axis']))+np.abs(np.gradient(df['06_z_axis']))
df['06_rolling_mean'] = df['06_gradient'].rolling(window).mean()

df['07_gradient']= np.abs(np.gradient(df['07_x_axis']))+np.abs(np.gradient(df['07_y_axis']))+np.abs(np.gradient(df['07_z_axis']))
df['07_rolling_mean'] = df['07_gradient'].rolling(window).mean()

print(df)

补充说明 #

手动设置min_periods参数后可让前2500秒返回NaN，但该效果本应无需额外设置即可实现，当前2500s窗口的计算逻辑存在异常，无法正确执行时间窗口滚动计算。

内容的提问来源于stack exchange，提问作者SimonDL