{"cells":[{"metadata":{},"cell_type":"markdown","source":"# Building Data-Driven Models to Identify the Key Socio-Environmental Hazards of Today\n\nFor this project I decided to focus on the question: *How do you help cities adapt to a rapidly changing climate amidst a global pandemic, but do it in a way that is socially equitable?*\n\nI built the following KPIs to measure the various ways in which climate hazards affect cities in 2020 with particular focus on the environmental and social impacts. These KPI will be important to identify which climate hazards to attack and where:\n\n**KPIs to measure hazards:**\n\n* Expected Environmental Impact\n\n* Expected Social Impact\n\n**KPIs to measure cities:**\n\n* Climate Hazard Count\n\n* Total Expected Environmental Impact\n\n* Total Expected Social Impact\n\nThe objective of the first set of KPIs is for cities to identify which of the many climate hazards they face have the highest need for urgency and action. \n\nThe objective of the second set of KPIs is for national and regional governments to identify specific cities that are most at risk and therefore most in need for funding or aid. \n"},{"metadata":{},"cell_type":"markdown","source":"\n\n# Deep dive into KPIs\n\n\n\n**Expected Environmental Impact**\n\nThis KPI is calculated using the formula\n\n*Expected_Environmental_Impact = Magnitude_of_Hazard x Probability_of_Hazard*\n\nwhere magnitude and probability of hazard are derived from 2020 cities disclosure survey.\n\n**Expected Social Impact**\n\nGiven that social impact data is also provided for each hazard, we can build an Expected Social Impact KPI that weigh not only the social impacts that a climate hazard generates but also the vulnerable populations that it affects. Expected Social Impact is calculated as:\n\n*Expected_Social_Impact = Social_Impact_Factor x Vulnerable_Population_Factor*\n\nwhere the social impact factor is the number of social impacts selected for each hazard divided by the number of all social impacts categories available. Similarly the vulnerable population factor is the number of vulnerable populations selected per hazard divided by the number of all vulnerable populations categories available.\n\n**Climate Hazard Count:**\n\nThis is a simple KPI that measures the number of climate hazards that cities reported each year. This KPI allow national or regional governments to identify specific areas that are facing the most hazards and therefore in need of more aid. \n\n**Total Expected Environmental Impact**\n\nSince the CDP cities data includes additional information about each climate risk, including probability and magnitude estimates, we can develop a second KPI which is a more complex yet also a more robust measure of climate hazard. The Total Expected Environmental Impact KPI considers not just how many hazards a city reports but also weighs both the magnitude and probability of each hazard. For each city each year, the Total Expected Environmental Impact KPI is calculated as follows:\n\n*Total_Environmental_Impact = Expected_Environmental_Impact (1st Hazard) + Expected_Environmental_Impact (2nd Hazard) + ... + Expected_Environmental_Impact (\"n\"-th Hazard),*\n\nwhere \"n\" is the climate hazard count.\n\n**Total Expected Social Impact**\n\nWe can similarly aggregate all the expected social impacts per city to generate a Total Social Impact KPI using the formula\n\n*Total_Social_Impact = Expected_Social_Impact (1st Hazard) + Expected_Social_Impact (2nd Hazard) + ... + Expected_Social_Impact (\"n\"-th Hazard),*\n\nhere \"n\" is the climate hazard count.\n"},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python Docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load\n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\npd.set_option('display.max_columns', None)\n\n# Input data files are available in the read-only \"../input/\" directory\n# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory\n\nimport os\n#for dirname, _, filenames in os.walk('/kaggle/input'):\n#    for filename in filenames:\n#        print(os.path.join(dirname, filename))\n\n# You can write up to 20GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using \"Save & Run All\" \n# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session\n\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"\n# importing cities response df\ncities_df = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Cities/Cities Responses/2020_Full_Cities_Dataset.csv\")\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"\nsocial_impacts=cities_df[((cities_df['Question Number'] == '2.1')&(cities_df['Column Number'] == 5))]\nsocial_impacts=social_impacts.rename(columns={\"Response Answer\":\"Social Impact\"})\n\nvulnerables=cities_df[((cities_df['Question Number'] == '2.1')&(cities_df['Column Number'] == 7))]\n\nvulnerables=vulnerables.rename(columns={\"Response Answer\":\"Vulnerable Population\"})\n\nhazards_prob=cities_df[((cities_df['Question Number'] == '2.1')&(cities_df['Column Number'] == 3))]\nhazards_prob=hazards_prob.rename(columns={\"Response Answer\":\"Current Probability of Hazard\"})\n\n\nhazards_mag=cities_df[((cities_df['Question Number'] == '2.1')&(cities_df['Column Number'] == 4))]\nhazards_mag=hazards_mag.rename(columns={\"Response Answer\":\"Current Magnitude of Hazard\"})\n\n\nclimate_harzards=cities_df[((cities_df['Question Number'] == '2.1')&(cities_df['Column Number'] == 1))]\nclimate_harzards=climate_harzards.rename(columns={\"Response Answer\":\"Climate Hazard\"})\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"impacts_hazards = social_impacts.merge(climate_harzards, on=['Row Number','Organization'])\n\nimpacts_hazards_prob = impacts_hazards.merge(hazards_prob,  on=['Row Number','Organization'])\n\nimpacts_hazards_prob_mag = impacts_hazards_prob.merge(hazards_mag,  on=['Row Number','Organization'])\n\nimpacts_hazards_prob_mag_vul = impacts_hazards_prob_mag.merge(vulnerables, on=['Row Number','Organization'])\n\ntest=impacts_hazards_prob_mag_vul[impacts_hazards_prob_mag_vul['Organization']=='City of Berkeley']\na=test.groupby(['Organization','Climate Hazard']).agg({'Social Impact': 'nunique','Row Number': 'count','Vulnerable Population':'nunique'})\na.reset_index()\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"impacts_hazards_prob_mag_vul.loc[impacts_hazards_prob_mag_vul['Current Magnitude of Hazard'] == 'Low', 'Mag_num'] = 1/5 \nimpacts_hazards_prob_mag_vul.loc[impacts_hazards_prob_mag_vul['Current Magnitude of Hazard'] == 'Medium Low', 'Mag_num'] = 2/5 \nimpacts_hazards_prob_mag_vul.loc[impacts_hazards_prob_mag_vul['Current Magnitude of Hazard'] == 'Medium', 'Mag_num'] = 3/5 \nimpacts_hazards_prob_mag_vul.loc[impacts_hazards_prob_mag_vul['Current Magnitude of Hazard'] == 'Medium High', 'Mag_num'] = 4/5\nimpacts_hazards_prob_mag_vul.loc[impacts_hazards_prob_mag_vul['Current Magnitude of Hazard'] == 'High', 'Mag_num'] = 5/5\n\nimpacts_hazards_prob_mag_vul.loc[impacts_hazards_prob_mag_vul['Current Probability of Hazard'] == 'Low', 'Prob_num'] = 1/5 \nimpacts_hazards_prob_mag_vul.loc[impacts_hazards_prob_mag_vul['Current Probability of Hazard'] == 'Medium Low', 'Prob_num'] = 2/5 \nimpacts_hazards_prob_mag_vul.loc[impacts_hazards_prob_mag_vul['Current Probability of Hazard'] == 'Medium', 'Prob_num'] = 3/5 \nimpacts_hazards_prob_mag_vul.loc[impacts_hazards_prob_mag_vul['Current Probability of Hazard'] == 'Medium High', 'Prob_num'] = 4/5\nimpacts_hazards_prob_mag_vul.loc[impacts_hazards_prob_mag_vul['Current Probability of Hazard'] == 'High', 'Prob_num'] = 5/5\n\n#impacts_hazards_prob_mag_vul=impacts_hazards_prob_mag_vul2.rename(columns={\"Response Answer_y\": \"Climate Hazard\",\"Response Answer_x\":\"Social Impact\"})\n\n\n#impacts_hazards_prob_mag_vul[['Climate Hazard','Social Impact','Prob_num','Mag_num']]\n#test=impacts_hazards_prob_mag_vul\n\nsummary=impacts_hazards_prob_mag_vul.groupby(['Country','CDP Region','Organization','Climate Hazard']).agg({'Social Impact': 'nunique','Vulnerable Population': 'nunique','Prob_num': 'max','Mag_num': 'max','Row Number': 'count'})\nsummary=summary.sort_values(['Prob_num','Social Impact'], ascending=[False,False]).reset_index()\nsummary[summary['Organization']=='City of Berkeley']\n\n#cities_df[((cities_df['Question Number'] == '2.1')&(cities_df['Column Number'] ==1)&(cities_df['Organization']=='Comune di Milano'))]\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"summary[summary['Organization']=='City of Berkeley']\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"summary[summary['Organization']=='Comune di Milano']\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"#showing for US\ncities_details = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Cities/Cities Disclosing/2020_Cities_Disclosing_to_CDP.csv\")\n\ncities_details=cities_details.rename(columns={\"Country\":\"Country2\",\"CDP Region\":\"CDP Region2\"})\n\nimpacts_hazards_prob_mag_vul_regional=impacts_hazards_prob_mag_vul.merge(cities_details, on=['Account Number'])\n\n\n\nus_summary=impacts_hazards_prob_mag_vul_regional[impacts_hazards_prob_mag_vul_regional['Country2']=='United States of America']\n\nus_summary[us_summary['Organization_x']=='City of Miramar'].head(30)\n\n\n#impacts_hazards_prob_mag_vul  34601\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"us=impacts_hazards_prob_mag_vul_regional[impacts_hazards_prob_mag_vul_regional['Country2']=='United States of America']\n\nus_pre=us[['Country','CDP Region2','Organization_y','Climate Hazard','Mag_num','Prob_num','Vulnerable Population','Social Impact','Population']].groupby(['Country','CDP Region2','Organization_y','Climate Hazard','Mag_num','Prob_num','Population']).agg({'Vulnerable Population': 'nunique','Social Impact': 'nunique'}).reset_index()\nus_pre['Expected_Environmental_Impact']=us_pre['Mag_num']*us_pre['Prob_num']\n\n\nus_pre['Vulnerable Population Factor']=us_pre['Vulnerable Population']/11\n\nus_pre['Social Impact Factor']=us_pre['Social Impact']/12\n\nus_pre['Expected_Social_Strain']=us_pre['Social Impact Factor']*us_pre['Vulnerable Population Factor']\n\nus_summary=us_pre.groupby(['Country','CDP Region2','Organization_y','Population']).agg({'Climate Hazard': 'nunique','Mag_num': 'mean','Prob_num': 'mean','Vulnerable Population':'mean','Social Impact':'mean','Expected_Environmental_Impact':'sum','Expected_Social_Strain':'sum'}).reset_index()\ntop20=us_summary.sort_values(['Climate Hazard'], ascending=[False]).head(20)\ntopAll=us_summary.sort_values(['Climate Hazard'], ascending=[False])\n\ntop20_exp_imp=us_summary.sort_values(['Expected_Environmental_Impact'], ascending=[False]).head(20)\ntopAll_exp_imp=us_summary.sort_values(['Expected_Environmental_Impact'], ascending=[False])\ntop20_exp_imp\n\ntop20_social=us_summary.sort_values(['Expected_Social_Strain'], ascending=[False]).head(20)\n\n\n#top20_exp_imp\nus_pre[us_pre['Organization_y']=='City of Cincinnati']\n\n\nus_pre[us_pre['Organization_y']=='City of Cincinnati'].sort_values(by='Expected_Social_Strain', ascending=False)\n#us_summary\n#us\n#us_summary\n\nus[us['Organization_x']=='City of Miramar']\ntopAll\n#us_pre=us[['Country','CDP Region2','Organization_y','Climate Hazard','Mag_num','Prob_num','Vulnerable Population','Social Impact','Population']].groupby(['Country','CDP Region2','Organization_y','Climate Hazard','Mag_num','Prob_num']).agg({'Vulnerable Population': 'nunique','Social Impact': 'nunique'}).reset_index()\n\n","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"trusted":true},"cell_type":"code","source":"#us_pre=us[['Country','CDP Region2','Organization_y','Climate Hazard','Mag_num','Prob_num','Vulnerable Population','Social Impact','Population']].groupby(['Country','CDP Region2','Organization_y','Climate Hazard','Mag_num','Prob_num']).agg({'Vulnerable Population': 'nunique','Social Impact': 'nunique'}).reset_index()\n\nus_prex=us[['Organization_y','Population']].groupby(['Organization_y']).max().reset_index()\n\nus_prex.sort_values(by='Population')\n#us.sort_values(by='Organization_x')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"# plotting libs\n!pip install seaborn==0.11.0\nimport seaborn as sns\nimport matplotlib.pyplot as plt\nprint(sns.__version__)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Analysis and Insights"},{"metadata":{},"cell_type":"markdown","source":"Using the Climate Hazard Count KPI to look at all the US cities that reported in 2020, we can see in the chart below that most cities reported between 3-7 climate hazards but a few cities face more than 10 hazards. This means that these locations might need more attention or help from the national government."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"plot1=sns.histplot(data=topAll, x=\"Climate Hazard\", binwidth = 1)\nplot1.set(ylabel=\"Number of Cities\", xlabel = \"Climate Hazard Count\")\nplt.show() #.xticks([0, 5, 10, 20])","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Ranking the cities by Climate Hazard Count, we see that the top condenders are Highland Park, Urbana, and Boynton Beach, each identifying 14 or more climate hazards."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"\n\nplot2 = sns.barplot(x=\"Organization_y\", y=\"Climate Hazard\",hue='Country', data=top20)\n\nplot2.set_xticklabels(plot2.get_xticklabels(), rotation=45, horizontalalignment='right')\nplot2.set(xlabel=\"Number of Cities\", ylabel = \"Climate Hazard Count\")\n\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"We can then use the Expected Environmental Impact KPI to check out which are the most urgent and impactful issues in Boynton Beach. It appears that it's facing major issues related to flood and sea level rise. This makes sense; the city is called Boynton *Beach* after all. Storm and wind related climate hazards follow in terms of Expected Environmental Impact."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"df=us_pre[['Organization_y','Climate Hazard','Expected_Environmental_Impact']][us_pre['Organization_y']=='City of Boynton Beach'].sort_values(by='Expected_Environmental_Impact', ascending=False).reset_index()\nsocial_impacts=social_impacts.rename(columns={\"Response Answer\":\"Social Impact\"})\n\ndf=df[['Organization_y','Climate Hazard','Expected_Environmental_Impact']]\n\ncm = sns.light_palette(\"red\", as_cmap=True)\n\ns = df.rename(columns={\"Expected_Environmental_Impact\":\"Expected Environmental Impact\",\"Organization_y\":\"City\"}).style.background_gradient(cmap=cm)\n\ns\n#social_impacts=social_impacts.rename(columns={\"Response Answer\":\"Social Impact\"})\n\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"We can also show the distribution of cities according to Total Expected Social Impact. We have a high frequency from 0-0.5 Total Expected Social Impact as many cities only cited one or two social impacts or vulnerable populations per hazard. "},{"metadata":{"trusted":true,"_kg_hide-output":false,"_kg_hide-input":true},"cell_type":"code","source":"plot1b=sns.histplot(data=topAll, x=\"Expected_Social_Strain\", binwidth = .5)\nplot1b.set(ylabel=\"Number of Cities\", xlabel = \"Total Expected Social Impact\")\nplt.show() #.xticks([0, 5, 10, 20])","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"If we rank the cities from highest to lowerst Total Expected Social Impact, we see below that Medford, Tempe, and Iowa cited the highest Total Expected Social Impact."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"top20_social\n\n\nplot2b = sns.barplot(x=\"Organization_y\", y=\"Expected_Social_Strain\",hue='Country', data=top20_social)\n\nplot2b.set_xticklabels(plot2b.get_xticklabels(), rotation=45, horizontalalignment='right')\nplot2b.set(xlabel=\"Number of Cities\", ylabel = \"Total Expected Social Impact\")\n\nplt.show()\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Below is a breakdown of the Expected Social Impact of each hazard in Medford."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"df=us_pre[['Organization_y','Climate Hazard','Expected_Social_Strain']][us_pre['Organization_y']=='City of Medford'].sort_values(by='Expected_Social_Strain', ascending=False).reset_index()\n\ndf=df[['Organization_y','Climate Hazard','Expected_Social_Strain']]\n\ncm = sns.light_palette(\"red\", as_cmap=True)\n\ns = df.rename(columns={\"Expected_Social_Strain\":\"Expected Social Impact\",\"Organization_y\":\"City\"}).style.background_gradient(cmap=cm)\n\ns","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Let's check out the social impacts of \"Extreme Precipitation > Rain storm\" in Medford."},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"us_medford=us[['Organization_y','Climate Hazard','Social Impact']][((us['Organization_y']=='City of Medford')&(us['Climate Hazard']=='Extreme Precipitation > Rain storm'))].drop_duplicates()\nus_medford['Len']=us_medford['Social Impact'].str.len()\n\nus_medford.sort_values('Len', ascending=False, inplace=True)\nus_medford_short=us_medford[['Organization_y','Climate Hazard','Social Impact']].reset_index()\nus_medford_short.rename(columns={\"Organization_y\":\"City\"}).style","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"We can also check out the list of vulnerable populations affected by this climate hazard."},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"us_medford=us[['Organization_y','Climate Hazard','Vulnerable Population']][((us['Organization_y']=='City of Medford')&(us['Climate Hazard']=='Extreme Precipitation > Rain storm'))].drop_duplicates()\nus_medford['Len']=us_medford['Vulnerable Population'].str.len()\n\nus_medford.sort_values('Len', ascending=False, inplace=True)\nus_medford_short=us_medford[['Organization_y','Climate Hazard','Vulnerable Population']].reset_index()\nus_medford_short.rename(columns={\"Organization_y\":\"City\"}).style","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Lastly, we can plot Total Expected Environmental Impact versus Total Expected Social Impact to identify where each city lie on both dimensions. By representing city population as the relative size of the dot on the scatterplot, we can find where the highly populated cities fall on the environmental and social impact scales.\n\nThe scatterplot below also shows a positive correlation between environmental impact and social impact. This means that as climate hazards intensify the social impacts, especially to vulnerable populations, worsen as well."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"sns.scatterplot(data=topAll, x=\"Expected_Environmental_Impact\", y=\"Expected_Social_Strain\",size='Population',sizes=(50, 500),alpha=.5)\n\n\nplt.xlabel(\"Toal Expected Environmental Impact\")\nplt.ylabel(\"Toal Expected Social Impact\")\nplt.show()\n\n#topAll.a = df.a.astype(float)\n\n#topAll\n#useful for cohorting cities to address\n#highlights that social impact is not yet fully realized harder to measure. ","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}