1 - Data Collection.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1 - Data Collection"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Download USDA data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Create folders if necessary.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import parameters\n",
    "\n",
    "if not os.path.exists(parameters.download_folder):\n",
    "    os.makedirs(parameters.download_folder)\n",
    "    \n",
    "if not os.path.exists(parameters.download_backup):\n",
    "    os.makedirs(parameters.download_backup)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Set download folder and start Selenium webdriver.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import parameters\n",
    "from selenium import webdriver\n",
    "\n",
    "chromeOptions = webdriver.ChromeOptions()\n",
    "prefs = {\"download.default_directory\" : parameters.download_folder}\n",
    "chromeOptions.add_experimental_option(\"prefs\",prefs)\n",
    "chromedriver = \"chromedriver\"\n",
    "driver = webdriver.Chrome(executable_path=parameters.chromedriver_location, options=chromeOptions)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Empty download folder into backup folder.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "\n",
    "files = os.listdir(parameters.download_folder)\n",
    "for file in files:\n",
    "    os.rename(parameters.download_folder + '/' + file,\n",
    "              parameters.download_backup + '/' + file)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Navigate to USDA Organic Integrity website.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "from time import sleep\n",
    "usda_website = 'https://organic.ams.usda.gov/integrity/Search.aspx'\n",
    "\n",
    "driver.get(usda_website)\n",
    "sleep(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Include surrendered and suspended certifications in search.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "driver.find_elements_by_class_name('rlbCheckAllItems')[0].click()\n",
    "sleep(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Limit search to \"grapes, wine\" and California.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "searchbox = driver.find_element_by_name(name=\"ctl00$MainContent$RadDock5$C$autoCompleteCertifiedProducts\")\n",
    "searchbox.send_keys('wine,grapes,')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "from selenium.webdriver.common.keys import Keys\n",
    "statebox = driver.find_element_by_name('ctl00$MainContent$RadDock3$C$AutoCompleteBoxStates')\n",
    "\n",
    "statebox.send_keys('CA-California')\n",
    "sleep(3)\n",
    "statebox.send_keys(Keys.RETURN)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "driver.find_element_by_id('ctl00_MainContent_btnSrch1_input').click()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Download.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "driver.find_element_by_id('MainContent_lbtnExportToExcel').click()\n",
    "sleep(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['OID.OperationSearchResults.2019.1.11.8_31 AM.xlsx']"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "os.listdir('./downloads/')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "driver.quit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    ".head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Download history of biodynamic vineyards & wineries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%%bash\n",
    "    scrapy crawl BiodynamicHistorySpider -o downloads/biodynamic_history.csv 2> downloads/biodynamic_history_log.txt"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Download data from CCOF website on certified organic vineyards & wineries"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "CCOF data is also included in the USDA data, but maybe we can get some more information on some of them. E.g., from the data we can extrapulate which vineyards make wine from their organic farm that is not certified."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bash\n",
    "    scrapy crawl CCOFSpider -o downloads/ccof.csv 2> downloads/ccof_log.txt"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Download LODI Rules data on certified sustainable vineyards"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bash\n",
    "    scrapy crawl LODISpider -o downloads/lodi.csv 2> downloads/lodi_log.txt"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "wine_research",
   "language": "python",
   "name": "wine_research"
  },
  "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.7"
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}