{"id":719,"date":"2013-01-25T09:06:57","date_gmt":"2013-01-25T09:06:57","guid":{"rendered":"http:\/\/helpmeinvestigate.com\/health\/?p=719"},"modified":"2021-10-22T07:03:44","modified_gmt":"2021-10-22T07:03:44","slug":"recipe-whats-the-worst-time-to-go-to-ae","status":"publish","type":"post","link":"http:\/\/helpmeinvestigate.com\/health\/2013\/01\/25\/recipe-whats-the-worst-time-to-go-to-ae\/","title":{"rendered":"Recipe: what’s the worst time to go to A&E?"},"content":{"rendered":"

Today we publish the first results of a collaboration with the Birmingham Mail<\/a>: when’s the worst time to go to A&E in the West Midlands? (It’s 1am, by the way). \u00a0Or, to give it its print headline: “A&E delays worst in the early hours”.<\/p>\n

The story could be repeated in any region. Here’s how you can do it yourself:<\/p>\n

1. Get the data<\/h2>\n

The most recent data for waiting times by hour, for 2010\/11, are published on\u00a0the Hospital Episode Statistics site, HESonline<\/a>. There is more recent data available on waiting times as a whole (the Department of Health publishes\u00a0quarterly data<\/a>), but not broken down by hour. Put this to one side for more up to date context.<\/p>\n

Find the sheet in the HESonline data that breaks down waiting times by hour of arrival by hospital trust (it’s the 12th sheet, ‘Table 10’). Note that these are average<\/em> waiting times: some people will spend less time, and some will wait longer. (Table 7 actually breaks down patients by duration, but not by hour as well<\/em>).<\/p>\n

You may want to copy this sheet into a new spreadsheet, as spreadsheet operations should work more quickly. Always keep a copy of the raw, untouched data<\/strong> to refer back to.<\/p>\n

2. Clean the data<\/h2>\n

To start to ask questions of this data, you’ll need to strip out extra rows and columns<\/strong> that aren’t needed. Like many health datasets, for example, this one has 8 rows of blurb and an image that you can just delete. The headers are also split between rows 9 and 10, but that’s not going to be a problem for us.<\/p>\n

For simplicity you might also want to strip out rows from other regions. Each region’s hospital trusts starts with a bold row for the strategic health authority (SHA) figures. Copy the section for those hospital trusts and paste into a new sheet.<\/p>\n

3. Ask the questions with formulae<\/h2>\n

I always break down the process of getting an answer to my questions<\/strong>, which makes it quicker and easier to solve. Here, for example, our question is ‘Which is the worst hour to go to A&E?’ More specifically, we want to know this for each hospital, and probably the best hour too. How do we answer that question?<\/p>\n

    \n
  1. Which is the largest number (waiting time) in each hospital trust’s row?<\/li>\n
  2. What hour does that number relate to?<\/li>\n<\/ol>\n

    To answer 1. you can use the MAX function in Excel or Google Docs. This tells you what is the highest value in a range of cells that you specify, like so: = MAX(C2:X2)<\/p>\n

    Answering 2. is a little more difficult, and involves two functions: MATCH tells us where a value is (in this case, that highest value) and INDIRECT brings back the contents of a cell (in this case, the header).<\/p>\n

    I’ve detailed the process in a separate post at sister blog ExcelNotes<\/a>. You can also see a spreadsheet where the various formulae have been used here<\/a>. This also includes some other formulae, such as one which uses COUNTIF to check that the maximum waiting time only occurs once, and another which converts the time from a decimal figure to hours and minutes (divide by 1440 and format the cells as time).<\/p>\n

    You can adapt the process for maximum waiting times so that you see minimum waiting times, by replacing MAX with MIN.<\/p>\n

    4. Sort and analyse<\/h2>\n

    Once you have the answers, you need to work out what the story is. There might be more than one to choose from: for example, topical issues like the Mid Staffordshire report<\/a> might come into play (in this case, they had the worst waiting times across the region). There might be wide differences. Or there may be neighbouring hospitals where a patient could drive from one to another and still be seen sooner.<\/p>\n

    Choose one clear angle and go with that<\/strong> – it can be easy to bury your story in detail when you’re dealing with data. Don’t. Use the same editing skills you would with interviews: pick the most newsworthy, interesting angle and start with that. Keep it simple: you can always add further details in the background further down the article.<\/p>\n

    Speak to people<\/strong>. Call the hospital trusts that feature most strongly in the piece and ask if they can explain why the figures say what they say.\u00a0Quotes make a big difference to a piece, but also you might find there are details missing from the data (although if they question the data entirely don’t take them on their word: talk to other authorities such as the body that collected the statistics, as well as\u00a0patient groups and health workers’ unions).<\/p>\n

    Explain potential limitations of the data<\/strong>. Make it clear that the data has limitations: for example, in this case, although it’s the most up to date data, it’s not very recent. In addition, it’s known that certain practices are used to manipulate what is counted in the data: for example, patients might be held on an ambulance (‘stacking’) or moved out of A&E while they’re still waiting.<\/p>\n

    And that’s it. If you do write up this as a story in your area, let us know so we can link to it.<\/p>\n

    \"Post<\/a> Tweet This Post<\/a><\/p><\/div>","protected":false},"excerpt":{"rendered":"

    Today we publish the first results of a collaboration with the Birmingham Mail: when’s the worst time to go to A&E in the West Midlands? (It’s 1am, by the way). \u00a0Or, to give it its print headline: “A&E delays worst … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[6,13],"tags":[349,367,58],"_links":{"self":[{"href":"http:\/\/helpmeinvestigate.com\/health\/wp-json\/wp\/v2\/posts\/719"}],"collection":[{"href":"http:\/\/helpmeinvestigate.com\/health\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/helpmeinvestigate.com\/health\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/helpmeinvestigate.com\/health\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/helpmeinvestigate.com\/health\/wp-json\/wp\/v2\/comments?post=719"}],"version-history":[{"count":6,"href":"http:\/\/helpmeinvestigate.com\/health\/wp-json\/wp\/v2\/posts\/719\/revisions"}],"predecessor-version":[{"id":938,"href":"http:\/\/helpmeinvestigate.com\/health\/wp-json\/wp\/v2\/posts\/719\/revisions\/938"}],"wp:attachment":[{"href":"http:\/\/helpmeinvestigate.com\/health\/wp-json\/wp\/v2\/media?parent=719"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/helpmeinvestigate.com\/health\/wp-json\/wp\/v2\/categories?post=719"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/helpmeinvestigate.com\/health\/wp-json\/wp\/v2\/tags?post=719"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}