{"cells":[{"metadata":{},"cell_type":"markdown","source":""},{"metadata":{},"cell_type":"markdown","source":"**PROBLEM STATEMENT**"},{"metadata":{},"cell_type":"markdown","source":"Develop a methodology for calculating key performance indicators (KPIs) that relate to the environmental and social issues that are discussed in the CDP survey data. Leverage external data sources and thoroughly discuss the intersection between environmental issues and social issues. Mine information to create automated insight generation demonstrating whether city and corporate ambitions take these factors into account."},{"metadata":{},"cell_type":"markdown","source":"* How do you help cities adapt to a rapidly changing climate amidst a global pandemic, but do it in a way that is socially equitable?\n\n* What are the projects that can be invested in that will help pull cities out of a recession, mitigate climate issues, but not perpetuate racial/social inequities?\n\n* What are the practical and actionable points where city and corporate ambition join, i.e. where do cities have problems that corporations affected by those problems could solve, and vice versa?\n\n* How can we measure the intersection between environmental risks and social equity, as a contributor to resiliency?"},{"metadata":{},"cell_type":"markdown","source":"**Note:**\n\n**While creating this study, examples from many previous studies on this problem were taken. In this respect, thanks to the scientists who shared and contributed to all kinds of development.**"},{"metadata":{},"cell_type":"markdown","source":"**Importing Libraries**"},{"metadata":{"trusted":true},"cell_type":"code","source":"# standard libs\nimport os\nimport pandas as pd\nimport numpy as np\nimport matplotlib.pyplot as plt\nimport re\nimport json\nimport cufflinks as cf\nimport plotly.figure_factory as ff\nimport emoji\nfrom pprint import pprint\nimport collections\nimport string\nimport time\nfrom time import time\nimport math\n\n\n# plotting libs\nimport seaborn as sns\n\n# geospatial libs\nfrom mpl_toolkits.basemap import Basemap\nfrom shapely.geometry import Polygon\nimport geopandas as gpd\nimport folium\nimport plotly.graph_objects as go\nimport plotly_express as px\nfrom plotly.offline import (download_plotlyjs, \n init_notebook_mode, \n plot, \n iplot)\n\nfrom tabulate import tabulate\nimport ipywidgets as widgets\n\nfrom sklearn.base import BaseEstimator, TransformerMixin\nfrom sklearn.feature_extraction.text import CountVectorizer\nfrom sklearn.feature_extraction.text import TfidfVectorizer\nfrom sklearn.model_selection import GridSearchCV\nfrom sklearn.model_selection import train_test_split\nfrom sklearn.pipeline import Pipeline, FeatureUnion\nfrom sklearn.metrics import classification_report\nfrom sklearn.naive_bayes import MultinomialNB\nfrom sklearn.linear_model import LogisticRegression\n\nimport gensim\n\nimport heapq\nfrom textblob import TextBlob\nimport spacy\nnlp = spacy.load('en_core_web_sm')\nfrom wordcloud import WordCloud\nfrom IPython.display import display\nimport base64\n\nfrom collections import Counter\n\n\nimport nltk\nfrom nltk.corpus import stopwords\nstopwords = stopwords.words('english')\nfrom nltk.stem import PorterStemmer\nfrom nltk.tokenize import word_tokenize, sent_tokenize\nimport warnings\nwarnings.filterwarnings('ignore')\nimport logging\nlogging.getLogger(\"lda\").setLevel(logging.WARNING)\n\n# set in line plotly \nfrom plotly.offline import init_notebook_mode;\ninit_notebook_mode(connected=True)\n\ncf.go_offline()\n\nprint(os.getcwd())","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":""},{"metadata":{},"cell_type":"markdown","source":"We want to merge all the files and then get insight from them. So, we gonna start with cities datasets"},{"metadata":{},"cell_type":"markdown","source":"**Dictionary for labelling df names**\n\n### Cities\n\n* Cities : c\n* Disclosing : d\n* Questionnaires : q\n* Responses : r\n\n### Corporations\n\n* Corporations : cr\n* Disclosing : d\n* Questionnaires : q\n* Responses : r\n* Climate Change : cc\n* Water Security : ws\n\n\n**Note : We will look to the ''*Supplementary Data*'' after merge and subset of the above folder datasets.**"},{"metadata":{},"cell_type":"markdown","source":"# Import Data"},{"metadata":{},"cell_type":"markdown","source":"**Cities**"},{"metadata":{"trusted":true},"cell_type":"code","source":"# import cities data\n # For Cities Disclosing\ncd_2018 = pd.read_csv('../input/cdp-unlocking-climate-solutions/Cities/Cities Disclosing/2018_Cities_Disclosing_to_CDP.csv')\ncd_2019 = pd.read_csv('../input/cdp-unlocking-climate-solutions/Cities/Cities Disclosing/2019_Cities_Disclosing_to_CDP.csv')\ncd_2020 = pd.read_csv('../input/cdp-unlocking-climate-solutions/Cities/Cities Disclosing/2020_Cities_Disclosing_to_CDP.csv')\n # For Cities Responses\ncr_2018 = pd.read_csv('../input/cdp-unlocking-climate-solutions/Cities/Cities Responses/2018_Full_Cities_Dataset.csv')\ncr_2019 = pd.read_csv('../input/cdp-unlocking-climate-solutions/Cities/Cities Responses/2019_Full_Cities_Dataset.csv')\ncr_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**"},{"metadata":{"trusted":true},"cell_type":"code","source":"# import corporations data\n # For Corporations Disclosing\n # For Climate Change\ncrd_cc_2018 = pd.read_csv('../input/cdp-unlocking-climate-solutions/Corporations/Corporations Disclosing/Climate Change/2018_Corporates_Disclosing_to_CDP_Climate_Change.csv')\ncrd_cc_2019 = pd.read_csv('../input/cdp-unlocking-climate-solutions/Corporations/Corporations Disclosing/Climate Change/2019_Corporates_Disclosing_to_CDP_Climate_Change.csv')\ncrd_cc_2020 = pd.read_csv('../input/cdp-unlocking-climate-solutions/Corporations/Corporations Disclosing/Climate Change/2020_Corporates_Disclosing_to_CDP_Climate_Change.csv')\n # For Water Security \ncrd_ws_2018 = pd.read_csv('../input/cdp-unlocking-climate-solutions/Corporations/Corporations Disclosing/Water Security/2018_Corporates_Disclosing_to_CDP_Water_Security.csv')\ncrd_ws_2019 = pd.read_csv('../input/cdp-unlocking-climate-solutions/Corporations/Corporations Disclosing/Water Security/2019_Corporates_Disclosing_to_CDP_Water_Security.csv')\ncrd_ws_2020 = pd.read_csv('../input/cdp-unlocking-climate-solutions/Corporations/Corporations Disclosing/Water Security/2020_Corporates_Disclosing_to_CDP_Water_Security.csv')\n # For Corporations Responses\n # For Climate Change\ncrr_cc_2018 = pd.read_csv('../input/cdp-unlocking-climate-solutions/Corporations/Corporations Responses/Climate Change/2018_Full_Climate_Change_Dataset.csv')\ncrr_cc_2019 = pd.read_csv('../input/cdp-unlocking-climate-solutions/Corporations/Corporations Responses/Climate Change/2019_Full_Climate_Change_Dataset.csv')\ncrr_cc_2020 = pd.read_csv('../input/cdp-unlocking-climate-solutions/Corporations/Corporations Responses/Climate Change/2020_Full_Climate_Change_Dataset.csv')\n # For Water Security \ncrr_ws_2018 = pd.read_csv('../input/cdp-unlocking-climate-solutions/Corporations/Corporations Responses/Water Security/2018_Full_Water_Security_Dataset.csv')\ncrr_ws_2019 = pd.read_csv('../input/cdp-unlocking-climate-solutions/Corporations/Corporations Responses/Water Security/2019_Full_Water_Security_Dataset.csv')\ncrr_ws_2020 = pd.read_csv('../input/cdp-unlocking-climate-solutions/Corporations/Corporations Responses/Water Security/2020_Full_Water_Security_Dataset.csv')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**Suplementary Data**"},{"metadata":{"trusted":true},"cell_type":"code","source":"# CDC 500 Cities Census Tract Data\ncdc_cities_census_track_data = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Supplementary Data/CDC 500 Cities Census Tract Data/500_Cities__Census_Tract-level_Data__GIS_Friendly_Format___2019_release.csv\")\n# CDC Social Vulnerability Index 2018\ncdc_social_vulnerability_index_2018_uscounty = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Supplementary Data/CDC Social Vulnerability Index 2018/SVI2018_US_COUNTY.csv\")\n# external data - import CDC social vulnerability index data - census tract level\ncdc_social_vulnerability_index_2018_us = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Supplementary Data/CDC Social Vulnerability Index 2018/SVI2018_US.csv\")\n# cities metadata - lat,lon locations for US cities\nsimple_maps_us_cities_data = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Supplementary Data/Simple Maps US Cities Data/uscities.csv\")\n# cities metadata - CDP metadata on organisation HQ cities\nlocations_of_corporations = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Supplementary Data/Locations of Corporations/NA_HQ_public_data.csv\")","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Referance image for merge process"},{"metadata":{"trusted":true},"cell_type":"code","source":"import os\nfrom IPython.display import Image\nImage(filename=\"../input/cdp-image/inbox_1314380_6f0f4d334e5b094bfcf002c4d2e931f6_CDP_dataset.png\", width= 1135, height=1615)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# **Merge Process**"},{"metadata":{"trusted":true},"cell_type":"code","source":"# For Cities \n # For Cities Disclosing\ndataset = [cd_2018, cd_2019, cd_2020]\ncd = pd.concat(dataset)\n\n# For Cities\n # For Cities Responses\ndataset1 = [cr_2018, cr_2019, cr_2020]\ncr = pd.concat(dataset1)\n\n# For Corporations\n # For Corporations Disclosing\n # For Climate Change\ndataset2 = [crd_cc_2018, crd_cc_2019, crd_cc_2020]\ncrd_cc = pd.concat(dataset2)\n\n# For Corporations\n # For Corporations Disclosing\n # For Water Security\ndataset3 = [crd_ws_2018, crd_ws_2019, crd_ws_2020]\ncrd_ws = pd.concat(dataset3)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Adding topic column for identify of datasets before combine them"},{"metadata":{"trusted":true},"cell_type":"code","source":"crd_cc['topic'] = 'Climate Change'\ncrd_ws['topic'] = 'Water Security'","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# For Corporations\n # For Corporations Disclosing\ndataset7 = [crd_cc, crd_ws]\ncrd = pd.concat(dataset7)\n\n# For Corporations\n # For Corporations Responses\n # For Climate Change\ndataset4 = [crr_cc_2018, crr_cc_2019, crr_cc_2020]\ncrr_cc = pd.concat(dataset4)\n\n# For Corporations\n # For Corporations Responses\n # For Water Security\ndataset5 = [crr_ws_2018, crr_ws_2019, crr_ws_2020]\ncrr_ws = pd.concat(dataset5)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Adding \"**topic**\" column for identify of datasets before combine them"},{"metadata":{"trusted":true},"cell_type":"code","source":"crr_cc['topic'] = 'Climate Change'\ncrr_ws['topic'] = 'Water Security'","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# For Corporations\n # For Corporations Disclosing\ndataset6 = [crr_cc, crr_ws]\ncrr = pd.concat(dataset6)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**First Look**\n---"},{"metadata":{},"cell_type":"markdown","source":"Cities:\n\n* cd --> Cities Disclosing\n* cr --> Cities Responses\n\nCorporations:\n* crd --> Corprations Disclosing\n* crr --> Corporations Responses"},{"metadata":{},"cell_type":"markdown","source":"**For Corporations**"},{"metadata":{},"cell_type":"markdown","source":"* For Responses"},{"metadata":{"trusted":true},"cell_type":"code","source":"crr.info()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"* For Disclosing"},{"metadata":{"trusted":true},"cell_type":"code","source":"crd.info()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"For Cities Disclosing"},{"metadata":{"trusted":true},"cell_type":"code","source":"cd.info()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"For Cities Responses"},{"metadata":{"trusted":true},"cell_type":"code","source":"cr.info()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# **Data Preparation**"},{"metadata":{},"cell_type":"markdown","source":"For \"**cr**\" data"},{"metadata":{},"cell_type":"markdown","source":"* Change **Organization** column name to **City** \n* Fill **NAN** values with **'No Response'** on the **Response Answer** column\n* Fill **NAN** values with **'No Section'** on the **Parent Section** column\n* Fill **NAN** values with **'Unknown'** on the **Column Name** column\n* Fill **NAN** values with **'Unknown'** on the **Row Name** column\n* Fill **NAN** valus with **'No Comment'** on the **Comments** column\n* Drop **'File Name'** column (%99 Null values)\n\nNote: Check Related Data Dictionary \n\n**(../input/cdp-unlocking-climate-solutions/Cities/Cities Responses/Full_Cities_Response_Data_Dictionary.csv)**"},{"metadata":{"trusted":true},"cell_type":"code","source":"cr = cr.rename(columns={'Organization': 'City'})\ncr['Response Answer'] = cr['Response Answer'].fillna('No Response')\ncr['Parent Section'] = cr['Parent Section'].fillna('No Section')\ncr['Column Name'] = cr['Column Name'].fillna('Unknown')\ncr['Row Name'] = cr['Row Name'].fillna('Unknown')\ncr['Comments'] = cr['Comments'].fillna('No Comment')\ncr = cr.drop(['File Name'], axis=1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cr.isnull().values.any()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"* For **\"cd\"** dataset"},{"metadata":{},"cell_type":"markdown","source":"* Fill **NAN** values with **'Unknown'** on the **City** column\n* Fill **NAN** values with **mean** values on the **Population** column\n* Fill **NAN** values with location values from [country_lat_long](https://www.kaggle.com/alexkaechele/country-geo) on he **City Location** column\n\nNote: Check Related Data Dictionary \n\n**(../input/cdp-unlocking-climate-solutions/Cities/Cities Disclosing/Cities_Disclosing_to_CDP_Data_Dictionary.csv)**"},{"metadata":{"trusted":true},"cell_type":"code","source":"ncountry = cd[cd['City Location'].isnull()]\nncountry['Country'].unique()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"ncountry['City Location'] = np.where ((ncountry[\"Country\"] == \"Turkey\"),'POINT (38.963745 35.243322)' , ncountry['City Location'])\nncountry['City Location'] = np.where ((ncountry[\"Country\"] == \"Sweden\"),'POINT (60.128161 18.643501)' , ncountry['City Location'])\nncountry['City Location'] = np.where ((ncountry[\"Country\"] == \"Peru\"),'POINT (-9.189967 -75.015152)' , ncountry['City Location'])\nncountry['City Location'] = np.where ((ncountry[\"Country\"] == \"Brazil\"),'POINT (-14.235004 -51.92528)' , ncountry['City Location'])\nncountry['City Location'] = np.where ((ncountry[\"Country\"] == \"Argentina\"),'POINT (-38.4161 -63.6167)' , ncountry['City Location'])\nncountry['City Location'] = np.where ((ncountry[\"Country\"] == \"Germany\"),'POINT (51.165691 10.451526)' , ncountry['City Location'])\nncountry['City Location'] = np.where ((ncountry[\"Country\"] == \"Philippines\"),'POINT (12.879721 121.774017)' , ncountry['City Location'])\nncountry['City Location'] = np.where ((ncountry[\"Country\"] == \"Chile\"),'POINT (-35.675147 -71.542969)' , ncountry['City Location'])\nncountry['City Location'] = np.where ((ncountry[\"Country\"] == \"Thailand\"),'POINT (15.870032 100.992541)' , ncountry['City Location'])\nncountry['City Location'] = np.where ((ncountry[\"Country\"] == \"New Zealand\"),'POINT (-40.900557 174.885971)' , ncountry['City Location'])\nncountry['City Location'] = np.where ((ncountry[\"Country\"] == \"Guatemala\"),'POINT (15.783471 -90.230759)' , ncountry['City Location'])\nncountry['City Location'] = np.where ((ncountry[\"Country\"] == \"Indonesia\"),'POINT (-0.789275 113.921327)' , ncountry['City Location'])\nncountry['City Location'] = np.where ((ncountry[\"Country\"] == \"Colombia\"),'POINT (4.570868 -74.297333)' , ncountry['City Location'])\nncountry['City Location'] = np.where ((ncountry[\"Country\"] == \"United Kingdom of Great Britain and Northern Ireland\"),'POINT (55.378051 -3.435973)' , ncountry['City Location'])\nncountry['City Location'] = np.where ((ncountry[\"Country\"] == \"Portugal\"),'POINT (39.399872 -8.224454)' , ncountry['City Location'])\nncountry['City Location'] = np.where ((ncountry[\"Country\"] == \"Mexico\"),'POINT (23.634501 -102.552784)' , ncountry['City Location'])\nncountry['City Location'] = np.where ((ncountry[\"Country\"] == \"United States of America\"),'POINT (37.09024 -95.712891)' , ncountry['City Location'])\nncountry['City Location'] = np.where ((ncountry[\"Country\"] == \"Italy\"),'POINT (41.87194 12.56738)' , ncountry['City Location'])\nncountry['City Location'] = np.where ((ncountry[\"Country\"] == \"India\"),'POINT (20.593684 78.96288)' , ncountry['City Location'])\nncountry['City Location'] = np.where ((ncountry[\"Country\"] == \"Australia\"),'POINT (-25.274398 133.775136)' , ncountry['City Location'])\nncountry['City Location'] = np.where ((ncountry[\"Country\"] == \"Lebanon\"),'POINT (33.854721 35.862285)' , ncountry['City Location'])\nncountry['City Location'] = np.where ((ncountry[\"Country\"] == \"Canada\"),'POINT (56.130366 -106.346771)' , ncountry['City Location'])\nncountry['City Location'] = np.where ((ncountry[\"Country\"] == \"Taiwan, Greater China\"),'POINT (23.69781 120.960515)' , ncountry['City Location'])\nncountry['City Location'] = np.where ((ncountry[\"Country\"] == \"Liberia\"),'POINT (6.428055 -9.429499)' , ncountry['City Location'])\nncountry['City Location'] = np.where ((ncountry[\"Country\"] == \"State of Palestine\"),'POINT (31.952162 35.233154)' , ncountry['City Location'])\nncountry['City Location'] = np.where ((ncountry[\"Country\"] == \"South Africa\"),'POINT (-30.559482 22.937506)' , ncountry['City Location'])\nncountry['City Location'] = np.where ((ncountry[\"Country\"] == \"China\"),'POINT (35.86166 104.195397)' , ncountry['City Location'])\nncountry['City Location'] = np.where ((ncountry[\"Country\"] == \"Viet Nam\"),'POINT (14.058324 108.277199)' , ncountry['City Location'])\nncountry['City Location'] = np.where ((ncountry[\"Country\"] == \"Lithuania\"),'POINT (55.169438 23.881275)' , ncountry['City Location'])\nncountry['City Location'] = np.where ((ncountry[\"Country\"] == \"Venezuela (Bolivarian Republic of)\"),'POINT (6.42375 -66.58973)' , ncountry['City Location'])\nncountry['City Location'] = np.where ((ncountry[\"Country\"] == \"Spain\"),'POINT (40.463667 -3.74922)' , ncountry['City Location'])\nncountry['City Location'] = np.where ((ncountry[\"Country\"] == \"Malaysia\"),'POINT (4.210484 101.975766)' , ncountry['City Location'])\nncountry['City Location'] = np.where ((ncountry[\"Country\"] == \"Costa Rica\"),'POINT (9.748917 -83.753428)' , ncountry['City Location'])\nncountry['City Location'] = np.where ((ncountry[\"Country\"] == \"Romania\"),'POINT (45.943161 24.96676)' , ncountry['City Location'])\nncountry['City Location'] = np.where ((ncountry[\"Country\"] == \"Uganda\"),'POINT (1.373333 32.290275)' , ncountry['City Location'])\nncountry['City Location'] = np.where ((ncountry[\"Country\"] == \"Burkina Faso\"),'POINT (12.238333 -1.561593)' , ncountry['City Location'])\nncountry['City Location'] = np.where ((ncountry[\"Country\"] == \"Republic of Korea\"),'POINT (40.339852 127.510093)' , ncountry['City Location'])\nncountry['City Location'] = np.where ((ncountry[\"Country\"] == \"Norway\"),'POINT (60.472024 8.468946)' , ncountry['City Location'])\nncountry['City Location'] = np.where ((ncountry[\"Country\"] == \"France\"),'POINT (46.227638 2.213749)' , ncountry['City Location'])\nncountry['City Location'] = np.where ((ncountry[\"Country\"] == \"Republic of Moldova\"),'POINT (47.411631 28.369885)' , ncountry['City Location'])\nncountry['City Location'] = np.where ((ncountry[\"Country\"] == \"Netherlands\"),'POINT (52.132633 5.291266)' , ncountry['City Location'])\nncountry['City Location'] = np.where ((ncountry[\"Country\"] == \"Finland\"),'POINT (61.92411 25.748151)' , ncountry['City Location'])\nncountry['City Location'] = np.where ((ncountry[\"Country\"] == \"Honduras\"),'POINT (15.199999 -86.241905)' , ncountry['City Location'])\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cd = cd[cd['City Location'].notna()]\ndatasett = [cd, ncountry]\ncd = pd.concat(datasett)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cd['City'] = cd['City'].fillna('Unknown')\ncd['Population'].fillna(cd['Population'].mean(), inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cd.isnull().values.any()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"For **\"crr\"** dataset"},{"metadata":{},"cell_type":"markdown","source":"**Note:** The dictionary of climate change datasets for corporations responses **\"Full_Corporations_Response_Data_Dictionary copy.csv\"** says: 'Use this unique identifier to match question responses across multiple years. For E.g. If question C0.1 has changed it’s question number label to question C0.2 from 2019 to 2020 - use the data_point_id_id to find the same response at column level from 2019 to 2020, mapping responses to the same question across multiple years'.. In the 2019 year dataset there are some **\"NAN\"** values for **\"data_point_id\"** column. We can't match these questions for other years. So I'm gonna ignore these **\"NAN\"** values and fill them a unique number to identify them as **'1000000000'**"},{"metadata":{},"cell_type":"markdown","source":"* Drop the **\"accounting_period_to\"** column (%98 null values)\n* Fill **\"NAN\"** values with **3** (3, when non-response table questions with no columns) on the **column_number** column\n* Fill **\"NAN\"** values with **\"Non-Response Table\"** on the **row_name** column (non-response table questions)\n* Fill **\"NAN\"** values with **1000000000** on the **data_point_id** column\n* Fill **\"NAN\"** values with **\"No Response\"** on the **response_values** column\n* Fill **\"NAN\"** values with **\"No Comment\"** on the **comments** column\n\nNote: Check Related Data Dictionary \n\n**(../input/cdp-unlocking-climate-solutions/Corporations/Corporations Responses/Water Security/Full_Corporations_Response_Data_Dictionary.csv)**"},{"metadata":{"trusted":true},"cell_type":"code","source":"# Delete the \"accounting_period_to\" column from the \"crr\" dataframe\ncrr = crr.drop(\"accounting_period_to\", axis=1)\n\n# Fill \"NAN\" values with 3 on the column_number column\ncrr['column_number'] = crr['column_number'].fillna(3)\n\n# Fill \"NAN\" values with \"Non-Response Table\" on the row_name column\ncrr['row_name'] = crr['row_name'].fillna('Non-Response Table')\n\n# Fill \"NAN\" values with 1000000000 on the data_point_id column\ncrr['data_point_id'] = crr['data_point_id'].fillna(1000000000)\n\n#Fill **\"NAN\"** values with **\"No Response\"** on the **response_values** column\ncrr['response_value'] = crr['response_value'].fillna('No Response')\n\n# Fill **\"NAN\"** values with **\"No Comment\"** on the **comments** column\ncrr['comments'] = crr['comments'].fillna('No Comment')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Clean ’ like symbols\ncrr['question_unique_reference'] = \\\n crr['question_unique_reference']\\\n .replace(regex=r'’', value=\"'\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"crr.isnull().values.any()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"For **\"crd\"** dataset"},{"metadata":{},"cell_type":"markdown","source":"* Drop the **region** column (% 98 null values). We could fill **NANs** with the **Unknown** but this process not gonna bring us any information\n* Fill **\"NAN\"** values with **\"Unknown\"** on the **samples** column\n* Fill **\"NAN\"** values with **\"Unknown\"** on the **minimum_tier** column\n* Fill **\"NAN\"** values with **\"Unknown\"** on the **primary_ticker** column\n* Fill **\"NAN\"** values with **\"Unknown\"** on the **tickers** column\n* Fill **\"NAN\"** values with **\"Unknown\"** on the **primary_activity** column\n* Fill **\"NAN\"** values with **\"Unknown\"** on the **primary_sector** column\n* Fill **\"NAN\"** values with **\"Other\"** on the **primary_industry** column\n* Fill **\"NAN\"** values with **\"Not Available\"** on the **primary_questionnaire_sector** column\n\nNote: Check Related Data Dictionary \n\n**(../input/cdp-unlocking-climate-solutions/Corporations/Corporations Disclosing/Climate Change/Corporations_Disclosing_to_CDP_Data_Dictionary.csv)**"},{"metadata":{"trusted":true},"cell_type":"code","source":"# Drop \"region\" column\ncrd = crd.drop(\"region\", axis=1)\n\n# Fill \"NAN\" values with \"Unknown\" on the samples columnn\ncrd['samples'] = crd['samples'].fillna('Unknown')\n\n# Fill \"NAN\" values with \"Unknown\" on the minimum_tier column\ncrd['minimum_tier'] = crd['minimum_tier'].fillna('Unknown')\n\n# Fill \"NAN\" values with \"Unknown\" on the primary_ticker column\ncrd['primary_ticker'] = crd['primary_ticker'].fillna('Unknown')\n\n# Fill \"NAN\" values with \"Unknown\" on the tickers column\ncrd['tickers'] = crd['tickers'].fillna('Unknown')\n\n# Fill \"NAN\" values with \"Unknown\" on the primary_activity column\ncrd['primary_activity'] = crd['primary_activity'].fillna('Unknown')\n\n# Fill \"NAN\" values with \"Unknown\" on the primary_sector column\ncrd['primary_sector'] = crd['primary_sector'].fillna('Unknown')\n\n# Fill \"NAN\" values with \"Other\" on the primary_sector column\ncrd['primary_industry'] = crd['primary_industry'].fillna('Other')\n\n# Fill \"NAN\" values with \"Not Available\" on the primary_questionnaire_sector column\ncrd['primary_questionnaire_sector'] = crd['primary_questionnaire_sector'].fillna('Not Available')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"crd.isnull().values.any()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Deleting unneccessary datasets"},{"metadata":{"trusted":true},"cell_type":"code","source":"del crr_ws\ndel crr_cc\ndel crd_ws\ndel crd_cc\ndel cr_2018\ndel cr_2019\ndel cr_2020\ndel cd_2018\ndel cd_2019\ndel cd_2020\ndel crd_cc_2018\ndel crd_cc_2019\ndel crd_cc_2020\ndel crd_ws_2018\ndel crd_ws_2019\ndel crd_ws_2020\ndel crr_cc_2018\ndel crr_cc_2019\ndel crr_cc_2020\ndel crr_ws_2018\ndel crr_ws_2019\ndel crr_ws_2020","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"------------------------------------------------------------------------------------------"},{"metadata":{},"cell_type":"markdown","source":"# **Analysis**"},{"metadata":{},"cell_type":"markdown","source":"**Questions**"},{"metadata":{},"cell_type":"markdown","source":"[example notebook](https://www.kaggle.com/ellada/breaking-down-the-problem-reshaping-data-eda)"},{"metadata":{},"cell_type":"markdown","source":"Examination of provided (as pdf) ) questionnaire reveals very hiearchial structure of dataset. Main questions are grouped into modules, and there are many subdivisions to main questions, some are with multiple choices for organizations to respond to. Would it be helpful (for analysis) to subset data say by a Module and pre-process each module/df in sequence?"},{"metadata":{"trusted":true},"cell_type":"code","source":"data_f = crr['module_name'].value_counts().\\\n to_dict()\n\n#import plotly.express as px\nfig = px.funnel(x = list(data_f.values()),\n y = list(data_f.keys()))\n\nfig.update_layout(title_text='Number of rows per module (k=1000)')\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**Cities Responses** **(cr dataset)**\n\n* Question 0.1(**Introduction**)\n\n* Question 1.0(**Governance and Data Management- Vulnerability**)\n\n* Question 2.0(**Climate Hazards**)\n\n* Question 2.2(**Climate Hazards**)\n\n* Question 3(**Adaptation**) \n\n* Question 4.0(**City-wide Emission**)\n\n* Question 6.1(**Opportunuties**)\n\n* Question 8.0(**Energy**)\n\n* Question 10.11(**Transport**)\n\n* Question 12(**Food**)\n\n* Question 13(**Waste**)\n\n* Question 14.3(**Water Security**)\n\n* Question 14.0(**Water Security**)"},{"metadata":{},"cell_type":"markdown","source":"By analyzing as many questions as we can, we will try to find the KPIs that give us the best results."},{"metadata":{},"cell_type":"markdown","source":"**Climate Change**"},{"metadata":{"trusted":true},"cell_type":"code","source":"climate = pd.merge(crd, crr, 'inner', on=['survey_year', 'account_number', 'organization', 'response_received_date', 'topic'])\nclimate = climate[climate['topic'] == 'Climate Change']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"water = pd.merge(crd, crr, 'inner', on=['survey_year', 'account_number', 'organization', 'response_received_date', 'topic'])\nwater = water[water['topic'] == 'Water Security']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def get_climate(q, by='row_number', year=2020, cols=['organization']):\n val = climate[climate['question_number']==q]['question_unique_reference']\n sh = val.shape[0]\n if sh > 0:\n print(q, val.iloc[0], f'({sh})')\n return climate[(climate['survey_year'] == year) & (climate['question_number']==q)][cols + [by, 'column_name', 'response_value']].dropna(subset=['response_value'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def get_water(q, by='row_number', year=2020, cols=['organization']):\n val = water[water['question_number']==q]['question_unique_reference']\n val2 = water[(water['survey_year'] == year) & (water['question_number']==q)][cols + [by, 'column_name', 'response_value']].dropna(subset=['response_value'])\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":"Below, we have picked a set of key questions from these sections in the questionnaire. We believe these might be useful in building the KPIs for corporations with respect to their influence on climate change and the potential risk certain climate hazards pose to their operations."},{"metadata":{"trusted":true},"cell_type":"code","source":"qs_cc = ['C2.2', 'C2.2a', 'C2.3a', 'C2.4a', 'C4.1a', 'C4.1b', 'C4.2b',\n 'C4.3a', 'C4.3b', 'C4.5a', 'C6.1', 'C6.3', 'C6.5', 'C6.10', \n 'C7.1a', 'C7.2', 'C7.3a', 'C7.3b', 'C7.3c', 'C7.5', 'C7.6a', \n 'C7.6c','C7.6b', 'C8.1', 'C8.2', 'C8.2a', 'C8.2b', 'C8.2c',\n 'C8.2d', 'C8.2e', 'C12.1a', 'C12.1b', 'C12.3a']\n\nqs_cc =sorted(climate[climate['survey_year']==2019]['question_number'].unique())\n\nfor q in qs_cc:\n get_climate(q, year=2019)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cc2_2a = get_climate('C2.2a', by='row_name')\ncc2_2a.row_name.value_counts().plot(kind='bar', title='Risk areas considered by corporated (2020)');\n# cc2_2a.pivot(index=['organization', 'row_name'], columns='column_name')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cc2_3a = get_climate('C2.3a')\npiv2_3a = cc2_3a.pivot(index=['organization', 'row_number'], columns='column_name')['response_value']\npiv2_3a[['C2.3a_C3Risk type & Primary climate-related risk driver', 'C2.3a_C9Magnitude of impact']] \\\n.groupby('C2.3a_C3Risk type & Primary climate-related risk driver')['C2.3a_C9Magnitude of impact'] \\\n.value_counts().sort_values(ascending=False)[:60].unstack() \\\n.plot(kind='barh', stacked=True, title='Types of risks with their magnitude of impact on businesses (2020)');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"piv2_3a[['C2.3a_C4Primary potential financial impact', 'C2.3a_C9Magnitude of impact']] \\\n.groupby('C2.3a_C4Primary potential financial impact')['C2.3a_C9Magnitude of impact'] \\\n.value_counts().sort_values(ascending=False)[:48].unstack() \\\n.plot(kind='barh', stacked=True, title='Kind of financial impacts on business and their magnitudes (2020)');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cc2_4a = get_climate('C2.4a')\nccpiv2_4a = cc2_4a.pivot(index=['organization', 'row_number'], columns='column_name')['response_value']\n\nccpiv2_4a[['C2.4a_C3Opportunity type','C2.4a_C4Primary climate-related opportunity driver', 'C2.4a_C7Time horizon']] \\\n.groupby(['C2.4a_C3Opportunity type', 'C2.4a_C4Primary climate-related opportunity driver'])['C2.4a_C7Time horizon'].value_counts() \\\n.sort_values(ascending=False)[:60].unstack().plot(kind='barh', figsize=(10, 8), stacked=True, title='Opportunity type and drivers, and time horizons for their potential impacts on business (2020)');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"ccpiv2_4a[['C2.4a_C5Primary potential financial impact', 'C2.4a_C9Magnitude of impact']] \\\n.groupby('C2.4a_C5Primary potential financial impact')['C2.4a_C9Magnitude of impact'] \\\n.value_counts().sort_values(ascending=False)[:33].unstack() \\\n.plot(kind='barh', stacked=True, title='Financial impacts of identified opportunities and the magnitudes (2020)');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cc4_1a = get_climate('C4.1a')\ncc4_1a = cc4_1a[cc4_1a['response_value']!= 'No Response']\ncc4_1a.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"ccpiv4_1a = cc4_1a.pivot(index=['organization', 'row_number'], columns='column_name')['response_value'] \\\n[['C4.1a_C5Base year', 'C4.1a_C6Covered emissions in base year (metric tons CO2e)', 'C4.1a_C8Target year', 'C4.1a_C9Targeted reduction from base year (%)', 'C4.1a_C12% of target achieved [auto-calculated]']]\n\nfor col in ccpiv4_1a.columns:\n ccpiv4_1a[col] = ccpiv4_1a[col].astype('float64', copy=False )\n\nccpiv4_1a['Target'] = ccpiv4_1a['C4.1a_C6Covered emissions in base year (metric tons CO2e)'] - (ccpiv4_1a['C4.1a_C6Covered emissions in base year (metric tons CO2e)'] * ccpiv4_1a['C4.1a_C9Targeted reduction from base year (%)']/100)\nccpiv4_1a['Reporting'] = ccpiv4_1a['C4.1a_C6Covered emissions in base year (metric tons CO2e)'] - ((ccpiv4_1a['C4.1a_C12% of target achieved [auto-calculated]']/100) * (ccpiv4_1a['C4.1a_C6Covered emissions in base year (metric tons CO2e)'] - ccpiv4_1a['Target']))\nccpiv4_1a.drop(['C4.1a_C9Targeted reduction from base year (%)', 'C4.1a_C12% of target achieved [auto-calculated]'], axis=1, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cc6_1 = get_climate('C6.1', by='row_name')\ncc6_1 = cc6_1[cc6_1['response_value'] != 'No Response']\nccpiv6_1 = cc6_1.pivot(index=['organization', 'row_name'], columns='column_name')['response_value']\nccpiv6_1['C6.1_C1Gross global Scope 1 emissions (metric tons CO2e)'] = ccpiv6_1['C6.1_C1Gross global Scope 1 emissions (metric tons CO2e)'].astype('float32')\nccpiv6_1['C6.1_C1Gross global Scope 1 emissions (metric tons CO2e)'].sort_values(ascending=False)[:120].unstack() \\\n.plot(kind='barh', figsize=(8, 14), logx=True, width=0.9, title='Organization yearly Scope 1 emissions in metric tonnes (reported 2020)');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cc6_3 = get_climate(\"C6.3\", by='row_name')\ncc6_3 = cc6_3[cc6_3['response_value'] != 'No Response']\nccpiv6_3 = cc6_3.pivot(index=['organization', 'row_name'], columns='column_name')['response_value']\nccpiv6_3['C6.3_C1Scope 2, location-based'] = ccpiv6_3['C6.3_C1Scope 2, location-based'].astype('float32')\nccpiv6_3['C6.3_C2Scope 2, market-based (if applicable)'] = ccpiv6_3['C6.3_C2Scope 2, market-based (if applicable)'].astype('float32')\nccpiv6_3['sum'] = ccpiv6_3['C6.3_C1Scope 2, location-based'] + ccpiv6_3['C6.3_C2Scope 2, market-based (if applicable)']\n\nccpiv6_3.sort_values(by='sum', ascending=False)[:80].drop('sum', axis=1) \\\n.plot(kind='barh', figsize=(10, 14), logx=True, width=0.9, title='Organization location and market based Scope 2 emissions in metric tonnes (reported 2020)');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cc6_5 = get_climate(\"C6.5\", by='row_name')\ncc6_5 = cc6_5[cc6_5['response_value'] != 'No Response']\nccpiv6_5 = cc6_5.pivot(index=['organization', 'row_name'], columns='column_name')['response_value']['C6.5_C2Metric tonnes CO2e']\nccpiv6_5 = ccpiv6_5.astype('float32')\n\nccpiv6_5.groupby('row_name').sum().sort_values().plot(kind='bar', title='Global Scope 3 emissions by activity/source (2020)');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"ccpiv6_5 = ccpiv6_5.unstack()\nccpiv6_5['sum'] = ccpiv6_5.sum(axis=1)\nccpiv6_5.sort_values(by='sum', ascending=False)[:60].drop('sum', axis=1) \\\n.plot(kind='barh', figsize=(12, 12), width=0.91, stacked=True, cmap='tab20', title='Organization Scope 3 emissions by activity/source (2020)').legend(bbox_to_anchor=(1, 0.6));","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cc7_1a = get_climate('C7.1a')\ncc7_1a = cc7_1a[cc7_1a['response_value'] != 'No Response']\nccpiv7_1a = cc7_1a.pivot(index=['organization', 'row_number'], columns='column_name')['response_value'].reset_index().drop(['row_number', 'C7.1a_C3GWP Reference'], axis=1)\nccpiv7_1a['C7.1a_C2Scope 1 emissions (metric tons of CO2e)'] = ccpiv7_1a['C7.1a_C2Scope 1 emissions (metric tons of CO2e)'].astype('float32')\nccpiv7_1a = ccpiv7_1a.pivot_table(index=['organization'], columns='C7.1a_C1Greenhouse gas', aggfunc=sum)['C7.1a_C2Scope 1 emissions (metric tons of CO2e)'] \\\n[['CO2', 'CO2: Refrigerants', 'CH4', 'N2O', 'HFCs', 'PFCs', 'SF6', 'NF3']]\n\nccpiv7_1a['sum'] = ccpiv7_1a.sum(axis=1)\nccpiv7_1a.sort_values('sum', ascending=False)[:60].drop('sum', axis=1) \\\n.plot(kind='barh', stacked=True, figsize=(10,10), title='Breakdown of Scope 1 emissions by Greenhouse Gas of top emitters (2020)');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cc7_2 = get_climate('C7.2')\ncc7_2 = cc7_2[cc7_2['response_value'] != 'No Response']\nccpiv7_2 = cc7_2.pivot(index=['organization', 'row_number'], columns='column_name')['response_value']\n\nccpiv7_2['C7.2_C2Scope 1 emissions (metric tons CO2e)'] = ccpiv7_2['C7.2_C2Scope 1 emissions (metric tons CO2e)'].astype('float32')\nccpiv7_2.groupby(['C7.2_C1Country/Region'])['C7.2_C2Scope 1 emissions (metric tons CO2e)'].sum().sort_values(ascending=False)[:30] \\\n.plot(kind='barh', figsize=(8,8), logx=True, title='Total Scope 1 emissions by country/region (2020)').legend(bbox_to_anchor=(0.2, 1));","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"ccpiv7_2.groupby(['C7.2_C1Country/Region'])['C7.2_C2Scope 1 emissions (metric tons CO2e)'].mean() \\\n.sort_values(ascending=False)[:80].plot(kind='barh', figsize=(10,14), logx=True, title='Mean Scope 1 emissions by country/region (2020)');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cc7_5 = get_climate('C7.5')\ncc7_5 = cc7_5[cc7_5['response_value'] != 'No Response']\nccpiv7_5 = cc7_5.pivot(index=['organization', 'row_number'], columns='column_name')['response_value']\nccpiv7_5['C7.5_C2Scope 2, location-based (metric tons CO2e)'] = ccpiv7_5['C7.5_C2Scope 2, location-based (metric tons CO2e)'].astype('float32')\nccpiv7_5['C7.5_C3Scope 2, market-based (metric tons CO2e)'] = ccpiv7_5['C7.5_C3Scope 2, market-based (metric tons CO2e)'].astype('float32')\nccpiv7_5['total CO2e'] = ccpiv7_5['C7.5_C2Scope 2, location-based (metric tons CO2e)'] + ccpiv7_5['C7.5_C3Scope 2, market-based (metric tons CO2e)']\nccpiv7_5.groupby(['C7.5_C1Country/Region'])['total CO2e'].sum().sort_values(ascending=False)[:75] \\\n.plot(kind='barh', logx=True, figsize=(10,12), title='Total Scope 2 emissions by region (2020)');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"ccpiv7_5.groupby(['C7.5_C1Country/Region'])['total CO2e'].mean().sort_values(ascending=False)[:75] \\\n.plot(kind='barh', figsize=(10,12), logx=True, title='Mean Scope 2 emissions by region (2020)');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"get_climate('C8.1')['response_value'].value_counts() \\\n.plot(kind='barh', title='Organizational spend on energy (2020)', figsize=(10, 6));","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"get_climate('C8.2', by='row_name').groupby('row_name')['response_value'].value_counts().unstack() \\\n.plot(kind='bar', title='Energy related activities undertaken by organizations (2020)', figsize=(10, 6));","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cc8_2a = get_climate('C8.2a', by='row_name')\ncc8_2a = cc8_2a[cc8_2a['response_value'] != 'No Response']\ncc8_2a.pivot(index=['organization', 'row_name'], columns='column_name')['response_value'] \\\n[['C8.2a_C2MWh from renewable sources', 'C8.2a_C3MWh from non-renewable sources']] \\\n.astype('float32').groupby(['row_name']).sum() \\\n.plot(kind='bar', figsize=(8, 6), title='Global energy consumption by organizations by source (2020)');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"get_climate('C8.2b', by='row_name').groupby('row_name')['response_value'].value_counts().unstack() \\\n.plot(kind='barh', title='Applications of fuel use by organization (2020)', figsize=(10, 6));","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cc8_2c = get_climate('C8.2c')\ncc8_2c = cc8_2c[cc8_2c['response_value'] != 'No Response']\nccpiv8_2c = cc8_2c.pivot(index=['organization', 'row_number'], columns='column_name')['response_value']\nccpiv8_2c['C8.2c_C3Total fuel MWh consumed by the organization'] = ccpiv8_2c['C8.2c_C3Total fuel MWh consumed by the organization'].astype('float64')\n\nccpiv8_2c.groupby(['organization', 'C8.2c_C1Fuels (excluding feedstocks)'])['C8.2c_C3Total fuel MWh consumed by the organization'].sum() \\\n.sort_values(ascending=False)[:120].unstack() \\\n.plot(kind='barh', stacked=True, figsize=(10,14), cmap='tab20', title='Breakdown of fuels consumption in MWh by organizations (2020)');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cc8_2d = get_climate('C8.2d', by='row_name')\ncc8_2d = cc8_2d[cc8_2d['response_value'] != 'No Response']\nccpiv8_2d = cc8_2d.pivot(index=['organization', 'row_name'], columns='column_name')['response_value'].astype('float32') \\\n[['C8.2d_C1Total Gross generation (MWh)', 'C8.2d_C3Gross generation from renewable sources (MWh)']].groupby('row_name').sum()\nccpiv8_2d['Gross generation from non-renewable sources'] = ccpiv8_2d['C8.2d_C1Total Gross generation (MWh)'] - ccpiv8_2d['C8.2d_C3Gross generation from renewable sources (MWh)']\n\nccpiv8_2d.drop('C8.2d_C1Total Gross generation (MWh)', axis=1) \\\n.plot(kind='bar', title='Comparisions between generation of electricity, heat, steam, cooling from renewable and non-renewable sources (2020)', figsize=(10, 8)).legend(bbox_to_anchor=(1,0.1));","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cc12_1a = get_climate('C12.1a')\ncc12_1a = cc12_1a[cc12_1a['response_value'] != 'No Response']\ncc12_1a.pivot(index=['organization', 'row_number'], columns='column_name')['response_value']['C12.1a_C2Details of engagement'].value_counts()[:14] \\\n.plot(kind='barh', figsize=(8, 8), title='Climate related supplier engangement strategies (2020)');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cc12_3a = get_climate('C12.3a').pivot(index=['organization', 'row_number'], columns='column_name')['response_value'] \\\n.groupby(['C12.3a_C1Focus of legislation'])['C12.3a_C2Corporate position'].value_counts().unstack()\ncc12_3a['sum'] = cc12_3a.sum(axis=1)\n\ncc12_3a.sort_values('sum', ascending=False).drop('sum', axis=1)[:8] \\\n.plot(kind='barh', stacked=True, title='Engagement with policy makers by issues and position (2020)');","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**Water Security**"},{"metadata":{},"cell_type":"markdown","source":"Once more, we pick a set of most important questions which are relevant towards gaining insights into building KPIs for corporations with respect to their water security and risk."},{"metadata":{"trusted":true},"cell_type":"code","source":"qs_ws = ['W1.2', 'W1.2b', 'W1.2h', 'W1.2i', 'W1.4a', 'W1.4b', \n 'W3.3a', 'W3.3b', 'W3.3c', 'W3.3d', 'W4.1b', \n 'W4.1c', 'W4.2', 'W4.2a', 'W4.3a', 'W5.1', \n 'W5.1a', 'W8.1', 'W8.1b']\n\nfor q in qs_ws:\n get_water(q, year=2020)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"w1_2 = get_water('W1.2', by='row_name').pivot(index=['organization', 'row_name'], columns='column_name')['response_value']['W1.2_C1% of sites/facilities/operations']\nw1_2.groupby('row_name').value_counts().unstack() \\\n.plot(kind='barh', stacked=True, figsize=(10, 8), title='Proportion of water related aspects that are regularly measured and monitored across organization operations (2020)').legend(bbox_to_anchor=(1,1));","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"w1_2j = get_water('W1.2j', by='row_name', cols=['country', 'organization'], year=2019).pivot(index=['country', 'organization', 'row_name'], columns='column_name')['response_value']\nw1_2j.groupby('country')['W1.2j_C1% recycled and reused'].value_counts().unstack(-1) \\\n.plot(kind='barh', figsize=(8, 6), stacked=True, title='Water recycled by country (US and Canada 2019)');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"w4_2 = get_water('W4.2').pivot(index=['organization', 'row_number'], columns='column_name')['response_value']\nplt.subplots(figsize=(10, 8))\nsns.heatmap(w4_2.groupby(['W4.2_C2Type of risk & Primary risk driver_G', 'W4.2_C2Type of risk & Primary risk driver'])['W4.2_C3Primary potential impact'] \\\n .value_counts().sort_values(ascending=False)[:40].unstack().T, annot=True);\n\nplt.xticks(rotation=65, horizontalalignment='right',)\nplt.title('Type of risks and risk drivers affecting direct operations with their potential impacts (2020)')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plt.subplots(figsize=(10, 8))\nsns.heatmap(w4_2.groupby(['W4.2_C2Type of risk & Primary risk driver_G', 'W4.2_C2Type of risk & Primary risk driver'])['W4.2_C13Primary response to risk'] \\\n .value_counts().sort_values(ascending=False)[:40].unstack().T, annot=True);\n\nplt.xticks(rotation=65, horizontalalignment='right')\nplt.title('Type of risks and risk drivers affecting direct operations by primary response to the risks (2020)')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"w4_2a = get_water('W4.2a').pivot(index=['organization', 'row_number'], columns='column_name')['response_value']\nplt.subplots(figsize=(10, 8))\nsns.heatmap(w4_2a.groupby(['W4.2a_C3Type of risk & Primary risk driver_G', 'W4.2a_C3Type of risk & Primary risk driver'])['W4.2a_C4Primary potential impact'] \\\n .value_counts().sort_values(ascending=False)[:].unstack(), annot=True);\n\nplt.xticks(rotation=65, horizontalalignment='right',)\nplt.title('Type of risks and risk drivers beyond direct operations and their primary impacts (2020)')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"w4_3a = get_water('W4.3a').pivot(index=['organization', 'row_number'], columns='column_name')['response_value']\nw4_3a.groupby(['W4.3a_C1Type of opportunity', 'W4.3a_C2Primary water-related opportunity'])['W4.3a_C5Magnitude of potential financial impact'].value_counts() \\\n.sort_values()[51:].unstack().plot(kind='barh', stacked=True, figsize=(12, 7), title='Opportunities identified as potentially having financial impacts on organizations - by magnitude of impact (2020)');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"get_water('W5.1a', cols=['country', 'organization']).pivot(index=['country', 'organization', 'row_number'], columns='column_name')['response_value']['W5.1a_C1% verified'].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"w5_1c = get_water('W5.1c', year=2019, cols=['country', 'organization']).pivot(index=['country', 'organization', 'row_number'], columns='column_name')['response_value']\nw5_1c.groupby('country')['W5.1c_C3% recycled or reused'].value_counts().unstack(0) \\\n.plot(kind='barh', stacked=True, figsize=(8, 6), title='Proportion of water recycled/reused at a facility by home country of organization (2019)');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"w8_1 = get_water('W8.1', cols=['organization', 'country']).pivot(index=['country', 'organization', 'row_number'], columns='column_name')['response_value']\nw8_1['W8.1_C1Levels for targets and/or goals'].str.split(';').explode().to_frame().groupby('country')['W8.1_C1Levels for targets and/or goals'].value_counts() \\\n.sort_values(ascending=False)[:26].unstack(0).plot(kind='barh', figsize=(8, 6), stacked=True, title='Organization approach to setting and monitoring water targets/goals by home country of organization (2020)');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"w8_1a = get_water('W8.1a').pivot(index=['organization', 'row_number'], columns='column_name')['response_value']\nplt.subplots(figsize=(10, 8))\nsns.heatmap(w8_1a.groupby('W8.1a_C2Category of target')['W8.1a_C4Primary motivation'].value_counts().sort_values(ascending=False)[:50].unstack(), annot=True);\nplt.xticks(rotation=65, horizontalalignment='right',)\nplt.title('Category of organization water targets by their primary motivation (2020)')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"w8_1b = get_water('W8.1b', cols=['country', 'organization']).pivot(index=['country', 'organization', 'row_number'], columns='column_name')['response_value']\nw8_1b.groupby(['country'])['W8.1b_C1Goal'].value_counts().sort_values(ascending=False)[:90].unstack(0)[:12] \\\n.plot(kind='barh', stacked=True, title='Organization water goals by home country of organization (2020)');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"w9_1a = get_water('W9.1a', cols=['country', 'organization'], year=2019).pivot(index=['country', 'organization', 'row_number'], columns='column_name')['response_value']\nw9_1a.groupby('W9.1a_C1Linkage or tradeoff')['W9.1a_C2Type of linkage/tradeoff'].value_counts().sort_values(ascending=False)[:20].unstack(0) \\\n.plot(kind='barh', stacked=True, figsize=(10, 7), title='Linkages and tradeoffs related to other environmental issues (2019)');","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**City Responses**"},{"metadata":{"trusted":true},"cell_type":"code","source":"plt.figure(figsize=(20,15))\nax1=plt.subplot(121, aspect='equal')\ncount_org_per_region=cr.groupby('CDP Region')['City'].count()\ncount_org_per_region.plot(kind='pie',ax=ax1,autopct='%1.1f%%')\nplt.title('Count of organization by CDP region')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"x = cr.groupby('Year Reported to CDP').size()\nx.sort_values(ascending = False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plt.figure(figsize=(15, 8))\n\nfreq = len(cr)\n\nsns.set_palette(\"rocket\")\n\ng = sns.countplot(cr['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":{"trusted":true},"cell_type":"code","source":"x = cr.groupby('CDP Region').size()\nx.sort_values(ascending = False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plt.figure(figsize=(18, 8))\n\nfreq = len(cr)\n\nsns.set_palette(\"Paired\")\n\ng = sns.countplot(cr['CDP Region'], order = cr['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":{"trusted":true},"cell_type":"code","source":"group = cr.groupby('Country').size()\ngroup.sort_values(ascending = False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"top_10 = cr['Country'].value_counts()\ntop_10 = top_10[:10,]\ntop_10","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plt.figure(figsize=(18, 8))\n\nfreq = len(cr)\n\nsns.set_palette(\"Paired\")\n\ng = sns.barplot(top_10.index, top_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":{"trusted":true},"cell_type":"code","source":"x = cr.groupby('City').size()\nx.sort_values(ascending = False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"top = cr['City'].value_counts()\ntop_10 = top[:10,]\n\nplt.figure(figsize=(18, 8))\n\nfreq = len(cr)\n\nsns.set_palette(\"Paired\")\n\ng = sns.barplot(top_10.index, top_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)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plt.figure(figsize=(20,10))\ncr.groupby('Country').nunique()['Response Answer'].sort_values(ascending=False)[:50].plot(kind='bar')\nplt.ylabel('Countries per responses')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The United States boasts the largest number of cities engaging in questions over climate hazards and future climate scenarios. They are distantly followed up by Brazil, Canada, Argentina and the United Kingdom. We will focus my analysis on the top ten countries in this dataset"},{"metadata":{"trusted":true},"cell_type":"code","source":"# Number of coments per city\ncity_group = cr.groupby('City')['Response Answer'].count().reset_index().sort_values('Response Answer', ascending=False).head(15)\n\nfig = px.bar(city_group, x='City', y='Response Answer')\nfig.update_layout(title_text='Number of comments per City', template=\"plotly_white\")\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Toronto has the most city responses to whether they see climate change as an opportunity or concern. They were closely followed by the city of Tarakan. The rest of the cities in this list are a little far behind."},{"metadata":{"trusted":true},"cell_type":"code","source":"def get_top_n_words(corpus, n=None):\n vec = CountVectorizer(stop_words = 'english').fit(corpus)\n bag_of_words = vec.transform(corpus)\n sum_words = bag_of_words.sum(axis=0) \n words_freq = [(word, sum_words[0, idx]) for word, idx in vec.vocabulary_.items()]\n words_freq =sorted(words_freq, key = lambda x: x[1], reverse=True)\n return words_freq[:n]\n\ncommon_words = get_top_n_words(cr['Response Answer'], 20)\nfor word, freq in common_words:\n print(word, freq)\ndf1 = pd.DataFrame(common_words, columns = ['word' , 'count'])\n\nfig = px.bar(df1, x='word', y='count')\nfig.update_layout(title_text='Response Answer word count top 20', template=\"plotly_white\")\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The most commonly used words in the city answers are applicable, water, energy and climate, followed up by emission and change"},{"metadata":{"trusted":true},"cell_type":"code","source":" def get_top_n_bigram(corpus, n=None):\n vec = CountVectorizer(ngram_range=(2, 2), stop_words='english').fit(corpus)\n bag_of_words = vec.transform(corpus)\n sum_words = bag_of_words.sum(axis=0) \n words_freq = [(word, sum_words[0, idx]) for word, idx in vec.vocabulary_.items()]\n words_freq =sorted(words_freq, key = lambda x: x[1], reverse=True)\n return words_freq[:n]\ncommon_words = get_top_n_bigram(cr['Response Answer'], 20)\nfor word, freq in common_words:\n print(word, freq)\ndf2 = pd.DataFrame(common_words, columns = ['word' , 'count'])\n\nfig = px.bar(df2, x='word', y='count')\nfig.update_layout(title_text='Answers bigram count top 20', template=\"plotly_white\")\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"From the top bigrams in the comments, we can see that the cities are generally more concerned about climate change and public health. Most cities view that climate hazards represent either a significant or moderate challenge, while other cities assert the need for sustainable development and their desire for public health."},{"metadata":{"trusted":true},"cell_type":"code","source":"cities = cr[(cr[\"Question Number\"] == '6.0') | (cr[\"Question Number\"] == '2.2')]\n\ncities = cities.loc[:, ['Year Reported to CDP', 'City', 'Country', 'CDP Region', 'Section', 'Question Name', 'Response Answer']]\n\ndf = cities.loc[(cities['Country'] == 'United States of America') | (cities['Country'] == 'Canada')\n | (cities['Country'] == 'United Kingdom of Great Britain and Northern Ireland')\n | (cities['Country'] == 'Brazil') | (cities['Country'] == 'Mexico') | (cities['Country'] == 'Peru')\n | (cities['Country'] == 'Portugal') | (cities['Country'] == 'Italy')\n | (cities['Country'] == 'Australia') | (cities['Country'] == 'Argentina')]\n\ndf['Country'].unique()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def comment_len(x):\n if type(x) is str:\n return len(x.split())\n else:\n return 0\n\n \ndf = df[df['Year Reported to CDP'] == 2020]\n\ndf['answer_len'] = df['Response Answer'].apply(comment_len)\nnums_comment = df.query('answer_len > 0')['answer_len']\n\nfig = ff.create_distplot(hist_data = [nums_comment], group_labels = ['Response Answer'])\nfig.update_layout(title_text='Distribution of word count in comment', template=\"plotly_white\")\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"Milano_comment = df.query(\"answer_len > 0 and City == 'Comune di Milano'\")['answer_len']\nSydney_comment = df.query(\"answer_len > 0 and City == 'City of Sydney'\")['answer_len']\nBuffalo_comment = df.query(\"answer_len > 0 and City == 'City of Buffalo'\")['answer_len']\n\nfig = ff.create_distplot(hist_data=[Milano_comment, Sydney_comment, Buffalo_comment],\n group_labels=['Milano', 'Sydney', 'Buffalo'],\n colors=px.colors.qualitative.Plotly[4:], show_hist=False)\n\nfig.update_layout(title_text=\"Answers word count vs. Cities\", xaxis_title='word count', template=\"plotly_white\")\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Most of the questions were answered by the three cities in less than 10 words. Whereas the maximum words in the responses of the City of Buffalo goes to 46 words in length, the answers of the city of Sydney and the city of Milano reached 227 and 379 words respectively."},{"metadata":{},"cell_type":"markdown","source":"**Sentiment Polarity**"},{"metadata":{},"cell_type":"markdown","source":"We will use TextBlob to calculate sentiment polarity which lies in the range of [-1,1] where 1 means positive sentiment and -1 means a negative sentiment."},{"metadata":{"trusted":true},"cell_type":"code","source":"df['sentiment'] = df['Response Answer'].apply(lambda x: TextBlob(x).sentiment[0])\n\ndf.loc[df['sentiment'] > 0, 'Opportunity_Concern'] = 'Opportunity'\ndf.loc[df['sentiment'] == 0, 'Opportunity_Concern'] = 'Opportunity'\ndf.loc[df['sentiment'] < 0, 'Opportunity_Concern'] = 'Concern'\n\ndf.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"temp = df.groupby('Opportunity_Concern').count()['Response Answer'].reset_index().sort_values(by='Response Answer',ascending=False)\ntemp.style.background_gradient(cmap='Purples')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"sentiment = df['Opportunity_Concern'].value_counts()\nfig = {\n \"data\": [\n {\n \"values\": sentiment.values,\n \"labels\": sentiment.index,\n \"domain\": {\"x\": [0, .6]},\n \"name\": \"Region\",\n \"hoverinfo\":\"label+percent+name\",\n \"hole\": .5,\n \"type\": \"pie\"\n },],\n \"layout\": {\n \"annotations\": [\n { \"font\": { \"size\": 20},\n \"showarrow\": False,\n \"text\": \"CDP Opportunity Concern\",\n \"x\": 0.50,\n \"y\": 1\n },\n ]\n }\n}\niplot(fig)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df.loc[df['Opportunity_Concern'] == 'Opportunity'].Country.value_counts()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The comments that view climate change as an opportunity werer largely collected from cities in the US."},{"metadata":{"trusted":true},"cell_type":"code","source":"df.loc[df['Opportunity_Concern'] == 'Concern'].Country.value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"fig = px.histogram(df, x=\"sentiment\")\nfig.update_layout(title_text='Distribution of sentiment polarity in Cities answers', template=\"plotly_white\")\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"From the sentiment polarity distribution above, we can see that many cities did not answer or provided very short answers regarding climate hazards (there is a significant peak close to 0). This could be because climate change is a relatively new area and cities are finding it difficult to calculate it and thus unable to discuss clearly how it could be an opportunity or concern to them"},{"metadata":{"trusted":true},"cell_type":"code","source":"fig = px.scatter(df, x='sentiment', y='answer_len', color='Opportunity_Concern', template=\"plotly_white\")\nfig.update_layout(title_text='Sentiment polarity')\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The scatter plot shows a clear distinction between opportunity and concern over future climate scenarios."},{"metadata":{"trusted":true},"cell_type":"code","source":"fig = px.histogram(df, x=\"answer_len\", y=\"Country\", color=\"Opportunity_Concern\",\n marginal=\"box\",\n hover_data=df.columns, nbins=100)\nfig.update_layout(title_text='Distribution of sentiment per country', template=\"plotly_white\")\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"Columbus_polarity = df.query(\"sentiment != 1000 and City == 'City of Columbus'\")['sentiment']\nVancouver_polarity = df.query(\"sentiment != 1000 and City == 'City of Vancouver'\")['sentiment']\nBuffalo_polarity = df.query(\"sentiment != 1000 and City == 'City of Buffalo'\")['sentiment']\n\nfig = ff.create_distplot(hist_data=[Columbus_polarity, Vancouver_polarity, Buffalo_polarity],\n group_labels=['City of Columbus', 'City of Vancouver', 'City of Buffalo'],\n colors=px.colors.qualitative.Plotly[5:], show_hist=False)\n\nfig.update_layout(title_text=\"Comment sentiment polarity vs cities\", xaxis_title='sentiment polarity', template=\"plotly_white\")\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"This means that the city of Buffalo are more optimistic over opportunities within future climate scenarios compared to the city of Vancouver and Colombus."},{"metadata":{"trusted":true},"cell_type":"code","source":"df2 = df.loc[(df['City'] == 'City of City of Vancouver') | (df['City'] == 'City of Charlotte') | (df['City'] == 'City of Columbus') | (df['City'] == 'City of Eugene')]\npolarity_group = df2.groupby('City')['sentiment'].mean().reset_index()\n\ncolors = ['lightslategray',] * 5\ncolors[2] = 'crimson'\nfig = go.Figure(data=[go.Bar(\n x=polarity_group['City'].unique(),\n y=polarity_group['sentiment'],\n marker_color=colors \n)])\nfig.update_layout(title_text='Lowest average sentment polarity')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Answers from the city of Eugene had the lowest average sentiment polarity score. In part, the city of Eugene did not provide as many answers as the city of Charlotte or Colombus."},{"metadata":{},"cell_type":"markdown","source":"# **Naturel Language Processing**"},{"metadata":{},"cell_type":"markdown","source":"**1. Sentiment Analysis**"},{"metadata":{},"cell_type":"markdown","source":"In this section, we will explore text mining techniques for sentiment analysis to detect whether a city sees opportunity (Cities Question 6.0) or concern (City Question 2.2) over future climate scenarios. First, we clean the text data, removing stop words and stemming. To infer the tweets’ sentiment we use two classifiers: logistic regression and multinomial naive Bayes."},{"metadata":{"trusted":true},"cell_type":"code","source":"df = df.loc[(df['Country'] == 'United States of America') | (df['Country'] == 'Canada')\n | (df['Country'] == 'United Kingdom of Great Britain and Northern Ireland')]\n\ndf = df.loc[:, ['Response Answer', 'Opportunity_Concern']]\ndf.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**1.1 Text cleaning**"},{"metadata":{},"cell_type":"markdown","source":"we’ll perform the following actions:\n\n* set all words to lowercase\n* remove all punctuations, including the question and exclamation marks\n* remove digits\n* remove stopwords\n* apply the PorterStemmer to keep the stem of the words"},{"metadata":{"trusted":true},"cell_type":"code","source":"df.rename(columns={'Response Answer':'Response_Answer'}, inplace=True)\n\nclass TextCounts(BaseEstimator, TransformerMixin):\n \n def count_regex(self, pattern, Response_Answer):\n return len(re.findall(pattern, Response_Answer))\n \n def fit(self, X, y=None, **fit_params):\n # fit method is used when specific operations need to be done on the train data, but not on the test data\n return self\n \n def transform(self, X, **transform_params):\n count_words = X.apply(lambda x: self.count_regex(r'\\w+', x)) \n count_mentions = X.apply(lambda x: self.count_regex(r'@\\w+', x))\n count_hashtags = X.apply(lambda x: self.count_regex(r'#\\w+', x))\n count_capital_words = X.apply(lambda x: self.count_regex(r'\\b[A-Z]{2,}\\b', x))\n count_excl_quest_marks = X.apply(lambda x: self.count_regex(r'!|\\?', x))\n count_urls = X.apply(lambda x: self.count_regex(r'http.?://[^\\s]+[\\s]?', x))\n # We will replace the emoji symbols with a description, which makes using a regex for counting easier\n # Moreover, it will result in having more words in the tweet\n count_emojis = X.apply(lambda x: emoji.demojize(x)).apply(lambda x: self.count_regex(r':[a-z_&]+:', x))\n \n df = pd.DataFrame({'count_words': count_words\n , 'count_mentions': count_mentions\n , 'count_hashtags': count_hashtags\n , 'count_capital_words': count_capital_words\n , 'count_excl_quest_marks': count_excl_quest_marks\n , 'count_urls': count_urls\n , 'count_emojis': count_emojis\n })\n \n return df\ntc = TextCounts()\ndf_eda = tc.fit_transform(df.Response_Answer)\ndf_eda['Opportunity_Concern'] = df.Opportunity_Concern","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"class CleanText(BaseEstimator, TransformerMixin):\n def remove_mentions(self, input_text):\n return re.sub(r'@\\w+', '', input_text)\n \n def remove_urls(self, input_text):\n return re.sub(r'http.?://[^\\s]+[\\s]?', '', input_text)\n \n def emoji_oneword(self, input_text):\n # By compressing the underscore, the emoji is kept as one word\n return input_text.replace('_','')\n \n def remove_punctuation(self, input_text):\n # Make translation table\n punct = string.punctuation\n trantab = str.maketrans(punct, len(punct)*' ') # Every punctuation symbol will be replaced by a space\n return input_text.translate(trantab)\n def remove_digits(self, input_text):\n return re.sub('\\d+', '', input_text)\n \n def to_lower(self, input_text):\n return input_text.lower()\n \n def remove_stopwords(self, input_text):\n stopwords_list = stopwords\n # Some words which might indicate a certain sentiment are kept via a whitelist\n whitelist = [\"n't\", \"not\", \"no\"]\n words = input_text.split() \n clean_words = [word for word in words if (word not in stopwords_list or word in whitelist) and len(word) > 1] \n return \" \".join(clean_words) \n \n def stemming(self, input_text):\n porter = PorterStemmer()\n words = input_text.split() \n stemmed_words = [porter.stem(word) for word in words]\n return \" \".join(stemmed_words)\n \n def fit(self, X, y=None, **fit_params):\n return self\n \n def transform(self, X, **transform_params):\n clean_X = X.apply(self.remove_mentions).apply(self.remove_urls).apply(self.emoji_oneword).apply(self.remove_punctuation).apply(self.remove_digits).apply(self.to_lower).apply(self.remove_stopwords).apply(self.stemming)\n return clean_X","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# A sample on how the cleaned text variable will look like:\nct = CleanText()\nsr_clean = ct.fit_transform(df.Response_Answer)\nsr_clean.sample(5)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"A down-side of the text cleaning is that some rows do not have any words left in their text. We fix this by imputing the missing value with some placeholder text such as: 'no_text'"},{"metadata":{"trusted":true},"cell_type":"code","source":"empty_clean = sr_clean == ''\nprint('{} records have no words left after text cleaning'.format(sr_clean[empty_clean].count()))\nsr_clean.loc[empty_clean] = '[no_text]'","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cv = CountVectorizer()\nbow = cv.fit_transform(sr_clean)\nword_freq = dict(zip(cv.get_feature_names(), np.asarray(bow.sum(axis=0)).ravel()))\nword_counter = collections.Counter(word_freq)\nword_counter_df = pd.DataFrame(word_counter.most_common(20), columns = ['word', 'freq'])\nfig, ax = plt.subplots(figsize=(16, 12))\nsns.barplot(x=\"word\", y=\"freq\", data=word_counter_df, palette=\"PuBuGn_d\", ax=ax)\nplt.show();","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**1.2 Test data**"},{"metadata":{"trusted":true},"cell_type":"code","source":"# Creating test data\ndf_model = df_eda\ndf_model['clean_text'] = sr_clean\ndf_model.columns.tolist()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"class ColumnExtractor(TransformerMixin, BaseEstimator):\n def __init__(self, cols):\n self.cols = cols\n def transform(self, X, **transform_params):\n return X[self.cols]\n def fit(self, X, y=None, **fit_params):\n return self\nX_train, X_test, y_train, y_test = train_test_split(df_model.drop('Opportunity_Concern', axis=1), df_model.Opportunity_Concern, test_size=0.1, random_state=37)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**1.3 Evaluation metrics**"},{"metadata":{"trusted":true},"cell_type":"code","source":"def grid_vect(clf, parameters_clf, X_train, X_test, parameters_text=None, vect=None, is_w2v=False):\n \n textcountscols = ['count_capital_words','count_emojis','count_excl_quest_marks','count_hashtags'\n ,'count_mentions','count_urls','count_words']\n \n if is_w2v:\n w2vcols = []\n for i in range(SIZE):\n w2vcols.append(i)\n features = FeatureUnion([('textcounts', ColumnExtractor(cols=textcountscols))\n , ('w2v', ColumnExtractor(cols=w2vcols))]\n , n_jobs=-1)\n else:\n features = FeatureUnion([('textcounts', ColumnExtractor(cols=textcountscols))\n , ('pipe', Pipeline([('cleantext', ColumnExtractor(cols='clean_text')), ('vect', vect)]))]\n , n_jobs=-1)\n \n pipeline = Pipeline([\n ('features', features)\n , ('clf', clf)\n ])\n \n # Join the parameters dictionaries together\n parameters = dict()\n if parameters_text:\n parameters.update(parameters_text)\n parameters.update(parameters_clf)\n # Make sure you have scikit-learn version 0.19 or higher to use multiple scoring metrics\n grid_search = GridSearchCV(pipeline, parameters, n_jobs=-1, verbose=1, cv=5)\n \n print(\"Performing grid search...\")\n print(\"pipeline:\", [name for name, _ in pipeline.steps])\n print(\"parameters:\")\n pprint(parameters)\n t0 = time()\n grid_search.fit(X_train, y_train)\n print(\"done in %0.3fs\" % (time() - t0))\n print()\n print(\"Best CV score: %0.3f\" % grid_search.best_score_)\n print(\"Best parameters set:\")\n best_parameters = grid_search.best_estimator_.get_params()\n for param_name in sorted(parameters.keys()):\n print(\"\\t%s: %r\" % (param_name, best_parameters[param_name]))\n \n print(\"Test score with best_estimator_: %0.3f\" % grid_search.best_estimator_.score(X_test, y_test))\n print(\"\\n\")\n print(\"Classification Report Test Data\")\n print(classification_report(y_test, grid_search.best_estimator_.predict(X_test)))\n \n return grid_search","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Parameter grid settings for the vectorizers (Count and TFIDF)\nparameters_vect = {\n 'features__pipe__vect__max_df': (0.25, 0.5, 0.75),\n 'features__pipe__vect__ngram_range': ((1, 1), (1, 2)),\n 'features__pipe__vect__min_df': (1,2)\n}\n# Parameter grid settings for MultinomialNB\nparameters_mnb = {\n 'clf__alpha': (0.25, 0.5, 0.75)\n}\n# Parameter grid settings for LogisticRegression\nparameters_logreg = {\n 'clf__C': (0.25, 0.5, 1.0),\n 'clf__penalty': ('l1', 'l2')\n}","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**1.4 Classifying the model - CountVectorizer**"},{"metadata":{"trusted":true},"cell_type":"code","source":"# Classifiers\nmnb = MultinomialNB()\nlogreg = LogisticRegression()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Converting text into numbers using CountVectorizer"},{"metadata":{"trusted":true},"cell_type":"code","source":"countvect = CountVectorizer()\n# MultinomialNB\nbest_mnb_countvect = grid_vect(mnb, parameters_mnb, X_train, X_test, parameters_text=parameters_vect, vect=countvect)\n# LogisticRegression\nbest_logreg_countvect = grid_vect(logreg, parameters_logreg, X_train, X_test, parameters_text=parameters_vect, vect=countvect)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The Logistic Regression classifier and the Multinomial Naive Bayes classifier achieve good results when using CountVectorizer"},{"metadata":{},"cell_type":"markdown","source":"**1.5 Deploying the model**"},{"metadata":{},"cell_type":"markdown","source":"Let's detect from responses of cities whether whether a city sees fitire climate scenarios an opportunity or concern"},{"metadata":{"trusted":true},"cell_type":"code","source":"textcountscols = ['count_capital_words','count_emojis','count_excl_quest_marks','count_hashtags'\n,'count_mentions','count_urls','count_words']\nfeatures = FeatureUnion([('textcounts', ColumnExtractor(cols=textcountscols))\n, ('pipe', Pipeline([('cleantext', ColumnExtractor(cols='clean_text'))\n, ('vect', CountVectorizer(max_df=0.5, min_df=1, ngram_range=(1,2)))]))]\n, n_jobs=-1)\npipeline = Pipeline([\n('features', features)\n, ('clf', LogisticRegression(C=1.0, penalty='l2'))\n])\n\nbest_model = pipeline.fit(df_model.drop('Opportunity_Concern', axis=1), df_model.Opportunity_Concern)\n\n# Applying on new positive tweets\nnew_positive_tweets = pd.Series(['The City of Pittsburgh has signed a memorandum of understanding with the Department of Energy to improve distributed district energy systems that will improve efficiency and resilience with microgrids and CHP systems and renewable energy'])\n\ndf_counts_pos = tc.transform(new_positive_tweets)\ndf_clean_pos = ct.transform(new_positive_tweets)\ndf_model_pos = df_counts_pos\ndf_model_pos['clean_text'] = df_clean_pos\n\nbest_model.predict(df_model_pos).tolist()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Applying on new negative tweets\nnew_negative_tweets = pd.Series([\"Other, please specify\"])\n\ndf_counts_neg = tc.transform(new_negative_tweets)\ndf_clean_neg = ct.transform(new_negative_tweets)\ndf_model_neg = df_counts_neg\ndf_model_neg['clean_text'] = df_clean_neg\nbest_model.predict(df_model_neg).tolist()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# **2. Text Summarizing**"},{"metadata":{},"cell_type":"markdown","source":"In this part, we attempt to summarise city 'readiness' for climate change and the hazards they anticipate (Cities Question 2.1)"},{"metadata":{"trusted":true},"cell_type":"code","source":"review = cr[(cr[\"Question Number\"] == '2.1')]\n\nreview = review.loc[(review['Country'] == 'United States of America') | (review['Country'] == 'Canada')\n | (review['Country'] == 'United Kingdom of Great Britain and Northern Ireland')]\n\nreview = review.loc[:, ['City', 'Response Answer']]\n\nreview ['Response Answer'] = review ['Response Answer'].fillna('No Response')\nreview ['comment'] = review ['Response Answer']\nreview = review .drop(['Response Answer'], axis =1)\n\n\npre = review.reset_index(drop=True)\npre.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**2.1 Text cleaning**"},{"metadata":{"trusted":true},"cell_type":"code","source":"!python -m spacy download en_core_web_lg\nnlp = spacy.load('en_core_web_lg')\ndef normalize_text(text):\n tm1 = re.sub('.*?
', '', text, flags=re.DOTALL)\n tm2 = re.sub('.*?', '', tm1, flags=re.DOTALL)\n tm3 = re.sub('<[^>]+>', '', tm1, flags=re.DOTALL)\n return tm3.replace(\"\\n\", \"\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# in this step we are going to remove code syntax from text \npre['Body_Cleaned_1'] = pre['comment'].apply(normalize_text)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Clean text before feeding it to spaCy\npunctuations = '!\"#$%&\\'()*+,-/:;<=>?@[\\\\]^_`{|}~'\n# Define function to cleanup text by removing personal pronouns, stopwords, and puncuation\ndef cleanup_text(docs, logging=False):\n texts = []\n doc = nlp(docs, disable=['parser', 'ner'])\n tokens = [tok.lemma_.lower().strip() for tok in doc if tok.lemma_ != '-PRON-']\n tokens = [tok for tok in tokens if tok not in stopwords and tok not in punctuations]\n tokens = ' '.join(tokens)\n texts.append(tokens)\n return pd.Series(texts)\n\n\npre['Body_Cleaned'] = pre['Body_Cleaned_1'].apply(lambda x: cleanup_text(x, False))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print('City answer with punctuatin and stopwords\\n')\nprint(pre['Body_Cleaned_1'][44258])\nprint('\\nCity answer after removing punctuation and stopwrods\\n')\nprint(pre['Body_Cleaned'][44258])","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**2.2 Text summarizing**"},{"metadata":{},"cell_type":"markdown","source":"We first need to convert the whole all paragraphs into sentences. The most common way of converting paragraphs to sentences is to split the paragraph whenever a period is encountered. Second, We remove all the special characters, stop words and numbers from all the sentences. Third, We tokenize all the sentences to get all the words that exist in the sentences. Fourth, We find the weighted frequency of occurrences of all the words by dividing its frequency by the frequency of the most occurring word. Finally, plug the weighted frequency in place of the corresponding words in original sentences and finding their sum, then sort the sentences in inverse order of their sum. The sentences with highest frequencies summarize the text."},{"metadata":{"trusted":true},"cell_type":"code","source":"# this is function for text summarization\n\ndef generate_summary(text_without_removing_dot, cleaned_text):\n sample_text = text_without_removing_dot\n doc = nlp(sample_text)\n sentence_list=[]\n for idx, sentence in enumerate(doc.sents): # we are using spacy for sentence tokenization\n sentence_list.append(re.sub(r'[^\\w\\s]','',str(sentence)))\n\n stopwords = nltk.corpus.stopwords.words('english')\n\n word_frequencies = {} \n for word in nltk.word_tokenize(cleaned_text): \n if word not in stopwords:\n if word not in word_frequencies.keys():\n word_frequencies[word] = 1\n else:\n word_frequencies[word] += 1\n\n\n maximum_frequncy = max(word_frequencies.values())\n\n for word in word_frequencies.keys(): \n word_frequencies[word] = (word_frequencies[word]/maximum_frequncy)\n\n\n sentence_scores = {} \n for sent in sentence_list: \n for word in nltk.word_tokenize(sent.lower()):\n if word in word_frequencies.keys():\n if len(sent.split(' ')) < 30:\n if sent not in sentence_scores.keys():\n sentence_scores[sent] = word_frequencies[word]\n else:\n sentence_scores[sent] += word_frequencies[word]\n\n\n summary_sentences = heapq.nlargest(7, sentence_scores, key=sentence_scores.get)\n\n summary = ' '.join(summary_sentences)\n print(\"Original Text::::::::::::\\n\")\n print(text_without_removing_dot)\n print('\\n\\nSummarized text::::::::\\n')\n print(summary)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"generate_summary(pre['Body_Cleaned_1'][44258], pre['Body_Cleaned'][44258])","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**Corporations Disclosing**"},{"metadata":{"trusted":true},"cell_type":"code","source":"crd.groupby('survey_year').size()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plt.figure(figsize=(12, 5))\n\nfreq = len(crd)\n\nsns.set_palette(\"Paired\")\n\ng = sns.countplot(crd['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":{"trusted":true},"cell_type":"code","source":"crd.groupby('country').size()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plt.figure(figsize=(12, 5))\n\nfreq = len(crd)\n\nsns.set_palette(\"pastel\")\n\ng = sns.countplot(crd['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":{"trusted":true},"cell_type":"code","source":"# Let's see how the numeric data is distributed.\nimport matplotlib.pyplot as plt\nplt.style.use('seaborn-whitegrid')\n\ncr.hist(bins=20, figsize=(14,10), color='#BC5090')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**Emission**"},{"metadata":{},"cell_type":"markdown","source":"Insight\n\n As expected, most of the reported emissions come from 4 industries: \n Fossil Fuels, Infrastructure, Power Generation and Transportation. One interesting \n observation is Materials."}],"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}