{"cells":[{"metadata":{},"cell_type":"markdown","source":"# CDP: Unlocking Climate Solutions\n\nIn this notebook, the CDP answers given by the cities are analysed, and the main issues are visualized. "},{"metadata":{"_uuid":"b32cdfdf-15f5-462d-a8c9-a4fe0d199446","_cell_guid":"0166dabb-73fc-4eea-a18c-a90840e52cdf","trusted":true},"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python Docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load\n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\nimport matplotlib.pyplot as plt\nimport seaborn as sns\nimport math\nimport geopandas\n\n# Input data files are available in the read-only \"../input/\" directory\n# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory\nlist_df=[]\nimport os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n#         print(os.path.join(dirname, filename))\n        if filename[-4:]=='.csv':\n            df = pd.read_csv(os.path.join(dirname, filename),low_memory=False)\n            vars()['df_'+filename[:-4]] = df\n            list_df.append(['df_'+filename[:-4]])\n            del df \n\n\n# You can write up to 20GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using \"Save & Run All\" \n# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e236a71b-1475-4da2-974a-076f83b2ec42","_cell_guid":"53c93c55-50ed-41ab-8fe0-93f641df766f","trusted":true},"cell_type":"code","source":"## List of questions \n# df_cities_8_a=df_2020_Full_Cities_Dataset.loc[df_2020_Full_Cities_Dataset['Question Number'] == '8.0a']\n# df_cities_8_a.head()\n# df_2020_Full_Cities_Dataset.sort_values(by='Question Number',ascending=True,inplace=True)\n# df_2020_Full_Cities_Dataset.head()\n# questions_city=df_2020_Full_Cities_Dataset['Question Name'].unique()\n# questions_city\n# questions_city_num=df_2020_Full_Cities_Dataset['Question Number'].unique()\n# questions_city_num\n# questions_cities=pd.DataFrame(questions_city,questions_city_num)\n# questions_cities.to_csv('question_list.csv')\n\n# 'df_2020_Full_Climate_Change_Dataset',\n#  'df_2020_Full_Water_Security_Dataset',\n    \n# df_2020_Full_Climate_Change_Dataset.head()\n# df_2020_Full_Climate_Change_Dataset.sort_values(by='question_number',ascending=True,inplace=True)\n# df_2020_Full_Climate_Change_Dataset.head()\n# questions_clima_corp=df_2020_Full_Climate_Change_Dataset['question_unique_reference'].unique()\n# questions_clima_corp\n# questions_clima_corp_num=df_2020_Full_Climate_Change_Dataset['question_number'].unique()\n# questions_clima_corp_num\n# questions_clima_corp=pd.DataFrame(questions_clima_corp,questions_clima_corp_num)\n# questions_clima_corp.to_csv('/kaggle/workingquestion_list_corp_clima.csv')\n\n# df_2020_Full_Water_Security_Dataset.head()\n# df_2020_Full_Water_Security_Dataset.sort_values(by='question_number',ascending=True,inplace=True)\n# df_2020_Full_Water_Security_Dataset.head()\n# questions_water_corp=df_2020_Full_Water_Security_Dataset['question_unique_reference'].unique()\n# questions_water_corp\n# questions_water_corp_num=df_2020_Full_Water_Security_Dataset['question_number'].unique()\n# questions_water_corp_num\n# questions_water_corp=pd.DataFrame(questions_water_corp,questions_water_corp_num)\n# questions_water_corp.to_csv('/kaggle/working/question_list_corp_water.csv')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"dbb620c1-7df8-49b4-9e32-d488ce66c626","_cell_guid":"24448798-0f43-4800-b660-b7bfeb99fa7d","trusted":true},"cell_type":"code","source":"# filename\n# os.path.join(dirname, filename)\n# filename[-3:]=='pdf'\n# filename[:-4]\n# ['df_'+filename[:-4]]\n# list_df\n# # df_2020_Full_Climate_Change_Dataset.head(100)\n# # df_2020_Full_Climate_Change_Dataset['question_unique_reference'].unique()\n# df_2020_Full_Cities_Dataset.head(2)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Cities location\nFirst of all, the cities are located on the map."},{"metadata":{"trusted":true},"cell_type":"code","source":"df_2020_Cities_Disclosing_to_CDP\ncities_pop_loc=df_2020_Cities_Disclosing_to_CDP[['Organization','Population','City Location']].copy()\n# cities_pop_loc=cities_pop_loc.replace('POINT (','')\ncities_pop_loc['City Location']=cities_pop_loc['City Location'].str.replace(\"POINT \\(\", '')\ncities_pop_loc['City Location']=cities_pop_loc['City Location'].str.replace(\"\\)\", '')\n\ncities_pop_loc\ntotal_pop=np.sum(cities_pop_loc['Population'])\ncities_pop_loc[['Lat','Lon']] = cities_pop_loc['City Location'].str.split(\" \",expand=True,)\ncities_pop_loc.head()\ngdf = geopandas.GeoDataFrame(\n    cities_pop_loc, geometry=geopandas.points_from_xy(cities_pop_loc.Lat, cities_pop_loc.Lon))\nworld = geopandas.read_file(geopandas.datasets.get_path('naturalearth_lowres'))\nax = world.plot(\n    color='white', edgecolor='black',figsize=(20,10))\ngdf.plot(ax=ax, color='red')\n\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Population represented in 2020 questionnaire"},{"metadata":{"trusted":true},"cell_type":"code","source":"df_cities_0_5_2020=df_2020_Full_Cities_Dataset.loc[df_2020_Full_Cities_Dataset['Question Number'] == '0.5'].copy()\ndf_cities_0_5_2020=df_cities_0_5_2020.loc[df_cities_0_5_2020['Column Number'] == 1]\npopulation=df_cities_0_5_2020[['Organization','Response Answer']].copy()\npopulation.rename(columns={\"Response Answer\": \"Population\"},inplace=True)\npopulation.set_index('Organization',inplace=True)\npopulation['Population']=population['Population'].astype(float)\ntotal_population=np.sum(population['Population'])\ntotal_population\ntotal_population = f\"{total_population:.0f}\"\ntotal_population\nprint('The total population represented by 2020 questionnaire is',total_population)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Assessments\n## Climate change risks assessment"},{"metadata":{"_uuid":"5550bc99-5bce-43ad-b02c-43dc499d5c32","_cell_guid":"c6fc44e7-f79e-4c2c-bb6f-c074b04875c6","trusted":true},"cell_type":"code","source":"\ndf_cities_2_0_2020=df_2020_Full_Cities_Dataset.loc[df_2020_Full_Cities_Dataset['Question Number'] == '2.0']\n\n# df_2020_Full_Cities_Dataset['Question Number'].unique()\ndf_cities_2_0_2020.groupby('Response Answer').count()\nanswer_2_0_2020=pd.DataFrame(df_cities_2_0_2020.groupby('Response Answer').count()['Organization'])\nanswer_2_0_2020['percent']=(answer_2_0_2020['Organization']/sum(answer_2_0_2020['Organization'])*100).round(1)\nanswer_2_0_2020\nlabels=df_cities_2_0_2020.groupby('Response Answer').count().index\nfig1, ax1 = plt.subplots(figsize=(7,7))\nax1.pie(df_cities_2_0_2020.groupby('Response Answer').count()['Questionnaire'],labels=labels,autopct='%1.1f%%')\nax1.axis('equal')  # Equal aspect ratio ensures that pie is drawn as a circle.\n# plt.title='Does your city have a renewable energy or electricity target?'\nfig1.suptitle('Has a climate change risk and vulnerability assessment been undertaken for your city? [2020]', fontsize=16)\nplt.show()\n\ndf_cities_2_0_2019=df_2019_Full_Cities_Dataset.loc[df_2019_Full_Cities_Dataset['Question Number'] == '2.0']\n# df_2020_Full_Cities_Dataset['Question Number'].unique()\ndf_cities_2_0_2019.groupby('Response Answer').count()\nanswer_2_0_2019=pd.DataFrame(df_cities_2_0_2019.groupby('Response Answer').count()['Organization'])\nanswer_2_0_2019['percent']=(answer_2_0_2019['Organization']/sum(answer_2_0_2019['Organization'])*100).round(1)\nanswer_2_0_2019\n\n\nx = np.arange(len(labels))  # the label locations\nwidth = 0.35  # the width of the bars\nfig, ax = plt.subplots()\n# rects1 = ax.barh(x + width/2, df_cities_2_0_2018.groupby('Response Answer').count()['Questionnaire'], width, label='2018')\nrects1 = ax.barh(x - width/2,answer_2_0_2019['percent'], width, label='2019')\nrects2 = ax.barh(x + width/2, answer_2_0_2020['percent'], width, label='2020')\nax.set_title('Has a climate change risk and vulnerability assessment been undertaken for your city?')\nax.set_yticks(x)\nax.set_yticklabels(labels)\nax.legend()\nrects=rects1+rects2\nfor i in range(len(rects)):\n    percentage = '{:.1f}%'.format(rects[i].get_width())\n    x=rects[i].get_x()+rects[i].get_width()-5\n    y=rects[i].get_y()+0.1\n    ax.annotate(percentage, (x, y))\n#     plt.text(x = x , y = y, s = (rects[i].get_width())+'f%%', size = 12)# for i in range(len(r4)):\nplt.show()\n\n# plt.text(x = r4[i]-0.5 , y = bars4[i]+0.1, s = label[i], size = 6)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Cliamate change risks detected by cities"},{"metadata":{"_uuid":"4a03afe2-2705-4632-8bf0-abbbc594f3c0","_cell_guid":"ac61ff9b-4af5-4c5d-95f1-8d745ebbfae7","trusted":true},"cell_type":"code","source":"df_cities_2_1_2020=df_2020_Full_Cities_Dataset.loc[df_2020_Full_Cities_Dataset['Question Number'] == '2.1'].copy()\ndf_cities_2_1_2020.sort_values(by=['Organization','Column Number','Row Number'],inplace=True)\ndf_cities_2_1_2020_Climate_Hazards=df_cities_2_1_2020.loc[df_cities_2_1_2020['Column Name'] == 'Climate Hazards'].copy()\ndf_cities_2_1_2020_Climate_Hazards=df_cities_2_1_2020_Climate_Hazards.groupby('Response Answer').count()\ndf_cities_2_1_2020_Climate_Hazards.sort_values(by='Organization',ascending=False,inplace=True)\ndf_cities_2_1_2020=df_2020_Full_Cities_Dataset.loc[df_2020_Full_Cities_Dataset['Question Number'] == '2.1'].copy()\ndf_cities_2_1_2020.sort_values(by=['Organization','Row Number'],inplace=True)\n\ncity_name=[]\ncity_risk_list=[]\ncity_current_list=[]\ncity_future_list=[]\ncity_frequency_list=[]\n\nfor i in range(len(df_cities_2_1_2020['Organization'].unique())):\n    city=df_cities_2_1_2020.loc[df_cities_2_1_2020['Organization'] == df_cities_2_1_2020['Organization'].unique()[i]].copy()\n    for j in range(len(city['Row Number'].unique())):\n        city_risk=city.loc[city['Row Number'] == city['Row Number'].unique()[j]].copy()\n        city_risk_magnitude_current=city_risk.loc[city_risk['Column Number'] == 3].copy()\n        city_risk_magnitude_future=city_risk.loc[city_risk['Column Number'] == 10].copy()\n        if city_risk_magnitude_current['Response Answer'].isin(['High','Medium High','Medium']).bool() | city_risk_magnitude_future['Response Answer'].isin(['High','Medium High','Medium']).bool():\n            city_name.append(city_risk_magnitude_current['Organization'].copy().to_string(index=False))\n            city_risk_list.append(city_risk.loc[city_risk['Column Number'] == 1].copy()['Response Answer'].to_string(index=False))\n            city_current_list.append(city_risk.loc[city_risk['Column Number'] == 4].copy()['Response Answer'].to_string(index=False))\n            city_future_list.append(city_risk.loc[city_risk['Column Number'] == 10].copy()['Response Answer'].to_string(index=False))\n            city_frequency_list.append(city_risk.loc[city_risk['Column Number'] == 8].copy()['Response Answer'].to_string(index=False))\n            del (city_risk,city_risk_magnitude_current,city_risk_magnitude_future)\n    del(city)\n\nrisks_cities = pd.DataFrame(list(zip(city_name, city_risk_list,city_current_list,city_future_list,city_frequency_list)), \n               columns =['Organization','Risk','Current risk magnitude','Future risk magnitude','Future risk frequency change']) \nrisks_cities=risks_cities.groupby('Risk').count()\nrisks_cities.sort_values(by='Organization',ascending=True,inplace=True)\nrisk_cities=risks_cities.copy()\nrisk_cities=risk_cities.iloc[math.floor(-len(risks_cities)*0.75):,:]\n\nlabels=risk_cities.index\nx = np.arange(len(labels))  # the label locations\nwidth = 0.35  # the width of the bars\nfig, ax = plt.subplots(figsize=(7, 15))\nrects1 = ax.barh(x + width/2, risk_cities['Organization'], width)\nax.set_title('Climate hazards reported by cities frequency in 2020 \\n Only taken into account the hazards reported to be from medium to high \\n and the 75% most reported',fontsize=20)\nplt.yticks(x, labels,fontsize=20)\nax.set_xlabel('Total amount of the cities that reported the climate hazard',fontsize=20)\nplt.xticks(fontsize=20)\nplt.show()\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"It is interesting that both extrem precipitation and water scarcity are in the TOP 3 of the risks detected by the cities."},{"metadata":{},"cell_type":"markdown","source":"## Climate change health risks assessment"},{"metadata":{"_uuid":"6992b1b9-14d6-4a5f-9905-59c01171addf","_cell_guid":"254975b7-591a-4cde-97e3-40a796e5b28b","trusted":true},"cell_type":"code","source":"\ndf_cities_2_3_2020=df_2020_Full_Cities_Dataset.loc[df_2020_Full_Cities_Dataset['Question Number'] == '2.3']\n\n# df_2020_Full_Cities_Dataset['Question Number'].unique()\ndf_cities_2_3_2020.groupby('Response Answer').count()\nanswer_2_3_2020=pd.DataFrame(df_cities_2_3_2020.groupby('Response Answer').count()['Organization'])\nanswer_2_3_2020['percent']=(answer_2_3_2020['Organization']/sum(answer_2_3_2020['Organization'])*100).round(1)\nanswer_2_3_2020\nlabels=df_cities_2_3_2020.groupby('Response Answer').count().index\nfig1, ax1 = plt.subplots(figsize=(7,7))\npatches, texts, autotexts=ax1.pie(df_cities_2_3_2020.groupby('Response Answer').count()['Questionnaire'],labels=labels,autopct='%1.1f%%')\nplt.setp(autotexts, size=14)\nfor i in range(len(texts)):\n    texts[i].set_fontsize(15)\n\nax1.axis('equal')  # Equal aspect ratio ensures that pie is drawn as a circle.\n# plt.title='Does your city have a renewable energy or electricity target?'\nfig1.suptitle('Is your city facing risks to public health or health systems associated with climate change? [2020] \\n\\n\\n', fontsize=16)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Climate change adaptation plan"},{"metadata":{"trusted":true},"cell_type":"code","source":"df_cities_3_2_2020=df_2020_Full_Cities_Dataset.loc[df_2020_Full_Cities_Dataset['Question Number'] == '3.2']\ndf_cities_3_2_2020.groupby('Response Answer').count()\ndf_cities_3_2_2020=pd.DataFrame(df_cities_3_2_2020.groupby('Response Answer').count()['Organization'])\n# answer_2_0_2020['percent']=(answer_2_0_2020['Organization']/sum(answer_2_0_2020['Organization'])*100).round(1)\n# answer_2_0_2020\nlabels=df_cities_3_2_2020.index\nfig1, ax1 = plt.subplots(figsize=(7,7))\npatches, texts, autotexts=ax1.pie(df_cities_3_2_2020['Organization'],labels=labels,autopct='%1.1f%%')\nplt.setp(autotexts, size=14)\nfor i in range(len(texts)):\n    texts[i].set_fontsize(15)\nax1.axis('equal')  # Equal aspect ratio ensures that pie is drawn as a circle.\n# plt.title='Does your city have a renewable energy or electricity target?'\nfig1.suptitle('Does your city council, or similar authority, have a published plan that addresses climate change adaptation? [2020]', fontsize=16)\nplt.show()\n\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## GHG reduction target"},{"metadata":{"trusted":true},"cell_type":"code","source":"df_cities_5_2020=df_2020_Full_Cities_Dataset.loc[df_2020_Full_Cities_Dataset['Question Number'] == '5.0']\ndf_cities_5_2020.groupby('Response Answer').count()\ndf_cities_5_2020=pd.DataFrame(df_cities_5_2020.groupby('Response Answer').count()['Organization'])\n# answer_2_0_2020['percent']=(answer_2_0_2020['Organization']/sum(answer_2_0_2020['Organization'])*100).round(1)\n# answer_2_0_2020\nlabels=df_cities_5_2020.index\nfig1, ax1 = plt.subplots(figsize=(7,7))\npatches, texts, autotexts=ax1.pie(df_cities_5_2020['Organization'],labels=labels,autopct='%1.1f%%')\nplt.setp(autotexts, size=14)\nfor i in range(len(texts)):\n    texts[i].set_fontsize(15)\nax1.axis('equal')  # Equal aspect ratio ensures that pie is drawn as a circle.\n# plt.title='Does your city have a renewable energy or electricity target?'\nfig1.suptitle('Do you have a GHG emissions reduction target(s) in place at the city-wide level? [2020]', fontsize=16)\nplt.show()\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Climate change mitigation plan"},{"metadata":{"trusted":true},"cell_type":"code","source":"df_cities_5_5_2020=df_2020_Full_Cities_Dataset.loc[df_2020_Full_Cities_Dataset['Question Number'] == '5.5']\n# city=df_cities_5_0b_2020.loc[df_cities_5_0b_2020['Organization'] == df_cities_5_0b_2020['Organization'].unique()[10]].copy()\ndf_cities_5_5_2020=df_cities_5_5_2020.groupby('Response Answer').count()\n# pd.set_option('display.max_rows', 10000)\n# # df_cities_5_5_2020.sort_values(by='Questionnaire',ascending=False,inplace=True)\ndf_cities_5_5_2020=df_cities_5_5_2020['Organization']\n\nlabels=df_cities_5_5_2020.index\nfig1, ax1 = plt.subplots(figsize=(7,7))\npatches, texts, autotexts=ax1.pie(df_cities_5_5_2020,labels=labels,autopct='%1.1f%%')\nplt.setp(autotexts, size=14)\nfor i in range(len(texts)):\n    texts[i].set_fontsize(15)\nax1.axis('equal')  # Equal aspect ratio ensures that pie is drawn as a circle.\n# plt.title='Does your city have a renewable energy or electricity target?'\nfig1.suptitle('Does your city have a climate change mitigation or energy access plan for reducing city-wide GHG emissions? [2020]', fontsize=16)\nplt.show()\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Opportunities climate change adressing"},{"metadata":{"trusted":true},"cell_type":"code","source":"df_cities_6_2020=df_2020_Full_Cities_Dataset.loc[df_2020_Full_Cities_Dataset['Question Number'] == '6.0']\ndf_cities_6_2020=df_cities_6_2020.loc[df_cities_6_2020['Column Name'] == 'Opportunity']\ndf_cities_6_2020=df_cities_6_2020.groupby('Response Answer').count()\ndf_cities_6_2020.sort_values('Organization',ascending=True,inplace=True)\ndf_cities_6_2020=df_cities_6_2020.loc[df_cities_6_2020['Organization'] >3]\ndf_cities_6_2020=df_cities_6_2020['Organization']\n\nlabels=df_cities_6_2020.index\nx = np.arange(len(labels))  # the label locations\nwidth = 0.35  # the width of the bars\nfig, ax = plt.subplots(figsize=(10, 20))\nrects1 = ax.barh(x + width/2, df_cities_6_2020, width)\nax.set_title('Opportunities identified by cities as a result of addressing climate change',fontsize=25)\nplt.yticks(x, labels,fontsize=20)\nax.set_xlabel('Total amount of the cities that reported the opportunities',fontsize=20)\nplt.xticks(fontsize=20)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Energy target"},{"metadata":{"_uuid":"78096060-e9b2-4d2a-9911-eb3bf35d2380","_cell_guid":"2380f910-9606-408b-904e-6f14a2ba4dc4","trusted":true},"cell_type":"code","source":"df_cities_8_0_2020=df_2020_Full_Cities_Dataset.loc[df_2020_Full_Cities_Dataset['Question Number'] == '8.0']\n\n# df_2020_Full_Cities_Dataset['Question Number'].unique()\ndf_cities_8_0_2020.groupby('Response Answer').count()\nanswer_8_0_2020=pd.DataFrame(df_cities_8_0_2020.groupby('Response Answer').count()['Organization'])\nanswer_8_0_2020['percent']=(answer_8_0_2020['Organization']/sum(answer_8_0_2020['Organization'])*100).round(1)\nanswer_8_0_2020\nlabels=df_cities_8_0_2020.groupby('Response Answer').count().index\nfig1, ax1 = plt.subplots(figsize=(7,7))\nax1.pie(df_cities_8_0_2020.groupby('Response Answer').count()['Questionnaire'],labels=labels,autopct='%1.1f%%')\nax1.axis('equal')  # Equal aspect ratio ensures that pie is drawn as a circle.\n# plt.title='Does your city have a renewable energy or electricity target?'\nfig1.suptitle('Does your city have a renewable energy or electricity target? [2020]', fontsize=16)\n# plt.show()\n\ndf_cities_8_0_2019=df_2019_Full_Cities_Dataset.loc[df_2019_Full_Cities_Dataset['Question Number'] == '8.0']\n# df_2020_Full_Cities_Dataset['Question Number'].unique()\ndf_cities_8_0_2019.groupby('Response Answer').count()\nanswer_8_0_2019=pd.DataFrame(df_cities_8_0_2019.groupby('Response Answer').count()['Organization'])\nanswer_8_0_2019['percent']=(answer_8_0_2019['Organization']/sum(answer_8_0_2019['Organization'])*100).round(1)\nanswer_8_0_2019\n# answer_8_0_2019['percent']=answer_8_0_2019['count']/sum(answer_8_0_2019['count'])\n# labels=df_cities_8_0_2019.groupby('Response Answer').count().index\n# fig1, ax1 = plt.subplots()\n# ax1.pie(df_cities_8_0_2019.groupby('Response Answer').count()['Questionnaire'],labels=labels,autopct='%1.1f%%')\n# ax1.axis('equal')  # Equal aspect ratio ensures that pie is drawn as a circle.\n# # plt.title='Does your city have a renewable energy or electricity target?'\n# fig1.suptitle('Does your city have a renewable energy or electricity target? [2019]', fontsize=16)\n# # plt.show()\n\n# # df_cities_8_0_2018=df_2018_Full_Cities_Dataset.loc[df_2018_Full_Cities_Dataset['Question Number'] == '8.0']\n# # # df_2020_Full_Cities_Dataset['Question Number'].unique()\n# # df_cities_8_0_2018.groupby('Response Answer').count()\n# # labels=df_cities_8_0_2018.groupby('Response Answer').count().index\n# # fig1, ax1 = plt.subplots()\n# # ax1.pie(df_cities_8_0_2018.groupby('Response Answer').count()['Questionnaire'],labels=labels,autopct='%1.1f%%')\n# # ax1.axis('equal')  # Equal aspect ratio ensures that pie is drawn as a circle.\n# # # plt.title='Does your city have a renewable energy or electricity target?'\n# # fig1.suptitle('Does your city have a renewable energy or electricity target? [2019]', fontsize=16)\n# # # plt.show()\n\nx = np.arange(len(labels))  # the label locations\nwidth = 0.35  # the width of the bars\nfig, ax = plt.subplots(figsize=(7,7))\n# rects1 = ax.barh(x + width/2, df_cities_8_0_2018.groupby('Response Answer').count()['Questionnaire'], width, label='2018')\nrects1 = ax.barh(x - width/2,answer_8_0_2019['percent'], width, label='2019')\nrects2 = ax.barh(x + width/2, answer_8_0_2020['percent'], width, label='2020')\nax.set_title('Does your city have a renewable energy or electricity target?')\nax.set_yticks(x)\nax.set_yticklabels(labels)\nax.legend()\nrects=rects1+rects2\nfor i in range(len(rects)):\n    percentage = '{:.1f}%'.format(rects[i].get_width())\n    x=rects[i].get_x()+rects[i].get_width()-5\n    y=rects[i].get_y()+0.1\n    ax.annotate(percentage, (x, y))\nplt.show()\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Electricity mix"},{"metadata":{"trusted":true},"cell_type":"code","source":"df_cities_8_1_2020=df_2020_Full_Cities_Dataset.loc[df_2020_Full_Cities_Dataset['Question Number'] == '8.1']\ncity_list=df_cities_8_1_2020['Organization'].unique()\n# df_cities_8_1_2020=df_cities_8_1_2020.loc[df_cities_8_1_2020['Organization'] == city_list[0]]\ndf_cities_8_1_2020\nCity_list=[]\nCity_Coal=[]\nCity_Gas=[]\nCity_Oil=[]\nCity_Nuclear=[]\nCity_Hydro=[]\nCity_Biomass=[]\nCity_Wind=[]\nCity_Geothermal=[]\nCity_Solar=[]\nCity_Other=[]\nCity_Year=[]\ndf_cities_8_1_2020.replace('NaN',np.nan)\ndf_cities_8_1_2020.astype('int',errors='ignore')\n\n\nfor i in range(len(city_list)):\n    city=df_cities_8_1_2020.loc[df_cities_8_1_2020['Organization'] == city_list[i]].copy()\n    City_list.append(city_list[i])\n    City_Coal.append(np.float(city.loc[city['Column Name'] == 'Coal'].copy()['Response Answer'].to_string(index=False)))\n#     print(city.loc[city['Column Name'] == 'Gas'].copy()['Response Answer'])\n    City_Gas.append(np.float(city.loc[city['Column Name'] == 'Gas'].copy()['Response Answer'].to_string(index=False)))\n    City_Oil.append(np.float(city.loc[city['Column Name'] == 'Oil'].copy()['Response Answer'].to_string(index=False)))\n    City_Nuclear.append(np.float(city.loc[city['Column Name'] == 'Nuclear'].copy()['Response Answer'].to_string(index=False)))\n    City_Hydro.append(np.float(city.loc[city['Column Name'] == 'Hydro'].copy()['Response Answer'].to_string(index=False)))\n    City_Biomass.append(np.float(city.loc[city['Column Name'] == 'Biomass'].copy()['Response Answer'].to_string(index=False)))\n    City_Wind.append(np.float(city.loc[city['Column Name'] == 'Wind'].copy()['Response Answer'].to_string(index=False)))\n    City_Geothermal.append(np.float(city.loc[city['Column Name'] == 'Geothermal'].copy()['Response Answer'].to_string(index=False)))\n    City_Solar.append(np.float(city.loc[city['Column Name'] == 'Solar'].copy()['Response Answer'].to_string(index=False)))\n    City_Other.append(np.float(city.loc[city['Column Name'] == 'Other sources'].copy()['Response Answer'].to_string(index=False)))\n    City_Year.append(city.loc[city['Column Name'] == 'Year data applies to'].copy()['Response Answer'].to_string(index=False))\n#     del(city)\n    electricity_mix = pd.DataFrame(list(zip(City_list,City_Coal,City_Gas,City_Oil,City_Nuclear,City_Hydro,City_Biomass,City_Wind,City_Geothermal,City_Solar,City_Other,City_Year)), \n               columns =['Organization','Coal','Gas','Oil','Nuclear','Hydro','Biomass','Wind','Geothermal','Solar','Other_sources','Year']) \n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"renewable=electricity_mix.mean()['Hydro']+electricity_mix.mean()['Biomass']+electricity_mix.mean()['Wind']+electricity_mix.mean()['Geothermal']+electricity_mix.mean()['Solar']\nno_renewable=electricity_mix.mean()['Coal']+electricity_mix.mean()['Gas']+electricity_mix.mean()['Other_sources']+electricity_mix.mean()['Oil']+electricity_mix.mean()['Nuclear']\nshare=[no_renewable,renewable]\nfig1, ax1 = plt.subplots(figsize=(7,10))\nexplode=(0,0,0,0,0.1,0.1,0.1,0.1,0.1,0)\n# explode=(0,0,0,0,0,0,0,0,0,0)\nlabels=electricity_mix.mean().index\npatches, texts, autotexts =ax1.pie(electricity_mix.mean(),autopct='%1.1f%%',labels=labels,explode=explode)\nax1.axis('equal')  # Equal aspect ratio ensures that pie is drawn as a circle.\n# plt.title='Does your city have a renewable energy or electricity target?'\n# fig1.suptitle('Average city energy sources', fontsize=16)\nplt.title('Average city energy sources', fontsize=16)\nplt.setp(autotexts, fontsize=15)\nplt.setp(texts, fontsize=15)\nplt.setp( patches, width=1)\nplt.show()\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Air quality"},{"metadata":{"trusted":true},"cell_type":"code","source":"# df_cities_8_0=df_2020_Full_Cities_Dataset[df_2020_Full_Cities_Dataset['Question Number']]=='8.0'\ndf_cities_10_11_2020=df_2020_Full_Cities_Dataset.loc[df_2020_Full_Cities_Dataset['Question Number'] == '10.11']\n\n# df_2020_Full_Cities_Dataset['Question Number'].unique()\ndf_cities_10_11_2020.groupby('Response Answer').count()\nanswer_10_11_2020=pd.DataFrame(df_cities_10_11_2020.groupby('Response Answer').count()['Organization'])\nanswer_10_11_2020['percent']=(answer_10_11_2020['Organization']/sum(answer_10_11_2020['Organization'])*100).round(1)\nanswer_10_11_2020\nlabels=answer_10_11_2020.groupby('Response Answer').count().index\nfig1, ax1 = plt.subplots(figsize=(7,7))\nax1.pie(df_cities_10_11_2020.groupby('Response Answer').count()['Questionnaire'],labels=labels,autopct='%1.1f%%')\nax1.axis('equal')  # Equal aspect ratio ensures that pie is drawn as a circle.\n# plt.title='Does your city have a renewable energy or electricity target?'\nfig1.suptitle('Does your city collect air quality data?', fontsize=16)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_cities_10_14_2020=df_2020_Full_Cities_Dataset.loc[df_2020_Full_Cities_Dataset['Question Number'] == '10.14'].copy()\n# df_cities_10_14_2020=df_cities_10_14_2020.loc[df_cities_10_14_2020['Organization'] == 'Gemeente Rotterdam']\ndf_cities_10_14_2020.sort_values(['Row Number','Column Number'],ascending=True,inplace=True)\ndf_cities_10_14_2020=df_cities_10_14_2020.loc[df_cities_10_14_2020['Column Number'] == 2]\n\ncity_list=df_cities_10_14_2020['Organization'].unique().copy()\n\nCity_list=[]\nCity_PM2_5_1year=[]\nCity_PM2_5_max=[]\nCity_PM10_1year=[]\nCity_PM10_max=[]\nCity_NO2=[]\nCity_O3=[]\nCity_SO2=[]\ndf_cities_10_14_2020.replace('NaN',np.nan)\ndf_cities_10_14_2020.replace('Question not applicable',np.nan,inplace=True)\ndf_cities_10_14_2020.astype('int',errors='ignore')\nfor i in range(len(city_list)):\n    city=df_cities_10_14_2020.loc[df_cities_10_14_2020['Organization'] == city_list[i]].copy()\n    City_list.append(city_list[i])\n    City_PM2_5_1year.append(np.float(city.loc[city['Row Name'] == 'PM2.5 (1 year (annual) mean)'].copy()['Response Answer'].to_string(index=False)))\n#     print(city.loc[city['Column Name'] == 'Gas'].copy()['Response Answer'])\n    City_PM2_5_max.append(np.float(city.loc[city['Row Name'] == 'PM2.5 (Maximum 24-hour average)'].copy()['Response Answer'].to_string(index=False)))\n    City_PM10_1year.append(np.float(city.loc[city['Row Name'] == 'PM10 (1 year (annual) mean)'].copy()['Response Answer'].to_string(index=False)))\n    City_PM10_max.append(np.float(city.loc[city['Row Name'] == 'PM10 (Maximum 24-hour average)'].copy()['Response Answer'].to_string(index=False)))\n    City_NO2.append(np.float(city.loc[city['Row Name'] == 'NO2 (1 year (annual) mean)'].copy()['Response Answer'].to_string(index=False)))\n    City_O3.append(np.float(city.loc[city['Row Name'] == 'O3 (Daily maximum 8 hour mean)'].copy()['Response Answer'].to_string(index=False)))\n    City_SO2.append(np.float(city.loc[city['Row Name'] == 'SO2 (Maximum 24-hour average)'].copy()['Response Answer'].to_string(index=False)))\n    air_quality = pd.DataFrame(list(zip(City_list,City_PM2_5_1year,City_PM2_5_max,City_PM10_1year,City_PM10_max,City_NO2,City_O3,City_SO2)), \n               columns =['Organization','PM2.5 (1 year (annual) mean)','PM2.5 (Maximum 24-hour average)','PM10 (1 year (annual) mean)','PM10 (Maximum 24-hour average)','NO2 (1 year (annual) mean)','O3 (Daily maximum 8 hour mean)','SO2 (Maximum 24-hour average)']) \npd.set_option('display.max_rows', 1000)\nair_quality.sort_values('PM10 (1 year (annual) mean)',ascending=False)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"air_quality['PM2.5 (Maximum 24-hour average)'].loc[air_quality['PM2.5 (Maximum 24-hour average)']>0].plot(kind='hist')\nair_quality['PM2.5 (1 year (annual) mean)'].loc[air_quality['PM2.5 (Maximum 24-hour average)']>0].plot(kind='hist')\nplt.show()\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_cities_10_3_2020=df_2020_Full_Cities_Dataset.loc[df_2020_Full_Cities_Dataset['Question Number'] == '10.3'].copy()\n# df_cities_10_3_2020.sort_values(['Row Number','Column Number'],ascending=True,inplace=True)\n# df_cities_10_14_2020=df_cities_10_14_2020.loc[df_cities_10_14_2020['Column Number'] == 2]\npd.set_option('display.max_rows', 5000)\n\n# df_cities_10_3_2020=df_cities_10_3_2020.loc[df_cities_10_3_2020['Organization'] == 'Ajuntament de Barcelona']\ndf_cities_10_3_2020\ncity_list=df_cities_10_3_2020['Organization'].unique().copy()\n\nCity_list=[]\nCity_Cycling=[]\nCity_Buses=[]\nCity_Rail=[]\nCity_Ferries =[]\nCity_Other=[]\nCity_Walking=[]\nCity_Taxis=[]\nCity_Cars=[]\n\ndf_cities_10_3_2020.replace('NaN',np.nan)\ndf_cities_10_3_2020.replace('Question not applicable',np.nan,inplace=True)\ndf_cities_10_3_2020.astype('int',errors='ignore')\nfor i in range(len(city_list)):\n    city=df_cities_10_3_2020.loc[df_cities_10_3_2020['Organization'] == city_list[i]].copy()\n    City_list.append(city_list[i])\n    City_Cycling.append(np.float(city.loc[city['Row Number'] == 6].copy()['Response Answer'].to_string(index=False)))\n    City_Buses.append(np.float(city.loc[city['Row Number'] == 3].copy()['Response Answer'].to_string(index=False)))\n    City_Rail.append(np.float(city.loc[city['Row Number'] == 2].copy()['Response Answer'].to_string(index=False)))\n    City_Ferries.append(np.float(city.loc[city['Row Number'] == 4].copy()['Response Answer'].to_string(index=False)))\n    City_Other.append(np.float(city.loc[city['Row Number'] == 8].copy()['Response Answer'].to_string(index=False)))\n    City_Walking.append(np.float(city.loc[city['Row Number'] == 5].copy()['Response Answer'].to_string(index=False)))\n    City_Taxis.append(np.float(city.loc[city['Row Number'] == 7].copy()['Response Answer'].to_string(index=False)))\n    City_Cars.append(np.float(city.loc[city['Row Number'] == 1].copy()['Response Answer'].to_string(index=False)))\n\nmobility = pd.DataFrame(list(zip(City_list,City_Cycling,City_Buses,City_Rail,City_Ferries,City_Other,City_Walking,City_Taxis,City_Cars)), \n            columns =['Organization','Cycling','Buses','Rail','Ferries','Other','Walking','Taxis','Cars']) \nmobility['Total']=mobility.iloc[:,1:-1].sum(skipna=True,axis=1)\nmobility_percent=sum(mobility['Total']>0)/len(mobility)*100\nprint('Only '+str(round(mobility_percent,1))+'% of the cities have reported mobility data, so it is not representative.')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Low emissions zone"},{"metadata":{"trusted":true},"cell_type":"code","source":"df_cities_10_7_2020=df_2020_Full_Cities_Dataset.loc[df_2020_Full_Cities_Dataset['Question Number'] == '10.7'].copy()\n\n# df_2020_Full_Cities_Dataset['Question Number'].unique()\ndf_cities_10_7_2020.groupby('Response Answer').count()\nanswer_10_7_2020=pd.DataFrame(df_cities_10_7_2020.groupby('Response Answer').count()['Organization'])\nanswer_10_7_2020['percent']=(answer_10_7_2020['Organization']/sum(answer_10_7_2020['Organization'])*100).round(1)\nanswer_10_7_2020\nlabels=answer_10_7_2020.groupby('Response Answer').count().index\nfig1, ax1 = plt.subplots(figsize=(7,7))\nax1.pie(df_cities_10_7_2020.groupby('Response Answer').count()['Questionnaire'],labels=labels,autopct='%1.1f%%')\nax1.axis('equal')  # Equal aspect ratio ensures that pie is drawn as a circle.\n# plt.title='Does your city have a renewable energy or electricity target?'\nfig1.suptitle('Do you have a low or zero-emission zone in your city?', fontsize=16)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_cities_13_2020=df_2020_Full_Cities_Dataset.loc[df_2020_Full_Cities_Dataset['Question Number'] == '13.0'].copy()\ndf_cities_13_2020=df_cities_13_2020.loc[df_cities_13_2020['Column Number'] == 1]\ndf_cities_13_2020.replace('NaN',np.nan)\ndf_cities_13_2020=pd.merge(df_cities_13_2020,population['Population'],left_on=df_cities_13_2020['Organization'],right_index=True,how='outer')\ndf_cities_13_2020['per_capita']=df_cities_13_2020['Response Answer'].astype(float,errors='ignore')/df_cities_13_2020['Population'].astype(float,errors='ignore')*1000\ndf_cities_13_2020.sort_values('per_capita',ascending=False)\ndf_cities_13_2020.loc[df_cities_13_2020['Organization']=='Ajuntament de Barcelona']\ndf_cities_13_2020=df_cities_13_2020.loc[df_cities_13_2020['per_capita']>0.0001]\ndf_cities_13_2020.sort_values('per_capita',ascending=False)\nmean__=df_cities_13_2020['per_capita'].mean()\nmedian__=df_cities_13_2020['per_capita'].median()\nprint('The amount of cities answered is '+str(round(sum(df_cities_13_2020['Response Answer'].astype(float,errors='ignore')>1)/len(df_cities_13_2020),2)*100)+'% of total.')\nprint('The mean and median waste per capita are '+str(round(mean__))+'kg/capita and '+str(round(median__))+'kg/capita.')\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"It seems that some organizations reported the waste per year in terms of kg/year instead of tonnes/year so this question is not considered. "},{"metadata":{"trusted":true},"cell_type":"code","source":"df_cities_13_4_2020=df_2020_Full_Cities_Dataset.loc[df_2020_Full_Cities_Dataset['Question Number'] == '13.4'].copy()\n\npd.set_option('display.max_rows', 5000)\n\n# df_cities_10_3_2020=df_cities_10_3_2020.loc[df_cities_10_3_2020['Organization'] == 'Ajuntament de Barcelona']\ncity_list=df_cities_13_4_2020['Organization'].unique().copy()\n\nCity_list=[]\nfor i in range(len(df_cities_13_4_2020['Row Name'].unique())):\n    vars()['City_'+df_cities_13_4_2020['Row Name'].unique()[i]] = []\n\n\ndf_cities_13_4_2020.replace('NaN',np.nan)\ndf_cities_13_4_2020=df_cities_13_4_2020.replace('Question not applicable',-1)\ndf_cities_13_4_2020=df_cities_13_4_2020.astype('int',errors='ignore')\nfor i in range(len(city_list)):\n    city=df_cities_13_4_2020.loc[df_cities_13_4_2020['Organization'] == city_list[i]].copy()\n    City_list.append(city_list[i])\n    for j in range(len(df_cities_13_4_2020['Row Name'].unique())):\n        vars()['City_'+df_cities_13_4_2020['Row Name'].unique()[j]].append(np.float(city.loc[city['Row Name'] == df_cities_13_4_2020['Row Name'].unique()[j]].copy()['Response Answer'].to_string(index=False)))\nwaste = pd.DataFrame(City_list, \n              columns =['Organization']) \nfor i in range(len(df_cities_13_4_2020['Row Name'].unique())):\n     waste[df_cities_13_4_2020['Row Name'].unique()[i]]= vars()['City_'+df_cities_13_4_2020['Row Name'].unique()[i]]\nwaste=waste.replace(-1,'Question not applicable')\n\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_corps_0_3_2020=df_2020_Full_Climate_Change_Dataset.loc[df_2020_Full_Climate_Change_Dataset['question_number']=='C0.3']\ntest=df_2020_Full_Climate_Change_Dataset.loc[df_2020_Full_Climate_Change_Dataset['organization']=='Ford Motor Company']\ntest.sort_values('question_number')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"pd.set_option('display.max_columns', 10000)\n\n# df_2020_Corporates_Disclosing_to_CDP_Climate_Change.groupby('primary_activity').count().sort_values('organization',ascending=False)['organization'].plot(kind='barh',figsize=[10,70],title='Primary Activity')\n# plt.show()\ndf_2020_Corporates_Disclosing_to_CDP_Climate_Change.groupby('primary_questionnaire_sector').count().sort_values('organization',ascending=True)['organization'].plot(kind='barh',figsize=[10,7],title='Primary Sector',xlabel='Sector',ylabel='Total amount',fontsize=15)\nplt.show()\ndf_2020_Corporates_Disclosing_to_CDP_Climate_Change.groupby('primary_industry').count().sort_values('organization',ascending=True)['organization'].plot(kind='barh',figsize=[10,7],title='Primary Industry',xlabel='Industry',ylabel='Total amount',fontsize=15)\nplt.show()\n\n# df_2020_Corporates_Disclosing_to_CDP_Climate_Change.head(2)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"","execution_count":null,"outputs":[]}],"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}