Transform HTML Table to DataFrame.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "663d4548-7279-4fb7-a90f-feb96db1adcf",
   "metadata": {},
   "source": [
    "# Transform HTML Table into DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "f153df97-7831-4053-a7fb-4fd1a8b12d3f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# import required libraries\n",
    "from bs4 import BeautifulSoup as bs\n",
    "import requests\n",
    "import pandas as pd\n",
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "5ec65641-29ec-4e4a-8753-3ca63ab36453",
   "metadata": {},
   "outputs": [],
   "source": [
    "url = \"https://en.wikipedia.org/wiki/List_of_postal_codes_of_Canada:_M\"\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "ef357fba-cb98-49c3-aa07-0eefefb78cff",
   "metadata": {},
   "outputs": [],
   "source": [
    "# send http request and get response using requests\n",
    "response = requests.get(url)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "4f0bc53b-01c2-45c2-8940-06195fbfa6e0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Response [200]>\n"
     ]
    }
   ],
   "source": [
    "print(response)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "03b7430b-4998-49d5-9d46-641d66f3783d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# parse response content using beautifulsoup\n",
    "html_page = bs(response.content, 'html.parser')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "ecc01116-dcf0-464b-a0cd-f183c70b62da",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get the first table tag and find all <tr> tag in the form of list\n",
    "tableData = html_page.table.find_all('tr')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "1ddb7243-7b91-4e6f-a543-49b21d7707a8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Guildwood,Morningside,West Hill'"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check the tableData\n",
    "re.findall(\"\\((.*?)\\)\", tableData[3].find('td').find('span').text)[0].replace(' / ', ',')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "b94fade1-abbd-41ce-9251-f99002f2f36c",
   "metadata": {},
   "outputs": [],
   "source": [
    "def parse_table(tableData):\n",
    "    data = {'Postal Code': [], 'Borough':[], 'Neighborhood':[]}\n",
    "    for row in tableData:\n",
    "        for td in row.find_all('td'):\n",
    "            code = td.find('b').text\n",
    "            borough_ = td.find('span').text\n",
    "            # skip if borough_ is  Not assigned\n",
    "            if borough_ == 'Not assigned':\n",
    "                continue\n",
    "                \n",
    "            borough = borough_.split('(')[0]\n",
    "            neighborhood = re.findall('\\((.*?)\\)', borough_)[0].replace(' / ', ',')\n",
    "            \n",
    "            # append into the dict\n",
    "            data['Postal Code'].append(code)\n",
    "            data['Borough'].append(borough)\n",
    "            data['Neighborhood'].append(neighborhood)\n",
    "    \n",
    "    \n",
    "    return data   \n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "b94935f2-4e22-4bbf-8324-d79660b95d7d",
   "metadata": {},
   "outputs": [],
   "source": [
    "data = parse_table(tableData)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "ef4525fa-4d62-47a0-be56-a46a16689a15",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "11187678-4539-4150-8734-4122ac98cb36",
   "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>Postal Code</th>\n",
       "      <th>Borough</th>\n",
       "      <th>Neighborhood</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>M3A</td>\n",
       "      <td>North York</td>\n",
       "      <td>Parkwoods</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>M4A</td>\n",
       "      <td>North York</td>\n",
       "      <td>Victoria Village</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>M5A</td>\n",
       "      <td>Downtown Toronto</td>\n",
       "      <td>Regent Park,Harbourfront</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>M6A</td>\n",
       "      <td>North York</td>\n",
       "      <td>Lawrence Manor,Lawrence Heights</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>M7A</td>\n",
       "      <td>Queen's Park</td>\n",
       "      <td>Ontario Provincial Government</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>M8X</td>\n",
       "      <td>Etobicoke</td>\n",
       "      <td>The Kingsway,Montgomery Road,Old Mill North</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>M4Y</td>\n",
       "      <td>Downtown Toronto</td>\n",
       "      <td>Church and Wellesley</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>M7Y</td>\n",
       "      <td>East TorontoBusiness reply mail Processing Cen...</td>\n",
       "      <td>Enclave of M4L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>M8Y</td>\n",
       "      <td>Etobicoke</td>\n",
       "      <td>Old Mill South,King's Mill Park,Sunnylea,Humbe...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>M8Z</td>\n",
       "      <td>Etobicoke</td>\n",
       "      <td>Mimico NW,The Queensway West,South of Bloor,Ki...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>103 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    Postal Code                                            Borough  \\\n",
       "0           M3A                                         North York   \n",
       "1           M4A                                         North York   \n",
       "2           M5A                                   Downtown Toronto   \n",
       "3           M6A                                         North York   \n",
       "4           M7A                                       Queen's Park   \n",
       "..          ...                                                ...   \n",
       "98          M8X                                          Etobicoke   \n",
       "99          M4Y                                   Downtown Toronto   \n",
       "100         M7Y  East TorontoBusiness reply mail Processing Cen...   \n",
       "101         M8Y                                          Etobicoke   \n",
       "102         M8Z                                          Etobicoke   \n",
       "\n",
       "                                          Neighborhood  \n",
       "0                                            Parkwoods  \n",
       "1                                     Victoria Village  \n",
       "2                             Regent Park,Harbourfront  \n",
       "3                      Lawrence Manor,Lawrence Heights  \n",
       "4                        Ontario Provincial Government  \n",
       "..                                                 ...  \n",
       "98         The Kingsway,Montgomery Road,Old Mill North  \n",
       "99                                Church and Wellesley  \n",
       "100                                     Enclave of M4L  \n",
       "101  Old Mill South,King's Mill Park,Sunnylea,Humbe...  \n",
       "102  Mimico NW,The Queensway West,South of Bloor,Ki...  \n",
       "\n",
       "[103 rows x 3 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7d6486ce-7b4c-4aa1-be88-a6e44b125276",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "48bf8fbb-3539-45be-8449-fd52ab12ef28",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b6ff6e3d-fb8a-48f6-bf84-f47b8a3b72d1",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7a822d77-2786-42be-b647-61ade8b8aa29",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "8fc26264-5c6e-4cf0-8480-9e38296d8d97",
   "metadata": {},
   "source": [
    "...."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "104d6a0a-2fea-461f-a8c9-0c22114e046b",
   "metadata": {},
   "source": [
    "# `any()` in Pandas DataFrame"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "44a06fd4-37ae-4991-9562-36d2de22a724",
   "metadata": {},
   "source": [
    "`syntax:DataFrame.any(axis=0, bool_only=None, skipna=True, level=None, **kwargs)`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "ca303a04-ae36-411b-b664-cdeacaf955c5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Example\n",
    "import pandas as pd\n",
    "\n",
    "data = [[True, False, True], [False, False, False]]\n",
    "df = pd.DataFrame(data, columns=['col1', 'col2', 'col3'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "719a7f12-6b95-49ea-9945-234b9a31d01f",
   "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>col1</th>\n",
       "      <th>col2</th>\n",
       "      <th>col3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    col1   col2   col3\n",
       "0   True  False   True\n",
       "1  False  False  False"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6613d97f-580a-4a11-8c4a-4b71ea55ef05",
   "metadata": {},
   "source": [
    "Check if any value in each row(index) is `True`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a5b0e0b4-89d1-4812-b967-a6905d950169",
   "metadata": {},
   "source": [
    "Whether each column contains at least one `True` element (the default)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "0f633c2d-c9a1-4f54-bdfc-2a75c9243945",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "col1     True\n",
       "col2    False\n",
       "col3     True\n",
       "dtype: bool"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.any()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0ae347a8-fb7a-4481-90ae-b074addcd5ad",
   "metadata": {},
   "source": [
    "Q: What is the use of `any()` in pandas dataframe?"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e9d0bfa0-2146-402e-bbe5-b2a9807672a3",
   "metadata": {},
   "source": [
    "Aggregating over the columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "afc34fb0-1e0e-414c-b1b6-8aee94c7d934",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     True\n",
       "1    False\n",
       "dtype: bool"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.any(axis='columns')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "68839567-c747-4e69-93bc-c41714dc1392",
   "metadata": {},
   "source": [
    "Aggregating over the entire DataFrame with `axis=None`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "56da1702-b14c-4401-8ef7-8a0c3d66a47c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.any(axis=None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c1afd4ab-2e3d-4983-b1c7-37bf148ba72a",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.9.10"
  },
  "widgets": {
   "application/vnd.jupyter.widget-state+json": {
    "state": {},
    "version_major": 2,
    "version_minor": 0
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}