在第二个参考数据框中查找最近（但以前）的日期

Question

我有两个数据帧，其中一个我想在另一个数据帧中找到最近的（以前的）日期

如果日期匹配，则我需要选择上一个日期

df_main包含引用信息

对于df_sample，我想在df_main中的Time中查找最近的（但以前的）条目。我可以使用method='ffill'来实现这一点，但是如果Time字段的日期是当天返回的日期-我希望它返回前一天-基本上是一个<而不是<=

在我的示例df_res中，我希望closest_val列包含[ "n/a", 90, 90, 280, 280, 280]

import pandas as pd

dsample = {'Index': [1, 2, 3, 4, 5, 6],
     'Time': ["2020-06-01", "2020-06-02", "2020-06-03", "2020-06-04" ,"2020-06-05" ,"2020-06-06"],
     'Pred': [100, -200, 300, -400 , -500, 600]
    }

dmain = {'Index': [1, 2, 3],
     'Time': ["2020-06-01", "2020-06-03","2020-06-06"],
     'Actual': [90, 280, 650]
    }

def find_closest(x, df2):
    df_res = df2.iloc[df2.index.get_loc(x['Time'], method='ffill')]
    x['closest_time'] = df_res['Time']
    x['closest_val'] = df_res['Actual']
    return x

df_sample = pd.DataFrame(data=dsample)
df_main = pd.DataFrame(data=dmain)

df_sample = df_sample.set_index(pd.DatetimeIndex(df_sample['Time']))
df_main = df_main.set_index(pd.DatetimeIndex(df_main['Time']))
df_res = df_sample.apply(find_closest, df2=df_main ,axis=1)

Answer 1

使用pd.merge_asof（确保'Time'确实是datetime）：

pd.merge_asof(dsample, dmain, left_on="Time", right_on="Time", allow_exact_matches=False)

输出为：

   Index_x       Time  Pred  Index_y  Actual
0        1 2020-06-01   100      NaN     NaN
1        2 2020-06-02  -200      1.0    90.0
2        3 2020-06-03   300      1.0    90.0
3        4 2020-06-04  -400      2.0   280.0
4        5 2020-06-05  -500      2.0   280.0
5        6 2020-06-06   600      2.0   280.0

Answer 2

IIUC，我们可以对两个数据帧进行笛卡尔积，然后过滤出精确的匹配，然后应用一些逻辑计算出最近的日期

最后，我们将把您的extact和非精确匹配加入到最终的数据帧中

s = pd.merge(
    df_sample.assign(key="var1"),
    df_main.assign(key="var1").rename(columns={"Time": "TimeDelta"}).drop("Index", 1),
    on="key",
    how="outer",
).drop("key", 1)


extact_matches = s[s['Time'].eq(s['TimeDelta'])]
non_exact_matches_cart = s[~s['Time'].isin(extact_matches['Time'])]


non_exact_matches = non_exact_matches_cart.assign(
    delta=(non_exact_matches_cart["Time"] - non_exact_matches_cart["TimeDelta"])
    / np.timedelta64(1, "D") 
).query("delta >= 0").sort_values(["Time", "delta"]).drop_duplicates(
    "Time", keep="first"
).drop('delta',1)

---

上面的变量有很多，但本质上，我们是在寻找时间上的差异，消除未来的任何差异，并删除保留过去最接近日期的值

df = pd.concat([extact_matches, non_exact_matches], axis=0).sort_values("Time").rename(
    columns={"TimeDelta": "closest_time", "Actual": "closest val"}
)


print(df)

    Index       Time  Pred closest_time  closest val
0       1 2020-06-01   100   2020-06-01           90
3       2 2020-06-02  -200   2020-06-01           90
7       3 2020-06-03   300   2020-06-03          280
10      4 2020-06-04  -400   2020-06-03          280
13      5 2020-06-05  -500   2020-06-03          280
17      6 2020-06-06   600   2020-06-06          650