{"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":"## Imports","metadata":{"papermill":{"duration":0.041729,"end_time":"2020-10-09T16:48:20.217686","exception":false,"start_time":"2020-10-09T16:48:20.175957","status":"completed"},"tags":[]}},{"cell_type":"code","source":"# standard libs\nimport pandas as pd\nimport numpy as np\nimport matplotlib.pyplot as plt\nimport json\n\n# plotting libs\nimport seaborn as sns\n\n# geospatial libs\nfrom mpl_toolkits.basemap import Basemap\nfrom shapely.geometry import Polygon\nimport geopandas as gpd\nimport folium\nimport plotly.graph_objects as go\nimport plotly_express as px\n\n# set in line plotly \nfrom plotly.offline import init_notebook_mode;\ninit_notebook_mode(connected=True)\n\nprint(os.getcwd())","metadata":{"papermill":{"duration":3.774252,"end_time":"2020-10-09T16:48:24.035223","exception":false,"start_time":"2020-10-09T16:48:20.260971","status":"completed"},"tags":[],"execution":{"iopub.status.busy":"2022-05-01T20:12:34.10479Z","iopub.execute_input":"2022-05-01T20:12:34.105538Z","iopub.status.idle":"2022-05-01T20:12:34.117762Z","shell.execute_reply.started":"2022-05-01T20:12:34.105486Z","shell.execute_reply":"2022-05-01T20:12:34.116816Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Data\n\n### Import Data","metadata":{"papermill":{"duration":0.042097,"end_time":"2020-10-09T16:48:24.122194","exception":false,"start_time":"2020-10-09T16:48:24.080097","status":"completed"},"tags":[]}},{"cell_type":"code","source":"# import cities response df \ncities21_df = pd.read_csv(\"../input/cdp2021/2021_Cities_Water_Security_Risks.csv\")","metadata":{"papermill":{"duration":5.060271,"end_time":"2020-10-09T16:48:37.290298","exception":false,"start_time":"2020-10-09T16:48:32.230027","status":"completed"},"tags":[],"execution":{"iopub.status.busy":"2022-05-01T20:16:55.017624Z","iopub.execute_input":"2022-05-01T20:16:55.018339Z","iopub.status.idle":"2022-05-01T20:16:55.062392Z","shell.execute_reply.started":"2022-05-01T20:16:55.018297Z","shell.execute_reply":"2022-05-01T20:16:55.061242Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cities20_df = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Cities/Cities Responses/2020_Full_Cities_Dataset.csv\")\ncities19_df = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Cities/Cities Responses/2019_Full_Cities_Dataset.csv\")","metadata":{"papermill":{"duration":5.060271,"end_time":"2020-10-09T16:48:37.290298","exception":false,"start_time":"2020-10-09T16:48:32.230027","status":"completed"},"tags":[],"execution":{"iopub.status.busy":"2022-05-01T20:04:00.297484Z","iopub.execute_input":"2022-05-01T20:04:00.298063Z","iopub.status.idle":"2022-05-01T20:04:12.959668Z","shell.execute_reply.started":"2022-05-01T20:04:00.298014Z","shell.execute_reply":"2022-05-01T20:04:12.958553Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Calculations\n\n### Data Cleaning & EDA\n\n","metadata":{"papermill":{"duration":0.042535,"end_time":"2020-10-09T16:48:39.382282","exception":false,"start_time":"2020-10-09T16:48:39.339747","status":"completed"},"tags":[]}},{"cell_type":"code","source":"cities19_df[cities19_df['Parent Section']=='Water Security']['Question Name'].unique()","metadata":{"execution":{"iopub.status.busy":"2022-05-01T20:17:23.084003Z","iopub.execute_input":"2022-05-01T20:17:23.084657Z","iopub.status.idle":"2022-05-01T20:17:23.186078Z","shell.execute_reply.started":"2022-05-01T20:17:23.084619Z","shell.execute_reply":"2022-05-01T20:17:23.184748Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cities21_df=cities21_df\\\n    .rename(columns={'Account Name': 'City'})\ncities20_df = cities20_df[cities20_df['Question Number'] == '14.2a']\\\n    .rename(columns={'Organization': 'City'})\ncities20_df['Response Answer'] = cities20_df['Response Answer'].fillna('No Response')\ncities19_df = cities19_df[\n    cities19_df['Question Name'] == \n    'Please identify the risks to your city’s water supply as well as the timescale and level of risk.']\\\n    .rename(columns={'Organization': 'City'})\ncities19_df['Response Answer'] = cities19_df['Response Answer'].fillna('No Response')\n\ncities21_df.head()\n","metadata":{"execution":{"iopub.status.busy":"2022-05-01T20:17:30.893107Z","iopub.execute_input":"2022-05-01T20:17:30.893822Z","iopub.status.idle":"2022-05-01T20:17:31.210429Z","shell.execute_reply.started":"2022-05-01T20:17:30.893782Z","shell.execute_reply":"2022-05-01T20:17:31.209163Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df1=pd.crosstab(index=cities21_df['Water security risk drivers'], columns='count').sort_values(by='count',ascending=False)\ndf1.head()\ndf1","metadata":{"execution":{"iopub.status.busy":"2022-05-01T20:52:28.767816Z","iopub.execute_input":"2022-05-01T20:52:28.768183Z","iopub.status.idle":"2022-05-01T20:52:28.79925Z","shell.execute_reply.started":"2022-05-01T20:52:28.768152Z","shell.execute_reply":"2022-05-01T20:52:28.79818Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cities20_df.head()","metadata":{"execution":{"iopub.status.busy":"2022-05-01T22:18:59.45788Z","iopub.execute_input":"2022-05-01T22:18:59.458233Z","iopub.status.idle":"2022-05-01T22:18:59.483085Z","shell.execute_reply.started":"2022-05-01T22:18:59.458203Z","shell.execute_reply":"2022-05-01T22:18:59.482036Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"risk20=cities20_df.pivot_table(index=['Account Number', 'City', 'Row Number'],\n                                     columns='Column Name', \n                                     values='Response Answer',\n                                     aggfunc=lambda x: ' '.join(x)).reset_index()\nrisk20.head()","metadata":{"execution":{"iopub.status.busy":"2022-05-01T22:19:05.297641Z","iopub.execute_input":"2022-05-01T22:19:05.298015Z","iopub.status.idle":"2022-05-01T22:19:05.420215Z","shell.execute_reply.started":"2022-05-01T22:19:05.297985Z","shell.execute_reply":"2022-05-01T22:19:05.41917Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df2=pd.crosstab(index=risk20['Water security risk drivers'], columns='count').sort_values(by='count',ascending=False)","metadata":{"execution":{"iopub.status.busy":"2022-05-01T22:22:43.023297Z","iopub.execute_input":"2022-05-01T22:22:43.023708Z","iopub.status.idle":"2022-05-01T22:22:43.045335Z","shell.execute_reply.started":"2022-05-01T22:22:43.023671Z","shell.execute_reply":"2022-05-01T22:22:43.04455Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cities19_df.head()","metadata":{"execution":{"iopub.status.busy":"2022-05-01T20:20:03.989704Z","iopub.execute_input":"2022-05-01T20:20:03.990696Z","iopub.status.idle":"2022-05-01T20:20:04.022868Z","shell.execute_reply.started":"2022-05-01T20:20:03.990637Z","shell.execute_reply":"2022-05-01T20:20:04.02164Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"risk19=cities19_df.pivot_table(index=['Account Number', 'City', 'Row Number'],\n                                     columns='Column Name', \n                                     values='Response Answer',\n                                     aggfunc=lambda x: ' '.join(x)).reset_index()\nrisk19.head()","metadata":{"execution":{"iopub.status.busy":"2022-05-01T22:19:25.422339Z","iopub.execute_input":"2022-05-01T22:19:25.42286Z","iopub.status.idle":"2022-05-01T22:19:25.511941Z","shell.execute_reply.started":"2022-05-01T22:19:25.422828Z","shell.execute_reply":"2022-05-01T22:19:25.510799Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df3=pd.crosstab(index=risk19['Risks'], columns='count').sort_values(by='count',ascending=False)\ndf3","metadata":{"execution":{"iopub.status.busy":"2022-05-01T22:19:40.820176Z","iopub.execute_input":"2022-05-01T22:19:40.820614Z","iopub.status.idle":"2022-05-01T22:19:40.845105Z","shell.execute_reply.started":"2022-05-01T22:19:40.820576Z","shell.execute_reply":"2022-05-01T22:19:40.84418Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#2021\ndf11 = df1.reset_index()\ndf11.columns=['Water security risk drivers','number']\ndf11=df11[df11.number>50]\n","metadata":{"execution":{"iopub.status.busy":"2022-05-01T22:16:55.354909Z","iopub.execute_input":"2022-05-01T22:16:55.355312Z","iopub.status.idle":"2022-05-01T22:16:55.365399Z","shell.execute_reply.started":"2022-05-01T22:16:55.355266Z","shell.execute_reply":"2022-05-01T22:16:55.36442Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"g = sns.catplot( \n    data=df11, kind=\"bar\",x ='Water security risk drivers', y=\"number\",\n    ci=\"sd\", palette=\"icefire\", alpha=.6, height=6)\ng.set_xticklabels(rotation=80)\ng.set_axis_labels( \"Water security risk drivers\", \"Number of Responses\")","metadata":{"execution":{"iopub.status.busy":"2022-05-01T22:20:43.554989Z","iopub.execute_input":"2022-05-01T22:20:43.555395Z","iopub.status.idle":"2022-05-01T22:20:43.800589Z","shell.execute_reply.started":"2022-05-01T22:20:43.555355Z","shell.execute_reply":"2022-05-01T22:20:43.799687Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#2020\ndf21 = df2.reset_index()\ndf21.columns=['Water security risk drivers','number']\ndf21=df21[df21.number>50]","metadata":{"execution":{"iopub.status.busy":"2022-05-01T22:23:02.817132Z","iopub.execute_input":"2022-05-01T22:23:02.817871Z","iopub.status.idle":"2022-05-01T22:23:02.825292Z","shell.execute_reply.started":"2022-05-01T22:23:02.817829Z","shell.execute_reply":"2022-05-01T22:23:02.824357Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"g = sns.catplot( \n    data=df21, kind=\"bar\",x ='Water security risk drivers', y=\"number\",\n    ci=\"sd\", palette=\"icefire\", alpha=.6, height=6)\ng.set_xticklabels(rotation=80)\ng.set_axis_labels( \"Water security risk drivers\", \"Number of Responses\")","metadata":{"execution":{"iopub.status.busy":"2022-05-01T22:23:05.842721Z","iopub.execute_input":"2022-05-01T22:23:05.843424Z","iopub.status.idle":"2022-05-01T22:23:06.238815Z","shell.execute_reply.started":"2022-05-01T22:23:05.843385Z","shell.execute_reply":"2022-05-01T22:23:06.237824Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#2019\ndf31 = df3.reset_index()\ndf31.columns=['Water security risk drivers','number']\ndf31=df31[df31.number>50]","metadata":{"execution":{"iopub.status.busy":"2022-05-01T22:25:02.992361Z","iopub.execute_input":"2022-05-01T22:25:02.992745Z","iopub.status.idle":"2022-05-01T22:25:03.000734Z","shell.execute_reply.started":"2022-05-01T22:25:02.992713Z","shell.execute_reply":"2022-05-01T22:25:02.99967Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"g = sns.catplot( \n    data=df31, kind=\"bar\",x ='Water security risk drivers', y=\"number\",\n    ci=\"sd\", palette=\"icefire\", alpha=.6, height=6)\ng.set_xticklabels(rotation=80)\ng.set_axis_labels( \"Water security risk drivers\", \"Number of Responses\")","metadata":{"papermill":{"duration":0.103026,"end_time":"2020-10-09T16:48:58.063366","exception":false,"start_time":"2020-10-09T16:48:57.96034","status":"completed"},"tags":[],"execution":{"iopub.status.busy":"2022-05-01T22:25:16.87627Z","iopub.execute_input":"2022-05-01T22:25:16.876726Z","iopub.status.idle":"2022-05-01T22:25:17.12731Z","shell.execute_reply.started":"2022-05-01T22:25:16.876689Z","shell.execute_reply":"2022-05-01T22:25:17.126051Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}