CODEWITHSUNDEEP
r
Jacob Nordstrom
Jacob Nordstrom

Reputation: 159

Merge to data sets by date, filling in missing dates in 1st table, R

I'd like to merge the two data sets by the date, keeping all the dates and filling in the totals col with NA when the date doesn't match

Data set 1

 dates      A_totals  
 2015-07-09     1
 2015-07-10     1   
 2015-07-12     2    
 2015-07-14     4   
 2015-07-16     0

Data set 2

 dates      B_totals  
 2015-07-09     2
 2015-07-11     5   
 2015-07-13     6    
 2015-07-15     9   
 2015-07-17     1

Desired Output

 dates      A_totals  B_totals 
 2015-07-09     1       2
 2015-07-10     1       NA
 2015-07-11     NA      5
 2015-07-12     2       NA
 2015-07-13     NA      6
 2015-07-14     4       NA
 2015-07-15     NA      9
 2015-07-16     0       NA
 2015-07-17     NA      1

Upvotes: 1

Views: 113

Answers (2)

Greg
Greg

Reputation: 3326

Solution

In the tidyverse, a simple dplyr::full_join() should suffice:

library(dplyr)

dataset_1 %>%
  full_join(dataset_2, by = "dates") %>%
  arrange(dates)

Results

Given dataset_*s like those reproduced here

dataset_1 <- structure(
  list(
    dates = structure(
      c(16625, 16626, 16628, 16630, 16632),
      class = "Date"
    ),
    A_totals = c(1, 1, 2, 4, 0)
  ),
  row.names = c(NA, -5L),
  class = "data.frame"
)

dataset_2 <- structure(
  list(
    dates = structure(
      c(16625, 16627, 16629, 16631, 16633),
      class = "Date"
    ),
    B_totals = c(2, 5, 6, 9, 1)
  ),
  row.names = c(NA, -5L),
  class = "data.frame"
)

this solution should yield the following data.frame:

       dates A_totals B_totals
1 2015-07-09        1        2
2 2015-07-10        1       NA
3 2015-07-11       NA        5
4 2015-07-12        2       NA
5 2015-07-13       NA        6
6 2015-07-14        4       NA
7 2015-07-15       NA        9
8 2015-07-16        0       NA
9 2015-07-17       NA        1

Upvotes: 2

Andre Wildberg
Andre Wildberg

Reputation: 19191

With base R using merge

merge(df1, df2, "dates", all=T)
       dates A_totals B_totals
1 2015-07-09        1        2
2 2015-07-10        1       NA
3 2015-07-11       NA        5
4 2015-07-12        2       NA
5 2015-07-13       NA        6
6 2015-07-14        4       NA
7 2015-07-15       NA        9
8 2015-07-16        0       NA
9 2015-07-17       NA        1

Data

df1 <- structure(list(dates = c("2015-07-09", "2015-07-10", "2015-07-12",
"2015-07-14", "2015-07-16"), A_totals = c(1L, 1L, 2L, 4L, 0L)), class = "data.frame", row.names = c(NA,
-5L))

df2 <- structure(list(dates = c("2015-07-09", "2015-07-11", "2015-07-13",
"2015-07-15", "2015-07-17"), B_totals = c(2L, 5L, 6L, 9L, 1L)), class = "data.frame", row.names = c(NA,
-5L))

Upvotes: 3

Related Questions