05.13_Panda_High_Performance_eval_and_query.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## query() 与eval()的设计动机：如何代数式"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-31T04:45:04.908426Z",
     "start_time": "2019-10-31T04:45:01.402445Z"
    }
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-31T04:46:39.458052Z",
     "start_time": "2019-10-31T04:46:36.161246Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "3.95 ms ± 90.9 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)\n"
     ]
    }
   ],
   "source": [
    "rng = np.random.RandomState(42)\n",
    "x = rng.rand(1000000)\n",
    "y = rng.rand(1000000)\n",
    "%timeit x+y"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-31T04:50:02.720169Z",
     "start_time": "2019-10-31T04:49:57.864536Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "612 ms ± 79.5 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)\n"
     ]
    }
   ],
   "source": [
    "# 复合代数运算,耗时长\n",
    "%timeit np.fromiter((xi+yi for xi, yi in zip(x, y)), dtype=x.dtype, count=len(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-31T04:52:13.318555Z",
     "start_time": "2019-10-31T04:52:13.298388Z"
    }
   },
   "outputs": [],
   "source": [
    "mask = (x> 0.5) & (y<0.5)\n",
    "mask\n",
    "\n",
    "# Numpy 会计算每个代数子式，所以上面的式子相当于\n",
    "temp1 = x > 0.5\n",
    "temp2 = y < 0.5\n",
    "mask = temp1 & temp2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "也就是说，中间过程都需要显示地分配内存。如果x、y数据非常大，那么消耗的资源是巨大的  \n",
    "而`Numexpr`程序库可以在不为中间过程分配内存的前提下，完成复合代数运算"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-31T04:54:23.381044Z",
     "start_time": "2019-10-31T04:54:23.344363Z"
    }
   },
   "outputs": [],
   "source": [
    "import numexpr"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-31T05:00:36.667185Z",
     "start_time": "2019-10-31T05:00:36.545650Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mask_numexpr = numexpr.evaluate('(x>0.5) & (y<0.5)')\n",
    "# 判断两个数组是否完全一样\n",
    "np.allclose(mask, mask_numexpr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-31T05:00:34.143472Z",
     "start_time": "2019-10-31T05:00:34.128216Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "28\n",
      "1000096\n"
     ]
    }
   ],
   "source": [
    "import sys\n",
    "print(sys.getsizeof(mask.size))\n",
    "print(sys.getsizeof(mask_numexpr))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-31T05:01:34.410855Z",
     "start_time": "2019-10-31T05:01:27.991075Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "3.67 ms ± 605 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)\n",
      "4.02 ms ± 41.1 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)\n"
     ]
    }
   ],
   "source": [
    "%timeit mask = (x> 0.5) & (y<0.5)\n",
    "%timeit mask_numexpr = numexpr.evaluate('(x>0.5) & (y<0.5)')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 用Pandas.eval()实现高性能运算"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-31T05:03:51.242088Z",
     "start_time": "2019-10-31T05:03:50.207203Z"
    }
   },
   "outputs": [],
   "source": [
    "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": "code",
   "execution_count": 36,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-31T05:04:43.791552Z",
     "start_time": "2019-10-31T05:04:32.239625Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "143 ms ± 1.37 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)\n"
     ]
    }
   ],
   "source": [
    "%timeit df1 + df2 + df3 + df4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-31T05:04:55.904188Z",
     "start_time": "2019-10-31T05:04:50.429078Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "67 ms ± 1.12 ms 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": 38,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-31T05:05:42.906975Z",
     "start_time": "2019-10-31T05:05:42.296233Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.allclose(df1+df2+df3+df4, \n",
    "           pd.eval('df1 + df2 + df3 + df4'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### pd.eval()支持的运算"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-31T05:07:03.088674Z",
     "start_time": "2019-10-31T05:07:03.055485Z"
    }
   },
   "outputs": [],
   "source": [
    "df1, df2, df3, df4, df5 = (pd.DataFrame(rng.randint(0, 100, (100,3)))\n",
    "                          for i in range(5))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 算数运算符"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-31T05:07:57.587017Z",
     "start_time": "2019-10-31T05:07:57.565158Z"
    }
   },
   "outputs": [],
   "source": [
    "result = -df1 * df2 / (df3 + df4) - df5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-31T05:08:21.417659Z",
     "start_time": "2019-10-31T05:08:21.384933Z"
    }
   },
   "outputs": [],
   "source": [
    "result2 = pd.eval('-df1 * df2 / (df3 + df4) - df5')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-31T05:08:39.602301Z",
     "start_time": "2019-10-31T05:08:39.585870Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.allclose(result, result2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 比较运算符(包括链式代数式)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-31T05:10:27.052080Z",
     "start_time": "2019-10-31T05:10:26.967027Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result1 = (df1 > df2) & (df2 <= df3) | (df3 != df4)\n",
    "result2 = pd.eval('(df1 > df2) & (df2 <= df3) | (df3 != df4)')\n",
    "np.allclose(result1, result2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 位运算符(& | 等)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-31T05:11:35.383729Z",
     "start_time": "2019-10-31T05:11:35.329464Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 46,
     "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": "code",
   "execution_count": 47,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-31T05:12:54.832150Z",
     "start_time": "2019-10-31T05:12:54.804855Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 可以在布尔类型的代数式中使用and 和 or\n",
    "result3 = pd.eval('(df1 < 0.5) and (df2 < 0.5) or (df3 < df4)')\n",
    "np.allclose(result1, result3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 对象属性与索引"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-31T05:13:53.962245Z",
     "start_time": "2019-10-31T05:13:53.393407Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 48,
     "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": {
    "ExecuteTime": {
     "end_time": "2019-10-31T05:14:51.892045Z",
     "start_time": "2019-10-31T05:14:51.834011Z"
    }
   },
   "source": [
    "#### 其他运算\n",
    "pd.eval()暂时不支持函数调用、条件语句、循环以及复杂的运算"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 用`DataFrame.eval()`实现列间运算"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-31T05:57:46.304632Z",
     "start_time": "2019-10-31T05:57:46.273712Z"
    }
   },
   "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>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.052480</td>\n",
       "      <td>0.767476</td>\n",
       "      <td>0.263192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.105160</td>\n",
       "      <td>0.987673</td>\n",
       "      <td>0.196751</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.361128</td>\n",
       "      <td>0.764004</td>\n",
       "      <td>0.368646</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.596964</td>\n",
       "      <td>0.257546</td>\n",
       "      <td>0.840027</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.375106</td>\n",
       "      <td>0.908979</td>\n",
       "      <td>0.125058</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          a         b         c\n",
       "0  0.052480  0.767476  0.263192\n",
       "1  0.105160  0.987673  0.196751\n",
       "2  0.361128  0.764004  0.368646\n",
       "3  0.596964  0.257546  0.840027\n",
       "4  0.375106  0.908979  0.125058"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame(rng.rand(1000, 3), columns=['a','b','c'])\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-31T06:00:31.743655Z",
     "start_time": "2019-10-31T06:00:31.715681Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 58,
     "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": "code",
   "execution_count": 59,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-31T06:01:30.921985Z",
     "start_time": "2019-10-31T06:01:30.901046Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 也可以用如下：\n",
    "result3 = df.eval('(a+b) / (c-1)')\n",
    "np.allclose(result1, result2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-31T06:03:05.236892Z",
     "start_time": "2019-10-31T06:03:05.222258Z"
    }
   },
   "source": [
    "\\# pd.eval(\"df.loc[:,'a'] + 1\")  \n",
    "这样使用会报错"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 用`DataFrame.eval()`新增列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-31T06:03:59.467709Z",
     "start_time": "2019-10-31T06:03:59.445747Z"
    }
   },
   "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>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.052480</td>\n",
       "      <td>0.767476</td>\n",
       "      <td>0.263192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.105160</td>\n",
       "      <td>0.987673</td>\n",
       "      <td>0.196751</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.361128</td>\n",
       "      <td>0.764004</td>\n",
       "      <td>0.368646</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.596964</td>\n",
       "      <td>0.257546</td>\n",
       "      <td>0.840027</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.375106</td>\n",
       "      <td>0.908979</td>\n",
       "      <td>0.125058</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          a         b         c\n",
       "0  0.052480  0.767476  0.263192\n",
       "1  0.105160  0.987673  0.196751\n",
       "2  0.361128  0.764004  0.368646\n",
       "3  0.596964  0.257546  0.840027\n",
       "4  0.375106  0.908979  0.125058"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-31T06:04:39.017438Z",
     "start_time": "2019-10-31T06:04:38.987591Z"
    }
   },
   "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>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.052480</td>\n",
       "      <td>0.767476</td>\n",
       "      <td>0.263192</td>\n",
       "      <td>3.115430</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.105160</td>\n",
       "      <td>0.987673</td>\n",
       "      <td>0.196751</td>\n",
       "      <td>5.554388</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.361128</td>\n",
       "      <td>0.764004</td>\n",
       "      <td>0.368646</td>\n",
       "      <td>3.052065</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.596964</td>\n",
       "      <td>0.257546</td>\n",
       "      <td>0.840027</td>\n",
       "      <td>1.017242</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.375106</td>\n",
       "      <td>0.908979</td>\n",
       "      <td>0.125058</td>\n",
       "      <td>10.267946</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          a         b         c          d\n",
       "0  0.052480  0.767476  0.263192   3.115430\n",
       "1  0.105160  0.987673  0.196751   5.554388\n",
       "2  0.361128  0.764004  0.368646   3.052065\n",
       "3  0.596964  0.257546  0.840027   1.017242\n",
       "4  0.375106  0.908979  0.125058  10.267946"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.eval('d = (a+b) / (c)', inplace=True)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-31T06:05:24.436673Z",
     "start_time": "2019-10-31T06:05:24.396543Z"
    }
   },
   "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>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.052480</td>\n",
       "      <td>0.767476</td>\n",
       "      <td>3.167909</td>\n",
       "      <td>3.115430</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.105160</td>\n",
       "      <td>0.987673</td>\n",
       "      <td>5.659549</td>\n",
       "      <td>5.554388</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.361128</td>\n",
       "      <td>0.764004</td>\n",
       "      <td>3.413193</td>\n",
       "      <td>3.052065</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.596964</td>\n",
       "      <td>0.257546</td>\n",
       "      <td>1.614206</td>\n",
       "      <td>1.017242</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.375106</td>\n",
       "      <td>0.908979</td>\n",
       "      <td>10.643052</td>\n",
       "      <td>10.267946</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          a         b          c          d\n",
       "0  0.052480  0.767476   3.167909   3.115430\n",
       "1  0.105160  0.987673   5.659549   5.554388\n",
       "2  0.361128  0.764004   3.413193   3.052065\n",
       "3  0.596964  0.257546   1.614206   1.017242\n",
       "4  0.375106  0.908979  10.643052  10.267946"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 修改已有的列\n",
    "df.eval('c = (a+d)', inplace=True)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### `DataFrame.eval()`使用局部变量 \n",
    "通过`@var`符号使用python局部变量  \n",
    "`@`只能在`DataFrame.eval()`中使用，不能再`pandas.eval()`使用"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-31T06:08:07.300744Z",
     "start_time": "2019-10-31T06:08:07.270453Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "column_mean = df.mean(1)\n",
    "\n",
    "result1 = df['a'] + column_mean\n",
    "result2 = df.eval('a + @column_mean')\n",
    "np.allclose(result1, result2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-31T06:08:47.828389Z",
     "start_time": "2019-10-31T06:08:47.804045Z"
    }
   },
   "source": [
    "## `DataFrame.query()`方法"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-31T06:12:24.478236Z",
     "start_time": "2019-10-31T06:12:24.398585Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 79,
     "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": {
    "ExecuteTime": {
     "end_time": "2019-10-31T06:12:55.112122Z",
     "start_time": "2019-10-31T06:12:55.033926Z"
    }
   },
   "source": [
    "\\# `df.eval('df[a < 0.5) & (b <0.5)]'`  \n",
    "再df无法使用\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-31T06:15:49.205097Z",
     "start_time": "2019-10-31T06:15:49.182829Z"
    }
   },
   "outputs": [],
   "source": [
    "# result3 = df.eval('a<0.5 & b<0.5')\n",
    "# np.allclose(result1, result3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-31T06:16:20.472217Z",
     "start_time": "2019-10-31T06:16:20.452812Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result3 = df.query('a<0.5 & b<0.5')\n",
    "np.allclose(result1, result3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`query()`也支持使用`@`符号引用局部变量\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-31T06:21:32.582713Z",
     "start_time": "2019-10-31T06:21:32.545554Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns_mean = df.mean(axis=1)\n",
    "result1 = df[(df.a > column_mean-1) & (df.c < column_mean+1)]\n",
    "result2 = df.query('(a>@column_mean-1) & (c<@column_mean+1)')\n",
    "np.allclose(result1, result2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 性能决定使用时机"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-31T06:22:32.682890Z",
     "start_time": "2019-10-31T06:22:32.668066Z"
    }
   },
   "outputs": [],
   "source": [
    "x = df[(df.a < 0.5) & (df.b <0.5)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "等价于"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-10-31T06:23:14.164723Z",
     "start_time": "2019-10-31T06:23:14.146568Z"
    }
   },
   "outputs": [],
   "source": [
    "tmp1 = df.a < 0.5\n",
    "tmp2 = df.b < 0.5\n",
    "tmp3 = tmp1 & tmp2\n",
    "x = df[tmp3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.7.3"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}