[R] sub-setting rows based on dates in R

[Jim Lemon]

Hi Md,
This kind of clunky, but it might do what you want.

df1<-read.table(text="Date    Rainfall_Duration
 6/14/2016       10
 6/15/2016       20
 6/17/2016       10
 8/16/2016       30
 8/19/2016       40",
 header=TRUE,stringsAsFactors=FALSE)

df1$Date<-as.Date(df1$Date,"%m/%d/%Y")

df2<-read.table(text="Date    Removal.Rate
 6/17/2016    64.7
 6/30/2016    22.63
 7/14/2016    18.18
 8/19/2016    27.87",
 header=TRUE,stringsAsFactors=FALSE)

df2$Date<-as.Date(df2$Date,"%m/%d/%Y")

df3<-data.frame(Rate.Removal.Date=NULL,Date=NULL,Rainfall_Duration=NULL)

df3row<-0

for(i in 1:dim(df2)[1]) {
 rdrows<-which(df2$Date[i] >= df1$Date & !(df2$Date[i] > df1$Date + 8))
 if(!length(rdrows)) rdrows<-lastrows
 lastrows<-rdrows
 nrows<-length(rdrows)
 for(row in 1:nrows) {
  df3[row+df3row,1]<-format(df2$Date[i],"%m/%d/%Y")
  df3[row+df3row,2]<-format(df1$Date[rdrows[row]],"%m/%d/%Y")
  df3[row+df3row,3]<-df1$Rainfall_Duration[rdrows[row]]
 }
 df3row<-df3row+nrows
}

names(df3)<-c("Rate.Removal.Date","Date","Rainfall_Duration")
df3

Jim

On Wed, Feb 1, 2017 at 3:48 AM, Md Sami Bin Shokrana <samimist at live.com> wrote:
> Hello guys, I am trying to solve a problem in R. I have 2 data frames which look like this:
> df1 <-
>   Date    Rainfall_Duration
> 6/14/2016       10
> 6/15/2016       20
> 6/17/2016       10
> 8/16/2016       30
> 8/19/2016       40
>
> df2 <-
>   Date    Removal.Rate
> 6/17/2016    64.7
> 6/30/2016    22.63
> 7/14/2016    18.18
> 8/19/2016    27.87
>
> I want to look up the dates from df2 in df1 and their corresponding Rainfall_Duration data. For example, I want to look for the 1st date of df2 in df1 and subset rows in df1 for that specific date and 7 days prior to that. additionally, for example: for 6/30/2016 (in df2) there is no dates available in df1 within it's 7 days range. So, in this case I just want to extract the results same as it's previous date (6/17/2016) in df2. Same logic goes for 7/14/2016(df2).
> The output should look like this:
>
> df3<-
>
> Rate.Removal.Date      Date             Rainfall_Duration
> 6/17/2016              6/14/2016              10
> 6/17/2016              6/15/2016              20
> 6/17/2016              6/17/2016              10
> 6/30/2016              6/14/2016              10
> 6/30/2016              6/15/2016              20
> 6/30/2016              6/17/2016              10
> 7/14/2016              6/14/2016              10
> 7/14/2016              6/15/2016              20
> 7/14/2016              6/17/2016              10
> 8/19/2016              8/16/2016              30
> 8/19/2016              8/19/2016              40
>
> I could subset data for the 7 days range. But could not do it when no dates are available in that range. I have the following code:
> library(plyr)
> library (dplyr)
> df1$Date <- as.Date(df1$Date,format = "%m/%d/%Y")
> df2$Date <- as.Date(df2$Date,format = "%m/%d/%Y")
>
> df3 <- lapply(df2$Date, function(x){
>   filter(df1, between(Date, x-7, x))
> })
>
> names(df3) <- as.character(df2$Date)
> bind_rows(df3, .id = "Rate.Removal.Date")
> df3 <- ldply (df3, data.frame, .id = "Rate.Removal.Date")
>
> I hope I could explain my problem properly. I would highly appreciate if someone can help me out with this code or a new one. Thanks in advance.
>
>
>
>
>         [[alternative HTML version deleted]]
>
> ______________________________________________
> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.