python - Pandas:通过时间戳获取观察结果

Question

我得到了动态值列表(例如观察值)。它记录了实体的所有值变化(例如显示)。

df
+----+---------------------+-----------------+---------+
|    | time                |   display_index | value   |
|----+---------------------+-----------------+---------|
|  0 | 2017-11-06 13:00:00 |               1 | val1    |
|  1 | 2017-11-06 14:00:00 |               1 | val2    |
|  2 | 2017-11-06 15:00:00 |               1 | val1    |
|  3 | 2017-11-06 13:30:00 |               2 | val3    |
|  4 | 2017-11-06 14:05:00 |               2 | val4    |
|  5 | 2017-11-06 15:30:00 |               2 | val1    |
+----+---------------------+-----------------+---------+

现在我得到了第二个时间戳列表，我对每个显示器当时显示的值感兴趣。 请注意，display_index 2 的第一个时间戳 (13:00) 早于该时间戳的任何值(第一个记录是 13:30)。

df_times
+----+---------------------+-----------------+
|    | time                |   display_index |
|----+---------------------+-----------------|
|  0 | 2017-11-06 13:20:00 |               1 |
|  1 | 2017-11-06 13:40:00 |               1 |
|  2 | 2017-11-06 13:00:00 |               2 |
|  3 | 2017-11-06 14:00:00 |               2 |
+----+---------------------+-----------------+

我尝试计算两个时间戳之间的周期，并选择该周期内最小值的观测值:

df_merged = df_times.merge(df, on='display_index', how='outer', suffixes=['','_measured'])
df_merged['seconds'] = (df_merged.time_measured - df_merged.time).astype('timedelta64[s]')
df_merged['seconds'] = df_merged['seconds'].apply(math.fabs)
df_merged = df_merged.sort_values('seconds').groupby(['time', 'display_index'], as_index=False).first()
print(tabulate(df_merged, headers='keys', tablefmt='psql'))

+----+---------------------+-----------------+---------------------+---------+-----------+
|    | time                |   display_index | time_measured       | value   |   seconds |
|----+---------------------+-----------------+---------------------+---------+-----------|
|  0 | 2017-11-06 13:00:00 |               2 | 2017-11-06 13:30:00 | val3    |      1800 |
|  1 | 2017-11-06 13:20:00 |               1 | 2017-11-06 13:00:00 | val1    |      1200 |
|  2 | 2017-11-06 13:40:00 |               1 | 2017-11-06 14:00:00 | val2    |      1200 |
|  3 | 2017-11-06 14:00:00 |               2 | 2017-11-06 14:05:00 | val4    |       300 |
+----+---------------------+-----------------+---------------------+---------+-----------+

问题是显示 1 和 2 的最后一个值是错误的，因为它们当时仍然显示另一个值。对于显示 1 应该是 val1，对于显示 2 应该是 val3。我实际上要寻找的是在时间戳之前最后看到的观察结果。 那么如何做到这一点？

这是我使用的代码:

import pandas as pd
from tabulate import tabulate
import math

values = [("2017-11-06 13:00", 1, 'val1'),
          ("2017-11-06 14:00", 1, 'val2'),
          ("2017-11-06 15:00", 1, 'val1'),
          ("2017-11-06 13:30", 2, 'val3'),
          ("2017-11-06 14:05", 2, 'val4'),
          ("2017-11-06 15:30", 2, 'val1'),
         ]
labels = ['time', 'display_index', 'value']
df = pd.DataFrame.from_records(values, columns=labels)
df['time'] = pd.to_datetime(df['time']) 
print(tabulate(df, headers='keys', tablefmt='psql'))

values = [("2017-11-06 13:20", 1),
          ("2017-11-06 13:40", 1),
          ("2017-11-06 13:00", 2),
          ("2017-11-06 14:00", 2),
         ]
labels = ['time', 'display_index']
df_times = pd.DataFrame.from_records(values, columns=labels)
df_times['time'] = pd.to_datetime(df_times['time']) 
print(tabulate(df_times, headers='keys', tablefmt='psql'))

df_merged = df_times.merge(df, on='display_index', how='outer', suffixes=['','_measured'])
df_merged['seconds'] = (df_merged.time_measured - df_merged.time).astype('timedelta64[s]')
df_merged['seconds'] = df_merged['seconds'].apply(math.fabs)
df_merged = df_merged.sort_values('seconds').groupby(['time', 'display_index'], as_index=False).first()
print(tabulate(df_merged, headers='keys', tablefmt='psql'))

Answer 1

这是 pd.merge_asof 的完美用例
注意:我认为你弄错了第二行。

# dataframes need to be sorted
df_times = df_times.sort_values(['time', 'display_index'])
df = df.sort_values(['time', 'display_index'])

pd.merge_asof(
    df_times, df.assign(time_measured=df.time),
    on='time', by='display_index', direction='forward'
).assign(seconds=lambda d: d.time_measured.sub(d.time).dt.total_seconds())

                 time  display_index value       time_measured  seconds
0 2017-11-06 13:00:00              2  val3 2017-11-06 13:30:00   1800.0
1 2017-11-06 13:20:00              1  val2 2017-11-06 14:00:00   2400.0
2 2017-11-06 13:40:00              1  val2 2017-11-06 14:00:00   1200.0
3 2017-11-06 14:00:00              2  val4 2017-11-06 14:05:00    300.0

<小时/>

说明

• pd.merge_asof 对于左侧参数中的每一行，它会尝试在右侧参数中查找匹配的行。
• 由于我们传递了 direction='forward' ，它将从左侧参数中的行向前查找并找到下一个值。
• 我需要一种方法来捕获 time_measured 列。由于 merge_asof 占用了 time 列，因此我将其分配为可以按预期使用的不同列。使用 df.assign(time_measured=df.time) 只是复制该列以供以后使用。
• 我再次使用分配。这次分配一个新列秒。使用分配时，您可以传递一个长度相等的数组作为数据帧。您可以传递一个系列，其中的值将根据索引对齐。或者您可以传递一个可调用函数，该可调用函数将传递调用分配的数据帧。这就是我所做的。 lambda 获取调用数据帧并查找这两个日期列中的差异，并将生成的一系列时间增量转换为秒。