Skip to content

Instantly share code, notes, and snippets.

@andy-esch
Last active December 14, 2016 20:10
Show Gist options
  • Save andy-esch/e17cb67c41dd767811825123060fbc18 to your computer and use it in GitHub Desktop.
Save andy-esch/e17cb67c41dd767811825123060fbc18 to your computer and use it in GitHub Desktop.
mortality data parsing
spans = ['1', '100+', '10-14', '1-4', '15-19', '20-24', '25-29', '30-34',
'35-39', '40-44', '45-49', '50-54', '55-59', '5-9', '60-64',
'65-69', '70-74', '75-79', '80-84', '85-89', '90-94', '95-99']
genders = ['Male', 'Female']
# max(deaths) FILTER (WHERE gender = 'Female' AND five_year_age_groups_code = '35-39') As women_35_39_deaths
def normalize(colname):
return colname.replace('-', '_').replace('+', '_')
def make_colname(span, gender):
new_colname = '{gender}_{span}_death'.format(
gender=gender,
span=span)
return normalize(new_colname)
col_select = 'max(deaths) FILTER (WHERE gender = \'{gender}\' AND five_year_age_groups_code = \'{span}\') As {normed_colname}'
all_selects = []
for g in genders:
if g == 'Male':
g_type = 'men'
else:
g_type = 'women'
for s in spans:
temp = col_select.format(
gender=g,
span=s,
normed_colname=make_colname(s, g_type))
print(temp)
all_selects.append(temp)
full_query = '''
SELECT county_code As geoid,
max(population) As population,
sum(deaths) FILTER (WHERE gender = 'Female') As women_all_death,
sum(deaths) FILTER (WHERE gender = 'Male') As men_all_death,
{all_selects}
FROM deaths_36_2011
GROUP BY county_code
'''.format(all_selects=',\n '.join(all_selects))
print(full_query)
SELECT county_code As geoid,
max(population) As population,
sum(deaths) FILTER (WHERE gender = 'Female') As women_all_death,
sum(deaths) FILTER (WHERE gender = 'Male') As men_all_death,
max(deaths) FILTER (WHERE gender = 'Male' AND five_year_age_groups_code = '1') As men_1_death,
max(deaths) FILTER (WHERE gender = 'Male' AND five_year_age_groups_code = '100+') As men_100__death,
max(deaths) FILTER (WHERE gender = 'Male' AND five_year_age_groups_code = '10-14') As men_10_14_death,
max(deaths) FILTER (WHERE gender = 'Male' AND five_year_age_groups_code = '1-4') As men_1_4_death,
max(deaths) FILTER (WHERE gender = 'Male' AND five_year_age_groups_code = '15-19') As men_15_19_death,
max(deaths) FILTER (WHERE gender = 'Male' AND five_year_age_groups_code = '20-24') As men_20_24_death,
max(deaths) FILTER (WHERE gender = 'Male' AND five_year_age_groups_code = '25-29') As men_25_29_death,
max(deaths) FILTER (WHERE gender = 'Male' AND five_year_age_groups_code = '30-34') As men_30_34_death,
max(deaths) FILTER (WHERE gender = 'Male' AND five_year_age_groups_code = '35-39') As men_35_39_death,
max(deaths) FILTER (WHERE gender = 'Male' AND five_year_age_groups_code = '40-44') As men_40_44_death,
max(deaths) FILTER (WHERE gender = 'Male' AND five_year_age_groups_code = '45-49') As men_45_49_death,
max(deaths) FILTER (WHERE gender = 'Male' AND five_year_age_groups_code = '50-54') As men_50_54_death,
max(deaths) FILTER (WHERE gender = 'Male' AND five_year_age_groups_code = '55-59') As men_55_59_death,
max(deaths) FILTER (WHERE gender = 'Male' AND five_year_age_groups_code = '5-9') As men_5_9_death,
max(deaths) FILTER (WHERE gender = 'Male' AND five_year_age_groups_code = '60-64') As men_60_64_death,
max(deaths) FILTER (WHERE gender = 'Male' AND five_year_age_groups_code = '65-69') As men_65_69_death,
max(deaths) FILTER (WHERE gender = 'Male' AND five_year_age_groups_code = '70-74') As men_70_74_death,
max(deaths) FILTER (WHERE gender = 'Male' AND five_year_age_groups_code = '75-79') As men_75_79_death,
max(deaths) FILTER (WHERE gender = 'Male' AND five_year_age_groups_code = '80-84') As men_80_84_death,
max(deaths) FILTER (WHERE gender = 'Male' AND five_year_age_groups_code = '85-89') As men_85_89_death,
max(deaths) FILTER (WHERE gender = 'Male' AND five_year_age_groups_code = '90-94') As men_90_94_death,
max(deaths) FILTER (WHERE gender = 'Male' AND five_year_age_groups_code = '95-99') As men_95_99_death,
max(deaths) FILTER (WHERE gender = 'Female' AND five_year_age_groups_code = '1') As women_1_death,
max(deaths) FILTER (WHERE gender = 'Female' AND five_year_age_groups_code = '100+') As women_100__death,
max(deaths) FILTER (WHERE gender = 'Female' AND five_year_age_groups_code = '10-14') As women_10_14_death,
max(deaths) FILTER (WHERE gender = 'Female' AND five_year_age_groups_code = '1-4') As women_1_4_death,
max(deaths) FILTER (WHERE gender = 'Female' AND five_year_age_groups_code = '15-19') As women_15_19_death,
max(deaths) FILTER (WHERE gender = 'Female' AND five_year_age_groups_code = '20-24') As women_20_24_death,
max(deaths) FILTER (WHERE gender = 'Female' AND five_year_age_groups_code = '25-29') As women_25_29_death,
max(deaths) FILTER (WHERE gender = 'Female' AND five_year_age_groups_code = '30-34') As women_30_34_death,
max(deaths) FILTER (WHERE gender = 'Female' AND five_year_age_groups_code = '35-39') As women_35_39_death,
max(deaths) FILTER (WHERE gender = 'Female' AND five_year_age_groups_code = '40-44') As women_40_44_death,
max(deaths) FILTER (WHERE gender = 'Female' AND five_year_age_groups_code = '45-49') As women_45_49_death,
max(deaths) FILTER (WHERE gender = 'Female' AND five_year_age_groups_code = '50-54') As women_50_54_death,
max(deaths) FILTER (WHERE gender = 'Female' AND five_year_age_groups_code = '55-59') As women_55_59_death,
max(deaths) FILTER (WHERE gender = 'Female' AND five_year_age_groups_code = '5-9') As women_5_9_death,
max(deaths) FILTER (WHERE gender = 'Female' AND five_year_age_groups_code = '60-64') As women_60_64_death,
max(deaths) FILTER (WHERE gender = 'Female' AND five_year_age_groups_code = '65-69') As women_65_69_death,
max(deaths) FILTER (WHERE gender = 'Female' AND five_year_age_groups_code = '70-74') As women_70_74_death,
max(deaths) FILTER (WHERE gender = 'Female' AND five_year_age_groups_code = '75-79') As women_75_79_death,
max(deaths) FILTER (WHERE gender = 'Female' AND five_year_age_groups_code = '80-84') As women_80_84_death,
max(deaths) FILTER (WHERE gender = 'Female' AND five_year_age_groups_code = '85-89') As women_85_89_death,
max(deaths) FILTER (WHERE gender = 'Female' AND five_year_age_groups_code = '90-94') As women_90_94_death,
max(deaths) FILTER (WHERE gender = 'Female' AND five_year_age_groups_code = '95-99') As women_95_99_death
FROM deaths_36_2011
GROUP BY county_code
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment