{"cells":[{"metadata":{},"cell_type":"markdown","source":"#
Do you have a climate plan ?
\n
\n Photo by Karsten Würth on Unsplash"},{"metadata":{},"cell_type":"markdown","source":"## Table of Contents\n1. [Introduction](#1)\n2. [Building indicators](#2)
\n 2.1 [Indicators for corporation](#2.1)
\n 2.2 [Indicators for cities](#2.2)
\n3. [KPI validation](#3)
\n4. [Discussion](#4)
\n 4.1 [Intersection between cities and corporations](#4.1)
\n 4.2 [Intersection between environnemental and social issues](#4.2)
\n5. [Conclusion](#5)
"},{"metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"trusted":true},"cell_type":"code","source":"# Importation of the library\nimport numpy as np\nimport pandas as pd\nimport matplotlib.pyplot as plt\nfrom matplotlib_venn import venn2\nimport plotly_express as px\nimport seaborn as sns\nfrom sklearn.preprocessing import QuantileTransformer\nfrom plotly.offline import init_notebook_mode;\ninit_notebook_mode(connected=True)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"# Loading databases\n\n# A database of the cities contained in the CDP data with corrected population and location\nCities_2020 = pd.read_csv(\"../input/cities-2020-for-cdp/Cities_2020.csv\")\nCities_2020[['Account Number', 'Organization', 'City','Country', 'CDP Region', 'Population','City Location', 'longitude', 'latitude']]\n\n# Responses to the survey by cities\nCities_Responses_2020 = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Cities/Cities Responses/2020_Full_Cities_Dataset.csv\")\nCities_Responses_2020[['Account Number','Organization', 'Country', 'CDP Region','Question Number', 'Column Number','Row Number', 'Response Answer', 'Comments']]\n\n# Corporations that responded to the Climate Survey\nCorporation_climate_2020 = pd.read_csv('../input/cdp-unlocking-climate-solutions/Corporations/Corporations Disclosing/Climate Change/2020_Corporates_Disclosing_to_CDP_Climate_Change.csv')\nCorporation_climate_2020 = Corporation_climate_2020[['account_number', 'organization', 'country']]\n\n# Corporations that responded to the Water Survey\nCorporation_water_2020 = pd.read_csv('../input/cdp-unlocking-climate-solutions/Corporations/Corporations Disclosing/Water Security/2020_Corporates_Disclosing_to_CDP_Water_Security.csv')\nCorporation_water_2020 = Corporation_water_2020[['account_number', 'organization', 'country']]\n\n# Corporate Responses to the Climate Survey\nCorporation_Responses_Climate_2020 = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Corporations/Corporations Responses/Climate Change/2020_Full_Climate_Change_Dataset.csv\", low_memory=False)\nCorporation_Responses_Climate_2020 = Corporation_Responses_Climate_2020[['account_number', 'organization', 'question_number','column_number', 'page_name','row_number','response_value', 'comments']]\n\n# Corporate Responses to the Water Survey\nCorporation_Responses_Water_2020 = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Corporations/Corporations Responses/Water Security/2020_Full_Water_Security_Dataset.csv\", low_memory=False)\nCorporation_Responses_Water_2020 = Corporation_Responses_Water_2020[['account_number', 'organization', 'question_number','column_number', 'page_name','row_number','response_value', 'comments']]\n\n# Vulnerability indicators from World Bank\nVulnerability = pd.read_csv(\"../input/environmentequity-starterpack/Environment-Equity Datasets/Social Equity/Vulnerability/Climate Vulnerability and Readiness 2015.csv\")\nVulnerability.columns = ['code_3digit', 'Population living below the national income poverty line','Climate Risk Score', 'Vulnerability score', 'Readiness score','Link to climate risk and adaptation profile', 'Droughts', 'Storms','Epidemics', 'Landslides', 'Floods', 'Wildfires']\n\n# Country iso code\ncountry_code = pd.read_csv(\"../input/country-code/country_code.csv\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"# Functions used to transform categorial data into numerical data\ndef engagement_water(x):\n total = 0\n try :\n if ('Yes, our suppliers') in x :\n total = total + 2\n if (\"Yes, our customers or other value chain partners\") in x:\n total = total + 2\n if (\"No, not currently but we intend to within the next two years\") in x :\n total = total + 1\n if x == 'No, we do not engage with our value chain on water' or x== 'No Response':\n total = 0\n return total \n except :\n return 0\n\ndef W14a_proportion(x):\n try :\n if x == 'None and we do not plan to request this from suppliers' or x == 'Unknown':\n return 0\n if x == 'None currently, but we plan to request this within the next two years':\n return 1\n if x == 'Less than 1%':\n return 2\n if x == '1-25':\n return 3\n if x == '26-50':\n return 4\n if x == '51-75':\n return 5\n if x == '76-100':\n return 6\n else:\n return 0\n except :\n return 0\n \ndef engagement_climate(x):\n total = 0\n try :\n if ('Yes, our suppliers') in x :\n total = total + 1\n if (\"Yes, our customers\") in x:\n total = total + 1\n if (\"Yes, our investee companies [Financial services only]\") in x :\n total = total + 1\n if (\"Yes, other partners in the value chain\") in x:\n total = total + 1 \n if x == 'No, we do not engage' or x== 'No Response':\n total = 0\n return total \n except :\n return 0 \n \ndef convert(x):\n try :\n if x == 'Yes':\n return 3\n if x == 'In progress':\n return 2\n if x == 'Intending to incorporate in the next 2 years' or x == 'Intending to undertake in the next 2 years':\n return 1\n if x == 'Not intending to incorporate' or x == 'Not intending to undertake':\n return 0\n if x == 'Do not know':\n return 0\n else:\n return 0 \n except :\n return x\ndef convert_water(x):\n try :\n if x == 'Yes':\n return 3\n if x == 'In progress':\n return 2\n if x == 'Not intending to incorporate' or x == 'Not intending to undertake':\n return 1\n if x == 'Do not know':\n return 0\n else:\n return 0 \n except :\n return x\ndef convert_transport(x):\n try :\n if x == 'Yes':\n return 1\n if x == 'No':\n return 0\n if x == 'Do not know':\n return 0\n else:\n return 0 \n except :\n return x\n\ndef normalize(df):\n return (df - df.min()) / (df.max() - df.min())\n\nscaler = QuantileTransformer(n_quantiles=20)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# 1. Introduction\n\nThe [CDP](https://www.cdp.net/en) is a non-profit, charitable organization that helps corporations and cities to take action in response to the changing climate.\nEach year, it brings together a set of data on the environmental measurements of thousands of cities and corporations.\nThrough the [CDP: Unlocking Climate Solutions competition](https://www.kaggle.com/c/cdp-unlocking-climate-solutions), the Kaggle community is asked to develop a methodology for calculating Key Performance Indicators (KPIs) related to environmental and social issues.\n\nIn this notebook, we will attempt to develop a KPI to assess the readiness of cities and corporation to deal with environmental risks and social issues.\n\nAre cities and corporations aware of future environmental and social issues? Are they well prepared to deal with them?"},{"metadata":{},"cell_type":"markdown","source":"# 2. Building indicators\nAll performance indicators will be normalized so that they can be compared with each other.\n\nWe introduce a first indicator in order to see what proportion of the survey is completed.\nFor all cities and corporations, the communication or transparency indicator is calculated as follows with i the citie or the corporation and j the survey.\n\n$$KPI_{Communication_{(i,j)}} = 1 - \\dfrac{\\sum Nan Values_{(i,j)}}{\\sum rows_{(i,j)}}$$\n\nThe communication indicator takes into account the uncompleted lines out of the total lines that the city or corporation has access to in the survey."},{"metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"trusted":true},"cell_type":"code","source":"# KPI_Communication_and_transparency for cities\n\nCities_Transparency=Cities_Responses_2020[[\"Account Number\", \"Response Answer\"]]\nCities_Transparency['Transparency'] = Cities_Transparency.isnull().sum(axis = 1)\nCities_Transparency['N_row'] = 1\nCities_Transparency = Cities_Transparency.groupby('Account Number').sum()\nCities_Transparency['KPI_Communication_and_transparency_cities'] = 1 - Cities_Transparency[\"Transparency\"] / Cities_Transparency['N_row']\nCities_Transparency=Cities_Transparency[['KPI_Communication_and_transparency_cities']]\nCities_2020 = pd.merge(Cities_2020, Cities_Transparency, how = 'outer', on = ['Account Number'])\n\n# KPI_Communication_and_transparency for corporation on climat\nCorporation_Transparency_climate=Corporation_Responses_Climate_2020[[\"account_number\", \"response_value\"]]\nCorporation_Transparency_climate['Transparency'] = Corporation_Transparency_climate.isnull().sum(axis = 1)\nCorporation_Transparency_climate['N_row'] = 1\nCorporation_Transparency_climate = Corporation_Transparency_climate.groupby('account_number').sum()\nCorporation_Transparency_climate['KPI_Communication_and_transparency_climate'] = 1 - Corporation_Transparency_climate[\"Transparency\"] / Corporation_Transparency_climate['N_row']\nCorporation_Transparency_climate=Corporation_Transparency_climate[['KPI_Communication_and_transparency_climate']]\nCorporation_climate_2020 = pd.merge(Corporation_climate_2020, Corporation_Transparency_climate, how = 'outer', on = ['account_number'])\n\n\n# KPI_Communication_and_transparency for corporation on climat\nCorporation_Transparency_water=Corporation_Responses_Water_2020[[\"account_number\", \"response_value\"]]\nCorporation_Transparency_water['Transparency'] = Corporation_Transparency_water.isnull().sum(axis = 1)\nCorporation_Transparency_water['N_row'] = 1\nCorporation_Transparency_water = Corporation_Transparency_water.groupby('account_number').sum()\nCorporation_Transparency_water['KPI_Communication_and_transparency_water'] = 1 - Corporation_Transparency_water[\"Transparency\"] / Corporation_Transparency_water['N_row']\nCorporation_Transparency_water=Corporation_Transparency_water[['KPI_Communication_and_transparency_water']]\nCorporation_water_2020 = pd.merge(Corporation_water_2020, Corporation_Transparency_water, how = 'outer', on = ['account_number'])\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The distribution of the indicator is as follows. The questionnaire is generally well completed for all cities. There are more disparities for corporations"},{"metadata":{"_kg_hide-input":true,"_kg_hide-output":false,"trusted":true},"cell_type":"code","source":"fig, axes = plt.subplots(nrows=1, ncols=3, figsize=(18,6))\nfig.suptitle('Communication and transparency of cities and corporations')\n\nsns.violinplot(ax=axes[0], y=Cities_2020['KPI_Communication_and_transparency_cities'], color = '#17b978')\naxes[0].set_title('Cities')\naxes[0].set_ylabel(\"Communication and Transparency\")\n\nsns.violinplot(ax=axes[1], y=Corporation_climate_2020['KPI_Communication_and_transparency_climate'], color = '#ff8264')\naxes[1].set_title('Corporation for climate')\naxes[1].set_ylabel(\"Communication and Transparency\")\n\n\nsns.violinplot(ax=axes[2], y=Corporation_water_2020['KPI_Communication_and_transparency_water'], color = '#53AFFF')\naxes[2].set_title('Corporation for water')\naxes[2].set_ylabel(\"Communication and Transparency\")\n\nShow = False","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# 2.1 Indicators for corporation\n\n985 corporations responded to the climate survey while 295 responded to the water survey.
\n
\nWhat proportion of corporations responded to both surveys?"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"Concat_df = pd.concat([Corporation_climate_2020, Corporation_water_2020])\nGroup_climate = Corporation_climate_2020.shape[0]\nGroup_water = Corporation_water_2020.shape[0]\nGroup_climate_and_water = Group_climate + Group_water - len(Concat_df['account_number'].unique())\ndel Concat_df\nvenn2(subsets = (Group_climate,Group_water,Group_climate_and_water), set_labels = ('Climate', 'Water'))\nplt.title(\"Number of corporations that completed the survey\")\n\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"279 corporations completed both questionnaires"},{"metadata":{},"cell_type":"markdown","source":"# Indicators on climate\nTo build an indicator with the climate responses given by corporations, we are interested in the following question :\n\n(C12.1) Do you engage with your value chain on climate-related issues?\n\nSeveral options can be selected among :\n* Yes, our suppliers\n* Yes, our customers\n* Yes, our investee companies [Financial services only]\n* Yes, other partners in the value chain\n* No, we do not engage\n\nThe indicator value is incremented by 1 for each option selected. Except for the answer \"No, we do not engage\", which is worth 0.\n\n$$KPI_{C12.1} = \\sum Options $$\n\nWe notice that several answers contain the words \"plan\",\"engagement\" or \"education\" in the choice of their options.\nFor each of these three words, an indicator is constructed based on the number of occurrences of these words in the answers to the questionnaire.\n\n$$KPI_{plan} = \\sum Occurence(\"plan\") $$\n$$KPI_{education} = \\sum Occurence(\"education\") $$\n$$KPI_{engagement} = \\sum Occurence(\"engagement\") $$\n\nAn overall indicator is constructed as the weighted sum of these three indicators.\n$$KPI_{Words} = \\dfrac{KPI_{plan} + KPI_{education} + KPI_{engagement}}{3}$$\n\nThe overall climate performance indicator for corporations is constructed as follows :\n$$KPI_{climate} = \\dfrac{KPI_{Communication_{(i,climate)}} + KPI_{C12.1} + KPI_{Words}}{3}$$"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"C121 = Corporation_Responses_Climate_2020[Corporation_Responses_Climate_2020['question_number'] == 'C12.1']\nC121 = C121[['account_number','response_value']]\nC121['response_value'] = C121['response_value'].apply(engagement_climate)\nC121.columns = ['account_number', 'KPI C12.1']\nCorporation_climate_2020 = Corporation_climate_2020.merge(C121, on = ['account_number'])\nCorporation_climate_2020['KPI C12.1'] = normalize(Corporation_climate_2020['KPI C12.1'])\n\nengagement_climate = Corporation_Responses_Climate_2020[Corporation_Responses_Climate_2020[\"response_value\"].str.contains('engagement') == True]\ndf_engagement_climate = pd.DataFrame(engagement_climate.groupby('account_number').size())\ndf_engagement_climate.columns=['KPI engagement']\nCorporation_climate_2020 = pd.merge(Corporation_climate_2020, df_engagement_climate, how = 'outer', on = ['account_number'])\nCorporation_climate_2020['KPI engagement'] = Corporation_climate_2020['KPI engagement'].fillna(0)\nCorporation_climate_2020['KPI engagement'] = scaler.fit_transform(Corporation_climate_2020['KPI engagement'].values.reshape(-1,1))\n\nplan_climate = Corporation_Responses_Climate_2020[Corporation_Responses_Climate_2020[\"response_value\"].str.contains('plan') == True]\ndf_plan_climate = pd.DataFrame(plan_climate.groupby('account_number').size())\ndf_plan_climate.columns=['KPI plan']\nCorporation_climate_2020 = pd.merge(Corporation_climate_2020, df_plan_climate, how = 'outer', on = ['account_number'])\nCorporation_climate_2020['KPI plan'] = Corporation_climate_2020['KPI plan'].fillna(0)\nCorporation_climate_2020['KPI plan'] = scaler.fit_transform(Corporation_climate_2020['KPI plan'].values.reshape(-1,1))\n\neducation_climate = Corporation_Responses_Climate_2020[Corporation_Responses_Climate_2020[\"response_value\"].str.contains('education') == True]\ndf_education_climate = pd.DataFrame(education_climate.groupby('account_number').size())\ndf_education_climate.columns=['KPI education']\nCorporation_climate_2020 = pd.merge(Corporation_climate_2020, df_education_climate, how = 'outer', on = ['account_number'])\nCorporation_climate_2020['KPI education'] = Corporation_climate_2020['KPI education'].fillna(0)\nCorporation_climate_2020['KPI education'] = scaler.fit_transform(Corporation_climate_2020['KPI education'].values.reshape(-1,1))\n\nCorporation_climate_2020['KPI Words'] = (Corporation_climate_2020['KPI engagement'] + Corporation_climate_2020['KPI plan'] + Corporation_climate_2020['KPI education'])/3\nCorporation_climate_2020['KPI climate'] = (Corporation_climate_2020['KPI_Communication_and_transparency_climate'] + Corporation_climate_2020['KPI C12.1'] + Corporation_climate_2020['KPI Words'])/3\n\nfig, axes = plt.subplots(nrows=1, ncols=3, figsize=(18,6))\nfig.suptitle('KPI for corporation on climate')\n\nsns.violinplot(ax=axes[0], y=Corporation_climate_2020['KPI C12.1'], color = '#f8b500')\n\nsns.violinplot(ax=axes[1], y=Corporation_climate_2020['KPI Words'], color = '#17b978')\n\nsns.violinplot(ax=axes[2], y=Corporation_climate_2020['KPI climate'], color = '#ff8264')\n\nShow = False","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Indicators on water security\nFor the water survey, KPIs are created in the same way with the following questions :\n* (W1.4) Do you engage with your value chain on water-related issues?\n* (W1.4a) What proportion of suppliers do you request to report on their water use, risks and/or management information and what proportion of your procurement spend does this represent?\n\nAs well as the performance indicator calculated on the number of occurrences of the words \"plan\" and \"engagement\" and \"education\".\n\nBy transforming categorical data into numerical data, the following distributions for KPIs are obtained :"},{"metadata":{"_kg_hide-input":true,"_kg_hide-output":false,"trusted":true},"cell_type":"code","source":"W14 = Corporation_Responses_Water_2020[Corporation_Responses_Water_2020['question_number'] == 'W1.4']\nW14 = W14[['account_number','response_value']]\nW14['response_value'] = W14['response_value'].apply(engagement_water)\nW14.columns = ['account_number', 'W1.4']\nCorporation_water_2020 = Corporation_water_2020.merge(W14, on = ['account_number'])\nCorporation_water_2020['W1.4'] = normalize(Corporation_water_2020['W1.4'])\n\nW14a = Corporation_Responses_Water_2020[Corporation_Responses_Water_2020['question_number'] == 'W1.4a']\nW14a1 = W14a[W14a['column_number'] == 1.0]\nW14a1 = W14a1[['account_number','response_value']]\nW14a1['response_value'] = W14a1['response_value'].apply(W14a_proportion)\nW14a1.columns = ['account_number', '% of suppliers']\nCorporation_water_2020 = Corporation_water_2020.merge(W14a1, on = ['account_number'])\nCorporation_water_2020['% of suppliers'] = normalize(Corporation_water_2020['% of suppliers'])\n\nW14a2 = W14a[W14a['column_number'] == 2.0]\nW14a2 = W14a2[['account_number','response_value']]\nW14a2['response_value'] = W14a2['response_value'].apply(W14a_proportion)\nW14a2.columns = ['account_number', '% of total procurement spend']\nCorporation_water_2020 = Corporation_water_2020.merge(W14a2, on = ['account_number'])\nCorporation_water_2020['% of total procurement spend'] = normalize(Corporation_water_2020['% of total procurement spend'])\n\nengagement_water = Corporation_Responses_Water_2020[Corporation_Responses_Water_2020[\"response_value\"].str.contains('engagement') == True]\ndf_engagement_water = pd.DataFrame(engagement_water.groupby('account_number').size())\ndf_engagement_water.columns=['KPI engagement']\nCorporation_water_2020 = pd.merge(Corporation_water_2020, df_engagement_water, how = 'outer', on = ['account_number'])\nCorporation_water_2020['KPI engagement'] = Corporation_water_2020['KPI engagement'].fillna(0)\nCorporation_water_2020['KPI engagement'] = scaler.fit_transform(Corporation_water_2020['KPI engagement'].values.reshape(-1,1))\n\nplan_water = Corporation_Responses_Water_2020[Corporation_Responses_Water_2020[\"response_value\"].str.contains('plan') == True]\ndf_plan_water = pd.DataFrame(plan_water.groupby('account_number').size())\ndf_plan_water.columns=['KPI plan']\nCorporation_water_2020 = pd.merge(Corporation_water_2020, df_plan_water, how = 'outer', on = ['account_number'])\nCorporation_water_2020['KPI plan'] = Corporation_water_2020['KPI plan'].fillna(0)\nCorporation_water_2020['KPI plan'] = scaler.fit_transform(Corporation_water_2020['KPI plan'].values.reshape(-1,1))\n\neducation_water = Corporation_Responses_Water_2020[Corporation_Responses_Water_2020[\"response_value\"].str.contains('education') == True]\ndf_education_water = pd.DataFrame(education_water.groupby('account_number').size())\ndf_education_water.columns=['KPI education']\nCorporation_water_2020 = pd.merge(Corporation_water_2020, df_education_water, how = 'outer', on = ['account_number'])\nCorporation_water_2020['KPI education'] = Corporation_water_2020['KPI education'].fillna(0)\nCorporation_water_2020['KPI education'] = scaler.fit_transform(Corporation_water_2020['KPI education'].values.reshape(-1,1))\n\n\nCorporation_water_2020['KPI W1.4, W1.4a'] = (Corporation_water_2020['W1.4'] + Corporation_water_2020['% of suppliers'] + Corporation_water_2020['% of total procurement spend'])/3\nCorporation_water_2020['KPI Words'] = (Corporation_water_2020['KPI engagement'] + Corporation_water_2020['KPI education'] + Corporation_water_2020['KPI plan'])/3\nCorporation_water_2020['KPI Water'] = (Corporation_water_2020['KPI_Communication_and_transparency_water'] + Corporation_water_2020['KPI W1.4, W1.4a'] + Corporation_water_2020['KPI Words'])/3\n\n\nfig, axes = plt.subplots(nrows=1, ncols=3, figsize=(24,6))\nfig.suptitle('KPI for corporation on water')\n\nsns.violinplot(ax=axes[0], y=Corporation_water_2020['KPI W1.4, W1.4a'], color = '#f8b500')\naxes[0].set_ylabel(\"KPI W1.4, W1.4a\")\n\nsns.violinplot(ax=axes[1], y=Corporation_water_2020['KPI Words'], color = '#17b978')\naxes[1].set_ylabel(\"KPI Words\")\n\nsns.violinplot(ax=axes[2], y=Corporation_water_2020['KPI Water'], color = '#53AFFF')\naxes[2].set_ylabel(\"KPI Water\")\n\nShow = False","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"We then build a database with a climate and water indicator per country corresponding to the average of the indicators of the corporations that are established in the country. "},{"metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"trusted":true},"cell_type":"code","source":"Country_list = Cities_2020['Country'].unique()\nCountry_water = pd.DataFrame()\ndf_country_water = Corporation_Responses_Water_2020[Corporation_Responses_Water_2020['page_name'] == 'W0.3']\ndf_country_water = Corporation_water_2020.merge(df_country_water, on = ['account_number'])\nfor Country in Country_list:\n df_country = df_country_water[df_country_water[\"response_value\"].str.contains(Country) == True]\n df_country['Country']=Country\n df_country=df_country[['KPI Water', 'Country' ]]\n Country_water = pd.concat([Country_water, df_country])\n\nCountry_climate = pd.DataFrame()\ndf_country_climate = Corporation_Responses_Climate_2020[Corporation_Responses_Climate_2020['page_name'] == 'C0.3']\ndf_country_climate = Corporation_climate_2020.merge(df_country_climate, on = ['account_number'])\nfor Country in Country_list:\n df_country = df_country_climate[df_country_climate[\"response_value\"].str.contains(Country) == True]\n df_country['Country']=Country\n df_country=df_country[['KPI climate', 'Country' ]]\n Country_climate = pd.concat([Country_climate, df_country])\n \nCountry_water=Country_water.groupby('Country').mean()\nCountry_climate=Country_climate.groupby('Country').mean()\n\nCountry_CDP_2020 = pd.merge(Country_water, Country_climate, how = 'outer', on = ['Country'])\nCountry_CDP_2020 = Country_CDP_2020.fillna(0)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# 2.2 Indicators for cities\n\nFor the questionnaire on cities, one indicator per section is constructed.\n\n# Governance\n\nThe survey provides the following questions :\n\n**(1.0) Does your city incorporate sustainability goals and targets (e.g. GHG reductions) into the master planning for the city?**\n\nA performance indicator $KPI_{1.0}$ is constructed by transforming categorical data into numerical data. \n* Yes = > 3\n* In progress = > 2\n* Intending to incorporate in the next 2 years = > 1\n* Not intending to incorporate = > 0\n* Do not know = > 0\n\n**(1.0a) Please detail which goals and targets are incorporated in your city’s master plan and describe how these goals are addressed in the table below.**\n\nThe answers to this question are a selection of targets. This is why an indicator $KPI_{1.0a}$ is constructed by counting the number of answered options.\n\nAn overall indicator for governance is then defined :\n$$KPI_{Governance} = \\dfrac{KPI_{1.0} + KPI_{1.0a}}{2}$$\n\nThe same methodology is used for the other questions. If it's a question with unique choices, we turn it into numerical data. If it is a question with multiple answers, the number of selected answers is counted. When there are several questions per section, the weighted sum of each indicator is calculated.\n\n* In the section Climate :\n> (2.0) Has a climate change risk or vulnerability assessment been undertaken for your city?\n* In the section Adaptation :\n> (3.2) Does your city council, or similar authority, have a published plan that addresses climate change adaptation?\n* In the section City-wide Emissions:\n> (4.0) Does your city have a city-wide emissions inventory to report?
\n> (4.9) Does your city have a consumption-based inventory to measure emissions from consumption of goods and services by your residents?
\n> (4.12) Has the city-wide GHG emissions data you are currently reporting been externally verified or audited in part or in whole?\n* In the section Emissions Reduction :\n> (5.5) Does your city have a climate change mitigation or energy access plan for reducing city-wide GHG emissions?\n* In the section Opportunities:\n> (6.0) Please indicate the opportunities your city has identified as a result of addressing climate change and describe how the city is positioning itself to take advantage of these opportunities.
\n> (6.2) Does your city collaborate in partnership with businesses in your city on sustainability projects?\n* In the section Energy :\n> (8.0) Does your city have a renewable energy or electricity target?
\n> (8.5) Does your city have a target to increase energy efficiency?\n* In the section Water Security\n> (14.4) Does your city have a publicly available Water Resource Management strategy?"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"Q10 = Cities_Responses_2020[Cities_Responses_2020['Question Number'] == '1.0']\nQ10 = Q10[['Account Number','Response Answer']]\nQ10['Response Answer'] = Q10['Response Answer'].apply(convert)\nQ10.columns = ['Account Number', 'KPI 1.0']\nCities_2020 = Cities_2020.merge(Q10, on = ['Account Number'])\nCities_2020['KPI 1.0'] = normalize(Cities_2020['KPI 1.0'])\n\nQ10a = Cities_Responses_2020[Cities_Responses_2020['Question Number'] == '1.0a']\nQ10a = Q10a[['Account Number','Response Answer']]\nQ10a.columns = ['Account Number', 'KPI 1.0a']\nQ10a = Q10a.groupby(['Account Number']).count()\nCities_2020 = Cities_2020.merge(Q10a, on = ['Account Number'])\nscaler = QuantileTransformer(n_quantiles=20)\nCities_2020['KPI 1.0a'] = scaler.fit_transform(Cities_2020['KPI 1.0a'].values.reshape(-1,1))\n\nCities_2020['KPI Governance'] = 0.5*Cities_2020['KPI 1.0'] + 0.5*Cities_2020['KPI 1.0a']\n\nQ20 = Cities_Responses_2020[Cities_Responses_2020['Question Number'] == '2.0']\nQ20 = Q20[['Account Number','Response Answer']]\nQ20['Response Answer'] = Q20['Response Answer'].apply(convert)\nQ20.columns = ['Account Number', 'KPI Climate Hazards and Vulnerability']\nCities_2020 = Cities_2020.merge(Q20, on = ['Account Number'])\nCities_2020['KPI Climate Hazards and Vulnerability'] = normalize(Cities_2020['KPI Climate Hazards and Vulnerability'])\n\nQ32 = Cities_Responses_2020[Cities_Responses_2020['Question Number'] == '3.2']\nQ32 = Q32[['Account Number','Response Answer']]\nQ32['Response Answer'] = Q32['Response Answer'].apply(convert)\nQ32.columns = ['Account Number', 'KPI Adaptation']\nCities_2020 = Cities_2020.merge(Q32, on = ['Account Number'])\nCities_2020['KPI Adaptation'] = normalize(Cities_2020['KPI Adaptation'])\n\nQ40 = Cities_Responses_2020[Cities_Responses_2020['Question Number'] == '4.0']\nQ40 = Q40[['Account Number','Response Answer']]\nQ40['Response Answer'] = Q40['Response Answer'].apply(convert)\nQ40.columns = ['Account Number', 'KPI 4.0']\nCities_2020 = Cities_2020.merge(Q40, on = ['Account Number'])\nCities_2020['KPI 4.0'] = normalize(Cities_2020['KPI 4.0'])\n\nQ49 = Cities_Responses_2020[Cities_Responses_2020['Question Number'] == '4.9']\nQ49 = Q49[Q49['Column Number'] == 1]\nQ49 = Q49[['Account Number','Response Answer']]\nQ49['Response Answer'] = Q49['Response Answer'].apply(convert)\nQ49.columns = ['Account Number', 'KPI 4.9']\nCities_2020 = Cities_2020.merge(Q49, on = ['Account Number'])\nCities_2020['KPI 4.9'] = normalize(Cities_2020['KPI 4.9'])\n\nQ412 = Cities_Responses_2020[Cities_Responses_2020['Question Number'] == '4.12']\nQ412 = Q412[['Account Number','Response Answer']]\nQ412['Response Answer'] = Q412['Response Answer'].apply(convert)\nQ412.columns = ['Account Number', 'KPI 4.12']\nCities_2020 = Cities_2020.merge(Q412, on = ['Account Number'])\nCities_2020['KPI 4.12'] = normalize(Cities_2020['KPI 4.12'])\n\nCities_2020['KPI City-wide Emissions'] = (Cities_2020['KPI 4.0'] + Cities_2020['KPI 4.9'] + Cities_2020['KPI 4.12'])/3\n\nQ55 = Cities_Responses_2020[Cities_Responses_2020['Question Number'] == '5.5']\nQ55 = Q55[['Account Number','Response Answer']]\nQ55['Response Answer'] = Q55['Response Answer'].apply(convert)\nQ55.columns = ['Account Number', 'KPI Emissions Reduction']\nCities_2020 = Cities_2020.merge(Q55, on = ['Account Number'])\nCities_2020['KPI Emissions Reduction'] = normalize(Cities_2020['KPI Emissions Reduction'])\n\nQ60 = Cities_Responses_2020[Cities_Responses_2020['Question Number'] == '6.0']\nQ60 = Q60[['Account Number','Response Answer']]\nQ60.columns = ['Account Number', 'KPI 6.0']\nQ60 = Q60.groupby(['Account Number']).count()\nCities_2020 = Cities_2020.merge(Q60, on = ['Account Number'])\nCities_2020['KPI 6.0'] = scaler.fit_transform(Cities_2020['KPI 6.0'].values.reshape(-1,1))\n\nQ62 = Cities_Responses_2020[Cities_Responses_2020['Question Number'] == '6.2']\nQ62 = Q62[['Account Number','Response Answer']]\nQ62['Response Answer'] = Q62['Response Answer'].apply(convert)\nQ62.columns = ['Account Number', 'KPI 6.2']\nCities_2020 = Cities_2020.merge(Q62, on = ['Account Number'])\nCities_2020['KPI 6.2'] = normalize(Cities_2020['KPI 6.2'])\n\nCities_2020['KPI Opportunities'] = (Cities_2020['KPI 6.0'] + Cities_2020['KPI 6.2'])/2\n\nQ80 = Cities_Responses_2020[Cities_Responses_2020['Question Number'] == '8.0']\nQ80 = Q80[['Account Number','Response Answer']]\nQ80['Response Answer'] = Q80['Response Answer'].apply(convert)\nQ80.columns = ['Account Number', 'KPI 8.0']\nCities_2020 = Cities_2020.merge(Q80, on = ['Account Number'])\nCities_2020['KPI 8.0'] = normalize(Cities_2020['KPI 8.0'])\n\nQ85 = Cities_Responses_2020[Cities_Responses_2020['Question Number'] == '8.5']\nQ85 = Q85[['Account Number','Response Answer']]\nQ85['Response Answer'] = Q85['Response Answer'].apply(convert)\nQ85.columns = ['Account Number', 'KPI 8.5']\nCities_2020 = Cities_2020.merge(Q85, on = ['Account Number'])\nCities_2020['KPI 8.5'] = normalize(Cities_2020['KPI 8.5'])\n\nCities_2020['KPI Energy'] = (Cities_2020['KPI 8.0'] + Cities_2020['KPI 8.5'])/2\n\nQ144 = Cities_Responses_2020[Cities_Responses_2020['Question Number'] == '14.4']\nQ144 = Q144[['Account Number','Response Answer']]\nQ144['Response Answer'] = Q144['Response Answer'].apply(convert_water)\nQ144.columns = ['Account Number', 'KPI Water Security']\nCities_2020 = Cities_2020.merge(Q144, on = ['Account Number'])\nCities_2020['KPI Water Security'] = normalize(Cities_2020['KPI Water Security'])\n\nCities_2020['KPI City']= Cities_2020['KPI Governance']+Cities_2020['KPI Climate Hazards and Vulnerability']+Cities_2020['KPI Adaptation']+Cities_2020['KPI Adaptation']+Cities_2020['KPI Emissions Reduction']+Cities_2020['KPI Opportunities']+Cities_2020['KPI Energy']+Cities_2020['KPI Water Security']\nCities_2020['KPI City'] = scaler.fit_transform(Cities_2020['KPI City'].values.reshape(-1,1))\n\nfig, axes = plt.subplots(nrows=1, ncols=4, figsize=(24,6))\nfig.suptitle('KPI for cities')\n\nsns.violinplot(ax=axes[0], y=Cities_2020['KPI Governance'], color = '#229954')\nsns.violinplot(ax=axes[1], y=Cities_2020['KPI Climate Hazards and Vulnerability'], color = '#27AE60')\nsns.violinplot(ax=axes[2], y=Cities_2020['KPI Adaptation'], color = '#52BE80')\nsns.violinplot(ax=axes[3], y=Cities_2020['KPI Adaptation'], color = '#7DCEA0')\n\nfig, axes = plt.subplots(nrows=1, ncols=4, figsize=(24,6))\n\nsns.violinplot(ax=axes[0], y=Cities_2020['KPI Emissions Reduction'], color = '#2E86C1')\nsns.violinplot(ax=axes[1], y=Cities_2020['KPI Opportunities'], color = '#3498DB')\nsns.violinplot(ax=axes[2], y=Cities_2020['KPI Energy'], color = '#5DADE2')\nsns.violinplot(ax=axes[3], y=Cities_2020['KPI Water Security'], color = '#85C1E9')\n\nfig, axes = plt.subplots(nrows=1, ncols=1, figsize=(4,4))\nsns.violinplot(y=Cities_2020['KPI City'], color = '#F39C12')\n\nShow = False","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"trusted":true},"cell_type":"code","source":"for i in range(0,Cities_2020.shape[0]):\n Cities_2020['KPI City'][i] = str(round(Cities_2020['KPI City'][i], 2)) ","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The figure below shows the scores of the different CDP cities around the world."},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"Fig_Cities = px.scatter_geo(Cities_2020,lat = 'latitude',lon= 'longitude',size = 'Population',width=1200,height = 1000, color='KPI City', hover_name=\"City\",projection=\"natural earth\",\n hover_data = {'longitude':False,'latitude':False,'Country':True,'Population':True,'CDP Region':True,'KPI City':True},\n labels = {'Risks':'Status of action'})\nFig_Cities.update_layout(\n title={\n 'text': \"Key Performance Indicator of CDP cities\",\n 'x':0.5},\n font=dict(\n family=\"Arial\",\n size=12,\n color= \"black\"),\n autosize=False,\n width=800,\n height=400,\n)\nFig_Cities.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# 3. KPI validation\n\nIn order to evaluate the performance of the created KPI, we use a database from World Bank [Compiled datasets for CDP Analytics Competition](https://www.kaggle.com/seraphimstreets/environmentequity-starterpack) which has been collected by [seraphimstreets](https://www.kaggle.com/seraphimstreets).\n\nThe indicators we are particularly interested in are vulnerability indicators :\n* Population living below the national income poverty line\n* Climate Risk Score\n* Vulnerability score\n* Readiness score\n\nWe create a correlation matrix between our main indicators and the indicators from World Bank."},{"metadata":{"trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"Country_CDP_2020_ = Country_CDP_2020.copy()\n\nCountry_2020 = Cities_2020.groupby('Country').mean()\nCountry_CDP_2020 = Country_CDP_2020.reset_index()\nCountry_2020 = Country_2020.reset_index()\nKPI_col = ['Country','KPI_Communication_and_transparency_cities', 'KPI 1.0', 'KPI 1.0a','KPI Governance', 'KPI Climate Hazards and Vulnerability','KPI Adaptation', 'KPI 4.0', 'KPI 4.9', 'KPI 4.12','KPI City-wide Emissions', 'KPI Emissions Reduction', 'KPI 6.0','KPI 6.2', 'KPI Opportunities', 'KPI 8.0', 'KPI 8.5', 'KPI Energy','KPI Water Security', 'KPI City']\nCountry_CDP_2020 = Country_CDP_2020.merge(Country_2020[KPI_col], on = ['Country'])\n\ncountry_map = {'United States of America':'United States',\n 'United Kingdom of Great Britain and Northern Ireland': 'United Kingdom',\n 'Gibraltar': None, \n 'Viet Nam': 'Vietnam', \n 'Taiwan, Greater China': 'Taiwan',\n 'China, Hong Kong Special Administrative Region': None,\n 'Republic of Korea': 'South Korea', \n 'United Republic of Tanzania': 'Tanzania',\n 'Democratic Republic of the Congo': 'Congo, Dem. rep',\n \"Côte d'Ivoire\": \"Cote d'Ivoire\",\n 'Bolivia (Plurinational State of)': 'Bolivia',\n 'State of Palestine': 'Palestine'\n }\nCountry_CDP_2020['country_mapped'] = Country_CDP_2020['Country'].map(country_map)\nCountry_CDP_2020.loc[Country_CDP_2020['country_mapped'].isna(), 'country_mapped'] = Country_CDP_2020['Country']\n\nVulnerability = pd.merge(Vulnerability[['code_3digit','Population living below the national income poverty line','Climate Risk Score', 'Vulnerability score', 'Readiness score']], country_code[['Country_name', 'code_3digit']])\nVulnerability.columns = ['code_3digit','Population living below the national income poverty line','Climate Risk Score', 'Vulnerability score', 'Readiness score','Country']\n\nVulnerability_corr = pd.merge(Vulnerability, Country_CDP_2020)\n\nfig, ax = plt.subplots(figsize=(12, 12))\neval_col = ['KPI City', 'KPI climate','KPI Water','Population living below the national income poverty line','Climate Risk Score', 'Vulnerability score', 'Readiness score']\nsns.heatmap(Vulnerability_corr[eval_col].corr(), annot=True)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Significant correlations are found between :\n* $KPI_{City}$ and Readiness score\n* $KPI_{climate}$ and Vulnerability score\n* $KPI_{climate}$ and Population living below the national income poverty line\n\nLess signficant correlations are between :\n* $KPI_{City}$ and Climate Risk score\n* $KPI_{climate}$ and Climate Risk score\n* $KPI_{Water}$ and Climate Risk score\n* $KPI_{Water}$ and Vulnerability score\n\nThis corresponds to the expectations we have. Cities are the most prepared for future challenges while corporations are more involved in climate risks. Corporations also appear to be well aware of social issues in the countries where they operate according to the correlation with the Population living below the national income poverty line indicator.\n"},{"metadata":{},"cell_type":"markdown","source":"# 4. Discussion\n# 4.1 Intersection between cities and corporations"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"df = pd.merge(Country_CDP_2020_, Cities_2020, how = 'outer', on = ['Country'])\ndf.sort_values(by = 'CDP Region', inplace = True)\ndf['CDP Region']=pd.Categorical(df['CDP Region'])\n \n# create data\nx = df['KPI City']\ny = (df['KPI climate'] + df['KPI Water'])/2\nz = df['Population']/5000\n\n# plot\nplt.rcParams[\"legend.title_fontsize\"] = \"large\"\nplt.rcParams[\"font.size\"] = 20\nplt.rcParams[\"font.sans-serif\"] = 'Arial'\n\nplt.figure(figsize=(22, 10))\nplt.scatter(x, y, s=z, c=df['CDP Region'].cat.codes, cmap=plt.cm.get_cmap('RdYlBu', 8), alpha=0.6, edgecolors=\"grey\", linewidth=2)\n\n# Add titles (main and on axis)\nplt.xlabel(\"KPI City\")\nplt.ylabel(\"(KPI Climate + KPI Water)/2 \")\nplt.title(\"Intersection of KPIs between cities and corporations\")\n\n\n# This function formatter will replace integers with target names\nformatter = plt.FuncFormatter(lambda val, loc: df['CDP Region'].unique()[val])\n\n# We must be sure to specify the ticks matching our target names\nplt.colorbar(ticks=[0, 1, 2, 3, 4, 5, 6, 7], format=formatter);\n\n# Set the clim so that labels are centered on each block\nplt.clim(-0.5, 7.5)\n\n\nfor area in [750, 1500, 3000]:\n plt.scatter([], [], c='k', alpha=0.4, s=area,label=' '+str(area/3000*2)+ ' M')\nplt.legend(scatterpoints=1, frameon=False, labelspacing=2, title='Population')\n\nShow = False","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The graph above shows us that environmental issues are best taken into account in African countries followed by Southeast Asia ans Oceania countries. The clusters seen in the graph are due to the fact that many cities are either in the United States or in Brazil. The KPI for cities are fairly different but the KPI for corporations are grouped by country."},{"metadata":{},"cell_type":"markdown","source":"# 4.2 Intersection between environnemental and social issues"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"df2 = pd.merge(df, Vulnerability, how = 'outer', on = ['Country'])\ndf2.sort_values(by = 'CDP Region', inplace = True)\ndf2['CDP Region']=pd.Categorical(df2['CDP Region'])\n \n# create data\nx = df2['Population living below the national income poverty line']\ny = df2['KPI climate']\nz = df2['Population']/5000\n\n# plot\nplt.rcParams[\"legend.title_fontsize\"] = \"large\"\nplt.rcParams[\"font.size\"] = 20\nplt.rcParams[\"font.sans-serif\"] = 'Arial'\n\nplt.figure(figsize=(22, 10))\nplt.scatter(x, y, s=z, c=df2['CDP Region'].cat.codes, cmap=plt.cm.get_cmap('RdYlBu', 8), alpha=0.6, edgecolors=\"grey\", linewidth=2)\n\n# Add titles (main and on axis)\nplt.xlabel(\"Population living below the national income poverty line\")\nplt.ylabel(\"KPI Climate\")\nplt.title(\"Intersection of KPIs between cities and corporations\")\n\n\n# This function formatter will replace integers with target names\nformatter = plt.FuncFormatter(lambda val, loc: df2['CDP Region'].unique()[val])\n\n# We must be sure to specify the ticks matching our target names\nplt.colorbar(ticks=[0, 1, 2, 3, 4, 5, 6, 7], format=formatter);\n\n# Set the clim so that labels are centered on each block\nplt.clim(-0.5, 7.5)\n\n\nfor area in [750, 1500, 3000]:\n plt.scatter([], [], c='k', alpha=0.4, s=area,label=' '+str(area/3000*2)+ ' M')\nplt.legend(scatterpoints=1, frameon=False, labelspacing=2, title='Population')\n\nShow = False","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The figure below confirms the previous information. Corporations seem to be engaged in social issues. Climate issues appear even more important if there is a high level of poverty in the country. Cities and corporations that are already facing tomorrow's global challenges are already prepared for them. Cities and corporations that are not yet involved should make plans to prepare for them."},{"metadata":{},"cell_type":"markdown","source":"# 5. Conclusion\n\nWe have succeeded in building KPI based on CDP data.\nIt was shown that the three main indicators $KPI_{City}$, $KPI_{climate}$ and $KPI_{Water}$ assess the readiness of cities and companies to deal with environmental risks and social issues. It also appears that many cities and corporations are well aware of environmental and social issues.\nIn addition, the corporations that obtain the highest scores are those where social issues are the most important.\n\nIf we were to answer the title question, the answer would be as follows:\n\nMost cities are prepared for environmental and social issues and have a plan for the future. Cities or corporations that are not yet or only minimally concerned with these issues should prepare plans in case the problems accelerate.\n\nIn this notebook, it was chosen to create KPIs on the whole database. It may be interesting to explore other elements of the dataset:\n- Observe the evolution of the performance indicators over the available years.\n- Create more accurate indicators using a specific section of the dataset\n- Analyze performance indicator values for specific cities or corporations"},{"metadata":{},"cell_type":"markdown","source":"# References\n1.[CDP: Unlocking Climate Solutions Dataset](https://www.kaggle.com/c/cdp-unlocking-climate-solutions/data)\n\n2.[Cities 2020 for CDP](https://www.kaggle.com/jbrans/cities-2020-for-cdp) \nA database of the cities contained in the CDP data with corrected population and location by [me](https://www.kaggle.com/jbrans).\n\n3.[Country code](https://www.kaggle.com/koki25ando/country-code) Country iso code by [thatdataanalyst](https://www.kaggle.com/koki25ando).\n\n4.[Compiled datasets for CDP Analytics Competition](https://www.kaggle.com/seraphimstreets/environmentequity-starterpack) Many indicators by [seraphimstreets](https://www.kaggle.com/seraphimstreets) obtained through [World Bank](https://www.worldbank.org/)"},{"metadata":{},"cell_type":"markdown","source":"## Thank you for reading so far. Do leave a vote if you liked it ✋"}],"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}