CODEWITHSUNDEEP
r
Henry
Henry

Reputation: 1686

Merge and fill R from two dataframes on date

I have reproduced my example as follows:

x<-as.Date(c(as.Date('2015-01-01'):as.Date('2016-01-01')), origin='1970-01-01')
dates<-as.Date(c(as.Date('2015-01-01'),as.Date('2015-03-04'),as.Date('2015-07-01')),origin='1970-01-01')
values<-c(3,7,10)

What I would like is an output dataframe where values for a particular date in x are joined to the most recent, but historic, date entry. For example:

x, value
2015-01-01, 3
2015-01-02, 3
....
2015-03-04, 7
2015-03-05, 7
....
2015-07-01, 10
2015-07-02, 10
....
2016-01-01, 10

I've currently implemented this through a for loop, but it feels slow and horrendously inefficient - I'm sure there must be some way in R to do it more automatically?

Upvotes: 0

Views: 43

Answers (1)

Batanichek
Batanichek

Reputation: 7871

Try something like it 

x<-data.frame(x=as.Date(c(as.Date('2015-01-01'):as.Date('2016-01-01')), origin='1970-01-01'))
dates<-as.Date(c(as.Date('2015-01-01'),as.Date('2015-03-04'),as.Date('2015-07-01')),origin='1970-01-01')
values<-c(3,7,10)

a=data.frame(dates,values)
y=merge(x,a,by.x='x',by.y='dates',all.x=T)
colnames(y)=c("x","value")
test=function(i){

  if(is.na(y[i,2])){
    if(i==1) return(NA)
    return(test(i-1))
  }else{
    return(y[i,2])
  }
}

y$value=sapply(1:nrow(y),test)

Upvotes: 2

Related Questions