I am working with HighCharts and the stacked bar chart. The solution I have been tasked with is to have as few trips back to the server to filter a given data set. The data is only refreshed if the X-Axis time frame changes. A user will have a set of filters that they can select via hierarchical checkbox system.
I have been working with D3.js and Underscore.js to handle the array of JSON I am getting back from the server. However, I am having difficulty understanding how to group the data and represent it in a stacked chart. I found a cool aggregation extension for Underscore, but I am not sure how to setup multiple groupings. I think I would have to map each group then group again. Both libraries seem to do what I want.
Here is the data we are working with:
[
{
"CheckInTime":"2015-06-04T09:25:13.297",
"CheckinDate":"2015-06-04",
"Week":"23",
"Day":"4",
"DayOfWeek":"5",
"Month":"6",
"Year":"2015",
"TwentyFourHour":"9",
"Hour":"9 AM",
"Period":"AM",
"CheckOutTime":"2015-06-04T09:42:46.927",
"TotalTimeInOffice":"1053",
"LocationId":"7",
"LocationName":"Exam5",
"VisitId":"62270",
"B360VisitTypeId":"11",
"VisitTypeName":"Follow Up Visit",
"PatientTimeInLocation":"741",
"PatientLocationHistoryTimeStamp":"2015-06-04T09:29:14.530",
"PatientNextLocationTime":"2015-06-04T09:41:35.757",
"UserProfileId":"2",
"GroupId":"12",
"GroupName":"Physicians",
"StaffTimeInLocation":"330",
"StaffLocationHistoryTimeStamp":"2015-06-04T09:36:05.373",
"StaffNextLocationTime":"2015-06-04T09:41:35.823",
"StaffLocationStartTime":"2015-06-04T09:36:05.373",
"StaffLocationEndTime":"2015-06-04T09:41:35.757",
"TotalPatientWaitTime":"241",
"TotalPatientWaitTimeInRoomForStaff":"411",
"TotalTimeWithPatient":"330",
"TotalTimeToCheckout":"71"
},
{
"CheckInTime":"2015-06-04T09:25:13.297",
"CheckinDate":"2015-06-04",
"Week":"23",
"Day":"4",
"DayOfWeek":"5",
"Month":"6",
"Year":"2015",
"TwentyFourHour":"9",
"Hour":"9 AM",
"Period":"AM",
"CheckOutTime":"2015-06-04T09:42:46.927",
"TotalTimeInOffice":"1053",
"LocationId":"7",
"LocationName":"Exam5",
"VisitId":"62270",
"B360VisitTypeId":"11",
"VisitTypeName":"Follow Up Visit",
"PatientTimeInLocation":"741",
"PatientLocationHistoryTimeStamp":"2015-06-04T09:29:14.530",
"PatientNextLocationTime":"2015-06-04T09:41:35.757",
"UserProfileId":"21",
"GroupId":"13",
"GroupName":"Medical Assistants",
"StaffTimeInLocation":"205",
"StaffLocationHistoryTimeStamp":"2015-06-04T09:29:19.527",
"StaffNextLocationTime":"2015-06-04T09:32:44.970",
"StaffLocationStartTime":"2015-06-04T09:29:19.527",
"StaffLocationEndTime":"2015-06-04T09:32:44.970",
"TotalPatientWaitTime":"241",
"TotalPatientWaitTimeInRoomForStaff":"411",
"TotalTimeWithPatient":"205",
"TotalTimeToCheckout":"71"
},
{
"CheckInTime":"2015-06-04T10:18:29.130",
"CheckinDate":"2015-06-04",
"Week":"23",
"Day":"4",
"DayOfWeek":"5",
"Month":"6",
"Year":"2015",
"TwentyFourHour":"10",
"Hour":"10 AM",
"Period":"AM",
"CheckOutTime":"2015-06-04T11:58:18.783",
"TotalTimeInOffice":"5989",
"LocationId":"12",
"LocationName":"Exam1",
"VisitId":"62430",
"B360VisitTypeId":"18",
"VisitTypeName":"NP Pulmonary Consultation",
"PatientTimeInLocation":"1232",
"PatientLocationHistoryTimeStamp":"2015-06-04T11:36:39.467",
"PatientNextLocationTime":"2015-06-04T11:57:11.930",
"UserProfileId":"25",
"GroupId":"13",
"GroupName":"Medical Assistants",
"StaffTimeInLocation":"241",
"StaffLocationHistoryTimeStamp":"2015-06-04T11:44:35.443",
"StaffNextLocationTime":"2015-06-04T11:48:36.093",
"StaffLocationStartTime":"2015-06-04T11:44:35.443",
"StaffLocationEndTime":"2015-06-04T11:48:36.093",
"TotalPatientWaitTime":"4690",
"TotalPatientWaitTimeInRoomForStaff":"476",
"TotalTimeWithPatient":"717",
"TotalTimeToCheckout":"67"
},
{
"CheckInTime":"2015-06-04T10:18:29.130",
"CheckinDate":"2015-06-04",
"Week":"23",
"Day":"4",
"DayOfWeek":"5",
"Month":"6",
"Year":"2015",
"TwentyFourHour":"10",
"Hour":"10 AM",
"Period":"AM",
"CheckOutTime":"2015-06-04T11:58:18.783",
"TotalTimeInOffice":"5989",
"LocationId":"12",
"LocationName":"Exam1",
"VisitId":"62430",
"B360VisitTypeId":"18",
"VisitTypeName":"NP Pulmonary Consultation",
"PatientTimeInLocation":"1232",
"PatientLocationHistoryTimeStamp":"2015-06-04T11:36:39.467",
"PatientNextLocationTime":"2015-06-04T11:57:11.930",
"UserProfileId":"25",
"GroupId":"13",
"GroupName":"Medical Assistants",
"StaffTimeInLocation":"576",
"StaffLocationHistoryTimeStamp":"2015-06-04T11:34:34.210",
"StaffNextLocationTime":"2015-06-04T11:44:10.407",
"StaffLocationStartTime":"2015-06-04T11:36:39.467",
"StaffLocationEndTime":"2015-06-04T11:44:10.407",
"TotalPatientWaitTime":"4690",
"TotalPatientWaitTimeInRoomForStaff":"476",
"TotalTimeWithPatient":"717",
"TotalTimeToCheckout":"67"
},
{
"CheckInTime":"2015-06-04T10:25:51.773",
"CheckinDate":"2015-06-04",
"Week":"23",
"Day":"4",
"DayOfWeek":"5",
"Month":"6",
"Year":"2015",
"TwentyFourHour":"10",
"Hour":"10 AM",
"Period":"AM",
"CheckOutTime":"2015-06-04T10:45:13.737",
"TotalTimeInOffice":"1162",
"LocationId":"8",
"LocationName":"Exam4",
"VisitId":"63077",
"B360VisitTypeId":"11",
"VisitTypeName":"Follow Up Visit",
"PatientTimeInLocation":"777",
"PatientLocationHistoryTimeStamp":"2015-06-04T10:31:31.477",
"PatientNextLocationTime":"2015-06-04T10:44:28.033",
"UserProfileId":"8",
"GroupId":"7",
"GroupName":"Physician Assistants",
"StaffTimeInLocation":"611",
"StaffLocationHistoryTimeStamp":"2015-06-04T10:36:22.217",
"StaffNextLocationTime":"2015-06-04T10:46:33.357",
"StaffLocationStartTime":"2015-06-04T10:36:22.217",
"StaffLocationEndTime":"2015-06-04T10:44:28.033",
"TotalPatientWaitTime":"340",
"TotalPatientWaitTimeInRoomForStaff":"291",
"TotalTimeWithPatient":"486",
"TotalTimeToCheckout":"45"
},
{
"CheckInTime":"2015-06-04T10:25:51.773",
"CheckinDate":"2015-06-04",
"Week":"23",
"Day":"4",
"DayOfWeek":"5",
"Month":"6",
"Year":"2015",
"TwentyFourHour":"10",
"Hour":"10 AM",
"Period":"AM",
"CheckOutTime":"2015-06-04T10:45:13.737",
"TotalTimeInOffice":"1162",
"LocationId":"8",
"LocationName":"Exam4",
"VisitId":"63077",
"B360VisitTypeId":"11",
"VisitTypeName":"Follow Up Visit",
"PatientTimeInLocation":"777",
"PatientLocationHistoryTimeStamp":"2015-06-04T10:31:31.477",
"PatientNextLocationTime":"2015-06-04T10:44:28.033",
"UserProfileId":"21",
"GroupId":"13",
"GroupName":"Medical Assistants",
"StaffTimeInLocation":"195",
"StaffLocationHistoryTimeStamp":"2015-06-04T10:31:36.507",
"StaffNextLocationTime":"2015-06-04T10:34:51.777",
"StaffLocationStartTime":"2015-06-04T10:31:36.507",
"StaffLocationEndTime":"2015-06-04T10:34:51.777",
"TotalPatientWaitTime":"340",
"TotalPatientWaitTimeInRoomForStaff":"291",
"TotalTimeWithPatient":"195",
"TotalTimeToCheckout":"45"
}
...
{
"CheckInTime":"2015-06-04T17:07:51.747",
"CheckinDate":"2015-06-04",
"Week":"23",
"Day":"4",
"DayOfWeek":"5",
"Month":"6",
"Year":"2015",
"TwentyFourHour":"17",
"Hour":"5 PM",
"Period":"PM",
"CheckOutTime":"2015-06-04T17:47:48.720",
"TotalTimeInOffice":"2397",
"LocationId":"13",
"LocationName":"Exam2",
"VisitId":"62816",
"B360VisitTypeId":"11",
"VisitTypeName":"Follow Up Visit",
"PatientTimeInLocation":"1453",
"PatientLocationHistoryTimeStamp":"2015-06-04T17:16:55.890",
"PatientNextLocationTime":"2015-06-04T17:41:08.270",
"UserProfileId":"8",
"GroupId":"7",
"GroupName":"Physician Assistants",
"StaffTimeInLocation":"731",
"StaffLocationHistoryTimeStamp":"2015-06-04T17:25:21.887",
"StaffNextLocationTime":"2015-06-04T17:37:32.993",
"StaffLocationStartTime":"2015-06-04T17:25:21.887",
"StaffLocationEndTime":"2015-06-04T17:37:32.993",
"TotalPatientWaitTime":"544",
"TotalPatientWaitTimeInRoomForStaff":"506",
"TotalTimeWithPatient":"731",
"TotalTimeToCheckout":"185"
},
{
"CheckInTime":"2015-06-04T17:07:51.747",
"CheckinDate":"2015-06-04",
"Week":"23",
"Day":"4",
"DayOfWeek":"5",
"Month":"6",
"Year":"2015",
"TwentyFourHour":"17",
"Hour":"5 PM",
"Period":"PM",
"CheckOutTime":"2015-06-04T17:47:48.720",
"TotalTimeInOffice":"2397",
"LocationId":"13",
"LocationName":"Exam2",
"VisitId":"62816",
"B360VisitTypeId":"11",
"VisitTypeName":"Follow Up Visit",
"PatientTimeInLocation":"1453",
"PatientLocationHistoryTimeStamp":"2015-06-04T17:16:55.890",
"PatientNextLocationTime":"2015-06-04T17:41:08.270",
"UserProfileId":"25",
"GroupId":"13",
"GroupName":"Medical Assistants",
"StaffTimeInLocation":"281",
"StaffLocationHistoryTimeStamp":"2015-06-04T17:17:15.890",
"StaffNextLocationTime":"2015-06-04T17:21:56.390",
"StaffLocationStartTime":"2015-06-04T17:17:15.890",
"StaffLocationEndTime":"2015-06-04T17:21:56.390",
"TotalPatientWaitTime":"544",
"TotalPatientWaitTimeInRoomForStaff":"506",
"TotalTimeWithPatient":"1648",
"TotalTimeToCheckout":"185"
},
{
"CheckInTime":"2015-06-04T17:07:51.747",
"CheckinDate":"2015-06-04",
"Week":"23",
"Day":"4",
"DayOfWeek":"5",
"Month":"6",
"Year":"2015",
"TwentyFourHour":"17",
"Hour":"5 PM",
"Period":"PM",
"CheckOutTime":"2015-06-04T17:47:48.720",
"TotalTimeInOffice":"2397",
"LocationId":"19",
"LocationName":"Testing",
"VisitId":"62816",
"B360VisitTypeId":"11",
"VisitTypeName":"Follow Up Visit",
"PatientTimeInLocation":"200",
"PatientLocationHistoryTimeStamp":"2015-06-04T17:41:23.277",
"PatientNextLocationTime":"2015-06-04T17:44:43.603",
"UserProfileId":"25",
"GroupId":"13",
"GroupName":"Medical Assistants",
"StaffTimeInLocation":"195",
"StaffLocationHistoryTimeStamp":"2015-06-04T17:41:28.257",
"StaffNextLocationTime":"2015-06-04T17:44:43.570",
"StaffLocationStartTime":"2015-06-04T17:41:28.257",
"StaffLocationEndTime":"2015-06-04T17:44:43.570",
"TotalPatientWaitTime":"544",
"TotalPatientWaitTimeInRoomForStaff":"506",
"TotalTimeWithPatient":"1648",
"TotalTimeToCheckout":"185"
},
{
"CheckInTime":"2015-06-04T17:14:18.983",
"CheckinDate":"2015-06-04",
"Week":"23",
"Day":"4",
"DayOfWeek":"5",
"Month":"6",
"Year":"2015",
"TwentyFourHour":"17",
"Hour":"5 PM",
"Period":"PM",
"CheckOutTime":"2015-06-04T18:12:50.673",
"TotalTimeInOffice":"3512",
"LocationId":"8",
"LocationName":"Exam4",
"VisitId":"62872",
"B360VisitTypeId":"11",
"VisitTypeName":"Follow Up Visit",
"PatientTimeInLocation":"2730",
"PatientLocationHistoryTimeStamp":"2015-06-04T17:27:01.943",
"PatientNextLocationTime":"2015-06-04T18:12:31.370",
"UserProfileId":"8",
"GroupId":"7",
"GroupName":"Physician Assistants",
"StaffTimeInLocation":"441",
"StaffLocationHistoryTimeStamp":"2015-06-04T18:04:55.633",
"StaffNextLocationTime":"2015-06-04T18:12:16.430",
"StaffLocationStartTime":"2015-06-04T18:04:55.633",
"StaffLocationEndTime":"2015-06-04T18:12:16.430",
"TotalPatientWaitTime":"763",
"TotalPatientWaitTimeInRoomForStaff":"2274",
"TotalTimeWithPatient":"441",
"TotalTimeToCheckout":"19"
},
{
"CheckInTime":"2015-06-04T17:14:18.983",
"CheckinDate":"2015-06-04",
"Week":"23",
"Day":"4",
"DayOfWeek":"5",
"Month":"6",
"Year":"2015",
"TwentyFourHour":"17",
"Hour":"5 PM",
"Period":"PM",
"CheckOutTime":"2015-06-04T18:12:50.673",
"TotalTimeInOffice":"3512",
"LocationId":"8",
"LocationName":"Exam4",
"VisitId":"62872",
"B360VisitTypeId":"11",
"VisitTypeName":"Follow Up Visit",
"PatientTimeInLocation":"2730",
"PatientLocationHistoryTimeStamp":"2015-06-04T17:27:01.943",
"PatientNextLocationTime":"2015-06-04T18:12:31.370",
"UserProfileId":"25",
"GroupId":"13",
"GroupName":"Medical Assistants",
"StaffTimeInLocation":"351",
"StaffLocationHistoryTimeStamp":"2015-06-04T17:24:16.613",
"StaffNextLocationTime":"2015-06-04T17:30:07.170",
"StaffLocationStartTime":"2015-06-04T17:27:01.943",
"StaffLocationEndTime":"2015-06-04T17:30:07.170",
"TotalPatientWaitTime":"763",
"TotalPatientWaitTimeInRoomForStaff":"2274",
"TotalTimeWithPatient":"186",
"TotalTimeToCheckout":"19"
}
]
Ultimately, this is what the graph would look like (This is just a mock and the numbers are not representative of my final data):
I understand, using D3, how to group the data, but I am not sure if I need to run D3 4 times for each section on a bar or if there is a slick way to project the final series I can pass into HighCharts. Each section on the graph is an average for that given set. I also need to count the number of unique visits for each time period. The problem I was facing is that multiple groups may span a Visit so. I suppose I could group by Visit first, count the visits, and then project that data?
For the Y-Axis I am thinking I can get the min / max using D3's built in functionality to pull out those values.
Using SQL this is how the data would look (the average columns are in seconds):
I started a fiddle for aggregating the data to get an understating of how grouping works.