#!/usr/bin/env python
# coding: utf-8

# https://www.england.nhs.uk/statistics/statistical-work-areas/nhs-111-minimum-data-set/nhs-111-minimum-data-set-2016-17/

# In[6]:


get_ipython().run_line_magic('matplotlib', 'inline')
import pandas as pd
import seaborn as sns


# In[3]:


get_ipython().system('wget -P data/ https://www.england.nhs.uk/statistics/wp-content/uploads/sites/2/2016/06/NHS-111-from-Aug10-to-Nov16-web-file.csv')


# In[ ]:


#Detailed Excel data files (require parsers...)
#MDS-Web-File-National-November-2016.xlsx
#MDS-Web-File-North-November-2016.xlsx
#MDS-Web-File-London-November-2016.xlsx
#MDS-Web-File-Midlands-East-England-November-2016.xlsx
#!wget -P data/ https://www.england.nhs.uk/statistics/wp-content/uploads/sites/2/2016/06/MDS-Web-File-South-November-2016.xlsx


# In[2]:


df=pd.read_csv('data/NHS-111-from-Aug10-to-Nov16-web-file.csv',skiprows=4)
df.head()


# In[23]:


df.columns


# In[3]:


df['_period']=pd.to_datetime(('01-'+df['Periodname']),format='%d-%b-%y').dt.to_period('M')
df.head()


# In[4]:


df.groupby(['_period','Provider Name'])['Nhs1 Recommend To Ae SUM'].sum().plot()


# In[7]:


df.groupby(['_period','Provider Name'])['Nhs1 Recommend To Ae SUM'].sum().unstack(1).fillna(0).plot()


# In[8]:


df.groupby(['_period'])['Nhs1 Recommend To Ae SUM'].sum().fillna(0).plot()


# In[28]:


cols=[c for c in df.columns if c.startswith('Nhs1 Rec')]
sns.set_palette("Set2", len(cols))
pd.melt(df,id_vars='_period',value_vars=cols
       ).groupby(['_period','variable']).sum().unstack(1).fillna(0).plot()


# In[29]:


#A&E referrals: https://www.england.nhs.uk/statistics/statistical-work-areas/ae-waiting-times-and-activity/statistical-work-areasae-waiting-times-and-activityae-attendances-and-emergency-admissions-2016-17/
get_ipython().system('wget -P data/ https://www.england.nhs.uk/statistics/wp-content/uploads/sites/2/2016/06/Quarterly-timeseries-Nov-2016.xls')


# In[30]:


get_ipython().run_line_magic('pinfo', 'pd.read_excel')


# In[62]:


df2=pd.read_excel('data/Quarterly-timeseries-Nov-2016.xls',skiprows=16,na_values='-').dropna(how='all')
df2['Year'].fillna(method='ffill',inplace=True)
#First group of cols are A&E attendances. I really need to sort out how to read multirow headers!
df2.head()


# In[64]:


df2[['Type 1 Departments - Major A&E',
     'Type 2 Departments - Single Specialty',
     'Type 3 Departments - Other A&E/Minor Injury Unit']].plot();


# In[65]:


df2['period']=df2['Year']+df2['Quarter']


# In[72]:


ax=df2[['Type 1 Departments - Major A&E',
     'Type 2 Departments - Single Specialty',
     'Type 3 Departments - Other A&E/Minor Injury Unit']][:-4].plot(logy=True, xticks=df2.index, rot=90)
#ax.set_xticklabels(df2['period'])
ticks = ax.xaxis.get_ticklocs()
ax.xaxis.set_ticks(ticks[::8])
ax.xaxis.set_ticklabels(df2['period'][::8]);


# In[38]:


df2["Year"].unique()


# In[67]:


df2[:-4].tail()


# In[ ]: