CODEWITHSUNDEEP
rdataframe
DeduciveR
DeduciveR

Reputation: 1702

Matching a date to date intervals in another data frame

I'm summarising some data to plot in R. I'd like to show the data on a 7 day interval basis with the x-axis being the week-beginning.

sample of the summary data:

structure(list(Date = structure(c(17843, 17843, 17844, 17846, 
                              17846, 17847, 17847, 17847, 17847, 17848, 17848, 17871, 17871, 
                              17871, 17871, 17872, 17872, 17873, 17873, 17873, 17873, 17873, 
                              17944, 17945, 17945, 17945, 17945, 17945, 17945, 17945, 17945, 
                              17945, 17945), class = "Date"), Source = df <- structure(c(1L, 1L, 
                                                                                   2L, 1L, 4L, 1L, 3L, 2L, 2L, 1L, 2L, 1L, 1L, 2L, 2L, 1L, 2L, 1L, 
                                                                                   1L, 1L, 1L, 2L, 3L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = c("direct", 
                                                                                                                                                           "google organic", "other organic search", "PR_referral"), class = "factor"), 
           Revenue = c(1897, 999, 2077.23, 1023.73, 1048, 1897, 949, 
                       949, 999, 849.15, 2077.23, 799.2, 819.18, 865.13, 819.18, 
                       1517.6, 898.2, 2446, 239.2, 867.13, 1517.6, 869.13, 799.2, 
                       853.1, 799.2, 799.2, 799.2, 799.2, 799.2, 799.2, 799.2, 1652.3, 
                       1083.25)), row.names = c(NA, -33L), class = "data.frame")

Using seq and the min and max dates within df$Date I created a data frame of 7 day intervals:

date_interval <- structure(list(week_beginning = structure(c(17827, 17834, 17841, 
                                                         17848, 17855, 17862, 17869, 17876, 17883, 17890, 17897, 17904, 
                                                         17911, 17918, 17925, 17932, 17939, 17946, 17953), class = "Date"), 
                            week_ending = structure(c(17833, 17840, 17847, 17854, 17861, 
                                                      17868, 17875, 17882, 17889, 17896, 17903, 17910, 17917, 17924, 
                                                      17931, 17938, 17945, 17952, NA), class = "Date")), class = c("tbl_df", 
                                                                                                                   "tbl", "data.frame"), row.names = c(NA, -19L))

What I'm trying to achieve is to add a column to df that is the week-beginning referenced from the date_interval data frame, for each observation of df$Date.

I'm thinking this may involve using which but can't get that to work, a non working example being:

converters %>%
  mutate(test = which(Date >= date_interval$week_beginning & Date <= date_interval$week_ending))

Upvotes: 0

Views: 165

Answers (1)

kwiscion
kwiscion

Reputation: 596

As I understand, you want to create column with date of first day of week specified by Date column. If so, you can just use lubridate::floor_date() function, i.e.

converters %>%
  mutate(week_beginning = lubridate::floor_date(Date, unit = 'weeks'))

Upvotes: 1

Related Questions