从一个数据框中获取日期,并在另一个数据框中过滤数据 [英] Take dates from one dataframe and filter data in another dataframe
问题描述
我有两个数据帧,
user=c(rep('A',7),rep('B',8))
data = seq(1:15)
date = as.Date(c('2016-01-01','2016-01-02','2016-01-03','2016-01-04','2016-01-05','2016-01-06','2016-01-07','2016-01-08','2016-01-09','2016-01-10','2016-01-11','2016-01-12','2016-01-13','2016-01-14','2016-01-15'))
df = data.frame(user,date,data)
df
user date data
1 A 2016-01-01 1
2 A 2016-01-02 2
3 A 2016-01-03 3
4 A 2016-01-04 4
5 A 2016-01-05 5
6 A 2016-01-06 6
7 A 2016-01-07 7
8 B 2016-01-08 8
9 B 2016-01-09 9
10 B 2016-01-10 10
11 B 2016-01-11 11
12 B 2016-01-12 12
13 B 2016-01-13 13
14 B 2016-01-14 14
15 B 2016-01-15 15
和
df1 =data.frame(user = c('A','B'), start_date = as.Date(c('2016-01-02','2016-01-10')), end_date = as.Date(c('2016-01-06','2016-01-14')))
> df1
user start_date end_date
1 A 2016-01-02 2016-01-06
2 B 2016-01-10 2016-01-14
我想从df1中获取开始日期和结束日期,并过滤df数据帧的date列中的记录。特定用户的数据应仅在df1的开始日期和结束日期之间。结果数据框应具有以下输出,
I want to take the start date and end date from df1 , and filter the records in the date column of df dataframe. The data for a particular user should be only between the start_date and end_date of df1. The resulting dataframe should have the following output,
user date data
A 2016-01-02 2
A 2016-01-03 3
A 2016-01-04 4
A 2016-01-05 5
A 2016-01-06 6
B 2016-01-10 10
B 2016-01-11 11
B 2016-01-12 12
B 2016-01-13 13
B 2016-01-14 14
我尝试了以下操作,
遍历每个用户,将其传递到数据框。然后使用df1中相应条目的开始日期和结束日期再次对其进行过滤,然后将其附加到新的数据框中。由于数据非常庞大,这对我来说要花费很长时间。有没有更有效的方法?
Looping through each user, passing it to a dataframe. Then filtering it again with the start_date and end_date of corresponding entry in df1, and then appending it to a new dataframe. This is taking a very long time for me since the data is very huge. Is there a more efficient way to do this?
谢谢
推荐答案
library(dplyr)
df<-left_join(df,df1,by="user")
df <- df %>% filter(date>=start_date & date<=end_date)
这篇关于从一个数据框中获取日期,并在另一个数据框中过滤数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!