Sorting out dates

Posted By Paul Cooper |  last update 19-May 2010

I need to try and get reliable data from our theatre system and who is doing what. The data in the Consultant resonsible for the list' field is unreliable ( dont ask......) in the theatre system. I'm told the most reliable source is the surgeons rota.  This is available on the intranet as an excel spreadsheet.

i thought automating data extracation would be easy. Courtesy of IT ive got a spare computer on my desk running linux ( was gentoo, now ubuntu)

so i can retrieve the file with wget and then parse out the data i need.

The aim is to put it all into a perl hash of hashes - so extracting other data in the future will be straightforward, and i could even do it as a web page.

The hash would be

{date}=>( {on call}=>surgeon1, {theatre} +>surgeon2 )

so to get the person on call for  4 April :

value for {20090404}{on call} = person on call.

to get theatre surgeon for days of a week , just put it in a loop

for date =startdate to enddate ( eg 4 April 2009 -10 April 2009

substitue each value of 'date' into the formula (20090404,20090405,20090406 etc etc )

then surgeon =name stored in {date}{theatre}

which will give the name on a particular date and rota

(i appreciate this wont actually run as code!)

its possible to expand as well and i could pull data from a number of rotas on the same principle.

this is the spreadsheet

First problem :


date field isnt formatted as a date - its text.

the formatting isnt consistent  - some dates have dots and some dont.

So i thought a regular expression would extract the bits I want.I played around with this sort of stuff.

/ (\d{2}) \. (\d{2}) \. (\d{4}) /

2 numbers followed by a dot, then 2 numbers then a dot then 4 numbers

/ (\d{2}) \.* (\d{2}) \.* (\d{4}) /

2 numbers followed by 0 or 1 characters followed by 2 numbers followed by zero or 1 characters followed by  4 numbers.

but it didnt sort out all the dates consistently.

 bit more fiddling around should get it fixed though with the help fo the Date::Manip module in perl

But some  thoughts :

  • Whoever produced  the rota doesnt know about date formatting in Excel
  • Whoever produced the rota must have spent a good while typing in each date - thats what must have happened because there are different typos in many date fields.
  • This must be the level of Excel /IT expertise , or ignorance that many users have.
  • And whats the best way to do something about it, if we want to to. Does ECDL or similar cover this , and how far into the course?

i know  for purists excel isnt the correct software to do rotas, but for small departments it gets used because its relatively straightforward and available

  • introduce a rota package an spend time and money training people on it
  • going and having a quiet word ("we've noticed you are doing the rota, do you know about ......)
  • Introduce a 'rota template' that all shall use ?Produce an 'advice for rotamakers using Excel ' handout
  • Just let things be.?

3 comment(s)

Posted by Grant on 20-Jan 2010 13:01

Good effort at tidying up someone else's rubbish data. Doubt that anyone else would have had the time or patience to do it. Software that requires users to input dates should use a date picker. End of story. The exception is date of birth which is trickier because of the wide range of years and there is no perfect solution. I really think we should make an effort to build a web-based theatre rota system and provide it as a SCATA freebie or maybe a modest subscription fee to cover hosting costs.

Posted by Nigel Bedayse on 15-May 2010 21:05

Hi. I've recently joined SCATA and only recently been working in the UK in the capacity of Specialty Doctor. So my understanding of your rota system is still rudimentary. I have worked before up to Head of Department level in the Caribbean and am quite aware of how formidable a challenge making a rota can be. If it is difficult for humans it will be difficult for computers too, which depend on human programming. From my recent attendance at the SCATA conference it was apparent to me the need for local development of software solutions that can work across different hardware capabilities with clinical involvement. I believe what is possibly lacking is a model that can simulate the true complexity of the rota and other hospital systems. Such a model needs to have appropriate complexity to reduce errors at real world implementation ( not logical errors, just processes that are not intuitive or efficient) yet be simple enough to allow the subtraction, addition or modification of clinical work. I would like to know; 1. Does any model exist for rota development? 2. What are the necessary elements of a hospital rota that will help someone to develop appropriate software? 3. If neither exists maybe they can be developed so instead of us trying to mould inappropriate software to do what we want we can have the correct tools for the correct job always.

Posted by paul cooper on 13-Dec 2017 17:12

there is a thread on the forums page of the SCATA website - it may be worth resurrecting that and seeing what people think .

show all blogs