NHS Winter SitRep

Notebook starting to explore NHS Winter Sitrep Data.

The original version of this notebook with working notes on the creation of the data harvester can be found here: ../archive/Winter_SitRep_old.ipynb.

The NHS Winter Sitrep data from 2017-18 is available as daily data published on a weekly basis in the form of an Excel spreadsheet. A time series spreadsheet is also published weekly that collates data from all the weekly spreadsheets in the 2017-18 Winter collection period.

The psychemedia/openHealthDataDoodles/tree/cli_winter_sitrep utility is a command line tool that attempts to download the most recent timeseries spreadsheets for Acute and NHS111 sitreps, and extract the data into a simple unnormalised SQLite3 database tables, one for the Acute data, one for the NHS111 data.

If you are running this notebook from the orginal repository via Binderhub, the cli_winter_sitrep utility should already be installed.

In [1]:
# Attempt to collect the most recent data
!nhs_winter_sitrep collect
Grabbing data for Winter Sitrep: Acute Time series 20 November 2017 to 31 December 2017 (XLSX, 774kB)
Grabbing data for Winter SitRep: NHS111 Time series 20 November 2017 to 31 December 2017 (XLSX, 395kB)
/srv/venv/lib/python3.5/site-packages/pandas/core/generic.py:1534: UserWarning: The spaces in these column names will not be changed. In pandas versions < 0.14, spaces were converted to underscores.
  chunksize=chunksize, dtype=dtype)
In [2]:
%matplotlib inline
import pandas as pd
import natural_time_periods as ntpd
from dateutil import parser

Accessing the database

In [3]:
import sqlite3
conn = sqlite3.connect('nhs_sitrepdb.db')
In [4]:
q="SELECT name FROM sqlite_master WHERE type='table';"

pd.read_sql_query(q,conn)
Out[4]:
name
0 sitrep
1 nhs111
In [5]:
#Test query - sitrep
pd.read_sql_query("SELECT * FROM sitrep LIMIT 5;", conn, parse_dates=['Date'])
Out[5]:
Date Area Code Name value Category Report
0 2017-11-20 London Commissioning Region RF4 Barking, Havering And Redbridge University Hos... 0 A&E closures A&E closures
1 2017-11-21 London Commissioning Region RF4 Barking, Havering And Redbridge University Hos... 0 A&E closures A&E closures
2 2017-11-22 London Commissioning Region RF4 Barking, Havering And Redbridge University Hos... 0 A&E closures A&E closures
3 2017-11-23 London Commissioning Region RF4 Barking, Havering And Redbridge University Hos... 0 A&E closures A&E closures
4 2017-11-24 London Commissioning Region RF4 Barking, Havering And Redbridge University Hos... 0 A&E closures A&E closures
In [6]:
pd.read_sql_query("SELECT DISTINCT Report, Category FROM sitrep;", conn)
Out[6]:
Report Category
0 A&E closures A&E closures
1 A&E diverts A&E diverts
2 G&A beds Core Beds Open
3 G&A beds Escalation Beds Open
4 G&A beds Total Beds Open
5 G&A beds Total beds occ'd
6 G&A beds Occupancy rate
7 Beds Occ by long stay patients > 7 days
8 Beds Occ by long stay patients > 21 days
9 D&V, Norovirus Beds closed
10 D&V, Norovirus Beds closed unocc
11 Adult critical care CC Adult avail
12 Adult critical care CC Adult Occ
13 Adult critical care Occupancy rate
14 Adult critical care CC Adult Open
15 Paediatric intensive care Paed Int Care Avail
16 Paediatric intensive care Paed Int Care Occ
17 Paediatric intensive care Occupancy rate
18 Paediatric intensive care Paed Int Care Open
19 Neonatal intensive care Neo Int Care Avail
20 Neonatal intensive care Neo Int Care Occ
21 Neonatal intensive care Occupancy rate
22 Neonatal intensive care Neo Int Care Open
23 Ambulance Arrivals and Delays Arriving by ambulance
24 Ambulance Arrivals and Delays Delay 30-60 mins
25 Ambulance Arrivals and Delays Delay >60 mins
In [7]:
#Test query - nhs111
pd.read_sql_query("SELECT * FROM nhs111 LIMIT 5;", conn, parse_dates=['Date'])
Out[7]:
Category Date Region Code NHS 111 area name Report value
0 Calls answered within 60 Seconds 2017-11-20 North 111AA1 North East England NHS 111 Answered in 60 2400.0
1 Calls answered within 60 Seconds 2017-11-21 North 111AA1 North East England NHS 111 Answered in 60 2135.0
2 Calls answered within 60 Seconds 2017-11-22 North 111AA1 North East England NHS 111 Answered in 60 2163.0
3 Calls answered within 60 Seconds 2017-11-23 North 111AA1 North East England NHS 111 Answered in 60 1899.0
4 Calls answered within 60 Seconds 2017-11-24 North 111AA1 North East England NHS 111 Answered in 60 1957.0
In [8]:
pd.read_sql_query("SELECT DISTINCT Report, Category FROM nhs111;", conn)
Out[8]:
Report Category
0 Answered in 60 Calls answered within 60 Seconds
1 Answered in 60 Calls answered
2 Abandoned Calls abandoned after at least 30 seconds waiting
3 Abandoned Calls offered
4 Triage Calls where person triaged
5 Clinical Advisor Calls transferred to or answered by a clinical...
6 Clinical Input Calls to a CAS clinician
7 Call Back Calls back within 10 minutes
8 Call Back Calls where person offered call back
9 Dispositions Ambulance dispatches
10 Dispositions Recommended to attend A&E
11 Dispositions Recommended to attend primary and community care
12 Dispositions Recommended to contact primary care
13 Dispositions Recommended to speak to primary care
14 Dispositions Recommended to dental
15 Dispositions Recommended to pharmacy
16 Dispositions Recommended to attend other service
17 Dispositions Not recommended to attend other service
18 Dispositions Given health information
19 Dispositions Recommended home Care
20 Dispositions Recommended non clinical

