![](\"https://storage.googleapis.com/kaggle-competitions/kaggle/22420/logos/header.png\")
\n![](\"https://i.pinimg.com/564x/1b/cb/87/1bcb8744497dca3ac1f44a5540cb3a58.jpg\")
![](\"https://mir-s3-cdn-cf.behance.net/project_modules/fs/191bb570799121.5baf64549e4ab.jpg\")
\n\nPhoto by [Neil V Fernando](https://www.behance.net/gallery/70799121/Editorial-Illustrations)\n![](\"https://static01.nyt.com/images/2015/11/29/opinion/sunday/29sun1/29sun1-jumbo.gif?quality=90&auto=webp\")
\n\n[NYTimes](https://www.nytimes.com/2015/11/29/opinion/sunday/what-the-paris-climate-meeting-must-do.html)\n![](\"https://i.guim.co.uk/img/media/00599b646cc4bf49445f1c8cc2ceb886109cd291/0_0_2560_1536/master/2560.jpg?width=1020&quality=85&auto=format&fit=max&s=e257bb67e7de4462155ae2e654d98c06\")
\n\n$\\begin{align} & CorporateElectricityGreed = \\frac{Total Electricity Consumption_{MWh}}{GlobalElectricityPoverty} \\times 100, \\\\ \\\\ \\text{where } \\\\ & GlobalElectricityPoverty = GlobalPerCapitaElectricityConsumption_{MWh} \\times PeopleWithoutElectricity \\end{align}$\n\n**Rationale** \nLarge corporations are amongst the topmost consumers of electricity in the world. But a standard measure of the amount of electricity consumed doesn't give us any more information than the figures of consumption in say MWh. One useful way to hold companies accountable for their electricity use would be to measure their total consumption as a factor of the global electricity poverty which we define as - _the number of MWh of additional electricity needed to provide adequate access to people currently without electricity._ \n\n---\nAs such, _Corporate Electricity Greed_ directly draws attention to the divide between the consumerism driven mindsets of large corporations and the resource inadequacy and unavailability for the poorest sections of society. "},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"# considering only corporates reporting at least some electricity consumption\ncc8_2a = cc8_2a[cc8_2a[total] > 0]\n\n# converting per capita consumption to MWh\nper_capita_MWh = global_pc_electricity_consumption['Per capita electricity (kWh)']/1000\n\n# calculating electricity greed by dividing corporate consumption by electricity poverty \n# electricity poverty = global per capita consumption X people without electricity\ncc8_2a['Greed'] = cc8_2a[total]*100/(per_capita_MWh * people_without_electricity)\n\ncc8_2a.reset_index().sort_values('Greed', ascending=False)[:20] \\\n.plot.barh(x='organization', y='Greed', figsize=(16,11), legend=False, color=palette[::2] + [grey]*19,\n title='Top 20 Corporations by Corporate Electricity Greed', logx=True);\nplt.plot([100, 100], [0.29, 20], linewidth=5, c=palette[1])\nplt.annotate('Threshold for consumption exceeding Global Electricity Poverty', (106, 17), \n rotation=-90, fontsize=13, fontweight=550)\nplt.gca().invert_yaxis()\n\n# cc8_2a[['C8.2a_C2MWh from renewable sources', 'C8.2a_C3MWh from non-renewable sources']] \\\n# .astype('float32').groupby(['row_name']).sum() \\\n# .plot(kind='barh', figsize=(8, 6), title='Global energy consumption by organizations by source (2020)');","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Visualizing the corporations ranked by electricity greed shows just how vast the divide is, with the top two corporates, Copper Tire & Rubber, and HudBay Minerals Inc., consuming roughly 2x and 0.5x the electricity required to provide enough to every single person who currently lacks it.\n\nIn addition to such indirect, comparitive, issues related to consumption, corporate energy activities have even more serious and direct impacts on human health, and that has to do with the fuels they use to fulfil their energy requirements. These will be addressed with the next metric.\n\n---\nMetric 9: Deaths by Corporate Fuel Use \n_Number of deaths casused due to corporations' choice of fuels for energy generation._\n
\n\n$NoOfDeaths = \\sum_{i=1}^{fuels}Fuel Consumption(TWh)_{i} \\times DeathsPerTWh_{i}$\n
\n\n**Rationale** \nMost of the world's energy continues to be generated by fossil fuels. As per [Markandya, A., & Wilkinson, P. (2007)](https://www.academia.edu/12681524/Electricity_generation_and_health), the use of fossil fuels for energy generation is directly linked to deaths in people due to air pollution related illnesses, or accidents in low visibility conditions. _Deaths by Corporate Fuel Use,_ thereby, provides a means for corporations to assess their impact on human health and safety by enabling them to calculate the number of deaths they might be responsible for due to their energy consumption habits. \n\n---\n\nThe following data on the fuel specific deaths per TWh of energy generated, is sourced from [Our World In Data](https://ourworldindata.org/grapher/death-rates-from-energy-production-per-twh), who themselves partially sourced it from the document linked above."},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"# avg. deaths per TWh of energy generation \n# https://ourworldindata.org/grapher/death-rates-from-energy-production-per-twh\nfuel_deaths = pd.DataFrame([\n ('Biofuels', 0.0048),\n ('Biomass', 4.63),\n ('Brown coal', 32.72),\n ('Coal', 24.62),\n ('Gas', 2.821),\n ('Hydropower', 0.0235),\n ('Nuclear', 0.071),\n ('Oil', 18.43),\n ('Solar', 0.019),\n ('Wind', 0.035)\n], columns=['Fuel Category', 'Deaths per TWh'])\n\nfuel_deaths.style.background_gradient(cmap='Reds')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"# mapping top fuels (by use) from CDP Questionnare Responses to the above given categories\nFUELS = pd.DataFrame([\n ('Biogas', 'Biofuels'),\n ('Biomethane', 'Biofuels'),\n ('Bioethanol', 'Biofuels'),\n ('Biodiesel', 'Biofuels'),\n ('Biodiesel Tallow', 'Biofuels'),\n ('Biodiesel Waste Cooking Oil', 'Biofuels'),\n ('Bamboo', 'Biofuels'),\n ('Black Liquor', 'Biofuels'),\n ('Vegetable Oil', 'Biofuels'),\n ('Animal Fat', 'Biofuels'),\n ('Hardwood', 'Biofuels'),\n ('Softwood', 'Biofuels'),\n ('Wood', 'Biofuels'),\n ('Wood Logs', 'Biofuels'),\n ('Bagasse', 'Biofuels'),\n ('Liquid Biofuel', 'Biofuels'),\n ('Straw', 'Biofuels'),\n ('Biogasoline', 'Biomass'),\n ('Solid Biomass Waste', 'Biomass'),\n ('Biomass Municipal Waste', 'Biomass'),\n ('Dried Sewage Sludge', 'Biomass'),\n ('Sludge Gas', 'Biomass'),\n ('Wood Pellets', 'Biomass'),\n ('Wood Chips', 'Biomass'),\n ('Wood Waste', 'Biomass'),\n ('Agricultural Waste', 'Biomass'),\n ('General Municipal Waste', 'Biomass'),\n ('Lignite Coal', 'Brown coal'),\n ('Brown Coal Briquettes (BKB)', 'Brown Coal'),\n ('Coal', 'Coal'),\n ('GCI Coal', 'Coal'),\n ('PCI Coal', 'Coal'),\n ('Coke', 'Coal'),\n ('Subbituminous Coal', 'Coal'),\n ('Thermal Coal', 'Coal'),\n ('Thermal Coal Commercial', 'Coal'),\n ('Thermal Coal Domestic', 'Coal'),\n ('Thermal Coal Industrial', 'Coal'),\n ('Anthracite Coal', 'Coal'),\n ('Bituminous Coal', 'Coal'),\n ('Coking Coal', 'Coal'),\n ('Metallurgical Coal', 'Coal'),\n ('Natural Gas', 'Gas'),\n ('Natural Gas Liquids (NGL)', 'Gas'),\n ('Natural Gasoline', 'Gas'),\n ('Liquefied Natural Gas (LNG)', 'Gas'),\n ('Compressed Natural Gas (CNG)', 'Gas'),\n ('Fuel Gas', 'Gas'),\n ('Crude Oil', 'Oil'),\n ('Crude Oil Extra Heavy', 'Oil'),\n ('Crude Oil Heavy', 'Oil'),\n ('Crude Oil Light', 'Oil'),\n ('Marine Fuel Oil', 'Oil'),\n ('Light Distillate', 'Oil'),\n ('Refinery Oil', 'Oil'),\n ('Petroleum Coke', 'Oil'),\n ('Petrol', 'Oil'),\n ('Diesel', 'Oil'),\n ('Motor Gasoline', 'Oil'),\n ('Aviation Gasoline', 'Oil'),\n ('Jet Gasoline', 'Oil'),\n ('Jet Kerosene', 'Oil'),\n ('Residual Fuel Oil', 'Oil'),\n ('Distillate Oil', 'Oil'),\n ('Fuel Oil Number 1', 'Oil'),\n ('Fuel Oil Number 2', 'Oil'),\n ('Fuel Oil Number 4', 'Oil'),\n ('Fuel Oil Number 5', 'Oil'),\n ('Fuel Oil Number 6', 'Oil'),\n ('Fuel Oil', 'Oil')\n], columns=['Fuel', 'Fuel Category'])","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Using a mapping between the fuel types present in this data and the list of fuels provided in the CDP questionnaire, we can estimate the death rates for each fuel used by the corporation. Next, joining the mapping with corporate responses for fuels used in energy generation, lets us calculate the death tolls for each corporation."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"# query corporate fuel use data\ncc8_2c = get_climate('C8.2c').pivot(index=['organization', 'row_number'], columns='column_name')['response_value']\n\n# convert from MWh to TWh\ncc8_2c['TWh of fuel consumed'] = (cc8_2c['C8.2c_C3Total fuel MWh consumed by the organization'].astype('float64'))/1000000\ncc8_2c = cc8_2c[cc8_2c['TWh of fuel consumed']!=0]\n\n# join (via fuel category mapping) with fuel deaths dataframe\ncc8_2c = cc8_2c[['C8.2c_C1Fuels (excluding feedstocks)', 'TWh of fuel consumed']] \\\n.join(FUELS.set_index('Fuel'), on='C8.2c_C1Fuels (excluding feedstocks)') \\\n.join(fuel_deaths.set_index('Fuel Category'), on='Fuel Category') \\\n.dropna(subset=['Fuel Category', 'TWh of fuel consumed'])\n\n# calculate number of deaths\ncc8_2c['Deaths'] = cc8_2c['TWh of fuel consumed'] * cc8_2c['Deaths per TWh']\n\ncc8_2c.groupby('organization')[['Deaths']].sum().round().astype('int32'). \\\nsort_values('Deaths', ascending=False)[:20].style.background_gradient(cmap='Reds') \\\n.set_caption('Number of (annual) deaths caused by corporate fuel use')\n\n# ccpiv8_2c.groupby(['organization', 'C8.2c_C1Fuels (excluding feedstocks)'])['C8.2c_C3Total fuel MWh consumed by the organization'].sum() \\\n# .sort_values(ascending=False)[:120].unstack() \\\n# .plot(kind='barh', stacked=True, figsize=(10,14), cmap='tab20', title='Breakdown of fuels consumption in MWh by organizations (2020)');","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Just like we saw in the Emissions section, the top culprits are corporations utilizing large quantities of fossil-fuels for energy generation and transportation.\n\n___\n\nWith nearly a billion people still without access to electricity in the developing world, the sheer scale of corporate energy consumption makes them come off as extremely complicit. Meanwhile, in energy poor areas, besides the obvious hardships, the use of dirty or polluting fuels has serious consequences on people’s health and wellbeing by exacerbating respiratory and cardiac illnesses, and mental health issues.\n\nFurther, by calculating _Deaths by Fuel Use,_ we discover the magnitude of corporate impact on people's lives due large scale corporate fossil-fuel consumption. The number of people affected by the activities of corporations such as Vistra Corp., Duke Energy and American Electric Power Company, shows the toxic nature of the corporations that value profits over life. \n\nIn view of all of this, we advocate that corporations need to take yet more notice of the extended consequences of their actions, and take steps to address the energy access problem. This is not only the right thing to do, but also opens doors to opportunities. For example, The World Bank’s International Finance Corporation (IFC) recently estimated the market size for modern energy services at US$37 billion – roughly equivalent to the amount those without access today spend on kerosene for lighting and biomass for cooking. In addition to the direct revenue opportunities providing access to energy bring, the domino effects - namely broader economic and socio-economic development - can lead to yet more opportunities.[[Link]](https://accntu.re/33rApI0)\n\n\n
7.3 Water Risks
\n\n![](\"https://i.pinimg.com/750x/0d/fd/51/0dfd512f7fb956eacbdf20afefe147ce.jpg\")
\n\nIllustration by [Sebastien Thibault](https://www.behance.net/sebastienthibault)\n\n\n
7.3.1 Hidden Water Costs of Electricity
\n\nThere have been numerous reports in the past year that talk about the depleting water sources, rising droughts and future water shortages to be faced by the world. However, overall action in safeguarding this dwindling resource is falling far short, and according to CDP,[[Link]](https://www.reutersevents.com/sustainability/global-water-crisis-looms-business-usual-no-longer-option) there has been a 50% rise in companies reporting higher water withdrawals since 2015.\n\nCorporations need to take more ambitious steps in order to counteract the impact they have on the planet's water resources, and it isn't just the social and environmental impacts of unsustainable water use that should drive companies to act. Water-related financial losses are rising. In 2018, companies reported such losses to the tune of $38 billion, and even after excluding the top 2 contributors, losses were still higher than in 2017.[[Link]](https://www.ipcc.ch/sr15/chapter/chapter-3/) Through this section, we aim to develop a metric that can be used as a mitigative tool for corporations to track the water consumed for electricity generation.\n\n---\nMetric 10: Water Cost of Electricity \n_Gallons of upstream water consumed for purposes relating to the generation of electricity._\n\n\n$$\\begin{align} & Water Cost_{gallons} = \\sum_{i=1}^{fuels}ElectricityConsumed(kWh)_{i} \\times WaterConsumptionFactor_{i}, \\\\ \n \\text{where, } \\\\\n&WaterConsumptionFactor_{i} = \\text{Gallons of water consumed per kWh electrcity from fuel }i \\end{align} $$\n
\n\n**Rationale** \nFreshwater use for electricity generation accounts for a large proportion of total water consumption across the world. At the same time, the world is not on track to meet SDG 6 - securing water and sanitation for all by 2030, with 1 in 3 people globally without access to safe drinking water.[[Link]](https://www.unicef.org.uk/press-releases/1-in-3-people-globally-do-not-have-access-to-safe-drinking-water-unicef-who/#:~:text=It%20is%20estimated%20that%201,who%20drink%20untreated%20surface%20water) The _Water Cost of Electricity_ allows us to measure the hidden upstream water consumption related to energy generation and use by corporations which may otherwise be used to satisfy the basic water-related needs of people.\n \n---\n\nData on the Water Consumption Factor for fuels is sourced from WRI. Their approach and calculations for the factor score are described in [Guidance for Calculating Water Use Embedded in Purchased Electricity](https://www.wri.org/publication/guidance-calculating-water-use-embedded-purchased-electricity). Since CDP has provided response data only for organizations in the USA and Canada, water consumption factor values for these 2 countries are all that we will need. These are displayed in the table given below."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"water_factor.unstack()['Water Consumption Factor'].drop(['Geothermal', 'Hydro', 'Nuclear', 'Photovoltaics', 'Solar Thermal', 'Wind'], axis=1) \\\n.style.background_gradient('OrRd', axis=1).set_caption('Water Consumption Factor for Fuels (gal/kWh)').set_precision(3)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"To use the water factor values, we must once again create a mapping between the dataset and the responses. Although all fuels from the CDP Questionnaire are not listed in the water factor dataset, we can make some assumptions in order to map them to the most similar fuels.\n\nFor example, Bituminous and Subbituminous coal have similar calorific content compared to Hard Coal (Anthracite), and hence, would result in a similar water consumption factor.\n\n
![\"Coal](\"https://upload.wikimedia.org/wikipedia/commons/thumb/2/21/Coal_Rank_USGS.png/1024px-Coal_Rank_USGS.png\"){kind=icon}
\n\n\n\nIn some of the other cases, we map high-grade derivatives of a fuel to the fuel itself (Natural Gas) with the assumption that derivatives would, in general, have a higher water factor than the raw fuel. The mapping now allows us to calculate our metric. Plotting the top 20 corporations by embedded water consumption shows that the group consists almost exclusively of energy generation companies, which makes a lot of sense."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"# mapping fuels from CDP questionnare to fuels provided in water consumption factor dataset\nFUELS2 = pd.DataFrame([\n ('Anthracite Coal', 'Hard Coal'),\n ('Bituminous Coal', 'Hard Coal'),\n ('Subbituminous Coal', 'Hard Coal'),\n ('Coal', 'Hard Coal'),\n ('Lignite Coal', 'Lignite'),\n ('Peat', 'Peat'),\n ('Natural Gas', 'Natural Gas'),\n ('Fuel Gas', 'Natural Gas'),\n ('Natural Gas Liquids (NGL)', 'Natural Gas'),\n ('Natural Gasoline', 'Natural Gas'),\n ('Liquefied Natural Gas (LNG)', 'Natural Gas'),\n ('Compressed Natural Gas (CNG)', 'Natural Gas'),\n ('Solid Biomass Waste', 'Biomass'),\n ('Biomass Municipal Waste', 'Biomass'),\n ('Dried Sewage Sludge', 'Biomass'),\n ('Sludge Gas', 'Biomass'),\n ('Wood Pellets', 'Biomass'),\n ('Wood Chips', 'Biomass'),\n ('Wood Waste', 'Biomass'),\n ('Agricultural Waste', 'Biomass'),\n ('Hardwood', 'Biomass'),\n ('Softwood', 'Biomass'),\n ('Wood', 'Biomass'),\n ('Wood Logs', 'Biomass'),\n ('Straw', 'Biomass'),\n ('Wood', 'Biomass'),\n ('Wood Waste', 'Biomass'),\n ('Landfill Gas', 'Biogas'),\n ('Biogas', 'Biogas'),\n ('Biomethane', 'Biogas'),\n ('General Municipal Waste', 'Waste'),\n ('Waste Tires', 'Waste'),\n ('Industrial Wastes', 'Waste'),\n ('Non-Biomass Municipal Waste', 'Waste'),\n ('Non-Biomass Waste', 'Waste'),\n ('Waste Paper and Card', 'Waste'),\n ('Waste Plastics', 'Waste'),\n ('Residual Fuel Oil', 'Heavy Fuel Oil'),\n ('Town Gas', 'Coal Gases')\n], columns=['Fuel', 'Fuel Type'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"# query corporate fuel use data again\ncc8_2c = get_climate('C8.2c', cols=['country', 'organization']) \\\n.pivot(index=['country', 'organization', 'row_number'], columns='column_name')['response_value']\n\n# convert electricity consumption from MWh to KWh\ncc8_2c['kWh of electricity generation'] = cc8_2c['C8.2c_C4MWh fuel consumed for self-generation of electricity'].astype('float64') * 1000\ncc8_2c = cc8_2c[cc8_2c['kWh of electricity generation']!=0]\n\n# join with fuel type mapping above \ncc8_2c = cc8_2c[['C8.2c_C1Fuels (excluding feedstocks)', 'kWh of electricity generation']] \\\n.join(FUELS2.set_index('Fuel'), on='C8.2c_C1Fuels (excluding feedstocks)').dropna(subset=['Fuel Type', 'kWh of electricity generation']) \\\n.reset_index().set_index(['country', 'Fuel Type'])\n\n# add water factor data for each fuel\ncc8_2c['water_factor'] = water_factor['Water Consumption Factor']\n\n# calculate gallons consumed for electricity generation\ncc8_2c['gallons'] = cc8_2c['kWh of electricity generation'] * cc8_2c['water_factor']\n\ncc8_2c.groupby(['organization'])['gallons'].sum().sort_values(ascending=False)[:20] \\\n.plot.barh(color=palette[:2] + [grey]*18,\n title='Gallons of Upstream Water Consumption linked to Electricity Generation');\nplt.gca().invert_yaxis()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"It must be noted that these results are somewhat incomplete. Even though we have tried our best to include a majority of the fuels from the CDP Questionnaire, all haven't been accounted for. Sadly, there was no way around this as the WRI data was the best we could find to calculate embedded water consumption in energy use. \n\nOn the plus side, despite of the gap in the data, the KPI does well to highlight just how integral water is to electricity generation. Moreover, it could be applied to calculate hidden water costs for organization facilities located near stressed water resources and generating their own electricity. We would have done this ourselves, using the responses to question `W5.1` of the Water Security Questionnaire, however, there wasn't any data on the total amount of electricity generated/consumed by the facilities. Maybe the 2021 Questionnaire could add this field! :)"},{"metadata":{},"cell_type":"markdown","source":"
7.3.2 Water Stress
\n\n![](\"https://files.wri.org/s3fs-public/styles/large/public/aqueduct_projections_20150409.png?itok=WISZGyCc\")
\n\nImage from [WRI](https://www.wri.org/)\n\n\nWater is a ubiquitous resource. However, massive quantities of it are consumed by multinational corporates to sustain the global scale of their operations. It is, therefore, to be expected that some, if not all, of this water is sourced from water-stressed regions. These may be overstressed river basins, or countries facing issues related to water scarcity at the national level. Due to this, it becomes essential for corporations to account for the level of their water consumption from stressed regions or resources.\n\nWe can use the responses provided by organizations, to visualize their facilities by their total water consumption in stressed and non-stressed regions - as reported by them."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"# using w, c, d for withdrawn, consumption, discharge respectively\nw = 'Total water withdrawals at this facility (megaliters/year)'\nc = 'Total water consumption at this facility (megaliters/year)'\nd = 'Total water discharges at this facility (megaliters/year)'\n\n# querying corporate responses for water accounting data\nw5_1 = get_water('W5.1').pivot(index=['organization', 'account_number', 'row_number'], columns='column_name')['response_value']\nw5_1 = w5_1[[\n 'W5.1_C3Country/Area & River basin_G', 'W5.1_C3Country/Area & River basin', \n 'W5.1_C6Located in area with water stress', 'W5.1_C4Latitude', 'W5.1_C5Longitude',\n 'W5.1_C9' + w, 'W5.1_C23' + c, 'W5.1_C17' + d\n]]\n\n# renaming columns to look nice on visualizations\nw5_1.columns= [\n 'Country/Area',\n 'River basin',\n 'Water Stressed Area',\n 'Latitude',\n 'Longitude',\n w, c, d\n]\n\nw5_1['Water Stressed Area'] = w5_1['Water Stressed Area'].fillna('Unknown')\n\nfor col in w5_1.columns[3:]:\n w5_1[col] = w5_1[col].astype('float32')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"w5_1c = w5_1[w5_1[c]>0]\n\n# size of plot in figure is a function of water consumption\nw5_1c['size'] = np.log10(1 + w5_1c[c])\n\n# visualizing facilites by their consumption and water stress\nfig = px.scatter_geo(w5_1c, lat='Latitude',\n lon='Longitude', \n color='Water Stressed Area',\n hover_data=[w, c, 'Country/Area', 'River basin'],\n# projection=\"natural earth\", \n size='size',\n size_max=12,\n color_discrete_sequence=palette[:2] + [grey],\n title='Self reported water consumption by facilities from stressed/non stressed water resources',\n)\nfig.update_layout(height=480, margin={\"r\":0,\"t\":30,\"l\":0,\"b\":0})\nfig.update_geos(resolution=50, showcountries=True, countrywidth=0.2, showrivers=True, showlakes=True, \n showframe=False, showcoastlines=False, rivercolor='#97B9DD')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Given that a majority of these facilities are located in water stressed regions, we present a way for corporations to evaluate their water consumption.\n\n---\nMetric 11: Unsustainable Water Use \n_Measure of water consumption from water-stressed regions and resources._\n
\n\n$$UnsustainableWaterUse_{riverBasins} = \\sum_{i=1}^{facilites}(WaterStressScore_{riverBasin_{i}} \\times \\log_{10}(1 + WaterConsumption_{i}))$$\n
\n$$UnsustainableWaterUse_{countries} = \\sum_{i=1}^{facilites}(WaterStressScore_{country_{i}} \\times \\log_{10}(1 + WaterConsumption_{i}))$$\n
\n\n**Rationale**\n_Unsustainable Water Use_ takes into consideration the water stress at the river basin and country level for each facility belonging to a corporation, and assigns a pair of scores to the company tied to their total consumption from all facilities. \n\n---\n\nThe water stress scores for the largest, most populous river basins, and for all of the countries are available on WRI as part of their [Aqueduct Country and River Basin Rankings](https://www.wri.org/resources/data-sets/aqueduct-country-and-river-basin-rankings). We use this data to first visualize water stress at the country level."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"# load water stress data for river basins, countries\nwater_stress_basin = pd.read_csv('../input/aqueduct-baseline-water-stress-20132014/aqueduct_basins_baseline_water_stress_20131024.csv').set_index('Name')\n\nwater_stress_country = pd.read_csv('../input/aqueduct-baseline-water-stress-20132014/aqueduct_countries_baseline_water_stress_20140109.csv') \\\n.set_index('Name').replace('-', np.nan).astype('float32')\n# add iso codes to country water stress data\nwater_stress_country = water_stress_country.join(iso_country, on='Name')[['All sectors', 'alpha-3']]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"# visualize baseline water stress country level data\nfig_c = px.choropleth(water_stress_country.reset_index(), locations=\"alpha-3\",\n color=\"All sectors\", # lifeExp is a column of gapminder\n hover_name=\"Name\", # column to add to hover information\n# projection=\"natural earth\",\n# projection='orthographic',\n color_continuous_scale=px.colors.sequential.Reds, \n title='Baseline water stress at country level',\n)\nfig_c.update_layout(height=480, margin={\"r\":0,\"t\":30,\"l\":0,\"b\":0})\nfig_c.update_geos(resolution=50, showcountries=True, countrywidth=0.2, showrivers=True, showlakes=True,\n showframe=False, showcoastlines=False, rivercolor='#97B9DD')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"It can be seen that large swathes of the world are facing high levels of water stress, and barring Central Africa and parts of South America, all continents have above-median baseline water stress scores. These scores will be used as weights for calculating each facilities' contribution towards the corporation's _Unsustainable Water Consumption,_ but to use the water stress data, we must, once again, create a mapping between the river basins in the dataset and those provided in the CDP Questionnaire. The mapping will allow us to directly associate facilities to their corresponding river basin's baseline stress scores as provided in the Aqueduct data."},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"# mapping between river basins in CDP Questionnaire and Water Stress data\nRIVER_BASINS = pd.DataFrame([\n ('Mississippi River', 'Mississippi River'),\n ('St. Lawrence', 'St.Lawrence'),\n ('Yangtze River (Chang Jiang)', 'Yangtze River (Chang Jiang)'),\n ('Sacramento River - San Joaquin River', 'Sacramento San Joaquin'),\n ('Colorado River (Pacific Ocean)', 'Colorado River (Pacific Ocean)'),\n ('Krishna', 'Krishna'),\n ('Ganges - Brahmaputra', 'Ganges Brahmaputra'),\n ('Santiago', 'Santiago'),\n ('Indus', 'Indus'),\n ('Bravo', 'Bravo'),\n ('Mackenzie River', 'Mackenzie River'),\n ('Brazos River', 'Brazos River'),\n ('Godavari', 'Godavari'),\n ('Nile', 'Nile'),\n ('Yongding He', 'Yongding He'),\n ('Huang He (Yellow River)', 'Huang He (Yellow River)'),\n ('Han-Gang (Han River)', 'Han-Gang (Han River)'),\n ('Columbia River', 'Columbia River'),\n ('Parana', 'Parana'),\n ('Murray - Darling', 'Murray'),\n ('Other, please specify: Sabarmati', 'Sabarmati River'),\n ('Wisla', 'Wisla'),\n ('Colorado River (Pacific Ocean)', 'Colorado River (Pacific Ocean)'),\n ('Liao He', 'Liao He'),\n ('Nelson River', 'Nelson River'),\n ('Delaware River', 'Delaware River'),\n ('Other, please specify: Rio Grande - Bravo', 'Bravo'),\n ('Other, please specify: Santiago Guadalajara', 'Santiago'),\n ('Other, please specify: Huangpu River', 'Huangpu Jiang'),\n ('Dead Sea', 'Dead Sea (Jordan)'),\n ('Brantas', 'Brantas'),\n ('Other, please specify: Sabarmati - Sabarmati', 'Sabarmati River'),\n ('Other, please specify: Pennar (Cauvery To Krishna)', 'Krishna'),\n ('Other, please specify: Sacramento', 'Sacramento San Joaquin'),\n ('Other, please specify: Krishna - Delta', 'Krishna'),\n ('Other, please specify: Several locations and basins: Krishna, South coast and East coast', 'Krishna'),\n ('Other, please specify: Sabarmati River', 'Sabarmati River'),\n ('Other, please specify: Krishna – Musi/Aler', 'Krishna'),\n ('Other, please specify: Krishna – Ghod/ Nira/ Bhima/ Man', 'Krishna'),\n ('Other, please specify: Krishna - Krishna/Malpharba/Varna', 'Krishna'),\n ('Other, please specify: Ganges Brahmaputra - Jamni', 'Ganges Brahmaputra'),\n ('Other, please specify: AMA, Colorado River and San Juan Basin', 'Colorado River (Pacific Ocean)'),\n ('Other, please specify: Mackenzie River Basin', 'Mackenzie River'),\n ('Other, please specify: Multiple river basins including Yangtze', 'Yangtze River (Chang Jiang)'),\n ('Other, please specify: Pearl and Yangtze', 'Yangtze River (Chang Jiang)'),\n ('Other, please specify: Several locations and basins: Coast, Xun Jiang, Bo Hai, Ziya He, Huang He, Amur', 'Huang He (Yellow River)'),\n ('Other, please specify: Multiple including Colorado and Mojave', 'Colorado River (Pacific Ocean)'),\n ('Other, please specify: St. Lawrence', 'St.Lawrence'),\n ('Other, please specify: AMAs, Colorado River, and San Juan River Basin', 'Colorado River (Pacific Ocean)'),\n ('Other, please specify: AMAs, Colorado River and Jan Juan River Basin', 'Colorado River (Pacific Ocean)'),\n ('Other, please specify: Citarum River Basin & Brantas River Basin', 'Brantas'),\n ('Other, please specify: GHAASBasin 3724, 3725, 3736; Colorado', 'Colorado River (Pacific Ocean)'),\n ('Other, please specify: Ganges Brahmaputra – Kali Gandaki/ Seti', 'Ganges Brahmaputra'),\n ('Other, please specify: Ganges Brahmaputra – Lakhanadi/Ghuari', 'Ganges Brahmaputra'),\n ('Other, please specify: Bravo, North Gulf, and Interior Basins', 'Bravo'),\n ('Other, please specify: Colorado River', 'Colorado River (Pacific Ocean)')\n], columns=['river_basin_cdp', 'river_basin'])","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Using the mapping, we are able to calculate the _Unsustainable Water Use_ at the basin level, and see that Coca-Cola ranks first by cumulative score across all river basins and facilities. (Probably because of all that coke we've been drinking while we worked...)"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"# joining with water stress data for river basins via river basin mapping\n# ignoring facilities with no consumption\nw5_1a = w5_1[w5_1[c] > 0].join(RIVER_BASINS.set_index('river_basin_cdp'), on='River basin') \\\n.join(water_stress_basin['All sectors'], on='river_basin')\n\n# fill values for river basins not present in water stress data with upper & lower quantile scores for Yes and No, and mean score for Unknown\nw5_1a['All sectors'].fillna(w5_1a['Water Stressed Area'].map({\n 'Yes': water_stress_basin['All sectors'].quantile(0.75), \n 'Unknown': water_stress_basin['All sectors'].mean(), \n 'No': water_stress_basin['All sectors'].quantile(0.25)\n}), inplace=True)\n\n# calculating unsustainable water consumption score for each facility\nw5_1a['score'] = w5_1a['All sectors'] * np.log10(1 + w5_1a[c]) #* w5_1a[c]/w5_1a[w]\n# w5_1a.sort_values(c, ascending=False)\nw5_1a.groupby('organization')['score'].sum().round(3).sort_values(ascending=False)[:20] \\\n.plot.barh(color=palette[:1]+[grey]*19,\n title='Top 20 companies by Unsustainable Water Use (River Basin)');\nplt.gca().invert_yaxis()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The facilities can once more be mapped out, but this time coloured by how they score on _Unsustainable Water Use,_ and overlaid on the baseline water stress at the country level. This enhances our understanding regarding the _contribution_ of these facilities towards increasing the water stress in already stressed regions."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"# calculating average scores for each river basin\nw5_1plot = w5_1a.groupby(['Country/Area', 'River basin', 'Water Stressed Area',\n 'Latitude', 'Longitude'])[[c, 'score']].mean().reset_index()\n\n# visualizing unsustainable water consumption\nfig = px.scatter_geo(w5_1plot, lat='Latitude',\n lon='Longitude', \n color='score',\n hover_data=[c, 'Country/Area', 'River basin'],\n# projection=\"natural earth\", \n size='score',\n size_max=14,\n title='Average Unsustainable Water Use scores for facilities',\n color_continuous_scale=px.colors.sequential.Reds,\n\n)\nfig.update_layout(height=480, margin={\"r\":0,\"t\":30,\"l\":0,\"b\":0})\nfig.update_geos(resolution=50, showcountries=True, countrywidth=0.2, showrivers=True, showlakes=True,\n showframe=False, showcoastlines=False, rivercolor='#97B9DD')\n\nfig.add_trace(\n fig_c.data[0]\n)\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Similarly, we can find country-level _Unsustainable Water Use_ by scoring corporation water use by the country/region they operate in. The scores for the top 20 companies for any single region are presented below. "},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"# change country names to match water stress data\nw5_1['Country/Area'] = w5_1['Country/Area'].replace({\n 'Republic of Korea': 'South Korea',\n 'Taiwan, Greater China': 'Taiwan',\n 'Puerto Rico': '',\n 'United Kingdom of Great Britain and Northern Ireland': 'United Kingdom',\n 'Russian Federation': 'Russia',\n 'Côte d\\'Ivoire': 'Ivory Coast',\n 'Viet Nam': 'Vietnam',\n 'North Macedonia': 'Macedonia',\n 'China, Hong Kong Special Administrative Region': 'Hong Kong'\n})","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"# join water consumption data with water stress scores and iso codes\n# ignoring facilities with no consumption\nw5_1b = w5_1[w5_1[c] > 0].join(water_stress_country[['All sectors', 'alpha-3']], on='Country/Area')\n\n# fill values for countries/regions not present in water stress data with quantile scores for Yes and No, and mean score for Unknown\nw5_1b['All sectors'].fillna(w5_1b['Water Stressed Area'].map({\n 'Yes': water_stress_country['All sectors'].quantile(0.75), \n 'Unknown': water_stress_country['All sectors'].mean(), \n 'No': water_stress_country['All sectors'].quantile(0.25)\n}), inplace=True)\n\n# calculate unsustainbable water use score\nw5_1b['score'] = w5_1b['All sectors'] * np.log10(1 + w5_1b[c]) # * w5_1b[c]/w5_1b[w]\n# create counter to keep track of number of facilities\nw5_1b['facilities'] = 1\n\nw5_1b.groupby(['organization', 'Country/Area'])[['score', c, w, d, 'facilities']].sum() \\\n.sort_values('score', ascending=False)[:20].unstack().plot.barh(y='score', stacked=True, figsize=(12, 9), \ncolor=[qualitative[-3:-2]]+ palette, title='Top 20 companies with highest Unsustainable Water Use in a single Country').legend(title='Country')\nplt.gca().invert_yaxis()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The data skews highly towards the USA simply because the responses are from companies located in the USA and Canada, and it is likely that most of the facilties will be located there too. However, we can still calculate the _mean Unsustainable Water Use_ score for each country to get an idea about how much an average facility located there might be impacting water resources. "},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"# calculate average score for each country\nw5_1plot2 = w5_1b.groupby(['Country/Area', 'alpha-3'])[['score', c, 'facilities']].sum().reset_index()\nw5_1plot2['mean_score'] = w5_1plot2['score']/w5_1plot2['facilities']\n\n# visualize average country level unsustainable water consumption\nfig = px.choropleth(w5_1plot2, locations=\"alpha-3\",\n color=\"mean_score\",\n hover_data=[\"Country/Area\", 'facilities'],\n color_continuous_scale=px.colors.sequential.OrRd,\n# projection='natural earth',\n title='Unsustainable water consumption at country level'\n )\nfig.update_layout(height=440, margin={\"r\":0,\"t\":30,\"l\":0,\"b\":0})\nfig.update_geos(resolution=50, showcountries=True, countrywidth=0.2, showrivers=True, showlakes=True,\n showframe=False, showcoastlines=False, rivercolor='#97B9DD')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"---\n\nClean freshwater is essential for a healthy human life, however, 1.1 billion people lack access to water and 2.7 billion experience water scarcity at least one month a year.[[Link]](https://wwf.to/36vR3YW) The analysis conducted in this section shows how large corporations use massive quantities of water for electricity generation and to conduct their operations. In many of the cases, this water comes from water-stressed areaa, resulting in further aggravation of water-scarcity related issues in these basins, and affecting the local populations and the ecology dependent on these water sources. \n\n---\n\n
8 Collaboration
\n\n![](\"https://media.istockphoto.com/vectors/raised-hands-helping-hands-team-work-collaboration-illustration-vector-id830356322?k=6&m=830356322&s=170667a&w=0&h=Co7OUUpoEjo0Q3ZGPA7liB7e8XIOX3slavvNEycBnzg=\")
\n\n\nIllustration from [iStock](https://media.istockphoto.com/vectors/raised-hands-helping-hands-team-work-collaboration-illustration-vector-id830356322)\n\n\nLooking at the previous sections we see a very disturbing trend of corporate overconsumption against a backdrop of inequality. In all these cases, corporate actions such as GHG emissions, use of unclean fuels, and over consumption of water from stressed resources, are seen to disproportionately affect those who are already prone to worst affects of climate change. This makes it clear that climate change, in addition to being a health issue and an existential issue, is more importantly a social issue. It is a challenge that is extremely complex and does not have a one size fits all solution, but it's a challenge that can drive human innovation to create sustainable solutions.\n\nUsing the Questionnaire data, we start by looking at the ways cities are already collaborating with businesses in the city and then create metrics that encourage collaborative actions."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"# query city respinse data on collaborations\nc6_2a = get_cities('6.2a').pivot(index=['City', 'Account Number', 'Row Number'], columns=['Column Name'])['Response Answer']\n\n# replaceing all self defined catergories with 'Others'\nc6_2a.loc[c6_2a['Type of collaboration'].str.contains('Other, ', na=False), 'Type of collaboration'] = 'Others'\n\ncollab_area = c6_2a.groupby('Collaboration area')['Type of collaboration'].value_counts().unstack()\ncollab_area.fillna(0).astype('int32').style.background_gradient('Reds') #.set_properties(**{'font-size': '20px'}) \n# sns.jointplot();\n\n# c6_2a['Type of collaboration'].value_counts()\n\n# plt.subplots(figsize=(12, 8))\n# sns.heatmap(c6_2a[cols], annot=True);\n# plt.xticks(rotation=65, horizontalalignment='right',)\n# plt.title('City and business collaborations (2020)')\n# plt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"We find that several initiatives are focussed towards Energy. In addition, Transport, Waste, and Water, seem to be noteworthy areas for collaboration. We can see that cities are actively making efforts to collaborate with businesses, so it would be extremely useful if there was a way to measure collaboration ability between a city and a corporation. And so we present our final metric.\n\n---\nMetric 12: Collaboration Strength \n_A measure of the joint interest and expected mutual benefits from collaborative partnerships between cities and corporations on shared risks, opportunities, and initiatives._\n
\n\n$$ CollaborationStrength_{city,org} = SharedRisk \\times CommonOpportunities \\times \\mathtt{SERO}Score_{city,org},$$\n
\n$\\begin{align} &\\text{where } \\quad \\mathtt{SERO}Score_{city,org} = 5 -\\frac{\\sum_{i=1}^{mitigationActions_{city}}\\sum_{j=1}^{reductionInitiatives_{org}}\\mathtt{WordMoversDistance}(i, j)}{No.ofMitigationActions \\times No.ofReductionInitiatives} \\\\ \\\\ & \\text{and } \\quad org \\text{ is a corporation headquartered in } city \\\\ \\\\\n(\\mathtt{SERO} &= \\text{Similarity in Emission Reduction Objectives})\n\\end{align}$\n
\n\n**Rationale** \n_Collaboration Strength_ is a metric that applies to pairs of cities and organizations, and thereby seeks to provide a score for the shared incentives that may be realized through joint initiatives. It is measured by taking into account the shared risks and objectives of cities with the corporations that have a presence in them. The shared objectives themselves are derived by finding overlaps between the city and corporate responses in the CDP Questionnaires. These include common opportunities and similarities in their strategies for emission reduction. _Collaboration Strength,_ therefore, enables cities to find corporations that are most suitable to work with to achieve their climate goals.\n\n---\n\nSince we only consider collaboration possibilities between corporations and cities which contain their headquarters, larger cities with high numbers of listed corporations are going to have more possibilites of finding suitable collaboration partners. The following visualization shows cities by the number of disclosing corporations headquartered in them."},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"collab_frame = corp_locations.join(city_locations[['lon', 'lat']], on=['cleaned_address_city', 'hq_country'], how='inner')\n\nplot3 = collab_frame.groupby(['hq_country', 'cleaned_address_city', 'lon', 'lat'])['cleaned_name'].count().rename('HQ Count')\n\nfig = px.scatter_geo(plot3.reset_index(), lat='lat',\n lon='lon', \n text='cleaned_address_city',\n scope='north america',\n size='HQ Count',\n color_discrete_sequence=palette[:1],\n title='Cities by number of reporting corporate HQs',\n)\nfig.update_layout(height=560, margin={\"r\":0,\"t\":30,\"l\":0,\"b\":0})\nfig.update_geos(showcountries=True, countrywidth=0.2, showcoastlines=False)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Collaboration by Shared Risks\n\nTo find the shared risks between cities and corporations, we return to the risk mapping we had used earlier in our vulnerability assessment for corporations. As a reminder, the mapping is between the climate change and water-related **physical risks** that corporations face and climate hazards and water security risks which cities are prone to. This can be used with the idea that corporations located in cities facing similar issues as them have a greater incentive to work together in tackling these problems."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"# get corporate climate risk data\ncorp_climate_risks = get_climate('C2.3a').pivot(index=['organization', 'account_number', 'row_number'], columns='column_name') \\\n['response_value']['C2.3a_C3Risk type & Primary climate-related risk driver']\n\n# query data from questions on water risks (direct and indirect operations)\nw4_2 = get_water('W4.2').pivot(index=['organization', 'account_number', 'row_number'], columns='column_name')['response_value']\nw4_2a = get_water('W4.2a').pivot(index=['organization', 'account_number','row_number'], columns='column_name')['response_value']\n\n# standardizing columns\n\nw4_2.columns = ['Potential financial impact figure - minimum (currency)',\n 'Potential financial impact figure - maximum (currency)',\n 'Explanation of financial impact',\n 'Primary response to risk', 'Description of response',\n 'Cost of response', 'Explanation of cost of response',\n 'Country/Area & River basin',\n 'Country/Area & River basin_G',\n 'Type of risk & Primary risk driver',\n 'Type of risk & Primary risk driver_G',\n 'Primary potential impact',\n 'Company-specific description', 'Timeframe',\n 'Magnitude of potential impact', 'Likelihood',\n 'Are you able to provide a potential financial impact figure?',\n 'Potential financial impact figure (currency)']\n\nw4_2a.columns = ['Potential financial impact figure (currency)',\n 'Potential financial impact figure - minimum (currency)',\n 'Potential financial impact figure - maximum (currency)',\n 'Explanation of financial impact',\n 'Primary response to risk',\n 'Primary response to risk_G',\n 'Description of response', 'Cost of response',\n 'Explanation of cost of response',\n 'Country/Area & River basin',\n 'Country/Area & River basin_G', 'Stage of value chain',\n 'Type of risk & Primary risk driver',\n 'Type of risk & Primary risk driver_G',\n 'Primary potential impact',\n 'Company-specific description', 'Timeframe',\n 'Magnitude of potential impact', 'Likelihood',\n 'Are you able to provide a potential financial impact figure?']\n\n# combine the 2 dataframes\nw4_2concat = pd.concat([w4_2, w4_2a]).sort_values('organization')\n\n# get corporate water risk data\ncorp_water_risks = w4_2concat['Type of risk & Primary risk driver']\n\n# combine climate and water risks\ncorp_risks = pd.concat([corp_climate_risks, corp_water_risks]).dropna()\ncorp_risks = corp_risks.to_frame().join(corp_locations).reset_index().rename(columns={0: 'corporate_risk'})","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"# get city climate hazards data\nc2_1 = get_cities('2.1', cols=['Country', 'City', 'Account Number'])\ncity_climate_hazards = c2_1[c2_1['Column Name']=='Climate Hazards'].set_index(['Country', 'City', 'Account Number'])['Response Answer']\n\n# get city water risk data\ncity_water_risks = get_cities('14.2a', cols=['Country', 'City', 'Account Number']) \\\n .pivot(index=['Country', 'City', 'Account Number', 'Row Number'], columns='Column Name')['Response Answer']['Water security risk drivers']\n\n# concatenate climate and water risks, and remove data where no city name is given \ncity_risks = pd.concat([city_climate_hazards, city_water_risks]).to_frame().sort_index().reset_index().dropna().rename(columns={0: 'city_risk'})","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"By filtering for common location, and applying the risk mapping to match corporates with cities, we can measure the number of shared risks."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"# join city data with corporate data via the risk mapping defined above\ncollab_frame['Shared Risks'] = corp_risks.join(risk_mapping.set_index('corporate_risk'), on='corporate_risk', how='inner') \\\n .join(city_risks.set_index(['city_risk', 'Country', 'City']), on=['city_risk', 'hq_country', 'cleaned_address_city'], how='inner') \\\n .groupby(['Account Number', 'hq_country', 'cleaned_address_city', 'organization', 'account_number'])['corporate_risk'].count() \\\n .reset_index([0,1,2], drop=True)\n\ncollab_frame.sort_values('Shared Risks', inplace=True)\ntemp = collab_frame.dropna(subset=['Shared Risks'])[-20:].reset_index()\n\nfig, ax = plt.subplots()\nax2 = ax.twinx()\ntemp.plot.barh(x='organization', y='Shared Risks', ax=ax, legend=False, xlabel='Organization',\n title='Top Corporations and Cities ranked by number of Shared Risks');\n\ntemp.plot.barh(x='cleaned_address_city', y='Shared Risks', ax=ax2, legend=False, \n color=[grey]*18 + palette[:2], xlabel='City');","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Collaboration by Identified Opportunities\n\nLikewise, we create a mapping between the common opportunity drivers for cities and corporations. Similar opportunities which are likely to benefit both the city and the corporation would further their interests in collaborating. In the next visualization, we see the top cities and corporations that have similar or related opportunities"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"# mapping between city and corporate opportunity drivers\nopportunity_mapping = pd.DataFrame([\n ('Development of clean energy technologies/businesses', 'Use of more efficient modes of transport'),\n ('Development of sustainable transport sector', 'Use of more efficient modes of transport'),\n ('Development of circular economy models and businesses', 'Use of more efficient production and distribution processes'),\n ('Development of local sustainable food businesses', 'Use of more efficient production and distribution processes'),\n ('Increased opportunities for investment in infrastructure projects', 'Use of more efficient production and distribution processes'),\n ('Increased opportunities for trade (nationally or internationally)', 'Use of more efficient production and distribution processes'),\n ('Development of waste management sector', 'Use of recycling'),\n ('Development of circular economy models and businesses', 'Use of recycling'),\n ('Development of resource conservation and management', 'Use of recycling'),\n ('Reduced risk to natural capital', 'Use of recycling'),\n ('Development of energy efficiency measures and technologies', 'Move to more efficient buildings'),\n ('Increased opportunities for investment in infrastructure projects', 'Move to more efficient buildings'),\n ('Development of sustainable construction/real estate sector', 'Move to more efficient buildings'),\n ('Development of water management sector', 'Reduced water usage and consumption'),\n ('Increased water security', 'Reduced water usage and consumption'),\n ('Development of resource conservation and management', 'Reduced water usage and consumption'),\n ('Development of clean energy technologies/businesses', 'Use of lower-emission sources of energy'),\n ('Development of energy efficiency measures and technologies', 'Use of lower-emission sources of energy'),\n ('Creation/development of carbon markets', 'Use of supportive policy incentives'),\n ('Development of clean energy technologies/businesses', 'Use of supportive policy incentives'),\n ('Development of energy efficiency measures and technologies', 'Use of supportive policy incentives'),\n ('Development of clean energy technologies/businesses', 'Use of new technologies'),\n ('Development of energy efficiency measures and technologies', 'Use of new technologies'),\n ('Carbon tax revenue', 'Participation in carbon market'),\n ('Creation/development of carbon markets', 'Participation in carbon market'),\n ('Development of energy efficiency measures and technologies', 'Shift toward decentralized energy generation'),\n ('Increased energy security', 'Shift toward decentralized energy generation'),\n ('Development of resource conservation and management', 'Development and/or expansion of low emission goods and services'),\n ('Development of circular economy models and businesses', 'Development and/or expansion of low emission goods and services'),\n ('Development of climate change resiliency projects', 'Development of climate adaptation, resilience and insurance risk solutions'),\n ('Development of resource conservation and management', 'Development of climate adaptation, resilience and insurance risk solutions'),\n ('Development of circular economy models and businesses', 'Development of new products or services through R&D and innovation'),\n ('Increase opportunities for partnerships', 'Ability to diversify business activities'),\n ('Increase opportunities for partnerships', 'Shift in consumer preferences'),\n ('Increase opportunities for partnerships', 'Reputational benefits resulting in increased demand for goods/services [Financial services only]'),\n ('Development of energy efficiency measures and technologies', 'Participation in renewable energy programs and adoption of energy-efficiency measures'),\n ('Development of clean energy technologies/businesses', 'Participation in renewable energy programs and adoption of energy-efficiency measures'),\n ('Development of resource conservation and management', 'Resource substitutes/diversification'),\n ('Development of circular economy models and businesses', 'Resource substitutes/diversification'),\n ('Development of climate change resiliency projects', 'Increased reliability, climate- resilience of investment chain'),\n ('Development of water management sector', 'Cost savings'),\n ('Increased opportunities for investment in infrastructure projects', 'Cost savings'),\n ('Increased opportunities for investment in infrastructure projects', 'Improved field recovery factor (oil & gas sector only)'),\n ('Increase opportunities for partnerships', 'Improved field recovery factor (oil & gas sector only)'),\n ('Development of water management sector', 'Improved water efficiency in operations'),\n ('Development of resource conservation and management', 'Improved water efficiency in operations'),\n ('Increased water security', 'Improved water efficiency in operations'),\n ('Development of water management sector', 'Water recovery from sewage management'),\n ('Development of waste management sector', 'Water recovery from sewage management'),\n ('Increased opportunities for investment in infrastructure projects', 'Water recovery from sewage management'),\n ('Development of resource conservation and management', 'Water recovery from sewage management'),\n ('Increased opportunities for investment in infrastructure projects', 'Increased resilience to impacts of climate change'),\n ('Improved flood risk mitigation', 'Increased resilience to impacts of climate change'),\n ('Development of climate change resiliency projects', 'Increased resilience to impacts of climate change'),\n ('Development of climate change resiliency projects', 'Increased supply chain resilience'),\n ('Additional funding opportunities', 'New R&D opportunities'),\n ('Development of circular economy models and businesses', 'Reduced impact of product use on water resources'),\n ('Increased water security', 'Reduced impact of product use on water resources'),\n ('Reduced risk to natural capital', 'Reduced impact of product use on water resources')\n], columns=['city_opportunity', 'corp_opportunity'])\n\nopportunity_mapping[:20].style.set_caption('Sample from the opportunity mapping')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"# creating city opportunity driver dataframe\ncity_opp = c6['Opportunity'].reset_index().set_index(['Opportunity', 'Country', 'City']).drop('Row Number', axis=1)\n\n# get climate chage related opportunity data \nc2_4a = get_climate('C2.4a').pivot(index=['organization', 'account_number', 'row_number'], columns='column_name')['response_value']\n\n# get water related opportunity data\nw4_3a = get_water('W4.3a').pivot(index=['organization', 'account_number', 'row_number'], columns='column_name')['response_value']\n\n# combining climate and water related opportunity drivers for corporates and joining with corporate location data\ncorp_opp = pd.concat([c2_4a['C2.4a_C4Primary climate-related opportunity driver'], w4_3a['W4.3a_C2Primary water-related opportunity']]) \\\n.reset_index().join(corp_locations, on=['organization', 'account_number']).drop('row_number', axis=1).rename(columns={0: 'corp_opp_driver'})\n\n# finding overlap between opportunity drivers for cities and corporates with HQs within them\ncollab_frame['Opportunities'] = corp_opp.join(opportunity_mapping.set_index('corp_opportunity'), on='corp_opp_driver', how='inner') \\\n .join(city_opp, on=['city_opportunity', 'hq_country', 'cleaned_address_city'], how='inner') \\\n .groupby(['Account Number', 'hq_country', 'cleaned_address_city', 'organization', 'account_number'])['corp_opp_driver'].count() \\\n .reset_index([0,1,2], drop=True)\n\ncollab_frame.sort_values('Opportunities', inplace=True)\ntemp = collab_frame.dropna(subset=['Opportunities'])[-20:].reset_index()\n\nfig, ax = plt.subplots()\nax2 = ax.twinx()\n\ntemp[:20].reset_index().plot.barh(x='organization', y='Opportunities', ax=ax, legend=False, xlabel='Organization');\n\ntemp[:20].reset_index().plot.barh(x='cleaned_address_city', y='Opportunities', ax=ax2, legend=False, color=[grey]*18 + palette[:1]*2,\n title='City-Corporate pairs ranked by number of common opportunities');","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Collaboration by Emission Reduction Initiatives\n\nNext, we try to judge the similarity between city and corporate emission reduction strategies. For this, once we have grouped them by location, we calculate Word Mover's Distances (WMD) between each set of responses by a given city/corporate pair. Word Mover's Distance uses pre-trained Word Embeddings to calculate a distance (or negative similarity) score between strings, where an exact match returns 0. For our model, we use Google's [pre-trained word embeddings](https://code.google.com/archive/p/word2vec/). The average WMD distance between a city-corporate pair is then subtracted from 5 to invert values such that a higher similarity results in a hgher SERO score."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"# query city data on emission reduction actions\nc5_4 = get_cities('5.4', cols=['Country', 'City', 'Account Number']) \\\n.pivot_table(index=['Country', 'City', 'Account Number', 'Row Number'], columns=['Column Name'],\n aggfunc=lambda x: x.values[0] if len(list(x))==1 else list(x))['Response Answer']\n\n# create a dataframe by joining corporate initiatives with cities they're headquartered in\nemissions = piv4_3b[['C4.3b_C9Comment']].join(corp_locations) \\\n.join(c5_4.reset_index().set_index(['Country', 'City'])['Scope and impact of action'], on=['hq_country', 'cleaned_address_city'], how='inner')\n\n\n# remove special chars/punctuation/diacritics, numbers, spaces, convert to lowercase, remove stopwords\nemissions['city_initiative'] = emissions['Scope and impact of action'].fillna('') \\\n.apply(lambda x: re.sub(r'[!-/:-@\\[-`\\{-~‘’–]', '', x)).apply(lambda x: re.sub(r'[0-9]', '', x)) \\\n.apply(lambda x: re.sub(r'\\s+', ' ', x.lower().strip())).apply(lambda x: [y for y in x.split() if y not in stop])\n\nemissions['corp_initiative'] = emissions['C4.3b_C9Comment'].fillna('') \\\n.apply(lambda x: re.sub(r'[!-/:-@\\[-`\\{-~‘’–]', '', x)).apply(lambda x: re.sub(r'[0-9]', '', x)) \\\n.apply(lambda x: re.sub(r'\\s+', ' ', x.lower().strip())).apply(lambda x: [y for y in x.split() if y not in stop])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"# load the Word2Vec model\nmodel = gensim.models.KeyedVectors.load_word2vec_format('../input/googles-trained-word2vec-model-in-python/GoogleNews-vectors-negative300.bin', binary=True)\n\n# calculate word mover's distances between corporate and city initiatives to judge similarity\n# this takes 20 mins approx\ntqdm.pandas()\nemissions['wmd_distance'] = emissions.progress_apply(lambda x: model.wmdistance(x['city_initiative'], x['corp_initiative']), axis=1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"# replacing infinities with 4.9 as the max distance\nemissions['wmd_distance'] = emissions['wmd_distance'].replace(np.inf, 4.9)\n\n# the mean of the WMD scores grouped by cities and organizations subracted from 5 gives us the SERO score\ncollab_frame['SERO Score'] = (5 - emissions.groupby(['hq_country', 'cleaned_address_city', 'organization', 'account_number'])['wmd_distance'].mean()) \\\n .reset_index([0,1], drop=True)\n\ncollab_frame.sort_values('SERO Score', inplace=True)\ntemp = collab_frame.dropna(subset=['SERO Score'])[-25:].reset_index()\n\nfig, ax = plt.subplots()\nax2 = ax.twinx()\n\ntemp.plot.barh(x='organization', y='SERO Score', ax=ax, legend=False, xlabel='Organization');\n\ntemp.plot.barh(x='cleaned_address_city', y='SERO Score', ax=ax2, legend=False, color=[grey]*23 + palette[:2],\n title='City-Corporate SERO Scores', xlabel='City');","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Now that we have all the components for the metric we can finally move on to calculating it."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"# calculating Collaboration Strength\ncollab_frame['Collaboration Strength'] = collab_frame['Shared Risks'] * collab_frame['Opportunities'] * collab_frame['SERO Score']\n\ncollab_frame.sort_values('Collaboration Strength', inplace=True)\ntemp = collab_frame.dropna(subset=['Collaboration Strength'])[-25:].reset_index()\n\nfig, ax = plt.subplots()\nax2 = ax.twinx()\n\ntemp.plot.barh(x='organization', y='Collaboration Strength', ax=ax, legend=False, xlabel='Organization');\n\ntemp.plot.barh(x='cleaned_address_city', y='Collaboration Strength', ax=ax2, legend=False, color=[grey]*23 + palette[:2],\n title='Top 20 City/Corporate pair by Collaboration Strength', xlabel='City');","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"This shows us there exist strong incentives for the possibilities of city-corporate collaborations, allowing cities and corporations to tackle the challenges that affect both adversely. \n\nWith more stakeholders, cities have a greater responsibility towards making more positive changes, even so, collaboration efforts should not be limited to those between corporates and cities. Although in this section we do not explore business-business collaboration, the approaches discussed here for city-corporate collaboration might just as easily be applied to businesses looking to work together. \n\nWith the data showing issues like inequality of resources, inequality of climate exposure and health impacts, unequal consumption, and a lack of emphasis on social issues by corporates, there is an urgent need for finding ever more avenues for city-city and business-business collaborations. 21st-century issues like the “Interwovenness of Climate Change and Inequality” require collective action and mass mobilisation. Tackling this issue is the duty of every individual, government and organisation, making it essential for all of humanity to work together in creating a new future that is equitable and habitable. \n\n\n\n