notebooks/01-merge-bls-data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This notebook takes various data sources from the BLS and merges them into a master sheet that will be used in conjunction with the EEOC data to produce aggregate data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load and merge data from BLS\n",
    "\n",
    "The code below pulls data from these two Bureau of Labor Statistics datasets:\n",
    "\n",
    "- Current Employment Statistics (for Sept. 2016, seasonally adjusted)\n",
    "- The most recent National Occupational Employment and Wage Estimates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "907"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(pd.read_csv('../data/CES_total_employment.csv'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "CES_total_employment = pd.read_csv('../data/CES_total_employment.csv')\\\n",
    "    .assign(metric=\"total_employment\")\n",
    "\n",
    "CES_avg_hrly_earnings = pd.read_csv('../data/CES_avg_hrly_earnings.csv')\\\n",
    "    .assign(metric=\"avg_hrly_earnings\")\n",
    "    \n",
    "CES_women_employment = pd.read_csv('../data/CES_women_employment.csv')\\\n",
    "    .assign(metric=\"women_employment\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Series ID</th>\n",
       "      <th>Jan 2016</th>\n",
       "      <th>Feb 2016</th>\n",
       "      <th>Mar 2016</th>\n",
       "      <th>Apr 2016</th>\n",
       "      <th>May 2016</th>\n",
       "      <th>Jun 2016</th>\n",
       "      <th>Jul 2016</th>\n",
       "      <th>Aug 2016</th>\n",
       "      <th>Sep 2016</th>\n",
       "      <th>Oct 2016</th>\n",
       "      <th>Nov 2016</th>\n",
       "      <th>Dec 2016</th>\n",
       "      <th>metric</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CES0000000001</td>\n",
       "      <td>143211.0</td>\n",
       "      <td>143448.0</td>\n",
       "      <td>143673.0</td>\n",
       "      <td>143826.0</td>\n",
       "      <td>143869.0</td>\n",
       "      <td>144166.0</td>\n",
       "      <td>144457.0</td>\n",
       "      <td>144633.0</td>\n",
       "      <td>144882.0</td>\n",
       "      <td>145006.0</td>\n",
       "      <td>145170.0</td>\n",
       "      <td>145325.0</td>\n",
       "      <td>total_employment</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CES0500000001</td>\n",
       "      <td>121097.0</td>\n",
       "      <td>121318.0</td>\n",
       "      <td>121507.0</td>\n",
       "      <td>121665.0</td>\n",
       "      <td>121682.0</td>\n",
       "      <td>121951.0</td>\n",
       "      <td>122200.0</td>\n",
       "      <td>122343.0</td>\n",
       "      <td>122566.0</td>\n",
       "      <td>122698.0</td>\n",
       "      <td>122876.0</td>\n",
       "      <td>123026.0</td>\n",
       "      <td>total_employment</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CES0600000001</td>\n",
       "      <td>19754.0</td>\n",
       "      <td>19747.0</td>\n",
       "      <td>19752.0</td>\n",
       "      <td>19743.0</td>\n",
       "      <td>19699.0</td>\n",
       "      <td>19705.0</td>\n",
       "      <td>19731.0</td>\n",
       "      <td>19707.0</td>\n",
       "      <td>19718.0</td>\n",
       "      <td>19727.0</td>\n",
       "      <td>19762.0</td>\n",
       "      <td>19794.0</td>\n",
       "      <td>total_employment</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CES0700000001</td>\n",
       "      <td>123457.0</td>\n",
       "      <td>123701.0</td>\n",
       "      <td>123921.0</td>\n",
       "      <td>124083.0</td>\n",
       "      <td>124170.0</td>\n",
       "      <td>124461.0</td>\n",
       "      <td>124726.0</td>\n",
       "      <td>124926.0</td>\n",
       "      <td>125164.0</td>\n",
       "      <td>125279.0</td>\n",
       "      <td>125408.0</td>\n",
       "      <td>125531.0</td>\n",
       "      <td>total_employment</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CES0800000001</td>\n",
       "      <td>101343.0</td>\n",
       "      <td>101571.0</td>\n",
       "      <td>101755.0</td>\n",
       "      <td>101922.0</td>\n",
       "      <td>101983.0</td>\n",
       "      <td>102246.0</td>\n",
       "      <td>102469.0</td>\n",
       "      <td>102636.0</td>\n",
       "      <td>102848.0</td>\n",
       "      <td>102971.0</td>\n",
       "      <td>103114.0</td>\n",
       "      <td>103232.0</td>\n",
       "      <td>total_employment</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Series ID  Jan 2016  Feb 2016  Mar 2016  Apr 2016  May 2016  Jun 2016  \\\n",
       "0  CES0000000001  143211.0  143448.0  143673.0  143826.0  143869.0  144166.0   \n",
       "1  CES0500000001  121097.0  121318.0  121507.0  121665.0  121682.0  121951.0   \n",
       "2  CES0600000001   19754.0   19747.0   19752.0   19743.0   19699.0   19705.0   \n",
       "3  CES0700000001  123457.0  123701.0  123921.0  124083.0  124170.0  124461.0   \n",
       "4  CES0800000001  101343.0  101571.0  101755.0  101922.0  101983.0  102246.0   \n",
       "\n",
       "   Jul 2016  Aug 2016  Sep 2016  Oct 2016  Nov 2016  Dec 2016  \\\n",
       "0  144457.0  144633.0  144882.0  145006.0  145170.0  145325.0   \n",
       "1  122200.0  122343.0  122566.0  122698.0  122876.0  123026.0   \n",
       "2   19731.0   19707.0   19718.0   19727.0   19762.0   19794.0   \n",
       "3  124726.0  124926.0  125164.0  125279.0  125408.0  125531.0   \n",
       "4  102469.0  102636.0  102848.0  102971.0  103114.0  103232.0   \n",
       "\n",
       "             metric  \n",
       "0  total_employment  \n",
       "1  total_employment  \n",
       "2  total_employment  \n",
       "3  total_employment  \n",
       "4  total_employment  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CES_total_employment.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Combine CES metrics\n",
    "\n",
    "Note: See https://www.bls.gov/ces/cesnaics.htm#2.3 for details on Series ID structure."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>metric</th>\n",
       "      <th>industry_code</th>\n",
       "      <th>avg_hrly_earnings</th>\n",
       "      <th>total_employment</th>\n",
       "      <th>women_employment</th>\n",
       "      <th>naics_supersector</th>\n",
       "      <th>naics_code</th>\n",
       "      <th>naics_sector_rollup</th>\n",
       "      <th>women_percentage</th>\n",
       "      <th>source</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>144882.0</td>\n",
       "      <td>71873.0</td>\n",
       "      <td>00</td>\n",
       "      <td>000000</td>\n",
       "      <td>00</td>\n",
       "      <td>0.496080</td>\n",
       "      <td>ces</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>05000000</td>\n",
       "      <td>25.81</td>\n",
       "      <td>122566.0</td>\n",
       "      <td>59073.0</td>\n",
       "      <td>05</td>\n",
       "      <td>000000</td>\n",
       "      <td>00</td>\n",
       "      <td>0.481969</td>\n",
       "      <td>ces</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>06000000</td>\n",
       "      <td>27.09</td>\n",
       "      <td>19718.0</td>\n",
       "      <td>4325.0</td>\n",
       "      <td>06</td>\n",
       "      <td>000000</td>\n",
       "      <td>00</td>\n",
       "      <td>0.219343</td>\n",
       "      <td>ces</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>07000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>125164.0</td>\n",
       "      <td>67548.0</td>\n",
       "      <td>07</td>\n",
       "      <td>000000</td>\n",
       "      <td>00</td>\n",
       "      <td>0.539676</td>\n",
       "      <td>ces</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>08000000</td>\n",
       "      <td>25.52</td>\n",
       "      <td>102848.0</td>\n",
       "      <td>54748.0</td>\n",
       "      <td>08</td>\n",
       "      <td>000000</td>\n",
       "      <td>00</td>\n",
       "      <td>0.532320</td>\n",
       "      <td>ces</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "metric industry_code  avg_hrly_earnings  total_employment  women_employment  \\\n",
       "0           00000000                NaN          144882.0           71873.0   \n",
       "1           05000000              25.81          122566.0           59073.0   \n",
       "2           06000000              27.09           19718.0            4325.0   \n",
       "3           07000000                NaN          125164.0           67548.0   \n",
       "4           08000000              25.52          102848.0           54748.0   \n",
       "\n",
       "metric naics_supersector naics_code naics_sector_rollup  women_percentage  \\\n",
       "0                     00     000000                  00          0.496080   \n",
       "1                     05     000000                  00          0.481969   \n",
       "2                     06     000000                  00          0.219343   \n",
       "3                     07     000000                  00          0.539676   \n",
       "4                     08     000000                  00          0.532320   \n",
       "\n",
       "metric source  \n",
       "0         ces  \n",
       "1         ces  \n",
       "2         ces  \n",
       "3         ces  \n",
       "4         ces  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ces_industry_metrics = pd.concat([\n",
    "    CES_total_employment,\n",
    "    CES_avg_hrly_earnings,\n",
    "    CES_women_employment\n",
    "])\\\n",
    "    .assign(industry_code=lambda x: x[\"Series ID\"].str[3:11])\\\n",
    "    .set_index([ \"industry_code\",  \"metric\" ])[\"Sep 2016\"].unstack()\\\n",
    "    .reset_index()\\\n",
    "    .assign(naics_supersector=lambda x: x[\"industry_code\"].str[0:2])\\\n",
    "    .assign(naics_code=lambda x: x[\"industry_code\"].str[2:8])\\\n",
    "    .assign(naics_sector_rollup=lambda x: x[\"naics_code\"].str[:2])\n",
    "\n",
    "ces_industry_metrics[\"women_percentage\"] = ces_industry_metrics[\"women_employment\"] / ces_industry_metrics[\"total_employment\"]\n",
    "ces_industry_metrics[\"source\"] = \"ces\"\n",
    "ces_industry_metrics.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "908"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(ces_industry_metrics)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Save industry metrics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "ces_industry_metrics.to_csv('../output/ces_industry_metrics.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Sector metrics"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load NAICS sectors"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>naics_sector</th>\n",
       "      <th>naics_sector_rollup</th>\n",
       "      <th>naics_supersector</th>\n",
       "      <th>naics_sector_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Agriculture, Forestry, Fishing and Hunting</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>21</td>\n",
       "      <td>21</td>\n",
       "      <td>10</td>\n",
       "      <td>Mining, Quarrying, and Oil and Gas Extraction</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>22</td>\n",
       "      <td>22</td>\n",
       "      <td>40</td>\n",
       "      <td>Utilities</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>23</td>\n",
       "      <td>23</td>\n",
       "      <td>20</td>\n",
       "      <td>Construction</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>31</td>\n",
       "      <td>31</td>\n",
       "      <td>30</td>\n",
       "      <td>Manufacturing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>32</td>\n",
       "      <td>31</td>\n",
       "      <td>30</td>\n",
       "      <td>Manufacturing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>33</td>\n",
       "      <td>31</td>\n",
       "      <td>30</td>\n",
       "      <td>Manufacturing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>42</td>\n",
       "      <td>42</td>\n",
       "      <td>40</td>\n",
       "      <td>Wholesale Trade</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>44</td>\n",
       "      <td>44</td>\n",
       "      <td>40</td>\n",
       "      <td>Retail Trade</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>45</td>\n",
       "      <td>44</td>\n",
       "      <td>40</td>\n",
       "      <td>Retail Trade</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>48</td>\n",
       "      <td>48</td>\n",
       "      <td>40</td>\n",
       "      <td>Transportation and Warehousing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>49</td>\n",
       "      <td>48</td>\n",
       "      <td>40</td>\n",
       "      <td>Transportation and Warehousing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>51</td>\n",
       "      <td>51</td>\n",
       "      <td>50</td>\n",
       "      <td>Information</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>52</td>\n",
       "      <td>52</td>\n",
       "      <td>55</td>\n",
       "      <td>Finance and Insurance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>53</td>\n",
       "      <td>53</td>\n",
       "      <td>55</td>\n",
       "      <td>Real Estate and Rental and Leasing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>54</td>\n",
       "      <td>54</td>\n",
       "      <td>60</td>\n",
       "      <td>Professional, Scientific, and Technical Services</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>55</td>\n",
       "      <td>55</td>\n",
       "      <td>60</td>\n",
       "      <td>Management of Companies and Enterprises</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>56</td>\n",
       "      <td>56</td>\n",
       "      <td>60</td>\n",
       "      <td>Administrative and Support and Waste Managemen...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>61</td>\n",
       "      <td>61</td>\n",
       "      <td>65</td>\n",
       "      <td>Educational Services</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>62</td>\n",
       "      <td>62</td>\n",
       "      <td>65</td>\n",
       "      <td>Health Care and Social Assistance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>71</td>\n",
       "      <td>71</td>\n",
       "      <td>70</td>\n",
       "      <td>Arts, Entertainment, and Recreation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>72</td>\n",
       "      <td>72</td>\n",
       "      <td>70</td>\n",
       "      <td>Accommodation and Food Services</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>81</td>\n",
       "      <td>81</td>\n",
       "      <td>80</td>\n",
       "      <td>Other Services (except Public Administration)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>92</td>\n",
       "      <td>92</td>\n",
       "      <td>90</td>\n",
       "      <td>Public Administration</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    naics_sector naics_sector_rollup naics_supersector  \\\n",
       "0             11                  11               NaN   \n",
       "1             21                  21                10   \n",
       "2             22                  22                40   \n",
       "3             23                  23                20   \n",
       "4             31                  31                30   \n",
       "5             32                  31                30   \n",
       "6             33                  31                30   \n",
       "7             42                  42                40   \n",
       "8             44                  44                40   \n",
       "9             45                  44                40   \n",
       "10            48                  48                40   \n",
       "11            49                  48                40   \n",
       "12            51                  51                50   \n",
       "13            52                  52                55   \n",
       "14            53                  53                55   \n",
       "15            54                  54                60   \n",
       "16            55                  55                60   \n",
       "17            56                  56                60   \n",
       "18            61                  61                65   \n",
       "19            62                  62                65   \n",
       "20            71                  71                70   \n",
       "21            72                  72                70   \n",
       "22            81                  81                80   \n",
       "23            92                  92                90   \n",
       "\n",
       "                                    naics_sector_name  \n",
       "0          Agriculture, Forestry, Fishing and Hunting  \n",
       "1       Mining, Quarrying, and Oil and Gas Extraction  \n",
       "2                                           Utilities  \n",
       "3                                        Construction  \n",
       "4                                       Manufacturing  \n",
       "5                                       Manufacturing  \n",
       "6                                       Manufacturing  \n",
       "7                                     Wholesale Trade  \n",
       "8                                        Retail Trade  \n",
       "9                                        Retail Trade  \n",
       "10                     Transportation and Warehousing  \n",
       "11                     Transportation and Warehousing  \n",
       "12                                        Information  \n",
       "13                              Finance and Insurance  \n",
       "14                 Real Estate and Rental and Leasing  \n",
       "15   Professional, Scientific, and Technical Services  \n",
       "16            Management of Companies and Enterprises  \n",
       "17  Administrative and Support and Waste Managemen...  \n",
       "18                               Educational Services  \n",
       "19                  Health Care and Social Assistance  \n",
       "20                Arts, Entertainment, and Recreation  \n",
       "21                    Accommodation and Food Services  \n",
       "22      Other Services (except Public Administration)  \n",
       "23                              Public Administration  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sectors = pd.read_csv(\n",
    "    \"../data/naics_sectors.csv\",\n",
    "    dtype={\n",
    "        \"naics_sector_rollup\": str,\n",
    "        \"naics_supersector\": str\n",
    "    })\n",
    "sectors"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Extract the straightforward sector data from CES\n",
    "\n",
    "I.e., CES series with the industry code \"{NAICS_SECTOR_CODE}0000\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>metric</th>\n",
       "      <th>industry_code</th>\n",
       "      <th>avg_hrly_earnings</th>\n",
       "      <th>total_employment</th>\n",
       "      <th>women_employment</th>\n",
       "      <th>naics_supersector</th>\n",
       "      <th>naics_code</th>\n",
       "      <th>naics_sector_rollup</th>\n",
       "      <th>women_percentage</th>\n",
       "      <th>source</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>10210000</td>\n",
       "      <td>32.95</td>\n",
       "      <td>610.2</td>\n",
       "      <td>92.6</td>\n",
       "      <td>10</td>\n",
       "      <td>210000</td>\n",
       "      <td>21</td>\n",
       "      <td>0.151754</td>\n",
       "      <td>ces</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>323</th>\n",
       "      <td>41420000</td>\n",
       "      <td>29.73</td>\n",
       "      <td>5877.5</td>\n",
       "      <td>1740.8</td>\n",
       "      <td>41</td>\n",
       "      <td>420000</td>\n",
       "      <td>42</td>\n",
       "      <td>0.296180</td>\n",
       "      <td>ces</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>516</th>\n",
       "      <td>44220000</td>\n",
       "      <td>38.35</td>\n",
       "      <td>555.9</td>\n",
       "      <td>129.4</td>\n",
       "      <td>44</td>\n",
       "      <td>220000</td>\n",
       "      <td>22</td>\n",
       "      <td>0.232776</td>\n",
       "      <td>ces</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>552</th>\n",
       "      <td>55520000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6167.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>55</td>\n",
       "      <td>520000</td>\n",
       "      <td>52</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ces</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>593</th>\n",
       "      <td>55530000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2156.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>55</td>\n",
       "      <td>530000</td>\n",
       "      <td>53</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ces</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "metric industry_code  avg_hrly_earnings  total_employment  women_employment  \\\n",
       "7           10210000              32.95             610.2              92.6   \n",
       "323         41420000              29.73            5877.5            1740.8   \n",
       "516         44220000              38.35             555.9             129.4   \n",
       "552         55520000                NaN            6167.0               NaN   \n",
       "593         55530000                NaN            2156.5               NaN   \n",
       "\n",
       "metric naics_supersector naics_code naics_sector_rollup  women_percentage  \\\n",
       "7                     10     210000                  21          0.151754   \n",
       "323                   41     420000                  42          0.296180   \n",
       "516                   44     220000                  22          0.232776   \n",
       "552                   55     520000                  52               NaN   \n",
       "593                   55     530000                  53               NaN   \n",
       "\n",
       "metric source  \n",
       "7         ces  \n",
       "323       ces  \n",
       "516       ces  \n",
       "552       ces  \n",
       "593       ces  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ces_sector_metrics = ces_industry_metrics[\n",
    "    (ces_industry_metrics[\"naics_code\"].str[2:] == \"0000\") &\n",
    "    (ces_industry_metrics[\"naics_sector_rollup\"] != \"00\")\n",
    "]\n",
    "\n",
    "ces_sector_metrics.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Extract supersector data\n",
    "\n",
    "This will be used, further down, to get female workforce numbers where not available in CES"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>naics_supersector</th>\n",
       "      <th>avg_hrly_earnings</th>\n",
       "      <th>total_employment</th>\n",
       "      <th>women_percentage</th>\n",
       "      <th>source</th>\n",
       "      <th>naics_sector_rollup</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>65</td>\n",
       "      <td>25.87</td>\n",
       "      <td>22745.0</td>\n",
       "      <td>0.770851</td>\n",
       "      <td>ces_supersector</td>\n",
       "      <td>62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>70</td>\n",
       "      <td>15.01</td>\n",
       "      <td>15684.0</td>\n",
       "      <td>0.519319</td>\n",
       "      <td>ces_supersector</td>\n",
       "      <td>71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>70</td>\n",
       "      <td>15.01</td>\n",
       "      <td>15684.0</td>\n",
       "      <td>0.519319</td>\n",
       "      <td>ces_supersector</td>\n",
       "      <td>72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>80</td>\n",
       "      <td>23.17</td>\n",
       "      <td>5708.0</td>\n",
       "      <td>0.525578</td>\n",
       "      <td>ces_supersector</td>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>90</td>\n",
       "      <td>NaN</td>\n",
       "      <td>22316.0</td>\n",
       "      <td>0.573579</td>\n",
       "      <td>ces_supersector</td>\n",
       "      <td>92</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   naics_supersector  avg_hrly_earnings  total_employment  women_percentage  \\\n",
       "27                65              25.87           22745.0          0.770851   \n",
       "28                70              15.01           15684.0          0.519319   \n",
       "29                70              15.01           15684.0          0.519319   \n",
       "30                80              23.17            5708.0          0.525578   \n",
       "31                90                NaN           22316.0          0.573579   \n",
       "\n",
       "             source naics_sector_rollup  \n",
       "27  ces_supersector                  62  \n",
       "28  ces_supersector                  71  \n",
       "29  ces_supersector                  72  \n",
       "30  ces_supersector                  81  \n",
       "31  ces_supersector                  92  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ces_supersector_metrics = ces_industry_metrics[\n",
    "    (ces_industry_metrics[\"naics_code\"] == \"000000\")\n",
    "]\\\n",
    "    .assign(naics_supersector=lambda x: x[\"industry_code\"].str[:2])\\\n",
    "    [[ \"naics_supersector\", \"avg_hrly_earnings\", \"total_employment\", \"women_percentage\"]]\\\n",
    "    .assign(source=\"ces_supersector\")\\\n",
    "    .pipe(pd.merge,\n",
    "        sectors[[ \"naics_sector_rollup\", \"naics_supersector\" ]],\n",
    "        how=\"left\"\n",
    "    )\n",
    "ces_supersector_metrics.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Manually add data for agriculture sector"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For the agriculture sector, CES doesn't provide the number of women employees. Below, we add that data manually, based on the Current Population Survey:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>naics_sector_rollup</th>\n",
       "      <th>source</th>\n",
       "      <th>women_percentage</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11</td>\n",
       "      <td>cps</td>\n",
       "      <td>0.224</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  naics_sector_rollup source  women_percentage\n",
       "0                  11    cps             0.224"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ces_sector_metrics_manual = pd.DataFrame([\n",
    "    {\n",
    "        \"naics_sector_rollup\": \"11\",\n",
    "        \"women_percentage\": 0.224,\n",
    "        \"source\": \"cps\"\n",
    "    } # Source: https://www.bls.gov/cps/cpsaat11.htm\n",
    "])\n",
    "ces_sector_metrics_manual"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Extract data from Occupational Employment Statistics\n",
    "\n",
    "(For sectors without data in the CES.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NAICS</th>\n",
       "      <th>NAICS_TITLE</th>\n",
       "      <th>OCC_CODE</th>\n",
       "      <th>OCC_TITLE</th>\n",
       "      <th>OCC_GROUP</th>\n",
       "      <th>TOT_EMP</th>\n",
       "      <th>EMP_PRSE</th>\n",
       "      <th>PCT_TOTAL</th>\n",
       "      <th>PCT_RPT</th>\n",
       "      <th>H_MEAN</th>\n",
       "      <th>...</th>\n",
       "      <th>H_MEDIAN</th>\n",
       "      <th>H_PCT75</th>\n",
       "      <th>H_PCT90</th>\n",
       "      <th>A_PCT10</th>\n",
       "      <th>A_PCT25</th>\n",
       "      <th>A_MEDIAN</th>\n",
       "      <th>A_PCT75</th>\n",
       "      <th>A_PCT90</th>\n",
       "      <th>ANNUAL</th>\n",
       "      <th>HOURLY</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11</td>\n",
       "      <td>Agriculture, Forestry, Fishing and Hunting</td>\n",
       "      <td>00-0000</td>\n",
       "      <td>Industry Total</td>\n",
       "      <td>total</td>\n",
       "      <td>416600</td>\n",
       "      <td>1.2</td>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "      <td>14.33</td>\n",
       "      <td>...</td>\n",
       "      <td>11.36</td>\n",
       "      <td>15.41</td>\n",
       "      <td>22.7</td>\n",
       "      <td>20040</td>\n",
       "      <td>20820</td>\n",
       "      <td>23630</td>\n",
       "      <td>32040</td>\n",
       "      <td>47210</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11</td>\n",
       "      <td>Agriculture, Forestry, Fishing and Hunting</td>\n",
       "      <td>11-0000</td>\n",
       "      <td>Management Occupations</td>\n",
       "      <td>major</td>\n",
       "      <td>7800</td>\n",
       "      <td>5.3</td>\n",
       "      <td>1.87</td>\n",
       "      <td>17</td>\n",
       "      <td>45.79</td>\n",
       "      <td>...</td>\n",
       "      <td>37.78</td>\n",
       "      <td>56.2</td>\n",
       "      <td>87.16</td>\n",
       "      <td>37490</td>\n",
       "      <td>53360</td>\n",
       "      <td>78580</td>\n",
       "      <td>116890</td>\n",
       "      <td>181290</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11</td>\n",
       "      <td>Agriculture, Forestry, Fishing and Hunting</td>\n",
       "      <td>11-1000</td>\n",
       "      <td>Top Executives</td>\n",
       "      <td>minor</td>\n",
       "      <td>3890</td>\n",
       "      <td>8.4</td>\n",
       "      <td>0.93</td>\n",
       "      <td>~</td>\n",
       "      <td>50.58</td>\n",
       "      <td>...</td>\n",
       "      <td>39.87</td>\n",
       "      <td>62.34</td>\n",
       "      <td>99.7</td>\n",
       "      <td>38140</td>\n",
       "      <td>55920</td>\n",
       "      <td>82930</td>\n",
       "      <td>129660</td>\n",
       "      <td>207380</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11</td>\n",
       "      <td>Agriculture, Forestry, Fishing and Hunting</td>\n",
       "      <td>11-1010</td>\n",
       "      <td>Chief Executives</td>\n",
       "      <td>broad</td>\n",
       "      <td>210</td>\n",
       "      <td>14.1</td>\n",
       "      <td>0.05</td>\n",
       "      <td>~</td>\n",
       "      <td>91.3</td>\n",
       "      <td>...</td>\n",
       "      <td>79.43</td>\n",
       "      <td>#</td>\n",
       "      <td>#</td>\n",
       "      <td>81690</td>\n",
       "      <td>98720</td>\n",
       "      <td>165210</td>\n",
       "      <td>#</td>\n",
       "      <td>#</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11</td>\n",
       "      <td>Agriculture, Forestry, Fishing and Hunting</td>\n",
       "      <td>11-1011</td>\n",
       "      <td>Chief Executives</td>\n",
       "      <td>detailed</td>\n",
       "      <td>210</td>\n",
       "      <td>14.1</td>\n",
       "      <td>0.05</td>\n",
       "      <td>1</td>\n",
       "      <td>91.3</td>\n",
       "      <td>...</td>\n",
       "      <td>79.43</td>\n",
       "      <td>#</td>\n",
       "      <td>#</td>\n",
       "      <td>81690</td>\n",
       "      <td>98720</td>\n",
       "      <td>165210</td>\n",
       "      <td>#</td>\n",
       "      <td>#</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  NAICS                                 NAICS_TITLE OCC_CODE  \\\n",
       "0    11  Agriculture, Forestry, Fishing and Hunting  00-0000   \n",
       "1    11  Agriculture, Forestry, Fishing and Hunting  11-0000   \n",
       "2    11  Agriculture, Forestry, Fishing and Hunting  11-1000   \n",
       "3    11  Agriculture, Forestry, Fishing and Hunting  11-1010   \n",
       "4    11  Agriculture, Forestry, Fishing and Hunting  11-1011   \n",
       "\n",
       "                OCC_TITLE OCC_GROUP TOT_EMP EMP_PRSE PCT_TOTAL PCT_RPT H_MEAN  \\\n",
       "0          Industry Total     total  416600      1.2       100     100  14.33   \n",
       "1  Management Occupations     major    7800      5.3      1.87      17  45.79   \n",
       "2          Top Executives     minor    3890      8.4      0.93       ~  50.58   \n",
       "3        Chief Executives     broad     210     14.1      0.05       ~   91.3   \n",
       "4        Chief Executives  detailed     210     14.1      0.05       1   91.3   \n",
       "\n",
       "   ...   H_MEDIAN H_PCT75 H_PCT90 A_PCT10 A_PCT25 A_MEDIAN A_PCT75 A_PCT90  \\\n",
       "0  ...      11.36   15.41    22.7   20040   20820    23630   32040   47210   \n",
       "1  ...      37.78    56.2   87.16   37490   53360    78580  116890  181290   \n",
       "2  ...      39.87   62.34    99.7   38140   55920    82930  129660  207380   \n",
       "3  ...      79.43       #       #   81690   98720   165210       #       #   \n",
       "4  ...      79.43       #       #   81690   98720   165210       #       #   \n",
       "\n",
       "  ANNUAL HOURLY  \n",
       "0    NaN    NaN  \n",
       "1    NaN    NaN  \n",
       "2    NaN    NaN  \n",
       "3    NaN    NaN  \n",
       "4    NaN    NaN  \n",
       "\n",
       "[5 rows x 24 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "oes = pd.read_excel(\"../data/oes-natsector_M2016_dl.xlsx\")\n",
    "oes.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>naics_sector_rollup</th>\n",
       "      <th>total_employment</th>\n",
       "      <th>avg_hrly_earnings</th>\n",
       "      <th>source</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11</td>\n",
       "      <td>416.6</td>\n",
       "      <td>14.33</td>\n",
       "      <td>oes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>291</th>\n",
       "      <td>21</td>\n",
       "      <td>649.13</td>\n",
       "      <td>31.69</td>\n",
       "      <td>oes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>843</th>\n",
       "      <td>22</td>\n",
       "      <td>549.96</td>\n",
       "      <td>36.35</td>\n",
       "      <td>oes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1372</th>\n",
       "      <td>23</td>\n",
       "      <td>6687.38</td>\n",
       "      <td>25.3</td>\n",
       "      <td>oes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2106</th>\n",
       "      <td>31</td>\n",
       "      <td>12337.5</td>\n",
       "      <td>24.39</td>\n",
       "      <td>oes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3101</th>\n",
       "      <td>42</td>\n",
       "      <td>5840.73</td>\n",
       "      <td>27.01</td>\n",
       "      <td>oes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4023</th>\n",
       "      <td>44</td>\n",
       "      <td>15982.5</td>\n",
       "      <td>15.44</td>\n",
       "      <td>oes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4839</th>\n",
       "      <td>48</td>\n",
       "      <td>5606.18</td>\n",
       "      <td>22.57</td>\n",
       "      <td>oes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5658</th>\n",
       "      <td>51</td>\n",
       "      <td>2762.09</td>\n",
       "      <td>34.86</td>\n",
       "      <td>oes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6285</th>\n",
       "      <td>52</td>\n",
       "      <td>5775.24</td>\n",
       "      <td>33.5</td>\n",
       "      <td>oes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6869</th>\n",
       "      <td>53</td>\n",
       "      <td>2110.6</td>\n",
       "      <td>22.4</td>\n",
       "      <td>oes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7564</th>\n",
       "      <td>54</td>\n",
       "      <td>8739.11</td>\n",
       "      <td>38.32</td>\n",
       "      <td>oes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8600</th>\n",
       "      <td>55</td>\n",
       "      <td>2302.59</td>\n",
       "      <td>39.35</td>\n",
       "      <td>oes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9566</th>\n",
       "      <td>56</td>\n",
       "      <td>9070.14</td>\n",
       "      <td>18.26</td>\n",
       "      <td>oes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10724</th>\n",
       "      <td>61</td>\n",
       "      <td>12982.9</td>\n",
       "      <td>26.03</td>\n",
       "      <td>oes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11806</th>\n",
       "      <td>62</td>\n",
       "      <td>19257.9</td>\n",
       "      <td>25.13</td>\n",
       "      <td>oes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12733</th>\n",
       "      <td>71</td>\n",
       "      <td>2322.4</td>\n",
       "      <td>17.43</td>\n",
       "      <td>oes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13488</th>\n",
       "      <td>72</td>\n",
       "      <td>13338.9</td>\n",
       "      <td>12.14</td>\n",
       "      <td>oes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14057</th>\n",
       "      <td>81</td>\n",
       "      <td>4078.8</td>\n",
       "      <td>19.41</td>\n",
       "      <td>oes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15046</th>\n",
       "      <td>92</td>\n",
       "      <td>9589.35</td>\n",
       "      <td>28.26</td>\n",
       "      <td>oes</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      naics_sector_rollup total_employment avg_hrly_earnings source\n",
       "0                      11            416.6             14.33    oes\n",
       "291                    21           649.13             31.69    oes\n",
       "843                    22           549.96             36.35    oes\n",
       "1372                   23          6687.38              25.3    oes\n",
       "2106                   31          12337.5             24.39    oes\n",
       "3101                   42          5840.73             27.01    oes\n",
       "4023                   44          15982.5             15.44    oes\n",
       "4839                   48          5606.18             22.57    oes\n",
       "5658                   51          2762.09             34.86    oes\n",
       "6285                   52          5775.24              33.5    oes\n",
       "6869                   53           2110.6              22.4    oes\n",
       "7564                   54          8739.11             38.32    oes\n",
       "8600                   55          2302.59             39.35    oes\n",
       "9566                   56          9070.14             18.26    oes\n",
       "10724                  61          12982.9             26.03    oes\n",
       "11806                  62          19257.9             25.13    oes\n",
       "12733                  71           2322.4             17.43    oes\n",
       "13488                  72          13338.9             12.14    oes\n",
       "14057                  81           4078.8             19.41    oes\n",
       "15046                  92          9589.35             28.26    oes"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "oes_sector_metrics = oes[\n",
    "    oes[\"OCC_TITLE\"] == \"Industry Total\"\n",
    "].rename(columns={\n",
    "    \"TOT_EMP\": \"total_employment\",\n",
    "    \"H_MEAN\": \"avg_hrly_earnings\",\n",
    "}).assign(naics_sector_rollup=lambda x: x[\"NAICS\"].apply(lambda y: y.split(\"-\")[0]))[[\n",
    "    \"naics_sector_rollup\",\n",
    "    \"total_employment\",\n",
    "    \"avg_hrly_earnings\"\n",
    "]]\\\n",
    "    .assign(total_employment=lambda x: x[\"total_employment\"] / 1000)\\\n",
    "    .assign(naics_sector_rollup=lambda x: x[\"naics_sector_rollup\"].replace(\"99\", \"92\"))\n",
    "oes_sector_metrics[\"source\"] = \"oes\"\n",
    "oes_sector_metrics"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Combine all metrics, in order of preference"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>naics_sector_rollup</th>\n",
       "      <th>priority</th>\n",
       "      <th>source</th>\n",
       "      <th>variable</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>21</td>\n",
       "      <td>1</td>\n",
       "      <td>ces</td>\n",
       "      <td>avg_hrly_earnings</td>\n",
       "      <td>32.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>285</th>\n",
       "      <td>92</td>\n",
       "      <td>1</td>\n",
       "      <td>ces</td>\n",
       "      <td>total_employment</td>\n",
       "      <td>5101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>284</th>\n",
       "      <td>91</td>\n",
       "      <td>1</td>\n",
       "      <td>ces</td>\n",
       "      <td>total_employment</td>\n",
       "      <td>2811</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>283</th>\n",
       "      <td>72</td>\n",
       "      <td>1</td>\n",
       "      <td>ces</td>\n",
       "      <td>total_employment</td>\n",
       "      <td>13449.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>282</th>\n",
       "      <td>71</td>\n",
       "      <td>1</td>\n",
       "      <td>ces</td>\n",
       "      <td>total_employment</td>\n",
       "      <td>2235.2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    naics_sector_rollup  priority source           variable    value\n",
       "0                    21         1    ces  avg_hrly_earnings    32.95\n",
       "285                  92         1    ces   total_employment     5101\n",
       "284                  91         1    ces   total_employment     2811\n",
       "283                  72         1    ces   total_employment  13449.2\n",
       "282                  71         1    ces   total_employment   2235.2"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sector_metrics_tidy = pd.concat([\n",
    "    ces_sector_metrics.assign(priority=1),\n",
    "    ces_sector_metrics_manual.assign(priority=2),\n",
    "    oes_sector_metrics.assign(priority=3),\n",
    "    ces_supersector_metrics.assign(priority=4),\n",
    "]).pipe(pd.melt,\n",
    "        id_vars=[\"naics_sector_rollup\", \"priority\", \"source\"]\n",
    "    ).sort_values(\"priority\")\\\n",
    "    .pipe(lambda x: x[x[\"variable\"].isin([ \"avg_hrly_earnings\", \"total_employment\", \"women_percentage\" ])])\\\n",
    "    .dropna(subset=[\"value\"])\n",
    "\n",
    "sector_metrics_tidy.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>naics_sector_rollup</th>\n",
       "      <th>variable</th>\n",
       "      <th>priority</th>\n",
       "      <th>source</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11</td>\n",
       "      <td>avg_hrly_earnings</td>\n",
       "      <td>3</td>\n",
       "      <td>oes</td>\n",
       "      <td>14.330</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11</td>\n",
       "      <td>total_employment</td>\n",
       "      <td>3</td>\n",
       "      <td>oes</td>\n",
       "      <td>416.600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11</td>\n",
       "      <td>women_percentage</td>\n",
       "      <td>2</td>\n",
       "      <td>cps</td>\n",
       "      <td>0.224</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>21</td>\n",
       "      <td>avg_hrly_earnings</td>\n",
       "      <td>1</td>\n",
       "      <td>ces</td>\n",
       "      <td>32.950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>21</td>\n",
       "      <td>total_employment</td>\n",
       "      <td>1</td>\n",
       "      <td>ces</td>\n",
       "      <td>610.200</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  naics_sector_rollup           variable  priority source    value\n",
       "0                  11  avg_hrly_earnings         3    oes   14.330\n",
       "1                  11   total_employment         3    oes  416.600\n",
       "2                  11   women_percentage         2    cps    0.224\n",
       "3                  21  avg_hrly_earnings         1    ces   32.950\n",
       "4                  21   total_employment         1    ces  610.200"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sector_metrics_tidy_unique = sector_metrics_tidy.groupby([\n",
    "    \"naics_sector_rollup\",\n",
    "    \"variable\"\n",
    "]).first().reset_index()\n",
    "sector_metrics_tidy_unique.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here are the data sources of each variable we're ultimately using:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>variable</th>\n",
       "      <th>avg_hrly_earnings</th>\n",
       "      <th>total_employment</th>\n",
       "      <th>women_percentage</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>source</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>ces</th>\n",
       "      <td>9</td>\n",
       "      <td>15</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ces_supersector</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cps</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>oes</th>\n",
       "      <td>11</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "variable         avg_hrly_earnings  total_employment  women_percentage\n",
       "source                                                                \n",
       "ces                              9                15                13\n",
       "ces_supersector                  0                 0                 8\n",
       "cps                              0                 0                 1\n",
       "oes                             11                 7                 0"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sector_metrics_tidy_unique.groupby([\n",
    "    \"source\",\n",
    "    \"variable\"\n",
    "]).size().unstack().fillna(0).astype(int)\\"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "sector_metrics_wide = sector_metrics_tidy_unique.set_index([\n",
    "    \"naics_sector_rollup\",\n",
    "    \"variable\"\n",
    "])[\"value\"].unstack().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>naics_sector</th>\n",
       "      <th>naics_sector_rollup</th>\n",
       "      <th>naics_supersector</th>\n",
       "      <th>naics_sector_name</th>\n",
       "      <th>avg_hrly_earnings</th>\n",
       "      <th>total_employment</th>\n",
       "      <th>women_percentage</th>\n",
       "      <th>source</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Agriculture, Forestry, Fishing and Hunting</td>\n",
       "      <td>14.33</td>\n",
       "      <td>416.60</td>\n",
       "      <td>0.224000</td>\n",
       "      <td>oes,cps</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>21</td>\n",
       "      <td>21</td>\n",
       "      <td>10</td>\n",
       "      <td>Mining, Quarrying, and Oil and Gas Extraction</td>\n",
       "      <td>32.95</td>\n",
       "      <td>610.20</td>\n",
       "      <td>0.151754</td>\n",
       "      <td>ces</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>22</td>\n",
       "      <td>22</td>\n",
       "      <td>40</td>\n",
       "      <td>Utilities</td>\n",
       "      <td>38.35</td>\n",
       "      <td>555.90</td>\n",
       "      <td>0.232776</td>\n",
       "      <td>ces</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>23</td>\n",
       "      <td>23</td>\n",
       "      <td>20</td>\n",
       "      <td>Construction</td>\n",
       "      <td>25.30</td>\n",
       "      <td>6687.38</td>\n",
       "      <td>0.125019</td>\n",
       "      <td>oes,ces_supersector</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>31</td>\n",
       "      <td>31</td>\n",
       "      <td>30</td>\n",
       "      <td>Manufacturing</td>\n",
       "      <td>24.39</td>\n",
       "      <td>12337.52</td>\n",
       "      <td>0.274534</td>\n",
       "      <td>oes,ces_supersector</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>32</td>\n",
       "      <td>31</td>\n",
       "      <td>30</td>\n",
       "      <td>Manufacturing</td>\n",
       "      <td>24.39</td>\n",
       "      <td>12337.52</td>\n",
       "      <td>0.274534</td>\n",
       "      <td>oes,ces_supersector</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>33</td>\n",
       "      <td>31</td>\n",
       "      <td>30</td>\n",
       "      <td>Manufacturing</td>\n",
       "      <td>24.39</td>\n",
       "      <td>12337.52</td>\n",
       "      <td>0.274534</td>\n",
       "      <td>oes,ces_supersector</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>42</td>\n",
       "      <td>42</td>\n",
       "      <td>40</td>\n",
       "      <td>Wholesale Trade</td>\n",
       "      <td>29.73</td>\n",
       "      <td>5877.50</td>\n",
       "      <td>0.296180</td>\n",
       "      <td>ces</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>44</td>\n",
       "      <td>44</td>\n",
       "      <td>40</td>\n",
       "      <td>Retail Trade</td>\n",
       "      <td>15.44</td>\n",
       "      <td>15982.52</td>\n",
       "      <td>0.406632</td>\n",
       "      <td>oes,ces_supersector</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>45</td>\n",
       "      <td>44</td>\n",
       "      <td>40</td>\n",
       "      <td>Retail Trade</td>\n",
       "      <td>15.44</td>\n",
       "      <td>15982.52</td>\n",
       "      <td>0.406632</td>\n",
       "      <td>oes,ces_supersector</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>48</td>\n",
       "      <td>48</td>\n",
       "      <td>40</td>\n",
       "      <td>Transportation and Warehousing</td>\n",
       "      <td>22.57</td>\n",
       "      <td>5606.18</td>\n",
       "      <td>0.406632</td>\n",
       "      <td>oes,ces_supersector</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>49</td>\n",
       "      <td>48</td>\n",
       "      <td>40</td>\n",
       "      <td>Transportation and Warehousing</td>\n",
       "      <td>22.57</td>\n",
       "      <td>5606.18</td>\n",
       "      <td>0.406632</td>\n",
       "      <td>oes,ces_supersector</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>51</td>\n",
       "      <td>51</td>\n",
       "      <td>50</td>\n",
       "      <td>Information</td>\n",
       "      <td>34.86</td>\n",
       "      <td>2762.09</td>\n",
       "      <td>0.400215</td>\n",
       "      <td>oes,ces_supersector</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>52</td>\n",
       "      <td>52</td>\n",
       "      <td>55</td>\n",
       "      <td>Finance and Insurance</td>\n",
       "      <td>33.50</td>\n",
       "      <td>6167.00</td>\n",
       "      <td>0.568236</td>\n",
       "      <td>oes,ces,ces_supersector</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>53</td>\n",
       "      <td>53</td>\n",
       "      <td>55</td>\n",
       "      <td>Real Estate and Rental and Leasing</td>\n",
       "      <td>22.40</td>\n",
       "      <td>2156.50</td>\n",
       "      <td>0.568236</td>\n",
       "      <td>oes,ces,ces_supersector</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>54</td>\n",
       "      <td>54</td>\n",
       "      <td>60</td>\n",
       "      <td>Professional, Scientific, and Technical Services</td>\n",
       "      <td>39.77</td>\n",
       "      <td>8956.50</td>\n",
       "      <td>0.465238</td>\n",
       "      <td>ces</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>55</td>\n",
       "      <td>55</td>\n",
       "      <td>60</td>\n",
       "      <td>Management of Companies and Enterprises</td>\n",
       "      <td>38.41</td>\n",
       "      <td>2246.50</td>\n",
       "      <td>0.482840</td>\n",
       "      <td>ces</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>56</td>\n",
       "      <td>56</td>\n",
       "      <td>60</td>\n",
       "      <td>Administrative and Support and Waste Managemen...</td>\n",
       "      <td>19.57</td>\n",
       "      <td>9076.10</td>\n",
       "      <td>0.425634</td>\n",
       "      <td>ces</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>61</td>\n",
       "      <td>61</td>\n",
       "      <td>65</td>\n",
       "      <td>Educational Services</td>\n",
       "      <td>26.03</td>\n",
       "      <td>3587.10</td>\n",
       "      <td>0.613699</td>\n",
       "      <td>oes,ces</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>62</td>\n",
       "      <td>62</td>\n",
       "      <td>65</td>\n",
       "      <td>Health Care and Social Assistance</td>\n",
       "      <td>26.11</td>\n",
       "      <td>19157.90</td>\n",
       "      <td>0.800281</td>\n",
       "      <td>ces</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>71</td>\n",
       "      <td>71</td>\n",
       "      <td>70</td>\n",
       "      <td>Arts, Entertainment, and Recreation</td>\n",
       "      <td>20.91</td>\n",
       "      <td>2235.20</td>\n",
       "      <td>0.471412</td>\n",
       "      <td>ces</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>72</td>\n",
       "      <td>72</td>\n",
       "      <td>70</td>\n",
       "      <td>Accommodation and Food Services</td>\n",
       "      <td>14.10</td>\n",
       "      <td>13449.20</td>\n",
       "      <td>0.527273</td>\n",
       "      <td>ces</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>81</td>\n",
       "      <td>81</td>\n",
       "      <td>80</td>\n",
       "      <td>Other Services (except Public Administration)</td>\n",
       "      <td>19.41</td>\n",
       "      <td>4078.80</td>\n",
       "      <td>0.525578</td>\n",
       "      <td>oes,ces_supersector</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>92</td>\n",
       "      <td>92</td>\n",
       "      <td>90</td>\n",
       "      <td>Public Administration</td>\n",
       "      <td>28.26</td>\n",
       "      <td>5101.00</td>\n",
       "      <td>0.537346</td>\n",
       "      <td>oes,ces</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    naics_sector naics_sector_rollup naics_supersector  \\\n",
       "0             11                  11               NaN   \n",
       "1             21                  21                10   \n",
       "2             22                  22                40   \n",
       "3             23                  23                20   \n",
       "4             31                  31                30   \n",
       "5             32                  31                30   \n",
       "6             33                  31                30   \n",
       "7             42                  42                40   \n",
       "8             44                  44                40   \n",
       "9             45                  44                40   \n",
       "10            48                  48                40   \n",
       "11            49                  48                40   \n",
       "12            51                  51                50   \n",
       "13            52                  52                55   \n",
       "14            53                  53                55   \n",
       "15            54                  54                60   \n",
       "16            55                  55                60   \n",
       "17            56                  56                60   \n",
       "18            61                  61                65   \n",
       "19            62                  62                65   \n",
       "20            71                  71                70   \n",
       "21            72                  72                70   \n",
       "22            81                  81                80   \n",
       "23            92                  92                90   \n",
       "\n",
       "                                    naics_sector_name  avg_hrly_earnings  \\\n",
       "0          Agriculture, Forestry, Fishing and Hunting              14.33   \n",
       "1       Mining, Quarrying, and Oil and Gas Extraction              32.95   \n",
       "2                                           Utilities              38.35   \n",
       "3                                        Construction              25.30   \n",
       "4                                       Manufacturing              24.39   \n",
       "5                                       Manufacturing              24.39   \n",
       "6                                       Manufacturing              24.39   \n",
       "7                                     Wholesale Trade              29.73   \n",
       "8                                        Retail Trade              15.44   \n",
       "9                                        Retail Trade              15.44   \n",
       "10                     Transportation and Warehousing              22.57   \n",
       "11                     Transportation and Warehousing              22.57   \n",
       "12                                        Information              34.86   \n",
       "13                              Finance and Insurance              33.50   \n",
       "14                 Real Estate and Rental and Leasing              22.40   \n",
       "15   Professional, Scientific, and Technical Services              39.77   \n",
       "16            Management of Companies and Enterprises              38.41   \n",
       "17  Administrative and Support and Waste Managemen...              19.57   \n",
       "18                               Educational Services              26.03   \n",
       "19                  Health Care and Social Assistance              26.11   \n",
       "20                Arts, Entertainment, and Recreation              20.91   \n",
       "21                    Accommodation and Food Services              14.10   \n",
       "22      Other Services (except Public Administration)              19.41   \n",
       "23                              Public Administration              28.26   \n",
       "\n",
       "    total_employment  women_percentage                   source  \n",
       "0             416.60          0.224000                  oes,cps  \n",
       "1             610.20          0.151754                      ces  \n",
       "2             555.90          0.232776                      ces  \n",
       "3            6687.38          0.125019      oes,ces_supersector  \n",
       "4           12337.52          0.274534      oes,ces_supersector  \n",
       "5           12337.52          0.274534      oes,ces_supersector  \n",
       "6           12337.52          0.274534      oes,ces_supersector  \n",
       "7            5877.50          0.296180                      ces  \n",
       "8           15982.52          0.406632      oes,ces_supersector  \n",
       "9           15982.52          0.406632      oes,ces_supersector  \n",
       "10           5606.18          0.406632      oes,ces_supersector  \n",
       "11           5606.18          0.406632      oes,ces_supersector  \n",
       "12           2762.09          0.400215      oes,ces_supersector  \n",
       "13           6167.00          0.568236  oes,ces,ces_supersector  \n",
       "14           2156.50          0.568236  oes,ces,ces_supersector  \n",
       "15           8956.50          0.465238                      ces  \n",
       "16           2246.50          0.482840                      ces  \n",
       "17           9076.10          0.425634                      ces  \n",
       "18           3587.10          0.613699                  oes,ces  \n",
       "19          19157.90          0.800281                      ces  \n",
       "20           2235.20          0.471412                      ces  \n",
       "21          13449.20          0.527273                      ces  \n",
       "22           4078.80          0.525578      oes,ces_supersector  \n",
       "23           5101.00          0.537346                  oes,ces  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sector_metrics = pd.merge(\n",
    "    sectors,\n",
    "    sector_metrics_wide[[\n",
    "        \"naics_sector_rollup\",\n",
    "        \"avg_hrly_earnings\",\n",
    "        \"total_employment\",\n",
    "        \"women_percentage\"\n",
    "    ]],\n",
    "    how=\"left\",\n",
    "    on=[\"naics_sector_rollup\"]\n",
    ").pipe(pd.merge,\n",
    "    sector_metrics_tidy_unique.groupby(\"naics_sector_rollup\")[\"source\"].unique()\\\n",
    "        .apply(\",\".join).reset_index(),\n",
    "    on=\"naics_sector_rollup\",\n",
    "    how=\"left\"\n",
    ")\n",
    "\n",
    "sector_metrics"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Save combined data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "sector_metrics.to_csv('../output/bls_sector_metrics.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merge in custom information for graphics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>industry</th>\n",
       "      <th>naics_code</th>\n",
       "      <th>grouping</th>\n",
       "      <th>industry_class</th>\n",
       "      <th>grouping_class</th>\n",
       "      <th>color</th>\n",
       "      <th>focus</th>\n",
       "      <th>index_num</th>\n",
       "      <th>info_text</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Accommodation and Food Services</td>\n",
       "      <td>72</td>\n",
       "      <td>Service and sales-related jobs</td>\n",
       "      <td>accommodationandfoodservices</td>\n",
       "      <td>serviceandsalesrelatedjobs</td>\n",
       "      <td>#af2469</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>By far, the most claims were filed by service-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Retail Trade</td>\n",
       "      <td>44</td>\n",
       "      <td>Service and sales-related jobs</td>\n",
       "      <td>retailtrade</td>\n",
       "      <td>serviceandsalesrelatedjobs</td>\n",
       "      <td>#f43192</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>By far, the most claims were filed by service-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Other Services (except Public Administration)</td>\n",
       "      <td>81</td>\n",
       "      <td>Service and sales-related jobs</td>\n",
       "      <td>otherservices</td>\n",
       "      <td>serviceandsalesrelatedjobs</td>\n",
       "      <td>#efb4d1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>By far, the most claims were filed by service-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Wholesale Trade</td>\n",
       "      <td>42</td>\n",
       "      <td>Service and sales-related jobs</td>\n",
       "      <td>wholesaletrade</td>\n",
       "      <td>serviceandsalesrelatedjobs</td>\n",
       "      <td>#efdce7</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>By far, the most claims were filed by service-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Manufacturing</td>\n",
       "      <td>31</td>\n",
       "      <td>Manual Labor</td>\n",
       "      <td>manufacturing</td>\n",
       "      <td>manuallabor</td>\n",
       "      <td>#096c5f</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>Manual labor jobs like construction, warehousi...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        industry naics_code  \\\n",
       "0                Accommodation and Food Services         72   \n",
       "1                                   Retail Trade         44   \n",
       "2  Other Services (except Public Administration)         81   \n",
       "3                                Wholesale Trade         42   \n",
       "4                                  Manufacturing         31   \n",
       "\n",
       "                         grouping                industry_class  \\\n",
       "0  Service and sales-related jobs  accommodationandfoodservices   \n",
       "1  Service and sales-related jobs                   retailtrade   \n",
       "2  Service and sales-related jobs                 otherservices   \n",
       "3  Service and sales-related jobs                wholesaletrade   \n",
       "4                    Manual Labor                 manufacturing   \n",
       "\n",
       "               grouping_class    color focus index_num  \\\n",
       "0  serviceandsalesrelatedjobs  #af2469     1         1   \n",
       "1  serviceandsalesrelatedjobs  #f43192     1         2   \n",
       "2  serviceandsalesrelatedjobs  #efb4d1     1         3   \n",
       "3  serviceandsalesrelatedjobs  #efdce7     1         4   \n",
       "4                 manuallabor  #096c5f     2         1   \n",
       "\n",
       "                                           info_text  \n",
       "0  By far, the most claims were filed by service-...  \n",
       "1  By far, the most claims were filed by service-...  \n",
       "2  By far, the most claims were filed by service-...  \n",
       "3  By far, the most claims were filed by service-...  \n",
       "4  Manual labor jobs like construction, warehousi...  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "graphics_info = pd.read_csv(\n",
    "    '../data/graphics_info.csv',\n",
    "    dtype={\n",
    "        \"naics_code\": str,\n",
    "        \"focus\" :str,\n",
    "        \"index_num\":str\n",
    "    }\n",
    ")\n",
    "\n",
    "graphics_info.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "21"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(graphics_info)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The next file includes information necessary for the graphic:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>industry</th>\n",
       "      <th>naics_code</th>\n",
       "      <th>grouping</th>\n",
       "      <th>industry_class</th>\n",
       "      <th>grouping_class</th>\n",
       "      <th>color</th>\n",
       "      <th>focus</th>\n",
       "      <th>index_num</th>\n",
       "      <th>info_text</th>\n",
       "      <th>naics_sector</th>\n",
       "      <th>naics_sector_rollup</th>\n",
       "      <th>naics_supersector</th>\n",
       "      <th>naics_sector_name</th>\n",
       "      <th>avg_hrly_earnings</th>\n",
       "      <th>total_employment</th>\n",
       "      <th>women_percentage</th>\n",
       "      <th>source</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Accommodation and Food Services</td>\n",
       "      <td>72</td>\n",
       "      <td>Service and sales-related jobs</td>\n",
       "      <td>accommodationandfoodservices</td>\n",
       "      <td>serviceandsalesrelatedjobs</td>\n",
       "      <td>#af2469</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>By far, the most claims were filed by service-...</td>\n",
       "      <td>72.0</td>\n",
       "      <td>72</td>\n",
       "      <td>70</td>\n",
       "      <td>Accommodation and Food Services</td>\n",
       "      <td>14.10</td>\n",
       "      <td>13449.20</td>\n",
       "      <td>0.527273</td>\n",
       "      <td>ces</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Retail Trade</td>\n",
       "      <td>44</td>\n",
       "      <td>Service and sales-related jobs</td>\n",
       "      <td>retailtrade</td>\n",
       "      <td>serviceandsalesrelatedjobs</td>\n",
       "      <td>#f43192</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>By far, the most claims were filed by service-...</td>\n",
       "      <td>44.0</td>\n",
       "      <td>44</td>\n",
       "      <td>40</td>\n",
       "      <td>Retail Trade</td>\n",
       "      <td>15.44</td>\n",
       "      <td>15982.52</td>\n",
       "      <td>0.406632</td>\n",
       "      <td>oes,ces_supersector</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Other Services (except Public Administration)</td>\n",
       "      <td>81</td>\n",
       "      <td>Service and sales-related jobs</td>\n",
       "      <td>otherservices</td>\n",
       "      <td>serviceandsalesrelatedjobs</td>\n",
       "      <td>#efb4d1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>By far, the most claims were filed by service-...</td>\n",
       "      <td>81.0</td>\n",
       "      <td>81</td>\n",
       "      <td>80</td>\n",
       "      <td>Other Services (except Public Administration)</td>\n",
       "      <td>19.41</td>\n",
       "      <td>4078.80</td>\n",
       "      <td>0.525578</td>\n",
       "      <td>oes,ces_supersector</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Wholesale Trade</td>\n",
       "      <td>42</td>\n",
       "      <td>Service and sales-related jobs</td>\n",
       "      <td>wholesaletrade</td>\n",
       "      <td>serviceandsalesrelatedjobs</td>\n",
       "      <td>#efdce7</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>By far, the most claims were filed by service-...</td>\n",
       "      <td>42.0</td>\n",
       "      <td>42</td>\n",
       "      <td>40</td>\n",
       "      <td>Wholesale Trade</td>\n",
       "      <td>29.73</td>\n",
       "      <td>5877.50</td>\n",
       "      <td>0.296180</td>\n",
       "      <td>ces</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Manufacturing</td>\n",
       "      <td>31</td>\n",
       "      <td>Manual Labor</td>\n",
       "      <td>manufacturing</td>\n",
       "      <td>manuallabor</td>\n",
       "      <td>#096c5f</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>Manual labor jobs like construction, warehousi...</td>\n",
       "      <td>31.0</td>\n",
       "      <td>31</td>\n",
       "      <td>30</td>\n",
       "      <td>Manufacturing</td>\n",
       "      <td>24.39</td>\n",
       "      <td>12337.52</td>\n",
       "      <td>0.274534</td>\n",
       "      <td>oes,ces_supersector</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        industry naics_code  \\\n",
       "0                Accommodation and Food Services         72   \n",
       "1                                   Retail Trade         44   \n",
       "2  Other Services (except Public Administration)         81   \n",
       "3                                Wholesale Trade         42   \n",
       "4                                  Manufacturing         31   \n",
       "\n",
       "                         grouping                industry_class  \\\n",
       "0  Service and sales-related jobs  accommodationandfoodservices   \n",
       "1  Service and sales-related jobs                   retailtrade   \n",
       "2  Service and sales-related jobs                 otherservices   \n",
       "3  Service and sales-related jobs                wholesaletrade   \n",
       "4                    Manual Labor                 manufacturing   \n",
       "\n",
       "               grouping_class    color focus index_num  \\\n",
       "0  serviceandsalesrelatedjobs  #af2469     1         1   \n",
       "1  serviceandsalesrelatedjobs  #f43192     1         2   \n",
       "2  serviceandsalesrelatedjobs  #efb4d1     1         3   \n",
       "3  serviceandsalesrelatedjobs  #efdce7     1         4   \n",
       "4                 manuallabor  #096c5f     2         1   \n",
       "\n",
       "                                           info_text  naics_sector  \\\n",
       "0  By far, the most claims were filed by service-...          72.0   \n",
       "1  By far, the most claims were filed by service-...          44.0   \n",
       "2  By far, the most claims were filed by service-...          81.0   \n",
       "3  By far, the most claims were filed by service-...          42.0   \n",
       "4  Manual labor jobs like construction, warehousi...          31.0   \n",
       "\n",
       "  naics_sector_rollup naics_supersector  \\\n",
       "0                  72                70   \n",
       "1                  44                40   \n",
       "2                  81                80   \n",
       "3                  42                40   \n",
       "4                  31                30   \n",
       "\n",
       "                               naics_sector_name  avg_hrly_earnings  \\\n",
       "0                Accommodation and Food Services              14.10   \n",
       "1                                   Retail Trade              15.44   \n",
       "2  Other Services (except Public Administration)              19.41   \n",
       "3                                Wholesale Trade              29.73   \n",
       "4                                  Manufacturing              24.39   \n",
       "\n",
       "   total_employment  women_percentage               source  \n",
       "0          13449.20          0.527273                  ces  \n",
       "1          15982.52          0.406632  oes,ces_supersector  \n",
       "2           4078.80          0.525578  oes,ces_supersector  \n",
       "3           5877.50          0.296180                  ces  \n",
       "4          12337.52          0.274534  oes,ces_supersector  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "graphics_info_bls = pd.merge(\n",
    "    graphics_info,\n",
    "    sector_metrics.drop_duplicates(subset=[\"naics_sector_name\"]),\n",
    "    left_on=\"industry\",\n",
    "    right_on=\"naics_sector_name\",\n",
    "    how=\"left\"\n",
    ")\n",
    "\n",
    "graphics_info_bls.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "21"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(graphics_info_bls)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "graphics_info_bls.to_csv(\n",
    "    \"../output/graphics_info_bls.csv\",\n",
    "    index=False\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "---\n",
    "\n",
    "---"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.4.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}