{"cells":[{"metadata":{},"cell_type":"markdown","source":"# Calculation notebook\nThis notebook holds the data extraction and processing part of the ADP:ABC team submission. It is split into the 3 main headings which align with the Sections in the descriptive notebook, and creation of additional helpful datasets:\n* Preparing external data\n* Section A: Ambition KPI\n* Section B: Benefits\n* Section C: Collaboration\n* Code appendix"},{"metadata":{"trusted":true},"cell_type":"code","source":"# Settings\nCURRENT_YEAR = 2020","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"import pandas as pd\n\nfname = '../input/cdp-unlocking-climate-solutions/Cities/Cities Responses/{}_Full_Cities_Dataset.csv'\ndf_cities = pd.read_csv(fname.format('2020'))\ndf_cities_2019 = pd.read_csv(fname.format('2019'))\ndf_cities_2018 = pd.read_csv(fname.format('2018'))\ndf_citites_all_years = pd.concat([df_cities, df_cities_2019, df_cities_2018])","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Preparing external data\n## Append city population and GDP data\nOur amibition KPI will be in 'per capita' terms, and evaluated in the context of city GDP. The following cells prepare population projections and GDP data for joining with the CDP questionnaire data.\n\n### City population data\nWe source projections for _country level_ populations from the World Bank. We then project each city's latest population estimate (CDP city questionnaire Q0.5 or Q0.6 depending on the year) by the yearly percentage change in the relevant country's urban population by this World Bank forecast.\n\n#### Limitations\n* Taiwan and the State of Palestine are not represented in the World Bank data; we sourced UN estimates. These are at a total country level and not urban population, so the projection for these cities assumes a flat level of urbanisation to 2050.\n* Where the latest population figure from the questionnaire is for a year preceding 2010 (or after 2050, though no cases apply) we assume the population is equal in 2010. This is so that we can join on the World Bank data, which starts in 2010."},{"metadata":{"trusted":true},"cell_type":"code","source":"from typing import List, Tuple, Set\nimport math\nimport collections\n\nPopulation = collections.namedtuple(\n 'Population', ['year', 'population', 'reported_year']\n)\n\ndf = pd.concat([\n df_cities[df_cities['Question Number'] == '0.5'],\n df_cities_2019[df_cities_2019['Question Number'] == '0.5'],\n df_cities_2018[df_cities_2018['Question Number'] == '0.6']\n])\n\nordered_questions = [\n 'Current population', \n 'Current population year', \n 'Projected population', \n 'Projected population year'\n] # random but consistent order\nordered_questions_dict = {i: q for i, q in enumerate(ordered_questions)}\n\n# Create tuples for all population records / forecasts\n# Note: no questionnaires have multiple rows for this question so it has been omitted,\n# but in future be careful to check this; we use Column Name as we believe it is less \n# like to change between questionnaires for different years than Column Number\ndef create_year_pop_tuples(r):\n return [\n Population(r['Current population year'], \n r['Current population'], \n r['Year Reported to CDP']), \n Population(r['Projected population year'], \n r['Projected population'], \n r['Year Reported to CDP'])\n ]\n\n# Transpose relevant rows to coulmns\ndf_pop = (\n df.sort_values(by=['Year Reported to CDP', 'Column Name'])\n .groupby(['Organization', 'Country', 'Year Reported to CDP'])['Response Answer'] \n .apply(lambda df: df.reset_index(drop=True))\n .unstack()\n .reset_index()\n .rename(columns=ordered_questions_dict)\n)\n# Create tuples\ndf_pop[ordered_questions] = df_pop[ordered_questions].astype(float)\ndf_pop['year_pop'] = df_pop.apply(create_year_pop_tuples, axis=1)\ndf_pop = (\n df_pop.groupby(['Organization', 'Country'])\n .agg({'year_pop': 'sum'})\n .reset_index()\n)\n\n# Where multiple values exist for the same population year, take the most recent questionnaire's\ndef take_latest_population_estimate(l: List[Tuple[int]]) -> Set[Tuple[int]]:\n \"\"\" \n Accepts a list of namedtuples and returns only the most recently specified (attribute \n 'reported_year') for each year (attribute 'year'). In the case of conflicts, takes\n the highest population (attribute 'population'). Filters tuples with nan values.\n \"\"\"\n l = [\n t for t in l \n if not any(isinstance(n, float) and math.isnan(n) for n in t)\n ] # remove nan\n _l = []\n for key in sorted({t.year for t in l}):\n group = filter(lambda t: t.year == key, l)\n _l.append(max(group, key=lambda t: (t.reported_year, -t.population)))\n return set(_l)\n\ndf_pop['year_pop'] = df_pop['year_pop'].apply(take_latest_population_estimate)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Load World Bank urban population estimates and projections\nfpath = \"../input/global-population-estimates/data.csv\"\ndf_wcp = pd.read_csv(fpath)\n\ncols = ['Country Name', 'Country Code', 'Series Name'] \ncols += [f'{str(y)} [YR{str(y)}]' for y in range(2010, 2051)]\nseries = ['Urban population'] # 'Urban population growth (annual %)', 'Population, total'\ndf_wcp = df_wcp[df_wcp['Series Name'].isin(series)][cols]\ndf_wcp['Country Name'] = df_wcp['Country Name'].apply(lambda s: s.lower())\n\ncols_rename = {f'{str(y)} [YR{str(y)}]': str(y) for y in range(2010, 2051)}\ndf_wcp = df_wcp.rename(columns=cols_rename)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"## Manual adjustments\n\n# Fill missing population data from CDP questionnaires\nd = {\n 'Comune di Lucca': Population(2017, 88397, 2020), # lstat\n 'Comune di Reggio Emilia': Population(2017, 171491, 2020), # lstat\n 'Dura Municipality': Population(2007, 28268, 2020), # Wikipedia \n 'Melton City Council': Population(2018, 51100, 2020), # ONS UK \n 'Piura': Population(2017, 484475, 2020), # Wikipedia\n 'Municipalidad de Colina': Population(2002, 77815, 2020), # Wikipedia \n 'Municipality of Hjørring': Population(2020, 25780, 2020), # Wikipedia \n 'Municipality of Ilha de Mozambique': Population(2020, 14000, 2020), # Wikipedia\n 'Municipality of Villanueva': Population(2018, 433734, 2018), # Wikipedia, census\n 'Municipalité de Rabat': Population(2014, 577827, 2020), # Wikipedia\n 'Municipio de Maneiro': Population(2011, 48952, 2011), # Wikipedia, census\n 'Município de Câmara de Lobos': Population(2020, 36000, 2020), # UNESCO estimate\n 'Prefeitura de Alexânia': Population(2007, 20033, 2020), # Wikipedia\n 'Prefeitura de Pedra Bela': Population(2015, 6044, 2020), # Wikipedia\n 'Prefeitura de São Carlos': Population(2010, 116765, 2020), # \n 'Puente Piedra': Population(2011, 10556, 2011), # Wikipedia, census\n 'West Coast District Municipality': Population(2018, 450610, 2018), # SA-DSD estimate\n 'Wrexham council': Population(2018, 136126, 2018) # ONS UK\n}\n\nfor city_name, city_pop_tuple in d.items():\n df_pop.loc[df_pop['Organization'].str.contains(city_name), 'year_pop'] = (\n set((city_pop_tuple, ))\n )\n\nmsg = '{} organizations without population information.'\nprint(msg.format(df_pop[df_pop['year_pop'].apply(lambda x: len(x) < 1)].shape[0]))\n\n\n# Add Taiwan and Palestine (population in thousands) to World Bank data\n# UN Department of Economic and Social Affairs: World Population Prospects 2019\n# Source: https://population.un.org/wpp/DataQuery/\n# Licence: Creative Commons BY 3.0 IGO: \n# http://creativecommons.org/licenses/by/3.0/igo/\n# Note: by using total population (data limitation) we're assuming no \n# change in urbanisation\ntaiwan_pop = {2010: 23188, 2011: 23269, 2012: 23347, 2013: 23422, 2014: 23492, 2015: 23557, 2016: 23618, 2017: 23675, 2018: 23726, 2019: 23774, 2020: 23817, 2021: 23855, 2022: 23889, 2023: 23918, 2024: 23943, 2025: 23965, 2026: 23983, 2027: 23998, 2028: 24007, 2029: 24012, 2030: 24011, 2031: 24005, 2032: 23992, 2033: 23972, 2034: 23945, 2035: 23908, 2036: 23863, 2037: 23809, 2038: 23746, 2039: 23674, 2040: 23593, 2041: 23503, 2042: 23405, 2043: 23299, 2044: 23187, 2045: 23069, 2046: 22946, 2047: 22818, 2048: 22686, 2049: 22551, 2050: 22413}\npalestine_pop = {2010: 4056, 2011: 4150, 2012: 4242, 2013: 4334, 2014: 4429, 2015: 4529, 2016: 4636, 2017: 4747, 2018: 4863, 2019: 4981, 2020: 5101, 2021: 5223, 2022: 5346, 2023: 5469, 2024: 5594, 2025: 5718, 2026: 5843, 2027: 5967, 2028: 6092, 2029: 6217, 2030: 6342, 2031: 6467, 2032: 6593, 2033: 6719, 2034: 6845, 2035: 6971, 2036: 7097, 2037: 7223, 2038: 7349, 2039: 7474, 2040: 7599, 2041: 7724, 2042: 7848, 2043: 7972, 2044: 8095, 2045: 8217, 2046: 8339, 2047: 8460, 2048: 8580, 2049: 8698, 2050: 8816}\ntaiwan_pop = {str(k): v * 1000 for k, v in taiwan_pop.items()}\npalestine_pop = {str(k): v * 1000 for k, v in palestine_pop.items()}\n\ndf_manual_pop = pd.concat([\n pd.DataFrame(taiwan_pop, index=[0]), \n pd.DataFrame(palestine_pop, index=[1])\n])\ndf_manual_pop['Country Name'] = ['taiwan, greater china', 'state of palestine']\ndf_manual_pop['Country Code'] = ['TWN', 'PSE']\ndf_manual_pop['Series Name'] = 'Total population'\ndf_wcp = pd.concat([df_wcp, df_manual_pop]).reset_index()\n\n\n# Map country names to World Bank dataset for merging\ncountry_name_map_wb = {\n 'united kingdom of great britain and northern ireland': 'united kingdom',\n 'china, hong kong special administrative region': 'hong kong sar, china',\n 'republic of korea': 'korea, rep.',\n 'republic of moldova': 'moldova',\n 'state of palestine': 'palestinian territory, occupied',\n 'united republic of tanzania': 'tanzania, united republic of',\n 'bolivia (plurinational state of)': 'bolivia',\n 'viet nam': 'vietnam',\n \"côte d'ivoire\": \"cote d'ivoire\",\n 'democratic republic of the congo': 'congo, dem. rep.',\n 'united states of america': 'united states',\n 'venezuela (bolivarian republic of)': 'venezuela, rb',\n 'tanzania, united republic of': 'tanzania',\n 'russia': 'russian federation'\n}\n\ndef _replace(s, d):\n return d[s] if s in d.keys() else s\n\ndf_pop['country_lower'] = df_pop['Country'].apply(lambda s: s.lower())\ndf_pop['country_lower'] = (\n df_pop['country_lower'].apply(lambda s: _replace(s, country_name_map_wb))\n)\n_df_pop = df_pop.merge(df_wcp, left_on='country_lower', right_on='Country Name', how='left')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Apply growth in urban population to city population\n# Make the assumption that the latest historical estimate of city population is correct\n# Note: nasty error handling: years outside of 2010 <= year <= 2050 converted to 2020\nimport numpy as np\n\n\ndef get_latest_current_population_estimate(l : List[Tuple[int]]) -> Tuple[int]:\n if not l:\n return Population(0, 0, 0)\n return min(l, key=lambda t: np.abs(t.year - CURRENT_YEAR))\n\n\n_df_pop['latest_year_pop_tuple'] = (\n _df_pop['year_pop'].apply(get_latest_current_population_estimate)\n)\n_df_pop['latest_year_pop'] = (\n _df_pop['latest_year_pop_tuple'].apply(lambda t: int(t.population))\n)\n# dangerous assumption; to be replaced!\n_df_pop['latest_year'] = _df_pop['latest_year_pop_tuple'].apply(\n lambda t: int(t.year) if t.year >= 2010 and t.year <= 2050 else 2010 \n)\n_df_pop['prop_urban_pop_in_city'] = _df_pop.apply(\n lambda r: r['latest_year_pop'] / max(r.loc[str(r['latest_year'])], 1), \n axis=1\n)\nfor c in cols_rename.values():\n _df_pop[c] = _df_pop[c] * _df_pop['prop_urban_pop_in_city']","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"A sample of the population data:"},{"metadata":{"trusted":true},"cell_type":"code","source":"_df_pop.sample(3)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### City GDP\nWe use city GDP to contrast the ambition of a city with its GDP, an indicator of the wealth of the city. The following cells import and clean this data. We use the most recently available data for the 2018 year.\n\n#### Limitations\n* GDP data at regional or city level was only identified for the United States. As such, while the method is suitable for any country, the analysis is limited to the US.\n* The GDP data available is for Metropolitan Statistical Areas (MSA), as defined by the US BEA. Broadly, this is comprised of a single city and suits our purpose, however when multiple cities are in close proximity they fall under the same MSA and their GDP is combined. In this case we use the MSA's combinated population figuresc to give each city the same, shared GDP per capita figure. These cities are marked with the same 'idx' field in the output. \n* Approximately 40% of city organizations who report to CDP do not have cities covered within the BEA data. To estimate the GDP per capita of these cities:\n * We calculate the 'uplift' in GDP per capita of cities who _are_ represented relative to the GDP per capita of the entire state.\n * We perform the population-weighted average of these uplifts.\n * If no data exists for the state, apply the median GDP per capita of the US cities who are represented in the CDP questionnaire."},{"metadata":{"trusted":true},"cell_type":"code","source":"## City GDP\nimport pandas as pd\nimport re\n\n\nfpath_gdp = '../input/cagdp1-gdp-by-us-metropolitan-area/CAGDP1_GDP_by_metropolitan_area.csv'\nfpath_pop = '../input/cagdp1-gdp-by-us-metropolitan-area/CAINC1_2018_population_by_metropolitan_area.csv'\n_df_gdp = pd.read_csv(fpath_gdp).rename(columns={'2018': '2018_gdp'})\n_df_gdp_pop = pd.read_csv(fpath_pop)[['GeoFips', '2018']].rename(\n columns={'2018': '2018_pop'}\n)\ndf_gdp = _df_gdp.merge(_df_gdp_pop, on='GeoFips', how='left')\nmsg = 'Merged 2018 population by metropolitan area with GDP data, ' + \\\n 'dropping {} records.'\nprint(msg.format(_df_gdp.shape[0] - df_gdp.shape[0]))\n\ndf_gdp['2018_gdp'] = df_gdp['2018_gdp'] * 1000\ndf_gdp['2018_gdp_per_capita'] = df_gdp['2018_gdp'] / df_gdp['2018_pop'] \ndf_gdp = df_gdp.iloc[1:] # cut off total US record\n\n# Extract country code, clean city name\ndef extract_country_code(s):\n m = re.search(r'\\b[A-Z]{2}\\b', str(s))\n if m:\n match = m.group()\n return match\n return None\n \ndf_gdp['state'] = df_gdp['GeoName'].apply(extract_country_code)\ndf_gdp['city'] = df_gdp['GeoName'].apply(lambda s: s.split(',')[0].lower())\ncols = ['city', 'state', '2018_gdp', '2018_pop', '2018_gdp_per_capita']\ndf_gdp = df_gdp[cols]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Add state information\nfpath_gdp_state = '../input/cagdp1-gdp-by-us-metropolitan-area/SAGDP1_2018_GDP_by_state.csv'\nfpath_pop_state = '../input/cagdp1-gdp-by-us-metropolitan-area/SAINC1_2018_population_by_state.csv'\n_df_gdp_state = pd.read_csv(fpath_gdp_state).rename(columns={'2018': '2018_gdp_state'})\n_df_gdp_pop_state = pd.read_csv(fpath_pop_state)[['GeoFips', '2018']].rename(\n columns={'2018': '2018_pop_state'}\n)\ndf_gdp_state = _df_gdp_state.merge(\n _df_gdp_pop_state, \n on='GeoFips', \n how='left'\n)\n\ndf_gdp_state['2018_gdp_state'] = df_gdp_state['2018_gdp_state'] * 1e6\ndf_gdp_state['2018_gdp_per_capita_state'] = (\n df_gdp_state['2018_gdp_state'] / df_gdp_state['2018_pop_state'] \n)\n\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\ndf_gdp_state['state_id'] = df_gdp_state['GeoName'].map(us_state_abbrev).dropna()\n\n# Merge state information into city GDP dataframe\ndf_gdp = df_gdp.merge(\n df_gdp_state[['state_id', '2018_gdp_per_capita_state']],\n left_on='state',\n right_on='state_id',\n how='left'\n)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# GDP information for some metropolitan areas is split between multiple \n# cities. Split these cities whilst we perform city-level calculations - \n# we'll give citiesin the municipalities the same GDP per capita, eventually\n\n# Add index, split into rows, use groupby later\ndf_gdp[df_gdp['city'].str.contains('-')].shape\ndf_gdp['idx'] = df_gdp.index\n\ns = df_gdp['city'].str.split('-').apply(pd.Series, 1).stack()\ns.index = s.index.droplevel(-1)\ns.name = 'city_split'\ndf_gdp = df_gdp.join(s)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"from typing import Tuple, List\nfrom math import cos, asin, sqrt, pi\nimport ast\n\n\ndef get_city_with_county_and_lat_long() -> pd.DataFrame:\n \"\"\"\n Merge US city locations data with supplementary data to find state. It's unfortunately\n a bit ridiculous but our latlong dataset doesn't contain the state, which means it is\n difficult to join on some external datasets which use county. Hence, add this function \n adds the state by matching a pre-prepared city location dataset (see Appendix: get city \n latitude / longitude) with the supplementary data: finding the state of the closest city \n via the Haversine distance (straight line on a sphere) between latitude/longitude values.\n \"\"\"\n # US city lat-long and clean organization field\n # (Later used to create the visualisation, includes all CDP reporting cities)\n fpath = '../input/cdp-city-locations-with-latlong/city_locs_with_latlong.csv'\n df_city_locs = pd.read_csv(\n fpath,\n index_col='Unnamed: 0', \n converters={'lat_long': lambda x: ast.literal_eval(x)} # load tuple\n )\n df_us_city_locs = df_city_locs[df_city_locs['alpha2_code'] == 'US']\n\n\n # Supplementary data for US state\n fpath = '../input/cdp-unlocking-climate-solutions/Supplementary Data/Simple Maps US Cities Data/uscities.csv'\n df_cities_meta = pd.read_csv(fpath)\n\n def distance(latlong1, latlong2):\n \"\"\" \n Optimised Haversine distance calculation.\n Source: https://stackoverflow.com/questions/27928\n \"\"\"\n lat1, lon1 = latlong1\n lat2, lon2 = latlong2\n p = pi/180\n a = 0.5 - cos((lat2-lat1)*p)/2 + cos(lat1*p) * cos(lat2*p) * (1-cos((lon2-lon1)*p))/2\n return 12742 * asin(sqrt(a))\n\n\n def find_closest_latlong(latlong: Tuple[float, float], \n latlongs: List[Tuple[float, float]]) -> Tuple[float, float]:\n return min(latlongs, key=lambda x: distance(latlong, x))\n\n\n cities_meta_latlong = list(zip(df_cities_meta['lat'], df_cities_meta['lng']))\n df_cities_meta['lat_long'] = cities_meta_latlong\n df_us_city_locs['lat_long_match'] = df_us_city_locs['lat_long'].apply(\n lambda x: find_closest_latlong(x, cities_meta_latlong)\n )\n\n df_us_city_locs = df_us_city_locs.merge(\n df_cities_meta, \n left_on='lat_long_match', \n right_on='lat_long',\n how='left'\n )\n\n # Note: we take population data from World bank projections, not the supplementary data, \n # and hence it is discarded here\n cols = ['organization', 'organization_clean', 'city', 'city_ascii', 'state_id', \n 'state_name', 'county_name', 'country', 'country_lower', 'alpha2_code', \n 'lat_long_y']\n df_us_city_locs = df_us_city_locs.loc[:, cols]\n df_us_city_locs['city'] = df_us_city_locs['city'].str.lower()\n return df_us_city_locs\n\n\ndf_us_city_locs = get_city_with_county_and_lat_long()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Merge cleaned city data with GDP data\ndf_us_city_gdp = df_us_city_locs.merge(\n df_gdp,\n left_on=['city', 'state_id'], \n right_on=['city_split', 'state'], \n how='left'\n)\n\ncols = ['organization', 'organization_clean', 'city_x', 'state_id_x', 'state_name', \n 'county_name' ,'country_lower', 'alpha2_code', 'lat_long_y', '2018_gdp',\n '2018_pop',\t'2018_gdp_per_capita', '2018_gdp_per_capita_state', 'idx']\ndf_us_city_gdp = df_us_city_gdp[cols].rename(columns={\n 'city_x': 'city', \n 'state_id_x': 'state',\n 'lat_long_y': 'lat_long'\n})","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_us_city_gdp['2018_gdp_per_capita'].hist(bins=20)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"## Fill missing GDP per capita values\n# For each state, get a weighted average 'uplift' in GDP per capita, relative \n# to the state GDP per capita, of the cities for which do have GDP information. \n# This will act as a proxy for urbna-vs-rural GDP (as production will tend to\n# pool in the cities or manufacturing areas). We're going to apply this uplift \n# to state-level GDP (which has far better availability) to estimate GDP per\n# capita for cities without GDP figures.\ndef f(r):\n \"\"\" Get prop of city's GDP/capita relative to state's GDP/capita. \"\"\"\n prop_gdp_pc = r['2018_gdp_per_capita'] / r['2018_gdp_per_capita_state']\n weighted_prop_gdp_pc = prop_gdp_pc * r['2018_pop']\n return weighted_prop_gdp_pc\n\n# Get weighted average city GDP/capita uplift by state\nweighted_avg_city_vs_state_gdp_per_capita_uplift = (\n df_us_city_gdp.groupby('state')\n .apply(lambda g: f(g).sum() / g['2018_pop'].sum())\n .rename('2018_gdp_per_capita_state_uplift')\n .reset_index()\n)\n# Add state GDP information\ndf_gdp_state = df_gdp_state.merge(\n weighted_avg_city_vs_state_gdp_per_capita_uplift,\n left_on='state_id',\n right_on='state',\n how='left'\n)\n# Calculate GDP per capita estimate by applying uplift to state GDP/capita\ndf_gdp_state['2018_gdp_per_capita_estimate'] = (\n df_gdp_state['2018_gdp_per_capita_state'] * \n df_gdp_state['2018_gdp_per_capita_state_uplift']\n)\n\n# Merge with US city dataframe\ndf_us_city_gdp = df_us_city_gdp.merge(\n df_gdp_state[['state', '2018_gdp_per_capita_estimate']], \n on='state', \n how='left'\n)\n\n# Fix missing values\n_df = df_us_city_gdp[pd.isnull(df_us_city_gdp['2018_gdp_per_capita_estimate'])]\nl = _df[['organization', 'county_name', 'state']].to_dict(orient='records')\nprint(f\"Missing information for {len(set(_df['state']))} states. Organizations:\")\nfor d in l:\n print(f\" - {d['organization']}, {d['county_name']}, {d['state']}\")\n\ncity_gdp_per_capita_median = df_us_city_gdp['2018_gdp_per_capita'].median()\nprint(f'Using city median GDP per capita: ${city_gdp_per_capita_median:.0f}.')\ndf_us_city_gdp.loc[:, '2018_gdp_per_capita_estimate'].fillna(\n city_gdp_per_capita_median, \n inplace=True\n)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def f(x):\n return 'ESTIMATE' if np.isnan(x) else 'ACTUAL'\n\ndef g(r):\n if np.isnan(r['2018_gdp_per_capita']):\n return r['2018_gdp_per_capita_estimate'] \n return r['2018_gdp_per_capita']\n\n \ndf_us_city_gdp['2018_gdp_per_capita_status'] = (\n df_us_city_gdp['2018_gdp_per_capita'].apply(f)\n)\ndf_us_city_gdp['2018_gdp_per_capita_value'] = (\n df_us_city_gdp.apply(g, axis=1)\n)\ncols = ['organization', 'organization_clean', 'city', 'county_name', \n 'state', 'state_name', 'country_lower', 'alpha2_code', 'lat_long', \n '2018_gdp', '2018_pop', '2018_gdp_per_capita_status', \n '2018_gdp_per_capita_value', '2018_gdp_per_capita_state', 'idx']\ndf_us_city_gdp = df_us_city_gdp.loc[:, cols]","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"A sample of the GDP per capita data:"},{"metadata":{"trusted":true},"cell_type":"code","source":"df_us_city_gdp.sample(3)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Save the GDP per capita dataframe for use in later analysis:"},{"metadata":{"trusted":true},"cell_type":"code","source":"cols = ['organization', 'city', '2018_gdp_per_capita_status', '2018_gdp_per_capita_value']\n_df_us_city_gdp = df_us_city_gdp[cols].copy()\n\n# Create manual clusters of GDP\n# In future this could be performed with a simple clustering algorithm or by number of\n# standard-deviations from the mean.\n_df_us_city_gdp.loc[_df_us_city_gdp['2018_gdp_per_capita_value'] < 45000, 'group'] = 'low'\n_df_us_city_gdp.loc[_df_us_city_gdp['2018_gdp_per_capita_value'] > 45000, 'group'] = 'medium-low'\n_df_us_city_gdp.loc[_df_us_city_gdp['2018_gdp_per_capita_value'] > 55000, 'group'] = 'medium-high'\n_df_us_city_gdp.loc[_df_us_city_gdp['2018_gdp_per_capita_value'] > 71000, 'group'] = 'high'\n_df_us_city_gdp.to_csv('city_gdp_per_capita.csv')\n\n_df_us_city_gdp['2018_gdp_per_capita_value'].hist(bins=20)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Calculate ambition KPI\nBelow, we calculate the ambition KPI for cities across the world who have reported sufficient information to generate an emissions trajectory.\n\n#### Limitations\n* We produce the KPI only for cities who report emissions targets which:\n * cover all emissions sources included in the city inventory (Q5.0a)\n * cover the entire city area or larger, relative to the city boundary (Q5.0a). We treat these boundaries equally.\n* We assume a _linear trajectory_ from the starting year to the target year.\n* As not to unfairly penalise cities with long-term predictions, we make the assumption that the target for years beyond the last target specified is kept constant. I.e., if the city has a target to 2030, that the target performance in all future year remains at the 2030 level.\n* Where multiple targets exist for a city in the same year, we take the lower target. \n* We assume the 2020 emissions performance of the city is in line with its target, linearly between the closest targets or base years which span the 2020 year. I.e., if we know the 2015 city emissions were 10MtCO2e and the 2025 target is 5MtCO2e, we'd assume a 2020 performance of 7.5MtCO2e."},{"metadata":{"trusted":true},"cell_type":"code","source":"# Settings\nMAX_YEAR = 2050\nEXTRAPOLATION_STRATEGY = 'constant'","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"city_list = set(df_cities['Organization'])\n\n_df = df_cities[\n (df_cities['Question Number'] == '5.0a') & \n (df_cities['Column Number'] == 1) &\n (df_cities['Response Answer'] == 'All emissions sources included in city inventory')\n]\nc = _df.groupby('Organization').agg({'Row Number': 'min'}).count()[0]\nprint('City count: {}'.format(len(city_list)))\nprint('Cities reporting emissions targets: {}'.format(c))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"## Apply conditions\n_df = df_cities[df_cities['Question Number'] == '5.0a']\n\n# All emission sources covered in target\nl1 = lambda g: g[g['Response Answer'] == 'All emissions sources included in city inventory']\n_df_q1 = _df.groupby('Organization').apply(l1).droplevel(0)\n# Target is city-wide (at minimum)\nl2 = lambda g: g[g['Response Answer'].isin([\n 'Same – covers entire city and nothing else', \n 'Larger – covers the whole city and adjoining areas'\n])]\n_df_q2 = _df.groupby('Organization').apply(l2).droplevel(0)\n\ncols = ['Organization', 'Row Number']\n_df_q = _df_q1.merge(_df_q2, on=['Organization', 'Row Number'], how='inner')[cols]\nprint('Organizations with targets meeting criteria: {}'.format(len(set(_df_q['Organization']))))\ndf_ambitions = _df.merge(_df_q[cols], on=cols, how='inner')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Question = 5.0a, Column Number = 12 has multiple values; compress rows into single list\ndf_ambitions_col12_list = (\n df_ambitions[df_ambitions['Column Number'] == 12]\n .groupby(['Organization', 'Column Number', 'Row Number'])['Response Answer']\n .apply(list)\n .reset_index()\n)\n\na = df_ambitions.shape[0]\ndf_ambitions = df_ambitions.merge(\n df_ambitions_col12_list, on=['Organization', 'Column Number', 'Row Number'], how='left'\n)\ndf_ambitions.loc[df_ambitions['Column Number'] == 12, 'Response Answer_x'] = (\n df_ambitions.loc[df_ambitions['Column Number'] == 12, 'Response Answer_y']\n)\ndf_ambitions = (\n df_ambitions.drop_duplicates(subset=['Organization', 'Column Number', 'Row Number'], keep='first')\n .rename(columns={'Response Answer_x': 'Response Answer'})\n .drop('Response Answer_y', axis=1)\n)\nb = df_ambitions.shape[0]\nprint(f'Combined initiatives to reduce duplicate data, removing {a - b} rows.')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Create column map to make feature creation steps simpler\ncolumn_map = dict(zip(df_ambitions['Column Number'], df_ambitions['Column Name']))\ncolumn_map = {k - 1: v for k, v in column_map.items()}","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Transpose data into column format\ndf_ambitions_T = (\n df_ambitions.sort_values(by=['Organization', 'Column Number', 'Row Number'])\n .groupby(['Organization', 'Row Number'])['Response Answer']\n .apply(lambda df: df.reset_index(drop=True))\n .unstack()\n .reset_index()\n)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Filter base year emissions (errors found, see Yaounde)\nimport numpy as np\nfrom scipy.stats import zscore, norm\n\n# Remove records where base year emissions are invalid (they equal the base year) \n# and drop other rows with NaN values\na = df_ambitions_T.shape[0]\ndf_ambitions_T.loc[df_ambitions_T[5] == df_ambitions_T[3], 5] = np.nan\ndf_ambitions_T = df_ambitions_T.dropna(subset=[3, 5, 7, 8])\nb = df_ambitions_T.shape[0]\nprint(f'Removed {a - b} rows with outliers in base year emissions or NaN values.')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Add World Bank population projections to convert to emission per capita\ndf_ambitions_T = df_ambitions_T.merge(_df_pop, on='Organization', how='left')\ndf_ambitions_T = df_ambitions_T.rename(columns={'Organization': 'organization'})","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Take 2010 or 2050 population for years outside of this\n# base_year = 3, base_year emissions = 5, target_year = 7, target_year emissions = 8\ndf_ambitions_T['_3'] = df_ambitions_T[3].apply(lambda y: y if int(y) >= 2010 else '2010')\ndf_ambitions_T['_7'] = df_ambitions_T[7].apply(lambda y: y if int(y) >= 2010 else '2010')\ndf_ambitions_T['_7'] = df_ambitions_T[7].apply(lambda y: y if int(y) <= 2050 else '2050')\n\n# Use lookup function to get population in target / base year\ndf_ambitions_T['_3_pop'] = df_ambitions_T.lookup(\n df_ambitions_T.index, df_ambitions_T['_3']\n)\ndf_ambitions_T['_7_pop'] = df_ambitions_T.lookup(\n df_ambitions_T.index, df_ambitions_T['_7']\n)\n\n# Divide emissions by population => emission per capita\ndf_ambitions_T['_5'] = df_ambitions_T[5].astype(float) / df_ambitions_T['_3_pop']\ndf_ambitions_T['_8'] = df_ambitions_T[8].astype(float) / df_ambitions_T['_7_pop']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Create city emissions trajectories, based on their base/target info\n# base_year = 3, base_year emissions = 5, target_year = 7, target_year emissions = 8\ndf_ambitions_T['t1'] = df_ambitions_T[[3, 5]].apply(tuple, axis=1)\ndf_ambitions_T['t2'] = df_ambitions_T[[7, 8]].apply(tuple, axis=1)\ndf_ambitions_T['row_trajectory'] = df_ambitions_T[['t1', 't2']].apply(list, axis=1)\ndf_trajectories = (\n df_ambitions_T.groupby('organization')['row_trajectory']\n .apply(sum)\n .reset_index()\n .rename(columns={'row_trajectory': 'org_trajectory'})\n)\ndf_ambitions_T = df_ambitions_T.merge(df_trajectories, on='organization', how='left')\n\n# Repeat the above for per capita trajectories\n# TODO: make function\ndf_ambitions_T['t1'] = df_ambitions_T[['_3', '_5']].apply(tuple, axis=1)\ndf_ambitions_T['t2'] = df_ambitions_T[['_7', '_8']].apply(tuple, axis=1)\ndf_ambitions_T['row_trajectory_per_capita'] = (\n df_ambitions_T[['t1', 't2']].apply(list, axis=1)\n)\nrename_dict = {'row_trajectory_per_capita': 'org_trajectory_per_capita'}\ndf_trajectories = (\n df_ambitions_T.groupby('organization')['row_trajectory_per_capita']\n .apply(sum)\n .reset_index()\n .rename(columns=rename_dict)\n)\ndf_ambitions_T = df_ambitions_T.merge(df_trajectories, on='organization', how='left')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"We've taken only the year and base / target date and emissions combinations as a tuple, however we've prepared the data such that it could be extracted as a dictionary for additional data when plotting."},{"metadata":{"trusted":true},"cell_type":"code","source":"## Create visualisation tooltips\nfrom typing import Callable, List, Tuple\n\ndef sort_list_of_tuples_by_first_element(l: List[Tuple[int, int]],\n descending: bool = False\n ) -> List[Tuple[int, int]]:\n return sorted(list(set(l)), key=lambda t: (t[0], -t[1]), reverse=descending)\n\n\ndef get_relative_tuples(f: Callable, l: List[Tuple[int, int]]) -> List[Tuple[int, int]]:\n \"\"\"\n Makes the second element of each tuple relative to second element of a tuple chosen \n using function f. E.g. [(0, 20), (1, 8)] => [(0, 1.0), (0, 0.4)].\n \"\"\"\n t_min_year = f(l, key=lambda t: (int(float(t[0])), -float(t[1])))\n return [(int(_t[0]), float(_t[1]) / float(t_min_year[1])) for _t in l]\n\n\ndef generate_trajectory_tooltips(l: List[Tuple[int, int]]) -> str:\n \"\"\" Convert trajectory tuples to string for tooltips. \"\"\"\n # Ensure tuples are ordered and unique by year (take max t[1])\n l = sort_list_of_tuples_by_first_element(l)\n l = [(y, next(v for k, v in l if k == y)) for y in dict(l).keys()]\n s = '{:.0f} -> {:.0f}%'\n return '
'.join([s.format(t[0], t[1] * 100) for t in l])\n\n\ndf_ambitions_T['org_trajectory_rel'] = (\n df_ambitions_T['org_trajectory'].apply(lambda t: get_relative_tuples(min, t))\n)\ndf_ambitions_T['org_trajectory_rel_str'] = (\n df_ambitions_T['org_trajectory_rel'].apply(generate_trajectory_tooltips)\n)\n\n# Note: relative trajectory not required for per capita trajectories (not written)\n# Calculated below after inclusion of current year","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"#### Current performance\nIdeally, we'd like to be able to check ambition *relative to a city's current condition*, and hence evaluate it's current progress towards its target(s). However, responses to the question 'Percentage of target achieved so far' appear to have quality issues: a significant proportion of organisations do not submit an entry for this field and a visual check of some of those who do suggest it is not completed consistently (for example, using the same percentage against different targets; in this case it is not clear which base level of emissions this is relative to - i.e. City of Cleveland). Instead, we could attempt to make use of data given in Q4.6: 70% of cities report current-year emissions inventories in Q4.0. However, the basis on which the targets and emissions inventories sit is particularly complex (see sub-questions within Q4.6 and variability in inventory data collection methodologies) and would need to be comparable for us to plot; this was considered too in-depth given the available time.\n\nAs such, we simplify the plot to be relative to the target in the current year (2020). Where no such target exists, we take the linear interpolation between the most recent base year emissions and next target year emissions. This assumes that the city has been decarbonising in line with its latest target up to 2020, evidently falsely benefitting cities who are behind their target (assuming a linear trajectory) and hiding over-achievement of cities who are ahead of their target."},{"metadata":{"trusted":true},"cell_type":"code","source":"from bisect import bisect_left, bisect_right\n\ndef linear_interpolation_from_trajectory(trajectory: List[Tuple[int, int]], \n interp_year: int = 2020) -> int:\n \"\"\"\n Provides linear interpolation of targets for year 'interp_year' (no extrapolation).\n - Returns latest target emissions if interpolation_year greater than max target year.\n - Returns None if interpolation_year is less than min target year (a base year).\n \"\"\"\n l = [(int(t[0]), float(t[1])) for t in trajectory] # convert str->int\n l = sort_list_of_tuples_by_first_element(l, False)\n years = [t[0] for t in l]\n # If interp_year exists as a base/target, return it\n i_year_tuple = [t for t in l if t[0] == interp_year]\n if len(i_year_tuple) > 0:\n return min(i_year_tuple, key=lambda t: t[1])[1]\n elif years[0] > interp_year:\n return None\n elif years[-1] < interp_year:\n return l[-1][1]\n t_lower = bisect_right(years, interp_year)\n ts = l[t_lower-1:t_lower+1]\n interp = (\n ts[1][1] + (ts[0][1] - ts[1][1]) * \n (ts[1][0] - interp_year) / (ts[1][0] - ts[0][0])\n )\n return interp\n\n\ndf_ambitions_T['current_year_emissions'] = df_ambitions_T['org_trajectory'].apply(\n lambda t: linear_interpolation_from_trajectory(t, CURRENT_YEAR)\n)\ndf_ambitions_T['current_year_emissions_per_capita'] = (\n df_ambitions_T['org_trajectory_per_capita'].apply(\n lambda t: linear_interpolation_from_trajectory(t, CURRENT_YEAR)\n )\n)\ndf_ambitions_T['current_year_emissions_type'] = 'interpolated'","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Add the current emissions interpolation into the trajectory and re-update the \n# relative trajectory values. This leaves the original trajectory string \n# (org_trajectory_rel_str) for the plot tooltips, excluding this ficticious base/\n# target year entry from the plot.\ndf_ambitions_T['org_trajectory'] = df_ambitions_T.apply(\n lambda r: r['org_trajectory'] + [\n (str(CURRENT_YEAR), str(r['current_year_emissions']))\n ], axis=1\n)\ndf_ambitions_T['org_trajectory_rel'] = (\n df_ambitions_T['org_trajectory'].apply(lambda t: get_relative_tuples(min, t))\n)\n\n# And for the per capita trajectory\ndf_ambitions_T['org_trajectory_per_capita'] = df_ambitions_T.apply(\n lambda r: r['org_trajectory_per_capita'] + [\n (str(CURRENT_YEAR), str(r['current_year_emissions_per_capita']))\n ], axis=1\n)\ndf_ambitions_T['org_trajectory_rel_per_capita'] = (\n df_ambitions_T['org_trajectory_per_capita'].apply(\n lambda t: get_relative_tuples(min, t)\n )\n)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"import collections\nimport numpy as np\nfrom shapely.geometry import MultiPoint, Polygon, LineString\nfrom shapely.affinity import scale\nfrom shapely.ops import split\n\n\ndef f(g, x, axis):\n \"\"\" \n Apply function g to iterable x considering element axis. \n \n Examples\n ------\n >>> x = {(2000, 100), (2015, 50), (2030, 25)}\n >>> f(min, x, 1) => (2030, 25)\n >>> f(max, x, 1) => (2000, 100)\n \"\"\"\n return g(x, key=lambda t: t[axis])[axis]\n\n\n\ndef create_polygon(d: List[Tuple[int, int]], base_year: int = 2020, max_year: int = 2050, \n extrapolation_strategy: str = 'constant') -> Polygon:\n \"\"\" \n Creates a shapely.geometry.polygon object from a list of coordinates.\n \n Parameters\n ------\n :param d: a list of tuples containing the year and associated quantity (i.e. tCO2e).\n :param base_year: left-hand cut-off for the polygon; years < base_year are clipped.\n :param max_year: right-hand cut-off for the polygon; years > max_year are clipped.\n :param extrapolation_strategy: method for extending d to max_year when the greatest\n year in d is less than the max_year. Options: 'constant', None.\n \n Examples\n ------\n >>> create_polygon([(1990, 1), (2010, 0.9), (2030, 0.5), (2050, 0)], 2000, 2050)\n \"\"\"\n # In case of multiple targets on the same date, take lowest emissions.\n # This is because, unless there are administrative errors, this situation should \n # only occur for target years (emissions for base/current years should be known \n # with certainty), and we hypothesize that lower targets would be employed only \n # in the case of exceeding an existing target; targets should not be increased \n # due to poor performance.\n _d = collections.defaultdict(lambda: float(np.inf))\n for year, emissions in d:\n _d[emissions] = min(_d[emissions], int(year))\n d = {(v,k) for k,v in _d.items()}\n\n # Extrapolate to end year\n if extrapolation_strategy == 'constant':\n d.add((max_year, f(min, d, 1)))\n\n # Create polygon, adding zero at start / end\n d = (\n [(f(min, d, 0), 0)] + \n sorted(list(d), key=lambda p: (p[0], p[1])) + [(f(max, d, 0), 0)]\n )\n mp_poly = Polygon(d)\n\n # Cut at base / max year if required\n min_year_line = LineString([(base_year, 0), (base_year, f(max, d, 1))])\n max_year_line = LineString([(max_year, 0), (max_year, f(max, d, 1))])\n mp_poly = split(mp_poly, max_year_line)[0]\n if len(list(split(mp_poly, min_year_line))) > 1:\n mp_poly = list(split(mp_poly, min_year_line))[1]\n\n # If missing base year value or its < 0, use y=1 for base_year\n base_year_y = [t[1] for t in d if t[0] == base_year]\n if len(base_year_y) < 1 or base_year_y[0] <= 0:\n base_year_y = 1.\n msg = 'Missing base_year={} entry, defaulting to base_year_emissions=1.0.'\n print(msg.format(base_year))\n else:\n base_year_y = base_year_y[0]\n \n mp_poly = scale(\n mp_poly, \n xfact=1/(max_year-base_year),\n yfact=1/(base_year_y - 0), # take y from 2020\n origin='center'\n )\n\n # print(f\"Area: {mp_poly.area:.3f}\")\n return mp_poly\n\n\ndf_ambitions_T['polygon'] = (\n df_ambitions_T['org_trajectory_rel_per_capita'].apply(create_polygon)\n)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Get resulting ambition metric\ndf_ambitions_T['area'] = df_ambitions_T['polygon'].apply(lambda x: x.area)\ndf_ambitions_T['ambition'] = 1 - df_ambitions_T['area']\ndf_ambitions_T[['organization', 'ambition']].sample(5)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Visualise emissions, ambition and KPI\n### Formatting, location and plot components\nThe following cells prepare the compiled data for plotting in an interactive Bokeh visualisation."},{"metadata":{"trusted":true},"cell_type":"code","source":"## Prepare data for visualisation\nimport ast\n\n# City locations data: see Code Appendix section below for preparation\ndf_city_locs = pd.read_csv(\n '../input/cdp-city-locations-with-latlong/city_locs_with_latlong.csv',\n index_col='Unnamed: 0', converters={'lat_long': lambda x: ast.literal_eval(x)}\n) # literal_eval loads as tuple\n\n_df = df_ambitions_T.merge(\n df_city_locs[['organization', 'lat_long']], on='organization', how='left'\n)\n\nmsg = 'Removed {} rows with missing lat-long information.'\nprint(msg.format(_df[_df['lat_long'].isnull()].shape[0]))\n_df = _df[~_df['lat_long'].isnull()]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def latlong_to_web_mercator(df):\n \"\"\" Converts decimal longitude/latitude to Web Mercator format. \"\"\"\n df[['lat', 'long']] = pd.DataFrame(df['lat_long'].tolist(), index=df.index)\n k = 6378137\n df['web_merc_x'] = df['long'] * (k * np.pi / 180.0)\n df['web_merc_y'] = np.log(np.tan((90 + df['lat']) * np.pi / 360.0)) * k\n return df\n\n_df = latlong_to_web_mercator(_df)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cols = ['organization', 3, 4, 5, 7, 11, 'org_trajectory_rel_str', \n 'org_trajectory_rel', 'org_trajectory_rel_per_capita', 'polygon', \n 'web_merc_x', 'web_merc_y', 'current_year_emissions', \n 'current_year_emissions_per_capita', 'current_year_emissions_type']\n_df = _df[cols].copy()\n\ncol_rename = {\n 'organization': 'city',\n 3: 'base_year',\n 4: 'target_introduced',\n 5: 'base_year_emissions_tco2e',\n # '_5': 'base_year_emissions_tco2e_per_capita',\n 10: 'paris_agreement_alignment',\n 11: 'linked_initiatives'\n}\n\n_df = (\n _df.sort_values(by=['organization', 3, 7])\n .drop_duplicates(subset=['organization'], keep='first')\n .fillna('')\n .drop(7, axis=1)\n .rename(columns=col_rename)\n)\n_df['current_year_emissions_mtco2e'] = _df['current_year_emissions'] / 1e6\n_df = _df.round({'current_year_emissions_mtco2e': 2})","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Remove polygon area outliers (i.e. Orlando, Yaounde) for colour normalisation when plotting\n# Take 95% using z-score\n_df['area'] = _df['polygon'].apply(lambda x: x.area)\n_df['z_score'] = zscore(_df['area'])\narea_max = 2 * np.std(_df['area']) + np.mean(_df['area'])\narea_min = -2 * np.std(_df['area']) + np.mean(_df['area'])\n_df['area_trimmed'] = _df.apply(\n lambda r: r['area'] if r['z_score'] < 2 else area_max, axis=1\n)\n_df['area_trimmed'] = _df.apply(\n lambda r: r['area_trimmed'] if r['z_score'] > -2 else area_min, axis=1\n)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"_df['area'].hist(bins=50)\n_df['area_trimmed'].hist(bins=50)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Ambition colour\nimport matplotlib\n\na = list(_df['area_trimmed']) + [1.1] # Viridis yellow can be hard to see => shift\n_df['colour'] = [\n \"#%02x%02x%02x\" % (int(r), int(g), int(b)) for r, g, b, _ \n in 255 * matplotlib.cm.viridis(matplotlib.colors.Normalize()(a)[:-1])\n]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Create tooltip text and image\nimport cairosvg\nimport base64\n\ndef svg_to_b64_png(svgs):\n \"\"\" Encode SVG to base64 PNG. \"\"\"\n urls = []\n for svg in svgs:\n png = cairosvg.svg2png(svg)\n url = 'data:image/png;base64,' + base64.b64encode(png).decode('utf-8')\n urls.append(url)\n return urls\n\n\n_df['polygon_image'] = _df['polygon'].apply(lambda p: p._repr_svg_())\n_df['polygon_area'] = _df['polygon'].apply(lambda p: p.area)\n_df.drop(columns=['polygon', 'linked_initiatives'], axis=1, inplace=True) # not JSON compliant\n_df['image_files'] = svg_to_b64_png(_df['polygon_image'])\n_df['_org_trajectory_rel'] = _df['org_trajectory_rel_str']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Save plot data for display notebook\n_df.to_csv('ambition_bokeh_plot_data.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"from bokeh.models import *\nfrom bokeh.plotting import *\nfrom bokeh.io import *\nfrom bokeh.tile_providers import *\nfrom bokeh.palettes import *\nfrom bokeh.transform import *\nfrom bokeh.layouts import *\n\nscale = 2000\nplot_height = 600\nx = _df['web_merc_x']\ny = _df['web_merc_y']\n\n# Centre map on London (51.5074, 0.1278)\nx_min=int(14226.630 - (scale ** 2))\nx_max=int(14226.630 + (scale ** 2))\ny_min=int(6711542.475 - (scale ** 2))\ny_max=int(6711542.475 + (scale ** 2))\n\ntile_provider = get_provider(CARTODBPOSITRON)\n\nplot = figure(\n title='City emissions trajectories (2020-2050)',\n match_aspect=True,\n tools='wheel_zoom,pan,reset,save',\n x_range=(x_min, x_max),\n y_range=(y_min, y_max),\n x_axis_type='mercator',\n y_axis_type='mercator',\n height=plot_height,\n width=750\n)\n\nplot.grid.visible = True\nmap = plot.add_tile(tile_provider)\nmap.level = 'underlay'\nplot.xaxis.visible = False\nplot.yaxis.visible = False\nplot.title.text_font_size='20px'\n\noutput_notebook()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def bubble_map(plot, df, radius_col, scale, leg_label):\n \"\"\" Add bubble map to existing Bokeh plot using \"\"\"\n df['radius'] = [int(float(i)) * scale for i in df[radius_col]]\n source = ColumnDataSource(df)\n c = plot.circle(\n x='web_merc_x', y='web_merc_y', color='colour', source=source, size=1, \n fill_alpha=0.4, radius='radius', legend_label=leg_label, hover_color='red'\n )\n\n circle_hover = HoverTool(\n mode='mouse', point_policy='follow_mouse', renderers=[c],\n tooltips=\"\"\"\n @city
\n Current: @current_year_emissions_mtco2e MtCO2e (@current_year_emissions_type)
\n              \n @current_year_emissions_per_capita tCO2e per capita
\n Targets:
\n
\n
\n @_org_trajectory_rel{safe}
\n
\n
\n 2020-2050 target reduction profile (per capita):
\n Area: @polygon_area
\n
\n
\n \n
\n
\n \"\"\"\n )\n circle_hover.renderers.append(c)\n plot.tools.append(circle_hover)\n plot.legend.location = 'top_right'\n plot.legend.click_policy = 'hide'\n\n\n\nbubble_map(\n plot=plot,\n df=_df, \n radius_col='current_year_emissions', \n leg_label='Cities', \n scale=0.01\n)\n\n# Create colour bar legend with custom title\ncolor_bar = ColorBar(\n color_mapper=LogColorMapper(palette=\"Viridis256\", low=0, high=1), ticker=LogTicker(), \n label_standoff=12, border_line_color=None, location=(0,0)\n)\ncolor_bar_title = '<<< less ambitous more ambitious >>>'\ncolor_bar_plot = figure(\n title=color_bar_title, title_location='right', height=plot_height, width=100, \n toolbar_location=None, min_border=0, outline_line_color=None\n)\n\ncolor_bar_plot.add_layout(color_bar, 'right')\ncolor_bar_plot.title.align='center'\ncolor_bar_plot.title.text_font_size = '9pt'\n\nlayout = row(plot, color_bar_plot)\n\nshow(layout)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Benefits\nThis section is sorted by co-benefit:\n* Health\n* Employment\n* Congestion\n* Inclusion\n* NLP\n\nSome metrics use an NLP model to label whether a sequence of free text discusses the implementation of, or an action related to, one of these co-benefits. These are computed separately from their respective co-benefit.\n\n## Health\n### Life expectancy at birth"},{"metadata":{"trusted":true},"cell_type":"code","source":"# Dataframe which will compile our results\ndf_b = get_city_with_county_and_lat_long()\n\n# Merge ambition metric for the 85 US cities we have sufficient data for\ndf_ambition = df_ambitions_T[['organization', 'ambition']].drop_duplicates()\ndf_b = df_b.merge(df_ambition, on='organization', how='left')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"## Helper functions\n# Helpful renaming dict for joining dataframes\norg_country_rename_dict = {'Organization': 'organization', 'Country': 'country'}\n\n# Some helper functions\nqna = 'Question not applicable'\n\ndef normalize_column(df: pd.DataFrame, col: str = None) -> pd.Series:\n \"\"\" Normalizes values in a given column of df. \"\"\"\n _df = df.copy()\n _df[col] = _df[col].astype(float)\n max_value = _df[col].max()\n min_value = _df[col].min()\n if min_value == max_value:\n _df[col] = _df[col].isna().replace(True, np.nan).astype('float') + 1\n else:\n _df[col] = (_df[col] - min_value) / (max_value - min_value)\n return _df[col]\n\n\ndef get_ordered_repeating_values(df, order_col, return_col=None):\n \"\"\" \n Given a dataframe of the CDP questionnaire format, return the dataframe ordered \n by Organization and 'order_col' and an ordered list of the unique values in \n 'return_col'. This is useful when there are multiple columns or rows per question \n and you're transposing to get columns as rows (for renaming columns).\n \"\"\"\n return_col = return_col if return_col else order_col\n _df = df.copy()\n _df = _df.sort_values(by=['organization', order_col])\n org_sample = _df.iloc[1, :].loc['organization'] \n\n return_col_unique_values = (\n _df[(_df['organization'] == org_sample)]\n .groupby('organization')[return_col]\n .apply(list)[org_sample]\n )\n return _df, return_col_unique_values\n\n\ndef get_bounds_using_iqr(df: pd.DataFrame, col: str) -> Tuple[float, float]:\n \"\"\" Return upper and lower bounds based on 1.5 * interquartile range. \"\"\"\n _df = df.copy()\n _df = _df.loc[~pd.isnull(_df[col]), col].astype(float)\n upper_quantile, lower_quantile = np.percentile(_df, [75, 25])\n iqr = upper_quantile - lower_quantile\n lower_bound = lower_quantile - 1.5 * iqr\n upper_bound = upper_quantile + 1.5 * iqr\n return float(lower_bound), float(upper_bound)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"import pandas as pd\nimport ast\n\nfpath = '../input/us-life-expectancy-at-birth-20102015/US_life_expectancy_at_birth_by_state_and_census_tract_2010_2015.csv'\ndf_life_exp = pd.read_csv(fpath)\n\ndf_us_city_locs = get_city_with_county_and_lat_long()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_life_exp['county_clean'] = (\n df_life_exp['County'].apply(lambda s: s.split(',')[0].replace('County', '').strip())\n)\ndf_life_exp = (\n df_life_exp.groupby('county_clean')\n .agg({'Life Expectancy': 'median'})\n .reset_index()\n .rename(columns={\n 'Life Expectancy': 'life_expectancy',\n 'county_clean': 'county_name'\n })\n)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_us_city_locs = df_us_city_locs.merge(\n df_life_exp[['county_name', 'life_expectancy']],\n on='county_name',\n how='left'\n)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Get state median life expectancy\nmedian_life_expectancy_by_state = (\n df_us_city_locs.groupby('state_id')['life_expectancy'].median()\n)\n\n# Fill states with no life expectancy data with US state median\nmedian_life_expectancy_by_state = (\n median_life_expectancy_by_state.fillna(median_life_expectancy_by_state.median())\n .to_dict()\n)\ndf_us_city_locs['life_expectancy_state'] = (\n df_us_city_locs['state_id'].map(median_life_expectancy_by_state)\n)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"_n = pd.isnull(df_us_city_locs['life_expectancy'])\na = df_us_city_locs.loc[_n, 'county_name'].shape[0]\ndf_us_city_locs['life_expectancy'].fillna(\n df_us_city_locs['life_expectancy_state'], \n inplace=True\n)\nb = df_us_city_locs.loc[_n, 'county_name'].shape[0]\nc = df_us_city_locs.shape[0]\n\nmsg = 'Filled missing values for life expectancy in {} counties ({:.1f}%).'\nprint(msg.format(a-b, (a-b)/c*100))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_life_exp = df_us_city_locs.copy()\ndf_life_exp['life_expectancy_rel'] = normalize_column(df_life_exp, 'life_expectancy')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Merge to results dataframe\ndf_b = df_b.merge(\n df_life_exp[['organization', 'life_expectancy', 'life_expectancy_rel']], \n on='organization',\n how='left'\n)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Air quality in cities\nPollution level for the most recent year available for PM2.5, PM10, NO2, O3, SO2. Source: (Q10.14)"},{"metadata":{"trusted":true},"cell_type":"code","source":"cols = [\n 'Most recent years available (select year)',\n 'Average concentration for most recent year available (ug/m3)'\n]\ndf_aq = df_cities[\n (df_cities['Question Number'] == '10.14') &\n (df_cities['Column Name'].isin(cols)) &\n (df_cities['Country'] == 'United States of America')\n].rename(columns={'Organization': 'organization'})\n\ndf_aq['Response Answer'] = df_aq['Response Answer'].replace(qna, np.nan)\ndf_aq, cols = get_ordered_repeating_values(df_aq, 'Row Number', 'Row Name')\n\n\n# Rename columns after transpose\n# Tailor for year for this question (questions for year are the same) and we don't \n# want to overwrite them in the dict; add '_year' flag.\nrename_dict_root = {\n 'NO2 (1 year (annual) mean)': 'q10.14_no2_annual_mean_rel',\n 'O3 (Daily maximum 8 hour mean)': 'q10.14_o3_daily_max_8hr_mean_rel',\n 'PM10 (1 year (annual) mean)': 'q10.14_pm10_annual_mean_rel',\n 'PM10 (Maximum 24-hour average)': 'q10.14_pm10_24hr_mean_rel',\n 'PM2.5 (1 year (annual) mean)': 'q10.14_pm2.5_annual_mean_rel',\n 'PM2.5 (Maximum 24-hour average)': 'q10.14_pm2.5_max_24hr_mean_rel',\n 'SO2 (Maximum 24-hour average)': 'q10.14_so2_max_24hr_mean_rel'\n}\ncols_with_year = []\nfor c in cols:\n for existing, new in rename_dict_root.items():\n c = c.replace(existing, new)\n if c + '_year' not in cols_with_year:\n cols_with_year.append(c + '_year')\n else:\n cols_with_year.append(c)\nordered_questions_dict = {i: c for i, c in enumerate(cols_with_year)}\nordered_questions_dict\n\n# Transpose relevant rows to columns\ndf_aq = (\n df_aq.sort_values(by=['Column Number', 'Row Number'])\n .groupby(['organization'])['Response Answer']\n .apply(lambda df: df.reset_index(drop=True))\n .unstack()\n .reset_index()\n .rename(columns=ordered_questions_dict)\n)\n\nfor c in rename_dict_root.values():\n df_aq[c] = 1 - normalize_column(df_aq, c)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Merge to results dataframe\ndf_b = df_b.merge(df_aq, on='organization', how='left')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Green areas\nArea of parks per capita. Source: Q11 and Q0.5 of city questionnaire."},{"metadata":{"trusted":true},"cell_type":"code","source":"cols = ['Organization', 'Country', 'Response Answer']\ndf_green = df_cities.loc[\n (df_cities['Question Number'] == '11.0') &\n (~df_cities['Response Answer'].isin([qna])) &\n (df_cities['Country'] == 'United States of America')\n].loc[:, cols]\n\n# Remove effect of outliers using interquartile range\n# (i.e. City of Lagos, City of Cape Town)\n_, ub = get_bounds_using_iqr(df_green, 'Response Answer')\ndf_green.loc[df_green['Response Answer'].astype(float) >= ub] = ub\n\n# Make per capita\npopulation = _df_pop[['Organization', 'latest_year_pop', 'latest_year']].copy()\ndf_green = df_green.merge(population, on='Organization', how='left')\ndf_green['park_space_sq_km_per_capita'] = (\n df_green['Response Answer'].astype(float) / df_green['latest_year_pop'] \n)\ndf_green['park_space_sq_km_per_capita_rel'] = normalize_column(\n df_green, 'park_space_sq_km_per_capita'\n)\ndf_green['park_space_sq_km_per_capita_rel'].fillna(0, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Merge to results dataframe\ndf_green.rename(columns={'Organization': 'organization'}, inplace=True)\ncols = ['organization', 'park_space_sq_km_per_capita_rel']\ndf_b = df_b.merge(df_green[cols], on='organization', how='left')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Access to potable water\nShare of total population having access to potable water (Q14.1):"},{"metadata":{"trusted":true},"cell_type":"code","source":"cols = ['Organization', 'Country', 'Response Answer']\ndf_water = df_cities.loc[\n (df_cities['Question Number'] == '14.1') &\n (df_cities['Country'] == 'United States of America'), \n cols\n]\n# Replace zero with NaN\ndf_water['Response Answer'] = (\n df_water['Response Answer'].astype(float)\n .mask(df_water['Response Answer'] == 0)\n)\ndf_water['potable_water_rel'] = normalize_column(df_water, 'Response Answer')\ndf_water['potable_water_rel'] = df_water['potable_water_rel'].fillna(0)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Merge to results dataframe\ndf_water.rename(columns={'Organization': 'organization'}, inplace=True)\ncols = ['organization', 'potable_water_rel']\ndf_b = df_b.merge(df_water[cols], on='organization', how='left')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Food security"},{"metadata":{"trusted":true},"cell_type":"code","source":"# Q12.6: Percentage of population that is food insecure\ncols = ['Organization', 'Country', 'Response Answer']\ndf_food = df_cities.loc[\n (df_cities['Question Number'] == '12.6') &\n (df_cities['Column Number'] == 1) &\n (df_cities['Country'] == 'United States of America') &\n (~df_cities['Response Answer'].isin([qna, np.nan])),\n cols\n]\ndf_food['q12.6'] = 1 - normalize_column(df_food, 'Response Answer')\ndf_food = df_food.drop('Response Answer', axis=1)\n\n# Merge to results dataframe\ndf_food.rename(columns=org_country_rename_dict, inplace=True)\ndf_b = df_b.merge(df_food, on=['organization', 'country'], how='left')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Supplementary\nExtracting data from other questions involved in the KPI:"},{"metadata":{"trusted":true},"cell_type":"code","source":"# Q2.0b: Is public health listed as one of the areas/sectors covered by the risk and \n# vulnerability assessment (Column_Number=8)?\ncols = ['Organization', 'Country', 'Response Answer']\n_df = df_cities.loc[\n (df_cities['Question Number'] == '2.0b') &\n (df_cities['Column Number'] == 8) &\n (df_cities['Country'] == 'United States of America'),\n cols\n]\n\n_df['Response Answer'] = _df['Response Answer'].str.lower().fillna('')\n_df['Response Answer'] = _df['Response Answer'].str.contains('health').astype('int')\n_df['Response Answer'] = _df['Response Answer'].astype(float)\n_df = _df.groupby(['Organization', 'Country']).max().reset_index()\n_df = _df.rename(columns={'Response Answer': 'q2.0b'})\n\n# Merge to results dataframe\n_df.rename(columns=org_country_rename_dict, inplace=True)\ndf_b = df_b.merge(_df, on=['organization', 'country'], how='left')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Q2.2: Factors that affect ability to adapt: is public health included in the assessment?\ncols = ['Organization', 'Country', 'Response Answer']\n_df = df_cities.loc[\n (df_cities['Question Number'] == '2.2') &\n (df_cities['Column Number'] == 4) &\n (df_cities['Country'] == 'United States of America'),\n cols\n]\n\n# 1 if not empty\n_df['Response Answer'] = -pd.isnull(_df['Response Answer']).astype(int) + 1\n_df = _df.groupby(['Organization', 'Country']).max().reset_index()\n_df = _df.rename(columns={'Response Answer': 'q2.2'})\n\n# Merge to results dataframe\n_df.rename(columns=org_country_rename_dict, inplace=True)\ndf_b = df_b.merge(_df, on=['organization', 'country'], how='left')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Q3.0: Do any of the actions include public health as a co-benefit?\ncols = ['Organization', 'Country', 'Response Answer']\nanswers = ['Improved resource quality (e.g. air, water)', 'Improved public health']\n_df = df_cities.loc[\n (df_cities['Question Number'] == '3.0') &\n (df_cities['Column Number'] == 6) &\n (df_cities['Country'] == 'United States of America') &\n (df_cities['Response Answer'].isin(answers)),\n cols\n]\n_df = _df.groupby(['Organization', 'Country']).count().reset_index()\n_df['Response Answer'] = _df['Response Answer'].apply(lambda c: c > 0).astype(int)\n_df = _df.rename(columns={'Response Answer': 'q3.0_health'})\n\n# Merge to results dataframe\n_df.rename(columns=org_country_rename_dict, inplace=True)\ndf_b = df_b.merge(_df, on=['organization', 'country'], how='left')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Q10.7: Do you have a low or zero-emission zone in your city?\ncols = ['Organization', 'Country', 'Response Answer']\n_df = df_cities.loc[\n (df_cities['Question Number'] == '10.7') &\n (df_cities['Country'] == 'United States of America') &\n (df_cities['Response Answer'] == 'Yes'),\n cols\n]\n_df = _df.groupby(['Organization', 'Country']).count().reset_index()\n_df = _df.rename(columns={'Response Answer': 'q10.7'})\n\n# Merge to results dataframe\n_df.rename(columns=org_country_rename_dict, inplace=True)\ndf_b = df_b.merge(_df, on=['organization', 'country'], how='left')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Q10.9: How many public access EV charging points do you have in your city and/or \n# metropolitan area for the following types. (Met. area => Column Number = 2)\ncols = ['Organization', 'Country', 'Response Answer']\n_df = df_cities.loc[\n (df_cities['Question Number'] == '10.9') &\n (df_cities['Column Number'] == 2) &\n (df_cities['Row Name'] == 'All types') &\n (df_cities['Country'] == 'United States of America') &\n (~df_cities['Response Answer'].isin([qna, np.nan])),\n cols\n]\n\n# Make per capita\npopulation = _df_pop[['Organization', 'latest_year_pop', 'latest_year']].copy()\n_df = _df.merge(population, on='Organization', how='left')\n_df['ev_charging_points_per_capita'] = (\n _df['Response Answer'].astype(float) / _df['latest_year_pop'] \n)\n_df['ev_charging_points_per_capita_rel'] = normalize_column(\n _df, 'ev_charging_points_per_capita'\n)\n\n# Merge to results dataframe\ncols = ['organization', 'country', 'ev_charging_points_per_capita_rel']\n_df.rename(columns=org_country_rename_dict, inplace=True)\ndf_b = df_b.merge(_df[cols], on=['organization', 'country'], how='left')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Q14.2: Please select the actions you are taking to reduce the risks to your city’s \n# water security\n# - If the answer is yes and Q14.3 includes an adaptation action – full score\n# - If the answer is yes and no adaptation action included in Q14.3 – zero score.\n\ncols = ['Organization', 'Country', 'Question Number', 'Column Name', 'Response Answer']\n_df = df_cities.loc[\n (df_cities['Question Number'].isin(['14.2', '14.3'])) &\n (df_cities['Column Name'].isin([np.nan, 'Adaptation action'])) &\n (df_cities['Country'] == 'United States of America') &\n (~df_cities['Response Answer'].isin([qna, np.nan])),\n cols\n] \n\n# Find orgs with answers to both 14.2, 14.3 where 14.3 includes 'Adaptation action'\nadaptation_action_incl = (\n _df.groupby('Organization')['Column Name']\n .apply(lambda r: 'Adaptation action' in list(r))\n .astype(int)\n .rename('q14.2')\n)\n\n_df = (\n _df.merge(adaptation_action_incl, on='Organization', how='left')\n .drop_duplicates(subset=['Organization'])\n)\n_df = _df[['Organization', 'Country', 'q14.2']]\n\n# Merge to results dataframe\n_df.rename(columns=org_country_rename_dict, inplace=True)\ndf_b = df_b.merge(_df, on=['organization', 'country'], how='left')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Q12.4: How does your city increase access to sustainable foods?\ncols = ['Organization', 'Country', 'Response Answer']\n_df = df_cities.loc[\n (df_cities['Question Number'] == '12.4') &\n (df_cities['Column Number'] == 1) &\n (df_cities['Country'] == 'United States of America'),\n cols\n]\n\n_df = _df.groupby(['Organization', 'Country'])['Response Answer'].apply(list).reset_index()\nmapping = {'No': 0., 'Yes': 0.25, np.nan: 0, 'Do not know': 0}\n_df['q12.4'] = _df['Response Answer'].apply(lambda l: sum(mapping.get(_l) for _l in l))\n_df = _df[['Organization', 'Country', 'q12.4']]\n\n# Merge to results dataframe\n_df.rename(columns=org_country_rename_dict, inplace=True)\n_df['q12.4'] = normalize_column(_df, 'q12.4')\ndf_b = df_b.merge(_df, on=['organization', 'country'], how='left')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Employment\n### Unemployment rate"},{"metadata":{"trusted":true},"cell_type":"code","source":"fpath = '../input/cdp-unlocking-climate-solutions/Supplementary Data/CDC Social Vulnerability Index 2018/SVI2018_US_COUNTY.csv'\ncols = {'ST_ABBR': 'state_id', 'COUNTY': 'county_name', 'MP_UNEMP': 'unemp_rate'}\ndf_emp = pd.read_csv(fpath, usecols=cols.keys())\ndf_emp = df_emp.rename(columns=cols)\ndf_emp = df_emp.loc[df_emp['unemp_rate'] >= 0]\n\n# Remove effect of outliers using interquartile range\n# (i.e. Loving, TX: 54.8% unemployment)\n_, ub = get_bounds_using_iqr(df_emp, 'unemp_rate')\ndf_emp.loc[df_emp['unemp_rate'].astype(float) >= ub] = ub\n\n# Normalize after merge as cities exist in emp dataset which don't in results df\ncols = ['county_name', 'state_id']\ndf_b = df_b.merge(df_emp[cols + ['unemp_rate']], on=cols, how='left')\ndf_b['emp_rate_rel'] = 1 - normalize_column(df_b, 'unemp_rate')\ndf_b = df_b.drop('unemp_rate', axis=1)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Share of green jobs\nShare of green jobs in the city (Q6.15):"},{"metadata":{"trusted":true},"cell_type":"code","source":"cols = ['Organization', 'Country', 'Response Answer']\ndf_gjobs = df_cities.loc[\n (df_cities['Question Number'] == '6.15') &\n (df_cities['Column Number'] == 1) &\n (~df_cities['Response Answer'].isin([qna])) &\n (df_cities['Country'] == 'United States of America')\n].loc[:, cols]\n\n# Make per capita\npopulation = _df_pop[['Organization', 'latest_year_pop', 'latest_year']].copy()\ndf_gjobs = df_gjobs.merge(population, on='Organization', how='left')\ndf_gjobs['green_jobs_per_capita'] = (\n df_gjobs['Response Answer'].astype(float) / df_gjobs['latest_year_pop'] \n)\ndf_gjobs['green_jobs_per_capita_rel'] = normalize_column(\n df_gjobs, 'green_jobs_per_capita'\n)\ndf_gjobs['green_jobs_per_capita_rel'].fillna(0, inplace=True)\n\ndf_gjobs = df_gjobs[~pd.isnull(df_gjobs['green_jobs_per_capita'])]\n\n# Merge to results dataframe\ncols = ['organization', 'country', 'green_jobs_per_capita_rel']\ndf_gjobs.rename(columns=org_country_rename_dict, inplace=True)\ndf_b = df_b.merge(df_gjobs[cols], on=['organization', 'country'], how='left')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Congestion\n### Average commute time\nAverage commute time is taken from the US Census Bureau in the format 'count of occurances in bin per county'. We assume the unbounded upper bin has a median of 65 minutes, i.e. treating it as a bin of equal width as the lower unbounded bin (for which we take x < 10 => x=5). The median is calculated using the following formula:\n\n
median = 𝐿+𝑤(𝑛/2−𝑐)/𝑓
\n\nwhere: \n* 𝐿 is the lower limit of the bin containing the median\n* 𝑤 is the width of that bin \n* 𝑛 is the total population\n* 𝑐 is the cumulative count (cumulative frequency) up to 𝐿 (the end of the previous bin)\n* 𝑓 is the count (frequency)"},{"metadata":{"trusted":true},"cell_type":"code","source":"fpath = '../input/average-commute-time-by-city-us-census-bureau/ACSDT1Y2019.B08135_data_with_overlays_2020-11-29T095052.csv'\ncols = pd.read_csv(fpath, header=1, nrows=1).columns.tolist()\ncols = [c for c in cols if not 'Margin of Error!' in c]\ndf_ct = pd.read_csv(fpath, header=1, usecols=cols)\n\nprepending = 'Estimate!!Aggregate travel time to work (in minutes):!!'\ndf_ct = df_ct.rename(columns={\n c: c.replace(prepending, '').replace(' ', '_').lower() for c in df_ct.columns\n})\ndf_ct = df_ct.rename(columns={\n 'estimate!!aggregate_travel_time_to_work_(in_minutes):': 'sum_of_travel_time'\n})\n\nBin = collections.namedtuple('Bin', ['median', 'width'])\nbins_map = {\n 'less_than_10_minutes': Bin(5, 10),\n '10_to_14_minutes': Bin(12.5, 5),\n '15_to_19_minutes': Bin(17.5, 5),\n '20_to_24_minutes': Bin(22.5, 5),\n '25_to_29_minutes': Bin(27.5, 5),\n '30_to_34_minutes': Bin(32.5, 5),\n '35_to_44_minutes': Bin(40, 10),\n '45_to_59_minutes': Bin(52.5, 15),\n '60_or_more_minutes': Bin(65, 10)\n}","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def get_median_from_bins(r: pd.Series, bin_cols: List[str], bins_map: Dict[str, Bin]):\n \"\"\"\n Gets median value from dataframe with bin index and bin value, given bin widths \n and center value described in bin_map.\n \"\"\"\n # Convert to dataframe, get bin width and median value\n r = r[bin_cols]\n r.name = 'count'\n df_r = r.to_frame()\n df_r['median'] = [bins_map[i].median for i in df_r.index]\n df_r['width'] = [bins_map[i].width for i in df_r.index]\n \n # Calculate median bin\n df_r['sum'] = df_r['median'] * df_r['count']\n threshold = df_r['sum'].sum() / 2 \n df_r['cumulative_sum'] = df_r['sum'].cumsum()\n median_bin_iloc = df_r['cumulative_sum'].searchsorted(threshold)\n median_bin = df_r.iloc[median_bin_iloc].name\n \n # Calculate median\n L = df_r.loc[median_bin, 'median'] - df_r.loc[median_bin, 'width'] / 2\n w = df_r.loc[median_bin, 'width']\n n = df_r['sum'].sum()\n c = df_r.loc[median_bin, 'cumulative_sum'] - df_r.loc[median_bin, 'sum']\n f = df_r.loc[median_bin, 'sum']\n return L + w * (n / 2 - c) / f\n \n\nbin_cols = [\n 'less_than_10_minutes', \n '10_to_14_minutes', \n '15_to_19_minutes', \n '20_to_24_minutes', \n '25_to_29_minutes', \n '30_to_34_minutes', \n '35_to_44_minutes', \n '45_to_59_minutes', \n '60_or_more_minutes'\n]\ndf_ct['av_commute_mins'] = df_ct.apply(\n lambda r: get_median_from_bins(r, bin_cols, bins_map), axis=1\n)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Merge with US city locations information\ncols = ['geographic_area_name', 'av_commute_mins']\ndf_ct = df_ct[cols]\ndf_ct['county_name'] = df_ct['geographic_area_name'].apply(\n lambda s: s.split(',')[0].replace('County', '').strip().lower()\n)\ndf_ct['state_name'] = df_ct['geographic_area_name'].apply(\n lambda s: s.split(',')[1].strip().lower()\n)\n\n# Merge with results dataframe\ndf_b['state_name'] = df_b['state_name'].str.lower()\ndf_b['county_name'] = df_b['county_name'].str.lower()\ndf_b = df_b.merge(\n df_ct[['county_name', 'state_name', 'av_commute_mins']], \n on=['county_name', 'state_name'], \n how='left'\n)\n\n# Fill missing counties with state average and make relative\na = df_b['av_commute_mins'].isna().sum()\ndf_b['av_commute_mins'] = (\n df_b.groupby('state_id')['av_commute_mins']\n .transform(lambda r: r.fillna(np.median(r)))\n)\nb = df_b['av_commute_mins'].isna().sum()\n\nus_median = df_b['av_commute_mins'].median()\ndf_b['av_commute_mins'] = df_b['av_commute_mins'].fillna(us_median)\ndf_b['av_commute_mins_rel'] = 1 - normalize_column(df_b, 'av_commute_mins')\n\nc = df_b['av_commute_mins_rel'].isna().sum()\nprint(f\"Filled {a-b} county records with the state average commute time.\")\nprint(f\"Filled {b-c} county records with the US average commute time: \" +\n f\"{us_median:.1f}.\")","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Share of transportation \nExcluding private transport/taxis (Q10.1):"},{"metadata":{"trusted":true},"cell_type":"code","source":"df_transp = df_cities.loc[\n (df_cities['Question Number'] == '10.1') &\n (df_cities['Country'] == 'United States of America')\n].rename(columns={'Organization': 'organization'})\n\ndf_transp['Response Answer'] = df_transp['Response Answer'].replace(qna, np.nan)\n\n# Transpose relevant rows to columns\ndf_transp, cols = get_ordered_repeating_values(df_transp, 'Column Name')\ndf_transp = (\n df_transp.sort_values(by=['Column Number'])\n .groupby(['organization'])['Response Answer']\n .apply(lambda df: df.reset_index(drop=True))\n .unstack()\n .reset_index()\n .rename(columns={i: c for i, c in enumerate(cols)})\n)\n\n# Scale sum down to 100, but not up to 100\ndf_transp['sum'] = df_transp[cols].astype(float).sum(axis=1, min_count=1)\nmsg = 'Transport split completed for {}/{} ({:.1f}%) records.'\na = df_transp[df_transp['sum'] >= 100].shape[0]\nb = df_transp.shape[0]\nprint(msg.format(a, b, a/b*100))\n\ndef scale_to_100(r: pd.Series, cols: List[str]) -> pd.Series:\n for c in cols:\n r[c] = float(r[c]) * 100 / r['sum']\n r['sum'] = 100.\n return r\n\ndf_transp.loc[df_transp['sum'] > 100] = (\n df_transp.loc[df_transp['sum'] > 100]\n .apply(lambda r: scale_to_100(r, cols), axis=1)\n)\n\n# Get percentage which is not private car / taxi\nundesired_cols = ['Private motorized transport', 'Taxis or For Hire Vehicles']\ncols = [c for c in cols if c not in undesired_cols]\ndf_transp['sum_public_tr'] = df_transp[cols].astype(float).sum(axis=1, min_count=1)\ndf_transp['public_tr_rel'] = normalize_column(df_transp, 'sum_public_tr').fillna(0)\ndf_transp = df_transp[['organization', 'public_tr_rel']]\n\n# Merge to results dataframe\ndf_b = df_b.merge(df_transp, on='organization', how='left')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Supplementary\nExtracting data form other questions involved in the KPI:"},{"metadata":{"trusted":true},"cell_type":"code","source":"# Q2.1: Is transport identified as areas/sectors covered by the risk and \n# vulnerability assessment?\ncols = ['Organization', 'Country', 'Response Answer']\n_df = df_cities.loc[\n (df_cities['Question Number'] == '2.1') &\n (df_cities['Column Number'] == 6) &\n (df_cities['Country'] == 'United States of America') &\n (df_cities['Response Answer'] == 'Transport'),\n cols\n]\n_df = _df.drop_duplicates().rename(columns={'Response Answer': 'q2.1'})\n_df['q2.1'] = 1\n\n# Merge to results dataframe\n_df.rename(columns=org_country_rename_dict, inplace=True)\ndf_b = df_b.merge(_df, on=['organization', 'country'], how='left')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Q5.4: Describe the anticipated outcomes of the most impactful mitigation actions your \n# city is currently undertaking; the total cost of the action and how much is being \n# funded by the local government. If one or more mitigation action related to “Mass \n# Transit” is/are included – full score\ncols = ['Organization', 'Country', 'Response Answer']\n_df = df_cities.loc[\n (df_cities['Question Number'] == '5.4') &\n (df_cities['Column Number'] == 1) &\n (df_cities['Country'] == 'United States of America') &\n (df_cities['Response Answer'].str.contains('Mass Transit')),\n cols\n]\n_df['Response Answer'] = 1\n_df = _df.drop_duplicates().rename(columns={'Response Answer': 'q5.4'})\n\n# Merge to results dataframe\n_df.rename(columns=org_country_rename_dict, inplace=True)\ndf_b = df_b.merge(_df, on=['organization', 'country'], how='left')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Q5.5a: Please attach your city’s climate change mitigation plan below. If your city \n# has both mitigation and energy access plans, please make sure to attach all relevant \n# documents below.\n# - If area covered includes “Transport (Mobility)” - 1 (Column Number = 5)\n# - If not included – 0\ncols = ['Organization', 'Country', 'Response Answer']\n_df = df_cities.loc[\n (df_cities['Question Number'] == '5.5a') &\n (df_cities['Column Number'] == 5) &\n (df_cities['Country'] == 'United States of America') &\n (df_cities['Response Answer'] == 'Transport (Mobility)'),\n cols\n]\n_df['Response Answer'] = 1\n_df = _df.drop_duplicates().rename(columns={'Response Answer': 'q5.5a'})\n\n# Merge to results dataframe\n_df.rename(columns=org_country_rename_dict, inplace=True)\ndf_b = df_b.merge(_df, on=['organization', 'country'], how='left')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Inclusion"},{"metadata":{"trusted":true},"cell_type":"code","source":"fpath = '../input/urban-institute-racial-and-economic-indexes/urban_inclusion_indexes.csv'\ndf_inclusion = pd.read_csv(fpath)\ndf_inclusion['city'] = df_inclusion['city'].str.lower()\n\ncols = ['economic_inclusion_index_2016', 'racial_inclusion_index_2016']\n\n# Merge with results dataset (some cities in the inclusion dataset don't exist\n# in the CDP responses, hence safe to do normalization after merge)\ndf_inclusion.rename(columns={'state': 'state_id'}, inplace=True)\ndf_inclusion = df_inclusion[['city', 'state_id'] + cols]\ndf_b = df_b.merge(df_inclusion, on=['city', 'state_id'], how='left')\n\n# Noramlize\ncols_median = {}\nfor c in cols:\n df_b[c] = normalize_column(df_b, c)\n cols_median[c] = df_b[c].median()\n df_b[c] = df_b[c].fillna(cols_median[c])\n \na = set(df_inclusion['city'])\nb = set(df_us_city_locs['city'])\nmsg = (\n f\"Racial / economic inclusion index not available for {len(b - a)}/{len(b)} \" +\n f\"({len(b - a) / len(b) * 100:.1f}%) cities who reported to CDP. These will \" +\n f\"be filled with the median normalized values - \" +\n f\"racial: {cols_median['economic_inclusion_index_2016']:.3f} \" +\n f\"economic: {cols_median['racial_inclusion_index_2016']:.3f}.\"\n)\nprint(msg)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Supplementary\nExtracting data form other questions involved in the KPI:"},{"metadata":{"trusted":true},"cell_type":"code","source":"# Q3.0: Please describe the main actions you are taking to reduce the risk to, and \n# vulnerability of, your city’s infrastructure, services, citizens, and businesses \n# from climate change as identified in the Climate...\n# - At least one action (Column Number = 2) includes “Social inclusion, social \n# justice” as a co-benefit – 1\n# - Not included – 0\ncols = ['Organization', 'Country', 'Response Answer']\n_df = df_cities.loc[\n (df_cities['Question Number'] == '3.0') &\n (df_cities['Column Number'] == 6) &\n (df_cities['Country'] == 'United States of America') &\n (df_cities['Response Answer'] == 'Social inclusion, social justice'),\n cols\n]\n_df['Response Answer'] = 1\n_df = _df.drop_duplicates().rename(columns={'Response Answer': 'q3.0_inclusion'})\n\n# Merge to results dataframe\n_df.rename(columns=org_country_rename_dict, inplace=True)\ndf_b = df_b.merge(_df, on=['organization', 'country'], how='left')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Labelling city actions: BERT\nCode based on multi-label classifier implementation of HuggingFace library, here: https://colab.research.google.com/drive/1pTuQhug6Dhl9XalKB0zUGf4FIdYFlpcX\n\nSee the CDP:ABC-NLP project notebook for the model."},{"metadata":{"trusted":true},"cell_type":"code","source":"# Create a simple test dataframe to check; used in development only\n# This is completely separate from the training and validation data\n# This is Andrew Ng's \"blurry cat picture\" data :)\ndf_cheeky_test = pd.DataFrame({\n 'response_answer_translated': [\n 'We are considering the health of the public.',\n 'This will create jobs.',\n 'We will make sure to look at all the options.',\n 'Our hospitals have set aside additional capacity',\n 'Vulnerable people are catered for.',\n 'Employment opportunities have been created.',\n 'We are making sure vulnerable people have health care plans',\n 'We are providing additional funding for improving the transporting network',\n 'This will improve general health.',\n 'Making sure vulnerable groups are considered when planning health measures',\n 'Including traffic reduction schemes to improve health'\n ],\n 'label': [\n [0, 0, 1, 0],\n [1, 0, 0, 0],\n [0, 0, 0, 0],\n [0, 0, 1, 0],\n [0, 1, 0, 0],\n [1, 0, 0, 0],\n [0, 1, 1, 0],\n [0, 0, 0, 1],\n [1, 0, 0, 0],\n [0, 1, 1, 0],\n [0, 0, 1, 1]\n ]\n})","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Answering questions with the model"},{"metadata":{"trusted":true},"cell_type":"code","source":"import tensorflow as tf\nimport torch\nimport pandas as pd\n\n# Check GPU availability\ndevice_name = tf.test.gpu_device_name()\nif device_name == '/device:GPU:0':\n print('Found GPU at: {}'.format(device_name))\nelse:\n print('No GPU found.')\n \n\n# Initialise pytorch with GPU\nif torch.cuda.is_available(): \n device = torch.device('cuda')\n torch.cuda.empty_cache()\n print('There are %d GPU(s) available.' % torch.cuda.device_count())\n print('We will use the GPU:', torch.cuda.get_device_name(0))\n gpu = True\nelse:\n print('No GPU available, using the CPU instead.')\n device = torch.device('cpu')\n gpu = False","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Settings - only change if respective changes made in NLP notebook first\nTOKEN_LENGTH = 512\nBATCH_SIZE = 16\nSEED = 42\nthreshold = 0.5\n\n# Model to load\nload_model = True\nmodel_tag = 4\nmodel_epoch = 750","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"from transformers.modeling_bert import BertPreTrainedModel, BertModel\nfrom torch.nn import BCEWithLogitsLoss\n\nclass BertFromPreTrained(BertPreTrainedModel):\n \"\"\"\n BERT model for encoding.\n \"\"\"\n def __init__(self, config):\n super().__init__(config)\n self.bert = BertModel(config)\n\n def forward(self, input_ids, token_type_ids=None, attention_mask=None, labels=None):\n _, pooled_output = self.bert(input_ids, token_type_ids, attention_mask)\n \n return pooled_output\n \n def freeze_bert_encoder(self):\n for param in self.bert.parameters():\n param.requires_grad = False\n \n def unfreeze_bert_encoder(self):\n for param in self.bert.parameters():\n param.requires_grad = True","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"pretrained_bert = BertFromPreTrained.from_pretrained(\n 'bert-base-uncased', # Use the 12-layer BERT model, with an uncased vocab.\n output_attentions=False,\n output_hidden_states=False,\n)\n\npretrained_bert.to(device)\npretrained_bert.freeze_bert_encoder()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"import torch\nimport torch.nn.functional as F\nfrom sklearn.datasets import make_multilabel_classification\nfrom torch import optim\n\nclass Network(torch.nn.Module):\n def __init__(self):\n super().__init__()\n self.dropout = torch.nn.Dropout(0.1)\n self.layer1 = torch.nn.Linear(768, 32)\n # self.layer2 = torch.nn.Linear(32, 32)\n self.logits = torch.nn.Linear(32, 4)\n # self.relu = torch.nn.ReLU\n \n def forward(self, x):\n x = self.dropout(x)\n x = self.layer1(x)\n x = F.relu(x)\n #x = self.layer2(x)\n #x = F.relu(x)\n return self.logits(x)\n\n\nmodel = Network()\n# model.to(device)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Load weights\nif load_model and gpu:\n fpath = f'../input/cdp-abc-nlp/model_{model_tag}_{model_epoch}'\n print(f'Model fpath: {fpath}')\n model.load_state_dict(torch.load(fpath))\n model.to(device)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"import pandas as pd\nimport functools\nfrom transformers import BertTokenizer\nfrom torch.utils.data import TensorDataset, DataLoader, SequentialSampler\nimport torch.nn.functional as F\n\n\ndef make_predictions(model, df_test: pd.DataFrame, text_col: str, threshold: float = 0.5):\n \"\"\" Predict labels using model on column in df_test.\"\"\"\n \n def tokenize_dataset(df_test: pd.DataFrame, text_col: str):\n print('Number of test sequences: {:,}\\n'.format(df_test.shape[0]))\n sequences = df_test[text_col].values\n\n # Load tokenizer\n tokenizer = BertTokenizer.from_pretrained('bert-base-uncased', do_lower_case=True)\n\n input_ids = []\n attention_masks = []\n for s in sequences:\n encoded_dict = tokenizer.encode_plus(\n s,\n add_special_tokens = True,\n max_length = TOKEN_LENGTH,\n pad_to_max_length = True,\n return_attention_mask = True,\n return_tensors = 'pt',\n )\n\n input_ids.append(encoded_dict['input_ids'])\n attention_masks.append(encoded_dict['attention_mask'])\n\n\n input_ids = torch.cat(input_ids, dim=0)\n attention_masks = torch.cat(attention_masks, dim=0)\n\n prediction_data = TensorDataset(input_ids, attention_masks)\n prediction_sampler = SequentialSampler(prediction_data)\n prediction_dataloader = DataLoader(\n prediction_data, sampler=prediction_sampler, batch_size=BATCH_SIZE\n )\n return prediction_dataloader\n \n \n # Prediction on test set\n model.eval()\n\n predictions = [] \n for batch in tokenize_dataset(df_test, text_col):\n batch = tuple(t.to(device) for t in batch)\n b_input_ids, b_input_mask = batch\n\n # Forward pass, predict\n with torch.no_grad():\n b_token_type_ids = None\n outputs = pretrained_bert(b_input_ids, b_token_type_ids, b_input_mask) # no labels\n outputs = model(outputs)\n m = torch.nn.Sigmoid()\n outputs = m(outputs)\n\n logits = outputs.detach().cpu().numpy()\n predictions.append(logits)\n\n print('Completed predictions.')\n predictions_binary = [(p > threshold).astype(float) for p in predictions]\n return np.vstack(predictions_binary), np.vstack(predictions)\n\n\ndef _predict_on_dataframe(model, output_col_names: List[str], df_test: pd.DataFrame, \n text_col: str, threshold: float = 0.5):\n \n preds, probs = make_predictions(model, df_test, text_col, threshold)\n df_preds = pd.DataFrame(preds, columns=output_col_names)\n return pd.concat([df_test.reset_index(), df_preds], axis=1)\n\n\n# Add partial function for our use case\noutput_col_names = ['employment', 'inclusion', 'health', 'congestion']\npredict_on_dataframe = functools.partial(_predict_on_dataframe, model, output_col_names)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Q3.0. Do any of the actions explain how public health is incorporated in the \n# adaptation actions?\nif gpu:\n q_col = 'q3.0_nlp_h'\n cols = ['Organization', 'Country', 'Response Answer']\n _df = df_cities.loc[\n (df_cities['Question Number'] == '3.0') &\n (df_cities['Column Number'] == 8) &\n (df_cities['Country'] == 'United States of America') &\n (~df_cities['Response Answer'].isin([qna, np.nan])),\n cols\n ] \n\n _df = predict_on_dataframe(_df, 'Response Answer', threshold=threshold)\n _df = _df.rename(columns={'health': q_col})\n _df = _df.groupby(['Organization', 'Country']).agg({q_col: 'sum'}).reset_index()\n _df[q_col] = _df[q_col].apply(lambda x: (x > 0)).astype(float)\n\n # Merge to results dataframe\n _df.rename(columns=org_country_rename_dict, inplace=True)\n cols = ['organization', 'country', q_col]\n df_b = df_b.merge(_df[cols], on=['organization', 'country'], how='left')\n df_b[q_col] = df_b[q_col].fillna(0)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Q3.5. Please explain how your city has addressed vulnerable groups through transformative \n# action – employment is mentioned\nif gpu:\n q_col = 'q3.5_nlp_e'\n cols = ['Organization', 'Country', 'Response Answer']\n _df = df_cities.loc[\n (df_cities['Question Number'] == '3.5') &\n (df_cities['Country'] == 'United States of America') &\n (~df_cities['Response Answer'].isin([qna, np.nan])),\n cols\n ]\n\n _df = predict_on_dataframe(_df, 'Response Answer', threshold=threshold)\n _df = _df.rename(columns={'employment': q_col})\n _df = _df.groupby(['Organization', 'Country']).agg({q_col: 'sum'}).reset_index()\n _df[q_col] = _df[q_col].apply(lambda x: (x > 0)).astype(float)\n\n # Merge to results dataframe\n _df.rename(columns=org_country_rename_dict, inplace=True)\n cols = ['organization', 'country', q_col]\n df_b = df_b.merge(_df[cols], on=['organization', 'country'], how='left')\n df_b[q_col] = df_b[q_col].fillna(0)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Q6.2a. Does your city collaborate in partnership with businesses in your city on \n# sustainability projects?\nif gpu:\n q_col = 'q6.2a_nlp_e'\n cols = ['Organization', 'Country', 'Response Answer']\n _df = df_cities.loc[\n (df_cities['Question Number'] == '6.2a') &\n (df_cities['Column Number'] == 3) &\n (df_cities['Country'] == 'United States of America') &\n (~df_cities['Response Answer'].isin([qna, np.nan])),\n cols\n ]\n\n _df = predict_on_dataframe(_df, 'Response Answer', threshold=threshold)\n _df = _df.rename(columns={'employment': q_col})\n _df = _df.groupby(['Organization', 'Country']).agg({q_col: 'sum'}).reset_index()\n _df[q_col] = _df[q_col].apply(lambda x: (x > 0)).astype(float)\n\n # Merge to results dataframe\n _df.rename(columns=org_country_rename_dict, inplace=True)\n cols = ['organization', 'country', q_col]\n df_b = df_b.merge(_df[cols], on=['organization', 'country'], how='left')\n df_b[q_col] = df_b[q_col].fillna(0)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Q6.3. Describe how your local/regional government collaborates and coordinates horizontally on \n# climate action.\nif gpu:\n q_col = 'q6.3_nlp_e'\n cols = ['Organization', 'Country', 'Response Answer']\n _df = df_cities.loc[\n (df_cities['Question Number'] == '6.3') &\n (df_cities['Column Number'] == 2) &\n (df_cities['Country'] == 'United States of America') &\n (~df_cities['Response Answer'].isin([qna, np.nan])),\n cols\n ]\n\n _df = predict_on_dataframe(_df, 'Response Answer', threshold=threshold)\n _df = _df.rename(columns={'employment': q_col})\n _df = _df.groupby(['Organization', 'Country']).agg({q_col: 'sum'}).reset_index()\n _df[q_col] = _df[q_col].apply(lambda x: (x > 0)).astype(float)\n\n # Merge to results dataframe\n _df.rename(columns=org_country_rename_dict, inplace=True)\n cols = ['organization', 'country', q_col]\n df_b = df_b.merge(_df[cols], on=['organization', 'country'], how='left')\n df_b[q_col] = df_b[q_col].fillna(0)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Q3.5: Please explain how your city has addressed vulnerable groups through transformative \n# action. (Description includes any actions to support inclusion/address inequality)\nif gpu:\n q_col = 'q3.5_nlp_i'\n cols = ['Organization', 'Country', 'Response Answer']\n _df = df_cities.loc[\n (df_cities['Question Number'] == '3.5') &\n (df_cities['Country'] == 'United States of America') &\n (~df_cities['Response Answer'].isin([qna, np.nan])),\n cols\n ]\n\n _df = predict_on_dataframe(_df, 'Response Answer', threshold=threshold)\n _df = _df.rename(columns={'inclusion': q_col})\n _df = _df.groupby(['Organization', 'Country']).agg({q_col: 'sum'}).reset_index()\n _df[q_col] = _df[q_col].apply(lambda x: (x > 0)).astype(float)\n\n # Merge to results dataframe\n _df.rename(columns=org_country_rename_dict, inplace=True)\n cols = ['organization', 'country', q_col]\n df_b = df_b.merge(_df[cols], on=['organization', 'country'], how='left')\n df_b[q_col] = df_b[q_col].fillna(0)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Collaboration\n### Corporate location\nPrepare corporation locations for later merging with the cities dataset."},{"metadata":{"trusted":true},"cell_type":"code","source":"import pandas as pd\nfpath = '../input/cdp-unlocking-climate-solutions/Supplementary Data/Locations of Corporations/NA_HQ_public_data.csv'\ncols = ['account_number', 'organization', 'hq_country', 'address_city', 'address_state']\ndf_corp_locs = pd.read_csv(fpath, usecols=cols)\n\n# Error handling\nus = 'United States of America'\nuk = 'United Kindom and Northern Ireland'\ndf_corp_locs.loc[df_corp_locs['account_number'] == 1271, 'hq_country'] = uk\ndf_corp_locs.loc[df_corp_locs['account_number'] == 1464, 'hq_country'] = us\ndf_corp_locs.loc[df_corp_locs['account_number'] == 1464, 'address_city'] = 'new york'\ndf_corp_locs.loc[df_corp_locs['account_number'] == 1464, 'address_state'] = 'NY'\ndf_corp_locs.loc[df_corp_locs['account_number'] == 73516, 'address_city'] = 'chicago'\ndf_corp_locs.loc[df_corp_locs['account_number'] == 73516, 'address_state'] = 'IL'\n\n# Filter US\ndf_corp_locs = df_corp_locs[df_corp_locs['hq_country'] == us]\n\n# Get state code\n_us_state_abbrev = {k.lower(): v for k, v in us_state_abbrev.items()}\ndf_corp_locs['address_state'] = (\n df_corp_locs['address_state']\n .astype(str)\n .apply(lambda s: _replace(s.lower(), _us_state_abbrev).lower())\n)\ndf_corp_locs['address_city'] = df_corp_locs['address_city'].str.lower()\ndf_corp_locs = df_corp_locs.drop_duplicates().dropna().reset_index(drop=True)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Supplementary"},{"metadata":{"trusted":true},"cell_type":"code","source":"# C12.3: Do you engage in activities that could either directly or indirectly \n# influence public policy on climate-related issues through any of the following?\n# Each form of reported engagement scores 1\n\nfpath = '../input/cdp-unlocking-climate-solutions/Corporations/Corporations Responses/Climate Change/2020_Full_Climate_Change_Dataset.csv'\ndf_corp_2020 = pd.read_csv(fpath, low_memory=False)\n\ncols = ['account_number', 'response_value']\n_df = df_corp_2020.loc[df_corp_2020['question_number'] == 'C12.3', cols]\n_df = (\n _df.groupby('account_number')\n .agg({'response_value': 'nunique'})\n .rename(columns={'response_value': 'c12.3'})\n .reset_index()\n)\n\n# Merge and normalize\ndf_corp_locs = df_corp_locs.merge(_df, on='account_number', how='left')\ndf_corp_locs['c12.3'] = normalize_column(df_corp_locs, 'c12.3')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Collaboration by company sector and activities:"},{"metadata":{"trusted":true},"cell_type":"code","source":"CollabArea = collections.namedtuple('CollabArea', ['activities', 'sectors'])\n\ncollab_areas = [\n CollabArea('Health care facilities', 'Health care provision'),\n CollabArea('Health care facilities, Health care services', 'Health care provision, Other services'),\n CollabArea('Health care facilities, Health care services, Health care supplies, Medical equipment', 'Health care provision, Medical equipment & supplies, Other services'),\n CollabArea('Health care facilities, Health care services, Health care supplies, Medical equipment, Other food processing, Pharmaceuticals', 'Biotech & pharma, Food & beverage processing, Health care provision, Medical equipment & supplies, Other services'),\n CollabArea('Health care facilities, Health care services, Medical equipment', 'Health care provision, Medical equipment & supplies, Other services'),\n CollabArea('Health care services', 'Other services'),\n CollabArea('Health care services, Health care supplies, Insurance, Supermarkets, food & drugstores', 'Convenience retail, Financial services, Medical equipment & supplies, Other services'),\n CollabArea('Health care services, Health care supplies, Medical equipment, Personal care & household products, Pharmaceuticals', 'Biotech & pharma, Chemicals, Medical equipment & supplies, Other services'),\n CollabArea('Health care services, Insurance', 'Financial services, Other services'),\n CollabArea('Health care services, Medical equipment', 'Medical equipment & supplies, Other services'), \n CollabArea('Health care supplies', 'Medical equipment & supplies'),\n CollabArea('Health care supplies, Medical equipment', 'Medical equipment & supplies'),\n CollabArea('Medical equipment', 'Medical equipment & supplies'),\n CollabArea('Medical equipment, Pharmaceuticals', 'Biotech & pharma, Medical equipment & supplies'),\n CollabArea('Intermodal transport', 'Intermodal transport & logistics'),\n CollabArea('Intermodal transport, Logistics - 3rd party, Road freight', 'Intermodal transport & logistics, Road transport'),\n CollabArea('Logistics - 3rd party, Logistics - transport', 'Intermodal transport & logistics'),\n CollabArea('Logistics - 3rd party, Logistics - transport, Transportation support services, Vehicles & machinery rental & leasing', 'Industrial support services, Intermodal transport & logistics, Trading, wholesale, distribution, rental & leasing'),\n CollabArea('Logistics - transport', 'Intermodal transport & logistics'),\n CollabArea('Rail freight, Transportation support services', 'Industrial support services, Rail transport'),\n CollabArea('Specialist retail, Transportation equipment wholesale & dealing', 'Discretionary retail, Trading, wholesale, distribution, rental & leasing'),\n CollabArea('Transportation infrastructure & other construction', 'Construction')\n]\n\n# Get company sector\nfpath = '../input/cdp-unlocking-climate-solutions/Corporations/Corporations Disclosing/Climate Change/2020_Corporates_Disclosing_to_CDP_Climate_Change.csv'\ndf_corp_sec = pd.read_csv(fpath)\ndf_corp_sec.columns\ndf_corp_sec = df_corp_sec[['account_number', 'organization', 'country', 'activities', 'sectors']]\n\ndf_collab = pd.DataFrame([])\nfor ca in collab_areas:\n # Find companies with relevant sectors and activities\n _df = df_corp_sec.loc[\n (df_corp_sec['activities'].str.strip() == ca.activities) &\n (df_corp_sec['sectors'].str.strip() == ca.sectors)\n ]\n accounts = set(_df['account_number'])\n \n # Extract count of distinct collaboration types\n _df_collab = df_corp_2020.loc[\n (df_corp_2020['account_number'].isin(accounts)) &\n (df_corp_2020['question_number'] == 'C12.3')\n ]\n _df_collab = (\n _df_collab.groupby('account_number')\n .agg({'response_value': 'nunique'})\n .rename(columns={'response_value': 'c12.3_sector'})\n .reset_index()\n )\n df_collab = pd.concat([df_collab, _df_collab])\n\n\n# Merge and normalize\ndf_corp_locs = df_corp_locs.merge(df_collab, on='account_number', how='left')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Sum, normalize and merge with results dataset\ndf_corp_collab = (\n df_corp_locs.groupby(['address_city', 'address_state'])\n .agg({'c12.3': 'sum', 'c12.3_sector': 'sum'})\n .reset_index()\n)\n\na = set(df_corp_collab['address_city'])\nb = set(df_b['city'])\nprint(f\"{len(a - b)} cities are referenced as one or more corporations' HQs where\" +\n \" the city does not exist in the 2020 CDP Cities return.\")\n\ndf_corp_collab['c12.3'] = normalize_column(df_corp_collab, 'c12.3')\ndf_corp_collab['c12.3_sector'] = normalize_column(df_corp_collab, 'c12.3_sector')\ndf_corp_collab = df_corp_collab[['address_city', 'address_state', 'c12.3', 'c12.3_sector']]\ndf_corp_collab['address_state'] = df_corp_collab['address_state'].str.upper()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_b = df_b.merge(\n df_corp_collab, \n left_on=['city', 'state_id'], \n right_on=['address_city', 'address_state'], \n how='left'\n)\ndf_b = df_b.drop(['address_city', 'address_state'], axis=1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Q6.2: Does your city collaborate in partnership with businesses in your city on \n# sustainability projects? Each form of reported collaboration (Column Number = 2) \n# scores 1\ncols = ['Organization', 'Country', 'Response Answer']\n_df = df_cities.loc[\n (df_cities['Question Number'] == '6.2a') &\n (df_cities['Column Number'] == 2) &\n (df_cities['Country'] == 'United States of America') &\n (~df_cities['Response Answer'].isna()),\n cols\n]\n\n_df = (\n _df.groupby(['Organization', 'Country'])\n .agg({'Response Answer': 'nunique'})\n .rename(columns={'Response Answer': 'q6.2'})\n .reset_index()\n)\n\n# Merge to results dataframe\n_df['q6.2'] = normalize_column(_df, 'q6.2')\n_df.rename(columns=org_country_rename_dict, inplace=True)\ndf_b = df_b.merge(_df, on=['organization', 'country'], how='left')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Q6.2: Does your city collaborate in partnership with businesses in your city on \n# sustainability projects? Each form of reported collaboration (Column Number = 2) \n# scores 1\ncols = ['Organization', 'Country', 'Response Answer']\nanswers_collab_area = [\n 'Public Health and Safety', \n 'Transport (Mobility)', \n 'Building and Infrastructure'\n 'Social Services'\n]\n_df_a = df_cities.loc[\n (df_cities['Question Number'] == '6.2a') &\n (df_cities['Column Number'] == 2) &\n (df_cities['Country'] == 'United States of America') &\n (df_cities['Response Answer'].isin(answers_collab_area)),\n cols\n]\n\nanswers_collab_type = [\n 'Labour market training initiatives', \n 'Capacity development'\n]\n_df_t = df_cities.loc[\n (df_cities['Question Number'] == '6.2a') &\n (df_cities['Column Number'] == 3) &\n (df_cities['Country'] == 'United States of America') &\n (df_cities['Response Answer'].isin(answers_collab_type)),\n cols\n]\n\n_df = pd.concat([_df_a, _df_t], ignore_index=True)\n_df = (\n _df.groupby(['Organization', 'Country'])\n .agg({'Response Answer': 'sum'})\n .rename(columns={'Response Answer': 'q6.2_sectors'})\n .reset_index()\n)\n\n_df.shape\n# Merge to results dataframe\n#_df.rename(columns=org_country_rename_dict, inplace=True)\n#_df['q6.2_sectors'] = normalize_column(_df, 'q6.2_sectors')\n# df_b = df_b.merge(_df, on=['organization', 'country'], how='left')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# There are no examples for q6.2_sector: submit zeroes\ndf_b['q6.2_sector'] = 0","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Compile results\nCompile and clean the results dataframe for Sections A, B and C (df_b). Note: information yet to be added from the NLP work."},{"metadata":{"trusted":true},"cell_type":"code","source":"cols_to_fill_zero = [\n 'life_expectancy_rel', 'park_space_sq_km_per_capita_rel', \n 'potable_water_rel', 'q2.0b', 'q2.2', 'q3.0_health', 'q10.7', \n 'ev_charging_points_per_capita_rel', 'q14.2', 'q12.4', 'emp_rate_rel', \n 'green_jobs_per_capita_rel', 'public_tr_rel', 'q2.1', 'q5.4', 'q5.5a', \n 'q3.0_inclusion', 'q12.6', 'q10.14_pm2.5_annual_mean_rel_year', \n 'q10.14_pm2.5_max_24hr_mean_rel_year',\n 'q10.14_pm10_annual_mean_rel_year', 'q10.14_pm10_24hr_mean_rel_year',\n 'q10.14_no2_annual_mean_rel_year', 'q10.14_o3_daily_max_8hr_mean_rel_year',\n 'q10.14_so2_max_24hr_mean_rel_year', 'q10.14_pm2.5_annual_mean_rel', \n 'q10.14_pm2.5_max_24hr_mean_rel', 'q10.14_pm10_annual_mean_rel', \n 'q10.14_pm10_24hr_mean_rel', 'q10.14_no2_annual_mean_rel', \n 'q10.14_o3_daily_max_8hr_mean_rel', 'q10.14_so2_max_24hr_mean_rel',\n 'c12.3', 'c12.3_sector', 'q6.2'\n]\n\ndf_b.loc[:, cols_to_fill_zero] = df_b.loc[:, cols_to_fill_zero].fillna(0.)\ndf_b = df_b.rename(columns={'lat_long_y': 'lat_long'})","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Calculating metrics\n### Section B"},{"metadata":{"trusted":true},"cell_type":"code","source":"# Section B\nair_quality_cols = [\n 'q10.14_pm2.5_annual_mean_rel', \n 'q10.14_pm10_annual_mean_rel',\n 'q10.14_no2_annual_mean_rel',\n 'q10.14_o3_daily_max_8hr_mean_rel',\n 'q10.14_so2_max_24hr_mean_rel'\n]\n \ndef b1_health(r):\n le = r['life_expectancy_rel'] * 0.5\n aq = sum(r[c] for c in air_quality_cols) / len(air_quality_cols) * 0.125\n gn = r['park_space_sq_km_per_capita_rel'] * 0.125\n pw = r['potable_water_rel'] * 0.125\n fs = r['q12.6'] * 0.125\n return sum([le, aq, gn, pw, fs])\n\n\ndef b1_employment(r):\n es = r['emp_rate_rel'] * 0.9\n gj = r['green_jobs_per_capita_rel'] * 0.1\n return sum([es, gj])\n\n\ndef b1_congestion(r):\n ct = r['av_commute_mins_rel'] * 0.75\n pt = r['public_tr_rel'] * 0.25\n return sum([ct, pt])\n\n\ndef b1_inclusion(r):\n ei = r['economic_inclusion_index_2016'] * 0.5\n ri = r['racial_inclusion_index_2016'] * 0.5\n return sum([ei, ri])\n\n\ndf_b['b1_health'] = df_b.apply(lambda r: b1_health(r), axis=1)\ndf_b['b1_employment'] = df_b.apply(lambda r: b1_employment(r), axis=1)\ndf_b['b1_congestion'] = df_b.apply(lambda r: b1_congestion(r), axis=1)\ndf_b['b1_inclusion'] = df_b.apply(lambda r: b1_inclusion(r), axis=1)\ndf_b['b1'] = df_b[['b1_health', 'b1_employment', 'b1_congestion', 'b1_inclusion']].mean(axis=1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Section B\ndef b2_health(r):\n return np.mean([\n r['q2.0b'],\n r['q2.2'],\n r['q3.0_health'],\n r['q3.0_nlp_h'],\n r['q10.7'],\n r['ev_charging_points_per_capita_rel'],\n r['q14.2'],\n r['q12.4']\n ])\n\n\ndef b2_employment(r):\n return np.mean([\n r['q3.5_nlp_e'],\n r['q6.2a_nlp_e'],\n r['q6.3_nlp_e']\n ])\n\n\ndef b2_congestion(r):\n return np.mean([\n r['q2.1'], \n r['q5.4'], \n r['q5.5a']\n ])\n\n\ndef b2_inclusion(r):\n return np.mean([\n r['q3.0_inclusion'],\n r['q3.5_nlp_i']\n ])\n\n\ndf_b['b2_health'] = df_b.apply(lambda r: b2_health(r), axis=1)\ndf_b['b2_employment'] = df_b.apply(lambda r: b2_employment(r), axis=1)\ndf_b['b2_congestion'] = df_b.apply(lambda r: b2_congestion(r), axis=1)\ndf_b['b2_inclusion'] = df_b.apply(lambda r: b2_inclusion(r), axis=1)\ndf_b['b2'] = df_b[['b2_health', 'b2_employment', 'b2_congestion', 'b2_inclusion']].mean(axis=1)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Section C"},{"metadata":{"trusted":true},"cell_type":"code","source":"# Section C\ndef c1(r):\n return np.mean([\n r['q6.2'],\n r['c12.3']\n ])\n\n\ndf_b['c1'] = df_b.apply(lambda r: c1(r), axis=1)\ndf_b['c2'] = df_b[['q6.2_sector', 'c12.3_sector']].mean(axis=1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_b.to_csv('abc_results.csv')\nprint('Saved!')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_b.columns","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Code appendix\n### Get city latitude / longitude\nWe use the Python geopy library to access the GeoNames API in order to return the latitude and longitude of the cities in the dataset for plotting. The technical challenge here is to identify the correct city from the CDP survey 'organization' field, which contains the name of the organisation submitting the questionnaire and not the raw city name. In order to perform this efficiently from both a code running and writing perspective, we emply the following methodology:\n* Make all tokens lower case, non-accented, punctuation removed.\n* Remove 'stop words' related to organisations or bloat terminology (i.e. 'City of ')\n* Call the GeoNames API using an ISO-3166 country-code to discern cities of the same name.\n\nApproximately 10% of the dataset had to be completed by hand. Typically, the changes to the 'organization_clean' column required to get GeoNames to recognise the city were very minor, and this automated process could be improved minor effort."},{"metadata":{"trusted":true},"cell_type":"code","source":"# prevents run by Kaggle notebook on save\nif False:\n \n import string \n import unidecode\n import functools\n import re\n from typing import Set\n\n\n def _clean_string(stop_words: Set[str], s: str):\n \"\"\" Remove accents, punctuation, capitals and user-defined stop words. \"\"\"\n s = unidecode.unidecode(s.translate(str.maketrans('', '', string.punctuation)).lower())\n for stop_word in stop_words:\n s = re.sub(r'\\b' + stop_word + r'\\b', '', s)\n s = re.sub(stop_word + r'\\b', '', s)\n s = re.sub(r'\\b' + stop_word, '', s)\n return s.strip()\n\n stop_words = {' city', 'govt ', 'campos de ', 'prefeitura de ', 'ciudad ', 'town of ', 'village of ', 'ville de', 'executive', 'intendencia', 'village', 'intermedio', 'ambiente', 'peoples', 'combined', 'authority', 'borough', 'distrital', 'administration', 'ajuntament', 'alcaldia', 'assembly', 'ayuntamiento', 'city of', ' of ', ' de ', ' di ', ' da ', 'capital', 'council', 'comune', 'kommune', 'district', 'federal', 'government', 'junta', 'regency', 'region', 'county', 'municipality', 'municipal', 'municipalidad', 'municipio', 'metropolitan', 'metropole', 'gemeente', 'prefeitura', 'metropolitana', 'territory', 'township', 'xiv'}\n stop_words = sorted(list(stop_words), key=lambda x: -len(x)) # ensure longest match taken first\n clean_string = functools.partial(_clean_string, stop_words)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"if False:\n # Prepare country names for ISO-3166 country-code matching\n df_city_locs = df_cities[['Organization', 'Country']].drop_duplicates().copy()\n df_city_locs = df_city_locs.rename(columns={\n 'Organization': 'organization', \n 'Country': 'country'\n })\n df_city_locs['organization_clean'] = df_city_locs['organization'].apply(clean_string)\n df_city_locs['country_lower'] = df_city_locs['country'].apply(lambda s: s.lower())\n\n country_name_map = {\n 'united kingdom of great britain and northern ireland': 'united kingdom',\n 'china, hong kong special administrative region': 'hong kong',\n 'republic of korea': 'korea, republic of (south korea)',\n 'republic of moldova': 'moldova, republic of',\n 'taiwan, greater china': 'taiwan',\n 'russian federation': 'russia',\n 'state of palestine': 'palestinian territory, occupied',\n 'united republic of tanzania': 'tanzania, united republic of',\n 'bolivia (plurinational state of)': 'bolivia'\n }\n\n def _replace(s, d):\n return d[s] if s in d.keys() else s\n\n df_city_locs['country_lower'] = (\n df_city_locs['country_lower'].apply(lambda s: _replace(s, country_name_map))\n )","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"if False:\n # Get IS0 country codes\n df_country_codes = pd.read_csv(\n '../input/countries-iso-codes/wikipedia-iso-country-codes.csv', \n keep_default_na=False # parse Namibia country code 'NA'\n )\n rename_dict = {\n 'English short name lower case': 'country_lower', \n 'Alpha-2 code': 'alpha2_code'\n }\n df_country_codes = df_country_codes.rename(columns=rename_dict)\n df_country_codes['country_lower'] = df_country_codes['country_lower'].apply(\n lambda s: s.lower()\n )\n\n df_city_locs = df_city_locs.merge(\n df_country_codes[['country_lower', 'alpha2_code']],\n on='country_lower', \n how='left'\n )","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"if False:\n from geopy import geocoders \n\n username = '' # set up at http://www.geonames.org/login\n # ensure webservice enabled for your account\n\n gn = geocoders.GeoNames(username=username)\n\n # List cities A-Z (in case we run out of free API call 'credits'; tracks progress)\n city_locs_list = sorted(df_city_locs.to_dict(orient='records'), \n key=lambda d: d['organization'])\n\n # Get latitude / longitude information\n city_locs_list_with_geocode = []\n failed_locs = []\n for d in city_locs_list:\n # If making many requests, consider using a 1s time delay\n geocode_call = gn.geocode(d['organization_clean'], country=d['alpha2_code'])\n if geocode_call is not None:\n d['lat_long'] = (geocode_call.latitude, geocode_call.longitude)\n city_locs_list_with_geocode.append(d)\n else:\n failed_locs.append(d)\n msg = 'No geocode information found for: {} ({})'\n print(msg.format(d['organization_clean'], d['alpha2_code']))\n\n msg = 'Found lat/long info for {}/{} ({:.1f}%) of cities.'\n print(msg.format(\n len(city_locs_list_with_geocode),\n len(city_locs_list), \n len(city_locs_list_with_geocode) / len(city_locs_list) * 100\n ))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Get translations of freetext answers\nWe can make up to 20k word translations each day using the GoogleTranslate free API. This is OK when running the model on new lines but makes creating an initial model training set more difficult as we quickly run out of calls, hence we place this code in the annex. We run them as follows:"},{"metadata":{"trusted":true},"cell_type":"code","source":"if False:\n df_3_0 = df_cities[\n (df_cities['Question Number'] == '3.0') &\n (df_cities['Column Name'] == 'Action description and implementation progress')\n ]\n\n df_3_5 = df_cities[df_cities['Question Number'] == '3.5']\n\n df_5_4 = df_cities[\n (df_cities['Question Number'] == '5.4') & \n (df_cities['Column Name'] == 'Scope and impact of action')\n ]\n\n _df = pd.concat([df_3_0, df_3_5, df_5_4], axis=0)\n\n cols = ['Account Number', 'Organization', 'Country', 'Question Number', 'Column Number', 'Response Answer']\n a = _df.shape[0]\n df = _df[~_df['Response Answer'].isin(['Question not applicable'])][cols].dropna()\n b = df.shape[0]\n\n print('Removed {} NaN records, leaving {} records.'.format(a - b, b))\n\n # We don't want to waste time trying to translate English answers, so try to pre-empt responses\n # which will be returned in English for all cities within a country. Tackle only the countries\n # with the largest numbers of submissions\n\n # List of countries with all responses in English\n en_countries = ['United Kingdom of Great Britain and Northern Ireland', 'United States of America', \n 'Canada', 'Australia', 'New Zealand', 'Denmark', 'Italy', 'Netherlands', 'Sweden', ]\n\n df[~df['Country'].isin(en_countries)]\n df['Country'].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"if False:\n from polyglot.detect import Detector\n\n list_of_dicts = df.to_dict(orient='records')\n\n # Use polyglot to detect languages to reduce the number of calls we'll make to the Google API\n list_of_dicts_reduced = []\n for d in list_of_dicts:\n if 'response_answer_translated' not in d.keys():\n try:\n lang = Detector(d['Response Answer']).languages[0].code\n except:\n lang = ''\n if lang != 'en':\n list_of_dicts_reduced.append([d])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"if False:\n import sys\n import time\n import deep_translator\n\n\n for d in list_of_dicts_reduced:\n if 'response_answer_translated' not in d[0].keys():\n time.sleep(1) # Google requests 0.2s delay but can complain at <1s\n try:\n t = (\n deep_translator.GoogleTranslator(source='auto', target='en')\n .translate(d[0]['Response Answer'])\n )\n d[0]['response_answer_translated'] = t\n except deep_translator.exceptions.NotValidLength:\n d[0]['response_answer_translated'] = ''\n continue\n except deep_translator.exceptions.TooManyRequests:\n print('Too many requests!')\n break\n except: deep_translator.exceptions.TranslationNotFound:\n d[0]['response_answer_translated'] = ''\n except:\n print(\"Unexpected error:\", sys.exc_info()[0])\n # continue","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"if False:\n df_translated = df.merge(\n df_list_of_dicts_reduced, \n on=['Account Number', 'Question Number', 'Column Number', 'Response Answer'], \n how='left'\n )\n df_translated = df_translated.drop(columns=['Organization_y', 'Country_y'])\n df_translated = df_translated.rename(columns={\n 'Organization_x': 'organization', \n 'Country_x': 'country', \n 'Response Answer_x': 'response_answer'\n })\n\n save = False\n if save:\n df_translated.to_csv('bert_translations.csv')\n print('Saved!')\n df_translated.head()","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}