merge data.frames based on year and fill in missing values

Question

I have two data.frames that I want to merge together. The first is:

datess <- seq(as.Date('2005-01-01'), as.Date('2009-12-31'), 'days')
sample<- data.frame(matrix(ncol = 3, nrow = length(datess)))
colnames(sample) <- c('Date', 'y', 'Z')
sample$Date <- datess

The second:

a <- data.frame(matrix(ncol = 3, nrow = 5))
colnames(a) <- c('a', 'y', 'Z')
a$Z <- c(1, 3, 4, 5, 2)
a$a <- c(2005, 2006, 2007, 2008, 2009)
a$y <- c('abc', 'def', 'ijk', 'xyz', 'thanks')

And I'd like the merged one to match the year and then fill in the rest of the values for every day of that year.

Date          y      Z
2005-01-01   abc     1
2005-01-02   abc     1 
2005-01-03   abc     1
{cont}
2009-12-31   thanks  2

Answer 1

So far, three different approaches have been posted:

• using match()
• using dplyr
• using merge()

There is a fourth approach called update join suggested by Frank in chat:

library(data.table)
setDT(sample)[, yr := year(Date)][setDT(a), on = .(yr = a), `:=`(y = i.y, Z = i.Z)]

which turned out to be the fastest and most concise of the four.

Benchmark results:

To decide which of the approaches is the most efficient in terms of speed I've set up a benchmark using the microbenchmarkpackage.

Unit: microseconds
        expr      min       lq     mean    median       uq      max neval
 create_data  248.827  291.116  316.240  302.0655  323.588  665.298   100
       match 4488.685 4545.701 4752.226 4649.5355 4810.763 6881.418   100
       dplyr 6086.609 6275.588 6513.997 6385.2760 6625.229 8535.979   100
       merge 2871.883 2942.490 3183.712 3004.6025 3168.096 5616.898   100
 update_join 1484.272 1545.063 1710.651 1659.8480 1733.476 3434.102   100

As sample is modified it has to be created anew before each benchmark run. This is been done by a function which is included in the benchmark as well (create data). The times for create data need to be subtracted from the other timings.

So, even for the small data set of about 1800 rows, update join is the fastest, nearly twice as fast as the second merge, followed by match, and dplyr being last, more than 4 times slower than update join (with the time for create data subtracted).

Benchmark code

datess <- seq(as.Date('2005-01-01'), as.Date('2009-12-31'), 'days')
a <- data.frame(Z = c(1, 3, 4, 5, 2),
                a = 2005:2009,
                y = c('abc', 'def', 'ijk', 'xyz', 'thanks'),
                stringsAsFactors = FALSE)
setDT(a)
make_sample <- function() data.frame(Date = datess, y = NA_character_, Z = NA_real_)

library(data.table)
library(magrittr)
microbenchmark::microbenchmark(
  create_data = make_sample(),
  match = {
    sample <- make_sample()
    matched<-match(format(sample$Date,"%Y"),a$a)
    sample$y<-a$y[matched]
    sample$Z<-a$Z[matched]
  },
  dplyr = {
    sample <- make_sample()
    sample <- sample %>% 
      dplyr::mutate(a = format(Date, "%Y") %>% as.numeric) %>% 
      dplyr::inner_join(a %>% dplyr::select(a), by = "a") 
  },
  merge = {
    sample <- make_sample()
    sample2 <- data.frame(Date = datess)
    sample2$a <- lubridate::year(sample2$Date)
    sample <- base::merge(sample2, a, by="a")
  },
  update_join = {
    sample <- make_sample()
    setDT(sample)[, yr := year(Date)][a, on = .(yr = a), `:=`(y = i.y, Z = i.Z)]
  }
)

Answer 2

You can use match

matched<-match(format(sample$Date,"%Y"),a$a)
sample$y<-a$y[matched]
sample$Z<-a$Z[matched]

Answer 3

If y and Z are always zero in sample you do not need them there, so all you have to do is join on year like this:

 library(dplyr)
 sample %>% mutate(a = format(Date, "%Y") %>% as.numeric) %>% 
   inner_join(a %>% select(a)) 

Answer 4

Is there anything speaking against having a column with year in your new df? If not you could generate one in 'sample' and use the merge function

require(lubridate) #to make generating the year easy 
sample2<-data.frame(Date=datess)
sample2$a<-year(sample2$Date)
df<-merge(sample2,a,by="a")

this will result in something like this:

head(df)
     a       Date   y Z
1 2005 2005-01-01 abc 1
2 2005 2005-01-02 abc 1
3 2005 2005-01-03 abc 1
4 2005 2005-01-04 abc 1
5 2005 2005-01-05 abc 1
6 2005 2005-01-06 abc 1

You could then remove the year column again if it bothers you.