Handling analyses and plotting histogram of variable with value for time of day

[Dil Singh]

I have a variable for the time of day at which the first patient arrived to a clinic, which is formatted as hh:mm:ss.

I wish to do two analyses of these data.

1) To calculate the median time of day at which the first attendee arrived. Obviously using "summ var, det" doesn't produce anything meaningful given how Stata handles time as a number (-1.89e+12 is what is produced) and wonder how this could be readily converted into a meaningful value of time.
2) I wish to plot a histogram with the time of day on this x axis and frequency on the y axis. However, the cut-offs for the times on the x axis are somewhat random times (e.g. 0713am) whereas it would be easier to interpret if this was evenly split between 0630 and 0930 as half an hour intervals.

Many thanks.

[Nick Cox]

Please give us a dataex of your time of day variable. A format of hh:mm:ss should refer to a display format but that is not reliable as indicating exactly how you are holding time of day.

[Dil Singh]

Hi Nick.

Thank you for your reply.

A dataex of the variable is as follows:

---

* Example generated by -dataex-. To install: ssc install dataex
clear
input str19 Specialty double patientintoclinic
"mixed acutes"             -1893428820000
"vascular"                 -1893428820000
"cardiac"                               .
"CS"                        -1.893429e+12
"cardiac"                               .
"neuro"                    -1.8934284e+12
"ortho"               -1893427500000.0002
"ortho"                    -1.8934299e+12
"ortho"                     -1.893429e+12
"cancel"                                .
"gen surg"                              .
"cancel"                                .
"elective eyes local"      -1.8934281e+12
"eyes local"               -1.8934284e+12
"ENT"                      -1.8934293e+12
"ent cancel "                           .
"gynae"                    -1.8934296e+12
"gensurg"                  -1.8934284e+12
"plastics"                 -1.8934287e+12
"paeds"                    -1.8934284e+12
"cancel"                                .
"plastic acute "            -1.893429e+12
"gen surg "                -1.8934269e+12
"mixed acutes"             -1.8934296e+12
"vascular"                 -1.8934287e+12
"cardiac"                               .
"UGI"                 -1893430200000.0002
"cx"                                    .
"mixed acutes"             -1.8934299e+12
"ortho "                   -1.8934299e+12
"ortho"                    -1.8934293e+12
"cancel"                                .
"ortho"                    -1.8934299e+12
"gen surg"                 -1.8934299e+12
"acute late start"                      .
"eyes"                     -1.8934284e+12
"dental"                   -1.8934284e+12
"cancel"                                .
"cancel"                                .
"gynae"                    -1.8934281e+12
"gen surg"                 -1.8934296e+12
"plastics"                  -1.893429e+12
"paeds"                    -1.8934287e+12
"urology"                  -1.8934284e+12
"plastic acute "      -1893429720000.0002
"acutes mixed "                         .
"acutes mixed (ICU)"                    .
"vascular"                 -1.8934272e+12
"cardiac"                               .
"CS"                       -1.8934296e+12
"thoracics"                -1.8934284e+12
"neuro"                    -1.8934281e+12
"ortho"                     -1.893429e+12
"ortho"                     -1.893429e+12
"ortho"                    -1.8934272e+12
"cancel"                                .
"gen surg"                 -1.8934296e+12
"cancel"                                .
"eyes local"               -1.8934272e+12
"TOPS"                                  .
"cancel"                                .
"cancel"                                .
"cancel"                                .
"colorectal"               -1893430740000
"plastics"                 -1.8934254e+12
"cancel"                                .
"urology"             -1893427799999.9998
"plastic acute "      -1893427500000.0002
"gen surg"                 -1893429420000
"mixed acutes"        -1893427500000.0002
"cancel"                                .
"cardiac"                               .
"CS"                                    .
"neuro"                                 .
"ortho"                                 .
"ortho "                   -1.8934272e+12
"ortho"                                 .
"ortho"                                 .
"gen surg"                              .
"paeds"                                 .
"cancel"                                .
"eyes"                                  .
"eyes"                                  .
"thoracics"                -1.8934287e+12
"ENT"                 -1893427799999.9998
"gynae"               -1893427799999.9998
"cancel"                                .
"plastics"                  -1.893429e+12
"cancel"                                .
"urology"             -1893427799999.9998
"plastic acute "            -1.893429e+12
"gen surg"                  -1.893429e+12
"mixed acutes"             -1.8934272e+12
"vascular"                 -1.8934284e+12
"cardiac"                               .
"maxfax"                   -1.8934299e+12
"cardiac"                               .
"neuro"                    -1.8934263e+12
"ortho "                   -1.8934263e+12
"ortho "                   -1.8934293e+12
end
format %tcHH:MM:SS patientintoclinic