Utils

Helpful quueries wrapped in functions.

In [9]:
def lookupTrust(conn,trust, typ='sitrep'):
    typ = typ.lower()
    name='NHS 111 area name' if typ=='nhs111' else 'Name'
    area='Region' if typ=='nhs111' else 'Area'
    q='''SELECT DISTINCT "{name}",{area}, code FROM {typ} WHERE LOWER("{name}") LIKE "%{trust}%";'''.format(typ=typ,
                                                                                                            name=name, 
                                                                                                            trust=trust.lower(),
                                                                                                            area=area)
    return pd.read_sql_query(q, conn)


def lookupTrustCode(conn,trust, typ='sitrep'):
    df=lookupTrust(conn,trust, typ=typ)
    name='NHS 111 area name' if typ.lower()=='nhs111' else 'Name'
    if len(df) > 1: return df
    return df[name].iloc[0],df['Code'].iloc[0]
In [10]:
lookupTrust(conn,'Wight')
Out[10]:
Name Area Code
0 Isle Of Wight NHS Trust South Of England Commissioning Region R1F
In [11]:
lookupTrust(conn,'Wight', 'NHS111')
Out[11]:
NHS 111 area name Region Code
0 Isle Of Wight NHS 111 South 111AA6
In [12]:
lookupTrustCode(conn,'Wight', 'NHS111')
Out[12]:
('Isle Of Wight NHS 111', '111AA6')
In [13]:
lookupTrustCode(conn,'Wight', 'sitrep')
Out[13]:
('Isle Of Wight NHS Trust', 'R1F')
In [14]:
lookupTrustCode(conn,'London', 'NHS111')
Out[14]:
NHS 111 area name Region Code
0 Inner North West London NHS 111 London 111AA7
1 Hillingdon London NHS 111 London 111AA9
2 South West London NHS 111 London 111AG5
3 North West London NHS 111 London 111AD4
4 North Central London NHS 111 London 111AD5
5 Outer North East London NHS 111 London 111AD6
6 South East London NHS 111 London 111AD7
7 East London & City NHS 111 London 111AD8
8 LONDON REGION London None
In [15]:
def lookupFromTrustCode(conn,code, typ='sitrep'):
    typ = typ.lower()
    name='NHS 111 area name' if typ=='nhs111' else 'Name'
    area='Region' if typ=='nhs111' else 'Area'
    q='''SELECT DISTINCT "{name}",{area}, code FROM {typ} WHERE LOWER(Code) LIKE "%{code}%";'''.format(typ=typ,
                                                                                                            name=name, 
                                                                                                            code=code.lower(),
                                                                                                            area=area)
    return pd.read_sql_query(q, conn).iloc[0].to_dict()
In [16]:
lookupFromTrustCode(conn,'R1F')
Out[16]:
{'Area': 'South Of England Commissioning Region',
 'Code': 'R1F',
 'Name': 'Isle Of Wight NHS Trust'}

inflect

The inflect package provides a variety of functions for generating text.

In [17]:
import inflect
P = inflect.engine()

Test plot

In [18]:
def timeSeriesPlot(df,time='Date',val='value',title=''):
    df.set_index('Date')['value'].plot(title=title)
In [19]:
q='''
SELECT * FROM sitrep WHERE Category = "Total beds occ'd" AND Code="R1F";
'''

df = pd.read_sql_query(q, conn, parse_dates=['Date'])

timeSeriesPlot(df,title="R1F Total beds occ'd ")

Time limited queries

In [20]:
def getperiod_dates(nl_period):
    if nl_period is None: return ''
    period = ''.join(nl_period.lower().split()) if nl_period is not None else nl_period
    if period in ['lastmonth', 'lastweek']:    
        if period == 'lastmonth': fromdate,todate=(ntpd.last_month(iso=True))
        elif period == 'lastweek': fromdate,todate=(ntpd.last_week(iso=True))
        return fromdate, todate
    return ''

def getperiod_sql_clause(nl_period, col='Date'):
    period = getperiod_dates(nl_period)
    if not period: return period
    else: (fromdate, todate)=period
    q=' AND date({col}) BETWEEN date("{fromdate}") AND date("{todate}") '.format(col=col, fromdate=fromdate,
                                                                                      todate=todate)
    return q       
In [21]:
getperiod_dates('last week'), getperiod_sql_clause('last month')
Out[21]:
(('2017-12-25', '2017-12-31'),
 ' AND date(Date) BETWEEN date("2017-12-01") AND date("2017-12-31") ')
