07292023_scrape_speedtest.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "2f9c3bc0",
   "metadata": {},
   "source": [
    "## How long does web-scraping take?\n",
    "\n",
    "Author: Tiangeng Lu\n",
    "\n",
    "Date: July 29, 2023\n",
    "\n",
    "- This notebook shows the processing time of scraping-cleaning web tables to dataframes. \n",
    "- I use U.S. Department of Homeland Security/SEVIS STEM enrollment data.\n",
    "- There are 23 webpages with tables.\n",
    "- Each table has between 20k to 23k rows and about 6 columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "0303b2ef",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 618 ms, sys: 127 ms, total: 746 ms\n",
      "Wall time: 1.31 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "import requests\n",
    "from scrapy import Selector\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import os\n",
    "from datetime import datetime\n",
    "from pandas.tseries.offsets import MonthEnd\n",
    "import datetime as dt\n",
    "os.chdir('/Users/tiangeng/Documents/Python Files')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3de94b6c",
   "metadata": {},
   "source": [
    "### Get URLS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "88064d81",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 74.8 ms, sys: 23.5 ms, total: 98.3 ms\n",
      "Wall time: 1.34 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "# urls\n",
    "main_url = 'https://studyinthestates.dhs.gov/sevis-by-the-numbers/sevis-by-the-numbers-data'\n",
    "main_html = requests.get(main_url).content\n",
    "main_selector = Selector(text = main_html)\n",
    "# how many elements? 266 as of 07/27/2023\n",
    "len(main_selector.xpath('//*'))\n",
    "\n",
    "# <a data-entity-substitution=\"canonical\" data-entity-type=\"node\" data-entity-uuid=\"98b24128-4146-433f-9288-77fad251a802\" href=\"/sevis-data-mapping-tool/march-2023-stem-sevis-data-mapping-tool-data\">March 2023 STEM SEVIS Data Mapping Tool Data</a>\n",
    "# extract from selector\n",
    "all_links = main_selector.xpath('//*[contains(@href,\"sevis-data-mapping-tool-data\")]/@href').extract()\n",
    "# urls to stem students\n",
    "stem_links = [link for link in all_links if '-stem-' in link]\n",
    "# https://studyinthestates.dhs.gov/sevis-data-mapping-tool/march-2023-stem-sevis-data-mapping-tool-data\n",
    "prefix = \"https://studyinthestates.dhs.gov\"\n",
    "stem_links = [prefix + link for link in stem_links if link.startswith('/sevis')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "a769587b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 11.9 ms, sys: 4.84 ms, total: 16.7 ms\n",
      "Wall time: 38.8 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "stem_catalog = pd.DataFrame(data = {\n",
    "    'url': stem_links,\n",
    "    'year':[link.split('/')[4].split('-')[1].upper() for link in stem_links],\n",
    "    'month': [link.split('/')[4].split('-')[0].upper() for link in stem_links]})\n",
    "stem_catalog['stamp'] = (stem_catalog['month'].str.cat(stem_catalog['year'], sep = '-'))\n",
    "stem_catalog['stamp'] = [datetime.strptime(stamp, \"%B-%Y\").strftime(\"%Y-%m-%d\") for stamp in stem_catalog['stamp']]\n",
    "stem_catalog['stamp'] = (pd.to_datetime(stem_catalog['stamp'], errors = 'ignore') + MonthEnd()).dt.date\n",
    "stem_catalog = stem_catalog.sort_values('stamp', ascending = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "fa3efcae",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('2015', 2), ('2016', 2), ('2017', 3), ('2018', 4), ('2019', 1), ('2020', 3), ('2021', 3), ('2022', 3), ('2023', 3)]\n"
     ]
    }
   ],
   "source": [
    "from collections import Counter\n",
    "stem_counter = Counter(stem_catalog['year'])\n",
    "print(sorted(stem_counter.items()))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "62cf8f1f",
   "metadata": {},
   "source": [
    "### Extracting Elements from URLs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "2607f28f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "elements_start = 11:59:23\n",
      "Are there any STEM webpages have more than 1 tables?\n",
      " []\n",
      "ALERT! Skip the following because it's blank:\n",
      " [8]\n",
      "Also, remember to update the catalog!\n",
      "23\n",
      "elements_finish = 12:00:02\n",
      "CPU times: user 6.95 s, sys: 272 ms, total: 7.22 s\n",
      "Wall time: 38.9 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "now = datetime.now()\n",
    "elements_start = now.strftime(\"%H:%M:%S\")\n",
    "print(\"elements_start =\", elements_start)\n",
    "stem_elements = [None] * len(stem_catalog)\n",
    "\n",
    "for i in range(len(stem_catalog)):\n",
    "    html = requests.get(stem_catalog['url'][i]).content\n",
    "    sel = Selector(text = html)\n",
    "    stem_elements[i] = sel.xpath('//table').extract() \n",
    "print(\"Are there any STEM webpages have more than 1 tables?\\n\",[len(element) for element in stem_elements if len(element) > 1])\n",
    "# The webpage of STEM July 2018 is blank as of 07/27/2023. I found this out after seeing an \"out of range\" error message.\n",
    "[len(element) for element in stem_elements]\n",
    "# which webpage doesn't have tables?\n",
    "blank_page_id = [i for i, element in enumerate(stem_elements) if len(element) < 1]\n",
    "if len(blank_page_id) == 0:\n",
    "    print(\"Looks good. Go ahead clean the tables!\")\n",
    "else:\n",
    "    print(\"ALERT! Skip the following because it's blank:\\n\", str(blank_page_id))\n",
    "    print(\"Also, remember to update the catalog!\")\n",
    "# select data row(s) to remove\n",
    "stem_catalog = stem_catalog.drop(blank_page_id, axis = 0)\n",
    "stem_catalog = stem_catalog.reset_index()\n",
    "# update the elements in stem_elements by removing the blank one\n",
    "stem_elements = [element for element in stem_elements if len(element) > 0]\n",
    "print(len(stem_elements))\n",
    "now = datetime.now()\n",
    "elements_finish = now.strftime(\"%H:%M:%S\")\n",
    "print(\"elements_finish =\", elements_finish)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "528aabb6",
   "metadata": {},
   "source": [
    "### Converting Elements to Dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "c10f0a9b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Element 0 begins: 13:37:09\n",
      "Element 0 ends: 13:37:56\n",
      "Element 1 begins: 13:37:56\n",
      "Element 1 ends: 13:38:43\n",
      "Element 2 begins: 13:38:43\n",
      "Element 2 ends: 13:39:30\n",
      "Element 3 begins: 13:39:30\n",
      "Element 3 ends: 13:40:18\n",
      "Element 4 begins: 13:40:18\n",
      "Element 4 ends: 13:41:05\n",
      "Element 5 begins: 13:41:05\n",
      "Element 5 ends: 13:41:53\n",
      "Element 6 begins: 13:41:53\n",
      "Element 6 ends: 13:42:40\n",
      "Element 7 begins: 13:42:40\n",
      "Element 7 ends: 13:43:27\n",
      "Element 8 begins: 13:43:27\n",
      "Element 8 ends: 13:44:14\n",
      "Element 9 begins: 13:44:14\n",
      "Element 9 ends: 13:45:02\n",
      "Element 10 begins: 13:45:02\n",
      "Element 10 ends: 13:45:49\n",
      "Element 11 begins: 13:45:49\n",
      "Element 11 ends: 13:46:37\n",
      "Element 12 begins: 13:46:37\n",
      "Element 12 ends: 13:47:24\n",
      "Element 13 begins: 13:47:24\n",
      "Element 13 ends: 13:48:12\n",
      "Element 14 begins: 13:48:12\n",
      "Element 14 ends: 13:48:59\n",
      "Element 15 begins: 13:48:59\n",
      "Element 15 ends: 13:49:47\n",
      "Element 16 begins: 13:49:47\n",
      "Element 16 ends: 13:50:34\n",
      "Element 17 begins: 13:50:34\n",
      "Element 17 ends: 13:51:22\n",
      "Element 18 begins: 13:51:22\n",
      "Element 18 ends: 13:52:10\n",
      "Element 19 begins: 13:52:10\n",
      "Element 19 ends: 13:52:57\n",
      "Element 20 begins: 13:52:57\n",
      "Element 20 ends: 13:53:48\n",
      "Element 21 begins: 13:53:48\n",
      "Element 21 ends: 13:54:37\n",
      "Element 22 begins: 13:54:37\n",
      "Element 22 ends: 13:55:27\n",
      "CPU times: user 17min 40s, sys: 9.72 s, total: 17min 50s\n",
      "Wall time: 18min 18s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "process_begin = [None] * len(stem_catalog)\n",
    "process_end = [None] * len(stem_catalog)\n",
    "STEM_dfs_copy = [None] * len(stem_catalog)\n",
    "for i, element in enumerate(stem_elements):\n",
    "    process_begin[i] = datetime.now().strftime(\"%H:%M:%S\")\n",
    "    print(\"Element\",str(i), \"begins:\",process_begin[i])\n",
    "    STEM_dfs_copy = [pd.read_html(element[0], header = 0)[0] for element in stem_elements]\n",
    "    process_end[i] = datetime.now().strftime(\"%H:%M:%S\")\n",
    "    print(\"Element\",str(i),\"ends:\",process_end[i])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "5de76da7",
   "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>name</th>\n",
       "      <th>KB</th>\n",
       "      <th>row</th>\n",
       "      <th>begin</th>\n",
       "      <th>end</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>stem_2015-09-30</td>\n",
       "      <td>7175.1</td>\n",
       "      <td>22045</td>\n",
       "      <td>13:37:09</td>\n",
       "      <td>13:37:56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>stem_2015-12-31</td>\n",
       "      <td>7472.3</td>\n",
       "      <td>22956</td>\n",
       "      <td>13:37:56</td>\n",
       "      <td>13:38:43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>stem_2016-03-31</td>\n",
       "      <td>7481.1</td>\n",
       "      <td>22982</td>\n",
       "      <td>13:38:43</td>\n",
       "      <td>13:39:30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>stem_2016-11-30</td>\n",
       "      <td>7636.5</td>\n",
       "      <td>23459</td>\n",
       "      <td>13:39:30</td>\n",
       "      <td>13:40:18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>stem_2017-03-31</td>\n",
       "      <td>7598.8</td>\n",
       "      <td>23345</td>\n",
       "      <td>13:40:18</td>\n",
       "      <td>13:41:05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>stem_2017-05-31</td>\n",
       "      <td>7540.9</td>\n",
       "      <td>23167</td>\n",
       "      <td>13:41:05</td>\n",
       "      <td>13:41:53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>stem_2017-12-31</td>\n",
       "      <td>7850.7</td>\n",
       "      <td>23540</td>\n",
       "      <td>13:41:53</td>\n",
       "      <td>13:42:40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>stem_2018-03-31</td>\n",
       "      <td>7709.7</td>\n",
       "      <td>23685</td>\n",
       "      <td>13:42:40</td>\n",
       "      <td>13:43:27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>stem_2018-08-31</td>\n",
       "      <td>7867.0</td>\n",
       "      <td>24147</td>\n",
       "      <td>13:43:27</td>\n",
       "      <td>13:44:14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>stem_2018-12-31</td>\n",
       "      <td>7792.3</td>\n",
       "      <td>23916</td>\n",
       "      <td>13:44:14</td>\n",
       "      <td>13:45:02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>stem_2019-03-31</td>\n",
       "      <td>8079.3</td>\n",
       "      <td>24789</td>\n",
       "      <td>13:45:02</td>\n",
       "      <td>13:45:49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>stem_2020-01-31</td>\n",
       "      <td>8197.3</td>\n",
       "      <td>25163</td>\n",
       "      <td>13:45:49</td>\n",
       "      <td>13:46:37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>stem_2020-09-30</td>\n",
       "      <td>5234.3</td>\n",
       "      <td>16046</td>\n",
       "      <td>13:46:37</td>\n",
       "      <td>13:47:24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>stem_2020-12-31</td>\n",
       "      <td>7859.7</td>\n",
       "      <td>24129</td>\n",
       "      <td>13:47:24</td>\n",
       "      <td>13:48:12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>stem_2021-03-31</td>\n",
       "      <td>7950.4</td>\n",
       "      <td>24407</td>\n",
       "      <td>13:48:12</td>\n",
       "      <td>13:48:59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>stem_2021-06-30</td>\n",
       "      <td>7858.4</td>\n",
       "      <td>24148</td>\n",
       "      <td>13:48:59</td>\n",
       "      <td>13:49:47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>stem_2021-10-31</td>\n",
       "      <td>8249.9</td>\n",
       "      <td>25326</td>\n",
       "      <td>13:49:47</td>\n",
       "      <td>13:50:34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>stem_2022-05-31</td>\n",
       "      <td>8339.1</td>\n",
       "      <td>25623</td>\n",
       "      <td>13:50:34</td>\n",
       "      <td>13:51:22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>stem_2022-09-30</td>\n",
       "      <td>8075.4</td>\n",
       "      <td>24819</td>\n",
       "      <td>13:51:22</td>\n",
       "      <td>13:52:10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>stem_2022-11-30</td>\n",
       "      <td>8685.9</td>\n",
       "      <td>26689</td>\n",
       "      <td>13:52:10</td>\n",
       "      <td>13:52:57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>stem_2023-01-31</td>\n",
       "      <td>8684.7</td>\n",
       "      <td>26686</td>\n",
       "      <td>13:52:57</td>\n",
       "      <td>13:53:48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>stem_2023-02-28</td>\n",
       "      <td>8847.0</td>\n",
       "      <td>27185</td>\n",
       "      <td>13:53:48</td>\n",
       "      <td>13:54:37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>stem_2023-03-31</td>\n",
       "      <td>8768.7</td>\n",
       "      <td>26943</td>\n",
       "      <td>13:54:37</td>\n",
       "      <td>13:55:27</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               name      KB    row     begin       end\n",
       "0   stem_2015-09-30  7175.1  22045  13:37:09  13:37:56\n",
       "1   stem_2015-12-31  7472.3  22956  13:37:56  13:38:43\n",
       "2   stem_2016-03-31  7481.1  22982  13:38:43  13:39:30\n",
       "3   stem_2016-11-30  7636.5  23459  13:39:30  13:40:18\n",
       "4   stem_2017-03-31  7598.8  23345  13:40:18  13:41:05\n",
       "5   stem_2017-05-31  7540.9  23167  13:41:05  13:41:53\n",
       "6   stem_2017-12-31  7850.7  23540  13:41:53  13:42:40\n",
       "7   stem_2018-03-31  7709.7  23685  13:42:40  13:43:27\n",
       "8   stem_2018-08-31  7867.0  24147  13:43:27  13:44:14\n",
       "9   stem_2018-12-31  7792.3  23916  13:44:14  13:45:02\n",
       "10  stem_2019-03-31  8079.3  24789  13:45:02  13:45:49\n",
       "11  stem_2020-01-31  8197.3  25163  13:45:49  13:46:37\n",
       "12  stem_2020-09-30  5234.3  16046  13:46:37  13:47:24\n",
       "13  stem_2020-12-31  7859.7  24129  13:47:24  13:48:12\n",
       "14  stem_2021-03-31  7950.4  24407  13:48:12  13:48:59\n",
       "15  stem_2021-06-30  7858.4  24148  13:48:59  13:49:47\n",
       "16  stem_2021-10-31  8249.9  25326  13:49:47  13:50:34\n",
       "17  stem_2022-05-31  8339.1  25623  13:50:34  13:51:22\n",
       "18  stem_2022-09-30  8075.4  24819  13:51:22  13:52:10\n",
       "19  stem_2022-11-30  8685.9  26689  13:52:10  13:52:57\n",
       "20  stem_2023-01-31  8684.7  26686  13:52:57  13:53:48\n",
       "21  stem_2023-02-28  8847.0  27185  13:53:48  13:54:37\n",
       "22  stem_2023-03-31  8768.7  26943  13:54:37  13:55:27"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame(data = {\n",
    "    'name': ['stem_' + str(stamp) for stamp in stem_catalog['stamp']],\n",
    "    'KB': [round(getsizeof(df)*0.001,1) for df in STEM_dfs_copy],\n",
    "    'row': [df.shape[0] for df in STEM_dfs_copy],\n",
    "    'begin': process_begin,\n",
    "    'end': process_end\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "86c8ce17",
   "metadata": {},
   "outputs": [],
   "source": [
    "stem_process_time = pd.DataFrame(data = {\n",
    "    'name': ['stem_' + str(stamp) for stamp in stem_catalog['stamp']],\n",
    "    'KB': [round(getsizeof(df)*0.001,1) for df in STEM_dfs_copy],\n",
    "    'row': [df.shape[0] for df in STEM_dfs_copy],\n",
    "    'begin': process_begin,\n",
    "    'end': process_end\n",
    "})\n",
    "stem_process_time.to_csv('stem_process_time.csv', index = False)"
   ]
  }
 ],
 "metadata": {
  "hide_input": false,
  "kernelspec": {
   "display_name": "Python 3.9.5 64-bit",
   "language": "python",
   "name": "python395jvsc74a57bd07812ea015bdcee6f23a998adcdd2ef97c151c0c241b7b7070987d9313e41299d"
  },
  "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.9.7"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}