Copy_of_Copy_of_reilAutomation.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Copy of Copy of reilAutomation.ipynb",
      "version": "0.3.2",
      "provenance": [],
      "collapsed_sections": [],
      "toc_visible": true,
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "accelerator": "GPU"
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/mitreilab/autonomousfuture/blob/master/Copy_of_Copy_of_reilAutomation.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "metadata": {
        "id": "wEAKnMja1sHx",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## Import OES Data"
      ]
    },
    {
      "metadata": {
        "id": "8xQ0hltz1zjV",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "import numpy as np\n",
        "import pandas as pd\n",
        "import matplotlib\n",
        "import matplotlib.pyplot as pp\n",
        "!wget \"https://www.dropbox.com/s/t4444pduqc7rtgs/recentyears.csv?dl=0\" -q -O \"recent.csv\" \n",
        "!wget \"https://www.dropbox.com/s/6gglwj5jedmfvhi/oldyears.csv?dl=0\" -q -O \"old.csv\" \n",
        "#Download raw csv file from Dropbox and rename to \"rawdatanaics.csv\"\n",
        "new = pd.read_csv(\"recent.csv\",low_memory=False)\n",
        "old=pd.read_csv(\"old.csv\",low_memory=False)\n"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "SaW5pHN8D0E2",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## Import ONet Data ##"
      ]
    },
    {
      "metadata": {
        "id": "P0atFUe0D2JX",
        "colab_type": "code",
        "outputId": "0951d0e7-d9e5-4518-d3dc-46a241f0be69",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 36
        }
      },
      "cell_type": "code",
      "source": [
        "!wget \"https://www.dropbox.com/s/5cjlzvxqtoq5mm4/combined.sqlite?dl=0\" -q -O  \"onet.sqlite\"  \n",
        "#Download raw SQLite file from Dropbox and rename to \"onet.sqlite\"\n",
        "import sqlite3\n",
        "onetConnection = sqlite3.connect('onet.sqlite')\n",
        "cur = onetConnection.cursor()\n",
        "query = \"SELECT * FROM occupation_data\";\n",
        "occdata = pd.read_sql_query(query, onetConnection)\n",
        "len(occdata)\n",
        "# Onet surveyed 1109 occupations.\n"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "1109"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 78
        }
      ]
    },
    {
      "metadata": {
        "id": "R7b1aU2rark-",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        ""
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "7Z35kElLdrlP",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## OES Data cleaning"
      ]
    },
    {
      "metadata": {
        "id": "q_N-45psJbRe",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "##Link old occ code with occ code after 2010\n"
      ]
    },
    {
      "metadata": {
        "id": "bZIxrCojJkT2",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "!wget \"https://www.dropbox.com/s/30fryvdkh5qnk1z/crosswalktable.csv?dl=0\" -q -O \"crosswalk.csv\" \n",
        "match=pd.read_csv(\"crosswalk.csv\",low_memory=False)\n"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "dlcwSkzcLH-C",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "old.columns"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "rNKUQWbdL0Rz",
        "colab_type": "code",
        "outputId": "ebd2fcdf-fa9a-4f1a-ad6c-6875a8aedbf4",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 36
        }
      },
      "cell_type": "code",
      "source": [
        "# Build a dictionary for old code to be transformed into new\n",
        "oldlist=match['2000 SOC code'].tolist()\n",
        "newlist=match['2010 SOC code'].tolist()\n",
        "keys = oldlist\n",
        "values = newlist\n",
        "matchbook= dict(zip(keys, values))\n",
        "matchbook[113041]"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "113111"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 5
        }
      ]
    },
    {
      "metadata": {
        "id": "Y1Edy573ZtoB",
        "colab_type": "code",
        "outputId": "978e9989-8219-42e0-ed8e-ec6369a96759",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 36
        }
      },
      "cell_type": "code",
      "source": [
        "tobecorrect=old['occ_code'].tolist()\n",
        "len(tobecorrect)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "7166"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 6
        }
      ]
    },
    {
      "metadata": {
        "id": "H_FMVwEUXNkl",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "for i in range(len(tobecorrect)):\n",
        "  oldcode=tobecorrect[i]\n",
        "  try:\n",
        "    temp=matchbook[oldcode]\n",
        "    tobecorrect[i]=temp\n",
        "  except:\n",
        "    continue\n",
        "correctcode= tobecorrect   \n",
        "  \n"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "BVfN5pxoL0Kj",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "\n",
        "correctcode=pd.DataFrame(correctcode)\n"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "VaW1nUmmL0C7",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "old['occ_code']=correctcode"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "MPJ0o4o0Lz2s",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "old.reset_index(drop=True, inplace=True)\n",
        "new.reset_index(drop=True, inplace=True)\n",
        "result = pd.concat([old,new])"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "srJ2w8tDeQ-B",
        "colab_type": "code",
        "outputId": "f24e8ed1-1ccc-4881-b510-5e6f91f465d0",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 36
        }
      },
      "cell_type": "code",
      "source": [
        "old.shape"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "(7166, 26)"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 11
        }
      ]
    },
    {
      "metadata": {
        "id": "9eudO3HIeZCT",
        "colab_type": "code",
        "outputId": "390f2430-43e2-4dd1-a484-f480a4e8f831",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 36
        }
      },
      "cell_type": "code",
      "source": [
        "new.shape"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "(15628, 26)"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 12
        }
      ]
    },
    {
      "metadata": {
        "id": "X5NbE-AWgkwq",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "result.reset_index(drop=True, inplace=True)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "9l_XrJRDgkq3",
        "colab_type": "code",
        "outputId": "0c8f5ebb-915e-4bc2-e653-7056ed83150c",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 36
        }
      },
      "cell_type": "code",
      "source": [
        "len(newlist)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "792"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 14
        }
      ]
    },
    {
      "metadata": {
        "id": "hWfYzmWqgklT",
        "colab_type": "code",
        "outputId": "650853a6-25ac-42d3-b360-40d7369690c3",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 36
        }
      },
      "cell_type": "code",
      "source": [
        "df=result[result['occ_code'].isin(newlist)]\n",
        "df.shape\n",
        "# Now we finishe building the OLS dabatase\n",
        "# There are 792  detailed ocupations in US\n"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "(21264, 26)"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 15
        }
      ]
    },
    {
      "metadata": {
        "id": "y-TphPoLgjvi",
        "colab_type": "code",
        "outputId": "367789f1-6208-4ca9-c2ea-c1d62f58a426",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 36
        }
      },
      "cell_type": "code",
      "source": [
        "# After we choose 13 industries (using 4_digit NAISC)\n",
        "# We have 528 detailed occupations\n",
        "targetlist=df['occ_code'].unique()\n",
        "len(targetlist)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "528"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 18
        }
      ]
    },
    {
      "metadata": {
        "id": "eGKLo1f7gjny",
        "colab_type": "code",
        "outputId": "21e0f5cd-a55a-4e21-e8e0-7e35d551b3e6",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 533
        }
      },
      "cell_type": "code",
      "source": [
        "df.head()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>year</th>\n",
              "      <th>naics</th>\n",
              "      <th>naics_title</th>\n",
              "      <th>occ_code</th>\n",
              "      <th>occ_title</th>\n",
              "      <th>group</th>\n",
              "      <th>tot_emp_num</th>\n",
              "      <th>emp_prse</th>\n",
              "      <th>pct_total</th>\n",
              "      <th>pct_rpt</th>\n",
              "      <th>...</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_num</th>\n",
              "      <th>a_pct75</th>\n",
              "      <th>a_pct90</th>\n",
              "      <th>naics_codedescription</th>\n",
              "      <th>occ_code-title</th>\n",
              "      <th>detailed</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2007</td>\n",
              "      <td>236100</td>\n",
              "      <td>Residential Building Construction</td>\n",
              "      <td>111011</td>\n",
              "      <td>Chief Executives</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2700.0</td>\n",
              "      <td>6.9</td>\n",
              "      <td>0.28</td>\n",
              "      <td>1</td>\n",
              "      <td>...</td>\n",
              "      <td>#</td>\n",
              "      <td>#</td>\n",
              "      <td>74180</td>\n",
              "      <td>115920</td>\n",
              "      <td>NaN</td>\n",
              "      <td>#</td>\n",
              "      <td>#</td>\n",
              "      <td>236100-Residential Building Construction</td>\n",
              "      <td>11-1011-Chief Executives</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2007</td>\n",
              "      <td>236100</td>\n",
              "      <td>Residential Building Construction</td>\n",
              "      <td>111021</td>\n",
              "      <td>General And Operations Managers</td>\n",
              "      <td>NaN</td>\n",
              "      <td>16680.0</td>\n",
              "      <td>3.4</td>\n",
              "      <td>1.72</td>\n",
              "      <td>7</td>\n",
              "      <td>...</td>\n",
              "      <td>#</td>\n",
              "      <td>#</td>\n",
              "      <td>46810</td>\n",
              "      <td>65310</td>\n",
              "      <td>98290.0</td>\n",
              "      <td>#</td>\n",
              "      <td>#</td>\n",
              "      <td>236100-Residential Building Construction</td>\n",
              "      <td>11-1021-General And Operations Managers</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2007</td>\n",
              "      <td>236100</td>\n",
              "      <td>Residential Building Construction</td>\n",
              "      <td>112021</td>\n",
              "      <td>Marketing Managers</td>\n",
              "      <td>NaN</td>\n",
              "      <td>1090.0</td>\n",
              "      <td>6.5</td>\n",
              "      <td>0.11</td>\n",
              "      <td>~</td>\n",
              "      <td>...</td>\n",
              "      <td>58.13</td>\n",
              "      <td>#</td>\n",
              "      <td>47930</td>\n",
              "      <td>64750</td>\n",
              "      <td>84200.0</td>\n",
              "      <td>120910</td>\n",
              "      <td>#</td>\n",
              "      <td>236100-Residential Building Construction</td>\n",
              "      <td>11-2021-Marketing Managers</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>2007</td>\n",
              "      <td>236100</td>\n",
              "      <td>Residential Building Construction</td>\n",
              "      <td>112022</td>\n",
              "      <td>Sales Managers</td>\n",
              "      <td>NaN</td>\n",
              "      <td>1540.0</td>\n",
              "      <td>5.8</td>\n",
              "      <td>0.16</td>\n",
              "      <td>1</td>\n",
              "      <td>...</td>\n",
              "      <td>67.45</td>\n",
              "      <td>#</td>\n",
              "      <td>49600</td>\n",
              "      <td>67100</td>\n",
              "      <td>95020.0</td>\n",
              "      <td>140290</td>\n",
              "      <td>#</td>\n",
              "      <td>236100-Residential Building Construction</td>\n",
              "      <td>11-2022-Sales Managers</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>2007</td>\n",
              "      <td>236100</td>\n",
              "      <td>Residential Building Construction</td>\n",
              "      <td>112031</td>\n",
              "      <td>Public Relations And Fundraising Managers</td>\n",
              "      <td>NaN</td>\n",
              "      <td>50.0</td>\n",
              "      <td>22.1</td>\n",
              "      <td>0.01</td>\n",
              "      <td>~</td>\n",
              "      <td>...</td>\n",
              "      <td>59.19</td>\n",
              "      <td>#</td>\n",
              "      <td>37600</td>\n",
              "      <td>57360</td>\n",
              "      <td>95690.0</td>\n",
              "      <td>123110</td>\n",
              "      <td>#</td>\n",
              "      <td>236100-Residential Building Construction</td>\n",
              "      <td>11-2031-Public Relations And Fundraising Managers</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>5 rows × 26 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "   year   naics                        naics_title  occ_code  \\\n",
              "0  2007  236100  Residential Building Construction    111011   \n",
              "1  2007  236100  Residential Building Construction    111021   \n",
              "2  2007  236100  Residential Building Construction    112021   \n",
              "3  2007  236100  Residential Building Construction    112022   \n",
              "4  2007  236100  Residential Building Construction    112031   \n",
              "\n",
              "                                   occ_title  group  tot_emp_num emp_prse  \\\n",
              "0                           Chief Executives    NaN       2700.0      6.9   \n",
              "1            General And Operations Managers    NaN      16680.0      3.4   \n",
              "2                         Marketing Managers    NaN       1090.0      6.5   \n",
              "3                             Sales Managers    NaN       1540.0      5.8   \n",
              "4  Public Relations And Fundraising Managers    NaN         50.0     22.1   \n",
              "\n",
              "  pct_total pct_rpt   ...    h_pct75 h_pct90 a_pct10 a_pct25 a_median_num  \\\n",
              "0      0.28       1   ...          #       #   74180  115920          NaN   \n",
              "1      1.72       7   ...          #       #   46810   65310      98290.0   \n",
              "2      0.11       ~   ...      58.13       #   47930   64750      84200.0   \n",
              "3      0.16       1   ...      67.45       #   49600   67100      95020.0   \n",
              "4      0.01       ~   ...      59.19       #   37600   57360      95690.0   \n",
              "\n",
              "  a_pct75 a_pct90                     naics_codedescription  \\\n",
              "0       #       #  236100-Residential Building Construction   \n",
              "1       #       #  236100-Residential Building Construction   \n",
              "2  120910       #  236100-Residential Building Construction   \n",
              "3  140290       #  236100-Residential Building Construction   \n",
              "4  123110       #  236100-Residential Building Construction   \n",
              "\n",
              "                                      occ_code-title detailed  \n",
              "0                           11-1011-Chief Executives        1  \n",
              "1            11-1021-General And Operations Managers        1  \n",
              "2                         11-2021-Marketing Managers        1  \n",
              "3                             11-2022-Sales Managers        1  \n",
              "4  11-2031-Public Relations And Fundraising Managers        1  \n",
              "\n",
              "[5 rows x 26 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 30
        }
      ]
    },
    {
      "metadata": {
        "id": "1sPV6mcpgje4",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        ""
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "T5WcmXpTZX_L",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "### Target the occupations that are 1. In Real Estate industry 2. Not General Group 3. Onet has surveyed\n"
      ]
    },
    {
      "metadata": {
        "id": "MpdkLpRlZ9UR",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# !wget \"https://www.dropbox.com/s/329edv8yrn3ik25/Occupation%20Data.csv?dl=0\"-q -O  \"Occupation Data.csv\" \n",
        "# occ_onet=pd.read_csv(\"Occupation Data.csv\",low_memory=False)\n",
        "# #Download 'Occupation Data' CSV that changes the 8-digit Onet-code to 6-digit, ommiting the last two digits.\n",
        "# del_occ_endwith_00_and_in_Onet=oes.loc[oes['occ_code'].isin(occ_onet['O*NET-SOC Code'])]\n",
        "# #Delete the occupation if the code is not in 'Occupation Data'\n",
        "# del_occ_endwith_00_and_in_Onet.shape"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "NGLAv20Bd5qR",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# target_occ=set(del_occ_endwith_00_and_in_Onet['occ_code'])\n",
        "# target_occ=list(target_occ)\n",
        "# target=pd.DataFrame({'target_occ': target_occ})\n",
        "# len(target)\n",
        "# # we target 558 occupations."
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "k6rLO1XWeu6n",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "### Drop the columns in the oes.csv that contains duplicated or redundant data."
      ]
    },
    {
      "metadata": {
        "id": "LmSKkbK8fJon",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# del_occ_endwith_00_and_in_Onet.columns\n",
        "# #drop the colomuns that contain duplicate information\n",
        "# del_duplicated_column=del_occ_endwith_00_and_in_Onet.drop(['title_code','occ_title_code', 'occ_title_mid', 'year.1', 'naics.1', 'naics_title.1', 'occ_code.1', 'occ_title.1', 'group', 'naics_codedescription', 'year-naics-occ_code', 'year-naics-occ_code.1','year.2', 'naics.2', 'naics_title.2', 'occ_code.2', 'title_code.1', 'dominant_lifecycle.1', 'occ_title.2', 'occ_title_code.1', 'occ_title_mid.1','group.1', 'naics_codedescription.1', 'keepsy'],axis=1)\n",
        "# # the columns # decrease to 37 from 61\n",
        "# del_duplicated_column.shape"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "j0QACoI1gpG3",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## Call BLS data by 6-digit occupation code"
      ]
    },
    {
      "metadata": {
        "id": "zK-dVy5ShHTW",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# try to extract data by occ_code \n",
        "dx = del_duplicated_column[del_duplicated_column['occ_code'] == '11-1011']\n",
        "dx[['year','naics_title', 'tot_emp_num']].head(3)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "8S8H1sX4hOnm",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# assign the matrix of \"year\" and \"naics_code\" to a specific occ_code, call it 'labour_economy_data'\n",
        "# first, create a function that generate the lb_econ_matrix for an occ_code\n",
        "def lb_econ (occ_code,field,data):\n",
        "    # this function yield a data frame for a given occ_code and a field\n",
        "    # get all unique naics titles in the data\n",
        "    feature_list = data['naics'].unique()\n",
        "    # get all unique year in the data\n",
        "    years = sorted(data['year'].unique())    \n",
        "    mt_result = pd.DataFrame(0.00, index = years, columns = feature_list)   \n",
        "    # filter out all rows and columns that have the given occ_code and fields\n",
        "    df_occ = data[data['occ_code'] == occ_code][['year','naics_title', field]]\n",
        "    for index, row in df_occ.iterrows():\n",
        "        try:\n",
        "            mt_result.loc[row['year']][row['naics_title']] = float(row[field])\n",
        "        except:\n",
        "            mt_result.loc[row['year']][row['naics_title']] = np.nan\n",
        "    return mt_result.round(2)\n"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "7JNwpGFQhdRp",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# An example of how to use the function\n",
        "data1=del_duplicated_column\n",
        "# the lb_Econ_features contains all the fields' name that we can choose from, there're 17 all together.\n",
        "lb_econ_features=['occ_code','tot_emp_num', 'emp_prse', 'pct_total', 'pct_rpt', 'h_mean', 'a_mean', 'mean_prse', 'h_pct10', 'h_pct25', 'h_median', 'h_pct75', 'h_pct90', 'a_pct10', 'a_pct25', 'a_median_num', 'a_pct75', 'a_pct90']\n",
        "#try to input a field name called 'tot_emp_num'\n",
        "aa=lb_econ (111011,'tot_emp_num',df)\n",
        "aa.head()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "gcJHcg70dnSg",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        ""
      ]
    },
    {
      "metadata": {
        "id": "SDFwgywni3Bh",
        "colab_type": "code",
        "outputId": "29b4f757-1af3-4130-93a7-4acc5386a276",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 129
        }
      },
      "cell_type": "code",
      "source": [
        "df.columns"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "Index(['year', 'naics', 'naics_title', 'occ_code', 'occ_title', 'group',\n",
              "       'tot_emp_num', 'emp_prse', 'pct_total', 'pct_rpt', 'h_mean', 'a_mean',\n",
              "       'mean_prse', 'h_pct10', 'h_pct25', 'h_median', 'h_pct75', 'h_pct90',\n",
              "       'a_pct10', 'a_pct25', 'a_median_num', 'a_pct75', 'a_pct90',\n",
              "       'naics_codedescription', 'occ_code-title', 'detailed'],\n",
              "      dtype='object')"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 21
        }
      ]
    },
    {
      "metadata": {
        "id": "RUqI754ecqcZ",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "# Object Oriented Programming (OOP)"
      ]
    },
    {
      "metadata": {
        "id": "XelbsrqgcwLP",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# laborfeature include:'tot_emp_num', 'emp_prse', 'pct_total', 'pct_rpt', 'h_mean', 'a_mean',\n",
        "#        'mean_prse', 'h_pct10', 'h_pct25', 'h_median', 'h_pct75', 'h_pct90',\n",
        "#        'a_pct10', 'a_pct25', 'a_median_num', 'a_pct75', 'a_pct90',\n",
        "#        'naics_codedescription'\n",
        "# year include: 2007 to 2016\n",
        "# industry include:\n",
        "# 'Residential Building Construction',\n",
        "# 'Nonresidential Building Construction',\n",
        "# 'Land Subdivision',\n",
        "# 'Foundation, Structure, and Building Exterior Contractors',\n",
        "# 'Building Equipment Contractors',\n",
        "# 'Building Finishing Contractors',\n",
        "# 'Other Specialty Trade Contractors',\n",
        "# 'Lessors of Real Estate',\n",
        "# 'Offices of Real Estate Agents and Brokers',\n",
        "# 'Activities Related to Real Estate',\n",
        "# 'Architectural, Engineering, and Related Services',\n",
        "# 'Specialized Design Services',\n",
        "# 'Services to Buildings and Dwellings',\n",
        "      \n",
        "class occ(object):\n",
        "  # add attributes to occ\n",
        "  def __init__(self,code,title,year,industry,industrycode,name):\n",
        "    self.code=code\n",
        "    self.title=title\n",
        "    self.year=year  \n",
        "    self.industry=industry\n",
        "    self.industrycode=industrycode\n",
        "    self.name=name\n",
        "\n",
        "#     yearemp,\n",
        "#               emp_prse, pct_total, pct_rpt, h_mean, a_mean,\n",
        "#        mean_prse, h_pct10, h_pct25, h_median, h_pct75, h_pct90,\n",
        "#        a_pct10, a_pct25, a_median_num, a_pct75, a_pct90,\n",
        "#       naics_codedescription,tasks,abilities,knowledge,skills,work_activities\n",
        "#     self.yearemp=yearemp\n",
        "#     self.emp_prse=emp_prse\n",
        "#     self.pct_total=pct_total\n",
        "#     self.pct_rpt=pct_rpt\n",
        "#     self.h_mean=h_mean\n",
        "#     self.a_mean=a.mean\n",
        "#     self.mean_prse=mean_prse\n",
        "#     self.h_pct10=h_pct10\n",
        "#     self.h_pct25=h_pct25\n",
        "#     self.h_median=h_median\n",
        "#     self.h_pct75=h_pct75\n",
        "#     self.h_pct90=h_pct90\n",
        "#     self.a_pct10=a_pct10\n",
        "#     self.a_pct25=a_pct25\n",
        "#     self.a_median_num=a_median_num\n",
        "#     self.a_pct75=a_pct75\n",
        "#     self.a_pct90=a_pct90\n",
        "#     self.naics_codedescription=naics_codedescription\n",
        "#     self.laborfeature=laborfeature\n",
        "#     self.task=tasks\n",
        "#     self.abilities=abilities\n",
        "#     self.knowledge=knowledge\n",
        "#     self.skills=skills\n",
        "#     self.work_activities=work_activities\n",
        "  def __str__(self):\n",
        "    return str(self.code)+str(self.title)+str(self.year)+str(self.industry+str(self.industrycode))\n",
        "#   def__add__(self,other):\n",
        "#     result=self.yearemp+other.yearemp\n",
        "#     return result\n",
        "#   def__eq__(self,other):\n",
        "    \n",
        "  # define methods:\n",
        "  # total employment calculate employment number of each occ in all 13 industries\n",
        "  # inputs for this function should be the 13 occ objects\n",
        "  # with same code,title,year\n",
        "  # with different industry,industry code,13 industries in the same year\n",
        "#   def sumemp(self,other):\n",
        "#     if self.code==other.code and self.year==other.year:\n",
        "#       return self.yearemp+other.yearemp\n",
        "\n"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "A1g288EDsUFs",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## make all the objects, 528 times 10 years times 13 industries"
      ]
    },
    {
      "metadata": {
        "id": "9kIbgv20u6Lh",
        "colab_type": "code",
        "outputId": "a1fc7905-b149-4787-f1bc-90ecc623036a",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 36
        }
      },
      "cell_type": "code",
      "source": [
        "row=df.iloc[0]\n",
        "type(row[['year','naics']])\n"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "pandas.core.series.Series"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 72
        }
      ]
    },
    {
      "metadata": {
        "id": "EJwHou_PvWZl",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "a=occ(row['occ_code'],row['occ_title'],row['year'],row['naics_title'],row['naics'],'name1')\n",
        "b=occ(row2['occ_code'],row2['occ_title'],row2['year'],row2['naics_title'],row2['naics'],'name2')"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "gFAvPJpAvZLw",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "newlist.append(a.name)\n",
        "newlist"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "T4uTu7pmrwjE",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# there are at most 68640 occ objects in total\n",
        "# now we create them out and name them by 'occ+their occ_code'\n",
        "listofocc=[]\n",
        "all_occ=[]\n",
        "for index, row in df.iterrows():\n",
        "  #create a new object\n",
        "  name=str(row['occ_code'])+str(row['occ_title'])+'in year'+str(row['year'])+'in industry'+str(row['naics'])\n",
        "  temp=occ(row['occ_code'],row['occ_title'],row['year'],row['naics_title'],row['naics'],name)\n",
        "  listofocc.append(temp.name)\n",
        "  all_occ.append(temp)\n",
        "  \n",
        "  "
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "TOE2zFdExqkm",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# the list containing all the objects names is named as listofocc\n",
        "# the list containing all the objects is named as all_occ\n",
        "listofocc"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "ifpKGwGaxrUs",
        "colab_type": "code",
        "outputId": "1783abfc-8d98-4d1c-a0b1-8c565d1f9680",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 36
        }
      },
      "cell_type": "code",
      "source": [
        "len(listofocc)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "21264"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 65
        }
      ]
    },
    {
      "metadata": {
        "id": "qEMT7tLKy9WD",
        "colab_type": "code",
        "outputId": "0c40f7fa-d067-44be-ead1-76f26ef5db47",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 36
        }
      },
      "cell_type": "code",
      "source": [
        "len(all_occ)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "21264"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 69
        }
      ]
    },
    {
      "metadata": {
        "id": "vA6yhT199ngJ",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "!wget \"https://www.dropbox.com/s/9ajd56nnhlrfo2m/Task%20Statements.csv?dl=0\" -q -O \"Task Statements.csv\" \n",
        "Tasks = pd.read_csv(\"Task Statements.csv\",low_memory=False)\n",
        "!wget \"https://www.dropbox.com/s/9ez75cfcojjpyur/Work%20Activities.csv?dl=0\" -q -O \"Work Activities.csv\" \n",
        "Activities = pd.read_csv(\"Work Activities.csv\",low_memory=False)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "Gya_v48t1RT3",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# Build a dictionary where\n",
        "#   -  keys: the occ object\n",
        "# Values : all the labor econ attributes and The O_net attributes\n",
        "\n",
        "\n",
        "keys = all_occ\n",
        "\n",
        "values =[]\n",
        "# # We first define some functions that we need to search values from Onet\n",
        "# # The function to search for tasks for an occpation:\n",
        "# def gettask(occ_code,Tasks):\n",
        "#   for index, row in Tasks.iterrows():\n",
        "#     temp=Tasks[Tasks['occ_code']==occ_code][['Task ID','Task']]\n",
        "#     onetvalue=temp.values.tolist()\n",
        "#   return onetvalue\n",
        "  \n",
        "# # The function to search for tasks for an occpation:\n"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "yojBypny-X7k",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# # Then we have a tasklist for each occupation, but this method is slow\n",
        "# tasklist=gettask(111011,Tasks)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "LrjoeEX1_XQC",
        "colab_type": "code",
        "outputId": "dd4ea3c7-014a-4411-ae0a-442cb0e66e6b",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 74
        }
      },
      "cell_type": "code",
      "source": [
        "# tasklist[2]"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[8825,\n",
              " '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.']"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 101
        }
      ]
    },
    {
      "metadata": {
        "id": "24sHv4ZE_q3g",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "def gettask(occ_code,Tasks):\n",
        "  for index, row in Tasks.iterrows():\n",
        "    temp=Tasks[Tasks['occ_code']==occ_code][['Task ID','Task']]\n",
        "  return temp"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "lWWnnE8C_qwr",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        ""
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "q7eAq3YB_qqK",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        ""
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "uABCcyLb_qfp",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        ""
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "UZjjnT0o-RIr",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "for index, row in df.iterrows():\n",
        "  #create a sublist to be one element of the values list\n",
        "  temp=row[['occ_code','tot_emp_num', 'emp_prse', 'pct_total', 'pct_rpt', 'h_mean', 'a_mean', 'mean_prse', 'h_pct10', 'h_pct25', 'h_median', 'h_pct75', 'h_pct90', 'a_pct10', 'a_pct25', 'a_median_num', 'a_pct75', 'a_pct90']]\n",
        "  temptask=gettask(row['occ_code'])\n",
        "  temp\n",
        "  \n",
        "  values.append(temp)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "in5C_jpd3jQx",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# The dictionary is named is manual\n",
        "manual= dict(zip(keys, values))"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "mSHaKASD3kGx",
        "colab_type": "code",
        "outputId": "9ff7abe8-cb6e-4dac-bd60-b03e61aa7d90",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 372
        }
      },
      "cell_type": "code",
      "source": [
        "manual[all_occ[1]]"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "occ_code        111021\n",
              "tot_emp_num      16680\n",
              "emp_prse           3.4\n",
              "pct_total         1.72\n",
              "pct_rpt              7\n",
              "h_mean           55.15\n",
              "a_mean          114710\n",
              "mean_prse          1.9\n",
              "h_pct10           22.5\n",
              "h_pct25           31.4\n",
              "h_median         47.26\n",
              "h_pct75              #\n",
              "h_pct90              #\n",
              "a_pct10          46810\n",
              "a_pct25          65310\n",
              "a_median_num     98290\n",
              "a_pct75              #\n",
              "a_pct90              #\n",
              "Name: 1, dtype: object"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 77
        }
      ]
    },
    {
      "metadata": {
        "id": "2C6TTXJn4E5J",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "query = \"SELECT * FROM occupation_data\";\n",
        "occdata = pd.read_sql_query(query, onetConnection)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "9CAecAZB6jU6",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "targetlist=targetlist.tolist()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "KBTeVKPp43SA",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# built a occpationcode list to change 6digit to 8 digit and change the form\n",
        "onetform=[]\n",
        "for item in targetlist:\n",
        "  string=str(item)\n",
        "  temp=str(string[:1]) + '-' +str(string[2:])+'.00'\n",
        "  onetform.append(temp)\n",
        "onetform"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "N2d2WNFH5FZF",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        ""
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "ONrMMCXJ52L0",
        "colab_type": "code",
        "outputId": "09c210e2-8991-4224-8441-451493226c28",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 3590
        }
      },
      "cell_type": "code",
      "source": [
        ""
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<bound method NDFrame.head of        occ_code                              Title  Task ID  \\\n",
              "0        111011                   Chief Executives     8823   \n",
              "1        111011                   Chief Executives     8831   \n",
              "2        111011                   Chief Executives     8825   \n",
              "3        111011                   Chief Executives     8826   \n",
              "4        111011                   Chief Executives     8827   \n",
              "5        111011                   Chief Executives     8824   \n",
              "6        111011                   Chief Executives     8836   \n",
              "7        111011                   Chief Executives     8832   \n",
              "8        111011                   Chief Executives     8835   \n",
              "9        111011                   Chief Executives     8833   \n",
              "10       111011                   Chief Executives     8829   \n",
              "11       111011                   Chief Executives     8837   \n",
              "12       111011                   Chief Executives     8830   \n",
              "13       111011                   Chief Executives     8839   \n",
              "14       111011                   Chief Executives     8843   \n",
              "15       111011                   Chief Executives     8834   \n",
              "16       111011                   Chief Executives    20461   \n",
              "17       111011                   Chief Executives     8846   \n",
              "18       111011                   Chief Executives     8848   \n",
              "19       111011                   Chief Executives     8828   \n",
              "20       111011                   Chief Executives     8844   \n",
              "21       111011                   Chief Executives     8845   \n",
              "22       111011                   Chief Executives     8847   \n",
              "23       111011                   Chief Executives     8851   \n",
              "24       111011                   Chief Executives     8838   \n",
              "25       111011                   Chief Executives     8853   \n",
              "26       111011                   Chief Executives     8840   \n",
              "27       111011                   Chief Executives     8849   \n",
              "28       111011                   Chief Executives     8852   \n",
              "29       111011                   Chief Executives     8850   \n",
              "...         ...                                ...      ...   \n",
              "19582    537111         Mine Shuttle Car Operators     5028   \n",
              "19583    537111         Mine Shuttle Car Operators     5029   \n",
              "19584    537111         Mine Shuttle Car Operators     5030   \n",
              "19585    537111         Mine Shuttle Car Operators     5031   \n",
              "19586    537111         Mine Shuttle Car Operators     5032   \n",
              "19587    537111         Mine Shuttle Car Operators     5033   \n",
              "19588    537111         Mine Shuttle Car Operators     5034   \n",
              "19589    537111         Mine Shuttle Car Operators     5035   \n",
              "19590    537111         Mine Shuttle Car Operators     5036   \n",
              "19591    537111         Mine Shuttle Car Operators     5037   \n",
              "19592    537111         Mine Shuttle Car Operators     5038   \n",
              "19593    537121  Tank Car, Truck, and Ship Loaders    12792   \n",
              "19594    537121  Tank Car, Truck, and Ship Loaders    12793   \n",
              "19595    537121  Tank Car, Truck, and Ship Loaders    12794   \n",
              "19596    537121  Tank Car, Truck, and Ship Loaders    12795   \n",
              "19597    537121  Tank Car, Truck, and Ship Loaders    12796   \n",
              "19598    537121  Tank Car, Truck, and Ship Loaders    12797   \n",
              "19599    537121  Tank Car, Truck, and Ship Loaders    12798   \n",
              "19600    537121  Tank Car, Truck, and Ship Loaders    12799   \n",
              "19601    537121  Tank Car, Truck, and Ship Loaders    12800   \n",
              "19602    537121  Tank Car, Truck, and Ship Loaders    12801   \n",
              "19603    537121  Tank Car, Truck, and Ship Loaders    12802   \n",
              "19604    537121  Tank Car, Truck, and Ship Loaders    12803   \n",
              "19605    537121  Tank Car, Truck, and Ship Loaders    12804   \n",
              "19606    537121  Tank Car, Truck, and Ship Loaders    12805   \n",
              "19607    537121  Tank Car, Truck, and Ship Loaders    12806   \n",
              "19608    537121  Tank Car, Truck, and Ship Loaders    12807   \n",
              "19609    537121  Tank Car, Truck, and Ship Loaders    12808   \n",
              "19610    537121  Tank Car, Truck, and Ship Loaders    12809   \n",
              "19611    537121  Tank Car, Truck, and Ship Loaders    12810   \n",
              "\n",
              "                                                    Task     Task Type  \\\n",
              "0      Direct or coordinate an organization's financi...          Core   \n",
              "1      Appoint department heads or managers and assig...          Core   \n",
              "2      Analyze operations to evaluate performance of ...          Core   \n",
              "3      Direct, plan, or implement policies, objective...          Core   \n",
              "4      Prepare budgets for approval, including those ...          Core   \n",
              "5      Confer with board members, organization offici...          Core   \n",
              "6      Implement corrective action plans to solve org...          Core   \n",
              "7      Direct human resources activities, including t...          Core   \n",
              "8      Establish departmental responsibilities and co...          Core   \n",
              "9      Preside over or serve on boards of directors, ...          Core   \n",
              "10     Negotiate or approve contracts or agreements w...          Core   \n",
              "11     Coordinate the development or implementation o...          Core   \n",
              "12     Review reports submitted by staff members to r...          Core   \n",
              "13     Deliver speeches, write articles, or present i...          Core   \n",
              "14     Interpret and explain policies, rules, regulat...          Core   \n",
              "15     Prepare or present reports concerning activiti...          Core   \n",
              "16     Review and analyze legislation, laws, or publi...          Core   \n",
              "17     Administer programs for selection of sites, co...          Core   \n",
              "18     Direct or conduct studies or research on issue...          Core   \n",
              "19     Direct or coordinate activities of businesses ...  Supplemental   \n",
              "20     Make presentations to legislative or other gov...  Supplemental   \n",
              "21     Refer major policy matters to elected represen...  Supplemental   \n",
              "22     Direct or coordinate activities of businesses ...  Supplemental   \n",
              "23     Conduct or direct investigations or hearings t...  Supplemental   \n",
              "24     Direct non-merchandising departments, such as ...  Supplemental   \n",
              "25     Prepare bylaws approved by elected officials a...  Supplemental   \n",
              "26     Serve as liaisons between organizations, share...  Supplemental   \n",
              "27     Attend and participate in meetings of municipa...  Supplemental   \n",
              "28     Represent organizations or promote their objec...  Supplemental   \n",
              "29            Organize or approve promotional campaigns.  Supplemental   \n",
              "...                                                  ...           ...   \n",
              "19582  Clean, fuel, and service equipment, and repair...          Core   \n",
              "19583  Move mine cars into position for loading and u...  Supplemental   \n",
              "19584  Guide and stop cars by switching, applying bra...  Supplemental   \n",
              "19585  Push or ride cars down slopes, or hook cars to...  Supplemental   \n",
              "19586  Observe hand signals, grade stakes, or other m...  Supplemental   \n",
              "19587  Open and close bottom doors of cars to dump co...  Supplemental   \n",
              "19588  Direct other workers to move stakes, place blo...  Supplemental   \n",
              "19589  Monitor loading processes to ensure that mater...  Supplemental   \n",
              "19590  Measure, weigh, or verify levels of rock, grav...  Supplemental   \n",
              "19591  Read written instructions or confer with super...  Supplemental   \n",
              "19592               Maintain records of materials moved.  Supplemental   \n",
              "19593  Verify tank car, barge, or truck load numbers ...          Core   \n",
              "19594  Observe positions of cars passing loading spou...          Core   \n",
              "19595  Operate ship loading and unloading equipment, ...          Core   \n",
              "19596  Monitor product movement to and from storage t...          Core   \n",
              "19597  Record operating data such as products and qua...          Core   \n",
              "19598  Check conditions and weights of vessels to ens...          Core   \n",
              "19599  Operate industrial trucks, tractors, loaders a...          Core   \n",
              "19600  Connect ground cables to carry off static elec...  Supplemental   \n",
              "19601  Seal outlet valves on tank cars, barges, and t...  Supplemental   \n",
              "19602  Test samples for specific gravity, using hydro...  Supplemental   \n",
              "19603  Remove and replace tank car dome caps, or dire...  Supplemental   \n",
              "19604  Lower gauge rods into tanks or read meters to ...  Supplemental   \n",
              "19605  Clean interiors of tank cars or tank trucks, u...  Supplemental   \n",
              "19606  Operate conveyors and equipment to transfer gr...  Supplemental   \n",
              "19607  Test vessels for leaks, damage, and defects, a...  Supplemental   \n",
              "19608  Unload cars containing liquids by connecting h...  Supplemental   \n",
              "19609  Copy and attach load specifications to loaded ...  Supplemental   \n",
              "19610  Start pumps and adjust valves or cables to reg...  Supplemental   \n",
              "19611  Perform general warehouse activities, such as ...  Supplemental   \n",
              "\n",
              "       Incumbents Responding     Date Domain Source  \n",
              "0                       87.0  07/2014     Incumbent  \n",
              "1                       87.0  07/2014     Incumbent  \n",
              "2                       87.0  07/2014     Incumbent  \n",
              "3                       87.0  07/2014     Incumbent  \n",
              "4                       87.0  07/2014     Incumbent  \n",
              "5                       87.0  07/2014     Incumbent  \n",
              "6                       87.0  07/2014     Incumbent  \n",
              "7                       86.0  07/2014     Incumbent  \n",
              "8                       86.0  07/2014     Incumbent  \n",
              "9                       87.0  07/2014     Incumbent  \n",
              "10                      87.0  07/2014     Incumbent  \n",
              "11                      87.0  07/2014     Incumbent  \n",
              "12                      87.0  07/2014     Incumbent  \n",
              "13                      87.0  07/2014     Incumbent  \n",
              "14                      87.0  07/2014     Incumbent  \n",
              "15                      86.0  07/2014     Incumbent  \n",
              "16                      87.0  07/2014     Incumbent  \n",
              "17                      87.0  07/2014     Incumbent  \n",
              "18                      87.0  07/2014     Incumbent  \n",
              "19                      87.0  07/2014     Incumbent  \n",
              "20                      87.0  07/2014     Incumbent  \n",
              "21                      87.0  07/2014     Incumbent  \n",
              "22                      87.0  07/2014     Incumbent  \n",
              "23                      87.0  07/2014     Incumbent  \n",
              "24                      87.0  07/2014     Incumbent  \n",
              "25                      86.0  07/2014     Incumbent  \n",
              "26                      87.0  07/2014     Incumbent  \n",
              "27                      86.0  07/2014     Incumbent  \n",
              "28                      86.0  07/2014     Incumbent  \n",
              "29                      87.0  07/2014     Incumbent  \n",
              "...                      ...      ...           ...  \n",
              "19582                  113.0  12/2004     Incumbent  \n",
              "19583                  110.0  12/2004     Incumbent  \n",
              "19584                  112.0  12/2004     Incumbent  \n",
              "19585                  110.0  12/2004     Incumbent  \n",
              "19586                  113.0  12/2004     Incumbent  \n",
              "19587                  110.0  12/2004     Incumbent  \n",
              "19588                  113.0  12/2004     Incumbent  \n",
              "19589                  109.0  12/2004     Incumbent  \n",
              "19590                  111.0  12/2004     Incumbent  \n",
              "19591                  113.0  12/2004     Incumbent  \n",
              "19592                  114.0  12/2004     Incumbent  \n",
              "19593                   65.0  12/2006     Incumbent  \n",
              "19594                   64.0  12/2006     Incumbent  \n",
              "19595                   67.0  12/2006     Incumbent  \n",
              "19596                   66.0  12/2006     Incumbent  \n",
              "19597                   67.0  12/2006     Incumbent  \n",
              "19598                   65.0  12/2006     Incumbent  \n",
              "19599                   67.0  12/2006     Incumbent  \n",
              "19600                   66.0  12/2006     Incumbent  \n",
              "19601                   66.0  12/2006     Incumbent  \n",
              "19602                   65.0  12/2006     Incumbent  \n",
              "19603                   65.0  12/2006     Incumbent  \n",
              "19604                   66.0  12/2006     Incumbent  \n",
              "19605                   66.0  12/2006     Incumbent  \n",
              "19606                   65.0  12/2006     Incumbent  \n",
              "19607                   66.0  12/2006     Incumbent  \n",
              "19608                   66.0  12/2006     Incumbent  \n",
              "19609                   64.0  12/2006     Incumbent  \n",
              "19610                   67.0  12/2006     Incumbent  \n",
              "19611                   67.0  12/2006     Incumbent  \n",
              "\n",
              "[19612 rows x 8 columns]>"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 91
        }
      ]
    },
    {
      "metadata": {
        "id": "L7v9UWlH8-Ep",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        ""
      ],
      "execution_count": 0,
      "outputs": []
    }
  ]
}