<jupyter_start><jupyter_text>Retrieve URLs from link:https://www.gov.uk/government/statistics/national-flu-and-covid-19-surveillance-reports-2021-to-2022-season (destination for weekly UKHSA publishing of CV19 stats)Choose only those ending with .xls for excel files** Needs updating - GOV.uk switched recently to ODS files **<jupyter_code>url = "https://www.gov.uk/government/statistics/national-flu-and-covid-19-surveillance-reports-2021-to-2022-season"

soup = BeautifulSoup(requests.get(url).content, "html.parser")

links=[]


for link in soup.select('a[href*=".xls"]'):
    links.append(link["href"])<jupyter_output><empty_output><jupyter_text>Choose the first (most recent) URL<jupyter_code>url = links[0]<jupyter_output><empty_output><jupyter_text>Convert URL just chosen to readable xls<jupyter_code>xls = pd.ExcelFile(url)<jupyter_output><empty_output><jupyter_text>Find date of publication using the workbook and convert this to a readable date<jupyter_code>Date_Pub = pd.read_excel(xls, 'Contents',
                   header=1,
                   usecols='C')
name_me = [(i,'Column') for i in Date_Pub.iloc[0:, :].columns.values]
Date_Pub.rename(columns = dict(name_me), inplace=True)
Pub_date = Date_Pub['Column'].iloc[0]

input_string = Pub_date
matches = list(datefinder.find_dates(input_string))

if len(matches) > 0:
    date = matches[0]
    
dates = [_ for _ in matches] # 'dates = list(matches)' also works
df = pd.DataFrame({'dt_index':dates}).set_index('dt_index')
df['date'] = df.index
df['date'] = pd.to_datetime(df['date'])
df['date'] = df['date'].dt.date
df = df.reset_index()
df = df.drop([1,2] )<jupyter_output><empty_output><jupyter_text>Clean a worksheet pertaining to weekly case rates. Converts 'index' to dates.<jupyter_code>WeeklyBySex = pd.read_excel(xls, 'Figure 4. Case rates by sex',
                   header=8,
                   usecols="C, D")
Cleaned_weekly_Sex = WeeklyBySex.add_prefix('Cases: ')
Cleaned_weekly_Sex = Cleaned_weekly_Sex.reset_index()
Cleaned_weekly_Sex2 = (pd.melt(Cleaned_weekly_Sex, id_vars=['index'], value_name='Cases'))
Cleaned_weekly_Sex2[['tmp','Sex']] = Cleaned_weekly_Sex2.variable.str.split(': ', expand=True)
Cleaned_weekly_Sex2 = Cleaned_weekly_Sex2.drop(['variable', 'tmp'],axis=1).sort_values(['index','Sex'])
Cleaned_weekly_Sex2['Sex'] = Cleaned_weekly_Sex2.Sex.astype(str)
Cleaned_weekly_Sex2 = Cleaned_weekly_Sex2[['index','Sex','Cases']]

Cleaned_weekly_Sex2['DateStart'] = date
Cleaned_weekly_Sex2['ReverseIndex'] = 52 - Cleaned_weekly_Sex2['index']
Cleaned_weekly_Sex2['Factor'] = (Cleaned_weekly_Sex2.ReverseIndex)*7
Cleaned_weekly_Sex2["Date"] = pd.to_datetime(Cleaned_weekly_Sex2["DateStart"]) - pd.to_timedelta(Cleaned_weekly_Sex2['Factor'], unit='D')


Cleaned_weekly_Sex2.drop(['DateStart', 'ReverseIndex','Factor','index'], axis=1, inplace=True)<jupyter_output><empty_output><jupyter_text>Age<jupyter_code>cols_age = ['0 to 4', '5 to 9', '10 to 19', '20 to 29', '30 to 39', '40 to 49', '50 to 59', 
               '60 to 69', '70 to 79', '80+']
WeeklyByAge = pd.read_excel(xls, 'Figure 5. Case rates by agegrp',
                   header=8,
                   usecols=lambda x: x.lower() in cols_age)
Cleaned_weekly_Age = WeeklyByAge.add_prefix('Cases: ')
Cleaned_weekly_Age = Cleaned_weekly_Age.reset_index()
Cleaned_weekly_1 = (pd.melt(Cleaned_weekly_Age, id_vars=['index'], value_name='Cases'))
Cleaned_weekly_1[['tmp','Age']] = Cleaned_weekly_1.variable.str.split(': ', expand=True)
Cleaned_weekly_1 = Cleaned_weekly_1.drop(['variable', 'tmp'],axis=1).sort_values(['index','Age'])
Cleaned_weekly_1['Age'] = Cleaned_weekly_1.Age.astype(str)
Cleaned_weekly_1 = Cleaned_weekly_1[['index','Age','Cases']]

Cleaned_weekly_1['DateStart'] = date
Cleaned_weekly_1['ReverseIndex'] = 52 - Cleaned_weekly_1['index']
Cleaned_weekly_1['Factor'] = (Cleaned_weekly_1.ReverseIndex)*7
Cleaned_weekly_1["Date"] = pd.to_datetime(Cleaned_weekly_1["DateStart"]) - pd.to_timedelta(Cleaned_weekly_1['Factor'], unit='D')

Cleaned_weekly_1.drop(['DateStart', 'ReverseIndex','Factor','index'], axis=1, inplace=True)<jupyter_output><empty_output><jupyter_text>Positivity<jupyter_code>WeeklyPositivity = pd.read_excel(xls, 'Figure 2. Positivity by test',
                   header=7,
                   usecols="C, D, E, F")
positivenames = [(i,'Posivity: '+i) for i in WeeklyPositivity.iloc[:, 2:].columns.values]
takennames = [(i,'Taken: '+i) for i in WeeklyPositivity.iloc[0:, :].columns.values]
WeeklyPositivity.rename(columns = dict(positivenames), inplace=True)
WeeklyPositivity.rename(columns = dict(takennames), inplace=True)
Cleaned_weekly_Pos = WeeklyPositivity.reset_index()
Cleaned_weekly_Pos2 = (pd.melt(Cleaned_weekly_Pos, id_vars=['index'], value_name='Value'))
Cleaned_weekly_Pos2[['tmp','Type']] = Cleaned_weekly_Pos2.variable.str.split(':', expand=True)
Cleaned_weekly_Pos2 = Cleaned_weekly_Pos2.drop(['variable', 'tmp'],axis=1).sort_values(['index','Type'])
Cleaned_weekly_Pos2['Type'] = Cleaned_weekly_Pos2.Type.astype(str)
Cleaned_weekly_Pos2 = Cleaned_weekly_Pos2[['index','Type','Value']]

Cleaned_weekly_Pos2['DateStart'] = date
Cleaned_weekly_Pos2['ReverseIndex'] = 52 - Cleaned_weekly_Pos2['index']
Cleaned_weekly_Pos2['Factor'] = (Cleaned_weekly_Pos2.ReverseIndex)*7
Cleaned_weekly_Pos2["Date"] = pd.to_datetime(Cleaned_weekly_Pos2["DateStart"]) - pd.to_timedelta(Cleaned_weekly_Pos2['Factor'], unit='D')



Cleaned_weekly_Pos2.drop(['DateStart', 'ReverseIndex','Factor','index'], axis=1, inplace=True)<jupyter_output><empty_output><jupyter_text>Local authority cases<jupyter_code>WeeklyUTLA = pd.read_excel(xls, 'Figure 10. Weekly rates UTLA',
                   header=7,
                   usecols="B:BD")
names_weeks = [(i,'WeeksOfYear: '+i) for i in WeeklyUTLA.iloc[:, 2:].columns.values]
WeeklyUTLA.rename(columns = dict(names_weeks), inplace=True)
WeeklyUTLA = WeeklyUTLA.dropna()
Cleaned_weekly_UTLA = WeeklyUTLA.reset_index()
Cleaned_weekly_UTLA2 = (pd.melt(Cleaned_weekly_UTLA, 
                                id_vars=['index','UTLA name', 'UTLA code'], value_name='CasesPer100k'))
Cleaned_weekly_UTLA2[['tmp','WeeksOfYear']] = Cleaned_weekly_UTLA2.variable.str.split(':', expand=True)
Cleaned_weekly_UTLA2 = Cleaned_weekly_UTLA2.drop(['variable', 'tmp'],axis=1).sort_values(['index','WeeksOfYear'])
Cleaned_weekly_UTLA2['CasesPer100k'] = Cleaned_weekly_UTLA2.CasesPer100k.astype(str)
Cleaned_weekly_UTLA2 = Cleaned_weekly_UTLA2[['index','UTLA code','UTLA name','CasesPer100k','WeeksOfYear']]

