{"cells":[{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true,"_kg_hide-input":true,"collapsed":true,"_kg_hide-output":true},"cell_type":"code","source":"import os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        print(os.path.join(dirname, filename))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"import numpy as np \nimport pandas as pd \nimport matplotlib.pyplot as plt\nimport re\n\nimport seaborn as sns\n%matplotlib inline\nfrom IPython.core.interactiveshell import InteractiveShell\nInteractiveShell.ast_node_interactivity = \"all\"\nimport plotly.io as pio\nimport plotly.graph_objects as go\nfrom plotly.subplots import make_subplots\nimport plotly.express as px\nfrom wordcloud import WordCloud, STOPWORDS\nfrom matplotlib_venn import venn3, venn3_circles, venn3_unweighted\nfrom matplotlib_venn import venn2, venn2_circles, venn2_unweighted\npd.set_option('display.max_columns', 500)\n","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"collapsed":true,"_kg_hide-output":true},"cell_type":"code","source":"# EDA of Cities\n# Discloser dataset \ncities18= pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Cities/Cities Disclosing/2018_Cities_Disclosing_to_CDP.csv\")\ncities19 = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Cities/Cities Disclosing/2019_Cities_Disclosing_to_CDP.csv\")\ncities20 = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Cities/Cities Disclosing/2020_Cities_Disclosing_to_CDP.csv\")\n#Survey dataset\ncitySurvey18 = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Cities/Cities Responses/2018_Full_Cities_Dataset.csv\")\ncitySurvey19 = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Cities/Cities Responses/2019_Full_Cities_Dataset.csv\")\ncitySurvey20 = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Cities/Cities Responses/2020_Full_Cities_Dataset.csv\")\n\n\n# cities metadata - lat,lon locations for US cities\ncities_meta_df = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Supplementary Data/Simple Maps US Cities Data/uscities.csv\")\n\n# subset for lat, lng cities data\ncities_meta_df = cities_meta_df[['city', 'state_id', 'lat','lng']].rename(columns={'city' : 'address_city', 'state_id' : 'state'})\ncities_meta_df.head()\n#Firt Time Discloser\n\ndef newrecruits(df):\n    cities = df[df['First Time Discloser']== \"Yes\"]\n    cities = cities[['Year Reported to CDP', 'CDP Region']]\n    cities = cities.rename(columns={'CDP Region': 'CDPRegion','Year Reported to CDP':'Year' })\n    return(cities)\n\n#get country counts for new recruits\ndef newcount(df):\n    counts =df.groupby(['CDPRegion','Year']).size().reset_index(name = \"Total\")\n    return counts\n\n\ndef graphinfo(df):\n    city = newrecruits(df)\n    city = newcount(city)   \n    return city\n\n\ncity18 = graphinfo(cities18)\ncity19= graphinfo(cities19)\ncity20= graphinfo(cities20)\ncity = pd.concat([city18, city19, city20])\n\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### EDA of Cities Data (Discloser + Survey) \n\n1. How many new cities joined CDP over the years(2018,2019,2020)? "},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"df = city\nfig = px.bar(df, x=\"CDPRegion\", y=\"Total\",\n          color=\"CDPRegion\", facet_col=\"Year\",title=\"First Time Discloser\"\n           )\nfig.update_layout(\n    height=417,\n    xaxis_title=\"CDP Region\",\n    yaxis_title=\"Total\",\n    xaxis_visible=False,\n    xaxis2_visible=False,\n    xaxis3_visible=False)\nfig.show();","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"* **Based on the figure above, it looks like more cities joined CDP in 2019, compare to year 2018 and 2020. \n* **Also, 2019 figure shows higher level of geographical diversity compare to 2018 and 2019."},{"metadata":{},"cell_type":"markdown","source":"### CDP Cities membership over the years (2018,2019, and 2020)"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"set1 = set(cities18['Account Number'])  \nset2 = set(cities19['Account Number']) \nset3 = set(cities20['Account Number'])\nintersection = len(set1.intersection(set2, set3))\nvenn3([set1, set2, set3], set_labels = ('Yr_2018', 'Yr_2019', 'Yr_2020'), alpha = 0.5)\nplt.title('CDP Membership Retention over the years(2018-2020)')\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**Venn diagram shows 378 cities have been CDP members from 2018-2020."},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"##merge survey and city discloser dfs\nfiltercities = cities19[['Account Number', 'City', 'Reporting Authority' ]]\nfiltercities20 = cities20[['Account Number', 'City', 'Reporting Authority' ]]\n\ndef mergesurveydis(df1, df2):\n    mergecity = pd.merge(df1, df2, how = 'right', on = 'Account Number')\n    return mergecity\n\nmergedcity19 =mergesurveydis(filtercities, citySurvey19)\nmergedcity20 =mergesurveydis(filtercities20, citySurvey20)\n\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## EDA of 2019 and 2020 Cities' Data\n##### General sentiment of Climate Change and Water Security by Sector Region"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"#filter comments \n#filter columns with comments\n\ndef filtercomments(sub,df):\n    comments = df.loc[df.Comments.notnull()]\n    comments = comments[comments['CDP Region']==sub]\n    comments = comments['Comments']\n    return comments\n\n#join all the comments in the column\ndef converttostr(input_seq, seperator):\n    \n   # Join all the strings in list\n    final_str = seperator.join(input_seq)\n    return final_str\n\n\ndef filterjoin(sub, df):\n    seperator = ' '\n    comment = filtercomments(sub, df)\n    comment = converttostr(comment, seperator)\n    return comment\n\n# clean text in comment section\n#filter comments by Parent Section\nLatin_America  = filterjoin('Latin America',mergedcity19)\nNorth_America = filterjoin('North America',mergedcity19)                \nEurope = filterjoin('Europe', mergedcity19)    \nSoutheast_Asia = filterjoin('Southeast Asia and Oceania', mergedcity19)                           \nAfrica  =  filterjoin('Africa', mergedcity19)                       \nEast_Asia = filterjoin('East Asia', mergedcity19)        \nSouth_West_Asia = filterjoin('South and West Asia', mergedcity19)                        \nMiddle_East  =  filterjoin('Middle East', mergedcity19)    \n\n#create word cloud for each CDP Region: \n\n# Generate the image\napp_words = ['https', 'appendix', 'http', 'html']\nSTOPWORDS.update(app_words)\n\n\n\nLatin_America = WordCloud(max_words=100, min_word_length=10, background_color='black', colormap='Set2',collocations=False, stopwords = STOPWORDS).generate(Latin_America)\nNorth_America = WordCloud(max_words=100, min_word_length=8, background_color='black', colormap='Set2',collocations=False, stopwords = STOPWORDS).generate(North_America)\nEurope  = WordCloud(max_words=100, min_word_length=8, background_color='black', colormap='Set2',collocations=False, stopwords = STOPWORDS).generate(Europe)\nSoutheast_Asia = WordCloud(max_words=100, min_word_length=8, background_color='black', colormap='Set2',collocations=False, stopwords = STOPWORDS).generate(Southeast_Asia)\nAfrica = WordCloud(max_words=100, min_word_length=8, background_color='black', colormap='Set2', collocations=False,stopwords = STOPWORDS).generate(Africa)\nEast_Asia = WordCloud(max_words=100, min_word_length=8, background_color='black', colormap='Set2',collocations=False, stopwords = STOPWORDS).generate(East_Asia)\nSouth_West_Asia = WordCloud(max_words=100, min_word_length=8, background_color='black', colormap='Set2',collocations=False, stopwords = STOPWORDS).generate(South_West_Asia)\nMiddle_East= WordCloud(max_words=100, min_word_length=8, background_color='black', colormap='Set2', collocations=False,stopwords = STOPWORDS).generate(Middle_East)\n\n# visualize the image\nplt.figure(figsize=(40, 30))\nplt.subplot(421)\nplt.title(\"Latin America\",fontsize= 57)\nplt.imshow(Latin_America, interpolation='bilinear', alpha = 1)\nplt.subplot(422)\nplt.title(\"North America\",fontsize= 57)\nplt.imshow(North_America, interpolation='bilinear', alpha = 1)\nplt.subplot(423)\nplt.title(\"Europe\",fontsize= 57)\nplt.imshow(Europe, interpolation='bilinear', alpha = 1)\nplt.subplot(424)\nplt.title(\"South East Asia\",fontsize= 57)\nplt.imshow(Southeast_Asia, interpolation='bilinear', alpha = 1)\nplt.subplot(425)\nplt.title(\"Africa\",fontsize= 57)\nplt.imshow(Africa, interpolation='bilinear', alpha = 1)\nplt.subplot(426)\nplt.title(\"East Asia\",fontsize= 57)\nplt.imshow(East_Asia, interpolation='bilinear', alpha = 1)\nplt.subplot(427)\nplt.title(\"South and West Asia\",fontsize= 57)\nplt.imshow(South_West_Asia, interpolation='bilinear', alpha = 1)\nplt.subplot(428)\nplt.title(\"Middle East\",fontsize= 57)\nplt.imshow(Middle_East, interpolation='bilinear', alpha = 1)\nplt.show()\n\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Analysis of US Cities"},{"metadata":{"_kg_hide-input":true,"trusted":true,"collapsed":true},"cell_type":"code","source":"# cities metadata - CDP metadata on organisation HQ cities\ncities_cdpmeta_df = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Supplementary Data/Locations of Corporations/NA_HQ_public_data.csv\")\n# import cities response df\ncities_df = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Cities/Cities Responses/2020_Full_Cities_Dataset.csv\")\n# state abbreviation dictionary\ndef list_dedupe(x):\n    return list(dict.fromkeys(x))\nus_state_abbrev = {\n    'Alabama': 'AL',\n    'Alaska': 'AK',\n    'American Samoa': 'AS',\n    'Arizona': 'AZ',\n    'Arkansas': 'AR',\n    'California': 'CA',\n    'Colorado': 'CO',\n    'Connecticut': 'CT',\n    'Delaware': 'DE',\n    'District of Columbia': 'DC',\n    'Florida': 'FL',\n    'Georgia': 'GA',\n    'Guam': 'GU',\n    'Hawaii': 'HI',\n    'Idaho': 'ID',\n    'Illinois': 'IL',\n    'Indiana': 'IN',\n    'Iowa': 'IA',\n    'Kansas': 'KS',\n    'Kentucky': 'KY',\n    'Louisiana': 'LA',\n    'Maine': 'ME',\n    'Maryland': 'MD',\n    'Massachusetts': 'MA',\n    'Michigan': 'MI',\n    'Minnesota': 'MN',\n    'Mississippi': 'MS',\n    'Missouri': 'MO',\n    'Montana': 'MT',\n    'Nebraska': 'NE',\n    'Nevada': 'NV',\n    'New Hampshire': 'NH',\n    'New Jersey': 'NJ',\n    'New Mexico': 'NM',\n    'New York': 'NY',\n    'North Carolina': 'NC',\n    'North Dakota': 'ND',\n    'Northern Mariana Islands':'MP',\n    'Ohio': 'OH',\n    'Oklahoma': 'OK',\n    'Oregon': 'OR',\n    'Pennsylvania': 'PA',\n    'Puerto Rico': 'PR',\n    'Rhode Island': 'RI',\n    'South Carolina': 'SC',\n    'South Dakota': 'SD',\n    'Tennessee': 'TN',\n    'Texas': 'TX',\n    'Utah': 'UT',\n    'Vermont': 'VT',\n    'Virgin Islands': 'VI',\n    'Virginia': 'VA',\n    'Washington': 'WA',\n    'West Virginia': 'WV',\n    'Wisconsin': 'WI',\n    'Wyoming': 'WY'\n}\n\n# map dict to clean full state names to abbreviations\ncities_cdpmeta_df['state'] = cities_cdpmeta_df['address_state'].map(us_state_abbrev)\n\n# infill non-matched from dict\ncities_cdpmeta_df['state'] = cities_cdpmeta_df['state'].fillna(cities_cdpmeta_df['address_state'])\ncities_cdpmeta_df['state'] = cities_cdpmeta_df['state'].replace({'ALBERTA':'AB'})\ncities_cdpmeta_df['address_city'] = cities_cdpmeta_df['address_city'].replace({'CALGARY':'Calgary'})\ncities_cdpmeta_df= cities_cdpmeta_df.drop(columns=['address_state'])\n\n# create joint city state variable\ncities_cdpmeta_df['city_state'] = cities_cdpmeta_df['address_city'].str.cat(cities_cdpmeta_df['state'],sep=\", \")\ncities_count = cities_cdpmeta_df[['organization', 'address_city', 'state', 'city_state']].groupby(['address_city', 'state', 'city_state']).count().sort_values(by = ['organization'],ascending = False).reset_index().rename(columns={'organization' : 'num_orgs'})\n\n# convert indexes to columns'\ncities_count.reset_index(inplace=True)\ncities_count = cities_count.rename(columns = {'index':'city_id'})\ncities_df.reset_index(inplace=True)\ncities_df = cities_df.rename(columns = {'index':'city_org_id'})\n\n# convert id and city label columns into lists\ncity_id_no = list_dedupe(cities_count['city_id'].tolist())\ncity_name = list_dedupe(cities_count['address_city'].tolist())\n\ncity_org_id_no = list_dedupe(cities_df['city_org_id'].tolist())\ncity_org_name = list_dedupe(cities_df['Organization'].tolist())\n\n# remove added index column in cities df\ncities_df.drop('city_org_id', inplace=True, axis=1)\ncities_count.drop('city_id', inplace=True, axis=1)\n\n# zip to join the lists and dict function to convert into dicts\ncity_dict = dict(zip(city_id_no, city_name))\ncity_org_dict = dict(zip(city_org_id_no, city_org_name))\n# compare dicts - matching when city name appears as a substring in the full city org name\ncity_names_df = pd.DataFrame(columns=['City ID No.','address_city', 'City Org ID No.','City Org', 'Match']) # initiate empty df\n\nfor ID, seq1 in city_dict.items():\n    for ID2, seq2 in city_org_dict.items():\n        m = re.search(seq1, seq2) # match string with regex search \n        if m:\n            match = m.group()\n            # Append rows in Empty Dataframe by adding dictionaries \n            city_names_df = city_names_df.append({'City ID No.': ID, 'address_city': seq1, 'City Org ID No.': ID2, 'City Org': seq2, 'Match' : match}, ignore_index=True)\n            \n# subset for city to city org name matches\ncity_names_df = city_names_df.loc[:,['address_city','City Org']]\ncities_count  = pd.merge(cities_count, city_names_df, on='address_city', how='left')\ncities_count = cities_count[['state','address_city', 'City Org']].rename(columns= { 'City Org': 'Organization'})\n","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"# join coordinates to cities count\ndef onlyUS(df):\n    data = df.loc[df['Country']=='United States of America'] \n    return data \ncities_count = pd.merge(left=cities_count, right=cities_meta_df, how='left', on=['address_city', 'state'])\ncities_count.loc[(cities_count['state']=='PA') & (cities_count['address_city']=='York'),'Organization'] ='City of Philadelphia'\nCities20= onlyUS(mergedcity20)\n#cities_count = cities_count.rename(columns ={'address_city': 'City'})\nUS_cities20 = pd.merge(Cities20,cities_count, how = 'right', on=['Organization'])\n","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"US_cities20 = US_cities20[US_cities20['Account Number'].notna()]","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"\n#graph1\ndef grp1(df):\n    data = df.groupby([\"City\", \"state\"]).size().reset_index(name='Total')\n    return data \nCity = grp1(US_cities20)\nCityfig1 = px.treemap(City,path=[px.Constant('US Cities'),'state','City'], values = \"Total\", color = 'state',\n                      title = 'Distribution of CDP Cities (US -2020)')\nCityfig1.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"#### Energy \n1. Does your city have a renewable energy or electricity target? \n"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"#cities8 = US_cities20.loc[US_cities20['Question Number'].str.startswith('8.0', na=False) & (US_cities20[\"Column Number\"]==2.0)]\ncities8 = US_cities20[US_cities20['Question Number']=='8.0']\ncities8 = cities8.groupby(['Response Answer','state']).size().reset_index(name = 'Total')\nyes_inprogress = cities8[(cities8['Response Answer']=='Yes') | (cities8['Response Answer']=='Intending to undertake in the next 2 years') |(cities8['Response Answer']=='In progress')]\nnope = cities8[cities8['Response Answer']=='Not intending to undertake']\nfig = px.bar(cities8, x=\"Response Answer\", y=\"Total\",\n          color=\"Response Answer\", title = \"Overall Response of Cities' Energy target/plan\"\n           )\nyes = px.treemap(yes_inprogress, path=[px.Constant('US Cities'),'state','Response Answer'], values = \"Total\", color = 'state',\n                      title = 'Reported having renewable energy or electricity target OR (in progress/intending to undertake)')\nno = px.treemap(nope, path=[px.Constant('US Cities'),'state','Response Answer'], values = \"Total\", color = 'state',\n                      title = 'Cities who reported having no energy target and not intending to undertake')\nfig.show();\nyes.show();\nno.show();","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"def converttostr(input_seq, seperator):\n    seperator = ' '\n   # Join all the strings in list\n    final_str = seperator.join(input_seq)\n    return final_str","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"#### Details of your renewable energy or electricity target(s) and how the city plans to meet those targets.\n\n1. Scale and Type "},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"\ncities8a = US_cities20[US_cities20['Question Number']=='8.0a']\ncities8a = cities8a.loc[cities8a['Response Answer'].notnull()]   \ncities8a1 = cities8a.loc[(cities8a['Column Number']==1.0)]\ncities8a2 = cities8a[cities8a['Column Number']==2.0]\ncities8a3 = cities8a[cities8a['Column Number']==3.0]\ncities8a1 = cities8a1.groupby(['Column Number', 'Response Answer']).size().reset_index(name = 'Total')\ncities8a2 = cities8a2.groupby(['Column Number', 'Response Answer']).size().reset_index(name = 'Total')\ncities8a3 = cities8a3.groupby(['Column Number', 'Response Answer']).size().reset_index(name = 'Total')\nfig83 = px.bar(cities8a3, x = 'Response Answer', y = 'Total', color = 'Response Answer',barmode = 'group', height = 400, width = 1333)\nfig81 = px.bar(cities8a1, x=\"Response Answer\", y=\"Total\",\n             color='Response Answer', barmode='group',\n             height=400, width = 777)\nfig82 =  px.bar(cities8a2, x=\"Response Answer\", y=\"Total\",\n             color='Response Answer', barmode='group',\n             height=400, width = 777)\nfig83.update_layout(\n    title = 'Energy/electricity types covered by target',\n    xaxis_visible=False)\nfig81.update_layout(\n    title = 'Energy Target Scale',\n    xaxis_visible=False)\nfig82.update_layout(\n    title = 'Energy Target Type',\n    xaxis_visible=False)\n","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"comment8a = cities8a.loc[(cities8a['Column Number']==4.0)|(cities8a['Column Number']==5.0)|(cities8a['Column Number']==6.0)|(cities8a['Column Number']==7.0)\n                         |(cities8a['Column Number']==8.0)\n                         |(cities8a['Column Number']==9.0)|(cities8a['Column Number']==10.0)|(cities8a['Column Number']==11.0)]\n\ncomment8a = comment8a['Response Answer']\nseperator = ' '\ncomment8a = converttostr(comment8a, seperator)\n# Generate the image\napp_words = ['https', 'Question not applicable', 'applicable', 'not', 'Question','consideration', 'approximately63', \"O'Shaughnessy\", 'participating', 'percentage']\nSTOPWORDS.update(app_words)\n\nplt.figure(figsize=(11, 11))\n\nc8response= WordCloud(max_words=100, min_word_length=11, background_color='black', colormap='Set2',collocations=False, stopwords = STOPWORDS).generate(comment8a)\nplt.imshow(c8response, interpolation='bilinear', alpha = 1)\nplt.title('Word Cloud analysis of of the cities plan to meet their Energy/Electricity Target')\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### EDA of CDP Corporation Data sets"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_kg_hide-output":true},"cell_type":"code","source":"#discloser >>water Security\ncordiswater18=pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Corporations/Corporations Disclosing/Water Security/2018_Corporates_Disclosing_to_CDP_Water_Security.csv\")\ncordiswater19=pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Corporations/Corporations Disclosing/Water Security/2019_Corporates_Disclosing_to_CDP_Water_Security.csv\")\ncordiswater20=pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Corporations/Corporations Disclosing/Water Security/2020_Corporates_Disclosing_to_CDP_Water_Security.csv\")\n\n#climate discloser\n#discloser >>Climate Change\ncordisclim18=pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Corporations/Corporations Disclosing/Climate Change/2018_Corporates_Disclosing_to_CDP_Climate_Change.csv\")\ncordisclim19=pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Corporations/Corporations Disclosing/Climate Change/2019_Corporates_Disclosing_to_CDP_Climate_Change.csv\")\ncordisclim20=pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Corporations/Corporations Disclosing/Climate Change/2020_Corporates_Disclosing_to_CDP_Climate_Change.csv\")","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_kg_hide-output":true},"cell_type":"code","source":"#REsponse> water Security\ncorwater18=pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Corporations/Corporations Responses/Water Security/2018_Full_Water_Security_Dataset.csv\")\ncorwater19=pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Corporations/Corporations Responses/Water Security/2019_Full_Water_Security_Dataset.csv\")\ncorwater20=pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Corporations/Corporations Responses/Water Security/2020_Full_Water_Security_Dataset.csv\")\n\n#Climate\n#REsponse> Climate Change\ncorclim18=pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Corporations/Corporations Responses/Climate Change/2018_Full_Climate_Change_Dataset.csv\")\ncorclim19=pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Corporations/Corporations Responses/Climate Change/2019_Full_Climate_Change_Dataset.csv\")\ncorclim20=pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Corporations/Corporations Responses/Climate Change/2020_Full_Climate_Change_Dataset.csv\")\n","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"def mergeCORPsurveydis(df1, df2):\n    mergecity = pd.merge(df1, df2, how = 'right', on = 'account_number')\n    return mergecity","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_kg_hide-output":true},"cell_type":"code","source":"#merge corporations discloser data + survey data (WATER)\nds18 = mergeCORPsurveydis(cordiswater18, corwater18)\nds19 = mergeCORPsurveydis(cordiswater19, corwater19)\nds20 = mergeCORPsurveydis(cordiswater20, corwater20)\n\n\n## merge corp discloser data + survey data (Climate_change)\ncorpclimate18 = mergeCORPsurveydis(cordisclim18, corclim18)\ncorpclimate19 = mergeCORPsurveydis(cordisclim19, corclim19)\ncorpclimate20 = mergeCORPsurveydis(cordisclim20, corclim20)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"#graph1\ndef grp1(df):\n    data = df.groupby(['country', 'primary_industry', 'survey_year_x']).size().reset_index(name='Total')\n    data = data.rename(columns={'primary_industry': 'industry', 'survey_year_x': 'Year'})\n    return data \n\n\n## WATER\ndfwater18 = grp1(ds18)\ndfwater19=grp1(ds19)\ndfwater20 = grp1(ds20)\ndfwater = pd.concat([dfwater18, dfwater19, dfwater20])\n\n###CLIMATE CHANGE\ndfclimate18 = grp1(corpclimate18)\ndfclimate19 = grp1(corpclimate19)\ndfclimate20= grp1(corpclimate20)\ndfclimate = pd.concat([dfclimate18, dfclimate19, dfclimate20])\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"##### 1. Distribution of Industry Discloser by Country from 2018 to 2020 (Water Security)"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"fig = px.bar(dfwater, x='industry', y='Total', color='country', facet_row='Year',\n                 color_discrete_map={\n                \"Canada\": px.colors.qualitative.Dark24[21],\n                \"United States of America\": px.colors.qualitative.Dark24[17]})\n        \nfig.update_layout(\n    height=800,\n    xaxis_title=\"\",\n    yaxis_title=\"Total\",    \n    font=dict(\n        family=\"Courier New\",\n        size=15,\n        color=px.colors.qualitative.Dark24[13],\n        \n        \n))\n\n\nfig.show();","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"##### 2. Distribution of Industry Discloser by Country from 2018 to 2020 (Climate Change)"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"fig = px.bar(dfclimate, x='industry', y='Total', color='country', facet_row='Year',\n                 color_discrete_map={\n                \"Canada\": px.colors.qualitative.Pastel[9],\n                \"United States of America\": px.colors.qualitative.Antique[3]})\n        \nfig.update_layout(\n    height=800,\n    xaxis_title=\"\",\n    yaxis_title=\"Total\",    \n    font=dict(\n        family=\"Courier New\",\n        size=15,\n        color=px.colors.qualitative.Safe[7],\n        \n        \n))\n\n\nfig.show();\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### EDA of US Corp Data Sets(2020)\n\n\n##### Corporation overlap between Water_Security and Climate_Change data sets.\n\nBelow Venn diagram shows 248 US companies participated in both Water and Climate survey for year 2020. \n"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"def onlyUS(df):\n    data = df.loc[df['country']=='United States of America'] \n    return data \n\nwater20=onlyUS(ds20)\nclimate20=onlyUS(corpclimate20)\n\n\n### account overlap (VENN DIagram)\nUSwater20 = water20[['account_number']]    \nUSclimate20 = climate20[['account_number']]\n\nvenn2([set(USwater20['account_number']), set(USclimate20['account_number'])],\n      set_labels = ('Water_Security', 'Climate_change'), set_colors=('purple', 'skyblue'), alpha = 0.7)\n\nplt.title('Overlap of account number')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### EDA of Climate Survey Data (US,2020)"},{"metadata":{"trusted":true},"cell_type":"code","source":"c2 = climate20.loc[climate20['question_number'].str.startswith('C6.1', na=False) & (climate20[\"column_number\"]==2.0)]\nc6= climate20.loc[(climate20['question_number']=='C6.1') & (climate20[\"column_number\"]==2.0)]\n#What were your organization’s gross global Scope 1 emissions in metric tons CO2e?\n\nc2 = climate20.loc[climate20['question_number'].str.startswith('C6.', na=False) & (climate20[\"column_number\"]==2.0)]\n### TO BE CONTINUED >>>>>>> \n#Will continue with my analysis  later, not enough time :( ","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}