{"cells":[{"metadata":{},"cell_type":"markdown","source":"# Finding Those Most At Risk\n\n### By: Climate Dreamer Pros"},{"metadata":{},"cell_type":"markdown","source":"## Introduction"},{"metadata":{},"cell_type":"markdown","source":"\"*Celebrating a birthday while ash-filled rain pours outside from a nearby wildfire*\"\n\n\"*Seeing cars ditched in the middle of the streets after a flash flood had made it impossible to drive*\"\n\n\"*Arguing over who gets the icepack next on the 15th blisteringly hot day in an apartment where the air conditioning has stopped working...again*\"\n\nEveryone on our team has one story of many describing how climate change has impacted their lives. \nAlthough we had all shared several uncomfortable experiences, we knew we did not come close to experiencing the true scale of climate change related adversity that exists. \n\nWe were not the ones that would suffer most if we lost our disaster-insured home in a wildfire or the ones that would experience the detremental health affects from inhaling the resulting smoke. \n\nWe were not the ones that had to live in a low-elevation coastal area that has cheaper housing but also is prone to flooding.  "},{"metadata":{},"cell_type":"markdown","source":"## Intersection between Environmental and Social Issues"},{"metadata":{},"cell_type":"markdown","source":"The relationship between climate change and the most disadvantaged groups in society is well known to be characterized as a positive feedback loop. Initial inequality not only allows the most disadvantaged groups to be impacted disproportionately worse by the impacts of climate change, but also results in increased subsequent inequality.\n\nSpecifically it has been [documented](http://www.un.org/esa/desa/papers/2017/wp152_2017.pdf) that the disadvantaged groups of society are more exposed, more susceptible and have more difficulty recovering from the consequences of climate change. Given this, it is crucial to determine which pockets of the world are either currently facing or at risk of facing this positive feedback loop. "},{"metadata":{},"cell_type":"markdown","source":"## Useful Data for Both Governments and Corporations"},{"metadata":{},"cell_type":"markdown","source":"Finding the areas that are most at risk of the positive feedback loop exacerbated by climate change can be of use to both governments and corporations. \n\nBy knowing the most at risk locations, governments can determine where to direct funding and resources in order to prevent the cycle from perpetuating. As we have seen with the current pandemic, governments have difficulty providing resources to the corporations that need it most and instead opt for [widespread injection of stimulus](https://www.washingtonpost.com/business/2020/05/04/small-businesses-still-afloat-grapple-with-whether-accept-stimulus-funds/) to company balance sheets. Knowing which companies have locations in the areas that are most disadvantaged (from both a social and environmental standpoint), would provide a more useful tool to direct resources.\n\nIf companies knew the most at risk locations, not only could that help with risk assessment regarding their physical locations (i.e. headquarters), but would also give companies a concrete reason to contribute to their local communities.  "},{"metadata":{},"cell_type":"markdown","source":"## Creating a Metric"},{"metadata":{},"cell_type":"markdown","source":"Our proposal for a metric is based on combining variables from both socio-economic data as well as the climate change induced hazards that an area is expecting to face. We designed the metric to be a combination of a 'disadvantaged' score and a 'hazard' score for all cities in the United States. Below is the methodology that we followed when developing our 'Most At Risk' metric."},{"metadata":{},"cell_type":"markdown","source":"### Importing Libraries"},{"metadata":{"trusted":true},"cell_type":"code","source":"import numpy as np\nimport pandas as pd","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Loading Data"},{"metadata":{},"cell_type":"markdown","source":"In order to create a 'disadvantaged' score for each city in the United States, we used the 2018 CDC Social Vulnerability Index data. Determining a 'hazard' score for each city in the United States was done using the 2018 CDP Cities Responses data."},{"metadata":{"trusted":true},"cell_type":"code","source":"survey_year = '2018'\nsurvey_type = 'Climate Change'\npath = '/kaggle/input/cdp-unlocking-climate-solutions/'\n\ncities_df = pd.read_csv(path+'Cities/Cities Responses/'+survey_year+'_Full_Cities_Dataset.csv')\ncities_info_df = pd.read_csv(path+'Cities/Cities Disclosing/'+survey_year+'_Cities_Disclosing_to_CDP.csv')\ncorps_cc_df = pd.read_csv(path+'Corporations/Corporations Responses/'+survey_type+'/'+survey_year+'_Full_Climate_Change_Dataset.csv')\nna_hq_df = pd.read_csv(path+'Supplementary Data/Locations of Corporations/NA_HQ_public_data.csv')\ncorps_cc_info_df = pd.read_csv(path+'Corporations/Corporations Disclosing/'+survey_type+'/'+survey_year+'_Corporates_Disclosing_to_CDP_Climate_Change.csv')\ncensus_df = pd.read_csv(path+'Supplementary Data/CDC 500 Cities Census Tract Data/500_Cities__Census_Tract-level_Data__GIS_Friendly_Format___2019_release.csv')\nsocial_df = pd.read_csv(path+'Supplementary Data/CDC Social Vulnerability Index 2018/SVI2018_US.csv')\nsocial_county_df = pd.read_csv(path+'Supplementary Data/CDC Social Vulnerability Index 2018/SVI2018_US_COUNTY.csv')\nuscities_df = pd.read_csv(path+'Supplementary Data/Simple Maps US Cities Data/uscities.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"markdown","source":"### Cleaning City Data"},{"metadata":{},"cell_type":"markdown","source":"When looking through the CDP Cities Responses data, we realized that there was a few fixes needed in order to create a metric from a clean dataset. \n\nSome of these fixes included dealing with duplicate City names (with different account numbers) and renaming the city name to one that is more common (removing 'City of' from the name). \n\nWe also separated the Latitude and Longitude information for each city into two columns, as a way to help us in the future when determining which City belong to with State in the United States. "},{"metadata":{"trusted":true},"cell_type":"code","source":"# removing duplicate cities\ncities_info = cities_info_df.copy()\ncities_info.loc[cities_info['Account Number'] == 60393, ['City']] = 'Santiago Government'\n\n# cleaning city names\ncity_names = cities_info[['Account Number','City']]\ncities_clean = pd.merge(cities_df,city_names,how='left',on='Account Number')\ncities_clean['Organization'] = cities_clean['City']\ncities_clean.drop(labels='City', axis=\"columns\", inplace=True)\ncities_clean = cities_clean.rename(columns={'Organization':'City'})\n\n# cleaning lat and long column\nlatlong = cities_info['City Location'].str.strip('POINT ()').str.split(' ', expand=True).rename(columns={0:'Longitude', 1:'Latitude'}) \ncities_info = pd.merge(cities_info,latlong,left_index=True,right_index=True)\ncities_info.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Adding States to Cities Data"},{"metadata":{},"cell_type":"raw","source":"Given that we needed to eventually join a hazard score and a disadvanated score together, it became essential to ensure a State was associated with each city in the CDP Cities Responses data, so we used the Simple Maps US Cities data to help facilitate this. \n\nWe used the pythogorean distance to calculate the shortest distance between two sets of latitude and longitude points (one set in the Cities Responses data, one set in the Simple Maps data) in order to determine the most likely state a city belong to."},{"metadata":{"trusted":true},"cell_type":"code","source":"# adding states to cities dataframe\nuscities = uscities_df.copy()\nuscities = uscities.rename(columns = {'county_name':'County','state_id':'State','city':'City'})\nuscities = uscities[['City','State','County','lat','lng']].drop_duplicates()\n\n# fix errors (switched latitude and longitude)\ncities_info.loc[cities_info['City'] == 'Key West',['Latitude']] = 24.5551\ncities_info.loc[cities_info['City'] == 'Key West',['Longitude']] = 81.78\n\ncities_info.loc[cities_info['City'] == 'South Bend',['Latitude']] = 41.6574 \ncities_info.loc[cities_info['City'] == 'South Bend',['Longitude']] = -86.2532\n\ncities_info.loc[cities_info['City'] == 'Aurora',['Latitude']] = 41.7606 \ncities_info.loc[cities_info['City'] == 'Aurora',['Longitude']] = 88.3201\n\ncities_info.loc[cities_info['City'] == 'Norfolk',['Latitude']] = 38.8468 \ncities_info.loc[cities_info['City'] == 'Norfolk',['Longitude']] = -76.2851\n\n# use pythagorean distance to determine most likely state that the city is in\npyth_dist = pd.merge(uscities[['City','State','lat','lng']],cities_info[['City','Latitude','Longitude']],on='City',how='left')\npyth_dist[pyth_dist['City']=='Cleveland']\n\ndef pyth(lat,Lat,lng,Long):\n    Q = (( float(lat) - abs(float(Lat)) )**2  + (float(lng) + abs(float(Long)))**2 )**0.5\n    return Q\n\npyth_dist['dist'] = pyth_dist.apply(lambda row:pyth(row['lat'],row['Latitude'],row['lng'],row['Longitude']), axis=1)\npyth_dist = pyth_dist[pyth_dist['Latitude'].notnull()]\npyth_dist = pyth_dist.loc[pyth_dist.groupby('City')['dist'].idxmin()]\n\n# add states to cities_clean \ncity_states = pyth_dist[['City','State']]\ncities_clean = pd.merge(cities_clean,city_states,on='City',how='left')\ncities_clean.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Cleaning Social Vulnerability Data"},{"metadata":{},"cell_type":"markdown","source":"We also realized that we had to add City names to the Social Vulnerability data (which only had county names) in order to eventually match the score we developed to a hazard score. This was done by using the Simple Maps data, which had information matching Cities with Counties. "},{"metadata":{"trusted":true},"cell_type":"code","source":"# adding cities, latitude and longitude to counties\nuscities = uscities_df.copy()\nsocial_county = social_county_df.copy()\n\nuscities = uscities.rename(columns = {'county_name':'County','state_id':'State','city':'City'})\nuscities = uscities[['City','State','County','lat','lng']].drop_duplicates()\nsocial_county = social_county.rename(columns = {'COUNTY':'County','ST_ABBR':'State'})\n\nsocial_cities = pd.merge(social_county,uscities,on=['State','County'],how='right')\nsocial_cities.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Creating a Social Vulnerability Score"},{"metadata":{},"cell_type":"raw","source":"There was a number of variables within the Social Vulnerability data that we believed would be indicators of a particular area being more disadvantaged than others. Specifically: \n\n* Percentage of persons below poverty estimate\n\n* Unemployment Rate estimate\n\n* Percentage of persons with no high school diploma (age 25+) estimate\n\n* Percentage of civilian noninstitutionalized population with a disability estimate\n\n* Percentage of households with no vehicle available estimate\n\n* Percentage of occupied housing units with more people than rooms estimate\n\n* Percentage of single parent households with children under 18 estimate\n\nNormalizing and then taking the average of these scores resulted in a 'disadvantaged' score per city. The higher the score, the more disadvantaged the city's population would be when facing the affects of climate change and further perpuating the positive feedback loop. "},{"metadata":{"trusted":true},"cell_type":"code","source":"def normalize(df,var):\n    min_var = df[var].min()\n    max_var = df[var].max()\n    diff = max_var - min_var\n    df['new_var'] = (df[var] - min_var) / diff\n    return df\n\nsc_metrics = social_cities\n\n# poverty metric\nsc_pov = sc_metrics[['State','County','City','EP_POV']][sc_metrics['EP_POV'] >= 0]\nsc_pov = normalize(sc_pov,'EP_POV').rename(columns={'new_var':'EP_POV_std'})\nsc_pov = sc_pov[['City','State','EP_POV_std']].drop_duplicates()\n\n# unemployment metric\nsc_unp = sc_metrics[['State','County','City','EP_UNEMP']][sc_metrics['EP_UNEMP'] >= 0]\nsc_unp = normalize(sc_unp,'EP_UNEMP').rename(columns={'new_var':'EP_UNEMP_std'})\nsc_unp = sc_unp[['City','State','EP_UNEMP_std']].drop_duplicates()\n\n# education metric\nsc_edu = sc_metrics[['State','County','City','EP_NOHSDP']][sc_metrics['EP_NOHSDP'] >= 0]\nsc_edu = normalize(sc_edu,'EP_NOHSDP').rename(columns={'new_var':'EP_NOHSDP_std'})\nsc_edu = sc_edu[['City','State','EP_NOHSDP_std']].drop_duplicates()\n\n# transportation\nsc_tsp = sc_metrics[['State','County','City','EP_NOVEH']][sc_metrics['EP_NOVEH'] >= 0]\nsc_tsp = normalize(sc_tsp,'EP_NOVEH').rename(columns={'new_var':'EP_NOVEH_std'})\nsc_tsp = sc_tsp[['City','State','EP_NOVEH_std']].drop_duplicates()\n\n# crowded living situation\nsc_crd = sc_metrics[['State','County','City','EP_CROWD']][sc_metrics['EP_CROWD'] >= 0]\nsc_crd = normalize(sc_crd,'EP_CROWD').rename(columns={'new_var':'EP_CROWD_std'})\nsc_crd = sc_crd[['City','State','EP_CROWD_std']].drop_duplicates()\n\n# disabled but non institutionalized\nsc_dis = sc_metrics[['State','County','City','EP_DISABL']][sc_metrics['EP_DISABL'] >= 0]\nsc_dis = normalize(sc_dis,'EP_DISABL').rename(columns={'new_var':'EP_DISABL_std'})\nsc_dis = sc_dis[['City','State','EP_DISABL_std']].drop_duplicates()\n\n# single parents with young kids\nsc_sng = sc_metrics[['State','County','City','EP_SNGPNT']][sc_metrics['EP_SNGPNT'] >= 0]\nsc_sng = normalize(sc_sng,'EP_SNGPNT').rename(columns={'new_var':'EP_SNGPNT_std'})\nsc_sng = sc_sng[['City','State','EP_SNGPNT_std']].drop_duplicates()\n\n# disadvantaged score = (poverty+unemployment+education+transportation+crowded+disabled+single)/7\ndisadv_score = pd.merge(sc_pov,sc_unp,on=['State','City'],how='left')\ndisadv_score = pd.merge(disadv_score,sc_edu,on=['State','City'],how='left')\ndisadv_score = pd.merge(disadv_score,sc_tsp,on=['State','City'],how='left')\ndisadv_score = pd.merge(disadv_score,sc_crd,on=['State','City'],how='left')\ndisadv_score = pd.merge(disadv_score,sc_dis,on=['State','City'],how='left')\ndisadv_score = pd.merge(disadv_score,sc_sng,on=['State','City'],how='left')\ndisadv_score['disadv_score'] = (disadv_score['EP_POV_std']+disadv_score['EP_UNEMP_std']+disadv_score['EP_NOHSDP_std']+disadv_score['EP_NOVEH_std']+disadv_score['EP_CROWD_std']+disadv_score['EP_DISABL_std']+disadv_score['EP_SNGPNT_std'])/7\n\ndisadv_score = disadv_score[['City','State','disadv_score']].drop_duplicates()\ndisadv_score = disadv_score[disadv_score['disadv_score'].notnull()]\ndisadv_score = disadv_score.loc[disadv_score.groupby(['City','State'])['disadv_score'].idxmin()]\n\ndisadv_score[disadv_score['City'].isin(cities_info.City.unique())].sort_values('disadv_score',ascending=False)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Creating a Hazard Score"},{"metadata":{},"cell_type":"markdown","source":"Within the CDP Cities Responses data, we found that a number of cities in the United States described both the upcoming and the current challenges that the city is facing due to climate change, with variables that allowed us to determine whether one city was more or less at risk in comparison to the others. By mapping word categories to numerical values and probabilities, we could then create a 'hazard' score, with the highest score indicating a city that is facing the largest number of high consequence climate change related hazards. "},{"metadata":{"trusted":true},"cell_type":"code","source":"# finding hazards per city\n\nhazards = cities_clean[cities_clean['Question Number'] == '2.2a']\nhazards = hazards[hazards['Response Answer'].notnull()]\n\nhazards = hazards.pivot_table(index=['Account Number', 'City', 'State','Row Number'],\n                                     columns='Column Name', \n                                     values='Response Answer',\n                                     aggfunc=lambda x: ' '.join(x)).reset_index()\nhazards.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"hazard_score = hazards.copy()\n\n# filtering out uncertain responses to be analyzed separately\nhazard_score = hazard_score[(hazard_score['Consequence of hazard'] != 'Do not know') & (hazard_score['Probability of hazard'] != 'Do not know')]\n\n# filtering out most urgent hazards that are currently affecting cities\nhazard_score = hazard_score[hazard_score['Hazard status'] == 'Currently affecting the city']\n\n# creating numerical mappings for hazard characteristics\ntimescale = {'Long-term':2.0, 'Medium-term':6.0,  'Short-term':10.0}\nmagnitude = {'Less serious':2.0, 'Serious':6.0, 'Extremely serious':10.0}\nconsequence = {'Low':2.0, 'Medium Low':4.0, 'Medium':6.0, 'Medium High':8.0, 'High':10.0}\nprobability = {'Low':0.2, 'Medium Low':0.4, 'Medium':0.6, 'Medium High':0.8, 'High':0.95}\n\nhazard_score['Anticipated timescale'].replace(timescale, inplace=True)\nhazard_score['Consequence of hazard'].replace(consequence, inplace=True)\nhazard_score['Magnitude of impact'].replace(magnitude, inplace=True)\nhazard_score['Probability of hazard'].replace(probability, inplace=True)\n\n# defining score and taking average across all hazards\nhazard_score['hazard_score'] = hazard_score['Anticipated timescale']+hazard_score['Magnitude of impact']+(hazard_score['Consequence of hazard']*hazard_score['Probability of hazard'])\nhazard_score[hazard_score['hazard_score'].notnull()][['City','hazard_score']].sort_values('hazard_score')\nhazard_score = hazard_score[['City','State','hazard_score']].groupby(['City','State']).mean()\n\nhazard_score.sort_values('hazard_score', ascending=False)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Combining Scores To Create 'Most At Risk' Metric"},{"metadata":{},"cell_type":"markdown","source":"Finally, multiplying the 'hazard' score with the 'disadvantaged' score of a city creates a 'most at risk' metric where a city with a high value indicates being most disadvantaged from both a socio-economic and climate change standpoint, allowing a clear mechanism to determine who needs the most help urgently. "},{"metadata":{"trusted":true},"cell_type":"code","source":"most_at_risk = pd.merge(hazard_score, disadv_score, on=['City','State'], how='left')\nmost_at_risk['mar_metric'] = most_at_risk['hazard_score']*most_at_risk['disadv_score']\nmost_at_risk.sort_values('mar_metric',ascending=False)","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}