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

Can you be clear about what's your question? In addition, can you provide us sample data and your desired output? — yusuzech, Nov 21 '19 at 22:59

Ronak Shah · Answer 1 · 2019-11-28T07:22:45.680

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>

Thanks Ronak, I have edited the question/data examples to make more sense. Your answer is almost there, however, I am looking for a table of trap activity and not site activity. — David Bernasconi, Nov 22 '19 at 16:10
@DavidBernasconi okay...I have updated the answer. Can you check now ? — Ronak Shah, Nov 23 '19 at 09:08
this is Awesome! It does 95% of what I need it to do. There is one issue though. It does not create date columns if there are gaps in the "site.use.df" where no traps are active at all. I need a column for every day even if no traps that day were active. — David Bernasconi, Nov 23 '19 at 23:24
@DavidBernasconi I am not sure what you mean? Doesn't it give you the expected output that you have shown ? — Ronak Shah, Nov 24 '19 at 01:11
It does give the expected output that was shown. However I need it to satisfy the requirement of "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." . EG if I change "3: B1 1/3/2018 1/12/2018" to "3: B1 1/3/2018 1/11/2018" the column 1/12/2018 would never occur in the output df. I have also noticed when working with my own data, the date columns appear in alphanumeric order of the site columns. This can be replicated by reversing the order of only the "site" column of the input df's — David Bernasconi, Nov 25 '19 at 14:19
@DavidBernasconi It is difficult to "imagine" what you want without relevant data and expected output. But I have updated another version, please check if that is what you need. — Ronak Shah, Nov 26 '19 at 04:09
Sorry for the confusion Ronak. I have update the data tables to clarify exactly what I am looking for. — David Bernasconi, Nov 27 '19 at 17:31
@DavidBernasconi I knew I was closer to what you want but just couldn't understand the actual data you had. Thank you for updating your post with data and expected output. I have updated the answer now. Can you check now and let me know ? — Ronak Shah, Nov 28 '19 at 07:25

Create a detector usage table with occasions for columns

1 Answers1