python - 在两个日期之间合并大型 Pandas 数据框的有效方法

Question

我知道有很多这样的问题，但我似乎找不到相关的答案。假设我有 2 个数据框，如下所示:

df1 = pd.DataFrame(
    {
        "end": [
            "2019-08-31",
            "2019-08-28",
            "2019-09-09",
            "2019-09-08",
            "2019-09-14",
            "2019-09-14",
        ],
        "start": [
            "2019-08-27",
            "2019-08-22",
            "2019-08-04",
            "2019-09-02",
            "2019-09-06",
            "2019-09-10",
        ],
        "id": [1234, 8679, 8679, 1234, 1234, 8679],
    }
)

df2 = pd.DataFrame(
    {
        "timestamp": [
            "2019-08-30 10:00",
            "2019-08-28 10:00",
            "2019-08-27 10:30",
            "2019-08-07 12:00",
            "2019-09-12 10:00",
            "2019-09-11 14:00",
            "2019-08-29 18:00",
        ],
        "id": [1234, 1234, 8679, 1234, 8679, 8679, 1234],
        "val": ["AAAB", "ABBA", "CXXC", "BBAA", "XCXC", "CCXX", "BAAB"],
    }
)

df1["end"] = pd.to_datetime(df1["end"])
df1["start"] = pd.to_datetime(df1["start"])

df2["timestamp"] = pd.to_datetime(df2["timestamp"])

df1.sort_values(by=["end"], inplace=True)
df2.sort_values(by="timestamp", inplace=True)

结果为:

 end       start    id
0  2019-08-31  2019-08-27  1234
1  2019-08-28  2019-08-22  8679
2  2019-09-09  2019-08-04  8679
3  2019-09-08  2019-09-02  1234
4  2019-09-14  2019-09-06  1234
5  2019-09-14  2019-09-10  8679

 timestamp    id   val
0  2019-08-30 10:00  1234  AAAB
1  2019-08-28 10:00  1234  ABBA
2  2019-08-27 10:30  8679  CXXC
3  2019-08-07 12:00  1234  BBAA
4  2019-09-12 10:00  8679  XCXC
5  2019-09-11 14:00  8679  CCXX
6  2019-08-29 18:00  1234  BAAB

按 ID 合并的经典方法是在 df1 中将时间戳记在开始和结束之间是通过合并 id 或虚拟变量和过滤器:

merged_df = pd.merge(df1, df2, how="left", on="id")
merged_df = merged_df.loc[
    (merged_df["timestamp"] >= merged_df["start"])
    & (merged_df["timestamp"] <= merged_df["end"])
]

在其中我得到了我想要的输出:

           end       start    id         timestamp   val
0   2019-08-31  2019-08-27  1234  2019-08-30 10:00  AAAB
1   2019-08-31  2019-08-27  1234  2019-08-28 10:00  ABBA
3   2019-08-31  2019-08-27  1234  2019-08-29 18:00  BAAB
4   2019-08-28  2019-08-22  8679  2019-08-27 10:30  CXXC
7   2019-09-09  2019-08-04  8679  2019-08-27 10:30  CXXC
19  2019-09-14  2019-09-10  8679  2019-09-12 10:00  XCXC
20  2019-09-14  2019-09-10  8679  2019-09-11 14:00  CCXX

我的问题:我需要进行相同的合并并获得相同的结果，但 df1 是 200K 行而 df2 是 600K。

到目前为止我尝试了什么:

• 如上所述，经典的合并和过滤方式会失败，因为初始合并会创建一个巨大的数据帧，使内存过载。

• 我还尝试了以 16GB 内存 PC 结束的 pandasql 方法
  卡住。

• 我在 left join、right join 和 outer join as 的 3 个步骤中尝试了 merge_asof
  解释 here但我运行了一些测试，它似乎总是
  从 df2 返回最多 2 条记录到 df1 中的一行。

任何好的建议将不胜感激!

Answer 1

也许您可以使用 groupby 创建一个函数并使用 pd.IntervalIndex 找到匹配的日期范围，这样您就不必合并 :

def func():
    for x, y in df2.groupby("id"):
        tmp = df1.loc[df1["id"].eq(x)]
        tmp.index = pd.IntervalIndex.from_arrays(tmp['start'], tmp['end'], closed='both')
        y[["start", "end"]] = tmp.loc[y.timestamp, ["start", "end"]].to_numpy()
        yield y

print (pd.concat(func()).sort_index())

            timestamp    id   val      start        end
0 2019-08-30 10:00:00  1234  AAAB 2019-08-27 2019-08-31
1 2019-08-28 10:00:00  1234  ABBA 2019-08-27 2019-08-31
2 2019-08-07 10:30:00  8679  CXXC 2019-08-04 2019-09-09
3 2019-08-27 12:00:00  1234  BBAA 2019-08-27 2019-08-31
4 2019-09-12 10:00:00  8679  XCXC 2019-09-10 2019-09-14
5 2019-09-11 14:00:00  8679  CCXX 2019-09-10 2019-09-14
6 2019-08-29 18:00:00  1234  BAAB 2019-08-27 2019-08-31