{"cells":[{"metadata":{"trusted":true},"cell_type":"code","source":"from IPython.core.display import HTML\nHTML(\"\"\"\n\n\n\"\"\")","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Creating KPIs for Equity/Inequity amongst cities"},{"metadata":{},"cell_type":"markdown","source":"# Socioeconomic KPIs\n\n| KPI Name | KPI Unit | KPI Interpretation | Public Data Source |\n|------------------------|-------------|-----------------------------------------------------------------|------------------------------------|\n| socio_economic | unitless | CDC's Social Vulnerability Index for socioeconomic indicators | CDC |\n| absolute_mobility | percentile | Absolute upward mobility for children compared to their parents | Chetty et al. (2014) |\n| job_density | jobs/sqmile | Job density | Chetty et al. (2014) / Census |\n| avg_job_growth | % | Average job growth | Chetty et al. (2014) / Census |\n| unemployment_trend | unitless | Change in unemployment over last ten years | County Health Rankings Trends Data |\n| children_poverty_trend | unitless | Change in child poverty over last ten years | County Health Rankings Trends Data |\n\n\n# Health KPIs\n\n| KPI Name | KPI Interpretation | Public Data Source |\n|-------------------------|------------------------------------------------------------------------|------------------------------------|\n| minority_language | CDC's Social Vulnerability Index for minorities | CDC |\n| food_insecurity | Food insecure percentage | Mind the Meal Gap |\n| child_food_insecurity | Food insecure percentage (children only) | Mind the Meal Gap |\n| premature_death_rate | Premature death rate | County Health Rankings Trends Data |\n| poor_health | % of people reporting to have poor health | County Health Rankings Trends Data |\n| healthy_food_access | Access to healthy food | County Health Rankings Trends Data |\n| percent_uninsured | Percentage uninsured | County Health Rankings Trends Data |\n| primary_care_physicians | Number of primary care physicians per x people | County Health Rankings Trends Data |\n| premature_death_trend | Premature death trend over last ten year | County Health Rankings Trends Data |\n| pcp_trend | Number of primary care physicians per x people over the last ten years | County Health Rankings Trends Data |\n\n\n\n# Housing KPIs\n\n| KPI Name | KPI Interpretation | Public Data Source |\n|------------------------------|------------------------------------------------|------------------------------------|\n| house_composition_disability | House composition / disability index | CDC |\n| house_type_transportation | Housing type and transportation inequity index | CDC |\n| segregation_black_white | Segregation black/white | County Health Rankings Trends Data |\n| segregation_nonwhite_white | Segregation non-white/white | County Health Rankings Trends Data |\n| housing_cost_burden | Burden in housing cost (% of income) | County Health Rankings Trends Data |"},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"import numpy as np\nimport matplotlib.pyplot as plt\nimport pandas as pd\nfrom scipy import stats\nfrom sklearn.preprocessing import RobustScaler, QuantileTransformer, StandardScaler, MinMaxScaler\nimport ast\nplt.style.use(\"/kaggle/input/mpl-style-file/harsh.mpl\")\nfrom subprocess import check_output\n%matplotlib inline","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true},"cell_type":"code","source":"path = \"../input/externald/Supplementary Data/\" \nequity_cities = pd.read_csv(\"../input/externald/Supplementary Data/cities_updated_geo_us_2020.csv\") # takes the output from reduce_and_add_cities\n\ncounties = equity_cities['counties'] # counties\ncounties_weights = (equity_cities['counties_weights']) # county \"weights\" for each city\nstates = equity_cities['state'] # state that this city belongs to\nflag_multi_counties = equity_cities['flag_multiple_counties'] # flag indicating whether this city\nmatch_fips = equity_cities['FIPS']\nmatch_fips = [ast.literal_eval(match_fips[i]) if flag_multi_counties[i] else int(match_fips[i]) for i in range(len(match_fips))]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def standardize_rank(arr, direction=1):\n rank = (stats.mstats.rankdata(np.ma.masked_invalid(arr)))\n rank[rank == 0] = np.nan\n if(direction == 1):\n return rank/(np.nanmax(rank)) \n else:\n return 1-(rank/(np.nanmax(rank)))\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df = pd.read_csv(path + \"CDC Social Vulnerability Index 2018/SVI2018_US_COUNTY.csv\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cdc_svi_metrics = []\nfor i in range(len(counties)):\n if(not flag_multi_counties[i]):\n fips = int(match_fips[i])\n cdf = df[df[\"FIPS\"] == fips]\n cdc_svi_metrics.append(np.array([float(cdf['RPL_THEME1']), float(cdf['RPL_THEME2']), \n float(cdf['RPL_THEME3']), float(cdf['RPL_THEME4'])]))\n else:\n fips = (match_fips[i])\n temp = []\n for j in range(len(fips)):\n cdf = df[df[\"FIPS\"] == fips[j]]\n temp.append(np.array([float(cdf['RPL_THEME1']), float(cdf['RPL_THEME2']), \n float(cdf['RPL_THEME3']), float(cdf['RPL_THEME4'])]))\n all_county_metrics = np.array(temp)\n weighted_metrics = np.matmul(np.array(ast.literal_eval(counties_weights[i])), all_county_metrics)\n cdc_svi_metrics.append(weighted_metrics)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cdc_svi_metrics = np.array(cdc_svi_metrics)\ndirection_cdc_svi_metrics = 1.0\n\nsocio_economic_score = standardize_rank(cdc_svi_metrics[:,0], direction=direction_cdc_svi_metrics)\nhouse_composition_disability_score = standardize_rank(cdc_svi_metrics[:,1], direction=direction_cdc_svi_metrics)\nminority_language_score = standardize_rank(cdc_svi_metrics[:,2], direction=direction_cdc_svi_metrics)\nhouse_type_transportation_score = standardize_rank(cdc_svi_metrics[:,3], direction=direction_cdc_svi_metrics)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"dff = pd.read_csv(path + \"mobility_chetty.csv\")\n\nrm = []\nfor i in range(len(match_fips)):\n if(not flag_multi_counties[i]):\n fips = int(match_fips[i])\n cdf = dff[dff[\"County FIPS Code\"] == fips]\n rm.append(np.float(cdf[\"Rank-Rank Slope\"]))\n else:\n fips = match_fips[i]\n temp = []\n for j in range(len(fips)):\n cdf = dff[dff[\"County FIPS Code\"] == fips[j]]\n temp.append(np.float(cdf[\"Rank-Rank Slope\"]))\n ar = np.array(temp)\n weighted_avg = np.average(ar, weights=np.array(ast.literal_eval(counties_weights[i])))\n rm.append(weighted_avg)\n \nam = []\nfor i in range(len(match_fips)):\n if(not flag_multi_counties[i]):\n fips = int(match_fips[i])\n cdf = dff[dff[\"County FIPS Code\"] == fips]\n am.append(np.float(cdf[\"Absolute Upward Mobility\"]))\n else:\n fips = (match_fips[i])\n temp = []\n for j in range(len(fips)):\n cdf = dff[dff[\"County FIPS Code\"] == fips[j]]\n temp.append(np.float(cdf[\"Absolute Upward Mobility\"]))\n ar = np.array(temp)\n weighted_avg = np.average(ar, weights=np.array(ast.literal_eval(counties_weights[i])))\n am.append(weighted_avg)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"relative_mobility = np.array(rm)\ndirection_relative_mobility = 1.0\n\nabsolute_mobility = np.array(am)\ndirection_absolute_mobility = -1.0\n\nrelative_mobility_score = standardize_rank(relative_mobility, direction=direction_relative_mobility)\nabsolute_mobility_score = standardize_rank(absolute_mobility, direction=direction_absolute_mobility)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"dfff = pd.read_csv(path + \"cty_covariates.csv\")\nct = np.char.zfill(np.array(dfff['county']).astype(str), 3)\nst = np.char.zfill(np.array(dfff['state']).astype(str), 2)\ndfff['fips'] = np.core.defchararray.add(st, ct).astype(int)\n\navg_job_growth = []\nfor i in range(len(match_fips)):\n if(not flag_multi_counties[i]):\n fips = int(match_fips[i])\n cdf = dfff[dfff[\"fips\"] == fips]\n avg_job_growth.append(np.float(cdf[\"ann_avg_job_growth_2004_2013\"]))\n else:\n fips = (match_fips[i])\n temp = []\n for j in range(len(fips)):\n cdf = dfff[dfff[\"fips\"] == fips[j]]\n temp.append(np.float(cdf[\"ann_avg_job_growth_2004_2013\"]))\n ar = np.array(temp)\n weighted_avg = np.average(ar, weights=np.array(ast.literal_eval(counties_weights[i])))\n avg_job_growth.append(weighted_avg)\n \njob_dens = []\nfor i in range(len(match_fips)):\n if(not flag_multi_counties[i]):\n fips = int(match_fips[i])\n cdf = dfff[dfff[\"fips\"] == fips]\n job_dens.append(np.float(cdf[\"job_density_2013\"]))\n else:\n fips = (match_fips[i])\n temp = []\n for j in range(len(fips)):\n cdf = dfff[dfff[\"fips\"] == fips[j]]\n temp.append(np.float(cdf[\"job_density_2013\"]))\n ar = np.array(temp)\n weighted_avg = np.average(ar, weights=np.array(ast.literal_eval(counties_weights[i])))\n job_dens.append(weighted_avg)\n ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"avg_job_growth = np.array(avg_job_growth)\ndirection_avg_job_growth = -1.0\n\njob_dens = np.array(job_dens)\ndirection_job_dens = -1.0\n\navg_job_growth_score = standardize_rank(avg_job_growth, direction=direction_avg_job_growth)\njob_dens_score = standardize_rank(job_dens, direction=direction_job_dens)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"tf = pd.read_excel(path + \"Feeding America/Map the Meal Gap data/MMG2020_2018Data_ToShare.xlsx\", sheet_name=\"2018 County\", skiprows=1)\n\nfood_insec = []\nfor i in range(len(match_fips)):\n if(not flag_multi_counties[i]):\n fips = int(match_fips[i])\n cdf = tf[tf[\"FIPS\"] == fips]\n food_insec.append(np.float(cdf[\"2018 Food Insecurity Rate\"]))\n else:\n fips = (match_fips[i])\n temp = []\n for j in range(len(fips)):\n cdf = tf[tf[\"FIPS\"] == fips[j]]\n temp.append(np.float(cdf[\"2018 Food Insecurity Rate\"]))\n ar = np.array(temp)\n weighted_avg = np.average(ar, weights=np.array(ast.literal_eval(counties_weights[i])))\n food_insec.append(weighted_avg)\n \nchild_food_insec = []\nfor i in range(len(match_fips)):\n if(not flag_multi_counties[i]):\n fips = int(match_fips[i])\n cdf = tf[tf[\"FIPS\"] == fips]\n child_food_insec.append(np.float(cdf[\"2018 Child food insecurity rate\"]))\n else:\n fips = (match_fips[i])\n temp = []\n for j in range(len(fips)):\n cdf = tf[tf[\"FIPS\"] == fips[j]]\n temp.append(np.float(cdf[\"2018 Child food insecurity rate\"]))\n ar = np.array(temp)\n weighted_avg = np.average(ar, weights=np.array(ast.literal_eval(counties_weights[i])))\n child_food_insec.append(weighted_avg)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"food_insec = np.array(food_insec)\ndirection_food_insec = 1.0\n\nchild_food_insec = np.array(child_food_insec)\ndirection_child_food_insec = 1.0\n\nfood_insec_score = standardize_rank(food_insec, direction=direction_food_insec)\nchild_food_insec_score = standardize_rank(child_food_insec, direction=direction_child_food_insec)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"ch = pd.read_csv(path + \"CountyHealth_analytic.csv\")\n\ndef extract_from_county_health_survey(field):\n metric = []\n for i in range(len(match_fips)):\n if(not flag_multi_counties[i]):\n fips = int(match_fips[i])\n cdf = ch[ch[\"FIPS\"] == fips]\n metric.append(np.float(cdf[field]))\n else:\n fips = (match_fips[i])\n temp = []\n for j in range(len(fips)):\n cdf = ch[ch[\"FIPS\"] == fips[j]]\n temp.append(np.float(cdf[field]))\n ar = np.array(temp)\n weighted_avg = np.average(ar, weights=np.array(ast.literal_eval(counties_weights[i])))\n metric.append(weighted_avg)\n return np.array(metric)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"premature_death_rate = extract_from_county_health_survey(\"Premature death raw value\")\ndirection_premature_death_rate = 1.0\npremature_death_rate_score = standardize_rank(premature_death_rate, direction=direction_premature_death_rate)\n\nhealth_level = extract_from_county_health_survey(\"Poor or fair health raw value\")\ndirection_health_level = 1.0\nhealth_level_score = standardize_rank(health_level, direction=direction_health_level)\n\nhealthy_food_access = extract_from_county_health_survey(\"Food environment index raw value\")\ndirection_healthy_food_access = -1.0\nhealthy_food_access_score = standardize_rank(healthy_food_access, direction=direction_healthy_food_access)\n\nexercise_access = extract_from_county_health_survey(\"Access to exercise opportunities raw value\")\ndirection_exercise_access = -1.0\nexercise_access_score = standardize_rank(exercise_access, direction=direction_exercise_access)\n\npercent_uninsured = extract_from_county_health_survey(\"Uninsured raw value\")\ndirection_percent_uninsured = 1.0\npercent_uninsured_score = standardize_rank(percent_uninsured, direction=direction_percent_uninsured)\n\nprimary_care_physicians = extract_from_county_health_survey(\"Primary care physicians raw value\")\ndirection_primary_care_physicians = -1.0\nprimary_care_physicians_score = standardize_rank(primary_care_physicians, direction=direction_primary_care_physicians)\n\nsegregation_bl = extract_from_county_health_survey(\"Residential segregation - Black/White raw value\")\ndirection_segregation_bl = 1.0\nsegregation_bl_score = standardize_rank(segregation_bl, direction=direction_segregation_bl)\n\nsegregation_nw = extract_from_county_health_survey(\"Residential segregation - non-White/White raw value\")\ndirection_segregation_nw = 1.0\nsegregation_nw_score = standardize_rank(segregation_nw, direction=direction_segregation_nw)\n\nhousing_cost_burden = extract_from_county_health_survey(\"Severe housing cost burden raw value\")\ndirection_housing_cost_burden = 1.0\nhousing_cost_burden_score = standardize_rank(housing_cost_burden, direction=direction_housing_cost_burden)\n\nmental_distress = extract_from_county_health_survey(\"Frequent mental distress raw value\")\ndirection_mental_distress = 1.0\nmental_distress_score = standardize_rank(mental_distress, direction=direction_mental_distress)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"trends = pd.read_csv(path + \"CHR_trends_csv_2020.csv\", encoding='latin-1')\nct = np.char.zfill(np.array(trends['countycode']).astype(str), 3)\nst = np.char.zfill(np.array(trends['statecode']).astype(str), 2)\ntrends['fips'] = np.core.defchararray.add(st, ct).astype(int)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def extract_trends_from_county_health_survey(field, num_measures_for_mean=3):\n metric = []\n for i in range(len(match_fips)):\n if(not flag_multi_counties[i]):\n fips = int(match_fips[i])\n cdf = trends[(trends['measurename'] == field) & (trends['fips'] == fips)]\n \n rv = list(cdf['rawvalue'])\n if(type(rv[0]) == str):\n rv = [float(x.replace(\",\", \"\")) for x in rv]\n \n rvd = (np.mean(rv[-num_measures_for_mean:]) - np.mean(rv[0:num_measures_for_mean]))/(np.mean(rv[0:num_measures_for_mean]))\n metric.append(np.float(rvd))\n else:\n fips = (match_fips[i])\n temp = []\n for j in range(len(fips)):\n cdf = trends[(trends['measurename'] == field) & (trends['fips'] == fips[j])]\n rv = list(cdf['rawvalue'])\n \n if(type(rv[0]) == str):\n rv = [float(x.replace(\",\", \"\")) for x in rv]\n \n rvd = (np.mean(rv[-num_measures_for_mean:]) - np.mean(rv[0:num_measures_for_mean]))/(np.mean(rv[0:num_measures_for_mean]))\n temp.append(np.float(rvd))\n ar = np.array(temp)\n weighted_avg = np.average(ar, weights=np.array(ast.literal_eval(counties_weights[i])))\n metric.append(weighted_avg)\n return np.array(metric)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"premature_death_trend = extract_trends_from_county_health_survey(\"Premature death\")\ndirection_premature_death_trend = 1.0\npremature_death_trend_score = standardize_rank(premature_death_trend, direction=direction_premature_death_trend)\n\nunemployment_trend = extract_trends_from_county_health_survey(\"Unemployment rate\")\ndirection_unemployment_trend = 1.0\nunemployment_trend_score = standardize_rank(unemployment_trend, direction=direction_unemployment_trend)\n\npcp_trend = extract_trends_from_county_health_survey(\"Primary care physicians\")\ndirection_pcp_trend = -1.0\npcp_trend_score = standardize_rank(pcp_trend, direction=direction_pcp_trend)\n\nuninsured_trend = extract_trends_from_county_health_survey(\"Uninsured\")\ndirection_uninsured_trend = 1.0\nuninsured_trend_score = standardize_rank(uninsured_trend, direction=direction_uninsured_trend)\n\nchildren_poverty_trend = extract_trends_from_county_health_survey(\"Children in poverty\")\ndirection_children_poverty_trend = 1.0\nchildren_poverty_trend_score = standardize_rank(children_poverty_trend, direction=direction_children_poverty_trend)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"nri = pd.read_csv(path + \"NRI_Table_Counties.csv\")\n\ndef nri_select(field):\n res = []\n for i in range(len(match_fips)):\n if(not flag_multi_counties[i]):\n fips = int(match_fips[i])\n cdf = nri[nri[\"STCOFIPS\"] == fips]\n try:\n res.append(float(cdf[field]))\n except:\n res.append(np.nan)\n else:\n fips = match_fips[i]\n temp = []\n for j in range(len(fips)):\n cdf = nri[nri[\"STCOFIPS\"] == fips[j]]\n try:\n temp.append(float(cdf[field]))\n except:\n temp.append(np.nan)\n ar = np.array(temp)\n weighted_avg = np.average(ar, weights=np.array(ast.literal_eval(counties_weights[i])))\n res.append(weighted_avg)\n return np.array(res)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"sovi_risk_index = nri_select(\"SOVI_SCORE\")\ndirection_sovi_risk_index = 1.0\nsovi_risk_index_score = standardize_rank(sovi_risk_index, direction=direction_sovi_risk_index)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"equity_cities_raw_kpis = equity_cities.copy()\nequity_cities_ranked_kpis = equity_cities.copy()\nequity_cities_aggregate_kpis = equity_cities.copy()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#### ECONOMIC \n\nequity_cities_raw_kpis[\"socio_economic\"] = cdc_svi_metrics[:,0]\nequity_cities_raw_kpis[\"absolute_mobility\"] = absolute_mobility\nequity_cities_raw_kpis[\"job_density\"] = job_dens\nequity_cities_raw_kpis[\"avg_job_growth\"] = avg_job_growth\nequity_cities_raw_kpis[\"unemployment_trend\"] = unemployment_trend\nequity_cities_raw_kpis[\"children_poverty_trend\"] = children_poverty_trend\n\n### HEALTH\n\nequity_cities_raw_kpis[\"minority_language\"] = cdc_svi_metrics[:,2]\nequity_cities_raw_kpis[\"food_insecurity\"] = food_insec\nequity_cities_raw_kpis[\"child_food_insecurity\"] = child_food_insec\nequity_cities_raw_kpis[\"premature_death_rate\"] = premature_death_rate\nequity_cities_raw_kpis[\"poor_health\"] = health_level\nequity_cities_raw_kpis[\"healthy_food_access\"] = healthy_food_access\nequity_cities_raw_kpis[\"percent_uninsured\"] = percent_uninsured\nequity_cities_raw_kpis[\"exercise_access\"] = exercise_access\nequity_cities_raw_kpis[\"primary_care_physicians\"] = primary_care_physicians\nequity_cities_raw_kpis[\"mental_distress\"] = mental_distress\nequity_cities_raw_kpis[\"premature_death_trend\"] = premature_death_trend\nequity_cities_raw_kpis[\"uninsured_trend\"] = uninsured_trend\nequity_cities_raw_kpis[\"pcp_trend\"] = pcp_trend\n\n### HOUSING \n\nequity_cities_raw_kpis[\"house_composition_disability\"] = cdc_svi_metrics[:,1]\nequity_cities_raw_kpis[\"house_type_transportation\"] = cdc_svi_metrics[:,3]\nequity_cities_raw_kpis[\"segregation_black_white\"] = segregation_bl\nequity_cities_raw_kpis[\"segregation_nonwhite_white\"] = segregation_nw\nequity_cities_raw_kpis[\"housing_cost_burden\"] = housing_cost_burden\n\n### SUMMARY \n\nequity_cities_raw_kpis[\"sovi_risk_index\"] = sovi_risk_index\n\nequity_cities_raw_kpis.to_csv(\"equity_cities_us_kpis_raw.csv\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#### ECONOMIC \n\nequity_cities_ranked_kpis[\"socio_economic_score\"] = socio_economic_score\nequity_cities_ranked_kpis[\"absolute_mobility_score\"] = absolute_mobility_score\nequity_cities_ranked_kpis[\"job_density_score\"] = job_dens_score\nequity_cities_ranked_kpis[\"avg_job_growth_score\"] = avg_job_growth_score\nequity_cities_ranked_kpis[\"unemployment_trend_score\"] = unemployment_trend_score\nequity_cities_ranked_kpis[\"children_poverty_trend_score\"] = children_poverty_trend_score\n\n### HEALTH\n\nequity_cities_ranked_kpis[\"minority_language_score\"] = minority_language_score\nequity_cities_ranked_kpis[\"food_insecurity_score\"] = food_insec_score\nequity_cities_ranked_kpis[\"child_food_insecurity_score\"] = child_food_insec_score\nequity_cities_ranked_kpis[\"premature_death_rate_score\"] = premature_death_rate_score\nequity_cities_ranked_kpis[\"poor_health_score\"] = health_level_score\nequity_cities_ranked_kpis[\"healthy_food_access_score\"] = healthy_food_access_score\nequity_cities_ranked_kpis[\"percent_uninsured_score\"] = percent_uninsured_score\nequity_cities_ranked_kpis[\"exercise_access_score\"] = exercise_access_score\nequity_cities_ranked_kpis[\"primary_care_physicians_score\"] = primary_care_physicians_score\nequity_cities_ranked_kpis[\"mental_distress_score\"] = mental_distress_score\nequity_cities_ranked_kpis[\"premature_death_trend_score\"] = premature_death_trend_score\nequity_cities_ranked_kpis[\"uninsured_trend_score\"] = uninsured_trend_score\nequity_cities_ranked_kpis[\"pcp_trend_score\"] = pcp_trend_score\n\n### HOUSING \n\nequity_cities_ranked_kpis[\"house_composition_disability_score\"] = house_composition_disability_score\nequity_cities_ranked_kpis[\"house_type_transportation_score\"] = house_type_transportation_score\nequity_cities_ranked_kpis[\"segregation_black_white_score\"] = segregation_bl_score\nequity_cities_ranked_kpis[\"segregation_nonwhite_white_score\"] = segregation_nw_score\nequity_cities_ranked_kpis[\"housing_cost_burden_score\"] = housing_cost_burden_score\n\n### SUMMARY \n\nequity_cities_ranked_kpis[\"sovi_summary_score\"] = sovi_risk_index_score\nequity_cities_ranked_kpis.to_csv(\"equity_cities_us_kpis_ranked.csv\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def aggr_and_rescale(arr):\n avg_arr = np.nanmean(arr, axis=1)\n rescaled_avg_arr = standardize_rank(avg_arr)\n return rescaled_avg_arr","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"all_socio_economic_kpis = np.c_[socio_economic_score, absolute_mobility_score, job_dens_score, avg_job_growth_score,\n unemployment_trend_score, children_poverty_trend_score]\n\naggr_socioeconomic_kpis = aggr_and_rescale(all_socio_economic_kpis)\n\n\nall_health_kpis = np.c_[minority_language_score, food_insec_score, child_food_insec_score, premature_death_rate_score,\n health_level_score, healthy_food_access_score, percent_uninsured_score, exercise_access_score, primary_care_physicians_score,\n mental_distress_score, premature_death_trend_score, uninsured_trend_score, pcp_trend_score]\n\naggr_health_kpis = aggr_and_rescale(all_health_kpis)\n\n\nall_housing_kpis = np.c_[house_composition_disability_score, house_type_transportation_score, segregation_bl_score,\n segregation_nw_score, housing_cost_burden_score]\n\naggr_housing_kpis = aggr_and_rescale(all_housing_kpis)\n\naggr_all_inequity = aggr_and_rescale(np.c_[aggr_socioeconomic_kpis, aggr_health_kpis, aggr_housing_kpis, sovi_risk_index_score])\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"equity_cities_aggregate_kpis[\"aggr_socio_economic_kpis\"] = aggr_socioeconomic_kpis\nequity_cities_aggregate_kpis[\"aggr_health_kpis\"] = aggr_health_kpis\nequity_cities_aggregate_kpis[\"aggr_housing_kpis\"] = aggr_housing_kpis\nequity_cities_aggregate_kpis[\"aggr_all_inequity\"] = aggr_all_inequity\n\nequity_cities_aggregate_kpis.to_csv(\"equity_cities_us_aggr_kpis.csv\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat":4,"nbformat_minor":4}