{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "HEiOAwQPW0qb"
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "j7whidfaYjns"
   },
   "outputs": [],
   "source": [
    "file_url = '../dataset/officers.csv'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "nFMz2jNVt-xy"
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv(file_url)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 204
    },
    "colab_type": "code",
    "id": "n0xY9ATvN6-M",
    "outputId": "91b5b5ed-aaa2-4d1c-b147-96f4873fcc61"
   },
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 102
    },
    "colab_type": "code",
    "id": "recrA9gLBmep",
    "outputId": "8aa238bb-d5c5-4c07-ea42-83cb18169a91"
   },
   "outputs": [],
   "source": [
    "df['State'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 235
    },
    "colab_type": "code",
    "id": "v9LJ8sZrRxuF",
    "outputId": "cfd4b5ad-bf32-471a-b2de-c4b5c967f1be"
   },
   "outputs": [],
   "source": [
    "df[df['State'].str.contains('il', na=False)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 527
    },
    "colab_type": "code",
    "id": "km1miLj2Sdbz",
    "outputId": "cca2a132-cf61-46ce-dbe0-d12c86ce81d6"
   },
   "outputs": [],
   "source": [
    "for state in ['Il', 'iL', 'Il']:\n",
    "  print(df.loc[df['State'] == state, ['City', 'State']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "gtzB7K0K4LBq"
   },
   "outputs": [],
   "source": [
    "il_mask = df['State'].isin(['il', 'Il', 'iL', 'Il'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 34
    },
    "colab_type": "code",
    "id": "wGEy6Ux17QNY",
    "outputId": "4634a503-90d4-4ff9-c0db-f4cda2d33ae6"
   },
   "outputs": [],
   "source": [
    "il_mask.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "iTERX9A1SdZD"
   },
   "outputs": [],
   "source": [
    "df.loc[il_mask, 'State'] = 'IL'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 102
    },
    "colab_type": "code",
    "id": "HRWrLS1y7mn5",
    "outputId": "632cf119-0b22-45d8-a3bc-a8ee330f27f9"
   },
   "outputs": [],
   "source": [
    "df['State'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 111
    },
    "colab_type": "code",
    "id": "vJWBdglfSg32",
    "outputId": "ca372632-eb97-4bc7-923e-5f1c25d59f75"
   },
   "outputs": [],
   "source": [
    "df.loc[df['State'] == 'II',]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 119
    },
    "colab_type": "code",
    "id": "97iBIGphCH7D",
    "outputId": "5f06ec34-1b69-4f3d-bfd2-046f9c01d707"
   },
   "outputs": [],
   "source": [
    "for val in ['I', '8I', '60']:\n",
    "  print(df.loc[df['State'] == val, ['City', 'State']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "ooHQO3ynBwHJ"
   },
   "outputs": [],
   "source": [
    "for val in ['II', 'I', '8I', '60']:\n",
    "  df.loc[df['State'] == val, 'State'] = 'IL'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 102
    },
    "colab_type": "code",
    "id": "6S6K8zSJZPxy",
    "outputId": "670b4528-3740-4e51-89c1-d3386c98055b"
   },
   "outputs": [],
   "source": [
    "df['State'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 102
    },
    "colab_type": "code",
    "id": "owFR6OfLct8P",
    "outputId": "19b59a9f-39ad-4278-b25b-0a780399232c"
   },
   "outputs": [],
   "source": [
    "for val in ['In', 'ng']:\n",
    "  print(df.loc[df['State'] == val, ['City', 'State']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "OzdeI3BEdJ_C"
   },
   "outputs": [],
   "source": [
    "df.loc[df['State'].str.contains('In', na=False), 'State'] = 'IN'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 85
    },
    "colab_type": "code",
    "id": "5wfMCwspd2YY",
    "outputId": "9a270a04-1116-46e4-dd89-adb7f042e358"
   },
   "outputs": [],
   "source": [
    "df['State'].unique()"
   ]
  }
 ],
 "metadata": {
  "colab": {
   "collapsed_sections": [],
   "name": "Exercise11.03.ipynb",
   "provenance": []
  },
  "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.8.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}