{"cells":[{"metadata":{"papermill":{"duration":0.043785,"end_time":"2020-10-09T16:48:20.134177","exception":false,"start_time":"2020-10-09T16:48:20.090392","status":"completed"},"tags":[]},"cell_type":"markdown","source":"# CDP Competition Starter Notebook\nExample data mapping, EDA and data wrangling pipeline to relate CDP Corporate response data to CDP Cities data and external data sets containing social equity data.\n\n#### Parameters\n\n#### Input\n\n**CDP Corporate Questionnaire response data sets**\n- **2019_Full_Climate_Change_Dataset.csv** = 2019 Climate Change publically disclosed questionnaire responses for North America\n- **2019_Full_Water_Security_Dataset.csv** = 2019 Water Security publically disclosed questionnaire responses for North America\n\n**CDP Cities Questionnaire response data sets**\n- **2020_-_Full_Cities_Dataset.csv** = Full 2020 Cities Questionnaire response data set\n\n**CDP Cities Meta data sets**\n- **NA_HQ_public_data.csv** = CDP curated Organisations metadata, mapping publically disclosed North American organisations to HQ city and state\n\n**External Non-CDP data sets**\n- **SVI2018_US.csv** = US Centers for Disease Control and Prevention (CDC) Social Vulnerability Index (SVI) Data for 2018 (*Census tract level*) - available publicly  bat https://www.atsdr.cdc.gov/placeandhealth/svi/data_documentation_download.html\n- **SVI2018_US_COUNTY.csv** = US Centers for Disease Control and Prevention (CDC) Social Vulnerability Index (SVI) Data for 2018 (*County level*) - available publicly at https://www.atsdr.cdc.gov/placeandhealth/svi/data_documentation_download.html\n- **uscities.csv** = metadata for United States cities and towns, with information such as populations size, median age and lat,lng location coordinates - available publicly at https://simplemaps.com/data/us-cities.\n\nSVI 2018 Documentation and Data Dictionary https://www.atsdr.cdc.gov/placeandhealth/svi/documentation/SVI_documentation_2018.html\n\n#### Output\n\nEDA and Visualisations to begin investigating the CDP competition data sets, environmental performance indicators and social-equity KPIs.\n"},{"metadata":{"papermill":{"duration":0.041729,"end_time":"2020-10-09T16:48:20.217686","exception":false,"start_time":"2020-10-09T16:48:20.175957","status":"completed"},"tags":[]},"cell_type":"markdown","source":"## Imports"},{"metadata":{"execution":{"iopub.execute_input":"2020-10-09T16:48:20.312257Z","iopub.status.busy":"2020-10-09T16:48:20.311414Z","iopub.status.idle":"2020-10-09T16:48:24.034193Z","shell.execute_reply":"2020-10-09T16:48:24.03499Z"},"papermill":{"duration":3.774252,"end_time":"2020-10-09T16:48:24.035223","exception":false,"start_time":"2020-10-09T16:48:20.260971","status":"completed"},"tags":[],"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":{"papermill":{"duration":0.042097,"end_time":"2020-10-09T16:48:24.122194","exception":false,"start_time":"2020-10-09T16:48:24.080097","status":"completed"},"tags":[]},"cell_type":"markdown","source":"## Data\n\n### Import Data"},{"metadata":{"execution":{"iopub.execute_input":"2020-10-09T16:48:24.220803Z","iopub.status.busy":"2020-10-09T16:48:24.220025Z","iopub.status.idle":"2020-10-09T16:48:32.18631Z","shell.execute_reply":"2020-10-09T16:48:32.185513Z"},"papermill":{"duration":8.02188,"end_time":"2020-10-09T16:48:32.186442","exception":false,"start_time":"2020-10-09T16:48:24.164562","status":"completed"},"tags":[],"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')\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":"cc_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"ws_df.head()","execution_count":null,"outputs":[]},{"metadata":{"execution":{"iopub.execute_input":"2020-10-09T16:48:32.28382Z","iopub.status.busy":"2020-10-09T16:48:32.283048Z","iopub.status.idle":"2020-10-09T16:48:37.289498Z","shell.execute_reply":"2020-10-09T16:48:37.29011Z"},"papermill":{"duration":5.060271,"end_time":"2020-10-09T16:48:37.290298","exception":false,"start_time":"2020-10-09T16:48:32.230027","status":"completed"},"tags":[],"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":"cities_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_df['Question Number'].unique()","execution_count":null,"outputs":[]},{"metadata":{"execution":{"iopub.execute_input":"2020-10-09T16:48:37.385441Z","iopub.status.busy":"2020-10-09T16:48:37.384706Z","iopub.status.idle":"2020-10-09T16:48:38.761962Z","shell.execute_reply":"2020-10-09T16:48:38.761025Z"},"papermill":{"duration":1.427792,"end_time":"2020-10-09T16:48:38.76212","exception":false,"start_time":"2020-10-09T16:48:37.334328","status":"completed"},"tags":[],"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":"svi_df","execution_count":null,"outputs":[]},{"metadata":{"execution":{"iopub.execute_input":"2020-10-09T16:48:38.857639Z","iopub.status.busy":"2020-10-09T16:48:38.856747Z","iopub.status.idle":"2020-10-09T16:48:39.025033Z","shell.execute_reply":"2020-10-09T16:48:39.024157Z"},"papermill":{"duration":0.219868,"end_time":"2020-10-09T16:48:39.025175","exception":false,"start_time":"2020-10-09T16:48:38.805307","status":"completed"},"tags":[],"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":{"trusted":true},"cell_type":"code","source":"cities_meta_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_cdpmeta_df.head()","execution_count":null,"outputs":[]},{"metadata":{"papermill":{"duration":0.042663,"end_time":"2020-10-09T16:48:39.111903","exception":false,"start_time":"2020-10-09T16:48:39.06924","status":"completed"},"tags":[]},"cell_type":"markdown","source":"### Helpers"},{"metadata":{"execution":{"iopub.execute_input":"2020-10-09T16:48:39.206736Z","iopub.status.busy":"2020-10-09T16:48:39.205426Z","iopub.status.idle":"2020-10-09T16:48:39.209321Z","shell.execute_reply":"2020-10-09T16:48:39.20872Z"},"papermill":{"duration":0.054662,"end_time":"2020-10-09T16:48:39.209454","exception":false,"start_time":"2020-10-09T16:48:39.154792","status":"completed"},"tags":[],"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":{"papermill":{"duration":0.0428,"end_time":"2020-10-09T16:48:39.296669","exception":false,"start_time":"2020-10-09T16:48:39.253869","status":"completed"},"tags":[]},"cell_type":"markdown","source":"### Set up and Parameters"},{"metadata":{"papermill":{"duration":0.042535,"end_time":"2020-10-09T16:48:39.382282","exception":false,"start_time":"2020-10-09T16:48:39.339747","status":"completed"},"tags":[]},"cell_type":"markdown","source":"## Calculations\n\n### Data Cleaning & EDA\n\n#### Extract City Questionnaire Response and map Cities to Organisations\n\n- Extract city response data for question *6.0 whether a city sees opportunity*\n- Map cities to organisations who are headquartered within that city, using the NA_HQ_public_data.csv meta data file\n\n(see [CDP Cities questionnaire guidance](https://guidance.cdp.net/en/guidance?cid=16&ctype=theme&idtype=ThemeID&incchild=1&microsite=0&otype=Questionnaire&tags=TAG-637%2CTAG-570%2CTAG-13013%2CTAG-13002%2CTAG-13009%2CTAG-13010))\n"},{"metadata":{"execution":{"iopub.execute_input":"2020-10-09T16:48:39.628644Z","iopub.status.busy":"2020-10-09T16:48:39.627837Z","iopub.status.idle":"2020-10-09T16:48:39.671247Z","shell.execute_reply":"2020-10-09T16:48:39.670535Z"},"papermill":{"duration":0.246041,"end_time":"2020-10-09T16:48:39.671376","exception":false,"start_time":"2020-10-09T16:48:39.425335","status":"completed"},"tags":[],"trusted":true},"cell_type":"code","source":"cities_6_0 = cities_df[cities_df['Question Number'] == '6.0']\\\n    .rename(columns={'Organization': 'City'})\n\ncities_6_0['Response Answer'] = cities_6_0['Response Answer'].fillna('No Response')\n\ncities_6_0.head()","execution_count":null,"outputs":[]},{"metadata":{"papermill":{"duration":0.043616,"end_time":"2020-10-09T16:48:39.759742","exception":false,"start_time":"2020-10-09T16:48:39.716126","status":"completed"},"tags":[]},"cell_type":"markdown","source":"Clean Organisation City HQ Metadata"},{"metadata":{"execution":{"iopub.execute_input":"2020-10-09T16:48:39.872235Z","iopub.status.busy":"2020-10-09T16:48:39.871012Z","iopub.status.idle":"2020-10-09T16:48:39.919958Z","shell.execute_reply":"2020-10-09T16:48:39.919268Z"},"papermill":{"duration":0.115948,"end_time":"2020-10-09T16:48:39.920093","exception":false,"start_time":"2020-10-09T16:48:39.804145","status":"completed"},"tags":[],"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":{"papermill":{"duration":0.045638,"end_time":"2020-10-09T16:48:40.011537","exception":false,"start_time":"2020-10-09T16:48:39.965899","status":"completed"},"tags":[]},"cell_type":"markdown","source":"Summarise the cities metadata to count the number organisations (HQ) per city "},{"metadata":{"execution":{"iopub.execute_input":"2020-10-09T16:48:40.112787Z","iopub.status.busy":"2020-10-09T16:48:40.111597Z","iopub.status.idle":"2020-10-09T16:48:40.136216Z","shell.execute_reply":"2020-10-09T16:48:40.135604Z"},"papermill":{"duration":0.079589,"end_time":"2020-10-09T16:48:40.13635","exception":false,"start_time":"2020-10-09T16:48:40.056761","status":"completed"},"tags":[],"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":{"papermill":{"duration":0.046244,"end_time":"2020-10-09T16:48:40.228968","exception":false,"start_time":"2020-10-09T16:48:40.182724","status":"completed"},"tags":[]},"cell_type":"markdown","source":"City name conversion\n\n- Align City names in CDP City questionnaire response data ('City Org') with common city names that may be present in external data sets\n- e.g. 'City of Boulder' -> Boulder\n\n*Note* This data quality control step can also be addressed by using the 'City' column in the 2019_Cities_Disclosing_to_CDP.csv dataset"},{"metadata":{"execution":{"iopub.execute_input":"2020-10-09T16:48:40.329819Z","iopub.status.busy":"2020-10-09T16:48:40.328964Z","iopub.status.idle":"2020-10-09T16:48:41.035922Z","shell.execute_reply":"2020-10-09T16:48:41.035021Z"},"papermill":{"duration":0.761369,"end_time":"2020-10-09T16:48:41.036066","exception":false,"start_time":"2020-10-09T16:48:40.274697","status":"completed"},"tags":[],"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":{"execution":{"iopub.execute_input":"2020-10-09T16:48:41.159351Z","iopub.status.busy":"2020-10-09T16:48:41.152388Z","iopub.status.idle":"2020-10-09T16:48:41.884402Z","shell.execute_reply":"2020-10-09T16:48:41.883576Z"},"papermill":{"duration":0.802654,"end_time":"2020-10-09T16:48:41.884558","exception":false,"start_time":"2020-10-09T16:48:41.081904","status":"completed"},"tags":[],"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":{"papermill":{"duration":0.047466,"end_time":"2020-10-09T16:48:41.978419","exception":false,"start_time":"2020-10-09T16:48:41.930953","status":"completed"},"tags":[]},"cell_type":"markdown","source":"Join city_org names to city-org count table\n"},{"metadata":{"execution":{"iopub.execute_input":"2020-10-09T16:48:42.095171Z","iopub.status.busy":"2020-10-09T16:48:42.094296Z","iopub.status.idle":"2020-10-09T16:48:42.098945Z","shell.execute_reply":"2020-10-09T16:48:42.098189Z"},"papermill":{"duration":0.074655,"end_time":"2020-10-09T16:48:42.099072","exception":false,"start_time":"2020-10-09T16:48:42.024417","status":"completed"},"tags":[],"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":{"papermill":{"duration":0.046312,"end_time":"2020-10-09T16:48:42.192077","exception":false,"start_time":"2020-10-09T16:48:42.145765","status":"completed"},"tags":[]},"cell_type":"markdown","source":"Join Count of Disclosing Organisations in HQ Cities with Question 6.0 Response dataframe\n\n- Label the response variable as a city's current Sustainability Project Collaboration"},{"metadata":{"execution":{"iopub.execute_input":"2020-10-09T16:48:42.299388Z","iopub.status.busy":"2020-10-09T16:48:42.298228Z","iopub.status.idle":"2020-10-09T16:48:42.306424Z","shell.execute_reply":"2020-10-09T16:48:42.305808Z"},"papermill":{"duration":0.066304,"end_time":"2020-10-09T16:48:42.306582","exception":false,"start_time":"2020-10-09T16:48:42.240278","status":"completed"},"tags":[],"trusted":true},"cell_type":"code","source":"cities_6_0 = cities_6_0[['City', 'Response Answer']].rename(columns={'City' : 'City Org'})\ncities_count = pd.merge(left=cities_count, right=cities_6_0, how='left', \n                        on ='City Org').rename(columns={'Response Answer' : 'Opportunity'})\ncities_count['Opportunity'] = cities_count['Opportunity'].fillna('No Response')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_count","execution_count":null,"outputs":[]},{"metadata":{"papermill":{"duration":0.047171,"end_time":"2020-10-09T16:48:42.402094","exception":false,"start_time":"2020-10-09T16:48:42.354923","status":"completed"},"tags":[]},"cell_type":"markdown","source":"Plot cities containing the highest proportion of organisations disclosing to CDP\n\n- Highlight number of disclosing orgnanisations with a HQ in the city\n- Highlight the city's response to question 6.0 as bar colour\n"},{"metadata":{"execution":{"iopub.execute_input":"2020-10-09T16:48:42.505544Z","iopub.status.busy":"2020-10-09T16:48:42.504761Z","iopub.status.idle":"2020-10-09T16:48:44.013562Z","shell.execute_reply":"2020-10-09T16:48:44.012798Z"},"papermill":{"duration":1.565062,"end_time":"2020-10-09T16:48:44.013702","exception":false,"start_time":"2020-10-09T16:48:42.44864","status":"completed"},"tags":[],"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 = \"Opportunity\",\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":{"papermill":{"duration":0.048539,"end_time":"2020-10-09T16:48:44.111685","exception":false,"start_time":"2020-10-09T16:48:44.063146","status":"completed"},"tags":[]},"cell_type":"markdown","source":"Spatial plot of cities and organisation mapping\n\n[Example bubble map with plotting with plotly](https://plotly.com/python/bubble-maps/)"},{"metadata":{"execution":{"iopub.execute_input":"2020-10-09T16:48:44.218798Z","iopub.status.busy":"2020-10-09T16:48:44.217099Z","iopub.status.idle":"2020-10-09T16:48:44.235156Z","shell.execute_reply":"2020-10-09T16:48:44.234536Z"},"papermill":{"duration":0.074641,"end_time":"2020-10-09T16:48:44.235291","exception":false,"start_time":"2020-10-09T16:48:44.16065","status":"completed"},"tags":[],"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":{"execution":{"iopub.execute_input":"2020-10-09T16:48:44.347145Z","iopub.status.busy":"2020-10-09T16:48:44.346294Z","iopub.status.idle":"2020-10-09T16:48:44.389977Z","shell.execute_reply":"2020-10-09T16:48:44.389363Z"},"papermill":{"duration":0.102905,"end_time":"2020-10-09T16:48:44.390105","exception":false,"start_time":"2020-10-09T16:48:44.2872","status":"completed"},"tags":[],"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.0 to an integar encoding \nresp_int_df = cities_count[[\"Opportunity\"]]\nresp_int_df= resp_int_df.rename(columns={'Opportunity' : '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":{"papermill":{"duration":0.050706,"end_time":"2020-10-09T16:48:44.490878","exception":false,"start_time":"2020-10-09T16:48:44.440172","status":"completed"},"tags":[]},"cell_type":"markdown","source":"- Highlight number of disclosing orgnanisations with a HQ in the city via bubble size\n- Highlight city's response to question 6.2 as bubble colour and highlight in hover box"},{"metadata":{"execution":{"iopub.execute_input":"2020-10-09T16:48:44.606546Z","iopub.status.busy":"2020-10-09T16:48:44.605721Z","iopub.status.idle":"2020-10-09T16:48:44.853933Z","shell.execute_reply":"2020-10-09T16:48:44.854624Z"},"papermill":{"duration":0.313552,"end_time":"2020-10-09T16:48:44.854789","exception":false,"start_time":"2020-10-09T16:48:44.541237","status":"completed"},"tags":[],"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>Opportunity: ' + (cities_count['Opportunity']).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":{"papermill":{"duration":0.094305,"end_time":"2020-10-09T16:48:45.041283","exception":false,"start_time":"2020-10-09T16:48:44.946978","status":"completed"},"tags":[]},"cell_type":"markdown","source":"####  Build NYC City Specific Dataset\n\nCombine SVI dataset with CDP City Questionnaire and Organisation level 2019 Climate Change questionnaire response data\n\nE.g. :\n- Identify which organisations located within NYC see climate-related opportunities within their operations\n- Match organisations with areas of the city that suffer from high unemployment rates\n- Pinpoint areas of NYC that present an opportunity for corporate collobaration and therefore an uplift in social equity metrics"},{"metadata":{"papermill":{"duration":0.090456,"end_time":"2020-10-09T16:48:45.223112","exception":false,"start_time":"2020-10-09T16:48:45.132656","status":"completed"},"tags":[]},"cell_type":"markdown","source":"###### Subset climate change questionnaire response data for question C6.0\n\n*Apply sentiment analysis to detect whether a city sees opportunity (positive sentiment/polarity) (Cities Question 6.0)\n\n(see [CDP Climate Change questionnaire guidance](https://guidance.cdp.net/en/guidance?cid=13&ctype=theme&idtype=ThemeID&incchild=1&microsite=0&otype=Questionnaire&tags=TAG-646%2CTAG-605%2CTAG-600))"},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_count","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"import seaborn as sns\nimport matplotlib.pyplot as plt\nimport os","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Import package\nfrom wordcloud import WordCloud, STOPWORDS","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cloud_words = \" \".join(i for i in rv_tokens)\nstopwords = set(STOPWORDS)\nstopwords.update([\"http\", \"https\",\"co\",\"com\",\"amp\"])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%matplotlib inline","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"import nltk \nnltk.download('stopwords')\nfrom nltk.corpus import stopwords \nfrom nltk.stem.porter import PorterStemmer","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"rv=cities_count['Opportunity'].tolist()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"rv1=\"\".join(rv)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"rv_tokens = nltk.word_tokenize(rv1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"rv_tokens","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Define a function to plot word cloud\ndef plot_cloud(wordcloud):\n    # Set figure size\n    plt.figure(figsize=(26, 8))\n    # Display image\n    plt.imshow(wordcloud) \n    # No axis details\n    plt.axis(\"off\");","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Generate word cloud\nwordcloud = WordCloud(width = 5000, height = 2000, random_state=1, background_color='salmon', colormap='Pastel1', collocations=False, stopwords = STOPWORDS).generate(cloud_words)\n# Plot\nplot_cloud(wordcloud)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"for i in range(len(cities_count['Opportunity'])):\n    cities_count['Opportunity'][i]=re.sub(r\"[^a-zA-Z,]\",\"\",str(cities_count['Opportunity'][i]))\n    cities_count['Opportunity'][i]= cities_count['Opportunity'][i].lower()\n    cities_count['Opportunity'][i]=cities_count['Opportunity'][i].split()\n    ps = PorterStemmer()\n    cities_count['Opportunity'][i] = [ps.stem(word) for word in cities_count['Opportunity'][i] if not word in set(stopwords.words('english'))]\n    cities_count['Opportunity'][i] = ''.join(cities_count['Opportunity'][i])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"from nltk.tokenize import sent_tokenize, word_tokenize, regexp_tokenize # funtions for standard tokenisation\nfrom nltk.tokenize import TweetTokenizer # function for tweets tokenization","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"tknzr = TweetTokenizer() # initialization of Tweet Tokenizer\n\ndef mean_words_length(text):\n    words = word_tokenize(text)\n    word_lengths = [len(w) for w in words]\n    return round(np.mean(word_lengths),1)\n\n# words count\ncities_count['words_count'] = cities_count['Opportunity'].apply(lambda x: len(tknzr.tokenize(x))) # number of words in tweet\n\n# numbers count\nnumbers_regex = r\"(\\d+\\.?,?\\s?\\d+)\"\ncities_count['numbers_count'] = cities_count['Opportunity'].apply(lambda x: len(regexp_tokenize(x, numbers_regex))) # count of extracted mentions\n\n# hashtags count\nhashtags_regex = r\"#\\w+\"\ncities_count['hashtags_count'] = cities_count['Opportunity'].apply(lambda x: len(regexp_tokenize(x, hashtags_regex))) # count of extracted hashtags\n\n\n# mean words length\ncities_count['mean_words_length'] = cities_count['Opportunity'].apply(mean_words_length) # count of extracted mentions\n\n# characters count\ncities_count['characters_count'] = cities_count['Opportunity'].apply(lambda x: len(x)) # count of extracted mentions","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"from nltk.corpus import stopwords\nimport string\n\n# lowercase tokens\ncities_count['lowercase_bag_o_w']=cities_count['Opportunity'].apply(lambda x: [w for w in tknzr.tokenize(x.lower())])\n\n# stopwords\ncities_count['stopwords']=cities_count['lowercase_bag_o_w'].apply(lambda x: [t for t in x if t in stopwords.words('english')])\n\n# stopwords count\ncities_count['stopwords_count']=cities_count['stopwords'].apply(lambda x: len(x))\n\n# alpha words only (excludes mentions and hashtags)\ncities_count['alpha_only']=cities_count['lowercase_bag_o_w'].apply(lambda x: [t for t in x if t.isalpha()])\n\n# counts of alpha words only\ncities_count['alpha_count']=cities_count['alpha_only'].apply(lambda x: len(x))\n\n# counts of punctuation marks only\npunctuation_regex = r\"[^\\w\\s]\"\ncities_count['punctuation_count']=cities_count['Opportunity'].apply(lambda x: len(regexp_tokenize(x, punctuation_regex)))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cities_count","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}