---

I hope this is sufficient and many thanks again for your time.

Regards.

[Nick Cox]

Thanks for your example. I see some simple values and some more bizarre details. Starting with a list and then simplifying

. list

     +--------------------------------+
     |           Specialty   patien~c |
     |--------------------------------|
  1. |        mixed acutes   07:33:00 |
  2. |            vascular   07:33:00 |
  3. |             cardiac          . |
  4. |                  CS   07:30:00 |
  5. |             cardiac          . |
     |--------------------------------|
  6. |               neuro   07:40:00 |
  7. |               ortho   07:54:59 |
  8. |               ortho   07:15:00 |
  9. |               ortho   07:30:00 |
 10. |              cancel          . |
     |--------------------------------|
 11. |            gen surg          . |
 12. |              cancel          . |
 13. | elective eyes local   07:45:00 |
 14. |          eyes local   07:40:00 |
 15. |                 ENT   07:25:00 |
     |--------------------------------|
 16. |         ent cancel           . |
 17. |               gynae   07:20:00 |
 18. |             gensurg   07:40:00 |
 19. |            plastics   07:35:00 |
 20. |               paeds   07:40:00 |
     |--------------------------------|
 21. |              cancel          . |
 22. |      plastic acute    07:30:00 |
 23. |           gen surg    08:05:00 |
 24. |        mixed acutes   07:20:00 |
 25. |            vascular   07:35:00 |
     |--------------------------------|
 26. |             cardiac          . |
 27. |                 UGI   07:09:59 |
 28. |                  cx          . |
 29. |        mixed acutes   07:15:00 |
 30. |              ortho    07:15:00 |
     |--------------------------------|
 31. |               ortho   07:25:00 |
 32. |              cancel          . |
 33. |               ortho   07:15:00 |
 34. |            gen surg   07:15:00 |
 35. |    acute late start          . |
     |--------------------------------|
 36. |                eyes   07:40:00 |
 37. |              dental   07:40:00 |
 38. |              cancel          . |
 39. |              cancel          . |
 40. |               gynae   07:45:00 |
     |--------------------------------|
 41. |            gen surg   07:20:00 |
 42. |            plastics   07:30:00 |
 43. |               paeds   07:35:00 |
 44. |             urology   07:40:00 |
 45. |      plastic acute    07:17:59 |
     |--------------------------------|
 46. |       acutes mixed           . |
 47. |  acutes mixed (ICU)          . |
 48. |            vascular   08:00:00 |
 49. |             cardiac          . |
 50. |                  CS   07:20:00 |
     |--------------------------------|
 51. |           thoracics   07:40:00 |
 52. |               neuro   07:45:00 |
 53. |               ortho   07:30:00 |
 54. |               ortho   07:30:00 |
 55. |               ortho   08:00:00 |
     |--------------------------------|
 56. |              cancel          . |
 57. |            gen surg   07:20:00 |
 58. |              cancel          . |
 59. |          eyes local   08:00:00 |
 60. |                TOPS          . |
     |--------------------------------|
 61. |              cancel          . |
 62. |              cancel          . |
 63. |              cancel          . |
 64. |          colorectal   07:01:00 |
 65. |            plastics   08:30:00 |
     |--------------------------------|
 66. |              cancel          . |
 67. |             urology   07:50:00 |
 68. |      plastic acute    07:54:59 |
 69. |            gen surg   07:23:00 |
 70. |        mixed acutes   07:54:59 |
     |--------------------------------|
 71. |              cancel          . |
 72. |             cardiac          . |
 73. |                  CS          . |
 74. |               neuro          . |
 75. |               ortho          . |
     |--------------------------------|
 76. |              ortho    08:00:00 |
 77. |               ortho          . |
 78. |               ortho          . |
 79. |            gen surg          . |
 80. |               paeds          . |
     |--------------------------------|
 81. |              cancel          . |
 82. |                eyes          . |
 83. |                eyes          . |
 84. |           thoracics   07:35:00 |
 85. |                 ENT   07:50:00 |
     |--------------------------------|
 86. |               gynae   07:50:00 |
 87. |              cancel          . |
 88. |            plastics   07:30:00 |
 89. |              cancel          . |
 90. |             urology   07:50:00 |
     |--------------------------------|
 91. |      plastic acute    07:30:00 |
 92. |            gen surg   07:30:00 |
 93. |        mixed acutes   08:00:00 |
 94. |            vascular   07:40:00 |
 95. |             cardiac          . |
     |--------------------------------|
 96. |              maxfax   07:15:00 |
 97. |             cardiac          . |
 98. |               neuro   08:15:00 |
 99. |              ortho    08:15:00 |
