notebooks/01 - Data Wrangling.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 01 - Data Wrangling 🧹\n",
    "\n",
    "## Problem\n",
    "\n",
    "How can we generate short descriptions including a list of popular beer styles from brewery reviews with natural language processing (NLP)? \n",
    "\n",
    "NOTE: This is similar to how Google Maps provides short blurbs for businesses such as “From scratch, Northern Italian dining.” \n",
    "\n",
    "For example, \"Spacious warehouse brewery with daily food trucks. Allows dogs. Features IPAs, Hazy IPAs, and high-gravity stouts.\"\n",
    "\n",
    "## Notebook Objectives\n",
    "\n",
    "* Clean the dataset and address issues like missing values and duplicate values. \n",
    "* Apply data wrangling techniques, as laid out in the DSM of; Data Collection, Data Organization, Data Definition, Data Cleaning.\n",
    "\n",
    "## Method\n",
    "\n",
    "This notebook will go through scraping the Beer Advocate site for breweries and reviews. \n",
    "\n",
    "1. Scrape **[Beer Advocate](https://www.beeradvocate.com)** Places for cities (86 cities)\n",
    "2. Loop through the cities and store Breweries (~5000 breweries)\n",
    "3. Loop through breweries and store Reviews\n",
    "\n",
    "All data will be stored in a SQLite DB.\n",
    "\n",
    "## DB Models\n",
    "\n",
    "* Cities\n",
    "    * id (Integer)\n",
    "    * name (String)\n",
    "    * url (String)\n",
    "    * ba_city_id (Integer)\n",
    "* Breweries\n",
    "    * id (Integer)\n",
    "    * name (String)\n",
    "    * street (String)\n",
    "    * city_id (Integer)\n",
    "    * url (String)\n",
    "    * ba_brewery_id (Integer)\n",
    "* Reviews\n",
    "    * id (Integer)\n",
    "    * text (Text)\n",
    "    * date (Datetime)\n",
    "    * rating (Float)\n",
    "    * ba_brewery_id (Integer)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import re\n",
    "import requests\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import time\n",
    "from datetime import datetime\n",
    "\n",
    "# Progress bar\n",
    "from tqdm import tqdm, trange\n",
    "\n",
    "# Beautiful Soup for HTML parsing\n",
    "from bs4 import BeautifulSoup\n",
    "\n",
    "# SQLAlchemy ORM for SQLite\n",
    "from sqlalchemy import create_engine, desc, Column, Date, Integer, Float, String, UnicodeText, Table\n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "from sqlalchemy.orm import sessionmaker"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Build the DB models declaratively (i.e., Pythonic way)\n",
    "Base = declarative_base()\n",
    "\n",
    "class City(Base):\n",
    "    __tablename__ = 'cities'\n",
    "    id = Column(Integer, primary_key=True)\n",
    "    name = Column(String)\n",
    "    url = Column(String)\n",
    "    ba_city_id = Column(Integer)\n",
    "    \n",
    "class Brewery(Base):\n",
    "    # FIXME: Rename to 'breweries'\n",
    "    __tablename__ = 'brewery'\n",
    "    id = Column(Integer, primary_key=True)\n",
    "    name = Column(String)\n",
    "    street = Column(String)\n",
    "    city_id = Column(Integer)\n",
    "    url = Column(String)\n",
    "    ba_brewery_id = Column(Integer)\n",
    "    \n",
    "class Review(Base):\n",
    "    __tablename__ = 'reviews'\n",
    "    id = Column(Integer, primary_key=True)\n",
    "    rating = Column(Float)\n",
    "    text = Column(UnicodeText)\n",
    "    date = Column(Date)\n",
    "    ba_brewery_id = Column(Integer)\n",
    "    \n",
    "engine = create_engine(f\"sqlite:///../data/processed/brewery_reviews.db\")\n",
    "\n",
    "# Set up the Session\n",
    "Session = sessionmaker()\n",
    "Session.configure(bind=engine)\n",
    "session = Session()\n",
    "\n",
    "# Ensure the Tables are created\n",
    "Base.metadata.create_all(engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of cities = 86\n",
      "Number of breweries = 4984\n",
      "Number of reviews = 45417\n"
     ]
    }
   ],
   "source": [
    "# Inspect DB counts\n",
    "num_cities = session.query(City).count()\n",
    "num_breweries = session.query(Brewery).count()\n",
    "num_reviews = session.query(Review).count()\n",
    "print(f\"Number of cities = {num_cities}\")\n",
    "print(f\"Number of breweries = {num_breweries}\")\n",
    "print(f\"Number of reviews = {num_reviews}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Setup variables\n",
    "ba_domain = \"https://www.beeradvocate.com\"\n",
    "places_url = \"https://www.beeradvocate.com/place/\"\n",
    "request_headers = {'User-agent': 'Springboard Capstone Project Research Tool by Chris Mears <chris.mears@gmail.com>'}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get the page and parse\n",
    "places_page = requests.get(places_url, headers=request_headers)\n",
    "places_soup = BeautifulSoup(places_page.text)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of Cities: 86\n"
     ]
    }
   ],
   "source": [
    "# Select all of the brewery items\n",
    "# <a href=\"/place/city/73/\">Anchorage</a>\n",
    "cities = places_soup.find_all(href=re.compile(\"\\/place\\/city\\/\"))\n",
    "print(f\"Number of Cities: {len(cities)}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Clean-up: Delete all rows in City\n",
    "# session.query(City).delete()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add cities to DB\n",
    "for city in cities:\n",
    "    name = city.string\n",
    "    url = f\"{ba_domain}{city['href']}\"\n",
    "    ba_city_id = re.match(r\"\\/place\\/city\\/(\\d+)\\/\", city['href']).group(1)\n",
    "    \n",
    "    # Skip if city already exists\n",
    "    if session.query(City).filter(City.ba_city_id==ba_city_id).first() is None:\n",
    "        new_city = City(name=name, url=url, ba_city_id=ba_city_id)\n",
    "        session.add(new_city)\n",
    "\n",
    "# Write to DB\n",
    "session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 Birmingham https://www.beeradvocate.com/place/city/83/ 83\n",
      "2 Anchorage https://www.beeradvocate.com/place/city/73/ 73\n",
      "3 Phoenix https://www.beeradvocate.com/place/city/29/ 29\n",
      "4 Los Angeles https://www.beeradvocate.com/place/city/9/ 9\n",
      "5 San Diego https://www.beeradvocate.com/place/city/28/ 28\n",
      "6 San Francisco https://www.beeradvocate.com/place/city/17/ 17\n",
      "7 Boulder https://www.beeradvocate.com/place/city/79/ 79\n",
      "8 Denver https://www.beeradvocate.com/place/city/7/ 7\n",
      "9 Fort Collins https://www.beeradvocate.com/place/city/53/ 53\n",
      "10 Hartford https://www.beeradvocate.com/place/city/38/ 38\n"
     ]
    }
   ],
   "source": [
    "# Inspect\n",
    "for city in session.query(City).limit(10):\n",
    "    print(city.id, city.name, city.url, city.ba_city_id)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 86/86 [02:30<00:00,  1.75s/it]\n"
     ]
    }
   ],
   "source": [
    "# Loop through City pages scraping Breweries (86 requests)\n",
    "cities = session.query(City).all()\n",
    "\n",
    "for city in tqdm(cities):\n",
    "    # Get city page and parse\n",
    "    city_page = requests.get(city.url, headers=request_headers)\n",
    "    city_soup = BeautifulSoup(city_page.text)    \n",
    "    \n",
    "    # Find breweries by URL regex pattern\n",
    "    breweries = city_soup.find(id=\"ba-content\").find_all(href=re.compile(\"\\/beer\\/profile\\/\"))\n",
    "    \n",
    "    # Loops through breweries, scrape, and add to DB\n",
    "    for brewery in breweries:\n",
    "        ba_brewery_id = re.match(r\"\\/beer\\/profile\\/(\\d+)\\/\", brewery['href']).group(1)\n",
    "        url = f\"{ba_domain}{brewery['href']}\"\n",
    "\n",
    "        try:\n",
    "            street_scrape = brewery.parent.find(class_='muted').get_text()\n",
    "            street = street_scrape.replace(' - ', '').replace(f\", {city.name}\", \"\")\n",
    "        except:\n",
    "            street = None\n",
    "        \n",
    "        # Skip if it already exists\n",
    "        if session.query(Brewery).filter(Brewery.ba_brewery_id==ba_brewery_id).first() is None:\n",
    "            new_brewery = Brewery(name=brewery.string, street=street, url=url, city_id=city.id, ba_brewery_id=ba_brewery_id)\n",
    "            session.add(new_brewery)\n",
    "\n",
    "        # Write to DB\n",
    "        session.commit()\n",
    "    \n",
    "    # Sleep for 1 sec (ethical scraping)\n",
    "    time.sleep(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "4984 PGW Produkter AB Cementvägen 34, Haninge https://www.beeradvocate.com/beer/profile/7219/?view=ratings 7219\n",
      "4983 Systembolaget - Passagen Regeringsgatan 44 https://www.beeradvocate.com/beer/profile/10916/?view=ratings 10916\n",
      "4982 Oliver Twist Repslagargatan 6 https://www.beeradvocate.com/beer/profile/5299/?view=ratings 5299\n",
      "4981 Belgobaren Bryggargatan 12 https://www.beeradvocate.com/beer/profile/10949/?view=ratings 10949\n",
      "4980 Man in the Moon Tegnérgatan 2 https://www.beeradvocate.com/beer/profile/21842/?view=ratings 21842\n",
      "4979 BrewDog Stockholm Sankt Eriksgatan 56 https://www.beeradvocate.com/beer/profile/31801/?view=ratings 31801\n",
      "4978 Duvel Café Vasagatan 50 https://www.beeradvocate.com/beer/profile/10522/?view=ratings 10522\n",
      "4977 Ardbeg Embassy Västerlångatan 68 https://www.beeradvocate.com/beer/profile/10906/?view=ratings 10906\n",
      "4976 Omnipollos Hatt Hökens gata 3 https://www.beeradvocate.com/beer/profile/41828/?view=ratings 41828\n",
      "4975 Akkurat Hornsgatan 18 https://www.beeradvocate.com/beer/profile/5292/?view=ratings 5292\n",
      "4974 Nya Carnegiebryggeriet Ljusslingan 15 https://www.beeradvocate.com/beer/profile/34391/ 34391\n",
      "4973 Schwarz Kaiser Brewery Moskvorechye ul., 31 https://www.beeradvocate.com/beer/profile/43432/?view=ratings 43432\n",
      "4972 Nemnoff Homebrewery Izmailovskoye Highway, 22. https://www.beeradvocate.com/beer/profile/23306/?view=ratings 23306\n",
      "4971 Craft RePUBlic Malyy Gnezdnikovskiy per., 9/8с7 https://www.beeradvocate.com/beer/profile/37737/?view=ratings 37737\n",
      "4970 Dogma Bottle Shop Bol’shoy Spasoglinishchenskiy Pereulok https://www.beeradvocate.com/beer/profile/45471/?view=ratings 45471\n",
      "4969 Method • Beer & Munchies Tovarischeskiy pereulok 19, str. 7 https://www.beeradvocate.com/beer/profile/54066/?view=ratings 54066\n",
      "4968 Sterk Avondverkoop De Clercqstraat 7 https://www.beeradvocate.com/beer/profile/41737/?view=ratings 41737\n",
      "4967 De Bierkoning Paleisstraat 125 https://www.beeradvocate.com/beer/profile/3971/?view=ratings 3971\n",
      "4966 De Brabantse Aap Spui 30 https://www.beeradvocate.com/beer/profile/7946/?view=ratings 7946\n",
      "4965 De Prael Proeflokaal Oudezijds Armsteeg 26 https://www.beeradvocate.com/beer/profile/31369/?view=ratings 31369\n",
      "4964 Café Belgique Gravenstraat 2 https://www.beeradvocate.com/beer/profile/1694/?view=ratings 1694\n",
      "4963 Biercafé Gollem Daniel Stalpertstraat 74 https://www.beeradvocate.com/beer/profile/25735/?view=ratings 25735\n",
      "4962 Het Elfde Gebod Zeedijk 5 https://www.beeradvocate.com/beer/profile/9478/?view=ratings 9478\n",
      "4961 Proeflokaal De Zotte 29 Raamstraat https://www.beeradvocate.com/beer/profile/7947/?view=ratings 7947\n",
      "4960 Gollem's Proeflokaal Overtoom 160-162 https://www.beeradvocate.com/beer/profile/25734/?view=ratings 25734\n"
     ]
    }
   ],
   "source": [
    "# Inspect scraped Breweries\n",
    "breweries = session.query(Brewery).order_by(desc(Brewery.id)).limit(25)\n",
    "for brewery in breweries:\n",
    "    print(brewery.id, brewery.name, brewery.street, brewery.url, brewery.ba_brewery_id)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Test things out by reading the 2nd brewery reviews page\n",
    "brewery = session.query(Brewery)[1]\n",
    "brewery_url = f\"{ba_domain}/beer/profile/{brewery.ba_brewery_id}/?view=ratings\"\n",
    "brewery_page = requests.get(brewery_url, headers=request_headers)\n",
    "brewery_soup = BeautifulSoup(brewery_page.text)\n",
    "reviews = brewery_soup.find_all(id=\"rating_fullview_content_2\")\n",
    "print(f\"Number of Reviews: {len(reviews)}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define regex pattern for user profile URLs\n",
    "def user_profile(href):\n",
    "    return href and re.compile(\"\\/beer\\/profile\\/\").search(href)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Scrape Brewery Reviews\n",
    "# NOTE: Scraping 500 breweries takes ~15 minutes\n",
    "\n",
    "# Set variables\n",
    "offset = 0\n",
    "n = 100\n",
    "breweries = session.query(Brewery).order_by(Brewery.id).offset(offset).limit(n)\n",
    "\n",
    "# Loop through breweries\n",
    "for i in trange(n):\n",
    "    # Request brewery page and parse\n",
    "    brewery = breweries[i]\n",
    "    brewery_url = f\"{ba_domain}/beer/profile/{brewery.ba_brewery_id}/?view=ratings\"\n",
    "    brewery_page = requests.get(brewery_url, headers=request_headers)\n",
    "    brewery_soup = BeautifulSoup(brewery_page.text)\n",
    "\n",
    "    # Get the reviews\n",
    "    reviews = brewery_soup.find_all(id=\"rating_fullview_content_2\")\n",
    "    \n",
    "    for review in reviews:\n",
    "        # Get the rating\n",
    "        rating = float(review.find(class_=\"BAscore_norm\").text)\n",
    "\n",
    "        try:\n",
    "            # Parse the date (linked via user's profile URL)\n",
    "            date_string = datetime.strptime(review.find(href=user_profile).text, \"%b %d, %Y\")\n",
    "        except:\n",
    "            date_string = datetime.now()\n",
    "\n",
    "        # Parse review text         \n",
    "        text = []\n",
    "        for sibling in review.find_all(class_=\"muted\")[1].next_siblings:\n",
    "            # Review text isn't wrapped in tags :shrug:\n",
    "            if sibling.name == None:\n",
    "                text.append(sibling)\n",
    "        review = \" \".join(text)\n",
    "\n",
    "        # DEBUG\n",
    "        # print(f\"Rating: {rating}\")\n",
    "        # print(f\"Date: {date_string}\")\n",
    "        # print(f\"Review: {review}\")\n",
    "        # print(f\"BA Brewery ID: {brewery.ba_brewery_id}\")\n",
    "\n",
    "        # Add Review to DB\n",
    "        new_review = Review(rating=rating, date=date_string, text=review, ba_brewery_id=brewery.ba_brewery_id)\n",
    "        session.add(new_review)\n",
    "\n",
    "    # Save Brewery Reviews\n",
    "    session.commit()\n",
    "    \n",
    "    # Sleep for 1 sec (ethical scraping)\n",
    "    time.sleep(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "45417"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check how many reviews we have\n",
    "len(session.query(Review).all())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "45417 4.3 Had the privilege to have a few drinks here while visiting Amsterdam in September 2011. 2012-01-13 25734\n",
      "45416 3.58 An American beer bar in Europe was intriguing enough to have to pay it a visit. It kind of had the vibe of a Belgian beer bar in America. Especially when it came to price, in Holland & Belgium their beers are very cheap compared to what we see in the US. Here the US beer prices were comparable to Belgian prices in the US.  2011-02-13 21770\n",
      "45415 4.58 This is a must stop for anyone visiting Amsterdam or locals who haven't been exposed to American craft beer. BeerTemple is small in size, but absolutely huge in terms of selecion. In fact, they have a better American craft selection than anywhere here in Nashville. 2011-02-28 21770\n",
      "45414 4.14 Beer temple was my final stop during my St Patrick's day Amsterdam beer bar crawl. It is best described as an American beer bar smack in the middle of Amsterdam. Founders, Jolly Pumpkin, Great Divide, Anchor, Left Hand, Flying Dog; basically all of the usual suspects for a solid American craft beer bar. They also have a very good selection of European/Belgian beers, as would be expected, but the emphasis is clearly to be an American beer bar first. 2011-03-22 21770\n",
      "45413 4.15 I looked up a number of places before visiting Amsterdam because I wanted to find the best beer bars in town. This place wasn't on any list and we literally passed it on our way to somewhere else and stopped in. It has an unbelievable beer list but it is primarily american beers. Although it was quite impressive I go to american craft beer bars all the time. I 2011-05-03 21770\n",
      "45412 4.29 A great find in Amsterdam! Centrally located in the city centre, just a few minutes walk from just about everything, this place not only has a terrific selection of craft American beers on tap and in bottles, it has a great staff!  2011-06-26 21770\n",
      "45411 4.0 the first american beer bar in europe,at least that is what they say in the bar. The ambiance is nice, bit 70s decoration. Not crowded when i was there (sunday afternoon). Service was very friendly, although had the feeling i knew more what he was selling than he did. List of beers is decent, as expected mainly american ipa and ris beers. Also small selection of european beers like mikkeller and brewdog. Bottle prices are too high (more than 20 euro for lost abbey angeld share 37cl), price of draft beers is ok, 4 euro for 25 cl. Nice place 2011-07-03 21770\n",
      "45410 3.74 Stopped by here while walking around and couldn't believe I was in an American beer bar in Amsterdam. 2011-10-20 21770\n",
      "45409 3.88 I visited this Amsterdam bar on Sunday night (October 23rd 2011) after having a meal in a nearby restaurant. It had been on my list of places to hit on my Amsterdam weekend and was dismayed to find out that it was closing at 10pm and we would only have time for one beer! So I went for a brew from Great Divide; the fantastic oak aged Imperial stout on tap.  2011-10-25 21770\n",
      "45408 4.24 My second stop in Amsterdam, a night after Arendsnest. This is a solid American bar with a good atmosphere, knowledgeable bartenders, and good (albeit expensive) selection. A little under 20 American brews on tap, mostly IPAs and stouts, along with a few European brews, including Mikkeller and Brew Dog.  2012-05-16 21770\n"
     ]
    }
   ],
   "source": [
    "# Inspect the last 10 reviews\n",
    "for review in session.query(Review).order_by(desc(Review.id)).limit(10):\n",
    "    print(review.id, review.rating, review.text, review.date, review.ba_brewery_id)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "# FOR ISSUES THAT NEED ROLLBACK\n",
    "# session.rollback()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Lessons Learned\n",
    "\n",
    "* A script would likely be more suitable than a notebook.\n",
    "* SQLite is useful for checking for existing records, but the same could have probably been done with Pandas and exporting to a CSV. "
   ]
  }
 ],
 "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.9.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}