06022023_scrape_all_pdf.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "bdcd6404",
   "metadata": {},
   "source": [
    "# Purposes\n",
    "\n",
    "This jupyter notebook is to scrape the monthly visa statistics data from the U.S. Department of State.\n",
    "\n",
    "This notebook perform two tasks:\n",
    "\n",
    "1. Create a catalog dataframe that includes the URL and timestamp of all webpages to be scraped.\n",
    "2. Batch download online .pdf documents to local drive.\n",
    "\n",
    "\n",
    "This jupyter notebook was created on 05/29/2023, Monday, 17:23 PM"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "334f2dda",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "This jupyter notebook was created on 05/29/2023, Monday, 17:23 PM\n"
     ]
    }
   ],
   "source": [
    "import time as tm\n",
    "start_time = tm.strftime(\"%m/%d/%Y, %A, %H:%M %p\")\n",
    "print(\"This jupyter notebook was created on\", start_time)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "e71a9e38",
   "metadata": {},
   "outputs": [],
   "source": [
    "import time as tm\n",
    "import requests\n",
    "from scrapy import Selector\n",
    "import re\n",
    "import datetime as dt\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from pandas.tseries.offsets import MonthEnd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b7f0d2ae",
   "metadata": {},
   "source": [
    "# URLs\n",
    "\n",
    "The main tasks in this step are:\n",
    "- Capture the unique patterns in the urls that I'd like to scrape\n",
    "- Make sure all urls are included. If the pattern doesn't cover all urls, append them manually to the list."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d30d3af5",
   "metadata": {},
   "source": [
    "## Main URL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "257387f1",
   "metadata": {},
   "outputs": [],
   "source": [
    "main_url = 'https://travel.state.gov/content/travel/en/legal/visa-law0/visa-statistics/immigrant-visa-statistics/monthly-immigrant-visa-issuances.html'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "e8d73f01",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Selector xpath=None data='<html><head><script type=\"text/javasc...'>"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "main_html = requests.get(main_url).content\n",
    "main_selector = Selector(text = main_html)\n",
    "main_selector"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "cae33fea",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "scrapy.selector.unified.Selector"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(main_selector) # This object has no length"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d262e0b1",
   "metadata": {},
   "source": [
    "## Developer Tools in Browser\n",
    "\n",
    "Open the setting of a browser, choose developer tools, and check the elements:\n",
    "\n",
    "- Copy Xpath: `/html/body/div[3]/div[7]/div[2]/div[1]/div[2]/div[2]/div/ul[7]/li[11]/a[1]`\n",
    "- Copy element: `<a href=\"/content/dam/visas/Statistics/Immigrant-Statistics/MonthlyIVIssuances/MARCH%202023%20-%20IV%20Issuances%20by%20FSC%20or%20Place%20of%20Birth%20and%20Visa%20Class.pdf\" target=\"_blank\" adhocenable=\"false\" class=\"pdf_link\">March 2023 - IV Issuances by FSC or Place of Birth and Visa Class<span class=\"far fa-file-pdf pdf_link\"></span></a>`\n",
    "- Copy Selector: `body > div.tsg-rwd-body-frame-row > div.contentbody > div.tsg-rwd-main-copy-frame > div.tsg-rwd-main-copy-body-frame.withrail > div.tsg-rwd-content-page-parsysxxx.parsys > div:nth-child(2) > div > ul:nth-child(15) > li:nth-child(11) > a.pdf_link`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "747a8d27",
   "metadata": {},
   "outputs": [],
   "source": [
    "# contains(@attri-name, \"string-expr\")\n",
    "all_links = main_selector.xpath('//*[contains(@href,\"Class.pdf\")]/@href').extract()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "bf993c54",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['/content/dam/visas/Statistics/Immigrant-Statistics/MonthlyIVIssuances/March%202017%20-%20IV%20Issuances%20by%20Post%20and%20Visa%20Class.pdf',\n",
       " '/content/dam/visas/Statistics/Immigrant-Statistics/MonthlyIVIssuances/APRIL%202017%20-%20IV%20Issuances%20by%20FSC%20and%20Visa%20Class.pdf',\n",
       " '/content/dam/visas/Statistics/Immigrant-Statistics/MonthlyIVIssuances/APRIL%202017%20-%20IV%20Issuances%20by%20Post%20and%20Visa%20Class.pdf',\n",
       " '/content/dam/visas/Statistics/Immigrant-Statistics/MonthlyIVIssuances/MAY%202017%20-%20IV%20Issuances%20by%20FSC%20and%20Visa%20Class.pdf',\n",
       " '/content/dam/visas/Statistics/Immigrant-Statistics/MonthlyIVIssuances/MAY%202017%20-%20IV%20Issuances%20by%20Post%20and%20Visa%20Class.pdf']"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_links[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "f62c10ee",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "73"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fsc_links = [link for link in all_links if \"FSC\" in link]\n",
    "len(fsc_links)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fb3e0e20",
   "metadata": {},
   "source": [
    "## Append additional URLs\n",
    "\n",
    "Although the URLs from a webpage usually have patterns, the pattern is usually not guaranteed. If all urls we want cannot be in(ex)cluded by specific patterns, copy the urls from the webpage and append them."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ba22bcef",
   "metadata": {},
   "source": [
    "- Element: `<a href=\"/content/dam/visas/Statistics/Immigrant-Statistics/MonthlyIVIssuances/March%202017%20-%20IV%20Issuances%20by%20FSC%20and%20Visa%20Class%20-%20Worldwide.pdf\" target=\"_blank\" adhocenable=\"false\" class=\"pdf_link\">March 2017 - IV Issuances by FSC or Place of Birth and Visa Class<span class=\"far fa-file-pdf pdf_link\"></span></a>`\n",
    "- Xpath: `/html/body/div[3]/div[7]/div[2]/div[1]/div[2]/div[2]/div/ul[1]/li[1]/a`\n",
    "- Full Xpath: `/html/body/div[3]/div[7]/div[2]/div[1]/div[2]/div[2]/div/ul[1]/li[1]/a`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "90d39dbc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "74\n",
      "74\n"
     ]
    }
   ],
   "source": [
    "fsc_links.append('/content/dam/visas/Statistics/Immigrant-Statistics/MonthlyIVIssuances/March%202017%20-%20IV%20Issuances%20by%20FSC%20and%20Visa%20Class%20-%20Worldwide.pdf')\n",
    "print(len(fsc_links))\n",
    "fsc_links = list(set(fsc_links))\n",
    "print(len(fsc_links))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7cd3af86",
   "metadata": {},
   "source": [
    "## Quality Check for URLs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "46ca3f51",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['/content/dam/visas/Statistics/Immigrant-Statistics/MonthlyIVIssuances/MARCH%202023%20-%20IV%20Issuances%20by%20FSC%20or%20Place%20of%20Birth%20and%20Visa%20Class.pdf',\n",
       " '/content/dam/visas/Statistics/Immigrant-Statistics/MonthlyIVIssuances/MARCH%202021%20-%20IV%20Issuances%20by%20FSC%20or%20Place%20of%20Birth%20and%20Visa%20Class.pdf',\n",
       " '/content/dam/visas/Statistics/Immigrant-Statistics/MonthlyIVIssuances/MARCH%202018%20-%20IV%20Issuances%20by%20FSC%20and%20Visa%20Class.pdf',\n",
       " '/content/dam/visas/Statistics/Immigrant-Statistics/MonthlyIVIssuances/MARCH%202019%20-%20IV%20Issuances%20by%20FSC%20and%20Visa%20Class.pdf',\n",
       " '/content/dam/visas/Statistics/Immigrant-Statistics/MonthlyIVIssuances/MARCH%202020%20-%20IV%20Issuances%20by%20FSC%20and%20Visa%20Class.pdf',\n",
       " '/content/dam/visas/Statistics/Immigrant-Statistics/MonthlyIVIssuances/MARCH%202022%20-%20IV%20Issuances%20by%20FSC%20or%20Place%20of%20Birth%20and%20Visa%20Class.pdf',\n",
       " '/content/dam/visas/Statistics/Immigrant-Statistics/MonthlyIVIssuances/March%202017%20-%20IV%20Issuances%20by%20FSC%20and%20Visa%20Class%20-%20Worldwide.pdf']"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "[fsc for fsc in fsc_links if ('March' in fsc or 'MARCH' in fsc)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "02d08cad",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2017: 10\n",
      "2018: 12\n",
      "2019: 12\n",
      "2020: 12\n",
      "2021: 12\n",
      "2022: 12\n",
      "2023: 4\n"
     ]
    }
   ],
   "source": [
    "#years = ['2017','2018','2019','2020','2021','2022','2023']\n",
    "# number of links in each year, from March 2017 (10 months in 2017) until March 2023\n",
    "print(\"2017:\",len([link for link in fsc_links if \"2017\" in link]))\n",
    "print(\"2018:\",len([link for link in fsc_links if \"2018\" in link]))\n",
    "print(\"2019:\",len([link for link in fsc_links if \"2019\" in link]))\n",
    "print(\"2020:\",len([link for link in fsc_links if \"202020\" in link])) # not a typo but by the actual links\n",
    "print(\"2021:\",len([link for link in fsc_links if \"2021\" in link]))\n",
    "print(\"2022:\",len([link for link in fsc_links if \"2022\" in link]))\n",
    "print(\"2023:\",len([link for link in fsc_links if \"2023\" in link]))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9904eb12",
   "metadata": {},
   "source": [
    "## Full URLs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "32b28871",
   "metadata": {},
   "outputs": [],
   "source": [
    "prefix = \"https://travel.state.gov\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "72a63e57",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['https://travel.state.gov/content/dam/visas/Statistics/Immigrant-Statistics/MonthlyIVIssuances/JULY2021%20-%20IV%20Issuances%20by%20FSC%20or%20Place%20of%20Birth%20and%20Visa%20Class.pdf',\n",
       " 'https://travel.state.gov/content/dam/visas/Statistics/Immigrant-Statistics/MonthlyIVIssuances/JUNE%202017%20-%20IV%20Issuances%20by%20FSC%20and%20Visa%20Class.pdf',\n",
       " 'https://travel.state.gov/content/dam/visas/Statistics/Immigrant-Statistics/MonthlyIVIssuances/OCTOBER2021%20-%20IV%20Issuances%20by%20FSC%20or%20Place%20of%20Birth%20and%20Visa%20Class.pdf',\n",
       " 'https://travel.state.gov/content/dam/visas/Statistics/Immigrant-Statistics/MonthlyIVIssuances/AUGUST%202022%20-%20IV%20Issuances%20by%20FSC%20or%20Place%20of%20Birth%20and%20Visa%20Class.pdf',\n",
       " 'https://travel.state.gov/content/dam/visas/Statistics/Immigrant-Statistics/MonthlyIVIssuances/MARCH%202023%20-%20IV%20Issuances%20by%20FSC%20or%20Place%20of%20Birth%20and%20Visa%20Class.pdf']"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Concatenate the prefix with every link\n",
    "for i, link in enumerate(fsc_links):\n",
    "    if link.startswith('/content'):\n",
    "        fsc_links[i] = prefix + link\n",
    "fsc_links[:5]      "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "54a7ac9d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# save all the FSC URLs\n",
    "file = open('URL_visa_issuance_FSC.txt','w')\n",
    "for url in fsc_links:\n",
    "    file.write(url + \"\\n\")\n",
    "file.close()    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1387ffa4",
   "metadata": {},
   "source": [
    "# Build Catalog Info"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9afdfd63",
   "metadata": {},
   "source": [
    "## Split URLs by Observed Patterns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "c6a2af25",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['NOVEMBER',\n",
       " '202022',\n",
       " '20-',\n",
       " '20IV',\n",
       " '20Issuances',\n",
       " '20by',\n",
       " '20FSC',\n",
       " '20or',\n",
       " '20Place',\n",
       " '20of',\n",
       " '20Birth',\n",
       " '20and',\n",
       " '20Visa',\n",
       " '20Class.pdf']"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fsc_links[5].split('/')[-1].split('%')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "fc7303c7",
   "metadata": {},
   "outputs": [],
   "source": [
    "month = [None]*len(fsc_links)\n",
    "year = [None]*len(fsc_links)\n",
    "for i, link in enumerate(fsc_links):\n",
    "    month[i] = link.split('/')[-1].split('%')[0].upper()\n",
    "    year[i] = link.split('/')[-1].split('%')[1][2:]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "49527ad6",
   "metadata": {},
   "source": [
    "## Data Cleaning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "832e746b",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'JANUARY', 'MAY', 'JUNE2021', 'OCTOBER2021', 'APRIL', 'AUGUST', 'JANUARY2022', 'SEPT', 'SEPTEMBER', 'NOVEMBER2021', 'MARCH', 'AUGUST2021', 'DECEMBER2021', 'NOVEMBER', 'JUNE', 'DECEMBER', 'FEBRUARY', 'JULY2021', 'MAY2021', 'OCTOBER', 'JULY', 'SEPTEMBER2021'}\n",
      "{'2022', '2023', '2019', '2017', '2018', '-', '2021', '2020'}\n"
     ]
    }
   ],
   "source": [
    "print(set(month))\n",
    "print(set(year))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "401ce89f",
   "metadata": {},
   "source": [
    "Unfortunately but not surprisingly, the URLs are not uniformly structured. When I extract the month and year by their location in a URL from observed patterns, there're exceptions. For example, 'JUNE2021', 'MAY2021', and 'DECEMBER2021', as shown in the month list, should be further split. Also, in the list of year, there is the '-'. \n",
    "\n",
    "Solutions to these problems should be data-dependent. Here, I try to get the index for the problematic elements in the year list."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "a9846ee6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[0, 2, 9, 17, 27, 32, 34, 44, 65]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temp_list = []\n",
    "for i,yr in enumerate(year):\n",
    "    if len(yr)!=4:\n",
    "        temp_list.append(i)\n",
    "temp_list        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "6fc16cad",
   "metadata": {},
   "outputs": [],
   "source": [
    "FSC_catalog = pd.DataFrame({\n",
    "    'url': fsc_links,\n",
    "    'year': year,\n",
    "    'month': month\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "b7ed2c82",
   "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>url</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>https://travel.state.gov/content/dam/visas/Sta...</td>\n",
       "      <td>-</td>\n",
       "      <td>JULY2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>https://travel.state.gov/content/dam/visas/Sta...</td>\n",
       "      <td>-</td>\n",
       "      <td>OCTOBER2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>https://travel.state.gov/content/dam/visas/Sta...</td>\n",
       "      <td>-</td>\n",
       "      <td>SEPTEMBER2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>https://travel.state.gov/content/dam/visas/Sta...</td>\n",
       "      <td>-</td>\n",
       "      <td>JUNE2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>https://travel.state.gov/content/dam/visas/Sta...</td>\n",
       "      <td>-</td>\n",
       "      <td>MAY2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>https://travel.state.gov/content/dam/visas/Sta...</td>\n",
       "      <td>-</td>\n",
       "      <td>JANUARY2022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>https://travel.state.gov/content/dam/visas/Sta...</td>\n",
       "      <td>-</td>\n",
       "      <td>DECEMBER2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>https://travel.state.gov/content/dam/visas/Sta...</td>\n",
       "      <td>-</td>\n",
       "      <td>AUGUST2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>https://travel.state.gov/content/dam/visas/Sta...</td>\n",
       "      <td>-</td>\n",
       "      <td>NOVEMBER2021</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                  url year          month\n",
       "0   https://travel.state.gov/content/dam/visas/Sta...    -       JULY2021\n",
       "2   https://travel.state.gov/content/dam/visas/Sta...    -    OCTOBER2021\n",
       "9   https://travel.state.gov/content/dam/visas/Sta...    -  SEPTEMBER2021\n",
       "17  https://travel.state.gov/content/dam/visas/Sta...    -       JUNE2021\n",
       "27  https://travel.state.gov/content/dam/visas/Sta...    -        MAY2021\n",
       "32  https://travel.state.gov/content/dam/visas/Sta...    -    JANUARY2022\n",
       "34  https://travel.state.gov/content/dam/visas/Sta...    -   DECEMBER2021\n",
       "44  https://travel.state.gov/content/dam/visas/Sta...    -     AUGUST2021\n",
       "65  https://travel.state.gov/content/dam/visas/Sta...    -   NOVEMBER2021"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "FSC_catalog.iloc[temp_list]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "d330001e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'FEBRUARY', 'JANUARY', 'MAY', 'OCTOBER', 'SEPTEMBER', 'APRIL', 'JULY', 'MARCH', 'AUGUST', 'JUNE', 'NOVEMBER', 'DECEMBER'}\n"
     ]
    }
   ],
   "source": [
    "# repeat the creation of the catalog dataframe in the same chunk of these ad-hoc editions\n",
    "# this way, we get the finished catalog dataframe within one chunk\n",
    "FSC_catalog = pd.DataFrame({\n",
    "    'url': fsc_links,\n",
    "    'year': year,\n",
    "    'month': month\n",
    "})\n",
    "# call `.str` method and slice from the other column\n",
    "FSC_catalog['year'].iloc[temp_list] = FSC_catalog['month'].iloc[temp_list].str[-4:]\n",
    "# then, remove the year information from the month column\n",
    "FSC_catalog['month'].iloc[temp_list] = FSC_catalog['month'].iloc[temp_list].str[:-4]\n",
    "# fix the irregular spelling of 'SEPT' for 'SEPTEMBER'\n",
    "FSC_catalog['month'].iloc[FSC_catalog[FSC_catalog['month'] == 'SEPT'].index] = 'SEPTEMBER'\n",
    "# confirm the month variable only includes the 12 months\n",
    "print(set(FSC_catalog['month']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "7dedbcd1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(74, 3)\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>url</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>https://travel.state.gov/content/dam/visas/Sta...</td>\n",
       "      <td>2021</td>\n",
       "      <td>JULY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>https://travel.state.gov/content/dam/visas/Sta...</td>\n",
       "      <td>2021</td>\n",
       "      <td>OCTOBER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>https://travel.state.gov/content/dam/visas/Sta...</td>\n",
       "      <td>2021</td>\n",
       "      <td>SEPTEMBER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>https://travel.state.gov/content/dam/visas/Sta...</td>\n",
       "      <td>2021</td>\n",
       "      <td>JUNE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>https://travel.state.gov/content/dam/visas/Sta...</td>\n",
       "      <td>2021</td>\n",
       "      <td>MAY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>https://travel.state.gov/content/dam/visas/Sta...</td>\n",
       "      <td>2022</td>\n",
       "      <td>JANUARY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>https://travel.state.gov/content/dam/visas/Sta...</td>\n",
       "      <td>2021</td>\n",
       "      <td>DECEMBER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>https://travel.state.gov/content/dam/visas/Sta...</td>\n",
       "      <td>2021</td>\n",
       "      <td>AUGUST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>https://travel.state.gov/content/dam/visas/Sta...</td>\n",
       "      <td>2021</td>\n",
       "      <td>NOVEMBER</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                  url  year      month\n",
       "0   https://travel.state.gov/content/dam/visas/Sta...  2021       JULY\n",
       "2   https://travel.state.gov/content/dam/visas/Sta...  2021    OCTOBER\n",
       "9   https://travel.state.gov/content/dam/visas/Sta...  2021  SEPTEMBER\n",
       "17  https://travel.state.gov/content/dam/visas/Sta...  2021       JUNE\n",
       "27  https://travel.state.gov/content/dam/visas/Sta...  2021        MAY\n",
       "32  https://travel.state.gov/content/dam/visas/Sta...  2022    JANUARY\n",
       "34  https://travel.state.gov/content/dam/visas/Sta...  2021   DECEMBER\n",
       "44  https://travel.state.gov/content/dam/visas/Sta...  2021     AUGUST\n",
       "65  https://travel.state.gov/content/dam/visas/Sta...  2021   NOVEMBER"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# fixed!\n",
    "print(FSC_catalog.shape)\n",
    "FSC_catalog.iloc[temp_list]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bc2750d3",
   "metadata": {},
   "source": [
    "## Create Timestamp `pd.to_datetime() + MonthEnd(0)`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "21c8080c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# concatenate the year and month data\n",
    "FSC_catalog['mmyy'] = FSC_catalog['year'].str.cat(FSC_catalog['month'], sep='-')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "67a33441",
   "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>url</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>mmyy</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>https://travel.state.gov/content/dam/visas/Sta...</td>\n",
       "      <td>2017</td>\n",
       "      <td>MARCH</td>\n",
       "      <td>2017-03-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>https://travel.state.gov/content/dam/visas/Sta...</td>\n",
       "      <td>2017</td>\n",
       "      <td>APRIL</td>\n",
       "      <td>2017-04-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>https://travel.state.gov/content/dam/visas/Sta...</td>\n",
       "      <td>2017</td>\n",
       "      <td>MAY</td>\n",
       "      <td>2017-05-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>https://travel.state.gov/content/dam/visas/Sta...</td>\n",
       "      <td>2017</td>\n",
       "      <td>JUNE</td>\n",
       "      <td>2017-06-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>https://travel.state.gov/content/dam/visas/Sta...</td>\n",
       "      <td>2017</td>\n",
       "      <td>JULY</td>\n",
       "      <td>2017-07-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>https://travel.state.gov/content/dam/visas/Sta...</td>\n",
       "      <td>2022</td>\n",
       "      <td>DECEMBER</td>\n",
       "      <td>2022-12-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>https://travel.state.gov/content/dam/visas/Sta...</td>\n",
       "      <td>2023</td>\n",
       "      <td>JANUARY</td>\n",
       "      <td>2023-01-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>https://travel.state.gov/content/dam/visas/Sta...</td>\n",
       "      <td>2023</td>\n",
       "      <td>FEBRUARY</td>\n",
       "      <td>2023-02-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>https://travel.state.gov/content/dam/visas/Sta...</td>\n",
       "      <td>2023</td>\n",
       "      <td>MARCH</td>\n",
       "      <td>2023-03-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>https://travel.state.gov/content/dam/visas/Sta...</td>\n",
       "      <td>2023</td>\n",
       "      <td>APRIL</td>\n",
       "      <td>2023-04-30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>74 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                  url  year     month  \\\n",
       "0   https://travel.state.gov/content/dam/visas/Sta...  2017     MARCH   \n",
       "1   https://travel.state.gov/content/dam/visas/Sta...  2017     APRIL   \n",
       "2   https://travel.state.gov/content/dam/visas/Sta...  2017       MAY   \n",
       "3   https://travel.state.gov/content/dam/visas/Sta...  2017      JUNE   \n",
       "4   https://travel.state.gov/content/dam/visas/Sta...  2017      JULY   \n",
       "..                                                ...   ...       ...   \n",
       "69  https://travel.state.gov/content/dam/visas/Sta...  2022  DECEMBER   \n",
       "70  https://travel.state.gov/content/dam/visas/Sta...  2023   JANUARY   \n",
       "71  https://travel.state.gov/content/dam/visas/Sta...  2023  FEBRUARY   \n",
       "72  https://travel.state.gov/content/dam/visas/Sta...  2023     MARCH   \n",
       "73  https://travel.state.gov/content/dam/visas/Sta...  2023     APRIL   \n",
       "\n",
       "         mmyy  \n",
       "0  2017-03-31  \n",
       "1  2017-04-30  \n",
       "2  2017-05-31  \n",
       "3  2017-06-30  \n",
       "4  2017-07-31  \n",
       "..        ...  \n",
       "69 2022-12-31  \n",
       "70 2023-01-31  \n",
       "71 2023-02-28  \n",
       "72 2023-03-31  \n",
       "73 2023-04-30  \n",
       "\n",
       "[74 rows x 4 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# https://stackoverflow.com/questions/37354105/find-the-end-of-the-month-of-a-pandas-dataframe-series\n",
    "from pandas.tseries.offsets import MonthEnd\n",
    "# because the visa statistics is the summary as of the end of the month, I set the date to the last date\n",
    "# use `+ MonthEnd(0)`\n",
    "FSC_catalog['mmyy'] =\\\n",
    "pd.to_datetime(FSC_catalog['mmyy']\\\n",
    "               , infer_datetime_format=True, errors = 'ignore')\\\n",
    "+ MonthEnd(0)\n",
    "FSC_catalog = FSC_catalog.sort_values('mmyy', ascending=True).reset_index(drop=True)\n",
    "FSC_catalog"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "05edb37c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The catalog data was last saved on 06/01/2023, Thursday, 22:47 PM\n"
     ]
    }
   ],
   "source": [
    "import time as tm\n",
    "# output the catalog data\n",
    "FSC_catalog.to_csv(\"FSC_catalog.csv\", index=False)\n",
    "print(\"The catalog data was last saved on\",tm.strftime(\"%m/%d/%Y, %A, %H:%M %p\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2da0ef1d",
   "metadata": {},
   "source": [
    "# Download ALL .pdf Documents\n",
    "\n",
    "Python only needs the full URLs of online .pdf documents in order to save them to local drive."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "5e417d5b",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "from urllib import request"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "e9a61ba1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 2.55 s, sys: 516 ms, total: 3.07 s\n",
      "Wall time: 49 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "for i in range(len(FSC_catalog)):\n",
    "    fullfilename = os.getcwd() + '/iv/'+ 'iv_'+str(FSC_catalog['mmyy'][i])[:10] + '.pdf'\n",
    "    request.urlretrieve(FSC_catalog['url'][i], fullfilename)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "7d6d2bd1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "We have just downloaded 74 .pdf files via web-scraping.\n",
      "\n",
      "['iv_2020-07-31.pdf', 'iv_2020-02-29.pdf', 'iv_2019-01-31.pdf', 'iv_2018-02-28.pdf', 'iv_2017-06-30.pdf', 'iv_2018-07-31.pdf', 'iv_2021-01-31.pdf', 'iv_2022-06-30.pdf', 'iv_2022-08-31.pdf', 'iv_2021-11-30.pdf', 'iv_2017-04-30.pdf', 'iv_2018-09-30.pdf', 'iv_2022-04-30.pdf', 'iv_2021-03-31.pdf', 'iv_2017-08-31.pdf', 'iv_2018-05-31.pdf', 'iv_2019-03-31.pdf', 'iv_2020-05-31.pdf', 'iv_2019-11-30.pdf', 'iv_2020-09-30.pdf']\n"
     ]
    }
   ],
   "source": [
    "print(\"We have just downloaded\",len(os.listdir('iv')), \".pdf files via web-scraping.\\n\")\n",
    "print(os.listdir('iv')[:20])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "0f4dd692",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "This jupyter notebook was last completed at 06/02/2023, Friday, 00:07 AM\n"
     ]
    }
   ],
   "source": [
    "print(\"This jupyter notebook was last completed at\", tm.strftime(\"%m/%d/%Y, %A, %H:%M %p\"))"
   ]
  }
 ],
 "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"
  },
  "latex_envs": {
   "LaTeX_envs_menu_present": true,
   "autoclose": false,
   "autocomplete": true,
   "bibliofile": "biblio.bib",
   "cite_by": "apalike",
   "current_citInitial": 1,
   "eqLabelWithNumbers": true,
   "eqNumInitial": 1,
   "hotkeys": {
    "equation": "Ctrl-E",
    "itemize": "Ctrl-I"
   },
   "labels_anchors": false,
   "latex_user_defs": false,
   "report_style_numbering": false,
   "user_envs_cfg": false
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Web Scrape and Download All .pdf Documents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "609px",
    "left": "115px",
    "top": "151.711px",
    "width": "260px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  },
  "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()) "
    }
   },
   "oldHeight": 122.852,
   "position": {
    "height": "40px",
    "left": "1170px",
    "right": "20px",
    "top": "120px",
    "width": "250px"
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "varInspector_section_display": "none",
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}