mortality data parsing

make_query.py

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)

output.sql

  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