Cleaned_weekly_UTLA2['DateStart'] = date
Cleaned_weekly_UTLA2['ReverseIndex'] = 52 - Cleaned_weekly_UTLA2['index']
Cleaned_weekly_UTLA2['Factor'] = (Cleaned_weekly_UTLA2.ReverseIndex)*7
Cleaned_weekly_UTLA2["Date"] = pd.to_datetime(Cleaned_weekly_UTLA2["DateStart"]) - pd.to_timedelta(Cleaned_weekly_UTLA2['Factor'], unit='D')


Cleaned_weekly_UTLA2.drop(['DateStart', 'ReverseIndex','Factor','index'], axis=1, inplace=True)<jupyter_output><empty_output><jupyter_text>Regional positivity<jupyter_code>WeeklyRegion = pd.read_excel(xls, 'Figure 9. Positivity by region',
                   header=8,
                   usecols="B:K")
names_regions = [(i,'Region: '+i) for i in WeeklyRegion.iloc[:, 1:].columns.values]
names_Wregions = [(i,'Week') for i in WeeklyRegion.iloc[1:, :].columns.values]
WeeklyRegion.rename(columns = dict(names_regions), inplace=True)
WeeklyRegion.rename(columns = dict(names_Wregions), inplace=True)
WeeklyRegion = WeeklyRegion.dropna()
Cleaned_weekly_Region = WeeklyRegion.reset_index()
Cleaned_weekly_Region2 = (pd.melt(Cleaned_weekly_Region, 
                                id_vars=['index','Week'], value_name='Positivity'))
Cleaned_weekly_Region2[['tmp','Region']] = Cleaned_weekly_Region2.variable.str.split(':', expand=True)
Cleaned_weekly_Region2 = Cleaned_weekly_Region2.drop(['variable', 'tmp'],axis=1).sort_values(['index','Region'])
Cleaned_weekly_Region2['Positivity'] = Cleaned_weekly_Region2.Positivity.astype(str)
Cleaned_weekly_Region2 = Cleaned_weekly_Region2[['index','Region','Positivity','Week']]

Cleaned_weekly_Region2['DateStart'] = date
Cleaned_weekly_Region2['ReverseIndex'] = 52 - Cleaned_weekly_Region2['index']
Cleaned_weekly_Region2['Factor'] = (Cleaned_weekly_Region2.ReverseIndex)*7
Cleaned_weekly_Region2["Date"] = pd.to_datetime(Cleaned_weekly_Region2["DateStart"]) - pd.to_timedelta(Cleaned_weekly_Region2['Factor'], unit='D')



Cleaned_weekly_Region2.drop(['DateStart', 'ReverseIndex','Factor','index'], axis=1, inplace=True)<jupyter_output><empty_output><jupyter_text>Regional cases<jupyter_code>WeeklyRegionCases = pd.read_excel(xls, 'Figure 8. Cases rates by region',
                   header=8,
                   usecols="C:K")
names_regions = [(i,'Region: '+i) for i in WeeklyRegionCases.iloc[:, :].columns.values]
WeeklyRegionCases.rename(columns = dict(names_regions), inplace=True)

WeeklyRegionCases = WeeklyRegionCases.dropna()
Cleaned_weekly_Regionc = WeeklyRegionCases.reset_index()
Cleaned_weekly_Regionc2 = (pd.melt(Cleaned_weekly_Regionc, 
                                id_vars=['index'], value_name='Cases'))
Cleaned_weekly_Regionc2[['tmp','Region']] = Cleaned_weekly_Regionc2.variable.str.split(':', expand=True)
Cleaned_weekly_Regionc2 = Cleaned_weekly_Regionc2.drop(['variable', 'tmp'],axis=1).sort_values(['index','Region'])
Cleaned_weekly_Regionc2['Cases'] = Cleaned_weekly_Regionc2.Cases.astype(str)
Cleaned_weekly_Regionc2 = Cleaned_weekly_Regionc2[['index','Region','Cases']]

Cleaned_weekly_Regionc2['DateStart'] = date
Cleaned_weekly_Regionc2['ReverseIndex'] = 52 - Cleaned_weekly_Regionc2['index']
Cleaned_weekly_Regionc2['Factor'] = (Cleaned_weekly_Regionc2.ReverseIndex)*7
Cleaned_weekly_Regionc2["Date"] = pd.to_datetime(Cleaned_weekly_Regionc2["DateStart"]) - pd.to_timedelta(Cleaned_weekly_Regionc2['Factor'], unit='D')



