cbp1.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "3ae81bbc-b55b-4e1c-95db-cfd5840195b5",
   "metadata": {},
   "outputs": [],
   "source": [
    "from playwright.async_api import async_playwright\n",
    "import pandas as pd\n",
    "from bs4 import BeautifulSoup\n",
    "import requests\n",
    "import datetime\n",
    "import time\n",
    "import json\n",
    "import csv\n",
    "import numpy as np\n",
    "import re\n",
    "import lxml.html"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "86ea7bc5-f073-4498-8cdf-18e98f2a16d4",
   "metadata": {},
   "outputs": [],
   "source": [
    "playwright = await async_playwright().start()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "c569da4e-f13e-4d9b-b076-c405ee24667c",
   "metadata": {},
   "outputs": [],
   "source": [
    "browser = await playwright.chromium.launch(headless = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "9000c084-f2e0-49f7-bbfe-0523ea058b55",
   "metadata": {},
   "outputs": [],
   "source": [
    "page = await browser.new_page()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "3d48bc52-cb8f-4c0b-81d4-2427a4e0bc25",
   "metadata": {},
   "outputs": [],
   "source": [
    "await page.goto(\"https://awt.cbp.gov/\")\n",
    "time.sleep(6)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "12972047-0045-44b1-b13e-fa9583844ed1",
   "metadata": {},
   "source": [
    "### Choose an airport -- JFK in our case: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "e1aad911-6256-411d-a31d-6f8fa3a8ed85",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['JFK']"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "await page.get_by_label(\"Select Your Airport/Terminal\").select_option(\"JFK\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "e386760a-fcf3-425a-b643-de13ca8ab8d5",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "await page.get_by_role(\"button\", name=\"Create Report\").click()\n",
    "time.sleep(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "0f331f36-4a01-4a2b-a115-1d520e22b335",
   "metadata": {},
   "outputs": [],
   "source": [
    "page = await page.content()\n",
    "tables = pd.read_html(page)\n",
    "tables[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "bd611a1a-06b9-4bff-b13f-ac7d273c219c",
   "metadata": {},
   "outputs": [],
   "source": [
    "table = tables[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "e848ff02-e730-4f4a-8755-8a5a90ad1be1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([('Airport', 'Airport', 'Airport'),\n",
       "       ('Terminal', 'Terminal', 'Terminal'), ('Date', 'Date', 'Date'),\n",
       "       ('Hour', 'Hour', 'Hour'),\n",
       "       ('U.S. Citizen', 'U.S. Citizen', 'Average Wait  Time'),\n",
       "       ('U.S. Citizen', 'U.S. Citizen', 'Max Wait  Time'),\n",
       "       ('Non U.S. Citizen', 'Non U.S. Citizen', 'Average Wait  Time'),\n",
       "       ('Non U.S. Citizen', 'Non U.S. Citizen', 'Max Wait  Time'),\n",
       "       ('All', 'Wait Times', 'Average Wait  Time'),\n",
       "       ('All', 'Wait Times', 'Max Wait  Time'),\n",
       "       ('All', 'Number Of Passengers Time Interval', '0-15'),\n",
       "       ('All', 'Number Of Passengers Time Interval', '16-30'),\n",
       "       ('All', 'Number Of Passengers Time Interval', '31-45'),\n",
       "       ('All', 'Number Of Passengers Time Interval', '46-60'),\n",
       "       ('All', 'Number Of Passengers Time Interval', '61-90'),\n",
       "       ('All', 'Number Of Passengers Time Interval', '91-120'),\n",
       "       ('All', 'Number Of Passengers Time Interval', '120 plus'),\n",
       "       ('All', 'Unnamed: 17_level_1', 'Excluded'),\n",
       "       ('All', 'Unnamed: 18_level_1', 'Total'),\n",
       "       ('All', 'Unnamed: 19_level_1', 'Flights'),\n",
       "       ('All', 'Unnamed: 20_level_1', 'Unnamed: 20_level_2')],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table.columns.values"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "953947ee-9900-4358-8907-1fd3a001da60",
   "metadata": {},
   "source": [
    "### We clean up the columns and consolidate in one row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "c1da4a61-4463-4b88-a7a6-893c031ba946",
   "metadata": {},
   "outputs": [],
   "source": [
    "table.columns = ['_'.join(col) for col in table.columns.values]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "e12adcfd-507a-49e0-b1c6-3b4cc70b7092",
   "metadata": {},
   "outputs": [],
   "source": [
    "table.rename(columns = {'Airport_Airport_Airport': 'Airport', 'Terminal_Terminal_Terminal':'Terminal', 'Date_Date_Date':'date', 'Hour_Hour_Hour':'hour' }, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "436f94b9-7f13-4327-935a-4e4864870221",
   "metadata": {},
   "outputs": [],
   "source": [
    "table.rename(columns = {'U.S. Citizen_U.S. Citizen_Average Wait  Time': 'US Citizen Avg', 'U.S. Citizen_U.S. Citizen_Max Wait  Time':'US Citizen Max', 'Non U.S. Citizen_Non U.S. Citizen_Average Wait  Time':'Non-Citizen Avg', 'Non U.S. Citizen_Non U.S. Citizen_Max Wait  Time': 'Non-Citizen Max' }, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "341ed8c8-bf5c-4c8a-a8d7-94cd2b43fce8",
   "metadata": {},
   "outputs": [],
   "source": [
    "table.rename(columns = {'All_Wait Times_Average Wait  Time': 'Avg Wait Time', 'All_Wait Times_Max Wait  Time':'Max Wait Time', 'All_Unnamed: 18_level_1_Total':'Total Passengers', 'All_Unnamed: 19_level_1_Flights': 'Total Flights' }, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "91cec03c-2596-4f72-9157-bd536b551233",
   "metadata": {},
   "outputs": [],
   "source": [
    "table = table[['Airport', 'Terminal', 'date', 'hour','US Citizen Avg', 'US Citizen Max', 'Non-Citizen Avg','Non-Citizen Max','Avg Wait Time', 'Max Wait Time','Total Passengers','Total Flights' ]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "92c42c51-799c-4bf4-bdbf-315dde9d8d79",
   "metadata": {},
   "outputs": [],
   "source": [
    "term1 = table[table[\"Terminal\"].str.contains(\"1\")].reset_index(inplace=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "721f676e-83a7-4ad6-a95f-5c252d89c554",
   "metadata": {},
   "outputs": [],
   "source": [
    "term1 = term1.drop('index', axis =1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "9af32868-559d-4f72-8bda-cb83c5f4b374",
   "metadata": {},
   "outputs": [],
   "source": [
    "reduced1 = term1[['hour','US Citizen Avg', 'Non-Citizen Avg']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "ce989461-68c3-462b-9ffd-018aea82cc95",
   "metadata": {},
   "outputs": [],
   "source": [
    "reducedtransposed1 = reduced1.transpose()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "08edc24d-4613-4ac9-9de3-ff8b57ba8aec",
   "metadata": {},
   "outputs": [],
   "source": [
    "reducedtransposed1.columns = reducedtransposed1.iloc[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "216c8250-638a-4b6e-9e16-ef8f529bc543",
   "metadata": {},
   "outputs": [],
   "source": [
    "reducedtransposed1 = reducedtransposed1.drop(index= \"hour\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "0d8eeec8-c4c2-427b-b5e4-4f6693282709",
   "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>hour</th>\n",
       "      <th>0900 - 1000</th>\n",
       "      <th>1100 - 1200</th>\n",
       "      <th>1200 - 1300</th>\n",
       "      <th>1300 - 1400</th>\n",
       "      <th>1400 - 1500</th>\n",
       "      <th>1500 - 1600</th>\n",
       "      <th>1600 - 1700</th>\n",
       "      <th>1700 - 1800</th>\n",
       "      <th>1800 - 1900</th>\n",
       "      <th>1900 - 2000</th>\n",
       "      <th>2000 - 2100</th>\n",
       "      <th>2100 - 2200</th>\n",
       "      <th>2200 - 2300</th>\n",
       "      <th>2300 - 0000</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>US Citizen Avg</th>\n",
       "      <td>31</td>\n",
       "      <td>38</td>\n",
       "      <td>41</td>\n",
       "      <td>27</td>\n",
       "      <td>53</td>\n",
       "      <td>49</td>\n",
       "      <td>44</td>\n",
       "      <td>21</td>\n",
       "      <td>28</td>\n",
       "      <td>25</td>\n",
       "      <td>26</td>\n",
       "      <td>33</td>\n",
       "      <td>31</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Non-Citizen Avg</th>\n",
       "      <td>41</td>\n",
       "      <td>86</td>\n",
       "      <td>58</td>\n",
       "      <td>77</td>\n",
       "      <td>100</td>\n",
       "      <td>114</td>\n",
       "      <td>110</td>\n",
       "      <td>98</td>\n",
       "      <td>47</td>\n",
       "      <td>34</td>\n",
       "      <td>66</td>\n",
       "      <td>65</td>\n",
       "      <td>36</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "hour            0900 - 1000 1100 - 1200 1200 - 1300 1300 - 1400 1400 - 1500  \\\n",
       "US Citizen Avg           31          38          41          27          53   \n",
       "Non-Citizen Avg          41          86          58          77         100   \n",
       "\n",
       "hour            1500 - 1600 1600 - 1700 1700 - 1800 1800 - 1900 1900 - 2000  \\\n",
       "US Citizen Avg           49          44          21          28          25   \n",
       "Non-Citizen Avg         114         110          98          47          34   \n",
       "\n",
       "hour            2000 - 2100 2100 - 2200 2200 - 2300 2300 - 0000  \n",
       "US Citizen Avg           26          33          31          17  \n",
       "Non-Citizen Avg          66          65          36          17  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reducedtransposed1 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "9fc6760a-5511-4f60-8fb6-e006323b4fce",
   "metadata": {},
   "outputs": [],
   "source": [
    "reducedtransposed1.to_csv(\"term1_us_nonus.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6852aebe-ea45-4d85-9646-1d20b4eb4f7e",
   "metadata": {},
   "source": [
    "### Terminal 4:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "237134a4-b503-4bf8-a8b0-0b70a583803e",
   "metadata": {},
   "outputs": [],
   "source": [
    "term4 = table[table[\"Terminal\"].str.contains(\"4\")].reset_index(inplace=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "8d37c879-b651-4d65-9e88-3f9ef7c27ca5",
   "metadata": {},
   "outputs": [],
   "source": [
    "term4 = term4.drop('index', axis =1)\n",
    "reduced4 = term4[['hour','US Citizen Avg', 'Non-Citizen Avg']]\n",
    "reducedtransposed4 = reduced4.transpose()\n",
    "reducedtransposed4.columns = reducedtransposed4.iloc[0]\n",
    "reducedtransposed4 = reducedtransposed4.drop(index= \"hour\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "550d6ab8-a08c-472a-8b2f-8b72f4a8c978",
   "metadata": {},
   "outputs": [],
   "source": [
    "reducedtransposed4.to_csv(\"term4_us_nonus.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7cb550b7-f62e-4a46-8d06-45169d0efcae",
   "metadata": {},
   "source": [
    "### Terminal 5: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "c3b1b876-5324-4bbd-b65a-1a687f0ce655",
   "metadata": {},
   "outputs": [],
   "source": [
    "term5 = table[table[\"Terminal\"].str.contains(\"5\")].reset_index(inplace=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "0d7b169c-8f79-412b-88cc-f722025fd0df",
   "metadata": {},
   "outputs": [],
   "source": [
    "term5 = term5.drop('index', axis =1)\n",
    "reduced5 = term5[['hour','US Citizen Avg', 'Non-Citizen Avg']]\n",
    "reducedtransposed5 = reduced5.transpose()\n",
    "reducedtransposed5.columns = reducedtransposed5.iloc[0]\n",
    "reducedtransposed5 = reducedtransposed5.drop(index= \"hour\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "07997550-557b-4c51-87db-9820dc217afa",
   "metadata": {},
   "outputs": [],
   "source": [
    "reducedtransposed5.to_csv(\"term5_us_nonus.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "54d74b3b-abd1-4e14-880b-47d2e87ed88a",
   "metadata": {},
   "source": [
    "### Terminal 7: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "b3770b1e-89ce-4c22-9f7e-1c0068664e25",
   "metadata": {},
   "outputs": [],
   "source": [
    "term7 = table[table[\"Terminal\"].str.contains(\"7\")].reset_index(inplace=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "a94faf85-9e79-45c9-b254-d785ac62e7e3",
   "metadata": {},
   "outputs": [],
   "source": [
    "term7 = term7.drop('index', axis =1)\n",
    "reduced7 = term7[['hour','US Citizen Avg', 'Non-Citizen Avg']]\n",
    "reducedtransposed7 = reduced7.transpose()\n",
    "reducedtransposed7.columns = reducedtransposed7.iloc[0]\n",
    "reducedtransposed7 = reducedtransposed7.drop(index= \"hour\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "74270bc0-da5a-43b4-8b32-6ff45af80d62",
   "metadata": {},
   "outputs": [],
   "source": [
    "reducedtransposed7.to_csv(\"term7_us_nonus.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "01e26d73-5726-4cdf-b14c-032ea9bd8ff3",
   "metadata": {},
   "source": [
    "### Terminal 8: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "dcc2cd4f-f372-4d56-b831-0bc77cfb9ccc",
   "metadata": {},
   "outputs": [],
   "source": [
    "term8 = table[table[\"Terminal\"].str.contains(\"8\")].reset_index(inplace=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "b69c02f5-76e9-4258-9dd8-4b7984b0c9e2",
   "metadata": {},
   "outputs": [],
   "source": [
    "reduced8 = term8[['hour','US Citizen Avg', 'Non-Citizen Avg']]\n",
    "reducedtransposed8 = reduced8.transpose()\n",
    "reducedtransposed8.columns = reducedtransposed8.iloc[0]\n",
    "reducedtransposed8 = reducedtransposed8.drop(index= \"hour\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "58e0efd1-0bac-4f11-b949-04b6374c05e1",
   "metadata": {},
   "outputs": [],
   "source": [
    "reducedtransposed8.to_csv(\"term8_us_nonus.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "81c09e56-f28a-47ee-ae3a-20049503e4d8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "README.md              original_cbp.ipynb     term7_us_nonus.csv\n",
      "action.yml             term1_us_nonus.csv     term8_us_nonus.csv\n",
      "cbp1.ipynb             term4_us_nonus.csv\n",
      "jfkterm8_us_nonus.csv  term5_us_nonus.csv\n"
     ]
    }
   ],
   "source": [
    "ls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "aecbccf3-1727-4053-acfd-3bc7ee8b7379",
   "metadata": {},
   "outputs": [],
   "source": [
    "await browser.close()"
   ]
  }
 ],
 "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.10.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}