test_4

ae66ce2d-fd85-4e8e-97a5-e28e27ff6673.py

# form query string for 2021 then request
file_name = [x for x in ENG_WALES_XLS if str(2021) in x][0]
file_name = ONS_ROOT + file_name
r = requests.get(file_name)
xl_obj = pd.ExcelFile(r.content)

# form df of deaths data
df_ew_21 = pd.read_excel(xl_obj, sheet_name='Weekly figures 2021', header=4)
df_ew_21 = pd.concat([df_ew_21.iloc[0], df_ew_21.iloc[3]], axis=1)
df_ew_21 = df_ew_21.reset_index()
df_ew_21.columns = df_ew_21.iloc[0]
df_ew_21 = df_ew_21[df_ew_21.index != 0]
df_ew_21 = df_ew_21.dropna()
df_ew_21 = df_ew_21[df_ew_21['Total deaths, all ages (2021)'] != ':']
df_ew_21['Year'] = 2021

# form df of respiratory deaths data
df_ew_21_r = pd.read_excel(xl_obj, sheet_name='Weekly figures by cause', header=5)
df_ew_21_r = pd.concat([df_ew_21_r.iloc[0], df_ew_21_r.iloc[5]], axis=1)
df_ew_21_r = df_ew_21_r.reset_index()
df_ew_21_r.columns = df_ew_21_r.iloc[0]
df_ew_21_r = df_ew_21_r[df_ew_21_r.index != 0]
df_ew_21_r = df_ew_21_r.dropna()
df_ew_21_r['Year'] = 2021

# merge them together to have resp and all deaths in same df
df_ew_21 = pd.merge(left=df_ew_21, right=df_ew_21_r, how='outer', on=['Year', 'Week number', 'Week ended'])
df_ew_21.columns = ['Week', 'week_end', 'Deaths', 'Year', 'RespiratoryDeaths']