Red, green and grey dots

Developing the coloured dots visualization of a day's patient flow

Introduction
This is a visualization that displays all of the movements into, out of and between (a) A&E, (b) the Assessment wards and (c) the downstream wards in the course of one day (which for the purposes of this example happens to be 3rd September 2014) in one general hospital. The idea is that you will be able to see the whole system's activity (well, the whole hospital system's activity, at least) in one eye-sweep, all on one side of A4, or one PowerPoint slide.

A day's patient flow—movement by movement—on one side of A4.


The dataset you need for this is therefore one that includes all of the movements on that day i.e. arrivals, departures and transfers, with each movement being shown on a separate row of the Excel workbook. This means it will look as if there's quite a bit of double-counting, treble-counting, quadruple-counting and more. For example, a patient who is admitted to A&E, then transferred to an Assessment ward, then transferred onwards to a downstream ward, with all of these movements taking place during the 24-hour period, will appear in the dataset five times. This is because there would have been five separate movements during the day: 1. into A&E; 2. out of A&E; 3. into Assessment; 4. out of Assessment; 5. into the ward.


Preparing the data extract
The Excel workbook we're going to use (you can download it here: RedGreenGreyDots.xlsx) has a Data (0) worksheet with 684 rows of data, each row representing a separate movement during Wednesday 3rd September 2014. This extract has been derived from two separate queries: one that extracted the arrivals and transfers in on that day; and another that extracted the departures and transfers out on the same day. We then glued these two separate extracts together, recording in columns F and G what type of movement it was.

Please note that the first names and last names in columns B and C are anonymised names.

A word about the columns you need when you put your extract together back at the ranch. You need date and time concatenated into one field, which we'll call MovementDateTime (this is column A in the example). And we also need clear markers about what type of movement each movement is: an arrival, a transfer in, a transfer out or a departure. This is because a key part of this visualization is that the movements are colour-coded: red for arrivals, grey for transfers in and out, green for departures.


Step 0
Ensure the data is sorted in the right way

The data needs to be sorted as follows:
- By MovementDateTime from oldest to newest (so that the movements are in chronological order)
- By FirstName from A to Z
- By LastName from A to Z
- By Movement_Type from Z to A (so that when movement dates are identical for identical patients, the departures will precede the arrivals)

Step 1
Create the Movement_15 field

The first additional column we need to create is called Movement_15 and we are going to insert this new column between the existing columns A and B. If you look at the Data (1) worksheet, you'll see that this has already been done. We create this column (which basically takes the MovementDateTime from column A and allocates it to a 15-minute time-interval category) because I think the graphic looks better if it's organised into 15-minute groupings. How many arrivals between 00:00 and 00:15? How many between 00:15 and 00:30? And so on. So we need to work out which 15-minute grouping each movement belongs to.

We can use Excel's =FLOOR function to do this.

Create a new column just to the right of the MovementDateTime column. This is now a new column B. Label it Movement_15 and in cell B2, type the following syntax:
=FLOOR(A2,"00:15")

Then copy this syntax down to all the other cells in column B. This function basically rounds down each of the times in column A to the nearest 15-minute interval. You are effectively creating the x-co-ordinates of all the dots in the scatterplot that will eventually make up the coloured-dot graphic.

The next thing we have to do is work out how to create the y-co-ordinates for the dots.

Step 3 Create the Movement_15_SEQNO field

Create a new column to the right of what is now column H (the field called IN_OUT). This will now be column I and you can label it Movement_15_SEQNO. The purpose of this field is to enable us to arrange our coloured dots in the right order within the 15-minute groupings in the final graph.

Before we populate the cells in column I, we will need to re-sort the data. We want it to be sorted first by IN_OUT (A to Z ascending, so that the INs come first). Then it needs to be sorted by Staging_Post (A to Z ascending, so that A&E comes before Assessment, which in turn comes before Wards) And then we want it sorted by MovementDateTime, chronologically.

The syntax you need to type into cell I2 is:

=IF(AND(F2=F1,B2=B1),I1+1,1)

What this syntax is doing is checking that (a) the movement on this row is in the same staging post as the movement in the preceding row (F2=F1), (b) the movement on this row took place in the same 15-minute interval as the movement in the preceding row (B2=B1) and (c) if both of these conditions are met, then make the Movement_15_SEQNO in this row one unit more than the Movement_15_SEQNO in the preceding row, otherwise make the Movement_15_SEQNO 1.

This syntax works for all of the movements that are IN movements (i.e. all of the movements in rows 2 to 339). But for the OUT movements we will need to edit the syntax so that the numbers are negative numbers. Hence in cell I340, we will need to type:

=IF(AND(F340=F339,B340=B339),I339-1,-1)

…which is basically the "mirror image" of the syntax we typed in cell I2.

Step 4
Create a new Data (3) worksheet and copy and paste-special the Movement_15_SEQNOs as values

We now have to set these Movement_15_SEQNOs in stone, so to speak. As it stands, having the correct values in column I is utterly reliant on the data being in a particular order, and we are soon going to re-order the data, so we need to preserve the column I values while we can.

So create a copy of the Data (2) worksheet, place it just to the right of Data (2) and rename it Data (3).

Next, highlight all the data in column I and copy and paste-special over the top of it as values.

Step5
Create columns for each of the Movement_Types

The data as it stands in Data (3) would be fit for purpose if we weren't bothered about colour-coding our graphic, distinguishing between the arrivals, the transfers and departures. But if we want arrivals to be one colour (red), departures to be another colour (green), and transfers to be yet another colour (grey), then we need to separate the pasted-special-as-values values in column I into four columns.

So in cells J1, K1, L1 and M1, create the following column labels:

- Column J: Arrival

- Column K: Transfer In

- Column L: Transfer Out

- Column M: Departure

Step 6
The syntax for the colour-coded y-co-ordinates

We only want the value in column I to appear in its relevant column in J, K, L or M if it matches up with the correct Movement_Type.

We'll do column J first. Go to cell J2 and type:

=IF($G2="Arrival",$I2,#N/A)

A couple of comments about this syntax.

First, the anchoring of columns G and I is to make the copying to the next three columns (K, L and M) easier.

Secondly, the #N/A is specified instead of a blank cell (" ") because of Excel's scatterplot behaviour (it tends to interpret blank cells as zero values, which we don't want; whereas #N/A values are interpreted as blank cells).

Next, copy the syntax in cell J2 into cells K2, L2 and M2, and then edit it so that you end up with:

=IF($G2="Transfer In",$I2,#N/A) in K2

=IF($G2="Transfer Out",$I2,#N/A) in L2

=IF($G2="Departure",$I2,#N/A) in M2

Step 7
Draw the charts

As an example, let's draw the chart for A&E.

We'll need to re-order the data, so that all of the A&E movements (in and out - all of them) are in one big cluster in the spreadsheet. So we'll take a copy of the Data (3) worksheet and name the new worksheet Data (4), and then we'll go into Data > Sort and sort by Staging_Post and then by MovementDateTime.

Next, highlight the data. The x-co-ordinates are the Movement_15 values in column B, so highlight the cells from B2 to B334 (row 334 is where the A&E movements stop and the Assessment movements start). And the y-co-ordinates are a block that is four columns wide - it's the range J2 to M334.

If you then choose X-Y chart from Excel's chart menu, you should get a chart that looks something like this:

You'll need to re-scale the x-axis, and then also think about how to re-size the whole chart (make it wide and flat) so that you can fit three of these charts onto the same page...

...and the rest of the formatting is up to you...