将 Pandas 数据帧行移动到最近的时间步

阿瑟·奇玛

背景

我有一个由 100 个数千个值组成的大型数据框。数据帧的头部如下所示

df = pd.DataFrame([np.nan, 1100, 1400, np.nan, 14000],
                   index=pd.to_datetime(["2011-05-25 10:00:00",
                                         "2011-05-25 16:40:00",
                                         "2011-05-25 17:06:00",
                                         "2011-05-25 17:10:00",
                                         "2011-05-25 17:24:00"])

                           0
2011-05-25 10:00:00      NaN
2011-05-25 16:40:00   1100.0
2011-05-25 17:06:00   1400.0
2011-05-25 17:10:00      NaN
2011-05-25 17:24:00  14000.0

我想要的是

这些值并不总是以 6 分钟的时间步长记录。我想将未在 6 分钟时间步长记录的值移动到最近的 6 分钟步长。我希望新数据框如下所示

n_df = pd.DataFrame([np.nan, 1100, 1400, np.nan, 14000],
                   index=pd.to_datetime(["2011-05-25 10:00:00",
                                         "2011-05-25 16:42:00",
                                         "2011-05-25 17:06:00",
                                         "2011-05-25 17:12:00",
                                         "2011-05-25 17:24:00"])
                   )

                           0
2011-05-25 10:00:00      NaN
2011-05-25 16:42:00   1100.0
2011-05-25 17:06:00   1400.0
2011-05-25 17:12:00      NaN
2011-05-25 17:24:00  14000.0

对我来说重要的是 n_df 中的所有值都应该是 6 分钟的时间步长，因此属性n_df.index.freq不能是None.

我怎样才能做到这一点。

到目前为止，我for通过迭代df并找到最近的 6 分钟步骤并将值移动/复制到该步骤来使用循环来完成它，但是如果df大于 1000 ，这将非常慢。

我试过的


    index = pd.date_range(df.index[0], end=df.index[-1], freq="6min")
    pydatetime_index = index.to_pydatetime()
    n_df = pd.DataFrame(columns=df.columns, index=index)

    for _idx, i in enumerate(df.index):
        nearest_neighbor = np.abs(pydatetime_index - i.to_pydatetime())
        idx = np.argmin(nearest_neighbor)
        val = df.loc[i]
        n_df.iloc[idx] = val

耶斯列

您可以使用merge_asofwithnearest并指定tolerance参数：

index = pd.date_range(df.index[0], end=df.index[-1], freq="6min")
df1 = pd.DataFrame(index=index)

df2 = pd.merge_asof(df1, 
                    df, 
                    left_index=True, 
                    right_index=True, 
                    direction='nearest', 
                    tolerance=pd.Timedelta('3Min'))
print (df2)
                           0
2011-05-25 10:00:00      NaN
2011-05-25 10:06:00      NaN
2011-05-25 10:12:00      NaN
2011-05-25 10:18:00      NaN
2011-05-25 10:24:00      NaN
                     ...
2011-05-25 17:00:00      NaN
2011-05-25 17:06:00   1400.0
2011-05-25 17:12:00      NaN
2011-05-25 17:18:00      NaN
2011-05-25 17:24:00  14000.0

[75 rows x 1 columns]

或类似DataFrame.reindex：

df2 = df.reindex(index, method='nearest', tolerance=pd.Timedelta('3Min'))
print (df2)
                           0
2011-05-25 10:00:00      NaN
2011-05-25 10:06:00      NaN
2011-05-25 10:12:00      NaN
2011-05-25 10:18:00      NaN
2011-05-25 10:24:00      NaN
                     ...
2011-05-25 17:00:00      NaN
2011-05-25 17:06:00   1400.0
2011-05-25 17:12:00      NaN
2011-05-25 17:18:00      NaN
2011-05-25 17:24:00  14000.0

[75 rows x 1 columns]

或者：

df2 = df.resample('6Min').first()
print (df2)
                           0
2011-05-25 10:00:00      NaN
2011-05-25 10:06:00      NaN
2011-05-25 10:12:00      NaN
2011-05-25 10:18:00      NaN
2011-05-25 10:24:00      NaN
                     ...
2011-05-25 17:00:00      NaN
2011-05-25 17:06:00   1400.0
2011-05-25 17:12:00      NaN
2011-05-25 17:18:00      NaN
2011-05-25 17:24:00  14000.0

[75 rows x 1 columns]

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。