{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"# **Introduction**\n\nClimate change disproportionately impacts underserved communities everywhere. While the manifestations of global warming vary based on geography, the burden of change will consistently be borne by the have-nots. Very little evidence exists to challenge this consensus.\n\nCDP facilitates a disclosure system for investors, companies, cities, states and regions to manage their environmental impacts. Through this process, we can gain insight into how their institutions are approaching this very serious issue. We can identify risks and seize opportunities. \n\nThis notebook is narrowly focused and oriented around policy at the city level. We are interested in climate change response planning and implementation specific to social inequity. \n","metadata":{}},{"cell_type":"markdown","source":"## Implementation","metadata":{"papermill":{"duration":0.041729,"end_time":"2020-10-09T16:48:20.217686","exception":false,"start_time":"2020-10-09T16:48:20.175957","status":"completed"},"tags":[]}},{"cell_type":"code","source":"import pandas as pd\nimport numpy as np\nimport matplotlib.pyplot as plt\nimport seaborn as sns\nimport re\nimport json\npd.options.display.max_seq_items = 2000\n\nimport seaborn as sns\n\nfrom sklearn.cluster import KMeans\nfrom sklearn.feature_extraction.text import CountVectorizer ","metadata":{"papermill":{"duration":3.774252,"end_time":"2020-10-09T16:48:24.035223","exception":false,"start_time":"2020-10-09T16:48:20.260971","status":"completed"},"tags":[],"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Data\n\n### Import Data","metadata":{"papermill":{"duration":0.042097,"end_time":"2020-10-09T16:48:24.122194","exception":false,"start_time":"2020-10-09T16:48:24.080097","status":"completed"},"tags":[]}},{"cell_type":"code","source":"# import city response data\ncities_df = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Cities/Cities Responses/2020_Full_Cities_Dataset.csv\")\ncities_df.columns= cities_df.columns.str.lower()","metadata":{"papermill":{"duration":8.02188,"end_time":"2020-10-09T16:48:32.186442","exception":false,"start_time":"2020-10-09T16:48:24.164562","status":"completed"},"tags":[],"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cities_df.columns","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Note that the raw data is hierarchical. Under each section there are subsections pertinent to the topic. Below, I drill down to the responses that I interpret as being most relevant to our goal.","metadata":{}},{"cell_type":"code","source":"cities_df['section'].unique()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Limit dataset to America\nus_df = cities_df[cities_df['country'] == 'United States of America']\nus_df = us_df.drop(columns=['questionnaire','year reported to cdp','country','cdp region','comments','file name', 'last update'])\nus_df['city'] = us_df['organization'].str.replace('City of ', '')","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"number_of_cities = len(us_df.organization.unique())\nprint(\"We have responses from {} American cities\".format(number_of_cities))","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"us_climate_df = us_df[us_df['parent section'] == 'Climate Hazards and Vulnerability']\nus_climate_df['question name'].unique()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The challenge with this data, and text data more generally, is that it lacks form - particularly when the questions are open-ended as we see above. Below, I select the four questions that are the most directionally interesting to our question of what's been done to help the most vulnerable. ","metadata":{}},{"cell_type":"code","source":"prep_q1 = 'Based on the climate hazards identified as \"high risk\" in your city, have you identified climate exposure scenarios?'\nprep_q2 = 'Have you identified the most vulnerable geographic areas in your city?'\nprep_q3 = 'Have you compiled information related to climate risk, vulnerabilities, and adaptive capacities into a baseline synthesis report?'\nprep_q4 = 'Has a climate change risk and vulnerability assessment been undertaken for your city?'\nq1 = us_climate_df[us_climate_df['question name'] == prep_q1]\nq2 = us_climate_df[us_climate_df['question name'] == prep_q2]\nq3 = us_climate_df[us_climate_df['question name'] == prep_q3]\nq4 = us_climate_df[us_climate_df['question name'] == prep_q4]","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"For the purposes of our analysis, responses such as 'not applicable' or 'do not know' will be treated as 'no'.","metadata":{}},{"cell_type":"code","source":"q1['response answer'] = q1['response answer'].str.replace('Question not applicable', 'No')\nq1['response answer'] = q1['response answer'].str.replace('Do not know', 'No')\nq1[q1['response answer'] != 'Yes'] = 'No'\nq2['response answer'] = q2['response answer'].str.replace('Question not applicable', 'No')\nq2['response answer'] = q2['response answer'].str.replace('Do not know', 'No')\nq2[q2['response answer'] != 'Yes'] = 'No'\nq3['response answer'] = q3['response answer'].str.replace('Question not applicable', 'No')\nq3['response answer'] = q3['response answer'].str.replace('Do not know', 'No')\nq3[q3['response answer'] != 'Yes'] = 'No'\nq4[q4['response answer'] != 'Yes'] = 'No'","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig, axarr = plt.subplots(2, 2, figsize=(12, 8))\n\nq1['response answer'].value_counts().plot.bar(\n    ax=axarr[0][0],  color=['salmon', 'lightblue']\n)\naxarr[0][0].set_title(\"2.1b: Identified Climate Exposure Scenarios\")\naxarr[0][0].set_xlabel(\"Task Accomplished\")\naxarr[0][0].set_ylabel(\"No. of Cities\")\n\nq2['response answer'].value_counts().plot.bar(\n    ax=axarr[0][1],  color=['salmon', 'lightblue']\n)\naxarr[0][1].set_title(\"2.1a: Identified Vulnerable Geographies\")\naxarr[0][1].set_xlabel(\"Task Accomplished\")\naxarr[0][1].set_ylabel(\"No. of Cities\")\n\nq3['response answer'].value_counts().plot.bar(\n    ax=axarr[1][0],  color=['salmon', 'lightblue']\n)\naxarr[1][0].set_title(\"2.1c: Baseline Synthesis Report\")\naxarr[1][0].set_xlabel(\"Task Accomplished\")\naxarr[1][0].set_ylabel(\"No. of Cities\")\n\nq4['response answer'].value_counts().sort_index().plot.bar(\n    ax=axarr[1][1],  color=['salmon', 'lightblue']\n)\naxarr[1][1].set_title(\"2.1d: Risk and Vulnerability Assessment\")\naxarr[1][1].set_xlabel(\"Task Accomplished\")\naxarr[1][1].set_ylabel(\"No. of Cities\")\n\nplt.subplots_adjust(hspace=.3)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"With the exception of the risk and vulnerability assessment (2.1d), we can reasonably conclude that, to the extent planning has taken place, the emphasis has not been on securing vulnerable populations. Climate exposure scenario planning, for example, would be a critical path item to addressing social inequity; the most likely outcomes would be rise to the surface and hopefully precipitate a discussion. \n\nLet's review the 'factors impacting cities' responses to see what the perceived obstacles are.","metadata":{}},{"cell_type":"code","source":"factors = 'Please identify and describe the factors that most greatly affect your city’s ability to adapt to climate change and indicate how those factors either support or challenge this ability.'\nfactors_responses = us_climate_df[us_climate_df['question name'] == factors]\ncorpus = factors_responses['response answer'].fillna(' ').values","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"stop_words = ['city', 'community', 'capacity', 'climate', 'plan', 'columbus', 'program', 'population', 'the', 'and', 'to', 'of', 'in', 'is','can', 'may','for', 'are', 'as', 'has', 'with', 'will','have','this','that', 'on', 'our', 'be', 'not', 'by', 'it', 'over', 'other', 'or', 'an', 'at', 'who', 'its', 'there', 'well', 'use','most','one', 'due', 'but', 'years', 'through', 'make', 'while','impact']\nvectorizer = CountVectorizer(stop_words=stop_words, ngram_range=(1,3))\nmatrix = vectorizer.fit_transform(corpus)\nfreqs = zip(vectorizer.get_feature_names(), matrix.sum(axis=0).tolist()[0])\nprint(sorted(freqs, key=lambda x: -x[1]))","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The first two responses are interesting, albeit not surprising: cities feel more challenged than supported. The first two specific policy topics, housing and infrastructure, tell some sort of cost story and I wonder if there is a relationship between those responses and the three bunched together further down the line: 'income', 'cost', 'economic', 'funding'. \n\nLastly, 'conditions maintenance', 'infrastructure conditions' and 'infrastructure conditions maintenance' each showed up 50 times. The language is specific enough there to warrant further investigation. Let's see if we can bring definition to that theme through kmeans clustering.","metadata":{}},{"cell_type":"code","source":"true_k = 5\nmodel = KMeans(n_clusters=true_k, init='k-means++', max_iter=100, n_init=1)\nmodel.fit(matrix)\ny_kmeans = model.predict(matrix)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(\"Top terms per cluster:\")\norder_centroids = model.cluster_centers_.argsort()[:, ::-1]\nterms = vectorizer.get_feature_names()\nfor i in range(true_k):\n    top_ten_words = [terms[ind] for ind in order_centroids[i, :4]]\n    print(\"Cluster {}: {}\".format(i, ' '.join(top_ten_words)))","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Cluster 0 and 1 offer the clearest message. Cluster 0 suggests a story about lack of funding for environmental programs relevant to climate change since their inception. Cluster 1 is where we see the first indication of infrastructure. My hypothesis is that the conditions maintenance terms we saw above are describing the poor state of public infrastructure and how that is likely to present a risk. ","metadata":{}},{"cell_type":"markdown","source":"# Summary\n\nWhile governments at all level search for a constructive and responsible response to climate change, cities are struggling to answer the call. Planning to date has been inadequate, in large part as a result of not having the money to support programs that are already in place.","metadata":{}},{"cell_type":"markdown","source":"# Contact\n\nTodd Hendricks\\\nhendricks.ta@gmail.com\\\nhttps://www.linkedin.com/in/todd-hendricks/","metadata":{}},{"cell_type":"markdown","source":"**END OF NOTEBOOK**","metadata":{}},{"cell_type":"code","source":"# state abbreviation dictionary\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=\", \")\n\ncities_cdpmeta_df","metadata":{"papermill":{"duration":0.115948,"end_time":"2020-10-09T16:48:39.920093","exception":false,"start_time":"2020-10-09T16:48:39.804145","status":"completed"},"tags":[],"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Summarise the cities metadata to count the number organisations (HQ) per city ","metadata":{"papermill":{"duration":0.045638,"end_time":"2020-10-09T16:48:40.011537","exception":false,"start_time":"2020-10-09T16:48:39.965899","status":"completed"},"tags":[]}},{"cell_type":"code","source":"cities_count = cities_cdpmeta_df[['organization', 'address_city', 'state', 'city_state']]\\\n        .groupby(['address_city', 'state', 'city_state']).count().\\\n            sort_values(by = ['organization'],ascending = False)\\\n                .reset_index()\\\n                    .rename(columns={'organization' : 'num_orgs'})\ncities_count.head()","metadata":{"papermill":{"duration":0.079589,"end_time":"2020-10-09T16:48:40.13635","exception":false,"start_time":"2020-10-09T16:48:40.056761","status":"completed"},"tags":[],"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"City name conversion\n\n- Align City names in CDP City questionnaire response data ('City Org') with common city names that may be present in external data sets\n- e.g. 'City of Boulder' -> Boulder\n\n*Note* This data quality control step can also be addressed by using the 'City' column in the 2019_Cities_Disclosing_to_CDP.csv dataset","metadata":{"papermill":{"duration":0.046244,"end_time":"2020-10-09T16:48:40.228968","exception":false,"start_time":"2020-10-09T16:48:40.182724","status":"completed"},"tags":[]}},{"cell_type":"code","source":"# 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))","metadata":{"papermill":{"duration":0.761369,"end_time":"2020-10-09T16:48:41.036066","exception":false,"start_time":"2020-10-09T16:48:40.274697","status":"completed"},"tags":[],"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 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']]\n\ncity_names_df.head()","metadata":{"papermill":{"duration":0.802654,"end_time":"2020-10-09T16:48:41.884558","exception":false,"start_time":"2020-10-09T16:48:41.081904","status":"completed"},"tags":[],"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Join city_org names to city-org count table\n","metadata":{"papermill":{"duration":0.047466,"end_time":"2020-10-09T16:48:41.978419","exception":false,"start_time":"2020-10-09T16:48:41.930953","status":"completed"},"tags":[]}},{"cell_type":"code","source":"cities_count  = pd.merge(cities_count, city_names_df, on='address_city', how='left')\ncities_count.head()","metadata":{"papermill":{"duration":0.074655,"end_time":"2020-10-09T16:48:42.099072","exception":false,"start_time":"2020-10-09T16:48:42.024417","status":"completed"},"tags":[],"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Join Count of Disclosing Organisations in HQ Cities with Question 6.2 Response dataframe\n\n- Label the response variable as a city's current Sustainability Project Collaboration","metadata":{"papermill":{"duration":0.046312,"end_time":"2020-10-09T16:48:42.192077","exception":false,"start_time":"2020-10-09T16:48:42.145765","status":"completed"},"tags":[]}},{"cell_type":"code","source":"cities_6_2 = cities_6_2[['City', 'Response Answer']].rename(columns={'City' : 'City Org'})\ncities_count = pd.merge(left=cities_count, right=cities_6_2, how='left', \n                        on ='City Org').rename(columns={'Response Answer' : 'Sustainability Project Collab.'})\n\ncities_count['Sustainability Project Collab.'] = cities_count['Sustainability Project Collab.'].fillna('No Response')","metadata":{"papermill":{"duration":0.066304,"end_time":"2020-10-09T16:48:42.306582","exception":false,"start_time":"2020-10-09T16:48:42.240278","status":"completed"},"tags":[],"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Plot cities containing the highest proportion of organisations disclosing to CDP\n\n- Highlight number of disclosing orgnanisations with a HQ in the city\n- Highlight the city's response to question 6.2 as bar colour\n","metadata":{"papermill":{"duration":0.047171,"end_time":"2020-10-09T16:48:42.402094","exception":false,"start_time":"2020-10-09T16:48:42.354923","status":"completed"},"tags":[]}},{"cell_type":"code","source":"cities_count_50 = cities_count.iloc[0:40,:]\n\nplt.figure(figsize=(15,8))\nax = sns.barplot(\n    x=\"city_state\", y=\"num_orgs\",\n    hue = \"Sustainability Project Collab.\",\n    data=cities_count_50 ,\n    palette=\"OrRd_r\"\n)\n\nplt.xticks(\n    rotation=45, \n    horizontalalignment='right',\n    fontweight='light',\n    fontsize='medium'  \n)\n","metadata":{"papermill":{"duration":1.565062,"end_time":"2020-10-09T16:48:44.013702","exception":false,"start_time":"2020-10-09T16:48:42.44864","status":"completed"},"tags":[],"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Spatial plot of cities and organisation mapping\n\n[Example bubble map with plotting with plotly](https://plotly.com/python/bubble-maps/)","metadata":{"papermill":{"duration":0.048539,"end_time":"2020-10-09T16:48:44.111685","exception":false,"start_time":"2020-10-09T16:48:44.063146","status":"completed"},"tags":[]}},{"cell_type":"code","source":"# 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()","metadata":{"papermill":{"duration":0.074641,"end_time":"2020-10-09T16:48:44.235291","exception":false,"start_time":"2020-10-09T16:48:44.16065","status":"completed"},"tags":[],"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# join coordinates to cities count\ncities_count = pd.merge(left=cities_count, right=cities_meta_df, how='left', on=['address_city', 'state'])\n\n# convert text response to question 6.2 to an integar encoding \nresp_int_df = cities_count[[\"Sustainability Project Collab.\"]]\nresp_int_df= resp_int_df.rename(columns={'Sustainability Project Collab.' : 'resp_int'})\n\nlabels = resp_int_df['resp_int'].unique().tolist()\nmapping = dict( zip(labels,range(len(labels))) )\nresp_int_df.replace({'resp_int': mapping},inplace=True)\n\nresp_list = resp_int_df['resp_int'].tolist()\ncities_count['resp_int'] = resp_list \ncities_count.head()","metadata":{"papermill":{"duration":0.102905,"end_time":"2020-10-09T16:48:44.390105","exception":false,"start_time":"2020-10-09T16:48:44.2872","status":"completed"},"tags":[],"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"- Highlight number of disclosing orgnanisations with a HQ in the city via bubble size\n- Highlight city's response to question 6.2 as bubble colour and highlight in hover box","metadata":{"papermill":{"duration":0.050706,"end_time":"2020-10-09T16:48:44.490878","exception":false,"start_time":"2020-10-09T16:48:44.440172","status":"completed"},"tags":[]}},{"cell_type":"code","source":"# plot spatial bubble map\ncities_count['text'] = cities_count['address_city'] + '<br>Number of Orgs: ' + (cities_count['num_orgs']).astype(str) +\\\n    '<br>Sustainability Project Colloboration: ' + (cities_count['Sustainability Project Collab.']).astype(str)\nlimits = [(0,20),(21,40),(41,60),(61,80),(81,100)]\ncities = []\nscale = 5\n\nfig = go.Figure()\n\nfor i in range(len(limits)):\n    lim = limits[i]\n    fig.add_trace(go.Scattergeo(\n        locationmode = 'USA-states',\n        lon = cities_count['lng'],\n        lat = cities_count['lat'],\n        text = cities_count['text'],\n        marker = dict(\n            size = cities_count['num_orgs']*scale,\n            color = cities_count['resp_int'],\n            line_color='rgb(40,40,40)',\n            line_width=0.5,\n            sizemode = 'area'\n        ),\n        name = '{0} - {1}'.format(lim[0],lim[1])))\n\nfig.update_layout(\n        title_text = '2019 CDP Climate Change Corporate Responders (Public) by City',\n        showlegend = False,\n        geo = dict(\n            scope = 'usa',\n            landcolor = 'rgb(217, 217, 217)',\n        )\n    )\n\nfig.show()","metadata":{"papermill":{"duration":0.313552,"end_time":"2020-10-09T16:48:44.854789","exception":false,"start_time":"2020-10-09T16:48:44.541237","status":"completed"},"tags":[],"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"####  Build NYC City Specific Dataset\n\nCombine SVI dataset with CDP City Questionnaire and Organisation level 2019 Climate Change questionnaire response data\n\nE.g. :\n- Identify which organisations located within NYC see climate-related opportunities within their operations\n- Match organisations with areas of the city that suffer from high unemployment rates\n- Pinpoint areas of NYC that present an opportunity for corporate collobaration and therefore an uplift in social equity metrics","metadata":{"papermill":{"duration":0.094305,"end_time":"2020-10-09T16:48:45.041283","exception":false,"start_time":"2020-10-09T16:48:44.946978","status":"completed"},"tags":[]}},{"cell_type":"markdown","source":"Subset climate change questionnaire response data for question C2.4a\n\n*C2.4a Provide details of opportunities identified with the potential to have a substantive financial or strategic impact on your business.*\n\n(see [CDP Climate Change questionnaire guidance](https://guidance.cdp.net/en/guidance?cid=13&ctype=theme&idtype=ThemeID&incchild=1&microsite=0&otype=Questionnaire&tags=TAG-646%2CTAG-605%2CTAG-600))","metadata":{"papermill":{"duration":0.090456,"end_time":"2020-10-09T16:48:45.223112","exception":false,"start_time":"2020-10-09T16:48:45.132656","status":"completed"},"tags":[]}},{"cell_type":"code","source":"cc_2_4a = cc_df[cc_df['question_number'] == 'C2.4a']","metadata":{"papermill":{"duration":0.321496,"end_time":"2020-10-09T16:48:45.655191","exception":false,"start_time":"2020-10-09T16:48:45.333695","status":"completed"},"tags":[],"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Join 2019 corporate responses with organisation HQ metadata, matching climate change questionnaire organisations to their HQ city\n\n","metadata":{"papermill":{"duration":0.089502,"end_time":"2020-10-09T16:48:45.834778","exception":false,"start_time":"2020-10-09T16:48:45.745276","status":"completed"},"tags":[]}},{"cell_type":"code","source":"cities_cdpmeta_join = cities_cdpmeta_df[[\"account_number\", 'survey_year', 'address_city']]\ncc_2_4a = pd.merge(left=cc_2_4a, right=cities_cdpmeta_join,  left_on=['account_number','survey_year'], right_on = ['account_number','survey_year'])","metadata":{"papermill":{"duration":0.192627,"end_time":"2020-10-09T16:48:46.122075","exception":false,"start_time":"2020-10-09T16:48:45.929448","status":"completed"},"tags":[],"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Subset for NYC HQ Cities\n","metadata":{"papermill":{"duration":0.090517,"end_time":"2020-10-09T16:48:46.305358","exception":false,"start_time":"2020-10-09T16:48:46.214841","status":"completed"},"tags":[]}},{"cell_type":"code","source":"cc_nyc = cc_2_4a[(cc_2_4a['address_city'] =='New York')]","metadata":{"papermill":{"duration":0.134154,"end_time":"2020-10-09T16:48:46.53064","exception":false,"start_time":"2020-10-09T16:48:46.396486","status":"completed"},"tags":[],"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Join City Linked C2.4a response data to citys question 6.2 response data frame, matching NYC city level responses to NYC organisation level climate change questionnaire responses\n","metadata":{"papermill":{"duration":0.089032,"end_time":"2020-10-09T16:48:46.709349","exception":false,"start_time":"2020-10-09T16:48:46.620317","status":"completed"},"tags":[]}},{"cell_type":"code","source":"cities_6_2['City Org'] = cities_6_2['City Org'].replace({'New York City':'New York'})\ncc_nyc = pd.merge(left=cc_nyc, right= cities_6_2,  left_on=['address_city'], right_on = ['City Org']).rename(columns={'Response Answer' : 'sustain_collab'})\ncc_nyc.head()","metadata":{"papermill":{"duration":0.144262,"end_time":"2020-10-09T16:48:46.943504","exception":false,"start_time":"2020-10-09T16:48:46.799242","status":"completed"},"tags":[],"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Subset SVI data for NYC Counties from Federal Information Processing Standard (FIPS) codes\n\n* The Bronx is Bronx County (ANSI / FIPS 36005)\n* Brooklyn is Kings County (ANSI / FIPS 36047)\n* Manhattan is New York County (ANSI / FIPS 36061)\n* Queens is Queens County (ANSI / FIPS 36081)\n* Staten Island is Richmond County (ANSI / FIPS 36085)\n\n(source: https://guides.newman.baruch.cuny.edu/nyc_data)","metadata":{"papermill":{"duration":0.091746,"end_time":"2020-10-09T16:48:47.128377","exception":false,"start_time":"2020-10-09T16:48:47.036631","status":"completed"},"tags":[]}},{"cell_type":"code","source":"nyc_svi_df = svi_df[svi_df['STCNTY'].isin([36005, 36047, 36061, 36081, 36085])]\nnyc_svi_df['City'] = 'New York'\nprint(nyc_svi_df.shape)\nnyc_svi_df.head()","metadata":{"papermill":{"duration":0.138674,"end_time":"2020-10-09T16:48:47.35727","exception":false,"start_time":"2020-10-09T16:48:47.218596","status":"completed"},"tags":[],"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Spatial Plotting\n\n- Choropleth Map of NYC SVI Data, displaying unemployment rates across NYC","metadata":{"papermill":{"duration":0.093489,"end_time":"2020-10-09T16:48:47.543645","exception":false,"start_time":"2020-10-09T16:48:47.450156","status":"completed"},"tags":[]}},{"cell_type":"code","source":"# import shapefile of NYC census tracts\ngeodf = gpd.read_file('../input/cdp-unlocking-climate-solutions/Supplementary Data/NYC CDP Census Tract Shapefiles/nyu_2451_34505.shp')\n\n# join geospatial data to SVI unemployment rates ('E_UNEMP')\ngdf_join = geodf[['tractid', 'geometry']].to_crs('+proj=robin')\nnyc_join =  nyc_svi_df[['E_UNEMP', 'FIPS']]\ngdf_join[\"tractid\"] = pd.to_numeric(geodf[\"tractid\"])\ngdf_nyc = pd.merge(left=gdf_join, right=nyc_join, how='left', left_on='tractid', right_on = 'FIPS')\ngdf_nyc.head()","metadata":{"papermill":{"duration":1.62248,"end_time":"2020-10-09T16:48:49.258457","exception":false,"start_time":"2020-10-09T16:48:47.635977","status":"completed"},"tags":[],"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# plot unemployment rate variation across NYC\ncolors = 5\ncmap = 'RdPu'\nfigsize = (16, 10)\nax = gdf_nyc.dropna().plot(column='E_UNEMP', cmap=cmap, figsize=figsize, scheme='equal_interval', k=colors, legend=True)\nax.set_title('CDV SVI Civilian (age 16+) unemployed estimate by NYC Census Tract', fontdict={'fontsize': 16}, loc='center')\nax.get_legend().set_bbox_to_anchor((.40, .8))","metadata":{"papermill":{"duration":2.505078,"end_time":"2020-10-09T16:48:51.855963","exception":false,"start_time":"2020-10-09T16:48:49.350885","status":"completed"},"tags":[],"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Join Neighbourhood level SVI data to Corporate and City level CDP Response Data for NYC\n\ne.g for the Bronx","metadata":{"papermill":{"duration":0.098468,"end_time":"2020-10-09T16:48:52.053633","exception":false,"start_time":"2020-10-09T16:48:51.955165","status":"completed"},"tags":[]}},{"cell_type":"code","source":"del cc_df \ndel cities_df \ndel svi_df\ndel cities_meta_df \ndel cities_cdpmeta_df\ndel cities_6_2\ndel cities_count\ndel cc_2_4a","metadata":{"papermill":{"duration":0.233961,"end_time":"2020-10-09T16:48:52.385529","exception":false,"start_time":"2020-10-09T16:48:52.151568","status":"completed"},"tags":[],"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# subset for Bronx\nbb_df = nyc_svi_df[(nyc_svi_df.COUNTY =='Bronx')]\n\n# join to city and climate change response data\nprint(cc_nyc.shape)\ncc_nyc = cc_nyc.rename(columns={'City Org' : 'City'})\nnyc_df = pd.merge(cc_nyc,bb_df,on='City',how='outer')\nprint(nyc_df.shape)","metadata":{"papermill":{"duration":3.681482,"end_time":"2020-10-09T16:48:56.165659","exception":false,"start_time":"2020-10-09T16:48:52.484177","status":"completed"},"tags":[],"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"nyc_df.head()","metadata":{"papermill":{"duration":0.171722,"end_time":"2020-10-09T16:48:56.435538","exception":false,"start_time":"2020-10-09T16:48:56.263816","status":"completed"},"tags":[],"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### Water Responses\n\nIdentify organisations with facilities oeprating in the Hudson river basin, flagging companies who's operations may impact NYC's major fresh water resource\n","metadata":{"papermill":{"duration":0.097621,"end_time":"2020-10-09T16:48:56.633754","exception":false,"start_time":"2020-10-09T16:48:56.536133","status":"completed"},"tags":[]}},{"cell_type":"code","source":"ws_df_4_1c = ws_df[ws_df['question_number'] == 'W4.1c']\nws_df_4_1c = ws_df_4_1c[ws_df_4_1c['response_value'].notnull()]\nws_df_4_1c.head()         ","metadata":{"papermill":{"duration":0.163176,"end_time":"2020-10-09T16:48:56.895978","exception":false,"start_time":"2020-10-09T16:48:56.732802","status":"completed"},"tags":[],"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Reshape data\n\n- Climate change and water response datasets are often presented in long format in the CDP datasets.\n- These data sets will become more useful when widened on the 'column_name' variable, enabling you to derive measurable metrics and KPIs from questionnaire response data","metadata":{"papermill":{"duration":0.099903,"end_time":"2020-10-09T16:48:57.09661","exception":false,"start_time":"2020-10-09T16:48:56.996707","status":"completed"},"tags":[]}},{"cell_type":"code","source":"# pivot data\nws_df_4_1c_wide = ws_df_4_1c.pivot_table(index=['account_number', 'organization', 'row_number'],\n                                     columns='column_name', \n                                     values='response_value',\n                                     aggfunc=lambda x: ' '.join(x)).reset_index()\n# identify orgs with facilities within the Hudson river basin\nws_df_4_1c_wide = ws_df_4_1c_wide[ws_df_4_1c_wide['W4.1c_C2River basin'].str.contains('Hudson', na=False)]\nws_df_4_1c_wide.head()","metadata":{"papermill":{"duration":0.182977,"end_time":"2020-10-09T16:48:57.378967","exception":false,"start_time":"2020-10-09T16:48:57.19599","status":"completed"},"tags":[],"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"ws_df.head()","metadata":{"papermill":{"duration":0.132044,"end_time":"2020-10-09T16:48:57.611431","exception":false,"start_time":"2020-10-09T16:48:57.479387","status":"completed"},"tags":[],"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Modelling\n\n#### What next?\n\nSuggested analysis and modelling techniques that you can be apply as you tackle the [competitions problem statement](https://www.kaggle.com/c/cdp-unlocking-climate-solutions/overview/description).\n\nSuggestions below are **only** a guide. You are not limited to these approaches -  use your imagination and publically available data to tackle this challenge from any angle you can dream of! \n\n\n**NLP principles to investigate the social-environmental overlap between Corporations and Cities Climate Change 'Readiness'**\n\n- Utilise pythons NLP capabilities and tokenization approaches such as [Term Frequency–inverse Document Frequency (TF-IDF)](https://medium.com/analytics-vidhya/getting-started-with-nlp-tokenization-document-term-matrix-tf-idf-2ea7d01f1942) (1) to construct a Document Term Matrix (DTM) from questionnaire responses, highlighting key terms in free text answers to aid in topic identification\n\n        - e.g. summarise city 'readiness' for climate change and the hazards they anticipate (Cities Question 2.1)\n        - e.g outline the future adaptations cities must implement to prepare for environmental challenges (City Question 3.0)\n        - e.g. find common topics in examples of colloboration between cities and business on sustainability projects (City Question 6.2a)\n\n- Apply [sentiment analysis](https://www.ncbi.nlm.nih.gov/pmc/articles/PMC6267440/) to detect whether a city sees opportunity (positive sentiment/polarity) (Cities Question 6.0) or concern (negative sentiment/polarity) (City Question 2.2) over future climate scenarios\n\n\n- Combine DTM and Sentiment analysis to build a combined KPI that incorporates measures of sentiment and susceptibility into one metric, identifying cities with high levels of percieved risk who may be open to colloboration with business as they foster climate resilience.\n\n        - e.g. Sentiment x Susceptibility  = Climate Risk Sensitivity Score\n\n\n\n**Social Accounting with Water Shadow Price Modeling**\n\nUsing external datasets and water-related risks identified by Corporations (Water Security Question W4.2), build a 'Shadow Price' of water for Corporations operating in a selection of North American cities. \n\n- A [shadow price](https://www.fir-pri-awards.org/wp-content/uploads/MasterThesis_Chisem.pdf) (3) can attempt to account for the total cost of a Corporations water use, estimating all internal and external costs ,as well as exposure to water stress. \n\n- The shadow price coefficient can be combined within volumetric withdrawal data (Water Security Question W5.1a) to assign a Water Risk Cost per company, weighting corporate activties with a measure of the inersection between environmental risks and social impact.\n\n    - e.g. Water risk cost for Company  = Shadow price for Company  * Water withdrawal volume for Company \n\n\n\n**References**\n\n1. Muñoz (2020). Getting started with NLP: Tokenization, Document-Term Matrix, TF-IDF. Medium. https://medium.com/analytics-vidhya/getting-started-with-nlp-tokenization-document-term-matrix-tf-idf-2ea7d01f1942\n\n2. Reyes-Menendez A, Saura JR, Alvarez-Alonso C. Understanding #WorldEnvironmentDay User Opinions in Twitter: A Topic-Based Sentiment Analysis Approach. Int J Environ Res Public Health. 2018;15(11):2537. Published 2018 Nov 13. doi:10.3390/ijerph15112537. https://www.ncbi.nlm.nih.gov/pmc/articles/PMC6267440/\n\n3. FIR-PRI. Portfolio Analysis Using Water Shadow Pricing: How Valuing Water Risk Can Reduce Carbon Emissions. https://www.fir-pri-awards.org/wp-content/uploads/MasterThesis_Chisem.pdf","metadata":{"papermill":{"duration":0.100476,"end_time":"2020-10-09T16:48:57.812223","exception":false,"start_time":"2020-10-09T16:48:57.711747","status":"completed"},"tags":[]}},{"cell_type":"code","source":"","metadata":{"papermill":{"duration":0.103026,"end_time":"2020-10-09T16:48:58.063366","exception":false,"start_time":"2020-10-09T16:48:57.96034","status":"completed"},"tags":[],"trusted":true},"execution_count":null,"outputs":[]}]}