0

I have a data frame that stores call records from a call center. My purpose is to count how many records exist per time interval, for example, in a time interval of 30 minutes there may be three call records (that is, three calls entered within that specific time interval); In case there are no records for that time interval, then my counter should show me a zero value.

This post was useful but I do not achieve that when there are no records in a time interval it shows me a zero value.

This is the structure of my call_log:

Classes ‘data.table’ and 'data.frame':  24416 obs. of  23 variables:
$ closecallid   : int  1145000 1144998 1144997 1144996 1144995 1144991 1144989 1144987 1144986 1144984 ...
$ lead_id       : int  1167647 1167645 1167644 1167643 1167642 1167638 1167636 1167634 1167633 1167631 ...
$ list_id       :integer64 998 998 998 998 998 998 998 998 ... 
$ campaign_id   : chr  "212120" "212120" "212120" "212120" ...
$ call_date     : POSIXct, format: "2019-08-26 20:25:30" "2019-08-26 19:32:28" "2019-08-26 19:27:03" ...
$ start_epoch   : POSIXct, format: "2019-08-26 20:25:30" "2019-08-26 19:32:28" "2019-08-26 19:27:03" ...
$ end_epoch     : POSIXct, format: "2019-08-26 20:36:25" "2019-08-26 19:44:52" "2019-08-26 19:40:23" ...
$ length_in_sec : int  655 744 800 1109 771 511 640 153 757 227 ...
$ status        : chr  "Ar" "Ar" "Ar" "Ar" ...
$ phone_code    : chr  "1" "1" "1" "1" ...
$ phone_number  : chr  "17035555" "43667342" "3135324788" "3214255222" ...
$ user          : chr  "jfino" "jfino" "jfino" "jfino" ...
$ comments      : chr  "AUTO" "AUTO" "AUTO" "AUTO" ...
$ processed     : chr  "N" "N" "N" "N" ...
$ queue_seconds : num  0 524 692 577 238 95 104 0 0 0 ...
$ user_group    : chr  "CEAS" "CEAS" "CEAS" "CEAS" ...
$ xfercallid    : int  0 0 0 0 0 0 0 0 0 0 ...
$ term_reason   : chr  "CALLER" "CALLER" "CALLER" "AGENT" ...
$ uniqueid      : chr  "1566869112.557969" "1566865941.557957" "1566865611.557952" "1566865127.557947" ...
$ agent_only    : chr  "" "" "" "" ...
$ queue_position: int  1 2 2 2 1 2 1 1 1 1 ...
$ called_count  : int  1 1 1 1 1 1 1 1 1 1 ...

And, this is my code

df <- setDT(call_log)[ , list(number_customers_arrive = sum(called_count)), by = cut(call_date, "30 min")]

Thanks in advance.

Joan
  • 269
  • 1
  • 3
  • 10

1 Answers1

1

Since there is not a reproducible example, I attempt the solution on a simulated data frame. First we create a log of calls with ID and time:

library(lubridate)
library(dplyr)
library(magrittr)
set.seed(123)

# Generate 100 random call times during a day
calls.df <- data.frame(id=seq(1,100,1), calltime=sample(seq(as.POSIXct('2019/10/01'),
     as.POSIXct('2019/10/02'), by="min"), 100))

There may not be all intervals represented in your call data so generate a sequence of all 30 minute bins in case:

full.df <- data.frame(bin=seq(as.POSIXct('2019/10/01'), as.POSIXct('2019/10/02'), by="30 min"))

Next tally up counts of calls in represented bins:

calls.df %>% arrange(calltime) %>% mutate(diff=interval(lag(calltime),calltime)) %>% 
     mutate(mins=diff@.Data/60) %>% select(-diff) %>% 
     mutate(bin=floor_date(calltime, unit="30 minutes")) %>% 
     group_by(bin) %>% tally() -> orig.counts

Now make sure there are zeroes for unrepresented bins:

right_join(orig.counts,full.df,by="bin") %>% mutate(count=ifelse(is.na(n), 0, n))

  # A tibble: 49 x 3
     bin                     n count
     <dttm>              <int> <dbl>
   1 2019-10-01 00:00:00     2     2
   2 2019-10-01 00:30:00     1     1
   3 2019-10-01 01:00:00     2     2
   4 2019-10-01 01:30:00    NA     0
   5 2019-10-01 02:00:00     2     2
   6 2019-10-01 02:30:00     4     4
   7 2019-10-01 03:00:00     1     1
   8 2019-10-01 03:30:00     1     1
   9 2019-10-01 04:00:00     2     2
  10 2019-10-01 04:30:00     1     1
  # ... with 39 more rows

Hope this is helpful for you.

mysteRious
  • 4,102
  • 2
  • 16
  • 36