Pandas_Cookbook_107.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Gaining perspective on stock prices"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Könyv 151. oldal\n",
    "### Mivel ez részvényekről szól, inkább a StockInfo notebookba rakom a gyakorlatot.\n",
    "# Translating SQL WHERE clauses\n",
    "### Könyv 155. oldal"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pandas has the ability to connect to databases and send SQL statements to them.\n",
    "This recipe will write pandas code that is equivalent to a SQL query that selects a certain subset\n",
    "of the employee dataset."
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "SELECT\n",
    "UNIQUE_ID,\n",
    "DEPARTMENT,\n",
    "GENDER,\n",
    "BASE_SALARY\n",
    "FROM\n",
    "EMPLOYEE\n",
    "WHERE\n",
    "DEPARTMENT IN ('Houston Police Department-HPD',\n",
    "'Houston Fire Department (HFD)') AND\n",
    "GENDER = 'Female' AND\n",
    "BASE_SALARY BETWEEN 80000 AND 120000;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "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>UNIQUE_ID</th>\n",
       "      <th>POSITION_TITLE</th>\n",
       "      <th>DEPARTMENT</th>\n",
       "      <th>BASE_SALARY</th>\n",
       "      <th>RACE</th>\n",
       "      <th>EMPLOYMENT_TYPE</th>\n",
       "      <th>GENDER</th>\n",
       "      <th>EMPLOYMENT_STATUS</th>\n",
       "      <th>HIRE_DATE</th>\n",
       "      <th>JOB_DATE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>ASSISTANT DIRECTOR (EX LVL)</td>\n",
       "      <td>Municipal Courts Department</td>\n",
       "      <td>121862.0</td>\n",
       "      <td>Hispanic/Latino</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Female</td>\n",
       "      <td>Active</td>\n",
       "      <td>2006-06-12</td>\n",
       "      <td>2012-10-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>LIBRARY ASSISTANT</td>\n",
       "      <td>Library</td>\n",
       "      <td>26125.0</td>\n",
       "      <td>Hispanic/Latino</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Female</td>\n",
       "      <td>Active</td>\n",
       "      <td>2000-07-19</td>\n",
       "      <td>2010-09-18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>POLICE OFFICER</td>\n",
       "      <td>Houston Police Department-HPD</td>\n",
       "      <td>45279.0</td>\n",
       "      <td>White</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Male</td>\n",
       "      <td>Active</td>\n",
       "      <td>2015-02-03</td>\n",
       "      <td>2015-02-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>ENGINEER/OPERATOR</td>\n",
       "      <td>Houston Fire Department (HFD)</td>\n",
       "      <td>63166.0</td>\n",
       "      <td>White</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Male</td>\n",
       "      <td>Active</td>\n",
       "      <td>1982-02-08</td>\n",
       "      <td>1991-05-25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>ELECTRICIAN</td>\n",
       "      <td>General Services Department</td>\n",
       "      <td>56347.0</td>\n",
       "      <td>White</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Male</td>\n",
       "      <td>Active</td>\n",
       "      <td>1989-06-19</td>\n",
       "      <td>1994-10-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>SENIOR POLICE OFFICER</td>\n",
       "      <td>Houston Police Department-HPD</td>\n",
       "      <td>66614.0</td>\n",
       "      <td>Black or African American</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Male</td>\n",
       "      <td>Active</td>\n",
       "      <td>1984-11-26</td>\n",
       "      <td>2005-03-26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>ENGINEER</td>\n",
       "      <td>Public Works &amp; Engineering-PWE</td>\n",
       "      <td>71680.0</td>\n",
       "      <td>Asian/Pacific Islander</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Male</td>\n",
       "      <td>Active</td>\n",
       "      <td>2012-03-26</td>\n",
       "      <td>2012-03-26</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   UNIQUE_ID               POSITION_TITLE                      DEPARTMENT  \\\n",
       "0          0  ASSISTANT DIRECTOR (EX LVL)     Municipal Courts Department   \n",
       "1          1            LIBRARY ASSISTANT                         Library   \n",
       "2          2               POLICE OFFICER   Houston Police Department-HPD   \n",
       "3          3            ENGINEER/OPERATOR   Houston Fire Department (HFD)   \n",
       "4          4                  ELECTRICIAN     General Services Department   \n",
       "5          5        SENIOR POLICE OFFICER   Houston Police Department-HPD   \n",
       "6          6                     ENGINEER  Public Works & Engineering-PWE   \n",
       "\n",
       "   BASE_SALARY                       RACE EMPLOYMENT_TYPE  GENDER  \\\n",
       "0     121862.0            Hispanic/Latino       Full Time  Female   \n",
       "1      26125.0            Hispanic/Latino       Full Time  Female   \n",
       "2      45279.0                      White       Full Time    Male   \n",
       "3      63166.0                      White       Full Time    Male   \n",
       "4      56347.0                      White       Full Time    Male   \n",
       "5      66614.0  Black or African American       Full Time    Male   \n",
       "6      71680.0     Asian/Pacific Islander       Full Time    Male   \n",
       "\n",
       "  EMPLOYMENT_STATUS   HIRE_DATE    JOB_DATE  \n",
       "0            Active  2006-06-12  2012-10-13  \n",
       "1            Active  2000-07-19  2010-09-18  \n",
       "2            Active  2015-02-03  2015-02-03  \n",
       "3            Active  1982-02-08  1991-05-25  \n",
       "4            Active  1989-06-19  1994-10-22  \n",
       "5            Active  1984-11-26  2005-03-26  \n",
       "6            Active  2012-03-26  2012-03-26  "
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "employee = pd.read_csv('https://raw.githubusercontent.com/DatasRev/source-files/master/csv/employee.csv')\n",
    "employee.head(7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2000, 10)"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "employee.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2000 entries, 0 to 1999\n",
      "Data columns (total 10 columns):\n",
      "UNIQUE_ID            2000 non-null int64\n",
      "POSITION_TITLE       2000 non-null object\n",
      "DEPARTMENT           2000 non-null object\n",
      "BASE_SALARY          1886 non-null float64\n",
      "RACE                 1965 non-null object\n",
      "EMPLOYMENT_TYPE      2000 non-null object\n",
      "GENDER               2000 non-null object\n",
      "EMPLOYMENT_STATUS    2000 non-null object\n",
      "HIRE_DATE            2000 non-null object\n",
      "JOB_DATE             1997 non-null object\n",
      "dtypes: float64(1), int64(1), object(8)\n",
      "memory usage: 156.3+ KB\n"
     ]
    }
   ],
   "source": [
    "employee.info()"
   ]
  },
  {
   "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>UNIQUE_ID</th>\n",
       "      <th>BASE_SALARY</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>2000.000000</td>\n",
       "      <td>1886.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>999.500000</td>\n",
       "      <td>55767.931601</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>577.494589</td>\n",
       "      <td>21693.706679</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>24960.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>499.750000</td>\n",
       "      <td>40170.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>999.500000</td>\n",
       "      <td>54461.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>1499.250000</td>\n",
       "      <td>66614.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>1999.000000</td>\n",
       "      <td>275000.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         UNIQUE_ID    BASE_SALARY\n",
       "count  2000.000000    1886.000000\n",
       "mean    999.500000   55767.931601\n",
       "std     577.494589   21693.706679\n",
       "min       0.000000   24960.000000\n",
       "25%     499.750000   40170.000000\n",
       "50%     999.500000   54461.000000\n",
       "75%    1499.250000   66614.000000\n",
       "max    1999.000000  275000.000000"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "employee.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Houston Police Department-HPD     638\n",
       "Houston Fire Department (HFD)     384\n",
       "Public Works & Engineering-PWE    343\n",
       "Health & Human Services           110\n",
       "Houston Airport System (HAS)      106\n",
       "Name: DEPARTMENT, dtype: int64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "employee.DEPARTMENT.value_counts().head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Male      1397\n",
       "Female     603\n",
       "Name: GENDER, dtype: int64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "employee.GENDER.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count      1886\n",
       "mean      55767\n",
       "std       21693\n",
       "min       24960\n",
       "25%       40170\n",
       "50%       54461\n",
       "75%       66614\n",
       "max      275000\n",
       "Name: BASE_SALARY, dtype: int32"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "employee.BASE_SALARY.describe().astype(int)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Write a single statement for each of the criteria. Use the isin method to test\n",
    "equality to one of many values:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The isin Series method is equivalent to the SQL IN operator and accepts a list of all\n",
    "possible values that you would like to keep. It is possible to use a series of OR conditions to\n",
    "replicate this expression but it would not be as efficient or idiomatic."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "depts = ['Houston Police Department-HPD',\n",
    "'Houston Fire Department (HFD)']\n",
    "criteria_dept = employee.DEPARTMENT.isin(depts)\n",
    "criteria_gender = employee.GENDER == 'Female'\n",
    "criteria_sal = (employee.BASE_SALARY >= 80000) & \\\n",
    "(employee.BASE_SALARY <= 120000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "criteria_final = (criteria_dept & criteria_gender & criteria_sal)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>UNIQUE_ID</th>\n",
       "      <th>DEPARTMENT</th>\n",
       "      <th>GENDER</th>\n",
       "      <th>BASE_SALARY</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>61</td>\n",
       "      <td>Houston Fire Department (HFD)</td>\n",
       "      <td>Female</td>\n",
       "      <td>96668.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136</th>\n",
       "      <td>136</td>\n",
       "      <td>Houston Police Department-HPD</td>\n",
       "      <td>Female</td>\n",
       "      <td>81239.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>367</th>\n",
       "      <td>367</td>\n",
       "      <td>Houston Police Department-HPD</td>\n",
       "      <td>Female</td>\n",
       "      <td>86534.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>474</th>\n",
       "      <td>474</td>\n",
       "      <td>Houston Police Department-HPD</td>\n",
       "      <td>Female</td>\n",
       "      <td>91181.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>513</th>\n",
       "      <td>513</td>\n",
       "      <td>Houston Police Department-HPD</td>\n",
       "      <td>Female</td>\n",
       "      <td>81239.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     UNIQUE_ID                     DEPARTMENT  GENDER  BASE_SALARY\n",
       "61          61  Houston Fire Department (HFD)  Female      96668.0\n",
       "136        136  Houston Police Department-HPD  Female      81239.0\n",
       "367        367  Houston Police Department-HPD  Female      86534.0\n",
       "474        474  Houston Police Department-HPD  Female      91181.0\n",
       "513        513  Houston Police Department-HPD  Female      81239.0"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Use boolean indexing to select only the rows that meet the final criteria:\n",
    "select_columns = ['UNIQUE_ID', 'DEPARTMENT', 'GENDER', 'BASE_SALARY']\n",
    "employee.loc[criteria_final, select_columns].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There's more...\n",
    "For many operations, pandas has multiple ways to do the same thing. In the preceding\n",
    "recipe, the criteria for salary uses two separate boolean expressions. Similarly to SQL, Series\n",
    "have a between method, with the salary criteria equivalently written as follows:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "criteria_sal = employee.BASE_SALARY.between(80000, 120000)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Another useful application of isin is to provide a sequence of values automatically\n",
    "generated by some other pandas statements. This would avoid any manual investigating to\n",
    "find the exact string names to store in a list. Conversely, let's try to exclude the rows from\n",
    "the top five most frequently occurring departments:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>UNIQUE_ID</th>\n",
       "      <th>POSITION_TITLE</th>\n",
       "      <th>DEPARTMENT</th>\n",
       "      <th>BASE_SALARY</th>\n",
       "      <th>RACE</th>\n",
       "      <th>EMPLOYMENT_TYPE</th>\n",
       "      <th>GENDER</th>\n",
       "      <th>EMPLOYMENT_STATUS</th>\n",
       "      <th>HIRE_DATE</th>\n",
       "      <th>JOB_DATE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>ASSISTANT DIRECTOR (EX LVL)</td>\n",
       "      <td>Municipal Courts Department</td>\n",
       "      <td>121862.0</td>\n",
       "      <td>Hispanic/Latino</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Female</td>\n",
       "      <td>Active</td>\n",
       "      <td>2006-06-12</td>\n",
       "      <td>2012-10-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>LIBRARY ASSISTANT</td>\n",
       "      <td>Library</td>\n",
       "      <td>26125.0</td>\n",
       "      <td>Hispanic/Latino</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Female</td>\n",
       "      <td>Active</td>\n",
       "      <td>2000-07-19</td>\n",
       "      <td>2010-09-18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>ELECTRICIAN</td>\n",
       "      <td>General Services Department</td>\n",
       "      <td>56347.0</td>\n",
       "      <td>White</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Male</td>\n",
       "      <td>Active</td>\n",
       "      <td>1989-06-19</td>\n",
       "      <td>1994-10-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>18</td>\n",
       "      <td>MAINTENANCE MECHANIC III</td>\n",
       "      <td>General Services Department</td>\n",
       "      <td>40581.0</td>\n",
       "      <td>Hispanic/Latino</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Male</td>\n",
       "      <td>Active</td>\n",
       "      <td>2008-12-29</td>\n",
       "      <td>2008-12-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>32</td>\n",
       "      <td>SENIOR ACCOUNTANT</td>\n",
       "      <td>Finance</td>\n",
       "      <td>46963.0</td>\n",
       "      <td>Black or African American</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Male</td>\n",
       "      <td>Active</td>\n",
       "      <td>1991-02-11</td>\n",
       "      <td>2016-02-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>37</td>\n",
       "      <td>ASSISTANT SHOP MANAGER</td>\n",
       "      <td>Fleet Management Department</td>\n",
       "      <td>51438.0</td>\n",
       "      <td>White</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Male</td>\n",
       "      <td>Active</td>\n",
       "      <td>1995-11-30</td>\n",
       "      <td>2016-03-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>57</td>\n",
       "      <td>SEMI-SKILLED LABORER</td>\n",
       "      <td>Solid Waste Management</td>\n",
       "      <td>27622.0</td>\n",
       "      <td>Black or African American</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Male</td>\n",
       "      <td>Active</td>\n",
       "      <td>2015-08-31</td>\n",
       "      <td>2015-08-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>71</td>\n",
       "      <td>COUNCIL INTERN (EXECUTIVE LEVEL)</td>\n",
       "      <td>City Council</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Hispanic/Latino</td>\n",
       "      <td>Part Time 30</td>\n",
       "      <td>Male</td>\n",
       "      <td>Active</td>\n",
       "      <td>2015-06-08</td>\n",
       "      <td>2016-02-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>73</td>\n",
       "      <td>ADMINISTRATIVE SPECIALIST</td>\n",
       "      <td>Human Resources Dept.</td>\n",
       "      <td>55939.0</td>\n",
       "      <td>Black or African American</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Female</td>\n",
       "      <td>Active</td>\n",
       "      <td>2011-12-19</td>\n",
       "      <td>2013-11-23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>74</td>\n",
       "      <td>SENIOR SIDELOADER OPERATOR</td>\n",
       "      <td>Solid Waste Management</td>\n",
       "      <td>38459.0</td>\n",
       "      <td>Black or African American</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Male</td>\n",
       "      <td>Active</td>\n",
       "      <td>2004-09-07</td>\n",
       "      <td>2005-07-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>80</td>\n",
       "      <td>ADMINISTRATIVE SPECIALIST</td>\n",
       "      <td>Fleet Management Department</td>\n",
       "      <td>50249.0</td>\n",
       "      <td>Black or African American</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Female</td>\n",
       "      <td>Active</td>\n",
       "      <td>2007-04-30</td>\n",
       "      <td>2012-09-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>87</td>\n",
       "      <td>SENIOR CONTRACT ADMINISTRATOR</td>\n",
       "      <td>Municipal Courts Department</td>\n",
       "      <td>79143.0</td>\n",
       "      <td>Black or African American</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Male</td>\n",
       "      <td>Active</td>\n",
       "      <td>2009-09-08</td>\n",
       "      <td>2009-09-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>90</td>\n",
       "      <td>9-1-1 TELECOMMUNICATOR</td>\n",
       "      <td>Houston Emergency Center (HEC)</td>\n",
       "      <td>32240.0</td>\n",
       "      <td>Black or African American</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Male</td>\n",
       "      <td>Active</td>\n",
       "      <td>2006-06-26</td>\n",
       "      <td>2006-06-26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>92</td>\n",
       "      <td>CUSTODIAN</td>\n",
       "      <td>Parks &amp; Recreation</td>\n",
       "      <td>26125.0</td>\n",
       "      <td>Black or African American</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Female</td>\n",
       "      <td>Active</td>\n",
       "      <td>1993-10-02</td>\n",
       "      <td>1993-10-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>96</td>\n",
       "      <td>ASSISTANT CITY CONTROLLER III</td>\n",
       "      <td>City Controller's Office</td>\n",
       "      <td>59077.0</td>\n",
       "      <td>Asian/Pacific Islander</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Female</td>\n",
       "      <td>Active</td>\n",
       "      <td>2013-06-10</td>\n",
       "      <td>2013-06-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>101</td>\n",
       "      <td>CUSTOMER SERVICE CLERK</td>\n",
       "      <td>Library</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Part Time</td>\n",
       "      <td>Female</td>\n",
       "      <td>Active</td>\n",
       "      <td>2016-02-29</td>\n",
       "      <td>2016-02-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105</th>\n",
       "      <td>105</td>\n",
       "      <td>ASSISTANT SHOP MANAGER</td>\n",
       "      <td>Fleet Management Department</td>\n",
       "      <td>56971.0</td>\n",
       "      <td>Hispanic/Latino</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Male</td>\n",
       "      <td>Active</td>\n",
       "      <td>2004-08-09</td>\n",
       "      <td>2013-10-26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>117</th>\n",
       "      <td>117</td>\n",
       "      <td>SENIOR ASSISTANT CITY ATTORNEY I</td>\n",
       "      <td>Legal Department</td>\n",
       "      <td>90957.0</td>\n",
       "      <td>Black or African American</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Female</td>\n",
       "      <td>Active</td>\n",
       "      <td>1998-03-20</td>\n",
       "      <td>2012-07-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <td>124</td>\n",
       "      <td>SOLID WASTE SUPERVISOR</td>\n",
       "      <td>Solid Waste Management</td>\n",
       "      <td>61428.0</td>\n",
       "      <td>Black or African American</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Male</td>\n",
       "      <td>Active</td>\n",
       "      <td>1990-09-18</td>\n",
       "      <td>2004-12-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>131</th>\n",
       "      <td>131</td>\n",
       "      <td>RECREATION SPECIALIST</td>\n",
       "      <td>Parks &amp; Recreation</td>\n",
       "      <td>33592.0</td>\n",
       "      <td>Black or African American</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Male</td>\n",
       "      <td>Active</td>\n",
       "      <td>2013-06-22</td>\n",
       "      <td>2013-06-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>133</th>\n",
       "      <td>133</td>\n",
       "      <td>SENIOR PROJECT MANAGER</td>\n",
       "      <td>General Services Department</td>\n",
       "      <td>88341.0</td>\n",
       "      <td>Hispanic/Latino</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Male</td>\n",
       "      <td>Active</td>\n",
       "      <td>2014-02-03</td>\n",
       "      <td>2014-02-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146</th>\n",
       "      <td>146</td>\n",
       "      <td>SENIOR STAFF ANALYST</td>\n",
       "      <td>Houston Information Tech Svcs</td>\n",
       "      <td>74951.0</td>\n",
       "      <td>White</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Female</td>\n",
       "      <td>Active</td>\n",
       "      <td>2014-03-17</td>\n",
       "      <td>2014-03-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149</th>\n",
       "      <td>149</td>\n",
       "      <td>ADMINISTRATIVE ASSISTANT</td>\n",
       "      <td>City Controller's Office</td>\n",
       "      <td>64251.0</td>\n",
       "      <td>Hispanic/Latino</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Female</td>\n",
       "      <td>Active</td>\n",
       "      <td>1990-12-11</td>\n",
       "      <td>2010-05-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157</th>\n",
       "      <td>157</td>\n",
       "      <td>RECREATION SPECIALIST</td>\n",
       "      <td>Parks &amp; Recreation</td>\n",
       "      <td>30368.0</td>\n",
       "      <td>Black or African American</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Male</td>\n",
       "      <td>Active</td>\n",
       "      <td>2007-10-22</td>\n",
       "      <td>2007-10-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>163</th>\n",
       "      <td>163</td>\n",
       "      <td>EQUIPMENT WORKER</td>\n",
       "      <td>Parks &amp; Recreation</td>\n",
       "      <td>29307.0</td>\n",
       "      <td>Black or African American</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Male</td>\n",
       "      <td>Active</td>\n",
       "      <td>2016-01-11</td>\n",
       "      <td>2016-01-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166</th>\n",
       "      <td>166</td>\n",
       "      <td>ADMINISTRATIVE ASSISTANT</td>\n",
       "      <td>Housing and Community Devp.</td>\n",
       "      <td>39666.0</td>\n",
       "      <td>Black or African American</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Male</td>\n",
       "      <td>Active</td>\n",
       "      <td>2007-04-09</td>\n",
       "      <td>2013-07-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>170</th>\n",
       "      <td>170</td>\n",
       "      <td>EQUIPMENT WORKER</td>\n",
       "      <td>Parks &amp; Recreation</td>\n",
       "      <td>30347.0</td>\n",
       "      <td>Hispanic/Latino</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Male</td>\n",
       "      <td>Active</td>\n",
       "      <td>2004-09-20</td>\n",
       "      <td>2004-09-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>171</th>\n",
       "      <td>171</td>\n",
       "      <td>ADMINISTRATION MANAGER</td>\n",
       "      <td>Human Resources Dept.</td>\n",
       "      <td>80790.0</td>\n",
       "      <td>White</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Male</td>\n",
       "      <td>Active</td>\n",
       "      <td>2015-01-12</td>\n",
       "      <td>2015-01-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>176</th>\n",
       "      <td>176</td>\n",
       "      <td>ADMINISTRATIVE ASSOCIATE</td>\n",
       "      <td>Dept of Neighborhoods (DON)</td>\n",
       "      <td>32386.0</td>\n",
       "      <td>Black or African American</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Female</td>\n",
       "      <td>Active</td>\n",
       "      <td>1979-09-06</td>\n",
       "      <td>2011-09-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>197</th>\n",
       "      <td>197</td>\n",
       "      <td>ADMINISTRATIVE COORDINATOR</td>\n",
       "      <td>Houston Emergency Center (HEC)</td>\n",
       "      <td>84456.0</td>\n",
       "      <td>Black or African American</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Female</td>\n",
       "      <td>Active</td>\n",
       "      <td>1987-12-07</td>\n",
       "      <td>2013-12-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1846</th>\n",
       "      <td>1846</td>\n",
       "      <td>LIBRARY ASSISTANT</td>\n",
       "      <td>Library</td>\n",
       "      <td>26125.0</td>\n",
       "      <td>American Indian or Alaskan Native</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Female</td>\n",
       "      <td>Active</td>\n",
       "      <td>2008-05-05</td>\n",
       "      <td>2013-01-19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1847</th>\n",
       "      <td>1847</td>\n",
       "      <td>PLANNER I</td>\n",
       "      <td>Planning &amp; Development</td>\n",
       "      <td>50378.0</td>\n",
       "      <td>Hispanic/Latino</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Male</td>\n",
       "      <td>Active</td>\n",
       "      <td>2013-10-28</td>\n",
       "      <td>2016-04-23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1850</th>\n",
       "      <td>1850</td>\n",
       "      <td>FINANCIAL ANALYST IV</td>\n",
       "      <td>Finance</td>\n",
       "      <td>81458.0</td>\n",
       "      <td>Black or African American</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Female</td>\n",
       "      <td>Active</td>\n",
       "      <td>1999-06-14</td>\n",
       "      <td>2014-03-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1853</th>\n",
       "      <td>1853</td>\n",
       "      <td>MUNICIPAL COURTS SECURITY OFFICER</td>\n",
       "      <td>Municipal Courts Department</td>\n",
       "      <td>34320.0</td>\n",
       "      <td>Black or African American</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Male</td>\n",
       "      <td>Active</td>\n",
       "      <td>2015-03-02</td>\n",
       "      <td>2015-03-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1865</th>\n",
       "      <td>1865</td>\n",
       "      <td>CUSTOMER SERVICE REPRESENTATIVE I</td>\n",
       "      <td>Municipal Courts Department</td>\n",
       "      <td>37128.0</td>\n",
       "      <td>Black or African American</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Female</td>\n",
       "      <td>Active</td>\n",
       "      <td>1988-09-10</td>\n",
       "      <td>2002-09-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1866</th>\n",
       "      <td>1866</td>\n",
       "      <td>RECREATION ASSISTANT</td>\n",
       "      <td>Parks &amp; Recreation</td>\n",
       "      <td>NaN</td>\n",
       "      <td>White</td>\n",
       "      <td>Part Time</td>\n",
       "      <td>Female</td>\n",
       "      <td>Active</td>\n",
       "      <td>2013-04-15</td>\n",
       "      <td>2013-04-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1867</th>\n",
       "      <td>1867</td>\n",
       "      <td>MECHANIC III</td>\n",
       "      <td>Fleet Management Department</td>\n",
       "      <td>44158.0</td>\n",
       "      <td>White</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Male</td>\n",
       "      <td>Active</td>\n",
       "      <td>2008-12-27</td>\n",
       "      <td>2013-08-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1868</th>\n",
       "      <td>1868</td>\n",
       "      <td>STAFF ANALYST</td>\n",
       "      <td>Admn. &amp; Regulatory Affairs</td>\n",
       "      <td>65000.0</td>\n",
       "      <td>Black or African American</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Female</td>\n",
       "      <td>Active</td>\n",
       "      <td>1993-08-13</td>\n",
       "      <td>2016-01-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1886</th>\n",
       "      <td>1886</td>\n",
       "      <td>SENIOR SIDELOADER OPERATOR</td>\n",
       "      <td>Solid Waste Management</td>\n",
       "      <td>35630.0</td>\n",
       "      <td>Black or African American</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Male</td>\n",
       "      <td>Active</td>\n",
       "      <td>2014-11-24</td>\n",
       "      <td>2014-11-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1887</th>\n",
       "      <td>1887</td>\n",
       "      <td>SENIOR HUMAN RESOURCES GENERALIST</td>\n",
       "      <td>Human Resources Dept.</td>\n",
       "      <td>51532.0</td>\n",
       "      <td>Black or African American</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Female</td>\n",
       "      <td>Active</td>\n",
       "      <td>2014-10-06</td>\n",
       "      <td>2014-10-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1893</th>\n",
       "      <td>1893</td>\n",
       "      <td>EQUIPMENT WORKER</td>\n",
       "      <td>Parks &amp; Recreation</td>\n",
       "      <td>29307.0</td>\n",
       "      <td>White</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Male</td>\n",
       "      <td>Active</td>\n",
       "      <td>2014-10-13</td>\n",
       "      <td>2016-01-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1894</th>\n",
       "      <td>1894</td>\n",
       "      <td>MECHANIC III</td>\n",
       "      <td>Fleet Management Department</td>\n",
       "      <td>44158.0</td>\n",
       "      <td>White</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Male</td>\n",
       "      <td>Active</td>\n",
       "      <td>2004-03-29</td>\n",
       "      <td>2012-09-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1896</th>\n",
       "      <td>1896</td>\n",
       "      <td>DATA BASE ANALYST</td>\n",
       "      <td>Library</td>\n",
       "      <td>59371.0</td>\n",
       "      <td>Hispanic/Latino</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Male</td>\n",
       "      <td>Active</td>\n",
       "      <td>2005-08-06</td>\n",
       "      <td>2013-10-26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1901</th>\n",
       "      <td>1901</td>\n",
       "      <td>MECHANIC III</td>\n",
       "      <td>Fleet Management Department</td>\n",
       "      <td>46426.0</td>\n",
       "      <td>Hispanic/Latino</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Male</td>\n",
       "      <td>Active</td>\n",
       "      <td>2002-12-02</td>\n",
       "      <td>2005-12-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1903</th>\n",
       "      <td>1903</td>\n",
       "      <td>ASSISTANT SUPERINTENDENT</td>\n",
       "      <td>Parks &amp; Recreation</td>\n",
       "      <td>47466.0</td>\n",
       "      <td>Hispanic/Latino</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Male</td>\n",
       "      <td>Active</td>\n",
       "      <td>1994-03-03</td>\n",
       "      <td>2011-08-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1907</th>\n",
       "      <td>1907</td>\n",
       "      <td>CUSTOMER SERVICE REPRESENTATIVE II</td>\n",
       "      <td>Municipal Courts Department</td>\n",
       "      <td>41621.0</td>\n",
       "      <td>Black or African American</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Female</td>\n",
       "      <td>Active</td>\n",
       "      <td>2004-08-02</td>\n",
       "      <td>2013-03-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1912</th>\n",
       "      <td>1912</td>\n",
       "      <td>SENIOR HUMAN RESOURCES GENERALIST</td>\n",
       "      <td>Human Resources Dept.</td>\n",
       "      <td>54938.0</td>\n",
       "      <td>Black or African American</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Female</td>\n",
       "      <td>Active</td>\n",
       "      <td>2012-07-30</td>\n",
       "      <td>2012-07-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1926</th>\n",
       "      <td>1926</td>\n",
       "      <td>LIBRARIAN I</td>\n",
       "      <td>Library</td>\n",
       "      <td>39874.0</td>\n",
       "      <td>White</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Female</td>\n",
       "      <td>Active</td>\n",
       "      <td>2009-02-11</td>\n",
       "      <td>2014-10-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1933</th>\n",
       "      <td>1933</td>\n",
       "      <td>HUMAN RESOURCES MANAGER</td>\n",
       "      <td>Human Resources Dept.</td>\n",
       "      <td>74435.0</td>\n",
       "      <td>Black or African American</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Female</td>\n",
       "      <td>Active</td>\n",
       "      <td>2004-10-11</td>\n",
       "      <td>2015-05-23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1946</th>\n",
       "      <td>1946</td>\n",
       "      <td>SENIOR ASSISTANT CITY ATTORNEY II</td>\n",
       "      <td>Legal Department</td>\n",
       "      <td>117831.0</td>\n",
       "      <td>Asian/Pacific Islander</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Female</td>\n",
       "      <td>Active</td>\n",
       "      <td>2005-07-18</td>\n",
       "      <td>2012-07-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1948</th>\n",
       "      <td>1948</td>\n",
       "      <td>ADMINISTRATIVE ASSISTANT</td>\n",
       "      <td>Parks &amp; Recreation</td>\n",
       "      <td>41330.0</td>\n",
       "      <td>Hispanic/Latino</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Female</td>\n",
       "      <td>Active</td>\n",
       "      <td>1988-10-25</td>\n",
       "      <td>2005-04-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1952</th>\n",
       "      <td>1952</td>\n",
       "      <td>9-1-1 TELECOMMUNICATOR</td>\n",
       "      <td>Houston Emergency Center (HEC)</td>\n",
       "      <td>39104.0</td>\n",
       "      <td>Black or African American</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Female</td>\n",
       "      <td>Active</td>\n",
       "      <td>2005-05-31</td>\n",
       "      <td>2005-05-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1963</th>\n",
       "      <td>1963</td>\n",
       "      <td>SENIOR COMMUNITY LIAISON</td>\n",
       "      <td>Library</td>\n",
       "      <td>51542.0</td>\n",
       "      <td>Black or African American</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Female</td>\n",
       "      <td>Active</td>\n",
       "      <td>2015-11-02</td>\n",
       "      <td>2015-11-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1967</th>\n",
       "      <td>1967</td>\n",
       "      <td>STAFF ANALYST</td>\n",
       "      <td>Human Resources Dept.</td>\n",
       "      <td>65000.0</td>\n",
       "      <td>Black or African American</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Female</td>\n",
       "      <td>Active</td>\n",
       "      <td>1995-10-30</td>\n",
       "      <td>2016-02-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1968</th>\n",
       "      <td>1968</td>\n",
       "      <td>CUSTOMER SERVICE CLERK</td>\n",
       "      <td>Library</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Black or African American</td>\n",
       "      <td>Part Time</td>\n",
       "      <td>Female</td>\n",
       "      <td>Active</td>\n",
       "      <td>2013-08-05</td>\n",
       "      <td>2013-08-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1976</th>\n",
       "      <td>1976</td>\n",
       "      <td>SENIOR SIDELOADER OPERATOR</td>\n",
       "      <td>Solid Waste Management</td>\n",
       "      <td>34466.0</td>\n",
       "      <td>Hispanic/Latino</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Female</td>\n",
       "      <td>Active</td>\n",
       "      <td>2015-07-20</td>\n",
       "      <td>2016-01-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1983</th>\n",
       "      <td>1983</td>\n",
       "      <td>ADMINISTRATIVE SPECIALIST</td>\n",
       "      <td>Admn. &amp; Regulatory Affairs</td>\n",
       "      <td>55172.0</td>\n",
       "      <td>Black or African American</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Female</td>\n",
       "      <td>Active</td>\n",
       "      <td>2006-10-16</td>\n",
       "      <td>2006-10-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1985</th>\n",
       "      <td>1985</td>\n",
       "      <td>TRUCK DRIVER</td>\n",
       "      <td>Fleet Management Department</td>\n",
       "      <td>30222.0</td>\n",
       "      <td>Black or African American</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Male</td>\n",
       "      <td>Active</td>\n",
       "      <td>2013-06-10</td>\n",
       "      <td>2015-08-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1988</th>\n",
       "      <td>1988</td>\n",
       "      <td>SENIOR ASSISTANT CITY ATTORNEY II</td>\n",
       "      <td>Legal Department</td>\n",
       "      <td>124115.0</td>\n",
       "      <td>White</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Male</td>\n",
       "      <td>Active</td>\n",
       "      <td>2013-01-23</td>\n",
       "      <td>2013-03-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990</th>\n",
       "      <td>1990</td>\n",
       "      <td>BUILDING MAINTENANCE SUPERVISOR</td>\n",
       "      <td>Parks &amp; Recreation</td>\n",
       "      <td>30347.0</td>\n",
       "      <td>Black or African American</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Female</td>\n",
       "      <td>Active</td>\n",
       "      <td>1995-10-14</td>\n",
       "      <td>2010-03-20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>419 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      UNIQUE_ID                      POSITION_TITLE  \\\n",
       "0             0         ASSISTANT DIRECTOR (EX LVL)   \n",
       "1             1                   LIBRARY ASSISTANT   \n",
       "4             4                         ELECTRICIAN   \n",
       "18           18            MAINTENANCE MECHANIC III   \n",
       "32           32                   SENIOR ACCOUNTANT   \n",
       "37           37              ASSISTANT SHOP MANAGER   \n",
       "57           57                SEMI-SKILLED LABORER   \n",
       "71           71    COUNCIL INTERN (EXECUTIVE LEVEL)   \n",
       "73           73           ADMINISTRATIVE SPECIALIST   \n",
       "74           74          SENIOR SIDELOADER OPERATOR   \n",
       "80           80           ADMINISTRATIVE SPECIALIST   \n",
       "87           87       SENIOR CONTRACT ADMINISTRATOR   \n",
       "90           90              9-1-1 TELECOMMUNICATOR   \n",
       "92           92                           CUSTODIAN   \n",
       "96           96       ASSISTANT CITY CONTROLLER III   \n",
       "101         101              CUSTOMER SERVICE CLERK   \n",
       "105         105              ASSISTANT SHOP MANAGER   \n",
       "117         117    SENIOR ASSISTANT CITY ATTORNEY I   \n",
       "124         124              SOLID WASTE SUPERVISOR   \n",
       "131         131               RECREATION SPECIALIST   \n",
       "133         133              SENIOR PROJECT MANAGER   \n",
       "146         146                SENIOR STAFF ANALYST   \n",
       "149         149            ADMINISTRATIVE ASSISTANT   \n",
       "157         157               RECREATION SPECIALIST   \n",
       "163         163                    EQUIPMENT WORKER   \n",
       "166         166            ADMINISTRATIVE ASSISTANT   \n",
       "170         170                    EQUIPMENT WORKER   \n",
       "171         171              ADMINISTRATION MANAGER   \n",
       "176         176            ADMINISTRATIVE ASSOCIATE   \n",
       "197         197          ADMINISTRATIVE COORDINATOR   \n",
       "...         ...                                 ...   \n",
       "1846       1846                   LIBRARY ASSISTANT   \n",
       "1847       1847                           PLANNER I   \n",
       "1850       1850                FINANCIAL ANALYST IV   \n",
       "1853       1853   MUNICIPAL COURTS SECURITY OFFICER   \n",
       "1865       1865   CUSTOMER SERVICE REPRESENTATIVE I   \n",
       "1866       1866                RECREATION ASSISTANT   \n",
       "1867       1867                        MECHANIC III   \n",
       "1868       1868                       STAFF ANALYST   \n",
       "1886       1886          SENIOR SIDELOADER OPERATOR   \n",
       "1887       1887   SENIOR HUMAN RESOURCES GENERALIST   \n",
       "1893       1893                    EQUIPMENT WORKER   \n",
       "1894       1894                        MECHANIC III   \n",
       "1896       1896                   DATA BASE ANALYST   \n",
       "1901       1901                        MECHANIC III   \n",
       "1903       1903            ASSISTANT SUPERINTENDENT   \n",
       "1907       1907  CUSTOMER SERVICE REPRESENTATIVE II   \n",
       "1912       1912   SENIOR HUMAN RESOURCES GENERALIST   \n",
       "1926       1926                         LIBRARIAN I   \n",
       "1933       1933             HUMAN RESOURCES MANAGER   \n",
       "1946       1946   SENIOR ASSISTANT CITY ATTORNEY II   \n",
       "1948       1948            ADMINISTRATIVE ASSISTANT   \n",
       "1952       1952              9-1-1 TELECOMMUNICATOR   \n",
       "1963       1963            SENIOR COMMUNITY LIAISON   \n",
       "1967       1967                       STAFF ANALYST   \n",
       "1968       1968              CUSTOMER SERVICE CLERK   \n",
       "1976       1976          SENIOR SIDELOADER OPERATOR   \n",
       "1983       1983           ADMINISTRATIVE SPECIALIST   \n",
       "1985       1985                        TRUCK DRIVER   \n",
       "1988       1988   SENIOR ASSISTANT CITY ATTORNEY II   \n",
       "1990       1990     BUILDING MAINTENANCE SUPERVISOR   \n",
       "\n",
       "                          DEPARTMENT  BASE_SALARY  \\\n",
       "0        Municipal Courts Department     121862.0   \n",
       "1                            Library      26125.0   \n",
       "4        General Services Department      56347.0   \n",
       "18       General Services Department      40581.0   \n",
       "32                           Finance      46963.0   \n",
       "37       Fleet Management Department      51438.0   \n",
       "57            Solid Waste Management      27622.0   \n",
       "71                      City Council          NaN   \n",
       "73             Human Resources Dept.      55939.0   \n",
       "74            Solid Waste Management      38459.0   \n",
       "80       Fleet Management Department      50249.0   \n",
       "87       Municipal Courts Department      79143.0   \n",
       "90    Houston Emergency Center (HEC)      32240.0   \n",
       "92                Parks & Recreation      26125.0   \n",
       "96          City Controller's Office      59077.0   \n",
       "101                          Library          NaN   \n",
       "105      Fleet Management Department      56971.0   \n",
       "117                 Legal Department      90957.0   \n",
       "124           Solid Waste Management      61428.0   \n",
       "131               Parks & Recreation      33592.0   \n",
       "133      General Services Department      88341.0   \n",
       "146    Houston Information Tech Svcs      74951.0   \n",
       "149         City Controller's Office      64251.0   \n",
       "157               Parks & Recreation      30368.0   \n",
       "163               Parks & Recreation      29307.0   \n",
       "166      Housing and Community Devp.      39666.0   \n",
       "170               Parks & Recreation      30347.0   \n",
       "171            Human Resources Dept.      80790.0   \n",
       "176      Dept of Neighborhoods (DON)      32386.0   \n",
       "197   Houston Emergency Center (HEC)      84456.0   \n",
       "...                              ...          ...   \n",
       "1846                         Library      26125.0   \n",
       "1847          Planning & Development      50378.0   \n",
       "1850                         Finance      81458.0   \n",
       "1853     Municipal Courts Department      34320.0   \n",
       "1865     Municipal Courts Department      37128.0   \n",
       "1866              Parks & Recreation          NaN   \n",
       "1867     Fleet Management Department      44158.0   \n",
       "1868      Admn. & Regulatory Affairs      65000.0   \n",
       "1886          Solid Waste Management      35630.0   \n",
       "1887           Human Resources Dept.      51532.0   \n",
       "1893              Parks & Recreation      29307.0   \n",
       "1894     Fleet Management Department      44158.0   \n",
       "1896                         Library      59371.0   \n",
       "1901     Fleet Management Department      46426.0   \n",
       "1903              Parks & Recreation      47466.0   \n",
       "1907     Municipal Courts Department      41621.0   \n",
       "1912           Human Resources Dept.      54938.0   \n",
       "1926                         Library      39874.0   \n",
       "1933           Human Resources Dept.      74435.0   \n",
       "1946                Legal Department     117831.0   \n",
       "1948              Parks & Recreation      41330.0   \n",
       "1952  Houston Emergency Center (HEC)      39104.0   \n",
       "1963                         Library      51542.0   \n",
       "1967           Human Resources Dept.      65000.0   \n",
       "1968                         Library          NaN   \n",
       "1976          Solid Waste Management      34466.0   \n",
       "1983      Admn. & Regulatory Affairs      55172.0   \n",
       "1985     Fleet Management Department      30222.0   \n",
       "1988                Legal Department     124115.0   \n",
       "1990              Parks & Recreation      30347.0   \n",
       "\n",
       "                                   RACE EMPLOYMENT_TYPE  GENDER  \\\n",
       "0                       Hispanic/Latino       Full Time  Female   \n",
       "1                       Hispanic/Latino       Full Time  Female   \n",
       "4                                 White       Full Time    Male   \n",
       "18                      Hispanic/Latino       Full Time    Male   \n",
       "32            Black or African American       Full Time    Male   \n",
       "37                                White       Full Time    Male   \n",
       "57            Black or African American       Full Time    Male   \n",
       "71                      Hispanic/Latino    Part Time 30    Male   \n",
       "73            Black or African American       Full Time  Female   \n",
       "74            Black or African American       Full Time    Male   \n",
       "80            Black or African American       Full Time  Female   \n",
       "87            Black or African American       Full Time    Male   \n",
       "90            Black or African American       Full Time    Male   \n",
       "92            Black or African American       Full Time  Female   \n",
       "96               Asian/Pacific Islander       Full Time  Female   \n",
       "101                                 NaN       Part Time  Female   \n",
       "105                     Hispanic/Latino       Full Time    Male   \n",
       "117           Black or African American       Full Time  Female   \n",
       "124           Black or African American       Full Time    Male   \n",
       "131           Black or African American       Full Time    Male   \n",
       "133                     Hispanic/Latino       Full Time    Male   \n",
       "146                               White       Full Time  Female   \n",
       "149                     Hispanic/Latino       Full Time  Female   \n",
       "157           Black or African American       Full Time    Male   \n",
       "163           Black or African American       Full Time    Male   \n",
       "166           Black or African American       Full Time    Male   \n",
       "170                     Hispanic/Latino       Full Time    Male   \n",
       "171                               White       Full Time    Male   \n",
       "176           Black or African American       Full Time  Female   \n",
       "197           Black or African American       Full Time  Female   \n",
       "...                                 ...             ...     ...   \n",
       "1846  American Indian or Alaskan Native       Full Time  Female   \n",
       "1847                    Hispanic/Latino       Full Time    Male   \n",
       "1850          Black or African American       Full Time  Female   \n",
       "1853          Black or African American       Full Time    Male   \n",
       "1865          Black or African American       Full Time  Female   \n",
       "1866                              White       Part Time  Female   \n",
       "1867                              White       Full Time    Male   \n",
       "1868          Black or African American       Full Time  Female   \n",
       "1886          Black or African American       Full Time    Male   \n",
       "1887          Black or African American       Full Time  Female   \n",
       "1893                              White       Full Time    Male   \n",
       "1894                              White       Full Time    Male   \n",
       "1896                    Hispanic/Latino       Full Time    Male   \n",
       "1901                    Hispanic/Latino       Full Time    Male   \n",
       "1903                    Hispanic/Latino       Full Time    Male   \n",
       "1907          Black or African American       Full Time  Female   \n",
       "1912          Black or African American       Full Time  Female   \n",
       "1926                              White       Full Time  Female   \n",
       "1933          Black or African American       Full Time  Female   \n",
       "1946             Asian/Pacific Islander       Full Time  Female   \n",
       "1948                    Hispanic/Latino       Full Time  Female   \n",
       "1952          Black or African American       Full Time  Female   \n",
       "1963          Black or African American       Full Time  Female   \n",
       "1967          Black or African American       Full Time  Female   \n",
       "1968          Black or African American       Part Time  Female   \n",
       "1976                    Hispanic/Latino       Full Time  Female   \n",
       "1983          Black or African American       Full Time  Female   \n",
       "1985          Black or African American       Full Time    Male   \n",
       "1988                              White       Full Time    Male   \n",
       "1990          Black or African American       Full Time  Female   \n",
       "\n",
       "     EMPLOYMENT_STATUS   HIRE_DATE    JOB_DATE  \n",
       "0               Active  2006-06-12  2012-10-13  \n",
       "1               Active  2000-07-19  2010-09-18  \n",
       "4               Active  1989-06-19  1994-10-22  \n",
       "18              Active  2008-12-29  2008-12-29  \n",
       "32              Active  1991-02-11  2016-02-13  \n",
       "37              Active  1995-11-30  2016-03-12  \n",
       "57              Active  2015-08-31  2015-08-31  \n",
       "71              Active  2015-06-08  2016-02-13  \n",
       "73              Active  2011-12-19  2013-11-23  \n",
       "74              Active  2004-09-07  2005-07-09  \n",
       "80              Active  2007-04-30  2012-09-29  \n",
       "87              Active  2009-09-08  2009-09-08  \n",
       "90              Active  2006-06-26  2006-06-26  \n",
       "92              Active  1993-10-02  1993-10-02  \n",
       "96              Active  2013-06-10  2013-06-10  \n",
       "101             Active  2016-02-29  2016-02-29  \n",
       "105             Active  2004-08-09  2013-10-26  \n",
       "117             Active  1998-03-20  2012-07-21  \n",
       "124             Active  1990-09-18  2004-12-11  \n",
       "131             Active  2013-06-22  2013-06-22  \n",
       "133             Active  2014-02-03  2014-02-03  \n",
       "146             Active  2014-03-17  2014-03-17  \n",
       "149             Active  1990-12-11  2010-05-01  \n",
       "157             Active  2007-10-22  2007-10-22  \n",
       "163             Active  2016-01-11  2016-01-11  \n",
       "166             Active  2007-04-09  2013-07-17  \n",
       "170             Active  2004-09-20  2004-09-20  \n",
       "171             Active  2015-01-12  2015-01-12  \n",
       "176             Active  1979-09-06  2011-09-03  \n",
       "197             Active  1987-12-07  2013-12-07  \n",
       "...                ...         ...         ...  \n",
       "1846            Active  2008-05-05  2013-01-19  \n",
       "1847            Active  2013-10-28  2016-04-23  \n",
       "1850            Active  1999-06-14  2014-03-29  \n",
       "1853            Active  2015-03-02  2015-03-02  \n",
       "1865            Active  1988-09-10  2002-09-21  \n",
       "1866            Active  2013-04-15  2013-04-15  \n",
       "1867            Active  2008-12-27  2013-08-17  \n",
       "1868            Active  1993-08-13  2016-01-30  \n",
       "1886            Active  2014-11-24  2014-11-24  \n",
       "1887            Active  2014-10-06  2014-10-06  \n",
       "1893            Active  2014-10-13  2016-01-30  \n",
       "1894            Active  2004-03-29  2012-09-29  \n",
       "1896            Active  2005-08-06  2013-10-26  \n",
       "1901            Active  2002-12-02  2005-12-31  \n",
       "1903            Active  1994-03-03  2011-08-06  \n",
       "1907            Active  2004-08-02  2013-03-02  \n",
       "1912            Active  2012-07-30  2012-07-30  \n",
       "1926            Active  2009-02-11  2014-10-11  \n",
       "1933            Active  2004-10-11  2015-05-23  \n",
       "1946            Active  2005-07-18  2012-07-21  \n",
       "1948            Active  1988-10-25  2005-04-16  \n",
       "1952            Active  2005-05-31  2005-05-31  \n",
       "1963            Active  2015-11-02  2015-11-02  \n",
       "1967            Active  1995-10-30  2016-02-13  \n",
       "1968            Active  2013-08-05  2013-08-05  \n",
       "1976            Active  2015-07-20  2016-01-30  \n",
       "1983            Active  2006-10-16  2006-10-16  \n",
       "1985            Active  2013-06-10  2015-08-01  \n",
       "1988            Active  2013-01-23  2013-03-02  \n",
       "1990            Active  1995-10-14  2010-03-20  \n",
       "\n",
       "[419 rows x 10 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "top_5_depts = employee.DEPARTMENT.value_counts().index[:5]\n",
    "criteria = ~employee.DEPARTMENT.isin(top_5_depts)\n",
    "employee[criteria]"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "The SQL equivalent of this would be as follows:\n",
    "SELECT\n",
    "*\n",
    "FROM\n",
    "EMPLOYEE\n",
    "WHERE\n",
    "DEPARTMENT not in\n",
    "(\n",
    "SELECT\n",
    "DEPARTMENT\n",
    "FROM (\n",
    "SELECT\n",
    "DEPARTMENT,\n",
    "COUNT(1) as CT\n",
    "FROM\n",
    "EMPLOYEE\n",
    "GROUP BY\n",
    "DEPARTMENT\n",
    "ORDER BY\n",
    "CT DESC\n",
    "LIMIT 5\n",
    ")\n",
    ");\n",
    "Notice the use of the pandas not operator, ~, which negates all boolean values of a Series."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Determining the normality of stock market returns"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Ez is részvényekkel kapcsolatos ezért azt a Stockinfo notebookban folytatom. Könyv 160. oldal"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Improving readability of boolean indexing with the query method"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Könyv 165. oldal\n",
    "Boolean indexing is not necessarily the most pleasant syntax to read or write, especially\n",
    "when using a single line to write a complex filter. Pandas has an alternative string-based\n",
    "syntax through the DataFrame query method that can provide more clarity.\n",
    "The query DataFrame method is experimental and not as capable as\n",
    "boolean indexing and should not be used for production code."
   ]
  },
  {
   "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>UNIQUE_ID</th>\n",
       "      <th>POSITION_TITLE</th>\n",
       "      <th>DEPARTMENT</th>\n",
       "      <th>BASE_SALARY</th>\n",
       "      <th>RACE</th>\n",
       "      <th>EMPLOYMENT_TYPE</th>\n",
       "      <th>GENDER</th>\n",
       "      <th>EMPLOYMENT_STATUS</th>\n",
       "      <th>HIRE_DATE</th>\n",
       "      <th>JOB_DATE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>ASSISTANT DIRECTOR (EX LVL)</td>\n",
       "      <td>Municipal Courts Department</td>\n",
       "      <td>121862.0</td>\n",
       "      <td>Hispanic/Latino</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Female</td>\n",
       "      <td>Active</td>\n",
       "      <td>2006-06-12</td>\n",
       "      <td>2012-10-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>LIBRARY ASSISTANT</td>\n",
       "      <td>Library</td>\n",
       "      <td>26125.0</td>\n",
       "      <td>Hispanic/Latino</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Female</td>\n",
       "      <td>Active</td>\n",
       "      <td>2000-07-19</td>\n",
       "      <td>2010-09-18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>POLICE OFFICER</td>\n",
       "      <td>Houston Police Department-HPD</td>\n",
       "      <td>45279.0</td>\n",
       "      <td>White</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Male</td>\n",
       "      <td>Active</td>\n",
       "      <td>2015-02-03</td>\n",
       "      <td>2015-02-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>ENGINEER/OPERATOR</td>\n",
       "      <td>Houston Fire Department (HFD)</td>\n",
       "      <td>63166.0</td>\n",
       "      <td>White</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Male</td>\n",
       "      <td>Active</td>\n",
       "      <td>1982-02-08</td>\n",
       "      <td>1991-05-25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>ELECTRICIAN</td>\n",
       "      <td>General Services Department</td>\n",
       "      <td>56347.0</td>\n",
       "      <td>White</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Male</td>\n",
       "      <td>Active</td>\n",
       "      <td>1989-06-19</td>\n",
       "      <td>1994-10-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>SENIOR POLICE OFFICER</td>\n",
       "      <td>Houston Police Department-HPD</td>\n",
       "      <td>66614.0</td>\n",
       "      <td>Black or African American</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Male</td>\n",
       "      <td>Active</td>\n",
       "      <td>1984-11-26</td>\n",
       "      <td>2005-03-26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>ENGINEER</td>\n",
       "      <td>Public Works &amp; Engineering-PWE</td>\n",
       "      <td>71680.0</td>\n",
       "      <td>Asian/Pacific Islander</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Male</td>\n",
       "      <td>Active</td>\n",
       "      <td>2012-03-26</td>\n",
       "      <td>2012-03-26</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   UNIQUE_ID               POSITION_TITLE                      DEPARTMENT  \\\n",
       "0          0  ASSISTANT DIRECTOR (EX LVL)     Municipal Courts Department   \n",
       "1          1            LIBRARY ASSISTANT                         Library   \n",
       "2          2               POLICE OFFICER   Houston Police Department-HPD   \n",
       "3          3            ENGINEER/OPERATOR   Houston Fire Department (HFD)   \n",
       "4          4                  ELECTRICIAN     General Services Department   \n",
       "5          5        SENIOR POLICE OFFICER   Houston Police Department-HPD   \n",
       "6          6                     ENGINEER  Public Works & Engineering-PWE   \n",
       "\n",
       "   BASE_SALARY                       RACE EMPLOYMENT_TYPE  GENDER  \\\n",
       "0     121862.0            Hispanic/Latino       Full Time  Female   \n",
       "1      26125.0            Hispanic/Latino       Full Time  Female   \n",
       "2      45279.0                      White       Full Time    Male   \n",
       "3      63166.0                      White       Full Time    Male   \n",
       "4      56347.0                      White       Full Time    Male   \n",
       "5      66614.0  Black or African American       Full Time    Male   \n",
       "6      71680.0     Asian/Pacific Islander       Full Time    Male   \n",
       "\n",
       "  EMPLOYMENT_STATUS   HIRE_DATE    JOB_DATE  \n",
       "0            Active  2006-06-12  2012-10-13  \n",
       "1            Active  2000-07-19  2010-09-18  \n",
       "2            Active  2015-02-03  2015-02-03  \n",
       "3            Active  1982-02-08  1991-05-25  \n",
       "4            Active  1989-06-19  1994-10-22  \n",
       "5            Active  1984-11-26  2005-03-26  \n",
       "6            Active  2012-03-26  2012-03-26  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "employee = pd.read_csv('https://raw.githubusercontent.com/DatasRev/source-files/master/csv/employee.csv')\n",
    "employee.head(7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "depts = ['Houston Police Department-HPD', 'Houston Fire Department (HFD)']\n",
    "select_columns = ['UNIQUE_ID', 'DEPARTMENT', 'GENDER', 'BASE_SALARY']"
   ]
  },
  {
   "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>UNIQUE_ID</th>\n",
       "      <th>DEPARTMENT</th>\n",
       "      <th>GENDER</th>\n",
       "      <th>BASE_SALARY</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>61</td>\n",
       "      <td>Houston Fire Department (HFD)</td>\n",
       "      <td>Female</td>\n",
       "      <td>96668.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136</th>\n",
       "      <td>136</td>\n",
       "      <td>Houston Police Department-HPD</td>\n",
       "      <td>Female</td>\n",
       "      <td>81239.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>367</th>\n",
       "      <td>367</td>\n",
       "      <td>Houston Police Department-HPD</td>\n",
       "      <td>Female</td>\n",
       "      <td>86534.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>474</th>\n",
       "      <td>474</td>\n",
       "      <td>Houston Police Department-HPD</td>\n",
       "      <td>Female</td>\n",
       "      <td>91181.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>513</th>\n",
       "      <td>513</td>\n",
       "      <td>Houston Police Department-HPD</td>\n",
       "      <td>Female</td>\n",
       "      <td>81239.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     UNIQUE_ID                     DEPARTMENT  GENDER  BASE_SALARY\n",
       "61          61  Houston Fire Department (HFD)  Female      96668.0\n",
       "136        136  Houston Police Department-HPD  Female      81239.0\n",
       "367        367  Houston Police Department-HPD  Female      86534.0\n",
       "474        474  Houston Police Department-HPD  Female      91181.0\n",
       "513        513  Houston Police Department-HPD  Female      81239.0"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Build the query string and execute the method:\n",
    "qs = \"DEPARTMENT in @depts \" \\\n",
    "\"and GENDER == 'Female' \" \\\n",
    "\"and 80000 <= BASE_SALARY <= 120000\"\n",
    "emp_filtered = employee.query(qs)\n",
    "emp_filtered[select_columns].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Strings passed to the query method are going to look more like plain English than normal\n",
    "pandas code. It is possible to reference Python variables using the at symbol (@) as with\n",
    "depts. All DataFrame column names are available in the query namespace by simply\n",
    "referencing their name without inner quotes. If a string is needed, such as Female, inner\n",
    "quotes will need to wrap it.\n",
    "Another nice feature of the query syntax is the ability to write a double inequality in a\n",
    "single expression and its ability to understand the verbose logical operators and, or, and\n",
    "not instead of their bitwise equivalents as with boolean indexing."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Instead of manually typing in a list of department names, we could have programmatically\n",
    "created it. For instance, if we wanted to find all the female employees that were not a\n",
    "member of the top 10 departments by frequency, we can run the following code:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>UNIQUE_ID</th>\n",
       "      <th>POSITION_TITLE</th>\n",
       "      <th>DEPARTMENT</th>\n",
       "      <th>BASE_SALARY</th>\n",
       "      <th>RACE</th>\n",
       "      <th>EMPLOYMENT_TYPE</th>\n",
       "      <th>GENDER</th>\n",
       "      <th>EMPLOYMENT_STATUS</th>\n",
       "      <th>HIRE_DATE</th>\n",
       "      <th>JOB_DATE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>ASSISTANT DIRECTOR (EX LVL)</td>\n",
       "      <td>Municipal Courts Department</td>\n",
       "      <td>121862.0</td>\n",
       "      <td>Hispanic/Latino</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Female</td>\n",
       "      <td>Active</td>\n",
       "      <td>2006-06-12</td>\n",
       "      <td>2012-10-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>73</td>\n",
       "      <td>ADMINISTRATIVE SPECIALIST</td>\n",
       "      <td>Human Resources Dept.</td>\n",
       "      <td>55939.0</td>\n",
       "      <td>Black or African American</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Female</td>\n",
       "      <td>Active</td>\n",
       "      <td>2011-12-19</td>\n",
       "      <td>2013-11-23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>96</td>\n",
       "      <td>ASSISTANT CITY CONTROLLER III</td>\n",
       "      <td>City Controller's Office</td>\n",
       "      <td>59077.0</td>\n",
       "      <td>Asian/Pacific Islander</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Female</td>\n",
       "      <td>Active</td>\n",
       "      <td>2013-06-10</td>\n",
       "      <td>2013-06-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>117</th>\n",
       "      <td>117</td>\n",
       "      <td>SENIOR ASSISTANT CITY ATTORNEY I</td>\n",
       "      <td>Legal Department</td>\n",
       "      <td>90957.0</td>\n",
       "      <td>Black or African American</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Female</td>\n",
       "      <td>Active</td>\n",
       "      <td>1998-03-20</td>\n",
       "      <td>2012-07-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146</th>\n",
       "      <td>146</td>\n",
       "      <td>SENIOR STAFF ANALYST</td>\n",
       "      <td>Houston Information Tech Svcs</td>\n",
       "      <td>74951.0</td>\n",
       "      <td>White</td>\n",
       "      <td>Full Time</td>\n",
       "      <td>Female</td>\n",
       "      <td>Active</td>\n",
       "      <td>2014-03-17</td>\n",
       "      <td>2014-03-17</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     UNIQUE_ID                    POSITION_TITLE  \\\n",
       "0            0       ASSISTANT DIRECTOR (EX LVL)   \n",
       "73          73         ADMINISTRATIVE SPECIALIST   \n",
       "96          96     ASSISTANT CITY CONTROLLER III   \n",
       "117        117  SENIOR ASSISTANT CITY ATTORNEY I   \n",
       "146        146              SENIOR STAFF ANALYST   \n",
       "\n",
       "                        DEPARTMENT  BASE_SALARY                       RACE  \\\n",
       "0      Municipal Courts Department     121862.0            Hispanic/Latino   \n",
       "73           Human Resources Dept.      55939.0  Black or African American   \n",
       "96        City Controller's Office      59077.0     Asian/Pacific Islander   \n",
       "117               Legal Department      90957.0  Black or African American   \n",
       "146  Houston Information Tech Svcs      74951.0                      White   \n",
       "\n",
       "    EMPLOYMENT_TYPE  GENDER EMPLOYMENT_STATUS   HIRE_DATE    JOB_DATE  \n",
       "0         Full Time  Female            Active  2006-06-12  2012-10-13  \n",
       "73        Full Time  Female            Active  2011-12-19  2013-11-23  \n",
       "96        Full Time  Female            Active  2013-06-10  2013-06-10  \n",
       "117       Full Time  Female            Active  1998-03-20  2012-07-21  \n",
       "146       Full Time  Female            Active  2014-03-17  2014-03-17  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "top10_depts = employee.DEPARTMENT.value_counts() \\\n",
    ".index[:10].tolist()\n",
    "qs = \"DEPARTMENT not in @top10_depts and GENDER == 'Female'\"\n",
    "employee_filtered2 = employee.query(qs)\n",
    "employee_filtered2.head()"
   ]
  }
 ],
 "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.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}