Create a detector usage table with occasions for columns

Question

I need to create a detector usage table where the rows are individual detectors and the columns are the full date range over which they all were active. Within this table a 1 indicates the detector is active on a given day and a 0 indicates that a detector was inactive. The traps were activated in groups delineated by "site" (eg site: A1,B1,C1). The continuous date range in the columns needs to start the first day the first trap was active and end the last day the last trap was active.

Currently I have following data tables to work with

Site use table:

  > site.use.df
   site     first      last
1:   B1 1/11/2017 1/12/2017
2:   B1 1/15/2017 1/16/2017
3:   P1  1/7/2017  1/8/2017
4:   P1 1/13/2017 1/14/2017
5:   R1 1/10/2017 1/11/2017
6:   R1 1/15/2017 1/16/2017

Trap ID table:

> trapID.df
    trapID site
 1:    154   P1
 2:    155   P1
 3:    156   P1
 4:    157   P1
 5:    158   P1
 6:    304   R1
 7:    305   R1
 8:    306   R1
 9:    307   R1
10:    308   R1
11:      1   B1
12:      2   B1
13:      3   B1
14:      4   B1
15:      5   B1

Ideally, the full date range will be taken from the site use table and not entered by hand.

The end product I am looking for will look like this:

 > detector.table
    trapID site 1/7/2017 1/8/2017 1/9/2017 1/10/2017 1/11/2017 1/12/2017 1/13/2017 1/14/2017 1/15/2017 1/16/2017
 1:      1   B1        0        0        0         0         1         1         0         0         1         1
 2:      2   B1        0        0        0         0         1         1         0         0         1         1
 3:      3   B1        0        0        0         0         1         1         0         0         1         1
 4:      4   B1        0        0        0         0         1         1         0         0         1         1
 5:      5   B1        0        0        0         0         1         1         0         0         1         1
 6:    154   P1        1        1        0         0         0         0         1         1         0         0
 7:    155   P1        1        1        0         0         0         0         1         1         0         0
 8:    156   P1        1        1        0         0         0         0         1         1         0         0
 9:    157   P1        1        1        0         0         0         0         1         1         0         0
10:    158   P1        1        1        0         0         0         0         1         1         0         0
11:    304   R1        0        0        0         1         1         0         0         0         1         1
12:    305   R1        0        0        0         1         1         0         0         0         1         1
13:    306   R1        0        0        0         1         1         0         0         0         1         1
14:    307   R1        0        0        0         1         1         0         0         0         1         1
15:    308   R1        0        0        0         1         1         0         0         0         1         1

Answer 1

If I have understood you correctly, using tidyverse we can create a sequence of dates between first and last columns and get the data in the wide format creating 1/0 columns.

library(tidyverse)

temp <- trapID.df %>%
          left_join(site.use.df) %>%
          mutate_at(vars(first, last), mdy) %>%
          mutate(date = map2(first, last, seq, by = "1 day")) %>%
          unnest(date) %>%
          select(-first, -last) %>%
          mutate(present = 1) %>%
          group_by(trapID, site) %>%
          complete(date = seq(min(date), max(date), by = "1 day"), 
                          fill = list(present = 0)) %>%
          pivot_wider(names_from = date, values_from = present, 
                      values_fill = list(present = 0))


#If you need data sorted by date
temp[c(names(temp)[1:2], sort(names(temp)[-(1:2)]))] 


#   trapID site  `2017-01-07` `2017-01-08` `2017-01-09` `2017-01-10` `2017-01-11`
#    <int> <fct>        <dbl>        <dbl>        <dbl>        <dbl>        <dbl>
# 1      1 B1               0            0            0            0            1
# 2      2 B1               0            0            0            0            1
# 3      3 B1               0            0            0            0            1
# 4      4 B1               0            0            0            0            1
# 5      5 B1               0            0            0            0            1
# 6    154 P1               1            1            0            0            0
# 7    155 P1               1            1            0            0            0
# 8    156 P1               1            1            0            0            0
# 9    157 P1               1            1            0            0            0
#10    158 P1               1            1            0            0            0
#11    304 R1               0            0            0            1            1
#12    305 R1               0            0            0            1            1
#13    306 R1               0            0            0            1            1
#14    307 R1               0            0            0            1            1
#15    308 R1               0            0            0            1            1
# … with 5 more variables: `2017-01-12` <dbl>, `2017-01-13` <dbl>, 
#   `2017-01-14` <dbl>, `2017-01-15` <dbl>, `2017-01-16` <dbl>