熊猫将数据重采样到秒，每~10秒分组一次

Question

假设我有以下数据框：

>>> df
a
2019-04-05 00:00:00  2.0                
2019-04-05 00:00:01  1.0
2019-04-05 00:00:02  NaN
2019-04-05 00:00:03  NaN
2019-04-05 00:00:04  NaN
2019-04-05 00:00:05  NaN
2019-04-05 00:00:06  NaN
2019-04-05 00:00:07  NaN
2019-04-05 00:00:08  3.0
2019-04-05 00:00:09  4.0
2019-04-05 00:00:10  NaN
2019-04-05 00:00:11  NaN
2019-04-05 00:00:12  NaN
2019-04-05 00:00:13  NaN
2019-04-05 00:00:14  NaN
2019-04-05 00:00:15  NaN
2019-04-05 00:00:16  NaN
2019-04-05 00:00:17  NaN
2019-04-05 00:00:18  NaN
2019-04-05 00:00:19  NaN
2019-04-05 00:00:20  4.0
2019-04-05 00:00:21  5.0
2019-04-05 00:00:22  NaN
2019-04-05 00:00:23  NaN
2019-04-05 00:00:24  NaN
2019-04-05 00:00:25  NaN
2019-04-05 00:00:26  6.0
2019-04-05 00:00:27  NaN
2019-04-05 00:00:28  4.0
2019-04-05 00:00:29  NaN
2019-04-05 00:00:30  NaN
2019-04-05 00:00:31  NaN

