python-3.x - 让 pandas merge_asof 只在同一个数据中找到最接近的值

Question

我想合并两个时间序列数据框。第一个 df 中的值应合并到具有最接近（但不完全匹配）日期时间戳的第二个 df 的值中。但是第二个 df 的日期时间戳只能在 IFF 中合并，它是同一日期。

Pandas merge_asof 提供了所需的功能，除了它将合并任何先前日期的值（如果该日期是“最近的”）。下面的示例显示了当前行为和所需行为。

围绕当前行为的任何方法？

import pandas as pd
from datetime import datetime

c = pd.DataFrame([["2017-01-25 00:30:17", "LQE7GNC8O"],
["2017-01-25 00:30:18", "IWK8UOOU1"],
["2017-01-25 00:30:46", "MULAPBFTI"],
["2017-01-25 01:00:08", "RO9N7X31Z"],
["2017-01-25 01:00:08", "SDSFKA3LK"],
["2017-01-25 01:00:42", "YRVASRCNT"],
["2017-01-25 01:00:43", "D5KVPH3H6"],
["2017-01-25 01:00:48", "QZ98EIW2O"],
["2017-01-25 01:01:49", "LBC4F46JG"],
["2017-01-25 02:01:49", "PKGUHA9SS"],
["2017-01-25 03:15:24", "8YD2DFLMZ"]], columns=["datetime", "key"])

d = pd.DataFrame([["2017-01-24 00:00:00", "111111111"],
["2017-01-24 23:00:22", "111111111"],
["2017-01-25 01:00:22", "J64SHBLXH"],
["2017-01-25 01:00:27", "XCKJCJWSR"],
["2017-01-25 01:15:42", "3XSN8RWEY"],
["2017-01-25 02:14:42", "UIQKQL9EH"]], columns=["datetime", "words"])

c["datetime"] = pd.to_datetime(c["datetime"])
d["datetime"] = pd.to_datetime(d["datetime"])

pd.merge_asof(c, d.sort_values("datetime"), on="datetime", allow_exact_matches=False)

YIELDS:
              datetime        key      words
0  2017-01-25 00:30:17  LQE7GNC8O  111111111
1  2017-01-25 00:30:18  IWK8UOOU1  111111111
2  2017-01-25 00:30:46  MULAPBFTI  111111111
3  2017-01-25 01:00:08  RO9N7X31Z  111111111
4  2017-01-25 01:00:08  SDSFKA3LK  111111111
5  2017-01-25 01:00:42  YRVASRCNT  XCKJCJWSR
6  2017-01-25 01:00:43  D5KVPH3H6  XCKJCJWSR
7  2017-01-25 01:00:48  QZ98EIW2O  XCKJCJWSR
8  2017-01-25 01:01:49  LBC4F46JG  XCKJCJWSR
9  2017-01-25 02:01:49  PKGUHA9SS  3XSN8RWEY
10 2017-01-25 03:15:24  8YD2DFLMZ  UIQKQL9EH

DESIRED:
              datetime        key      words
0  2017-01-25 00:30:17  LQE7GNC8O  NaN
1  2017-01-25 00:30:18  IWK8UOOU1  NaN
2  2017-01-25 00:30:46  MULAPBFTI  NaN
3  2017-01-25 01:00:08  RO9N7X31Z  NaN
4  2017-01-25 01:00:08  SDSFKA3LK  NaN
5  2017-01-25 01:00:42  YRVASRCNT  XCKJCJWSR
6  2017-01-25 01:00:43  D5KVPH3H6  XCKJCJWSR
7  2017-01-25 01:00:48  QZ98EIW2O  XCKJCJWSR
8  2017-01-25 01:01:49  LBC4F46JG  XCKJCJWSR
9  2017-01-25 02:01:49  PKGUHA9SS  3XSN8RWEY
10 2017-01-25 03:15:24  8YD2DFLMZ  UIQKQL9EH

标签： python-3.xpandasmerge

Answer 1

在这里你去传递date到by

pd.merge_asof(c.assign(date=c.datetime.dt.date), d.sort_values("datetime").assign(date=d.datetime.dt.date), on="datetime", allow_exact_matches=False , by = 'date')
Out[215]: 
              datetime        key        date      words
0  2017-01-25 00:30:17  LQE7GNC8O  2017-01-25        NaN
1  2017-01-25 00:30:18  IWK8UOOU1  2017-01-25        NaN
2  2017-01-25 00:30:46  MULAPBFTI  2017-01-25        NaN
3  2017-01-25 01:00:08  RO9N7X31Z  2017-01-25        NaN
4  2017-01-25 01:00:08  SDSFKA3LK  2017-01-25        NaN
5  2017-01-25 01:00:42  YRVASRCNT  2017-01-25  XCKJCJWSR
6  2017-01-25 01:00:43  D5KVPH3H6  2017-01-25  XCKJCJWSR
7  2017-01-25 01:00:48  QZ98EIW2O  2017-01-25  XCKJCJWSR
8  2017-01-25 01:01:49  LBC4F46JG  2017-01-25  XCKJCJWSR
9  2017-01-25 02:01:49  PKGUHA9SS  2017-01-25  3XSN8RWEY
10 2017-01-25 03:15:24  8YD2DFLMZ  2017-01-25  UIQKQL9EH