In [22]:
def period_text(period):
    p = getperiod_dates(period)
    if p: return '({} to {})'.format(parser.parse(p[0]).strftime('%A %d %B %Y'),
                                     parser.parse(p[1]).strftime('%A %d %B %Y'))
    return ''
In [23]:
period_text('last week')
Out[23]:
'(Monday 25 December 2017 to Sunday 31 December 2017)'
In [24]:
def timeSeriesSelectPlot(code, category, report=None, title=None, period=None):
    q=''' SELECT * FROM sitrep WHERE Category = "{category}" AND Code="{code}"'''.format(category=category,code=code)
    #We may have to disambiguate category values
    if report is not None:
        q=q+' AND Report="{report}"'.format(report=report)
    
    #Add time limit
    q = q+ getperiod_sql_clause(period, col='Date')
    
    df = pd.read_sql_query(q, conn, parse_dates=['Date'])
    if title is None:
        title = '{} - '.format(report) if report is not None else ''
        title = '{}{} for {}'.format(title, category,code)
            
    timeSeriesPlot(df,title=title)
In [25]:
timeSeriesSelectPlot('R1F',"Total beds occ'd")
In [26]:
timeSeriesSelectPlot('R1F',"Total beds occ'd", period='last week')
In [27]:
timeSeriesSelectPlot('R1F',"Occupancy rate",report="G&A beds")

Using the data to verify previously published reports

For example, via @carlbaker, I see this:

Let's get a count of the delayed ambulances, by Trust, within that period:

In [28]:
q='''
SELECT  Name, SUM(value) FROM sitrep 
WHERE date(Date) BETWEEN date('2017-12-25') AND date('2017-12-31') 
AND (Category = 'Delay >60 mins' OR Category='Delay 30-60 mins') AND value NOT NULL GROUP BY Name ORDER BY SUM(value) DESC
;
'''

pd.read_sql_query(q, conn).head()
Out[28]:
Name SUM(value)
0 East Kent Hospitals University NHS Foundation ... 585
1 Worcestershire Acute Hospitals NHS Trust 413
2 East Lancashire Hospitals NHS Trust 391
3 Lancashire Teaching Hospitals NHS Foundation T... 390
4 United Lincolnshire Hospitals NHS Trust 373

We can also find the total number of arrivals for each Trust in the same period:

In [29]:
q2='''
SELECT  Name, SUM(value) FROM sitrep 
WHERE date(Date) BETWEEN date('2017-12-25') AND date('2017-12-31') 
AND Category = 'Arriving by ambulance'  AND value NOT NULL GROUP BY Name ORDER BY SUM(value) DESC
;
'''

pd.read_sql_query(q2, conn).head()
Out[29]:
Name SUM(value)
0 Barts Health NHS Trust 1783
1 Pennine Acute Hospitals NHS Trust 1699
2 Heart Of England NHS Foundation Trust 1535
3 Leeds Teaching Hospitals NHS Trust 1429
4 Frimley Health NHS Foundation Trust 1402

Now we can combine those and find the percentage of total arrivials to each Trust that were delayed, ordering from most delayed.

In [30]:
q3='''
SELECT total.Name, Delayed, Total, 100.0*Delayed/Total AS pc FROM (SELECT  Name, SUM(value) AS Delayed FROM sitrep 
WHERE date(Date) BETWEEN date('{fromdate}') AND date('{todate}') 
AND (Category = 'Delay >60 mins' OR Category='Delay 30-60 mins') 
AND value NOT NULL GROUP BY Name) delayed JOIN (SELECT  Name, SUM(value) AS Total FROM sitrep 
WHERE date(Date) BETWEEN date('{fromdate}') AND date('{todate}') 
AND Category = 'Arriving by ambulance' AND value NOT NULL GROUP BY Name) total on total.Name = delayed.Name 
ORDER BY pc DESC
'''.format(fromdate='2017-12-25', todate='2017-12-31')
tmp = pd.read_sql_query(q3, conn)
tmp.head(10)
Out[30]:
Name Delayed Total pc
0 The Queen Elizabeth Hospital, King's Lynn, NHS... 239 418 57.177033
1 Lancashire Teaching Hospitals NHS Foundation T... 390 724 53.867403
2 East Lancashire Hospitals NHS Trust 391 890 43.932584
3 East Kent Hospitals University NHS Foundation ... 585 1350 43.333333
4 Sherwood Forest Hospitals NHS Foundation Trust 274 637 43.014129
5 Portsmouth Hospitals NHS Trust 321 753 42.629482
6 Mid Essex Hospital Services NHS Trust 243 592 41.047297
7 Kettering General Hospital NHS Foundation Trust 240 618 38.834951
8 Worcestershire Acute Hospitals NHS Trust 413 1103 37.443336
9 The Dudley Group NHS Foundation Trust 344 951 36.172450

Let's compare:

That display may not be so useful though, because the population sizes differ. Something along the lines of a funnel plot can be used to check rates where rate distrbutions are statistically normal, which may not be the case here.

For now let's stick with a scatter plot withouth any distribution guide lines to see if there are any outliers:

In [31]:
pd.read_sql_query(q3, conn).plot(kind='scatter', x='Total',y='pc');

If we're looking for stories, outliers are a good place to start. For example trusts where there is a high rate of delays (y-axis in chart above), or perhaps a large number of delays.

