{"cells":[{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python Docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load\n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\nimport matplotlib.pyplot as plt\n# Input data files are available in the read-only \"../input/\" directory\n# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory\n\nimport os\n# 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#for dirname, _, filenames in os.walk('/kaggle/input'):\n    #for filename in filenames:\n        #print(os.path.join(dirname, filename))\n\n# You can write up to 5GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using \"Save & Run All\" \n# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Let us visualize most impactful actions different cities are planning to take - this is found in responses to Question 5.4. Likewise we will visualize the most transformative actions regarding vulnerable groups that different cities are planning to take - responses to question 3.5.\nThe idea is to match the themes that cities have around these with the themes that corporates with HQ in respective cities have. These highly aligned corporations and cities present an opportunity to accelerate the actions. Also, from picking the companies that are likely to outperform from positive ESG actions, this is likely to be a good signal, all other things being equal.\n\nTo begin with, let's visualize across different regions - USA, UK and Australia."},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"cities_df = pd.read_csv(\"/kaggle/input/cdp-unlocking-climate-solutions/Cities/Cities Responses/2020_Full_Cities_Dataset.csv\")\n#Let's visualize Q5.4 most impactful city actions and Q3.5 vulnerable groups city action\nusa_cities = cities_df[cities_df['Country'] == 'United States of America']\nusa_SVI_action_3_5 = usa_cities[(usa_cities['Question Number'] == '3.5') & (usa_cities['Response Answer'] != 'Question not applicable')]\n\nuk_cities = cities_df[cities_df['Country'] == 'United Kingdom of Great Britain and Northern Ireland']\nuk_SVI_action_3_5 = uk_cities[(uk_cities['Question Number'] == '3.5') & (uk_cities['Response Answer'] != 'Question not applicable')]\n\naus_cities = cities_df[cities_df['Country'] == 'Australia']\naus_SVI_action_3_5 = aus_cities[(aus_cities['Question Number'] == '3.5') & (aus_cities['Response Answer'] != 'Question not applicable')]\n\n\nfrom wordcloud import WordCloud, STOPWORDS\nvul_stop_words = [\"City\", \"city\", \"provides\",\"nan\",\"provided\",\"helped\",\"will\",\"vulnerable\",\"including\",\"government\",\"people\",\"vulnerability\",\"action\",\"https\",\"program\",\"project\",\"Implementation\",\"plan\",\"policy\"] + list(STOPWORDS)\nusa_svi_wordcloud = WordCloud(stopwords=vul_stop_words).generate(' '.join(map(str, usa_SVI_action_3_5['Response Answer'])))\nuk_svi_wordcloud = WordCloud(stopwords=vul_stop_words).generate(' '.join(map(str, uk_SVI_action_3_5['Response Answer'])))\naus_svi_wordcloud = WordCloud(stopwords=vul_stop_words).generate(' '.join(map(str, aus_SVI_action_3_5['Response Answer'])))\nplt.figure()\nfig, (ax0, ax1,ax2) = plt.subplots(1, 3,figsize=(50,25))\nfig.suptitle('City DEI Themes',fontsize=32)\nax0.set_title(\"USA\",fontsize=32)\nax1.set_title(\"UK\",fontsize=32)\nax2.set_title(\"Australia\",fontsize=32)\n\n\nax0.imshow(usa_svi_wordcloud, interpolation=\"bilinear\")\nax1.imshow(uk_svi_wordcloud, interpolation=\"bilinear\")\nax2.imshow(aus_svi_wordcloud, interpolation=\"bilinear\")\n\nplt.axis(\"off\")\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"usa_SVI_action_5_4 = usa_cities[(usa_cities['Question Number'] == '5.4') & (usa_cities['Response Answer'] != 'Question not applicable')]\nuk_SVI_action_5_4 = uk_cities[(uk_cities['Question Number'] == '5.4') & (uk_cities['Response Answer'] != 'Question not applicable')]\naus_SVI_action_5_4 = aus_cities[(aus_cities['Question Number'] == '5.4') & (aus_cities['Response Answer'] != 'Question not applicable')]\n\nimpact_stop_words = [\"City\", \"city\", \"provides\",\"nan\",\"provided\",\"helped\",\"will\",\"vulnerable\",\"including\",\"government\",\"people\",\"vulnerability\",\"action\",\"https\",\"program\",\"programme\",\"scheme\",\"Improved\",\"Reduced\",\"reduced\",\"improved\",\"project\",\"Implementation\",\"plan\",\"policy\"] + list(STOPWORDS)\nusa_svi_wordcloud = WordCloud(stopwords=impact_stop_words).generate(' '.join(map(str, usa_SVI_action_5_4['Response Answer'])))\nuk_svi_wordcloud = WordCloud(stopwords=impact_stop_words).generate(' '.join(map(str, uk_SVI_action_5_4['Response Answer'])))\naus_svi_wordcloud = WordCloud(stopwords=impact_stop_words).generate(' '.join(map(str, aus_SVI_action_5_4['Response Answer'])))\nplt.figure()\nfig, (ax0, ax1,ax2) = plt.subplots(1, 3,figsize=(50,25))\nfig.suptitle('City Actions towards Positive Impact',fontsize=32)\nax0.set_title(\"USA\",fontsize=32)\nax1.set_title(\"UK\",fontsize=32)\nax2.set_title(\"Australia\",fontsize=32)\n\n\nax0.imshow(usa_svi_wordcloud, interpolation=\"bilinear\")\nax1.imshow(uk_svi_wordcloud, interpolation=\"bilinear\")\nax2.imshow(aus_svi_wordcloud, interpolation=\"bilinear\")\n\nplt.axis(\"off\")\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"from sklearn.feature_extraction.text import TfidfVectorizer\nfrom sklearn.metrics.pairwise import cosine_similarity\nimport numpy as np\nimport matplotlib.pyplot as plt\n\n\ndef get_tf_idf_query_similarity(query,vectorizer,docs_tfidf):\n    \"\"\"\n    vectorizer: TfIdfVectorizer model\n    docs_tfidf: tfidf vectors for all docs\n    query: query doc\n\n    return: cosine similarity between query and all docs\n    \"\"\"\n    query_joined = ' '.join(query)\n    query_tfidf = vectorizer.transform([query_joined])\n    cosineSimilarities = cosine_similarity(query_tfidf, docs_tfidf).flatten()\n    return max(cosineSimilarities)\ndef get_corp_city_synergies(city_name, corp_city_state):\n    \n    #Need to setup TF-IDF Vectorizer to given city's 5.4 response\n    given_city = cities_df[cities_df['Organization'] == city_name]\n    city_5_4 = given_city[(given_city['Question Number'] == '5.4') & (given_city['Response Answer'] != \" \")]\n    city_5_4['Response Answer'].replace('', np.nan, inplace=True)\n    city_5_4.dropna(subset=['Response Answer'], inplace=True)\n    vectorizer = TfidfVectorizer()\n    docs_tfidf = vectorizer.fit_transform(city_5_4['Response Answer'])\n    \n    city_orgs = cities_cdpmeta_df[cities_cdpmeta_df['city_state'] == corp_city_state]\n    city_orgs_corp_data = pd.merge(left=city_orgs, right=corporates_df, how='left', on ='organization')\n    city_orgs_2_4 = city_orgs_corp_data[(city_orgs_corp_data['question_number'] == 'C2.4a') & (city_orgs_corp_data['response_value'] != 'Question not applicable')]\n    #for each org's 2_4a response, find TF-IDF cosine distance with City 5.4 response\n    city_orgs_2_4['response_value'].replace('', np.nan, inplace=True)\n    city_orgs_2_4.dropna(subset=['response_value'], inplace=True)\n\n    city_orgs_2_4_grouped = city_orgs_2_4.groupby(by='organization')\n    org_synergies = {}\n    for org_name, org_data in city_orgs_2_4_grouped:\n        #print(org_name)\n        org_2_4_response = org_data['response_value'].values.astype(str).tolist()\n        #print(org_2_4_response, type(org_2_4_response))\n        tf_idf_similarity = int(100*get_tf_idf_query_similarity(org_2_4_response,vectorizer,docs_tfidf))\n        #print(tf_idf_similarity)\n        org_synergies[org_name] = tf_idf_similarity\n    return org_synergies\n\ndef show_city_plot(sorted_list, synergies_dict, plot_title):\n    labels = []\n    numbers = []\n    total = 0\n    org_len = len(sorted_list)\n    for org_name in sorted_list:\n        labels.append(org_name)\n        value = synergies_dict[org_name]\n        numbers.append(value)\n    index = np.arange(len(labels))\n    plt.figure(figsize=(10,org_len))\n    plt.barh(index,numbers)\n    for i, v in enumerate(numbers):\n        plt.text(v + 0.2, i , str(v), color='black', fontweight='bold',fontsize=20)\n    plt.yticks(index, labels, fontsize=24, rotation=0)\n    plt.xlabel(plot_title, fontsize=32)\n    plt.title(plot_title, fontsize=32)\n    plt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**Now create TF-IDF similarity between a Corporate's climate related direction and that of the City that the corporate is based in**\nTo begin with, we will do this only for NYC and use Question C2.4a as the basis of the corporate direction and question 5.4 for city's response. Based on that we will plot the similarity score (multiplied by 100 for percent) for NYC HQ firms."},{"metadata":{"trusted":true,"collapsed":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"#corporate question_number=C2.1 is about short/med/long-term horizons c2.4 is about climate-related strategic opportunities (details in C2.4a)\n\ncorporates_df =  pd.read_csv('../input/cdp-unlocking-climate-solutions/Corporations/Corporations Responses/Climate Change/2019_Full_Climate_Change_Dataset.csv')\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# 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# 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\norg_synergies_nyc = get_corp_city_synergies('New York City','New York, NY')\norg_synergies_sorted_nyc = sorted(org_synergies_nyc, key=org_synergies_nyc.get)\norg_synergies_sf = get_corp_city_synergies('City of San Francisco','San Francisco, CA')\norg_synergies_sorted_sf = sorted(org_synergies_sf, key=org_synergies_sf.get)\n\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"show_city_plot(org_synergies_sorted_nyc, org_synergies_nyc, \"New York City\")\nshow_city_plot(org_synergies_sorted_sf, org_synergies_sf, \"City of San Francisco\")\n","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat":4,"nbformat_minor":4}