05- Working with Text Data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Working with Text Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd "
   ]
  },
  {
   "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>Name</th>\n",
       "      <th>Position Title</th>\n",
       "      <th>Department</th>\n",
       "      <th>Employee Annual Salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AARON,  ELVIA J</td>\n",
       "      <td>WATER RATE TAKER</td>\n",
       "      <td>WATER MGMNT</td>\n",
       "      <td>$90744.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AARON,  JEFFERY M</td>\n",
       "      <td>POLICE OFFICER</td>\n",
       "      <td>POLICE</td>\n",
       "      <td>$84450.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AARON,  KARINA</td>\n",
       "      <td>POLICE OFFICER</td>\n",
       "      <td>POLICE</td>\n",
       "      <td>$84450.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AARON,  KIMBERLEI R</td>\n",
       "      <td>CHIEF CONTRACT EXPEDITER</td>\n",
       "      <td>GENERAL SERVICES</td>\n",
       "      <td>$89880.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ABAD JR,  VICENTE M</td>\n",
       "      <td>CIVIL ENGINEER IV</td>\n",
       "      <td>WATER MGMNT</td>\n",
       "      <td>$106836.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32058</th>\n",
       "      <td>ZYGOWICZ,  PETER J</td>\n",
       "      <td>POLICE OFFICER</td>\n",
       "      <td>POLICE</td>\n",
       "      <td>$87384.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32059</th>\n",
       "      <td>ZYMANTAS,  MARK E</td>\n",
       "      <td>POLICE OFFICER</td>\n",
       "      <td>POLICE</td>\n",
       "      <td>$84450.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32060</th>\n",
       "      <td>ZYRKOWSKI,  CARLO E</td>\n",
       "      <td>POLICE OFFICER</td>\n",
       "      <td>POLICE</td>\n",
       "      <td>$87384.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32061</th>\n",
       "      <td>ZYSKOWSKI,  DARIUSZ</td>\n",
       "      <td>CHIEF DATA BASE ANALYST</td>\n",
       "      <td>DoIT</td>\n",
       "      <td>$113664.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32062</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>32063 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                      Name            Position Title        Department  \\\n",
       "0          AARON,  ELVIA J          WATER RATE TAKER       WATER MGMNT   \n",
       "1        AARON,  JEFFERY M            POLICE OFFICER            POLICE   \n",
       "2           AARON,  KARINA            POLICE OFFICER            POLICE   \n",
       "3      AARON,  KIMBERLEI R  CHIEF CONTRACT EXPEDITER  GENERAL SERVICES   \n",
       "4      ABAD JR,  VICENTE M         CIVIL ENGINEER IV       WATER MGMNT   \n",
       "...                    ...                       ...               ...   \n",
       "32058   ZYGOWICZ,  PETER J            POLICE OFFICER            POLICE   \n",
       "32059    ZYMANTAS,  MARK E            POLICE OFFICER            POLICE   \n",
       "32060  ZYRKOWSKI,  CARLO E            POLICE OFFICER            POLICE   \n",
       "32061  ZYSKOWSKI,  DARIUSZ   CHIEF DATA BASE ANALYST              DoIT   \n",
       "32062                  NaN                       NaN               NaN   \n",
       "\n",
       "      Employee Annual Salary  \n",
       "0                  $90744.00  \n",
       "1                  $84450.00  \n",
       "2                  $84450.00  \n",
       "3                  $89880.00  \n",
       "4                 $106836.00  \n",
       "...                      ...  \n",
       "32058              $87384.00  \n",
       "32059              $84450.00  \n",
       "32060              $87384.00  \n",
       "32061             $113664.00  \n",
       "32062                    NaN  \n",
       "\n",
       "[32063 rows x 4 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chicago = pd.read_csv(\"pandas/chicago.csv\")\n",
    "chicago"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 32063 entries, 0 to 32062\n",
      "Data columns (total 4 columns):\n",
      " #   Column                  Non-Null Count  Dtype \n",
      "---  ------                  --------------  ----- \n",
      " 0   Name                    32062 non-null  object\n",
      " 1   Position Title          32062 non-null  object\n",
      " 2   Department              32062 non-null  object\n",
      " 3   Employee Annual Salary  32062 non-null  object\n",
      "dtypes: object(4)\n",
      "memory usage: 1002.1+ KB\n"
     ]
    }
   ],
   "source": [
    "chicago.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "35"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chicago['Department'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Name                      31776\n",
       "Position Title             1093\n",
       "Department                   35\n",
       "Employee Annual Salary     1156\n",
       "dtype: int64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chicago.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "chicago['Department']=chicago['Department'].astype('category')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 32063 entries, 0 to 32062\n",
      "Data columns (total 4 columns):\n",
      " #   Column                  Non-Null Count  Dtype   \n",
      "---  ------                  --------------  -----   \n",
      " 0   Name                    32062 non-null  object  \n",
      " 1   Position Title          32062 non-null  object  \n",
      " 2   Department              32062 non-null  category\n",
      " 3   Employee Annual Salary  32062 non-null  object  \n",
      "dtypes: category(1), object(3)\n",
      "memory usage: 784.4+ KB\n"
     ]
    }
   ],
   "source": [
    "chicago.info()"
   ]
  },
  {
   "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>Name</th>\n",
       "      <th>Position Title</th>\n",
       "      <th>Department</th>\n",
       "      <th>Employee Annual Salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>32060</th>\n",
       "      <td>ZYRKOWSKI,  CARLO E</td>\n",
       "      <td>POLICE OFFICER</td>\n",
       "      <td>POLICE</td>\n",
       "      <td>$87384.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32061</th>\n",
       "      <td>ZYSKOWSKI,  DARIUSZ</td>\n",
       "      <td>CHIEF DATA BASE ANALYST</td>\n",
       "      <td>DoIT</td>\n",
       "      <td>$113664.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32062</th>\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": [
       "                      Name           Position Title Department  \\\n",
       "32060  ZYRKOWSKI,  CARLO E           POLICE OFFICER     POLICE   \n",
       "32061  ZYSKOWSKI,  DARIUSZ  CHIEF DATA BASE ANALYST       DoIT   \n",
       "32062                  NaN                      NaN        NaN   \n",
       "\n",
       "      Employee Annual Salary  \n",
       "32060              $87384.00  \n",
       "32061             $113664.00  \n",
       "32062                    NaN  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chicago.tail(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Common String Methods - lower,upper,title and len"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Position Title</th>\n",
       "      <th>Department</th>\n",
       "      <th>Employee Annual Salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AARON,  ELVIA J</td>\n",
       "      <td>WATER RATE TAKER</td>\n",
       "      <td>WATER MGMNT</td>\n",
       "      <td>$90744.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AARON,  JEFFERY M</td>\n",
       "      <td>POLICE OFFICER</td>\n",
       "      <td>POLICE</td>\n",
       "      <td>$84450.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AARON,  KARINA</td>\n",
       "      <td>POLICE OFFICER</td>\n",
       "      <td>POLICE</td>\n",
       "      <td>$84450.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                Name    Position Title   Department Employee Annual Salary\n",
       "0    AARON,  ELVIA J  WATER RATE TAKER  WATER MGMNT              $90744.00\n",
       "1  AARON,  JEFFERY M    POLICE OFFICER       POLICE              $84450.00\n",
       "2     AARON,  KARINA    POLICE OFFICER       POLICE              $84450.00"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chicago = pd.read_csv(\"pandas/chicago.csv\")\n",
    "chicago['Department']=chicago['Department'].astype('category')\n",
    "chicago.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'hello world'"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\"Hello World\".lower()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'HELLO WORLD'"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\"Hello World\".upper()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Hello World'"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\"hello wOrld\".title()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "11"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(\"hello world\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0            AARON,  ELVIA J\n",
       "1          AARON,  JEFFERY M\n",
       "2             AARON,  KARINA\n",
       "3        AARON,  KIMBERLEI R\n",
       "4        ABAD JR,  VICENTE M\n",
       "                ...         \n",
       "32058     ZYGOWICZ,  PETER J\n",
       "32059      ZYMANTAS,  MARK E\n",
       "32060    ZYRKOWSKI,  CARLO E\n",
       "32061    ZYSKOWSKI,  DARIUSZ\n",
       "32062                    NaN\n",
       "Name: Name, Length: 32063, dtype: object"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chicago['Name']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0            aaron,  elvia j\n",
       "1          aaron,  jeffery m\n",
       "2             aaron,  karina\n",
       "3        aaron,  kimberlei r\n",
       "4        abad jr,  vicente m\n",
       "                ...         \n",
       "32058     zygowicz,  peter j\n",
       "32059      zymantas,  mark e\n",
       "32060    zyrkowski,  carlo e\n",
       "32061    zyskowski,  dariusz\n",
       "32062                    NaN\n",
       "Name: Name, Length: 32063, dtype: object"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chicago['Name'].str.lower()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0            AARON,  ELVIA J\n",
       "1          AARON,  JEFFERY M\n",
       "2             AARON,  KARINA\n",
       "3        AARON,  KIMBERLEI R\n",
       "4        ABAD JR,  VICENTE M\n",
       "                ...         \n",
       "32058     ZYGOWICZ,  PETER J\n",
       "32059      ZYMANTAS,  MARK E\n",
       "32060    ZYRKOWSKI,  CARLO E\n",
       "32061    ZYSKOWSKI,  DARIUSZ\n",
       "32062                    NaN\n",
       "Name: Name, Length: 32063, dtype: object"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chicago['Name'].str.lower().str.upper()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0            Aaron,  Elvia J\n",
       "1          Aaron,  Jeffery M\n",
       "2             Aaron,  Karina\n",
       "3        Aaron,  Kimberlei R\n",
       "4        Abad Jr,  Vicente M\n",
       "                ...         \n",
       "32058     Zygowicz,  Peter J\n",
       "32059      Zymantas,  Mark E\n",
       "32060    Zyrkowski,  Carlo E\n",
       "32061    Zyskowski,  Dariusz\n",
       "32062                    NaN\n",
       "Name: Name, Length: 32063, dtype: object"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chicago['Name'].str.title()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>Name</th>\n",
       "      <th>Position Title</th>\n",
       "      <th>Department</th>\n",
       "      <th>Employee Annual Salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AARON,  ELVIA J</td>\n",
       "      <td>WATER RATE TAKER</td>\n",
       "      <td>WATER MGMNT</td>\n",
       "      <td>$90744.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AARON,  JEFFERY M</td>\n",
       "      <td>POLICE OFFICER</td>\n",
       "      <td>POLICE</td>\n",
       "      <td>$84450.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AARON,  KARINA</td>\n",
       "      <td>POLICE OFFICER</td>\n",
       "      <td>POLICE</td>\n",
       "      <td>$84450.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                Name    Position Title   Department Employee Annual Salary\n",
       "0    AARON,  ELVIA J  WATER RATE TAKER  WATER MGMNT              $90744.00\n",
       "1  AARON,  JEFFERY M    POLICE OFFICER       POLICE              $84450.00\n",
       "2     AARON,  KARINA    POLICE OFFICER       POLICE              $84450.00"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chicago.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        11.0\n",
       "1         6.0\n",
       "2         6.0\n",
       "3        16.0\n",
       "4        11.0\n",
       "         ... \n",
       "32058     6.0\n",
       "32059     6.0\n",
       "32060     6.0\n",
       "32061     4.0\n",
       "32062     NaN\n",
       "Name: Department, Length: 32063, dtype: float64"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chicago['Department'].str.len()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The .str.replace() Method"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'He!!o Wor!d'"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\"Hello World\".replace(\"l\",\"!\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>Name</th>\n",
       "      <th>Position Title</th>\n",
       "      <th>Department</th>\n",
       "      <th>Employee Annual Salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AARON,  ELVIA J</td>\n",
       "      <td>WATER RATE TAKER</td>\n",
       "      <td>WATER MGMNT</td>\n",
       "      <td>$90744.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AARON,  JEFFERY M</td>\n",
       "      <td>POLICE OFFICER</td>\n",
       "      <td>POLICE</td>\n",
       "      <td>$84450.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AARON,  KARINA</td>\n",
       "      <td>POLICE OFFICER</td>\n",
       "      <td>POLICE</td>\n",
       "      <td>$84450.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                Name    Position Title   Department Employee Annual Salary\n",
       "0    AARON,  ELVIA J  WATER RATE TAKER  WATER MGMNT              $90744.00\n",
       "1  AARON,  JEFFERY M    POLICE OFFICER       POLICE              $84450.00\n",
       "2     AARON,  KARINA    POLICE OFFICER       POLICE              $84450.00"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chicago = pd.read_csv(\"pandas/chicago.csv\")\n",
    "chicago['Department']=chicago['Department'].astype('category')\n",
    "chicago.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        WATER MANAGEMENT\n",
       "1                  POLICE\n",
       "2                  POLICE\n",
       "3        GENERAL SERVICES\n",
       "4        WATER MANAGEMENT\n",
       "               ...       \n",
       "32058              POLICE\n",
       "32059              POLICE\n",
       "32060              POLICE\n",
       "32061                DoIT\n",
       "32062                 NaN\n",
       "Name: Department, Length: 32063, dtype: object"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chicago['Department'].str.replace(\"MGMNT\",\"MANAGEMENT\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>Name</th>\n",
       "      <th>Position Title</th>\n",
       "      <th>Department</th>\n",
       "      <th>Employee Annual Salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AARON,  ELVIA J</td>\n",
       "      <td>WATER RATE TAKER</td>\n",
       "      <td>WATER MANAGEMENT</td>\n",
       "      <td>$90744.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AARON,  JEFFERY M</td>\n",
       "      <td>POLICE OFFICER</td>\n",
       "      <td>POLICE</td>\n",
       "      <td>$84450.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AARON,  KARINA</td>\n",
       "      <td>POLICE OFFICER</td>\n",
       "      <td>POLICE</td>\n",
       "      <td>$84450.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AARON,  KIMBERLEI R</td>\n",
       "      <td>CHIEF CONTRACT EXPEDITER</td>\n",
       "      <td>GENERAL SERVICES</td>\n",
       "      <td>$89880.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ABAD JR,  VICENTE M</td>\n",
       "      <td>CIVIL ENGINEER IV</td>\n",
       "      <td>WATER MANAGEMENT</td>\n",
       "      <td>$106836.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  Name            Position Title        Department  \\\n",
       "0      AARON,  ELVIA J          WATER RATE TAKER  WATER MANAGEMENT   \n",
       "1    AARON,  JEFFERY M            POLICE OFFICER            POLICE   \n",
       "2       AARON,  KARINA            POLICE OFFICER            POLICE   \n",
       "3  AARON,  KIMBERLEI R  CHIEF CONTRACT EXPEDITER  GENERAL SERVICES   \n",
       "4  ABAD JR,  VICENTE M         CIVIL ENGINEER IV  WATER MANAGEMENT   \n",
       "\n",
       "  Employee Annual Salary  \n",
       "0              $90744.00  \n",
       "1              $84450.00  \n",
       "2              $84450.00  \n",
       "3              $89880.00  \n",
       "4             $106836.00  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chicago['Department']= chicago['Department'].str.replace(\"MGMNT\",\"MANAGEMENT\")\n",
    "chicago.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>Name</th>\n",
       "      <th>Position Title</th>\n",
       "      <th>Department</th>\n",
       "      <th>Employee Annual Salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AARON,  ELVIA J</td>\n",
       "      <td>WATER RATE TAKER</td>\n",
       "      <td>WATER MANAGEMENT</td>\n",
       "      <td>90744.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AARON,  JEFFERY M</td>\n",
       "      <td>POLICE OFFICER</td>\n",
       "      <td>POLICE</td>\n",
       "      <td>84450.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AARON,  KARINA</td>\n",
       "      <td>POLICE OFFICER</td>\n",
       "      <td>POLICE</td>\n",
       "      <td>84450.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AARON,  KIMBERLEI R</td>\n",
       "      <td>CHIEF CONTRACT EXPEDITER</td>\n",
       "      <td>GENERAL SERVICES</td>\n",
       "      <td>89880.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ABAD JR,  VICENTE M</td>\n",
       "      <td>CIVIL ENGINEER IV</td>\n",
       "      <td>WATER MANAGEMENT</td>\n",
       "      <td>106836.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  Name            Position Title        Department  \\\n",
       "0      AARON,  ELVIA J          WATER RATE TAKER  WATER MANAGEMENT   \n",
       "1    AARON,  JEFFERY M            POLICE OFFICER            POLICE   \n",
       "2       AARON,  KARINA            POLICE OFFICER            POLICE   \n",
       "3  AARON,  KIMBERLEI R  CHIEF CONTRACT EXPEDITER  GENERAL SERVICES   \n",
       "4  ABAD JR,  VICENTE M         CIVIL ENGINEER IV  WATER MANAGEMENT   \n",
       "\n",
       "  Employee Annual Salary  \n",
       "0               90744.00  \n",
       "1               84450.00  \n",
       "2               84450.00  \n",
       "3               89880.00  \n",
       "4              106836.00  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chicago['Employee Annual Salary']= chicago['Employee Annual Salary'].str.replace(\"$\",\"\")\n",
    "chicago.head()"
   ]
  },
  {
   "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>Name</th>\n",
       "      <th>Position Title</th>\n",
       "      <th>Department</th>\n",
       "      <th>Employee Annual Salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AARON,  ELVIA J</td>\n",
       "      <td>WATER RATE TAKER</td>\n",
       "      <td>WATER MANAGEMENT</td>\n",
       "      <td>90744.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AARON,  JEFFERY M</td>\n",
       "      <td>POLICE OFFICER</td>\n",
       "      <td>POLICE</td>\n",
       "      <td>84450.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AARON,  KARINA</td>\n",
       "      <td>POLICE OFFICER</td>\n",
       "      <td>POLICE</td>\n",
       "      <td>84450.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AARON,  KIMBERLEI R</td>\n",
       "      <td>CHIEF CONTRACT EXPEDITER</td>\n",
       "      <td>GENERAL SERVICES</td>\n",
       "      <td>89880.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ABAD JR,  VICENTE M</td>\n",
       "      <td>CIVIL ENGINEER IV</td>\n",
       "      <td>WATER MANAGEMENT</td>\n",
       "      <td>106836.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  Name            Position Title        Department  \\\n",
       "0      AARON,  ELVIA J          WATER RATE TAKER  WATER MANAGEMENT   \n",
       "1    AARON,  JEFFERY M            POLICE OFFICER            POLICE   \n",
       "2       AARON,  KARINA            POLICE OFFICER            POLICE   \n",
       "3  AARON,  KIMBERLEI R  CHIEF CONTRACT EXPEDITER  GENERAL SERVICES   \n",
       "4  ABAD JR,  VICENTE M         CIVIL ENGINEER IV  WATER MANAGEMENT   \n",
       "\n",
       "   Employee Annual Salary  \n",
       "0                 90744.0  \n",
       "1                 84450.0  \n",
       "2                 84450.0  \n",
       "3                 89880.0  \n",
       "4                106836.0  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chicago['Employee Annual Salary']= chicago['Employee Annual Salary'].astype(float)\n",
    "chicago.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 32063 entries, 0 to 32062\n",
      "Data columns (total 4 columns):\n",
      " #   Column                  Non-Null Count  Dtype  \n",
      "---  ------                  --------------  -----  \n",
      " 0   Name                    32062 non-null  object \n",
      " 1   Position Title          32062 non-null  object \n",
      " 2   Department              32062 non-null  object \n",
      " 3   Employee Annual Salary  32062 non-null  float64\n",
      "dtypes: float64(1), object(3)\n",
      "memory usage: 1002.1+ KB\n"
     ]
    }
   ],
   "source": [
    "chicago.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "chicago['Department']=chicago['Department'].astype('category')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 32063 entries, 0 to 32062\n",
      "Data columns (total 4 columns):\n",
      " #   Column                  Non-Null Count  Dtype   \n",
      "---  ------                  --------------  -----   \n",
      " 0   Name                    32062 non-null  object  \n",
      " 1   Position Title          32062 non-null  object  \n",
      " 2   Department              32062 non-null  category\n",
      " 3   Employee Annual Salary  32062 non-null  float64 \n",
      "dtypes: category(1), float64(1), object(2)\n",
      "memory usage: 784.4+ KB\n"
     ]
    }
   ],
   "source": [
    "chicago.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2571506375.36"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chicago['Employee Annual Salary'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "8184     300000.0\n",
       "7954     216210.0\n",
       "25532    202728.0\n",
       "8924     197736.0\n",
       "8042     197724.0\n",
       "Name: Employee Annual Salary, dtype: float64"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chicago['Employee Annual Salary'].nlargest()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Filtering with String Methods"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "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>Name</th>\n",
       "      <th>Position Title</th>\n",
       "      <th>Department</th>\n",
       "      <th>Employee Annual Salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>32060</th>\n",
       "      <td>ZYRKOWSKI,  CARLO E</td>\n",
       "      <td>POLICE OFFICER</td>\n",
       "      <td>POLICE</td>\n",
       "      <td>$87384.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32061</th>\n",
       "      <td>ZYSKOWSKI,  DARIUSZ</td>\n",
       "      <td>CHIEF DATA BASE ANALYST</td>\n",
       "      <td>DoIT</td>\n",
       "      <td>$113664.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32062</th>\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": [
       "                      Name           Position Title Department  \\\n",
       "32060  ZYRKOWSKI,  CARLO E           POLICE OFFICER     POLICE   \n",
       "32061  ZYSKOWSKI,  DARIUSZ  CHIEF DATA BASE ANALYST       DoIT   \n",
       "32062                  NaN                      NaN        NaN   \n",
       "\n",
       "      Employee Annual Salary  \n",
       "32060              $87384.00  \n",
       "32061             $113664.00  \n",
       "32062                    NaN  "
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chicago = pd.read_csv(\"pandas/chicago.csv\")\n",
    "chicago['Department']=chicago['Department'].astype('category')\n",
    "chicago.tail(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "chicago = chicago.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "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>Name</th>\n",
       "      <th>Position Title</th>\n",
       "      <th>Department</th>\n",
       "      <th>Employee Annual Salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>32059</th>\n",
       "      <td>ZYMANTAS,  MARK E</td>\n",
       "      <td>POLICE OFFICER</td>\n",
       "      <td>POLICE</td>\n",
       "      <td>$84450.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32060</th>\n",
       "      <td>ZYRKOWSKI,  CARLO E</td>\n",
       "      <td>POLICE OFFICER</td>\n",
       "      <td>POLICE</td>\n",
       "      <td>$87384.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32061</th>\n",
       "      <td>ZYSKOWSKI,  DARIUSZ</td>\n",
       "      <td>CHIEF DATA BASE ANALYST</td>\n",
       "      <td>DoIT</td>\n",
       "      <td>$113664.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      Name           Position Title Department  \\\n",
       "32059    ZYMANTAS,  MARK E           POLICE OFFICER     POLICE   \n",
       "32060  ZYRKOWSKI,  CARLO E           POLICE OFFICER     POLICE   \n",
       "32061  ZYSKOWSKI,  DARIUSZ  CHIEF DATA BASE ANALYST       DoIT   \n",
       "\n",
       "      Employee Annual Salary  \n",
       "32059              $84450.00  \n",
       "32060              $87384.00  \n",
       "32061             $113664.00  "
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chicago.tail(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0         True\n",
       "1        False\n",
       "2        False\n",
       "3        False\n",
       "4        False\n",
       "         ...  \n",
       "32057    False\n",
       "32058    False\n",
       "32059    False\n",
       "32060    False\n",
       "32061    False\n",
       "Name: Position Title, Length: 32062, dtype: bool"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chicago['Position Title'].str.lower().str.contains(\"water\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "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>Name</th>\n",
       "      <th>Position Title</th>\n",
       "      <th>Department</th>\n",
       "      <th>Employee Annual Salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AARON,  ELVIA J</td>\n",
       "      <td>WATER RATE TAKER</td>\n",
       "      <td>WATER MGMNT</td>\n",
       "      <td>$90744.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>554</th>\n",
       "      <td>ALUISE,  VINCENT G</td>\n",
       "      <td>FOREMAN OF WATER PIPE CONSTRUCTION</td>\n",
       "      <td>WATER MGMNT</td>\n",
       "      <td>$102440.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>671</th>\n",
       "      <td>ANDER,  PERRY A</td>\n",
       "      <td>WATER CHEMIST II</td>\n",
       "      <td>WATER MGMNT</td>\n",
       "      <td>$82044.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>685</th>\n",
       "      <td>ANDERSON,  ANDREW J</td>\n",
       "      <td>DISTRICT SUPERINTENDENT OF WATER DISTRIBUTION</td>\n",
       "      <td>WATER MGMNT</td>\n",
       "      <td>$109272.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>702</th>\n",
       "      <td>ANDERSON,  DONALD</td>\n",
       "      <td>FOREMAN OF WATER PIPE CONSTRUCTION</td>\n",
       "      <td>WATER MGMNT</td>\n",
       "      <td>$102440.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29669</th>\n",
       "      <td>VERMA,  ANUPAM</td>\n",
       "      <td>MANAGING ENGINEER - WATER MANAGEMENT</td>\n",
       "      <td>WATER MGMNT</td>\n",
       "      <td>$111192.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30239</th>\n",
       "      <td>WASHINGTON,  JOSEPH</td>\n",
       "      <td>WATER CHEMIST III</td>\n",
       "      <td>WATER MGMNT</td>\n",
       "      <td>$89676.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30544</th>\n",
       "      <td>WEST,  THOMAS R</td>\n",
       "      <td>GEN SUPT OF WATER MANAGEMENT</td>\n",
       "      <td>WATER MGMNT</td>\n",
       "      <td>$115704.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30991</th>\n",
       "      <td>WILLIAMS,  MATTHEW</td>\n",
       "      <td>FOREMAN OF WATER PIPE CONSTRUCTION</td>\n",
       "      <td>WATER MGMNT</td>\n",
       "      <td>$102440.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31405</th>\n",
       "      <td>WOODRIDGE,  ROBERT L</td>\n",
       "      <td>FOREMAN OF WATER PIPE CONSTRUCTION</td>\n",
       "      <td>WATER MGMNT</td>\n",
       "      <td>$102440.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>111 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                       Name                                 Position Title  \\\n",
       "0           AARON,  ELVIA J                               WATER RATE TAKER   \n",
       "554      ALUISE,  VINCENT G             FOREMAN OF WATER PIPE CONSTRUCTION   \n",
       "671         ANDER,  PERRY A                               WATER CHEMIST II   \n",
       "685     ANDERSON,  ANDREW J  DISTRICT SUPERINTENDENT OF WATER DISTRIBUTION   \n",
       "702       ANDERSON,  DONALD             FOREMAN OF WATER PIPE CONSTRUCTION   \n",
       "...                     ...                                            ...   \n",
       "29669        VERMA,  ANUPAM           MANAGING ENGINEER - WATER MANAGEMENT   \n",
       "30239   WASHINGTON,  JOSEPH                              WATER CHEMIST III   \n",
       "30544       WEST,  THOMAS R                   GEN SUPT OF WATER MANAGEMENT   \n",
       "30991    WILLIAMS,  MATTHEW             FOREMAN OF WATER PIPE CONSTRUCTION   \n",
       "31405  WOODRIDGE,  ROBERT L             FOREMAN OF WATER PIPE CONSTRUCTION   \n",
       "\n",
       "        Department Employee Annual Salary  \n",
       "0      WATER MGMNT              $90744.00  \n",
       "554    WATER MGMNT             $102440.00  \n",
       "671    WATER MGMNT              $82044.00  \n",
       "685    WATER MGMNT             $109272.00  \n",
       "702    WATER MGMNT             $102440.00  \n",
       "...            ...                    ...  \n",
       "29669  WATER MGMNT             $111192.00  \n",
       "30239  WATER MGMNT              $89676.00  \n",
       "30544  WATER MGMNT             $115704.00  \n",
       "30991  WATER MGMNT             $102440.00  \n",
       "31405  WATER MGMNT             $102440.00  \n",
       "\n",
       "[111 rows x 4 columns]"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mask = chicago['Position Title'].str.lower().str.contains('water')\n",
    "chicago[mask]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "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>Name</th>\n",
       "      <th>Position Title</th>\n",
       "      <th>Department</th>\n",
       "      <th>Employee Annual Salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AARON,  ELVIA J</td>\n",
       "      <td>WATER RATE TAKER</td>\n",
       "      <td>WATER MGMNT</td>\n",
       "      <td>$90744.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>671</th>\n",
       "      <td>ANDER,  PERRY A</td>\n",
       "      <td>WATER CHEMIST II</td>\n",
       "      <td>WATER MGMNT</td>\n",
       "      <td>$82044.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1054</th>\n",
       "      <td>ASHLEY,  KARMA T</td>\n",
       "      <td>WATER CHEMIST II</td>\n",
       "      <td>WATER MGMNT</td>\n",
       "      <td>$82044.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1079</th>\n",
       "      <td>ATKINS,  JOANNA M</td>\n",
       "      <td>WATER CHEMIST II</td>\n",
       "      <td>WATER MGMNT</td>\n",
       "      <td>$82044.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1181</th>\n",
       "      <td>AZEEM,  MOHAMMED A</td>\n",
       "      <td>WATER CHEMIST II</td>\n",
       "      <td>WATER MGMNT</td>\n",
       "      <td>$53172.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28574</th>\n",
       "      <td>THREATT,  DENISE R</td>\n",
       "      <td>WATER QUALITY INSPECTOR</td>\n",
       "      <td>WATER MGMNT</td>\n",
       "      <td>$62004.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28602</th>\n",
       "      <td>TIGNOR,  DARRYL B</td>\n",
       "      <td>WATER RATE TAKER</td>\n",
       "      <td>WATER MGMNT</td>\n",
       "      <td>$78948.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28955</th>\n",
       "      <td>TRAVIS COOK,  LESLIE R</td>\n",
       "      <td>WATER RATE TAKER</td>\n",
       "      <td>WATER MGMNT</td>\n",
       "      <td>$78948.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29584</th>\n",
       "      <td>VELAZQUEZ,  JOHN</td>\n",
       "      <td>WATER RATE TAKER</td>\n",
       "      <td>WATER MGMNT</td>\n",
       "      <td>$78948.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30239</th>\n",
       "      <td>WASHINGTON,  JOSEPH</td>\n",
       "      <td>WATER CHEMIST III</td>\n",
       "      <td>WATER MGMNT</td>\n",
       "      <td>$89676.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>75 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                         Name           Position Title   Department  \\\n",
       "0             AARON,  ELVIA J         WATER RATE TAKER  WATER MGMNT   \n",
       "671           ANDER,  PERRY A         WATER CHEMIST II  WATER MGMNT   \n",
       "1054         ASHLEY,  KARMA T         WATER CHEMIST II  WATER MGMNT   \n",
       "1079        ATKINS,  JOANNA M         WATER CHEMIST II  WATER MGMNT   \n",
       "1181       AZEEM,  MOHAMMED A         WATER CHEMIST II  WATER MGMNT   \n",
       "...                       ...                      ...          ...   \n",
       "28574      THREATT,  DENISE R  WATER QUALITY INSPECTOR  WATER MGMNT   \n",
       "28602       TIGNOR,  DARRYL B         WATER RATE TAKER  WATER MGMNT   \n",
       "28955  TRAVIS COOK,  LESLIE R         WATER RATE TAKER  WATER MGMNT   \n",
       "29584        VELAZQUEZ,  JOHN         WATER RATE TAKER  WATER MGMNT   \n",
       "30239     WASHINGTON,  JOSEPH        WATER CHEMIST III  WATER MGMNT   \n",
       "\n",
       "      Employee Annual Salary  \n",
       "0                  $90744.00  \n",
       "671                $82044.00  \n",
       "1054               $82044.00  \n",
       "1079               $82044.00  \n",
       "1181               $53172.00  \n",
       "...                      ...  \n",
       "28574              $62004.00  \n",
       "28602              $78948.00  \n",
       "28955              $78948.00  \n",
       "29584              $78948.00  \n",
       "30239              $89676.00  \n",
       "\n",
       "[75 rows x 4 columns]"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mask = chicago['Position Title'].str.lower().str.startswith('water')\n",
    "chicago[mask]"
   ]
  },
  {
   "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>Name</th>\n",
       "      <th>Position Title</th>\n",
       "      <th>Department</th>\n",
       "      <th>Employee Annual Salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AARON,  ELVIA J</td>\n",
       "      <td>WATER RATE TAKER</td>\n",
       "      <td>WATER MGMNT</td>\n",
       "      <td>$90744.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>671</th>\n",
       "      <td>ANDER,  PERRY A</td>\n",
       "      <td>WATER CHEMIST II</td>\n",
       "      <td>WATER MGMNT</td>\n",
       "      <td>$82044.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1054</th>\n",
       "      <td>ASHLEY,  KARMA T</td>\n",
       "      <td>WATER CHEMIST II</td>\n",
       "      <td>WATER MGMNT</td>\n",
       "      <td>$82044.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1079</th>\n",
       "      <td>ATKINS,  JOANNA M</td>\n",
       "      <td>WATER CHEMIST II</td>\n",
       "      <td>WATER MGMNT</td>\n",
       "      <td>$82044.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1181</th>\n",
       "      <td>AZEEM,  MOHAMMED A</td>\n",
       "      <td>WATER CHEMIST II</td>\n",
       "      <td>WATER MGMNT</td>\n",
       "      <td>$53172.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28574</th>\n",
       "      <td>THREATT,  DENISE R</td>\n",
       "      <td>WATER QUALITY INSPECTOR</td>\n",
       "      <td>WATER MGMNT</td>\n",
       "      <td>$62004.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28602</th>\n",
       "      <td>TIGNOR,  DARRYL B</td>\n",
       "      <td>WATER RATE TAKER</td>\n",
       "      <td>WATER MGMNT</td>\n",
       "      <td>$78948.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28955</th>\n",
       "      <td>TRAVIS COOK,  LESLIE R</td>\n",
       "      <td>WATER RATE TAKER</td>\n",
       "      <td>WATER MGMNT</td>\n",
       "      <td>$78948.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29584</th>\n",
       "      <td>VELAZQUEZ,  JOHN</td>\n",
       "      <td>WATER RATE TAKER</td>\n",
       "      <td>WATER MGMNT</td>\n",
       "      <td>$78948.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30239</th>\n",
       "      <td>WASHINGTON,  JOSEPH</td>\n",
       "      <td>WATER CHEMIST III</td>\n",
       "      <td>WATER MGMNT</td>\n",
       "      <td>$89676.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>75 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                         Name           Position Title   Department  \\\n",
       "0             AARON,  ELVIA J         WATER RATE TAKER  WATER MGMNT   \n",
       "671           ANDER,  PERRY A         WATER CHEMIST II  WATER MGMNT   \n",
       "1054         ASHLEY,  KARMA T         WATER CHEMIST II  WATER MGMNT   \n",
       "1079        ATKINS,  JOANNA M         WATER CHEMIST II  WATER MGMNT   \n",
       "1181       AZEEM,  MOHAMMED A         WATER CHEMIST II  WATER MGMNT   \n",
       "...                       ...                      ...          ...   \n",
       "28574      THREATT,  DENISE R  WATER QUALITY INSPECTOR  WATER MGMNT   \n",
       "28602       TIGNOR,  DARRYL B         WATER RATE TAKER  WATER MGMNT   \n",
       "28955  TRAVIS COOK,  LESLIE R         WATER RATE TAKER  WATER MGMNT   \n",
       "29584        VELAZQUEZ,  JOHN         WATER RATE TAKER  WATER MGMNT   \n",
       "30239     WASHINGTON,  JOSEPH        WATER CHEMIST III  WATER MGMNT   \n",
       "\n",
       "      Employee Annual Salary  \n",
       "0                  $90744.00  \n",
       "671                $82044.00  \n",
       "1054               $82044.00  \n",
       "1079               $82044.00  \n",
       "1181               $53172.00  \n",
       "...                      ...  \n",
       "28574              $62004.00  \n",
       "28602              $78948.00  \n",
       "28955              $78948.00  \n",
       "29584              $78948.00  \n",
       "30239              $89676.00  \n",
       "\n",
       "[75 rows x 4 columns]"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chicago[chicago['Position Title'].str.lower().str.startswith('water')]"
   ]
  },
  {
   "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>Name</th>\n",
       "      <th>Position Title</th>\n",
       "      <th>Department</th>\n",
       "      <th>Employee Annual Salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>184</th>\n",
       "      <td>AFROZ,  NAYYAR</td>\n",
       "      <td>PSYCHIATRIST</td>\n",
       "      <td>HEALTH</td>\n",
       "      <td>$99840.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>308</th>\n",
       "      <td>ALARCON,  LUIS J</td>\n",
       "      <td>LOAN PROCESSING SPECIALIST</td>\n",
       "      <td>COMMUNITY DEVELOPMENT</td>\n",
       "      <td>$81948.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>422</th>\n",
       "      <td>ALLAIN,  CAROLYN</td>\n",
       "      <td>SENIOR TELECOMMUNICATIONS SPECIALIST</td>\n",
       "      <td>DoIT</td>\n",
       "      <td>$89880.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>472</th>\n",
       "      <td>ALLEN,  ROBERT</td>\n",
       "      <td>MACHINIST</td>\n",
       "      <td>WATER MGMNT</td>\n",
       "      <td>$94328.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>705</th>\n",
       "      <td>ANDERSON,  EDWARD M</td>\n",
       "      <td>SR PROCUREMENT SPECIALIST</td>\n",
       "      <td>PROCUREMENT</td>\n",
       "      <td>$91476.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31667</th>\n",
       "      <td>YODER,  TERESA G</td>\n",
       "      <td>ARCHIVAL SPECIALIST</td>\n",
       "      <td>PUBLIC LIBRARY</td>\n",
       "      <td>$74304.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31688</th>\n",
       "      <td>YOUNGBLOOM,  LAURENCE G</td>\n",
       "      <td>CRIMES SURVEILLANCE SPECIALIST</td>\n",
       "      <td>OEMC</td>\n",
       "      <td>$19676.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31717</th>\n",
       "      <td>YOUNG,  KIMBERLY M</td>\n",
       "      <td>SR PROCUREMENT SPECIALIST</td>\n",
       "      <td>PROCUREMENT</td>\n",
       "      <td>$68556.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31837</th>\n",
       "      <td>ZAPATA,  HUGO</td>\n",
       "      <td>SR PROCUREMENT SPECIALIST</td>\n",
       "      <td>PROCUREMENT</td>\n",
       "      <td>$87324.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31918</th>\n",
       "      <td>ZEMKE,  RICHARD P</td>\n",
       "      <td>MACHINIST</td>\n",
       "      <td>AVIATION</td>\n",
       "      <td>$94328.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>172 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                          Name                        Position Title  \\\n",
       "184             AFROZ,  NAYYAR                          PSYCHIATRIST   \n",
       "308           ALARCON,  LUIS J            LOAN PROCESSING SPECIALIST   \n",
       "422           ALLAIN,  CAROLYN  SENIOR TELECOMMUNICATIONS SPECIALIST   \n",
       "472             ALLEN,  ROBERT                             MACHINIST   \n",
       "705        ANDERSON,  EDWARD M             SR PROCUREMENT SPECIALIST   \n",
       "...                        ...                                   ...   \n",
       "31667         YODER,  TERESA G                   ARCHIVAL SPECIALIST   \n",
       "31688  YOUNGBLOOM,  LAURENCE G        CRIMES SURVEILLANCE SPECIALIST   \n",
       "31717       YOUNG,  KIMBERLY M             SR PROCUREMENT SPECIALIST   \n",
       "31837            ZAPATA,  HUGO             SR PROCUREMENT SPECIALIST   \n",
       "31918        ZEMKE,  RICHARD P                             MACHINIST   \n",
       "\n",
       "                  Department Employee Annual Salary  \n",
       "184                   HEALTH              $99840.00  \n",
       "308    COMMUNITY DEVELOPMENT              $81948.00  \n",
       "422                     DoIT              $89880.00  \n",
       "472              WATER MGMNT              $94328.00  \n",
       "705              PROCUREMENT              $91476.00  \n",
       "...                      ...                    ...  \n",
       "31667         PUBLIC LIBRARY              $74304.00  \n",
       "31688                   OEMC              $19676.80  \n",
       "31717            PROCUREMENT              $68556.00  \n",
       "31837            PROCUREMENT              $87324.00  \n",
       "31918               AVIATION              $94328.00  \n",
       "\n",
       "[172 rows x 4 columns]"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mask = chicago['Position Title'].str.lower().str.endswith('ist')\n",
    "chicago[mask]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## More String Methods - .strip(), .lstrip() and .rstrip()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "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>Name</th>\n",
       "      <th>Position Title</th>\n",
       "      <th>Department</th>\n",
       "      <th>Employee Annual Salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>32060</th>\n",
       "      <td>ZYRKOWSKI,  CARLO E</td>\n",
       "      <td>POLICE OFFICER</td>\n",
       "      <td>POLICE</td>\n",
       "      <td>$87384.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32061</th>\n",
       "      <td>ZYSKOWSKI,  DARIUSZ</td>\n",
       "      <td>CHIEF DATA BASE ANALYST</td>\n",
       "      <td>DoIT</td>\n",
       "      <td>$113664.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32062</th>\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": [
       "                      Name           Position Title Department  \\\n",
       "32060  ZYRKOWSKI,  CARLO E           POLICE OFFICER     POLICE   \n",
       "32061  ZYSKOWSKI,  DARIUSZ  CHIEF DATA BASE ANALYST       DoIT   \n",
       "32062                  NaN                      NaN        NaN   \n",
       "\n",
       "      Employee Annual Salary  \n",
       "32060              $87384.00  \n",
       "32061             $113664.00  \n",
       "32062                    NaN  "
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chicago = pd.read_csv(\"pandas/chicago.csv\")\n",
    "chicago['Department']=chicago['Department'].astype('category')\n",
    "chicago.tail(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "chicago = chicago.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Hello World'"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'      Hello World   '.strip()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Hello World   '"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'      Hello World   '.lstrip()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'      Hello World'"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'      Hello World   '.rstrip()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "chicago['Name']=chicago['Name'].str.lstrip().str.rstrip().str.strip()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0            AARON,  ELVIA J\n",
       "1          AARON,  JEFFERY M\n",
       "2             AARON,  KARINA\n",
       "3        AARON,  KIMBERLEI R\n",
       "4        ABAD JR,  VICENTE M\n",
       "                ...         \n",
       "32057    ZYGADLO,  MICHAEL J\n",
       "32058     ZYGOWICZ,  PETER J\n",
       "32059      ZYMANTAS,  MARK E\n",
       "32060    ZYRKOWSKI,  CARLO E\n",
       "32061    ZYSKOWSKI,  DARIUSZ\n",
       "Name: Name, Length: 32062, dtype: object"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chicago['Name']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "chicago['Position Title']=chicago['Position Title'].str.lstrip().str.rstrip().str.strip()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0                      WATER RATE TAKER\n",
       "1                        POLICE OFFICER\n",
       "2                        POLICE OFFICER\n",
       "3              CHIEF CONTRACT EXPEDITER\n",
       "4                     CIVIL ENGINEER IV\n",
       "                      ...              \n",
       "32057    FRM OF MACHINISTS - AUTOMOTIVE\n",
       "32058                    POLICE OFFICER\n",
       "32059                    POLICE OFFICER\n",
       "32060                    POLICE OFFICER\n",
       "32061           CHIEF DATA BASE ANALYST\n",
       "Name: Position Title, Length: 32062, dtype: object"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chicago['Position Title']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## String Methods on Index and Columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "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>Position Title</th>\n",
       "      <th>Department</th>\n",
       "      <th>Employee Annual Salary</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Name</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>ZYMANTAS,  MARK E</th>\n",
       "      <td>POLICE OFFICER</td>\n",
       "      <td>POLICE</td>\n",
       "      <td>$84450.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZYRKOWSKI,  CARLO E</th>\n",
       "      <td>POLICE OFFICER</td>\n",
       "      <td>POLICE</td>\n",
       "      <td>$87384.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZYSKOWSKI,  DARIUSZ</th>\n",
       "      <td>CHIEF DATA BASE ANALYST</td>\n",
       "      <td>DoIT</td>\n",
       "      <td>$113664.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              Position Title Department Employee Annual Salary\n",
       "Name                                                                          \n",
       "ZYMANTAS,  MARK E             POLICE OFFICER     POLICE              $84450.00\n",
       "ZYRKOWSKI,  CARLO E           POLICE OFFICER     POLICE              $87384.00\n",
       "ZYSKOWSKI,  DARIUSZ  CHIEF DATA BASE ANALYST       DoIT             $113664.00"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chicago = pd.read_csv(\"pandas/chicago.csv\", index_col='Name')\n",
    "chicago['Department']=chicago['Department'].astype('category')\n",
    "chicago = chicago.dropna()\n",
    "chicago.tail(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "chicago.index=chicago.index.str.strip().str.title()"
   ]
  },
  {
   "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>Position Title</th>\n",
       "      <th>Department</th>\n",
       "      <th>Employee Annual Salary</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Name</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Aaron,  Elvia J</th>\n",
       "      <td>WATER RATE TAKER</td>\n",
       "      <td>WATER MGMNT</td>\n",
       "      <td>$90744.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Aaron,  Jeffery M</th>\n",
       "      <td>POLICE OFFICER</td>\n",
       "      <td>POLICE</td>\n",
       "      <td>$84450.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Aaron,  Karina</th>\n",
       "      <td>POLICE OFFICER</td>\n",
       "      <td>POLICE</td>\n",
       "      <td>$84450.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Aaron,  Kimberlei R</th>\n",
       "      <td>CHIEF CONTRACT EXPEDITER</td>\n",
       "      <td>GENERAL SERVICES</td>\n",
       "      <td>$89880.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Abad Jr,  Vicente M</th>\n",
       "      <td>CIVIL ENGINEER IV</td>\n",
       "      <td>WATER MGMNT</td>\n",
       "      <td>$106836.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               Position Title        Department  \\\n",
       "Name                                                              \n",
       "Aaron,  Elvia J              WATER RATE TAKER       WATER MGMNT   \n",
       "Aaron,  Jeffery M              POLICE OFFICER            POLICE   \n",
       "Aaron,  Karina                 POLICE OFFICER            POLICE   \n",
       "Aaron,  Kimberlei R  CHIEF CONTRACT EXPEDITER  GENERAL SERVICES   \n",
       "Abad Jr,  Vicente M         CIVIL ENGINEER IV       WATER MGMNT   \n",
       "\n",
       "                    Employee Annual Salary  \n",
       "Name                                        \n",
       "Aaron,  Elvia J                  $90744.00  \n",
       "Aaron,  Jeffery M                $84450.00  \n",
       "Aaron,  Karina                   $84450.00  \n",
       "Aaron,  Kimberlei R              $89880.00  \n",
       "Abad Jr,  Vicente M             $106836.00  "
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chicago.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Position Title', 'Department', 'Employee Annual Salary'], dtype='object')"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chicago.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [],
   "source": [
    "chicago.columns=chicago.columns.str.upper()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['POSITION TITLE', 'DEPARTMENT', 'EMPLOYEE ANNUAL SALARY'], dtype='object')"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chicago.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "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>POSITION TITLE</th>\n",
       "      <th>DEPARTMENT</th>\n",
       "      <th>EMPLOYEE ANNUAL SALARY</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Name</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Aaron,  Elvia J</th>\n",
       "      <td>WATER RATE TAKER</td>\n",
       "      <td>WATER MGMNT</td>\n",
       "      <td>$90744.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Aaron,  Jeffery M</th>\n",
       "      <td>POLICE OFFICER</td>\n",
       "      <td>POLICE</td>\n",
       "      <td>$84450.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Aaron,  Karina</th>\n",
       "      <td>POLICE OFFICER</td>\n",
       "      <td>POLICE</td>\n",
       "      <td>$84450.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Aaron,  Kimberlei R</th>\n",
       "      <td>CHIEF CONTRACT EXPEDITER</td>\n",
       "      <td>GENERAL SERVICES</td>\n",
       "      <td>$89880.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Abad Jr,  Vicente M</th>\n",
       "      <td>CIVIL ENGINEER IV</td>\n",
       "      <td>WATER MGMNT</td>\n",
       "      <td>$106836.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               POSITION TITLE        DEPARTMENT  \\\n",
       "Name                                                              \n",
       "Aaron,  Elvia J              WATER RATE TAKER       WATER MGMNT   \n",
       "Aaron,  Jeffery M              POLICE OFFICER            POLICE   \n",
       "Aaron,  Karina                 POLICE OFFICER            POLICE   \n",
       "Aaron,  Kimberlei R  CHIEF CONTRACT EXPEDITER  GENERAL SERVICES   \n",
       "Abad Jr,  Vicente M         CIVIL ENGINEER IV       WATER MGMNT   \n",
       "\n",
       "                    EMPLOYEE ANNUAL SALARY  \n",
       "Name                                        \n",
       "Aaron,  Elvia J                  $90744.00  \n",
       "Aaron,  Jeffery M                $84450.00  \n",
       "Aaron,  Karina                   $84450.00  \n",
       "Aaron,  Kimberlei R              $89880.00  \n",
       "Abad Jr,  Vicente M             $106836.00  "
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chicago.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Split Strings by Characters with .str.split()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "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>Name</th>\n",
       "      <th>Position Title</th>\n",
       "      <th>Department</th>\n",
       "      <th>Employee Annual Salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>32059</th>\n",
       "      <td>ZYMANTAS,  MARK E</td>\n",
       "      <td>POLICE OFFICER</td>\n",
       "      <td>POLICE</td>\n",
       "      <td>$84450.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32060</th>\n",
       "      <td>ZYRKOWSKI,  CARLO E</td>\n",
       "      <td>POLICE OFFICER</td>\n",
       "      <td>POLICE</td>\n",
       "      <td>$87384.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32061</th>\n",
       "      <td>ZYSKOWSKI,  DARIUSZ</td>\n",
       "      <td>CHIEF DATA BASE ANALYST</td>\n",
       "      <td>DoIT</td>\n",
       "      <td>$113664.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      Name           Position Title Department  \\\n",
       "32059    ZYMANTAS,  MARK E           POLICE OFFICER     POLICE   \n",
       "32060  ZYRKOWSKI,  CARLO E           POLICE OFFICER     POLICE   \n",
       "32061  ZYSKOWSKI,  DARIUSZ  CHIEF DATA BASE ANALYST       DoIT   \n",
       "\n",
       "      Employee Annual Salary  \n",
       "32059              $84450.00  \n",
       "32060              $87384.00  \n",
       "32061             $113664.00  "
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chicago = pd.read_csv(\"pandas/chicago.csv\")\n",
    "chicago['Department']=chicago['Department'].astype('category')\n",
    "chicago = chicago.dropna()\n",
    "chicago.tail(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Hello', 'How', 'are', 'you?']"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\"Hello How are you?\".split(\" \")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0            [AARON,   ELVIA J]\n",
       "1          [AARON,   JEFFERY M]\n",
       "2             [AARON,   KARINA]\n",
       "3        [AARON,   KIMBERLEI R]\n",
       "4        [ABAD JR,   VICENTE M]\n",
       "                  ...          \n",
       "32057    [ZYGADLO,   MICHAEL J]\n",
       "32058     [ZYGOWICZ,   PETER J]\n",
       "32059      [ZYMANTAS,   MARK E]\n",
       "32060    [ZYRKOWSKI,   CARLO E]\n",
       "32061    [ZYSKOWSKI,   DARIUSZ]\n",
       "Name: Name, Length: 32062, dtype: object"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chicago['Name'].str.split(\",\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0            AARON\n",
       "1            AARON\n",
       "2            AARON\n",
       "3            AARON\n",
       "4          ABAD JR\n",
       "           ...    \n",
       "32057      ZYGADLO\n",
       "32058     ZYGOWICZ\n",
       "32059     ZYMANTAS\n",
       "32060    ZYRKOWSKI\n",
       "32061    ZYSKOWSKI\n",
       "Name: Name, Length: 32062, dtype: object"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chicago['Name'].str.split(\",\").str.get(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Williams       293\n",
       "Johnson        244\n",
       "Smith          241\n",
       "Brown          185\n",
       "Jones          183\n",
       "              ... \n",
       "Wiley Earls      1\n",
       "Triggiani        1\n",
       "Gniedziejko      1\n",
       "Bila             1\n",
       "O Kane           1\n",
       "Name: Name, Length: 13829, dtype: int64"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chicago['Name'].str.split(\",\").str.get(0).str.title().value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The expand and n parameters of the str.split() Methods "
   ]
  },
  {
   "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>Name</th>\n",
       "      <th>Position Title</th>\n",
       "      <th>Department</th>\n",
       "      <th>Employee Annual Salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>32059</th>\n",
       "      <td>ZYMANTAS,  MARK E</td>\n",
       "      <td>POLICE OFFICER</td>\n",
       "      <td>POLICE</td>\n",
       "      <td>$84450.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32060</th>\n",
       "      <td>ZYRKOWSKI,  CARLO E</td>\n",
       "      <td>POLICE OFFICER</td>\n",
       "      <td>POLICE</td>\n",
       "      <td>$87384.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32061</th>\n",
       "      <td>ZYSKOWSKI,  DARIUSZ</td>\n",
       "      <td>CHIEF DATA BASE ANALYST</td>\n",
       "      <td>DoIT</td>\n",
       "      <td>$113664.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      Name           Position Title Department  \\\n",
       "32059    ZYMANTAS,  MARK E           POLICE OFFICER     POLICE   \n",
       "32060  ZYRKOWSKI,  CARLO E           POLICE OFFICER     POLICE   \n",
       "32061  ZYSKOWSKI,  DARIUSZ  CHIEF DATA BASE ANALYST       DoIT   \n",
       "\n",
       "      Employee Annual Salary  \n",
       "32059              $84450.00  \n",
       "32060              $87384.00  \n",
       "32061             $113664.00  "
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chicago = pd.read_csv(\"pandas/chicago.csv\")\n",
    "chicago['Department']=chicago['Department'].astype('category')\n",
    "chicago = chicago.dropna()\n",
    "chicago.tail(3)"
   ]
  },
  {
   "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>0</th>\n",
       "      <th>1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AARON</td>\n",
       "      <td>ELVIA J</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AARON</td>\n",
       "      <td>JEFFERY M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AARON</td>\n",
       "      <td>KARINA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AARON</td>\n",
       "      <td>KIMBERLEI R</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ABAD JR</td>\n",
       "      <td>VICENTE M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32057</th>\n",
       "      <td>ZYGADLO</td>\n",
       "      <td>MICHAEL J</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32058</th>\n",
       "      <td>ZYGOWICZ</td>\n",
       "      <td>PETER J</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32059</th>\n",
       "      <td>ZYMANTAS</td>\n",
       "      <td>MARK E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32060</th>\n",
       "      <td>ZYRKOWSKI</td>\n",
       "      <td>CARLO E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32061</th>\n",
       "      <td>ZYSKOWSKI</td>\n",
       "      <td>DARIUSZ</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>32062 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               0              1\n",
       "0          AARON        ELVIA J\n",
       "1          AARON      JEFFERY M\n",
       "2          AARON         KARINA\n",
       "3          AARON    KIMBERLEI R\n",
       "4        ABAD JR      VICENTE M\n",
       "...          ...            ...\n",
       "32057    ZYGADLO      MICHAEL J\n",
       "32058   ZYGOWICZ        PETER J\n",
       "32059   ZYMANTAS         MARK E\n",
       "32060  ZYRKOWSKI        CARLO E\n",
       "32061  ZYSKOWSKI        DARIUSZ\n",
       "\n",
       "[32062 rows x 2 columns]"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chicago['Name'].str.split(\",\",expand=True)"
   ]
  },
  {
   "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>Name</th>\n",
       "      <th>Position Title</th>\n",
       "      <th>Department</th>\n",
       "      <th>Employee Annual Salary</th>\n",
       "      <th>First Name</th>\n",
       "      <th>Last Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AARON,  ELVIA J</td>\n",
       "      <td>WATER RATE TAKER</td>\n",
       "      <td>WATER MGMNT</td>\n",
       "      <td>$90744.00</td>\n",
       "      <td>AARON</td>\n",
       "      <td>ELVIA J</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AARON,  JEFFERY M</td>\n",
       "      <td>POLICE OFFICER</td>\n",
       "      <td>POLICE</td>\n",
       "      <td>$84450.00</td>\n",
       "      <td>AARON</td>\n",
       "      <td>JEFFERY M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AARON,  KARINA</td>\n",
       "      <td>POLICE OFFICER</td>\n",
       "      <td>POLICE</td>\n",
       "      <td>$84450.00</td>\n",
       "      <td>AARON</td>\n",
       "      <td>KARINA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AARON,  KIMBERLEI R</td>\n",
       "      <td>CHIEF CONTRACT EXPEDITER</td>\n",
       "      <td>GENERAL SERVICES</td>\n",
       "      <td>$89880.00</td>\n",
       "      <td>AARON</td>\n",
       "      <td>KIMBERLEI R</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ABAD JR,  VICENTE M</td>\n",
       "      <td>CIVIL ENGINEER IV</td>\n",
       "      <td>WATER MGMNT</td>\n",
       "      <td>$106836.00</td>\n",
       "      <td>ABAD JR</td>\n",
       "      <td>VICENTE M</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32057</th>\n",
       "      <td>ZYGADLO,  MICHAEL J</td>\n",
       "      <td>FRM OF MACHINISTS - AUTOMOTIVE</td>\n",
       "      <td>GENERAL SERVICES</td>\n",
       "      <td>$99528.00</td>\n",
       "      <td>ZYGADLO</td>\n",
       "      <td>MICHAEL J</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32058</th>\n",
       "      <td>ZYGOWICZ,  PETER J</td>\n",
       "      <td>POLICE OFFICER</td>\n",
       "      <td>POLICE</td>\n",
       "      <td>$87384.00</td>\n",
       "      <td>ZYGOWICZ</td>\n",
       "      <td>PETER J</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32059</th>\n",
       "      <td>ZYMANTAS,  MARK E</td>\n",
       "      <td>POLICE OFFICER</td>\n",
       "      <td>POLICE</td>\n",
       "      <td>$84450.00</td>\n",
       "      <td>ZYMANTAS</td>\n",
       "      <td>MARK E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32060</th>\n",
       "      <td>ZYRKOWSKI,  CARLO E</td>\n",
       "      <td>POLICE OFFICER</td>\n",
       "      <td>POLICE</td>\n",
       "      <td>$87384.00</td>\n",
       "      <td>ZYRKOWSKI</td>\n",
       "      <td>CARLO E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32061</th>\n",
       "      <td>ZYSKOWSKI,  DARIUSZ</td>\n",
       "      <td>CHIEF DATA BASE ANALYST</td>\n",
       "      <td>DoIT</td>\n",
       "      <td>$113664.00</td>\n",
       "      <td>ZYSKOWSKI</td>\n",
       "      <td>DARIUSZ</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>32062 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                      Name                  Position Title        Department  \\\n",
       "0          AARON,  ELVIA J                WATER RATE TAKER       WATER MGMNT   \n",
       "1        AARON,  JEFFERY M                  POLICE OFFICER            POLICE   \n",
       "2           AARON,  KARINA                  POLICE OFFICER            POLICE   \n",
       "3      AARON,  KIMBERLEI R        CHIEF CONTRACT EXPEDITER  GENERAL SERVICES   \n",
       "4      ABAD JR,  VICENTE M               CIVIL ENGINEER IV       WATER MGMNT   \n",
       "...                    ...                             ...               ...   \n",
       "32057  ZYGADLO,  MICHAEL J  FRM OF MACHINISTS - AUTOMOTIVE  GENERAL SERVICES   \n",
       "32058   ZYGOWICZ,  PETER J                  POLICE OFFICER            POLICE   \n",
       "32059    ZYMANTAS,  MARK E                  POLICE OFFICER            POLICE   \n",
       "32060  ZYRKOWSKI,  CARLO E                  POLICE OFFICER            POLICE   \n",
       "32061  ZYSKOWSKI,  DARIUSZ         CHIEF DATA BASE ANALYST              DoIT   \n",
       "\n",
       "      Employee Annual Salary First Name      Last Name  \n",
       "0                  $90744.00      AARON        ELVIA J  \n",
       "1                  $84450.00      AARON      JEFFERY M  \n",
       "2                  $84450.00      AARON         KARINA  \n",
       "3                  $89880.00      AARON    KIMBERLEI R  \n",
       "4                 $106836.00    ABAD JR      VICENTE M  \n",
       "...                      ...        ...            ...  \n",
       "32057              $99528.00    ZYGADLO      MICHAEL J  \n",
       "32058              $87384.00   ZYGOWICZ        PETER J  \n",
       "32059              $84450.00   ZYMANTAS         MARK E  \n",
       "32060              $87384.00  ZYRKOWSKI        CARLO E  \n",
       "32061             $113664.00  ZYSKOWSKI        DARIUSZ  \n",
       "\n",
       "[32062 rows x 6 columns]"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chicago[[\"First Name\",\"Last Name\"]]= chicago['Name'].str.split(\",\",expand=True)\n",
    "chicago"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [],
   "source": [
    "chicago[['First Title', 'Remaining title']] = chicago['Position Title'].str.split(\" \",expand=True, n=1)"
   ]
  },
  {
   "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>Name</th>\n",
       "      <th>Position Title</th>\n",
       "      <th>Department</th>\n",
       "      <th>Employee Annual Salary</th>\n",
       "      <th>First Name</th>\n",
       "      <th>Last Name</th>\n",
       "      <th>First Title</th>\n",
       "      <th>Remaining title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AARON,  ELVIA J</td>\n",
       "      <td>WATER RATE TAKER</td>\n",
       "      <td>WATER MGMNT</td>\n",
       "      <td>$90744.00</td>\n",
       "      <td>AARON</td>\n",
       "      <td>ELVIA J</td>\n",
       "      <td>WATER</td>\n",
       "      <td>RATE TAKER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AARON,  JEFFERY M</td>\n",
       "      <td>POLICE OFFICER</td>\n",
       "      <td>POLICE</td>\n",
       "      <td>$84450.00</td>\n",
       "      <td>AARON</td>\n",
       "      <td>JEFFERY M</td>\n",
       "      <td>POLICE</td>\n",
       "      <td>OFFICER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AARON,  KARINA</td>\n",
       "      <td>POLICE OFFICER</td>\n",
       "      <td>POLICE</td>\n",
       "      <td>$84450.00</td>\n",
       "      <td>AARON</td>\n",
       "      <td>KARINA</td>\n",
       "      <td>POLICE</td>\n",
       "      <td>OFFICER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AARON,  KIMBERLEI R</td>\n",
       "      <td>CHIEF CONTRACT EXPEDITER</td>\n",
       "      <td>GENERAL SERVICES</td>\n",
       "      <td>$89880.00</td>\n",
       "      <td>AARON</td>\n",
       "      <td>KIMBERLEI R</td>\n",
       "      <td>CHIEF</td>\n",
       "      <td>CONTRACT EXPEDITER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ABAD JR,  VICENTE M</td>\n",
       "      <td>CIVIL ENGINEER IV</td>\n",
       "      <td>WATER MGMNT</td>\n",
       "      <td>$106836.00</td>\n",
       "      <td>ABAD JR</td>\n",
       "      <td>VICENTE M</td>\n",
       "      <td>CIVIL</td>\n",
       "      <td>ENGINEER IV</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  Name            Position Title        Department  \\\n",
       "0      AARON,  ELVIA J          WATER RATE TAKER       WATER MGMNT   \n",
       "1    AARON,  JEFFERY M            POLICE OFFICER            POLICE   \n",
       "2       AARON,  KARINA            POLICE OFFICER            POLICE   \n",
       "3  AARON,  KIMBERLEI R  CHIEF CONTRACT EXPEDITER  GENERAL SERVICES   \n",
       "4  ABAD JR,  VICENTE M         CIVIL ENGINEER IV       WATER MGMNT   \n",
       "\n",
       "  Employee Annual Salary First Name      Last Name First Title  \\\n",
       "0              $90744.00      AARON        ELVIA J       WATER   \n",
       "1              $84450.00      AARON      JEFFERY M      POLICE   \n",
       "2              $84450.00      AARON         KARINA      POLICE   \n",
       "3              $89880.00      AARON    KIMBERLEI R       CHIEF   \n",
       "4             $106836.00    ABAD JR      VICENTE M       CIVIL   \n",
       "\n",
       "      Remaining title  \n",
       "0          RATE TAKER  \n",
       "1             OFFICER  \n",
       "2             OFFICER  \n",
       "3  CONTRACT EXPEDITER  \n",
       "4         ENGINEER IV  "
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chicago.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}