pandas 如何将datetime panda列与另一个datetime列中时间范围匹配

ztigrdn8 于 2023-01-07 发布在其他

关注(0)|答案(1)|浏览(123)

我有一个df 1，其中包含列“报告日期”和“订单”，df 2包含列“lastoccurance”和“订单”，我想合并df 1和df 2，条件为lastoccurrence =报告日期+或- 30分钟（范围为-/+ 30分钟），订单=订单注意：报告日期和最后一次出现是日期时间列
例如：输入
DF1

Reported Date            order          
12/14/2022  6:10:32 PM        A
9/15/2022  2:45:57 AM         B
9/15/2022  11:08:26 AM        C

DF2

lastoccurrence          orders
12/14/2022  6:15:35 PM        A
12/14/2022  6:00:35 PM        A
12/14/2022  5:40:35 PM        A
12/14/2022  6:40:35 PM        A
12/14/2022  6:10:32 PM        B
9/15/2022  11:20:26 AM        C
9/15/2022  11:08:26 AM        A

产出
DF3

lastoccurrence          orders
12/14/2022  6:15:35 PM        A
12/14/2022  6:00:35 PM        A
12/14/2022  5:40:35 PM        A
12/14/2022  6:40:35 PM        A
9/15/2022  11:20:26 AM        C

pandas

来源：https://stackoverflow.com/questions/75016944/how-to-match-datetime-pandas-column-with-range-of-times-in-another-datetime-colu

1条答案

按热度按时间

nzk0hqpo1#

首先将两列都转换为日期时间，然后按orders和Series.mapMapReported Date，因此可以按Series.between在容差范围内进行比较：

df1['Reported Date'] = pd.to_datetime(df1['Reported Date'])
df2['lastoccurrence'] = pd.to_datetime(df2['lastoccurrence'])

s = df2['orders'].map(df1.set_index('order')['Reported Date'])

td =  pd.Timedelta('30 min')
df = df2[df2['lastoccurrence'].between(s - td, s + td)]
print (df)
       lastoccurrence orders
0 2022-12-14 18:15:35      A
1 2022-12-14 18:00:35      A
2 2022-12-14 17:40:35      A
5 2022-09-15 11:20:26      C

或者使用带公差参数的merge_asof和direction='nearest'：

df1['Reported Date'] = pd.to_datetime(df1['Reported Date'])
df2['lastoccurrence'] = pd.to_datetime(df2['lastoccurrence'])

df = pd.merge_asof(df2.sort_values('lastoccurrence'), 
                   df1.sort_values('Reported Date'), 
                   left_by='orders',
                   right_by='order', 
                   left_on='lastoccurrence', 
                   right_on='Reported Date', 
                   tolerance=pd.Timedelta('30 min'),
                   direction='nearest').dropna(subset=['order']).drop(['Reported Date','order'], axis=1)

print (df)
       lastoccurrence orders
1 2022-09-15 11:20:26      C
2 2022-12-14 17:40:35      A
3 2022-12-14 18:00:35      A
5 2022-12-14 18:15:35      A

赞(0）回复(0）举报 2023-01-07

我来回答

pandas 如何将datetime panda列与另一个datetime列中时间范围匹配

1条答案

相关问题

热门标签

最新问答