{"cells":[{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"markdown","source":"In this notebook, we will try to come up with KPIs that can help CDP to assess performances of cities and corporations on different aspects, that have impacts on our environment and that in turn cause social impacts as well. All the KPI use CDP data and leverages some external data.\n\nWe have restricted most of our work to US cities, for deeper exploration of socio-environmental issues, through utilization of a diverse set of external data. But occasionally we have also looked at European cities, for possible comparisons on KPI values. \n\n**We have categorized cities into 4 different segments, based on population density**. Why? Because population density is a reflection of various underlying factors and their current status. For example, industry, employment, facilities, favourable geolocation etc. For last 100 years, these factors have caused increment or decrement in population in the cities, thus changing the population density. However, resource consumption varies according to population density and often causes catastrophic consequences to city and it's neighbouring environment. \n\n**We have opted this way for two reasons.**\n\n**1. Along with creating KPIs, it's important to assess their performance and accuracy. This will pave the way to do that, through relevant discussions.** \n\n**2. Measuring and observing the values of KPIs, against the population density segment first, instead of looking at individual performance of a city, will help us to generate insights on a broader scale.**\n\n\n\nSince this is a long notebook, let us start with an abstract of the entire work.\n\n# Abstract\n\nMain theme of this work is to explore the impact of our energy sources in GHG emission and water resources. Currently, our energy plants, cities and corporations generate & use power, from  different kinds of fuels. Some of them are renewable and some are not. GHG emission varies from high to low, based on the fuel. We will explore KPIs that can help to establish link between energy consumption of a city and it's ghg emission in various sectors and how it varies across different population density segment, due to availability or non-availability of renewable energy sources. We will also discuss on correlated social impacts of the total emissions.\n\nBut along with this, we will see, how choice of energy source also influence the water usage. Later, we will combine, ghg emission and excess water usage for energy needs to create **'Long-term Environment Risk ', that summarizes, how adversely a city's energy consumption pattern is affecting the air and water reserves of environment.**\n\nThen, we will map **Long-term Environment Risk  and CDC's Social Vulnerability Index in one to,categorize cities into sections, such as 'high vulnerability-high environmental risk' category.** This will show, how dire is the situation for a city in long term.\n\nNext, we will measure imminent environmental risk using city responses on climate hazards faced. We will combine Long-term risks and imminent risks to environment against adaptation efforts of the cities, to explore, whether cities' responses are proportionate to risk.\n\nAlthough, the above will be our main focus of work, we will later explore additional KPIs , that can help CDP to assess other aspects of survey responses, such as\n\n1. adaptation efforts of the cities\n2. severity of impact on social aspects of the cities\n3. severity of affected services in the cities\n4. KPIs on food and waste related sectors.\n\nLastly, we will also look at some KPIs, related to corporations, showing , \n1. their favourability towards GHG emissions in terms of corporate expenditure and \n2. their carbon dependency or growth of GHG emission in their operations."},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","collapsed":true,"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":false},"cell_type":"markdown","source":"# Data used and approach towards data usage\n\nGHG emission doesn't happen as an isolated event. It's sources are there in food production, waste generation, transport and our day to day city activities. CDP' survey captures data on many of these and we will study all these data parallelly, instead of individually, so that the environmental impacts become more clear.\n \n However, **to make relevant KPIs, we need data, which has not been provided by all cities in all of the sections. Some times data we need to combine, belong to different but adjacent years. However, most of the data used here are at city-based county level or at state level. We will leverage the FIPS based values, present in many of these data to increase accuracy of the derived KPIs.** \n \n We will work with these data,under the assumption that at city level, these data do not change drastically on the very next year. For example, per capita income or emission or energy consumption, is unlikely to differ by a significant margin between 2018 and 2019.\n\nSince, we have used a lot of external data, it's necessary to give some details about it to the readers.\n\n**1. USA's per capita energy consumption data, sector-wise.**\n\n    This provides data about state-wise per capita energy consumption in sectors like residential,industrial,commercial,transport.\n\n    Source : https://www.eia.gov/state/seds/data.php?incfile=/state/seds/sep_sum/html/rank_use_capita.html&sid=US\n\n\n**2. USA's water usage data for cooling needs in energy plants.**\n\n    Plant-wise water usage for colling needs, with details of related fuel, state-based location.\n    Source : https://www.eia.gov/electricity/data/water/ \n    \n\n**3. USA's Water census data 2015.**\n    \n    Water census happens on every 5 years in USA. Last available census data with details of sector based usage and source of water, at county level of USA.\n\n    Source : https://www.sciencebase.gov/catalog/item/5af3311be4b0da30c1b245d8\n\n\n**4. Financial data of enlisted US companies.**\n\n    Financial data of companies , enlisted with US securities and exchange commission.\n    Source  : https://www.sec.gov/dera/data/financial-statement-data-sets.html\n    \n**5. Social Vulnerability Index Data.**\n\n    CDC provided social vulnerability index data for 2019.\n    \n**6. CDC Census data of 500 cities.**\n\n    "},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"from glob import glob\nimport pandas as pd\nimport numpy as np\nfrom tqdm import tqdm\nfrom difflib import SequenceMatcher\nimport plotly.graph_objects as go\nimport plotly.express as px\nfrom plotly.subplots import make_subplots\nfrom sklearn.preprocessing import normalize\n\nimport seaborn as sns\nimport plotly as ply\nimport altair as alt\nimport matplotlib.pyplot as plt","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"external_data_dir = '/kaggle/input/cdp-leo/External_data/'\ncdp_dir = '/kaggle/input/cdp-unlocking-climate-solutions/'\ndata_2018 = glob(cdp_dir+'*/*/*/2018*.csv')+glob(cdp_dir+'*/*/*2018*.csv')\ndata_2019 = glob(cdp_dir+'*/*/*/2019*.csv')+glob(cdp_dir+'*/*/*2019*.csv')\ndata_2020 = glob(cdp_dir+'*/*/*/2020*.csv')+glob(cdp_dir+'*/*/*2020*.csv')\n\ncities_response_files = glob(cdp_dir+'*/*/*Full_Cities_Dataset.csv')\ncities_disclosing_files = glob(cdp_dir+'*/*/*Cities_Disclosing_to_CDP.csv')\n\ncities_disclosing = pd.concat([pd.read_csv(file) for file in cities_disclosing_files])\ncities_response = pd.concat([pd.read_csv(file) for file in cities_response_files])\n\ncorporate_climate_change_disclosing_files = glob(cdp_dir+'*/*/*/*Corporates_Disclosing_to_CDP_Climate_Change*.csv')\ncorporate_water_security_disclosing_files = glob(cdp_dir+'*/*/*/*Corporates_Disclosing_to_CDP_Water_Security*.csv')\ncorporate_climate_change_response_files = glob(cdp_dir+'*/*/*/*Full_Climate_Change_Dataset*.csv')\ncorporate_water_security_response_files = glob(cdp_dir+'*/*/*/*Full_Water_Security_Dataset*.csv')\n\nsvi_data = pd.read_csv(glob(cdp_dir+'*/*/*SVI*US.csv')[0])\nsvi_data['AREA_SQKM']= svi_data['AREA_SQMI']*2.59\ncensus_tract_data = pd.read_csv(cdp_dir+'Supplementary Data/CDC 500 Cities Census Tract Data/500_Cities__Census_Tract-level_Data__GIS_Friendly_Format___2019_release.csv')\n\ncorporate_climate_change_disclosing = pd.concat([pd.read_csv(file) for file in corporate_climate_change_disclosing_files])\ncorporate_water_security_disclosing = pd.concat([pd.read_csv(file) for file in corporate_water_security_disclosing_files])\ncorporate_climate_change_response = pd.concat([pd.read_csv(file) for file in corporate_climate_change_response_files])\ncorporate_water_security_response = pd.concat([pd.read_csv(file) for file in corporate_water_security_response_files])\n\nweightage = pd.read_csv(external_data_dir+'weightage.csv')\nwater_usage_data = pd.read_csv(external_data_dir+'usco2015v2.0.csv')\ncooling_summary = pd.read_csv(external_data_dir+'cooling_summary_2019.csv')\ncorporations = pd.read_csv(external_data_dir+'climate_change_us_comps.csv')\nfinancial_data = pd.read_csv(external_data_dir+'2020q1/num.txt',sep='\\t')\ntag_data = pd.read_csv(external_data_dir+'2020q1/sub.txt',sep='\\t')\n\nworldcities = pd.read_csv(external_data_dir+'worldcities.csv').rename(columns={'country':'Country'})\nworldcities['Country'].replace(['Bolivia','Hong Kong','Côte D’Ivoire','Congo (Kinshasa)',\n                               'Korea, South','Moldova','Russia','West Bank','Taiwan','United Kingdom','Tanzania',\n                                'United States','Venezuela','Vietnam'                                \n                               ],\n                               \n                               \n                              ['Bolivia (Plurinational State of)',\n 'China, Hong Kong Special Administrative Region',                              \n \"Côte d'Ivoire\",\n 'Democratic Republic of the Congo',\n 'Republic of Korea',\n 'Republic of Moldova',\n 'Russian Federation',\n 'State of Palestine',\n 'Taiwan, Greater China',\n 'United Kingdom of Great Britain and Northern Ireland',\n 'United Republic of Tanzania',\n 'United States of America',\n 'Venezuela (Bolivarian Republic of)',\n 'Viet Nam'],\n                              inplace=True)\n\n\n\n\nworldcities = worldcities.merge(cities_response[['Country','CDP Region']].drop_duplicates(),on='Country',how='left')\nworld_cities_response = pd.DataFrame(cities_response,copy=True)\ncities_response = cities_response[(cities_response['Country']=='United States of America')&(cities_response['Year Reported to CDP'].isin([2019,2020]))].reset_index(drop=True)\n\nuscities = pd.read_csv(glob(cdp_dir+'*/*/uscities.csv')[0])\ncities_response['state_id'] = cities_response['Organization'].apply(lambda x: x.split(',')[-1].rstrip().lstrip() if ',' in x else '')\n\n\n\nmapping = {}\npossible_mismatch = []\n\n# Match names from worldcities data and create mapping, based on a string similarity more than 0.5\n\nfor city in cities_response['Organization'].unique():\n    if 'city' in city.lower() and 'county' not in city.lower():\n        similarity = 0\n        candidate = 'None'\n    #     print(city)\n\n\n        city1 = city.split(',')[0]\n        city1 = city1.replace('City of ','')\n        city1 = city1.replace('Town of ','')\n    #     print(city)\n    #     print(country)\n        for wcity in uscities['city'].unique():\n            sim = SequenceMatcher(None, city1, wcity).ratio()\n    #         print(city,wcity,sim)\n            if sim>similarity:\n                candidate = wcity\n                similarity=sim\n\n\n        if similarity<0.5:\n            possible_mismatch.append((city1,candidate))\n        else:\n            mapping[city] = candidate\n        \n# those cities which did not have a good match, find their locations below\nmapping['Metropolitan Government of Nashville and Davidson County'] = 'Nashville'\nmapping['City and County of Honolulu'] = 'Honolulu'\nmapping['District of Columbia'] = 'Washington'\n\ncities_response['Organization'].replace(mapping,inplace=True)\ncities_response = cities_response[cities_response['Organization'].isin(mapping.values())]\n\n\n\nuscities.rename(columns={'city':'Organization','lng':'long'},inplace=True)\nuscities.sort_values(by='population',ascending=False,inplace=True)\nuscities.drop_duplicates(subset='Organization',keep='first',inplace=True)\n\n\ndef get_state_id(x):\n    \n    org = x[0]\n    state_id = x[1]\n    \n    if state_id=='':\n        return uscities[uscities['Organization']==org]['state_id'].values[0]\n    else:\n        return state_id\n    \n    \ncity_metadata = cities_response[cities_response['Question Number']=='0.6'][['Organization','Response Answer']].drop_duplicates(subset='Organization',keep='last')\ncity_metadata.rename(columns={'Response Answer':'area in sq.km'},inplace=True)\ncity_metadata.sort_values(by='Organization',inplace=True)\ncity_metadata = city_metadata.merge(cities_response[['Organization','state_id']].drop_duplicates(subset='Organization',keep='last'),on='Organization',how='left')\ncity_metadata['state_id'] = city_metadata[['Organization','state_id']].apply(lambda x: get_state_id(x),axis=1)\n\n\nworld_city_metadata = world_cities_response[world_cities_response['Question Number']=='0.6'][['Organization','Response Answer']].drop_duplicates(subset='Organization',keep='last')\nworld_city_metadata.rename(columns={'Response Answer':'area in sq.km'},inplace=True)\nworld_city_metadata.sort_values(by='Organization',inplace=True)\n\n\npopulation_data = cities_response[(cities_response['Question Number']=='0.5')&(cities_response['Column Name']=='Current population')][['Organization','Response Answer']].drop_duplicates(subset='Organization',keep='last')\npopulation_data.rename(columns={'Response Answer':'population'},inplace=True)\npopulation_data.sort_values(by='Organization',inplace=True)\n\nworld_population_data = world_cities_response[(world_cities_response['Question Number']=='0.5')&(world_cities_response['Column Name']=='Current population')][['Organization','Response Answer']].drop_duplicates(subset='Organization',keep='last')\nworld_population_data.rename(columns={'Response Answer':'population'},inplace=True)\nworld_population_data.sort_values(by='Organization',inplace=True)\n\n\ncity_metadata = city_metadata.merge(population_data,on='Organization',how='left')\ncity_metadata['population'] = city_metadata['population'].astype('float')\ncity_metadata['area in sq.km'] = city_metadata['area in sq.km'].astype('float')\ncity_metadata['density'] = city_metadata['population']/city_metadata['area in sq.km']\n\nworld_city_metadata = world_city_metadata.merge(world_population_data,on='Organization',how='left')\nworld_city_metadata['population'] = world_city_metadata['population'].astype('float')\nworld_city_metadata['area in sq.km'] = world_city_metadata['area in sq.km'].astype('float')\nworld_city_metadata['density'] = world_city_metadata['population']/world_city_metadata['area in sq.km']\nworld_cities_response = world_cities_response.merge(world_city_metadata,on='Organization',how='left')\n\n\ncities_response.drop(['state_id'],axis=1,inplace=True)\ncities_response = cities_response.merge(city_metadata,on='Organization',how='left')\ncities_response = cities_response.merge(uscities[['state_name','state_id','lat','long','Organization','county_fips']],how='left',on=['Organization','state_id'])\n\n\n\nfavorability = []\nfor org in cities_response['Organization'].unique():\n    q_num='2.2'\n\n    data = cities_response[((cities_response['Question Number']==q_num)&(cities_response['Year Reported to CDP'].isin([2020])))                            \n                  &(cities_response['Organization']==org)].sort_values(by='Year Reported to CDP')[['Organization','Column Name','Response Answer','Row Number']]\\\n    .drop_duplicates(keep='first')\\\n    .sort_values(by=['Column Name','Row Number'])[['Column Name','Response Answer','Row Number']]\n    \n    data = data.pivot(index='Row Number',columns='Column Name',values='Response Answer')\n    \n#     data.pivot()\n    favorability.append(data)\n    \nfavorability = pd.concat(favorability).dropna().reset_index(drop=True)\nfavorability_weightage = pd.pivot_table(favorability[['Level of degree to which factor challenges/supports the adaptive capacity of your city','Factors that affect ability to adapt','Indicate if this factor either supports or challenges the ability to adapt']],index='Factors that affect ability to adapt',\\\n               columns='Indicate if this factor either supports or challenges the ability to adapt',\\\n               values='Level of degree to which factor challenges/supports the adaptive capacity of your city',\\\n               aggfunc='count',fill_value=0.0)\n\nfavorability_weightage.columns = ['Challenges','Supports']\nfavorability_weightage['Challenges'] = favorability_weightage['Challenges']/favorability_weightage['Challenges'].sum()\nfavorability_weightage['Supports'] = favorability_weightage['Supports']/favorability_weightage['Supports'].sum()\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"We will work with US cities having at least 35000 population. Then we will split these cities into 4 categories as per population density. Population and area(converted to sq.km) of the city are collected from city responses in the introduction section.\nBelow, you can see the distribution data for population density(population/Sq.km). We will pick the population density value at 25th,50th and 80th percentile to create four density segments. \n\n**1. Very High density : population >= 3371/sq.km**\n\n**2. High density : population between 1747 and 3371/sq.km**\n\n**3. Medium density : population between 1148 and 1747/sq.km**\n\n**4. Low density : population less than 1148 people/sq.km**\n\n"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"cities_with_35k_pop = list(city_metadata[city_metadata['population']>=35000]['Organization'].values)\ncities_response = cities_response[cities_response['Organization'].isin(cities_with_35k_pop)]\n\n\nenlisted_cities = []\nfor city,state_id in zip(city_metadata['Organization'],city_metadata['state_id']):\n    \n    data = svi_data[svi_data['FIPS'].isin(census_tract_data[(census_tract_data['PlaceName']==city)&(census_tract_data['StateAbbr']==state_id)]['TractFIPS'].unique())][['E_TOTPOP','M_TOTPOP','AREA_SQKM']].sum()\n#     print('City name : ',city)\n    pop_error= ((data['E_TOTPOP']-data['M_TOTPOP'])/city_metadata[city_metadata['Organization']==city]['population'].values[0])\n#     print('population error : ',pop_error)\n    area_error = (data['AREA_SQKM']/city_metadata[city_metadata['Organization']==city]['area in sq.km'].values[0])\n#     print('Area error : ', area_error )\n    pop_upper_margin = 1.17\n    pop_lower_margin = 0.83\n    area_upper_margin = 1.5\n    area_lower_margin = 0.5\n#     print(pop_error)\n    if (pop_lower_margin<pop_error) & (pop_error<pop_upper_margin) & (area_lower_margin<area_error) & (area_error<area_upper_margin):\n        enlisted_cities.append(city)\n        \ncities_response = cities_response[cities_response['Organization'].isin(enlisted_cities)]\n\n\n\nvery_high_density_cities_ = cities_response[cities_response['density']>=3371]['Organization'].unique()\nhigh_density_cities_ = cities_response[(cities_response['density']>=1747)&(cities_response['density']<3371)]['Organization'].unique()\nmedium_density_cities_ = cities_response[(cities_response['density']>=1148)&(cities_response['density']<1747)]['Organization'].unique()\nlow_density_cities_ = cities_response[cities_response['density']<1148]['Organization'].unique()\nvery_high_density_cities_ = list(cities_response[cities_response['Organization'].isin(list(very_high_density_cities_))].groupby(['Organization']).count().sort_values(by='Country',ascending=False).index.values)\nhigh_density_cities_ = list(cities_response[cities_response['Organization'].isin(list(high_density_cities_))].groupby(['Organization']).count().sort_values(by='Country',ascending=False).index.values)\nmedium_density_cities_ = list(cities_response[cities_response['Organization'].isin(list(medium_density_cities_))].groupby(['Organization']).count().sort_values(by='Country',ascending=False).index.values)\nlow_density_cities_ = list(cities_response[cities_response['Organization'].isin(list(low_density_cities_))].groupby(['Organization']).count().sort_values(by='Country',ascending=False).index.values)\n\n\nvery_high_density_cities = []\nhigh_density_cities = []\nmedium_density_cities = []\nlow_density_cities = []\nlimit=10\ncount=0\nfor city in low_density_cities_:\n    if city in census_tract_data['PlaceName'].unique():\n        low_density_cities.append(city)\n        count+=1\n        if count==limit:\n            break\n            \ncount=0\nfor city in medium_density_cities_:\n    if city in census_tract_data['PlaceName'].unique():\n        medium_density_cities.append(city)\n        count+=1\n        if count==limit:\n            break\n            \ncount=0\nfor city in high_density_cities_:\n    if city in census_tract_data['PlaceName'].unique():\n        high_density_cities.append(city)\n        count+=1\n        if count==limit:\n            break\n            \ncount=0\nfor city in very_high_density_cities_:\n    if city in census_tract_data['PlaceName'].unique():\n        very_high_density_cities.append(city)\n        count+=1\n        if count==limit:\n            break   \n            \nall_cities = very_high_density_cities+high_density_cities+medium_density_cities+low_density_cities \n\ncity_metadata['density'].fillna(1.0,inplace=True)\ncity_metadata['normalized_density'] = normalize(city_metadata['density'].values.reshape(1,-1),norm='max')[0]\n\ndef find_category(x):\n    \n    if x>=3371:\n        return 'very high density'\n    if (x<3371)&(x>=1747):\n        return 'high density'  \n    if (x<1747)&(x>=1148):\n        return 'medium density'    \n    else:\n        return 'low density'\n\ncity_metadata['density_category'] = city_metadata['density'].apply(lambda x: find_category(x))\nworld_city_metadata['density_category'] = world_city_metadata['density'].apply(lambda x: find_category(x))\nworld_cities_response = world_cities_response.merge(world_city_metadata,on='Organization',how='left')\n\ntotal_cities_response = pd.DataFrame(cities_response,copy=True)\ntotal_cities_response = total_cities_response.merge(city_metadata,on='Organization',how='left')\n\nq_num = '4.6a'\nrow_name = ['Total Generation of grid-supplied energy','Stationary energy > Residential buildings',\n            'Stationary energy > Commercial buildings & facilities',\n            'Stationary energy > Industrial buildings & facilities',  \n            'Total Transport',\n           'Total Stationary Energy',\n            'Total Waste',\n            'Stationary energy > Agriculture'\n           ]\n\ncol_name = ['Direct emissions (metric tonnes CO2e)']\n\nselected_cities = {}\n\nfor density in ['very high density','high density','medium density','low density']:\n    selected_cities[density] = list(total_cities_response[(total_cities_response['density_category']==density)&\\\n                                                 (total_cities_response['Question Number']==q_num)&\\\n                                                 (total_cities_response['Column Name'].isin(col_name))&\\\n                                                 (total_cities_response['Row Name'].isin(row_name))&\\\n                                                 (total_cities_response['Year Reported to CDP'].isin([2020]))]\\\n                           [['Organization','Response Answer','density_category']]\\\n                           .replace({'Question not applicable':np.nan,'0':np.nan})\\\n                           .dropna()\\\n                           .groupby(['Organization'])\\\n                           .count()\\\n                           .sort_values(by='Organization',ascending=False)\n                           .index.values)\n\ncarbon_intensity_per_kw_consumption = {}\n\nfor density in ['very high density','high density','medium density','low density']:\n    for city in selected_cities[density]:\n#         for row_n in row_name:\n            carbon_intensity_per_kw_consumption[city] = [ total_cities_response[(total_cities_response['density_category']==density)&\\\n                                                     (total_cities_response['Question Number']==q_num)&\\\n                                                     (total_cities_response['Column Name'].isin(col_name))&\\\n                                                     (total_cities_response['Row Name'].isin([row_n]))&\\\n                                                      (total_cities_response['Organization']==city)&\\\n                                                     (total_cities_response['Year Reported to CDP'].isin([2020]))]\\\n                               ['Response Answer'].astype('float').values.sum()\n\n                                                        for row_n in row_name]\n\nghg_from_consumption = pd.DataFrame(carbon_intensity_per_kw_consumption).T.reset_index().rename(columns={'index':'Organization'})\nghg_from_consumption = ghg_from_consumption.merge(city_metadata,on='Organization',how='left')\n# ghg_from_consumption = ghg_from_consumption.merge(uscities[['Organization','state_id']],on='Organization',how='left')\nghg_from_consumption.fillna(0.0,inplace=True)\nghg_from_consumption.rename(columns={0:'Total emissions due to Generation of grid-supplied energy',\n                                     1:'Total emissions from residential buildings',\n                                     2:'Total emissions from commercial buildings',\n                                     3:'Total emissions from industrial buildings',\n                                     4:'Total emissions from transport',\n                                     5:'Total emissions from Stationary Energy',\n                                     6:'Total emissions from Waste',\n                                     7:'Total emissions from agriculture'\n                                     \n                                    },inplace=True)\n\nconsumption_data_residential = pd.read_csv(external_data_dir+'Total Energy Consumption Estimates per Capita by End-Use Sector.csv')[['state_name','Residential_consumption_per_capita']]\nconsumption_data_commercial = pd.read_csv(external_data_dir+'Total Energy Consumption Estimates per Capita by End-Use Sector.csv')[['state_name.1','Commercial_consumption_per_capita']].rename(columns={'state_name.1':'state_name'})\nconsumption_data_industrial = pd.read_csv(external_data_dir+'Total Energy Consumption Estimates per Capita by End-Use Sector.csv')[['state_name.2', 'Industrial_consumption_per_capita']].rename(columns={'state_name.2':'state_name'})\nconsumption_data_transport = pd.read_csv(external_data_dir+'Total Energy Consumption Estimates per Capita by End-Use Sector.csv')[['state_name.3', 'Transportation_consumption_per_capita']].rename(columns={'state_name.3':'state_name'})\nconsumption_data_total= pd.read_csv(external_data_dir+'Total Energy Consumption Estimates per Capita by End-Use Sector.csv')[['state_name.4', 'Total_consumption_per_capita']].rename(columns={'state_name.4':'state_name'})\n\nconsumption_data = consumption_data_residential[['state_name','Residential_consumption_per_capita']]\\\n.merge(consumption_data_commercial[['state_name','Commercial_consumption_per_capita']],on='state_name',how='left')\\\n.merge(consumption_data_industrial[['state_name', 'Industrial_consumption_per_capita']],on='state_name',how='left')\\\n.merge(consumption_data_transport[['state_name', 'Transportation_consumption_per_capita']],on='state_name',how='left')\\\n.merge(consumption_data_total[['state_name', 'Total_consumption_per_capita']],on='state_name',how='left')\n\nghg_from_consumption = ghg_from_consumption.merge(uscities[['Organization','state_name']],on='Organization',how='left')\nghg_from_consumption = ghg_from_consumption.merge(consumption_data,on='state_name')\n\nghg_from_consumption = ghg_from_consumption.sort_values(by=['density_category','Total emissions due to Generation of grid-supplied energy',\n       'Total emissions from residential buildings',\n       'Total emissions from commercial buildings',\n       'Total emissions from industrial buildings',\n       'Total emissions from transport'],ascending=False).reset_index(drop=True)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# ENERGY\n\nWe will start by collecting sector wise GHG emission in every city."},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"ghg_from_consumption.head(10).style.set_caption('Total GHG emission sector wise')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"ghg_from_consumption['Residential building emissions - carbon intensity in metric tonnes of CO2e generated, for each million Btu of energy consumed, per capita'] =\\\n(ghg_from_consumption['Total emissions from residential buildings']/ghg_from_consumption['population'])/ghg_from_consumption['Residential_consumption_per_capita']\n                                                                    \nghg_from_consumption['Commercial building emissions - carbon intensity in metric tonnes of CO2e generated, for each million Btu of energy consumed, per capita'] =\\\n(ghg_from_consumption['Total emissions from commercial buildings']/ghg_from_consumption['population'])/ghg_from_consumption['Commercial_consumption_per_capita']\n                                                                    \nghg_from_consumption['Industrial building emissions - carbon intensity in metric tonnes of CO2e generated, for each million Btu of energy consumed, per capita'] =\\\n(ghg_from_consumption['Total emissions from industrial buildings']/ghg_from_consumption['population'])/ghg_from_consumption['Industrial_consumption_per_capita']\n                                                                    \nghg_from_consumption['Transport emissions - carbon intensity in metric tonnes of CO2e generated, for each million Btu of energy consumed, per capita'] =\\\n(ghg_from_consumption['Total emissions from transport']/ghg_from_consumption['population'])/ghg_from_consumption['Transportation_consumption_per_capita']  \n\nghg_from_consumption['Residential building emissions - carbon intensity in metric tonnes of CO2e, for each million Btu of energy consumed, in every sq.km'] =\\\n(ghg_from_consumption['Total emissions from residential buildings']/ghg_from_consumption['population'])*ghg_from_consumption['density']/ghg_from_consumption['Residential_consumption_per_capita']\n                                                                    \nghg_from_consumption['Commercial building emissions - carbon intensity in metric tonnes of CO2e, for each million Btu of energy consumed, in every sq.km'] =\\\n(ghg_from_consumption['Total emissions from commercial buildings']/ghg_from_consumption['population'])*ghg_from_consumption['density']/ghg_from_consumption['Commercial_consumption_per_capita']\n                                                                    \nghg_from_consumption['Industrial building emissions - carbon intensity in metric tonnes of CO2e, for each million Btu of energy consumed, in every sq.km'] =\\\n(ghg_from_consumption['Total emissions from industrial buildings']/ghg_from_consumption['population'])*ghg_from_consumption['density']/ghg_from_consumption['Industrial_consumption_per_capita']\n                                                                    \nghg_from_consumption['Transport emissions - carbon intensity in metric tonnes of CO2e, for each million Btu of energy consumed, in every sq.km'] =\\\n(ghg_from_consumption['Total emissions from transport']/ghg_from_consumption['population'])*ghg_from_consumption['density']/ghg_from_consumption['Transportation_consumption_per_capita']    \n\nghg_from_consumption_kpis_per_sqkm = ghg_from_consumption.groupby(['density_category'])[['Residential building emissions - carbon intensity in metric tonnes of CO2e, for each million Btu of energy consumed, in every sq.km',\n       'Commercial building emissions - carbon intensity in metric tonnes of CO2e, for each million Btu of energy consumed, in every sq.km',\n       'Transport emissions - carbon intensity in metric tonnes of CO2e, for each million Btu of energy consumed, in every sq.km']].describe(percentiles=[.50]).T\\\n.reset_index()\\\n.rename(columns={'level_0':'KPI','level_1':'stats'})\n\nghg_from_consumption_kpis_per_capita = ghg_from_consumption.groupby(['density_category'])[['Residential building emissions - carbon intensity in metric tonnes of CO2e generated, for each million Btu of energy consumed, per capita',\n       'Commercial building emissions - carbon intensity in metric tonnes of CO2e generated, for each million Btu of energy consumed, per capita',\n       'Transport emissions - carbon intensity in metric tonnes of CO2e generated, for each million Btu of energy consumed, per capita',]].describe(percentiles=[.50]).T\\\n.reset_index()\\\n.rename(columns={'level_0':'KPI','level_1':'stats'})","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# GHG emission/million Btu energy consumption KPI\n\nHere, we will measure sector-wise GHG generation rate against energy consumption in that very sector.IN CDP surveys, GHG generation is captured using metric tonnes of Co2 equivalent. We will gather per capita energy consumption of that sector in the corresponding state, the city belongs to. Then we will try to get an approximate energy consumption in every sq.km of the city and also approximate energy consumption per capita , using population density, for that sector.\n\nPer capita energy consumption data , that we have is in the scale of million Btu(British Thermal Unit).\n\n**Source of data :** We have data on sector-wise per capita energy consumption data for each state in USA and also city based details of energy consumption in corresponding sector from CDP, along with population values.\n\nSo, a KPI like **'Residential building emissions - carbon intensity in metric tonnes of CO2e, for each million Btu of energy consumed, in every sq.km' means** :\n\n**In every sq.km of the city,**\n\n**how much GHG, in metric tonnes of Co2e, is getting generated,**\n\n**for every 1 million Btu of energy consumed,**\n\n**in the residential buildings.**\n\nFor 'industrial building' section , there is only one sample in medium density category. Hence, we summarize the carbon intensity KPI for industrial buildings without it.\n\nDo note, we have considered only direct emissions here.\n\n\n***Carbon  Intensity / Million  Btu Energy Consumption KPI for Sector X = Per capita GHG emission in sector X in metric tonnes of Co2e/Per capita energy consumption in sector X in million Btu.*** \n\n\nWe multiply this with population density to arrive at carbon intensity per sq.km.\n\n## How is this Carbon intensity/million Btu energy consumption KPI helpful?\n\nWhile other metrics like 'total emission' or 'emission per capita' helps in understanding, how much overall emission is or how much emission a person's activities generate on average, it skips a critical aspect of it. Source of energy. Depending on the source of energy, GHG generation for equal amount of energy consumption, can be higher or lower. That's why, we have the term 'clean energy' on fire nowadays. It simply means, GHG generation is far too less, compared to other sources of energy. But how do we measure that? That's where KPIs like these come into picture. \n\nSince we are talking in terms of a city's consumption, Carbon intenity for each million Btu of energy consumed in a particular sector, can tell us about a city's efforts or endorsement towards renewable or clean energy. For equal amount of enery consumed, if a city's per capita carbon intensity is less, that is good indication of having a better sources of energy.\n\n"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"cm = sns.light_palette(\"orangered\", as_cmap=True)\nghg_from_consumption[['density_category', 'Organization', 'Residential building emissions - carbon intensity in metric tonnes of CO2e, for each million Btu of energy consumed, in every sq.km',\n       'Commercial building emissions - carbon intensity in metric tonnes of CO2e, for each million Btu of energy consumed, in every sq.km',\n       'Industrial building emissions - carbon intensity in metric tonnes of CO2e, for each million Btu of energy consumed, in every sq.km',\n       'Transport emissions - carbon intensity in metric tonnes of CO2e, for each million Btu of energy consumed, in every sq.km',\n]].style.background_gradient(cmap=cm,axis=1).set_caption('carbon intensity in metric tonnes of CO2e, for each million Btu of energy consumed, in every sq.km')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"cm = sns.light_palette(\"orangered\", as_cmap=True)\nghg_from_consumption[['density_category', 'Organization', 'Residential building emissions - carbon intensity in metric tonnes of CO2e generated, for each million Btu of energy consumed, per capita',\n       'Commercial building emissions - carbon intensity in metric tonnes of CO2e generated, for each million Btu of energy consumed, per capita',\n       'Industrial building emissions - carbon intensity in metric tonnes of CO2e generated, for each million Btu of energy consumed, per capita',\n       'Transport emissions - carbon intensity in metric tonnes of CO2e generated, for each million Btu of energy consumed, per capita',\n]].style.background_gradient(cmap=cm,axis=1).set_caption('carbon intensity in metric tonnes of CO2e generated, for each million Btu of energy consumed, per capita')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## What can we say about transport section from the above data?\n\nWell, it looks pretty bad. Total emission and per capita emission, in both categories, transport is emerging as a winner, over others. So, it's fair to say, that focusing on transport related emissions, should be a top priority to the cities. We will look into it further, when we check out the transport section of the survey.\n\n## But why are the figures in transport so high, compared to other sectors? \n\nSince, these KPIs relate source of energy and emission, we must point out to the primary source of energy in transport. Fossil fuels, Petroleum or hydrocarbon based fuel. While other sectors can resort to some kind of clean energy, like solar or hydro energy, not enough has been done, to change the picture in transport sector. On top of it, city life and it's economics are hugely dependent on mobility. \n\n\n## Why are both Carbon intensity/million Btu , per capita KPI and per sqkm KPI important? \n\n**Per capita KPI** creates a level playing field for cities from every density segment, thus helping with a **inter-density-segment comparison**. That's why, in the below section, where we do the density-segment based study with per capita KPI, we find cities with medium and low densities are in worse condition, that others. \n\n**Per sq.km KPI** can help us with **intra-density-segment comparison**. Within a particular density segment, how cities are doing compared to each other.\n\n\n\n\n\nLet's look at the below study, where we compare how cities from different density segments are doing in each of these KPIs.\n\nPer sq.km KPI tells us nothing much in this summary,as cities with higher density will be above the others and that is what reflects in the first table, below. However, it can tell you how San Fransisco is doing compared to another highly dense city like New York or Washington."},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"ghg_from_consumption_kpis_per_sqkm[ghg_from_consumption_kpis_per_sqkm['stats'].isin(['mean','50%'])]\\\n.style.highlight_max(color = 'orangered', axis = 1)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"However, per capita KPIs show an interesting picture.\n\n1. **Low density cities are doing worst in transport section.** Could this be because of lack of mass transport systems? \n\n2. In commercial buildng section, low density cities are far behind, in a good way and understandably so.\n3. Whereas, in residential building section, carbon intensity/million Btu energy consumption per capita is almost equal for all. \n\n**4. 'Carbon intensity/energy consumption Per capita KPIs' also show that very high category density-segment is doing fairly better in all sectors, except industrial section, where it's understandably highest.** "},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"ghg_from_consumption_kpis_per_capita[ghg_from_consumption_kpis_per_capita['stats'].isin(['mean','50%'])]\\\n.style.highlight_max(color = 'orangered', axis = 1)\\\n.highlight_min(color = 'lightgreen', axis = 1)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"# since there is only 1 value from medium density category, we are not considering it over here\n\nghg_from_consumption_industrial_kpi_per_sqkm = ghg_from_consumption[(ghg_from_consumption['Industrial building emissions - carbon intensity in metric tonnes of CO2e, for each million Btu of energy consumed, in every sq.km']>0)&\\\n                                                          (ghg_from_consumption['density_category']!='medium density')]\\\n.groupby(['density_category'])[['Industrial building emissions - carbon intensity in metric tonnes of CO2e, for each million Btu of energy consumed, in every sq.km']]\\\n.describe(percentiles=[.50]).T\\\n.reset_index()\\\n.rename(columns={'level_0':'KPI','level_1':'stats'})\n\nghg_from_consumption_industrial_kpi_per_capita = ghg_from_consumption[(ghg_from_consumption['Industrial building emissions - carbon intensity in metric tonnes of CO2e generated, for each million Btu of energy consumed, per capita']>0)&\\\n                                                          (ghg_from_consumption['density_category']!='medium density')]\\\n.groupby(['density_category'])[['Industrial building emissions - carbon intensity in metric tonnes of CO2e generated, for each million Btu of energy consumed, per capita']]\\\n.describe(percentiles=[.50]).T\\\n.reset_index()\\\n.rename(columns={'level_0':'KPI','level_1':'stats'})\n\n\nghg_from_consumption_industrial_kpi_per_sqkm[ghg_from_consumption_industrial_kpi_per_sqkm['stats'].isin(['mean','50%'])]\\\n.style.highlight_max(color = 'orangered', axis = 1)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"ghg_from_consumption_industrial_kpi_per_capita[ghg_from_consumption_industrial_kpi_per_capita['stats'].isin(['mean','50%'])]\\\n.style.highlight_max(color = 'orangered', axis = 1)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"q_num = '8.1'\ncol_name = ['Coal', \n'Gas', \n'Oil', \n'Nuclear', \n'Hydro', \n'Biomass', \n'Wind', \n'Geothermal', \n'Solar',\n'Other sources'            \n]\n\nrow_name = ['Electricity source','Percent']\n\nselected_cities = {}\n\nfor density in ['very high density','high density','medium density','low density']:\n    selected_cities[density] = list(total_cities_response[(total_cities_response['density_category']==density)&\\\n                                                 (total_cities_response['Question Number']==q_num)&\\\n                                                 (total_cities_response['Column Name'].isin(col_name))&\\\n                                                 (total_cities_response['Row Name'].isin(row_name))&\\\n                                                 (total_cities_response['Year Reported to CDP'].isin([2020]))]\\\n                           [['Organization','Response Answer','density_category']]\\\n                           .replace({'Question not applicable':np.nan,'0':np.nan})\\\n                           .dropna()\\\n                           .groupby(['Organization'])\\\n                           .count()\\\n                           .sort_values(by='Organization',ascending=False)\n                           .index.values)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"\n## Direct GHG emission per capita, excluding generation because of grid-supplied energy\n\n= total direct ghg emission by city/ city population, it's a widely used KPI and will be helpful to CDP as well.\n\n## Direct GHG emission per sq.km\n\n= total direct ghg emission by city/ city area.\n\nGHG emissions per capita, when looked at from density point-of-view, shows medium density cities are ahead of the rest and low density cities are also at par with it.\n"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"q_num = '4.6a'\nrow_name = ['Total Emissions (excluding generation of grid-supplied energy)'\n           ]\n\ncol_name = ['Direct emissions (metric tonnes CO2e)']\n\n\nscope1_scope3_emissions = total_cities_response[\n#     (total_cities_response['density_category']==density)&\\\n                                                 (total_cities_response['Question Number']==q_num)&\\\n                                                 (total_cities_response['Column Name'].isin(col_name))&\\\n                                                 (total_cities_response['Row Name'].isin(row_name))&\\\n                                                 (total_cities_response['Year Reported to CDP'].isin([2020]))]\\\n                           [['Organization','Response Answer','density_category','Column Name','Row Name']]\\\n                           .replace({'Question not applicable':np.nan,'0':np.nan})\\\n                           .dropna()\\\n                           .sort_values(by='Organization').pivot(index='Organization',columns='Row Name',values='Response Answer')\\\n                           .dropna().reset_index() \n\nscope1_scope3_emissions = scope1_scope3_emissions.merge(city_metadata,on='Organization',how='left')\nscope1_scope3_emissions['Direct emissions per capita(metric tonnes CO2e),excluding generation of grid-supplied energy)'] = scope1_scope3_emissions['Total Emissions (excluding generation of grid-supplied energy)'].astype('float')/scope1_scope3_emissions['population'].astype('float')\nscope1_scope3_emissions['Direct emissions per sq.km(metric tonnes CO2e),excluding generation of grid-supplied energy)'] = scope1_scope3_emissions['Total Emissions (excluding generation of grid-supplied energy)'].astype('float')/scope1_scope3_emissions['area in sq.km'].astype('float')\n# scope1_scope3_emissions['Total Generation of grid-supplied energy per capita'] = scope1_scope3_emissions['Total Generation of grid-supplied energy'].astype('float')/scope1_scope3_emissions['population'].astype('float')\n\n\nscope1_scope3_emissions.sort_values(by='Direct emissions per capita(metric tonnes CO2e),excluding generation of grid-supplied energy)',ascending=False).head(10)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"scope1_scope3_emissions.groupby(['density_category'])['Direct emissions per capita(metric tonnes CO2e),excluding generation of grid-supplied energy)'].describe(percentiles=[.50])\\\n.style.apply(lambda x: ['None','None','None','None','background-color: lightyellow','None'],axis=1)\\\n.set_caption('Direct emissions per capita(metric tonnes CO2e),excluding generation of grid-supplied energy)')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## How can this total GHG emissions per capita help?\n\nLet's look at the 'Asthma' data, we have from CDC's 500 city census dataset.Between GHG emissions per capita and percentage of asthma patients in the city population, we have a positive correlation, presented by the trendline. It's not a conclusive evidence for sure. However,below, we can observe some odd and coherent facts in the data, coming from two different sources.\n\nFor example, **city of Cleveland, at the top right corner of the graph, is having an unusually high GHG emissions per capita. 22.12 metric tonnes Co2e. This is what we have from CDP data.**\n\n**At the same time, from CDC's census data, we can gather that, 12.5% of Cleveland's population suffer from asthma. That's 3.4% higher than the median of 9.1% and is a 95th percentile value. That's second highest among the cities who have responded with GHG emissions data to CDP.**\n\nSame goes for Providence,Lousville,Pittsburgh.\n\nSo, GHG emission per capita could be an indicator of lot of underlying and brewing problems and health hazards."},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"scope1_scope3_emissions['estimated percentage of asthma patients'] = 0.0\n\nfor idx in scope1_scope3_emissions.index.values:\n    org = scope1_scope3_emissions['Organization'].iloc[idx]\n    state_id = scope1_scope3_emissions['state_id'].iloc[idx]\n    perc_asthma_patients = (census_tract_data[(census_tract_data['PlaceName']==org)&(census_tract_data['StateAbbr']==state_id)]['CASTHMA_CrudePrev'].values[0]*\\\n                                     svi_data[svi_data['FIPS'].isin(census_tract_data[(census_tract_data['PlaceName']==org)&(census_tract_data['StateAbbr']==state_id)]['TractFIPS'].unique())]['E_TOTPOP']).values.sum()/svi_data[svi_data['FIPS'].isin(census_tract_data[(census_tract_data['PlaceName']==org)&(census_tract_data['StateAbbr']==state_id)]['TractFIPS'].unique())]['E_TOTPOP'].sum()\n    scope1_scope3_emissions.at[idx,'estimated percentage of asthma patients']=perc_asthma_patients\n\n\nscope1_scope3_emissions[['estimated percentage of asthma patients','Direct emissions per capita(metric tonnes CO2e),excluding generation of grid-supplied energy)']].describe(percentiles=[.5,.75,.95]).drop(['count','min','max','std'])\\\n.style.set_caption('Percentage of asthma patients vs Direct emissions per capita')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"fig = px.scatter(scope1_scope3_emissions,y='estimated percentage of asthma patients',\n    x='Direct emissions per capita(metric tonnes CO2e),excluding generation of grid-supplied energy)',\n                 text='Organization',\n    trendline=\"ols\")\n\nfig.update_layout(\n        template=\"plotly_dark\",\n        title = dict(text='Percentage of asthma affected population and GHG emissions per capita',x=0.5,y=.97),\n        height=300,\n        width=1000,\n        font_color=\"rgb(199,233,180)\"\n        )\n\nfig.show()\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Furthermore, this estimated percentage of asthma affected population is barely positively related with population, has good degree of positive correlation with direct emissions per capita. But it is negatively related with density! "},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"scope1_scope3_emissions[['estimated percentage of asthma patients','population','density','Direct emissions per capita(metric tonnes CO2e),excluding generation of grid-supplied energy)']].corr().iloc[0:1]","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Intuitively, it may seem population in higher density areas are more susceptible to asthma. However,statistics is showing, lower density areas are expected to have slightly greater percentage of affected people , than rest. This actually is in line with per capita GHG emissions data in CDP, we saw previously, where low density cities were showing quite higher per capita GHG emissions. Although remember, these two data are coming from two different sources, yet pointing out a relatable information.  \n\n### Estimated percentage of asthma patients in cities - density segment wise"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"scope1_scope3_emissions.groupby(['density_category'])['estimated percentage of asthma patients'].describe()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Here are the details of the low density cities for your reference."},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"scope1_scope3_emissions[scope1_scope3_emissions['density_category']=='low density']","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Source of Energy\n\nNow that we have looked at the GHG emission from different sectors and it's relation with energy consumption,\nlet's look into the source of energy of the cities, especially, in the renewable energy section.\n\n## Renewable Energy Percentage\n**'Renewable Energy Percentage' can be a good KPI to understand a city's efforts to address GHG emission.**\nBelow summary, based on density-segments, shows **very high density cities are ahead** of cities from other segment by a good margin. ***This explains why in carbon intensity/energy consumption per capita KPIs, very high density segment was doing better than rest.***\n\n\nOn the other hand, in cities from ***low and medium density segments, use of gas and oil are considerably higher, compared to high density and very high density cities.***\nThis explains why, in **transport section's carbon intensity/energy consumption per capita KPI, low density segment showed much higher emission.**\n\nAt the same time, medium density cities on an average use 28% of energy generated from renewable sources, which is a little higher than half  of what high and very high density segments are using.\n\n"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"q_num = '8.1'\ncol_name = ['Coal', \n'Gas', \n'Oil', \n'Nuclear', \n'Hydro', \n'Biomass', \n'Wind', \n'Geothermal', \n'Solar',\n'Other sources'            \n]\n\nrow_name = ['Electricity source','Percent']\n\nsource_of_energy = total_cities_response[(total_cities_response['Question Number']==q_num)&\\\n                      (total_cities_response['Column Name'].isin(col_name))&\\\n                      (total_cities_response['Row Name'].isin(row_name))&\\\n                      (total_cities_response['Year Reported to CDP'].isin([2020]))]\\\n                           [['Organization','Response Answer','Row Name','Column Name']].fillna(0.0).pivot(index='Organization',values='Response Answer',columns='Column Name')\n\nfor col in source_of_energy.columns.values:\n        source_of_energy[col] = source_of_energy[col].astype('float')\n\n        \nsource_of_energy.reset_index(inplace=True)\n\nsource_of_energy.drop([6,32,40,45,48],inplace=True)\n\n\nsource_of_energy = source_of_energy.merge(city_metadata,on='Organization',how='left')\n\nsource_of_energy['Renewable Energy percentage'] = source_of_energy[['Biomass','Geothermal','Hydro','Nuclear','Solar','Wind']].sum(axis=1)\n\nsource_of_energy[source_of_energy['Renewable Energy percentage']!=0.0].groupby(['density_category'])[['Biomass', 'Geothermal', 'Hydro',\n       'Nuclear', 'Solar', 'Wind','Other sources','Renewable Energy percentage']].mean()\\\n.style.highlight_max(color = 'lightgreen', axis = 0)\\\n.highlight_min(color = 'orangered', axis = 0).set_caption('Renewable energy percentage in total source of energy')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"source_of_energy[source_of_energy['Renewable Energy percentage']!=0.0].groupby(['density_category'])[['Coal', 'Gas','Oil']].mean()\\\n.style.highlight_min(color = 'lightgreen', axis = 0)\\\n.highlight_max(color = 'orangered', axis = 0).set_caption('Non-renewable Energy percentage across density-segments')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"source_of_energy.head(10).style.set_caption('Source of energy for cities')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Evaluation of Carbon Intensity/energy consumption per capita KPIs\n### Source of energy generation stats vs GHG generation from energy consumption stats \n\nLet's look at the individual city performances. In the table below, we will find values for 'Salt Lake City' and 'San Francisco'.However, below table shows, how the sector-wise carbon intensity/energy consumption, per capita KPI looks like, along with 'Renewable Energy percentage'. \n\n**A gap of 65% in renewable energy percentage of the whole enrgy production is reflecting in Carbon Intensity/energy consumption (CI/EC from here on) per capita KPIs. If you look at the individual sources of energy, you will further notice, that highest source of energy production for Salt lake City is coal, making up for 59%.On the other hand, San Francisco gets 18% of it's energy from nuclear sources.**\n\n**This makes San Francisco a far more energy efficient city than Salt Lake city, inspite of having 10x population density.** ****"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"city_metadata[city_metadata['Organization'].isin(['Salt Lake City','San Francisco'])]","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"ghg_from_consumption[ghg_from_consumption['Organization'].isin(['Salt Lake City','San Francisco'])][[ 'Organization', 'Residential building emissions - carbon intensity in metric tonnes of CO2e generated, for each million Btu of energy consumed, per capita',\n       'Commercial building emissions - carbon intensity in metric tonnes of CO2e generated, for each million Btu of energy consumed, per capita',\n       'Transport emissions - carbon intensity in metric tonnes of CO2e generated, for each million Btu of energy consumed, per capita',\n]].set_index('Organization').T\\\n.style.highlight_min(color = 'lightgreen', axis = 1)\\\n","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"source_of_energy[source_of_energy['Organization'].isin(['Salt Lake City','San Francisco'])][['Organization','Renewable Energy percentage','Biomass', 'Geothermal', 'Hydro',\n       'Nuclear', 'Other sources', 'Solar', 'Wind']].set_index('Organization').T\\\n.style.highlight_max(color = 'lightgreen', axis = 1)\\\n","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"source_of_energy[source_of_energy['Organization'].isin(['Salt Lake City','San Francisco'])][['Organization','Coal','Oil','Gas']].set_index('Organization').T\\\n.style.highlight_max(color = 'orangered', axis = 1)\\","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Let's do some PCA on the energy data and CI/EC per capita data, we have gathered till now and make a scatter plot based on principal components.\n\n**For 1st principal component, features that explain variance in data, relate to usage of renewable energy, with maximum variance being explained by usage of Geothermal energy.**\n\n**For 2nd principal component, it is usage of fossil fuel energy, with maximum variance being explained by usage of oil.**\n\nThis puts Seattle and Providence in two opposite corners, with Seattle being more energy efficient, inspite of both being very high density cities. It also create a cluster of cities with San Francisco,Oakland,Fremont etc., which are higher in renewable enregy usage and have less reliance on oil and gas."},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"source_of_energy[source_of_energy['Organization'].isin(['Seattle','Providence','San Francisco','Salt Lake City'])][['Organization','Renewable Energy percentage','Biomass', 'Geothermal', 'Hydro',\n       'Nuclear', 'Other sources', 'Solar', 'Wind','Coal','Oil','Gas']].set_index('Organization').T\\\n.style.highlight_max(color = 'lightgreen', axis = 1)\\\n.set_caption('Energy source in percentages across some of the cities')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"ghg_from_consumption","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"from sklearn.decomposition import PCA\n\npca = PCA(n_components=2)\n\ndata_to_cluster = ghg_from_consumption[[ 'Organization', 'Residential building emissions - carbon intensity in metric tonnes of CO2e generated, for each million Btu of energy consumed, per capita',\n       'Commercial building emissions - carbon intensity in metric tonnes of CO2e generated, for each million Btu of energy consumed, per capita',\n       'Transport emissions - carbon intensity in metric tonnes of CO2e generated, for each million Btu of energy consumed, per capita',\n]]\\\n.merge(source_of_energy[['Organization','Renewable Energy percentage','Biomass', 'Coal', 'Gas', 'Geothermal', 'Hydro',\n       'Nuclear', 'Oil', 'Other sources', 'Solar', 'Wind']],on='Organization',how='left').set_index('Organization').fillna(0.0)\n\n\ndata_to_cluster.drop(['New York','Santa Monica'],inplace=True)\n\npca_values = pd.DataFrame(pca.fit_transform(normalize(data_to_cluster.values,axis=0)),columns=['pca_1','pca_2'])\npca_values['Organization'] = ['Long Beach', 'Cambridge', 'San Francisco', 'Chicago',\n       'Philadelphia', 'Rochester', 'Washington', 'Somerville', 'Phoenix',\n       'San Antonio', 'Grand Rapids', 'Cincinnati', 'Ann Arbor',\n       'Lakewood', 'New Bedford', 'Louisville', 'West Palm Beach',\n       'Anchorage', 'Nashville', 'Salt Lake City', 'Carmel', 'Fremont',\n       'Hayward', 'Los Angeles', 'Oakland', 'Cleveland', 'Pittsburgh',\n       'Denver', 'Boston', 'Minneapolis', 'Seattle', 'Providence',\n       'San Leandro', 'Alameda']\n\n\nfig = px.scatter(pca_values,y='pca_1',\n    x='pca_2',\n    text='Organization',         \n    )\n\n\nfig.update_layout(\n        template=\"plotly_dark\",\n        title = dict(text='PCA of cities by energy source data and GHG generation from per capita energy consumption data',x=0.5,y=.97),\n        xaxis_title=\"Principal component representing - Usage of oil & other fossil fuel energy - high to low -->\",\n        yaxis_title=\"Principal component representing - Usage of renewable energy\",    \n        height=500,\n        width=1200,\n        font_color=\"rgb(199,233,180)\",\n        shapes=[\n                dict(\n                  type= 'line',\n                  yref= 'paper', y0= 0, y1= 0,\n                  xref= 'x', x0= 0, x1= 0,\n                 \n                )\n            ]\n        )\n\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"This is how it looks for all other cities and Alameda city's official site kind of confirms, what they have reported below."},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"source_of_energy.sort_values(by='Renewable Energy percentage',ascending=False)[['density_category','Organization',\n                                       'Renewable Energy percentage','Biomass', 'Coal', 'Gas', 'Geothermal', 'Hydro',\n       'Nuclear', 'Oil', 'Other sources', 'Solar', 'Wind' ]].head(15)\\\n.style.set_caption('Source of energy for cities')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"A simple heatmap based on correlation between CI/EC per capita KPIs and percentage of energy sources, shows, how negatively correlated they are with renewable sources."},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"ghg_consumption_renewability = ghg_from_consumption[['Organization','Residential building emissions - carbon intensity in metric tonnes of CO2e generated, for each million Btu of energy consumed, per capita',\n       'Commercial building emissions - carbon intensity in metric tonnes of CO2e generated, for each million Btu of energy consumed, per capita',\n       'Industrial building emissions - carbon intensity in metric tonnes of CO2e generated, for each million Btu of energy consumed, per capita',\n       'Transport emissions - carbon intensity in metric tonnes of CO2e generated, for each million Btu of energy consumed, per capita',\n]]\\\n.merge(source_of_energy[['Organization','Renewable Energy percentage','Biomass', 'Coal', 'Gas', 'Geothermal', 'Hydro',\n       'Nuclear', 'Oil', 'Other sources', 'Solar', 'Wind']],on='Organization',how='left')\n\n\nplt.figure(figsize=(10,10))\nsns.heatmap(ghg_consumption_renewability[['Residential building emissions - carbon intensity in metric tonnes of CO2e generated, for each million Btu of energy consumed, per capita',\n       'Commercial building emissions - carbon intensity in metric tonnes of CO2e generated, for each million Btu of energy consumed, per capita',\n       'Industrial building emissions - carbon intensity in metric tonnes of CO2e generated, for each million Btu of energy consumed, per capita',\n       'Transport emissions - carbon intensity in metric tonnes of CO2e generated, for each million Btu of energy consumed, per capita',\n        'Renewable Energy percentage','Coal','Gas','Solar','Hydro'\n                             ]].corr())","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Now that we have worked with source of energy data of the cities. We will extend this work to water usage aspect of this. \n\n\n# Water usage in energy plants\n\nBefore we dive in to prepare the KPIs, let's understand the context first. Below picture from USGS shows water usage across different sectors, as per 2015 water census data. This shows the volume of water withdrawn and delivered to power plants for cooling purposes. \n\n![wss-wuse-pipe-diagram-2015.png](https://prd-wret.s3.us-west-2.amazonaws.com/assets/palladium/production/s3fs-public/styles/full_width/public/thumbnails/image/wss-wuse-pipe-diagram-2015.png)\n\n## Why is this crucial?\n\nAbove picture shows that 40% of the entire water withdrawn in USA goes to energy plants, mostly for cooling needs. This tells us that , how large the water requirement is for this sector.\n\nApart from surface water, a good share of this water is groundwater as well. Cities are crucially dependent on groundwaters. Also, sources of surface water are also often same for cities and power plants. Huge water withdrawal from these sources can actually become crucial in future, where groundwater for most of the cities, around the world are depleting fast and other surface based sources are also drying up. \n\n## What can we gain out of it?\n\nCooling needs for energy plants varies,based on two factors.\n1. Fuel used.\n2. Reusability of water in the energy plant.\n3. Size of the plant.\n\n\nIn below section we will see, how renewable energy sources, have less water needs and if plants are replaced with renewable sources or if cities switches to a renewable energy source, it can result in sigificant amount of water saving, which can be utilized to meet other needs in future.\n\nSo, we will try to come up with a KPI, which can estimate, how much excess water is being used from surrounding water sources, that can be saved if energy generation is switched to a renewable source.\n\n## How do we do it?\n\nCities are powered by plants, that differ in their water requirement. We will first calculate, 'water withdrawal intensity (m3/MWh)'. This will tell us, how much cubic meters of water is withdrawn for the plant to produce 1 megawatt-hour of power. \n\nWe have the ratio of non-renewable energy source usage from CDP data, that we used in the previous section.\nWe multiply this ratio with water withdrawal intensity to get, for 1 megawatt-hour of power production, how much water is being used for non-renewable source based power production. This amount of water withdrawal can be reduced. We convert this to litres/MWh and name this - \n\n## Estimated reducable water withdrawal (cubic meters/MWh) KPI\n\n## What else can be done?\n\nWe will further check from the water census data of 2015, how much data is available per capita at each city. Here, the census data is at county level. We will fetch only few of them and try to understand , by how much the above reducable water can increase the water availability."},{"metadata":{},"cell_type":"markdown","source":"Below we can see, how based on source of energy, **water withdrawal intensity, measured in cubic meter of water withdrawn for every megawatts/h of power production,** varies considerably. Coal, oil and gas based power generation shows considerably higher water requirement.**Especially for petroleum based power generation, it's much higher than rest. On the other hand, for biomass, solar, nuclear, water requirement is considerably lower.** For wind based power generation, there was no data for water withdrawal. It perhaps is safe to assume that, there is no requirement for water. If so,it can be most helpful for coastal cities and we have lot of them.\n\n"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"def change_format(x):\n#     print(x)\n    return pd.to_numeric(x.replace(' ','').replace(',',''))\n\n\ncooling_summary['Water Withdrawal Volume (Million Gallons)'] = cooling_summary['Water Withdrawal Volume (Million Gallons)'].apply(lambda x: change_format(x))\ncooling_summary['Net Generation (MWh)'] = cooling_summary['Net Generation from Steam Turbines (MWh)'].apply(lambda x: change_format(x))\n\nwater_withdrawal_data = cooling_summary.groupby(['State','Generator Primary Technology'])[['Water Withdrawal Volume (Million Gallons)','Net Generation (MWh)']].sum().reset_index()\nwater_withdrawal_data['water withdrawal intensity (m3/MWh)'] = 3785.41178*(water_withdrawal_data['Water Withdrawal Volume (Million Gallons)']/water_withdrawal_data['Net Generation (MWh)']).replace(np.inf,0.0)\n\ntemp = water_withdrawal_data.groupby(['Generator Primary Technology'])['water withdrawal intensity (m3/MWh)'].describe(percentiles=[0.5]).reset_index().rename(columns={'50%':'water withdrawal intensity (m3/MWh)'})\ntemp[['Generator Primary Technology','water withdrawal intensity (m3/MWh)']].style.set_caption('Median water withdrawal intensity (m3/MWh) for different energy sources').background_gradient(cmap=cm,axis=0)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"water_withdrawal_data.head(15).style.set_caption('Water withdrawal data - state & plant wise')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"This is how the values for KPI - 'Estimated reducable water withdrawal (m3/MWh)' looks.\nFor Cambridge , we have a value of 27367.66 litres/MWh. This means for every MWh of power the city uses currently, maximum 27367.66 litres of water saving can be done by switching to renewable energy sources. We used the word maximum here, considering the fact that renewable energy sources would also have certain water requirement."},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"# estimate reduction in water withdrawal for cooling needs of energy plant\n\n# get the state and source of non-renewable energy : coal,gas,oil and the share of production  \nsource_of_energy['Estimated reducable water withdrawal (m3/MWh)'] = 0.0\nsource_of_energy['Net water withdrawal intensity (m3/MWh)'] = 0.0\n\nfor idx in source_of_energy.index.values:\n    \n    net_water_withdrawal_intensity_per_MWh = 0.0\n    org = source_of_energy['Organization'].iloc[idx]\n    state_id = source_of_energy['state_id'].iloc[idx]\n    power_ratio = source_of_energy[['Coal','Oil','Gas','Solar','Nuclear','Geothermal','Biomass','Other sources']].iloc[idx]/source_of_energy[['Coal','Oil','Gas','Solar','Nuclear','Geothermal','Biomass','Other sources']].iloc[idx].sum()\n\n    net_water_withdrawal_intensity_per_MWh = 3785.41178*water_withdrawal_data[water_withdrawal_data['State']==state_id]['Water Withdrawal Volume (Million Gallons)'].sum()/water_withdrawal_data[water_withdrawal_data['State']==state_id]['Net Generation (MWh)'].sum()\n    net_water_withdrawal_intensity_per_MWh = 0.0 if np.isnan(net_water_withdrawal_intensity_per_MWh) else net_water_withdrawal_intensity_per_MWh\n    \n    net_water_withdrawal_intensity_per_MWh_for_coal = net_water_withdrawal_intensity_per_MWh*power_ratio['Coal']\n    net_water_withdrawal_intensity_per_MWh_for_gas = net_water_withdrawal_intensity_per_MWh*power_ratio['Gas']\n    net_water_withdrawal_intensity_per_MWh_for_oil = net_water_withdrawal_intensity_per_MWh*power_ratio['Oil']\n    \n    source_of_energy.at[idx,'Net water withdrawal intensity (m3/MWh)'] = net_water_withdrawal_intensity_per_MWh\n    source_of_energy.at[idx,'Estimated reducable water withdrawal (m3/MWh)'] = net_water_withdrawal_intensity_per_MWh_for_coal+net_water_withdrawal_intensity_per_MWh_for_oil+net_water_withdrawal_intensity_per_MWh_for_gas\n\nsource_of_energy['Estimated reducable water withdrawal (litres/MWh)'] = 0.0    \nsource_of_energy['Estimated reducable water withdrawal (litres/MWh)'] = source_of_energy['Estimated reducable water withdrawal (m3/MWh)']*1000\n\nsource_of_energy[source_of_energy['Estimated reducable water withdrawal (litres/MWh)']>0][['Organization', \n       'state_id','Oil','Coal','Gas',\n       'density_category', 'Renewable Energy percentage',\n       'Estimated reducable water withdrawal (m3/MWh)',\n       'Net water withdrawal intensity (m3/MWh)',\n       'Estimated reducable water withdrawal (litres/MWh)',\n       ]].head(8)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Now, to understand , how this KPI can help, we will look at water census data of 2015. This census data is at county level and hence it will give us more accurate values for cities per capita water availability back in 2015. Population value of 2015 is included in census data."},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"\n\ndef get_name(x):\n#     print(x)\n    x = x.lower()\n    for w in x.split():\n        if w in ['municipality','&','municipio','district','council','city','county','govern','commune','of','the','census','area','borough',]:\n            x = x.replace(w,'')\n            \n    x = x.rstrip().lstrip()\n    try:\n        return ' '.join([w[0].upper()+w[1:] for w in x.split(' ')])\n    except Exception as e:\n        return x\n\nwater_usage_data['COUNTY'] = water_usage_data['COUNTY'].apply(lambda x: get_name(x))\nwater_usage_data.rename(columns={'COUNTY':'Organization','STATE':'state_id'},inplace=True)\n\nsource_of_energy = source_of_energy.merge(water_usage_data[['Organization','state_id','TP-TotPop','DO-WDelv ','PS-Wtotl','IN-Wtotl','LI-WFrTo']],on=['Organization','state_id'],how='left').fillna(0.0)\nsource_of_energy['Approx. annual water availability per capita 2015 (m3)'] = (((3785.41178*(source_of_energy['DO-WDelv ']+source_of_energy['PS-Wtotl']+source_of_energy['IN-Wtotl']+source_of_energy['LI-WFrTo']))/(source_of_energy['TP-TotPop']*1000))*365).fillna(0.0)\nwater_availability = source_of_energy[source_of_energy['Approx. annual water availability per capita 2015 (m3)']>0.0][['Organization','state_id','density_category','Approx. annual water availability per capita 2015 (m3)','Estimated reducable water withdrawal (m3/MWh)']]\nwater_availability[['Organization','state_id','density_category','Approx. annual water availability per capita 2015 (m3)']]\\\n.style.set_caption('Annual water avilability as per water census data 2015')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Let's look at the water issues reported by cities along with annual water availability and estimated reducable water volume. We can see almost all cities are reporting increased water stress and Dallas and Sacremento have reported drought situations.\n\nIn the next table an overall summary of reported water related issues for US cities have been shown in every density segment."},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"q_num='14.2a'\ncol_name = ['Water security risk drivers']\n\ntemp = pd.pivot_table(total_cities_response[(total_cities_response['Organization'].isin(['Alameda', 'Anchorage', 'Austin', 'Dallas', 'Denver', 'Honolulu',\n       'Los Angeles', 'Philadelphia', 'Providence', 'Sacramento',\n       'San Francisco']))&\\\n                                                 (total_cities_response['Question Number']==q_num)&\\\n                                                 (total_cities_response['Column Name'].isin(col_name))&\\\n#                                                  (total_cities_response['Row Name'].isin(row_name))&\\\n                                                 (total_cities_response['Year Reported to CDP'].isin([2020]))]\\\n                           [['Organization','Response Answer','density_category']],\nindex='Organization',columns='Response Answer',aggfunc='count',fill_value=0)\\\n.reset_index()\ntemp.columns = ['Organization','Declining water quality',\n        'Drought',\n        'Energy supply issues',\n        'Inadequate or ageing water supply infrastructure',\n        'Increased water demand',\n        'Increased water stress',\n        'Question not applicable']\n\ntemp.merge(source_of_energy[source_of_energy['Approx. annual water availability per capita 2015 (m3)']>0.0][['Organization','state_id','Approx. annual water availability per capita 2015 (m3)','Estimated reducable water withdrawal (m3/MWh)']],on=['Organization'],how='left')\\\n.style.set_caption('Water related issues present in corresponding city')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"temp2 = total_cities_response[\n                                                 (total_cities_response['Question Number']==q_num)&\\\n                                                 (total_cities_response['Column Name'].isin(col_name))&\\\n#                                                  (total_cities_response['Row Name'].isin(row_name))&\\\n                                                 (total_cities_response['Year Reported to CDP'].isin([2020]))]\\\n                           [['Response Answer','density_category']]\n\n# temp2 = pd.crosstab(temp2['Response Answer'],temp2['density_category']).style.set_caption('test')\npd.crosstab(temp2['Response Answer'],temp2['density_category'])\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"We will try to see now, how the previously calculated 'Estimated reducable water withdrawal (m3/MWh)' KPI can help. For Dallas, we have the following values:\n\n**population** : 1345047.0\n\n**Annual energy consumption** : 720000 MWh (source : offcial website of City of Dallas Office of Environmental Quality & Sustainability http://greendallas.net/energy/city-energy/#:~:text=The%20City%20of%20Dallas%20uses,year%20(Source%3A%20EPA) )\n\n\n**Estimated reducable water withdrawal (m3/MWh)** : 104.259507\n\n**Approx. annual water availability per capita 2015 (m3)** : 303.392441\n\nSo, **maximum increment in per capita water availability, by reducing water requirement** is :\n\n(Annual energy consumption * Estimated reducable water withdrawal (m3/MWh)) / population = 55.81\n\n**How much increment is this, on top of 2015's water availability value, as a  percentage**: (55.81/303.392441) * 100 = 18.4% \n\nThis has not only potential to solve water availability problems of the cities to good extent, it can also help cities to recover depleting groundwater levels even. Most importantly, All this can be done. only by changing our way of energy generation.\n\n**But do note, in this discussion, we are not looking at the water stress level in and around the city, as the approach towards water usage cannot be reactive anymore. Our cities should be more proactive and water conserving in action, instead of looking at how much water is available in natural reserves.**"},{"metadata":{},"cell_type":"markdown","source":"## Long-term Environment Risk Indices (LERI)\n\nSo, now we have some KPIs with us on cities, that tells us the following :\n\n1. Residential building emissions - carbon intensity in metric tonnes of CO2e generated, for each million Btu of energy consumed, per capita\n2. Commercial building emissions - carbon intensity in metric tonnes of CO2e generated, for each million Btu of energy consumed, per capita\n3. Transport emissions - carbon intensity in metric tonnes of CO2e generated, for each million Btu of energy consumed, per capita\n4. Direct emissions per capita(metric tonnes CO2e),excluding generation of grid-supplied energy) \n5. Estimated reducable water withdrawal (m3/MWh)\n\nEmissions KPIs tell us, how bad is the energy emission scenario in the city. Reducable water withdrawal KPI tells us, how much excess water is being withdrawn currently.\n\nSo, can we combine these informations into one index to present the overall scenario, that will tell, how adversely the air of the cities and water reserves are being affected? Since, these damages to environment are unlikely to be resolved in shorter period of time and can have long-term effects on environment, we recognize this as Long Term Environment Risk.\n\nFor both, emissions KPI and reducable water KPI, higher value is worse.\n\nSo, We create 'Long-term Environment Risk  1' in following way : \n\nNormalize the variable and then\n\n**<u>Long-term Environment Risk  1  = Square root of ((Residential building emissions per capita KPI)^2 + (Commercial building emissions per capita KPI)^2 + (Transport emissions per capita KPI)^2 + (Estimated reducable water withdrawal(cubic meters/MWh))^2)</u>**\n\nThis will basicall give the length of the vector or L2 -distance from origin in 4 dimensional vector space.\n\nBut we cannot visualize this. So, only for convinience of visualization, we will create \n\n**<u>Long-term Environment Risk  2  = Square root of ((Direct emissions per capita(metric tonnes CO2e)^2 + (Estimated reducable water withdrawal(cubic meters/MWh))^2))</u>**\n"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"aei1 = source_of_energy[['Organization','state_id','density_category','Estimated reducable water withdrawal (m3/MWh)']].merge(ghg_from_consumption[['Organization','state_id',\n                                              'Residential building emissions - carbon intensity in metric tonnes of CO2e generated, for each million Btu of energy consumed, per capita',\n       'Commercial building emissions - carbon intensity in metric tonnes of CO2e generated, for each million Btu of energy consumed, per capita',\n       'Transport emissions - carbon intensity in metric tonnes of CO2e generated, for each million Btu of energy consumed, per capita'                                \n                                                                              ]],on=['Organization','state_id'],how='left').dropna()\n\naei2 = source_of_energy[['Organization','state_id','density_category','Estimated reducable water withdrawal (m3/MWh)']].merge(scope1_scope3_emissions[['Organization','state_id',\n                                                                     'Direct emissions per capita(metric tonnes CO2e),excluding generation of grid-supplied energy)'\n                                                                              ]],on=['Organization','state_id'],how='left').dropna()\n\n\naei1['Long-term Environment Risk  1'] = ((normalize(aei1[['Estimated reducable water withdrawal (m3/MWh)','Residential building emissions - carbon intensity in metric tonnes of CO2e generated, for each million Btu of energy consumed, per capita',\n       'Commercial building emissions - carbon intensity in metric tonnes of CO2e generated, for each million Btu of energy consumed, per capita',\n       'Transport emissions - carbon intensity in metric tonnes of CO2e generated, for each million Btu of energy consumed, per capita']],norm='max',axis=0))**2).sum(axis=1)**0.5\n\naei1[['Estimated reducable water withdrawal (m3/MWh)','Residential building emissions - carbon intensity in metric tonnes of CO2e generated, for each million Btu of energy consumed, per capita',\n       'Commercial building emissions - carbon intensity in metric tonnes of CO2e generated, for each million Btu of energy consumed, per capita',\n       'Transport emissions - carbon intensity in metric tonnes of CO2e generated, for each million Btu of energy consumed, per capita']] = normalize(aei1[['Estimated reducable water withdrawal (m3/MWh)','Residential building emissions - carbon intensity in metric tonnes of CO2e generated, for each million Btu of energy consumed, per capita',\n       'Commercial building emissions - carbon intensity in metric tonnes of CO2e generated, for each million Btu of energy consumed, per capita',\n       'Transport emissions - carbon intensity in metric tonnes of CO2e generated, for each million Btu of energy consumed, per capita']],norm='max',axis=0)\n\n\naei2['Long-term Environment Risk 2'] = ((normalize(aei2[['Estimated reducable water withdrawal (m3/MWh)','Direct emissions per capita(metric tonnes CO2e),excluding generation of grid-supplied energy)']],norm='max',axis=0))**2).sum(axis=1)**0.5\naei2[['Estimated reducable water withdrawal (m3/MWh)','Direct emissions per capita(metric tonnes CO2e),excluding generation of grid-supplied energy)']] = (normalize(aei2[['Estimated reducable water withdrawal (m3/MWh)','Direct emissions per capita(metric tonnes CO2e),excluding generation of grid-supplied energy)']],norm='max',axis=0))\n\n\n\nfig = px.scatter(aei2[aei2['Estimated reducable water withdrawal (m3/MWh)']>0],y='Estimated reducable water withdrawal (m3/MWh)',\n    x='Direct emissions per capita(metric tonnes CO2e),excluding generation of grid-supplied energy)',\n                 text='Organization',\n#                  color='state_id'\n                 \n    )\n\nfig.update_layout(\n        template=\"plotly_dark\",\n        title = dict(text='Long-term Environment Risk  2',x=0.5,y=.97),\n        height=600,\n        width=1000,\n        \n        font_color=\"rgb(199,233,180)\",\n        shapes=[\n            dict(opacity=0.3,line_dash='dot',fillcolor='rgb(199,233,180)',\n              type= 'rect',\n              y0= 0, y1= 0.5,   # adding a horizontal line at Y = 1\n              x0= 0, x1= 0.5\n                 ),\n            dict(opacity=0.3,line_dash='dot',fillcolor='rgb(215,48,39)',\n              type= 'rect',\n              y0= 0.5, y1= 1,   # adding a horizontal line at Y = 1\n              x0= 0.5, x1= 1\n                 ),\n            ]\n        )\n\nfig.show()\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Plot above, shows where the cities are and also it has been separated into 4 quadrants. The bottom-left green quadrant shows the cities that are less adverse toward the environment and upper-right red quadrant shows cities are highly adverse toward environment."},{"metadata":{},"cell_type":"markdown","source":"# Socio-Environmental Risk monitor\n\nWhile Long-term Environment Risk (LERI) is capable of showing how bad or friendly a city is towards it's environment, it shows only half of the picture. CDC's Social Vulnerability Index has the other half. As CDC mentions, Social Vulnerability Index measures how well prepared a community is to recover from any natural disaster or hazard. However, risks defined by both of these KPIs are long term in nature and cannot be changed soon. So, we combine Long-term Environment Risk  and SVI together to create Socio-Environmental Risk Monitor.\n\n## Why do we need it?\n\nA city that has high adverserial score, yet well prepared to cope up with climate hazards may not require immediate attention. However, a city that has high vulnerability according to SVI and has high adverserial score in LERI, is subjecting it's population to a greater disaster,as the population in these areas has lesser chances of recovering from it.\n\nSocio-Environmental Risk Monitor essentially can give us an excellent outlook on both social risk front and environmental risk front.\n\n## How do we calculate it?\n\nWe have LERI score at city level. However, SVI works differently. SVI is created by considering ,\n1. Soci-economic status\n2. Household composition & Disability\n3. Minority status and language\n4. Housing type & transportation\n\nand it's scores are sum of all the flagged up values, that has individual KPI scores above 90th percentile.\nThe values are also at CDC's census FIPS level. In this notebook, we consider FIPS level SVI scores present within a city and city's total population to get a derived SVI score at city level. We use this derived SVI score with LERI 1 score.\n\n<u>**derived SVI score =  (SVI score at city's relevant FIPS level * FIPS population)/city's total population**</u>\n\n<u>**Socio-Environmental Risk Monitor = Square root of ((Derived SVI score)^2 + (LERI 1 score)^2))**</u>\n"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"aei1.reset_index(drop=True,inplace=True)\naei1['derived SVI score'] = 0.0\n\nfor idx in aei1.index.values:\n#     print(idx)\n    org = aei1['Organization'].iloc[idx]\n    state_id = aei1['state_id'].iloc[idx]\n    derived_svi_score = (svi_data[svi_data['FIPS'].isin(census_tract_data[(census_tract_data['PlaceName']==org)&(census_tract_data['StateAbbr']==state_id)]['TractFIPS'].unique())&(svi_data['F_TOTAL']>-1)]['F_TOTAL']*\\\n                                     svi_data[svi_data['FIPS'].isin(census_tract_data[(census_tract_data['PlaceName']==org)&(census_tract_data['StateAbbr']==state_id)]['TractFIPS'].unique())&(svi_data['F_TOTAL']>-1)]['E_TOTPOP']).values.sum()/svi_data[svi_data['FIPS'].isin(census_tract_data[(census_tract_data['PlaceName']==org)&(census_tract_data['StateAbbr']==state_id)]['TractFIPS'].unique())&(svi_data['F_TOTAL']>-1)]['E_TOTPOP'].sum()\n    aei1.at[idx,'derived SVI score'] = derived_svi_score\n\n# aei1[['Long-term Environment Risk  1','derived SVI score']] = normalize(aei1[['Long-term Environment Risk  1','derived SVI score']],axis=0)    \naei1['Socio-Environmental Risk Monitor'] = ((normalize(aei1[['Long-term Environment Risk  1','derived SVI score']],axis=0)**2).sum(axis=1))**0.5\nfig = px.scatter(aei1,y='derived SVI score',\n    x='Long-term Environment Risk  1',\n                 text='Organization',\n#                  color='state_id'\n                 \n    )\n\nfig.update_layout(\n        template=\"plotly_dark\",\n        title = dict(text='Socio-Environmental Risk Monitor',x=0.5,y=.97),\n        height=600,\n        width=1000,\n        \n        font_color=\"rgb(199,233,180)\",\n        shapes=[\n            dict(opacity=0.3,line_dash='dot',fillcolor='rgb(199,233,180)',\n              type= 'rect',\n              y0= 0, y1= 1.88,   # adding a horizontal line at Y = 1\n              x0= 0, x1= 0.73\n                 ),\n            dict(opacity=0.3,line_dash='dot',fillcolor='rgb(215,48,39)',\n              type= 'rect',\n              y0= 1.88, y1= 3.97,   # adding a horizontal line at Y = 1\n              x0= 0.73, x1= 1.41\n                 ),\n            ]\n        )\n\nfig.show()\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"So the plot above shows where cities stand in terms of Socio-Environmental Risk Monitor. **<u>This value can help policy makers and administrators to identify high risk cities in red-zone and prioritize actions on them.</u>**\n    \n\nThe bottom-left green quadrant shows the cities having less than the 50th percentile values in both factors. These cities have low vulnerability and low Long-term Environment Risk on environment. \n    \n\n**<u>The upper-right red quadrant shows cities which have high vulnerability and high Long-term Environment Risk . These cities have higher chances of facing climate hazards and climate hazard related social issues and yet have a population, that has relatively less strength and capability to recover. This can be driven by unemployment, higher percentage of aging population and below poverty level population.</u>**"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"aei1[['density_category','Organization','Socio-Environmental Risk Monitor','derived SVI score','Long-term Environment Risk  1']].sort_values(by='Socio-Environmental Risk Monitor',ascending=False).head(10)\\\n.style.set_caption('Socio-Environmental Risk Monitor')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"temp = aei1[['density_category','Organization','Socio-Environmental Risk Monitor']]\\\n.groupby(['density_category'])['Socio-Environmental Risk Monitor'].describe().reset_index().sort_values(by='mean')\n\nfig = px.bar(temp, x='density_category', y='mean')\nfig.update_layout(title_text='Average Socio-Environmental Risk monitor value across density segments ',\n        height=400,\n        width=900,)\n\nfig.show()\ntemp[['density_category','mean','50%']].style.set_caption('Socio-Environmental Risk monitor value across density segments')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Stats above shows, very high density cities are having a higher degree of socio-environmental risk than other segments. Risk in low density cities are no different from that of  high density cities."},{"metadata":{},"cell_type":"markdown","source":"#  Imminent Environmental Risk KPI \n\nImminent Environmental Risk measures the climate related hazard reported by the city, using frequency of the hazard, happening at all other places, magnitude of hazard and impact of the hazard.\n\n\nImminent Environmental Risk = \n\nsum of(Frequency of the hazard  *  Magnitude of the hazard  *  impact of the hazard) for all hazards\n\nIt's important to segregate imminent or acute environmental risk from long-term or chronic environmental risk, because of the way it affects the population."},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"# let's build a common framework\n# we need a question number - against year\n# we need the country - US/UK\nlocation_values = cities_response[['lat','long','Organization']].drop_duplicates()\n\ndata = cities_response[(((cities_response['Question Number']=='2.1')&(cities_response['Year Reported to CDP'].isin([2019,2020])))\n               |((cities_response['Question Number']=='2.2a')&(cities_response['Year Reported to CDP']==2018)))\n                &(cities_response['Column Name']=='Climate Hazards')\n               ]\\\n[['Response Answer','CDP Region','Organization','Year Reported to CDP','Country','Column Name','Row Number']].drop_duplicates().dropna().reset_index(drop=True)\n\ndata['Sub-Response'] = data['Response Answer'].apply(lambda x: x.split('>')[1] if '>' in x else ' None')\ndata['Response Answer'] = data['Response Answer'].apply(lambda x: x.split('>')[0] if '>' in x else x)\n\ntemp = data[['Response Answer','Sub-Response']].groupby(['Response Answer'])['Sub-Response'].value_counts()\ntemp = pd.DataFrame(temp).rename(columns={'Sub-Response':'Count'}).reset_index()\ntemp['Sub-Response'] = temp['Sub-Response'].apply(lambda x: x[1:]) \n\ncommon = list(set(temp['Response Answer'].values).intersection(set(temp['Sub-Response'].values)))\n\nhazard_mapping = {}\nfor hazard in common:\n#     print(hazard,'  ',temp[temp['Sub-Response']==hazard]['Response Answer'].values[0])\n    hazard_mapping[hazard] = temp[temp['Sub-Response']==hazard]['Response Answer'].values[0]\n    \ndata['Response Answer'].replace(hazard_mapping,inplace=True)\nclimate_hazard = data\n\nhazard_probability = cities_response[(((cities_response['Question Number']=='2.1')&(cities_response['Year Reported to CDP'].isin([2020])))\n               )\n                &(cities_response['Column Name'].isin(['Current probability of hazard']))\n               ]\\\n[['Response Answer','CDP Region','Organization','Year Reported to CDP','Country','Column Name','Row Number']].sort_values(by='Year Reported to CDP',ascending=False).drop_duplicates().dropna().reset_index(drop=True)\n\nhazard_magnitude = cities_response[(((cities_response['Question Number']=='2.1')&(cities_response['Year Reported to CDP'].isin([2020])))\n               )\n                &(cities_response['Column Name'].isin(['Current magnitude of hazard']))\n               ]\\\n[['Response Answer','CDP Region','Organization','Year Reported to CDP','Country','Column Name','Row Number']].sort_values(by='Year Reported to CDP',ascending=False).drop_duplicates().dropna().reset_index(drop=True)\n\n\n\nclimate_hazard_info = pd.pivot_table(climate_hazard[['Year Reported to CDP','CDP Region','Country','Organization','Response Answer','Column Name']].drop_duplicates(subset=['Year Reported to CDP','CDP Region','Country','Organization','Response Answer']),\n                                     index=['Year Reported to CDP','CDP Region','Country','Organization'],\n                                     columns='Response Answer',aggfunc='count',fill_value=0.0)\nclimate_hazard_info.reset_index(inplace=True)\nclimate_hazard_info.columns = ['Year Reported to CDP','CDP Region','Country',\n       'Organization','Biological hazards ',\n       'Chemical change ',\n       'Extreme Precipitation ',\n       'Extreme cold temperature ',\n       'Extreme hot temperature ',\n       'Flood and sea level rise ',\n       'Mass movement ',\n       'Storm and wind ',\n       'Water Scarcity ', 'Wild fire ']\n\n# climate_hazard_info.drop(['No Answer'],axis=1,inplace=True)\nCDP_enlisted_hazards = list(climate_hazard['Response Answer'].unique())\n\n\nclimate_hazard_info['Total no. of hazards faced'] = climate_hazard_info[CDP_enlisted_hazards].sum(axis=1)\nclimate_hazard_info['percentage of CDP enlisted climate hazards faced'] = np.round(100*(climate_hazard_info['Total no. of hazards faced']/len(climate_hazard['Response Answer'].unique())),4)\n\n\n# climate_hazard_summary = get_regional_summary(climate_hazard_info,'percentage of CDP enlisted climate hazards faced')\n\nclimate_hazard_info = climate_hazard_info.merge(location_values,on='Organization',how='left')\n\n\n\nclimate_hazard_last_status = climate_hazard_info.sort_values(by=['Year Reported to CDP','Organization']).drop_duplicates(subset=['Organization'],keep='last')\n\nhazards_temp = (climate_hazard_last_status[CDP_enlisted_hazards].sum(axis=0)/climate_hazard_last_status.shape[0]).sort_values(ascending=False)*100\nhazards_temp_trace = go.Bar(x=hazards_temp.index.values, y=hazards_temp.values,\n             showlegend=True,text=np.round(hazards_temp.values,2),textposition='auto'\n             )\n\nclimate_hazard = climate_hazard.rename(columns={'Response Answer':'Hazard'})\\\n.merge(hazard_probability.rename(columns={'Response Answer':'Hazard Probability'}),on=['Organization','Row Number'],how='left')\\\n.merge(hazard_magnitude.rename(columns={'Response Answer':'Hazard Magnitude'}),on=['Organization','Row Number'],how='left')\\\n.drop(['CDP Region_x','CDP Region_y','Country_y','Country_x','Year Reported to CDP_y','Year Reported to CDP_x','Column Name','Column Name_x','Column Name_y'],axis=1)\n\nclimate_hazard.replace(dict(hazards_temp/100),inplace=True)\nclimate_hazard.replace({'Medium High':0.8, 'Medium':0.6, 'High':1, 'Medium Low':0.4, 'Low':0.2,\n       'Does not currently impact the city':0.0, 'Do not know':0.0},inplace=True)\n\nclimate_hazard.replace({'Medium High':0.8, 'Medium':0.6, 'High':1, 'Medium Low':0.4, 'Low':0.2,\n       'Does not currently impact the city':0.0, 'Do not know':0.0},inplace=True)\n\nclimate_hazard['Imminent Risk indicator'] = climate_hazard['Hazard']*climate_hazard['Hazard Magnitude']*climate_hazard['Hazard Probability']\nclimate_hazard = climate_hazard.groupby(['Organization'])['Imminent Risk indicator'].sum().reset_index()\nclimate_hazard = climate_hazard.merge(location_values,on='Organization',how='left')\nclimate_hazard['text'] = climate_hazard[['Organization','Imminent Risk indicator']].\\\napply(lambda x: 'Organization : '+str(x[0])+', Severity: '+str(x[1]),axis=1)\n\nsmb = go.Scattermapbox(name='Severity map',\n        lon = climate_hazard['long'],\n        lat = climate_hazard['lat'],\n        text = climate_hazard[\"Imminent Risk indicator\"],\n        mode = 'markers',\n#         locationmode='USA-states',\n        hovertext=climate_hazard['text'],\n        marker = dict(\n#             sizemin = 5,\n#             sizemode='area',\n            size = 10,\n            opacity = 0.8,\n            reversescale = False,\n            autocolorscale = False,\n            colorscale = 'Reds',\n            cmin = 0,\n            color = climate_hazard[\"Imminent Risk indicator\"],\n            cmax = climate_hazard[\"Imminent Risk indicator\"].max(),\n            colorbar_title=\"Severity scale\",\n            colorbar_thickness=10,\n            colorbar_title_side='right',\n            colorbar_len=.3,\n            colorbar_xanchor='left',\n            colorbar_yanchor='bottom',\n            colorbar_y=0.7\n        ))\n\nfig = make_subplots(\n    rows=2, cols=2,\n    specs=[\n           [{\"type\": \"scattermapbox\",'colspan':2},None],\n#            [{\"type\": \"histogram2d\"}],\n           [{\"type\": \"bar\",'colspan':2},None],\n\n          ],\n    subplot_titles=['Imminent Environmental Risk map',\n                    'Percentage of cities facing the climate hazard',\n                    \n                    ],\n#     y_title='Exploration of climate hazards across the world'.upper(),\n    vertical_spacing=0.14,\n    row_heights=[0.75,0.25],\n        \n#     column_widths=[1,1,1]\n    \n                    )\n\nfig.add_trace(smb,row=1,col=1)\nfig.update_layout(\n        mapbox_style=\"carto-positron\",\n#         title = dict(text='% of CDP enlisted services affected',x=0.5,y=.97),\n        height=900,\n        width=800,\n        hovermode='closest',\n        mapbox=dict(\n        bearing=0,\n        center=go.layout.mapbox.Center(\n            lat=35,\n            lon=-95\n        ),\n        pitch=0,\n        zoom=3\n    )\n        )\n\nfig.update_layout(\n    template=\"plotly_dark\",\n    margin=dict(r=25, t=25, b=140, l=100),\n    showlegend=False,\n    bargroupgap=0.25,\n    bargap=0.25\n)\n\n\nfig.append_trace(hazards_temp_trace,row=2,col=1)\n\nfig.update_traces(opacity=0.65)\n\nfig.update_layout(font_color=\"rgb(199,233,180)\",font_family='Arial')\n\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Now that we have explored the risk, let's see the response of the cities in terms of adaptation.\n\n# ADAPTATION\n\n#### Adaptation KPIs sums up the adaptation efforts being made, by the city. It individually measures different aspects of these efforts. We sum up all of these KPIs in an index at the end, to give a high level overview of these efforts by the cities.\n\nIn this section we depeloped smaller KPIs based on maximum and detailed use of CDP data only. KPI scores are calculated using custom assigned weightages, shown below. \n\nKPIs are calculated in three ways. \n\n1. Count :  No. of parameters reported on the corresponding section, by the city.\n2. Sum : Sum of weightages against the reported parameters in the corresponding section, by the city.\n3. Ratio :  Sum of weightages/maximum possible value. For example, \n\nstatus score of the projects = sum of weightages provided against all the status/(No. of projects * weightage of final status)\n\nThis tells us, to what extent projects have progressed in a normalized scale.\n\nOverall , We considered and calculated following :\n\n1. **'actions_taken_score'** : Count of actions taken \n2. '**status_of_action_score'**:  sum of weightages provided against all the status/(No. of projects * weightage of final status)\n\nCurrent overall status of all the projects. Calculated in 'Ratio' way.\n\n3. **'co_benefit_score'**: Count of co-beneficiaries involved in these actions\n4. **'sector_coverage_score'**: We assigned weights to each sector, based on importance. Such as water, energy and transport has higher weightage than others. Calculated as sum of weightages.\n\n5. **'finance_status_score'**: sum of weightages provided against all the finance status/(No. of projects * weightage of final status)\n\nStatus of financing of the projects. Calculated in 'Ratio' way.\n\n6. **'funding_diversity_score'**: Count of funding sources\n7. **'total_funding'**: Total funding as disclosed by city.\n8. **'total_govt_funding'**:  Total govt. funding as disclosed by city.\n9. **'funding_reliability_strength'**: total_govt_funding/total_funding\n10. **'funding_dependency_score'**: Total cost provided by the majority funding source/total funding\n\n11. **'funding_to_per_capita_income_ratio_per_sqkm'**: Funding per sq.km of the city/Income per sq.km\n12. **'funding_to_per_capita_income_ratio_per_head'**: per capita funding/per capita income\n\nPer capita income and per sq.km income data collected from SVI data, based on FIPS, present within a city.\n\n\n13. **'adaptation_favorability_score'** : This score shows the level of favorability, city faces, in terms of different factors, that are reported either to be challenging them or supporting them. "},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"weightage.head(20).style.set_caption('Weightage to adaptation related values')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"def get_score(type_,sub_type,section,score_type='ratio'):\n    \n    var_ = '_'.join(section.split(' '))\n#     print(globals()[var_])\n        \n    values = []\n    for val in globals()[var_]:\n        try:\n            values.append(weightage[(weightage['Type']==type_)&(weightage['Sub-type']==sub_type)\\\n                                       &(weightage['Section']==section)&(weightage['Parameter'].isin([val]))]['Weightage'].values[0])\n        except Exception as e:\n            pass\n#     print(values)\n    if score_type=='ratio':\n        score = np.sum(values)/(len(values)*weightage[(weightage['Type']==type_)&(weightage['Sub-type']==sub_type)\\\n                                   &(weightage['Section']==section)]['Weightage'].max())\n    if score_type=='sum':\n        score = np.sum(values)\n    \n    return score\n\n\ndef get_count(q_num,years,col_name,org):\n    \n    return cities_response[((cities_response['Question Number']==q_num)&(cities_response['Year Reported to CDP'].isin(years)))\n                      &(cities_response['Column Name']==col_name)&(cities_response['Organization']==org)\n                      ].sort_values(by=['Year Reported to CDP','Column Number','Row Number'],ascending=False)[['Response Answer','Row Number']].drop_duplicates(keep='first')\\\n    ['Response Answer'].dropna().unique().shape[0]\n\n\ndef get_values(q_num,years,col_name,org):\n    \n    return cities_response[((cities_response['Question Number']==q_num)&(cities_response['Year Reported to CDP'].isin(years)))\n                      &(cities_response['Column Name']==col_name)&(cities_response['Organization']==org)].sort_values(by=['Year Reported to CDP','Column Number','Row Number'],ascending=False)[['Response Answer','Row Number']].drop_duplicates(keep='first')['Response Answer'].dropna().values\n\n\n\nadaptation_action_kpi_recorder = {}\nkpis =  ['actions_taken_score','status_of_action_score','co_benefit_score',\\\n               'sector_coverage_score','finance_status_score','funding_diversity_score','total_funding','total_govt_funding',\n               'funding_reliability_strength','funding_dependency_score','funding_to_per_capita_income_ratio_per_sqkm',\n               'funding_to_per_capita_income_ratio_per_head'\n               ]\n\nfor org,state_id in zip(city_metadata[city_metadata['Organization'].isin(all_cities)]['Organization'].values,city_metadata[city_metadata['Organization'].isin(all_cities)]['state_id'].values):\n    \n    # No. of actions taken\n\n\n    q_num='3.0'\n    type_='Adaptation'\n    sub_type='Adaptation-action'\n\n    years=[2019,2020]\n    col_name='Action title'\n\n    actions_taken_score = get_count(q_num,years,col_name,org)\n    \n    # Status of action - with weightage\n    col_name='Status of action'\n    Status_of_action = get_values(q_num,years,col_name,org)\n\n    section=col_name\n    status_of_action_score = get_score(type_,sub_type,section)\n\n    col_name = 'Co-benefit area'\n    co_benefit_score = get_count(q_num,years,col_name,org)\n\n    # Action Coverage KPI : Sectors affected of the corresponding cities/Sectors covered under adaptation plan - with weightage\n    col_name='Sectors/areas adaptation action applies to'\n    Sectors_areas_adaptation_action_applies_to = get_values(q_num,years,col_name,org)\n\n    section='Sectors areas adaptation action applies to'\n    sector_coverage_score = get_score(type_,sub_type,section,score_type='sum')\n\n    # Finance Status - with weightage\n\n    col_name='Finance status'\n    Finance_status = get_values(q_num,years,col_name,org)\n\n    section=col_name\n    finance_status_score = get_score(type_,sub_type,section,score_type='ratio')\n\n    # Funding diversity KPI : No. of Majority funding sources \n\n    col_name='Majority funding source'\n    funding_diversity_score = get_count(q_num,years,col_name,org)\n\n\n    # Funding reliability strength - share of reliable funding source such as Government funding in total funding \n    col_name = 'Total cost of the project (currency)'\n    years=[2020]\n    total_funding = get_values(q_num,years,col_name,org).astype('int').sum()\n\n\n    col_name = 'Total cost provided by the local government (currency)'\n    total_govt_funding = get_values(q_num,years,col_name,org).astype('int').sum()\n\n    if total_funding=='No Answer':\n        total_funding=0.0\n    if total_govt_funding=='No Answer':\n        total_govt_funding=0.0\n\n    if (total_funding!=0.0) & (total_govt_funding!=0.0):    \n        funding_reliability_strength = float(total_govt_funding)/float(total_funding)\n    else:\n        funding_reliability_strength = 0.0\n\n\n    # Funding dependency score - how much of total funding comes from the majority funding source   \n\n    col_name = 'Total cost provided by the majority funding source (currency)'\n    funding_dependency_score = get_values(q_num,years,col_name,org).astype('int').sum()/total_funding\n\n    # Growth in overall funding from previous year\n\n#     years=[2019]\n#     total_funding_prev_year = get_values(q_num,years,col_name,org).astype('int').sum()\n\n\n#     growth_in_funding = (total_funding/total_funding_prev_year)-1 if (total_funding_prev_year!=0.0) else 0.0\n\n\n    # Funding to per capita income ratio : Funding per sq.km/Avg. income per sq.km \n\n    ## first find funding per sq.km\n\n    funding_per_sqkm = total_funding/city_metadata[city_metadata['Organization']==org]['area in sq.km'].values[0]\n    funding_per_head = total_funding/city_metadata[city_metadata['Organization']==org]['population'].values[0]\n\n    ## Then find per capita income of the city from SVI data\n    per_capita_income_of_city = (svi_data[svi_data['FIPS'].isin(census_tract_data[(census_tract_data['PlaceName']==org)&(census_tract_data['StateAbbr']==state_id)]['TractFIPS'].unique())]['E_PCI']*\\\n                                     svi_data[svi_data['FIPS'].isin(census_tract_data[(census_tract_data['PlaceName']==org)&(census_tract_data['StateAbbr']==state_id)]['TractFIPS'].unique())]['E_TOTPOP']).values.sum()/svi_data[svi_data['FIPS'].isin(census_tract_data[(census_tract_data['PlaceName']==org)&(census_tract_data['StateAbbr']==state_id)]['TractFIPS'].unique())]['E_TOTPOP'].sum()\n\n    ## multiply per capita income with density and you get avg. income per sq.km\n    avg_income_per_sqkm = (per_capita_income_of_city*city_metadata[city_metadata['Organization']==org]['density'].values[0])\n\n\n    \n    funding_to_per_capita_income_ratio_per_head = funding_per_head/per_capita_income_of_city \n    funding_to_per_capita_income_ratio_per_sqkm = funding_to_per_capita_income_ratio_per_head*city_metadata[city_metadata['Organization']==org]['density'].values[0]\n    \n    # Record KPI values:\n    \n    adaptation_action_kpi_recorder[org] = [globals()[kpi] for kpi in kpis]\n    ","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Per sq.km funding to per sqkm income ratio \n\nWe can interpret this as, **amount that comes back to a sq.km area of the city as funding against the avg. income of per sq.km area of the city.**\n\n\nFor City of Cincinnati, we have Funding to per capita income ratio per sqkm of 14.08. So we can say, **for every 1000 USDs earned in a sq.km area, 14.08 USDs come back to the city as part of environmental funding.**\n\n\n## Per capita funding to per capita income ratio \n\nThis kpi is almost same as above. It tries to capture funding to income ratio on per head basis. \n\nFor City of Cincinnati, we have Funding to per capita income ratio per head of 0.009(approx.). So we can say, **for every 1000 USDs earned per person, 9 USDs are spent as part of environmental funding.**\n\n#### Reason for judging the funding amount against per capita income, instead of looking at per capita funding, is to see, if there is any disparity, in wealth generation capacity of a locality and the funding it receives. This could help to ensure social equity , while addressing environmental issues.\n\nAverage income data has been collected from CDC's social vulnerability index dataset. "},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"adaptation_action_kpi = pd.DataFrame(adaptation_action_kpi_recorder).T\nadaptation_action_kpi.columns = kpis\nadaptation_action_kpi.fillna(0.0,inplace=True)\nadaptation_action_kpi.reset_index(inplace=True)\nadaptation_action_kpi.rename(columns={'index':'Organization'},inplace=True)\n\n\nadaptation_action_kpi[['Organization', 'funding_to_per_capita_income_ratio_per_sqkm',\n       'funding_to_per_capita_income_ratio_per_head', 'actions_taken_score', 'status_of_action_score',\n       'co_benefit_score', 'sector_coverage_score', 'finance_status_score',\n       'funding_diversity_score', 'total_funding', 'total_govt_funding',\n       'funding_reliability_strength', 'funding_dependency_score',       \n       ]][adaptation_action_kpi['funding_to_per_capita_income_ratio_per_head']>0].sort_values(by='funding_to_per_capita_income_ratio_per_head',ascending=True).head(10).style.set_caption('Adaptation KPIs')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"# Define weightage for levels in both support and challenge\n\nlevel_weightage = {'Significantly challenges':-1,\n'Moderately challenges':-0.6,\n'Somewhat challenges':-0.3,\n'Significantly supports':1,\n'Moderately supports':0.6,\n'Somewhat supports':0.3,\n}","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"\nfavorability_scores = {}\n\n\nfor org in all_cities:\n    q_num='2.2'\n#     print(org)\n    data = cities_response[((cities_response['Question Number']==q_num)&(cities_response['Year Reported to CDP'].isin([2020])))                            \n                  &(cities_response['Organization']==org)].sort_values(by='Year Reported to CDP')[['Organization','Column Name','Response Answer','Row Number']]\\\n    .drop_duplicates(keep='first')\\\n    .sort_values(by=['Column Name','Row Number'])[['Column Name','Response Answer','Row Number']]\n    \n    data = data.pivot(index='Row Number',columns='Column Name',values='Response Answer').iloc[:,:3]\n    data.columns = ['Factors that affect ability to adapt','Indicate if this factor either supports or challenges the ability to adapt','Level of degree to which factor challenges/supports the adaptive capacity of your city']\n    data = data.merge(favorability_weightage,on='Factors that affect ability to adapt',how='left').dropna()\n    data.replace(level_weightage,inplace=True)\n    \n    data['factor_related_favorability_score'] = 0.0\n    data['factor_related_favorability_score'] = data[['Indicate if this factor either supports or challenges the ability to adapt','Level of degree to which factor challenges/supports the adaptive capacity of your city','Challenges','Supports']]\\\n    .apply(lambda x: x[1]*x[2] if x[0]=='Challenges' else x[1]*x[3],axis=1)\n    \n    favorability_scores[org] = data['factor_related_favorability_score'].sum()\n    \n    \n# include this favorability score for each city in adaptation kpi dataframe. Include density category of the cities.\n\nadaptation_action_kpi = adaptation_action_kpi.merge(pd.DataFrame(favorability_scores.values(),favorability_scores.keys()).reset_index()\\\n.rename(columns={'index':'Organization',0:'adaptation_favorability_score'}),on='Organization',how='left')\n\nadaptation_action_kpi = adaptation_action_kpi.merge(city_metadata[['Organization','density_category']],on='Organization',how='left')    ","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Observations on KPI values for cities with different densities\n\nMany of the selected cities have not disclosed funding related amounts. So we avoid those and choose relevant ones only and look at some of the statistics.\n\n**Following the median values we can say, cities with high densities seem to be getting more funding per person compared to per capita income.**  However, when we switch to **funding per sq. km., very high density cities are getting twice the amount** compared to low density cities and medium density and high desnity cities are lagging way behind. We will later see, how this disparity affects the overall performances of the cities towards adaptation."},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"adaptation_action_kpi[adaptation_action_kpi['funding_to_per_capita_income_ratio_per_head']>0].groupby(['density_category'])['funding_to_per_capita_income_ratio_per_head'].describe()\\\n.style.set_caption('funding to per capita income ratio per head')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"adaptation_action_kpi[adaptation_action_kpi['funding_to_per_capita_income_ratio_per_sqkm']>0].groupby(['density_category'])['funding_to_per_capita_income_ratio_per_sqkm'].describe()\\\n.style.set_caption('funding to per capita income ratio per sq.km')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Let's have a look at the top 20 cities, who have given more information than the rest and try to look at the basic statistics, coming out of it."},{"metadata":{},"cell_type":"markdown","source":"## Further observations on KPI values for cities with different densities\n\n1. Cities with very high density are ahead of high and medium density cities, in terms of number of projects.\n\n\n2. In terms of preoject execution, low density cities are behind by margin of 3-5%.\n\n\n3. Co-benefit scores are higher in medium density cities. \n\n4. In terms of financing and getting diverse sources of funding very high density cities are doing understandably better.\n\n5. Very high & high density cities are less reliable on govt. funding and low density cities are most dependent.\n\n6. Low density cities faces higher degree of challenges and receives higher per capita funding for per capita income."},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"adaptation_action_kpi_stats = adaptation_action_kpi[adaptation_action_kpi['Organization'].isin(['Washington', 'Miami', 'Tempe', 'Louisville', 'Phoenix',\n       'West Palm Beach', 'Providence', 'Indianapolis', 'Denver',\n       'Ann Arbor', 'New Bedford', 'Oakland', 'Somerville',\n       'Grand Rapids', 'Hayward', 'Boston', 'Santa Monica', 'Cleveland',\n       'Nashville', 'Pittsburgh'])].groupby(['density_category']).describe(percentiles=[.5]).T.reset_index()\\\n                                .rename(columns={'level_0':'KPI','level_1':'stat'})\nadaptation_action_kpi_stats = adaptation_action_kpi_stats[adaptation_action_kpi_stats['stat'].isin(['mean','50%'])]\n\n\n\nadaptation_action_kpi_stats[(adaptation_action_kpi_stats['stat']=='mean')&(adaptation_action_kpi_stats['KPI'].isin(['actions_taken_score', 'actions_taken_score',\n       'status_of_action_score', 'status_of_action_score',\n       'co_benefit_score', 'co_benefit_score', 'sector_coverage_score',\n       'sector_coverage_score', 'finance_status_score',\n       'finance_status_score', 'funding_diversity_score',\n       'funding_diversity_score', \n       'funding_reliability_strength', 'funding_reliability_strength',       \n       'funding_to_per_capita_income_ratio_per_sqkm',\n       'funding_to_per_capita_income_ratio_per_sqkm',\n       'funding_to_per_capita_income_ratio_per_head',\n       'funding_to_per_capita_income_ratio_per_head',\n        \n       ]))].style.highlight_max(color = 'lightgreen', axis = 1).set_caption('Adaptation KPIs across density segments with maximum values highlighted')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"adaptation_action_kpi_stats[(adaptation_action_kpi_stats['stat']=='mean')&(adaptation_action_kpi_stats['KPI'].isin([\n        'adaptation_favorability_score',      \n]))].style.highlight_min(color = 'orangered', axis = 1).set_caption('adaptation favorability score across density segments')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"plt.figure(figsize=(8,8))\nsns.heatmap(adaptation_action_kpi.corr())","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Observations from heatmap:\n\n1. **'actions_taken_score'** has high positive correlation with **'funding_to_per_capita_ratios'**, which states the obvious fact, that **more funding results in more projects.** \n\n\n2. **'status_of_action'** score is highly correlated with **'finance_status_score'**. This can be interpreted as , **as financing of the projects get more secured, projects also advance towards becoming operational.**\n\n\n3. **'co-benefit_score'** has highest positive correlation with **'sector_coverage_score'**, which makes sense, **as more sectors are covered , scope to include more co-beneficiaries also go up.**\n\n\n4. **'funding_diversity_score'** is positively correlated with **'finance_status_score' and 'co-benefit_score'**. While **higher diversity helping to advance finance status of a project** is understandable, a positive correlation with 'co-benefit_score' might mean , **different sources of funding also perhaps comes with a need for commitment towards more diverse implementation of the same project**. Same issues affecting different sectors, might make different sources of funding to come and work together.\n\n\n5. **'funding_reliability_strength' and 'funding_dependency_score'** are having positive correlation. While relibility strength defines reliability on funding sources that are more safe and reliable, dependency score defines, how much the project is dependent on the biggest funding source. **Government funding for example is mostly the biggest source of funding for low density areas**,as evident from the previous section. So a positive correlation between, 'funding_reliability_strength' and 'funding_dependency_score' is explainable. \n\n"},{"metadata":{},"cell_type":"markdown","source":"### Lets see , if we can sum all of it up in one KPI or index that can tell the whole comparative picture of adaptation work done in a city.\n\n#### First,we will select the following KPIs. \nactions_taken_score, status_of_action_score,co_benefit_score, sector_coverage_score,\nfinance_status_score, funding_diversity_score,funding_reliability_strength,funding_dependency_score,\nfunding_to_per_capita_income_ratio_per_sqkm,funding_to_per_capita_income_ratio_per_head,adaptation_favorability_score\n\n#### Then we will normalize them and assign a weighage to each of the KPI, based on our observations above. Since, funding and no. of projects are the most important and driving factors, we give more weightage to them.\n\nHowever, since many cities have chosen not to disclose the received amount of funding, we will create indices with and without funding considered.\n\n**adaptaton_index_with_funding** = actions_taken_score * 0.15+\n       status_of_action_score * 0.1+\n       co_benefit_score * 0.05+ \n       sector_coverage_score * 0.08+\n       finance_status_score * 0.08+ \n       funding_diversity_score * 0.05+\n       funding_reliability_strength * 0.05+\n       funding_dependency_score * 0.04+\n       funding_to_per_capita_income_ratio_per_sqkm * 0.15+\n       funding_to_per_capita_income_ratio_per_head * 0.15+\n       adaptation_favorability_score * 0.1\n       \n       \n**adaptation_index_without_funding** = actions_taken_score * 0.2+\n       status_of_action_score * 0.15+\n       co_benefit_score * 0.1+ \n       sector_coverage_score * 0.13+\n       finance_status_score * 0.13+ \n       funding_diversity_score * 0.1+\n       funding_reliability_strength * 0.05+\n       funding_dependency_score * 0.04+\n       adaptation_favorability_score * 0.1"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"kpi_weightage_with_funding = {'actions_taken_score':0.15,\n       'status_of_action_score':0.1,\n       'co_benefit_score':0.05, \n       'sector_coverage_score':0.08,\n       'finance_status_score':0.08, \n       'funding_diversity_score':0.05,\n       'funding_reliability_strength':0.05,\n       'funding_dependency_score':0.04,\n       'funding_to_per_capita_income_ratio_per_sqkm':0.15,\n       'funding_to_per_capita_income_ratio_per_head':0.15,\n       'adaptation_favorability_score':0.1}\n\nkpi_weightage_without_funding = {'actions_taken_score':0.2,\n       'status_of_action_score':0.15,\n       'co_benefit_score':0.1, \n       'sector_coverage_score':0.13,\n       'finance_status_score':0.13, \n       'funding_diversity_score':0.1,\n       'funding_reliability_strength':0.05,\n       'funding_dependency_score':0.04,\n       'adaptation_favorability_score':0.1}\n\nselected_kpis_with_funding = ['actions_taken_score', 'status_of_action_score',\n       'co_benefit_score', 'sector_coverage_score',\n       'finance_status_score', 'funding_diversity_score','funding_reliability_strength',\n       'funding_dependency_score',\n       'funding_to_per_capita_income_ratio_per_sqkm',\n       'funding_to_per_capita_income_ratio_per_head',\n       'adaptation_favorability_score']\n\nselected_kpis_without_funding = ['actions_taken_score', 'status_of_action_score',\n       'co_benefit_score', 'sector_coverage_score',\n       'finance_status_score', 'funding_diversity_score','funding_reliability_strength',\n       'funding_dependency_score',\n       'adaptation_favorability_score']\n\nkpis_to_be_normalized = ['actions_taken_score','co_benefit_score','sector_coverage_score','funding_diversity_score',\n                        'funding_to_per_capita_income_ratio_per_sqkm','adaptation_favorability_score'                      \n                        ]\n\nadaptation_action_kpi[kpis_to_be_normalized] = normalize(adaptation_action_kpi[kpis_to_be_normalized],axis=0,norm='max')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"adaptation_action_kpi['adaptation_index_with_funding'] = np.dot(adaptation_action_kpi[selected_kpis_with_funding],pd.DataFrame(kpi_weightage_with_funding.values(),kpi_weightage_with_funding.keys())).reshape(-1,1)\nadaptation_action_kpi['adaptation_index_without_funding'] = np.dot(adaptation_action_kpi[selected_kpis_without_funding],pd.DataFrame(kpi_weightage_without_funding.values(),kpi_weightage_without_funding.keys())).reshape(-1,1)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Interpretation of adaptation index\n\nHigher score on adaptation index means a better performing city towards the adaptation related goals.\nWe have selected only top 50% to avoid low scoring cities. A low score in CDP survey reponse, doesn't essentially mean poor performing city always.Itcould be becuase of non-disclosure of certain data, such as funding, which resulted with no score on important funding KPIs.\n\nA density based look at the top 50% cities, on mean 'adaptation index' score shows, **cities with low density are ahead of the rest.** considering funding KPIs. Otherwise, high density cities are doing better than the other segments.\n\n"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"adaptation_action_kpi[adaptation_action_kpi['adaptation_index_with_funding']!=0]\\\n.sort_values(by='adaptation_index_with_funding',ascending=False)[:20]\\\n.groupby(['density_category'])['adaptation_index_with_funding'].describe().style.set_caption('Adaptation index considering funding')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"adaptation_action_kpi[adaptation_action_kpi['adaptation_index_without_funding']!=0]\\\n.sort_values(by='adaptation_index_without_funding',ascending=False)[:20]\\\n.groupby(['density_category'])['adaptation_index_without_funding'].describe().style.set_caption('Adaptation index without considering funding')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"adaptation_action_kpi[['Organization','adaptation_index_with_funding','adaptation_index_without_funding','density_category']].sort_values(by='adaptation_index_with_funding',ascending=False).reset_index(drop=True).head(20)\\\n.style.set_caption('Adaptation indices of US cities')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Response to Risk KPI\n\nHaving an adaptation index with details gives us an opportunity to measure a city's response to the environmental risks, we calculated before. \n\nResponse to Risk KPI shows, whether city's adaptation efforts are proportionate enough, considering both social vulnerability risk  and both long term and imminent environmental risk.\n\n**Response to risk = adaptation_index_without_funding of the city/(Socio-Environmental Risk of of city+Imminent Environmental risk)**\n\nBelow we can see, most of the cities are following the ideal line, marked in the plot. However, cities like Oakland, Cincinnati are low adaptation effort, compared to the risk they are facing.\n\n***Response to risk KPI considers adaptation effort, social vulnerability, long term and imminent environmental risk and shows whether city authority's actions are taking these factors into account or not.***"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"response_to_risk = adaptation_action_kpi[['Organization','adaptation_index_without_funding','adaptation_index_with_funding']]\\\n.merge(aei1[['Organization','Socio-Environmental Risk Monitor']],on='Organization').merge(climate_hazard[['Organization','Imminent Risk indicator']],on='Organization',how='left')\n\nresponse_to_risk['Cumulated risk'] = (response_to_risk['Socio-Environmental Risk Monitor']+response_to_risk['Imminent Risk indicator'])\nresponse_to_risk['response to risk'] = response_to_risk['adaptation_index_without_funding']/(response_to_risk['Socio-Environmental Risk Monitor']+response_to_risk['Imminent Risk indicator'])\nresponse_to_risk.sort_values(by='response to risk',ascending=True).head(10)\\\n.style.set_caption('Response to Risk - sorted in low response to high response')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"fig = px.scatter(response_to_risk,y='adaptation_index_without_funding',\n    x='Cumulated risk',\n                 text='Organization',\n#                trendline='ols'\n                 \n    )\n\nfig.update_layout(\n        template=\"plotly_dark\",\n        title = dict(text='Response to Risk',x=0.5,y=.97),\n        height=600,\n        width=1000,\n        \n        font_color=\"rgb(199,233,180)\",\n        shapes=[\n            dict(opacity=0.3,line_dash='dot',fillcolor='rgb(199,233,180)',\n              type= 'line',\n              y0= 0.15, y1= 0.5,   # adding a horizontal line at Y = 1\n              x0= 0.5, x1= 14\n                 ),\n#             dict(opacity=0.3,line_dash='dot',fillcolor='rgb(215,48,39)',\n#               type= 'rect',\n#               y0= 1.88, y1= 3.97,   # adding a horizontal line at Y = 1\n#               x0= 0.73, x1= 1.41\n#                  ),\n            ]\n        )\n\nfig.show()\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Severity of impact on services\n\nSeverity of impact on services is calculated as percentage of CDP enlisted city services or assets, observed to be impacted in the city.\n\nThis shows , how badly cities operations are affected."},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"# select the data - by setting question no. and year based conditions\n\n# climate hazard related responses\n# 2018 reponse options are not in sync with 2019 and 2020.\ndata = cities_response[((cities_response['Question Number']=='2.1')&(cities_response['Year Reported to CDP'].isin([2019,2020])))\n               |((cities_response['Question Number']=='2.2a')&(cities_response['Year Reported to CDP']==2018))\n               ][['Year Reported to CDP',\n                  'CDP Region',\n                  'Column Name',\n                  'Country',\n                  'Question Name',\n                  'Organization',\n                  'Response Answer']].drop_duplicates().reset_index(drop=True)\n\ndata['Column Name'].replace(['Current consequence of hazard','Magnitude of impact'],'Current magnitude of hazard',inplace=True)\ndata['Question Name'] = 'Most significant climate hazards faced by city'\nCDP_enlisted_major_impacts = list(data[data['Column Name']=='Social impact of hazard overall']['Response Answer'].value_counts()[:30].index.values[:11])\nsocial_impact_info = pd.pivot_table(data[['Year Reported to CDP','CDP Region','Country','Organization','Response Answer','Column Name']][(data['Column Name']=='Social impact of hazard overall')&(data['Response Answer'].isin(CDP_enlisted_major_impacts))],index=['Organization','CDP Region','Country','Year Reported to CDP'],columns='Response Answer',aggfunc='count',fill_value=0.0)\nsocial_impact_info['Total no. of impacted social aspects'] = social_impact_info.sum(axis=1)\nsocial_impact_info.reset_index(inplace=True)\nsocial_impact_info['percentage of CDP enlisted social aspects impacted'] = (100*social_impact_info['Total no. of impacted social aspects']/len(CDP_enlisted_major_impacts)).round(2)\n\n\naffected_services = data[data['Column Name'].isin(['Most relevant assets / services affected overall',\n                               'Top three assets/ services affected' \n                              ])]\naffected_services.dropna(inplace=True)\naffected_services.sort_values(by=['Year Reported to CDP','Organization'],inplace=True)\nCDP_enlisted_sectors = list(affected_services['Response Answer'].value_counts()[:17].index.values)\n\naffected_services_info = pd.pivot_table(affected_services[['Year Reported to CDP','CDP Region','Country','Organization','Response Answer','Column Name']][affected_services['Response Answer'].isin(CDP_enlisted_sectors)],index=['Year Reported to CDP','CDP Region','Country','Organization'],columns='Response Answer',aggfunc='count',fill_value=0.0)\naffected_services_info['Total no. of impacted services'] = affected_services_info.sum(axis=1)\naffected_services_info.reset_index(inplace=True)\naffected_services_info['percentage of CDP enlisted services impacted'] = np.round(100*(affected_services_info['Total no. of impacted services']/len(CDP_enlisted_sectors)),4)\n\ndef get_regional_summary(info,feature):\n    info = info.groupby(['CDP Region','Year Reported to CDP'])[feature].describe()\n    return info.reset_index()\n\nsocial_impact_summary = get_regional_summary(social_impact_info,'percentage of CDP enlisted social aspects impacted')\naffected_services_summary = get_regional_summary(affected_services_info,'percentage of CDP enlisted services impacted')\naffected_services_info.columns = ['Year Reported to CDP', 'CDP Region', 'Country', \n       'Organization', 'Commercial',\n       'Education',\n       'Emergency services', 'Energy',\n       'Environment, biodiversity, forestry',\n       'Food & agriculture',\n       'Industrial',\n       'Information & communications technology',\n       'Land use planning',\n       'Law & order', 'Public health',\n       'Residential',\n       'Society / community & culture',\n       'Tourism', 'Transport',\n       'Waste management',\n       'Water supply & sanitation',\n       'Total no. of impacted services',\n       'percentage of CDP enlisted services impacted']\n\naffected_services_info = affected_services_info.merge(cities_response[['lat','long','Organization']].drop_duplicates(),on='Organization',how='left')\n\naffected_services_info['text'] = affected_services_info[['Organization','Country','percentage of CDP enlisted services impacted']].\\\napply(lambda x: 'Country : '+x[1]+', Organization : '+x[0]+', % of impact: '+str(x[2]),axis=1)\n\naffected_services_last_status = affected_services_info.sort_values(by=['Year Reported to CDP','Organization']).drop_duplicates(subset=['Organization'],keep='last')\n\nsmb = go.Scattermapbox(name='severity map',\n        lon = affected_services_last_status['long'],\n        lat = affected_services_last_status['lat'],\n        text = affected_services_info[\"percentage of CDP enlisted services impacted\"],\n        mode = 'markers',\n#         locationmode='USA-states',\n        hovertext=affected_services_last_status['text'],\n        marker = dict(\n#             sizemin = 5,\n#             sizemode='area',\n            size = 10,\n            opacity = 0.8,\n            reversescale = False,\n            autocolorscale = False,\n            colorscale = 'Reds',\n            cmin = 0,\n            color = affected_services_last_status[\"percentage of CDP enlisted services impacted\"],\n            cmax = affected_services_last_status[\"percentage of CDP enlisted services impacted\"].max(),\n            colorbar_title=\"Impact% - Severity scale\",\n            colorbar_thickness=10,\n            colorbar_title_side='right',\n            colorbar_len=.3,\n            colorbar_xanchor='left',\n            colorbar_yanchor='bottom',\n            colorbar_y=0.7\n        ))\n\n\nservices_temp = (affected_services_last_status[CDP_enlisted_sectors].sum(axis=0)/affected_services_last_status.shape[0]).sort_values(ascending=False)*100\nservices_temp_trace = go.Bar(x=services_temp.index.values, y=services_temp.values,\n             showlegend=True,text=np.round(services_temp.values,2),textposition='auto'\n             )\n\nservices_temp2 = affected_services_last_status.groupby(['CDP Region'])[CDP_enlisted_sectors].sum()/affected_services_last_status.groupby(['CDP Region'])[CDP_enlisted_sectors].count()\n\n\n\nfig = make_subplots(\n    rows=2, cols=2,\n    specs=[\n           [{\"type\": \"scattermapbox\",'colspan':2},None],\n#            [{\"type\": \"histogram2d\"}],\n           [{\"type\": \"bar\",'colspan':2},None],\n          ],\n    subplot_titles=['Severity map of impacted services',\n                    'Service-wise analysis : % of cities having the corresponding sector affected',\n                    ],\n#     y_title='Exploration of affected services across the world'.upper(),\n    vertical_spacing=0.14,\n    row_heights=[0.75,0.25],\n        \n#     column_widths=[1,1,1]\n    \n                    )\n\nfig.add_trace(smb,row=1,col=1)\nfig.update_layout(\n        mapbox_style=\"carto-positron\",\n#         title = dict(text='% of CDP enlisted services affected',x=0.5,y=.97),\n        height=900,\n        width=800,\n        hovermode='closest',\n        mapbox=dict(\n        bearing=0,\n        center=go.layout.mapbox.Center(\n            lat=35,\n            lon=-95\n        ),\n        pitch=0,\n        zoom=3\n    )\n        )\n\nfig.update_layout(\n    template=\"plotly_dark\",\n    margin=dict(r=25, t=25, b=140, l=100),\n    showlegend=False,\n    bargroupgap=0.25,\n    bargap=0.25\n)\n\n\nfig.append_trace(services_temp_trace,row=2,col=1)\n\nfig.update_traces(opacity=0.65)\n\nfig.update_layout(font_color=\"rgb(199,233,180)\",font_family='Arial')\n\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Severity of social impact\n\nSeverity of social impact is calculated as percentage of CDP enlisted social impacts observed in the city. "},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"\n\nsocial_impact_info.columns = ['Organization', 'CDP Region','Country',\n       'Year Reported to CDP','Fluctuating socio-economic conditions',\n        'Increased conflict and/or crime',\n        'Increased demand for healthcare services',\n        'Increased demand for public services',\n        'Increased incidence and prevalence of disease and illness',\n        'Increased resource demand',\n        'Increased risk to already vulnerable populations',\n        'Loss of tax base to support public services',\n        'Loss of traditional jobs',\n        'Migration from rural areas to cities',\n        'Population displacement',\n        'Total no. of impacted social aspects',\n        'percentage of CDP enlisted social aspects impacted']\n\nsocial_impact_info = social_impact_info.merge(cities_response[['lat','long','Organization']].drop_duplicates(),on='Organization',how='left')\nsocial_impact_info['text'] = social_impact_info[['Organization','Country','percentage of CDP enlisted social aspects impacted']].\\\napply(lambda x: 'Country : '+x[1]+', Organization : '+x[0]+', % of impact: '+str(x[2]),axis=1)\n\n\nsocial_impact_last_status = social_impact_info.sort_values(by=['Year Reported to CDP','Organization']).drop_duplicates(subset=['Organization'],keep='last')\nsocial_impact_last_status[CDP_enlisted_major_impacts].sum(axis=0)/social_impact_last_status.shape[0]\n\nsmb = go.Scattermapbox(name='Severity map',\n        lon = social_impact_last_status['long'],\n        lat = social_impact_last_status['lat'],\n        text = social_impact_info[\"percentage of CDP enlisted social aspects impacted\"],\n        mode = 'markers',\n#         locationmode='USA-states',\n        hovertext=social_impact_last_status['text'],\n        marker = dict(\n#             sizemin = 5,\n#             sizemode='area',\n            size = 10,\n            opacity = 0.8,\n            reversescale = False,\n            autocolorscale = False,\n            colorscale = 'Reds',\n            cmin = 0,\n            color = social_impact_last_status[\"percentage of CDP enlisted social aspects impacted\"],\n            cmax = social_impact_last_status[\"percentage of CDP enlisted social aspects impacted\"].max(),\n            colorbar_title=\"Impact% - Severity scale\",\n            colorbar_thickness=10,\n            colorbar_title_side='right',\n            colorbar_len=.3,\n            colorbar_xanchor='left',\n            colorbar_yanchor='bottom',\n            colorbar_y=0.7\n        ))\n \n\naspects_temp = (social_impact_last_status[CDP_enlisted_major_impacts].sum(axis=0)/social_impact_last_status.shape[0]).sort_values(ascending=False)*100\naspects_temp_trace = go.Bar(x=aspects_temp.index.values, y=aspects_temp.values,\n             showlegend=True,text=np.round(aspects_temp.values,2),textposition='auto'\n             )\n\nservices_temp2 = social_impact_last_status.groupby(['CDP Region'])[CDP_enlisted_major_impacts].sum()/social_impact_last_status.groupby(['CDP Region'])[CDP_enlisted_major_impacts].count()\n\n\n\nfig = make_subplots(\n    rows=2, cols=2,\n    specs=[\n           [{\"type\": \"scattermapbox\",'colspan':2},None],\n#            [{\"type\": \"histogram2d\"}],\n           [{\"type\": \"bar\",'colspan':2},None],\n          ],\n    subplot_titles=['Severity map of impacted social aspects',                    \n                    'Service-wise analysis : % of cities having the corresponding social aspect affected',\n                    ],\n#     y_title='Exploration of social impacts across the world'.upper(),\n    vertical_spacing=0.14,\n    row_heights=[0.75,0.25],\n        \n#     column_widths=[1,1,1]\n    \n                    )\n\nfig.add_trace(smb,row=1,col=1)\n\nfig.update_layout(\n        mapbox_style=\"carto-positron\",\n#         title = dict(text='% of CDP enlisted services affected',x=0.5,y=.97),\n        height=900,\n        width=800,\n        hovermode='closest',\n        mapbox=dict(\n        bearing=0,\n        center=go.layout.mapbox.Center(\n            lat=35,\n            lon=-95\n        ),\n        pitch=0,\n        zoom=3\n    )\n        )\n\nfig.update_layout(\n    template=\"plotly_dark\",\n    margin=dict(r=25, t=25, b=140, l=100),\n    showlegend=False,\n    bargroupgap=0.25,\n    bargap=0.25\n)\n\n\nfig.append_trace(aspects_temp_trace,row=2,col=1)\n\n\n# fig.append_trace(sun1,row=4,col=2)\n\n\n# fig.update_histogram(barmode='overlay')\n# Reduce opacity to see both histograms\nfig.update_traces(opacity=0.65)\n\nfig.update_layout(font_color=\"rgb(199,233,180)\",font_family='Arial')\n\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"affected_services_info = affected_services_info.merge(city_metadata,on='Organization',how='left')\naffected_services_info.groupby(['density_category','Year Reported to CDP'])['percentage of CDP enlisted services impacted'].describe(percentiles=[0.5])\\\n.style.set_caption('Severity of impacted services across density segments')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"social_impact_info = social_impact_info.merge(city_metadata,on='Organization',how='left')\nsocial_impact_info.groupby(['density_category','Year Reported to CDP'])['percentage of CDP enlisted social aspects impacted'].describe(percentiles=[0.5])\\\n.style.set_caption('Social impact severity across density segments')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Observation \n\nPercentages of both social impact and affected services are on the rise across all segments. However, low density cities are comparatively less affected than other segments.\n"},{"metadata":{},"cell_type":"markdown","source":"# Transport\n\nIn the previous sections, we found that transport is a big pain point, when it comes to GHG emissions.\nLet's explore further. "},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"q_num = '10.1'\nrow_name = ['Mode','Mode Share'\n           ]\n\ncol_name = ['Private motorized transport', \n'Rail/Metro/Tram', \n'Buses (including BRT)', \n'Ferries/ River boats', \n'Walking', \n'Cycling', \n'Taxis or For Hire Vehicles']\n\n# selected_cities = {}\n\n# for density in ['very high density','high density','medium density','low density']:\n#     selected_cities[density] = list(total_cities_response[\n#                                                  (total_cities_response['Question Number']==q_num)&\\\n#                                                  (total_cities_response['Column Name'].isin(col_name))&\\\n#                                                  (total_cities_response['Row Name'].isin(row_name))&\\\n#                                                  (total_cities_response['Year Reported to CDP'].isin([2020]))]\\\n#                            [['Organization','Response Answer','density_category','CDP Region','Country']]\\\n#                            .replace({'Question not applicable':np.nan,'0':np.nan})\\\n#                            .dropna()\\\n#                            .groupby(['Organization'])\\\n#                            .count()\\\n#                            .sort_values(by='Organization',ascending=False)\n#                            .index.values)\n\ntransport_mode_perc = {}\n\nfor density in ['very high density','high density','medium density','low density']:\n    for city in selected_cities[density]:\n#         for row_n in row_name:\n            transport_mode_perc[city] = [ total_cities_response[(total_cities_response['density_category']==density)&\\\n                                                     (total_cities_response['Question Number']==q_num)&\\\n                                                     (total_cities_response['Column Name'].isin(col_name))&\\\n                                                     (total_cities_response['Row Name'].isin([row_n]))&\\\n                                                      (total_cities_response['Organization']==city)&\\\n                                                     (total_cities_response['Year Reported to CDP'].isin([2020]))]\\\n                               ['Response Answer'].astype('float').values.sum()\n\n                                                        for row_n in row_name]\n    \n    \ntransport_mode = total_cities_response[\n                                                 (total_cities_response['Question Number']==q_num)&\\\n                                                 (total_cities_response['Column Name'].isin(col_name))&\\\n#                                                  (total_cities_response['Row Name'].isin(row_name))&\\\n                                                 (total_cities_response['Year Reported to CDP'].isin([2020]))]\\\n                           [['Organization','Response Answer','Column Name','density_category']]\\\n.replace({'Question not applicable':np.nan,'0':np.nan})\\\n.dropna()\\\n.sort_values(by=['Organization','Column Name'])\\\n.pivot(index='Organization',columns='Column Name',values='Response Answer')\\\n.fillna(0.0)\\\n.reset_index()\\\n.merge(city_metadata,on='Organization',how='left')\n\ntransport_mode['Mass transport mode usage in %'] = transport_mode[['Buses (including BRT)','Rail/Metro/Tram','Ferries/ River boats']].astype('float').sum(axis=1)\ntransport_mode['Non-motorized mode usage in %'] = transport_mode[['Cycling','Walking']].astype('float').sum(axis=1)\ntransport_mode['Private vehicle based commute mode usage in %'] = transport_mode[['Private motorized transport','Taxis or For Hire Vehicles']].astype('float').sum(axis=1)\n\nworld_transport_mode = world_cities_response[(world_cities_response['CDP Region']=='Europe')&\\\n                                                 (world_cities_response['Question Number']==q_num)&\\\n                                                 (world_cities_response['Column Name'].isin(col_name))&\\\n#                                                  (world_cities_response['Row Name'].isin(row_name))&\\\n                                                 (world_cities_response['Year Reported to CDP'].isin([2020]))]\\\n                           [['Organization','Response Answer','Column Name','density_category']]\\\n.replace({'Question not applicable':np.nan,'0':np.nan})\\\n.dropna()\\\n.sort_values(by=['Organization','Column Name'])\\\n.pivot(index='Organization',columns='Column Name',values='Response Answer')\\\n.fillna(0.0)\\\n.reset_index()\\\n.merge(world_city_metadata,on='Organization',how='left')\n\nworld_transport_mode['Mass transport mode usage in %'] = world_transport_mode[['Buses (including BRT)','Rail/Metro/Tram','Ferries/ River boats']].astype('float').sum(axis=1)\nworld_transport_mode['Non-motorized mode usage in %'] = world_transport_mode[['Cycling','Walking']].astype('float').sum(axis=1)\nworld_transport_mode['Private vehicle based commute mode usage in %'] = world_transport_mode[['Private motorized transport','Taxis or For Hire Vehicles']].astype('float').sum(axis=1)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Here, we will create KPIs that, summarizes the transport mode usage by the population of the cities.\n\nWe will categorize it into 3 KPIs.\n\n### 1. Private vehicle based commute mode usage in %\n### 2. Mass transport mode usage in %\n### 3. Non-motorized mode usage in %\n\nIn below table, if we look at mode share in cities, we can see higher density results into stronger mass transport system. In Low density cities only 4% of the passengers commute by mass transport system and almost equal share resorts to walking or cycling. This is consistent with our findings on carbon intensity/million Btu per capita KPI values for low density segment and answers the following question we raised back there.\n\n<b> Low density cities are doing terrible in transport section. Could this be because of lack of mass transport systems? \n</b>\n\nIt sure is. Although, there are few exceptions in all categories. \n"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"transport_mode_summary = transport_mode.groupby(['density_category'])[['Private vehicle based commute mode usage in %',\n                                             'Mass transport mode usage in %',\n                                             'Non-motorized mode usage in %' \n                                             ]].describe(percentiles=[0.50]).T\\\n.reset_index()\\\n.rename(columns={'level_0':'Transport Mode','level_1':'stat'})\n\ntransport_mode_summary[transport_mode_summary['stat'].isin(['50%','mean'])].style.set_caption('Percentage of population availing different transport modes in USA')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"But how does this fair against the European cities? **European cities are pretty ahead**, it seems. **A lower rate of using private vehicle and almost 10-12% more citizens rely on mass transport systems, even in low density cities, where US is lagging far behind.**\n\n'Non-motorized mode usage in %' is higher across all segments. That's a **clear indication of strong citizen awareness and engagement**. Some of the cities that top the chart are Paris,Berlin,Rotterdam,Copenhagen.\n\nIf we look at BIKEWAYS/SQ.KM AREA KPI, \n\n**<u>Paris scores 700/105 = 16.19km/sq.km,(based on 2015 data)</u>**\n\n**<u>Rotterdam scores 600/325 = 1.84km/sq.km,</u>**\n\n**<u>Copenhagen scores 500/88 = 5.68km/sq.km</u>**\n\nsource: Data from Google"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"world_transport_mode_summary = world_transport_mode.groupby(['density_category'])[['Private vehicle based commute mode usage in %',\n                                             'Mass transport mode usage in %',\n                                             'Non-motorized mode usage in %' \n                                             ]].describe(percentiles=[0.50]).T\\\n.reset_index()\\\n.rename(columns={'level_0':'Transport Mode','level_1':'stat'})\n\nworld_transport_mode_summary[world_transport_mode_summary['stat'].isin(['50%','mean'])]","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Here are the top 10 cycling and walking friendly cities in Europe."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"world_transport_mode.sort_values(by=['Non-motorized mode usage in %'],ascending=False)[['Organization','Mass transport mode usage in %','Non-motorized mode usage in %','Private vehicle based commute mode usage in %']].head(10)\\\n.style.apply(lambda x: ['None','None','background-color: lightgreen','None'],axis=1)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Let's move on to \n# FOOD & WASTE\n\nThe reason for studying food and waste together doesn't need to be mentioned. But since, we are looking into USA data, it's more significant. Why? In United States, food waste is estimated at between 30-40 percent of the food supply. 21.6% of total solid waste generated in US is food waste.\n\nsource : https://www.epa.gov/sites/production/files/2020-11/documents/2018_tables_and_figures_fnl_508.pdf\n\n\nBut this is not a US specific problem. Study of Food and Agriculture Organization (FAO) of the United Nations estimates that if 'global food waste' was a country, it would be the third highest emitter of greenhouse gases after the US and China.\n\nSo a **KPI like,** \n## Estimated amount of recyclable/compostable food waste (tonnes/year)\n=.216*solid waste generated in city (reported in waste section - 13.0)\ncan help city councils in US not only to \n\n**a. monitor and reduce the food waste by enforcing policies.**\n**b. bring down the GHG emission due to total solid waste disposal or incineration/burning of waste**, but also to \n**c. recycle or compost this waste effciently to produce renewable energy.** \n\n\nThus, it will help the energy recovery process. \n\n\nBelow, we can also see '<b>Solid waste generated per capita</b>' KPI values of the cities. "},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"q_num = '13.0'\ncol_name = [\n    'Amount of waste generated (tonnes/year)'\n           ]\n\nrow_name = ['Amount']\n\nsolid_waste_data = total_cities_response[\n#                       (total_cities_response['Organization'].isin(orgs))&\\\n#                                                  (total_cities_response['density_category']==density)&\\\n                                                 (total_cities_response['Question Number']==q_num)&\\\n                                                 (total_cities_response['Column Name'].isin(col_name))&\\\n#                                                  (total_cities_response['Row Name'].isin(row_name))&\\\n                                                 (total_cities_response['Year Reported to CDP'].isin([2020]))]\\\n                           [['Organization','Response Answer']]\\\n                            .replace({'Question not applicable':np.nan,'0':np.nan})\\\n                            .dropna()\\\n                            .rename(columns={'Response Answer':'Solid waste generated (tonnes/year)'}).reset_index()\n\nsolid_waste_data.drop([29],inplace=True)\nsolid_waste_data = solid_waste_data.merge(city_metadata[['Organization','population','density_category']],on='Organization',how='left')\nsolid_waste_data['Estimated amount of recyclable/compostable food waste (tonnes/year)'] = solid_waste_data['Solid waste generated (tonnes/year)'].astype('float')*.216\nsolid_waste_data['Solid waste generated per capita (tonnes/year)'] = solid_waste_data['Solid waste generated (tonnes/year)'].astype('float')/solid_waste_data['population']\nsolid_waste_data.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Per capita solid waste generaton at very high density cities is almost twice of that of medium density cities.\nAlthough, surprisingly low density cities are producing significant per capita solid waste."},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"solid_waste_data.groupby(['density_category'])[['Solid waste generated per capita (tonnes/year)']].median()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"What about sustainability in food policies? We measure, by counting , how many categories a city has already acted on, among the following CDP questions.\n\n1. Do you incentivise fresh fruit/vegetables vendor locations?\n2. Do you subsidise fresh fruits and vegetables?\n3. Do you tax/ban higher carbon foods (meat, dairy, ultra-processed)?\n4. Do you use regulatory mechanisms that limit advertising of higher carbon foods (meat, dairy, ultra-processed)?\n\n## sustainability in food policy KPI\n\n**A sustainability_score_on_food_policy shows lower density in cities are showing a higher sustainability score, based on limited responses, CDP have received from Europe and North American regions** \n\n**<u>Most cities are incentivising fresh fruit/vegetables vendor locations, while preferring not to tax or ban higher carbon foods (meat, dairy, ultra-processed).</u>**\n\n\n"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"q_num = '12.4'\ncol_name = ['Action implemented']\n\nrow_name = ['Amount']\n\nsustainability_score_on_food_policy = world_cities_response[\n                      (world_cities_response['CDP Region'].isin(['Europe','North America']))&\\\n#                                                  (world_cities_response['density_category']==density)&\\\n                                                 (world_cities_response['Question Number']==q_num)&\\\n                                                 (world_cities_response['Column Name'].isin(col_name))&\\\n#                                                  (world_cities_response['Row Name'].isin(row_name))&\\\n                                                 (world_cities_response['Year Reported to CDP'].isin([2020]))]\\\n                           [['Organization','Response Answer','Column Name','Row Name']]\\\n                        .replace({'Question not applicable':np.nan,'0':np.nan})\\\n                           .dropna()\\\n                           .pivot(index='Organization',values='Response Answer',columns='Row Name')\\\n                           .dropna().reset_index()\\\n                           .replace({'No':0,'Do not know':0,'Yes':1}) \n\nsustainability_score_on_food_policy['sustainability_score_on_food_policy'] = sustainability_score_on_food_policy[['Do you incentivise fresh fruit/vegetables vendor locations?',\n       'Do you subsidise fresh fruits and vegetables?',\n       'Do you tax/ban higher carbon foods (meat, dairy, ultra-processed)?',\n       'Do you use regulatory mechanisms that limit advertising of higher carbon foods (meat, dairy, ultra-processed)?']].mean(axis=1)  \nsustainability_score_on_food_policy = sustainability_score_on_food_policy.merge(world_cities_response[['Organization','density_category','CDP Region']].drop_duplicates(),on='Organization',how='left')\n\nsustainability_score_on_food_policy[sustainability_score_on_food_policy['sustainability_score_on_food_policy']>0].groupby(['density_category'])['sustainability_score_on_food_policy']\\\n.mean().sort_values(ascending=False)\\\n.plot.barh(title='Sustainbility score on food policies across density segments');","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"\ntemp = sustainability_score_on_food_policy[['Do you incentivise fresh fruit/vegetables vendor locations?',\n       'Do you subsidise fresh fruits and vegetables?',\n       'Do you tax/ban higher carbon foods (meat, dairy, ultra-processed)?',\n       'Do you use regulatory mechanisms that limit advertising of higher carbon foods (meat, dairy, ultra-processed)?']].sum(axis=0)/sustainability_score_on_food_policy.shape[0]\ntemp = pd.DataFrame(temp).reset_index().rename(columns={0:'values'})\nfig = px.bar(temp, x='index', y='values')\nfig.update_layout(title_text='Percentage of cities opting for food sustainability policies')\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"\n# Corporate Data\n\nSince, we are looking into US data more, here also, we will narrow down on companies who are reporting on their US operations.\nWe have 356 companies responding to climate change questionnaire and 69 companies responding to water security questionnaire, on 2020. We have selected 135 from first 356 companies, who have provided adequate answers to the questions.\n\nHere we will focus on two KPIs, that will show , how corporations are doing in terms of carbon usage in their operations and how they are favouring carbon emission in terms of expenditure.\n\n\nFirst we will look at industry-wise scope1 emissions."},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"# ws_us_comps = corporate_water_security_response[(corporate_water_security_response['survey_year']==2020)&(corporate_water_security_response['question_number']=='W0.3')&(corporate_water_security_response['response_value']=='United States of America')]['organization'].unique()\n\ncc_us_comps = corporate_climate_change_response[(corporate_climate_change_response['survey_year']==2020)&(corporate_climate_change_response['question_number']=='C0.3')&(corporate_climate_change_response['response_value']=='United States of America')]['organization'].unique()\n\nresponse_count = pd.DataFrame(pd.DataFrame(corporate_climate_change_response[(corporate_climate_change_response['organization'].isin(cc_us_comps))&\\\n                                               (corporate_climate_change_response['question_number'].isin(['C4.3b','C6.5','C7.5','C7.6b','C7.3b','C8.2d']))]\\\n             .groupby(['organization',\"module_name\",\"question_number\"]).size()).reset_index().groupby(['organization'])[0].sum()).rename(columns={0:'count'}).reset_index().sort_values(by='count',ascending=False)\n\ncc_us_comps = response_count[response_count['count']>125]['organization'].values\ncorporate_climate_change_response = corporate_climate_change_response[(corporate_climate_change_response['survey_year']==2020)&(corporate_climate_change_response['organization'].isin(cc_us_comps))]\ncc_us_comps = corporate_climate_change_disclosing[(corporate_climate_change_disclosing['survey_year']==2020)&(corporate_climate_change_disclosing['organization'].isin(cc_us_comps))][['organization','activities']].drop_duplicates()\n\ncc_us_comps = cc_us_comps.merge(corporations[['organization','category']],on='organization',how='left')\ncorporate_climate_change_response = corporate_climate_change_response.merge(cc_us_comps,on='organization',how='left')\n\n\n\nq_num = 'C6.1'\nrow_name = ['Reporting year']\ncol_name = ['C6.1_C1Gross global Scope 1 emissions (metric tons CO2e)']\n\nscope1_emission = corporate_climate_change_response[(corporate_climate_change_response['question_number']==q_num)&\\\n                                  (corporate_climate_change_response['column_name'].isin(col_name))&\\\n                                  (corporate_climate_change_response['row_name'].isin(row_name))\n                                 ][['organization','row_name','column_name','response_value']]\\\n.replace('0.0',np.nan)\\\n.dropna()\\\n.pivot(index='organization',values='response_value',columns='row_name')\\\n.reset_index()\\\n.merge(cc_us_comps,on='organization',how='left')\\\n.rename(columns={'Reporting year':'Gross scope 1 emission (metric tons CO2e)'})\n\nscope1_emission['Gross scope 1 emission (metric tons CO2e)'] = scope1_emission['Gross scope 1 emission (metric tons CO2e)'].astype('float')\n\n\nq_num = 'C6.3'\nrow_name = ['Reporting year']\ncol_name = ['C6.3_C1Scope 2, location-based']\n\nscope2_emission_location_based = corporate_climate_change_response[(corporate_climate_change_response['question_number']==q_num)&\\\n                                  (corporate_climate_change_response['column_name'].isin(col_name))&\\\n                                  (corporate_climate_change_response['row_name'].isin(row_name))\n                                 ][['organization','row_name','column_name','response_value']]\\\n.replace('0.0',np.nan)\\\n.dropna()\\\n.pivot(index='organization',values='response_value',columns='row_name')\\\n.reset_index()\\\n.merge(cc_us_comps,on='organization',how='left')\\\n.rename(columns={'Reporting year':'Gross location based scope 2 emission (metric tons CO2e)'})\n\nscope2_emission_location_based['Gross location based scope 2 emission (metric tons CO2e)'] = scope2_emission_location_based['Gross location based scope 2 emission (metric tons CO2e)'].astype('float')\n\n\n\nq_num = 'C6.3'\nrow_name = ['Reporting year']\ncol_name = ['C6.3_C2Scope 2, market-based (if applicable)']\n\nscope2_emission_market_based = corporate_climate_change_response[(corporate_climate_change_response['question_number']==q_num)&\\\n                                  (corporate_climate_change_response['column_name'].isin(col_name))&\\\n                                  (corporate_climate_change_response['row_name'].isin(row_name))\n                                 ][['organization','row_name','column_name','response_value']]\\\n.replace('0.0',np.nan)\\\n.dropna()\\\n.pivot(index='organization',values='response_value',columns='row_name')\\\n.reset_index()\\\n.merge(cc_us_comps,on='organization',how='left')\\\n.rename(columns={'Reporting year':'Gross market based scope 2 emission (metric tons CO2e)'})\n\nscope2_emission_market_based['Gross market based scope 2 emission (metric tons CO2e)'] = scope2_emission_market_based['Gross market based scope 2 emission (metric tons CO2e)'].astype('float')\n\n\n\nq_num = 'C6.5'\nrow_name = ['Purchased goods and services']\ncol_name = ['C6.5_C2Metric tonnes CO2e']\n\nscope3_emission = corporate_climate_change_response[(corporate_climate_change_response['question_number']==q_num)&\\\n                                  (corporate_climate_change_response['column_name'].isin(col_name))\n#                                   (corporate_climate_change_response['row_name'].isin(row_name))\n                                 ][['organization','row_name','column_name','response_value']]\\\n.replace('0.0',np.nan)\\\n.dropna()\\\n.pivot(index='organization',values='response_value',columns='row_name')\\\n.reset_index()\\\n.merge(cc_us_comps,on='organization',how='left')\\\n.fillna(0.0)\n# .rename(columns={'Purchased goods and services':'Gross scope 3 emission (metric tons CO2e)'}).fillna(0.0)\n\n# scope3_emission['Gross scope 3 emission (metric tons CO2e)'] = scope3_emission['Gross scope 3 emission (metric tons CO2e)'].astype('float')\n\n\nscope3_category = ['Business travel', 'Capital goods',\n       'Downstream leased assets',\n       'Downstream transportation and distribution', 'Employee commuting',\n       'End of life treatment of sold products', 'Franchises',\n       'Fuel-and-energy-related activities (not included in Scope 1 or 2)',\n       'Investments', 'Other (downstream)', 'Other (upstream)',\n       'Processing of sold products', 'Purchased goods and services',\n       'Upstream leased assets', 'Upstream transportation and distribution',\n       'Use of sold products', 'Waste generated in operations']\n\nscope3_emission[scope3_category] = scope3_emission[scope3_category].astype('float')\nscope3_emission['Gross scope 3 emission (metric tons CO2e)'] = scope3_emission[scope3_category].sum(axis=1)\n\n\ntemp = []\n\nfor s3_category in scope3_category:\n    temp.append(pd.DataFrame(scope3_emission[scope3_emission[s3_category]>0].groupby(['category'])[s3_category].mean()).T.reset_index())\n    \n    \nscope3_data_category_wise = pd.concat(temp).rename(columns={'level_0':'scope3_category','level_1':'stat','index':'scope3_category'}).fillna(0.0).T\nscope3_data_category_wise.columns=scope3_data_category_wise.iloc[0]\nscope3_data_category_wise.drop(['scope3_category'],inplace=True)\n\n\nscope3_data_category_wise['Sum_of_scope3_category_wise_average_emissions'] = scope3_data_category_wise[scope3_category].sum(axis=1)\n\n\n\ntemp = scope1_emission.groupby(['category'])['Gross scope 1 emission (metric tons CO2e)'].describe(percentiles=[0.5]).sort_values(by=['mean'],ascending=False).reset_index().rename(columns={'mean':'scope 1 avg. emission'})\\\n\ntemp.head(10).style.set_caption('Industry-wise gross scope 1 emission (metric tons CO2e)')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"temp1 = scope3_emission.groupby(['category'])['Gross scope 3 emission (metric tons CO2e)'].describe(percentiles=[0.5]).sort_values(by=['mean'],ascending=False).reset_index().rename(columns={'mean':'scope 2 avg. emission'})\ntemp2 = scope2_emission_market_based.groupby(['category'])['Gross market based scope 2 emission (metric tons CO2e)'].describe(percentiles=[0.5]).sort_values(by=['mean'],ascending=False).reset_index().rename(columns={'mean':'scope 3 avg. emission'})\ntemp3 = temp.merge(temp1,on='category').merge(temp2,on='category')\ntemp3[['category', 'scope 1 avg. emission', \n       'scope 2 avg. emission',  'scope 3 avg. emission']].style.set_caption('Emissions in scope1,2 and 3 across industry categories')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"sum_ghg = temp3[['scope 1 avg. emission','scope 2 avg. emission','scope 3 avg. emission']].sum(axis=1).values\ntemp3['scope 1 avg. emission'] = temp3['scope 1 avg. emission']/sum_ghg\ntemp3['scope 2 avg. emission'] = temp3['scope 2 avg. emission']/sum_ghg\ntemp3['scope 3 avg. emission'] = temp3['scope 3 avg. emission']/sum_ghg\ncategory=temp3.category.values\n\nfig = go.Figure(data=[\n    go.Bar(name='Scope 1 avg. emission', x=category, y=temp3['scope 1 avg. emission']),\n    go.Bar(name='Scope 2 avg. emission', x=category, y=temp3['scope 2 avg. emission']),\n    go.Bar(name='Scope 3 avg. emission', x=category, y=temp3['scope 3 avg. emission']),\n])\n# Change the bar mode\nfig.update_layout(barmode='stack',title={\n        'text': \"Share of GHG emission in scope1,2 and 3 across industries\",\n#         'y':0.9,\n        'x':0.5,\n        'xanchor': 'center',\n        'yanchor': 'top'})\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The plot above shows, how associated GHG emission of a corporation differs in different scopes. Base on the nature of the industry, emission in one scope significantly greater than the emission in other scopes.. Although corporations are not directly responsible for such emissions, they do help such emission favouring product and processes by purchasing/availing them.\n\n\n# KPI - GHG emission favourability by operational cost paid\n\nIn this KPI, we will measure a **corporation's carbon favouraility by calculating, how much GHG it has generated, directly or indirectly, for every 1000 USD it has spent**. not only scope 1 and 2, but corporate expenditure towards high emission favouring upstream or downstream scope 3 activities is one of the root causes , that makes it difficult to break the chain. It aggrevates the problem and motivates creation of a GHG favouring trade environment. With this KPI, we will see, how terrible it is.\n\n\nWe gather expenditure data, as 'Cost and expense' or 'Cost of Goods Sold', disclosed in financial reports, reported to US securities and exchange commission.\n\n**GHG favourability by operational cost = Total GHG emissions in scope1,2 and 3/ Total expenditure**\n\nAs you can see below, how energy plants , especially the ones running on coal and gas are emitting as high as <b><u>19 metric tonnes of Co2e for every 1000 USD spent in operation.</u> </b>\n\n    \nThis may result into cheaper energy for short term, but it's environmental side effects are alarming.     "},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"corp_desc = corporate_climate_change_response[['organization','response_value']].sort_values(by='response_value',ascending=True).drop_duplicates(keep='first').rename(columns={'response_value':'description'})\n\ncorp_desc['nyse_listing'] = corp_desc['description'].apply(lambda x: 'nyse' in str(x).lower())\ncorp_desc = corp_desc.merge(cc_us_comps,on='organization',how='left')\n\n\nfinancial_data = financial_data.merge(tag_data[['adsh','name']].drop_duplicates(),on='adsh',how='left')\n\nmapping = {}\npossible_mismatch = []\n\n\nfor corp in corp_desc['organization'].unique():\n        similarity = 0\n        candidate = 'None'\n    #     print(corp)\n    #     print(corp)\n    #     print(country)\n        for wcorp in tag_data['name'].unique():\n            sim = SequenceMatcher(None, corp.lower(), wcorp.lower()).ratio()\n    #         print(corp,wcorp,sim)\n            if sim>similarity:\n                candidate = wcorp\n                similarity=sim\n\n\n        if similarity<0.8:\n            possible_mismatch.append((corp,candidate))\n        else:\n            mapping[corp] = candidate\n            \n            \n            \n\ncost_corps = []\n\nfor corp in list(mapping.keys())[:-1]:\n# get tags for each corp\n#     corp_adsh = financial_data[financial_data['name']==mapping[corp]]['adsh'].unique()\n    corp_tags = financial_data[(financial_data['name']==mapping[corp])&\\\n                               (financial_data['qtrs']==4)&\\\n                               (financial_data['ddate']==20191231)]['tag'].unique()\n\n    # check presence of the word in tags \n    for tag in corp_tags:\n        if ('CostsAndExpenses'.lower()==tag.lower()) or ('CostOfGoodsAndServicesSold'.lower()==tag.lower()):\n             cost_corps.append(corp)\n             break   \n\nmissing = []\nfor corp in cost_corps:\n    \n    if corp not in scope1_emission.organization.values:\n#         print(corp)\n        missing.append(corp)\n    elif corp not in scope2_emission_location_based.organization.values:\n#         print(corp)\n        missing.append(corp)\n    elif corp not in scope3_emission.organization.values:\n#         print(corp)  \n        missing.append(corp)\n        \nselected_corps = set(cost_corps)-set(missing)        \n\n\n# get corp name\nghg_favourability_kpi = {}\n\nfor corp in list(selected_corps):\n    corp_adsh = financial_data[financial_data['name']==mapping[corp]]['adsh'].unique()\n    corp_tags = financial_data[financial_data['name']==mapping[corp]]['tag'].unique()\n#     print(corp_tags)\n    est_cost = None\n    total_emission = None\n# check if cost & expense data is available\n    if 'CostsAndExpenses' in corp_tags:\n        est_cost = financial_data[(financial_data['name']==mapping[corp])&\\\n                              (financial_data['tag']=='CostsAndExpenses')&\\\n                              (financial_data['qtrs']==4)&\\\n                              (financial_data['ddate']==20191231)]['value'].values[0]   \n#         print(corp,est_cost)\n        # get scope 1, scope 2 , scope 3 emission data\n        scope1_em = scope1_emission['Gross scope 1 emission (metric tons CO2e)'][scope1_emission['organization']==corp].values[0]\n        scope3_em = scope3_emission['Gross scope 3 emission (metric tons CO2e)'][scope3_emission['organization']==corp].values[0]\n        scope2_em = scope2_emission_location_based['Gross location based scope 2 emission (metric tons CO2e)'][scope2_emission_location_based['organization']==corp].values[0]\n    \n        total_emission = scope1_em+scope2_em+scope3_em\n        ghg_favourability = (total_emission/est_cost)*1000\n        ghg_favourability_kpi[corp] = {'GHG favourability by operational cost':ghg_favourability,'estimated cost & expenses':est_cost,'total emission':total_emission}\n\n# if not available, check if COGS data is available\n    elif 'CostOfGoodsAndServicesSold' in corp_tags:\n        est_cost = financial_data[(financial_data['name']==mapping[corp])&\\\n                              (financial_data['tag']=='CostOfGoodsAndServicesSold')&\\\n                              (financial_data['qtrs']==4)&\\\n                              (financial_data['ddate']==20191231)]['value'].values[0]    \n#         print(corp,est_cost)\n        # get scope 1, scope 2 , scope 3 emission data\n        scope1_em = scope1_emission['Gross scope 1 emission (metric tons CO2e)'][scope1_emission['organization']==corp].values[0]\n        scope3_em = scope3_emission['Gross scope 3 emission (metric tons CO2e)'][scope3_emission['organization']==corp].values[0]\n        scope2_em = scope2_emission_location_based['Gross location based scope 2 emission (metric tons CO2e)'][scope2_emission_location_based['organization']==corp].values[0]\n    \n        total_emission = scope1_em+scope2_em+scope3_em\n        ghg_favourability = (total_emission/est_cost)*1000\n        ghg_favourability_kpi[corp] = {'GHG favourability by operational cost':ghg_favourability,'estimated cost & expenses':est_cost,'total emission':total_emission}\n\n    \n    \n    \n    \nghg_favourability = pd.DataFrame(ghg_favourability_kpi).T.sort_values(by='GHG favourability by operational cost',ascending=False).reset_index().rename(columns={'index':'organization'})\\\n.merge(cc_us_comps[['organization','activities']],on='organization',how='left') \n\n\n\nghg_favourability.head(10).style.set_caption('GHG emission in metric tonnes of Co2e for 1000 USD spent in operation')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"sns.distplot(ghg_favourability['GHG favourability by operational cost']);","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Carbon Dependency KPI\n\nCarbon dependency is the growth of carbon usage in terms of scope 1 GHG emission, over the previous year.\nCarbon dependency shows, whether a corporation is making any sustained effort to bring down the emission in the their own operations.\n\n**Carbon dependency =  Scope 1 GHG emission (metric tonnes of Co2) on reporting year/Scope 1 GHG emission (metric tonnes of Co2) on reporting year -1 or previous year** \n\n\nAs the below plot reveals, for some of them, carbon dependency is decreasing in steady manner. But for many, a sustained effort is not reflecting."},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"# carbon dependency\n\n\nq_num = 'C6.1'\nrow_name = ['Reporting year','Past year 1']\ncol_name = ['C6.1_C1Gross global Scope 1 emissions (metric tons CO2e)']\n\ncarbon_dependency = corporate_climate_change_response[(corporate_climate_change_response['question_number']==q_num)&\\\n                                  (corporate_climate_change_response['column_name'].isin(col_name))\n#                                   (corporate_climate_change_response['row_name'].isin(row_name))\n#                                   (corporate_climate_change_response['organization'].isin([temp.organization.values]))  \n                                 ][['organization','row_name','column_name','response_value']]\\\n.replace('0.0',np.nan)\\\n.dropna()\\\n.pivot(index='organization',values='response_value',columns='row_name')\\\n.fillna(0.0)\\\n.reset_index()\\\n.merge(cc_us_comps[['organization','activities','category']],on='organization',how='left')\\\n# .rename(columns={'Reporting year':'Gross scope 1 emission (metric tons CO2e)'})\ncarbon_dependency['Reporting year'] = carbon_dependency['Reporting year'].astype('float')\ncarbon_dependency['Past year 1 '] = carbon_dependency['Past year 1 '].astype('float')\ncarbon_dependency['Past year 2'] = carbon_dependency['Past year 2'].astype('float')\ncarbon_dependency['Past year 3'] = carbon_dependency['Past year 3'].astype('float')\n\ncarbon_dependency['carbon dependency: Reporting year'] = carbon_dependency['Reporting year']/carbon_dependency['Past year 1 '] \ncarbon_dependency['carbon dependency: Reporting year - 1'] = carbon_dependency['Past year 1 ']/carbon_dependency['Past year 2'] \ncarbon_dependency['carbon dependency: Reporting year - 2'] = carbon_dependency['Past year 2']/carbon_dependency['Past year 3']\n\ncarbon_dependency.replace(np.inf,np.nan,inplace=True)\ncarbon_dependency.dropna(0.0,inplace=True)\n# scope1_emission['Gross scope 1 emission (metric tons CO2e)'] = scope1_emission['Gross scope 1 emission (metric tons CO2e)'].astype('float')\n\ncarbon_dependency.drop(['category'],axis=1).reset_index(drop=True).sort_values(by='carbon dependency: Reporting year',ascending=False).head(10)\\\n.style.set_caption('Carbon Dependency of the companies over last 3 years')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"temp = carbon_dependency[['organization','carbon dependency: Reporting year','carbon dependency: Reporting year - 1','carbon dependency: Reporting year - 2']]\\\n.melt(id_vars=['organization'],value_vars=['carbon dependency: Reporting year','carbon dependency: Reporting year - 1','carbon dependency: Reporting year - 2'])\\\n.rename(columns={'variable':'Year','value':'Carbon Dependency'}).sort_values(by='Year',ascending=False)\n\nfig = go.Figure()\n\nfor org in temp['organization'].values:\n    \n    fig.add_trace(go.Scatter(y=temp['Carbon Dependency'][temp['organization']==org], x=temp['Year'][temp['organization']==org],mode='lines+markers',name=org))\n\nfig.update_layout(\n    title={\n        'text': \"Carbon Dependency of corporations over last 3 years\",\n#         'y':0.9,\n        'x':0.5,\n        'xanchor': 'center',\n        'yanchor': 'top'})\n    \nfig.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Conclusion\n\n\n**Last century's solutions are this century's problems.** Perhaps that's the best way to describe the environmental issues, we are facing today.\nIn this notebook,we have explored how current energy generation methods and fuels are damaging two things, most essential for our survival. Air and water. \n\nKPIs that we developed have good capacity to shed lights on present and future dangers , that our cities and corporations are going to face together. We have also discussed , how adaptation efforts as reported by cities can be summarized in one index and can be used against risk mapping KPIs. This will give us insights on , how good or bad cities are responding to the risks, they are facing. We have also tried to assess through KPIs, how much of water recovry can be done and have shown, it could be sufficient enough to ensure, no drought like conditions occur in city or city surroundings.\n\nWe have also shown, how we can create a KPI to measure sustained effort of corporations to reduce GHG emissions. We have developed KPI to measure a corporations favoribility towards GHG emission, in terms of it's expenditure. With more data, we belive these KPIs can further be developed to explore deeper and broader aspects."},{"metadata":{},"cell_type":"markdown","source":"# References \n\n* Corporate Carbon Performance Indicators, Carbon Intensity, Dependency, Exposure, and Risk \n  Volker H. Hoffmann and Timo Busch"}],"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}