{"cells":[{"metadata":{},"cell_type":"markdown","source":"In this notebook, we explore a section of the data provided under 'City-Response'.\n\nCDP is currently collaborating with cities across the world. So we will try to put up some visualizations, on the data contribution and few aspects, covered in the survey, from regional perspective ."},{"metadata":{"trusted":true},"cell_type":"code","source":"from glob import glob\nimport pandas as pd\nimport numpy as np\nfrom tqdm import tqdm\nfrom difflib import SequenceMatcher\nimport plotly.graph_objects as go\nimport plotly.express as px\nfrom plotly.subplots import make_subplots\n\nimport seaborn as sns\nimport plotly as ply\nimport altair as alt\nimport matplotlib.pyplot as plt\n\nimport warnings\nwarnings.filterwarnings('ignore')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"\n\nfrom IPython.core.display import display, HTML\ndisplay(HTML(\"<style>div.output_scroll { height: 4444em,width: 4444em; }</style>\"))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"data_2018 = glob('/kaggle/input/cdp-unlocking-climate-solutions/*/*/2018*.csv')+glob('/kaggle/input/cdp-unlocking-climate-solutions/*/*2018*.csv')\ndata_2019 = glob('/kaggle/input/cdp-unlocking-climate-solutions/*/*/2019*.csv')+glob('/kaggle/input/cdp-unlocking-climate-solutions/*/*2019*.csv')\ndata_2020 = glob('/kaggle/input/cdp-unlocking-climate-solutions/*/*/2020*.csv')+glob('/kaggle/input/cdp-unlocking-climate-solutions/*/*2020*.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_response_files = glob('/kaggle/input/cdp-unlocking-climate-solutions/*/*/*Full_Cities_Dataset.csv')\ncities_disclosing_files = glob('/kaggle/input/cdp-unlocking-climate-solutions/*/*/*Cities_Disclosing_to_CDP.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_disclosing = pd.concat([pd.read_csv(file) for file in cities_disclosing_files])\ncities_response = pd.concat([pd.read_csv(file) for file in cities_response_files])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"corporate_climate_change_disclosing_files = glob('/kaggle/input/cdp-unlocking-climate-solutions/*/*/*/*Corporates_Disclosing_to_CDP_Climate_Change*.csv')\ncorporate_water_security_disclosing_files = glob('/kaggle/input/cdp-unlocking-climate-solutions/*/*/*/*Corporates_Disclosing_to_CDP_Water_Security*.csv')\ncorporate_climate_change_response_files = glob('/kaggle/input/cdp-unlocking-climate-solutions/*/*/*/*Full_Climate_Change_Dataset*.csv')\ncorporate_water_security_response_files = glob('/kaggle/input/cdp-unlocking-climate-solutions/*/*/*/*Full_Water_Security_Dataset*.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"corporate_climate_change_disclosing = pd.concat([pd.read_csv(file) for file in corporate_climate_change_disclosing_files])\ncorporate_water_security_disclosing = pd.concat([pd.read_csv(file) for file in corporate_water_security_disclosing_files])\ncorporate_climate_change_response = pd.concat([pd.read_csv(file) for file in corporate_climate_change_response_files])\ncorporate_water_security_response = pd.concat([pd.read_csv(file) for file in corporate_water_security_response_files])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"worldcities = pd.read_csv('/kaggle/input/world-cities/worldcities.csv').rename(columns={'country':'Country'})\nworldcities['Country'].replace(['Bolivia','Hong Kong','Côte D’Ivoire','Congo (Kinshasa)',\n                               'Korea, South','Moldova','Russia','West Bank','Taiwan','United Kingdom','Tanzania',\n                                'United States','Venezuela','Vietnam'                                \n                               ],\n                               \n                               \n                              ['Bolivia (Plurinational State of)',\n 'China, Hong Kong Special Administrative Region',                              \n \"Côte d'Ivoire\",\n 'Democratic Republic of the Congo',\n 'Republic of Korea',\n 'Republic of Moldova',\n 'Russian Federation',\n 'State of Palestine',\n 'Taiwan, Greater China',\n 'United Kingdom of Great Britain and Northern Ireland',\n 'United Republic of Tanzania',\n 'United States of America',\n 'Venezuela (Bolivarian Republic of)',\n 'Viet Nam'],\n                              inplace=True)\n\nworldcities = worldcities.merge(cities_response[['Country','CDP Region']].drop_duplicates(),on='Country',how='left')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"world_data = worldcities[['city','CDP Region']][(~worldcities['CDP Region'].isna())&(worldcities['population']>100000)].drop_duplicates()\nworld_data = pd.DataFrame(world_data.groupby(['CDP Region'])['city'].count()).rename(columns={'city':'Total City Count'}).reset_index()\n\n\nworld_bars = alt.Chart(world_data).mark_bar(size=17).encode(\n    y=alt.Y('CDP Region'),\n    x=alt.X('Total City Count', stack='zero'),\n    opacity=alt.value(0.2),\n\n                    )\nworld_bars.height = 300\nworld_bars.width = 650\n\n\ncdp_data = cities_response[['Organization','CDP Region']].drop_duplicates()\ncdp_data = pd.DataFrame(cdp_data.groupby(['CDP Region'])['Organization'].count()).rename(columns={'Organization':'Total Organization Count'}).reset_index()\n\n\ncdp_bars = alt.Chart(cdp_data).mark_bar(size=17).encode(\n    y=alt.Y('CDP Region'),\n    x=alt.X('Total Organization Count', stack='zero'),\n    opacity=alt.value(0.8),\n                    )\n\ncdp_bars.height = 300\ncdp_bars.width = 650\n\n\n\nperc_data = world_data.merge(cdp_data,on='CDP Region')\nperc_data['Percentage of participation'] = (perc_data['Total Organization Count']/perc_data['Total City Count'])*100\nperc_data['text'] = perc_data['Percentage of participation'].apply(lambda x: 'Percentage of participation : '+str(np.round(x,2)))\n\ntext = alt.Chart(perc_data).mark_text(dx=78, dy=1, color='#0e7735',size=10).encode(\n    y=alt.Y('CDP Region'),\n    x=alt.X('Total City Count', stack='zero'),\n    detail='Percentage of participation:N',\n    text=alt.Text('text'),\n    )\n\nlegend = alt.Chart().mark_bar().encode(color=alt.Color('Legend:N', \n              scale=alt.Scale(domain=['Total City Count',\n                                      'Total Organization Count'],\n                              range=[ '#bed8ec','#3181bd']),\n                    legend=alt.Legend(orient='bottom')))\n\nlegend.height = 300\nlegend.width = 650\n\ncomb = (cdp_bars+world_bars+text).properties(title={\n    'text':['Percentage of participation at city level',\n           '(Considers only the countries, present in CDP data / cities having 100K+ population in region)'],\n     'subtitle':['Total City Count: Total no. of cities with 100K+ population at the region ,    Total Organization Count : Total no. of city-based organizations collaborating with CDP at the region'],\n           })\n    \n\n\n\n(comb+legend)\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Percentage of participation at city level:\n#### The visualization above tells us about the participation level from each CDP region.\nThis considers number of cities participating at the region currently and total no. of the cities with 100k+ population at the region.\nThe percentage number shows where CDP can improve collaboration and by how much. \n\nThe chart shows, \n* 'Latin america' and 'North america' are considerably ahead of other regions. \n* 'East Asia' and 'South and West Asia' reginos are having pretty low participation."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"data = cities_response[(cities_response['Question Number']=='2.0')][['Response Answer','CDP Region','Organization','Year Reported to CDP']].drop_duplicates()\ndata.sort_values(by=['Organization','Year Reported to CDP'],inplace=True)\nfinal_data = pd.DataFrame(columns=[ 'Organization',\n       'Year Reported to CDP'],index=np.arange(len(data['Organization'].unique())*3))\nfinal_data['Year Reported to CDP'] = [2018]*len(data['Organization'].unique())+[2019]*len(data['Organization'].unique())+[2020]*len(data['Organization'].unique())\nfinal_data['Organization'] = list(data['Organization'].unique())+list(data['Organization'].unique())+list(data['Organization'].unique())\nfinal_data = final_data.merge(data[['Organization','Year Reported to CDP','Response Answer']],how='left',on=['Organization','Year Reported to CDP'])\nfinal_data = final_data.merge(data[['Organization','CDP Region']].drop_duplicates(),how='left',on=['Organization'])\n\nfinal_data.sort_values(by=['Organization','Year Reported to CDP'],inplace=True)\nfinal_data.reset_index(drop=True,inplace=True)\n\nidx = final_data[(final_data['Response Answer'].isna())&(final_data['Year Reported to CDP']==2018)].index.values\nfinal_data['Response Answer'].iloc[idx] = 'Do not know'\n\nidx = final_data[(final_data['Response Answer'].isna())&(final_data['Year Reported to CDP']==2019)].index.values\nfinal_data['Response Answer'].iloc[idx] = final_data['Response Answer'].iloc[idx-1].values\n\nidx = final_data[(final_data['Response Answer'].isna())&(final_data['Year Reported to CDP']==2020)].index.values\nfinal_data['Response Answer'].iloc[idx] = final_data['Response Answer'].iloc[idx-1].values\n\ndata2 = pd.DataFrame((final_data.groupby(['CDP Region','Year Reported to CDP'])['Response Answer'].value_counts().sort_index()/final_data.groupby(['CDP Region','Year Reported to CDP'])['CDP Region'].count().sort_index())*100)\ndata2.reset_index(inplace=True)\ndata2.rename(columns={0:'Percentage','Response Answer':'Status of climate change risk and vulnerability assessment'},inplace=True)\ndata2.loc[-1] = ['Africa',2018,'Not intending to undertake',0.0]\ndata2.index = data2.index + 1\ndata2.loc[-1] = ['Africa',2018,'Intending to undertake in the next 2 years',0.0]\ndata2.index = data2.index + 1\ndata2.sort_values(by=['Status of climate change risk and vulnerability assessment'],ascending=False,inplace=True)\n# alt.Legend(orient='top-left')\n\nbars = alt.Chart(data2).mark_bar(size=18).encode(\n    y=alt.Y('CDP Region'),\n    x=alt.X('Percentage', stack='zero'),\n    color=alt.Color('Status of climate change risk and vulnerability assessment',\\\n                    legend=alt.Legend(orient='bottom'),\n                    scale=alt.Scale(\n            domain=['Yes', 'Not intending to undertake', 'No',\n       'Intending to undertake in the next 2 years',\n       'Intending to undertake in future', 'In progress', 'Do not know'],\n                        range=['#46ab5e','orange','#dc2a25','#bbccdd','teal','#d18954','#5fb0c0']\n                    ))\n).properties(title={\n    'text':['Status of climate risk and vulnerability assessment for each CDP region (in percentage)',\n           '(Includes all the organizations reported till 2020)'],\n    })\n\nbars.height = 460\nbars.width = 650\n\n\ntext = alt.Chart(data2).mark_text(dx=-10, dy=1, color='white',size=12).encode(\n    y=alt.Y('CDP Region'),\n    x=alt.X('Percentage', stack='zero'),\n    detail='Status of climate change risk and vulnerability assessment:N',\n    text=alt.Text('Percentage', format='2d'),\n)\n\ntext.height = 460\ntext.width = 650\n\nyear_slider = alt.binding_range(name='Year Reported to CDP',min=2018, max=2020, step=1)\nslider_selection = alt.selection_single(bind=year_slider, \n                                        fields=['Year Reported to CDP'], \n                                        name=\"Year Reported to CDP\",\n                                        init={'Year Reported to CDP':2018})\n\ncomb = bars+text\n\nfilter_year = comb.add_selection(\n    slider_selection\n).transform_filter(\n    slider_selection\n)\n\nfilter_year","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Status of climate change risk and vulnerability assessment:\n\n#### This shows, where the participating cities are in terms of assessing climate change risk and vulnerability in each CDP region.\nThe participation status , as defined by CDP, are: \n1. Yes \n2. Not intending to take \n3. No \n4. Intending to take in next 2 years\n5. Intending to undertake in future\n6. In progress\n7. Do not know \n\n\n* **Numbers in the chart are percentage of all cities , that participated over last 3 years, in each segment of that particular year.\nThe slider in the chart will show you, how the numbers have changed over the years. Any city that did not participate in a previous year has been marked under 'Do not know' segment for that year.**\n\n\n* **The final status on year 2020, shows though the participations across asian regions are low, almost 80% of the participating cities of 'South and West Asia','East Asia' and 'Southeast Asia and Oceania' have conducted a climate change risk and vulnerability assessment.**"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"def get_regional_summary(info,feature):\n    info = info.groupby(['CDP Region','Year Reported to CDP'])[feature].describe()\n    return info.reset_index()\n\n\nlocation_values= pd.read_csv('/kaggle/input/comp-dataset/location_values.csv')\n\n# select the data \n\n# climate hazard related responses\n# 2018 reponse options are not in sync with 2019 and 2020.\ndata = cities_response[((cities_response['Question Number']=='2.1')&(cities_response['Year Reported to CDP'].isin([2019,2020])))\n               |((cities_response['Question Number']=='2.2a')&(cities_response['Year Reported to CDP']==2018))\n               ][['Year Reported to CDP',\n                  'CDP Region',\n                  'Column Name',\n                  'Country',\n                  'Question Name',\n                  'Organization',\n                  'Response Answer']].drop_duplicates().reset_index(drop=True)\n\n\ndata['Column Name'].replace(['Current consequence of hazard','Magnitude of impact'],'Current magnitude of hazard',inplace=True)\ndata['Question Name'] = 'Most significant climate hazards faced by city'\n\nCDP_enlisted_major_impacts = list(data[data['Column Name']=='Social impact of hazard overall']['Response Answer'].value_counts()[:30].index.values[:11])\n\n\n\nsocial_impact_info = pd.pivot_table(data[['Year Reported to CDP','CDP Region','Country','Organization','Response Answer','Column Name']][(data['Column Name']=='Social impact of hazard overall')&(data['Response Answer'].isin(CDP_enlisted_major_impacts))],index=['Organization','CDP Region','Country','Year Reported to CDP'],columns='Response Answer',aggfunc='count',fill_value=0.0)\nsocial_impact_info['Total no. of impacted social aspects'] = social_impact_info.sum(axis=1)\nsocial_impact_info.reset_index(inplace=True)\nsocial_impact_info['percentage of CDP enlisted social aspects impacted'] = (100*social_impact_info['Total no. of impacted social aspects']/len(CDP_enlisted_major_impacts)).round(2)\n\n\n\naffected_services = data[data['Column Name'].isin(['Most relevant assets / services affected overall',\n                               'Top three assets/ services affected' \n                              ])]\naffected_services.dropna(inplace=True)\naffected_services.sort_values(by=['Year Reported to CDP','Organization'],inplace=True)\nCDP_enlisted_sectors = list(affected_services['Response Answer'].value_counts()[:20].index.values)\n\naffected_services_info = pd.pivot_table(affected_services[['Year Reported to CDP','CDP Region','Country','Organization','Response Answer','Column Name']][affected_services['Response Answer'].isin(CDP_enlisted_sectors)],index=['Year Reported to CDP','CDP Region','Country','Organization'],columns='Response Answer',aggfunc='count',fill_value=0.0)\naffected_services_info['Total no. of impacted services'] = affected_services_info.sum(axis=1)\naffected_services_info.reset_index(inplace=True)\naffected_services_info['percentage of CDP enlisted services impacted'] = np.round(100*(affected_services_info['Total no. of impacted services']/len(CDP_enlisted_sectors)),4)\n\n\n\nsocial_impact_summary = get_regional_summary(social_impact_info,'percentage of CDP enlisted social aspects impacted')\naffected_services_summary = get_regional_summary(affected_services_info,'percentage of CDP enlisted services impacted')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"\nCDP begins the survey on cities, with a set of questions on public services/assests,that are crucial to keep city operations up and running. Questions assess, how these services are affected by climate related hazards. They also survey, how it has impacted different aspects of our society. In  below two sections, we explore data submitted by the cities, on these two categories."},{"metadata":{},"cell_type":"markdown","source":"# **Exploration of impacted Services/Assets**\n\n\n1. **Global Severity Map of Impacted Services** :  This global map shows , of the CDP enlisted services and assets , how many are impacted in each city as per last reported status, in terms of percentage.Colorbar beside the map shows serverity scale. A brighter spot represents a city with higher severity.\n\n2. **Regional average of city-based impact% over last 3 years** : This section shows in each CDP region, how the city based impact percentages have been, over the last 3 years. The numbers that you will see on hovering over the chart, will tell you that, ***European region and North American regions are showing a jump from 35% to 40% and 43% to 48% respectively between 2019 and 2020. Other regions are showing around 3% jump***. But in reality, it could be higher, as apart from Europe and North America(USA & Canada) , other regions have less particiaption on 2020. Guess, we all have reasons to be busy with Covid!\n\n\n\n3. **Trendline of global average impact%** : Trendline shows how the impact percentage has grown over the years globally.\n\n4. **Service-wise analysis : % of cities having the corresponding sector affected** : This section shows the majorly hit services/assets in the cities, across the world.\nWater supply & sanitation are topping the chart, with 81% cities across the world reporting an impact. Which also explains why public health is the next one.\nThis in a way shows why CDP has chosen to do explicit survey on water security.\n\n5. **Region-wise analysis on - \"Water supply & sanitation\" and \"Public health\"** : When we explore, how each region is doing on these two most affected services, we can see a ***strong pattern of obvious correlation in all the region, except, 'Middle East'***(highlighted bars). All the middle-eastern cities have reported to have water supply & sanitation impacted. However, impact on public heath services is least , when compared with other regions. \n\n\n**Severity value and other details of a city can be found by hovering over the city in the map**"},{"metadata":{"scrolled":false,"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"\naffected_services_info.columns = ['Year Reported to CDP', 'CDP Region','Country',\n       'Organization','Commercial',\n       'Community & culture',\n       'Education',\n       'Emergency management',\n       'Emergency services', 'Energy',\n       'Environment',\n       'Environment, biodiversity, forestry',\n       'Food & agriculture',\n       'Industrial',\n       'Information & communications technology',\n       'Land use planning',\n       'Law & order', 'Public health',\n       'Residential',\n       'Society / community & culture',\n       'Tourism', 'Transport',\n       'Waste management',\n       'Water supply & sanitation',\n       'Total no. of impacted services',\n       'percentage of CDP enlisted services impacted']\n\naffected_services_info = affected_services_info.merge(location_values,on='Organization',how='left')\n\n\n\naffected_services_info['text'] = affected_services_info[['Organization','Country','percentage of CDP enlisted services impacted']].\\\napply(lambda x: 'Country : '+x[1]+', Organization : '+x[0]+', % of impact: '+str(x[2]),axis=1)\n\naffected_services_last_status = affected_services_info.sort_values(by=['Year Reported to CDP','Organization']).drop_duplicates(subset=['Organization'],keep='last')\n\nsmb = go.Scattermapbox(name='severity map',\n        lon = affected_services_last_status['long'],\n        lat = affected_services_last_status['lat'],\n        text = affected_services_info[\"percentage of CDP enlisted services impacted\"],\n        mode = 'markers',\n#         locationmode='USA-states',\n        hovertext=affected_services_last_status['text'],\n        marker = dict(\n#             sizemin = 5,\n#             sizemode='area',\n            size = 10,\n            opacity = 0.8,\n            reversescale = True,\n            autocolorscale = False,\n            colorscale = 'Reds',\n            cmin = 0,\n            color = affected_services_last_status[\"percentage of CDP enlisted services impacted\"],\n            cmax = affected_services_last_status[\"percentage of CDP enlisted services impacted\"].max(),\n            colorbar_title=\"Impact% - Severity scale\",\n            colorbar_thickness=10,\n            colorbar_title_side='right',\n            colorbar_len=.3,\n            colorbar_xanchor='left',\n            colorbar_yanchor='bottom',\n            colorbar_y=0.7\n        ))\n\nregional_mean = [go.Bar(x=affected_services_summary[\"CDP Region\"][affected_services_summary['Year Reported to CDP']==year],\n                        y=affected_services_summary[\"mean\"][affected_services_summary['Year Reported to CDP']==year],\n             showlegend=True,orientation='v',textfont_size=2,\n             hovertext='Mean % of CDP enlisted services affected across region, on year - '+str(year),\n#                         text=affected_services_summary[\"mean\"].round(2)\n                        \n             ) for year in [2018,2019,2020]]\n\nregional_scatter = go.Scatter(x=[2018,2019,2020], name='Trendline',\n                               y=affected_services_info.groupby(['Year Reported to CDP'])[\"percentage of CDP enlisted services impacted\"].mean(),\n                               mode='lines+markers')    \n\nservices_temp = (affected_services_last_status[CDP_enlisted_sectors].sum(axis=0)/affected_services_last_status.shape[0]).sort_values(ascending=False)*100\nservices_temp_trace = go.Bar(x=services_temp.index.values, y=services_temp.values,\n             showlegend=True,text=np.round(services_temp.values,2),textposition='auto'\n             )\n\nservices_temp2 = affected_services_last_status.groupby(['CDP Region'])[CDP_enlisted_sectors].sum()/affected_services_last_status.groupby(['CDP Region'])[CDP_enlisted_sectors].count()\nwater_temp = services_temp2['Water supply & sanitation'].sort_values(ascending=False)*100\nhealth_temp = services_temp2['Public health'][water_temp.index.values]*100\nwater_health_temp_trace = [go.Bar(x=water_temp.index.values, y=water_temp.values,\n             showlegend=True,text=np.round(water_temp.values,2),textposition='auto',\n            hovertext=['Water Supply & Sanitation : % of affected cities in the region : '+str(i) for i in np.round(water_temp.values,2)],orientation='v'\n             ),\n go.Bar(x=health_temp.index.values, y=health_temp.values,text=np.round(health_temp.values,2),\n             showlegend=True,textposition='auto',\n        hovertext=['Public health : % of affected cities in the region : '+str(i) for i in np.round(health_temp.values,2)],orientation='v'\n             )]\n\n\nwater_health_temp_trace[0].marker.color = [\"rgb(255,247,188)\"]+[\"rgb(165,15,21)\"]*7\nwater_health_temp_trace[1].marker.color = [\"rgb(255,247,188)\"]+[\"rgb(35,139,69)\"]*7   \nwater_health_temp_trace[0].width=.3\nwater_health_temp_trace[1].width=.3\n\n\nfig1 = make_subplots(\n    rows=4, cols=2,\n    specs=[\n           [{\"type\": \"scattermapbox\",'colspan':2},None],\n           [{\"type\": \"bar\",'colspan':1},{\"type\": \"scatter\",'colspan':1}],\n#            [{\"type\": \"histogram2d\"}],\n           [{\"type\": \"bar\",'colspan':2},None],\n           [{\"type\": \"bar\",'colspan':2},None] \n          ],\n    subplot_titles=['<b>Global Severity map of impacted services</b>',\n                    '<b>Regional average of city-based impact% over last 3 years','<b>  Trendline of global average impact%</b>',\n                    '<b>Service-wise analysis : % of cities having the corresponding sector affected</b>',\n                    '<b>Region-wise analysis on - \"Water supply & sanitation\" and \"Public health\"</b>'\n                    ],\n#     x_title='Exploration of affected services across the world'.upper(),\n    vertical_spacing=0.14,horizontal_spacing=0.2,\n    row_heights=[0.5,0.15,0.10,0.3],\n        \n#     column_widths=[1,1,1]\n    \n                    )\n\nfig1.add_trace(smb,row=1,col=1)\nfig1.update_layout(\n        mapbox_style=\"carto-darkmatter\",\n#         title = dict(text='% of CDP enlisted services affected',x=0.5,y=.97),\n        height=1600,\n        width=900,\n        )\n\nfig1.update_layout(\n    template=\"plotly_dark\",\n    margin=dict(r=25, t=25, b=140, l=100),\n    showlegend=False,\n    bargroupgap=0.25,\n    bargap=0.25\n)\n\n\nfig1.append_trace(regional_mean[0],row=2,col=1)\nfig1.append_trace(regional_mean[1],row=2,col=1)\nfig1.append_trace(regional_mean[2],row=2,col=1)\n\nfig1.append_trace(regional_scatter,row=2,col=2)\n# regional_scatter.update(xticks=[2018,2019,2020])\n\n\nfig1.append_trace(services_temp_trace,row=3,col=1)\n\nfig1.append_trace(water_health_temp_trace[0],row=4,col=1)\nfig1.append_trace(water_health_temp_trace[1],row=4,col=1)\n\n\n# fig1.update_histogram(barmode='overlay')\n# Reduce opacity to see both histograms\nfig1.update_traces(opacity=0.65)\nfig1.update_layout(font_color=\"rgb(199,233,180)\",font_family='sans-serif')\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Exploration of Impact on Social Aspects\n\n1. **Global Severity map of impacted social aspects**:  This global map shows , of the CDP enlisted social aspects , how many are impacted in each city as per last reported status, in terms of percentage.Colorbar beside the map shows serverity scale. A brighter spot on the map represents a city with higher severity.\n\n2. **Regional average of city-based impact% over last 2 years** : This section shows in each CDP region, how the city based impact percentages have been over the last 2 years, as 2018 data doesn't include this. Again European region and North American region(USA & Canada) are showing around 5-7% jump in impact. For other regions, although the survey number are show around 3% jump, it is most likely because of lesser participation on 2020 in those regions.\n\n3. **Trendline : global avg. impact% on social aspects** : Trendline shows approximately 3.5% jump in percentage of impacted social aspects globally. But as discussed in the previous section, actual numbers could be more coherent with that of European and North American regions. \n\n4. **Service-wise analysis : % of cities having the corresponding social aspect affected** : 'Increased risk to already vullnerable population' and 'increased demand for public services' and 'increased demand for health services' are top 3 most impacted social aspects, globally.\n\n5. **Region-wise: \"vulnerable population\\'s risk\" & \"demand for public services\"** : Regional exploration, shows with a figure of around 92%, no. of  cities in the both African and North American region are reporting risk to vulnerable section. European cities are showing the least number on this.\nOn the contrary, increased public service demand is least in African cities and highest in North American and 'Southeast and Oceania' cities.\n\n\n**Severity value and other details of a city can be found by hovering over the city in the map**"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"\n\n\nsocial_impact_info.columns = ['Organization', 'CDP Region','Country',\n       'Year Reported to CDP','Fluctuating socio-economic conditions',\n        'Increased conflict and/or crime',\n        'Increased demand for healthcare services',\n        'Increased demand for public services',\n        'Increased incidence and prevalence of disease and illness',\n        'Increased resource demand',\n        'Increased risk to already vulnerable populations',\n        'Loss of tax base to support public services',\n        'Loss of traditional jobs',\n        'Migration from rural areas to cities',\n        'Population displacement',\n        'Total no. of impacted social aspects',\n        'percentage of CDP enlisted social aspects impacted']\n\nsocial_impact_info = social_impact_info.merge(location_values,on='Organization',how='left')\n\n\n\nsocial_impact_info['text'] = social_impact_info[['Organization','Country','percentage of CDP enlisted social aspects impacted']].\\\napply(lambda x: 'Country : '+x[1]+', Organization : '+x[0]+', % of impact: '+str(x[2]),axis=1)\n\n\nsocial_impact_last_status = social_impact_info.sort_values(by=['Year Reported to CDP','Organization']).drop_duplicates(subset=['Organization'],keep='last')\nsocial_impact_last_status[CDP_enlisted_major_impacts].sum(axis=0)/social_impact_last_status.shape[0]\n\n\nsmb = go.Scattermapbox(name='Severity map',\n        lon = social_impact_last_status['long'],\n        lat = social_impact_last_status['lat'],\n        text = social_impact_info[\"percentage of CDP enlisted social aspects impacted\"],\n        mode = 'markers',\n#         locationmode='USA-states',\n        hovertext=social_impact_last_status['text'],\n        marker = dict(\n#             sizemin = 5,\n#             sizemode='area',\n            size = 10,\n            opacity = 0.8,\n            reversescale = True,\n            autocolorscale = False,\n            colorscale = 'Reds',\n            cmin = 0,\n            color = social_impact_last_status[\"percentage of CDP enlisted social aspects impacted\"],\n            cmax = social_impact_last_status[\"percentage of CDP enlisted social aspects impacted\"].max(),\n            colorbar_title=\"Impact% - Severity scale\",\n            colorbar_thickness=10,\n            colorbar_title_side='right',\n            colorbar_len=.3,\n            colorbar_xanchor='left',\n            colorbar_yanchor='bottom',\n            colorbar_y=0.7\n        ))\n\nregional_mean = [go.Bar(x=social_impact_summary[\"CDP Region\"][social_impact_summary['Year Reported to CDP']==year],\n                        y=social_impact_summary[\"mean\"][social_impact_summary['Year Reported to CDP']==year],\n             showlegend=True,orientation='v',textfont_size=2,\n             hovertext='Mean % of CDP enlisted social aspects affected across region, on year - '+str(year),\n#                         text=social_impact_summary[\"mean\"].round(2)\n                        \n             ) for year in [2019,2020]]\n\nregional_scatter = go.Scatter(name='Trendline',\n                               x=[2019,2020], \n                               y=social_impact_info.groupby(['Year Reported to CDP'])[\"percentage of CDP enlisted social aspects impacted\"].mean(),\n                               mode='lines+markers')  \n\naspects_temp = (social_impact_last_status[CDP_enlisted_major_impacts].sum(axis=0)/social_impact_last_status.shape[0]).sort_values(ascending=False)*100\naspects_temp_trace = go.Bar(x=aspects_temp.index.values, y=aspects_temp.values,\n             showlegend=True,text=np.round(aspects_temp.values,2),textposition='auto'\n             )\n\nservices_temp2 = social_impact_last_status.groupby(['CDP Region'])[CDP_enlisted_major_impacts].sum()/social_impact_last_status.groupby(['CDP Region'])[CDP_enlisted_major_impacts].count()\nvul_temp = services_temp2['Increased risk to already vulnerable populations'].sort_values(ascending=False)*100\npublic_temp = services_temp2['Increased demand for public services'][vul_temp.index.values]*100\nvul_public_temp_trace = [go.Bar(x=vul_temp.index.values, y=vul_temp.values,\n             showlegend=True,text=np.round(vul_temp.values,2),textposition='auto',\n            hovertext=['Increased risk to already vulnerable populations: % of affected cities in the region : '+str(i) for i in np.round(water_temp.values,2)],orientation='v'\n             ),\n go.Bar(x=public_temp.index.values, y=public_temp.values,text=np.round(public_temp.values,2),\n             showlegend=True,textposition='auto',\n        hovertext=['Increased demand for public services: % of affected cities in the region : '+str(i) for i in np.round(public_temp.values,2)],orientation='v'\n             )]\n\n\nvul_public_temp_trace[0].marker.color = [\"rgb(255,247,188)\"]+[\"rgb(165,15,21)\"]*7\nvul_public_temp_trace[1].marker.color = [\"rgb(255,247,188)\"]+[\"rgb(35,139,69)\"]*7   \n\n\nfig2 = make_subplots(\n    rows=4, cols=2,\n    specs=[\n           [{\"type\": \"scattermapbox\",'colspan':2},None],\n           [{\"type\": \"bar\",'colspan':1},{\"type\": \"scatter\",'colspan':1}],\n#            [{\"type\": \"histogram2d\"}],\n           [{\"type\": \"bar\",'colspan':2},None],\n           [{\"type\": \"bar\",'colspan':2},None] \n          ],\n    subplot_titles=['<b>Global Severity map of impacted social aspects</b>',\n                    '<b>Regional avg. of city-based impact% over years','  <b>Trendline : global avg. impact% </b>',\n                    '<b>Service-wise analysis : % of cities having the corresponding social aspect affected</b>',\n                    '<b>Region-wise: \"vulnerable population\\'s risk\" & \"demand for public services\"</b>'\n                    ],\n#     x_title='Exploration of social impacts across the world'.upper(),\n    vertical_spacing=0.16,horizontal_spacing=0.2,\n    row_heights=[0.5,0.15,0.1,0.25],\n        \n#     column_widths=[1,1,1]\n    \n                    )\n\nfig2.add_trace(smb,row=1,col=1)\nfig2.update_layout(\n        mapbox_style=\"carto-darkmatter\",\n#         title = dict(text='% of CDP enlisted services affected',x=0.5,y=.97),\n        height=1600,\n        width=900,\n        )\n\nfig2.update_layout(\n    template=\"plotly_dark\",\n    margin=dict(r=25, t=25, b=140, l=100),\n    showlegend=False,\n    bargroupgap=0.25,\n    bargap=0.25\n)\n\n\nfig2.append_trace(regional_mean[0],row=2,col=1)\nfig2.append_trace(regional_mean[1],row=2,col=1)\n# fig2.append_trace(regional_mean[2],row=2,col=1)\n\nfig2.append_trace(regional_scatter,row=2,col=2)\n# regional_scatter.update(xticks=[2018,2019,2020])\n\n\nfig2.append_trace(aspects_temp_trace,row=3,col=1)\nfig2.append_trace(vul_public_temp_trace[0],row=4,col=1)\nfig2.append_trace(vul_public_temp_trace[1],row=4,col=1)\n\n# fig2.append_trace(sun1,row=4,col=2)\n\n\n# fig2.update_histogram(barmode='overlay')\n# Reduce opacity to see both histograms\nfig2.update_traces(opacity=0.65)\n\nfig2.update_layout(font_color=\"rgb(199,233,180)\",font_family='sans-serif')\n","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}