100. |              ortho    07:25:00 |
     +--------------------------------+

. contract patient

. l

     +------------------+
     | patien~c   _freq |
     |------------------|
  1. | 07:01:00       1 |
  2. | 07:09:59       1 |
  3. | 07:15:00       6 |
  4. | 07:17:59       1 |
  5. | 07:20:00       5 |
     |------------------|
  6. | 07:23:00       1 |
  7. | 07:25:00       3 |
  8. | 07:30:00       9 |
  9. | 07:33:00       2 |
 10. | 07:35:00       4 |
     |------------------|
 11. | 07:40:00       9 |
 12. | 07:45:00       3 |
 13. | 07:50:00       4 |
 14. | 07:54:59       3 |
 15. | 08:00:00       5 |
     |------------------|
 16. | 08:05:00       1 |
 17. | 08:15:00       2 |
 18. | 08:30:00       1 |
 19. |        .      39 |
     +------------------+

I see that every reported time is just hours and minutes apart from some ending with 59 seconds, which just looks some kind of precision problem.

We might as well use our own measures, minutes after midnight.

 

. gen strversion = string(patient, "%tchh_mm_ss")

. l

     +-----------------------------+
     | patien~c   _freq   strver~n |
     |-----------------------------|
  1. | 07:01:00       1      7 1 0 |
  2. | 07:09:59       1     7 9 59 |
  3. | 07:15:00       6     7 15 0 |
  4. | 07:17:59       1    7 17 59 |
  5. | 07:20:00       5     7 20 0 |
     |-----------------------------|
  6. | 07:23:00       1     7 23 0 |
  7. | 07:25:00       3     7 25 0 |
  8. | 07:30:00       9     7 30 0 |
  9. | 07:33:00       2     7 33 0 |
 10. | 07:35:00       4     7 35 0 |
     |-----------------------------|
 11. | 07:40:00       9     7 40 0 |
 12. | 07:45:00       3     7 45 0 |
 13. | 07:50:00       4     7 50 0 |
 14. | 07:54:59       3    7 54 59 |
 15. | 08:00:00       5      8 0 0 |
     |-----------------------------|
 16. | 08:05:00       1      8 5 0 |
 17. | 08:15:00       2     8 15 0 |
 18. | 08:30:00       1     8 30 0 |
 19. |        .      39          . |
     +-----------------------------+

. split strversion, destring
variables born as string: 
strversion1  strversion2  strversion3
strversion1: all characters numeric; replaced as byte
(1 missing value generated)
strversion2: all characters numeric; replaced as byte
(1 missing value generated)
strversion3: all characters numeric; replaced as byte
(1 missing value generated)

. gen better = 60 * strversion1 + strversion2 + (strversion3 == 59)
(1 missing value generated)

. list

     +-----------------------------------------------------------------------+
     | patien~c   _freq   strver~n   strver~1   strver~2   strver~3   better |
     |-----------------------------------------------------------------------|
  1. | 07:01:00       1      7 1 0          7          1          0      421 |
  2. | 07:09:59       1     7 9 59          7          9         59      430 |
  3. | 07:15:00       6     7 15 0          7         15          0      435 |
  4. | 07:17:59       1    7 17 59          7         17         59      438 |
  5. | 07:20:00       5     7 20 0          7         20          0      440 |
     |-----------------------------------------------------------------------|
  6. | 07:23:00       1     7 23 0          7         23          0      443 |
  7. | 07:25:00       3     7 25 0          7         25          0      445 |
  8. | 07:30:00       9     7 30 0          7         30          0      450 |
  9. | 07:33:00       2     7 33 0          7         33          0      453 |
 10. | 07:35:00       4     7 35 0          7         35          0      455 |
     |-----------------------------------------------------------------------|
 11. | 07:40:00       9     7 40 0          7         40          0      460 |
 12. | 07:45:00       3     7 45 0          7         45          0      465 |
 13. | 07:50:00       4     7 50 0          7         50          0      470 |
 14. | 07:54:59       3    7 54 59          7         54         59      475 |
 15. | 08:00:00       5      8 0 0          8          0          0      480 |
     |-----------------------------------------------------------------------|
 16. | 08:05:00       1      8 5 0          8          5          0      485 |
 17. | 08:15:00       2     8 15 0          8         15          0      495 |
 18. | 08:30:00       1     8 30 0          8         30          0      510 |
 19. |        .      39          .          .          .          .        . |
     +-----------------------------------------------------------------------+

