Census_Demo.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Dependencies\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import requests\n",
    "from census import Census\n",
    "\n",
    "# Census API Key\n",
    "#from config import api_key\n",
    "c = Census('d71cbb5b9686d70681b05daa33b6ea1e10dc3e40', year=2013)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "52\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Population</th>\n",
       "      <th>Poverty Rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>4799277.0</td>\n",
       "      <td>18.140878</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>720316.0</td>\n",
       "      <td>9.650487</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>6479703.0</td>\n",
       "      <td>17.468409</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Arkansas</td>\n",
       "      <td>2933369.0</td>\n",
       "      <td>18.658682</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>California</td>\n",
       "      <td>37659181.0</td>\n",
       "      <td>15.628107</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Name  Population  Poverty Rate\n",
       "0     Alabama   4799277.0     18.140878\n",
       "1      Alaska    720316.0      9.650487\n",
       "2     Arizona   6479703.0     17.468409\n",
       "3    Arkansas   2933369.0     18.658682\n",
       "4  California  37659181.0     15.628107"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Run Census Search to retrieve data on all zip codes (2013 ACS5 Census)\n",
    "# See: https://github.com/CommerceDataService/census-wrapper for library documentation\n",
    "# See: https://gist.github.com/afhaque/60558290d6efd892351c4b64e5c01e9b for labels\n",
    "census_data = c.acs5.get((\"NAME\", \"B19013_001E\", \"B01003_001E\", \"B01002_001E\",\n",
    "                          \"B19301_001E\",\n",
    "                          \"B17001_002E\"), {'for': 'state:*'})\n",
    "\n",
    "# Convert to DataFrame\n",
    "census_pd = pd.DataFrame(census_data)\n",
    "\n",
    "# Column Reordering\n",
    "census_pd = census_pd.rename(columns={\"B01003_001E\": \"Population\",\n",
    "                                      \"B01002_001E\": \"Median Age\",\n",
    "                                      \"B19013_001E\": \"Household Income\",\n",
    "                                      \"B19301_001E\": \"Per Capita Income\",\n",
    "                                      \"B17001_002E\": \"Poverty Count\",\n",
    "                                      \"NAME\": \"Name\", \n",
    "                                      \"state tabulation area\": \"state\"})\n",
    "\n",
    "# Add in Poverty Rate (Poverty Count / Population)\n",
    "census_pd[\"Poverty Rate\"] = 100 * \\\n",
    "    census_pd[\"Poverty Count\"].astype(int) / census_pd[\"Population\"].astype(int)\n",
    "\n",
    "# Final DataFrame\n",
    "census_pd = census_pd[[\"Name\", \"Population\", \"Poverty Rate\"]]\n",
    "\n",
    "# Visualize\n",
    "print(len(census_pd))\n",
    "census_pd.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save as a csv\n",
    "# Note to avoid any issues later, use encoding=\"utf-8\"\n",
    "census_pd.to_csv(\"census_data.csv\", encoding=\"utf-8\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>dateAdded</th>\n",
       "      <th>dateUpdated</th>\n",
       "      <th>address</th>\n",
       "      <th>city</th>\n",
       "      <th>country</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>postalCode</th>\n",
       "      <th>province</th>\n",
       "      <th>rName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016-03-29T05:06:36Z</td>\n",
       "      <td>2018-06-26T02:59:52Z</td>\n",
       "      <td>206 Wears Valley Rd</td>\n",
       "      <td>Pigeon Forge</td>\n",
       "      <td>US</td>\n",
       "      <td>35.803788</td>\n",
       "      <td>-83.580553</td>\n",
       "      <td>37863</td>\n",
       "      <td>TN</td>\n",
       "      <td>Taco Bell</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015-10-23T23:59:49Z</td>\n",
       "      <td>2018-06-26T02:59:43Z</td>\n",
       "      <td>9768 Grand River Ave</td>\n",
       "      <td>Detroit</td>\n",
       "      <td>US</td>\n",
       "      <td>42.368823</td>\n",
       "      <td>-83.138251</td>\n",
       "      <td>48204</td>\n",
       "      <td>MI</td>\n",
       "      <td>Wendys</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015-09-21T07:47:08Z</td>\n",
       "      <td>2018-06-26T02:59:43Z</td>\n",
       "      <td>13600 W McNichols Rd</td>\n",
       "      <td>Detroit</td>\n",
       "      <td>US</td>\n",
       "      <td>42.416819</td>\n",
       "      <td>-83.180444</td>\n",
       "      <td>48235</td>\n",
       "      <td>MI</td>\n",
       "      <td>Burger King</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017-06-30T23:04:32Z</td>\n",
       "      <td>2018-06-25T09:29:45Z</td>\n",
       "      <td>1224 State St</td>\n",
       "      <td>Schenectady</td>\n",
       "      <td>US</td>\n",
       "      <td>42.797920</td>\n",
       "      <td>-73.924364</td>\n",
       "      <td>12304</td>\n",
       "      <td>NY</td>\n",
       "      <td>McDonalds</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017-06-29T02:03:33Z</td>\n",
       "      <td>2018-06-25T09:29:45Z</td>\n",
       "      <td>1232 Ulster Ave</td>\n",
       "      <td>Kingston</td>\n",
       "      <td>US</td>\n",
       "      <td>41.967079</td>\n",
       "      <td>-73.988583</td>\n",
       "      <td>12401</td>\n",
       "      <td>NY</td>\n",
       "      <td>McDonalds</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4905</th>\n",
       "      <td>2016-05-23T07:37:37Z</td>\n",
       "      <td>2018-04-07T23:40:02Z</td>\n",
       "      <td>8390 N Stemmons Fwy</td>\n",
       "      <td>Dallas</td>\n",
       "      <td>US</td>\n",
       "      <td>32.826350</td>\n",
       "      <td>-96.871290</td>\n",
       "      <td>75247</td>\n",
       "      <td>TX</td>\n",
       "      <td>McDonalds</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4906</th>\n",
       "      <td>2016-03-24T09:44:33Z</td>\n",
       "      <td>2018-04-07T23:40:01Z</td>\n",
       "      <td>7007 W Wheatland Rd</td>\n",
       "      <td>Dallas</td>\n",
       "      <td>US</td>\n",
       "      <td>32.647333</td>\n",
       "      <td>-96.943384</td>\n",
       "      <td>75249</td>\n",
       "      <td>TX</td>\n",
       "      <td>McDonalds</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4907</th>\n",
       "      <td>2017-06-23T05:54:25Z</td>\n",
       "      <td>2018-04-07T23:39:57Z</td>\n",
       "      <td>542 Tecumseh St</td>\n",
       "      <td>Dundee</td>\n",
       "      <td>US</td>\n",
       "      <td>41.959490</td>\n",
       "      <td>-83.668100</td>\n",
       "      <td>48131</td>\n",
       "      <td>MI</td>\n",
       "      <td>McDonalds</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4908</th>\n",
       "      <td>2017-06-19T14:51:08Z</td>\n",
       "      <td>2018-04-07T23:39:53Z</td>\n",
       "      <td>401 W Channel Islands Blvd</td>\n",
       "      <td>Port Hueneme</td>\n",
       "      <td>US</td>\n",
       "      <td>34.176370</td>\n",
       "      <td>-119.208850</td>\n",
       "      <td>93041</td>\n",
       "      <td>CA</td>\n",
       "      <td>McDonalds</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4909</th>\n",
       "      <td>2015-11-02T15:34:13Z</td>\n",
       "      <td>2018-04-07T23:39:50Z</td>\n",
       "      <td>1741 Pacific Ave</td>\n",
       "      <td>Oxnard</td>\n",
       "      <td>US</td>\n",
       "      <td>34.180630</td>\n",
       "      <td>-119.164980</td>\n",
       "      <td>93033</td>\n",
       "      <td>CA</td>\n",
       "      <td>McDonalds</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4910 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 dateAdded           dateUpdated                     address  \\\n",
       "0     2016-03-29T05:06:36Z  2018-06-26T02:59:52Z         206 Wears Valley Rd   \n",
       "1     2015-10-23T23:59:49Z  2018-06-26T02:59:43Z        9768 Grand River Ave   \n",
       "2     2015-09-21T07:47:08Z  2018-06-26T02:59:43Z        13600 W McNichols Rd   \n",
       "3     2017-06-30T23:04:32Z  2018-06-25T09:29:45Z               1224 State St   \n",
       "4     2017-06-29T02:03:33Z  2018-06-25T09:29:45Z             1232 Ulster Ave   \n",
       "...                    ...                   ...                         ...   \n",
       "4905  2016-05-23T07:37:37Z  2018-04-07T23:40:02Z         8390 N Stemmons Fwy   \n",
       "4906  2016-03-24T09:44:33Z  2018-04-07T23:40:01Z         7007 W Wheatland Rd   \n",
       "4907  2017-06-23T05:54:25Z  2018-04-07T23:39:57Z             542 Tecumseh St   \n",
       "4908  2017-06-19T14:51:08Z  2018-04-07T23:39:53Z  401 W Channel Islands Blvd   \n",
       "4909  2015-11-02T15:34:13Z  2018-04-07T23:39:50Z            1741 Pacific Ave   \n",
       "\n",
       "              city country   latitude   longitude  postalCode province  \\\n",
       "0     Pigeon Forge      US  35.803788  -83.580553       37863       TN   \n",
       "1          Detroit      US  42.368823  -83.138251       48204       MI   \n",
       "2          Detroit      US  42.416819  -83.180444       48235       MI   \n",
       "3      Schenectady      US  42.797920  -73.924364       12304       NY   \n",
       "4         Kingston      US  41.967079  -73.988583       12401       NY   \n",
       "...            ...     ...        ...         ...         ...      ...   \n",
       "4905        Dallas      US  32.826350  -96.871290       75247       TX   \n",
       "4906        Dallas      US  32.647333  -96.943384       75249       TX   \n",
       "4907        Dundee      US  41.959490  -83.668100       48131       MI   \n",
       "4908  Port Hueneme      US  34.176370 -119.208850       93041       CA   \n",
       "4909        Oxnard      US  34.180630 -119.164980       93033       CA   \n",
       "\n",
       "            rName  \n",
       "0       Taco Bell  \n",
       "1          Wendys  \n",
       "2     Burger King  \n",
       "3       McDonalds  \n",
       "4       McDonalds  \n",
       "...           ...  \n",
       "4905    McDonalds  \n",
       "4906    McDonalds  \n",
       "4907    McDonalds  \n",
       "4908    McDonalds  \n",
       "4909    McDonalds  \n",
       "\n",
       "[4910 rows x 10 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ff = pd.read_csv(\"datatoo.csv\")\n",
    "ff"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "# by_state = ff.groupby(['province', \"rName\"])['rName'].count()\n",
    "# by_state.head(20)\n",
    "# wendys = ff.loc[ff[\"rName\"] == \"Wendys\"]\n",
    "# w = wendys.groupby(\"province\")['rName'].count()\n",
    "# wendys = ff.loc[ff[\"rName\"] == \"Wendys\"]\n",
    "# w = wendys.groupby(\"province\")['rName'].count()\n",
    "# wendysrest = ff.loc[ff[\"rName\"] == \"Wendys\"]\n",
    "# w = wendys.groupby(\"province\")['rName'].count()\n",
    "# wendys = ff.loc[ff[\"rName\"] == \"Wendys\"]\n",
    "# w = wendys.groupby(\"province\")['rName'].count()\n",
    "# wendys = ff.loc[ff[\"rName\"] == \"Wendys\"]\n",
    "w = wendys.groupby(\"province\")['rName'].count()\n",
    "def restcounter(df):\n",
    "    listname = list(set(df.rName))\n",
    "    restdict = {}\n",
    "    for x in listname:\n",
    "        series = df.loc[df[\"rName\"] == x]\n",
    "        rest = series.groupby(\"province\")['rName'].count()\n",
    "        restdict[x] = rest  \n",
    "    return restdict\n",
    "    \n",
    "dict_to_use = restcounter(ff)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "rest_df = pd.DataFrame(dict_to_use)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "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>Burger King</th>\n",
       "      <th>Subway</th>\n",
       "      <th>McDonalds</th>\n",
       "      <th>Wendys</th>\n",
       "      <th>Taco Bell</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>AK</th>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AL</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AR</th>\n",
       "      <td>3.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AZ</th>\n",
       "      <td>18.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA</th>\n",
       "      <td>114.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>141.0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>115.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CO</th>\n",
       "      <td>8.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>24.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CT</th>\n",
       "      <td>4.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DE</th>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FL</th>\n",
       "      <td>43.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>133.0</td>\n",
       "      <td>53.0</td>\n",
       "      <td>67.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GA</th>\n",
       "      <td>17.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HI</th>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IA</th>\n",
       "      <td>8.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <td>3.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IL</th>\n",
       "      <td>44.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IN</th>\n",
       "      <td>5.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>41.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>KS</th>\n",
       "      <td>1.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>13.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>KY</th>\n",
       "      <td>12.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>47.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LA</th>\n",
       "      <td>26.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>24.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MA</th>\n",
       "      <td>24.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MD</th>\n",
       "      <td>21.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ME</th>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MI</th>\n",
       "      <td>55.0</td>\n",
       "      <td>47.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MN</th>\n",
       "      <td>17.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MO</th>\n",
       "      <td>4.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>38.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MS</th>\n",
       "      <td>5.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MT</th>\n",
       "      <td>2.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NC</th>\n",
       "      <td>32.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ND</th>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NE</th>\n",
       "      <td>14.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NH</th>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NJ</th>\n",
       "      <td>22.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NM</th>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NV</th>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>19.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NY</th>\n",
       "      <td>30.0</td>\n",
       "      <td>51.0</td>\n",
       "      <td>95.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>22.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OH</th>\n",
       "      <td>42.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>95.0</td>\n",
       "      <td>43.0</td>\n",
       "      <td>43.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OK</th>\n",
       "      <td>1.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OR</th>\n",
       "      <td>15.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>47.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>13.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PA</th>\n",
       "      <td>25.0</td>\n",
       "      <td>38.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>37.0</td>\n",
       "      <td>13.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RI</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SC</th>\n",
       "      <td>4.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SD</th>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TN</th>\n",
       "      <td>8.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>63.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>21.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TX</th>\n",
       "      <td>46.0</td>\n",
       "      <td>51.0</td>\n",
       "      <td>130.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>52.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UT</th>\n",
       "      <td>5.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VA</th>\n",
       "      <td>32.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>29.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VT</th>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WA</th>\n",
       "      <td>15.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>29.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WI</th>\n",
       "      <td>15.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WV</th>\n",
       "      <td>11.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WY</th>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Burger King  Subway  McDonalds  Wendys  Taco Bell\n",
       "AK          NaN     4.0        1.0     NaN        2.0\n",
       "AL          NaN     NaN        NaN     NaN        3.0\n",
       "AR          3.0    10.0       16.0    12.0       14.0\n",
       "AZ         18.0    18.0       56.0    14.0       40.0\n",
       "CA        114.0    70.0      141.0    32.0      115.0\n",
       "CO          8.0    18.0       13.0    12.0       24.0\n",
       "CT          4.0    17.0       11.0     1.0        4.0\n",
       "DE          1.0     3.0        5.0     1.0        6.0\n",
       "FL         43.0    29.0      133.0    53.0       67.0\n",
       "GA         17.0    35.0       90.0    36.0       40.0\n",
       "HI          3.0     NaN       11.0     1.0        1.0\n",
       "IA          8.0     3.0       22.0     5.0        4.0\n",
       "ID          3.0    10.0        3.0     5.0        2.0\n",
       "IL         44.0    29.0       70.0    18.0       30.0\n",
       "IN          5.0    25.0        8.0    22.0       41.0\n",
       "KS          1.0     9.0        2.0    12.0       13.0\n",
       "KY         12.0    10.0       47.0    18.0       18.0\n",
       "LA         26.0    12.0       44.0    17.0       24.0\n",
       "MA         24.0    27.0       33.0    17.0        8.0\n",
       "MD         21.0    13.0       39.0     6.0       18.0\n",
       "ME          5.0     NaN        6.0     3.0        3.0\n",
       "MI         55.0    47.0       66.0     7.0       50.0\n",
       "MN         17.0    11.0       49.0     2.0       14.0\n",
       "MO          4.0    17.0       32.0     4.0       38.0\n",
       "MS          5.0     8.0       16.0     NaN        5.0\n",
       "MT          2.0     7.0        7.0     NaN        3.0\n",
       "NC         32.0    28.0       62.0     6.0       17.0\n",
       "ND          3.0     5.0        4.0     2.0        4.0\n",
       "NE         14.0     9.0        7.0     6.0        6.0\n",
       "NH          6.0     1.0        7.0     1.0        6.0\n",
       "NJ         22.0     4.0       18.0    19.0       12.0\n",
       "NM          2.0     3.0       19.0     5.0       11.0\n",
       "NV          2.0     4.0       23.0    10.0       19.0\n",
       "NY         30.0    51.0       95.0    22.0       22.0\n",
       "OH         42.0    64.0       95.0    43.0       43.0\n",
       "OK          1.0    18.0       33.0     3.0       10.0\n",
       "OR         15.0    13.0       47.0     9.0       13.0\n",
       "PA         25.0    38.0       93.0    37.0       13.0\n",
       "RI          NaN     2.0        NaN     3.0        NaN\n",
       "SC          4.0    21.0       27.0    20.0        9.0\n",
       "SD          NaN     4.0       13.0     2.0        2.0\n",
       "TN          8.0    26.0       63.0    19.0       21.0\n",
       "TX         46.0    51.0      130.0    29.0       52.0\n",
       "UT          5.0     2.0        8.0     3.0        6.0\n",
       "VA         32.0    22.0       10.0    26.0       29.0\n",
       "VT          2.0     NaN        6.0     1.0        1.0\n",
       "WA         15.0    17.0       41.0    12.0       29.0\n",
       "WI         15.0    20.0       34.0     NaN       18.0\n",
       "WV         11.0     NaN       16.0     4.0        7.0\n",
       "WY          1.0     2.0        6.0     1.0        1.0"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rest_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "abbr = [\"AL\",\"AK\",\"AZ\",\"AR\",\"CA\",\"CO\",\"CT\",\"DE\",\"FL\",\"GA\",\"HI\",\"ID\",\"IL\",\"IN\",\"IA\",\"KS\",\"KY\",\"LA\",\"ME\",\"MD\",\"MA\",\"MI\",\"MN\",\"MS\",\"MO\",\"MT\",\"NE\",\"NV\",\"NH\",\"NJ\",\"NM\",\"NY\",\"NC\",\"ND\",\"OH\",\"OK\",\"OR\",\"PA\",\"RI\",\"SC\",\"SD\",\"TN\",\"TX\",\"UT\",\"VT\",\"VA\",\"WA\",\"WV\",\"WI\",\"WY\"]\n",
    "states = {\"AL\":\"Alabama\",\"AK\":\"Alaska\",\"AZ\":\"Arizona\",\"AR\":\"Arkansas\",\"CA\":\"California\",\"CO\":\"Colorado\",\"CT\":\"Connecticut\",\"DE\":\"Delaware\",\"FL\":\"Florida\",\"GA\":\"Georgia\",\"HI\":\"Hawaii\",\"ID\":\"Idaho\",\"IL\":\"Illinois\",\"IN\":\"Indiana\",\"IA\":\"Iowa\",\"KS\":\"Kansas\",\"KY\":\"Kentucky\",\"LA\":\"Louisiana\",\"ME\":\"Maine\",\"MD\":\"Maryland\",\"MA\":\"Massachusetts\",\"MI\":\"Michigan\",\"MN\":\"Minnesota\",\"MS\":\"Mississippi\",\"MO\":\"Missouri\",\"MT\":\"Montana\",\"NE\":\"Nebraska\",\"NV\":\"Nevada\",\"NH\":\"New Hampshire\",\"NJ\":\"New Jersey\",\"NM\":\"New Mexico\",\"NY\":\"New York\",\"NC\":\"North Carolina\",\"ND\":\"North Dakota\",\"OH\":\"Ohio\",\"OK\":\"Oklahoma\",\"OR\":\"Oregon\",\"PA\":\"Pennsylvania\",\"RI\":\"Rhode Island\",\"SC\":\"South Carolina\",\"SD\":\"South Dakota\",\"TN\":\"Tennessee\",\"TX\":\"Texas\",\"UT\":\"Utah\",\"VT\":\"Vermont\",\"VA\":\"Virginia\",\"WA\":\"Washington\",\"WV\":\"West Virginia\",\"WI\":\"Wisconsin\",\"WY\":\"Wyoming\"}\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "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>Burger King</th>\n",
       "      <th>Subway</th>\n",
       "      <th>McDonalds</th>\n",
       "      <th>Wendys</th>\n",
       "      <th>Taco Bell</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>State</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>Alabama</th>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Alaska</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Arizona</th>\n",
       "      <td>3.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Arkansas</th>\n",
       "      <td>18.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>California</th>\n",
       "      <td>114.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>141.0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>115.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Colorado</th>\n",
       "      <td>8.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>24.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Connecticut</th>\n",
       "      <td>4.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Delaware</th>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Florida</th>\n",
       "      <td>43.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>133.0</td>\n",
       "      <td>53.0</td>\n",
       "      <td>67.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Georgia</th>\n",
       "      <td>17.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hawaii</th>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Idaho</th>\n",
       "      <td>8.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Illinois</th>\n",
       "      <td>3.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Indiana</th>\n",
       "      <td>44.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Iowa</th>\n",
       "      <td>5.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>41.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kansas</th>\n",
       "      <td>1.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>13.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kentucky</th>\n",
       "      <td>12.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>47.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Louisiana</th>\n",
       "      <td>26.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>24.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Maine</th>\n",
       "      <td>24.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Maryland</th>\n",
       "      <td>21.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Massachusetts</th>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Michigan</th>\n",
       "      <td>55.0</td>\n",
       "      <td>47.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Minnesota</th>\n",
       "      <td>17.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mississippi</th>\n",
       "      <td>4.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>38.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Missouri</th>\n",
       "      <td>5.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Montana</th>\n",
       "      <td>2.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Nebraska</th>\n",
       "      <td>32.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Nevada</th>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>New Hampshire</th>\n",
       "      <td>14.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>New Jersey</th>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>New Mexico</th>\n",
       "      <td>22.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>New York</th>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>North Carolina</th>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>19.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>North Dakota</th>\n",
       "      <td>30.0</td>\n",
       "      <td>51.0</td>\n",
       "      <td>95.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>22.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ohio</th>\n",
       "      <td>42.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>95.0</td>\n",
       "      <td>43.0</td>\n",
       "      <td>43.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Oklahoma</th>\n",
       "      <td>1.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Oregon</th>\n",
       "      <td>15.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>47.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>13.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pennsylvania</th>\n",
       "      <td>25.0</td>\n",
       "      <td>38.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>37.0</td>\n",
       "      <td>13.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Rhode Island</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>South Carolina</th>\n",
       "      <td>4.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>South Dakota</th>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tennessee</th>\n",
       "      <td>8.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>63.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>21.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Texas</th>\n",
       "      <td>46.0</td>\n",
       "      <td>51.0</td>\n",
       "      <td>130.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>52.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Utah</th>\n",
       "      <td>5.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Vermont</th>\n",
       "      <td>32.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>29.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Virginia</th>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Washington</th>\n",
       "      <td>15.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>29.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>West Virginia</th>\n",
       "      <td>15.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wisconsin</th>\n",
       "      <td>11.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wyoming</th>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                Burger King  Subway  McDonalds  Wendys  Taco Bell\n",
       "State                                                            \n",
       "Alabama                 NaN     4.0        1.0     NaN        2.0\n",
       "Alaska                  NaN     NaN        NaN     NaN        3.0\n",
       "Arizona                 3.0    10.0       16.0    12.0       14.0\n",
       "Arkansas               18.0    18.0       56.0    14.0       40.0\n",
       "California            114.0    70.0      141.0    32.0      115.0\n",
       "Colorado                8.0    18.0       13.0    12.0       24.0\n",
       "Connecticut             4.0    17.0       11.0     1.0        4.0\n",
       "Delaware                1.0     3.0        5.0     1.0        6.0\n",
       "Florida                43.0    29.0      133.0    53.0       67.0\n",
       "Georgia                17.0    35.0       90.0    36.0       40.0\n",
       "Hawaii                  3.0     NaN       11.0     1.0        1.0\n",
       "Idaho                   8.0     3.0       22.0     5.0        4.0\n",
       "Illinois                3.0    10.0        3.0     5.0        2.0\n",
       "Indiana                44.0    29.0       70.0    18.0       30.0\n",
       "Iowa                    5.0    25.0        8.0    22.0       41.0\n",
       "Kansas                  1.0     9.0        2.0    12.0       13.0\n",
       "Kentucky               12.0    10.0       47.0    18.0       18.0\n",
       "Louisiana              26.0    12.0       44.0    17.0       24.0\n",
       "Maine                  24.0    27.0       33.0    17.0        8.0\n",
       "Maryland               21.0    13.0       39.0     6.0       18.0\n",
       "Massachusetts           5.0     NaN        6.0     3.0        3.0\n",
       "Michigan               55.0    47.0       66.0     7.0       50.0\n",
       "Minnesota              17.0    11.0       49.0     2.0       14.0\n",
       "Mississippi             4.0    17.0       32.0     4.0       38.0\n",
       "Missouri                5.0     8.0       16.0     NaN        5.0\n",
       "Montana                 2.0     7.0        7.0     NaN        3.0\n",
       "Nebraska               32.0    28.0       62.0     6.0       17.0\n",
       "Nevada                  3.0     5.0        4.0     2.0        4.0\n",
       "New Hampshire          14.0     9.0        7.0     6.0        6.0\n",
       "New Jersey              6.0     1.0        7.0     1.0        6.0\n",
       "New Mexico             22.0     4.0       18.0    19.0       12.0\n",
       "New York                2.0     3.0       19.0     5.0       11.0\n",
       "North Carolina          2.0     4.0       23.0    10.0       19.0\n",
       "North Dakota           30.0    51.0       95.0    22.0       22.0\n",
       "Ohio                   42.0    64.0       95.0    43.0       43.0\n",
       "Oklahoma                1.0    18.0       33.0     3.0       10.0\n",
       "Oregon                 15.0    13.0       47.0     9.0       13.0\n",
       "Pennsylvania           25.0    38.0       93.0    37.0       13.0\n",
       "Rhode Island            NaN     2.0        NaN     3.0        NaN\n",
       "South Carolina          4.0    21.0       27.0    20.0        9.0\n",
       "South Dakota            NaN     4.0       13.0     2.0        2.0\n",
       "Tennessee               8.0    26.0       63.0    19.0       21.0\n",
       "Texas                  46.0    51.0      130.0    29.0       52.0\n",
       "Utah                    5.0     2.0        8.0     3.0        6.0\n",
       "Vermont                32.0    22.0       10.0    26.0       29.0\n",
       "Virginia                2.0     NaN        6.0     1.0        1.0\n",
       "Washington             15.0    17.0       41.0    12.0       29.0\n",
       "West Virginia          15.0    20.0       34.0     NaN       18.0\n",
       "Wisconsin              11.0     NaN       16.0     4.0        7.0\n",
       "Wyoming                 1.0     2.0        6.0     1.0        1.0"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rest_df['State'] = states.values()\n",
    "rest_df = rest_df.set_index(\"State\")\n",
    "rest_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "combo = pd.merge(census_pd, rest_df, left_on = \"Name\", right_on = \"State\")\n",
    "combo = combo.fillna(0)\n",
    "combo\n",
    "combo.to_json(\"third.json\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [conda env:.conda-PythonData]*",
   "language": "python",
   "name": "myenv"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  },
  "latex_envs": {
   "LaTeX_envs_menu_present": true,
   "autoclose": false,
   "autocomplete": true,
   "bibliofile": "biblio.bib",
   "cite_by": "apalike",
   "current_citInitial": 1,
   "eqLabelWithNumbers": true,
   "eqNumInitial": 1,
   "hotkeys": {
    "equation": "Ctrl-E",
    "itemize": "Ctrl-I"
   },
   "labels_anchors": false,
   "latex_user_defs": false,
   "report_style_numbering": false,
   "user_envs_cfg": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}