onet_census_link.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Linking Census Occupation Codes with O\\*NET SOC Data\n",
    "\n",
    "This notebook links occupation descriptions and task content descriptions from the Occupation Information Network (O\\*NET) to Census occupation codes and outputs to a `.json` format file. O\\*NET data are organized by the Standard Occupational Classification (SOC) system, while Census uses its own coding regime based on the SOC. Many Census codes are aggregates of one or more SOC codes. This script uses the Census-SOC crosswalk to aggregate O\\*NET data to each Census code.\n",
    "\n",
    "O\\*NET data include a detailed description of each occupation as well as descriptions of the specific tasks which job analysts have determined are required to function within each occupation. In addition, they include sample job titles that fit into each occupation. If we assume that respondents are likely to write in their occupational title or describe what they do (tasks) for their occupation, then these data represent a collection of common occupation-specific terms that could be used by a NLP algorithm to train a model capable of predicting the occupation code for a given write-in.\n",
    "\n",
    "The output of this workbook is a `.json` file of Census occupation codes, a list of matched SOC codes from O\\*NET, a text string containing descriptions from each O\\*NET occupation input, a text string containing a description of all tasks associated with the Census code, and a text field comprising a comma-separated list of sample job titles. I chose to keep these fields separate so analysts could choose how or when to implement them indepdently, if at all. The JSON file is easily accessible using a standard text editor and it can be read into a `pandas` dataframe directly for use with the `nltk` in Python (at the end of this notebook I demonstrate how to read JSON into `pandas`).\n",
    "\n",
    "This file uses two inputs: (1) a Census-SOC crosswalk file obtained from the U.S. Census Bureau Web site and (2) a custom dictionary file taken from the O\\*NET API. Creation of the latter file is undertaken by the file `soc_work.py`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import modules\n",
    "import json\n",
    "import re\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# load json files\n",
    "with open( 'soc_census_xwalk.json' , 'r' ) as f:\n",
    "    xwalk = json.load( f )\n",
    "\n",
    "with open( 'onet_occdata.json' , 'r' ) as f:\n",
    "    onet = json.load( f )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `json` format is essentially a list of dictionaries and each dictionary is a set of key-value pairs. If the data were organized in a tradtional rectangular data set, each dictionary (list element) would be a record, the keys would indicate a column title, and the value would be the datum for each column. \n",
    "\n",
    "The `xwalk` dictionary follows this format. However, the O\\*NET dictionary varies slightly. The set of sample occupations (indicated by the key `sample_of_reported_job_titles`) is organized as a list. Further, `tasks` are themselves a list of dictionary objects. In this regard, the `onet` JSON object may not be in \"true\" JSON format. The ability of JSON objects to store different types of information without an explicit schema is advantageous. \n",
    "\n",
    "For potentially complicated data structures, it is important to understand the hierarchical structure before it can be leveraged effectively. Here, we explore the keys associated with each JSON object and print off its first record."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "xwalk keys:\n",
      " dict_keys(['occ_title', 'cenocc', 'soc']) \n",
      "\n",
      "\n",
      "first record of xwalk:\n",
      " {'occ_title': 'Chief executives', 'cenocc': '0010', 'soc': '11-1011'} \n",
      "\n",
      "\n",
      "number of census occupations:\n",
      " 507\n"
     ]
    }
   ],
   "source": [
    "# explore 'xwalk' object\n",
    "print( 'xwalk keys:\\n' , xwalk[ 0 ].keys() , '\\n\\n' )\n",
    "\n",
    "print( 'first record of xwalk:\\n' , xwalk[ 0 ] , '\\n\\n' )\n",
    "\n",
    "print( 'number of census occupations:\\n' , len( xwalk ) )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "onet keys:\n",
      " dict_keys(['code', 'title', 'description', 'sample_of_reported_job_titles', 'tasks']) \n",
      "\n",
      "\n",
      "number of onet occupations:\n",
      " 1016 \n",
      "\n",
      "first record of onet:\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "{'code': '13-2011.00',\n",
       " 'title': 'Accountants and Auditors',\n",
       " 'description': 'Examine, analyze, and interpret accounting records to prepare financial statements, give advice, or audit and evaluate statements prepared by others. Install or advise on systems of recording costs or other financial and budgetary data.',\n",
       " 'sample_of_reported_job_titles': ['Accountant',\n",
       "  'Accounting Officer',\n",
       "  'Audit Partner',\n",
       "  'Auditor',\n",
       "  'Certified Public Accountant (CPA)',\n",
       "  'Cost Accountant',\n",
       "  'Financial Auditor',\n",
       "  'General Accountant',\n",
       "  'Internal Auditor',\n",
       "  'Revenue Tax Specialist'],\n",
       " 'tasks': [{'id': 21505,\n",
       "   'name': 'Prepare detailed reports on audit findings.'},\n",
       "  {'id': 21506,\n",
       "   'name': 'Report to management about asset utilization and audit results, and recommend changes in operations and financial activities.'},\n",
       "  {'id': 21507,\n",
       "   'name': 'Collect and analyze data to detect deficient controls, duplicated effort, extravagance, fraud, or non-compliance with laws, regulations, and management policies.'},\n",
       "  {'id': 21508,\n",
       "   'name': 'Inspect account books and accounting systems for efficiency, effectiveness, and use of accepted accounting procedures to record transactions.'},\n",
       "  {'id': 21509,\n",
       "   'name': 'Supervise auditing of establishments, and determine scope of investigation required.'}]}"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# explore 'onet' object\n",
    "print( 'onet keys:\\n' , onet[ 0 ].keys() , '\\n\\n' )\n",
    "\n",
    "print( 'number of onet occupations:\\n' , len( onet ) , '\\n' )\n",
    "\n",
    "print( 'first record of onet:' )\n",
    "onet[ 0 ]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The above illustration reveals several important details. First, with 507 Census occupations and 1,016 O\\*NET occupations, some aggregation will be necessary. Second, O\\*NET occupation codes include eight numeric digits (two following the decimal) while those referenced in the Census codes comprise only six. It is also worth noting that some Census occupations may have multiple SOC codes assigned to them (they will be in a space-delimited list; see the full file for details).\n",
    "\n",
    "The SOC is a hierarchical system where the number of non-zero digits indicate nesting within a larger grouping. There are four levels of nesting: major, minor, broad, and detailed. The first two _non-zero_ digits indicate the \"major\" occupation group. For example, the 2018 SOC code 11-0000 refers to the \"Management Occupations\" major group. This major group nests all \"minor\" groups, which are indicated at the three-digit level (11-1000, 11-2000, 11-3000, and 11-9000). The broad group is characterized by the fifth digit being non-zero and the sixth digit being zero. For example, \"Top Executives\" are the broad group nested within the minor group 11-1000 and comprises occupation codes 11-1010, 11-1020, and 11-1030. Finally, the broad group nests \"detailed\" occupations, which have non-elements at the sixth digit. The broad group for \"Public Relations and Fundraising Managers\" (SOC 11-2030) nests detailed occupations \"Public Realations managers\" (SOC 11-2031) and \"Fundraising Managers\" (SOC 11-2033). More information about the SOC is available from the [Bureau of Labor Statistics](https://www.bls.gov/soc). For our purposes, we use the 2018 SOC definitions, which will correspond to the ACS 2018 and 2019 data used in our capstone project.\n",
    "\n",
    "The eight-digit SOC codes used by O\\*NET will follow the same nesting structure and should pose no problems for matching to Census occupation codes. Most O\\*NET occupation codes have .00 as their seventh and eight digits. Any non-zero digit at this level of detail will be nested to the sixth digit SOC as described above, while values of .00 are equivalent to six-digit codes.\n",
    "\n",
    "Understanding the SOC hierarchy is critical for correctly matching O\\*NET data to Census codes. Many Census occupation codes will have a direct match to the SOC detailed occupations, such as \"Food Service Managers\" (Census code 0310 and SOC code 11-9051). Others refer to broad occupations and require aggregating information from detailed occupations in O\\*NET. For example, Census occupation code 0335 refers to \"Entertainment and Recreation Managers,\" which is the broad occupation group 11-9070 comprising detailed occupations 11-9071 and 11-9072.\n",
    "\n",
    "The next steps of this notebook use the SOC hierarchical structure to create a Census-code specific occupations \"documents\" by matching and, in some cases, aggregating information from O\\*NET descriptions of occupations, tasks, and sample occupations. To do this, I iterate over the set of Census occupation codes in `xwalk` and use the associated SOC code to select matching elements from O\\*NET. The information will be stored in a dictionary file that can be exported to JSON format for later use. O\\*NET descriptions, tasks, and job titles are stored in their own fields.\n",
    "\n",
    "Note that this is a first attempt at creating such a corpus. It may be decided upon later that omitting the sample job titles will be a good idea. Further, we may decided that adding sample occupations from the DTMF is desirable."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''\n",
    "this section outlines the process for linking onet dat to census occupation codes. the strategy is to\n",
    "loop through each census code in 'xwalk'. for each census code, i use a list comprehension to get the \n",
    "set of matching soc codes in 'onet'. i use a regular expression pattern ('repat') that is conditionally\n",
    "assigned based on the user-defined function 'socpat'. see the offical census crosswalk file for explicit \n",
    "occupation codes assigned into aggregate categories (i.e., those ending in one or more '0' or 'X'); this\n",
    "accounts for the unque regex assignment controlled for in 'socpat'. i define a class ('onetOutput') that\n",
    "constructs composites of descriptions, tasks, and sample job titles for all soc codes that serve as\n",
    "an input to the census code. finally, all results are stored in a json object (i.e., a list of\n",
    "dictionaries) with a field for each of these components. i also preserve the set of onet soc occupation\n",
    "codes that go into a given match for manual inspection.\n",
    "'''\n",
    "# define a class that takes a list of soc codes and returns descriptions, tasks, and job titles from 'onet'\n",
    "# NOTE: this class is not general; it takes a list object in the same format as 'onet'\n",
    "class onetOutput:\n",
    "    def __init__( self , soclist , onetlist ):\n",
    "        self.soclist = soclist\n",
    "        self.onetlist = onetlist\n",
    "    \n",
    "    # get description(s) for each element of soclist and output to a single string\n",
    "    def description( self ):\n",
    "        desclist = [ x[ 'description' ] for x in self.onetlist if x[ 'code' ] in self.soclist ]\n",
    "        if len( desclist ) == 1:\n",
    "            return( desclist[ 0 ] )\n",
    "        else:\n",
    "            return( ' '.join( desclist ) )\n",
    "    \n",
    "    # get tasks (if available, otherwise return empty string)\n",
    "    def tasks( self ):\n",
    "        tasklist = [ x[ 'tasks' ] for x in self.onetlist if x[ 'code' ] in self.soclist ]\n",
    "        if sum( [ len( x ) for x in tasklist ] ) == 0:\n",
    "            return( '' )\n",
    "        else:\n",
    "            task_desc = []\n",
    "            for i in tasklist:\n",
    "                for j in i:\n",
    "                    task_desc.append( j[ 'name' ] )\n",
    "            return( ' '.join( task_desc ) )\n",
    "    \n",
    "    # get list of sample job titles as a comma-separated string\n",
    "    def job_titles( self ):\n",
    "        try:\n",
    "            titles = [ x[ 'sample_of_reported_job_titles' ] for x in self.onetlist if x['code' ] in self.soclist ]\n",
    "            return( ', '.join( [ ', '.join( x ) for x in titles ] ) )\n",
    "            \n",
    "        except:\n",
    "            return( '' )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def socpat( soc_code ):\n",
    "    if soc_code[ -1 ] == '0':\n",
    "        numz = re.findall( '(?<=[1-9])0+$' , soc_code )\n",
    "        return( '{0}{1}\\.\\d{{2}}'.format( re.split( '{}$'.format( numz[ 0 ] ) , censoc )[ 0 ] , \n",
    "                                          '\\d{' + str( len( numz[ 0 ] ) ) + '}' ) )\n",
    "    elif soc_code[ -1 ] == 'X':\n",
    "        if soc_code == '13-20XX':\n",
    "            return( '{}\\d{{2}}(?<=54|99)\\.\\d{{2}}'.format( re.sub( '-' , '\\-' , soc_code[ 0:-2 ] ) ) )\n",
    "        elif soc_code == '15-124X':\n",
    "            return( '{}[23]\\.\\d{{2}}'.format( re.sub( '-' , '\\-' , soc_code[ 0:-1 ] ) ) )\n",
    "        elif soc_code == '15-20XX':\n",
    "            return( '{}\\d{{2}}(?<=51|99)\\.\\d{{2}}'.format( re.sub( '-' , '\\-' , soc_code[ 0:-2 ] ) ) )\n",
    "        elif soc_code == '17-301X':\n",
    "            return( '{}[239]\\.\\d{{2}}'.format( re.sub( '-' , '\\-' , soc_code[ 0:-1 ] ) ) )\n",
    "        elif soc_code == '17-302X':\n",
    "            return( '{}\\d(?<!3)\\.\\d{{2}}'.format( re.sub( '-' , '\\-' , soc_code[ 0:-1 ] ) ) )\n",
    "        elif soc_code == '19-204X':\n",
    "            return( '{}[23]\\.\\d{{2}}'.format( re.sub( '-' , '\\-' , soc_code[ 0:-1 ] ) ) )\n",
    "        elif soc_code == '19-303X':\n",
    "            return( '{}[29]\\.\\d{{2}}'.format( re.sub( '-' , '\\-' , soc_code[ 0:-1 ] ) ) )\n",
    "        elif soc_code == '19-40XX':\n",
    "            return( '{}\\d{{2}}(?<=40|51)\\.\\d{{2}}'.format( re.sub( '-' , '\\-' , soc_code[ 0:-2 ] ) ) )\n",
    "        elif soc_code == '21-109X':\n",
    "            return( '{}[149]\\.\\d{{2}}'.format( re.sub( '-' , '\\-' , soc_code[ 0:-1 ] ) ) )\n",
    "        elif soc_code == '25-30XX':\n",
    "            return( '{}\\d{{2}}(?<=11|21|31|99)\\.\\d{{2}}'.format( re.sub( '-' , '\\-' , soc_code[ 0:-2 ] ) ) )\n",
    "        elif soc_code == '25-90XX':\n",
    "            return( '{}\\d{{2}}(?<=21|31|99).\\d{{2}}'.format( re.sub( '-' , '\\-' , soc_code[ 0:-2 ] ) ) )\n",
    "        elif soc_code == '27-102X':\n",
    "            return( '{}[79]\\d\\.\\d{{2}}'.format( re.sub( '-' , '\\-' , soc_code[ 0:-1 ] ) ) )\n",
    "        elif soc_code == '29-12XX':\n",
    "            return( '{}[12](?!4)\\d\\.\\d{{2}}'.format( re.sub( '-' , '\\-' , soc_code[ 0:-2 ] ) ) )\n",
    "        elif soc_code == '29-203X':\n",
    "            return( '{}[36]\\.\\d{{2}}'.format( re.sub( '-' , '\\-' , soc_code[ 0:-1 ] ) ) )\n",
    "        elif soc_code == '29-205X':\n",
    "            return( '{}[17]\\.\\d{{2}}'.format( re.sub( '-' , '\\-' , soc_code[ 0:-1 ] ) ) )\n",
    "        elif soc_code == '31-113X':\n",
    "            return( '{}[23]\\.\\d{{2}}'.format( re.sub( '-' , '\\-' , soc_code[ 0:-1 ] ) ) )\n",
    "        elif soc_code == '31-909X':\n",
    "            return( '{}[39]\\.\\d{{2}}'.format( re.sub( '-' , '\\-' , soc_code[ 0:-1 ] ) ) )\n",
    "        elif soc_code == '33-909X':\n",
    "            return( '{}[29]\\.\\d{{2}}'.format( re.sub( '-' , '\\-' , soc_code[ 0:-1 ] ) ) )\n",
    "        elif soc_code == '37-201X':\n",
    "            return( '{}[19]\\.\\d{{2}}'.format( re.sub( '-' , '\\-' , soc_code[ 0:-1 ] ) ) )\n",
    "        elif soc_code == '37-301X':\n",
    "            return( '{}[29]\\.\\d{{2}}'.format( re.sub( '-' , '\\-' , soc_code[ 0:-1 ] ) ) )\n",
    "        elif soc_code == '39-30XX':\n",
    "            return( '{}\\d{{2}}(?<=21|90)\\.\\d{{2}}'.format( re.sub( '-' , '\\-' , soc_code[ 0:-2 ] ) ) )\n",
    "        elif soc_code == '39-40XX':\n",
    "            return( '{}\\d{{2}}(?<=11|12|21)\\.\\d{{2}}'.format( re.sub( '-' , '\\-' , soc_code[ 0:-2 ] ) ) )\n",
    "        elif soc_code == '39-509X':\n",
    "            return( '{}[13]\\.\\d{{2}}'.format( re.sub( '-' , '\\-' , soc_code[ 0:-1 ] ) ) )\n",
    "        elif soc_code == '47-50XX':\n",
    "            return( '{}[589]\\d(?<=1|9)\\.\\d{{2}}'.format( re.sub( '-' , '\\-' , soc_code[ 0:-2 ] ) ) )\n",
    "        elif soc_code == '49-209X':\n",
    "            return( '{}[45]\\.\\d{{2}}'.format( re.sub( '-' , '\\-' , soc_code[ 0:-1 ] ) ) )\n",
    "        elif soc_code == '49-904X':\n",
    "            return( '{}[15]\\.\\d{{2}}'.format( re.sub( '-' , '\\-' , soc_code[ 0:-1 ] ) ) )\n",
    "        elif soc_code == '49-909X':\n",
    "            return( '{}[79]\\.\\d{{2}}'.format( re.sub( '-' , '\\-' , soc_code[ 0:-1 ] ) ) )\n",
    "        elif soc_code == '51-20XX':\n",
    "            return( '{}[569]\\d(?<=1|2|9)\\.\\d{{2}}'.format( re.sub( '-' , '\\-' , soc_code[ 0:-2 ] ) ) )\n",
    "        elif soc_code == '51-403X':\n",
    "            return( '{}[245]\\.\\d{{2}}'.format( re.sub( '-' , '\\-' , soc_code[ 0:-1 ] ) ) )\n",
    "        elif soc_code == '51-4XXX':\n",
    "            return( '{}\\d{{3}}(?<=081|19[1-49])\\.\\d{{2}}'.format( re.sub( '-' , '\\-' , soc_code[ 0:-3 ] ) ) )\n",
    "        elif soc_code == '51-609X':\n",
    "            return( '{}[129]\\.\\d{{2}}'.format( re.sub( '-' , '\\-' , soc_code[ 0:-1 ] ) ) )\n",
    "        elif soc_code == '51-20XX':\n",
    "            return( '{}\\d{{2}}(?<=30|99)\\.\\d{{2}}'.format( re.sub( '-' , '\\-' , soc_code[ 0:-2 ] ) ) )\n",
    "        elif soc_code == '51-919X':\n",
    "            return( '{}[23]\\.\\d{{2}}'.format( re.sub( '-' , '\\-' , soc_code[ 0:-1 ] ) ) )\n",
    "        elif soc_code == '51-91XX':\n",
    "            return( '{}\\d{{2}}(?<=41|99)\\.\\d{{2}}'.format( re.sub( '-' , '\\-' , soc_code[ 0:-2 ] ) ) )\n",
    "        elif soc_code == '53-40XX':\n",
    "            return( '{}\\d{{2}}(?<=22|41|99)\\.\\d{{2}}'.format( re.sub( '-' , '\\-' , soc_code[ 0:-2 ] ) ) )\n",
    "        elif soc_code == '53-60XX':\n",
    "            return( '{}\\d{{2}}(?<=11|41|99)\\.\\d{{2}}'.format( re.sub( '-' , '\\-' , soc_code[ 0:-2 ] ) ) )\n",
    "        elif soc_code == '53-70XX':\n",
    "            return( '{}\\d{{2}}(?<=11|31|41)\\.\\d{{2}}'.format( re.sub( '-' , '\\-' , soc_code[ 0:-2 ] ) ) )\n",
    "        elif soc_code == '53-71XX':\n",
    "            return( '{}\\d{{2}}(?<=21|99)\\.\\d{{2}}'.format( re.sub( '-' , '\\-' , soc_code[ 0:-2 ] ) ) )\n",
    "        else:\n",
    "            raise ValueError( 'no corresponding SOC code(s) given for' , soc_code )\n",
    "    else:\n",
    "        return( re.sub( '-' , '\\-' , soc_code ) )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# initialize list to store results\n",
    "cenonet = []\n",
    "\n",
    "# loop over census occupation codes and get needed onet content\n",
    "for i in xwalk:\n",
    "    # extract crosswalk between census code and soc\n",
    "    # NOTE: split censoc on space to account for multiple SOC codes\n",
    "    cencode = i[ 'cenocc' ]\n",
    "    censoc = i[ 'soc' ]\n",
    "    \n",
    "    # use list comprehension to get set of all matching soc codes in onet\n",
    "    socmatch = [ x[ 'code' ] for x in onet if len( re.findall( socpat( censoc ) , x[ 'code' ] ) ) > 0 ]\n",
    "    \n",
    "    # get components for each element of 'socmatch', store as dictionary, and append to list\n",
    "    cendict = { 'cenocc' : str( cencode ) ,\n",
    "                'occtitle' : i[ 'occ_title' ] ,\n",
    "                'cen_soc' : censoc ,\n",
    "                'onet_soc' : socmatch ,\n",
    "                'description' : onetOutput( socmatch , onet ).description() ,\n",
    "                'tasks' : onetOutput( socmatch , onet ).tasks() ,\n",
    "                'job_titles' : onetOutput( socmatch , onet ).job_titles() }\n",
    "    \n",
    "    cenonet.append( cendict )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'cenocc': '0010',\n",
       "  'occtitle': 'Chief executives',\n",
       "  'cen_soc': '11-1011',\n",
       "  'onet_soc': ['11-1011.00', '11-1011.03'],\n",
       "  'description': 'Determine and formulate policies and provide overall direction of companies or private and public sector organizations within guidelines set up by a board of directors or similar governing body. Plan, direct, or coordinate operational activities at the highest level of management with the help of subordinate executives and staff managers. Communicate and coordinate with management, shareholders, customers, and employees to address sustainability issues. Enact or oversee a corporate sustainability strategy.',\n",
       "  'tasks': \"Direct or coordinate an organization's financial or budget activities to fund operations, maximize investments, or increase efficiency. Appoint department heads or managers and assign or delegate responsibilities to them. Analyze operations to evaluate performance of a company or its staff in meeting objectives or to determine areas of potential cost reduction, program improvement, or policy change. Direct, plan, or implement policies, objectives, or activities of organizations or businesses to ensure continuing operations, to maximize returns on investments, or to increase productivity. Prepare budgets for approval, including those for funding or implementation of programs. Develop or execute strategies to address issues such as energy use, resource conservation, recycling, pollution reduction, waste elimination, transportation, education, and building design. Supervise employees or volunteers working on sustainability projects. Research environmental sustainability issues, concerns, or stakeholder interests. Develop methodologies to assess the viability or success of sustainability initiatives. Monitor and evaluate effectiveness of sustainability programs.\",\n",
       "  'job_titles': 'Chief Diversity Officer (CDO), Chief Executive Officer (CEO), Chief Financial Officer (CFO), Chief Nursing Officer, Chief Operating Officer (COO), Executive Director, Executive Vice President (EVP), Operations Vice President, Corporate Sustainability Process Manager, Director of Sustainability, Director of Sustainability Programs, Director of Sustainable Design, Sustainability Director, Vice President, Corporate Social Responsibility and Sustainability (VP CSR and Sustainability)'},\n",
       " {'cenocc': '0020',\n",
       "  'occtitle': 'General and operations managers',\n",
       "  'cen_soc': '11-1021',\n",
       "  'onet_soc': ['11-1021.00'],\n",
       "  'description': 'Plan, direct, or coordinate the operations of public or private sector organizations, overseeing multiple departments or locations. Duties and responsibilities include formulating policies, managing daily operations, and planning the use of materials and human resources, but are too diverse and general in nature to be classified in any one functional area of management or administration, such as personnel, purchasing, or administrative services. Usually manage through subordinate supervisors. Excludes First-Line Supervisors.',\n",
       "  'tasks': 'Review financial statements, sales or activity reports, or other performance data to measure productivity or goal achievement or to identify areas needing cost reduction or program improvement. Direct and coordinate activities of businesses or departments concerned with the production, pricing, sales, or distribution of products. Direct administrative activities directly related to making products or providing services. Prepare staff work schedules and assign specific duties. Monitor suppliers to ensure that they efficiently and effectively provide needed goods or services within budgetary limits.',\n",
       "  'job_titles': 'Business Manager, General Manager (GM), Operations Director, Operations Manager, Plant Superintendent, Store Manager'},\n",
       " {'cenocc': '0030',\n",
       "  'occtitle': 'Legislators',\n",
       "  'cen_soc': '11-1031',\n",
       "  'onet_soc': ['11-1031.00'],\n",
       "  'description': 'Develop, introduce, or enact laws and statutes at the local, tribal, state, or federal level. Includes only workers in elected positions.',\n",
       "  'tasks': 'Analyze and understand the local and national implications of proposed legislation. Appoint nominees to leadership posts, or approve such appointments. Confer with colleagues to formulate positions and strategies pertaining to pending issues. Debate the merits of proposals and bill amendments during floor sessions, following the appropriate rules of procedure. Develop expertise in subject matters related to committee assignments.',\n",
       "  'job_titles': ''},\n",
       " {'cenocc': '0040',\n",
       "  'occtitle': 'Advertising and promotions managers',\n",
       "  'cen_soc': '11-2011',\n",
       "  'onet_soc': ['11-2011.00'],\n",
       "  'description': 'Plan, direct, or coordinate advertising policies and programs or produce collateral materials, such as posters, contests, coupons, or giveaways, to create extra interest in the purchase of a product or service for a department, an entire organization, or on an account basis.',\n",
       "  'tasks': 'Plan and prepare advertising and promotional material to increase sales of products or services, working with customers, company officials, sales departments, and advertising agencies. Inspect layouts and advertising copy and edit scripts, audio and video tapes, and other promotional material for adherence to specifications. Confer with department heads or staff to discuss topics such as contracts, selection of advertising media, or product to be advertised. Coordinate with the media to disseminate advertising. Coordinate activities of departments, such as sales, graphic arts, media, finance, and research.',\n",
       "  'job_titles': 'Account Executive, Advertising Manager (Ad Manager), Advertising Sales Manager (Ad Sales Manager), Classified Advertising Manager (Classified Ad Manager), Communications Director, Communications Manager, Creative Services Director, Marketing and Promotions Manager, Promotions Director, Promotions Manager'},\n",
       " {'cenocc': '0051',\n",
       "  'occtitle': 'Marketing managers',\n",
       "  'cen_soc': '11-2021',\n",
       "  'onet_soc': ['11-2021.00'],\n",
       "  'description': \"Plan, direct, or coordinate marketing policies and programs, such as determining the demand for products and services offered by a firm and its competitors, and identify potential customers. Develop pricing strategies with the goal of maximizing the firm's profits or share of the market while ensuring the firm's customers are satisfied. Oversee product development or monitor trends that indicate the need for new products and services.\",\n",
       "  'tasks': 'Identify, develop, or evaluate marketing strategy, based on knowledge of establishment objectives, market characteristics, and cost and markup factors. Formulate, direct, or coordinate marketing activities or policies to promote products or services, working with advertising or promotion managers. Evaluate the financial aspects of product development, such as budgets, expenditures, research and development appropriations, or return-on-investment and profit-loss projections. Develop pricing strategies, balancing firm objectives and customer satisfaction. Compile lists describing product or service offerings.',\n",
       "  'job_titles': 'Account Supervisor, Brand Manager, Business Development Director, Business Development Manager, Commercial Lines Manager, Market Development Executive, Marketing Coordinator, Marketing Director, Marketing Manager, Product Manager'}]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cenonet[0:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "507"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len( cenonet )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# save dictionary to json object\n",
    "with open( 'cenocc_onet.json' , 'w' ) as f:\n",
    "    json.dump( cenonet , f , indent = 4 )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The file `cenocc_onet.json` contains a link between Census occupation codes and the O\\*NET data described earlier in this notebook.\n",
    "\n",
    "It is possible to read the JSON file directly using `pandas` and the `read_json()` function. I demonstrate that below. I also print the `shape` of the dataframe; note that the number of rows equal 507, which is the same as the number of Census occupation codes in the `xwalk` file. Finally, I print the first 10 records of the dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# load json object with pandas\n",
    "df = pd.read_json( 'cenocc_onet.json' )\n",
    "df.sort_values( by = 'cenocc' , inplace = True )\n",
    "df.reset_index( drop = True , inplace = True )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(507, 7)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# print the dimensions of the object\n",
    "df.shape"
   ]
  },
  {
   "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>cenocc</th>\n",
       "      <th>occtitle</th>\n",
       "      <th>cen_soc</th>\n",
       "      <th>onet_soc</th>\n",
       "      <th>description</th>\n",
       "      <th>tasks</th>\n",
       "      <th>job_titles</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10</td>\n",
       "      <td>Chief executives</td>\n",
       "      <td>11-1011</td>\n",
       "      <td>[11-1011.00, 11-1011.03]</td>\n",
       "      <td>Determine and formulate policies and provide o...</td>\n",
       "      <td>Direct or coordinate an organization's financi...</td>\n",
       "      <td>Chief Diversity Officer (CDO), Chief Executive...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20</td>\n",
       "      <td>General and operations managers</td>\n",
       "      <td>11-1021</td>\n",
       "      <td>[11-1021.00]</td>\n",
       "      <td>Plan, direct, or coordinate the operations of ...</td>\n",
       "      <td>Review financial statements, sales or activity...</td>\n",
       "      <td>Business Manager, General Manager (GM), Operat...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>30</td>\n",
       "      <td>Legislators</td>\n",
       "      <td>11-1031</td>\n",
       "      <td>[11-1031.00]</td>\n",
       "      <td>Develop, introduce, or enact laws and statutes...</td>\n",
       "      <td>Analyze and understand the local and national ...</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>40</td>\n",
       "      <td>Advertising and promotions managers</td>\n",
       "      <td>11-2011</td>\n",
       "      <td>[11-2011.00]</td>\n",
       "      <td>Plan, direct, or coordinate advertising polici...</td>\n",
       "      <td>Plan and prepare advertising and promotional m...</td>\n",
       "      <td>Account Executive, Advertising Manager (Ad Man...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>51</td>\n",
       "      <td>Marketing managers</td>\n",
       "      <td>11-2021</td>\n",
       "      <td>[11-2021.00]</td>\n",
       "      <td>Plan, direct, or coordinate marketing policies...</td>\n",
       "      <td>Identify, develop, or evaluate marketing strat...</td>\n",
       "      <td>Account Supervisor, Brand Manager, Business De...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>52</td>\n",
       "      <td>Sales managers</td>\n",
       "      <td>11-2022</td>\n",
       "      <td>[11-2022.00]</td>\n",
       "      <td>Plan, direct, or coordinate the actual distrib...</td>\n",
       "      <td>Direct and coordinate activities involving sal...</td>\n",
       "      <td>District Sales Manager, National Sales Manager...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>60</td>\n",
       "      <td>Public relations and fundraising managers</td>\n",
       "      <td>11-2030</td>\n",
       "      <td>[11-2033.00, 11-2032.00]</td>\n",
       "      <td>Plan, direct, or coordinate activities to soli...</td>\n",
       "      <td>Assign, supervise, and review the activities o...</td>\n",
       "      <td>Account Supervisor, Annual Giving Director, De...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>101</td>\n",
       "      <td>Administrative services managers</td>\n",
       "      <td>11-3012</td>\n",
       "      <td>[11-3012.00]</td>\n",
       "      <td>Plan, direct, or coordinate one or more admini...</td>\n",
       "      <td>Prepare and review operational reports and sch...</td>\n",
       "      <td>Administrative Coordinator, Administrative Dir...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>102</td>\n",
       "      <td>Facilities managers</td>\n",
       "      <td>11-3013</td>\n",
       "      <td>[11-3013.00, 11-3013.01]</td>\n",
       "      <td>Plan, direct, or coordinate operations and fun...</td>\n",
       "      <td>Acquire, distribute and store supplies. Conduc...</td>\n",
       "      <td>Facilities Manager, Corporate Physical Securit...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>110</td>\n",
       "      <td>Computer and information systems managers</td>\n",
       "      <td>11-3021</td>\n",
       "      <td>[11-3021.00]</td>\n",
       "      <td>Plan, direct, or coordinate activities in such...</td>\n",
       "      <td>Direct daily operations of department, analyzi...</td>\n",
       "      <td>Application Development Director, Computing Se...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   cenocc                                   occtitle  cen_soc  \\\n",
       "0      10                           Chief executives  11-1011   \n",
       "1      20            General and operations managers  11-1021   \n",
       "2      30                                Legislators  11-1031   \n",
       "3      40        Advertising and promotions managers  11-2011   \n",
       "4      51                         Marketing managers  11-2021   \n",
       "5      52                             Sales managers  11-2022   \n",
       "6      60  Public relations and fundraising managers  11-2030   \n",
       "7     101           Administrative services managers  11-3012   \n",
       "8     102                        Facilities managers  11-3013   \n",
       "9     110  Computer and information systems managers  11-3021   \n",
       "\n",
       "                   onet_soc  \\\n",
       "0  [11-1011.00, 11-1011.03]   \n",
       "1              [11-1021.00]   \n",
       "2              [11-1031.00]   \n",
       "3              [11-2011.00]   \n",
       "4              [11-2021.00]   \n",
       "5              [11-2022.00]   \n",
       "6  [11-2033.00, 11-2032.00]   \n",
       "7              [11-3012.00]   \n",
       "8  [11-3013.00, 11-3013.01]   \n",
       "9              [11-3021.00]   \n",
       "\n",
       "                                         description  \\\n",
       "0  Determine and formulate policies and provide o...   \n",
       "1  Plan, direct, or coordinate the operations of ...   \n",
       "2  Develop, introduce, or enact laws and statutes...   \n",
       "3  Plan, direct, or coordinate advertising polici...   \n",
       "4  Plan, direct, or coordinate marketing policies...   \n",
       "5  Plan, direct, or coordinate the actual distrib...   \n",
       "6  Plan, direct, or coordinate activities to soli...   \n",
       "7  Plan, direct, or coordinate one or more admini...   \n",
       "8  Plan, direct, or coordinate operations and fun...   \n",
       "9  Plan, direct, or coordinate activities in such...   \n",
       "\n",
       "                                               tasks  \\\n",
       "0  Direct or coordinate an organization's financi...   \n",
       "1  Review financial statements, sales or activity...   \n",
       "2  Analyze and understand the local and national ...   \n",
       "3  Plan and prepare advertising and promotional m...   \n",
       "4  Identify, develop, or evaluate marketing strat...   \n",
       "5  Direct and coordinate activities involving sal...   \n",
       "6  Assign, supervise, and review the activities o...   \n",
       "7  Prepare and review operational reports and sch...   \n",
       "8  Acquire, distribute and store supplies. Conduc...   \n",
       "9  Direct daily operations of department, analyzi...   \n",
       "\n",
       "                                          job_titles  \n",
       "0  Chief Diversity Officer (CDO), Chief Executive...  \n",
       "1  Business Manager, General Manager (GM), Operat...  \n",
       "2                                                     \n",
       "3  Account Executive, Advertising Manager (Ad Man...  \n",
       "4  Account Supervisor, Brand Manager, Business De...  \n",
       "5  District Sales Manager, National Sales Manager...  \n",
       "6  Account Supervisor, Annual Giving Director, De...  \n",
       "7  Administrative Coordinator, Administrative Dir...  \n",
       "8  Facilities Manager, Corporate Physical Securit...  \n",
       "9  Application Development Director, Computing Se...  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# print the first 10 records of the dataframe\n",
    "df.head( 10 )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.8.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}