df, meta cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Merging data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import modules\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>Unnamed: 0</th>\n",
       "      <th>zipcode</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>county</th>\n",
       "      <th>fips</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>501</td>\n",
       "      <td>40.922326</td>\n",
       "      <td>-72.637078</td>\n",
       "      <td>Holtsville</td>\n",
       "      <td>NY</td>\n",
       "      <td>Suffolk</td>\n",
       "      <td>36103.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>544</td>\n",
       "      <td>40.922326</td>\n",
       "      <td>-72.637078</td>\n",
       "      <td>Holtsville</td>\n",
       "      <td>NY</td>\n",
       "      <td>Suffolk</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>601</td>\n",
       "      <td>18.165273</td>\n",
       "      <td>-66.722583</td>\n",
       "      <td>Adjuntas</td>\n",
       "      <td>PR</td>\n",
       "      <td>Adjuntas</td>\n",
       "      <td>72001.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>601</td>\n",
       "      <td>18.165273</td>\n",
       "      <td>-66.722583</td>\n",
       "      <td>Adjuntas</td>\n",
       "      <td>PR</td>\n",
       "      <td>Adjuntas</td>\n",
       "      <td>72113.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>602</td>\n",
       "      <td>18.393103</td>\n",
       "      <td>-67.180953</td>\n",
       "      <td>Aguada</td>\n",
       "      <td>PR</td>\n",
       "      <td>Aguada</td>\n",
       "      <td>72003.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  zipcode   latitude  longitude        city state    county  \\\n",
       "0           0      501  40.922326 -72.637078  Holtsville    NY   Suffolk   \n",
       "1           1      544  40.922326 -72.637078  Holtsville    NY   Suffolk   \n",
       "2           2      601  18.165273 -66.722583    Adjuntas    PR  Adjuntas   \n",
       "3           3      601  18.165273 -66.722583    Adjuntas    PR  Adjuntas   \n",
       "4           4      602  18.393103 -67.180953      Aguada    PR    Aguada   \n",
       "\n",
       "      fips  \n",
       "0  36103.0  \n",
       "1      NaN  \n",
       "2  72001.0  \n",
       "3  72113.0  \n",
       "4  72003.0  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# import zipcodes\n",
    "zipcode = pd.read_csv(\"data/zipfips.csv\")\n",
    "zipcode.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# function for data files\n",
    "\n",
    "def imp_acsdf(num, samp ):\n",
    "    dfname = \"df\" + num \n",
    "    print(\"Dataframe Name is: %s\" % dfname)\n",
    "    if samp == False:\n",
    "        dfname = pd.read_csv(\"./data/ACS_16_5YR_DP\" + num + \"_with_ann.csv\",skiprows=[1], low_memory=False)\n",
    "    else:\n",
    "        dfname = pd.read_csv(\"./data/ACS_16_5YR_DP\" + num + \"_with_ann.csv\", nrows = 1000, skiprows=[1] )\n",
    "    print (dfname.shape)\n",
    "    return dfname"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# function for meta files\n",
    "\n",
    "def imp_acsmeta(num ):\n",
    "\n",
    "    metaname = \"meta\" + num\n",
    "\n",
    "    print(\"MetaData Name is: %s\" % metaname)\n",
    "    metaname = pd.read_csv(\"./data/ACS_16_5YR_DP\" + num + \"_metadata.csv\", header=None, index_col = 0 )\n",
    "    print(metaname.shape)\n",
    "    return metaname"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Dataframe Name is: df02\n",
      "(32989, 611)\n",
      "MetaData Name is: meta02\n",
      "(611, 1)\n",
      "Dataframe Name is: df03\n",
      "(33120, 551)\n",
      "MetaData Name is: meta03\n",
      "(551, 1)\n",
      "Dataframe Name is: df04\n",
      "(33120, 575)\n",
      "MetaData Name is: meta04\n",
      "(575, 1)\n",
      "Dataframe Name is: df05\n",
      "(33120, 339)\n",
      "MetaData Name is: meta05\n",
      "(339, 1)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "dict_keys(['df02', 'df03', 'df04', 'df05'])"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dict_df = {}\n",
    "dict_meta={}\n",
    "\n",
    "for i in range(4):\n",
    "    numstr = \"0\" + str(i+2)\n",
    "\n",
    "    key_namedf = \"df\" + numstr     \n",
    "\n",
    "    dict_df[key_namedf] = imp_acsdf(num = numstr, samp = False) # change to false to import all data\n",
    "\n",
    "    key_namemeta = \"meta\" + numstr    \n",
    "\n",
    "    dict_meta[key_namemeta] = imp_acsmeta(num = numstr)\n",
    "       \n",
    "\n",
    "dict_df.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# add df and meta to dict\n",
    "df02 = dict_df['df02']\n",
    "df03 = dict_df['df03']\n",
    "df04 = dict_df['df04']\n",
    "df05 = dict_df['df05']\n",
    "meta02 = dict_meta['meta02'].to_dict()[1]\n",
    "meta03 = dict_meta['meta03'].to_dict()[1]\n",
    "meta04 = dict_meta['meta04'].to_dict()[1]\n",
    "meta05 = dict_meta['meta05'].to_dict()[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# function to extact estimates\n",
    "def extract_Est(df):\n",
    "    df = df[df.columns.drop(list(df.filter(regex='HC02')))]\n",
    "    df = df[df.columns.drop(list(df.filter(regex='HC03')))]\n",
    "    df = df[df.columns.drop(list(df.filter(regex='HC04')))]\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Extract only the estimates in df which all start with 'HCO1'\n",
    "df02 = extract_Est(df02)\n",
    "df03 = extract_Est(df03)\n",
    "df04 = extract_Est(df04)\n",
    "df05 = extract_Est(df05)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Function to extract meaningful categories from variable names\n",
    "def extract_catdf(df, dname, dno):\n",
    "    df = pd.DataFrame.from_dict(df,  orient='index')\n",
    "    df = df.rename_axis('v').reset_index().rename(columns={0:'detail'})\n",
    "    df =df.iloc[3:] \n",
    "    df['type'] = df.detail.str.split(';').str[0]\n",
    "    df['cat0'] = dname \n",
    "    df['cat1'] = df.detail.str.split(';').str[1].str.split('-',1).str[0].str.strip()\n",
    "    df['cat2'] = df.detail.str.split(';').str[1].str.split('-',1).str[1].str.strip()\n",
    "    df['vname'] = dno + df.v\n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# name categories extracted from variable names\n",
    "m02 = extract_catdf(meta02, \"Social\", \"02\")\n",
    "m03 = extract_catdf(meta03, \"Economic\",\"03\")\n",
    "m04 = extract_catdf(meta04, \"Housing\",\"04\")\n",
    "m05 = extract_catdf(meta05 , \"Demographic\",\"05\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# concatenate meta categories\n",
    "meta = pd.concat([m02, m03, m04, m05], ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Estimate' 'Margin of Error' 'Percent' 'Percent Margin of Error']\n",
      "['Social' 'Economic' 'Housing' 'Demographic']\n",
      "['HOUSEHOLDS BY TYPE' 'RELATIONSHIP' 'MARITAL STATUS' 'FERTILITY'\n",
      " 'GRANDPARENTS' 'SCHOOL ENROLLMENT' 'EDUCATIONAL ATTAINMENT'\n",
      " 'VETERAN STATUS'\n",
      " 'DISABILITY STATUS OF THE CIVILIAN NONINSTITUTIONALIZED POPULATION'\n",
      " 'RESIDENCE 1 YEAR AGO' 'PLACE OF BIRTH' 'U.S. CITIZENSHIP STATUS'\n",
      " 'YEAR OF ENTRY' 'WORLD REGION OF BIRTH OF FOREIGN BORN'\n",
      " 'LANGUAGE SPOKEN AT HOME' 'ANCESTRY' 'COMPUTERS AND INTERNET USE'\n",
      " 'EMPLOYMENT STATUS' 'COMMUTING TO WORK' 'OCCUPATION' 'INDUSTRY'\n",
      " 'CLASS OF WORKER' 'INCOME AND BENEFITS (IN 2016 INFLATION'\n",
      " 'HEALTH INSURANCE COVERAGE'\n",
      " 'PERCENTAGE OF FAMILIES AND PEOPLE WHOSE INCOME IN THE PAST 12 MONTHS IS BELOW THE POVERTY LEVEL'\n",
      " 'HOUSING OCCUPANCY' 'UNITS IN STRUCTURE' 'YEAR STRUCTURE BUILT' 'ROOMS'\n",
      " 'BEDROOMS' 'HOUSING TENURE' 'YEAR HOUSEHOLDER MOVED INTO UNIT'\n",
      " 'VEHICLES AVAILABLE' 'HOUSE HEATING FUEL' 'SELECTED CHARACTERISTICS'\n",
      " 'OCCUPANTS PER ROOM' 'VALUE' 'MORTGAGE STATUS'\n",
      " 'SELECTED MONTHLY OWNER COSTS (SMOC)'\n",
      " 'SELECTED MONTHLY OWNER COSTS AS A PERCENTAGE OF HOUSEHOLD INCOME (SMOCAPI)'\n",
      " 'GROSS RENT' 'GROSS RENT AS A PERCENTAGE OF HOUSEHOLD INCOME (GRAPI)'\n",
      " 'SEX AND AGE' 'RACE' 'HISPANIC OR LATINO AND RACE'\n",
      " 'CITIZEN, VOTING AGE POPULATION']\n",
      "['Total households' 'Total households - Family households (families)'\n",
      " 'Total households - Family households (families) - With own children of the householder under 18 years'\n",
      " 'Total households - Family households (families) - Married-couple family'\n",
      " 'Total households - Family households (families) - Married-couple family - With own children of the householder under 18 years'\n",
      " 'Total households - Family households (families) - Male householder, no wife present, family'\n",
      " 'Total households - Family households (families) - Male householder, no wife present, family - With own children of the householder under 18 years'\n",
      " 'Total households - Family households (families) - Female householder, no husband present, family'\n",
      " 'Total households - Family households (families) - Female householder, no husband present, family - With own children of the householder under 18 years'\n",
      " 'Total households - Nonfamily households'\n",
      " 'Total households - Nonfamily households - Householder living alone'\n",
      " 'Total households - Nonfamily households - Householder living alone - 65 years and over'\n",
      " 'Households with one or more people under 18 years'\n",
      " 'Households with one or more people 65 years and over'\n",
      " 'Average household size' 'Average family size' 'Population in households'\n",
      " 'Population in households - Householder'\n",
      " 'Population in households - Spouse' 'Population in households - Child'\n",
      " 'Population in households - Other relatives'\n",
      " 'Population in households - Nonrelatives'\n",
      " 'Population in households - Nonrelatives - Unmarried partner'\n",
      " 'Males 15 years and over' 'Males 15 years and over - Never married'\n",
      " 'Males 15 years and over - Now married, except separated'\n",
      " 'Males 15 years and over - Separated' 'Males 15 years and over - Widowed'\n",
      " 'Males 15 years and over - Divorced' 'Females 15 years and over'\n",
      " 'Females 15 years and over - Never married'\n",
      " 'Females 15 years and over - Now married, except separated'\n",
      " 'Females 15 years and over - Separated'\n",
      " 'Females 15 years and over - Widowed'\n",
      " 'Females 15 years and over - Divorced'\n",
      " 'Number of women 15 to 50 years old who had a birth in the past 12 months'\n",
      " 'Number of women 15 to 50 years old who had a birth in the past 12 months - Unmarried women (widowed, divorced, and never married)'\n",
      " 'Number of women 15 to 50 years old who had a birth in the past 12 months - Unmarried women (widowed, divorced, and never married) - Per 1,000 unmarried women'\n",
      " 'Number of women 15 to 50 years old who had a birth in the past 12 months - Per 1,000 women 15 to 50 years old'\n",
      " 'Number of women 15 to 50 years old who had a birth in the past 12 months - Per 1,000 women 15 to 19 years old'\n",
      " 'Number of women 15 to 50 years old who had a birth in the past 12 months - Per 1,000 women 20 to 34 years old'\n",
      " 'Number of women 15 to 50 years old who had a birth in the past 12 months - Per 1,000 women 35 to 50 years old'\n",
      " 'Number of grandparents living with own grandchildren under 18 years'\n",
      " 'Number of grandparents living with own grandchildren under 18 years - Grandparents responsible for grandchildren'\n",
      " 'Number of grandparents living with own grandchildren under 18 years - Years responsible for grandchildren - Less than 1 year'\n",
      " 'Number of grandparents living with own grandchildren under 18 years - Years responsible for grandchildren - 1 or 2 years'\n",
      " 'Number of grandparents living with own grandchildren under 18 years - Years responsible for grandchildren - 3 or 4 years'\n",
      " 'Number of grandparents living with own grandchildren under 18 years - Years responsible for grandchildren - 5 or more years'\n",
      " 'Number of grandparents responsible for own grandchildren under 18 years'\n",
      " 'Number of grandparents responsible for own grandchildren under 18 years - Who are female'\n",
      " 'Number of grandparents responsible for own grandchildren under 18 years - Who are married'\n",
      " 'Population 3 years and over enrolled in school'\n",
      " 'Population 3 years and over enrolled in school - Nursery school, preschool'\n",
      " 'Population 3 years and over enrolled in school - Kindergarten'\n",
      " 'Population 3 years and over enrolled in school - Elementary school (grades 1-8)'\n",
      " 'Population 3 years and over enrolled in school - High school (grades 9-12)'\n",
      " 'Population 3 years and over enrolled in school - College or graduate school'\n",
      " 'Population 25 years and over'\n",
      " 'Population 25 years and over - Less than 9th grade'\n",
      " 'Population 25 years and over - 9th to 12th grade, no diploma'\n",
      " 'Population 25 years and over - High school graduate (includes equivalency)'\n",
      " 'Population 25 years and over - Some college, no degree'\n",
      " \"Population 25 years and over - Associate's degree\"\n",
      " \"Population 25 years and over - Bachelor's degree\"\n",
      " 'Population 25 years and over - Graduate or professional degree'\n",
      " 'Percent high school graduate or higher'\n",
      " \"Percent bachelor's degree or higher\"\n",
      " 'Civilian population 18 years and over'\n",
      " 'Civilian population 18 years and over - Civilian veterans'\n",
      " 'Total Civilian Noninstitutionalized Population'\n",
      " 'Total Civilian Noninstitutionalized Population - With a disability'\n",
      " 'Under 18 years' 'Under 18 years - With a disability' '18 to 64 years'\n",
      " '18 to 64 years - With a disability' '65 years and over'\n",
      " '65 years and over - With a disability' 'Population 1 year and over'\n",
      " 'Population 1 year and over - Same house'\n",
      " 'Population 1 year and over - Different house in the U.S.'\n",
      " 'Population 1 year and over - Different house in the U.S. - Same county'\n",
      " 'Population 1 year and over - Different house in the U.S. - Different county'\n",
      " 'Population 1 year and over - Different house in the U.S. - Different county - Same state'\n",
      " 'Population 1 year and over - Different house in the U.S. - Different county - Different state'\n",
      " 'Population 1 year and over - Abroad' 'Total population'\n",
      " 'Total population - Native'\n",
      " 'Total population - Native - Born in United States'\n",
      " 'Total population - Native - Born in United States - State of residence'\n",
      " 'Total population - Native - Born in United States - Different state'\n",
      " 'Total population - Native - Born in Puerto Rico, U.S. Island areas, or born abroad to American parent(s)'\n",
      " 'Total population - Foreign born' 'Foreign-born population'\n",
      " 'Foreign-born population - Naturalized U.S. citizen'\n",
      " 'Foreign-born population - Not a U.S. citizen'\n",
      " 'Population born outside the United States' 'Native'\n",
      " 'Native - Entered 2010 or later' 'Native - Entered before 2010'\n",
      " 'Foreign born' 'Foreign born - Entered 2010 or later'\n",
      " 'Foreign born - Entered before 2010'\n",
      " 'Foreign-born population, excluding population born at sea'\n",
      " 'Foreign-born population, excluding population born at sea - Europe'\n",
      " 'Foreign-born population, excluding population born at sea - Asia'\n",
      " 'Foreign-born population, excluding population born at sea - Africa'\n",
      " 'Foreign-born population, excluding population born at sea - Oceania'\n",
      " 'Foreign-born population, excluding population born at sea - Latin America'\n",
      " 'Foreign-born population, excluding population born at sea - Northern America'\n",
      " 'Population 5 years and over'\n",
      " 'Population 5 years and over - English only'\n",
      " 'Population 5 years and over - Language other than English'\n",
      " 'Population 5 years and over - Language other than English - Speak English less than \"very well\"'\n",
      " 'Population 5 years and over - Spanish'\n",
      " 'Population 5 years and over - Spanish - Speak English less than \"very well\"'\n",
      " 'Population 5 years and over - Other Indo-European languages'\n",
      " 'Population 5 years and over - Other Indo-European languages - Speak English less than \"very well\"'\n",
      " 'Population 5 years and over - Asian and Pacific Islander languages'\n",
      " 'Population 5 years and over - Asian and Pacific Islander languages - Speak English less than \"very well\"'\n",
      " 'Population 5 years and over - Other languages'\n",
      " 'Population 5 years and over - Other languages - Speak English less than \"very well\"'\n",
      " 'Total population - American' 'Total population - Arab'\n",
      " 'Total population - Czech' 'Total population - Danish'\n",
      " 'Total population - Dutch' 'Total population - English'\n",
      " 'Total population - French (except Basque)'\n",
      " 'Total population - French Canadian' 'Total population - German'\n",
      " 'Total population - Greek' 'Total population - Hungarian'\n",
      " 'Total population - Irish' 'Total population - Italian'\n",
      " 'Total population - Lithuanian' 'Total population - Norwegian'\n",
      " 'Total population - Polish' 'Total population - Portuguese'\n",
      " 'Total population - Russian' 'Total population - Scotch-Irish'\n",
      " 'Total population - Scottish' 'Total population - Slovak'\n",
      " 'Total population - Subsaharan African' 'Total population - Swedish'\n",
      " 'Total population - Swiss' 'Total population - Ukrainian'\n",
      " 'Total population - Welsh'\n",
      " 'Total population - West Indian (excluding Hispanic origin groups)'\n",
      " 'Total households - With a computer'\n",
      " 'Total households - With a broadband Internet subscription'\n",
      " 'Population 16 years and over'\n",
      " 'Population 16 years and over - In labor force'\n",
      " 'Population 16 years and over - In labor force - Civilian labor force'\n",
      " 'Population 16 years and over - In labor force - Civilian labor force - Employed'\n",
      " 'Population 16 years and over - In labor force - Civilian labor force - Unemployed'\n",
      " 'Population 16 years and over - In labor force - Armed Forces'\n",
      " 'Population 16 years and over - Not in labor force'\n",
      " 'Civilian labor force' 'Civilian labor force - Unemployment Rate'\n",
      " 'Females 16 years and over' 'Females 16 years and over - In labor force'\n",
      " 'Females 16 years and over - In labor force - Civilian labor force'\n",
      " 'Females 16 years and over - In labor force - Civilian labor force - Employed'\n",
      " 'Own children of the householder under 6 years'\n",
      " 'Own children of the householder under 6 years - All parents in family in labor force'\n",
      " 'Own children of the householder 6 to 17 years'\n",
      " 'Own children of the householder 6 to 17 years - All parents in family in labor force'\n",
      " 'Workers 16 years and over'\n",
      " 'Workers 16 years and over - Car, truck, or van -- drove alone'\n",
      " 'Workers 16 years and over - Car, truck, or van -- carpooled'\n",
      " 'Workers 16 years and over - Public transportation (excluding taxicab)'\n",
      " 'Workers 16 years and over - Walked'\n",
      " 'Workers 16 years and over - Other means'\n",
      " 'Workers 16 years and over - Worked at home'\n",
      " 'Mean travel time to work (minutes)'\n",
      " 'Civilian employed population 16 years and over'\n",
      " 'Civilian employed population 16 years and over - Management, business, science, and arts occupations'\n",
      " 'Civilian employed population 16 years and over - Service occupations'\n",
      " 'Civilian employed population 16 years and over - Sales and office occupations'\n",
      " 'Civilian employed population 16 years and over - Natural resources, construction, and maintenance occupations'\n",
      " 'Civilian employed population 16 years and over - Production, transportation, and material moving occupations'\n",
      " 'Civilian employed population 16 years and over - Agriculture, forestry, fishing and hunting, and mining'\n",
      " 'Civilian employed population 16 years and over - Construction'\n",
      " 'Civilian employed population 16 years and over - Manufacturing'\n",
      " 'Civilian employed population 16 years and over - Wholesale trade'\n",
      " 'Civilian employed population 16 years and over - Retail trade'\n",
      " 'Civilian employed population 16 years and over - Transportation and warehousing, and utilities'\n",
      " 'Civilian employed population 16 years and over - Information'\n",
      " 'Civilian employed population 16 years and over - Finance and insurance, and real estate and rental and leasing'\n",
      " 'Civilian employed population 16 years and over - Professional, scientific, and management, and administrative and waste management services'\n",
      " 'Civilian employed population 16 years and over - Educational services, and health care and social assistance'\n",
      " 'Civilian employed population 16 years and over - Arts, entertainment, and recreation, and accommodation and food services'\n",
      " 'Civilian employed population 16 years and over - Other services, except public administration'\n",
      " 'Civilian employed population 16 years and over - Public administration'\n",
      " 'Civilian employed population 16 years and over - Private wage and salary workers'\n",
      " 'Civilian employed population 16 years and over - Government workers'\n",
      " 'Civilian employed population 16 years and over - Self-employed in own not incorporated business workers'\n",
      " 'Civilian employed population 16 years and over - Unpaid family workers'\n",
      " 'ADJUSTED DOLLARS) - Total households'\n",
      " 'ADJUSTED DOLLARS) - Total households - Less than $10,000'\n",
      " 'ADJUSTED DOLLARS) - Total households - $10,000 to $14,999'\n",
      " 'ADJUSTED DOLLARS) - Total households - $15,000 to $24,999'\n",
      " 'ADJUSTED DOLLARS) - Total households - $25,000 to $34,999'\n",
      " 'ADJUSTED DOLLARS) - Total households - $35,000 to $49,999'\n",
      " 'ADJUSTED DOLLARS) - Total households - $50,000 to $74,999'\n",
      " 'ADJUSTED DOLLARS) - Total households - $75,000 to $99,999'\n",
      " 'ADJUSTED DOLLARS) - Total households - $100,000 to $149,999'\n",
      " 'ADJUSTED DOLLARS) - Total households - $150,000 to $199,999'\n",
      " 'ADJUSTED DOLLARS) - Total households - $200,000 or more'\n",
      " 'ADJUSTED DOLLARS) - Total households - Median household income (dollars)'\n",
      " 'ADJUSTED DOLLARS) - Total households - Mean household income (dollars)'\n",
      " 'ADJUSTED DOLLARS) - With earnings'\n",
      " 'ADJUSTED DOLLARS) - With earnings - Mean earnings (dollars)'\n",
      " 'ADJUSTED DOLLARS) - With Social Security'\n",
      " 'ADJUSTED DOLLARS) - With Social Security - Mean Social Security income (dollars)'\n",
      " 'ADJUSTED DOLLARS) - With retirement income'\n",
      " 'ADJUSTED DOLLARS) - With retirement income - Mean retirement income (dollars)'\n",
      " 'ADJUSTED DOLLARS) - With Supplemental Security Income'\n",
      " 'ADJUSTED DOLLARS) - With Supplemental Security Income - Mean Supplemental Security Income (dollars)'\n",
      " 'ADJUSTED DOLLARS) - With cash public assistance income'\n",
      " 'ADJUSTED DOLLARS) - With cash public assistance income - Mean cash public assistance income (dollars)'\n",
      " 'ADJUSTED DOLLARS) - With Food Stamp/SNAP benefits in the past 12 months'\n",
      " 'ADJUSTED DOLLARS) - Families'\n",
      " 'ADJUSTED DOLLARS) - Families - Less than $10,000'\n",
      " 'ADJUSTED DOLLARS) - Families - $10,000 to $14,999'\n",
      " 'ADJUSTED DOLLARS) - Families - $15,000 to $24,999'\n",
      " 'ADJUSTED DOLLARS) - Families - $25,000 to $34,999'\n",
      " 'ADJUSTED DOLLARS) - Families - $35,000 to $49,999'\n",
      " 'ADJUSTED DOLLARS) - Families - $50,000 to $74,999'\n",
      " 'ADJUSTED DOLLARS) - Families - $75,000 to $99,999'\n",
      " 'ADJUSTED DOLLARS) - Families - $100,000 to $149,999'\n",
      " 'ADJUSTED DOLLARS) - Families - $150,000 to $199,999'\n",
      " 'ADJUSTED DOLLARS) - Families - $200,000 or more'\n",
      " 'ADJUSTED DOLLARS) - Families - Median family income (dollars)'\n",
      " 'ADJUSTED DOLLARS) - Families - Mean family income (dollars)'\n",
      " 'ADJUSTED DOLLARS) - Per capita income (dollars)'\n",
      " 'ADJUSTED DOLLARS) - Nonfamily households'\n",
      " 'ADJUSTED DOLLARS) - Nonfamily households - Median nonfamily income (dollars)'\n",
      " 'ADJUSTED DOLLARS) - Nonfamily households - Mean nonfamily income (dollars)'\n",
      " 'ADJUSTED DOLLARS) - Median earnings for workers (dollars)'\n",
      " 'ADJUSTED DOLLARS) - Median earnings for male full-time, year-round workers (dollars)'\n",
      " 'ADJUSTED DOLLARS) - Median earnings for female full-time, year-round workers (dollars)'\n",
      " 'Civilian noninstitutionalized population'\n",
      " 'Civilian noninstitutionalized population - With health insurance coverage'\n",
      " 'Civilian noninstitutionalized population - With health insurance coverage - With private health insurance'\n",
      " 'Civilian noninstitutionalized population - With health insurance coverage - With public coverage'\n",
      " 'Civilian noninstitutionalized population - No health insurance coverage'\n",
      " 'Civilian noninstitutionalized population under 18 years'\n",
      " 'Civilian noninstitutionalized population under 18 years - No health insurance coverage'\n",
      " 'Civilian noninstitutionalized population 18 to 64 years'\n",
      " 'Civilian noninstitutionalized population 18 to 64 years - In labor force:'\n",
      " 'Civilian noninstitutionalized population 18 to 64 years - In labor force: - Employed:'\n",
      " 'Civilian noninstitutionalized population 18 to 64 years - In labor force: - Employed: - With health insurance coverage'\n",
      " 'Civilian noninstitutionalized population 18 to 64 years - In labor force: - Employed: - With health insurance coverage - With private health insurance'\n",
      " 'Civilian noninstitutionalized population 18 to 64 years - In labor force: - Employed: - With health insurance coverage - With public coverage'\n",
      " 'Civilian noninstitutionalized population 18 to 64 years - In labor force: - Employed: - No health insurance coverage'\n",
      " 'Civilian noninstitutionalized population 18 to 64 years - In labor force: - Unemployed:'\n",
      " 'Civilian noninstitutionalized population 18 to 64 years - In labor force: - Unemployed: - With health insurance coverage'\n",
      " 'Civilian noninstitutionalized population 18 to 64 years - In labor force: - Unemployed: - With health insurance coverage - With private health insurance'\n",
      " 'Civilian noninstitutionalized population 18 to 64 years - In labor force: - Unemployed: - With health insurance coverage - With public coverage'\n",
      " 'Civilian noninstitutionalized population 18 to 64 years - In labor force: - Unemployed: - No health insurance coverage'\n",
      " 'Civilian noninstitutionalized population 18 to 64 years - Not in labor force:'\n",
      " 'Civilian noninstitutionalized population 18 to 64 years - Not in labor force: - With health insurance coverage'\n",
      " 'Civilian noninstitutionalized population 18 to 64 years - Not in labor force: - With health insurance coverage - With private health insurance'\n",
      " 'Civilian noninstitutionalized population 18 to 64 years - Not in labor force: - With health insurance coverage - With public coverage'\n",
      " 'Civilian noninstitutionalized population 18 to 64 years - Not in labor force: - No health insurance coverage'\n",
      " 'All families'\n",
      " 'All families - With related children of the householder under 18 years'\n",
      " 'All families - With related children of the householder under 18 years - With related children of the householder under 5 years only'\n",
      " 'Married couple families'\n",
      " 'Married couple families - With related children of the householder under 18 years'\n",
      " 'Married couple families - With related children of the householder under 18 years - With related children of the householder under 5 years only'\n",
      " 'Families with female householder, no husband present'\n",
      " 'Families with female householder, no husband present - With related children of the householder under 18 years'\n",
      " 'Families with female householder, no husband present - With related children of the householder under 18 years - With related children of the householder under 5 years only'\n",
      " 'All people' 'All people - Under 18 years'\n",
      " 'All people - Under 18 years - Related children of the householder under 18 years'\n",
      " 'All people - Under 18 years - Related children of the householder under 18 years - Related children of the householder under 5 years'\n",
      " 'All people - Under 18 years - Related children of the householder under 18 years - Related children of the householder 5 to 17 years'\n",
      " 'All people - 18 years and over'\n",
      " 'All people - 18 years and over - 18 to 64 years'\n",
      " 'All people - 18 years and over - 65 years and over' 'People in families'\n",
      " 'Unrelated individuals 15 years and over' 'Total housing units'\n",
      " 'Total housing units - Occupied housing units'\n",
      " 'Total housing units - Vacant housing units' 'Homeowner vacancy rate'\n",
      " 'Rental vacancy rate' 'Total housing units - 1-unit, detached'\n",
      " 'Total housing units - 1-unit, attached' 'Total housing units - 2 units'\n",
      " 'Total housing units - 3 or 4 units' 'Total housing units - 5 to 9 units'\n",
      " 'Total housing units - 10 to 19 units'\n",
      " 'Total housing units - 20 or more units'\n",
      " 'Total housing units - Mobile home'\n",
      " 'Total housing units - Boat, RV, van, etc.'\n",
      " 'Total housing units - Built 2014 or later'\n",
      " 'Total housing units - Built 2010 to 2013'\n",
      " 'Total housing units - Built 2000 to 2009'\n",
      " 'Total housing units - Built 1990 to 1999'\n",
      " 'Total housing units - Built 1980 to 1989'\n",
      " 'Total housing units - Built 1970 to 1979'\n",
      " 'Total housing units - Built 1960 to 1969'\n",
      " 'Total housing units - Built 1950 to 1959'\n",
      " 'Total housing units - Built 1940 to 1949'\n",
      " 'Total housing units - Built 1939 or earlier'\n",
      " 'Total housing units - 1 room' 'Total housing units - 2 rooms'\n",
      " 'Total housing units - 3 rooms' 'Total housing units - 4 rooms'\n",
      " 'Total housing units - 5 rooms' 'Total housing units - 6 rooms'\n",
      " 'Total housing units - 7 rooms' 'Total housing units - 8 rooms'\n",
      " 'Total housing units - 9 rooms or more'\n",
      " 'Total housing units - Median rooms' 'Total housing units - No bedroom'\n",
      " 'Total housing units - 1 bedroom' 'Total housing units - 2 bedrooms'\n",
      " 'Total housing units - 3 bedrooms' 'Total housing units - 4 bedrooms'\n",
      " 'Total housing units - 5 or more bedrooms' 'Occupied housing units'\n",
      " 'Occupied housing units - Owner-occupied'\n",
      " 'Occupied housing units - Renter-occupied'\n",
      " 'Average household size of owner-occupied unit'\n",
      " 'Average household size of renter-occupied unit'\n",
      " 'Occupied housing units - Moved in 2015 or later'\n",
      " 'Occupied housing units - Moved in 2010 to 2014'\n",
      " 'Occupied housing units - Moved in 2000 to 2009'\n",
      " 'Occupied housing units - Moved in 1990 to 1999'\n",
      " 'Occupied housing units - Moved in 1980 to 1989'\n",
      " 'Occupied housing units - Moved in 1979 and earlier'\n",
      " 'Occupied housing units - No vehicles available'\n",
      " 'Occupied housing units - 1 vehicle available'\n",
      " 'Occupied housing units - 2 vehicles available'\n",
      " 'Occupied housing units - 3 or more vehicles available'\n",
      " 'Occupied housing units - Utility gas'\n",
      " 'Occupied housing units - Bottled, tank, or LP gas'\n",
      " 'Occupied housing units - Electricity'\n",
      " 'Occupied housing units - Fuel oil, kerosene, etc.'\n",
      " 'Occupied housing units - Coal or coke' 'Occupied housing units - Wood'\n",
      " 'Occupied housing units - Solar energy'\n",
      " 'Occupied housing units - Other fuel'\n",
      " 'Occupied housing units - No fuel used'\n",
      " 'Occupied housing units - Lacking complete plumbing facilities'\n",
      " 'Occupied housing units - Lacking complete kitchen facilities'\n",
      " 'Occupied housing units - No telephone service available'\n",
      " 'Occupied housing units - 1.00 or less'\n",
      " 'Occupied housing units - 1.01 to 1.50'\n",
      " 'Occupied housing units - 1.51 or more' 'Owner-occupied units'\n",
      " 'Owner-occupied units - Less than $50,000'\n",
      " 'Owner-occupied units - $50,000 to $99,999'\n",
      " 'Owner-occupied units - $100,000 to $149,999'\n",
      " 'Owner-occupied units - $150,000 to $199,999'\n",
      " 'Owner-occupied units - $200,000 to $299,999'\n",
      " 'Owner-occupied units - $300,000 to $499,999'\n",
      " 'Owner-occupied units - $500,000 to $999,999'\n",
      " 'Owner-occupied units - $1,000,000 or more'\n",
      " 'Owner-occupied units - Median (dollars)'\n",
      " 'Owner-occupied units - Housing units with a mortgage'\n",
      " 'Owner-occupied units - Housing units without a mortgage'\n",
      " 'Housing units with a mortgage'\n",
      " 'Housing units with a mortgage - Less than $500'\n",
      " 'Housing units with a mortgage - $500 to $999'\n",
      " 'Housing units with a mortgage - $1,000 to $1,499'\n",
      " 'Housing units with a mortgage - $1,500 to $1,999'\n",
      " 'Housing units with a mortgage - $2,000 to $2,499'\n",
      " 'Housing units with a mortgage - $2,500 to $2,999'\n",
      " 'Housing units with a mortgage - $3,000 or more'\n",
      " 'Housing units with a mortgage - Median (dollars)'\n",
      " 'Housing units without a mortgage'\n",
      " 'Housing units without a mortgage - Less than $250'\n",
      " 'Housing units without a mortgage - $250 to $399'\n",
      " 'Housing units without a mortgage - $400 to $599'\n",
      " 'Housing units without a mortgage - $600 to $799'\n",
      " 'Housing units without a mortgage - $800 to $999'\n",
      " 'Housing units without a mortgage - $1,000 or more'\n",
      " 'Housing units without a mortgage - Median (dollars)'\n",
      " 'Housing units with a mortgage (excluding units where SMOCAPI cannot be computed)'\n",
      " 'Housing units with a mortgage (excluding units where SMOCAPI cannot be computed) - Less than 20.0 percent'\n",
      " 'Housing units with a mortgage (excluding units where SMOCAPI cannot be computed) - 20.0 to 24.9 percent'\n",
      " 'Housing units with a mortgage (excluding units where SMOCAPI cannot be computed) - 25.0 to 29.9 percent'\n",
      " 'Housing units with a mortgage (excluding units where SMOCAPI cannot be computed) - 30.0 to 34.9 percent'\n",
      " 'Housing units with a mortgage (excluding units where SMOCAPI cannot be computed) - 35.0 percent or more'\n",
      " 'Not computed'\n",
      " 'Housing unit without a mortgage (excluding units where SMOCAPI cannot be computed)'\n",
      " 'Housing unit without a mortgage (excluding units where SMOCAPI cannot be computed) - Less than 10.0 percent'\n",
      " 'Housing unit without a mortgage (excluding units where SMOCAPI cannot be computed) - 10.0 to 14.9 percent'\n",
      " 'Housing unit without a mortgage (excluding units where SMOCAPI cannot be computed) - 15.0 to 19.9 percent'\n",
      " 'Housing unit without a mortgage (excluding units where SMOCAPI cannot be computed) - 20.0 to 24.9 percent'\n",
      " 'Housing unit without a mortgage (excluding units where SMOCAPI cannot be computed) - 25.0 to 29.9 percent'\n",
      " 'Housing unit without a mortgage (excluding units where SMOCAPI cannot be computed) - 30.0 to 34.9 percent'\n",
      " 'Housing unit without a mortgage (excluding units where SMOCAPI cannot be computed) - 35.0 percent or more'\n",
      " 'Occupied units paying rent'\n",
      " 'Occupied units paying rent - Less than $500'\n",
      " 'Occupied units paying rent - $500 to $999'\n",
      " 'Occupied units paying rent - $1,000 to $1,499'\n",
      " 'Occupied units paying rent - $1,500 to $1,999'\n",
      " 'Occupied units paying rent - $2,000 to $2,499'\n",
      " 'Occupied units paying rent - $2,500 to $2,999'\n",
      " 'Occupied units paying rent - $3,000 or more'\n",
      " 'Occupied units paying rent - Median (dollars)' 'No rent paid'\n",
      " 'Occupied units paying rent (excluding units where GRAPI cannot be computed)'\n",
      " 'Occupied units paying rent (excluding units where GRAPI cannot be computed) - Less than 15.0 percent'\n",
      " 'Occupied units paying rent (excluding units where GRAPI cannot be computed) - 15.0 to 19.9 percent'\n",
      " 'Occupied units paying rent (excluding units where GRAPI cannot be computed) - 20.0 to 24.9 percent'\n",
      " 'Occupied units paying rent (excluding units where GRAPI cannot be computed) - 25.0 to 29.9 percent'\n",
      " 'Occupied units paying rent (excluding units where GRAPI cannot be computed) - 30.0 to 34.9 percent'\n",
      " 'Occupied units paying rent (excluding units where GRAPI cannot be computed) - 35.0 percent or more'\n",
      " 'Total population - Male' 'Total population - Female' 'Under 5 years'\n",
      " '5 to 9 years' '10 to 14 years' '15 to 19 years' '20 to 24 years'\n",
      " '25 to 34 years' '35 to 44 years' '45 to 54 years' '55 to 59 years'\n",
      " '60 to 64 years' '65 to 74 years' '75 to 84 years' '85 years and over'\n",
      " 'Median age (years)' '18 years and over' '21 years and over'\n",
      " '62 years and over' '18 years and over - Male'\n",
      " '18 years and over - Female' '65 years and over - Male'\n",
      " '65 years and over - Female' 'Total population - One race'\n",
      " 'Total population - Two or more races' 'One race' 'One race - White'\n",
      " 'One race - Black or African American'\n",
      " 'One race - American Indian and Alaska Native'\n",
      " 'One race - American Indian and Alaska Native - Cherokee tribal grouping'\n",
      " 'One race - American Indian and Alaska Native - Chippewa tribal grouping'\n",
      " 'One race - American Indian and Alaska Native - Navajo tribal grouping'\n",
      " 'One race - American Indian and Alaska Native - Sioux tribal grouping'\n",
      " 'One race - Asian' 'One race - Asian - Asian Indian'\n",
      " 'One race - Asian - Chinese' 'One race - Asian - Filipino'\n",
      " 'One race - Asian - Japanese' 'One race - Asian - Korean'\n",
      " 'One race - Asian - Vietnamese' 'One race - Asian - Other Asian'\n",
      " 'One race - Native Hawaiian and Other Pacific Islander'\n",
      " 'One race - Native Hawaiian and Other Pacific Islander - Native Hawaiian'\n",
      " 'One race - Native Hawaiian and Other Pacific Islander - Guamanian or Chamorro'\n",
      " 'One race - Native Hawaiian and Other Pacific Islander - Samoan'\n",
      " 'One race - Native Hawaiian and Other Pacific Islander - Other Pacific Islander'\n",
      " 'One race - Some other race' 'Two or more races'\n",
      " 'Two or more races - White and Black or African American'\n",
      " 'Two or more races - White and American Indian and Alaska Native'\n",
      " 'Two or more races - White and Asian'\n",
      " 'Two or more races - Black or African American and American Indian and Alaska Native'\n",
      " 'Race alone or in combination with one or more other races - Total population'\n",
      " 'Race alone or in combination with one or more other races - Total population - White'\n",
      " 'Race alone or in combination with one or more other races - Total population - Black or African American'\n",
      " 'Race alone or in combination with one or more other races - Total population - American Indian and Alaska Native'\n",
      " 'Race alone or in combination with one or more other races - Total population - Asian'\n",
      " 'Race alone or in combination with one or more other races - Total population - Native Hawaiian and Other Pacific Islander'\n",
      " 'Race alone or in combination with one or more other races - Total population - Some other race'\n",
      " 'Total population - Hispanic or Latino (of any race)'\n",
      " 'Total population - Hispanic or Latino (of any race) - Mexican'\n",
      " 'Total population - Hispanic or Latino (of any race) - Puerto Rican'\n",
      " 'Total population - Hispanic or Latino (of any race) - Cuban'\n",
      " 'Total population - Hispanic or Latino (of any race) - Other Hispanic or Latino'\n",
      " 'Total population - Not Hispanic or Latino'\n",
      " 'Total population - Not Hispanic or Latino - White alone'\n",
      " 'Total population - Not Hispanic or Latino - Black or African American alone'\n",
      " 'Total population - Not Hispanic or Latino - American Indian and Alaska Native alone'\n",
      " 'Total population - Not Hispanic or Latino - Asian alone'\n",
      " 'Total population - Not Hispanic or Latino - Native Hawaiian and Other Pacific Islander alone'\n",
      " 'Total population - Not Hispanic or Latino - Some other race alone'\n",
      " 'Total population - Not Hispanic or Latino - Two or more races'\n",
      " 'Total population - Not Hispanic or Latino - Two or more races - Two races including Some other race'\n",
      " 'Total population - Not Hispanic or Latino - Two or more races - Two races excluding Some other race, and Three or more races'\n",
      " 'Citizen, 18 and over population'\n",
      " 'Citizen, 18 and over population - Male'\n",
      " 'Citizen, 18 and over population - Female']\n"
     ]
    },
    {
     "ename": "AttributeError",
     "evalue": "'DataFrame' object has no attribute 'cat3'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mAttributeError\u001b[0m                            Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-13-084143334f03>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m      3\u001b[0m \u001b[0mprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mmeta\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcat1\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0munique\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      4\u001b[0m \u001b[0mprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mmeta\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcat2\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0munique\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 5\u001b[0;31m \u001b[0mprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mmeta\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcat3\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0munique\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m~/anaconda/envs/city-dash/lib/python3.6/site-packages/pandas/core/generic.py\u001b[0m in \u001b[0;36m__getattr__\u001b[0;34m(self, name)\u001b[0m\n\u001b[1;32m   3612\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mname\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_info_axis\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3613\u001b[0m                 \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mname\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 3614\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mobject\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__getattribute__\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mname\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   3615\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3616\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m__setattr__\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mname\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mAttributeError\u001b[0m: 'DataFrame' object has no attribute 'cat3'"
     ]
    }
   ],
   "source": [
    "print(meta.type.unique())\n",
    "print(meta.cat0.unique())\n",
    "print(meta.cat1.unique())\n",
    "print(meta.cat2.unique())\n",
    "print(meta.cat3.unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "meta = meta[meta.type == 'Estimate']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "def rename_cols(df, num):\n",
    "    cols = df.columns[~df.columns.str.contains('GEO')]\n",
    "    df.rename(columns = dict(zip(cols, num + cols )), inplace=True)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "df02 = rename_cols(df02,'02')\n",
    "df03 = rename_cols(df03,'03')\n",
    "df04 = rename_cols(df04,'04')\n",
    "df05 = rename_cols(df05,'05')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(57163, 8)\n",
      "(57163, 163)\n",
      "(57163, 303)\n",
      "(57163, 449)\n",
      "(57163, 536)\n"
     ]
    }
   ],
   "source": [
    "# merge data files with zipcodes\n",
    "print(zipcode.shape)\n",
    "df = pd.merge(left=zipcode, right=df02, right_on='GEO.id2', left_on='zipcode', how = 'left')\n",
    "print(df.shape)\n",
    "df = pd.merge(left=df, right=df03, right_on='GEO.id2', left_on='zipcode', how = 'left')\n",
    "print(df.shape)\n",
    "df = pd.merge(left=df, right=df04, right_on='GEO.id2', left_on='zipcode', how = 'left')\n",
    "print(df.shape)\n",
    "df = pd.merge(left=df, right=df05, right_on='GEO.id2', left_on='zipcode', how = 'left')\n",
    "print(df.shape)"
   ]
  },
  {
   "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>Unnamed: 0</th>\n",
       "      <th>zipcode</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>county</th>\n",
       "      <th>fips</th>\n",
       "      <th>GEO.id_x</th>\n",
       "      <th>GEO.id2_x</th>\n",
       "      <th>...</th>\n",
       "      <th>05HC01_VC97</th>\n",
       "      <th>05HC01_VC98</th>\n",
       "      <th>05HC01_VC99</th>\n",
       "      <th>05HC01_VC100</th>\n",
       "      <th>05HC01_VC101</th>\n",
       "      <th>05HC01_VC102</th>\n",
       "      <th>05HC01_VC104</th>\n",
       "      <th>05HC01_VC108</th>\n",
       "      <th>05HC01_VC109</th>\n",
       "      <th>05HC01_VC110</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>501</td>\n",
       "      <td>40.922326</td>\n",
       "      <td>-72.637078</td>\n",
       "      <td>Holtsville</td>\n",
       "      <td>NY</td>\n",
       "      <td>Suffolk</td>\n",
       "      <td>36103.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>544</td>\n",
       "      <td>40.922326</td>\n",
       "      <td>-72.637078</td>\n",
       "      <td>Holtsville</td>\n",
       "      <td>NY</td>\n",
       "      <td>Suffolk</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>601</td>\n",
       "      <td>18.165273</td>\n",
       "      <td>-66.722583</td>\n",
       "      <td>Adjuntas</td>\n",
       "      <td>PR</td>\n",
       "      <td>Adjuntas</td>\n",
       "      <td>72001.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7290.0</td>\n",
       "      <td>13646.0</td>\n",
       "      <td>6573.0</td>\n",
       "      <td>7073.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>601</td>\n",
       "      <td>18.165273</td>\n",
       "      <td>-66.722583</td>\n",
       "      <td>Adjuntas</td>\n",
       "      <td>PR</td>\n",
       "      <td>Adjuntas</td>\n",
       "      <td>72113.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7290.0</td>\n",
       "      <td>13646.0</td>\n",
       "      <td>6573.0</td>\n",
       "      <td>7073.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>602</td>\n",
       "      <td>18.393103</td>\n",
       "      <td>-67.180953</td>\n",
       "      <td>Aguada</td>\n",
       "      <td>PR</td>\n",
       "      <td>Aguada</td>\n",
       "      <td>72003.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>59.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>960.0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>928.0</td>\n",
       "      <td>17312.0</td>\n",
       "      <td>31048.0</td>\n",
       "      <td>15156.0</td>\n",
       "      <td>15892.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 536 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  zipcode   latitude  longitude        city state    county  \\\n",
       "0           0      501  40.922326 -72.637078  Holtsville    NY   Suffolk   \n",
       "1           1      544  40.922326 -72.637078  Holtsville    NY   Suffolk   \n",
       "2           2      601  18.165273 -66.722583    Adjuntas    PR  Adjuntas   \n",
       "3           3      601  18.165273 -66.722583    Adjuntas    PR  Adjuntas   \n",
       "4           4      602  18.393103 -67.180953      Aguada    PR    Aguada   \n",
       "\n",
       "      fips GEO.id_x  GEO.id2_x      ...      05HC01_VC97  05HC01_VC98  \\\n",
       "0  36103.0      NaN        NaN      ...              NaN          NaN   \n",
       "1      NaN      NaN        NaN      ...              NaN          NaN   \n",
       "2  72001.0      NaN        NaN      ...              0.0          0.0   \n",
       "3  72113.0      NaN        NaN      ...              0.0          0.0   \n",
       "4  72003.0      NaN        NaN      ...             59.0          0.0   \n",
       "\n",
       "   05HC01_VC99  05HC01_VC100  05HC01_VC101  05HC01_VC102  05HC01_VC104  \\\n",
       "0          NaN           NaN           NaN           NaN           NaN   \n",
       "1          NaN           NaN           NaN           NaN           NaN   \n",
       "2          0.0           0.0           0.0           0.0        7290.0   \n",
       "3          0.0           0.0           0.0           0.0        7290.0   \n",
       "4         31.0         960.0          32.0         928.0       17312.0   \n",
       "\n",
       "   05HC01_VC108  05HC01_VC109  05HC01_VC110  \n",
       "0           NaN           NaN           NaN  \n",
       "1           NaN           NaN           NaN  \n",
       "2       13646.0        6573.0        7073.0  \n",
       "3       13646.0        6573.0        7073.0  \n",
       "4       31048.0       15156.0       15892.0  \n",
       "\n",
       "[5 rows x 536 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>v</th>\n",
       "      <th>detail</th>\n",
       "      <th>type</th>\n",
       "      <th>cat0</th>\n",
       "      <th>cat1</th>\n",
       "      <th>cat2</th>\n",
       "      <th>vname</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>HC01_VC03</td>\n",
       "      <td>Estimate; HOUSEHOLDS BY TYPE - Total households</td>\n",
       "      <td>Estimate</td>\n",
       "      <td>Social</td>\n",
       "      <td>HOUSEHOLDS BY TYPE</td>\n",
       "      <td>Total households</td>\n",
       "      <td>02HC01_VC03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>HC01_VC04</td>\n",
       "      <td>Estimate; HOUSEHOLDS BY TYPE - Total household...</td>\n",
       "      <td>Estimate</td>\n",
       "      <td>Social</td>\n",
       "      <td>HOUSEHOLDS BY TYPE</td>\n",
       "      <td>Total households - Family households (families)</td>\n",
       "      <td>02HC01_VC04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>HC01_VC05</td>\n",
       "      <td>Estimate; HOUSEHOLDS BY TYPE - Total household...</td>\n",
       "      <td>Estimate</td>\n",
       "      <td>Social</td>\n",
       "      <td>HOUSEHOLDS BY TYPE</td>\n",
       "      <td>Total households - Family households (families...</td>\n",
       "      <td>02HC01_VC05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>HC01_VC06</td>\n",
       "      <td>Estimate; HOUSEHOLDS BY TYPE - Total household...</td>\n",
       "      <td>Estimate</td>\n",
       "      <td>Social</td>\n",
       "      <td>HOUSEHOLDS BY TYPE</td>\n",
       "      <td>Total households - Family households (families...</td>\n",
       "      <td>02HC01_VC06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>HC01_VC07</td>\n",
       "      <td>Estimate; HOUSEHOLDS BY TYPE - Total household...</td>\n",
       "      <td>Estimate</td>\n",
       "      <td>Social</td>\n",
       "      <td>HOUSEHOLDS BY TYPE</td>\n",
       "      <td>Total households - Family households (families...</td>\n",
       "      <td>02HC01_VC07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            v                                             detail      type  \\\n",
       "0   HC01_VC03    Estimate; HOUSEHOLDS BY TYPE - Total households  Estimate   \n",
       "4   HC01_VC04  Estimate; HOUSEHOLDS BY TYPE - Total household...  Estimate   \n",
       "8   HC01_VC05  Estimate; HOUSEHOLDS BY TYPE - Total household...  Estimate   \n",
       "12  HC01_VC06  Estimate; HOUSEHOLDS BY TYPE - Total household...  Estimate   \n",
       "16  HC01_VC07  Estimate; HOUSEHOLDS BY TYPE - Total household...  Estimate   \n",
       "\n",
       "      cat0                cat1  \\\n",
       "0   Social  HOUSEHOLDS BY TYPE   \n",
       "4   Social  HOUSEHOLDS BY TYPE   \n",
       "8   Social  HOUSEHOLDS BY TYPE   \n",
       "12  Social  HOUSEHOLDS BY TYPE   \n",
       "16  Social  HOUSEHOLDS BY TYPE   \n",
       "\n",
       "                                                 cat2        vname  \n",
       "0                                    Total households  02HC01_VC03  \n",
       "4     Total households - Family households (families)  02HC01_VC04  \n",
       "8   Total households - Family households (families...  02HC01_VC05  \n",
       "12  Total households - Family households (families...  02HC01_VC06  \n",
       "16  Total households - Family households (families...  02HC01_VC07  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "meta.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# save df and meta to csv\n",
    "df.to_csv(\"./data/df.csv\")\n",
    "meta.to_csv(\"./data/meta.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Cleaning data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# function to group data\n",
    "def group_data(df, geo = 'country'):\n",
    "    if geo == 'country':\n",
    "        df\n",
    "    elif geo == 'state':\n",
    "        df = df.groupby(\"state\").sum()\n",
    "    elif geo == 'county':\n",
    "        df = df.groupby([\"state\", \"county\"]).sum()\n",
    "    elif geo == 'city':\n",
    "        df = df.groupby([\"state\", \"county\", \"city\"]).sum()\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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></th>\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>zipcode</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>fips</th>\n",
       "      <th>GEO.id2_x</th>\n",
       "      <th>02HC01_VC03</th>\n",
       "      <th>02HC01_VC04</th>\n",
       "      <th>02HC01_VC05</th>\n",
       "      <th>02HC01_VC06</th>\n",
       "      <th>...</th>\n",
       "      <th>05HC01_VC97</th>\n",
       "      <th>05HC01_VC98</th>\n",
       "      <th>05HC01_VC99</th>\n",
       "      <th>05HC01_VC100</th>\n",
       "      <th>05HC01_VC101</th>\n",
       "      <th>05HC01_VC102</th>\n",
       "      <th>05HC01_VC104</th>\n",
       "      <th>05HC01_VC108</th>\n",
       "      <th>05HC01_VC109</th>\n",
       "      <th>05HC01_VC110</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>state</th>\n",
       "      <th>county</th>\n",
       "      <th>city</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">AK</th>\n",
       "      <th rowspan=\"5\" valign=\"top\">Aleutians East</th>\n",
       "      <th>Akutan</th>\n",
       "      <td>56926</td>\n",
       "      <td>99553</td>\n",
       "      <td>55.430594</td>\n",
       "      <td>-162.558130</td>\n",
       "      <td>2013.0</td>\n",
       "      <td>99553.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>...</td>\n",
       "      <td>297.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>367.0</td>\n",
       "      <td>260.0</td>\n",
       "      <td>107.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cold Bay</th>\n",
       "      <td>56941</td>\n",
       "      <td>99571</td>\n",
       "      <td>55.650875</td>\n",
       "      <td>-160.654522</td>\n",
       "      <td>2013.0</td>\n",
       "      <td>99571.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>106.0</td>\n",
       "      <td>102.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>46.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>False Pass</th>\n",
       "      <td>56952</td>\n",
       "      <td>99583</td>\n",
       "      <td>55.430594</td>\n",
       "      <td>-162.558130</td>\n",
       "      <td>2013.0</td>\n",
       "      <td>99583.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>...</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>47.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>16.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>King Cove</th>\n",
       "      <td>56972</td>\n",
       "      <td>99612</td>\n",
       "      <td>55.430594</td>\n",
       "      <td>-162.558130</td>\n",
       "      <td>2013.0</td>\n",
       "      <td>99612.0</td>\n",
       "      <td>246.0</td>\n",
       "      <td>169.0</td>\n",
       "      <td>77.0</td>\n",
       "      <td>113.0</td>\n",
       "      <td>...</td>\n",
       "      <td>379.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>295.0</td>\n",
       "      <td>671.0</td>\n",
       "      <td>439.0</td>\n",
       "      <td>232.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sand Point</th>\n",
       "      <td>57014</td>\n",
       "      <td>99661</td>\n",
       "      <td>55.350356</td>\n",
       "      <td>-160.485506</td>\n",
       "      <td>2013.0</td>\n",
       "      <td>99661.0</td>\n",
       "      <td>405.0</td>\n",
       "      <td>263.0</td>\n",
       "      <td>140.0</td>\n",
       "      <td>138.0</td>\n",
       "      <td>...</td>\n",
       "      <td>292.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>86.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>86.0</td>\n",
       "      <td>472.0</td>\n",
       "      <td>782.0</td>\n",
       "      <td>491.0</td>\n",
       "      <td>291.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 463 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 Unnamed: 0  zipcode   latitude   longitude  \\\n",
       "state county         city                                                     \n",
       "AK    Aleutians East Akutan           56926    99553  55.430594 -162.558130   \n",
       "                     Cold Bay         56941    99571  55.650875 -160.654522   \n",
       "                     False Pass       56952    99583  55.430594 -162.558130   \n",
       "                     King Cove        56972    99612  55.430594 -162.558130   \n",
       "                     Sand Point       57014    99661  55.350356 -160.485506   \n",
       "\n",
       "                                   fips  GEO.id2_x  02HC01_VC03  02HC01_VC04  \\\n",
       "state county         city                                                      \n",
       "AK    Aleutians East Akutan      2013.0    99553.0         54.0         27.0   \n",
       "                     Cold Bay    2013.0    99571.0         52.0         25.0   \n",
       "                     False Pass  2013.0    99583.0         31.0         15.0   \n",
       "                     King Cove   2013.0    99612.0        246.0        169.0   \n",
       "                     Sand Point  2013.0    99661.0        405.0        263.0   \n",
       "\n",
       "                                 02HC01_VC05  02HC01_VC06      ...       \\\n",
       "state county         city                                      ...        \n",
       "AK    Aleutians East Akutan              8.0         18.0      ...        \n",
       "                     Cold Bay            4.0         23.0      ...        \n",
       "                     False Pass          6.0          6.0      ...        \n",
       "                     King Cove          77.0        113.0      ...        \n",
       "                     Sand Point        140.0        138.0      ...        \n",
       "\n",
       "                                 05HC01_VC97  05HC01_VC98  05HC01_VC99  \\\n",
       "state county         city                                                \n",
       "AK    Aleutians East Akutan            297.0         22.0          0.0   \n",
       "                     Cold Bay            0.0          0.0          0.0   \n",
       "                     False Pass          4.0          0.0          0.0   \n",
       "                     King Cove         379.0          4.0          0.0   \n",
       "                     Sand Point        292.0         10.0          0.0   \n",
       "\n",
       "                                 05HC01_VC100  05HC01_VC101  05HC01_VC102  \\\n",
       "state county         city                                                   \n",
       "AK    Aleutians East Akutan              13.0           0.0          13.0   \n",
       "                     Cold Bay             3.0           0.0           3.0   \n",
       "                     False Pass           0.0           0.0           0.0   \n",
       "                     King Cove           49.0           0.0          49.0   \n",
       "                     Sand Point          86.0           0.0          86.0   \n",
       "\n",
       "                                 05HC01_VC104  05HC01_VC108  05HC01_VC109  \\\n",
       "state county         city                                                   \n",
       "AK    Aleutians East Akutan              62.0         367.0         260.0   \n",
       "                     Cold Bay           106.0         102.0          56.0   \n",
       "                     False Pass          47.0          44.0          28.0   \n",
       "                     King Cove          295.0         671.0         439.0   \n",
       "                     Sand Point         472.0         782.0         491.0   \n",
       "\n",
       "                                 05HC01_VC110  \n",
       "state county         city                      \n",
       "AK    Aleutians East Akutan             107.0  \n",
       "                     Cold Bay            46.0  \n",
       "                     False Pass          16.0  \n",
       "                     King Cove          232.0  \n",
       "                     Sand Point         291.0  \n",
       "\n",
       "[5 rows x 463 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped_data = group_data(df, geo = 'city')\n",
    "grouped_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "meta['level1'] = meta.cat2.str.split('-',1).str[0].str.strip()\n",
    "meta['level2'] = meta.cat2.str.split('-',1).str[1].str.strip()\n",
    "meta['level3'] = meta.level2.str.split('-',1).str[1].str.strip()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>v</th>\n",
       "      <th>detail</th>\n",
       "      <th>type</th>\n",
       "      <th>cat0</th>\n",
       "      <th>cat1</th>\n",
       "      <th>cat2</th>\n",
       "      <th>vname</th>\n",
       "      <th>level1</th>\n",
       "      <th>level2</th>\n",
       "      <th>level3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>HC01_VC03</td>\n",
       "      <td>Estimate; HOUSEHOLDS BY TYPE - Total households</td>\n",
       "      <td>Estimate</td>\n",
       "      <td>Social</td>\n",
       "      <td>HOUSEHOLDS BY TYPE</td>\n",
       "      <td>Total households</td>\n",
       "      <td>02HC01_VC03</td>\n",
       "      <td>Total households</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>HC01_VC04</td>\n",
       "      <td>Estimate; HOUSEHOLDS BY TYPE - Total household...</td>\n",
       "      <td>Estimate</td>\n",
       "      <td>Social</td>\n",
       "      <td>HOUSEHOLDS BY TYPE</td>\n",
       "      <td>Total households - Family households (families)</td>\n",
       "      <td>02HC01_VC04</td>\n",
       "      <td>Total households</td>\n",
       "      <td>Family households (families)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>HC01_VC05</td>\n",
       "      <td>Estimate; HOUSEHOLDS BY TYPE - Total household...</td>\n",
       "      <td>Estimate</td>\n",
       "      <td>Social</td>\n",
       "      <td>HOUSEHOLDS BY TYPE</td>\n",
       "      <td>Total households - Family households (families...</td>\n",
       "      <td>02HC01_VC05</td>\n",
       "      <td>Total households</td>\n",
       "      <td>Family households (families) - With own childr...</td>\n",
       "      <td>With own children of the householder under 18 ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>HC01_VC06</td>\n",
       "      <td>Estimate; HOUSEHOLDS BY TYPE - Total household...</td>\n",
       "      <td>Estimate</td>\n",
       "      <td>Social</td>\n",
       "      <td>HOUSEHOLDS BY TYPE</td>\n",
       "      <td>Total households - Family households (families...</td>\n",
       "      <td>02HC01_VC06</td>\n",
       "      <td>Total households</td>\n",
       "      <td>Family households (families) - Married-couple ...</td>\n",
       "      <td>Married-couple family</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>HC01_VC07</td>\n",
       "      <td>Estimate; HOUSEHOLDS BY TYPE - Total household...</td>\n",
       "      <td>Estimate</td>\n",
       "      <td>Social</td>\n",
       "      <td>HOUSEHOLDS BY TYPE</td>\n",
       "      <td>Total households - Family households (families...</td>\n",
       "      <td>02HC01_VC07</td>\n",
       "      <td>Total households</td>\n",
       "      <td>Family households (families) - Married-couple ...</td>\n",
       "      <td>Married-couple family - With own children of t...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            v                                             detail      type  \\\n",
       "0   HC01_VC03    Estimate; HOUSEHOLDS BY TYPE - Total households  Estimate   \n",
       "4   HC01_VC04  Estimate; HOUSEHOLDS BY TYPE - Total household...  Estimate   \n",
       "8   HC01_VC05  Estimate; HOUSEHOLDS BY TYPE - Total household...  Estimate   \n",
       "12  HC01_VC06  Estimate; HOUSEHOLDS BY TYPE - Total household...  Estimate   \n",
       "16  HC01_VC07  Estimate; HOUSEHOLDS BY TYPE - Total household...  Estimate   \n",
       "\n",
       "      cat0                cat1  \\\n",
       "0   Social  HOUSEHOLDS BY TYPE   \n",
       "4   Social  HOUSEHOLDS BY TYPE   \n",
       "8   Social  HOUSEHOLDS BY TYPE   \n",
       "12  Social  HOUSEHOLDS BY TYPE   \n",
       "16  Social  HOUSEHOLDS BY TYPE   \n",
       "\n",
       "                                                 cat2        vname  \\\n",
       "0                                    Total households  02HC01_VC03   \n",
       "4     Total households - Family households (families)  02HC01_VC04   \n",
       "8   Total households - Family households (families...  02HC01_VC05   \n",
       "12  Total households - Family households (families...  02HC01_VC06   \n",
       "16  Total households - Family households (families...  02HC01_VC07   \n",
       "\n",
       "              level1                                             level2  \\\n",
       "0   Total households                                                NaN   \n",
       "4   Total households                       Family households (families)   \n",
       "8   Total households  Family households (families) - With own childr...   \n",
       "12  Total households  Family households (families) - Married-couple ...   \n",
       "16  Total households  Family households (families) - Married-couple ...   \n",
       "\n",
       "                                               level3  \n",
       "0                                                 NaN  \n",
       "4                                                 NaN  \n",
       "8   With own children of the householder under 18 ...  \n",
       "12                              Married-couple family  \n",
       "16  Married-couple family - With own children of t...  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "meta.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>Unnamed: 0</th>\n",
       "      <th>zipcode</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>fips</th>\n",
       "      <th>GEO.id2_x</th>\n",
       "      <th>02HC01_VC03</th>\n",
       "      <th>02HC01_VC04</th>\n",
       "      <th>02HC01_VC05</th>\n",
       "      <th>02HC01_VC06</th>\n",
       "      <th>...</th>\n",
       "      <th>05HC01_VC97</th>\n",
       "      <th>05HC01_VC98</th>\n",
       "      <th>05HC01_VC99</th>\n",
       "      <th>05HC01_VC100</th>\n",
       "      <th>05HC01_VC101</th>\n",
       "      <th>05HC01_VC102</th>\n",
       "      <th>05HC01_VC104</th>\n",
       "      <th>05HC01_VC108</th>\n",
       "      <th>05HC01_VC109</th>\n",
       "      <th>05HC01_VC110</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>57163.000000</td>\n",
       "      <td>57163.000000</td>\n",
       "      <td>56436.000000</td>\n",
       "      <td>56436.000000</td>\n",
       "      <td>53467.000000</td>\n",
       "      <td>46965.000000</td>\n",
       "      <td>46965.000000</td>\n",
       "      <td>46965.000000</td>\n",
       "      <td>46965.000000</td>\n",
       "      <td>46965.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>47257.000000</td>\n",
       "      <td>47257.000000</td>\n",
       "      <td>47257.000000</td>\n",
       "      <td>47257.000000</td>\n",
       "      <td>47257.000000</td>\n",
       "      <td>47257.000000</td>\n",
       "      <td>47257.000000</td>\n",
       "      <td>47257.000000</td>\n",
       "      <td>47257.000000</td>\n",
       "      <td>47257.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>28581.000000</td>\n",
       "      <td>49268.873275</td>\n",
       "      <td>38.509356</td>\n",
       "      <td>-90.433289</td>\n",
       "      <td>29998.783960</td>\n",
       "      <td>49672.259023</td>\n",
       "      <td>3436.436325</td>\n",
       "      <td>2286.384329</td>\n",
       "      <td>980.683041</td>\n",
       "      <td>1690.494262</td>\n",
       "      <td>...</td>\n",
       "      <td>414.075989</td>\n",
       "      <td>12.139260</td>\n",
       "      <td>18.159976</td>\n",
       "      <td>200.061218</td>\n",
       "      <td>7.695177</td>\n",
       "      <td>192.366041</td>\n",
       "      <td>3990.069111</td>\n",
       "      <td>6723.684153</td>\n",
       "      <td>3256.909537</td>\n",
       "      <td>3466.774615</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>16501.681056</td>\n",
       "      <td>26640.371876</td>\n",
       "      <td>5.302350</td>\n",
       "      <td>15.269060</td>\n",
       "      <td>15518.847622</td>\n",
       "      <td>25677.868428</td>\n",
       "      <td>4936.433948</td>\n",
       "      <td>3278.631393</td>\n",
       "      <td>1532.780859</td>\n",
       "      <td>2408.296240</td>\n",
       "      <td>...</td>\n",
       "      <td>1783.291993</td>\n",
       "      <td>135.337163</td>\n",
       "      <td>99.203722</td>\n",
       "      <td>436.743299</td>\n",
       "      <td>31.716741</td>\n",
       "      <td>423.807535</td>\n",
       "      <td>5547.377237</td>\n",
       "      <td>9496.737857</td>\n",
       "      <td>4550.462611</td>\n",
       "      <td>4967.558927</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>501.000000</td>\n",
       "      <td>-7.209975</td>\n",
       "      <td>-176.787412</td>\n",
       "      <td>1001.000000</td>\n",
       "      <td>1001.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>14290.500000</td>\n",
       "      <td>28063.000000</td>\n",
       "      <td>35.124321</td>\n",
       "      <td>-96.671008</td>\n",
       "      <td>18065.000000</td>\n",
       "      <td>29135.000000</td>\n",
       "      <td>333.000000</td>\n",
       "      <td>230.000000</td>\n",
       "      <td>78.000000</td>\n",
       "      <td>182.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>440.000000</td>\n",
       "      <td>668.000000</td>\n",
       "      <td>336.000000</td>\n",
       "      <td>328.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>28581.000000</td>\n",
       "      <td>48867.000000</td>\n",
       "      <td>39.109550</td>\n",
       "      <td>-88.055804</td>\n",
       "      <td>29173.000000</td>\n",
       "      <td>49769.000000</td>\n",
       "      <td>1130.000000</td>\n",
       "      <td>780.000000</td>\n",
       "      <td>291.000000</td>\n",
       "      <td>607.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>36.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>35.000000</td>\n",
       "      <td>1421.000000</td>\n",
       "      <td>2273.000000</td>\n",
       "      <td>1135.000000</td>\n",
       "      <td>1136.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>42871.500000</td>\n",
       "      <td>70649.000000</td>\n",
       "      <td>41.873852</td>\n",
       "      <td>-80.734879</td>\n",
       "      <td>42063.000000</td>\n",
       "      <td>68929.000000</td>\n",
       "      <td>4412.000000</td>\n",
       "      <td>2987.000000</td>\n",
       "      <td>1205.000000</td>\n",
       "      <td>2176.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>109.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>194.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>188.000000</td>\n",
       "      <td>5263.000000</td>\n",
       "      <td>8905.000000</td>\n",
       "      <td>4350.000000</td>\n",
       "      <td>4548.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>57162.000000</td>\n",
       "      <td>99950.000000</td>\n",
       "      <td>70.494693</td>\n",
       "      <td>166.410291</td>\n",
       "      <td>78030.000000</td>\n",
       "      <td>99929.000000</td>\n",
       "      <td>42783.000000</td>\n",
       "      <td>27649.000000</td>\n",
       "      <td>16999.000000</td>\n",
       "      <td>22827.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>60407.000000</td>\n",
       "      <td>15389.000000</td>\n",
       "      <td>11889.000000</td>\n",
       "      <td>15479.000000</td>\n",
       "      <td>1792.000000</td>\n",
       "      <td>15305.000000</td>\n",
       "      <td>48196.000000</td>\n",
       "      <td>78545.000000</td>\n",
       "      <td>35405.000000</td>\n",
       "      <td>43140.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8 rows × 463 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         Unnamed: 0       zipcode      latitude     longitude          fips  \\\n",
       "count  57163.000000  57163.000000  56436.000000  56436.000000  53467.000000   \n",
       "mean   28581.000000  49268.873275     38.509356    -90.433289  29998.783960   \n",
       "std    16501.681056  26640.371876      5.302350     15.269060  15518.847622   \n",
       "min        0.000000    501.000000     -7.209975   -176.787412   1001.000000   \n",
       "25%    14290.500000  28063.000000     35.124321    -96.671008  18065.000000   \n",
       "50%    28581.000000  48867.000000     39.109550    -88.055804  29173.000000   \n",
       "75%    42871.500000  70649.000000     41.873852    -80.734879  42063.000000   \n",
       "max    57162.000000  99950.000000     70.494693    166.410291  78030.000000   \n",
       "\n",
       "          GEO.id2_x   02HC01_VC03   02HC01_VC04   02HC01_VC05   02HC01_VC06  \\\n",
       "count  46965.000000  46965.000000  46965.000000  46965.000000  46965.000000   \n",
       "mean   49672.259023   3436.436325   2286.384329    980.683041   1690.494262   \n",
       "std    25677.868428   4936.433948   3278.631393   1532.780859   2408.296240   \n",
       "min     1001.000000      0.000000      0.000000      0.000000      0.000000   \n",
       "25%    29135.000000    333.000000    230.000000     78.000000    182.000000   \n",
       "50%    49769.000000   1130.000000    780.000000    291.000000    607.000000   \n",
       "75%    68929.000000   4412.000000   2987.000000   1205.000000   2176.000000   \n",
       "max    99929.000000  42783.000000  27649.000000  16999.000000  22827.000000   \n",
       "\n",
       "           ...        05HC01_VC97   05HC01_VC98   05HC01_VC99  05HC01_VC100  \\\n",
       "count      ...       47257.000000  47257.000000  47257.000000  47257.000000   \n",
       "mean       ...         414.075989     12.139260     18.159976    200.061218   \n",
       "std        ...        1783.291993    135.337163     99.203722    436.743299   \n",
       "min        ...           0.000000      0.000000      0.000000      0.000000   \n",
       "25%        ...           0.000000      0.000000      0.000000      4.000000   \n",
       "50%        ...           7.000000      0.000000      0.000000     36.000000   \n",
       "75%        ...         109.000000      0.000000      4.000000    194.000000   \n",
       "max        ...       60407.000000  15389.000000  11889.000000  15479.000000   \n",
       "\n",
       "       05HC01_VC101  05HC01_VC102  05HC01_VC104  05HC01_VC108  05HC01_VC109  \\\n",
       "count  47257.000000  47257.000000  47257.000000  47257.000000  47257.000000   \n",
       "mean       7.695177    192.366041   3990.069111   6723.684153   3256.909537   \n",
       "std       31.716741    423.807535   5547.377237   9496.737857   4550.462611   \n",
       "min        0.000000      0.000000      0.000000      0.000000      0.000000   \n",
       "25%        0.000000      3.000000    440.000000    668.000000    336.000000   \n",
       "50%        0.000000     35.000000   1421.000000   2273.000000   1135.000000   \n",
       "75%        0.000000    188.000000   5263.000000   8905.000000   4350.000000   \n",
       "max     1792.000000  15305.000000  48196.000000  78545.000000  35405.000000   \n",
       "\n",
       "       05HC01_VC110  \n",
       "count  47257.000000  \n",
       "mean    3466.774615  \n",
       "std     4967.558927  \n",
       "min        0.000000  \n",
       "25%      328.000000  \n",
       "50%     1136.000000  \n",
       "75%     4548.000000  \n",
       "max    43140.000000  \n",
       "\n",
       "[8 rows x 463 columns]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['city', 'state', 'county', 'GEO.id_x', 'GEO.display-label_x',\n",
       "       '02HC01_VC21', '02HC01_VC22', '02HC01_VC52', '02HC01_VC53',\n",
       "       '02HC01_VC54', '02HC01_VC55', '02HC01_VC56', '02HC01_VC57',\n",
       "       '02HC01_VC58', '02HC01_VC95', '02HC01_VC96', '02HC01_VC216',\n",
       "       '02HC01_VC217', '02HC01_VC218', 'GEO.id_y', 'GEO.display-label_y',\n",
       "       '03HC01_VC12', '03HC01_VC36', '03HC01_VC85', '03HC01_VC86',\n",
       "       '03HC01_VC90', '03HC01_VC92', '03HC01_VC94', '03HC01_VC98',\n",
       "       '03HC01_VC100', '03HC01_VC114', '03HC01_VC115', '03HC01_VC118',\n",
       "       '03HC01_VC121', '03HC01_VC122', '03HC01_VC124', '03HC01_VC125',\n",
       "       '03HC01_VC126', '03HC01_VC161', '03HC01_VC162', '03HC01_VC163',\n",
       "       '03HC01_VC164', '03HC01_VC165', '03HC01_VC166', '03HC01_VC167',\n",
       "       '03HC01_VC168', '03HC01_VC169', '03HC01_VC171', '03HC01_VC172',\n",
       "       '03HC01_VC173', '03HC01_VC174', '03HC01_VC175', '03HC01_VC176',\n",
       "       '03HC01_VC177', '03HC01_VC178', '03HC01_VC179', '03HC01_VC180',\n",
       "       'GEO.id_x', 'GEO.display-label_x', '04HC01_VC08', '04HC01_VC09',\n",
       "       '04HC01_VC50', '04HC01_VC69', '04HC01_VC70', '04HC01_VC106',\n",
       "       '04HC01_VC108', '04HC01_VC128', '04HC01_VC146', '04HC01_VC155',\n",
       "       '04HC01_VC191', 'GEO.id_y', 'GEO.display-label_y', '05HC01_VC23'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.select_dtypes(include=['object']).columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "columns = ['02HC01_VC21', '02HC01_VC22', '02HC01_VC52', '02HC01_VC53',\n",
    "       '02HC01_VC54', '02HC01_VC55', '02HC01_VC56', '02HC01_VC57',\n",
    "       '02HC01_VC58', '02HC01_VC95', '02HC01_VC96', '02HC01_VC216',\n",
    "       '02HC01_VC217', '02HC01_VC218', '03HC01_VC12', '03HC01_VC36',\n",
    "       '03HC01_VC85', '03HC01_VC86', '03HC01_VC90', '03HC01_VC92',\n",
    "       '03HC01_VC94', '03HC01_VC98', '03HC01_VC100', '03HC01_VC114',\n",
    "       '03HC01_VC115', '03HC01_VC118', '03HC01_VC121', '03HC01_VC122',\n",
    "       '03HC01_VC124', '03HC01_VC125', '03HC01_VC126', '03HC01_VC161',\n",
    "       '03HC01_VC162', '03HC01_VC163', '03HC01_VC164', '03HC01_VC165',\n",
    "       '03HC01_VC166', '03HC01_VC167', '03HC01_VC168', '03HC01_VC169',\n",
    "       '03HC01_VC171', '03HC01_VC172', '03HC01_VC173', '03HC01_VC174',\n",
    "       '03HC01_VC175', '03HC01_VC176', '03HC01_VC177', '03HC01_VC178',\n",
    "       '03HC01_VC179', '03HC01_VC180', '04HC01_VC08', '04HC01_VC09',\n",
    "       '04HC01_VC50', '04HC01_VC69', '04HC01_VC70', '04HC01_VC106',\n",
    "       '04HC01_VC108', '04HC01_VC128', '04HC01_VC146', '04HC01_VC155',\n",
    "       '04HC01_VC191', '05HC01_VC23']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[columns] = df[columns].apply(pd.to_numeric, errors='coerce')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Unnamed: 0               int64\n",
       "zipcode                  int64\n",
       "latitude               float64\n",
       "longitude              float64\n",
       "city                    object\n",
       "state                   object\n",
       "county                  object\n",
       "fips                   float64\n",
       "GEO.id_x                object\n",
       "GEO.id2_x              float64\n",
       "GEO.display-label_x     object\n",
       "02HC01_VC03            float64\n",
       "02HC01_VC04            float64\n",
       "02HC01_VC05            float64\n",
       "02HC01_VC06            float64\n",
       "02HC01_VC07            float64\n",
       "02HC01_VC08            float64\n",
       "02HC01_VC09            float64\n",
       "02HC01_VC10            float64\n",
       "02HC01_VC11            float64\n",
       "02HC01_VC12            float64\n",
       "02HC01_VC13            float64\n",
       "02HC01_VC14            float64\n",
       "02HC01_VC17            float64\n",
       "02HC01_VC18            float64\n",
       "02HC01_VC21            float64\n",
       "02HC01_VC22            float64\n",
       "02HC01_VC26            float64\n",
       "02HC01_VC27            float64\n",
       "02HC01_VC28            float64\n",
       "                        ...   \n",
       "05HC01_VC72            float64\n",
       "05HC01_VC73            float64\n",
       "05HC01_VC74            float64\n",
       "05HC01_VC77            float64\n",
       "05HC01_VC78            float64\n",
       "05HC01_VC79            float64\n",
       "05HC01_VC80            float64\n",
       "05HC01_VC81            float64\n",
       "05HC01_VC82            float64\n",
       "05HC01_VC83            float64\n",
       "05HC01_VC87            float64\n",
       "05HC01_VC88            float64\n",
       "05HC01_VC89            float64\n",
       "05HC01_VC90            float64\n",
       "05HC01_VC91            float64\n",
       "05HC01_VC92            float64\n",
       "05HC01_VC93            float64\n",
       "05HC01_VC94            float64\n",
       "05HC01_VC95            float64\n",
       "05HC01_VC96            float64\n",
       "05HC01_VC97            float64\n",
       "05HC01_VC98            float64\n",
       "05HC01_VC99            float64\n",
       "05HC01_VC100           float64\n",
       "05HC01_VC101           float64\n",
       "05HC01_VC102           float64\n",
       "05HC01_VC104           float64\n",
       "05HC01_VC108           float64\n",
       "05HC01_VC109           float64\n",
       "05HC01_VC110           float64\n",
       "Length: 536, dtype: object"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# see column types\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['city', 'state', 'county', 'GEO.id_x', 'GEO.display-label_x',\n",
       "       'GEO.id_y', 'GEO.display-label_y', 'GEO.id_x', 'GEO.display-label_x',\n",
       "       'GEO.id_y', 'GEO.display-label_y'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.select_dtypes(include=['object']).columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 57163 entries, 0 to 57162\n",
      "Columns: 536 entries, Unnamed: 0 to 05HC01_VC110\n",
      "dtypes: float64(523), int64(2), object(11)\n",
      "memory usage: 234.2+ MB\n",
      "None\n"
     ]
    }
   ],
   "source": [
    "# get info on data\n",
    "print(df.info())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # DataFrame method to check for missing values in a column\n",
    "# # The .all() method returns True if all values are True\n",
    "# # if using it on a DataFrame, you need to chain another .all() \n",
    "# # method so that you return only one True or False value.\n",
    "# assert pd.notnull(df['GEO.id_x']).all().all()\n",
    "# assert pd.notnull(df['HC01_VC150']).all().all()\n",
    "# assert (df.HC01_VC150 >=0).all().all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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>Unnamed: 0</th>\n",
       "      <th>zipcode</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>county</th>\n",
       "      <th>fips</th>\n",
       "      <th>GEO.id_x</th>\n",
       "      <th>GEO.id2_x</th>\n",
       "      <th>...</th>\n",
       "      <th>05HC01_VC97</th>\n",
       "      <th>05HC01_VC98</th>\n",
       "      <th>05HC01_VC99</th>\n",
       "      <th>05HC01_VC100</th>\n",
       "      <th>05HC01_VC101</th>\n",
       "      <th>05HC01_VC102</th>\n",
       "      <th>05HC01_VC104</th>\n",
       "      <th>05HC01_VC108</th>\n",
       "      <th>05HC01_VC109</th>\n",
       "      <th>05HC01_VC110</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>57143</th>\n",
       "      <td>57143</td>\n",
       "      <td>99832</td>\n",
       "      <td>58.331798</td>\n",
       "      <td>-135.181256</td>\n",
       "      <td>Pelican</td>\n",
       "      <td>AK</td>\n",
       "      <td>Skagway Hoonah Angoon</td>\n",
       "      <td>2105.0</td>\n",
       "      <td>8600000US99832</td>\n",
       "      <td>99832.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>126.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>38.0</td>\n",
       "      <td>52.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57144</th>\n",
       "      <td>57144</td>\n",
       "      <td>99833</td>\n",
       "      <td>56.767056</td>\n",
       "      <td>-132.931950</td>\n",
       "      <td>Petersburg</td>\n",
       "      <td>AK</td>\n",
       "      <td>Wrangell Petersburg</td>\n",
       "      <td>2195.0</td>\n",
       "      <td>8600000US99833</td>\n",
       "      <td>99833.0</td>\n",
       "      <td>...</td>\n",
       "      <td>142.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>245.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>233.0</td>\n",
       "      <td>1759.0</td>\n",
       "      <td>2341.0</td>\n",
       "      <td>1289.0</td>\n",
       "      <td>1052.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57145</th>\n",
       "      <td>57145</td>\n",
       "      <td>99835</td>\n",
       "      <td>57.080818</td>\n",
       "      <td>-135.318348</td>\n",
       "      <td>Sitka</td>\n",
       "      <td>AK</td>\n",
       "      <td>Sitka</td>\n",
       "      <td>2220.0</td>\n",
       "      <td>8600000US99835</td>\n",
       "      <td>99835.0</td>\n",
       "      <td>...</td>\n",
       "      <td>605.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>873.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>829.0</td>\n",
       "      <td>4100.0</td>\n",
       "      <td>6601.0</td>\n",
       "      <td>3350.0</td>\n",
       "      <td>3251.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57146</th>\n",
       "      <td>57146</td>\n",
       "      <td>99836</td>\n",
       "      <td>56.535950</td>\n",
       "      <td>-132.827948</td>\n",
       "      <td>Port Alexander</td>\n",
       "      <td>AK</td>\n",
       "      <td>Wrangell Petersburg</td>\n",
       "      <td>2195.0</td>\n",
       "      <td>8600000US99836</td>\n",
       "      <td>99836.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57147</th>\n",
       "      <td>57147</td>\n",
       "      <td>99840</td>\n",
       "      <td>59.456753</td>\n",
       "      <td>-135.314852</td>\n",
       "      <td>Skagway</td>\n",
       "      <td>AK</td>\n",
       "      <td>Skagway Hoonah Angoon</td>\n",
       "      <td>2230.0</td>\n",
       "      <td>8600000US99840</td>\n",
       "      <td>99840.0</td>\n",
       "      <td>...</td>\n",
       "      <td>76.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>639.0</td>\n",
       "      <td>823.0</td>\n",
       "      <td>470.0</td>\n",
       "      <td>353.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57148</th>\n",
       "      <td>57148</td>\n",
       "      <td>99841</td>\n",
       "      <td>57.798249</td>\n",
       "      <td>-135.285061</td>\n",
       "      <td>Tenakee Springs</td>\n",
       "      <td>AK</td>\n",
       "      <td>Skagway Hoonah Angoon</td>\n",
       "      <td>2105.0</td>\n",
       "      <td>8600000US99841</td>\n",
       "      <td>99841.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>205.0</td>\n",
       "      <td>143.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>63.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57149</th>\n",
       "      <td>57149</td>\n",
       "      <td>99850</td>\n",
       "      <td>58.383480</td>\n",
       "      <td>-134.197786</td>\n",
       "      <td>Juneau</td>\n",
       "      <td>AK</td>\n",
       "      <td>Juneau</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57150</th>\n",
       "      <td>57150</td>\n",
       "      <td>99901</td>\n",
       "      <td>55.400674</td>\n",
       "      <td>-131.674090</td>\n",
       "      <td>Ketchikan</td>\n",
       "      <td>AK</td>\n",
       "      <td>Ketchikan Gateway</td>\n",
       "      <td>2130.0</td>\n",
       "      <td>8600000US99901</td>\n",
       "      <td>99901.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1049.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>1259.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1259.0</td>\n",
       "      <td>6254.0</td>\n",
       "      <td>10184.0</td>\n",
       "      <td>5327.0</td>\n",
       "      <td>4857.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57151</th>\n",
       "      <td>57151</td>\n",
       "      <td>99903</td>\n",
       "      <td>55.517921</td>\n",
       "      <td>-132.003244</td>\n",
       "      <td>Meyers Chuck</td>\n",
       "      <td>AK</td>\n",
       "      <td>Prince Wales Ketchikan</td>\n",
       "      <td>2275.0</td>\n",
       "      <td>8600000US99903</td>\n",
       "      <td>99903.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57152</th>\n",
       "      <td>57152</td>\n",
       "      <td>99918</td>\n",
       "      <td>55.517921</td>\n",
       "      <td>-132.003244</td>\n",
       "      <td>Coffman Cove</td>\n",
       "      <td>AK</td>\n",
       "      <td>Prince Wales Ketchikan</td>\n",
       "      <td>2198.0</td>\n",
       "      <td>8600000US99918</td>\n",
       "      <td>99918.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>233.0</td>\n",
       "      <td>170.0</td>\n",
       "      <td>97.0</td>\n",
       "      <td>73.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57153</th>\n",
       "      <td>57153</td>\n",
       "      <td>99919</td>\n",
       "      <td>55.126774</td>\n",
       "      <td>-131.580407</td>\n",
       "      <td>Thorne Bay</td>\n",
       "      <td>AK</td>\n",
       "      <td>Prince Wales Ketchikan</td>\n",
       "      <td>2198.0</td>\n",
       "      <td>8600000US99919</td>\n",
       "      <td>99919.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>526.0</td>\n",
       "      <td>540.0</td>\n",
       "      <td>298.0</td>\n",
       "      <td>242.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57154</th>\n",
       "      <td>57154</td>\n",
       "      <td>99921</td>\n",
       "      <td>55.513301</td>\n",
       "      <td>-133.117022</td>\n",
       "      <td>Craig</td>\n",
       "      <td>AK</td>\n",
       "      <td>Prince Wales Ketchikan</td>\n",
       "      <td>2198.0</td>\n",
       "      <td>8600000US99921</td>\n",
       "      <td>99921.0</td>\n",
       "      <td>...</td>\n",
       "      <td>26.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>178.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>178.0</td>\n",
       "      <td>1026.0</td>\n",
       "      <td>1392.0</td>\n",
       "      <td>778.0</td>\n",
       "      <td>614.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57155</th>\n",
       "      <td>57155</td>\n",
       "      <td>99922</td>\n",
       "      <td>55.208706</td>\n",
       "      <td>-132.825903</td>\n",
       "      <td>Hydaburg</td>\n",
       "      <td>AK</td>\n",
       "      <td>Prince Wales Ketchikan</td>\n",
       "      <td>2198.0</td>\n",
       "      <td>8600000US99922</td>\n",
       "      <td>99922.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>111.0</td>\n",
       "      <td>206.0</td>\n",
       "      <td>102.0</td>\n",
       "      <td>104.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57156</th>\n",
       "      <td>57156</td>\n",
       "      <td>99923</td>\n",
       "      <td>55.517921</td>\n",
       "      <td>-132.003244</td>\n",
       "      <td>Hyder</td>\n",
       "      <td>AK</td>\n",
       "      <td>Prince Wales Ketchikan</td>\n",
       "      <td>2198.0</td>\n",
       "      <td>8600000US99923</td>\n",
       "      <td>99923.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57157</th>\n",
       "      <td>57157</td>\n",
       "      <td>99925</td>\n",
       "      <td>55.554393</td>\n",
       "      <td>-133.090680</td>\n",
       "      <td>Klawock</td>\n",
       "      <td>AK</td>\n",
       "      <td>Prince Wales Ketchikan</td>\n",
       "      <td>2198.0</td>\n",
       "      <td>8600000US99925</td>\n",
       "      <td>99925.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>401.0</td>\n",
       "      <td>699.0</td>\n",
       "      <td>396.0</td>\n",
       "      <td>303.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57158</th>\n",
       "      <td>57158</td>\n",
       "      <td>99926</td>\n",
       "      <td>55.094325</td>\n",
       "      <td>-131.566827</td>\n",
       "      <td>Metlakatla</td>\n",
       "      <td>AK</td>\n",
       "      <td>Prince Wales Ketchikan</td>\n",
       "      <td>2198.0</td>\n",
       "      <td>8600000US99926</td>\n",
       "      <td>99926.0</td>\n",
       "      <td>...</td>\n",
       "      <td>18.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>200.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>200.0</td>\n",
       "      <td>521.0</td>\n",
       "      <td>1152.0</td>\n",
       "      <td>604.0</td>\n",
       "      <td>548.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57159</th>\n",
       "      <td>57159</td>\n",
       "      <td>99927</td>\n",
       "      <td>55.517921</td>\n",
       "      <td>-132.003244</td>\n",
       "      <td>Point Baker</td>\n",
       "      <td>AK</td>\n",
       "      <td>Prince Wales Ketchikan</td>\n",
       "      <td>2198.0</td>\n",
       "      <td>8600000US99927</td>\n",
       "      <td>99927.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>121.0</td>\n",
       "      <td>81.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>37.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57160</th>\n",
       "      <td>57160</td>\n",
       "      <td>99928</td>\n",
       "      <td>55.395359</td>\n",
       "      <td>-131.675370</td>\n",
       "      <td>Ward Cove</td>\n",
       "      <td>AK</td>\n",
       "      <td>Ketchikan Gateway</td>\n",
       "      <td>2130.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57161</th>\n",
       "      <td>57161</td>\n",
       "      <td>99929</td>\n",
       "      <td>56.449893</td>\n",
       "      <td>-132.364407</td>\n",
       "      <td>Wrangell</td>\n",
       "      <td>AK</td>\n",
       "      <td>Wrangell Petersburg</td>\n",
       "      <td>2275.0</td>\n",
       "      <td>8600000US99929</td>\n",
       "      <td>99929.0</td>\n",
       "      <td>...</td>\n",
       "      <td>83.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>223.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>223.0</td>\n",
       "      <td>1378.0</td>\n",
       "      <td>1848.0</td>\n",
       "      <td>921.0</td>\n",
       "      <td>927.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57162</th>\n",
       "      <td>57162</td>\n",
       "      <td>99950</td>\n",
       "      <td>55.542007</td>\n",
       "      <td>-131.432682</td>\n",
       "      <td>Ketchikan</td>\n",
       "      <td>AK</td>\n",
       "      <td>Ketchikan Gateway</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>20 rows × 536 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Unnamed: 0  zipcode   latitude   longitude             city state  \\\n",
       "57143       57143    99832  58.331798 -135.181256          Pelican    AK   \n",
       "57144       57144    99833  56.767056 -132.931950       Petersburg    AK   \n",
       "57145       57145    99835  57.080818 -135.318348            Sitka    AK   \n",
       "57146       57146    99836  56.535950 -132.827948   Port Alexander    AK   \n",
       "57147       57147    99840  59.456753 -135.314852          Skagway    AK   \n",
       "57148       57148    99841  57.798249 -135.285061  Tenakee Springs    AK   \n",
       "57149       57149    99850  58.383480 -134.197786           Juneau    AK   \n",
       "57150       57150    99901  55.400674 -131.674090        Ketchikan    AK   \n",
       "57151       57151    99903  55.517921 -132.003244     Meyers Chuck    AK   \n",
       "57152       57152    99918  55.517921 -132.003244     Coffman Cove    AK   \n",
       "57153       57153    99919  55.126774 -131.580407       Thorne Bay    AK   \n",
       "57154       57154    99921  55.513301 -133.117022            Craig    AK   \n",
       "57155       57155    99922  55.208706 -132.825903         Hydaburg    AK   \n",
       "57156       57156    99923  55.517921 -132.003244            Hyder    AK   \n",
       "57157       57157    99925  55.554393 -133.090680          Klawock    AK   \n",
       "57158       57158    99926  55.094325 -131.566827       Metlakatla    AK   \n",
       "57159       57159    99927  55.517921 -132.003244      Point Baker    AK   \n",
       "57160       57160    99928  55.395359 -131.675370        Ward Cove    AK   \n",
       "57161       57161    99929  56.449893 -132.364407         Wrangell    AK   \n",
       "57162       57162    99950  55.542007 -131.432682        Ketchikan    AK   \n",
       "\n",
       "                       county    fips        GEO.id_x  GEO.id2_x  \\\n",
       "57143   Skagway Hoonah Angoon  2105.0  8600000US99832    99832.0   \n",
       "57144     Wrangell Petersburg  2195.0  8600000US99833    99833.0   \n",
       "57145                   Sitka  2220.0  8600000US99835    99835.0   \n",
       "57146     Wrangell Petersburg  2195.0  8600000US99836    99836.0   \n",
       "57147   Skagway Hoonah Angoon  2230.0  8600000US99840    99840.0   \n",
       "57148   Skagway Hoonah Angoon  2105.0  8600000US99841    99841.0   \n",
       "57149                  Juneau     NaN             NaN        NaN   \n",
       "57150       Ketchikan Gateway  2130.0  8600000US99901    99901.0   \n",
       "57151  Prince Wales Ketchikan  2275.0  8600000US99903    99903.0   \n",
       "57152  Prince Wales Ketchikan  2198.0  8600000US99918    99918.0   \n",
       "57153  Prince Wales Ketchikan  2198.0  8600000US99919    99919.0   \n",
       "57154  Prince Wales Ketchikan  2198.0  8600000US99921    99921.0   \n",
       "57155  Prince Wales Ketchikan  2198.0  8600000US99922    99922.0   \n",
       "57156  Prince Wales Ketchikan  2198.0  8600000US99923    99923.0   \n",
       "57157  Prince Wales Ketchikan  2198.0  8600000US99925    99925.0   \n",
       "57158  Prince Wales Ketchikan  2198.0  8600000US99926    99926.0   \n",
       "57159  Prince Wales Ketchikan  2198.0  8600000US99927    99927.0   \n",
       "57160       Ketchikan Gateway  2130.0             NaN        NaN   \n",
       "57161     Wrangell Petersburg  2275.0  8600000US99929    99929.0   \n",
       "57162       Ketchikan Gateway     NaN             NaN        NaN   \n",
       "\n",
       "           ...      05HC01_VC97  05HC01_VC98  05HC01_VC99  05HC01_VC100  \\\n",
       "57143      ...              0.0          0.0          0.0          19.0   \n",
       "57144      ...            142.0         20.0          2.0         245.0   \n",
       "57145      ...            605.0         33.0          0.0         873.0   \n",
       "57146      ...              0.0          0.0          0.0           6.0   \n",
       "57147      ...             76.0          0.0          2.0          45.0   \n",
       "57148      ...              0.0          0.0          0.0           0.0   \n",
       "57149      ...              NaN          NaN          NaN           NaN   \n",
       "57150      ...           1049.0         10.0         74.0        1259.0   \n",
       "57151      ...              0.0          0.0          0.0           0.0   \n",
       "57152      ...              0.0          0.0          0.0           5.0   \n",
       "57153      ...              0.0          2.0          0.0          28.0   \n",
       "57154      ...             26.0          0.0          0.0         178.0   \n",
       "57155      ...              1.0         22.0          0.0           9.0   \n",
       "57156      ...              0.0          0.0          0.0           0.0   \n",
       "57157      ...              0.0         13.0          0.0          78.0   \n",
       "57158      ...             18.0          1.0          0.0         200.0   \n",
       "57159      ...              0.0          0.0          7.0           0.0   \n",
       "57160      ...              NaN          NaN          NaN           NaN   \n",
       "57161      ...             83.0          0.0          2.0         223.0   \n",
       "57162      ...              NaN          NaN          NaN           NaN   \n",
       "\n",
       "       05HC01_VC101  05HC01_VC102  05HC01_VC104  05HC01_VC108  05HC01_VC109  \\\n",
       "57143           0.0          19.0         126.0          90.0          38.0   \n",
       "57144          12.0         233.0        1759.0        2341.0        1289.0   \n",
       "57145          44.0         829.0        4100.0        6601.0        3350.0   \n",
       "57146           0.0           6.0          65.0          31.0          14.0   \n",
       "57147           4.0          41.0         639.0         823.0         470.0   \n",
       "57148           0.0           0.0         205.0         143.0          80.0   \n",
       "57149           NaN           NaN           NaN           NaN           NaN   \n",
       "57150           0.0        1259.0        6254.0       10184.0        5327.0   \n",
       "57151           0.0           0.0          23.0          16.0           8.0   \n",
       "57152           0.0           5.0         233.0         170.0          97.0   \n",
       "57153           0.0          28.0         526.0         540.0         298.0   \n",
       "57154           0.0         178.0        1026.0        1392.0         778.0   \n",
       "57155           3.0           6.0         111.0         206.0         102.0   \n",
       "57156           0.0           0.0          62.0           0.0           0.0   \n",
       "57157           0.0          78.0         401.0         699.0         396.0   \n",
       "57158           0.0         200.0         521.0        1152.0         604.0   \n",
       "57159           0.0           0.0         121.0          81.0          44.0   \n",
       "57160           NaN           NaN           NaN           NaN           NaN   \n",
       "57161           0.0         223.0        1378.0        1848.0         921.0   \n",
       "57162           NaN           NaN           NaN           NaN           NaN   \n",
       "\n",
       "       05HC01_VC110  \n",
       "57143          52.0  \n",
       "57144        1052.0  \n",
       "57145        3251.0  \n",
       "57146          17.0  \n",
       "57147         353.0  \n",
       "57148          63.0  \n",
       "57149           NaN  \n",
       "57150        4857.0  \n",
       "57151           8.0  \n",
       "57152          73.0  \n",
       "57153         242.0  \n",
       "57154         614.0  \n",
       "57155         104.0  \n",
       "57156           0.0  \n",
       "57157         303.0  \n",
       "57158         548.0  \n",
       "57159          37.0  \n",
       "57160           NaN  \n",
       "57161         927.0  \n",
       "57162           NaN  \n",
       "\n",
       "[20 rows x 536 columns]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.tail(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # save df and meta to csv\n",
    "# df.to_csv(\"./data/df1.csv\")\n",
    "# meta.to_csv(\"./data/meta1.csv\")"
   ]
  },
  {
   "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>Unnamed: 0</th>\n",
       "      <th>zipcode</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>county</th>\n",
       "      <th>fips</th>\n",
       "      <th>GEO.id_x</th>\n",
       "      <th>GEO.id2_x</th>\n",
       "      <th>...</th>\n",
       "      <th>05HC01_VC97</th>\n",
       "      <th>05HC01_VC98</th>\n",
       "      <th>05HC01_VC99</th>\n",
       "      <th>05HC01_VC100</th>\n",
       "      <th>05HC01_VC101</th>\n",
       "      <th>05HC01_VC102</th>\n",
       "      <th>05HC01_VC104</th>\n",
       "      <th>05HC01_VC108</th>\n",
       "      <th>05HC01_VC109</th>\n",
       "      <th>05HC01_VC110</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>501</td>\n",
       "      <td>40.922326</td>\n",
       "      <td>-72.637078</td>\n",
       "      <td>Holtsville</td>\n",
       "      <td>NY</td>\n",
       "      <td>Suffolk</td>\n",
       "      <td>36103.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>544</td>\n",
       "      <td>40.922326</td>\n",
       "      <td>-72.637078</td>\n",
       "      <td>Holtsville</td>\n",
       "      <td>NY</td>\n",
       "      <td>Suffolk</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>601</td>\n",
       "      <td>18.165273</td>\n",
       "      <td>-66.722583</td>\n",
       "      <td>Adjuntas</td>\n",
       "      <td>PR</td>\n",
       "      <td>Adjuntas</td>\n",
       "      <td>72001.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7290.0</td>\n",
       "      <td>13646.0</td>\n",
       "      <td>6573.0</td>\n",
       "      <td>7073.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>601</td>\n",
       "      <td>18.165273</td>\n",
       "      <td>-66.722583</td>\n",
       "      <td>Adjuntas</td>\n",
       "      <td>PR</td>\n",
       "      <td>Adjuntas</td>\n",
       "      <td>72113.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7290.0</td>\n",
       "      <td>13646.0</td>\n",
       "      <td>6573.0</td>\n",
       "      <td>7073.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>602</td>\n",
       "      <td>18.393103</td>\n",
       "      <td>-67.180953</td>\n",
       "      <td>Aguada</td>\n",
       "      <td>PR</td>\n",
       "      <td>Aguada</td>\n",
       "      <td>72003.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>59.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>960.0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>928.0</td>\n",
       "      <td>17312.0</td>\n",
       "      <td>31048.0</td>\n",
       "      <td>15156.0</td>\n",
       "      <td>15892.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 536 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  zipcode   latitude  longitude        city state    county  \\\n",
       "0           0      501  40.922326 -72.637078  Holtsville    NY   Suffolk   \n",
       "1           1      544  40.922326 -72.637078  Holtsville    NY   Suffolk   \n",
       "2           2      601  18.165273 -66.722583    Adjuntas    PR  Adjuntas   \n",
       "3           3      601  18.165273 -66.722583    Adjuntas    PR  Adjuntas   \n",
       "4           4      602  18.393103 -67.180953      Aguada    PR    Aguada   \n",
       "\n",
       "      fips GEO.id_x  GEO.id2_x      ...      05HC01_VC97  05HC01_VC98  \\\n",
       "0  36103.0      NaN        NaN      ...              NaN          NaN   \n",
       "1      NaN      NaN        NaN      ...              NaN          NaN   \n",
       "2  72001.0      NaN        NaN      ...              0.0          0.0   \n",
       "3  72113.0      NaN        NaN      ...              0.0          0.0   \n",
       "4  72003.0      NaN        NaN      ...             59.0          0.0   \n",
       "\n",
       "   05HC01_VC99  05HC01_VC100  05HC01_VC101  05HC01_VC102  05HC01_VC104  \\\n",
       "0          NaN           NaN           NaN           NaN           NaN   \n",
       "1          NaN           NaN           NaN           NaN           NaN   \n",
       "2          0.0           0.0           0.0           0.0        7290.0   \n",
       "3          0.0           0.0           0.0           0.0        7290.0   \n",
       "4         31.0         960.0          32.0         928.0       17312.0   \n",
       "\n",
       "   05HC01_VC108  05HC01_VC109  05HC01_VC110  \n",
       "0           NaN           NaN           NaN  \n",
       "1           NaN           NaN           NaN  \n",
       "2       13646.0        6573.0        7073.0  \n",
       "3       13646.0        6573.0        7073.0  \n",
       "4       31048.0       15156.0       15892.0  \n",
       "\n",
       "[5 rows x 536 columns]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "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>v</th>\n",
       "      <th>detail</th>\n",
       "      <th>type</th>\n",
       "      <th>cat0</th>\n",
       "      <th>cat1</th>\n",
       "      <th>cat2</th>\n",
       "      <th>vname</th>\n",
       "      <th>level1</th>\n",
       "      <th>level2</th>\n",
       "      <th>level3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>HC01_VC03</td>\n",
       "      <td>Estimate; HOUSEHOLDS BY TYPE - Total households</td>\n",
       "      <td>Estimate</td>\n",
       "      <td>Social</td>\n",
       "      <td>HOUSEHOLDS BY TYPE</td>\n",
       "      <td>Total households</td>\n",
       "      <td>02HC01_VC03</td>\n",
       "      <td>Total households</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>HC01_VC04</td>\n",
       "      <td>Estimate; HOUSEHOLDS BY TYPE - Total household...</td>\n",
       "      <td>Estimate</td>\n",
       "      <td>Social</td>\n",
       "      <td>HOUSEHOLDS BY TYPE</td>\n",
       "      <td>Total households - Family households (families)</td>\n",
       "      <td>02HC01_VC04</td>\n",
       "      <td>Total households</td>\n",
       "      <td>Family households (families)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>HC01_VC05</td>\n",
       "      <td>Estimate; HOUSEHOLDS BY TYPE - Total household...</td>\n",
       "      <td>Estimate</td>\n",
       "      <td>Social</td>\n",
       "      <td>HOUSEHOLDS BY TYPE</td>\n",
       "      <td>Total households - Family households (families...</td>\n",
       "      <td>02HC01_VC05</td>\n",
       "      <td>Total households</td>\n",
       "      <td>Family households (families) - With own childr...</td>\n",
       "      <td>With own children of the householder under 18 ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>HC01_VC06</td>\n",
       "      <td>Estimate; HOUSEHOLDS BY TYPE - Total household...</td>\n",
       "      <td>Estimate</td>\n",
       "      <td>Social</td>\n",
       "      <td>HOUSEHOLDS BY TYPE</td>\n",
       "      <td>Total households - Family households (families...</td>\n",
       "      <td>02HC01_VC06</td>\n",
       "      <td>Total households</td>\n",
       "      <td>Family households (families) - Married-couple ...</td>\n",
       "      <td>Married-couple family</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>HC01_VC07</td>\n",
       "      <td>Estimate; HOUSEHOLDS BY TYPE - Total household...</td>\n",
       "      <td>Estimate</td>\n",
       "      <td>Social</td>\n",
       "      <td>HOUSEHOLDS BY TYPE</td>\n",
       "      <td>Total households - Family households (families...</td>\n",
       "      <td>02HC01_VC07</td>\n",
       "      <td>Total households</td>\n",
       "      <td>Family households (families) - Married-couple ...</td>\n",
       "      <td>Married-couple family - With own children of t...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            v                                             detail      type  \\\n",
       "0   HC01_VC03    Estimate; HOUSEHOLDS BY TYPE - Total households  Estimate   \n",
       "4   HC01_VC04  Estimate; HOUSEHOLDS BY TYPE - Total household...  Estimate   \n",
       "8   HC01_VC05  Estimate; HOUSEHOLDS BY TYPE - Total household...  Estimate   \n",
       "12  HC01_VC06  Estimate; HOUSEHOLDS BY TYPE - Total household...  Estimate   \n",
       "16  HC01_VC07  Estimate; HOUSEHOLDS BY TYPE - Total household...  Estimate   \n",
       "\n",
       "      cat0                cat1  \\\n",
       "0   Social  HOUSEHOLDS BY TYPE   \n",
       "4   Social  HOUSEHOLDS BY TYPE   \n",
       "8   Social  HOUSEHOLDS BY TYPE   \n",
       "12  Social  HOUSEHOLDS BY TYPE   \n",
       "16  Social  HOUSEHOLDS BY TYPE   \n",
       "\n",
       "                                                 cat2        vname  \\\n",
       "0                                    Total households  02HC01_VC03   \n",
       "4     Total households - Family households (families)  02HC01_VC04   \n",
       "8   Total households - Family households (families...  02HC01_VC05   \n",
       "12  Total households - Family households (families...  02HC01_VC06   \n",
       "16  Total households - Family households (families...  02HC01_VC07   \n",
       "\n",
       "              level1                                             level2  \\\n",
       "0   Total households                                                NaN   \n",
       "4   Total households                       Family households (families)   \n",
       "8   Total households  Family households (families) - With own childr...   \n",
       "12  Total households  Family households (families) - Married-couple ...   \n",
       "16  Total households  Family households (families) - Married-couple ...   \n",
       "\n",
       "                                               level3  \n",
       "0                                                 NaN  \n",
       "4                                                 NaN  \n",
       "8   With own children of the householder under 18 ...  \n",
       "12                              Married-couple family  \n",
       "16  Married-couple family - With own children of t...  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "meta.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "social = meta[meta['cat0'] == 'Social']\n",
    "total_household = social[social['level1'] == 'Total households']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "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>v</th>\n",
       "      <th>detail</th>\n",
       "      <th>type</th>\n",
       "      <th>cat0</th>\n",
       "      <th>cat1</th>\n",
       "      <th>cat2</th>\n",
       "      <th>vname</th>\n",
       "      <th>level1</th>\n",
       "      <th>level2</th>\n",
       "      <th>level3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>HC01_VC03</td>\n",
       "      <td>Estimate; HOUSEHOLDS BY TYPE - Total households</td>\n",
       "      <td>Estimate</td>\n",
       "      <td>Social</td>\n",
       "      <td>HOUSEHOLDS BY TYPE</td>\n",
       "      <td>Total households</td>\n",
       "      <td>02HC01_VC03</td>\n",
       "      <td>Total households</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>HC01_VC04</td>\n",
       "      <td>Estimate; HOUSEHOLDS BY TYPE - Total household...</td>\n",
       "      <td>Estimate</td>\n",
       "      <td>Social</td>\n",
       "      <td>HOUSEHOLDS BY TYPE</td>\n",
       "      <td>Total households - Family households (families)</td>\n",
       "      <td>02HC01_VC04</td>\n",
       "      <td>Total households</td>\n",
       "      <td>Family households (families)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>HC01_VC05</td>\n",
       "      <td>Estimate; HOUSEHOLDS BY TYPE - Total household...</td>\n",
       "      <td>Estimate</td>\n",
       "      <td>Social</td>\n",
       "      <td>HOUSEHOLDS BY TYPE</td>\n",
       "      <td>Total households - Family households (families...</td>\n",
       "      <td>02HC01_VC05</td>\n",
       "      <td>Total households</td>\n",
       "      <td>Family households (families) - With own childr...</td>\n",
       "      <td>With own children of the householder under 18 ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>HC01_VC06</td>\n",
       "      <td>Estimate; HOUSEHOLDS BY TYPE - Total household...</td>\n",
       "      <td>Estimate</td>\n",
       "      <td>Social</td>\n",
       "      <td>HOUSEHOLDS BY TYPE</td>\n",
       "      <td>Total households - Family households (families...</td>\n",
       "      <td>02HC01_VC06</td>\n",
       "      <td>Total households</td>\n",
       "      <td>Family households (families) - Married-couple ...</td>\n",
       "      <td>Married-couple family</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>HC01_VC07</td>\n",
       "      <td>Estimate; HOUSEHOLDS BY TYPE - Total household...</td>\n",
       "      <td>Estimate</td>\n",
       "      <td>Social</td>\n",
       "      <td>HOUSEHOLDS BY TYPE</td>\n",
       "      <td>Total households - Family households (families...</td>\n",
       "      <td>02HC01_VC07</td>\n",
       "      <td>Total households</td>\n",
       "      <td>Family households (families) - Married-couple ...</td>\n",
       "      <td>Married-couple family - With own children of t...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>HC01_VC08</td>\n",
       "      <td>Estimate; HOUSEHOLDS BY TYPE - Total household...</td>\n",
       "      <td>Estimate</td>\n",
       "      <td>Social</td>\n",
       "      <td>HOUSEHOLDS BY TYPE</td>\n",
       "      <td>Total households - Family households (families...</td>\n",
       "      <td>02HC01_VC08</td>\n",
       "      <td>Total households</td>\n",
       "      <td>Family households (families) - Male householde...</td>\n",
       "      <td>Male householder, no wife present, family</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>HC01_VC09</td>\n",
       "      <td>Estimate; HOUSEHOLDS BY TYPE - Total household...</td>\n",
       "      <td>Estimate</td>\n",
       "      <td>Social</td>\n",
       "      <td>HOUSEHOLDS BY TYPE</td>\n",
       "      <td>Total households - Family households (families...</td>\n",
       "      <td>02HC01_VC09</td>\n",
       "      <td>Total households</td>\n",
       "      <td>Family households (families) - Male householde...</td>\n",
       "      <td>Male householder, no wife present, family - Wi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>HC01_VC10</td>\n",
       "      <td>Estimate; HOUSEHOLDS BY TYPE - Total household...</td>\n",
       "      <td>Estimate</td>\n",
       "      <td>Social</td>\n",
       "      <td>HOUSEHOLDS BY TYPE</td>\n",
       "      <td>Total households - Family households (families...</td>\n",
       "      <td>02HC01_VC10</td>\n",
       "      <td>Total households</td>\n",
       "      <td>Family households (families) - Female househol...</td>\n",
       "      <td>Female householder, no husband present, family</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>HC01_VC11</td>\n",
       "      <td>Estimate; HOUSEHOLDS BY TYPE - Total household...</td>\n",
       "      <td>Estimate</td>\n",
       "      <td>Social</td>\n",
       "      <td>HOUSEHOLDS BY TYPE</td>\n",
       "      <td>Total households - Family households (families...</td>\n",
       "      <td>02HC01_VC11</td>\n",
       "      <td>Total households</td>\n",
       "      <td>Family households (families) - Female househol...</td>\n",
       "      <td>Female householder, no husband present, family...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>HC01_VC12</td>\n",
       "      <td>Estimate; HOUSEHOLDS BY TYPE - Total household...</td>\n",
       "      <td>Estimate</td>\n",
       "      <td>Social</td>\n",
       "      <td>HOUSEHOLDS BY TYPE</td>\n",
       "      <td>Total households - Nonfamily households</td>\n",
       "      <td>02HC01_VC12</td>\n",
       "      <td>Total households</td>\n",
       "      <td>Nonfamily households</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>HC01_VC13</td>\n",
       "      <td>Estimate; HOUSEHOLDS BY TYPE - Total household...</td>\n",
       "      <td>Estimate</td>\n",
       "      <td>Social</td>\n",
       "      <td>HOUSEHOLDS BY TYPE</td>\n",
       "      <td>Total households - Nonfamily households - Hous...</td>\n",
       "      <td>02HC01_VC13</td>\n",
       "      <td>Total households</td>\n",
       "      <td>Nonfamily households - Householder living alone</td>\n",
       "      <td>Householder living alone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>HC01_VC14</td>\n",
       "      <td>Estimate; HOUSEHOLDS BY TYPE - Total household...</td>\n",
       "      <td>Estimate</td>\n",
       "      <td>Social</td>\n",
       "      <td>HOUSEHOLDS BY TYPE</td>\n",
       "      <td>Total households - Nonfamily households - Hous...</td>\n",
       "      <td>02HC01_VC14</td>\n",
       "      <td>Total households</td>\n",
       "      <td>Nonfamily households - Householder living alon...</td>\n",
       "      <td>Householder living alone - 65 years and over</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>596</th>\n",
       "      <td>HC01_VC216</td>\n",
       "      <td>Estimate; COMPUTERS AND INTERNET USE - Total h...</td>\n",
       "      <td>Estimate</td>\n",
       "      <td>Social</td>\n",
       "      <td>COMPUTERS AND INTERNET USE</td>\n",
       "      <td>Total households</td>\n",
       "      <td>02HC01_VC216</td>\n",
       "      <td>Total households</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>600</th>\n",
       "      <td>HC01_VC217</td>\n",
       "      <td>Estimate; COMPUTERS AND INTERNET USE - Total h...</td>\n",
       "      <td>Estimate</td>\n",
       "      <td>Social</td>\n",
       "      <td>COMPUTERS AND INTERNET USE</td>\n",
       "      <td>Total households - With a computer</td>\n",
       "      <td>02HC01_VC217</td>\n",
       "      <td>Total households</td>\n",
       "      <td>With a computer</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>604</th>\n",
       "      <td>HC01_VC218</td>\n",
       "      <td>Estimate; COMPUTERS AND INTERNET USE - Total h...</td>\n",
       "      <td>Estimate</td>\n",
       "      <td>Social</td>\n",
       "      <td>COMPUTERS AND INTERNET USE</td>\n",
       "      <td>Total households - With a broadband Internet s...</td>\n",
       "      <td>02HC01_VC218</td>\n",
       "      <td>Total households</td>\n",
       "      <td>With a broadband Internet subscription</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              v                                             detail      type  \\\n",
       "0     HC01_VC03    Estimate; HOUSEHOLDS BY TYPE - Total households  Estimate   \n",
       "4     HC01_VC04  Estimate; HOUSEHOLDS BY TYPE - Total household...  Estimate   \n",
       "8     HC01_VC05  Estimate; HOUSEHOLDS BY TYPE - Total household...  Estimate   \n",
       "12    HC01_VC06  Estimate; HOUSEHOLDS BY TYPE - Total household...  Estimate   \n",
       "16    HC01_VC07  Estimate; HOUSEHOLDS BY TYPE - Total household...  Estimate   \n",
       "20    HC01_VC08  Estimate; HOUSEHOLDS BY TYPE - Total household...  Estimate   \n",
       "24    HC01_VC09  Estimate; HOUSEHOLDS BY TYPE - Total household...  Estimate   \n",
       "28    HC01_VC10  Estimate; HOUSEHOLDS BY TYPE - Total household...  Estimate   \n",
       "32    HC01_VC11  Estimate; HOUSEHOLDS BY TYPE - Total household...  Estimate   \n",
       "36    HC01_VC12  Estimate; HOUSEHOLDS BY TYPE - Total household...  Estimate   \n",
       "40    HC01_VC13  Estimate; HOUSEHOLDS BY TYPE - Total household...  Estimate   \n",
       "44    HC01_VC14  Estimate; HOUSEHOLDS BY TYPE - Total household...  Estimate   \n",
       "596  HC01_VC216  Estimate; COMPUTERS AND INTERNET USE - Total h...  Estimate   \n",
       "600  HC01_VC217  Estimate; COMPUTERS AND INTERNET USE - Total h...  Estimate   \n",
       "604  HC01_VC218  Estimate; COMPUTERS AND INTERNET USE - Total h...  Estimate   \n",
       "\n",
       "       cat0                        cat1  \\\n",
       "0    Social          HOUSEHOLDS BY TYPE   \n",
       "4    Social          HOUSEHOLDS BY TYPE   \n",
       "8    Social          HOUSEHOLDS BY TYPE   \n",
       "12   Social          HOUSEHOLDS BY TYPE   \n",
       "16   Social          HOUSEHOLDS BY TYPE   \n",
       "20   Social          HOUSEHOLDS BY TYPE   \n",
       "24   Social          HOUSEHOLDS BY TYPE   \n",
       "28   Social          HOUSEHOLDS BY TYPE   \n",
       "32   Social          HOUSEHOLDS BY TYPE   \n",
       "36   Social          HOUSEHOLDS BY TYPE   \n",
       "40   Social          HOUSEHOLDS BY TYPE   \n",
       "44   Social          HOUSEHOLDS BY TYPE   \n",
       "596  Social  COMPUTERS AND INTERNET USE   \n",
       "600  Social  COMPUTERS AND INTERNET USE   \n",
       "604  Social  COMPUTERS AND INTERNET USE   \n",
       "\n",
       "                                                  cat2         vname  \\\n",
       "0                                     Total households   02HC01_VC03   \n",
       "4      Total households - Family households (families)   02HC01_VC04   \n",
       "8    Total households - Family households (families...   02HC01_VC05   \n",
       "12   Total households - Family households (families...   02HC01_VC06   \n",
       "16   Total households - Family households (families...   02HC01_VC07   \n",
       "20   Total households - Family households (families...   02HC01_VC08   \n",
       "24   Total households - Family households (families...   02HC01_VC09   \n",
       "28   Total households - Family households (families...   02HC01_VC10   \n",
       "32   Total households - Family households (families...   02HC01_VC11   \n",
       "36             Total households - Nonfamily households   02HC01_VC12   \n",
       "40   Total households - Nonfamily households - Hous...   02HC01_VC13   \n",
       "44   Total households - Nonfamily households - Hous...   02HC01_VC14   \n",
       "596                                   Total households  02HC01_VC216   \n",
       "600                 Total households - With a computer  02HC01_VC217   \n",
       "604  Total households - With a broadband Internet s...  02HC01_VC218   \n",
       "\n",
       "               level1                                             level2  \\\n",
       "0    Total households                                                NaN   \n",
       "4    Total households                       Family households (families)   \n",
       "8    Total households  Family households (families) - With own childr...   \n",
       "12   Total households  Family households (families) - Married-couple ...   \n",
       "16   Total households  Family households (families) - Married-couple ...   \n",
       "20   Total households  Family households (families) - Male householde...   \n",
       "24   Total households  Family households (families) - Male householde...   \n",
       "28   Total households  Family households (families) - Female househol...   \n",
       "32   Total households  Family households (families) - Female househol...   \n",
       "36   Total households                               Nonfamily households   \n",
       "40   Total households    Nonfamily households - Householder living alone   \n",
       "44   Total households  Nonfamily households - Householder living alon...   \n",
       "596  Total households                                                NaN   \n",
       "600  Total households                                    With a computer   \n",
       "604  Total households             With a broadband Internet subscription   \n",
       "\n",
       "                                                level3  \n",
       "0                                                  NaN  \n",
       "4                                                  NaN  \n",
       "8    With own children of the householder under 18 ...  \n",
       "12                               Married-couple family  \n",
       "16   Married-couple family - With own children of t...  \n",
       "20           Male householder, no wife present, family  \n",
       "24   Male householder, no wife present, family - Wi...  \n",
       "28      Female householder, no husband present, family  \n",
       "32   Female householder, no husband present, family...  \n",
       "36                                                 NaN  \n",
       "40                            Householder living alone  \n",
       "44        Householder living alone - 65 years and over  \n",
       "596                                                NaN  \n",
       "600                                                NaN  \n",
       "604                                                NaN  "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_household"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(15, 10)"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_household.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "total_household = total_household[: -3]"
   ]
  },
  {
   "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>v</th>\n",
       "      <th>detail</th>\n",
       "      <th>type</th>\n",
       "      <th>cat0</th>\n",
       "      <th>cat1</th>\n",
       "      <th>cat2</th>\n",
       "      <th>vname</th>\n",
       "      <th>level1</th>\n",
       "      <th>level2</th>\n",
       "      <th>level3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>HC01_VC03</td>\n",
       "      <td>Estimate; HOUSEHOLDS BY TYPE - Total households</td>\n",
       "      <td>Estimate</td>\n",
       "      <td>Social</td>\n",
       "      <td>HOUSEHOLDS BY TYPE</td>\n",
       "      <td>Total households</td>\n",
       "      <td>02HC01_VC03</td>\n",
       "      <td>Total households</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>HC01_VC04</td>\n",
       "      <td>Estimate; HOUSEHOLDS BY TYPE - Total household...</td>\n",
       "      <td>Estimate</td>\n",
       "      <td>Social</td>\n",
       "      <td>HOUSEHOLDS BY TYPE</td>\n",
       "      <td>Total households - Family households (families)</td>\n",
       "      <td>02HC01_VC04</td>\n",
       "      <td>Total households</td>\n",
       "      <td>Family households (families)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>HC01_VC05</td>\n",
       "      <td>Estimate; HOUSEHOLDS BY TYPE - Total household...</td>\n",
       "      <td>Estimate</td>\n",
       "      <td>Social</td>\n",
       "      <td>HOUSEHOLDS BY TYPE</td>\n",
       "      <td>Total households - Family households (families...</td>\n",
       "      <td>02HC01_VC05</td>\n",
       "      <td>Total households</td>\n",
       "      <td>Family households (families) - With own childr...</td>\n",
       "      <td>With own children of the householder under 18 ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>HC01_VC06</td>\n",
       "      <td>Estimate; HOUSEHOLDS BY TYPE - Total household...</td>\n",
       "      <td>Estimate</td>\n",
       "      <td>Social</td>\n",
       "      <td>HOUSEHOLDS BY TYPE</td>\n",
       "      <td>Total households - Family households (families...</td>\n",
       "      <td>02HC01_VC06</td>\n",
       "      <td>Total households</td>\n",
       "      <td>Family households (families) - Married-couple ...</td>\n",
       "      <td>Married-couple family</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>HC01_VC07</td>\n",
       "      <td>Estimate; HOUSEHOLDS BY TYPE - Total household...</td>\n",
       "      <td>Estimate</td>\n",
       "      <td>Social</td>\n",
       "      <td>HOUSEHOLDS BY TYPE</td>\n",
       "      <td>Total households - Family households (families...</td>\n",
       "      <td>02HC01_VC07</td>\n",
       "      <td>Total households</td>\n",
       "      <td>Family households (families) - Married-couple ...</td>\n",
       "      <td>Married-couple family - With own children of t...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>HC01_VC08</td>\n",
       "      <td>Estimate; HOUSEHOLDS BY TYPE - Total household...</td>\n",
       "      <td>Estimate</td>\n",
       "      <td>Social</td>\n",
       "      <td>HOUSEHOLDS BY TYPE</td>\n",
       "      <td>Total households - Family households (families...</td>\n",
       "      <td>02HC01_VC08</td>\n",
       "      <td>Total households</td>\n",
       "      <td>Family households (families) - Male householde...</td>\n",
       "      <td>Male householder, no wife present, family</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>HC01_VC09</td>\n",
       "      <td>Estimate; HOUSEHOLDS BY TYPE - Total household...</td>\n",
       "      <td>Estimate</td>\n",
       "      <td>Social</td>\n",
       "      <td>HOUSEHOLDS BY TYPE</td>\n",
       "      <td>Total households - Family households (families...</td>\n",
       "      <td>02HC01_VC09</td>\n",
       "      <td>Total households</td>\n",
       "      <td>Family households (families) - Male householde...</td>\n",
       "      <td>Male householder, no wife present, family - Wi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>HC01_VC10</td>\n",
       "      <td>Estimate; HOUSEHOLDS BY TYPE - Total household...</td>\n",
       "      <td>Estimate</td>\n",
       "      <td>Social</td>\n",
       "      <td>HOUSEHOLDS BY TYPE</td>\n",
       "      <td>Total households - Family households (families...</td>\n",
       "      <td>02HC01_VC10</td>\n",
       "      <td>Total households</td>\n",
       "      <td>Family households (families) - Female househol...</td>\n",
       "      <td>Female householder, no husband present, family</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>HC01_VC11</td>\n",
       "      <td>Estimate; HOUSEHOLDS BY TYPE - Total household...</td>\n",
       "      <td>Estimate</td>\n",
       "      <td>Social</td>\n",
       "      <td>HOUSEHOLDS BY TYPE</td>\n",
       "      <td>Total households - Family households (families...</td>\n",
       "      <td>02HC01_VC11</td>\n",
       "      <td>Total households</td>\n",
       "      <td>Family households (families) - Female househol...</td>\n",
       "      <td>Female householder, no husband present, family...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>HC01_VC12</td>\n",
       "      <td>Estimate; HOUSEHOLDS BY TYPE - Total household...</td>\n",
       "      <td>Estimate</td>\n",
       "      <td>Social</td>\n",
       "      <td>HOUSEHOLDS BY TYPE</td>\n",
       "      <td>Total households - Nonfamily households</td>\n",
       "      <td>02HC01_VC12</td>\n",
       "      <td>Total households</td>\n",
       "      <td>Nonfamily households</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>HC01_VC13</td>\n",
       "      <td>Estimate; HOUSEHOLDS BY TYPE - Total household...</td>\n",
       "      <td>Estimate</td>\n",
       "      <td>Social</td>\n",
       "      <td>HOUSEHOLDS BY TYPE</td>\n",
       "      <td>Total households - Nonfamily households - Hous...</td>\n",
       "      <td>02HC01_VC13</td>\n",
       "      <td>Total households</td>\n",
       "      <td>Nonfamily households - Householder living alone</td>\n",
       "      <td>Householder living alone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>HC01_VC14</td>\n",
       "      <td>Estimate; HOUSEHOLDS BY TYPE - Total household...</td>\n",
       "      <td>Estimate</td>\n",
       "      <td>Social</td>\n",
       "      <td>HOUSEHOLDS BY TYPE</td>\n",
       "      <td>Total households - Nonfamily households - Hous...</td>\n",
       "      <td>02HC01_VC14</td>\n",
       "      <td>Total households</td>\n",
       "      <td>Nonfamily households - Householder living alon...</td>\n",
       "      <td>Householder living alone - 65 years and over</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            v                                             detail      type  \\\n",
       "0   HC01_VC03    Estimate; HOUSEHOLDS BY TYPE - Total households  Estimate   \n",
       "4   HC01_VC04  Estimate; HOUSEHOLDS BY TYPE - Total household...  Estimate   \n",
       "8   HC01_VC05  Estimate; HOUSEHOLDS BY TYPE - Total household...  Estimate   \n",
       "12  HC01_VC06  Estimate; HOUSEHOLDS BY TYPE - Total household...  Estimate   \n",
       "16  HC01_VC07  Estimate; HOUSEHOLDS BY TYPE - Total household...  Estimate   \n",
       "20  HC01_VC08  Estimate; HOUSEHOLDS BY TYPE - Total household...  Estimate   \n",
       "24  HC01_VC09  Estimate; HOUSEHOLDS BY TYPE - Total household...  Estimate   \n",
       "28  HC01_VC10  Estimate; HOUSEHOLDS BY TYPE - Total household...  Estimate   \n",
       "32  HC01_VC11  Estimate; HOUSEHOLDS BY TYPE - Total household...  Estimate   \n",
       "36  HC01_VC12  Estimate; HOUSEHOLDS BY TYPE - Total household...  Estimate   \n",
       "40  HC01_VC13  Estimate; HOUSEHOLDS BY TYPE - Total household...  Estimate   \n",
       "44  HC01_VC14  Estimate; HOUSEHOLDS BY TYPE - Total household...  Estimate   \n",
       "\n",
       "      cat0                cat1  \\\n",
       "0   Social  HOUSEHOLDS BY TYPE   \n",
       "4   Social  HOUSEHOLDS BY TYPE   \n",
       "8   Social  HOUSEHOLDS BY TYPE   \n",
       "12  Social  HOUSEHOLDS BY TYPE   \n",
       "16  Social  HOUSEHOLDS BY TYPE   \n",
       "20  Social  HOUSEHOLDS BY TYPE   \n",
       "24  Social  HOUSEHOLDS BY TYPE   \n",
       "28  Social  HOUSEHOLDS BY TYPE   \n",
       "32  Social  HOUSEHOLDS BY TYPE   \n",
       "36  Social  HOUSEHOLDS BY TYPE   \n",
       "40  Social  HOUSEHOLDS BY TYPE   \n",
       "44  Social  HOUSEHOLDS BY TYPE   \n",
       "\n",
       "                                                 cat2        vname  \\\n",
       "0                                    Total households  02HC01_VC03   \n",
       "4     Total households - Family households (families)  02HC01_VC04   \n",
       "8   Total households - Family households (families...  02HC01_VC05   \n",
       "12  Total households - Family households (families...  02HC01_VC06   \n",
       "16  Total households - Family households (families...  02HC01_VC07   \n",
       "20  Total households - Family households (families...  02HC01_VC08   \n",
       "24  Total households - Family households (families...  02HC01_VC09   \n",
       "28  Total households - Family households (families...  02HC01_VC10   \n",
       "32  Total households - Family households (families...  02HC01_VC11   \n",
       "36            Total households - Nonfamily households  02HC01_VC12   \n",
       "40  Total households - Nonfamily households - Hous...  02HC01_VC13   \n",
       "44  Total households - Nonfamily households - Hous...  02HC01_VC14   \n",
       "\n",
       "              level1                                             level2  \\\n",
       "0   Total households                                                NaN   \n",
       "4   Total households                       Family households (families)   \n",
       "8   Total households  Family households (families) - With own childr...   \n",
       "12  Total households  Family households (families) - Married-couple ...   \n",
       "16  Total households  Family households (families) - Married-couple ...   \n",
       "20  Total households  Family households (families) - Male householde...   \n",
       "24  Total households  Family households (families) - Male householde...   \n",
       "28  Total households  Family households (families) - Female househol...   \n",
       "32  Total households  Family households (families) - Female househol...   \n",
       "36  Total households                               Nonfamily households   \n",
       "40  Total households    Nonfamily households - Householder living alone   \n",
       "44  Total households  Nonfamily households - Householder living alon...   \n",
       "\n",
       "                                               level3  \n",
       "0                                                 NaN  \n",
       "4                                                 NaN  \n",
       "8   With own children of the householder under 18 ...  \n",
       "12                              Married-couple family  \n",
       "16  Married-couple family - With own children of t...  \n",
       "20          Male householder, no wife present, family  \n",
       "24  Male householder, no wife present, family - Wi...  \n",
       "28     Female householder, no husband present, family  \n",
       "32  Female householder, no husband present, family...  \n",
       "36                                                NaN  \n",
       "40                           Householder living alone  \n",
       "44       Householder living alone - 65 years and over  "
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_household"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "household_list = total_household[\"vname\"].tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['02HC01_VC03',\n",
       " '02HC01_VC04',\n",
       " '02HC01_VC05',\n",
       " '02HC01_VC06',\n",
       " '02HC01_VC07',\n",
       " '02HC01_VC08',\n",
       " '02HC01_VC09',\n",
       " '02HC01_VC10',\n",
       " '02HC01_VC11',\n",
       " '02HC01_VC12',\n",
       " '02HC01_VC13',\n",
       " '02HC01_VC14']"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "household_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_cols = ['zipcode', 'latitude', 'longitude', 'city', 'state', 'county', 'fips', '02HC01_VC03', '02HC01_VC04', '02HC01_VC05']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_th = df[new_cols].fillna(0)\n",
    "df_th = df_th[(df_th != 0).all(1)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "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>zipcode</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>county</th>\n",
       "      <th>fips</th>\n",
       "      <th>02HC01_VC03</th>\n",
       "      <th>02HC01_VC04</th>\n",
       "      <th>02HC01_VC05</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>356</th>\n",
       "      <td>1001</td>\n",
       "      <td>42.140549</td>\n",
       "      <td>-72.788661</td>\n",
       "      <td>Agawam</td>\n",
       "      <td>MA</td>\n",
       "      <td>Hampden</td>\n",
       "      <td>25013.0</td>\n",
       "      <td>7190.0</td>\n",
       "      <td>3943.0</td>\n",
       "      <td>1406.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>357</th>\n",
       "      <td>1002</td>\n",
       "      <td>42.367092</td>\n",
       "      <td>-72.464571</td>\n",
       "      <td>Amherst</td>\n",
       "      <td>MA</td>\n",
       "      <td>Hampshire</td>\n",
       "      <td>25011.0</td>\n",
       "      <td>9561.0</td>\n",
       "      <td>4626.0</td>\n",
       "      <td>2068.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>358</th>\n",
       "      <td>1002</td>\n",
       "      <td>42.367092</td>\n",
       "      <td>-72.464571</td>\n",
       "      <td>Amherst</td>\n",
       "      <td>MA</td>\n",
       "      <td>Hampshire</td>\n",
       "      <td>25015.0</td>\n",
       "      <td>9561.0</td>\n",
       "      <td>4626.0</td>\n",
       "      <td>2068.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>361</th>\n",
       "      <td>1005</td>\n",
       "      <td>42.329160</td>\n",
       "      <td>-72.139465</td>\n",
       "      <td>Barre</td>\n",
       "      <td>MA</td>\n",
       "      <td>Worcester</td>\n",
       "      <td>25027.0</td>\n",
       "      <td>1840.0</td>\n",
       "      <td>1150.0</td>\n",
       "      <td>462.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>362</th>\n",
       "      <td>1007</td>\n",
       "      <td>42.280267</td>\n",
       "      <td>-72.402056</td>\n",
       "      <td>Belchertown</td>\n",
       "      <td>MA</td>\n",
       "      <td>Hampshire</td>\n",
       "      <td>25015.0</td>\n",
       "      <td>5611.0</td>\n",
       "      <td>4010.0</td>\n",
       "      <td>1882.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     zipcode   latitude  longitude         city state     county     fips  \\\n",
       "356     1001  42.140549 -72.788661       Agawam    MA    Hampden  25013.0   \n",
       "357     1002  42.367092 -72.464571      Amherst    MA  Hampshire  25011.0   \n",
       "358     1002  42.367092 -72.464571      Amherst    MA  Hampshire  25015.0   \n",
       "361     1005  42.329160 -72.139465        Barre    MA  Worcester  25027.0   \n",
       "362     1007  42.280267 -72.402056  Belchertown    MA  Hampshire  25015.0   \n",
       "\n",
       "     02HC01_VC03  02HC01_VC04  02HC01_VC05  \n",
       "356       7190.0       3943.0       1406.0  \n",
       "357       9561.0       4626.0       2068.0  \n",
       "358       9561.0       4626.0       2068.0  \n",
       "361       1840.0       1150.0        462.0  \n",
       "362       5611.0       4010.0       1882.0  "
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_th.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "# save df to csv\n",
    "df_th.to_csv(\"./data/df_th.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "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>zipcode</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>county</th>\n",
       "      <th>fips</th>\n",
       "      <th>02HC01_VC03</th>\n",
       "      <th>02HC01_VC04</th>\n",
       "      <th>02HC01_VC05</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>356</th>\n",
       "      <td>1001</td>\n",
       "      <td>42.140549</td>\n",
       "      <td>-72.788661</td>\n",
       "      <td>Agawam</td>\n",
       "      <td>MA</td>\n",
       "      <td>Hampden</td>\n",
       "      <td>25013.0</td>\n",
       "      <td>7190.0</td>\n",
       "      <td>3943.0</td>\n",
       "      <td>1406.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>357</th>\n",
       "      <td>1002</td>\n",
       "      <td>42.367092</td>\n",
       "      <td>-72.464571</td>\n",
       "      <td>Amherst</td>\n",
       "      <td>MA</td>\n",
       "      <td>Hampshire</td>\n",
       "      <td>25011.0</td>\n",
       "      <td>9561.0</td>\n",
       "      <td>4626.0</td>\n",
       "      <td>2068.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>358</th>\n",
       "      <td>1002</td>\n",
       "      <td>42.367092</td>\n",
       "      <td>-72.464571</td>\n",
       "      <td>Amherst</td>\n",
       "      <td>MA</td>\n",
       "      <td>Hampshire</td>\n",
       "      <td>25015.0</td>\n",
       "      <td>9561.0</td>\n",
       "      <td>4626.0</td>\n",
       "      <td>2068.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>361</th>\n",
       "      <td>1005</td>\n",
       "      <td>42.329160</td>\n",
       "      <td>-72.139465</td>\n",
       "      <td>Barre</td>\n",
       "      <td>MA</td>\n",
       "      <td>Worcester</td>\n",
       "      <td>25027.0</td>\n",
       "      <td>1840.0</td>\n",
       "      <td>1150.0</td>\n",
       "      <td>462.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>362</th>\n",
       "      <td>1007</td>\n",
       "      <td>42.280267</td>\n",
       "      <td>-72.402056</td>\n",
       "      <td>Belchertown</td>\n",
       "      <td>MA</td>\n",
       "      <td>Hampshire</td>\n",
       "      <td>25015.0</td>\n",
       "      <td>5611.0</td>\n",
       "      <td>4010.0</td>\n",
       "      <td>1882.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>363</th>\n",
       "      <td>1008</td>\n",
       "      <td>42.177833</td>\n",
       "      <td>-72.958359</td>\n",
       "      <td>Blandford</td>\n",
       "      <td>MA</td>\n",
       "      <td>Hampden</td>\n",
       "      <td>25003.0</td>\n",
       "      <td>530.0</td>\n",
       "      <td>366.0</td>\n",
       "      <td>108.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>364</th>\n",
       "      <td>1008</td>\n",
       "      <td>42.177833</td>\n",
       "      <td>-72.958359</td>\n",
       "      <td>Blandford</td>\n",
       "      <td>MA</td>\n",
       "      <td>Hampden</td>\n",
       "      <td>25013.0</td>\n",
       "      <td>530.0</td>\n",
       "      <td>366.0</td>\n",
       "      <td>108.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>365</th>\n",
       "      <td>1009</td>\n",
       "      <td>42.206092</td>\n",
       "      <td>-72.340486</td>\n",
       "      <td>Bondsville</td>\n",
       "      <td>MA</td>\n",
       "      <td>Hampden</td>\n",
       "      <td>25013.0</td>\n",
       "      <td>106.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>366</th>\n",
       "      <td>1010</td>\n",
       "      <td>42.108585</td>\n",
       "      <td>-72.204480</td>\n",
       "      <td>Brimfield</td>\n",
       "      <td>MA</td>\n",
       "      <td>Hampden</td>\n",
       "      <td>25013.0</td>\n",
       "      <td>1494.0</td>\n",
       "      <td>1026.0</td>\n",
       "      <td>369.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>367</th>\n",
       "      <td>1010</td>\n",
       "      <td>42.108585</td>\n",
       "      <td>-72.204480</td>\n",
       "      <td>Brimfield</td>\n",
       "      <td>MA</td>\n",
       "      <td>Hampden</td>\n",
       "      <td>25027.0</td>\n",
       "      <td>1494.0</td>\n",
       "      <td>1026.0</td>\n",
       "      <td>369.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>368</th>\n",
       "      <td>1011</td>\n",
       "      <td>42.294259</td>\n",
       "      <td>-72.952776</td>\n",
       "      <td>Chester</td>\n",
       "      <td>MA</td>\n",
       "      <td>Hampden</td>\n",
       "      <td>25003.0</td>\n",
       "      <td>552.0</td>\n",
       "      <td>416.0</td>\n",
       "      <td>137.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>369</th>\n",
       "      <td>1011</td>\n",
       "      <td>42.294259</td>\n",
       "      <td>-72.952776</td>\n",
       "      <td>Chester</td>\n",
       "      <td>MA</td>\n",
       "      <td>Hampden</td>\n",
       "      <td>25013.0</td>\n",
       "      <td>552.0</td>\n",
       "      <td>416.0</td>\n",
       "      <td>137.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>370</th>\n",
       "      <td>1011</td>\n",
       "      <td>42.294259</td>\n",
       "      <td>-72.952776</td>\n",
       "      <td>Chester</td>\n",
       "      <td>MA</td>\n",
       "      <td>Hampden</td>\n",
       "      <td>25015.0</td>\n",
       "      <td>552.0</td>\n",
       "      <td>416.0</td>\n",
       "      <td>137.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>371</th>\n",
       "      <td>1012</td>\n",
       "      <td>42.392274</td>\n",
       "      <td>-72.825607</td>\n",
       "      <td>Chesterfield</td>\n",
       "      <td>MA</td>\n",
       "      <td>Hampshire</td>\n",
       "      <td>25015.0</td>\n",
       "      <td>243.0</td>\n",
       "      <td>155.0</td>\n",
       "      <td>58.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>372</th>\n",
       "      <td>1013</td>\n",
       "      <td>42.161492</td>\n",
       "      <td>-72.667341</td>\n",
       "      <td>Chicopee</td>\n",
       "      <td>MA</td>\n",
       "      <td>Hampden</td>\n",
       "      <td>25013.0</td>\n",
       "      <td>9302.0</td>\n",
       "      <td>5531.0</td>\n",
       "      <td>2767.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>374</th>\n",
       "      <td>1020</td>\n",
       "      <td>42.177492</td>\n",
       "      <td>-72.562563</td>\n",
       "      <td>Chicopee</td>\n",
       "      <td>MA</td>\n",
       "      <td>Hampden</td>\n",
       "      <td>25013.0</td>\n",
       "      <td>12482.0</td>\n",
       "      <td>8058.0</td>\n",
       "      <td>2752.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>376</th>\n",
       "      <td>1022</td>\n",
       "      <td>42.193392</td>\n",
       "      <td>-72.554360</td>\n",
       "      <td>Chicopee</td>\n",
       "      <td>MA</td>\n",
       "      <td>Hampden</td>\n",
       "      <td>25013.0</td>\n",
       "      <td>1204.0</td>\n",
       "      <td>383.0</td>\n",
       "      <td>86.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>377</th>\n",
       "      <td>1026</td>\n",
       "      <td>42.428617</td>\n",
       "      <td>-72.909841</td>\n",
       "      <td>Cummington</td>\n",
       "      <td>MA</td>\n",
       "      <td>Hampshire</td>\n",
       "      <td>25003.0</td>\n",
       "      <td>455.0</td>\n",
       "      <td>276.0</td>\n",
       "      <td>114.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>378</th>\n",
       "      <td>1026</td>\n",
       "      <td>42.428617</td>\n",
       "      <td>-72.909841</td>\n",
       "      <td>Cummington</td>\n",
       "      <td>MA</td>\n",
       "      <td>Hampshire</td>\n",
       "      <td>25015.0</td>\n",
       "      <td>455.0</td>\n",
       "      <td>276.0</td>\n",
       "      <td>114.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>379</th>\n",
       "      <td>1027</td>\n",
       "      <td>42.368303</td>\n",
       "      <td>-72.768839</td>\n",
       "      <td>Easthampton</td>\n",
       "      <td>MA</td>\n",
       "      <td>Hampshire</td>\n",
       "      <td>25015.0</td>\n",
       "      <td>7883.0</td>\n",
       "      <td>4594.0</td>\n",
       "      <td>1616.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     zipcode   latitude  longitude          city state     county     fips  \\\n",
       "356     1001  42.140549 -72.788661        Agawam    MA    Hampden  25013.0   \n",
       "357     1002  42.367092 -72.464571       Amherst    MA  Hampshire  25011.0   \n",
       "358     1002  42.367092 -72.464571       Amherst    MA  Hampshire  25015.0   \n",
       "361     1005  42.329160 -72.139465         Barre    MA  Worcester  25027.0   \n",
       "362     1007  42.280267 -72.402056   Belchertown    MA  Hampshire  25015.0   \n",
       "363     1008  42.177833 -72.958359     Blandford    MA    Hampden  25003.0   \n",
       "364     1008  42.177833 -72.958359     Blandford    MA    Hampden  25013.0   \n",
       "365     1009  42.206092 -72.340486    Bondsville    MA    Hampden  25013.0   \n",
       "366     1010  42.108585 -72.204480     Brimfield    MA    Hampden  25013.0   \n",
       "367     1010  42.108585 -72.204480     Brimfield    MA    Hampden  25027.0   \n",
       "368     1011  42.294259 -72.952776       Chester    MA    Hampden  25003.0   \n",
       "369     1011  42.294259 -72.952776       Chester    MA    Hampden  25013.0   \n",
       "370     1011  42.294259 -72.952776       Chester    MA    Hampden  25015.0   \n",
       "371     1012  42.392274 -72.825607  Chesterfield    MA  Hampshire  25015.0   \n",
       "372     1013  42.161492 -72.667341      Chicopee    MA    Hampden  25013.0   \n",
       "374     1020  42.177492 -72.562563      Chicopee    MA    Hampden  25013.0   \n",
       "376     1022  42.193392 -72.554360      Chicopee    MA    Hampden  25013.0   \n",
       "377     1026  42.428617 -72.909841    Cummington    MA  Hampshire  25003.0   \n",
       "378     1026  42.428617 -72.909841    Cummington    MA  Hampshire  25015.0   \n",
       "379     1027  42.368303 -72.768839   Easthampton    MA  Hampshire  25015.0   \n",
       "\n",
       "     02HC01_VC03  02HC01_VC04  02HC01_VC05  \n",
       "356       7190.0       3943.0       1406.0  \n",
       "357       9561.0       4626.0       2068.0  \n",
       "358       9561.0       4626.0       2068.0  \n",
       "361       1840.0       1150.0        462.0  \n",
       "362       5611.0       4010.0       1882.0  \n",
       "363        530.0        366.0        108.0  \n",
       "364        530.0        366.0        108.0  \n",
       "365        106.0         80.0         12.0  \n",
       "366       1494.0       1026.0        369.0  \n",
       "367       1494.0       1026.0        369.0  \n",
       "368        552.0        416.0        137.0  \n",
       "369        552.0        416.0        137.0  \n",
       "370        552.0        416.0        137.0  \n",
       "371        243.0        155.0         58.0  \n",
       "372       9302.0       5531.0       2767.0  \n",
       "374      12482.0       8058.0       2752.0  \n",
       "376       1204.0        383.0         86.0  \n",
       "377        455.0        276.0        114.0  \n",
       "378        455.0        276.0        114.0  \n",
       "379       7883.0       4594.0       1616.0  "
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_th.iloc[0:20]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Total households',\n",
       " 'Total households - Family households (families)',\n",
       " 'Total households - Family households (families) - With own children of the householder under 18 years',\n",
       " 'Total households - Family households (families) - Married-couple family',\n",
       " 'Total households - Family households (families) - Married-couple family - With own children of the householder under 18 years',\n",
       " 'Total households - Family households (families) - Male householder, no wife present, family',\n",
       " 'Total households - Family households (families) - Male householder, no wife present, family - With own children of the householder under 18 years',\n",
       " 'Total households - Family households (families) - Female householder, no husband present, family',\n",
       " 'Total households - Family households (families) - Female householder, no husband present, family - With own children of the householder under 18 years',\n",
       " 'Total households - Nonfamily households',\n",
       " 'Total households - Nonfamily households - Householder living alone',\n",
       " 'Total households - Nonfamily households - Householder living alone - 65 years and over']"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "household_list1 = total_household[\"cat2\"].tolist()\n",
    "household_list1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:city-dash]",
   "language": "python",
   "name": "conda-env-city-dash-py"
  },
  "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.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}