{"cells":[{"metadata":{},"cell_type":"markdown","source":"![](https://imgur.com/AcU0UOv.jpg)"},{"metadata":{},"cell_type":"markdown","source":"# CDP: Unlocking Climate Solutions - KPIs Proposal\n\nThis notebook will propose several Key Performance Indicators (KPIs) derived from the analysis of the three questionnaires made available by the CDP: the cities, corporation and water questionnaires.\n\nThe KPIs we propose can be grouped as follows:\n* [Objective tracking KPIs](#section-one): these KPIs are designed to assess whether the objective outlined by the city/company is reasonable and achievable based on historical performance; or if the objective been set is too  easy given the indicated time frame. Equally,through these KPIs is possible to see if a company/city is on track on achieving its goals\n* [Risk/Opportunities assessment KPIs](#section-two): these KPIs are aimed at assessing exposure to a risk/opportunity given the probability of its manifestation, the severity and a time horizon. KPIs in this section can be easily enhanced/cross-referenced with external data to add further dimentions, for example we use the Social Vulnerability Index and the ND-GAIN datasets to add further insights on what could be the impact of a climate related event on the City.\n* [Return on Investment KPIs](#section-three): KPIs aimed at assessing the \"value for money\" of a given target by a company/city. These KPIs could help identify what are the most efficient measures that can be implemented by a company/city to cut for example CO2 emissions or reduce MWh of electricy consumption.\n* [Trends KPIs](#section-four): these KPIs look at trend information e.g. CO2 emissions/ water consumption, etc. and monitor how well/badly a company/city is doing in controlling polluting behaviours\n* [City assessment KPIs](#section-five): KPIs monitoring some of the city main enviromental themes, including - green energy usage, transportation preferences, waste produced, eating habits and CO2 emissions (e.g. consumption of meat & dairy, number of private cars per citizen, waste production per citizen or square km etc.)\n* [Corporate assessment KPIs](#section-six): KPIs monitoring efficiency and consumption in terms of energy, CO2 emissions efficiency and consumption/production of green energy. This is also produced with a country level view. \n* [Water Management assessment KPIs](#section-seven): several KPIs built on water consumption and water management, also with a country/facility level view. \n\n**--> All the KPIs we produce can be used both as a raw indicator or as a \"ranking factor\" to be able to order the cities/companies and identify best/worst in class.** "},{"metadata":{},"cell_type":"markdown","source":"<a id=\"section-one\"></a>\n# Objective tracking KPIs#\n\nIn this section the objective tracking KPIs will be described. The table below summarizes the main KPIs we propose for this section and the tables within the questionnaires they can be applied to:"},{"metadata":{},"cell_type":"markdown","source":"<html>\n<head>\n<style>\ntable, th, td {\n  border: 1px solid black;\n}\ntable {\n  width: 100%;\n} \n</style>\n</head>\n<body>\n   \n    \n<table>\n   \n  <tr>\n    <th style=\"text-align:centre\" \"width:130px\"BGcolor=#ff9999>KPI type</th>\n    <th style=\"text-align:centre\" \"width:200px\"BGcolor=#ff9999>Formula/methodology</th>\n    <th style=\"text-align:centre\" \"width:300px\"BGcolor=#ff9999>Interpretation</th>\n    <th style=\"text-align:centre\" \"width:100px\"BGcolor=#ff9999>Cities</th>\n    <th style=\"text-align:centre\" \"width:100px\"BGcolor=#ff9999>Corporations</th>\n    <th style=\"text-align:centre\" \"width:100px\"BGcolor=#ff9999>Water</th>\n  </tr>\n  <tr>\n    <td style=\"text-align:right\" BGcolor=#ffcccc><b>Objective strategy</b></td>\n    <td style=\"text-align:right\" BGcolor=#ffcccc>Objective strategy = Years set to achieve target - Actual years it should take to achieve it\n<br>Where:Years set to achieve the target: are the total years a company/city has given itself to achieve a target\nActual years: years it should take to achieve it based on performance so far (e.g. emissions cut since the objective was set per year)</td>\n    <td style=\"text-align:right\" BGcolor=#ffcccc>This KPI assesses how reasonable the objective been set is, based on current performance.\nA large negative value on this KPI means that the objective will be hard to achieve based on the historical performance and what has been done so far (e.g. a company wants to cut emissions by 50% in 5 years and after 4 years has only cut 10%)\nPositive values of the raw KPI mean that actually the objective been set might be too easy (e.g. a company gives itself 10 years to cut emissions by 50% but after 2 years it has already cut 45%). Balanced objectives will range around the Zero value for this metric. </td>\n   <td style=\"text-align:right\" BGcolor=#ffcccc>5.0a</td>\n   <td style=\"text-align:right\" BGcolor=#ffcccc>4.1a</td>\n   <td style=\"text-align:right\" BGcolor=#ffcccc>8.1a</td>\n  </tr>\n  <tr>\n   <td style=\"text-align:right\" BGcolor=#ffcccc><b>Objective ambition</b></td>\n   <td style=\"text-align:right\" BGcolor=#ffcccc>Objective ambition = Total emissions in base year/ Emissions cuts that are target of the objective</td>\n   <td style=\"text-align:right\" BGcolor=#ffcccc>A measure of how ambitious is a company in setting the objective (e.g. a company wanting to cut 100% of a target can be considered more ambitions than a company cutting only 10%)</td>\n   <td style=\"text-align:right\" BGcolor=#ffcccc>5.0a</td>\n   <td style=\"text-align:right\" BGcolor=#ffcccc>4.1a</td>\n   <td style=\"text-align:right\" BGcolor=#ffcccc>8.1a</td>\n  </tr>\n   <tr>\n    <td style=\"text-align:right\" BGcolor=#ffcccc><b>Objective status</b></td>\n    <td style=\"text-align:right\" BGcolor=#ffcccc>Objective status = If Objective Strategy<0 objective is \"On target\", if Objective strategy>0 objective is \"Behind Target\" </td>\n    <td style=\"text-align:right\" BGcolor=#ffcccc>A binary outcome to check if objectives are on track or not, based on the \"Objective Strategy\" KPI above. </td>\n    <td style=\"text-align:right\" BGcolor=#ffcccc>5.0a/ 8.0a/ 8.5a</td>\n    <td style=\"text-align:right\" BGcolor=#ffcccc>4.1a</td>\n    <td style=\"text-align:right\" BGcolor=#ffcccc>8.1a</td>   \n  </tr>\n    \n   <tr>\n    <td style=\"text-align:right\" BGcolor=#ffcccc><b>Objective progress</b></td>\n    <td style=\"text-align:right\" BGcolor=#ffcccc>Objective progress = % achieved against an objective</td>\n    <td style=\"text-align:right\" BGcolor=#ffcccc>A rank order measure is provided by company/city to see which ones are the the closest to achieve their objectives. For certain Tables this is weighted by objective size when grouping by company (e.g. Emissions that are being cut under that specific objective)</td>\n    <td style=\"text-align:right\" BGcolor=#ffcccc>5.0a/ 8.0a/ 8.5a</td>\n    <td style=\"text-align:right\" BGcolor=#ffcccc>4.1a</td>\n    <td style=\"text-align:right\" BGcolor=#ffcccc>8.1a</td>\n  </tr>\n    \n</table>\n</body>\n</html>\n"},{"metadata":{},"cell_type":"markdown","source":"Before we start we will show how we have re-organised the data to build the KPIs. This approach is aimed at re-constructing the data in the same way the corporation/city has visualised and entered the information. After trying several approaches we found this to be the easiest way to understand the contents of the questionnaires. \n\nAn example below for table 4.1a of the corporate questionnaire for \"Celestica Inc.\". This how we re-organize the data to build KPIs:"},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"### For simplicity we present below the mian project code, which will create all the KPIs discussed throughout the notebook.\n### THe code starts with the tables of the corporate questionnaire and then moves to water and finally the cities. Putting all the code here makes it simple\n### for us to call different KPIs throughout the notebook. The underlying process which we use to analyse the tables and create the KPIs is always the same:\n### Import the table from the main dataset, clean the table from uneccessary columns and prepare for pivot, pivot the table, transform strings into floats where \n### necessary and create the KPI.\n\n\n### IMPORTS\n# Python packages\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\nfrom pandas_profiling import ProfileReport # quick EDA\nimport matplotlib.pyplot as plt # plotting\nfrom math import pi\nimport string\nimport seaborn as sns # plotting\nfrom shapely.geometry import Point # To represent and analyze geo coordinates\nimport functools \nimport operator\nimport warnings\nwarnings.filterwarnings('ignore')\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# Import files\n\nq20 = pd.read_csv('../input/cdp-unlocking-climate-solutions/Corporations/Corporations Responses/Climate Change/2020_Full_Climate_Change_Dataset.csv', low_memory=False)\nw20 = pd.read_csv('../input/cdp-unlocking-climate-solutions/Corporations/Corporations Responses/Water Security/2020_Full_Water_Security_Dataset.csv', low_memory=False)\nc20 = pd.read_csv('../input/cdp-unlocking-climate-solutions/Cities/Cities Responses/2020_Full_Cities_Dataset.csv', low_memory=False)\nSVI_C = pd.read_csv('../input/cdp-unlocking-climate-solutions/Supplementary Data/CDC Social Vulnerability Index 2018/SVI2018_US_COUNTY.csv', low_memory=False)\nSVI = pd.read_csv('../input/cdp-unlocking-climate-solutions/Supplementary Data/CDC Social Vulnerability Index 2018/SVI2018_US.csv', low_memory=False)\ndisc = pd.read_csv('../input/cdp-unlocking-climate-solutions/Cities/Cities Disclosing/2020_Cities_Disclosing_to_CDP.csv', low_memory=False)\nND_DROUGHT = pd.read_csv('../input/ndgain-uaa-dataset/UAA Data/Drought Data.csv', low_memory=False)\n\n## Create folder to store KPI df:\n\ntry:\n    os.mkdir('/kaggle/working/df_KPIs/')\n    os.mkdir('/kaggle/working/df_KPIs/Climate/')\n    os.mkdir('/kaggle/working/df_KPIs/Water/')\n    os.mkdir('/kaggle/working/df_KPIs/Cities/')\n    os.mkdir('/kaggle/working/df_KPIs/Final/')\nexcept:\n    pass\n\n\n################################################################################################################################################################\n################################################################################################################################################################\n################################################################################################################################################################\n###################################################                                                      #######################################################\n###################################################            C O R P O R A T I O N S - 2 0 2 0         #######################################################\n###################################################                                                      #######################################################\n################################################################################################################################################################\n################################################################################################################################################################\n################################################################################################################################################################\n\n############################################################### TABLE - 4.1a Emission reduciton targets ########################################################\n\n# Select questions\nC41a = q20[q20['question_number']=='C4.1a']\n\n# Remove unnecessary columns\nC41a =C41a.drop(['survey_year', 'response_received_date','accounting_period_to','ors_response_id','submission_date','page_name','module_name',\n          'question_number','question_unique_reference','data_point_name','data_point_id','comments','row_name','table_columns_unique_reference','column_number'], axis=1)\n\n## Pivot\nC41a=C41a.pivot(index=['account_number','organization','row_number'], columns='column_name', values='response_value').reset_index()\nC41a=C41a.sort_values(by=['account_number','organization','row_number'])\n\n## Select columns\nC41a=C41a[['account_number','organization','row_number','C4.1a_C1Target reference number', 'C4.1a_C2Year target was set','C4.1a_C4Scope(s) (or Scope 3 category)',\n     'C4.1a_C5Base year','C4.1a_C6Covered emissions in base year (metric tons CO2e)','C4.1a_C7Covered emissions in base year as % of total base year emissions in selected Scope(s) (or Scope 3 category)',\n     'C4.1a_C8Target year','C4.1a_C9Targeted reduction from base year (%)','C4.1a_C12% of target achieved [auto-calculated]','C4.1a_C13Target status in reporting year',\n     'C4.1a_C14Is this a science-based target?','C4.1a_C15Please explain (including target coverage)']]\n\n# Re set col type\nC41a['C4.1a_C8Target year'] = C41a['C4.1a_C8Target year'].astype(float)\nC41a['C4.1a_C2Year target was set'] = C41a['C4.1a_C2Year target was set'].astype(float)\nC41a['C4.1a_C12% of target achieved [auto-calculated]'] = C41a['C4.1a_C12% of target achieved [auto-calculated]'].astype(float)\nC41a['C4.1a_C9Targeted reduction from base year (%)'] = C41a['C4.1a_C9Targeted reduction from base year (%)'].astype(float)\nC41a['C4.1a_C6Covered emissions in base year (metric tons CO2e)'] = C41a['C4.1a_C6Covered emissions in base year (metric tons CO2e)'].astype(float)\nC41a['C4.1a_C6Covered emissions in base year (metric tons CO2e)']=C41a['C4.1a_C6Covered emissions in base year (metric tons CO2e)'].astype(float)\n\n# Calculate new cols\nC41a['target_years']=C41a['C4.1a_C8Target year'] - C41a['C4.1a_C2Year target was set']+1\nC41a['Emissions_reduction_obj']= (C41a['C4.1a_C9Targeted reduction from base year (%)']/100)*C41a['C4.1a_C6Covered emissions in base year (metric tons CO2e)']\nC41a['Emissions_reduction_achieved']= (C41a['C4.1a_C12% of target achieved [auto-calculated]']/100)*C41a['C4.1a_C6Covered emissions in base year (metric tons CO2e)']\nC41a['Percentage_reduction_per_year']= (C41a['C4.1a_C9Targeted reduction from base year (%)']/100)/C41a['target_years']\n\nC41a['years_left']= C41a['C4.1a_C8Target year']-2020\n\nC41a['achiev_per_year'] = (C41a['C4.1a_C9Targeted reduction from base year (%)'])*(C41a['C4.1a_C12% of target achieved [auto-calculated]']/100)/(2020- C41a['C4.1a_C2Year target was set']+1)/100\nC41a['effective_years_to_achiev'] = C41a['Emissions_reduction_obj']/ (C41a['C4.1a_C6Covered emissions in base year (metric tons CO2e)']*(C41a['C4.1a_C9Targeted reduction from base year (%)']/100))\nC41a['Emissions_reduction per year'] = C41a['Emissions_reduction_obj']/C41a['target_years']\nC41a['Actual_emissions_cut_per_year'] = (C41a['Emissions_reduction_obj']*(C41a['C4.1a_C12% of target achieved [auto-calculated]']/100))/(2020- C41a['C4.1a_C2Year target was set']+1)\nC41a['Actual_years_to_achiev'] = C41a['Emissions_reduction_obj']/C41a['Actual_emissions_cut_per_year']\nC41a['Years_diff'] = C41a['target_years']-C41a['Actual_years_to_achiev']\nC41a['Years_diff'].replace(np.inf, np.nan, inplace=True)\nC41a['Years_diff'].replace(np.NINF, np.nan, inplace=True)\nC41a.loc[(C41a['C4.1a_C12% of target achieved [auto-calculated]']<0),'Years_diff'] = np.nan \nC41a['KPI_Strategy']=np.nan \n\nC41a.loc[(C41a['Years_diff']>C41a['Years_diff'].std()) | (C41a['Years_diff']<-C41a['Years_diff'].std()), 'Years_diff'] = np.nan\nC41a['Years_diff'].replace(np.inf, 0, inplace=True)\nC41a['Years_diff'].replace(np.NINF, 0, inplace=True)\n\n\n## Filter down Q're for KPI creation  \nC41a_KPI=C41a[['account_number','organization','row_number', 'C4.1a_C6Covered emissions in base year (metric tons CO2e)', 'Emissions_reduction_achieved','Emissions_reduction_obj','Years_diff']]\n\n# Do a groupby and weight by emission reduction objective\nwm = lambda x: np.average(x, weights=C41a_KPI.loc[x.index, 'Emissions_reduction_obj'])\n\n# C41a_KPI.loc[C41a_KPI['Years_diff'].isna(), 'Years_diff'] = 0.00000001\n# C41a_KPI.loc[C41a_KPI['Emissions_reduction_obj'].isna(), 'Emissions_reduction_obj'] = 0.00000001\nC41a_KPI.loc[C41a_KPI['Years_diff']==0, 'Years_diff'] = 0.00000001\nC41a_KPI.loc[C41a_KPI['Emissions_reduction_obj']==0, 'Emissions_reduction_obj'] = 0.00000001\n\nC41a_KPI=C41a_KPI.groupby(['account_number','organization'], as_index=False).agg(\n    Emissions_reduction_achieved=('Emissions_reduction_achieved', 'sum'),\n    Covered_emissions= ('C4.1a_C6Covered emissions in base year (metric tons CO2e)', 'sum'),\n    Emissions_reduction_obj=('Emissions_reduction_obj', 'sum'),\n    Years_diff=('Years_diff', wm)).dropna().reset_index()\n\n# Create final features\nC41a_KPI['Percentage_obj_total'] = C41a_KPI.Emissions_reduction_obj / C41a_KPI.Covered_emissions\nC41a_KPI['Percentage_obj_achieved'] = C41a_KPI.Emissions_reduction_achieved / C41a_KPI.Covered_emissions\n\n# Create ranking KPIs\nC41a_KPI['KPI_rank_objective_strategy'] = C41a_KPI['Years_diff'].rank(pct=True)\nC41a_KPI['KPI_rank_objective_ambition'] = C41a_KPI['Percentage_obj_total'].rank(pct=True)\nC41a_KPI['KPI_rank_objective_progress'] = C41a_KPI['Percentage_obj_achieved'].rank(pct=True)\n\nC41a_KPI = C41a_KPI.sort_values(by=['KPI_rank_objective_strategy']).reset_index()\n\n\n## Save KPI df to folder\nC41a_KPI.to_csv('df_KPIs/Climate/C41a_KPI.csv')\n\n################################################### TABLE 7.5C1 global Scope 2 emissions by country/region #####################################################\n\n# Location vs market based: http://www.trackmyelectricity.com/learn-more/scope-2-guidance-on-energy-emissions-reporting\n\n## Prepare the df\nC75 = q20[q20['question_number']=='C7.5']\nC75 =C75.drop(['survey_year', 'response_received_date','accounting_period_to','ors_response_id','submission_date','page_name','module_name',\n          'question_number','question_unique_reference','data_point_name','data_point_id','comments','row_name','table_columns_unique_reference','column_number'], axis=1)\nC75=C75.pivot(index=['account_number','organization','row_number'], columns='column_name', values='response_value').reset_index()\nC75=C75.sort_values(by=['account_number','organization','row_number'])\nC75=C75.dropna()\n\n# Clean cols\nC75['C7.5_C2Scope 2, location-based (metric tons CO2e)'] = C75['C7.5_C2Scope 2, location-based (metric tons CO2e)'].astype(float)\nC75['C7.5_C3Scope 2, market-based (metric tons CO2e)'] = C75['C7.5_C3Scope 2, market-based (metric tons CO2e)'].astype(float)\nC75['C7.5_C4Purchased and consumed electricity, heat, steam or cooling (MWh)'] = C75['C7.5_C4Purchased and consumed electricity, heat, steam or cooling (MWh)'].astype(float)\nC75['C7.5_C5Purchased and consumed low-carbon electricity, heat, steam or cooling accounted for in Scope 2 market-based approach (MWh)'] = C75['C7.5_C5Purchased and consumed low-carbon electricity, heat, steam or cooling accounted for in Scope 2 market-based approach (MWh)'].astype(float)\n\n### Create KPI for country\nC75_country=C75.copy()\nC75_country=C75_country.drop(columns=['row_number'])\n\nC75_country=C75_country.groupby(['C7.5_C1Country/Region']).agg({\n    'account_number': 'count', \n    'C7.5_C2Scope 2, location-based (metric tons CO2e)': 'sum',\n    'C7.5_C3Scope 2, market-based (metric tons CO2e)': 'sum',\n    'C7.5_C4Purchased and consumed electricity, heat, steam or cooling (MWh)': 'sum',\n    'C7.5_C5Purchased and consumed low-carbon electricity, heat, steam or cooling accounted for in Scope 2 market-based approach (MWh)': 'sum'\n    })\n\nC75_country=C75_country.rename(columns={'account_number': 'count_companies'})\n\n# C75_country=C75_country.groupby(['C7.5_C1Country/Region']).sum()\nC75_country['mkt_vs_loc']=C75_country['C7.5_C2Scope 2, location-based (metric tons CO2e)']-C75_country['C7.5_C3Scope 2, market-based (metric tons CO2e)']\n\nC75_country['t_co2_per_megawatt_loc']=C75_country['C7.5_C2Scope 2, location-based (metric tons CO2e)']/(C75_country['C7.5_C4Purchased and consumed electricity, heat, steam or cooling (MWh)']+C75_country['C7.5_C5Purchased and consumed low-carbon electricity, heat, steam or cooling accounted for in Scope 2 market-based approach (MWh)'])\nC75_country['t_co2_per_megawatt_loc'].replace(np.inf, np.nan, inplace=True)\n\nC75_country['t_co2_per_megawatt_mkt']=C75_country['C7.5_C3Scope 2, market-based (metric tons CO2e)']/(C75_country['C7.5_C4Purchased and consumed electricity, heat, steam or cooling (MWh)']+C75_country['C7.5_C5Purchased and consumed low-carbon electricity, heat, steam or cooling accounted for in Scope 2 market-based approach (MWh)'])\nC75_country['t_co2_per_megawatt_mkt'].replace(np.inf, np.nan, inplace=True)\n\n\n## Create KPI (0: least efficient; 1: most efficient)\nC75_country['KPI_rank_energy_efficiency_mkt'] = C75_country['t_co2_per_megawatt_mkt'].rank(pct=True)\nC75_country['KPI_rank_energy_efficiency_loc'] = C75_country['t_co2_per_megawatt_loc'].rank(pct=True)\n\nC75_country['KPI_rank_energy_efficiency_mkt'] = 1-C75_country['KPI_rank_energy_efficiency_mkt']\nC75_country['KPI_rank_energy_efficiency_loc'] = 1-C75_country['KPI_rank_energy_efficiency_loc']\n\n# Sort df by KPI values\nC75_country = C75_country.sort_values(by=['KPI_rank_energy_efficiency_mkt']).reset_index()\n\n\n### Create KPI for company\nC75_KPI=C75.copy()\nC75_KPI=C75_KPI.drop(columns=['C7.5_C1Country/Region'])\n\n# Do groupby\nC75_KPI=C75_KPI.groupby(['account_number','organization'], as_index=False).agg({\n    'row_number': 'max', \n    'C7.5_C2Scope 2, location-based (metric tons CO2e)': 'sum',\n    'C7.5_C3Scope 2, market-based (metric tons CO2e)': 'sum',\n    'C7.5_C4Purchased and consumed electricity, heat, steam or cooling (MWh)': 'sum',\n    'C7.5_C5Purchased and consumed low-carbon electricity, heat, steam or cooling accounted for in Scope 2 market-based approach (MWh)': 'sum'\n    })\n\nC75_KPI=C75_KPI.rename(columns={'row_number': 'count_countries'})\n\n# Create extra features\nC75_KPI['mkt_vs_loc']=C75_KPI['C7.5_C2Scope 2, location-based (metric tons CO2e)']-C75_KPI['C7.5_C3Scope 2, market-based (metric tons CO2e)']\nC75_KPI['t_co2_per_megawatt_loc']=C75_KPI['C7.5_C2Scope 2, location-based (metric tons CO2e)']/(C75_KPI['C7.5_C4Purchased and consumed electricity, heat, steam or cooling (MWh)']+C75_KPI['C7.5_C5Purchased and consumed low-carbon electricity, heat, steam or cooling accounted for in Scope 2 market-based approach (MWh)'])\nC75_KPI['t_co2_per_megawatt_loc'].replace(np.inf, np.nan, inplace=True)\nC75_KPI['t_co2_per_megawatt_mkt']=C75_KPI['C7.5_C3Scope 2, market-based (metric tons CO2e)']/(C75_KPI['C7.5_C4Purchased and consumed electricity, heat, steam or cooling (MWh)']+C75_KPI['C7.5_C5Purchased and consumed low-carbon electricity, heat, steam or cooling accounted for in Scope 2 market-based approach (MWh)'])\nC75_KPI['t_co2_per_megawatt_mkt'].replace(np.inf, np.nan, inplace=True)\n\n\n## Create KPIs (0: least efficient; 1: most efficient)\nC75_KPI['KPI_rank_energy_efficiency_mkt'] = C75_KPI['t_co2_per_megawatt_mkt'].rank(pct=True)\nC75_KPI['KPI_rank_energy_efficiency_loc'] = C75_KPI['t_co2_per_megawatt_loc'].rank(pct=True)\n\nC75_KPI['KPI_rank_energy_efficiency_mkt'] = 1-C75_KPI['KPI_rank_energy_efficiency_mkt']\nC75_KPI['KPI_rank_energy_efficiency_loc'] = 1-C75_KPI['KPI_rank_energy_efficiency_loc']\n\n# Sort df by KPI values\nC75_KPI = C75_KPI.sort_values(by=['KPI_rank_energy_efficiency_mkt']).reset_index()\n\npd.set_option('display.max_columns', None)\nC75_KPI\n\n## Save KPI df to folder\nC75_KPI.to_csv('df_KPIs/Climate/C75_company.csv')\nC75_country.to_csv('df_KPIs/Climate/C75_country.csv')\n\n# # Do some checks\n# C75_KPI['t_co2_per_megawatt_mkt'].sort_values(ascending=False)\n# C75_KPI['t_co2_per_megawatt_loc'].sort_values(ascending=False)\n\n##################################################### Q8.2a energy consumption totals ######################################################################\n\nC82a = q20[q20['question_number']=='C8.2a']\nC82a =C82a.drop(['survey_year', 'response_received_date','accounting_period_to','ors_response_id','submission_date','page_name','module_name',\n          'question_number','question_unique_reference','data_point_name','data_point_id','comments','row_name','table_columns_unique_reference','column_number'], axis=1)\nC82a=C82a.pivot(index=['account_number','organization','row_number'], columns='column_name', values='response_value').reset_index()\nC82a=C82a.sort_values(by=['account_number','organization','row_number'])\nC82a=C82a.dropna()\n\n# Drop row_number, as there is only 1 value\nC82a=C82a.drop(columns=['row_number'])\n\n\n## Clean cols\nC82a['C8.2a_C2MWh from renewable sources'] = C82a['C8.2a_C2MWh from renewable sources'].astype(float)\nC82a['C8.2a_C3MWh from non-renewable sources'] = C82a['C8.2a_C3MWh from non-renewable sources'].astype(float)\nC82a['C8.2a_C4Total (renewable and non-renewable) MWh'] = C82a['C8.2a_C4Total (renewable and non-renewable) MWh'].astype(float)\n\nC82a['C8.2a_C1Heating value'].value_counts(dropna=True)\n\n\n## Binarize 'C8.2a_C1Heating value'\none_hot = pd.get_dummies(C82a['C8.2a_C1Heating value']).add_prefix('C8.2a_C1Heating value_')\n# df = df.drop('C8.2a_C1Heating value', axis = 1)\nC82a = C82a.join(one_hot)\n\n\n## Create % of green energy consumption \nC82a['energy_consumption_mwh_pctg_renewables']=C82a['C8.2a_C2MWh from renewable sources']/C82a['C8.2a_C4Total (renewable and non-renewable) MWh']\nC82a['energy_consumption_mwh_pctg_renewables'].replace(np.inf, np.nan, inplace=True)\n\n# C82a[C82a['energy_consumption_mwh_pctg_renewables']>0]\n\n\n## Create KPI (0: least green; 1: most green)\nC82a['KPI_rank_energy_renewables'] = C82a['energy_consumption_mwh_pctg_renewables'].rank(pct=True)\n# C82a['KPI_rank_energy_efficiency_mkt'] = 1-C82a['KPI_rank_energy_efficiency_mkt']\n\n# Sort df by KPI values\nC82a = C82a.sort_values(by=['KPI_rank_energy_renewables']).reset_index()\n\n\n## Save KPI df to folder\nC82a.to_csv('df_KPIs/Climate/C82a.csv')\n\n\n## Visualize\n#pd.set_option('display.max_columns', None)\n#C82a.head(5)\n# C82a['energy_consumption_mwh_pctg_renewables'].describe()\n# sns.distplot(C82a['energy_consumption_mwh_pctg_renewables'])\n\n\n########################### Q8.2d details on the electricity, heat, steam, and cooling your organization has generated and consumed ############################\n\nC82d = q20[q20['question_number']=='C8.2d']\nC82d =C82d.drop(['survey_year', 'response_received_date','accounting_period_to','ors_response_id','submission_date','page_name','module_name',\n          'question_number','question_unique_reference','data_point_name','data_point_id','comments','row_name','table_columns_unique_reference','column_number'], axis=1)\nC82d=C82d.pivot(index=['account_number','organization','row_number'], columns='column_name', values='response_value').reset_index()\nC82d=C82d.sort_values(by=['account_number','organization','row_number'])\nC82d=C82d.dropna()\n\n## Clean cols\nC82d['C8.2d_C1Total Gross generation (MWh)'] = C82d['C8.2d_C1Total Gross generation (MWh)'].astype(float)\nC82d['C8.2d_C2Generation that is consumed by the organization (MWh)'] = C82d['C8.2d_C2Generation that is consumed by the organization (MWh)'].astype(float)\nC82d['C8.2d_C3Gross generation from renewable sources (MWh)'] = C82d['C8.2d_C3Gross generation from renewable sources (MWh)'].astype(float)\nC82d['C8.2d_C4Generation from renewable sources that is consumed by the organization (MWh)'] = C82d['C8.2d_C4Generation from renewable sources that is consumed by the organization (MWh)'].astype(float)\n# C82d['row_number'].value_counts(dropna=True)\n\n\n### Create KPI for company\nC82d_KPI=C82d.copy()\n# C82d_KPI=C82d_KPI.drop(columns=['C7.5_C1Country/Region'])\n\nC82d_KPI=C82d_KPI.groupby(['account_number','organization'], as_index=False).agg({\n    'row_number': 'max', \n    'C8.2d_C1Total Gross generation (MWh)': 'sum',\n    'C8.2d_C2Generation that is consumed by the organization (MWh)': 'sum',\n    'C8.2d_C3Gross generation from renewable sources (MWh)': 'sum',\n    'C8.2d_C4Generation from renewable sources that is consumed by the organization (MWh)': 'sum'\n    })\n\nC82d_KPI=C82d_KPI.rename(columns={'row_number': 'count_rows'})\n\n\nC82d_KPI['electricity_generation_mwh_renewables_pctg']=C82d_KPI['C8.2d_C3Gross generation from renewable sources (MWh)']/C82d_KPI['C8.2d_C1Total Gross generation (MWh)']\nC82d_KPI['electricity_consumption_generated_mwh_renewables_pctg']=C82d_KPI['C8.2d_C4Generation from renewable sources that is consumed by the organization (MWh)']/C82d_KPI['C8.2d_C2Generation that is consumed by the organization (MWh)']\n\nC82d_KPI['renewables_sold']=C82d_KPI['C8.2d_C3Gross generation from renewable sources (MWh)']-C82d_KPI['C8.2d_C4Generation from renewable sources that is consumed by the organization (MWh)']\nC82d_KPI['electricity_generation_contribution_mwh_renewables_pctg']=C82d_KPI['renewables_sold']/C82d_KPI['C8.2d_C1Total Gross generation (MWh)']\n\nC82d_KPI['electricity_generation_mwh_renewables_pctg'].replace(np.inf, np.nan, inplace=True)\nC82d_KPI['electricity_consumption_generated_mwh_renewables_pctg'].replace(np.inf, np.nan, inplace=True)\nC82d_KPI['electricity_generation_contribution_mwh_renewables_pctg'].replace(np.inf, np.nan, inplace=True)\n\n# Check out values:\n# C82d_KPI[(C82d_KPI['electricity_generation_mwh_renewables_pctg']<0) | (C82d_KPI['electricity_generation_mwh_renewables_pctg']>1)]\n# C82d_KPI[(C82d_KPI['electricity_consumption_generated_mwh_renewables_pctg']<0) | (C82d_KPI['electricity_consumption_generated_mwh_renewables_pctg']>1)]\nC82d_KPI[(C82d_KPI['electricity_generation_contribution_mwh_renewables_pctg']<0) | (C82d_KPI['electricity_generation_contribution_mwh_renewables_pctg']>1)]\n\n\n# Correct wrongly assigned values:\nC82d_KPI.loc[C82d_KPI['electricity_consumption_generated_mwh_renewables_pctg']>1, 'electricity_consumption_generated_mwh_renewables_pctg'] = 1\nC82d_KPI.loc[C82d_KPI['electricity_generation_contribution_mwh_renewables_pctg']<0, 'electricity_generation_contribution_mwh_renewables_pctg'] = 0\n\n\n## Create KPI (0: least green; 1: most green)\nC82d_KPI['KPI_rank_electricty_generated_from_renewables'] = C82d_KPI['electricity_generation_mwh_renewables_pctg'].rank(pct=True)\nC82d_KPI['KPI_rank_electricty_consumed_generated_mwh_renewables'] = C82d_KPI['electricity_consumption_generated_mwh_renewables_pctg'].rank(pct=True)\nC82d_KPI['KPI_rank_electricty_generated_from_renewables_sold'] = C82d_KPI['electricity_generation_contribution_mwh_renewables_pctg'].rank(pct=True)\n# C82a['KPI_rank_energy_efficiency_mkt'] = 1-C82a['KPI_rank_energy_efficiency_mkt']\n\n# Sort df by KPI values\nC82d_KPI = C82d_KPI.sort_values(by=['KPI_rank_electricty_generated_from_renewables']).reset_index()\n\n\n## Save KPI df to folder\nC82d_KPI.to_csv('df_KPIs/Climate/C82d_KPI.csv')\n\n## Visualize\n# pd.set_option('display.max_columns', None)\nC82d_KPI.head(5)\n\n\n# ### Q6.1\n# C61 = q20[q20['question_number']=='C6.1']\n# C61 =C61.drop(['survey_year', 'response_received_date','accounting_period_to','ors_response_id','submission_date','page_name','module_name',\n#           'question_number','question_unique_reference','data_point_name','data_point_id','comments','row_name','table_columns_unique_reference','column_number'], axis=1)\n# C61=C61.pivot(index=['account_number','organization','row_number'], columns='column_name', values='response_value').reset_index()\n# C61=C61.sort_values(by=['account_number','organization','row_number'])\n\n# C61=C61.dropna()\n# C61['row_number'].value_counts(dropna=True)\n\n\n\n################################################################ TABLE 2.3a details of risks identified #####################################################\n\nC23a = q20[q20['question_number']=='C2.3a']\nC23a =C23a.drop(['survey_year', 'response_received_date','accounting_period_to','ors_response_id','submission_date','page_name','module_name',\n          'question_number','question_unique_reference','data_point_name','data_point_id','comments','row_name','table_columns_unique_reference','column_number'], axis=1)\nC23a=C23a.pivot(index=['account_number','organization','row_number'], columns='column_name', values='response_value').reset_index()\nC23a=C23a.sort_values(by=['account_number','organization','row_number'])\n# C23a=C23a.dropna()\n\n# Drop row_number, as there is only 1 value\n# C23a=C82a.drop(columns=['row_number'])\n\n\n## Clean cols\nC23a[C23a['row_number']==0]\nC23a = C23a[C23a['row_number'] != 0] # All row number = 0 are NaN\n\nC23a['C2.3a_C11Potential financial impact figure (currency)'] = C23a['C2.3a_C11Potential financial impact figure (currency)'].astype(float)\nC23a['C2.3a_C12Potential financial impact figure – minimum (currency)'] = C23a['C2.3a_C12Potential financial impact figure – minimum (currency)'].astype(float)\nC23a['C2.3a_C13Potential financial impact figure – maximum (currency)'] = C23a['C2.3a_C13Potential financial impact figure – maximum (currency)'].astype(float)\nC23a['C2.3a_C15Cost of response to risk'] = C23a['C2.3a_C15Cost of response to risk'].astype(float)\n\nC23a =C23a.drop([\n    'C2.3a_C14Explanation of financial impact figure',\n    'C2.3a_C17Comment',\n    'C2.3a_C1Identifier',\n    'C2.3a_C5Climate risk type mapped to traditional financial services industry risk classification'\n    ], axis=1)\n\n\n## Calculate financial impact into a single col\nC23a['risk_financial_impact'] = C23a['C2.3a_C11Potential financial impact figure (currency)']\nC23a.loc[C23a['risk_financial_impact'].isnull(), 'risk_financial_impact'] = (C23a['C2.3a_C12Potential financial impact figure – minimum (currency)'] + C23a['C2.3a_C13Potential financial impact figure – maximum (currency)'])/2\n\n# C23a =C23a.drop([\n#     'C2.3a_C11Potential financial impact figure (currency)', \n#     'C2.3a_C12Potential financial impact figure – minimum (currency)', \n#     'C2.3a_C13Potential financial impact figure – maximum (currency)'\n#     ], axis=1)\n\n\n### Add time horizon in years\n# Q2.1a  Extract and assign short-, medium- and long-term time horizons definitions\nC21a = q20[q20['question_number']=='C2.1a']\nC21a =C21a.drop(['survey_year', 'response_received_date','accounting_period_to','ors_response_id','submission_date','page_name','module_name',\n          'question_number','question_unique_reference','data_point_name','data_point_id','comments','row_name','table_columns_unique_reference','column_number'], axis=1)\nC21a=C21a.pivot(index=['account_number','organization','row_number'], columns='column_name', values='response_value').reset_index()\nC21a =C21a.drop(['C2.1a_C3Comment', 'C2.1a_C1From (years)'], axis=1)\nC21a=C21a.pivot(index=['account_number','organization'], columns='row_number', values='C2.1a_C2To (years)').reset_index()\n\nC23a = C23a.merge(C21a, how='left', left_on='account_number', right_on='account_number')\n\n# Assign horizon years values\n\"\"\"\nTIME HORIZON:\nShort-term: Use company definition from 2.1\nMedium-term: Use company definition from 2.1\nLong-term: Use company definition from 2.1\nUnknown: np.nan\n\"\"\"\nC23a['time_horizon_years'] = np.nan\nC23a.loc[C23a['C2.3a_C7Time horizon']=='Short-term', 'time_horizon_years'] = C23a[1]\nC23a.loc[C23a['C2.3a_C7Time horizon']=='Medium-term', 'time_horizon_years'] = C23a[2]\nC23a.loc[C23a['C2.3a_C7Time horizon']=='Long-term', 'time_horizon_years'] = C23a[3]\n\nC23a['time_horizon_years'] = C23a['time_horizon_years'].astype(float)\n\nC23a =C23a.drop(['organization_y', 1, 2, 3], axis=1)\n\n### Categorize numerically categoricals, between 0 and 1\n## Likelihood\n\"\"\"\nLIKELIHOOD:\n    Virtually certain = 8/8\n    Very likely = 7/8\n    Likely = 6/8\n    Likely More likely than not = 5/7\n    About as likely as not =  = 4/7\n    Unlikely = 3/7\n    Very unlikely = 2/7\n    Exceptionally unlikely = 1/7\n    Unknown = np.nan\n\"\"\"\nC23a['risk_likelihood'] = np.nan\nC23a.loc[C23a['C2.3a_C8Likelihood']=='Virtually certain', 'risk_likelihood'] = 8/8\nC23a.loc[C23a['C2.3a_C8Likelihood']=='Very likely', 'risk_likelihood'] = 7/8\nC23a.loc[C23a['C2.3a_C8Likelihood']=='Likely', 'risk_likelihood'] = 6/8\nC23a.loc[C23a['C2.3a_C8Likelihood']=='More likely than not', 'risk_likelihood'] = 5/8\nC23a.loc[C23a['C2.3a_C8Likelihood']=='About as likely as not', 'risk_likelihood'] = 4/8\nC23a.loc[C23a['C2.3a_C8Likelihood']=='Unlikely', 'risk_likelihood'] = 3/8\nC23a.loc[C23a['C2.3a_C8Likelihood']=='Very unlikely', 'risk_likelihood'] = 2/8\nC23a.loc[C23a['C2.3a_C8Likelihood']=='Exceptionally unlikely', 'risk_likelihood'] = 1/8\n# C23a =C23a.drop(['C2.3a_C8Likelihood'], axis=1)\n\n\n## Magnitude\n\"\"\"\nMAGNITUDE\n    High = 5/5\n    Medium-high = 4/5\n    Medium = 3/5\n    Medium-low = 2/5\n    Low = 1/5\n    Unknown = np.nan\n\"\"\"\nC23a['risk_impact_magnitude'] = np.nan\nC23a.loc[C23a['C2.3a_C9Magnitude of impact']=='High', 'risk_impact_magnitude'] = 5/5\nC23a.loc[C23a['C2.3a_C9Magnitude of impact']=='Medium-high', 'risk_impact_magnitude'] = 4/5\nC23a.loc[C23a['C2.3a_C9Magnitude of impact']=='Medium', 'risk_impact_magnitude'] = 3/5\nC23a.loc[C23a['C2.3a_C9Magnitude of impact']=='Medium-low', 'risk_impact_magnitude'] = 2/5\nC23a.loc[C23a['C2.3a_C9Magnitude of impact']=='Low', 'risk_impact_magnitude'] = 1/5\n# C23a =C23a.drop(['C2.3a_C8Likelihood'], axis=1)\n\nC23a.head(5)\n\n\n\n## Do a groupby company\nC23a_KPI=C23a.copy()\nC23a_KPI=C23a_KPI.drop(columns=[\n    'C2.3a_C10Are you able to provide a potential financial impact figure?',\n    'C2.3a_C16Description of response and explanation of cost calculation',\n    'C2.3a_C2Where in the value chain does the risk driver occur?',\n    'C2.3a_C3Risk type & Primary climate-related risk driver',\n    'C2.3a_C3Risk type & Primary climate-related risk driver_G',\n    'C2.3a_C4Primary potential financial impact',\n    'C2.3a_C7Time horizon',\n    'C2.3a_C9Magnitude of impact',\n    'C2.3a_C8Likelihood'\n    ])\n\n\n## Groupby company\n# wm_risk_financial_impact = lambda x: np.average(x, weights=C23a_KPI.loc[x.index, 'risk_financial_impact'])\n# wm_time_horizon_years = lambda x: np.average(x, weights=C23a_KPI.loc[x.index, 'risk_financial_impact'])\n# wm_risk_likelihood = lambda x: np.average(x, weights=C23a_KPI.loc[x.index, 'risk_financial_impact'])\n# wm_risk_impact_magnitude = lambda x: np.average(x, weights=C23a_KPI.loc[x.index, 'risk_financial_impact'])\n# wm_risk_index = lambda x: np.average(x, weights=C23a_KPI.loc[x.index, 'risk_financial_impact'])\n\nC23a_KPI=C23a_KPI.groupby(['account_number','organization_x'], as_index=False).agg(\n    row_number = ('row_number', 'max'),\n    C11_Potential_financial_impact = ('C2.3a_C11Potential financial impact figure (currency)', 'sum'),\n    C12_Potential_financial_impact_range_min = ('C2.3a_C12Potential financial impact figure – minimum (currency)', 'sum'),\n    C12_Potential_financial_impact_range_max = ('C2.3a_C13Potential financial impact figure – maximum (currency)', 'sum'),\n    C15_Cost_of_response_to_risk = ('C2.3a_C15Cost of response to risk', 'sum'),\n    risk_financial_impact = ('risk_financial_impact', 'sum'),\n    time_horizon_years = ('time_horizon_years', 'mean'),\n    risk_likelihood=('risk_likelihood', 'mean'),\n    risk_impact_magnitude=('risk_impact_magnitude', 'mean')\n    )\n\nC23a_KPI=C23a_KPI.rename(columns={'row_number': 'count_risks'})\n\n\n### Calculate final features and KPIs\n## Likelihood * Magnitude / Years\nC23a_KPI['risk_index'] = (C23a_KPI['risk_likelihood'] * C23a_KPI['risk_impact_magnitude']) / C23a_KPI['time_horizon_years'] \nC23a_KPI['risk_index'].replace(np.inf, np.nan, inplace=True)\n\n## ROI_risk_mitigation\nC23a_KPI['ROI_risk_mitigation'] = C23a_KPI['risk_financial_impact']/C23a_KPI['C15_Cost_of_response_to_risk']\nC23a_KPI['ROI_risk_mitigation'].replace(np.inf, np.nan, inplace=True)\n\n## Create KPI (0: least at risk; 1: most at risk)\nC23a_KPI['KPI_rank_risk_response_cost'] = C23a_KPI['C15_Cost_of_response_to_risk'].rank(pct=True)\nC23a_KPI['KPI_rank_risk_financial_impact'] = C23a_KPI['risk_financial_impact'].rank(pct=True)\nC23a_KPI['KPI_rank_risk_time_horizon'] = C23a_KPI['time_horizon_years'].rank(pct=True)\nC23a_KPI['KPI_rank_risk_likelihood'] = C23a_KPI['risk_likelihood'].rank(pct=True)\nC23a_KPI['KPI_rank_risk_impact_magnitude'] = C23a_KPI['risk_impact_magnitude'].rank(pct=True)\nC23a_KPI['KPI_rank_risk_index'] = C23a_KPI['risk_index'].rank(pct=True)\nC23a_KPI['KPI_rank_risk_mitigation'] = C23a_KPI['ROI_risk_mitigation'].rank(pct=True)\n\n# Revert time horizon to align 0 (good) to 1 (bad) as the other KPIs\nC23a_KPI['KPI_rank_risk_time_horizon'] = 1-C23a_KPI['KPI_rank_risk_time_horizon']\nC23a_KPI['KPI_rank_risk_mitigation'] = 1-C23a_KPI['KPI_rank_risk_mitigation']\n# C23a_KPI[''] = 1-C23a_KPI['']\n\n# Sort df by KPI values\nC23a_KPI = C23a_KPI.sort_values(by=['KPI_rank_risk_index']).reset_index()\n\n\n\n## Save KPI df to folder\nC23a_KPI.to_csv('df_KPIs/Climate/C23a_KPI.csv')\n\n\n## Visualize results\n# pd.set_option('display.max_columns', None)\n# C23a.head(5)\n#C23a_KPI.head(5)\n# C23a[C23a['C2.3a_C7Time horizon']=='Unknown']\n# C23a[C23a['C2.3a_C10Are you able to provide a potential financial impact figure?']=='No, we do not have this figure']\n# C23a[C23a['C2.3a_C8Likelihood']=='Unknown']\n\n\n############################################################ TABLE 2.4a climate-related opportunities ##########################################################\n\nC24a = q20[q20['question_number']=='C2.4a']\nC24a =C24a.drop(['survey_year', 'response_received_date','accounting_period_to','ors_response_id','submission_date','page_name','module_name',\n          'question_number','question_unique_reference','data_point_name','data_point_id','comments','row_name','table_columns_unique_reference','column_number'], axis=1)\nC24a=C24a.pivot(index=['account_number','organization','row_number'], columns='column_name', values='response_value').reset_index()\nC24a=C24a.sort_values(by=['account_number','organization','row_number'])\n# C23a=C23a.dropna()\n\n## Clean up df and cols\nC24a[C24a['row_number']==0]\nC24a = C24a[C24a['row_number'] != 0] # All row number = 0 are NaN\n\nC24a['C2.4a_C11Potential financial impact figure (currency)'] = C24a['C2.4a_C11Potential financial impact figure (currency)'].astype(float)\nC24a['C2.4a_C12Potential financial impact figure – minimum (currency)'] = C24a['C2.4a_C12Potential financial impact figure – minimum (currency)'].astype(float)\nC24a['C2.4a_C13Potential financial impact figure – maximum (currency)'] = C24a['C2.4a_C13Potential financial impact figure – maximum (currency)'].astype(float)\nC24a['C2.4a_C15Cost to realize opportunity'] = C24a['C2.4a_C15Cost to realize opportunity'].astype(float)\n\nC24a =C24a.drop([\n    'C2.4a_C14Explanation of financial impact figure',\n    'C2.4a_C17Comment',\n    'C2.4a_C1Identifier',\n    'C2.4a_C16Strategy to realize opportunity and explanation of cost calculation'\n    ], axis=1)\n\n\n## Calculate financial impact into a single col\nC24a['opportunity_financial_impact'] = C24a['C2.4a_C11Potential financial impact figure (currency)']\nC24a.loc[C24a['opportunity_financial_impact'].isnull(), 'opportunity_financial_impact'] = (C24a['C2.4a_C12Potential financial impact figure – minimum (currency)'] + C24a['C2.4a_C13Potential financial impact figure – maximum (currency)'])/2\n\n\n## Add time horizon in years (Merge with horizon definition in 2.1)\n\"\"\"\nTIME HORIZON:\nShort-term: Use company definition from 2.1\nMedium-term: Use company definition from 2.1\nLong-term: Use company definition from 2.1\nUnknown: np.nan\n\"\"\"\nC24a = C24a.merge(C21a, how='left', left_on='account_number', right_on='account_number')\n\n## Add values expressed in years\nC24a['time_horizon_years'] = np.nan\nC24a.loc[C24a['C2.4a_C7Time horizon']=='Short-term', 'time_horizon_years'] = C24a[1]\nC24a.loc[C24a['C2.4a_C7Time horizon']=='Medium-term', 'time_horizon_years'] = C24a[2]\nC24a.loc[C24a['C2.4a_C7Time horizon']=='Long-term', 'time_horizon_years'] = C24a[3]\n\nC24a['time_horizon_years'] = C24a['time_horizon_years'].astype(float)\n\nC24a =C24a.drop(['organization_y', 1, 2, 3], axis=1)\n\n\n### Categorize categorical into o to 1 values\n## Likelihood\n\"\"\"\nLIKELIHOOD:\n    Virtually certain = 8/8\n    Very likely = 7/8\n    Likely = 6/8\n    More likely than not = 5/8\n    About as likely as not =  = 4/8\n    Unlikely = 3/8\n    Very unlikely = 2/8\n    Exceptionally unlikely = 1/8\n    Unknown = np.nan\n\"\"\"\nC24a['opportunity_likelihood'] = np.nan\nC24a.loc[C24a['C2.4a_C8Likelihood']=='Virtually certain', 'opportunity_likelihood'] = 8/8\nC24a.loc[C24a['C2.4a_C8Likelihood']=='Very likely', 'opportunity_likelihood'] = 7/8\nC24a.loc[C24a['C2.4a_C8Likelihood']=='Likely', 'opportunity_likelihood'] = 6/8\nC24a.loc[C24a['C2.4a_C8Likelihood']=='More likely than not', 'opportunity_likelihood'] = 5/8\nC24a.loc[C24a['C2.4a_C8Likelihood']=='About as likely as not', 'opportunity_likelihood'] = 4/8\nC24a.loc[C24a['C2.4a_C8Likelihood']=='Unlikely', 'opportunity_likelihood'] = 3/8\nC24a.loc[C24a['C2.4a_C8Likelihood']=='Very unlikely', 'opportunity_likelihood'] = 2/8\nC24a.loc[C24a['C2.4a_C8Likelihood']=='Exceptionally unlikely', 'opportunity_likelihood'] = 1/8\n# C24a =C24a.drop(['C2.4a_C8Likelihood'], axis=1)\n\n\n## Magnitude\n\"\"\"\nMAGNITUDE\n    High = 5/5\n    Medium-high = 4/5\n    Medium = 3/5\n    Medium-low = 2/5\n    Low = 1/5\n    Unknown = np.nan\n\"\"\"\nC24a['opportunity_impact_magnitude'] = np.nan\nC24a.loc[C24a['C2.4a_C9Magnitude of impact']=='High', 'opportunity_impact_magnitude'] = 5/5\nC24a.loc[C24a['C2.4a_C9Magnitude of impact']=='Medium-high', 'opportunity_impact_magnitude'] = 4/5\nC24a.loc[C24a['C2.4a_C9Magnitude of impact']=='Medium', 'opportunity_impact_magnitude'] = 3/5\nC24a.loc[C24a['C2.4a_C9Magnitude of impact']=='Medium-low', 'opportunity_impact_magnitude'] = 2/5\nC24a.loc[C24a['C2.4a_C9Magnitude of impact']=='Low', 'opportunity_impact_magnitude'] = 1/5\n# C24a =C24a.drop(['C2.4a_C8Likelihood'], axis=1)\n\n\n## Do a groupby \nC24a_KPI=C24a.copy()\nC24a_KPI=C24a_KPI.drop(columns=[\n    'C2.4a_C10Are you able to provide a potential financial impact figure?',\n    'C2.4a_C2Where in the value chain does the opportunity occur?',\n    'C2.4a_C3Opportunity type',\n    'C2.4a_C4Primary climate-related opportunity driver',\n    'C2.4a_C5Primary potential financial impact',\n    'C2.4a_C6Company-specific description',\n    'C2.4a_C7Time horizon',\n    'C2.4a_C9Magnitude of impact',\n    'C2.4a_C8Likelihood'\n    ])\n\n# wm_risk_financial_impact = lambda x: np.average(x, weights=C23a_KPI.loc[x.index, 'risk_financial_impact'])\n# wm_time_horizon_years = lambda x: np.average(x, weights=C23a_KPI.loc[x.index, 'risk_financial_impact'])\n# wm_risk_likelihood = lambda x: np.average(x, weights=C23a_KPI.loc[x.index, 'risk_financial_impact'])\n# wm_risk_impact_magnitude = lambda x: np.average(x, weights=C23a_KPI.loc[x.index, 'risk_financial_impact'])\n# wm_risk_index = lambda x: np.average(x, weights=C23a_KPI.loc[x.index, 'risk_financial_impact'])\n\nC24a_KPI=C24a_KPI.groupby(['account_number','organization_x'], as_index=False).agg(\n    row_number = ('row_number', 'max'),\n    C11_Potential_financial_impact = ('C2.4a_C11Potential financial impact figure (currency)', 'sum'),\n    C12_Potential_financial_impact_range_min = ('C2.4a_C12Potential financial impact figure – minimum (currency)', 'sum'),\n    C12_Potential_financial_impact_range_max = ('C2.4a_C13Potential financial impact figure – maximum (currency)', 'sum'),\n    C15_Cost_to_realize_opportunity = ('C2.4a_C15Cost to realize opportunity', 'sum'),\n    opportunity_financial_impact = ('opportunity_financial_impact', 'sum'),\n    time_horizon_years = ('time_horizon_years', 'mean'),\n    opportunity_likelihood=('opportunity_likelihood', 'mean'),\n    opportunity_impact_magnitude=('opportunity_impact_magnitude', 'mean')\n    )\n\nC24a_KPI=C24a_KPI.rename(columns={'row_number': 'count_opportunities'})\n\n\n## create new features:\n## Likelihood * Magnitude / Years\nC24a_KPI['opportunity_index'] = (C24a_KPI['opportunity_likelihood'] * C24a_KPI['opportunity_impact_magnitude']) / C24a_KPI['time_horizon_years'] \nC24a_KPI['opportunity_index'].replace(np.inf, np.nan, inplace=True)\n\n## ROI_risk_mitigation\nC24a_KPI['ROI_opportunity'] = C24a_KPI['opportunity_financial_impact']/C24a_KPI['C15_Cost_to_realize_opportunity']\nC24a_KPI['ROI_opportunity'].replace(np.inf, np.nan, inplace=True)\n\n## Create KPI (0: least at risk; 1: most at risk)\nC24a_KPI['KPI_rank_opportunity_realization_cost'] = C24a_KPI['C15_Cost_to_realize_opportunity'].rank(pct=True)\nC24a_KPI['KPI_rank_opportunity_financial_impact'] = C24a_KPI['opportunity_financial_impact'].rank(pct=True)\nC24a_KPI['KPI_rank_opportunity_time_horizon'] = C24a_KPI['time_horizon_years'].rank(pct=True)\nC24a_KPI['KPI_rank_opportunity_likelihood'] = C24a_KPI['opportunity_likelihood'].rank(pct=True)\nC24a_KPI['KPI_rank_opportunity_impact_magnitude'] = C24a_KPI['opportunity_impact_magnitude'].rank(pct=True)\nC24a_KPI['KPI_rank_opportunity_index'] = C24a_KPI['opportunity_index'].rank(pct=True)\nC24a_KPI['KPI_rank_opportunity_creation'] = C24a_KPI['ROI_opportunity'].rank(pct=True)\n\n# Revert time horizon to align 0 (good) to 1 (bad) as the other KPIs\n# C24a_KPI['KPI_rank_opportunity_time_horizon'] = 1-C24a_KPI['KPI_rank_opportunity_time_horizon']\n# C24a_KPI['KPI_rank_opportunity_creation'] = 1-C24a_KPI['KPI_rank_risk_mitigation']\n\n# Sort df by KPI values\nC24a_KPI = C24a_KPI.sort_values(by=['KPI_rank_opportunity_index']).reset_index()\n\npd.set_option('display.max_columns', None)\nC24a_KPI.head(5)\n\n## Save KPI df to folder\nC24a_KPI.to_csv('df_KPIs/Climate/C24a_KPI.csv')\n\n## Visualize results:\n\n#C24a.head(5)\n#C24a_KPI.head(5)\n\n################################################################################################################################################################\n################################################################################################################################################################\n################################################################################################################################################################\n###################################################                                                      #######################################################\n###################################################                 W A T E R - 2 0 2 0                  #######################################################\n###################################################                                                      #######################################################\n################################################################################################################################################################\n################################################################################################################################################################\n################################################################################################################################################################\n\n\n####################################################### TABLE 1.1 Water importance ############################################################################\n\nW11 = w20[w20['question_number']=='W1.1']\nW11 =W11.drop(['survey_year', 'response_received_date','accounting_period_to','ors_response_id','submission_date','page_name','module_name',\n          'question_number','question_unique_reference','data_point_name','data_point_id','comments','table_columns_unique_reference','column_number'], axis=1)\nW11=W11.pivot(index=['account_number','organization','row_number','row_name'], columns='column_name', values='response_value').reset_index()\nW11=W11.sort_values(by=['account_number','organization','row_number'])\n# C11=C11.dropna()\n\n# Pivot dataset to manage data and calculations \nW11_p1 = W11.drop(['W1.1_C3Please explain', 'row_number', 'W1.1_C2Indirect use importance rating'], axis=1)\nW11_p2 = W11.drop(['W1.1_C3Please explain', 'row_number', 'W1.1_C1Direct use importance rating'], axis=1)\n\nW11_p1 = W11_p1.pivot(index=['account_number','organization'], columns='row_name', values='W1.1_C1Direct use importance rating').reset_index()\nW11_p1.columns = ['account_number', 'organization', 'direct_use_importance_sufficient_freshwater', 'direct_use_importance_sufficient_recycled']\n\nW11_p2 = W11_p2.pivot(index=['account_number','organization'], columns='row_name', values='W1.1_C2Indirect use importance rating').reset_index()\nW11_p2.columns = ['account_number', 'organization', 'indirect_use_importance_sufficient_freshwater', 'indirect_use_importance_sufficient_recycled']\n\nW11_c = W11_p1.merge(W11_p2, how='left', on='account_number')\n\nW11_c.drop(['organization_y'], axis=1, inplace=True)\nW11_c.rename(columns={'organization_x': 'organization'}, inplace=True)\n\n\n### Categorize columns into numericals\n# W11_c['direct_use_importance_sufficient_recycled'].value_counts(dropna=True)\n## Water importance\n\"\"\"\nIMPORTANCE:\n    'Vital' = 5/5\n    'Important' = 4/5\n    'Neutral' = 3/5\n    'Not very important' = 2/5\n    'Not important at all' = 1/5\n    'Have not evaluated' = np.nan\n\"\"\"\nW11_c.loc[W11_c['direct_use_importance_sufficient_freshwater']=='Vital', 'direct_use_importance_sufficient_freshwater'] = 5/5\nW11_c.loc[W11_c['direct_use_importance_sufficient_freshwater']=='Important', 'direct_use_importance_sufficient_freshwater'] = 4/5\nW11_c.loc[W11_c['direct_use_importance_sufficient_freshwater']=='Neutral', 'direct_use_importance_sufficient_freshwater'] = 3/5\nW11_c.loc[W11_c['direct_use_importance_sufficient_freshwater']=='Not very important', 'direct_use_importance_sufficient_freshwater'] = 2/5\nW11_c.loc[W11_c['direct_use_importance_sufficient_freshwater']=='Not important at all', 'direct_use_importance_sufficient_freshwater'] = 1/5\nW11_c.loc[W11_c['direct_use_importance_sufficient_freshwater']=='Have not evaluated', 'direct_use_importance_sufficient_freshwater'] = np.nan\n\nW11_c.loc[W11_c['direct_use_importance_sufficient_recycled']=='Vital', 'direct_use_importance_sufficient_recycled'] = 5/5\nW11_c.loc[W11_c['direct_use_importance_sufficient_recycled']=='Important', 'direct_use_importance_sufficient_recycled'] = 4/5\nW11_c.loc[W11_c['direct_use_importance_sufficient_recycled']=='Neutral', 'direct_use_importance_sufficient_recycled'] = 3/5\nW11_c.loc[W11_c['direct_use_importance_sufficient_recycled']=='Not very important', 'direct_use_importance_sufficient_recycled'] = 2/5\nW11_c.loc[W11_c['direct_use_importance_sufficient_recycled']=='Not important at all', 'direct_use_importance_sufficient_recycled'] = 1/5\nW11_c.loc[W11_c['direct_use_importance_sufficient_recycled']=='Have not evaluated', 'direct_use_importance_sufficient_recycled'] = np.nan\n\nW11_c.loc[W11_c['indirect_use_importance_sufficient_freshwater']=='Vital', 'indirect_use_importance_sufficient_freshwater'] = 5/5\nW11_c.loc[W11_c['indirect_use_importance_sufficient_freshwater']=='Important', 'indirect_use_importance_sufficient_freshwater'] = 4/5\nW11_c.loc[W11_c['indirect_use_importance_sufficient_freshwater']=='Neutral', 'indirect_use_importance_sufficient_freshwater'] = 3/5\nW11_c.loc[W11_c['indirect_use_importance_sufficient_freshwater']=='Not very important', 'indirect_use_importance_sufficient_freshwater'] = 2/5\nW11_c.loc[W11_c['indirect_use_importance_sufficient_freshwater']=='Not important at all', 'indirect_use_importance_sufficient_freshwater'] = 1/5\nW11_c.loc[W11_c['indirect_use_importance_sufficient_freshwater']=='Have not evaluated', 'indirect_use_importance_sufficient_freshwater'] = np.nan\n\nW11_c.loc[W11_c['indirect_use_importance_sufficient_recycled']=='Vital', 'indirect_use_importance_sufficient_recycled'] = 5/5\nW11_c.loc[W11_c['indirect_use_importance_sufficient_recycled']=='Important', 'indirect_use_importance_sufficient_recycled'] = 4/5\nW11_c.loc[W11_c['indirect_use_importance_sufficient_recycled']=='Neutral', 'indirect_use_importance_sufficient_recycled'] = 3/5\nW11_c.loc[W11_c['indirect_use_importance_sufficient_recycled']=='Not very important', 'indirect_use_importance_sufficient_recycled'] = 2/5\nW11_c.loc[W11_c['indirect_use_importance_sufficient_recycled']=='Not important at all', 'indirect_use_importance_sufficient_recycled'] = 1/5\nW11_c.loc[W11_c['indirect_use_importance_sufficient_recycled']=='Have not evaluated', 'indirect_use_importance_sufficient_recycled'] = np.nan\n# C24a =C24a.drop(['C2.4a_C8Likelihood'], axis=1)\n\n\n## Calcolate new features\n# Composite metrics\nW11_c['direct_use_importance'] = W11_c['direct_use_importance_sufficient_freshwater'] + W11_c['direct_use_importance_sufficient_freshwater']\nW11_c['indirect_use_importance'] = W11_c['indirect_use_importance_sufficient_freshwater'] + W11_c['indirect_use_importance_sufficient_freshwater']\n\nW11_c['use_importance_freshwater'] = W11_c['direct_use_importance_sufficient_freshwater'] + W11_c['indirect_use_importance_sufficient_freshwater']\nW11_c['use_importance_recycled'] = W11_c['direct_use_importance_sufficient_freshwater'] + W11_c['indirect_use_importance_sufficient_freshwater']\n\nW11_c['use_importance'] = W11_c['direct_use_importance'] + W11_c['indirect_use_importance']\n\n# Create KPIs\nW11_c['KPI_rank_use_importance_freshwater'] = W11_c['use_importance_freshwater'].rank(pct=True)\nW11_c['KPI_rank_use_importance_recycled'] = W11_c['use_importance_recycled'].rank(pct=True)\nW11_c['KPI_rank_direct_use_importance'] = W11_c['direct_use_importance'].rank(pct=True)\nW11_c['KPI_rank_indirect_use_importance'] = W11_c['indirect_use_importance'].rank(pct=True)\nW11_c['KPI_rank_use_importance'] = W11_c['use_importance'].rank(pct=True)\n\n\n## Visualize results\n#W11_c.head(5)\n\n############################################################# TABLE 1.2b Water use ###############################################################################\n\nW12b = w20[w20['question_number']=='W1.2b']\nW12b =W12b.drop(['survey_year', 'response_received_date','accounting_period_to','ors_response_id','submission_date','page_name','module_name',\n          'question_number','question_unique_reference','data_point_name','data_point_id','comments','table_columns_unique_reference','column_number'], axis=1)\nW12b=W12b.pivot(index=['account_number','organization','row_number','row_name'], columns='column_name', values='response_value').reset_index()\nW12b=W12b.sort_values(by=['account_number','organization','row_number'])\n# C11=C11.dropna()\n\n# Pivot dataset to manage data and calculations \nW12b_p1 = W12b.drop(['W1.2b_C3Please explain', 'row_number', 'W1.2b_C2Comparison with previous reporting year'], axis=1)\nW12b_p2 = W12b.drop(['W1.2b_C3Please explain', 'row_number', 'W1.2b_C1Volume (megaliters/year)'], axis=1)\n\nW12b_p1 = W12b_p1.pivot(index=['account_number','organization'], columns='row_name', values='W1.2b_C1Volume (megaliters/year)').reset_index()\nW12b_p1.columns = ['account_number', 'organization', 'water_use_consumption_quantity_MLpa', 'water_use_discharges_quantity_MLpa', 'water_use_withdrawals_quantity_MLpa']\n\nW12b_p2 = W12b_p2.pivot(index=['account_number','organization'], columns='row_name', values='W1.2b_C2Comparison with previous reporting year').reset_index()\nW12b_p2.columns = ['account_number', 'organization', 'water_use_consumption_vs_last_year', 'water_use_discharges_vs_last_year', 'water_use_withdrawals_vs_last_year']\n\nW12b_c = W12b_p1.merge(W12b_p2, how='left', on='account_number')\n\nW12b_c.drop(['organization_y'], axis=1, inplace=True)\nW12b_c.rename(columns={'organization_x': 'organization'}, inplace=True)\n\n### Clean cols\nW12b_c['water_use_consumption_quantity_MLpa'] = W12b_c['water_use_consumption_quantity_MLpa'].astype(float)\nW12b_c['water_use_discharges_quantity_MLpa'] = W12b_c['water_use_discharges_quantity_MLpa'].astype(float)\nW12b_c['water_use_withdrawals_quantity_MLpa'] = W12b_c['water_use_withdrawals_quantity_MLpa'].astype(float)\n\nW12b_c['water_use_consumption_vs_last_year'].value_counts(dropna=True)\n\n## Water consumption\n\"\"\"\nCONSUMPTION:\nMuch lower = 1/5\nLower = 2/5\nAbout the same = 3/5\nHigher = 4/5\nMuch higher = 5/5\nThis is our first year of measurement = np.nan\n\"\"\"\nW12b_c.loc[W12b_c['water_use_consumption_vs_last_year']=='Much lower', 'water_use_consumption_vs_last_year'] = 1/5\nW12b_c.loc[W12b_c['water_use_consumption_vs_last_year']=='Lower', 'water_use_consumption_vs_last_year'] = 2/5\nW12b_c.loc[W12b_c['water_use_consumption_vs_last_year']=='About the same', 'water_use_consumption_vs_last_year'] = 3/5\nW12b_c.loc[W12b_c['water_use_consumption_vs_last_year']=='Higher', 'water_use_consumption_vs_last_year'] = 4/5\nW12b_c.loc[W12b_c['water_use_consumption_vs_last_year']=='Much higher', 'water_use_consumption_vs_last_year'] = 5/5\nW12b_c.loc[W12b_c['water_use_consumption_vs_last_year']=='This is our first year of measurement', 'water_use_consumption_vs_last_year'] = np.nan\n\nW12b_c.loc[W12b_c['water_use_discharges_vs_last_year']=='Much lower', 'water_use_discharges_vs_last_year'] = 1/5\nW12b_c.loc[W12b_c['water_use_discharges_vs_last_year']=='Lower', 'water_use_discharges_vs_last_year'] = 2/5\nW12b_c.loc[W12b_c['water_use_discharges_vs_last_year']=='About the same', 'water_use_discharges_vs_last_year'] = 3/5\nW12b_c.loc[W12b_c['water_use_discharges_vs_last_year']=='Higher', 'water_use_discharges_vs_last_year'] = 4/5\nW12b_c.loc[W12b_c['water_use_discharges_vs_last_year']=='Much higher', 'water_use_discharges_vs_last_year'] = 5/5\nW12b_c.loc[W12b_c['water_use_discharges_vs_last_year']=='This is our first year of measurement', 'water_use_discharges_vs_last_year'] = np.nan\n\nW12b_c.loc[W12b_c['water_use_withdrawals_vs_last_year']=='Much lower', 'water_use_withdrawals_vs_last_year'] = 1/5\nW12b_c.loc[W12b_c['water_use_withdrawals_vs_last_year']=='Lower', 'water_use_withdrawals_vs_last_year'] = 2/5\nW12b_c.loc[W12b_c['water_use_withdrawals_vs_last_year']=='About the same', 'water_use_withdrawals_vs_last_year'] = 3/5\nW12b_c.loc[W12b_c['water_use_withdrawals_vs_last_year']=='Higher', 'water_use_withdrawals_vs_last_year'] = 4/5\nW12b_c.loc[W12b_c['water_use_withdrawals_vs_last_year']=='Much higher', 'water_use_withdrawals_vs_last_year'] = 5/5\nW12b_c.loc[W12b_c['water_use_withdrawals_vs_last_year']=='This is our first year of measurement', 'water_use_withdrawals_vs_last_year'] = np.nan\n\n# C24a =C24a.drop(['C2.4a_C8Likelihood'], axis=1)\n\n\n## Create KPIs\n# Composite metrics\nW12b_c['water_use_total_movement_quantity_MLpa'] = W12b_c['water_use_consumption_quantity_MLpa'] + W12b_c['water_use_discharges_quantity_MLpa'] + W12b_c['water_use_withdrawals_quantity_MLpa']\n\n# KPIs\nW12b_c['KPI_rank_water_use_consumption_quantity'] = W12b_c['water_use_consumption_quantity_MLpa'].rank(pct=True)\nW12b_c['KPI_rank_water_use_discharges_quantity'] = W12b_c['water_use_discharges_quantity_MLpa'].rank(pct=True)\nW12b_c['KPI_rank_water_use_withdrawals_quantity'] = W12b_c['water_use_withdrawals_quantity_MLpa'].rank(pct=True)\nW12b_c['KPI_rank_water_use_total_movement_quantity'] = W12b_c['water_use_total_movement_quantity_MLpa'].rank(pct=True)\nW12b_c['KPI_rank_water_use_consumption_vs_last_year'] = W12b_c['water_use_consumption_vs_last_year'].rank(pct=True)\nW12b_c['KPI_rank_water_use_discharges_vs_last_year'] = W12b_c['water_use_discharges_vs_last_year'].rank(pct=True)\nW12b_c['KPI_rank_water_use_withdrawals_vs_last_year'] = W12b_c['water_use_withdrawals_vs_last_year'].rank(pct=True)\n\n\n## Visualzie results\n#W12b_c.head(5)\n\n####################################################### TABLE 4.1b Water-related risks #######################################################################\n\nW41b = w20[w20['question_number']=='W4.1b']\nW41b =W41b.drop(['survey_year', 'response_received_date','accounting_period_to','ors_response_id','submission_date','page_name','module_name',\n          'question_number','question_unique_reference','data_point_name','data_point_id','comments','table_columns_unique_reference','column_number'], axis=1)\nW41b=W41b.pivot(index=['account_number','organization','row_number','row_name'], columns='column_name', values='response_value').reset_index()\nW41b=W41b.sort_values(by=['account_number','organization','row_number'])\n\nW41b = W41b.drop(['W4.1b_C3Comment', 'row_number', 'row_name'], axis=1)\n\n\n### Clean cols\nW41b['W4.1b_C1Total number of facilities exposed to water risk'] = W41b['W4.1b_C1Total number of facilities exposed to water risk'].astype(float)\n\n\n### Categorize columns into numericals\n## Water risk\n\"\"\"\nRISK:\n    100 = 1.0\n    76-99 = (99 + 76)/2\n    51-75 = (75 + 51)/2\n    26-50 = (50 + 26)/2\n    1-25 = (25 + 1)/2\n    1 = (1 + 0)/2\n    Unknown = np.nan\n\"\"\"\nW41b['water_risk_business_exposure_pct'] = np.nan\nW41b.loc[W41b['W4.1b_C2% company-wide facilities this represents']=='100', 'water_risk_business_exposure_pct'] = 1.0\nW41b.loc[W41b['W4.1b_C2% company-wide facilities this represents']=='76-99', 'water_risk_business_exposure_pct'] = (99 + 76)/2/100\nW41b.loc[W41b['W4.1b_C2% company-wide facilities this represents']=='51-75', 'water_risk_business_exposure_pct'] = (75 + 51)/2/100\nW41b.loc[W41b['W4.1b_C2% company-wide facilities this represents']=='26-50', 'water_risk_business_exposure_pct'] = (50 + 26)/2/100\nW41b.loc[W41b['W4.1b_C2% company-wide facilities this represents']=='1-25', 'water_risk_business_exposure_pct'] = (25 + 1)/2/100\nW41b.loc[W41b['W4.1b_C2% company-wide facilities this represents']=='Less than 1%', 'water_risk_business_exposure_pct'] = (1 + 0)/2/100\nW41b.loc[W41b['W4.1b_C2% company-wide facilities this represents']=='Unknown', 'water_risk_business_exposure_pct'] = np.nan\n# C24a =C24a.drop(['C2.4a_C8Likelihood'], axis=1)\n\n\n## Create KPIs\n# KPIs\nW41b['KPI_rank_business_risk_facilities_exposed'] = W41b['W4.1b_C1Total number of facilities exposed to water risk'].rank(pct=True)\nW41b['KPI_rank_business_risk_business_exposure'] = W41b['water_risk_business_exposure_pct'].rank(pct=True)\n\n\n\n## Visualize results\n#W41b.head(5)\n# W41b['row_number'].value_counts(dropna=True)\n# W41b['water_risk_business_exposure_pct'].value_counts(dropna=True)\n\n#####################################################  TABLE 4.1c Water-related risk by geography #################################################################\n\nW41c = w20[w20['question_number']=='W4.1c']\nW41c =W41c.drop(['survey_year', 'response_received_date','accounting_period_to','ors_response_id','submission_date','page_name','module_name',\n          'question_number','question_unique_reference','data_point_name','data_point_id','comments','table_columns_unique_reference','column_number'], axis=1)\nW41c=W41c.pivot(index=['account_number','organization','row_number','row_name'], columns='column_name', values='response_value').reset_index()\nW41c=W41c.sort_values(by=['account_number','organization','row_number'])\n# C11=C11.dropna()\n\n\n### Clean up dataframe\n## Remove questons\n\"\"\"\nThese questions will be removed: \n'W4.1c_C4Production value for the metals & mining activities associated with these facilities': too few values/answers (23)\n'W4.1c_C1Country/Area & River basin': too atomic/text sparse\n'W4.1c_C5% company’s annual electricity generation that could be affected by these facilities': too few values/answers (63)\n'W4.1c_C6% company’s global oil & gas production volume that could be affected by these facilities': too few values/answers (27)\n\n\"\"\"\nW41c = W41c.drop([\n    'row_number',\n    'row_name',\n    'W4.1c_C4Production value for the metals & mining activities associated with these facilities', \n    'W4.1c_C1Country/Area & River basin', \n    'W4.1c_C5% company’s annual electricity generation that could be affected by these facilities',\n    'W4.1c_C6% company’s global oil & gas production volume that could be affected by these facilities',                  \n    'W4.1c_C8Comment'], axis=1)\n\n\n## Clean cols\nW41c['facilities_exposed_to_water_risk'] = W41c['W4.1c_C2Number of facilities exposed to water risk'].astype(float)\n\n### Categorize columns into numericals\n## Water risk impact\n\"\"\"\nRISK:\n    # 100% = 1.0\n    # 81-90 = (90 + 81)/2\n    # 71-80 = (80 + 71)/2\n    # 61-70 = (70 + 61)/2\n    # 51-60 = (60 + 51)/2\n    # 41-50 = (50 + 41)/2\n    # 31-40 = (40 + 31)/2\n    # 21-30 = (30 + 21)/2\n    # 11-20 = (20 + 11)/2\n    # 1-10 = (10 + 1)/2\n    # Less than 1% = (1 + 0)/2\n    # Unknown = np.nan\n\"\"\"\nW41c['water_risk_business_exposure_by_country_pct'] = np.nan\nW41c.loc[W41c['W4.1c_C7% company’s total global revenue that could be affected']=='100%', 'water_risk_business_exposure_by_country_pct'] = 1.0\nW41c.loc[W41c['W4.1c_C7% company’s total global revenue that could be affected']=='81-90', 'water_risk_business_exposure_by_country_pct'] = (90 + 81)/2/100\nW41c.loc[W41c['W4.1c_C7% company’s total global revenue that could be affected']=='71-80', 'water_risk_business_exposure_by_country_pct'] = (80 + 71)/2/100\nW41c.loc[W41c['W4.1c_C7% company’s total global revenue that could be affected']=='61-70', 'water_risk_business_exposure_by_country_pct'] = (70 + 61)/2/100\nW41c.loc[W41c['W4.1c_C7% company’s total global revenue that could be affected']=='51-60', 'water_risk_business_exposure_by_country_pct'] = (60 + 51)/2/100\nW41c.loc[W41c['W4.1c_C7% company’s total global revenue that could be affected']=='41-50', 'water_risk_business_exposure_by_country_pct'] = (50 + 41)/2/100\nW41c.loc[W41c['W4.1c_C7% company’s total global revenue that could be affected']=='31-40', 'water_risk_business_exposure_by_country_pct'] = (40 + 31)/2/100\nW41c.loc[W41c['W4.1c_C7% company’s total global revenue that could be affected']=='21-30', 'water_risk_business_exposure_by_country_pct'] = (30 + 21)/2/100\nW41c.loc[W41c['W4.1c_C7% company’s total global revenue that could be affected']=='11-20', 'water_risk_business_exposure_by_country_pct'] = (20 + 11)/2/100\nW41c.loc[W41c['W4.1c_C7% company’s total global revenue that could be affected']=='1-10', 'water_risk_business_exposure_by_country_pct'] = (10 + 1)/2/100\nW41c.loc[W41c['W4.1c_C7% company’s total global revenue that could be affected']=='Less than 1%', 'water_risk_business_exposure_by_country_pct'] = (1 + 0)/2/100\nW41c.loc[W41c['W4.1c_C7% company’s total global revenue that could be affected']=='Unknown', 'water_risk_business_exposure_by_country_pct'] = np.nan\n\n\n\"\"\"\nRISK:\n    100 = 1.0\n    76-99 = (99 + 76)/2\n    51-75 = (75 + 51)/2\n    26-50 = (50 + 26)/2\n    1-25 = (25 + 1)/2\n    1 = (1 + 0)/2\n    Unknown = np.nan\n\"\"\"\nW41c['water_risk_company_size_facilities_exposed_by_country_pct'] = np.nan\nW41c.loc[W41c['W4.1c_C3% company-wide facilities this represents']=='100%', 'water_risk_company_size_facilities_exposed_by_country_pct'] = 1.0\nW41c.loc[W41c['W4.1c_C3% company-wide facilities this represents']=='76-99', 'water_risk_company_size_facilities_exposed_by_country_pct'] = (99 + 76)/2/100\nW41c.loc[W41c['W4.1c_C3% company-wide facilities this represents']=='51-75', 'water_risk_company_size_facilities_exposed_by_country_pct'] = (75 + 51)/2/100\nW41c.loc[W41c['W4.1c_C3% company-wide facilities this represents']=='26-50', 'water_risk_company_size_facilities_exposed_by_country_pct'] = (50 + 26)/2/100\nW41c.loc[W41c['W4.1c_C3% company-wide facilities this represents']=='1-25', 'water_risk_company_size_facilities_exposed_by_country_pct'] = (25 + 1)/2/100\nW41c.loc[W41c['W4.1c_C3% company-wide facilities this represents']=='Less than 1%', 'water_risk_company_size_facilities_exposed_by_country_pct'] = (1 + 0)/2/100\nW41c.loc[W41c['W4.1c_C3% company-wide facilities this represents']=='Unknown', 'water_risk_company_size_facilities_exposed_by_country_pct'] = np.nan\n# C24a =C24a.drop(['C2.4a_C8Likelihood'], axis=1)\n\n\n## Drop last cols:\nW41c = W41c.drop([\n    'W4.1c_C2Number of facilities exposed to water risk',\n    'W4.1c_C3% company-wide facilities this represents',\n    'W4.1c_C7% company’s total global revenue that could be affected'], axis=1)\n\n\n### Create by country list:\n## Clean up cols to allow weighted means calc\nW41c.loc[W41c['water_risk_company_size_facilities_exposed_by_country_pct'].isna(), 'water_risk_company_size_facilities_exposed_by_country_pct'] = 0.0000000001\nW41c.loc[W41c['water_risk_business_exposure_by_country_pct'].isna(), 'water_risk_business_exposure_by_country_pct'] = 0.0000000001\nwm_risk_financial_impact_by_country = lambda x: np.average(x, weights=W41c.loc[x.index, 'water_risk_company_size_facilities_exposed_by_country_pct'])\n\n## Group by country\nW41c_by_country = W41c.groupby(['W4.1c_C1Country/Area & River basin_G'], as_index=False).agg(\n    facilities_exposed_to_water_risk = ('facilities_exposed_to_water_risk', 'sum'),\n    water_risk_business_exposure_by_country_pct = ('water_risk_business_exposure_by_country_pct', wm_risk_financial_impact_by_country),\n    )\n\n## Create KPIs\nW41c_by_country['KPI_rank_business_risk_facilities_exposed_ctry'] = W41c_by_country['facilities_exposed_to_water_risk'].rank(pct=True)\nW41c_by_country['KPI_rank_business_risk_business_exposure_ctry'] = W41c_by_country['water_risk_business_exposure_by_country_pct'].rank(pct=True)\n\n\n\n## Visualize results\n# W41c.head(5)\n#W41c_by_country.head(5)\n# W41c['water_risk_business_exposure_by_country_pct'].value_counts(dropna=True)\n# W41c_by_country.head(5)\n# W41c[W41c['W4.1c_C1Country/Area & River basin_G']=='Brazil']\n\n###########################################################  TABLE 4.2 Water-related identified risks ##############################################################\n\nW42 = w20[w20['question_number']=='W4.2']\nW42 = W42.drop(['survey_year', 'response_received_date','accounting_period_to','ors_response_id','submission_date','page_name','module_name',\n          'question_number','question_unique_reference','data_point_name','data_point_id','comments','table_columns_unique_reference','column_number'], axis=1)\nW42=W42.pivot(index=['account_number','organization','row_number','row_name'], columns='column_name', values='response_value').reset_index()\nW42=W42.sort_values(by=['account_number','organization','row_number'])\n# C11=C11.dropna()\n\n## Drop rows where == nan\nW42 = W42[W42['row_number']!=0]\n\n## Calculate financial impact into a single col\n# Clean up numeric cols\nW42['W4.2_C10Potential financial impact figure - minimum (currency)'] = W42['W4.2_C10Potential financial impact figure - minimum (currency)'].astype(float)\nW42['W4.2_C11Potential financial impact figure - maximum (currency)'] = W42['W4.2_C11Potential financial impact figure - maximum (currency)'].astype(float)\nW42['W4.2_C9Potential financial impact figure (currency)'] = W42['W4.2_C9Potential financial impact figure (currency)'].astype(float)\n\n# Calculate financial impact into a single col\nW42['C9_C10_C11_risk_financial_impact'] = W42['W4.2_C9Potential financial impact figure (currency)']\nW42.loc[W42['C9_C10_C11_risk_financial_impact'].isnull(), 'C9_C10_C11_risk_financial_impact'] = (W42['W4.2_C10Potential financial impact figure - minimum (currency)'] + W42['W4.2_C11Potential financial impact figure - maximum (currency)'])/2\n\n\n### Re-categorize categoricals into float\n## Timeframe\n\"\"\"\nTIMEFRAME:\n    Current up to one year = 1\n    1-3 years = 2\n    4-6 years = 5\n    More than 6 years = 6\n    Unknown = np.nan\n\"\"\"\nW42['C5_timeframe'] = np.nan\nW42.loc[W42['W4.2_C5Timeframe']=='Current up to one year', 'C5_timeframe'] = 1\nW42.loc[W42['W4.2_C5Timeframe']=='1-3 years', 'C5_timeframe'] = 2\nW42.loc[W42['W4.2_C5Timeframe']=='4-6 years', 'C5_timeframe'] = 5\nW42.loc[W42['W4.2_C5Timeframe']=='More than 6 years', 'C5_timeframe'] = 6\nW42.loc[W42['W4.2_C5Timeframe']=='Unknown', 'C5_timeframe'] = np.nan\n\n## Magnitude\n\"\"\"\nMAGNITUDE\n    High = 5/5\n    Medium-high = 4/5\n    Medium = 3/5\n    Medium-low = 2/5\n    Low = 1/5\n    Unknown = np.nan\n\"\"\"\nW42['C6_risk_impact_magnitude'] = np.nan\nW42.loc[W42['W4.2_C6Magnitude of potential impact']=='High', 'C6_risk_impact_magnitude'] = 5/5\nW42.loc[W42['W4.2_C6Magnitude of potential impact']=='Medium-high', 'C6_risk_impact_magnitude'] = 4/5\nW42.loc[W42['W4.2_C6Magnitude of potential impact']=='Medium', 'C6_risk_impact_magnitude'] = 3/5\nW42.loc[W42['W4.2_C6Magnitude of potential impact']=='Medium-low', 'C6_risk_impact_magnitude'] = 2/5\nW42.loc[W42['W4.2_C6Magnitude of potential impact']=='Low', 'C6_risk_impact_magnitude'] = 1/5\n\n## Likelihood\n\"\"\"\nLIKELIHOOD:\n    Virtually certain = 8/8\n    Very likely = 7/8\n    Likely = 6/8\n    More likely than not = 5/8\n    About as likely as not =  = 4/8\n    Unlikely = 3/8\n    Very unlikely = 2/8\n    Exceptionally unlikely = 1/8\n    Unknown = np.nan\n\"\"\"\nW42['C7_risk_likelihood'] = np.nan\nW42.loc[W42['W4.2_C7Likelihood']=='Virtually certain', 'C7_risk_likelihood'] = 8/8\nW42.loc[W42['W4.2_C7Likelihood']=='Very likely', 'C7_risk_likelihood'] = 7/8\nW42.loc[W42['W4.2_C7Likelihood']=='Likely', 'C7_risk_likelihood'] = 6/8\nW42.loc[W42['W4.2_C7Likelihood']=='More likely than not', 'C7_risk_likelihood'] = 5/8\nW42.loc[W42['W4.2_C7Likelihood']=='About as likely as not', 'C7_risk_likelihood'] = 4/8\nW42.loc[W42['W4.2_C7Likelihood']=='Unlikely', 'C7_risk_likelihood'] = 3/8\nW42.loc[W42['W4.2_C7Likelihood']=='Very unlikely', 'C7_risk_likelihood'] = 2/8\nW42.loc[W42['W4.2_C7Likelihood']=='Exceptionally unlikely', 'C7_risk_likelihood'] = 1/8\n\n\n## Drop last cols:\nW42 = W42.drop([\n    'W4.2_C12Explanation of financial impact'\n    ,'W4.2_C14Description of response'\n    ,'W4.2_C16Explanation of cost of response'\n    ,'W4.2_C1Country/Area & River basin'\n    ,'W4.2_C4Company-specific description'\n    ,'W4.2_C8Are you able to provide a potential financial impact figure?'\n    ,'row_name'\n    ,'W4.2_C10Potential financial impact figure - minimum (currency)'\n    ,'W4.2_C11Potential financial impact figure - maximum (currency)'\n    ,'W4.2_C9Potential financial impact figure (currency)'\n    ,'W4.2_C5Timeframe'\n    ,'W4.2_C6Magnitude of potential impact'\n    ,'W4.2_C7Likelihood'\n    ], axis=1)\n\n## Re-categorize col values with low frequencies:\ncols_to_recat =['W4.2_C13Primary response to risk', 'W4.2_C2Type of risk & Primary risk driver', 'W4.2_C3Primary potential impact']\nW42 = W42.apply(lambda x: x.mask(x.map(x.value_counts())<10, 'Other') if x.name in cols_to_recat else x)\n\n# Clean up a 'Other' value:\n\"\"\"\n8, 9, 9: too many columns! Recode into new df, one for companies without these, and another by country\n\"\"\"\nW42.loc[W42['W4.2_C13Primary response to risk']=='Other, please specify: Comprehensive site water stewardship action plans', 'W4.2_C13Primary response to risk'] = 'Other'\n\n\n\n## Create df by company\nW42_company = W42.groupby(['account_number','organization'], as_index=False).agg(\n    risks_count = ('row_number', 'count'),\n    C9_C10_C11_risk_financial_impact = ('C9_C10_C11_risk_financial_impact', 'sum'), \n    C5_timeframe_years = ('C5_timeframe', 'mean'),\n    C6_risk_impact_magnitude = ('C6_risk_impact_magnitude', 'mean'), \n    C7_risk_likelihood = ('C7_risk_likelihood', 'mean')\n    )\n\n## Create df by country\nW42_country = W42.groupby(['W4.2_C1Country/Area & River basin_G'], as_index=False).agg(\n    risks_count = ('row_number', 'count'),\n    C9_C10_C11_risk_financial_impact = ('C9_C10_C11_risk_financial_impact', 'sum'), \n    C5_timeframe_years = ('C5_timeframe', 'mean'),\n    C6_risk_impact_magnitude = ('C6_risk_impact_magnitude', 'mean'), \n    C7_risk_likelihood = ('C7_risk_likelihood', 'mean')\n    )\n\n# Clean up bug by Pandas of counting NaNs as 0 in sum\nW42_company.loc[W42_company['C9_C10_C11_risk_financial_impact']==0, 'C9_C10_C11_risk_financial_impact'] = np.nan\nW42_country.loc[W42_country['C9_C10_C11_risk_financial_impact']==0, 'C9_C10_C11_risk_financial_impact'] = np.nan\n\n\n## Create KPIs:\n## Company\n# Likelihood * Magnitude / Years\nW42_company['risk_index'] = (W42_company['C7_risk_likelihood'] * W42_company['C6_risk_impact_magnitude']) / W42_company['C5_timeframe_years'] \nW42_company['risk_index'].replace(np.inf, np.nan, inplace=True)\n\n# Create KPI (0: least at risk; 1: most at risk)\nW42_company['KPI_rank_risk_count'] = W42_company['risks_count'].rank(pct=True)\nW42_company['KPI_rank_risk_financial_impact'] = W42_company['C9_C10_C11_risk_financial_impact'].rank(pct=True)\nW42_company['KPI_rank_risk_timeframe_years'] = W42_company['C5_timeframe_years'].rank(pct=True)\nW42_company['KPI_rank_risk_timeframe_years'] = 1 - W42_company['KPI_rank_risk_timeframe_years']\nW42_company['KPI_rank_risk_impact_magnitude'] = W42_company['C6_risk_impact_magnitude'].rank(pct=True)\nW42_company['KPI_rank_risk_risk_likelihood'] = W42_company['C7_risk_likelihood'].rank(pct=True)\nW42_company['KPI_rank_risk_index'] = W42_company['risk_index'].rank(pct=True)\n\n## Country\n# Likelihood * Magnitude / Years\nW42_country['risk_index'] = (W42_country['C7_risk_likelihood'] * W42_country['C6_risk_impact_magnitude']) / W42_country['C5_timeframe_years'] \nW42_country['risk_index'].replace(np.inf, np.nan, inplace=True)\n\n# Create KPI (0: least at risk; 1: most at risk)\nW42_country['KPI_rank_risk_count'] = W42_country['risks_count'].rank(pct=True)\nW42_country['KPI_rank_risk_financial_impact'] = W42_country['C9_C10_C11_risk_financial_impact'].rank(pct=True)\nW42_country['KPI_rank_risk_timeframe_years'] = W42_country['C5_timeframe_years'].rank(pct=True)\nW42_country['KPI_rank_risk_timeframe_years'] = 1 - W42_country['KPI_rank_risk_timeframe_years']\nW42_country['KPI_rank_risk_impact_magnitude'] = W42_country['C6_risk_impact_magnitude'].rank(pct=True)\nW42_country['KPI_rank_risk_risk_likelihood'] = W42_country['C7_risk_likelihood'].rank(pct=True)\nW42_country['KPI_rank_risk_index'] = W42_country['risk_index'].rank(pct=True)\n\n\n## Visualize results\n# W42.head(5)\n#W42_company.head(5)\n# W42_country.head(5)\n\n################################################## TABLE 4.3a Water  opportunities currently being realized ########################################################\n\nW43a = w20[w20['question_number']=='W4.3a']\nW43a = W43a.drop(['survey_year', 'response_received_date','accounting_period_to','ors_response_id','submission_date','page_name','module_name',\n          'question_number','question_unique_reference','data_point_name','data_point_id','comments','table_columns_unique_reference','column_number'], axis=1)\nW43a=W43a.pivot(index=['account_number','organization','row_number','row_name'], columns='column_name', values='response_value').reset_index()\nW43a=W43a.sort_values(by=['account_number','organization','row_number'])\n# C11=C11.dropna()\n\n## Drop rows where == nan\nW43a = W43a[W43a['row_number']!=0]\n\n## Calculate financial impact into a single col\n# Clean up numeric cols\nW43a['W4.3a_C8Potential financial impact figure – minimum (currency)'] = W43a['W4.3a_C8Potential financial impact figure – minimum (currency)'].astype(float)\nW43a['W4.3a_C9Potential financial impact figure – maximum (currency)'] = W43a['W4.3a_C9Potential financial impact figure – maximum (currency)'].astype(float)\nW43a['W4.3a_C7Potential financial impact figure (currency)'] = W43a['W4.3a_C7Potential financial impact figure (currency)'].astype(float)\n\n# Calculate financial impact into a single col\nW43a['C7_C8_C9_opportunity_financial_impact'] = W43a['W4.3a_C7Potential financial impact figure (currency)']\nW43a.loc[W43a['C7_C8_C9_opportunity_financial_impact'].isnull(), 'C7_C8_C9_opportunity_financial_impact'] = (W43a['W4.3a_C8Potential financial impact figure – minimum (currency)'] + W43a['W4.3a_C9Potential financial impact figure – maximum (currency)'])/2\n\n## Re-categorize into float\n\"\"\"\nTIMEFRAME:\n    Current up to one year = 1\n    1-3 years = 2\n    4-6 years = 5\n    More than 6 years = 6\n    Unknown = np.nan\n\"\"\"\nW43a['C4_timeframe'] = np.nan\nW43a.loc[W43a['W4.3a_C4Estimated timeframe for realization']=='Current - up to 1 year', 'C4_timeframe'] = 1\nW43a.loc[W43a['W4.3a_C4Estimated timeframe for realization']=='1-3 years', 'C4_timeframe'] = 2\nW43a.loc[W43a['W4.3a_C4Estimated timeframe for realization']=='4-6 years', 'C4_timeframe'] = 5\nW43a.loc[W43a['W4.3a_C4Estimated timeframe for realization']=='More than 6 years', 'C4_timeframe'] = 6\nW43a.loc[W43a['W4.3a_C4Estimated timeframe for realization']=='Unknown', 'C4_timeframe'] = np.nan\n\n## Magnitude\n\"\"\"\nMAGNITUDE\n    High = 5/5\n    Medium-high = 4/5\n    Medium = 3/5\n    Medium-low = 2/5\n    Low = 1/5\n    Unknown = np.nan\n\"\"\"\nW43a['C5_opportunity_impact_magnitude'] = np.nan\nW43a.loc[W43a['W4.3a_C5Magnitude of potential financial impact']=='High', 'C5_opportunity_impact_magnitude'] = 5/5\nW43a.loc[W43a['W4.3a_C5Magnitude of potential financial impact']=='Medium-high', 'C5_opportunity_impact_magnitude'] = 4/5\nW43a.loc[W43a['W4.3a_C5Magnitude of potential financial impact']=='Medium', 'C5_opportunity_impact_magnitude'] = 3/5\nW43a.loc[W43a['W4.3a_C5Magnitude of potential financial impact']=='Medium-low', 'C5_opportunity_impact_magnitude'] = 2/5\nW43a.loc[W43a['W4.3a_C5Magnitude of potential financial impact']=='Low', 'C5_opportunity_impact_magnitude'] = 1/5\n\n## Create binaries for type of opportunity:\none_hot = pd.get_dummies(W43a['W4.3a_C1Type of opportunity']).add_prefix('opportunity_type_')\nW43a = W43a.join(one_hot)\n\n\n## Drop last cols:\nW43a = W43a.drop([\n    'row_name'\n    ,'W4.3a_C4Estimated timeframe for realization'\n    ,'W4.3a_C5Magnitude of potential financial impact'\n    ,'W4.3a_C6Are you able to provide a potential financial impact figure?'\n    ,'W4.3a_C7Potential financial impact figure (currency)'\n    ,'W4.3a_C8Potential financial impact figure – minimum (currency)'\n    ,'W4.3a_C9Potential financial impact figure – maximum (currency)'\n    ,'W4.3a_C10Explanation of financial impact'\n    ,'W4.3a_C1Type of opportunity'\n    ,'W4.3a_C2Primary water-related opportunity'\n    ,'W4.3a_C3Company-specific description & strategy to realize opportunity'\n    ], axis=1)\n\n\n\n## Create df by company\nW43a_KPI = W43a.groupby(['account_number','organization'], as_index=False).agg(\n    opportunity_count = ('row_number', 'count'),\n    opportunity_type_efficiency = ('opportunity_type_Efficiency', 'mean'),\n    opportunity_type_products_and_services = ('opportunity_type_Products and services', 'mean'),\n    opportunity_type_resilience = ('opportunity_type_Resilience', 'mean'),\n    opportunity_type_markets = ('opportunity_type_Markets', 'mean'),\n    opportunity_type_other = ('opportunity_type_Other', 'mean'),\n    C7_C8_C9_opportunity_financial_impact = ('C7_C8_C9_opportunity_financial_impact', 'sum'), \n    C4_timeframe_years = ('C4_timeframe', 'mean'),\n    C5_opportunity_impact_magnitude = ('C5_opportunity_impact_magnitude', 'mean') \n    )\n\n# Clean up bug by Pandas of counting NaNs as 0 in sum\nW43a_KPI.loc[W43a_KPI['C7_C8_C9_opportunity_financial_impact']==0, 'C7_C8_C9_opportunity_financial_impact'] = np.nan\n\n\n## Create KPIs:\n# Likelihood * Magnitude / Years\n\"\"\"\n!!! WARNING !!!\nThere is no associated likelihood with this df\n\"\"\"\n# W43a_KPI['opportunity_index'] = (W43a_KPI['C7_risk_likelihood'] * W42_country['C6_risk_impact_magnitude']) / W42_country['C5_timeframe_years'] \nW43a_KPI['opportunity_index'] = (W43a_KPI['C5_opportunity_impact_magnitude']) / W43a_KPI['C4_timeframe_years'] \nW43a_KPI['opportunity_index'].replace(np.inf, np.nan, inplace=True)\n\n## Create KPI (0: least at opportunity; 1: most at opportunity)\nW43a_KPI['KPI_rank_opportunity_financial_impact'] = W43a_KPI['C7_C8_C9_opportunity_financial_impact'].rank(pct=True)\nW43a_KPI['KPI_rank_opportunity_timeframe'] = W43a_KPI['C4_timeframe_years'].rank(pct=True)\nW43a_KPI['KPI_rank_opportunity_impact_magnitude'] = W43a_KPI['C5_opportunity_impact_magnitude'].rank(pct=True)\nW43a_KPI['KPI_rank_opportunity_index'] = W43a_KPI['opportunity_index'].rank(pct=True)\n\n\n## Visualize results\n#W43a.head(5)\n#W43a_KPI.head(5)\n# W42_country.head(5)\n# W43a['W4.3a_C1Type of opportunity'].value_counts(dropna=True)\n\n###########################################################  TABLE 5.1 Water use by facility ######################################################################\n\nW51 = w20[w20['question_number']=='W5.1']\nW51 = W51.drop(['survey_year', 'response_received_date','accounting_period_to','ors_response_id','submission_date','page_name','module_name',\n          'question_number','question_unique_reference','data_point_name','data_point_id','comments','table_columns_unique_reference','column_number'], axis=1)\nW51=W51.pivot(index=['account_number','organization','row_number','row_name'], columns='column_name', values='response_value').reset_index()\nW51=W51.sort_values(by=['account_number','organization','row_number'])\n# C11=C11.dropna()\n\n## Drop rows where == nan\nW51 = W51[W51['row_number']!=0]\n\n## Convert coordinates into Point\nW51['W5.1_C5Longitude'] = W51['W5.1_C5Longitude'].astype(float)\nW51['W5.1_C4Latitude'] = W51['W5.1_C4Latitude'].astype(float)\nW51['C4_C5_facility_coordinates_lon_lat'] = W51.apply(lambda x: Point(x['W5.1_C5Longitude'], x['W5.1_C4Latitude']), axis=1)\n\n## Create binaries for whether there is water stress in area:\n# Recode Unknown into NaN\nW51.loc[W51['W5.1_C6Located in area with water stress']=='Unknown', 'W5.1_C6Located in area with water stress'] = np.nan\n\n# Binarize\none_hot = pd.get_dummies(W51['W5.1_C6Located in area with water stress']).add_prefix('C6_facility_in_area_with_water_stress_')\nW51 = W51.join(one_hot)\ndel W51['C6_facility_in_area_with_water_stress_No']\n\n\n## Convert (and rename) strings to float:\nfloat_cols = [\n 'W5.1_C9Total water withdrawals at this facility (megaliters/year)',\n 'W5.1_C11Withdrawals from fresh surface water, including rainwater, water from wetlands, rivers and lakes', \n 'W5.1_C12Withdrawals from brackish surface water/seawater', \n 'W5.1_C13Withdrawals from groundwater - renewable', \n 'W5.1_C14Withdrawals from groundwater - non-renewable', \n 'W5.1_C15Withdrawals from produced/entrained water', \n 'W5.1_C16Withdrawals from third party sources', \n 'W5.1_C17Total water discharges at this facility (megaliters/year)',\n 'W5.1_C19Discharges to fresh surface water', \n 'W5.1_C20Discharges to brackish surface water/seawater', \n 'W5.1_C21Discharges to groundwater', 'W5.1_C22Discharges to third party destinations', \n 'W5.1_C23Total water consumption at this facility (megaliters/year)'\n]\nfloat_cols_renamed = [\n 'C9_total_water_withdrawals_MLpa',\n 'C11_withdrawals_from_fresh_surface_water_MLpa', \n 'C12_withdrawals_from_brackish_surface_water_or_seawater_MLpa', \n 'C13_withdrawals_from_groundwater_renewable_MLpa', \n 'C14_withdrawals_from_groundwater_non_renewable_MLpa', \n 'C15_withdrawals_from_produced_water_MLpa', \n 'C16_withdrawals_from_third_party_sources_MLpa', \n 'C17_total_water_discharges_at_facility_MLpa',\n 'C19_discharges_to_fresh_surface_water_MLpa', \n 'C20_discharges_to_brackish_surface_water_or_seawater_MLpa', \n 'C21_discharges_to_groundwater_MLpa', \n 'C22_discharges_to_third_party_destinations_MLpa', \n 'C23_total_water_consumption_at_facility_MLpa'\n]\nix = 0\nfor col in float_cols:\n    W51[col] = W51[col].astype(float)\n    W51[float_cols_renamed[ix]] = W51[col]\n    del W51[col]\n    ix += 1\n\n    \n### Recode time comparisons\n\"\"\"\nCONSUMPTION:\n    Much lower = 1/5\n    Lower = 2/5\n    About the same = 3/5\n    Higher = 4/5\n    Much higher = 5/5\n    This is our first year of measurement = np.nan\n\"\"\"\ncompa_cols = ['W5.1_C10Comparison of total withdrawals with previous reporting year',\n    'W5.1_C18Comparison of total discharges with previous reporting year',\n    'W5.1_C24Comparison of total consumption with previous reporting year']\ncompa_cols_new = [\n    'C10_water_total_withdrawals_vs_last_year',\n    'C18_water_total_discharges_vs_last_year',\n    'C24_water_total_consumption_vs_last_year']\n\nix_ = 0\nfor col in compa_cols:\n    compa_col_new = compa_cols_new[ix_]\n\n    W51[compa_col_new] = np.nan\n    W51.loc[W51[col]=='Much lower', compa_col_new] = 1/5\n    W51.loc[W51[col]=='Lower', compa_col_new] = 2/5\n    W51.loc[W51[col]=='About the same', compa_col_new] = 3/5\n    W51.loc[W51[col]=='Higher', compa_col_new] = 4/5\n    W51.loc[W51[col]=='Much higher', compa_col_new] = 5/5\n    W51.loc[W51[col]=='This is our first year of measurement', compa_col_new] = np.nan\n\n    ix_ += 1\n\n    \n## Create composite cols:\nW51['C9_C17_C23_water_total_use_MLpa'] = W51['C9_total_water_withdrawals_MLpa'] + W51['C17_total_water_discharges_at_facility_MLpa'] + W51['C23_total_water_consumption_at_facility_MLpa']\nW51['C10_C18_C24_water_total_use_vs_last_year'] = W51['C10_water_total_withdrawals_vs_last_year'] + W51['C18_water_total_discharges_vs_last_year'] + W51['C24_water_total_consumption_vs_last_year']\n\n\n## Drop last cols:\nW51 = W51.drop([\n#     'row_number'\n    'row_name'\n    ,'W5.1_C25Please explain'\n    ,'W5.1_C2Facility name (optional)'\n    ,'W5.1_C3Country/Area & River basin'\n    ,'W5.1_C5Longitude'\n    ,'W5.1_C4Latitude'\n    ,'W5.1_C1Facility reference number'\n    ,'W5.1_C8Oil & gas sector business division' # Too few data: only 42 answers\n    ,'W5.1_C6Located in area with water stress'\n    ,'W5.1_C10Comparison of total withdrawals with previous reporting year'\n    ,'W5.1_C18Comparison of total discharges with previous reporting year'\n    ,'W5.1_C24Comparison of total consumption with previous reporting year'\n    ], axis=1)\n\n\n\n### Create 3 df, Group by company and by country, and by facility (original df)\n## Prepare for groupbys\n# Create weight based on tot water use:\nwm_water_use = lambda x: np.average(x, weights=W51.loc[x.index, 'C9_C17_C23_water_total_use_MLpa'])\n# wm_water_use = lambda x: np.average(x, weights=W51.loc[x.index, 'facility_count'])\n\n\n# Add a minimal value to NaN in order for weights to work \nW51['C9_C17_C23_water_total_use_MLpa'] = W51['C9_C17_C23_water_total_use_MLpa'].fillna(0)\nW51['C10_water_total_withdrawals_vs_last_year'] = W51['C10_water_total_withdrawals_vs_last_year'].fillna(0)\nW51['C18_water_total_discharges_vs_last_year'] = W51['C18_water_total_discharges_vs_last_year'].fillna(0)\nW51['C24_water_total_consumption_vs_last_year'] = W51['C24_water_total_consumption_vs_last_year'].fillna(0)\n\nW51['C9_C17_C23_water_total_use_MLpa'] = W51['C9_C17_C23_water_total_use_MLpa'] + 0.0000000001\nW51['C10_water_total_withdrawals_vs_last_year'] = W51['C10_water_total_withdrawals_vs_last_year'] + 0.0000000001\nW51['C18_water_total_discharges_vs_last_year'] = W51['C18_water_total_discharges_vs_last_year'] + 0.0000000001\nW51['C24_water_total_consumption_vs_last_year'] = W51['C24_water_total_consumption_vs_last_year'] + 0.0000000001\n\n## Facility\nW51_by_facility = W51.copy()\n\n## Company \nW51_by_company = W51.groupby(['account_number','organization'], as_index=False).agg(\n    facility_count = ('row_number', 'count'),\n    C6_facility_in_area_with_water_stress_Yes = ('C6_facility_in_area_with_water_stress_Yes', wm_water_use),\n    C11_withdrawals_from_fresh_surface_water_MLpa = ('C11_withdrawals_from_fresh_surface_water_MLpa', 'sum'),\n    C12_withdrawals_from_brackish_surface_water_or_seawater_MLpa = ('C12_withdrawals_from_brackish_surface_water_or_seawater_MLpa', 'sum'),\n    C13_withdrawals_from_groundwater_renewable_MLpa = ('C13_withdrawals_from_groundwater_renewable_MLpa', 'sum'),\n    C14_withdrawals_from_groundwater_non_renewable_MLpa = ('C14_withdrawals_from_groundwater_non_renewable_MLpa', 'sum'),\n    C15_withdrawals_from_produced_water_MLpa = ('C15_withdrawals_from_produced_water_MLpa', 'sum'),\n    C16_withdrawals_from_third_party_sources_MLpa = ('C16_withdrawals_from_third_party_sources_MLpa', 'sum'),\n    C19_discharges_to_fresh_surface_water_MLpa = ('C19_discharges_to_fresh_surface_water_MLpa', 'sum'),\n    C20_discharges_to_brackish_surface_water_or_seawater_MLpa = ('C20_discharges_to_brackish_surface_water_or_seawater_MLpa', 'sum'),\n    C21_discharges_to_groundwater_MLpa = ('C21_discharges_to_groundwater_MLpa', 'sum'),\n    C22_discharges_to_third_party_destinations_MLpa = ('C22_discharges_to_third_party_destinations_MLpa', 'sum'),\n    C9_total_water_withdrawals_MLpa = ('C9_total_water_withdrawals_MLpa', 'sum'),\n    C17_total_water_discharges_at_facility_MLpa = ('C17_total_water_discharges_at_facility_MLpa', 'sum'),\n    C23_total_water_consumption_at_facility_MLpa = ('C23_total_water_consumption_at_facility_MLpa', 'sum'),\n    C9_C17_C23_water_total_use_MLpa = ('C9_C17_C23_water_total_use_MLpa', 'sum'),\n    C10_water_total_withdrawals_vs_last_year = ('C10_water_total_withdrawals_vs_last_year', wm_water_use),\n    C18_water_total_discharges_vs_last_year = ('C18_water_total_discharges_vs_last_year', wm_water_use),\n    C24_water_total_consumption_vs_last_year = ('C24_water_total_consumption_vs_last_year', wm_water_use),\n    C10_C18_C24_water_total_use_vs_last_year = ('C10_C18_C24_water_total_use_vs_last_year', wm_water_use)\n    )\n\nW51_by_company['C10_C18_C24_water_total_use_vs_last_year'] = W51_by_company['C10_water_total_withdrawals_vs_last_year'] + W51_by_company['C18_water_total_discharges_vs_last_year'] + W51_by_company['C24_water_total_consumption_vs_last_year']\n\n\n## Country\nW51_by_country = W51.groupby(['W5.1_C3Country/Area & River basin_G'], as_index=False).agg(\n    facility_count = ('row_number', 'count'),\n    C6_facility_in_area_with_water_stress_Yes = ('C6_facility_in_area_with_water_stress_Yes', wm_water_use),\n    C11_withdrawals_from_fresh_surface_water_MLpa = ('C11_withdrawals_from_fresh_surface_water_MLpa', 'sum'),\n    C12_withdrawals_from_brackish_surface_water_or_seawater_MLpa = ('C12_withdrawals_from_brackish_surface_water_or_seawater_MLpa', 'sum'),\n    C13_withdrawals_from_groundwater_renewable_MLpa = ('C13_withdrawals_from_groundwater_renewable_MLpa', 'sum'),\n    C14_withdrawals_from_groundwater_non_renewable_MLpa = ('C14_withdrawals_from_groundwater_non_renewable_MLpa', 'sum'),\n    C15_withdrawals_from_produced_water_MLpa = ('C15_withdrawals_from_produced_water_MLpa', 'sum'),\n    C16_withdrawals_from_third_party_sources_MLpa = ('C16_withdrawals_from_third_party_sources_MLpa', 'sum'),\n    C19_discharges_to_fresh_surface_water_MLpa = ('C19_discharges_to_fresh_surface_water_MLpa', 'sum'),\n    C20_discharges_to_brackish_surface_water_or_seawater_MLpa = ('C20_discharges_to_brackish_surface_water_or_seawater_MLpa', 'sum'),\n    C21_discharges_to_groundwater_MLpa = ('C21_discharges_to_groundwater_MLpa', 'sum'),\n    C22_discharges_to_third_party_destinations_MLpa = ('C22_discharges_to_third_party_destinations_MLpa', 'sum'),\n    C9_total_water_withdrawals_MLpa = ('C9_total_water_withdrawals_MLpa', 'sum'),\n    C17_total_water_discharges_at_facility_MLpa = ('C17_total_water_discharges_at_facility_MLpa', 'sum'),\n    C23_total_water_consumption_at_facility_MLpa = ('C23_total_water_consumption_at_facility_MLpa', 'sum'),\n    C9_C17_C23_water_total_use_MLpa = ('C9_C17_C23_water_total_use_MLpa', 'sum'),\n    C10_water_total_withdrawals_vs_last_year = ('C10_water_total_withdrawals_vs_last_year', wm_water_use),\n    C18_water_total_discharges_vs_last_year = ('C18_water_total_discharges_vs_last_year', wm_water_use),\n    C24_water_total_consumption_vs_last_year = ('C24_water_total_consumption_vs_last_year', wm_water_use),\n    C10_C18_C24_water_total_use_vs_last_year = ('C10_C18_C24_water_total_use_vs_last_year', wm_water_use)\n    )\n\nW51_by_country['C10_C18_C24_water_total_use_vs_last_year'] = W51_by_country['C10_water_total_withdrawals_vs_last_year'] + W51_by_country['C18_water_total_discharges_vs_last_year'] + W51_by_country['C24_water_total_consumption_vs_last_year']\n\n\n\n### KPIs\n# # W43a_KPI['opportunity_index'] = (W43a_KPI['C7_risk_likelihood'] * W42_country['C6_risk_impact_magnitude']) / W42_country['C5_timeframe_years'] \n# W43a_KPI['opportunity_index'] = (W43a_KPI['C5_opportunity_impact_magnitude']) / W43a_KPI['C4_timeframe_years'] \n# W43a_KPI['opportunity_index'].replace(np.inf, np.nan, inplace=True)\n\n## Create KPI (0: ; 1: )\n# By facility\nW51_by_facility['KPI_rank_facility_in_area_with_water_stress'] =W51_by_facility['C6_facility_in_area_with_water_stress_Yes'].rank(pct=True)\nW51_by_facility['KPI_rank_total_water_withdrawals'] =W51_by_facility['C9_total_water_withdrawals_MLpa'].rank(pct=True)\nW51_by_facility['KPI_rank_total_water_discharges_at_facility'] =W51_by_facility['C17_total_water_discharges_at_facility_MLpa'].rank(pct=True)\nW51_by_facility['KPI_rank_total_water_consumption_at_facility'] =W51_by_facility['C23_total_water_consumption_at_facility_MLpa'].rank(pct=True)\nW51_by_facility['KPI_rank_water_total_use'] =W51_by_facility['C9_C17_C23_water_total_use_MLpa'].rank(pct=True)\nW51_by_facility['KPI_rank_water_total_withdrawals_vs_last_year'] =W51_by_facility['C10_water_total_withdrawals_vs_last_year'].rank(pct=True)\nW51_by_facility['KPI_rank_water_total_discharges_vs_last_year'] =W51_by_facility['C18_water_total_discharges_vs_last_year'].rank(pct=True)\nW51_by_facility['KPI_rank_water_total_consumption_vs_last_year'] =W51_by_facility['C24_water_total_consumption_vs_last_year'].rank(pct=True)\nW51_by_facility['KPI_rank_water_total_use_vs_last_year'] =W51_by_facility['C10_C18_C24_water_total_use_vs_last_year'].rank(pct=True)\n\n# By company\nW51_by_company['KPI_rank_facility_in_area_with_water_stress'] =W51_by_company['C6_facility_in_area_with_water_stress_Yes'].rank(pct=True)\nW51_by_company['KPI_rank_total_water_withdrawals'] =W51_by_company['C9_total_water_withdrawals_MLpa'].rank(pct=True)\nW51_by_company['KPI_rank_total_water_discharges_at_facility'] =W51_by_company['C17_total_water_discharges_at_facility_MLpa'].rank(pct=True)\nW51_by_company['KPI_rank_total_water_consumption_at_facility'] =W51_by_company['C23_total_water_consumption_at_facility_MLpa'].rank(pct=True)\nW51_by_company['KPI_rank_water_total_use'] =W51_by_company['C9_C17_C23_water_total_use_MLpa'].rank(pct=True)\nW51_by_company['KPI_rank_water_total_withdrawals_vs_last_year'] =W51_by_company['C10_water_total_withdrawals_vs_last_year'].rank(pct=True)\nW51_by_company['KPI_rank_water_total_discharges_vs_last_year'] =W51_by_company['C18_water_total_discharges_vs_last_year'].rank(pct=True)\nW51_by_company['KPI_rank_water_total_consumption_vs_last_year'] =W51_by_company['C24_water_total_consumption_vs_last_year'].rank(pct=True)\nW51_by_company['KPI_rank_water_total_use_vs_last_year'] =W51_by_company['C10_C18_C24_water_total_use_vs_last_year'].rank(pct=True)\n\n# By company\nW51_by_country['KPI_rank_facility_in_area_with_water_stress'] =W51_by_country['C6_facility_in_area_with_water_stress_Yes'].rank(pct=True)\nW51_by_country['KPI_rank_total_water_withdrawals'] =W51_by_country['C9_total_water_withdrawals_MLpa'].rank(pct=True)\nW51_by_country['KPI_rank_total_water_discharges_at_facility'] =W51_by_country['C17_total_water_discharges_at_facility_MLpa'].rank(pct=True)\nW51_by_country['KPI_rank_total_water_consumption_at_facility'] =W51_by_country['C23_total_water_consumption_at_facility_MLpa'].rank(pct=True)\nW51_by_country['KPI_rank_water_total_use'] =W51_by_country['C9_C17_C23_water_total_use_MLpa'].rank(pct=True)\nW51_by_country['KPI_rank_water_total_withdrawals_vs_last_year'] =W51_by_country['C10_water_total_withdrawals_vs_last_year'].rank(pct=True)\nW51_by_country['KPI_rank_water_total_discharges_vs_last_year'] =W51_by_country['C18_water_total_discharges_vs_last_year'].rank(pct=True)\nW51_by_country['KPI_rank_water_total_consumption_vs_last_year'] =W51_by_country['C24_water_total_consumption_vs_last_year'].rank(pct=True)\nW51_by_country['KPI_rank_water_total_use_vs_last_year'] =W51_by_country['C10_C18_C24_water_total_use_vs_last_year'].rank(pct=True)\n\n\n\n## Visualize results\n#W51.head(5)\n#W51_by_facility.head(5)\n#W51_by_company.head(5)\n#W51_by_country.head(5)\n# W51['W5.1_C24Comparison of total consumption with previous reporting year'].value_counts()\n# W51.size\n\n######################################################## TABLE 8.1a Water targets ###############################################################################\n\nW81a = w20[w20['question_number']=='W8.1a']\nW81a = W81a.drop(['survey_year', 'response_received_date','accounting_period_to','ors_response_id','submission_date','page_name','module_name',\n          'question_number','question_unique_reference','data_point_name','data_point_id','comments','table_columns_unique_reference','column_number'], axis=1)\nW81a=W81a.pivot(index=['account_number','organization','row_number','row_name'], columns='column_name', values='response_value').reset_index()\nW81a=W81a.sort_values(by=['account_number','organization','row_number'])\n# C11=C11.dropna()\n\n## Drop rows where == nan\n# W81a = W81a[W81a['row_number']!=0]\n\n## Convert (and rename) strings to float:\nfloat_cols = [\n'W8.1a_C7Baseline year',\n'W8.1a_C8Start year',\n'W8.1a_C9Target year',\n'W8.1a_C10% of target achieved'\n]\nfloat_cols_renamed = [\n'C7_baseline_year',\n'C8_start_year',\n'C9_target_year',\n'C10_pctg_target_achieved'\n]\nix = 0\nfor col in float_cols:\n    W81a[col] = W81a[col].astype(float)\n    W81a[float_cols_renamed[ix]] = W81a[col]\n    del W81a[col]\n    ix += 1\n\nW81a['C10_pctg_target_achieved'] = W81a['C10_pctg_target_achieved'] / 100\n\n\n## Re-categorize col values with low frequencies:\ncols_to_recat = ['W8.1a_C2Category of target', 'W8.1a_C3Level', 'W8.1a_C4Primary motivation', 'W8.1a_C6Quantitative metric']\nW81a = W81a.apply(lambda x: x.mask(x.map(x.value_counts())<10, 'Other') if x.name in cols_to_recat else x)\n\n\n\"\"\"\n!!! WARNING !!!\nTargets are too disparate/sparse, meaning a comparison of the 'Other' classifications would be inaccurate.\nAt this point, let's calculate overall achievement metrics & KPI\n\"\"\"\n\n\n## Drop last cols:\nW81a = W81a.drop([\n#     'row_number'\n    'row_name'\n    ,'W8.1a_C11Please explain'\n    ,'W8.1a_C1Target reference number'\n    ,'W8.1a_C5Description of target'\n    ], axis=1)\n\n\n## Calculate extra features:\n# Calculate years to achievement:\nW81a['target_years_to_achievement'] = W81a['C9_target_year'] - W81a['C8_start_year'] + 1 \nW81a.loc[W81a['C9_target_year']>2020, 'years_left'] = W81a['C9_target_year']-2020\nW81a.loc[W81a['C9_target_year']<=2020, 'years_left'] = 0\nW81a.loc[W81a['C9_target_year']>2020, 'years_past'] = W81a['C9_target_year']-W81a['C8_start_year'] + 1\nW81a.loc[W81a['C9_target_year']<=2020, 'years_past'] = W81a['target_years_to_achievement']\n\n# Progress\nW81a['target_pctg_achievement_per_year']= 1/W81a['target_years_to_achievement']\nW81a['actual_pctg_achievement_per_year']= W81a['C10_pctg_target_achieved']/W81a['years_past']\nW81a['actual_years_to_achiev'] = 1/W81a['actual_pctg_achievement_per_year']\n\nW81a['target_pctg_achievement_per_year'].replace(np.inf, np.nan, inplace=True)\nW81a['target_pctg_achievement_per_year'].replace(np.NINF, np.nan, inplace=True)\nW81a['actual_pctg_achievement_per_year'].replace(np.inf, np.nan, inplace=True)\nW81a['actual_pctg_achievement_per_year'].replace(np.NINF, np.nan, inplace=True)\nW81a['actual_years_to_achiev'].replace(np.inf, np.nan, inplace=True)\nW81a['actual_years_to_achiev'].replace(np.NINF, np.nan, inplace=True)\n\nW81a['Years_diff'] = W81a['target_years_to_achievement']-W81a['actual_years_to_achiev']\nW81a.loc[(W81a['Years_diff']>W81a['Years_diff'].std()) | (W81a['Years_diff']<-W81a['Years_diff'].std()), 'Years_diff'] = np.nan\nW81a['Years_diff'].replace(np.inf, np.nan, inplace=True)\nW81a['Years_diff'].replace(np.NINF, np.nan, inplace=True)\n\nW81a['diff_actual_vs_achieved_pctg_achievement_per_year'] = W81a['actual_pctg_achievement_per_year'] / W81a['target_pctg_achievement_per_year']\n\n\n## Group by company\nW81a_by_company = W81a.groupby(['account_number','organization'], as_index=False).agg(\n    targets_count = ('row_number', 'count'),\n    C7_baseline_year=('C7_baseline_year', 'mean'),\n    C8_start_year=('C8_start_year', 'mean'),\n    C9_target_year=('C9_target_year', 'mean'),\n    C10_pctg_target_achieved=('C10_pctg_target_achieved', 'mean'),\n    target_years_to_achievement=('target_years_to_achievement', 'mean'),\n    years_left=('years_left', 'mean'),\n    years_past=('years_past', 'mean'),\n    target_pctg_achievement_per_year=('target_pctg_achievement_per_year', 'mean'),\n    actual_pctg_achievement_per_year=('actual_pctg_achievement_per_year', 'mean'),\n    actual_years_to_achiev=('actual_years_to_achiev', 'mean'),\n    Years_diff=('Years_diff', 'mean'),\n    diff_actual_vs_achieved_pctg_achievement_per_year = ('Years_diff', 'mean')\n    ).dropna().reset_index()\n\n\n\n## Calculate KPIs\nW81a_by_company['KPI_rank_objective_strategy'] = W81a['Years_diff'].rank(pct=True)\nW81a_by_company['KPI_rank_objective_progress'] = W81a['C10_pctg_target_achieved'].rank(pct=True)\nW81a_by_company['KPI_rank_objective_ambition'] = W81a['diff_actual_vs_achieved_pctg_achievement_per_year'].rank(pct=True)\n\nW81a_by_company = W81a_by_company.sort_values(by=['KPI_rank_objective_strategy']).reset_index()\n\n\n\n## Visualize results\n#W81a.head(5)\n#W81a_by_company.head(5)\n# W81a['W8.1a_C4Primary motivation'].value_counts()\n# W81a.size\n\n### Climate\n##################################################################### Make list of all df with KPIs: ###############################################################\n# Companies \nclimate_df_company = [C23a_KPI,\n    C24a_KPI,\n    C41a_KPI,\n    C75_KPI,\n    C82a,\n    C82d_KPI]\n\nclimate_df_company_suffixes = [\n    '_risks_identified',\n    '_opportunities_identified',\n    '_emissions_targets',\n    '_emissions_targets_by_country',\n    '_energy_consumption',\n    '_energy_consumption_details',\n    '_emissions_targets_by_country']\n\n\n## Merge dataframes together\n# Companies\ncompany_climate_cleaned_2020 = q20[['account_number', 'organization']].drop_duplicates(ignore_index=True)\nix = 0\ncount_cols = 0\nfor df_ in climate_df_company:\n    company_climate_cleaned_2020 = pd.merge(company_climate_cleaned_2020, df_.copy().add_suffix(climate_df_company_suffixes[ix]), \n                                 how='outer', \n                                 left_on='account_number', \n                                 right_on='account_number'+climate_df_company_suffixes[ix], \n                                ) \n\n    ix += 1\n    count_cols += len(df_.columns)\n\n# Sort\ncompany_climate_cleaned_2020 = company_climate_cleaned_2020.sort_values('account_number', ascending=True, ignore_index=True)\n\n# Make a single df with only KPIs   \nKPIs_cols = ['account_number', 'organization']\nKPIs_cols += [col for col in company_climate_cleaned_2020.columns if 'KPI_rank_' in col]\ncompany_climate_KPIs_2020 = company_climate_cleaned_2020[KPIs_cols]\n\n# Do the same for country df\ncountry_climate_cleaned_2020 = C75_country.copy()\nKPIs_cols = ['C7.5_C1Country/Region']\nKPIs_cols += [col for col in country_climate_cleaned_2020.columns if 'KPI_rank_' in col]\ncountry_climate_KPIs_2020 = country_climate_cleaned_2020[KPIs_cols]\n\n\n\n### Water\n## Make list of all df with KPIs:\n# Companies \nwater_df_company = [\n    W11_c,\n    W12b_c,\n    W41b,\n    W42_company,\n    W43a_KPI,\n    W51_by_company,\n    W81a_by_company\n    ]\n\nwater_df_company_suffixes = [\n    '_water_importance',\n    '_water_use',\n    '_risks_overall_identified',\n    '_risks_identified',\n    '_opportunities_identified',\n    '_water_use_by_facility',\n    '_water_targets'\n    ]\n\n# Country\nwater_df_country = [\n    W41c_by_country,\n    W42_country,\n    W51_by_country\n    ]\n\nwater_df_country_suffixes = [\n    '_risks_overall_identified_by_geography',\n    '_risks_identified',\n    '_water_use_by_facility'\n    ]\n\n\n## Merge dataframes together\n# Companies\ncompany_water_cleaned_2020 = w20[['account_number', 'organization']].drop_duplicates(ignore_index=True)\nix = 0\ncount_cols = 0\nfor df_ in water_df_company:\n    try:\n        company_water_cleaned_2020 = pd.merge(company_water_cleaned_2020, df_.copy().add_suffix(water_df_company_suffixes[ix]), \n                                     how='outer', \n                                     left_on='account_number', \n                                     right_on='account_number'+water_df_company_suffixes[ix], \n                                    ) \n    except:\n        print(df_.columns)\n\n    ix += 1\n    count_cols += len(df_.columns)\n\n# Sort\ncompany_water_cleaned_2020 = company_water_cleaned_2020.sort_values('account_number', ascending=True, ignore_index=True)\n\n# Make a single df with only KPIs   \nKPIs_cols = ['account_number', 'organization']\nKPIs_cols += [col for col in company_water_cleaned_2020.columns if 'KPI_rank_' in col]\ncompany_water_KPIs_2020 = company_water_cleaned_2020[KPIs_cols]\n\n\n# Countries\ncountry_water_cleaned_2020 = W41c_by_country.copy().add_suffix(water_df_country_suffixes[0])\ncountry_water_cleaned_2020 = pd.merge(country_water_cleaned_2020, \n                                     W42_country.copy().add_suffix(water_df_country_suffixes[1]), \n                                     how='outer', \n                                     left_on='W4.1c_C1Country/Area & River basin_G'+water_df_country_suffixes[0], \n                                     right_on='W4.2_C1Country/Area & River basin_G'+water_df_country_suffixes[1], \n                                    ) \ncountry_water_cleaned_2020 = pd.merge(country_water_cleaned_2020, \n                                     W51_by_country.copy().add_suffix(water_df_country_suffixes[2]), \n                                     how='outer', \n                                     left_on='W4.1c_C1Country/Area & River basin_G'+water_df_country_suffixes[0], \n                                     right_on='W5.1_C3Country/Area & River basin_G'+water_df_country_suffixes[2], \n                                    ) \n\ncountry_water_cleaned_2020 = country_water_cleaned_2020.rename(columns={'W4.1c_C1Country/Area & River basin_G'+water_df_country_suffixes[0]: 'country_or_region'})\n\n# Sort\ncountry_water_cleaned_2020 = country_water_cleaned_2020.sort_values('country_or_region', ascending=True, ignore_index=True)\n\n# Make a single df with only KPIs   \nKPIs_cols = ['country_or_region']\nKPIs_cols += [col for col in country_water_cleaned_2020.columns if 'KPI_rank_' in col]\ncountry_water_KPIs_2020 = country_water_cleaned_2020[KPIs_cols]\n\n\n## Facilities\nfacility_water_cleaned_2020 = W51_by_facility.copy()\nKPIs_cols = ['account_number','organization', 'C4_C5_facility_coordinates_lon_lat']\nKPIs_cols += [col for col in facility_water_cleaned_2020.columns if 'KPI_rank_' in col]\nfacility_water_KPIs_2020 = facility_water_cleaned_2020[KPIs_cols]\n\n\n\n### Save all df into CSV:\ncompany_climate_cleaned_2020.to_csv('./df_KPIs/Final/company_climate_cleaned_2020.csv')\ncompany_climate_KPIs_2020.to_csv('./df_KPIs/Final/company_climate_KPIs_2020.csv')\ncountry_climate_cleaned_2020.to_csv('./df_KPIs/Final/country_climate_cleaned_2020.csv')\ncountry_climate_KPIs_2020.to_csv('./df_KPIs/Final/country_climate_KPIs_2020.csv')\ncompany_water_cleaned_2020.to_csv('./df_KPIs/Final/company_water_cleaned_2020.csv')\ncompany_water_KPIs_2020.to_csv('./df_KPIs/Final/company_water_KPIs_2020.csv')\ncountry_water_cleaned_2020.to_csv('./df_KPIs/Final/country_water_cleaned_2020.csv')\ncountry_water_KPIs_2020.to_csv('./df_KPIs/Final/country_water_KPIs_2020.csv')\nfacility_water_cleaned_2020.to_csv('./df_KPIs/Final/facility_water_cleaned_2020.csv')\nfacility_water_KPIs_2020.to_csv('./df_KPIs/Final/facility_water_KPIs_2020.csv')\n\n\n# print(count_cols) # 90+2 = 92\n# print(len(company_water_cleaned_2020.columns))\n# print(len(climate_cleaned_2020.index))\n# climate_cleaned_2020.head(5)\n# climate_KPIs_2020.head(5)\n#country_water_KPIs_2020.head(5)\n\n\n################################################################################################################################################################\n################################################################################################################################################################\n################################################################################################################################################################\n###################################################                                                      #######################################################\n###################################################                 C I T I E S - 2 0 2 0                #######################################################\n###################################################                                                      #######################################################\n################################################################################################################################################################\n################################################################################################################################################################\n################################################################################################################################################################\n\n#### ########################################################### TABLE 2.1 - CLIMATE HAZARD #################################################################### \n#Please list the most significant climate hazards faced by your city and indicate the probability and consequence of these hazards, \n### as well as the expected future change in frequency and intensity. Please also select the most relevant assets or services that are affected by the climate hazard \n### and provide a description of the impact.\n\n# Import table\n\nC21 = c20[c20['Question Number']=='2.1']\nC21 = C21.drop(['Questionnaire', 'Year Reported to CDP','Parent Section','Section','Comments','File Name','Last update','Row Name','Column Number', 'Question Name',\n               'Question Number','CDP Region', 'Country'], axis=1)\n\n#Prepare table for analysis\n\ndef convert(list): \n    return tuple(list) \nC21 =C21.groupby(['Account Number', 'Organization','Column Name', 'Row Number'])['Response Answer'].apply(list).reset_index()\nC21['Response Answer'] = tuple(list(C21['Response Answer']))\nC21 ['Response Answer'] = C21['Response Answer'].apply(convert)\nC21=C21.pivot(index=['Account Number','Organization','Row Number'], columns='Column Name', values='Response Answer').reset_index()\nC21 = C21.drop(['Most relevant assets / services affected overall','Please describe the impacts experienced so far, and how you expect the hazard to impact in   the future',\n               'Please identify which vulnerable populations are affected','Social impact of hazard overall'], axis=1)\ndef convertTuple(tup): \n    str = functools.reduce(operator.add, (tup)) \n    return str\ncollist = C21.columns.values.tolist()[3:]\nC21[collist] = C21[collist].apply(convertTuple)\n\n# Current risk likelihood indicator  \n\nC21['risk_likelihood_curr'] = np.nan\nC21.loc[C21['Current probability of hazard']=='High', 'risk_likelihood_curr'] = 5/5\nC21.loc[C21['Current probability of hazard']=='Medium High', 'risk_likelihood_curr'] = 4/5\nC21.loc[C21['Current probability of hazard']=='Medium', 'risk_likelihood_curr'] = 3/5\nC21.loc[C21['Current probability of hazard']=='Medium Low', 'risk_likelihood_curr'] = 2/5\nC21.loc[C21['Current probability of hazard']=='Low', 'risk_likelihood_curr'] = 1/5\nC21.loc[C21['Current probability of hazard']=='Does not currently impact the city', 'risk_likelihood_curr'] = 0/5\n\n# Current Risk magnitude indicator\n\nC21['risk_impact_magnitude_curr'] = np.nan\nC21.loc[C21['Current magnitude of hazard']=='High', 'risk_impact_magnitude_curr'] = 5/5\nC21.loc[C21['Current magnitude of hazard']=='Medium High', 'risk_impact_magnitude_curr'] = 4/5\nC21.loc[C21['Current magnitude of hazard']=='Medium', 'risk_impact_magnitude_curr'] = 3/5\nC21.loc[C21['Current magnitude of hazard']=='Medium Low', 'risk_impact_magnitude_curr'] = 2/5\nC21.loc[C21['Current magnitude of hazard']=='Low', 'risk_impact_magnitude_curr'] = 1/5\nC21.loc[C21['Current magnitude of hazard']=='Does not currently impact the city', 'risk_likelihood_curr'] = 0/5\n\n# Time horizon Variable\n\nC21['time_horizon_years'] = np.nan\nC21.loc[C21['When do you first expect to experience those changes in frequency and intensity?']=='Immediately', 'time_horizon_years'] = 1\nC21.loc[C21['When do you first expect to experience those changes in frequency and intensity?']=='Short-term (by 2025)', 'time_horizon_years'] = 5\nC21.loc[C21['When do you first expect to experience those changes in frequency and intensity?']=='Medium-term (2026-2050)', 'time_horizon_years'] = 30\nC21.loc[C21['When do you first expect to experience those changes in frequency and intensity?']=='Long-term (after 2050)', 'time_horizon_years'] = 50\n\n# Future risk likelihood indicator\n\nC21['risk_likelihood_fut_temp'] = np.nan\nC21.loc[C21['Current probability of hazard']=='High', 'risk_likelihood_fut_temp'] = 5\nC21.loc[C21['Current probability of hazard']=='Medium High', 'risk_likelihood_fut_temp'] = 4\nC21.loc[C21['Current probability of hazard']=='Medium', 'risk_likelihood_fut_temp'] = 3\nC21.loc[C21['Current probability of hazard']=='Medium Low', 'risk_likelihood_fut_temp'] = 2\nC21.loc[C21['Current probability of hazard']=='Low', 'risk_likelihood_fut_temp'] = 1\nC21.loc[C21['Current probability of hazard']=='Does not currently impact the city', 'risk_likelihood_fut_temp'] = 0\n\n# Change in future intensity - trend factor\n\nC21['risk_add_fut_temp'] = np.nan\nC21.loc[C21['Future change in intensity']=='Increasing','risk_add_fut_temp']=1\nC21.loc[C21['Future change in intensity']=='Decreasing','risk_add_fut_temp']=-1\nC21.loc[C21['Future change in intensity']=='None','risk_add_fut_temp']=0\nC21.loc[C21['Future change in intensity']=='Not expected to happen in the future','risk_add_fut_temp']=-5 # will set the risk to zero if currently existing\n\n# Change in future likelihood indicator\n\nC21['risk_likelihood_fut'] = C21['risk_likelihood_fut_temp']+ C21['risk_add_fut_temp']\nC21.loc[C21['risk_likelihood_fut'] > 5.0,'risk_likelihood_fut'] = 5.0       \nC21.loc[C21['risk_likelihood_fut'] < 0,'risk_likelihood_fut'] = 0\nC21['risk_likelihood_fut'] = C21['risk_likelihood_fut']/5\nC21 = C21.drop(['risk_likelihood_fut_temp','risk_add_fut_temp'],axis=1)\n\n# Future risk magnitude indicator\n\nC21['risk_impact_magnitude_fut'] = np.nan\nC21.loc[C21['Future expected magnitude of hazard']=='High', 'risk_impact_magnitude_fut'] = 5/5\nC21.loc[C21['Future expected magnitude of hazard']=='Medium High', 'risk_impact_magnitude_fut'] = 4/5\nC21.loc[C21['Future expected magnitude of hazard']=='Medium', 'risk_impact_magnitude_fut'] = 3/5\nC21.loc[C21['Future expected magnitude of hazard']=='Medium Low', 'risk_impact_magnitude_fut'] = 2/5\nC21.loc[C21['Future expected magnitude of hazard']=='Low', 'risk_impact_magnitude_fut'] = 1/5\nC21.loc[C21['Future expected magnitude of hazard']=='Does not currently impact the city', 'risk_likelihood_fut'] = 0/5\n\n### KPI: Yearly risk exposure (Likelihood * Magnitude / Years) - Current & Future\nC21['risk_index_curr'] = (C21['risk_likelihood_curr'] * C21['risk_impact_magnitude_curr']) / C21['time_horizon_years'] \nC21['risk_index_curr'].replace(np.inf, np.nan, inplace=True)\nC21['risk_index_fut'] = (C21['risk_likelihood_fut'] * C21['risk_impact_magnitude_fut']) / C21['time_horizon_years'] \nC21['risk_index_fut'].replace(np.inf, np.nan, inplace=True)\n\n# Grouping at city level by using SIMPLE AVERAGE\n\nC21_city= C21.drop(['Row Number','Climate Hazards','Current magnitude of hazard','Current probability of hazard','Did this hazard significantly impact your city before 2020?',\n                'Future change in frequency','Future change in intensity','Future expected magnitude of hazard','When do you first expect to experience those changes in frequency and intensity?'],\n               axis=1)\n                \nC21_city =C21_city.groupby(['Account Number', 'Organization'])[['risk_likelihood_curr', 'risk_impact_magnitude_curr','time_horizon_years',\n                                                                                 'risk_likelihood_fut','risk_impact_magnitude_fut','risk_index_curr',\n                                                                                 'risk_index_fut']].mean().reset_index()\n\n### KPI: Rank by city - Current & Future Yearly risk exposure - The higher the number, the more the exposure to risk\n\nC21_city['KPI_21_risk_index_curr_rank'] = C21_city['risk_index_curr'].rank(pct=True)\nC21_city['KPI_21_risk_index_fut_rank'] = C21_city['risk_index_fut'].rank(pct=True)\n\n################################################### Import the Social Vulnerability Index by CDP #####################################################################\n#a lot of manual cleaning is required as no merging key is provided. Available for USA Only\n\ncities=c20[['Account Number','Organization','Country','CDP Region']].groupby(['Account Number','Organization','Country','CDP Region']).sum().reset_index()\ncities= cities[cities['Country']=='United States of America']\n\n# Remove extra text to be able to merge by city name\n\ncities['Organization'] = cities['Organization'].map(lambda x: x.replace('Town of',''))\ncities['Organization'] = cities['Organization'].map(lambda x: x.replace('Township of',''))\ncities['Organization'] = cities['Organization'].map(lambda x: x.replace('City of',''))\ncities['Organization'] = cities['Organization'].map(lambda x: x.replace('City and County of',''))\ncities['Organization'] = cities['Organization'].map(lambda x: x.replace('Metropolitan Government of',''))\ncities['Organization'] = cities['Organization'].map(lambda x: x.lstrip())\n\n# Prepare for merge\n\ncities['Organization_new']=cities['Organization']\ncities.loc[cities['Organization_new'].str.contains(', [A-Z][A-Z]', regex=True), 'Organization_new'] = cities['Organization_new'].str[:-4]\nct =pd.merge(cities, SVI_C, left_on='Organization_new',right_on='COUNTY',how='left')\n\n# Clean after merge - cities in wrong states are picked up - they need to be eliminated\n\nct = ct[ct['ST'].notna()]\nct= ct[ct['Organization']!='Columbia, MO']\nct = ct.drop(ct[(ct['Organization'] == 'York, ME') & (ct['ST_ABBR'] == 'NE')].index)\nct = ct.drop(ct[(ct['Organization'] == 'York, ME') & (ct['ST_ABBR'] == 'PA')].index)\nct = ct.drop(ct[(ct['Organization'] == 'York, ME') & (ct['ST_ABBR'] == 'SC')].index)\nct = ct.drop(ct[(ct['Organization'] == 'York, ME') & (ct['ST_ABBR'] == 'VA')].index)\nct = ct.drop(ct[(ct['Organization'] == 'Dallas') & (ct['ST_ABBR'] == 'MO')].index)\nct = ct.drop(ct[(ct['Organization'] == 'Dallas') & (ct['ST_ABBR'] == 'IA')].index)\nct = ct.drop(ct[(ct['Organization'] == 'Dallas') & (ct['ST_ABBR'] == 'AR')].index)\nct = ct.drop(ct[(ct['Organization'] == 'Dallas') & (ct['ST_ABBR'] == 'AL')].index)\nct = ct.drop(ct[(ct['Organization'] == 'Houston') & (ct['ST_ABBR'] == 'AL')].index)\nct = ct.drop(ct[(ct['Organization'] == 'Houston') & (ct['ST_ABBR'] == 'GA')].index)\nct = ct.drop(ct[(ct['Organization'] == 'Houston') & (ct['ST_ABBR'] == 'MN')].index)\nct = ct.drop(ct[(ct['Organization'] == 'Houston') & (ct['ST_ABBR'] == 'TN')].index)\nct= ct[ct['Organization']!='Henderson']\nct = ct.drop(ct[(ct['Organization'] == 'Richmond, VA') & (ct['ST_ABBR'] == 'GA')].index)\nct = ct.drop(ct[(ct['Organization'] == 'Richmond, VA') & (ct['ST_ABBR'] == 'NY')].index)\nct = ct.drop(ct[(ct['Organization'] == 'Richmond, VA') & (ct['ST_ABBR'] == 'NC')].index)\nct= ct[ct['Organization']!='Fremont']\nct= ct[ct['Organization']!='Cleveland']\nct= ct[ct['Organization']!='Northampton, MA']\nct= ct[ct['Organization']!='Miami']\nct= ct[ct['Organization']!='Buffalo']\nct = ct.drop(ct[(ct['Organization'] == 'Santa Cruz, CA') & (ct['ST_ABBR'] == 'AZ')].index)\nct= ct[ct['Organization']!='Ashland, OR']\nct= ct[ct['Organization']!='Charlotte']\nct= ct[ct['Organization']!='Berkeley']\nct= ct[ct['Organization']!='Aurora, IL']\nct= ct[ct['Organization']!='Columbus']\nct= ct[ct['Organization']!='Oakland']\nct= ct[ct['Organization']!='Palo Alto']\nct= ct[ct['Organization']!='Salem, MA']\nct= ct[ct['Organization']!='Guilford, VT']\nct= ct[ct['Organization']!='Lexington, MA']\n\n# Merge datasets\n# Legend:\n# Socioeconomic – RPL_THEME1\n# Household Composition & Disability – RPL_THEME2\n# Minority Status & Language – RPL_THEME3\n# Housing Type & Transportation – RPL_THEME4\n# The overall tract summary ranking variable is RPL_THEMES.\n# SVI is created by using percentile ranking values range from 0 to 1, with higher values indicating greater vulnerability.\n\nct=ct[['Account Number','Organization','ST_ABBR','FIPS','E_TOTPOP','RPL_THEME1','RPL_THEME2','RPL_THEME3','RPL_THEME4','RPL_THEMES']]\nct =pd.merge(ct,C21_city, left_on='Account Number',right_on='Account Number',how='left')\n\n########################################################### TABLE 3.0 - ADAPTATION #############################################################################\n\n#Please describe the main actions you are taking to reduce the risk to, and vulnerability of, your city’s infrastructure, \n###services, citizens, and businesses from climate change as identified in the Climate Hazards section.\n\n# Import table\n\nC30 = c20[c20['Question Number']=='3.0']\nC30 = C30.drop(['Questionnaire', 'Year Reported to CDP','Parent Section','Section','Comments','File Name','Last update','Row Name','Column Number', 'Question Name',\n               'Question Number','CDP Region', 'Country'], axis=1)\n\n# Prepare table for analysis\n\ndef convert(list): \n    return tuple(list) \nC30 = C30.groupby(['Account Number', 'Organization','Column Name', 'Row Number'])['Response Answer'].apply(list).reset_index()\nC30['Response Answer'] = tuple(list(C30['Response Answer']))\nC30['Response Answer'] = C30['Response Answer'].apply(convert)\nC30 = C30.pivot(index=['Account Number','Organization','Row Number'], columns='Column Name', values='Response Answer').reset_index()\nC30 = C30.drop(['Action description and implementation progress','Action title','Web link','Finance status'],axis=1)\ndef convertTuple(tup): \n    str = functools.reduce(operator.add, (tup)) \n    return str\nC30['Action'] = C30['Action'].apply(convertTuple)\nC30['Climate hazards'] = C30['Climate hazards'].apply(convertTuple)\nC30['Co-benefit area'] = C30['Co-benefit area'].apply(convertTuple)\nC30['Majority funding source'] = C30['Majority funding source'].apply(convertTuple)\nC30['Sectors/areas adaptation action applies to'] = C30['Sectors/areas adaptation action applies to'].apply(convertTuple)\nC30['Status of action'] = C30['Status of action'].apply(convertTuple)\nC30['Total cost of the project (currency)'] = C30['Total cost of the project (currency)'].apply(convertTuple)\nC30['Total cost provided by the local government (currency)'] = C30['Total cost provided by the local government (currency)'].apply(convertTuple)\nC30['Total cost provided by the majority funding source (currency)'] = C30['Total cost provided by the majority funding source (currency)'].apply(convertTuple)\nC30['Means of implementation'] = C30['Means of implementation'].apply(convertTuple)\n\n# Group 'Other please specify category'\n\nC30= C30.dropna(subset=['Action'])\nC30.loc[C30['Action'].str.contains('Other, please specify:'), 'Action'] = 'Other'\nC30= C30.dropna(subset=['Status of action'])\nC30.loc[C30['Status of action'].str.contains('Other, please specify:'), 'Status of action'] = 'Other'\nC30= C30.dropna(subset=['Majority funding source'])\nC30.loc[C30['Majority funding source'].str.contains('Other, please specify:'), 'Majority funding source'] = 'Other'\n\n# Import population figures\n\nC05 = c20[c20['Question Number']=='0.5']\nC05 = C05.drop(['Questionnaire', 'Year Reported to CDP','Parent Section','Section','Comments','File Name','Last update','Row Name','Column Number', 'Question Name',\n               'Question Number','CDP Region', 'Country'], axis=1)\nC05 = C05.pivot(index=['Account Number','Organization','Row Number'], columns='Column Name', values='Response Answer').reset_index()\npop = C05[['Account Number', 'Current population']]\n\n# Import currency - most recurrent currencies are converted to USD\n\nC04 = c20[c20['Question Number']=='0.4']\nC04 = C04.drop(['Questionnaire', 'Year Reported to CDP','Parent Section','Section','Comments','File Name','Last update','Row Name','Column Number', 'Question Name',\n               'Question Number','CDP Region', 'Country'], axis=1)\nC04 = C04.pivot(index=['Account Number','Organization','Row Number'], columns='Column Name', values='Response Answer').reset_index()\nC04.columns = C04.columns.fillna('Currency')\n\n#Exchange Rates for main currencies to USD as of 25/11/20\n\nC04['Exchange Rate'] = np.nan\nC04.loc[C04['Currency']=='USD US Dollar', 'Exchange Rate'] = 1\nC04.loc[C04['Currency']=='BRL Brazilian Real', 'Exchange Rate'] = 0.19\nC04.loc[C04['Currency']=='EUR Euro', 'Exchange Rate'] = 1.19\nC04.loc[C04['Currency']=='MXN Mexican Peso', 'Exchange Rate'] = 0.05\nC04.loc[C04['Currency']=='GBP Pound Sterling', 'Exchange Rate'] = 1.34\nC04.loc[C04['Currency']=='CAD Canadian Dollar', 'Exchange Rate'] = 0.77\nC04.loc[C04['Currency']=='COP Colombian Peso', 'Exchange Rate'] = 0.00028\nC04.loc[C04['Currency']=='PEN Nuevo Sol', 'Exchange Rate'] = 0.28\nC04.loc[C04['Currency']=='DKK Danish Krone', 'Exchange Rate'] = 0.16\nC04.loc[C04['Currency']=='AUD Australian Dollar', 'Exchange Rate'] = 0.74\nC04.loc[C04['Currency']=='SEK Swedish Krona', 'Exchange Rate'] = 0.12\ncurr = C04[['Account Number','Exchange Rate']]\n\n# Merge population & currencies\n\nC30 =pd.merge(C30,pop, left_on='Account Number',right_on='Account Number',how='left')\nC30 =pd.merge(C30,curr, left_on='Account Number',right_on='Account Number',how='left')\nC30= C30.dropna(subset=['Total cost of the project (currency)'])\nC30= C30.dropna(subset=['Exchange Rate'])\n\n# Convert total project cost in USD\n\nC30['Total cost of the project (currency)'] = C30['Total cost of the project (currency)'].astype(float)\nC30['Total Project Cost USD']= C30['Total cost of the project (currency)']*C30['Exchange Rate']\n\n### KPI: Calculate spending per capite\n\nC30['Current population'] = C30['Current population'].astype(float)\nC30['Spend_per_capite'] = C30['Total Project Cost USD']/C30['Current population']\n\n# Group by city - use sum to group spending per capite\n\nC30_KPI = C30.groupby(['Account Number', 'Organization'])['Spend_per_capite'].sum().reset_index()\n\n#Some cities omitted the info and entered 0 - exclude\n\nC30_KPI = C30_KPI[C30_KPI['Spend_per_capite']!=0]\n\n# There are some outliers - Big infrastructure projects\n#C30_KPI['Spend_per_capite'].describe()\n\n## KPI: Spending per capite ranking - the more the city spends, the higher the rank\n\nC30_KPI['KPI_30_spend_per_capite_rank'] = C30_KPI['Spend_per_capite'].rank(pct=True)\n\n\n\n# Compare current risk with spending per capite and vulnerability\nct1 =pd.merge(ct,C30_KPI, left_on='Account Number',right_on='Account Number',how='left')\nct1= ct1.dropna(subset=['Spend_per_capite'])\n\n\n########################################################################## TABLE 4.6b - CITY WIDE EMISSIONS ##########################################################\n\n#Please provide a summary of emissions by sector and scope as defined in the Global Protocol for Community Greenhouse Gas Emissions Inventories (GPC) in the table below.\n\n# Import table \nC46b = c20[c20['Question Number']=='4.6b']\nC46b = C46b.drop(['Questionnaire', 'Year Reported to CDP','Parent Section','Section','Comments','File Name','Last update','Column Number', 'Question Name',\n               'Question Number','CDP Region', 'Country'], axis=1)\n\n# Prepare table for analysis\n\nC46b = C46b.pivot(index=['Account Number','Organization','Row Number','Row Name'], columns='Column Name', values='Response Answer').reset_index()\nC46b = pd.merge(C46b,pop, left_on='Account Number',right_on='Account Number',how='left')\nC46b['Current population'] = C46b['Current population'].astype(float)\n\n### KPI: Metric tonnes of CO2 emissions per capite. Note: is possible todo this by emissions type\n\nC46b= C46b.dropna(subset=['Current population'])\nC46b= C46b.dropna(subset=['Emissions (metric tonnes CO2e)'])\nC46b=C46b[C46b['Emissions (metric tonnes CO2e)']!='Question not applicable']\nC46b['Emissions (metric tonnes CO2e)'] = C46b['Emissions (metric tonnes CO2e)'].astype(float)\nC46b['CO2 Emissions per capite'] = C46b['Emissions (metric tonnes CO2e)']/C46b['Current population']\n\n## KPI: Metric tonnes of CO2 emissions per capite - TOTAL BASIC emissions Rank order - the higher the rank, the higher the emissions \n\nC46b_KPI = C46b[C46b['Row Name'] == 'TOTAL BASIC emissions']\nC46b_KPI['KPI_46b_CO2 Emissions per capite_rank'] = C46b_KPI['CO2 Emissions per capite'].rank(pct=True)\n\n####################################################### TABLE 4.13 - CITY WIDE EMISSIONS ########################################################################\n#Please provide details on any historical and base year city-wide emissions inventories your city has, in order to allow assessment of targets in the table below.\n\n# Import table\n\nC413 = c20[c20['Question Number']=='4.13']\nC413 = C413.drop(['Questionnaire', 'Year Reported to CDP','Parent Section','Section','Comments','File Name','Row Name','Last update','Column Number', 'Question Name',\n               'Question Number','CDP Region', 'Country'], axis=1)\n\n# Prepare table for analysis\n\ndef convert(list): \n    return tuple(list) \nC413 =C413.groupby(['Account Number', 'Organization','Column Name', 'Row Number'])['Response Answer'].apply(list).reset_index()\nC413['Response Answer'] = tuple(list(C413['Response Answer']))\nC413 ['Response Answer'] = C413['Response Answer'].apply(convert)\nC413 = C413.pivot(index=['Account Number','Organization','Row Number'], columns='Column Name', values='Response Answer').reset_index()\ndef convertTuple(tup): \n    str = functools.reduce(operator.add, (tup)) \n    return str\nC413 = C413.drop(['Comments','File name and attach your inventory','Is this inventory used as the base year inventory?','Scopes / boundary covered'],axis=1)\ncollist = C413.columns.values.tolist()[3:]\nC413[collist] = C413[collist].apply(convertTuple)\n\n# KPI: Average trend of emissions reduction/increase percentage from time series\n\nC413_KPI = C413.drop('Inventory date from',axis=1)\nC413_KPI['Inventory date to'] = pd.to_datetime(C413_KPI['Inventory date to'])\nC413_KPI['Inventory date to'] = C413_KPI['Inventory date to'].dt.year\nC413_KPI = C413_KPI.sort_values(['Organization','Inventory date to','Row Number']).reset_index()\nC413_KPI = C413_KPI[C413_KPI['Methodology']== 'Global Protocol for Community Greenhouse Gas Emissions Inventories (GPC)']\nC413_KPI['Previous emissions (metric tonnes CO2e)'] = C413_KPI['Previous emissions (metric tonnes CO2e)'].astype(float)\nC413_KPI['diffs'] = C413_KPI['Previous emissions (metric tonnes CO2e)'].pct_change()\nmask = C413_KPI.Organization != C413_KPI.Organization.shift(1)\nC413_KPI['diffs'][mask] = np.nan\nC413_KPI['y_diffs'] = C413_KPI['Inventory date to'].diff()\nC413_KPI['y_diffs'][mask] = np.nan\nC413_KPI=C413_KPI.dropna(subset=['diffs'])\nC413_KPI=C413_KPI[C413_KPI['y_diffs']==1]\nC413_KPI = C413_KPI.groupby(['Account Number', 'Organization'])['diffs'].mean().reset_index()\n\n### KPI: Ranking of average trend of city emissions reduction/increase percentage from time series - The lowest the rank the better, it means the \n### city is cutting more emisions\n\nC413_KPI['KPI_413_diffs_rank'] = C413_KPI['diffs'].rank(pct=True)\n\n###################################################### TABLE 5.0a - EMISSIONS REDUCTION ######################################################################\n\n# Please provide details of your total city-wide base year emissions reduction (absolute) target(s). \n# In addition, you may add rows to provide details of your sector-specific targets, by providing the base year emissions specific to that target.\n\n# Import table \n\nC50a = c20[c20['Question Number']=='5.0a']\nC50a = C50a.drop(['Questionnaire', 'Year Reported to CDP','Parent Section','Section','Comments','File Name','Row Name','Last update','Column Number', 'Question Name',\n               'Question Number','CDP Region', 'Country'], axis=1)\n\n# Prepare table for analysis \n\ndef convert(list): \n    return tuple(list) \nC50a =C50a.groupby(['Account Number', 'Organization','Column Name', 'Row Number'])['Response Answer'].apply(list).reset_index()\nC50a['Response Answer'] = tuple(list(C50a['Response Answer']))\nC50a['Response Answer'] = C50a['Response Answer'].apply(convert)\nC50a = C50a.pivot(index=['Account Number','Organization','Row Number'], columns='Column Name', values='Response Answer').reset_index()\nC50a = C50a.drop(['Boundary of target relative to city boundary (reported in 0.1)','Does this target align to a requirement from a higher level of sub-national government',\n                 'Does this target align with the global 1.5 - 2 °C pathway set out in the Paris Agreement?','Select the initiatives that this target contributes towards',\n                 'Target meets initial GCoM validation criteria','Where sources differ from the inventory, identify and explain these additions / exclusions',\n                 'Please describe your target. If your country has an NDC and your city’s target is less ambitious than the NDC, please explain why.'],axis=1)\ndef convertTuple(tup): \n    str = functools.reduce(operator.add, (tup)) \n    return str\ncollist = C50a.columns.values.tolist()[3:]\nC50a[collist] = C50a[collist].apply(convertTuple)\nC50a = C50a[C50a['Base year']!='Question not applicable']\nC50a = C50a.dropna()\nC50a['Base year'] = C50a['Base year'].astype(float)\nC50a['Base year emissions (metric tonnes CO2e)'] = C50a['Base year emissions (metric tonnes CO2e)'].astype(float)\nC50a['Percentage of target achieved so far'] = C50a['Percentage of target achieved so far'].astype(float)\nC50a['Percentage reduction target'] = C50a['Percentage reduction target'].astype(float)\nC50a['Target year absolute emissions (metric tonnes CO2e) [Auto-calculated]'] = C50a['Target year absolute emissions (metric tonnes CO2e) [Auto-calculated]'].astype(float)\nC50a['Target year'] = C50a['Target year'].astype(float)\nC50a['Year of target introduction'] = C50a['Year of target introduction'].astype(float)\n\n# Calculate new cols - to evaluate ambition & progress of objectives \n\nC50a['target_years']=C50a['Target year'] - C50a['Year of target introduction']+1\nC50a['Emissions_reduction_obj']= C50a['Target year absolute emissions (metric tonnes CO2e) [Auto-calculated]']\nC50a['Emissions_reduction_achieved']= (C50a['Percentage of target achieved so far']/100)*C50a['Base year emissions (metric tonnes CO2e)'] ###I THINK is WRONG!\nC50a['Percentage_reduction_per_year']= (C50a['Percentage reduction target']/100)/C50a['target_years']\nC50a['years_left']= C50a['Target year']-2020\nC50a['achiev_per_year'] = (C50a['Percentage reduction target'])*(C50a['Percentage of target achieved so far']/100)/(2020- C50a['Year of target introduction']+1)/100\nC50a['effective_years_to_achiev'] = C50a['Emissions_reduction_obj']/ (C50a['Base year emissions (metric tonnes CO2e)']*(C50a['Percentage reduction target']/100))\nC50a['Emissions_reduction per year'] = C50a['Emissions_reduction_obj']/C50a['target_years']\nC50a['Actual_emissions_cut_per_year'] = (C50a['Emissions_reduction_obj']*(C50a['Percentage of target achieved so far']/100))/(2020- C50a['Year of target introduction']+1)\nC50a['Actual_years_to_achiev'] = C50a['Emissions_reduction_obj']/C50a['Actual_emissions_cut_per_year']\nC50a['Years_diff'] = C50a['target_years']-C50a['Actual_years_to_achiev']\nC50a['Years_diff'].replace(np.inf, np.nan, inplace=True)\nC50a['Years_diff'].replace(np.NINF, np.nan, inplace=True)\nC50a.loc[(C50a['Percentage of target achieved so far']<0),'Years_diff'] = np.nan \nC50a['KPI_Strategy']=np.nan \n\nC50a.loc[(C50a['Years_diff']>C50a['Years_diff'].std()) | (C50a['Years_diff']<-C50a['Years_diff'].std()), 'Years_diff'] = np.nan\nC50a['Years_diff'].replace(np.inf, 0, inplace=True)\nC50a['Years_diff'].replace(np.NINF, 0, inplace=True)\n\nC50a_KPI=C50a[['Account Number','Organization','Row Number', 'Base year emissions (metric tonnes CO2e)', 'Emissions_reduction_achieved',\n               'Emissions_reduction_obj','Years_diff']]\n\n# Group by city by weighted average per Emissions reduction objective\n\nwm = lambda x: np.average(x, weights=C50a_KPI.loc[x.index, 'Emissions_reduction_obj'])\nC50a_KPI\nC50a_KPI['Years_diff']=C50a_KPI['Years_diff']+0.00000000001\nC50a_KPI['Emissions_reduction_obj']=C50a_KPI['Emissions_reduction_obj']+0.00000000001\nC50a_KPI=C50a_KPI.groupby(['Account Number','Organization']).agg(Emissions_reduction_achieved=('Emissions_reduction_achieved', 'sum'),\n    Covered_emissions= ('Base year emissions (metric tonnes CO2e)', 'sum'),\n    Emissions_reduction_obj=('Emissions_reduction_obj', 'sum'),\n    Years_diff=('Years_diff', wm)).dropna().reset_index()\n\n### KPIs: Objective strategy, Objective ambition, Objective progress - RANK\n\nC50a_KPI['Percentage_obj_total'] = C50a_KPI.Emissions_reduction_obj / C50a_KPI.Covered_emissions\nC50a_KPI['Percentage_obj_achieved'] = C50a_KPI.Emissions_reduction_achieved / C50a_KPI.Covered_emissions\n\nC50a_KPI['KPI_50a_rank_objective_strategy'] = C50a_KPI['Years_diff'].rank(pct=True)\nC50a_KPI['KPI_50a_rank_objective_ambition'] = C50a_KPI['Percentage_obj_total'].rank(pct=True)\nC50a_KPI['KPI_50a_rank_objective_progress'] = C50a_KPI['Percentage_obj_achieved'].rank(pct=True)\n\n##################################################### TABLE 5.4 - EMISSIONS REDUCTION ################################################################################# \n#Describe the anticipated outcomes of the most impactful mitigation actions your city is currently undertaking; the total cost of the action and how much is being \n#funded by the local government.\n\n# Import table\n\nC54 = c20[c20['Question Number']=='5.4']\nC54 = C54.drop(['Questionnaire', 'Year Reported to CDP','Parent Section','Section','Comments','File Name','Row Name','Last update','Column Number', 'Question Name',\n               'Question Number','CDP Region', 'Country'], axis=1)\n\n# Prepare table for analysis\n\ndef convert(list): \n    return tuple(list) \nC54 =C54.groupby(['Account Number', 'Organization','Column Name', 'Row Number'])['Response Answer'].apply(list).reset_index()\nC54['Response Answer'] = tuple(list(C54['Response Answer']))\nC54['Response Answer'] = C54['Response Answer'].apply(convert)\nC54 = C54.pivot(index=['Account Number','Organization','Row Number'], columns='Column Name', values='Response Answer').reset_index()\nC54 = C54.drop(['Web link to action website','Scope and impact of action','Role in the GCC program','Name of the stakeholder group','Name of the engagement activities',\n               'Co-benefit area','Aim of the engagement activities','Attach reference document','Means of implementation'],axis=1)\ndef convertTuple(tup): \n    str = functools.reduce(operator.add, (tup)) \n    return str\ncollist = C54.columns.values.tolist()[3:]\nC54[collist] = C54[collist].apply(convertTuple)\nC54=C54.dropna(subset=['Energy savings (MWh)', 'Estimated emissions reduction (metric tonnes CO2e)','Renewable energy production (MWh)'], how='all')\nC54['Energy savings (MWh)'] = C54['Energy savings (MWh)'].astype(float)\nC54['Estimated emissions reduction (metric tonnes CO2e)'] = C54['Estimated emissions reduction (metric tonnes CO2e)'].astype(float)\nC54['Renewable energy production (MWh)'] = C54['Renewable energy production (MWh)'].astype(float)\nC54['Total cost of the project'] = C54['Total cost of the project'].astype(float)\n\n# Import currency information to convert cost of project in USD\n\nC54 =pd.merge(C54,curr, left_on='Account Number',right_on='Account Number',how='left')\nC54['Total dollar cost'] = C54['Total cost of the project']*C54['Exchange Rate']\n\n## CLean up final data\nC54 = C54.dropna(subset=['Total dollar cost'])\nC54 = C54[C54['Timescale of reduction / savings / energy production'] == 'Per year']\nC54 = C54[C54['Total dollar cost']!=0]\n\n\n### KPI: Energy savings in MWh per dollar spent\n### KPI: CO2 emissions reduction per dollar spent \n### KPI: Mwh of renewable energy per MWh spent\n\nC54_KPI = C54.groupby(['Account Number', 'Organization'], as_index=False).agg({\n    'Total dollar cost': 'sum',\n    'Energy savings (MWh)': 'sum',\n    'Estimated emissions reduction (metric tonnes CO2e)': 'sum',\n    'Renewable energy production (MWh)': 'sum',\n    'Total cost of the project': 'sum'\n    })\n\nC54_KPI['Dollar per 1 MWh of Energy Savings'] = C54_KPI['Total dollar cost']/ C54_KPI['Energy savings (MWh)']\nC54_KPI['Dollar per 1 Mt of CO2 Emissions reductions'] = C54_KPI['Total dollar cost']/ C54_KPI['Estimated emissions reduction (metric tonnes CO2e)']\nC54_KPI['Dollar per 1 MWh of Renewable energy production'] = C54_KPI['Total dollar cost']/ C54_KPI['Renewable energy production (MWh)']\nC54_KPI.replace(np.inf, np.nan, inplace=True)\nC54_KPI.replace(np.NINF, np.nan, inplace=True)\nC54_KPI=C54_KPI.dropna(subset=['Dollar per 1 MWh of Energy Savings','Dollar per 1 Mt of CO2 Emissions reductions',\n                               'Dollar per 1 MWh of Renewable energy production'], how='all')\n\n### KPI: Ranking for Dollar for each Mwh Energy saving, CO2 emissions cuts and Mwh of Reneable energy - The lower the better, more savings for less $\n# Done on the Per year metric for comparability\n# C54_KPI = C54_KPI[C54_KPI['Timescale of reduction / savings / energy production'] == 'Per year']\n# C54_KPI = C54_KPI[C54_KPI['Total dollar cost'] != 0]\n\n# High chance that cities have used different scales e.g. they write 100 but they mean 100k for 100000. Try correct for that\n\nC54_KPI = C54_KPI[C54_KPI['Dollar per 1 MWh of Energy Savings']<10000]\nC54_KPI = C54_KPI[C54_KPI['Dollar per 1 Mt of CO2 Emissions reductions']<25000]\nC54_KPI = C54_KPI[C54_KPI['Energy savings (MWh)']>1000]\nC54_KPI = C54_KPI[C54_KPI['Estimated emissions reduction (metric tonnes CO2e)']>10000]\nC54_KPI = C54_KPI[C54_KPI['Total cost of the project']>10000]\n\nC54_KPI['KPI_54_$_per_MWh_saving'] = C54_KPI['Dollar per 1 MWh of Energy Savings'].rank(pct=True)\nC54_KPI['KPI_54_$_per_CO2_cut'] = C54_KPI['Dollar per 1 Mt of CO2 Emissions reductions'].rank(pct=True)\nC54_KPI['KPI_54_$_per_MWh_production'] = C54_KPI['Dollar per 1 MWh of Renewable energy production'].rank(pct=True)\n\n################################################################ TABLE 8.0a - ENERGY ########################################################################### \n#Please provide details of your renewable energy or electricity target(s) and how the city plans to meet those targets.\n\n# Import table\n\nC80a = c20[c20['Question Number']=='8.0a']\nC80a = C80a.drop(['Questionnaire', 'Year Reported to CDP','Parent Section','Section','Comments','File Name','Row Name','Last update','Column Number', 'Question Name',\n               'Question Number','CDP Region', 'Country'], axis=1)\n\n# Prepare table for analysis \n\nC80a = C80a.pivot(index=['Account Number','Organization','Row Number'], columns='Column Name', values='Response Answer').reset_index()\nC80a = C80a.drop(['Please specify plans to meet the target(s) and in which sector this target will be implemented (i.e. All energy sectors, electricity, heating and cooling and/or transport)'],\n                 axis=1)\nC80a = C80a[C80a['Base year']!='Question not applicable']\n\n## Clean up the table\nC80a['Base year'] = C80a['Base year'].astype(float)\nC80a['Target year'] = C80a['Target year'].astype(float)\nC80a['Percentage of target achieved'] = C80a['Percentage of target achieved'].astype(float)\nC80a['Total renewable energy / electricity covered by target in base year (in unit specified in column 3:  energy/electricity types covered by target)'] = C80a['Total renewable energy / electricity covered by target in base year (in unit specified in column 3:  energy/electricity types covered by target)'].astype(float)\nC80a['Total renewable energy / electricity covered by target in target year (in unit specified in column 3: energy/electricity types covered by target)'] = C80a['Total renewable energy / electricity covered by target in target year (in unit specified in column 3: energy/electricity types covered by target)'].astype(float)\nC80a['Percentage renewable energy / electricity of total energy or electricity in base year'] = C80a['Percentage renewable energy / electricity of total energy or electricity in base year'].astype(float)\nC80a['Percentage renewable energy / electricity of total energy or electricity in target year'] = C80a['Percentage renewable energy / electricity of total energy or electricity in target year'].astype(float)\n\n## Create new calculated features\nC80a['target_years']=C80a['Target year'] - C80a['Base year']+1\nC80a['Percentage_E_growth_per_year']= ((C80a['Percentage renewable energy / electricity of total energy or electricity in target year']-C80a['Percentage renewable energy / electricity of total energy or electricity in base year'])/100)/C80a['target_years']\nC80a['years_past']=2020-C80a['Base year']+1\nC80a['Green_E_achiev_per_year']=(C80a['Percentage of target achieved']/100)/C80a['years_past']\nC80a=C80a.dropna(subset=['Green_E_achiev_per_year','Percentage_E_growth_per_year'])\nC80a = C80a[C80a['Percentage_E_growth_per_year']>=0]\n\n## KPI: Target status tracker: On target or behind target?\nC80a['Target_status'] = 'Behind target'\nC80a.loc[C80a['Percentage_E_growth_per_year']<=C80a['Green_E_achiev_per_year'], 'Target_status'] = 'On target'\n\n\n\n### KPI: Target percentage growth of reneable per year VS Actual growth against the target\n# wm_ = lambda x: np.average(x, weights=C41a_KPI.loc[x.index, 'Emissions_reduction_obj'])\nC80a_KPI = C80a.groupby(['Account Number', 'Organization'], as_index=False).agg({\n    'target_years': 'mean',\n    'Percentage_E_growth_per_year': 'mean',\n    'Green_E_achiev_per_year': 'mean',\n    })\n\n\n### KPI: Rank order of distance from target - the higher the number the worst it is as further from target\n\nC80a_KPI['Distance from target'] = C80a_KPI['Percentage_E_growth_per_year'] - C80a_KPI['Green_E_achiev_per_year']\nC80a_KPI['KPI_80a_rank_dist_from_target'] = C80a_KPI['Distance from target'].rank(pct=True)\n\n############################################################# TABLE 8.1 - ENERGY ############################################################################ \n#Please indicate the source mix of electricity consumed in your city\n\n# Import table\n\nC81 = c20[c20['Question Number']=='8.1']\nC81 = C81.drop(['Questionnaire', 'Year Reported to CDP','Parent Section','Section','Comments','File Name','Row Name','Last update','Column Number', 'Question Name',\n               'Question Number','CDP Region', 'Country'], axis=1)\n\n# Prepare data for analysis\n\nC81 = C81.pivot(index=['Account Number','Organization','Row Number'], columns='Column Name', values='Response Answer').reset_index()\nC81=C81.dropna(subset=['Total - please ensure this equals 100%'])\n\n### KPI: Rank ordering Greenest cities - higher rank mean cleaner energy used\n\nC81['Geothermal'] = C81['Geothermal'].astype(float)\nC81['Solar'] = C81['Solar'].astype(float)\nC81['Wind'] = C81['Wind'].astype(float)\nC81['Green_energy%'] = C81['Geothermal'] + C81['Wind'] + C81['Solar']\nC81['KPI_81_Green_energy_rank'] = C81['Green_energy%'].rank(pct=True)\n\n########################################################################### TABLE 8.5 - ENERGY ############################################################### \n#Does your city have a target to increase energy efficiency?\n\n# Import table\nC85a = c20[c20['Question Number']=='8.5a']\nC85a = C85a.drop(['Questionnaire', 'Year Reported to CDP','Parent Section','Section','Comments','File Name','Row Name','Last update','Column Number', 'Question Name',\n               'Question Number','CDP Region', 'Country'], axis=1)\n\n# Prepare data for analysis\n\ndef convert(list): \n    return tuple(list) \nC85a =C85a.groupby(['Account Number', 'Organization','Column Name', 'Row Number'])['Response Answer'].apply(list).reset_index()\nC85a['Response Answer'] = tuple(list(C85a['Response Answer']))\nC85a['Response Answer'] = C85a['Response Answer'].apply(convert)\nC85a = C85a.pivot(index=['Account Number','Organization','Row Number'], columns='Column Name', values='Response Answer').reset_index()\nC85a = C85a.drop(['Plans to meet target (include details on types of energy in thermal /electricity)','Please indicate to which energy sector(s) the target applies (Multiple choice)']\n                 ,axis=1)\ndef convertTuple(tup): \n    str = functools.reduce(operator.add, (tup)) \n    return str\ncollist = C85a.columns.values.tolist()[3:]\nC85a[collist] = C85a[collist].apply(convertTuple)\nC85a = C85a[C85a['Base year']!='Question not applicable']\n\n\n## Clean up columns\nC85a['Base year'] = C85a['Base year'].astype(float)\nC85a['Target year'] = C85a['Target year'].astype(float)\nC85a['Percentage of energy efficiency improvement in target year compared to base year levels'] = C85a['Percentage of energy efficiency improvement in target year compared to base year levels'].astype(float)\nC85a['Percentage of target achieved'] = C85a['Percentage of target achieved'].astype(float)\n\n\n## Calculate new features\nC85a['target_years']=C85a['Target year'] - C85a['Base year']+1\nC85a['Percentage_E_growth_per_year']= (C85a['Percentage of energy efficiency improvement in target year compared to base year levels']/100)/C85a['target_years']\nC85a['years_past']=2020-C85a['Base year']+1\nC85a['Green_E_achiev_per_year']=(C85a['Percentage of target achieved']/100)/C85a['years_past']\nC85a=C85a.dropna(subset=['Green_E_achiev_per_year','Percentage_E_growth_per_year'])\nC85a = C85a[C85a['Percentage_E_growth_per_year']>=0]\n\n## KPI: Target status tracker: On target or behind target?\nC85a['Target_status'] = 'Behind target'\nC85a.loc[C85a['Percentage_E_growth_per_year']<=C85a['Green_E_achiev_per_year'], 'Target_status'] = 'On target'\n\n\n\n### KPI: Target percentage growth of energy efficiency per year VS Actual growth against the target\n# wm_ = lambda x: np.average(x, weights=C41a_KPI.loc[x.index, 'Emissions_reduction_obj'])\nC85a_KPI = C85a.groupby(['Account Number', 'Organization'], as_index=False).agg({\n    'Percentage_E_growth_per_year': 'mean',\n    'Green_E_achiev_per_year': 'mean',\n    })\n\n\n\n### KPI: Rank order of distance from target - the higher the number the worst it is as further from target\n\nC85a_KPI['Distance from target'] = C85a_KPI['Percentage_E_growth_per_year'] - C85a_KPI['Green_E_achiev_per_year']\nC85a_KPI['KPI_85a_rank_dist_from_target_eff'] = C85a_KPI['Distance from target'].rank(pct=True)\n\n################################################################# TABLE 10.1 TRANSPORT ##################################################################### \n#What is the mode share of each transport mode in your city for passenger transport?\n\n# Import table\n\nC101 = c20[c20['Question Number']=='10.1']\nC101 = C101.drop(['Questionnaire', 'Year Reported to CDP','Parent Section','Section','Comments','File Name','Row Name','Last update','Column Number', 'Question Name',\n               'Question Number','CDP Region', 'Country'], axis=1)\n\n# Prepare table for analysis\n\nC101 = C101.pivot(index=['Account Number','Organization','Row Number'], columns='Column Name', values='Response Answer').reset_index()\nC101 = C101[C101['Cycling']!='Question not applicable']\nC101['Micro-Mobility'] = C101['Micro-Mobility'].astype(float)\nC101['Cycling'] = C101['Cycling'].astype(float)\nC101['Walking'] = C101['Walking'].astype(float)\n\n### KPI: Rank ordering greenest cities - the higher the greener  \n\nC101['Green_transport'] = C101['Micro-Mobility'] + C101['Cycling'] + C101['Walking']\nC101['KPI_101_Green_transport_rank'] = C101['Green_transport'].rank(pct=True)\n\n#################################################################### TABLE 10.4 TRANSPORT ############################################################\n#Please provide the total fleet size and number of vehicle types for the following modes of transport.\n\n# Import table\n\nC104 = c20[c20['Question Number']=='10.4']\nC104 = C104.drop(['Questionnaire', 'Year Reported to CDP','Parent Section','Section','Comments','File Name','Question Name','Last update','Column Number',\n               'Question Number','CDP Region', 'Country'], axis=1)\n\n# Prepare table for analysis \n\nC104 = C104.pivot(index=['Account Number','Organization','Row Number','Row Name'], columns='Column Name', values='Response Answer').reset_index()\n\n# Merge with population per city\n\nC104 =pd.merge(C104,pop, left_on='Account Number',right_on='Account Number',how='left')\nC104_KPI = C104[C104['Row Name']== 'Total fleet size']\nC104_KPI['Number of private cars'] = C104_KPI['Number of private cars'].astype(float)\nC104_KPI['Current population'] = C104_KPI['Current population'].astype(float)\nC104_KPI['Number of buses'] = C104_KPI['Number of buses'].astype(float)\nC104_KPI['Number of municipal fleet (excluding buses)'] = C104_KPI['Number of municipal fleet (excluding buses)'].astype(float)\n\n### KPI: Public transport per capite/ private cars per capite\n\nC104_KPI['Municipal fleet'] = C104_KPI['Number of buses'] + C104_KPI['Number of municipal fleet (excluding buses)']\nC104_KPI['Municipal fleet per capite'] = C104_KPI['Municipal fleet'] / C104_KPI['Current population']\nC104_KPI['Private cars per capite'] = C104_KPI['Number of private cars'] / C104_KPI['Current population']\n\n# Remove some weird outliers\n\nC104_KPI=C104_KPI[C104_KPI['Private cars per capite']<2]\nC104_KPI=C104_KPI[C104_KPI['Municipal fleet per capite']<2]\n\n### KPI: Rank of public transport per capite (higher the better) and ranking of cars per capite (higher the worse)\n\nC104_KPI['KPI_104_public_transport_rank'] = C104_KPI['Municipal fleet per capite'].rank(pct=True)\nC104_KPI['KPI_104_private_car_rank'] = C104_KPI['Private cars per capite'].rank(pct=True)\n\n################################################################################# TABLE 10.7 TRANSPORT ########################################################\n#Do you have a low or zero-emission zone in your city? (i.e. an area that disincentivises fossil fuel vehicles through a charge, a ban or access restriction)\n\n# Import table\n\nC107a = c20[c20['Question Number']=='10.7a']\nC107a = C107a.drop(['Questionnaire', 'Year Reported to CDP','Parent Section','Section','Comments','File Name','Question Name','Last update','Column Number','Row Name',\n               'Question Number','CDP Region', 'Country'], axis=1)\nC107a = C107a.pivot(index=['Account Number','Organization','Row Number'], columns='Column Name', values='Response Answer').reset_index()\n\n# Import land area \n\nC06 = c20[c20['Question Number']=='0.6']\nC06 = C06.drop(['Questionnaire', 'Year Reported to CDP','Parent Section','Section','Comments','File Name','Question Name','Last update','Column Number','Row Name',\n               'Question Number','CDP Region', 'Country'], axis=1)\nC06 = C06.pivot(index=['Account Number','Organization','Row Number'], columns='Column Name', values='Response Answer').reset_index()\nC06['Land area of the city boundary as defined in question 0.1 (in square km)'] = C06['Land area of the city boundary as defined in question 0.1 (in square km)'].astype(float)\n\n### KPI: Percentage of zero emission area with respect to city size\n\nC107a = C107a[C107a['Size (sq. km)']!='Question not applicable']\nC107a = C107a.dropna(subset=['Size (sq. km)'])\nC107a['Size (sq. km)'] = C107a['Size (sq. km)'].astype(float)\nC107a =pd.merge(C107a,C06, left_on='Account Number',right_on='Account Number',how='left')\nC107a['Percentage of 0 emission'] = C107a['Size (sq. km)'] / C107a['Land area of the city boundary as defined in question 0.1 (in square km)']\n\n# Remove weird outliers\n\nC107a = C107a[C107a['Percentage of 0 emission'] <= 1]\n\n### KPI: Ranking of percentage of zero emission area - the highest the better\n\nC107a['KPI_107a_zero_emissions'] = C107a['Percentage of 0 emission'].rank(pct=True)\n\n############################################################################ TABLE 12.1 - FOOD ##################################################################\n#What is the per capita meat and dairy consumption (kg) in your city?\n\n# Import table\n\nC121 = c20[c20['Question Number']=='12.1']\nC121 = C121.drop(['Questionnaire', 'Year Reported to CDP','Parent Section','Section','Comments','File Name','Question Name','Last update','Column Number',\n               'Question Number','CDP Region', 'Country'], axis=1)\n\n#Prepare table for analysis\n\nC121 = C121.pivot(index=['Account Number','Organization','Row Number','Row Name'], columns='Column Name', values='Response Answer').reset_index()\nC121 = C121[C121['Amount']!='Question not applicable']\nC121 = C121.dropna(subset=['Amount'])\nC121['Amount']= C121['Amount'].astype(float)\nC121_meat = C121[C121['Row Name']=='Meat consumption per capita (kg/year)']\nC121_meat['Meat_rank'] = C121_meat['Amount'].rank(pct=True)\nC121_dairy = C121[C121['Row Name']=='Dairy consumption per capita (kg/year)']\nC121_dairy['dairy_rank'] = C121_dairy['Amount'].rank(pct=True)\nC121_dairy = C121_dairy[['Account Number','dairy_rank']]\nC121_md =pd.merge(C121_meat,C121_dairy, left_on='Account Number',right_on='Account Number',how='left')\nC121_md['M/D'] = C121_md['dairy_rank'] + C121_md['Meat_rank']\n\n### KPI:Meat/Dairy consumption rank, the lower the better\n\nC121_md['KPI_121_Meat/Dairy Rank'] = C121_md['M/D'].rank(pct=True)\n\n############################################################################# TABLE 13.0 - WASTE ####################################################\n#What is the annual solid waste generation in your city?\n\n# Import table\n\nC130 = c20[c20['Question Number']=='13.0']\nC130 = C130.drop(['Questionnaire', 'Year Reported to CDP','Parent Section','Section','Comments','File Name','Question Name','Last update','Column Number',\n               'Question Number','CDP Region', 'Country'], axis=1)\n\n# Prepare table for analysis\n\nC130 = C130.pivot(index=['Account Number','Organization','Row Number','Row Name'], columns='Column Name', values='Response Answer').reset_index()\nC130['Amount of waste generated (tonnes/year)']= C130['Amount of waste generated (tonnes/year)'].astype(float)\n\n# Import population size data\n\nC130 =pd.merge(C130,pop, left_on='Account Number',right_on='Account Number',how='left')\nC130['Current population']= C130['Current population'].astype(float)\n\n\n### KPI: Tonnes of waste produced per capite\n\nC130['Waste tonnes per capite'] = C130['Amount of waste generated (tonnes/year)']/C130['Current population']\n\n#Remove weird outliers \n\nC130 = C130[C130['Waste tonnes per capite']<4]\n\n### KPI: ranking of tonnes of waste produced per capite - the smaller the better\n\nC130['KPI_130_waste_rank'] = C130['Waste tonnes per capite'].rank(pct=True)\n\n########################################################### CREATE SINGLE KPI RANKING TABLE - CITY LEVEL ########################################################\n\nT21 = C21_city[['Account Number', 'KPI_21_risk_index_curr_rank', 'KPI_21_risk_index_fut_rank']]\nT30 = C30_KPI[['Account Number','KPI_30_spend_per_capite_rank']]\nT46b = C46b_KPI[['Account Number','KPI_46b_CO2 Emissions per capite_rank']]\nT413 = C413_KPI[['Account Number','KPI_413_diffs_rank']]\nT50a = C50a_KPI[['Account Number','KPI_50a_rank_objective_strategy','KPI_50a_rank_objective_ambition','KPI_50a_rank_objective_progress']] \nT54 = C54_KPI[['Account Number','KPI_54_$_per_MWh_saving','KPI_54_$_per_CO2_cut','KPI_54_$_per_MWh_production']] \nT80a = C80a_KPI[['Account Number','KPI_80a_rank_dist_from_target']]\nT81 = C81[['Account Number','KPI_81_Green_energy_rank']]\nT85a = C85a_KPI[['Account Number','KPI_85a_rank_dist_from_target_eff']]\nT101 = C101[['Account Number','KPI_101_Green_transport_rank']]\nT104 = C104_KPI[['Account Number','KPI_104_public_transport_rank','KPI_104_private_car_rank']]\nT107a = C107a[['Account Number','KPI_107a_zero_emissions']]\nT121 = C121_md[['Account Number','KPI_121_Meat/Dairy Rank']]\nT130 = C130[['Account Number','KPI_130_waste_rank']]\n\n\n## Merge everything\n# Create list of df\ncities_df_list = [\n    T21,\n    T30,\n    T46b,\n    T413,\n    T50a,\n    T54,\n    T80a,\n    T81,\n    T85a,\n    T101,\n    T104,\n    T107a,\n    T121,\n    T130]\n\n# Create list of suffixes\ncities_df_suffixes_list = [\n    '_climate_hazard',\n    '_adaptation',\n    '_city_wide_emissions',\n    '_city_wide_emissions_historical',\n    '_emissions_reduction_targets',\n    '_emissions_reduction_outcomes',\n    '_energy_targets',\n    '_energy_consumption',\n    '_energy_efficiency_targets',\n    '_transport',\n    '_transport_fleet_size',\n    '_transport_low_zero_emission_areas',\n    '_food',\n    '_waste']\n\n# Prime the final table\ncities_KPIs_2020 = disc[['Account Number', 'Organization']]\n\n# Merge everything\nix = 0\ncount_cols = 0\nfor df_ in cities_df_list:\n    cities_KPIs_2020 = pd.merge(cities_KPIs_2020, df_.copy().add_suffix(cities_df_suffixes_list[ix]), \n                                 how='outer', \n                                 left_on='Account Number', \n                                 right_on='Account Number'+cities_df_suffixes_list[ix], \n                                ) \n\n    ix += 1\n    count_cols += len(df_.columns)\n\n    \n## Save into CSV\ncities_KPIs_2020.to_csv('./df_KPIs/Final/cities_KPIs_2020.csv')\n\n\n## Final checks\n#print(count_cols) # 34+2 = 36\n#print(len(cities_KPIs_2020.columns)) # 36\n\n################################################################ Add region and Country to Cities KPIs df #######################################################\n\nuq_cities = c20[['Account Number', 'Organization', 'CDP Region', 'Country', 'Row Number']].groupby(['Account Number', 'Organization', 'CDP Region', 'Country'], as_index=False).count() #value_counts()\n\ncities_KPIs_2020 = pd.merge(cities_KPIs_2020, \n                        uq_cities.copy().add_suffix('_'), \n                        how='left', \n                        left_on='Account Number',\n                        right_on='Account Number_'\n                        )\n\n\ncities_KPIs_2020 = cities_KPIs_2020.drop(['Organization_', 'Account Number_', 'Row Number_'], axis=1)\n\n\n# uq_cities.head()\n#cities_KPIs_2020.head() Organization\n\n################################################################### Link Companies to cities ######################################################\n\n## Add Cities to corporations' df\ncorp_locations = pd.read_csv('../input/cdp-unlocking-climate-solutions/Supplementary Data/Locations of Corporations/NA_HQ_public_data.csv', low_memory=False)\ncorp_locations_cleaned= corp_locations.groupby(['account_number', 'hq_country','address_city'], as_index=False)['public'].count()\ncompany_climate_KPIs_2020 = pd.merge(company_climate_KPIs_2020, \n                                    corp_locations_cleaned.copy().add_suffix('_'), \n                                    how='left', \n                                    left_on='account_number',\n                                    right_on='account_number_'\n                                    )\n\ncompany_water_KPIs_2020 = pd.merge(company_water_KPIs_2020, \n                                    corp_locations_cleaned.copy().add_suffix('_'), \n                                    how='left', \n                                    left_on='account_number',\n                                    right_on='account_number_'\n                                    )\n\n\nclimate_city_KPIs_2020 = pd.merge(company_water_KPIs_2020, \n                                   corp_locations_cleaned.copy().add_suffix('_'), \n                                   how='left', \n                                    left_on='account_number',\n                                    right_on='account_number_'\n                                    )\n\n\n## Merge \n\n\n# print(len(corp_locations.index))\n# corp_locations.head(4)\n# company_climate_KPIs_2020['theme'].value_counts()\n# company_water_KPIs_2020['theme'].value_counts()\n\n\n\n\n\n\n\n\n## Visualize sample table\n# pd.set_option('display.max_columns', None)\n# C41a_KPI\nC41a[C41a['organization'] == 'Celestica Inc.']\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**Objective strategy**\n\nWe start presenting a KPI that tracks the \"objective strategy\" and feasibility set by a company/city. The metric that is proposed is the \"Years to achievement\", which is built by comparing directly what % of CO2 emissions a company will need to achieve to reach its goal VS how much it has already been able to cut so far against the same goal. \n\nIt is interesting to observe that whilst the distribution peaks at a zero value, many large negative and positive values are present for this metric. How to interpret this KPI:\n* A value close to zero indicates that the objective is well calibrated, meaning that if the company/city keeps reducing emissions in the way it has done so far, it will achieve its goal exactly in the indicated time span\n* A negative value for the KPI indicates that the company/city has not been able to keep on track with its objective and reaching the objective is starting to look unfeasible, unless there is a change in pace, the further away from zero, the less likely the company/city will be to achieve its goal\n* A positive value for the KPI means that the company has been able to reduce emissions at a much faster pace than the target. While this can be seen as a positive, it could also indicate that the objective is not ambitious enough, and was therefore too easy to achieve and should probably be adjusted.\n\nBelow the distribution of \"Years to achievement\" for the Corporates objectives based on the answers of table 4.1a of the Corporate questionnaire:\n"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"def kde_plotter(df, x, y, x_label, y_label, main_title, subtitle):\n\n    fig = sns.kdeplot(df[x], df[y], cmap=\"Blues\", shade=True, bw=.1)\n\n    fig.figure.suptitle(main_title, fontsize = 14)\n\n    plt.xlabel(x_label, fontsize=12)\n    plt.ylabel(y_label, fontsize=12)\n    plt.title(subtitle, fontsize=12)\n    plt.ylim(0, 1.0)\n    plt.xlim(0, 1.0)\n    plt.gca().set_aspect('equal', adjustable='box')\n\nfig = sns.kdeplot(C41a_KPI['Years_diff'], shade=True)\nfig.figure.suptitle(\"Companies ambitions of objectives\", fontsize = 14)\nplt.title(\"Distribution of companies' years to achievement of objective\", fontsize=12)\nplt.xlabel('Years to achievement', fontsize=12)\nplt.ylabel(\"Companies' distribution\", fontsize=12)\nplt.xlim(-50, 50)\nplt.legend('')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**Objective strategy VS objective ambition**\n\nWe also created another metric which looks at the \"objective ambition\", expressed in the percentage of CO2 that the company/city intends to cut with for a given objective. For this, we produce another graph that shows the distance from the objective for companies grouped by sector (the companies around the red dotted line are on track to achieve the goal, for the ones above they are way ahead of time to achieve it and for the one below are falling behind). The size of the dot shows the quantity of emissions that they are aiming to cut, while the \"ambition KPI\" shows what is the percentage of emissions they are aiming to cut. \n\nIn the example below, we cross-reference three different KPIs for the cities objectives: \"Years to achivement\", \"Ambition\" and overall CO2 emissions targeted. The KPI refers to question 5.0a of the Cities questionnaire on city-wide emissions reductions in absolute targets.\n\nThe closer to the red dotted line, the more the city is on track to achieve its goal. Points below the red dotted line mean that the city is behind target and the dots above the red line mean that the city is ahead of target, similarly to the distribution presented for companies in the previous section. Further, we cross-reference the KPIs to get additional insight: we can also check how \"ambitious\" is the objective (meaning what percentage of emissions they are aiming to cut) and how big emissions cuts are in terms of CO2 in scope of the objective.\n\nIn other words, to read the graph below, \"big dots\" that are close or above of the red line on the right hand side of the graph are the preferrable status for a city objective.  "},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"fig = sns.relplot(x=\"KPI_50a_rank_objective_ambition\", \n            y=\"Years_diff\", \n            size=\"Covered_emissions\", # WARNING: these emissions are based on different years\n            sizes=(50, 500),\n            hue=\"Covered_emissions\", \n            legend='brief',\n            data=C50a_KPI)\nleg = fig._legend\nleg.texts[0].set_text(\"\")#, fontsize = 10)\n# fig._legend.set_title(\"Covered emissions (metric tonnes CO2e)\", fontsize = 10)\n# leg.set_bbox_to_anchor([.7,0.7])\nplt.axhline(0,1,0,color='r',linestyle='--')\n# fig.figure.suptitle(\"Cities: ambitions of objectives\", fontsize = 18)\n# plt.suptitle(\"Cities: ambitions of objectives\", fontsize = 18)\nplt.title(\"Cities' objectives: Years to achievement VS Ambition\", fontsize=14)\nplt.ylabel('Years to achievement', fontsize=12)\nplt.xlabel(\"KPI: Objective Ambition\", fontsize=12)\nplt.gcf().set_size_inches(12, 4)#, dpi=80)#, forward=True)\nplt.xlim(0, 1)\n\n# plt.setp(\nfig._legend.set_title(\"Covered emissions\")\n#     , fontsize='14') # for legend text #(metric tonnes CO2e)\nplt.setp(fig._legend.get_texts(), fontsize='6') # for legend text\nplt.setp(fig._legend.get_title(), fontsize='8') # for legend title\n# plt.legend('Covered emissions (metric tonnes CO2e)')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**Objective Status**\n\nThe objectives can be also monitored with a binary flag to check if they are on target or behind target, based on progress history.\nA representation below for the Cities objectives on reneable energy targets from table 8.0a of the Cities Questionnaire:"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"# On target VS Behind target\nfig = sns.countplot(x='Target_status', data=C80a)\n\nfig.figure.suptitle(\"Cities: Objective status Table 8.0a\", fontsize = 14)\nplt.title(\"Count of target achievement statuses\", fontsize=12)\nplt.xlabel('Objective achievement status', fontsize=12)\n# plt.ylabel(\"Count of companies\", fontsize=12)\n# plt.xlim(-80, 80)\n# plt.legend('')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**Progress VS Ambition of an objective**\n\nFinally, we show for table 4.1a of Corporations the relationship between two KPI we have created: \"progress\" and \"ambition\". As it can be expected there is an inverse relationship between the two where a lot of low ambition objectives have been achieved or are closed to be achieved (see the dark area on the top left of the graph below), whereas there is a concentration of highly ambitious objectives with little progress that will require a lot of further work in the future (bottom right of the graph below).  "},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"x=\"KPI_rank_objective_ambition\" \ny=\"KPI_rank_objective_progress\"\nx_label = \"Ambition\"\ny_label = \"Progress\"\nmain_title = \"Corporations Objectives\"\nsubtitle = \"Progress vs Ambition\"\n\nkde_plotter(C41a_KPI,\n    x, \n    y, \n    x_label, \n    y_label, \n    main_title, \n    subtitle)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"markdown","source":"<a id=\"section-two\"></a>\n# Risk & Opportunity assessment KPIs\n\nIn this section the Risk & Opportunity assessment KPIs will be described. The table below summarizes the main KPIs we propose for this section and the tables within the questionnaires they can be applied to:"},{"metadata":{},"cell_type":"markdown","source":"<html>\n<head>\n<style>\ntable, th, td {\n  border: 1px solid black;\n}\ntable {\n  width: 100%;\n} \n</style>\n</head>\n<body>\n   \n    \n<table>\n   \n  <tr>\n    <th style=\"text-align:centre\" \"width:130px\"BGcolor=#ff9999>KPI type</th>\n    <th style=\"text-align:centre\" \"width:200px\"BGcolor=#ff9999>Formula/methodology</th>\n    <th style=\"text-align:centre\" \"width:300px\"BGcolor=#ff9999>Interpretation</th>\n    <th style=\"text-align:centre\" \"width:100px\"BGcolor=#ff9999>Cities</th>\n    <th style=\"text-align:centre\" \"width:100px\"BGcolor=#ff9999>Corporations</th>\n    <th style=\"text-align:centre\" \"width:100px\"BGcolor=#ff9999>Water</th>\n  </tr>\n  <tr>\n    <td style=\"text-align:right\" BGcolor=#ffffcc><b>Risk Index</b></td>\n    <td style=\"text-align:right\" BGcolor=#ffffcc>Risk Index = Risk probability * Risk Magnitude / Time horizon</td>\n    <td style=\"text-align:right\" BGcolor=#ffffcc>This KPI gives risk exposure by company/city based on their expectations. The time horizon in years for companies is taken from table C2.1a where they state their definition of short/medium/long term. \nThe concept behind this KPI is an yearly exposure to risk based on how likely it is to happen and how severe its manifestation will be.  Companies/cities can be ranked on their risk exposure. \nFor cities, it was also possible to compare current VS future exposure to a given risk as the questionnaire asks to assess this in both time dimentions. Additionally, for cities this KPI is linked to the Social Vulnerability Index (SVI) to better understand a possible risk/effect outcome.\nFor the water questionnaire this is also aggregated at the country level.</td>\n   <td style=\"text-align:right\" BGcolor=#ffffcc>2.1</td>\n   <td style=\"text-align:right\" BGcolor=#ffffcc>2.3a</td>\n   <td style=\"text-align:right\" BGcolor=#ffffcc>4.2</td>\n  </tr>\n  <tr>\n   <td style=\"text-align:right\" BGcolor=#ffffcc><b>Historical trends of water consumption</b></td>\n   <td style=\"text-align:right\" BGcolor=#ffffcc>Opportunity Index = Opportunity Probability * Opportunity magnitude / Time horizon</td>\n   <td style=\"text-align:right\" BGcolor=#ffffcc>This KPI returns an assessment on opportunities available for companies, by considering the likelihood and the magnitude of the event within a time horizon. The time horizon in years for companies is taken from table C2.1a where they state their definition of short/medium/long term.  \nPlease note that the Water questionnaire did not have the magnitude of the opportunity so in this case the probability is simply divided by the time horizon.</td>\n   <td style=\"text-align:right\" BGcolor=#ffffcc>-</td>\n   <td style=\"text-align:right\" BGcolor=#ffffcc>2.4a</td>\n   <td style=\"text-align:right\" BGcolor=#ffffcc>-</td>\n  </tr>\n  <tr>\n   <td style=\"text-align:right\" BGcolor=#ffffcc><b>Water related risk </b></td>\n   <td style=\"text-align:right\" BGcolor=#ffffcc>Ranking of water related risks by country and company</td>\n   <td style=\"text-align:right\" BGcolor=#ffffcc>A ranking based on number of facilities exposed to water risks. The second version of this KPI aggregates by business and looks at how much a company is exposed in % terms in a given the facility.</td>\n   <td style=\"text-align:right\" BGcolor=#ffffcc>-</td>\n   <td style=\"text-align:right\" BGcolor=#ffffcc>-</td>\n   <td style=\"text-align:right\" BGcolor=#ffffcc>4.1b/ 4.1c</td>\n  </tr>\n</table>\n</body>\n</html>"},{"metadata":{"trusted":true},"cell_type":"markdown","source":"**Risk Index**\n\nWe first display the relationship bewteen the Risk Index KPI and the Time Horizon. The Time horizon in the case of Corporation is obtained by using table 2.1a, where they define their timeframe for short/medium and long term. \n\nWe then match that answer with table 2.3a, where risks faced by the company are transformed by assigning a numerical value to the categorical answers (e.g. High=5, Medium-High=4, Medium=3, etc.) and probability is multipled by magnitude. This value is then divided by the time horizon (where short/medium/long will be based on question 2.1a).\n\nThis information can be further enhanced with the \"Financial Impact\" of the risk that is also identified within table 2.3a.\n\nWe can obesrve from the plot below that the majority of Risks faced by organizations are expected to materialise within the next 10 years, even some high probability/materiality ones with significant financial impact. "},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"## MOST IDENTIFIED RISKS ARE CLOSER IN TIME AND HIGHER IN COSTS! \nfig = sns.relplot(x=\"time_horizon_years\", \n            y=\"KPI_rank_risk_index\", \n            size=\"risk_financial_impact\",\n            sizes=(10, 500), \n            hue=\"risk_financial_impact\", \n            legend='brief',\n            data=C23a_KPI)\n\naxes = fig.axes.flatten()\nleg = fig._legend\nleg.texts[0].set_text(\"\")#, fontsize = 10)\n# axes[0].set_ylabel(\"Risk Index\")\n# axes[0].set_xlabel(\"Time Horizon (years)\")\naxes[0].set_xlim(0, 50)\naxes[0].set_ylim(0, 1.1)\n# axes[0].set_title(\"Risk: forward look and magnitude\")\n\nplt.title(\"Risk: forward look and magnitude\", fontsize=14)\nplt.ylabel('Risk Index', fontsize=12)\nplt.xlabel(\"Time Horizon (years)\", fontsize=12)\nplt.gcf().set_size_inches(12, 4)#, dpi=80)#, forward=True)\nplt.xlim(0, 50)\n# plt.setp(#\nfig._legend.set_title(\"Financial impact\")\n# , fontsize='14') # for legend text #(metric tonnes CO2e)\nplt.setp(fig._legend.get_texts(), fontsize='6') # for legend text\nplt.setp(fig._legend.get_title(), fontsize='8') # for legend title\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**Current VS Future Risk Index for Cities with Social Vulnerability Index**\n\nAn advantage of the KPIs that we propose within this section can be easily crossed with external data sources to enhance the understanding of the risks faced are covered: present (or current) risk and future risk. \n\nWe present below how the risk index increases/decreases over time by also adding the Social Vulnerability Index that was made available by CPD in the supplementary data. \n\nThe bigger the dot, the higher is the social vulnerability (which is the result of an assessment of demographic data on socieconomic status, household composition & disability, minority & language barriers, housing type and transportation). \n\nAdditionally, dots that are above the diagonal indicate that the risk is increasing in the future, whereas the ones below it show a decreasing risk trend reported by the city. Points on the diagonal have a constant expected present/future risk level.   "},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"g = sns.relplot(data=ct, x='risk_index_curr', y='risk_index_fut',  size='RPL_THEMES', hue='RPL_THEMES',\n    sizes=(20, 200))\naxes = g.axes.flatten()\nplt.gcf().set_size_inches(10, 7.5)\naxes[0].set_ylabel(\"Future Risk Index\")\naxes[0].set_xlabel(\"Current Risk Index\")\naxes[0].set_title(\"Current VS Future - Risk Index\")\ndata_to_graph = ct[['risk_index_curr','risk_index_fut','Organization_x']]\ncities_to_remove=[\n   'San Luis Obispo', 'Racine, WI', 'Durham', 'Providence', 'York, ME', 'District of Columbia', 'Winona, MN'\n]\ndata_to_graph[~data_to_graph['Organization_x'].isin(cities_to_remove)].apply(lambda x: axes[0].text(*x),axis=1)\n#     ct['risk_index_curr'], ct['risk_index_fut'], *x, transform=ax.transAxes\n#     ),axis=1)\n\ng._legend.texts[0].set_text(\"\")\ng._legend.set_title(\"Social Vulnerability Index\")\n\nX_plot = np.linspace(0, 1)\nY_plot = X_plot\nplt.plot(X_plot, Y_plot, '--', color='r')\n\nplt.axis('equal')\nplt.ylim(0, 1.1)\nplt.xlim(0, 1.1)\n\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**Risk Index and Project spending on Drought risk - with ND-GAIN data for vulnerability**\n\nSimirarly, we can use the Risk vulnerability KPI against other external datasets such as the ND-GAIN dataset which provides a risk/vulnerability assessment against specific climate change events such as rising sea levels, droughts, heavy precipitations, etc.\n\nIn the graph below we only select projects from table 2.1 of the City questionnaire which are earmarked as for \"Drought\" prevention. We then enchance the data with the ND-GAIN which gives us the Vulnerability to the Drough event for a given city, and finally we plot against the project cost, to check how well a city is gearing up against the risk.\n\nFrom the below, it looks like cities who are more exposed and vulnerable to Drought risk are investing relatively less than cities who are less exposed. They should probably invest more to prevent this risk!"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"a=C30.dropna(subset=['Climate hazards'])\na=a.loc[a['Climate hazards'].str.contains('Water Scarcity')] \na = a.groupby(['Account Number', 'Organization'])['Total Project Cost USD'].sum().reset_index()\na['Total_cost_Log'] = np.log(a['Total Project Cost USD'])\ndrough =pd.merge(cities, ND_DROUGHT, left_on='Organization_new',right_on='City',how='left')\n#drough = drough.dropna()\ndro =pd.merge(drough,a, left_on='Account Number',right_on='Account Number',how='left')\ndro= dro[dro['Organization_x']!='Fremont']\ndro['Risk_Rank'] = dro['Risk'].rank(pct=True)\ng = sns.relplot(data=dro, x='Total_cost_Log', y='Risk',  size='Vulnerability', hue= 'Vulnerability',\n    sizes=(20, 200))\n\naxes = g.axes.flatten()\nplt.gcf().set_size_inches(12, 4)\naxes[0].set_ylabel(\"Risk Index\")\naxes[0].set_xlabel(\"Total Cost of Project in USD (Log))\")\n#axes[0].set_xlim(0, 50)\n# axes[0].set_ylim(0, 0.5)\naxes[0].set_title(\"Drought: Risk Index/Vulnerability and investment\")\ndro[['Total_cost_Log','Risk','Organization_x']].apply(lambda x: axes[0].text(*x),axis=1)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**Water related Risk**\n\nIt is possible to visualize the Risk Index also with a \"country\" level aggregation. The Water questionnaire with Table 4.1c lends itself quite well for this purpose as it reports the Country/Area for which the risk is analysed. \nThe map below shows water related Risk Index by country, where a darker red color indicates a higher Risk Index, meaning that the country is more exposed to water related risk:"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"import plotly.express as px\n\nfig = px.choropleth(country_water_KPIs_2020, locations=\"country_or_region\",\n                    color=\"KPI_rank_risk_index_risks_identified\", # lifeExp is a column of gapminder\n                    labels={\n                     \"country_or_region\": \"Countries\",\n                     \"KPI_rank_risk_index_risks_identified\": \"Risk index\"},\n                    hover_name=\"country_or_region\", # column to add to hover information\n                    locationmode='country names',\n                    color_continuous_scale=px.colors.sequential.OrRd)\n\nfig.update_layout(\n    title_text=\"Companies' water-related Risk Index by country\"\n)\n\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**Stress areas by country**\n\nWe finally show another view by country of a KPI: \"water stress\" by area. This is from Table 6.0 of the Water questionnaire. It reports if there is a \"water stress\" for the area where the company facility is located. The darker the red, the higher the stress for the area."},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"fig = px.choropleth(country_water_KPIs_2020, locations=\"country_or_region\",\n                    color=\"KPI_rank_facility_in_area_with_water_stress_water_use_by_facility\", # lifeExp is a column of gapminder\n                    labels={\n                     \"country_or_region\": \"Countries\",\n                     \"KPI_rank_facility_in_area_with_water_stress_water_use_by_facility\": \"Water stress facility\"},\n                    hover_name=\"country_or_region\", # column to add to hover information\n                    locationmode='country names',\n                    color_continuous_scale=px.colors.sequential.OrRd)\n\nfig.update_layout(\n    title_text=\"Companies' facilities in water stress areas, by country\"\n)\n\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"markdown","source":"<a id=\"section-three\"></a>\n# Return on Investment KPIs\n\nIn this section the Return on investment KPIs will be described. The table below summarizes the main KPIs we propose for this section and the tables within the questionnaires they can be applied to:"},{"metadata":{},"cell_type":"markdown","source":"<html>\n<head>\n<style>\ntable, th, td {\n  border: 1px solid black;\n}\ntable {\n  width: 100%;\n} \n</style>\n</head>\n<body>\n   \n    \n<table>\n   \n  <tr>\n    <th style=\"text-align:centre\" \"width:130px\"BGcolor=#ff9999>KPI type</th>\n    <th style=\"text-align:centre\" \"width:200px\"BGcolor=#ff9999>Formula/methodology</th>\n    <th style=\"text-align:centre\" \"width:300px\"BGcolor=#ff9999>Interpretation</th>\n    <th style=\"text-align:centre\" \"width:100px\"BGcolor=#ff9999>Cities</th>\n    <th style=\"text-align:centre\" \"width:100px\"BGcolor=#ff9999>Corporations</th>\n    <th style=\"text-align:centre\" \"width:100px\"BGcolor=#ff9999>Water</th>\n  </tr>\n  <tr>\n    <td style=\"text-align:right\" BGcolor=#00cc66><b>ROI of risk mitigation</b></td>\n    <td style=\"text-align:right\" BGcolor=#00cc66>ROI of risk mitigation = Risk financial impact / Risk response cost </td>\n    <td style=\"text-align:right\" BGcolor=#00cc66>This KPI show the return on investment of the response cost. A low response cost linked to an high financial impact, indicates a high ROI ,meaning that the company will get a big benefit from a proportionally low investment.</td>\n   <td style=\"text-align:right\" BGcolor=#00cc66>-</td>\n   <td style=\"text-align:right\" BGcolor=#00cc66>2.3a</td>\n   <td style=\"text-align:right\" BGcolor=#00cc66>4.3a</td>\n  </tr>\n  <tr>\n    <td style=\"text-align:right\" BGcolor=#00cc66><b>ROI of opportunity</b></td>\n    <td style=\"text-align:right\" BGcolor=#00cc66>ROI of opportunity = Opportunity financial impact / Cost of realizing the opportunity</td>\n    <td style=\"text-align:right\" BGcolor=#00cc66>This KPI show the return on investment for realizing opportunities. A low opportunity cost linked to an high financial impact, indicates a high ROI ,meaning that the company will get a big benefit from a proportionally low investment.</td>\n   <td style=\"text-align:right\" BGcolor=#00cc66></td>\n   <td style=\"text-align:right\" BGcolor=#00cc66>2.4a</td>\n   <td style=\"text-align:right\" BGcolor=#00cc66></td>\n  </tr>\n  <tr>\n    <td style=\"text-align:right\" BGcolor=#00cc66><b>Spending per capite</b></td>\n    <td style=\"text-align:right\" BGcolor=#00cc66>Spending per capite = Total USD project cost/ Population</td>\n    <td style=\"text-align:right\" BGcolor=#00cc66>Amount in USD per citizen spent on reducing the vulnerability of the city on climate threats.</td>\n   <td style=\"text-align:right\" BGcolor=#00cc66>3.0</td>\n   <td style=\"text-align:right\" BGcolor=#00cc66>-</td>\n   <td style=\"text-align:right\" BGcolor=#00cc66>-</td>\n  </tr>\n  <tr>\n    <td style=\"text-align:right\" BGcolor=#00cc66><b>Return on investment for MWh saving/production C02 cuts</b></td>\n    <td style=\"text-align:right\" BGcolor=#00cc66>ROI MWh cuts = Total USD cost / Mwh cuts \n<br>ROI Green MWh produced = Total USD cost / MWh of green energy produced\n<br>ROI Carbon cuts = Total USD cost / CO2 cuts</td>\n    <td style=\"text-align:right\" BGcolor=#00cc66>This KPI gives a return per dollar investment in terms of the target being achieved. It allows to compare the effectiveness of projects and select the best ones. For example, between 2 projects both aimed at saving 10MWh costing respectively 1000 USD and 500 USD, the second project is clearly more efficient as for half the cost achieves the same result.</td>\n   <td style=\"text-align:right\" BGcolor=#00cc66>5.4</td>\n   <td style=\"text-align:right\" BGcolor=#00cc66>-</td>\n   <td style=\"text-align:right\" BGcolor=#00cc66>-</td>\n  </tr>\n</table>\n</body>\n</html>"},{"metadata":{},"cell_type":"markdown","source":"**Return on investment for MWh saving/production C02 cuts**\n\nThe graph below shows the Return on Investment (ROI) KPI for the cities investments, this is applied to Table 5.4 of the Cities questionnaire. The way to interpret the KPI is that it gives \"how much\" a MWh of electricity savings has cost in US Dollars.\n\nHere we compare: energy savings projects, CO2 reduction projects and energy production projects. \n\nThe lower the number the better it is, as a unit saving is achieved for less cost. \n\nWe can observe that on average CO2 reduction projects tend to be the most cost efficient ones as opposed to Green energy production projects which tend to be more expensive on average and also show a greater degree of variability in cost/efficiency. \n\nHowever, it needs to be said, that often these savings are linked to each other (e.g. a new Green energy facility will bring CO2 reductions), so it shouldn't necessarily be concluded that to invest in a CO2 reduction project alone is more money efficient.\n\nFurthermore, by using this KPI, it is also interesting to identify the projects with the best ROI, as these projects could also be recommended to other cities to achieve their goals.\n\nKPIs on ROI for Corporations Risk Mitigations actions (Table 2.3a for Corporate climate change & Table 4.3a for water questionnaires) can be analysed in a similar fashion, and the most cost efficient actions can be identified. Similarly Table 2.4a is analysed with respect to climate related opportunities, in this case there will be the return on investment a company will achieve  with that climate opportunity."},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"C54KPI= C54_KPI[['Dollar per 1 MWh of Energy Savings','Dollar per 1 Mt of CO2 Emissions reductions','Dollar per 1 MWh of Renewable energy production']]\nC54KPI = C54KPI[C54KPI['Dollar per 1 MWh of Energy Savings']<5000]\nC54KPI = C54KPI[C54KPI['Dollar per 1 MWh of Renewable energy production']<5000]\nC54KPI = C54KPI[C54KPI['Dollar per 1 Mt of CO2 Emissions reductions']<5000]\nC54KPI=C54KPI.rename(columns={'Dollar per 1 MWh of Energy Savings':\"$ return MWH Savings\",'Dollar per 1 Mt of CO2 Emissions reductions':'$ return CO2 reductions',\n                              'Dollar per 1 MWh of Renewable energy production':'$ return MWH ren eng prod'})\n\n#C54KPIe=C54KPI[[\"$ return MWH Savings\",'$ return MWH renewable energy production']]\nsns.set_style(\"white\")\nsns.boxplot(data=C54KPI)\nplt.gcf().set_size_inches(12, 4)\nplt.title(\"Dollars spent for MWh/CO2 unit reduction\")\nplt.show()\n ","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**Other ROI KPIs produced in this section**\n\nROI KPIs are quite useful and can be used together with other KPIs produced within this work to put things into perspective. \n\nWe have shown some examples of this throughout this Notebook, where we use for example total project cost or spending per capite to add further insight to other KPIs. \n"},{"metadata":{"trusted":true},"cell_type":"markdown","source":"<a id=\"section-four\"></a>\n# Trends KPIs\n\nIn this section the Trends KPIs will be described. The table below summarizes the main KPIs we propose for this section and the tables within the questionnaires they can be applied to:"},{"metadata":{"trusted":true},"cell_type":"markdown","source":"<html>\n<head>\n<style>\ntable, th, td {\n  border: 1px solid black;\n}\ntable {\n  width: 100%;\n} \n</style>\n</head>\n<body>\n   \n    \n<table>\n   \n  <tr>\n    <th style=\"text-align:centre\" \"width:130px\"BGcolor=#ff9999>KPI type</th>\n    <th style=\"text-align:centre\" \"width:200px\"BGcolor=#ff9999>Formula/methodology</th>\n    <th style=\"text-align:centre\" \"width:300px\"BGcolor=#ff9999>Interpretation</th>\n    <th style=\"text-align:centre\" \"width:100px\"BGcolor=#ff9999>Cities</th>\n    <th style=\"text-align:centre\" \"width:100px\"BGcolor=#ff9999>Corporations</th>\n    <th style=\"text-align:centre\" \"width:100px\"BGcolor=#ff9999>Water</th>\n  </tr>\n  <tr>\n    <td style=\"text-align:right\" BGcolor=#ffcc66><b>Historical emissions trends (city or company)</b></td>\n    <td style=\"text-align:right\" BGcolor=#ffcc66>Average of the historical CO2 emissions trends by calculating % difference between year t0 - year t1 and then averaging the amounts of the time series per city</td>\n    <td style=\"text-align:right\" BGcolor=#ffcc66> This KPI looks at the trend of Historical emissions of CO2 for the city. Cities which consistently have reduced emissions and showed a % cut for the available time series will be the best performing ones.</td>\n   <td style=\"text-align:right\" BGcolor=#ffcc66>4.13</td>\n   <td style=\"text-align:right\" BGcolor=#ffcc66>6.1</td>\n   <td style=\"text-align:right\" BGcolor=#ffcc66>-</td>\n  </tr>\n  <tr>\n   <td style=\"text-align:right\" BGcolor=#ffcc66><b>Historical trends of water consumption</b></td>\n   <td style=\"text-align:right\" BGcolor=#ffcc66>A ranking is produced by looking at the trends of water consumption of the company.</td>\n   <td style=\"text-align:right\" BGcolor=#ffcc66>Based on the answer of question 1.2b companies are ranked by the trends in water consumption</td>\n   <td style=\"text-align:right\" BGcolor=#ffcc66>-</td>\n   <td style=\"text-align:right\" BGcolor=#ffcc66>-</td>\n   <td style=\"text-align:right\" BGcolor=#ffcc66>1.2d</td>\n  </tr>\n\n</table>\n</body>\n</html>\n"},{"metadata":{"_kg_hide-input":true},"cell_type":"markdown","source":"**Trends KPI**\n\nThe series below shows the data on which we build the historical emissions trends KPI, in this case for cities (Table 4.13). The KPI is simply obtained by averaging the time series of historical percentage changes in emissions. We then rank order cities using this metric to see be able to compare their performance. Cities with the biggest CO2 cuts are obviously the best performing ones.  "},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"C413time = C413.drop('Inventory date from',axis=1)\nC413time['Inventory date to'] = pd.to_datetime(C413time['Inventory date to'])\nC413time['Inventory date to'] = C413time['Inventory date to'].dt.year\nC413time = C413time.sort_values(['Organization','Inventory date to','Row Number']).reset_index()\nC413time = C413time[C413time['Methodology']== 'Global Protocol for Community Greenhouse Gas Emissions Inventories (GPC)']\nC413time['Previous emissions (metric tonnes CO2e)'] = C413time['Previous emissions (metric tonnes CO2e)'].astype(float)\nC413time['diffs'] = C413time['Previous emissions (metric tonnes CO2e)'].pct_change()\nmask = C413time.Organization != C413time.Organization.shift(1)\nC413time['diffs'][mask] = np.nan\nC413time['y_diffs'] = C413time['Inventory date to'].diff()\nC413time['y_diffs'][mask] = np.nan\nC413time=C413time.dropna(subset=['diffs'])\nC413time=C413time[C413time['y_diffs']==1]\n\n\nC413time = C413time.drop(['index','Methodology','Row Number','Previous emissions (metric tonnes CO2e)','y_diffs','Account Number'],axis=1)\n#C413time = C413time.pivot(index=['Inventory date to'], columns='Organization', values='diffs').reset_index()\nC413time = C413time.pivot(index=['Organization'], columns='Inventory date to', values='diffs').reset_index()\nC413time = C413time.T.reset_index()\nnew_header = C413time.iloc[0] #grab the first row for the header\nC413time = C413time[1:] #take the data less the header row\nC413time.columns = new_header\nC413time=C413time[8:12]\nC413time=C413time[['Organization','Ajuntament de Barcelona','City of Boston','City of Columbus','City of Los Angeles','City of Toronto','Hørsholm Kommune']]\nC413time=C413time.astype(float)\n\nC413time['Organization'] = C413time['Organization'].astype(int)\n\n# sns.lineplot(x=\"Organization\", y=\"Ajuntament de Barcelona\", data=C413time)\n# sns.lineplot(x=\"Organization\", y=\"City of Boston\", data=C413time)\n# sns.lineplot(x=\"Organization\", y=\"City of Columbus\", data=C413time)\n# sns.lineplot(x=\"Organization\", y=\"City of Toronto\", data=C413time)\n\n\nfig, ax = plt.subplots(1, 1)\nax.plot(C413time[\"Organization\"], C413time[\"Ajuntament de Barcelona\"], color=\"blue\", label=\"Ajuntament de Barcelona\", linestyle=\"-\")\nax.plot(C413time[\"Organization\"], C413time[\"City of Boston\"], color=\"red\", label=\"City of Boston\", linestyle=\"-\")\nax.plot(C413time[\"Organization\"], C413time[\"City of Columbus\"], color=\"green\", label=\"City of Columbus\", linestyle=\"-\")\nax.plot(C413time[\"Organization\"], C413time[\"City of Toronto\"], color=\"orange\", label=\"City of Toronto\", linestyle=\"-\")\n\nplt.title(\"CO2 emission trends for cities\", fontsize=14)\nplt.xlabel('Years', fontsize=12)\nplt.ylabel(\"Percentage emissions change year on year\", fontsize=12)\n\nimport math\nxint = range(C413time['Organization'].min(), math.ceil(C413time['Organization'].max())+1)\n\nplt.xticks(xint)\n\nax = plt.gca()\nvals = ax.get_yticks()\nax.set_yticklabels(['{:,.1%}'.format(x) for x in vals])\n\nplt.legend(bbox_to_anchor=(1.4, 0.57))#loc=0)\nplt.gcf().set_size_inches(12, 4)\nplt.axhline(0,1,0,color='grey',linestyle=':', linewidth=0.5)\n\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**Historical trends of water consumption**\n\nFor the water questionnaire, we show increase of water movement versus previous reporting year, this KPI is built by using the information collected in Table 1.2d of the Water questionnaire. Water movement is defined as the total sum of water withdrawal, consumption and discharge, which is then grouped by country."},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"fig = px.choropleth(country_water_KPIs_2020, locations=\"country_or_region\",\n                    color=\"KPI_rank_water_total_use_vs_last_year_water_use_by_facility\", # lifeExp is a column of gapminder\n                    labels={\n                     \"country_or_region\": \"Countries\",\n                     \"KPI_rank_water_total_use_vs_last_year_water_use_by_facility\": \"Total water movements VS previous year\"},\n                    hover_name=\"country_or_region\", # column to add to hover information\n                    locationmode='country names',\n                    color_continuous_scale=px.colors.sequential.PuBu)\n\nfig.update_layout(\n    title_text=\"Companies' total water movements VS previous year, by country\"\n)\n\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"markdown","source":"<a id=\"section-five\"></a>\n# City Assessment KPIs\n\nIn this section the City Assessment KPIs will be described. The table below summarizes the main KPIs we propose for this section and the tables within the questionnaires they can be applied to:\n"},{"metadata":{},"cell_type":"markdown","source":"<html>\n<head>\n<style>\ntable, th, td {\n  border: 1px solid black;\n}\ntable {\n  width: 100%;\n} \n</style>\n</head>\n<body>\n   \n    \n<table>\n   \n  <tr>\n    <th style=\"text-align:centre\" \"width:130px\"BGcolor=#ff9999>KPI type</th>\n    <th style=\"text-align:centre\" \"width:200px\"BGcolor=#ff9999>Formula/methodology</th>\n    <th style=\"text-align:centre\" \"width:300px\"BGcolor=#ff9999>Interpretation</th>\n    <th style=\"text-align:centre\" \"width:100px\"BGcolor=#ff9999>Cities</th>\n    <th style=\"text-align:centre\" \"width:100px\"BGcolor=#ff9999>Corporations</th>\n    <th style=\"text-align:centre\" \"width:100px\"BGcolor=#ff9999>Water</th>\n  </tr>\n  <tr>\n    <td style=\"text-align:right\" BGcolor=#ccffcc><b>Green Energy</b></td>\n    <td style=\"text-align:right\" BGcolor=#ccffcc>Ranking of cities with the most % of green energy, where green energy is % of Geothermal + % of Solar + % of Wind Energy</td>\n    <td style=\"text-align:right\" BGcolor=#ccffcc>This KPI ranks the city by looking looking at the energy source.</td>\n   <td style=\"text-align:right\" BGcolor=#ccffcc>8.1</td>\n   <td style=\"text-align:right\" BGcolor=#ccffcc>-</td>\n   <td style=\"text-align:right\" BGcolor=#ccffcc>-</td>\n  </tr>\n  <tr>\n    <td style=\"text-align:right\" BGcolor=#ccffcc><b>Green Transport</b></td>\n    <td style=\"text-align:right\" BGcolor=#ccffcc>Ranking of cities with the greenest ways of transport. Sum of % Micromobility, % Cycling, % Walking.</td>\n    <td style=\"text-align:right\" BGcolor=#ccffcc>This KPI ranks the city by looking at % of clean transportation means. </td>\n   <td style=\"text-align:right\" BGcolor=#ccffcc>10.1</td>\n   <td style=\"text-align:right\" BGcolor=#ccffcc>-</td>\n   <td style=\"text-align:right\" BGcolor=#ccffcc>-</td>\n  </tr>\n  <tr>\n    <td style=\"text-align:right\" BGcolor=#ccffcc><b>Private VS Public Transport</b></td>\n    <td style=\"text-align:right\" BGcolor=#ccffcc>Private transport = Ranking of cities with the least cars per capite by looking at num of cars/ number of citizens\nPublic transport = Ranking of cities by looking at public transportation per capita</td>\n    <td style=\"text-align:right\" BGcolor=#ccffcc>This KPI returns the number of cars in the city per capite and the number of public transportation vehicles per capite</td>\n   <td style=\"text-align:right\" BGcolor=#ccffcc>10.4</td>\n   <td style=\"text-align:right\" BGcolor=#ccffcc>-</td>\n   <td style=\"text-align:right\" BGcolor=#ccffcc>-</td>\n  </tr>\n  <tr>\n    <td style=\"text-align:right\" BGcolor=#ccffcc><b>Size of Zero emissions zone</b></td>\n    <td style=\"text-align:right\" BGcolor=#ccffcc>Percentage of zero emissions zone compared to city area</td>\n    <td style=\"text-align:right\" BGcolor=#ccffcc>This KPI assess the size of zero emissions zones in comparable terms</td>\n   <td style=\"text-align:right\" BGcolor=#ccffcc>10.7a</td>\n   <td style=\"text-align:right\" BGcolor=#ccffcc>-</td>\n   <td style=\"text-align:right\" BGcolor=#ccffcc>-</td>\n  </tr>\n  <tr>\n    <td style=\"text-align:right\" BGcolor=#ccffcc><b>Meat/Dairy consumption</b></td>\n    <td style=\"text-align:right\" BGcolor=#ccffcc>Ranking of cities by using the dairy and meat consumption per capite data </td>\n    <td style=\"text-align:right\" BGcolor=#ccffcc>This KPI assesses food habits by ranking cities with the most food/dairy consumption per capita</td>\n   <td style=\"text-align:right\" BGcolor=#ccffcc>12.1</td>\n   <td style=\"text-align:right\" BGcolor=#ccffcc>-</td>\n   <td style=\"text-align:right\" BGcolor=#ccffcc>-</td>\n  </tr>\n  <tr>\n    <td style=\"text-align:right\" BGcolor=#ccffcc><b>Waste per capita/ per km2</b></td>\n    <td style=\"text-align:right\" BGcolor=#ccffcc>Ranking of cities by calculating of tons of waste per capite (or per km2)</td>\n    <td style=\"text-align:right\" BGcolor=#ccffcc>This KPI returns the amount of waste in  a city per capita or per km2 to allow a term of comparison between cities</td>\n   <td style=\"text-align:right\" BGcolor=#ccffcc>13.0</td>\n   <td style=\"text-align:right\" BGcolor=#ccffcc>-</td>\n   <td style=\"text-align:right\" BGcolor=#ccffcc>-</td>\n  </tr>\n  <tr>\n    <td style=\"text-align:right\" BGcolor=#ccffcc><b>CO2 emissions per capita/ per km2</b></td>\n    <td style=\"text-align:right\" BGcolor=#ccffcc>Ranking of cities per emissions of CO2 per citizen</td>\n    <td style=\"text-align:right\" BGcolor=#ccffcc>This KPI allows a comparable CO2 per capita (or km2) in order to understand which cities are most polluting</td>\n   <td style=\"text-align:right\" BGcolor=#ccffcc>4.6b</td>\n   <td style=\"text-align:right\" BGcolor=#ccffcc>-</td>\n   <td style=\"text-align:right\" BGcolor=#ccffcc>-</td>\n  </tr>\n</table>\n</body>\n</html>"},{"metadata":{"trusted":true},"cell_type":"markdown","source":"**City assessment and performance comparison**\n\nThe City Assessment KPIs allow a direct comparison between cities. \n\nThis is achieved by calculating the raw KPI value (explained below) and then ranking cities based on their results. \n\nIn the spider charts below we compare four cities by using CO2 Emissions per capita (the lower the better), % of green energy sourcing (the higher the better), number of private cars per individual (the lower the better), number of public transport vehicles per capita (the higher the better) and tons of waste per capita (the lower the better). It becomes evident from the below how the KPIs we have built in this section allow a direct comparison of the performance of the city against key metrics. "},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"abc = cities_KPIs_2020[['Organization','KPI_130_waste_rank_waste','KPI_104_private_car_rank_transport_fleet_size',\n                  'KPI_104_public_transport_rank_transport_fleet_size','KPI_81_Green_energy_rank_energy_consumption',\n                 'KPI_46b_CO2 Emissions per capite_rank_city_wide_emissions']]\nabc=abc.dropna()\nabc=abc.rename(columns={\"KPI_130_waste_rank_waste\": \"Waste per capite\", \"KPI_104_private_car_rank_transport_fleet_size\": \"Cars per capite\", \n                   \"KPI_104_public_transport_rank_transport_fleet_size\": \"Public transport per individual\",'KPI_81_Green_energy_rank_energy_consumption':'Green Energy consumed',\n                  'KPI_46b_CO2 Emissions per capite_rank_city_wide_emissions':'CO2 per capite'}, errors=\"raise\")\nbcd =abc[2:6]\nbcd =bcd.reset_index()\nbcd=bcd.drop(['index'], axis=1)\n\n# ------- PART 1: Define a function that do a plot for one line of the dataset!\n \ndef make_spider( row, title, color):\n \n# number of variable\n    categories=list(bcd)[1:]\n    N = len(categories)\n \n# What will be the angle of each axis in the plot? (we divide the plot / number of variable)\n    angles = [n / float(N) * 2 * pi for n in range(N)]\n    angles += angles[:1]\n \n# Initialise the spider plot\n    ax = plt.subplot(2,2,row+1, polar=True, )\n \n# If you want the first axis to be on top:\n    ax.set_theta_offset(pi / 2)\n    ax.set_theta_direction(-1)\n \n# Draw one axe per variable + add labels labels yet\n    plt.xticks(angles[:-1], categories, color='grey', size=8)\n \n# Draw ylabels\n    ax.set_rlabel_position(0)\n    plt.yticks([0.25,0.5,0.75,1], ['0.25','0.5','0.75','1'], color=\"grey\", size=7)\n    plt.ylim(0,1)\n \n# Ind1\n    values=bcd.loc[row].drop('Organization').values.flatten().tolist()\n    values += values[:1]\n    ax.plot(angles, values, color=color, linewidth=2, linestyle='solid')\n    ax.fill(angles, values, color=color, alpha=0.4)\n \n# Add a title\n    plt.title(title, size=11, color=color, y=1.1)\n    plt.subplots_adjust(wspace=0.4)\n \n# ------- PART 2: Apply to all individuals\n# initialize the figure\nmy_dpi=96\nplt.figure(figsize=(1000/my_dpi, 1000/my_dpi), dpi=my_dpi)\n \n# Create a color palette:\nmy_palette = plt.cm.get_cmap(\"Set2\", len(bcd.index))\n \n# Loop to plot\nfor row in range(0, len(bcd.index)):\n    make_spider( row=row, title=bcd['Organization'][row], color=my_palette(row))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**Correlation between cars per capita and Public transport per capita**\n\nAnother example on how the KPIs we have presented in this section can be used is to checking the correlation between them, when meaningful. For example, somewhat expectedly we find that there is a negative correlation between number of Cars and Public transport available: the more the cars per capita in the city, the less public transport per capita. This means that cities that invest in public transportation are successfully reducing the number of cars per person. \n\nCorrelation between the two KPIs:"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"C104_KPI['Private cars per capite'].corr(C104_KPI['Municipal fleet per capite'])","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"<a id=\"section-six\"></a>\n# Corporate Assessment KPIs\n\nIn this section the Corporate Assessment KPIs will be described. The table below summarizes the main KPIs we propose for this section and the tables within the questionnaires they can be applied to:"},{"metadata":{"trusted":true},"cell_type":"markdown","source":"<html>\n<head>\n<style>\ntable, th, td {\n  border: 1px solid black;\n}\ntable {\n  width: 100%;\n} \n</style>\n</head>\n<body>\n   \n    \n<table>\n   \n  <tr>\n    <th style=\"text-align:centre\" \"width:130px\"BGcolor=#ff9999>KPI type</th>\n    <th style=\"text-align:centre\" \"width:200px\"BGcolor=#ff9999>Formula/methodology</th>\n    <th style=\"text-align:centre\" \"width:300px\"BGcolor=#ff9999>Interpretation</th>\n    <th style=\"text-align:centre\" \"width:100px\"BGcolor=#ff9999>Cities</th>\n    <th style=\"text-align:centre\" \"width:100px\"BGcolor=#ff9999>Corporations</th>\n    <th style=\"text-align:centre\" \"width:100px\"BGcolor=#ff9999>Water</th>\n  </tr>\n  <tr>\n    <td style=\"text-align:right\" BGcolor=#99ccff><b>Efficiency of purchased energy in terms of CO2 for company or country</b></td>\n    <td style=\"text-align:right\" BGcolor=#99ccff>Efficiency of Metric tons of CO2 Scope 2 market based divide by Purchased & consumed electricity including low carbon. Available per company and country</td>\n    <td style=\"text-align:right\" BGcolor=#99ccff>This KPI shows the CO2 efficiency of energy sources, lower C02 emissions per MWh purchased are desirable. Available at company or country level.</td>\n   <td style=\"text-align:right\" BGcolor=#99ccff>-</td>\n   <td style=\"text-align:right\" BGcolor=#99ccff>7.5</td>\n   <td style=\"text-align:right\" BGcolor=#99ccff>-</td>\n  </tr>\n  <tr>\n   <td style=\"text-align:right\" BGcolor=#99ccff><b>Energy consumption - renewable</b></td>\n   <td style=\"text-align:right\" BGcolor=#99ccff>% of renewable energy energy consumed</td>\n   <td style=\"text-align:right\" BGcolor=#99ccff>This KPI allows to order companies by % of renwable energy consumed</td>\n   <td style=\"text-align:right\" BGcolor=#99ccff>-</td>\n   <td style=\"text-align:right\" BGcolor=#99ccff>8.2a</td>\n   <td style=\"text-align:right\" BGcolor=#99ccff>-</td>\n  </tr>\n  <tr>\n   <td style=\"text-align:right\" BGcolor=#99ccff><b>Electricity generated from renewable sources</b></td>\n   <td style=\"text-align:right\" BGcolor=#99ccff>Renewable energy generated = Energy from renewable sources divided by total generation of energy\nRenewable energy consumed =  Energy from renewable sources that company consumes divided total energy consumed\nRenewable energy sold] = Renewable energy produced in excess (gross renewable produced - consumed) divide by total gorss generation of energy</td>\n   <td style=\"text-align:right\" BGcolor=#99ccff>This KPI allows to rank companies by looking at the amount of renewable energy generated/consumed and released into the network in percentage terms</td>\n   <td style=\"text-align:right\" BGcolor=#99ccff>-</td>\n   <td style=\"text-align:right\" BGcolor=#99ccff>8.2d</td>\n   <td style=\"text-align:right\" BGcolor=#99ccff>-</td>\n  </tr>\n</table>\n</body>\n</html>"},{"metadata":{},"cell_type":"markdown","source":"**Corporations Key performance**\n\nBelow is an example of Corporation assessment KPIs: Energy produced from renewables (the highest the better), Excess Energy produced from renewables (clean energy produced but not used by the Company, and put into the network, the highest the better), Percentage consumption of renewable energy (the higher the better) and finally the Market emissions efficiency where the higher KPI is, the less CO2 Metric TOns are produced per MWh. \n\nFor the KPIs presented in this section, it is also possible to create a grouping by Country by using the data of the company that are located in a certain country. It needs to be said that, that this grouping is based on the location of the company headquarter and so this visual could be biased as global companies could have operations abroad and some of the emissions could be generated in another country."},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"abc1 = company_climate_KPIs_2020[['organization','KPI_rank_electricty_generated_from_renewables_energy_consumption_details','KPI_rank_electricty_generated_from_renewables_sold_energy_consumption_details',\n                       'KPI_rank_energy_renewables_energy_consumption','KPI_rank_energy_efficiency_mkt_emissions_targets_by_country']]\nabc1=abc1.dropna()\nabc1=abc1.rename(columns={'KPI_rank_electricty_generated_from_renewables_energy_consumption_details':'Energy produced from renewables',\n                          'KPI_rank_electricty_generated_from_renewables_sold_energy_consumption_details': 'Excess renewable produced',\n                       'KPI_rank_energy_renewables_energy_consumption':'Consumption of renewable energy','KPI_rank_energy_efficiency_mkt_emissions_targets_by_country':'Market emissions efficiency'},\n                 errors=\"raise\")\nbcd1 =abc1[8:12]\nbcd1 =bcd1.reset_index()\nbcd1=bcd1.drop(['index'], axis=1)\n# ------- PART 1: Define a function that do a plot for one line of the dataset!\n \ndef make_spider( row, title, color):\n \n# number of variable\n    categories=list(bcd1)[1:]\n    N = len(categories)\n \n# What will be the angle of each axis in the plot? (we divide the plot / number of variable)\n    angles = [n / float(N) * 2 * pi for n in range(N)]\n    angles += angles[:1]\n \n# Initialise the spider plot\n    ax = plt.subplot(2,2,row+1, polar=True, )\n \n# If you want the first axis to be on top:\n    ax.set_theta_offset(pi / 2)\n    ax.set_theta_direction(-1)\n \n# Draw one axe per variable + add labels labels yet\n    plt.xticks(angles[:-1], categories, color='grey', size=8)\n \n# Draw ylabels\n    ax.set_rlabel_position(0)\n    plt.yticks([0.25,0.5,0.75,1], ['0.25','0.5','0.75','1'], color=\"grey\", size=7)\n    plt.ylim(0,1)\n \n# Ind1\n    values=bcd1.loc[row].drop('organization').values.flatten().tolist()\n    values += values[:1]\n    ax.plot(angles, values, color=color, linewidth=2, linestyle='solid')\n    ax.fill(angles, values, color=color, alpha=0.4)\n \n# Add a title\n    plt.title(title, size=11, color=color, y=1.1)\n    plt.subplots_adjust(wspace=0.75)\n \n# ------- PART 2: Apply to all individuals\n# initialize the figure\nmy_dpi=96\nplt.figure(figsize=(1000/my_dpi, 1000/my_dpi), dpi=my_dpi)\n \n# Create a color palette:\nmy_palette = plt.cm.get_cmap(\"Set2\", len(bcd1.index))\n \n# Loop to plot\nfor row in range(0, len(bcd1.index)):\n    make_spider( row=row, title=bcd1['organization'][row], color=my_palette(row))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**Energy effieincy in terms of CO2**\n\nAdditionally, we report the CO2 efficiency reported by companies answering Table 7.5, the greener the color in the map, the more energy efficient is the country, where efficiency is expressed in of Metric tons of CO2 Scope 2 market based divided by Purchased & consumed electricity including low carbon. "},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"import plotly.express as px\n\nfig = px.choropleth(country_climate_KPIs_2020, locations=\"C7.5_C1Country/Region\",\n                    color=\"KPI_rank_energy_efficiency_loc\", # lifeExp is a column of gapminder\n                    labels={\n                     \"C7.5_C1Country/Region\": \"Countries\",\n                     \"KPI_rank_energy_efficiency_loc\": \"Energy efficiency\"},\n                    hover_name=\"C7.5_C1Country/Region\", # column to add to hover information\n                    locationmode='country names',\n                    color_continuous_scale=px.colors.sequential.BuGn)\n\nfig.update_layout(\n    title_text=\"Companies' energy efficiency by country\"\n)\n\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"<a id=\"section-seven\"></a>\n# Water Management Assessment KPIs\n\nIn this section the Water Management Assessment KPIs will be described. The table below summarizes the main KPIs we propose for this section and the tables within the questionnaires they can be applied to:"},{"metadata":{"trusted":true},"cell_type":"markdown","source":"<html>\n<head>\n<style>\ntable, th, td {\n  border: 1px solid black;\n}\ntable {\n  width: 100%;\n} \n</style>\n</head>\n<body>\n   \n    \n<table>\n   \n  <tr>\n    <th style=\"text-align:centre\" \"width:130px\"BGcolor=#ff9999>KPI type</th>\n    <th style=\"text-align:centre\" \"width:200px\"BGcolor=#ff9999>Formula/methodology</th>\n    <th style=\"text-align:centre\" \"width:300px\"BGcolor=#ff9999>Interpretation</th>\n    <th style=\"text-align:centre\" \"width:100px\"BGcolor=#ff9999>Cities</th>\n    <th style=\"text-align:centre\" \"width:100px\"BGcolor=#ff9999>Corporations</th>\n    <th style=\"text-align:centre\" \"width:100px\"BGcolor=#ff9999>Water</th>\n  </tr>\n  <tr>\n    <td style=\"text-align:right\" BGcolor=#ffccff><b>Overall importance of water</b></td>\n    <td style=\"text-align:right\" BGcolor=#ffccff>Overall importance = Direct water importance + Indirect water importance both for fresh and recycled water </td>\n    <td style=\"text-align:right\" BGcolor=#ffccff>This KPI looks at the questionnaire answers of the company and rates the importance of water for the comapany. Ranking of the companies is provided.</td>\n   <td style=\"text-align:right\" BGcolor=#ffccff>-</td>\n   <td style=\"text-align:right\" BGcolor=#ffccff>-</td>\n   <td style=\"text-align:right\" BGcolor=#ffccff>1.1</td>\n  </tr>\n  <tr>\n   <td style=\"text-align:right\" BGcolor=#ffccff><b>Water movement </b></td>\n   <td style=\"text-align:right\" BGcolor=#ffccff>Water movement = water withdrawn + water consumed + water discharged\nThis is also available by location/facility/company by using table 5.1</td>\n   <td style=\"text-align:right\" BGcolor=#ffccff>This KPI shows the amount of water that is handled by the company by looking at the quantity from the processes of withdrawal, consumption and discharged. Ranking of the companies with higher/lower movements is produced.</td>\n   <td style=\"text-align:right\" BGcolor=#ffccff>-</td>\n   <td style=\"text-align:right\" BGcolor=#ffccff>-</td>\n   <td style=\"text-align:right\" BGcolor=#ffccff>1.2d/ 5.1</td>\n  </tr>\n\n</table>\n</body>\n</html>"},{"metadata":{},"cell_type":"markdown","source":"**Water management - Key performance**\n\nBelow we show some of the KPIs produced to assess Water management, built from the water questionnaire. \n\nIn detail we show: Water intensity (which accounts for overall water usage from the company), Water Withdrawals, Water Consumption, Water Discharges and Water Importance. These KPIs are also produced by Country based on the facility location . "},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"abc2=company_water_KPIs_2020[['organization','KPI_rank_water_use_total_movement_quantity_water_use','KPI_rank_water_use_withdrawals_quantity_water_use','KPI_rank_water_use_discharges_quantity_water_use',\n                        'KPI_rank_water_use_consumption_quantity_water_use','KPI_rank_use_importance_water_importance']]\nabc2=abc2.dropna()\nabc2=abc2.rename(columns={'KPI_rank_water_use_total_movement_quantity_water_use':'Water Intensity','KPI_rank_water_use_withdrawals_quantity_water_use':'Water Withdrawalsz',\n                          'KPI_rank_water_use_discharges_quantity_water_use':'Water Discharges',\n                        'KPI_rank_water_use_consumption_quantity_water_use':'Water Consumption','KPI_rank_use_importance_water_importance':'Water Importance'},errors='raise')\nbcd2 =abc2[8:12]\nbcd2 =bcd2.reset_index()\nbcd2=bcd2.drop(['index'], axis=1)\n# ------- PART 1: Define a function that do a plot for one line of the dataset!\n \ndef make_spider( row, title, color):\n \n# number of variable\n    categories=list(bcd2)[1:]\n    N = len(categories)\n \n# What will be the angle of each axis in the plot? (we divide the plot / number of variable)\n    angles = [n / float(N) * 2 * pi for n in range(N)]\n    angles += angles[:1]\n \n# Initialise the spider plot\n    ax = plt.subplot(2,2,row+1, polar=True, )\n \n# If you want the first axis to be on top:\n    ax.set_theta_offset(pi / 2)\n    ax.set_theta_direction(-1)\n \n# Draw one axe per variable + add labels labels yet\n    plt.xticks(angles[:-1], categories, color='grey', size=8)\n \n# Draw ylabels\n    ax.set_rlabel_position(0)\n    plt.yticks([0.25,0.5,0.75,1], ['0.25','0.5','0.75','1'], color=\"grey\", size=7)\n    plt.ylim(0,1)\n \n# Ind1\n    values=bcd2.loc[row].drop('organization').values.flatten().tolist()\n    values += values[:1]\n    ax.plot(angles, values, color=color, linewidth=2, linestyle='solid')\n    ax.fill(angles, values, color=color, alpha=0.4)\n \n# Add a title\n    plt.title(title, size=11, color=color, y=1.1)\n    plt.subplots_adjust(wspace=0.6)\n \n# ------- PART 2: Apply to all individuals\n# initialize the figure\nmy_dpi=96\nplt.figure(figsize=(1000/my_dpi, 1000/my_dpi), dpi=my_dpi)\n \n# Create a color palette:\nmy_palette = plt.cm.get_cmap(\"Set2\", len(bcd2.index))\n \n# Loop to plot\nfor row in range(0, len(bcd2.index)):\n    make_spider( row=row, title=bcd2['organization'][row], color=my_palette(row))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**Water Movements**\n\nFinally, total water movements by country are captured from table 5.1 of the Water questionnaire, where water movement is defined by the sum of water withdrawal, water consumption and discharge. \n\nThis map can be looked at with the tables we have shown previously for water, for example USA has a high water risk (shown in the Risk KPI section), combined with an upward water movement trend (shown in the Trend KPI section). Therefore this country,as well as others, could be affected by water related issues in the future and should make efforts to mitigate this risk."},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"### Companies' water uses\nfig = px.choropleth(country_water_KPIs_2020, locations=\"country_or_region\",\n                    color=\"KPI_rank_water_total_use_water_use_by_facility\", # lifeExp is a column of gapminder\n                    labels={\n                     \"country_or_region\": \"Countries\",\n                     \"KPI_rank_water_total_use_water_use_by_facility\": \"Total water movements\"},\n                    hover_name=\"country_or_region\", # column to add to hover information\n                    locationmode='country names',\n                    color_continuous_scale=px.colors.sequential.PuBu)\n\nfig.update_layout(\n    title_text=\"Companies' total water movements, by country\"\n)\n\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"markdown","source":"# Conclusion\n\nWe have produced quite a large number of possible KPIs that can be found in our code and summary tables for this project. We chose the ones that we think are more meaningful to be presented in this notebook. \n\nWe think our KPIs can help CDP to construct a meaningful monitoring of key indicators on how well a company/city is doing. Additionally, we have shown that the KPIs can be utilised against other data sources to obtain further insights.\n\nWe have enjoyed working on this project and we have learned a lot from it. We hope our results will be interesting for the CDP and the Kaggle community. "}],"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}