analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "source": [
    "# Setup"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import ipdb\n",
    "from collections import defaultdict, Counter\n",
    "import glob\n",
    "\n",
    "import sys\n",
    "import seaborn as sns\n",
    "import pylab\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "import geopandas as gpd\n",
    "\n",
    "PAPER_DATA_TIME = 1600387200 # September 25th, 2020 (approximate)\n",
    "\n",
    "def print_full(df):\n",
    "    with pd.option_context('display.max_rows', None, 'display.max_columns', None):\n",
    "        display(df)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "ISPS_PER_STATE = { # major ISPs\n",
    "'AR': ['att', 'centurylink', 'xfinity', 'cox', 'windstream'],\n",
    "'ME': ['consolidated','charter'],\n",
    "'MA': ['verizon','xfinity','charter'],\n",
    "'NY': ['verizon', 'charter', 'frontier'],\n",
    "'NC': ['charter','att','centurylink','windstream', 'frontier'],\n",
    "'OH': ['att','charter', 'frontier', 'centurylink', 'windstream'],\n",
    "'VT': ['consolidated', 'xfinity'],\n",
    "'VA': ['xfinity', 'cox', 'verizon', 'centurylink'],\n",
    "'WI': ['att','charter', 'centurylink', 'frontier']\n",
    "}\n",
    "\n",
    "ISP_FULL_NAMES = {\n",
    "'windstream': 'Windstream Holdings, Inc.',\n",
    "'charter': 'Charter Communications',\n",
    "'xfinity': 'Comcast Corporation',\n",
    "'centurylink':'CenturyLink, Inc.',\n",
    "'att': 'AT&T Inc.',\n",
    "'cox': 'Cox Communications, Inc.',\n",
    "'verizon': 'Verizon Communications Inc.',\n",
    "'frontier': 'Frontier Communications Corporation',\n",
    "'consolidated': 'Consolidated Communications, Inc.',\n",
    "}\n",
    "\n",
    "major_isps_rev = {v:k for k, v in ISP_FULL_NAMES.items()}\n",
    "\n",
    "STATES = ['AR', 'ME', 'MA', 'NY', 'NC', 'OH', 'VT', 'VA', 'WI']\n",
    "STATES_TUPLE = ('AR', 'ME', 'MA', 'NY', 'NC', 'OH','VT','VA','WI')\n",
    "\n",
    "# Positive responses\n",
    "POS_RESPONSES = {\n",
    "'att': ('2', '3'),\n",
    "'xfinity': ('2','3'),\n",
    "'centurylink': ('1',),\n",
    "'cox': ('1',),\n",
    "'consolidated': ('1',),\n",
    "'verizon': ('1','25'),\n",
    "'windstream': ('1',),\n",
    "'charter': ('1', ),\n",
    "'frontier': ('2','3'), \n",
    "}\n",
    "\n",
    "# Negative responses (no coverage)\n",
    "NEG_RESPONSES = {\n",
    "'att': ('0',),\n",
    "'xfinity': ('0',),\n",
    "'centurylink': ('20', '21'),\n",
    "'cox': ('0',),\n",
    "'consolidated': ('0', '20'),\n",
    "'verizon': ('0', '21'),\n",
    "'windstream': ('22','24'),\n",
    "'charter': ('0','29'),\n",
    "'frontier':('0','21'), \n",
    "\n",
    "}\n",
    "\n",
    "# Responses that the address is unrecognized\n",
    "UNRECOGNIZED_RESPONSES = {\n",
    "'att': ('4',),\n",
    "'xfinity': ('4',),\n",
    "'centurylink': ('0','4'),\n",
    "'cox': ('4',),\n",
    "'consolidated': ('21','22'),\n",
    "'verizon': ('4',),\n",
    "'windstream': ('20','23'),\n",
    "'charter': (),\n",
    "'frontier': (), \n",
    "}\n",
    "\n",
    "# Business responses\n",
    "BUSINESS_RESPONSES = {\n",
    "'att': (),\n",
    "'xfinity': ('5',),\n",
    "'centurylink': (),\n",
    "'cox': ('5',),\n",
    "'consolidated': (),\n",
    "'verizon': (),\n",
    "'windstream': (),\n",
    "'charter': (),\n",
    "'frontier':(), \n",
    "}\n",
    "\n",
    "# Sensitivity Analysis 2 (Table 12)\n",
    "UNKNOWN_RESPONSES = {\n",
    "'att': ('23', '24', '25', '26','30', '34'),\n",
    "'xfinity': ('20', '21', '28', '29', '33', '34'),\n",
    "'centurylink': ('-60', '-65',  '22', '24', '31'),\n",
    "'cox': (),\n",
    "'consolidated': ('23','31','32'),\n",
    "'verizon': ('23','24','25','30'),\n",
    "'windstream': ('21',),\n",
    "'charter': ('22','24','25'),\n",
    "'frontier': ('22','23'),\n",
    "}\n",
    "\n",
    "# Excluded (If not in UNKNOWN_RESPONSES)\n",
    "EXCLUDED_RESPONSES = {\n",
    "'att': ('23', '24', '25', '26','30', '34'),\n",
    "'xfinity': ('20', '21', '28', '29', '33', '34', '40'),\n",
    "'centurylink': ('-60', '-65',  '22', '24', '31', '33'),\n",
    "'cox': ('31',),\n",
    "'consolidated': ('23','31','32'),\n",
    "'verizon': ('23','24','25','30'),\n",
    "'windstream': ('21',),\n",
    "'charter': ('22','24','25','31','50','51','60'),\n",
    "'frontier': ('22','23'), \n",
    "}\n",
    "\n",
    "ISP_TECH_DATA = {\n",
    "    'verizon': {'states': STATES, 'tech_codes': {'fcc': [10, 50], 'tool': [10, 50]}},\n",
    "    'centurylink': {'states': ['AR', 'NC'], 'tech_codes': {'fcc': [10, 50]}},\n",
    "    'att': {'states': STATES, 'tech_codes': {'fcc': [10, 50, 70], 'tool': [10, 50, 70]}},\n",
    "    'windstream': {'states': ['NC'], 'tech_codes': {'fcc': [10, 41, 50]}}\n",
    "}\n",
    "\n",
    "VALID_STATE_FIPS = {\n",
    "    5: \"AR\",\n",
    "    23: \"ME\",\n",
    "    25: \"MA\",\n",
    "    36: \"NY\",\n",
    "    37: \"NC\",\n",
    "    39: \"OH\",\n",
    "    50: \"VT\",\n",
    "    51: \"VA\",\n",
    "    55: \"WI\"\n",
    "}\n",
    "\n",
    "LABELS = {\n",
    "'att': 'AT&T',\n",
    "'centurylink': 'CenturyLink',\n",
    "'consolidated': 'Consolidated',\n",
    "'cox': 'Cox',\n",
    "'xfinity' : 'Comcast',\n",
    "'verizon' : 'Verizon',\n",
    "'windstream': 'Windstream',\n",
    "'charter': 'Charter',\n",
    "'frontier': 'Frontier',\n",
    "'VA': 'Virginia',\n",
    "'VT': 'Vermont',\n",
    "'AR': 'Arkansas',\n",
    "'MA': 'Massachusetts',\n",
    "'NC': 'North Carolina',\n",
    "'NY': 'New York',\n",
    "'ME': 'Maine',\n",
    "'OH': 'Ohio',\n",
    "'WI': 'Wisconsin',\n",
    "'U': 'Urban',\n",
    "'R': 'Rural',\n",
    "'Total': 'Total'\n",
    "}\n",
    "\n",
    "SORTED_ISPS = ['att', 'centurylink', 'charter', 'xfinity', 'consolidated', 'cox', 'frontier', 'verizon', 'windstream']"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Load FCC Form 477 Data (Unused)"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "form477_data = pd.read_csv(\"fbd_us_without_satellite_jun2018_v1.csv\", encoding='latin1')\n",
    "form477_data = form477_data[form477_data[\"Consumer\"] == 1]"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "if \"form477_isps\" in globals():\n",
    "    del form477_isps\n",
    "if \"form477_local\" in globals():\n",
    "    del form477_local\n",
    "form477_isps = defaultdict(set)\n",
    "form477_local = defaultdict(set)\n",
    "for listing in form477_data.itertuples():\n",
    "    form477_isps[listing.BlockCode].add(listing.HocoFinal)\n",
    "    if listing.HocoFinal not in major_isps_rev:\n",
    "        form477_local[listing.BlockCode].add(listing.HocoFinal)\n",
    "del form477_data"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Load Population Data"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "pop_data = pd.read_csv('us2019.csv')"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "if \"pop_dict\" in locals():\n",
    "    del pop_dict\n",
    "pop_dict = {}\n",
    "for block in pop_data.itertuples():\n",
    "    pop_dict[block.block_fips] = block.pop2018\n",
    "    \n",
    "del pop_data"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Load Urban/Rural Data"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "urban_rural_dict = {}"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "for state in ISPS_PER_STATE:\n",
    "    print(state)\n",
    "    shp = gpd.read_file(glob.glob(f\"block_class/{state}/*.shp\")[0])\n",
    "    \n",
    "    data = shp[shp.UR10 == 'R']\n",
    "    for blockno in data.GEOID10.astype(int).unique():\n",
    "        urban_rural_dict[blockno] = 'R'\n",
    "    \n",
    "    data = shp[shp.UR10 == 'U']\n",
    "    for blockno in data.GEOID10.astype(int).unique():\n",
    "        urban_rural_dict[blockno] = 'U'"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# Load Dataset"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "data_columns = {}\n",
    "\n",
    "with open('columns.csv') as f:\n",
    "    curr_state = \"\"\n",
    "    curr_columns = []\n",
    "    for line in f.read().splitlines():\n",
    "        if not line:\n",
    "            data_columns[curr_state] = curr_columns\n",
    "            curr_state = \"\"\n",
    "            curr_columns = []\n",
    "        elif len(line) == 2:\n",
    "            curr_state = line\n",
    "        else:\n",
    "            curr_columns.append(line)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "all_state_data = {}\n",
    "all_null_data = {}\n",
    "\n",
    "for state, isps in ISPS_PER_STATE.items():\n",
    "    print(state)\n",
    "    \n",
    "    null_data = []\n",
    "    \n",
    "    state_cols = data_columns[state]\n",
    "    \n",
    "    state_data = pd.read_csv(\"data/data_{}.csv\".format(state), names=state_cols, header=0)\n",
    "    state_data = state_data[(state_data['addr_dpv'].isin(['Y', 'D', 'S'])) & (state_data['addr_rdi'] == 'R')] # filter USPS DPV and RDI\n",
    "    \n",
    "    state_data[\"state_tb\"] = state\n",
    "    \n",
    "    null_data.append(state_data[state_data[\"addr_census_block\"].isna()])\n",
    "    state_data = state_data[~state_data[\"addr_census_block\"].isna()]\n",
    "    \n",
    "    null_data.append(state_data[state_data[\"fcc_coverage_LOCAL\"].isna()])\n",
    "    state_data = state_data[~state_data[\"fcc_coverage_LOCAL\"].isna()]\n",
    "    \n",
    "    state_data[\"addr_census_block\"] = state_data[\"addr_census_block\"].astype(int)\n",
    "    \n",
    "    all_state_data[state] = state_data\n",
    "    all_null_data[state] = pd.concat(null_data, ignore_index=True)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Build Data Columns"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "data_cols = {} # state -> {'isp': {fcc': [cols], 'tool', [cols], 'local': [cols]}}\n",
    "\n",
    "for state, isps in ISPS_PER_STATE.items():\n",
    "    state_dict = {}\n",
    "    for isp in isps:\n",
    "        fcc_cols = []\n",
    "        fcc_speed_cols = []\n",
    "        tool_cols = []\n",
    "        tool_speed_cols = []\n",
    "        timestamp_cols = []\n",
    "        \n",
    "        has_fcc_code = False\n",
    "        has_tool_code = False\n",
    "\n",
    "        if isp in ISP_TECH_DATA:\n",
    "            isp_tech_data = ISP_TECH_DATA[isp]\n",
    "            if state in isp_tech_data[\"states\"]:\n",
    "                codes = isp_tech_data[\"tech_codes\"]\n",
    "                if \"fcc\" in codes:\n",
    "                    has_fcc_code = True\n",
    "                    fcc_codes = codes[\"fcc\"]\n",
    "                    for fcc_code in fcc_codes:\n",
    "                        fcc_cols.append(f\"fcc_coverage_{isp}_{fcc_code}\")\n",
    "                        fcc_speed_cols.append(f\"fcc_coverage_downspeed_{isp}_{fcc_code}\")\n",
    "                    if \"tool\" in codes:\n",
    "                        has_tool_code = True\n",
    "                        tool_codes = codes[\"tool\"]\n",
    "                        for tool_code in tool_codes:\n",
    "                            tool_cols.append(f\"tool_coverage_{isp}_{tool_code}\")\n",
    "                            tool_speed_cols.append(f\"tool_coverage_downspeed_{isp}_{tool_code}\")\n",
    "                            timestamp_cols.append(f\"tool_timestamp_{isp}_{tool_code}\")\n",
    "        \n",
    "        if not has_fcc_code:\n",
    "            fcc_cols.append(f\"fcc_coverage_{isp}\")\n",
    "            fcc_speed_cols.append(f\"fcc_coverage_downspeed_{isp}\")\n",
    "        if not has_tool_code:\n",
    "            tool_cols.append(f\"tool_coverage_{isp}\")\n",
    "            tool_speed_cols.append(f\"tool_coverage_downspeed_{isp}\")\n",
    "            timestamp_cols.append(f\"tool_timestamp_{isp}\")\n",
    "            \n",
    "        state_dict[isp] = {\n",
    "            'fcc': fcc_cols,\n",
    "            'fcc_speed': fcc_speed_cols,\n",
    "            'tool': tool_cols,\n",
    "            'tool_speed': tool_speed_cols,\n",
    "            'timestamp': timestamp_cols\n",
    "        }\n",
    "        \n",
    "    if state == \"NY\":\n",
    "        local_col = \"fcc_coverage_LOCAL_altice\"\n",
    "        local_speed_col = \"fcc_coverage_downspeed_LOCAL_altice\"\n",
    "    else:\n",
    "        local_col = \"fcc_coverage_LOCAL\"\n",
    "        local_speed_col = \"fcc_coverage_downspeed_LOCAL\"\n",
    "    state_dict[\"local\"] = local_col\n",
    "    state_dict[\"local_speed\"] = local_speed_col\n",
    "        \n",
    "    data_cols[state] = state_dict\n",
    "    \n",
    "def get_cols(state, typ):\n",
    "    cols = []\n",
    "    for isp in data_cols[state].values():\n",
    "        if \"LOCAL\" not in isp:\n",
    "            cols.extend(isp[typ])\n",
    "    return cols"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Compute Table 5"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "# Table 5\n",
    "table5_dict = {0: {}, 25: {}}\n",
    "\n",
    "for speed in [0, 25]:\n",
    "    for state in ISPS_PER_STATE:\n",
    "        \n",
    "        state_dict = {}\n",
    "        table5_dict[speed][state] = state_dict\n",
    "\n",
    "        state_data = all_state_data[state]\n",
    "        state_data_cols = data_cols[state]\n",
    "\n",
    "        for address in state_data.itertuples():\n",
    "            blockno = getattr(address, \"addr_census_block\")\n",
    "\n",
    "            block_data = state_dict.get(blockno, [0, 0])\n",
    "\n",
    "            local_col = state_data_cols[\"local\"]\n",
    "            local_speed_col = state_data_cols[\"local_speed\"]\n",
    "            local_speed_data = getattr(address, local_speed_col)\n",
    "\n",
    "            has_local_ISP = False\n",
    "            if getattr(address, local_col) == 1 and local_speed_data >= speed:\n",
    "                block_data[0] += 1\n",
    "                block_data[1] += 1\n",
    "                has_local_ISP = True\n",
    "\n",
    "\n",
    "            if not has_local_ISP:\n",
    "                fcc_covered = False\n",
    "                bat_covered = False\n",
    "                bad = False # unrecognized/unknown\n",
    "\n",
    "                for isp in ISPS_PER_STATE[state]:\n",
    "                    if bat_covered:\n",
    "                        break\n",
    "\n",
    "                    isp_fcc_covered = False\n",
    "\n",
    "                    state_isp_data_cols = state_data_cols[isp]\n",
    "                    fcc_cols = state_isp_data_cols[\"fcc\"]\n",
    "                    fcc_speed_cols = state_isp_data_cols[\"fcc_speed\"]\n",
    "                    tool_cols = state_isp_data_cols[\"tool\"]\n",
    "                    tool_speed_cols = state_isp_data_cols[\"tool_speed\"]\n",
    "                    timestamp_cols = state_isp_data_cols[\"timestamp\"]\n",
    "\n",
    "                    for i, fcc_col in enumerate(fcc_cols):\n",
    "                        fcc_speed_col = fcc_speed_cols[i]\n",
    "                        if getattr(address, fcc_col) == 1 and getattr(address, fcc_speed_col) >= speed:\n",
    "                            isp_fcc_covered = True\n",
    "                            fcc_covered = True\n",
    "\n",
    "                    if not isp_fcc_covered:\n",
    "                        continue\n",
    "\n",
    "                    for i, tool_col in enumerate(tool_cols):\n",
    "                        tool_speed_col = tool_speed_cols[i]\n",
    "                        timestamp_col = timestamp_cols[i]\n",
    "                        \n",
    "                        timestamp = getattr(address, timestamp_col)\n",
    "                        if timestamp > PAPER_TIME:\n",
    "                            bad = True\n",
    "                            continue\n",
    "\n",
    "                        try:\n",
    "                            tool_data = str(int(getattr(address, tool_col))) # implicitly reports NaNs :D\n",
    "                        except:\n",
    "                            bad = True\n",
    "                            continue\n",
    "                        \n",
    "                        if tool_data in POS_RESPONSES[isp]:\n",
    "                            bat_covered = True\n",
    "                            break\n",
    "\n",
    "                        elif tool_data not in NEG_RESPONSES[isp]: # unrecognized/unknown/nan\n",
    "                            bad = True\n",
    "\n",
    "                if bat_covered or (fcc_covered and not bad):\n",
    "                    block_data[0] += 1 # FCC\n",
    "                    if bat_covered:\n",
    "                        block_data[1] += 1 # BAT\n",
    "\n",
    "            state_dict[blockno] = block_data"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "table5_results_dict = {0: {}, 25: {}}\n",
    "for speed in [0, 25]:\n",
    "    total_fcc_counts = {\n",
    "        'U': 0,\n",
    "        'R': 0\n",
    "    }\n",
    "    total_bat_counts = {\n",
    "        'U': 0,\n",
    "        'R': 0\n",
    "    }\n",
    "    total_fcc_populations = {\n",
    "        'U': 0,\n",
    "        'R': 0\n",
    "    }\n",
    "    total_bat_populations = {\n",
    "        'U': 0,\n",
    "        'R': 0\n",
    "    }\n",
    "    \n",
    "    for state in table5_dict[speed]:\n",
    "        print(state)\n",
    "\n",
    "        res = {}\n",
    "        table5_results_dict[speed][state] = res\n",
    "\n",
    "        fcc_counts = {\n",
    "            'U': 0,\n",
    "            'R': 0\n",
    "        }\n",
    "        bat_counts = {\n",
    "            'U': 0,\n",
    "            'R': 0\n",
    "        }\n",
    "        fcc_populations = {\n",
    "            'U': 0,\n",
    "            'R': 0\n",
    "        }\n",
    "        bat_populations = {\n",
    "            'U': 0,\n",
    "            'R': 0\n",
    "        }\n",
    "\n",
    "        state_data = table5_dict[speed][state]\n",
    "\n",
    "        for blockno in state_data:\n",
    "            block_data = state_data[blockno]\n",
    "            try:\n",
    "                block_class = urban_rural_dict[blockno]\n",
    "            except:\n",
    "                continue\n",
    "\n",
    "            if block_data[0] > 0:\n",
    "                fcc_populations[block_class] += pop_dict[blockno]\n",
    "                bat_populations[block_class] += pop_dict[blockno] * (block_data[1]/block_data[0])\n",
    "\n",
    "                fcc_counts[block_class] += block_data[0]\n",
    "                bat_counts[block_class] += block_data[1]\n",
    "\n",
    "        res['fcc_count_total'] = fcc_counts['R'] + fcc_counts['U']\n",
    "        res['fcc_count_U'] = fcc_counts['U']\n",
    "        res['fcc_count_R'] = fcc_counts['R']\n",
    "\n",
    "        res['bat_count_total'] = bat_counts['R'] + bat_counts['U']\n",
    "        res['bat_count_U'] = bat_counts['U']\n",
    "        res['bat_count_R'] = bat_counts['R']\n",
    "\n",
    "        res['count_per_total'] = str(100 * res['bat_count_total'] / res['fcc_count_total'])\n",
    "        res['count_per_U'] = str(100 * res['bat_count_U'] / res['fcc_count_U'])\n",
    "        res['count_per_R'] = str(100 * res['bat_count_R'] / res['fcc_count_R'])\n",
    "\n",
    "        res['fcc_pop_total'] = fcc_populations['R'] + fcc_populations['U']\n",
    "        res['fcc_pop_U'] = fcc_populations['U']\n",
    "        res['fcc_pop_R'] = fcc_populations['R']\n",
    "        \n",
    "        res['bat_pop_total'] = int(bat_populations['R'] + bat_populations['U'])\n",
    "        res['bat_pop_U'] = int(bat_populations['U'])\n",
    "        res['bat_pop_R'] = int(bat_populations['R'])\n",
    "        \n",
    "        res['pop_per_total'] = str(100 * res['bat_pop_total'] / res['fcc_pop_total'])\n",
    "        res['pop_per_U'] = str(100 * res['bat_pop_U'] / res['fcc_pop_U'])\n",
    "        res['pop_per_R'] = str(100 * res['bat_pop_R'] / res['fcc_pop_R'])\n",
    "        \n",
    "        total_fcc_counts['U'] += fcc_counts['U']\n",
    "        total_fcc_counts['R'] += fcc_counts['R']\n",
    "        total_bat_counts['U'] += bat_counts['U']\n",
    "        total_bat_counts['R'] += bat_counts['R']\n",
    "        \n",
    "        total_fcc_populations['U'] += fcc_populations['U']\n",
    "        total_fcc_populations['R'] += fcc_populations['R']\n",
    "        total_bat_populations['U'] += bat_populations['U']\n",
    "        total_bat_populations['R'] += bat_populations['R']\n",
    "    \n",
    "    total_res = {}\n",
    "    table5_results_dict[speed][\"Total\"] = total_res\n",
    "    \n",
    "    total_res['fcc_count_total'] = total_fcc_counts['R'] + total_fcc_counts['U']\n",
    "    total_res['fcc_count_U'] = total_fcc_counts['U']\n",
    "    total_res['fcc_count_R'] = total_fcc_counts['R']\n",
    "\n",
    "    total_res['bat_count_total'] = total_bat_counts['R'] + total_bat_counts['U']\n",
    "    total_res['bat_count_U'] = total_bat_counts['U']\n",
    "    total_res['bat_count_R'] = total_bat_counts['R']\n",
    "\n",
    "    total_res['count_per_total'] = str(100 * total_res['bat_count_total'] / total_res['fcc_count_total'])\n",
    "    total_res['count_per_U'] = str(100 * total_res['bat_count_U'] / total_res['fcc_count_U'])\n",
    "    total_res['count_per_R'] = str(100 * total_res['bat_count_R'] / total_res['fcc_count_R'])\n",
    "\n",
    "    total_res['fcc_pop_total'] = total_fcc_populations['R'] + total_fcc_populations['U']\n",
    "    total_res['fcc_pop_U'] = total_fcc_populations['U']\n",
    "    total_res['fcc_pop_R'] = total_fcc_populations['R']\n",
    "\n",
    "    total_res['bat_pop_total'] = int(total_bat_populations['R'] + total_bat_populations['U'])\n",
    "    total_res['bat_pop_U'] = int(total_bat_populations['U'])\n",
    "    total_res['bat_pop_R'] = int(total_bat_populations['R'])\n",
    "\n",
    "    total_res['pop_per_total'] = str(100 * total_res['bat_pop_total'] / total_res['fcc_pop_total'])\n",
    "    total_res['pop_per_U'] = str(100 * total_res['bat_pop_U'] / total_res['fcc_pop_U'])\n",
    "    total_res['pop_per_R'] = str(100 * total_res['bat_pop_R'] / total_res['fcc_pop_R'])"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Compute Table 11 (S1)"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "# Table 11 (S1)\n",
    "s1_states_dict = {0: {}, 25: {}}\n",
    "\n",
    "for speed in [0, 25]:\n",
    "    for state in ISPS_PER_STATE:\n",
    "        \n",
    "        state_dict = {}\n",
    "        s1_states_dict[speed][state] = state_dict\n",
    "\n",
    "        state_data = all_state_data[state]\n",
    "        state_data_cols = data_cols[state]\n",
    "\n",
    "        for address in state_data.itertuples():\n",
    "            blockno = getattr(address, \"addr_census_block\")\n",
    "\n",
    "            block_data = state_dict.get(blockno, [0, 0])\n",
    "\n",
    "            local_col = state_data_cols[\"local\"]\n",
    "            local_speed_col = state_data_cols[\"local_speed\"]\n",
    "            local_speed_data = getattr(address, local_speed_col)\n",
    "\n",
    "            has_local_ISP = False\n",
    "            if getattr(address, local_col) == 1 and local_speed_data >= speed:\n",
    "                block_data[0] += 1\n",
    "                block_data[1] += 1\n",
    "                has_local_ISP = True\n",
    "\n",
    "\n",
    "            if not has_local_ISP:\n",
    "                fcc_covered = False\n",
    "                bat_covered = False\n",
    "                bat_uncovered = False\n",
    "                bad = False # unrecognized/unknown\n",
    "\n",
    "                for isp in ISPS_PER_STATE[state]:\n",
    "                    if bat_covered:\n",
    "                        break\n",
    "\n",
    "                    isp_fcc_covered = False\n",
    "\n",
    "                    state_isp_data_cols = state_data_cols[isp]\n",
    "                    fcc_cols = state_isp_data_cols[\"fcc\"]\n",
    "                    fcc_speed_cols = state_isp_data_cols[\"fcc_speed\"]\n",
    "                    tool_cols = state_isp_data_cols[\"tool\"]\n",
    "                    tool_speed_cols = state_isp_data_cols[\"tool_speed\"]\n",
    "                    timestamp_cols = state_isp_data_cols[\"timestamp\"]\n",
    "\n",
    "                    for i, fcc_col in enumerate(fcc_cols):\n",
    "                        fcc_speed_col = fcc_speed_cols[i]\n",
    "                        if getattr(address, fcc_col) == 1 and getattr(address, fcc_speed_col) >= speed:\n",
    "                            isp_fcc_covered = True\n",
    "                            fcc_covered = True\n",
    "\n",
    "                    if not isp_fcc_covered:\n",
    "                        continue\n",
    "\n",
    "                    for i, tool_col in enumerate(tool_cols):\n",
    "                        try:\n",
    "                            tool_data = str(int(getattr(address, tool_col)))\n",
    "                        except:\n",
    "                            bad = True\n",
    "                            continue\n",
    "\n",
    "                        if tool_data in POS_RESPONSES[isp]: # covered by ISP (BAT)\n",
    "                            bat_covered = True\n",
    "                            break\n",
    "\n",
    "                        elif tool_data in NEG_RESPONSES[isp]: # unrecognized/unknown/nan\n",
    "                            bat_uncovered = True\n",
    "\n",
    "                        elif tool_data not in UNRECOGNIZED_RESPONSES[isp]:\n",
    "                            bad = True\n",
    "\n",
    "                if bat_covered or (fcc_covered and bat_uncovered and not bad):\n",
    "                    block_data[0] += 1 # FCC\n",
    "                    if bat_covered:\n",
    "                        block_data[1] += 1 # BAT\n",
    "\n",
    "            state_dict[blockno] = block_data"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "s1_states_results_dict = {0: {}, 25: {}}\n",
    "for speed in [0, 25]:\n",
    "    total_fcc_counts = {\n",
    "        'U': 0,\n",
    "        'R': 0\n",
    "    }\n",
    "    total_bat_counts = {\n",
    "        'U': 0,\n",
    "        'R': 0\n",
    "    }\n",
    "    total_fcc_populations = {\n",
    "        'U': 0,\n",
    "        'R': 0\n",
    "    }\n",
    "    total_bat_populations = {\n",
    "        'U': 0,\n",
    "        'R': 0\n",
    "    }\n",
    "    for state in s1_states_dict[speed]:\n",
    "        print(state)\n",
    "\n",
    "        res = {}\n",
    "        s1_states_results_dict[speed][state] = res\n",
    "\n",
    "        fcc_counts = {\n",
    "            'U': 0,\n",
    "            'R': 0\n",
    "        }\n",
    "        bat_counts = {\n",
    "            'U': 0,\n",
    "            'R': 0\n",
    "        }\n",
    "        fcc_populations = {\n",
    "            'U': 0,\n",
    "            'R': 0\n",
    "        }\n",
    "        bat_populations = {\n",
    "            'U': 0,\n",
    "            'R': 0\n",
    "        }\n",
    "\n",
    "        state_data = s1_states_dict[speed][state]\n",
    "\n",
    "        for blockno in state_data:\n",
    "            block_data = state_data[blockno]\n",
    "            try:\n",
    "                block_class = urban_rural_dict[blockno]\n",
    "            except:\n",
    "                continue\n",
    "\n",
    "            if block_data[0] > 0:\n",
    "                fcc_populations[block_class] += pop_dict[blockno]\n",
    "                bat_populations[block_class] += pop_dict[blockno] * (block_data[1]/block_data[0])\n",
    "\n",
    "                fcc_counts[block_class] += block_data[0]\n",
    "                bat_counts[block_class] += block_data[1]\n",
    "\n",
    "        res['fcc_count_total'] = fcc_counts['R'] + fcc_counts['U']\n",
    "        res['fcc_count_U'] = fcc_counts['U']\n",
    "        res['fcc_count_R'] = fcc_counts['R']\n",
    "\n",
    "        res['bat_count_total'] = bat_counts['R'] + bat_counts['U']\n",
    "        res['bat_count_U'] = bat_counts['U']\n",
    "        res['bat_count_R'] = bat_counts['R']\n",
    "\n",
    "        res['count_per_total'] = str(100 * res['bat_count_total'] / res['fcc_count_total'])\n",
    "        res['count_per_U'] = str(100 * res['bat_count_U'] / res['fcc_count_U'])\n",
    "        res['count_per_R'] = str(100 * res['bat_count_R'] / res['fcc_count_R'])\n",
    "\n",
    "        res['fcc_pop_total'] = fcc_populations['R'] + fcc_populations['U']\n",
    "        res['fcc_pop_U'] = fcc_populations['U']\n",
    "        res['fcc_pop_R'] = fcc_populations['R']\n",
    "        \n",
    "        res['bat_pop_total'] = int(bat_populations['R'] + bat_populations['U'])\n",
    "        res['bat_pop_U'] = int(bat_populations['U'])\n",
    "        res['bat_pop_R'] = int(bat_populations['R'])\n",
    "        \n",
    "        res['pop_per_total'] = str(100 * res['bat_pop_total'] / res['fcc_pop_total'])\n",
    "        res['pop_per_U'] = str(100 * res['bat_pop_U'] / res['fcc_pop_U'])\n",
    "        res['pop_per_R'] = str(100 * res['bat_pop_R'] / res['fcc_pop_R'])\n",
    "        \n",
    "        total_fcc_counts['U'] += fcc_counts['U']\n",
    "        total_fcc_counts['R'] += fcc_counts['R']\n",
    "        total_bat_counts['U'] += bat_counts['U']\n",
    "        total_bat_counts['R'] += bat_counts['R']\n",
    "        \n",
    "        total_fcc_populations['U'] += fcc_populations['U']\n",
    "        total_fcc_populations['R'] += fcc_populations['R']\n",
    "        total_bat_populations['U'] += bat_populations['U']\n",
    "        total_bat_populations['R'] += bat_populations['R']\n",
    "    \n",
    "    total_res = {}\n",
    "    s1_states_results_dict[speed][\"Total\"] = total_res\n",
    "    \n",
    "    total_res['fcc_count_total'] = total_fcc_counts['R'] + total_fcc_counts['U']\n",
    "    total_res['fcc_count_U'] = total_fcc_counts['U']\n",
    "    total_res['fcc_count_R'] = total_fcc_counts['R']\n",
    "\n",
    "    total_res['bat_count_total'] = total_bat_counts['R'] + total_bat_counts['U']\n",
    "    total_res['bat_count_U'] = total_bat_counts['U']\n",
    "    total_res['bat_count_R'] = total_bat_counts['R']\n",
    "\n",
    "    total_res['count_per_total'] = str(100 * total_res['bat_count_total'] / total_res['fcc_count_total'])\n",
    "    total_res['count_per_U'] = str(100 * total_res['bat_count_U'] / total_res['fcc_count_U'])\n",
    "    total_res['count_per_R'] = str(100 * total_res['bat_count_R'] / total_res['fcc_count_R'])\n",
    "\n",
    "    total_res['fcc_pop_total'] = total_fcc_populations['R'] + total_fcc_populations['U']\n",
    "    total_res['fcc_pop_U'] = total_fcc_populations['U']\n",
    "    total_res['fcc_pop_R'] = total_fcc_populations['R']\n",
    "\n",
    "    total_res['bat_pop_total'] = int(total_bat_populations['R'] + total_bat_populations['U'])\n",
    "    total_res['bat_pop_U'] = int(total_bat_populations['U'])\n",
    "    total_res['bat_pop_R'] = int(total_bat_populations['R'])\n",
    "\n",
    "    total_res['pop_per_total'] = str(100 * total_res['bat_pop_total'] / total_res['fcc_pop_total'])\n",
    "    total_res['pop_per_U'] = str(100 * total_res['bat_pop_U'] / total_res['fcc_pop_U'])\n",
    "    total_res['pop_per_R'] = str(100 * total_res['bat_pop_R'] / total_res['fcc_pop_R'])"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Compute Table 12 (S2)"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "# Table 12 (S2)\n",
    "s2_states_dict = {0: {}, 25: {}}\n",
    "\n",
    "for speed in [0, 25]:\n",
    "    for state in ISPS_PER_STATE:\n",
    "\n",
    "        state_dict = {}\n",
    "        s2_states_dict[speed][state] = state_dict\n",
    "\n",
    "        state_data = all_state_data[state]\n",
    "        state_data_cols = data_cols[state]\n",
    "\n",
    "        for address in state_data.itertuples():\n",
    "            blockno = getattr(address, \"addr_census_block\")\n",
    "\n",
    "            block_data = state_dict.get(blockno, [0, 0])\n",
    "\n",
    "            local_col = state_data_cols[\"local\"]\n",
    "            local_speed_col = state_data_cols[\"local_speed\"]\n",
    "            local_speed_data = getattr(address, local_speed_col)\n",
    "\n",
    "            has_local_ISP = False\n",
    "            if getattr(address, local_col) == 1 and local_speed_data >= speed:\n",
    "                block_data[0] += 1\n",
    "                block_data[1] += 1\n",
    "                has_local_ISP = True\n",
    "\n",
    "\n",
    "            if not has_local_ISP:\n",
    "                fcc_covered = False\n",
    "                bat_covered = False\n",
    "                bad = False\n",
    "\n",
    "                for isp in ISPS_PER_STATE[state]:\n",
    "                    if bat_covered:\n",
    "                        break\n",
    "\n",
    "                    isp_fcc_covered = False\n",
    "\n",
    "                    state_isp_data_cols = state_data_cols[isp]\n",
    "                    fcc_cols = state_isp_data_cols[\"fcc\"]\n",
    "                    fcc_speed_cols = state_isp_data_cols[\"fcc_speed\"]\n",
    "                    tool_cols = state_isp_data_cols[\"tool\"]\n",
    "                    tool_speed_cols = state_isp_data_cols[\"tool_speed\"]\n",
    "                    timestamp_cols = state_isp_data_cols[\"timestamp\"]\n",
    "\n",
    "                    for i, fcc_col in enumerate(fcc_cols):\n",
    "                        fcc_speed_col = fcc_speed_cols[i]\n",
    "                        if getattr(address, fcc_col) == 1 and getattr(address, fcc_speed_col) >= speed:\n",
    "                            isp_fcc_covered = True\n",
    "                            fcc_covered = True\n",
    "\n",
    "                    if not isp_fcc_covered:\n",
    "                        continue\n",
    "\n",
    "                    for i, tool_col in enumerate(tool_cols):\n",
    "                        try:\n",
    "                            tool_data = str(int(getattr(address, tool_col)))\n",
    "                        except:\n",
    "                            bad = True\n",
    "                            break\n",
    "\n",
    "                        if tool_data in POS_RESPONSES[isp]: # covered by ISP (BAT)\n",
    "                            bat_covered = True\n",
    "                            break\n",
    "\n",
    "                        elif tool_data not in NEG_RESPONSES[isp] + UNRECOGNIZED_RESPONSES[isp] + BUSINESS_RESPONSES[isp] + UNKNOWN_RESPONSES[isp]:\n",
    "                            bad = True\n",
    "\n",
    "                if bat_covered or (fcc_covered and not bad):\n",
    "                    block_data[0] += 1 # FCC\n",
    "                    if bat_covered:\n",
    "                        block_data[1] += 1 # BAT\n",
    "\n",
    "            state_dict[blockno] = block_data"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "s2_states_results_dict = {0: {}, 25: {}}\n",
    "for speed in [0, 25]:\n",
    "    total_fcc_counts = {\n",
    "        'U': 0,\n",
    "        'R': 0\n",
    "    }\n",
    "    total_bat_counts = {\n",
    "        'U': 0,\n",
    "        'R': 0\n",
    "    }\n",
    "    total_fcc_populations = {\n",
    "        'U': 0,\n",
    "        'R': 0\n",
    "    }\n",
    "    total_bat_populations = {\n",
    "        'U': 0,\n",
    "        'R': 0\n",
    "    }\n",
    "    for state in s2_states_dict[speed]:\n",
    "        print(state)\n",
    "\n",
    "        res = {}\n",
    "        s2_states_results_dict[speed][state] = res\n",
    "\n",
    "        fcc_counts = {\n",
    "            'U': 0,\n",
    "            'R': 0\n",
    "        }\n",
    "        bat_counts = {\n",
    "            'U': 0,\n",
    "            'R': 0\n",
    "        }\n",
    "        fcc_populations = {\n",
    "            'U': 0,\n",
    "            'R': 0\n",
    "        }\n",
    "        bat_populations = {\n",
    "            'U': 0,\n",
    "            'R': 0\n",
    "        }\n",
    "\n",
    "        state_data = s2_states_dict[speed][state]\n",
    "\n",
    "        for blockno in state_data:\n",
    "            block_data = state_data[blockno]\n",
    "            try:\n",
    "                block_class = urban_rural_dict[blockno]\n",
    "            except:\n",
    "                continue\n",
    "\n",
    "            if block_data[0] > 0:\n",
    "                fcc_populations[block_class] += pop_dict[blockno]\n",
    "                bat_populations[block_class] += pop_dict[blockno] * (block_data[1]/block_data[0])\n",
    "\n",
    "                fcc_counts[block_class] += block_data[0]\n",
    "                bat_counts[block_class] += block_data[1]\n",
    "\n",
    "        res['fcc_count_total'] = fcc_counts['R'] + fcc_counts['U']\n",
    "        res['fcc_count_U'] = fcc_counts['U']\n",
    "        res['fcc_count_R'] = fcc_counts['R']\n",
    "\n",
    "        res['bat_count_total'] = bat_counts['R'] + bat_counts['U']\n",
    "        res['bat_count_U'] = bat_counts['U']\n",
    "        res['bat_count_R'] = bat_counts['R']\n",
    "\n",
    "        res['count_per_total'] = str(100 * res['bat_count_total'] / res['fcc_count_total'])\n",
    "        res['count_per_U'] = str(100 * res['bat_count_U'] / res['fcc_count_U'])\n",
    "        res['count_per_R'] = str(100 * res['bat_count_R'] / res['fcc_count_R'])\n",
    "\n",
    "        res['fcc_pop_total'] = fcc_populations['R'] + fcc_populations['U']\n",
    "        res['fcc_pop_U'] = fcc_populations['U']\n",
    "        res['fcc_pop_R'] = fcc_populations['R']\n",
    "        \n",
    "        res['bat_pop_total'] = int(bat_populations['R'] + bat_populations['U'])\n",
    "        res['bat_pop_U'] = int(bat_populations['U'])\n",
    "        res['bat_pop_R'] = int(bat_populations['R'])\n",
    "        \n",
    "        res['pop_per_total'] = str(100 * res['bat_pop_total'] / res['fcc_pop_total'])\n",
    "        res['pop_per_U'] = str(100 * res['bat_pop_U'] / res['fcc_pop_U'])\n",
    "        res['pop_per_R'] = str(100 * res['bat_pop_R'] / res['fcc_pop_R'])\n",
    "    \n",
    "        total_fcc_counts['U'] += fcc_counts['U']\n",
    "        total_fcc_counts['R'] += fcc_counts['R']\n",
    "        total_bat_counts['U'] += bat_counts['U']\n",
    "        total_bat_counts['R'] += bat_counts['R']\n",
    "        \n",
    "        total_fcc_populations['U'] += fcc_populations['U']\n",
    "        total_fcc_populations['R'] += fcc_populations['R']\n",
    "        total_bat_populations['U'] += bat_populations['U']\n",
    "        total_bat_populations['R'] += bat_populations['R']\n",
    "    \n",
    "    total_res = {}\n",
    "    s2_states_results_dict[speed][\"Total\"] = total_res\n",
    "    \n",
    "    total_res['fcc_count_total'] = total_fcc_counts['R'] + total_fcc_counts['U']\n",
    "    total_res['fcc_count_U'] = total_fcc_counts['U']\n",
    "    total_res['fcc_count_R'] = total_fcc_counts['R']\n",
    "\n",
    "    total_res['bat_count_total'] = total_bat_counts['R'] + total_bat_counts['U']\n",
    "    total_res['bat_count_U'] = total_bat_counts['U']\n",
    "    total_res['bat_count_R'] = total_bat_counts['R']\n",
    "\n",
    "    total_res['count_per_total'] = str(100 * total_res['bat_count_total'] / total_res['fcc_count_total'])\n",
    "    total_res['count_per_U'] = str(100 * total_res['bat_count_U'] / total_res['fcc_count_U'])\n",
    "    total_res['count_per_R'] = str(100 * total_res['bat_count_R'] / total_res['fcc_count_R'])\n",
    "\n",
    "    total_res['fcc_pop_total'] = total_fcc_populations['R'] + total_fcc_populations['U']\n",
    "    total_res['fcc_pop_U'] = total_fcc_populations['U']\n",
    "    total_res['fcc_pop_R'] = total_fcc_populations['R']\n",
    "\n",
    "    total_res['bat_pop_total'] = int(total_bat_populations['R'] + total_bat_populations['U'])\n",
    "    total_res['bat_pop_U'] = int(total_bat_populations['U'])\n",
    "    total_res['bat_pop_R'] = int(total_bat_populations['R'])\n",
    "\n",
    "    total_res['pop_per_total'] = str(100 * total_res['bat_pop_total'] / total_res['fcc_pop_total'])\n",
    "    total_res['pop_per_U'] = str(100 * total_res['bat_pop_U'] / total_res['fcc_pop_U'])\n",
    "    total_res['pop_per_R'] = str(100 * total_res['bat_pop_R'] / total_res['fcc_pop_R'])"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Compute Table 13 (S3)"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "# Table 13 (S3)\n",
    "s3_states_dict = {0: {}, 25: {}}\n",
    "\n",
    "for speed in [0, 25]:\n",
    "    for state in ISPS_PER_STATE:\n",
    "        state_dict = {}\n",
    "        s3_states_dict[speed][state] = state_dict\n",
    "\n",
    "        state_data = all_state_data[state]\n",
    "        state_data_cols = data_cols[state]\n",
    "\n",
    "        for address in state_data.itertuples():\n",
    "            blockno = getattr(address, \"addr_census_block\")\n",
    "\n",
    "            block_data = state_dict.get(blockno, [0, 0])\n",
    "\n",
    "            local_col = state_data_cols[\"local\"]\n",
    "            local_speed_col = state_data_cols[\"local_speed\"]\n",
    "            local_speed_data = getattr(address, local_speed_col)\n",
    "\n",
    "            fcc_covered = False\n",
    "            bat_covered = False\n",
    "            bad = False\n",
    "\n",
    "            for isp in ISPS_PER_STATE[state]:\n",
    "                if bat_covered:\n",
    "                    break\n",
    "                    \n",
    "                state_isp_data_cols = state_data_cols[isp]\n",
    "                fcc_cols = state_isp_data_cols[\"fcc\"]\n",
    "                fcc_speed_cols = state_isp_data_cols[\"fcc_speed\"]\n",
    "                tool_cols = state_isp_data_cols[\"tool\"]\n",
    "                tool_speed_cols = state_isp_data_cols[\"tool_speed\"]\n",
    "                timestamp_cols = state_isp_data_cols[\"timestamp\"]\n",
    "\n",
    "                isp_fcc_covered = False\n",
    "\n",
    "                for i, fcc_col in enumerate(fcc_cols):\n",
    "                    fcc_speed_col = fcc_speed_cols[i]\n",
    "                    if getattr(address, fcc_col) == 1 and getattr(address, fcc_speed_col) >= speed:\n",
    "                        isp_fcc_covered = True\n",
    "                        fcc_covered = True\n",
    "                        break\n",
    "\n",
    "                if not isp_fcc_covered:\n",
    "                    continue\n",
    "\n",
    "                for i, tool_col in enumerate(tool_cols):\n",
    "                    timestamp_col = timestamp_cols[i]\n",
    "                    timestamp = getattr(address, timestamp_col) \n",
    "\n",
    "                    if timestamp > PAPER_TIME:\n",
    "                        bad = True\n",
    "                        continue\n",
    "\n",
    "                    try:\n",
    "                        tool_data = str(int(getattr(address, tool_col)))\n",
    "                    except:\n",
    "                        bad = True\n",
    "                        break\n",
    "\n",
    "                    if tool_data in POS_RESPONSES[isp]: # covered by ISP (BAT)\n",
    "                        bat_covered = True\n",
    "                        break\n",
    "\n",
    "                    elif tool_data not in NEG_RESPONSES[isp]:\n",
    "                        bad = True\n",
    "\n",
    "            if bat_covered or (fcc_covered and not bad):\n",
    "                block_data[0] += 1 # FCC\n",
    "                if has_local_ISP or bat_covered:\n",
    "                    block_data[1] += 1 # BAT\n",
    "\n",
    "            state_dict[blockno] = block_data"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "s3_states_results_dict = {0: {}, 25: {}}\n",
    "for speed in [0, 25]:\n",
    "    total_fcc_counts = {\n",
    "        'U': 0,\n",
    "        'R': 0\n",
    "    }\n",
    "    total_bat_counts = {\n",
    "        'U': 0,\n",
    "        'R': 0\n",
    "    }\n",
    "    total_fcc_populations = {\n",
    "        'U': 0,\n",
    "        'R': 0\n",
    "    }\n",
    "    total_bat_populations = {\n",
    "        'U': 0,\n",
    "        'R': 0\n",
    "    }\n",
    "    for state in s3_states_dict[speed]:\n",
    "        print(state)\n",
    "\n",
    "        res = {}\n",
    "        s3_states_results_dict[speed][state] = res\n",
    "\n",
    "        fcc_counts = {\n",
    "            'U': 0,\n",
    "            'R': 0\n",
    "        }\n",
    "        bat_counts = {\n",
    "            'U': 0,\n",
    "            'R': 0\n",
    "        }\n",
    "        fcc_populations = {\n",
    "            'U': 0,\n",
    "            'R': 0\n",
    "        }\n",
    "        bat_populations = {\n",
    "            'U': 0,\n",
    "            'R': 0\n",
    "        }\n",
    "\n",
    "        state_data = s3_states_dict[speed][state]\n",
    "\n",
    "        for blockno in state_data:\n",
    "            block_data = state_data[blockno]\n",
    "            try:\n",
    "                block_class = urban_rural_dict[blockno]\n",
    "            except:\n",
    "                continue\n",
    "\n",
    "            if block_data[0] > 0:\n",
    "                fcc_populations[block_class] += pop_dict[blockno]\n",
    "                bat_populations[block_class] += pop_dict[blockno] * (block_data[1]/block_data[0])\n",
    "                \n",
    "                fcc_counts[block_class] += block_data[0]\n",
    "                bat_counts[block_class] += block_data[1]\n",
    "\n",
    "        res['fcc_count_total'] = fcc_counts['R'] + fcc_counts['U']\n",
    "        res['fcc_count_U'] = fcc_counts['U']\n",
    "        res['fcc_count_R'] = fcc_counts['R']\n",
    "\n",
    "        res['bat_count_total'] = bat_counts['R'] + bat_counts['U']\n",
    "        res['bat_count_U'] = bat_counts['U']\n",
    "        res['bat_count_R'] = bat_counts['R']\n",
    "\n",
    "        res['count_per_total'] = str(100 * res['bat_count_total'] / res['fcc_count_total'])\n",
    "        res['count_per_U'] = str(100 * res['bat_count_U'] / res['fcc_count_U'])\n",
    "        res['count_per_R'] = str(100 * res['bat_count_R'] / res['fcc_count_R'])\n",
    "\n",
    "        res['fcc_pop_total'] = fcc_populations['R'] + fcc_populations['U']\n",
    "        res['fcc_pop_U'] = fcc_populations['U']\n",
    "        res['fcc_pop_R'] = fcc_populations['R']\n",
    "        \n",
    "        res['bat_pop_total'] = int(bat_populations['R'] + bat_populations['U'])\n",
    "        res['bat_pop_U'] = int(bat_populations['U'])\n",
    "        res['bat_pop_R'] = int(bat_populations['R'])\n",
    "        \n",
    "        res['pop_per_total'] = str(100 * res['bat_pop_total'] / res['fcc_pop_total'])\n",
    "        res['pop_per_U'] = str(100 * res['bat_pop_U'] / res['fcc_pop_U'])\n",
    "        res['pop_per_R'] = str(100 * res['bat_pop_R'] / res['fcc_pop_R'])\n",
    "        \n",
    "        total_fcc_counts['U'] += fcc_counts['U']\n",
    "        total_fcc_counts['R'] += fcc_counts['R']\n",
    "        total_bat_counts['U'] += bat_counts['U']\n",
    "        total_bat_counts['R'] += bat_counts['R']\n",
    "        \n",
    "        total_fcc_populations['U'] += fcc_populations['U']\n",
    "        total_fcc_populations['R'] += fcc_populations['R']\n",
    "        total_bat_populations['U'] += bat_populations['U']\n",
    "        total_bat_populations['R'] += bat_populations['R']\n",
    "    \n",
    "    total_res = {}\n",
    "    s3_states_results_dict[speed][\"Total\"] = total_res\n",
    "    \n",
    "    total_res['fcc_count_total'] = total_fcc_counts['R'] + total_fcc_counts['U']\n",
    "    total_res['fcc_count_U'] = total_fcc_counts['U']\n",
    "    total_res['fcc_count_R'] = total_fcc_counts['R']\n",
    "\n",
    "    total_res['bat_count_total'] = total_bat_counts['R'] + total_bat_counts['U']\n",
    "    total_res['bat_count_U'] = total_bat_counts['U']\n",
    "    total_res['bat_count_R'] = total_bat_counts['R']\n",
    "\n",
    "    total_res['count_per_total'] = str(100 * total_res['bat_count_total'] / total_res['fcc_count_total'])\n",
    "    total_res['count_per_U'] = str(100 * total_res['bat_count_U'] / total_res['fcc_count_U'])\n",
    "    total_res['count_per_R'] = str(100 * total_res['bat_count_R'] / total_res['fcc_count_R'])\n",
    "\n",
    "    total_res['fcc_pop_total'] = total_fcc_populations['R'] + total_fcc_populations['U']\n",
    "    total_res['fcc_pop_U'] = total_fcc_populations['U']\n",
    "    total_res['fcc_pop_R'] = total_fcc_populations['R']\n",
    "\n",
    "    total_res['bat_pop_total'] = int(total_bat_populations['R'] + total_bat_populations['U'])\n",
    "    total_res['bat_pop_U'] = int(total_bat_populations['U'])\n",
    "    total_res['bat_pop_R'] = int(total_bat_populations['R'])\n",
    "\n",
    "    total_res['pop_per_total'] = str(100 * total_res['bat_pop_total'] / total_res['fcc_pop_total'])\n",
    "    total_res['pop_per_U'] = str(100 * total_res['bat_pop_U'] / total_res['fcc_pop_U'])\n",
    "    total_res['pop_per_R'] = str(100 * total_res['bat_pop_R'] / total_res['fcc_pop_R'])"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Print Tables as LaTeX"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "for state in STATES + [\"Total\"]:\n",
    "    speed_0 = table5_dict[0][state] # SET TABLE (table5_dict, s1_states_results_dict, s2_states_results_dict, s3_states_results_dict)\n",
    "    speed_25 = table5_dict[25][state] # SET TABLE (table5_dict, s1_states_results_dict, s2_states_results_dict, s3_states_results_dict)\n",
    "    \n",
    "    line1 = [\n",
    "        speed_0['fcc_count_total'], speed_0['bat_count_total'], speed_0['count_per_total'] , r\"\\%\",\n",
    "        speed_25['fcc_count_total'], speed_25['bat_count_total'], speed_25['count_per_total'] , r\"\\%\",\n",
    "        speed_0['fcc_pop_total'], speed_0['bat_pop_total'], speed_0['pop_per_total'] , r\"\\%\",\n",
    "        speed_25['fcc_pop_total'], speed_25['bat_pop_total'], speed_25['pop_per_total'] , r\"\\%\"\n",
    "    ]\n",
    "    line2 = [\n",
    "        speed_0['fcc_count_U'], speed_0['bat_count_U'], speed_0['count_per_U'] , r\"\\%\",\n",
    "        speed_25['fcc_count_U'], speed_25['bat_count_U'], speed_25['count_per_U'] , r\"\\%\",\n",
    "        speed_0['fcc_pop_U'], speed_0['bat_pop_U'], speed_0['pop_per_U'] , r\"\\%\",\n",
    "        speed_25['fcc_pop_U'], speed_25['bat_pop_U'], speed_25['pop_per_U'] , r\"\\%\"\n",
    "    ]\n",
    "    line3 = [\n",
    "        speed_0['fcc_count_R'], speed_0['bat_count_R'], speed_0['count_per_R'] , r\"\\%\",\n",
    "        speed_25['fcc_count_R'], speed_25['bat_count_R'], speed_25['count_per_R'] , r\"\\%\",\n",
    "        speed_0['fcc_pop_R'], speed_0['bat_pop_R'], speed_0['pop_per_R'] , r\"\\%\",\n",
    "        speed_25['fcc_pop_R'], speed_25['bat_pop_R'], speed_25['pop_per_R'] , r\"\\%\"\n",
    "    ]\n",
    "    \n",
    "    line1_str = r\"\\rowcolor[HTML]{C0C0C0}\" + LABELS[state]\n",
    "    line1_str += \" & All & \" + \" & \".join([\"{:,.2f}\".format(float(x)) if i % 4 != 3 else x for i, x in enumerate(line1)]).replace(\".00\", \"\").replace(r\" & \\%\", r\"\\%\") + r\" \\\\\"\n",
    "    \n",
    "    line2_str = \" & Urban & \" + \" & \".join([\"{:,.2f}\".format(float(x)) if i % 4 != 3 else x for i, x in enumerate(line2)]).replace(\".00\", \"\").replace(r\" & \\%\", r\"\\%\") + r\" \\\\\"\n",
    "    \n",
    "    line3_str = \" & Rural & \" + \" & \".join([\"{:,.2f}\".format(float(x)) if i % 4 != 3 else x for i, x in enumerate(line3)]).replace(\".00\", \"\").replace(r\" & \\%\", r\"\\%\") + r\" \\\\\"\n",
    "    \n",
    "    print(line1_str)\n",
    "    print(line2_str)\n",
    "    print(line3_str)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# Figures/Misc Tables"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Figure 3: CDF"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "all_isp_data = defaultdict(lambda: defaultdict(lambda: Counter()))\n",
    "\n",
    "for state in all_state_data:\n",
    "    print(state)\n",
    "    state_data = all_state_data[state]\n",
    "\n",
    "    for isp in ISPS_PER_STATE[state]:\n",
    "        state_isp_data_cols = data_cols[state][isp]\n",
    "        \n",
    "        fcc_cols = state_isp_data_cols[\"fcc\"]\n",
    "        tool_cols = state_isp_data_cols[\"tool\"]\n",
    "        \n",
    "        for address in state_data.itertuples():\n",
    "            blockno = address.addr_census_block\n",
    "            \n",
    "            fcc_covered = False\n",
    "            for fcc_col in fcc_cols:\n",
    "                if getattr(address, fcc_col) == 1:\n",
    "                    all_isp_data[isp][blockno]['fcc'] += 1\n",
    "                    fcc_covered = True\n",
    "                    break\n",
    "                    \n",
    "            if not fcc_covered:\n",
    "                continue\n",
    "            \n",
    "            bat_covered = False\n",
    "            bad = False\n",
    "                \n",
    "            for tool_col in tool_cols:\n",
    "                try:\n",
    "                    tool_data = str(int(getattr(address, tool_col)))\n",
    "                except:\n",
    "                    continue\n",
    "                if tool_data in POS_RESPONSES[isp]:\n",
    "                    bat_covered = True\n",
    "                    all_isp_data[isp][blockno]['tool_pos'] += 1\n",
    "                    break\n",
    "                elif tool_data not in NEG_RESPONSES[isp]:\n",
    "                    bad = True\n",
    "            \n",
    "            if bat_covered or not bad:\n",
    "                all_isp_data[isp][blockno]['tool_neg'] += 1"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "fig, axes = plt.subplots()\n",
    "handles = []\n",
    "\n",
    "for isp, per_isp_data in sorted(all_isp_data.items(), key=lambda x: SORTED_ISPS.index(x[0])):\n",
    "    ratios = []\n",
    "    \n",
    "    for block, per_isp_block_data in per_isp_data.items():\n",
    "        fcc = per_isp_block_data[\"fcc\"]\n",
    "        pos = per_isp_block_data[\"tool_pos\"]\n",
    "        neg = per_isp_block_data[\"tool_neg\"]\n",
    "        if pos+neg > 0:\n",
    "            ratios.append(pos / neg)\n",
    "\n",
    "    sorted_ratios = sorted(ratios)\n",
    "                      \n",
    "    x_axis = np.arange(1, len(sorted_ratios)+1) / len(sorted_ratios)\n",
    "    \n",
    "    line, = axes.step(x_axis, sorted_ratios, label=LABELS[isp])\n",
    "#     handles.append(line)\n",
    "    \n",
    "plt.legend()\n",
    "plt.ylabel(\"Percentage of Block with Coverage\")\n",
    "plt.xlabel(\"Proportion of Blocks\")\n",
    "axes.grid(False)\n",
    "vals = axes.get_yticks()\n",
    "axes.set_yticklabels(['{:,.0%}'.format(x) for x in vals])\n",
    "fig.tight_layout()\n",
    "plt.savefig(\"coverage_distribution.pdf\")"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Table 3"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "tab3_data_0 = defaultdict(Counter)\n",
    "tab3_data_25 = defaultdict(Counter)\n",
    "\n",
    "for speed in [0, 25]:\n",
    "    for state, state_data in all_state_data.items():\n",
    "        for isp in ISPS_PER_STATE[state]:\n",
    "            state_isp_data_cols = data_cols[state][isp]\n",
    "            fcc_cols = state_isp_data_cols[\"fcc\"]\n",
    "            fcc_speed_cols = state_isp_data_cols[\"fcc_speed\"]\n",
    "            tool_cols = state_isp_data_cols[\"tool\"]\n",
    "            tool_speed_cols = state_isp_data_cols[\"tool_speed\"]\n",
    "\n",
    "            isp_data = state_data[fcc_cols + fcc_speed_cols + tool_cols]\n",
    "            isp_data = isp_data[(isp_data[fcc_cols] == 1).any(axis=1)]\n",
    "            isp_data = isp_data[(isp_data[fcc_speed_cols] >= speed).any(axis=1)]\n",
    "            \n",
    "            cov_data = isp_data[(isp_data[tool_cols].isin(POS_RESPONSES[isp]).any(axis=1))]\n",
    "            \n",
    "            other_data = isp_data[~(isp_data[tool_cols].isin(POS_RESPONSES[isp]).any(axis=1))]\n",
    "            \n",
    "            noncov_data = other_data[(other_data[tool_cols].isin(NEG_RESPONSES[isp]).all(axis=1))]\n",
    "            \n",
    "            if speed == 0:\n",
    "                tab3_data_0[isp]['fcc'] += len(cov_data) + len(noncov_data)\n",
    "                tab3_data_0[isp]['bat'] += len(cov_data)\n",
    "            if speed == 25:\n",
    "                tab3_data_25[isp]['fcc'] += len(cov_data) + len(noncov_data)\n",
    "                tab3_data_25[isp]['bat'] += len(cov_data)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "tab3_data_0"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "tab3_data_25"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Table 4"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "block_count_0 = Counter()\n",
    "block_count_25 = Counter()\n",
    "\n",
    "overreporting_counts_0 = Counter()\n",
    "overreporting_counts_25 = Counter()\n",
    "\n",
    "def overcount_block(block_data, isp, fcc_cols, fcc_speed_cols, tool_cols, timestamp_cols):\n",
    "    if len(block_data) > 0:\n",
    "            block_count_0[isp] += 1\n",
    "    for speed in [0, 25]:\n",
    "        if speed == 25:\n",
    "            block_data = block_data[(block_data[fcc_speed_cols] >= speed).any(axis=1)]\n",
    "            if len(block_data) > 0:\n",
    "                block_count_25[isp] += 1\n",
    "                \n",
    "        if len(block_data) <= 20:\n",
    "            return\n",
    "\n",
    "        if len(block_data) > 0 and len(block_data) == block_data[tool_cols].isin(NEG_RESPONSES[isp]).all(axis=1).sum():\n",
    "            if speed == 0:\n",
    "                overreporting_counts_0[isp] += 1\n",
    "            else:\n",
    "                overreporting_counts_25[isp] += 1\n",
    "\n",
    "for state, state_data in all_state_data.items():\n",
    "    print(state)\n",
    "    for isp in ISPS_PER_STATE[state]:\n",
    "        \n",
    "        isp_cols = data_cols[state][isp]\n",
    "        fcc_cols = isp_cols[\"fcc\"]\n",
    "        fcc_speed_cols = isp_cols[\"fcc_speed\"]\n",
    "        tool_cols = isp_cols[\"tool\"]\n",
    "        \n",
    "        state_data[(state_data[fcc_cols] == 1).any(axis=1)].groupby(\"addr_census_block\").apply(lambda df: overcount_block(df, isp, fcc_cols, fcc_speed_cols, tool_cols, timestamp_cols))"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "overreporting_counts_0"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "overreporting_counts_25"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "block_count_0"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "block_count_25"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Figure 5"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "#### Vectorized"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "speed_isps = ['att', 'centurylink', 'consolidated', 'windstream']\n",
    "all_isp_speed_max = defaultdict(list)\n",
    "\n",
    "for state in all_state_data:\n",
    "    state_data_global = all_state_data[state]\n",
    "    \n",
    "    isps = [x for x in speed_isps if x in ISPS_PER_STATE[state]]\n",
    "    \n",
    "    for isp in isps:\n",
    "        state_isp_data_cols = data_cols[state][isp]\n",
    "        fcc_cols = state_isp_data_cols[\"fcc\"]\n",
    "        fcc_speed_cols = state_isp_data_cols[\"fcc_speed\"]\n",
    "        tool_cols = state_isp_data_cols[\"tool\"]\n",
    "        tool_speed_cols = state_isp_data_cols[\"tool_speed\"]\n",
    "        \n",
    "        state_data.loc[:, \"FCC\"] = state_data[fcc_speed_cols].max(axis=1)\n",
    "        state_data.loc[:, \"BATs\"] = state_data[tool_speed_cols].max(axis=1)\n",
    "\n",
    "        all_isp_speed_max[isp].append(state_data[[\"addr_id\", \"addr_census_block\", \"FCC\", \"BATs\"]])\n",
    "        \n",
    "for isp, isp_speed_data in list(all_isp_speed_max.items()):\n",
    "    all_isp_speed_max[isp] = pd.concat(isp_speed_data).dropna()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "fig, axes = plt.subplots(ncols=4, sharey=True, figsize=(16, 6))\n",
    "speed_isps = ['att', 'centurylink', 'consolidated', 'windstream']\n",
    "for i, (isp, isp_speed_data) in enumerate(sorted(all_isp_speed_max.items(), key=lambda x: speed_isps.index(x[0]))):\n",
    "    isp_speed_data = isp_speed_data.copy(deep=True)\n",
    "\n",
    "    isp_speed_data = isp_speed_data[isp_speed_data[\"BATs\"] > 0]\n",
    "    \n",
    "    isp_speed_data[\"urban/rural\"] = isp_speed_data[\"addr_census_block\"].transform(lambda x: LABELS[urban_rural_dict[x]])\n",
    "    new_copy = isp_speed_data.copy(deep=True)\n",
    "    new_copy[\"urban/rural\"] = \"Total\"\n",
    "    \n",
    "    isp_speed_data = pd.concat([isp_speed_data, new_copy])\n",
    "    \n",
    "    melted = isp_speed_data.melt(id_vars=['addr_census_block', 'urban/rural', 'addr_id'], value_vars=['FCC', 'BATs'], var_name='source', value_name='max_speed')\n",
    "    \n",
    "    ax = sns.boxplot(x='urban/rural', y='max_speed', hue='source', data=melted, showfliers=False, ax=axes[i], order=['Total', 'Urban', 'Rural'], palette=\"Blues\")\n",
    "    ax.set_yscale('log')\n",
    "    ax.set_xlabel(\"\")\n",
    "    handles, labels = ax.get_legend_handles_labels()\n",
    "    ax.legend(handles=handles, labels=labels)\n",
    "    if i == 0:\n",
    "        ax.set_ylabel(\"Maximum Download Speed (Mbps)\")\n",
    "    else:\n",
    "        ax.set_ylabel(\"\")\n",
    "        ax.get_legend().remove()\n",
    "    ax.set_title(\"{}\".format(LABELS[isp]))\n",
    "\n",
    "fig.savefig(\"fig5.pdf\")"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "#### Iterative"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "speed_isps = ['att', 'centurylink', 'consolidated', 'windstream']\n",
    "all_isp_speed_max_list = []\n",
    "\n",
    "for state in all_state_data:\n",
    "    print(state)\n",
    "    state_data_global = all_state_data[state]\n",
    "    \n",
    "    isps = [x for x in speed_isps if x in ISPS_PER_STATE[state]]\n",
    "    \n",
    "    for address in state_data_global.itertuples():\n",
    "        blockno = getattr(address, \"addr_census_block\")  \n",
    "        addr_id = getattr(address, \"addr_id\")\n",
    "        try:\n",
    "            block_class = urban_rural_dict[blockno]\n",
    "        except:\n",
    "            continue\n",
    "        \n",
    "        for isp in isps:\n",
    "            isp_label = LABELS[isp]\n",
    "            state_isp_data_cols = data_cols[state][isp]\n",
    "            fcc_cols = state_isp_data_cols[\"fcc\"]\n",
    "            fcc_speed_cols = state_isp_data_cols[\"fcc_speed\"]\n",
    "            tool_cols = state_isp_data_cols[\"tool\"]\n",
    "            tool_speed_cols = state_isp_data_cols[\"tool_speed\"]\n",
    "            \n",
    "            fcc_max = float('-inf')\n",
    "            tool_max = float('-inf')\n",
    "            for i, tool_col in enumerate(tool_cols):\n",
    "                fcc_col = fcc_cols[i]\n",
    "                fcc_speed_col = fcc_speed_cols[i]\n",
    "                tool_speed_col = tool_speed_cols[i]\n",
    "                \n",
    "                if getattr(address, fcc_col) == 1:\n",
    "                    fcc_max = max(fcc_max, getattr(address, fcc_speed_col))\n",
    "                    if str(int(getattr(address, tool_col))) in POS_RESPONSES[isp]:\n",
    "                        tool_max = max(tool_max, getattr(address, tool_speed_col))\n",
    "                \n",
    "            if tool_max > 0:\n",
    "                all_isp_speed_max_list.append([addr_id, blockno, LABELS[block_class], isp, \"FCC\", fcc_max])\n",
    "                all_isp_speed_max_list.append([addr_id, blockno, LABELS[block_class], isp, \"BATs\", tool_max])\n",
    "\n",
    "                all_isp_speed_max_list.append([addr_id, blockno, \"Total\", isp, \"FCC\", fcc_max])\n",
    "                all_isp_speed_max_list.append([addr_id, blockno, \"Total\", isp, \"BATs\", tool_max])\n",
    "\n",
    "column_names = [\"addr_id\", \"blockno\", \"Urban/Rural\", \"ISP\", \"Source\", \"Maximum Download Speed\"]\n",
    "isp_speed_data_iter = pd.DataFrame(all_isp_speed_max_list, columns=column_names)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "fig, axes = plt.subplots(ncols=4, sharey=True, figsize=(16, 6))\n",
    "\n",
    "for i, isp in enumerate(speed_isps):\n",
    "    print(isp)\n",
    "    isp_data = isp_speed_data_iter.query(f\"ISP == '{isp}'\")\n",
    "\n",
    "    ax = sns.boxplot(x='Urban/Rural', y='Maximum Download Speed', hue='Source', data=isp_data, showfliers=False, ax=axes[i], order=['Total', 'Urban', 'Rural'], palette=\"Blues\")\n",
    "    ax.set_yscale('log')\n",
    "    ax.set_xlabel(\"\")\n",
    "    handles, labels = ax.get_legend_handles_labels()\n",
    "    ax.legend(handles=handles, labels=labels)\n",
    "    if i == 0:\n",
    "        ax.set_ylabel(\"Maximum Download Speed (Mbps)\")\n",
    "    else:\n",
    "        ax.set_ylabel(\"\")\n",
    "        ax.get_legend().remove()\n",
    "    ax.set_title(\"{}\".format(LABELS[isp]))\n",
    "\n",
    "fig.tight_layout()\n",
    "fig.savefig(\"fig5.pdf\")"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Table 10"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "isp_stats = defaultdict(Counter)\n",
    "\n",
    "for state, state_data in all_state_data.items():\n",
    "    print(state)\n",
    "    for isp in ISPS_PER_STATE[state]:\n",
    "        isp_cols = data_cols[state][isp]\n",
    "        tool_cols = isp_cols[\"tool\"]\n",
    "        \n",
    "        pos = False\n",
    "        neg = False\n",
    "        unrecognized = False\n",
    "        business = False\n",
    "        unknown = False\n",
    "\n",
    "        isp_stats[isp][\"pos\"] += (state_data[tool_cols].isin(POS_RESPONSES[isp]).any(axis=1)).sum()\n",
    "        isp_stats[isp][\"neg\"] += (state_data[tool_cols].isin(NEG_RESPONSES[isp]).all(axis=1)).sum()\n",
    "        isp_stats[isp][\"unrecognized\"] += (state_data[tool_cols].isin(UNRECOGNIZED_RESPONSES[isp]).all(axis=1)).sum()\n",
    "        isp_stats[isp][\"business\"] += (state_data[tool_cols].isin(BUSINESS_RESPONSES[isp]).all(axis=1)).sum()\n",
    "        isp_stats[isp][\"unknown\"] += (state_data[tool_cols].isin(UNKNOWN_RESPONSES[isp]).all(axis=1)).sum()\n",
    "isp_stats"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Precompute for Fig 6 + Fig 9 + Regression"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "isps_per_block = defaultdict(set) # Regression\n",
    "isps_per_block_cov_noncov_only_0 = defaultdict(lambda: defaultdict(list)) # Table 6\n",
    "isps_per_block_cov_noncov_only_25 = defaultdict(lambda: defaultdict(list)) # Table 6\n",
    "\n",
    "for state, state_data in all_state_data.items():\n",
    "    state_data_cols = data_cols[state]\n",
    "    for address in state_data.itertuples():\n",
    "        blockno = getattr(address, \"addr_census_block\")\n",
    "        bad = False\n",
    "        \n",
    "        fcc_isps_0 = []\n",
    "        fcc_isps_25 = []\n",
    "        bat_isps = []\n",
    "        \n",
    "        for isp in ISPS_PER_STATE[state]:\n",
    "            state_isp_data_cols = state_data_cols[isp]\n",
    "            fcc_cols = state_isp_data_cols[\"fcc\"]\n",
    "            fcc_speed_cols = state_isp_data_cols[\"fcc_speed\"]\n",
    "            tool_cols = state_isp_data_cols[\"tool\"]\n",
    "            tool_speed_cols = state_isp_data_cols[\"tool_speed\"]\n",
    "            \n",
    "            fcc_covered = False\n",
    "            fcc_covered_25 = False\n",
    "            for i, fcc_col in enumerate(fcc_cols):\n",
    "                fcc_speed_col = fcc_speed_cols[i]\n",
    "                if getattr(address, fcc_col) == 1:\n",
    "                    fcc_covered = True\n",
    "                    if getattr(address, fcc_speed_col) >= 25:\n",
    "                        fcc_covered_25 = True\n",
    "\n",
    "            if not fcc_covered:\n",
    "                continue\n",
    "                \n",
    "            fcc_isps_0.append(isp)\n",
    "            if fcc_covered_25:\n",
    "                fcc_isps_25.append(isp)\n",
    "            \n",
    "            bat_covered = False\n",
    "\n",
    "            for i, tool_col in enumerate(tool_cols):\n",
    "                tool_speed_col = tool_speed_cols[i]\n",
    "\n",
    "                tool_data = getattr(address, tool_col)\n",
    "                if pd.isna(tool_data):\n",
    "                    bad = True\n",
    "                    break\n",
    "                \n",
    "                tool_data = str(int(tool_data))\n",
    "                \n",
    "                if tool_data in POS_RESPONSES[isp]:\n",
    "                    bat_covered = True\n",
    "                elif tool_data not in NEG_RESPONSES[isp]:\n",
    "                    bad = True\n",
    "                    break\n",
    "\n",
    "            if bat_covered:\n",
    "                bat_isps.append(isp)\n",
    "                \n",
    "        for isp in fcc_isps_0:\n",
    "            isps_per_block[blockno].add(isp)\n",
    "            \n",
    "        if not bad and len(fcc_isps_0) > 0:\n",
    "            isps_per_block_cov_noncov_only_0[blockno]['fcc'].append(len(fcc_isps_0))\n",
    "            isps_per_block_cov_noncov_only_0[blockno]['tool'].append(len(bat_isps))\n",
    "            \n",
    "            if len(fcc_isps_25) > 0:\n",
    "                isps_per_block_cov_noncov_only_25[blockno]['fcc'].append(len(fcc_isps_0))\n",
    "                isps_per_block_cov_noncov_only_25[blockno]['tool'].append(len(bat_isps))"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Table 6 (Regression)"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "ACS_race = pd.read_csv(\"ACS/ACSDT5Y2018.B03002_data_with_overlays_2020-12-21T003055.csv\")[1:][[\"GEO_ID\", \"B03002_003E\", \"B03002_004E\", \"B03002_005E\", \"B03002_006E\", \"B03002_007E\", \"B03002_008E\", \"B03002_009E\", \"B03002_012E\"]]\n",
    "ACS_poverty = pd.read_csv(\"ACS/ACSST5Y2018.S1701_data_with_overlays_2020-12-21T002937.csv\")[1:][[\"GEO_ID\", \"S1701_C03_001E\"]]\n",
    "\n",
    "ACS_race[\"GEO_ID\"] = ACS_race[\"GEO_ID\"].transform(lambda x: x.split(\"US\")[1]).astype(int)\n",
    "for col in ACS_race:\n",
    "    if col != \"GEO_ID\":\n",
    "        ACS_race[col] = ACS_race[col].astype(int)\n",
    "\n",
    "ACS_poverty[\"GEO_ID\"] = ACS_poverty[\"GEO_ID\"].transform(lambda x: x.split(\"US\")[1]).astype(int)\n",
    "\n",
    "ACS_poverty = ACS_poverty[ACS_poverty[\"S1701_C03_001E\"] != '-']\n",
    "ACS_poverty[\"S1701_C03_001E\"] = ACS_poverty[\"S1701_C03_001E\"].astype(float)\n",
    "ACS_poverty[\"S1701_C03_001E\"] = ACS_poverty[\"S1701_C03_001E\"].transform(lambda x: x/100.0)\n",
    "ACS_poverty = ACS_poverty.rename(columns={'S1701_C03_001E': 'pov_rate'})"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "ACS_race_dict = ACS_race.set_index(\"GEO_ID\").to_dict('index')\n",
    "ACS_poverty_dict = ACS_poverty.set_index(\"GEO_ID\").to_dict('index')"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "minority_count_per_tract = defaultdict(dict)\n",
    "\n",
    "for tractno, race_data in ACS_race_dict.items():\n",
    "    white = race_data[\"B03002_003E\"]\n",
    "    minority = sum(race_data.values()) - white\n",
    "    \n",
    "    minority_count_per_tract[tractno]['white'] = white\n",
    "    minority_count_per_tract[tractno]['minority'] = minority\n",
    "    \n",
    "poverty_percent_per_tract = {}\n",
    "\n",
    "for tractno, poverty_rate in ACS_poverty_dict.items():\n",
    "    poverty_percent_per_tract[tractno] = poverty_rate['pov_rate']"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "covered_counts_per_tract = Counter()\n",
    "not_covered_counts_per_tract = Counter()\n",
    "rural_urban_total_counts_per_tract = defaultdict(lambda:{\n",
    "    'U': 0,\n",
    "    'R': 0,\n",
    "})\n",
    "\n",
    "for state, state_data in table5_dict[0].items():\n",
    "    for blockno in state_data:\n",
    "        if blockno not in urban_rural_dict:\n",
    "            continue\n",
    "        block_class = urban_rural_dict[blockno]\n",
    "        if state_data[blockno][0] > 0:\n",
    "            tractno = int(blockno / 10000)\n",
    "            covered_counts_per_tract[tractno] += state_data[blockno][1]\n",
    "            not_covered_counts_per_tract[tractno] += state_data[blockno][0]\n",
    "            \n",
    "            rural_urban_total_counts_per_tract[tractno][block_class] += state_data[blockno][0]\n",
    "\n",
    "isps_per_tract = defaultdict(Counter)\n",
    "blocks_per_tract = Counter()\n",
    "for blockno, isps in isps_per_block.items():\n",
    "    if len(isps) == 0:\n",
    "        continue\n",
    "    tractno = int(blockno / 10000)\n",
    "    blocks_per_tract[tractno] += 1\n",
    "    \n",
    "    for isp in isps:\n",
    "        isps_per_tract[tractno][isp] += 1\n",
    "\n",
    "for tractno in isps_per_tract:\n",
    "    for isp in isps_per_tract[tractno]:\n",
    "        isps_per_tract[tractno][isp] = isps_per_tract[tractno][isp] / blocks_per_tract[tractno]\n",
    "        \n",
    "pop_per_tract = Counter()\n",
    "\n",
    "for blockno, pop in pop_dict.items():\n",
    "    tractno = int(blockno / 10000)\n",
    "    pop_per_tract[tractno] += pop"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "column_names=['tract', 'coverage_avg','minority_per','poverty_per','rural_per','state', 'pop'] + SORTED_ISPS"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "data_for_df = []\n",
    "\n",
    "arr = []\n",
    "\n",
    "excluded_tracts = []\n",
    "\n",
    "for tractno in rural_urban_total_counts_per_tract:\n",
    "    state_fips = int(tractno / 10**9)\n",
    "    if state_fips not in VALID_STATE_FIPS:\n",
    "        excluded_tracts.append(state_fips)\n",
    "        continue\n",
    "    state = VALID_STATE_FIPS[state_fips]\n",
    "    arr.append((tractno, tractno not in minority_count_per_tract, tractno not in poverty_percent_per_tract, tractno not in covered_counts_per_tract, tractno not in not_covered_counts_per_tract))\n",
    "    if tractno not in minority_count_per_tract or tractno not in poverty_percent_per_tract or (tractno not in covered_counts_per_tract and tractno not in not_covered_counts_per_tract):\n",
    "        continue\n",
    "    data_for_df.append([\n",
    "        tractno,\n",
    "        covered_counts_per_tract[tractno]/not_covered_counts_per_tract[tractno],\n",
    "        minority_count_per_tract[tractno]['minority']/(minority_count_per_tract[tractno]['minority']+minority_count_per_tract[tractno]['white']),\n",
    "        poverty_percent_per_tract[tractno],\n",
    "        rural_urban_total_counts_per_tract[tractno]['R']/(rural_urban_total_counts_per_tract[tractno]['R']+rural_urban_total_counts_per_tract[tractno]['U']),\n",
    "        state,\n",
    "        pop_per_tract[tractno],\n",
    "        isps_per_tract[tractno]['att'],\n",
    "        isps_per_tract[tractno]['centurylink'],\n",
    "        isps_per_tract[tractno]['consolidated'],\n",
    "        isps_per_tract[tractno]['cox'],\n",
    "        isps_per_tract[tractno]['xfinity'],\n",
    "        isps_per_tract[tractno]['verizon'],\n",
    "        isps_per_tract[tractno]['windstream'],\n",
    "        isps_per_tract[tractno]['charter'],\n",
    "        isps_per_tract[tractno]['frontier'],\n",
    "    ])\n",
    "    \n",
    "df_tract_data = pd.DataFrame(data_for_df, columns=column_names)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "import statsmodels.api as sm\n",
    "import statsmodels.formula.api as smf\n",
    "\n",
    "mod = smf.ols(formula='coverage_avg ~ minority_per + poverty_per + rural_per + C(state) + pop + att + centurylink + consolidated + cox + xfinity + verizon + windstream + charter + frontier', data=df_tract_data)\n",
    "lm = mod.fit()\n",
    "print(lm.summary())"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Figure 6 + 9 (Competition by Urban/Rural and Speed)"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### Figure 6 (Competition by Urban/Rural)"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "competition_state_stats = defaultdict(list)\n",
    "column_names = ['state', 'block', 'Rural/Urban', 'avg_tool_count', 'avg_fcc_count', 'overstatement']\n",
    "\n",
    "for blockno, block_data in isps_per_block_cov_noncov_only_0.items():\n",
    "    if blockno not in urban_rural_dict:\n",
    "        continue\n",
    "    block_class = urban_rural_dict[blockno]\n",
    "    state_fips = int(blockno / 10**13)\n",
    "    state = VALID_STATE_FIPS[state_fips]\n",
    "    \n",
    "    fcc_data = block_data[\"fcc\"]\n",
    "    tool_data = block_data[\"tool\"]\n",
    "    fcc_count_avg = sum(fcc_data)/len(fcc_data)\n",
    "    tool_count_avg = sum(tool_data)/len(tool_data)\n",
    "    competition_state_stats[state].append([state, blockno, LABELS[block_class], tool_count_avg, fcc_count_avg, tool_count_avg / fcc_count_avg])\n",
    "    competition_state_stats[state].append([state, blockno, \"Total\", tool_count_avg, fcc_count_avg, tool_count_avg / fcc_count_avg])"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "fig, axes = plt.subplots(nrows=3,ncols=3, figsize=(10,10), sharey=True)\n",
    "\n",
    "for i, state in enumerate(STATES):\n",
    "    ax_row = int(i / (len(STATES)/3))\n",
    "    ax_col = int(i % (len(STATES)/3))\n",
    "    df_counts_per_block = pd.DataFrame(competition_state_stats[state], columns = column_names)\n",
    "    \n",
    "    ax = sns.boxplot(x = 'Rural/Urban', y = 'overstatement', data = df_counts_per_block,fliersize=0,ax=axes[ax_row][ax_col], order=['Total','Urban','Rural']) \n",
    "    ax.set_ylabel(\"\")\n",
    "        #ax.get_legend().remove()\n",
    "    ax.set_xlabel(\"\")\n",
    "    ax.set_title(\"{}\".format(LABELS[state]))\n",
    "    ax.grid(False)\n",
    "\n",
    "fig.tight_layout()\n",
    "fig.savefig(\"fig6.pdf\")"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### Figure 9 (Competition by Speed)"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "competition_state_stats = defaultdict(list)\n",
    "column_names = ['state', 'block', 'speed', 'avg_tool_count', 'avg_fcc_count', 'overstatement']\n",
    "\n",
    "for blockno, block_data in isps_per_block_cov_noncov_only_0.items():\n",
    "    if blockno not in urban_rural_dict:\n",
    "        continue\n",
    "    block_class = urban_rural_dict[blockno]\n",
    "    state_fips = int(blockno / 10**13)\n",
    "    state = VALID_STATE_FIPS[state_fips]\n",
    "    \n",
    "    fcc_data = block_data[\"fcc\"]\n",
    "    tool_data = block_data[\"tool\"]\n",
    "    fcc_count_avg = sum(fcc_data)/len(fcc_data)\n",
    "    tool_count_avg = sum(tool_data)/len(tool_data)\n",
    "\n",
    "    competition_state_stats[state].append([state, blockno, 0, tool_count_avg, fcc_count_avg, tool_count_avg / fcc_count_avg])\n",
    "    if blockno in isps_per_block_cov_noncov_only_25:\n",
    "        competition_state_stats[state].append([state, blockno, 25, tool_count_avg, fcc_count_avg, tool_count_avg / fcc_count_avg])"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "fig, axes = plt.subplots(nrows=3,ncols=3, figsize=(10,10), sharey=True)\n",
    "\n",
    "for i, state in enumerate(STATES):\n",
    "    ax_row = int(i / (len(STATES)/3))\n",
    "    ax_col = int(i % (len(STATES)/3))\n",
    "    df_counts_per_block = pd.DataFrame(competition_state_stats[state], columns = column_names)\n",
    "    ax = sns.boxplot(x = 'speed', y = 'overstatement', data = df_counts_per_block,fliersize=0,ax=axes[ax_row][ax_col], order=[0, 25]) \n",
    "    if ax_col == 0 and ax_row != 1:\n",
    "        ax.set_ylabel(\"Proportion of # of Providers Per Block (BATs)\")\n",
    "    else:\n",
    "        ax.set_ylabel(\"\")\n",
    "        #ax.get_legend().remove()\n",
    "    ax.set_xlabel(\"\")\n",
    "    ax.set_title(\"{}\".format(LABELS[state]))\n",
    "    ax.grid(False)\n",
    "    \n",
    "fig.tight_layout()\n",
    "fig.savefig(\"fig9.pdf\")"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "for code, state in VALID_STATE_FIPS.items():\n",
    "    state_data = all_state_data[state]\n",
    "    lol = (state_data[(state_data.addr_census_block / 10**13).astype(int) != code])\n",
    "    for col in lol.columns[11:]:\n",
    "        print(lol[col].unique())"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# Appendix"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Figure 7 (Overstatements by Min Speed)"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "speed_isps = ['att', 'centurylink', 'consolidated', 'windstream']\n",
    "all_isp_speed_max = defaultdict(list)\n",
    "\n",
    "for state in all_state_data:\n",
    "    state_data_global = all_state_data[state]\n",
    "    \n",
    "    isps = [x for x in speed_isps if x in ISPS_PER_STATE[state]]\n",
    "    \n",
    "    for isp in isps:\n",
    "        state_isp_data_cols = data_cols[state][isp]\n",
    "        fcc_cols = state_isp_data_cols[\"fcc\"]\n",
    "        fcc_speed_cols = state_isp_data_cols[\"fcc_speed\"]\n",
    "        tool_cols = state_isp_data_cols[\"tool\"]\n",
    "        tool_speed_cols = state_isp_data_cols[\"tool_speed\"]\n",
    "        timestamp_cols = state_isp_data_cols[\"timestamp\"]\n",
    "        \n",
    "        state_data = state_data_global.copy(deep=True)\n",
    "        \n",
    "        state_data = state_data[((state_data[timestamp_cols] < PAPER_TIME)).any(axis=1)]\n",
    "        \n",
    "        state_data[\"FCC\"] = state_data[fcc_speed_cols].max(axis=1)\n",
    "        state_data[\"BATs\"] = state_data[tool_speed_cols].max(axis=1)\n",
    "        \n",
    "        all_isp_speed_max[isp].append(state_data[[\"addr_id\", \"addr_census_block\", \"FCC\", \"BATs\"]])\n",
    "        \n",
    "for isp, isp_speed_data in list(all_isp_speed_max.items()):\n",
    "    all_isp_speed_max[isp] = pd.concat(isp_speed_data).dropna()"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "speed_isps = ['att', 'centurylink', 'consolidated', 'windstream']\n",
    "fig7_data = defaultdict(lambda: defaultdict(Counter)) # speed -> {blockno -> {'fcc', 'tool'}}\n",
    "\n",
    "for i, (isp, isp_speed_data) in enumerate(sorted(all_isp_speed_max.items(), key=lambda x: speed_isps.index(x[0]))):\n",
    "    print(isp)\n",
    "    isp_speed_data = isp_speed_data.copy(deep=True)\n",
    "    isp_speed_data = isp_speed_data[isp_speed_data[\"BATs\"] > 0]\n",
    "    \n",
    "    for address in isp_speed_data.itertuples():\n",
    "        blockno = getattr(address, \"addr_census_block\")\n",
    "        fcc_speed = getattr(address, \"FCC\")\n",
    "        tool_speed = getattr(address, \"BATs\")\n",
    "        \n",
    "        good = False\n",
    "        if tool_speed >= fcc_speed:\n",
    "            good = True\n",
    "        \n",
    "        for speed in [0, 25, 50, 100, 200]:\n",
    "            if fcc_speed >= speed:\n",
    "                fig7_data[speed][blockno]['fcc'] += 1\n",
    "                if good:\n",
    "                    fig7_data[speed][blockno]['tool'] += 1\n",
    "    \n",
    "total_line = []\n",
    "rural_line = []\n",
    "urban_line = []\n",
    "\n",
    "for speed, speed_data in fig7_data.items():\n",
    "    rural_count = []\n",
    "    urban_count = []\n",
    "    total_count = []\n",
    "    \n",
    "    for blockno, block_data in speed_data.items():\n",
    "        block_class = urban_rural_dict[blockno]\n",
    "        if block_class == \"R\":\n",
    "            rural_count.append(block_data[\"tool\"]/block_data[\"fcc\"])\n",
    "        else:\n",
    "            urban_count.append(block_data[\"tool\"]/block_data[\"fcc\"])\n",
    "        total_count.append(block_data[\"tool\"]/block_data[\"fcc\"])\n",
    "    \n",
    "    rural_count = sum(rural_count)/len(rural_count)\n",
    "    urban_count = sum(urban_count)/len(urban_count)\n",
    "    total_count = sum(total_count)/len(total_count)\n",
    "    \n",
    "    rural_line.append(rural_count)\n",
    "    urban_line.append(urban_count)\n",
    "\n",
    "    total_line.append(total_count)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "ticks = [0, 1, 2, 3, 4]\n",
    "labels = [\"0\", \"25\", \"50\", \"100\", \"200\"]\n",
    "fig, ax = plt.subplots(1,1)\n",
    "plt.plot(rural_line, marker='o', label='Rural')\n",
    "plt.plot(urban_line, marker='o', label='Urban')\n",
    "plt.plot(total_line, marker='o', label='Total')\n",
    "ax.set_xticks(ticks)\n",
    "ax.set_xticklabels(labels)\n",
    "plt.legend()\n",
    "plt.ylabel(\"\")\n",
    "plt.xlabel(\"Minimum Download Speed (Mbps)\")\n",
    "# ax.set_yticklabels(['{:,.0%}'.format(x) for x in ticks])\n",
    "\n",
    "fig.tight_layout()\n",
    "plt.savefig(\"overstatements_lower_speeds.pdf\")"
   ],
   "outputs": [],
   "metadata": {}
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.8.2 64-bit ('py38': conda)",
   "language": "python",
   "name": "python38264bitpy38condabccf429ab6a34b9d9c88a1df106dcd7b"
  },
  "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": 4
}