Cleaned_weekly_Regionc2.drop(['DateStart', 'ReverseIndex','Factor','index'], axis=1, inplace=True)<jupyter_output><empty_output><jupyter_text>Non-ICU admissions by Ethnicity<jupyter_code>WeeklyNICAdmitsEth = pd.read_excel(xls, 'Supplem 2. SARIWatch-hospet',
                   header=7,
                   usecols="C, D, E, F, G")
Ethnames = [(i,'Ethnicity: '+i) for i in WeeklyNICAdmitsEth.iloc[:, 0:].columns.values]
WeeklyNICAdmitsEth.rename(columns = dict(Ethnames), inplace=True)

WeeklyNICAdmitsEth = WeeklyNICAdmitsEth.reset_index()
WeeklyNICAdmitsEth2 = (pd.melt(WeeklyNICAdmitsEth, id_vars=['index'], value_name='Admissionsper100k'))
WeeklyNICAdmitsEth2[['tmp','Ethnicity']] = WeeklyNICAdmitsEth2.variable.str.split(':', expand=True)
WeeklyNICAdmitsEth2 = WeeklyNICAdmitsEth2.drop(['variable', 'tmp'],axis=1).sort_values(['index','Ethnicity'])
WeeklyNICAdmitsEth2['Admissionsper100k'] = WeeklyNICAdmitsEth2.Admissionsper100k.astype(str)
WeeklyNICAdmitsEth2 = WeeklyNICAdmitsEth2[['index','Admissionsper100k','Ethnicity']]

WeeklyNICAdmitsEth2['DateStart'] = date
WeeklyNICAdmitsEth2['ReverseIndex'] = 52 - WeeklyNICAdmitsEth2['index']
WeeklyNICAdmitsEth2['Factor'] = (WeeklyNICAdmitsEth2.ReverseIndex)*7
WeeklyNICAdmitsEth2["Date"] = pd.to_datetime(WeeklyNICAdmitsEth2["DateStart"]) - pd.to_timedelta(WeeklyNICAdmitsEth2['Factor'], unit='D')



WeeklyNICAdmitsEth2.drop(['DateStart', 'ReverseIndex','Factor','index'], axis=1, inplace=True)<jupyter_output><empty_output><jupyter_text>ICU admissions by Ethnicity<jupyter_code>WeeklyAdmitsICUEth = pd.read_excel(xls, 'Supplem 3. SARIWatch-ICUeth',
                   header=7,
                   usecols="C, D, E, F, G")
Ethnames = [(i,'Ethnicity: '+i) for i in WeeklyAdmitsICUEth.iloc[:, 0:].columns.values]
WeeklyAdmitsICUEth.rename(columns = dict(Ethnames), inplace=True)

WeeklyAdmitsICUEth = WeeklyAdmitsICUEth.reset_index()
WeeklyAdmitsICUEth2 = (pd.melt(WeeklyAdmitsICUEth, id_vars=['index'], value_name='Admissionsper100k'))
WeeklyAdmitsICUEth2[['tmp','Ethnicity']] = WeeklyAdmitsICUEth2.variable.str.split(':', expand=True)
WeeklyAdmitsICUEth2 = WeeklyAdmitsICUEth2.drop(['variable', 'tmp'],axis=1).sort_values(['index','Ethnicity'])
WeeklyAdmitsICUEth2['Admissionsper100k'] = WeeklyAdmitsICUEth2.Admissionsper100k.astype(str)
WeeklyAdmitsICUEth2 = WeeklyAdmitsICUEth2[['index','Admissionsper100k','Ethnicity']]

WeeklyAdmitsICUEth2['DateStart'] = date
WeeklyAdmitsICUEth2['ReverseIndex'] = 52 - WeeklyAdmitsICUEth2['index']
WeeklyAdmitsICUEth2['Factor'] = (WeeklyAdmitsICUEth2.ReverseIndex)*7
WeeklyAdmitsICUEth2["Date"] = pd.to_datetime(WeeklyAdmitsICUEth2["DateStart"]) - pd.to_timedelta(WeeklyAdmitsICUEth2['Factor'], unit='D')



