{"cells":[{"metadata":{},"cell_type":"markdown","source":"# CDP-Unlocking Climate Solutions"},{"metadata":{},"cell_type":"markdown","source":"Data mapping, EDA and data wrangling pipeline to relate CDP Corporate response data to CDP Cities data  sets containing social equity data."},{"metadata":{},"cell_type":"markdown","source":"## 8. Energy:\n\n### 8.0 Does your city have a renewable energy or electricity target?\n\n\n\n### Response Options\nSelect one of the following options:\n\n- Yes\n- In progress\n- Intending to undertake in the next 2 years\n- Not intending to undertake\n- Do not know"},{"metadata":{},"cell_type":"markdown","source":"### Output of This notebook\n"},{"metadata":{},"cell_type":"markdown","source":"EDA and Visualisations to begin investigating the CDP competition data sets, environmental performance indicators and social-equity KPIs."},{"metadata":{},"cell_type":"markdown","source":"### Extract City Questionnaire Response and map Cities to Organisations\n"},{"metadata":{},"cell_type":"markdown","source":"Extract city response data for question 8.0 \n\nDoes your city collaborate in partnership with businesses in your city on sustainability projects?\n\nMap cities to organisations who are headquartered within that city, using the NA_HQ_public_data.csv meta data file."},{"metadata":{},"cell_type":"markdown","source":"### Summarise the cities metadata to count the number organisations (HQ) per city."},{"metadata":{},"cell_type":"markdown","source":"Join Count of Disclosing Organisations in HQ Cities with Question 8.0 Response dataframe\n\nLabel the response variable as a city's current Sustainability Project Collaboration"},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":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)\n\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\nprint(os.listdir('/kaggle/input/cdp-unlocking-climate-solutions'))\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":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true},"cell_type":"code","source":"# standard libs\nimport os\nimport pandas as pd\nimport numpy as np\nimport matplotlib.pyplot as plt\nimport re\nimport json\n\n# plotting libs\nimport seaborn as sns\n\n# geospatial libs\nfrom mpl_toolkits.basemap import Basemap\nfrom shapely.geometry import Polygon\nimport geopandas as gpd\nimport folium\nimport plotly.graph_objects as go\nimport plotly_express as px\n\n# set in line plotly \nfrom plotly.offline import init_notebook_mode;\ninit_notebook_mode(connected=True)\n\nprint(os.getcwd())","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Import Data"},{"metadata":{"trusted":true},"cell_type":"code","source":"# import corporate response data\ncc_df = pd.read_csv('../input/cdp-unlocking-climate-solutions/Corporations/Corporations Responses/Climate Change/2019_Full_Climate_Change_Dataset.csv')\n\nws_df = pd.read_csv('../input/cdp-unlocking-climate-solutions/Corporations/Corporations Responses/Water Security/2019_Full_Water_Security_Dataset.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# import cities response df\ncities_df = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Cities/Cities Responses/2020_Full_Cities_Dataset.csv\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# external data - import CDC social vulnerability index data - census tract level\nsvi_df = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Supplementary Data/CDC Social Vulnerability Index 2018/SVI2018_US.csv\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# 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\")","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Helpers"},{"metadata":{"trusted":true},"cell_type":"code","source":"def list_dedupe(x):\n    \"\"\"\n    Convert list to dict and back to list to dedupe\n    \n    Parameters\n    ----------\n    x: list\n        Python list object\n        \n    Returns\n    -------\n    dictionary:\n        dictionary object with duplicates removed\n        \n    \"\"\"\n    return list(dict.fromkeys(x))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Set up and Parameters"},{"metadata":{"trusted":true},"cell_type":"code","source":"#cities_6_2 = cities_df[cities_df['Question Number'] == '6.2']\\\n    #.rename(columns={'Organization': 'City'})\n\n#cities_6_2['Response Answer'] = cities_6_2['Response Answer'].fillna('No Response')\n\n#cities_6_2.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_8_0 = cities_df[cities_df['Question Number'] == '8.0']\\\n    .rename(columns={'Organization': 'City'})\n\ncities_8_0['Response Answer'] = cities_8_0['Response Answer'].fillna('No Response')\n\ncities_8_0.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"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","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"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()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"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))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"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()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_count  = pd.merge(cities_count, city_names_df, on='address_city', how='left')\ncities_count.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_8_0 = cities_8_0[['City', 'Response Answer']].rename(columns={'City' : 'City Org'})\ncities_count = pd.merge(left=cities_count, right=cities_8_0, 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')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"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)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"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()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"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()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"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()","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}