{"cells":[{"metadata":{},"cell_type":"markdown","source":"# Introduction\n\n\nThere are 2 main folders :- 1. Cities and 2. Corporations.\n\n\nNow under Cities, there are 3 folders :-\n1. Cities Disclosing\n2. Cities Questonnaires\n3. Cities Responses\n\n\nIn this notebook, we explore and visualize Cities dataset. "},{"metadata":{},"cell_type":"markdown","source":"### List All Files \n\nHere is full list of files.\n\n\n\n\n"},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true,"_kg_hide-output":true},"cell_type":"code","source":"\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 plotly.graph_objects as go\nimport plotly_express as px\n\n# Input data files are available in the read-only \"../input/\" directory\n# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory\n\nimport os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        print(os.path.join(dirname, filename))\n\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"#### Code for bar graphs"},{"metadata":{"_kg_hide-input":false,"_kg_hide-output":true,"trusted":true},"cell_type":"code","source":"def sort_dict_by_values(dict):\n    \n    # Change dict from (key,value) to (value,key) pairs\n    # {'a': 20,'b': 10,'c': 30}  changes to  {20: 'a', 10: 'b', 30: 'c'}\n    \n    dict = {v:k    for(k,v) in dict.items()}\n    \n    # Sort list of tuples in Desc order\n    # ([(20, 'a'), (10, 'b'), (30, 'c')])  changes to   [(30, 'c'), (20, 'a'), (10, 'b')]\n    \n    list_tuples = sorted(dict.items(), reverse=True)\n    \n    # Create dictionary from list of tuples (Dictionary Comprehension)\n    # {'a':20,'b':10,'c':30} \n    \n    dict = {v:k for (k,v) in list_tuples}\n    \n    return dict","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-output":false,"trusted":true},"cell_type":"code","source":"def draw_bar_chart(count_dict, title, orientation='v', color='blue'):\n    \n    count_series = pd.Series(count_dict)\n    \n    fig = go.Figure()\n    \n    trace = go.Bar(\n        x=count_series.index,\n        y=count_series.values,\n        text=count_series.values,\n        textposition='auto',\n        marker=dict(\n            color=color,\n            ),\n    )\n\n    fig.add_trace(trace)\n    \n    #Set xaxis_tickangle depending on orientation\n    if (orientation =='h'):\n        angle = 0 \n    else:\n        angle=-45\n\n    # Set layout properties for title, title_font, axis_tick_angle and background color\n    fig.update_layout(\n        \n        title=dict(\n            text=title,\n            y=0.9,\n            x=0.5,\n            xanchor= 'center',\n            yanchor= 'top'\n        ),\n        \n        font=dict(\n            family=\"Arial\",\n            size=14,\n            color=\"#7f7f7f\"\n        ),\n        \n        xaxis_tickangle=angle,\n        plot_bgcolor = 'White',\n        \n     )\n\n    fig.show()\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"markdown","source":"### 1. Cities Disclosing"},{"metadata":{},"cell_type":"markdown","source":"#### Cities Disclosing Data dictionary"},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_disc_2020_data_dict = pd.read_csv(\"/kaggle/input/cdp-unlocking-climate-solutions/Cities/Cities Disclosing/Cities_Disclosing_to_CDP_Data_Dictionary.csv\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_disc_2020_data_dict.shape","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_disc_2020_data_dict.info()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_disc_2020_data_dict.head(13)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"#### Cities Disclosing Data Set"},{"metadata":{},"cell_type":"markdown","source":"#### Cities 2018"},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_2018 = pd.read_csv(\"/kaggle/input/cdp-unlocking-climate-solutions/Cities/Cities Disclosing/2018_Cities_Disclosing_to_CDP.csv\")\ncities_2019 = pd.read_csv(\"/kaggle/input/cdp-unlocking-climate-solutions/Cities/Cities Disclosing/2019_Cities_Disclosing_to_CDP.csv\")\ncities_2020 = pd.read_csv(\"/kaggle/input/cdp-unlocking-climate-solutions/Cities/Cities Disclosing/2020_Cities_Disclosing_to_CDP.csv\")\n\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_2018.shape","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_2018.info()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_2018.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# get all the unique values in the 'City' column\ncities_2018_uniq = cities_2018['City'].unique().tolist()\n\n# sort them alphabetically and then take a closer look\ncities_2018_uniq.sort()\nlen(cities_2018_uniq)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# get all the values in the 'Country' column\ncities_2018_all = cities_2018['City'].values.tolist()\n\n# sort them alphabetically and then take a closer look\ncities_2018_all.sort()\nlen(cities_2018_all)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# find repeated city\nset([x for x in cities_2018_all if cities_2018_all.count(x) > 1])\n\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_2018.loc[cities_2018['City'].isin(['Santiago'])]","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"#### Understanding\nIt is not uncommon for smaller towns to keep same name as Capital or bigger town."},{"metadata":{"trusted":true},"cell_type":"code","source":"# get the number of missing data points per column\ncities_2018_missing_data_count = cities_2018.isnull().sum()\n\n# look at the # of missing points in the first ten columns\ncities_2018_missing_data_count[0:13]","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"#### Understanding\n55 rows have population data missing."},{"metadata":{},"cell_type":"markdown","source":"#### What about City repetition in 2019 and 2020"},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_2019_all = cities_2019['City'].values.tolist()\nset([x for x in cities_2019_all if cities_2019_all.count(x) > 1])\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_2019.loc[cities_2019['City'].isin(['Santiago'])]","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"#### Understanding\nLike 2018 Santiago is repeated and population is missing for larger city.\n\nAlso nan us repeated 200 something times as seen below."},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_2019.loc[cities_2019['City'].isnull()]","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"#### What about 2020?"},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_2020_all = cities_2020['City'].values.tolist()\nset([x for x in cities_2020_all if cities_2020_all.count(x) > 1])\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_2020.loc[cities_2020['City'].isnull()]","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"#### Understanding\nIn 2020 also, 119 rows have city value Nan."},{"metadata":{},"cell_type":"markdown","source":"### Year wise Survey Participation\nLet us find out the trends un years 2018, 2019, 2020 of coint of Cities, Organisations and countries."},{"metadata":{"trusted":true},"cell_type":"code","source":"count_dict = {\n    'cities 2018' : (cities_2018['City'].unique().size),\n    'cities 2019' : (cities_2019['City'].unique().size),\n    'cities 2020' : (cities_2020['City'].unique().size),\n    \n}\n\n#Sort dictionary by values\ncount_dict = sort_dict_by_values(count_dict)\n\n#Draw Chart\ndraw_bar_chart(count_dict,'cities')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"count_dict = {\n    \n    'countries 2018' : (cities_2018['Country'].unique().size),\n    'countries 2019' : (cities_2019['Country'].unique().size),\n    'countries 2020' : (cities_2020['Country'].unique().size),\n    \n}\n\n#Sort dictionary by values\ncount_dict = sort_dict_by_values(count_dict)\n\n#Draw Chart\ndraw_bar_chart(count_dict,'Country')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"count_dict = {\n    'Organization 2018' : (cities_2018['Organization'].unique().size),\n    'Organization 2019' : (cities_2019['Organization'].unique().size),\n    'Organization 2020' : (cities_2020['Organization'].unique().size),\n}\n\n#Sort dictionary by values\ncount_dict = sort_dict_by_values(count_dict)\n\n#Draw Chart\ndraw_bar_chart(count_dict,'Organization')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"#### Conclusion\nYear by year there is decline in participation."},{"metadata":{"trusted":true},"cell_type":"code","source":"import plotly.express as px\n\nfig = px.scatter_geo(cities_2018, locations=\"City Location\", color=\"CDP Region\",\n                     hover_name=\"City\", \n                     #size=\"Population\",\n                     projection=\"natural earth\")\nfig.show()","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":{"trusted":true},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_disc_2020_data = pd.read_csv(\"/kaggle/input/cdp-unlocking-climate-solutions/Cities/Cities Disclosing/2020_Cities_Disclosing_to_CDP.csv\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_disc_2020_data.shape","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_disc_2020_data.info()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_disc_2020_data.head(10)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Cities Responses 2020\n"},{"metadata":{},"cell_type":"markdown","source":"#### Cities Responses Data Dictionary"},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_resp_2020_data_dict = pd.read_csv(\"/kaggle/input/cdp-unlocking-climate-solutions/Cities/Cities Responses/Full_Cities_Response_Data_Dictionary.csv\")\ncities_resp_2020_data_dict.shape","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_resp_2020_data_dict.info()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_resp_2020_data_dict.head(18)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"#### Cities Responses Data Set 2020"},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true},"cell_type":"code","source":"\n# read in all our data\ncities_resp_2020_data = pd.read_csv(\"/kaggle/input/cdp-unlocking-climate-solutions/Cities/Cities Responses/2020_Full_Cities_Dataset.csv\")\n                            \ncities_resp_2020_data_dict = pd.read_csv(\"/kaggle/input/cdp-unlocking-climate-solutions/Cities/Cities Responses/Full_Cities_Response_Data_Dictionary.csv\")\n\n# set seed for reproducibility\nnp.random.seed(0) ","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":" Let us take a look at some of it"},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_resp_2020_data.shape","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"This table has .8 million 69 thousand 3 hundred 13 Rows and 18 columns."},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_resp_2020_data.info()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"There are 18 columns as listed above.\n\n4 of them namely( 1 -  Year Reported to CDP, 2 - Account Number, 10 - Column Number and 12 - Row Number) are int64 .\n\nRest 14 are Object type.  \n\n\n\n\nAs can be seen under non -null section, there are lot of missing values.\n\n 11  Column Name           840594 non-null  object\n \n 13  Row Name              404832 non-null  object\n \n 14  Response Answer       703025 non-null  object\n \n 15  Comments              20123 non-null   object\n \n 16  File Name             1786 non-null    object\n\n"},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_resp_2020_data.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Parent section and last few columns have lot of NaN values"},{"metadata":{},"cell_type":"markdown","source":"### Unique Data\n#### 2020\nHow many categories of data do we have?"},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_resp_2020_data.columns","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def display_columns_uniqvals(df):\n    for i, col in enumerate(df.columns.tolist()):\n         #print('\\n ({} {}) Sz:{} \\n Uniq: {} '.format(i,col, df[col].unique().size, df[col].unique() ))\n        print('\\n ({} {}) Sz:{} '.format(i,col, df[col].unique().size ))\n    print('\\n')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"display_columns_uniqvals(cities_resp_2020_data)\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Visualizing CDP Region, Parent Section and Section"},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_resp_2020_data['CDP Region'].unique()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_resp_2020_data['Parent Section'].unique()\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"#### Missing  Parent Section\nIn 2020 Full cities dataset, Parent setion is Nan wherever Section has following values :-\nBuilding, Energy, Food,Transport, Urban Planning, Waste , Introduction, Submit your response.\n\nFrom 2018 and 2019 Full cities dataset we can infer that Strategy should be value for Parent Section in all but last two."},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_resp_2020_data[pd.isnull(cities_resp_2020_data['Parent Section'])& \\\n                      cities_resp_2020_data['Section'].isin(['Building','Energy','Food','Transport',\\\n                        'Urban Planning','Waste'])].fillna(\"Strategy\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_resp_2020_data['Parent Section'].unique()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_resp_2020_data['Section'].unique()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plt.style.use('seaborn-white')\n\n# Create a data frame of CDP Region counts \nparent_section_counts = cities_resp_2020_data['Parent Section'].value_counts()\n\n\n# Get the figure and the axes (or subplots)\nfig, (ax0, ax1) = plt.subplots(nrows=1, ncols=2, sharey=True,figsize=(15, 5))\n\n\nparent_section_counts.plot(kind='bar', ax= ax0)\nax0.set(title = 'Parent Section', xlabel='' , ylabel = '# Rows')\n\n#plt.style.use('ggplot')\n# Create a data frame of CDP Region counts \ncdp_region_counts = cities_resp_2020_data['CDP Region'].value_counts()\n\n\n\ncdp_region_counts.plot(kind='bar', ax= ax1);\nax1.set(title = 'CDP Region', xlabel='' , ylabel = '')\n\n\n\n# Title the figure\nfig.suptitle('', fontsize=20, fontweight='bold');\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"#### Observations\n\n**Top 3 Parent Sections** are :-\n\nCity-wide Emissions\n\nEmissions Reduction\n\nClimate Hazards and Vulnerability\n\n**Top 3 CDP Regions** are :-\n\nNorth America\n\nLatin America\n\nEurope\n"},{"metadata":{"trusted":true},"cell_type":"code","source":"\n\n# Create a data frame of CDP Region counts \nsection_counts = cities_resp_2020_data['Section'].value_counts()\nprint(section_counts)\n\n\n\nprint(section_counts.index.values)\nprint(section_counts.values)\n\n# Get the figure and the axes (or subplots)\nfig, (ax0) = plt.subplots(nrows=1, ncols=1, sharey=True,figsize=(15, 5))\n\n\nsection_counts.plot(kind='bar', ax= ax0)\nax0.set(title = '', xlabel='Section' , ylabel = '# Rows')\n\n\n\n\n# Title the figure\nfig.suptitle('Frequency Counts', fontsize=14, fontweight='bold');\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"#### Observations\n\n**Top 5 Sections** are :-\n\nCity-wide GHG Emissions Data\n\nTransport\n\nClimate Hazards \n\nMitigation Actions\n\nMitigation Target Setting\n\nAdaptation Actions"},{"metadata":{"trusted":true},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_resp_2020_df1 = cities_resp_2020_data.groupby(['Parent Section','CDP Region'])['Organization'].aggregate('count').unstack()\ncities_resp_2020_df1\n\n\n\ncities_resp_2020_df2 = cities_resp_2020_df1.fillna(0)\ncities_resp_2020_df2\n\n#Side-by-side bar plot\n\n# Get the figure and the axes (or subplots)\nfig, ax = plt.subplots(figsize=(10, 5))\n\n# Using dataframe's plot\ncities_resp_2020_df2.plot(kind='bar', ax=ax, fontsize=10, colormap=plt.cm.coolwarm_r, grid=False)\n\nax.set(xlabel='Parent Section' , ylabel = 'Number of Rows',title='By each CDP Region in 2020')\n\n\n\nfig.suptitle('Frequency of Parent Section', fontsize=14, fontweight='bold');","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"####Top 3 CDP Regions\n\nFor all Parent Section (Adaptation, City-wide Emissions etc), \n\n**North America** is **leading**\n\n\nFollowed by **Latin America**  or **Europe**.\n\n "},{"metadata":{"trusted":true},"cell_type":"code","source":"#### Cities Responses Data Set 2019\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# read in all our data\ncities_resp_2019_data = pd.read_csv(\"/kaggle/input/cdp-unlocking-climate-solutions/Cities/Cities Responses/2019_Full_Cities_Dataset.csv\")\n                            \ncities_resp_2019_data_dict = pd.read_csv(\"/kaggle/input/cdp-unlocking-climate-solutions/Cities/Cities Responses/Full_Cities_Response_Data_Dictionary.csv\")\n\n# set seed for reproducibility\nnp.random.seed(0) \n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_resp_2019_data.shape","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"This table has .4 million 86 thousand something rows (half of 2020)"},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_resp_2019_data.info()\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_resp_2019_data.head()\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_resp_2019_data.columns","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"display_columns_uniqvals(cities_resp_2019_data)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_resp_2019_data['CDP Region'].unique()\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_resp_2019_data['Parent Section'].unique()\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_resp_2019_df1 = cities_resp_2019_data.groupby(['Parent Section','CDP Region'])['Organization'].aggregate('count').unstack()\ncities_resp_2019_df1\n\n\n\ncities_resp_2019_df2 = cities_resp_2019_df1.fillna(0)\ncities_resp_2019_df2\n\n#Side-by-side bar plot\n\n# Get the figure and the axes (or subplots)\nfig, ax = plt.subplots(figsize=(10, 5))\n\n# Using dataframe's plot\ncities_resp_2019_df2.plot(kind='bar', ax=ax, fontsize=10, colormap=plt.cm.coolwarm_r, grid=False)\n\nax.set(xlabel='Parent Section' , ylabel = 'Number of Rows',title='By CDP Region in 2019')\n\nfig.suptitle('Frequency of Parent Section', fontsize=14, fontweight='bold');","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"#### Cities Responses Data Set 2018\n"},{"metadata":{"trusted":true},"cell_type":"code","source":"# read in all our data\ncities_resp_2018_data = pd.read_csv(\"/kaggle/input/cdp-unlocking-climate-solutions/Cities/Cities Responses/2018_Full_Cities_Dataset.csv\")\n                            \ncities_resp_2018_data_dict = pd.read_csv(\"/kaggle/input/cdp-unlocking-climate-solutions/Cities/Cities Responses/Full_Cities_Response_Data_Dictionary.csv\")\n\n# set seed for reproducibility\nnp.random.seed(0) ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_resp_2018_data.shape","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"This table has .1 million 86 thousand something rows (One eighth of 2020 and One fourth of 2019) rows."},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_resp_2018_data.info()\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_resp_2018_data.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_resp_2018_data.columns","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"display_columns_uniqvals(cities_resp_2018_data)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_resp_2018_data['CDP Region'].unique()\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_resp_2018_data['CDP Region'].unique().size","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_resp_2018_data['Parent Section'].unique()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_resp_2018_data['Parent Section'].unique().size","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_resp_2018_df1 = cities_resp_2018_data.groupby(['Parent Section','CDP Region'])['Organization'].aggregate('count').unstack()\ncities_resp_2018_df1\n\n\n\ncities_resp_2018_df2 = cities_resp_2018_df1.fillna(0)\ncities_resp_2018_df2\n\n#Side-by-side bar plot\n\n# Get the figure and the axes (or subplots)\nfig, ax = plt.subplots(figsize=(15, 5))\n\n# Using dataframe's plot\ncities_resp_2018_df2.plot(kind='bar', ax=ax, fontsize=10, colormap=plt.cm.coolwarm_r, grid=False)\n\nax.set(xlabel='Parent Section' , ylabel = 'Number of Rows',title='By each CDP Region in 2018')\n\nfig.suptitle('Frequency of Parent Section', fontsize=14, fontweight='bold');","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"####**Top 3 Parent Sections** are :-\n\n**Strategy**\n   **North America** is leading, followed by **Europe** and then **Latin America**\n\n\n**Climate Hazards**\n    **North America** is leading, followed by **Latin America** and then **Europe**\n\n**Hazards and Adaptation**\n    **North America** is leading, followed by **Europe** and then **Latin America**"},{"metadata":{"trusted":true},"cell_type":"code","source":"# Get the figure and the axes (or subplots)\nfig, (ax0, ax1) = plt.subplots(nrows=2, ncols=1, sharex=True,figsize=(15,10))\n\n\ncities_resp_2020_df2.plot(kind='barh', ax=ax0, fontsize=10, colormap=plt.cm.coolwarm_r, grid=False,legend=None)\nax0.set(xlabel='' , ylabel = '',title='Year 2020')\n\n\ncities_resp_2019_df2.plot(kind='barh', ax=ax1, fontsize=10, colormap=plt.cm.coolwarm_r, grid=False)\nax1.set(xlabel='Parent Section' , ylabel = '',title='Year 2019')\n\n#cities_resp_2018_df2.plot(kind='bar', ax=ax2, fontsize=10, colormap=plt.cm.coolwarm_r, grid=False,legend=None)\n#ax2.set(xlabel='Parent Section' , ylabel = 'Number of Rows',title=' Year 2018')\n\n\nfig.suptitle('Frequency of Parent Section by CDP Region (2020 & 2019)', fontsize=14, fontweight='bold');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Get the figure and the axes (or subplots)\nfig, (ax0, ax1) = plt.subplots(nrows=2, ncols=1,sharex=True, figsize=(15, 10))\n\n\ncities_resp_2020_df2.plot(kind='barh', ax=ax0, fontsize=10, colormap=plt.cm.coolwarm_r, grid=False,legend=None)\nax0.set(xlabel='' , ylabel = '',title='Year 2020')\n\n\ncities_resp_2019_df2.plot(kind='barh', ax=ax1, fontsize=10, colormap=plt.cm.coolwarm_r, grid=False)\nax1.set(xlabel='#Rows' , ylabel = '',title='Year 2019')\n\n#cities_resp_2018_df2.plot(kind='bar', ax=ax2, fontsize=10, colormap=plt.cm.coolwarm_r, grid=False,legend=None)\n#ax2.set(xlabel='Parent Section' , ylabel = 'Number of Rows',title=' Year 2018')\n\n\nfig.suptitle('Frequency of Parent Section by CDP Region (2020 & 2019)', fontsize=14, fontweight='bold');","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"#### Increase from 2019 to 2020\n\n**Top 3 Parent Sections** which show increase are\n\n\n\nCity-wide emissions, \n\nEmission Reduction and \n\nOpportuntites"},{"metadata":{"trusted":true},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Get the figure and the axes (or subplots)\nfig, (ax0, ax1, ax2) = plt.subplots(nrows=3, ncols=1, sharex=True,figsize=(15, 10))\n\n\ncities_resp_2020_df2.plot(kind='bar', ax=ax0, fontsize=10, colormap=plt.cm.coolwarm_r, grid=False,legend=None)\nax0.set(xlabel='Parent Section' , ylabel = 'Number of Rows',title='Year 2020')\n\n\ncities_resp_2019_df2.plot(kind='bar', ax=ax1, fontsize=10, colormap=plt.cm.coolwarm_r, grid=False)\nax1.set(xlabel='Parent Section' , ylabel = 'Number of Rows',title='Year 2019')\n\ncities_resp_2018_df2.plot(kind='bar', ax=ax2, fontsize=10, colormap=plt.cm.coolwarm_r, grid=False,legend=None)\nax2.set(xlabel='Parent Section' , ylabel = 'Number of Rows',title=' Year 2018')\n\n\nfig.suptitle('Year wise Frequency of Parent Section by CDP Region', fontsize=14, fontweight='bold');","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Observations\n\nThere are **more number of Parent Sections in 2018** as compared to 2019 and 2020.\n\nThis needs to be explored further."},{"metadata":{},"cell_type":"markdown","source":"### Missing Data\n\nHow many missing data points do we have?"},{"metadata":{"trusted":true},"cell_type":"code","source":"# get the number of missing data points per column\nmissing_cities_resp_2020_count = cities_resp_2020_data.isnull().sum()\n\n# look at the # of missing points in the first ten columns\nmissing_cities_resp_2020_count[0:17]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"markdown","source":"What percentage of the values in our dataset were missing?"},{"metadata":{"trusted":true},"cell_type":"code","source":"# how many total missing values do we have?\ntotal_cells = np.product(cities_resp_2020_data.shape)\ntotal_missing = missing_cities_resp_2020_count.sum()\n\n# percent of data that is missing\npercent_missing = (total_missing/total_cells) * 100\nprint(percent_missing)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# look at the # of missing points in the first ten columns\nmissing_cities_resp_2020_count[0:10]","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Parent Section has .2 million 5 thousand something missing values."},{"metadata":{"trusted":true},"cell_type":"code","source":"# look at the # of missing points in the next remaining columns\nmissing_cities_resp_2020_count[10:18]","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Column Name has             28 thousand plus something missing\n\nRow Name has         .4 million 64 thousand plus something missing\n\nResponse Answer has  .1 million 66 thousand plus something missing\n\nComments  has        .8 million 49 thousand plus something missing\n\nFile Name   has      .8 million 67 thousand plus something missing\n\n"},{"metadata":{},"cell_type":"markdown","source":"### Regarding Missing Values"},{"metadata":{},"cell_type":"markdown","source":"Parent Section has Nan values at several places. \nValues of Section from corresponding rows can be copied to Parent Section."},{"metadata":{"trusted":true},"cell_type":"markdown","source":"This is a great place to read over the dataset documentation."},{"metadata":{},"cell_type":"markdown","source":"# Conclusion\nThis is Work In Progress. Thanks for reading."}],"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}