{"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 os\nimport pandas as pd\nimport numpy as np\nimport matplotlib.pyplot as plt\nimport re\nimport json\n\n# plotting libs\nimport seaborn as sns\n\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-02T13:07:03.180127Z","iopub.execute_input":"2022-05-02T13:07:03.180523Z","iopub.status.idle":"2022-05-02T13:07:04.048911Z","shell.execute_reply.started":"2022-05-02T13:07:03.180492Z","shell.execute_reply":"2022-05-02T13:07:04.048037Z"},"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\")\ncities20_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-02T13:07:06.217699Z","iopub.execute_input":"2022-05-02T13:07:06.218129Z","iopub.status.idle":"2022-05-02T13:07:17.825958Z","shell.execute_reply.started":"2022-05-02T13:07:06.218090Z","shell.execute_reply":"2022-05-02T13:07:17.824968Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cities19_df","metadata":{"execution":{"iopub.status.busy":"2022-05-02T13:07:28.553821Z","iopub.execute_input":"2022-05-02T13:07:28.554233Z","iopub.status.idle":"2022-05-02T13:07:28.602597Z","shell.execute_reply.started":"2022-05-02T13:07:28.554195Z","shell.execute_reply":"2022-05-02T13:07:28.601719Z"},"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-02T13:08:46.922075Z","iopub.execute_input":"2022-05-02T13:08:46.922804Z","iopub.status.idle":"2022-05-02T13:08:46.972494Z","shell.execute_reply.started":"2022-05-02T13:08:46.922750Z","shell.execute_reply":"2022-05-02T13:08:46.971492Z"},"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-02T13:08:49.113909Z","iopub.execute_input":"2022-05-02T13:08:49.114291Z","iopub.status.idle":"2022-05-02T13:08:49.247425Z","shell.execute_reply.started":"2022-05-02T13:08:49.114233Z","shell.execute_reply":"2022-05-02T13:08:49.246615Z"},"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(20)","metadata":{"execution":{"iopub.status.busy":"2022-05-02T13:08:51.662572Z","iopub.execute_input":"2022-05-02T13:08:51.662914Z","iopub.status.idle":"2022-05-02T13:08:51.689553Z","shell.execute_reply.started":"2022-05-02T13:08:51.662883Z","shell.execute_reply":"2022-05-02T13:08:51.688501Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df1.iloc[1:18,:].plot.pie(y='count',figsize=(6,6),legend=False)","metadata":{"execution":{"iopub.status.busy":"2022-05-02T13:08:54.260887Z","iopub.execute_input":"2022-05-02T13:08:54.261213Z","iopub.status.idle":"2022-05-02T13:08:54.448347Z","shell.execute_reply.started":"2022-05-02T13:08:54.261186Z","shell.execute_reply":"2022-05-02T13:08:54.447466Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cities20_df.head()","metadata":{"execution":{"iopub.status.busy":"2022-05-02T13:08:56.824501Z","iopub.execute_input":"2022-05-02T13:08:56.825195Z","iopub.status.idle":"2022-05-02T13:08:56.846865Z","shell.execute_reply.started":"2022-05-02T13:08:56.825147Z","shell.execute_reply":"2022-05-02T13:08:56.845679Z"},"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-02T13:08:59.219363Z","iopub.execute_input":"2022-05-02T13:08:59.219709Z","iopub.status.idle":"2022-05-02T13:08:59.312161Z","shell.execute_reply.started":"2022-05-02T13:08:59.219677Z","shell.execute_reply":"2022-05-02T13:08:59.311175Z"},"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)\ndf2.info()\ndf2.head(20)","metadata":{"execution":{"iopub.status.busy":"2022-05-02T13:09:02.063233Z","iopub.execute_input":"2022-05-02T13:09:02.063605Z","iopub.status.idle":"2022-05-02T13:09:02.095155Z","shell.execute_reply.started":"2022-05-02T13:09:02.063569Z","shell.execute_reply":"2022-05-02T13:09:02.094357Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df2.iloc[2:19,:].plot.pie(y='count',figsize=(6,6),legend=False)","metadata":{"execution":{"iopub.status.busy":"2022-05-02T13:09:05.270628Z","iopub.execute_input":"2022-05-02T13:09:05.271016Z","iopub.status.idle":"2022-05-02T13:09:05.401352Z","shell.execute_reply.started":"2022-05-02T13:09:05.270980Z","shell.execute_reply":"2022-05-02T13:09:05.400360Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cities19_df.head()","metadata":{"execution":{"iopub.status.busy":"2022-05-02T13:09:08.023566Z","iopub.execute_input":"2022-05-02T13:09:08.023936Z","iopub.status.idle":"2022-05-02T13:09:08.046261Z","shell.execute_reply.started":"2022-05-02T13:09:08.023902Z","shell.execute_reply":"2022-05-02T13:09:08.045273Z"},"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-02T13:09:14.988144Z","iopub.execute_input":"2022-05-02T13:09:14.988626Z","iopub.status.idle":"2022-05-02T13:09:15.056104Z","shell.execute_reply.started":"2022-05-02T13:09:14.988595Z","shell.execute_reply":"2022-05-02T13:09:15.055071Z"},"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.info()\ndf3","metadata":{"execution":{"iopub.status.busy":"2022-05-02T13:09:17.186374Z","iopub.execute_input":"2022-05-02T13:09:17.186919Z","iopub.status.idle":"2022-05-02T13:09:17.214753Z","shell.execute_reply.started":"2022-05-02T13:09:17.186885Z","shell.execute_reply":"2022-05-02T13:09:17.213793Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df3.iloc[1:18,:].plot.pie(y='count',figsize=(6,6),legend=False)","metadata":{"execution":{"iopub.status.busy":"2022-05-02T13:09:19.634341Z","iopub.execute_input":"2022-05-02T13:09:19.634748Z","iopub.status.idle":"2022-05-02T13:09:19.736526Z","shell.execute_reply.started":"2022-05-02T13:09:19.634707Z","shell.execute_reply":"2022-05-02T13:09:19.735439Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df21=df1\ndf20=df2\ndf19=df3","metadata":{"execution":{"iopub.status.busy":"2022-05-02T13:09:25.508806Z","iopub.execute_input":"2022-05-02T13:09:25.509445Z","iopub.status.idle":"2022-05-02T13:09:25.513205Z","shell.execute_reply.started":"2022-05-02T13:09:25.509402Z","shell.execute_reply":"2022-05-02T13:09:25.512553Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df21=df21.rename(columns={'count': 'count21'})\ndf20=df20.rename(columns={'count': 'count20'})\ndf19=df19.rename(columns={'count': 'count19'})","metadata":{"execution":{"iopub.status.busy":"2022-05-02T13:09:27.068297Z","iopub.execute_input":"2022-05-02T13:09:27.068888Z","iopub.status.idle":"2022-05-02T13:09:27.074929Z","shell.execute_reply.started":"2022-05-02T13:09:27.068847Z","shell.execute_reply":"2022-05-02T13:09:27.074173Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{"execution":{"iopub.status.busy":"2022-05-02T13:09:31.902582Z","iopub.execute_input":"2022-05-02T13:09:31.903188Z","iopub.status.idle":"2022-05-02T13:09:31.915962Z","shell.execute_reply.started":"2022-05-02T13:09:31.903152Z","shell.execute_reply":"2022-05-02T13:09:31.914739Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"","metadata":{}},{"cell_type":"code","source":"risk21=df21.index.values\nrisk21count=df21.values\nrisk20=df20.index.values\nrisk20count=df20.values\nrisk19=df19.index.values\nrisk19count=df19.values","metadata":{"execution":{"iopub.status.busy":"2022-05-02T13:21:27.491117Z","iopub.execute_input":"2022-05-02T13:21:27.491772Z","iopub.status.idle":"2022-05-02T13:21:27.497718Z","shell.execute_reply.started":"2022-05-02T13:21:27.491721Z","shell.execute_reply":"2022-05-02T13:21:27.497033Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\n\n\n \n# Create the pandas DataFrame\ndf_21 = pd.DataFrame(risk21, columns = ['RISK'])\n\ndf_20 = pd.DataFrame(risk20, columns = ['RISK'])\n\ndf_19 = pd.DataFrame(risk19, columns = ['RISK'])\n \n# print dataframe.\ndf_21['count21']=risk21count\n\ndf_20['count20']=risk20count\n\ndf_19['count19']=risk19count\n","metadata":{"execution":{"iopub.status.busy":"2022-05-02T13:23:34.259894Z","iopub.execute_input":"2022-05-02T13:23:34.260576Z","iopub.status.idle":"2022-05-02T13:23:34.270314Z","shell.execute_reply.started":"2022-05-02T13:23:34.260525Z","shell.execute_reply":"2022-05-02T13:23:34.269413Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_19","metadata":{"execution":{"iopub.status.busy":"2022-05-02T13:23:44.703432Z","iopub.execute_input":"2022-05-02T13:23:44.703911Z","iopub.status.idle":"2022-05-02T13:23:44.715631Z","shell.execute_reply.started":"2022-05-02T13:23:44.703878Z","shell.execute_reply":"2022-05-02T13:23:44.714407Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"outer_merged = pd.merge(df_21, df_20, how=\"outer\", on=[\"RISK\"])","metadata":{"execution":{"iopub.status.busy":"2022-05-02T13:24:06.148709Z","iopub.execute_input":"2022-05-02T13:24:06.149063Z","iopub.status.idle":"2022-05-02T13:24:06.160101Z","shell.execute_reply.started":"2022-05-02T13:24:06.149031Z","shell.execute_reply":"2022-05-02T13:24:06.159261Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"outer_merged ","metadata":{"execution":{"iopub.status.busy":"2022-05-02T13:24:23.780216Z","iopub.execute_input":"2022-05-02T13:24:23.780589Z","iopub.status.idle":"2022-05-02T13:24:23.797177Z","shell.execute_reply.started":"2022-05-02T13:24:23.780555Z","shell.execute_reply":"2022-05-02T13:24:23.795943Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"outer_merged = pd.merge(outer_merged , df_19, how=\"outer\", on=[\"RISK\"])","metadata":{"execution":{"iopub.status.busy":"2022-05-02T13:24:55.790290Z","iopub.execute_input":"2022-05-02T13:24:55.790641Z","iopub.status.idle":"2022-05-02T13:24:55.800158Z","shell.execute_reply.started":"2022-05-02T13:24:55.790612Z","shell.execute_reply":"2022-05-02T13:24:55.799283Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"outer_merged","metadata":{"execution":{"iopub.status.busy":"2022-05-02T13:25:03.941296Z","iopub.execute_input":"2022-05-02T13:25:03.941901Z","iopub.status.idle":"2022-05-02T13:25:03.957759Z","shell.execute_reply.started":"2022-05-02T13:25:03.941865Z","shell.execute_reply":"2022-05-02T13:25:03.956946Z"},"trusted":true},"execution_count":null,"outputs":[]}]}