research/240619_EDA_geo.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## EDA 결과 요약\n",
    "- raw data의 31개 columns 중 record의 geo관련 정보가 포함된 12개의 column에 대한 조사\n",
    "- 각 도시 및 통계 항목 별로 통일 되어있는지 조사 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np \n",
    "import pandas as pd \n",
    "import matplotlib as mpl\n",
    "import matplotlib.pyplot as plt \n",
    "import missingno as msno\n",
    "import seaborn as sns \n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib_inline.backend_inline\n",
    "\n",
    "matplotlib_inline.backend_inline.set_matplotlib_formats(\"png2x\")\n",
    "# 테마 설정: \"default\", \"classic\", \"dark_background\", \"fivethirtyeight\", \"seaborn\"\n",
    "mpl.style.use(\"fivethirtyeight\")\n",
    "# 이미지가 레이아웃 안으로 들어오도록 함\n",
    "mpl.rcParams.update({\"figure.constrained_layout.use\": True})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.font_manager as fm\n",
    "font_list = fm.findSystemFonts(fontpaths=None, fontext='ttf')\n",
    "[fm.FontProperties(fname=font).get_name() for font in font_list if 'D2C' in font]\n",
    "plt.rc('font', family='D2Coding')\n",
    "mpl.rcParams['axes.unicode_minus'] = False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "DATASET_DIR = '/home/doeun/code/AI/ESTSOFT2024/workspace/dataset/'\n",
    "dir_name = 'america_big_cities_health_inventory'\n",
    "file_name = 'BigCitiesHealth.csv'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "RSLT_DIR = '/home/doeun/code/AI/ESTSOFT2024/workspace/1.project1_structured/BCHI/processed/'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_296727/664991473.py:2: DtypeWarning: Columns (30) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  df = pd.read_csv(file_path)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 189979 entries, 0 to 189978\n",
      "Data columns (total 31 columns):\n",
      " #   Column                           Non-Null Count   Dtype  \n",
      "---  ------                           --------------   -----  \n",
      " 0   metric_item_label                189979 non-null  object \n",
      " 1   metric_cat_label                 189979 non-null  object \n",
      " 2   metric_subcat_label              189979 non-null  object \n",
      " 3   metric_item_label_subtitle       189979 non-null  object \n",
      " 4   metric_cat_item_yaxis_label      189979 non-null  object \n",
      " 5   metric_source_desc_label_fn      189979 non-null  object \n",
      " 6   metric_source_desc_label_url_fn  189979 non-null  object \n",
      " 7   geo_label_city                   189979 non-null  object \n",
      " 8   geo_label_state                  188667 non-null  object \n",
      " 9   geo_label_citystate              189979 non-null  object \n",
      " 10  geo_fips_code                    188667 non-null  float64\n",
      " 11  value                            189979 non-null  float64\n",
      " 12  date_label                       189979 non-null  int64  \n",
      " 13  geo_label_proxy_or_real          189979 non-null  object \n",
      " 14  geo_label_proxy_footnote         16804 non-null   object \n",
      " 15  geo_fips_desc                    189979 non-null  object \n",
      " 16  date_label_proxy_or_real         189979 non-null  object \n",
      " 17  date_label_proxy_footnote        54759 non-null   object \n",
      " 18  value_ci_flag_yesno              189979 non-null  object \n",
      " 19  value_95_ci_low                  118670 non-null  float64\n",
      " 20  value_95_ci_high                 118682 non-null  float64\n",
      " 21  value_90_ci_low                  0 non-null       float64\n",
      " 22  value_90_ci_high                 0 non-null       float64\n",
      " 23  geo_strata_region                188667 non-null  object \n",
      " 24  geo_strata_poverty               188667 non-null  object \n",
      " 25  geo_strata_Population            188666 non-null  object \n",
      " 26  geo_strata_PopDensity            188667 non-null  object \n",
      " 27  geo_strata_Segregation           188667 non-null  object \n",
      " 28  strata_race_label                189979 non-null  object \n",
      " 29  strata_sex_label                 189979 non-null  object \n",
      " 30  strata_race_sex_label            46662 non-null   object \n",
      "dtypes: float64(6), int64(1), object(24)\n",
      "memory usage: 44.9+ MB\n"
     ]
    }
   ],
   "source": [
    "file_path = os.path.join(DATASET_DIR,dir_name,file_name)\n",
    "df = pd.read_csv(file_path)\n",
    "\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"metric_label\"] = df[\"metric_subcat_label\"]+' | '+df[\"metric_item_label\"] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "metric_label\n",
       "Deaths | Premature Death                                  6825\n",
       "Deaths | Injury Deaths                                    6405\n",
       "Deaths | Deaths from All Causes                           6244\n",
       "Cancer | All Cancer Deaths                                6233\n",
       "Cardiovascular Disease | Cardiovascular Disease Deaths    6195\n",
       "                                                          ... \n",
       "Housing | Homeless, Total                                  266\n",
       "Housing | Vacant Housing and Homelessness                  266\n",
       "Housing | Homeless, Children                               256\n",
       "Respiratory Infection | COVID-19 Vaccination, Adults        70\n",
       "Respiratory Infection | COVID-19 Vaccination, Seniors       70\n",
       "Name: count, Length: 118, dtype: int64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"metric_label\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['metric_item_label', 'metric_cat_label', 'metric_subcat_label',\n",
       "       'metric_item_label_subtitle', 'metric_cat_item_yaxis_label',\n",
       "       'metric_source_desc_label_fn', 'metric_source_desc_label_url_fn',\n",
       "       'geo_label_city', 'geo_label_state', 'geo_label_citystate',\n",
       "       'geo_fips_code', 'value', 'date_label', 'geo_label_proxy_or_real',\n",
       "       'geo_label_proxy_footnote', 'geo_fips_desc', 'date_label_proxy_or_real',\n",
       "       'date_label_proxy_footnote', 'value_ci_flag_yesno', 'value_95_ci_low',\n",
       "       'value_95_ci_high', 'value_90_ci_low', 'value_90_ci_high',\n",
       "       'geo_strata_region', 'geo_strata_poverty', 'geo_strata_Population',\n",
       "       'geo_strata_PopDensity', 'geo_strata_Segregation', 'strata_race_label',\n",
       "       'strata_sex_label', 'strata_race_sex_label', 'metric_label'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1. geo들어간 데이터 체크"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1312"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "188667"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "cond = df['geo_label_city'] == 'U.S. Total'\n",
    "\n",
    "df_US = df[cond]\n",
    "df_city = df[~cond]\n",
    "\n",
    "display(len(df_US))\n",
    "display(len(df_city))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['geo_label_city',\n",
       " 'geo_label_state',\n",
       " 'geo_label_citystate',\n",
       " 'geo_fips_code',\n",
       " 'geo_label_proxy_or_real',\n",
       " 'geo_label_proxy_footnote',\n",
       " 'geo_fips_desc',\n",
       " 'geo_strata_region',\n",
       " 'geo_strata_poverty',\n",
       " 'geo_strata_Population',\n",
       " 'geo_strata_PopDensity',\n",
       " 'geo_strata_Segregation']"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(filter(lambda x: 'geo' in x,df.columns))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## geo_proxy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "geo_label_proxy_footnote\n",
       "Data are for school districts    9867\n",
       "County level data were used      6655\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "geo_label_proxy_or_real\n",
       "real     162663\n",
       "proxy     26004\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "geo_label_proxy_footnote\n",
       "Data are for school districts    198\n",
       "Average for BCHC cities           52\n",
       "County level data were used       20\n",
       "Average of all BCHC cities        12\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "geo_label_proxy_or_real\n",
       "real     878\n",
       "proxy    434\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(df_city.geo_label_proxy_footnote.value_counts())\n",
    "display(df_city.geo_label_proxy_or_real.value_counts())\n",
    "display(df_US.geo_label_proxy_footnote.value_counts())\n",
    "display(df_US.geo_label_proxy_or_real.value_counts())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "172145"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "1030"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(np.sum(df_city.geo_label_proxy_footnote.isna()))\n",
    "display(np.sum(df_city.geo_label_proxy_or_real.isna()))\n",
    "display(np.sum(df_US.geo_label_proxy_footnote.isna()))\n",
    "display(np.sum(df_US.geo_label_proxy_or_real.isna()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "geo_label_proxy_or_real\n",
       "real     162663\n",
       "proxy      9482\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "cond = df_city.geo_label_proxy_footnote.isna()\n",
    "\n",
    "display(df_city.loc[cond,'geo_label_proxy_or_real'].value_counts())\n",
    "display(np.sum(df_city.loc[cond,'geo_label_proxy_or_real'].isna()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>metric_label</th>\n",
       "      <th>geo_label_proxy_or_real</th>\n",
       "      <th>geo_label_proxy_footnote</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Substance Use | Teen Marijuana</td>\n",
       "      <td>proxy</td>\n",
       "      <td>Data are for school districts</td>\n",
       "      <td>563</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Diabetes and Obesity | Teen Obesity</td>\n",
       "      <td>proxy</td>\n",
       "      <td>Data are for school districts</td>\n",
       "      <td>560</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Substance Use | Teen Alcohol</td>\n",
       "      <td>proxy</td>\n",
       "      <td>Data are for school districts</td>\n",
       "      <td>557</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Physical Activity | Teen Physical Activity</td>\n",
       "      <td>proxy</td>\n",
       "      <td>Data are for school districts</td>\n",
       "      <td>555</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Mental Health | School Bullying</td>\n",
       "      <td>proxy</td>\n",
       "      <td>Data are for school districts</td>\n",
       "      <td>548</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Mental Health | Electronic Bullying</td>\n",
       "      <td>proxy</td>\n",
       "      <td>Data are for school districts</td>\n",
       "      <td>547</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Mental Health | Teen Mental Distress</td>\n",
       "      <td>proxy</td>\n",
       "      <td>Data are for school districts</td>\n",
       "      <td>547</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Substance Use | Teen Smoking</td>\n",
       "      <td>proxy</td>\n",
       "      <td>Data are for school districts</td>\n",
       "      <td>545</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Physical Activity | Teen TV Time</td>\n",
       "      <td>proxy</td>\n",
       "      <td>Data are for school districts</td>\n",
       "      <td>544</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Physical Activity | Teen Physical Inactivity</td>\n",
       "      <td>proxy</td>\n",
       "      <td>Data are for school districts</td>\n",
       "      <td>538</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>School Violence | Fighting in School</td>\n",
       "      <td>proxy</td>\n",
       "      <td>Data are for school districts</td>\n",
       "      <td>520</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Physical Activity | Teen Computer Time</td>\n",
       "      <td>proxy</td>\n",
       "      <td>Data are for school districts</td>\n",
       "      <td>518</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>School Violence | Weapons in School</td>\n",
       "      <td>proxy</td>\n",
       "      <td>Data are for school districts</td>\n",
       "      <td>509</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Dietary Quality | Teen Soda</td>\n",
       "      <td>proxy</td>\n",
       "      <td>Data are for school districts</td>\n",
       "      <td>507</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Respiratory Disease | Teen Asthma</td>\n",
       "      <td>proxy</td>\n",
       "      <td>Data are for school districts</td>\n",
       "      <td>506</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Mental Health | Teen Suicidal Ideation</td>\n",
       "      <td>proxy</td>\n",
       "      <td>Data are for school districts</td>\n",
       "      <td>497</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Dietary Quality | Teen Breakfast</td>\n",
       "      <td>proxy</td>\n",
       "      <td>Data are for school districts</td>\n",
       "      <td>475</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Physical Activity | Teen Physical Education</td>\n",
       "      <td>proxy</td>\n",
       "      <td>Data are for school districts</td>\n",
       "      <td>444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Deaths | Premature Death</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>390</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Birth Control | Teen Birth Control</td>\n",
       "      <td>proxy</td>\n",
       "      <td>Data are for school districts</td>\n",
       "      <td>387</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Deaths | Injury Deaths</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>370</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Substance Use | Drug Overdose Deaths</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>326</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Deaths | Gun Deaths (Firearms)</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>315</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Mental Health | Suicide</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>315</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Cancer | All Cancer Deaths</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>312</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Cardiovascular Disease | Cardiovascular Diseas...</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>311</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Cardiovascular Disease | Heart Disease Deaths</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>311</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Crime Incidents | Homicides</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>310</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Deaths | Motor Vehicle Deaths</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Deaths | Deaths from All Causes</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Substance Use | Opioid Overdose Deaths</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>305</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Cancer | Colorectal Cancer Deaths</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>304</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>Diabetes and Obesity | Diabetes Deaths</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>299</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Cancer | Lung Cancer Deaths</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>296</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Respiratory Infection | Pneumonia or Influenza...</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>292</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Life Expectancy at Birth | Life Expectancy</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>289</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Sexually Transmitted Disease | HIV-Related Deaths</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>234</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Housing | Homeless, Total</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>178</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>Housing | Vacant Housing and Homelessness</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>170</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>Housing | Homeless, Children</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>160</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>Births | Teen Births</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>129</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>Cancer | Prostate Cancer Deaths</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>Cancer | Breast Cancer Deaths</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>Respiratory Infection | COVID-19 Deaths</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>Respiratory Infection | COVID-19 Vaccination, ...</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>Respiratory Infection | COVID-19 Vaccination, ...</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         metric_label geo_label_proxy_or_real  \\\n",
       "0                      Substance Use | Teen Marijuana                   proxy   \n",
       "1                 Diabetes and Obesity | Teen Obesity                   proxy   \n",
       "2                        Substance Use | Teen Alcohol                   proxy   \n",
       "3          Physical Activity | Teen Physical Activity                   proxy   \n",
       "4                     Mental Health | School Bullying                   proxy   \n",
       "5                 Mental Health | Electronic Bullying                   proxy   \n",
       "6                Mental Health | Teen Mental Distress                   proxy   \n",
       "7                        Substance Use | Teen Smoking                   proxy   \n",
       "8                    Physical Activity | Teen TV Time                   proxy   \n",
       "9        Physical Activity | Teen Physical Inactivity                   proxy   \n",
       "10               School Violence | Fighting in School                   proxy   \n",
       "11             Physical Activity | Teen Computer Time                   proxy   \n",
       "12                School Violence | Weapons in School                   proxy   \n",
       "13                        Dietary Quality | Teen Soda                   proxy   \n",
       "14                  Respiratory Disease | Teen Asthma                   proxy   \n",
       "15             Mental Health | Teen Suicidal Ideation                   proxy   \n",
       "16                   Dietary Quality | Teen Breakfast                   proxy   \n",
       "17        Physical Activity | Teen Physical Education                   proxy   \n",
       "18                           Deaths | Premature Death                   proxy   \n",
       "19                 Birth Control | Teen Birth Control                   proxy   \n",
       "20                             Deaths | Injury Deaths                   proxy   \n",
       "21               Substance Use | Drug Overdose Deaths                   proxy   \n",
       "22                     Deaths | Gun Deaths (Firearms)                   proxy   \n",
       "23                            Mental Health | Suicide                   proxy   \n",
       "24                         Cancer | All Cancer Deaths                   proxy   \n",
       "25  Cardiovascular Disease | Cardiovascular Diseas...                   proxy   \n",
       "26      Cardiovascular Disease | Heart Disease Deaths                   proxy   \n",
       "27                        Crime Incidents | Homicides                   proxy   \n",
       "28                      Deaths | Motor Vehicle Deaths                   proxy   \n",
       "29                    Deaths | Deaths from All Causes                   proxy   \n",
       "30             Substance Use | Opioid Overdose Deaths                   proxy   \n",
       "31                  Cancer | Colorectal Cancer Deaths                   proxy   \n",
       "32             Diabetes and Obesity | Diabetes Deaths                   proxy   \n",
       "33                        Cancer | Lung Cancer Deaths                   proxy   \n",
       "34  Respiratory Infection | Pneumonia or Influenza...                   proxy   \n",
       "35         Life Expectancy at Birth | Life Expectancy                   proxy   \n",
       "36  Sexually Transmitted Disease | HIV-Related Deaths                   proxy   \n",
       "37                          Housing | Homeless, Total                   proxy   \n",
       "38          Housing | Vacant Housing and Homelessness                   proxy   \n",
       "39                       Housing | Homeless, Children                   proxy   \n",
       "40                               Births | Teen Births                   proxy   \n",
       "41                    Cancer | Prostate Cancer Deaths                   proxy   \n",
       "42                      Cancer | Breast Cancer Deaths                   proxy   \n",
       "43            Respiratory Infection | COVID-19 Deaths                   proxy   \n",
       "44  Respiratory Infection | COVID-19 Vaccination, ...                   proxy   \n",
       "45  Respiratory Infection | COVID-19 Vaccination, ...                   proxy   \n",
       "\n",
       "         geo_label_proxy_footnote  count  \n",
       "0   Data are for school districts    563  \n",
       "1   Data are for school districts    560  \n",
       "2   Data are for school districts    557  \n",
       "3   Data are for school districts    555  \n",
       "4   Data are for school districts    548  \n",
       "5   Data are for school districts    547  \n",
       "6   Data are for school districts    547  \n",
       "7   Data are for school districts    545  \n",
       "8   Data are for school districts    544  \n",
       "9   Data are for school districts    538  \n",
       "10  Data are for school districts    520  \n",
       "11  Data are for school districts    518  \n",
       "12  Data are for school districts    509  \n",
       "13  Data are for school districts    507  \n",
       "14  Data are for school districts    506  \n",
       "15  Data are for school districts    497  \n",
       "16  Data are for school districts    475  \n",
       "17  Data are for school districts    444  \n",
       "18    County level data were used    390  \n",
       "19  Data are for school districts    387  \n",
       "20    County level data were used    370  \n",
       "21    County level data were used    326  \n",
       "22    County level data were used    315  \n",
       "23    County level data were used    315  \n",
       "24    County level data were used    312  \n",
       "25    County level data were used    311  \n",
       "26    County level data were used    311  \n",
       "27    County level data were used    310  \n",
       "28    County level data were used    308  \n",
       "29    County level data were used    308  \n",
       "30    County level data were used    305  \n",
       "31    County level data were used    304  \n",
       "32    County level data were used    299  \n",
       "33    County level data were used    296  \n",
       "34    County level data were used    292  \n",
       "35    County level data were used    289  \n",
       "36    County level data were used    234  \n",
       "37    County level data were used    178  \n",
       "38    County level data were used    170  \n",
       "39    County level data were used    160  \n",
       "40    County level data were used    129  \n",
       "41    County level data were used    104  \n",
       "42    County level data were used    104  \n",
       "43    County level data were used     79  \n",
       "44    County level data were used     68  \n",
       "45    County level data were used     68  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_geoproxy_counts =df_city[['metric_label','geo_label_proxy_or_real','geo_label_proxy_footnote']].value_counts().reset_index()\n",
    "df_geoproxy_counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.sum(df_geoproxy_counts.duplicated(subset=['metric_label'],keep=False))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "54"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_temp = df_city.copy()\n",
    "df_temp['geo_label_proxy_footnote'] = df_city['geo_label_proxy_footnote'].fillna('NAN')\n",
    "df_geoproxy_counts2 =df_temp[['metric_label','geo_label_proxy_or_real','geo_label_proxy_footnote']].value_counts().reset_index()\n",
    "\n",
    "np.sum(df_geoproxy_counts2.duplicated(subset=['metric_label'],keep=False))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "geo_label_proxy_or_real\n",
       "real     86\n",
       "proxy    59\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_geoproxy_counts2['geo_label_proxy_or_real'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>metric_label</th>\n",
       "      <th>geo_label_proxy_or_real</th>\n",
       "      <th>geo_label_proxy_footnote</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>134</th>\n",
       "      <td>Births | Teen Births</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>129</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Births | Teen Births</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>2106</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>117</th>\n",
       "      <td>Cancer | All Cancer Deaths</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>312</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Cancer | All Cancer Deaths</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>5908</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136</th>\n",
       "      <td>Cancer | Breast Cancer Deaths</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>Cancer | Breast Cancer Deaths</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>1669</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <td>Cancer | Colorectal Cancer Deaths</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>304</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Cancer | Colorectal Cancer Deaths</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>4740</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126</th>\n",
       "      <td>Cancer | Lung Cancer Deaths</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>296</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Cancer | Lung Cancer Deaths</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>5179</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>135</th>\n",
       "      <td>Cancer | Prostate Cancer Deaths</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>Cancer | Prostate Cancer Deaths</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>1609</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118</th>\n",
       "      <td>Cardiovascular Disease | Cardiovascular Diseas...</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>311</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Cardiovascular Disease | Cardiovascular Diseas...</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>5871</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>Cardiovascular Disease | Heart Disease Deaths</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>311</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Cardiovascular Disease | Heart Disease Deaths</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>5814</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>Crime Incidents | Homicides</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>310</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Crime Incidents | Homicides</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>4430</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>Deaths | Deaths from All Causes</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Deaths | Deaths from All Causes</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>5923</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116</th>\n",
       "      <td>Deaths | Gun Deaths (Firearms)</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>315</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Deaths | Gun Deaths (Firearms)</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>4589</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>Deaths | Injury Deaths</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>370</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Deaths | Injury Deaths</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>6022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>Deaths | Motor Vehicle Deaths</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Deaths | Motor Vehicle Deaths</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>4804</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>104</th>\n",
       "      <td>Deaths | Premature Death</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>390</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Deaths | Premature Death</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>6435</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125</th>\n",
       "      <td>Diabetes and Obesity | Diabetes Deaths</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>299</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Diabetes and Obesity | Diabetes Deaths</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>5177</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>133</th>\n",
       "      <td>Housing | Homeless, Children</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>160</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>138</th>\n",
       "      <td>Housing | Homeless, Children</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>131</th>\n",
       "      <td>Housing | Homeless, Total</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>178</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>137</th>\n",
       "      <td>Housing | Homeless, Total</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <td>Housing | Vacant Housing and Homelessness</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>170</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>139</th>\n",
       "      <td>Housing | Vacant Housing and Homelessness</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129</th>\n",
       "      <td>Lead Poisoning | Child Lead Levels 10+ mcg/dL</td>\n",
       "      <td>proxy</td>\n",
       "      <td>NAN</td>\n",
       "      <td>267</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143</th>\n",
       "      <td>Lead Poisoning | Child Lead Levels 10+ mcg/dL</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113</th>\n",
       "      <td>Lead Poisoning | Child Lead Levels 5+ mcg/dL</td>\n",
       "      <td>proxy</td>\n",
       "      <td>NAN</td>\n",
       "      <td>344</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144</th>\n",
       "      <td>Lead Poisoning | Child Lead Levels 5+ mcg/dL</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128</th>\n",
       "      <td>Life Expectancy at Birth | Life Expectancy</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>289</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Life Expectancy at Birth | Life Expectancy</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>4703</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115</th>\n",
       "      <td>Mental Health | Suicide</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>315</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Mental Health | Suicide</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>4822</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>140</th>\n",
       "      <td>Respiratory Infection | COVID-19 Deaths</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>Respiratory Infection | COVID-19 Deaths</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>1317</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127</th>\n",
       "      <td>Respiratory Infection | Pneumonia or Influenza...</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>292</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Respiratory Infection | Pneumonia or Influenza...</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>4535</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130</th>\n",
       "      <td>Sexually Transmitted Disease | HIV-Related Deaths</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>234</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Sexually Transmitted Disease | HIV-Related Deaths</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>3134</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114</th>\n",
       "      <td>Substance Use | Drug Overdose Deaths</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>326</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Substance Use | Drug Overdose Deaths</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>5124</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>Substance Use | Opioid Overdose Deaths</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>305</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Substance Use | Opioid Overdose Deaths</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>4509</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          metric_label  \\\n",
       "134                               Births | Teen Births   \n",
       "34                                Births | Teen Births   \n",
       "117                         Cancer | All Cancer Deaths   \n",
       "3                           Cancer | All Cancer Deaths   \n",
       "136                      Cancer | Breast Cancer Deaths   \n",
       "38                       Cancer | Breast Cancer Deaths   \n",
       "124                  Cancer | Colorectal Cancer Deaths   \n",
       "11                   Cancer | Colorectal Cancer Deaths   \n",
       "126                        Cancer | Lung Cancer Deaths   \n",
       "6                          Cancer | Lung Cancer Deaths   \n",
       "135                    Cancer | Prostate Cancer Deaths   \n",
       "39                     Cancer | Prostate Cancer Deaths   \n",
       "118  Cardiovascular Disease | Cardiovascular Diseas...   \n",
       "4    Cardiovascular Disease | Cardiovascular Diseas...   \n",
       "119      Cardiovascular Disease | Heart Disease Deaths   \n",
       "5        Cardiovascular Disease | Heart Disease Deaths   \n",
       "120                        Crime Incidents | Homicides   \n",
       "16                         Crime Incidents | Homicides   \n",
       "121                    Deaths | Deaths from All Causes   \n",
       "2                      Deaths | Deaths from All Causes   \n",
       "116                     Deaths | Gun Deaths (Firearms)   \n",
       "13                      Deaths | Gun Deaths (Firearms)   \n",
       "106                             Deaths | Injury Deaths   \n",
       "1                               Deaths | Injury Deaths   \n",
       "122                      Deaths | Motor Vehicle Deaths   \n",
       "10                       Deaths | Motor Vehicle Deaths   \n",
       "104                           Deaths | Premature Death   \n",
       "0                             Deaths | Premature Death   \n",
       "125             Diabetes and Obesity | Diabetes Deaths   \n",
       "7               Diabetes and Obesity | Diabetes Deaths   \n",
       "133                       Housing | Homeless, Children   \n",
       "138                       Housing | Homeless, Children   \n",
       "131                          Housing | Homeless, Total   \n",
       "137                          Housing | Homeless, Total   \n",
       "132          Housing | Vacant Housing and Homelessness   \n",
       "139          Housing | Vacant Housing and Homelessness   \n",
       "129      Lead Poisoning | Child Lead Levels 10+ mcg/dL   \n",
       "143      Lead Poisoning | Child Lead Levels 10+ mcg/dL   \n",
       "113       Lead Poisoning | Child Lead Levels 5+ mcg/dL   \n",
       "144       Lead Poisoning | Child Lead Levels 5+ mcg/dL   \n",
       "128         Life Expectancy at Birth | Life Expectancy   \n",
       "12          Life Expectancy at Birth | Life Expectancy   \n",
       "115                            Mental Health | Suicide   \n",
       "9                              Mental Health | Suicide   \n",
       "140            Respiratory Infection | COVID-19 Deaths   \n",
       "42             Respiratory Infection | COVID-19 Deaths   \n",
       "127  Respiratory Infection | Pneumonia or Influenza...   \n",
       "14   Respiratory Infection | Pneumonia or Influenza...   \n",
       "130  Sexually Transmitted Disease | HIV-Related Deaths   \n",
       "17   Sexually Transmitted Disease | HIV-Related Deaths   \n",
       "114               Substance Use | Drug Overdose Deaths   \n",
       "8                 Substance Use | Drug Overdose Deaths   \n",
       "123             Substance Use | Opioid Overdose Deaths   \n",
       "15              Substance Use | Opioid Overdose Deaths   \n",
       "\n",
       "    geo_label_proxy_or_real     geo_label_proxy_footnote  count  \n",
       "134                   proxy  County level data were used    129  \n",
       "34                     real                          NAN   2106  \n",
       "117                   proxy  County level data were used    312  \n",
       "3                      real                          NAN   5908  \n",
       "136                   proxy  County level data were used    104  \n",
       "38                     real                          NAN   1669  \n",
       "124                   proxy  County level data were used    304  \n",
       "11                     real                          NAN   4740  \n",
       "126                   proxy  County level data were used    296  \n",
       "6                      real                          NAN   5179  \n",
       "135                   proxy  County level data were used    104  \n",
       "39                     real                          NAN   1609  \n",
       "118                   proxy  County level data were used    311  \n",
       "4                      real                          NAN   5871  \n",
       "119                   proxy  County level data were used    311  \n",
       "5                      real                          NAN   5814  \n",
       "120                   proxy  County level data were used    310  \n",
       "16                     real                          NAN   4430  \n",
       "121                   proxy  County level data were used    308  \n",
       "2                      real                          NAN   5923  \n",
       "116                   proxy  County level data were used    315  \n",
       "13                     real                          NAN   4589  \n",
       "106                   proxy  County level data were used    370  \n",
       "1                      real                          NAN   6022  \n",
       "122                   proxy  County level data were used    308  \n",
       "10                     real                          NAN   4804  \n",
       "104                   proxy  County level data were used    390  \n",
       "0                      real                          NAN   6435  \n",
       "125                   proxy  County level data were used    299  \n",
       "7                      real                          NAN   5177  \n",
       "133                   proxy  County level data were used    160  \n",
       "138                    real                          NAN     88  \n",
       "131                   proxy  County level data were used    178  \n",
       "137                    real                          NAN     88  \n",
       "132                   proxy  County level data were used    170  \n",
       "139                    real                          NAN     88  \n",
       "129                   proxy                          NAN    267  \n",
       "143                    real                          NAN     11  \n",
       "113                   proxy                          NAN    344  \n",
       "144                    real                          NAN     11  \n",
       "128                   proxy  County level data were used    289  \n",
       "12                     real                          NAN   4703  \n",
       "115                   proxy  County level data were used    315  \n",
       "9                      real                          NAN   4822  \n",
       "140                   proxy  County level data were used     79  \n",
       "42                     real                          NAN   1317  \n",
       "127                   proxy  County level data were used    292  \n",
       "14                     real                          NAN   4535  \n",
       "130                   proxy  County level data were used    234  \n",
       "17                     real                          NAN   3134  \n",
       "114                   proxy  County level data were used    326  \n",
       "8                      real                          NAN   5124  \n",
       "123                   proxy  County level data were used    305  \n",
       "15                     real                          NAN   4509  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cond_dup = (df_geoproxy_counts2.duplicated(subset=['metric_label'],keep=False))\n",
    "df_geoproxy_counts2[cond_dup].sort_values(by=['metric_label','geo_label_proxy_or_real'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "geo_label_proxy_or_real\n",
       "real     86\n",
       "proxy    13\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cond = df_geoproxy_counts2['geo_label_proxy_footnote'] == 'NAN'\n",
    "df_geoproxy_counts2.loc[cond,'geo_label_proxy_or_real'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>metric_label</th>\n",
       "      <th>geo_label_proxy_or_real</th>\n",
       "      <th>geo_label_proxy_footnote</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Sexually Transmitted Disease | HIV/AIDS Preval...</td>\n",
       "      <td>proxy</td>\n",
       "      <td>NAN</td>\n",
       "      <td>2897</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Respiratory Infection | Flu Vaccinations, Medi...</td>\n",
       "      <td>proxy</td>\n",
       "      <td>NAN</td>\n",
       "      <td>1995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>Respiratory Infection | New Tuberculosis Cases</td>\n",
       "      <td>proxy</td>\n",
       "      <td>NAN</td>\n",
       "      <td>455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>Sexually Transmitted Disease | Syphilis, Newborns</td>\n",
       "      <td>proxy</td>\n",
       "      <td>NAN</td>\n",
       "      <td>455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>Heat and Disasters | Climate-related  Disasters</td>\n",
       "      <td>proxy</td>\n",
       "      <td>NAN</td>\n",
       "      <td>455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>Air Pollution | Hazardous Air Quality</td>\n",
       "      <td>proxy</td>\n",
       "      <td>NAN</td>\n",
       "      <td>454</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>Air Pollution | Poor Air Quality</td>\n",
       "      <td>proxy</td>\n",
       "      <td>NAN</td>\n",
       "      <td>454</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>Sexually Transmitted Disease | Syphilis Preval...</td>\n",
       "      <td>proxy</td>\n",
       "      <td>NAN</td>\n",
       "      <td>452</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>Sexually Transmitted Disease | New Gonorrhea C...</td>\n",
       "      <td>proxy</td>\n",
       "      <td>NAN</td>\n",
       "      <td>452</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>Sexually Transmitted Disease | New Chlamydia C...</td>\n",
       "      <td>proxy</td>\n",
       "      <td>NAN</td>\n",
       "      <td>452</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>111</th>\n",
       "      <td>Lead Poisoning | Child Lead Testing</td>\n",
       "      <td>proxy</td>\n",
       "      <td>NAN</td>\n",
       "      <td>350</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113</th>\n",
       "      <td>Lead Poisoning | Child Lead Levels 5+ mcg/dL</td>\n",
       "      <td>proxy</td>\n",
       "      <td>NAN</td>\n",
       "      <td>344</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129</th>\n",
       "      <td>Lead Poisoning | Child Lead Levels 10+ mcg/dL</td>\n",
       "      <td>proxy</td>\n",
       "      <td>NAN</td>\n",
       "      <td>267</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          metric_label  \\\n",
       "18   Sexually Transmitted Disease | HIV/AIDS Preval...   \n",
       "37   Respiratory Infection | Flu Vaccinations, Medi...   \n",
       "65      Respiratory Infection | New Tuberculosis Cases   \n",
       "70   Sexually Transmitted Disease | Syphilis, Newborns   \n",
       "86     Heat and Disasters | Climate-related  Disasters   \n",
       "96               Air Pollution | Hazardous Air Quality   \n",
       "97                    Air Pollution | Poor Air Quality   \n",
       "98   Sexually Transmitted Disease | Syphilis Preval...   \n",
       "99   Sexually Transmitted Disease | New Gonorrhea C...   \n",
       "100  Sexually Transmitted Disease | New Chlamydia C...   \n",
       "111                Lead Poisoning | Child Lead Testing   \n",
       "113       Lead Poisoning | Child Lead Levels 5+ mcg/dL   \n",
       "129      Lead Poisoning | Child Lead Levels 10+ mcg/dL   \n",
       "\n",
       "    geo_label_proxy_or_real geo_label_proxy_footnote  count  \n",
       "18                    proxy                      NAN   2897  \n",
       "37                    proxy                      NAN   1995  \n",
       "65                    proxy                      NAN    455  \n",
       "70                    proxy                      NAN    455  \n",
       "86                    proxy                      NAN    455  \n",
       "96                    proxy                      NAN    454  \n",
       "97                    proxy                      NAN    454  \n",
       "98                    proxy                      NAN    452  \n",
       "99                    proxy                      NAN    452  \n",
       "100                   proxy                      NAN    452  \n",
       "111                   proxy                      NAN    350  \n",
       "113                   proxy                      NAN    344  \n",
       "129                   proxy                      NAN    267  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cond0 = df_geoproxy_counts2['geo_label_proxy_footnote'] == 'NAN'\n",
    "cond1 = df_geoproxy_counts2['geo_label_proxy_or_real'] == 'proxy'\n",
    "df_geoproxy_counts2.loc[cond0 & cond1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- geo_proxy_footnote가 na인 경우를 제외하면\n",
    "    - metric_label 별로, geo_proxy 들은 통일 되어있는 것을 확인 \n",
    "- geo_proxy_footnote가 na가 있는 경우를 보면\n",
    "    - 대부분은 통일 되어있음\n",
    "    - 통일 되어있지 않은 케이스\n",
    "        - child lead level을 제외하고는 footnote가 NAN인 경우 누락으로 추측 가능\n",
    "        - 누락된 경우 proxy_or_real이 real로 채워졌다 추측 가능"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "geo_label_proxy_footnote\n",
       "Data are for school districts    9867\n",
       "County level data were used      6655\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "9482"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "cond = df_city.geo_label_proxy_or_real == 'proxy'\n",
    "\n",
    "display(df_city.loc[cond,'geo_label_proxy_footnote'].value_counts())\n",
    "display(np.sum(df_city.loc[cond,'geo_label_proxy_footnote'].isna()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- school districts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "metric_label\n",
       "Substance Use | Teen Marijuana                  563\n",
       "Diabetes and Obesity | Teen Obesity             560\n",
       "Substance Use | Teen Alcohol                    557\n",
       "Physical Activity | Teen Physical Activity      555\n",
       "Mental Health | School Bullying                 548\n",
       "Mental Health | Teen Mental Distress            547\n",
       "Mental Health | Electronic Bullying             547\n",
       "Substance Use | Teen Smoking                    545\n",
       "Physical Activity | Teen TV Time                544\n",
       "Physical Activity | Teen Physical Inactivity    538\n",
       "School Violence | Fighting in School            520\n",
       "Physical Activity | Teen Computer Time          518\n",
       "School Violence | Weapons in School             509\n",
       "Dietary Quality | Teen Soda                     507\n",
       "Respiratory Disease | Teen Asthma               506\n",
       "Mental Health | Teen Suicidal Ideation          497\n",
       "Dietary Quality | Teen Breakfast                475\n",
       "Physical Activity | Teen Physical Education     444\n",
       "Birth Control | Teen Birth Control              387\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "geo_label_city\n",
       "Philadelphia     756\n",
       "Boston           737\n",
       "New York City    719\n",
       "Houston          702\n",
       "San Diego        680\n",
       "San Francisco    635\n",
       "Los Angeles      626\n",
       "Washington       601\n",
       "Chicago          555\n",
       "Detroit          508\n",
       "Fort Worth       488\n",
       "Oakland          472\n",
       "Seattle          472\n",
       "Cleveland        406\n",
       "Baltimore        386\n",
       "Charlotte        386\n",
       "Portland         318\n",
       "Dallas           269\n",
       "Las Vegas        151\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "19"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "cond0 = df_city.geo_label_proxy_or_real == 'proxy'\n",
    "cond1 = df_city.geo_label_proxy_footnote == 'Data are for school districts' \n",
    "display(df_city.loc[cond0 & cond1,'metric_label'].value_counts())\n",
    "display(df_city.loc[cond0 & cond1,'geo_label_city'].value_counts())\n",
    "display(len(df_city.loc[cond0 & cond1,'geo_label_city'].value_counts()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Birth Control | Teen Birth Control',\n",
       " 'Diabetes and Obesity | Teen Obesity',\n",
       " 'Dietary Quality | Teen Breakfast',\n",
       " 'Dietary Quality | Teen Soda',\n",
       " 'Mental Health | Electronic Bullying',\n",
       " 'Mental Health | School Bullying',\n",
       " 'Mental Health | Teen Mental Distress',\n",
       " 'Mental Health | Teen Suicidal Ideation',\n",
       " 'Physical Activity | Teen Computer Time',\n",
       " 'Physical Activity | Teen Physical Activity',\n",
       " 'Physical Activity | Teen Physical Education',\n",
       " 'Physical Activity | Teen Physical Inactivity',\n",
       " 'Physical Activity | Teen TV Time',\n",
       " 'Respiratory Disease | Teen Asthma',\n",
       " 'School Violence | Fighting in School',\n",
       " 'School Violence | Weapons in School',\n",
       " 'Substance Use | Teen Alcohol',\n",
       " 'Substance Use | Teen Marijuana',\n",
       " 'Substance Use | Teen Smoking'}"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "19"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "geo_label_proxy_or_real\n",
       "proxy    9867\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "geo_label_proxy_footnote\n",
       "Data are for school districts    9867\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "label_proxy_school = set(df_city.loc[cond0 & cond1,'metric_label'].unique())\n",
    "display(label_proxy_school)\n",
    "display(len(label_proxy_school))\n",
    "cond = df_city['metric_label'].isin(label_proxy_school)\n",
    "display(df_city.loc[cond,'geo_label_proxy_or_real'].value_counts())\n",
    "display(np.sum(df_city.loc[cond,'geo_label_proxy_or_real'].isna()))\n",
    "display(df_city.loc[cond,'geo_label_proxy_footnote'].value_counts())\n",
    "display(np.sum(df_city.loc[cond,'geo_label_proxy_footnote'].isna()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "20"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "geo_label_proxy_or_real\n",
       "proxy    9449\n",
       "real     2561\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "geo_label_proxy_footnote\n",
       "Data are for school districts    9320\n",
       "County level data were used       129\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "2561"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "label_school = list(filter(lambda x : (('school' in x.lower()) or ('teen' in x.lower())),\n",
    "                           df_city['metric_label'].unique())) \n",
    "cond = df_city['metric_label'].isin(label_school)\n",
    "display(len(label_school))\n",
    "display(df_city.loc[cond,'geo_label_proxy_or_real'].value_counts())\n",
    "display(np.sum(df_city.loc[cond,'geo_label_proxy_or_real'].isna()))\n",
    "display(df_city.loc[cond,'geo_label_proxy_footnote'].value_counts())\n",
    "display(np.sum(df_city.loc[cond,'geo_label_proxy_footnote'].isna()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "21"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "geo_label_proxy_or_real\n",
       "proxy    9996\n",
       "real     2561\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "geo_label_proxy_footnote\n",
       "Data are for school districts    9867\n",
       "County level data were used       129\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "2561"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "35"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "label_school = list(filter(lambda x : (('school' in x.lower()) or ('teen' in x.lower())) or ('bullying' in x.lower()),\n",
    "                           df_city['metric_label'].unique())) \n",
    "cond = df_city['metric_label'].isin(label_school)\n",
    "display(len(label_school))\n",
    "display(df_city.loc[cond,'geo_label_proxy_or_real'].value_counts())\n",
    "display(np.sum(df_city.loc[cond,'geo_label_proxy_or_real'].isna()))\n",
    "display(df_city.loc[cond,'geo_label_proxy_footnote'].value_counts())\n",
    "display(np.sum(df_city.loc[cond,'geo_label_proxy_footnote'].isna()))\n",
    "display(len(df_city.loc[cond,'geo_label_city'].value_counts()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Diabetes and Obesity | Teen Obesity',\n",
       " 'Respiratory Disease | Teen Asthma',\n",
       " 'Physical Activity | Teen Physical Activity',\n",
       " 'Dietary Quality | Teen Soda',\n",
       " 'Dietary Quality | Teen Breakfast',\n",
       " 'Physical Activity | Teen Computer Time',\n",
       " 'Physical Activity | Teen TV Time',\n",
       " 'Physical Activity | Teen Physical Inactivity',\n",
       " 'Physical Activity | Teen Physical Education',\n",
       " 'Births | Teen Births',\n",
       " 'Birth Control | Teen Birth Control',\n",
       " 'Substance Use | Teen Smoking',\n",
       " 'Mental Health | Teen Mental Distress',\n",
       " 'Mental Health | Teen Suicidal Ideation',\n",
       " 'Substance Use | Teen Alcohol',\n",
       " 'Substance Use | Teen Marijuana']"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "geo_label_proxy_or_real\n",
       "proxy    7872\n",
       "real     2106\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "geo_label_proxy_footnote\n",
       "Data are for school districts    7743\n",
       "County level data were used       129\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "2106"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "35"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "label_teen = list(filter(lambda x : ('teen' in x.lower()),\n",
    "                           df_city['metric_label'].unique()))\n",
    "display(label_teen)\n",
    "cond = df_city['metric_label'].isin(label_teen)\n",
    "display(df_city.loc[cond,'geo_label_proxy_or_real'].value_counts())\n",
    "display(np.sum(df_city.loc[cond,'geo_label_proxy_or_real'].isna()))\n",
    "display(df_city.loc[cond,'geo_label_proxy_footnote'].value_counts())\n",
    "display(np.sum(df_city.loc[cond,'geo_label_proxy_footnote'].isna()))\n",
    "display(len(df_city.loc[cond,'geo_label_city'].value_counts()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Births | Teen Births']"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "geo_label_proxy_or_real\n",
       "real     2106\n",
       "proxy     129\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "geo_label_proxy_footnote\n",
       "County level data were used    129\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "2106"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "label_notprxy_teen = list(filter(lambda x : x not in label_proxy_school, label_teen))\n",
    "display(label_notprxy_teen)\n",
    "cond = df_city['metric_label'].isin(label_notprxy_teen)\n",
    "display(df_city.loc[cond,'geo_label_proxy_or_real'].value_counts())\n",
    "display(np.sum(df_city.loc[cond,'geo_label_proxy_or_real'].isna()))\n",
    "display(df_city.loc[cond,'geo_label_proxy_footnote'].value_counts())\n",
    "display(np.sum(df_city.loc[cond,'geo_label_proxy_footnote'].isna()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- county level"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "metric_label\n",
       "Deaths | Premature Death                                  390\n",
       "Deaths | Injury Deaths                                    370\n",
       "Substance Use | Drug Overdose Deaths                      326\n",
       "Mental Health | Suicide                                   315\n",
       "Deaths | Gun Deaths (Firearms)                            315\n",
       "Cancer | All Cancer Deaths                                312\n",
       "Cardiovascular Disease | Cardiovascular Disease Deaths    311\n",
       "Cardiovascular Disease | Heart Disease Deaths             311\n",
       "Crime Incidents | Homicides                               310\n",
       "Deaths | Motor Vehicle Deaths                             308\n",
       "Deaths | Deaths from All Causes                           308\n",
       "Substance Use | Opioid Overdose Deaths                    305\n",
       "Cancer | Colorectal Cancer Deaths                         304\n",
       "Diabetes and Obesity | Diabetes Deaths                    299\n",
       "Cancer | Lung Cancer Deaths                               296\n",
       "Respiratory Infection | Pneumonia or Influenza Deaths     292\n",
       "Life Expectancy at Birth | Life Expectancy                289\n",
       "Sexually Transmitted Disease | HIV-Related Deaths         234\n",
       "Housing | Homeless, Total                                 178\n",
       "Housing | Vacant Housing and Homelessness                 170\n",
       "Housing | Homeless, Children                              160\n",
       "Births | Teen Births                                      129\n",
       "Cancer | Prostate Cancer Deaths                           104\n",
       "Cancer | Breast Cancer Deaths                             104\n",
       "Respiratory Infection | COVID-19 Deaths                    79\n",
       "Respiratory Infection | COVID-19 Vaccination, Seniors      68\n",
       "Respiratory Infection | COVID-19 Vaccination, Adults       68\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "geo_label_city\n",
       "Las Vegas        3617\n",
       "Louisville       2450\n",
       "Columbus           28\n",
       "Los Angeles        28\n",
       "Memphis            28\n",
       "Oakland            28\n",
       "Portland           28\n",
       "Phoenix            28\n",
       "San Jose           28\n",
       "El Paso            28\n",
       "Milwaukee          28\n",
       "Cleveland          28\n",
       "Charlotte          28\n",
       "San Diego          28\n",
       "Fort Worth         28\n",
       "Austin             28\n",
       "Minneapolis        28\n",
       "Seattle            28\n",
       "San Antonio        28\n",
       "Tucson             24\n",
       "Denver             16\n",
       "Dallas             12\n",
       "Houston            12\n",
       "New York City       4\n",
       "San Francisco       4\n",
       "Philadelphia        4\n",
       "Long Beach          4\n",
       "Oklahoma City       4\n",
       "Boston              4\n",
       "Kansas City         4\n",
       "Indianapolis        4\n",
       "Chicago             4\n",
       "Washington          4\n",
       "Detroit             4\n",
       "Baltimore           4\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "cond0 = df_city.geo_label_proxy_or_real == 'proxy'\n",
    "cond1 = df_city.geo_label_proxy_footnote == 'County level data were used'\n",
    "display(df_city.loc[cond0 & cond1,'metric_label'].value_counts())\n",
    "display(df_city.loc[cond0 & cond1,'geo_label_city'].value_counts())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "geo_label_proxy_or_real\n",
       "real     98684\n",
       "proxy     6655\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "geo_label_proxy_footnote\n",
       "County level data were used    6655\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "98684"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "label_proxy_county = set(df_city.loc[cond0 & cond1,'metric_label'].unique())\n",
    "cond = df_city['metric_label'].isin(label_proxy_county)\n",
    "display(df_city.loc[cond,'geo_label_proxy_or_real'].value_counts())\n",
    "display(np.sum(df_city.loc[cond,'geo_label_proxy_or_real'].isna()))\n",
    "display(df_city.loc[cond,'geo_label_proxy_footnote'].value_counts())\n",
    "display(np.sum(df_city.loc[cond,'geo_label_proxy_footnote'].isna()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "metric_label\n",
       "Sexually Transmitted Disease | HIV/AIDS Prevalence    2897\n",
       "Respiratory Infection | Flu Vaccinations, Medicare    1995\n",
       "Respiratory Infection | New Tuberculosis Cases         455\n",
       "Sexually Transmitted Disease | Syphilis, Newborns      455\n",
       "Heat and Disasters | Climate-related  Disasters        455\n",
       "Air Pollution | Poor Air Quality                       454\n",
       "Air Pollution | Hazardous Air Quality                  454\n",
       "Sexually Transmitted Disease | New Chlamydia Cases     452\n",
       "Sexually Transmitted Disease | Syphilis Prevalence     452\n",
       "Sexually Transmitted Disease | New Gonorrhea Cases     452\n",
       "Lead Poisoning | Child Lead Testing                    350\n",
       "Lead Poisoning | Child Lead Levels 5+ mcg/dL           344\n",
       "Lead Poisoning | Child Lead Levels 10+ mcg/dL          267\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "geo_label_city\n",
       "New York City    287\n",
       "Phoenix          287\n",
       "Detroit          287\n",
       "Minneapolis      287\n",
       "Seattle          287\n",
       "Indianapolis     283\n",
       "Philadelphia     283\n",
       "Denver           279\n",
       "Kansas City      278\n",
       "Oakland          278\n",
       "Columbus         278\n",
       "Cleveland        278\n",
       "San Diego        278\n",
       "Memphis          278\n",
       "Charlotte        278\n",
       "Los Angeles      278\n",
       "Milwaukee        277\n",
       "Washington       273\n",
       "Tucson           272\n",
       "Oklahoma City    271\n",
       "Dallas           270\n",
       "El Paso          269\n",
       "San Jose         268\n",
       "Chicago          268\n",
       "Fort Worth       267\n",
       "Houston          267\n",
       "Austin           267\n",
       "San Antonio      267\n",
       "San Francisco    267\n",
       "Baltimore        258\n",
       "Portland         254\n",
       "Las Vegas        254\n",
       "Louisville       246\n",
       "Long Beach       245\n",
       "Boston           218\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "cond0 = df_city.geo_label_proxy_or_real == 'proxy'\n",
    "cond1 = df_city.geo_label_proxy_footnote.isna()\n",
    "display(df_city.loc[cond0 & cond1,'metric_label'].value_counts())\n",
    "display(df_city.loc[cond0 & cond1,'geo_label_city'].value_counts())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "geo_label_proxy_or_real\n",
       "proxy    9482\n",
       "real       22\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "Series([], Name: count, dtype: int64)"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "9504"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "label_proxy_na = set(df_city.loc[cond0 & cond1,'metric_label'].unique())\n",
    "cond = df_city['metric_label'].isin(label_proxy_na)\n",
    "display(df_city.loc[cond,'geo_label_proxy_or_real'].value_counts())\n",
    "display(np.sum(df_city.loc[cond,'geo_label_proxy_or_real'].isna()))\n",
    "display(df_city.loc[cond,'geo_label_proxy_footnote'].value_counts())\n",
    "display(np.sum(df_city.loc[cond,'geo_label_proxy_footnote'].isna()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- geo_label_proxy_or_real이 proxy인 metric label의 경우 바깥의 데이터를 모델에 이식할 때 ratio가 아닌 경우 특히 주의 필요"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## geo_label"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['geo_label_city',\n",
       " 'geo_label_state',\n",
       " 'geo_label_citystate',\n",
       " 'geo_fips_code',\n",
       " 'geo_label_proxy_or_real',\n",
       " 'geo_label_proxy_footnote',\n",
       " 'geo_fips_desc',\n",
       " 'geo_strata_region',\n",
       " 'geo_strata_poverty',\n",
       " 'geo_strata_Population',\n",
       " 'geo_strata_PopDensity',\n",
       " 'geo_strata_Segregation']"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(filter(lambda x: 'geo' in x,df.columns))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(np.sum(df_city['geo_label_city'].isna()))\n",
    "display(np.sum(df_city['geo_label_state'].isna()))\n",
    "display(np.sum(df_city['geo_label_citystate'].isna()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_city_label =df_city[['geo_label_city','geo_label_state','geo_label_citystate']].value_counts().reset_index()\n",
    "\n",
    "np.sum(df_city_label.duplicated(subset=['geo_label_city'],keep=False))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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>geo_label_city</th>\n",
       "      <th>geo_label_state</th>\n",
       "      <th>geo_label_citystate</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>New York City</td>\n",
       "      <td>NY</td>\n",
       "      <td>New York City, NY</td>\n",
       "      <td>6535</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Houston</td>\n",
       "      <td>TX</td>\n",
       "      <td>Houston, TX</td>\n",
       "      <td>6431</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>CA</td>\n",
       "      <td>Los Angeles, CA</td>\n",
       "      <td>6372</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Philadelphia</td>\n",
       "      <td>PA</td>\n",
       "      <td>Philadelphia, PA</td>\n",
       "      <td>6310</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>San Diego</td>\n",
       "      <td>CA</td>\n",
       "      <td>San Diego, CA</td>\n",
       "      <td>6251</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Chicago</td>\n",
       "      <td>IL</td>\n",
       "      <td>Chicago, IL</td>\n",
       "      <td>6210</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>CA</td>\n",
       "      <td>San Francisco, CA</td>\n",
       "      <td>6121</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>NV</td>\n",
       "      <td>Las Vegas, NV</td>\n",
       "      <td>5849</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Boston</td>\n",
       "      <td>MA</td>\n",
       "      <td>Boston, MA</td>\n",
       "      <td>5718</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Oakland</td>\n",
       "      <td>CA</td>\n",
       "      <td>Oakland, CA</td>\n",
       "      <td>5676</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Dallas</td>\n",
       "      <td>TX</td>\n",
       "      <td>Dallas, TX</td>\n",
       "      <td>5655</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Fort Worth</td>\n",
       "      <td>TX</td>\n",
       "      <td>Fort Worth, TX</td>\n",
       "      <td>5628</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Seattle</td>\n",
       "      <td>WA</td>\n",
       "      <td>Seattle, WA</td>\n",
       "      <td>5507</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Phoenix</td>\n",
       "      <td>AZ</td>\n",
       "      <td>Phoenix, AZ</td>\n",
       "      <td>5437</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Charlotte</td>\n",
       "      <td>NC</td>\n",
       "      <td>Charlotte, NC</td>\n",
       "      <td>5381</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>San Antonio</td>\n",
       "      <td>TX</td>\n",
       "      <td>San Antonio, TX</td>\n",
       "      <td>5349</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Washington</td>\n",
       "      <td>DC</td>\n",
       "      <td>Washington, DC</td>\n",
       "      <td>5264</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Portland</td>\n",
       "      <td>OR</td>\n",
       "      <td>Portland, OR</td>\n",
       "      <td>5219</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Long Beach</td>\n",
       "      <td>CA</td>\n",
       "      <td>Long Beach, CA</td>\n",
       "      <td>5216</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>San Jose</td>\n",
       "      <td>CA</td>\n",
       "      <td>San Jose, CA</td>\n",
       "      <td>5206</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Austin</td>\n",
       "      <td>TX</td>\n",
       "      <td>Austin, TX</td>\n",
       "      <td>5198</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Detroit</td>\n",
       "      <td>MI</td>\n",
       "      <td>Detroit, MI</td>\n",
       "      <td>5160</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Denver</td>\n",
       "      <td>CO</td>\n",
       "      <td>Denver, CO</td>\n",
       "      <td>5157</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Cleveland</td>\n",
       "      <td>OH</td>\n",
       "      <td>Cleveland, OH</td>\n",
       "      <td>5113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Baltimore</td>\n",
       "      <td>MD</td>\n",
       "      <td>Baltimore, MD</td>\n",
       "      <td>5101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Oklahoma City</td>\n",
       "      <td>OK</td>\n",
       "      <td>Oklahoma City, OK</td>\n",
       "      <td>5030</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Indianapolis</td>\n",
       "      <td>IN</td>\n",
       "      <td>Indianapolis, IN</td>\n",
       "      <td>4916</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Columbus</td>\n",
       "      <td>OH</td>\n",
       "      <td>Columbus, OH</td>\n",
       "      <td>4907</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Milwaukee</td>\n",
       "      <td>WI</td>\n",
       "      <td>Milwaukee, WI</td>\n",
       "      <td>4881</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Kansas City</td>\n",
       "      <td>MO</td>\n",
       "      <td>Kansas City, MO</td>\n",
       "      <td>4848</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Tucson</td>\n",
       "      <td>AZ</td>\n",
       "      <td>Tucson, AZ</td>\n",
       "      <td>4799</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Memphis</td>\n",
       "      <td>TN</td>\n",
       "      <td>Memphis, TN</td>\n",
       "      <td>4631</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>El Paso</td>\n",
       "      <td>TX</td>\n",
       "      <td>El Paso, TX</td>\n",
       "      <td>4553</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Louisville</td>\n",
       "      <td>KY</td>\n",
       "      <td>Louisville, KY</td>\n",
       "      <td>4527</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Minneapolis</td>\n",
       "      <td>MN</td>\n",
       "      <td>Minneapolis, MN</td>\n",
       "      <td>4511</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   geo_label_city geo_label_state geo_label_citystate  count\n",
       "0   New York City              NY   New York City, NY   6535\n",
       "1         Houston              TX         Houston, TX   6431\n",
       "2     Los Angeles              CA     Los Angeles, CA   6372\n",
       "3    Philadelphia              PA    Philadelphia, PA   6310\n",
       "4       San Diego              CA       San Diego, CA   6251\n",
       "5         Chicago              IL         Chicago, IL   6210\n",
       "6   San Francisco              CA   San Francisco, CA   6121\n",
       "7       Las Vegas              NV       Las Vegas, NV   5849\n",
       "8          Boston              MA          Boston, MA   5718\n",
       "9         Oakland              CA         Oakland, CA   5676\n",
       "10         Dallas              TX          Dallas, TX   5655\n",
       "11     Fort Worth              TX      Fort Worth, TX   5628\n",
       "12        Seattle              WA         Seattle, WA   5507\n",
       "13        Phoenix              AZ         Phoenix, AZ   5437\n",
       "14      Charlotte              NC       Charlotte, NC   5381\n",
       "15    San Antonio              TX     San Antonio, TX   5349\n",
       "16     Washington              DC      Washington, DC   5264\n",
       "17       Portland              OR        Portland, OR   5219\n",
       "18     Long Beach              CA      Long Beach, CA   5216\n",
       "19       San Jose              CA        San Jose, CA   5206\n",
       "20         Austin              TX          Austin, TX   5198\n",
       "21        Detroit              MI         Detroit, MI   5160\n",
       "22         Denver              CO          Denver, CO   5157\n",
       "23      Cleveland              OH       Cleveland, OH   5113\n",
       "24      Baltimore              MD       Baltimore, MD   5101\n",
       "25  Oklahoma City              OK   Oklahoma City, OK   5030\n",
       "26   Indianapolis              IN    Indianapolis, IN   4916\n",
       "27       Columbus              OH        Columbus, OH   4907\n",
       "28      Milwaukee              WI       Milwaukee, WI   4881\n",
       "29    Kansas City              MO     Kansas City, MO   4848\n",
       "30         Tucson              AZ          Tucson, AZ   4799\n",
       "31        Memphis              TN         Memphis, TN   4631\n",
       "32        El Paso              TX         El Paso, TX   4553\n",
       "33     Louisville              KY      Louisville, KY   4527\n",
       "34    Minneapolis              MN     Minneapolis, MN   4511"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_city_label"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## geo_fips"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['geo_label_city',\n",
       " 'geo_label_state',\n",
       " 'geo_label_citystate',\n",
       " 'geo_fips_code',\n",
       " 'geo_label_proxy_or_real',\n",
       " 'geo_label_proxy_footnote',\n",
       " 'geo_fips_desc',\n",
       " 'geo_strata_region',\n",
       " 'geo_strata_poverty',\n",
       " 'geo_strata_Population',\n",
       " 'geo_strata_PopDensity',\n",
       " 'geo_strata_Segregation']"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(filter(lambda x: 'geo' in x,df.columns))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "geo_fips_desc\n",
       "place     178541\n",
       "county     10126\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(df_city['geo_fips_desc'].value_counts())\n",
    "display(np.sum(df_city['geo_fips_desc'].isna()))\n",
    "display(np.sum(df_city['geo_fips_code'].isna()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "70"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>geo_label_city</th>\n",
       "      <th>geo_fips_code</th>\n",
       "      <th>geo_fips_desc</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Austin</td>\n",
       "      <td>4805000.0</td>\n",
       "      <td>county</td>\n",
       "      <td>295</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Austin</td>\n",
       "      <td>4805000.0</td>\n",
       "      <td>place</td>\n",
       "      <td>4903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Baltimore</td>\n",
       "      <td>2404000.0</td>\n",
       "      <td>county</td>\n",
       "      <td>262</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Baltimore</td>\n",
       "      <td>2404000.0</td>\n",
       "      <td>place</td>\n",
       "      <td>4839</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Boston</td>\n",
       "      <td>2507000.0</td>\n",
       "      <td>county</td>\n",
       "      <td>222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>Seattle</td>\n",
       "      <td>5363000.0</td>\n",
       "      <td>place</td>\n",
       "      <td>5192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>Tucson</td>\n",
       "      <td>477000.0</td>\n",
       "      <td>county</td>\n",
       "      <td>296</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>Tucson</td>\n",
       "      <td>477000.0</td>\n",
       "      <td>place</td>\n",
       "      <td>4503</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>Washington</td>\n",
       "      <td>1150000.0</td>\n",
       "      <td>county</td>\n",
       "      <td>277</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>Washington</td>\n",
       "      <td>1150000.0</td>\n",
       "      <td>place</td>\n",
       "      <td>4987</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>70 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   geo_label_city  geo_fips_code geo_fips_desc  count\n",
       "0          Austin      4805000.0        county    295\n",
       "1          Austin      4805000.0         place   4903\n",
       "2       Baltimore      2404000.0        county    262\n",
       "3       Baltimore      2404000.0         place   4839\n",
       "4          Boston      2507000.0        county    222\n",
       "..            ...            ...           ...    ...\n",
       "65        Seattle      5363000.0         place   5192\n",
       "66         Tucson       477000.0        county    296\n",
       "67         Tucson       477000.0         place   4503\n",
       "68     Washington      1150000.0        county    277\n",
       "69     Washington      1150000.0         place   4987\n",
       "\n",
       "[70 rows x 4 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df_fips_cnt =df_city[['geo_label_city','geo_fips_code','geo_fips_desc']].value_counts().reset_index()\n",
    "df_fips_cnt = df_fips_cnt.sort_values(by=['geo_label_city','geo_fips_desc']).reset_index(drop = True)\n",
    "\n",
    "display(np.sum(df_fips_cnt.duplicated(subset=['geo_label_city'],keep=False)))\n",
    "display(df_fips_cnt)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "72"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>geo_label_city</th>\n",
       "      <th>geo_fips_desc</th>\n",
       "      <th>geo_label_proxy_or_real</th>\n",
       "      <th>geo_label_proxy_footnote</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Austin</td>\n",
       "      <td>county</td>\n",
       "      <td>proxy</td>\n",
       "      <td>NAN</td>\n",
       "      <td>267</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Austin</td>\n",
       "      <td>county</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Austin</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>4903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Baltimore</td>\n",
       "      <td>county</td>\n",
       "      <td>proxy</td>\n",
       "      <td>NAN</td>\n",
       "      <td>258</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Baltimore</td>\n",
       "      <td>county</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>Tucson</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>4503</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>Washington</td>\n",
       "      <td>county</td>\n",
       "      <td>proxy</td>\n",
       "      <td>NAN</td>\n",
       "      <td>273</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>Washington</td>\n",
       "      <td>county</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <td>Washington</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>Data are for school districts</td>\n",
       "      <td>601</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125</th>\n",
       "      <td>Washington</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>4386</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>126 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    geo_label_city geo_fips_desc geo_label_proxy_or_real  \\\n",
       "0           Austin        county                   proxy   \n",
       "1           Austin        county                   proxy   \n",
       "2           Austin         place                    real   \n",
       "3        Baltimore        county                   proxy   \n",
       "4        Baltimore        county                   proxy   \n",
       "..             ...           ...                     ...   \n",
       "121         Tucson         place                    real   \n",
       "122     Washington        county                   proxy   \n",
       "123     Washington        county                   proxy   \n",
       "124     Washington         place                   proxy   \n",
       "125     Washington         place                    real   \n",
       "\n",
       "          geo_label_proxy_footnote  count  \n",
       "0                              NAN    267  \n",
       "1      County level data were used     28  \n",
       "2                              NAN   4903  \n",
       "3                              NAN    258  \n",
       "4      County level data were used      4  \n",
       "..                             ...    ...  \n",
       "121                            NAN   4503  \n",
       "122                            NAN    273  \n",
       "123    County level data were used      4  \n",
       "124  Data are for school districts    601  \n",
       "125                            NAN   4386  \n",
       "\n",
       "[126 rows x 5 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df_county_check =df_temp[['geo_label_city','geo_fips_desc','geo_label_proxy_or_real','geo_label_proxy_footnote']].value_counts().reset_index()\n",
    "df_county_check = df_county_check.sort_values(by=['geo_label_city','geo_fips_desc','geo_label_proxy_or_real']).reset_index(drop = True)\n",
    "\n",
    "display(np.sum(df_county_check.duplicated(subset=['geo_label_city','geo_fips_desc','geo_label_proxy_or_real'],keep=False)))\n",
    "display(df_county_check)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "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>geo_label_city</th>\n",
       "      <th>geo_fips_desc</th>\n",
       "      <th>geo_label_proxy_or_real</th>\n",
       "      <th>geo_label_proxy_footnote</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>county</td>\n",
       "      <td>proxy</td>\n",
       "      <td>NAN</td>\n",
       "      <td>254</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>county</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>3589</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>Data are for school districts</td>\n",
       "      <td>151</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>1827</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   geo_label_city geo_fips_desc geo_label_proxy_or_real  \\\n",
       "54      Las Vegas        county                   proxy   \n",
       "55      Las Vegas        county                   proxy   \n",
       "56      Las Vegas         place                   proxy   \n",
       "57      Las Vegas         place                   proxy   \n",
       "58      Las Vegas         place                    real   \n",
       "\n",
       "         geo_label_proxy_footnote  count  \n",
       "54                            NAN    254  \n",
       "55    County level data were used     28  \n",
       "56    County level data were used   3589  \n",
       "57  Data are for school districts    151  \n",
       "58                            NAN   1827  "
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cond = df_county_check['geo_label_city'] == 'Las Vegas'\n",
    "df_county_check[cond]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "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>geo_label_city</th>\n",
       "      <th>geo_fips_desc</th>\n",
       "      <th>geo_label_proxy_or_real</th>\n",
       "      <th>geo_label_proxy_footnote</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Baltimore</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>Data are for school districts</td>\n",
       "      <td>386</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Boston</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>Data are for school districts</td>\n",
       "      <td>737</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Charlotte</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>Data are for school districts</td>\n",
       "      <td>386</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Chicago</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>Data are for school districts</td>\n",
       "      <td>555</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Cleveland</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>Data are for school districts</td>\n",
       "      <td>406</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Dallas</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>Data are for school districts</td>\n",
       "      <td>269</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Detroit</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>Data are for school districts</td>\n",
       "      <td>508</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>Fort Worth</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>Data are for school districts</td>\n",
       "      <td>488</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>Houston</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>Data are for school districts</td>\n",
       "      <td>702</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>Data are for school districts</td>\n",
       "      <td>151</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>Data are for school districts</td>\n",
       "      <td>626</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>New York City</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>Data are for school districts</td>\n",
       "      <td>719</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>Oakland</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>Data are for school districts</td>\n",
       "      <td>472</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>Philadelphia</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>Data are for school districts</td>\n",
       "      <td>756</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>Portland</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>Data are for school districts</td>\n",
       "      <td>318</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>San Diego</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>Data are for school districts</td>\n",
       "      <td>680</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>Data are for school districts</td>\n",
       "      <td>635</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>117</th>\n",
       "      <td>Seattle</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>Data are for school districts</td>\n",
       "      <td>472</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <td>Washington</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>Data are for school districts</td>\n",
       "      <td>601</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    geo_label_city geo_fips_desc geo_label_proxy_or_real  \\\n",
       "5        Baltimore         place                   proxy   \n",
       "9           Boston         place                   proxy   \n",
       "13       Charlotte         place                   proxy   \n",
       "17         Chicago         place                   proxy   \n",
       "21       Cleveland         place                   proxy   \n",
       "28          Dallas         place                   proxy   \n",
       "35         Detroit         place                   proxy   \n",
       "42      Fort Worth         place                   proxy   \n",
       "46         Houston         place                   proxy   \n",
       "57       Las Vegas         place                   proxy   \n",
       "64     Los Angeles         place                   proxy   \n",
       "81   New York City         place                   proxy   \n",
       "85         Oakland         place                   proxy   \n",
       "92    Philadelphia         place                   proxy   \n",
       "99        Portland         place                   proxy   \n",
       "106      San Diego         place                   proxy   \n",
       "110  San Francisco         place                   proxy   \n",
       "117        Seattle         place                   proxy   \n",
       "124     Washington         place                   proxy   \n",
       "\n",
       "          geo_label_proxy_footnote  count  \n",
       "5    Data are for school districts    386  \n",
       "9    Data are for school districts    737  \n",
       "13   Data are for school districts    386  \n",
       "17   Data are for school districts    555  \n",
       "21   Data are for school districts    406  \n",
       "28   Data are for school districts    269  \n",
       "35   Data are for school districts    508  \n",
       "42   Data are for school districts    488  \n",
       "46   Data are for school districts    702  \n",
       "57   Data are for school districts    151  \n",
       "64   Data are for school districts    626  \n",
       "81   Data are for school districts    719  \n",
       "85   Data are for school districts    472  \n",
       "92   Data are for school districts    756  \n",
       "99   Data are for school districts    318  \n",
       "106  Data are for school districts    680  \n",
       "110  Data are for school districts    635  \n",
       "117  Data are for school districts    472  \n",
       "124  Data are for school districts    601  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "19"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "cond = df_county_check['geo_label_proxy_footnote'] == 'Data are for school districts'\n",
    "display(df_county_check[cond])\n",
    "display(len(df_county_check[cond]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- data for school districts에 해당하는 metric label이 없는 도시가 있는지 확인 필요"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "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>geo_label_city</th>\n",
       "      <th>geo_fips_desc</th>\n",
       "      <th>geo_label_proxy_or_real</th>\n",
       "      <th>geo_label_proxy_footnote</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Austin</td>\n",
       "      <td>county</td>\n",
       "      <td>proxy</td>\n",
       "      <td>NAN</td>\n",
       "      <td>267</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Austin</td>\n",
       "      <td>county</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Baltimore</td>\n",
       "      <td>county</td>\n",
       "      <td>proxy</td>\n",
       "      <td>NAN</td>\n",
       "      <td>258</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Baltimore</td>\n",
       "      <td>county</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Boston</td>\n",
       "      <td>county</td>\n",
       "      <td>proxy</td>\n",
       "      <td>NAN</td>\n",
       "      <td>218</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116</th>\n",
       "      <td>Seattle</td>\n",
       "      <td>county</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>Tucson</td>\n",
       "      <td>county</td>\n",
       "      <td>proxy</td>\n",
       "      <td>NAN</td>\n",
       "      <td>272</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>Tucson</td>\n",
       "      <td>county</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>Washington</td>\n",
       "      <td>county</td>\n",
       "      <td>proxy</td>\n",
       "      <td>NAN</td>\n",
       "      <td>273</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>Washington</td>\n",
       "      <td>county</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>72 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    geo_label_city geo_fips_desc geo_label_proxy_or_real  \\\n",
       "0           Austin        county                   proxy   \n",
       "1           Austin        county                   proxy   \n",
       "3        Baltimore        county                   proxy   \n",
       "4        Baltimore        county                   proxy   \n",
       "7           Boston        county                   proxy   \n",
       "..             ...           ...                     ...   \n",
       "116        Seattle        county                   proxy   \n",
       "119         Tucson        county                   proxy   \n",
       "120         Tucson        county                   proxy   \n",
       "122     Washington        county                   proxy   \n",
       "123     Washington        county                   proxy   \n",
       "\n",
       "        geo_label_proxy_footnote  count  \n",
       "0                            NAN    267  \n",
       "1    County level data were used     28  \n",
       "3                            NAN    258  \n",
       "4    County level data were used      4  \n",
       "7                            NAN    218  \n",
       "..                           ...    ...  \n",
       "116  County level data were used     28  \n",
       "119                          NAN    272  \n",
       "120  County level data were used     24  \n",
       "122                          NAN    273  \n",
       "123  County level data were used      4  \n",
       "\n",
       "[72 rows x 5 columns]"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cond_dup = df_county_check.duplicated(subset=['geo_label_city','geo_fips_desc','geo_label_proxy_or_real'],keep=False)\n",
    "df_county_temp = df_county_check[cond_dup].sort_values(by=['geo_label_city','geo_fips_desc'])\n",
    "df_county_temp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "geo_fips_desc\n",
       "county    70\n",
       "place      2\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "geo_label_proxy_or_real\n",
       "proxy    72\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "geo_label_proxy_footnote\n",
       "County level data were used      36\n",
       "NAN                              35\n",
       "Data are for school districts     1\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(df_county_temp['geo_fips_desc'].value_counts())\n",
    "display(df_county_temp['geo_label_proxy_or_real'].value_counts())\n",
    "display(df_county_temp['geo_label_proxy_footnote'].value_counts())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "geo_label_city\n",
       "Las Vegas        2\n",
       "Austin           1\n",
       "Louisville       1\n",
       "Milwaukee        1\n",
       "Minneapolis      1\n",
       "New York City    1\n",
       "Oakland          1\n",
       "Oklahoma City    1\n",
       "Philadelphia     1\n",
       "Phoenix          1\n",
       "Portland         1\n",
       "San Antonio      1\n",
       "San Diego        1\n",
       "San Francisco    1\n",
       "San Jose         1\n",
       "Seattle          1\n",
       "Tucson           1\n",
       "Memphis          1\n",
       "Los Angeles      1\n",
       "Baltimore        1\n",
       "Long Beach       1\n",
       "Kansas City      1\n",
       "Indianapolis     1\n",
       "Houston          1\n",
       "Fort Worth       1\n",
       "El Paso          1\n",
       "Detroit          1\n",
       "Denver           1\n",
       "Dallas           1\n",
       "Columbus         1\n",
       "Cleveland        1\n",
       "Chicago          1\n",
       "Charlotte        1\n",
       "Boston           1\n",
       "Washington       1\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cond = df_county_temp['geo_label_proxy_footnote'] == 'County level data were used'\n",
    "df_county_temp.loc[cond,'geo_label_city'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "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>geo_label_city</th>\n",
       "      <th>geo_fips_desc</th>\n",
       "      <th>geo_label_proxy_or_real</th>\n",
       "      <th>geo_label_proxy_footnote</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>county</td>\n",
       "      <td>proxy</td>\n",
       "      <td>NAN</td>\n",
       "      <td>254</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>county</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>3589</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>Data are for school districts</td>\n",
       "      <td>151</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   geo_label_city geo_fips_desc geo_label_proxy_or_real  \\\n",
       "54      Las Vegas        county                   proxy   \n",
       "55      Las Vegas        county                   proxy   \n",
       "56      Las Vegas         place                   proxy   \n",
       "57      Las Vegas         place                   proxy   \n",
       "\n",
       "         geo_label_proxy_footnote  count  \n",
       "54                            NAN    254  \n",
       "55    County level data were used     28  \n",
       "56    County level data were used   3589  \n",
       "57  Data are for school districts    151  "
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cond = df_county_temp['geo_label_city'] == 'Las Vegas'\n",
    "df_county_temp[cond]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 어떤 metric label인지 몰라도 las vegas에서 해당 metric label에 대해서 feo_fips_desc를 잘못 기입한 것으로 추측\n",
    "- 나머지의 경우 geo_fips_desc가 county면 county level data가 쓰인 것으로 보면 될 듯"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- metric label 별로 geo_fips_desc가 유일한지 확인 필요"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3781"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>metric_label</th>\n",
       "      <th>geo_label_city</th>\n",
       "      <th>geo_fips_desc</th>\n",
       "      <th>geo_label_proxy_or_real</th>\n",
       "      <th>geo_label_proxy_footnote</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Active Transportation | Riding Bike to Work</td>\n",
       "      <td>Austin</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Active Transportation | Riding Bike to Work</td>\n",
       "      <td>Baltimore</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Active Transportation | Riding Bike to Work</td>\n",
       "      <td>Boston</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Active Transportation | Riding Bike to Work</td>\n",
       "      <td>Charlotte</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Active Transportation | Riding Bike to Work</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>13</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3778</th>\n",
       "      <td>Transportation | Public Transportation Use</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3779</th>\n",
       "      <td>Transportation | Public Transportation Use</td>\n",
       "      <td>San Jose</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3780</th>\n",
       "      <td>Transportation | Public Transportation Use</td>\n",
       "      <td>Seattle</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3781</th>\n",
       "      <td>Transportation | Public Transportation Use</td>\n",
       "      <td>Tucson</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3782</th>\n",
       "      <td>Transportation | Public Transportation Use</td>\n",
       "      <td>Washington</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>63</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3783 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     metric_label geo_label_city  \\\n",
       "0     Active Transportation | Riding Bike to Work         Austin   \n",
       "1     Active Transportation | Riding Bike to Work      Baltimore   \n",
       "2     Active Transportation | Riding Bike to Work         Boston   \n",
       "3     Active Transportation | Riding Bike to Work      Charlotte   \n",
       "4     Active Transportation | Riding Bike to Work        Chicago   \n",
       "...                                           ...            ...   \n",
       "3778   Transportation | Public Transportation Use  San Francisco   \n",
       "3779   Transportation | Public Transportation Use       San Jose   \n",
       "3780   Transportation | Public Transportation Use        Seattle   \n",
       "3781   Transportation | Public Transportation Use         Tucson   \n",
       "3782   Transportation | Public Transportation Use     Washington   \n",
       "\n",
       "     geo_fips_desc geo_label_proxy_or_real geo_label_proxy_footnote  count  \n",
       "0            place                    real                      NAN     13  \n",
       "1            place                    real                      NAN     13  \n",
       "2            place                    real                      NAN     13  \n",
       "3            place                    real                      NAN     13  \n",
       "4            place                    real                      NAN     13  \n",
       "...            ...                     ...                      ...    ...  \n",
       "3778         place                    real                      NAN     63  \n",
       "3779         place                    real                      NAN     63  \n",
       "3780         place                    real                      NAN     63  \n",
       "3781         place                    real                      NAN     63  \n",
       "3782         place                    real                      NAN     63  \n",
       "\n",
       "[3783 rows x 6 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df_metric_county =df_temp[['metric_label','geo_label_city','geo_fips_desc','geo_label_proxy_or_real','geo_label_proxy_footnote']].value_counts().reset_index()\n",
    "df_metric_county = df_metric_county.sort_values(by=['metric_label','geo_label_city','geo_fips_desc','geo_label_proxy_or_real']).reset_index(drop = True)\n",
    "\n",
    "display(np.sum(df_metric_county.duplicated(subset=['metric_label','geo_fips_desc','geo_label_proxy_or_real'],keep=False)))\n",
    "display(df_metric_county)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>metric_label</th>\n",
       "      <th>geo_fips_desc</th>\n",
       "      <th>geo_label_proxy_or_real</th>\n",
       "      <th>geo_label_proxy_footnote</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Active Transportation | Riding Bike to Work</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Active Transportation | Walking to Work</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>2205</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Air Pollution | Hazardous Air Quality</td>\n",
       "      <td>county</td>\n",
       "      <td>proxy</td>\n",
       "      <td>NAN</td>\n",
       "      <td>454</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Air Pollution | Poor Air Quality</td>\n",
       "      <td>county</td>\n",
       "      <td>proxy</td>\n",
       "      <td>NAN</td>\n",
       "      <td>454</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Birth Control | Teen Birth Control</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>Data are for school districts</td>\n",
       "      <td>387</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>140</th>\n",
       "      <td>Substance Use | Teen Smoking</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>Data are for school districts</td>\n",
       "      <td>545</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141</th>\n",
       "      <td>Transportation | Drives Alone to Work</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>2205</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>142</th>\n",
       "      <td>Transportation | Lack of Car</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143</th>\n",
       "      <td>Transportation | Longer Driving Commute Time</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>350</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144</th>\n",
       "      <td>Transportation | Public Transportation Use</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>2205</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>145 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     metric_label geo_fips_desc  \\\n",
       "0     Active Transportation | Riding Bike to Work         place   \n",
       "1         Active Transportation | Walking to Work         place   \n",
       "2           Air Pollution | Hazardous Air Quality        county   \n",
       "3                Air Pollution | Poor Air Quality        county   \n",
       "4              Birth Control | Teen Birth Control         place   \n",
       "..                                            ...           ...   \n",
       "140                  Substance Use | Teen Smoking         place   \n",
       "141         Transportation | Drives Alone to Work         place   \n",
       "142                  Transportation | Lack of Car         place   \n",
       "143  Transportation | Longer Driving Commute Time         place   \n",
       "144    Transportation | Public Transportation Use         place   \n",
       "\n",
       "    geo_label_proxy_or_real       geo_label_proxy_footnote  count  \n",
       "0                      real                            NAN    455  \n",
       "1                      real                            NAN   2205  \n",
       "2                     proxy                            NAN    454  \n",
       "3                     proxy                            NAN    454  \n",
       "4                     proxy  Data are for school districts    387  \n",
       "..                      ...                            ...    ...  \n",
       "140                   proxy  Data are for school districts    545  \n",
       "141                    real                            NAN   2205  \n",
       "142                    real                            NAN    455  \n",
       "143                    real                            NAN    350  \n",
       "144                    real                            NAN   2205  \n",
       "\n",
       "[145 rows x 5 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df_metric_county =df_temp[['metric_label','geo_fips_desc','geo_label_proxy_or_real','geo_label_proxy_footnote']].value_counts().reset_index()\n",
    "df_metric_county = df_metric_county.sort_values(by=['metric_label','geo_fips_desc','geo_label_proxy_or_real']).reset_index(drop = True)\n",
    "\n",
    "display(np.sum(df_metric_county.duplicated(subset=['metric_label','geo_fips_desc','geo_label_proxy_or_real'],keep=False)))\n",
    "display(df_metric_county)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "44"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>metric_label</th>\n",
       "      <th>geo_fips_desc</th>\n",
       "      <th>geo_label_proxy_or_real</th>\n",
       "      <th>geo_label_proxy_footnote</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Active Transportation | Riding Bike to Work</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Active Transportation | Walking to Work</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>2205</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Air Pollution | Hazardous Air Quality</td>\n",
       "      <td>county</td>\n",
       "      <td>proxy</td>\n",
       "      <td>NAN</td>\n",
       "      <td>454</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Air Pollution | Poor Air Quality</td>\n",
       "      <td>county</td>\n",
       "      <td>proxy</td>\n",
       "      <td>NAN</td>\n",
       "      <td>454</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Birth Control | Teen Birth Control</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>Data are for school districts</td>\n",
       "      <td>387</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>140</th>\n",
       "      <td>Substance Use | Teen Smoking</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>Data are for school districts</td>\n",
       "      <td>545</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141</th>\n",
       "      <td>Transportation | Drives Alone to Work</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>2205</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>142</th>\n",
       "      <td>Transportation | Lack of Car</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143</th>\n",
       "      <td>Transportation | Longer Driving Commute Time</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>350</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144</th>\n",
       "      <td>Transportation | Public Transportation Use</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>2205</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>145 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     metric_label geo_fips_desc  \\\n",
       "0     Active Transportation | Riding Bike to Work         place   \n",
       "1         Active Transportation | Walking to Work         place   \n",
       "2           Air Pollution | Hazardous Air Quality        county   \n",
       "3                Air Pollution | Poor Air Quality        county   \n",
       "4              Birth Control | Teen Birth Control         place   \n",
       "..                                            ...           ...   \n",
       "140                  Substance Use | Teen Smoking         place   \n",
       "141         Transportation | Drives Alone to Work         place   \n",
       "142                  Transportation | Lack of Car         place   \n",
       "143  Transportation | Longer Driving Commute Time         place   \n",
       "144    Transportation | Public Transportation Use         place   \n",
       "\n",
       "    geo_label_proxy_or_real       geo_label_proxy_footnote  count  \n",
       "0                      real                            NAN    455  \n",
       "1                      real                            NAN   2205  \n",
       "2                     proxy                            NAN    454  \n",
       "3                     proxy                            NAN    454  \n",
       "4                     proxy  Data are for school districts    387  \n",
       "..                      ...                            ...    ...  \n",
       "140                   proxy  Data are for school districts    545  \n",
       "141                    real                            NAN   2205  \n",
       "142                    real                            NAN    455  \n",
       "143                    real                            NAN    350  \n",
       "144                    real                            NAN   2205  \n",
       "\n",
       "[145 rows x 5 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df_metric_county =df_temp[['metric_label','geo_fips_desc','geo_label_proxy_or_real','geo_label_proxy_footnote']].value_counts().reset_index()\n",
    "df_metric_county = df_metric_county.sort_values(by=['metric_label','geo_fips_desc']).reset_index(drop = True)\n",
    "\n",
    "display(np.sum(df_metric_county.duplicated(subset=['metric_label','geo_fips_desc'],keep=False)))\n",
    "display(df_metric_county)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "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>metric_label</th>\n",
       "      <th>geo_fips_desc</th>\n",
       "      <th>geo_label_proxy_or_real</th>\n",
       "      <th>geo_label_proxy_footnote</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Births | Teen Births</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>2106</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Births | Teen Births</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>129</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Cancer | All Cancer Deaths</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>5908</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Cancer | All Cancer Deaths</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>312</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Cancer | Breast Cancer Deaths</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>1669</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Cancer | Breast Cancer Deaths</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Cancer | Colorectal Cancer Deaths</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>4740</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Cancer | Colorectal Cancer Deaths</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>304</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Cancer | Lung Cancer Deaths</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>5179</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Cancer | Lung Cancer Deaths</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>296</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Cancer | Prostate Cancer Deaths</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>1609</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Cancer | Prostate Cancer Deaths</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Cardiovascular Disease | Cardiovascular Diseas...</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>5871</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Cardiovascular Disease | Cardiovascular Diseas...</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>311</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Cardiovascular Disease | Heart Disease Deaths</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>5814</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Cardiovascular Disease | Heart Disease Deaths</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>311</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Crime Incidents | Homicides</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>4430</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Crime Incidents | Homicides</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>310</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Deaths | Deaths from All Causes</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>5923</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Deaths | Deaths from All Causes</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Deaths | Gun Deaths (Firearms)</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>4589</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Deaths | Gun Deaths (Firearms)</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>315</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Deaths | Injury Deaths</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>6022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Deaths | Injury Deaths</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>370</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Deaths | Motor Vehicle Deaths</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>4804</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Deaths | Motor Vehicle Deaths</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>Deaths | Premature Death</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>6435</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>Deaths | Premature Death</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>390</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>Diabetes and Obesity | Diabetes Deaths</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>5177</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>Diabetes and Obesity | Diabetes Deaths</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>299</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>Life Expectancy at Birth | Life Expectancy</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>4703</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>Life Expectancy at Birth | Life Expectancy</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>289</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>Mental Health | Suicide</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>4822</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>Mental Health | Suicide</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>315</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115</th>\n",
       "      <td>Respiratory Infection | COVID-19 Deaths</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>1317</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116</th>\n",
       "      <td>Respiratory Infection | COVID-19 Deaths</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>Respiratory Infection | Pneumonia or Influenza...</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>4535</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>Respiratory Infection | Pneumonia or Influenza...</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>292</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125</th>\n",
       "      <td>Sexually Transmitted Disease | HIV-Related Deaths</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>3134</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126</th>\n",
       "      <td>Sexually Transmitted Disease | HIV-Related Deaths</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>234</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>134</th>\n",
       "      <td>Substance Use | Drug Overdose Deaths</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>5124</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>135</th>\n",
       "      <td>Substance Use | Drug Overdose Deaths</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>326</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136</th>\n",
       "      <td>Substance Use | Opioid Overdose Deaths</td>\n",
       "      <td>place</td>\n",
       "      <td>real</td>\n",
       "      <td>NAN</td>\n",
       "      <td>4509</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>137</th>\n",
       "      <td>Substance Use | Opioid Overdose Deaths</td>\n",
       "      <td>place</td>\n",
       "      <td>proxy</td>\n",
       "      <td>County level data were used</td>\n",
       "      <td>305</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          metric_label geo_fips_desc  \\\n",
       "8                                 Births | Teen Births         place   \n",
       "9                                 Births | Teen Births         place   \n",
       "10                          Cancer | All Cancer Deaths         place   \n",
       "11                          Cancer | All Cancer Deaths         place   \n",
       "12                       Cancer | Breast Cancer Deaths         place   \n",
       "13                       Cancer | Breast Cancer Deaths         place   \n",
       "14                   Cancer | Colorectal Cancer Deaths         place   \n",
       "15                   Cancer | Colorectal Cancer Deaths         place   \n",
       "16                         Cancer | Lung Cancer Deaths         place   \n",
       "17                         Cancer | Lung Cancer Deaths         place   \n",
       "18                     Cancer | Prostate Cancer Deaths         place   \n",
       "19                     Cancer | Prostate Cancer Deaths         place   \n",
       "20   Cardiovascular Disease | Cardiovascular Diseas...         place   \n",
       "21   Cardiovascular Disease | Cardiovascular Diseas...         place   \n",
       "22       Cardiovascular Disease | Heart Disease Deaths         place   \n",
       "23       Cardiovascular Disease | Heart Disease Deaths         place   \n",
       "25                         Crime Incidents | Homicides         place   \n",
       "26                         Crime Incidents | Homicides         place   \n",
       "28                     Deaths | Deaths from All Causes         place   \n",
       "29                     Deaths | Deaths from All Causes         place   \n",
       "30                      Deaths | Gun Deaths (Firearms)         place   \n",
       "31                      Deaths | Gun Deaths (Firearms)         place   \n",
       "33                              Deaths | Injury Deaths         place   \n",
       "34                              Deaths | Injury Deaths         place   \n",
       "36                       Deaths | Motor Vehicle Deaths         place   \n",
       "37                       Deaths | Motor Vehicle Deaths         place   \n",
       "39                            Deaths | Premature Death         place   \n",
       "40                            Deaths | Premature Death         place   \n",
       "44              Diabetes and Obesity | Diabetes Deaths         place   \n",
       "45              Diabetes and Obesity | Diabetes Deaths         place   \n",
       "89          Life Expectancy at Birth | Life Expectancy         place   \n",
       "90          Life Expectancy at Birth | Life Expectancy         place   \n",
       "94                             Mental Health | Suicide         place   \n",
       "95                             Mental Health | Suicide         place   \n",
       "115            Respiratory Infection | COVID-19 Deaths         place   \n",
       "116            Respiratory Infection | COVID-19 Deaths         place   \n",
       "121  Respiratory Infection | Pneumonia or Influenza...         place   \n",
       "122  Respiratory Infection | Pneumonia or Influenza...         place   \n",
       "125  Sexually Transmitted Disease | HIV-Related Deaths         place   \n",
       "126  Sexually Transmitted Disease | HIV-Related Deaths         place   \n",
       "134               Substance Use | Drug Overdose Deaths         place   \n",
       "135               Substance Use | Drug Overdose Deaths         place   \n",
       "136             Substance Use | Opioid Overdose Deaths         place   \n",
       "137             Substance Use | Opioid Overdose Deaths         place   \n",
       "\n",
       "    geo_label_proxy_or_real     geo_label_proxy_footnote  count  \n",
       "8                      real                          NAN   2106  \n",
       "9                     proxy  County level data were used    129  \n",
       "10                     real                          NAN   5908  \n",
       "11                    proxy  County level data were used    312  \n",
       "12                     real                          NAN   1669  \n",
       "13                    proxy  County level data were used    104  \n",
       "14                     real                          NAN   4740  \n",
       "15                    proxy  County level data were used    304  \n",
       "16                     real                          NAN   5179  \n",
       "17                    proxy  County level data were used    296  \n",
       "18                     real                          NAN   1609  \n",
       "19                    proxy  County level data were used    104  \n",
       "20                     real                          NAN   5871  \n",
       "21                    proxy  County level data were used    311  \n",
       "22                     real                          NAN   5814  \n",
       "23                    proxy  County level data were used    311  \n",
       "25                     real                          NAN   4430  \n",
       "26                    proxy  County level data were used    310  \n",
       "28                     real                          NAN   5923  \n",
       "29                    proxy  County level data were used    308  \n",
       "30                     real                          NAN   4589  \n",
       "31                    proxy  County level data were used    315  \n",
       "33                     real                          NAN   6022  \n",
       "34                    proxy  County level data were used    370  \n",
       "36                     real                          NAN   4804  \n",
       "37                    proxy  County level data were used    308  \n",
       "39                     real                          NAN   6435  \n",
       "40                    proxy  County level data were used    390  \n",
       "44                     real                          NAN   5177  \n",
       "45                    proxy  County level data were used    299  \n",
       "89                     real                          NAN   4703  \n",
       "90                    proxy  County level data were used    289  \n",
       "94                     real                          NAN   4822  \n",
       "95                    proxy  County level data were used    315  \n",
       "115                    real                          NAN   1317  \n",
       "116                   proxy  County level data were used     79  \n",
       "121                    real                          NAN   4535  \n",
       "122                   proxy  County level data were used    292  \n",
       "125                    real                          NAN   3134  \n",
       "126                   proxy  County level data were used    234  \n",
       "134                    real                          NAN   5124  \n",
       "135                   proxy  County level data were used    326  \n",
       "136                    real                          NAN   4509  \n",
       "137                   proxy  County level data were used    305  "
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cond = (df_metric_county.duplicated(subset=['metric_label','geo_fips_desc'],keep=False))\n",
    "df_metric_county[cond]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 결론을 뭔가 내야 함"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## geo_strata"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "geo_strata_region\n",
       "West         66810\n",
       "South        62748\n",
       "Midwest      40546\n",
       "Northeast    18563\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "geo_strata_poverty\n",
       "Less poor cities (<18% poor)    141688\n",
       "Poorest cities (18%+ poor)       46979\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "geo_strata_Population\n",
       "Smaller (<1.3 million)    134116\n",
       "Largest (>1.3 million)     54550\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "geo_strata_PopDensity\n",
       "Lower pop. density (<10k per sq mi)      152508\n",
       "Highest pop. density (>10k per sq mi)     36158\n",
       "Smaller (<1.3 million)                        1\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "geo_strata_Segregation\n",
       "Less Segregated (<50%)      104633\n",
       "Highly Segregated (50%+)     84034\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "col_list = ['geo_strata_region',\n",
    " 'geo_strata_poverty',\n",
    " 'geo_strata_Population',\n",
    " 'geo_strata_PopDensity',\n",
    " 'geo_strata_Segregation']\n",
    "for col in col_list:\n",
    "    display(df_city[col].value_counts())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "for col in col_list:\n",
    "    display(np.sum(df_city[col].isna()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "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>metric_item_label</th>\n",
       "      <th>metric_cat_label</th>\n",
       "      <th>metric_subcat_label</th>\n",
       "      <th>metric_item_label_subtitle</th>\n",
       "      <th>metric_cat_item_yaxis_label</th>\n",
       "      <th>metric_source_desc_label_fn</th>\n",
       "      <th>metric_source_desc_label_url_fn</th>\n",
       "      <th>geo_label_city</th>\n",
       "      <th>geo_label_state</th>\n",
       "      <th>geo_label_citystate</th>\n",
       "      <th>...</th>\n",
       "      <th>value_90_ci_high</th>\n",
       "      <th>geo_strata_region</th>\n",
       "      <th>geo_strata_poverty</th>\n",
       "      <th>geo_strata_Population</th>\n",
       "      <th>geo_strata_PopDensity</th>\n",
       "      <th>geo_strata_Segregation</th>\n",
       "      <th>strata_race_label</th>\n",
       "      <th>strata_sex_label</th>\n",
       "      <th>strata_race_sex_label</th>\n",
       "      <th>metric_label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>All Cancer Deaths</td>\n",
       "      <td>Chronic Health Conditions</td>\n",
       "      <td>Cancer</td>\n",
       "      <td>Deaths from all types of cancer (per 100,000 p...</td>\n",
       "      <td>Per 100,000</td>\n",
       "      <td>National Vital Statistics System (NVSS), Cente...</td>\n",
       "      <td>https://www.cdc.gov/nchs/nvss/index.htm</td>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>NV</td>\n",
       "      <td>Las Vegas, NV</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>West</td>\n",
       "      <td>Less poor cities (&lt;18% poor)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Lower pop. density (&lt;10k per sq mi)</td>\n",
       "      <td>Less Segregated (&lt;50%)</td>\n",
       "      <td>All</td>\n",
       "      <td>Male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Cancer | All Cancer Deaths</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 32 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   metric_item_label           metric_cat_label metric_subcat_label  \\\n",
       "7  All Cancer Deaths  Chronic Health Conditions              Cancer   \n",
       "\n",
       "                          metric_item_label_subtitle  \\\n",
       "7  Deaths from all types of cancer (per 100,000 p...   \n",
       "\n",
       "  metric_cat_item_yaxis_label  \\\n",
       "7                 Per 100,000   \n",
       "\n",
       "                         metric_source_desc_label_fn  \\\n",
       "7  National Vital Statistics System (NVSS), Cente...   \n",
       "\n",
       "           metric_source_desc_label_url_fn geo_label_city geo_label_state  \\\n",
       "7  https://www.cdc.gov/nchs/nvss/index.htm      Las Vegas              NV   \n",
       "\n",
       "  geo_label_citystate  ...  value_90_ci_high  geo_strata_region  \\\n",
       "7       Las Vegas, NV  ...               NaN               West   \n",
       "\n",
       "             geo_strata_poverty geo_strata_Population  \\\n",
       "7  Less poor cities (<18% poor)                   NaN   \n",
       "\n",
       "                 geo_strata_PopDensity  geo_strata_Segregation  \\\n",
       "7  Lower pop. density (<10k per sq mi)  Less Segregated (<50%)   \n",
       "\n",
       "  strata_race_label strata_sex_label strata_race_sex_label  \\\n",
       "7               All             Male                   NaN   \n",
       "\n",
       "                 metric_label  \n",
       "7  Cancer | All Cancer Deaths  \n",
       "\n",
       "[1 rows x 32 columns]"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_city[df_city['geo_strata_Population'].isna()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cols = ['geo_label_city']+col_list\n",
    "np.sum(~df_city[cols].duplicated(keep=False))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "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>metric_item_label</th>\n",
       "      <th>metric_cat_label</th>\n",
       "      <th>metric_subcat_label</th>\n",
       "      <th>metric_item_label_subtitle</th>\n",
       "      <th>metric_cat_item_yaxis_label</th>\n",
       "      <th>metric_source_desc_label_fn</th>\n",
       "      <th>metric_source_desc_label_url_fn</th>\n",
       "      <th>geo_label_city</th>\n",
       "      <th>geo_label_state</th>\n",
       "      <th>geo_label_citystate</th>\n",
       "      <th>...</th>\n",
       "      <th>value_90_ci_high</th>\n",
       "      <th>geo_strata_region</th>\n",
       "      <th>geo_strata_poverty</th>\n",
       "      <th>geo_strata_Population</th>\n",
       "      <th>geo_strata_PopDensity</th>\n",
       "      <th>geo_strata_Segregation</th>\n",
       "      <th>strata_race_label</th>\n",
       "      <th>strata_sex_label</th>\n",
       "      <th>strata_race_sex_label</th>\n",
       "      <th>metric_label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>All Cancer Deaths</td>\n",
       "      <td>Chronic Health Conditions</td>\n",
       "      <td>Cancer</td>\n",
       "      <td>Deaths from all types of cancer (per 100,000 p...</td>\n",
       "      <td>Per 100,000</td>\n",
       "      <td>National Vital Statistics System (NVSS), Cente...</td>\n",
       "      <td>https://www.cdc.gov/nchs/nvss/index.htm</td>\n",
       "      <td>Long Beach</td>\n",
       "      <td>CA</td>\n",
       "      <td>Long Beach, CA</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>West</td>\n",
       "      <td>Less poor cities (&lt;18% poor)</td>\n",
       "      <td>Smaller (&lt;1.3 million)</td>\n",
       "      <td>Smaller (&lt;1.3 million)</td>\n",
       "      <td>Highly Segregated (50%+)</td>\n",
       "      <td>Hispanic</td>\n",
       "      <td>Female</td>\n",
       "      <td>Hispanic Female</td>\n",
       "      <td>Cancer | All Cancer Deaths</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>All Cancer Deaths</td>\n",
       "      <td>Chronic Health Conditions</td>\n",
       "      <td>Cancer</td>\n",
       "      <td>Deaths from all types of cancer (per 100,000 p...</td>\n",
       "      <td>Per 100,000</td>\n",
       "      <td>National Vital Statistics System (NVSS), Cente...</td>\n",
       "      <td>https://www.cdc.gov/nchs/nvss/index.htm</td>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>NV</td>\n",
       "      <td>Las Vegas, NV</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>West</td>\n",
       "      <td>Less poor cities (&lt;18% poor)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Lower pop. density (&lt;10k per sq mi)</td>\n",
       "      <td>Less Segregated (&lt;50%)</td>\n",
       "      <td>All</td>\n",
       "      <td>Male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Cancer | All Cancer Deaths</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 32 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   metric_item_label           metric_cat_label metric_subcat_label  \\\n",
       "3  All Cancer Deaths  Chronic Health Conditions              Cancer   \n",
       "7  All Cancer Deaths  Chronic Health Conditions              Cancer   \n",
       "\n",
       "                          metric_item_label_subtitle  \\\n",
       "3  Deaths from all types of cancer (per 100,000 p...   \n",
       "7  Deaths from all types of cancer (per 100,000 p...   \n",
       "\n",
       "  metric_cat_item_yaxis_label  \\\n",
       "3                 Per 100,000   \n",
       "7                 Per 100,000   \n",
       "\n",
       "                         metric_source_desc_label_fn  \\\n",
       "3  National Vital Statistics System (NVSS), Cente...   \n",
       "7  National Vital Statistics System (NVSS), Cente...   \n",
       "\n",
       "           metric_source_desc_label_url_fn geo_label_city geo_label_state  \\\n",
       "3  https://www.cdc.gov/nchs/nvss/index.htm     Long Beach              CA   \n",
       "7  https://www.cdc.gov/nchs/nvss/index.htm      Las Vegas              NV   \n",
       "\n",
       "  geo_label_citystate  ...  value_90_ci_high  geo_strata_region  \\\n",
       "3      Long Beach, CA  ...               NaN               West   \n",
       "7       Las Vegas, NV  ...               NaN               West   \n",
       "\n",
       "             geo_strata_poverty   geo_strata_Population  \\\n",
       "3  Less poor cities (<18% poor)  Smaller (<1.3 million)   \n",
       "7  Less poor cities (<18% poor)                     NaN   \n",
       "\n",
       "                 geo_strata_PopDensity    geo_strata_Segregation  \\\n",
       "3               Smaller (<1.3 million)  Highly Segregated (50%+)   \n",
       "7  Lower pop. density (<10k per sq mi)    Less Segregated (<50%)   \n",
       "\n",
       "  strata_race_label strata_sex_label strata_race_sex_label  \\\n",
       "3          Hispanic           Female       Hispanic Female   \n",
       "7               All             Male                   NaN   \n",
       "\n",
       "                 metric_label  \n",
       "3  Cancer | All Cancer Deaths  \n",
       "7  Cancer | All Cancer Deaths  \n",
       "\n",
       "[2 rows x 32 columns]"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_city[~df_city[cols].duplicated(keep=False)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "geo_label_city  geo_strata_region  geo_strata_poverty            geo_strata_Population   geo_strata_PopDensity                geo_strata_Segregation  \n",
       "Long Beach      West               Less poor cities (<18% poor)  Smaller (<1.3 million)  Lower pop. density (<10k per sq mi)  Highly Segregated (50%+)    5215\n",
       "                                                                                         Smaller (<1.3 million)               Highly Segregated (50%+)       1\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_city.loc[df_city['geo_label_city']=='Long Beach',cols].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "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>geo_label_city</th>\n",
       "      <th>geo_strata_region</th>\n",
       "      <th>geo_strata_poverty</th>\n",
       "      <th>geo_strata_Population</th>\n",
       "      <th>geo_strata_PopDensity</th>\n",
       "      <th>geo_strata_Segregation</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Chicago</td>\n",
       "      <td>Midwest</td>\n",
       "      <td>Less poor cities (&lt;18% poor)</td>\n",
       "      <td>Largest (&gt;1.3 million)</td>\n",
       "      <td>Highest pop. density (&gt;10k per sq mi)</td>\n",
       "      <td>Highly Segregated (50%+)</td>\n",
       "      <td>6210</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Indianapolis</td>\n",
       "      <td>Midwest</td>\n",
       "      <td>Less poor cities (&lt;18% poor)</td>\n",
       "      <td>Smaller (&lt;1.3 million)</td>\n",
       "      <td>Lower pop. density (&lt;10k per sq mi)</td>\n",
       "      <td>Less Segregated (&lt;50%)</td>\n",
       "      <td>4916</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Columbus</td>\n",
       "      <td>Midwest</td>\n",
       "      <td>Less poor cities (&lt;18% poor)</td>\n",
       "      <td>Smaller (&lt;1.3 million)</td>\n",
       "      <td>Lower pop. density (&lt;10k per sq mi)</td>\n",
       "      <td>Less Segregated (&lt;50%)</td>\n",
       "      <td>4907</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Kansas City</td>\n",
       "      <td>Midwest</td>\n",
       "      <td>Less poor cities (&lt;18% poor)</td>\n",
       "      <td>Smaller (&lt;1.3 million)</td>\n",
       "      <td>Lower pop. density (&lt;10k per sq mi)</td>\n",
       "      <td>Less Segregated (&lt;50%)</td>\n",
       "      <td>4848</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Minneapolis</td>\n",
       "      <td>Midwest</td>\n",
       "      <td>Less poor cities (&lt;18% poor)</td>\n",
       "      <td>Smaller (&lt;1.3 million)</td>\n",
       "      <td>Lower pop. density (&lt;10k per sq mi)</td>\n",
       "      <td>Less Segregated (&lt;50%)</td>\n",
       "      <td>4511</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Detroit</td>\n",
       "      <td>Midwest</td>\n",
       "      <td>Poorest cities (18%+ poor)</td>\n",
       "      <td>Smaller (&lt;1.3 million)</td>\n",
       "      <td>Lower pop. density (&lt;10k per sq mi)</td>\n",
       "      <td>Highly Segregated (50%+)</td>\n",
       "      <td>5160</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Cleveland</td>\n",
       "      <td>Midwest</td>\n",
       "      <td>Poorest cities (18%+ poor)</td>\n",
       "      <td>Smaller (&lt;1.3 million)</td>\n",
       "      <td>Lower pop. density (&lt;10k per sq mi)</td>\n",
       "      <td>Highly Segregated (50%+)</td>\n",
       "      <td>5113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Milwaukee</td>\n",
       "      <td>Midwest</td>\n",
       "      <td>Poorest cities (18%+ poor)</td>\n",
       "      <td>Smaller (&lt;1.3 million)</td>\n",
       "      <td>Lower pop. density (&lt;10k per sq mi)</td>\n",
       "      <td>Highly Segregated (50%+)</td>\n",
       "      <td>4881</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>New York City</td>\n",
       "      <td>Northeast</td>\n",
       "      <td>Less poor cities (&lt;18% poor)</td>\n",
       "      <td>Largest (&gt;1.3 million)</td>\n",
       "      <td>Highest pop. density (&gt;10k per sq mi)</td>\n",
       "      <td>Highly Segregated (50%+)</td>\n",
       "      <td>6535</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Boston</td>\n",
       "      <td>Northeast</td>\n",
       "      <td>Less poor cities (&lt;18% poor)</td>\n",
       "      <td>Smaller (&lt;1.3 million)</td>\n",
       "      <td>Highest pop. density (&gt;10k per sq mi)</td>\n",
       "      <td>Highly Segregated (50%+)</td>\n",
       "      <td>5718</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Philadelphia</td>\n",
       "      <td>Northeast</td>\n",
       "      <td>Poorest cities (18%+ poor)</td>\n",
       "      <td>Largest (&gt;1.3 million)</td>\n",
       "      <td>Highest pop. density (&gt;10k per sq mi)</td>\n",
       "      <td>Highly Segregated (50%+)</td>\n",
       "      <td>6310</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Dallas</td>\n",
       "      <td>South</td>\n",
       "      <td>Less poor cities (&lt;18% poor)</td>\n",
       "      <td>Largest (&gt;1.3 million)</td>\n",
       "      <td>Lower pop. density (&lt;10k per sq mi)</td>\n",
       "      <td>Highly Segregated (50%+)</td>\n",
       "      <td>5655</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>San Antonio</td>\n",
       "      <td>South</td>\n",
       "      <td>Less poor cities (&lt;18% poor)</td>\n",
       "      <td>Largest (&gt;1.3 million)</td>\n",
       "      <td>Lower pop. density (&lt;10k per sq mi)</td>\n",
       "      <td>Less Segregated (&lt;50%)</td>\n",
       "      <td>5349</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Washington</td>\n",
       "      <td>South</td>\n",
       "      <td>Less poor cities (&lt;18% poor)</td>\n",
       "      <td>Smaller (&lt;1.3 million)</td>\n",
       "      <td>Highest pop. density (&gt;10k per sq mi)</td>\n",
       "      <td>Highly Segregated (50%+)</td>\n",
       "      <td>5264</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Fort Worth</td>\n",
       "      <td>South</td>\n",
       "      <td>Less poor cities (&lt;18% poor)</td>\n",
       "      <td>Smaller (&lt;1.3 million)</td>\n",
       "      <td>Lower pop. density (&lt;10k per sq mi)</td>\n",
       "      <td>Less Segregated (&lt;50%)</td>\n",
       "      <td>5628</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Charlotte</td>\n",
       "      <td>South</td>\n",
       "      <td>Less poor cities (&lt;18% poor)</td>\n",
       "      <td>Smaller (&lt;1.3 million)</td>\n",
       "      <td>Lower pop. density (&lt;10k per sq mi)</td>\n",
       "      <td>Less Segregated (&lt;50%)</td>\n",
       "      <td>5381</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Austin</td>\n",
       "      <td>South</td>\n",
       "      <td>Less poor cities (&lt;18% poor)</td>\n",
       "      <td>Smaller (&lt;1.3 million)</td>\n",
       "      <td>Lower pop. density (&lt;10k per sq mi)</td>\n",
       "      <td>Less Segregated (&lt;50%)</td>\n",
       "      <td>5198</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Oklahoma City</td>\n",
       "      <td>South</td>\n",
       "      <td>Less poor cities (&lt;18% poor)</td>\n",
       "      <td>Smaller (&lt;1.3 million)</td>\n",
       "      <td>Lower pop. density (&lt;10k per sq mi)</td>\n",
       "      <td>Less Segregated (&lt;50%)</td>\n",
       "      <td>5030</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Louisville</td>\n",
       "      <td>South</td>\n",
       "      <td>Less poor cities (&lt;18% poor)</td>\n",
       "      <td>Smaller (&lt;1.3 million)</td>\n",
       "      <td>Lower pop. density (&lt;10k per sq mi)</td>\n",
       "      <td>Less Segregated (&lt;50%)</td>\n",
       "      <td>4527</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Houston</td>\n",
       "      <td>South</td>\n",
       "      <td>Poorest cities (18%+ poor)</td>\n",
       "      <td>Largest (&gt;1.3 million)</td>\n",
       "      <td>Lower pop. density (&lt;10k per sq mi)</td>\n",
       "      <td>Highly Segregated (50%+)</td>\n",
       "      <td>6431</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Baltimore</td>\n",
       "      <td>South</td>\n",
       "      <td>Poorest cities (18%+ poor)</td>\n",
       "      <td>Smaller (&lt;1.3 million)</td>\n",
       "      <td>Lower pop. density (&lt;10k per sq mi)</td>\n",
       "      <td>Highly Segregated (50%+)</td>\n",
       "      <td>5101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Memphis</td>\n",
       "      <td>South</td>\n",
       "      <td>Poorest cities (18%+ poor)</td>\n",
       "      <td>Smaller (&lt;1.3 million)</td>\n",
       "      <td>Lower pop. density (&lt;10k per sq mi)</td>\n",
       "      <td>Highly Segregated (50%+)</td>\n",
       "      <td>4631</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>El Paso</td>\n",
       "      <td>South</td>\n",
       "      <td>Poorest cities (18%+ poor)</td>\n",
       "      <td>Smaller (&lt;1.3 million)</td>\n",
       "      <td>Lower pop. density (&lt;10k per sq mi)</td>\n",
       "      <td>Less Segregated (&lt;50%)</td>\n",
       "      <td>4553</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>West</td>\n",
       "      <td>Less poor cities (&lt;18% poor)</td>\n",
       "      <td>Largest (&gt;1.3 million)</td>\n",
       "      <td>Lower pop. density (&lt;10k per sq mi)</td>\n",
       "      <td>Highly Segregated (50%+)</td>\n",
       "      <td>6372</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Phoenix</td>\n",
       "      <td>West</td>\n",
       "      <td>Less poor cities (&lt;18% poor)</td>\n",
       "      <td>Largest (&gt;1.3 million)</td>\n",
       "      <td>Lower pop. density (&lt;10k per sq mi)</td>\n",
       "      <td>Highly Segregated (50%+)</td>\n",
       "      <td>5437</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>San Diego</td>\n",
       "      <td>West</td>\n",
       "      <td>Less poor cities (&lt;18% poor)</td>\n",
       "      <td>Largest (&gt;1.3 million)</td>\n",
       "      <td>Lower pop. density (&lt;10k per sq mi)</td>\n",
       "      <td>Less Segregated (&lt;50%)</td>\n",
       "      <td>6251</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>West</td>\n",
       "      <td>Less poor cities (&lt;18% poor)</td>\n",
       "      <td>NAN</td>\n",
       "      <td>Lower pop. density (&lt;10k per sq mi)</td>\n",
       "      <td>Less Segregated (&lt;50%)</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>West</td>\n",
       "      <td>Less poor cities (&lt;18% poor)</td>\n",
       "      <td>Smaller (&lt;1.3 million)</td>\n",
       "      <td>Highest pop. density (&gt;10k per sq mi)</td>\n",
       "      <td>Less Segregated (&lt;50%)</td>\n",
       "      <td>6121</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Long Beach</td>\n",
       "      <td>West</td>\n",
       "      <td>Less poor cities (&lt;18% poor)</td>\n",
       "      <td>Smaller (&lt;1.3 million)</td>\n",
       "      <td>Lower pop. density (&lt;10k per sq mi)</td>\n",
       "      <td>Highly Segregated (50%+)</td>\n",
       "      <td>5215</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>West</td>\n",
       "      <td>Less poor cities (&lt;18% poor)</td>\n",
       "      <td>Smaller (&lt;1.3 million)</td>\n",
       "      <td>Lower pop. density (&lt;10k per sq mi)</td>\n",
       "      <td>Less Segregated (&lt;50%)</td>\n",
       "      <td>5848</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Oakland</td>\n",
       "      <td>West</td>\n",
       "      <td>Less poor cities (&lt;18% poor)</td>\n",
       "      <td>Smaller (&lt;1.3 million)</td>\n",
       "      <td>Lower pop. density (&lt;10k per sq mi)</td>\n",
       "      <td>Less Segregated (&lt;50%)</td>\n",
       "      <td>5676</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Seattle</td>\n",
       "      <td>West</td>\n",
       "      <td>Less poor cities (&lt;18% poor)</td>\n",
       "      <td>Smaller (&lt;1.3 million)</td>\n",
       "      <td>Lower pop. density (&lt;10k per sq mi)</td>\n",
       "      <td>Less Segregated (&lt;50%)</td>\n",
       "      <td>5507</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Portland</td>\n",
       "      <td>West</td>\n",
       "      <td>Less poor cities (&lt;18% poor)</td>\n",
       "      <td>Smaller (&lt;1.3 million)</td>\n",
       "      <td>Lower pop. density (&lt;10k per sq mi)</td>\n",
       "      <td>Less Segregated (&lt;50%)</td>\n",
       "      <td>5219</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>San Jose</td>\n",
       "      <td>West</td>\n",
       "      <td>Less poor cities (&lt;18% poor)</td>\n",
       "      <td>Smaller (&lt;1.3 million)</td>\n",
       "      <td>Lower pop. density (&lt;10k per sq mi)</td>\n",
       "      <td>Less Segregated (&lt;50%)</td>\n",
       "      <td>5206</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Denver</td>\n",
       "      <td>West</td>\n",
       "      <td>Less poor cities (&lt;18% poor)</td>\n",
       "      <td>Smaller (&lt;1.3 million)</td>\n",
       "      <td>Lower pop. density (&lt;10k per sq mi)</td>\n",
       "      <td>Less Segregated (&lt;50%)</td>\n",
       "      <td>5157</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Long Beach</td>\n",
       "      <td>West</td>\n",
       "      <td>Less poor cities (&lt;18% poor)</td>\n",
       "      <td>Smaller (&lt;1.3 million)</td>\n",
       "      <td>Smaller (&lt;1.3 million)</td>\n",
       "      <td>Highly Segregated (50%+)</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Tucson</td>\n",
       "      <td>West</td>\n",
       "      <td>Poorest cities (18%+ poor)</td>\n",
       "      <td>Smaller (&lt;1.3 million)</td>\n",
       "      <td>Lower pop. density (&lt;10k per sq mi)</td>\n",
       "      <td>Less Segregated (&lt;50%)</td>\n",
       "      <td>4799</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   geo_label_city geo_strata_region            geo_strata_poverty  \\\n",
       "5         Chicago           Midwest  Less poor cities (<18% poor)   \n",
       "26   Indianapolis           Midwest  Less poor cities (<18% poor)   \n",
       "27       Columbus           Midwest  Less poor cities (<18% poor)   \n",
       "29    Kansas City           Midwest  Less poor cities (<18% poor)   \n",
       "34    Minneapolis           Midwest  Less poor cities (<18% poor)   \n",
       "21        Detroit           Midwest    Poorest cities (18%+ poor)   \n",
       "23      Cleveland           Midwest    Poorest cities (18%+ poor)   \n",
       "28      Milwaukee           Midwest    Poorest cities (18%+ poor)   \n",
       "0   New York City         Northeast  Less poor cities (<18% poor)   \n",
       "8          Boston         Northeast  Less poor cities (<18% poor)   \n",
       "3    Philadelphia         Northeast    Poorest cities (18%+ poor)   \n",
       "10         Dallas             South  Less poor cities (<18% poor)   \n",
       "15    San Antonio             South  Less poor cities (<18% poor)   \n",
       "16     Washington             South  Less poor cities (<18% poor)   \n",
       "11     Fort Worth             South  Less poor cities (<18% poor)   \n",
       "14      Charlotte             South  Less poor cities (<18% poor)   \n",
       "20         Austin             South  Less poor cities (<18% poor)   \n",
       "25  Oklahoma City             South  Less poor cities (<18% poor)   \n",
       "33     Louisville             South  Less poor cities (<18% poor)   \n",
       "1         Houston             South    Poorest cities (18%+ poor)   \n",
       "24      Baltimore             South    Poorest cities (18%+ poor)   \n",
       "31        Memphis             South    Poorest cities (18%+ poor)   \n",
       "32        El Paso             South    Poorest cities (18%+ poor)   \n",
       "2     Los Angeles              West  Less poor cities (<18% poor)   \n",
       "13        Phoenix              West  Less poor cities (<18% poor)   \n",
       "4       San Diego              West  Less poor cities (<18% poor)   \n",
       "35      Las Vegas              West  Less poor cities (<18% poor)   \n",
       "6   San Francisco              West  Less poor cities (<18% poor)   \n",
       "18     Long Beach              West  Less poor cities (<18% poor)   \n",
       "7       Las Vegas              West  Less poor cities (<18% poor)   \n",
       "9         Oakland              West  Less poor cities (<18% poor)   \n",
       "12        Seattle              West  Less poor cities (<18% poor)   \n",
       "17       Portland              West  Less poor cities (<18% poor)   \n",
       "19       San Jose              West  Less poor cities (<18% poor)   \n",
       "22         Denver              West  Less poor cities (<18% poor)   \n",
       "36     Long Beach              West  Less poor cities (<18% poor)   \n",
       "30         Tucson              West    Poorest cities (18%+ poor)   \n",
       "\n",
       "     geo_strata_Population                  geo_strata_PopDensity  \\\n",
       "5   Largest (>1.3 million)  Highest pop. density (>10k per sq mi)   \n",
       "26  Smaller (<1.3 million)    Lower pop. density (<10k per sq mi)   \n",
       "27  Smaller (<1.3 million)    Lower pop. density (<10k per sq mi)   \n",
       "29  Smaller (<1.3 million)    Lower pop. density (<10k per sq mi)   \n",
       "34  Smaller (<1.3 million)    Lower pop. density (<10k per sq mi)   \n",
       "21  Smaller (<1.3 million)    Lower pop. density (<10k per sq mi)   \n",
       "23  Smaller (<1.3 million)    Lower pop. density (<10k per sq mi)   \n",
       "28  Smaller (<1.3 million)    Lower pop. density (<10k per sq mi)   \n",
       "0   Largest (>1.3 million)  Highest pop. density (>10k per sq mi)   \n",
       "8   Smaller (<1.3 million)  Highest pop. density (>10k per sq mi)   \n",
       "3   Largest (>1.3 million)  Highest pop. density (>10k per sq mi)   \n",
       "10  Largest (>1.3 million)    Lower pop. density (<10k per sq mi)   \n",
       "15  Largest (>1.3 million)    Lower pop. density (<10k per sq mi)   \n",
       "16  Smaller (<1.3 million)  Highest pop. density (>10k per sq mi)   \n",
       "11  Smaller (<1.3 million)    Lower pop. density (<10k per sq mi)   \n",
       "14  Smaller (<1.3 million)    Lower pop. density (<10k per sq mi)   \n",
       "20  Smaller (<1.3 million)    Lower pop. density (<10k per sq mi)   \n",
       "25  Smaller (<1.3 million)    Lower pop. density (<10k per sq mi)   \n",
       "33  Smaller (<1.3 million)    Lower pop. density (<10k per sq mi)   \n",
       "1   Largest (>1.3 million)    Lower pop. density (<10k per sq mi)   \n",
       "24  Smaller (<1.3 million)    Lower pop. density (<10k per sq mi)   \n",
       "31  Smaller (<1.3 million)    Lower pop. density (<10k per sq mi)   \n",
       "32  Smaller (<1.3 million)    Lower pop. density (<10k per sq mi)   \n",
       "2   Largest (>1.3 million)    Lower pop. density (<10k per sq mi)   \n",
       "13  Largest (>1.3 million)    Lower pop. density (<10k per sq mi)   \n",
       "4   Largest (>1.3 million)    Lower pop. density (<10k per sq mi)   \n",
       "35                     NAN    Lower pop. density (<10k per sq mi)   \n",
       "6   Smaller (<1.3 million)  Highest pop. density (>10k per sq mi)   \n",
       "18  Smaller (<1.3 million)    Lower pop. density (<10k per sq mi)   \n",
       "7   Smaller (<1.3 million)    Lower pop. density (<10k per sq mi)   \n",
       "9   Smaller (<1.3 million)    Lower pop. density (<10k per sq mi)   \n",
       "12  Smaller (<1.3 million)    Lower pop. density (<10k per sq mi)   \n",
       "17  Smaller (<1.3 million)    Lower pop. density (<10k per sq mi)   \n",
       "19  Smaller (<1.3 million)    Lower pop. density (<10k per sq mi)   \n",
       "22  Smaller (<1.3 million)    Lower pop. density (<10k per sq mi)   \n",
       "36  Smaller (<1.3 million)                 Smaller (<1.3 million)   \n",
       "30  Smaller (<1.3 million)    Lower pop. density (<10k per sq mi)   \n",
       "\n",
       "      geo_strata_Segregation  count  \n",
       "5   Highly Segregated (50%+)   6210  \n",
       "26    Less Segregated (<50%)   4916  \n",
       "27    Less Segregated (<50%)   4907  \n",
       "29    Less Segregated (<50%)   4848  \n",
       "34    Less Segregated (<50%)   4511  \n",
       "21  Highly Segregated (50%+)   5160  \n",
       "23  Highly Segregated (50%+)   5113  \n",
       "28  Highly Segregated (50%+)   4881  \n",
       "0   Highly Segregated (50%+)   6535  \n",
       "8   Highly Segregated (50%+)   5718  \n",
       "3   Highly Segregated (50%+)   6310  \n",
       "10  Highly Segregated (50%+)   5655  \n",
       "15    Less Segregated (<50%)   5349  \n",
       "16  Highly Segregated (50%+)   5264  \n",
       "11    Less Segregated (<50%)   5628  \n",
       "14    Less Segregated (<50%)   5381  \n",
       "20    Less Segregated (<50%)   5198  \n",
       "25    Less Segregated (<50%)   5030  \n",
       "33    Less Segregated (<50%)   4527  \n",
       "1   Highly Segregated (50%+)   6431  \n",
       "24  Highly Segregated (50%+)   5101  \n",
       "31  Highly Segregated (50%+)   4631  \n",
       "32    Less Segregated (<50%)   4553  \n",
       "2   Highly Segregated (50%+)   6372  \n",
       "13  Highly Segregated (50%+)   5437  \n",
       "4     Less Segregated (<50%)   6251  \n",
       "35    Less Segregated (<50%)      1  \n",
       "6     Less Segregated (<50%)   6121  \n",
       "18  Highly Segregated (50%+)   5215  \n",
       "7     Less Segregated (<50%)   5848  \n",
       "9     Less Segregated (<50%)   5676  \n",
       "12    Less Segregated (<50%)   5507  \n",
       "17    Less Segregated (<50%)   5219  \n",
       "19    Less Segregated (<50%)   5206  \n",
       "22    Less Segregated (<50%)   5157  \n",
       "36  Highly Segregated (50%+)      1  \n",
       "30    Less Segregated (<50%)   4799  "
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_city[cols].fillna('NAN').value_counts().reset_index(drop=False).sort_values(by =col_list)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**결론**\n",
    "- las vegas, long beach에 대해서 오기입된 란이 한 record 씩 있음\n",
    "- 그외의 도시는 연도/record 상관없이 통일되어있음\n",
    "- 도시간의 metric을 외부에 따로 저장해두고 사용하면 될 듯"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## TODO\n",
    "- 도시간 정보 정리하여 저장하기\n",
    "- 도시간 metric 만들기\n",
    "- county 관련 값이 들어간 것들 확실히 체크함 해야할 듯"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "EST_NA",
   "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"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}