Side-projects/Dataframe-indexing-selecting.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### The axis labeling information in pandas objects serves many purposes:\n",
    "\n",
    "- Identifies data (i.e. provides metadata) using known indicators, important for analysis, visualization, and interactive console display.\n",
    "- Enables automatic and explicit data alignment.\n",
    "- Allows intuitive getting and setting of subsets of the data set.\n",
    "\n",
    "In this section, we will focus on the final point: namely, how to slice, dice, and generally get and set subsets of pandas objects. The primary focus will be on Series and DataFrame as they have received more development attention in this area."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Note The Python and NumPy indexing operators [] and attribute operator . provide quick and easy access to pandas data structures across a wide range of use cases. This makes interactive work intuitive, as there’s little new to learn if you already know how to deal with Python dictionaries and NumPy arrays. However, since the type of the data to be accessed isn’t known in advance, directly using standard operators has some optimization limits. For production code, we recommended that you take advantage of the optimized pandas data access methods exposed in this chapter."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Basics"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The following table shows return type values when indexing pandas objects with []:\n",
    "\n",
    "| Object Type | Selection      | Return Value Type               |\n",
    "|-------------|----------------|---------------------------------|\n",
    "| Series      | series[label]  | scalar value                    |\n",
    "| DataFrame   | frame[colname] | Series corresponding to colname |"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2000-01-01</td>\n",
       "      <td>0.971047</td>\n",
       "      <td>0.542045</td>\n",
       "      <td>-0.495519</td>\n",
       "      <td>0.137423</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-02</td>\n",
       "      <td>0.126074</td>\n",
       "      <td>-0.886903</td>\n",
       "      <td>0.279908</td>\n",
       "      <td>-2.781176</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-03</td>\n",
       "      <td>1.287311</td>\n",
       "      <td>-1.199292</td>\n",
       "      <td>1.569447</td>\n",
       "      <td>-0.880195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-04</td>\n",
       "      <td>-0.784285</td>\n",
       "      <td>-0.024566</td>\n",
       "      <td>0.388444</td>\n",
       "      <td>-0.683443</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-05</td>\n",
       "      <td>-0.776495</td>\n",
       "      <td>-0.320646</td>\n",
       "      <td>-1.371365</td>\n",
       "      <td>-0.302604</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   A         B         C         D\n",
       "2000-01-01  0.971047  0.542045 -0.495519  0.137423\n",
       "2000-01-02  0.126074 -0.886903  0.279908 -2.781176\n",
       "2000-01-03  1.287311 -1.199292  1.569447 -0.880195\n",
       "2000-01-04 -0.784285 -0.024566  0.388444 -0.683443\n",
       "2000-01-05 -0.776495 -0.320646 -1.371365 -0.302604"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dates = pd.date_range('1/1/2000', periods=8)\n",
    "\n",
    "df = pd.DataFrame(np.random.randn(8, 4), index=dates, columns=['A', 'B', 'C', 'D'])\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Thus, as per above, we have the most basic indexing using []:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "-0.3885082169730956"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = df['A']\n",
    "s[dates[5]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It is very useful to apply a transform (in-place) to a subset of the columns (cannot be done similarly with loc and iloc):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2000-01-01</td>\n",
       "      <td>0.971047</td>\n",
       "      <td>0.542045</td>\n",
       "      <td>-0.495519</td>\n",
       "      <td>0.137423</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-02</td>\n",
       "      <td>0.126074</td>\n",
       "      <td>-0.886903</td>\n",
       "      <td>0.279908</td>\n",
       "      <td>-2.781176</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-03</td>\n",
       "      <td>1.287311</td>\n",
       "      <td>-1.199292</td>\n",
       "      <td>1.569447</td>\n",
       "      <td>-0.880195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-04</td>\n",
       "      <td>-0.784285</td>\n",
       "      <td>-0.024566</td>\n",
       "      <td>0.388444</td>\n",
       "      <td>-0.683443</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-05</td>\n",
       "      <td>-0.776495</td>\n",
       "      <td>-0.320646</td>\n",
       "      <td>-1.371365</td>\n",
       "      <td>-0.302604</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   A         B         C         D\n",
       "2000-01-01  0.971047  0.542045 -0.495519  0.137423\n",
       "2000-01-02  0.126074 -0.886903  0.279908 -2.781176\n",
       "2000-01-03  1.287311 -1.199292  1.569447 -0.880195\n",
       "2000-01-04 -0.784285 -0.024566  0.388444 -0.683443\n",
       "2000-01-05 -0.776495 -0.320646 -1.371365 -0.302604"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "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>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2000-01-01</td>\n",
       "      <td>0.542045</td>\n",
       "      <td>0.971047</td>\n",
       "      <td>-0.495519</td>\n",
       "      <td>0.137423</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-02</td>\n",
       "      <td>-0.886903</td>\n",
       "      <td>0.126074</td>\n",
       "      <td>0.279908</td>\n",
       "      <td>-2.781176</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-03</td>\n",
       "      <td>-1.199292</td>\n",
       "      <td>1.287311</td>\n",
       "      <td>1.569447</td>\n",
       "      <td>-0.880195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-04</td>\n",
       "      <td>-0.024566</td>\n",
       "      <td>-0.784285</td>\n",
       "      <td>0.388444</td>\n",
       "      <td>-0.683443</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-05</td>\n",
       "      <td>-0.320646</td>\n",
       "      <td>-0.776495</td>\n",
       "      <td>-1.371365</td>\n",
       "      <td>-0.302604</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   A         B         C         D\n",
       "2000-01-01  0.542045  0.971047 -0.495519  0.137423\n",
       "2000-01-02 -0.886903  0.126074  0.279908 -2.781176\n",
       "2000-01-03 -1.199292  1.287311  1.569447 -0.880195\n",
       "2000-01-04 -0.024566 -0.784285  0.388444 -0.683443\n",
       "2000-01-05 -0.320646 -0.776495 -1.371365 -0.302604"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[['B', 'A']] = df[['A', 'B']]\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Attribute access"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You may access an index on a Series or column on a DataFrame directly as an attribute:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2000-01-01    0.542045\n",
       "2000-01-02   -0.886903\n",
       "2000-01-03   -1.199292\n",
       "2000-01-04   -0.024566\n",
       "2000-01-05   -0.320646\n",
       "2000-01-06   -0.665214\n",
       "2000-01-07    0.345925\n",
       "2000-01-08   -0.286089\n",
       "Freq: D, Name: A, dtype: float64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.A"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sa = pd.Series([1, 2, 3], index=list('abc'))\n",
    "sa.b"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Slicing"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With DataFrame, slicing inside of [] slices the rows. This is provided largely as a convenience since it is such a common operation."
   ]
  },
  {
   "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>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2000-01-08</td>\n",
       "      <td>-0.286089</td>\n",
       "      <td>0.294147</td>\n",
       "      <td>0.467222</td>\n",
       "      <td>-0.796261</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-07</td>\n",
       "      <td>0.345925</td>\n",
       "      <td>-0.122817</td>\n",
       "      <td>0.797087</td>\n",
       "      <td>-3.073061</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-06</td>\n",
       "      <td>-0.665214</td>\n",
       "      <td>-0.388508</td>\n",
       "      <td>-0.034068</td>\n",
       "      <td>1.827295</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-05</td>\n",
       "      <td>-0.320646</td>\n",
       "      <td>-0.776495</td>\n",
       "      <td>-1.371365</td>\n",
       "      <td>-0.302604</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-04</td>\n",
       "      <td>-0.024566</td>\n",
       "      <td>-0.784285</td>\n",
       "      <td>0.388444</td>\n",
       "      <td>-0.683443</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-03</td>\n",
       "      <td>-1.199292</td>\n",
       "      <td>1.287311</td>\n",
       "      <td>1.569447</td>\n",
       "      <td>-0.880195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-02</td>\n",
       "      <td>-0.886903</td>\n",
       "      <td>0.126074</td>\n",
       "      <td>0.279908</td>\n",
       "      <td>-2.781176</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-01</td>\n",
       "      <td>0.542045</td>\n",
       "      <td>0.971047</td>\n",
       "      <td>-0.495519</td>\n",
       "      <td>0.137423</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   A         B         C         D\n",
       "2000-01-08 -0.286089  0.294147  0.467222 -0.796261\n",
       "2000-01-07  0.345925 -0.122817  0.797087 -3.073061\n",
       "2000-01-06 -0.665214 -0.388508 -0.034068  1.827295\n",
       "2000-01-05 -0.320646 -0.776495 -1.371365 -0.302604\n",
       "2000-01-04 -0.024566 -0.784285  0.388444 -0.683443\n",
       "2000-01-03 -1.199292  1.287311  1.569447 -0.880195\n",
       "2000-01-02 -0.886903  0.126074  0.279908 -2.781176\n",
       "2000-01-01  0.542045  0.971047 -0.495519  0.137423"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[::-1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Selection by label"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "pandas provides a suite of methods in order to have purely label based indexing. This is a strict inclusion based protocol. Every label asked for must be in the index, or a KeyError will be raised. When slicing, both the start bound AND the stop bound are included, if present in the index. Integers are valid labels, but they refer to the label and not the position."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The .loc attribute is the primary access method. The following are valid inputs:\n",
    "\n",
    "- A single label, e.g. 5 or 'a' (Note that 5 is interpreted as a label of the index. This use is not an integer position along the index.).\n",
    "\n",
    "- A list or array of labels ['a', 'b', 'c'].\n",
    "- A slice object with labels 'a':'f' (Note that contrary to usual python slices, both the start and the stop are included, when present in the index! See Slicing with labels.\n",
    "- A boolean array.\n",
    "- A callable, see Selection By Callable."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "c    0.935358\n",
       "d   -0.443303\n",
       "e    2.078960\n",
       "f    1.075126\n",
       "dtype: float64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s1 = pd.Series(np.random.randn(6), index=list('abcdef'))\n",
    "\n",
    "s1.loc['c':]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.17712964058191943"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s1.loc['b']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that setting works as well:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a   -2.669998\n",
       "b    0.177130\n",
       "c    0.000000\n",
       "d    0.000000\n",
       "e    0.000000\n",
       "f    0.000000\n",
       "dtype: float64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s1.loc['c':] = 0\n",
    "s1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With a DataFrame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>a</td>\n",
       "      <td>0.353738</td>\n",
       "      <td>0.127227</td>\n",
       "      <td>-0.661476</td>\n",
       "      <td>0.717148</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>b</td>\n",
       "      <td>-0.160520</td>\n",
       "      <td>-0.322845</td>\n",
       "      <td>1.517146</td>\n",
       "      <td>-0.148533</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>c</td>\n",
       "      <td>2.558492</td>\n",
       "      <td>1.966952</td>\n",
       "      <td>0.464750</td>\n",
       "      <td>0.583828</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>d</td>\n",
       "      <td>1.738518</td>\n",
       "      <td>-0.757292</td>\n",
       "      <td>-0.859018</td>\n",
       "      <td>1.276810</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>e</td>\n",
       "      <td>-0.128382</td>\n",
       "      <td>0.959006</td>\n",
       "      <td>-2.361875</td>\n",
       "      <td>-0.756041</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          A         B         C         D\n",
       "a  0.353738  0.127227 -0.661476  0.717148\n",
       "b -0.160520 -0.322845  1.517146 -0.148533\n",
       "c  2.558492  1.966952  0.464750  0.583828\n",
       "d  1.738518 -0.757292 -0.859018  1.276810\n",
       "e -0.128382  0.959006 -2.361875 -0.756041"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.DataFrame(np.random.randn(6, 4), \n",
    "                   index=list('abcdef'),\n",
    "                   columns=list('ABCD'))\n",
    "df1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>a</td>\n",
       "      <td>0.353738</td>\n",
       "      <td>0.127227</td>\n",
       "      <td>-0.661476</td>\n",
       "      <td>0.717148</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>b</td>\n",
       "      <td>-0.160520</td>\n",
       "      <td>-0.322845</td>\n",
       "      <td>1.517146</td>\n",
       "      <td>-0.148533</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>d</td>\n",
       "      <td>1.738518</td>\n",
       "      <td>-0.757292</td>\n",
       "      <td>-0.859018</td>\n",
       "      <td>1.276810</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          A         B         C         D\n",
       "a  0.353738  0.127227 -0.661476  0.717148\n",
       "b -0.160520 -0.322845  1.517146 -0.148533\n",
       "d  1.738518 -0.757292 -0.859018  1.276810"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.loc[['a', 'b', 'd'], :]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Accessing via label slices:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>d</td>\n",
       "      <td>1.738518</td>\n",
       "      <td>-0.757292</td>\n",
       "      <td>-0.859018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>e</td>\n",
       "      <td>-0.128382</td>\n",
       "      <td>0.959006</td>\n",
       "      <td>-2.361875</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>f</td>\n",
       "      <td>1.506578</td>\n",
       "      <td>1.152356</td>\n",
       "      <td>1.355340</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          A         B         C\n",
       "d  1.738518 -0.757292 -0.859018\n",
       "e -0.128382  0.959006 -2.361875\n",
       "f  1.506578  1.152356  1.355340"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.loc['d':, 'A':'C']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For getting a cross section using a label (equivalent to df.xs('a')):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "A    0.353738\n",
       "B    0.127227\n",
       "C   -0.661476\n",
       "D    0.717148\n",
       "Name: a, dtype: float64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.loc['a']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For getting values with a boolean array:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "A     True\n",
       "B     True\n",
       "C    False\n",
       "D     True\n",
       "Name: a, dtype: bool"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.loc['a'] > 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>A</th>\n",
       "      <th>B</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>a</td>\n",
       "      <td>0.353738</td>\n",
       "      <td>0.127227</td>\n",
       "      <td>0.717148</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>b</td>\n",
       "      <td>-0.160520</td>\n",
       "      <td>-0.322845</td>\n",
       "      <td>-0.148533</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>c</td>\n",
       "      <td>2.558492</td>\n",
       "      <td>1.966952</td>\n",
       "      <td>0.583828</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>d</td>\n",
       "      <td>1.738518</td>\n",
       "      <td>-0.757292</td>\n",
       "      <td>1.276810</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>e</td>\n",
       "      <td>-0.128382</td>\n",
       "      <td>0.959006</td>\n",
       "      <td>-0.756041</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>f</td>\n",
       "      <td>1.506578</td>\n",
       "      <td>1.152356</td>\n",
       "      <td>1.140441</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          A         B         D\n",
       "a  0.353738  0.127227  0.717148\n",
       "b -0.160520 -0.322845 -0.148533\n",
       "c  2.558492  1.966952  0.583828\n",
       "d  1.738518 -0.757292  1.276810\n",
       "e -0.128382  0.959006 -0.756041\n",
       "f  1.506578  1.152356  1.140441"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.loc[:, df1.loc['a'] > 0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For getting a value explicitly"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.3537380098535124"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.loc['a', 'A']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Slicing with labels"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3    b\n",
       "2    c\n",
       "5    d\n",
       "dtype: object"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = pd.Series(list('abcde'), index=[0, 3, 2, 5, 4])\n",
    "s.loc[3:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If at least one of the two is absent, but the index is sorted, and can be compared against start and stop labels, then slicing will still work as expected, by selecting labels which rank between the two:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    a\n",
       "2    c\n",
       "3    b\n",
       "4    e\n",
       "5    d\n",
       "dtype: object"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.sort_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2    c\n",
       "3    b\n",
       "4    e\n",
       "5    d\n",
       "dtype: object"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.sort_index().loc[1:6]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Selection by position"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pandas provides a suite of methods in order to get purely integer based indexing. The semantics follow closely Python and NumPy slicing. These are 0-based indexing. When slicing, the start bound is included, while the upper bound is excluded. Trying to use a non-integer, even a valid label will raise an IndexError."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The .iloc attribute is the primary access method. The following are valid inputs:\n",
    "\n",
    "- An integer e.g. 5.\n",
    "- A list or array of integers [4, 3, 0].\n",
    "- A slice object with ints 1:7.\n",
    "- A boolean array.\n",
    "- A callable, see Selection By Callable."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    " s1 = pd.Series(np.random.randn(5), index=list(range(0, 10, 2)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    0.647571\n",
       "2    1.262127\n",
       "4    0.468873\n",
       "6    0.612121\n",
       "8    0.107659\n",
       "dtype: float64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    0.647571\n",
       "2    1.262127\n",
       "4    0.468873\n",
       "dtype: float64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s1.iloc[:3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.6121214935755164"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s1.iloc[3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    0.000000\n",
       "2    0.000000\n",
       "4    0.000000\n",
       "6    0.612121\n",
       "8    0.107659\n",
       "dtype: float64"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s1.iloc[:3] = 0\n",
    "s1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With a DataFrame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = pd.DataFrame(np.random.randn(6, 4),\n",
    "                   index=list(range(0, 12, 2)),\n",
    "                   columns=list(range(0, 8, 2)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>0</th>\n",
       "      <th>2</th>\n",
       "      <th>4</th>\n",
       "      <th>6</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>-1.653440</td>\n",
       "      <td>-0.785734</td>\n",
       "      <td>0.014698</td>\n",
       "      <td>0.655930</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>0.650503</td>\n",
       "      <td>-0.122037</td>\n",
       "      <td>1.315481</td>\n",
       "      <td>-0.753851</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>1.188704</td>\n",
       "      <td>-0.783487</td>\n",
       "      <td>-2.154820</td>\n",
       "      <td>0.414759</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>1.662279</td>\n",
       "      <td>-1.113476</td>\n",
       "      <td>-1.882006</td>\n",
       "      <td>0.585102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>0.041996</td>\n",
       "      <td>0.466142</td>\n",
       "      <td>-0.570213</td>\n",
       "      <td>-0.423184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>-1.308567</td>\n",
       "      <td>-1.164956</td>\n",
       "      <td>0.912067</td>\n",
       "      <td>-0.283824</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           0         2         4         6\n",
       "0  -1.653440 -0.785734  0.014698  0.655930\n",
       "2   0.650503 -0.122037  1.315481 -0.753851\n",
       "4   1.188704 -0.783487 -2.154820  0.414759\n",
       "6   1.662279 -1.113476 -1.882006  0.585102\n",
       "8   0.041996  0.466142 -0.570213 -0.423184\n",
       "10 -1.308567 -1.164956  0.912067 -0.283824"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Select via integer slicing:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>0</th>\n",
       "      <th>2</th>\n",
       "      <th>4</th>\n",
       "      <th>6</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>-1.653440</td>\n",
       "      <td>-0.785734</td>\n",
       "      <td>0.014698</td>\n",
       "      <td>0.655930</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>0.650503</td>\n",
       "      <td>-0.122037</td>\n",
       "      <td>1.315481</td>\n",
       "      <td>-0.753851</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>1.188704</td>\n",
       "      <td>-0.783487</td>\n",
       "      <td>-2.154820</td>\n",
       "      <td>0.414759</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          0         2         4         6\n",
       "0 -1.653440 -0.785734  0.014698  0.655930\n",
       "2  0.650503 -0.122037  1.315481 -0.753851\n",
       "4  1.188704 -0.783487 -2.154820  0.414759"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.iloc[:3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>4</th>\n",
       "      <th>6</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>1.315481</td>\n",
       "      <td>-0.753851</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>-2.154820</td>\n",
       "      <td>0.414759</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>-1.882006</td>\n",
       "      <td>0.585102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>-0.570213</td>\n",
       "      <td>-0.423184</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          4         6\n",
       "2  1.315481 -0.753851\n",
       "4 -2.154820  0.414759\n",
       "6 -1.882006  0.585102\n",
       "8 -0.570213 -0.423184"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.iloc[1:5, 2:4]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Select via integer list:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>2</th>\n",
       "      <th>6</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>-0.122037</td>\n",
       "      <td>-0.753851</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>-1.113476</td>\n",
       "      <td>0.585102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>-1.164956</td>\n",
       "      <td>-0.283824</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           2         6\n",
       "2  -0.122037 -0.753851\n",
       "6  -1.113476  0.585102\n",
       "10 -1.164956 -0.283824"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.iloc[[1, 3, 5], [1, 3]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "-0.12203673606980107"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.iloc[1, 1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Selection by callable"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    ".loc, .iloc, and also [] indexing can accept a callable as indexer. The callable must be a function with one argument (the calling Series or DataFrame) that returns valid output for indexing."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = pd.DataFrame(np.random.randn(6, 4),\n",
    "                   index=list('abcdef'),\n",
    "                   columns=list('ABCD'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>a</td>\n",
       "      <td>-1.041412</td>\n",
       "      <td>-0.167223</td>\n",
       "      <td>-0.027865</td>\n",
       "      <td>-0.216964</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>b</td>\n",
       "      <td>0.975096</td>\n",
       "      <td>0.528548</td>\n",
       "      <td>-0.308485</td>\n",
       "      <td>-0.326877</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>c</td>\n",
       "      <td>-1.480319</td>\n",
       "      <td>1.311950</td>\n",
       "      <td>-1.429285</td>\n",
       "      <td>-0.615063</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>d</td>\n",
       "      <td>-0.297724</td>\n",
       "      <td>-1.470654</td>\n",
       "      <td>1.599896</td>\n",
       "      <td>0.340529</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>e</td>\n",
       "      <td>-0.839494</td>\n",
       "      <td>0.517960</td>\n",
       "      <td>0.197426</td>\n",
       "      <td>-0.032205</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>f</td>\n",
       "      <td>-1.157605</td>\n",
       "      <td>-0.815674</td>\n",
       "      <td>-0.513306</td>\n",
       "      <td>0.890670</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          A         B         C         D\n",
       "a -1.041412 -0.167223 -0.027865 -0.216964\n",
       "b  0.975096  0.528548 -0.308485 -0.326877\n",
       "c -1.480319  1.311950 -1.429285 -0.615063\n",
       "d -0.297724 -1.470654  1.599896  0.340529\n",
       "e -0.839494  0.517960  0.197426 -0.032205\n",
       "f -1.157605 -0.815674 -0.513306  0.890670"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "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>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>b</td>\n",
       "      <td>0.975096</td>\n",
       "      <td>0.528548</td>\n",
       "      <td>-0.308485</td>\n",
       "      <td>-0.326877</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          A         B         C         D\n",
       "b  0.975096  0.528548 -0.308485 -0.326877"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.loc[lambda df: df.A > 0, :]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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>A</th>\n",
       "      <th>B</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>a</td>\n",
       "      <td>-1.041412</td>\n",
       "      <td>-0.167223</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>b</td>\n",
       "      <td>0.975096</td>\n",
       "      <td>0.528548</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>c</td>\n",
       "      <td>-1.480319</td>\n",
       "      <td>1.311950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>d</td>\n",
       "      <td>-0.297724</td>\n",
       "      <td>-1.470654</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>e</td>\n",
       "      <td>-0.839494</td>\n",
       "      <td>0.517960</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>f</td>\n",
       "      <td>-1.157605</td>\n",
       "      <td>-0.815674</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          A         B\n",
       "a -1.041412 -0.167223\n",
       "b  0.975096  0.528548\n",
       "c -1.480319  1.311950\n",
       "d -0.297724 -1.470654\n",
       "e -0.839494  0.517960\n",
       "f -1.157605 -0.815674"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.loc[:, lambda df: ['A', 'B']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "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>A</th>\n",
       "      <th>B</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>a</td>\n",
       "      <td>-1.041412</td>\n",
       "      <td>-0.167223</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>b</td>\n",
       "      <td>0.975096</td>\n",
       "      <td>0.528548</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>c</td>\n",
       "      <td>-1.480319</td>\n",
       "      <td>1.311950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>d</td>\n",
       "      <td>-0.297724</td>\n",
       "      <td>-1.470654</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>e</td>\n",
       "      <td>-0.839494</td>\n",
       "      <td>0.517960</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>f</td>\n",
       "      <td>-1.157605</td>\n",
       "      <td>-0.815674</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          A         B\n",
       "a -1.041412 -0.167223\n",
       "b  0.975096  0.528548\n",
       "c -1.480319  1.311950\n",
       "d -0.297724 -1.470654\n",
       "e -0.839494  0.517960\n",
       "f -1.157605 -0.815674"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.iloc[:, lambda df: [0, 1]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a   -1.041412\n",
       "b    0.975096\n",
       "c   -1.480319\n",
       "d   -0.297724\n",
       "e   -0.839494\n",
       "f   -1.157605\n",
       "Name: A, dtype: float64"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1[lambda df: df.columns[0]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can use callable indexing in Series."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "b    0.975096\n",
       "Name: A, dtype: float64"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.A.loc[lambda s: s > 0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Selecting random samples"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = pd.Series([0, 1, 2, 3, 4, 5])\n",
    "# When no arguments are passed, returns 1 row.\n",
    "s.sample()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3    3\n",
       "2    2\n",
       "5    5\n",
       "dtype: int64"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# One may specify either a number of rows:\n",
    "s.sample(n=3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4    4\n",
       "1    1\n",
       "2    2\n",
       "dtype: int64"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Or a fraction of the rows:\n",
    "s.sample(frac=0.5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By default, sample will return each row at most once, but one can also sample with replacement using the replace option:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3    3\n",
       "2    2\n",
       "3    3\n",
       "1    1\n",
       "4    4\n",
       "4    4\n",
       "dtype: int64"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# With replacement:\n",
    "s.sample(n=6, replace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Setting with enlargement"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The .loc/[] operations can perform enlargement when setting a non-existent key for that axis.\n",
    "\n",
    "In the Series case this is effectively an appending operation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    1\n",
       "1    2\n",
       "2    3\n",
       "dtype: int64"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "se = pd.Series([1, 2, 3])\n",
    "se"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    1.0\n",
       "1    2.0\n",
       "2    3.0\n",
       "5    5.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "se[5] = 5.\n",
    "se"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A DataFrame can be enlarged on either axis via .loc."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "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>A</th>\n",
       "      <th>B</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   A  B\n",
       "0  0  1\n",
       "1  2  3\n",
       "2  4  5"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfi = pd.DataFrame(np.arange(6).reshape(3, 2),\n",
    "                  columns=['A', 'B'])\n",
    "dfi"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "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>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   A  B  C\n",
       "0  0  1  0\n",
       "1  2  3  2\n",
       "2  4  5  4"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfi.loc[:, 'C'] = dfi.loc[:, 'A']\n",
    "dfi"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is like an append operation on the DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "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>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   A  B  C\n",
       "0  0  1  0\n",
       "1  2  3  2\n",
       "2  4  5  4\n",
       "3  5  5  5"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfi.loc[3] = 5\n",
    "dfi"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Boolean indexing"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Another common operation is the use of boolean vectors to filter the data. The operators are: | for or, & for and, and ~ for not. These must be grouped by using parentheses, since by default Python will evaluate an expression such as df.A > 2 & df.B < 3 as df.A > (2 & df.B) < 3, while the desired evaluation order is (df.A > 2) & (df.B < 3).\n",
    "\n",
    "Using a boolean vector to index a Series works exactly as in a NumPy ndarray:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0   -3\n",
       "1   -2\n",
       "2   -1\n",
       "3    0\n",
       "4    1\n",
       "5    2\n",
       "6    3\n",
       "dtype: int64"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = pd.Series(range(-3, 4))\n",
    "s"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4    1\n",
       "5    2\n",
       "6    3\n",
       "dtype: int64"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s[s > 0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0   -3\n",
       "1   -2\n",
       "4    1\n",
       "5    2\n",
       "6    3\n",
       "dtype: int64"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s[(s < -1) | (s > 0.5)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3    0\n",
       "4    1\n",
       "5    2\n",
       "6    3\n",
       "dtype: int64"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s[~(s < 0)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You may select rows from a DataFrame using a boolean vector the same length as the DataFrame’s index (for example, something derived from one of the columns of the DataFrame):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "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>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2000-01-01</td>\n",
       "      <td>0.542045</td>\n",
       "      <td>0.971047</td>\n",
       "      <td>-0.495519</td>\n",
       "      <td>0.137423</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-07</td>\n",
       "      <td>0.345925</td>\n",
       "      <td>-0.122817</td>\n",
       "      <td>0.797087</td>\n",
       "      <td>-3.073061</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   A         B         C         D\n",
       "2000-01-01  0.542045  0.971047 -0.495519  0.137423\n",
       "2000-01-07  0.345925 -0.122817  0.797087 -3.073061"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['A'] > 0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "List comprehensions and the map method of Series can also be used to produce more complex criteria:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "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>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>one</td>\n",
       "      <td>x</td>\n",
       "      <td>-1.252611</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>one</td>\n",
       "      <td>y</td>\n",
       "      <td>-0.645279</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>two</td>\n",
       "      <td>y</td>\n",
       "      <td>2.426394</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>three</td>\n",
       "      <td>x</td>\n",
       "      <td>0.756417</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>two</td>\n",
       "      <td>y</td>\n",
       "      <td>-0.506433</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>one</td>\n",
       "      <td>x</td>\n",
       "      <td>-0.726977</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>six</td>\n",
       "      <td>x</td>\n",
       "      <td>0.655565</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       a  b         c\n",
       "0    one  x -1.252611\n",
       "1    one  y -0.645279\n",
       "2    two  y  2.426394\n",
       "3  three  x  0.756417\n",
       "4    two  y -0.506433\n",
       "5    one  x -0.726977\n",
       "6    six  x  0.655565"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = pd.DataFrame({'a': ['one', 'one', 'two', 'three', 'two', 'one', 'six'],\n",
    "                    'b': ['x', 'y', 'y', 'x', 'y', 'x', 'x'],\n",
    "                    'c': np.random.randn(7)})\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    False\n",
       "1    False\n",
       "2     True\n",
       "3     True\n",
       "4     True\n",
       "5    False\n",
       "6    False\n",
       "Name: a, dtype: bool"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# only want 'two' or 'three'\n",
    "criterion = df2['a'].map(lambda x: x.startswith('t'))\n",
    "criterion"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "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>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>two</td>\n",
       "      <td>y</td>\n",
       "      <td>2.426394</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>three</td>\n",
       "      <td>x</td>\n",
       "      <td>0.756417</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>two</td>\n",
       "      <td>y</td>\n",
       "      <td>-0.506433</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       a  b         c\n",
       "2    two  y  2.426394\n",
       "3  three  x  0.756417\n",
       "4    two  y -0.506433"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2[criterion]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "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>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>two</td>\n",
       "      <td>y</td>\n",
       "      <td>2.426394</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>three</td>\n",
       "      <td>x</td>\n",
       "      <td>0.756417</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>two</td>\n",
       "      <td>y</td>\n",
       "      <td>-0.506433</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       a  b         c\n",
       "2    two  y  2.426394\n",
       "3  three  x  0.756417\n",
       "4    two  y -0.506433"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# equivalent but slower\n",
    "df2[[x.startswith('t') for x in df2['a']]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>three</td>\n",
       "      <td>x</td>\n",
       "      <td>0.756417</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       a  b         c\n",
       "3  three  x  0.756417"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Multiple criteria\n",
    "df2[criterion & (df2['b'] == 'x')]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With the choice methods Selection by Label, Selection by Position, and Advanced Indexing you may select along more than one axis using boolean vectors combined with other indexing expressions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "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>b</th>\n",
       "      <th>c</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>x</td>\n",
       "      <td>0.756417</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   b         c\n",
       "3  x  0.756417"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.loc[criterion & (df2['b'] == 'x'), 'b':'c']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Indexing with isin"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Consider the isin() method of Series, which returns a boolean vector that is true wherever the Series elements exist in the passed list. This allows you to select rows where one or more columns have values you want:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4    0\n",
       "3    1\n",
       "2    2\n",
       "1    3\n",
       "0    4\n",
       "dtype: int64"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = pd.Series(np.arange(5), index=np.arange(5)[::-1], dtype='int64')\n",
    "s"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4    False\n",
       "3    False\n",
       "2     True\n",
       "1    False\n",
       "0     True\n",
       "dtype: bool"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.isin([2, 4, 6])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2    2\n",
       "0    4\n",
       "dtype: int64"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s[s.isin([2, 4, 6])]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The same method is available for Index objects and is useful for the cases when you don’t know which of the sought labels are in fact present:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4    0\n",
       "2    2\n",
       "dtype: int64"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s[s.index.isin([2, 4, 6])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2    2.0\n",
       "4    0.0\n",
       "6    NaN\n",
       "dtype: float64"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# compare it to the following\n",
    "s.reindex([2, 4, 6])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In addition to that, MultiIndex allows selecting a separate level to use in the membership check:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0  a    0\n",
       "   b    1\n",
       "   c    2\n",
       "1  a    3\n",
       "   b    4\n",
       "   c    5\n",
       "dtype: int32"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s_mi = pd.Series(np.arange(6),\n",
    "                  index=pd.MultiIndex.from_product([[0, 1], ['a', 'b', 'c']]))\n",
    "s_mi"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0  c    2\n",
       "1  a    3\n",
       "dtype: int32"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s_mi.iloc[s_mi.index.isin([(1, 'a'), (2, 'b'), (0, 'c')])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0  a    0\n",
       "   c    2\n",
       "1  a    3\n",
       "   c    5\n",
       "dtype: int32"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s_mi.iloc[s_mi.index.isin(['a', 'c', 'e'], level=1)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "DataFrame also has an isin() method. When calling isin, pass a set of values as either an array or dict. If values is an array, isin returns a DataFrame of booleans that is the same shape as the original DataFrame, with True wherever the element is in the sequence of values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "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>vals</th>\n",
       "      <th>ids</th>\n",
       "      <th>ids2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>a</td>\n",
       "      <td>a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>b</td>\n",
       "      <td>n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>f</td>\n",
       "      <td>c</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>n</td>\n",
       "      <td>n</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   vals ids ids2\n",
       "0     1   a    a\n",
       "1     2   b    n\n",
       "2     3   f    c\n",
       "3     4   n    n"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame({'vals': [1, 2, 3, 4], 'ids': ['a', 'b', 'f', 'n'],\n",
    "                   'ids2': ['a', 'n', 'c', 'n']})\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "values = ['a', 'b', 1, 3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "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>vals</th>\n",
       "      <th>ids</th>\n",
       "      <th>ids2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    vals    ids   ids2\n",
       "0   True   True   True\n",
       "1  False   True  False\n",
       "2   True  False  False\n",
       "3  False  False  False"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.isin(values)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Oftentimes you’ll want to match certain values with certain columns. Just make values a dict where the key is the column, and the value is a list of items you want to check for."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "values = {'ids': ['a', 'b'], 'vals': [1, 3]}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>vals</th>\n",
       "      <th>ids</th>\n",
       "      <th>ids2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    vals    ids   ids2\n",
       "0   True   True  False\n",
       "1  False   True  False\n",
       "2   True  False  False\n",
       "3  False  False  False"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.isin(values)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Combine DataFrame’s isin with the any() and all() methods to quickly select subsets of your data that meet a given criteria. To select a row where each column meets its own criterion:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [],
   "source": [
    "values = {'ids': ['a', 'b'], 'ids2': ['a', 'c'], 'vals': [1, 3]}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "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>vals</th>\n",
       "      <th>ids</th>\n",
       "      <th>ids2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>a</td>\n",
       "      <td>a</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   vals ids ids2\n",
       "0     1   a    a"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "row_mask = df.isin(values).all(1)\n",
    "df[row_mask]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The where() Method and Masking"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Selecting values from a Series with a boolean vector generally returns a subset of the data. To guarantee that selection output has the same shape as the original data, you can use the where method in Series and DataFrame.\n",
    "\n",
    "To return only the selected rows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3    1\n",
       "2    2\n",
       "1    3\n",
       "0    4\n",
       "dtype: int64"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s[s > 0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To return a Series of the same shape as the original:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4    NaN\n",
       "3    1.0\n",
       "2    2.0\n",
       "1    3.0\n",
       "0    4.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.where(s > 0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Selecting values from a DataFrame with a boolean criterion now also preserves input data shape. where is used under the hood as the implementation. The code below is equivalent to df.where(df < 0)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(np.random.randn(8, 4), index=dates, columns=['A', 'B', 'C', 'D'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "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>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2000-01-01</td>\n",
       "      <td>-0.909418</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.306566</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-02</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.169101</td>\n",
       "      <td>-0.790862</td>\n",
       "      <td>-0.102295</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-03</td>\n",
       "      <td>-1.297461</td>\n",
       "      <td>-0.656223</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-04</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.086942</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.461271</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-05</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-06</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.314352</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-07</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-2.738897</td>\n",
       "      <td>-0.676540</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   A         B         C         D\n",
       "2000-01-01 -0.909418       NaN       NaN -0.306566\n",
       "2000-01-02       NaN -0.169101 -0.790862 -0.102295\n",
       "2000-01-03 -1.297461 -0.656223       NaN       NaN\n",
       "2000-01-04       NaN -0.086942       NaN -0.461271\n",
       "2000-01-05       NaN       NaN       NaN       NaN\n",
       "2000-01-06       NaN -0.314352       NaN       NaN\n",
       "2000-01-07       NaN       NaN -2.738897 -0.676540\n",
       "2000-01-08       NaN       NaN       NaN       NaN"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df < 0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In addition, where takes an optional other argument for replacement of values where the condition is False, in the returned copy."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "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>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2000-01-01</td>\n",
       "      <td>-0.909418</td>\n",
       "      <td>-1.038693</td>\n",
       "      <td>-0.062330</td>\n",
       "      <td>-0.306566</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-02</td>\n",
       "      <td>-0.487326</td>\n",
       "      <td>-0.169101</td>\n",
       "      <td>-0.790862</td>\n",
       "      <td>-0.102295</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-03</td>\n",
       "      <td>-1.297461</td>\n",
       "      <td>-0.656223</td>\n",
       "      <td>-0.631014</td>\n",
       "      <td>-0.793230</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-04</td>\n",
       "      <td>-0.301923</td>\n",
       "      <td>-0.086942</td>\n",
       "      <td>-0.333109</td>\n",
       "      <td>-0.461271</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-05</td>\n",
       "      <td>-0.578371</td>\n",
       "      <td>-0.180206</td>\n",
       "      <td>-0.907961</td>\n",
       "      <td>-0.597696</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-06</td>\n",
       "      <td>-0.405113</td>\n",
       "      <td>-0.314352</td>\n",
       "      <td>-0.658482</td>\n",
       "      <td>-1.002064</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-07</td>\n",
       "      <td>-0.571720</td>\n",
       "      <td>-0.736449</td>\n",
       "      <td>-2.738897</td>\n",
       "      <td>-0.676540</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-08</td>\n",
       "      <td>-1.114886</td>\n",
       "      <td>-0.259167</td>\n",
       "      <td>-0.943092</td>\n",
       "      <td>-1.543070</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   A         B         C         D\n",
       "2000-01-01 -0.909418 -1.038693 -0.062330 -0.306566\n",
       "2000-01-02 -0.487326 -0.169101 -0.790862 -0.102295\n",
       "2000-01-03 -1.297461 -0.656223 -0.631014 -0.793230\n",
       "2000-01-04 -0.301923 -0.086942 -0.333109 -0.461271\n",
       "2000-01-05 -0.578371 -0.180206 -0.907961 -0.597696\n",
       "2000-01-06 -0.405113 -0.314352 -0.658482 -1.002064\n",
       "2000-01-07 -0.571720 -0.736449 -2.738897 -0.676540\n",
       "2000-01-08 -1.114886 -0.259167 -0.943092 -1.543070"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.where(df < 0, -df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You may wish to set values based on some boolean criteria. This can be done intuitively like so:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4    0\n",
       "3    1\n",
       "2    2\n",
       "1    3\n",
       "0    4\n",
       "dtype: int64"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s2 = s.copy()\n",
    "s2[s2 < 0] = 0\n",
    "s2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "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>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2000-01-01</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.038693</td>\n",
       "      <td>0.062330</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-02</td>\n",
       "      <td>0.487326</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-03</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.631014</td>\n",
       "      <td>0.793230</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-04</td>\n",
       "      <td>0.301923</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.333109</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-05</td>\n",
       "      <td>0.578371</td>\n",
       "      <td>0.180206</td>\n",
       "      <td>0.907961</td>\n",
       "      <td>0.597696</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-06</td>\n",
       "      <td>0.405113</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.658482</td>\n",
       "      <td>1.002064</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-07</td>\n",
       "      <td>0.571720</td>\n",
       "      <td>0.736449</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-08</td>\n",
       "      <td>1.114886</td>\n",
       "      <td>0.259167</td>\n",
       "      <td>0.943092</td>\n",
       "      <td>1.543070</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   A         B         C         D\n",
       "2000-01-01  0.000000  1.038693  0.062330  0.000000\n",
       "2000-01-02  0.487326  0.000000  0.000000  0.000000\n",
       "2000-01-03  0.000000  0.000000  0.631014  0.793230\n",
       "2000-01-04  0.301923  0.000000  0.333109  0.000000\n",
       "2000-01-05  0.578371  0.180206  0.907961  0.597696\n",
       "2000-01-06  0.405113  0.000000  0.658482  1.002064\n",
       "2000-01-07  0.571720  0.736449  0.000000  0.000000\n",
       "2000-01-08  1.114886  0.259167  0.943092  1.543070"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = df.copy()\n",
    "df2[df2 < 0] = 0\n",
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By default, where returns a modified copy of the data. There is an optional parameter inplace so that the original data can be modified without creating a copy:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "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>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2000-01-01</td>\n",
       "      <td>0.909418</td>\n",
       "      <td>1.038693</td>\n",
       "      <td>0.062330</td>\n",
       "      <td>0.306566</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-02</td>\n",
       "      <td>0.487326</td>\n",
       "      <td>0.169101</td>\n",
       "      <td>0.790862</td>\n",
       "      <td>0.102295</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-03</td>\n",
       "      <td>1.297461</td>\n",
       "      <td>0.656223</td>\n",
       "      <td>0.631014</td>\n",
       "      <td>0.793230</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-04</td>\n",
       "      <td>0.301923</td>\n",
       "      <td>0.086942</td>\n",
       "      <td>0.333109</td>\n",
       "      <td>0.461271</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-05</td>\n",
       "      <td>0.578371</td>\n",
       "      <td>0.180206</td>\n",
       "      <td>0.907961</td>\n",
       "      <td>0.597696</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-06</td>\n",
       "      <td>0.405113</td>\n",
       "      <td>0.314352</td>\n",
       "      <td>0.658482</td>\n",
       "      <td>1.002064</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-07</td>\n",
       "      <td>0.571720</td>\n",
       "      <td>0.736449</td>\n",
       "      <td>2.738897</td>\n",
       "      <td>0.676540</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-08</td>\n",
       "      <td>1.114886</td>\n",
       "      <td>0.259167</td>\n",
       "      <td>0.943092</td>\n",
       "      <td>1.543070</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   A         B         C         D\n",
       "2000-01-01  0.909418  1.038693  0.062330  0.306566\n",
       "2000-01-02  0.487326  0.169101  0.790862  0.102295\n",
       "2000-01-03  1.297461  0.656223  0.631014  0.793230\n",
       "2000-01-04  0.301923  0.086942  0.333109  0.461271\n",
       "2000-01-05  0.578371  0.180206  0.907961  0.597696\n",
       "2000-01-06  0.405113  0.314352  0.658482  1.002064\n",
       "2000-01-07  0.571720  0.736449  2.738897  0.676540\n",
       "2000-01-08  1.114886  0.259167  0.943092  1.543070"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_orig = df.copy()\n",
    "df_orig.where(df > 0, -df, inplace=True)\n",
    "df_orig"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Alignment"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Furthermore, where aligns the input boolean condition (ndarray or DataFrame), such that partial selection with setting is possible. This is analogous to partial setting via .loc (but on the contents rather than the axis labels)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2000-01-01</td>\n",
       "      <td>-0.909418</td>\n",
       "      <td>1.038693</td>\n",
       "      <td>0.062330</td>\n",
       "      <td>-0.306566</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-02</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>-0.169101</td>\n",
       "      <td>-0.790862</td>\n",
       "      <td>-0.102295</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-03</td>\n",
       "      <td>-1.297461</td>\n",
       "      <td>-0.656223</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>3.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-04</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>-0.086942</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>-0.461271</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-05</td>\n",
       "      <td>0.578371</td>\n",
       "      <td>0.180206</td>\n",
       "      <td>0.907961</td>\n",
       "      <td>0.597696</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-06</td>\n",
       "      <td>0.405113</td>\n",
       "      <td>-0.314352</td>\n",
       "      <td>0.658482</td>\n",
       "      <td>1.002064</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-07</td>\n",
       "      <td>0.571720</td>\n",
       "      <td>0.736449</td>\n",
       "      <td>-2.738897</td>\n",
       "      <td>-0.676540</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-08</td>\n",
       "      <td>1.114886</td>\n",
       "      <td>0.259167</td>\n",
       "      <td>0.943092</td>\n",
       "      <td>1.543070</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   A         B         C         D\n",
       "2000-01-01 -0.909418  1.038693  0.062330 -0.306566\n",
       "2000-01-02  3.000000 -0.169101 -0.790862 -0.102295\n",
       "2000-01-03 -1.297461 -0.656223  3.000000  3.000000\n",
       "2000-01-04  3.000000 -0.086942  3.000000 -0.461271\n",
       "2000-01-05  0.578371  0.180206  0.907961  0.597696\n",
       "2000-01-06  0.405113 -0.314352  0.658482  1.002064\n",
       "2000-01-07  0.571720  0.736449 -2.738897 -0.676540\n",
       "2000-01-08  1.114886  0.259167  0.943092  1.543070"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = df.copy()\n",
    "df2[df2[1:4] > 0] = 3\n",
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Where can also accept axis and level parameters to align the input when performing the where."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "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>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2000-01-01</td>\n",
       "      <td>-0.909418</td>\n",
       "      <td>1.038693</td>\n",
       "      <td>0.062330</td>\n",
       "      <td>-0.909418</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-02</td>\n",
       "      <td>0.487326</td>\n",
       "      <td>0.487326</td>\n",
       "      <td>0.487326</td>\n",
       "      <td>0.487326</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-03</td>\n",
       "      <td>-1.297461</td>\n",
       "      <td>-1.297461</td>\n",
       "      <td>0.631014</td>\n",
       "      <td>0.793230</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-04</td>\n",
       "      <td>0.301923</td>\n",
       "      <td>0.301923</td>\n",
       "      <td>0.333109</td>\n",
       "      <td>0.301923</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-05</td>\n",
       "      <td>0.578371</td>\n",
       "      <td>0.180206</td>\n",
       "      <td>0.907961</td>\n",
       "      <td>0.597696</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-06</td>\n",
       "      <td>0.405113</td>\n",
       "      <td>0.405113</td>\n",
       "      <td>0.658482</td>\n",
       "      <td>1.002064</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-07</td>\n",
       "      <td>0.571720</td>\n",
       "      <td>0.736449</td>\n",
       "      <td>0.571720</td>\n",
       "      <td>0.571720</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2000-01-08</td>\n",
       "      <td>1.114886</td>\n",
       "      <td>0.259167</td>\n",
       "      <td>0.943092</td>\n",
       "      <td>1.543070</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   A         B         C         D\n",
       "2000-01-01 -0.909418  1.038693  0.062330 -0.909418\n",
       "2000-01-02  0.487326  0.487326  0.487326  0.487326\n",
       "2000-01-03 -1.297461 -1.297461  0.631014  0.793230\n",
       "2000-01-04  0.301923  0.301923  0.333109  0.301923\n",
       "2000-01-05  0.578371  0.180206  0.907961  0.597696\n",
       "2000-01-06  0.405113  0.405113  0.658482  1.002064\n",
       "2000-01-07  0.571720  0.736449  0.571720  0.571720\n",
       "2000-01-08  1.114886  0.259167  0.943092  1.543070"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = df.copy()\n",
    "df2.where(df2 > 0, df2['A'], axis='index')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The query() Method"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "DataFrame objects have a query() method that allows selection using an expression.\n",
    "\n",
    "You can get the value of the frame where column b has values between the values of columns a and c. For example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [],
   "source": [
    "n = 10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "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>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>0.908424</td>\n",
       "      <td>0.388245</td>\n",
       "      <td>0.404597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>0.555787</td>\n",
       "      <td>0.034677</td>\n",
       "      <td>0.881989</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>0.999417</td>\n",
       "      <td>0.875911</td>\n",
       "      <td>0.697915</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>0.412039</td>\n",
       "      <td>0.875874</td>\n",
       "      <td>0.415148</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>0.304421</td>\n",
       "      <td>0.563916</td>\n",
       "      <td>0.898697</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>0.899240</td>\n",
       "      <td>0.012951</td>\n",
       "      <td>0.645819</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>0.583757</td>\n",
       "      <td>0.516434</td>\n",
       "      <td>0.382090</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>0.928993</td>\n",
       "      <td>0.001691</td>\n",
       "      <td>0.148981</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>0.339668</td>\n",
       "      <td>0.869557</td>\n",
       "      <td>0.553435</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>0.879368</td>\n",
       "      <td>0.109711</td>\n",
       "      <td>0.128623</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          a         b         c\n",
       "0  0.908424  0.388245  0.404597\n",
       "1  0.555787  0.034677  0.881989\n",
       "2  0.999417  0.875911  0.697915\n",
       "3  0.412039  0.875874  0.415148\n",
       "4  0.304421  0.563916  0.898697\n",
       "5  0.899240  0.012951  0.645819\n",
       "6  0.583757  0.516434  0.382090\n",
       "7  0.928993  0.001691  0.148981\n",
       "8  0.339668  0.869557  0.553435\n",
       "9  0.879368  0.109711  0.128623"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame(np.random.rand(n, 3), columns=list('abc'))\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "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>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>0.304421</td>\n",
       "      <td>0.563916</td>\n",
       "      <td>0.898697</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          a         b         c\n",
       "4  0.304421  0.563916  0.898697"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# pure python\n",
    "df[(df.a < df.b) & (df.b < df.c)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "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>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>0.304421</td>\n",
       "      <td>0.563916</td>\n",
       "      <td>0.898697</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          a         b         c\n",
       "4  0.304421  0.563916  0.898697"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# query\n",
    "df.query('(a < b) & (b < c)')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Do the same thing but fall back on a named index if there is no column with the name a."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "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>b</th>\n",
       "      <th>c</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   b  c\n",
       "a      \n",
       "0  1  1\n",
       "1  2  0\n",
       "2  3  0\n",
       "3  2  3\n",
       "4  4  3\n",
       "5  1  3\n",
       "6  3  4\n",
       "7  0  1\n",
       "8  4  1\n",
       "9  4  3"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame(np.random.randint(n / 2, size=(n, 2)), columns=list('bc'))\n",
    "df.index.name = 'a'\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "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>b</th>\n",
       "      <th>c</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [b, c]\n",
       "Index: []"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.query('a < b and b < c')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If instead you don’t want to or cannot name your index, you can use the name index in your query expression:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "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>b</th>\n",
       "      <th>c</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   b  c\n",
       "0  1  4\n",
       "1  8  8\n",
       "2  8  9\n",
       "3  6  0\n",
       "4  4  7\n",
       "5  3  7\n",
       "6  1  1\n",
       "7  7  4\n",
       "8  8  4\n",
       "9  9  5"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame(np.random.randint(n, size=(n, 2)), columns=list('bc'))\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "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>b</th>\n",
       "      <th>c</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   b  c\n",
       "0  1  4\n",
       "2  8  9"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.query('index < b < c')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## MultiIndex query() Syntax"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also use the levels of a DataFrame with a MultiIndex as if they were columns in the frame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [],
   "source": [
    "n = 10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['red', 'red', 'red', 'red', 'red', 'red', 'green', 'green',\n",
       "       'green', 'green'], dtype='<U5')"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "colors = np.random.choice(['red', 'green'], size=n)\n",
    "colors"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['ham', 'ham', 'eggs', 'eggs', 'eggs', 'eggs', 'eggs', 'eggs',\n",
       "       'eggs', 'eggs'], dtype='<U4')"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "foods = np.random.choice(['eggs', 'ham'], size=n)\n",
    "foods"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [],
   "source": [
    "index = pd.MultiIndex.from_arrays([colors, foods], names=['color', 'food'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(np.random.randn(n, 2), index=index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "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></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>color</th>\n",
       "      <th>food</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td rowspan=\"6\" valign=\"top\">red</td>\n",
       "      <td>ham</td>\n",
       "      <td>0.341503</td>\n",
       "      <td>-0.697112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>ham</td>\n",
       "      <td>1.143331</td>\n",
       "      <td>0.493137</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>eggs</td>\n",
       "      <td>0.763090</td>\n",
       "      <td>-1.584505</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>eggs</td>\n",
       "      <td>0.521574</td>\n",
       "      <td>0.376577</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>eggs</td>\n",
       "      <td>-1.099881</td>\n",
       "      <td>0.446010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>eggs</td>\n",
       "      <td>0.354173</td>\n",
       "      <td>0.819636</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td rowspan=\"4\" valign=\"top\">green</td>\n",
       "      <td>eggs</td>\n",
       "      <td>-2.256711</td>\n",
       "      <td>0.542087</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>eggs</td>\n",
       "      <td>-0.037731</td>\n",
       "      <td>0.352857</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>eggs</td>\n",
       "      <td>-0.963752</td>\n",
       "      <td>1.390826</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>eggs</td>\n",
       "      <td>-0.877505</td>\n",
       "      <td>0.255727</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   0         1\n",
       "color food                    \n",
       "red   ham   0.341503 -0.697112\n",
       "      ham   1.143331  0.493137\n",
       "      eggs  0.763090 -1.584505\n",
       "      eggs  0.521574  0.376577\n",
       "      eggs -1.099881  0.446010\n",
       "      eggs  0.354173  0.819636\n",
       "green eggs -2.256711  0.542087\n",
       "      eggs -0.037731  0.352857\n",
       "      eggs -0.963752  1.390826\n",
       "      eggs -0.877505  0.255727"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "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></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>color</th>\n",
       "      <th>food</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td rowspan=\"6\" valign=\"top\">red</td>\n",
       "      <td>ham</td>\n",
       "      <td>0.341503</td>\n",
       "      <td>-0.697112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>ham</td>\n",
       "      <td>1.143331</td>\n",
       "      <td>0.493137</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>eggs</td>\n",
       "      <td>0.763090</td>\n",
       "      <td>-1.584505</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>eggs</td>\n",
       "      <td>0.521574</td>\n",
       "      <td>0.376577</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>eggs</td>\n",
       "      <td>-1.099881</td>\n",
       "      <td>0.446010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>eggs</td>\n",
       "      <td>0.354173</td>\n",
       "      <td>0.819636</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   0         1\n",
       "color food                    \n",
       "red   ham   0.341503 -0.697112\n",
       "      ham   1.143331  0.493137\n",
       "      eggs  0.763090 -1.584505\n",
       "      eggs  0.521574  0.376577\n",
       "      eggs -1.099881  0.446010\n",
       "      eggs  0.354173  0.819636"
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.query('color == \"red\"')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If the levels of the MultiIndex are unnamed, you can refer to them using special names:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "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></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td rowspan=\"6\" valign=\"top\">red</td>\n",
       "      <td>ham</td>\n",
       "      <td>0.341503</td>\n",
       "      <td>-0.697112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>ham</td>\n",
       "      <td>1.143331</td>\n",
       "      <td>0.493137</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>eggs</td>\n",
       "      <td>0.763090</td>\n",
       "      <td>-1.584505</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>eggs</td>\n",
       "      <td>0.521574</td>\n",
       "      <td>0.376577</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>eggs</td>\n",
       "      <td>-1.099881</td>\n",
       "      <td>0.446010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>eggs</td>\n",
       "      <td>0.354173</td>\n",
       "      <td>0.819636</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td rowspan=\"4\" valign=\"top\">green</td>\n",
       "      <td>eggs</td>\n",
       "      <td>-2.256711</td>\n",
       "      <td>0.542087</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>eggs</td>\n",
       "      <td>-0.037731</td>\n",
       "      <td>0.352857</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>eggs</td>\n",
       "      <td>-0.963752</td>\n",
       "      <td>1.390826</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>eggs</td>\n",
       "      <td>-0.877505</td>\n",
       "      <td>0.255727</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   0         1\n",
       "red   ham   0.341503 -0.697112\n",
       "      ham   1.143331  0.493137\n",
       "      eggs  0.763090 -1.584505\n",
       "      eggs  0.521574  0.376577\n",
       "      eggs -1.099881  0.446010\n",
       "      eggs  0.354173  0.819636\n",
       "green eggs -2.256711  0.542087\n",
       "      eggs -0.037731  0.352857\n",
       "      eggs -0.963752  1.390826\n",
       "      eggs -0.877505  0.255727"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.index.names = [None, None]\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "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></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td rowspan=\"6\" valign=\"top\">red</td>\n",
       "      <td>ham</td>\n",
       "      <td>0.341503</td>\n",
       "      <td>-0.697112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>ham</td>\n",
       "      <td>1.143331</td>\n",
       "      <td>0.493137</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>eggs</td>\n",
       "      <td>0.763090</td>\n",
       "      <td>-1.584505</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>eggs</td>\n",
       "      <td>0.521574</td>\n",
       "      <td>0.376577</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>eggs</td>\n",
       "      <td>-1.099881</td>\n",
       "      <td>0.446010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>eggs</td>\n",
       "      <td>0.354173</td>\n",
       "      <td>0.819636</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 0         1\n",
       "red ham   0.341503 -0.697112\n",
       "    ham   1.143331  0.493137\n",
       "    eggs  0.763090 -1.584505\n",
       "    eggs  0.521574  0.376577\n",
       "    eggs -1.099881  0.446010\n",
       "    eggs  0.354173  0.819636"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.query('ilevel_0 == \"red\"')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### query() Use Cases"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A use case for query() is when you have a collection of DataFrame objects that have a subset of column names (or index levels/names) in common. You can pass the same query to both frames without having to specify which frame you’re interested in querying"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "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>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>0.814189</td>\n",
       "      <td>0.833274</td>\n",
       "      <td>0.427600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>0.083828</td>\n",
       "      <td>0.350698</td>\n",
       "      <td>0.550696</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>0.187878</td>\n",
       "      <td>0.580243</td>\n",
       "      <td>0.309829</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>0.112394</td>\n",
       "      <td>0.707579</td>\n",
       "      <td>0.593830</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>0.459885</td>\n",
       "      <td>0.477544</td>\n",
       "      <td>0.742091</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>0.858728</td>\n",
       "      <td>0.346716</td>\n",
       "      <td>0.512817</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>0.770165</td>\n",
       "      <td>0.536469</td>\n",
       "      <td>0.492052</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>0.153694</td>\n",
       "      <td>0.249172</td>\n",
       "      <td>0.493870</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>0.838038</td>\n",
       "      <td>0.584314</td>\n",
       "      <td>0.255168</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>0.609922</td>\n",
       "      <td>0.638270</td>\n",
       "      <td>0.198955</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          a         b         c\n",
       "0  0.814189  0.833274  0.427600\n",
       "1  0.083828  0.350698  0.550696\n",
       "2  0.187878  0.580243  0.309829\n",
       "3  0.112394  0.707579  0.593830\n",
       "4  0.459885  0.477544  0.742091\n",
       "5  0.858728  0.346716  0.512817\n",
       "6  0.770165  0.536469  0.492052\n",
       "7  0.153694  0.249172  0.493870\n",
       "8  0.838038  0.584314  0.255168\n",
       "9  0.609922  0.638270  0.198955"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame(np.random.rand(n, 3), columns=list('abc'))\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "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>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>0.833263</td>\n",
       "      <td>0.758818</td>\n",
       "      <td>0.793420</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>0.988505</td>\n",
       "      <td>0.998416</td>\n",
       "      <td>0.014811</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>0.734373</td>\n",
       "      <td>0.933668</td>\n",
       "      <td>0.514030</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>0.987510</td>\n",
       "      <td>0.823450</td>\n",
       "      <td>0.085583</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>0.180899</td>\n",
       "      <td>0.744538</td>\n",
       "      <td>0.469165</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>0.390772</td>\n",
       "      <td>0.190175</td>\n",
       "      <td>0.764046</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>0.344588</td>\n",
       "      <td>0.146359</td>\n",
       "      <td>0.611258</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>0.628458</td>\n",
       "      <td>0.921038</td>\n",
       "      <td>0.023861</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>0.589165</td>\n",
       "      <td>0.038185</td>\n",
       "      <td>0.271746</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>0.891400</td>\n",
       "      <td>0.963155</td>\n",
       "      <td>0.178853</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>0.770442</td>\n",
       "      <td>0.060388</td>\n",
       "      <td>0.451688</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>0.389353</td>\n",
       "      <td>0.478112</td>\n",
       "      <td>0.676749</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           a         b         c\n",
       "0   0.833263  0.758818  0.793420\n",
       "1   0.988505  0.998416  0.014811\n",
       "2   0.734373  0.933668  0.514030\n",
       "3   0.987510  0.823450  0.085583\n",
       "4   0.180899  0.744538  0.469165\n",
       "5   0.390772  0.190175  0.764046\n",
       "6   0.344588  0.146359  0.611258\n",
       "7   0.628458  0.921038  0.023861\n",
       "8   0.589165  0.038185  0.271746\n",
       "9   0.891400  0.963155  0.178853\n",
       "10  0.770442  0.060388  0.451688\n",
       "11  0.389353  0.478112  0.676749"
      ]
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = pd.DataFrame(np.random.rand(n + 2, 3), columns=df.columns)\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [],
   "source": [
    "expr = '0.0 <= a <= c <= 0.5'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<map at 0x286b3d35988>"
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "map(lambda frame: frame.query(expr), [df, df2])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### The in and not in operators"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "query() also supports special use of Python’s in and not in comparison operators, providing a succinct syntax for calling the isin method of a Series or DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "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>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>a</td>\n",
       "      <td>a</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>a</td>\n",
       "      <td>a</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>b</td>\n",
       "      <td>a</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>b</td>\n",
       "      <td>a</td>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>c</td>\n",
       "      <td>b</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>c</td>\n",
       "      <td>b</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>d</td>\n",
       "      <td>b</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>d</td>\n",
       "      <td>b</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>e</td>\n",
       "      <td>c</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>e</td>\n",
       "      <td>c</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>f</td>\n",
       "      <td>c</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>f</td>\n",
       "      <td>c</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    a  b  c  d\n",
       "0   a  a  3  6\n",
       "1   a  a  0  4\n",
       "2   b  a  2  4\n",
       "3   b  a  3  8\n",
       "4   c  b  0  5\n",
       "5   c  b  1  3\n",
       "6   d  b  1  8\n",
       "7   d  b  4  4\n",
       "8   e  c  3  2\n",
       "9   e  c  4  1\n",
       "10  f  c  4  2\n",
       "11  f  c  0  3"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get all rows where columns \"a\" and \"b\" have overlapping values\n",
    "df = pd.DataFrame({'a': list('aabbccddeeff'), 'b': list('aaaabbbbcccc'),\n",
    "                             'c': np.random.randint(5, size=12),\n",
    "                             'd': np.random.randint(9, size=12)})\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "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>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>a</td>\n",
       "      <td>a</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>a</td>\n",
       "      <td>a</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>b</td>\n",
       "      <td>a</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>b</td>\n",
       "      <td>a</td>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>c</td>\n",
       "      <td>b</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>c</td>\n",
       "      <td>b</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   a  b  c  d\n",
       "0  a  a  3  6\n",
       "1  a  a  0  4\n",
       "2  b  a  2  4\n",
       "3  b  a  3  8\n",
       "4  c  b  0  5\n",
       "5  c  b  1  3"
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.query('a in b')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "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>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>a</td>\n",
       "      <td>a</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>a</td>\n",
       "      <td>a</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>b</td>\n",
       "      <td>a</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>b</td>\n",
       "      <td>a</td>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>c</td>\n",
       "      <td>b</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>c</td>\n",
       "      <td>b</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   a  b  c  d\n",
       "0  a  a  3  6\n",
       "1  a  a  0  4\n",
       "2  b  a  2  4\n",
       "3  b  a  3  8\n",
       "4  c  b  0  5\n",
       "5  c  b  1  3"
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df.a.isin(df.b)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "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>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>d</td>\n",
       "      <td>b</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>d</td>\n",
       "      <td>b</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>e</td>\n",
       "      <td>c</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>e</td>\n",
       "      <td>c</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>f</td>\n",
       "      <td>c</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>f</td>\n",
       "      <td>c</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    a  b  c  d\n",
       "6   d  b  1  8\n",
       "7   d  b  4  4\n",
       "8   e  c  3  2\n",
       "9   e  c  4  1\n",
       "10  f  c  4  2\n",
       "11  f  c  0  3"
      ]
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.query('a not in b')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can combine this with other expressions for very succinct queries:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "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>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>a</td>\n",
       "      <td>a</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>a</td>\n",
       "      <td>a</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>b</td>\n",
       "      <td>a</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>b</td>\n",
       "      <td>a</td>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>c</td>\n",
       "      <td>b</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>c</td>\n",
       "      <td>b</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   a  b  c  d\n",
       "0  a  a  3  6\n",
       "1  a  a  0  4\n",
       "2  b  a  2  4\n",
       "3  b  a  3  8\n",
       "4  c  b  0  5\n",
       "5  c  b  1  3"
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# rows where cols a and b have overlapping values\n",
    "# and col c's values are less than col d's\n",
    "df.query('a in b and c < d')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "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>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>a</td>\n",
       "      <td>a</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>a</td>\n",
       "      <td>a</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>b</td>\n",
       "      <td>a</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>b</td>\n",
       "      <td>a</td>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>c</td>\n",
       "      <td>b</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>c</td>\n",
       "      <td>b</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>d</td>\n",
       "      <td>b</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>f</td>\n",
       "      <td>c</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    a  b  c  d\n",
       "0   a  a  3  6\n",
       "1   a  a  0  4\n",
       "2   b  a  2  4\n",
       "3   b  a  3  8\n",
       "4   c  b  0  5\n",
       "5   c  b  1  3\n",
       "6   d  b  1  8\n",
       "11  f  c  0  3"
      ]
     },
     "execution_count": 111,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# pure Python\n",
    "df[df.b.isin(df.a) & (df.c < df.d)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Special use of the == operator with list objects"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Comparing a list of values to a column using ==/!= works similarly to in/not in."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "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>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>a</td>\n",
       "      <td>a</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>a</td>\n",
       "      <td>a</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>b</td>\n",
       "      <td>a</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>b</td>\n",
       "      <td>a</td>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>c</td>\n",
       "      <td>b</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>c</td>\n",
       "      <td>b</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>d</td>\n",
       "      <td>b</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>d</td>\n",
       "      <td>b</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>e</td>\n",
       "      <td>c</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>e</td>\n",
       "      <td>c</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>f</td>\n",
       "      <td>c</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>f</td>\n",
       "      <td>c</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    a  b  c  d\n",
       "0   a  a  3  6\n",
       "1   a  a  0  4\n",
       "2   b  a  2  4\n",
       "3   b  a  3  8\n",
       "4   c  b  0  5\n",
       "5   c  b  1  3\n",
       "6   d  b  1  8\n",
       "7   d  b  4  4\n",
       "8   e  c  3  2\n",
       "9   e  c  4  1\n",
       "10  f  c  4  2\n",
       "11  f  c  0  3"
      ]
     },
     "execution_count": 112,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.query('b == [\"a\", \"b\", \"c\"]')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "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>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>a</td>\n",
       "      <td>a</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>a</td>\n",
       "      <td>a</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>b</td>\n",
       "      <td>a</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>b</td>\n",
       "      <td>a</td>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>c</td>\n",
       "      <td>b</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>c</td>\n",
       "      <td>b</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>d</td>\n",
       "      <td>b</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>d</td>\n",
       "      <td>b</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>e</td>\n",
       "      <td>c</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>e</td>\n",
       "      <td>c</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>f</td>\n",
       "      <td>c</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>f</td>\n",
       "      <td>c</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    a  b  c  d\n",
       "0   a  a  3  6\n",
       "1   a  a  0  4\n",
       "2   b  a  2  4\n",
       "3   b  a  3  8\n",
       "4   c  b  0  5\n",
       "5   c  b  1  3\n",
       "6   d  b  1  8\n",
       "7   d  b  4  4\n",
       "8   e  c  3  2\n",
       "9   e  c  4  1\n",
       "10  f  c  4  2\n",
       "11  f  c  0  3"
      ]
     },
     "execution_count": 113,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# pure Python\n",
    "df[df.b.isin([\"a\", \"b\", \"c\"])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "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>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>a</td>\n",
       "      <td>a</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>a</td>\n",
       "      <td>a</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>b</td>\n",
       "      <td>a</td>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>c</td>\n",
       "      <td>b</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>d</td>\n",
       "      <td>b</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>e</td>\n",
       "      <td>c</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>e</td>\n",
       "      <td>c</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>f</td>\n",
       "      <td>c</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>f</td>\n",
       "      <td>c</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    a  b  c  d\n",
       "0   a  a  3  6\n",
       "1   a  a  0  4\n",
       "3   b  a  3  8\n",
       "4   c  b  0  5\n",
       "7   d  b  4  4\n",
       "8   e  c  3  2\n",
       "9   e  c  4  1\n",
       "10  f  c  4  2\n",
       "11  f  c  0  3"
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.query('c != [1, 2]')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "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>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>b</td>\n",
       "      <td>a</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>c</td>\n",
       "      <td>b</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>d</td>\n",
       "      <td>b</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   a  b  c  d\n",
       "2  b  a  2  4\n",
       "5  c  b  1  3\n",
       "6  d  b  1  8"
      ]
     },
     "execution_count": 115,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# using in/not in\n",
    "df.query('[1, 2] in c')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "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>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>a</td>\n",
       "      <td>a</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>a</td>\n",
       "      <td>a</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>b</td>\n",
       "      <td>a</td>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>c</td>\n",
       "      <td>b</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>d</td>\n",
       "      <td>b</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>e</td>\n",
       "      <td>c</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>e</td>\n",
       "      <td>c</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>f</td>\n",
       "      <td>c</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>f</td>\n",
       "      <td>c</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    a  b  c  d\n",
       "0   a  a  3  6\n",
       "1   a  a  0  4\n",
       "3   b  a  3  8\n",
       "4   c  b  0  5\n",
       "7   d  b  4  4\n",
       "8   e  c  3  2\n",
       "9   e  c  4  1\n",
       "10  f  c  4  2\n",
       "11  f  c  0  3"
      ]
     },
     "execution_count": 116,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.query('[1, 2] not in c')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "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>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>b</td>\n",
       "      <td>a</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>c</td>\n",
       "      <td>b</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>d</td>\n",
       "      <td>b</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   a  b  c  d\n",
       "2  b  a  2  4\n",
       "5  c  b  1  3\n",
       "6  d  b  1  8"
      ]
     },
     "execution_count": 117,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# pure Python\n",
    "df[df.c.isin([1, 2])]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Boolean operators"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can negate boolean expressions with the word not or the ~ operator."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(np.random.rand(n, 3), columns=list('abc'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['bools'] = np.random.rand(len(df)) > 0.5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "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>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>bools</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>0.794435</td>\n",
       "      <td>0.963006</td>\n",
       "      <td>0.283197</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>0.175726</td>\n",
       "      <td>0.530576</td>\n",
       "      <td>0.949796</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>0.524196</td>\n",
       "      <td>0.793120</td>\n",
       "      <td>0.674460</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>0.406132</td>\n",
       "      <td>0.784641</td>\n",
       "      <td>0.855185</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          a         b         c  bools\n",
       "0  0.794435  0.963006  0.283197  False\n",
       "5  0.175726  0.530576  0.949796  False\n",
       "8  0.524196  0.793120  0.674460  False\n",
       "9  0.406132  0.784641  0.855185  False"
      ]
     },
     "execution_count": 120,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.query('~bools')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "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>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>bools</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>0.794435</td>\n",
       "      <td>0.963006</td>\n",
       "      <td>0.283197</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>0.175726</td>\n",
       "      <td>0.530576</td>\n",
       "      <td>0.949796</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>0.524196</td>\n",
       "      <td>0.793120</td>\n",
       "      <td>0.674460</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>0.406132</td>\n",
       "      <td>0.784641</td>\n",
       "      <td>0.855185</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          a         b         c  bools\n",
       "0  0.794435  0.963006  0.283197  False\n",
       "5  0.175726  0.530576  0.949796  False\n",
       "8  0.524196  0.793120  0.674460  False\n",
       "9  0.406132  0.784641  0.855185  False"
      ]
     },
     "execution_count": 121,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.query('not bools')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "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>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>bools</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      a     b     c  bools\n",
       "0  True  True  True   True\n",
       "5  True  True  True   True\n",
       "8  True  True  True   True\n",
       "9  True  True  True   True"
      ]
     },
     "execution_count": 122,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.query('not bools') == df[~df.bools]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Duplicate data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you want to identify and remove duplicate rows in a DataFrame, there are two methods that will help: duplicated and drop_duplicates. Each takes as an argument the columns to use to identify duplicated rows.\n",
    "\n",
    "- duplicated returns a boolean vector whose length is the number of rows, and which indicates whether a row is duplicated.\n",
    "- drop_duplicates removes duplicate rows.\n",
    "By default, the first observed row of a duplicate set is considered unique, but each method has a keep parameter to specify targets to be kept.\n",
    "\n",
    "- keep='first' (default): mark / drop duplicates except for the first occurrence.\n",
    "- keep='last': mark / drop duplicates except for the last occurrence.\n",
    "- keep=False: mark / drop all duplicates."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = pd.DataFrame({'a': ['one', 'one', 'two', 'two', 'two', 'three', 'four'],\n",
    "                    'b': ['x', 'y', 'x', 'y', 'x', 'x', 'x'],\n",
    "                    'c': np.random.randn(7)})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "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>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>one</td>\n",
       "      <td>x</td>\n",
       "      <td>-0.450025</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>one</td>\n",
       "      <td>y</td>\n",
       "      <td>1.057947</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>two</td>\n",
       "      <td>x</td>\n",
       "      <td>0.167764</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>two</td>\n",
       "      <td>y</td>\n",
       "      <td>-1.699190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>two</td>\n",
       "      <td>x</td>\n",
       "      <td>0.059692</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>three</td>\n",
       "      <td>x</td>\n",
       "      <td>0.289795</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>four</td>\n",
       "      <td>x</td>\n",
       "      <td>0.044878</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       a  b         c\n",
       "0    one  x -0.450025\n",
       "1    one  y  1.057947\n",
       "2    two  x  0.167764\n",
       "3    two  y -1.699190\n",
       "4    two  x  0.059692\n",
       "5  three  x  0.289795\n",
       "6   four  x  0.044878"
      ]
     },
     "execution_count": 124,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    False\n",
       "1     True\n",
       "2    False\n",
       "3     True\n",
       "4     True\n",
       "5    False\n",
       "6    False\n",
       "dtype: bool"
      ]
     },
     "execution_count": 125,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.duplicated('a')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     True\n",
       "1    False\n",
       "2     True\n",
       "3     True\n",
       "4    False\n",
       "5    False\n",
       "6    False\n",
       "dtype: bool"
      ]
     },
     "execution_count": 126,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.duplicated('a', keep='last')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     True\n",
       "1     True\n",
       "2     True\n",
       "3     True\n",
       "4     True\n",
       "5    False\n",
       "6    False\n",
       "dtype: bool"
      ]
     },
     "execution_count": 127,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.duplicated('a', keep=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "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>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>one</td>\n",
       "      <td>x</td>\n",
       "      <td>-0.450025</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>two</td>\n",
       "      <td>x</td>\n",
       "      <td>0.167764</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>three</td>\n",
       "      <td>x</td>\n",
       "      <td>0.289795</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>four</td>\n",
       "      <td>x</td>\n",
       "      <td>0.044878</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       a  b         c\n",
       "0    one  x -0.450025\n",
       "2    two  x  0.167764\n",
       "5  three  x  0.289795\n",
       "6   four  x  0.044878"
      ]
     },
     "execution_count": 128,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.drop_duplicates('a')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "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>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>three</td>\n",
       "      <td>x</td>\n",
       "      <td>0.289795</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>four</td>\n",
       "      <td>x</td>\n",
       "      <td>0.044878</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       a  b         c\n",
       "5  three  x  0.289795\n",
       "6   four  x  0.044878"
      ]
     },
     "execution_count": 129,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.drop_duplicates('a', keep=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Also, you can pass a list of columns to identify duplications."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    False\n",
       "1    False\n",
       "2    False\n",
       "3    False\n",
       "4     True\n",
       "5    False\n",
       "6    False\n",
       "dtype: bool"
      ]
     },
     "execution_count": 130,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.duplicated(['a', 'b'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "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>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>one</td>\n",
       "      <td>x</td>\n",
       "      <td>-0.450025</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>one</td>\n",
       "      <td>y</td>\n",
       "      <td>1.057947</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>two</td>\n",
       "      <td>x</td>\n",
       "      <td>0.167764</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>two</td>\n",
       "      <td>y</td>\n",
       "      <td>-1.699190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>three</td>\n",
       "      <td>x</td>\n",
       "      <td>0.289795</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>four</td>\n",
       "      <td>x</td>\n",
       "      <td>0.044878</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       a  b         c\n",
       "0    one  x -0.450025\n",
       "1    one  y  1.057947\n",
       "2    two  x  0.167764\n",
       "3    two  y -1.699190\n",
       "5  three  x  0.289795\n",
       "6   four  x  0.044878"
      ]
     },
     "execution_count": 131,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.drop_duplicates(['a', 'b'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To drop duplicates by index value, use Index.duplicated then perform slicing. The same set of options are available for the keep parameter."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "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>a</th>\n",
       "      <th>b</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>a</td>\n",
       "      <td>0</td>\n",
       "      <td>-1.190302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>a</td>\n",
       "      <td>1</td>\n",
       "      <td>-1.579655</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>b</td>\n",
       "      <td>2</td>\n",
       "      <td>0.903773</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>c</td>\n",
       "      <td>3</td>\n",
       "      <td>1.316083</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>b</td>\n",
       "      <td>4</td>\n",
       "      <td>2.124853</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>a</td>\n",
       "      <td>5</td>\n",
       "      <td>-0.142256</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   a         b\n",
       "a  0 -1.190302\n",
       "a  1 -1.579655\n",
       "b  2  0.903773\n",
       "c  3  1.316083\n",
       "b  4  2.124853\n",
       "a  5 -0.142256"
      ]
     },
     "execution_count": 132,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3 = pd.DataFrame({'a': np.arange(6),\n",
    "                    'b': np.random.randn(6)},\n",
    "                    index=['a', 'a', 'b', 'c', 'b', 'a'])\n",
    "df3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([False,  True, False, False,  True,  True])"
      ]
     },
     "execution_count": 133,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.index.duplicated()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "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>a</th>\n",
       "      <th>b</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>a</td>\n",
       "      <td>0</td>\n",
       "      <td>-1.190302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>b</td>\n",
       "      <td>2</td>\n",
       "      <td>0.903773</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>c</td>\n",
       "      <td>3</td>\n",
       "      <td>1.316083</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   a         b\n",
       "a  0 -1.190302\n",
       "b  2  0.903773\n",
       "c  3  1.316083"
      ]
     },
     "execution_count": 134,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3[~df3.index.duplicated()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "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>a</th>\n",
       "      <th>b</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>c</td>\n",
       "      <td>3</td>\n",
       "      <td>1.316083</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>b</td>\n",
       "      <td>4</td>\n",
       "      <td>2.124853</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>a</td>\n",
       "      <td>5</td>\n",
       "      <td>-0.142256</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   a         b\n",
       "c  3  1.316083\n",
       "b  4  2.124853\n",
       "a  5 -0.142256"
      ]
     },
     "execution_count": 135,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3[~df3.index.duplicated(keep='last')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "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>a</th>\n",
       "      <th>b</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>c</td>\n",
       "      <td>3</td>\n",
       "      <td>1.316083</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   a         b\n",
       "c  3  1.316083"
      ]
     },
     "execution_count": 136,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3[~df3.index.duplicated(keep=False)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Dictionary-like get() method"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "metadata": {},
   "outputs": [],
   "source": [
    "s = pd.Series([1, 2, 3], index=['a', 'b', 'c'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 138,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.get('a')  # equivalent to s['a']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "-1"
      ]
     },
     "execution_count": 139,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.get('x', default=-1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### The lookup() method"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Sometimes you want to extract a set of values given a sequence of row labels and column labels, and the lookup method allows for this and returns a NumPy array. For instance:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "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>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>0.054165</td>\n",
       "      <td>0.039686</td>\n",
       "      <td>0.463959</td>\n",
       "      <td>0.897753</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>0.987206</td>\n",
       "      <td>0.408886</td>\n",
       "      <td>0.368289</td>\n",
       "      <td>0.467334</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>0.327182</td>\n",
       "      <td>0.288902</td>\n",
       "      <td>0.978694</td>\n",
       "      <td>0.206721</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>0.835074</td>\n",
       "      <td>0.042289</td>\n",
       "      <td>0.397429</td>\n",
       "      <td>0.158334</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>0.472961</td>\n",
       "      <td>0.046405</td>\n",
       "      <td>0.835337</td>\n",
       "      <td>0.871208</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>0.473969</td>\n",
       "      <td>0.021123</td>\n",
       "      <td>0.727689</td>\n",
       "      <td>0.839052</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>0.664039</td>\n",
       "      <td>0.252106</td>\n",
       "      <td>0.786200</td>\n",
       "      <td>0.272490</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>0.874370</td>\n",
       "      <td>0.582257</td>\n",
       "      <td>0.953597</td>\n",
       "      <td>0.386553</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>0.807368</td>\n",
       "      <td>0.288600</td>\n",
       "      <td>0.388318</td>\n",
       "      <td>0.211070</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>0.172337</td>\n",
       "      <td>0.538019</td>\n",
       "      <td>0.915884</td>\n",
       "      <td>0.204608</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>0.072455</td>\n",
       "      <td>0.173496</td>\n",
       "      <td>0.456774</td>\n",
       "      <td>0.559019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>0.643521</td>\n",
       "      <td>0.030270</td>\n",
       "      <td>0.293021</td>\n",
       "      <td>0.385000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>12</td>\n",
       "      <td>0.105428</td>\n",
       "      <td>0.270971</td>\n",
       "      <td>0.095699</td>\n",
       "      <td>0.939616</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13</td>\n",
       "      <td>0.069470</td>\n",
       "      <td>0.781573</td>\n",
       "      <td>0.088396</td>\n",
       "      <td>0.653255</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>14</td>\n",
       "      <td>0.467156</td>\n",
       "      <td>0.861115</td>\n",
       "      <td>0.611954</td>\n",
       "      <td>0.891335</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>15</td>\n",
       "      <td>0.400601</td>\n",
       "      <td>0.424122</td>\n",
       "      <td>0.218549</td>\n",
       "      <td>0.169752</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>16</td>\n",
       "      <td>0.564146</td>\n",
       "      <td>0.229393</td>\n",
       "      <td>0.665982</td>\n",
       "      <td>0.357056</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>17</td>\n",
       "      <td>0.402930</td>\n",
       "      <td>0.557683</td>\n",
       "      <td>0.074518</td>\n",
       "      <td>0.757835</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>18</td>\n",
       "      <td>0.698153</td>\n",
       "      <td>0.728509</td>\n",
       "      <td>0.275681</td>\n",
       "      <td>0.744091</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>19</td>\n",
       "      <td>0.911889</td>\n",
       "      <td>0.514096</td>\n",
       "      <td>0.890753</td>\n",
       "      <td>0.160560</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           A         B         C         D\n",
       "0   0.054165  0.039686  0.463959  0.897753\n",
       "1   0.987206  0.408886  0.368289  0.467334\n",
       "2   0.327182  0.288902  0.978694  0.206721\n",
       "3   0.835074  0.042289  0.397429  0.158334\n",
       "4   0.472961  0.046405  0.835337  0.871208\n",
       "5   0.473969  0.021123  0.727689  0.839052\n",
       "6   0.664039  0.252106  0.786200  0.272490\n",
       "7   0.874370  0.582257  0.953597  0.386553\n",
       "8   0.807368  0.288600  0.388318  0.211070\n",
       "9   0.172337  0.538019  0.915884  0.204608\n",
       "10  0.072455  0.173496  0.456774  0.559019\n",
       "11  0.643521  0.030270  0.293021  0.385000\n",
       "12  0.105428  0.270971  0.095699  0.939616\n",
       "13  0.069470  0.781573  0.088396  0.653255\n",
       "14  0.467156  0.861115  0.611954  0.891335\n",
       "15  0.400601  0.424122  0.218549  0.169752\n",
       "16  0.564146  0.229393  0.665982  0.357056\n",
       "17  0.402930  0.557683  0.074518  0.757835\n",
       "18  0.698153  0.728509  0.275681  0.744091\n",
       "19  0.911889  0.514096  0.890753  0.160560"
      ]
     },
     "execution_count": 140,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dflookup = pd.DataFrame(np.random.rand(20, 4), columns = ['A', 'B', 'C', 'D'])\n",
    "dflookup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([0.0396864 , 0.97869447, 0.47296127, 0.25210565, 0.2110704 ])"
      ]
     },
     "execution_count": 141,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dflookup.lookup(list(range(0, 10, 2)), ['B', 'C', 'A', 'B', 'D'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Index objects"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The pandas Index class and its subclasses can be viewed as implementing an ordered multiset. Duplicates are allowed. However, if you try to convert an Index object with duplicate entries into a set, an exception will be raised.\n",
    "\n",
    "Index also provides the infrastructure necessary for lookups, data alignment, and reindexing. The easiest way to create an Index directly is to pass a list or other sequence to Index:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['e', 'd', 'a', 'b'], dtype='object')"
      ]
     },
     "execution_count": 142,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "index = pd.Index(['e', 'd', 'a', 'b'])\n",
    "index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 143,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'d' in index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'something'"
      ]
     },
     "execution_count": 144,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "index = pd.Index(['e', 'd', 'a', 'b'], name='something')\n",
    "index.name"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Setting metadata"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Indexes are “mostly immutable”, but it is possible to set and change their metadata, like the index name (or, for MultiIndex, levels and codes).\n",
    "\n",
    "You can use the rename, set_names, set_levels, and set_codes to set these attributes directly. They default to returning a copy; however, you can specify inplace=True to have the data change in place.\n",
    "\n",
    "See Advanced Indexing for usage of MultiIndexes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "metadata": {},
   "outputs": [],
   "source": [
    "ind = pd.Index([1, 2, 3])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Int64Index([1, 2, 3], dtype='int64', name='apple')"
      ]
     },
     "execution_count": 146,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ind.rename(\"apple\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "metadata": {},
   "outputs": [],
   "source": [
    "ind.set_names([\"apple\"], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "metadata": {},
   "outputs": [],
   "source": [
    "ind.name = \"bob\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Int64Index([1, 2, 3], dtype='int64', name='bob')"
      ]
     },
     "execution_count": 149,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ind"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "set_names, set_levels, and set_codes also take an optional level argument"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "MultiIndex([(0, 'one'),\n",
       "            (0, 'two'),\n",
       "            (1, 'one'),\n",
       "            (1, 'two'),\n",
       "            (2, 'one'),\n",
       "            (2, 'two')],\n",
       "           names=['first', 'second'])"
      ]
     },
     "execution_count": 150,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "index = pd.MultiIndex.from_product([range(3), ['one', 'two']], names=['first', 'second'])\n",
    "index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['one', 'two'], dtype='object', name='second')"
      ]
     },
     "execution_count": 151,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "index.levels[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "MultiIndex([(0, 'a'),\n",
       "            (0, 'b'),\n",
       "            (1, 'a'),\n",
       "            (1, 'b'),\n",
       "            (2, 'a'),\n",
       "            (2, 'b')],\n",
       "           names=['first', 'second'])"
      ]
     },
     "execution_count": 152,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "index.set_levels([\"a\", \"b\"], level=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Set operations on Index objects"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The two main operations are union (|) and intersection (&). These can be directly called as instance methods or used via overloaded operators. Difference is provided via the .difference() method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['a', 'b', 'c', 'd', 'e'], dtype='object')"
      ]
     },
     "execution_count": 153,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a = pd.Index(['c', 'b', 'a'])\n",
    "b = pd.Index(['c', 'e', 'd'])\n",
    "a | b"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['c'], dtype='object')"
      ]
     },
     "execution_count": 154,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a & b"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['a', 'b'], dtype='object')"
      ]
     },
     "execution_count": 155,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a.difference(b)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Also available is the symmetric_difference (^) operation, which returns elements that appear in either idx1 or idx2, but not in both. This is equivalent to the Index created by idx1.difference(idx2).union(idx2.difference(idx1)), with duplicates dropped."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Int64Index([1, 5], dtype='int64')"
      ]
     },
     "execution_count": 156,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "idx1 = pd.Index([1, 2, 3, 4])\n",
    "idx2 = pd.Index([2, 3, 4, 5])\n",
    "idx1.symmetric_difference(idx2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Int64Index([1, 5], dtype='int64')"
      ]
     },
     "execution_count": 157,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "idx1 ^ idx2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Missing values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Index.fillna fills missing values with specified scalar value."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Float64Index([1.0, nan, 3.0, 4.0], dtype='float64')"
      ]
     },
     "execution_count": 158,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "idx1 = pd.Index([1, np.nan, 3, 4])\n",
    "idx1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Float64Index([1.0, 2.0, 3.0, 4.0], dtype='float64')"
      ]
     },
     "execution_count": 159,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "idx1.fillna(2)"
   ]
  }
 ],
 "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}