Pandas：比较两个不同 Dataframe 中的日期时间，以找到最接近的匹配

aurhwmvo 于 2022-12-21 发布在其他

关注(0)|答案(2)|浏览(208)

考虑以下两个 Dataframe ：

date,price
2022-07-23 02:00:00,22834.24
2022-07-23 03:00:00,22808.55
2022-07-23 04:00:00,22895.41
2022-07-23 05:00:00,22902.46
2022-07-23 06:00:00,22827.46
2022-07-23 19:00:00,22272.57
2022-07-23 20:00:00,22325.82
2022-07-23 21:00:00,22243.32
2022-07-23 22:00:00,22469.08
2022-07-23 23:00:00,22451.07
2022-07-24 00:00:00,22549.18
2022-07-24 01:00:00,22423.58
2022-07-24 02:00:00,22469.09
2022-07-24 04:00:00,22396.51
2022-07-24 05:00:00,22749.98
2022-07-24 06:00:00,22679.01
2022-07-24 07:00:00,22701.61

date,price,passed_bars
2022-07-23 02:00:00,22834.24,30.0
2022-07-23 19:00:00,22272.57,13.0
2022-07-24 04:00:00,22396.51,4.0

我们可以使用以下代码片段重新生成 Dataframe ：

import pandas as pd

li1 = [{'date': '2022-07-23 02:00:00', 'price': 22834.24}, {'date': '2022-07-23 03:00:00', 'price': 22808.55},
       {'date': '2022-07-23 04:00:00', 'price': 22895.41}, {'date': '2022-07-23 05:00:00', 'price': 22902.46},
       {'date': '2022-07-23 06:00:00', 'price': 22827.46}, {'date': '2022-07-23 19:00:00', 'price': 22272.57},
       {'date': '2022-07-23 20:00:00', 'price': 22325.82}, {'date': '2022-07-23 21:00:00', 'price': 22243.32},
       {'date': '2022-07-23 22:00:00', 'price': 22469.08}, {'date': '2022-07-23 23:00:00', 'price': 22451.07},
       {'date': '2022-07-24 00:00:00', 'price': 22549.18}, {'date': '2022-07-24 01:00:00', 'price': 22423.58},
       {'date': '2022-07-24 02:00:00', 'price': 22469.09}, {'date': '2022-07-24 04:00:00', 'price': 22396.51},
       {'date': '2022-07-24 05:00:00', 'price': 22749.98}, {'date': '2022-07-24 06:00:00', 'price': 22679.01},
       {'date': '2022-07-24 07:00:00', 'price': 22701.61}]

li2 = [{'date': '2022-07-23 02:00:00', 'price': 22834.24, 'passed_bars': 30.0},
       {'date': '2022-07-23 19:00:00', 'price': 22272.57, 'passed_bars': 13.0},
       {'date': '2022-07-24 04:00:00', 'price': 22396.51, 'passed_bars': 4.0}]

df1 = pd.DataFrame.from_records(li1)

df2 = pd.DataFrame.from_records(li2)

目标是向第一个 Dataframe df1添加一个新列，其中每个值必须根据以下逻辑计算：
此新列是df1中的当前记录与df2中的最近记录之间的时间距离，使得df1.date.iloc[i] >= nearest_to_current(df2.date)。
根据上述逻辑，所需的 Dataframe 应如下所示：

date,price, passed_time
2022-07-23 02:00:00,22834.24, 0 hours
2022-07-23 03:00:00,22808.55, 1 hours
2022-07-23 04:00:00,22895.41, 2 hours
2022-07-23 05:00:00,22902.46, 3 hours
2022-07-23 06:00:00,22827.46, 4 hours
2022-07-23 19:00:00,22272.57, 0 hours
2022-07-23 20:00:00,22325.82, 1 hours
2022-07-23 21:00:00,22243.32, 2 hours
2022-07-23 22:00:00,22469.08, 3 hours
2022-07-23 23:00:00,22451.07, 4 hours
2022-07-24 00:00:00,22549.18, 5 hours
2022-07-24 01:00:00,22423.58, 6 hours
2022-07-24 02:00:00,22469.09, 7 hours
2022-07-24 04:00:00,22396.51, 0 hours
2022-07-24 05:00:00,22749.98, 1 hours
2022-07-24 06:00:00,22679.01, 2 hours
2022-07-24 07:00:00,22701.61, 3 hours

pandas

来源：https://stackoverflow.com/questions/73096938/pandas-datetime-comparsion-in-two-different-dataframes-to-find-the-nearset-matc

2条答案

按热度按时间

cetgtptt1#

尝试pd.merge_asof（ Dataframe 必须排序!）：

df1["date"] = pd.to_datetime(df1["date"])
df2["date"] = pd.to_datetime(df2["date"])
df2["passed_time"] = df2["date"]

x = pd.merge_asof(df1, df2[["date", "passed_time"]], on="date")
x["passed_time"] = (x["date"] - x["passed_time"]) / pd.Timedelta("1 hour")
print(x)

图纸：

date     price  passed_time
0  2022-07-23 02:00:00  22834.24          0.0
1  2022-07-23 03:00:00  22808.55          1.0
2  2022-07-23 04:00:00  22895.41          2.0
3  2022-07-23 05:00:00  22902.46          3.0
4  2022-07-23 06:00:00  22827.46          4.0
5  2022-07-23 19:00:00  22272.57          0.0
6  2022-07-23 20:00:00  22325.82          1.0
7  2022-07-23 21:00:00  22243.32          2.0
8  2022-07-23 22:00:00  22469.08          3.0
9  2022-07-23 23:00:00  22451.07          4.0
10 2022-07-24 00:00:00  22549.18          5.0
11 2022-07-24 01:00:00  22423.58          6.0
12 2022-07-24 02:00:00  22469.09          7.0
13 2022-07-24 04:00:00  22396.51          0.0
14 2022-07-24 05:00:00  22749.98          1.0
15 2022-07-24 06:00:00  22679.01          2.0
16 2022-07-24 07:00:00  22701.61          3.0

赞(0）回复(0）举报 2022-12-21

z4iuyo4d2#

要在 Dataframe df中添加新列，可以使用以下命令：

df["passed_time"] = passed_time_values

假设你计算了通过的时间。至于计算通过的时间，一个建议如下：

def get_passed_time(date_time, date_time_list):

    time_deltas = [date_time - date_time_df2 for date_time_df2 in date_time_list]
    # Convert time deltas to hours
    time_deltas = [time_delta.days * 24 + time_delta.seconds/3600 for time_delta in time_deltas]
    positive_t_deltas = [t_delta for t_delta in time_deltas if t_delta > 0.]
    passed_time = min(positive_t_deltas)
    return passed_time

可能还有其他更简单的方法来做到这一点。
样本代码：

date_time_list = list(pd.to_datetime(df1["date"]))
df2_date_time_list = list(pd.to_datetime(df2["date"]))
passed_time_values = [get_passed_time(date_time, df2_date_time_list)
                          for date_time in date_time_list]
df1["passed_time"] = passed_time_values

赞(0）回复(0）举报 2022-12-21

我来回答

Pandas：比较两个不同 Dataframe 中的日期时间，以找到最接近的匹配

2条答案

相关问题

热门标签

最新问答