1 - Reading Data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Reading Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To do any data science, the first thing you're going to need is data. Let's start to look at a few common ways of reading data in Python. Along the way, we'll take a look at some language constructs that we'll use throughout the day and really start to get familiar with coding in Python."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### File handling in Python"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, let's look at file objects in Python. We get a file handle by using the **open** function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0 - Introduction.ipynb\r\n",
      "1 - Reading Data.ipynb\r\n",
      "2 - Data Wrangling with Pandas.ipynb\r\n",
      "3 - Exploratory Analysis with Pandas.ipynb\r\n",
      "4 - Exploratory Plotting.ipynb\r\n",
      "5 - Modeling with scikit-learn.ipynb\r\n",
      "Mem_new_stuff.ipynb\r\n",
      "README.md\r\n",
      "\u001b[34mdata\u001b[m\u001b[m/\r\n",
      "environment.yaml\r\n",
      "\u001b[34mimg\u001b[m\u001b[m/\r\n",
      "imports-test.py\r\n",
      "introduction.pdf\r\n",
      "introduction.pptx\r\n",
      "load_data.py\r\n",
      "python-tutorial.ipynb\r\n",
      "\u001b[34msolutions\u001b[m\u001b[m/\r\n"
     ]
    }
   ],
   "source": [
    "ls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "csv_file = open(\"data/health_inspection_chi_sample.csv\") #opens file handle"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "File objects are lazy **iterators** (here, *stream objects*). Lazy means that they only do things, in this case read data, when you ask them to. You can call **next** on iterator objects to explicitly get the next item."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "address,aka_name,city,dba_name,facility_type,inspection_date,inspection_id,inspection_type,latitude,license_,location,longitude,results,risk,state,violations,zip\r\n"
     ]
    }
   ],
   "source": [
    "!head -1 data/health_inspection_chi_sample.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "line = next(csv_file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "address,aka_name,city,dba_name,facility_type,inspection_date,inspection_id,inspection_type,latitude,license_,location,longitude,results,risk,state,violations,zip\n",
      "\n"
     ]
    }
   ],
   "source": [
    "print(line)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here, the next item is a string which is the line in a file. In this case, it's the headers of the `csv` file.\n",
    "\n",
    "The nice thing about **iterators** is that you can iterate through them with a for loop."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "for line in csv_file:\n",
    "    pass # I don't want to do anything just keep going"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " | 41. PREMISES MAINTAINED FREE OF LITTER, UNNECESSARY ARTICLES, CLEANING  EQUIPMENT PROPERLY STORED - Comments: CORRECTED. | 41. PREMISES MAINTAINED FREE OF LITTER, UNNECESSARY ARTICLES, CLEANING  EQUIPMENT PROPERLY STORED - Comments: OBSERVED STANDING WASTE WATER INSIDE OF THE MOP BUCKET IN THE REAR PREP AREA AND A WET MOP ON THE FLOOR; INSTRUCTED TO DISPOSE OF STANDING WASTE WATER WHEN DONE USING AND TO HANG WET MOPS UPWARD TO DRY.\",60651.0\n",
      "\n"
     ]
    }
   ],
   "source": [
    "print(line) # this will be the last line of the file"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After consuming the file, we need to close the open file-handle."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "csv_file.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can avoid that by using what's called a **context manager** in Python. This is a really nice abstraction. You use context managers via the **with** statement."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open(\"data/health_inspection_chi_sample.csv\") as csv_file:\n",
    "    for line in csv_file:\n",
    "        pass"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By using the `open` function as a context manager, we get an automatic call to close the open file when we exit the context (determined by the indentation level). When working with files non-interactively, you'll almost always want to use open as a context manager."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise\n",
    "\n",
    "Write some code that iterates through the file `data/health_inspection_chi_sample.json` twice. Only call `open` once, however, then close the file. Can you find out, programatically, how many characters are in the file (hint: have a look at the `tell` method of the file object)?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Type your solution here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# %load solutions/read_json_twice.py\n",
    "json_file = open('data/health_inspection_chi_sample.json')\n",
    "\n",
    "for line in json_file:\n",
    "    pass\n",
    "\n",
    "print(json_file.tell())\n",
    "\n",
    "json_file.seek(0)\n",
    "\n",
    "for line in json_file:\n",
    "    pass\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reading Data: CSVs"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We peaked at this file above. We might already see some things we're going to have to clean up. Let's look at how we may do this. As always, Python has *batteries included*. The `csv` built-in module is great for basic data-munging needs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv\n",
    "from pprint import pprint"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's open a csv file and use csv's `reader` function to read in the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "csv_file = open(\"data/health_inspection_chi_sample.csv\")\n",
    "\n",
    "reader = csv.reader(csv_file)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`csv.reader` also returns a lazy iterator."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "headers = next(reader)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['address',\n",
      " 'aka_name',\n",
      " 'city',\n",
      " 'dba_name',\n",
      " 'facility_type',\n",
      " 'inspection_date',\n",
      " 'inspection_id',\n",
      " 'inspection_type',\n",
      " 'latitude',\n",
      " 'license_',\n",
      " 'location',\n",
      " 'longitude',\n",
      " 'results',\n",
      " 'risk',\n",
      " 'state',\n",
      " 'violations',\n",
      " 'zip']\n"
     ]
    }
   ],
   "source": [
    "pprint(headers)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "line = next(reader)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['5255 W MADISON ST ',\n",
      " 'RED SNAPPER FISH CHICKEN & PIZZA',\n",
      " 'CHICAGO',\n",
      " 'RED SNAPPER FISH CHICKEN & PIZZA',\n",
      " 'Restaurant',\n",
      " '2016-09-26T00:00:00.000',\n",
      " '1965287',\n",
      " 'Canvass',\n",
      " '41.880236543865834',\n",
      " '1991820.0',\n",
      " \"{'type': 'Point', 'coordinates': [-87.757220392117, 41.880236543866]}\",\n",
      " '-87.7572203921175',\n",
      " 'Pass w/ Conditions',\n",
      " 'Risk 1 (High)',\n",
      " 'IL',\n",
      " '35. WALLS, CEILINGS, ATTACHED EQUIPMENT CONSTRUCTED PER CODE: GOOD REPAIR, '\n",
      " 'SURFACES CLEAN AND DUST-LESS CLEANING METHODS - Comments: MUST CLEAN THE '\n",
      " 'WALLS AT WALL BASE NEAR THE MIXER IN REAR OF PREMISES AND THE PREP AREA OF '\n",
      " 'FOOD SPILLS AND CLEAN THE WALL VENT IN PREP AREA ,INSTRUCTED TO CLEAN AND '\n",
      " 'MAINTAIN AREA | 33. FOOD AND NON-FOOD CONTACT EQUIPMENT UTENSILS CLEAN, FREE '\n",
      " 'OF ABRASIVE DETERGENTS - Comments: MUST CLEAN THE INTERIOR PANEL OF THE ICE '\n",
      " 'MACHINE IN REAR OF PREMISES | 34. FLOORS: CONSTRUCTED PER CODE, CLEANED, '\n",
      " 'GOOD REPAIR, COVING INSTALLED, DUST-LESS CLEANING METHODS USED - Comments: '\n",
      " 'MUST CLEAN FLOORS THROUGHOUT PREMISES,ALONG WALL BASE AND CORNERS OF GREASE '\n",
      " 'AND DIRT BUILD-UP,INSTRUCTED TO CLEAN AND MAINTAIN AREA | 36. LIGHTING: '\n",
      " 'REQUIRED MINIMUM FOOT-CANDLES OF LIGHT PROVIDED, FIXTURES SHIELDED - '\n",
      " 'Comments: MUST PROVIDE A LIGHT SHIELD COVER FOR THE WALK-IN FREEZER IN REAR '\n",
      " 'OF PREMISES | 22. DISH MACHINES: PROVIDED WITH ACCURATE THERMOMETERS, '\n",
      " 'CHEMICAL TEST KITS AND SUITABLE GAUGE COCK - Comments: OBSERVED NO CHEMICAL '\n",
      " 'TEST STRIPS TO TEST THE SANITIZER AT 3 COMPARTMENT SINK,WHEN SINK IS SET UP '\n",
      " 'FOR WASH,RINSE AND SANITIZE,INSTRUCTED TO PROVIDE SERIOUS VIOLATION 7-38-030 '\n",
      " '| 40. REFRIGERATION AND METAL STEM THERMOMETERS PROVIDED AND CONSPICUOUS - '\n",
      " 'Comments: MUST PROVIDE A THERMOMETER INSIDE THE PREP COOLER IN PREP AREA',\n",
      " '60644.0']\n"
     ]
    }
   ],
   "source": [
    "pprint(line)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The biggest difference in using `csv.reader` vs. iterating through the file is that it automatically splits the csv on commas and returns the line of the file split into a list.\n",
    "\n",
    "You can control this behavior through a `Dialect` object. By default, `csv.reader` uses a Dialect object called \"excel.\" Here let's look at the attributes of the excel dialect. Don't worry too much about the code used to achieve this. We'll look more at this later."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'delimiter': ',',\n",
      " 'doublequote': True,\n",
      " 'lineterminator': '\\r\\n',\n",
      " 'quotechar': '\"',\n",
      " 'quoting': 0,\n",
      " 'skipinitialspace': False}\n"
     ]
    }
   ],
   "source": [
    "items = csv.excel.__dict__.items()\n",
    "\n",
    "pprint({key: value for key, value in items if not key.startswith(\"_\")})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You might also find `DictReader` to be useful. \n",
    "\n",
    "First, let's back up in the file to the beginning."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "csv_file.seek(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "reader = csv.DictReader(csv_file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "OrderedDict([('address', '5255 W MADISON ST '),\n",
      "             ('aka_name', 'RED SNAPPER FISH CHICKEN & PIZZA'),\n",
      "             ('city', 'CHICAGO'),\n",
      "             ('dba_name', 'RED SNAPPER FISH CHICKEN & PIZZA'),\n",
      "             ('facility_type', 'Restaurant'),\n",
      "             ('inspection_date', '2016-09-26T00:00:00.000'),\n",
      "             ('inspection_id', '1965287'),\n",
      "             ('inspection_type', 'Canvass'),\n",
      "             ('latitude', '41.880236543865834'),\n",
      "             ('license_', '1991820.0'),\n",
      "             ('location',\n",
      "              \"{'type': 'Point', 'coordinates': [-87.757220392117, \"\n",
      "              '41.880236543866]}'),\n",
      "             ('longitude', '-87.7572203921175'),\n",
      "             ('results', 'Pass w/ Conditions'),\n",
      "             ('risk', 'Risk 1 (High)'),\n",
      "             ('state', 'IL'),\n",
      "             ('violations',\n",
      "              '35. WALLS, CEILINGS, ATTACHED EQUIPMENT CONSTRUCTED PER CODE: '\n",
      "              'GOOD REPAIR, SURFACES CLEAN AND DUST-LESS CLEANING METHODS - '\n",
      "              'Comments: MUST CLEAN THE WALLS AT WALL BASE NEAR THE MIXER IN '\n",
      "              'REAR OF PREMISES AND THE PREP AREA OF FOOD SPILLS AND CLEAN THE '\n",
      "              'WALL VENT IN PREP AREA ,INSTRUCTED TO CLEAN AND MAINTAIN AREA | '\n",
      "              '33. FOOD AND NON-FOOD CONTACT EQUIPMENT UTENSILS CLEAN, FREE OF '\n",
      "              'ABRASIVE DETERGENTS - Comments: MUST CLEAN THE INTERIOR PANEL '\n",
      "              'OF THE ICE MACHINE IN REAR OF PREMISES | 34. FLOORS: '\n",
      "              'CONSTRUCTED PER CODE, CLEANED, GOOD REPAIR, COVING INSTALLED, '\n",
      "              'DUST-LESS CLEANING METHODS USED - Comments: MUST CLEAN FLOORS '\n",
      "              'THROUGHOUT PREMISES,ALONG WALL BASE AND CORNERS OF GREASE AND '\n",
      "              'DIRT BUILD-UP,INSTRUCTED TO CLEAN AND MAINTAIN AREA | 36. '\n",
      "              'LIGHTING: REQUIRED MINIMUM FOOT-CANDLES OF LIGHT PROVIDED, '\n",
      "              'FIXTURES SHIELDED - Comments: MUST PROVIDE A LIGHT SHIELD COVER '\n",
      "              'FOR THE WALK-IN FREEZER IN REAR OF PREMISES | 22. DISH '\n",
      "              'MACHINES: PROVIDED WITH ACCURATE THERMOMETERS, CHEMICAL TEST '\n",
      "              'KITS AND SUITABLE GAUGE COCK - Comments: OBSERVED NO CHEMICAL '\n",
      "              'TEST STRIPS TO TEST THE SANITIZER AT 3 COMPARTMENT SINK,WHEN '\n",
      "              'SINK IS SET UP FOR WASH,RINSE AND SANITIZE,INSTRUCTED TO '\n",
      "              'PROVIDE SERIOUS VIOLATION 7-38-030 | 40. REFRIGERATION AND '\n",
      "              'METAL STEM THERMOMETERS PROVIDED AND CONSPICUOUS - Comments: '\n",
      "              'MUST PROVIDE A THERMOMETER INSIDE THE PREP COOLER IN PREP AREA'),\n",
      "             ('zip', '60644.0')])\n"
     ]
    }
   ],
   "source": [
    "pprint(next(reader))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "csv_file.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "One last handy trick with the `csv` module is to use `Sniffer` to determine the csv dialect for you."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "file_name = \"data/health_inspection_chi_sample.csv\"\n",
    "\n",
    "with open(file_name) as csv_file:\n",
    "    \n",
    "    try:\n",
    "        dialect = csv.Sniffer().sniff(csv_file.read(1024))\n",
    "    except csv.Error as err:\n",
    "        # log that this file format couldn't be deduced\n",
    "        print(f\"The format of {file_name} could not be detected.\")\n",
    "    else:\n",
    "        csv_file.seek(0)\n",
    "    \n",
    "        dta = csv.reader(csv_file, dialect=dialect)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A big part of increased productivity is saving yourself some work later. The first rule of dealing with data is probably something like \"The data is against you. Act accordingly.\"\n",
    "\n",
    "We set this block of code up to be pretty paranoid. Software engineers call this **defensive programming**. It's a good habit to get into when you're doing any data science work in Python. \n",
    "\n",
    "There are three things to note here. First, is the use of `Sniffer` at all. If `file_name` is a standard csv format, we'll be able to read it.\n",
    "\n",
    "The second is using a `try/except/else` block. In a `try/except` block, any exception that is raised will trigger the code in the except block. If no exception is raised, the *optional* else block will run.\n",
    "\n",
    "Let's take a look at a toy example to fix ideas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    1/0\n",
    "except:\n",
    "    print(\"Something went wrong.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    1/0\n",
    "except ZeroDivisionError as err:\n",
    "    print(\"Something went wrong.\")\n",
    "    raise(err)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    1/0\n",
    "except FileNotFoundError:\n",
    "    print(\"This error isn't raised, so we're not here.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The final thing to note in the block above is the use of `print` to provide some information about what went wrong. Logging is another really good habit to get into, and print statements are the dead simplest way to log the behavior of your code.\n",
    "\n",
    "In practice, you probably don't want to use `print`. You want to use the [logging](https://docs.python.org/3/library/logging.html) module, but we're not going to talk about best practices in logging anymore today."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reading Data: json"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Perhaps the second most common file format after CSVs is the `JSON` format. JSON stands for JavaScript Object Notation. When reading data from an API, for example, you will often encounter json files."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Each line in the file `data/health_inspection_chi_sample.json` is a single json object that represents the same data above. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{\"address\":\"5255 W MADISON ST \",\"aka_name\":\"RED SNAPPER FISH CHICKEN & PIZZA\",\"city\":\"CHICAGO\",\"dba_name\":\"RED SNAPPER FISH CHICKEN & PIZZA\",\"facility_type\":\"Restaurant\",\"inspection_date\":\"2016-09-26T00:00:00.000\",\"inspection_id\":1965287,\"inspection_type\":\"Canvass\",\"latitude\":41.8802365439,\"license_\":1991820.0,\"location\":{\"type\":\"Point\",\"coordinates\":[-87.7572203921,41.8802365439]},\"longitude\":-87.7572203921,\"results\":\"Pass w\\/ Conditions\",\"risk\":\"Risk 1 (High)\",\"state\":\"IL\",\"violations\":\"35. WALLS, CEILINGS, ATTACHED EQUIPMENT CONSTRUCTED PER CODE: GOOD REPAIR, SURFACES CLEAN AND DUST-LESS CLEANING METHODS - Comments: MUST CLEAN THE WALLS AT WALL BASE NEAR THE MIXER IN REAR OF PREMISES AND THE PREP AREA OF FOOD SPILLS AND CLEAN THE WALL VENT IN PREP AREA ,INSTRUCTED TO CLEAN AND MAINTAIN AREA | 33. FOOD AND NON-FOOD CONTACT EQUIPMENT UTENSILS CLEAN, FREE OF ABRASIVE DETERGENTS - Comments: MUST CLEAN THE INTERIOR PANEL OF THE ICE MACHINE IN REAR OF PREMISES | 34. FLOORS: CONSTRUCTED PER CODE, CLEANED, GOOD REPAIR, COVING INSTALLED, DUST-LESS CLEANING METHODS USED - Comments: MUST CLEAN FLOORS THROUGHOUT PREMISES,ALONG WALL BASE AND CORNERS OF GREASE AND DIRT BUILD-UP,INSTRUCTED TO CLEAN AND MAINTAIN AREA | 36. LIGHTING: REQUIRED MINIMUM FOOT-CANDLES OF LIGHT PROVIDED, FIXTURES SHIELDED - Comments: MUST PROVIDE A LIGHT SHIELD COVER FOR THE WALK-IN FREEZER IN REAR OF PREMISES | 22. DISH MACHINES: PROVIDED WITH ACCURATE THERMOMETERS, CHEMICAL TEST KITS AND SUITABLE GAUGE COCK - Comments: OBSERVED NO CHEMICAL TEST STRIPS TO TEST THE SANITIZER AT 3 COMPARTMENT SINK,WHEN SINK IS SET UP FOR WASH,RINSE AND SANITIZE,INSTRUCTED TO PROVIDE SERIOUS VIOLATION 7-38-030 | 40. REFRIGERATION AND METAL STEM THERMOMETERS PROVIDED AND CONSPICUOUS - Comments: MUST PROVIDE A THERMOMETER INSIDE THE PREP COOLER IN PREP AREA\",\"zip\":60644.0}\r\n"
     ]
    }
   ],
   "source": [
    "!head -n 1 data/health_inspection_chi_sample.json"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can use the `json` module to read and manipulate json data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since each line is a json object here, we need to iterate over the file and parse each line. We use the `json.loads` function here for \"load string.\" The similar function `json.load` takes a file-like object."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'address': '5255 W MADISON ST ',\n",
      " 'aka_name': 'RED SNAPPER FISH CHICKEN & PIZZA',\n",
      " 'city': 'CHICAGO',\n",
      " 'dba_name': 'RED SNAPPER FISH CHICKEN & PIZZA',\n",
      " 'facility_type': 'Restaurant',\n",
      " 'inspection_date': '2016-09-26T00:00:00.000',\n",
      " 'inspection_id': 1965287,\n",
      " 'inspection_type': 'Canvass',\n",
      " 'latitude': 41.8802365439,\n",
      " 'license_': 1991820.0,\n",
      " 'location': {'coordinates': [-87.7572203921, 41.8802365439], 'type': 'Point'},\n",
      " 'longitude': -87.7572203921,\n",
      " 'results': 'Pass w/ Conditions',\n",
      " 'risk': 'Risk 1 (High)',\n",
      " 'state': 'IL',\n",
      " 'violations': '35. WALLS, CEILINGS, ATTACHED EQUIPMENT CONSTRUCTED PER CODE: '\n",
      "               'GOOD REPAIR, SURFACES CLEAN AND DUST-LESS CLEANING METHODS - '\n",
      "               'Comments: MUST CLEAN THE WALLS AT WALL BASE NEAR THE MIXER IN '\n",
      "               'REAR OF PREMISES AND THE PREP AREA OF FOOD SPILLS AND CLEAN '\n",
      "               'THE WALL VENT IN PREP AREA ,INSTRUCTED TO CLEAN AND MAINTAIN '\n",
      "               'AREA | 33. FOOD AND NON-FOOD CONTACT EQUIPMENT UTENSILS CLEAN, '\n",
      "               'FREE OF ABRASIVE DETERGENTS - Comments: MUST CLEAN THE '\n",
      "               'INTERIOR PANEL OF THE ICE MACHINE IN REAR OF PREMISES | 34. '\n",
      "               'FLOORS: CONSTRUCTED PER CODE, CLEANED, GOOD REPAIR, COVING '\n",
      "               'INSTALLED, DUST-LESS CLEANING METHODS USED - Comments: MUST '\n",
      "               'CLEAN FLOORS THROUGHOUT PREMISES,ALONG WALL BASE AND CORNERS '\n",
      "               'OF GREASE AND DIRT BUILD-UP,INSTRUCTED TO CLEAN AND MAINTAIN '\n",
      "               'AREA | 36. LIGHTING: REQUIRED MINIMUM FOOT-CANDLES OF LIGHT '\n",
      "               'PROVIDED, FIXTURES SHIELDED - Comments: MUST PROVIDE A LIGHT '\n",
      "               'SHIELD COVER FOR THE WALK-IN FREEZER IN REAR OF PREMISES | 22. '\n",
      "               'DISH MACHINES: PROVIDED WITH ACCURATE THERMOMETERS, CHEMICAL '\n",
      "               'TEST KITS AND SUITABLE GAUGE COCK - Comments: OBSERVED NO '\n",
      "               'CHEMICAL TEST STRIPS TO TEST THE SANITIZER AT 3 COMPARTMENT '\n",
      "               'SINK,WHEN SINK IS SET UP FOR WASH,RINSE AND '\n",
      "               'SANITIZE,INSTRUCTED TO PROVIDE SERIOUS VIOLATION 7-38-030 | '\n",
      "               '40. REFRIGERATION AND METAL STEM THERMOMETERS PROVIDED AND '\n",
      "               'CONSPICUOUS - Comments: MUST PROVIDE A THERMOMETER INSIDE THE '\n",
      "               'PREP COOLER IN PREP AREA',\n",
      " 'zip': 60644.0}\n"
     ]
    }
   ],
   "source": [
    "dta = []\n",
    "\n",
    "with open(\"data/health_inspection_chi_sample.json\") as json_file:\n",
    "    for line in json_file:\n",
    "        line = json.loads(line)\n",
    "        dta.append(line)\n",
    "\n",
    "pprint(dta[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`json.loads` places each json object into a Python dictionary, helpfully filling in `None` for `null` for missing values and otherwise preserving types. It also, works recursively as we see in the `location` field."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Aside: List Comprehensions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's take a look at another Pythonic concept, introduced a bit above, called a **list comprehension**. This is what's called *syntactic sugar*. It's a concise way to create a list."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[1, 2, 3, 4, 5]"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list1 = [i for i in range(1, 6)]\n",
    "list1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Alternatively, we could have made this list by writing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[1, 2, 3, 4, 5]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result_list = []\n",
    "\n",
    "for i in range(1, 6):\n",
    "    result_list.append(i)\n",
    "\n",
    "result_list"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "List comprehensions can contain logic."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "x = ['a', 'b', 'c', 'd', '_e', '_f']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['a', 'b', 'c', 'd']"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "[i for i in x if not i.startswith('_')]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also use a an else clause. Notice the slightly different syntax."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['a', 'b', 'c', 'd', 'skipped', 'skipped']"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "[i if not i.startswith('_') else 'skipped' for i in x]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "List comprehensions can be nested, though it's usually best practices not to go overboard. They can quickly become difficult to read."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "matrix = [\n",
    "    [1, 2, 3, 4],\n",
    "    [5, 6, 7, 8],\n",
    "    [9, 10, 11, 12],\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that we write this from outside to in."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "[element for row in matrix for element in row]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also create **dictionary comprehensions**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pairs = [\n",
    "    (\"first_initial\", \"J\"), \n",
    "    (\"last_name\", \"Doe\"), \n",
    "    (\"address\", \"1234 Main Street\")\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "{key: value for key, value in pairs}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise\n",
    "\n",
    "Returning to the code that we introduced above, we can take further control over how a file with json objects is read in by using the `object_hook` argument. Say we wanted to remove the `location` field above. We don't need the `geoJSON` formatted information. We could do so with the `object_hook`. Write a function called `remove_entry` that removes the `'location'` field from each record in the `'data/health_inspection_chi_sample.json'` file.\n",
    "\n",
    "Pass this function to the `object_hook` argument of `json.loads`. Be careful, `object_hook` will get called recursively on nested json objects."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Type your solution here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load solutions/object_hook_json.py"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reading Data: pandas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's take a look at using [pandas]() to read data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Introducing Pandas\n",
    "\n",
    "First, a few words of introduction for **pandas**. Pandas is a Python package providing fast, flexible, and expressive data structures designed to work with relational or labeled data. It is a high-level tool for doing practical, real world data analysis in Python.\n",
    "\n",
    "You reach for pandas when you have:\n",
    "\n",
    "* Tabular data with heterogeneously-typed columns\n",
    "* Ordered and unordered (not necessarily fixed-frequency) time series data.\n",
    "* Arbitrary matrix data with row and column labels\n",
    "\n",
    "Almost any dataset can be converted to a pandas data structure for cleaning, transformation, and analysis.\n",
    "\n",
    "Pandas is built on top of **numpy**, which we'll look a bit more at later."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, let's import the pandas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "pd.set_option(\"max.rows\", 6)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pandas has facilities for reading csv files and files containing JSON records (and other formats). We can use `read_csv` for csv files."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "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>address</th>\n",
       "      <th>aka_name</th>\n",
       "      <th>city</th>\n",
       "      <th>dba_name</th>\n",
       "      <th>facility_type</th>\n",
       "      <th>inspection_date</th>\n",
       "      <th>inspection_id</th>\n",
       "      <th>inspection_type</th>\n",
       "      <th>latitude</th>\n",
       "      <th>license_</th>\n",
       "      <th>location</th>\n",
       "      <th>longitude</th>\n",
       "      <th>results</th>\n",
       "      <th>risk</th>\n",
       "      <th>state</th>\n",
       "      <th>violations</th>\n",
       "      <th>zip</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5255 W MADISON ST</td>\n",
       "      <td>RED SNAPPER FISH CHICKEN &amp; PIZZA</td>\n",
       "      <td>CHICAGO</td>\n",
       "      <td>RED SNAPPER FISH CHICKEN &amp; PIZZA</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>2016-09-26T00:00:00.000</td>\n",
       "      <td>1965287</td>\n",
       "      <td>Canvass</td>\n",
       "      <td>41.880237</td>\n",
       "      <td>1991820.0</td>\n",
       "      <td>{'type': 'Point', 'coordinates': [-87.75722039...</td>\n",
       "      <td>-87.757220</td>\n",
       "      <td>Pass w/ Conditions</td>\n",
       "      <td>Risk 1 (High)</td>\n",
       "      <td>IL</td>\n",
       "      <td>35. WALLS, CEILINGS, ATTACHED EQUIPMENT CONSTR...</td>\n",
       "      <td>60644.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5958 W DIVERSEY AVE</td>\n",
       "      <td>TAQUERIA MORELOS</td>\n",
       "      <td>CHICAGO</td>\n",
       "      <td>TAQUERIA MORELOS</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>2014-02-06T00:00:00.000</td>\n",
       "      <td>1329698</td>\n",
       "      <td>Canvass</td>\n",
       "      <td>41.931250</td>\n",
       "      <td>2099479.0</td>\n",
       "      <td>{'type': 'Point', 'coordinates': [-87.77590699...</td>\n",
       "      <td>-87.775907</td>\n",
       "      <td>Pass</td>\n",
       "      <td>Risk 1 (High)</td>\n",
       "      <td>IL</td>\n",
       "      <td>33. FOOD AND NON-FOOD CONTACT EQUIPMENT UTENSI...</td>\n",
       "      <td>60639.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5400-5402 N CLARK ST</td>\n",
       "      <td>HAMBURGER MARY'S/MARY'S REC ROOM</td>\n",
       "      <td>CHICAGO</td>\n",
       "      <td>HAMBURGER MARY'S CHICAGO/MARY'S REC ROOM</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>2010-12-03T00:00:00.000</td>\n",
       "      <td>470787</td>\n",
       "      <td>SFP</td>\n",
       "      <td>41.979884</td>\n",
       "      <td>1933748.0</td>\n",
       "      <td>{'type': 'Point', 'coordinates': [-87.66842948...</td>\n",
       "      <td>-87.668429</td>\n",
       "      <td>Fail</td>\n",
       "      <td>Risk 1 (High)</td>\n",
       "      <td>IL</td>\n",
       "      <td>6. HANDS WASHED AND CLEANED, GOOD HYGIENIC PRA...</td>\n",
       "      <td>60640.0</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>997</th>\n",
       "      <td>3251 E 92ND ST</td>\n",
       "      <td>VICTORY CENTRE OF SOUTH CHICAGO SLF</td>\n",
       "      <td>CHICAGO</td>\n",
       "      <td>VICTORY CENTRE OF SOUTH CHICAGO SLF</td>\n",
       "      <td>ASSISTED LIVING</td>\n",
       "      <td>2012-10-12T00:00:00.000</td>\n",
       "      <td>1296216</td>\n",
       "      <td>Canvass</td>\n",
       "      <td>41.728214</td>\n",
       "      <td>1968546.0</td>\n",
       "      <td>{'type': 'Point', 'coordinates': [-87.54469279...</td>\n",
       "      <td>-87.544693</td>\n",
       "      <td>Pass</td>\n",
       "      <td>Risk 1 (High)</td>\n",
       "      <td>IL</td>\n",
       "      <td>34. FLOORS: CONSTRUCTED PER CODE, CLEANED, GOO...</td>\n",
       "      <td>60617.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>998</th>\n",
       "      <td>4300 S UNION AVE</td>\n",
       "      <td>TUXEDO JUNCTION #2</td>\n",
       "      <td>CHICAGO</td>\n",
       "      <td>TUXEDO JUNCTION #2</td>\n",
       "      <td>Grocery Store</td>\n",
       "      <td>2011-07-14T00:00:00.000</td>\n",
       "      <td>585793</td>\n",
       "      <td>Canvass</td>\n",
       "      <td>41.816186</td>\n",
       "      <td>8974.0</td>\n",
       "      <td>{'type': 'Point', 'coordinates': [-87.64345182...</td>\n",
       "      <td>-87.643452</td>\n",
       "      <td>Fail</td>\n",
       "      <td>Risk 2 (Medium)</td>\n",
       "      <td>IL</td>\n",
       "      <td>16. FOOD PROTECTED DURING STORAGE, PREPARATION...</td>\n",
       "      <td>60609.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>999</th>\n",
       "      <td>3820 W CHICAGO AVE</td>\n",
       "      <td>Bro-N-Laws</td>\n",
       "      <td>CHICAGO</td>\n",
       "      <td>Bro-N-Laws</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>2015-08-19T00:00:00.000</td>\n",
       "      <td>1566470</td>\n",
       "      <td>Complaint Re-Inspection</td>\n",
       "      <td>41.895513</td>\n",
       "      <td>2288968.0</td>\n",
       "      <td>{'type': 'Point', 'coordinates': [-87.72216981...</td>\n",
       "      <td>-87.722170</td>\n",
       "      <td>Pass</td>\n",
       "      <td>Risk 1 (High)</td>\n",
       "      <td>IL</td>\n",
       "      <td>45. FOOD HANDLER REQUIREMENTS MET - Comments: ...</td>\n",
       "      <td>60651.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1000 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                   address                             aka_name     city  \\\n",
       "0       5255 W MADISON ST      RED SNAPPER FISH CHICKEN & PIZZA  CHICAGO   \n",
       "1     5958 W DIVERSEY AVE                      TAQUERIA MORELOS  CHICAGO   \n",
       "2    5400-5402 N CLARK ST      HAMBURGER MARY'S/MARY'S REC ROOM  CHICAGO   \n",
       "..                     ...                                  ...      ...   \n",
       "997        3251 E 92ND ST   VICTORY CENTRE OF SOUTH CHICAGO SLF  CHICAGO   \n",
       "998      4300 S UNION AVE                    TUXEDO JUNCTION #2  CHICAGO   \n",
       "999    3820 W CHICAGO AVE                            Bro-N-Laws  CHICAGO   \n",
       "\n",
       "                                     dba_name    facility_type  \\\n",
       "0            RED SNAPPER FISH CHICKEN & PIZZA       Restaurant   \n",
       "1                            TAQUERIA MORELOS       Restaurant   \n",
       "2    HAMBURGER MARY'S CHICAGO/MARY'S REC ROOM       Restaurant   \n",
       "..                                        ...              ...   \n",
       "997       VICTORY CENTRE OF SOUTH CHICAGO SLF  ASSISTED LIVING   \n",
       "998                        TUXEDO JUNCTION #2    Grocery Store   \n",
       "999                                Bro-N-Laws       Restaurant   \n",
       "\n",
       "             inspection_date  inspection_id          inspection_type  \\\n",
       "0    2016-09-26T00:00:00.000        1965287                  Canvass   \n",
       "1    2014-02-06T00:00:00.000        1329698                  Canvass   \n",
       "2    2010-12-03T00:00:00.000         470787                      SFP   \n",
       "..                       ...            ...                      ...   \n",
       "997  2012-10-12T00:00:00.000        1296216                  Canvass   \n",
       "998  2011-07-14T00:00:00.000         585793                  Canvass   \n",
       "999  2015-08-19T00:00:00.000        1566470  Complaint Re-Inspection   \n",
       "\n",
       "      latitude   license_                                           location  \\\n",
       "0    41.880237  1991820.0  {'type': 'Point', 'coordinates': [-87.75722039...   \n",
       "1    41.931250  2099479.0  {'type': 'Point', 'coordinates': [-87.77590699...   \n",
       "2    41.979884  1933748.0  {'type': 'Point', 'coordinates': [-87.66842948...   \n",
       "..         ...        ...                                                ...   \n",
       "997  41.728214  1968546.0  {'type': 'Point', 'coordinates': [-87.54469279...   \n",
       "998  41.816186     8974.0  {'type': 'Point', 'coordinates': [-87.64345182...   \n",
       "999  41.895513  2288968.0  {'type': 'Point', 'coordinates': [-87.72216981...   \n",
       "\n",
       "     longitude             results             risk state  \\\n",
       "0   -87.757220  Pass w/ Conditions    Risk 1 (High)    IL   \n",
       "1   -87.775907                Pass    Risk 1 (High)    IL   \n",
       "2   -87.668429                Fail    Risk 1 (High)    IL   \n",
       "..         ...                 ...              ...   ...   \n",
       "997 -87.544693                Pass    Risk 1 (High)    IL   \n",
       "998 -87.643452                Fail  Risk 2 (Medium)    IL   \n",
       "999 -87.722170                Pass    Risk 1 (High)    IL   \n",
       "\n",
       "                                            violations      zip  \n",
       "0    35. WALLS, CEILINGS, ATTACHED EQUIPMENT CONSTR...  60644.0  \n",
       "1    33. FOOD AND NON-FOOD CONTACT EQUIPMENT UTENSI...  60639.0  \n",
       "2    6. HANDS WASHED AND CLEANED, GOOD HYGIENIC PRA...  60640.0  \n",
       "..                                                 ...      ...  \n",
       "997  34. FLOORS: CONSTRUCTED PER CODE, CLEANED, GOO...  60617.0  \n",
       "998  16. FOOD PROTECTED DURING STORAGE, PREPARATION...  60609.0  \n",
       "999  45. FOOD HANDLER REQUIREMENTS MET - Comments: ...  60651.0  \n",
       "\n",
       "[1000 rows x 17 columns]"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(\"data/health_inspection_chi_sample.csv\")\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1000 entries, 0 to 999\n",
      "Data columns (total 17 columns):\n",
      "address            1000 non-null object\n",
      "aka_name           991 non-null object\n",
      "city               1000 non-null object\n",
      "dba_name           1000 non-null object\n",
      "facility_type      993 non-null object\n",
      "inspection_date    1000 non-null object\n",
      "inspection_id      1000 non-null int64\n",
      "inspection_type    1000 non-null object\n",
      "latitude           991 non-null float64\n",
      "license_           998 non-null float64\n",
      "location           991 non-null object\n",
      "longitude          991 non-null float64\n",
      "results            1000 non-null object\n",
      "risk               1000 non-null object\n",
      "state              1000 non-null object\n",
      "violations         963 non-null object\n",
      "zip                1000 non-null float64\n",
      "dtypes: float64(4), int64(1), object(12)\n",
      "memory usage: 132.9+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`read_csv` is one of the best/worst functions in all of Python. It's great because it does just about everything. It's terrible, because it does just about everything. Chances are if you have a special case that pandas `read_csv` will accomodate your needs. Go ahead and have a look at the `docstring`.\n",
    "\n",
    "\n",
    "```python\n",
    "pd.read_csv?\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.read_table?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`read_csv` returns a pandas DataFrame. We'll take a deeper dive into DataFrames next when we start to clean this data set."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The JSON counterpart to `read_csv` is `read_json`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise\n",
    "\n",
    "Use `pd.read_json` to read in the Chicago health inspections json sample in the `data` folder."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[34mdata\u001b[m\u001b[m/                              health_inspection_chi_sample.csv\r\n",
      "data.zip                           health_inspection_chi_sample.json\r\n",
      "get_data.py                        titanic.csv\r\n",
      "health_inspection_chi.csv          \u001b[31mzip_data.sh\u001b[m\u001b[m*\r\n"
     ]
    }
   ],
   "source": [
    "ls data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "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>address</th>\n",
       "      <th>aka_name</th>\n",
       "      <th>city</th>\n",
       "      <th>dba_name</th>\n",
       "      <th>facility_type</th>\n",
       "      <th>inspection_date</th>\n",
       "      <th>inspection_id</th>\n",
       "      <th>inspection_type</th>\n",
       "      <th>latitude</th>\n",
       "      <th>license_</th>\n",
       "      <th>location</th>\n",
       "      <th>longitude</th>\n",
       "      <th>results</th>\n",
       "      <th>risk</th>\n",
       "      <th>state</th>\n",
       "      <th>violations</th>\n",
       "      <th>zip</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5255 W MADISON ST</td>\n",
       "      <td>RED SNAPPER FISH CHICKEN &amp; PIZZA</td>\n",
       "      <td>CHICAGO</td>\n",
       "      <td>RED SNAPPER FISH CHICKEN &amp; PIZZA</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>2016-09-26T00:00:00.000</td>\n",
       "      <td>1965287</td>\n",
       "      <td>Canvass</td>\n",
       "      <td>41.880237</td>\n",
       "      <td>1991820.0</td>\n",
       "      <td>{'type': 'Point', 'coordinates': [-87.75722039...</td>\n",
       "      <td>-87.757220</td>\n",
       "      <td>Pass w/ Conditions</td>\n",
       "      <td>Risk 1 (High)</td>\n",
       "      <td>IL</td>\n",
       "      <td>35. WALLS, CEILINGS, ATTACHED EQUIPMENT CONSTR...</td>\n",
       "      <td>60644</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5958 W DIVERSEY AVE</td>\n",
       "      <td>TAQUERIA MORELOS</td>\n",
       "      <td>CHICAGO</td>\n",
       "      <td>TAQUERIA MORELOS</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>2014-02-06T00:00:00.000</td>\n",
       "      <td>1329698</td>\n",
       "      <td>Canvass</td>\n",
       "      <td>41.931250</td>\n",
       "      <td>2099479.0</td>\n",
       "      <td>{'type': 'Point', 'coordinates': [-87.77590699...</td>\n",
       "      <td>-87.775907</td>\n",
       "      <td>Pass</td>\n",
       "      <td>Risk 1 (High)</td>\n",
       "      <td>IL</td>\n",
       "      <td>33. FOOD AND NON-FOOD CONTACT EQUIPMENT UTENSI...</td>\n",
       "      <td>60639</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5400-5402 N CLARK ST</td>\n",
       "      <td>HAMBURGER MARY'S/MARY'S REC ROOM</td>\n",
       "      <td>CHICAGO</td>\n",
       "      <td>HAMBURGER MARY'S CHICAGO/MARY'S REC ROOM</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>2010-12-03T00:00:00.000</td>\n",
       "      <td>470787</td>\n",
       "      <td>SFP</td>\n",
       "      <td>41.979884</td>\n",
       "      <td>1933748.0</td>\n",
       "      <td>{'type': 'Point', 'coordinates': [-87.66842948...</td>\n",
       "      <td>-87.668429</td>\n",
       "      <td>Fail</td>\n",
       "      <td>Risk 1 (High)</td>\n",
       "      <td>IL</td>\n",
       "      <td>6. HANDS WASHED AND CLEANED, GOOD HYGIENIC PRA...</td>\n",
       "      <td>60640</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>997</th>\n",
       "      <td>3251 E 92ND ST</td>\n",
       "      <td>VICTORY CENTRE OF SOUTH CHICAGO SLF</td>\n",
       "      <td>CHICAGO</td>\n",
       "      <td>VICTORY CENTRE OF SOUTH CHICAGO SLF</td>\n",
       "      <td>ASSISTED LIVING</td>\n",
       "      <td>2012-10-12T00:00:00.000</td>\n",
       "      <td>1296216</td>\n",
       "      <td>Canvass</td>\n",
       "      <td>41.728214</td>\n",
       "      <td>1968546.0</td>\n",
       "      <td>{'type': 'Point', 'coordinates': [-87.54469279...</td>\n",
       "      <td>-87.544693</td>\n",
       "      <td>Pass</td>\n",
       "      <td>Risk 1 (High)</td>\n",
       "      <td>IL</td>\n",
       "      <td>34. FLOORS: CONSTRUCTED PER CODE, CLEANED, GOO...</td>\n",
       "      <td>60617</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>998</th>\n",
       "      <td>4300 S UNION AVE</td>\n",
       "      <td>TUXEDO JUNCTION #2</td>\n",
       "      <td>CHICAGO</td>\n",
       "      <td>TUXEDO JUNCTION #2</td>\n",
       "      <td>Grocery Store</td>\n",
       "      <td>2011-07-14T00:00:00.000</td>\n",
       "      <td>585793</td>\n",
       "      <td>Canvass</td>\n",
       "      <td>41.816186</td>\n",
       "      <td>8974.0</td>\n",
       "      <td>{'type': 'Point', 'coordinates': [-87.64345182...</td>\n",
       "      <td>-87.643452</td>\n",
       "      <td>Fail</td>\n",
       "      <td>Risk 2 (Medium)</td>\n",
       "      <td>IL</td>\n",
       "      <td>16. FOOD PROTECTED DURING STORAGE, PREPARATION...</td>\n",
       "      <td>60609</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>999</th>\n",
       "      <td>3820 W CHICAGO AVE</td>\n",
       "      <td>Bro-N-Laws</td>\n",
       "      <td>CHICAGO</td>\n",
       "      <td>Bro-N-Laws</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>2015-08-19T00:00:00.000</td>\n",
       "      <td>1566470</td>\n",
       "      <td>Complaint Re-Inspection</td>\n",
       "      <td>41.895513</td>\n",
       "      <td>2288968.0</td>\n",
       "      <td>{'type': 'Point', 'coordinates': [-87.72216981...</td>\n",
       "      <td>-87.722170</td>\n",
       "      <td>Pass</td>\n",
       "      <td>Risk 1 (High)</td>\n",
       "      <td>IL</td>\n",
       "      <td>45. FOOD HANDLER REQUIREMENTS MET - Comments: ...</td>\n",
       "      <td>60651</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1000 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                   address                             aka_name     city  \\\n",
       "0       5255 W MADISON ST      RED SNAPPER FISH CHICKEN & PIZZA  CHICAGO   \n",
       "1     5958 W DIVERSEY AVE                      TAQUERIA MORELOS  CHICAGO   \n",
       "2    5400-5402 N CLARK ST      HAMBURGER MARY'S/MARY'S REC ROOM  CHICAGO   \n",
       "..                     ...                                  ...      ...   \n",
       "997        3251 E 92ND ST   VICTORY CENTRE OF SOUTH CHICAGO SLF  CHICAGO   \n",
       "998      4300 S UNION AVE                    TUXEDO JUNCTION #2  CHICAGO   \n",
       "999    3820 W CHICAGO AVE                            Bro-N-Laws  CHICAGO   \n",
       "\n",
       "                                     dba_name    facility_type  \\\n",
       "0            RED SNAPPER FISH CHICKEN & PIZZA       Restaurant   \n",
       "1                            TAQUERIA MORELOS       Restaurant   \n",
       "2    HAMBURGER MARY'S CHICAGO/MARY'S REC ROOM       Restaurant   \n",
       "..                                        ...              ...   \n",
       "997       VICTORY CENTRE OF SOUTH CHICAGO SLF  ASSISTED LIVING   \n",
       "998                        TUXEDO JUNCTION #2    Grocery Store   \n",
       "999                                Bro-N-Laws       Restaurant   \n",
       "\n",
       "             inspection_date  inspection_id          inspection_type  \\\n",
       "0    2016-09-26T00:00:00.000        1965287                  Canvass   \n",
       "1    2014-02-06T00:00:00.000        1329698                  Canvass   \n",
       "2    2010-12-03T00:00:00.000         470787                      SFP   \n",
       "..                       ...            ...                      ...   \n",
       "997  2012-10-12T00:00:00.000        1296216                  Canvass   \n",
       "998  2011-07-14T00:00:00.000         585793                  Canvass   \n",
       "999  2015-08-19T00:00:00.000        1566470  Complaint Re-Inspection   \n",
       "\n",
       "      latitude   license_                                           location  \\\n",
       "0    41.880237  1991820.0  {'type': 'Point', 'coordinates': [-87.75722039...   \n",
       "1    41.931250  2099479.0  {'type': 'Point', 'coordinates': [-87.77590699...   \n",
       "2    41.979884  1933748.0  {'type': 'Point', 'coordinates': [-87.66842948...   \n",
       "..         ...        ...                                                ...   \n",
       "997  41.728214  1968546.0  {'type': 'Point', 'coordinates': [-87.54469279...   \n",
       "998  41.816186     8974.0  {'type': 'Point', 'coordinates': [-87.64345182...   \n",
       "999  41.895513  2288968.0  {'type': 'Point', 'coordinates': [-87.72216981...   \n",
       "\n",
       "     longitude             results             risk state  \\\n",
       "0   -87.757220  Pass w/ Conditions    Risk 1 (High)    IL   \n",
       "1   -87.775907                Pass    Risk 1 (High)    IL   \n",
       "2   -87.668429                Fail    Risk 1 (High)    IL   \n",
       "..         ...                 ...              ...   ...   \n",
       "997 -87.544693                Pass    Risk 1 (High)    IL   \n",
       "998 -87.643452                Fail  Risk 2 (Medium)    IL   \n",
       "999 -87.722170                Pass    Risk 1 (High)    IL   \n",
       "\n",
       "                                            violations    zip  \n",
       "0    35. WALLS, CEILINGS, ATTACHED EQUIPMENT CONSTR...  60644  \n",
       "1    33. FOOD AND NON-FOOD CONTACT EQUIPMENT UTENSI...  60639  \n",
       "2    6. HANDS WASHED AND CLEANED, GOOD HYGIENIC PRA...  60640  \n",
       "..                                                 ...    ...  \n",
       "997  34. FLOORS: CONSTRUCTED PER CODE, CLEANED, GOO...  60617  \n",
       "998  16. FOOD PROTECTED DURING STORAGE, PREPARATION...  60609  \n",
       "999  45. FOOD HANDLER REQUIREMENTS MET - Comments: ...  60651  \n",
       "\n",
       "[1000 rows x 17 columns]"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Type your solution Here\n",
    "df = pd.read_json(\"data/health_inspection_chi_sample.json\",\n",
    "                orient=\"records\",\n",
    "                lines=True,) #--because there are a many json objects not one\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# %load solutions/read_json.py\n",
    "import pandas as pd\n",
    "\n",
    "pd.read_json(\n",
    "    \"data/health_inspection_chi_sample.json\",\n",
    "    orient=\"records\",\n",
    "    lines=True,\n",
    ")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reading Data: web data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So far, we've seen some ways that we can read data from disk. As Data Scientists, we often need to go out and grab data from the Internet.\n",
    "\n",
    "Generally Python is \"batteries included\" and reading data from the Internet is no exception, but there are some *great* packages out there. [requests](http://docs.python-requests.org/en/master/) is one of them for making HTTP requests.\n",
    "\n",
    "Let's look at how we can use the [Chicago Data Portal](https://data.cityofchicago.org/) API to get this data in the first place. (I originally used San Francisco for this, but the data was just too clean to be terribly interesting.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We use the requests library to perform a GET request to the API, passing an optional query string via `params` to limit the returned number of records. The parameters are documented as part of the [Socrata Open Data API](https://dev.socrata.com/consumers/getting-started.html) (SODA)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "#note the addition of parens to split the url into two lines\n",
    "response = requests.get(\n",
    "    \"https://data.cityofchicago.org/'\n",
    "    'resource/cwig-ma7x.json\", \n",
    "    params=\"$limit=10\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Requests returns a [Reponse](http://docs.python-requests.org/en/master/api/#requests.Response) object with many helpful methods and attributes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Response [200]>"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "response"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "response.ok"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta = pd.read_json(response.content, orient='records')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can use the `head` method to peak at the first 5 rows of data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "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>:@computed_region_43wa_7qmu</th>\n",
       "      <th>:@computed_region_6mkv_f3dw</th>\n",
       "      <th>:@computed_region_awaf_s7ux</th>\n",
       "      <th>:@computed_region_bdys_3d7i</th>\n",
       "      <th>:@computed_region_vrxf_vc4k</th>\n",
       "      <th>address</th>\n",
       "      <th>aka_name</th>\n",
       "      <th>city</th>\n",
       "      <th>dba_name</th>\n",
       "      <th>facility_type</th>\n",
       "      <th>...</th>\n",
       "      <th>inspection_type</th>\n",
       "      <th>latitude</th>\n",
       "      <th>license_</th>\n",
       "      <th>location</th>\n",
       "      <th>longitude</th>\n",
       "      <th>results</th>\n",
       "      <th>risk</th>\n",
       "      <th>state</th>\n",
       "      <th>violations</th>\n",
       "      <th>zip</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>42</td>\n",
       "      <td>4447</td>\n",
       "      <td>33</td>\n",
       "      <td>264</td>\n",
       "      <td>73</td>\n",
       "      <td>3323 W 111TH ST</td>\n",
       "      <td>WONDERBURGER</td>\n",
       "      <td>CHICAGO</td>\n",
       "      <td>WONDERBURGER</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>...</td>\n",
       "      <td>License</td>\n",
       "      <td>41.691404</td>\n",
       "      <td>2626727</td>\n",
       "      <td>{'type': 'Point', 'coordinates': [-87.70428241...</td>\n",
       "      <td>-87.704282</td>\n",
       "      <td>Fail</td>\n",
       "      <td>Risk 3 (Low)</td>\n",
       "      <td>IL</td>\n",
       "      <td>10. ADEQUATE HANDWASHING SINKS PROPERLY SUPPLI...</td>\n",
       "      <td>60655.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>16</td>\n",
       "      <td>4451</td>\n",
       "      <td>46</td>\n",
       "      <td>536</td>\n",
       "      <td>76</td>\n",
       "      <td>1515 W DEVON AVE</td>\n",
       "      <td>LA UNICA FOOD MART</td>\n",
       "      <td>CHICAGO</td>\n",
       "      <td>LA UNICA FOOD MART, INC.</td>\n",
       "      <td>RESTAURANT/GROCERY STORE</td>\n",
       "      <td>...</td>\n",
       "      <td>Complaint</td>\n",
       "      <td>41.997993</td>\n",
       "      <td>491</td>\n",
       "      <td>{'type': 'Point', 'coordinates': [-87.66842788...</td>\n",
       "      <td>-87.668428</td>\n",
       "      <td>Fail</td>\n",
       "      <td>Risk 1 (High)</td>\n",
       "      <td>IL</td>\n",
       "      <td>2. CITY OF CHICAGO FOOD SERVICE SANITATION CER...</td>\n",
       "      <td>60660.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>46</td>\n",
       "      <td>14917</td>\n",
       "      <td>41</td>\n",
       "      <td>63</td>\n",
       "      <td>29</td>\n",
       "      <td>811 W FULTON MARKET ST</td>\n",
       "      <td>THE WING FULTON</td>\n",
       "      <td>NaN</td>\n",
       "      <td>THE WING FULTON</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>...</td>\n",
       "      <td>License</td>\n",
       "      <td>41.886684</td>\n",
       "      <td>2642303</td>\n",
       "      <td>{'type': 'Point', 'coordinates': [-87.64800067...</td>\n",
       "      <td>-87.648001</td>\n",
       "      <td>Fail</td>\n",
       "      <td>Risk 2 (Medium)</td>\n",
       "      <td>IL</td>\n",
       "      <td>3. MANAGEMENT, FOOD EMPLOYEE AND CONDITIONAL E...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>41</td>\n",
       "      <td>21560</td>\n",
       "      <td>24</td>\n",
       "      <td>518</td>\n",
       "      <td>25</td>\n",
       "      <td>1610 W CHICAGO AVE</td>\n",
       "      <td>LOOP TAVERN</td>\n",
       "      <td>CHICAGO</td>\n",
       "      <td>LOOP TAVERN</td>\n",
       "      <td>Liquor</td>\n",
       "      <td>...</td>\n",
       "      <td>License</td>\n",
       "      <td>41.896175</td>\n",
       "      <td>2632196</td>\n",
       "      <td>{'type': 'Point', 'coordinates': [-87.66777924...</td>\n",
       "      <td>-87.667779</td>\n",
       "      <td>Fail</td>\n",
       "      <td>Risk 3 (Low)</td>\n",
       "      <td>IL</td>\n",
       "      <td>5. PROCEDURES FOR RESPONDING TO VOMITING AND D...</td>\n",
       "      <td>60622.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>39</td>\n",
       "      <td>22616</td>\n",
       "      <td>37</td>\n",
       "      <td>20</td>\n",
       "      <td>31</td>\n",
       "      <td>4521 N SHERIDAN RD</td>\n",
       "      <td>WABI SABI ROTARY</td>\n",
       "      <td>CHICAGO</td>\n",
       "      <td>WABI SABI ROTARY</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>...</td>\n",
       "      <td>License</td>\n",
       "      <td>41.964336</td>\n",
       "      <td>2647241</td>\n",
       "      <td>{'type': 'Point', 'coordinates': [-87.65462704...</td>\n",
       "      <td>-87.654627</td>\n",
       "      <td>Pass w/ Conditions</td>\n",
       "      <td>Risk 1 (High)</td>\n",
       "      <td>IL</td>\n",
       "      <td>5. PROCEDURES FOR RESPONDING TO VOMITING AND D...</td>\n",
       "      <td>60640.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   :@computed_region_43wa_7qmu  :@computed_region_6mkv_f3dw  \\\n",
       "0                           42                         4447   \n",
       "1                           16                         4451   \n",
       "2                           46                        14917   \n",
       "3                           41                        21560   \n",
       "4                           39                        22616   \n",
       "\n",
       "   :@computed_region_awaf_s7ux  :@computed_region_bdys_3d7i  \\\n",
       "0                           33                          264   \n",
       "1                           46                          536   \n",
       "2                           41                           63   \n",
       "3                           24                          518   \n",
       "4                           37                           20   \n",
       "\n",
       "   :@computed_region_vrxf_vc4k                  address            aka_name  \\\n",
       "0                           73         3323 W 111TH ST         WONDERBURGER   \n",
       "1                           76        1515 W DEVON AVE   LA UNICA FOOD MART   \n",
       "2                           29  811 W FULTON MARKET ST      THE WING FULTON   \n",
       "3                           25      1610 W CHICAGO AVE          LOOP TAVERN   \n",
       "4                           31      4521 N SHERIDAN RD     WABI SABI ROTARY   \n",
       "\n",
       "      city                  dba_name             facility_type   ...     \\\n",
       "0  CHICAGO              WONDERBURGER                Restaurant   ...      \n",
       "1  CHICAGO  LA UNICA FOOD MART, INC.  RESTAURANT/GROCERY STORE   ...      \n",
       "2      NaN           THE WING FULTON                Restaurant   ...      \n",
       "3  CHICAGO               LOOP TAVERN                    Liquor   ...      \n",
       "4  CHICAGO          WABI SABI ROTARY                Restaurant   ...      \n",
       "\n",
       "  inspection_type   latitude license_  \\\n",
       "0         License  41.691404  2626727   \n",
       "1       Complaint  41.997993      491   \n",
       "2         License  41.886684  2642303   \n",
       "3         License  41.896175  2632196   \n",
       "4         License  41.964336  2647241   \n",
       "\n",
       "                                            location  longitude  \\\n",
       "0  {'type': 'Point', 'coordinates': [-87.70428241... -87.704282   \n",
       "1  {'type': 'Point', 'coordinates': [-87.66842788... -87.668428   \n",
       "2  {'type': 'Point', 'coordinates': [-87.64800067... -87.648001   \n",
       "3  {'type': 'Point', 'coordinates': [-87.66777924... -87.667779   \n",
       "4  {'type': 'Point', 'coordinates': [-87.65462704... -87.654627   \n",
       "\n",
       "              results             risk state  \\\n",
       "0                Fail     Risk 3 (Low)    IL   \n",
       "1                Fail    Risk 1 (High)    IL   \n",
       "2                Fail  Risk 2 (Medium)    IL   \n",
       "3                Fail     Risk 3 (Low)    IL   \n",
       "4  Pass w/ Conditions    Risk 1 (High)    IL   \n",
       "\n",
       "                                          violations      zip  \n",
       "0  10. ADEQUATE HANDWASHING SINKS PROPERLY SUPPLI...  60655.0  \n",
       "1  2. CITY OF CHICAGO FOOD SERVICE SANITATION CER...  60660.0  \n",
       "2  3. MANAGEMENT, FOOD EMPLOYEE AND CONDITIONAL E...      NaN  \n",
       "3  5. PROCEDURES FOR RESPONDING TO VOMITING AND D...  60622.0  \n",
       "4  5. PROCEDURES FOR RESPONDING TO VOMITING AND D...  60640.0  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dta.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Of course, pandas can also load data directly from a URL, but I encourage you to reach for `requests` as often as you need it."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise\n",
    "\n",
    "Try passing the URL above to `pd.read_json`. What happens?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{\"address\":\"5255 W MADISON ST \",\"aka_name\":\"RED SNAPPER FISH CHICKEN & PIZZA\",\"city\":\"CHICAGO\",\"dba_name\":\"RED SNAPPER FISH CHICKEN & PIZZA\",\"facility_type\":\"Restaurant\",\"inspection_date\":\"2016-09-26T00:00:00.000\",\"inspection_id\":1965287,\"inspection_type\":\"Canvass\",\"latitude\":41.8802365439,\"license_\":1991820.0,\"location\":{\"type\":\"Point\",\"coordinates\":[-87.7572203921,41.8802365439]},\"longitude\":-87.7572203921,\"results\":\"Pass w\\/ Conditions\",\"risk\":\"Risk 1 (High)\",\"state\":\"IL\",\"violations\":\"35. WALLS, CEILINGS, ATTACHED EQUIPMENT CONSTRUCTED PER CODE: GOOD REPAIR, SURFACES CLEAN AND DUST-LESS CLEANING METHODS - Comments: MUST CLEAN THE WALLS AT WALL BASE NEAR THE MIXER IN REAR OF PREMISES AND THE PREP AREA OF FOOD SPILLS AND CLEAN THE WALL VENT IN PREP AREA ,INSTRUCTED TO CLEAN AND MAINTAIN AREA | 33. FOOD AND NON-FOOD CONTACT EQUIPMENT UTENSILS CLEAN, FREE OF ABRASIVE DETERGENTS - Comments: MUST CLEAN THE INTERIOR PANEL OF THE ICE MACHINE IN REAR OF PREMISES | 34. FLOORS: CONSTRUCTED PER CODE, CLEANED, GOOD REPAIR, COVING INSTALLED, DUST-LESS CLEANING METHODS USED - Comments: MUST CLEAN FLOORS THROUGHOUT PREMISES,ALONG WALL BASE AND CORNERS OF GREASE AND DIRT BUILD-UP,INSTRUCTED TO CLEAN AND MAINTAIN AREA | 36. LIGHTING: REQUIRED MINIMUM FOOT-CANDLES OF LIGHT PROVIDED, FIXTURES SHIELDED - Comments: MUST PROVIDE A LIGHT SHIELD COVER FOR THE WALK-IN FREEZER IN REAR OF PREMISES | 22. DISH MACHINES: PROVIDED WITH ACCURATE THERMOMETERS, CHEMICAL TEST KITS AND SUITABLE GAUGE COCK - Comments: OBSERVED NO CHEMICAL TEST STRIPS TO TEST THE SANITIZER AT 3 COMPARTMENT SINK,WHEN SINK IS SET UP FOR WASH,RINSE AND SANITIZE,INSTRUCTED TO PROVIDE SERIOUS VIOLATION 7-38-030 | 40. REFRIGERATION AND METAL STEM THERMOMETERS PROVIDED AND CONSPICUOUS - Comments: MUST PROVIDE A THERMOMETER INSIDE THE PREP COOLER IN PREP AREA\",\"zip\":60644.0}\r\n"
     ]
    }
   ],
   "source": [
    "!head -n 1 data/health_inspection_chi_sample.json #inefficent way to store data -column orientation\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Type your solution here\n",
    "url = (\"https://data.cityofchicago.org/resource/cwig-ma7x.json\")\n",
    "df = pd.read_json(url, orient= 'records')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.shape\n",
    "df.head(1)\n",
    "df = df.iloc[:,5:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "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>address</th>\n",
       "      <th>aka_name</th>\n",
       "      <th>city</th>\n",
       "      <th>dba_name</th>\n",
       "      <th>facility_type</th>\n",
       "      <th>inspection_date</th>\n",
       "      <th>inspection_id</th>\n",
       "      <th>inspection_type</th>\n",
       "      <th>latitude</th>\n",
       "      <th>license_</th>\n",
       "      <th>location</th>\n",
       "      <th>longitude</th>\n",
       "      <th>results</th>\n",
       "      <th>risk</th>\n",
       "      <th>state</th>\n",
       "      <th>violations</th>\n",
       "      <th>zip</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4521 N SHERIDAN RD</td>\n",
       "      <td>WABI SABI ROTARY</td>\n",
       "      <td>CHICAGO</td>\n",
       "      <td>WABI SABI ROTARY</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>2019-03-21T00:00:00.000</td>\n",
       "      <td>2279748</td>\n",
       "      <td>Canvass Re-Inspection</td>\n",
       "      <td>41.964336</td>\n",
       "      <td>2492706</td>\n",
       "      <td>{'type': 'Point', 'coordinates': [-87.65462704...</td>\n",
       "      <td>-87.654627</td>\n",
       "      <td>Pass</td>\n",
       "      <td>Risk 1 (High)</td>\n",
       "      <td>IL</td>\n",
       "      <td>37. FOOD PROPERLY LABELED; ORIGINAL CONTAINER ...</td>\n",
       "      <td>60640.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3323 W 111TH ST</td>\n",
       "      <td>WONDERBURGER</td>\n",
       "      <td>CHICAGO</td>\n",
       "      <td>WONDERBURGER</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>2019-03-21T00:00:00.000</td>\n",
       "      <td>2279749</td>\n",
       "      <td>License</td>\n",
       "      <td>41.691404</td>\n",
       "      <td>2626726</td>\n",
       "      <td>{'type': 'Point', 'coordinates': [-87.70428241...</td>\n",
       "      <td>-87.704282</td>\n",
       "      <td>Fail</td>\n",
       "      <td>Risk 1 (High)</td>\n",
       "      <td>IL</td>\n",
       "      <td>3. MANAGEMENT, FOOD EMPLOYEE AND CONDITIONAL E...</td>\n",
       "      <td>60655.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>811 W FULTON MARKET ST</td>\n",
       "      <td>THE WING FULTON</td>\n",
       "      <td>NaN</td>\n",
       "      <td>THE WING FULTON</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>2019-03-21T00:00:00.000</td>\n",
       "      <td>2279779</td>\n",
       "      <td>License</td>\n",
       "      <td>41.886684</td>\n",
       "      <td>2642303</td>\n",
       "      <td>{'type': 'Point', 'coordinates': [-87.64800067...</td>\n",
       "      <td>-87.648001</td>\n",
       "      <td>Fail</td>\n",
       "      <td>Risk 2 (Medium)</td>\n",
       "      <td>IL</td>\n",
       "      <td>3. MANAGEMENT, FOOD EMPLOYEE AND CONDITIONAL E...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3323 W 111TH ST</td>\n",
       "      <td>WONDERBURGER</td>\n",
       "      <td>CHICAGO</td>\n",
       "      <td>WONDERBURGER</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>2019-03-21T00:00:00.000</td>\n",
       "      <td>2279757</td>\n",
       "      <td>License</td>\n",
       "      <td>41.691404</td>\n",
       "      <td>2626727</td>\n",
       "      <td>{'type': 'Point', 'coordinates': [-87.70428241...</td>\n",
       "      <td>-87.704282</td>\n",
       "      <td>Fail</td>\n",
       "      <td>Risk 3 (Low)</td>\n",
       "      <td>IL</td>\n",
       "      <td>10. ADEQUATE HANDWASHING SINKS PROPERLY SUPPLI...</td>\n",
       "      <td>60655.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1610 W CHICAGO AVE</td>\n",
       "      <td>LOOP TAVERN</td>\n",
       "      <td>CHICAGO</td>\n",
       "      <td>LOOP TAVERN</td>\n",
       "      <td>Liquor</td>\n",
       "      <td>2019-03-21T00:00:00.000</td>\n",
       "      <td>2279758</td>\n",
       "      <td>License</td>\n",
       "      <td>41.896175</td>\n",
       "      <td>2632196</td>\n",
       "      <td>{'type': 'Point', 'coordinates': [-87.66777924...</td>\n",
       "      <td>-87.667779</td>\n",
       "      <td>Fail</td>\n",
       "      <td>Risk 3 (Low)</td>\n",
       "      <td>IL</td>\n",
       "      <td>5. PROCEDURES FOR RESPONDING TO VOMITING AND D...</td>\n",
       "      <td>60622.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   address          aka_name     city          dba_name  \\\n",
       "0      4521 N SHERIDAN RD   WABI SABI ROTARY  CHICAGO  WABI SABI ROTARY   \n",
       "1         3323 W 111TH ST       WONDERBURGER  CHICAGO      WONDERBURGER   \n",
       "2  811 W FULTON MARKET ST    THE WING FULTON      NaN   THE WING FULTON   \n",
       "3         3323 W 111TH ST       WONDERBURGER  CHICAGO      WONDERBURGER   \n",
       "4      1610 W CHICAGO AVE        LOOP TAVERN  CHICAGO       LOOP TAVERN   \n",
       "\n",
       "  facility_type          inspection_date  inspection_id  \\\n",
       "0    Restaurant  2019-03-21T00:00:00.000        2279748   \n",
       "1    Restaurant  2019-03-21T00:00:00.000        2279749   \n",
       "2    Restaurant  2019-03-21T00:00:00.000        2279779   \n",
       "3    Restaurant  2019-03-21T00:00:00.000        2279757   \n",
       "4        Liquor  2019-03-21T00:00:00.000        2279758   \n",
       "\n",
       "         inspection_type   latitude  license_  \\\n",
       "0  Canvass Re-Inspection  41.964336   2492706   \n",
       "1                License  41.691404   2626726   \n",
       "2                License  41.886684   2642303   \n",
       "3                License  41.691404   2626727   \n",
       "4                License  41.896175   2632196   \n",
       "\n",
       "                                            location  longitude results  \\\n",
       "0  {'type': 'Point', 'coordinates': [-87.65462704... -87.654627    Pass   \n",
       "1  {'type': 'Point', 'coordinates': [-87.70428241... -87.704282    Fail   \n",
       "2  {'type': 'Point', 'coordinates': [-87.64800067... -87.648001    Fail   \n",
       "3  {'type': 'Point', 'coordinates': [-87.70428241... -87.704282    Fail   \n",
       "4  {'type': 'Point', 'coordinates': [-87.66777924... -87.667779    Fail   \n",
       "\n",
       "              risk state                                         violations  \\\n",
       "0    Risk 1 (High)    IL  37. FOOD PROPERLY LABELED; ORIGINAL CONTAINER ...   \n",
       "1    Risk 1 (High)    IL  3. MANAGEMENT, FOOD EMPLOYEE AND CONDITIONAL E...   \n",
       "2  Risk 2 (Medium)    IL  3. MANAGEMENT, FOOD EMPLOYEE AND CONDITIONAL E...   \n",
       "3     Risk 3 (Low)    IL  10. ADEQUATE HANDWASHING SINKS PROPERLY SUPPLI...   \n",
       "4     Risk 3 (Low)    IL  5. PROCEDURES FOR RESPONDING TO VOMITING AND D...   \n",
       "\n",
       "       zip  \n",
       "0  60640.0  \n",
       "1  60655.0  \n",
       "2      NaN  \n",
       "3  60655.0  \n",
       "4  60622.0  "
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# %load solutions/read_url_json.py\n",
    "import pandas as pd\n",
    "\n",
    "url = ('https://data.cityofchicago.org/'\n",
    "              'resource/cwig-ma7x.json?$limit=5')\n",
    "pd.read_json(url, orient='records')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice how you can split a string across lines. This can be a very handy tip for improving readability, by splitting a string and putting it in parentheses, we preserve a single string."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'super long string split across lines'"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(\"super \"\n",
    " \"long \"\n",
    " \"string \"\n",
    " \"split \"\n",
    " \"across \"\n",
    " \"lines\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Pandas DataReader"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In addition to the core I/O functionality in pandas, there is also the [pandas-datareader](https://pandas-datareader.readthedocs.io/en/latest/) project. This package provides programmatic access to data sets from\n",
    "\n",
    "* Yahoo! Finance (deprecated)\n",
    "* Google Finance\n",
    "* Enigma\n",
    "* Quandl\n",
    "* FRED\n",
    "* Fama/French\n",
    "* World Bank\n",
    "* OECD\n",
    "* Eurostat\n",
    "* EDGAR Index (deprecated)\n",
    "* TSP Fund Data\n",
    "* Nasdaq Trader Symbol Definitions\n",
    "* Morningstar\n",
    "* Etc."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Further Resources"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Sometimes we need to be resourceful in order to get data. Knowing how to scrape the web can really come in handy.\n",
    "\n",
    "We're not going to go into details today, but you'll likely find libraries like [Beautiful Soup](https://www.crummy.com/software/BeautifulSoup/), [lxml](http://lxml.de/), and [mechanize](https://mechanize.readthedocs.io/en/latest/) to be helpful. \n",
    "\n",
    "There's also a `read_html` function in pandas that will quickly scrape HTML tables for you and put them into a DataFrame. "
   ]
  }
 ],
 "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.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}