{"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_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"## Introduction\n\nAs a first look at this Kaggle dataset, I want to explore survey participation and seek to summarize some of properties of the participant cities.\n\nSome initial questions to frame my analysis:\n- How has survey participation changed between 2018 and 2020? \n- What does meta-analysis tell us about ways in which we can leverage the survey results?\n","metadata":{"papermill":{"duration":0.011753,"end_time":"2021-07-16T12:52:18.049015","exception":false,"start_time":"2021-07-16T12:52:18.037262","status":"completed"},"tags":[]}},{"cell_type":"code","source":"import pandas as pd\nimport seaborn as sns\nimport numpy as np\nfrom matplotlib import pyplot as plt","metadata":{"execution":{"iopub.execute_input":"2021-07-16T12:52:18.083429Z","iopub.status.busy":"2021-07-16T12:52:18.082772Z","iopub.status.idle":"2021-07-16T12:52:19.148991Z","shell.execute_reply":"2021-07-16T12:52:19.148168Z","shell.execute_reply.started":"2021-07-14T16:09:22.024003Z"},"papermill":{"duration":1.088371,"end_time":"2021-07-16T12:52:19.149162","exception":false,"start_time":"2021-07-16T12:52:18.060791","status":"completed"},"tags":[]},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# loading dataframe\ndata_root_dir = '../input/cdp-unlocking-climate-solutions'\ndata_cities_dir = data_root_dir + '\\cities\\cities responses\\\\'\n\ndef rename_mapper(index_label):\n    return str.lower(index_label).replace(' ','_')\n\ndf_disc_2018 = pd.read_csv(data_root_dir + '/Cities/Cities Disclosing/2018_Cities_Disclosing_to_CDP.csv')\ndf_disc_2019 = pd.read_csv(data_root_dir + '/Cities/Cities Disclosing/2019_Cities_Disclosing_to_CDP.csv')\ndf_disc_2020 = pd.read_csv(data_root_dir + '/Cities/Cities Disclosing/2020_Cities_Disclosing_to_CDP.csv')\n\ndf_response = pd.read_csv('../input/cdp-unlocking-climate-solutions/Cities/Cities Responses/2019_Full_Cities_Dataset.csv')\n\ndf_disc_2018['year'] = 2018\ndf_disc_2019['year'] = 2019\ndf_disc_2020['year'] = 2020\n\ndf_disc_2018.rename(columns=rename_mapper,inplace=True)\ndf_disc_2019.rename(columns=rename_mapper,inplace=True)\ndf_disc_2020.rename(columns=rename_mapper,inplace=True)\ndf_response.rename(columns=rename_mapper,inplace=True)\n\ndf_disc = pd.concat([df_disc_2018,df_disc_2019,df_disc_2020])","metadata":{"execution":{"iopub.execute_input":"2021-07-16T12:52:19.179646Z","iopub.status.busy":"2021-07-16T12:52:19.178882Z","iopub.status.idle":"2021-07-16T12:52:24.788288Z","shell.execute_reply":"2021-07-16T12:52:24.787752Z","shell.execute_reply.started":"2021-07-14T16:09:22.030813Z"},"papermill":{"duration":5.628238,"end_time":"2021-07-16T12:52:24.788433","exception":false,"start_time":"2021-07-16T12:52:19.160195","status":"completed"},"tags":[]},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Survey Participation \n\nI'm interested in knowing ways in which survey participation changed each year, such as:\n- How many cities participated only one of the survey years?\n- How many cities participated every single survey year (in this dataset)\n- What is the city partipation count by region?\n\nBy knowing how and why the survey participation improved (or didn't improve) each year, we can begin to understand how to improve participation going forward. The charts below may indicate good starting points for that analysis.","metadata":{"papermill":{"duration":0.010453,"end_time":"2021-07-16T12:52:24.809823","exception":false,"start_time":"2021-07-16T12:52:24.799370","status":"completed"},"tags":[]}},{"cell_type":"code","source":"df_tmp = df_disc.groupby(['cdp_region','year'],as_index=False).size()\n\nsns.set_theme(style='whitegrid')\nplt.figure(figsize=(7.5,7.5))\n\ngraph = sns.barplot(\n    data=df_tmp,\n    x='year', \n    y='size',\n    palette='deep',\n    ci=None\n    ).set_title(\n        'Total # Respondent Cities, Each Year'\n        , size=14\n    )\n\nplt.xlabel('Year')\nplt.ylabel('# Cities')","metadata":{"execution":{"iopub.execute_input":"2021-07-16T12:52:24.844478Z","iopub.status.busy":"2021-07-16T12:52:24.839635Z","iopub.status.idle":"2021-07-16T12:52:25.056529Z","shell.execute_reply":"2021-07-16T12:52:25.057048Z","shell.execute_reply.started":"2021-07-14T16:09:25.36753Z"},"papermill":{"duration":0.236621,"end_time":"2021-07-16T12:52:25.057239","exception":false,"start_time":"2021-07-16T12:52:24.820618","status":"completed"},"tags":[]},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Unfortunately - 2020 had the lowest participation count out of all three survey years. It would be interesting to know if the COVID pandemic had any impact on survey participation.","metadata":{"papermill":{"duration":0.01176,"end_time":"2021-07-16T12:52:25.081238","exception":false,"start_time":"2021-07-16T12:52:25.069478","status":"completed"},"tags":[]}},{"cell_type":"code","source":"sns.set_theme(style='whitegrid')\n\nplt.figure(figsize=(15,7.5))\ngraph = sns.barplot(\n    data=df_tmp,\n    x='year', \n    y='size', \n    hue='cdp_region',\n     palette='deep'\n     ).set_title(\n    '# of Respondent Cities by Region',\n    size=14\n    )\nplt.xlabel('Year')\nplt.ylabel('# of Cities')\n\n\n# Put the legend out of the figure\nplt.legend(bbox_to_anchor=(1.05, 1), loc=2)","metadata":{"execution":{"iopub.execute_input":"2021-07-16T12:52:25.154407Z","iopub.status.busy":"2021-07-16T12:52:25.153349Z","iopub.status.idle":"2021-07-16T12:52:25.488409Z","shell.execute_reply":"2021-07-16T12:52:25.487780Z","shell.execute_reply.started":"2021-07-14T16:09:25.550218Z"},"papermill":{"duration":0.395254,"end_time":"2021-07-16T12:52:25.488563","exception":false,"start_time":"2021-07-16T12:52:25.093309","status":"completed"},"tags":[]},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"It may be worth noting that the Latin American region had a surge of participation in 2019. Other regions don't seem to have as significant of a participation change between the survey years.","metadata":{"papermill":{"duration":0.01335,"end_time":"2021-07-16T12:52:25.515728","exception":false,"start_time":"2021-07-16T12:52:25.502378","status":"completed"},"tags":[]}},{"cell_type":"code","source":"df_tmp = df_disc\ndf_tmp = df_tmp[['city','year']]\ndf_tmp = df_tmp.groupby('city',as_index=False).nunique()\n\ndf_tmp = df_tmp.groupby('year',as_index=False).size()\n\nplt.figure(figsize=(7.5,7.5))\nsns.barplot(data=df_tmp,x='year',y='size')\nplt.xlabel('# of Year''s Participating')\nplt.ylabel('# of Cities')\nplt.title('Survey Participation, Years Participating',size=14)","metadata":{"execution":{"iopub.execute_input":"2021-07-16T12:52:25.556297Z","iopub.status.busy":"2021-07-16T12:52:25.555265Z","iopub.status.idle":"2021-07-16T12:52:25.746957Z","shell.execute_reply":"2021-07-16T12:52:25.746315Z","shell.execute_reply.started":"2021-07-14T16:09:25.924143Z"},"papermill":{"duration":0.217832,"end_time":"2021-07-16T12:52:25.747115","exception":false,"start_time":"2021-07-16T12:52:25.529283","status":"completed"},"tags":[]},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"It seems encouraging that a plurality of cities participated for 3 straight years. \n\nIf the CDP is aiming to expand the scope of participation each year, it could be a point of interest to understand why particular cities participated for *less* than 3 straight years.  However - perhaps it's not critical for cities to participate every single year, as cities could be providing redundant information.","metadata":{"papermill":{"duration":0.014814,"end_time":"2021-07-16T12:52:25.777422","exception":false,"start_time":"2021-07-16T12:52:25.762608","status":"completed"},"tags":[]}},{"cell_type":"markdown","source":"## Question Meta-Analysis\n\n  - Which questions have a limited number of responses? \n  - Which questions have many responses?\n  - Which questions have a high count of numeric responses?","metadata":{"papermill":{"duration":0.014626,"end_time":"2021-07-16T12:52:25.807079","exception":false,"start_time":"2021-07-16T12:52:25.792453","status":"completed"},"tags":[]}},{"cell_type":"code","source":"df_tmp = df_response\n\ndf_tmp = df_tmp.assign(is_response_notnull = np.where(~(df_tmp.response_answer.isna()),1,0))\ndf_tmp = df_tmp.assign(is_response_numeric = np.where(df_tmp.response_answer.str.isnumeric(),1,0))\n\ndf_tmp = df_tmp.groupby('parent_section',as_index=False).agg(\n    count_values = pd.NamedAgg('is_response_notnull','sum'),\n    count_numeric = pd.NamedAgg('is_response_numeric','sum'),\n    count_total = pd.NamedAgg('response_answer','size')\n)\n\ndf_tmp = df_tmp.assign(perc_numeric = [\"{:.2%}\".format(val) for val in round(df_tmp.count_numeric/df_tmp.count_total,4)])\ndf_tmp = df_tmp.assign(perc_values = [\"{:.2%}\".format(val) for val in round(df_tmp.count_values/df_tmp.count_total,4)])\n\ndf_tmp.sort_values(by='count_values',ascending=False)","metadata":{"execution":{"iopub.execute_input":"2021-07-16T12:52:25.886373Z","iopub.status.busy":"2021-07-16T12:52:25.885271Z","iopub.status.idle":"2021-07-16T12:52:26.493132Z","shell.execute_reply":"2021-07-16T12:52:26.491880Z","shell.execute_reply.started":"2021-07-14T16:09:26.127879Z"},"papermill":{"duration":0.671194,"end_time":"2021-07-16T12:52:26.493342","exception":false,"start_time":"2021-07-16T12:52:25.822148","status":"completed"},"tags":[]},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Next Steps\n\n- Look in detail at: \n    - The cities participating in 2019 that *did not* participate in 2020\n    - The cities participating in 2020 that *did* participate in 2019\n- Does either category have distinguishing properties?\n- What are the cities that *aren't* participating? See if it's possible to get a list of the cities not participating above a particular population threshold\n- Perform survey participation analysis for *corporations*\n","metadata":{"papermill":{"duration":0.015235,"end_time":"2021-07-16T12:52:26.600239","exception":false,"start_time":"2021-07-16T12:52:26.585004","status":"completed"},"tags":[]}},{"cell_type":"markdown","source":"\n\n","metadata":{"papermill":{"duration":0.023783,"end_time":"2021-07-16T12:52:26.644091","exception":false,"start_time":"2021-07-16T12:52:26.620308","status":"completed"},"tags":[]}}]}