{"cells":[{"metadata":{"trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"!pip install geopandas\n!pip install pyshp\n!pip install shapely\n!pip install plotly-geo","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"

CDP: Unlocking Climate Solutions

\n
Developing a methodology for calculating KPIs that relate to the environmental and social issues that are discussed in the CDP survey data.
\n
\n\n
Table of Contents
\n"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"%%HTML\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"# standard libs\nimport os\nimport pandas as pd\nimport numpy as np\nimport matplotlib.pyplot as plt\nimport re\nimport json\n\nprint(os.getcwd()) #returns current working directory of a process","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"\n
Loading Data
\nLets look into dataset one by one.First lets gather details about cities datasets on both responses and disclosure of cities\n"},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true},"cell_type":"code","source":"# import cities response dataset \ncities_res_2020 = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Cities/Cities Responses/2020_Full_Cities_Dataset.csv\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_res_2020.head().T","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"\n
Question based on CDP questionaries by countries and cities
\nNow we try to answer some quesions using this dataset, in orderto undestand the data deeply\n
\n\n"},{"metadata":{},"cell_type":"markdown","source":"\n
contribution based on CDP Region
"},{"metadata":{"trusted":true},"cell_type":"code","source":"#Combining dataset for year 2018, 2019,2020 to compare\ncities_res_2020=pd.read_csv('../input/cdp-unlocking-climate-solutions/Cities/Cities Responses/2020_Full_Cities_Dataset.csv')\ncities_res_2019=pd.read_csv('../input/cdp-unlocking-climate-solutions/Cities/Cities Responses/2019_Full_Cities_Dataset.csv')\ncities_res_2018=pd.read_csv('../input/cdp-unlocking-climate-solutions/Cities/Cities Responses/2018_Full_Cities_Dataset.csv')\n\ncities_resp = pd.concat([cities_res_2020, cities_res_2019, cities_res_2018])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#number of organisation count\ncountry_count = cities_resp[['Organization', 'Country', 'CDP Region','Year Reported to CDP']]\\\n .groupby(['Country', 'CDP Region','Year Reported to CDP']).count().\\\n sort_values(by = ['Organization'],ascending = False)\\\n .reset_index()\\\n .rename(columns={'Organization' : 'num_orgs'})\ncountry_count.head(10)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"#replacing some misspelled words to match external flag image api\ncountry_count['Country']= country_count['Country'].replace({'Taiwan, Greater China':'Taiwan, Province of China'})\ncountry_count['Country']= country_count['Country'].replace({'China, Hong Kong Special Administrative Region':'Hong Kong Special Administrative Region'})\ncountry_count['Country']= country_count['Country'].replace({'Bolivia (Plurinational State of)':'Bolivia, Plurinational State of'})\ncountry_count['Country']= country_count['Country'].replace({'Democratic Republic of the Congo':'Republic of the Congo'})\ncountry_count['Country']= country_count['Country'].replace({'Venezuela (Bolivarian Republic of)':'Venezuela, Bolivarian Republic of'})","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"# adding two letter abbreviation to countries\nimport pycountry\ndef country_codes(country_name):\n return pycountry.countries.search_fuzzy(country_name)[0].alpha_2 \n\ncountry_count['codes']=country_count.apply(lambda x: country_codes(x['Country']), axis=1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"country_count.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"\ncdp_reg_count = country_count[country_count['Year Reported to CDP']==2020].groupby(['CDP Region'])['num_orgs'].sum().rename(\"count\").sort_values(ascending = False)\ncountry_2020=country_count[country_count['Year Reported to CDP']==2020]\nprint(cdp_reg_count)\n\ngroup_names=list(country_count['CDP Region'].unique())\nprint(group_names)\ngroup_size=list(cdp_reg_count/cdp_reg_count.sum() *100)\nprint(group_size)\n\n# Create colors\na, b, c=[plt.cm.Blues, plt.cm.gist_heat, plt.cm.Greens]\n\n# First Ring (outside)\nfig, ax = plt.subplots()\nax.axis('equal')\nmypie, _ = ax.pie(group_size, radius=2.5, labels=group_names, colors=[b(0.1),b(0.2),b(0.4),b(0.6),b(0.7),b(0.8), b(0.9),b(2.0)] )\nplt.setp( mypie, width=1.0, edgecolor='white')\n \n \n# show it\nplt.show()\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"\n
what are the countries contributed the most ?
\n"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"import requests\nfrom io import BytesIO\nfrom matplotlib.offsetbox import OffsetImage, AnnotationBbox\n# Create colors\na, b, c=[plt.cm.Blues, plt.cm.gist_heat, plt.cm.Greens]\ncountry_2020=country_count[country_count['Year Reported to CDP']==2020]\ntop5_countries=country_2020.iloc[:5]\n\ndef offset_image(x, y, label, bar_is_too_short, ax):\n response = requests.get(f'https://www.countryflags.io/{label}/flat/64.png')\n img = plt.imread(BytesIO(response.content))\n im = OffsetImage(img, zoom=0.65)\n im.image.axes = ax\n x_offset = -25\n if bar_is_too_short:\n x = 0\n ab = AnnotationBbox(im, (x, y), xybox=(x_offset, 0), frameon=False,\n xycoords='data', boxcoords=\"offset points\", pad=0)\n ax.add_artist(ab)\n\n\n#for 2nd map\nlabels = list(top5_countries['codes'])\ncolors = [ a(1.2),a(0.8),a(0.6),a(0.4),a(0.1)]\nvalues = list(top5_countries['num_orgs']/country_2020['num_orgs'].sum() *100)\n\nheight = 0.9\nplt.barh(y=labels, width=values, height=height, color=colors, align='center', alpha=1.0)\nmax_value = max(values)\nfor i, (label, value) in enumerate(zip(labels, values)):\n offset_image(value, i, label, bar_is_too_short=value < max_value / 10, ax=plt.gca())\nplt.subplots_adjust(left=0.15)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"\n
Countries and CDP region over the years
\n\n"},{"metadata":{"trusted":true},"cell_type":"code","source":"import plotly.express as px\n\n#country_count[\"Year Reported to CDP\"] = country_count[\"Year Reported to CDP\"].astype(str)\nfig = px.treemap(country_count, \n path=['Year Reported to CDP','Country'], \n values='num_orgs',\n color='CDP Region'#color_discrete_sequence= px.colors.sequential.Plasma_r##color='CDP Region'\n )\nfig.update_layout(uniformtext=dict(minsize=28))\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"

using this above interactive treemap we can get the information about the country participation over the years and could see the number of organization paricipated increase or discrease over the year. click on the year or country to expand the view.

\nfor example:\n
    \n
  1. Denmark has 5,853 organization responded in 2018 and it increased to 7,394 in 2019 and further more in 2020 as 20,697 orgs
    2. \n
  2. India has 1,924 organization responded in 2018 and it increased to 7,577 in 2019 but in 2020 only 2,512 organization responded.
    3. \n
"},{"metadata":{},"cell_type":"markdown","source":"\n
CDP Questionaries and countries and cities responses
\n\n"},{"metadata":{"trusted":true},"cell_type":"code","source":"# Filling the missing values in Response answer column as No Response\ncities_resp['Response Answer'] = cities_resp['Response Answer'].fillna('No Response')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"cities_resp['Parent Section'].unique()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-output":true},"cell_type":"code","source":"no_info=['Question not applicable','No Response']# for checking the responses \nques_responded=cities_resp[cities_resp[\"Response Answer\"].str.contains(('|'.join(no_info)))==0] [[\"Account Number\",\"Organization\",\"Year Reported to CDP\", \"Country\",\"Parent Section\",\"Section\",\"Question Name\",\"Question Number\",\"Response Answer\"]]\nques_responded.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"import squarify\nimport seaborn as sns\nplt.style.use('fivethirtyeight')\n\nfig = plt.figure(figsize=(25, 21))\nmarrimeko=ques_responded.Section.value_counts().to_frame().iloc[:10]\nax = fig.add_subplot(111, aspect=\"equal\")\nax = squarify.plot(sizes=marrimeko['Section'].values,label=marrimeko.index,\n color=sns.color_palette(\"YlGn\", 10), alpha=1)\nax.set_xticks([])\nax.set_yticks([])\nfig=plt.gcf()\nfig.set_size_inches(30,25)\nplt.title(\"Overall top 10 Sections with Responded Answer\", fontsize=18,fontweight=\"bold\")\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-output":true},"cell_type":"code","source":"not_applicable_info=['Question not applicable']# for checking the responses \nques_not_appl=cities_resp[cities_resp[\"Response Answer\"].str.contains(('|'.join(not_applicable_info)))==1] [[\"Account Number\",\"Organization\",\"Year Reported to CDP\", \"Country\",\"Parent Section\",\"Section\",\"Question Name\",\"Question Number\",\"Response Answer\"]]\nques_not_appl.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"import squarify\nimport seaborn as sns\nplt.style.use('fivethirtyeight')\n\nfig = plt.figure(figsize=(25, 21))\nmarrimeko=ques_not_appl.Section.value_counts().to_frame().iloc[:10]\nax = fig.add_subplot(111, aspect=\"equal\")\nax = squarify.plot(sizes=marrimeko['Section'].values,label=marrimeko.index,\n color=sns.color_palette(\"Spectral\", 18), alpha=1)\nax.set_xticks([])\nax.set_yticks([])\nfig=plt.gcf()\nfig.set_size_inches(30,25)\nplt.title(\"Overall top 10 Sections with Question not applicable\", fontsize=18,fontweight=\"bold\")\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-output":true},"cell_type":"code","source":"no_resp_info=['No Response']# for checking the responses \nques_no_resp=cities_resp[cities_resp[\"Response Answer\"].str.contains(('|'.join(no_resp_info)))==1] [[\"Account Number\",\"Organization\",\"Year Reported to CDP\", \"Country\",\"Parent Section\",\"Section\",\"Question Name\",\"Question Number\",\"Response Answer\"]]\nques_no_resp.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"no_res_count=ques_no_resp.groupby(['Parent Section', 'Section','Question Number','Year Reported to CDP']).size().reset_index(name='counts')\nno_res_count.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"import squarify\nimport seaborn as sns\nplt.style.use('fivethirtyeight')\n\nfig = plt.figure(figsize=(25, 21))\nmarrimeko=ques_no_resp.Section.value_counts().to_frame().iloc[:10]\nax = fig.add_subplot(111, aspect=\"equal\")\nax = squarify.plot(sizes=marrimeko['Section'].values,label=marrimeko.index,\n color=sns.color_palette(\"RdYlBu\", 18), alpha=1)\nax.set_xticks([])\nax.set_yticks([])\nfig=plt.gcf()\nfig.set_size_inches(30,25)\nplt.title(\" Overall top 10 Sections with No Response\", fontsize=18,fontweight=\"bold\")\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"\n
Percent of question answered by orgs
\n"},{"metadata":{"trusted":true,"_kg_hide-output":true,"_kg_hide-input":true},"cell_type":"code","source":"no_info=['Question not applicable','No Response']# for checking the responses \nno_responded=cities_resp[cities_resp[\"Response Answer\"].str.contains(('|'.join(no_info)))==1] [[\"Account Number\",\"Organization\",\"Year Reported to CDP\", \"Country\",\"Parent Section\",\"Section\",\"Question Name\",\"Question Number\",\"Response Answer\"]]\nno_responded.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"City_Helsinki=no_responded.loc[(no_responded[\"Country\"] == \"Norway\") ]\n#City_Helsinki=no_responded.loc[(no_responded[\"Organization\"] == \"City of Gretna, LA\") ]\nplt.style.use('seaborn-white')\n#plt.style.use('dark_background')\nResp_Helsinki = City_Helsinki.groupby(['Section','Response Answer']).size()\nResp_Helsinki.unstack().plot(kind='bar',stacked=True, colormap= 'Wistia', figsize=(13,11), grid=False)\nplt.title('Norway-Stacked Barplot of Section and their Response Answer distribution', fontsize=18,fontweight=\"bold\")\nplt.ylabel('Count of Response Answer in a particular Section', fontsize=16)\nplt.xlabel('Section', fontsize=16)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"\n
City disclosure
\n"},{"metadata":{"trusted":true},"cell_type":"code","source":"country_codes = pd.read_csv('../input/alpha-country-codes/Alpha__2_and_3_country_codes.csv', sep=';')\ncountry_codes = country_codes[['Country','Alpha-2 code','Alpha-3 code']]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"country_codes['Country'] = country_codes['Country'].replace({\"Korea, Democratic People's Republic of\": 'Republic of Korea'})\ncountry_codes['Country'] = country_codes['Country'].replace({'United Kingdom': 'United Kingdom of Great Britain and Northern Ireland'})\ncountry_codes['Country'] = country_codes['Country'].replace({'Taiwan':'Taiwan, Greater China'})\ncountry_codes['Country'] = country_codes['Country'].replace({'Russia':'Russian Federation'})\ncountry_codes['Country'] = country_codes['Country'].replace({'Palestinian Territory, Occupied':'State of Palestine'})\ncountry_codes['Country'] = country_codes['Country'].replace({'Congo, the Democratic Republic of the':'Democratic Republic of the Congo'})\ncountry_codes['Country'] = country_codes['Country'].replace({'Bolivia':'Bolivia (Plurinational State of)'})\ncountry_codes['Country'] = country_codes['Country'].replace({'Vietnam':'Viet Nam'})\ncountry_codes['Country'] = country_codes['Country'].replace({'Hong Kong':'Hong Kong Special Administrative Region'})\ncountry_codes['Country'] = country_codes['Country'].replace({'Venezuela':'Venezuela (Bolivarian Republic of)'})\ncountry_codes['Country'] = country_codes['Country'].replace({'United States Of America':'United States of America'})","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"cities_dis=pd.read_csv('../input/cdp-unlocking-climate-solutions/Cities/Cities Disclosing/2018_Cities_Disclosing_to_CDP.csv')\ncities_dis.head().T","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-output":true},"cell_type":"code","source":"cities_dis = pd.merge(cities_dis,country_codes, how = 'left', on = 'Country')\ncities_dis.head().T","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"import plotly.graph_objects as go\n\nfig = go.Figure(data=go.Choropleth(\n locations = cities_dis['Country'],\n locationmode='country names',\n z = cities_dis['Population'],\n text = cities_dis['Country'],\n colorscale = 'Blues',\n autocolorscale=False,\n reversescale=True,\n marker_line_color='darkgray',\n marker_line_width=0.5,\n colorbar_tickprefix = '',\n colorbar_title = 'Population',\n))\n\nfig.update_layout(\n title_text='2018 Population',\n geo=dict(\n showframe=False,\n showcoastlines=False,\n projection_type='equirectangular'\n )\n)\n\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"\n
CDC 500 Cities Census Tract Data
\n
\n

This dataset has vast majority of medical indicators such as cancer, asthma, tobacco, \"behavioral risk factors\", and more.These are relevant to social equity as it relates to the environment and also we can estimate the air/environment quality by asthma prevelance percent in each city/state.

\t\t\n"},{"metadata":{"trusted":true},"cell_type":"code","source":"CDC_Census =pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Supplementary Data/CDC 500 Cities Census Tract Data/500_Cities__Census_Tract-level_Data__GIS_Friendly_Format___2019_release.csv\")\nCDC_Census .head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Multiple state visualization"},{"metadata":{"trusted":true},"cell_type":"code","source":"import plotly.figure_factory as ff\n\nNE_states = ['CT', 'ME', 'MA', 'NH', 'RI', 'VT']\nCDC_Census_r =CDC_Census[CDC_Census['StateAbbr'].isin(NE_states)]\nCDC_Census_r ['FIPS'] =CDC_Census_r ['Place_TractID'].apply(lambda x: str(x).split(\"-\",1)[1][:5] )\n\nvalues = CDC_Census_r['CASTHMA_CrudePrev'].tolist()\nfips = CDC_Census_r['FIPS'].tolist()\n\ncolorscale = [\n 'rgb(68.0, 1.0, 84.0)',\n 'rgb(66.0, 64.0, 134.0)',\n 'rgb(38.0, 130.0, 142.0)',\n 'rgb(63.0, 188.0, 115.0)',\n 'rgb(216.0, 226.0, 25.0)'\n]\n\nfig = ff.create_choropleth(\n fips=fips, values=values,\n scope=['Connecticut', 'Maine', 'Massachusetts', 'New Hampshire', 'Rhode Island', 'Vermont'], county_outline={'color': 'rgb(15, 15, 55)', 'width': 0.5}, state_outline={'color': 'rgb(15, 15, 55)','width': 0.5},\n legend_title='asthma %', title=\"Asthma prevalence percentage on multiple state\"\n\n)\nfig.update_layout(\n legend_x = 0,\n annotations = {'x': -0.12, 'xanchor': 'left'}\n)\n\nfig.layout.template = None\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Single state visualization"},{"metadata":{"trusted":true},"cell_type":"code","source":"import plotly.figure_factory as ff\n\nCDC_Census_s = CDC_Census[CDC_Census['StateAbbr'] == 'FL']\nCDC_Census_s ['FIPS'] = CDC_Census_s ['Place_TractID'].apply(lambda x: str(x).split(\"-\",1)[1][:5] )\n\nvalues = CDC_Census_s['CASTHMA_CrudePrev'].tolist()\nfips = CDC_Census_s['FIPS'].tolist()\n\nendpts = list(np.mgrid[min(values):max(values):4j])\ncolorscale = [\"#030512\",\"#1d1d3b\",\"#323268\",\"#3d4b94\",\"#3e6ab0\",\n \"#4989bc\",\"#60a7c7\",\"#85c5d3\",\"#b7e0e4\",\"#eafcfd\"]\nfig = ff.create_choropleth(\n fips=fips, values=values, scope=['Florida'], show_state_data=True,\n colorscale=colorscale, binning_endpoints=endpts, round_legend_values=True,\n plot_bgcolor='rgb(229,229,229)',\n paper_bgcolor='rgb(229,229,229)',\n legend_title='ASTHMA %',\n county_outline={'color': 'rgb(255,255,255)', 'width': 0.5},\n state_outline={'color': 'rgb(15, 15, 55)','width': 0.5},\n title=\"Asthma percentage in various cities of Florida State\",\n exponent_format=True,\n)\nfig.layout.template = None\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"

Thanks a lot for having a look at this notebook.I Hope all you liked the notebook. Any suggestions and feedback are always welcome.

\n\nPlease Upvote this notebook as it encourages me in doing better."},{"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}