{"cells":[{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"import numpy as np  # linear algebra\nimport pandas as pd  # data processing, CSV file I/O (e.g. pd.read_csv)\n\nimport  seaborn as sns\nimport matplotlib.pyplot as plt\nimport plotly_express as px\n\nfrom wordcloud import WordCloud\n\nfrom nltk.corpus import stopwords\nstop = stopwords.words('english') + stopwords.words('spanish') \n\nPATH = '../input/cdp-unlocking-climate-solutions/Cities/'\nPATH2 = '../input/cdp-unlocking-climate-solutions/Corporations/'","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Data Wrangling\n\nStart by loading the data for cities, and merging the response and disclosing dataframes."},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true},"cell_type":"code","source":"# disclosing data\ncd2020 = pd.read_csv(PATH + \"Cities Disclosing/2020_Cities_Disclosing_to_CDP.csv\")\ncd2019 = pd.read_csv(PATH + \"Cities Disclosing/2019_Cities_Disclosing_to_CDP.csv\")\ncd2018 = pd.read_csv(PATH + \"Cities Disclosing/2018_Cities_Disclosing_to_CDP.csv\")\n\ncities_dis = pd.concat([cd2020, cd2019, cd2018])\n\n# response data\ncr2020 = pd.read_csv(PATH + \"Cities Responses/2020_Full_Cities_Dataset.csv\")\ncr2019 = pd.read_csv(PATH + \"Cities Responses/2019_Full_Cities_Dataset.csv\")\ncr2018 = pd.read_csv(PATH + \"Cities Responses/2018_Full_Cities_Dataset.csv\")\n\ncities_res = pd.concat([cr2020, cr2019, cr2018])\n\ncities_dis.columns, cities_res.columns","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Next we join these two dataframes to create a single dataframe `cities` for our queries. Since we're only concerned about survey responses, we perform an inner join on the basis of `Year Reported to CDP` and `Account Number`. The remaining columns used in join depend on `Account Number` and hence do not change for a given row.\n\nThe count of unique cities which responded to each year's questionnaire are given below. "},{"metadata":{"trusted":true},"cell_type":"code","source":"cities = pd.merge(cities_dis, cities_res, 'inner', on=['Year Reported to CDP', 'Account Number', 'Organization', 'Country', 'CDP Region',])\n\n# the following columns contain data we do not need, and are dropped\ncities.drop(columns=['Access', 'File Name', 'Last update_x', 'Last update_y'], inplace=True)\n\ncities.groupby(['Year Reported to CDP'])['Account Number'].nunique()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cities[['Country', 'City', 'Organization', 'Account Number']].drop_duplicates().sort_values(by=['Country'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"# City column may have NaN, use Account Number as unique identifier\ncities[['CDP Region', 'Country', 'Account Number']].drop_duplicates()[['CDP Region', 'Country']].value_counts().sort_values(ascending=False)[:40]\\\n.unstack(0).plot(kind='barh', stacked=True, cmap='tab20', figsize=(12, 8), title='No. of cities contributing to the surveys - by country and region');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"# helper function to query city data from combined dataframe. By default the 2020 responses are returned\n\ndef get_cities(q, by='Row Number', years=[2020], cols=['City', 'Account Number']):\n    val = cities[cities['Question Number']==q]['Question Name']\n    val2 = cities[(cities['Year Reported to CDP'].isin(years)) & (cities['Question Number']==q) & (cities['Response Answer'] != 'Question not applicable')] \\\n    [cols + [by, 'Column Name', 'Response Answer']].dropna(subset=['Response Answer'])\n    sh = val2.shape[0]\n    if sh > 0:\n        print(q, val.iloc[0], f'({sh})')\n    return val2","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Cities\n\nThe 2020 Cities Questionnaire presents the following paren sections for the questions asked with respect to which one may begin discussing the environmental and social issues for cities. (The numbering for the sections is as provided in the questionnaire)\n\n* 1 Governance\n* 2 Climate Hazards and Vulnerability\n* 3 Adaptation\n* 4 City-wide Emissions\n* 5 Emissions Reduction\n* 6 Opportunities\n* 7 Energy\n* 10 Transport\n* 12 Food\n* 13 Waste    \n* 14 Water Security"},{"metadata":{},"cell_type":"markdown","source":"To begin, once we've built our `cities` dataframe (see Data Wrangling), we select a set of key questions from the 2020 Questionnaire which might aid in the identification of KPIs. These are listed together as follows- "},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"# '4.8',\nqs = ['1.0a', '2.0b', '2.1', '2.2', '2.3a', '3.0', '3.2a', '3.3', '4.4', '4.6a',\n      '4.9', '4.15', '5.0a', '5.0b', '5.0c', '5.1', '5.3a', '5.4', '5.5',\n      '6.0', '6.2a', '8.0a', '8.1', '8.4', '8.5a', '9.1', '10.1',  '10.2', \n      '10.4', '10.7a', '12.0', '12.1', '12.3', '12.4', '12.5', '13.0', '14.1', \n      '14.2a', '14.3']\n\nfor q in qs:\n    get_cities(q)   ","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Let us now start to analyse the broad goals and targets cities have set themselves by looking at their response to Question `1.0a`.   \nPlease zoom in if the text on the visualizations is too small to read. Enjoy!\n\nGrouping together the main areas cities have set themelves targets for in their master plan shows a primary focus on waste management and emission reduction, with cities showing a massive increase in their waste management goals from 2019 to 2020. The remaining targets see much smaller year on year changes."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"c1a = get_cities('1.0a', years=[2019, 2020], cols=['Year Reported to CDP', 'City', 'Account Number']) \\\n.pivot(index=['Year Reported to CDP', 'City', 'Account Number', 'Row Number'], columns=['Column Name'])['Response Answer']\nc1a.groupby('Year Reported to CDP')['Goal type'].value_counts().sort_values(ascending=False)[:12] \\\n.unstack(0).plot(kind='barh', title='Main target areas for cities (2019 and 2020)', cmap='tab20', figsize=(10, 6));","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"And how are these targets, as per the cities, going to be addressed? \n\nAnalysin responses for the methods of implementation with the following word cloud provides some insight on this. A lot of emphasis seems to have been given on good management and planning. In addition, we can also infer that a number of plans focus on development and building of new infrastructure and facilities, such as that related to renewable energy, for example."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"answers = c1a['How are these goals/targets addressed in the city master plan?'].astype(str)\nanswers = answers.apply(lambda x: ' '.join([item for item in x.lower().split() if item not in stop and len(item) > 3]))\n# answers.str.split(expand=True).stack().value_counts()[:50]\n\nfig, ax = plt.subplots(figsize=(16,8))       \n\nwordcloud = WordCloud(stopwords=stop,\n                      background_color='white', width=1600,                            \n                      height=800).generate(' '.join(answers))\n\n# ax.imshow(wordcloud, interpolation='bilinear') \nax.set_axis_off()\nplt.title('Word Cloud: Methods to address city goals')\nplt.imshow(wordcloud);","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Next we try to get an idea of the impact of climate change and climate hazards on different sectors, and how these differ from country to country. \n\nWater and sanitation come out to be the most at risk, which is in line with the predictions that a majority of the nations of the world will be facing serious risk to water supply due to the fast diminishing sources of freshwater. The data also tells us that sanitation continues to be one of the most important requirements in developing nations for the minimizations of water borne diseases, many of which are responsible for the extreme loss of life, especially in the case of infants."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"c2b = get_cities('2.0b', cols=['Country'])\nc2b[c2b['Column Name'] == 'Areas/sectors covered by the risk and vulnerability assessment'].groupby(['Response Answer'])['Country'] \\\n.value_counts().sort_values(ascending=False)[:220].unstack()\\\n.plot(kind='barh', stacked=True, figsize=(12, 9), cmap='tab20', title='Sectors vulnerable to climate hazards - by country (2020)').legend(bbox_to_anchor=(1,1));","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The following visualization shows the kinds of services that will be most impacted by the various kinds of climate hazards. \n\nWild fires, heat waves, sea level rise, and extreme precipitation events, such as storms, feature prominently in the reported data. This is not at all surprising given that various parts of the world have already been seeing a very significant increase in events of these types in the last decade. These include but are not limited to the heat waves across Europe, the wildfires in Australia, California, and the Amazon, and gradual increase in the strength and frequency of storms in the United States.\n\nMoreover, water scarcity continues to affect a number of locations across the planet, and it's nice to see these kinds of climate related threats have started to register amongst the top priorities for cities."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"##\nc2_1 = get_cities('2.1')\npiv2_1 = c2_1[c2_1['Column Name'].isin([\n    'Climate Hazards',\n    'Please identify which vulnerable populations are affected',\n    'Most relevant assets / services affected overall',\n    'Social impact of hazard overall'\n])].pivot_table(index=['City', 'Account Number', 'Row Number'], columns='Column Name', aggfunc= lambda x: '^'.join(x))['Response Answer'].groupby('Climate Hazards')\n\n\ngg = piv2_1['Most relevant assets / services affected overall'].apply(lambda x: x.str.split('^').explode()).reset_index() \\\n.groupby('Climate Hazards')['Most relevant assets / services affected overall'].value_counts().unstack()\n\ncols = [col for col in gg.columns if 'Other,' not in col]\ngg[cols].plot(kind='barh', width=0.9, logx=True, figsize=(10, 20), cmap='tab20', title='Climate hazards by the areas most affected (2020)').legend(bbox_to_anchor=(0.5, -0.04));","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Across all kinds of climate hazards it is clearly seen that the poor and elderly are amongst the most adversely affected. \n\nThis includes everyone from those living in low income or sub-standard housing, to marginalized groups which generally make up a large proportion of poor populations as well as those suffering from chronic diseases and health issues, which is again a demographic that skews towards the disenfranchised and poor."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"gg = piv2_1['Please identify which vulnerable populations are affected'].apply(lambda x: x.str.split('^').explode()).reset_index() \\\n.groupby('Climate Hazards')['Please identify which vulnerable populations are affected'].value_counts().unstack()\n\ncols = [col for col in gg.columns if 'Other,' not in col]\ngg[cols].plot(kind='barh',  cmap='tab20', logx=True, width=0.9, figsize=(10,18),title='Climate hazards by populations affected (2020)').legend(bbox_to_anchor=(0.4, -0.05));","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The next visualization gives us an idea of the kind of impact we are likely to see in the world if the goals of climate action and adaptation are not met. \n\nWith the population of the world set to grow up to 10 billion by the year 2050, we can see that there will be an immense shortage of and therefore a huge increase in demand for resources and public services. These two areas, along with a further increase in risk to already vulnerable populations make up the top social impacts across the entire set of climate hazards."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"gg = piv2_1['Social impact of hazard overall'].apply(lambda x: x.str.split('^').explode()).reset_index() \\\n.groupby('Climate Hazards')['Social impact of hazard overall'].value_counts().unstack()\ncols = [col for col in gg.columns if 'Other,' not in col]\ngg[cols].plot(kind='barh', width=0.9, logx=True, figsize=(10, 18), cmap='tab20', title='Climate hazards by social impact (2020)').legend(bbox_to_anchor=(0.5, -0.05));","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The ability to adapt to climate change is an important factor to ensure the thriving of cities. Here we look at the factors most affecting adaptation ability across the globe, and whether they contribute positively or negatively to it.\n\nCities seem to continue to think that throwing money at an issue solves the problem, and the primary challenge to adapting is budgetary constraints. Now this may very well depend on the exact situation on ground, but it does give us a look into the direction of thinking of policy makers and city administrators. Other factors that support adaptability are community and political engagement, access to education, stability, and access to key resources such as data and basic services."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"##\nc2_2 = get_cities('2.2')\nc2_2.pivot(index=['City', 'Account Number', 'Row Number'], columns=['Column Name'])['Response Answer'] \\\n.groupby('Factors that affect ability to adapt')['Indicate if this factor either supports or challenges the ability to adapt'] \\\n.value_counts().sort_values(ascending=False)[:53].unstack() \\\n.plot(kind='barh', cmap='tab20', stacked=True, figsize=(12, 8), title='Factors affecting adaptation ability (2020)');","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"As has become well known in the last few years, as cities such as Beijing and New Delhi continue to be choked with toxic air primarily consisting of harmful PM 2.5 partculate matter, air pollution remains one of the leading issues affecting cities and the health of people living within them. It is estimated that over 4.2 million people lose their lives each year due to air pollution related health complications, such as chronic respiratory diseases, lung cancer, stroke, etc."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"##\nc2_3a = get_cities('2.3a')\nc2_3a[c2_3a['Column Name'].isin([\n    'Identify the climate-related health issues faced by your city', \n])]['Response Answer'].value_counts()[:14].plot(kind='barh', stacked=True, figsize=(12, 7), title='Climate related health issues faced by city (2020)');","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Children, the elderly, and the poor are most affected by climate related health issues owing to the fact that they comprise the most vulnerable populations in any given demographic in part of the world."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"##\nc2_3a[c2_3a['Column Name'].isin([\n    'Please identify which vulnerable populations are affected by these climate-related impacts'\n])]['Response Answer'].value_counts()[:12].plot(kind='barh', stacked=True, figsize=(12, 6), title='Populations vulnerable to Climate related health impacts (2020)');","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Next let's take a look at the actions being taken to mitigate climate risks. \n\nThe following heatmap shows the climate hazards against the actions being implemented/planned by cities to mitigate them. Flood mapping, tree planting, and long term planning stand out as the primary mitigative actions - owing to the increasing concerns due to rising sea levels and overflooding, rising greenhouse effects, and the need for effective planning much in advance of actually facing the climate issues. \n\nIt is essential that we as a planet don't ignore these threats and come up with well thought out plans for the future of humanity in order to prevent the worst case scenario's of climate change."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"##\nc3 = get_cities('3.0')\nfig, ax = plt.subplots(figsize=(16,10))\n\nsns.heatmap(c3[c3['Column Name'].isin(['Climate hazards', 'Action'])].pivot(index=['City', 'Account Number', 'Row Number'], columns=['Column Name'])['Response Answer'] \\\n.groupby('Action')['Climate hazards'].value_counts().sort_values(ascending=False)[:271].unstack(), cmap=\"YlOrRd\", annot=True)\n\nplt.xticks(\n    rotation=65, \n    horizontalalignment='right',\n#     fontweight='light',\n#     fontsize='medium'  \n)\nplt.title('Climate hazards and mitigatory actions taken by cities (2020)')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The same plans visualized above require resources in order to be implemented. Going in hand with that is the urgent need for the development of infrastructure, not just to counteract increasing greenhouse effects, but also to provide safety and shelter to the vulnerable and affected populations, as well as developing the required tools to scientifically study the environment and come up with methods most suited to reverse its causes and prevent its worst impacts. \n\nTo do this, it is even more essential to raise awareness amongst people (through education or awareness raising campaigns) towards the various climate related issues and garner support for the policies required to change our outdated methods of functioning on Earth."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"c3[c3['Column Name'] == 'Means of implementation']['Response Answer'].value_counts()[:12] \\\n.plot(kind='barh', figsize=(12, 7), title='Means of implementation for mitigatory actions (2020)');","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Here are the sectors sorted according to the number of adaptation actions that apply to them as per the reported data. "},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"c3[c3['Column Name'] == 'Sectors/areas adaptation action applies to']['Response Answer'].value_counts()[:13] \\\n.plot(kind='barh', figsize=(12, 7), title='Sectors the actions apply to (2020)');","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Next we see the sectors that correspond to city plans with respect to the adaptation goals of cities. We see that there is a large co-relation between the sectors corresponding to city adaptation actions and city adaptation plans."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"##\nc3_2a = get_cities('3.2a')\nc3_2a[c3_2a['Column Name'] == 'Sectors/areas covered by plan that addresses climate change adaptation']['Response Answer'].value_counts()[:13] \\\n.plot(kind='barh', figsize=(12, 7), title='Sectors covered by climate change adaptation plans (2020)');\n# c3_2a.pivot(index=['City', 'Account Number', 'Row Number'], columns=['Column Name'])['Response Answer']","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The following visualization shows the primary goals cities have set themselves with respect to their adaptation efforts. As seen earlier in this notebook, the key hazards cities seem to be focusing on are those related to extreme temperature and precipitation events such as heat waves and storm events respectively, those that relate to water scarcity and rising sea levels."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"##\nc3_3 = get_cities('3.3')\nc3_3[c3_3['Column Name'] == 'Climate hazards that adaptation goal addresses']['Response Answer'].value_counts() \\\n.plot(kind='barh', figsize=(12,10), title='Climate hazards addressed by adaptations goals (2020)');","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"When coming up with goals, it is important to develop indicators used to monitor the progress towards those goals. In fact, that is the entire point of this CDP competition. Here we try to visualize the kind of metrics cities are using to track their goals by creating a word cloud for the descriptions they provide for their indicators. "},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"answers3_3 = c3_3[c3_3['Column Name'] == 'Description of metric / indicator used to track goal']['Response Answer']\n# .apply(lambda x: ' '.join([item for item in x.lower().split() if item not in stop and len(item) > 3]))\n# answers3_3.str.split(expand=True).stack().value_counts()[:45]\n\nfig, ax = plt.subplots(figsize=(16,8))       \n\nwordcloud = WordCloud(stopwords=stop,\n                      background_color='white', width=1600,                            \n                      height=800).generate(' '.join(answers3_3))\n\nax.set_axis_off()\nplt.title('Word Cloud: Metrics used to track adaptation goals (2020)')\nplt.imshow(wordcloud);","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"When addressing issues affecting vulnerable groups in a city, it is important to focus on the specific communities that are being predominantly impacted. This is what we see happening by looking at the next word cloud analysing responses relating to transformative acion towards vulnerable communities. Other areas of focus seem to be equity, providing support and access to health care, and other initiatives to benefit the people."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"c3_5 = get_cities('3.5')\nanswers3_5 = c3_5['Response Answer']\n# .apply(lambda x: ' '.join([item for item in x.lower().split() if item not in stop and len(item) > 3]))\n# answers3_5.str.split(expand=True).stack().value_counts()[:25]\n\nfig, ax = plt.subplots(figsize=(16,8))       \n\nwordcloud = WordCloud(stopwords=stop,\n                      background_color='white', width=1600,                            \n                      height=800).generate(' '.join(answers3_5))\n\nax.set_axis_off()\nplt.title('Word Cloud: Transformative actions addressing vulnerable groups in cities (2020)')\nplt.imshow(wordcloud);","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"What are the gases most contirbuting to each city's emissions? This is an important question to shape policy around the reduction of activities that lead to increasing Greenhouse effects. The following heatmap shows the GHGs that contribute to city emissions grouped by regions. Methane, CO2 and Nitrous oxide show a greater contribution in each of the regions, whereas the high counts in North America, Europe and Latin America is mainly due to a greater number of contributing cities than other regions."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"c_4_4 = get_cities('4.4', cols=['CDP Region']).drop(['Row Number', 'Column Name'], axis=1)\nplt.subplots(figsize=(10, 6))\nplt.title('Gases contributing to city emissions by region (2020)')\nsns.heatmap(c_4_4.pivot_table(index=['CDP Region'], columns='Response Answer', aggfunc=len), annot=True);","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Next we move to analysing emissions. The following visuzlization displays the average CO2 emissions by region and sector/subsector. Not surprisingly, a number of cities in developing nations in Asia and the Middle east contribute more towards the global emissions especially in sectors related to unclean energy generation and transportation, both areas which have seen unprecedented growth linked to their developing nation status."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"##\nc4_6a = get_cities('4.6a', by='Row Name', cols=['CDP Region', 'City', 'Account Number'])\npiv4_6a = c4_6a.pivot(index=['CDP Region', 'City', 'Account Number', 'Row Name'], columns='Column Name')['Response Answer']\n\n\nplt.subplots(figsize=(12, 10))\nsns.heatmap(piv4_6a['Direct emissions (metric tonnes CO2e)'].dropna().astype('float64', copy=False ) \\\n.groupby(['CDP Region', 'Row Name']).mean().unstack().T, annot=True) \n\nplt.xticks(rotation=65, horizontalalignment='right',)\nplt.title('Mean metric tonnes of CO2 emitted by sector/sub-sector and region (2020)')\n# .plot(kind='barh', width=0.9, figsize=(14, 14), logx=True);\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"A breakdown of city GHG emissions by scope of emissions is provided below."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"##\nc4_6c = get_cities('4.6c', cols=['City', 'Account Number', 'Column Number'])\npiv4_6c = c4_6c.pivot(index=['City', 'Account Number'], columns=['Column Number', 'Column Name'] )['Response Answer']\n# piv4_6c.reindex(sorted(piv4_6c.columns), axis=1)\npiv4_6c.columns = piv4_6c.columns.droplevel()\npiv4_6c = piv4_6c[[\n    'Scope 1 emissions excluding emissions from grid-supplied energy generation', \n    'Scope 1 emissions from grid-supplied energy generation within the city boundary',\n    'Total Scope 2 emissions',\n    'Total Scope 3 emissions'\n]].astype('float32')\npiv4_6c['sum'] = piv4_6c.sum(axis=1)\npiv4_6c.sort_values('sum', ascending=False).drop('sum', axis=1)[:-6] \\\n.plot(kind='barh', cmap='tab20', figsize=(10,12), title='Breakdown of city emissions by scope (2020)', width=0.8, logx=True).legend(bbox_to_anchor=(1,-0.05));\n# .groupby(['CDP Region', 'Row Name']).sum().unstack().T.plot(kind='barh', stacked=True, figsize=(14, 8), logx=True);\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The next visualization analyses the contribution of GHG gases in certain sectors. CO2 and CO2 equivalent emissions contribute the most, mainly in transpotation and energy generation. We also see a significant amount of methane emissions being contributed in relation to livestock rearing. Other gases have relatively lower contributions."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"##\nc4_15 = get_cities('4.15')\npiv4_15 = c4_15[c4_15['Column Name'] != 'Category'].pivot(index=['City', 'Account Number', 'Row Number'], columns=['Column Name'])['Response Answer'] \\\n.groupby(['Applicable sub-sector'])['Gas'].value_counts().unstack()\n\npiv4_15['sum'] = piv4_15.sum(axis=1)\npiv4_15.sort_values('sum', ascending=False).drop('sum', axis=1)[:25] \\\n.plot(kind='barh', cmap='tab20', stacked=True, figsize=(10, 8), title='Gases contributing to sectoral emissions (2020)');","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"A similar visualization now compares GHG gases emitted through certain activies or the burning of specific fuels. Once again we see CO2 and CO2 equivalent emissions are most often found to contribute towards emissions from a variety of different fuels and activities, followed next by methane and nitrous oxide."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"piv4_15 = c4_15[c4_15['Column Name'] != 'Category'].pivot(index=['City', 'Account Number', 'Row Number'], columns=['Column Name'])['Response Answer'] \\\n.groupby(['Fuel type or activity'])['Gas'].value_counts().unstack()\n\npiv4_15['sum'] = piv4_15.sum(axis=1)\npiv4_15.sort_values('sum', ascending=False).drop('sum', axis=1)[:25] \\\n.plot(kind='barh', cmap='tab20', stacked=True, figsize=(12, 8), title='Gases emitted by Fuels and Activities (2020)');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"# c5a['Column Name'].unique()\n###\n# c5a = get_cities('5.0a', year=2019)\n# c5a[~c5a['Column Name'].isin(['Select the initiatives that this target contributes towards'])] \\\n# .pivot(index=['City', 'Account Number', 'Row Number'], columns=['Column Name'])['Response Answer']","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"A good measure for judging the amount of emissions in a city is to calculate the per capita emissions or emission intensity. This gives us a fairer metric to compare different cities on their emissions. The next visualization shows the top cities with maximum emission intensities as per the reported data."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"###\nc5b = get_cities('5.0b', cols=['Country', 'City', 'Account Number'])\npiv5b = c5b[~c5b['Column Name'].isin(['Select the initiatives that this target contributes towards'])] \\\n.pivot(index=['Country', 'City', 'Account Number', 'Row Number'], columns=['Column Name'])['Response Answer'].reset_index().set_index(['City', 'Country', 'Account Number'])\n\nfor col in ['Target year absolute emissions goal (metric tonnes CO2e)', 'Projected population in target year']:\n    piv5b[col] = piv5b[col].astype('float32')\n\npiv5b['per capita'] = piv5b['Target year absolute emissions goal (metric tonnes CO2e)']/piv5b['Projected population in target year']\npiv5b['per capita'].sort_values(ascending=False)[:22].plot(kind='barh', figsize=(12,10), logx=True, title='Per capita emissions (in metric tonnes) in target year (as per reported data 2020)');","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"###\n# c5c = get_cities('5.0c', year=2019)\n# c5c[~c5c['Column Name'].isin(['Select the initiatives that this target contributes towards'])].pivot(index=['City', 'Account Number', 'Row Number'], columns=['Column Name'])['Response Answer']","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"What are the methods for emission transfer applied by cities and the consequent total emissions (in metric tonnes) saved?"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"c5_3a = get_cities('5.3a')\npiv5_3a = c5_3a.pivot(index=['City', 'Account Number', 'Row Number'], columns=['Column Name'])['Response Answer']\npiv5_3a['Emissions saved (metric tonnes CO2e)'] = piv5_3a['Emissions saved (metric tonnes CO2e)'].astype('float64')\npiv5_3a.groupby('Type of transferable emissions')['Emissions saved (metric tonnes CO2e)'].mean().sort_values() \\\n.plot(kind='barh', logx=True, title='Metric tonnes of CO2 emissions saved by type of emission transfer method (2020)');","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Next, we attempt to score some of the mitigation actions the cities are undertaking. This is in order to provide some way to compare the various actions after weighing their costs alongside their benefits.\n\nThe proposed score is calculated by considering the amount of renewable energy a certain action causes to be produced, along with the energy savings and reduction in emissions that occur as a direct consequence of undertaking the action. This is then divided by the total cost of the project to account for the financial resources that need to be directed towards the implementation of the action. This results in the final score for the action.\n\nThe actions sorted by their scores are visualized below, and key areas to focus on turn out to be the transport and transit sectors, which also happen to be large contributors of greenhouse gas emissions. These high scores are expected mainly due to the increasing demand and availability of electric transportation technology. Another cause of this would be due the steep downward trend in the price of batteries, which are used to store energy for electric transportation."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"##\nc5_4 = get_cities('5.4')\npiv5_4 = c5_4[c5_4['Column Name'].isin([\n    'Mitigation action', 'Estimated emissions reduction (metric tonnes CO2e)', 'Energy savings (MWh)', 'Renewable energy production (MWh)', \n    'Total cost of the project', 'Total cost provided by the local government', 'Total cost provided by the majority funding source (currency)'\n])].pivot(index=['City', 'Account Number', 'Row Number'], columns=['Column Name'])['Response Answer']\n\nfor col in piv5_4.columns:\n    if not col == 'Mitigation action':\n        piv5_4[col] = piv5_4[col].astype('float32')\n\nan = piv5_4.groupby('Mitigation action').sum()\nan['score'] = (((an['Energy savings (MWh)'] + an['Renewable energy production (MWh)'])/2) + an['Estimated emissions reduction (metric tonnes CO2e)'])/(1+an['Total cost of the project'])\n# an.sort_values(by='score', ascending=False)\nan['score'].sort_values(ascending=False)[:50].plot(kind='barh', logx=True, figsize=(10, 12), title='Scoring mitigation actions by cost, energy savings, and emission redictions (2020)');","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The following text output box lists key opportunities that have been identified by cities in addressing climate change, along with the keywords mentioned with them in the reported data - sorted by occurence counts."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"##\nc6 = get_cities('6.0')\npiv6 = c6.pivot(index=['City', 'Account Number', 'Row Number'], columns=['Column Name'])['Response Answer']\npiv6['keywords'] = piv6['Describe how the city is maximizing this opportunity'].astype('str') \\\n.apply(lambda x: ' '.join([item for item in x.lower().split() if item not in stop and len(item) > 3]))\n\ns = piv6['keywords'].str.split(expand=True).stack()\ns.index = s.index.droplevel(-1)\ns.name = 'keywords2'\ndff = piv6.drop('keywords', axis=1).join(s)\n\nordered = dff.groupby('Opportunity')['keywords2'].value_counts()\n\nprint('\\n\\nTop opportunity keyword counts:\\n')\nfor opp in ordered.index.get_level_values(0).unique()[:25]:\n    top10 = ordered[opp][:10]\n    print(opp)\n    for k,val in zip(top10.index, top10):\n        print('-', k, val)\n    print()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"City and business collaboration is necessary to adapt to and speed up the fight against climate change. The main areas of collaboration, along with collaborative activities are displayed in the heatmap below. "},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"c6_2a = get_cities('6.2a')\npiv6_2a = c6_2a.pivot(index=['City', 'Account Number', 'Row Number'], columns=['Column Name'])['Response Answer'].drop('Description of collaboration', axis=1) \\\n            .groupby('Collaboration area')['Type of collaboration'].value_counts().unstack()\n\ncols = [col for col in piv6_2a.columns if 'Other,' not in col]\n\nplt.subplots(figsize=(12, 8))\nsns.heatmap(piv6_2a[cols], annot=True);\nplt.xticks(rotation=65, horizontalalignment='right',)\nplt.title('City and business collaborations (2020)')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The following heatmap shows the differences in the sources of energy used to power cities within each region."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"###\n#use region instead of year\nc8_1 = get_cities('8.1', cols=['CDP Region', 'City', 'Account Number'])\npiv8_1 = c8_1.pivot(index=['CDP Region', 'City', 'Account Number'], columns=['Column Name'])['Response Answer'] \\\n.drop(['Total - please ensure this equals 100%', 'Year data applies to'], axis=1)\n\nfor col in piv8_1.columns:\n    piv8_1[col] = piv8_1[col].astype('float64', copy=False )\n#     if not col == 'Year data applies to':\n#         piv8_1[col] = piv8_1[col].fillna(0)\n\n# piv8_1['total'] = piv8_1.drop('Year data applies to', axis=1).dropna(how='all').sum(axis=1)\nplt.subplots(figsize=(10, 7))\nsns.heatmap(piv8_1.groupby(['CDP Region']).mean(), annot=True);\nplt.xticks(rotation=65, horizontalalignment='right',)\nplt.title('Difference in mean source mix of electricity in each region (2020)')\nplt.show()\n# .plot(kind='bar', stacked=True, figsize=(12, 6)).legend(bbox_to_anchor=(1.2, 0.5));","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Renewable energy installed in cities depends in large part on the geographical attributes and locations of the cities that enable certain technologies for energy capture/generation. The next visualization shows the division of renewable energy capacity (in MW) in each region by its source."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"###\nc8_4 = get_cities('8.4', by='Row Name', cols=['CDP Region', 'City', 'Account Number'])\npiv8_4 = c8_4.pivot(index=['CDP Region', 'City', 'Account Number', 'Row Name'], columns=['Column Name'])['Response Answer']\npiv8_4['MW capacity'] = piv8_4['MW capacity'].astype('float32').fillna(0)\n\n# piv8_4['MW capacity'].sort_values(ascending=False)[:60]\npiv8_4['MW capacity'].groupby(['CDP Region', 'Row Name']).mean().unstack() \\\n.plot(kind='barh', cmap='tab20', title='Mean renewable energy capacity (MW) installed in cities by region and source (2020)', figsize=(10, 8), logx=True, width=0.9);","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The predominant form of passenger transport in cities continues to be private vehicles - by a huge margin. \n\nAs such, cities should explore ways to reduce dependence on private vehicles by building the required infrastructe for well connected public transport services. Moreover, green zones within city perimeters, or implementation of surge pricing withing city areas prone to overcrowding,  congestion, or poor air quality could influence a shift towards other forms of travel such as cycling."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"# not my colors\ncolors= ['#003f5c','#2f4b7c','#665191','#a05195','#d45087','#f95d6a','#ff7c43','#ffa600','#fcca46','#a1c181','#619b8a','#386641']\n\nc10_1 = get_cities('10.1', by='Row Name')\npiv10_1 = c10_1.pivot(index=['City', 'Account Number'], columns=['Column Name'])['Response Answer'].fillna(0)\n\nfor col in piv10_1.columns:\n    piv10_1[col] = piv10_1[col].astype('float32')\n    \npiv10_1.mean().plot.pie(labels=None, ylabel='', autopct='%1.1f%%', figsize=(8, 7), pctdistance=0.8, textprops={'color':\"w\"},\n                        title='Mean share of passenger transport in cities (2020)', colors=colors) \\\n.legend(labels=piv10_1.columns, bbox_to_anchor=(1, 0.6));","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"c10_2 = get_cities('10.2', by='Row Name')\npiv10_2 = c10_2.pivot(index=['City', 'Account Number', 'Row Name'], columns=['Column Name'])['Response Answer'].fillna(0).unstack()['Mode share']\nfor col in piv10_2.columns:\n    piv10_2[col] = piv10_2[col].astype('float32')\n    \npiv10_2.mean().plot.pie(autopct='%1.1f%%', figsize=(8, 7), labels=None, ylabel='', pctdistance=0.8, textprops={'color': \"w\"}, colors=colors,\n                        title='Mean share of frieght transport in cities (2020)') \\\n.legend(labels=piv10_2.columns, bbox_to_anchor=(1, 0.6));","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"As discussed above, green zones are important in improving air quality and traffic congestion within city limits. The following bar chart shows the total size (in sq. km) of green zones within countries - as per the reported data."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"c10_7a = get_cities('10.7a', cols=['Country', 'City', 'Account Number'])\npiv10_7a = c10_7a.pivot(index=['Country', 'City', 'Account Number'], columns=['Column Name'])['Response Answer']\npiv10_7a['Size (sq. km)'] = piv10_7a['Size (sq. km)'].astype('float32')\npiv10_7a.groupby('Country')['Size (sq. km)'].sum().sort_values() \\\n.plot.barh(figsize=(10, 8), title='Cumulative size (in sq. km) of zero emission zones in countries (2020)');","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Air pollution primarily consists of the harmful PM2.5 and PM10 matter which passes into the lungs and damages the blood vessels. \n\nAir pollution today is one of the major reasons for health issues for people living in densely populated cities, especially in developing nations whose control on air quality is generally below par. The next visualization presents breakups for concentrations of various constituents of air pollution at different time scales."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"get_cities('10.14', by='Row Name').dropna().pivot(index=['City', 'Account Number', 'Row Name'], columns=['Column Name'])['Response Answer']['Average concentration for most recent year available (ug/m3)'] \\\n.astype('float32').dropna().unstack().plot(kind='barh', cmap='tab20', figsize=(12,20), logx=True, width=0.9, title='Air pollution concentrations by city (2020)');","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The amount of food served by cities shows their dedication towards eliminating hunger. Chennai outshines other cities in terms of total number of meals served."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"c12 = get_cities('12.0', by='Row Name')\npiv12 = c12.pivot(index=['City', 'Account Number'], columns=['Column Name'])['Response Answer'].drop('Comment', axis=1)\n\nfor col in piv12.columns:\n    piv12[col] = piv12[col].astype('float32')\n\npiv12.sort_values(by='Number of meals', ascending=False)[:30].plot(kind='barh', figsize=(10, 12), title='Number and tonnes of meals served/sold through city programs (2020)');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"c12_1 = get_cities('12.1', by='Row Name', cols=['Country', 'City', 'Account Number'])\npiv12_1 = c12_1.pivot(index=['Country', 'City', 'Account Number', 'Row Name'], columns=['Column Name'])['Response Answer']['Amount'].astype('float32').unstack().dropna(how='all')\npiv12_1['sum'] = piv12_1.sum(axis=1)\n\npiv12_1.groupby('Country').sum().sort_values(by='sum', ascending=False).drop('sum', axis=1) \\\n.plot(kind='barh', logx=True, width=0.8, figsize=(10, 12), title='Meat and daiy consumotion by city (2020)');\n# piv12_1.sort_values(by='sum', ascending=False)[:30].drop('sum', axis=1).plot(kind='barh', stacked=True, logx=True, figsize=(12, 7));","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Minimizing city-wide food waste and promoting sustainable foods further shows a city's commitment towards meeting their carbon goals and making the most of their food resources, while alleviating hunger. \n\nThe next two visualizations show how cities say they are promoting sustainable foods, and the total annual food wastage in the city, respectively."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"##\nc12_4 = get_cities('12.4', by='Row Name')\n# c12_4[c12_4['Column Name']=='Action implemented'].pivot(index=['City', 'Account Number'], columns=['Row Name'])['Response Answer']\nc12_4.pivot(index=['City', 'Account Number', 'Row Name'], columns=['Column Name'])['Response Answer'].groupby(['Row Name'])['Action implemented'] \\\n.value_counts().unstack().plot(kind='barh', stacked=True, cmap='tab20', title='Promotion of access to sustainable foods').legend(bbox_to_anchor=(1,1));","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"## w/13\nget_cities('12.5', cols=['CDP Region', 'City', 'Population']).pivot(index=['CDP Region', 'City', 'Population'], columns=['Column Name'])['Response Answer']['Total annual volume of food waste in tonnes'] \\\n.astype('float32').dropna().sort_values(ascending=False).unstack(0).plot(kind='barh', cmap='tab20', stacked=True, figsize=(10, 8), title='Annual food waste in tonnes by city and region (2020)');","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The amount of solid waste generation in a city is a useful metric to judge the amount of recycling and waste management a city might require to undertake. However, when comparing different cities, it would be more useful to compare them on the per capita waste generation. \n\nThe same is presented below in the bar chart sorted to indicate the cities responsible for the maximum per capita solid waste."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"###\nc13 = get_cities('13.0', by='Row Name', cols=['Country', 'City', 'Account Number', 'Population'])\npiv13 = c13.pivot(index=['Country', 'City', 'Account Number', 'Population'], columns=['Column Name'])['Response Answer'].reset_index().set_index(['City', 'Country'])\n\nper_capita_waste = piv13['Amount of waste generated (tonnes/year)'].astype('float32')/piv13['Population'].astype('float32')\n\nper_capita_waste.sort_values(ascending=False)[:50] \\\n.plot(kind='barh', logx=True, stacked=True, figsize=(13, 11), cmap='tab20', title='Per capita solid waste generation by city (2020)');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"c14_1 = get_cities('14.1', cols=['CDP Region', 'Country'])\nc14_1['Response Answer'] = c14_1['Response Answer'].astype('float32')\nc14_1.groupby(['CDP Region', 'Country'])['Response Answer'].mean().unstack(0) \\\n.plot(kind='barh', stacked=True, title='Mean access to potable water supply by country and region (2020)', cmap='tab20', figsize=(10, 14)).legend(bbox_to_anchor=(1,1));","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The next vizualization identifies the main risk drivers affecting water security in cities. These are grouped according to their magnitude and the expected timescales for their materialization. \n\nDeclining water quality and other risks that affect access to water, such as stressed water resources, droughts, and inadequate water supply infrastructure are expected to pose serious issues to cities in the near to medium term."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"##\nc14_2a = get_cities('14.2a')\nplt.subplots(figsize=(12,8))\nsns.heatmap(c14_2a.pivot(index=['City', 'Account Number', 'Row Number'], columns=['Column Name'])['Response Answer'] \\\n.groupby(['Water security risk drivers', 'Estimated magnitude of potential impact'])['Anticipated timescale'].value_counts().unstack().unstack()[:12], annot=True);\n# .plot(kind='barh', stacked=True, figsize=(12,6), cmap='tab20').legend(bbox_to_anchor=(1, 0.6));\n\nplt.xticks(rotation=65, horizontalalignment='right',)\nplt.title('Risks to water security, and their magnitude & timescales (2020)')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The final visualization of this notebook presents the mitigation actions being undertaken to reduce risks to water security. \n\nThe important actions being considered that work with multiple risks are spreading awareness and increasing investment in improving water supply infrastructure. This makes sense considering the need for water conservation requires every individual to do his or her part and since a number of cities report inadequate water supply structure"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"##\nc14_3 = get_cities('14.3')\npiv14_3 = c14_3.pivot(index=['City', 'Account Number', 'Row Number'], columns=['Column Name'])['Response Answer'] \\\n.groupby('Risks')['Adaptation action'].value_counts().sort_values(ascending=False).unstack()[:17]\n\ncols = [col for col in piv14_3.columns if 'Other,' not in col][:-1]\n\npiv14_3[cols].plot(kind='barh', stacked=True, figsize=(12, 8), cmap='tab20', title='Mitigation actions considered and implemented by risk to water security (2020)');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"markdown","source":"Thank you for sticking with this notebook right to the end. I hope this was useful. "}],"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}