<commit_before>{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#[Work work, money money][repeatyoutube]\n",
    "Combine job board and BLS data to find trends in job / industry growth in Chicago and elsewhere.\n",
    "\n",
    "##Data:\n",
    "- BLS: http://www.bls.gov/help/hlpforma.htm,\n",
    "    + Occupational Employment Statistics http://download.bls.gov/pub/time.series/oe/\n",
    "    + National Compensation Survey http://download.bls.gov/pub/time.series/nc/\n",
    "- Census:\n",
    "    + county shapefiles https://www.census.gov/geo/maps-data/data/tiger-cart-boundary.html\n",
    "    + or, better, <NAME>'s [US-atlas project] [usatlas] (census shapefiles ⇒ GeoJSON)\n",
    "- [JobsAggregator][ja]: (results from all 5 of [Indeed][indeed], [SimplyHired][simply], [CareerBuilder][cb], [Monster][monster], and [CareerJet][cj]): \n",
    "\n",
    "##Technology:\n",
    "- Apache Spark on an Amazon EC2 (Elastic Cloud 2) cluster; instructions below\n",
    "- input data stored in Amazon S3 buckets, output written to HDFS permanent storage\n",
    "- Images rendered in D3 via a private [Lightning][lightning] server\n",
    "\n",
    "[bls_api]: http://www.bls.gov/developers/api_python.htm\n",
    "[cb]: http://developer.careerbuilder.com/\n",
    "[cj]: http://www.careerjet.com/partners/\n",
    "[indeed]: http://www.indeed.com/publisher\n",
    "[ja]: http://www.jobsaggregator.com/US/\n",
    "[lightning]: http://lightning-viz.org/documentation/\n",
    "[monster]: http://partner.monster.com/developers\n",
    "[repeatyoutube]: http://listenonrepeat.com/watch/?v=SvpsoEOJ0_E\n",
    "[simply]: https://simply-partner.com/partners-signup\n",
    "[usatlas]: https://github.com/mbostock/us-atlas"
   ]
  },
  {
   "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}<commit_msg>workbook<commit_after>{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#[Work work, money money][repeatyoutube]\n",
    "Combine job board and BLS data to find trends in job / industry growth in Chicago and elsewhere.\n",
    "\n",
    "##Data:\n",
    "- BLS: http://www.bls.gov/help/hlpforma.htm,\n",
    "    + Occupational Employment Statistics http://download.bls.gov/pub/time.series/oe/\n",
    "    + National Compensation Survey http://download.bls.gov/pub/time.series/nc/\n",
    "- Census:\n",
    "    + county shapefiles https://www.census.gov/geo/maps-data/data/tiger-cart-boundary.html\n",
    "    + or, better, Mike Bostock's [US-atlas project] [usatlas] (census shapefiles ⇒ GeoJSON)\n",
    "- [JobsAggregator][ja]: (results from all 5 of [Indeed][indeed], [SimplyHired][simply], [CareerBuilder][cb], [Monster][monster], and [CareerJet][cj]): \n",
    "\n",
    "##Technology:\n",
    "- Apache Spark on an Amazon EC2 (Elastic Cloud 2) cluster; instructions below\n",
    "- input data stored in Amazon S3 buckets, output written to HDFS permanent storage\n",
    "- Images rendered in D3 via a private [Lightning][lightning] server\n",
    "\n",
    "[bls_api]: http://www.bls.gov/developers/api_python.htm\n",
    "[cb]: http://developer.careerbuilder.com/\n",
    "[cj]: http://www.careerjet.com/partners/\n",
    "[indeed]: http://www.indeed.com/publisher\n",
    "[ja]: http://www.jobsaggregator.com/US/\n",
    "[lightning]: http://lightning-viz.org/documentation/\n",
    "[monster]: http://partner.monster.com/developers\n",
    "[repeatyoutube]: http://listenonrepeat.com/watch/?v=SvpsoEOJ0_E\n",
    "[simply]: https://simply-partner.com/partners-signup\n",
    "[usatlas]: https://github.com/mbostock/us-atlas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##Steps to launch Spark AWS EC2 cluster\n",
    "Here is how to do your own:\n",
    "\n",
    "0. Get an [Amazon Web Services account] [aws_main]\n",
    "1. Get the [newest version of Spark, pre-built for Hadoop 2.4] [spark_source]. It has to be\n",
    "   pre-built so that the pyspark client will have the proper jars\n",
    "        \n",
    "        curl -O http://www.apache.org/dyn/closer.cgi/spark/spark-1.3.0/spark-1.3.0-bin-hadoop2.4.tgz\n",
    "        tar -xzvf spark-1.3.0-bin-hadoop2.4.tgz\n",
    "        \n",
    "\n",
    "2. Launch it on Amazon EC2 using a script in the ec2 directory in the source [instructions] [ec2_quickstart] \n",
    "\n",
    "        cd spark-1.3.0-bin-hadoop2.4\n",
    "        export SPARK_HOME=`pwd`\n",
    "        $SPARK_HOME/ec2/spark-ec2  --slaves 2  \\\n",
    "            --key-pair <Amazon_Keypair_Name>  \\\n",
    "            --identity-file <path/to/Amazon_Keypair.pem>  \\\n",
    "            --copy-aws-credentials  \\\n",
    "            --zone us-east-1b --instance-type=m1.medium  \\\n",
    "            launch spark_cluster\n",
    "\n",
    "3. At the end of the startup run, it will show a URL we can use to connect, or else\n",
    "   navigate to the EC2 dashboard through the [Amazon Web Service Console] [aws_console]\n",
    "   to find out what the IP address is for the master node. The Spark dashboard is on\n",
    "   port 8080 by default:  `<ip address>:8080` <br/>\n",
    "   Mine is here: http://ec2-54-166-72-95.compute-1.amazonaws.com:8080/\n",
    "\n",
    "\n",
    "[aws_main]: http://aws.amazon.com/\n",
    "[spark_source]: https://spark.apache.org/downloads.html\n",
    "[ec2_quickstart]: http://spark.apache.org/docs/latest/ec2-scripts.html\n",
    "[aws_console]: https://console.aws.amazon.com"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##Steps to launch IPython notebook connecting to AWS EC2 cluster\n",
    "\n",
    "A handful of blogs describe how to set up IPython + Spark; they're helpful but outdated:\n",
    "\n",
    "1. [Presentation of two distinct ways to do it][cloudera]\n",
    "2. [Too much information][fperez]\n",
    "3. [Two separate ways presented as if they were one][ramheiser]\n",
    "\n",
    "IPython options are built into pyspark. Follow option 3 above to create a password-protected IPython notebook configuration file, but instead of setting `c.NotebookApp.ip = *` like it says, use the Master's designated address. (e.g. `'ec2-54-166-72-95.compute-1.amazonaws.com'`).\n",
    "\n",
    "###Configuration for Python 2.7\n",
    "PySpark prefers Python 2.7 but Python 2.6 is the default Python for an Amazon EC2 instance. The below are a set of scripts to install Python 2.7 and some dependencies I need (`requests`, `pymongo` and `lxml`). All of the others are PySpark dependencies.\n",
    "\n",
    "SSH in to the master node and execute:\n",
    "\n",
    "    xargs -L1 -a commands.txt ./go.sh\n",
    "\n",
    "\n",
    "####with `commands.txt` containing:\n",
    "\n",
    "    yes | yum install python27-devel\n",
    "    unlink /etc/alternatives/python\n",
    "    ln -s /usr/bin/python2.7 /etc/alternatives/python\n",
    "    \n",
    "    wget https://bootstrap.pypa.io/ez_setup.py\n",
    "    python ez_setup.py\n",
    "    easy_install pip\n",
    "    rm ez_setup.py\n",
    "    rm setuptools-16.0.zip \n",
    "\n",
    "    easy_install Cython\n",
    "    yes | yum install freetype-devel\n",
    "    yes | yum install libpng-devel\n",
    "    pip install numpy scipy\n",
    "    pip install matplotlib\n",
    "    \n",
    "    yes | yum install libxml2-devel\n",
    "    yes | yum install libxslt-devel\n",
    "    pip install requests lxml pymongo\n",
    "    pip install ipython[notebook]\n",
    "\n",
    "\n",
    "####and `go.sh` containing:\n",
    "\n",
    "    #!/usr/bin/env bash\n",
    "    \n",
    "    echo '------------------------------------------------'\n",
    "    echo $@;  $@\n",
    "    \n",
    "    while read worker\n",
    "    do\n",
    "      ssh ${worker} \"echo 'machine ${worker}'; $@\"\n",
    "    done < /root/spark/conf/slaves\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "###Back to launching an IPython notebook\n",
    "1. Add a Custom TCP rule to the `spark_cluster-master` Amazon EC2 security group to allow the port for the IPython notebook (8888 by default; Cloudera says to watch for potential port assignment collisions but 8888 worked fine.)\n",
    "    a. Navigate  to the [EC2 console][ec2_console]\n",
    "    b. Click on the master instance, and then on the security group assigned to that instance\n",
    "    c. It will open another user interface. Click on **Actions** → **Edit inbound rules** and add a custom TCP rule with protocol **TCP**, port range **8888**, and source **Anywhere**\n",
    "\n",
    "2. Set environment variables for `<spark-home>/bin/pyspark` to use to launch a properly configured IPython notebook, ready to use pyspark. As of Spark 1.2:\n",
    "   \n",
    "        export PYSPARK_DRIVER_PYTHON=ipython\n",
    "        export PYSPARK_DRIVER_PYTHON_OPTS='notebook --profile=pyspark'\n",
    "    \n",
    "\n",
    "    \n",
    "3. Launch pyspark. Specifically designate the master node or else `pyspark` will run as a local standalone spark instance:\n",
    "        \n",
    "        /root/spark/bin/pyspark --master spark://ec2-54-166-72-95.compute-1.amazonaws.com:7077\n",
    "    \n",
    "\n",
    "And to ensure persistence after logging out, the above was wrapped in `nohup <command> &` \n",
    "\n",
    "[ec2_console]: https://console.aws.amazon.com/ec2/v2\n",
    "[spark_submit]: https://spark.apache.org/docs/1.2.0/submitting-applications.html\n",
    "[aws_console]: https://console.aws.amazon.com\n",
    "[fperez]: http://nbviewer.ipython.org/gist/fperez/6384491/00-Setup-IPython-PySpark.ipynb\n",
    "[cloudera]: http://blog.cloudera.com/blog/2014/08/how-to-use-ipython-notebook-with-apache-spark/\n",
    "[ramheiser]: http://ramhiser.com/2015/02/01/configuring-ipython-notebook-support-for-pyspark/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[1, 2]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "###\n",
    "# There is now a 'SparkContext' instance available as the named variable 'sc'\n",
    "# and there is a HiveContext instance (for SQL-like queries) available as 'sqlCtx'\n",
    "#\n",
    "## Check that this simple code runs without error:\n",
    "sc.parallelize([1,2,3,4,5]).take(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "###\n",
    "# Inspect the SparkContext [sc] or the HiveContext [sqlCtx]\n",
    "#help(sc)\n",
    "help(sqlCtx)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##De Pie :: (parallel calculation)\n",
    "\n",
    "    ${SPARK_HOME}/spark/examples/src/main/python/pi.py\n",
    "\n",
    "<img src=\"http://www.mixingbowlgal.com/wp-content/uploads/2013/06/IMG_3999.jpg\" style=\"width:250px;\"></img>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3.140564"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from random import random\n",
    "from operator import add\n",
    "\n",
    "def monte_carlo(_):\n",
    "    \"\"\"4 * area (1 quadrant of a unit circle)  pi\"\"\"\n",
    "    x = random()\n",
    "    y = random()\n",
    "    return 4.0 if pow(x, 2) + pow(y, 2) < 1 else 0\n",
    "\n",
    "N = 1000\n",
    "parts = 2\n",
    "sc.parallelize(xrange(N), parts).map(monte_carlo).reduce(add) / N"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##The data\n",
    "We will merge a job postings dataset and the BLS Occupations and Earnings data together, using location, occupation, and possibly industry category.\n",
    "\n",
    "\n",
    "###Amazon S3 buckets\n",
    "\n",
    "Information on connecting to S3 from Spark is at the bottom of the\n",
    "[Spark docs on EC2 scripts] [ec2-scripts], and copied here:\n",
    "\n",
    "\n",
    "> You can specify a path in S3 as input through a URI of the form `s3n://<bucket>/path`. To provide AWS credentials for S3 access, launch the Spark cluster with the option `--copy-aws-credentials`. Full instructions on S3 access using the Hadoop input libraries can be found on the [Hadoop S3 page] [hadoop-s3].\n",
    ">\n",
    "> In addition to using a single input file, you can also use a directory of files as input by simply giving the path to the directory.\n",
    "\n",
    "Separate files listing the BLS categories were loaded to an [Amazon S3 bucket][s3buckets]: `tts-wwmm/areas.txt`, `tts-wwmm/industry.txt`, `tts-wwmm/occupations.txt`. All are two-column files with no headers, and a tab separating the variable code and the variable label.\n",
    "\n",
    "\n",
    "###The JobsAggregator data\n",
    "\n",
    "JobsAggregator aggregates from Indeed, SimplyHired, CareerBuilder, Monster, and CareerJet, showing the most recent job posts on each site.\n",
    "\n",
    "The function `scrape` in the file `jobs_aggregator_scraper.py` iteratively scrapes the site, and returns a generator that yields current job listings (as a dictionary) for a given state and occupation.\n",
    "\n",
    "###The BLS data\n",
    "\n",
    "The data are actually *not* best pulled via the [BLS API][blsapi]. It has only the most recent year's statistics; the rest are archived at http://www.bls.gov/oes/tables.htm.\n",
    "\n",
    "The contents of the archive files were loaded to a MongoDB database. There was manual work to handle different column names and file formats for the different years. Data are available by occupation at the state and national level, and at more aggregated levels for municipal areas. Below is an example of one observation. The `ANNUAL` and `OVERALL` entries are lists of dictionaries with one entry per year, possibly with data from as far back as 2000.\n",
    "\n",
    "```\n",
    "{    \"AREA\": \"3800003\",\n",
    "\"AREA_NAME\": \"East Central North Dakota\",\n",
    "       \"ST\": \"ND\",\n",
    " \"OCC_CODE\": \"39-1012\",\n",
    "\"OCC_TITLE\": \"Slot key persons\",\n",
    "   \"ANNUAL\": [\n",
    "        {\n",
    "             \"YEAR\": 2009,\n",
    "            \"pct90\": 34530,\n",
    "            \"pct75\": 32730,\n",
    "            \"pct50\": 30170,\n",
    "            \"pct25\": 27610,\n",
    "            \"pct10\": 19250\n",
    "        }\n",
    "    ],\n",
    "    \"OVERALL\": [\n",
    "        {\n",
    "               \"YEAR\": 2009,\n",
    "          \"JOBS_1000\": 0.923,\n",
    "            \"TOT_EMP\": 40,\n",
    "             \"A_MEAN\": 29100,\n",
    "          \"MEAN_PRSE\": 5.6,\n",
    "             \"H_MEAN\": 13.99,\n",
    "           \"EMP_PRSE\": 30.2\n",
    "        }\n",
    "    ]\n",
    "}\n",
    "```\n",
    "\n",
    "[blsapi]: http://www.bls.gov/developers/api_sample_code.htm\n",
    "[ec2-scripts]: https://spark.apache.org/docs/1.2.0/ec2-scripts.html\n",
    "[hadoop-s3]: http://wiki.apache.org/hadoop/AmazonS3\n",
    "[s3buckets]: http://docs.aws.amazon.com/AmazonS3/latest/dev/UsingBucket.html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##Load the lookup tables from Amazon S3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[Row(AREA_NAME=u'Alabama', AREA=u'S0100000'), Row(AREA_NAME=u'Alaska', AREA=u'S0200000')]\n"
     ]
    }
   ],
   "source": [
    "### ------------------------------------------------- AMAZON ----- ###\n",
    "# ⇒ These files identify columns that will be common to the job\n",
    "#    board data and the BLS datasets.\n",
    "#\n",
    "# To use S3 buckets add `--copy-aws-credentials` to the ec2 launch command.\n",
    "#\n",
    "# Create a Resilient Distributed Dataset with the\n",
    "# list of occupations in the BLS dataset:\n",
    "#     https://s3.amazonaws.com/tts-wwmm/occupations.txt\n",
    "from pyspark.sql import Row\n",
    "\n",
    "# Load the occupations lookups and convert each line to a Row.\n",
    "lines = sc.textFile('s3n://tts-wwmm/occupations.txt')\n",
    "Occupation = Row('OCC_CODE', 'OCC_TITLE')\n",
    "occ = lines.map(lambda l: Occupation( *l.split('\\t') ))\n",
    "\n",
    "# Do the same for the areas lookups.\n",
    "lines = sc.textFile('s3n://tts-wwmm/areas.txt')\n",
    "Area = Row('AREA', 'AREA_NAME')\n",
    "area = lines.map(lambda l: Area( *l.split('\\t') ))\n",
    "area_df = sqlCtx.createDataFrame(area)\n",
    "area_df.registerTempTable('area')\n",
    "\n",
    "# Just to show how sqlCtx.sql works\n",
    "states = sqlCtx.sql(\"SELECT AREA_NAME, AREA FROM area WHERE AREA RLIKE '^S.*'\")\n",
    "print states.take(2)\n",
    "\n",
    "# Same as above, but result is another Resilient Distributed Dataset\n",
    "states = area.filter(lambda a: a.AREA.startswith('S'))\n",
    "\n",
    "# Create every combination of occupation, state\n",
    "occ_by_states = occ.cartesian(states)\n",
    "\n",
    "# Broadcast makes a static copy of the variable available to all nodes\n",
    "#broadcast_state_names = sc.broadcast(broadcast_state_names)\n",
    "#\n",
    "#print broadcast_state_names.take(2)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##Scrape JobsAggregator"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "### ----------------------------------------- JOBS_AGGREGATOR ----- ###\n",
    "#\n",
    "# Make `jobs_aggregator_scraper.py` available on all nodes\n",
    "# and iteratively get the top 5 jobs from each poster in each state for\n",
    "# each occupation via JobsAggregator.com\n",
    "sc.addPyFile('s3n://tts-wwmm/jobsaggregator_scraper.py')\n",
    "\n",
    "def scrape_occupation(occ_state):\n",
    "    from jobsaggregator_scraper import scrape\n",
    "    occ_row, state_entry = occ_state\n",
    "    return [Row(**job)\n",
    "            for job in scrape(state=state_entry[1], occupation=occ_row.OCC_TITLE)]\n",
    "\n",
    "jobs = occ_by_states.flatMap(scrape_occupation).distinct()\n",
    "jobs_df = sqlCtx.inferSchema(jobs)\n",
    "jobs_df.registerTempTable('jobs')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "jobs_df.toJSON().saveAsTextFile('wwmm/jobsaggregator_json')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "jobs.saveAsTextFile('wwmm/jobsaggregator_df')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Row(area_name=u'Pennsylvania', date='2015-5-25', description='dishwasher: dennys is hiring in new kensington pa dishwasher responsibilities ensure all dishware is clean ', employer='dennys', job_title='dishwasher', occ_title=u'Dishwashers'),\n",
       " Row(area_name=u'California', date='2015-5-23', description='part time dog walker, pet sitter:  job is ideal for future vet techs dog moms i am looking people with a solid work ethic people who are mature organization with a steady contribution an ideal candidate would have a background in or human health care for dog ', employer='', job_title='part time dog walker, pet sitter', occ_title=u'Animal-Trainers')]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "jobs.take(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "##Load OES Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{u'ANNUAL': [{u'pct75': 54250.0, u'pct50': 33840.0, u'pct10': 17690.0, u'pct90': 83140.0, u'YEAR': 2010, u'pct25': 22150.0}, {u'pct75': 55470.0, u'pct50': 34460.0, u'pct10': 18000.0, u'pct90': 85280.0, u'YEAR': 2011, u'pct25': 22380.0}, {u'pct75': 56200.0, u'pct50': 34750.0, u'pct10': 18090.0, u'pct90': 86810.0, u'YEAR': 2012, u'pct25': 22480.0}, {u'pct75': 56860.0, u'pct50': 35080.0, u'pct10': 18190.0, u'pct90': 88330.0, u'YEAR': 2013, u'pct25': 22670.0}, {u'pct75': 57720.0, u'pct50': 35540.0, u'pct10': 18350.0, u'pct90': 90060.0, u'YEAR': 2014, u'pct25': 22950.0}], u'OCC_CODE': u'00-0000', u'OVERALL': [{u'TOT_EMP': 132588810.0, u'YEAR': 2013, u'A_MEAN': 46440.0, u'MEAN_PRSE': 0.1, u'H_MEAN': 22.33, u'EMP_PRSE': 0.1}, {u'TOT_EMP': 135128260.0, u'YEAR': 2014, u'A_MEAN': 47230.0, u'MEAN_PRSE': 0.1, u'H_MEAN': 22.71, u'EMP_PRSE': 0.1}], u'OCC_TITLE': u'All Occupations'}\n"
     ]
    }
   ],
   "source": [
    "### -------------------------------------------- BLS OES DATA ----- ###\n",
    "#\n",
    "# The OES data were loaded to a mongolabs database. Read the URI\n",
    "# (which has a user name and password) from an environment variable\n",
    "# and create a connection. The pymongo API is very simple.\n",
    "#\n",
    "# Datasets are stored one entry per Occupation ID (OCC_ID)\n",
    "# per area (00-0000)\n",
    "from pymongo import MongoClient\n",
    "\n",
    "MONGO_URI = os.getenv('MONGO_URI')\n",
    "client = MongoClient(MONGO_URI)  # connection\n",
    "oe = client.oe  # database\n",
    "\n",
    "# Confirm we can get data from each collection\n",
    "oo = oe['nat'].find(filter={'OCC_CODE':'00-0000'},\n",
    "               projection={'_id':False,\n",
    "                           'OCC_CODE':True, 'OCC_TITLE':True,\n",
    "                           'ANNUAL':{'$slice':-5}, 'OVERALL':{'$slice':-2}})\n",
    "\n",
    "for o in oo:\n",
    "    print o"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Which OCC contains software-type people?\n",
    "occ_df = sqlCtx.createDataFrame(occ)\n",
    "occ_df.registerTempTable('occ')\n",
    "\n",
    "computer_jobs = sqlCtx.sql((\n",
    "    \"SELECT OCC_CODE, OCC_TITLE \"\n",
    "     \"FROM occ \"\n",
    "     \"WHERE OCC_TITLE RLIKE 'omputer'\"\n",
    "    )).collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "11-3021: Computer and Information Systems Managers\n",
      "15-0000: Computer and Mathematical Occupations\n",
      "15-1100: Computer Occupations\n",
      "15-1111: Computer and Information Research Scientists\n",
      "15-1120: Computer and Information Analysts\n",
      "15-1121: Computer Systems Analysts\n",
      "15-1131: Computer Programmers\n",
      "15-1142: Network and Computer Systems Administrators\n",
      "15-1143: Computer Network Architects\n",
      "15-1150: Computer Support Specialists\n",
      "15-1151: Computer User Support Specialists\n",
      "15-1152: Computer Network Support Specialists\n",
      "15-1199: Computer Occupations, All Other\n",
      "17-2061: Computer Hardware Engineers\n",
      "17-2072: Electronics Engineers, Except Computer\n",
      "25-1020: Math and Computer Teachers, Postsecondary\n",
      "25-1021: Computer Science Teachers, Postsecondary\n",
      "43-9011: Computer Operators\n",
      "43-9071: Office Machine Operators, Except Computer\n",
      "49-2011: Computer, Automated Teller, and Office Machine Repairers\n",
      "51-4010: Computer Control Programmers and Operators\n",
      "51-4011: Computer-Controlled Machine Tool Operators, Metal and Plastic\n",
      "51-4012: Computer Numerically Controlled Machine Tool Programmers, Metal and Plastic\n"
     ]
    }
   ],
   "source": [
    "for row in computer_jobs:\n",
    "    print \"{OCC_CODE}: {OCC_TITLE}\".format(**row.asDict())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "M0016974: Chicago-Joliet-Naperville, IL Metropolitan Division\n",
      "M0016980: Chicago-Joliet-Naperville, IL-IN-WI\n",
      "S1700000: Illinois\n",
      "M1700001: Northwest Illinois nonmetropolitan area\n",
      "M1700002: West Central Illinois nonmetropolitan area\n",
      "M1700003: East Central Illinois nonmetropolitan area\n",
      "M1700004: South Illinois nonmetropolitan area\n"
     ]
    }
   ],
   "source": [
    "# Want Chicago's area code\n",
    "\n",
    "chicago = sqlCtx.sql((\n",
    "    \"SELECT AREA, AREA_NAME \"\n",
    "     \"FROM area \"\n",
    "     \"WHERE AREA_NAME RLIKE 'icago' or AREA_NAME RLIKE 'llinois'\"\n",
    "    )).collect()\n",
    "\n",
    "print \"\\n\".join(\"{}: {}\".format(c.AREA, c.AREA_NAME) for c in chicago)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Now get the data:\n",
    "## -------------------------------------- National\n",
    "\n",
    "desired_data = {'_id':False,\n",
    "                'ANNUAL':{'$slice':-5}, 'OVERALL':{'$slice':-2}}\n",
    "nat = oe['nat'].find(filter={'OCC_CODE':'15-1131'},\n",
    "               projection=desired_data)\n",
    "\n",
    "nat = [n for n in nat]\n",
    "len(nat)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## -------------------------------------- State\n",
    "\n",
    "il = oe['st'].find(filter={'OCC_CODE':'15-1131',\n",
    "                           'AREA':'17'},\n",
    "                  projection=desired_data)\n",
    "\n",
    "il = [i for i in il]\n",
    "len(il)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## -------------------------------------- Municipal Areas\n",
    "## The lookup for chicago didn't work...\n",
    "## ... so I am looking through all of the municipal areas...\n",
    "chi = oe['ma'].find(filter={'OCC_CODE':'15-1131'},\n",
    "                   projection=desired_data)\n",
    "chi = [c for c in chi if 'IL' in c['AREA_NAME']]\n",
    "len(chi)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Get the mean \n",
    "import tablib\n",
    "\n",
    "nat_annual = tablib.Dataset()\n",
    "nat_annual.dict = nat[0]['ANNUAL']\n",
    "il_annual = tablib.Dataset()\n",
    "il_annual.dict = il[0]['ANNUAL']\n",
    "chi_annual = tablib.Dataset()\n",
    "chi_annual.dict = chi[1]['ANNUAL']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##Lightning-viz plots for inline D3.js in IPython\n",
    "\n",
    "http://lightning-viz.org/\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from lightning import Lightning\n",
    "\n",
    "lgn = Lightning(host=\"https://tts-lightning.herokuapp.com\",\n",
    "                ipython=True,\n",
    "                auth=(\"<EMAIL>\", \"password\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<meta name=\"viewport\" content=\"width=device-width, initial-scale=1, maximum-scale=1\"/><meta http-equiv=\"X-UA-Compatible\" content=\"IE=10; IE=9; IE=8; IE=7; IE=EDGE\"/><meta charset=\"UTF-8\"/><link href=\"//fonts.googleapis.com/css?family=Open+Sans:400,700\" rel=\"stylesheet\" type=\"text/css\"/><link rel=\"stylesheet\" href=\"https://tts-lightning.herokuapp.com/css/app.css\"/><link rel=\"stylesheet\" href=\"https://tts-lightning.herokuapp.com/css/dynamic/viz/?visualizations[]=line\"/><div id=\"lightning-body\" class=\"container content wrap push\"><div class=\"feed-container\"><div data-model=\"visualization\" data-model-id=\"12\" class=\"feed-item-container\"><div data-type=\"line\" data-data=\"{&quot;index&quot;:[2010,2011,2012,2013,2014],&quot;yaxis&quot;:[&quot;Median annual salary&quot;],&quot;color&quot;:[[0,0,0],[255,0,0],[0,155,0]],&quot;series&quot;:[[71380,72630,74280,76140,77550],[69300,70650,71390,72280,73270],[70680,66040,74700,75120,86180]],&quot;xaxis&quot;:[&quot;Year&quot;],&quot;size&quot;:[5,2,2]}\" data-images=\"null\" data-options=\"{&quot;logScaleY&quot;:false,&quot;logScaleX&quot;:false}\" id=\"viz-12\" data-initialized=\"false\" class=\"feed-item\"></div></div></div></div><script>window.lightning = window.lightning || {};\n",
       "window.lightning.host = \"https://tts-lightning.herokuapp.com/\" || 'http://127.0.0.1:3000/';\n",
       "window.lightning.vizCount = (window.lightning.vizCount + 1) || 1;\n",
       "window.lightning.requiredVizTypes = window.lightning.requiredVizTypes || [];\n",
       "if(window.lightning.requiredVizTypes.indexOf(\"line\") === -1) {\n",
       "    window.lightning.requiredVizTypes.push(\"line\");\n",
       "}\n",
       "window._require = window.require;\n",
       "window.require = undefined;\n",
       "window._define = window.define;\n",
       "window.define = undefined;</script><script src=\"https://tts-lightning.herokuapp.com/js/embed.js\"></script>"
      ],
      "text/plain": [
       "<lightning.types.plots.Line at 0x7f014e3bbf10>"
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Median salaries\n",
    "lgn.line(series=[nat_annual['pct50'], il_annual['pct50'], chi_annual['pct50']],\n",
    "         index=nat_annual['YEAR'],\n",
    "         color=[[0,0,0],[255,0,0],[0,155,0]],\n",
    "         size=[5,2,2],\n",
    "         xaxis=\"Year\",\n",
    "         yaxis=\"Median annual salary\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "53"
      ]
     },
     "execution_count": 112,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# How about regionally?\n",
    "\n",
    "all_states = oe['st'].find(filter={'OCC_CODE':'15-1131'},\n",
    "                  projection={'$_id': False,\n",
    "                             'OVERALL':{'$slice':-2}})\n",
    "all_states = [a for a in all_states]\n",
    "len(all_states)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "state_abbrs = [a['ST'] for a  in all_states]\n",
    "mean_salaries = [a['OVERALL'][0]['A_MEAN'] for a in all_states]\n",
    "num_employed = [a['OVERALL'][0]['TOT_EMP'] for a in all_states]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "max average salary: 111320.0\n",
      "Illinois: 74620.0\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<meta name=\"viewport\" content=\"width=device-width, initial-scale=1, maximum-scale=1\"/><meta http-equiv=\"X-UA-Compatible\" content=\"IE=10; IE=9; IE=8; IE=7; IE=EDGE\"/><meta charset=\"UTF-8\"/><link href=\"//fonts.googleapis.com/css?family=Open+Sans:400,700\" rel=\"stylesheet\" type=\"text/css\"/><link rel=\"stylesheet\" href=\"https://tts-lightning.herokuapp.com/css/app.css\"/><link rel=\"stylesheet\" href=\"https://tts-lightning.herokuapp.com/css/dynamic/viz/?visualizations[]=map\"/><div id=\"lightning-body\" class=\"container content wrap push\"><div class=\"feed-container\"><div data-model=\"visualization\" data-model-id=\"16\" class=\"feed-item-container\"><div data-type=\"map\" data-data=\"{&quot;regions&quot;:[&quot;MT&quot;,&quot;WV&quot;,&quot;PA&quot;,&quot;TX&quot;,&quot;SC&quot;,&quot;VT&quot;,&quot;UT&quot;,&quot;GU&quot;,&quot;WA&quot;,&quot;AK&quot;,&quot;MA&quot;,&quot;MI&quot;,&quot;AL&quot;,&quot;CA&quot;,&quot;KY&quot;,&quot;AZ&quot;,&quot;AR&quot;,&quot;SD&quot;,&quot;TN&quot;,&quot;CO&quot;,&quot;CT&quot;,&quot;MS&quot;,&quot;MO&quot;,&quot;OK&quot;,&quot;OR&quot;,&quot;VA&quot;,&quot;MD&quot;,&quot;WY&quot;,&quot;OH&quot;,&quot;MN&quot;,&quot;PR&quot;,&quot;KS&quot;,&quot;ND&quot;,&quot;DC&quot;,&quot;DE&quot;,&quot;GA&quot;,&quot;FL&quot;,&quot;HI&quot;,&quot;LA&quot;,&quot;IL&quot;,&quot;ID&quot;,&quot;IA&quot;,&quot;IN&quot;,&quot;NE&quot;,&quot;ME&quot;,&quot;NC&quot;,&quot;NY&quot;,&quot;NM&quot;,&quot;NJ&quot;,&quot;NH&quot;,&quot;WI&quot;,&quot;NV&quot;,&quot;RI&quot;],&quot;values&quot;:[65210,56110,74080,79990,68650,64830,76290,61650,111320,78560,82750,69060,80490,89450,62760,77750,68350,52530,75660,90500,86480,57650,71520,65550,72750,78300,94100,63980,68880,78390,44930,70680,53850,91700,83770,89170,75570,63570,65480,74620,72100,64550,63050,75400,68740,77670,83700,96190,89880,70060,72540,74540,79270]}\" data-images=\"null\" data-options=\"{}\" id=\"viz-16\" data-initialized=\"false\" class=\"feed-item\"></div></div></div></div><script>window.lightning = window.lightning || {};\n",
       "window.lightning.host = \"https://tts-lightning.herokuapp.com/\" || 'http://127.0.0.1:3000/';\n",
       "window.lightning.vizCount = (window.lightning.vizCount + 1) || 1;\n",
       "window.lightning.requiredVizTypes = window.lightning.requiredVizTypes || [];\n",
       "if(window.lightning.requiredVizTypes.indexOf(\"map\") === -1) {\n",
       "    window.lightning.requiredVizTypes.push(\"map\");\n",
       "}\n",
       "window._require = window.require;\n",
       "window.require = undefined;\n",
       "window._define = window.define;\n",
       "window.define = undefined;</script><script src=\"https://tts-lightning.herokuapp.com/js/embed.js\"></script>"
      ],
      "text/plain": [
       "<lightning.types.plots.Map at 0x7f014d648d90>"
      ]
     },
     "execution_count": 118,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Mean salaries\n",
    "print \"max average salary:\", max(mean_salaries)\n",
    "print \"Illinois:\", mean_salaries[state_abbrs.index('IL')]\n",
    "lgn.map(regions=state_abbrs, values=mean_salaries)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Most programmers: 38750.0\n",
      "Illinois: 20620.0\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<meta name=\"viewport\" content=\"width=device-width, initial-scale=1, maximum-scale=1\"/><meta http-equiv=\"X-UA-Compatible\" content=\"IE=10; IE=9; IE=8; IE=7; IE=EDGE\"/><meta charset=\"UTF-8\"/><link href=\"//fonts.googleapis.com/css?family=Open+Sans:400,700\" rel=\"stylesheet\" type=\"text/css\"/><link rel=\"stylesheet\" href=\"https://tts-lightning.herokuapp.com/css/app.css\"/><link rel=\"stylesheet\" href=\"https://tts-lightning.herokuapp.com/css/dynamic/viz/?visualizations[]=map\"/><div id=\"lightning-body\" class=\"container content wrap push\"><div class=\"feed-container\"><div data-model=\"visualization\" data-model-id=\"19\" class=\"feed-item-container\"><div data-type=\"map\" data-data=\"{&quot;regions&quot;:[&quot;MT&quot;,&quot;WV&quot;,&quot;PA&quot;,&quot;TX&quot;,&quot;SC&quot;,&quot;VT&quot;,&quot;UT&quot;,&quot;GU&quot;,&quot;WA&quot;,&quot;AK&quot;,&quot;MA&quot;,&quot;MI&quot;,&quot;AL&quot;,&quot;CA&quot;,&quot;KY&quot;,&quot;AZ&quot;,&quot;AR&quot;,&quot;SD&quot;,&quot;TN&quot;,&quot;CO&quot;,&quot;CT&quot;,&quot;MS&quot;,&quot;MO&quot;,&quot;OK&quot;,&quot;OR&quot;,&quot;VA&quot;,&quot;MD&quot;,&quot;WY&quot;,&quot;OH&quot;,&quot;MN&quot;,&quot;PR&quot;,&quot;KS&quot;,&quot;ND&quot;,&quot;DC&quot;,&quot;DE&quot;,&quot;GA&quot;,&quot;FL&quot;,&quot;HI&quot;,&quot;LA&quot;,&quot;IL&quot;,&quot;ID&quot;,&quot;IA&quot;,&quot;IN&quot;,&quot;NE&quot;,&quot;ME&quot;,&quot;NC&quot;,&quot;NY&quot;,&quot;NM&quot;,&quot;NJ&quot;,&quot;NH&quot;,&quot;WI&quot;,&quot;NV&quot;,&quot;RI&quot;],&quot;values&quot;:[780,800,13660,23040,3820,330,4940,40,15640,620,7950,7920,5260,38750,1510,4550,3590,510,4540,4040,3740,920,8560,2110,2880,8660,6980,170,6860,5700,1340,3570,320,1350,1310,11250,15630,600,2000,20620,950,1840,4650,2790,860,7200,22020,1370,13710,1380,7650,1320,1120]}\" data-images=\"null\" data-options=\"{}\" id=\"viz-19\" data-initialized=\"false\" class=\"feed-item\"></div></div></div></div><script>window.lightning = window.lightning || {};\n",
       "window.lightning.host = \"https://tts-lightning.herokuapp.com/\" || 'http://127.0.0.1:3000/';\n",
       "window.lightning.vizCount = (window.lightning.vizCount + 1) || 1;\n",
       "window.lightning.requiredVizTypes = window.lightning.requiredVizTypes || [];\n",
       "if(window.lightning.requiredVizTypes.indexOf(\"map\") === -1) {\n",
       "    window.lightning.requiredVizTypes.push(\"map\");\n",
       "}\n",
       "window._require = window.require;\n",
       "window.require = undefined;\n",
       "window._define = window.define;\n",
       "window.define = undefined;</script><script src=\"https://tts-lightning.herokuapp.com/js/embed.js\"></script>"
      ],
      "text/plain": [
       "<lightning.types.plots.Map at 0x7f014d648990>"
      ]
     },
     "execution_count": 121,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Employees\n",
    "print \"Most programmers:\", max(num_employed)\n",
    "print \"Illinois:\", num_employed[state_abbrs.index('IL')]\n",
    "lgn.map(regions=state_abbrs, values=num_employed)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "salaries = tablib.Dataset(*zip(state_abbrs, mean_salaries),\n",
    "                          headers=('State', 'Salary'))\n",
    "employees= tablib.Dataset(*zip(state_abbrs, num_employed),\n",
    "                         headers=('State', 'Employees'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "WA: 111,320\n",
      "NM: 96,190\n",
      "MD: 94,100\n",
      "DC: 91,700\n",
      "CO: 90,500\n"
     ]
    }
   ],
   "source": [
    "salaries = salaries.sort(\"Salary\", reverse=True)\n",
    "print \"\\n\".join(\"{s[0]}: {s[1]:0,.0f}\".format(s=s) for s in salaries[:5])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CA: 38,750\n",
      "TX: 23,040\n",
      "NY: 22,020\n",
      "IL: 20,620\n",
      "WA: 15,640\n"
     ]
    }
   ],
   "source": [
    "employees = employees.sort(\"Employees\", reverse=True)\n",
    "print \"\\n\".join(\"{e[0]}: {e[1]:0,.0f}\".format(e=e) for e in employees[:5])"
   ]
  }
 ],
 "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.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}
{'fraction_non_alphanumeric': 0.21182983682983683, 'fraction_numerical': 0.043414918414918416, 'mean_word_length': 2.8007567368032484, 'pattern_counts': {'":': 320, '<': 73, '<?xml version=': 0, '>': 74, 'https://': 27, 'lorem ipsum': 0, 'www.': 16, 'xml': 3}, 'pii_count': 3, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 6, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '6019150', 'n_tokens_mistral': 16507, 'n_tokens_neox': 15182, 'n_words': 2906}