test_16

37157fe7-a5fb-4810-a0f3-a868fb372067.py

# rename col, drop redundant rows, melt to column format
df_s_ed = df_s_ed.rename(columns={'Unnamed: 0': 'Week'}).dropna()
df_s_ed = df_s_ed[[x for x in df_s_ed.columns if 'Unnamed' not in x]]
df_s_ed = pd.melt(df_s_ed, id_vars='Week', value_vars=[x for x in df_s_ed.columns if x!='Week'], value_name='Deaths', var_name='Year')

# format data types
df_s_ed['Week'] = pd.to_numeric(df_s_ed['Week'].str[-2:])
df_s_ed['Year'] = pd.to_numeric(df_s_ed['Year'])
# remove number formatting from death numbers and '-' for zero/unknown
df_s_ed['Deaths'] = pd.to_numeric(df_s_ed['Deaths'].apply(lambda x: x.replace('-', '').replace(',', '').strip()))
# dropna for week 53s
df_s_ed = df_s_ed.dropna()