12_SF_Salaries_Exercise.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "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.5.1"
    },
    "colab": {
      "name": "12_SF Salaries Exercise.ipynb",
      "provenance": [],
      "include_colab_link": true
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/mohd-faizy/_Pandas_CodeDump/blob/master/12_SF_Salaries_Exercise.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "hqbGQZzoiQJh"
      },
      "source": [
        "# SF Salaries \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": "WKGE2q6WkSB3"
      },
      "source": [
        "```python\r\n",
        "# Create api key -> account ->Create New Token \r\n",
        "# .json file is genrated \r\n",
        "\r\n",
        "!pip install -q kaggle\r\n",
        "from google.colab import files \r\n",
        "files.upload() #upload kaggle.json \r\n",
        "\r\n",
        "!mkdir -p ~/.kaggle \r\n",
        "!cp kaggle.json ~/.kaggle/ \r\n",
        "!ls ~/.kaggle \r\n",
        "!chmod 600 /root/.kaggle/kaggle.json \r\n",
        "!kaggle kernels list — user YOUR_USER — sort-by dateRun \r\n",
        "!kaggle competitions download -c DATASET \r\n",
        "!unzip -q train.csv.zip -d . \r\n",
        "!unzip -q test.csv.zip -d . \r\n",
        "!ls\r\n",
        "```"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "LEGH1frAihDc"
      },
      "source": [
        "#pip install -q kaggle"
      ],
      "execution_count": 1,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "4oJWAnlxjgAr"
      },
      "source": [
        "#from google.colab import files \r\n",
        "#files.upload() #upload kaggle.json "
      ],
      "execution_count": 2,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_6FxlkEQkhVf"
      },
      "source": [
        "#!mkdir -p ~/.kaggle \r\n",
        "#!cp kaggle.json ~/.kaggle/ \r\n",
        "#!ls ~/.kaggle \r\n",
        "#!chmod 600 /root/.kaggle/kaggle.json \r\n",
        "#API command"
      ],
      "execution_count": 3,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "f9xqHGo8ljHX"
      },
      "source": [
        "#!unzip -uq '/content/sf-salaries.zip' -d '/content/'"
      ],
      "execution_count": 4,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "1uSnyPAiyFSr"
      },
      "source": [
        "## Featching the data from google drive"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "eqm3VS5Slcpw",
        "outputId": "455c0cfb-dbca-444a-f5c4-5d79abbba21f"
      },
      "source": [
        "! gdown --id 1pHoO9ZDTIujnv5ZT5AEyb7KcPRggODA5"
      ],
      "execution_count": 5,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Downloading...\n",
            "From: https://drive.google.com/uc?id=1pHoO9ZDTIujnv5ZT5AEyb7KcPRggODA5\n",
            "To: /content/Salaries.csv\n",
            "16.2MB [00:00, 39.1MB/s]\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "e5c4miYpwJ-0",
        "outputId": "ce1a8607-9f63-4bcf-daf5-3d55e437205d"
      },
      "source": [
        "ls"
      ],
      "execution_count": 6,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Salaries.csv  \u001b[0m\u001b[01;34msample_data\u001b[0m/\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ytQdrAnziQJj"
      },
      "source": [
        "** Import pandas as pd.**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "collapsed": true,
        "id": "oG8YY_7ViQJk"
      },
      "source": [
        "import pandas as pd"
      ],
      "execution_count": 7,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "R5IbY4iNiQJl"
      },
      "source": [
        "** Read Salaries.csv as a dataframe called sal.**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "pupLOsJdiQJm"
      },
      "source": [
        "df_sal = pd.read_csv('/content/Salaries.csv')"
      ],
      "execution_count": 8,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Cpjg9h-MiQJm"
      },
      "source": [
        "** Check the head of the DataFrame. **"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 447
        },
        "id": "tpZF5ZpNiQJn",
        "outputId": "90ca85e3-fbc5-4db1-8e94-094e1d8155a6"
      },
      "source": [
        "df_sal.head()"
      ],
      "execution_count": 9,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>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>"
            ],
            "text/plain": [
              "   Id       EmployeeName  ...         Agency  Status\n",
              "0   1     NATHANIEL FORD  ...  San Francisco     NaN\n",
              "1   2       GARY JIMENEZ  ...  San Francisco     NaN\n",
              "2   3     ALBERT PARDINI  ...  San Francisco     NaN\n",
              "3   4  CHRISTOPHER CHONG  ...  San Francisco     NaN\n",
              "4   5    PATRICK GARDNER  ...  San Francisco     NaN\n",
              "\n",
              "[5 rows x 13 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 9
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "SfF1EdBsiQJo"
      },
      "source": [
        "** Use the .info() method to find out how many entries there are.**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "yAixPAUOiQJp",
        "outputId": "b2285190-ae0c-440c-9c38-4d4663bbf872"
      },
      "source": [
        "df_sal.info() # 148654 Entries"
      ],
      "execution_count": 10,
      "outputs": [
        {
          "output_type": "stream",
          "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"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 287
        },
        "id": "Jot2nIaviQJq",
        "outputId": "8f1039ba-3f99-49b9-c062-52d35e2c7834"
      },
      "source": [
        "df_sal.describe()"
      ],
      "execution_count": 11,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>Id</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>Status</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>count</th>\n",
              "      <td>148654.000000</td>\n",
              "      <td>148045.000000</td>\n",
              "      <td>148650.000000</td>\n",
              "      <td>148650.000000</td>\n",
              "      <td>112491.000000</td>\n",
              "      <td>148654.000000</td>\n",
              "      <td>148654.000000</td>\n",
              "      <td>148654.000000</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>mean</th>\n",
              "      <td>74327.500000</td>\n",
              "      <td>66325.448841</td>\n",
              "      <td>5066.059886</td>\n",
              "      <td>3648.767297</td>\n",
              "      <td>25007.893151</td>\n",
              "      <td>74768.321972</td>\n",
              "      <td>93692.554811</td>\n",
              "      <td>2012.522643</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>std</th>\n",
              "      <td>42912.857795</td>\n",
              "      <td>42764.635495</td>\n",
              "      <td>11454.380559</td>\n",
              "      <td>8056.601866</td>\n",
              "      <td>15402.215858</td>\n",
              "      <td>50517.005274</td>\n",
              "      <td>62793.533483</td>\n",
              "      <td>1.117538</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>min</th>\n",
              "      <td>1.000000</td>\n",
              "      <td>-166.010000</td>\n",
              "      <td>-0.010000</td>\n",
              "      <td>-7058.590000</td>\n",
              "      <td>-33.890000</td>\n",
              "      <td>-618.130000</td>\n",
              "      <td>-618.130000</td>\n",
              "      <td>2011.000000</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>25%</th>\n",
              "      <td>37164.250000</td>\n",
              "      <td>33588.200000</td>\n",
              "      <td>0.000000</td>\n",
              "      <td>0.000000</td>\n",
              "      <td>11535.395000</td>\n",
              "      <td>36168.995000</td>\n",
              "      <td>44065.650000</td>\n",
              "      <td>2012.000000</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>50%</th>\n",
              "      <td>74327.500000</td>\n",
              "      <td>65007.450000</td>\n",
              "      <td>0.000000</td>\n",
              "      <td>811.270000</td>\n",
              "      <td>28628.620000</td>\n",
              "      <td>71426.610000</td>\n",
              "      <td>92404.090000</td>\n",
              "      <td>2013.000000</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>75%</th>\n",
              "      <td>111490.750000</td>\n",
              "      <td>94691.050000</td>\n",
              "      <td>4658.175000</td>\n",
              "      <td>4236.065000</td>\n",
              "      <td>35566.855000</td>\n",
              "      <td>105839.135000</td>\n",
              "      <td>132876.450000</td>\n",
              "      <td>2014.000000</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>max</th>\n",
              "      <td>148654.000000</td>\n",
              "      <td>319275.010000</td>\n",
              "      <td>245131.880000</td>\n",
              "      <td>400184.250000</td>\n",
              "      <td>96570.660000</td>\n",
              "      <td>567595.430000</td>\n",
              "      <td>567595.430000</td>\n",
              "      <td>2014.000000</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                  Id        BasePay  ...  Notes  Status\n",
              "count  148654.000000  148045.000000  ...    0.0     0.0\n",
              "mean    74327.500000   66325.448841  ...    NaN     NaN\n",
              "std     42912.857795   42764.635495  ...    NaN     NaN\n",
              "min         1.000000    -166.010000  ...    NaN     NaN\n",
              "25%     37164.250000   33588.200000  ...    NaN     NaN\n",
              "50%     74327.500000   65007.450000  ...    NaN     NaN\n",
              "75%    111490.750000   94691.050000  ...    NaN     NaN\n",
              "max    148654.000000  319275.010000  ...    NaN     NaN\n",
              "\n",
              "[8 rows x 10 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 11
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "coXKgfldpLgW",
        "outputId": "b5c9cb44-c7c1-4325-c199-f6d2c0da0f87"
      },
      "source": [
        "df_sal.isna().sum()"
      ],
      "execution_count": 12,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "Id                       0\n",
              "EmployeeName             0\n",
              "JobTitle                 0\n",
              "BasePay                609\n",
              "OvertimePay              4\n",
              "OtherPay                 4\n",
              "Benefits             36163\n",
              "TotalPay                 0\n",
              "TotalPayBenefits         0\n",
              "Year                     0\n",
              "Notes               148654\n",
              "Agency                   0\n",
              "Status              148654\n",
              "dtype: int64"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 12
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "hVG3jzkXiQJq"
      },
      "source": [
        "**What is the average BasePay ?**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Vjy8BrkTsnpJ",
        "outputId": "2e9e2256-4c0b-49e6-e9a3-884f9511a368"
      },
      "source": [
        "df_sal.BasePay.mean()"
      ],
      "execution_count": 13,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "66325.44884050643"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 13
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "HxtW0JJ6iQJq"
      },
      "source": [
        "** What is the highest amount of OvertimePay in the dataset ? **"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "a1yQEAxNiQJr",
        "outputId": "7881b943-0708-48a1-cba7-83a11704eab6"
      },
      "source": [
        "df_sal['OvertimePay'].max()"
      ],
      "execution_count": 14,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "245131.88"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 14
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "FPc25CybiQJr"
      },
      "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",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "WqgCpN2LiQJs",
        "outputId": "d5686fe6-9721-40e9-db44-b03d7e4dfa0a"
      },
      "source": [
        "df_sal[df_sal['EmployeeName']=='JOSEPH DRISCOLL']['JobTitle']"
      ],
      "execution_count": 15,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "24    CAPTAIN, FIRE SUPPRESSION\n",
              "Name: JobTitle, dtype: object"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 15
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "52PSEweIiQJs"
      },
      "source": [
        "** How much does JOSEPH DRISCOLL make (including benefits)? **"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "X00NwpUgiQJt",
        "outputId": "161ba915-6693-48d7-acd2-cd87de1f492e"
      },
      "source": [
        "df_sal[df_sal['EmployeeName']=='JOSEPH DRISCOLL']['TotalPayBenefits']"
      ],
      "execution_count": 16,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "24    270324.91\n",
              "Name: TotalPayBenefits, dtype: float64"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 16
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "LfvlTDINiQJt"
      },
      "source": [
        "** What is the name of highest paid person (including benefits)?**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 144
        },
        "id": "h9bsDhmniQJt",
        "outputId": "c136fa8a-af38-4d73-a031-3de7d41facd3"
      },
      "source": [
        "df_sal[df_sal['TotalPayBenefits']== df_sal['TotalPayBenefits'].max()] #['EmployeeName']\n",
        "# or\n",
        "# sal.loc[sal['TotalPayBenefits'].idxmax()]"
      ],
      "execution_count": 17,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>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.0</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",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   Id    EmployeeName  ...         Agency  Status\n",
              "0   1  NATHANIEL FORD  ...  San Francisco     NaN\n",
              "\n",
              "[1 rows x 13 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 17
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "4d1Y2cBmiQJu"
      },
      "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",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 111
        },
        "id": "ZuQSgUu5iQJu",
        "outputId": "78e25080-290c-4c35-ad01-833ec70dbf16"
      },
      "source": [
        "df_sal[df_sal['TotalPayBenefits']== df_sal['TotalPayBenefits'].min()] #['EmployeeName']\n",
        "# or\n",
        "# sal.loc[sal['TotalPayBenefits'].idxmax()]['EmployeeName']\n",
        "\n",
        "## ITS NEGATIVE!! VERY STRANGE"
      ],
      "execution_count": 18,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>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>148653</th>\n",
              "      <td>148654</td>\n",
              "      <td>Joe Lopez</td>\n",
              "      <td>Counselor, Log Cabin Ranch</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</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",
              "</div>"
            ],
            "text/plain": [
              "            Id EmployeeName  ...         Agency  Status\n",
              "148653  148654    Joe Lopez  ...  San Francisco     NaN\n",
              "\n",
              "[1 rows x 13 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 18
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "lMEj9gtFiQJv"
      },
      "source": [
        "** What was the average (mean) BasePay of all employees per year? (2011-2014) ? **"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "l7HLlOajiQJv",
        "outputId": "9206fc24-edcd-453a-b470-410f0a9426b4"
      },
      "source": [
        "df_sal.groupby('Year').mean()['BasePay']"
      ],
      "execution_count": 19,
      "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": {
            "tags": []
          },
          "execution_count": 19
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "0DZpPt24iQJv"
      },
      "source": [
        "** How many unique job titles are there? **"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Wsq10lX_iQJw",
        "outputId": "17e8a705-2a3f-4806-ec47-d0b067a3e35c"
      },
      "source": [
        "df_sal['JobTitle'].nunique()"
      ],
      "execution_count": 20,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "2159"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 20
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Bd6InG_tiQJw"
      },
      "source": [
        "** What are the top 5 most common jobs? **"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ZsT5Z1u1iQJw",
        "outputId": "e2369cb8-0b4a-4daa-d8fa-d43ccfc54297"
      },
      "source": [
        "df_sal['JobTitle'].value_counts().head(5)"
      ],
      "execution_count": 21,
      "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": {
            "tags": []
          },
          "execution_count": 21
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "QuD6BtlciQJx"
      },
      "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",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "WnTZ8pZiiQJx",
        "outputId": "d30fc7ef-8472-4072-d2a3-296def8fa019"
      },
      "source": [
        "sum(df_sal[df_sal['Year']==2013]['JobTitle'].value_counts() == 1) # pretty tricky way to do this..."
      ],
      "execution_count": 22,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "202"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 22
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "dcN-qHXIiQJy"
      },
      "source": [
        "** How many people have the word Chief in their job title? (This is pretty tricky) **"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "collapsed": true,
        "id": "xUX-6mD4iQJy"
      },
      "source": [
        "def chief_string(title):\n",
        "    if 'chief' in title.lower():\n",
        "        return True\n",
        "    else:\n",
        "        return False"
      ],
      "execution_count": 23,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ocIwte7xiQJy",
        "outputId": "36615ab7-49c9-4758-e10e-b7219048f45b"
      },
      "source": [
        "sum(df_sal['JobTitle'].apply(lambda x: chief_string(x)))"
      ],
      "execution_count": 24,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "627"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 24
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "k3R6oOcuiQJz"
      },
      "source": [
        "** Bonus: Is there a correlation between length of the Job Title string and Salary? **"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "d0aLjCVniQJz"
      },
      "source": [
        "df_sal['title_len'] = df_sal['JobTitle'].apply(len)"
      ],
      "execution_count": 25,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 107
        },
        "id": "9ae4DTfsiQJ0",
        "outputId": "ebdfdfcd-e4d6-4582-ef7b-4471d3842b49"
      },
      "source": [
        "df_sal[['title_len','TotalPayBenefits']].corr() # No correlation."
      ],
      "execution_count": 26,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>title_len</th>\n",
              "      <th>TotalPayBenefits</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>title_len</th>\n",
              "      <td>1.000000</td>\n",
              "      <td>-0.036878</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>TotalPayBenefits</th>\n",
              "      <td>-0.036878</td>\n",
              "      <td>1.000000</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                  title_len  TotalPayBenefits\n",
              "title_len          1.000000         -0.036878\n",
              "TotalPayBenefits  -0.036878          1.000000"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 26
        }
      ]
    }
  ]
}