Divide values in data frame according to date range in another data frame

Question

I have two dataframes. Dataframe data one has two columns: one contains ymd dates, and the other values:

        date value
1 2009-10-23  1100
2 2009-05-01  5000
3 2010-01-13  3050
4 2010-07-24  2700
5 2009-06-16  2600

My second dataframe (named factors) also has two columns: another ymd date, and a coefficient. Here, for each month of each year, I always have two specific dates: the 1st and 15th of each month. This is how the data frame looks (I only added some dates on this minimal example, but there shouldn't be any 'jumps': I have continued data in a 10-year period):

         date coeff
1  2009-05-01  2.00
2  2009-05-15  3.00
3  2009-06-01  2.50
4  2009-06-15  4.00
5  2009-10-01  3.65
6  2009-10-15  4.80
7  2010-01-01  2.40
8  2010-01-15  1.90
9  2010-07-01  5.20
10 2010-07-15  4.30

The dataframes are ready to use on this fiddle: http://rextester.com/MOIY96065

My problem

I need to create a new column in dataframe 1 (named data) where this column is data$value / factors$coeff following a condition: it must use the coeff with the previous closest date value.

For example: date$value[1] should be divided by factors$coeff[6] (the value on October 15th), but date$value[2] should be divided by factors$coeff[1] (the value on May 1st).

My factors dataframe is ordered by date. I've been using lubridate to parse the dates from string type, but I don't know how can I make this work.

Mikko Marttila · Accepted Answer · 2018-08-09T13:57:22.960

You can use findInterval() to get the indices for selecting the correct rows from factors:

(i <- findInterval(date$date, factors$date))
#> [1]  6  1  7 10  4

date$value / factors$coeff[i]
#> [1]  229.1667 2500.0000 1270.8333  627.9070  650.0000

Created on 2018-08-09 by the reprex package (v0.2.0.9000).

Data:

date <- structure(list(date = structure(c(14540, 14365, 14622, 14814, 
14411), class = "Date"), value = c(1100, 5000, 3050, 2700, 2600
)), row.names = c(NA, -5L), class = "data.frame")

factors <- structure(list(date = structure(c(14365, 14379, 14396, 14410, 
14518, 14532, 14610, 14624, 14791, 14805), class = "Date"), coeff = c(2, 
3, 2.5, 4, 3.65, 4.8, 2.4, 1.9, 5.2, 4.3)), row.names = c(NA, 
-10L), class = "data.frame")

score 1 · Answer 2 · edited Jun 20 '20 at 09:12

Adapted form @Frank answer's here

d <- function(x,y) {
      diff <- as.numeric(x-y)
      diff <- which.min(diff[diff>=0])
}

indx <- sapply(df$date, function(x) d(x,df1$date))

df_final <- cbind(df,df1[indx,,drop=FALSE])

df_final$result <- df_final$value/df_final$coeff

date value       date coeff    result
1 2009-10-23  1100 2009-10-15   4.8  229.1667
2 2009-05-01  5000 2009-05-01   2.0 2500.0000
3 2010-01-13  3050 2010-01-01   2.4 1270.8333
4 2010-07-24  2700 2010-07-15   4.3  627.9070
5 2009-06-16  2600 2009-06-15   4.0  650.0000

data

df<-read.table(text="        date value
           1 2009-10-23  1100
           2 2009-05-01  5000
           3 2010-01-13  3050
           4 2010-07-24  2700
           5 2009-06-16  2600
           ",header=TRUE)


df1<-read.table(text="         date coeff
           1  2009-05-01  2.00
           2  2009-05-15  3.00
           3  2009-06-01  2.50
           4  2009-06-15  4.00
           5  2009-10-01  3.65
           6  2009-10-15  4.80
           7  2010-01-01  2.40
           8  2010-01-15  1.90
           9  2010-07-01  5.20
           10 2010-07-15  4.30
           ",header=TRUE)

Divide values in data frame according to date range in another data frame

2 Answers2

data