01-GWS-Notebook.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Run the code cell below with the ▶| button above to set up this notebook, or type `SHIFT-ENTER`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from datascience import *\n",
    "import numpy as np\n",
    "import math\n",
    "import matplotlib.pyplot as plt\n",
    "from IPython.display import Image, display\n",
    "import ipywidgets as widgets\n",
    "from scipy import stats\n",
    "import pandas as pd\n",
    "from scripts.gws_module import *\n",
    "import itertools\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Welcome to GWS-131's Data Science Module!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Part 1: Introduction to Python and Jupyter Notebooks:\n",
    "\n",
    "## 1. Cells, Arithmetic, and Code\n",
    "In a notebook, each rectangle containing text or code is called a *cell*.\n",
    "\n",
    "Cells (like this one) can be edited by double-clicking on them. This cell is a text cell, written in a simple format called [Markdown](http://daringfireball.net/projects/markdown/syntax) to add formatting and section headings.  You don't need to worry about Markdown today, but it's a pretty fun+easy tool to learn.\n",
    "\n",
    "After you edit a cell, click the \"run cell\" button at the top that looks like ▶| to confirm any changes. (Try not to delete the instructions.) You can also press `SHIFT-ENTER` to run any cell or progress from one cell to the next.\n",
    "\n",
    "Other cells contain code in the Python programming language.  Running a code cell will execute all of the code it contains.\n",
    "\n",
    "Try running this cell:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "print(\"Hello, World!\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will now quickly go through some very basic functionality of Python, which we'll be using throughout the rest of this notebook.\n",
    "\n",
    "### 1.1 Arithmetic\n",
    "Quantitative information arises everywhere in data science. In addition to representing commands to `print` out lines, expressions can represent numbers and methods of combining numbers. \n",
    "\n",
    "The expression `3.2500` evaluates to the number 3.25. (Run the cell and see.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "3.2500"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We don't necessarily always need to say \"`print`\", because Jupyter always prints the last line in a code cell. If you want to print more than one line, though, do specify \"`print`\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "print(3)\n",
    "4\n",
    "5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Many basic arithmetic operations are built in to Python, like `*` (multiplication), `+` (addition), `-` (subtraction), and `/` (division). There are many others, which you can find information about [here](http://www.inferentialthinking.com/chapters/03/1/expressions.html). Use parenthesis to specify the order of operations, which act according to PEMDAS, just as you may have learned in school. Use parentheses for a happy new year!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "1+(6*5-(6*3))**2*((2**3)/4*7)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.2 Variables\n",
    "\n",
    "We sometimes want to work with the result of some computation more than once. To be able to do that without repeating code everywhere we want to use it, we can store it in a variable with *assignment statements*, which have the variable name on the left, an equals sign, and the expression to be evaluated and stored on the right. In the cell below, `(3 * 11 + 5) / 2 - 9` evaluates to 10, and gets stored in the variable `result`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "result = (3 * 11 + 5) / 2 - 9\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Functions\n",
    "\n",
    "    \n",
    "One important form of an expression is the call expression, which first names a function and then describes its arguments. The function returns some value, based on its arguments. Some important mathematical functions are\n",
    "\n",
    "| Function | Description                                                   |\n",
    "|----------|---------------------------------------------------------------|\n",
    "| `abs`      | Returns the absolute value of its argument                    |\n",
    "| `max`      | Returns the maximum of all its arguments                      |\n",
    "| `min`      | Returns the minimum of all its arguments                      |\n",
    "| `round`    | Round its argument to the nearest integer                     |\n",
    "\n",
    "Here are two call expressions that both evaluate to 3\n",
    "\n",
    "```python\n",
    "abs(2 - 5)\n",
    "max(round(2.8), min(pow(2, 10), -1 * pow(2, 10)))\n",
    "```\n",
    "\n",
    "These function calls first evaluate the expressions in the arguments (inside the parentheses), then evaluate the function on the results. `abs(2-5)` evaluates first to `abs(3)`, then returns `3`.\n",
    "\n",
    "A **statement** is a whole line of code.  Some statements are just expressions, like the examples above, that can be broken down into its subexpressions which get evaluated individually before evaluating the statement as a whole.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.1 Calling functions\n",
    "\n",
    "The most common way to combine or manipulate values in Python is by calling functions. Python comes with many built-in functions that perform common operations.\n",
    "\n",
    "For example, the `abs` function takes a single number as its argument and returns the absolute value of that number.  The absolute value of a number is its distance from 0 on the number line, so `abs(5)` is 5 and `abs(-5)` is also 5."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "abs(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "abs(-5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Functions can be called as above, putting the argument in parentheses at the end, or by using \"dot notation\", and calling the function after finding the arguments, as in the cell immediately below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "nums = make_array(1,2,5) # a list of items, in this case, numbers\n",
    "nums.max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "max(nums)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Part 2: Tables\n",
    "\n",
    "Now it's time to work with tables and explore some real data. A `Table` is just like how we made a list above with `make_array`, but for all the rows in a table.\n",
    "\n",
    "We've arranged the order of the datasets we're going to explore by geographic proximity to UCB."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. UC Berkeley, Ladder Rank Equivalent Faculty\n",
    "\n",
    "We're going to start right here at UCB! These data are from Fall 2015 from the *UC Corporate Personnel System*, and give us the ratio of female ladder rank equivalent (LRE), which are tenure and tenure track faculty at Berkeley, in the respective divisions.\n",
    "\n",
    "**Note**: STEM includes engineering and computer science, life sciences, math, medicine, other health sciences and physical sciences.\n",
    "\n",
    "To read in a file to a table, we use the general syntax:\n",
    "\n",
    "```python\n",
    "Table.read_table(\"file_name\")\n",
    "```\n",
    "\n",
    "Most often, these file names end in `.csv` to show the data format. `.csv` format is popular for spreadsheets and can be imported/exported from programs such as Microsoft Excel, OpenOffice Calc, or Google spreadsheets."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "UCB_LRE_female = Table.read_table('data/UCB-percent-female-LRE.csv').drop(1)\n",
    "UCB_LRE_female.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "That was easy! Now that we have these data stored in the `UCB_LRE_female` table, we can start getting some information from it.\n",
    "\n",
    "This notebook can calculate how large this table is with two functions: `num_rows` and `num_columns`. Let's use these on the table above. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "UCB_LRE_female.num_rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "UCB_LRE_female.num_columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This table is `9x5`. We can quickly plot these data on a bar graph using the `barh` function, so that we can best visually compare between disciplines over time."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "UCB_LRE_female.barh('Discipline')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-info\">\n",
    "**Question**: What do you notice about the proportions of female LRE faculty across disciplines? Discuss with the people around you, and write a few sentences about it below. \n",
    "</div>"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "We can use the `transpose` function to flip the rows and columns so we can get a better set up for time series data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "transposed_table = transpose(UCB_LRE_female)\n",
    "transposed_table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Then we can add the `.plot()` method to plot a line graph to see how the ratio of female faculty has changed over time by discipline:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "transposed_table.plot()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also take the average across disciplines for each year and add that to our table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "transposed_table[\"average\"] = transposed_table[transposed_table.columns[1:]].mean(axis=1)\n",
    "transposed_table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Then we can plot that like above:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "transposed_table['average'].plot()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Tables Essentials!\n",
    "\n",
    "For your reference, here's a table of all the useful `Table` functions:\n",
    "\n",
    "|Name|Example|Purpose|\n",
    "|-|-|-|\n",
    "|`Table`|`Table()`|Create an empty table, usually to extend with data|\n",
    "|`Table.read_table`|`Table.read_table(\"my_data.csv\")`|Create a table from a data file|\n",
    "|`show` | `tbl.show(5)` | Show the first *N* rows of the table |\n",
    "|`with_columns`|`tbl = Table().with_columns(\"N\", np.arange(5), \"2*N\", np.arange(0, 10, 2))`|Create a copy of a table with more columns|\n",
    "|`column`|`tbl.column(\"N\")`|Create an array containing the elements of a column|\n",
    "|`sort`|`tbl.sort(\"N\")`|Create a copy of a table sorted by the values in a column|\n",
    "|`where`|`tbl.where(\"N\", are.above(2))`|Create a copy of a table with only the rows that match some *predicate*|\n",
    "|`num_rows`|`tbl.num_rows`|Compute the number of rows in a table|\n",
    "|`num_columns`|`tbl.num_columns`|Compute the number of columns in a table|\n",
    "|`select`|`tbl.select(\"N\")`|Create a copy of a table with only some of the columns|\n",
    "|`drop`|`tbl.drop(\"2*N\")`|Create a copy of a table without some of the columns|\n",
    "|`take`|`tbl.take(np.arange(0, 6, 2))`|Create a copy of the table with only the rows whose indices are in the given array|"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Visualizations**\n",
    "\n",
    "These are the different methods you can call on a table to plot data within that table. Insert the column names as the arguments `x column` and `y column` when calling these methods. \n",
    "\n",
    "|Plotting type | | Call structure |\n",
    "|-|-|-|\n",
    "|Scatter | | `table.scatter(\"x column\", \"y column\")` |\n",
    "|Line | | `table.plot(\"x column\", \"y column\")` |\n",
    "|Bar | | `table.bar(\"x column\", \"y column\")` |\n",
    "|Horiz. Bar | | `table.barh(\"x column\", \"y column\")` |\n",
    "|Histogram | | `table.hist(\"x axis\", bins(optional), unit(optional))` |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "---\n",
    "\n",
    "## 2. UCOP Payroll Dataset\n",
    "\n",
    "Let's look at another dataset that has the payroll for all UC employees:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "UC_data = Table.read_table('data/UCOP.csv')\n",
    "UC_data.show(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's look only at professors. To do this, we need to use a new function `where`. The general form of this function is:\n",
    "\n",
    "```python\n",
    "table.where(\"column_name\", \"predicate\")\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "reduced_table = UC_data.select(2,3,4,5).sort(3, descending=True)\n",
    "professors = reduced_table.where(\"Title\", are.equal_to(\"PROF-AY\"))\n",
    "professors.show(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Big money!\n",
    "\n",
    "We can visualize the distribution of pay with a histogram, but the histogram (counting frequencies of a specific pay level) will change depending upon the \"bin size\" of these pay levels. We can make an interactive slider to see this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def hist_bins(bin_size=1):\n",
    "    professors.select(3).hist(bins=np.arange(0,500000,bin_size*2000))\n",
    "\n",
    "slider = widgets.IntSlider(min=1,max=10,step=1,value=5)\n",
    "display(widgets.interactive(hist_bins, bin_size=slider))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "### Salary for male vs. female professors on the UC payroll\n",
    "\n",
    "While we don't have gender data in this dataset, we can use a pre-trained machine learning model to predict gender based on first name (we will forget for a moment that creating binary categories of male and female is problematic to begin with!). While this is ***certainly not 100% accurate***, it is more around 70-80%, we can use it to get a better idea of salaries for different genders.\n",
    "\n",
    "Here is an example of what the classification model can do:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from scripts.gender import classify_gender\n",
    "\n",
    "classify_gender(\"Daniel\"), classify_gender(\"Charis\"), classify_gender(\"Niklaus\"), classify_gender(\"Maureen\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can add a new column to our professors table with the gender classification output by the model for each professor's name."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "professors.append_column(\"Gender\", [classify_gender(name) for name in professors['First Name']])\n",
    "professors.show(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice the incorrect classification of Bin Yu. These models aren't perfect! They were like trained on common English names, and so take these results with a grain of salt. But either way, we see in the top 10 salaries that 9 are male!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-info\">\n",
    "**Challenge**: Now let's investigate the average salary amounts for female vs. male professors, try to use what you've learned so far to get the `mean` of `Gross Pay` by gender from the `professors` table, and then make a `bar` plot (refresh yourself on how to use this in the table at the top of the section!)\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# task"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "---\n",
    "\n",
    "## 3. Silicon Valley\n",
    "\n",
    "These data are compiled from [EEO-1](https://www.eeoc.gov/eeoc/statistics/reports/hightech/) reports from Apple, Twitter, Salesforce, Facebook, Microsoft, and Intel. The EEO-1 is a document required by the federal government that provides the raw numbers of employees in each of the categories below. We summed the most recent data (all from 2014-16) for these companies to get the table below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "tech_data = Table.read_table('data/eeo-aggregate.csv')\n",
    "tech_data.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can look at a basic bar chart of all males and females in these companies by job category:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tech_data.select(['Job Categories', 'All Male', 'All Female']).barh('Job Categories')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also break down each gender by race:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "females = [c for c in tech_data.to_df().columns if \"Female\" in c]\n",
    "tech_data.select(['Job Categories'] + females).barh('Job Categories')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "males = [c for c in tech_data.to_df().columns if \"Male\" in c]\n",
    "tech_data.select(['Job Categories'] + males).barh('Job Categories')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-info\">\n",
    "**Question**: What do you see in this data? \n",
    "(**Note**: It might be a little hard to see since the bars are so small - zoom in!)\n",
    "</div>"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 4. Bay Area Census data\n",
    "\n",
    "Let's read in a CSV file with Bay Area employment data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "bay_area = Table().read_table('data/bay_area_data.csv')\n",
    "bay_area.show(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Job code subset\n",
    "\n",
    "As you can see above, this table has a lot of information. The variables are in the columns and each row represents an individual. First, we will subset this table to only include the occupations we want to analyze. [Job codes](https://www2.census.gov/programs-surveys/acs/tech_docs/code_lists/2013_ACS_Code_Lists.pdf) are listed in the column `OCC2010`. We're going to focus on management and stem jobs, and have taken out the corresponding codes from the [code lists](https://www2.census.gov/programs-surveys/acs/tech_docs/code_lists/2013_ACS_Code_Lists.pdf). We'll zoom back out at the end to get a big picture conclusion."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "job_codes = [10, 20, 30, 100, 110, 120, 130, 140, 150, 160, 220, 300, 310, 330, 350, 360, 410, 420,\n",
    "             620, 700, 710, 720, 730, 800, 820, 940, 950, 1000, 1010, 1020, 1050, 1060, 1100, 1200, 1220,\n",
    "             1230, 1240, 1350, 1360, 1400, 1410, 1420, 1430, 1450, 1460, 1540, 1550, 1720, 1910, 1920,\n",
    "             1980, 2840, 2900, 4000, 4010, 4030, 4050, 4060, 4110, 4120, 4130, 4140, 4150, 4200, 4210,\n",
    "             4220, 4230, 4250, 4720, 5000, 7720, 7730, 7900, 8000, 8010, 8030, 8060, 8800, 8830, 7700,\n",
    "             9620, 9630, 9640]\n",
    "\n",
    "df = bay_area.to_df()\n",
    "bay_area_cut = Table.from_df(df.loc[df['OCC2010'].isin(job_codes)])\n",
    "bay_area_cut.show(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Although still large, a table with 13110 rows has now decreased to 2550 by selecting rows that match our `job_codes` array. Let's subset this further by picking out specific variables we want to look at:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cut_bay_area= bay_area_cut.drop(\"CPSID\",\"ASECFLAG\",\"HWTSUPP\", \"HFLAG\", \"MONTH\", \"PERNUM\", \"CPSIDP\",\"WTSUPP\")\n",
    "cut_bay_area.show(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The column of job codes in \"OCC2010\" still does not collapse categories down enough for us to understand conceptual differences between these jobs. The code cell below is a bit complicated, but all it does is create a new column for us with the `job_categories` mapping the job codes, so we can collapse some of these jobs into useful categories:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "job_categories = {\"STEM\": [700, 1000, 1010, 1020, 1050, 1220, 1230, 1240, 1350, 1360, 1400, 1410, 1420, 1430, 1450, \\\n",
    "                           1460, 1540, 1550, 1720, 1910, 1920, 1980,2840, 2900,7720, 7730, 7900, 8000, 8010,8030, \\\n",
    "                           8060, 8800, 8830],\n",
    "                  \"SERVICE\": [7700, 9620, 9630, 9640, 4000, 4010, 4030, 4050, 4060, 4110, 4120, 4130, 4140, 4150, 4720],\n",
    "                  \"FINANCIAL\": [120, 800, 820, 940, 950],\n",
    "                  \"CUSTODIAL\": [4200, 4210, 4220, 4230, 4250],\n",
    "                  \"MANAGEMENT\": [130, 150, 160, 220, 30, 100, 410, 420],\n",
    "                  \"STEM_MANAGER\": [140,300,330, 350, 360, 1060, 1100],\n",
    "                  \"ADMINISTRATOR\": [10,20]}\n",
    "\n",
    "job_categories = dict((v,k) for k in job_categories for v in job_categories[k])\n",
    "\n",
    "sectors = []\n",
    "for job in cut_bay_area.column(\"OCC2010\"):\n",
    "    try:\n",
    "        sectors.append(job_categories[job])\n",
    "    except:\n",
    "        sectors.append(\"UNKNOWN\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can add the sector of each individual's job into a column by using the `with_column` function as seen below. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "with_sector = cut_bay_area.with_column('SECTOR', sectors)\n",
    "with_sector"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You might have noticed this earlier but race in this table is listed as a number. To make analyis more intuitive, let's change the race codes into what they mean. The census documentation tells use that we can map races to the following codes, again using the [code PDF](https://www2.census.gov/programs-surveys/acs/tech_docs/code_lists/2013_ACS_Code_Lists.pdf):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "race_dict = {'White': list(range(100,200)),\n",
    "             'Black': list(range(200,300)),\n",
    "             'Indigenous': list(range(300,400)),\n",
    "             'Asian/ Pacific Islander': list(range(400,700)),\n",
    "             'Other': list(range(700,900))}\n",
    "\n",
    "race_dict = dict((v,k) for k in race_dict for v in race_dict[k])\n",
    "\n",
    "with_race = Table.from_df(with_sector.to_df().replace({\"RACE\": race_dict, \"SEX\": {1: \"MALE\", 2: \"FEMALE\"}}))\n",
    "with_race.show(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's make a quick bar chart for the gender and races in our biased sample from the Bay Area Census:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "with_race.select(\"SEX\").group(\"SEX\").barh(\"SEX\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "with_race.select(\"RACE\").group(\"RACE\").barh(\"RACE\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you can see, \"White\" is a pretty big ethnicity group, this may be due to the fact that \"White\" encompasses a lot according to the 2010 U.S. Census. The definitions of White include Middle Easterners, North Africans and the majority of Hispanic people in the United States. \n",
    "\n",
    "Let's try looking at job `SECTOR` by `SEX`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "with_race.to_df().groupby(['SECTOR', 'SEX'])['SEX'].count().unstack().plot.barh(stacked=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-info\">\n",
    "**Challenge**: Copy the code above but change `SEX` to `RACE` to see the same analysis breakdown by `RACE`:\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# task"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can write a short `for` loop to print out bar charts for each `SECTOR` broken down by `SEX`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "for s in set(sectors):\n",
    "    df = with_race.to_df()\n",
    "    df[df['SECTOR'] == s].groupby(['RACE', 'SEX'])['SEX'].count().unstack().plot.barh(stacked=True, title=s)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "By looking at the average mean of each part of the sample, we see some differences. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Income by race and gender\n",
    "\n",
    "<div class=\"alert alert-info\">\n",
    "**Challenge**: Use our table `with_race` to get `groupby` `SEX` and then get the mean of `INCTOT`, then plot this:\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# task"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Do the same for `RACE`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# task"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "This type of comparison isn't very reliable if we just look at the sample mean. We will perform a p-value test to determine if the change of income across races/sex is statistically significant. To do this we first need to bootstrap our sample to make a 95% confidence interval of the estimated population mean.  We have a pre-defined function called `bootstrap_median` that we can use to do this. The following block of code computes and plots the median confidence interval for each sex using `bootstrap_median`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's look at `MALE` vs. `FEMALE`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "median_dict = {}\n",
    "for i, s in enumerate(['MALE', 'FEMALE']):\n",
    "    subset = Table.from_df(df[df['SEX'] == s])\n",
    "    medians = bootstrap_median(subset, \"INCTOT\", 1000) # Using bootstrap_median here\n",
    "    median_dict[s] = medians\n",
    "    left = percentile(2.5, medians)\n",
    "    right = percentile(97.5, medians)\n",
    "    CI = make_array(left, right)\n",
    "    print(\"The median 95% Confidence Interval for \" + s + \" is\", CI)\n",
    "    plt.plot(CI, make_array(i, i), lw=10, label=s)\n",
    "plt.legend(bbox_to_anchor=(1.01, 1), loc='upper left', ncol=1)\n",
    "plt.title('Bootstrap Median Income Confidence Intervals')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can calculate the p-value from the median samples to determine whether the difference is significant:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "stats.ttest_ind(median_dict['MALE'], median_dict['FEMALE'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "We can also look at `RACE`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "median_dict = {}\n",
    "for i, r in enumerate(set(df['RACE'])):\n",
    "    subset = Table.from_df(df[df['RACE'] == r])\n",
    "    medians= bootstrap_median(subset, \"INCTOT\", 1000) # Using bootstrap_median again\n",
    "    median_dict[r] = medians\n",
    "    left = percentile(2.5, medians)\n",
    "    right = percentile(97.5, medians)\n",
    "    CI = make_array(left, right)\n",
    "    print(\"The median 95% Confidence Interval for \" + r + \" is\", CI)\n",
    "    plt.plot(CI, make_array(i, i), lw=10, label=r)\n",
    "plt.legend(bbox_to_anchor=(1.01, 1), loc='upper left', ncol=1)\n",
    "plt.title('Bootstrap Median Income Confidence Intervals')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can do a one way F test to see if there is significance in the difference between the median samples:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "stats.f_oneway(median_dict['NA'], median_dict['Other'], median_dict['Indigenous'], median_dict['White'], median_dict['Black'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can use a fancy tool to give us all the combinations of `SEX` and `RACE` and then get the confidence intervals for those:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "combos = [i for i in itertools.product(set(df['RACE']), ['MALE', 'FEMALE'])]\n",
    "combos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "for i, c in enumerate(combos):\n",
    "    subset = df[df['RACE'] == c[0]]\n",
    "    subset = Table.from_df(subset[subset['SEX'] == c[1]])\n",
    "    medians= bootstrap_median(subset, \"INCTOT\", 1000)\n",
    "    left = percentile(2.5, medians)\n",
    "    right = percentile(97.5, medians)\n",
    "    CI = make_array(left, right)\n",
    "    print(\"The median 95% Confidence Interval for \" + c[0] + ' ' + c[1] + \" is\", CI)\n",
    "    plt.plot(CI, make_array(i, i), lw=10, label=c[0] + ' ' + c[1])\n",
    "plt.legend(bbox_to_anchor=(1.01, 1), loc='upper left', ncol=1)\n",
    "plt.title('Bootstrap Median Income Confidence Intervals')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-info\">\n",
    "**Task**: Discuss this plot with the people around you and write a response below.\n",
    "</div>"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "---\n",
    "\n",
    "## 5. Compared to entire Bay Area census sample\n",
    "\n",
    "So how does our tech-biased subset compare to the entire census subset of the Bay Area? First we'll do some quick processing to get out non-responses and relabel the Bay Area subset: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "bay_area2 = bay_area.to_df().replace({\"RACE\": race_dict, \"SEX\": {1: \"MALE\", 2: \"FEMALE\"}})\n",
    "bay_area2 = bay_area2[bay_area2[\"INCTOT\"] != 0]\n",
    "bay_area2 = Table.from_df(bay_area2[bay_area2[\"INCTOT\"] != 99999999])\n",
    "bay_area2.show(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's check to see what our sample contains in terms of `SEX` and `RACE`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "bay_area2.group('SEX')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "bay_area2.group('SEX').barh(\"SEX\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "bay_area2.group('RACE')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "bay_area2.group('RACE').barh(\"RACE\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can then look at income by `SEX` and `RACE`. Let's start with `SEX`.\n",
    "\n",
    "### `SEX`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "bay_area_mean_sex = bay_area2.to_df().groupby(['SEX'])['INCTOT'].mean()\n",
    "bay_area_mean_sex.plot.barh()\n",
    "bay_area_mean_sex"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We'll recall that our biased subset had much higher means, but similar disparity:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# this data is the same as the one in the previous section with job codes - copied here for your reference\n",
    "with_race_mean_sex = with_race.to_df().groupby(['SEX'])['INCTOT'].mean()\n",
    "with_race_mean_sex.plot.barh()\n",
    "with_race_mean_sex"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "UCB is doing much better than both of these subsets:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "professors_mean_sex = professors.to_df().groupby(['Gender'])['Gross Pay'].mean()\n",
    "professors_mean_sex.plot.barh()\n",
    "professors_mean_sex"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can put all of these bar charts together to get a better idea of how the numbers in each of these subsets relate to each other:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "b = pd.DataFrame([bay_area_mean_sex, with_race_mean_sex, professors_mean_sex], [\"Entire Bay Area\", \"Biased subset\", \"Professors\"])\n",
    "b.plot.barh()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Again, combining the charts in a different way to group the numbers for each gender together:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "b2 = pd.DataFrame(list(zip(bay_area_mean_sex, with_race_mean_sex, professors_mean_sex)), bay_area_mean_sex.keys())\n",
    "ax = b2.plot.barh()\n",
    "ax.legend([\"Entire Bay Area\", \"Biased subset\", \"Professors\"], loc=4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The table below is the complete table with the wages of each gender for each group that we calculated above, which we used in the charts we just created."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "b"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also calculate the differences between average wages of males and females in each of our population subsets.\n",
    "\n",
    "To calculate the difference, we subtract the amount of each gender group's wages from each other, and get the numbers below. These numbers represent how much more males earn than females in each group."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "b['MALE'] - b['FEMALE']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How about `FEMALE` as a percentage of `MALE`?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "b['FEMALE'] / b['MALE']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### `RACE`\n",
    "We can also look at `RACE` in the larger Bay Area subset:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "bay_area_mean_race = bay_area2.to_df().groupby(['RACE'])['INCTOT'].mean()\n",
    "bay_area_mean_race"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "bay_area_mean_race.plot.barh()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Our biased subset:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "with_race_mean_race = with_race.to_df().groupby(['RACE'])['INCTOT'].mean()\n",
    "with_race_mean_race"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "with_race_mean_race.plot.barh()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Again, we'll put these charts together so we can better compare how these different subsets' numbers relate to each other:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "b = pd.DataFrame(list(zip(bay_area_mean_race, with_race_mean_race)), bay_area_mean_race.keys())\n",
    "ax = b.plot.barh(stacked=False)\n",
    "ax.legend([\"Entire Bay Area\", \"Biased subset\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "As we noted when we were investigating the data from workers in the Tech industry, it is more helpful to consider race and gender together.\n",
    "\n",
    "For the entire Bay Area:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "bay_area2.to_df().groupby(['RACE', 'SEX'])['INCTOT'].mean().unstack().plot.barh()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For our biased subset:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "with_race.to_df().groupby(['RACE', 'SEX'])['INCTOT'].mean().unstack().plot.barh()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "***Please fill out our [modules feedback survey](https://goo.gl/forms/QCgq3B5uA5npe5ja2)!***"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "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.6.1"
  },
  "widgets": {
   "state": {
    "b6053d95112a4d3b9232ede3111ec7af": {
     "views": [
      {
       "cell_index": 46
      }
     ]
    }
   },
   "version": "1.2.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}