Florida_WARN_Data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Florida Worker Adjustment & Retraining Notifications"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Companies meeting certain requirements are required to file notices with the state about workers that are being laid off, furloughed or having hours cut. Those filings can be accessed at:\n",
    "\n",
    "https://floridajobs.org/office-directory/division-of-workforce-services/workforce-programs/reemployment-and-emergency-assistance-coordination-team-react/warn-notices"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Aggregating and cleaning WARN data\n",
    "\n",
    "### Step 1\n",
    "\n",
    "First, I will import the requests and BeautifulSoup libraries, so that I can quickly retrieve and filter the HTML code from the state's WARN website. \n",
    "\n",
    "Then, I will make a GET request of the website and retrieve the retrieved HTML code."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "from bs4 import BeautifulSoup\n",
    "url = 'http://reactwarn.floridajobs.org/WarnList/Records?year=2020'\n",
    "r = requests.get(url)\n",
    "text = r.text\n",
    "soup = BeautifulSoup(text)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 2\n",
    "\n",
    "I will find out the number of webpages to loop through. I will store each page number in a list, which I will use to loop through and append to the end of my subsequent get requests."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13']\n"
     ]
    }
   ],
   "source": [
    "pages = []\n",
    "for page in range(1,15):\n",
    "    if len(pages) != len([]):\n",
    "        if page > int(pages[-1]):\n",
    "            break\n",
    "    url = f'http://reactwarn.floridajobs.org/WarnList/Records?year=2020&page={page}'\n",
    "    r = requests.get(url)\n",
    "    text = r.text\n",
    "    soup = BeautifulSoup(text)\n",
    "    lst = soup.tfoot.td.text.split()\n",
    "    for item in lst:\n",
    "        if item == '>' or item == '<':\n",
    "            pass\n",
    "        else:\n",
    "            if item not in pages:\n",
    "                pages.append(item)\n",
    "            else:\n",
    "                continue\n",
    "print(pages)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 3\n",
    "\n",
    "Now I will loop through every page, and extract the data into a series of lists."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "company = []\n",
    "address = []\n",
    "zip_code = []\n",
    "city = []\n",
    "notification_date = []\n",
    "start_date = []\n",
    "end_date = []\n",
    "employees = []\n",
    "industry = []\n",
    "links = []\n",
    "\n",
    "for page in pages:\n",
    "    url = f'http://reactwarn.floridajobs.org/WarnList/Records?year=2020&page={page}'\n",
    "    r = requests.get(url)\n",
    "    text = r.text\n",
    "    soup = BeautifulSoup(text)\n",
    "    tds = soup.tbody.find_all('td')\n",
    "    \n",
    "    for i in range(0,len(tds),6):\n",
    "        company.append(str(tds[i])[7:-5].split(\"</b> \")[0])\n",
    "        address.append(str(tds[i])[7:-5].split(\"</b> \")[1].split(\", FL, \")[0])\n",
    "        zip_code.append(str(tds[i])[7:-5].split(\"</b> \")[1].split(\", FL, \")[1])\n",
    "    \n",
    "    for i in range(1,len(tds),6):\n",
    "        notification_date.append(str(tds[i])[4:6]+'/'+str(tds[i])[7:9]+'/'+str(tds[i])[10:12])\n",
    "        \n",
    "    for i in range(2,len(tds),6):\n",
    "        start_date.append(str(tds[i])[4:6]+'/'+str(tds[i])[7:9]+'/'+str(tds[i])[10:12])\n",
    "        end_date.append(str(tds[i]).split(\"i> \")[2][:2]+'/'+str(tds[i]).split(\"i> \")[2][3:5]+'/'+str(tds[i]).split(\"i> \")[2][6:-5])\n",
    "    \n",
    "    for i in range(3,len(tds),6):\n",
    "        employees.append(int(str(tds[i])[4:-5]))\n",
    "        \n",
    "    for i in range(4,len(tds),6):\n",
    "        industry.append(str(tds[i])[4:-5])\n",
    "        \n",
    "    for i in range(5,len(tds),6):\n",
    "        try:\n",
    "            links.append('http://reactwarn.floridajobs.org'+str(tds[i]).split('''=\"''')[1][:-19])\n",
    "        except:\n",
    "            links.append('None')\n",
    "\n",
    "for a in address:\n",
    "    reverse = str()\n",
    "    for i in reversed(range(0,len(a))):\n",
    "        reverse = reverse + a[i]\n",
    "    counter = 0\n",
    "    for i in range(0,len(reverse)):\n",
    "        if (reverse[i].isupper() and reverse[i] not in [0,1,2,3,4,5,6,7,8,9] and reverse[i] != \".\") or reverse[i] == \" \":\n",
    "            counter += 1\n",
    "        else:\n",
    "            idx = counter * -1\n",
    "            break\n",
    "    if a[idx] == ' ':\n",
    "        if a[idx-3:idx].upper() == \"ST.\":\n",
    "            idx = idx - 3\n",
    "        else:\n",
    "            idx = idx + 2\n",
    "    city.append(a[idx:].title())\n",
    "\n",
    "# Fix common errors\n",
    "for i in range(0,len(city)):\n",
    "    if city[i] == 'Aboynton Beach':\n",
    "        city[i] = 'Boynton Beach'\n",
    "    elif city[i] == 'Acoral Springs':\n",
    "        city[i] = 'Coral Springs'\n",
    "    elif city[i] == 'Amiami':\n",
    "        city[i] = 'Miami'\n",
    "    elif city[i] == 'Aorange Park':\n",
    "        city[i] = 'Orange Park'\n",
    "    elif city[i] == 'Aport Saint Lucie':\n",
    "        city[i] = 'Port Saint Lucie'\n",
    "    elif city[i] == 'Asatellite Beach':\n",
    "        city[i] = 'Satellite Beach'\n",
    "    elif city[i] == 'Awinter Park':\n",
    "        city[i] = 'Winter Park'\n",
    "    elif city[i] == 'Bfort Lauderdale':\n",
    "        city[i] = 'Fort Lauderdale'\n",
    "    elif city[i] == 'Awinter Park':\n",
    "        city[i] = 'Winter Park'\n",
    "    elif city[i] == 'Ckendall':\n",
    "        city[i] = 'Kendall'\n",
    "    elif city[i] == 'Npalm Harbor':\n",
    "        city[i] = 'Palm Harbor'\n",
    "    elif city[i] == 'St. Nnaples':\n",
    "        city[i] = 'Naples'    \n",
    "for i in range(0,len(address)):\n",
    "    address[i] = address[i][:-len(city[i])]    \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 4\n",
    "\n",
    "Now I will append my lists into a Pandas dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>company</th>\n",
       "      <th>address</th>\n",
       "      <th>zip</th>\n",
       "      <th>city</th>\n",
       "      <th>notification_date</th>\n",
       "      <th>start_date</th>\n",
       "      <th>end_date</th>\n",
       "      <th>employees</th>\n",
       "      <th>industry</th>\n",
       "      <th>filing</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>TrueCore Behavioral Solutions</td>\n",
       "      <td>5050 N.E. 168th St.</td>\n",
       "      <td>34972</td>\n",
       "      <td>Okeechobee</td>\n",
       "      <td>10/08/20</td>\n",
       "      <td>09/28/20</td>\n",
       "      <td>09/28/20</td>\n",
       "      <td>101</td>\n",
       "      <td>Administrative and Support and Waste Managemen...</td>\n",
       "      <td>http://reactwarn.floridajobs.org/WarnList/Down...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Cuba Libre Orlando, LLC t/a Cuba Libre Restaur...</td>\n",
       "      <td>9101 International Dr.</td>\n",
       "      <td>32819</td>\n",
       "      <td>Orlando</td>\n",
       "      <td>10/08/20</td>\n",
       "      <td>10/10/20</td>\n",
       "      <td>10/10/20</td>\n",
       "      <td>120</td>\n",
       "      <td>Accommodation and Food Services</td>\n",
       "      <td>http://reactwarn.floridajobs.org/WarnList/Down...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Hyatt Regency Orlando</td>\n",
       "      <td>9801 International Dr.</td>\n",
       "      <td>32819</td>\n",
       "      <td>Orlando</td>\n",
       "      <td>10/12/20</td>\n",
       "      <td>10/17/20</td>\n",
       "      <td>10/17/20</td>\n",
       "      <td>315</td>\n",
       "      <td>Accommodation and Food Services</td>\n",
       "      <td>http://reactwarn.floridajobs.org/WarnList/Down...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ABM. Inc.</td>\n",
       "      <td>Nova Southeastern University3301 College Ave.</td>\n",
       "      <td>33314</td>\n",
       "      <td>Fort Lauderdale</td>\n",
       "      <td>10/12/20</td>\n",
       "      <td>11/30/20</td>\n",
       "      <td>11/30/20</td>\n",
       "      <td>269</td>\n",
       "      <td>Accommodation and Food Services</td>\n",
       "      <td>http://reactwarn.floridajobs.org/WarnList/Down...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Newport Beachside Resort</td>\n",
       "      <td>16701 Collins Avenue</td>\n",
       "      <td>33160</td>\n",
       "      <td>Sunny Isles Beach</td>\n",
       "      <td>10/13/20</td>\n",
       "      <td>11/30/20</td>\n",
       "      <td>11/30/20</td>\n",
       "      <td>100</td>\n",
       "      <td>Accommodation and Food Services</td>\n",
       "      <td>http://reactwarn.floridajobs.org/WarnList/Down...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Homewood Suites by Hilton Orlando-Internationa...</td>\n",
       "      <td>8745 International Drive</td>\n",
       "      <td>32819</td>\n",
       "      <td>Orlando</td>\n",
       "      <td>10/15/20</td>\n",
       "      <td>11/17/20</td>\n",
       "      <td>11/17/20</td>\n",
       "      <td>22</td>\n",
       "      <td>Accommodation and Food Services</td>\n",
       "      <td>http://reactwarn.floridajobs.org/WarnList/Down...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Embassy Suites by HIlton Orlando International...</td>\n",
       "      <td>8250 Jamaican Court</td>\n",
       "      <td>32819</td>\n",
       "      <td>Orlando</td>\n",
       "      <td>10/15/20</td>\n",
       "      <td>11/17/20</td>\n",
       "      <td>11/17/20</td>\n",
       "      <td>20</td>\n",
       "      <td>Accommodation and Food Services</td>\n",
       "      <td>http://reactwarn.floridajobs.org/WarnList/Down...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Hilton Singer Island Oceanfront/Palm Beaches R...</td>\n",
       "      <td>3700 North Ocean Drive</td>\n",
       "      <td>33404</td>\n",
       "      <td>Riviera Beach</td>\n",
       "      <td>10/15/20</td>\n",
       "      <td>11/17/20</td>\n",
       "      <td>11/17/20</td>\n",
       "      <td>19</td>\n",
       "      <td>Accommodation and Food Services</td>\n",
       "      <td>http://reactwarn.floridajobs.org/WarnList/Down...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Wyndham Vacation Ownership/The Bonnet Creek Re...</td>\n",
       "      <td>9560 Via Encinas</td>\n",
       "      <td>32830</td>\n",
       "      <td>Orlando</td>\n",
       "      <td>10/14/20</td>\n",
       "      <td>09/04/20</td>\n",
       "      <td>10/17/20</td>\n",
       "      <td>6</td>\n",
       "      <td>Accommodation and Food Services</td>\n",
       "      <td>http://reactwarn.floridajobs.org/WarnList/Down...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Dr. Smood, LLC</td>\n",
       "      <td>Aventura19501 Biscayne Blvd.</td>\n",
       "      <td>33180</td>\n",
       "      <td>Miami</td>\n",
       "      <td>10/13/20</td>\n",
       "      <td>11/04/20</td>\n",
       "      <td>11/04/20</td>\n",
       "      <td>5</td>\n",
       "      <td>Accommodation and Food Services</td>\n",
       "      <td>http://reactwarn.floridajobs.org/WarnList/Down...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             company  \\\n",
       "0                      TrueCore Behavioral Solutions   \n",
       "1  Cuba Libre Orlando, LLC t/a Cuba Libre Restaur...   \n",
       "2                              Hyatt Regency Orlando   \n",
       "3                                          ABM. Inc.   \n",
       "4                           Newport Beachside Resort   \n",
       "5  Homewood Suites by Hilton Orlando-Internationa...   \n",
       "6  Embassy Suites by HIlton Orlando International...   \n",
       "7  Hilton Singer Island Oceanfront/Palm Beaches R...   \n",
       "8  Wyndham Vacation Ownership/The Bonnet Creek Re...   \n",
       "9                                     Dr. Smood, LLC   \n",
       "\n",
       "                                         address    zip               city  \\\n",
       "0                            5050 N.E. 168th St.  34972         Okeechobee   \n",
       "1                         9101 International Dr.  32819            Orlando   \n",
       "2                         9801 International Dr.  32819            Orlando   \n",
       "3  Nova Southeastern University3301 College Ave.  33314    Fort Lauderdale   \n",
       "4                           16701 Collins Avenue  33160  Sunny Isles Beach   \n",
       "5                       8745 International Drive  32819            Orlando   \n",
       "6                            8250 Jamaican Court  32819            Orlando   \n",
       "7                         3700 North Ocean Drive  33404      Riviera Beach   \n",
       "8                               9560 Via Encinas  32830            Orlando   \n",
       "9                   Aventura19501 Biscayne Blvd.  33180              Miami   \n",
       "\n",
       "  notification_date start_date  end_date  employees  \\\n",
       "0          10/08/20   09/28/20  09/28/20        101   \n",
       "1          10/08/20   10/10/20  10/10/20        120   \n",
       "2          10/12/20   10/17/20  10/17/20        315   \n",
       "3          10/12/20   11/30/20  11/30/20        269   \n",
       "4          10/13/20   11/30/20  11/30/20        100   \n",
       "5          10/15/20   11/17/20  11/17/20         22   \n",
       "6          10/15/20   11/17/20  11/17/20         20   \n",
       "7          10/15/20   11/17/20  11/17/20         19   \n",
       "8          10/14/20   09/04/20  10/17/20          6   \n",
       "9          10/13/20   11/04/20  11/04/20          5   \n",
       "\n",
       "                                            industry  \\\n",
       "0  Administrative and Support and Waste Managemen...   \n",
       "1                    Accommodation and Food Services   \n",
       "2                    Accommodation and Food Services   \n",
       "3                    Accommodation and Food Services   \n",
       "4                    Accommodation and Food Services   \n",
       "5                    Accommodation and Food Services   \n",
       "6                    Accommodation and Food Services   \n",
       "7                    Accommodation and Food Services   \n",
       "8                    Accommodation and Food Services   \n",
       "9                    Accommodation and Food Services   \n",
       "\n",
       "                                              filing  \n",
       "0  http://reactwarn.floridajobs.org/WarnList/Down...  \n",
       "1  http://reactwarn.floridajobs.org/WarnList/Down...  \n",
       "2  http://reactwarn.floridajobs.org/WarnList/Down...  \n",
       "3  http://reactwarn.floridajobs.org/WarnList/Down...  \n",
       "4  http://reactwarn.floridajobs.org/WarnList/Down...  \n",
       "5  http://reactwarn.floridajobs.org/WarnList/Down...  \n",
       "6  http://reactwarn.floridajobs.org/WarnList/Down...  \n",
       "7  http://reactwarn.floridajobs.org/WarnList/Down...  \n",
       "8  http://reactwarn.floridajobs.org/WarnList/Down...  \n",
       "9  http://reactwarn.floridajobs.org/WarnList/Down...  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df = pd.DataFrame(list(zip(company,address,zip_code,city,notification_date,start_date,end_date,employees,industry,links)),\n",
    "            columns=['company','address','zip','city','notification_date','start_date','end_date','employees','industry','filing'])\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 4\n",
    "\n",
    "Finally, I will save this dataframe to a Google Sheet so that it can be easily shared.\n",
    "\n",
    "\n",
    "###### Note: Follow these instructions to be able to save to your own Google Sheet: \n",
    "https://medium.com/analytics-vidhya/how-to-read-and-write-data-to-google-spreadsheet-using-python-ebf54d51a72c"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# !pip install google_spreadsheet\n",
    "# !pip install google-auth-oauthlib\n",
    "\n",
    "from oauth2client.service_account import ServiceAccountCredentials\n",
    "from pandas.io.json import json_normalize"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "import google_spreadsheet\n",
    "import google_auth_oauthlib\n",
    "from googleapiclient.discovery import build\n",
    "from google_auth_oauthlib.flow import InstalledAppFlow,Flow\n",
    "from google.auth.transport.requests import Request\n",
    "import os\n",
    "import pickle"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "sheets service created successfully\n",
      "Sheet successfully Updated\n"
     ]
    }
   ],
   "source": [
    "#change this to your sheet ID\n",
    "gsheetId = ''\n",
    "\n",
    "#change the range if needed\n",
    "SAMPLE_RANGE_NAME = 'A1:AA10000'\n",
    "\n",
    "def Create_Service(client_secret_file, api_service_name, api_version, *scopes):\n",
    "    global service\n",
    "    SCOPES = [scope for scope in scopes[0]]\n",
    "    #print(SCOPES)\n",
    "    \n",
    "    cred = None\n",
    "\n",
    "    if os.path.exists('token_write.pickle'):\n",
    "        with open('token_write.pickle', 'rb') as token:\n",
    "            cred = pickle.load(token)\n",
    "\n",
    "    if not cred or not cred.valid:\n",
    "        if cred and cred.expired and cred.refresh_token:\n",
    "            cred.refresh(Request())\n",
    "        else:\n",
    "            flow = InstalledAppFlow.from_client_secrets_file(client_secret_file, SCOPES)\n",
    "            cred = flow.run_local_server()\n",
    "\n",
    "        with open('token_write.pickle', 'wb') as token:\n",
    "            pickle.dump(cred, token)\n",
    "\n",
    "    try:\n",
    "        service = build(api_service_name, api_version, credentials=cred)\n",
    "        print(api_service_name, 'service created successfully')\n",
    "        #return service\n",
    "    except Exception as e:\n",
    "        print(e)\n",
    "        #return None\n",
    "        \n",
    "# change './credentials.json' to your downloaded JSON file.\n",
    "Create_Service('./credentials.json', 'sheets', 'v4',['https://www.googleapis.com/auth/spreadsheets'])\n",
    "    \n",
    "def Export_Data_To_Sheets():\n",
    "    response_date = service.spreadsheets().values().update(\n",
    "        spreadsheetId=gsheetId,\n",
    "        valueInputOption='RAW',\n",
    "        range=SAMPLE_RANGE_NAME,\n",
    "        body=dict(\n",
    "            majorDimension='ROWS',\n",
    "            values=df.T.reset_index().T.values.tolist())\n",
    "    ).execute()\n",
    "    print('Sheet successfully Updated')\n",
    "\n",
    "Export_Data_To_Sheets()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here is the final spreadhseet:\n",
    "    \n",
    "https://docs.google.com/spreadsheets/d/14ZRbs59AqFmJsY6Aehu0HtBzqIyWO7L6rWSG4rbYB38/edit#gid=0"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And here are some of the articles that utilized this data:\n",
    "    \n",
    "- https://www.bizjournals.com/jacksonville/news/2020/07/17/downtown-jaguars-jobs-jacksonville-layoffs.html\n",
    "- https://www.bizjournals.com/jacksonville/news/2020/05/14/ponte-vedra-layoffs-northeast-florida-covid19.html\n",
    "- https://www.bizjournals.com/jacksonville/news/2020/06/15/downtown-ponte-vedra-see-hundreds-of-hotel-layoffs.html"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}