ProjectScratch.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "18b3a3df",
   "metadata": {},
   "source": [
    "Note that MIR is mortality rate divided by incidence rate. https://wonder.cdc.gov/wonder/help/CancerMIR-v2011.html#About-MIRR\n",
    "$$MIR = \\frac{Mortality Rate}{Incidence Rate}$$\n",
    "\n",
    "A cancer mortality rate is the number of deaths, with cancer as the underlying cause of death, occurring in a specified population during a year.\n",
    "$$Mortality Rate = \\frac{Cancer Deaths}{Population} \\times 100,000$$\n",
    "\n",
    "A cancer incidence rate is the number of new cancers of a specific site/type occurring in a specified population during a year, usually expressed as the number of cancers per 100,000 population at risk.\n",
    "$$Incidence Rate = \\frac{New Cancers}{Population} \\times 100,000$$\n",
    "$\\newline$\n",
    "\n",
    "Notes for the report:\n",
    "1. Break code into at least 2 Python files.\n",
    "2. Test the visualization with the numbers we calculated. (Use a smaller subset to achieve)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "9e4cd8af",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import geopandas as gpd\n",
    "import altair as alt\n",
    "from utilities import Utils\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "by_age = pd.read_csv(\"C:\\\\Users\\\\pxu11\\\\OneDrive - UW\\\\2022 Winter\\\\CSE 163 A\\\\cse163-cancer-research\\\\data\\\\USCS-1999-2018-ASCII\\\\BYAGE.TXT\", delimiter=\"|\", low_memory=False)\n",
    "by_area = pd.read_csv(\"C:\\\\Users\\\\pxu11\\\\OneDrive - UW\\\\2022 Winter\\\\CSE 163 A\\\\cse163-cancer-research\\\\data\\\\USCS-1999-2018-ASCII\\\\BYAREA.TXT\", delimiter=\"|\", low_memory=False)\n",
    "by_county = pd.read_csv(\"C:\\\\Users\\\\pxu11\\\\OneDrive - UW\\\\2022 Winter\\\\CSE 163 A\\\\cse163-cancer-research\\\\data\\\\USCS-1999-2018-ASCII\\\\BYAREA_COUNTY.TXT\", delimiter=\"|\", low_memory=False)\n",
    "counties = gpd.read_file(\"C:\\\\Users\\\\pxu11\\\\OneDrive - UW\\\\2022 Winter\\\\CSE 163 A\\\\cse163-cancer-research\\\\data\\\\2020_us_county_shp\\\\cb_2020_us_county_20m.shp\")\n",
    "by_site = pd.read_csv(\"C:\\\\Users\\\\pxu11\\\\OneDrive - UW\\\\2022 Winter\\\\CSE 163 A\\\\cse163-cancer-research\\\\data\\\\USCS-1999-2018-ASCII\\\\BYSITE.TXT\", delimiter=\"|\", low_memory=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "7973947d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(by_area) == pd.DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "c9a07498",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "123613\n"
     ]
    }
   ],
   "source": [
    "def strip_geo_id(area):\n",
    "    # elements = area.split(\"(\")\n",
    "    geoid = area[area.index(\"(\"): area.index(\")\") + 1]\n",
    "    return geoid\n",
    "\n",
    "\n",
    "def clean_join_shp(by_county, counties):\n",
    "    by_county.loc[:, \"geoid\"] = by_county.loc[:, \"AREA\"] \\\n",
    "                                            .apply(strip_geo_id)\n",
    "    by_county = by_county.astype({\"geoid\": \"float32\"})\n",
    "    counties = counties.astype({\"GEOID\": \"float32\"})\n",
    "    prepared_shp = counties.merge(by_county, left_on=\"GEOID\",\n",
    "                                  right_on=\"geoid\", how=\"left\")\n",
    "    print(\"by_county: \", by_county[\"geoid\"].sort())\n",
    "    print(\"counties: \", counties[\"GEOID\"].sort())\n",
    "    return prepared_shp\n",
    "\n",
    "\n",
    "def get_mir(data, on, rate_col):\n",
    "    data = data.astype({rate_col: 'float32'})\n",
    "    mortality = data[data[\"EVENT_TYPE\"] == \"Mortality\"]\n",
    "    incidence = data[data[\"EVENT_TYPE\"] == \"Incidence\"]\n",
    "    joined = pd.merge(mortality, incidence, how='inner', on=on)\n",
    "    joined[\"MIR\"] = joined[rate_col + \"_x\"] / joined[rate_col + \"_y\"]\n",
    "    return joined\n",
    "\n",
    "\n",
    "def remove_rows(data, chars):\n",
    "    check = data != chars[0]  # get base filter\n",
    "    for c in chars:\n",
    "        check = check & (data != c)\n",
    "    return data[check.all(1)]\n",
    "\n",
    "\n",
    "def prepare_shp(by_county, counties):\n",
    "    by_county_c = by_county[[\"AREA\", \"RACE\", \"SITE\", \"YEAR\", \"EVENT_TYPE\",\n",
    "                             \"AGE_ADJUSTED_RATE\", \"SEX\"]].copy()\n",
    "    counties_c = counties[[\"GEOID\", \"NAMELSAD\", \"STUSPS\", \"STATE_NAME\",\n",
    "                           \"geometry\"]].copy()\n",
    "    by_county_c = Utils.remove_rows(data=by_county_c,\n",
    "                                    chars=['+', '~', '.', '-'])\n",
    "    by_county_c = Utils.get_mir(data=by_county_c, \n",
    "                                on=['AREA', 'RACE', 'SEX', 'SITE', 'YEAR'],\n",
    "                                rate_col='AGE_ADJUSTED_RATE')\n",
    "    prepared_shp = Utils.clean_join_shp(by_county=by_county_c,\n",
    "                                        counties=counties_c)\n",
    "    return prepared_shp\n",
    "\n",
    "\n",
    "prepared_shp = prepare_shp(by_county, counties)\n",
    "print(len(prepared_shp))\n",
    "no_ak = prepared_shp[\"STUSPS\"] != \"AK\"\n",
    "no_hi = prepared_shp[\"STUSPS\"] != \"HI\"\n",
    "races = prepared_shp[\"RACE\"] == \"Asian/Pacific Islander\"\n",
    "sites = prepared_shp[\"SITE\"] == \"Colon and Rectum\"\n",
    "sexes = prepared_shp[\"SEX\"] == \"Male and Female\"\n",
    "\n",
    "# alt \\\n",
    "#     .Chart(result[0][no_ak & no_hi & sexes & sites & races]) \\\n",
    "#     .mark_geoshape().encode(\n",
    "#     color='MIR:Q'\n",
    "# )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "5f93c869",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['All Races', 'American Indian/Alaska Native',\n",
       "       'Asian/Pacific Islander', 'Black', 'Hispanic', 'White'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "by_county[\"RACE\"].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "b7cfc15e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['2014-2018']\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>AREA</th>\n",
       "      <th>RACE</th>\n",
       "      <th>SITE</th>\n",
       "      <th>YEAR</th>\n",
       "      <th>EVENT_TYPE</th>\n",
       "      <th>AGE_ADJUSTED_RATE</th>\n",
       "      <th>SEX</th>\n",
       "      <th>STATE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>796158</th>\n",
       "      <td>KS: Allen County (20001)</td>\n",
       "      <td>All Races</td>\n",
       "      <td>All Cancer Sites Combined</td>\n",
       "      <td>2014-2018</td>\n",
       "      <td>Mortality</td>\n",
       "      <td>128.1</td>\n",
       "      <td>Female</td>\n",
       "      <td>KS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>796171</th>\n",
       "      <td>KS: Allen County (20001)</td>\n",
       "      <td>All Races</td>\n",
       "      <td>Lung and Bronchus</td>\n",
       "      <td>2014-2018</td>\n",
       "      <td>Mortality</td>\n",
       "      <td>28.6</td>\n",
       "      <td>Female</td>\n",
       "      <td>KS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>796182</th>\n",
       "      <td>KS: Allen County (20001)</td>\n",
       "      <td>All Races</td>\n",
       "      <td>All Cancer Sites Combined</td>\n",
       "      <td>2014-2018</td>\n",
       "      <td>Mortality</td>\n",
       "      <td>232.7</td>\n",
       "      <td>Male</td>\n",
       "      <td>KS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>796192</th>\n",
       "      <td>KS: Allen County (20001)</td>\n",
       "      <td>All Races</td>\n",
       "      <td>Lung and Bronchus</td>\n",
       "      <td>2014-2018</td>\n",
       "      <td>Mortality</td>\n",
       "      <td>61.2</td>\n",
       "      <td>Male</td>\n",
       "      <td>KS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>796205</th>\n",
       "      <td>KS: Allen County (20001)</td>\n",
       "      <td>All Races</td>\n",
       "      <td>All Cancer Sites Combined</td>\n",
       "      <td>2014-2018</td>\n",
       "      <td>Mortality</td>\n",
       "      <td>169.9</td>\n",
       "      <td>Male and Female</td>\n",
       "      <td>KS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>842590</th>\n",
       "      <td>KS: Wyandotte County (20209)</td>\n",
       "      <td>White</td>\n",
       "      <td>Oral Cavity and Pharynx</td>\n",
       "      <td>2014-2018</td>\n",
       "      <td>Mortality</td>\n",
       "      <td>2.9</td>\n",
       "      <td>Male and Female</td>\n",
       "      <td>KS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>842592</th>\n",
       "      <td>KS: Wyandotte County (20209)</td>\n",
       "      <td>White</td>\n",
       "      <td>Pancreas</td>\n",
       "      <td>2014-2018</td>\n",
       "      <td>Mortality</td>\n",
       "      <td>11.9</td>\n",
       "      <td>Male and Female</td>\n",
       "      <td>KS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>842593</th>\n",
       "      <td>KS: Wyandotte County (20209)</td>\n",
       "      <td>White</td>\n",
       "      <td>Prostate</td>\n",
       "      <td>2014-2018</td>\n",
       "      <td>Mortality</td>\n",
       "      <td>21.4</td>\n",
       "      <td>Male and Female</td>\n",
       "      <td>KS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>842594</th>\n",
       "      <td>KS: Wyandotte County (20209)</td>\n",
       "      <td>White</td>\n",
       "      <td>Stomach</td>\n",
       "      <td>2014-2018</td>\n",
       "      <td>Mortality</td>\n",
       "      <td>3.8</td>\n",
       "      <td>Male and Female</td>\n",
       "      <td>KS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>842597</th>\n",
       "      <td>KS: Wyandotte County (20209)</td>\n",
       "      <td>White</td>\n",
       "      <td>Urinary Bladder</td>\n",
       "      <td>2014-2018</td>\n",
       "      <td>Mortality</td>\n",
       "      <td>4.0</td>\n",
       "      <td>Male and Female</td>\n",
       "      <td>KS</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2037 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                AREA       RACE                       SITE  \\\n",
       "796158      KS: Allen County (20001)  All Races  All Cancer Sites Combined   \n",
       "796171      KS: Allen County (20001)  All Races          Lung and Bronchus   \n",
       "796182      KS: Allen County (20001)  All Races  All Cancer Sites Combined   \n",
       "796192      KS: Allen County (20001)  All Races          Lung and Bronchus   \n",
       "796205      KS: Allen County (20001)  All Races  All Cancer Sites Combined   \n",
       "...                              ...        ...                        ...   \n",
       "842590  KS: Wyandotte County (20209)      White    Oral Cavity and Pharynx   \n",
       "842592  KS: Wyandotte County (20209)      White                   Pancreas   \n",
       "842593  KS: Wyandotte County (20209)      White                   Prostate   \n",
       "842594  KS: Wyandotte County (20209)      White                    Stomach   \n",
       "842597  KS: Wyandotte County (20209)      White            Urinary Bladder   \n",
       "\n",
       "             YEAR EVENT_TYPE AGE_ADJUSTED_RATE              SEX STATE  \n",
       "796158  2014-2018  Mortality             128.1           Female    KS  \n",
       "796171  2014-2018  Mortality              28.6           Female    KS  \n",
       "796182  2014-2018  Mortality             232.7             Male    KS  \n",
       "796192  2014-2018  Mortality              61.2             Male    KS  \n",
       "796205  2014-2018  Mortality             169.9  Male and Female    KS  \n",
       "...           ...        ...               ...              ...   ...  \n",
       "842590  2014-2018  Mortality               2.9  Male and Female    KS  \n",
       "842592  2014-2018  Mortality              11.9  Male and Female    KS  \n",
       "842593  2014-2018  Mortality              21.4  Male and Female    KS  \n",
       "842594  2014-2018  Mortality               3.8  Male and Female    KS  \n",
       "842597  2014-2018  Mortality               4.0  Male and Female    KS  \n",
       "\n",
       "[2037 rows x 8 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def strip_county_name(area):\n",
    "    elements = area.split(\":\")\n",
    "    county = elements[0] +\" \" + elements[1].split(\"(\")[0].strip()\n",
    "    return county\n",
    "\n",
    "by_county_c = by_county[[\"AREA\", \"RACE\", \"SITE\", \"YEAR\", \"EVENT_TYPE\",\n",
    "                         \"AGE_ADJUSTED_RATE\", \"SEX\", \"STATE\"]].copy()\n",
    "by_county_c = Utils.remove_rows(data=by_county_c, chars=['+', '~', '.', '-'])\n",
    "# by_county_c[(by_county_c[\"STATE\"] == \"KS\") & \\\n",
    "#             (by_county_c[\"AREA\"] == \"KS: Unknown (20999)\")]# & (by_county_c[\"EVENT_TYPE\"] == \"Incidence\")]\n",
    "\n",
    "\n",
    "print(by_county_c[\"YEAR\"].unique())\n",
    "# Somehow get.mir has erased Kansas and Nevada\n",
    "state = by_county_c[\"STATE\"] == \"KS\"\n",
    "# len = 2037\n",
    "# by_county_c[state].groupby(['AREA', 'RACE', 'SEX', 'SITE', 'YEAR'])[\"AGE_ADJUSTED_RATE\"].min()\n",
    "area = by_county_c[\"AREA\"] == \"KS: Allen County (20001)\"\n",
    "race = by_county_c[\"RACE\"] == \"All Races\"\n",
    "site = by_county_c[\"SITE\"] == \"All Cancer Sites Combined\"\n",
    "sex = by_county_c[\"SEX\"] == \"Female\"\n",
    "year = by_county_c[\"YEAR\"] == \"2014-2018\"\n",
    "by_county_c[state & year]\n",
    "\n",
    "\n",
    "#by_county_c = Utils.get_mir(data=by_county_c, \n",
    "#                          on=['AREA', 'RACE', 'SEX', 'SITE', 'YEAR'],\n",
    "#                          rate_col='AGE_ADJUSTED_RATE')\n",
    "# by_county_c[by_county_c[\"STATE_x\"] == \"NE\"]\n",
    "\n",
    "#counties_c = counties[[\"NAMELSAD\", \"STUSPS\", \"geometry\"]].copy()\n",
    "#by_county_c.loc[:, \"county\"] = by_county_c.loc[:, \"AREA\"].apply(strip_county_name)\n",
    "#counties_c.loc[:, \"county\"] = counties_c.loc[:, \"STUSPS\"] + \" \" + counties_c.loc[:, \"NAMELSAD\"]\n",
    "\n",
    "#by_county_c = by_county_c.astype({\"county\": \"str\"}, copy=True)\n",
    "#counties_c = counties_c.astype({\"county\": \"str\"}, copy=True)\n",
    "#test_gdf = counties_c.merge(by_county_c, left_on=\"county\", right_on=\"county\",\n",
    "#                         how=\"left\")\n",
    "\n",
    "# test_gdf[test_gdf[\"county\"].notna()]\n",
    "\n",
    "#no_ak = test_gdf[\"STUSPS\"] != \"AK\"\n",
    "#no_hi = test_gdf[\"STUSPS\"] != \"HI\"\n",
    "#all_races = test_gdf[\"RACE\"] == \"All Races\"\n",
    "#all_cancer_sites = test_gdf[\"SITE\"] == \"All Cancer Sites Combined\"\n",
    "#both_sexes = test_gdf[\"SEX\"] == \"Male and Female\"\n",
    "#mortality = test_gdf[\"EVENT_TYPE\"] == \"Mortality\"\n",
    "#test_plot = test_gdf[all_races & all_cancer_sites & both_sexes & mortality & no_ak & no_hi]\n",
    "#test_plot.plot(figsize=(250, 500))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "cf69a2d8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>AREA</th>\n",
       "      <th>RACE</th>\n",
       "      <th>SITE</th>\n",
       "      <th>YEAR</th>\n",
       "      <th>EVENT_TYPE_x</th>\n",
       "      <th>AGE_ADJUSTED_RATE_x</th>\n",
       "      <th>SEX</th>\n",
       "      <th>STATE_x</th>\n",
       "      <th>EVENT_TYPE_y</th>\n",
       "      <th>AGE_ADJUSTED_RATE_y</th>\n",
       "      <th>STATE_y</th>\n",
       "      <th>MIR</th>\n",
       "      <th>county</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [AREA, RACE, SITE, YEAR, EVENT_TYPE_x, AGE_ADJUSTED_RATE_x, SEX, STATE_x, EVENT_TYPE_y, AGE_ADJUSTED_RATE_y, STATE_y, MIR, county]\n",
       "Index: []"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "state = by_county_c[\"STATE_x\"] == \"NV\"\n",
    "site = by_county_c[\"SITE\"] == \"All Cancer Sites Combined\"\n",
    "by_county_c[state]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "6bbb1037",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NAMELSAD</th>\n",
       "      <th>STUSPS</th>\n",
       "      <th>geometry</th>\n",
       "      <th>county</th>\n",
       "      <th>AREA</th>\n",
       "      <th>RACE</th>\n",
       "      <th>SITE</th>\n",
       "      <th>YEAR</th>\n",
       "      <th>EVENT_TYPE_x</th>\n",
       "      <th>AGE_ADJUSTED_RATE_x</th>\n",
       "      <th>SEX</th>\n",
       "      <th>EVENT_TYPE_y</th>\n",
       "      <th>AGE_ADJUSTED_RATE_y</th>\n",
       "      <th>MIR</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>785</th>\n",
       "      <td>Sumner County</td>\n",
       "      <td>KS</td>\n",
       "      <td>POLYGON ((-97.80760 37.47418, -97.15333 37.475...</td>\n",
       "      <td>KS Sumner County</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>915</th>\n",
       "      <td>Hamilton County</td>\n",
       "      <td>KS</td>\n",
       "      <td>POLYGON ((-102.04463 38.26241, -101.56748 38.2...</td>\n",
       "      <td>KS Hamilton County</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2379</th>\n",
       "      <td>Marshall County</td>\n",
       "      <td>KS</td>\n",
       "      <td>POLYGON ((-96.80620 39.82754, -96.80577 40.001...</td>\n",
       "      <td>KS Marshall County</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3680</th>\n",
       "      <td>Morris County</td>\n",
       "      <td>KS</td>\n",
       "      <td>POLYGON ((-96.93029 38.60936, -96.92723 38.812...</td>\n",
       "      <td>KS Morris County</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3681</th>\n",
       "      <td>Stafford County</td>\n",
       "      <td>KS</td>\n",
       "      <td>POLYGON ((-99.02214 38.00024, -98.91314 38.000...</td>\n",
       "      <td>KS Stafford County</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116299</th>\n",
       "      <td>Leavenworth County</td>\n",
       "      <td>KS</td>\n",
       "      <td>POLYGON ((-95.18710 39.04411, -95.18013 39.128...</td>\n",
       "      <td>KS Leavenworth County</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120285</th>\n",
       "      <td>Crawford County</td>\n",
       "      <td>KS</td>\n",
       "      <td>POLYGON ((-95.08801 37.67452, -94.61787 37.673...</td>\n",
       "      <td>KS Crawford County</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121933</th>\n",
       "      <td>Gray County</td>\n",
       "      <td>KS</td>\n",
       "      <td>POLYGON ((-100.66417 38.00253, -100.22661 38.0...</td>\n",
       "      <td>KS Gray County</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122198</th>\n",
       "      <td>Elk County</td>\n",
       "      <td>KS</td>\n",
       "      <td>POLYGON ((-96.52530 37.60701, -95.96100 37.603...</td>\n",
       "      <td>KS Elk County</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122433</th>\n",
       "      <td>Montgomery County</td>\n",
       "      <td>KS</td>\n",
       "      <td>POLYGON ((-95.96453 37.36358, -95.96161 37.386...</td>\n",
       "      <td>KS Montgomery County</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>105 rows × 14 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  NAMELSAD STUSPS  \\\n",
       "785          Sumner County     KS   \n",
       "915        Hamilton County     KS   \n",
       "2379       Marshall County     KS   \n",
       "3680         Morris County     KS   \n",
       "3681       Stafford County     KS   \n",
       "...                    ...    ...   \n",
       "116299  Leavenworth County     KS   \n",
       "120285     Crawford County     KS   \n",
       "121933         Gray County     KS   \n",
       "122198          Elk County     KS   \n",
       "122433   Montgomery County     KS   \n",
       "\n",
       "                                                 geometry  \\\n",
       "785     POLYGON ((-97.80760 37.47418, -97.15333 37.475...   \n",
       "915     POLYGON ((-102.04463 38.26241, -101.56748 38.2...   \n",
       "2379    POLYGON ((-96.80620 39.82754, -96.80577 40.001...   \n",
       "3680    POLYGON ((-96.93029 38.60936, -96.92723 38.812...   \n",
       "3681    POLYGON ((-99.02214 38.00024, -98.91314 38.000...   \n",
       "...                                                   ...   \n",
       "116299  POLYGON ((-95.18710 39.04411, -95.18013 39.128...   \n",
       "120285  POLYGON ((-95.08801 37.67452, -94.61787 37.673...   \n",
       "121933  POLYGON ((-100.66417 38.00253, -100.22661 38.0...   \n",
       "122198  POLYGON ((-96.52530 37.60701, -95.96100 37.603...   \n",
       "122433  POLYGON ((-95.96453 37.36358, -95.96161 37.386...   \n",
       "\n",
       "                       county AREA RACE SITE YEAR EVENT_TYPE_x  \\\n",
       "785          KS Sumner County  NaN  NaN  NaN  NaN          NaN   \n",
       "915        KS Hamilton County  NaN  NaN  NaN  NaN          NaN   \n",
       "2379       KS Marshall County  NaN  NaN  NaN  NaN          NaN   \n",
       "3680         KS Morris County  NaN  NaN  NaN  NaN          NaN   \n",
       "3681       KS Stafford County  NaN  NaN  NaN  NaN          NaN   \n",
       "...                       ...  ...  ...  ...  ...          ...   \n",
       "116299  KS Leavenworth County  NaN  NaN  NaN  NaN          NaN   \n",
       "120285     KS Crawford County  NaN  NaN  NaN  NaN          NaN   \n",
       "121933         KS Gray County  NaN  NaN  NaN  NaN          NaN   \n",
       "122198          KS Elk County  NaN  NaN  NaN  NaN          NaN   \n",
       "122433   KS Montgomery County  NaN  NaN  NaN  NaN          NaN   \n",
       "\n",
       "        AGE_ADJUSTED_RATE_x  SEX EVENT_TYPE_y  AGE_ADJUSTED_RATE_y  MIR  \n",
       "785                     NaN  NaN          NaN                  NaN  NaN  \n",
       "915                     NaN  NaN          NaN                  NaN  NaN  \n",
       "2379                    NaN  NaN          NaN                  NaN  NaN  \n",
       "3680                    NaN  NaN          NaN                  NaN  NaN  \n",
       "3681                    NaN  NaN          NaN                  NaN  NaN  \n",
       "...                     ...  ...          ...                  ...  ...  \n",
       "116299                  NaN  NaN          NaN                  NaN  NaN  \n",
       "120285                  NaN  NaN          NaN                  NaN  NaN  \n",
       "121933                  NaN  NaN          NaN                  NaN  NaN  \n",
       "122198                  NaN  NaN          NaN                  NaN  NaN  \n",
       "122433                  NaN  NaN          NaN                  NaN  NaN  \n",
       "\n",
       "[105 rows x 14 columns]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "state = test_gdf[\"STUSPS\"] == \"KS\"\n",
    "slice = [\"EVENT_TYPE\", \"county\"]\n",
    "race = test_gdf[\"RACE\"] == \"White\"\n",
    "\n",
    "test_gdf[state]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e13090af",
   "metadata": {},
   "outputs": [],
   "source": [
    "counties.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "0c9972ce",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>AREA</th>\n",
       "      <th>AGE_ADJUSTED_CI_LOWER</th>\n",
       "      <th>AGE_ADJUSTED_CI_UPPER</th>\n",
       "      <th>AGE_ADJUSTED_RATE</th>\n",
       "      <th>COUNT</th>\n",
       "      <th>EVENT_TYPE</th>\n",
       "      <th>POPULATION</th>\n",
       "      <th>RACE</th>\n",
       "      <th>SEX</th>\n",
       "      <th>SITE</th>\n",
       "      <th>YEAR</th>\n",
       "      <th>CRUDE_CI_LOWER</th>\n",
       "      <th>CRUDE_CI_UPPER</th>\n",
       "      <th>CRUDE_RATE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3003</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>87.7</td>\n",
       "      <td>335.7</td>\n",
       "      <td>185.8</td>\n",
       "      <td>~</td>\n",
       "      <td>Incidence</td>\n",
       "      <td>11411</td>\n",
       "      <td>American Indian/Alaska Native</td>\n",
       "      <td>Female</td>\n",
       "      <td>All Cancer Sites Combined</td>\n",
       "      <td>1999</td>\n",
       "      <td>54.3</td>\n",
       "      <td>183.7</td>\n",
       "      <td>105.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3004</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>0.4</td>\n",
       "      <td>88.5</td>\n",
       "      <td>17.7</td>\n",
       "      <td>~</td>\n",
       "      <td>Mortality</td>\n",
       "      <td>11411</td>\n",
       "      <td>American Indian/Alaska Native</td>\n",
       "      <td>Female</td>\n",
       "      <td>All Cancer Sites Combined</td>\n",
       "      <td>1999</td>\n",
       "      <td>0.2</td>\n",
       "      <td>48.8</td>\n",
       "      <td>8.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3005</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>72.5</td>\n",
       "      <td>275.7</td>\n",
       "      <td>152.5</td>\n",
       "      <td>~</td>\n",
       "      <td>Incidence</td>\n",
       "      <td>12506</td>\n",
       "      <td>American Indian/Alaska Native</td>\n",
       "      <td>Female</td>\n",
       "      <td>All Cancer Sites Combined</td>\n",
       "      <td>2000</td>\n",
       "      <td>49.6</td>\n",
       "      <td>167.6</td>\n",
       "      <td>96.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3006</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>35.4</td>\n",
       "      <td>222.9</td>\n",
       "      <td>104.1</td>\n",
       "      <td>~</td>\n",
       "      <td>Mortality</td>\n",
       "      <td>12506</td>\n",
       "      <td>American Indian/Alaska Native</td>\n",
       "      <td>Female</td>\n",
       "      <td>All Cancer Sites Combined</td>\n",
       "      <td>2000</td>\n",
       "      <td>17.6</td>\n",
       "      <td>104.4</td>\n",
       "      <td>48.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3007</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>45.2</td>\n",
       "      <td>216.8</td>\n",
       "      <td>109.9</td>\n",
       "      <td>~</td>\n",
       "      <td>Incidence</td>\n",
       "      <td>12933</td>\n",
       "      <td>American Indian/Alaska Native</td>\n",
       "      <td>Female</td>\n",
       "      <td>All Cancer Sites Combined</td>\n",
       "      <td>2001</td>\n",
       "      <td>31.8</td>\n",
       "      <td>132.1</td>\n",
       "      <td>69.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>914851</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>2.0</td>\n",
       "      <td>5.1</td>\n",
       "      <td>3.3</td>\n",
       "      <td>21</td>\n",
       "      <td>Mortality</td>\n",
       "      <td>549879</td>\n",
       "      <td>White</td>\n",
       "      <td>Male and Female</td>\n",
       "      <td>Urinary Bladder</td>\n",
       "      <td>2016</td>\n",
       "      <td>2.4</td>\n",
       "      <td>5.8</td>\n",
       "      <td>3.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>914852</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>18.2</td>\n",
       "      <td>25.5</td>\n",
       "      <td>21.6</td>\n",
       "      <td>150</td>\n",
       "      <td>Incidence</td>\n",
       "      <td>544322</td>\n",
       "      <td>White</td>\n",
       "      <td>Male and Female</td>\n",
       "      <td>Urinary Bladder</td>\n",
       "      <td>2017</td>\n",
       "      <td>23.3</td>\n",
       "      <td>32.3</td>\n",
       "      <td>27.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>914853</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>1.6</td>\n",
       "      <td>4.3</td>\n",
       "      <td>2.7</td>\n",
       "      <td>18</td>\n",
       "      <td>Mortality</td>\n",
       "      <td>544322</td>\n",
       "      <td>White</td>\n",
       "      <td>Male and Female</td>\n",
       "      <td>Urinary Bladder</td>\n",
       "      <td>2017</td>\n",
       "      <td>2.0</td>\n",
       "      <td>5.2</td>\n",
       "      <td>3.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>914854</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>16.8</td>\n",
       "      <td>23.8</td>\n",
       "      <td>20.0</td>\n",
       "      <td>139</td>\n",
       "      <td>Incidence</td>\n",
       "      <td>542445</td>\n",
       "      <td>White</td>\n",
       "      <td>Male and Female</td>\n",
       "      <td>Urinary Bladder</td>\n",
       "      <td>2018</td>\n",
       "      <td>21.5</td>\n",
       "      <td>30.3</td>\n",
       "      <td>25.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>914855</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>3.2</td>\n",
       "      <td>6.6</td>\n",
       "      <td>4.7</td>\n",
       "      <td>35</td>\n",
       "      <td>Mortality</td>\n",
       "      <td>542445</td>\n",
       "      <td>White</td>\n",
       "      <td>Male and Female</td>\n",
       "      <td>Urinary Bladder</td>\n",
       "      <td>2018</td>\n",
       "      <td>4.5</td>\n",
       "      <td>9.0</td>\n",
       "      <td>6.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>762380 rows × 14 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           AREA AGE_ADJUSTED_CI_LOWER AGE_ADJUSTED_CI_UPPER AGE_ADJUSTED_RATE  \\\n",
       "3003    Alabama                  87.7                 335.7             185.8   \n",
       "3004    Alabama                   0.4                  88.5              17.7   \n",
       "3005    Alabama                  72.5                 275.7             152.5   \n",
       "3006    Alabama                  35.4                 222.9             104.1   \n",
       "3007    Alabama                  45.2                 216.8             109.9   \n",
       "...         ...                   ...                   ...               ...   \n",
       "914851  Wyoming                   2.0                   5.1               3.3   \n",
       "914852  Wyoming                  18.2                  25.5              21.6   \n",
       "914853  Wyoming                   1.6                   4.3               2.7   \n",
       "914854  Wyoming                  16.8                  23.8              20.0   \n",
       "914855  Wyoming                   3.2                   6.6               4.7   \n",
       "\n",
       "       COUNT EVENT_TYPE  POPULATION                           RACE  \\\n",
       "3003       ~  Incidence       11411  American Indian/Alaska Native   \n",
       "3004       ~  Mortality       11411  American Indian/Alaska Native   \n",
       "3005       ~  Incidence       12506  American Indian/Alaska Native   \n",
       "3006       ~  Mortality       12506  American Indian/Alaska Native   \n",
       "3007       ~  Incidence       12933  American Indian/Alaska Native   \n",
       "...      ...        ...         ...                            ...   \n",
       "914851    21  Mortality      549879                          White   \n",
       "914852   150  Incidence      544322                          White   \n",
       "914853    18  Mortality      544322                          White   \n",
       "914854   139  Incidence      542445                          White   \n",
       "914855    35  Mortality      542445                          White   \n",
       "\n",
       "                    SEX                       SITE  YEAR CRUDE_CI_LOWER  \\\n",
       "3003             Female  All Cancer Sites Combined  1999           54.3   \n",
       "3004             Female  All Cancer Sites Combined  1999            0.2   \n",
       "3005             Female  All Cancer Sites Combined  2000           49.6   \n",
       "3006             Female  All Cancer Sites Combined  2000           17.6   \n",
       "3007             Female  All Cancer Sites Combined  2001           31.8   \n",
       "...                 ...                        ...   ...            ...   \n",
       "914851  Male and Female            Urinary Bladder  2016            2.4   \n",
       "914852  Male and Female            Urinary Bladder  2017           23.3   \n",
       "914853  Male and Female            Urinary Bladder  2017            2.0   \n",
       "914854  Male and Female            Urinary Bladder  2018           21.5   \n",
       "914855  Male and Female            Urinary Bladder  2018            4.5   \n",
       "\n",
       "       CRUDE_CI_UPPER CRUDE_RATE  \n",
       "3003            183.7      105.2  \n",
       "3004             48.8        8.8  \n",
       "3005            167.6       96.0  \n",
       "3006            104.4       48.0  \n",
       "3007            132.1       69.6  \n",
       "...               ...        ...  \n",
       "914851            5.8        3.8  \n",
       "914852           32.3       27.6  \n",
       "914853            5.2        3.3  \n",
       "914854           30.3       25.6  \n",
       "914855            9.0        6.5  \n",
       "\n",
       "[762380 rows x 14 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "by_area[by_area[\"RACE\"] != \"All Races\"]"
   ]
  }
 ],
 "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.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}