Copy_of_reilAutomation.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "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": [
        "[View in Colaboratory](https://colab.research.google.com/github/mitreilab/autonomousfuture/blob/master/Copy_of_reilAutomation.ipynb)"
      ]
    },
    {
      "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",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 36
        },
        "outputId": "0951d0e7-d9e5-4518-d3dc-46a241f0be69"
      },
      "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": 78,
      "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": 5,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "113111"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 5
        }
      ]
    },
    {
      "metadata": {
        "id": "Y1Edy573ZtoB",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 36
        },
        "outputId": "978e9989-8219-42e0-ed8e-ec6369a96759"
      },
      "cell_type": "code",
      "source": [
        "tobecorrect=old['occ_code'].tolist()\n",
        "len(tobecorrect)"
      ],
      "execution_count": 6,
      "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",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 36
        },
        "outputId": "f24e8ed1-1ccc-4881-b510-5e6f91f465d0"
      },
      "cell_type": "code",
      "source": [
        "old.shape"
      ],
      "execution_count": 11,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "(7166, 26)"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 11
        }
      ]
    },
    {
      "metadata": {
        "id": "9eudO3HIeZCT",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 36
        },
        "outputId": "390f2430-43e2-4dd1-a484-f480a4e8f831"
      },
      "cell_type": "code",
      "source": [
        "new.shape"
      ],
      "execution_count": 12,
      "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",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 36
        },
        "outputId": "0c8f5ebb-915e-4bc2-e653-7056ed83150c"
      },
      "cell_type": "code",
      "source": [
        "len(newlist)"
      ],
      "execution_count": 14,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "792"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 14
        }
      ]
    },
    {
      "metadata": {
        "id": "hWfYzmWqgklT",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 36
        },
        "outputId": "650853a6-25ac-42d3-b360-40d7369690c3"
      },
      "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": 15,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "(21264, 26)"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 15
        }
      ]
    },
    {
      "metadata": {
        "id": "y-TphPoLgjvi",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 36
        },
        "outputId": "367789f1-6208-4ca9-c2ea-c1d62f58a426"
      },
      "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": 18,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "528"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 18
        }
      ]
    },
    {
      "metadata": {
        "id": "eGKLo1f7gjny",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 533
        },
        "outputId": "21e0f5cd-a55a-4e21-e8e0-7e35d551b3e6"
      },
      "cell_type": "code",
      "source": [
        "df.head()"
      ],
      "execution_count": 30,
      "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",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 129
        },
        "outputId": "29b4f757-1af3-4130-93a7-4acc5386a276"
      },
      "cell_type": "code",
      "source": [
        "df.columns"
      ],
      "execution_count": 21,
      "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",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 36
        },
        "outputId": "a1fc7905-b149-4787-f1bc-90ecc623036a"
      },
      "cell_type": "code",
      "source": [
        "row=df.iloc[0]\n",
        "type(row[['year','naics']])\n"
      ],
      "execution_count": 72,
      "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",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 36
        },
        "outputId": "1783abfc-8d98-4d1c-a0b1-8c565d1f9680"
      },
      "cell_type": "code",
      "source": [
        "len(listofocc)"
      ],
      "execution_count": 65,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "21264"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 65
        }
      ]
    },
    {
      "metadata": {
        "id": "qEMT7tLKy9WD",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 36
        },
        "outputId": "0c40f7fa-d067-44be-ead1-76f26ef5db47"
      },
      "cell_type": "code",
      "source": [
        "len(all_occ)"
      ],
      "execution_count": 69,
      "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",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 74
        },
        "outputId": "dd4ea3c7-014a-4411-ae0a-442cb0e66e6b"
      },
      "cell_type": "code",
      "source": [
        "# tasklist[2]"
      ],
      "execution_count": 101,
      "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": [
        "# # 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",
        "    temp=Tasks[Tasks['occ_code']==occ_code][['Task ID','Task']]\n",
        "    return temp"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "XU2JdLvnCzsL",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 111
        },
        "outputId": "a23200bd-f11c-4f9d-fa6a-2e2edcfe31a7"
      },
      "cell_type": "code",
      "source": [
        "Activities.columns"
      ],
      "execution_count": 103,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "Index(['occ_code', 'Title', 'Element ID', 'Element Name', 'Scale ID',\n",
              "       'Scale Name', 'Data Value', 'N', 'Standard Error', 'Lower CI Bound',\n",
              "       'Upper CI Bound', 'Recommend Suppress', 'Not Relevant', 'Date',\n",
              "       'Domain Source'],\n",
              "      dtype='object')"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 103
        }
      ]
    },
    {
      "metadata": {
        "id": "lWWnnE8C_qwr",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# # We first define some functions that we need to search values from Onet\n",
        "# # The function to search for work activities and the scores for an occpation:\n",
        "def getactivities(occ_code,Activities):\n",
        "    temp=Activities[Activities['occ_code']==occ_code][['Element ID', 'Element Name', 'Scale ID','Data Value', 'N']]\n",
        "    return temp"
      ],
      "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": [
        "keys = all_occ\n",
        "\n",
        "values =[]"
      ],
      "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'],Tasks)\n",
        "  tempactivities=getactivities(row['occ_code'],Activities)\n",
        "  templist=[temp,temptask,tempactivities]\n",
        "  values.append(templist)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "flA8YSvuMD4P",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 582
        },
        "outputId": "739b9777-9ff5-4e0b-f5fd-b4872f905888"
      },
      "cell_type": "code",
      "source": [
        "len(values)\n",
        "values[1][1]"
      ],
      "execution_count": 120,
      "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>Task ID</th>\n",
              "      <th>Task</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>49</th>\n",
              "      <td>20699</td>\n",
              "      <td>Review financial statements, sales or activity...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>50</th>\n",
              "      <td>933</td>\n",
              "      <td>Direct and coordinate activities of businesses...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>51</th>\n",
              "      <td>20700</td>\n",
              "      <td>Direct administrative activities directly rela...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>52</th>\n",
              "      <td>20701</td>\n",
              "      <td>Prepare staff work schedules and assign specif...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>53</th>\n",
              "      <td>20702</td>\n",
              "      <td>Monitor suppliers to ensure that they efficien...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>54</th>\n",
              "      <td>20703</td>\n",
              "      <td>Direct or coordinate financial or budget activ...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>55</th>\n",
              "      <td>20704</td>\n",
              "      <td>Establish or implement departmental policies, ...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>56</th>\n",
              "      <td>20705</td>\n",
              "      <td>Perform personnel functions such as selection,...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>57</th>\n",
              "      <td>20706</td>\n",
              "      <td>Plan or direct activities such as sales promot...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>58</th>\n",
              "      <td>20707</td>\n",
              "      <td>Set prices or credit terms for goods or servic...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>59</th>\n",
              "      <td>20708</td>\n",
              "      <td>Manage the movement of goods into and out of p...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>60</th>\n",
              "      <td>944</td>\n",
              "      <td>Perform sales floor work, such as greeting or ...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>61</th>\n",
              "      <td>945</td>\n",
              "      <td>Develop or implement product-marketing strateg...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>62</th>\n",
              "      <td>948</td>\n",
              "      <td>Recommend locations for new facilities or over...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>63</th>\n",
              "      <td>19501</td>\n",
              "      <td>Implement or oversee environmental management ...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>64</th>\n",
              "      <td>947</td>\n",
              "      <td>Direct non-merchandising departments of busine...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>65</th>\n",
              "      <td>949</td>\n",
              "      <td>Plan store layouts or design displays.</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "    Task ID                                               Task\n",
              "49    20699  Review financial statements, sales or activity...\n",
              "50      933  Direct and coordinate activities of businesses...\n",
              "51    20700  Direct administrative activities directly rela...\n",
              "52    20701  Prepare staff work schedules and assign specif...\n",
              "53    20702  Monitor suppliers to ensure that they efficien...\n",
              "54    20703  Direct or coordinate financial or budget activ...\n",
              "55    20704  Establish or implement departmental policies, ...\n",
              "56    20705  Perform personnel functions such as selection,...\n",
              "57    20706  Plan or direct activities such as sales promot...\n",
              "58    20707  Set prices or credit terms for goods or servic...\n",
              "59    20708  Manage the movement of goods into and out of p...\n",
              "60      944  Perform sales floor work, such as greeting or ...\n",
              "61      945  Develop or implement product-marketing strateg...\n",
              "62      948  Recommend locations for new facilities or over...\n",
              "63    19501  Implement or oversee environmental management ...\n",
              "64      947  Direct non-merchandising departments of busine...\n",
              "65      949             Plan store layouts or design displays."
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 120
        }
      ]
    },
    {
      "metadata": {
        "id": "aSdHKeASDdP0",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "tempactivities=getactivities(row['occ_code'],Activities)\n",
        "tempactivities"
      ],
      "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",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "manual[all_occ[1]][2]\n"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "7gvAuYJHNA8G",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "keys_new=listofocc\n",
        "keys_new"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "cqbnbP2cM5jS",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "manual2= dict(zip(keys_new, values))"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "2C6TTXJn4E5J",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        ""
      ],
      "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",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        ""
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "L7v9UWlH8-Ep",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        ""
      ],
      "execution_count": 0,
      "outputs": []
    }
  ]
}