{"cells":[{"metadata":{},"cell_type":"markdown","source":"**PROBLEM STATEMENT**\n\nDevelop a methodology for calculating key performance indicators (KPIs) that relate to the environmental and social issues that are discussed in the CDP survey data. Leverage external data sources and thoroughly discuss the intersection between environmental issues and social issues. Mine information to create automated insight generation demonstrating whether city and corporate ambitions take these factors into account."},{"metadata":{},"cell_type":"markdown","source":"# **INTRODUCTION**"},{"metadata":{},"cell_type":"markdown","source":"This notebook explores the climate hazards faced by cities around the world, the impact on people, and the mitigation plans put in place to deal with the consequences of the risks as disclosed to CDP in the annual surveys."},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"import numpy as np\nimport pandas as pd\nimport matplotlib.pyplot as plt\nimport seaborn as sns\nimport plotly.express as px\nfrom pandas.plotting import parallel_coordinates as pc\nfrom ipywidgets import interact, interactive\nimport ipywidgets as widgets\nfrom IPython.display import display\nimport plotly.graph_objects as go\nfrom plotly.graph_objects import Layout\nimport warnings\nfrom tabulate import tabulate\nfrom palettable.colorbrewer.qualitative import Pastel1_7\nwarnings.simplefilter('ignore')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"import ee.mapclient\nimport os\nimport folium\nimport ee\nfrom kaggle_secrets import UserSecretsClient\nfrom google.oauth2.credentials import Credentials\nimport rasterio as rio\nmysecret = \"secretz\" \nrefresh_token = UserSecretsClient().get_secret(mysecret)\ncredentials = Credentials(None,refresh_token=refresh_token,token_uri=ee.oauth.TOKEN_URI,\n        client_id=ee.oauth.CLIENT_ID,client_secret=ee.oauth.CLIENT_SECRET,scopes=ee.oauth.SCOPES)\nee.Initialize(credentials=credentials)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"citid18 = pd.read_csv('../input/cdp-unlocking-climate-solutions/Cities/Cities Disclosing/2018_Cities_Disclosing_to_CDP.csv')\ncitid19 = pd.read_csv('../input/cdp-unlocking-climate-solutions/Cities/Cities Disclosing/2019_Cities_Disclosing_to_CDP.csv')\ncitid20 = pd.read_csv('../input/cdp-unlocking-climate-solutions/Cities/Cities Disclosing/2020_Cities_Disclosing_to_CDP.csv')\ncitid = pd.read_csv('../input/cdp-unlocking-climate-solutions/Cities/Cities Disclosing/Cities_Disclosing_to_CDP_Data_Dictionary.csv')\ncitires18 = pd.read_csv('../input/cdp-unlocking-climate-solutions/Cities/Cities Responses/2018_Full_Cities_Dataset.csv')\ncitires19 = pd.read_csv('../input/cdp-unlocking-climate-solutions/Cities/Cities Responses/2019_Full_Cities_Dataset.csv')\ncitires20 = pd.read_csv('../input/cdp-unlocking-climate-solutions/Cities/Cities Responses/2020_Full_Cities_Dataset.csv')\ncitires = pd.read_csv('../input/cdp-unlocking-climate-solutions/Cities/Cities Responses/Full_Cities_Response_Data_Dictionary.csv')\nacctorg = dict(zip(citid19['Account Number'], citid19['Organization']))\nnox = pd.read_csv('../input/city-no2-emissions/citid19.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"#population\npopulation = citires19[(citires19['Question Number']=='0.5') & (citires19['Column Number']==1)][['Account Number','Response Answer']]\npopulation = population.rename(columns={'Response Answer':'Popn'})\npopulation['Popn'] = population['Popn'].astype('float')\npopulation = population[(population['Popn']>100000) & (population['Popn']<25000000)]\n\n#area\narea = citires19[(citires19['Question Number']=='0.6') & (citires19['Column Number']==1)][['Account Number','Response Answer']]","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Climate Commitments"},{"metadata":{},"cell_type":"markdown","source":"Let us take a look at the different covenants that the cities have signed up for, and the network sizes of the different charters."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"com = citires19[(citires19['Question Number']=='1.1a') & (citires19['Column Number']==1)][['Account Number','Row Number', 'Response Answer']]\ncom = com.replace('Deadline 2020 - Delivering the 1.5 degree ambition of the Paris Agreement in a resilient, inclusive way', 'Paris Agreement')\ncom = com.replace('Global Covenant of Mayors for Climate & Energy: Compact of mayors & Individual city committment', 'Global Covenant of Mayors for Climate & Energy')\ncomtype = citires19[(citires19['Question Number']=='1.1a') & (citires19['Column Number']==2)][['Account Number','Row Number','Response Answer']]\ncom['Response Answer'] = com['Response Answer'].astype('str')\ncom['Response Answer'] = com['Response Answer'].apply(lambda x: 'Other' if 'Other' in x else x)\ncom = com[com['Response Answer']!='nan']\ncomtype['Response Answer'] = comtype['Response Answer'].astype('str')\ncomtype['Response Answer'] = comtype['Response Answer'].apply(lambda x: 'Other' if 'Other' in x else x)\ncomtype = pd.merge(com, comtype, on=['Account Number', 'Row Number'])\ncomtype = comtype.rename(columns={'Response Answer_x':'Commitment', 'Response Answer_y':'Commit Type'})\ncomchart = comtype.groupby(['Commitment','Commit Type']).size().unstack('Commit Type', fill_value=0)\ncomchart = comchart[['Both', 'Adaptation', 'Mitigation','Other','nan']]\ncomchart = comchart.rename(columns={'nan':'Unspecified'})\ncomchart['s']=comchart.sum(axis=1)\ncomchart = comchart.sort_values(by='s')\ncolz = ['Both', 'Adaptation', 'Mitigation','Other','Unspecified']\nax = comchart[colz].plot.barh(stacked=True, figsize=(15,8), grid=True,\n                         color={'Both': '#810F1B', 'Adaptation':'#D2681B', 'Mitigation': '#D2BB1B', 'Unspecified': 'Gray', 'Other':'#98894A'})\nax.xaxis.grid(True, which='major', linestyle='-', linewidth=0.15)\nax.yaxis.grid(True, which='major', linestyle='-', linewidth=0.1)\nax.set_xlabel('No of Signatories')\nax.set_title('Overview of Climate Commitments', fontsize=20)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Most cities have committed to both adaptation and mitigation measures, with a higher focus on mitigation.\nThe **Global Covenant of Mayors for Climate and Energy**, which has the largest number of participating cities, was established in 2016 by merging the Compact of Mayors and the European Union's Covenant of Mayors. It is a global coalition of city leaders addressing climate change by pledging to cut greenhouse gas emissions and prepare for the future impacts of climate change. The Compact highlights cities’ climate impact while measuring their relative risk levels and carbon pollution.The Compact represents a common effort from global city networks C40 Cities Climate Leadership Group (C40), ICLEI, and United Cities and Local Governments (UCLG), as well as UN-Habitat, to unite against climate change."},{"metadata":{},"cell_type":"markdown","source":"Cities have identified various climate hazards, as well as the probability and consequences of each type of risk. The different types of risks can be classified into 8 main categories -\n1. Extreme Weather (temperatures, precipitation)\n2. Water Scarcity\n3. Floods and Sea-level Rise\n4. Storm and Wind\n5. Wild Fire\n6. Mass Movement (landslides and avalanches)\n7. Biological Hazards (water and air-borne diseases)\n8. Chemical Change (Ocean acidificaiton, salt water intrusion)\n\nThe total risk of a given hazard can be calculated as -\n\n#   **Total Risk = Likelihood x Impact**"},{"metadata":{"trusted":true},"cell_type":"markdown","source":"Let us begin by taking a look at how the risks are distributed."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"riskmap = {'Does not currently impact the city':0, 'Do not know':0, 'Low':1, 'Medium Low':2, 'Medium':3, 'Medium High':4, 'High':5}\nhazards = citires19[(citires19['Question Number']=='2.1')][['Account Number','Column Number','Row Number','Response Answer']]\nhazids = hazards[(hazards['Column Number']==1)]\nhazids = hazids.rename(columns={'Response Answer':'Hazard'})\nhazids = hazids[~hazids['Hazard'].isna()]\nhazids['Hazard']=hazids['Hazard'].astype('str')\nhazids['Hazard']=hazids['Hazard'].apply(lambda x: x.split(' >')[0])\nhazids['Hazard']=hazids['Hazard'].apply(lambda x: 'Extreme Weather' if 'Extreme' in x else x)\nhazids['Hazard']=hazids['Hazard'].replace({'Flood and sea level rise':'Flood & sea level rise'})\nprobab = hazards[(hazards['Column Number']==3)]\nconseq = hazards[(hazards['Column Number']==4)]\ntiming = hazards[(hazards['Column Number']==8)]\nhazids = pd.merge(hazids, probab, on=['Account Number', 'Row Number'] )\nhazids = pd.merge(hazids, conseq, on=['Account Number', 'Row Number'] )\nhazids = pd.merge(hazids, timing, on=['Account Number', 'Row Number'] )\nhazids = hazids.rename(columns={'Response Answer_x':'Likelihood', 'Response Answer_y':'Impact','Response Answer':'Timing'})\nhazids['Total Risk'] = hazids['Impact'].map(riskmap)*hazids['Likelihood'].map(riskmap)\nsoconseq = hazards[(hazards['Column Number']==5)].sort_values(by='Row Number')\nsoconseq = soconseq.rename(columns={'Response Answer':'Social Impact'})\nsoconseq = pd.merge(hazids, soconseq, how='left', on=['Account Number','Row Number'])\nsoconseq['Hazard'] = soconseq['Hazard'].astype('str')\nsoconseq['Hazard'] = soconseq['Hazard'].apply(lambda x: 'Other' if 'Other' in x else x)\nsoconseq['Social Impact'] = soconseq['Social Impact'].astype('str')\nsoconseq['Social Impact'] = soconseq['Social Impact'].apply(lambda x: 'Other' if 'Other' in x else x)\nsoconseq = soconseq[soconseq['Social Impact']!='nan']\nsocdic = {'Increased risk to already vulnerable populations': 'Increased risk to vulnerable pop',\n         'Increased demand for healthcare services':'Increased demand for healthcare',\n         'Increased incidence and prevalence of disease and illness':'Increased disease and illness',\n         'Loss of tax base to support public services':'Loss of tax base'}\nsoconseq['Social Impact'] = soconseq['Social Impact'].replace(socdic)\nvulnerable = hazards[(hazards['Column Number']==10)]\nvulnerable = pd.merge(vulnerable, hazids, on=['Account Number','Row Number'])\nvulnerable['Response Answer'] = vulnerable['Response Answer'].astype('str').apply(lambda x: 'Other' if 'Other' in x else x)\nvulnerable['Response Answer'] = vulnerable['Response Answer'].replace('nan', 'Other')\n\nplt.xlabel('Total Risk')\nplt.title('Histogram of Risks')\nalf=0.3\nplt.axvspan(0, 5, facecolor='green', alpha=alf,label='Lo')\nplt.axvspan(5, 10, facecolor='#8CBE2C', alpha=alf, label='Md-Lo')\nplt.axvspan(10, 15, facecolor='yellow', alpha=alf, label='Med')\nplt.axvspan(15, 20, facecolor='orange', alpha=alf, label='Md-Hi')\nplt.axvspan(20, 25, facecolor='red', alpha=alf, label='Hi')\nplt.hist(hazids['Total Risk'],bins=5,  color='black', alpha=0.5, rwidth=0.8)\nplt.xlim(left=0, right=25)\nplt.ylim(bottom=0, top=1100)\nplt.legend(ncol=5)\nplt.grid(True, 'major', linestyle='--', linewidth=.3)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"riskadapt = citires19[(citires19['Question Number']=='3.0') & (citires19['Column Number']==1)][['Account Number', 'Row Number', 'Response Answer']]\nriskadaptact = citires19[(citires19['Question Number']=='3.0') & (citires19['Column Number']==2)][['Account Number', 'Row Number', 'Response Answer']]\nriskadaptactstat = citires19[(citires19['Question Number']=='3.0') & (citires19['Column Number']==4)][['Account Number', 'Row Number', 'Response Answer']]\nriskadapt2 = riskadapt.merge(riskadaptact, on=['Account Number','Row Number'])\nriskadapt2 = riskadapt2.merge(riskadaptactstat, on=['Account Number','Row Number'])\nriskadapt2['Response Answer_y']=riskadapt2['Response Answer_y'].astype('str')\nriskadapt2['Response Answer_y']=riskadapt2['Response Answer_y'].apply(lambda x: 'Other' if 'Other' in x else x)\nriskadapt2['Response Answer_x']=riskadapt2['Response Answer_x'].astype('str')\nriskadapt2['Response Answer_x']=riskadapt2['Response Answer_x'].apply(lambda x: x.split(' >')[0])\nriskadapt2 = riskadapt2.rename(columns={'Response Answer_x':'Hazard','Response Answer_y':'Actions','Response Answer':'Status'})\nactdict = {'Public preparedness (including practice exercises/drills)':'Public preparedness',\n           'Public preparedness (including practice exercises/drills): Mangrove Planting':'Public preparedness',\n           'Shading in public spaces, markets':'Shading in public spaces',\n           'Crisis management including warning and evacuation systems: Heat Distribution Mapping':'Crisis mgmt inc warning & evac sys',\n           'Crisis management including warning and evacuation systems':'Crisis mgmt inc warning & evac sys',\n           'Hazard resistant infrastructure design and construction':'Hazard resistant infrastructure',\n          }\n\nriskadapt2['Actions'] = riskadapt2['Actions'].replace(actdict)\nriskadapt2['Hazard']=riskadapt2['Hazard'].astype('str')\nriskadapt2['Hazard']=riskadapt2['Hazard'].apply(lambda x: x.split(' >')[0])\nriskadapt2['Hazard']=riskadapt2['Hazard'].apply(lambda x: 'Extreme Weather' if 'Extreme' in x else x)\nriskadapt2['Hazard']=riskadapt2['Hazard'].replace({'Flood and sea level rise':'Flood & sea level rise'})\nhazids2 = pd.merge(hazids, riskadapt2, on=['Account Number', 'Hazard'])\ntimedict = {np.nan:'Unknown', 'Medium-term (2026-2050)':'Medium Term', 'Long-term (after 2050)':'Long Term',\n       'Immediately': 'Immediate', 'Short-term (by 2025)':'Short Term'}\nhazids2['Timing'] = hazids2['Timing'].map(timedict)\ntimewise = hazids2.groupby(['Timing','Hazard'])['Total Risk'].sum().reset_index()\ntimewise = timewise.pivot_table(values=['Total Risk'], index='Timing', columns='Hazard', aggfunc='sum')\ntimeorder = ['Immediate','Short Term','Medium Term','Long Term']\ntimewise = timewise.reindex(timeorder)\ntimewise.columns = timewise.columns.to_flat_index() #timewise.columns.get_level_values(1)\ntimewise.columns = [x[1] for x in timewise.columns]\ntimewise = timewise.fillna(0)\ntimewisestack = timewise.div(timewise.sum(1), axis=0)\nhazs = hazids2['Hazard'].unique()\nriskcolors = ['#3F5A7D', '#D2BB1B','#99B1D1', '#D2681B', '#673E0D', 'gray','#593567','#356738']\nfig, ax = plt.subplots(1,2, figsize=(15,5))\nax[0].bar(timeorder,timewisestack[hazs[0]], label=hazs[0], color = riskcolors[0])\nax[0].set_title('Relative Proportion of Risks wrt Time Horizon', fontsize=18)\ns = timewisestack[hazs[0]]\nfor i in range(1,8):\n    ax[0].bar(timeorder,timewisestack[hazs[i]], label=hazs[i], bottom=s, color=riskcolors[i])\n    s = s+timewisestack[hazs[i]]\nax[0].grid(True, 'major', linestyle='--', linewidth=.3)\nax[1].bar(timeorder,timewise[hazs[0]], label=hazs[0], color = riskcolors[0])\nfor i in range(1,8):\n    ax[1].bar(timeorder,timewise[hazs[i]], label=hazs[i], color = riskcolors[i])\nax[1].grid(True, 'major', linestyle='--', linewidth=.3)\nax[1].set_title('Total Risks Identified', fontsize=18)\nplt.legend(loc='upper left', bbox_to_anchor=(1,1), ncol=1)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"While we have a good understanding of immediate and short-term risks, most cities have not identified any significant long-term risks.\nOf the identified risks, while extreme weather poses a large risk in the immediate future, the threat of flood and sea-level rise looms large among the few long-term risks identified."},{"metadata":{},"cell_type":"markdown","source":"# Social Impact of Identified Climate Hazards"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"socheatmap = soconseq.groupby(['Hazard','Social Impact']).size().unstack('Social Impact', fill_value=0)\nfig, ax = plt.subplots(figsize=(15,5))\nsns.heatmap(socheatmap, ax=ax)\nplt.yticks(rotation=0, fontsize=20)\nax.xaxis.tick_top()\nax.xaxis.set_label_position('top')\nplt.xticks(rotation=70, ha='left', fontsize=15)\nplt.title('Social Impact of Climate Hazards', fontsize=20)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"nsocheatmap = socheatmap.div(socheatmap.sum(1), axis=0)\nfig, ax = plt.subplots(figsize=(15,5))\nsns.heatmap(nsocheatmap, ax=ax)\nplt.yticks(rotation=0, fontsize=20)\nax.xaxis.tick_top()\nax.xaxis.set_label_position('top')\nplt.xticks(rotation=70, ha='left', fontsize=15)\nplt.title('Normalized Social Impact of Climate Hazards', fontsize=20)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"On a global scale, the greatest impact of extreme weather and other hazards will be on vulnerable populations."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"vulnerability = vulnerable.groupby('Response Answer')['Total Risk'].sum()\nnames= list(vulnerability.index)\nsize=list(vulnerability.values)\nmy_circle=plt.Circle( (0,0), 0.7, color='white')\nplt.pie(size, labels=names, colors=Pastel1_7.hex_colors)\np=plt.gcf()\np.gca().add_artist(my_circle)\nplt.title('Population Vulnerable to Climate Risks', fontsize=18)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The sub-groups that would be most affect include the elderly, poor, children and youth."},{"metadata":{},"cell_type":"markdown","source":"# Risk Management KPIs"},{"metadata":{},"cell_type":"markdown","source":"Cities have identified various mitigation measures that they have undertaken to deals with the risks of climate change."},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"def f(Dataset):\n    df = hazids2[hazids2['Hazard']==Dataset]\n    figa1 = go.Figure(go.Parcats(dimensions=[{'label':'Hazard', 'values':df['Hazard']},\n                                        {'label':'Actions', 'values':df['Actions']},\n                                        {'label':'Status', 'values':df['Status']}], \n                            #line={'color': df['Status'], 'colorscale': colorscale},\n                               bundlecolors=True))\n    figa1 = go.FigureWidget(figa1)\n    figa1.update_layout()\n    widgets.HBox([figa1])\n    figa1.show()\n    return Dataset\ninteract(f, Dataset = widgets.RadioButtons(options=[hazs[i] for i in range(len(hazs))],description='Select:',disabled=False))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"figs = []\ncatar = 8*[[ 'Scoping','Pre-feasibility study', 'Pre-implementation', 'Implementation', 'Implementation complete', 'Operation', 'Monitoring and reporting']]\ncatar[7] = [ 'Scoping','Pre-feasibility study', 'Implementation', 'Implementation complete', 'Operation', 'Monitoring and reporting']\nhazids2['Total Risk Cat'] = pd.cut(hazids2['Total Risk'], bins=[0,5,10,15,20,25], labels=['Low','Medium Low','Medium','Medium High','High'])\nhazids2['Total Risk Cat']=hazids2['Total Risk Cat'].cat.add_categories(\"Unknown\").fillna(\"Unknown\")\nhazids2['Status'] = hazids2['Status'].replace('Implementation complete but not in operation','Implementation complete')\nriskmap = {'Low':1, 'Medium Low':2, 'Medium':3, 'Medium High':4, 'High':5, 'Unknown':0}\nhazids2['Total Risk Catc']=hazids2['Total Risk Cat'].astype('str').map(riskmap)\ncolorscale =px.colors.diverging.RdYlGn #['#488235','#CCC731','#EBE893','#CC8025','#CC2A25', 'gray']\nfor i in range(len(hazs)):\n    df = hazids2[hazids2['Hazard']==hazs[i]]\n    figa = go.Figure(go.Parcats(dimensions=[{'label':'Impact Timing', 'values':df['Timing'],  'categoryorder': 'array', 'categoryarray':['Unknown', 'Immediate','Short Term', 'Medium Term', 'Long Term']},\n                                        {'label':'Total Risk', 'values':df['Total Risk Cat'], 'categoryorder': 'array', 'categoryarray':['Unknown', 'Low','Medium Low', 'Medium', 'Medium High', 'High']},\n                                        {'label':'Mitigation Status', 'values':df['Status'], 'categoryorder': 'array', 'categoryarray':catar[i]}], \n                            line={'color': df['Total Risk Catc'], 'colorscale': colorscale},\n                               bundlecolors=True))\n    figa = go.FigureWidget(figa)\n    figa.layout.title = hazs[i]\n    figa.update_layout(\n    margin=dict(l=50, r=100, t=50, b=20),\n    paper_bgcolor=\"#EBEDEF\",autosize=False,width=800,height=300)\n    figa.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"For defining a risk management KPI, we need to take into account the magnitude of the present risk, as well as the status of the mitigation actions. Cities that are pro-active and effective in risk management should have implemented mitigation measures that are operating well, and being monitored and reported transparently on a regular basis.\nWe define a Status Score scale to rate the mitigation measures from 0 to 1, where 0 implies no mitigation actions identified, and 1 is assigned to measures that are the most a city can do - Monitor and Report the implemented mitigation plan."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"Status_scores = {'Unknown':0, 'Scoping':0.1, 'Pre-feasibility study':0.2, 'Pre-implementation':0.25,\n                 'Implementation':0.5, 'Implementation complete':0.75, 'Operation':0.9, 'Monitoring and reporting':1}\ntix = [*Status_scores.values()]\na = np.array([tix])\nfiga = plt.figure(figsize=(15, 1))\nimg = plt.imshow(a, cmap=\"RdYlGn\")\nplt.gca().set_visible(False)\ncax = plt.axes([0.1, 0.2, 0.8, 0.6])\ncbar = plt.colorbar(orientation=\"horizontal\", cax=cax, ticks=tix)\nfiga.axes[1].set_xticklabels(list(Status_scores.keys()))\nfiga.axes[1].xaxis.tick_top()\nplt.xticks(rotation=20, ha='left', fontsize=16)\nplt.title('Implementation Status Score Scale', fontsize = 20)\nplt.show()\nprint(tabulate(Status_scores.items(), headers=['Status','Status Score'], tablefmt=\"grid\"))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"To take into account the magnitude of the risks, each Status Score is weighted by the total risk it covers, so that actions mitigating greater risks score higher than actions mitigating lower risks. This is to ensure that cities that give primacy to high risk items are rewarded commensurately with higher KPI scores.\nTaking all the above into account, we arrive at a Risk Management Index as calculated below -"},{"metadata":{},"cell_type":"markdown","source":"#                 Risk Management Index\n# RMI=         $ \\frac{\\sum_{i=1}^nStatus Score_i*Total Risk_i}{\\sum_{i=1}^nTotal Risk_i} $"},{"metadata":{},"cell_type":"markdown","source":"This is calculated for immediate risks.\n\nTo take into account readiness to face future risks, we define a **Risk Preparedness Index**, which uses the same formula as the Risk Management Index, but is calculated for identified future risks. "},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"hazids2['Status_score'] = hazids2['Status'].map(Status_scores)\nhazids2['Risk Adaptation'] = hazids2['Total Risk']*hazids2['Status_score']\nrmi = {}\nhazids2imm = hazids2[hazids2['Timing']=='Immediate']\nfor i in range(len(hazs)):\n    df = hazids2imm[hazids2imm['Hazard']==hazs[i]]\n    dfacct = df.groupby('Account Number')\n    dfr = pd.DataFrame(dfacct['Risk Adaptation'].sum()/dfacct['Total Risk'].sum(), columns=['Risk Adaptation'])   \n    dfr['Org'] = dfr.index.map(acctorg)\n    rmi[hazs[i]]=dfr.sort_values(by='Risk Adaptation', ascending=False)\n    \nrpi = {}\nhazids2fut = hazids2[hazids2['Timing']!='Immediate']\nfor i in range(len(hazs)):\n    df = hazids2fut[hazids2fut['Hazard']==hazs[i]]\n    dfacct = df.groupby('Account Number')\n    dfr = pd.DataFrame(dfacct['Risk Adaptation'].sum()/dfacct['Total Risk'].sum(), columns=['Risk Adaptation'])   \n    dfr['Org'] = dfr.index.map(acctorg)\n    rpi[hazs[i]]=dfr.sort_values(by='Risk Adaptation', ascending=False)\nfig, ax = plt.subplots(4,2, figsize=(20,10), constrained_layout=True)\nfor i in range(len(hazs)):\n    ax[int(i/2),i%2].barh(rmi[hazs[i]].head()['Org'], rmi[hazs[i]].head()['Risk Adaptation'], color='#3D6034')\n    ax[int(i/2),i%2].invert_yaxis()\n    ax[int(i/2),i%2].set_title(hazs[i])\n    ax[int(i/2),i%2].set_xlim(right=1)\n    ax[int(i/2),i%2].xaxis.grid(True, which='major', linestyle='-', linewidth=0.15)\n    ax[int(i/2),i%2].yaxis.grid(True, which='major', linestyle='-', linewidth=0.1)\n    ax[int(i/2),i%2].tick_params(axis='y', which='major', labelsize=15)\n\n\n#plt.tight_layout()\nfig.suptitle('Top Cities in Risk Management', fontsize=25)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"fig, ax = plt.subplots(4,2, figsize=(20,10), constrained_layout=True)\nfor i in range(len(hazs)):\n    ax[int(i/2),i%2].barh(rpi[hazs[i]].head()['Org'], rpi[hazs[i]].head()['Risk Adaptation'], color='#3D6034')\n    ax[int(i/2),i%2].invert_yaxis()\n    ax[int(i/2),i%2].set_title(hazs[i])\n    ax[int(i/2),i%2].set_xlim(right=1)\n    ax[int(i/2),i%2].xaxis.grid(True, which='major', linestyle='-', linewidth=0.15)\n    ax[int(i/2),i%2].yaxis.grid(True, which='major', linestyle='-', linewidth=0.1)\n\n#plt.tight_layout()\nfig.suptitle('Top Cities in Risk Preparedness', fontsize=25)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Emissions KPIs"},{"metadata":{},"cell_type":"markdown","source":"According to the Global Protocol for Community-Scale Greenhouse Gas Emission Inventories (GPC), cities are responsible for more than 70 percent of global energy-related carbon dioxide emissions, and present the single greatest opportunity for tackling climate change. It defines a standard protocol for measuring GHG emissions."},{"metadata":{},"cell_type":"markdown","source":"Emissions are classified based on scope, as well as the source. The scope categorizes emissions into 3 classes based on their generation and usage with respect to city boundaries. The major sources of include Stationary Energy generation, Transportation, Waste, Industry, and Agriculture."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"emcats = ['Stat_energy-1', 'Stat_energy-2', 'Stat_energy-3', 'Stat_energy2grid-1','Transport-1', 'Transport-2', 'Transport-3', 'Waste-1', 'Waste-3', 'Wasteout-1',\n         'Industry-1', 'Agri-1', 'Total-1', 'Total-2', 'Total-3', 'Totalbasic', 'Totalbasic+']\nemissions, citicount = {}, {}\nfor i in range(len(emcats)):\n    emi = citires19[(citires19['Question Number']=='4.6b') & (citires19['Column Number']==1) & (citires19['Row Number']==i+1)]\n    emires = emi[emi['Response Answer']!='Question not applicable']\n    emissions[emcats[i]]= emires\n    citicount[emcats[i]] = emires['Response Answer'].count()\nemscope1exgrid = citires19[(citires19['Question Number']=='4.6c') & (citires19['Column Number']==1)]\nemscope1ingrid = citires19[(citires19['Question Number']=='4.6c') & (citires19['Column Number']==3)]\nemscope1tot = citires19[(citires19['Question Number']=='4.6c') & (citires19['Column Number']==5)]","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"fig, ax = plt.subplots(1,1,figsize=(15,4))\nax.xaxis.grid(True, which='major', linestyle='-', linewidth=0.15)\nax.yaxis.grid(True, which='major', linestyle='-', linewidth=0.1)\nplt.xticks(rotation=70, ha='right')\nplt.bar(list(citicount.keys()), list(citicount.values()))\nplt.title('Number of Responses Received per Category')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"We have emissions declaration data from fewer than 140 cities in all categories."},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"my_circle=plt.Circle( (0,0), 0.5, color='white')\nx = emcats[:-5]\ny = [emissions[cat]['Response Answer'].astype('float').mean() for cat in emcats[:-5]]\nplt.gcf().gca().add_artist(my_circle)\ncolors = Pastel1_7.hex_colors\npatches, texts = plt.pie(y, colors=colors, startangle=90, radius=1.2)\nlabels = x\nplt.legend(patches, labels, loc='right center', bbox_to_anchor=(1, 1.),fontsize=10)\nplt.title('Breakdown of Emissions by Scope and Source')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Energy Generation accounts for over 50% of the total emissions in all cities. A simple KPI for measuring the carbon footprint of a city would be **Carbon Emissions per Capita**. However, as the data per city is not widely available, we can use NOx emissions as a proxy. NOx is emitted along with CO2 in vehicular exhausts, with the NOx/CO2 ratio determined by the Emissions Standard of the country.\n\nThe Sentinel-5 Precursor satellite has an onboard sensor - Tropomi (TROPOspheric Monitoring Instrument), which measures NOx. Data is available on [Earth Engine](https://developers.google.com/earth-engine/datasets/catalog/COPERNICUS_S5P_OFFL_L3_NO2)."},{"metadata":{},"cell_type":"markdown","source":"NOX CONCENTRATIONS WORLDWIDE"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"column = 'tropospheric_NO2_column_number_density'\ndataset = \"COPERNICUS/S5P/OFFL/L3_NO2\"\nbegin_date = '2019-01-01'; end_date = '2019-12-31'\nminval = 0; maxval=.0002\nlatitude = 60; longitude = 25.9\nzoom = 2\nvis_params = {'min':minval, 'max':maxval,'palette': ['green', 'cyan', 'blue', 'yellow', 'orange', 'red']}\nmy_map = folium.Map(location=[latitude,longitude], zoom_start=zoom, height=500)\ns5p = ee.ImageCollection(dataset).filterDate(begin_date, end_date)\nime = s5p.select(column).reduce(ee.Reducer.mean())\nmap_id = ime.getMapId(vis_params)\nfolium.raster_layers.TileLayer(\n    tiles = map_id['tile_fetcher'].url_format,\n    attr = 'Map Data &copy; <a href=\"https://earthengine.google.com/\">Google Earth Engine</a>',\n    overlay = True,control = True).add_to(my_map)\ndisplay(my_map)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"(Note: Slow Loading Graphs)"},{"metadata":{},"cell_type":"markdown","source":"If the kernel has not been run in a couple of days, the above Earth Engine layer may not show, so below is a representative layer downloaded and saved offline as a [dataset](https://www.kaggle.com/parselt/noxmap)."},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"im = '../input/noxmap/ime.tif'\nband = rio.open(im).read()\nband10 = band[0]*10000\nband10 = np.where(band10>1,1,band10)\nband10 = np.where(np.isnan(band10), 0, band10)\nbounds = [[90, -180], [-90, 180]]\nm = folium.Map(height=300, width=500)\nfolium.raster_layers.ImageOverlay(image=band10,colormap=lambda x: (1, 0, 0, x), bounds=bounds).add_to(m)\ndisplay(m)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**NOx METHODOLOGY**\n\nThe city is assumed to be a square of the same area as reported in the disclosure files. Tropospheric NOx emissions for a city is extracted for the given area, and averaged over all of 2019 in the [City NO2 Emissions](https://www.kaggle.com/parselt/city-no2-emissions) notebook.\n**NOx per Capita** is calculated as a standard emissions KPI."},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"#nox = pd.read_csv('../input/city-no2-emissions/citid19.csv')\nnox = pd.merge(nox, population, on='Account Number')\nnox['no2']=nox['no2'].astype('float')\nnox['Popn']=nox['Popn'].astype('float')\nnox['Nox_percapita'] = nox['no2']/nox['Popn']*10**10\nnox['Org']= nox['Account Number'].map(acctorg)\nnox = nox[nox['Nox_percapita']>0]\nnox = nox.sort_values(by='Nox_percapita', ascending=False)\nfig, ax = plt.subplots(1,2, figsize=(15,5), constrained_layout=True)\nax[0].barh(nox.head(10)['Org'], nox.head(10)['Nox_percapita'])\nax[0].invert_yaxis()\nax[1].barh(nox.tail(10)['Org'], nox.tail(10)['Nox_percapita'])\nax[0].set_title('Highest Nox per Capita')\nax[1].set_title('Lowest Nox per Capita')\nfor ax in [ax[0], ax[1]]:\n    ax.xaxis.grid(True, which='major', linestyle='-', linewidth=0.15)\n    ax.yaxis.grid(True, which='major', linestyle='-', linewidth=0.1)\n    ax.tick_params(axis='y', which='major', labelsize=15)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"emitreduct = citires19[(citires19['Question Number']=='5.0a')]\nemitreductsec = emitreduct[emitreduct['Column Number']==1].rename(columns={'Response Answer':'Sector'})\nemitbaseyr = emitreduct[emitreduct['Column Number']==4].rename(columns={'Response Answer':'Base Year'})\nemitbase = emitreduct[emitreduct['Column Number']==6].rename(columns={'Response Answer':'Base Emissions'})\nemittgt = emitreduct[emitreduct['Column Number']==7].rename(columns={'Response Answer':'Reduction%'})\ntgtyr = emitreduct[emitreduct['Column Number']==8].rename(columns={'Response Answer':'Year'})\ntgtabs = emitreduct[emitreduct['Column Number']==9].rename(columns={'Response Answer':'Abs Tgt'})\ntgt2day = emitreduct[emitreduct['Column Number']==10].rename(columns={'Response Answer':'Tgt2day'})\nemitbase = pd.merge(emitreductsec, emitbase, on=['Account Number', 'Row Number'])[['Account Number', 'Sector','Row Number', 'Base Emissions']]\nemittgt = pd.merge(emitbase, emittgt, on=['Account Number', 'Row Number'])[['Account Number','Row Number', 'Sector' ,'Base Emissions', 'Reduction%']]\nemittgt = pd.merge(emittgt, emitbaseyr, on=['Account Number', 'Row Number'])[['Account Number','Row Number', 'Sector' ,'Base Emissions', 'Base Year','Reduction%']]\nemittgt = pd.merge(emittgt, tgtyr, on=['Account Number', 'Row Number'])[['Account Number','Row Number', 'Sector' ,'Base Emissions', 'Base Year','Reduction%', 'Year']]\nemittgt = pd.merge(emittgt, tgtabs, on=['Account Number', 'Row Number'])[['Account Number','Row Number', 'Sector' ,'Base Emissions', 'Base Year','Reduction%', 'Abs Tgt', 'Year']]\nemittgt = pd.merge(emittgt, tgt2day, on=['Account Number', 'Row Number'])[['Account Number','Row Number', 'Sector' ,'Base Emissions', 'Base Year','Reduction%', 'Abs Tgt', 'Year','Tgt2day']]\nemittgt[['Base Emissions', 'Reduction%','Abs Tgt','Tgt2day']]=emittgt[['Base Emissions', 'Reduction%','Abs Tgt','Tgt2day']].astype('float')\nemittgt[['Year', 'Base Year']]=emittgt[['Year', 'Base Year']].astype('float')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"We can calculate a GHG Reduction Commitment Score which takes into account the urgency with which cities have promised to act on climate change. It measures the rate at which cities are promising to reduce emissions.\n\n# Commitment Score = $ \\frac{Emissions \\enspace Reduction \\%}{No \\enspace of\\enspace Years} $\n\nWe can also calculate a Proportional Commitment Score, which takes into account the current level of emissions per capita (High, Medium, Low), so that cities with high emissions need to commit to a greater rate of reduction to have the same score as cities that are already low on emissions.\n\n# Proportional Commitment Score = $ \\frac{Emissions \\enspace Reduction \\%*Emissions\\enspace Penalty}{Number\\enspace of\\enspace Years} $\n\n\nWe can also calculate a performance score, comparing the declared reduction rate so far with respect to committed reduction rate over the entire period of commitment. Cities that are on-track or doing better than committed reductions have a higher score.\n\n# Performance Score = $ \\frac{Actual \\enspace Emissions \\enspace Reduction \\enspace Rate}{Committed\\enspace Reduction\\enspace Rate} $"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"pmap = {'Low':1, 'Medium':0.9, 'High':0.8}\nemittgt = pd.merge(emittgt, population, on='Account Number')\nemittgt['Emissions pc'] = emittgt['Base Emissions']/emittgt['Popn'].astype('float')\nemittgt['Emissions pc'] = pd.qcut(emittgt['Emissions pc'],3,labels=['Low','Medium','High'])\nemittgt['Penalty'] = emittgt['Emissions pc'].map(pmap).astype('float')\nemittgt['CS'] = (emittgt['Reduction%'])/(emittgt['Year']-emittgt['Base Year']).values\nemittgt['PCS'] = (emittgt['Reduction%']* emittgt['Penalty'])/(emittgt['Year']-emittgt['Base Year']).values\nemittgt['PS'] = (emittgt['Tgt2day']/(2020-emittgt['Base Year']))*emittgt['Penalty']/ emittgt['CS']\nemittgt['Org']=emittgt['Account Number'].map(acctorg)\nemittgt = emittgt.replace([np.inf, -np.inf], np.nan)\nemittgt = emittgt.dropna()\nfig, ax = plt.subplots(1,2, figsize=(15,5), constrained_layout=True)\nemittgt = emittgt.sort_values(by='PCS', ascending=False)\nax[0].barh(emittgt.head(10)['Org'], emittgt.head(10)['CS'])\nax[0].invert_yaxis()\nemittgt = emittgt.sort_values(by='PS', ascending=False)\nax[1].barh(emittgt.head(10)['Org'], emittgt.head(10)['PS'])\nax[0].set_title('Top Cities by Commitment', fontsize=20)\nax[1].set_title('Best Tracking Cities', fontsize=20)\nax[1].invert_yaxis()\nfor ax in [ax[0], ax[1]]:\n    ax.xaxis.grid(True, which='major', linestyle='-', linewidth=0.15)\n    ax.yaxis.grid(True, which='major', linestyle='-', linewidth=0.1)\n    ax.tick_params(axis='y', which='major', labelsize=15)\n","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}