test_joining_df.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Combine page id and redirect to correct the links"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "redirect loaded\n",
      "page loaded\n",
      "pagelinks loaded\n",
      "Load Time: 9.46012282371521\n"
     ]
    }
   ],
   "source": [
    "# Load the data\n",
    "start_t = time.time()\n",
    "redirect_df = pd.read_pickle('/home/benjamin/wikipedia/Wikipedia/redirect_parsed0.gz')\n",
    "print('redirect loaded')\n",
    "pageid_df = pd.read_pickle('/home/benjamin/wikipedia/Wikipedia/pageid_parsed0.gz')\n",
    "print('page loaded')\n",
    "pagelinks_df = pd.read_pickle('/home/benjamin/wikipedia/Wikipedia/pagelinks_parsed0.gz')\n",
    "print('pagelinks loaded')\n",
    "print('Load Time:', time.time() - start_t)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>pageId</th>\n",
       "      <th>pageTitle</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9773</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>15154</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>25213</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1028188</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1340746</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    pageId pageTitle\n",
       "0     9773         !\n",
       "1    15154         !\n",
       "2    25213         !\n",
       "3  1028188         !\n",
       "4  1340746         !"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pagelinks_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Change source Ids to titles in redirect"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['pageId', 'pageTitle'], dtype='object')\n"
     ]
    }
   ],
   "source": [
    "print(pageid_df.columns)\n",
    "pageid_df.set_index('pageId', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Old name Index(['pageId', 'pageTitle'], dtype='object')\n",
      "New name Index(['initial_id', 'fix_target'], dtype='object')\n"
     ]
    }
   ],
   "source": [
    "print('Old name',redirect_df.columns)\n",
    "redirect_df.rename(columns={'pageId' : 'initial_id', 'pageTitle' : 'fix_target'}, inplace=True)\n",
    "print('New name',redirect_df.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "redirect_df = redirect_df.join(pageid_df, on='initial_id', how='left')\n",
    "redirect_df.rename(columns={'pageTitle' : 'initial_target'}, inplace=True)\n",
    "redirect_df = redirect_df.drop('initial_id', axis=1)\n",
    "redirect_df = redirect_df.set_index('initial_target')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Check for NaN values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "8500104"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(redirect_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "fix_target    0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "redirect_df.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>fix_target</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>initial_target</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [fix_target]\n",
       "Index: []"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "redirect_df[redirect_df.isnull().any(axis=1)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "#redirect_df = redirect_df.reindex(columns=['initial_id','initial_target','fix_target'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>fix_target</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>initial_target</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>AccessibleComputing</th>\n",
       "      <td>Computer_accessibility</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AfghanistanHistory</th>\n",
       "      <td>History_of_Afghanistan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AfghanistanGeography</th>\n",
       "      <td>Geography_of_Afghanistan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AfghanistanPeople</th>\n",
       "      <td>Demographics_of_Afghanistan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AfghanistanCommunications</th>\n",
       "      <td>Communications_in_Afghanistan</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              fix_target\n",
       "initial_target                                          \n",
       "AccessibleComputing               Computer_accessibility\n",
       "AfghanistanHistory                History_of_Afghanistan\n",
       "AfghanistanGeography            Geography_of_Afghanistan\n",
       "AfghanistanPeople            Demographics_of_Afghanistan\n",
       "AfghanistanCommunications  Communications_in_Afghanistan"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "redirect_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Replacing target in pagelinks by the redirected one"
   ]
  },
  {
   "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>pageId</th>\n",
       "      <th>pageTitle</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9773</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>15154</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>25213</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1028188</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1340746</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    pageId pageTitle\n",
       "0     9773         !\n",
       "1    15154         !\n",
       "2    25213         !\n",
       "3  1028188         !\n",
       "4  1340746         !"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pagelinks_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "11003768"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#pagelinks_df.set_index('sourceId', inplace=True)\n",
    "len(pagelinks_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "11003768 24359904\n"
     ]
    }
   ],
   "source": [
    "old_size = len(pagelinks_df)\n",
    "pagelinks_df.rename(columns={'pageTitle' : 'target'}, inplace=True)\n",
    "pagelinks_df = pagelinks_df.join(pageid_df, on='pageId', how='outer')\n",
    "pagelinks_df.rename(columns={'pageTitle' : 'source'}, inplace=True)\n",
    "new_size = len(pagelinks_df)\n",
    "print(old_size,new_size)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pageId           0\n",
       "target    13356136\n",
       "source          86\n",
       "dtype: int64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pagelinks_df.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>pageId</th>\n",
       "      <th>target</th>\n",
       "      <th>source</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>253291</th>\n",
       "      <td>58044482</td>\n",
       "      <td>0-4-0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>254041</th>\n",
       "      <td>58044482</td>\n",
       "      <td>0-4-2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4402628</th>\n",
       "      <td>58044713</td>\n",
       "      <td>1877_Men\\'s_Tennis_tour</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4419523</th>\n",
       "      <td>58044713</td>\n",
       "      <td>1878_Men\\'s_Tennis_tour</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4436437</th>\n",
       "      <td>58044713</td>\n",
       "      <td>1879_Men\\'s_Tennis_tour</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4459036</th>\n",
       "      <td>58044713</td>\n",
       "      <td>1880_Men\\'s_Tennis_tour</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4494754</th>\n",
       "      <td>58044713</td>\n",
       "      <td>1881_Men\\'s_Tennis_tour</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4514671</th>\n",
       "      <td>58044713</td>\n",
       "      <td>1882_Men\\'s_Tennis_tour</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4536196</th>\n",
       "      <td>58044713</td>\n",
       "      <td>1883_Men\\'s_Tennis_tour</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4559070</th>\n",
       "      <td>58044713</td>\n",
       "      <td>1884_Men\\'s_Tennis_tour</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4564481</th>\n",
       "      <td>58044713</td>\n",
       "      <td>1884_Wimbledon_Championships_–_Gentlemen\\'s_Si...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4582857</th>\n",
       "      <td>58044713</td>\n",
       "      <td>1885_Men\\'s_Tennis_tour</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4606312</th>\n",
       "      <td>58044713</td>\n",
       "      <td>1886_Men\\'s_Tennis_tour</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4632139</th>\n",
       "      <td>58044713</td>\n",
       "      <td>1887_Men\\'s_Tennis_tour</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4658616</th>\n",
       "      <td>58044713</td>\n",
       "      <td>1888_Men\\'s_Tennis_tour</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4689489</th>\n",
       "      <td>58044713</td>\n",
       "      <td>1889_Men\\'s_Tennis_tour</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4724416</th>\n",
       "      <td>58044713</td>\n",
       "      <td>1890_Men\\'s_Tennis_tour</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4775524</th>\n",
       "      <td>58044713</td>\n",
       "      <td>1891_Men\\'s_Tennis_tour</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4813301</th>\n",
       "      <td>58044713</td>\n",
       "      <td>1892_Men\\'s_Tennis_tour</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4853612</th>\n",
       "      <td>58044713</td>\n",
       "      <td>1893_Men\\'s_Tennis_tour</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4895812</th>\n",
       "      <td>58044713</td>\n",
       "      <td>1894_Men\\'s_Tennis_tour</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4938999</th>\n",
       "      <td>58044713</td>\n",
       "      <td>1895_Men\\'s_Tennis_tour</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4984640</th>\n",
       "      <td>58044713</td>\n",
       "      <td>1896_Men\\'s_Tennis_tour</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5032129</th>\n",
       "      <td>58044713</td>\n",
       "      <td>1897_Men\\'s_Tennis_tour</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5078395</th>\n",
       "      <td>58044713</td>\n",
       "      <td>1898_Men\\'s_Tennis_tour</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5128746</th>\n",
       "      <td>58044713</td>\n",
       "      <td>1899_Men\\'s_Tennis_tour</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5226369</th>\n",
       "      <td>58044713</td>\n",
       "      <td>1900_Men\\'s_Tennis_tour</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5303120</th>\n",
       "      <td>58044713</td>\n",
       "      <td>1901_Men\\'s_Tennis_tour</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5359756</th>\n",
       "      <td>58044713</td>\n",
       "      <td>1902_Men\\'s_Tennis_tour</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5415631</th>\n",
       "      <td>58044713</td>\n",
       "      <td>1903_Men\\'s_Tennis_tour</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11003767</th>\n",
       "      <td>58044362</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Night_aviation_regulations_in_the_US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11003767</th>\n",
       "      <td>58044364</td>\n",
       "      <td>NaN</td>\n",
       "      <td>WGLC_(AM)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11003767</th>\n",
       "      <td>58044365</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Jean_de_Rode</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11003767</th>\n",
       "      <td>58044366</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Give_It_a_Name_(Jerry_Cantrell_song)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11003767</th>\n",
       "      <td>58044368</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Thanks_Anyway</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11003767</th>\n",
       "      <td>58044372</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Washington_Park_station_(St._Louis)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11003767</th>\n",
       "      <td>58044374</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Matsuyama_Motonori</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11003767</th>\n",
       "      <td>58044377</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Harry_Peeters_(rowing)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11003767</th>\n",
       "      <td>58044389</td>\n",
       "      <td>NaN</td>\n",
       "      <td>İsmet_Kür</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11003767</th>\n",
       "      <td>58044405</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31/32_(song)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11003767</th>\n",
       "      <td>58044406</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Abheri_Gupta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11003767</th>\n",
       "      <td>58044407</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Sujata_choudhary</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11003767</th>\n",
       "      <td>58044410</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Motets_(Bruckner)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11003767</th>\n",
       "      <td>58044426</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Discovery_World_(Europe)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11003767</th>\n",
       "      <td>58044428</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Journal_Square-Hoboken</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11003767</th>\n",
       "      <td>58044429</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Gianchand_(Pakistani_politician)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11003767</th>\n",
       "      <td>58044433</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Journal_Square-World_Trade_Center_(via_Hoboken)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11003767</th>\n",
       "      <td>58044434</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Esther_Razanadrasoa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11003767</th>\n",
       "      <td>58044436</td>\n",
       "      <td>NaN</td>\n",
       "      <td>JSQ-WTC_(via_HOB)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11003767</th>\n",
       "      <td>58044437</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Nicholas_Pelham_(by_1513_-_1560)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11003767</th>\n",
       "      <td>58044444</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Oneness_(Soul_Basement_album)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11003767</th>\n",
       "      <td>58044445</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Binomial_experiment</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11003767</th>\n",
       "      <td>58044456</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Gazi_Eğitim_Enstitüsü</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11003767</th>\n",
       "      <td>58044457</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Verawaty_Wiharjo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11003767</th>\n",
       "      <td>58044461</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Anja-Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11003767</th>\n",
       "      <td>58044462</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Total_Divas_(season_8)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11003767</th>\n",
       "      <td>58044468</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Nairac</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11003767</th>\n",
       "      <td>58044469</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Cynthia_Tait</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11003767</th>\n",
       "      <td>58044478</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Kilroy_Was_Here_(1947_film)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11003767</th>\n",
       "      <td>58044479</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Grimethorpe_Rovers_F.C.</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>13356222 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            pageId                                             target  \\\n",
       "253291    58044482                                              0-4-0   \n",
       "254041    58044482                                              0-4-2   \n",
       "4402628   58044713                            1877_Men\\'s_Tennis_tour   \n",
       "4419523   58044713                            1878_Men\\'s_Tennis_tour   \n",
       "4436437   58044713                            1879_Men\\'s_Tennis_tour   \n",
       "4459036   58044713                            1880_Men\\'s_Tennis_tour   \n",
       "4494754   58044713                            1881_Men\\'s_Tennis_tour   \n",
       "4514671   58044713                            1882_Men\\'s_Tennis_tour   \n",
       "4536196   58044713                            1883_Men\\'s_Tennis_tour   \n",
       "4559070   58044713                            1884_Men\\'s_Tennis_tour   \n",
       "4564481   58044713  1884_Wimbledon_Championships_–_Gentlemen\\'s_Si...   \n",
       "4582857   58044713                            1885_Men\\'s_Tennis_tour   \n",
       "4606312   58044713                            1886_Men\\'s_Tennis_tour   \n",
       "4632139   58044713                            1887_Men\\'s_Tennis_tour   \n",
       "4658616   58044713                            1888_Men\\'s_Tennis_tour   \n",
       "4689489   58044713                            1889_Men\\'s_Tennis_tour   \n",
       "4724416   58044713                            1890_Men\\'s_Tennis_tour   \n",
       "4775524   58044713                            1891_Men\\'s_Tennis_tour   \n",
       "4813301   58044713                            1892_Men\\'s_Tennis_tour   \n",
       "4853612   58044713                            1893_Men\\'s_Tennis_tour   \n",
       "4895812   58044713                            1894_Men\\'s_Tennis_tour   \n",
       "4938999   58044713                            1895_Men\\'s_Tennis_tour   \n",
       "4984640   58044713                            1896_Men\\'s_Tennis_tour   \n",
       "5032129   58044713                            1897_Men\\'s_Tennis_tour   \n",
       "5078395   58044713                            1898_Men\\'s_Tennis_tour   \n",
       "5128746   58044713                            1899_Men\\'s_Tennis_tour   \n",
       "5226369   58044713                            1900_Men\\'s_Tennis_tour   \n",
       "5303120   58044713                            1901_Men\\'s_Tennis_tour   \n",
       "5359756   58044713                            1902_Men\\'s_Tennis_tour   \n",
       "5415631   58044713                            1903_Men\\'s_Tennis_tour   \n",
       "...            ...                                                ...   \n",
       "11003767  58044362                                                NaN   \n",
       "11003767  58044364                                                NaN   \n",
       "11003767  58044365                                                NaN   \n",
       "11003767  58044366                                                NaN   \n",
       "11003767  58044368                                                NaN   \n",
       "11003767  58044372                                                NaN   \n",
       "11003767  58044374                                                NaN   \n",
       "11003767  58044377                                                NaN   \n",
       "11003767  58044389                                                NaN   \n",
       "11003767  58044405                                                NaN   \n",
       "11003767  58044406                                                NaN   \n",
       "11003767  58044407                                                NaN   \n",
       "11003767  58044410                                                NaN   \n",
       "11003767  58044426                                                NaN   \n",
       "11003767  58044428                                                NaN   \n",
       "11003767  58044429                                                NaN   \n",
       "11003767  58044433                                                NaN   \n",
       "11003767  58044434                                                NaN   \n",
       "11003767  58044436                                                NaN   \n",
       "11003767  58044437                                                NaN   \n",
       "11003767  58044444                                                NaN   \n",
       "11003767  58044445                                                NaN   \n",
       "11003767  58044456                                                NaN   \n",
       "11003767  58044457                                                NaN   \n",
       "11003767  58044461                                                NaN   \n",
       "11003767  58044462                                                NaN   \n",
       "11003767  58044468                                                NaN   \n",
       "11003767  58044469                                                NaN   \n",
       "11003767  58044478                                                NaN   \n",
       "11003767  58044479                                                NaN   \n",
       "\n",
       "                                                   source  \n",
       "253291                                                NaN  \n",
       "254041                                                NaN  \n",
       "4402628                                               NaN  \n",
       "4419523                                               NaN  \n",
       "4436437                                               NaN  \n",
       "4459036                                               NaN  \n",
       "4494754                                               NaN  \n",
       "4514671                                               NaN  \n",
       "4536196                                               NaN  \n",
       "4559070                                               NaN  \n",
       "4564481                                               NaN  \n",
       "4582857                                               NaN  \n",
       "4606312                                               NaN  \n",
       "4632139                                               NaN  \n",
       "4658616                                               NaN  \n",
       "4689489                                               NaN  \n",
       "4724416                                               NaN  \n",
       "4775524                                               NaN  \n",
       "4813301                                               NaN  \n",
       "4853612                                               NaN  \n",
       "4895812                                               NaN  \n",
       "4938999                                               NaN  \n",
       "4984640                                               NaN  \n",
       "5032129                                               NaN  \n",
       "5078395                                               NaN  \n",
       "5128746                                               NaN  \n",
       "5226369                                               NaN  \n",
       "5303120                                               NaN  \n",
       "5359756                                               NaN  \n",
       "5415631                                               NaN  \n",
       "...                                                   ...  \n",
       "11003767             Night_aviation_regulations_in_the_US  \n",
       "11003767                                        WGLC_(AM)  \n",
       "11003767                                     Jean_de_Rode  \n",
       "11003767             Give_It_a_Name_(Jerry_Cantrell_song)  \n",
       "11003767                                    Thanks_Anyway  \n",
       "11003767              Washington_Park_station_(St._Louis)  \n",
       "11003767                               Matsuyama_Motonori  \n",
       "11003767                           Harry_Peeters_(rowing)  \n",
       "11003767                                        İsmet_Kür  \n",
       "11003767                                     31/32_(song)  \n",
       "11003767                                     Abheri_Gupta  \n",
       "11003767                                 Sujata_choudhary  \n",
       "11003767                                Motets_(Bruckner)  \n",
       "11003767                         Discovery_World_(Europe)  \n",
       "11003767                           Journal_Square-Hoboken  \n",
       "11003767                 Gianchand_(Pakistani_politician)  \n",
       "11003767  Journal_Square-World_Trade_Center_(via_Hoboken)  \n",
       "11003767                              Esther_Razanadrasoa  \n",
       "11003767                                JSQ-WTC_(via_HOB)  \n",
       "11003767                 Nicholas_Pelham_(by_1513_-_1560)  \n",
       "11003767                    Oneness_(Soul_Basement_album)  \n",
       "11003767                              Binomial_experiment  \n",
       "11003767                            Gazi_Eğitim_Enstitüsü  \n",
       "11003767                                 Verawaty_Wiharjo  \n",
       "11003767                                           Anja-Z  \n",
       "11003767                           Total_Divas_(season_8)  \n",
       "11003767                                           Nairac  \n",
       "11003767                                     Cynthia_Tait  \n",
       "11003767                      Kilroy_Was_Here_(1947_film)  \n",
       "11003767                          Grimethorpe_Rovers_F.C.  \n",
       "\n",
       "[13356222 rows x 3 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pagelinks_df[pagelinks_df.isnull().any(axis=1)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([58044482, 58044713])"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Id list of unique pages that have an id but do not have a title\n",
    "pagelinks_df['pageId'][pagelinks_df['source'].isnull()].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>pageId</th>\n",
       "      <th>target</th>\n",
       "      <th>source</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9773</td>\n",
       "      <td>!</td>\n",
       "      <td>EBCDIC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1326</th>\n",
       "      <td>9773</td>\n",
       "      <td>\\\"</td>\n",
       "      <td>EBCDIC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12977</th>\n",
       "      <td>9773</td>\n",
       "      <td>$</td>\n",
       "      <td>EBCDIC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15271</th>\n",
       "      <td>9773</td>\n",
       "      <td>%</td>\n",
       "      <td>EBCDIC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112343</th>\n",
       "      <td>9773</td>\n",
       "      <td>+</td>\n",
       "      <td>EBCDIC</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        pageId target  source\n",
       "0         9773      !  EBCDIC\n",
       "1326      9773     \\\"  EBCDIC\n",
       "12977     9773      $  EBCDIC\n",
       "15271     9773      %  EBCDIC\n",
       "112343    9773      +  EBCDIC"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pagelinks_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "pagelinks_df['fix_target'] = pagelinks_df['target'].copy()\n",
    "pagelinks_df = pagelinks_df.set_index('target')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "#redirect_df.rename(columns={'r_t_T' : 'fix_target'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "11003682\n"
     ]
    }
   ],
   "source": [
    "pagelinks_df.update(redirect_df)\n",
    "pagelinks_df.reset_index(inplace = True)\n",
    "pagelinks_df = pagelinks_df.drop('pageId', axis=1)\n",
    "pagelinks_df['is_redirect'] = (pagelinks_df['target'] != pagelinks_df['fix_target'])\n",
    "pagelinks_df = pagelinks_df.reindex(columns=['source','target','fix_target','is_redirect'])\n",
    "print(len(pagelinks_df))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>source</th>\n",
       "      <th>target</th>\n",
       "      <th>fix_target</th>\n",
       "      <th>is_redirect</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>EBCDIC</td>\n",
       "      <td>!</td>\n",
       "      <td>Exclamation_mark</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>EBCDIC</td>\n",
       "      <td>\\\"</td>\n",
       "      <td>Quotation_mark</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>EBCDIC</td>\n",
       "      <td>$</td>\n",
       "      <td>Dollar_sign</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>EBCDIC</td>\n",
       "      <td>%</td>\n",
       "      <td>Percent_sign</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>EBCDIC</td>\n",
       "      <td>+</td>\n",
       "      <td>Plus_and_minus_signs</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   source target            fix_target  is_redirect\n",
       "0  EBCDIC      !      Exclamation_mark         True\n",
       "1  EBCDIC     \\\"        Quotation_mark         True\n",
       "2  EBCDIC      $           Dollar_sign         True\n",
       "3  EBCDIC      %          Percent_sign         True\n",
       "4  EBCDIC      +  Plus_and_minus_signs         True"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pagelinks_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "11003682"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(pagelinks_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>source</th>\n",
       "      <th>target</th>\n",
       "      <th>fix_target</th>\n",
       "      <th>is_redirect</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>11003682</td>\n",
       "      <td>11003682</td>\n",
       "      <td>11003682</td>\n",
       "      <td>11003682</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique</th>\n",
       "      <td>694691</td>\n",
       "      <td>257558</td>\n",
       "      <td>228792</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>top</th>\n",
       "      <td>List_of_named_minor_planets:_10000–19999</td>\n",
       "      <td>1950_United_States_Census</td>\n",
       "      <td>1950_United_States_Census</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>freq</th>\n",
       "      <td>4164</td>\n",
       "      <td>23124</td>\n",
       "      <td>23126</td>\n",
       "      <td>10549505</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          source                     target  \\\n",
       "count                                   11003682                   11003682   \n",
       "unique                                    694691                     257558   \n",
       "top     List_of_named_minor_planets:_10000–19999  1950_United_States_Census   \n",
       "freq                                        4164                      23124   \n",
       "\n",
       "                       fix_target is_redirect  \n",
       "count                    11003682    11003682  \n",
       "unique                     228792           2  \n",
       "top     1950_United_States_Census       False  \n",
       "freq                        23126    10549505  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pagelinks_df.describe()"
   ]
  },
  {
   "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.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}