02_transform.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Transform\n",
    "\n",
    "Opens and combines [annual H2A visa disclosure data files](https://www.foreignlaborcert.doleta.gov/performancedata.cfm) from the Office of Foreign Labor Certification at the United States Department of Labor. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "input_dir = os.path.join(os.getcwd(), 'input')\n",
    "output_dir = os.path.join(os.getcwd(), 'output')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%capture\n",
    "os.path.exists(input_dir) or os.mkdirs(intput_dir)\n",
    "os.path.exists(output_dir) or os.mkdirs(output_dir)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A crosswalk that standardizes the headers from each year's file to a common schema"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "schema = {\n",
    "    2008: dict(\n",
    "        case_number=\"CASE_NO\",\n",
    "        case_status=\"CASE_STATUS\",\n",
    "        workers_certified=\"NBR_WORKERS_CERTIFIED\",\n",
    "        employer=\"EMPLOYER_NAME\",\n",
    "        city=\"ALIEN_WORK_CITY\",\n",
    "        state=\"ALIEN_WORK_STATE\",\n",
    "        job_title=\"JOB_TITLE\",\n",
    "        certification_start_date=\"CERTIFICATION_BEGIN_DATE\",\n",
    "        \n",
    "    ),\n",
    "    2009: dict(\n",
    "        case_number=\"CASE_NO\",\n",
    "        case_status=\"CASE_STATUS\",\n",
    "        workers_certified=\"NBR_WORKERS_CERTIFIED\",\n",
    "        employer=\"EMPLOYER_NAME\",\n",
    "        city=\"ALIEN_WORK_CITY\",\n",
    "        state=\"ALIEN_WORK_STATE\",\n",
    "        job_title=\"JOB_TITLE\",\n",
    "        certification_start_date=\"CERTIFICATION_BEGIN_DATE\",\n",
    "    ),\n",
    "    2010: dict(\n",
    "        case_number=\"CASE_NO\",\n",
    "        case_status=\"CASE_STATUS\",\n",
    "        workers_certified=\"NBR_WORKERS_CERTIFIED\",\n",
    "        employer=\"EMPLOYER_NAME\",\n",
    "        city=\"ALIEN_WORK_CITY\",\n",
    "        state=\"ALIEN_WORK_STATE\",\n",
    "        job_title=\"JOB_TITLE\",\n",
    "        certification_start_date=\"CERTIFICATION_BEGIN_DATE\",\n",
    "    ),\n",
    "    2011: dict(\n",
    "        case_number=\"CASE_NO\",\n",
    "        case_status=\"CASE_STATUS\",\n",
    "        workers_certified=\"NBR_WORKERS_CERTIFIED\",\n",
    "        employer=\"EMPLOYER_NAME\",\n",
    "        city=\"ALIEN_WORK_CITY\",\n",
    "        state=\"ALIEN_WORK_STATE\",\n",
    "        crop=\"PRIMARY_CROP\",\n",
    "        job_title=\"JOB_TITLE\",\n",
    "        certification_start_date=\"CERTIFICATION_BEGIN_DATE\",\n",
    "    ),\n",
    "    2012: dict(\n",
    "        case_number=\"CASE_NO\",\n",
    "        case_status=\"CASE_STATUS\",\n",
    "        workers_certified=\"NBR_WORKERS_CERTIFIED\",\n",
    "        employer=\"EMPLOYER_NAME\",\n",
    "        city=\"ALIEN_WORK_CITY\",\n",
    "        state=\"ALIEN_WORK_STATE\",\n",
    "        crop=\"PRIMARY_CROP\",\n",
    "        job_title=\"JOB_TITLE\",\n",
    "        certification_start_date=\"CERTIFICATION_BEGIN_DATE\",\n",
    "    ),\n",
    "    2013: dict(\n",
    "        case_number=\"CASE_NO\",\n",
    "        case_status=\"CASE_STATUS\",\n",
    "        workers_certified=\"NBR_WORKERS_CERTIFIED\",\n",
    "        employer=\"EMPLOYER_NAME\",\n",
    "        city=\"ALIEN_WORK_CITY\",\n",
    "        state=\"ALIEN_WORK_STATE\",\n",
    "        job_title=\"JOB_TITLE\",\n",
    "        certification_start_date=\"CERTIFICATION_BEGIN_DATE\",\n",
    "    ),\n",
    "    2014: dict(\n",
    "        case_number=\"CASE_NO\",\n",
    "        case_status=\"CASE_STATUS\",\n",
    "        workers_certified=\"NBR_WORKERS_CERTIFIED\",\n",
    "        employer=\"EMPLOYER_NAME\",\n",
    "        city=\"WORKSITE_LOCATION_CITY\",\n",
    "        state=\"WORKSITE_LOCATION_STATE\",\n",
    "        job_title=\"JOB_TITLE\",\n",
    "        certification_start_date=\"CERTIFICATION_BEGIN_DATE\",\n",
    "    ),\n",
    "    2015: dict(\n",
    "        case_number=\"CASE_NUMBER\",\n",
    "        case_status=\"CASE_STATUS\",\n",
    "        workers_certified=\"NBR_WORKERS_CERTIFIED\",\n",
    "        employer=\"EMPLOYER_NAME\",\n",
    "        city=\"WORKSITE_CITY\",\n",
    "        state=\"WORKSITE_STATE\",\n",
    "        crop=\"PRIMARY_CROP\",\n",
    "        job_title=\"JOB_TITLE\",\n",
    "        certification_start_date=\"CERTIFICATION_BEGIN_DATE\",\n",
    "    ),\n",
    "    2016: dict(\n",
    "        case_number=\"CASE_NUMBER\",\n",
    "        case_status=\"CASE_STATUS\",\n",
    "        workers_certified=\"NBR_WORKERS_CERTIFIED\",\n",
    "        employer=\"EMPLOYER_NAME\",\n",
    "        city=\"WORKSITE_CITY\",\n",
    "        state=\"WORKSITE_STATE\",\n",
    "        crop=\"PRIMARY_CROP\",\n",
    "        job_title=\"JOB_TITLE\",\n",
    "        certification_start_date=\"CERTIFICATION_BEGIN_DATE\",\n",
    "    ),\n",
    "    2017: dict(\n",
    "        case_number=\"CASE_NUMBER\",\n",
    "        case_status=\"CASE_STATUS\",\n",
    "        workers_certified=\"NBR_WORKERS_CERTIFIED\",\n",
    "        employer=\"EMPLOYER_NAME\",\n",
    "        city=\"WORKSITE_CITY\",\n",
    "        state=\"WORKSITE_STATE\",\n",
    "        crop=\"PRIMARY_CROP\",\n",
    "        job_title=\"JOB_TITLE\",\n",
    "        certification_start_date=\"CERTIFICATION_BEGIN_DATE\",\n",
    "    ),\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def transform_xls(year):\n",
    "    \"\"\"\n",
    "    Transforms the H2A visa data from the provided year.\n",
    "    \n",
    "    Returns it cleaned up, deduped and standardized for consolidation with other years.\n",
    "    \"\"\"\n",
    "    print(\"Transforming {} data\".format(year))\n",
    "\n",
    "    # Open the raw file\n",
    "    input_path = os.path.join(\n",
    "        input_dir,\n",
    "        \"{}.xlsx\".format(year)\n",
    "    )\n",
    "    df = pd.read_excel(input_path)\n",
    "    \n",
    "    # Pull the schema for this year\n",
    "    s = schema[year]\n",
    "\n",
    "    # Reverse the schemaand standardize the column names\n",
    "    i = {v: k for k, v in s.iteritems()}\n",
    "    df = df.rename(columns=i)\n",
    "    \n",
    "    # Trim down to just the columns we want to keep\n",
    "    trimmed = df[s.keys()]\n",
    "\n",
    "    # Add the fiscal year from the file name\n",
    "    trimmed['fiscal_year'] = year\n",
    "    \n",
    "    # Add a column with the calendar year where the jobs were certified\n",
    "    trimmed.certification_start_date = pd.to_datetime(trimmed.certification_start_date)\n",
    "    trimmed['certification_start_year'] = trimmed.apply(lambda x: x.certification_start_date.year, axis=1)\n",
    "    \n",
    "    # Trim applications from outside the time range we want to analyze\n",
    "    trimmed = trimmed[\n",
    "        (trimmed.certification_start_year > 2007) &\n",
    "        (trimmed.certification_start_year < 2018)\n",
    "    ]\n",
    "   \n",
    "    # Add incremental id\n",
    "    trimmed['row_number'] = range(len(trimmed))\n",
    "\n",
    "    # Combine that with the fiscal year into a unique id\n",
    "    trimmed['latimes_id'] = trimmed.apply(lambda x: \"{}-{}\".format(x.fiscal_year, x.row_number), axis=1)\n",
    "\n",
    "    # A little clean up on numbers\n",
    "    trimmed.workers_certified.fillna(0, inplace=True)\n",
    "    trimmed.workers_certified = trimmed.workers_certified.astype(int)\n",
    "    \n",
    "    # Drop duplicate case records, as prescribed by the DOL.\n",
    "    ## They say, \"When analyzing H-2A program use, it is recommended to\n",
    "    ## use the total number of workers certified (NBR_WORKERS_CERTIFIED)\n",
    "    ## listed on the \"master\" record (i.e., the first employer record\n",
    "    ## listed in the series of duplicate case numbers), which\n",
    "    ## represents the total unique number of workers certified for\n",
    "    ## the entire H-2A application, including all of the \"sub\" employer records.\n",
    "    \n",
    "    # HOWEVER! Close examination of the data shows that we cannot depend on the master\n",
    "    # master record coming first in the spreadsheets.\n",
    "    \n",
    "    # On the advice of the DOL, we will resort the data so that the record\n",
    "    # with the maximum workers certified comes first and will then infer\n",
    "    # that is must be the parent record.\n",
    "    dupes = trimmed.groupby(\"case_number\").filter(lambda x: len(x) > 1).sort_values(\n",
    "        [\"case_number\", \"workers_certified\"],\n",
    "        ascending=[True, False]\n",
    "    )\n",
    "    master_cases = dupes.drop_duplicates(\"case_number\", keep=\"first\")\n",
    "    master_cases['master_case'] = True\n",
    "    sub_cases = dupes[~dupes['latimes_id'].isin(master_cases.latimes_id)]\n",
    "    deduped = trimmed[~trimmed.case_number.isin(dupes.case_number)]\n",
    "    deduped['master_case'] = False\n",
    "    deduped = deduped.append(master_cases)\n",
    "    \n",
    "    # Filter it down to only those applications that were approved\n",
    "    approved_status_list = [\n",
    "        'DETERMINATION ISSUED - CERTIFICATION',\n",
    "        'DETERMINATION ISSUED - PARTIAL CERTIFICATION',\n",
    "        'Certified - Full',\n",
    "        'Certified - Partial',\n",
    "        # According to interview with a DOL official,\n",
    "        # the expired certifications should be included.\n",
    "        # They are only marked that way due to a bookkeeping error\n",
    "        # when the records are pulled for public disclosure.\n",
    "        'DETERMINATION ISSUED - CERTIFICATION EXPIRED',\n",
    "        'DETERMINATION ISSUED - PARTIAL CERTIFICATION EXPIRED',\n",
    "    ]\n",
    "    certified = deduped[deduped.case_status.isin(approved_status_list)]\n",
    "    sub_cases = sub_cases[sub_cases.case_status.isin(approved_status_list)]\n",
    "       \n",
    "    # Pass the DataFrames back\n",
    "    return certified, sub_cases"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Process all the annual files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Transforming 2008 data\n",
      "Transforming 2009 data\n",
      "Transforming 2010 data\n",
      "Transforming 2011 data\n",
      "Transforming 2012 data\n",
      "Transforming 2013 data\n",
      "Transforming 2014 data\n",
      "Transforming 2015 data\n",
      "Transforming 2016 data\n",
      "Transforming 2017 data\n"
     ]
    }
   ],
   "source": [
    "master_list, sub_list = zip(*[transform_xls(y) for y in sorted(schema.keys())])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Merge all the dataframes into big ones"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "master_df = pd.concat(master_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "sub_df = pd.concat(sub_list)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Filter out any case number that appear in multiple years"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "master_df.sort_values(\n",
    "    [\"case_number\", \"fiscal_year\"],\n",
    "    ascending=[True, False]\n",
    ").drop_duplicates(\"case_number\", keep=\"first\", inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Standardize common variations on crop names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "clean_dict = {\n",
    "    'Ag Eq Operator': 'Agricultural Equipment Operators',\n",
    "    'Ag Eqp Operator;': 'Agricultural Equipment Operators',\n",
    "    'Ag Equip Oper': 'Agricultural Equipment Operators',\n",
    "    'Ag Equipment Operator': 'Agricultural Equipment Operators',\n",
    "    'Ag equip operator': 'Agricultural Equipment Operators',\n",
    "    'Agricultural Equipment Operator': 'Agricultural Equipment Operators',\n",
    "    'Air Cured': 'Tobacco',\n",
    "    'Apple Drops': 'Apples',\n",
    "    'Asian Pears': 'Pears',\n",
    "    'Bartlett Pears': 'Pears',\n",
    "    'Bell Peppers': 'Peppers',\n",
    "    'Dwarf Apples': 'Apples',\n",
    "    'Romaine': 'Lettuce',\n",
    "    'Spinich': 'Lettuce',\n",
    "    'Spinach': 'Lettuce',\n",
    "    'Onion': 'Onions',\n",
    "    \"Romaine Lettuce\": \"Lettuce\",\n",
    "    \"Iceburg Lettuce\": \"Lettuce\",\n",
    "    \"sheep\": \"Sheep\",\n",
    "    'Vineyards': 'Grapes',\n",
    "    'Iceburg Lettuce': 'Lettuce',\n",
    "    'Harvest Strawberries': \"Strawberries\",\n",
    "    \"Vidalia Onions\": \"Onion\",\n",
    "    'Nursery': 'Nursery or greenhouse',\n",
    "    'Nursery Work': 'Nursery or greenhouse',\n",
    "    \"Greenhouses\": 'Nursery or greenhouse',\n",
    "    \"Nurseries & Greenhouses\": 'Nursery or greenhouse',\n",
    "    'Nursery and Greenhouse Worker': 'Nursery or greenhouse',\n",
    "    'Nursery and Greenhouse Workers': 'Nursery or greenhouse',\n",
    "    'Purple Hull Peas': 'Other crops',\n",
    "    'Sugercane': 'Sugarcane',\n",
    "    'Sugar Cane': 'Sugarcane',\n",
    "    'Sugarcane': 'Sugarcane',\n",
    "    'Sugar Beets': 'Beets',\n",
    "    'Sweet Onions': 'Onions',\n",
    "    'Sweet Peppers': 'Peppers',\n",
    "    'Valencia Oranges': 'Oranges',\n",
    "    'Watermelon': 'Watermelons',\n",
    "    'Yellow Cherries': 'Cherries',\n",
    "    'Jalapeno Peppers': 'Peppers',\n",
    "    'Chile Peppers': 'Peppers',\n",
    "    'Kale': \"Lettuce\",\n",
    "    \"Construction of Livestock buildings\": 'Construction of Livestock Buildings',\n",
    "    \"Construction of Livestock Buildings\": \"Construction of Livestock Buildings\",\n",
    "    \"Construction, Livestock Building\": \"Construction of Livestock Buildings\",\n",
    "    'Construction of Livestock  Buildings': 'Construction of Livestock Buildings',\n",
    "    'Construction of livestock buildings.': 'Construction of Livestock Buildings',\n",
    "    'Construction of livestocks buildings': 'Construction of Livestock Buildings',\n",
    "    'Constructionof Livestock Buildings': 'Construction of Livestock Buildings',\n",
    "    'Consturcition of Livestock Buildings': 'Construction of Livestock Buildings',\n",
    "    'Construction Livestock Buildings': 'Construction of Livestock Buildings',\n",
    "    'Custom Combine ': 'Custom Combine',\n",
    "    \"Chili Peppers\": \"Peppers\",\n",
    "    \"Flue Cured\": \"Tobacco\",\n",
    "    \"HARVEST, GATHER, COUNT AND PACKAGE;WATERMELON\": \"Watermelosn\",\n",
    "    \"Hand harvest peaches\": \"Peaches\",\n",
    "    \"Harvest Corn\": \"Corn\",\n",
    "    \"Harvesting Citrus and other fruits\": \"Citrus\",\n",
    "    \"Harvesting Watermelons\": \"Watermelons\",\n",
    "    \"Hay And Straw\": \"Hay\",\n",
    "    \"Hay/Straw\": \"Hay\",\n",
    "    \"Open range cattle\": \"Cattle\",\n",
    "    \"ALL PRODUCITON OF HANDLING SMALL BLAES OF HAY\": \"Hay\",\n",
    "    \"Grapes Harvest\": \"Grapes\",\n",
    "    \"Vineyards\": \"Grapes\",\n",
    "    \"Grass Hay\": \"Hay\",\n",
    "    \"Hay & Straw\": \"Hay\",\n",
    "    'Straw': \"Hay\",\n",
    "    'Straw/Hay': \"Hay\",\n",
    "    \"Straw\": \"Hay\",\n",
    "    'Straw': \"Hay\",\n",
    "    \"Custom Harvester Wheat, corn, small grain\": \"Wheat\",\n",
    "    \"OPEN RANGE CATTLE\": \"Cattle\",\n",
    "    \"Wathermelon Unloaders and Watermelon Packing\": \"Watermelons\",\n",
    "    'Apple': \"Apples\",\n",
    "    'Apple Harvest': \"Apples\",\n",
    "    'Detassel Corn': \"Corn\",\n",
    "    'Detasseling Corn': \"Corn\",\n",
    "    'Calves': \"Cattle\",\n",
    "    'Calving': \"Cattle\",\n",
    "    'Cattle Herder': \"Cattle\",\n",
    "    'Cattle Worker': \"Cattle\",\n",
    "    'TOBACCO': \"Tobacco\",\n",
    "    'Tobacco - Topping & Oiling': \"Tobacco\",\n",
    "    'Tobacco -Setting': \"Tobacco\",\n",
    "    'Tobbaco': \"Tobacco\",\n",
    "    'Sweet Corn': \"Corn\",\n",
    "    'Sweet corn, harvest': \"Corn\",\n",
    "    'Watermelosn': \"Watermelons\",\n",
    "    'Stripping Tobacco': \"Tobacco\",\n",
    "    \"Farm worker\": \"Other\",\n",
    "    \"General Farmworker\": \"Other\",\n",
    "    \"Grain\": \"Grains\",\n",
    "    \"Nursery Stock\": \"Nursery or greenhouse\",\n",
    "    \"Sheepherder\": \"Sheep\",\n",
    "    \"Farm Worker\": \"Other\",\n",
    "    \"Sheep Shearer\": \"Sheep\",\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "master_df['latimes_crop'] = master_df.apply(lambda x: clean_dict.get(x.crop, x.crop), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "sub_df['latimes_crop'] = sub_df.apply(lambda x: clean_dict.get(x.crop, x.crop), axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create a combined file that merges master and sub cases."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, sum up the total number of workers certified for subcases of each case number"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "subcase_workers = sub_df.groupby(\"case_number\").agg(dict(workers_certified=\"sum\")).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "subcase_workers.columns = ['case_number', 'workers_subcases']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Merge that number to the master list for comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "combined_df = pd.merge(\n",
    "    master_df,\n",
    "    subcase_workers,\n",
    "    how=\"left\",\n",
    "    on=\"case_number\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Append all subcases with case numbers that appear in the master list to the combined list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "linked_subcases = sub_df[sub_df.case_number.isin(combined_df.case_number)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "linked_subcases['sub_case'] = True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "combined_df = combined_df.append(linked_subcases)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Zero out the subcases worker count where it is empty"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "combined_df.workers_subcases.fillna(0, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Calculate a net worker count by subtracting the subcase count from the total. This prevents double counting subcase positions included in master cases."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "combined_df['net_workers'] = combined_df.apply(\n",
    "    lambda x: x.workers_certified - x.workers_subcases,\n",
    "    axis=1\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Drop master cases with zero workers after subtracting their subcases"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "net_df = combined_df[combined_df.net_workers > 0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Write out the transformed files for analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "master_df.to_csv(\n",
    "    os.path.join(output_dir, \"transformed_master_cases.csv\"),\n",
    "    index=False,\n",
    "    encoding=\"utf-8\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "sub_df.to_csv(\n",
    "    os.path.join(output_dir, \"transformed_sub_cases.csv\"),\n",
    "    index=False,\n",
    "    encoding=\"utf-8\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "net_df.to_csv(\n",
    "    os.path.join(output_dir, \"transformed_all_cases.csv\"),\n",
    "    index=False,\n",
    "    encoding=\"utf-8\"\n",
    ")"
   ]
  }
 ],
 "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.12+"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}