How to count number of Month based on date R

Question

I have below mentioned dataframr:

DF_1

ID      Date
123     18/03/2018 16:45
456     10/03/2018 20:15

DF_2

ID      Date1                  Date2
123     2018-03-18 06:37:22    1519109133704
123     2018-03-18 06:37:21    1520324827462
123     2018-03-16 04:03:01    1520690354458
456     2018-03-10 14:46:03    1517319313151
456     2018-03-10 14:46:04    1515143046429
456     2018-03-10 14:46:03    1515838021062
456     2018-03-10 14:46:15    1488092209241

• Where i want to count number of Month as Month considering Date2 of the DF_2 with respect to the ID with comparing ID of DF_1.
• And Average Number as Avg of Rows created per month (i.e if there are 3 month based on Date2 comprising 90 Rows than average would be 30).
• And Average number of rows per Day as Day (i.e if there are 3 month comprising 90 rows than there would be value 1 for Day).
• Last5 Count of Number of Rows created in Last 5 days (considering Date1) with respect to Sys.Date()

I have below mentioned code for the same:

library(tidyverse)
 library(lubridate)
 DF_2 <- tibble(ID = c(123L, 123L, 123L, 456L, 456L, 456L, 456L), 
                Date1 = c("2018-03-18 06:37:22", "2018-03-18 06:37:21", "2018-03-16 04:03:01", 
                 "2018-03-10 14:46:03", "2018-03-10 14:46:04", "2018-03-10 14:46:03", 
                 "2018-03-10 14:46:15"), 
                Date2 = c(1519109133704, 1520324827462, 1520690354458, 1517319313151, 1515143046429, 1515838021062, 1488092209241)
               )

 DF_2 <- DF_2 %>% mutate(Date1 = ymd_hms(Date1), 
                         Date2 = as.POSIXct(Date2/1000,origin = "1970-01-01")) 

 DF_2_tab <- DF_2 %>% group_by(ID) %>% summarise(date1 = sum(date(Date1)==date(DF_1$Date1[DF_1$ID==ID])),
                            Total = n(), 
                            Month = month(count(Date2)),
                            Avg = mean #Don;t know how to calculate
                            Day = day(Date2),
                            Last5 = sum( (Sys.Date()-date(Date1)) < 5 )
                            )

Answer 1

Your statement 1 is not very clear, what is the use of DF_1. Anyway, please see the code below to summarize the DF_2 the way you want. In case, I have distinct number of months, and total record, Point 2 and 3 is accomplished (assuming you are simply taking 30 days a month, as you have explained above). 4th point is done in the code -

DF_2 = data.table(DF_2)
DF = DF_2[, list(num_mth = uniqueN(format(Date2, "%Y%m")), num_rec=.N, 
          numrec_5d=length(ID[as.numeric(difftime(today(), Date2), units = "days")<=5])), 
          by=ID]

Since you explained the use of DF_1, I have edited my code. Now first merge the two datasets on ID and date1, and then summarize -

DF_2 <- tibble(ID = c(123L, 123L, 123L, 456L, 456L, 456L, 456L), 
               Date1 = c("2018-03-18 06:37:22", "2018-03-18 06:37:21", "2018-03-16 04:03:01", 
                         "2018-03-10 14:46:03", "2018-03-10 14:46:04", "2018-03-10 14:46:03", 
                         "2018-03-10 14:46:15"), 
               Date2 = c(1519109133704, 1520324827462, 1520690354458, 1517319313151, 1515143046429, 1515838021062, 1488092209241)
)

DF_2 <- DF_2 %>% mutate(Date1 = ymd_hms(Date1), 
                        Date2 = as.POSIXct(Date2/1000,origin = "1970-01-01")) 


DF_1 <- tibble(ID = c(123L, 456L), 
               Date1 = c("18/03/2018 16:45", "10/03/2018 20:15"))

DF_1 <- DF_1 %>% mutate(Date1 = dmy_hm(Date1))


DF_2 = data.table(DF_2)
DF_1 = data.table(DF_1)

DF_2 = DF_2[, Date1:= date(Date1)]
DF_2 = DF_2[, Date2:= date(Date2)]
DF_1 = DF_1[, Date1:= date(Date1)]
DF_1[DF_2, on = c("ID","Date1") , nomatch=0L]


DF = DF_2[, list(num_mth = uniqueN(format(Date2, "%Y%m")), num_rec=.N,
          num_day = uniqueN(format(Date2, "%Y%m%d")),
          numrec_5d=length(ID[as.numeric(difftime(today(), Date2), units = "days")<=5])), 
          by=ID]
DF[, recpermonth := num_rec/num_mth][, recperday := num_rec/num_day][, recperday2 := num_mth/num_day/30]