WeeklyAdmitsICUEth2.drop(['DateStart', 'ReverseIndex','Factor','index'], axis=1, inplace=True)<jupyter_output><empty_output><jupyter_text>Deaths<jupyter_code>DailyDeaths = pd.read_excel(xls, 'Figure 57. Daily excess deaths',
                   header=7,
                   usecols="B,C, D,E")<jupyter_output><empty_output><jupyter_text>Write to excel<jupyter_code>writer = pd.ExcelWriter("Downloads/GOVCleanedData_Published_" + str(date) + ".xlsx", engine='xlsxwriter')

# Write each dataframe to a different worksheet.
df.to_excel(writer, sheet_name='Date')
Cleaned_weekly_Pos2.to_excel(writer, sheet_name='Positivity')
Cleaned_weekly_1.to_excel(writer, sheet_name='Age')
Cleaned_weekly_Sex2.to_excel(writer, sheet_name='Sex')
Cleaned_weekly_Region2.to_excel(writer, sheet_name='Region')
Cleaned_weekly_UTLA2.to_excel(writer, sheet_name='LA')
WeeklyNICAdmitsEth2.to_excel(writer, sheet_name='Non-ICU admissions, ethnicity')
WeeklyAdmitsICUEth2.to_excel(writer, sheet_name='ICU admissions, ethnicity')
DailyDeaths.to_excel(writer, sheet_name='DeathsbyDay')


writer.save()<jupyter_output><empty_output><jupyter_text>Writing to Google Sheets to integrate into Tableau, below are credentials & identifying worksheet<jupyter_code>from df2gspread import df2gspread as d2g 
scope = ['https://spreadsheets.google.com/feeds']
credentials = ServiceAccountCredentials.from_json_keyfile_name('**Directory/OfJH/Credentials**', scope)
gc = gspread.authorize(credentials)
spreadsheet_key = '**JHGoogleSpreadSheet**' 
book = gc.open_by_key(spreadsheet_key)<jupyter_output><empty_output><jupyter_text>Sending each dataframe to a seperate sheet in GS<jupyter_code>wks_name = 'Sex'
pd_df_name = 'Cleaned_weekly_Sex2'
d2g.upload(pd_df_name, spreadsheet_key, wks_name, credentials=credentials, row_names=True)

wks_name = 'Age'
pd_df_name = 'Cleaned_weekly_1'
d2g.upload(pd_df_name, spreadsheet_key, wks_name, credentials=credentials, row_names=True)

wks_name = 'Positivity'
pd_df_name = 'Cleaned_weekly_Pos2'
d2g.upload(pd_df_name, spreadsheet_key, wks_name, credentials=credentials, row_names=True)

wks_name = 'Regional Positivity'
pd_df_name = 'Cleaned_weekly_Region2'
d2g.upload(pd_df_name, spreadsheet_key, wks_name, credentials=credentials, row_names=True)

wks_name = 'Regional Cases'
pd_df_name = 'Cleaned_weekly_Regionc2'
d2g.upload(pd_df_name, spreadsheet_key, wks_name, credentials=credentials, row_names=True)

wks_name = 'LA Cases'
pd_df_name = 'Cleaned_weekly_UTLA2'
d2g.upload(pd_df_name, spreadsheet_key, wks_name, credentials=credentials, row_names=True)

wks_name = 'ICU Admits by Ethnicity'
pd_df_name = 'WeeklyAdmitsICUEth2'
d2g.upload(pd_df_name, spreadsheet_key, wks_name, credentials=credentials, row_names=True)

wks_name = 'Non-ICU Admits by Ethnicity'
pd_df_name = 'WeeklyNICAdmitsEth2'
d2g.upload(pd_df_name, spreadsheet_key, wks_name, credentials=credentials, row_names=True)

wks_name = 'Deaths'
pd_df_name = 'DailyDeaths'
d2g.upload(pd_df_name, spreadsheet_key, wks_name, credentials=credentials, row_names=True)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1457557467574162, 'fraction_numerical': 0.021702838063439065, 'mean_word_length': 7.769707207207207, 'pattern_counts': {'":': 0, '<': 65, '<?xml version=': 0, '>': 66, 'https://': 3, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '16', 'dir': 'jupyter-structured-clean-dedup', 'id': '533046', 'license': 'MIT', 'n_tokens_mistral': 6211, 'n_tokens_neox': 5799, 'n_words': 760, 'path': 'GOV_Clean_Upload.ipynb', 'repo_name': 'JamesHumpos/GOV_Clean_Upload', 'size': '15574'}