Let's size the scatter plot by the number of delayed visits to see if any points jump out.

In [32]:
pd.read_sql_query(q3, conn).plot(kind='scatter', x='Total',y='pc', s=tmp['Delayed']/10);

We can also look for trusts whre there is a large number of delays more directly. In this case, a high y-axis value is bad...:

In [33]:
pd.read_sql_query(q3, conn).plot(kind='scatter', x='Total',y='Delayed');

Again, we can add a bit more subtlety to the chart by sizing the nodes, in this case, by the delay rate:

In [34]:
#Size by percent...
tmp=pd.read_sql_query(q3, conn)
tmp.plot(kind='scatter', x='Total',y='Delayed', s=tmp['pc']);

Let's have a go at adding some sort of distribution guide. Anything above the line is what we're looking for...:

In [35]:
import matplotlib.pyplot as plt

tmp.plot(kind='scatter', x='Total',y='pc', s=tmp['Delayed']/10, ylim=(0,100));

#Not sure if I'm doing this right? Normal distribution model is wrong for a start?
import numpy as np
import weightedstats as ws

tmp=tmp
number=tmp['Total']
p=tmp['pc']/100

p_se = np.sqrt( (p*(1-p)) / (number) )

p_fem = ws.weighted_mean(p, weights=p_se)
dff = pd.DataFrame({'Total':np.arange(1, max(tmp['Total']), 20)})
dff['number_ul95'] = 100 * ( p_fem + 1.96 * np.sqrt((p_fem*(1-p_fem)) / dff['Total']) )
dff['number_ul999'] = 100 * ( p_fem + 3.29 * np.sqrt((p_fem*(1-p_fem)) / dff['Total']) )
plt.plot(dff['Total'],dff['number_ul95'] )
plt.plot(dff['Total'],dff['number_ul999'] );

Here's another table we could try to recreate, again via @carlbaker: Bed Occupancy:

But how do we calculate this? The average of the occupancy rates over the week?

Which data sheet(s) does the data come from and how is it analysed to generate that table?

For example, what if we naively just try to take the average of the rates for each trust over the week?

In [36]:
q4='''
SELECT Name, 100*AVG(value) AS av FROM sitrep 
WHERE date(Date) BETWEEN date('{fromdate}') AND date('{todate}') 
AND Category = "Occupancy rate" GROUP BY Name
ORDER BY av DESC
'''.format(fromdate='2017-12-25', todate='2017-12-31')
pd.read_sql_query(q4, conn).head(10)
Out[36]:
Name av
0 Weston Area Health NHS Trust 96.274174
1 James Paget University Hospitals NHS Foundatio... 96.191210
2 Harrogate And District NHS Foundation Trust 95.764972
3 University Hospitals Birmingham NHS Foundation... 95.652122
4 St Helens And Knowsley Hospital Services NHS T... 95.351082
5 King's College Hospital NHS Foundation Trust 94.762360
6 North Middlesex University Hospital NHS Trust 94.109607
7 Nottingham University Hospitals NHS Trust 93.903388
8 Countess Of Chester Hospital NHS Foundation Trust 93.614342
9 North Cumbria University Hospitals NHS Trust 93.088257

This is why I think research reports need to show their working or at least have the working available...

Isle of Wight Report

Sample graphical reports for Isle of Wight.

In [38]:
code='R1F'
In [39]:
typ='G&A beds'

q2='''
SELECT Date, Name, Category, value FROM sitrep WHERE Report='{typ}' AND Category='Occupancy rate'
AND Code = '{code}'
'''.format(typ=typ,code=code)
tmp = pd.read_sql_query(q2, conn, parse_dates=['Date'])
tmp_p = tmp[['Date','Category','value']].pivot_table(index='Date',columns='Category')
tmp_p.columns = tmp_p.columns.get_level_values(1)
tmp_p.plot(title=typ).legend(loc='center left', bbox_to_anchor=(1, 0.5));
In [40]:
typ='Ambulance Arrivals and Delays'

q3='''
SELECT Date, Name, Category, value FROM sitrep WHERE Report='{typ}'
AND Code = '{code}'
'''.format(typ=typ,code=code)
tmp = pd.read_sql_query(q3, conn, parse_dates=['Date'])
tmp_p = tmp[['Date','Category','value']].pivot_table(index='Date',columns='Category')
tmp_p.columns = tmp_p.columns.get_level_values(1)
tmp_p.plot(title=typ).legend(loc='center left', bbox_to_anchor=(1, 0.5));

Are there correlations between ambulance delays and bed availability, I wonder?

In [41]:
q4='''
SELECT Date, Name, Category, value FROM sitrep WHERE Report='Adult critical care'
AND Code = '{code}'
'''.format(code=code)
tmp = pd.read_sql_query(q4, conn, parse_dates=['Date'])
tmp_p = tmp[['Date','Category','value']].pivot_table(index='Date',columns='Category')
tmp_p.columns = tmp_p.columns.get_level_values(1)
tmp_p.plot().legend(loc='center left', bbox_to_anchor=(1, 0.5));
In [42]:
startDate = '2017-12-25'
endDate = '2017-12-31'
In [43]:
q5='''
SELECT Date, Name, Category, value FROM sitrep WHERE Report='Ambulance Arrivals and Delays'
AND date(Date) BETWEEN date('{fromdate}') AND date('{todate}')
AND Code = '{code}'
'''.format(fromdate='2017-12-25', todate='2017-12-31',code=code)
tmp = pd.read_sql_query(q5, conn, parse_dates=['Date'])
tmp_p = tmp[['Date','Category','value']].pivot_table(index='Date',columns='Category')
tmp_p.plot(kind='bar').legend(loc='center left', bbox_to_anchor=(1, 0.5));

