Pandas-Vectorized-String-Ops.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Vectorized String Operations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Introduction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 4,  6, 10, 14, 22, 26])"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numpy as np\n",
    "x = np.array([2, 3, 5, 7, 11, 13])\n",
    "x * 2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- NumPy does not do vectorization for string arrays - thus you're stuck using a loop syntax:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Peter', 'Paul', 'Mary', 'Guido']"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = ['peter', 'Paul', 'MARY', 'gUIDO']\n",
    "[s.capitalize() for s in data]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- This will break if there are any missing values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "ename": "AttributeError",
     "evalue": "'NoneType' object has no attribute 'capitalize'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mAttributeError\u001b[0m                            Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-6-fc1d891ab539>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0mdata\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m[\u001b[0m\u001b[0;34m'peter'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'Paul'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'MARY'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'gUIDO'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 2\u001b[0;31m \u001b[0;34m[\u001b[0m\u001b[0ms\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcapitalize\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mfor\u001b[0m \u001b[0ms\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mdata\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m<ipython-input-6-fc1d891ab539>\u001b[0m in \u001b[0;36m<listcomp>\u001b[0;34m(.0)\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0mdata\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m[\u001b[0m\u001b[0;34m'peter'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'Paul'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'MARY'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'gUIDO'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 2\u001b[0;31m \u001b[0;34m[\u001b[0m\u001b[0ms\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcapitalize\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mfor\u001b[0m \u001b[0ms\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mdata\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mAttributeError\u001b[0m: 'NoneType' object has no attribute 'capitalize'"
     ]
    }
   ],
   "source": [
    "data = ['peter', 'Paul', None, 'MARY', 'gUIDO']\n",
    "[s.capitalize() for s in data]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Pandas solves this problem via the ``str`` attribute of Pandas Series and Index objects containing strings:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    peter\n",
       "1     Paul\n",
       "2     None\n",
       "3     MARY\n",
       "4    gUIDO\n",
       "dtype: object"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "names = pd.Series(data)\n",
    "names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    Peter\n",
       "1     Paul\n",
       "2     None\n",
       "3     Mary\n",
       "4    Guido\n",
       "dtype: object"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "names.str.capitalize()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Reminder: using tab completion on this ``str`` attribute will list all the vectorized string methods available to Pandas."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Tables of Pandas String Methods"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "monte = pd.Series(['Graham Chapman', 'John Cleese', 'Terry Gilliam',\n",
    "                   'Eric Idle', 'Terry Jones', 'Michael Palin'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Methods similar to Python string methods\n",
    "Nearly all Python's built-in string methods are mirrored by a Pandas vectorized string method. Here is a list of Pandas ``str`` methods that mirror Python string methods:\n",
    "\n",
    "|             |                  |                  |                  |\n",
    "|-------------|------------------|------------------|------------------|\n",
    "|``len()``    | ``lower()``      | ``translate()``  | ``islower()``    | \n",
    "|``ljust()``  | ``upper()``      | ``startswith()`` | ``isupper()``    | \n",
    "|``rjust()``  | ``find()``       | ``endswith()``   | ``isnumeric()``  | \n",
    "|``center()`` | ``rfind()``      | ``isalnum()``    | ``isdecimal()``  | \n",
    "|``zfill()``  | ``index()``      | ``isalpha()``    | ``split()``      | \n",
    "|``strip()``  | ``rindex()``     | ``isdigit()``    | ``rsplit()``     | \n",
    "|``rstrip()`` | ``capitalize()`` | ``isspace()``    | ``partition()``  | \n",
    "|``lstrip()`` |  ``swapcase()``  |  ``istitle()``   | ``rpartition()`` |\n",
    "\n",
    "- Notice that these have various return values. Some, like ``lower()``, return a series of strings:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    graham chapman\n",
       "1       john cleese\n",
       "2     terry gilliam\n",
       "3         eric idle\n",
       "4       terry jones\n",
       "5     michael palin\n",
       "dtype: object"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "monte.str.lower()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Some return numbers:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    14\n",
       "1    11\n",
       "2    13\n",
       "3     9\n",
       "4    11\n",
       "5    13\n",
       "dtype: int64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "monte.str.len()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Or Boolean values:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    False\n",
       "1    False\n",
       "2     True\n",
       "3    False\n",
       "4     True\n",
       "5    False\n",
       "dtype: bool"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "monte.str.startswith('T')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Others return lists or other compound values for each element:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    [Graham, Chapman]\n",
       "1       [John, Cleese]\n",
       "2     [Terry, Gilliam]\n",
       "3         [Eric, Idle]\n",
       "4       [Terry, Jones]\n",
       "5     [Michael, Palin]\n",
       "dtype: object"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "monte.str.split()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Methods using regular expressions\n",
    "\n",
    "- Several methods accept regular expressions. They follow the API conventions of Python's built-in ``re`` module:\n",
    "\n",
    "| Method | Description |\n",
    "|--------|-------------|\n",
    "| ``match()`` | Call ``re.match()`` on each element, returning a boolean. |\n",
    "| ``extract()`` | Call ``re.match()`` on each element, returning matched groups as strings.|\n",
    "| ``findall()`` | Call ``re.findall()`` on each element |\n",
    "| ``replace()`` | Replace occurrences of pattern with some other string|\n",
    "| ``contains()`` | Call ``re.search()`` on each element, returning a boolean |\n",
    "| ``count()`` | Count occurrences of pattern|\n",
    "| ``split()``   | Equivalent to ``str.split()``, but accepts regexps |\n",
    "| ``rsplit()`` | Equivalent to ``str.rsplit()``, but accepts regexps |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- For example, we can extract the first name from each by asking for a contiguous group of characters at the beginning of each element."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     Graham\n",
       "1       John\n",
       "2      Terry\n",
       "3       Eric\n",
       "4      Terry\n",
       "5    Michael\n",
       "dtype: object"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "monte.str.extract('([A-Za-z]+)', expand=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Find all names that start and end with a consonant, using the start-of-string (``^``) and end-of-string (``$``) regular expression characters:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    [Graham Chapman]\n",
       "1                  []\n",
       "2     [Terry Gilliam]\n",
       "3                  []\n",
       "4       [Terry Jones]\n",
       "5     [Michael Palin]\n",
       "dtype: object"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "monte.str.findall(r'^[^AEIOU].*[^aeiou]$')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Miscellaneous methods\n",
    "\n",
    "| Method | Description |\n",
    "|--------|-------------|\n",
    "| ``get()`` | Index each element |\n",
    "| ``slice()`` | Slice each element|\n",
    "| ``slice_replace()`` | Replace slice in each element with passed value|\n",
    "| ``cat()``      | Concatenate strings|\n",
    "| ``repeat()`` | Repeat values |\n",
    "| ``normalize()`` | Return Unicode form of string |\n",
    "| ``pad()`` | Add whitespace to left, right, or both sides of strings|\n",
    "| ``wrap()`` | Split long strings into lines with length less than a given width|\n",
    "| ``join()`` | Join strings in each element of the Series with passed separator|\n",
    "| ``get_dummies()`` | extract dummy variables as a dataframe |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Vectorized item access and slicing\n",
    "\n",
    "- ``get()`` and ``slice()`` provide vectorized element access from each string array. For example, we can get the first three characters of each array using ``str.slice(0, 3)``. This behavior is also available through Python's normal indexing syntax–for example, ``df.str.slice(0, 3)`` is equivalent to ``df.str[0:3]``:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    Gra\n",
       "1    Joh\n",
       "2    Ter\n",
       "3    Eri\n",
       "4    Ter\n",
       "5    Mic\n",
       "dtype: object"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "monte.str[0:3]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Indexing via ``df.str.get(i)`` and ``df.str[i]`` works similarly. \n",
    "\n",
    "- ``get()`` and ``slice()`` also let you access elements of arrays returned by ``split()``. For example, to extract the last name of each entry, we can combine ``split()`` and ``get()``:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    Chapman\n",
       "1     Cleese\n",
       "2    Gilliam\n",
       "3       Idle\n",
       "4      Jones\n",
       "5      Palin\n",
       "dtype: object"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "monte.str.split().str.get(-1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Indicator variables\n",
    "\n",
    "- ``get_dummies()`` is useful when your data has a column containing a coded indicator. For example, we might have a dataset that contains information in the form of codes, such as A=\"born in America,\" B=\"born in the United Kingdom,\" C=\"likes cheese,\" D=\"likes spam\":"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>info</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>B|C|D</td>\n",
       "      <td>Graham Chapman</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B|D</td>\n",
       "      <td>John Cleese</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A|C</td>\n",
       "      <td>Terry Gilliam</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>B|D</td>\n",
       "      <td>Eric Idle</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>B|C</td>\n",
       "      <td>Terry Jones</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>B|C|D</td>\n",
       "      <td>Michael Palin</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    info            name\n",
       "0  B|C|D  Graham Chapman\n",
       "1    B|D     John Cleese\n",
       "2    A|C   Terry Gilliam\n",
       "3    B|D       Eric Idle\n",
       "4    B|C     Terry Jones\n",
       "5  B|C|D   Michael Palin"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "full_monte = pd.DataFrame({'name': monte,\n",
    "                           'info': ['B|C|D', 'B|D', 'A|C',\n",
    "                                    'B|D', 'B|C', 'B|C|D']})\n",
    "full_monte"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- ``get_dummies()`` lets you split these indicator variables into a ``DataFrame``:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   A  B  C  D\n",
       "0  0  1  1  1\n",
       "1  0  1  0  1\n",
       "2  1  0  1  0\n",
       "3  0  1  0  1\n",
       "4  0  1  1  0\n",
       "5  0  1  1  1"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "full_monte['info'].str.get_dummies('|')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Example: Recipe Database\n",
    "\n",
    "- Vectorized string operations become most useful when cleaning up messy, real-world data. Let's parse some recipe data into ingredient lists, so we can quickly find a recipe based on some ingredients we have on hand.\n",
    "\n",
    "- The scripts used to compile this can be found at https://github.com/fictivekin/openrecipes, and the link to the current version of the database is found there as well.\n",
    "\n",
    "- See [issue# 62](https://github.com/jakevdp/PythonDataScienceHandbook/issues/62) - updated dataset is [here](https://s3.amazonaws.com/openrecipes/20170107-061401-recipeitems.json.gz)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "#!curl -O http://openrecipes.s3.amazonaws.com/recipeitems-latest.json.gz\n",
    "#!gunzip recipeitems-latest.json.gz"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "- The database is in JSON format - try ``pd.read_json`` to read it:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ValueError: Trailing data\n"
     ]
    }
   ],
   "source": [
    "try:\n",
    "    #recipes = pd.read_json('recipeitems-latest.json')\n",
    "    recipes = pd.read_json('20170107-061401-recipeitems.json')\n",
    "except ValueError as e:\n",
    "    print(\"ValueError:\", e)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Oops! We get a ``ValueError`` mentioning that there is \"trailing data.\"\n",
    "Searching for the text of this error on the Internet, it seems that it's due to using a file in which *each line* is itself a valid JSON, but the full file is not. Let's check if this interpretation is true:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2, 12)"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with open('20170107-061401-recipeitems.json') as f:\n",
    "    line = f.readline()\n",
    "pd.read_json(line).shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Yes, apparently each line is a valid JSON, so we'll need to string them together. One way we can do this is to actually construct a string representation containing all these JSON entries, and then load the whole thing with ``pd.read_json``:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# read the entire file into a Python array\n",
    "with open('20170107-061401-recipeitems.json', 'r') as f:\n",
    "    # Extract each line\n",
    "    data = (line.strip() for line in f)\n",
    "    # Reformat so each line is the element of a list\n",
    "    data_json = \"[{0}]\".format(','.join(data))\n",
    "# read the result as a JSON\n",
    "recipes = pd.read_json(data_json)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(173278, 17)"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "recipes.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Examine one row to see what we have:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "_id                                {'$oid': '5160756b96cc62079cc2db15'}\n",
       "cookTime                                                          PT30M\n",
       "creator                                                             NaN\n",
       "dateModified                                                        NaN\n",
       "datePublished                                                2013-03-11\n",
       "description           Late Saturday afternoon, after Marlboro Man ha...\n",
       "image                 http://static.thepioneerwoman.com/cooking/file...\n",
       "ingredients           Biscuits\\n3 cups All-purpose Flour\\n2 Tablespo...\n",
       "name                                    Drop Biscuits and Sausage Gravy\n",
       "prepTime                                                          PT10M\n",
       "recipeCategory                                                      NaN\n",
       "recipeInstructions                                                  NaN\n",
       "recipeYield                                                          12\n",
       "source                                                  thepioneerwoman\n",
       "totalTime                                                           NaN\n",
       "ts                                             {'$date': 1365276011104}\n",
       "url                   http://thepioneerwoman.com/cooking/2013/03/dro...\n",
       "Name: 0, dtype: object"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "recipes.iloc[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- There is a lot of information there, but much of it is in a very messy form, as is typical of data scraped from the Web. The ingredient list is in string format; we're going to have to carefully extract the information we're interested in. Describe the ingredients:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    173278.000000\n",
       "mean        244.617926\n",
       "std         146.705285\n",
       "min           0.000000\n",
       "25%         147.000000\n",
       "50%         221.000000\n",
       "75%         314.000000\n",
       "max        9067.000000\n",
       "Name: ingredients, dtype: float64"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "recipes.ingredients.str.len().describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- The ingredient lists averages ~244 characters. Which recipe has the longest ingredient list?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Carrot Pineapple Spice &amp; Brownie Layer Cake with Whipped Cream &amp; Cream Cheese Frosting and Marzipan Carrots'"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "recipes.name[np.argmax(recipes.ingredients.str.len())]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- How many of the recipes are for breakfast?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3524"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "recipes.description.str.contains('[Bb]reakfast').sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- How many of the recipes list cinnamon as an ingredient?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "10526"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "recipes.ingredients.str.contains('[Cc]innamon').sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Any recipes misspell the ingredient as \"cinamon\"?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "11"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "recipes.ingredients.str.contains('[Cc]inamon').sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### A simple recipe recommender\n",
    "\n",
    "- Design a simple recipe recommendation system: given a list of ingredients, find a recipe that uses all those ingredients.\n",
    "- While conceptually straightforward, the task is complicated by the heterogeneity of the data: there is no easy operation, for example, to extract a clean list of ingredients from each row.\n",
    "- So we will cheat by starting with a list of common ingredients and search to see whether they are in each recipe's ingredient list.\n",
    "- For simplicity, start with herbs and spices."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "spice_list = ['salt', 'pepper', 'oregano', 'sage', 'parsley',\n",
    "              'rosemary', 'tarragon', 'thyme', 'paprika', 'cumin']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Build a Boolean ``DataFrame`` consisting of True and False values, indicating whether this ingredient appears in the list."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cumin</th>\n",
       "      <th>oregano</th>\n",
       "      <th>paprika</th>\n",
       "      <th>parsley</th>\n",
       "      <th>pepper</th>\n",
       "      <th>rosemary</th>\n",
       "      <th>sage</th>\n",
       "      <th>salt</th>\n",
       "      <th>tarragon</th>\n",
       "      <th>thyme</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   cumin  oregano  paprika  parsley  pepper  rosemary   sage   salt  tarragon  \\\n",
       "0  False    False    False    False   False     False   True  False     False   \n",
       "1  False    False    False    False   False     False  False  False     False   \n",
       "2   True    False    False    False    True     False  False   True     False   \n",
       "3  False    False    False    False   False     False  False  False     False   \n",
       "4  False    False    False    False   False     False  False  False     False   \n",
       "\n",
       "   thyme  \n",
       "0  False  \n",
       "1  False  \n",
       "2  False  \n",
       "3  False  \n",
       "4  False  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import re\n",
    "spice_df = pd.DataFrame(dict((spice, recipes.ingredients.str.contains(spice, re.IGNORECASE))\n",
    "                             for spice in spice_list))\n",
    "spice_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Let's say we'd want a recipe that uses parsley, paprika, and tarragon. Use the ``query()`` method of ``DataFrame``s."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "10"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "selection = spice_df.query('parsley & paprika & tarragon')\n",
    "len(selection)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Use the index returned by this selection to discover the names of the 10 recipes that have this combination."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2069      All cremat with a Little Gem, dandelion and wa...\n",
       "74964                         Lobster with Thermidor butter\n",
       "93768      Burton's Southern Fried Chicken with White Gravy\n",
       "113926                     Mijo's Slow Cooker Shredded Beef\n",
       "137686                     Asparagus Soup with Poached Eggs\n",
       "140530                                 Fried Oyster Po’boys\n",
       "158475                Lamb shank tagine with herb tabbouleh\n",
       "158486                 Southern fried chicken in buttermilk\n",
       "163175            Fried Chicken Sliders with Pickles + Slaw\n",
       "165243                        Bar Tartine Cauliflower Salad\n",
       "Name: name, dtype: object"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "recipes.name[selection.index]"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}