I am having trouble calculating the median number of a column, where each row is a different hour of a day, but there may be missing dates/hours that I'd like to count as 0 in my median function.
It's difficult to explain, so I put a table demonstrating my issue below, covering 5 days of the year (1/1/2023-1/5/2023). NumStudies is the number of studies done during the given date/time. I would like to calculate for the given timeframe (1/1-1/5 in this case), what is the median number of studies done for every hour of the day? If there is no date/time in the table (for example on 1/1 there were no other studies except at 1:00 pm and 8:00 pm) I would like that to count as 0 when I calculate the median studies done for each hour for the given time frame.
Source Data:
| DateTime | NumStudies |
| 1/1/23 1:00 PM | 1 |
| 1/2/23 1:00 PM | 3 |
| 1/3/23 1:00 PM | 5 |
| 1/5/23 1:00 PM | 7 |
| 1/3/23 2:00 PM | 1 |
| 1/4/23 2:00 PM | 2 |
| 1/5/23 2:00 PM | 3 |
| 1/5/23 3:00 PM | 5 |
| 1/5/23 4:00 PM | 10 |
| 1/5/23 5:00 PM | 20 |
| 1/5/23 6:00 PM | 30 |
| 1/5/23 7:00 PM | 40 |
| 1/1/23 8:00 PM | 100 |
| 1/2/23 8:00 PM | 110 |
| 1/3/23 8:00 PM | 120 |
| 1/4/23 8:00 PM | 130 |
| 1/5/23 8:00 PM | 140 |
What I'd like is a measure which calculates the median of the number of studies for each hour, accounting for date/times that may be missing. The results of the median function in this example for 1/1/2023 through 1/5/2023 would show:
Results:
| Hour | MedianNumStudies |
| 12:00 AM | 0 |
| 1:00 AM | 0 |
| 2:00 AM | 0 |
| 3:00 AM | 0 |
| 4:00 AM | 0 |
| 5:00 AM | 0 |
| 6:00 AM | 0 |
| 7:00 AM | 0 |
| 8:00 AM | 0 |
| 9:00 AM | 0 |
| 10:00 AM | 0 |
| 11:00 AM | 0 |
| 12:00 PM | 0 |
| 1:00 PM | 3 (i.e. median of 1,3,5,0 (from 1/4/23) ,7) |
| 2:00 PM | 1 (median of 1,2,3,0,0) |
| 3:00 PM | 0 |
| 4:00 PM | 0 |
| 5:00 PM | 0 |
| 6:00 PM | 0 |
| 7:00 PM | 0 |
| 8:00 PM | 120 |
| 9:00 PM | 0 |
| 10:00 PM | 0 |
| 11:00 PM | 0 |
I've tried a number of different methods but have not been able to come up with anything that works. Any help is much appreciated.
Thanks in advance!