Example report: https://onthewight.com/50-patients-stuck-outside-st-marys-in-ambulances-for-up-to-an-hour-in-december/

Ambulances were forced to wait up to an hour at A&E 52 times on the Isle of Wight last month, with emergency patients stranded inside the vehicles waiting to be admitted.

The NHS has released statistics concerning Isle Of Wight NHS Trust as part of a special series which highlights the winter pressures facing the health service.

The figures show that in December, 52 of the Trust’s patients had to spend between half an hour and an hour waiting in an ambulance at hospital, before they could be transferred to the emergency department.

Some for more than an hour 13 were stuck in ambulances for more than 60 minutes.

NHS England’s target time is up to 15 minutes.

The waits, known as handover delays, can be due to ambulance queues or slow processing at hospitals, and can have the knock-on effect of delaying paramedics being despatched to future emergencies.

In total 5.4% of all patients arriving by ambulances at hospital were delayed by between 30 and 60 minutes.

Let's have a go at trying to match those numbers...

In [44]:
q_month='''
SELECT Date, Name, Category, SUM(value) FROM sitrep WHERE Report='Ambulance Arrivals and Delays'
AND date(Date) BETWEEN date('{fromdate}') AND date('{todate}')
AND Code = '{code}'
GROUP BY Category
'''.format(fromdate='2017-12-01', todate='2017-12-31',code=code)
pd.read_sql_query(q_month, conn, parse_dates=['Date'])
Out[44]:
Date Name Category SUM(value)
0 2017-12-31 Isle Of Wight NHS Trust Arriving by ambulance 1455
1 2017-12-31 Isle Of Wight NHS Trust Delay 30-60 mins 59
2 2017-12-31 Isle Of Wight NHS Trust Delay >60 mins 13

I can match the 13 over an hour but not the others?

In [45]:
q_pc='''
SELECT total.Name, Delayed, Total, 100.0*Delayed/Total AS pc FROM (SELECT  Name, SUM(value) AS Delayed FROM sitrep 
WHERE date(Date) BETWEEN date('{fromdate}') AND date('{todate}') 
AND (Category = 'Delay >60 mins' OR Category='Delay 30-60 mins') 
AND Code = '{code}'
AND value NOT NULL GROUP BY Name) delayed JOIN (SELECT  Name, SUM(value) AS Total FROM sitrep 
WHERE date(Date) BETWEEN date('{fromdate}') AND date('{todate}') 
AND Category = 'Arriving by ambulance' 
AND Code = '{code}'
AND value NOT NULL GROUP BY Name) total on total.Name = delayed.Name 
ORDER BY pc DESC
'''.format(fromdate='2017-12-01', todate='2017-12-31',code=code)
pd.read_sql_query(q_pc, conn)
Out[45]:
Name Delayed Total pc
0 Isle Of Wight NHS Trust 72 1455 4.948454

Simple Report Generation

We can generate simple text reports from the data.

For example, something of the form Last week, of M ambulance arrivals overall, N patients at LOCATION Y waited over thirty minutes, of which Z waited over one hour.

In [46]:
def query_ambulance_arrivals(conn, code, period = 'last week'):
    q_simple='''
    SELECT Date, Name, Category, SUM(value) as Total FROM sitrep WHERE Report='Ambulance Arrivals and Delays'
    {period}
    AND Code = '{code}'
    GROUP BY Category
    '''.format(period=getperiod_sql_clause(period), code=code)

    return pd.read_sql_query(q_simple, conn).set_index('Category').to_dict(orient='index')
In [47]:
locationQuery = 'Wight'

trust,code = lookupTrustCode(conn,locationQuery, 'sitrep')
query_ambulance_arrivals(conn, code,'last week')
Out[47]:
{'Arriving by ambulance': {'Date': '2017-12-31 00:00:00',
  'Name': 'Isle Of Wight NHS Trust',
  'Total': 357},
 'Delay 30-60 mins': {'Date': '2017-12-31 00:00:00',
  'Name': 'Isle Of Wight NHS Trust',
  'Total': 18},
 'Delay >60 mins': {'Date': '2017-12-31 00:00:00',
  'Name': 'Isle Of Wight NHS Trust',
  'Total': 3}}
In [48]:
def _report_ambulance_arrivals(repdict, resp):
    repdict['overall'] = P.number_to_words(resp['Arriving by ambulance']['Total'])
    repdict['over60'] = P.number_to_words(resp['Delay >60 mins']['Total'])
    repdict['over30'] = P.number_to_words(resp['Delay 30-60 mins']['Total'])

    txt = '''\
{nl_period}, of {overall} {location} ambulance arrivals overall, {over30} incurred a handover delay between thirty minutes and an hour \
and {over60} had a delay of over an hour.'''.format(**repdict)
    return txt

