I work for a hospital and we're trying to get a handle on operating room usage. The room situation is getting out of control. We have providers doing several concurrent procedures and managing several residents at once. I need to figure out how much that is happening. I've read several solutions but they all seem to operate on two values instead of many, or be summing an existing value.
The first problem to solve was to figure out how many real-world minutes a provider is working. Overlapping procedures would make it look like one person was in the operating room over 24 hours a day, which obviously isn't possible.
Thanks to this awesome solution by Valentino I've been able to calculate overlapping times.
Now I need to know what the max number of concurrent procedures is for a provider in one day. For example, if Dr. Jones starts at 8am and walks into a situation where they have their own procedure, but also two residents need assistance, that's three procedures at the same time. But maybe late in the day, in a different block of overlapping time, there's a surge. Now Dr. Jones is running between three different operating rooms and still managing two residents, that jumps up to five concurrent procedures.
Valentino's solution sorts the rows in the initial dataframe into groups of overlapping times:
null AN_DATE AN_PROV_ID USER_ID PROV_NAME AN_BEGIN_LOCAL_DTTM AN_END_LOCAL_DTTM MINS group
2 2019-07-17 700 JB007 J. Bond 2019-07-17 08:00:00 2019-07-17 09:00:00 01:00:00 1
3 2019-07-17 700 JB007 J. Bond 2019-07-17 08:00:00 2019-07-17 09:30:00 01:30:00 1
5 2019-07-17 700 JB007 J. Bond 2019-07-17 09:00:00 2019-07-17 10:15:00 01:15:00 1
6 2019-07-17 700 JB007 J. Bond 2019-07-17 13:00:00 2019-07-17 14:00:00 01:00:00 2
7 2019-07-17 700 JB007 J. Bond 2019-07-17 12:30:00 2019-07-17 13:30:00 01:00:00 2
To get the number of concurrent procedures, can I use the same merged dataframe used to calculate groups? At the point that these calculations are possible, merged dataframe looks like this:
time what running newwin group
2 2019-07-17 08:00:00 1 1 True 1
3 2019-07-17 08:00:00 1 2 False 1
5 2019-07-17 09:00:00 1 3 False 1
2 2019-07-17 09:00:00 -1 2 False 1
3 2019-07-17 09:30:00 -1 1 False 1
5 2019-07-17 10:15:00 -1 0 False 1
7 2019-07-17 12:30:00 1 1 True 2
6 2019-07-17 13:00:00 1 2 False 2
7 2019-07-17 13:30:00 -1 1 False 2
6 2019-07-17 14:00:00 -1 0 False 2
I thought at first that I could simply count the number of ones (1) on each group. But that is not actually concurrency. In the example below, the block of time is correctly merged into a start time of 8am and end time of 10:15am because of overlapping procedures, but the max number of concurrent procedures for that block is 2:
8am 9am 10am
proc1 ########
proc2 ############
proc3 ###########
For the block below the max concurrency would be 3:
8am 9am 10am
proc1 ########
proc2 ############
proc3 ######
proc4 ###########
proc5 ##
It seems like I should be able to use pandas.Interval.overlaps, but that operates on two intervals. I'll have 1 to x intervals. So that led me to consider pandas.Series.cummax. But I don't actually have a value to be comparing. In fact every solution I've found either operates on two values or the summation of an already present value. So would I somehow getting the cummax of Interval.overlaps? That seems way too abstract. I'm too new to this and I don't understand how these pieces fit together so I appreciate any insights.