python - 使用最近的时间连接数据帧并填充特定对象的缺失时间戳

Question

我有两个数据框。

数据帧#1 (df1):

1    object_name     time_epoch_ms      source      data
2    a               1538518822490      source_1     some_data
3    b               1538528822490      source_2     some_data
4    b               1538538822490      source_2     some_data
5    b               1538548822490      source_3     some_data
6    b               1538558822490      source_1     some_data
7    c               1538568822490      source_2     some_data
8    c               1538578822490      source_2     some_data
9    c               1538588822490      source_2     some_data
... etc etc

数据帧#2(df2):

1    object_name     time_epoch_ms      new_data
2    a               1538518722490      x
3    b               1538528822490      y
4    b               1538518922490      z
5    b               1538519922490      a
6    b               1538598822490      b
7    c               1538548822490      c
8    c               1538538822490      c
9    c               1538528822490      d
... etc etc

这两个表中的条目提供了有关不同时间点具有 object_names 的对象的信息。

我想组合这两个表，以便使用 object_name 在数据帧 #1 中指定的特定时间使用的源来扩充数据帧 #2。

问题:两个表中的时间戳不完全匹配。表2中存在的一些时间戳在表1中不存在。但是，它们应该大致对应于小时。表 1 中的一些时间戳缺失，因此“最后可用数据”是最好的。

有没有办法使用 pandas 的合并功能执行以下步骤？

1. 匹配数据帧中的数据，以便 df2 从中获取“源”数据df1 基于 object_name 和 time_stamp 使得 time_stamp 匹配到最接近的时间。
2. 如果特定小时的数据位于 df2 中，但是 不是 df1，则从最后一个可用小时重试源 数据，因此它是根据该数据存在的任何数据“填充”的 对象。
3. 如果 df2 中的对象不在 df1 中，则它会得到“null”

我的方法:

我目前使用 .apply 从 df2 获取每一行，并在 df1 中查找该对象的所有时间戳(如果存在)。然后，我返回最接近的匹配项或 null。

我想知道是否有一种更优雅的方法可以使用 pandas 的合并或连接功能来执行此操作，但我无法理解如何在这种情况下使用它们以及如何处理填充数据和基于小时的匹配(无需进行单独的预处理即可在其中获得小时列)。

Answer 1

总的来说，Pandas 使“等连接”变得容易，但其他类型有点困难。在这种情况下你很幸运，因为有一个很好的方法叫做 merge_asof这应该可以满足您的需要。

关于如何设置数据有点迂腐，但 MWE 是:

from io import StringIO
import pandas as pd

df1 = pd.read_table(StringIO("""1    object_name     time_epoch_ms      source      data
2    a               1538518822490      source_1     some_data_1
3    b               1538528822490      source_2     some_data_2
4    b               1538538822490      source_2     some_data_3
5    b               1538548822490      source_3     some_data_4
6    b               1538558822490      source_1     some_data_5
7    c               1538568822490      source_2     some_data_6
8    c               1538578822490      source_2     some_data_7
9    c               1538588822490      source_2     some_data_8
"""), sep=r"\s+", index_col=0)

df2 = pd.read_table(StringIO("""1    object_name     time_epoch_ms      new_data
2    a               1538518722490      x
3    b               1538528822490      y
4    b               1538518922490      z
5    b               1538519922490      a
6    b               1538598822490      b
7    c               1538548822490      c
8    c               1538538822490      c
9    c               1538528822490      d
"""), sep=r"\s+", index_col=0)

pd.merge_asof(
    df2.sort_values(['time_epoch_ms', 'object_name']),
    df1.sort_values(['time_epoch_ms', 'object_name']),
    by="object_name", on="time_epoch_ms",
    direction='forward',
).sort_values(['object_name', 'time_epoch_ms'])

返回:

  object_name  time_epoch_ms new_data    source         data
0           a  1538518722490        x  source_1  some_data_1
1           b  1538518922490        z  source_2  some_data_2
2           b  1538519922490        a  source_2  some_data_2
3           b  1538528822490        y  source_2  some_data_2
7           b  1538598822490        b       NaN          NaN
4           c  1538528822490        d  source_2  some_data_6
5           c  1538538822490        c  source_2  some_data_6
6           c  1538548822490        c  source_2  some_data_6

参见Pandas equivalent of SQL non-equi JOIN再举个例子。还有merge_ordered但我认为这对你的情况没有帮助。