def report_ambulance_arrivals(conn, locationQuery, period='last week'):
    trust,code = lookupTrustCode(conn,locationQuery, 'sitrep')
    resp = query_ambulance_arrivals(conn, code, period)
    repdict={}
    repdict['location'] = trust
    repdict['nl_period']=period.capitalize()
    return  _report_ambulance_arrivals(repdict, resp)
In [49]:
txt = report_ambulance_arrivals(conn, locationQuery, 'last week')
print(txt)
Last week, of three hundred and fifty-seven Isle Of Wight NHS Trust ambulance arrivals overall, eighteen incurred a handover delay between thirty minutes and an hour and three had a delay of over an hour.

More Example Reports

In [50]:
q_month='''
SELECT Date, Name, Category, value FROM sitrep WHERE Report='Ambulance Arrivals and Delays'
AND date(Date) BETWEEN date('{fromdate}') AND date('{todate}')
AND Code = '{code}' 
AND Category = 'Delay 30-60 mins'
ORDER BY Category,Date
'''.format(fromdate='2017-12-01', todate='2017-12-31',code=code)
pd.read_sql_query(q_month, conn, parse_dates=['Date'])
Out[50]:
Date Name Category value
0 2017-12-01 Isle Of Wight NHS Trust Delay 30-60 mins 0
1 2017-12-02 Isle Of Wight NHS Trust Delay 30-60 mins 2
2 2017-12-03 Isle Of Wight NHS Trust Delay 30-60 mins 1
3 2017-12-04 Isle Of Wight NHS Trust Delay 30-60 mins 3
4 2017-12-05 Isle Of Wight NHS Trust Delay 30-60 mins 0
5 2017-12-06 Isle Of Wight NHS Trust Delay 30-60 mins 0
6 2017-12-07 Isle Of Wight NHS Trust Delay 30-60 mins 2
7 2017-12-08 Isle Of Wight NHS Trust Delay 30-60 mins 5
8 2017-12-09 Isle Of Wight NHS Trust Delay 30-60 mins 1
9 2017-12-10 Isle Of Wight NHS Trust Delay 30-60 mins 2
10 2017-12-11 Isle Of Wight NHS Trust Delay 30-60 mins 2
11 2017-12-12 Isle Of Wight NHS Trust Delay 30-60 mins 2
12 2017-12-13 Isle Of Wight NHS Trust Delay 30-60 mins 1
13 2017-12-14 Isle Of Wight NHS Trust Delay 30-60 mins 2
14 2017-12-15 Isle Of Wight NHS Trust Delay 30-60 mins 4
15 2017-12-16 Isle Of Wight NHS Trust Delay 30-60 mins 0
16 2017-12-17 Isle Of Wight NHS Trust Delay 30-60 mins 2
17 2017-12-18 Isle Of Wight NHS Trust Delay 30-60 mins 5
18 2017-12-19 Isle Of Wight NHS Trust Delay 30-60 mins 2
19 2017-12-20 Isle Of Wight NHS Trust Delay 30-60 mins 3
20 2017-12-21 Isle Of Wight NHS Trust Delay 30-60 mins 0
21 2017-12-22 Isle Of Wight NHS Trust Delay 30-60 mins 0
22 2017-12-23 Isle Of Wight NHS Trust Delay 30-60 mins 0
23 2017-12-24 Isle Of Wight NHS Trust Delay 30-60 mins 2
24 2017-12-25 Isle Of Wight NHS Trust Delay 30-60 mins 2
25 2017-12-26 Isle Of Wight NHS Trust Delay 30-60 mins 2
26 2017-12-27 Isle Of Wight NHS Trust Delay 30-60 mins 0
27 2017-12-28 Isle Of Wight NHS Trust Delay 30-60 mins 6
28 2017-12-29 Isle Of Wight NHS Trust Delay 30-60 mins 3
29 2017-12-30 Isle Of Wight NHS Trust Delay 30-60 mins 3
30 2017-12-31 Isle Of Wight NHS Trust Delay 30-60 mins 2
In [51]:
#Beds last month
fromdate,todate=ntpd.last_month(iso=True)

q_month_beds='''
SELECT Date, Name, Category, value FROM sitrep WHERE Report='G&A beds'
AND date(Date) BETWEEN date('{fromdate}') AND date('{todate}')
AND Code = '{code}'
'''.format(fromdate=fromdate, todate=todate,code=code)
tmp_b = pd.read_sql_query(q_month_beds, conn, parse_dates=['Date'])


tmp_b = tmp_b[['Date','Category','value']].pivot_table(index='Date',columns='Category')

#tmp_b[('value','Occupancy rate')] = 100*tmp_b[('value','Occupancy rate')]
tmp_b.columns = tmp_b.columns.get_level_values(1)
tmp_b['Occupancy rate'] = 100*tmp_b['Occupancy rate']


tmp_b.plot().legend(loc='center left', bbox_to_anchor=(1, 0.5))
plt.axhline(y=100, color='r', linestyle=':');

Days when G&A escalation beds were open:

In [52]:
q_esc_beds_open_on='''
SELECT Date FROM sitrep 
WHERE date(Date) BETWEEN date('{fromdate}') AND date('{todate}') 
AND Category ='Escalation Beds Open' AND value > 0.0
AND Code = '{code}'
'''.format(fromdate='2017-12-01', todate='2017-12-31',code=code)