我希望每 7 秒有 1 个值(假设有一个值，否则只是一个 NaN(，所以数据帧如下所示：

>>> df
a
2019-04-05 00:00:00  2.0                
2019-04-05 00:00:01  NaN
2019-04-05 00:00:02  NaN
2019-04-05 00:00:03  NaN
2019-04-05 00:00:04  NaN
2019-04-05 00:00:05  NaN
2019-04-05 00:00:06  NaN
2019-04-05 00:00:07  NaN
2019-04-05 00:00:08  3.0
2019-04-05 00:00:09  NaN
2019-04-05 00:00:10  NaN
2019-04-05 00:00:11  NaN
2019-04-05 00:00:12  NaN
2019-04-05 00:00:13  NaN
2019-04-05 00:00:14  NaN
2019-04-05 00:00:15  NaN
2019-04-05 00:00:16  NaN
2019-04-05 00:00:17  NaN
2019-04-05 00:00:18  NaN
2019-04-05 00:00:19  NaN
2019-04-05 00:00:20  4.0
2019-04-05 00:00:21  NaN
2019-04-05 00:00:22  NaN
2019-04-05 00:00:23  NaN
2019-04-05 00:00:24  NaN
2019-04-05 00:00:25  NaN
2019-04-05 00:00:26  NaN
2019-04-05 00:00:27  NaN
2019-04-05 00:00:28  4.0
2019-04-05 00:00:29  NaN
2019-04-05 00:00:30  NaN
2019-04-05 00:00:31  NaN

7 秒点是任意的，我实际上大约每分钟取一次值。这是我到目前为止尝试过的：

df = df.resample('7s').first()

但这会产生以下数据帧：

a
2019-04-05 00:00:00  2.0
2019-04-05 00:00:07  3.0
2019-04-05 00:00:14  4.0
2019-04-05 00:00:21  5.0
2019-04-05 00:00:28  4.0

注意：我并不为这两点之间缺少NaN而烦恼，因为它们是暗示的。我只是对时间不满意，因为它每 7 秒强制一个值，因为我只想禁止值彼此在 7 秒内，而不需要每 7 秒一个值。

伊迪丝为清楚起见：

我不想要的数据帧：

a
2019-04-05 00:00:00  2.0
2019-04-05 00:00:07  3.0
2019-04-05 00:00:14  4.0
2019-04-05 00:00:21  5.0
2019-04-05 00:00:28  4.0

我确实想要的数据帧：

>>> df
a
2019-04-05 00:00:00  2.0                
2019-04-05 00:00:01  NaN
2019-04-05 00:00:02  NaN
2019-04-05 00:00:03  NaN
2019-04-05 00:00:04  NaN
2019-04-05 00:00:05  NaN
2019-04-05 00:00:06  NaN
2019-04-05 00:00:07  NaN
2019-04-05 00:00:08  3.0
2019-04-05 00:00:09  NaN
2019-04-05 00:00:10  NaN
2019-04-05 00:00:11  NaN
2019-04-05 00:00:12  NaN
2019-04-05 00:00:13  NaN
2019-04-05 00:00:14  NaN
2019-04-05 00:00:15  NaN
2019-04-05 00:00:16  NaN
2019-04-05 00:00:17  NaN
2019-04-05 00:00:18  NaN
2019-04-05 00:00:19  NaN
2019-04-05 00:00:20  4.0
2019-04-05 00:00:21  NaN
2019-04-05 00:00:22  NaN
2019-04-05 00:00:23  NaN
2019-04-05 00:00:24  NaN
2019-04-05 00:00:25  NaN
2019-04-05 00:00:26  NaN
2019-04-05 00:00:27  NaN
2019-04-05 00:00:28  4.0
2019-04-05 00:00:29  NaN
2019-04-05 00:00:30  NaN
2019-04-05 00:00:31  NaN

或：

>>> df
a
2019-04-05 00:00:00  2.0
2019-04-05 00:00:08  3.0
2019-04-05 00:00:20  4.0
2019-04-05 00:00:28  4.0

Answer 1

您可以对数据帧进行上采样，您非常接近;

df = df.resample('7s').first()
df = df.resample(rule='1s')

这将创建一个数据帧，其中包含 NaN 的数据帧，用于在它添加的秒数上新插入的行。

Answer 2

这不是严格使用熊猫方法，但它可以完成工作。

c = 8
for index, row in df.iterrows():
c += 1
if c > 7 and not(np.isnan(row[0])):
c=0
else:
row[0] = np.nan

应用于df后将返回所需的数据帧。

编辑：

对于包含n列的数据帧，以及每x行的值：

c = [x+1 for i in range(df.shape[1])]
for index, row in df.iterrows():
c = [i+1 for i in c]
for i in range(len(c)):
if c[i] > x and not(np.isnan(row[i])):
c[i] = 0
else:
row[i] = np.nan

第二次编辑：

以上假设每个时间值都有一个NaN。下面适用于数据框中的间隙：

c = [dt.datetime(1,1,1) for i in range(df.shape[1])]
for index, row in df.iterrows():
for i in range(len(c)):
if index.to_pydatetime() - c[i] > dt.timedelta(seconds=x) and not(np.isnan(row[i])):
c[i] = index.to_pydatetime()
else:
row[i] = np.nan

Answer 3

在重新采样之前填充 NA 值怎么样？

df = df.fillna('something').resample('7s').first()

则不会强制使用这些值：

a
2019-04-05 00:00:00 2
2019-04-05 00:00:07 something
2019-04-05 00:00:14 something
2019-04-05 00:00:21 5
2019-04-05 00:00:28 4

请注意，如果您用类似something的字符串填充 NA，它会将整个列转换为object而不是float。因此，如果要维护数据类型，则可以改用df.fillna(0)

Answer 4

df.loc[df.resample("7s").apply(lambda s: s.first_valid_index()).a]

如果您希望用 NaN 填充中间值，则

df1 = df.loc[df.resample("7s").apply(lambda s: s.first_valid_index()).a]
df1.resample("1s").apply(lambda s: None if s.empty else s)

编辑：

根据澄清，我们在这里：

df[df.rolling(window="7s", closed='neither').sum().isna()]

使用上面显示的上采样代码填充 NaN。

编辑-2

我们必须使用行循环，因为发出值的决定取决于先前发出的值：

def f():
skip = 0
for row in df.itertuples():
if skip == 0:
if pd.notna(row.a):
yield row
skip = 7
else:
skip = skip - 1
pd.DataFrame(f())