220461_ishan_dandwani_02.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "3moDvXxH1cWb"
      },
      "source": [
        "# SF Salaries Exercise \n",
        "\n",
        "Welcome to a quick exercise for you to practice your pandas skills! We will be using the [SF Salaries Dataset](https://www.kaggle.com/kaggle/sf-salaries) from Kaggle! Just follow along and complete the tasks outlined in bold below. The tasks will get harder and harder as you go along."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "aUkS3-qb1cWf"
      },
      "source": [
        "** Import pandas as pd.**"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "collapsed": true,
        "id": "tBfMNGIE1cWf"
      },
      "outputs": [],
      "source": [
        "import pandas as pd"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "eOyUCUig1cWg"
      },
      "source": [
        "** Read Salaries.csv as a dataframe called sal.**"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 641
        },
        "id": "Z37m33-O1cWg",
        "outputId": "3d96a5d7-586f-434f-a22b-fbcc32d48389"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "            Id       EmployeeName  \\\n",
              "0            1     NATHANIEL FORD   \n",
              "1            2       GARY JIMENEZ   \n",
              "2            3     ALBERT PARDINI   \n",
              "3            4  CHRISTOPHER CHONG   \n",
              "4            5    PATRICK GARDNER   \n",
              "...        ...                ...   \n",
              "148649  148650      Roy I Tillery   \n",
              "148650  148651       Not provided   \n",
              "148651  148652       Not provided   \n",
              "148652  148653       Not provided   \n",
              "148653  148654          Joe Lopez   \n",
              "\n",
              "                                              JobTitle    BasePay  \\\n",
              "0       GENERAL MANAGER-METROPOLITAN TRANSIT AUTHORITY  167411.18   \n",
              "1                      CAPTAIN III (POLICE DEPARTMENT)  155966.02   \n",
              "2                      CAPTAIN III (POLICE DEPARTMENT)  212739.13   \n",
              "3                 WIRE ROPE CABLE MAINTENANCE MECHANIC   77916.00   \n",
              "4         DEPUTY CHIEF OF DEPARTMENT,(FIRE DEPARTMENT)  134401.60   \n",
              "...                                                ...        ...   \n",
              "148649                                       Custodian       0.00   \n",
              "148650                                    Not provided        NaN   \n",
              "148651                                    Not provided        NaN   \n",
              "148652                                    Not provided        NaN   \n",
              "148653                      Counselor, Log Cabin Ranch       0.00   \n",
              "\n",
              "        OvertimePay   OtherPay  Benefits   TotalPay  TotalPayBenefits  Year  \\\n",
              "0              0.00  400184.25       NaN  567595.43         567595.43  2011   \n",
              "1         245131.88  137811.38       NaN  538909.28         538909.28  2011   \n",
              "2         106088.18   16452.60       NaN  335279.91         335279.91  2011   \n",
              "3          56120.71  198306.90       NaN  332343.61         332343.61  2011   \n",
              "4           9737.00  182234.59       NaN  326373.19         326373.19  2011   \n",
              "...             ...        ...       ...        ...               ...   ...   \n",
              "148649         0.00       0.00       0.0       0.00              0.00  2014   \n",
              "148650          NaN        NaN       NaN       0.00              0.00  2014   \n",
              "148651          NaN        NaN       NaN       0.00              0.00  2014   \n",
              "148652          NaN        NaN       NaN       0.00              0.00  2014   \n",
              "148653         0.00    -618.13       0.0    -618.13           -618.13  2014   \n",
              "\n",
              "        Notes         Agency  Status  \n",
              "0         NaN  San Francisco     NaN  \n",
              "1         NaN  San Francisco     NaN  \n",
              "2         NaN  San Francisco     NaN  \n",
              "3         NaN  San Francisco     NaN  \n",
              "4         NaN  San Francisco     NaN  \n",
              "...       ...            ...     ...  \n",
              "148649    NaN  San Francisco     NaN  \n",
              "148650    NaN  San Francisco     NaN  \n",
              "148651    NaN  San Francisco     NaN  \n",
              "148652    NaN  San Francisco     NaN  \n",
              "148653    NaN  San Francisco     NaN  \n",
              "\n",
              "[148654 rows x 13 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-1ddd6fd3-3295-4fa7-8d7d-a3cd06e04076\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>Id</th>\n",
              "      <th>EmployeeName</th>\n",
              "      <th>JobTitle</th>\n",
              "      <th>BasePay</th>\n",
              "      <th>OvertimePay</th>\n",
              "      <th>OtherPay</th>\n",
              "      <th>Benefits</th>\n",
              "      <th>TotalPay</th>\n",
              "      <th>TotalPayBenefits</th>\n",
              "      <th>Year</th>\n",
              "      <th>Notes</th>\n",
              "      <th>Agency</th>\n",
              "      <th>Status</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>1</td>\n",
              "      <td>NATHANIEL FORD</td>\n",
              "      <td>GENERAL MANAGER-METROPOLITAN TRANSIT AUTHORITY</td>\n",
              "      <td>167411.18</td>\n",
              "      <td>0.00</td>\n",
              "      <td>400184.25</td>\n",
              "      <td>NaN</td>\n",
              "      <td>567595.43</td>\n",
              "      <td>567595.43</td>\n",
              "      <td>2011</td>\n",
              "      <td>NaN</td>\n",
              "      <td>San Francisco</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2</td>\n",
              "      <td>GARY JIMENEZ</td>\n",
              "      <td>CAPTAIN III (POLICE DEPARTMENT)</td>\n",
              "      <td>155966.02</td>\n",
              "      <td>245131.88</td>\n",
              "      <td>137811.38</td>\n",
              "      <td>NaN</td>\n",
              "      <td>538909.28</td>\n",
              "      <td>538909.28</td>\n",
              "      <td>2011</td>\n",
              "      <td>NaN</td>\n",
              "      <td>San Francisco</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>3</td>\n",
              "      <td>ALBERT PARDINI</td>\n",
              "      <td>CAPTAIN III (POLICE DEPARTMENT)</td>\n",
              "      <td>212739.13</td>\n",
              "      <td>106088.18</td>\n",
              "      <td>16452.60</td>\n",
              "      <td>NaN</td>\n",
              "      <td>335279.91</td>\n",
              "      <td>335279.91</td>\n",
              "      <td>2011</td>\n",
              "      <td>NaN</td>\n",
              "      <td>San Francisco</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>4</td>\n",
              "      <td>CHRISTOPHER CHONG</td>\n",
              "      <td>WIRE ROPE CABLE MAINTENANCE MECHANIC</td>\n",
              "      <td>77916.00</td>\n",
              "      <td>56120.71</td>\n",
              "      <td>198306.90</td>\n",
              "      <td>NaN</td>\n",
              "      <td>332343.61</td>\n",
              "      <td>332343.61</td>\n",
              "      <td>2011</td>\n",
              "      <td>NaN</td>\n",
              "      <td>San Francisco</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>5</td>\n",
              "      <td>PATRICK GARDNER</td>\n",
              "      <td>DEPUTY CHIEF OF DEPARTMENT,(FIRE DEPARTMENT)</td>\n",
              "      <td>134401.60</td>\n",
              "      <td>9737.00</td>\n",
              "      <td>182234.59</td>\n",
              "      <td>NaN</td>\n",
              "      <td>326373.19</td>\n",
              "      <td>326373.19</td>\n",
              "      <td>2011</td>\n",
              "      <td>NaN</td>\n",
              "      <td>San Francisco</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>148649</th>\n",
              "      <td>148650</td>\n",
              "      <td>Roy I Tillery</td>\n",
              "      <td>Custodian</td>\n",
              "      <td>0.00</td>\n",
              "      <td>0.00</td>\n",
              "      <td>0.00</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.00</td>\n",
              "      <td>0.00</td>\n",
              "      <td>2014</td>\n",
              "      <td>NaN</td>\n",
              "      <td>San Francisco</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>148650</th>\n",
              "      <td>148651</td>\n",
              "      <td>Not provided</td>\n",
              "      <td>Not provided</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>0.00</td>\n",
              "      <td>0.00</td>\n",
              "      <td>2014</td>\n",
              "      <td>NaN</td>\n",
              "      <td>San Francisco</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>148651</th>\n",
              "      <td>148652</td>\n",
              "      <td>Not provided</td>\n",
              "      <td>Not provided</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>0.00</td>\n",
              "      <td>0.00</td>\n",
              "      <td>2014</td>\n",
              "      <td>NaN</td>\n",
              "      <td>San Francisco</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>148652</th>\n",
              "      <td>148653</td>\n",
              "      <td>Not provided</td>\n",
              "      <td>Not provided</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>0.00</td>\n",
              "      <td>0.00</td>\n",
              "      <td>2014</td>\n",
              "      <td>NaN</td>\n",
              "      <td>San Francisco</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>148653</th>\n",
              "      <td>148654</td>\n",
              "      <td>Joe Lopez</td>\n",
              "      <td>Counselor, Log Cabin Ranch</td>\n",
              "      <td>0.00</td>\n",
              "      <td>0.00</td>\n",
              "      <td>-618.13</td>\n",
              "      <td>0.0</td>\n",
              "      <td>-618.13</td>\n",
              "      <td>-618.13</td>\n",
              "      <td>2014</td>\n",
              "      <td>NaN</td>\n",
              "      <td>San Francisco</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>148654 rows × 13 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-1ddd6fd3-3295-4fa7-8d7d-a3cd06e04076')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-1ddd6fd3-3295-4fa7-8d7d-a3cd06e04076 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-1ddd6fd3-3295-4fa7-8d7d-a3cd06e04076');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 26
        }
      ],
      "source": [
        "sal=pd.read_csv('Salaries.csv')\n",
        "sal"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "EUYk9kHC1cWg"
      },
      "source": [
        "** Check the head of the DataFrame. **"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "id": "nnDEqd1S1cWh",
        "outputId": "10187464-9e1f-48c7-9c7e-af7fee271225"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "   Id       EmployeeName                                        JobTitle  \\\n",
              "0   1     NATHANIEL FORD  GENERAL MANAGER-METROPOLITAN TRANSIT AUTHORITY   \n",
              "1   2       GARY JIMENEZ                 CAPTAIN III (POLICE DEPARTMENT)   \n",
              "2   3     ALBERT PARDINI                 CAPTAIN III (POLICE DEPARTMENT)   \n",
              "3   4  CHRISTOPHER CHONG            WIRE ROPE CABLE MAINTENANCE MECHANIC   \n",
              "4   5    PATRICK GARDNER    DEPUTY CHIEF OF DEPARTMENT,(FIRE DEPARTMENT)   \n",
              "\n",
              "     BasePay  OvertimePay   OtherPay  Benefits   TotalPay  TotalPayBenefits  \\\n",
              "0  167411.18         0.00  400184.25       NaN  567595.43         567595.43   \n",
              "1  155966.02    245131.88  137811.38       NaN  538909.28         538909.28   \n",
              "2  212739.13    106088.18   16452.60       NaN  335279.91         335279.91   \n",
              "3   77916.00     56120.71  198306.90       NaN  332343.61         332343.61   \n",
              "4  134401.60      9737.00  182234.59       NaN  326373.19         326373.19   \n",
              "\n",
              "   Year  Notes         Agency  Status  \n",
              "0  2011    NaN  San Francisco     NaN  \n",
              "1  2011    NaN  San Francisco     NaN  \n",
              "2  2011    NaN  San Francisco     NaN  \n",
              "3  2011    NaN  San Francisco     NaN  \n",
              "4  2011    NaN  San Francisco     NaN  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-f375fb18-05cc-444e-80ff-c001f13005eb\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>Id</th>\n",
              "      <th>EmployeeName</th>\n",
              "      <th>JobTitle</th>\n",
              "      <th>BasePay</th>\n",
              "      <th>OvertimePay</th>\n",
              "      <th>OtherPay</th>\n",
              "      <th>Benefits</th>\n",
              "      <th>TotalPay</th>\n",
              "      <th>TotalPayBenefits</th>\n",
              "      <th>Year</th>\n",
              "      <th>Notes</th>\n",
              "      <th>Agency</th>\n",
              "      <th>Status</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>1</td>\n",
              "      <td>NATHANIEL FORD</td>\n",
              "      <td>GENERAL MANAGER-METROPOLITAN TRANSIT AUTHORITY</td>\n",
              "      <td>167411.18</td>\n",
              "      <td>0.00</td>\n",
              "      <td>400184.25</td>\n",
              "      <td>NaN</td>\n",
              "      <td>567595.43</td>\n",
              "      <td>567595.43</td>\n",
              "      <td>2011</td>\n",
              "      <td>NaN</td>\n",
              "      <td>San Francisco</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2</td>\n",
              "      <td>GARY JIMENEZ</td>\n",
              "      <td>CAPTAIN III (POLICE DEPARTMENT)</td>\n",
              "      <td>155966.02</td>\n",
              "      <td>245131.88</td>\n",
              "      <td>137811.38</td>\n",
              "      <td>NaN</td>\n",
              "      <td>538909.28</td>\n",
              "      <td>538909.28</td>\n",
              "      <td>2011</td>\n",
              "      <td>NaN</td>\n",
              "      <td>San Francisco</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>3</td>\n",
              "      <td>ALBERT PARDINI</td>\n",
              "      <td>CAPTAIN III (POLICE DEPARTMENT)</td>\n",
              "      <td>212739.13</td>\n",
              "      <td>106088.18</td>\n",
              "      <td>16452.60</td>\n",
              "      <td>NaN</td>\n",
              "      <td>335279.91</td>\n",
              "      <td>335279.91</td>\n",
              "      <td>2011</td>\n",
              "      <td>NaN</td>\n",
              "      <td>San Francisco</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>4</td>\n",
              "      <td>CHRISTOPHER CHONG</td>\n",
              "      <td>WIRE ROPE CABLE MAINTENANCE MECHANIC</td>\n",
              "      <td>77916.00</td>\n",
              "      <td>56120.71</td>\n",
              "      <td>198306.90</td>\n",
              "      <td>NaN</td>\n",
              "      <td>332343.61</td>\n",
              "      <td>332343.61</td>\n",
              "      <td>2011</td>\n",
              "      <td>NaN</td>\n",
              "      <td>San Francisco</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>5</td>\n",
              "      <td>PATRICK GARDNER</td>\n",
              "      <td>DEPUTY CHIEF OF DEPARTMENT,(FIRE DEPARTMENT)</td>\n",
              "      <td>134401.60</td>\n",
              "      <td>9737.00</td>\n",
              "      <td>182234.59</td>\n",
              "      <td>NaN</td>\n",
              "      <td>326373.19</td>\n",
              "      <td>326373.19</td>\n",
              "      <td>2011</td>\n",
              "      <td>NaN</td>\n",
              "      <td>San Francisco</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-f375fb18-05cc-444e-80ff-c001f13005eb')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-f375fb18-05cc-444e-80ff-c001f13005eb button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-f375fb18-05cc-444e-80ff-c001f13005eb');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 27
        }
      ],
      "source": [
        "sal.head()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Zaf9bLMj1cWi"
      },
      "source": [
        "** Use the .info() method to find out how many entries there are.**"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Td7Tl2oY1cWj",
        "outputId": "2644cbde-d788-464f-e75e-b857e5a6ebee"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "RangeIndex: 148654 entries, 0 to 148653\n",
            "Data columns (total 13 columns):\n",
            " #   Column            Non-Null Count   Dtype  \n",
            "---  ------            --------------   -----  \n",
            " 0   Id                148654 non-null  int64  \n",
            " 1   EmployeeName      148654 non-null  object \n",
            " 2   JobTitle          148654 non-null  object \n",
            " 3   BasePay           148045 non-null  float64\n",
            " 4   OvertimePay       148650 non-null  float64\n",
            " 5   OtherPay          148650 non-null  float64\n",
            " 6   Benefits          112491 non-null  float64\n",
            " 7   TotalPay          148654 non-null  float64\n",
            " 8   TotalPayBenefits  148654 non-null  float64\n",
            " 9   Year              148654 non-null  int64  \n",
            " 10  Notes             0 non-null       float64\n",
            " 11  Agency            148654 non-null  object \n",
            " 12  Status            0 non-null       float64\n",
            "dtypes: float64(8), int64(2), object(3)\n",
            "memory usage: 14.7+ MB\n"
          ]
        }
      ],
      "source": [
        "sal.info()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "48iO_DA71cWj"
      },
      "source": [
        "**What is the average BasePay ?**"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "3URxWPEp1cWk",
        "outputId": "d52058c9-fe62-482e-a080-7066ac2d328a"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "<ipython-input-29-0cec5eafbb51>:1: FutureWarning: The default value of numeric_only in DataFrame.mean is deprecated. In a future version, it will default to False. In addition, specifying 'numeric_only=None' is deprecated. Select only valid columns or specify the value of numeric_only to silence this warning.\n",
            "  sal.mean(axis=0)['BasePay']\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "66325.4488404877"
            ]
          },
          "metadata": {},
          "execution_count": 29
        }
      ],
      "source": [
        "sal.mean(axis=0)['BasePay']"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cMbuxFUm1cWk"
      },
      "source": [
        "** What is the highest amount of OvertimePay in the dataset ? **"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "NJYuvn-U1cWk",
        "outputId": "e572c0e9-c8f8-4838-d1eb-66621bbd5d1f"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "245131.88"
            ]
          },
          "metadata": {},
          "execution_count": 30
        }
      ],
      "source": [
        "sal.max(axis=0)['OvertimePay']"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "CUjWY96x1cWl"
      },
      "source": [
        "** What is the job title of  JOSEPH DRISCOLL ? Note: Use all caps, otherwise you may get an answer that doesn't match up (there is also a lowercase Joseph Driscoll). **"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "-97zIUd81cWl",
        "outputId": "4874f896-4573-46d8-c5c1-272f3218a70e"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "24    CAPTAIN, FIRE SUPPRESSION\n",
              "Name: JobTitle, dtype: object"
            ]
          },
          "metadata": {},
          "execution_count": 31
        }
      ],
      "source": [
        "sal.loc[sal['EmployeeName'] =='JOSEPH DRISCOLL']['JobTitle']"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "7TVyR1Rr1cWl"
      },
      "source": [
        "** How much does JOSEPH DRISCOLL make (including benefits)? **"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "UcGE30Pi1cWl",
        "outputId": "725758a4-c979-4179-c8d1-833e41f9b6a3"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "24    270324.91\n",
              "Name: TotalPayBenefits, dtype: float64"
            ]
          },
          "metadata": {},
          "execution_count": 32
        }
      ],
      "source": [
        "sal.loc[sal['EmployeeName'] =='JOSEPH DRISCOLL']['TotalPayBenefits']"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "CsqlR4un1cWm"
      },
      "source": [
        "** What is the name of highest paid person (including benefits)?**"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 35
        },
        "id": "4YDfJ0jg1cWm",
        "outputId": "1728be6d-b1eb-4535-e20c-6f84a1b8bbb8"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "'NATHANIEL FORD'"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            }
          },
          "metadata": {},
          "execution_count": 33
        }
      ],
      "source": [
        "sal.loc[sal['TotalPayBenefits'].idxmax()]['EmployeeName']"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "H2bToW361cWm"
      },
      "source": [
        "** What is the name of lowest paid person (including benefits)? Do you notice something strange about how much he or she is paid?**"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 35
        },
        "id": "RjxclgEM1cWm",
        "outputId": "b2bec521-6d06-468e-e650-770c9b996725"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "'Joe Lopez'"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            }
          },
          "metadata": {},
          "execution_count": 34
        }
      ],
      "source": [
        "sal.loc[sal['TotalPayBenefits'].idxmin()]['EmployeeName']"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "7C_FCin51cWn"
      },
      "source": [
        "** What was the average (mean) BasePay of all employees per year? (2011-2014) ? **"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "gKr3c-KJ1cWn",
        "outputId": "657016c6-2c59-416d-842b-451058def802"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "Year\n",
              "2011    63595.956517\n",
              "2012    65436.406857\n",
              "2013    69630.030216\n",
              "2014    66564.421924\n",
              "Name: BasePay, dtype: float64"
            ]
          },
          "metadata": {},
          "execution_count": 35
        }
      ],
      "source": [
        "sal.groupby('Year')[\"BasePay\"].mean()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "50M6MaYH1cWn"
      },
      "source": [
        "** How many unique job titles are there? **"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "hPclxNZe1cWo",
        "outputId": "d13a50e9-c5a2-4f77-ab5b-1e7686020ff7"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "2159"
            ]
          },
          "metadata": {},
          "execution_count": 36
        }
      ],
      "source": [
        "sal[\"JobTitle\"].nunique()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "OrQjmvtY1cWo"
      },
      "source": [
        "** What are the top 5 most common jobs? **"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "vEnE9wTb1cWo",
        "outputId": "0c05b087-1871-4362-8a85-51030098d115"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "Transit Operator                7036\n",
              "Special Nurse                   4389\n",
              "Registered Nurse                3736\n",
              "Public Svc Aide-Public Works    2518\n",
              "Police Officer 3                2421\n",
              "Name: JobTitle, dtype: int64"
            ]
          },
          "metadata": {},
          "execution_count": 37
        }
      ],
      "source": [
        "sal[\"JobTitle\"].value_counts().head()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "jqEovSQ11cWp"
      },
      "source": [
        "** How many Job Titles were represented by only one person in 2013? (e.g. Job Titles with only one occurence in 2013?) **"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "2P0CQ3ML1cWp",
        "outputId": "713a4995-0925-4f6b-9d9d-4a84939f9ca3"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "202"
            ]
          },
          "metadata": {},
          "execution_count": 38
        }
      ],
      "source": [
        "sum(sal[sal['Year']==2013]['JobTitle'].value_counts()==1)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "vvE08U9r1cWp"
      },
      "source": [
        "** How many people have the word Chief in their job title? (This is pretty tricky) **"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "collapsed": true,
        "id": "K9mVr6hT1cWq"
      },
      "outputs": [],
      "source": [
        "def chief(JobTitle):\n",
        "    if 'chief' in JobTitle.lower().split():\n",
        "        return True\n",
        "    else:\n",
        "        return False"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "JvigTqiP1cWq",
        "outputId": "97b785a3-5e43-481a-f7c5-5a3f86cdf28c"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "477"
            ]
          },
          "metadata": {},
          "execution_count": 40
        }
      ],
      "source": [
        "sum(sal['JobTitle'].apply(lambda t: chief(t)))"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "uu16PIeW1cWq"
      },
      "source": [
        "** Bonus: Is there a correlation between length of the Job Title string and Salary? **"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 42,
      "metadata": {
        "id": "OFsIjz5k1cWq"
      },
      "outputs": [],
      "source": []
    },
    {
      "cell_type": "code",
      "execution_count": 42,
      "metadata": {
        "id": "eXRcDgWQ1cWq"
      },
      "outputs": [],
      "source": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "80kuqFoy1cWr"
      },
      "source": [
        "# Great Job!"
      ]
    }
  ],
  "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.8.5"
    },
    "colab": {
      "provenance": []
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}