{"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| drought_severity | drought severity index | UNL/drougt.gov (custom query) |\n| drought_severity_variability | drought variability index | UNL/drougt.gov (custom query) |\n| water_loss | freshwater loss | SEDAC |\n| all_locs_construction | Ratio of construction 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| score_drought | Calculated score based on survey response | CDP |\n| score_wild_fire | Calculated score based on survey response | CDP |\n| score_land_fire | Calculated score based on survey response | CDP |\n| fema_drought_fire_events_ranked | # of FEMA drought / wild fire events disasters | FEMA National Risk Index |\n| drought_eals | drought expected annual loss | FEMA National Risk Index |\n| wildfire_eals | wildfire 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\"]","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":"fp = path + 'freshwater_availability.tif'\nimg = rasterio.open(fp)\nimg_array = img.read(1)\n\ndef get_value(raster, array, lon, lat):\n py, px = raster.index(lon, lat)\n return array[py, px]\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"water_loss = []\nfor i in range(len(vulnerability)):\n coords = ((vulnerability['loc'].iloc[i].replace(\"POINT (\", \"\")).replace(\")\",\"\")).split()\n lon = float(coords[0])\n lat = float(coords[1])\n water_loss.append(get_value(img, img_array, lon, lat))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"water_loss_ranked = standardize_rank(water_loss, direction=-1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"drh = pd.read_csv(path + \"drought_monitor_percent_by_population_categorical_2010_2020.csv\")\ndrh[\"Date\"] = pd.to_datetime(drh[\"MapDate\"], format=\"%Y%m%d\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def get_metric_county_year(county, year):\n cdf = drh[(drh['Date'].dt.year == year) & (drh['FIPS'] == county)]\n val = np.array(1.0*(cdf['D0']) + 3.0*(cdf['D1']) + 5.0*(cdf['D2']) + 10.0*(cdf['D3']) + 20.0*(cdf['D4']))\n return val","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"allc = []\nfor j in tqdm(range(len(match_fips))):\n years = np.arange(2010, 2021)\n combine = []\n for i in range(len(years)):\n if(not flag_multi_counties[j]):\n val = get_metric_county_year(match_fips[j], years[i])\n argsortval = (np.argsort(val))[::-1]\n val = np.mean(val[argsortval[0:4]])\n combine.append(val)\n else:\n temp = []\n for k in range(len(match_fips[j])):\n val = get_metric_county_year(match_fips[j][k], years[i])\n argsortval = (np.argsort(val))[::-1]\n val = np.mean(val[argsortval[0:4]])\n temp.append(val)\n ar = np.array(temp)\n weighted_avg = np.average(ar, weights=np.array(ast.literal_eval(counties_weights[j])))\n combine.append(weighted_avg)\n allc.append(combine)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"last_decade_all_cities = np.array(allc)\ndsci = np.median(last_decade_all_cities, axis=1)\ndsci_ranked = standardize_rank(dsci, direction=1)\ndsci_std = np.std(last_decade_all_cities, axis=1)\ndsci_std_ranked = standardize_rank(dsci_std, 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_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))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"all_locs_farming_ranked = standardize_rank(all_locs_farming, 1)\nall_locs_construction_ranked = standardize_rank(all_locs_construction, 1)\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":"scores_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, self_reported_score\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"risk_drought, score_drought = return_risk_and_score_per_county(\"Water Scarcity > Drought\")\nrisk_wild_fire, score_wild_fire = return_risk_and_score_per_county(\"Wild fire > Forest fire\")\nrisk_land_fire, score_land_fire = return_risk_and_score_per_county(\"Wild fire > Land fire\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"drought_ranked = standardize_rank(score_drought, direction=1)\nwild_fire_ranked = standardize_rank(score_wild_fire, direction=1)\nland_fire_ranked = standardize_rank(score_land_fire, 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)\n\nfema_drought_fire_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\"] == \"Fire\") | (fema[\"incidentType\"] == \"Drought\")]\n cdf = cdf[cdf[\"fips\"] == fips]\n fema_drought_fire_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\"] == \"Fire\") | (fema[\"incidentType\"] == \"Drought\")]\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_drought_fire_events.append(weighted_avg)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"fema_drought_fire_events_ranked = standardize_rank(fema_drought_fire_events, direction=1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"score_severity = {\n \"Extremely serious\": 6,\n \"Serious\": 3,\n \"Less Serious\": 1\n}\n\nscore_prob = {\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\ndef score_water_risk(acct_number):\n sub = fetch_answer_individual(acct_number, '14.2a')\n number_of_rows = len(np.unique(sub[\"Row Number\"]))\n flag = np.any(sub[\"Response Answer\"].str.contains(\"Question not applicable\"))\n if((number_of_rows > 0) & (not flag)):\n mag = np.array(sub[sub[\"Column Number\"] == 3.0][\"Response Answer\"])\n score_mag = sum([score_severity[mag[i]] if type(mag[i]) == str else 1 for i in range(len(mag))])\n prob = np.array(sub[sub[\"Column Number\"] == 4.0][\"Response Answer\"])\n score_probab = sum([score_prob[prob[i]] if type(prob[i]) == str else 1 for i in range(len(prob))])\n return number_of_rows*score_mag*score_probab\n else:\n return 0","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"water_security_risk_scores = [score_water_risk(acct_number[i]) for i in range(len(acct_number))]\nwater_security_risk_ranked = standardize_rank(water_security_risk_scores, 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\ndrought_eals = nri_select(\"DRGT_EALS\")\nwildfire_eals = nri_select(\"WFIR_EALS\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"drought_eals_ranked = standardize_rank(drought_eals, 1)\nwildfire_eals_ranked = standardize_rank(wildfire_eals, 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\nvulnerability_raw[\"water_loss\"] = water_loss\nvulnerability_raw[\"drought_severity_index\"] = dsci\nvulnerability_raw[\"drought_severity_index_variability\"] = dsci_std\n\nvulnerability_raw[\"score_drought\"] = score_drought\nvulnerability_raw[\"score_wild_fire\"] = score_wild_fire\nvulnerability_raw[\"score_land_fire\"] = score_land_fire\nvulnerability_raw[\"water_security_risk_score\"] = water_security_risk_scores\n\nvulnerability_raw[\"fema_drought_fire_events\"] = fema_drought_fire_events\n\n### SENSITIVITY \n\nvulnerability_raw[\"LOC_farming_fishing_forestry\"] = all_locs_farming\nvulnerability_raw[\"LOC_construction\"] = all_locs_construction\n\nvulnerability_raw[\"drought_expected_annual_loss\"] = drought_eals\nvulnerability_raw[\"wildfire_expected_annual_loss\"] = wildfire_eals\nvulnerability_raw.to_csv(\"vulnerability_cities_us_drought_kpis_raw.csv\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"### EXPOSURE \n\nvulnerability_ranked[\"water_loss_ranked\"] = water_loss_ranked\nvulnerability_ranked[\"drought_severity_index_ranked\"] = dsci_ranked\nvulnerability_ranked[\"drought_severity_index_variability_ranked\"] = dsci_std_ranked\n\nvulnerability_ranked[\"drought_ranked\"] = drought_ranked\nvulnerability_ranked[\"wild_fire_ranked\"] = wild_fire_ranked\nvulnerability_ranked[\"land_fire_ranked\"] = land_fire_ranked\nvulnerability_ranked[\"water_security_risk_ranked\"] = water_security_risk_ranked\n\nvulnerability_ranked[\"fema_drought_fire_events_ranked\"] = fema_drought_fire_events_ranked\n\n### SENSITIVITY \n\nvulnerability_ranked[\"LOC_farming_fishing_forestry_ranked\"] = all_locs_farming_ranked\nvulnerability_ranked[\"LOC_construction_ranked\"] = all_locs_construction_ranked\n\nvulnerability_ranked[\"drought_expected_annual_loss_ranked\"] = drought_eals_ranked\nvulnerability_ranked[\"wildfire_expected_annual_loss_ranked\"] = wildfire_eals_ranked\nvulnerability_ranked.to_csv(\"vulnerability_cities_us_drought_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\n\nall_expo_drought_kpis = np.c_[water_loss_ranked, dsci_ranked, dsci_std_ranked, drought_ranked, wild_fire_ranked, land_fire_ranked, \n water_security_risk_ranked, fema_drought_fire_events_ranked]\n\naggr_expo_drought_kpis = aggr_and_rescale(all_expo_drought_kpis)\n\nall_sens_drought_kpis = np.c_[all_locs_farming_ranked, all_locs_construction_ranked, drought_eals_ranked, wildfire_eals_ranked]\n\naggr_sens_drought_kpis = aggr_and_rescale(all_sens_drought_kpis)\n\naggr_drought_kpi = aggr_and_rescale(np.c_[aggr_expo_drought_kpis, aggr_sens_drought_kpis])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"vulnerability_aggr = vulnerability.copy()\n\nvulnerability_aggr[\"aggr_drought_kpis\"] = aggr_drought_kpi\nvulnerability_aggr[\"aggr_exposure_drought_kpis\"] = aggr_expo_drought_kpis\nvulnerability_aggr[\"aggr_sensitivity_drought_kpis\"] = aggr_sens_drought_kpis\n\nvulnerability_aggr.to_csv(\"vulnerability_cities_us_drought_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}