{"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\nfrom io import StringIO","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"| KPI Name | KPI Interpretation | Public Data Source |\n|---------------------------|----------------------------------------------------------------------|----------------------------|\n| flood_score | Calculated score based on survey response | CDP Survey |\n| insured_locations | # of NFIP insured locations (should be indication of exposure) | FEMA NFIP |\n| fema_flood_events | # of FEMA disaster declarations near city related to floods | FEMA Disaster Declarations |\n| fema_coastal_storms | # of FEMA disaster declarations near city related to coastal storms | FEMA Disaster Declarations |\n| fema_landslides | # of FEMA disaster declarations near city related to landslides | FEMA Disaster Declarations |\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| percent_built_before_1999 | Percentage of buildings built before 1999 | US Census |\n| coastal_flood_eals | Coastal flood expected annual loss | FEMA National Risk Index |\n| river_flood_eals | River flood expected annual loss | FEMA National Risk Index |\n| landslide_eals | Landslide 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))]\ncities = vulnerability[\"city_bing\"]\npopulation = vulnerability[\"Population\"]","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":"\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}\n\ndef 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))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"risk_storm_surge, score_storm_surge = return_risk_and_score_per_county(\"Storm and wind > Storm surge\")\n\nrisk_monsoon, score_monsoon = return_risk_and_score_per_county(\"Extreme Precipitation > Monsoon\")\n\nrisk_rain_storm, score_rain_storm = return_risk_and_score_per_county(\"Extreme Precipitation > Rain storm\")\n\nrisk_flash_flood, score_flash_flood = return_risk_and_score_per_county(\"Flood and sea level rise > Flash / surface flood\")\n\nrisk_river_flood, score_river_flood = return_risk_and_score_per_county(\"Flood and sea level rise > River flood\")\n\nrisk_coastal_flood, score_coastal_flood = return_risk_and_score_per_county(\"Flood and sea level rise > Coastal flood\")\n\nrisk_groundwater_flood, score_groundwater_flood = return_risk_and_score_per_county(\"Flood and sea level rise > Groundwater flood\")\n\nrisk_permanent_inundation, score_permanent_inundation = return_risk_and_score_per_county(\"Flood and sea level rise > Permanent inundation\")\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"flood_score = score_storm_surge + score_flash_flood + score_river_flood + score_coastal_flood + score_groundwater_flood + score_permanent_inundation\nprecip_score = score_rain_storm + score_monsoon\n\nflood_score_ranked = standardize_rank(flood_score, direction=1)\nprecip_score_ranked = standardize_rank(precip_score, direction=1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"exposure = pd.read_excel(\"../input/nfipexposure/02_NFIP_2018_By_Zip5_Exposure.xlsx\", sheet_name=\"Exposure_ByZip\", skiprows=5)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"insured_locations = []\nfor i in range(len(match_fips)):\n if(not flag_multi_counties[i]):\n fips = int(match_fips[i])\n cdf = exposure[exposure[\"StateCountyFIPS\"] == fips]\n insured_locations.append(np.sum(cdf[\"Locations\"])/population[i])\n else:\n fips = match_fips[i]\n temp = []\n for j in range(len(fips)):\n cdf = exposure[exposure[\"StateCountyFIPS\"] == fips[j]]\n temp.append(np.sum(cdf[\"Locations\"]))\n ar = np.array(temp)\n weighted_avg = np.average(ar, weights=np.array(ast.literal_eval(counties_weights[i])))\n insured_locations.append(weighted_avg/population[i])\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"insured_locations_ranked = standardize_rank(insured_locations, direction=1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"fema = pd.read_csv(\"../input/femadisasters/DisasterDeclarationsSummaries.csv\")\nct = np.char.zfill(np.array(fema['fipsCountyCode']).astype(str), 3)\nst = np.char.zfill(np.array(fema['fipsStateCode']).astype(str), 2)\nfema['fips'] = np.core.defchararray.add(st, ct).astype(int)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"fema_flood_events = []\nfor i in range(len(match_fips)):\n if(not flag_multi_counties[i]):\n fips = int(match_fips[i])\n cdf = fema[(fema[\"incidentType\"] == \"Flood\") | ((fema[\"incidentType\"] == \"Severe Storm(s)\") & (fema[\"declarationTitle\"].str.lower().str.contains(\"flood\")))]\n cdf = cdf[cdf[\"fips\"] == fips]\n fema_flood_events.append(len(cdf))\n else:\n fips = match_fips[i]\n temp = []\n for j in range(len(fips)):\n cdf = fema[(fema[\"incidentType\"] == \"Flood\") | ((fema[\"incidentType\"] == \"Severe Storm(s)\") & (fema[\"declarationTitle\"].str.lower().str.contains(\"flood\")))]\n cdf = cdf[cdf[\"fips\"] == fips[j]]\n temp.append(len(cdf))\n ar = np.array(temp)\n weighted_avg = np.average(ar, weights=np.array(ast.literal_eval(counties_weights[i])))\n fema_flood_events.append(weighted_avg)\n\nfema_coastal_storms = []\nfor i in range(len(match_fips)):\n if(not flag_multi_counties[i]):\n fips = int(match_fips[i])\n cdf = fema[(fema[\"incidentType\"] == \"Coastal Storm\") | ((fema[\"incidentType\"] == \"Severe Storm(s)\") & (fema[\"declarationTitle\"].str.lower().str.contains(\"coastal\")))]\n cdf = cdf[cdf[\"fips\"] == fips]\n fema_coastal_storms.append(len(cdf))\n else:\n fips = match_fips[i]\n temp = []\n for j in range(len(fips)):\n cdf = fema[(fema[\"incidentType\"] == \"Coastal Storm\") | ((fema[\"incidentType\"] == \"Severe Storm(s)\") & (fema[\"declarationTitle\"].str.lower().str.contains(\"coastal\")))]\n cdf = cdf[cdf[\"fips\"] == fips[j]]\n temp.append(len(cdf))\n ar = np.array(temp)\n weighted_avg = np.average(ar, weights=np.array(ast.literal_eval(counties_weights[i])))\n fema_coastal_storms.append(weighted_avg)\n\nfema_landslides = []\nfor i in range(len(match_fips)):\n if(not flag_multi_counties[i]):\n fips = int(match_fips[i])\n cdf = fema[(fema[\"incidentType\"] == \"Mud/Landslide\") | ((fema[\"incidentType\"] == \"Severe Storm(s)\") & (fema[\"declarationTitle\"].str.lower().str.contains(\"landslide\")))]\n cdf = cdf[cdf[\"fips\"] == fips]\n fema_landslides.append(len(cdf))\n else:\n fips = match_fips[i]\n temp = []\n for j in range(len(fips)):\n cdf = fema[(fema[\"incidentType\"] == \"Mud/Landslide\") | ((fema[\"incidentType\"] == \"Severe Storm(s)\") & (fema[\"declarationTitle\"].str.lower().str.contains(\"landslide\")))]\n cdf = cdf[cdf[\"fips\"] == fips[j]]\n temp.append(len(cdf))\n ar = np.array(temp)\n weighted_avg = np.average(ar, weights=np.array(ast.literal_eval(counties_weights[i])))\n fema_landslides.append(weighted_avg)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"fema_flood_events_ranked = standardize_rank(fema_flood_events, direction=1)\nfema_coastal_storms_ranked = standardize_rank(fema_coastal_storms, direction=1)\nfema_landslides_ranked = standardize_rank(fema_landslides, direction=1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"building = pd.read_csv(\"../input/housingyear/Year_Housing/Building_Year.csv\", skiprows=1)\nbuilding.head()\nbuilding = building.loc[:, ~building.columns.str.startswith('Margin')]\nbuilding[\"id\"] = building[\"id\"].str[9:].astype(int)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"percent_built_before_1999 = []\nfor i in range(len(match_fips)):\n if(not flag_multi_counties[i]):\n fips = int(match_fips[i])\n cdf = building[building[\"id\"] == fips]\n percent_built_before_1999.append(np.sum(np.array(cdf)[0][6:])/(np.array(cdf)[0][2]))\n else:\n fips = match_fips[i]\n temp = []\n for j in range(len(fips)):\n cdf = building[building[\"id\"] == fips[j]]\n temp.append(np.sum(np.array(cdf)[0][6:])/(np.array(cdf)[0][2]))\n ar = np.array(temp)\n weighted_avg = np.average(ar, weights=np.array(ast.literal_eval(counties_weights[i])))\n percent_built_before_1999.append(weighted_avg)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"percent_built_before_1999_ranked = standardize_rank(percent_built_before_1999, direction=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\ncoastal_flood_eals = nri_select(\"CFLD_EALS\")\nriver_flood_eals = nri_select(\"RFLD_EALS\")\nlandslide_eals = nri_select(\"LNDS_EALS\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"coastal_flood_eals_ranked = standardize_rank(coastal_flood_eals, direction=1)\nriver_flood_eals_ranked = standardize_rank(river_flood_eals, direction=1)\nlandslide_eals_ranked = standardize_rank(landslide_eals, direction=1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"vulnerability_raw = vulnerability.copy()\nvulnerability_ranked = vulnerability.copy()\nvulnerability_aggr = vulnerability.copy()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"### EXPOSURE \n\n\nvulnerability_raw[\"flood_score\"] = flood_score\nvulnerability_raw[\"precip_score\"] = precip_score\nvulnerability_raw[\"insured_locations_normalized_by_pop\"] = insured_locations\n\nvulnerability_raw[\"fema_flood_events\"] = fema_flood_events\nvulnerability_raw[\"fema_coastal_storms\"] = fema_coastal_storms\nvulnerability_raw[\"fema_landslides\"] = fema_landslides\n\n### SENSITIVITY\n\nvulnerability_raw[\"percent_built_before_1999\"] = percent_built_before_1999\nvulnerability_raw[\"coastal_flood_expected_annual_loss\"] = coastal_flood_eals\nvulnerability_raw[\"river_flood_expected_annual_loss\"] = river_flood_eals\nvulnerability_raw[\"landslide_expected_annual_loss\"] = landslide_eals\n\nvulnerability_raw.to_csv(\"vulnerability_cities_us_floods_kpis_raw.csv\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"### EXPOSURE \nvulnerability_ranked[\"flood_score_ranked\"] = flood_score_ranked\nvulnerability_ranked[\"precip_score_ranked\"] = precip_score_ranked\nvulnerability_ranked[\"insured_locations_ranked\"] = insured_locations_ranked\n\nvulnerability_ranked[\"fema_flood_events_ranked\"] = fema_flood_events_ranked\nvulnerability_ranked[\"fema_coastal_storms_ranked\"] = fema_coastal_storms_ranked\nvulnerability_ranked[\"fema_landslides_ranked\"] = fema_landslides_ranked\n\n### SENSITIVITY\n\nvulnerability_ranked[\"percent_built_before_1999_ranked\"] = percent_built_before_1999_ranked\nvulnerability_ranked[\"coastal_flood_expected_annual_loss_ranked\"] = coastal_flood_eals_ranked\nvulnerability_ranked[\"river_flood_expected_annual_loss_ranked\"] = river_flood_eals_ranked\nvulnerability_ranked[\"landslide_expected_annual_loss_ranked\"] = landslide_eals_ranked\nvulnerability_ranked.to_csv(\"vulnerability_cities_us_floods_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","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"all_expo_floods_kpis = np.c_[flood_score_ranked, precip_score_ranked, insured_locations_ranked, fema_flood_events_ranked,\n fema_coastal_storms_ranked, fema_landslides_ranked]\n\naggr_expo_floods_kpis = aggr_and_rescale(all_expo_floods_kpis)\n\nall_sens_floods_kpis = np.c_[percent_built_before_1999_ranked, coastal_flood_eals_ranked, river_flood_eals_ranked, landslide_eals_ranked]\n\naggr_sens_floods_kpis = aggr_and_rescale(all_sens_floods_kpis)\n\naggr_floods_kpi = aggr_and_rescale(np.c_[aggr_expo_floods_kpis, aggr_sens_floods_kpis])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"vulnerability_aggr = vulnerability.copy()\n\nvulnerability_aggr[\"aggr_floods_kpis\"] = aggr_floods_kpi\nvulnerability_aggr[\"aggr_exposure_floods_kpis\"] = aggr_expo_floods_kpis\nvulnerability_aggr[\"aggr_sensitivity_floods_kpis\"] = aggr_sens_floods_kpis\n\nvulnerability_aggr.to_csv(\"vulnerability_cities_us_floods_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}