bls_df_cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 119,
   "id": "0d370d45-079d-43c8-bf37-f8402c4bbbd0",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import plotly as pl\n",
    "import seaborn as sns\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "id": "ace442a8-c97d-4fc4-91ff-984f62c3b95c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Organizations involved</th>\n",
       "      <th>States</th>\n",
       "      <th>Areas</th>\n",
       "      <th>Ownership</th>\n",
       "      <th>Industry code[1]</th>\n",
       "      <th>Union</th>\n",
       "      <th>Union acronym</th>\n",
       "      <th>Union Local</th>\n",
       "      <th>Bargaining unit</th>\n",
       "      <th>Work stoppage beginning date</th>\n",
       "      <th>Work stoppage ending date</th>\n",
       "      <th>Number of workers[2]</th>\n",
       "      <th>Days idle, cumulative for this work stoppage[3]</th>\n",
       "      <th>Note</th>\n",
       "      <th>NAICS_col</th>\n",
       "      <th>start_year</th>\n",
       "      <th>end_year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Marine Towing and Transportation Employers' As...</td>\n",
       "      <td>NY</td>\n",
       "      <td>New York</td>\n",
       "      <td>Private industry</td>\n",
       "      <td>488330</td>\n",
       "      <td>International Longshoremen's Association</td>\n",
       "      <td>ILA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1988-02-16</td>\n",
       "      <td>1993-12-20</td>\n",
       "      <td>2500.0</td>\n",
       "      <td>2879500.0</td>\n",
       "      <td>The number of workers decreased to 2,000 in De...</td>\n",
       "      <td>48</td>\n",
       "      <td>1988.0</td>\n",
       "      <td>1993.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Boeing Company</td>\n",
       "      <td>WA</td>\n",
       "      <td>Seattle</td>\n",
       "      <td>Private industry</td>\n",
       "      <td>336411</td>\n",
       "      <td>Seattle Professional Engineering Employees Ass...</td>\n",
       "      <td>SPEEA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1993-01-19</td>\n",
       "      <td>1993-01-19</td>\n",
       "      <td>21000.0</td>\n",
       "      <td>21000.0</td>\n",
       "      <td>The union name was changed to the Society of P...</td>\n",
       "      <td>33</td>\n",
       "      <td>1993.0</td>\n",
       "      <td>1993.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Boston Gas Company</td>\n",
       "      <td>MA</td>\n",
       "      <td>Boston</td>\n",
       "      <td>Private industry</td>\n",
       "      <td>22121</td>\n",
       "      <td>United Steelworkers</td>\n",
       "      <td>USW</td>\n",
       "      <td>12003</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1993-01-24</td>\n",
       "      <td>1993-05-20</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>83000.0</td>\n",
       "      <td>Boston Gas Company also does business as \"Nati...</td>\n",
       "      <td>22</td>\n",
       "      <td>1993.0</td>\n",
       "      <td>1993.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Bituminous Coal Operators Association</td>\n",
       "      <td>IN, IL, KY, WV</td>\n",
       "      <td>Multiple states</td>\n",
       "      <td>Private industry</td>\n",
       "      <td>212112</td>\n",
       "      <td>United Mine Workers of America</td>\n",
       "      <td>UMWA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1993-02-02</td>\n",
       "      <td>1993-03-02</td>\n",
       "      <td>6700.0</td>\n",
       "      <td>103400.0</td>\n",
       "      <td>Number of workers involved increased from 5,00...</td>\n",
       "      <td>21</td>\n",
       "      <td>1993.0</td>\n",
       "      <td>1993.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Douglas Aircraft</td>\n",
       "      <td>CA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Private industry</td>\n",
       "      <td>336411</td>\n",
       "      <td>International Association of Machinists</td>\n",
       "      <td>IAM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1993-03-02</td>\n",
       "      <td>1993-03-03</td>\n",
       "      <td>6800.0</td>\n",
       "      <td>13600.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>33</td>\n",
       "      <td>1993.0</td>\n",
       "      <td>1993.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              Organizations involved          States  \\\n",
       "0  Marine Towing and Transportation Employers' As...              NY   \n",
       "1                                     Boeing Company              WA   \n",
       "2                                 Boston Gas Company              MA   \n",
       "3              Bituminous Coal Operators Association  IN, IL, KY, WV   \n",
       "4                                   Douglas Aircraft              CA   \n",
       "\n",
       "             Areas         Ownership Industry code[1]  \\\n",
       "0         New York  Private industry           488330   \n",
       "1          Seattle  Private industry           336411   \n",
       "2           Boston  Private industry            22121   \n",
       "3  Multiple states  Private industry           212112   \n",
       "4              NaN  Private industry           336411   \n",
       "\n",
       "                                               Union Union acronym  \\\n",
       "0           International Longshoremen's Association           ILA   \n",
       "1  Seattle Professional Engineering Employees Ass...         SPEEA   \n",
       "2                                United Steelworkers           USW   \n",
       "3                     United Mine Workers of America          UMWA   \n",
       "4            International Association of Machinists           IAM   \n",
       "\n",
       "  Union Local Bargaining unit Work stoppage beginning date  \\\n",
       "0         NaN             NaN                   1988-02-16   \n",
       "1         NaN             NaN                   1993-01-19   \n",
       "2       12003             NaN                   1993-01-24   \n",
       "3         NaN             NaN                   1993-02-02   \n",
       "4         NaN             NaN                   1993-03-02   \n",
       "\n",
       "  Work stoppage ending date  Number of workers[2]  \\\n",
       "0                1993-12-20                2500.0   \n",
       "1                1993-01-19               21000.0   \n",
       "2                1993-05-20                1000.0   \n",
       "3                1993-03-02                6700.0   \n",
       "4                1993-03-03                6800.0   \n",
       "\n",
       "   Days idle, cumulative for this work stoppage[3]  \\\n",
       "0                                        2879500.0   \n",
       "1                                          21000.0   \n",
       "2                                          83000.0   \n",
       "3                                         103400.0   \n",
       "4                                          13600.0   \n",
       "\n",
       "                                                Note NAICS_col  start_year  \\\n",
       "0  The number of workers decreased to 2,000 in De...        48      1988.0   \n",
       "1  The union name was changed to the Society of P...        33      1993.0   \n",
       "2  Boston Gas Company also does business as \"Nati...        22      1993.0   \n",
       "3  Number of workers involved increased from 5,00...        21      1993.0   \n",
       "4                                                NaN        33      1993.0   \n",
       "\n",
       "   end_year  \n",
       "0    1993.0  \n",
       "1    1993.0  \n",
       "2    1993.0  \n",
       "3    1993.0  \n",
       "4    1993.0  "
      ]
     },
     "execution_count": 120,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bls_df = pd.read_excel(\"monthly-listing-3.xlsx\",header=1)\n",
    "bls_df[\"NAICS_col\"] = bls_df[\"Industry code[1]\"].astype(str).str[:2]\n",
    "bls_df[\"start_year\"] = pd.DatetimeIndex(bls_df[\"Work stoppage beginning date\"]).year\n",
    "bls_df[\"end_year\"] = pd.DatetimeIndex(bls_df[\"Work stoppage ending date\"]).year\n",
    "bls_df = bls_df[bls_df['end_year'] >= 1993]\n",
    "bls_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "id": "5d917d6a-5d34-43e1-998a-309b6f27012f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Organizations involved</th>\n",
       "      <th>States</th>\n",
       "      <th>Areas</th>\n",
       "      <th>Ownership</th>\n",
       "      <th>Industry code[1]</th>\n",
       "      <th>Union</th>\n",
       "      <th>Union acronym</th>\n",
       "      <th>Union Local</th>\n",
       "      <th>Bargaining unit</th>\n",
       "      <th>Work stoppage beginning date</th>\n",
       "      <th>Work stoppage ending date</th>\n",
       "      <th>Number of workers[2]</th>\n",
       "      <th>Days idle, cumulative for this work stoppage[3]</th>\n",
       "      <th>Note</th>\n",
       "      <th>NAICS_col</th>\n",
       "      <th>start_year</th>\n",
       "      <th>end_year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Marine Towing and Transportation Employers' As...</td>\n",
       "      <td>NY</td>\n",
       "      <td>New York</td>\n",
       "      <td>Private industry</td>\n",
       "      <td>488330</td>\n",
       "      <td>International Longshoremen's Association</td>\n",
       "      <td>ILA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1988-02-16</td>\n",
       "      <td>1993-12-20</td>\n",
       "      <td>2500.0</td>\n",
       "      <td>2879500.0</td>\n",
       "      <td>The number of workers decreased to 2,000 in De...</td>\n",
       "      <td>48</td>\n",
       "      <td>1988.0</td>\n",
       "      <td>1993.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Boeing Company</td>\n",
       "      <td>WA</td>\n",
       "      <td>Seattle</td>\n",
       "      <td>Private industry</td>\n",
       "      <td>336411</td>\n",
       "      <td>Seattle Professional Engineering Employees Ass...</td>\n",
       "      <td>SPEEA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1993-01-19</td>\n",
       "      <td>1993-01-19</td>\n",
       "      <td>21000.0</td>\n",
       "      <td>21000.0</td>\n",
       "      <td>The union name was changed to the Society of P...</td>\n",
       "      <td>33</td>\n",
       "      <td>1993.0</td>\n",
       "      <td>1993.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Boston Gas Company</td>\n",
       "      <td>MA</td>\n",
       "      <td>Boston</td>\n",
       "      <td>Private industry</td>\n",
       "      <td>22121</td>\n",
       "      <td>United Steelworkers</td>\n",
       "      <td>USW</td>\n",
       "      <td>12003</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1993-01-24</td>\n",
       "      <td>1993-05-20</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>83000.0</td>\n",
       "      <td>Boston Gas Company also does business as \"Nati...</td>\n",
       "      <td>22</td>\n",
       "      <td>1993.0</td>\n",
       "      <td>1993.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Bituminous Coal Operators Association</td>\n",
       "      <td>IN, IL, KY, WV</td>\n",
       "      <td>Multiple states</td>\n",
       "      <td>Private industry</td>\n",
       "      <td>212112</td>\n",
       "      <td>United Mine Workers of America</td>\n",
       "      <td>UMWA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1993-02-02</td>\n",
       "      <td>1993-03-02</td>\n",
       "      <td>6700.0</td>\n",
       "      <td>103400.0</td>\n",
       "      <td>Number of workers involved increased from 5,00...</td>\n",
       "      <td>21</td>\n",
       "      <td>1993.0</td>\n",
       "      <td>1993.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Douglas Aircraft</td>\n",
       "      <td>CA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Private industry</td>\n",
       "      <td>336411</td>\n",
       "      <td>International Association of Machinists</td>\n",
       "      <td>IAM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1993-03-02</td>\n",
       "      <td>1993-03-03</td>\n",
       "      <td>6800.0</td>\n",
       "      <td>13600.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>33</td>\n",
       "      <td>1993.0</td>\n",
       "      <td>1993.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>624</th>\n",
       "      <td>Sutter Health Hospitals</td>\n",
       "      <td>CA</td>\n",
       "      <td>Berkeley</td>\n",
       "      <td>Private industry</td>\n",
       "      <td>622110</td>\n",
       "      <td>California Nurses Association/National Nurses ...</td>\n",
       "      <td>CNA/NNU</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-10-24</td>\n",
       "      <td>2022-10-29</td>\n",
       "      <td>1800.0</td>\n",
       "      <td>9000.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>62</td>\n",
       "      <td>2022.0</td>\n",
       "      <td>2022.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>625</th>\n",
       "      <td>University of California</td>\n",
       "      <td>CA</td>\n",
       "      <td>Statewide</td>\n",
       "      <td>State government</td>\n",
       "      <td>61131</td>\n",
       "      <td>United Auto Workers</td>\n",
       "      <td>UAW</td>\n",
       "      <td>Local 2865, 5810</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-11-14</td>\n",
       "      <td>2022-12-23</td>\n",
       "      <td>48000.0</td>\n",
       "      <td>1272000.0</td>\n",
       "      <td>48,000 workers from 11/14-12/11. Number droppe...</td>\n",
       "      <td>61</td>\n",
       "      <td>2022.0</td>\n",
       "      <td>2022.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>626</th>\n",
       "      <td>The New School</td>\n",
       "      <td>NY</td>\n",
       "      <td>NY</td>\n",
       "      <td>Private industry</td>\n",
       "      <td>61131</td>\n",
       "      <td>Academics Come Together - United Auto Workers</td>\n",
       "      <td>ACT-UAW</td>\n",
       "      <td>Local 7902</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-11-16</td>\n",
       "      <td>2022-12-10</td>\n",
       "      <td>1700.0</td>\n",
       "      <td>28900.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>61</td>\n",
       "      <td>2022.0</td>\n",
       "      <td>2022.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>627</th>\n",
       "      <td>New York Times</td>\n",
       "      <td>NY</td>\n",
       "      <td>NY</td>\n",
       "      <td>Private industry</td>\n",
       "      <td>511110</td>\n",
       "      <td>The NewsGuild</td>\n",
       "      <td>TNG-CWA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-12-08</td>\n",
       "      <td>2022-12-08</td>\n",
       "      <td>1100.0</td>\n",
       "      <td>1100.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>51</td>\n",
       "      <td>2022.0</td>\n",
       "      <td>2022.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>628</th>\n",
       "      <td>Alta Bates Summit</td>\n",
       "      <td>CA</td>\n",
       "      <td>Oakland and Berkeley</td>\n",
       "      <td>Private industry</td>\n",
       "      <td>622110</td>\n",
       "      <td>California Nurses Association/National Nurses ...</td>\n",
       "      <td>NNU</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-12-24</td>\n",
       "      <td>2023-01-01</td>\n",
       "      <td>1800.0</td>\n",
       "      <td>7200.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>62</td>\n",
       "      <td>2022.0</td>\n",
       "      <td>2023.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>628 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                Organizations involved          States  \\\n",
       "0    Marine Towing and Transportation Employers' As...              NY   \n",
       "1                                       Boeing Company              WA   \n",
       "2                                   Boston Gas Company              MA   \n",
       "3                Bituminous Coal Operators Association  IN, IL, KY, WV   \n",
       "4                                     Douglas Aircraft              CA   \n",
       "..                                                 ...             ...   \n",
       "624                            Sutter Health Hospitals              CA   \n",
       "625                           University of California              CA   \n",
       "626                                     The New School              NY   \n",
       "627                                     New York Times              NY   \n",
       "628                                  Alta Bates Summit              CA   \n",
       "\n",
       "                    Areas         Ownership Industry code[1]  \\\n",
       "0                New York  Private industry           488330   \n",
       "1                 Seattle  Private industry           336411   \n",
       "2                  Boston  Private industry            22121   \n",
       "3         Multiple states  Private industry           212112   \n",
       "4                     NaN  Private industry           336411   \n",
       "..                    ...               ...              ...   \n",
       "624              Berkeley  Private industry           622110   \n",
       "625             Statewide  State government            61131   \n",
       "626                    NY  Private industry            61131   \n",
       "627                    NY  Private industry           511110   \n",
       "628  Oakland and Berkeley  Private industry           622110   \n",
       "\n",
       "                                                 Union Union acronym  \\\n",
       "0             International Longshoremen's Association           ILA   \n",
       "1    Seattle Professional Engineering Employees Ass...         SPEEA   \n",
       "2                                  United Steelworkers           USW   \n",
       "3                       United Mine Workers of America          UMWA   \n",
       "4              International Association of Machinists           IAM   \n",
       "..                                                 ...           ...   \n",
       "624  California Nurses Association/National Nurses ...       CNA/NNU   \n",
       "625                                United Auto Workers           UAW   \n",
       "626      Academics Come Together - United Auto Workers       ACT-UAW   \n",
       "627                                      The NewsGuild       TNG-CWA   \n",
       "628  California Nurses Association/National Nurses ...           NNU   \n",
       "\n",
       "          Union Local Bargaining unit Work stoppage beginning date  \\\n",
       "0                 NaN             NaN                   1988-02-16   \n",
       "1                 NaN             NaN                   1993-01-19   \n",
       "2               12003             NaN                   1993-01-24   \n",
       "3                 NaN             NaN                   1993-02-02   \n",
       "4                 NaN             NaN                   1993-03-02   \n",
       "..                ...             ...                          ...   \n",
       "624               NaN             NaN                   2022-10-24   \n",
       "625  Local 2865, 5810             NaN                   2022-11-14   \n",
       "626        Local 7902             NaN                   2022-11-16   \n",
       "627               NaN             NaN                   2022-12-08   \n",
       "628               NaN             NaN                   2022-12-24   \n",
       "\n",
       "    Work stoppage ending date  Number of workers[2]  \\\n",
       "0                  1993-12-20                2500.0   \n",
       "1                  1993-01-19               21000.0   \n",
       "2                  1993-05-20                1000.0   \n",
       "3                  1993-03-02                6700.0   \n",
       "4                  1993-03-03                6800.0   \n",
       "..                        ...                   ...   \n",
       "624                2022-10-29                1800.0   \n",
       "625                2022-12-23               48000.0   \n",
       "626                2022-12-10                1700.0   \n",
       "627                2022-12-08                1100.0   \n",
       "628                2023-01-01                1800.0   \n",
       "\n",
       "     Days idle, cumulative for this work stoppage[3]  \\\n",
       "0                                          2879500.0   \n",
       "1                                            21000.0   \n",
       "2                                            83000.0   \n",
       "3                                           103400.0   \n",
       "4                                            13600.0   \n",
       "..                                               ...   \n",
       "624                                           9000.0   \n",
       "625                                        1272000.0   \n",
       "626                                          28900.0   \n",
       "627                                           1100.0   \n",
       "628                                           7200.0   \n",
       "\n",
       "                                                  Note NAICS_col  start_year  \\\n",
       "0    The number of workers decreased to 2,000 in De...        48      1988.0   \n",
       "1    The union name was changed to the Society of P...        33      1993.0   \n",
       "2    Boston Gas Company also does business as \"Nati...        22      1993.0   \n",
       "3    Number of workers involved increased from 5,00...        21      1993.0   \n",
       "4                                                  NaN        33      1993.0   \n",
       "..                                                 ...       ...         ...   \n",
       "624                                                NaN        62      2022.0   \n",
       "625  48,000 workers from 11/14-12/11. Number droppe...        61      2022.0   \n",
       "626                                                NaN        61      2022.0   \n",
       "627                                                NaN        51      2022.0   \n",
       "628                                                NaN        62      2022.0   \n",
       "\n",
       "     end_year  \n",
       "0      1993.0  \n",
       "1      1993.0  \n",
       "2      1993.0  \n",
       "3      1993.0  \n",
       "4      1993.0  \n",
       "..        ...  \n",
       "624    2022.0  \n",
       "625    2022.0  \n",
       "626    2022.0  \n",
       "627    2022.0  \n",
       "628    2023.0  \n",
       "\n",
       "[628 rows x 17 columns]"
      ]
     },
     "execution_count": 121,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bls_df_subset = bls_df[~bls_df['States'].isna()]\n",
    "#bls_df_subset = bls_df_subset[~bls_df_subset['States'].str.contains(\",\")]\n",
    "bls_df_subset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "id": "4333622b-1213-4731-b378-a375ac504e25",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['IN', ' IL', ' KY', ' WV']\n"
     ]
    },
    {
     "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>Organizations involved</th>\n",
       "      <th>States</th>\n",
       "      <th>Areas</th>\n",
       "      <th>Ownership</th>\n",
       "      <th>Industry code[1]</th>\n",
       "      <th>Union</th>\n",
       "      <th>Union acronym</th>\n",
       "      <th>Union Local</th>\n",
       "      <th>Bargaining unit</th>\n",
       "      <th>Work stoppage beginning date</th>\n",
       "      <th>Work stoppage ending date</th>\n",
       "      <th>Number of workers[2]</th>\n",
       "      <th>Days idle, cumulative for this work stoppage[3]</th>\n",
       "      <th>Note</th>\n",
       "      <th>NAICS_col</th>\n",
       "      <th>start_year</th>\n",
       "      <th>end_year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Marine Towing and Transportation Employers' As...</td>\n",
       "      <td>NY</td>\n",
       "      <td>New York</td>\n",
       "      <td>Private industry</td>\n",
       "      <td>488330</td>\n",
       "      <td>International Longshoremen's Association</td>\n",
       "      <td>ILA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1988-02-16</td>\n",
       "      <td>1993-12-20</td>\n",
       "      <td>2500.0</td>\n",
       "      <td>2879500.0</td>\n",
       "      <td>The number of workers decreased to 2,000 in De...</td>\n",
       "      <td>48</td>\n",
       "      <td>1988.0</td>\n",
       "      <td>1993.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Boeing Company</td>\n",
       "      <td>WA</td>\n",
       "      <td>Seattle</td>\n",
       "      <td>Private industry</td>\n",
       "      <td>336411</td>\n",
       "      <td>Seattle Professional Engineering Employees Ass...</td>\n",
       "      <td>SPEEA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1993-01-19</td>\n",
       "      <td>1993-01-19</td>\n",
       "      <td>21000.0</td>\n",
       "      <td>21000.0</td>\n",
       "      <td>The union name was changed to the Society of P...</td>\n",
       "      <td>33</td>\n",
       "      <td>1993.0</td>\n",
       "      <td>1993.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Boston Gas Company</td>\n",
       "      <td>MA</td>\n",
       "      <td>Boston</td>\n",
       "      <td>Private industry</td>\n",
       "      <td>22121</td>\n",
       "      <td>United Steelworkers</td>\n",
       "      <td>USW</td>\n",
       "      <td>12003</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1993-01-24</td>\n",
       "      <td>1993-05-20</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>83000.0</td>\n",
       "      <td>Boston Gas Company also does business as \"Nati...</td>\n",
       "      <td>22</td>\n",
       "      <td>1993.0</td>\n",
       "      <td>1993.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Bituminous Coal Operators Association</td>\n",
       "      <td>IN</td>\n",
       "      <td>Multiple states</td>\n",
       "      <td>Private industry</td>\n",
       "      <td>212112</td>\n",
       "      <td>United Mine Workers of America</td>\n",
       "      <td>UMWA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1993-02-02</td>\n",
       "      <td>1993-03-02</td>\n",
       "      <td>6700.0</td>\n",
       "      <td>103400.0</td>\n",
       "      <td>Number of workers involved increased from 5,00...</td>\n",
       "      <td>21</td>\n",
       "      <td>1993.0</td>\n",
       "      <td>1993.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Douglas Aircraft</td>\n",
       "      <td>CA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Private industry</td>\n",
       "      <td>336411</td>\n",
       "      <td>International Association of Machinists</td>\n",
       "      <td>IAM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1993-03-02</td>\n",
       "      <td>1993-03-03</td>\n",
       "      <td>6800.0</td>\n",
       "      <td>13600.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>33</td>\n",
       "      <td>1993.0</td>\n",
       "      <td>1993.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>871</th>\n",
       "      <td>John Deere</td>\n",
       "      <td>IL</td>\n",
       "      <td>Multiple</td>\n",
       "      <td>Private industry</td>\n",
       "      <td>333112</td>\n",
       "      <td>United Auto Workers</td>\n",
       "      <td>UAW</td>\n",
       "      <td>Local 74</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-10-14</td>\n",
       "      <td>2021-11-17</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>240000.0</td>\n",
       "      <td>The work stoppage involved employees at 14 dif...</td>\n",
       "      <td>33</td>\n",
       "      <td>2021.0</td>\n",
       "      <td>2021.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>872</th>\n",
       "      <td>John Deere</td>\n",
       "      <td>IA</td>\n",
       "      <td>Multiple</td>\n",
       "      <td>Private industry</td>\n",
       "      <td>333112</td>\n",
       "      <td>United Auto Workers</td>\n",
       "      <td>UAW</td>\n",
       "      <td>Local 74</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-10-14</td>\n",
       "      <td>2021-11-17</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>240000.0</td>\n",
       "      <td>The work stoppage involved employees at 14 dif...</td>\n",
       "      <td>33</td>\n",
       "      <td>2021.0</td>\n",
       "      <td>2021.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>873</th>\n",
       "      <td>John Deere</td>\n",
       "      <td>KS</td>\n",
       "      <td>Multiple</td>\n",
       "      <td>Private industry</td>\n",
       "      <td>333112</td>\n",
       "      <td>United Auto Workers</td>\n",
       "      <td>UAW</td>\n",
       "      <td>Local 74</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-10-14</td>\n",
       "      <td>2021-11-17</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>240000.0</td>\n",
       "      <td>The work stoppage involved employees at 14 dif...</td>\n",
       "      <td>33</td>\n",
       "      <td>2021.0</td>\n",
       "      <td>2021.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>874</th>\n",
       "      <td>CNH Industrial</td>\n",
       "      <td>WI</td>\n",
       "      <td>Burlington, IA; Racine, WI</td>\n",
       "      <td>Private industry</td>\n",
       "      <td>33311, 33312</td>\n",
       "      <td>United Auto Workers</td>\n",
       "      <td>UAW</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-05-02</td>\n",
       "      <td>2023-01-21</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>180000.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>33</td>\n",
       "      <td>2022.0</td>\n",
       "      <td>2023.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>875</th>\n",
       "      <td>Weyerhaeuser Company</td>\n",
       "      <td>WA</td>\n",
       "      <td>Multiple</td>\n",
       "      <td>Private industry</td>\n",
       "      <td>113110</td>\n",
       "      <td>International Association of Machinists and Ae...</td>\n",
       "      <td>IAM</td>\n",
       "      <td>Local Lodge W246</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-09-13</td>\n",
       "      <td>2022-10-28</td>\n",
       "      <td>1200.0</td>\n",
       "      <td>39600.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11</td>\n",
       "      <td>2022.0</td>\n",
       "      <td>2022.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>875 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                Organizations involved States  \\\n",
       "0    Marine Towing and Transportation Employers' As...     NY   \n",
       "1                                       Boeing Company     WA   \n",
       "2                                   Boston Gas Company     MA   \n",
       "3                Bituminous Coal Operators Association     IN   \n",
       "4                                     Douglas Aircraft     CA   \n",
       "..                                                 ...    ...   \n",
       "871                                         John Deere     IL   \n",
       "872                                         John Deere     IA   \n",
       "873                                         John Deere     KS   \n",
       "874                                     CNH Industrial     WI   \n",
       "875                               Weyerhaeuser Company     WA   \n",
       "\n",
       "                          Areas         Ownership Industry code[1]  \\\n",
       "0                      New York  Private industry           488330   \n",
       "1                       Seattle  Private industry           336411   \n",
       "2                        Boston  Private industry            22121   \n",
       "3               Multiple states  Private industry           212112   \n",
       "4                           NaN  Private industry           336411   \n",
       "..                          ...               ...              ...   \n",
       "871                    Multiple  Private industry           333112   \n",
       "872                    Multiple  Private industry           333112   \n",
       "873                    Multiple  Private industry           333112   \n",
       "874  Burlington, IA; Racine, WI  Private industry     33311, 33312   \n",
       "875                    Multiple  Private industry          113110    \n",
       "\n",
       "                                                 Union Union acronym  \\\n",
       "0             International Longshoremen's Association           ILA   \n",
       "1    Seattle Professional Engineering Employees Ass...         SPEEA   \n",
       "2                                  United Steelworkers           USW   \n",
       "3                       United Mine Workers of America          UMWA   \n",
       "4              International Association of Machinists           IAM   \n",
       "..                                                 ...           ...   \n",
       "871                                United Auto Workers           UAW   \n",
       "872                                United Auto Workers           UAW   \n",
       "873                                United Auto Workers           UAW   \n",
       "874                                United Auto Workers           UAW   \n",
       "875  International Association of Machinists and Ae...           IAM   \n",
       "\n",
       "          Union Local Bargaining unit Work stoppage beginning date  \\\n",
       "0                 NaN             NaN                   1988-02-16   \n",
       "1                 NaN             NaN                   1993-01-19   \n",
       "2               12003             NaN                   1993-01-24   \n",
       "3                 NaN             NaN                   1993-02-02   \n",
       "4                 NaN             NaN                   1993-03-02   \n",
       "..                ...             ...                          ...   \n",
       "871          Local 74             NaN                   2021-10-14   \n",
       "872          Local 74             NaN                   2021-10-14   \n",
       "873          Local 74             NaN                   2021-10-14   \n",
       "874               NaN             NaN                   2022-05-02   \n",
       "875  Local Lodge W246             NaN                   2022-09-13   \n",
       "\n",
       "    Work stoppage ending date  Number of workers[2]  \\\n",
       "0                  1993-12-20                2500.0   \n",
       "1                  1993-01-19               21000.0   \n",
       "2                  1993-05-20                1000.0   \n",
       "3                  1993-03-02                6700.0   \n",
       "4                  1993-03-03                6800.0   \n",
       "..                        ...                   ...   \n",
       "871                2021-11-17               10000.0   \n",
       "872                2021-11-17               10000.0   \n",
       "873                2021-11-17               10000.0   \n",
       "874                2023-01-21                1000.0   \n",
       "875                2022-10-28                1200.0   \n",
       "\n",
       "     Days idle, cumulative for this work stoppage[3]  \\\n",
       "0                                          2879500.0   \n",
       "1                                            21000.0   \n",
       "2                                            83000.0   \n",
       "3                                           103400.0   \n",
       "4                                            13600.0   \n",
       "..                                               ...   \n",
       "871                                         240000.0   \n",
       "872                                         240000.0   \n",
       "873                                         240000.0   \n",
       "874                                         180000.0   \n",
       "875                                          39600.0   \n",
       "\n",
       "                                                  Note NAICS_col  start_year  \\\n",
       "0    The number of workers decreased to 2,000 in De...        48      1988.0   \n",
       "1    The union name was changed to the Society of P...        33      1993.0   \n",
       "2    Boston Gas Company also does business as \"Nati...        22      1993.0   \n",
       "3    Number of workers involved increased from 5,00...        21      1993.0   \n",
       "4                                                  NaN        33      1993.0   \n",
       "..                                                 ...       ...         ...   \n",
       "871  The work stoppage involved employees at 14 dif...        33      2021.0   \n",
       "872  The work stoppage involved employees at 14 dif...        33      2021.0   \n",
       "873  The work stoppage involved employees at 14 dif...        33      2021.0   \n",
       "874                                                NaN        33      2022.0   \n",
       "875                                                NaN        11      2022.0   \n",
       "\n",
       "     end_year  \n",
       "0      1993.0  \n",
       "1      1993.0  \n",
       "2      1993.0  \n",
       "3      1993.0  \n",
       "4      1993.0  \n",
       "..        ...  \n",
       "871    2021.0  \n",
       "872    2021.0  \n",
       "873    2021.0  \n",
       "874    2023.0  \n",
       "875    2022.0  \n",
       "\n",
       "[875 rows x 17 columns]"
      ]
     },
     "execution_count": 122,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "testing_df = bls_df_subset.copy()\n",
    "for index, row in testing_df.iterrows():\n",
    "    if \",\" in row['States']:\n",
    "        states_list = row['States'].split(\",\")\n",
    "        testing_df.loc[index, 'States'] = states_list[0]\n",
    "        if index == 3:\n",
    "            print(states_list)\n",
    "        for i in np.arange(1,len(states_list)):\n",
    "            row_2 = row\n",
    "            row_2['States'] = states_list[i]\n",
    "            testing_df.loc[testing_df.index.max()+1, :] = row_2\n",
    "bls_df_subset = testing_df\n",
    "bls_df_subset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "id": "72af53f0-2ddb-4c14-a4ca-45d659ed4a74",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "952.0"
      ]
     },
     "execution_count": 123,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bls_df_subset['num_years'] = bls_df_subset[\"end_year\"] - bls_df_subset[\"start_year\"] + 1\n",
    "bls_df_subset['num_years'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "id": "059154ac-6deb-4385-b611-f49641d94518",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Organizations involved</th>\n",
       "      <th>States</th>\n",
       "      <th>Areas</th>\n",
       "      <th>Ownership</th>\n",
       "      <th>Industry code[1]</th>\n",
       "      <th>Union</th>\n",
       "      <th>Union acronym</th>\n",
       "      <th>Union Local</th>\n",
       "      <th>Bargaining unit</th>\n",
       "      <th>Work stoppage beginning date</th>\n",
       "      <th>Work stoppage ending date</th>\n",
       "      <th>Number of workers[2]</th>\n",
       "      <th>Days idle, cumulative for this work stoppage[3]</th>\n",
       "      <th>Note</th>\n",
       "      <th>NAICS_col</th>\n",
       "      <th>start_year</th>\n",
       "      <th>end_year</th>\n",
       "      <th>num_years</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Marine Towing and Transportation Employers' As...</td>\n",
       "      <td>NY</td>\n",
       "      <td>New York</td>\n",
       "      <td>Private industry</td>\n",
       "      <td>488330</td>\n",
       "      <td>International Longshoremen's Association</td>\n",
       "      <td>ILA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1988-02-16</td>\n",
       "      <td>1993-12-20</td>\n",
       "      <td>2500.0</td>\n",
       "      <td>2879500.0</td>\n",
       "      <td>The number of workers decreased to 2,000 in De...</td>\n",
       "      <td>48</td>\n",
       "      <td>1988.0</td>\n",
       "      <td>1993.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Boeing Company</td>\n",
       "      <td>WA</td>\n",
       "      <td>Seattle</td>\n",
       "      <td>Private industry</td>\n",
       "      <td>336411</td>\n",
       "      <td>Seattle Professional Engineering Employees Ass...</td>\n",
       "      <td>SPEEA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1993-01-19</td>\n",
       "      <td>1993-01-19</td>\n",
       "      <td>21000.0</td>\n",
       "      <td>21000.0</td>\n",
       "      <td>The union name was changed to the Society of P...</td>\n",
       "      <td>33</td>\n",
       "      <td>1993.0</td>\n",
       "      <td>1993.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Boston Gas Company</td>\n",
       "      <td>MA</td>\n",
       "      <td>Boston</td>\n",
       "      <td>Private industry</td>\n",
       "      <td>22121</td>\n",
       "      <td>United Steelworkers</td>\n",
       "      <td>USW</td>\n",
       "      <td>12003</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1993-01-24</td>\n",
       "      <td>1993-05-20</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>83000.0</td>\n",
       "      <td>Boston Gas Company also does business as \"Nati...</td>\n",
       "      <td>22</td>\n",
       "      <td>1993.0</td>\n",
       "      <td>1993.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Bituminous Coal Operators Association</td>\n",
       "      <td>IN</td>\n",
       "      <td>Multiple states</td>\n",
       "      <td>Private industry</td>\n",
       "      <td>212112</td>\n",
       "      <td>United Mine Workers of America</td>\n",
       "      <td>UMWA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1993-02-02</td>\n",
       "      <td>1993-03-02</td>\n",
       "      <td>6700.0</td>\n",
       "      <td>103400.0</td>\n",
       "      <td>Number of workers involved increased from 5,00...</td>\n",
       "      <td>21</td>\n",
       "      <td>1993.0</td>\n",
       "      <td>1993.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Douglas Aircraft</td>\n",
       "      <td>CA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Private industry</td>\n",
       "      <td>336411</td>\n",
       "      <td>International Association of Machinists</td>\n",
       "      <td>IAM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1993-03-02</td>\n",
       "      <td>1993-03-03</td>\n",
       "      <td>6800.0</td>\n",
       "      <td>13600.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>33</td>\n",
       "      <td>1993.0</td>\n",
       "      <td>1993.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>948</th>\n",
       "      <td>Charter Communications Inc.</td>\n",
       "      <td>NJ</td>\n",
       "      <td>Statewide</td>\n",
       "      <td>Private industry</td>\n",
       "      <td>517110</td>\n",
       "      <td>International Brotherhood of Electrical Workers</td>\n",
       "      <td>IBEW</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2017-03-28</td>\n",
       "      <td>2022-04-18</td>\n",
       "      <td>1800.0</td>\n",
       "      <td>2284200.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>51</td>\n",
       "      <td>2020.0</td>\n",
       "      <td>2022.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>949</th>\n",
       "      <td>Charter Communications Inc.</td>\n",
       "      <td>NJ</td>\n",
       "      <td>Statewide</td>\n",
       "      <td>Private industry</td>\n",
       "      <td>517110</td>\n",
       "      <td>International Brotherhood of Electrical Workers</td>\n",
       "      <td>IBEW</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2017-03-28</td>\n",
       "      <td>2022-04-18</td>\n",
       "      <td>1800.0</td>\n",
       "      <td>2284200.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>51</td>\n",
       "      <td>2019.0</td>\n",
       "      <td>2022.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>950</th>\n",
       "      <td>Charter Communications Inc.</td>\n",
       "      <td>NJ</td>\n",
       "      <td>Statewide</td>\n",
       "      <td>Private industry</td>\n",
       "      <td>517110</td>\n",
       "      <td>International Brotherhood of Electrical Workers</td>\n",
       "      <td>IBEW</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2017-03-28</td>\n",
       "      <td>2022-04-18</td>\n",
       "      <td>1800.0</td>\n",
       "      <td>2284200.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>51</td>\n",
       "      <td>2018.0</td>\n",
       "      <td>2022.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>951</th>\n",
       "      <td>Asarco</td>\n",
       "      <td>TX</td>\n",
       "      <td>Multiple</td>\n",
       "      <td>Private industry</td>\n",
       "      <td>212230</td>\n",
       "      <td>United Steelworkers</td>\n",
       "      <td>USW</td>\n",
       "      <td>District 12</td>\n",
       "      <td>43751</td>\n",
       "      <td>2019-10-13</td>\n",
       "      <td>2020-07-06</td>\n",
       "      <td>1800.0</td>\n",
       "      <td>325800.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>21</td>\n",
       "      <td>2020.0</td>\n",
       "      <td>2020.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>952</th>\n",
       "      <td>CNH Industrial</td>\n",
       "      <td>WI</td>\n",
       "      <td>Burlington, IA; Racine, WI</td>\n",
       "      <td>Private industry</td>\n",
       "      <td>33311, 33312</td>\n",
       "      <td>United Auto Workers</td>\n",
       "      <td>UAW</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-05-02</td>\n",
       "      <td>2023-01-21</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>180000.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>33</td>\n",
       "      <td>2023.0</td>\n",
       "      <td>2023.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>952 rows × 18 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                Organizations involved States  \\\n",
       "0    Marine Towing and Transportation Employers' As...     NY   \n",
       "1                                       Boeing Company     WA   \n",
       "2                                   Boston Gas Company     MA   \n",
       "3                Bituminous Coal Operators Association     IN   \n",
       "4                                     Douglas Aircraft     CA   \n",
       "..                                                 ...    ...   \n",
       "948                        Charter Communications Inc.     NJ   \n",
       "949                        Charter Communications Inc.     NJ   \n",
       "950                        Charter Communications Inc.     NJ   \n",
       "951                                             Asarco     TX   \n",
       "952                                     CNH Industrial     WI   \n",
       "\n",
       "                          Areas         Ownership Industry code[1]  \\\n",
       "0                      New York  Private industry           488330   \n",
       "1                       Seattle  Private industry           336411   \n",
       "2                        Boston  Private industry            22121   \n",
       "3               Multiple states  Private industry           212112   \n",
       "4                           NaN  Private industry           336411   \n",
       "..                          ...               ...              ...   \n",
       "948                   Statewide  Private industry           517110   \n",
       "949                   Statewide  Private industry           517110   \n",
       "950                   Statewide  Private industry           517110   \n",
       "951                    Multiple  Private industry           212230   \n",
       "952  Burlington, IA; Racine, WI  Private industry     33311, 33312   \n",
       "\n",
       "                                                 Union Union acronym  \\\n",
       "0             International Longshoremen's Association           ILA   \n",
       "1    Seattle Professional Engineering Employees Ass...         SPEEA   \n",
       "2                                  United Steelworkers           USW   \n",
       "3                       United Mine Workers of America          UMWA   \n",
       "4              International Association of Machinists           IAM   \n",
       "..                                                 ...           ...   \n",
       "948    International Brotherhood of Electrical Workers          IBEW   \n",
       "949    International Brotherhood of Electrical Workers          IBEW   \n",
       "950    International Brotherhood of Electrical Workers          IBEW   \n",
       "951                               United Steelworkers            USW   \n",
       "952                                United Auto Workers           UAW   \n",
       "\n",
       "     Union Local Bargaining unit Work stoppage beginning date  \\\n",
       "0            NaN             NaN                   1988-02-16   \n",
       "1            NaN             NaN                   1993-01-19   \n",
       "2          12003             NaN                   1993-01-24   \n",
       "3            NaN             NaN                   1993-02-02   \n",
       "4            NaN             NaN                   1993-03-02   \n",
       "..           ...             ...                          ...   \n",
       "948            3             NaN                   2017-03-28   \n",
       "949            3             NaN                   2017-03-28   \n",
       "950            3             NaN                   2017-03-28   \n",
       "951  District 12           43751                   2019-10-13   \n",
       "952          NaN             NaN                   2022-05-02   \n",
       "\n",
       "    Work stoppage ending date  Number of workers[2]  \\\n",
       "0                  1993-12-20                2500.0   \n",
       "1                  1993-01-19               21000.0   \n",
       "2                  1993-05-20                1000.0   \n",
       "3                  1993-03-02                6700.0   \n",
       "4                  1993-03-03                6800.0   \n",
       "..                        ...                   ...   \n",
       "948                2022-04-18                1800.0   \n",
       "949                2022-04-18                1800.0   \n",
       "950                2022-04-18                1800.0   \n",
       "951                2020-07-06                1800.0   \n",
       "952                2023-01-21                1000.0   \n",
       "\n",
       "     Days idle, cumulative for this work stoppage[3]  \\\n",
       "0                                          2879500.0   \n",
       "1                                            21000.0   \n",
       "2                                            83000.0   \n",
       "3                                           103400.0   \n",
       "4                                            13600.0   \n",
       "..                                               ...   \n",
       "948                                        2284200.0   \n",
       "949                                        2284200.0   \n",
       "950                                        2284200.0   \n",
       "951                                         325800.0   \n",
       "952                                         180000.0   \n",
       "\n",
       "                                                  Note NAICS_col  start_year  \\\n",
       "0    The number of workers decreased to 2,000 in De...        48      1988.0   \n",
       "1    The union name was changed to the Society of P...        33      1993.0   \n",
       "2    Boston Gas Company also does business as \"Nati...        22      1993.0   \n",
       "3    Number of workers involved increased from 5,00...        21      1993.0   \n",
       "4                                                  NaN        33      1993.0   \n",
       "..                                                 ...       ...         ...   \n",
       "948                                                NaN        51      2020.0   \n",
       "949                                                NaN        51      2019.0   \n",
       "950                                                NaN        51      2018.0   \n",
       "951                                                NaN        21      2020.0   \n",
       "952                                                NaN        33      2023.0   \n",
       "\n",
       "     end_year  num_years  \n",
       "0      1993.0        6.0  \n",
       "1      1993.0        1.0  \n",
       "2      1993.0        1.0  \n",
       "3      1993.0        1.0  \n",
       "4      1993.0        1.0  \n",
       "..        ...        ...  \n",
       "948    2022.0        6.0  \n",
       "949    2022.0        6.0  \n",
       "950    2022.0        6.0  \n",
       "951    2020.0        2.0  \n",
       "952    2023.0        2.0  \n",
       "\n",
       "[952 rows x 18 columns]"
      ]
     },
     "execution_count": 124,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "testing_df = bls_df_subset.copy()\n",
    "for index, row in testing_df.iterrows():\n",
    "    if row['num_years'] > 1:\n",
    "        i = row['num_years']-1\n",
    "        start_year = row['start_year']\n",
    "        while i != 0:\n",
    "            row_2 = row\n",
    "            row_2['start_year'] = start_year + i\n",
    "            testing_df.loc[testing_df.index.max()+1, :] = row_2\n",
    "            i = i - 1\n",
    "bls_df_subset = testing_df\n",
    "bls_df_subset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "id": "b4faf336-437b-4294-9b8d-66cb3b4fb764",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>States</th>\n",
       "      <th>start_year</th>\n",
       "      <th>Number of workers[2]</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AL</td>\n",
       "      <td>1994.0</td>\n",
       "      <td>1200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AL</td>\n",
       "      <td>2005.0</td>\n",
       "      <td>1500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AL</td>\n",
       "      <td>2006.0</td>\n",
       "      <td>1500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AZ</td>\n",
       "      <td>2019.0</td>\n",
       "      <td>4300.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CA</td>\n",
       "      <td>1994.0</td>\n",
       "      <td>1700.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>602</th>\n",
       "      <td>WV</td>\n",
       "      <td>1996.0</td>\n",
       "      <td>1000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>603</th>\n",
       "      <td>WV</td>\n",
       "      <td>2015.0</td>\n",
       "      <td>2200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>604</th>\n",
       "      <td>WV</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>2200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>605</th>\n",
       "      <td>WV</td>\n",
       "      <td>2018.0</td>\n",
       "      <td>36400.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>606</th>\n",
       "      <td>WV</td>\n",
       "      <td>2019.0</td>\n",
       "      <td>36400.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>607 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    States  start_year  Number of workers[2]\n",
       "0       AL      1994.0                1200.0\n",
       "1       AL      2005.0                1500.0\n",
       "2       AL      2006.0                1500.0\n",
       "3       AZ      2019.0                4300.0\n",
       "4       CA      1994.0                1700.0\n",
       "..     ...         ...                   ...\n",
       "602     WV      1996.0                1000.0\n",
       "603     WV      2015.0                2200.0\n",
       "604     WV      2016.0                2200.0\n",
       "605     WV      2018.0               36400.0\n",
       "606     WV      2019.0               36400.0\n",
       "\n",
       "[607 rows x 3 columns]"
      ]
     },
     "execution_count": 125,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped_df = bls_df_subset.groupby(['States','start_year'])['Number of workers[2]'].sum().reset_index()\n",
    "grouped_df#.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "id": "8c15a692-4984-4463-8052-54261d58b7a8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "322700.0"
      ]
     },
     "execution_count": 126,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped_df['Number of workers[2]'].max()\n",
    "#bls_df_subset[bls_df_subset['States'] == 'AK']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fea1e60d-f4e6-4fcf-aaa7-b2955d4f003b",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}