Representing Time Periods¶

#Download example data file
url='https://www.england.nhs.uk/statistics/wp-content/uploads/sites/2/2016/06/Quarterly-timeseries-May-2017.xls'
!wget -P data/ {url}

import pandas as pd

#Preview data
dfw=pd.read_excel('data/Quarterly-timeseries-May-2017.xls',
                  skiprows=15,header=None,na_values='-').dropna(how='all').dropna(axis=1,how='all')
dfw.iloc[0,0]='Reporting'
dfw.iloc[1,0]='Code'

dfw= dfw.fillna(axis=1,method='ffill').T.set_index([0,1]).T.dropna(how='all').dropna(axis=1,how='all')
dfw=dfw.reset_index(drop=True)
dfw.head()

dfw[[('Reporting','Year'),('Reporting','Quarter')]].head()

dfw['Reporting','Year'].fillna(method='ffill',inplace=True)
dfw['Reporting','period']=dfw['Reporting','Year']+' '+dfw['Reporting','Quarter']
dfw[[('Reporting','Year'),('Reporting','Quarter'),('Reporting','period')]].head()

#Return a date that we can use to anchor a period on
def getMonthYear(row):
    if not row['Reporting','Quarter'].startswith('Q'): return ''
    month=row['Reporting','Quarter'].split(':')[1].split('-')[0].strip()
    year=int(row['Reporting','Year'].split('-')[0])
    if month in ['Jan']:
        year= year+1
    #Following the conversion, the _quarter year specifies the calendar year in which the financial year ends
    #Return the date corresponding to the first day of the month in the calendar year the financial quarter applies to
    return pd.to_datetime("01-{}-{}".format(month[:3],year),format='%d-%b-%Y')

#Get a date corrsponding to the first day in the month of the first month in the quarter, by calendar year
#Use this date to align a particular quarterly period
#Q-MAR specifies a quarterly frequency, with year ending in March
dfw['Reporting','_quarter']=pd.PeriodIndex(dfw.apply(getMonthYear,axis=1), freq='Q-MAR')

#Sort the columns
dfw=dfw.sort_index(axis=1)
dfw=dfw.reindex(columns=['Reporting']+[c for c in dfw.columns.levels[0] if c!='Reporting'],level=0)

#Preview the data with the quarter time period column defined
dfw[[('Reporting','Year'),('Reporting','Quarter'),('Reporting','period'),('Reporting','_quarter')]].head()

url='https://www.england.nhs.uk/statistics/wp-content/uploads/sites/2/2017/06/May-17-Monthly-AE-Timeseries-with-growth-charts.xlsx'
!wget -P data/ {url}

dfw=pd.read_excel('data/May-17-Monthly-AE-Timeseries-with-growth-charts.xlsx',skiprows=16,header=None,na_values='-').dropna(how='all').dropna(axis=1,how='all')
dfw.iloc[0,0]='Reporting'
dfw.iloc[1,0]='Code'

dfw= dfw.fillna(axis=1,method='ffill').T.set_index([0,1]).T.dropna(how='all').dropna(axis=1,how='all')
dfw.head()

#Cast the date corresponding to the month start as a month period starting on that date
dfw['Reporting','_period']=pd.to_datetime(dfw['Reporting','Period'],
                                          format='%d/%m/%Y').dt.to_period('M')

dfw=dfw.sort_index(axis=1)
dfw=dfw.reindex(columns=['Reporting']+[c for c in dfw.columns.levels[0] if c!='Reporting'],level=0)
dfw[[('Reporting','Period'),('Reporting','_period')]].head()

url='https://www.england.nhs.uk/statistics/wp-content/uploads/sites/2/2015/04/2015.06.28-AE-TimeseriesBaG87.xls'
!wget -P data/ {url}

dfw=pd.read_excel('data/2015.06.28-AE-TimeseriesBaG87.xls',
                  skiprows=14,header=None,na_values='-').dropna(how='all').dropna(axis=1,how='all')
dfw.iloc[0,0]='Reporting'
dfw.iloc[1,0]='Code'

dfw= dfw.fillna(axis=1,method='ffill').T.set_index([0,1]).T.dropna(how='all').dropna(axis=1,how='all')

dfw.head()

#Filter to retain rows that have a W/E period specified
dfw=dfw[dfw[('Reporting','Period')].str.startswith('W/E')]

#Remove the W/E prefix and use the date to define
dfw['Reporting','_period']=pd.to_datetime(dfw['Reporting','Period'].str.replace('W/E ',''),
                                          format='%d/%m/%Y').dt.to_period('W') 

dfw[[('Reporting','Period'),('Reporting','_period')]].head()