{"cells":[{"metadata":{},"cell_type":"markdown","source":"In this notebook we try establishing a way to investigate and evaluate the level of cities engagement in answering the questionnaire. Such level of engagement shows which questions are more important for cities and which questions are more often avoided. Measuring engagement could help analysis social and climate issues, but most importantly shall help with improving the questionnaire and engaging cities into providing more complete answers"},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"markdown","source":"# Table of Contents\n\n* [Data Loading and Cleaning](#data-load)\n* [Cities Engagement in the Survey](#engagement)\n    - [Engagement KPI](#kpi)    \n    - [Grouping cities based on answerring or skipping questions pattern](#groupping)\n    - [Grouping questions based on cities answering](#groupping_questions)\n* [Corporations Engagement in the Survey](#corp_engagement)    \n    "},{"metadata":{},"cell_type":"markdown","source":"<a id=\"data-load\"></a>\n# Data Loading and cleaning\nAlthough using additional data source helps a lot in understanding of environmental and social issues our planet is facing now, we concentrate on analysis of CDP questionaires responses only. \n\nLet us start with restructuring  and cleaning input data. \n"},{"metadata":{"trusted":true},"cell_type":"code","source":"#all the imports required for the notebook are given in this block\nimport pandas as pd\nimport numpy as np\nimport matplotlib.pyplot as plt\nfrom IPython.core.display import HTML\nfrom IPython.display import  Markdown\nimport seaborn as sns\nimport geopandas as gpd\nfrom shapely.geometry import Point\n\nimport plotly.graph_objects as go\nimport plotly.express as px\nfrom plotly.subplots import make_subplots\n\nimport seaborn as sns\n\nimport umap\nfrom sklearn.cluster import DBSCAN\n\n\n#centering figures\nHTML(\"\"\"<style> .output_png { display: table-cell; text-align: center; vertical-align: middle;}</style>\"\"\")\n\n#my colors\ncolors= ['#003f5c','#2f4b7c','#665191','#a05195','#d45087','#f95d6a','#ff7c43','#ffa600','#fcca46','#a1c181','#619b8a','#386641']","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"# Load cities disclosing info\ncities_disclosing_2018 = pd.read_csv(\"/kaggle/input/cdp-unlocking-climate-solutions/Cities/Cities Disclosing/2018_Cities_Disclosing_to_CDP.csv\")\ncities_disclosing_2019 = pd.read_csv(\"/kaggle/input/cdp-unlocking-climate-solutions/Cities/Cities Disclosing/2019_Cities_Disclosing_to_CDP.csv\")\ncities_disclosing_2020 = pd.read_csv(\"/kaggle/input/cdp-unlocking-climate-solutions/Cities/Cities Disclosing/2020_Cities_Disclosing_to_CDP.csv\")\n\n\n# Load cities responses\ncities_2018 = pd.read_csv(\"/kaggle/input/cdp-unlocking-climate-solutions/Cities/Cities Responses/2018_Full_Cities_Dataset.csv\")\ncities_2019 = pd.read_csv(\"/kaggle/input/cdp-unlocking-climate-solutions/Cities/Cities Responses/2019_Full_Cities_Dataset.csv\")\ncities_2020 = pd.read_csv(\"/kaggle/input/cdp-unlocking-climate-solutions/Cities/Cities Responses/2020_Full_Cities_Dataset.csv\")\n\n## Define Functions for bringing data in tabular form\n   \ndef pivot_cities(cities):\n    ''' make pivot table preserving multi-choice columns '''    \n    \n    def fix_pivot_column(col):\n        ''' remove dummy arrays in pivoted cities dataframe '''\n        def fix_multi_choice(x):\n            if x==x: \n                return x[0]\n            else:\n                return x    \n        def fix_single_choice(x):\n            if x==x: \n                return x[0][0]\n            else:\n                return x\n\n        if all([len(c[0]) == 1 for c in col if c==c]):\n            return col.map(fix_single_choice)\n        else:\n            return col.map(fix_multi_choice)\n    \n    cities_piv = cities.pivot_table(index=['CDP Region', 'Country', 'Account Number', 'Organization'], \n                      columns=[ 'Question Number', 'Column Number', 'Row Number'], \n                      values = ['Response Answer'],\n                   aggfunc = lambda x:[x.values])    \n\n    # drop columns with only one answer - > like filled by mistake\n    columns = cities_piv.columns\n    to_drop=[]\n    for col in columns:        \n        if sum([len(c[0]) == 1 for c in cities_piv[col] if c==c]) == 1:\n            to_drop.append(col)\n    cities_piv.drop(columns=to_drop, inplace=True)        \n    \n    # remove dummy arrays that appear in pivot because of multi-choice columns\n    columns = cities_piv.columns\n    for col in columns:        \n        cities_piv[col] = fix_pivot_column(cities_piv[col])\n    return cities_piv\n\ndef get_questions(cities):\n    ''' get questions reference table '''\n    return cities.loc[:, ['Question Number', 'Question Name', 'Column Number', 'Column Name', 'Row Number', 'Row Name']].drop_duplicates()\n\ndef get_question_names(cities, questionNumber, columnNumber, rowNumber):\n    questions = get_questions(cities)\n    return questions.loc[(questions['Question Number'] == questionNumber) & \n                  (questions['Column Number'] == columnNumber) & \n                  (questions['Row Number'] == rowNumber), ['Question Name', 'Column Name', 'Row Name']]\n    \n\ndef fix_row_number(cities):\n    '''For Rows without Name, positive Row Number means multi-choice question. Change Row Number accordingly'''\n    cities.loc[pd.isnull(cities['Row Name']), 'Row Number'] = 0\n\ndef fix_skipped_multichoice(cities_piv):\n    \"\"\" Caused by use of wrong use of row number and the way it is fixed by fix_row_number, some multichoice answers contain nan. This functions remove those nan's\"\"\"\n    def fix_skipped_multichoice_loc(multichoice):\n        multichoice_fixxed = np.array([choice for choice in multichoice if choice==choice])\n        if len(multichoice_fixxed)==0:\n            multichoice_fixxed = np.array([np.nan])\n        return multichoice_fixxed\n    \n    for col in cities_piv.columns:\n        c = cities_piv[col]\n        if type(c[0]) == np.ndarray:\n            cities_piv[col] = cities_piv[col].apply(fix_skipped_multichoice_loc)\n    return cities_piv\n    \n\n# Questions to consider    \nCDP_recommended_questions = ['0.1', '0.5', '1.0', '1.0a', '2.0', '2.0a', '2.0b', '2.1', '2.2', '4.0', '4.1', '4.2', '4.3', '4.4', '4.5', \n                    '5.0', '5.0a', '5.0b','5.0c','5.0d', '6.0', '6.1',  '8.0', '8.0a', '8.2', '8.3', '8.5', '10.1',\n                   '10.2', '10.11', '10.12', '10.13', '10.14', '10.15', '12.0', '12.4', '14.0', '14.1', '14.3',  '14.4'] # ['6.1a' '8.6' '10.16' '14.3a'] were never answered/asked in 2020\n\n\n# Cleans and transform data to tabular form\nfix_row_number(cities_2020)\ncities_2020_piv = pivot_cities(cities_2020)\ncities_2020_piv = cities_2020_piv['Response Answer']#[CDP_recommended_questions]\ncities_2020_piv = fix_skipped_multichoice(cities_2020_piv)\n\n\n        ","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"<a id=\"engagement\"></a>\n# Cities Engagement in the Survey\nWe would like to understand why some questions are not answered by some cities. \n\nAs we can see there is a big variety in how actively different questions was answered\n"},{"metadata":{"trusted":true},"cell_type":"code","source":"# Find which questions were not answered at all, and which were answered with 'Question not applicable'\n\ndef is_answered (x):\n    if isinstance(x, list) or isinstance(x, np.ndarray):\n        return any(x==x)\n    else:\n        return x==x\n    \ndef is_not_applicable(x):\n    if isinstance(x, list) or isinstance(x, np.ndarray):\n        return all([el == 'Question not applicable' for el in x ])\n    else:\n        return x == 'Question not applicable'\n\n\ndef find_answered_question(cities_piv):    \n    cities_answered = pd.DataFrame().reindex_like(cities_piv, method = None)\n    cities_notApplicable = pd.DataFrame().reindex_like(cities_piv, method = None)\n    \n    for col in cities_piv.columns:    \n        cities_answered[col] = cities_piv[col].apply(is_answered)\n        cities_notApplicable[col] = cities_piv[col].apply(is_not_applicable)\n\n    cities_answer_provided = cities_answered & (~cities_notApplicable)    \n    return cities_answered, cities_notApplicable, cities_answer_provided\n\ncities_2020_answered, cities_2020_notApplicable, cities_2020_answer_provided = find_answered_question(cities_2020_piv)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def plot_answere_per_question(cities, cities_bool):\n\n    answers_per_question = cities_bool.values.sum(axis=0)\n\n    questionNumber = list(map(lambda x: x[0], cities_bool.columns))\n    columnNumber = list(map(lambda x: x[1], cities_bool.columns))\n    rowNumber = list(map(lambda x: x[2], cities_bool.columns))\n\n    df = pd.DataFrame({'Question Number': questionNumber, 'Column Number': columnNumber, 'Row Number':rowNumber, 'Answer Count': answers_per_question})\n    df = df.merge(get_questions(cities), on = ['Question Number', 'Column Number', 'Row Number'])\n    df['ind'] = df.index\n    df['Question Name'] = df['Question Name'].apply(lambda x: x[:60])\n    df['Column Name'] = df['Column Name'].apply(lambda x: (x[:60] if x==x else 'None'))\n    df['Row Name'] = df['Row Name'].apply(lambda x: (x[:60] if x==x else 'None'))\n\n\n    fig = px.scatter(df, x='ind', y='Answer Count', color='Question Number', \n                     hover_data=['Answer Count', 'Question Number', 'Question Name', 'Column Number', 'Column Name', 'Row Number', 'Row Name'],\n                     title=\"Number of answers per question\",height=800,)\n\n    return fig\n    \n    \nplot_answere_per_question(cities_2020, cities_2020_answer_provided)\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"To make plot less busy we look only at questions recommended for the analysis"},{"metadata":{"trusted":true},"cell_type":"code","source":"fig = plot_answere_per_question(cities_2020, find_answered_question(cities_2020_piv[CDP_recommended_questions])[2])\nfig.update_layout(title='Number of answers on selected set of questions')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Few of recommended questions were actually answered by majority of the cities. Ans some very important questions, were answered by a fraction of cities.\nFor example question 10.15 \"Please indicate if your city currently has any programs or projects to improve air quality.\" was unswered only by 39 cities! (See table below)"},{"metadata":{"trusted":true},"cell_type":"code","source":"# the only 39 cities that indicated they currrently have any programs or projects to improve air quality\npd.DataFrame(cities_2020_piv['10.15'][0][0].loc[cities_2020_answer_provided['10.15'][0][0]])","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"<a id=\"kpi\"></a>\n## Engagement KPI\n\nWe define an engagement  KPI that shows how much a city is willing to give answers to the questionnaire as quantile ranking of number of answered questions (i.e City that answered the most of questions in a category get KPI value of 1.0, and City that answeerd the least of question get KPI value of 0). Such KPI could be defined per question, per section, per group of questions or for the whole questionnaire. And we suggest to use qunatile raning as KPI. Further we caclulate the KPI on set of Recomended questions."},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_2020_selected_answered =find_answered_question(cities_2020_piv[CDP_recommended_questions])[2]\ncities_2020_selected_answered['n_answers'] = cities_2020_selected_answered.values.sum(axis=1)\ncities_2020_selected_answered['rank'] = cities_2020_selected_answered['n_answers'].rank(pct=True)\n\ndf = cities_2020_selected_answered.reset_index().loc[:, ['Account Number']]\ndf = df.merge(cities_disclosing_2020.loc[:, ['City', 'Account Number', 'City Location', 'CDP Region']], on=['Account Number'])\ndf.fillna('POINT (0 0)', inplace=True) # fast dummy solution for cities without coordinates\ndf['x'] = df['City Location'].apply(lambda txt: float(txt.split(\"POINT \")[1].replace('(', '').replace(\")\", '').split(\" \")[0]))\ndf['y'] = df['City Location'].apply(lambda txt: float(txt.split(\"POINT \")[1].replace('(', '').replace(\")\", '').split(\" \")[1]))\ndf['rank'] = cities_2020_selected_answered['rank'].values\ndf\n\nsmb = go.Scattermapbox(name='severity map',\n        lon = df.x,\n        lat = df.y,\n        text = df.loc[:,['City', 'rank']],\n        mode = 'markers',\n        marker = dict(\n            size = 10,\n            opacity = 0.8,\n            colorscale = 'Viridis',\n            cmin = 0,\n            color = cities_2020_selected_answered['rank'],\n\n        ))\nfig = go.Figure()\n\nfig.add_trace(smb)\nfig.update_layout(\n        mapbox_style=\"open-street-map\",\n        title = dict(text='Questionnaire Engagement KPI for recommended set of questions',x=0.5,y=.97),\n        height=900,\n        width=900,\n        )\n\ncities_2020_selected_answered = cities_2020_selected_answered.drop(columns= ['n_answers', 'rank'])\n\nfig ","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"<a id=\"groupping\"></a>\n## Grouping cities based on answering or skipping questions\n\nIt would be possible to define an engagement  KPI: KPI showing how much a city is willing to give answers to the questionnaire. Such KPI could be defined per question, per section, per group of questions or for the whole questionnaire. And we suggest to use qunatile raning as KPI (i.e City that answered the most of questions in a category get KPI value of 1.0, and City that answeerd the least of question get KPI value of 0)"},{"metadata":{},"cell_type":"markdown","source":"Going further we can cluster cities based on the way the answer the questions. We see distinc clusters associated with often answering on distinct questions.  For example cluster number 4 consist of cities that answered on 10.14 questions"},{"metadata":{"trusted":true},"cell_type":"code","source":"embedding = umap.UMAP(metric='cosine', n_components = 2, random_state=42).fit_transform(cities_2020_selected_answered.values)\n\nclustering = DBSCAN().fit(embedding)\nlabels = clustering.labels_\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df['umap_x'] = embedding[:,0]\ndf['umap_y'] = embedding[:,1]\ndf['cluster_id'] = labels\n\nfig = px.scatter(df, x='umap_x', y='umap_y', color='cluster_id', \n                 hover_data=['City', 'cluster_id'],\n                 title=\"Clustering of Cities by the answering on the Questions recommened for the analysis\")\nfig","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_2020_selected_answered['cluster_id'] = labels\ndf2 = cities_2020_selected_answered.groupby('cluster_id').mean()\nx = np.arange(len(df2.columns))\n\n# Create traces\nfig = go.Figure(layout = go.Layout(title='Average answering frequency per cluster over set of recommended questions', legend = {'title':'cluster_id'}))\n\n\nfor i, cluster_id in  enumerate(list(df2.index)):\n    fig.add_trace(go.Scatter(x=x, y=df2.values[i,:],\n                        mode='lines',\n                        name= str(cluster_id),\n                        hovertext = list(df2.columns)))\n\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"smb = go.Scattermapbox(name='severity map',\n        lon = df.x,\n        lat = df.y,\n        hovertext = df.loc[:, ['City', 'cluster_id']],\n        mode = 'markers',\n        marker = dict(\n            size = 10,\n            opacity = 0.8,\n            colorscale = 'rainbow',\n            color = df[\"cluster_id\"],\n        ))\nfig = go.Figure()\n\nfig.add_trace(smb)\nfig.update_layout(\n        mapbox_style=\"open-street-map\",\n        title = dict(text='Clustering of Cities by the answering on the Questions recommened for the analysis',x=0.5,y=.97),\n        height=900,\n        width=900,\n        )","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"<a id=\"groupping_questions\"></a>\n## Grouping questions based on cities answering\n\n\nIt would be possible to define an engagement  KPI: KPI showing how much a city is willing to give answers to the questionnaire. Such KPI could be defined per question, per section, per group of questions or for the whole questionnaire. And we suggest to use qunatile raning as KPI (i.e City that answered the most of questions in a category get KPI value of 1.0, and City that answeerd the least of question get KPI value of 0)\nQuestions could also be clustered in the simialr way.**"},{"metadata":{"trusted":true},"cell_type":"code","source":"embedding = umap.UMAP(metric='cosine', n_components = 2, random_state=42).fit_transform(cities_2020_selected_answered.values.T)\nclustering = DBSCAN(eps = 1).fit(embedding)\nlabels = clustering.labels_\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"questions = list(cities_2020_selected_answered.columns)\ndf = pd.DataFrame({'x': embedding[:,0],'y': embedding[:,1], 'cluster_id':labels, 'question': questions})\n\n\nfig = px.scatter(df, x='x', y='y', color='cluster_id', \n                 hover_data=['question'],\n                 title=\"Clustering of recommended Questions by the way how they were answered by cities\")\nfig\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"<a id=\"corp_engagement\"></a>\n# Corporations Engagement in the Survey\nOur assumption is that analysys of corporation engagement is much more interesting, as vwe expect corporations to avoid answering certain questions. Analysing this phenomena with methodology developped above for the cities could actually lead to defenition of other KPIs, more meaningful for actual social and environmental effects. And most definetely such analysis shall lead for a better questionnaire and strategy for engaging cororations into sharing answers.\n\n"},{"metadata":{"trusted":true},"cell_type":"code","source":"#TODO","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat":4,"nbformat_minor":4}