{"cells":[{"metadata":{},"cell_type":"markdown","source":"# CDP: Unlocking Climate Solutions\n\n####  By Gilad Shtern 27-Nov-2020"},{"metadata":{},"cell_type":"markdown","source":"### Introduction\nCDP is a global non-profit that drives companies and governments to reduce their greenhouse gas emissions, safeguard water resources, and protect forests. Each year, CDP takes the information supplied in its annual reporting process and scores companies and cities based on their journey through disclosure and towards environmental leadership.\n\nCDP houses the world’s largest, most comprehensive dataset on environmental action. As the data grows to include thousands more companies and cities each year, there is increasing potential for the data to be utilized in impactful ways. Because of this potential, CDP is excited to launch an analytics challenge for the Kaggle community. Data scientists will scour environmental information provided to CDP by disclosing companies and cities, searching for solutions to our most pressing problems related to climate change, water security, deforestation, and social inequity.\n\nHow 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\nWhat 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\nWhat 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\nHow can we measure the intersection between environmental risks and social equity, as a contributor to resiliency?\n\n### PROBLEM STATEMENT\nDevelop 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.\n\n"},{"metadata":{},"cell_type":"markdown","source":"## Part1 - Data Exploration for Cities Responses Questionnaires"},{"metadata":{"trusted":true},"cell_type":"code","source":"#Step1 - Aggregate csv by csv subject\nimport pandas as pd\nimport os\nimport nbconvert\nimport seaborn as sns\nimport warnings;\nwarnings.filterwarnings('ignore');\n\npath = '/kaggle/working/'\nfolderList = ['../input/cdp-unlocking-climate-solutions/Cities/Cities Responses',\n'../input/cdp-unlocking-climate-solutions/Corporations/Corporations Responses/Climate Change',\n'../input/cdp-unlocking-climate-solutions/Corporations/Corporations Responses/Water Security']\nfor i, valueA in enumerate(folderList):\n    filesList = os.listdir(valueA)[0:3]\n    for j, valueB in enumerate(filesList):\n        df = pd.read_csv(valueA + \"/\" + valueB)\n        if j == 0:\n            df0 = df\n        else:\n            #df0 = pd.concat([df0,df], axis=1)\n            df0 = df0.append(df)\n\n    if len(valueA.split(\"/\")) == 5:\n        newFilePath = path + valueA.split(\"/\")[4] + \".csv\"\n    else:\n        newFilePath = path + valueA.split(\"/\")[4] + \" \" + valueA.split(\"/\")[5].split(\" \")[0] + \".csv\"\n    df0.to_csv(newFilePath , index=False, sep=',', header=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#Step2 - Load cities aggregate responses\nCities_Responses_df = pd.read_csv('/kaggle/working/Cities Responses.csv')\nCities_Responses_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#Step3 - Check col types, NA, unique.\ndf = pd.DataFrame(columns = ['Col', 'Type', 'NA', '%NA', 'UniqLen']) \ncolList = list(Cities_Responses_df)\n\nfor i, value in enumerate(colList):\n    df.loc[i] = [value, Cities_Responses_df.dtypes[i], Cities_Responses_df[value].isna().sum(),  Cities_Responses_df[value].isna().sum()/len(Cities_Responses_df), len(Cities_Responses_df[value].unique())]\n\ndf","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#Step4 - Handle column\n#Convert Last Update into year & month\nCities_Responses_df['ResYear'] = int(Cities_Responses_df['Last update'][1].split(\"/\")[2].split(\" \")[0])\nCities_Responses_df['ResMonth'] = int(Cities_Responses_df['Last update'][1].split(\"/\")[1])\n\n#Drop NA cols\nCities_Responses_df = Cities_Responses_df.drop(['Questionnaire', 'Parent Section', 'Comments', 'File Name', 'Column Name','Row Name', 'Last update'], axis=1)\n#Drop specific row in which Response Answer col is NaN\nCities_Responses_df = Cities_Responses_df.dropna(subset = ['Response Answer'])\n\n#Convert col type:int64 -> int 32\ncolList = list(Cities_Responses_df)\nfor i, value in enumerate(colList):\n    if Cities_Responses_df.dtypes[i] == 'int64' or Cities_Responses_df.dtypes[i] == 'float64':\n        Cities_Responses_df[value] = Cities_Responses_df[value].astype('int32')\n\nCities_Responses_df.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Part2 - Analyze cities questionnaires to find KPIs questions "},{"metadata":{},"cell_type":"markdown","source":"In this part, I will try to analyze cities responses questionnaires to find which question are most likely to be related with climate issues by using NLP. at the end of this part, we can assume to keep with clean dataset."},{"metadata":{"trusted":true},"cell_type":"code","source":"#Step5 - Question NLP check\n#Create empty DF: Question Number, Question Name, Question Keywords, Climate Keywords\ncities_keywords_df = pd.DataFrame(columns=['Question Number', 'Question Name', 'Question Keywords', 'Climate Keywords'])\n\n#Keep unique questions for NLP check\ncities_keywords_df['Question Number'] = Cities_Responses_df['Question Number']\ncities_keywords_df['Question Name'] = Cities_Responses_df['Question Name']\ncities_keywords_df['Question Keywords'] = ''\ncities_keywords_df['Climate Keywords'] = ''\n\ncities_keywords_df = cities_keywords_df.drop_duplicates(subset=['Question Number', 'Question Name'], keep=\"first\")\ncities_keywords_df.head()\n\n#NLP\nfrom nltk.tokenize import sent_tokenize\nimport nltk\nen_stop = set(nltk.corpus.stopwords.words('english'))\nfrom nltk.stem import WordNetLemmatizer\nimport re\n\nstemmer = WordNetLemmatizer()\n#Preprocess func\ndef preprocess_text(document):\n    # Remove all the special characters\n    document = re.sub(r'\\W', ' ', str(document))\n    # remove all single characters\n    document = re.sub(r'\\s+[a-zA-Z]\\s+', ' ', document)\n    # Remove single characters from the start\n    document = re.sub(r'\\^[a-zA-Z]\\s+', ' ', document)\n    # Substituting multiple spaces with single space\n    document = re.sub(r'\\s+', ' ', document, flags=re.I)\n    # Removing prefixed 'b'\n    document = re.sub(r'^b\\s+', '', document)\n    # Converting to Lowercase\n    document = document.lower()\n    # Lemmatization\n    tokens = document.split()\n    tokens = [stemmer.lemmatize(word) for word in tokens]\n    tokens = [word for word in tokens if word not in en_stop]\n    tokens = [word for word in tokens if len(word) > 3]\n    preprocessed_text = ' '.join(tokens)\n    return preprocessed_text\n\nfor i, value in enumerate(cities_keywords_df['Question Name']):\n    text = sent_tokenize(str(value))\n    cities_keywords_df['Question Keywords'].iloc[i] = preprocess_text(text)\n    \ncities_keywords_df.head()\n\n#Create list of climate/pollusion dictionary\nclimate_change_keywords = ['carbon', 'dioxide', 'greenhouse', 'ghg', 'co2', 'emissions', 'extreme weather', 'global warming', 'climate change', 'fossil fuels','sea-level rise', 'global average temperature', 'renewable energy', 'methane', 'atmosphere', 'melt', 'waste', 'smoke', 'smog', 'sulfur', 'nitrates', 'harmful', 'hazard', 'fire', 'damage', 'acid']\n\n#Check if col keyword contains climate/Greenhouse  keywords\nfor i in range(0, len(cities_keywords_df)):\n    question_keywords = list(cities_keywords_df['Question Keywords'].iloc[i].split(\" \"))\n    counter = 0\n    for j, value in enumerate(question_keywords):\n        try: \n            climate_change_keywords.index(value)\n            counter += 1\n            cities_keywords_df['Climate Keywords'].iloc[i] = counter\n            \n        except:\n            {}\n\ncities_keywords_df.to_csv('/kaggle/working/cities_keywords_df.csv', index=False)\ncities_keywords_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#Step6 - find Cities KPI impact questions\ncities_keywords_df['Climate Keywords'] = pd.to_numeric(cities_keywords_df['Climate Keywords'])\ncities_keywords_df['Climate Keywords'].hist()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Upon the above analysis, we can distinguish between question that contain less than at least 2 climate/greenhouse keywords with those that have more than 2 climate greenhouse keywords.\nHence, we should remove all non significant questions from the cities aggregate responses."},{"metadata":{"trusted":true},"cell_type":"code","source":"#Step7 - Clean Cities Responses dataset\n#Keep question numbers list for remove\nremove_question_df = cities_keywords_df[cities_keywords_df['Climate Keywords'] <2]\nremove_question_list = list(remove_question_df['Question Number'])\n\nfor i, value in enumerate(remove_question_list):\n    Cities_Responses_df = Cities_Responses_df[Cities_Responses_df['Question Number'] != value]\n    \nCities_Responses_df.to_csv('/kaggle/working/cities_responses_clean_df.csv', index=False)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Part3 - Analyze climate impact keywords in matrix answers\n\nNow, that we left with clean dataset in which contains climate KPI questions. We need to allocate climate impact answers.\nHowever, our answers are build in a non formal matrix way. In regular rectangle matrix, our celss would be N*M. But on this questionnaires asnwers could be found with a diferent row number per a column; col A may have 3 rows, while col D may have 5 rows.\nLet raise up our assumptions:\n-  Assumption1: Most cities answer won't have any climate keywords.\n-  Assumption2: By, contrast, sensitive climate cities will have climate keywords.\n\nOnce again, I will use NLP analysis."},{"metadata":{"trusted":true},"cell_type":"code","source":"#Step8 - Clean answers for calc answer matrix\nCities_Responses_df = pd.read_csv('/kaggle/working/cities_responses_clean_df.csv')\n\n#Add col clean text\nCities_Responses_df['Answer Keywords'] = ''\n\n#Insert clean text into col\nfor i in range(0, len(Cities_Responses_df)):\n    text = sent_tokenize(Cities_Responses_df['Response Answer'].iloc[i])\n    Cities_Responses_df['Answer Keywords'].iloc[i] = preprocess_text(text)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#Step9 - Calc most climate impact answer cells\n#Create answer keywords col\nCities_Responses_df['Climate Keywords'] = ''\n\n#Create list of climate/pollusion keywords\nclimate_change_keywords = ['carbon', 'dioxide', 'greenhouse', 'ghg', 'co2', 'emissions', 'extreme weather', 'global warming', 'climate change', 'fossil fuels','sea-level rise', 'global average temperature', 'renewable energy', 'methane', 'atmosphere', 'melt', 'waste', 'smoke', 'smog', 'sulfur', 'nitrates', 'harmful', 'hazard', 'fire', 'damage', 'acid']\n\n#Check if col keyword contains climate/pollusion/Greenhouse\nfor i in range(0, len(Cities_Responses_df)):\n    question_keywords = list(str(Cities_Responses_df['Answer Keywords'].iloc[i]).split(\" \"))\n    counter = 0\n    for j, value in enumerate(question_keywords):\n        try:\n            climate_change_keywords.index(value)\n            counter += 1\n        except:\n            {}\n    Cities_Responses_df['Climate Keywords'].iloc[i] = counter\n\nCities_Responses_df = Cities_Responses_df.drop(['Question Name', 'Response Answer', 'Answer Keywords'], axis=1)\nCities_Responses_df['FullAnswerID'] = Cities_Responses_df['Question Number'] + \".\" + Cities_Responses_df['Column Number'].map(str) + \".\" + Cities_Responses_df['Row Number'].map(str)\nCities_Responses_df.to_csv('/kaggle/working/cities_responses_clean_df1.csv', index=False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#Step10 - Display year trend by region as factor climate keywords\nCities_Responses_clean_df = pd.read_csv('/kaggle/working/cities_responses_clean_df1.csv')\nCities_Responses_orig_df = pd.read_csv('/kaggle/working/Cities Responses.csv')\nRegionClimateByYearDF = pd.DataFrame(columns=['CDP Region', 'Year', 'Climate Ratio'])\nCDPRegionList = set(Cities_Responses_clean_df['CDP Region'])\nYearList = [2018, 2019, 2020]\nk = 0\nfor i, valueA in enumerate(CDPRegionList):\n    for j, valueB in enumerate(YearList):\n        df0 = Cities_Responses_orig_df[Cities_Responses_orig_df['CDP Region'] == valueA]\n        df1 = Cities_Responses_clean_df[Cities_Responses_clean_df['CDP Region'] == valueA]\n        df0 = df0[df0['Year Reported to CDP'] == valueB]\n        df1 = df1[df1['Year Reported to CDP'] == valueB]\n        RegionClimateByYearDF.loc[k] = [valueA, valueB, int(df1['Climate Keywords'].sum()/len(df0) * 100)]\n        k += 1\n\nsns.catplot(x=\"Year\", y=\"Climate Ratio\", hue=\"CDP Region\", kind=\"bar\", data=RegionClimateByYearDF)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"In the above graph, we see climate ratio trend by CDP regions & years. As example, Southeast Asis & Oceania climate keyword ration was reduced fromm 8% to 3% between 2018 to 2019. Also, North America reduced from 4% to 1%."},{"metadata":{},"cell_type":"markdown","source":"### Part4 - Build A Model\n\nAt this point, we can build an classification problem ML model. As prepare, dataset should be convert into numeric. Then, we will compare between models & choose one."},{"metadata":{"trusted":true},"cell_type":"code","source":"#Step11 - Dictionary\ndic = {}\nobjList = []\ncolList = list(Cities_Responses_clean_df)\n#Create list of all object col\nfor i, value in enumerate(colList):\n    #Per each object col, find unique values and add into list.\n    if Cities_Responses_clean_df[value].dtype == 'object':\n        objList += list(Cities_Responses_clean_df[value].unique())\n\n#Remove duplicate from list and 'nan'\nobjList = list(set(objList))\n\n#Build dic with values\nfor i, value in enumerate(objList):\n    dic[value] = (i + 3) * 4 - 1\n    #Go over dic and replace strings into numeric\n    Cities_Responses_clean_df= Cities_Responses_clean_df.replace(value, dic[value])\n\nfor i in range(0, len(Cities_Responses_clean_df)):\n    if Cities_Responses_clean_df['Climate Keywords'].iloc[i] > 0:\n        Cities_Responses_clean_df['Climate Keywords'].iloc[i] = 1\n\n#Save dic\nimport pickle\nfile_to_write = open(\"/kaggle/working/dic.pickle\", \"wb\")\npickle.dump(dic, file_to_write)\n#Save df\nCities_Responses_clean_df.to_csv('/kaggle/working/cities_convert_numeric.csv', index=False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#Step12 - ML PycCaret\nfrom pycaret.classification import *\n\nexp1 = setup(Cities_Responses_clean_df, target = 'Climate Keywords')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#Step13 - Model comparision\ncompare_models()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#Step14 -  train logistic regression model\nlgbms = create_model('lightgbm')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"save_experiment(experiment_name = '/kaggle/working/Exp1')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# summary plot\ninterpret_model(lgbms)\n# correlation plot\ninterpret_model(lgbms, plot = 'summary')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Conclussion\n\nTo summarize the answer for develop a methode for calculate KPIs that relate to the environmental and social issues that are discussed in the CDP survey data, we van suggest the followed:\n- Find KPI question from the qyestionnaires by use of NLP.\n- Use Light Gradient Boosting Machine model analyze the answers. Main KPI's: row number, section & question number.\n\nEnjoy,\nGilad"}],"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}