{"cells":[{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"import numpy as np\nimport matplotlib.pyplot as plt\nimport pandas as pd\nfrom scipy import stats\nfrom sklearn.preprocessing import RobustScaler, QuantileTransformer\n%matplotlib inline\nimport rasterio\nfrom matplotlib import colors\nfrom scipy import stats\nfrom tqdm import *\nfrom pyproj import Transformer\nimport ast\nfrom geopy import distance\nfrom sklearn.metrics.pairwise import haversine_distances\nfrom math import radians\nfrom sklearn.neighbors import BallTree\nfrom nltk.tokenize import word_tokenize\nEARTH_RADIUS = 3958.761\nimport spacy\nnlp = spacy.load('en_core_web_lg')\nfrom collections import defaultdict\nfrom spacy.lang.en import STOP_WORDS","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true},"cell_type":"code","source":"city_collab = pd.read_csv(\"../input/externald/Supplementary Data/cities_updated_geo_us_2020.csv\")\nacct_number_cities = city_collab[\"Account Number\"]\ncounties = (city_collab['counties'])\ncounties_weights = (city_collab['counties_weights'])\nstates = (city_collab['state'])\nflag_multi_counties = city_collab['flag_multiple_counties']\nmatch_fips = city_collab['FIPS']\nMSA = (city_collab['MSA'])\nmatch_fips = [ast.literal_eval(match_fips[i]) if flag_multi_counties[i] else int(match_fips[i]) for i in range(len(match_fips))]\ncities = city_collab[\"city_bing\"]\ncity_locations = ((city_collab[\"loc\"])).apply(lambda x: (eval(x.split(\"POINT \")[1].replace(\" \",\",\").replace(\"(\", \"[\").replace(\")\", \"]\"))) if type(x) == str else Point(0,0))\ncity_locations = np.vstack(city_locations)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_2020 = pd.read_csv(\"/kaggle/input/cdp-unlocking-climate-solutions/Cities/Cities Responses/2020_Full_Cities_Dataset.csv\")\n\ncities_disc_2020 = pd.read_csv(\"/kaggle/input/cdp-unlocking-climate-solutions/Cities/Cities Disclosing/2020_Cities_Disclosing_to_CDP.csv\")\n\ncities_2020_merged = pd.merge(cities_2020, cities_disc_2020, on=\"Account Number\", how=\"outer\", copy=False)\nunique_20 = np.unique(cities_disc_2020[cities_disc_2020[\"Country\"] == \"United States of America\"][\"Account Number\"])\ncities_2020_merged_sub = cities_2020_merged[np.in1d(cities_2020_merged[\"Account Number\"], unique_20)]\n\ndef fetch_answer_individual(account, question_number, org_type=\"city\", year=2018, corp_res_type=None, column_number=None, row_number=None):\n    if(org_type==\"city\"):\n        df = cities_2020_merged_sub\n        subset = df[df['Question Number'] == question_number]\n        answer = subset[subset[\"Account Number\"] == account]\n        if((column_number is not None) and (row_number is None)):\n            answer = answer[answer[\"Column Number\"] == column_number]\n        elif((column_number is not None) and (row_number is not None)):\n            answer = answer[(answer[\"Column Number\"] == column_number) & (answer[\"Row Number\"] == row_number)]\n        elif((column_number is None) and (row_number is not None)):\n            answer = answer[answer[\"Row Number\"] == row_number]\n        else:\n            pass\n    elif(org_type==\"corp\" and corp_res_type==\"cc\"):\n        df = corporations_2020\n        subset = df[df['question_number'] == question_number]\n        answer = subset[subset[\"account_number\"] == account]\n        if((column_number is not None) and (row_number is None)):\n            answer = answer[answer[\"column_number\"] == column_number]\n        elif((column_number is not None) and (row_number is not None)):\n            answer = answer[(answer[\"column_number\"] == column_number) & (answer[\"row_number\"] == row_number)]\n        elif((column_number is None) and (row_number is not None)):\n            answer = answer[answer[\"row_number\"] == row_number]\n        else:\n            pass\n    elif(org_type==\"corp\" and corp_res_type==\"ws\"):\n        df = all_corps_cc[year]\n        subset = df[df['question_number'] == question_number]\n        answer = subset[subset[\"account_number\"] == account]\n    else:\n        print(\"Something went wrong. Try again.\")\n    \n    return answer","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"corporations_2020 = pd.read_csv(\"../input/externald/Supplementary Data/corporations_2020_climate_change_geo.csv\")\nacct_org = dict(zip(np.array(corporations_2020[\"account_number\"]), np.array(corporations_2020[\"organization\"])))\n\nall_corp_locations = np.c_[np.array(corporations_2020[\"lat\"]), np.array(corporations_2020[\"lng\"])]\nall_corp_accounts = np.array(corporations_2020[\"account_number\"])\nunique_corp_accounts, unique_corp_idxs = np.unique(all_corp_accounts, axis=0, return_index=True)\n\ncorp_locs_lat = all_corp_locations[:,0][unique_corp_idxs]\ncorp_locs_lng = all_corp_locations[:,1][unique_corp_idxs]\ncorp_names = np.array(corporations_2020[\"organization\"].iloc[unique_corp_idxs])\ncorp_cities = np.array(corporations_2020[\"city\"].iloc[unique_corp_idxs])\ncorp_states = np.array(corporations_2020[\"state\"].iloc[unique_corp_idxs])\ncorp_countries = np.array(corporations_2020[\"country\"].iloc[unique_corp_idxs])\n\ncorp_locs = np.c_[corp_locs_lat, corp_locs_lng]\ncorp_locs = np.nan_to_num(corp_locs, nan=-999)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"bt = BallTree(np.deg2rad(corp_locs), metric='haversine')\nradius_thresh = 150/EARTH_RADIUS ## radius thresh = 150 miles","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"number_corps = []\n\ncity_corp_match_indices = dict()\ncity_corp_match_distances = dict()\n\nfor i in range(len(cities)):\n    indices, distances = bt.query_radius(np.deg2rad(np.c_[city_locations[i,1], city_locations[i,0]]), radius_thresh, return_distance=True)\n    number_corps.append(len(indices[0]))\n    \n    non_canada_countries = np.where(corp_countries[indices[0]] != \"Canada\")[0]\n    city_corp_match_indices[cities[i]] = indices[0][non_canada_countries]\n    city_corp_match_distances[cities[i]] = distances[0][non_canada_countries]*EARTH_RADIUS","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def compute_similarity(doc1, doc2):\n    vector1 = np.zeros(300)\n    vector2 = np.zeros(300)\n    accum1 = 0\n    for token in doc1:\n        if (token.text not in STOP_WORDS):\n            vector1 = vector1 + token.vector\n            accum1 += 1\n    vector1 = np.divide(vector1, accum1)\n    accum2 = 0\n    for token in doc2:\n        if (token.text not in STOP_WORDS):\n            vector2 = vector2 + token.vector\n            accum2 += 1\n    vector2 = np.divide(vector2, accum2)\n    return np.dot(vector1, vector2) / (np.linalg.norm(vector1) * np.linalg.norm(vector2))\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def compute_similarity_scores_for_question(question_number_city, question_number_corp, column_number_city, column_number_corp, min_characters=100):\n    subset = corporations_2020[corporations_2020['question_number'] == question_number_corp]\n    subset_corp_answers = subset[subset[\"column_number\"] == column_number_corp]\n    \n    subset = cities_2020[cities_2020['Question Number'] == question_number_city]\n    subset_city_answers = subset[subset[\"Column Number\"] == column_number_city]\n    \n    city_corp_collab_scores = defaultdict(dict)\n    corp_city_collab_scores = defaultdict(dict)\n    city_corp_collab_dist = defaultdict(dict)\n    corp_city_collab_dist = defaultdict(dict)\n\n    for i in tqdm(range(len(cities))):\n        cdf_city = subset_city_answers[subset_city_answers[\"Account Number\"] == acct_number_cities[i]][\"Response Answer\"]\n        cdf_city = cdf_city.fillna('')\n        str_response_city = ' '.join(list(cdf_city))\n        if(len(str_response_city) > min_characters):\n\n            corps_to_curr_city_idxs = city_corp_match_indices[cities[i]]\n\n            if(len(corps_to_curr_city_idxs) > 0):\n\n                for j in range(len(corps_to_curr_city_idxs)):\n                    corp_account = unique_corp_accounts[corps_to_curr_city_idxs[j]]\n                    answer_corp = subset_corp_answers[subset_corp_answers[\"account_number\"] == corp_account][\"response_value\"]\n                    answer_corp = answer_corp.fillna('')\n                    str_response_corp = ' '.join(list(answer_corp))\n\n                    if(len(str_response_corp) > min_characters):\n                        str_response_corp = ' '.join(answer_corp)\n                        doc1 = nlp(str_response_city)\n                        doc2 = nlp(str_response_corp)\n                        score = compute_similarity(doc1, doc2)\n\n                        city_corp_collab_scores[acct_number_cities[i]][corp_account] = score\n                        city_corp_collab_dist[acct_number_cities[i]][corp_account] = city_corp_match_distances[cities[i]][j]\n\n                        corp_city_collab_scores[corp_account][acct_number_cities[i]] = score\n                        corp_city_collab_dist[corp_account][acct_number_cities[i]] = city_corp_match_distances[cities[i]][j]\n\n                    else:\n                        city_corp_collab_scores[acct_number_cities[i]][corp_account] = np.nan\n                        corp_city_collab_scores[corp_account][acct_number_cities[i]] = np.nan\n\n                        city_corp_collab_dist[acct_number_cities[i]][corp_account] = city_corp_match_distances[cities[i]][j]\n                        corp_city_collab_dist[corp_account][acct_number_cities[i]] = city_corp_match_distances[cities[i]][j]\n\n\n\n            else:\n                city_corp_collab_scores[acct_number_cities[i]] = np.nan\n\n        else:\n            city_corp_collab_scores[acct_number_cities[i]] = np.nan\n\n    return city_corp_collab_scores, corp_city_collab_scores, city_corp_collab_dist, corp_city_collab_dist","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"city_corp_collab_scores, corp_city_collab_scores, city_corp_collab_dist, corp_city_collab_dist = compute_similarity_scores_for_question(\"6.2a\", \"C12.3a\", 3.0, 3.0)\n\ncity_corp_alignment_opportunity_scores, corp_city_alignment_opportunity_scores, city_corp_alignment_opportunity_dist, corp_city_alignment_opportunity_dist = compute_similarity_scores_for_question(\"6.0\", \"C2.4a\", 2.0, 6.0)\n\ncity_corp_alignment_risk_scores, corp_city_alignment_risk_scores, city_corp_alignment_risk_dist, corp_city_alignment_risk_dist = compute_similarity_scores_for_question(\"2.1\", \"C2.2a\", 12.0, 2.0)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"import pickle","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"with open('city_corp_collab.pickle', 'wb') as handle:\n    pickle.dump([city_corp_collab_scores, city_corp_collab_dist, city_corp_alignment_opportunity_scores,\n                corp_city_alignment_opportunity_scores, city_corp_alignment_risk_scores,\n                corp_city_alignment_risk_scores], handle, protocol=pickle.HIGHEST_PROTOCOL)\n","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}