{"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\nimport geopandas as gpd\n%matplotlib inline\nimport time \nfrom tqdm import *","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true},"cell_type":"code","source":"corporations_2020 = pd.read_csv(\"../input/externald/Supplementary Data/corporations_2020_climate_change_geo.csv\")\nacct_corps = dict(zip(np.array(corporations_2020[\"account_number\"]), np.array(corporations_2020[\"organization\"])))\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 = corporations_2020_water\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    else:\n        print(\"Something went wrong. Try again.\")\n    \n    return answer","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def standardize_rank(arr, direction=1):\n    rank = (stats.mstats.rankdata(np.ma.masked_invalid(arr)))\n    rank[rank == 0] = np.nan\n    if(direction == 1):\n        return rank/(np.nanmax(rank)) \n    else:\n        return 1-(rank/(np.nanmax(rank)))\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"all_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":"from nltk.sentiment.vader import SentimentIntensityAnalyzer\nfrom nltk import tokenize\n\nsid = SentimentIntensityAnalyzer()\n\ndef polarity_score_corp(question, column, sent_thresh=2):\n    subset = corporations_2020[corporations_2020['question_number'] == question]\n    subset_corp_answers = subset[subset[\"column_number\"] == column]\n    \n    compound_score = []\n    for i in tqdm(range(len(unique_corp_accounts))):\n        ss = list(subset_corp_answers[subset_corp_answers[\"account_number\"] == unique_corp_accounts[i]][\"response_value\"])\n        \n        ss = ' '.join(map(str, ss))\n        sents = tokenize.sent_tokenize(ss)\n        if(len(sents) > sent_thresh):\n            cc = 0\n            for s in sents:\n                r = sid.polarity_scores(s)\n                cc += r['compound']\n            avg_cc = cc/len(sents)\n            compound_score.append(avg_cc)\n        else:\n            compound_score.append(np.nan)\n        \n    return compound_score","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"risk_corp_sentiment = polarity_score_corp(\"C2.2a\", 2.0)\nopportunity_corp_sentiment = polarity_score_corp(\"C2.4a\", 6.0)\npotential_corp_sentiment = polarity_score_corp(\"C12.3a\", 3.0)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"financial_data = pd.read_csv(\"../input/annual-financial-data-for-hybrid-cdp-kpi/cdp_financial_data.csv\")\n\nfinance_acct = []\nmatch_len = []\nfor i in range(len(unique_corp_accounts)):\n    cdf = financial_data[financial_data[\"account_number\"] == unique_corp_accounts[i]]\n    try:\n        finance_acct.append(pd.DataFrame(cdf))\n    except:\n        pass\n    match_len.append(len(cdf))\ndf = pd.concat(finance_acct)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"tons_reduced_per_year = []\nfor i in range(len(unique_corp_accounts)):\n    cdf = fetch_answer_individual(unique_corp_accounts[i], \"C4.1a\", org_type=\"corp\", year=2020, corp_res_type=\"cc\")\n    if(len(cdf) > 0):\n        base_emissions = float(cdf[cdf[\"column_number\"] == 6.0][\"response_value\"].iloc[0])\n        base_emissions_year = float(cdf[cdf[\"column_number\"] == 5.0][\"response_value\"].iloc[0])\n        reduction_emissions = float(cdf[cdf[\"column_number\"] == 9.0][\"response_value\"].iloc[0])*base_emissions\n        goal_year = float(cdf[cdf[\"column_number\"] == 8.0][\"response_value\"].iloc[0])\n        try:\n            rate = reduction_emissions/(goal_year - base_emissions_year)\n        except:\n            rate = reduction_emissions\n        tons_reduced_per_year.append(rate)\n    else:\n        tons_reduced_per_year.append(np.nan)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"tons_reduced_per_year = []\nfor i in range(len(unique_corp_accounts)):\n    cdf = fetch_answer_individual(unique_corp_accounts[i], \"C4.1a\", org_type=\"corp\", year=2020, corp_res_type=\"cc\")\n    if(len(cdf) > 0):\n        base_emissions = float(cdf[cdf[\"column_number\"] == 6.0][\"response_value\"].iloc[0])\n        base_emissions_year = float(cdf[cdf[\"column_number\"] == 5.0][\"response_value\"].iloc[0])\n        reduction_emissions = float(cdf[cdf[\"column_number\"] == 9.0][\"response_value\"].iloc[0])*base_emissions\n        goal_year = float(cdf[cdf[\"column_number\"] == 8.0][\"response_value\"].iloc[0])\n        try:\n            rate = reduction_emissions/(goal_year - base_emissions_year)\n        except:\n            rate = reduction_emissions\n        tons_reduced_per_year.append(rate)\n    else:\n        tons_reduced_per_year.append(np.nan)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"ebdtas = []\nfor i in range(len(unique_corp_accounts)):\n    cdf = financial_data[financial_data[\"account_number\"] == unique_corp_accounts[i]]\n    if(len(cdf) > 0):\n        ebdtas.append(cdf[\"EBITDA\"].iloc[0])\n    else:\n        ebdtas.append(np.nan)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"tons_to_save_life = 258200\nlives_saved_twenty_years = np.array(tons_reduced_per_year)/tons_to_save_life * 20","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"ebidta_lives_saved_emissions = lives_saved_twenty_years*np.array(tons_reduced_per_year)\nebidta_lives_saved_emissions_rank = standardize_rank(ebidta_lives_saved_emissions)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"corp_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])\nrisk_corp_sentiment = polarity_score_corp(\"C2.2a\", 2.0)\nopportunity_corp_sentiment = polarity_score_corp(\"C2.4a\", 6.0)\npotential_corp_sentiment = polarity_score_corp(\"C12.3a\", 3.0)\nlives_saved = lives_saved_twenty_years","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df = pd.DataFrame(np.c_[unique_corp_accounts, corp_names, corp_cities, corp_states, corp_countries, corp_locs_lat, corp_locs_lng,\n          ebidta_lives_saved_emissions, ebidta_lives_saved_emissions_rank, risk_corp_sentiment, opportunity_corp_sentiment, \n                       potential_corp_sentiment, lives_saved], \n                  columns=[\"account_number\", \"organization\", \"city\", \"state\", \"country\", \"lat\", \"long\", \"ebidta_lives_saved\", \"ebidta_lives_saved_rank\",\n                          \"sentiment_risk\", \"sentiment_opportunity\", \"sentiment_collaboration\", \"lives_saved\"])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df.to_csv(\"corporate_kpis_general.csv\")","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}