Created
February 22, 2021 11:10
-
-
Save mjam03/74ebcd53a92de1d68960106e2c46076e to your computer and use it in GitHub Desktop.
test_4
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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'] |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment