Salaries - modificando conteudo-auxiliar.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "59a4a42b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# trabalhando com o pandas e dataframes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "74aa611d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "dd81daf6",
   "metadata": {},
   "outputs": [],
   "source": [
    "sal = pd.read_csv('Salaries.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "527da628",
   "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>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                                        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  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sal.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "0bc51cab",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "148654"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(sal)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "24558a9d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "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"
     ]
    }
   ],
   "source": [
    "sal.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "c94e978b",
   "metadata": {},
   "outputs": [],
   "source": [
    "salm = sal.drop('Notes', axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "48bf160d",
   "metadata": {},
   "outputs": [],
   "source": [
    "salm = salm.drop('Status', axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "c19ba948",
   "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>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>Agency</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>San Francisco</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>San Francisco</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>San Francisco</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>San Francisco</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>San Francisco</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "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         Agency  \n",
       "0  2011  San Francisco  \n",
       "1  2011  San Francisco  \n",
       "2  2011  San Francisco  \n",
       "3  2011  San Francisco  \n",
       "4  2011  San Francisco  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "salm.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "9b48330f",
   "metadata": {},
   "outputs": [],
   "source": [
    "ag_sf = salm[salm['Agency']=='San Francisco'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "18d87202",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1    111886\n",
       "dtype: int64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ag_sf.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "bac49272",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "66325.44884050643"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#media do pagamento base\n",
    "sal['BasePay'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "cb72cbaa",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "245131.88"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#maior quantidade no OvertimePay\n",
    "sal['OvertimePay'].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "e408d1ef",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "24    CAPTAIN, FIRE SUPPRESSION\n",
       "Name: JobTitle, dtype: object"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#cargo de JOSEPH DRISCOLL\n",
    "sal[sal['EmployeeName']=='JOSEPH DRISCOLL']['JobTitle']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "01cede7f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "24    270324.91\n",
       "Name: TotalPayBenefits, dtype: float64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#quanto JOSEPH ganha incluindo beneficios\n",
    "sal[sal['EmployeeName']=='JOSEPH DRISCOLL']['TotalPayBenefits']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "70c14a58",
   "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>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                                        JobTitle  \\\n",
       "0   1  NATHANIEL FORD  GENERAL MANAGER-METROPOLITAN TRANSIT AUTHORITY   \n",
       "\n",
       "     BasePay  OvertimePay   OtherPay  Benefits   TotalPay  TotalPayBenefits  \\\n",
       "0  167411.18          0.0  400184.25       NaN  567595.43         567595.43   \n",
       "\n",
       "   Year  Notes         Agency  Status  \n",
       "0  2011    NaN  San Francisco     NaN  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#nome da pessoa mais bem paga\n",
    "sal[sal['TotalPayBenefits'] == sal['TotalPayBenefits'].max()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "f4bb7ae8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Id                                                               1\n",
       "EmployeeName                                        NATHANIEL FORD\n",
       "JobTitle            GENERAL MANAGER-METROPOLITAN TRANSIT AUTHORITY\n",
       "BasePay                                                  167411.18\n",
       "OvertimePay                                                    0.0\n",
       "OtherPay                                                 400184.25\n",
       "Benefits                                                       NaN\n",
       "TotalPay                                                 567595.43\n",
       "TotalPayBenefits                                         567595.43\n",
       "Year                                                          2011\n",
       "Notes                                                          NaN\n",
       "Agency                                               San Francisco\n",
       "Status                                                         NaN\n",
       "Name: 0, dtype: object"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sal.loc[sal['TotalPayBenefits'].idxmax()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "301cb2f8",
   "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>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                    JobTitle  BasePay  OvertimePay  \\\n",
       "148653  148654    Joe Lopez  Counselor, Log Cabin Ranch      0.0          0.0   \n",
       "\n",
       "        OtherPay  Benefits  TotalPay  TotalPayBenefits  Year  Notes  \\\n",
       "148653   -618.13       0.0   -618.13           -618.13  2014    NaN   \n",
       "\n",
       "               Agency  Status  \n",
       "148653  San Francisco     NaN  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#menor salario\n",
    "sal[sal['TotalPayBenefits'] == sal['TotalPayBenefits'].min()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "43ae1573",
   "metadata": {},
   "outputs": [
    {
     "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"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#media BasePay - de todos funcionarios por ano (2011-2014)\n",
    "sal.groupby('Year').mean()['BasePay']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "9fd0d324",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['GENERAL MANAGER-METROPOLITAN TRANSIT AUTHORITY',\n",
       "       'CAPTAIN III (POLICE DEPARTMENT)',\n",
       "       'WIRE ROPE CABLE MAINTENANCE MECHANIC', ..., 'Conversion',\n",
       "       'Cashier 3', 'Not provided'], dtype=object)"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#quantos titulos de trabalhos unicos existem?\n",
    "sal['JobTitle'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "9a55520a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2159"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#quantos titulos de trabalhos unicos existem? (quantidade)\n",
    "sal['JobTitle'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "54fcc8bc",
   "metadata": {},
   "outputs": [
    {
     "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"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 5 empregos + comuns\n",
    "sal['JobTitle'].value_counts().iloc[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "6da8007e",
   "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>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>72925</th>\n",
       "      <td>72926</td>\n",
       "      <td>Gregory P Suhr</td>\n",
       "      <td>Chief of Police</td>\n",
       "      <td>319275.01</td>\n",
       "      <td>0.00</td>\n",
       "      <td>20007.06</td>\n",
       "      <td>86533.21</td>\n",
       "      <td>339282.07</td>\n",
       "      <td>425815.28</td>\n",
       "      <td>2013</td>\n",
       "      <td>NaN</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72926</th>\n",
       "      <td>72927</td>\n",
       "      <td>Joanne M Hayes-White</td>\n",
       "      <td>Chief, Fire Department</td>\n",
       "      <td>313686.01</td>\n",
       "      <td>0.00</td>\n",
       "      <td>23236.00</td>\n",
       "      <td>85431.39</td>\n",
       "      <td>336922.01</td>\n",
       "      <td>422353.40</td>\n",
       "      <td>2013</td>\n",
       "      <td>NaN</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72927</th>\n",
       "      <td>72928</td>\n",
       "      <td>Samson  Lai</td>\n",
       "      <td>Battalion Chief, Fire Suppress</td>\n",
       "      <td>186236.42</td>\n",
       "      <td>131217.63</td>\n",
       "      <td>29648.27</td>\n",
       "      <td>57064.95</td>\n",
       "      <td>347102.32</td>\n",
       "      <td>404167.27</td>\n",
       "      <td>2013</td>\n",
       "      <td>NaN</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72928</th>\n",
       "      <td>72929</td>\n",
       "      <td>Ellen G Moffatt</td>\n",
       "      <td>Asst Med Examiner</td>\n",
       "      <td>272855.51</td>\n",
       "      <td>23727.91</td>\n",
       "      <td>38954.54</td>\n",
       "      <td>66198.92</td>\n",
       "      <td>335537.96</td>\n",
       "      <td>401736.88</td>\n",
       "      <td>2013</td>\n",
       "      <td>NaN</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72929</th>\n",
       "      <td>72930</td>\n",
       "      <td>Robert L Shaw</td>\n",
       "      <td>Dep Dir for Investments, Ret</td>\n",
       "      <td>315572.01</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>82849.66</td>\n",
       "      <td>315572.01</td>\n",
       "      <td>398421.67</td>\n",
       "      <td>2013</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>110526</th>\n",
       "      <td>110527</td>\n",
       "      <td>Arthur L Curry</td>\n",
       "      <td>PS Aide Health Services</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.00</td>\n",
       "      <td>10.67</td>\n",
       "      <td>0.00</td>\n",
       "      <td>10.67</td>\n",
       "      <td>10.67</td>\n",
       "      <td>2013</td>\n",
       "      <td>NaN</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110527</th>\n",
       "      <td>110528</td>\n",
       "      <td>Nereida  Vega</td>\n",
       "      <td>Senior Clerk</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.00</td>\n",
       "      <td>5.56</td>\n",
       "      <td>0.00</td>\n",
       "      <td>5.56</td>\n",
       "      <td>5.56</td>\n",
       "      <td>2013</td>\n",
       "      <td>NaN</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110528</th>\n",
       "      <td>110529</td>\n",
       "      <td>Timothy E Gibson</td>\n",
       "      <td>Police Officer 3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-2.73</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-2.73</td>\n",
       "      <td>2013</td>\n",
       "      <td>NaN</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110529</th>\n",
       "      <td>110530</td>\n",
       "      <td>Mark E Laherty</td>\n",
       "      <td>Police Officer 3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-8.20</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-8.20</td>\n",
       "      <td>2013</td>\n",
       "      <td>NaN</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110530</th>\n",
       "      <td>110531</td>\n",
       "      <td>David P Kucia</td>\n",
       "      <td>Police Officer 3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-33.89</td>\n",
       "      <td>0.00</td>\n",
       "      <td>-33.89</td>\n",
       "      <td>2013</td>\n",
       "      <td>NaN</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>37606 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            Id          EmployeeName                        JobTitle  \\\n",
       "72925    72926        Gregory P Suhr                 Chief of Police   \n",
       "72926    72927  Joanne M Hayes-White          Chief, Fire Department   \n",
       "72927    72928           Samson  Lai  Battalion Chief, Fire Suppress   \n",
       "72928    72929       Ellen G Moffatt               Asst Med Examiner   \n",
       "72929    72930         Robert L Shaw    Dep Dir for Investments, Ret   \n",
       "...        ...                   ...                             ...   \n",
       "110526  110527        Arthur L Curry         PS Aide Health Services   \n",
       "110527  110528         Nereida  Vega                    Senior Clerk   \n",
       "110528  110529      Timothy E Gibson                Police Officer 3   \n",
       "110529  110530        Mark E Laherty                Police Officer 3   \n",
       "110530  110531         David P Kucia                Police Officer 3   \n",
       "\n",
       "          BasePay  OvertimePay  OtherPay  Benefits   TotalPay  \\\n",
       "72925   319275.01         0.00  20007.06  86533.21  339282.07   \n",
       "72926   313686.01         0.00  23236.00  85431.39  336922.01   \n",
       "72927   186236.42    131217.63  29648.27  57064.95  347102.32   \n",
       "72928   272855.51     23727.91  38954.54  66198.92  335537.96   \n",
       "72929   315572.01         0.00      0.00  82849.66  315572.01   \n",
       "...           ...          ...       ...       ...        ...   \n",
       "110526        NaN         0.00     10.67      0.00      10.67   \n",
       "110527        NaN         0.00      5.56      0.00       5.56   \n",
       "110528        NaN         0.00      0.00     -2.73       0.00   \n",
       "110529        NaN         0.00      0.00     -8.20       0.00   \n",
       "110530        NaN         0.00      0.00    -33.89       0.00   \n",
       "\n",
       "        TotalPayBenefits  Year  Notes         Agency  Status  \n",
       "72925          425815.28  2013    NaN  San Francisco     NaN  \n",
       "72926          422353.40  2013    NaN  San Francisco     NaN  \n",
       "72927          404167.27  2013    NaN  San Francisco     NaN  \n",
       "72928          401736.88  2013    NaN  San Francisco     NaN  \n",
       "72929          398421.67  2013    NaN  San Francisco     NaN  \n",
       "...                  ...   ...    ...            ...     ...  \n",
       "110526             10.67  2013    NaN  San Francisco     NaN  \n",
       "110527              5.56  2013    NaN  San Francisco     NaN  \n",
       "110528             -2.73  2013    NaN  San Francisco     NaN  \n",
       "110529             -8.20  2013    NaN  San Francisco     NaN  \n",
       "110530            -33.89  2013    NaN  San Francisco     NaN  \n",
       "\n",
       "[37606 rows x 13 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#titulos de trabalho represeentado por apenas uma pessoa em 2013. Uma ocorrencia em 2013\n",
    "sal[sal['Year']==2013]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "dc095a6b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Transit Operator                False\n",
       "Special Nurse                   False\n",
       "Registered Nurse                False\n",
       "Public Svc Aide-Public Works    False\n",
       "Custodian                       False\n",
       "                                ...  \n",
       "Chief Fire Alarm Dispatcher      True\n",
       "Operating Room Nurse             True\n",
       "Market Research Spec, Port       True\n",
       "IS Trainer-Senior                True\n",
       "Crane Mechanic Supervisor        True\n",
       "Name: JobTitle, Length: 1051, dtype: bool"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sal[sal['Year']==2013]['JobTitle'].value_counts()==1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "9149a36f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "202"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sum(sal[sal['Year']==2013]['JobTitle'].value_counts()==1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "6d1e6901",
   "metadata": {},
   "outputs": [],
   "source": [
    "#quantas pessoas tem a palavra 'Chief' em seu cargo?\n",
    "def chief_string(title):\n",
    "    if'chief' in title.lower():\n",
    "        return True\n",
    "    else:\n",
    "        return False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "6e454cc8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0         False\n",
       "1         False\n",
       "2         False\n",
       "3         False\n",
       "4          True\n",
       "          ...  \n",
       "148649    False\n",
       "148650    False\n",
       "148651    False\n",
       "148652    False\n",
       "148653    False\n",
       "Name: JobTitle, Length: 148654, dtype: bool"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sal['JobTitle'].apply(lambda x: chief_string(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "5680e575",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "627"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sum(sal['JobTitle'].apply(lambda x: chief_string(x)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "c43cec3a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "13927773042.81"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sal['TotalPayBenefits'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "0e8164da",
   "metadata": {},
   "outputs": [],
   "source": [
    "#existe uma correlacao entre o comprimento da sequencia do titulo do trabalho e do salario\n",
    "sal['Tamanho da string'] = sal['JobTitle'].apply(len)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "4380e84a",
   "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>Tamanho da string</th>\n",
       "      <th>TotalPayBenefits</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Tamanho da string</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": [
       "                   Tamanho da string  TotalPayBenefits\n",
       "Tamanho da string           1.000000         -0.036878\n",
       "TotalPayBenefits           -0.036878          1.000000"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sal[['Tamanho da string', 'TotalPayBenefits']].corr()"
   ]
  }
 ],
 "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.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}