{"cells":[{"metadata":{},"cell_type":"markdown","source":"This notebook highlights the inequalities between different regions in terms of access to a water supply service.\n\nCDP data are combined with the Water Poverty Index (WPI) to show areas where drinking water issues are critical.\n\nThe WPI is an index that takes into account water resources, access to water, water use and the environment. See more [here](http://ihp-wins.unesco.org/)"},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"import pandas as pd\nimport numpy as np\nimport matplotlib.pyplot as plt\nimport seaborn as sns\nimport plotly_express as px\n\nCities_disclosing_df = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Cities/Cities Disclosing/2020_Cities_Disclosing_to_CDP.csv\")\nCities_disclosing_df.drop(['Year Reported to CDP','Organization', 'Reporting Authority', 'Access', 'First Time Discloser', 'Population Year','Last update'], axis = 1, inplace = True)\n\nMPI = pd.read_csv('../input/water-poverty-index-wpi/Water poverty index.csv') \nMPI.drop(['the_geom','FID','OBJECTID','ISO','UNESCOMEMB'], axis = 1, inplace = True)\nMPI.columns = ['Country', 'WPI']\nMPI['Country'] =MPI['Country'].replace({\"Cote d'Ivoire\":\"Côte d'Ivoire\"})\nMPI['Country'] =MPI['Country'].replace({\"Palestine\":\"State of Palestine\"})\nMPI.loc[MPI.shape[0], 'Country'] = 'Taiwan, Greater China'\nMPI.loc[MPI.shape[0]-1, 'WPI'] = 56.6\nMPI.loc[MPI.shape[0], 'Country'] = 'Gibraltar'\nMPI.loc[MPI.shape[0]-1, 'WPI'] = -999.0\nMPI.loc[MPI.shape[0], 'Country'] = 'China, Hong Kong Special Administrative Region'\nMPI.loc[MPI.shape[0]-1, 'WPI'] = 56.6\n\nmerge_df = pd.merge(Cities_disclosing_df, MPI)\nmerge_df['Population'][548]=1383432134.0/1000\nmerge_df['Population'][396]=399724000.0/100\nmerge_df.sort_values(by = 'Population', inplace = True)\n\ncities_df = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Cities/Cities Responses/2020_Full_Cities_Dataset.csv\")\ncities_df['Response Answer'] = cities_df['Response Answer'].fillna('No Response')\n\ndef convert_float(x):\n    try :\n        x = float(x)\n        return x\n    except :\n        return x\n    \nQ141 = cities_df[cities_df['Question Number'] == '14.1']\nQ141['Response Answer'] = Q141['Response Answer'].apply(convert_float)\nQ141.drop(['Questionnaire', 'Year Reported to CDP', 'CDP Region', 'Parent Section', 'Section','Question Number', 'Question Name', 'Column Number', 'Column Name','Row Number', 'Row Name', 'Comments', 'File Name','Last update'], axis = 1, inplace = True)\n# Replace response with errors\nQ141['Response Answer'][857449]=95.52\nQ141['Response Answer'][246477]=96.\nQ141.drop(Q141[Q141['Response Answer'] == 'No Response'].index, inplace = True)\n\nMerge_q141 = pd.merge(Q141, merge_df)\nMerge_q141 = Merge_q141.drop(Merge_q141[Merge_q141['Response Answer'] == 'No Response'].index)\nMerge_q141['Response Answer'].astype(np.float)\nMerge_q141.sort_values(by = 'Response Answer', inplace = True)\n\ndf = Merge_q141[Merge_q141['WPI'] != -999.0]\ndf = df[df['Response Answer'] != 'No Response']","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-output":false,"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"df.sort_values(by = 'CDP Region', inplace = True)\ndf['CDP Region']=pd.Categorical(df['CDP Region'])\n \n# create data\nx = df['Response Answer']\ny = df['WPI']\nz = df['Population']/5000\n\n# plot\nplt.rcParams[\"legend.title_fontsize\"] = \"large\"\nplt.rcParams[\"font.size\"] = 20\nplt.rcParams[\"font.sans-serif\"] = 'Arial'\n\nplt.figure(figsize=(22, 10))\nplt.scatter(x, y, s=z, c=df['CDP Region'].cat.codes, cmap=plt.cm.get_cmap('RdYlBu', 8), alpha=0.6, edgecolors=\"grey\", linewidth=2)\n\n# Add titles (main and on axis)\nplt.xlabel(\"City's population with a water supply service (%)\")\nplt.ylabel(\"Water poverty index (WPI) of the city's country\")\nplt.title(\"Inequalities of regions regarding access to water supply service\")\n\n\n# This function formatter will replace integers with target names\nformatter = plt.FuncFormatter(lambda val, loc: df['CDP Region'].unique()[val])\n\n# We must be sure to specify the ticks matching our target names\nplt.colorbar(ticks=[0, 1, 2, 3, 4, 5, 6, 7], format=formatter);\n\n# Set the clim so that labels are centered on each block\nplt.clim(-0.5, 7.5)\n\n\nfor area in [750, 1500, 3000]:\n    plt.scatter([], [], c='k', alpha=0.4, s=area,label='     '+str(area/3000*2)+ ' M')\nplt.legend(scatterpoints=1, frameon=False, labelspacing=2,loc='lower left',bbox_to_anchor=(0.05, 0.1, 0.5, 0.5), title='Population')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The region with the greatest lack of overall water supply service is Africa.\n\nAre the cities most concerned taking appropriate measures to improve their water supply service?"},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"Q143 = cities_df[cities_df['Question Number'] == '14.3']\nQ143w = Q143[Q143['Response Answer']=='Inadequate or ageing water supply infrastructure']\n\nQ143_actions = pd.DataFrame()\n\nfor index in Q143w.index:\n    Row_Number = Q143['Row Number'][index]\n    Account_Number = Q143['Account Number'][index]\n    df1 = Q143[Q143['Account Number']==Account_Number]\n    df1 = df1[df1['Row Number']==Row_Number]\n    Q143_actions = pd.concat([Q143_actions,df1])\n    \nQ143_actions = Q143_actions[Q143_actions['Response Answer']=='Investment in existing water supply infrastructure']\n\nQ143_status_action = pd.DataFrame()\nfor index in Q143_actions.index:\n    Row_Number = Q143['Row Number'][index]\n    Account_Number = Q143['Account Number'][index]\n    df2 = Q143[Q143['Account Number']==Account_Number]\n    df2 = df2[df2['Row Number']==Row_Number]\n    Q143_status_action = pd.concat([Q143_status_action,df2])\n    \nQ143_status_action1 = Q143_status_action[Q143_status_action['Response Answer']=='Scoping']\nQ143_status_action2 = Q143_status_action[Q143_status_action['Response Answer']=='Pre-feasibility study/impact assessment']\nQ143_status_action3 = Q143_status_action[Q143_status_action['Response Answer']=='Pre-implementation']\nQ143_status_action4 = Q143_status_action[Q143_status_action['Response Answer']=='Implementation']\nQ143_status_action5 = Q143_status_action[Q143_status_action['Response Answer']=='Implementation complete but not in operation']\nQ143_status_action6 = Q143_status_action[Q143_status_action['Response Answer']=='Operation']\nQ143_status_action7 = Q143_status_action[Q143_status_action['Response Answer']=='Monitoring and reporting']\n\nmerge_df['Risks'] = 'Not identified as a risk'\nfor index in merge_df.index:\n    for j in Q143w.index:\n        Account_Number = Q143w['Account Number'][j]\n        if merge_df['Account Number'][index]==Account_Number:\n            for k in Q143_actions.index :\n                action = Q143_actions['Account Number'][k]\n                if merge_df['Account Number'][index]==action:\n                    merge_df['Risks'][index]='No investment in existing water supply infrastructure'\n                    for h in Q143_status_action1.index:\n                        status =Q143_status_action1['Account Number'][h]\n                        if merge_df['Account Number'][index]==status: \n                            merge_df['Risks'][index]='Scoping'\n                    for h in Q143_status_action2.index:\n                        status =Q143_status_action2['Account Number'][h]\n                        if merge_df['Account Number'][index]==status: \n                            merge_df['Risks'][index]='Pre-feasibility study/impact assessment'\n                    for h in Q143_status_action3.index:\n                        status =Q143_status_action3['Account Number'][h]\n                        if merge_df['Account Number'][index]==status:\n                            merge_df['Risks'][index]='Pre-implementation'\n                    for h in Q143_status_action4.index:\n                        status =Q143_status_action4['Account Number'][h]\n                        if merge_df['Account Number'][index]==status:\n                            merge_df['Risks'][index]='Implementation'\n                    for h in Q143_status_action5.index:\n                        status =Q143_status_action5['Account Number'][h]\n                        if merge_df['Account Number'][index]==status:\n                            merge_df['Risks'][index]='Implementation complete but not in operation'\n                    for h in Q143_status_action6.index:\n                        status =Q143_status_action6['Account Number'][h]\n                        if merge_df['Account Number'][index]==status:\n                            merge_df['Risks'][index]='Operation'\n                    for h in Q143_status_action7.index:\n                        status =Q143_status_action7['Account Number'][h]\n                        if merge_df['Account Number'][index]==status: \n                            merge_df['Risks'][index]='Monitoring and reporting'\n                            \nmerge_df['Population'].fillna(0, inplace = True) \nmerge_df = merge_df[merge_df['Population']!=0]\n\nmerge_df['City Location'].fillna(0, inplace = True) \nmerge_df = merge_df[merge_df['City Location']!=0]\nmerge_df['longitude'] = merge_df['City Location']\nmerge_df['latitude'] = merge_df['City Location']\nfor i in merge_df.index:\n    merge_df['longitude'][i] = merge_df['City Location'][i].split()[1].replace('(','')\n    merge_df['latitude'][i] = merge_df['City Location'][i].split()[2].replace(')','')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"Fig_Q14_3 = px.scatter_geo(merge_df,lat = 'latitude',lon= 'longitude',size = 'Population',width=1200,height = 1000, color='Risks', hover_name=\"City\",projection=\"natural earth\",color_discrete_map={\n                'No investment in existing water supply infrastructure': \"#C4181C\",\n                'Scoping': \"#EC4B2E\",\n                'Pre-feasibility study/impact assessment': \"#FA9346\",\n                'Pre-implementation': \"#FBD374\",\n                'Implementation': \"#CBEC6E\",\n                'Implementation complete but not in operation': \"#A1D868\",\n                'Operation': \"#1A7E37\",\n                'Monitoring and reporting': \"#5992BE\",\n                'Not identified as a risk': \"#2E559B\"},\n                category_orders={'Risks':(\n                'No investment in existing water supply infrastructure',\n                'Scoping',\n                'Pre-feasibility study/impact assessment',\n                'Pre-implementation',\n                'Implementation',\n                'Implementation complete but not in operation',\n                'Operation',\n                'Monitoring and reporting',\n                'Not identified as a risk')\n                },  \n                hover_data = {'longitude':False,'latitude':False,'Country':True,'Population':True,'CDP Region':True,'Risks':True, 'WPI': True},\n                labels = {'Risks':'Status of action'})\nFig_Q14_3.update_layout(\n    title={\n        'text': \"Risk to urban water security\",\n        'y':0.85,\n        'x':0.35,\n        'xanchor': 'center',\n        'yanchor': 'top'},\n    legend_title={\n        'text': \"Inadequate or ageing water supply infrastructure\",\n        'side': \"top\"},\n    legend = {\n        'yanchor':\"top\",\n        'y':0.9,\n        'xanchor':\"left\",\n        'x':1},\n    font=dict(\n        family=\"Arial\",\n        size=12,\n        color= \"black\"),\n    autosize=False,\n    width=800,\n    height=400,\n)\nFig_Q14_3 .show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Some cities take water-related risks well into consideration.\nNevertheless, it seems that many cities underestimate the risks. Both cities with an inadequate water supply system and cities with a well-established but aging system are neglecting these issues."}],"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}