{"cells":[{"metadata":{},"cell_type":"markdown","source":"# This notebook cleans up the corporations data and finds lat/lng/city for each corporation. \n\nNote: the kernel won't run without setting GOOGLE_API_KEY to your key. "},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"!pip install geocoder\n!pip install uszipcode","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","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\nimport geocoder\nimport time \nfrom tqdm import *\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"corporations_2020 = pd.read_csv(\"/kaggle/input/cdp-unlocking-climate-solutions/Corporations/Corporations Responses/Climate Change/2020_Full_Climate_Change_Dataset.csv\")\nsupplied_geo = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Supplementary Data/Locations of Corporations/NA_HQ_public_data.csv\")\nsupplied_geo = supplied_geo[supplied_geo[\"survey_name\"] == \"Climate Change 2020\"]\n\nsupplied_geo[\"lat\"] = np.nan\nsupplied_geo[\"lng\"] = np.nan","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"acct_numbers = np.unique(corporations_2020[\"account_number\"])\nacct_org = dict(zip(np.array(corporations_2020[\"account_number\"]), np.array(corporations_2020[\"organization\"])))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Get all cities and states that are supplied and set up a boolean flag to figure out which ones we don't have info for. "},{"metadata":{"trusted":true},"cell_type":"code","source":"cities = np.zeros(len(acct_numbers), dtype=object)\nstates = np.zeros(len(acct_numbers), dtype=object)\nhas_geo_flag = np.zeros(len(acct_numbers), dtype=np.bool)\n\nfor i in range(len(acct_numbers)):\n    cdf = supplied_geo[supplied_geo[\"account_number\"] == acct_numbers[i]]\n    if(len(cdf) > 0): # if we match\n        if((type(cdf['address_city'].iloc[0]) == str) & ((type(cdf['address_state'].iloc[0]) == str))):\n            cities[i] = (cdf['address_city'].iloc[0])\n            states[i] = (cdf['address_state'].iloc[0])\n            has_geo_flag[i] = True\n        else: # if no match, we need to find the geo ourselves\n            cities[i] = (np.nan)\n            states[i] = (np.nan)\n    else:\n        cities[i] = (np.nan)\n        states[i] = (np.nan)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"GOOGLE_MAPS_API_KEY = \"api_key\" # won't run without your key here","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The query below finds lat/lng, city, state, and country for all corporations. "},{"metadata":{"trusted":true},"cell_type":"code","source":"gs = []\ncountries = np.zeros_like(cities)\nlatlng = []\n\nfor i in tqdm(range(len(acct_numbers))): # loop over all accounts\n    acct = acct_numbers[i]\n    cdf = supplied_geo[supplied_geo[\"account_number\"] == acct]\n    idx = np.where(supplied_geo[\"account_number\"] == acct)[0] # find this account in the supplementary dataset\n\n    if(len(cdf) > 0): # some accounts are not in the supplementary data so this checks for that\n        if(has_geo_flag[i]): # if geo already supplied\n\n            query_string = cities[i] + \" \" + states[i]\n            g = geocoder.google(query_string, key=GOOGLE_MAPS_API_KEY)\n            gs.append(g)\n            latlng.append(g.latlng)\n            try: # this fials a couple of times\n                countries[i] = cdf[\"hq_country\"].iloc[0]\n                supplied_geo[\"lat\"].iloc[idx] = g.latlng[0]\n                supplied_geo[\"lng\"].iloc[idx] = g.latlng[1]\n            except:\n                countries[i] = cdf[\"hq_country\"].iloc[0]\n                supplied_geo[\"lat\"].iloc[idx] = None\n                supplied_geo[\"lng\"].iloc[idx] = None\n            \n        else: # geo is not supplied! let's do a query\n            \n            query_string = acct_org[acct]\n\n            city = cdf[\"address_city\"].iloc[0]\n            state = cdf[\"address_state\"].iloc[0]\n            country = cdf[\"hq_country\"].iloc[0]\n            \n            if(type(city) == str): # if city is provided, add\n                query_string = query_string + \" \" + city\n            \n            if(type(state) == str): # if state is provided, add\n                query_string = query_string + \" \" + state\n            \n            if(type(country) == str): # if country is provided, add\n                query_string = query_string + \" \" + country\n            \n            g = geocoder.google(query_string, key=GOOGLE_MAPS_API_KEY)\n\n            gs.append(g)\n\n            cities[i] = g.city\n            states[i] = g.state\n\n            if(type(country) == str): # country from the supplementary data frame\n                countries[i] = country\n            else: # no country supplied, use google result\n                countries[i] = g.country\n\n            latlng.append(g.latlng)\n            \n            supplied_geo[\"address_city\"].iloc[idx] = g.city\n            supplied_geo[\"address_state\"].iloc[idx] = g.state\n            supplied_geo[\"hq_country\"].iloc[idx] = g.country\n\n            try: # fails a couple of times\n                supplied_geo[\"lat\"].iloc[idx] = g.latlng[0]\n                supplied_geo[\"lng\"].iloc[idx] = g.latlng[1]\n            except:\n                supplied_geo[\"lat\"].iloc[idx] = None\n                supplied_geo[\"lng\"].iloc[idx] = None\n    else: # account not in supplementary data\n        query_string = acct_org[acct]\n        g = geocoder.google(query_string, key=GOOGLE_MAPS_API_KEY)\n        gs.append(g)\n        cities[i] = g.city\n        states[i] = g.state\n        countries[i] = g.country\n        latlng.append(g.latlng)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"corporations_2020[\"city\"] = np.nan\ncorporations_2020[\"state\"] = np.nan\ncorporations_2020[\"country\"] = np.nan\ncorporations_2020[\"lat\"] = np.zeros(len(corporations_2020), dtype=float)\ncorporations_2020[\"lng\"] = np.zeros(len(corporations_2020), dtype=float)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Now that we have the cities, states, countries, and coordinates for all the corporations, we can modify the original survey file and those as fields. "},{"metadata":{"trusted":true},"cell_type":"code","source":"for i in tqdm(range(len(acct_numbers))):\n    idxs = np.where(corporations_2020[\"account_number\"] == acct_numbers[i])[0]\n    \n    curr_city = cities[i]\n    curr_state = states[i]\n    curr_country = countries[i]\n    curr_latlng = latlng[i]\n    \n    if((curr_city) is None):\n        corporations_2020[\"city\"].iloc[idxs] = np.nan\n    else:\n        corporations_2020[\"city\"].iloc[idxs] = curr_city\n    \n    if((curr_state) is None):\n        corporations_2020[\"state\"].iloc[idxs] = np.nan\n    else:\n        corporations_2020[\"state\"].iloc[idxs] = curr_state\n    \n    if((curr_country) is None):\n        corporations_2020[\"country\"].iloc[idxs] = np.nan\n    else:\n        corporations_2020[\"country\"].iloc[idxs] = curr_country\n    \n    if((curr_latlng) is None):\n        corporations_2020[\"lat\"].iloc[idxs] = np.nan\n        corporations_2020[\"lng\"].iloc[idxs] = np.nan\n    else:\n        ll = np.zeros((len(idxs), 2))\n        ll[:,0].fill(curr_latlng[0])\n        ll[:,1].fill(curr_latlng[1])\n        corporations_2020[\"lat\"].iloc[idxs] = ll[:,0]\n        corporations_2020[\"lng\"].iloc[idxs] = ll[:,1]\n    \n    ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# corporations_2020.to_csv(\"data/Supplementary Data/corporations_2020_climate_change_geo.csv\") save file for later use should show up in external","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}