. spikeplot better [fw=_freq] , xla(420 "07:00" 450 "07:30" 480 "08:00" 510 "08:30")

Naturally you might eant to bin those values more coarsely. See https://journals.sagepub.com/doi/pdf...867X1801800311

[Dil Singh]

Hi Nick.

That's exceptionally helpful and thanks again for taking the time.

Any idea how I would be able to describe a median time?

Using ". summ var, det" doesn't seem to work for this data type.

[Nick Cox]

You don't give precise code -- which variable you tried to summarize -- or any evidence for "doesn't seem to work".

I ran this code on your example data and get shown 7:35 both times. Perhaps the trick you're missing is representing results in conventional time format.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str19 Specialty double patientintoclinic
"mixed acutes"             -1893428820000
"vascular"                 -1893428820000
"cardiac"                               .
"CS"                        -1.893429e+12
"cardiac"                               .
"neuro"                    -1.8934284e+12
"ortho"               -1893427500000.0002
"ortho"                    -1.8934299e+12
"ortho"                     -1.893429e+12
"cancel"                                .
"gen surg"                              .
"cancel"                                .
"elective eyes local"      -1.8934281e+12
"eyes local"               -1.8934284e+12
"ENT"                      -1.8934293e+12
"ent cancel "                           .
"gynae"                    -1.8934296e+12
"gensurg"                  -1.8934284e+12
"plastics"                 -1.8934287e+12
"paeds"                    -1.8934284e+12
"cancel"                                .
"plastic acute "            -1.893429e+12
"gen surg "                -1.8934269e+12
"mixed acutes"             -1.8934296e+12
"vascular"                 -1.8934287e+12
"cardiac"                               .
"UGI"                 -1893430200000.0002
"cx"                                    .
"mixed acutes"             -1.8934299e+12
"ortho "                   -1.8934299e+12
"ortho"                    -1.8934293e+12
"cancel"                                .
"ortho"                    -1.8934299e+12
"gen surg"                 -1.8934299e+12
"acute late start"                      .
"eyes"                     -1.8934284e+12
"dental"                   -1.8934284e+12
"cancel"                                .
"cancel"                                .
"gynae"                    -1.8934281e+12
"gen surg"                 -1.8934296e+12
"plastics"                  -1.893429e+12
"paeds"                    -1.8934287e+12
"urology"                  -1.8934284e+12
"plastic acute "      -1893429720000.0002
"acutes mixed "                         .
"acutes mixed (ICU)"                    .
"vascular"                 -1.8934272e+12
"cardiac"                               .
"CS"                       -1.8934296e+12
"thoracics"                -1.8934284e+12
"neuro"                    -1.8934281e+12
"ortho"                     -1.893429e+12
"ortho"                     -1.893429e+12
"ortho"                    -1.8934272e+12
"cancel"                                .
"gen surg"                 -1.8934296e+12
"cancel"                                .
"eyes local"               -1.8934272e+12
"TOPS"                                  .
"cancel"                                .
"cancel"                                .
"cancel"                                .
"colorectal"               -1893430740000
"plastics"                 -1.8934254e+12
"cancel"                                .
"urology"             -1893427799999.9998
"plastic acute "      -1893427500000.0002
"gen surg"                 -1893429420000
"mixed acutes"        -1893427500000.0002
"cancel"                                .
"cardiac"                               .
"CS"                                    .
"neuro"                                 .
"ortho"                                 .
"ortho "                   -1.8934272e+12
"ortho"                                 .
"ortho"                                 .
"gen surg"                              .
"paeds"                                 .
"cancel"                                .
"eyes"                                  .
"eyes"                                  .
"thoracics"                -1.8934287e+12
"ENT"                 -1893427799999.9998
"gynae"               -1893427799999.9998
"cancel"                                .
"plastics"                  -1.893429e+12
"cancel"                                .
"urology"             -1893427799999.9998
"plastic acute "            -1.893429e+12
"gen surg"                  -1.893429e+12
"mixed acutes"             -1.8934272e+12
"vascular"                 -1.8934284e+12
"cardiac"                               .
"maxfax"                   -1.8934299e+12
"cardiac"                               .
"neuro"                    -1.8934263e+12
"ortho "                   -1.8934263e+12
"ortho "                   -1.8934293e+12
end
format %tcHH:MM:SS patientintoclinic

drop if missing(patient)

su patient, detail 

di %tcHH:MM:SS r(p50)
 

gen strversion = string(patient, "%tchh_mm_ss")
split strversion, destring
gen better = 60 * strversion1 + strversion2 + (strversion3 == 59)

su better, detail 

di floor(r(p50)/60) ":" mod(r(p50), 60)