CSV_Loader.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv\n",
    "import pandas as pd\n",
    "import json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = \"2019_Hackathon_Data.csv\"\n",
    "ad_data = \"2019_Hackathon_Additional_Data.csv\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [],
   "source": [
    "file = pd.read_csv(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['venue_id', 'brand', 'state', 'city', 'daily_traffic_avg', 'home_air_distance_kilometers_median', 'home_air_distance_kilometers_25th_pct', 'home_air_distance_75th_kilometers_pct', 'work_air_distance_kilometers_median', 'work_air_distance_25th_kilometers_pct', 'work_air_distance_75th_kilometers_pct', 'fipsCode', 'traffic_fraction', 'people_fraction', 'venue_air_distance', 'population']\n",
      "['47d5c33a-0ee4-40d8-b808-0166286ca834', 'Whole Foods Market', 'TX', 'Houston', '1948', '3', '2', '10', '5', '3', '13', 482014115011, 0.001, 0.003, 2.482, 2198]\n"
     ]
    }
   ],
   "source": [
    "with open(data) as f:\n",
    "    reader = csv.reader(f, delimiter=\",\")\n",
    "    rows = [row for row in reader]\n",
    "    # original keys\n",
    "    keys = rows[0][:-1]\n",
    "    # rows of original values\n",
    "    o_rows = [row[:-1] for row in rows[1:]]\n",
    "    \n",
    "    new_rows = []\n",
    "    for k, row in enumerate(rows[1:]):\n",
    "        row = row[-1] # get [...]\n",
    "        row = row[1:-1] # remove [, ]\n",
    "        items = row.split('},') # get out every {...}\n",
    "        items = [item[1:] for item in items]\n",
    "        items[-1] = items[-1][: -1]\n",
    "        \n",
    "        # calculate fraction sum\n",
    "        t_sum = 0\n",
    "        p_sum = 0\n",
    "        \n",
    "        for l, item in enumerate(items):\n",
    "            infos = item.split(',')\n",
    "            values = []\n",
    "            \n",
    "            for p, info in enumerate(infos):\n",
    "                key, value = info.split(':')\n",
    "                key = key[1:-1]\n",
    "                if value[0] == '\\\"':\n",
    "                    value = value[1:-1]\n",
    "                value = float(value)\n",
    "                if value > 1:\n",
    "                    value = int(value)\n",
    "                if p == 3:\n",
    "                    value /= 1000\n",
    "                values.append(value)\n",
    "                if p == 1:\n",
    "                    t_sum += value\n",
    "                elif p == 2:\n",
    "                    p_sum += value\n",
    "                    \n",
    "                if (k == 0) and (l == 0): # need to modify keys\n",
    "                    keys.append(key)\n",
    "            \n",
    "            current_row = o_rows[k].copy()\n",
    "            current_row += values\n",
    "            new_rows.append(current_row)\n",
    "        \n",
    "#        print(t_sum)\n",
    "#        print(p_sum)\n",
    "    \n",
    "    print(keys)\n",
    "    print(new_rows[0])\n",
    "            \n",
    "            \n",
    "        \n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('mod_file.csv', mode='w') as file:\n",
    "    f_writer = csv.writer(file, delimiter=',')\n",
    "\n",
    "    f_writer.writerow(keys)\n",
    "    for row in new_rows:\n",
    "        f_writer.writerow(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "metadata": {},
   "outputs": [],
   "source": [
    "geo_data = \"cbg_geographic_data.csv\"\n",
    "new_data = \"mod_file.csv\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "metadata": {},
   "outputs": [],
   "source": [
    "geo = pd.read_csv(geo_data)\n",
    "new = pd.read_csv(new_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "metadata": {},
   "outputs": [],
   "source": [
    "geo.columns = ['fipsCode', 'amount_land', 'amount_water', 'latitude',\n",
    "       'longitude']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "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>fipsCode</th>\n",
       "      <th>amount_land</th>\n",
       "      <th>amount_water</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>venue_id</th>\n",
       "      <th>brand</th>\n",
       "      <th>state</th>\n",
       "      <th>city</th>\n",
       "      <th>daily_traffic_avg</th>\n",
       "      <th>home_air_distance_kilometers_median</th>\n",
       "      <th>home_air_distance_kilometers_25th_pct</th>\n",
       "      <th>home_air_distance_75th_kilometers_pct</th>\n",
       "      <th>work_air_distance_kilometers_median</th>\n",
       "      <th>work_air_distance_25th_kilometers_pct</th>\n",
       "      <th>work_air_distance_75th_kilometers_pct</th>\n",
       "      <th>traffic_fraction</th>\n",
       "      <th>people_fraction</th>\n",
       "      <th>venue_air_distance</th>\n",
       "      <th>population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>80010085053</td>\n",
       "      <td>626719.0</td>\n",
       "      <td>0</td>\n",
       "      <td>39.896425</td>\n",
       "      <td>-104.972767</td>\n",
       "      <td>dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>CO</td>\n",
       "      <td>Thornton</td>\n",
       "      <td>440</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>20</td>\n",
       "      <td>0.005</td>\n",
       "      <td>0.007</td>\n",
       "      <td>1.903</td>\n",
       "      <td>1647</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>80010085061</td>\n",
       "      <td>438783.0</td>\n",
       "      <td>39928</td>\n",
       "      <td>39.910950</td>\n",
       "      <td>-104.981807</td>\n",
       "      <td>dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>CO</td>\n",
       "      <td>Thornton</td>\n",
       "      <td>440</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>20</td>\n",
       "      <td>0.006</td>\n",
       "      <td>0.004</td>\n",
       "      <td>0.471</td>\n",
       "      <td>1427</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>80010085063</td>\n",
       "      <td>983855.0</td>\n",
       "      <td>0</td>\n",
       "      <td>39.905276</td>\n",
       "      <td>-104.985711</td>\n",
       "      <td>dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>CO</td>\n",
       "      <td>Thornton</td>\n",
       "      <td>440</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>20</td>\n",
       "      <td>0.009</td>\n",
       "      <td>0.009</td>\n",
       "      <td>1.159</td>\n",
       "      <td>3310</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>80010085063</td>\n",
       "      <td>983855.0</td>\n",
       "      <td>0</td>\n",
       "      <td>39.905276</td>\n",
       "      <td>-104.985711</td>\n",
       "      <td>076858a5-6dfd-4d61-970a-dcbca60e3a06</td>\n",
       "      <td>Whole Foods Market</td>\n",
       "      <td>CO</td>\n",
       "      <td>Westminster</td>\n",
       "      <td>749</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "      <td>10</td>\n",
       "      <td>5</td>\n",
       "      <td>23</td>\n",
       "      <td>0.002</td>\n",
       "      <td>0.003</td>\n",
       "      <td>4.579</td>\n",
       "      <td>3310</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>80010085071</td>\n",
       "      <td>776717.0</td>\n",
       "      <td>0</td>\n",
       "      <td>39.904478</td>\n",
       "      <td>-104.963450</td>\n",
       "      <td>dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>CO</td>\n",
       "      <td>Thornton</td>\n",
       "      <td>440</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>20</td>\n",
       "      <td>0.004</td>\n",
       "      <td>0.007</td>\n",
       "      <td>0.946</td>\n",
       "      <td>2204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>80010085072</td>\n",
       "      <td>476945.0</td>\n",
       "      <td>0</td>\n",
       "      <td>39.904002</td>\n",
       "      <td>-104.970332</td>\n",
       "      <td>dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>CO</td>\n",
       "      <td>Thornton</td>\n",
       "      <td>440</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>20</td>\n",
       "      <td>0.010</td>\n",
       "      <td>0.008</td>\n",
       "      <td>0.897</td>\n",
       "      <td>1364</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>80010085073</td>\n",
       "      <td>329154.0</td>\n",
       "      <td>0</td>\n",
       "      <td>39.902973</td>\n",
       "      <td>-104.975251</td>\n",
       "      <td>dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>CO</td>\n",
       "      <td>Thornton</td>\n",
       "      <td>440</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>20</td>\n",
       "      <td>0.004</td>\n",
       "      <td>0.007</td>\n",
       "      <td>1.038</td>\n",
       "      <td>974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>80010085074</td>\n",
       "      <td>538572.0</td>\n",
       "      <td>0</td>\n",
       "      <td>39.911064</td>\n",
       "      <td>-104.973365</td>\n",
       "      <td>dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>CO</td>\n",
       "      <td>Thornton</td>\n",
       "      <td>440</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>20</td>\n",
       "      <td>0.006</td>\n",
       "      <td>0.009</td>\n",
       "      <td>0.295</td>\n",
       "      <td>1061</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>80010085081</td>\n",
       "      <td>560164.0</td>\n",
       "      <td>0</td>\n",
       "      <td>39.902984</td>\n",
       "      <td>-104.944014</td>\n",
       "      <td>dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>CO</td>\n",
       "      <td>Thornton</td>\n",
       "      <td>440</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>20</td>\n",
       "      <td>0.004</td>\n",
       "      <td>0.004</td>\n",
       "      <td>2.519</td>\n",
       "      <td>977</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>80010085082</td>\n",
       "      <td>1134078.0</td>\n",
       "      <td>28118</td>\n",
       "      <td>39.906055</td>\n",
       "      <td>-104.953742</td>\n",
       "      <td>dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>CO</td>\n",
       "      <td>Thornton</td>\n",
       "      <td>440</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>20</td>\n",
       "      <td>0.023</td>\n",
       "      <td>0.015</td>\n",
       "      <td>1.335</td>\n",
       "      <td>2702</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>80010085082</td>\n",
       "      <td>1134078.0</td>\n",
       "      <td>28118</td>\n",
       "      <td>39.906055</td>\n",
       "      <td>-104.953742</td>\n",
       "      <td>076858a5-6dfd-4d61-970a-dcbca60e3a06</td>\n",
       "      <td>Whole Foods Market</td>\n",
       "      <td>CO</td>\n",
       "      <td>Westminster</td>\n",
       "      <td>749</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "      <td>10</td>\n",
       "      <td>5</td>\n",
       "      <td>23</td>\n",
       "      <td>0.003</td>\n",
       "      <td>0.003</td>\n",
       "      <td>7.327</td>\n",
       "      <td>2702</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>80010085083</td>\n",
       "      <td>827330.0</td>\n",
       "      <td>0</td>\n",
       "      <td>39.910630</td>\n",
       "      <td>-104.946768</td>\n",
       "      <td>dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>CO</td>\n",
       "      <td>Thornton</td>\n",
       "      <td>440</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>20</td>\n",
       "      <td>0.007</td>\n",
       "      <td>0.008</td>\n",
       "      <td>1.738</td>\n",
       "      <td>2137</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>80010085241</td>\n",
       "      <td>983835.0</td>\n",
       "      <td>30346</td>\n",
       "      <td>39.937794</td>\n",
       "      <td>-104.926118</td>\n",
       "      <td>dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>CO</td>\n",
       "      <td>Thornton</td>\n",
       "      <td>440</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>20</td>\n",
       "      <td>0.010</td>\n",
       "      <td>0.009</td>\n",
       "      <td>3.958</td>\n",
       "      <td>2041</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>80010085242</td>\n",
       "      <td>493347.0</td>\n",
       "      <td>6481</td>\n",
       "      <td>39.931763</td>\n",
       "      <td>-104.932673</td>\n",
       "      <td>dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>CO</td>\n",
       "      <td>Thornton</td>\n",
       "      <td>440</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>20</td>\n",
       "      <td>0.005</td>\n",
       "      <td>0.007</td>\n",
       "      <td>3.350</td>\n",
       "      <td>1354</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>80010085243</td>\n",
       "      <td>572912.0</td>\n",
       "      <td>0</td>\n",
       "      <td>39.940554</td>\n",
       "      <td>-104.933889</td>\n",
       "      <td>dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>CO</td>\n",
       "      <td>Thornton</td>\n",
       "      <td>440</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>20</td>\n",
       "      <td>0.005</td>\n",
       "      <td>0.004</td>\n",
       "      <td>3.774</td>\n",
       "      <td>1397</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>80010085244</td>\n",
       "      <td>2499538.0</td>\n",
       "      <td>5239</td>\n",
       "      <td>39.951578</td>\n",
       "      <td>-104.927948</td>\n",
       "      <td>dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>CO</td>\n",
       "      <td>Thornton</td>\n",
       "      <td>440</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>20</td>\n",
       "      <td>0.006</td>\n",
       "      <td>0.012</td>\n",
       "      <td>4.050</td>\n",
       "      <td>1510</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>80010085261</td>\n",
       "      <td>5303239.0</td>\n",
       "      <td>31690</td>\n",
       "      <td>39.948761</td>\n",
       "      <td>-104.974382</td>\n",
       "      <td>dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>CO</td>\n",
       "      <td>Thornton</td>\n",
       "      <td>440</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>20</td>\n",
       "      <td>0.011</td>\n",
       "      <td>0.013</td>\n",
       "      <td>1.369</td>\n",
       "      <td>1481</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>80010085261</td>\n",
       "      <td>5303239.0</td>\n",
       "      <td>31690</td>\n",
       "      <td>39.948761</td>\n",
       "      <td>-104.974382</td>\n",
       "      <td>076858a5-6dfd-4d61-970a-dcbca60e3a06</td>\n",
       "      <td>Whole Foods Market</td>\n",
       "      <td>CO</td>\n",
       "      <td>Westminster</td>\n",
       "      <td>749</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "      <td>10</td>\n",
       "      <td>5</td>\n",
       "      <td>23</td>\n",
       "      <td>0.003</td>\n",
       "      <td>0.005</td>\n",
       "      <td>5.111</td>\n",
       "      <td>1481</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>80010085262</td>\n",
       "      <td>593598.0</td>\n",
       "      <td>0</td>\n",
       "      <td>39.935653</td>\n",
       "      <td>-104.964275</td>\n",
       "      <td>dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>CO</td>\n",
       "      <td>Thornton</td>\n",
       "      <td>440</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>20</td>\n",
       "      <td>0.007</td>\n",
       "      <td>0.009</td>\n",
       "      <td>1.363</td>\n",
       "      <td>1569</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>80010085263</td>\n",
       "      <td>321336.0</td>\n",
       "      <td>66649</td>\n",
       "      <td>39.932065</td>\n",
       "      <td>-104.973475</td>\n",
       "      <td>dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>CO</td>\n",
       "      <td>Thornton</td>\n",
       "      <td>440</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>20</td>\n",
       "      <td>0.011</td>\n",
       "      <td>0.009</td>\n",
       "      <td>1.312</td>\n",
       "      <td>1283</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>80010085264</td>\n",
       "      <td>425607.0</td>\n",
       "      <td>0</td>\n",
       "      <td>39.936441</td>\n",
       "      <td>-104.970032</td>\n",
       "      <td>dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>CO</td>\n",
       "      <td>Thornton</td>\n",
       "      <td>440</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>20</td>\n",
       "      <td>0.003</td>\n",
       "      <td>0.005</td>\n",
       "      <td>1.812</td>\n",
       "      <td>918</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>80010085265</td>\n",
       "      <td>596650.0</td>\n",
       "      <td>0</td>\n",
       "      <td>39.938837</td>\n",
       "      <td>-104.974443</td>\n",
       "      <td>dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>CO</td>\n",
       "      <td>Thornton</td>\n",
       "      <td>440</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>20</td>\n",
       "      <td>0.014</td>\n",
       "      <td>0.010</td>\n",
       "      <td>1.724</td>\n",
       "      <td>1459</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>80010085265</td>\n",
       "      <td>596650.0</td>\n",
       "      <td>0</td>\n",
       "      <td>39.938837</td>\n",
       "      <td>-104.974443</td>\n",
       "      <td>076858a5-6dfd-4d61-970a-dcbca60e3a06</td>\n",
       "      <td>Whole Foods Market</td>\n",
       "      <td>CO</td>\n",
       "      <td>Westminster</td>\n",
       "      <td>749</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "      <td>10</td>\n",
       "      <td>5</td>\n",
       "      <td>23</td>\n",
       "      <td>0.003</td>\n",
       "      <td>0.003</td>\n",
       "      <td>6.031</td>\n",
       "      <td>1459</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>80010085291</td>\n",
       "      <td>1273572.0</td>\n",
       "      <td>298340</td>\n",
       "      <td>39.925141</td>\n",
       "      <td>-104.953117</td>\n",
       "      <td>dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>CO</td>\n",
       "      <td>Thornton</td>\n",
       "      <td>440</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>20</td>\n",
       "      <td>0.008</td>\n",
       "      <td>0.011</td>\n",
       "      <td>1.260</td>\n",
       "      <td>2265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>80010085291</td>\n",
       "      <td>1273572.0</td>\n",
       "      <td>298340</td>\n",
       "      <td>39.925141</td>\n",
       "      <td>-104.953117</td>\n",
       "      <td>076858a5-6dfd-4d61-970a-dcbca60e3a06</td>\n",
       "      <td>Whole Foods Market</td>\n",
       "      <td>CO</td>\n",
       "      <td>Westminster</td>\n",
       "      <td>749</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "      <td>10</td>\n",
       "      <td>5</td>\n",
       "      <td>23</td>\n",
       "      <td>0.002</td>\n",
       "      <td>0.003</td>\n",
       "      <td>6.959</td>\n",
       "      <td>2265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>80010085292</td>\n",
       "      <td>1102324.0</td>\n",
       "      <td>97372</td>\n",
       "      <td>39.918459</td>\n",
       "      <td>-104.951023</td>\n",
       "      <td>076858a5-6dfd-4d61-970a-dcbca60e3a06</td>\n",
       "      <td>Whole Foods Market</td>\n",
       "      <td>CO</td>\n",
       "      <td>Westminster</td>\n",
       "      <td>749</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "      <td>10</td>\n",
       "      <td>5</td>\n",
       "      <td>23</td>\n",
       "      <td>0.004</td>\n",
       "      <td>0.005</td>\n",
       "      <td>7.158</td>\n",
       "      <td>2924</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>80010085293</td>\n",
       "      <td>3704092.0</td>\n",
       "      <td>90384</td>\n",
       "      <td>39.920299</td>\n",
       "      <td>-104.973423</td>\n",
       "      <td>dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>CO</td>\n",
       "      <td>Thornton</td>\n",
       "      <td>440</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>20</td>\n",
       "      <td>0.038</td>\n",
       "      <td>0.023</td>\n",
       "      <td>0.000</td>\n",
       "      <td>2307</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>80010085293</td>\n",
       "      <td>3704092.0</td>\n",
       "      <td>90384</td>\n",
       "      <td>39.920299</td>\n",
       "      <td>-104.973423</td>\n",
       "      <td>076858a5-6dfd-4d61-970a-dcbca60e3a06</td>\n",
       "      <td>Whole Foods Market</td>\n",
       "      <td>CO</td>\n",
       "      <td>Westminster</td>\n",
       "      <td>749</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "      <td>10</td>\n",
       "      <td>5</td>\n",
       "      <td>23</td>\n",
       "      <td>0.002</td>\n",
       "      <td>0.004</td>\n",
       "      <td>4.655</td>\n",
       "      <td>2307</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>80010085331</td>\n",
       "      <td>1199768.0</td>\n",
       "      <td>9570</td>\n",
       "      <td>39.890591</td>\n",
       "      <td>-104.947087</td>\n",
       "      <td>dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>CO</td>\n",
       "      <td>Thornton</td>\n",
       "      <td>440</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>20</td>\n",
       "      <td>0.010</td>\n",
       "      <td>0.012</td>\n",
       "      <td>3.055</td>\n",
       "      <td>3705</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>80010085332</td>\n",
       "      <td>2153260.0</td>\n",
       "      <td>26973</td>\n",
       "      <td>39.893485</td>\n",
       "      <td>-104.956992</td>\n",
       "      <td>dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>CO</td>\n",
       "      <td>Thornton</td>\n",
       "      <td>440</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>20</td>\n",
       "      <td>0.017</td>\n",
       "      <td>0.018</td>\n",
       "      <td>2.003</td>\n",
       "      <td>4532</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11252</th>\n",
       "      <td>484910207082</td>\n",
       "      <td>1301503.0</td>\n",
       "      <td>16223</td>\n",
       "      <td>30.504210</td>\n",
       "      <td>-97.624912</td>\n",
       "      <td>5262a5f4-0028-414c-8b62-f90ba66caf11</td>\n",
       "      <td>Trader Joe's</td>\n",
       "      <td>TX</td>\n",
       "      <td>Austin</td>\n",
       "      <td>1102</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>20</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>19</td>\n",
       "      <td>0.001</td>\n",
       "      <td>0.002</td>\n",
       "      <td>16.377</td>\n",
       "      <td>3050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11253</th>\n",
       "      <td>484910207082</td>\n",
       "      <td>1301503.0</td>\n",
       "      <td>16223</td>\n",
       "      <td>30.504210</td>\n",
       "      <td>-97.624912</td>\n",
       "      <td>d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>TX</td>\n",
       "      <td>Round Rock</td>\n",
       "      <td>450</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>21</td>\n",
       "      <td>0.004</td>\n",
       "      <td>0.004</td>\n",
       "      <td>5.430</td>\n",
       "      <td>3050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11254</th>\n",
       "      <td>484910208041</td>\n",
       "      <td>8944248.0</td>\n",
       "      <td>49987</td>\n",
       "      <td>30.519569</td>\n",
       "      <td>-97.585237</td>\n",
       "      <td>d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>TX</td>\n",
       "      <td>Round Rock</td>\n",
       "      <td>450</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>21</td>\n",
       "      <td>0.003</td>\n",
       "      <td>0.005</td>\n",
       "      <td>7.376</td>\n",
       "      <td>2486</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11255</th>\n",
       "      <td>484910208042</td>\n",
       "      <td>1305933.0</td>\n",
       "      <td>7081</td>\n",
       "      <td>30.503865</td>\n",
       "      <td>-97.609383</td>\n",
       "      <td>d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>TX</td>\n",
       "      <td>Round Rock</td>\n",
       "      <td>450</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>21</td>\n",
       "      <td>0.003</td>\n",
       "      <td>0.003</td>\n",
       "      <td>7.026</td>\n",
       "      <td>1165</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11256</th>\n",
       "      <td>484910208043</td>\n",
       "      <td>717198.0</td>\n",
       "      <td>18408</td>\n",
       "      <td>30.515460</td>\n",
       "      <td>-97.601078</td>\n",
       "      <td>d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>TX</td>\n",
       "      <td>Round Rock</td>\n",
       "      <td>450</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>21</td>\n",
       "      <td>0.004</td>\n",
       "      <td>0.004</td>\n",
       "      <td>7.711</td>\n",
       "      <td>1398</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11257</th>\n",
       "      <td>484910208044</td>\n",
       "      <td>3153292.0</td>\n",
       "      <td>52495</td>\n",
       "      <td>30.516570</td>\n",
       "      <td>-97.596821</td>\n",
       "      <td>d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>TX</td>\n",
       "      <td>Round Rock</td>\n",
       "      <td>450</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>21</td>\n",
       "      <td>0.005</td>\n",
       "      <td>0.005</td>\n",
       "      <td>7.932</td>\n",
       "      <td>3767</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11258</th>\n",
       "      <td>484910208061</td>\n",
       "      <td>10590786.0</td>\n",
       "      <td>128152</td>\n",
       "      <td>30.555704</td>\n",
       "      <td>-97.583895</td>\n",
       "      <td>d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>TX</td>\n",
       "      <td>Round Rock</td>\n",
       "      <td>450</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>21</td>\n",
       "      <td>0.011</td>\n",
       "      <td>0.011</td>\n",
       "      <td>8.679</td>\n",
       "      <td>1504</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11259</th>\n",
       "      <td>484910208062</td>\n",
       "      <td>6148314.0</td>\n",
       "      <td>118897</td>\n",
       "      <td>30.553028</td>\n",
       "      <td>-97.612575</td>\n",
       "      <td>d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>TX</td>\n",
       "      <td>Round Rock</td>\n",
       "      <td>450</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>21</td>\n",
       "      <td>0.013</td>\n",
       "      <td>0.014</td>\n",
       "      <td>7.413</td>\n",
       "      <td>3151</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11260</th>\n",
       "      <td>484910214031</td>\n",
       "      <td>11677149.0</td>\n",
       "      <td>63584</td>\n",
       "      <td>30.594533</td>\n",
       "      <td>-97.674460</td>\n",
       "      <td>d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>TX</td>\n",
       "      <td>Round Rock</td>\n",
       "      <td>450</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>21</td>\n",
       "      <td>0.016</td>\n",
       "      <td>0.017</td>\n",
       "      <td>7.737</td>\n",
       "      <td>2239</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11261</th>\n",
       "      <td>484910215021</td>\n",
       "      <td>1231977.0</td>\n",
       "      <td>2424</td>\n",
       "      <td>30.533143</td>\n",
       "      <td>-97.674214</td>\n",
       "      <td>d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>TX</td>\n",
       "      <td>Round Rock</td>\n",
       "      <td>450</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>21</td>\n",
       "      <td>0.008</td>\n",
       "      <td>0.005</td>\n",
       "      <td>2.250</td>\n",
       "      <td>4721</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11262</th>\n",
       "      <td>484910215031</td>\n",
       "      <td>2261373.0</td>\n",
       "      <td>28537</td>\n",
       "      <td>30.520902</td>\n",
       "      <td>-97.682169</td>\n",
       "      <td>d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>TX</td>\n",
       "      <td>Round Rock</td>\n",
       "      <td>450</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>21</td>\n",
       "      <td>0.002</td>\n",
       "      <td>0.004</td>\n",
       "      <td>0.567</td>\n",
       "      <td>2323</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11263</th>\n",
       "      <td>484910215032</td>\n",
       "      <td>766917.0</td>\n",
       "      <td>0</td>\n",
       "      <td>30.524110</td>\n",
       "      <td>-97.671025</td>\n",
       "      <td>5262a5f4-0028-414c-8b62-f90ba66caf11</td>\n",
       "      <td>Trader Joe's</td>\n",
       "      <td>TX</td>\n",
       "      <td>Austin</td>\n",
       "      <td>1102</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>20</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>19</td>\n",
       "      <td>0.001</td>\n",
       "      <td>0.001</td>\n",
       "      <td>15.691</td>\n",
       "      <td>2576</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11264</th>\n",
       "      <td>484910215032</td>\n",
       "      <td>766917.0</td>\n",
       "      <td>0</td>\n",
       "      <td>30.524110</td>\n",
       "      <td>-97.671025</td>\n",
       "      <td>d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>TX</td>\n",
       "      <td>Round Rock</td>\n",
       "      <td>450</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>21</td>\n",
       "      <td>0.012</td>\n",
       "      <td>0.009</td>\n",
       "      <td>1.762</td>\n",
       "      <td>2576</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11265</th>\n",
       "      <td>484910215041</td>\n",
       "      <td>2395272.0</td>\n",
       "      <td>2132</td>\n",
       "      <td>30.564301</td>\n",
       "      <td>-97.684864</td>\n",
       "      <td>d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>TX</td>\n",
       "      <td>Round Rock</td>\n",
       "      <td>450</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>21</td>\n",
       "      <td>0.004</td>\n",
       "      <td>0.004</td>\n",
       "      <td>5.618</td>\n",
       "      <td>2323</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11266</th>\n",
       "      <td>484910215042</td>\n",
       "      <td>3839039.0</td>\n",
       "      <td>26999</td>\n",
       "      <td>30.576245</td>\n",
       "      <td>-97.649382</td>\n",
       "      <td>d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>TX</td>\n",
       "      <td>Round Rock</td>\n",
       "      <td>450</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>21</td>\n",
       "      <td>0.011</td>\n",
       "      <td>0.008</td>\n",
       "      <td>7.491</td>\n",
       "      <td>2184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11267</th>\n",
       "      <td>484910215043</td>\n",
       "      <td>3789919.0</td>\n",
       "      <td>44962</td>\n",
       "      <td>30.575321</td>\n",
       "      <td>-97.672246</td>\n",
       "      <td>d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>TX</td>\n",
       "      <td>Round Rock</td>\n",
       "      <td>450</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>21</td>\n",
       "      <td>0.008</td>\n",
       "      <td>0.009</td>\n",
       "      <td>6.251</td>\n",
       "      <td>3429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11268</th>\n",
       "      <td>484910215051</td>\n",
       "      <td>661408.0</td>\n",
       "      <td>347</td>\n",
       "      <td>30.539225</td>\n",
       "      <td>-97.656090</td>\n",
       "      <td>d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>TX</td>\n",
       "      <td>Round Rock</td>\n",
       "      <td>450</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>21</td>\n",
       "      <td>0.008</td>\n",
       "      <td>0.009</td>\n",
       "      <td>4.046</td>\n",
       "      <td>1679</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11269</th>\n",
       "      <td>484910215052</td>\n",
       "      <td>1836458.0</td>\n",
       "      <td>0</td>\n",
       "      <td>30.524339</td>\n",
       "      <td>-97.661368</td>\n",
       "      <td>5262a5f4-0028-414c-8b62-f90ba66caf11</td>\n",
       "      <td>Trader Joe's</td>\n",
       "      <td>TX</td>\n",
       "      <td>Austin</td>\n",
       "      <td>1102</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>20</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>19</td>\n",
       "      <td>0.001</td>\n",
       "      <td>0.001</td>\n",
       "      <td>15.894</td>\n",
       "      <td>3432</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11270</th>\n",
       "      <td>484910215052</td>\n",
       "      <td>1836458.0</td>\n",
       "      <td>0</td>\n",
       "      <td>30.524339</td>\n",
       "      <td>-97.661368</td>\n",
       "      <td>d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>TX</td>\n",
       "      <td>Round Rock</td>\n",
       "      <td>450</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>21</td>\n",
       "      <td>0.006</td>\n",
       "      <td>0.006</td>\n",
       "      <td>2.148</td>\n",
       "      <td>3432</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11271</th>\n",
       "      <td>484910215053</td>\n",
       "      <td>1178629.0</td>\n",
       "      <td>13009</td>\n",
       "      <td>30.533504</td>\n",
       "      <td>-97.660525</td>\n",
       "      <td>d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>TX</td>\n",
       "      <td>Round Rock</td>\n",
       "      <td>450</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>21</td>\n",
       "      <td>0.006</td>\n",
       "      <td>0.005</td>\n",
       "      <td>3.090</td>\n",
       "      <td>2836</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11272</th>\n",
       "      <td>484910215062</td>\n",
       "      <td>688284.0</td>\n",
       "      <td>0</td>\n",
       "      <td>30.549732</td>\n",
       "      <td>-97.620948</td>\n",
       "      <td>d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>TX</td>\n",
       "      <td>Round Rock</td>\n",
       "      <td>450</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>21</td>\n",
       "      <td>0.008</td>\n",
       "      <td>0.006</td>\n",
       "      <td>7.250</td>\n",
       "      <td>1797</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11273</th>\n",
       "      <td>484910215071</td>\n",
       "      <td>5522714.0</td>\n",
       "      <td>120637</td>\n",
       "      <td>30.546891</td>\n",
       "      <td>-97.680343</td>\n",
       "      <td>5262a5f4-0028-414c-8b62-f90ba66caf11</td>\n",
       "      <td>Trader Joe's</td>\n",
       "      <td>TX</td>\n",
       "      <td>Austin</td>\n",
       "      <td>1102</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>20</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>19</td>\n",
       "      <td>0.001</td>\n",
       "      <td>0.002</td>\n",
       "      <td>16.608</td>\n",
       "      <td>4120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11274</th>\n",
       "      <td>484910215071</td>\n",
       "      <td>5522714.0</td>\n",
       "      <td>120637</td>\n",
       "      <td>30.546891</td>\n",
       "      <td>-97.680343</td>\n",
       "      <td>d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>TX</td>\n",
       "      <td>Round Rock</td>\n",
       "      <td>450</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>21</td>\n",
       "      <td>0.023</td>\n",
       "      <td>0.018</td>\n",
       "      <td>2.858</td>\n",
       "      <td>4120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11275</th>\n",
       "      <td>484910215072</td>\n",
       "      <td>7162853.0</td>\n",
       "      <td>337378</td>\n",
       "      <td>30.557827</td>\n",
       "      <td>-97.654281</td>\n",
       "      <td>d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>TX</td>\n",
       "      <td>Round Rock</td>\n",
       "      <td>450</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>21</td>\n",
       "      <td>0.008</td>\n",
       "      <td>0.011</td>\n",
       "      <td>3.877</td>\n",
       "      <td>3307</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11276</th>\n",
       "      <td>484910215073</td>\n",
       "      <td>1162319.0</td>\n",
       "      <td>0</td>\n",
       "      <td>30.553544</td>\n",
       "      <td>-97.667061</td>\n",
       "      <td>d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>TX</td>\n",
       "      <td>Round Rock</td>\n",
       "      <td>450</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>21</td>\n",
       "      <td>0.004</td>\n",
       "      <td>0.005</td>\n",
       "      <td>4.777</td>\n",
       "      <td>3115</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11277</th>\n",
       "      <td>484910215081</td>\n",
       "      <td>3354020.0</td>\n",
       "      <td>10151</td>\n",
       "      <td>30.533896</td>\n",
       "      <td>-97.641724</td>\n",
       "      <td>d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>TX</td>\n",
       "      <td>Round Rock</td>\n",
       "      <td>450</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>21</td>\n",
       "      <td>0.008</td>\n",
       "      <td>0.006</td>\n",
       "      <td>4.001</td>\n",
       "      <td>3047</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11278</th>\n",
       "      <td>484910215082</td>\n",
       "      <td>1690897.0</td>\n",
       "      <td>1040</td>\n",
       "      <td>30.521141</td>\n",
       "      <td>-97.651898</td>\n",
       "      <td>d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>TX</td>\n",
       "      <td>Round Rock</td>\n",
       "      <td>450</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>21</td>\n",
       "      <td>0.009</td>\n",
       "      <td>0.007</td>\n",
       "      <td>1.716</td>\n",
       "      <td>2345</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11279</th>\n",
       "      <td>484910215083</td>\n",
       "      <td>7069869.0</td>\n",
       "      <td>73253</td>\n",
       "      <td>30.534567</td>\n",
       "      <td>-97.628453</td>\n",
       "      <td>d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>TX</td>\n",
       "      <td>Round Rock</td>\n",
       "      <td>450</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "      <td>11</td>\n",
       "      <td>5</td>\n",
       "      <td>21</td>\n",
       "      <td>0.008</td>\n",
       "      <td>0.006</td>\n",
       "      <td>3.033</td>\n",
       "      <td>2329</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11280</th>\n",
       "      <td>484930001032</td>\n",
       "      <td>39708761.0</td>\n",
       "      <td>206707</td>\n",
       "      <td>29.332585</td>\n",
       "      <td>-98.185416</td>\n",
       "      <td>c3bb8a9a-2758-4679-bb7f-00e2a3c10b73</td>\n",
       "      <td>Trader Joe's</td>\n",
       "      <td>TX</td>\n",
       "      <td>San Antonio</td>\n",
       "      <td>1222</td>\n",
       "      <td>12</td>\n",
       "      <td>5</td>\n",
       "      <td>28</td>\n",
       "      <td>11</td>\n",
       "      <td>6</td>\n",
       "      <td>24</td>\n",
       "      <td>0.001</td>\n",
       "      <td>0.002</td>\n",
       "      <td>30.494</td>\n",
       "      <td>1438</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11281</th>\n",
       "      <td>484930004033</td>\n",
       "      <td>24869029.0</td>\n",
       "      <td>52486</td>\n",
       "      <td>29.271174</td>\n",
       "      <td>-98.249512</td>\n",
       "      <td>c3bb8a9a-2758-4679-bb7f-00e2a3c10b73</td>\n",
       "      <td>Trader Joe's</td>\n",
       "      <td>TX</td>\n",
       "      <td>San Antonio</td>\n",
       "      <td>1222</td>\n",
       "      <td>12</td>\n",
       "      <td>5</td>\n",
       "      <td>28</td>\n",
       "      <td>11</td>\n",
       "      <td>6</td>\n",
       "      <td>24</td>\n",
       "      <td>0.001</td>\n",
       "      <td>0.002</td>\n",
       "      <td>31.014</td>\n",
       "      <td>3489</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>11282 rows × 20 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           fipsCode  amount_land  amount_water   latitude   longitude  \\\n",
       "0       80010085053     626719.0             0  39.896425 -104.972767   \n",
       "1       80010085061     438783.0         39928  39.910950 -104.981807   \n",
       "2       80010085063     983855.0             0  39.905276 -104.985711   \n",
       "3       80010085063     983855.0             0  39.905276 -104.985711   \n",
       "4       80010085071     776717.0             0  39.904478 -104.963450   \n",
       "5       80010085072     476945.0             0  39.904002 -104.970332   \n",
       "6       80010085073     329154.0             0  39.902973 -104.975251   \n",
       "7       80010085074     538572.0             0  39.911064 -104.973365   \n",
       "8       80010085081     560164.0             0  39.902984 -104.944014   \n",
       "9       80010085082    1134078.0         28118  39.906055 -104.953742   \n",
       "10      80010085082    1134078.0         28118  39.906055 -104.953742   \n",
       "11      80010085083     827330.0             0  39.910630 -104.946768   \n",
       "12      80010085241     983835.0         30346  39.937794 -104.926118   \n",
       "13      80010085242     493347.0          6481  39.931763 -104.932673   \n",
       "14      80010085243     572912.0             0  39.940554 -104.933889   \n",
       "15      80010085244    2499538.0          5239  39.951578 -104.927948   \n",
       "16      80010085261    5303239.0         31690  39.948761 -104.974382   \n",
       "17      80010085261    5303239.0         31690  39.948761 -104.974382   \n",
       "18      80010085262     593598.0             0  39.935653 -104.964275   \n",
       "19      80010085263     321336.0         66649  39.932065 -104.973475   \n",
       "20      80010085264     425607.0             0  39.936441 -104.970032   \n",
       "21      80010085265     596650.0             0  39.938837 -104.974443   \n",
       "22      80010085265     596650.0             0  39.938837 -104.974443   \n",
       "23      80010085291    1273572.0        298340  39.925141 -104.953117   \n",
       "24      80010085291    1273572.0        298340  39.925141 -104.953117   \n",
       "25      80010085292    1102324.0         97372  39.918459 -104.951023   \n",
       "26      80010085293    3704092.0         90384  39.920299 -104.973423   \n",
       "27      80010085293    3704092.0         90384  39.920299 -104.973423   \n",
       "28      80010085331    1199768.0          9570  39.890591 -104.947087   \n",
       "29      80010085332    2153260.0         26973  39.893485 -104.956992   \n",
       "...             ...          ...           ...        ...         ...   \n",
       "11252  484910207082    1301503.0         16223  30.504210  -97.624912   \n",
       "11253  484910207082    1301503.0         16223  30.504210  -97.624912   \n",
       "11254  484910208041    8944248.0         49987  30.519569  -97.585237   \n",
       "11255  484910208042    1305933.0          7081  30.503865  -97.609383   \n",
       "11256  484910208043     717198.0         18408  30.515460  -97.601078   \n",
       "11257  484910208044    3153292.0         52495  30.516570  -97.596821   \n",
       "11258  484910208061   10590786.0        128152  30.555704  -97.583895   \n",
       "11259  484910208062    6148314.0        118897  30.553028  -97.612575   \n",
       "11260  484910214031   11677149.0         63584  30.594533  -97.674460   \n",
       "11261  484910215021    1231977.0          2424  30.533143  -97.674214   \n",
       "11262  484910215031    2261373.0         28537  30.520902  -97.682169   \n",
       "11263  484910215032     766917.0             0  30.524110  -97.671025   \n",
       "11264  484910215032     766917.0             0  30.524110  -97.671025   \n",
       "11265  484910215041    2395272.0          2132  30.564301  -97.684864   \n",
       "11266  484910215042    3839039.0         26999  30.576245  -97.649382   \n",
       "11267  484910215043    3789919.0         44962  30.575321  -97.672246   \n",
       "11268  484910215051     661408.0           347  30.539225  -97.656090   \n",
       "11269  484910215052    1836458.0             0  30.524339  -97.661368   \n",
       "11270  484910215052    1836458.0             0  30.524339  -97.661368   \n",
       "11271  484910215053    1178629.0         13009  30.533504  -97.660525   \n",
       "11272  484910215062     688284.0             0  30.549732  -97.620948   \n",
       "11273  484910215071    5522714.0        120637  30.546891  -97.680343   \n",
       "11274  484910215071    5522714.0        120637  30.546891  -97.680343   \n",
       "11275  484910215072    7162853.0        337378  30.557827  -97.654281   \n",
       "11276  484910215073    1162319.0             0  30.553544  -97.667061   \n",
       "11277  484910215081    3354020.0         10151  30.533896  -97.641724   \n",
       "11278  484910215082    1690897.0          1040  30.521141  -97.651898   \n",
       "11279  484910215083    7069869.0         73253  30.534567  -97.628453   \n",
       "11280  484930001032   39708761.0        206707  29.332585  -98.185416   \n",
       "11281  484930004033   24869029.0         52486  29.271174  -98.249512   \n",
       "\n",
       "                                   venue_id                   brand state  \\\n",
       "0      dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca  Sprouts Farmers Market    CO   \n",
       "1      dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca  Sprouts Farmers Market    CO   \n",
       "2      dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca  Sprouts Farmers Market    CO   \n",
       "3      076858a5-6dfd-4d61-970a-dcbca60e3a06      Whole Foods Market    CO   \n",
       "4      dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca  Sprouts Farmers Market    CO   \n",
       "5      dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca  Sprouts Farmers Market    CO   \n",
       "6      dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca  Sprouts Farmers Market    CO   \n",
       "7      dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca  Sprouts Farmers Market    CO   \n",
       "8      dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca  Sprouts Farmers Market    CO   \n",
       "9      dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca  Sprouts Farmers Market    CO   \n",
       "10     076858a5-6dfd-4d61-970a-dcbca60e3a06      Whole Foods Market    CO   \n",
       "11     dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca  Sprouts Farmers Market    CO   \n",
       "12     dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca  Sprouts Farmers Market    CO   \n",
       "13     dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca  Sprouts Farmers Market    CO   \n",
       "14     dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca  Sprouts Farmers Market    CO   \n",
       "15     dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca  Sprouts Farmers Market    CO   \n",
       "16     dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca  Sprouts Farmers Market    CO   \n",
       "17     076858a5-6dfd-4d61-970a-dcbca60e3a06      Whole Foods Market    CO   \n",
       "18     dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca  Sprouts Farmers Market    CO   \n",
       "19     dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca  Sprouts Farmers Market    CO   \n",
       "20     dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca  Sprouts Farmers Market    CO   \n",
       "21     dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca  Sprouts Farmers Market    CO   \n",
       "22     076858a5-6dfd-4d61-970a-dcbca60e3a06      Whole Foods Market    CO   \n",
       "23     dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca  Sprouts Farmers Market    CO   \n",
       "24     076858a5-6dfd-4d61-970a-dcbca60e3a06      Whole Foods Market    CO   \n",
       "25     076858a5-6dfd-4d61-970a-dcbca60e3a06      Whole Foods Market    CO   \n",
       "26     dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca  Sprouts Farmers Market    CO   \n",
       "27     076858a5-6dfd-4d61-970a-dcbca60e3a06      Whole Foods Market    CO   \n",
       "28     dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca  Sprouts Farmers Market    CO   \n",
       "29     dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca  Sprouts Farmers Market    CO   \n",
       "...                                     ...                     ...   ...   \n",
       "11252  5262a5f4-0028-414c-8b62-f90ba66caf11            Trader Joe's    TX   \n",
       "11253  d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9  Sprouts Farmers Market    TX   \n",
       "11254  d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9  Sprouts Farmers Market    TX   \n",
       "11255  d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9  Sprouts Farmers Market    TX   \n",
       "11256  d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9  Sprouts Farmers Market    TX   \n",
       "11257  d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9  Sprouts Farmers Market    TX   \n",
       "11258  d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9  Sprouts Farmers Market    TX   \n",
       "11259  d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9  Sprouts Farmers Market    TX   \n",
       "11260  d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9  Sprouts Farmers Market    TX   \n",
       "11261  d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9  Sprouts Farmers Market    TX   \n",
       "11262  d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9  Sprouts Farmers Market    TX   \n",
       "11263  5262a5f4-0028-414c-8b62-f90ba66caf11            Trader Joe's    TX   \n",
       "11264  d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9  Sprouts Farmers Market    TX   \n",
       "11265  d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9  Sprouts Farmers Market    TX   \n",
       "11266  d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9  Sprouts Farmers Market    TX   \n",
       "11267  d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9  Sprouts Farmers Market    TX   \n",
       "11268  d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9  Sprouts Farmers Market    TX   \n",
       "11269  5262a5f4-0028-414c-8b62-f90ba66caf11            Trader Joe's    TX   \n",
       "11270  d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9  Sprouts Farmers Market    TX   \n",
       "11271  d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9  Sprouts Farmers Market    TX   \n",
       "11272  d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9  Sprouts Farmers Market    TX   \n",
       "11273  5262a5f4-0028-414c-8b62-f90ba66caf11            Trader Joe's    TX   \n",
       "11274  d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9  Sprouts Farmers Market    TX   \n",
       "11275  d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9  Sprouts Farmers Market    TX   \n",
       "11276  d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9  Sprouts Farmers Market    TX   \n",
       "11277  d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9  Sprouts Farmers Market    TX   \n",
       "11278  d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9  Sprouts Farmers Market    TX   \n",
       "11279  d7c7eb1e-bdfa-462e-a222-9fff61d6d6a9  Sprouts Farmers Market    TX   \n",
       "11280  c3bb8a9a-2758-4679-bb7f-00e2a3c10b73            Trader Joe's    TX   \n",
       "11281  c3bb8a9a-2758-4679-bb7f-00e2a3c10b73            Trader Joe's    TX   \n",
       "\n",
       "              city  daily_traffic_avg  home_air_distance_kilometers_median  \\\n",
       "0         Thornton                440                                    4   \n",
       "1         Thornton                440                                    4   \n",
       "2         Thornton                440                                    4   \n",
       "3      Westminster                749                                    6   \n",
       "4         Thornton                440                                    4   \n",
       "5         Thornton                440                                    4   \n",
       "6         Thornton                440                                    4   \n",
       "7         Thornton                440                                    4   \n",
       "8         Thornton                440                                    4   \n",
       "9         Thornton                440                                    4   \n",
       "10     Westminster                749                                    6   \n",
       "11        Thornton                440                                    4   \n",
       "12        Thornton                440                                    4   \n",
       "13        Thornton                440                                    4   \n",
       "14        Thornton                440                                    4   \n",
       "15        Thornton                440                                    4   \n",
       "16        Thornton                440                                    4   \n",
       "17     Westminster                749                                    6   \n",
       "18        Thornton                440                                    4   \n",
       "19        Thornton                440                                    4   \n",
       "20        Thornton                440                                    4   \n",
       "21        Thornton                440                                    4   \n",
       "22     Westminster                749                                    6   \n",
       "23        Thornton                440                                    4   \n",
       "24     Westminster                749                                    6   \n",
       "25     Westminster                749                                    6   \n",
       "26        Thornton                440                                    4   \n",
       "27     Westminster                749                                    6   \n",
       "28        Thornton                440                                    4   \n",
       "29        Thornton                440                                    4   \n",
       "...            ...                ...                                  ...   \n",
       "11252       Austin               1102                                   11   \n",
       "11253   Round Rock                450                                    7   \n",
       "11254   Round Rock                450                                    7   \n",
       "11255   Round Rock                450                                    7   \n",
       "11256   Round Rock                450                                    7   \n",
       "11257   Round Rock                450                                    7   \n",
       "11258   Round Rock                450                                    7   \n",
       "11259   Round Rock                450                                    7   \n",
       "11260   Round Rock                450                                    7   \n",
       "11261   Round Rock                450                                    7   \n",
       "11262   Round Rock                450                                    7   \n",
       "11263       Austin               1102                                   11   \n",
       "11264   Round Rock                450                                    7   \n",
       "11265   Round Rock                450                                    7   \n",
       "11266   Round Rock                450                                    7   \n",
       "11267   Round Rock                450                                    7   \n",
       "11268   Round Rock                450                                    7   \n",
       "11269       Austin               1102                                   11   \n",
       "11270   Round Rock                450                                    7   \n",
       "11271   Round Rock                450                                    7   \n",
       "11272   Round Rock                450                                    7   \n",
       "11273       Austin               1102                                   11   \n",
       "11274   Round Rock                450                                    7   \n",
       "11275   Round Rock                450                                    7   \n",
       "11276   Round Rock                450                                    7   \n",
       "11277   Round Rock                450                                    7   \n",
       "11278   Round Rock                450                                    7   \n",
       "11279   Round Rock                450                                    7   \n",
       "11280  San Antonio               1222                                   12   \n",
       "11281  San Antonio               1222                                   12   \n",
       "\n",
       "       home_air_distance_kilometers_25th_pct  \\\n",
       "0                                          2   \n",
       "1                                          2   \n",
       "2                                          2   \n",
       "3                                          3   \n",
       "4                                          2   \n",
       "5                                          2   \n",
       "6                                          2   \n",
       "7                                          2   \n",
       "8                                          2   \n",
       "9                                          2   \n",
       "10                                         3   \n",
       "11                                         2   \n",
       "12                                         2   \n",
       "13                                         2   \n",
       "14                                         2   \n",
       "15                                         2   \n",
       "16                                         2   \n",
       "17                                         3   \n",
       "18                                         2   \n",
       "19                                         2   \n",
       "20                                         2   \n",
       "21                                         2   \n",
       "22                                         3   \n",
       "23                                         2   \n",
       "24                                         3   \n",
       "25                                         3   \n",
       "26                                         2   \n",
       "27                                         3   \n",
       "28                                         2   \n",
       "29                                         2   \n",
       "...                                      ...   \n",
       "11252                                      5   \n",
       "11253                                      4   \n",
       "11254                                      4   \n",
       "11255                                      4   \n",
       "11256                                      4   \n",
       "11257                                      4   \n",
       "11258                                      4   \n",
       "11259                                      4   \n",
       "11260                                      4   \n",
       "11261                                      4   \n",
       "11262                                      4   \n",
       "11263                                      5   \n",
       "11264                                      4   \n",
       "11265                                      4   \n",
       "11266                                      4   \n",
       "11267                                      4   \n",
       "11268                                      4   \n",
       "11269                                      5   \n",
       "11270                                      4   \n",
       "11271                                      4   \n",
       "11272                                      4   \n",
       "11273                                      5   \n",
       "11274                                      4   \n",
       "11275                                      4   \n",
       "11276                                      4   \n",
       "11277                                      4   \n",
       "11278                                      4   \n",
       "11279                                      4   \n",
       "11280                                      5   \n",
       "11281                                      5   \n",
       "\n",
       "       home_air_distance_75th_kilometers_pct  \\\n",
       "0                                          6   \n",
       "1                                          6   \n",
       "2                                          6   \n",
       "3                                         11   \n",
       "4                                          6   \n",
       "5                                          6   \n",
       "6                                          6   \n",
       "7                                          6   \n",
       "8                                          6   \n",
       "9                                          6   \n",
       "10                                        11   \n",
       "11                                         6   \n",
       "12                                         6   \n",
       "13                                         6   \n",
       "14                                         6   \n",
       "15                                         6   \n",
       "16                                         6   \n",
       "17                                        11   \n",
       "18                                         6   \n",
       "19                                         6   \n",
       "20                                         6   \n",
       "21                                         6   \n",
       "22                                        11   \n",
       "23                                         6   \n",
       "24                                        11   \n",
       "25                                        11   \n",
       "26                                         6   \n",
       "27                                        11   \n",
       "28                                         6   \n",
       "29                                         6   \n",
       "...                                      ...   \n",
       "11252                                     20   \n",
       "11253                                     13   \n",
       "11254                                     13   \n",
       "11255                                     13   \n",
       "11256                                     13   \n",
       "11257                                     13   \n",
       "11258                                     13   \n",
       "11259                                     13   \n",
       "11260                                     13   \n",
       "11261                                     13   \n",
       "11262                                     13   \n",
       "11263                                     20   \n",
       "11264                                     13   \n",
       "11265                                     13   \n",
       "11266                                     13   \n",
       "11267                                     13   \n",
       "11268                                     13   \n",
       "11269                                     20   \n",
       "11270                                     13   \n",
       "11271                                     13   \n",
       "11272                                     13   \n",
       "11273                                     20   \n",
       "11274                                     13   \n",
       "11275                                     13   \n",
       "11276                                     13   \n",
       "11277                                     13   \n",
       "11278                                     13   \n",
       "11279                                     13   \n",
       "11280                                     28   \n",
       "11281                                     28   \n",
       "\n",
       "       work_air_distance_kilometers_median  \\\n",
       "0                                       10   \n",
       "1                                       10   \n",
       "2                                       10   \n",
       "3                                       10   \n",
       "4                                       10   \n",
       "5                                       10   \n",
       "6                                       10   \n",
       "7                                       10   \n",
       "8                                       10   \n",
       "9                                       10   \n",
       "10                                      10   \n",
       "11                                      10   \n",
       "12                                      10   \n",
       "13                                      10   \n",
       "14                                      10   \n",
       "15                                      10   \n",
       "16                                      10   \n",
       "17                                      10   \n",
       "18                                      10   \n",
       "19                                      10   \n",
       "20                                      10   \n",
       "21                                      10   \n",
       "22                                      10   \n",
       "23                                      10   \n",
       "24                                      10   \n",
       "25                                      10   \n",
       "26                                      10   \n",
       "27                                      10   \n",
       "28                                      10   \n",
       "29                                      10   \n",
       "...                                    ...   \n",
       "11252                                   11   \n",
       "11253                                   11   \n",
       "11254                                   11   \n",
       "11255                                   11   \n",
       "11256                                   11   \n",
       "11257                                   11   \n",
       "11258                                   11   \n",
       "11259                                   11   \n",
       "11260                                   11   \n",
       "11261                                   11   \n",
       "11262                                   11   \n",
       "11263                                   11   \n",
       "11264                                   11   \n",
       "11265                                   11   \n",
       "11266                                   11   \n",
       "11267                                   11   \n",
       "11268                                   11   \n",
       "11269                                   11   \n",
       "11270                                   11   \n",
       "11271                                   11   \n",
       "11272                                   11   \n",
       "11273                                   11   \n",
       "11274                                   11   \n",
       "11275                                   11   \n",
       "11276                                   11   \n",
       "11277                                   11   \n",
       "11278                                   11   \n",
       "11279                                   11   \n",
       "11280                                   11   \n",
       "11281                                   11   \n",
       "\n",
       "       work_air_distance_25th_kilometers_pct  \\\n",
       "0                                          4   \n",
       "1                                          4   \n",
       "2                                          4   \n",
       "3                                          5   \n",
       "4                                          4   \n",
       "5                                          4   \n",
       "6                                          4   \n",
       "7                                          4   \n",
       "8                                          4   \n",
       "9                                          4   \n",
       "10                                         5   \n",
       "11                                         4   \n",
       "12                                         4   \n",
       "13                                         4   \n",
       "14                                         4   \n",
       "15                                         4   \n",
       "16                                         4   \n",
       "17                                         5   \n",
       "18                                         4   \n",
       "19                                         4   \n",
       "20                                         4   \n",
       "21                                         4   \n",
       "22                                         5   \n",
       "23                                         4   \n",
       "24                                         5   \n",
       "25                                         5   \n",
       "26                                         4   \n",
       "27                                         5   \n",
       "28                                         4   \n",
       "29                                         4   \n",
       "...                                      ...   \n",
       "11252                                      5   \n",
       "11253                                      5   \n",
       "11254                                      5   \n",
       "11255                                      5   \n",
       "11256                                      5   \n",
       "11257                                      5   \n",
       "11258                                      5   \n",
       "11259                                      5   \n",
       "11260                                      5   \n",
       "11261                                      5   \n",
       "11262                                      5   \n",
       "11263                                      5   \n",
       "11264                                      5   \n",
       "11265                                      5   \n",
       "11266                                      5   \n",
       "11267                                      5   \n",
       "11268                                      5   \n",
       "11269                                      5   \n",
       "11270                                      5   \n",
       "11271                                      5   \n",
       "11272                                      5   \n",
       "11273                                      5   \n",
       "11274                                      5   \n",
       "11275                                      5   \n",
       "11276                                      5   \n",
       "11277                                      5   \n",
       "11278                                      5   \n",
       "11279                                      5   \n",
       "11280                                      6   \n",
       "11281                                      6   \n",
       "\n",
       "       work_air_distance_75th_kilometers_pct  traffic_fraction  \\\n",
       "0                                         20             0.005   \n",
       "1                                         20             0.006   \n",
       "2                                         20             0.009   \n",
       "3                                         23             0.002   \n",
       "4                                         20             0.004   \n",
       "5                                         20             0.010   \n",
       "6                                         20             0.004   \n",
       "7                                         20             0.006   \n",
       "8                                         20             0.004   \n",
       "9                                         20             0.023   \n",
       "10                                        23             0.003   \n",
       "11                                        20             0.007   \n",
       "12                                        20             0.010   \n",
       "13                                        20             0.005   \n",
       "14                                        20             0.005   \n",
       "15                                        20             0.006   \n",
       "16                                        20             0.011   \n",
       "17                                        23             0.003   \n",
       "18                                        20             0.007   \n",
       "19                                        20             0.011   \n",
       "20                                        20             0.003   \n",
       "21                                        20             0.014   \n",
       "22                                        23             0.003   \n",
       "23                                        20             0.008   \n",
       "24                                        23             0.002   \n",
       "25                                        23             0.004   \n",
       "26                                        20             0.038   \n",
       "27                                        23             0.002   \n",
       "28                                        20             0.010   \n",
       "29                                        20             0.017   \n",
       "...                                      ...               ...   \n",
       "11252                                     19             0.001   \n",
       "11253                                     21             0.004   \n",
       "11254                                     21             0.003   \n",
       "11255                                     21             0.003   \n",
       "11256                                     21             0.004   \n",
       "11257                                     21             0.005   \n",
       "11258                                     21             0.011   \n",
       "11259                                     21             0.013   \n",
       "11260                                     21             0.016   \n",
       "11261                                     21             0.008   \n",
       "11262                                     21             0.002   \n",
       "11263                                     19             0.001   \n",
       "11264                                     21             0.012   \n",
       "11265                                     21             0.004   \n",
       "11266                                     21             0.011   \n",
       "11267                                     21             0.008   \n",
       "11268                                     21             0.008   \n",
       "11269                                     19             0.001   \n",
       "11270                                     21             0.006   \n",
       "11271                                     21             0.006   \n",
       "11272                                     21             0.008   \n",
       "11273                                     19             0.001   \n",
       "11274                                     21             0.023   \n",
       "11275                                     21             0.008   \n",
       "11276                                     21             0.004   \n",
       "11277                                     21             0.008   \n",
       "11278                                     21             0.009   \n",
       "11279                                     21             0.008   \n",
       "11280                                     24             0.001   \n",
       "11281                                     24             0.001   \n",
       "\n",
       "       people_fraction  venue_air_distance  population  \n",
       "0                0.007               1.903        1647  \n",
       "1                0.004               0.471        1427  \n",
       "2                0.009               1.159        3310  \n",
       "3                0.003               4.579        3310  \n",
       "4                0.007               0.946        2204  \n",
       "5                0.008               0.897        1364  \n",
       "6                0.007               1.038         974  \n",
       "7                0.009               0.295        1061  \n",
       "8                0.004               2.519         977  \n",
       "9                0.015               1.335        2702  \n",
       "10               0.003               7.327        2702  \n",
       "11               0.008               1.738        2137  \n",
       "12               0.009               3.958        2041  \n",
       "13               0.007               3.350        1354  \n",
       "14               0.004               3.774        1397  \n",
       "15               0.012               4.050        1510  \n",
       "16               0.013               1.369        1481  \n",
       "17               0.005               5.111        1481  \n",
       "18               0.009               1.363        1569  \n",
       "19               0.009               1.312        1283  \n",
       "20               0.005               1.812         918  \n",
       "21               0.010               1.724        1459  \n",
       "22               0.003               6.031        1459  \n",
       "23               0.011               1.260        2265  \n",
       "24               0.003               6.959        2265  \n",
       "25               0.005               7.158        2924  \n",
       "26               0.023               0.000        2307  \n",
       "27               0.004               4.655        2307  \n",
       "28               0.012               3.055        3705  \n",
       "29               0.018               2.003        4532  \n",
       "...                ...                 ...         ...  \n",
       "11252            0.002              16.377        3050  \n",
       "11253            0.004               5.430        3050  \n",
       "11254            0.005               7.376        2486  \n",
       "11255            0.003               7.026        1165  \n",
       "11256            0.004               7.711        1398  \n",
       "11257            0.005               7.932        3767  \n",
       "11258            0.011               8.679        1504  \n",
       "11259            0.014               7.413        3151  \n",
       "11260            0.017               7.737        2239  \n",
       "11261            0.005               2.250        4721  \n",
       "11262            0.004               0.567        2323  \n",
       "11263            0.001              15.691        2576  \n",
       "11264            0.009               1.762        2576  \n",
       "11265            0.004               5.618        2323  \n",
       "11266            0.008               7.491        2184  \n",
       "11267            0.009               6.251        3429  \n",
       "11268            0.009               4.046        1679  \n",
       "11269            0.001              15.894        3432  \n",
       "11270            0.006               2.148        3432  \n",
       "11271            0.005               3.090        2836  \n",
       "11272            0.006               7.250        1797  \n",
       "11273            0.002              16.608        4120  \n",
       "11274            0.018               2.858        4120  \n",
       "11275            0.011               3.877        3307  \n",
       "11276            0.005               4.777        3115  \n",
       "11277            0.006               4.001        3047  \n",
       "11278            0.007               1.716        2345  \n",
       "11279            0.006               3.033        2329  \n",
       "11280            0.002              30.494        1438  \n",
       "11281            0.002              31.014        3489  \n",
       "\n",
       "[11282 rows x 20 columns]"
      ]
     },
     "execution_count": 152,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = pd.merge(geo,\n",
    "                 new,\n",
    "                 on='fipsCode')\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "metadata": {},
   "outputs": [],
   "source": [
    "result.to_csv('loc_data.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "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_x</th>\n",
       "      <th>fipsCode</th>\n",
       "      <th>amount_land</th>\n",
       "      <th>amount_water</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>venue_id</th>\n",
       "      <th>brand</th>\n",
       "      <th>state_x</th>\n",
       "      <th>city</th>\n",
       "      <th>...</th>\n",
       "      <th>work_air_distance_kilometers_median</th>\n",
       "      <th>work_air_distance_25th_kilometers_pct</th>\n",
       "      <th>work_air_distance_75th_kilometers_pct</th>\n",
       "      <th>traffic_fraction</th>\n",
       "      <th>people_fraction</th>\n",
       "      <th>venue_air_distance</th>\n",
       "      <th>population</th>\n",
       "      <th>Unnamed: 0_y</th>\n",
       "      <th>state_y</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>80010085053</td>\n",
       "      <td>626719.0</td>\n",
       "      <td>0</td>\n",
       "      <td>39.896425</td>\n",
       "      <td>-104.972767</td>\n",
       "      <td>dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>CO</td>\n",
       "      <td>Thornton</td>\n",
       "      <td>...</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>20</td>\n",
       "      <td>0.005</td>\n",
       "      <td>0.007</td>\n",
       "      <td>1.903</td>\n",
       "      <td>1647</td>\n",
       "      <td>25</td>\n",
       "      <td>CO</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>80010085061</td>\n",
       "      <td>438783.0</td>\n",
       "      <td>39928</td>\n",
       "      <td>39.910950</td>\n",
       "      <td>-104.981807</td>\n",
       "      <td>dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>CO</td>\n",
       "      <td>Thornton</td>\n",
       "      <td>...</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>20</td>\n",
       "      <td>0.006</td>\n",
       "      <td>0.004</td>\n",
       "      <td>0.471</td>\n",
       "      <td>1427</td>\n",
       "      <td>25</td>\n",
       "      <td>CO</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>80010085063</td>\n",
       "      <td>983855.0</td>\n",
       "      <td>0</td>\n",
       "      <td>39.905276</td>\n",
       "      <td>-104.985711</td>\n",
       "      <td>dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>CO</td>\n",
       "      <td>Thornton</td>\n",
       "      <td>...</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>20</td>\n",
       "      <td>0.009</td>\n",
       "      <td>0.009</td>\n",
       "      <td>1.159</td>\n",
       "      <td>3310</td>\n",
       "      <td>25</td>\n",
       "      <td>CO</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>80010085071</td>\n",
       "      <td>776717.0</td>\n",
       "      <td>0</td>\n",
       "      <td>39.904478</td>\n",
       "      <td>-104.963450</td>\n",
       "      <td>dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>CO</td>\n",
       "      <td>Thornton</td>\n",
       "      <td>...</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>20</td>\n",
       "      <td>0.004</td>\n",
       "      <td>0.007</td>\n",
       "      <td>0.946</td>\n",
       "      <td>2204</td>\n",
       "      <td>25</td>\n",
       "      <td>CO</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>80010085072</td>\n",
       "      <td>476945.0</td>\n",
       "      <td>0</td>\n",
       "      <td>39.904002</td>\n",
       "      <td>-104.970332</td>\n",
       "      <td>dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca</td>\n",
       "      <td>Sprouts Farmers Market</td>\n",
       "      <td>CO</td>\n",
       "      <td>Thornton</td>\n",
       "      <td>...</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>20</td>\n",
       "      <td>0.010</td>\n",
       "      <td>0.008</td>\n",
       "      <td>0.897</td>\n",
       "      <td>1364</td>\n",
       "      <td>25</td>\n",
       "      <td>CO</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0_x     fipsCode  amount_land  amount_water   latitude  \\\n",
       "0             0  80010085053     626719.0             0  39.896425   \n",
       "1             1  80010085061     438783.0         39928  39.910950   \n",
       "2             2  80010085063     983855.0             0  39.905276   \n",
       "3             4  80010085071     776717.0             0  39.904478   \n",
       "4             5  80010085072     476945.0             0  39.904002   \n",
       "\n",
       "    longitude                              venue_id                   brand  \\\n",
       "0 -104.972767  dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca  Sprouts Farmers Market   \n",
       "1 -104.981807  dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca  Sprouts Farmers Market   \n",
       "2 -104.985711  dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca  Sprouts Farmers Market   \n",
       "3 -104.963450  dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca  Sprouts Farmers Market   \n",
       "4 -104.970332  dc01b8c5-8ff5-40fa-9e58-f8d8cc888cca  Sprouts Farmers Market   \n",
       "\n",
       "  state_x      city  ...    work_air_distance_kilometers_median  \\\n",
       "0      CO  Thornton  ...                                     10   \n",
       "1      CO  Thornton  ...                                     10   \n",
       "2      CO  Thornton  ...                                     10   \n",
       "3      CO  Thornton  ...                                     10   \n",
       "4      CO  Thornton  ...                                     10   \n",
       "\n",
       "   work_air_distance_25th_kilometers_pct  \\\n",
       "0                                      4   \n",
       "1                                      4   \n",
       "2                                      4   \n",
       "3                                      4   \n",
       "4                                      4   \n",
       "\n",
       "   work_air_distance_75th_kilometers_pct  traffic_fraction  people_fraction  \\\n",
       "0                                     20             0.005            0.007   \n",
       "1                                     20             0.006            0.004   \n",
       "2                                     20             0.009            0.009   \n",
       "3                                     20             0.004            0.007   \n",
       "4                                     20             0.010            0.008   \n",
       "\n",
       "   venue_air_distance  population  Unnamed: 0_y  state_y  count  \n",
       "0               1.903        1647            25       CO      1  \n",
       "1               0.471        1427            25       CO      1  \n",
       "2               1.159        3310            25       CO      1  \n",
       "3               0.946        2204            25       CO      1  \n",
       "4               0.897        1364            25       CO      1  \n",
       "\n",
       "[5 rows x 24 columns]"
      ]
     },
     "execution_count": 155,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tmp_data = 'loc_data.csv'\n",
    "mbc_data = 'market_count_by_city.csv'\n",
    "\n",
    "tmp = pd.read_csv(tmp_data)\n",
    "mbc = pd.read_csv(mbc_data)\n",
    "\n",
    "addc = pd.merge(tmp,\n",
    "                 mbc,\n",
    "                 on='city')\n",
    "addc.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "metadata": {},
   "outputs": [],
   "source": [
    "addc.to_csv('count_file.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 169,
   "metadata": {},
   "outputs": [],
   "source": [
    "addc.columns = ['Unnamed: 0_x', 'fipsCode', 'amount_land', 'amount_water', 'latitude',\n",
    "       'longitude', 'venue_id', 'brand', 'state_x', 'city',\n",
    "       'daily_traffic_avg', 'home_air_distance_kilometers_median',\n",
    "       'home_air_distance_kilometers_25th_pct',\n",
    "       'home_air_distance_75th_kilometers_pct',\n",
    "       'work_air_distance_kilometers_median',\n",
    "       'work_air_distance_25th_kilometers_pct',\n",
    "       'work_air_distance_75th_kilometers_pct', 'traffic_fraction',\n",
    "       'people_fraction', 'venue_air_distance', 'population', 'Unnamed: 0_y',\n",
    "       'state_y', 'map_count_by_city']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "metadata": {},
   "outputs": [],
   "source": [
    "avg_data = \"info_avg_by_city.csv\"\n",
    "avg = pd.read_csv(avg_data)\n",
    "avg.columns = ['Unnamed: 0', 'state', 'city', 'daily_traffic_avg_avg',\n",
    "       'home_air_distance_kilometers_median_avg',\n",
    "       'home_air_distance_kilometers_25th_pct_avg',\n",
    "       'home_air_distance_75th_kilometers_pct_avg',\n",
    "       'work_air_distance_kilometers_median_avg',\n",
    "       'work_air_distance_25th_kilometers_pct_avg',\n",
    "       'work_air_distance_75th_kilometers_pct_avg']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "metadata": {},
   "outputs": [],
   "source": [
    "result = pd.merge(addc,\n",
    "             avg,   \n",
    "            on='city')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 175,
   "metadata": {},
   "outputs": [],
   "source": [
    "income = pd.read_csv('fipscode_income.csv')\n",
    "income.columns = ['fipsCode', 'income_mean', 'income_median']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 182,
   "metadata": {},
   "outputs": [],
   "source": [
    "result = pd.merge(result, income, on='fipsCode')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "metadata": {},
   "outputs": [],
   "source": [
    "result.to_csv(\"final.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}