pd.read_sql_query(q_esc_beds_open_on, conn, parse_dates=['Date'])
Out[52]:
Date
0 2017-12-04
1 2017-12-11
2 2017-12-12
3 2017-12-17
In [53]:
q_peak_occ_beds = '''
SELECT Date, Category, value FROM sitrep WHERE Date IN (SELECT Date FROM sitrep 
WHERE date(Date) BETWEEN date('{fromdate}') AND date('{todate}') 
AND Category = 'Occupancy rate'
AND Code = '{code}'
ORDER BY value DESC LIMIT 5 ) 
AND Report = 'G&A beds' AND Code = '{code}'
'''.format(fromdate='2017-12-01', todate='2017-12-31',code=code)

pd.read_sql_query(q_peak_occ_beds, conn, parse_dates=['Date'])
Out[53]:
Date Category value
0 2017-12-02 Core Beds Open 246.000000
1 2017-12-02 Escalation Beds Open 0.000000
2 2017-12-02 Total Beds Open 246.000000
3 2017-12-02 Total beds occ'd 208.000000
4 2017-12-02 Occupancy rate 0.845528
5 2017-12-03 Core Beds Open 246.000000
6 2017-12-03 Escalation Beds Open 0.000000
7 2017-12-03 Total Beds Open 246.000000
8 2017-12-03 Total beds occ'd 224.000000
9 2017-12-03 Occupancy rate 0.910569
10 2017-12-04 Core Beds Open 246.000000
11 2017-12-04 Escalation Beds Open 1.000000
12 2017-12-04 Total Beds Open 247.000000
13 2017-12-04 Total beds occ'd 231.000000
14 2017-12-04 Occupancy rate 0.935223
15 2017-12-05 Core Beds Open 246.000000
16 2017-12-05 Escalation Beds Open 0.000000
17 2017-12-05 Total Beds Open 246.000000
18 2017-12-05 Total beds occ'd 237.000000
19 2017-12-05 Occupancy rate 0.963415
20 2017-12-17 Core Beds Open 246.000000
21 2017-12-17 Escalation Beds Open 3.000000
22 2017-12-17 Total Beds Open 249.000000
23 2017-12-17 Total beds occ'd 249.000000
24 2017-12-17 Occupancy rate 1.000000
In [54]:
q_fully_occupied = '''
SELECT Date, Category, value FROM sitrep 
WHERE date(Date) BETWEEN date('{fromdate}') AND date('{todate}') 
AND Category = 'Occupancy rate'
AND Code = '{code}'
AND value = 1
AND Report = 'G&A beds' 
AND Code = '{code}'
'''.format(fromdate='2017-12-01', todate='2017-12-31',code=code)

pd.read_sql_query(q_fully_occupied, conn, parse_dates=['Date'])
Out[54]:
Date Category value
0 2017-12-17 Occupancy rate 1
In [55]:
q_month_esc_beds='''
SELECT Date, Name, Category, value FROM sitrep 
WHERE Report='G&A beds'
AND Code = '{code}'
AND Date IN 
    (SELECT Date FROM sitrep 
        WHERE date(Date) BETWEEN date('{fromdate}') AND date('{todate}') 
        AND Category ='Escalation Beds Open' AND value > 0.0
        AND Code = '{code}'
    ) 
'''.format(fromdate='2017-12-01', todate='2017-12-31',code=code)

pd.read_sql_query(q_month_esc_beds, conn, parse_dates=['Date'])
Out[55]:
Date Name Category value
0 2017-12-04 Isle Of Wight NHS Trust Core Beds Open 246.000000
1 2017-12-04 Isle Of Wight NHS Trust Escalation Beds Open 1.000000
2 2017-12-04 Isle Of Wight NHS Trust Total Beds Open 247.000000
3 2017-12-04 Isle Of Wight NHS Trust Total beds occ'd 231.000000
4 2017-12-04 Isle Of Wight NHS Trust Occupancy rate 0.935223
5 2017-12-11 Isle Of Wight NHS Trust Core Beds Open 246.000000
6 2017-12-11 Isle Of Wight NHS Trust Escalation Beds Open 1.000000
7 2017-12-11 Isle Of Wight NHS Trust Total Beds Open 247.000000
8 2017-12-11 Isle Of Wight NHS Trust Total beds occ'd 246.000000
9 2017-12-11 Isle Of Wight NHS Trust Occupancy rate 0.995951
10 2017-12-12 Isle Of Wight NHS Trust Core Beds Open 241.000000
11 2017-12-12 Isle Of Wight NHS Trust Escalation Beds Open 5.000000
12 2017-12-12 Isle Of Wight NHS Trust Total Beds Open 246.000000
13 2017-12-12 Isle Of Wight NHS Trust Total beds occ'd 245.000000
14 2017-12-12 Isle Of Wight NHS Trust Occupancy rate 0.995935
15 2017-12-17 Isle Of Wight NHS Trust Core Beds Open 246.000000
16 2017-12-17 Isle Of Wight NHS Trust Escalation Beds Open 3.000000
17 2017-12-17 Isle Of Wight NHS Trust Total Beds Open 249.000000
18 2017-12-17 Isle Of Wight NHS Trust Total beds occ'd 249.000000
19 2017-12-17 Isle Of Wight NHS Trust Occupancy rate 1.000000
In [56]:
q_peak_occ_beds_acc = '''
SELECT Date, Category, value FROM sitrep WHERE Date IN (SELECT Date FROM sitrep 
WHERE date(Date) BETWEEN date('{fromdate}') AND date('{todate}') 
AND Category = 'Occupancy rate'
AND Code = '{code}'
ORDER BY value DESC LIMIT 5 ) 
AND Report = 'Adult critical care' AND Code = '{code}'
'''.format(fromdate='2017-12-01', todate='2017-12-31',code=code)

