Pandas-Performance-Eval-and-Query.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# High-Performance Pandas: eval() and query()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- NumPy and Pandas rely on the creation of temporary intermediate objects. They can cause increased computational time and memory use.\n",
    "- Pandas v0.13 includes ``eval()`` and ``query()`` functions, which allow you to directly access C-speed operations without the need for intermediate arrays. They rely on the [Numexpr](https://github.com/pydata/numexpr) package."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How Compound Expressions cause slowdowns\n",
    "- Consider adding the elements of two arrays in Numpy vs Python:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1.73 ms ± 28.7 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)\n"
     ]
    }
   ],
   "source": [
    "import numpy as np\n",
    "rng = np.random.RandomState(42)\n",
    "x = rng.rand(1000000)\n",
    "y = rng.rand(1000000)\n",
    "%timeit x + y"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "168 ms ± 2.29 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)\n"
     ]
    }
   ],
   "source": [
    "%timeit np.fromiter((xi + yi for xi, yi in zip(x, y)), dtype=x.dtype, count=len(x))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Now consider what happens when computing compound expressions. NumPy evaluates each subexpression, which looks like this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "mask = (x > 0.5) & (y < 0.5)\n",
    "tmp1 = (x > 0.5)\n",
    "tmp2 = (y < 0.5)\n",
    "mask = tmp1 & tmp2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- *every intermediate step is explicitly allocated in memory*. If the ``x`` and ``y`` arrays are very large, this is a problem.\n",
    "- The Numexpr library allows you to eval this compound expression element by element, without the need to allocate full intermediate arrays. The [Numexpr documentation](https://github.com/pydata/numexpr) has more details, but for the time being it is sufficient to say that the library accepts a *string* giving the NumPy-style expression you'd like to compute:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numexpr\n",
    "mask_numexpr = numexpr.evaluate('(x > 0.5) & (y < 0.5)')\n",
    "\n",
    "# NumPy allclose(): returns True if two arrays are element-wise equal within a tolerance.\n",
    "np.allclose(mask, mask_numexpr)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### pandas.eval() for Efficient Operations\n",
    "- ``eval()`` uses string expressions to compute operations using ``DataFrame``s."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# build 4 dataframes, all 100x100K\n",
    "import pandas as pd\n",
    "nrows, ncols = 100000, 100\n",
    "rng = np.random.RandomState(42)\n",
    "df1, df2, df3, df4 = (pd.DataFrame(rng.rand(nrows, ncols))\n",
    "                      for i in range(4))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- How fast using pd.eval() provides ~50% speedup, and uses less memory."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "92.8 ms ± 958 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)\n"
     ]
    }
   ],
   "source": [
    "%timeit df1 + df2 + df3 + df4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "37.3 ms ± 699 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)\n"
     ]
    }
   ],
   "source": [
    "%timeit pd.eval('df1 + df2 + df3 + df4')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.allclose(df1 + df2 + df3 + df4, pd.eval('df1 + df2 + df3 + df4'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Operations supported by pd.eval()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>180</td>\n",
       "      <td>112</td>\n",
       "      <td>748</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>447</td>\n",
       "      <td>205</td>\n",
       "      <td>487</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>656</td>\n",
       "      <td>100</td>\n",
       "      <td>98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>90</td>\n",
       "      <td>450</td>\n",
       "      <td>613</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>529</td>\n",
       "      <td>224</td>\n",
       "      <td>530</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     0    1    2\n",
       "0  180  112  748\n",
       "1  447  205  487\n",
       "2  656  100   98\n",
       "3   90  450  613\n",
       "4  529  224  530"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1, df2, df3, df4, df5 = (pd.DataFrame(rng.randint(0, 1000, (100, 3)))\n",
    "                           for i in range(5))\n",
    "df1.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### pd.eval() supports all arithmetic operators:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result1 = -df1 * df2 / (df3 + df4) - df5\n",
    "result2 = pd.eval('-df1 * df2 / (df3 + df4) - df5')\n",
    "np.allclose(result1, result2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### ``pd.eval()`` supports all comparison operators, including chained expressions:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result1 = (df1 < df2) & (df2 <= df3) & (df3 != df4)\n",
    "result2 = pd.eval('df1 < df2 <= df3 != df4')\n",
    "np.allclose(result1, result2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### ``pd.eval()`` supports the ``&`` and ``|`` bitwise operators:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result1 = (df1 < 0.5) & (df2 < 0.5) | (df3 < df4)\n",
    "result2 = pd.eval('(df1 < 0.5) & (df2 < 0.5) | (df3 < df4)')\n",
    "np.allclose(result1, result2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### ``pd(eval()`` also supports literal ``and`` and ``or`` in Boolean expressions:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result3 = pd.eval('(df1 < 0.5) and (df2 < 0.5) or (df3 < df4)')\n",
    "np.allclose(result1, result3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### ``pd.eval()`` supports access to object attributes via ``obj.attr``, and indexes via ``obj[index]``:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result1 = df2.T[0] + df3.iloc[1]\n",
    "result2 = pd.eval('df2.T[0] + df3.iloc[1]')\n",
    "np.allclose(result1, result2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Other operations\n",
    "- function calls, conditional statements, loops, and more complex constructs are __currently *not* implemented__ in ``pd.eval()``."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### ``DataFrame.eval()`` for Column-Wise Operations\n",
    "\n",
    "- Just as Pandas uses ``pd.eval()``, ``DataFrame``s have a similar ``eval()`` method. The benefit of the ``eval()`` method is that columns can be referred to *by name*."
   ]
  },
  {
   "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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.375506</td>\n",
       "      <td>0.406939</td>\n",
       "      <td>0.069938</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.069087</td>\n",
       "      <td>0.235615</td>\n",
       "      <td>0.154374</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.677945</td>\n",
       "      <td>0.433839</td>\n",
       "      <td>0.652324</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.264038</td>\n",
       "      <td>0.808055</td>\n",
       "      <td>0.347197</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.589161</td>\n",
       "      <td>0.252418</td>\n",
       "      <td>0.557789</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          A         B         C\n",
       "0  0.375506  0.406939  0.069938\n",
       "1  0.069087  0.235615  0.154374\n",
       "2  0.677945  0.433839  0.652324\n",
       "3  0.264038  0.808055  0.347197\n",
       "4  0.589161  0.252418  0.557789"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame(rng.rand(1000, 3), columns=['A', 'B', 'C'])\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Now we can compute expressions using the column names:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result1 = (df['A'] + df['B']) / (df['C'] - 1)\n",
    "result2 = pd.eval(\"(df.A + df.B) / (df.C - 1)\")\n",
    "np.allclose(result1, result2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- ``DataFrame.eval()`` allows more succinct evaluation of expressions with the columns. We can treat *column names as variables*."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result3 = df.eval('(A + B) / (C - 1)')\n",
    "np.allclose(result1, result3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Assignment in DataFrame.eval()\n",
    "- ``DataFrame.eval()``  also allows assignment to any column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.375506</td>\n",
       "      <td>0.406939</td>\n",
       "      <td>0.069938</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.069087</td>\n",
       "      <td>0.235615</td>\n",
       "      <td>0.154374</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.677945</td>\n",
       "      <td>0.433839</td>\n",
       "      <td>0.652324</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.264038</td>\n",
       "      <td>0.808055</td>\n",
       "      <td>0.347197</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.589161</td>\n",
       "      <td>0.252418</td>\n",
       "      <td>0.557789</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          A         B         C\n",
       "0  0.375506  0.406939  0.069938\n",
       "1  0.069087  0.235615  0.154374\n",
       "2  0.677945  0.433839  0.652324\n",
       "3  0.264038  0.808055  0.347197\n",
       "4  0.589161  0.252418  0.557789"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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.375506</td>\n",
       "      <td>0.406939</td>\n",
       "      <td>0.069938</td>\n",
       "      <td>11.187620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.069087</td>\n",
       "      <td>0.235615</td>\n",
       "      <td>0.154374</td>\n",
       "      <td>1.973796</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.677945</td>\n",
       "      <td>0.433839</td>\n",
       "      <td>0.652324</td>\n",
       "      <td>1.704344</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.264038</td>\n",
       "      <td>0.808055</td>\n",
       "      <td>0.347197</td>\n",
       "      <td>3.087857</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.589161</td>\n",
       "      <td>0.252418</td>\n",
       "      <td>0.557789</td>\n",
       "      <td>1.508776</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          A         B         C          D\n",
       "0  0.375506  0.406939  0.069938  11.187620\n",
       "1  0.069087  0.235615  0.154374   1.973796\n",
       "2  0.677945  0.433839  0.652324   1.704344\n",
       "3  0.264038  0.808055  0.347197   3.087857\n",
       "4  0.589161  0.252418  0.557789   1.508776"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.eval('D = (A + B) / C', inplace=True)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Any existing column can be modified:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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.375506</td>\n",
       "      <td>0.406939</td>\n",
       "      <td>0.069938</td>\n",
       "      <td>-0.449425</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.069087</td>\n",
       "      <td>0.235615</td>\n",
       "      <td>0.154374</td>\n",
       "      <td>-1.078728</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.677945</td>\n",
       "      <td>0.433839</td>\n",
       "      <td>0.652324</td>\n",
       "      <td>0.374209</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.264038</td>\n",
       "      <td>0.808055</td>\n",
       "      <td>0.347197</td>\n",
       "      <td>-1.566886</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.589161</td>\n",
       "      <td>0.252418</td>\n",
       "      <td>0.557789</td>\n",
       "      <td>0.603708</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          A         B         C         D\n",
       "0  0.375506  0.406939  0.069938 -0.449425\n",
       "1  0.069087  0.235615  0.154374 -1.078728\n",
       "2  0.677945  0.433839  0.652324  0.374209\n",
       "3  0.264038  0.808055  0.347197 -1.566886\n",
       "4  0.589161  0.252418  0.557789  0.603708"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.eval('D = (A - B) / C', inplace=True)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Local variables in DataFrame.eval()\n",
    "- ``DataFrame.eval()`` local Python variables via the '@' syntax:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "column_mean = df.mean(1) # local variable\n",
    "\n",
    "result1 = df['A'] + column_mean\n",
    "result2 = df.eval('A + @column_mean')\n",
    "np.allclose(result1, result2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- The ``@`` character marks a *variable name* rather than a *column name*, and lets you efficiently evaluate expressions involving __two \"namespaces\"__: the namespace of columns, and the namespace of Python objects.\n",
    "\n",
    "- Note: this ``@`` character is only supported by ``DataFrame.eval()``, not by ``pandas.eval()`` - ``pandas.eval()``  only has access to one (Python) namespace."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## DataFrame.query() Method\n",
    "\n",
    "Consider the following:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result1 =          df[(df.A < 0.5) & (df.B < 0.5)]\n",
    "result2 = pd.eval('df[(df.A < 0.5) & (df.B < 0.5)]')\n",
    "np.allclose(result1, result2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- These expressions involve columns of ``DataFrame``s. But it cannot be evaluated using ``DataFrame.eval()``... instead, for this type of filtering operation, you can use the ``query()`` method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result2 = df.query('A < 0.5 and B < 0.5')\n",
    "np.allclose(result1, result2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- This is much more efficient than the masking expression above, plus it's much easier to read. Note that ``query()`` also accepts the ``@`` flag to mark local variables:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Cmean = df['C'].mean()\n",
    "result1 = df[(df.A < Cmean) & (df.B < Cmean)]\n",
    "result2 = df.query('A < @Cmean and B < @Cmean')\n",
    "np.allclose(result1, result2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Performance: When to Use These Functions\n",
    "\n",
    "- Memory use is the most predictable aspect. Every compound expression involving NumPy arrays or Pandas ``DataFrame``s will result in implicit creation of temporary arrays:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "x = df[(df.A < 0.5) & (df.B < 0.5)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Is roughly equivalent to:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tmp1 = df.A < 0.5\n",
    "tmp2 = df.B < 0.5\n",
    "tmp3 = tmp1 & tmp2\n",
    "x = df[tmp3]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- If the size of the temporary ``DataFrame``s is significant compared to available system memory (typically several GB) then consider using ``eval()`` or ``query()``. You can check the approximate size of your array in bytes using this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "32000"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.values.nbytes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- ``eval()`` can be faster even when you are not maxing-out your system memory.\n",
    "- The issue is how your temporary ``DataFrame``s compare to the size of the L1 or L2 CPU cache on your system (typically a few megabytes); if they are much bigger, then ``eval()`` can avoid some potentially slow movement of values between the different memory caches.\n",
    "- See [\"Enhancing Performance\" section](http://pandas.pydata.org/pandas-docs/dev/enhancingperf.html) for more detail."
   ]
  }
 ],
 "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
}