R - Reclassifying days in relation to another variable

Question

I currently have the following data frame:

> head(Coyote_reports_garbage)
# A tibble: 6 x 4
  name_1          Date       Day       Collection
  <chr>           <date>     <chr>     <chr>     
1 PLEASANTVIEW    2013-02-20 Wednesday Friday    
2 MCCONACHIE AREA 2012-11-20 Tuesday   Friday    
3 MAYLIEWAN       2013-11-28 Thursday  Friday    
4 BROOKSIDE       2013-12-18 Wednesday Thursday  
5 KIRKNESS        2012-11-14 Wednesday Friday    
6 RIDEAU PARK     2013-11-15 Friday    Friday  

Where "name_1" represents the name of a neighbourhood, "Date" represents the date when a report was made, "Day" represent the day of the week where that report was name (in relation to the date), and "Collection" represents the garbage day in that neighbourhood. "Collection" therefore varies per neighbourhood and year.

I am trying to add a column (Day_in_relation_to_collection) where the day would be related to Collection day. If the day of the week is the same as the garbage collection day, Day_in_relation_to_collection = 0. If the day of the week is a day after collection day, Day_in_relation_to_collection = 1, etc.

  name_1          Date       Day       Collection   Day_in_relation_to_collection
  <chr>           <date>     <chr>     <chr>     
1 PLEASANTVIEW    2013-02-20 Wednesday Friday                                  5
2 MCCONACHIE AREA 2012-11-20 Tuesday   Friday                                  4
3 MAYLIEWAN       2013-11-28 Thursday  Friday                                  6
4 BROOKSIDE       2013-12-18 Wednesday Thursday                                6   
5 KIRKNESS        2012-11-14 Wednesday Friday                                  5
6 RIDEAU PARK     2013-11-15 Friday    Friday                                  0

I'm not quite sure how to do this, so any help would be appreciated.

Answer 1

I'm assuming here that Day will always be after Collection, and it will always be the next instance of that day. If so, a simple way to do that would be to make a reference matrix setting up the number of days between a combination of 2 days of the week and then using that to fill in this value:

dnames <- c("Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday")

day_table <- matrix(c(0:6,6,0:5,5:6,0:4,4:6,0:3,3:6,0:2,2:6,0:1,1:6,0),
                    nrow=7, ncol=7, byrow=T,
                    dimnames = list(dnames, dnames))

day_table
          Sunday Monday Tuesday Wednesday Thursday Friday Saturday
Sunday         0      1       2         3        4      5        6
Monday         6      0       1         2        3      4        5
Tuesday        5      6       0         1        2      3        4
Wednesday      4      5       6         0        1      2        3
Thursday       3      4       5         6        0      1        2
Friday         2      3       4         5        6      0        1
Saturday       1      2       3         4        5      6        0

Now we can just access the values of Coyote_reports_garbage$Collection and Coyote_reports_garbage$Day to access values in that table to get the appropriate value. We can either run this as a tidyverse mutate statement, or insert it using base R.

Either way, we need to use diag here, as subsetting a matrix with 2 vectors gives a matrix with all combinations of the selected values. The diagonal of that matrix will give the result you want here:

library(tidyverse)
Coyote_reports_garbage %>%
    mutate(Day_in_relation_to_collection = diag(day_table[Collection,Day]))

           name_1       Date       Day Collection Day_in_relation_to_collection
1    PLEASANTVIEW 2013-02-20 Wednesday     Friday                             5
2 MCCONACHIE AREA 2012-11-20   Tuesday     Friday                             4
3       MAYLIEWAN 2013-11-28  Thursday     Friday                             6
4       BROOKSIDE 2013-12-18 Wednesday   Thursday                             6
5        KIRKNESS 2012-11-14 Wednesday     Friday                             5
6     RIDEAU PARK 2013-11-15    Friday     Friday                             0

Or in base R

Coyote_reports_garbage$dr_collect <- diag(day_table[Coyote_reports_garbage$Collection,
                                                    Coyote_reports_garbage$Day])

Coyote_reports_garbage
           name_1       Date       Day Collection dr_collect
1    PLEASANTVIEW 2013-02-20 Wednesday     Friday          5
2 MCCONACHIE AREA 2012-11-20   Tuesday     Friday          4
3       MAYLIEWAN 2013-11-28  Thursday     Friday          6
4       BROOKSIDE 2013-12-18 Wednesday   Thursday          6
5        KIRKNESS 2012-11-14 Wednesday     Friday          5
6     RIDEAU PARK 2013-11-15    Friday     Friday          0