wrangling.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Final Project: End-to-End Data Cleaning Workflow\n",
    "\n",
    "### Farmers Market Dataset\n",
    "\n",
    "> Author: *Luis Magana* (luism2)\n",
    "\n",
    "- Repository: https://github.com/maganaluis/CS513-FinalProject\n",
    "- Overview & Assesment: https://github.com/maganaluis/CS513-FinalProject/blob/master/InitialAssesment.pdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from IPython.display import display\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import re\n",
    "import calendar\n",
    "import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('farmers.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(8739, 59)"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape #(Rows, Cols)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Raw Data View"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": false
   },
   "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>FMID</th>\n",
       "      <th>MarketName</th>\n",
       "      <th>Website</th>\n",
       "      <th>Facebook</th>\n",
       "      <th>Twitter</th>\n",
       "      <th>Youtube</th>\n",
       "      <th>OtherMedia</th>\n",
       "      <th>street</th>\n",
       "      <th>city</th>\n",
       "      <th>County</th>\n",
       "      <th>State</th>\n",
       "      <th>zip</th>\n",
       "      <th>Season1Date</th>\n",
       "      <th>Season1Time</th>\n",
       "      <th>Season2Date</th>\n",
       "      <th>Season2Time</th>\n",
       "      <th>Season3Date</th>\n",
       "      <th>Season3Time</th>\n",
       "      <th>Season4Date</th>\n",
       "      <th>Season4Time</th>\n",
       "      <th>x</th>\n",
       "      <th>y</th>\n",
       "      <th>Location</th>\n",
       "      <th>Credit</th>\n",
       "      <th>WIC</th>\n",
       "      <th>WICcash</th>\n",
       "      <th>SFMNP</th>\n",
       "      <th>SNAP</th>\n",
       "      <th>Organic</th>\n",
       "      <th>Bakedgoods</th>\n",
       "      <th>Cheese</th>\n",
       "      <th>Crafts</th>\n",
       "      <th>Flowers</th>\n",
       "      <th>Eggs</th>\n",
       "      <th>Seafood</th>\n",
       "      <th>Herbs</th>\n",
       "      <th>Vegetables</th>\n",
       "      <th>Honey</th>\n",
       "      <th>Jams</th>\n",
       "      <th>Maple</th>\n",
       "      <th>Meat</th>\n",
       "      <th>Nursery</th>\n",
       "      <th>Nuts</th>\n",
       "      <th>Plants</th>\n",
       "      <th>Poultry</th>\n",
       "      <th>Prepared</th>\n",
       "      <th>Soap</th>\n",
       "      <th>Trees</th>\n",
       "      <th>Wine</th>\n",
       "      <th>Coffee</th>\n",
       "      <th>Beans</th>\n",
       "      <th>Fruits</th>\n",
       "      <th>Grains</th>\n",
       "      <th>Juices</th>\n",
       "      <th>Mushrooms</th>\n",
       "      <th>PetFood</th>\n",
       "      <th>Tofu</th>\n",
       "      <th>WildHarvested</th>\n",
       "      <th>updateTime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1018261</td>\n",
       "      <td>Caledonia Farmers Market Association - Danville</td>\n",
       "      <td>https://sites.google.com/site/caledoniafarmers...</td>\n",
       "      <td>https://www.facebook.com/Danville.VT.Farmers.M...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Danville</td>\n",
       "      <td>Caledonia</td>\n",
       "      <td>Vermont</td>\n",
       "      <td>05828</td>\n",
       "      <td>06/14/2017 to 08/30/2017</td>\n",
       "      <td>Wed: 9:00 AM-1:00 PM;</td>\n",
       "      <td>09/06/2017 to 10/18/2017</td>\n",
       "      <td>Wed: 2:00 PM-6:00 PM;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-72.140337</td>\n",
       "      <td>44.411036</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>6/20/2017 10:43:57 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1018318</td>\n",
       "      <td>Stearns Homestead Farmers' Market</td>\n",
       "      <td>http://www.StearnsHomestead.com</td>\n",
       "      <td>StearnsHomesteadFarmersMarket</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6975 Ridge Road</td>\n",
       "      <td>Parma</td>\n",
       "      <td>Cuyahoga</td>\n",
       "      <td>Ohio</td>\n",
       "      <td>NaN</td>\n",
       "      <td>06/24/2017 to 09/30/2017</td>\n",
       "      <td>Sat: 9:00 AM-1:00 PM;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-81.733939</td>\n",
       "      <td>41.374801</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>-</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>6/21/2017 5:15:01 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1009364</td>\n",
       "      <td>106 S. Main Street Farmers Market</td>\n",
       "      <td>http://thetownofsixmile.wordpress.com/</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>106 S. Main Street</td>\n",
       "      <td>Six Mile</td>\n",
       "      <td>NaN</td>\n",
       "      <td>South Carolina</td>\n",
       "      <td>29682</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-82.818700</td>\n",
       "      <td>34.804200</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>-</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1010691</td>\n",
       "      <td>10th Steet Community Farmers Market</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>http://agrimissouri.com/mo-grown/grodetail.php...</td>\n",
       "      <td>10th Street and Poplar</td>\n",
       "      <td>Lamar</td>\n",
       "      <td>Barton</td>\n",
       "      <td>Missouri</td>\n",
       "      <td>64759</td>\n",
       "      <td>04/02/2014 to 11/30/2014</td>\n",
       "      <td>Wed: 3:00 PM-6:00 PM;Sat: 8:00 AM-1:00 PM;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-94.274619</td>\n",
       "      <td>37.495628</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>-</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>10/28/2014 9:49:46 AM</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      FMID                                        MarketName  \\\n",
       "0  1018261   Caledonia Farmers Market Association - Danville   \n",
       "1  1018318                 Stearns Homestead Farmers' Market   \n",
       "2  1009364                 106 S. Main Street Farmers Market   \n",
       "3  1010691               10th Steet Community Farmers Market   \n",
       "\n",
       "                                             Website  \\\n",
       "0  https://sites.google.com/site/caledoniafarmers...   \n",
       "1                    http://www.StearnsHomestead.com   \n",
       "2             http://thetownofsixmile.wordpress.com/   \n",
       "3                                                NaN   \n",
       "\n",
       "                                            Facebook Twitter Youtube  \\\n",
       "0  https://www.facebook.com/Danville.VT.Farmers.M...     NaN     NaN   \n",
       "1                      StearnsHomesteadFarmersMarket     NaN     NaN   \n",
       "2                                                NaN     NaN     NaN   \n",
       "3                                                NaN     NaN     NaN   \n",
       "\n",
       "                                          OtherMedia                  street  \\\n",
       "0                                                NaN                     NaN   \n",
       "1                                                NaN         6975 Ridge Road   \n",
       "2                                                NaN      106 S. Main Street   \n",
       "3  http://agrimissouri.com/mo-grown/grodetail.php...  10th Street and Poplar   \n",
       "\n",
       "       city     County           State    zip               Season1Date  \\\n",
       "0  Danville  Caledonia         Vermont  05828  06/14/2017 to 08/30/2017   \n",
       "1    Parma    Cuyahoga            Ohio    NaN  06/24/2017 to 09/30/2017   \n",
       "2  Six Mile        NaN  South Carolina  29682                       NaN   \n",
       "3    Lamar      Barton        Missouri  64759  04/02/2014 to 11/30/2014   \n",
       "\n",
       "                                  Season1Time               Season2Date  \\\n",
       "0                       Wed: 9:00 AM-1:00 PM;  09/06/2017 to 10/18/2017   \n",
       "1                       Sat: 9:00 AM-1:00 PM;                       NaN   \n",
       "2                                         NaN                       NaN   \n",
       "3  Wed: 3:00 PM-6:00 PM;Sat: 8:00 AM-1:00 PM;                       NaN   \n",
       "\n",
       "             Season2Time Season3Date Season3Time Season4Date Season4Time  \\\n",
       "0  Wed: 2:00 PM-6:00 PM;         NaN         NaN         NaN         NaN   \n",
       "1                    NaN         NaN         NaN         NaN         NaN   \n",
       "2                    NaN         NaN         NaN         NaN         NaN   \n",
       "3                    NaN         NaN         NaN         NaN         NaN   \n",
       "\n",
       "           x          y Location Credit WIC WICcash SFMNP SNAP Organic  \\\n",
       "0 -72.140337  44.411036      NaN      Y   Y       N     Y    N       Y   \n",
       "1 -81.733939  41.374801      NaN      Y   N       N     Y    N       -   \n",
       "2 -82.818700  34.804200      NaN      Y   N       N     N    N       -   \n",
       "3 -94.274619  37.495628      NaN      Y   N       N     N    N       -   \n",
       "\n",
       "  Bakedgoods Cheese Crafts Flowers Eggs Seafood Herbs Vegetables Honey Jams  \\\n",
       "0          Y      Y      Y       Y    Y       N     Y          Y     Y    Y   \n",
       "1          Y      N      Y       Y    Y       N     Y          Y     Y    Y   \n",
       "2        NaN    NaN    NaN     NaN  NaN     NaN   NaN        NaN   NaN  NaN   \n",
       "3          Y      N      Y       N    Y       N     Y          Y     Y    Y   \n",
       "\n",
       "  Maple Meat Nursery Nuts Plants Poultry Prepared Soap Trees Wine Coffee  \\\n",
       "0     Y    Y       N    N      N       Y        Y    Y     Y    N      Y   \n",
       "1     Y    N       N    N      N       Y        N    Y     N    N      N   \n",
       "2   NaN  NaN     NaN  NaN    NaN     NaN      NaN  NaN   NaN  NaN    NaN   \n",
       "3     N    Y       N    N      Y       Y        Y    Y     N    N      N   \n",
       "\n",
       "  Beans Fruits Grains Juices Mushrooms PetFood Tofu WildHarvested  \\\n",
       "0     Y      Y      N      N         Y       Y    N             N   \n",
       "1     N      Y      N      N         N       N    N             N   \n",
       "2   NaN    NaN    NaN    NaN       NaN     NaN  NaN           NaN   \n",
       "3     N      Y      N      N         N       N    N             N   \n",
       "\n",
       "              updateTime  \n",
       "0  6/20/2017 10:43:57 PM  \n",
       "1   6/21/2017 5:15:01 PM  \n",
       "2                   2013  \n",
       "3  10/28/2014 9:49:46 AM  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "with pd.option_context('display.max_columns', None):\n",
    "    display(df.head(4))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Cleaning\n",
    "\n",
    "\n",
    "All of my data cleaning can be done with Pandas, since the dataset is small enough and is just one table. In order to clean the data and ensure it is on a usable format for the end user I've performed the following operations:\n",
    "\n",
    "- Replace all nan values with '' (empty strings), as it is easier to handle strings with Python.\n",
    "- Ensure updateTime is of DATETIME type and then format it to YYYY-MM-DD \n",
    "- Create FullAddress from columns street, city, zip and State only if they all are present, otherwise\n",
    "  set the row to empty.\n",
    "- Ensure that only Y or N are used in all bin columns, which are the products that are offered in the market. \n",
    "  Any value that is not Y defaults to N.\n",
    "- Using regex ensure that the columns Website, Facebook and Youtube follow a URL format.\n",
    "- Ensure that Twitter uses @username format.\n",
    "- Ensure that season date uses a MM/DD/YYYY to MM/DD/YYYY format and that its season time is present."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#replace all nan values with empty strings for now\n",
    "df = df.replace(np.nan, '', regex=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "#ensure dates are properly formatted\n",
    "df['updateTime'] = pd.to_datetime(df['updateTime']) #does the heavy lifting\n",
    "df['updateTime'] = df['updateTime'].apply(lambda x: x.strftime(\"%Y-%m-%d\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "def form_address(rows):\n",
    "    \"\"\"\n",
    "    Form address will return the address using the columns\n",
    "    listed below, if any of the columns is not available\n",
    "    it will return an empty string. \n",
    "    \"\"\"\n",
    "    cols = ['street', 'city', 'State', 'zip']\n",
    "    if any([not rows[c] for c in cols]):\n",
    "        return ''\n",
    "    else:\n",
    "        return ', '.join([rows[c] for c in cols])\n",
    "\n",
    "#create standard address\n",
    "address = df.apply(form_address, axis=1)\n",
    "idx = df.columns.get_loc('zip') + 1\n",
    "df.insert(idx, 'FullAddress', address)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "#ensure bin columns are Y or N, if they are not Y defaults to N\n",
    "bin_cols = df.iloc[:,23:-1].columns\n",
    "for col in bin_cols:\n",
    "    mask = mask = np.argwhere(df[col] != 'Y').ravel().tolist()\n",
    "    df.loc[mask, col] = \"N\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "def is_valid_url(url):\n",
    "    \"\"\"\n",
    "    Takes any string and returns it if it's a valid url\n",
    "    returns and empty string otherwise.\n",
    "    \"\"\"\n",
    "    regex = re.compile(\n",
    "        r'^https?://'  # http:// or https://\n",
    "        r'(?:(?:[A-Z0-9](?:[A-Z0-9-]{0,61}[A-Z0-9])?\\.)+[A-Z]{2,6}\\.?|'  # domain...\n",
    "        r'\\d{1,3}\\.\\d{1,3}\\.\\d{1,3}\\.\\d{1,3})' # ...or ip\n",
    "        r'(?::\\d+)?'  # optional port\n",
    "        r'(?:/?|[/?]\\S+)$', re.IGNORECASE)\n",
    "    if regex.search(url):\n",
    "        return url\n",
    "    else:\n",
    "        return ''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "#validate website, facebook, and youtube\n",
    "df['Facebook'] = df['Facebook'].apply(is_valid_url)\n",
    "df['Website'] = df['Website'].apply(is_valid_url)\n",
    "df['Youtube'] = df['Youtube'].apply(is_valid_url)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "#ensure twitter uses @\n",
    "def format_twitter(row):\n",
    "    if 'https://twitter.com/' in row:\n",
    "        return row.replace('https://twitter.com/', '@')\n",
    "    elif row and '@' not in row:\n",
    "        return '@' + row\n",
    "    elif '@' in row:\n",
    "        return row\n",
    "    else:\n",
    "        return ''\n",
    "\n",
    "df['Twitter'] = df['Twitter'].apply(format_twitter)\n",
    "#othermedia can stay as is, since it's a free form input"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "#drop x, y geo locations since a lot appear to be incorrect\n",
    "df = df.drop(columns=['x','y'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "def is_valid_season(row):\n",
    "    \"\"\"\n",
    "    Returns the row if the season in the format \n",
    "    \"%m/%d/%Y\" to \"%m/%d/%Y\", otherwise it sends the \n",
    "    row to another function for further inspecting.\n",
    "    \"\"\"\n",
    "    pattern = re.compile(\"\\d+/\\d+/\\d+ to \\d+/\\d+/\\d+\")\n",
    "    if pattern.match(row.strip()):\n",
    "        return row\n",
    "    else:\n",
    "        return get_valid_season(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_valid_season(row):\n",
    "    \"\"\"\n",
    "    This is the second step for season dates inspection, \n",
    "    if the season is formatted with names of months it will use\n",
    "    the current year for the season, otherwise it will send an \n",
    "    empty string back.\n",
    "    \"\"\"\n",
    "    vals = row.strip().split(\" to \")\n",
    "    months = {v: k for k,v in enumerate(calendar.month_name)}\n",
    "    del months[''] #empty not a valid month\n",
    "    if not vals:\n",
    "        return ''\n",
    "    elif not all([val in list(months.keys()) for val in vals]):\n",
    "        return ''\n",
    "    elif len(vals) > 2: #we are only expecting two\n",
    "        return ''\n",
    "    else:\n",
    "        year = datetime.datetime.now().year\n",
    "        dt1 = datetime.datetime(year=year, month=months[vals[0]], day=1)\n",
    "        dt2 = datetime.datetime(year=year, month=months[vals[1]], day=1)\n",
    "        return dt1.strftime(\"%m/%d/%Y\") + \" to \" + dt2.strftime(\"%m/%d/%Y\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "#validate and correct all seasons dates\n",
    "df['Season1Date'] = df['Season1Date'].apply(is_valid_season)\n",
    "df['Season2Date'] = df['Season2Date'].apply(is_valid_season)\n",
    "df['Season3Date'] = df['Season3Date'].apply(is_valid_season)\n",
    "df['Season4Date'] = df['Season4Date'].apply(is_valid_season)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## YesWorkFlow Model Graph\n",
    "\n",
    "Below you can see the overall structure of the code, and you can find the source code for this model in the github repository as **WorkFlow.py**\n",
    "\n",
    "The key input of this model is *farmers.csv*, which is fed into read_csv a powerful pandas connector. From there output is saved as a DataFrame, which is the basic data structure of pandas. The DataFrame will be fed into different methods that take a row, and return an output in functional programming fashion.\n",
    "\n",
    "Each method is independent and will write back to the same DataFrame with exception of `Drop_Geo_Locators` which doesn't provide an actual output. In the end the same DataFrame which will be used to create a new *CleanFarmers.csv*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img style=\"height:500px;\" src=\"https://raw.githubusercontent.com/maganaluis/CS513-FinalProject/master/FarmersWf.png\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Clean Data View"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "scrolled": false
   },
   "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>FMID</th>\n",
       "      <th>MarketName</th>\n",
       "      <th>Website</th>\n",
       "      <th>Facebook</th>\n",
       "      <th>Twitter</th>\n",
       "      <th>Youtube</th>\n",
       "      <th>OtherMedia</th>\n",
       "      <th>street</th>\n",
       "      <th>city</th>\n",
       "      <th>County</th>\n",
       "      <th>State</th>\n",
       "      <th>zip</th>\n",
       "      <th>FullAddress</th>\n",
       "      <th>Season1Date</th>\n",
       "      <th>Season1Time</th>\n",
       "      <th>Season2Date</th>\n",
       "      <th>Season2Time</th>\n",
       "      <th>Season3Date</th>\n",
       "      <th>Season3Time</th>\n",
       "      <th>Season4Date</th>\n",
       "      <th>Season4Time</th>\n",
       "      <th>Location</th>\n",
       "      <th>Credit</th>\n",
       "      <th>WIC</th>\n",
       "      <th>WICcash</th>\n",
       "      <th>SFMNP</th>\n",
       "      <th>SNAP</th>\n",
       "      <th>Organic</th>\n",
       "      <th>Bakedgoods</th>\n",
       "      <th>Cheese</th>\n",
       "      <th>Crafts</th>\n",
       "      <th>Flowers</th>\n",
       "      <th>Eggs</th>\n",
       "      <th>Seafood</th>\n",
       "      <th>Herbs</th>\n",
       "      <th>Vegetables</th>\n",
       "      <th>Honey</th>\n",
       "      <th>Jams</th>\n",
       "      <th>Maple</th>\n",
       "      <th>Meat</th>\n",
       "      <th>Nursery</th>\n",
       "      <th>Nuts</th>\n",
       "      <th>Plants</th>\n",
       "      <th>Poultry</th>\n",
       "      <th>Prepared</th>\n",
       "      <th>Soap</th>\n",
       "      <th>Trees</th>\n",
       "      <th>Wine</th>\n",
       "      <th>Coffee</th>\n",
       "      <th>Beans</th>\n",
       "      <th>Fruits</th>\n",
       "      <th>Grains</th>\n",
       "      <th>Juices</th>\n",
       "      <th>Mushrooms</th>\n",
       "      <th>PetFood</th>\n",
       "      <th>Tofu</th>\n",
       "      <th>WildHarvested</th>\n",
       "      <th>updateTime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1018261</td>\n",
       "      <td>Caledonia Farmers Market Association - Danville</td>\n",
       "      <td>https://sites.google.com/site/caledoniafarmers...</td>\n",
       "      <td>https://www.facebook.com/Danville.VT.Farmers.M...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Danville</td>\n",
       "      <td>Caledonia</td>\n",
       "      <td>Vermont</td>\n",
       "      <td>05828</td>\n",
       "      <td></td>\n",
       "      <td>06/14/2017 to 08/30/2017</td>\n",
       "      <td>Wed: 9:00 AM-1:00 PM;</td>\n",
       "      <td>09/06/2017 to 10/18/2017</td>\n",
       "      <td>Wed: 2:00 PM-6:00 PM;</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>2017-06-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1018318</td>\n",
       "      <td>Stearns Homestead Farmers' Market</td>\n",
       "      <td>http://www.StearnsHomestead.com</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>6975 Ridge Road</td>\n",
       "      <td>Parma</td>\n",
       "      <td>Cuyahoga</td>\n",
       "      <td>Ohio</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>06/24/2017 to 09/30/2017</td>\n",
       "      <td>Sat: 9:00 AM-1:00 PM;</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>2017-06-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1009364</td>\n",
       "      <td>106 S. Main Street Farmers Market</td>\n",
       "      <td>http://thetownofsixmile.wordpress.com/</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>106 S. Main Street</td>\n",
       "      <td>Six Mile</td>\n",
       "      <td></td>\n",
       "      <td>South Carolina</td>\n",
       "      <td>29682</td>\n",
       "      <td>106 S. Main Street, Six Mile, South Carolina, ...</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>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>2013-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1010691</td>\n",
       "      <td>10th Steet Community Farmers Market</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>http://agrimissouri.com/mo-grown/grodetail.php...</td>\n",
       "      <td>10th Street and Poplar</td>\n",
       "      <td>Lamar</td>\n",
       "      <td>Barton</td>\n",
       "      <td>Missouri</td>\n",
       "      <td>64759</td>\n",
       "      <td>10th Street and Poplar, Lamar , Missouri, 64759</td>\n",
       "      <td>04/02/2014 to 11/30/2014</td>\n",
       "      <td>Wed: 3:00 PM-6:00 PM;Sat: 8:00 AM-1:00 PM;</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>2014-10-28</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      FMID                                        MarketName  \\\n",
       "0  1018261   Caledonia Farmers Market Association - Danville   \n",
       "1  1018318                 Stearns Homestead Farmers' Market   \n",
       "2  1009364                 106 S. Main Street Farmers Market   \n",
       "3  1010691               10th Steet Community Farmers Market   \n",
       "\n",
       "                                             Website  \\\n",
       "0  https://sites.google.com/site/caledoniafarmers...   \n",
       "1                    http://www.StearnsHomestead.com   \n",
       "2             http://thetownofsixmile.wordpress.com/   \n",
       "3                                                      \n",
       "\n",
       "                                            Facebook Twitter Youtube  \\\n",
       "0  https://www.facebook.com/Danville.VT.Farmers.M...                   \n",
       "1                                                                      \n",
       "2                                                                      \n",
       "3                                                                      \n",
       "\n",
       "                                          OtherMedia                  street  \\\n",
       "0                                                                              \n",
       "1                                                            6975 Ridge Road   \n",
       "2                                                         106 S. Main Street   \n",
       "3  http://agrimissouri.com/mo-grown/grodetail.php...  10th Street and Poplar   \n",
       "\n",
       "       city     County           State    zip  \\\n",
       "0  Danville  Caledonia         Vermont  05828   \n",
       "1    Parma    Cuyahoga            Ohio          \n",
       "2  Six Mile             South Carolina  29682   \n",
       "3    Lamar      Barton        Missouri  64759   \n",
       "\n",
       "                                         FullAddress  \\\n",
       "0                                                      \n",
       "1                                                      \n",
       "2  106 S. Main Street, Six Mile, South Carolina, ...   \n",
       "3    10th Street and Poplar, Lamar , Missouri, 64759   \n",
       "\n",
       "                Season1Date                                 Season1Time  \\\n",
       "0  06/14/2017 to 08/30/2017                       Wed: 9:00 AM-1:00 PM;   \n",
       "1  06/24/2017 to 09/30/2017                       Sat: 9:00 AM-1:00 PM;   \n",
       "2                                                                         \n",
       "3  04/02/2014 to 11/30/2014  Wed: 3:00 PM-6:00 PM;Sat: 8:00 AM-1:00 PM;   \n",
       "\n",
       "                Season2Date            Season2Time Season3Date Season3Time  \\\n",
       "0  09/06/2017 to 10/18/2017  Wed: 2:00 PM-6:00 PM;                           \n",
       "1                                                                            \n",
       "2                                                                            \n",
       "3                                                                            \n",
       "\n",
       "  Season4Date Season4Time Location Credit WIC WICcash SFMNP SNAP Organic  \\\n",
       "0                                N      Y   Y       N     Y    N       Y   \n",
       "1                                N      Y   N       N     Y    N       N   \n",
       "2                                N      Y   N       N     N    N       N   \n",
       "3                                N      Y   N       N     N    N       N   \n",
       "\n",
       "  Bakedgoods Cheese Crafts Flowers Eggs Seafood Herbs Vegetables Honey Jams  \\\n",
       "0          Y      Y      Y       Y    Y       N     Y          Y     Y    Y   \n",
       "1          Y      N      Y       Y    Y       N     Y          Y     Y    Y   \n",
       "2          N      N      N       N    N       N     N          N     N    N   \n",
       "3          Y      N      Y       N    Y       N     Y          Y     Y    Y   \n",
       "\n",
       "  Maple Meat Nursery Nuts Plants Poultry Prepared Soap Trees Wine Coffee  \\\n",
       "0     Y    Y       N    N      N       Y        Y    Y     Y    N      Y   \n",
       "1     Y    N       N    N      N       Y        N    Y     N    N      N   \n",
       "2     N    N       N    N      N       N        N    N     N    N      N   \n",
       "3     N    Y       N    N      Y       Y        Y    Y     N    N      N   \n",
       "\n",
       "  Beans Fruits Grains Juices Mushrooms PetFood Tofu WildHarvested  updateTime  \n",
       "0     Y      Y      N      N         Y       Y    N             N  2017-06-20  \n",
       "1     N      Y      N      N         N       N    N             N  2017-06-21  \n",
       "2     N      N      N      N         N       N    N             N  2013-01-01  \n",
       "3     N      Y      N      N         N       N    N             N  2014-10-28  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "with pd.option_context('display.max_columns', None):\n",
    "    display(df.head(4))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Logical Integrity Constraints (ICs)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With Pandas is easy to create a database schema by using \"create_engine\" and save the farmers.db file for later use. In this case I've identified the following integrity constrains that I think we should enforce:\n",
    "\n",
    "- FMID must be unique\n",
    "- All rows must have a MarketName\n",
    "- All markets must have at least one season \n",
    "- If a market has one season they must provide a time\n",
    "- All markets must provide city and state\n",
    "- If a market doesn't provide an address it must provide at least a form of media\n",
    "\n",
    "> Note: We are using the __clean__ DataFrame for this section."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "engine = create_engine('sqlite:///farmers.db') #Notice is an SQLite Engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.replace('', np.nan, inplace=True) #set back empty strings to NAN, it is easier to handle these on SQL\n",
    "df.to_sql('farmers', con=engine, if_exists='replace', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First we need to make sure that FMID entries are unique, altough I've verified this on Pandas we can also check with SQL and as we can see all of them are."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('column values are unique',)]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "engine.execute(\"\"\"SELECT CASE WHEN COUNT(DISTINCT FMID)= COUNT(FMID)\n",
    "                         THEN 'column values are unique' ELSE 'column values are NOT unique' END\n",
    "                         FROM farmers\"\"\").fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We need to ensure that all markets have a name, it would be rare to see one without it and as we can see they all do. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(0,)]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "engine.execute(\"\"\"SELECT COUNT(*) FROM farmers WHERE MarketName IS NULL\"\"\").fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next we need to make sure that every market has at least one season, this lets the user know when the market is available or open without having to check their website. We can see that 3299 of them don't have any season. \n",
    "\n",
    "Because it is a large number we should not drop these items, but ensure that at least one season is entered in future entries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(3299,)]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "engine.execute(\"\"\"SELECT COUNT(*) FROM farmers AS least_one_season WHERE\n",
    "                          ((CASE WHEN Season1Date IS NULL THEN 0 ELSE 1 END) +\n",
    "                          (CASE WHEN Season2Date IS NULL THEN 0 ELSE 1 END) +\n",
    "                          (CASE WHEN Season3Date IS NULL THEN 0 ELSE 1 END) +\n",
    "                          (CASE WHEN Season4Date IS NULL THEN 0 ELSE 1 END)) = 0\n",
    "                           \"\"\").fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now from the entries that have at least one season we must find the the ones that do not have at least one season time. As we can see there are 81 entries, for which we can obtain the FMID and then decide if we need to drop them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(81,)]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "engine.execute(\"\"\"SELECT COUNT(*) FROM\n",
    "                    ((SELECT * FROM farmers WHERE\n",
    "                          ((CASE WHEN Season1Date IS NULL THEN 0 ELSE 1 END) +\n",
    "                           (CASE WHEN Season2Date IS NULL THEN 0 ELSE 1 END) +\n",
    "                           (CASE WHEN Season3Date IS NULL THEN 0 ELSE 1 END) +\n",
    "                           (CASE WHEN Season4Date IS NULL THEN 0 ELSE 1 END)) > 0) AS LSONE)\n",
    "                    WHERE \n",
    "                          ((CASE WHEN LSONE.Season1Time IS NULL THEN 0 ELSE 1 END) +\n",
    "                           (CASE WHEN LSONE.Season2Time IS NULL THEN 0 ELSE 1 END) +\n",
    "                           (CASE WHEN LSONE.Season3Time IS NULL THEN 0 ELSE 1 END) +\n",
    "                           (CASE WHEN LSONE.Season3Time IS NULL THEN 0 ELSE 1 END)) = 0\n",
    "                           \"\"\").fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since we need to know where the market is located, we need to ensure they provide at very least a city and a state. In this case we've found 40 entries that don't meet this criteria. We can also obtain the FMID and drop these entries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(40,)]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "engine.execute(\"\"\"SELECT COUNT(*) FROM farmers\n",
    "                    WHERE city IS NULL OR State IS NULL\"\"\").fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally some markets can have multiple locations (streets) per season, therefore we can allow entries not to contain a street. If this is the case we assume that they are providing the end-user the location in their website or social media. In this case we found 117 entries that do not have a street and do not provide any social media."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(117,)]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "engine.execute(\"\"\"SELECT COUNT(*) FROM \n",
    "                  ((SELECT * FROM farmers WHERE\n",
    "                  ((CASE WHEN Website IS NULL THEN 0 ELSE 1 END) +\n",
    "                   (CASE WHEN Facebook IS NULL THEN 0 ELSE 1 END) +\n",
    "                   (CASE WHEN Twitter IS NULL THEN 0 ELSE 1 END) +\n",
    "                   (CASE WHEN Youtube IS NULL THEN 0 ELSE 1 END) +\n",
    "                   (CASE WHEN OtherMedia IS NULL THEN 0 ELSE 1 END)) = 0) AS LSONE)\n",
    "                   WHERE LSONE.street IS NULL\"\"\").fetchall()"
   ]
  }
 ],
 "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.7.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}