Calculate difference b/w dates by group in R - unsolved

Question

I'm trying to calculate the difference b/w minimum and maximum date by group in R. The code to achieve this I found here. However, replicating the example does not lead to the expected result. This is the dataset example that was used:

HS_Hatch <- structure(list(ClutchID = c(1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 
                                        2L, 3L, 3L, 3L, 4L, 4L, 4L, 4L, 4L, 4L, 5L, 5L, 5L, 5L, 5L, 5L
), DateVisit = c("3/15/2012", "3/18/2012", "3/20/2012", "4/1/2012", 
                 "4/3/2012", "3/18/2012", "3/20/2012", "3/22/2012", "4/3/2012", 
                 "4/4/2012", "3/22/2012", "4/3/2012", "4/4/2012", "3/18/2012", 
                 "3/20/2012", "3/22/2012", "4/2/2012", "4/3/2012", "4/4/2012", 
                 "3/20/2012", "3/22/2012", "3/25/2012", "3/27/2012", "4/4/2012", 
                 "4/5/2012"), Year = c(2012L, 2012L, 2012L, 2012L, 2012L, 2012L, 
                                       2012L, 2012L, 2012L, 2012L, 2012L, 2012L, 2012L, 2012L, 2012L, 
                                       2012L, 2012L, 2012L, 2012L, 2012L, 2012L, 2012L, 2012L, 2012L, 
                                       2012L), Survive = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
                                                           1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L)), class = c("tbl_df", 
                                                                                                                               "tbl", "data.frame"), row.names = c(NA, -25L), .Names = c("ClutchID", 
                                                                                                                                                                                         "DateVisit", "Year", "Survive"), spec = structure(list(cols = structure(list(
                                                                                                                                                                                             ClutchID = structure(list(), class = c("collector_integer", 
                                                                                                                                                                                                                                    "collector")), DateVisit = structure(list(), class = c("collector_character", 
                                                                                                                                                                                                                                                                                           "collector")), Year = structure(list(), class = c("collector_integer", 
                                                                                                                                                                                                                                                                                                                                             "collector")), Survive = structure(list(), class = c("collector_integer", 
                                                                                                                                                                                                                                                                                                                                                                                                  "collector"))), .Names = c("ClutchID", "DateVisit", "Year", 
                                                                                                                                                                                                                                                                                                                                                                                                                             "Survive")), default = structure(list(), class = c("collector_guess", 
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                "collector"))), .Names = c("cols", "default"), class = "col_spec"))

This was the proposed solution using dplyr:

library(dplyr)
HS_Hatch <- HS_Hatch %>%
 mutate(date_visit = as.Date(DateVisit, "%m/%d/%Y"))
exposure <- HS_Hatch %>% 
    group_by(ClutchID) %>%
    summarize(first_visit = min(date_visit), 
              last_visit = max(date_visit), 
              exposure = last_visit - first_visit)

This is the expected result:

  ClutchID first_visit last_visit exposure
     <int>      <date>     <date>    <dbl>
1        1  2012-03-15 2012-04-03       19
2        2  2012-03-18 2012-04-04       17
3        3  2012-03-22 2012-04-04       13
4        4  2012-03-18 2012-04-04       17
5        5  2012-03-20 2012-04-05       16

This is the actual result:

  first_visit last_visit exposure
1  2012-03-15 2012-04-05  21 days

It seems that the grouping factor gets ignored. How do i have it calculate the date difference per ClutchID?

@Henrik Thanks. I solved the issue by rebooting Rstudio. I cleared the workspace and reloaded dplyr, but that didn't seem to overwrite the plyr package that was loaded. — FlyingDutch, Jun 28 '18 at 15:45

score 2 · Accepted Answer · answered Jun 28 '18 at 16:17

2

It works with just dplyr loaded.

Change summarize to dplyr::summarize to make it unambiguous. I would suggest not using plyr as you can do everything with dplyr and tidyverse.

answered Jun 28 '18 at 16:17

thc

9,527
1
24
39

thank you, i didn't know that. – FlyingDutch Jul 01 '18 at 12:48

score 1 · Answer 2 · answered Jun 28 '18 at 16:22

After the import of the dataframe, try this

HS_Hatch$DateVisit = as.Date(HS_Hatch$DateVisit, "%m/%d/%Y")
HS_Hatch$DateVisit = as.POSIXct(HS_Hatch$DateVisit, "%m/%d/%Y")

Then change your dplyr pipe to:

HS_Hatch <- HS_Hatch %>%
group_by(ClutchID) %>%
summarize(first_visit = min(date_visit), 
          last_visit = max(date_visit), 
          exposure = last_visit - first_visit)

This gave the expected result and worked since the format Posixct stores time in seconds since "the origin" and you can calculate differences.

Thanks for the helpful answer – FlyingDutch Jul 01 '18 at 12:48 — FlyingDutch, Jul 01 '18 at 12:48

Calculate difference b/w dates by group in R - unsolved

2 Answers2