pd.read_sql_query(q_peak_occ_beds_acc, conn, parse_dates=['Date'])
Out[56]:
Date Category value
0 2017-12-02 CC Adult avail 6
1 2017-12-02 CC Adult Occ 6
2 2017-12-02 Occupancy rate 1
3 2017-12-03 CC Adult avail 6
4 2017-12-03 CC Adult Occ 6
5 2017-12-03 Occupancy rate 1
6 2017-12-04 CC Adult avail 7
7 2017-12-04 CC Adult Occ 7
8 2017-12-04 Occupancy rate 1
9 2017-12-05 CC Adult avail 6
10 2017-12-05 CC Adult Occ 6
11 2017-12-05 Occupancy rate 1
12 2017-12-17 CC Adult Open 7
13 2017-12-17 CC Adult Occ 7
14 2017-12-17 Occupancy rate 1

NHS 111

In [57]:
pd.read_sql_query("SELECT DISTINCT Report, Category FROM nhs111;", conn)
Out[57]:
Report Category
0 Answered in 60 Calls answered within 60 Seconds
1 Answered in 60 Calls answered
2 Abandoned Calls abandoned after at least 30 seconds waiting
3 Abandoned Calls offered
4 Triage Calls where person triaged
5 Clinical Advisor Calls transferred to or answered by a clinical...
6 Clinical Input Calls to a CAS clinician
7 Call Back Calls back within 10 minutes
8 Call Back Calls where person offered call back
9 Dispositions Ambulance dispatches
10 Dispositions Recommended to attend A&E
11 Dispositions Recommended to attend primary and community care
12 Dispositions Recommended to contact primary care
13 Dispositions Recommended to speak to primary care
14 Dispositions Recommended to dental
15 Dispositions Recommended to pharmacy
16 Dispositions Recommended to attend other service
17 Dispositions Not recommended to attend other service
18 Dispositions Given health information
19 Dispositions Recommended home Care
20 Dispositions Recommended non clinical
In [58]:
def disposition_report(conn, code, nl_period=None):
    q='''SELECT DISTINCT Report, Category, Code, SUM(value) as Total FROM nhs111 
    WHERE Report='Dispositions'
    AND Code= '{code}'
    {period}
    GROUP BY Category;
    
    '''.format(code=code, period=getperiod_sql_clause(nl_period))
    return pd.read_sql_query(q, conn)
                      
code= '111AA6'
disposition_report(conn, code, 'last month' )
Out[58]:
Report Category Code Total
0 Dispositions Ambulance dispatches 111AA6 1058.0
1 Dispositions Given health information 111AA6 90.0
2 Dispositions Not recommended to attend other service 111AA6 1031.0
3 Dispositions Recommended home Care 111AA6 288.0
4 Dispositions Recommended non clinical 111AA6 653.0
5 Dispositions Recommended to attend A&E 111AA6 601.0
6 Dispositions Recommended to attend other service 111AA6 250.0
7 Dispositions Recommended to attend primary and community care 111AA6 4412.0
8 Dispositions Recommended to contact primary care 111AA6 3049.0
9 Dispositions Recommended to dental 111AA6 460.0
10 Dispositions Recommended to pharmacy 111AA6 16.0
11 Dispositions Recommended to speak to primary care 111AA6 887.0

Generate a written report

Perhaps also offer ability to compare with previous same period?

In [59]:
def bulleted_list_builder(reps, header=''):
    for rep in reps:
        header+='\n\t- {}'.format(rep)
    return header

def report_disposition(conn,code,nl_period=None, intify=None):
    def _reporter(row):
        txt='''{Category}: {Total}'''.format(**row)
        return txt
    df = disposition_report(conn, code, nl_period )
    if intify:
        df[intify]=df[intify].astype(int)

    return df.apply(_reporter,axis=1)

period = 'last month'
locationQuery = 'Wight'

trust,code = lookupTrustCode(conn,locationQuery, 'nhs111')


print( bulleted_list_builder( report_disposition(conn,code,period, intify='Total' ),
                             header = 'NHS111 dispositions for {} {} {}:'.format(trust, period,period_text(period) )) )
NHS111 dispositions for Isle Of Wight NHS 111 last month (Friday 01 December 2017 to Sunday 31 December 2017):
	- Ambulance dispatches: 1058
	- Given health information: 90
	- Not recommended to attend other service: 1031
	- Recommended home Care: 288
	- Recommended non clinical: 653
	- Recommended to attend A&E: 601
	- Recommended to attend other service: 250
	- Recommended to attend primary and community care: 4412
	- Recommended to contact primary care: 3049
	- Recommended to dental: 460
	- Recommended to pharmacy: 16
	- Recommended to speak to primary care: 887