{"cells":[{"metadata":{},"cell_type":"markdown","source":"# KPI - Estimating Level of City Effort - The \"Yes Score\"\n\nThe observation that most of the success in complex efforts results from a relatively small portion of the overall effort is captured by the Pareto Priniciple. This principle is based on the often occurring phenomenon that 80% of the improvement comes from 20% of the effort. Along those lines this data investigation attempts to determine a city's level of effort by assigning a \"Positive Yes Score\" to each city as a measure of whether they are even trying.\n\nThe Yes score is based on each city's positive yes responses for their questionaire with diminishing factor for returns on each successive yes. Since the questionnaires have a \"branching\" style set of questions, the most important part of the response is whether or not the city is even trying. The main question is the primary differentiator. An example of a branching question is when a 'yes' for 1.0 would then ask the respondant for more details on the response in 1.1, 1.2, and so on. Conversely a 'no' response would result in the questionnaire ending that line of questions or possibly branching to ask a different set of questions altogether.\n\n**I kept the order of the survey questions in this investigation, with an assumption that the more important questions are posed at the beginning of each section in the survey.**\n\nThe first two questions as an example case for 2020 are: \n1.0 Does your city incorporate sustainability goals and targets (e.g. GHG reductions) into the master planning for the city?\n2.0 Has a climate change risk or vulnerability assessment been undertaken for your city?\n\n**Datasets Used:**\n1) CDP Questionnaire Responses\n2) CDP Complemenatary Datasets - GHG Emissions\n3) Gap Minder - Gini Index for years 2018-2020\n"},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"import os\nimport math\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\nimport matplotlib.pyplot as plt\nimport seaborn as sns\n\n# set seed for reproducibility\nnp.random.seed(42) ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Functions for creating easier to use column names\ndef std_col_ref(text_series):\n    ''' Standardizes column values for matching column names dynamically\n    '''\n    new_text = text_series.str.strip()\n    new_text = new_text.str.replace('\\s+','_')\n    new_text = new_text.str.replace('[-]+', '_')\n    new_text = new_text.str.replace('#', 'num')\n    new_text = new_text.str.replace('(', '',regex=False)\n    new_text = new_text.str.replace(')', '',regex=False)\n    new_text = new_text.str.replace('[_]+', '_')\n    new_text = new_text.str.replace('/', '_', regex=False)\n    new_text = new_text.str.replace('\\\\', '_', regex=False)\n    new_text = new_text.str.replace('[!@#$%^&*]+','')\n    new_text = new_text.str.lower()\n    return new_text\n\n\ndef standardize_columns(df):\n    ''' Standardizes column names and references\n    '''\n    the_columns = df.columns\n    the_columns = std_col_ref(the_columns)\n    df.columns = the_columns\n    df = df.dropna(axis=1, how='all')\n    return df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Read in the disclosing data and format the dates\ncities_disc_2020_data = standardize_columns(pd.read_csv(\"/kaggle/input/cdp-unlocking-climate-solutions/Cities/Cities Disclosing/2020_Cities_Disclosing_to_CDP.csv\"))\ncities_disc_2020_data['account_number'] = cities_disc_2020_data.account_number.astype(np.object)\ncities_disc_2020_data['last_update'] = pd.to_datetime(cities_disc_2020_data['last_update'], format='%Y-%m-%dT%H:%M:%S.%f')\n\ncities_disc_2019_data = standardize_columns(pd.read_csv(\"/kaggle/input/cdp-unlocking-climate-solutions/Cities/Cities Disclosing/2019_Cities_Disclosing_to_CDP.csv\"))\ncities_disc_2019_data['account_number'] = cities_disc_2019_data.account_number.astype(np.object)\ncities_disc_2019_data['last_update'] = pd.to_datetime(cities_disc_2019_data['last_update'], format='%Y-%m-%dT%H:%M:%S.%f')\n\ncities_disc_2018_data = standardize_columns(pd.read_csv(\"/kaggle/input/cdp-unlocking-climate-solutions/Cities/Cities Disclosing/2018_Cities_Disclosing_to_CDP.csv\"))\ncities_disc_2018_data['account_number'] = cities_disc_2018_data.account_number.astype(np.object)\ncities_disc_2018_data['last_update'] = pd.to_datetime(cities_disc_2018_data['last_update'], format='%Y-%m-%dT%H:%M:%S.%f')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true},"cell_type":"code","source":"# Read in the response data\ncities_resp_2020_data = standardize_columns(pd.read_csv(\"/kaggle/input/cdp-unlocking-climate-solutions/Cities/Cities Responses/2020_Full_Cities_Dataset.csv\"))\ncities_resp_2019_data = standardize_columns(pd.read_csv(\"/kaggle/input/cdp-unlocking-climate-solutions/Cities/Cities Responses/2019_Full_Cities_Dataset.csv\"))\ncities_resp_2018_data = standardize_columns(pd.read_csv(\"/kaggle/input/cdp-unlocking-climate-solutions/Cities/Cities Responses/2018_Full_Cities_Dataset.csv\"))\n\n# Drop rows that don't have any response answer\ncities_resp_2020_data.dropna(subset=['response_answer'], inplace=True)\ncities_resp_2019_data.dropna(subset=['response_answer'], inplace=True)\ncities_resp_2018_data.dropna(subset=['response_answer'], inplace=True)\n\n# Turn the question numbers in to numbers, nullifying any subquestions by \"coercing\" items such as 2.1a to NaN\ncities_resp_2018_data['question_number'] = pd.to_numeric(cities_resp_2018_data['question_number'], errors='coerce')\ncities_resp_2019_data['question_number'] = pd.to_numeric(cities_resp_2019_data['question_number'], errors='coerce')\ncities_resp_2020_data['question_number'] = pd.to_numeric(cities_resp_2020_data['question_number'], errors='coerce')\n\ncities_resp_2020_data['question_number'].value_counts()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**This is where I make a big assumption. The assumption is that the most important questions are posed early in each section.**\nIn that regard, I put the first question for each section at the front of the list. I also referenced the \"CDP Recommendations for Questions to Focus On\" Excel worksheet to aid the selection of questions."},{"metadata":{"trusted":true},"cell_type":"code","source":"# Yes/No Response Questions for each year, first question of each section, with secondary questions in order\nlist_of_yes_no_questions_2018 = [1.4,2.0,3.0,4.0,5.0,6.0,7.0,8.0,9.0,2.1,2.2,3.1,3.2,5.1,6.8,6.10,7.3,7.9,7.10,7.12,8.2,9.3,15.3]\nlist_of_yes_no_questions_2019 = [1.0,2.0,3.1,4.0,5.0,6.1,7.0,8.0,1.1,1.6,1.15,4.9,4.11,5.5,6.3,6.4,6.6,6.9,7.7,7.9,8.1,8.6,9.1,9.2,9.3,10.7,10.11,12.4,12.5,13.6,14.3,14.5]\nlist_of_yes_no_questions_2020 = [1.0,2.0,3.1,4.0,5.0,6.2,8.0,2.3,3.2,4.9,4.12,5.2,5.3,5.5,8.5,10.7,12.3,14.2,14.4]\n\nquestion_filter_2018 = cities_resp_2018_data['question_number'].isin(list_of_yes_no_questions_2018)\nquestion_filter_2019 = cities_resp_2019_data['question_number'].isin(list_of_yes_no_questions_2019)\nquestion_filter_2020 = cities_resp_2020_data['question_number'].isin(list_of_yes_no_questions_2020)\n\n# List of \"Positive\" answers \npositive_answer_regex = ['Yes','In progress','Base year','Fixed level']\n\n# Filter reponse answers based on \"postive\" responses\nanswer_filter_2018 = (cities_resp_2018_data['response_answer'].isin(positive_answer_regex)) | (cities_resp_2018_data['response_answer'].str.startswith('Base year'))  | (cities_resp_2018_data['response_answer'].str.startswith('Fixed level'))\nanswer_filter_2019 = (cities_resp_2019_data['response_answer'].isin(positive_answer_regex)) | (cities_resp_2019_data['response_answer'].str.startswith('Base year'))  | (cities_resp_2019_data['response_answer'].str.startswith('Fixed level'))\nanswer_filter_2020 = (cities_resp_2020_data['response_answer'].isin(positive_answer_regex)) | (cities_resp_2020_data['response_answer'].str.startswith('Base year'))  | (cities_resp_2020_data['response_answer'].str.startswith('Fixed level'))\n\n# Filter out the \"yes\" data for scoring\ncities_resp_2018_yes_data = cities_resp_2018_data.loc[question_filter_2018 & answer_filter_2018,:]\ncities_resp_2019_yes_data = cities_resp_2019_data.loc[question_filter_2019 & answer_filter_2019,:]\ncities_resp_2020_yes_data = cities_resp_2020_data.loc[question_filter_2020 & answer_filter_2020,:]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Assign \"weight\" based on the Pareto Principle (First 20% of questions are worth 80%)\np80 = .8  # Pareto 80% effective weight\np20 = .2 # Pareto 20% effective weight\n\n# Set ceiling index values which I use to select the first ~20% of the yes/no questions\nceil_2018 = math.ceil(len(list_of_yes_no_questions_2018) * .2)\nceil_2019 = math.ceil(len(list_of_yes_no_questions_2019) * .2)\nceil_2020 = math.ceil(len(list_of_yes_no_questions_2020) * .2)\n\n# Select the questions which will be assigned 80% of the weighted value\neighty_percent_2018 = list_of_yes_no_questions_2018[0:ceil_2018]\neighty_percent_2019 = list_of_yes_no_questions_2019[0:ceil_2019]\neighty_percent_2020 = list_of_yes_no_questions_2020[0:ceil_2020]\n\n# Select the questions which will be assigned 20% of the weighted value\ntwenty_percent_2018 = list_of_yes_no_questions_2018[ceil_2018:]\ntwenty_percent_2019 = list_of_yes_no_questions_2019[ceil_2019:]\ntwenty_percent_2020 = list_of_yes_no_questions_2020[ceil_2020:]\n\n# Filters for question weight assignments\neighty_percent_2018_filter = (cities_resp_2018_yes_data['question_number'].isin(eighty_percent_2018))\neighty_percent_2019_filter = (cities_resp_2019_yes_data['question_number'].isin(eighty_percent_2019))\neighty_percent_2020_filter = (cities_resp_2020_yes_data['question_number'].isin(eighty_percent_2020))\n\ntwenty_percent_2018_filter = (cities_resp_2018_yes_data['question_number'].isin(twenty_percent_2018))\ntwenty_percent_2019_filter = (cities_resp_2019_yes_data['question_number'].isin(twenty_percent_2019))\ntwenty_percent_2020_filter = (cities_resp_2020_yes_data['question_number'].isin(twenty_percent_2020))\n\n# Weight assignments for each question.\ncities_resp_2018_yes_data.loc[eighty_percent_2018_filter, 'question_weight'] = p80 / len(eighty_percent_2018)\ncities_resp_2019_yes_data.loc[eighty_percent_2019_filter, 'question_weight'] = p80 / len(eighty_percent_2019)\ncities_resp_2020_yes_data.loc[eighty_percent_2020_filter, 'question_weight'] = p80 / len(eighty_percent_2020)\n\ncities_resp_2018_yes_data.loc[twenty_percent_2018_filter, 'question_weight'] = p20 / len(twenty_percent_2018)\ncities_resp_2019_yes_data.loc[twenty_percent_2019_filter, 'question_weight'] = p20 / len(twenty_percent_2019)\ncities_resp_2020_yes_data.loc[twenty_percent_2020_filter, 'question_weight'] = p20 / len(twenty_percent_2020)\n\n# Double check that every question got weighted.\nif any(cities_resp_2018_yes_data.question_weight.isna()):\n    print('WARNING: Some questions did not receive a weight value')\n    \nif any(cities_resp_2019_yes_data.question_weight.isna()):\n    print('WARNING: Some questions did not receive a weight value')\n    \nif any(cities_resp_2020_yes_data.question_weight.isna()):\n    print('WARNING: Some questions did not receive a weight value')\n    \nprint(cities_resp_2018_yes_data.question_weight.describe())\nprint(cities_resp_2019_yes_data.question_weight.describe())\nprint(cities_resp_2020_yes_data.question_weight.describe())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Aggregate scores for each city\ncity_scores_2018 = cities_resp_2018_yes_data.groupby(['account_number']).question_weight.sum('question_weight')\ncity_scores_2019 = cities_resp_2019_yes_data.groupby(['account_number']).question_weight.sum('question_weight')\ncity_scores_2020 = cities_resp_2020_yes_data.groupby(['account_number']).question_weight.sum('question_weight')\n\nprint(city_scores_2018.describe())\nprint(city_scores_2019.describe())\nprint(city_scores_2020.describe())","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Top and Bottom Cities by Yes Scores for 2020"},{"metadata":{"trusted":true},"cell_type":"code","source":"# Bring in data from the Cities Disclosing dataset\nall_city_data_2018 = city_scores_2018.reset_index().merge(cities_disc_2018_data, how='inner', on='account_number')\nall_city_data_2019 = city_scores_2019.reset_index().merge(cities_disc_2019_data, how='inner', on='account_number')\nall_city_data_2020 = city_scores_2020.reset_index().merge(cities_disc_2020_data, how='inner', on='account_number')\n\ncities_2020 = all_city_data_2020.sort_values('question_weight')[['organization','question_weight']]\nprint(f'5 Cities with worst Yes Scores in 2020:\\n{cities_2020.head()}')\nprint('-' * 25)\nprint(f'5 Cities with best Yes Scores in 2020:\\n{cities_2020.tail()}')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"all_city_scores = all_city_data_2018.append(all_city_data_2019, ignore_index=True).append(all_city_data_2020, ignore_index=True)\nall_city_scores.rename(columns={'question_weight':'yes_score'}).to_csv('all_city_yes_scores.csv')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Calculate the average scores across country and region"},{"metadata":{"trusted":true},"cell_type":"code","source":"# Generate average score for the country\ncountry_means_2018 = all_city_data_2018.groupby('country').question_weight.mean().rename('country_avg_score').reset_index()\ncountry_means_2019 = all_city_data_2019.groupby('country').question_weight.mean().rename('country_avg_score').reset_index()\ncountry_means_2020 = all_city_data_2020.groupby('country').question_weight.mean().rename('country_avg_score').reset_index()\ncountry_means =  country_means_2018.rename(columns={'country_avg_score':'2018_country_avg_score'}).merge(country_means_2019.rename(columns={'country_avg_score':'2019_country_avg_score'})).merge(country_means_2020.rename(columns={'country_avg_score':'2020_country_avg_score'}))\n\nall_city_data_2018 = all_city_data_2018.merge(country_means_2018, how='inner', on='country') \nall_city_data_2019 = all_city_data_2019.merge(country_means_2019, how='inner', on='country')\nall_city_data_2020 = all_city_data_2020.merge(country_means_2020, how='inner', on='country')\n\n\n# Generage average cdp_region score\nregion_means_2018 = all_city_data_2018.groupby('cdp_region').question_weight.mean().rename('region_avg_score').reset_index()\nregion_means_2019 = all_city_data_2019.groupby('cdp_region').question_weight.mean().rename('region_avg_score').reset_index()\nregion_means_2020 = all_city_data_2020.groupby('cdp_region').question_weight.mean().rename('region_avg_score').reset_index()\n\nregion_means =  region_means_2018.rename(columns={'region_avg_score':'2018_region_avg_score'}).merge(region_means_2019.rename(columns={'region_avg_score':'2019_region_avg_score'})).merge(region_means_2020.rename(columns={'region_avg_score':'2020_region_avg_score'}))\nregion_means.to_csv('region_yes_score_means.csv')\n\n# Bring region mean to the rest of the data for correlation analysis\nall_city_data_2018 = all_city_data_2018.merge(region_means_2018, how='inner', on='cdp_region') \nall_city_data_2019 = all_city_data_2019.merge(region_means_2019, how='inner', on='cdp_region')\nall_city_data_2020 = all_city_data_2020.merge(region_means_2020, how='inner', on='cdp_region')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#Visualize city scores for 2018\nf, ax = plt.subplots(figsize=(20, 5))\nax = sns.swarmplot(data=all_city_data_2018.sort_values('region_avg_score'), x='cdp_region', y='question_weight')\nax.set(title=f'2018 City Scores by Region',\n       ylabel=\"City Score\",\n       xlabel=\"CDP Region\")\nplt.xticks(rotation=90)\nfor ind, label in enumerate(ax.get_xticklabels()):\n    if ind % 1 == 0:  # every 10th label is kept\n        label.set_visible(True)\n    else:\n        label.set_visible(False)\nplt.show()\n\n#2019\nf, ax = plt.subplots(figsize=(20, 5))\nax = sns.swarmplot(data=all_city_data_2018.sort_values('region_avg_score'), x='cdp_region', y='question_weight')\nax.set(title=f'2019 City Scores by Region',\n       ylabel=\"City Score\",\n       xlabel=\"CDP Region\")\nplt.xticks(rotation=90)\nfor ind, label in enumerate(ax.get_xticklabels()):\n    if ind % 1 == 0:  # every 10th label is kept\n        label.set_visible(True)\n    else:\n        label.set_visible(False)\nplt.show()\n\n#2020\nf, ax = plt.subplots(figsize=(20, 5))\nax = sns.swarmplot(data=all_city_data_2020.sort_values('region_avg_score'), x='cdp_region', y='question_weight')\nax.set(title='2020 City Scores by Region',\n       ylabel=\"City Score\",\n       xlabel=\"CDP Region\")\nplt.xticks(rotation=90)\nfor ind, label in enumerate(ax.get_xticklabels()):\n    if ind % 1 == 0:  # every 10th label is kept\n        label.set_visible(True)\n    else:\n        label.set_visible(False)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# What correlation exists between score and C40 emmisisons?"},{"metadata":{"trusted":true},"cell_type":"code","source":"c40_cities_emmissions = standardize_columns(pd.read_csv(\"/kaggle/input/cdp-complementary-datasets/C40_Cities_Citywide_Emissions.csv\"))\nc40_cities_emmissions.info()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"country_c40_map = {'United States of America':'USA',\n               'United Kingdom of Great Britain and Northern Ireland': 'United Kingdom',\n               'China, Hong Kong Special Administrative Region': 'Hong Kong',\n               'Republic of Korea': 'South Korea'\n              }\ncountry_means['country_mapped'] = country_means.country.map(country_c40_map)\ncountry_means.loc[country_means.country_mapped.isna(), 'country_mapped'] = country_means['country']\ncountry_means_c40 = country_means.merge(c40_cities_emmissions, how='inner', left_on='country_mapped', right_on='country')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Plot the correlations\nf, ax = plt.subplots(figsize=(12, 12))\nsns.heatmap(country_means_c40.corr(), annot=True)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Only weak correlation between, C40 Emissions and a country's \"Yes Score\""},{"metadata":{},"cell_type":"markdown","source":"## Analysis of the correlation between country or region \"Yes Score\" and the Gini index for the country\nThe idea here is to determine how a Yes Score might correlate with a Gini Index. Starting in on this part of the analysis, it seems to make sense that a negative correlation between the two values would be likely. Negative correlation in this case would mean that a country with higher Yes Score would probably have a lower Gini Index value. Meaning that countries or regions that make sustainability efforts would have less social inequality. Not necessarily a causal relationship though."},{"metadata":{"trusted":true},"cell_type":"code","source":"gm_gini_scores = standardize_columns(pd.read_csv('/kaggle/input/income-inequality/gini.csv', encoding = 'cp1252'))\n\n# Keep only the years 2017-2021\ngm_gini_scores = pd.concat([gm_gini_scores.country, gm_gini_scores.iloc[:,-23:-19]], axis=1)\ngm_gini_scores.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Map Country names so joining to the Gap Minder Gini data is possible\ncountry_map = {'United States of America':'United States',\n               'United Kingdom of Great Britain and Northern Ireland': 'United Kingdom',\n               'Gibraltar': None, \n               'Viet Nam': 'Vietnam', \n               'Taiwan, Greater China': 'Taiwan',\n               'China, Hong Kong Special Administrative Region': None,\n               'Republic of Korea': 'South Korea', \n               'United Republic of Tanzania': 'Tanzania',\n               'Democratic Republic of the Congo': 'Congo, Dem. rep',\n               \"Côte d'Ivoire\": \"Cote d'Ivoire\",\n               'Bolivia (Plurinational State of)': 'Bolivia',\n               'State of Palestine': 'Palestine'\n              }\nall_city_data_2018['country_mapped'] = all_city_data_2018.country.map(country_map)\nall_city_data_2019['country_mapped'] = all_city_data_2019.country.map(country_map)\nall_city_data_2020['country_mapped'] = all_city_data_2020.country.map(country_map)\n\nall_city_data_2018.loc[all_city_data_2018.country_mapped.isna(), 'country_mapped'] = all_city_data_2018.country\nall_city_data_2019.loc[all_city_data_2019.country_mapped.isna(), 'country_mapped'] = all_city_data_2019.country\nall_city_data_2020.loc[all_city_data_2020.country_mapped.isna(), 'country_mapped'] = all_city_data_2020.country","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# How much data can we keep when joining the gini index data?\nprint(all_city_data_2018.country_mapped.isin(gm_gini_scores['country']).sum() / len(all_city_data_2018.country))\nprint(all_city_data_2019.country_mapped.isin(gm_gini_scores['country']).sum() / len(all_city_data_2019.country))\nprint(all_city_data_2020.country_mapped.isin(gm_gini_scores['country']).sum() / len(all_city_data_2020.country))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Merge to comare \"Current Year\" Yes Score with the Gini Score the same year and the next year\nall_city_gini_data_2018 = all_city_data_2018.merge(gm_gini_scores[['country','2018','2019']], how='inner', left_on='country_mapped', right_on='country')\nall_city_gini_data_2019 = all_city_data_2019.merge(gm_gini_scores[['country','2019','2020']], how='inner', left_on='country_mapped', right_on='country')\nall_city_gini_data_2020 = all_city_data_2020.merge(gm_gini_scores[['country','2020','2021']], how='inner', left_on='country_mapped', right_on='country')\nall_city_gini_data_2020.sample(5)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Plot the correlations\nf, ax = plt.subplots(1, 3, figsize=(30,7))\nsns.heatmap(all_city_gini_data_2018.corr(), annot=True, ax=ax[0])\nax[0].set_title('2018 Correlation Matrix')\n\nsns.heatmap(all_city_gini_data_2019.corr(), annot=True, ax=ax[1])\nax[1].set_title('2019 Correlation Matrix')\n\nsns.heatmap(all_city_gini_data_2020.corr(), annot=True, ax=ax[2])\nax[2].set_title('2020 Correlation Matrix')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Result of Correlation Analysis between Yes Score and Gini Index\nThere is a moderate negative correlation between country and region average score and the Gini Index. This indicates that a country where cities are making additional efforts to improve sustainability and environmental impact of human actions are also environments where there is more social equality as measured by a lower Gini Index."},{"metadata":{},"cell_type":"markdown","source":"## Examples of \"low\" scoring regions and countries"},{"metadata":{"trusted":true},"cell_type":"code","source":"# Examples of \"low\" scoring regions and countries\n\nprint(all_city_gini_data_2020[all_city_gini_data_2020.region_avg_score < .6].cdp_region.value_counts())\nprint('-'*25)\nprint(all_city_gini_data_2020[all_city_gini_data_2020.country_avg_score < .6].country_mapped.value_counts().head())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Checking to see if a low score for the country correlates with the Gini index\nf, ax = plt.subplots(1, 3, figsize=(30,7))\nsns.heatmap(all_city_gini_data_2018[all_city_gini_data_2018.country_avg_score < .6].loc[:,['country_mapped','country_avg_score']].drop_duplicates().merge(gm_gini_scores[['country','2018','2019']], how='inner', left_on='country_mapped', right_on='country').corr(), annot=True, ax=ax[0])\nax[0].set_title('2018 Country Score Correlation Matrix')\n\nsns.heatmap(all_city_gini_data_2019[all_city_gini_data_2019.country_avg_score < .6].loc[:,['country_mapped','country_avg_score']].drop_duplicates().merge(gm_gini_scores[['country','2019','2020']], how='inner', left_on='country_mapped', right_on='country').corr(), annot=True, ax=ax[1])\nax[1].set_title('2019 Country Score Correlation Matrix')\n\nsns.heatmap(all_city_gini_data_2020[all_city_gini_data_2020.country_avg_score < .6].loc[:,['country_mapped','country_avg_score']].drop_duplicates().merge(gm_gini_scores[['country','2020','2021']], how='inner', left_on='country_mapped', right_on='country').corr(), annot=True, ax=ax[2])\nax[2].set_title('2020 Country Score Correlation Matrix')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### No obvious correlation between a low country \"Yes score\" and the Gini Index for 2020."},{"metadata":{},"cell_type":"markdown","source":"# Checking correlation between regions with a low Yes Score and the Gini Index"},{"metadata":{"trusted":true},"cell_type":"code","source":"# Visualize - Correlation matrix for \"Low\" region scores\nf, region_ax = plt.subplots(1, 3, figsize=(30,7))\nregion_gini_merge_2018 = all_city_gini_data_2018[all_city_gini_data_2018.region_avg_score < .6].loc[:,['country_mapped','region_avg_score']].drop_duplicates()\nregion_gini_merge_2018 = region_gini_merge_2018.merge(gm_gini_scores[['country','2018','2019']], how='inner', left_on='country_mapped', right_on='country')\nregion_gini_merge_2018.rename(columns={'2018':'cdp_year','2019':'cdp_year_+_1'}, inplace=True)\nsns.heatmap(region_gini_merge_2018.corr(), annot=True, ax=region_ax[0])\nregion_ax[0].set_title('2018 Region Score Correlation Matrix')\n\nregion_gini_merge_2019 = all_city_gini_data_2019[all_city_gini_data_2019.region_avg_score < .6].loc[:,['country_mapped','region_avg_score']].drop_duplicates()\nregion_gini_merge_2019 = region_gini_merge_2019.merge(gm_gini_scores[['country','2019','2020']], how='inner', left_on='country_mapped', right_on='country')\nregion_gini_merge_2019.rename(columns={'2019':'cdp_year','2020':'cdp_year_+_1'}, inplace=True)\nsns.heatmap(region_gini_merge_2019.corr(), annot=True, ax=region_ax[1])\nregion_ax[1].set_title('2019 Region Score Correlation Matrix')\n\nregion_gini_merge_2020 = all_city_gini_data_2020[all_city_gini_data_2020.region_avg_score < .6].loc[:,['country_mapped','region_avg_score']].drop_duplicates()\nregion_gini_merge_2020 = region_gini_merge_2020.merge(gm_gini_scores[['country','2020','2021']], how='inner', left_on='country_mapped', right_on='country')\nregion_gini_merge_2020.rename(columns={'2020':'cdp_year','2021':'cdp_year_+_1'}, inplace=True)\nsns.heatmap(region_gini_merge_2020.corr(), annot=True, ax=region_ax[2])\nregion_ax[2].set_title('2020 Region Score Correlation Matrix')\n\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Bring all the data together to see what factors may help predict the Gini Index\nac_2018 = all_city_gini_data_2018.rename(columns={'country_x':'country','2018':'gini_cdp_year','question_weight':'yes_score'})\nac_2019 = all_city_gini_data_2019.rename(columns={'country_x':'country','2019':'gini_cdp_year','question_weight':'yes_score'})\nac_2020 = all_city_gini_data_2020.rename(columns={'country_x':'country','2020':'gini_cdp_year','question_weight':'yes_score'})\nall_city_gini_data = ac_2018.append(ac_2019).append(ac_2020).drop(['country_y','2019','2020','2021','country_mapped'], axis=1)\nall_city_gini_data","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cdp_region_categories = pd.CategoricalDtype(all_city_gini_data.cdp_region.dropna().unique())\nall_city_gini_data['cdp_region'] = all_city_gini_data.cdp_region.astype(cdp_region_categories)\n\nfirst_time_region_categories = pd.CategoricalDtype(all_city_gini_data.first_time_discloser.dropna().unique())\nall_city_gini_data['first_time_discloser'] = all_city_gini_data.first_time_discloser.astype(first_time_region_categories)\nprint(all_city_gini_data.dtypes)\nall_city_gini_data","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Regional factors Correlations:\nf, ax = plt.subplots(figsize=(12,12))\nsns.heatmap(all_city_gini_data.corr(), annot=True)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### A low region \"Yes Score\" is negatively correlated to the country's Gini score\nThis may indicate that areas with little or no efforts toward sustainability are some how correlated to situations that also result in environments with worse social equality (a higher Gini Index)"},{"metadata":{},"cell_type":"markdown","source":"## Decision Tree Prediction of Gini Scores from Country Yes Scores and count of cities responding"},{"metadata":{"trusted":true},"cell_type":"code","source":"from sklearn import svm\nfrom sklearn import tree\nfrom sklearn.preprocessing import MinMaxScaler\nfrom sklearn.model_selection import train_test_split\nfrom sklearn.model_selection import GridSearchCV\nfrom sklearn.model_selection import cross_val_score\n\n# Aggregate values on country\ncountry_gini_data = all_city_gini_data.groupby(['country','year_reported_to_cdp']).agg({'country_avg_score':'mean','city':'count','gini_cdp_year':'mean'}).reset_index()\n\n# Normalize the yes scores\nscaler = MinMaxScaler()\ncountry_gini_data[['country_avg_score','gini_cdp_year']] = scaler.fit_transform(country_gini_data[['country_avg_score','gini_cdp_year']])\ncountry_gini_data","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"parameter_selction_scoring_type = 'neg_mean_squared_error'\nmodel_scoring_type = 'r2'\n\n# Set up prediction\nx = country_gini_data[['country_avg_score','city']]\ny = country_gini_data['gini_cdp_year']\nX_train, X_test, y_train, y_test = train_test_split(x, y, test_size=0.2, random_state=42)\n\nparameters = {'max_depth':range(3,7)}\nclf = GridSearchCV(tree.DecisionTreeRegressor(), parameters, n_jobs=4, scoring=parameter_selction_scoring_type)\nclf.fit(X=X_train, y=y_train)\ntree_model = clf.best_estimator_\nprint(f'{parameter_selction_scoring_type}:\\tBest{(clf.best_score_, clf.best_params_)}')\n\nscores = cross_val_score(tree_model, X_test, y_test, cv=5, scoring=model_scoring_type)\nprint(f\"{model_scoring_type}: {scores.mean():0.2f} (+/- {scores.std() * 2:0.2f})\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Test countries with low scores\nscore_threshold = .6\n\n# Set up prediction\nx = country_gini_data.loc[country_gini_data.country_avg_score <= score_threshold, ['country_avg_score','city']]\ny = country_gini_data.loc[country_gini_data.country_avg_score <= score_threshold, 'gini_cdp_year']\n\n# Set up prediction\nx = country_gini_data[['country_avg_score','city']]\ny = country_gini_data['gini_cdp_year']\nX_train, X_test, y_train, y_test = train_test_split(x, y, test_size=0.3, random_state=42)\n\nparameters = {'max_depth':range(3,7)}\nlow_clf = GridSearchCV(tree.DecisionTreeRegressor(), parameters, n_jobs=4, scoring=parameter_selction_scoring_type)\nlow_clf.fit(X=X_train, y=y_train)\nlow_tree_model = low_clf.best_estimator_\nprint(f'{parameter_selction_scoring_type}:\\tBest{(low_clf.best_score_, low_clf.best_params_)}')\n\nscores = cross_val_score(low_tree_model, X_test, y_test, cv=5, scoring=model_scoring_type)\nprint(f\"{model_scoring_type}: {scores.mean():0.2f} (+/- {scores.std() * 2:0.2f})\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"fig = plt.figure(figsize=(25,20))\n_ = tree.plot_tree(tree_model, \n                   feature_names=['country_avg_score','city'],  \n                   class_names=['gini_cdp_year'],\n                   filled=True)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Conclusion\nMy analysis of a \"Yes Score\" as an indicator for the level of sustainability efforts is one method for comparing the relative efforts of cities, countries and regions. It does not seem to be a predictor of factors that lead to better social equality as measured by the Gini Index even though it is negatively correlated with the Gini Index. and it is only weakly correlated with City C40 Emissions.\n\nSince the country and region Yes Score is negatively correlated with the Gini Index, it seems that efforts tend to be higher in environments that also foster a better level of social equality as measured by the Gini Index. It doesn't make a good predictor though of the Gini Index. At the region level, a low Yes Score (Indicating a low-level effort toward sustainability as measured by the CDP Yes/No questions) is negatively correlated with the Gini index. So countries with less efforts may also have an environment that is worse for social equality."}],"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}