49. Pandas查询数据的简便方法df.query.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pandas查询数据的简便方法df.query\n",
    "\n",
    "怎样进行复杂组合条件对数据查询：\n",
    "* 方式1. 使用df[(df[\"a\"] > 3) & (df[\"b\"]<5)]的方式；\n",
    "* 方式2. 使用df.query(\"a>3 & b<5\")的方式；\n",
    "\n",
    "方法2的语法更加简洁\n",
    "\n",
    "性能对比：\n",
    "* 当数据量小时，方法1更快；\n",
    "* 当数据量大时，因为方法2直接用C语言实现，节省方法1临时数组的多次复制，方法2更快；"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1.0.1\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "print(pd.__version__)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 0、读取数据\n",
    "数据为北京2018年全年天气预报  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"./datas/beijing_tianqi/beijing_tianqi_2018.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>ymd</th>\n",
       "      <th>bWendu</th>\n",
       "      <th>yWendu</th>\n",
       "      <th>tianqi</th>\n",
       "      <th>fengxiang</th>\n",
       "      <th>fengli</th>\n",
       "      <th>aqi</th>\n",
       "      <th>aqiInfo</th>\n",
       "      <th>aqiLevel</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>3℃</td>\n",
       "      <td>-6℃</td>\n",
       "      <td>晴~多云</td>\n",
       "      <td>东北风</td>\n",
       "      <td>1-2级</td>\n",
       "      <td>59</td>\n",
       "      <td>良</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-01-02</td>\n",
       "      <td>2℃</td>\n",
       "      <td>-5℃</td>\n",
       "      <td>阴~多云</td>\n",
       "      <td>东北风</td>\n",
       "      <td>1-2级</td>\n",
       "      <td>49</td>\n",
       "      <td>优</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-01-03</td>\n",
       "      <td>2℃</td>\n",
       "      <td>-5℃</td>\n",
       "      <td>多云</td>\n",
       "      <td>北风</td>\n",
       "      <td>1-2级</td>\n",
       "      <td>28</td>\n",
       "      <td>优</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-01-04</td>\n",
       "      <td>0℃</td>\n",
       "      <td>-8℃</td>\n",
       "      <td>阴</td>\n",
       "      <td>东北风</td>\n",
       "      <td>1-2级</td>\n",
       "      <td>28</td>\n",
       "      <td>优</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018-01-05</td>\n",
       "      <td>3℃</td>\n",
       "      <td>-6℃</td>\n",
       "      <td>多云~晴</td>\n",
       "      <td>西北风</td>\n",
       "      <td>1-2级</td>\n",
       "      <td>50</td>\n",
       "      <td>优</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          ymd bWendu yWendu tianqi fengxiang fengli  aqi aqiInfo  aqiLevel\n",
       "0  2018-01-01     3℃    -6℃   晴~多云       东北风   1-2级   59       良         2\n",
       "1  2018-01-02     2℃    -5℃   阴~多云       东北风   1-2级   49       优         1\n",
       "2  2018-01-03     2℃    -5℃     多云        北风   1-2级   28       优         1\n",
       "3  2018-01-04     0℃    -8℃      阴       东北风   1-2级   28       优         1\n",
       "4  2018-01-05     3℃    -6℃   多云~晴       西北风   1-2级   50       优         1"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 替换掉温度的后缀℃\n",
    "df.loc[:, \"bWendu\"] = df[\"bWendu\"].str.replace(\"℃\", \"\").astype('int32')\n",
    "df.loc[:, \"yWendu\"] = df[\"yWendu\"].str.replace(\"℃\", \"\").astype('int32')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1、使用dataframe条件表达式查询"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 最低温度低于-10度的列表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>ymd</th>\n",
       "      <th>bWendu</th>\n",
       "      <th>yWendu</th>\n",
       "      <th>tianqi</th>\n",
       "      <th>fengxiang</th>\n",
       "      <th>fengli</th>\n",
       "      <th>aqi</th>\n",
       "      <th>aqiInfo</th>\n",
       "      <th>aqiLevel</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2018-01-23</td>\n",
       "      <td>-4</td>\n",
       "      <td>-12</td>\n",
       "      <td>晴</td>\n",
       "      <td>西北风</td>\n",
       "      <td>3-4级</td>\n",
       "      <td>31</td>\n",
       "      <td>优</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>2018-01-24</td>\n",
       "      <td>-4</td>\n",
       "      <td>-11</td>\n",
       "      <td>晴</td>\n",
       "      <td>西南风</td>\n",
       "      <td>1-2级</td>\n",
       "      <td>34</td>\n",
       "      <td>优</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>2018-01-25</td>\n",
       "      <td>-3</td>\n",
       "      <td>-11</td>\n",
       "      <td>多云</td>\n",
       "      <td>东北风</td>\n",
       "      <td>1-2级</td>\n",
       "      <td>27</td>\n",
       "      <td>优</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>359</th>\n",
       "      <td>2018-12-26</td>\n",
       "      <td>-2</td>\n",
       "      <td>-11</td>\n",
       "      <td>晴~多云</td>\n",
       "      <td>东北风</td>\n",
       "      <td>2级</td>\n",
       "      <td>26</td>\n",
       "      <td>优</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>360</th>\n",
       "      <td>2018-12-27</td>\n",
       "      <td>-5</td>\n",
       "      <td>-12</td>\n",
       "      <td>多云~晴</td>\n",
       "      <td>西北风</td>\n",
       "      <td>3级</td>\n",
       "      <td>48</td>\n",
       "      <td>优</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            ymd  bWendu  yWendu tianqi fengxiang fengli  aqi aqiInfo  aqiLevel\n",
       "22   2018-01-23      -4     -12      晴       西北风   3-4级   31       优         1\n",
       "23   2018-01-24      -4     -11      晴       西南风   1-2级   34       优         1\n",
       "24   2018-01-25      -3     -11     多云       东北风   1-2级   27       优         1\n",
       "359  2018-12-26      -2     -11   晴~多云       东北风     2级   26       优         1\n",
       "360  2018-12-27      -5     -12   多云~晴       西北风     3级   48       优         1"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df[\"yWendu\"] < -10].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 复杂条件查询\n",
    "注意，组合条件用&符号合并，每个条件判断都得带括号"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>ymd</th>\n",
       "      <th>bWendu</th>\n",
       "      <th>yWendu</th>\n",
       "      <th>tianqi</th>\n",
       "      <th>fengxiang</th>\n",
       "      <th>fengli</th>\n",
       "      <th>aqi</th>\n",
       "      <th>aqiInfo</th>\n",
       "      <th>aqiLevel</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>235</th>\n",
       "      <td>2018-08-24</td>\n",
       "      <td>30</td>\n",
       "      <td>20</td>\n",
       "      <td>晴</td>\n",
       "      <td>北风</td>\n",
       "      <td>1-2级</td>\n",
       "      <td>40</td>\n",
       "      <td>优</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>249</th>\n",
       "      <td>2018-09-07</td>\n",
       "      <td>27</td>\n",
       "      <td>16</td>\n",
       "      <td>晴</td>\n",
       "      <td>西北风</td>\n",
       "      <td>3-4级</td>\n",
       "      <td>22</td>\n",
       "      <td>优</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            ymd  bWendu  yWendu tianqi fengxiang fengli  aqi aqiInfo  aqiLevel\n",
       "235  2018-08-24      30      20      晴        北风   1-2级   40       优         1\n",
       "249  2018-09-07      27      16      晴       西北风   3-4级   22       优         1"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## 查询最高温度小于30度，并且最低温度大于15度，并且是晴天，并且天气为优的数据\n",
    "df[\n",
    "    (df[\"bWendu\"]<=30) \n",
    "    & (df[\"yWendu\"]>=15) \n",
    "    & (df[\"tianqi\"]=='晴') \n",
    "    & (df[\"aqiLevel\"]==1)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2、使用df.query可以简化查询\n",
    "\n",
    "形式：DataFrame.query(expr, inplace=False, **kwargs)\n",
    "\n",
    "其中expr为要返回boolean结果的字符串表达式\n",
    "\n",
    "形如：\n",
    "* df.query('a<100')\n",
    "* df.query('a < b & b < c')，或者df.query('(a<b)&(b<c)')\n",
    "\n",
    "df.query可支持的表达式语法：\n",
    "* 逻辑操作符: &, |, ~\n",
    "* 比较操作符: <, <=, ==, !=, >=, >\n",
    "* 单变量操作符: -\n",
    "* 多变量操作符: +, -, *, /, %\n",
    "\n",
    "df.query中可以使用@var的方式传入外部变量\n",
    "\n",
    "df.query支持的语法来自NumExpr，地址：   \n",
    "https://numexpr.readthedocs.io/projects/NumExpr3/en/latest/index.html\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 查询最低温度低于-10度的列表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>ymd</th>\n",
       "      <th>bWendu</th>\n",
       "      <th>yWendu</th>\n",
       "      <th>tianqi</th>\n",
       "      <th>fengxiang</th>\n",
       "      <th>fengli</th>\n",
       "      <th>aqi</th>\n",
       "      <th>aqiInfo</th>\n",
       "      <th>aqiLevel</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>3</td>\n",
       "      <td>-6</td>\n",
       "      <td>晴~多云</td>\n",
       "      <td>东北风</td>\n",
       "      <td>1-2级</td>\n",
       "      <td>59</td>\n",
       "      <td>良</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-01-02</td>\n",
       "      <td>2</td>\n",
       "      <td>-5</td>\n",
       "      <td>阴~多云</td>\n",
       "      <td>东北风</td>\n",
       "      <td>1-2级</td>\n",
       "      <td>49</td>\n",
       "      <td>优</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-01-03</td>\n",
       "      <td>2</td>\n",
       "      <td>-5</td>\n",
       "      <td>多云</td>\n",
       "      <td>北风</td>\n",
       "      <td>1-2级</td>\n",
       "      <td>28</td>\n",
       "      <td>优</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          ymd  bWendu  yWendu tianqi fengxiang fengli  aqi aqiInfo  aqiLevel\n",
       "0  2018-01-01       3      -6   晴~多云       东北风   1-2级   59       良         2\n",
       "1  2018-01-02       2      -5   阴~多云       东北风   1-2级   49       优         1\n",
       "2  2018-01-03       2      -5     多云        北风   1-2级   28       优         1"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.query(\"yWendu < 3\").head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 查询最高温度小于30度，并且最低温度大于15度，并且是晴天，并且天气为优的数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>ymd</th>\n",
       "      <th>bWendu</th>\n",
       "      <th>yWendu</th>\n",
       "      <th>tianqi</th>\n",
       "      <th>fengxiang</th>\n",
       "      <th>fengli</th>\n",
       "      <th>aqi</th>\n",
       "      <th>aqiInfo</th>\n",
       "      <th>aqiLevel</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>235</th>\n",
       "      <td>2018-08-24</td>\n",
       "      <td>30</td>\n",
       "      <td>20</td>\n",
       "      <td>晴</td>\n",
       "      <td>北风</td>\n",
       "      <td>1-2级</td>\n",
       "      <td>40</td>\n",
       "      <td>优</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>249</th>\n",
       "      <td>2018-09-07</td>\n",
       "      <td>27</td>\n",
       "      <td>16</td>\n",
       "      <td>晴</td>\n",
       "      <td>西北风</td>\n",
       "      <td>3-4级</td>\n",
       "      <td>22</td>\n",
       "      <td>优</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            ymd  bWendu  yWendu tianqi fengxiang fengli  aqi aqiInfo  aqiLevel\n",
       "235  2018-08-24      30      20      晴        北风   1-2级   40       优         1\n",
       "249  2018-09-07      27      16      晴       西北风   3-4级   22       优         1"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## 查询最高温度小于30度，并且最低温度大于15度，并且是晴天，并且天气为优的数据\n",
    "df.query(\"bWendu<=30 & yWendu>=15 & tianqi=='晴' & aqiLevel==1\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 查询温差大于15度的日子"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>ymd</th>\n",
       "      <th>bWendu</th>\n",
       "      <th>yWendu</th>\n",
       "      <th>tianqi</th>\n",
       "      <th>fengxiang</th>\n",
       "      <th>fengli</th>\n",
       "      <th>aqi</th>\n",
       "      <th>aqiInfo</th>\n",
       "      <th>aqiLevel</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>2018-03-10</td>\n",
       "      <td>14</td>\n",
       "      <td>-2</td>\n",
       "      <td>晴</td>\n",
       "      <td>东南风</td>\n",
       "      <td>1-2级</td>\n",
       "      <td>171</td>\n",
       "      <td>中度污染</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>2018-03-24</td>\n",
       "      <td>22</td>\n",
       "      <td>5</td>\n",
       "      <td>晴</td>\n",
       "      <td>西南风</td>\n",
       "      <td>1-2级</td>\n",
       "      <td>119</td>\n",
       "      <td>轻度污染</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>2018-03-25</td>\n",
       "      <td>24</td>\n",
       "      <td>7</td>\n",
       "      <td>晴</td>\n",
       "      <td>南风</td>\n",
       "      <td>1-2级</td>\n",
       "      <td>78</td>\n",
       "      <td>良</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84</th>\n",
       "      <td>2018-03-26</td>\n",
       "      <td>25</td>\n",
       "      <td>7</td>\n",
       "      <td>多云</td>\n",
       "      <td>西南风</td>\n",
       "      <td>1-2级</td>\n",
       "      <td>151</td>\n",
       "      <td>中度污染</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>2018-03-27</td>\n",
       "      <td>27</td>\n",
       "      <td>11</td>\n",
       "      <td>晴</td>\n",
       "      <td>南风</td>\n",
       "      <td>1-2级</td>\n",
       "      <td>243</td>\n",
       "      <td>重度污染</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           ymd  bWendu  yWendu tianqi fengxiang fengli  aqi aqiInfo  aqiLevel\n",
       "68  2018-03-10      14      -2      晴       东南风   1-2级  171    中度污染         4\n",
       "82  2018-03-24      22       5      晴       西南风   1-2级  119    轻度污染         3\n",
       "83  2018-03-25      24       7      晴        南风   1-2级   78       良         2\n",
       "84  2018-03-26      25       7     多云       西南风   1-2级  151    中度污染         4\n",
       "85  2018-03-27      27      11      晴        南风   1-2级  243    重度污染         5"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.query(\"bWendu-yWendu >= 15\").head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 可以使用外部的变量"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 查询温度在这两个温度之间的数据\n",
    "high_temperature = 15\n",
    "low_temperature = 13"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>ymd</th>\n",
       "      <th>bWendu</th>\n",
       "      <th>yWendu</th>\n",
       "      <th>tianqi</th>\n",
       "      <th>fengxiang</th>\n",
       "      <th>fengli</th>\n",
       "      <th>aqi</th>\n",
       "      <th>aqiInfo</th>\n",
       "      <th>aqiLevel</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>107</th>\n",
       "      <td>2018-04-18</td>\n",
       "      <td>27</td>\n",
       "      <td>14</td>\n",
       "      <td>多云~晴</td>\n",
       "      <td>西南风</td>\n",
       "      <td>3-4级</td>\n",
       "      <td>147</td>\n",
       "      <td>轻度污染</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108</th>\n",
       "      <td>2018-04-19</td>\n",
       "      <td>26</td>\n",
       "      <td>13</td>\n",
       "      <td>多云</td>\n",
       "      <td>东南风</td>\n",
       "      <td>4-5级</td>\n",
       "      <td>170</td>\n",
       "      <td>中度污染</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>109</th>\n",
       "      <td>2018-04-20</td>\n",
       "      <td>28</td>\n",
       "      <td>14</td>\n",
       "      <td>多云~小雨</td>\n",
       "      <td>南风</td>\n",
       "      <td>4-5级</td>\n",
       "      <td>164</td>\n",
       "      <td>中度污染</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116</th>\n",
       "      <td>2018-04-27</td>\n",
       "      <td>25</td>\n",
       "      <td>13</td>\n",
       "      <td>晴</td>\n",
       "      <td>西南风</td>\n",
       "      <td>3-4级</td>\n",
       "      <td>112</td>\n",
       "      <td>轻度污染</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>2018-04-30</td>\n",
       "      <td>24</td>\n",
       "      <td>14</td>\n",
       "      <td>多云</td>\n",
       "      <td>南风</td>\n",
       "      <td>3-4级</td>\n",
       "      <td>62</td>\n",
       "      <td>良</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            ymd  bWendu  yWendu tianqi fengxiang fengli  aqi aqiInfo  aqiLevel\n",
       "107  2018-04-18      27      14   多云~晴       西南风   3-4级  147    轻度污染         3\n",
       "108  2018-04-19      26      13     多云       东南风   4-5级  170    中度污染         4\n",
       "109  2018-04-20      28      14  多云~小雨        南风   4-5级  164    中度污染         4\n",
       "116  2018-04-27      25      13      晴       西南风   3-4级  112    轻度污染         3\n",
       "119  2018-04-30      24      14     多云        南风   3-4级   62       良         2"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.query(\"yWendu<=@high_temperature & yWendu>=@low_temperature\").head()"
   ]
  },
  {
   "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}