{"cells":[{"metadata":{},"cell_type":"markdown","source":"### Importing Libraries"},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"import numpy as np\nimport pandas as pd\nimport nltk\nimport os\n\n# Plot\nimport matplotlib.pyplot as plt\nimport seaborn as sns\nimport plotly.express as px\nfrom wordcloud import WordCloud, STOPWORDS, ImageColorGenerator\n\nimport warnings","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Loading Data"},{"metadata":{},"cell_type":"markdown","source":"### Cities Responses"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"def resumetable(df):\n    print(f\"Dataset Shape: {df.shape}\")\n    summary = pd.DataFrame(df.dtypes,columns=['dtypes'])\n    summary = summary.reset_index()\n    summary['Name'] = summary['index']\n    summary = summary[['Name','dtypes']]\n    summary['Missing'] = df.isnull().sum().values    \n    summary['Uniques'] = df.nunique().values\n    summary['First Value'] = df.loc[0].values\n    summary['Second Value'] = df.loc[1].values\n    summary['Third Value'] = df.loc[2].values\n    return summary","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#Loading Cities response\ndf_ct_full_2018 = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Cities/Cities Responses/2018_Full_Cities_Dataset.csv\")\ndf_ct_full_2019 = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Cities/Cities Responses/2019_Full_Cities_Dataset.csv\")\ndf_ct_full_2020 = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Cities/Cities Responses/2020_Full_Cities_Dataset.csv\")","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Corporations Disclosing"},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true},"cell_type":"code","source":"# Dataset_Corporates\ndf_cl_2018 = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Corporations/Corporations Disclosing/Climate Change/2018_Corporates_Disclosing_to_CDP_Climate_Change.csv\")\ndf_cl_2019 = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Corporations/Corporations Disclosing/Climate Change/2019_Corporates_Disclosing_to_CDP_Climate_Change.csv\")\ndf_cl_2020 = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Corporations/Corporations Disclosing/Climate Change/2020_Corporates_Disclosing_to_CDP_Climate_Change.csv\")","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Joining Climate Change Data."},{"metadata":{"trusted":true},"cell_type":"code","source":"# Cities Responses\n# concatenating\ndf = [df_ct_full_2018, df_ct_full_2019, df_ct_full_2020]\ndf_ct = pd.concat(df)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# resetting the index\ndf_ct = df_ct.reset_index()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"resumetable(df_ct)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Corporations Disclosing\n# concatenating\ndf = [df_cl_2018, df_cl_2019, df_cl_2020]\ndf_cl = pd.concat(df)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# resetting the index\ndf_cl = df_cl.reset_index()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"resumetable(df_cl)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Exploratory analysis"},{"metadata":{},"cell_type":"markdown","source":"* Cities Responses Data "},{"metadata":{},"cell_type":"markdown","source":"Year Reported to CDP"},{"metadata":{"trusted":true},"cell_type":"code","source":"group = df_ct.groupby('Year Reported to CDP').size()\ngroup.sort_values(ascending = False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plt.figure(figsize=(15, 8))\n\nfreq = len(df_ct)\n\nsns.set_palette(\"pastel\")\n\ng = sns.countplot(df_ct['Year Reported to CDP'])\ng.set_xlabel('Year', fontsize = 15)\ng.set_ylabel(\"Count\", fontsize = 15)\n\nfor p in g.patches:\n    height = p.get_height()\n    g.text(p.get_x() + p.get_width() / 2., height + 3,\n          '{:1.2f}%'.format(height / freq * 100),\n          ha = \"center\", fontsize = 18)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Region"},{"metadata":{"trusted":true},"cell_type":"code","source":"group = df_ct.groupby('CDP Region').size()\ngroup.sort_values(ascending = False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plt.figure(figsize=(18, 8))\n\nfreq = len(df_ct)\n\nsns.set_palette(\"pastel\")\n\ng = sns.countplot(df_ct['CDP Region'], order = df_ct['CDP Region'].value_counts().index)\ng.set_xlabel('Region', fontsize = 15)\ng.set_ylabel(\"Count\", fontsize = 15)\n\nfor p in g.patches:\n    height = p.get_height()\n    g.text(p.get_x() + p.get_width() / 2., height + 3,\n          '{:1.2f}%'.format(height / freq * 100),\n          ha = \"center\", fontsize = 18)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"CDP Region by Year Reported to CDP"},{"metadata":{"trusted":true},"cell_type":"code","source":"features = [\"CDP Region\", 'Year Reported to CDP']\ncm = sns.light_palette(\"red\", as_cmap = True)\npd.crosstab(df_ct[features[0]], df_ct[features[1]]).style.background_gradient(cmap = cm)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Country"},{"metadata":{"trusted":true},"cell_type":"code","source":"group = df_ct.groupby('Country').size()\ngroup.sort_values(ascending = False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"city_count = df_ct['Country'].value_counts()\ncity_count_10 = city_count[:10,]\ncity_count_10","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plt.figure(figsize=(18, 8))\n\nfreq = len(df_ct)\n\nsns.set_palette(\"pastel\")\n\ng = sns.barplot(city_count_10.index, city_count_10.values)\ng.set_title('Top 10 Country', fontsize = 15)\ng.set_xlabel('Region', fontsize = 15)\ng.set_ylabel(\"Count\", fontsize = 15)\nplt.xticks(rotation = 90)\n\nfor p in g.patches:\n    height = p.get_height()\n    g.text(p.get_x() + p.get_width() / 2., height + 3,\n          '{:1.2f}%'.format(height / freq * 100),\n          ha = \"center\", fontsize = 18)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Organization"},{"metadata":{"trusted":true},"cell_type":"code","source":"group_ct = df_ct.groupby('Organization').size()\ngroup_ct.sort_values(ascending = False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"ct_count = df_ct['Organization'].value_counts()\nct_count_10 = ct_count[:10,]\nct_count_10","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plt.figure(figsize=(18, 8))\n\nfreq = len(df_ct)\n\nsns.set_palette(\"pastel\")\n\ng = sns.barplot(ct_count_10.index, ct_count_10.values)\ng.set_title('Top 10 City', fontsize = 15)\ng.set_xlabel('City', fontsize = 15)\ng.set_ylabel(\"Count\", fontsize = 15)\nplt.xticks(rotation=90)\n\nfor p in g.patches:\n    height = p.get_height()\n    g.text(p.get_x() + p.get_width() / 2., height + 3,\n          '{:1.2f}%'.format(height / freq * 100),\n          ha = \"center\", fontsize = 18)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Question\n* 6.2 Does your city collaborate in partnership with businesses in your city on sustainability projects?"},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_6_2 = df_ct[df_ct['Question Number'] == '6.2'].rename(columns={'Organization': 'City'})\n\ncities_6_2['Response Answer'] = df_ct['Response Answer'].fillna('No Response')\n\ncities_6_2.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Added data\nImport a database to use the Code variable to build the map."},{"metadata":{"trusted":true},"cell_type":"code","source":"df_import = pd.read_csv('../input/countries-iso-codes/country_codes.csv').rename(columns={'COUNTRY': 'country'})","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_import","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Checking country names"},{"metadata":{"trusted":true},"cell_type":"code","source":"countries = df_import['country'].unique().tolist()\nNumber_of_countries = len(countries)\nprint(countries)\nprint(\"\\nTotal countries df_import present: \",Number_of_countries)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"countries = cities_6_2['Country'].unique().tolist()\nNumber_of_countries = len(countries)\nprint(countries)\nprint(\"\\nTotal countries CDP_6.2 present: \",Number_of_countries)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"It is important to check that the names of the countries in the two databases are the same."},{"metadata":{"trusted":true},"cell_type":"code","source":"rename = {\n    'United States of America': 'United States',\n    'United Kingdom of Great Britain and Northern Ireland': 'United Kingdom',\n    'Venezuela (Bolivarian Republic of)': 'Venezuela',\n    'Bolivia (Plurinational State of)': 'Bolivia',\n    'China, Hong Kong Special Administrative Region': 'Hong Kong',\n    'Taiwan, Greater China': 'Taiwan',\n    'Viet Nam': 'Vietnam',\n    'Democratic Republic of the Congo': 'Congo, Democratic Republic of the',\n    'Russian Federation': 'Russia',\n    'Republic of Korea': 'Korea, South',\n    \"Côte d'Ivoire\": \"Cote d'Ivoire\",\n    'United Republic of Tanzania': 'Tanzania',\n    'Republic of Moldova': 'Moldova',\n    'Chile': 'Chile'    \n}\n\ncities_6_2['re_country'] = cities_6_2['Country'].map(rename)\ncities_6_2['re_country'] = cities_6_2['re_country'].fillna(cities_6_2['Country'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"countries_year = cities_6_2.groupby(['Year Reported to CDP',\n                                     'CDP Region',\n                                     're_country'])['City'].count().to_frame()\ncountries_year = countries_year.reset_index().rename(columns={'Year Reported to CDP': 'Year',\n                                                              're_country': 'country'})","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Reference:\n\nhttps://www.kaggle.com/callumr22/cdp-starter-notebook"},{"metadata":{},"cell_type":"markdown","source":"Joining the CDP database with df_import."},{"metadata":{"trusted":true},"cell_type":"code","source":"countries_year = pd.merge(countries_year, df_import, how='inner', on = 'country')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Number of cities collaborate in partnership with businesses in your city on sustainability projects per year"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"df = countries_year\nfig = px.scatter_geo(df, locations=\"CODE\", color=\"CDP Region\", \n                     hover_name=\"country\", size=\"City\",\n                     animation_frame=\"Year\", projection=\"natural earth\")\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"countries_total = cities_6_2.groupby(['CDP Region','re_country'])['City'].count().to_frame()\ncountries_total = countries_total.reset_index().rename(columns={'re_country': 'country'})","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"countries_total = pd.merge(countries_total, df_import, how='inner', on = 'country')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Total cities collaborate in partnership with companies in your city in sustainability projects"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"df = countries_total\nfig = px.scatter_geo(df, locations=\"CODE\", color=\"CDP Region\",\n                     hover_name=\"country\", size=\"City\",\n                     projection=\"natural earth\")\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Word Cloud for Question Name"},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_6_2.dropna(subset=['Question Name'], axis=0, inplace = True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"Response = cities_6_2['Question Name']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"Response_summary = \" \".join(s for s in Response)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"stopwords=nltk.corpus.stopwords.words('english')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"wordcloud = WordCloud(stopwords=stopwords,\n                      background_color='white', width=1600,                            \n                      height=800).generate(Response_summary)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"fig, ax = plt.subplots(figsize=(16,8))       \n\nax.imshow(wordcloud, interpolation='bilinear') \nax.set_axis_off()\n\nplt.imshow(wordcloud)              \nwordcloud.to_file('rafael.png',);","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Word Cloud for Response Answer"},{"metadata":{"trusted":true},"cell_type":"code","source":"Response = cities_6_2['Response Answer']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"Response_summary = \" \".join(s for s in Response)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"stopwords=nltk.corpus.stopwords.words('english', 'portuguese')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"wordcloud = WordCloud(stopwords=stopwords,\n                      background_color='white', width=1600,                            \n                      height=800).generate(Response_summary)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"fig, ax = plt.subplots(figsize=(16,8))       \n\nax.imshow(wordcloud, interpolation='bilinear') \nax.set_axis_off()\n\nplt.imshow(wordcloud)              \nwordcloud.to_file('rafael.png',);","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"* Corporations Disclosing\n\nCountry"},{"metadata":{"trusted":true},"cell_type":"code","source":"df_cl.groupby('country').size()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plt.figure(figsize=(12, 5))\n\nfreq = len(df_cl)\n\nsns.set_palette(\"pastel\")\n\ng = sns.countplot(df_cl['country'])\ng.set_xlabel('Country', fontsize = 15)\ng.set_ylabel(\"Count\", fontsize = 15)\n\nfor p in g.patches:\n    height = p.get_height()\n    g.text(p.get_x() + p.get_width() / 2., height + 3,\n          '{:1.2f}%'.format(height / freq * 100),\n          ha = \"center\", fontsize = 18)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Year"},{"metadata":{"trusted":true},"cell_type":"code","source":"df_cl.groupby('survey_year').size()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plt.figure(figsize=(12, 5))\n\nfreq = len(df_cl)\n\nsns.set_palette(\"pastel\")\n\ng = sns.countplot(df_cl['survey_year'])\ng.set_xlabel('Survey year', fontsize = 15)\ng.set_ylabel(\"Count\", fontsize = 15)\n\nfor p in g.patches:\n    height = p.get_height()\n    g.text(p.get_x() + p.get_width() / 2., height + 3,\n          '{:1.2f}%'.format(height / freq * 100),\n          ha = \"center\", fontsize = 18)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### To be continued..."}],"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}