SF Salaries.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exploring SF Public Salaries\n",
    "\n",
    "I thought it would be interesting to explore what 'normal' people make in San Francisco and came across the \n",
    "[San Francisco Public Salaries Dataset](https://www.kaggle.com/kaggle/sf-salaries). I also used reports from [Priceonomics](http://priceonomics.com/the-san-francisco-rent-explosion-part-iii/) to investigate average housing price. Here's what I found."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/katharine/.virtualenv/book/local/lib/python2.7/site-packages/IPython/core/interactiveshell.py:2871: DtypeWarning: Columns (3,4,5,6,12) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "salaries = pd.read_csv('Salaries.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\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</td>\n",
       "      <td>0</td>\n",
       "      <td>400184</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</td>\n",
       "      <td>245132</td>\n",
       "      <td>137811</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</td>\n",
       "      <td>106088</td>\n",
       "      <td>16452.6</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</td>\n",
       "      <td>56120.7</td>\n",
       "      <td>198307</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",
       "  </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",
       "\n",
       "  BasePay OvertimePay OtherPay Benefits   TotalPay  TotalPayBenefits  Year  \\\n",
       "0  167411           0   400184      NaN  567595.43         567595.43  2011   \n",
       "1  155966      245132   137811      NaN  538909.28         538909.28  2011   \n",
       "2  212739      106088  16452.6      NaN  335279.91         335279.91  2011   \n",
       "3   77916     56120.7   198307      NaN  332343.61         332343.61  2011   \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  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "salaries[:4]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Id</th>\n",
       "      <th>TotalPay</th>\n",
       "      <th>TotalPayBenefits</th>\n",
       "      <th>Year</th>\n",
       "      <th>Notes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>148654.000000</td>\n",
       "      <td>148654.000000</td>\n",
       "      <td>148654.000000</td>\n",
       "      <td>148654.000000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>74327.500000</td>\n",
       "      <td>74768.321972</td>\n",
       "      <td>93692.554811</td>\n",
       "      <td>2012.522643</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>42912.857795</td>\n",
       "      <td>50517.005274</td>\n",
       "      <td>62793.533483</td>\n",
       "      <td>1.117538</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>-618.130000</td>\n",
       "      <td>-618.130000</td>\n",
       "      <td>2011.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>37164.250000</td>\n",
       "      <td>36168.995000</td>\n",
       "      <td>44065.650000</td>\n",
       "      <td>2012.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>74327.500000</td>\n",
       "      <td>71426.610000</td>\n",
       "      <td>92404.090000</td>\n",
       "      <td>2013.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>111490.750000</td>\n",
       "      <td>105839.135000</td>\n",
       "      <td>132876.450000</td>\n",
       "      <td>2014.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>148654.000000</td>\n",
       "      <td>567595.430000</td>\n",
       "      <td>567595.430000</td>\n",
       "      <td>2014.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  Id       TotalPay  TotalPayBenefits           Year  Notes\n",
       "count  148654.000000  148654.000000     148654.000000  148654.000000      0\n",
       "mean    74327.500000   74768.321972      93692.554811    2012.522643    NaN\n",
       "std     42912.857795   50517.005274      62793.533483       1.117538    NaN\n",
       "min         1.000000    -618.130000       -618.130000    2011.000000    NaN\n",
       "25%     37164.250000   36168.995000      44065.650000    2012.000000    NaN\n",
       "50%     74327.500000   71426.610000      92404.090000    2013.000000    NaN\n",
       "75%    111490.750000  105839.135000     132876.450000    2014.000000    NaN\n",
       "max    148654.000000  567595.430000     567595.430000    2014.000000    NaN"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "salaries.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "latest_salaries = salaries[salaries['Year'] == 2014]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Id</th>\n",
       "      <th>TotalPay</th>\n",
       "      <th>TotalPayBenefits</th>\n",
       "      <th>Year</th>\n",
       "      <th>Notes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>38123.000000</td>\n",
       "      <td>38123.000000</td>\n",
       "      <td>38123.000000</td>\n",
       "      <td>38123</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>129593.000000</td>\n",
       "      <td>75463.918140</td>\n",
       "      <td>100250.918884</td>\n",
       "      <td>2014</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>11005.306493</td>\n",
       "      <td>51697.713029</td>\n",
       "      <td>66238.852887</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>110532.000000</td>\n",
       "      <td>-618.130000</td>\n",
       "      <td>-618.130000</td>\n",
       "      <td>2014</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>120062.500000</td>\n",
       "      <td>33436.210000</td>\n",
       "      <td>44199.990000</td>\n",
       "      <td>2014</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>129593.000000</td>\n",
       "      <td>72359.980000</td>\n",
       "      <td>101105.370000</td>\n",
       "      <td>2014</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>139123.500000</td>\n",
       "      <td>107974.445000</td>\n",
       "      <td>142746.950000</td>\n",
       "      <td>2014</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>148654.000000</td>\n",
       "      <td>471952.640000</td>\n",
       "      <td>510732.680000</td>\n",
       "      <td>2014</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  Id       TotalPay  TotalPayBenefits   Year  Notes\n",
       "count   38123.000000   38123.000000      38123.000000  38123      0\n",
       "mean   129593.000000   75463.918140     100250.918884   2014    NaN\n",
       "std     11005.306493   51697.713029      66238.852887      0    NaN\n",
       "min    110532.000000    -618.130000       -618.130000   2014    NaN\n",
       "25%    120062.500000   33436.210000      44199.990000   2014    NaN\n",
       "50%    129593.000000   72359.980000     101105.370000   2014    NaN\n",
       "75%    139123.500000  107974.445000     142746.950000   2014    NaN\n",
       "max    148654.000000  471952.640000     510732.680000   2014    NaN"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "latest_salaries.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "average_yearly_rent = 3880 * 12"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "average_city_pay = latest_salaries['TotalPay'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.61698360153365217"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "average_yearly_rent / average_city_pay"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "11360"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "latest_salaries[latest_salaries['TotalPay'] < average_yearly_rent].shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count     38119.000000\n",
       "mean      66564.421924\n",
       "std       44053.783972\n",
       "min           0.000000\n",
       "25%       30138.055000\n",
       "50%       65055.000000\n",
       "75%       94735.050000\n",
       "max      318835.490000\n",
       "Name: BasePay, dtype: float64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.to_numeric(latest_salaries['BasePay'], errors='coerce').describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "base_pay_series = pd.to_numeric(latest_salaries['BasePay'], errors='coerce') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(20074,)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "base_pay_series[base_pay_series * .7 < average_yearly_rent].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "overtime_series = pd.to_numeric(latest_salaries['OvertimePay'], errors='coerce')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count     38119.000000\n",
       "mean       5401.993737\n",
       "std       11769.656257\n",
       "min           0.000000\n",
       "25%           0.000000\n",
       "50%           0.000000\n",
       "75%        5113.430000\n",
       "max      173547.730000\n",
       "Name: OvertimePay, dtype: float64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "overtime_series.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(15361,)"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "overtime_series[overtime_series > 1000].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}