#5 Read Excel in Python Pandas.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Check documentation: \n",
    "# https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_excel.html\n",
    "#\n",
    "# Video about this notebook: \n",
    "# https://youtu.be/--zFZjSlfK8"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>Address</th>\n",
       "      <th>Content</th>\n",
       "      <th>Status Code</th>\n",
       "      <th>Status</th>\n",
       "      <th>Contains: '\"article-text-box\"'</th>\n",
       "      <th>Contains: '\"video-player\"'</th>\n",
       "      <th>Contains: '\"article-body'</th>\n",
       "      <th>Contains: '\\(sda\\)'</th>\n",
       "      <th>Contains: '\"scribble\"'</th>\n",
       "      <th>Contains: 'basic-image--free-desktop'</th>\n",
       "      <th>Contains: '&lt;iframe'</th>\n",
       "      <th>Contains: 'layout-item--flex-desktop-4'</th>\n",
       "      <th>Contains: '\\(sda\\/'</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>https://www.blick.ch/people-tv/international/r...</td>\n",
       "      <td>text/html; charset=utf-8</td>\n",
       "      <td>200</td>\n",
       "      <td>Connection Refused</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>https://www.blick.ch/sport/eishockey/overtime-...</td>\n",
       "      <td>text/html; charset=utf-8</td>\n",
       "      <td>200</td>\n",
       "      <td>Connection Timeout</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>https://www.blick.ch/news/ausland/neben-berueh...</td>\n",
       "      <td>text/html; charset=utf-8</td>\n",
       "      <td>200</td>\n",
       "      <td>Connection Timeout</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>https://www.blick.ch/news/grausiger-fund-in-an...</td>\n",
       "      <td>text/html; charset=utf-8</td>\n",
       "      <td>200</td>\n",
       "      <td>Connection Timeout</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>https://www.blick.ch/news/mahamaya-bern-die-we...</td>\n",
       "      <td>text/html; charset=utf-8</td>\n",
       "      <td>200</td>\n",
       "      <td>Connection Timeout</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>https://www.blick.ch/people-tv/musik/liechtens...</td>\n",
       "      <td>text/html; charset=utf-8</td>\n",
       "      <td>200</td>\n",
       "      <td>Connection Timeout</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>https://www.blick.ch/people-tv/tv/bachelorette...</td>\n",
       "      <td>text/html; charset=utf-8</td>\n",
       "      <td>200</td>\n",
       "      <td>Connection Timeout</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>https://www.blick.ch/news/das-ist-die-swiss-bo...</td>\n",
       "      <td>text/html; charset=utf-8</td>\n",
       "      <td>200</td>\n",
       "      <td>Connection Timeout</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>https://www.blick.ch/news/schweiz/vergangenhei...</td>\n",
       "      <td>text/html; charset=utf-8</td>\n",
       "      <td>200</td>\n",
       "      <td>Connection Timeout</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>https://www.blick.ch/news/wirtschaft/das-sind-...</td>\n",
       "      <td>text/html; charset=utf-8</td>\n",
       "      <td>200</td>\n",
       "      <td>Connection Timeout</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             Address  \\\n",
       "0  https://www.blick.ch/people-tv/international/r...   \n",
       "1  https://www.blick.ch/sport/eishockey/overtime-...   \n",
       "2  https://www.blick.ch/news/ausland/neben-berueh...   \n",
       "3  https://www.blick.ch/news/grausiger-fund-in-an...   \n",
       "4  https://www.blick.ch/news/mahamaya-bern-die-we...   \n",
       "5  https://www.blick.ch/people-tv/musik/liechtens...   \n",
       "6  https://www.blick.ch/people-tv/tv/bachelorette...   \n",
       "7  https://www.blick.ch/news/das-ist-die-swiss-bo...   \n",
       "8  https://www.blick.ch/news/schweiz/vergangenhei...   \n",
       "9  https://www.blick.ch/news/wirtschaft/das-sind-...   \n",
       "\n",
       "                    Content  Status Code              Status  \\\n",
       "0  text/html; charset=utf-8          200  Connection Refused   \n",
       "1  text/html; charset=utf-8          200  Connection Timeout   \n",
       "2  text/html; charset=utf-8          200  Connection Timeout   \n",
       "3  text/html; charset=utf-8          200  Connection Timeout   \n",
       "4  text/html; charset=utf-8          200  Connection Timeout   \n",
       "5  text/html; charset=utf-8          200  Connection Timeout   \n",
       "6  text/html; charset=utf-8          200  Connection Timeout   \n",
       "7  text/html; charset=utf-8          200  Connection Timeout   \n",
       "8  text/html; charset=utf-8          200  Connection Timeout   \n",
       "9  text/html; charset=utf-8          200  Connection Timeout   \n",
       "\n",
       "   Contains: '\"article-text-box\"'  Contains: '\"video-player\"'  \\\n",
       "0                               0                           0   \n",
       "1                               0                           0   \n",
       "2                               0                           0   \n",
       "3                               0                           0   \n",
       "4                               0                           0   \n",
       "5                               0                           0   \n",
       "6                               0                           0   \n",
       "7                               0                           0   \n",
       "8                               0                           0   \n",
       "9                               0                           0   \n",
       "\n",
       "   Contains: '\"article-body'  Contains: '\\(sda\\)'  Contains: '\"scribble\"'  \\\n",
       "0                          0                    0                       0   \n",
       "1                          0                    0                       0   \n",
       "2                          0                    0                       0   \n",
       "3                          0                    0                       0   \n",
       "4                          0                    0                       0   \n",
       "5                          0                    0                       0   \n",
       "6                          0                    0                       0   \n",
       "7                          0                    0                       0   \n",
       "8                          0                    0                       0   \n",
       "9                          0                    0                       0   \n",
       "\n",
       "   Contains: 'basic-image--free-desktop'  Contains: '<iframe'  \\\n",
       "0                                      0                    0   \n",
       "1                                      0                    0   \n",
       "2                                      0                    0   \n",
       "3                                      0                    0   \n",
       "4                                      0                    0   \n",
       "5                                      0                    0   \n",
       "6                                      0                    0   \n",
       "7                                      0                    0   \n",
       "8                                      0                    0   \n",
       "9                                      0                    0   \n",
       "\n",
       "   Contains: 'layout-item--flex-desktop-4'  Contains: '\\(sda\\/'  \n",
       "0                                        0                    0  \n",
       "1                                        0                    0  \n",
       "2                                        0                    0  \n",
       "3                                        0                    0  \n",
       "4                                        0                    0  \n",
       "5                                        0                    0  \n",
       "6                                        0                    0  \n",
       "7                                        0                    0  \n",
       "8                                        0                    0  \n",
       "9                                        0                    0  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfCrawled = pd.DataFrame(pd.read_excel('./custom_search_all-test.xlsx', header=0, sheet_name='1 - All'))\n",
    "dfCrawled.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>Address</th>\n",
       "      <th>Content</th>\n",
       "      <th>Status Code</th>\n",
       "      <th>Status</th>\n",
       "      <th>article-text-box</th>\n",
       "      <th>video-player</th>\n",
       "      <th>article-body</th>\n",
       "      <th>sda</th>\n",
       "      <th>scribble</th>\n",
       "      <th>has-image</th>\n",
       "      <th>iframe</th>\n",
       "      <th>normal-article-layout</th>\n",
       "      <th>sda2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>https://www.blick.ch/people-tv/international/r...</td>\n",
       "      <td>text/html; charset=utf-8</td>\n",
       "      <td>200</td>\n",
       "      <td>Connection Refused</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>https://www.blick.ch/sport/eishockey/overtime-...</td>\n",
       "      <td>text/html; charset=utf-8</td>\n",
       "      <td>200</td>\n",
       "      <td>Connection Timeout</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>https://www.blick.ch/news/ausland/neben-berueh...</td>\n",
       "      <td>text/html; charset=utf-8</td>\n",
       "      <td>200</td>\n",
       "      <td>Connection Timeout</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>https://www.blick.ch/news/grausiger-fund-in-an...</td>\n",
       "      <td>text/html; charset=utf-8</td>\n",
       "      <td>200</td>\n",
       "      <td>Connection Timeout</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>https://www.blick.ch/news/mahamaya-bern-die-we...</td>\n",
       "      <td>text/html; charset=utf-8</td>\n",
       "      <td>200</td>\n",
       "      <td>Connection Timeout</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>https://www.blick.ch/people-tv/musik/liechtens...</td>\n",
       "      <td>text/html; charset=utf-8</td>\n",
       "      <td>200</td>\n",
       "      <td>Connection Timeout</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>https://www.blick.ch/people-tv/tv/bachelorette...</td>\n",
       "      <td>text/html; charset=utf-8</td>\n",
       "      <td>200</td>\n",
       "      <td>Connection Timeout</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>https://www.blick.ch/news/das-ist-die-swiss-bo...</td>\n",
       "      <td>text/html; charset=utf-8</td>\n",
       "      <td>200</td>\n",
       "      <td>Connection Timeout</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>https://www.blick.ch/news/schweiz/vergangenhei...</td>\n",
       "      <td>text/html; charset=utf-8</td>\n",
       "      <td>200</td>\n",
       "      <td>Connection Timeout</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>https://www.blick.ch/news/wirtschaft/das-sind-...</td>\n",
       "      <td>text/html; charset=utf-8</td>\n",
       "      <td>200</td>\n",
       "      <td>Connection Timeout</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             Address  \\\n",
       "0  https://www.blick.ch/people-tv/international/r...   \n",
       "1  https://www.blick.ch/sport/eishockey/overtime-...   \n",
       "2  https://www.blick.ch/news/ausland/neben-berueh...   \n",
       "3  https://www.blick.ch/news/grausiger-fund-in-an...   \n",
       "4  https://www.blick.ch/news/mahamaya-bern-die-we...   \n",
       "5  https://www.blick.ch/people-tv/musik/liechtens...   \n",
       "6  https://www.blick.ch/people-tv/tv/bachelorette...   \n",
       "7  https://www.blick.ch/news/das-ist-die-swiss-bo...   \n",
       "8  https://www.blick.ch/news/schweiz/vergangenhei...   \n",
       "9  https://www.blick.ch/news/wirtschaft/das-sind-...   \n",
       "\n",
       "                    Content  Status Code              Status  \\\n",
       "0  text/html; charset=utf-8          200  Connection Refused   \n",
       "1  text/html; charset=utf-8          200  Connection Timeout   \n",
       "2  text/html; charset=utf-8          200  Connection Timeout   \n",
       "3  text/html; charset=utf-8          200  Connection Timeout   \n",
       "4  text/html; charset=utf-8          200  Connection Timeout   \n",
       "5  text/html; charset=utf-8          200  Connection Timeout   \n",
       "6  text/html; charset=utf-8          200  Connection Timeout   \n",
       "7  text/html; charset=utf-8          200  Connection Timeout   \n",
       "8  text/html; charset=utf-8          200  Connection Timeout   \n",
       "9  text/html; charset=utf-8          200  Connection Timeout   \n",
       "\n",
       "   article-text-box  video-player  article-body  sda  scribble  has-image  \\\n",
       "0                 0             0             0    0         0          0   \n",
       "1                 0             0             0    0         0          0   \n",
       "2                 0             0             0    0         0          0   \n",
       "3                 0             0             0    0         0          0   \n",
       "4                 0             0             0    0         0          0   \n",
       "5                 0             0             0    0         0          0   \n",
       "6                 0             0             0    0         0          0   \n",
       "7                 0             0             0    0         0          0   \n",
       "8                 0             0             0    0         0          0   \n",
       "9                 0             0             0    0         0          0   \n",
       "\n",
       "   iframe  normal-article-layout  sda2  \n",
       "0       0                      0     0  \n",
       "1       0                      0     0  \n",
       "2       0                      0     0  \n",
       "3       0                      0     0  \n",
       "4       0                      0     0  \n",
       "5       0                      0     0  \n",
       "6       0                      0     0  \n",
       "7       0                      0     0  \n",
       "8       0                      0     0  \n",
       "9       0                      0     0  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Rename columns\n",
    "dfCrawled = dfCrawled.rename(columns={\"Contains: \\'\\\"article-text-box\\\"\\'\": \"article-text-box\", \n",
    "                                         \"Contains: \\'\\\"video-player\\\"\\'\":\"video-player\", \n",
    "                                         \"Contains: \\'\\\"article-body\\'\":\"article-body\",\n",
    "                                         \"Contains: \\'\\(sda\\)\\'\":\"sda\",\n",
    "                                         \"Contains: \\'\\\"scribble\\\"\\'\":\"scribble\",\n",
    "                                         \"Contains: \\'basic-image--free-desktop\\'\":\"has-image\", \n",
    "                                         \"Contains: \\'<iframe\\'\":\"iframe\", \n",
    "                                         \"Contains: \\'layout-item--flex-desktop-4'\":\"normal-article-layout\", \n",
    "                                         \"Contains: \\'\\(sda\\/\\'\":\"sda2\", \n",
    "                                     })\n",
    "dfCrawled.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>Address</th>\n",
       "      <th>article-text-box</th>\n",
       "      <th>video-player</th>\n",
       "      <th>article-body</th>\n",
       "      <th>sda</th>\n",
       "      <th>scribble</th>\n",
       "      <th>has-image</th>\n",
       "      <th>iframe</th>\n",
       "      <th>normal-article-layout</th>\n",
       "      <th>sda2</th>\n",
       "      <th>ID_Crawl</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>https://www.blick.ch/people-tv/international/r...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4270807</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>https://www.blick.ch/sport/eishockey/overtime-...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>106244</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>https://www.blick.ch/news/ausland/neben-berueh...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3896239</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>https://www.blick.ch/news/grausiger-fund-in-an...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2024473</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>https://www.blick.ch/news/mahamaya-bern-die-we...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>110094</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>https://www.blick.ch/people-tv/musik/liechtens...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2131248</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>https://www.blick.ch/people-tv/tv/bachelorette...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3790667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>https://www.blick.ch/news/das-ist-die-swiss-bo...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2238005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>https://www.blick.ch/news/schweiz/vergangenhei...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5550392</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>https://www.blick.ch/news/wirtschaft/das-sind-...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4955860</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             Address  article-text-box  \\\n",
       "0  https://www.blick.ch/people-tv/international/r...                 0   \n",
       "1  https://www.blick.ch/sport/eishockey/overtime-...                 0   \n",
       "2  https://www.blick.ch/news/ausland/neben-berueh...                 0   \n",
       "3  https://www.blick.ch/news/grausiger-fund-in-an...                 0   \n",
       "4  https://www.blick.ch/news/mahamaya-bern-die-we...                 0   \n",
       "5  https://www.blick.ch/people-tv/musik/liechtens...                 0   \n",
       "6  https://www.blick.ch/people-tv/tv/bachelorette...                 0   \n",
       "7  https://www.blick.ch/news/das-ist-die-swiss-bo...                 0   \n",
       "8  https://www.blick.ch/news/schweiz/vergangenhei...                 0   \n",
       "9  https://www.blick.ch/news/wirtschaft/das-sind-...                 0   \n",
       "\n",
       "   video-player  article-body  sda  scribble  has-image  iframe  \\\n",
       "0             0             0    0         0          0       0   \n",
       "1             0             0    0         0          0       0   \n",
       "2             0             0    0         0          0       0   \n",
       "3             0             0    0         0          0       0   \n",
       "4             0             0    0         0          0       0   \n",
       "5             0             0    0         0          0       0   \n",
       "6             0             0    0         0          0       0   \n",
       "7             0             0    0         0          0       0   \n",
       "8             0             0    0         0          0       0   \n",
       "9             0             0    0         0          0       0   \n",
       "\n",
       "   normal-article-layout  sda2 ID_Crawl  \n",
       "0                      0     0  4270807  \n",
       "1                      0     0   106244  \n",
       "2                      0     0  3896239  \n",
       "3                      0     0  2024473  \n",
       "4                      0     0   110094  \n",
       "5                      0     0  2131248  \n",
       "6                      0     0  3790667  \n",
       "7                      0     0  2238005  \n",
       "8                      0     0  5550392  \n",
       "9                      0     0  4955860  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfCrawled['ID_Crawl'] = dfCrawled['Address'].str.extract('(?:.*id)([0-9]+)(?:.html)', expand=False)\n",
    "dfCrawled = dfCrawled.drop(\"Status Code\",1)\n",
    "dfCrawled = dfCrawled.drop(\"Status\",1)\n",
    "dfCrawled = dfCrawled.drop(\"Content\",1)\n",
    "dfCrawled.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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.7.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}