Welcome to WordPress. This is your first post. Edit or delete it, then start blogging!
My GP’s surgery, St Paul’s in Winchester, has an adjacent pharmacy and small private car park. It’s always been very busy, but recently I’ve found myself queuing to get a place. It got me thinking – has my surgery actually got busier over the last few years.
Cue looking for some open data. A Google search took me to:
Numbers of Patients Registered at a GP Practice (practice level, 5 year age groups) on data.gov.uk. It lists data for 5 years.
Confusingly, some datasets are directly downloadable, whilst others are hosted on the NHS Digital Catalogue.
The data sets have unhelpful file names and were not all consistent. Some had the the GP Practice code and postcode, whilst others only had the GP Practice code. Also, at GP Practice level data are released in single year of age bands and 5-year age bands both of which finish at 95+, split by gender and aggregated, but for some datasets I could only find the ‘single year of age’ version.
After downloading the datasets, it was a simple, if tedious, job to use Google sheets to tidy the data and visualise it.
So, my practice has certainly increased its patient numbers, by 29.7% over 5 years, from April 2013 to March 2018.
An analysis of the growth across the 5 year bands explained why. Whilst most bands have increased steadily, from 2016 onwards there has been a sharp increase in the number of people in the 15-19 year old and 20-24 year old bands. This is especially marked for females.
|Apr 2013||Apr 2014||Apr 2015||Apr 2016||Apr 2017||Mar 2018||% inc. ’13-’18|
The growth in numbers for these age bands would point to students using the practice. The University of Winchester advises students:
We encourage all our students to register with a GP practice. There are three GP practices in Winchester. These are St Clements Practice, The Friarsgate Practice and St Paul’s Surgery… You can choose to register at any of these GP practices.
However, St Paul’s is the nearest practice to the University and seems to have attracted most students. I haven’t analysed the age band data for the other practices, but the number of patients at the other practices has been essentially constant.
I’d make the assertion that most of the students aren’t driving to the surgery and that the increasing business of the car park is due to the gradually growth in other ages bands at St Paul’s.
I’ve been working with [Data Studio](https://datastudio.google.com/) today and realised it interprets dates a bit more strictly than charts in Sheets. I thought I’d share what I did – and its reminder to pay more attention to managing dates.
Firstly, I had a simple table of dates and a value for each month. In Google sheets, I’d used a *dd/mm/yyyy* formatted date which I have displayed as, for example, **Mar 16**.
Linking this sheet to Data Studio, I found, not surprisingly, that the dates were displayed back as a full *dd/mm/yy* format and that some days and months were switched.
A simple reformat of the dates to *yyyymm* fixed this issue. This allowed Data Studio to interpret and display the dates correctly:
My second challenge was to convert a text date into a month and year date that Data Studio could interpret.
The text date was, for example, **Dec 2017**. The conversion steps were as follows:
Convert the text date (Dec 2017) to a datevalue [=DATEVALUE(A2)]
Extract the end of month value using eomonth [=eomonth(B2,0)]
Finally, format the date again to yyyymm.
This allowed Data Studio to interpret and display the data correctly.