{"cells":[{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"import numpy as np  # linear algebra\nimport pandas as pd  # data processing, CSV file I/O (e.g. pd.read_csv)\n\nimport  seaborn as sns\nimport matplotlib.pyplot as plt\nimport plotly_express as px\n\nfrom nltk.corpus import stopwords\nstop = stopwords.words('english') + stopwords.words('spanish') \n\nPATH = '../input/cdp-unlocking-climate-solutions/Cities/'\nPATH2 = '../input/cdp-unlocking-climate-solutions/Corporations/'","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"This notebook provides an idea of the corporate responses to the Climate Change and Water Security Questionnares in the form of visualizations. These visualizations attemt present the most significant or noteworthy attributes of the answers to the selected questions.\n\nThe 2020 Questionnaires seems to contain data only from organizations headquartered in USA and Canada. As such, multiple location based visualiations presented show only these two countries. Wherever data from operations conducted or facilities owned by these organizations in other parts of the world is available, the visualizations will reflect it. I have tried to make the best use of the space but if any of the visualizations seem too small to read the text labels, I request you to zoom in. Thanks, and enjoy!"},{"metadata":{"trusted":true,"_kg_hide-output":true,"_kg_hide-input":true},"cell_type":"code","source":"# disclosing\ncd_cc2018 = pd.read_csv(PATH2 + 'Corporations Disclosing/Climate Change/2018_Corporates_Disclosing_to_CDP_Climate_Change.csv')\ncd_cc2019 = pd.read_csv(PATH2 + 'Corporations Disclosing/Climate Change/2019_Corporates_Disclosing_to_CDP_Climate_Change.csv')\ncd_cc2020 = pd.read_csv(PATH2 + 'Corporations Disclosing/Climate Change/2020_Corporates_Disclosing_to_CDP_Climate_Change.csv')\n\ncd_cc = pd.concat([cd_cc2020, cd_cc2019, cd_cc2018])\n\n# responses\ncr_cc2018 = pd.read_csv(PATH2 + 'Corporations Responses/Climate Change/2018_Full_Climate_Change_Dataset.csv')\ncr_cc2019 = pd.read_csv(PATH2 + 'Corporations Responses/Climate Change/2019_Full_Climate_Change_Dataset.csv')\ncr_cc2020 = pd.read_csv(PATH2 + 'Corporations Responses/Climate Change/2020_Full_Climate_Change_Dataset.csv')\n\ncr_cc = pd.concat([cr_cc2020, cr_cc2019, cr_cc2018])\n\n# cd_cc.columns, cr_cc.columns","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"climate = pd.merge(cd_cc, cr_cc, 'inner', on=['survey_year', 'account_number', 'organization', 'response_received_date'])\n\n# climate.groupby('survey_year')['account_number'].nunique()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-output":true,"_kg_hide-input":true},"cell_type":"code","source":"# disclosing\ncd_ws2018 = pd.read_csv(PATH2 + 'Corporations Disclosing/Water Security/2018_Corporates_Disclosing_to_CDP_Water_Security.csv')\ncd_ws2019 = pd.read_csv(PATH2 + 'Corporations Disclosing/Water Security/2019_Corporates_Disclosing_to_CDP_Water_Security.csv')\ncd_ws2020 = pd.read_csv(PATH2 + 'Corporations Disclosing/Water Security/2020_Corporates_Disclosing_to_CDP_Water_Security.csv')\n\ncd_ws = pd.concat([cd_ws2020, cd_ws2019, cd_ws2018])\n\n# responses\ncr_ws2018 = pd.read_csv(PATH2 + 'Corporations Responses/Water Security/2018_Full_Water_Security_Dataset.csv')\ncr_ws2019 = pd.read_csv(PATH2 + 'Corporations Responses/Water Security/2019_Full_Water_Security_Dataset.csv')\ncr_ws2020 = pd.read_csv(PATH2 + 'Corporations Responses/Water Security/2020_Full_Water_Security_Dataset.csv')\n\ncr_ws = pd.concat([cr_ws2020, cr_ws2019, cr_ws2018])\n\n# cd_ws.columns, cr_ws.columns","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"water = pd.merge(cd_ws, cr_ws, 'inner', on=['survey_year', 'account_number', 'organization', 'response_received_date'])\n\n# water.groupby('survey_year')['account_number'].nunique()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"def get_climate(q, by='row_number', year=2020, cols=['organization']):\n    val = climate[climate['question_number']==q]['question_unique_reference']\n    sh = val.shape[0]\n    if sh > 0:\n        print(q, val.iloc[0], f'({sh})')\n    return climate[(climate['survey_year'] == year) & (climate['question_number']==q)][cols + [by, 'column_name', 'response_value']].dropna(subset=['response_value'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"def get_water(q, by='row_number', year=2020, cols=['organization']):\n    val = water[water['question_number']==q]['question_unique_reference']\n    val2 = water[(water['survey_year'] == year) & (water['question_number']==q)][cols + [by, 'column_name', 'response_value']].dropna(subset=['response_value'])\n    sh = val2.shape[0]\n    if sh > 0:\n        print(q, val.iloc[0], f'({sh})')\n    return val2","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Climate Change\n\nThe 2020 Climate Change questionnaire is organized into the following sections. From these we again extract a list of relevant questions that can facilitate the understanding of what would be good KPIs\n\n1. Governance\n1. Risks and opportunities\n1. Business strategy\n1. Targets and performance\n1. Emissions methodology\n1. Emissions data\n1. Emissions breakdown\n1. Energy\n1. Additional metrics\n1. Verification\n1. Carbon pricing\n1. Engagement\n\nBelow, we have picked a set of key questions from these sections in the questionnaire. We believe these might be useful in building the KPIs for corporations with respect to their influence on climate change and the potential risk certain climate hazards pose to their operations."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"# no data (2020): ['C3.1b', 'C3.1d', 'C4.2a', 'C4.3d', 'C5.1', 'C7.9a',\n#           'C9.1', 'C11.1d', 'C11.2a', 'C11.3a', 'C12.3c',\n#           'C12.3e', 'C12.3f', 'C-FI', 'SC0.2']\n\nqs_cc = ['C2.2', 'C2.2a', 'C2.3a', 'C2.4a', 'C4.1a', 'C4.1b', 'C4.2b',\n         'C4.3a', 'C4.3b', 'C4.5a', 'C6.1', 'C6.3', 'C6.5',  'C6.10', \n         'C7.1a', 'C7.2', 'C7.3a', 'C7.3b', 'C7.3c', 'C7.5', 'C7.6a', \n         'C7.6c','C7.6b', 'C8.1', 'C8.2', 'C8.2a', 'C8.2b', 'C8.2c',\n         'C8.2d', 'C8.2e', 'C12.1a', 'C12.1b', 'C12.3a']\n\nqs_cc =sorted(climate[climate['survey_year']==2019]['question_number'].unique())\n\nfor q in qs_cc:\n    get_climate(q, year=2019)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"cc2_2a = get_climate('C2.2a', by='row_name')\ncc2_2a.row_name.value_counts().plot(kind='barh', title='Risk areas considered by corporated (2020)');\n# cc2_2a.pivot(index=['organization', 'row_name'], columns='column_name')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"cc2_3a = get_climate('C2.3a')\npiv2_3a = cc2_3a.pivot(index=['organization', 'row_number'], columns='column_name')['response_value']\npiv2_3a[['C2.3a_C3Risk type & Primary climate-related risk driver', 'C2.3a_C9Magnitude of impact']] \\\n.groupby('C2.3a_C3Risk type & Primary climate-related risk driver')['C2.3a_C9Magnitude of impact'] \\\n.value_counts().sort_values(ascending=False)[:60].unstack() \\\n.plot(kind='barh', stacked=True, title='Types of risks with their magnitude of impact on businesses (2020)');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"piv2_3a[['C2.3a_C4Primary potential financial impact', 'C2.3a_C9Magnitude of impact']] \\\n.groupby('C2.3a_C4Primary potential financial impact')['C2.3a_C9Magnitude of impact'] \\\n.value_counts().sort_values(ascending=False)[:48].unstack() \\\n.plot(kind='barh', stacked=True, title='Kind of financial impacts on business and their magnitudes (2020)');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"cc2_4a = get_climate('C2.4a')\nccpiv2_4a = cc2_4a.pivot(index=['organization', 'row_number'], columns='column_name')['response_value']\n\nccpiv2_4a[['C2.4a_C3Opportunity type','C2.4a_C4Primary climate-related opportunity driver', 'C2.4a_C7Time horizon']] \\\n.groupby(['C2.4a_C3Opportunity type', 'C2.4a_C4Primary climate-related opportunity driver'])['C2.4a_C7Time horizon'].value_counts() \\\n.sort_values(ascending=False)[:60].unstack().plot(kind='barh', figsize=(10, 8), stacked=True, title='Opportunity type and drivers, and time horizons for their potential impacts on business (2020)');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"ccpiv2_4a[['C2.4a_C5Primary potential financial impact', 'C2.4a_C9Magnitude of impact']] \\\n.groupby('C2.4a_C5Primary potential financial impact')['C2.4a_C9Magnitude of impact'] \\\n.value_counts().sort_values(ascending=False)[:33].unstack() \\\n.plot(kind='barh', stacked=True, title='Financial impacts of identified opportunities and the magnitudes (2020)');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"cc4_1a = get_climate('C4.1a')\nccpiv4_1a = cc4_1a.pivot(index=['organization', 'row_number'], columns='column_name')['response_value'] \\\n[['C4.1a_C5Base year', 'C4.1a_C6Covered emissions in base year (metric tons CO2e)', 'C4.1a_C8Target year', 'C4.1a_C9Targeted reduction from base year (%)', 'C4.1a_C12% of target achieved [auto-calculated]']]\n\nfor col in ccpiv4_1a.columns:\n    ccpiv4_1a[col] = ccpiv4_1a[col].astype('float64', copy=False )\n\nccpiv4_1a['Target'] = ccpiv4_1a['C4.1a_C6Covered emissions in base year (metric tons CO2e)'] - (ccpiv4_1a['C4.1a_C6Covered emissions in base year (metric tons CO2e)'] * ccpiv4_1a['C4.1a_C9Targeted reduction from base year (%)']/100)\nccpiv4_1a['Reporting'] = ccpiv4_1a['C4.1a_C6Covered emissions in base year (metric tons CO2e)'] - ((ccpiv4_1a['C4.1a_C12% of target achieved [auto-calculated]']/100) * (ccpiv4_1a['C4.1a_C6Covered emissions in base year (metric tons CO2e)'] - ccpiv4_1a['Target']))\nccpiv4_1a.drop(['C4.1a_C9Targeted reduction from base year (%)', 'C4.1a_C12% of target achieved [auto-calculated]'], axis=1, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"# pd.concat([ccpiv4_1a.groupby(['C4.1a_C5Base year'])['C4.1a_C6Covered emissions in base year (metric tons CO2e)'].sum(), ccpiv4_1a.groupby(['C4.1a_C8Target year'])['Target'].sum()])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"# ccpiv4_1a.groupby(['C4.1a_C8Target year'])['Target'].sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"cc6_1 = get_climate('C6.1', by='row_name')\nccpiv6_1 = cc6_1.pivot(index=['organization', 'row_name'], columns='column_name')['response_value']\nccpiv6_1['C6.1_C1Gross global Scope 1 emissions (metric tons CO2e)'] = ccpiv6_1['C6.1_C1Gross global Scope 1 emissions (metric tons CO2e)'].astype('float32')\nccpiv6_1['C6.1_C1Gross global Scope 1 emissions (metric tons CO2e)'].sort_values(ascending=False)[:120].unstack() \\\n.plot(kind='barh', figsize=(8, 14), logx=True, width=0.9, title='Organization yearly Scope 1 emissions in metric tonnes (reported 2020)');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"cc6_3 = get_climate(\"C6.3\", by='row_name')\nccpiv6_3 = cc6_3.pivot(index=['organization', 'row_name'], columns='column_name')['response_value']\nccpiv6_3['C6.3_C1Scope 2, location-based'] = ccpiv6_3['C6.3_C1Scope 2, location-based'].astype('float32')\nccpiv6_3['C6.3_C2Scope 2, market-based (if applicable)'] = ccpiv6_3['C6.3_C2Scope 2, market-based (if applicable)'].astype('float32')\nccpiv6_3['sum'] = ccpiv6_3['C6.3_C1Scope 2, location-based'] + ccpiv6_3['C6.3_C2Scope 2, market-based (if applicable)']\n\nccpiv6_3.sort_values(by='sum', ascending=False)[:80].drop('sum', axis=1) \\\n.plot(kind='barh', figsize=(10, 14), logx=True, width=0.9, title='Organization location and market based Scope 2 emissions in metric tonnes (reported 2020)');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"cc6_5 = get_climate(\"C6.5\", by='row_name')\nccpiv6_5 = cc6_5.pivot(index=['organization', 'row_name'], columns='column_name')['response_value']['C6.5_C2Metric tonnes CO2e']\nccpiv6_5 = ccpiv6_5.astype('float32')\n\nccpiv6_5.groupby('row_name').sum().sort_values().plot(kind='barh', title='Global Scope 3 emissions by activity/source (2020)');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"ccpiv6_5 = ccpiv6_5.unstack()\nccpiv6_5['sum'] = ccpiv6_5.sum(axis=1)\nccpiv6_5.sort_values(by='sum', ascending=False)[:60].drop('sum', axis=1) \\\n.plot(kind='barh', figsize=(12, 12), width=0.91, stacked=True, cmap='tab20', title='Organization Scope 3 emissions by activity/source (2020)').legend(bbox_to_anchor=(1, 0.6));","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"cc6_10 = get_climate(\"C6.10\").pivot(index=['organization', 'row_number'], columns='column_name')['response_value']\ncc6_10 = cc6_10[cc6_10['C6.10_C3Metric denominator'] == 'unit total revenue']['C6.10_C1Intensity figure'].astype('float32')\ncc6_10.groupby(['organization']).sum().sort_values(ascending=False)[:60] \\\n.plot(kind='barh', figsize=(10, 12), logx=True, title='Top 60 organizations by intensity of combined global Scope 1 and 2 emissions per unit total revenue (2020)');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"cc7_1a = get_climate('C7.1a')\nccpiv7_1a = cc7_1a.pivot(index=['organization', 'row_number'], columns='column_name')['response_value'].reset_index().drop(['row_number', 'C7.1a_C3GWP Reference'], axis=1)\nccpiv7_1a['C7.1a_C2Scope 1 emissions (metric tons of CO2e)'] = ccpiv7_1a['C7.1a_C2Scope 1 emissions (metric tons of CO2e)'].astype('float32')\nccpiv7_1a = ccpiv7_1a.pivot_table(index=['organization'], columns='C7.1a_C1Greenhouse gas', aggfunc=sum)['C7.1a_C2Scope 1 emissions (metric tons of CO2e)'] \\\n[['CO2', 'CO2: Refrigerants', 'CH4', 'N2O', 'HFCs', 'PFCs', 'SF6', 'NF3']]\n\nccpiv7_1a['sum'] = ccpiv7_1a.sum(axis=1)\nccpiv7_1a.sort_values('sum', ascending=False)[:60].drop('sum', axis=1) \\\n.plot(kind='barh', stacked=True, figsize=(10,10), title='Breakdown of Scope 1 emissions by Greenhouse Gas of top emitters (2020)');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"cc7_2 = get_climate('C7.2')\nccpiv7_2 = cc7_2.pivot(index=['organization', 'row_number'], columns='column_name')['response_value']\n\nccpiv7_2['C7.2_C2Scope 1 emissions (metric tons CO2e)'] = ccpiv7_2['C7.2_C2Scope 1 emissions (metric tons CO2e)'].astype('float32')\nccpiv7_2.groupby(['C7.2_C1Country/Region'])['C7.2_C2Scope 1 emissions (metric tons CO2e)'].sum().sort_values(ascending=False)[:30] \\\n.plot(kind='barh', figsize=(8,8), logx=True, title='Total Scope 1 emissions by country/region (2020)').legend(bbox_to_anchor=(0.2, 1));","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"ccpiv7_2.groupby(['C7.2_C1Country/Region'])['C7.2_C2Scope 1 emissions (metric tons CO2e)'].mean() \\\n.sort_values(ascending=False)[:80].plot(kind='barh', figsize=(10,14), logx=True, title='Mean Scope 1 emissions by country/region (2020)');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"cc7_5 = get_climate('C7.5')\nccpiv7_5 = cc7_5.pivot(index=['organization', 'row_number'], columns='column_name')['response_value']\nccpiv7_5['C7.5_C2Scope 2, location-based (metric tons CO2e)'] = ccpiv7_5['C7.5_C2Scope 2, location-based (metric tons CO2e)'].astype('float32')\nccpiv7_5['C7.5_C3Scope 2, market-based (metric tons CO2e)'] = ccpiv7_5['C7.5_C3Scope 2, market-based (metric tons CO2e)'].astype('float32')\nccpiv7_5['total CO2e'] = ccpiv7_5['C7.5_C2Scope 2, location-based (metric tons CO2e)'] + ccpiv7_5['C7.5_C3Scope 2, market-based (metric tons CO2e)']\nccpiv7_5.groupby(['C7.5_C1Country/Region'])['total CO2e'].sum().sort_values(ascending=False)[:75] \\\n.plot(kind='barh', logx=True, figsize=(10,12), title='Total Scope 2 emissions by region (2020)');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"ccpiv7_5.groupby(['C7.5_C1Country/Region'])['total CO2e'].mean().sort_values(ascending=False)[:75] \\\n.plot(kind='barh', figsize=(10,12), logx=True, title='Mean Scope 2 emissions by region (2020)');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"get_climate('C8.1')['response_value'].value_counts() \\\n.plot(kind='barh', title='Organizational spend on energy (2020)', figsize=(10, 6));","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"get_climate('C8.2', by='row_name').groupby('row_name')['response_value'].value_counts().unstack() \\\n.plot(kind='barh', title='Energy related activities undertaken by organizations (2020)', figsize=(10, 6));","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"cc8_2a = get_climate('C8.2a', by='row_name')\ncc8_2a.pivot(index=['organization', 'row_name'], columns='column_name')['response_value'] \\\n[['C8.2a_C2MWh from renewable sources', 'C8.2a_C3MWh from non-renewable sources']] \\\n.astype('float32').groupby(['row_name']).sum() \\\n.plot(kind='barh', figsize=(8, 6), title='Global energy consumption by organizations by source (2020)');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"get_climate('C8.2b', by='row_name').groupby('row_name')['response_value'].value_counts().unstack() \\\n.plot(kind='barh', title='Applications of fuel use by organization (2020)', figsize=(10, 6));","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"cc8_2c = get_climate('C8.2c')\nccpiv8_2c = cc8_2c.pivot(index=['organization', 'row_number'], columns='column_name')['response_value']\nccpiv8_2c['C8.2c_C3Total fuel MWh consumed by the organization'] = ccpiv8_2c['C8.2c_C3Total fuel MWh consumed by the organization'].astype('float64')\n\nccpiv8_2c.groupby(['organization', 'C8.2c_C1Fuels (excluding feedstocks)'])['C8.2c_C3Total fuel MWh consumed by the organization'].sum() \\\n.sort_values(ascending=False)[:120].unstack() \\\n.plot(kind='barh', stacked=True, figsize=(10,14), cmap='tab20', title='Breakdown of fuels consumption in MWh by organizations (2020)');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"cc8_2d = get_climate('C8.2d', by='row_name')\nccpiv8_2d = cc8_2d.pivot(index=['organization', 'row_name'], columns='column_name')['response_value'].astype('float32') \\\n[['C8.2d_C1Total Gross generation (MWh)', 'C8.2d_C3Gross generation from renewable sources (MWh)']].groupby('row_name').sum()\nccpiv8_2d['Gross generation from non-renewable sources'] = ccpiv8_2d['C8.2d_C1Total Gross generation (MWh)'] - ccpiv8_2d['C8.2d_C3Gross generation from renewable sources (MWh)']\n\nccpiv8_2d.drop('C8.2d_C1Total Gross generation (MWh)', axis=1) \\\n.plot(kind='barh', title='Comparisions between generation of electricity, heat, steam, cooling from renewable and non-renewable sources (2020)', figsize=(10, 8)).legend(bbox_to_anchor=(1,0.1));","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"cc12_1a = get_climate('C12.1a')\ncc12_1a.pivot(index=['organization', 'row_number'], columns='column_name')['response_value']['C12.1a_C2Details of engagement'].value_counts()[:14] \\\n.plot(kind='barh', figsize=(8, 8), title='Climate related supplier engangement strategies (2020)');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"cc12_1b = get_climate('C12.1b')\ncc12_1b.pivot(index=['organization', 'row_number'], columns='column_name')['response_value']['C12.1b_C2Details of engagement'].value_counts()[:5]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"cc12_3a = get_climate('C12.3a').pivot(index=['organization', 'row_number'], columns='column_name')['response_value'] \\\n.groupby(['C12.3a_C1Focus of legislation'])['C12.3a_C2Corporate position'].value_counts().unstack()\ncc12_3a['sum'] = cc12_3a.sum(axis=1)\n\ncc12_3a.sort_values('sum', ascending=False).drop('sum', axis=1)[:8] \\\n.plot(kind='barh', stacked=True, title='Engagement with policy makers by issues and position (2020)');","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Water Security\n\nThe Sections as per the 2020 Water Security Questionnaire for Corporations are as follows.\n\n1. Current state\n1. Business impacts\n1. Procedures\n1. Risks and opportunities\n1. Facility-level water accounting\n1. Governance\n1. Business strategy\n1. Targets\n1. Verfication\n\nOnce more, we pick a set of most important questions which are relevant towards gaining insights into building KPIs for corporations with respect to their water security and risk."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"# no data (2020): ['W1.2d', 'W1.4c', 'W1.4d', 'W2.1a', 'W2.2a', 'W2.2b',\n#           'W4.1a', 'W4.3b', 'W6.1a', 'W6.2a', 'W6.2b', 'W6.3', 'W6.4a', \n#           'W6.5a', 'W6.6', 'W7.1', 'W7.2', 'W7.3b', 'W9.1a']\n\nqs_ws = ['W1.2', 'W1.2b', 'W1.2h', 'W1.2i', 'W1.4a', 'W1.4b',   \n         'W3.3a', 'W3.3b', 'W3.3c', 'W3.3d', 'W4.1b', \n         'W4.1c', 'W4.2', 'W4.2a', 'W4.3a', 'W5.1', \n         'W5.1a', 'W8.1', 'W8.1b']\n# qs_ws = sorted(water[water['survey_year']==2019]['question_number'].unique())\n\nfor q in qs_ws:\n    get_water(q, year=2020)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"w1_2 = get_water('W1.2', by='row_name').pivot(index=['organization', 'row_name'], columns='column_name')['response_value']['W1.2_C1% of sites/facilities/operations']\nw1_2.groupby('row_name').value_counts().unstack() \\\n.plot(kind='barh', stacked=True, figsize=(10, 8), title='Proportion of water related aspects that are regularly measured and monitored across organization operations (2020)').legend(bbox_to_anchor=(1,1));","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"w1_2b = get_water('W1.2b', by='row_name').pivot(index=['organization', 'row_name'], columns='column_name')['response_value']['W1.2b_C1Volume (megaliters/year)'].unstack().astype('float32')\nw1_2b['sum'] = w1_2b.sum(axis=1)\nw1_2b.sort_values(by='sum', ascending=False).drop('sum', axis=1)[:60] \\\n.plot(kind='barh', logx=True, figsize=(10, 14), width=0.9, title='Water volume consumed, discharged, withdrawn by organization (202)');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"w1_2h = get_water('W1.2h', by='row_name').pivot(index=['organization', 'row_name'], columns='column_name')['response_value']['W1.2h_C2Volume (megaliters/year)'].astype('float32')\nw1_2h.groupby(['organization', 'row_name']).sum().sort_values(ascending=False)[:75].unstack() \\\n.plot(kind='barh', logx=True, width=0.9, figsize=(10, 12), title='Volume of water withdrawn by organizations by source (2020)');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"w1_2i = get_water('W1.2i', by='row_name').pivot(index=['organization', 'row_name'], columns='column_name')['response_value']['W1.2i_C2Volume (megaliters/year)'].astype('float32')\nw1_2i.groupby(['organization', 'row_name']).sum().sort_values(ascending=False)[:75].unstack() \\\n.plot(kind='barh', logx=True, width=0.9, figsize=(10, 12), title='Volume of water discharged by organizations by destination (2020)');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"w1_2j = get_water('W1.2j', by='row_name', cols=['country', 'organization'], year=2019).pivot(index=['country', 'organization', 'row_name'], columns='column_name')['response_value']\nw1_2j.groupby('country')['W1.2j_C1% recycled and reused'].value_counts().unstack(-1) \\\n.plot(kind='barh', figsize=(8, 6), stacked=True, title='Water recycled by country (US and Canada 2019)');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"get_water('W4.1b').pivot(index=['organization', 'row_number'], columns='column_name')['response_value']['W4.1b_C1Total number of facilities exposed to water risk'] \\\n.astype('float32').plot(kind='hist', title='Number of facilities exposed to water risk for organizations (2020)', logy=True);","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"w4_1c = get_water('W4.1c').pivot(index=['organization', 'row_number'], columns='column_name')['response_value']\nw4_1c['W4.1c_C2Number of facilities exposed to water risk'] = w4_1c['W4.1c_C2Number of facilities exposed to water risk'].astype('float32')\nw4_1c.groupby(['W4.1c_C1Country/Area & River basin_G', 'W4.1c_C7% company’s total global revenue that could be affected'])['W4.1c_C2Number of facilities exposed to water risk'].sum() \\\n.sort_values(ascending=False)[:50].unstack() \\\n.plot(kind='barh', stacked=True, figsize=(14, 8), title='No. of facilities exposed to water risk by location and % impact to  organizationrevenue (2020)').legend(bbox_to_anchor=(1, 0.5));","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"w4_2 = get_water('W4.2').pivot(index=['organization', 'row_number'], columns='column_name')['response_value']\nplt.subplots(figsize=(10, 8))\nsns.heatmap(w4_2.groupby(['W4.2_C2Type of risk & Primary risk driver_G', 'W4.2_C2Type of risk & Primary risk driver'])['W4.2_C3Primary potential impact'] \\\n            .value_counts().sort_values(ascending=False)[:40].unstack().T, annot=True);\n\nplt.xticks(rotation=65, horizontalalignment='right',)\nplt.title('Type of risks and risk drivers affecting direct operations with their potential impacts (2020)')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"plt.subplots(figsize=(10, 8))\nsns.heatmap(w4_2.groupby(['W4.2_C2Type of risk & Primary risk driver_G', 'W4.2_C2Type of risk & Primary risk driver'])['W4.2_C13Primary response to risk'] \\\n            .value_counts().sort_values(ascending=False)[:40].unstack().T, annot=True);\n\nplt.xticks(rotation=65, horizontalalignment='right')\nplt.title('Type of risks and risk drivers affecting direct operations by primary response to the risks (2020)')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"w4_2a = get_water('W4.2a').pivot(index=['organization', 'row_number'], columns='column_name')['response_value']\nplt.subplots(figsize=(10, 8))\nsns.heatmap(w4_2a.groupby(['W4.2a_C3Type of risk & Primary risk driver_G', 'W4.2a_C3Type of risk & Primary risk driver'])['W4.2a_C4Primary potential impact'] \\\n            .value_counts().sort_values(ascending=False)[:].unstack(), annot=True);\n\nplt.xticks(rotation=65, horizontalalignment='right',)\nplt.title('Type of risks and risk drivers beyond direct operations and their primary impacts (2020)')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"w4_3a = get_water('W4.3a').pivot(index=['organization', 'row_number'], columns='column_name')['response_value']\nw4_3a.groupby(['W4.3a_C1Type of opportunity', 'W4.3a_C2Primary water-related opportunity'])['W4.3a_C5Magnitude of potential financial impact'].value_counts() \\\n.sort_values()[51:].unstack().plot(kind='barh', stacked=True, figsize=(12, 7), title='Opportunities identified as potentially having financial impacts on organizations - by magnitude of impact (2020)');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"pd.options.mode.chained_assignment = None\n\nw = 'W5.1_C9Total water withdrawals at this facility (megaliters/year)'\nd = 'W5.1_C17Total water discharges at this facility (megaliters/year)'\nc = 'W5.1_C23Total water consumption at this facility (megaliters/year)'\n\nw5_1 = get_water('W5.1').pivot(index=['organization', 'row_number'], columns='column_name')['response_value']\nw5_1 = w5_1[[\n    'W5.1_C3Country/Area & River basin_G', 'W5.1_C3Country/Area & River basin', w, c, d,\n    'W5.1_C4Latitude', 'W5.1_C5Longitude'\n]]\n\nw5_1.dropna(subset=['W5.1_C4Latitude', 'W5.1_C5Longitude'], inplace=True)\n\nfor col in w5_1.columns[2:]:\n    w5_1[col] = w5_1[col].astype('float32')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"w5_1w = w5_1[w5_1[w]>0]\n\nw5_1w['withdrawn'] = np.log10(w5_1w[w])\n# adding min negative value to remove negatives\nw5_1w['withdrawn']= w5_1w['withdrawn'] - w5_1w['withdrawn'].min()\n\nw5_1w['log consumed'] = np.log10(w5_1w[c])\n\npx.scatter_geo(w5_1w, lat='W5.1_C4Latitude',\n               lon='W5.1_C5Longitude', \n               color='log consumed',\n               hover_data=[w, c, 'W5.1_C3Country/Area & River basin_G', 'W5.1_C3Country/Area & River basin'],\n               projection=\"natural earth\",        \n               size='withdrawn',\n               size_max=15,\n               color_continuous_scale=px.colors.sequential.Viridis,\n               title='Water withdrawn (size) and consumed (color) by facility location (2020)',\n)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"w5_1d = w5_1[w5_1[d]>0]\n\nw5_1d['size'] = np.log10(w5_1d[d])\n# adding min negative value to remove negatives\nw5_1d['size']= w5_1d['size'] - w5_1d['size'].min()\n\npx.scatter_geo(w5_1d, lat='W5.1_C4Latitude',\n               lon='W5.1_C5Longitude', \n               color='size',\n               hover_data=[d, 'W5.1_C3Country/Area & River basin_G', 'W5.1_C3Country/Area & River basin'],\n               projection=\"natural earth\",        \n               size='size',\n               size_max=15,\n               title='Discharge (size and color) of water by facility location (2020)'\n    )","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"get_water('W5.1a', cols=['country', 'organization']).pivot(index=['country', 'organization', 'row_number'], columns='column_name')['response_value']['W5.1a_C1% verified'].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"w5_1c = get_water('W5.1c', year=2019, cols=['country', 'organization']).pivot(index=['country', 'organization', 'row_number'], columns='column_name')['response_value']\nw5_1c.groupby('country')['W5.1c_C3% recycled or reused'].value_counts().unstack(0) \\\n.plot(kind='barh', stacked=True, figsize=(8, 6), title='Proportion of water recycled/reused at a facility by home country of organization (2019)');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"w8_1 = get_water('W8.1', cols=['organization', 'country']).pivot(index=['country', 'organization', 'row_number'], columns='column_name')['response_value']\nw8_1['W8.1_C1Levels for targets and/or goals'].str.split(';').explode().to_frame().groupby('country')['W8.1_C1Levels for targets and/or goals'].value_counts() \\\n.sort_values(ascending=False)[:26].unstack(0).plot(kind='barh', figsize=(8, 6), stacked=True, title='Organization approach to setting and monitoring water targets/goals by home country of organization (2020)');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"w8_1a = get_water('W8.1a').pivot(index=['organization', 'row_number'], columns='column_name')['response_value']\nplt.subplots(figsize=(10, 8))\nsns.heatmap(w8_1a.groupby('W8.1a_C2Category of target')['W8.1a_C4Primary motivation'].value_counts().sort_values(ascending=False)[:50].unstack(), annot=True);\nplt.xticks(rotation=65, horizontalalignment='right',)\nplt.title('Category of organization water targets by their primary motivation (2020)')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"w8_1b = get_water('W8.1b', cols=['country', 'organization']).pivot(index=['country', 'organization', 'row_number'], columns='column_name')['response_value']\nw8_1b.groupby(['country'])['W8.1b_C1Goal'].value_counts().sort_values(ascending=False)[:90].unstack(0)[:12] \\\n.plot(kind='barh', stacked=True, title='Organization water goals by home country of organization (2020)');","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"w9_1a = get_water('W9.1a', cols=['country', 'organization'], year=2019).pivot(index=['country', 'organization', 'row_number'], columns='column_name')['response_value']\nw9_1a.groupby('W9.1a_C1Linkage or tradeoff')['W9.1a_C2Type of linkage/tradeoff'].value_counts().sort_values(ascending=False)[:20].unstack(0) \\\n.plot(kind='barh', stacked=True, figsize=(10, 7), title='Linkages and tradeoffs related to other environmental issues (2019)');","execution_count":null,"outputs":[]},{"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}