LocalifyMusicEvents-USA-2019/LocalifyMusicEvents-USA-2019_Step0_DataCreation.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "0b45c435",
   "metadata": {},
   "source": [
    "# Pre-Processing: Dataset Creation"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "681cc9e8",
   "metadata": {},
   "source": [
    "## IMPORTANT Note for Reproducibility:\n",
    "\n",
    "This code will scrape the **LATEST Census data** on CensusReporter. Our paper only uses 2019 Census data. To use the dataset used for our paper, click on this link: https://github.com/JimiLab/LocalifyMusicEventData/blob/main/LocalifyMusicEvents-USA-2019/data/LocalifyMusicEvents-USA-2019_paper.csv. \n",
    "\n",
    "**Only use this notebook if you are using the LATEST Census data.**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "606f2ab9",
   "metadata": {},
   "source": [
    "NOTE: Due to the nature of the procedure in this notebook, we advise that this notebook be run ONE AT A TIME (NOT all at once!)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "c4999e21",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import censusdata\n",
    "import requests\n",
    "import webbrowser\n",
    "import os\n",
    "from sqlalchemy import create_engine\n",
    "from geopy.geocoders import Nominatim\n",
    "import numpy as np\n",
    "import re\n",
    "import time\n",
    "from tqdm import tqdm, trange\n",
    "import math\n",
    "from bs4 import BeautifulSoup as bs\n",
    "import math\n",
    "import unidecode\n",
    "import json\n",
    "from lxml import etree\n",
    "\n",
    "import mysql.connector\n",
    "from time import sleep"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4da5d325",
   "metadata": {},
   "source": [
    "### Read CSV Files and Preprocess them\n",
    "\n",
    "We are only looking at city-level data here. Any non-city-level data (such as county-level data) are filtered out. Any data based in US territories such as Puerto Rico are also filtered out."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "7f4ed3eb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Below excel spreadsheet downloaded from https://www2.census.gov/programs-surveys/metro-micro/geographies/reference-files/2020/delineation-files/list2_2020.xls\n",
    "df = pd.read_excel('data/list2_2020.xls') \n",
    "df = df[2:-4]\n",
    "df.rename(columns={'Table with row headers in column A and column headers in row 3':'CBSA Code', 'Unnamed: 1':'CBSA Title', 'Unnamed: 2':'Metropolitan/Micropolitan Statistical Area', 'Unnamed: 3':'Principal City Name', 'Unnamed: 4':'FIPS State Code', 'Unnamed: 5':'FIPS Place Code'}, inplace=True)\n",
    "df = df.set_index('CBSA Code')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "35c4f270",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Exclude Puerto Rico (which has state code 72)\n",
    "df = df[df['FIPS State Code'] != '72']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "82063d2c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Below excel spreadsheet downloaded from https://gist.github.com/dantonnoriega/bf1acd2290e15b91e6710b6fd3be0a53\n",
    "states = pd.read_csv('data/us-state-ansi-fips.csv', delimiter=',') \n",
    "states.columns=['stname', 'st', 'stusps']\n",
    "states.index = states['st']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "61304b3f",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "state_abbrv = []\n",
    "for i in range(len(df)):\n",
    "    state_abbrv.append(states['stusps'][int(df['FIPS State Code'][i])])\n",
    "\n",
    "df['Principal City Name'] = df['Principal City Name'] \n",
    "df['State'] = state_abbrv\n",
    "df[\"State\"] = df[\"State\"].str.strip()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "5d5286df",
   "metadata": {},
   "outputs": [],
   "source": [
    "state_name = []\n",
    "for i in range(len(df)):\n",
    "    state_name.append(states['stname'][int(df['FIPS State Code'][i])])\n",
    "\n",
    "df['State Name'] = state_name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "a03ea18d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get rid of actual counties that are listed as a \"Principal City\":\n",
    "df = df[df['Principal City Name'].str.contains('County') == False]\n",
    "\n",
    "# Get rid of \"Nashville-Davidson metropolitan government\"\n",
    "df = df[~df['Principal City Name'].str.contains('government')]\n",
    "\n",
    "# \"Butte-Silver Bow\" is not a city\n",
    "df = df[~df['Principal City Name'].str.contains('Butte')]\n",
    "\n",
    "# \"Lexington-Fayette\" is not a city, but a county\n",
    "df = df[~(df['Principal City Name'] == 'Lexington-Fayette')]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c7d1204d",
   "metadata": {},
   "source": [
    "### Get Corresponding Latitude, Longitude Coordinates for each Area\n",
    "\n",
    "Note that an area can have multiple cities/counties. \n",
    "\n",
    "Some of the principal cities below may require manual manipulation (as the region names may need to be cleaned up)."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d95f0344",
   "metadata": {},
   "source": [
    "WARNING: This cell can take just over 10 minutes to run. Try to reduce the number of times you run this cell below!!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "301eeedc",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 1246/1246 [10:30<00:00,  1.98it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Time elapsed: 630.5243608951569 seconds\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "# Get longitudes and latitudes of each individual city. An area can have multiple cities. \n",
    "latitudes = []\n",
    "longitudes = []\n",
    "\n",
    "start = time.time()\n",
    "\n",
    "gn = Nominatim(user_agent=\"name\")\n",
    "\n",
    "for i in tqdm(range(len(df))):\n",
    "    city = df['Principal City Name'].iloc[i]\n",
    "    state = df['State'].iloc[i]\n",
    "    loc = gn.geocode(city + ', ' + state)\n",
    "    latitudes.append(loc.latitude if loc is not None else np.nan)\n",
    "    longitudes.append(loc.longitude if loc is not None else np.nan)\n",
    "\n",
    "print('Time elapsed:', time.time() - start, 'seconds')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "b5f3306e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Latitude'] = latitudes\n",
    "df['Longitude'] = longitudes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5326e886",
   "metadata": {},
   "source": [
    "Below areas require manual manipulation\n",
    "(Paper: Put on Appendix)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "876524d3",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/michaelzhou/opt/anaconda3/lib/python3.8/site-packages/pandas/core/indexing.py:692: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  iloc._setitem_with_indexer(indexer, value, self.name)\n"
     ]
    }
   ],
   "source": [
    "# \"Indianapolis city (balance), IN\" -> \"Indianapolis, IN\"\n",
    "a = gn.geocode('Indianapolis, IN')\n",
    "df.at['26900', 'Latitude'].iloc[2] = a.latitude\n",
    "df.at['26900', 'Longitude'].iloc[2] =  a.longitude"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "6807fb39",
   "metadata": {},
   "outputs": [],
   "source": [
    "# \"Urban Honolulu, HI\" -> \"Honolulu, HI\"\n",
    "a = gn.geocode('Honolulu, HI')\n",
    "df.at['46520', 'Latitude'] = a.latitude\n",
    "df.at['46520', 'Longitude'] = a.longitude"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "fbcc5daf",
   "metadata": {},
   "outputs": [],
   "source": [
    "# \"San Luis Obispo, CA\" coordinates corrected via latlong.net\n",
    "df.at['42020', 'Latitude'].iloc[1] = 35.2827524\n",
    "df.at['42020', 'Longitude'].iloc[1] = -120.6596156"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "e377ce3c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# \"Napa, CA\" coordinates corrected via latlong.net\n",
    "df.at['34900', 'Latitude'] = 38.297539\n",
    "df.at['34900', 'Longitude'] = -122.286865"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "22d431bb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Geocode confuses \"Middletown, NY\" with the \"Midtown NY\", so we have to change manually from latlong.net\n",
    "df.at['39100', 'Latitude'].iloc[3] = 41.450123 \n",
    "df.at['39100', 'Longitude'].iloc[3] = -74.429253"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "1df603c7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# \"Grenada, MS\" coordinates corrected via latlong.net\n",
    "df.at['24980', 'Latitude'] = 33.7817513\n",
    "df.at['24980', 'Longitude'] = -89.81306"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "6c90b0f7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Inspect missing location coordinate values\n",
    "assert(len(df[df['Latitude'].isnull().values]) == 0)\n",
    "assert(len(df[df['Longitude'].isnull().values]) == 0)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "abf11397",
   "metadata": {},
   "source": [
    "### Retrieve Socioeconomic Indicators"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "53fe3ede",
   "metadata": {},
   "source": [
    "We will retrieve different socioeconomic indicators by scraping four different websites - CensusReporter, DataUSA, Census Quickfacts, and AreaVibes. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b4236b41",
   "metadata": {},
   "source": [
    "#### CensusReporter\n",
    "\n",
    "Retrieve 2019 population, population density, median age, percent of population per age group, per capita income, median household income, education-related rates, mean travel time, transportation rates (walking, public transit, biking), poverty rate, median property value, migration rate since previous year, percent of population per race.\n",
    "\n",
    "Example query: https://censusreporter.org/profiles/16000US2002900-atchison-ks/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "28b30383",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Find relevant script from CensusReporter\n",
    "def find_relevant_script(soup):\n",
    "    script_tags = soup.findAll('script')\n",
    "    \n",
    "    for script in script_tags:\n",
    "        if 'profileData' in str(script):\n",
    "            return str(script)\n",
    "\n",
    "    return None"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2fd46987",
   "metadata": {},
   "source": [
    "WARNING: The following chunk of code can take 15-20 minutes to run:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "a64dcf90",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 1246/1246 [18:24<00:00,  1.13it/s]\n"
     ]
    }
   ],
   "source": [
    "# Population/population density:\n",
    "population_2019 = []\n",
    "population_density = []\n",
    "\n",
    "# Median Age:\n",
    "median_age = []\n",
    "\n",
    "# Age diversity:\n",
    "population_under_18 = []\n",
    "population_18_to_64 = []\n",
    "population_over_65 = []\n",
    "\n",
    "population_0_to_9 = []\n",
    "population_10_to_19 = []\n",
    "population_20_to_29 = []\n",
    "population_30_to_39 = []\n",
    "population_40_to_49 = []\n",
    "population_50_to_59 = []\n",
    "population_60_to_69 = []\n",
    "population_70_to_79 = []\n",
    "population_over_80 = []\n",
    "\n",
    "proportion_under_18 = []\n",
    "proportion_18_to_64 = []\n",
    "proportion_over_65 = []\n",
    "\n",
    "proportion_0_to_9 = []\n",
    "proportion_10_to_19 = []\n",
    "proportion_20_to_29 = []\n",
    "proportion_30_to_39 = []\n",
    "proportion_40_to_49 = []\n",
    "proportion_50_to_59 = []\n",
    "proportion_60_to_69 = []\n",
    "proportion_70_to_79 = []\n",
    "proportion_over_80 = []\n",
    "\n",
    "# Income:\n",
    "per_capita_income = []\n",
    "median_household_income = []\n",
    "\n",
    "# Education:\n",
    "highschool_or_higher = []\n",
    "bachelors_or_higher = []\n",
    "postgrad = []\n",
    "\n",
    "# Transportation:\n",
    "mean_travel_time = []\n",
    "public_transit = []\n",
    "bicycle = []\n",
    "walkability = []\n",
    "\n",
    "# Poverty rate:\n",
    "poverty_rate = []\n",
    "\n",
    "# Median value of owner-occupied housing units:\n",
    "median_property_value = []\n",
    "\n",
    "# Percentage moved since previous year:\n",
    "migration_rate_since_previous_year = []\n",
    "\n",
    "# Race populations:\n",
    "population_white = []\n",
    "population_black = []\n",
    "population_native = []\n",
    "population_asian = []\n",
    "population_islander = []\n",
    "population_other = []\n",
    "population_two_or_more = []\n",
    "population_hispanic = []\n",
    "\n",
    "for i in tqdm(range(len(df))):\n",
    "    row = df.iloc[i]\n",
    "    \n",
    "    url = 'https://censusreporter.org/profiles/16000US' + f\"{int(row['FIPS State Code']):02d}\" + f\"{int(row['FIPS Place Code']):05d}\"\n",
    "    response = requests.get(url)\n",
    "        \n",
    "    soup = bs(response.text)\n",
    "    js_script = find_relevant_script(soup)\n",
    "    data_string = re.search('(?<=profileData = )(.*)(?=;)', js_script).group(0)\n",
    "    \n",
    "    profile_data = json.loads(data_string)\n",
    "    \n",
    "    ### WARNING: Asserting this statement will fail, as CensusReporter is no longer using ACS-2019 data.\n",
    "    # assert('2019' in profile_data['geography']['census_release'])\n",
    "\n",
    "    # Population/population density:\n",
    "    population_2019.append(profile_data['geography']['this']['total_population'])\n",
    "    population_density.append(profile_data['geo_metadata']['population_density'])\n",
    "\n",
    "    # Median Age:\n",
    "    median_age.append(profile_data['demographics']['age']['median_age']['total']['values']['this'])\n",
    "\n",
    "    # Age diversity:\n",
    "    population_under_18.append(profile_data['demographics']['age']['distribution_by_category']['percent_under_18']['numerators']['this'])\n",
    "    population_18_to_64.append(profile_data['demographics']['age']['distribution_by_category']['percent_18_to_64']['numerators']['this'])\n",
    "    population_over_65.append(profile_data['demographics']['age']['distribution_by_category']['percent_over_65']['numerators']['this'])\n",
    "    \n",
    "    population_0_to_9.append(profile_data['demographics']['age']['distribution_by_decade']['total']['0-9']['numerators']['this'])\n",
    "    population_10_to_19.append(profile_data['demographics']['age']['distribution_by_decade']['total']['10-19']['numerators']['this'])\n",
    "    population_20_to_29.append(profile_data['demographics']['age']['distribution_by_decade']['total']['20-29']['numerators']['this'])\n",
    "    population_30_to_39.append(profile_data['demographics']['age']['distribution_by_decade']['total']['30-39']['numerators']['this'])\n",
    "    population_40_to_49.append(profile_data['demographics']['age']['distribution_by_decade']['total']['40-49']['numerators']['this'])\n",
    "    population_50_to_59.append(profile_data['demographics']['age']['distribution_by_decade']['total']['50-59']['numerators']['this'])\n",
    "    population_60_to_69.append(profile_data['demographics']['age']['distribution_by_decade']['total']['60-69']['numerators']['this'])\n",
    "    population_70_to_79.append(profile_data['demographics']['age']['distribution_by_decade']['total']['70-79']['numerators']['this'])\n",
    "    population_over_80.append(profile_data['demographics']['age']['distribution_by_decade']['total']['80+']['numerators']['this'])\n",
    "    \n",
    "    proportion_under_18.append(profile_data['demographics']['age']['distribution_by_category']['percent_under_18']['values']['this'])\n",
    "    proportion_18_to_64.append(profile_data['demographics']['age']['distribution_by_category']['percent_18_to_64']['values']['this'])\n",
    "    proportion_over_65.append(profile_data['demographics']['age']['distribution_by_category']['percent_over_65']['values']['this'])\n",
    "    \n",
    "    proportion_0_to_9.append(profile_data['demographics']['age']['distribution_by_decade']['total']['0-9']['values']['this'])\n",
    "    proportion_10_to_19.append(profile_data['demographics']['age']['distribution_by_decade']['total']['10-19']['values']['this'])\n",
    "    proportion_20_to_29.append(profile_data['demographics']['age']['distribution_by_decade']['total']['20-29']['values']['this'])\n",
    "    proportion_30_to_39.append(profile_data['demographics']['age']['distribution_by_decade']['total']['30-39']['values']['this'])\n",
    "    proportion_40_to_49.append(profile_data['demographics']['age']['distribution_by_decade']['total']['40-49']['values']['this'])\n",
    "    proportion_50_to_59.append(profile_data['demographics']['age']['distribution_by_decade']['total']['50-59']['values']['this'])\n",
    "    proportion_60_to_69.append(profile_data['demographics']['age']['distribution_by_decade']['total']['60-69']['values']['this'])\n",
    "    proportion_70_to_79.append(profile_data['demographics']['age']['distribution_by_decade']['total']['70-79']['values']['this'])\n",
    "    proportion_over_80.append(profile_data['demographics']['age']['distribution_by_decade']['total']['80+']['values']['this'])\n",
    "    \n",
    "    \n",
    "    # Income:\n",
    "    per_capita_income.append(profile_data['economics']['income']['per_capita_income_in_the_last_12_months']['values']['this'])\n",
    "    median_household_income.append(profile_data['economics']['income']['median_household_income']['values']['this'])\n",
    "\n",
    "    # Education:\n",
    "    highschool_or_higher.append(profile_data['social']['educational_attainment']['percent_high_school_grad_or_higher']['values']['this'])\n",
    "    bachelors_or_higher.append(profile_data['social']['educational_attainment']['percent_bachelor_degree_or_higher']['values']['this'])\n",
    "    postgrad.append(profile_data['social']['educational_attainment_distribution']['post_grad_degree']['values']['this'])\n",
    "\n",
    "    # Transportation:\n",
    "    mean_travel_time.append(profile_data['economics']['employment']['mean_travel_time']['values']['this'])\n",
    "    public_transit.append(profile_data['economics']['employment']['transportation_distribution']['public_transit']['values']['this'])\n",
    "    bicycle.append(profile_data['economics']['employment']['transportation_distribution']['Bicycle']['values']['this'])\n",
    "    walkability.append(profile_data['economics']['employment']['transportation_distribution']['walked']['values']['this'])\n",
    "\n",
    "    # Poverty rate:\n",
    "    poverty_rate.append(profile_data['economics']['poverty']['percent_below_poverty_line']['values']['this'])\n",
    "\n",
    "    # Median value of owner-occupied housing units:\n",
    "    median_property_value.append(profile_data['housing']['ownership']['median_value']['values']['this'])\n",
    "\n",
    "    # Percentage moved since previous year:\n",
    "    migration_rate_since_previous_year.append(profile_data['housing']['migration']['moved_since_previous_year']['values']['this'])\n",
    "\n",
    "    # Race populations:\n",
    "    population_white.append(profile_data['demographics']['race']['percent_white']['numerators']['this'])\n",
    "    population_black.append(profile_data['demographics']['race']['percent_black']['numerators']['this'])\n",
    "    population_native.append(profile_data['demographics']['race']['percent_native']['numerators']['this'])\n",
    "    population_asian.append(profile_data['demographics']['race']['percent_asian']['numerators']['this'])\n",
    "    population_islander.append(profile_data['demographics']['race']['percent_islander']['numerators']['this'])\n",
    "    population_other.append(profile_data['demographics']['race']['percent_other']['numerators']['this'])\n",
    "    population_two_or_more.append(profile_data['demographics']['race']['percent_two_or_more']['numerators']['this'])\n",
    "    population_hispanic.append(profile_data['demographics']['race']['percent_hispanic']['numerators']['this'])\n",
    "\n",
    "# Population/population density:\n",
    "df['Population'] = population_2019\n",
    "df['Population Density'] = population_density\n",
    "\n",
    "# Median Age:\n",
    "df['Median Age'] = median_age\n",
    "\n",
    "# Age diversity:\n",
    "df['Population Under 18'] = population_under_18\n",
    "df['Population 18-64'] = population_18_to_64\n",
    "df['Population Over 65'] = population_over_65\n",
    "\n",
    "df['Population 0-9'] = population_0_to_9\n",
    "df['Population 10-19'] = population_10_to_19\n",
    "df['Population 20-29'] = population_20_to_29\n",
    "df['Population 30-39'] = population_30_to_39\n",
    "df['Population 40-49'] = population_40_to_49\n",
    "df['Population 50-59'] = population_50_to_59\n",
    "df['Population 60-69'] = population_60_to_69\n",
    "df['Population 70-79'] = population_70_to_79\n",
    "df['Population Over 80'] = population_over_80\n",
    "\n",
    "df['Percent Under 18'] = proportion_under_18 \n",
    "df['Percent 18-64'] = proportion_18_to_64 \n",
    "df['Percent Over 65'] = proportion_over_65\n",
    "\n",
    "df['Percent 0-9'] = proportion_0_to_9\n",
    "df['Percent 10-19'] = proportion_10_to_19\n",
    "df['Percent 20-29'] = proportion_20_to_29\n",
    "df['Percent 30-39'] = proportion_30_to_39\n",
    "df['Percent 40-49'] = proportion_40_to_49\n",
    "df['Percent 50-59'] = proportion_50_to_59\n",
    "df['Percent 60-69'] = proportion_60_to_69\n",
    "df['Percent 70-79'] = proportion_70_to_79\n",
    "df['Percent Over 80'] = proportion_over_80\n",
    "\n",
    "# Income:\n",
    "df['Per Capita Income'] = per_capita_income\n",
    "df['Median Household Income'] = median_household_income\n",
    "\n",
    "# Education:\n",
    "df['High School Or Higher'] = highschool_or_higher\n",
    "df['Bachelor Or Higher'] = bachelors_or_higher\n",
    "df['Postgrad Degree'] = postgrad\n",
    "\n",
    "# Transportation:\n",
    "df['Mean Travel Time'] = mean_travel_time\n",
    "df['Public Transit'] = public_transit\n",
    "df['Bicycle'] = bicycle\n",
    "df['Walkability'] = walkability\n",
    "\n",
    "# Poverty rate:\n",
    "df['Poverty Rate'] = poverty_rate\n",
    "\n",
    "# Median value of owner-occupied housing units:\n",
    "df['Median Property Value'] = median_property_value\n",
    "\n",
    "# Percentage moved since previous year:\n",
    "df['Migration Rate Since Previous Year'] = migration_rate_since_previous_year\n",
    "\n",
    "# Race populations:\n",
    "df['Population White'] = population_white\n",
    "df['Population Black'] = population_black\n",
    "df['Population Native'] = population_native\n",
    "df['Population Asian'] = population_asian\n",
    "df['Population Islander'] = population_islander\n",
    "df['Population Other'] = population_other\n",
    "df['Population Two+'] = population_two_or_more\n",
    "df['Population Hispanic'] = population_hispanic"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c9108ef6",
   "metadata": {},
   "source": [
    "#### DataUSA\n",
    "\n",
    "Retrieve employed population and calculate employment rate. \n",
    "\n",
    "Example query: https://datausa.io/profile/geo/middletown-ny"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "75358ee7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Find index given DataUSA HTML tag list\n",
    "def find_index(tag_list, field):\n",
    "    i = 0\n",
    "    for profile in tag_list:\n",
    "        if field in profile:\n",
    "            return i\n",
    "        i+=1\n",
    "    return -1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "b898b9c6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get value from DataUSA\n",
    "def get_value(tag_list, field):\n",
    "    i = find_index(tag_list, field)\n",
    "    return tag_list[i+1].string "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "69a4d3b6",
   "metadata": {},
   "source": [
    "WARNING: The following chunk of code may take about 10-20 minutes to run (unless the web requests are cached):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "2cf4bea5",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 1246/1246 [09:31<00:00,  2.18it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Time elapsed: 571.6628119945526 seconds\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "employed_population = []\n",
    "\n",
    "m = {'K': 3, 'M': 6, 'B': 9, 'T': 12} # for converting strings like '2.69M' to 2690000\n",
    "\n",
    "start = time.time()\n",
    "\n",
    "start_index = 0\n",
    "\n",
    "for i in tqdm(range(start_index, len(df)), initial=start_index):\n",
    "    row = df.iloc[i]\n",
    "    \n",
    "    city = row['Principal City Name']\n",
    "    state = row['State']\n",
    "    city_ticker = city.lower().replace(',', '').replace('.', '').replace('/','').replace(' ', '-').replace(\"'\",'').replace('(', '').replace(')', '')\n",
    "    city_ticker = unidecode.unidecode(city_ticker) # remove accents (e.g. replace 'ñ' with 'n')\n",
    "    \n",
    "    results = requests.get('https://datausa.io/profile/geo/' + city_ticker + '-' + state.lower().strip())\n",
    "    soup = bs(results.text)\n",
    "    tag_list = soup.findAll(True, {'class':['stat-title', 'stat-value', 'stat-subtitle']})\n",
    "    \n",
    "    ep = get_value(tag_list, '2019 Employed Population')\n",
    "    employed_population.append(math.nan if ep[-2].isalpha() else int(ep.replace(',','')) if not ep[-1].isalpha() else int(float(ep[:-1]) * 10**m[ep[-1]]))\n",
    "    \n",
    "    # Wait for some time before the next request \n",
    "    sleep(0.1)\n",
    "\n",
    "print('Time elapsed:', time.time() - start, 'seconds')\n",
    "\n",
    "df['Employed Population'] = employed_population\n",
    "df['Employment Rate'] = df['Employed Population'] / df['Population']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cb75f45e",
   "metadata": {},
   "source": [
    "#### Census QuickFacts\n",
    "\n",
    "Retrieve population from 4/1/2010, median gross rent (2015-2019), median owner costs with mortgage (2015-2019), median owner costs without mortgage (2015-2019), owner-occupied housing unit rate (2015-2019).\n",
    "\n",
    "Example queries: \n",
    "- https://www.census.gov/quickfacts/fact/table/milwaukeecitywisconsin\n",
    "- https://www.census.gov/quickfacts/fact/table/senecafallscdpnewyork \n",
    "\n",
    "Note that we query at most 6 cities at a time (to save time). If there is no data available for a certain city, we ignore it. \n",
    "\n",
    "So a sample query from our implementation would be similar to this:\n",
    "https://www.census.gov/quickfacts/fact/table/annapoliscitymaryland,amsterdamcitynewyork,andrewscitytexas,angolacityindiana,annarborcitymichigan,annistoncityalabama"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "bd22ed5d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get a certain field value from Census Quickfacts\n",
    "def get_values(soup, field, num):\n",
    "    text = ''\n",
    "    td_tags = soup.findAll('td')\n",
    "    i = 0\n",
    "    for body in td_tags:\n",
    "        if field in str(body):\n",
    "            text = body\n",
    "            break\n",
    "        i+=1\n",
    "    \n",
    "    vals = []\n",
    "    for j in range(1, num+1):\n",
    "        try: \n",
    "            vals.append(float(td_tags[i+j].attrs.get(\"data-value\", None)))\n",
    "        except:\n",
    "            continue\n",
    "    \n",
    "    return vals"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "81a2aba7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get Census population recorded on April 1, 2010\n",
    "def get_2010_population(soup, num):\n",
    "    return get_values(soup, 'Population, Census, April 1, 2010', num)\n",
    "\n",
    "# Get Median Gross Rent from 2015-2019\n",
    "def get_median_gross_rent(soup, num):\n",
    "    return get_values(soup, 'Median gross rent, 2015-2019', num)\n",
    "\n",
    "# Get Median selected monthly owner costs -with a mortgage, 2015-2019\n",
    "def get_median_owner_cost_with_mortgage(soup, num):\n",
    "    return get_values(soup, 'Median selected monthly owner costs -with a mortgage, 2015-2019', num)\n",
    "\n",
    "# Get Median selected monthly owner costs -without a mortgage, 2015-2019\n",
    "def get_median_owner_cost_without_mortgage(soup, num):\n",
    "    return get_values(soup, 'Median selected monthly owner costs -without a mortgage, 2015-2019', num)\n",
    "\n",
    "# Get Owner-occupied housing unit rate, 2015-2019\n",
    "def get_owner_occupied_housing_unit_rate(soup, num):\n",
    "    return get_values(soup, 'Owner-occupied housing unit rate, 2015-2019', num)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d44e6dd1",
   "metadata": {},
   "source": [
    "WARNING: The following chunk of code will take over an hour to run!!!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "a944dfeb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get the associated string to the city for the URL (e.g. \"Milwaukee, WI\" is \"city\" for \"milwaukeecitywisconsin\")\n",
    "# Usually, the principal city will be followed by \"city\" as a suffix before the state; \n",
    "# however, there are some exceptions in which the suffix is actually \"town\", \"village\", \"borough\", \"cdp\", etc.\n",
    "# The code below specifies these exceptions:\n",
    "def get_city_str(city, state):\n",
    "    city_str = 'city'\n",
    "\n",
    "    if 'balance' in city_ticker or (city == 'Carson City' and state == 'Nevada') or (city == 'Princeton' and state == 'New Jersey'):\n",
    "        city_str = ''\n",
    "\n",
    "    elif city == 'Anchorage' and state == 'Alaska': # Anchorage, AL only shows up as 'Anchorage municipality'\n",
    "        city_str = 'municipality'\n",
    "\n",
    "    elif (state == 'Pennsylvania' and (city == 'Berwick' \n",
    "                                       or city == 'Chambersburg' \n",
    "                                       or city == 'Waynesboro' \n",
    "                                       or city == 'East Stroudsburg' \n",
    "                                       or city == 'Gettysburg'\n",
    "                                       or city == 'Carlisle' \n",
    "                                       or city == 'Huntingdon' \n",
    "                                       or city == 'Indiana'  \n",
    "                                       or city == 'Lewisburg'\n",
    "                                      or city == 'Lewistown'\n",
    "                                      or city == 'Somerset'\n",
    "                                      or city == 'Sayre'\n",
    "                                      or city == 'Selinsgrove'\n",
    "                                      or city == 'State College'\n",
    "                                      or city == 'Hanover')):\n",
    "        city_str = 'borough'\n",
    "    \n",
    "    elif city == 'Juneau' and state == 'Alaska':\n",
    "        city_str = 'cityandborough'\n",
    "    \n",
    "    elif city == 'Norwich' and state == 'Connecticut':\n",
    "        city_str = 'townnewlondoncounty'\n",
    "    \n",
    "    elif (city == 'Stratford' and state == 'Connecticut'):\n",
    "        city_str = 'town' + 'fairfieldcounty'\n",
    "\n",
    "    elif (city == 'Bolingbrook' and state == 'Illinois'\n",
    "          or city == 'Hoffman Estates' and state == 'Illinois'\n",
    "          or city == 'Schaumburg' and state == 'Illinois'\n",
    "          or city == 'Skokie' and state == 'Illinois'\n",
    "          or city == 'Fredonia' and state == 'New York'\n",
    "          or city == 'Malone' and state == 'New York'\n",
    "          or city == 'Massena' and state == 'New York'\n",
    "         or city == 'Pinehurst' and state == 'North Carolina'\n",
    "         or city == 'Ruidoso' and state == 'New Mexico'\n",
    "         or city == 'Weston' and state == 'Wisconsin'):\n",
    "        city_str = 'village'\n",
    "\n",
    "    elif (city == 'Hammonton' and state == 'New Jersey'\n",
    "          or city == 'Big Stone Gap' and state == 'Virginia'\n",
    "          or city == 'Blacksburg' and state == 'Virginia'\n",
    "          or city == 'Christiansburg' and state == 'Virginia'\n",
    "          or city == 'Bloomsburg' and state == 'Pennsylvania'\n",
    "          or city == 'Boone' and state == 'North Carolina'\n",
    "          or city == 'Breckenridge' and state == 'Colorado'\n",
    "          or city == 'Chapel Hill' and state == 'North Carolina'\n",
    "          or city == 'Easton' and state == 'Maryland'\n",
    "          or city == 'Forest City' and state == 'North Carolina'\n",
    "          or city == 'Greeneville' and state == 'Tennessee'\n",
    "          or city == 'Bluffton' and state == 'South Carolina'\n",
    "          or city == 'Hilton Head Island' and state == 'South Carolina'\n",
    "          or city == 'Jackson' and state == 'Wyoming'\n",
    "          or city == 'Kill Devil Hills' and state == 'North Carolina'\n",
    "          or city == 'Jupiter' and state == 'Florida'\n",
    "          or city == 'Morehead City' and state == 'North Carolina'\n",
    "          or city == 'Prescott Valley' and state == 'Arizona'\n",
    "          or city == 'Cary' and state == 'North Carolina'\n",
    "         or city == 'Payson' and state == 'Arizona'\n",
    "         or city == 'Southern Pines' and state == 'North Carolina'\n",
    "         or city == 'Silver City' and state == 'New Mexico'\n",
    "         or city == 'Taos' and state == 'New Mexico'\n",
    "         or city == 'Truckee' and state == 'California'):\n",
    "        city_str = 'town'\n",
    "    \n",
    "    elif (state == 'Hawaii' \n",
    "          or city == 'Columbia' and state == 'Maryland' \n",
    "          or city == 'Towson' and state == 'Maryland'\n",
    "          or city == 'Bennington' and state == 'Vermont'\n",
    "          or city == 'Silverdale' and state == 'Washington'\n",
    "          or city == 'Cheektowaga' and state == 'New York'\n",
    "          or city == 'California' and state == 'Maryland'\n",
    "          or city == 'Lexington Park' and state == 'Maryland'\n",
    "          or city == 'Cullowhee' and state == 'North Carolina'\n",
    "          or city == 'Edwards' and state == 'Colorado'\n",
    "          or city == 'Fort Knox' and state == 'Kentucky'\n",
    "          or city == 'Fort Leonard Wood' and state == 'Missouri'\n",
    "          or city == 'Fort Polk South' and state == 'Louisiana'\n",
    "          or city == 'Gardnerville Ranchos' and state == 'Nevada'\n",
    "          or city == 'East Hartford' and state == 'Connecticut'\n",
    "          or city == 'Homosassa Springs' and state == 'Florida'\n",
    "          or city == 'The Woodlands' and state == 'Texas'\n",
    "          or city == 'Paradise' and state == 'Nevada'\n",
    "          or city == 'Los Alamos' and state == 'New Mexico'\n",
    "          or city == 'Kendall' and state == 'Florida'\n",
    "          or city == 'Metairie' and state == 'Louisiana'\n",
    "          or city == 'Lakewood' and state == 'New Jersey'\n",
    "          or city == 'Ferry Pass' and state == 'Florida'\n",
    "          or city == 'The Villages' and state == 'Florida'\n",
    "          or city == 'Arlington' and state == 'Virginia'\n",
    "          or city == 'Reston' and state == 'Virginia'\n",
    "          or city == 'Pahrump' and state == 'Nevada'\n",
    "         or city == 'Brent' and state == 'Florida'\n",
    "         or city == 'Woodbury' and state == 'New York'\n",
    "         or city == 'Seneca Falls' and state == 'New York'\n",
    "         or city == 'Bethesda' and state == 'Maryland'\n",
    "         or city == 'Fort Drum' and state == 'New York'\n",
    "         or city == 'Zapata' and state == 'Texas'):\n",
    "        city_str = 'cdp'\n",
    "    \n",
    "    return city_str"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "6796c071",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "1248it [1:06:20,  3.19s/it]                          "
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Elapsed time: 3980.339728116989 seconds\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "num = 6\n",
    "df.index = range(len(df))\n",
    "\n",
    "start = time.time()\n",
    "\n",
    "start_index = 0\n",
    "\n",
    "with tqdm(total=len(df), initial=start_index) as pbar:\n",
    "    for i in range(start_index, len(df), num):\n",
    "        city_tickers = ''\n",
    "        for j in range(num):\n",
    "            \n",
    "            if i+j >= len(df):\n",
    "                break\n",
    "            \n",
    "            row = df.iloc[i+j]\n",
    "            city = row['Principal City Name']\n",
    "            state = row['State Name'].strip()\n",
    "            \n",
    "            city_ticker = city.lower().replace(',', '').replace('.', '').replace('/','').replace(' ', '').replace(\"'\",'').replace('(', '').replace(')', '').replace('-','')\n",
    "            city_ticker = unidecode.unidecode(city_ticker)\n",
    "            city_str = get_city_str(city, state)\n",
    "            city_ticker = city_ticker + city_str + state.lower().replace(' ', '')\n",
    "\n",
    "            city_tickers += city_ticker + ','\n",
    "\n",
    "        url = 'https://www.census.gov/quickfacts/fact/table/' + city_tickers[:-1]\n",
    "        response = requests.get(url)\n",
    "\n",
    "        soup = bs(response.text)\n",
    "        \n",
    "        population_2010 = get_2010_population(soup, num)\n",
    "        median_gross_rent = get_median_gross_rent(soup, num)\n",
    "        median_owner_cost_with_mortgage = get_median_owner_cost_with_mortgage(soup,num)\n",
    "        median_owner_cost_without_mortgage = get_median_owner_cost_without_mortgage(soup,num)\n",
    "        owner_occupied_housing_unit_rate = get_owner_occupied_housing_unit_rate(soup,num)\n",
    "        \n",
    "        k = 0\n",
    "        for j in range(num):\n",
    "            \n",
    "            if i+j >= len(df):\n",
    "                break\n",
    "            \n",
    "            row = df.iloc[i+j]\n",
    "            city = row['Principal City Name']\n",
    "            state = row['State Name'].strip()\n",
    "            \n",
    "            # The following cities do not have any relevant data \n",
    "            if (city == 'Cornelia' and state == 'Georgia'\n",
    "                or city == 'Mount Gay-Shamrock' and state == 'West Virginia'\n",
    "                or city == 'North Wilkesboro' and state == 'North Carolina'\n",
    "               or city == 'Point Pleasant' and state == 'West Virginia'\n",
    "               or city == 'Summerville' and state == 'Georgia'\n",
    "               or city == 'Vineyard Haven' and state == 'Massachusetts'\n",
    "               or city == 'Wauchula' and state == 'Florida'\n",
    "               or city == 'Boardman' and state == 'Ohio'):\n",
    "                assert(math.isnan(df.at[i+j, 'Population (2010)']))\n",
    "                assert(math.isnan(df.at[i+j, 'Median Gross Rent']))\n",
    "                assert(math.isnan(df.at[i+j, 'Median Owner Cost With Mortgage']))\n",
    "                assert(math.isnan(df.at[i+j, 'Median Owner Cost Without Mortgage']))\n",
    "                assert(math.isnan(df.at[i+j, 'Owner-Occupied Housing Unit Rate']))\n",
    "                continue\n",
    "            \n",
    "            df.at[i+j, 'Population (2010)'] = population_2010[k] \n",
    "            df.at[i+j, 'Median Gross Rent'] = median_gross_rent[k] \n",
    "            df.at[i+j, 'Median Owner Cost With Mortgage'] = median_owner_cost_with_mortgage[k] \n",
    "            df.at[i+j, 'Median Owner Cost Without Mortgage'] = median_owner_cost_without_mortgage[k] \n",
    "            df.at[i+j, 'Owner-Occupied Housing Unit Rate'] = owner_occupied_housing_unit_rate[k]\n",
    "            \n",
    "            k += 1\n",
    "        \n",
    "        pbar.update(num)\n",
    "\n",
    "print('Elapsed time:', time.time() - start, 'seconds')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "0c5ce8e4",
   "metadata": {},
   "outputs": [],
   "source": [
    "assert(len(df[df['Population (2010)'].isnull().values]) == 8)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "df582aab",
   "metadata": {},
   "source": [
    "#### AreaVibes.com\n",
    "\n",
    "Retrieve the crime rates (number of reported incidents per 100K people): total crime, violent crime, property crime\n",
    "\n",
    "Example query: https://www.areavibes.com/indianapolis-in/crime/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "0682575d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get Crime Rate (number of reported incidents per 100K people) from AreaVibes.com\n",
    "def get_crime_rate(table_td_tags, crime_type='Total crime'):\n",
    "    i = 0\n",
    "    for tag in table_td_tags:\n",
    "        if crime_type in tag.text:\n",
    "            break\n",
    "        i+=1\n",
    "    \n",
    "    result = table_td_tags[i+3].text.replace(',','').replace('(estimate)','').strip()\n",
    "    return math.nan if result=='n/a' else float(result)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f4274ea7",
   "metadata": {},
   "source": [
    "WARNING: The code snippet below takes about 7-9 minutes to run:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "3e6363ca",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 1246/1246 [09:00<00:00,  2.31it/s]\n"
     ]
    }
   ],
   "source": [
    "for i in tqdm(range(len(df))):\n",
    "    row = df.iloc[i]\n",
    "    city = row['Principal City Name']\n",
    "    state = row['State']\n",
    "\n",
    "    if city.startswith('Indianapolis') or city.startswith('Boise') or city.startswith('Urban H'):\n",
    "        city = city.lower().replace('city','').replace('urban', '').strip()\n",
    "\n",
    "    city = city.lower().replace('(balance)','').strip()\n",
    "    city_ticker = city.replace(' ', '+').lower() + '-' + state.lower().strip()\n",
    "    city_ticker = unidecode.unidecode(city_ticker)\n",
    "\n",
    "    url = f'https://www.areavibes.com/{city_ticker}/crime/'\n",
    "\n",
    "    response = requests.get(url)\n",
    "\n",
    "    if response.status_code != 200:\n",
    "        print(i, 'Something Wrong', url)\n",
    "        continue\n",
    "    \n",
    "    soup = bs(response.text)\n",
    "\n",
    "    table_td_tags = soup.find('table').findAll('td')\n",
    "    total_crime_rate = get_crime_rate(table_td_tags, 'Total crime')\n",
    "    violent_crime_rate = get_crime_rate(table_td_tags, 'Violent crime')\n",
    "    property_crime_rate = get_crime_rate(table_td_tags, 'Property crime')\n",
    "\n",
    "    df.at[i, 'Total Crime Rate'] = total_crime_rate\n",
    "    df.at[i, 'Violent Crime Rate'] = violent_crime_rate\n",
    "    df.at[i, 'Property Crime Rate'] = property_crime_rate\n",
    "    \n",
    "    # Wait for some time before the next request \n",
    "    sleep(0.1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "39ee4439",
   "metadata": {},
   "source": [
    "### Calculate metrics"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "344f9f10",
   "metadata": {},
   "source": [
    "Diversity Index Formula (Used for Race and Age):\n",
    "\n",
    "$D = 1 - \\frac{\\sum{n (n-1)}}{N (N-1)}$\n",
    "\n",
    "where:\n",
    "$n$ = Number of individuals of each species/category, \n",
    "$N$ = Number of total individuals of all species/categories\n",
    "\n",
    "Reference: https://www.statisticshowto.com/simpsons-diversity-index/ "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "9e3d8243",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Race\n",
    "populations_per_race = df[['Population White', 'Population Black', 'Population Native', 'Population Asian', 'Population Islander', 'Population Other', 'Population Two+', 'Population Hispanic']]\n",
    "total_populations_race = populations_per_race.sum(axis=1)\n",
    "df['Race Diversity Index'] = (populations_per_race * (populations_per_race - 1)).sum(axis=1) / (total_populations_race * (total_populations_race - 1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "3200b4fc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Age\n",
    "populations_per_age = df[['Population 0-9', 'Population 10-19', 'Population 20-29', 'Population 30-39', 'Population 40-49', 'Population 50-59', 'Population 60-69', 'Population 70-79', 'Population Over 80']]\n",
    "total_populations_age = populations_per_age.sum(axis=1)\n",
    "df['Age Diversity Index'] = (populations_per_age * (populations_per_age - 1)).sum(axis=1) / (total_populations_age * (total_populations_age - 1))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2ef964e8",
   "metadata": {},
   "source": [
    "Define percent of population under 30 years old, 10-29 years old, and 18-29 years old:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "id": "dcc083b7",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Percent Under 30'] = df['Percent 0-9'] + df['Percent 10-19'] + df['Percent 20-29']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "5597b361",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Percent 10-29'] = df['Percent 10-19'] + df['Percent 20-29']\n",
    "df['Percent 18-29'] = df['Percent Under 30'] - df['Percent Under 18']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aa44f231",
   "metadata": {},
   "source": [
    "Add the percentages taking public transit, biking, and walking together:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "id": "60a4a8a7",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Public Transit+Bicycle+Walkability'] = df[['Public Transit', 'Bicycle', 'Walkability']].sum(axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "be15b02a",
   "metadata": {},
   "source": [
    "Calculate population growth from 2010 to 2019:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "62a87a8b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['10 Year Population Growth'] = df['Population'] - df['Population (2010)']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6b2fb943",
   "metadata": {},
   "source": [
    "### Reorganize Columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "id": "6f245d7e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_copy = pd.read_excel('data/list2_2020.xls') # Download from https://www2.census.gov/programs-surveys/metro-micro/geographies/reference-files/2020/delineation-files/list2_2020.xls\n",
    "df_copy = df_copy[2:-4]\n",
    "df_copy.rename(columns={'Table with row headers in column A and column headers in row 3':'CBSA Code', 'Unnamed: 1':'CBSA Title', 'Unnamed: 2':'Metropolitan/Micropolitan Statistical Area', 'Unnamed: 3':'Principal City Name', 'Unnamed: 4':'FIPS State Code', 'Unnamed: 5':'FIPS Place Code'}, inplace=True)\n",
    "df_copy = df_copy.set_index('CBSA Code')\n",
    "\n",
    "# Exclude Puerto Rico (which has state code 72)\n",
    "df_copy = df_copy[df_copy['FIPS State Code'] != '72']\n",
    "\n",
    "# Get rid of actual counties that are listed as a \"Principal City\":\n",
    "df_copy = df_copy[df_copy['Principal City Name'].str.contains('County') == False]\n",
    "\n",
    "# Get rid of \"Nashville-Davidson metropolitan government\"\n",
    "df_copy = df_copy[~df_copy['Principal City Name'].str.contains('government')]\n",
    "\n",
    "# \"Butte-Silver Bow\" is not a city\n",
    "df_copy = df_copy[~df_copy['Principal City Name'].str.contains('Butte')]\n",
    "\n",
    "# \"Lexington-Fayette\" is not a city, but a county\n",
    "df_copy = df_copy[~(df_copy['Principal City Name'] == 'Lexington-Fayette')]\n",
    "\n",
    "df['CBSA Code'] = df_copy.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "id": "513f42e5",
   "metadata": {},
   "outputs": [],
   "source": [
    "cols = ['CBSA Code', 'CBSA Title',\n",
    "   'Metropolitan/Micropolitan Statistical Area', 'Principal City Name',\n",
    "   'State', 'State Name', 'FIPS State Code', 'FIPS Place Code', 'Latitude', 'Longitude', \n",
    "        \n",
    "    'Population', 'Population (2010)', '10 Year Population Growth',\n",
    "    'Population Density', 'Migration Rate Since Previous Year',\n",
    "        \n",
    "    'Mean Travel Time', 'Public Transit', 'Bicycle', 'Walkability', \n",
    "    'Public Transit+Bicycle+Walkability',\n",
    "    \n",
    "    'Per Capita Income', 'Median Household Income', 'Poverty Rate', 'Median Property Value',\n",
    "    'Employed Population', 'Employment Rate', 'Median Gross Rent',\n",
    "    'Median Owner Cost With Mortgage', 'Median Owner Cost Without Mortgage',\n",
    "    'Owner-Occupied Housing Unit Rate',\n",
    "    \n",
    "    'Median Age', 'Population 0-9', 'Population 10-19',\n",
    "   'Population 20-29', 'Population 30-39', 'Population 40-49',\n",
    "   'Population 50-59', 'Population 60-69', 'Population 70-79',\n",
    "   'Population Over 80', 'Population Under 18',\n",
    "   'Population 18-64', 'Population Over 65', 'Percent Under 18',\n",
    "   'Percent 18-64', 'Percent Over 65', 'Percent 0-9', 'Percent 10-19',\n",
    "   'Percent 20-29', 'Percent 30-39', 'Percent 40-49', 'Percent 50-59',\n",
    "   'Percent 60-69', 'Percent 70-79', 'Percent Over 80', \n",
    "    'Age Diversity Index', 'Percent Under 30', 'Percent 10-29', 'Percent 18-29',\n",
    "        \n",
    "    'High School Or Higher', 'Bachelor Or Higher', 'Postgrad Degree', \n",
    "        \n",
    "    'Population White', 'Population Black', 'Population Native',\n",
    "    'Population Asian', 'Population Islander', 'Population Other',\n",
    "    'Population Two+', 'Population Hispanic', 'Race Diversity Index',\n",
    "        \n",
    "    'Total Crime Rate', 'Violent Crime Rate', 'Property Crime Rate'\n",
    "   ]\n",
    "\n",
    "df = df[cols]\n",
    "df = df.loc[:,~df.columns.duplicated()]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9cd06f38",
   "metadata": {},
   "source": [
    "### Count number of shows per city\n",
    "\n",
    "Connect Localify MySQL database.\n",
    "\n",
    "Get a count of shows in each city in the year of 2019.\n",
    "\n",
    "Make sure that you add the database to your MySQL server using the \"localify_2021_12_08_week_bkup.sql\" file.\n",
    "\n",
    "You should also create a 'my.conf' file, whose contents should look something like this:\n",
    "\n",
    "``\n",
    "[connector_python]\n",
    "host = 127.0.0.1\n",
    "database = database_name\n",
    "user = your_user\n",
    "password = your_password\n",
    "``"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "id": "77b7ec9c",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = mysql.connector.connect(option_files='my.conf') # Use your OWN my.conf file!\n",
    "\n",
    "start_date = '2019-01-01' # start from 2019\n",
    "end_date = '2019-12-31' # end in 2019"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f20203cc",
   "metadata": {},
   "source": [
    "WARNING: This following code segment can over a minute to run (unless cached)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "id": "d693ea84",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 1246/1246 [00:14<00:00, 86.54it/s] "
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Time elapsed: 14.400950193405151 seconds\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "# Note: This following segment takes about 20 seconds!\n",
    "\n",
    "# Calculate the number of events listed in a certain city, state. \n",
    "\n",
    "# One exception was noted: \n",
    "# The 1 event listed in Paradise, NV is a typo in the database - it was listed in \"aradise\" instead of \"Paradise\".\n",
    "event_counts = []\n",
    "\n",
    "start = time.time()\n",
    "\n",
    "for idx in tqdm(range(len(df))):\n",
    "    row = df.iloc[idx]\n",
    "    city_name = row['Principal City Name']\n",
    "    state = row['State'].strip()\n",
    "    \n",
    "    # Since the principal city name (from Census data) is used, some city names might need to be cleaned up before querying the database.\n",
    "    if city_name == \"Indianapolis city (balance)\" and state == \"IN\":\n",
    "        city_name = \"Indianapolis\"\n",
    "        \n",
    "    if city_name == \"San Buenaventura (Ventura)\" and state == \"CA\":\n",
    "        city_name = \"Ventura\"\n",
    "        \n",
    "    if city_name == \"Urban Honolulu\" and state == \"HI\":\n",
    "        city_name = \"Honolulu\"\n",
    "    \n",
    "    if city_name == \"Boise City\" and state == \"ID\":\n",
    "        city_name = \"Boise\"\n",
    "    \n",
    "    q = f\"\"\"\n",
    "    SELECT count(*)\n",
    "       FROM venue\n",
    "       INNER JOIN event on event.venue_id = venue.id\n",
    "       INNER JOIN artist_to_event on event.id = artist_to_event.event_id\n",
    "       INNER JOIN artist on artist.id = artist_to_event.artist_id\n",
    "       where \n",
    "         start_time >= '{start_date}' and start_time <= '{end_date}'\n",
    "         and ticket_url not like '%ithaca.com%'\n",
    "         and venue.city_name like \"{city_name}\"\n",
    "         and venue.state like '{state}'\n",
    "       ORDER BY event.start_time;\n",
    "       \"\"\"\n",
    "    \n",
    "    # The 1 event listed in Paradise, NV is a typo in the database - it was listed in \"aradise\" instead of \"Paradise\".\n",
    "    num_events = int(pd.read_sql_query(q, conn).iloc[0]) if not (city_name == \"Paradise\" and state.strip() == \"NV\") else 1 \n",
    "    \n",
    "    event_counts.append(num_events)\n",
    "\n",
    "    \n",
    "print('Time elapsed:', time.time() - start, 'seconds')\n",
    "\n",
    "df['Number of Events'] = event_counts\n",
    "df['Live Event Music Rate (LMER)'] = df['Number of Events'] / df['Population']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "id": "1888962e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Close connection\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "456e056e",
   "metadata": {},
   "source": [
    "### Finally, Write to CSV File!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "393cbef5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write to file!\n",
    "df = df.sort_values('Live Event Music Rate (LMER)', ascending=False)\n",
    "df.to_csv('data/LocalifyMusicEvents-USA-2019_full.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "091d251e",
   "metadata": {},
   "source": [
    "### Apply Filter for Paper: Population must have 10K+ people in each city!\n",
    "\n",
    "Write this to a separate dataset file. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "04ba940d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# df = pd.read_csv('data/LocalifyMusicEvents-USA-2019_full.csv')\n",
    "df_paper = df[df['Population'] >= 10000]\n",
    "df_paper.to_csv('data/LocalifyMusicEvents-USA-2019_paper.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1b385755",
   "metadata": {},
   "source": [
    "### Note:\n",
    "\n",
    "For cells that involve scraping web pages (and take a long time to run), if you see an error that says \"list out of range\", try waiting for a while, then run it again. Often times, you might have had too many requests in too short period of time, which often times links to a CAPTCHA page. "
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}