在几天之内按日期合并2个Pandas数据框? [英] Merge 2 Pandas dataframes by dates within a few days of each other?

问题描述

我有这两个熊猫数据框，它们是我从在线数据中创建和清除的，我试图根据它们的日期(它们都是按月)进行合并.但是，无论第二个数据集是否基于该月的第一天，第一个数据集的日期都在该月的最后一天.

I have these two pandas dataframes I created and cleaned from online data, and I was trying to merge them based on their dates, which are all by month. However, the first dataset has its days on the last day of the month, whether the second dataset is based on the first day of the month.

# data1
0    1987-01-01  63.752
1    1987-02-01  64.152
2    1987-03-01  64.488
3    1987-04-01  64.995

# data2
0   1987-01-31  1115.10
1   1987-02-30  1095.63
2   1987-03-30  1036.19
3   1987-04-30  1057.08

如果我每天都有一些丢失的几天的数据，通常我会通过类似的方式合并它们

I would normally merge them by something like this if I had daily data with a few missing days

data3 = pd.merge(left=data1, left_on='Date', right=data2, right_on='Date')

，但是在这种情况下，即使它们都是相似的日期，它们也永远不会匹配.

but in this case they are never matching, even though they are all similar dates.

我该如何告诉"熊猫以仅相隔几天的日期合并数据集，并以月-年"命名每个数据?我不知道从哪里开始.

How would I go about "telling" Pandas to combine the datasets based on dates that are just a few days apart, and name each data by just "month - year"? I don't know where to begin.

推荐答案

IIUC，您想在最近的日期合并吗?这就是 merge_asof 是用于

IIUC, you want to merge on the closest dates? This is what merge_asof is for

如果日期尚未转换为日期时间，则将其转换为

If dates are not already converted to datetime, convert them like so

data1.date = pd.to_datetime(data1.date)
data2.date = pd.to_datetime(data2.date)

现在完成合并

pd.merge_asof(data1,data2,on='date',direction='nearest')

       date  value_x  value_y
0 1987-01-01   63.752  1115.10
1 1987-02-01   64.152  1036.19
2 1987-03-01   64.488  1057.08
3 1987-04-01   64.995  1057.08

这篇关于在几天之内按日期合并2个Pandas数据框?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！