时间戳

首页 > 解决方案 > 使用日期条件仅检索 r 中的选定列数据

r - 使用日期条件仅检索 r 中的选定列数据

问题描述

我有两个表（订单，价格），我想根据日期标准从订单表到价格表中检索 Monthly_code 和 daily_code。两个表都没有唯一的主键。

订单表价格表

**Orders table data**

orders <- data.table(ID = c(1,1,1,2,2,3), Monthly_code = c('xx','xx','vv','uu','mm','gg'),
           daily_code = c('xx-1','xx-1','vv-1','uu-1','mm-1','gg-1'), 
           Time_in = c('12/1/2020','12/16/2020','12/28/2020', '6/1/2020', '4/5/2020', '6/9/2020'),
           Time_out = c('12/6/2020', '12/27/2020', '12/31/2020','6/13/2020','4/12/2020','6/23/2020')

**Prices table data**

prices <- data.table(ID = c(1,1,1,1,2,2,2,3), record_date = c('12/2/2020','12/3/2020','12/4/2020',
                         '12/5/2020', '6/6/2020', '6/7/2020', '6/8/2020' , '6/20/2020'), Price = c(20,22,21,22,13,15,22,30))

**Expected results data**

price_2 <- data.table(ID = c(1,1,1,1,2,2,2,3), record_date = c('12/2/2020','12/3/2020','12/4/2020',
                                                             '12/5/2020', '6/6/2020', '6/7/2020', '6/8/2020' , '6/20/2020'), 
                    Price = c(20,22,21,22,13,15,22,30), Monthly_code = c('xx','xx','xx','xx', 'uu','uu', 'uu','gg'),
                    daily_code = c('xx-1', 'xx-1', 'xx-1','xx-1', 'uu-1', 'uu-1','uu-1','gg-1'))

标签： rconditional-statements

解决方案

您可以使用fuzzyjoin连接范围内的两个数据框。

library(dplyr)
library(lubridate)
library(fuzzyjoin)

orders %>%
  mutate(across(starts_with('Time'), mdy)) %>%
  fuzzy_right_join(prices %>% mutate(record_date = mdy(record_date)), 
                   by = c('ID', 'Time_in' = 'record_date', 'Time_out' = 'record_date'), 
                   match_fun = c(`==`, `<=`, `>=`)) -> result

result

#  ID.x Monthly_code daily_code    Time_in   Time_out ID.y record_date Price
#1    1           xx       xx-1 2020-12-01 2020-12-06    1  2020-12-02    20
#2    1           xx       xx-1 2020-12-01 2020-12-06    1  2020-12-03    22
#3    1           xx       xx-1 2020-12-01 2020-12-06    1  2020-12-04    21
#4    1           xx       xx-1 2020-12-01 2020-12-06    1  2020-12-05    22
#5    2           uu       uu-1 2020-06-01 2020-06-13    2  2020-06-06    13
#6    2           uu       uu-1 2020-06-01 2020-06-13    2  2020-06-07    15
#7    2           uu       uu-1 2020-06-01 2020-06-13    2  2020-06-08    22
#8    3           gg       gg-1 2020-06-09 2020-06-23    3  2020-06-20    30

推荐阅读