Stata Analyzing Queue Length for Different Stores

user_5499487 0
Viewed 57

Suppose I have a list of stores (e.g. Walmart, Costco, etc.), as well as: 1. data on their open hours, 2. data on each customer that enters the store, and 3. data on each customer that pays and leaves the store. (For 2 and 3, customers can wait outside of the store before it opens and leave after it closes, respectively; this is not precluded in the data). How might one go about calculating the queue (i.e. the number of customers in the store) at every hour mark during the open hours?

Originally, I thought I might look at each data point and find out the customers at this specific hour mark (this isn't too tricky). However, it does not account for the other hours that the store is open. In other words, saying that there are 4 people in the store at 3 p.m. is insufficient, as it does not describe the hours of 4, 5, 6 p.m., etc., even if the number of people remains the same. Simply put, I'm not sure how to find the number of customers during each hour of store open hours. Additionally, I would assume that I would need to create a different dataframe to store this information, as its size would not match up with the data set that I currently have.

I've pasted some sample data below:

input str15 store_name double(open close customer_arrival customer_leave)
"Walmart" 15may2020 13:00:00 15may2020 22:00:00 15may2020 20:40:00 15may2020 22:51:00
"Costco" 15may2020 19:00:00 16may2020 4:00:00 15may2020 21:31:00 16may2020 1:10:00
"Costco" 15may2020 19:00:00 16may2020 4:00:00 16may2020 1:32:00 16may2020 7:40:00
"Costco" 15may2020 19:00:00 16may2020 4:00:00 15may2020 20:52:00 16may2020 00:42:00
"Target" 16may2020 03:00:00 16may2020 12:00:00 16may2020 02:13:00 16may2020 04:47:00
"Target" 16may2020 03:00:00 16may2020 12:00:00 16may2020 07:28:00 16may2020 13:55:00

For example, for Walmart on the given day, there were no individuals in the store from 13:00 to 20:40, which means that the time buckets for 13:00 to 20:00 would indicate 0. Then for the buckets 21:00 and 22:00, there would be 1 person in queue, and then none again at 23:00.

Any suggestions as to how to proceed would be sincerely appreciated. If I've omitted any details/if I could be more clear, please let me know. Cheers.

stata
1 Answers

Here's some technique to do it. It will make the dataset a lot larger at first, so if your dataset at start is already large you might run into memory problems.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str15 store_name double(open close customer_arrival customer_leave)
"Walmart" 1.9051668e+12  1.905201e+12 1.9051944e+12 1905202260000
"Costco"  1.9051884e+12 1.9052208e+12 1905197460000 1.9052106e+12
"Costco"  1.9051884e+12 1.9052208e+12 1905211920000  1.905234e+12
"Costco"  1.9051884e+12 1.9052208e+12 1905195120000 1905208920000
"Target"  1.9052172e+12 1.9052496e+12 1905214380000 1905223620000
"Target"  1.9052172e+12 1.9052496e+12 1905233280000 1.9052565e+12
end
format %tc open
format %tc close
format %tc customer_arrival
format %tc customer_leave

// create customer id
gen customer_id = _n

// create hourly dataset
gen hours = cond(hh(open) < hh(close), hh(close) - hh(open), hh(close) + 24 - hh(open))
expand hours
sort store_name open close
bysort store_name customer_id (open close): gen double start = open + 3600000 * (_n - 1)
bysort store_name customer_id (open close): gen double end = start + 3600000
format start end %tc
drop open close hours

// check if customer is in store 
gen in_store = (customer_arrival < start & customer_leave >= start) | (customer_arrival >= start & customer_arrival < end)

// sum customers in store per hour
collapse (sum) in_store, by(store_name start end)

list, sepby(store_name) noobs

  +---------------------------------------------------------------+
  | store_~e                start                  end   in_store |
  |---------------------------------------------------------------|
  |   Costco   15may2020 19:00:00   15may2020 20:00:00          0 |
  |   Costco   15may2020 20:00:00   15may2020 21:00:00          1 |
  |   Costco   15may2020 21:00:00   15may2020 22:00:00          2 |
  |   Costco   15may2020 22:00:00   15may2020 23:00:00          2 |
  |   Costco   15may2020 23:00:00   16may2020 00:00:00          2 |
  |   Costco   16may2020 00:00:00   16may2020 01:00:00          2 |
  |   Costco   16may2020 01:00:00   16may2020 02:00:00          2 |
  |   Costco   16may2020 02:00:00   16may2020 03:00:00          1 |
  |   Costco   16may2020 03:00:00   16may2020 04:00:00          1 |
  |---------------------------------------------------------------|
  |   Target   16may2020 03:00:00   16may2020 04:00:00          1 |
  |   Target   16may2020 04:00:00   16may2020 05:00:00          1 |
  |   Target   16may2020 05:00:00   16may2020 06:00:00          0 |
  |   Target   16may2020 06:00:00   16may2020 07:00:00          0 |
  |   Target   16may2020 07:00:00   16may2020 08:00:00          1 |
  |   Target   16may2020 08:00:00   16may2020 09:00:00          1 |
  |   Target   16may2020 09:00:00   16may2020 10:00:00          1 |
  |   Target   16may2020 10:00:00   16may2020 11:00:00          1 |
  |   Target   16may2020 11:00:00   16may2020 12:00:00          1 |
  |---------------------------------------------------------------|
  |  Walmart   15may2020 13:00:00   15may2020 14:00:00          0 |
  |  Walmart   15may2020 14:00:00   15may2020 15:00:00          0 |
  |  Walmart   15may2020 15:00:00   15may2020 16:00:00          0 |
  |  Walmart   15may2020 16:00:00   15may2020 17:00:00          0 |
  |  Walmart   15may2020 17:00:00   15may2020 18:00:00          0 |
  |  Walmart   15may2020 18:00:00   15may2020 19:00:00          0 |
  |  Walmart   15may2020 19:00:00   15may2020 20:00:00          0 |
  |  Walmart   15may2020 20:00:00   15may2020 21:00:00          1 |
  |  Walmart   15may2020 21:00:00   15may2020 22:00:00          1 |
  +---------------------------------------------------------------+
user_117702860
Related