{"cells":[{"metadata":{"trusted":true},"cell_type":"code","source":"from IPython.core.display import HTML\nHTML(\"\"\"\n\n\n\"\"\")","execution_count":null,"outputs":[]},{"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\nimport rasterio\nfrom matplotlib import colors\nfrom scipy import stats\nfrom tqdm import *\nfrom pyproj import Transformer\nimport ast\nimport glob","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"| KPI Name | KPI Interpretation | Public Data Source |\n|---------------------------|----------------------------------------------------------------------|--------------------------|\n| score_heat_wave | Calculated score based on survey response | CDP Survey |\n| score_hot_days | Calculated score based on survey response | CDP Survey |\n| all_locs_construction | Ratio of construction workers in city compared to rest of the US | OECS |\n| all_locs_transportation | Ratio of transporation workers in city compared to rest of the US | OECS |\n| all_locs_protective | Ratio of protective workers in city compared to rest of the US | OECS |\n| all_locs_building_grounds | Ratio of building grounds workers in city compared to rest of the US | OECS |\n| all_locs_farming | Ratio of farming/ag workers in city compared to rest of the US | OECS |\n| heat_maximum_temp | Heat wave days based on maximum temperature | CDC Wonder |\n| heat_maximum_stress | Heat wave days based on net daily heat stress | CDC Wonder |\n| heat_maximum_index | Heat wave days based on maximum heat index | CDC Wonder |\n| heat_maximum_temp_std | Heat wave days variability based on maximum temperature | CDC Wonder |\n| heat_maximum_stress_std | Heat wave days variability based on net daily heat stress | CDC Wonder |\n| heat_maximum_index_std | Heat wave days variability based on maximum heat index | CDC Wonder |\n| heat_wave_eals | Heat wave expected annual loss | FEMA National Risk Index |"},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true},"cell_type":"code","source":"path = \"../input/externald/Supplementary Data/\" \nvulnerability = pd.read_csv(path + \"cities_updated_geo_us_2020.csv\")\nacct_number = vulnerability[\"Account Number\"]\ncounties = (vulnerability['counties'])\ncounties_weights = (vulnerability['counties_weights'])\nstates = (vulnerability['state'])\nflag_multi_counties = vulnerability['flag_multiple_counties']\nmatch_fips = vulnerability['FIPS']\nMSA = (vulnerability['MSA'])\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":"cities_2020 = pd.read_csv(\"/kaggle/input/cdp-unlocking-climate-solutions/Cities/Cities Responses/2020_Full_Cities_Dataset.csv\")\n\ncities_disc_2020 = pd.read_csv(\"/kaggle/input/cdp-unlocking-climate-solutions/Cities/Cities Disclosing/2020_Cities_Disclosing_to_CDP.csv\")\n\ncities_2020_merged = pd.merge(cities_2020, cities_disc_2020, on=\"Account Number\", how=\"outer\", copy=False)\nunique_20 = np.unique(cities_disc_2020[cities_disc_2020[\"Country\"] == \"United States of America\"][\"Account Number\"])\ncities_2020_merged_sub = cities_2020_merged[np.in1d(cities_2020_merged[\"Account Number\"], unique_20)]\n\ndef fetch_answer_individual(account, question_number, org_type=\"city\", year=2018, corp_res_type=None, column_number=None, row_number=None):\n if(org_type==\"city\"):\n df = cities_2020_merged_sub\n subset = df[df['Question Number'] == question_number]\n answer = subset[subset[\"Account Number\"] == account]\n if((column_number is not None) and (row_number is None)):\n answer = answer[answer[\"Column Number\"] == column_number]\n elif((column_number is not None) and (row_number is not None)):\n answer = answer[(answer[\"Column Number\"] == column_number) & (answer[\"Row Number\"] == row_number)]\n elif((column_number is None) and (row_number is not None)):\n answer = answer[answer[\"Row Number\"] == row_number]\n else:\n pass\n elif(org_type==\"corp\" and corp_res_type==\"cc\"):\n df = corporations_2020\n subset = df[df['question_number'] == question_number]\n answer = subset[subset[\"account_number\"] == account]\n if((column_number is not None) and (row_number is None)):\n answer = answer[answer[\"column_number\"] == column_number]\n elif((column_number is not None) and (row_number is not None)):\n answer = answer[(answer[\"column_number\"] == column_number) & (answer[\"row_number\"] == row_number)]\n elif((column_number is None) and (row_number is not None)):\n answer = answer[answer[\"row_number\"] == row_number]\n else:\n pass\n elif(org_type==\"corp\" and corp_res_type==\"ws\"):\n df = all_corps_cc[year]\n subset = df[df['question_number'] == question_number]\n answer = subset[subset[\"account_number\"] == account]\n else:\n print(\"Something went wrong. Try again.\")\n \n return answer","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def score_hazard_risk(cdf):\n prob = scores_lo_hi[str(cdf[cdf[\"Column Number\"] == 3.0][\"Response Answer\"].iloc[0])]\n mag = scores_lo_hi[str(cdf[cdf[\"Column Number\"] == 4.0][\"Response Answer\"].iloc[0])]\n \n future_freq = scores_increasing_decreasing[str(cdf[cdf[\"Column Number\"] == 8.0][\"Response Answer\"].iloc[0])]\n future_intensity = scores_increasing_decreasing[str(cdf[cdf[\"Column Number\"] == 9.0][\"Response Answer\"].iloc[0])]\n \n future_mag = scores_lo_hi[str(cdf[cdf[\"Column Number\"] == 10.0][\"Response Answer\"].iloc[0])]\n return (prob*mag) + (prob*future_freq*future_intensity*future_mag)\n\ndef return_risk_and_score_per_county(hazard_type):\n risk = []\n self_reported_score = []\n\n for i in range(len(vulnerability)):\n df = fetch_answer_individual(vulnerability[\"Account Number\"].iloc[i], '2.1')\n idx = np.where(np.array(df[\"Response Answer\"]) == hazard_type)[0]\n if(len(idx) > 0):\n risk.append(1)\n cdf = df[df[\"Row Number\"] == df[\"Row Number\"].iloc[idx[0]]]\n self_reported_score.append(score_hazard_risk(cdf))\n else:\n risk.append(np.nan)\n self_reported_score.append(np.nan)\n return risk, np.nan_to_num(np.array(self_reported_score))\n\nscores_lo_hi = {\n \"Low\" : 1, \n \"Medium Low\": 2, \n \"Medium\":3,\n \"Medium High\":4,\n \"High\":5,\n \"Do not know\":0.5,\n \"Does not currently impact the city\":0.5,\n \"nan\":0,\n}\n\nscores_increasing_decreasing = {\n \"Increasing\" : 2,\n \"Decreasing\" : 0.5,\n \"None\" : 1, \n \"Do not know\" : 1, \n \"Not expected to happen in the future\" : 0.1,\n \"nan\" : 0.0\n}","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"risk_heat_wave, score_heat_wave = return_risk_and_score_per_county(\"Extreme hot temperature > Heat wave\")\nrisk_hot_days, score_hot_days = return_risk_and_score_per_county(\"Extreme hot temperature > Extreme hot days\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"hot_days_ranked = standardize_rank(score_hot_days, direction=1)\nheat_wave_ranked = standardize_rank(score_heat_wave, direction=1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"occupation = pd.read_excel(path + \"Occupation_Data_OECS/MSA_M2019_dl.xlsx\", sheet_name=\"All May 2019 Data\")\n\nall_locs_protective = []\nfor i in range(len(MSA)):\n o = (occupation[occupation[\"area\"] == MSA[i]])\n locs = o[o['occ_code'].str.startswith(\"33\")][\"loc_quotient\"]\n locs = np.array([float(locs.iloc[x]) for x in range(len(locs)) if type(locs.iloc[x]) is float])\n all_locs_protective.append(np.nanmean(locs))\n \nall_locs_building_grounds = []\nfor i in range(len(MSA)):\n o = (occupation[occupation[\"area\"] == MSA[i]])\n locs = o[o['occ_code'].str.startswith(\"37\")][\"loc_quotient\"]\n locs = np.array([float(locs.iloc[x]) for x in range(len(locs)) if type(locs.iloc[x]) is float])\n all_locs_building_grounds.append(np.nanmean(locs))\n \nall_locs_farming = []\nfor i in range(len(MSA)):\n o = (occupation[occupation[\"area\"] == MSA[i]])\n locs = o[o['occ_code'].str.startswith(\"45\")][\"loc_quotient\"]\n locs = np.array([float(locs.iloc[x]) for x in range(len(locs)) if type(locs.iloc[x]) is float])\n all_locs_farming.append(np.nanmean(locs))\n \nall_locs_construction = []\nfor i in range(len(MSA)):\n o = (occupation[occupation[\"area\"] == MSA[i]])\n locs = o[o['occ_code'].str.startswith(\"47\")][\"loc_quotient\"]\n locs = np.array([float(locs.iloc[x]) for x in range(len(locs)) if type(locs.iloc[x]) is float])\n all_locs_construction.append(np.nanmean(locs))\n\nall_locs_production = []\nfor i in range(len(MSA)):\n o = (occupation[occupation[\"area\"] == MSA[i]])\n locs = o[o['occ_code'].str.startswith(\"51\")][\"loc_quotient\"]\n locs = np.array([float(locs.iloc[x]) for x in range(len(locs)) if type(locs.iloc[x]) is float])\n all_locs_production.append(np.nanmean(locs))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"all_locs_protective_ranked = standardize_rank(all_locs_protective, 1)\nall_locs_farming_ranked = standardize_rank(all_locs_farming, 1)\nall_locs_building_grounds_ranked = standardize_rank(all_locs_building_grounds, 1)\nall_locs_construction_ranked = standardize_rank(all_locs_construction, 1)\nall_locs_production_ranked = standardize_rank(all_locs_production, 1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"heat = pd.read_table(\"../input/cdcwonder/heatwave_CDC_wonder.txt\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"heat_maximum_temp = []\nfor i in range(len(match_fips)):\n if(not flag_multi_counties[i]):\n fips = int(match_fips[i])\n cdf = heat[(heat[\"County Code\"] == fips) & ~np.isnan(heat[\"Year\"])]\n heat_maximum_temp.append(np.mean(np.array(cdf[\"Heat Wave Days Based on Daily Maximum Temperature\"], dtype=np.float)))\n else:\n fips = match_fips[i]\n temp = []\n for j in range(len(fips)):\n cdf = heat[(heat[\"County Code\"] == fips[j]) & ~np.isnan(heat[\"Year\"])]\n temp.append(np.mean(np.array(cdf[\"Heat Wave Days Based on Daily Maximum Temperature\"], dtype=np.float)))\n ar = np.array(temp)\n weighted_avg = np.average(ar, weights=np.array(ast.literal_eval(counties_weights[i])))\n heat_maximum_temp.append(weighted_avg)\n\nheat_maximum_index = []\nfor i in range(len(match_fips)):\n if(not flag_multi_counties[i]):\n fips = int(match_fips[i])\n cdf = heat[(heat[\"County Code\"] == fips) & ~np.isnan(heat[\"Year\"])]\n heat_maximum_index.append(np.mean(np.array(cdf[\"Heat Wave Days Based on Daily Maximum Heat Index\"], dtype=np.float)))\n else:\n fips = match_fips[i]\n temp = []\n for j in range(len(fips)):\n cdf = heat[(heat[\"County Code\"] == fips[j]) & ~np.isnan(heat[\"Year\"])]\n temp.append(np.mean(np.array(cdf[\"Heat Wave Days Based on Daily Maximum Heat Index\"], dtype=np.float)))\n ar = np.array(temp)\n weighted_avg = np.average(ar, weights=np.array(ast.literal_eval(counties_weights[i])))\n heat_maximum_index.append(weighted_avg)\n\nheat_maximum_stress = []\nfor i in range(len(match_fips)):\n if(not flag_multi_counties[i]):\n fips = int(match_fips[i])\n cdf = heat[(heat[\"County Code\"] == fips) & ~np.isnan(heat[\"Year\"])]\n heat_maximum_stress.append(np.mean(np.array(cdf[\"Heat Wave Days Based on Net Daily Heat Stress\"], dtype=np.float)))\n else:\n fips = match_fips[i]\n temp = []\n for j in range(len(fips)):\n cdf = heat[(heat[\"County Code\"] == fips[j]) & ~np.isnan(heat[\"Year\"])]\n temp.append(np.mean(np.array(cdf[\"Heat Wave Days Based on Net Daily Heat Stress\"], dtype=np.float)))\n ar = np.array(temp)\n weighted_avg = np.average(ar, weights=np.array(ast.literal_eval(counties_weights[i])))\n heat_maximum_stress.append(weighted_avg)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"heat_maximum_temp_ranked = standardize_rank(heat_maximum_temp, 1)\nheat_maximum_index_ranked = standardize_rank(heat_maximum_index, 1)\nheat_maximum_stress_ranked = standardize_rank(heat_maximum_stress, 1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"heat_maximum_temp_std = []\nfor i in range(len(match_fips)):\n if(not flag_multi_counties[i]):\n fips = int(match_fips[i])\n cdf = heat[(heat[\"County Code\"] == fips) & ~np.isnan(heat[\"Year\"])]\n heat_maximum_temp_std.append(np.std(np.array(cdf[\"Heat Wave Days Based on Daily Maximum Temperature\"], dtype=np.float)))\n else:\n fips = match_fips[i]\n temp = []\n for j in range(len(fips)):\n cdf = heat[(heat[\"County Code\"] == fips[j]) & ~np.isnan(heat[\"Year\"])]\n temp.append(np.std(np.array(cdf[\"Heat Wave Days Based on Daily Maximum Temperature\"], dtype=np.float)))\n ar = np.array(temp)\n weighted_avg = np.average(ar, weights=np.array(ast.literal_eval(counties_weights[i])))\n heat_maximum_temp_std.append(weighted_avg)\n\nheat_maximum_index_std = []\nfor i in range(len(match_fips)):\n if(not flag_multi_counties[i]):\n fips = int(match_fips[i])\n cdf = heat[(heat[\"County Code\"] == fips) & ~np.isnan(heat[\"Year\"])]\n heat_maximum_index_std.append(np.std(np.array(cdf[\"Heat Wave Days Based on Daily Maximum Heat Index\"], dtype=np.float)))\n else:\n fips = match_fips[i]\n temp = []\n for j in range(len(fips)):\n cdf = heat[(heat[\"County Code\"] == fips[j]) & ~np.isnan(heat[\"Year\"])]\n temp.append(np.std(np.array(cdf[\"Heat Wave Days Based on Daily Maximum Heat Index\"], dtype=np.float)))\n ar = np.array(temp)\n weighted_avg = np.average(ar, weights=np.array(ast.literal_eval(counties_weights[i])))\n heat_maximum_index_std.append(weighted_avg)\n\nheat_maximum_stress_std = []\nfor i in range(len(match_fips)):\n if(not flag_multi_counties[i]):\n fips = int(match_fips[i])\n cdf = heat[(heat[\"County Code\"] == fips) & ~np.isnan(heat[\"Year\"])]\n heat_maximum_stress_std.append(np.std(np.array(cdf[\"Heat Wave Days Based on Net Daily Heat Stress\"], dtype=np.float)))\n else:\n fips = match_fips[i]\n temp = []\n for j in range(len(fips)):\n cdf = heat[(heat[\"County Code\"] == fips[j]) & ~np.isnan(heat[\"Year\"])]\n temp.append(np.std(np.array(cdf[\"Heat Wave Days Based on Net Daily Heat Stress\"], dtype=np.float)))\n ar = np.array(temp)\n weighted_avg = np.average(ar, weights=np.array(ast.literal_eval(counties_weights[i])))\n heat_maximum_stress_std.append(weighted_avg)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"heat_maximum_temp_std_ranked = standardize_rank(heat_maximum_temp_std, 1)\nheat_maximum_index_std_ranked = standardize_rank(heat_maximum_index_std, 1)\nheat_maximum_stress_std_ranked = standardize_rank(heat_maximum_stress_std, 1)","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\nheat_wave_eals = nri_select(\"HWAV_EALS\")\nheat_wave_eals_ranked = standardize_rank(heat_wave_eals, 1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"vulnerability_raw = vulnerability.copy()\nvulnerability_ranked = vulnerability.copy()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"vulnerability_raw[\"score_heat_wave\"] = score_heat_wave\nvulnerability_raw[\"score_hot_days\"] = score_hot_days\nvulnerability_raw[\"heat_maximum_temp\"] = heat_maximum_temp\nvulnerability_raw[\"heat_maximum_index\"] = heat_maximum_index\nvulnerability_raw[\"heat_maximum_stress\"] = heat_maximum_stress\nvulnerability_raw[\"heat_maximum_temp_std\"] = heat_maximum_temp_std\nvulnerability_raw[\"heat_maximum_index_std\"] = heat_maximum_index_std\nvulnerability_raw[\"heat_maximum_stress_std\"] = heat_maximum_stress_std\n\nvulnerability_raw[\"LOC_farming_fishing_forestry\"] = all_locs_farming\nvulnerability_raw[\"LOC_construction\"] = all_locs_construction\nvulnerability_raw[\"LOC_protective\"] = all_locs_protective\nvulnerability_raw[\"LOC_building_grounds\"] = all_locs_building_grounds\nvulnerability_raw[\"LOC_production\"] = all_locs_production\nvulnerability_raw[\"heat_wave_expected_annual_loss\"] = heat_wave_eals\n\nvulnerability_raw.to_csv(\"vulnerability_cities_us_heat_kpis_raw.csv\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"vulnerability_ranked[\"heat_wave_ranked\"] = heat_wave_ranked\nvulnerability_ranked[\"hot_days_ranked\"] = hot_days_ranked\nvulnerability_ranked[\"heat_maximum_temp_ranked\"] = heat_maximum_temp_ranked\nvulnerability_ranked[\"heat_maximum_index_ranked\"] = heat_maximum_index_ranked\nvulnerability_ranked[\"heat_maximum_stress_ranked\"] = heat_maximum_stress_ranked\nvulnerability_ranked[\"heat_maximum_temp_std_ranked\"] = heat_maximum_temp_std_ranked\nvulnerability_ranked[\"heat_maximum_index_std_ranked\"] = heat_maximum_index_std_ranked\nvulnerability_ranked[\"heat_maximum_stress_std_ranked\"] = heat_maximum_stress_std_ranked\n\n\nvulnerability_ranked[\"LOC_farming_fishing_forestry_ranked\"] = all_locs_farming_ranked\nvulnerability_ranked[\"LOC_construction_ranked\"] = all_locs_construction_ranked\nvulnerability_ranked[\"LOC_protective_ranked\"] = all_locs_protective_ranked\nvulnerability_ranked[\"LOC_building_grounds_ranked\"] = all_locs_building_grounds_ranked\nvulnerability_ranked[\"LOC_production_ranked\"] = all_locs_production_ranked\nvulnerability_ranked[\"heat_wave_expected_annual_loss_ranked\"] = heat_wave_eals_ranked\nvulnerability_ranked.to_csv(\"vulnerability_cities_us_heat_kpis_ranked.csv\")\n","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\n\nall_expo_heat_kpis = np.c_[heat_wave_ranked, hot_days_ranked, heat_maximum_temp_ranked, heat_maximum_index_ranked,\n heat_maximum_stress_ranked, heat_maximum_temp_std_ranked, heat_maximum_index_std_ranked,\n heat_maximum_stress_std_ranked]\n\naggr_expo_heat_kpis = aggr_and_rescale(all_expo_heat_kpis)\n\nall_sens_heat_kpis = np.c_[all_locs_farming_ranked, all_locs_construction_ranked, all_locs_protective_ranked, all_locs_building_grounds_ranked,\n all_locs_production_ranked, heat_wave_eals_ranked]\n\naggr_sens_heat_kpis = aggr_and_rescale(all_sens_heat_kpis)\n\naggr_heat_kpi = aggr_and_rescale(np.c_[aggr_expo_heat_kpis, aggr_sens_heat_kpis])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"vulnerability_aggr = vulnerability.copy()\n\nvulnerability_aggr[\"aggr_heat_kpis\"] = aggr_heat_kpi\nvulnerability_aggr[\"aggr_exposure_heat_kpis\"] = aggr_expo_heat_kpis\nvulnerability_aggr[\"aggr_sensitivity_heat_kpis\"] = aggr_sens_heat_kpis\n\nvulnerability_aggr.to_csv(\"vulnerability_cities_us_heat_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}