Cleaning data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Reddit Analysis - import, clean and export data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: pandas in c:\\programdata\\anaconda3\\lib\\site-packages (1.1.3)\n",
      "Requirement already satisfied: python-dateutil>=2.7.3 in c:\\programdata\\anaconda3\\lib\\site-packages (from pandas) (2.8.1)\n",
      "Requirement already satisfied: numpy>=1.15.4 in c:\\programdata\\anaconda3\\lib\\site-packages (from pandas) (1.19.2)\n",
      "Requirement already satisfied: pytz>=2017.2 in c:\\programdata\\anaconda3\\lib\\site-packages (from pandas) (2020.1)\n",
      "Requirement already satisfied: six>=1.5 in c:\\programdata\\anaconda3\\lib\\site-packages (from python-dateutil>=2.7.3->pandas) (1.15.0)\n",
      "Note: you may need to restart the kernel to use updated packages.\n",
      "Note: you may need to restart the kernel to use updated packages.\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "ERROR: Could not find a version that satisfies the requirement pickle (from versions: none)\n",
      "ERROR: No matching distribution found for pickle\n"
     ]
    }
   ],
   "source": [
    "# imports\n",
    "%pip install pandas\n",
    "import pandas as pd\n",
    "pd.options.mode.chained_assignment = None\n",
    "\n",
    "%pip install pickle\n",
    "import pickle"
   ]
  },
  {
   "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>id</th>\n",
       "      <th>url</th>\n",
       "      <th>permalink</th>\n",
       "      <th>author</th>\n",
       "      <th>created_utc</th>\n",
       "      <th>subreddit</th>\n",
       "      <th>subreddit_id</th>\n",
       "      <th>num_comments</th>\n",
       "      <th>score</th>\n",
       "      <th>over_18</th>\n",
       "      <th>distinguished</th>\n",
       "      <th>domain</th>\n",
       "      <th>stickied</th>\n",
       "      <th>locked</th>\n",
       "      <th>hide_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>648oo</td>\n",
       "      <td>http://www.ignorancedenied.com/viewthread.php?...</td>\n",
       "      <td>/r/reddit.com/comments/648oo/brain_disease_is_...</td>\n",
       "      <td>DITUS</td>\n",
       "      <td>1199145615</td>\n",
       "      <td>reddit.com</td>\n",
       "      <td>t5_6</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>ignorancedenied.com</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>648op</td>\n",
       "      <td>http://www.flascience.org/wp/?p=363</td>\n",
       "      <td>/r/science/comments/648op/three_more_florida_c...</td>\n",
       "      <td>rmuser</td>\n",
       "      <td>1199145634</td>\n",
       "      <td>science</td>\n",
       "      <td>t5_mouw</td>\n",
       "      <td>5</td>\n",
       "      <td>20</td>\n",
       "      <td>False</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>flascience.org</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>648oq</td>\n",
       "      <td>http://dlweinreb.wordpress.com/2007/12/31/obje...</td>\n",
       "      <td>/r/programming/comments/648oq/the_engineering_...</td>\n",
       "      <td>[deleted]</td>\n",
       "      <td>1199145691</td>\n",
       "      <td>programming</td>\n",
       "      <td>t5_2fwo</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>dlweinreb.wordpress.com</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>648or</td>\n",
       "      <td>http://hosted.ap.org/dynamic/stories/O/ODD_SHO...</td>\n",
       "      <td>/r/reddit.com/comments/648or/nude_couple_grapp...</td>\n",
       "      <td>zorno</td>\n",
       "      <td>1199145709</td>\n",
       "      <td>reddit.com</td>\n",
       "      <td>t5_6</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>False</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>hosted.ap.org</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>648os</td>\n",
       "      <td>http://www.sltrib.com/opinion/ci_7846101?sourc...</td>\n",
       "      <td>/r/politics/comments/648os/apparently_bushs_pr...</td>\n",
       "      <td>rmuser</td>\n",
       "      <td>1199145735</td>\n",
       "      <td>politics</td>\n",
       "      <td>t5_2cneq</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>sltrib.com</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>283910</th>\n",
       "      <td>7mq3r</td>\n",
       "      <td>http://www.youtube.com/watch?v=gdQH1CI4LHY&amp;amp...</td>\n",
       "      <td>/r/politics/comments/7mq3r/ron_paul_on_recent_...</td>\n",
       "      <td>middkidd</td>\n",
       "      <td>1230767963</td>\n",
       "      <td>politics</td>\n",
       "      <td>t5_2cneq</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>youtube.com</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>283911</th>\n",
       "      <td>7mq3s</td>\n",
       "      <td>http://xs434.xs.to/xs434/08013/a312.jpg</td>\n",
       "      <td>/r/ass/comments/7mq3s/francesca/</td>\n",
       "      <td>[deleted]</td>\n",
       "      <td>1230767963</td>\n",
       "      <td>ass</td>\n",
       "      <td>t5_2qoil</td>\n",
       "      <td>0</td>\n",
       "      <td>9</td>\n",
       "      <td>True</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>xs434.xs.to</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>283912</th>\n",
       "      <td>7mq3t</td>\n",
       "      <td>http://unemployedguide.blogspot.com/2008/12/la...</td>\n",
       "      <td>/r/nsfw/comments/7mq3t/laura_ingrahams_embryo/</td>\n",
       "      <td>[deleted]</td>\n",
       "      <td>1230767974</td>\n",
       "      <td>nsfw</td>\n",
       "      <td>t5_vf2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>True</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>unemployedguide.blogspot.com</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>283913</th>\n",
       "      <td>7mq3u</td>\n",
       "      <td>http://www.youtube.com/watch?v=AnGpJ3_MnYQ&amp;amp...</td>\n",
       "      <td>/r/gaming/comments/7mq3u/splinter_cell_double_...</td>\n",
       "      <td>[deleted]</td>\n",
       "      <td>1230767984</td>\n",
       "      <td>gaming</td>\n",
       "      <td>t5_2qh03</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>youtube.com</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>283914</th>\n",
       "      <td>7mq3v</td>\n",
       "      <td>http://player.video.news.com.au/dailytelegraph...</td>\n",
       "      <td>/r/reddit.com/comments/7mq3v/video_nye_firewor...</td>\n",
       "      <td>klippa</td>\n",
       "      <td>1230767988</td>\n",
       "      <td>reddit.com</td>\n",
       "      <td>t5_6</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>player.video.news.com.au</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2519853 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           id                                                url  \\\n",
       "0       648oo  http://www.ignorancedenied.com/viewthread.php?...   \n",
       "1       648op                http://www.flascience.org/wp/?p=363   \n",
       "2       648oq  http://dlweinreb.wordpress.com/2007/12/31/obje...   \n",
       "3       648or  http://hosted.ap.org/dynamic/stories/O/ODD_SHO...   \n",
       "4       648os  http://www.sltrib.com/opinion/ci_7846101?sourc...   \n",
       "...       ...                                                ...   \n",
       "283910  7mq3r  http://www.youtube.com/watch?v=gdQH1CI4LHY&amp...   \n",
       "283911  7mq3s            http://xs434.xs.to/xs434/08013/a312.jpg   \n",
       "283912  7mq3t  http://unemployedguide.blogspot.com/2008/12/la...   \n",
       "283913  7mq3u  http://www.youtube.com/watch?v=AnGpJ3_MnYQ&amp...   \n",
       "283914  7mq3v  http://player.video.news.com.au/dailytelegraph...   \n",
       "\n",
       "                                                permalink     author  \\\n",
       "0       /r/reddit.com/comments/648oo/brain_disease_is_...      DITUS   \n",
       "1       /r/science/comments/648op/three_more_florida_c...     rmuser   \n",
       "2       /r/programming/comments/648oq/the_engineering_...  [deleted]   \n",
       "3       /r/reddit.com/comments/648or/nude_couple_grapp...      zorno   \n",
       "4       /r/politics/comments/648os/apparently_bushs_pr...     rmuser   \n",
       "...                                                   ...        ...   \n",
       "283910  /r/politics/comments/7mq3r/ron_paul_on_recent_...   middkidd   \n",
       "283911                   /r/ass/comments/7mq3s/francesca/  [deleted]   \n",
       "283912     /r/nsfw/comments/7mq3t/laura_ingrahams_embryo/  [deleted]   \n",
       "283913  /r/gaming/comments/7mq3u/splinter_cell_double_...  [deleted]   \n",
       "283914  /r/reddit.com/comments/7mq3v/video_nye_firewor...     klippa   \n",
       "\n",
       "        created_utc    subreddit subreddit_id  num_comments  score  over_18  \\\n",
       "0        1199145615   reddit.com         t5_6             1      0    False   \n",
       "1        1199145634      science      t5_mouw             5     20    False   \n",
       "2        1199145691  programming      t5_2fwo             0      0    False   \n",
       "3        1199145709   reddit.com         t5_6             1      3    False   \n",
       "4        1199145735     politics     t5_2cneq             2      0    False   \n",
       "...             ...          ...          ...           ...    ...      ...   \n",
       "283910   1230767963     politics     t5_2cneq             3      1    False   \n",
       "283911   1230767963          ass     t5_2qoil             0      9     True   \n",
       "283912   1230767974         nsfw       t5_vf2             1      0     True   \n",
       "283913   1230767984       gaming     t5_2qh03             0      0    False   \n",
       "283914   1230767988   reddit.com         t5_6             1      1    False   \n",
       "\n",
       "       distinguished                        domain  stickied  locked  \\\n",
       "0               <NA>           ignorancedenied.com     False   False   \n",
       "1               <NA>                flascience.org     False   False   \n",
       "2               <NA>       dlweinreb.wordpress.com     False   False   \n",
       "3               <NA>                 hosted.ap.org     False   False   \n",
       "4               <NA>                    sltrib.com     False   False   \n",
       "...              ...                           ...       ...     ...   \n",
       "283910          <NA>                   youtube.com     False   False   \n",
       "283911          <NA>                   xs434.xs.to     False   False   \n",
       "283912          <NA>  unemployedguide.blogspot.com     False   False   \n",
       "283913          <NA>                   youtube.com     False   False   \n",
       "283914          <NA>      player.video.news.com.au     False   False   \n",
       "\n",
       "        hide_score  \n",
       "0            False  \n",
       "1            False  \n",
       "2            False  \n",
       "3            False  \n",
       "4            False  \n",
       "...            ...  \n",
       "283910       False  \n",
       "283911       False  \n",
       "283912       False  \n",
       "283913       False  \n",
       "283914       False  \n",
       "\n",
       "[2519853 rows x 15 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# import submissions\n",
    "submissions_path = \"data/initial/submissions_2008/\"\n",
    "\n",
    "submissions_dtype = {\"id\": \"string\", \"url\": \"string\", \"permalink\": \"string\", \"author\": \"string\", \"created utc\": int, \"subreddit\": \"string\", \"subreddit id\": \"string\", \"num comments\": int, \"score\": int, \"over 18\": bool, \"distinguished\": \"string\", \"domain\": \"string\", \"stickied\": bool, \"locked\": bool, \"hide score\": bool}\n",
    "list_subs = []\n",
    "\n",
    "for i in range(0,12):\n",
    "    name = submissions_path + f\"csv-{i}.csv\"\n",
    "    list_subs.append(pd.read_csv(name, dtype=submissions_dtype, index_col=0))\n",
    "submissions = pd.concat(list_subs)\n",
    "\n",
    "submissions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": true
   },
   "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>id</th>\n",
       "      <th>author</th>\n",
       "      <th>link_id</th>\n",
       "      <th>parent_id</th>\n",
       "      <th>created_utc</th>\n",
       "      <th>subreddit</th>\n",
       "      <th>subreddit_id</th>\n",
       "      <th>score</th>\n",
       "      <th>distinguished</th>\n",
       "      <th>gilded</th>\n",
       "      <th>controversiality</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>c02s9s6</td>\n",
       "      <td>Haven</td>\n",
       "      <td>t3_648oh</td>\n",
       "      <td>t1_c02s9rv</td>\n",
       "      <td>1199145604</td>\n",
       "      <td>reddit.com</td>\n",
       "      <td>t5_6</td>\n",
       "      <td>4</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>c02s9s7</td>\n",
       "      <td>[deleted]</td>\n",
       "      <td>t3_647ht</td>\n",
       "      <td>t1_c02s8c8</td>\n",
       "      <td>1199145619</td>\n",
       "      <td>reddit.com</td>\n",
       "      <td>t5_6</td>\n",
       "      <td>2</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>c02s9s8</td>\n",
       "      <td>lilmiss2</td>\n",
       "      <td>t3_648oh</td>\n",
       "      <td>t1_c02s9rv</td>\n",
       "      <td>1199145620</td>\n",
       "      <td>reddit.com</td>\n",
       "      <td>t5_6</td>\n",
       "      <td>2</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>c02s9s9</td>\n",
       "      <td>[deleted]</td>\n",
       "      <td>t3_648oo</td>\n",
       "      <td>t3_648oo</td>\n",
       "      <td>1199145623</td>\n",
       "      <td>reddit.com</td>\n",
       "      <td>t5_6</td>\n",
       "      <td>1</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>c02s9sa</td>\n",
       "      <td>[deleted]</td>\n",
       "      <td>t3_648et</td>\n",
       "      <td>t3_648et</td>\n",
       "      <td>1199145632</td>\n",
       "      <td>reddit.com</td>\n",
       "      <td>t5_6</td>\n",
       "      <td>1</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>850354</th>\n",
       "      <td>c06vwuf</td>\n",
       "      <td>[deleted]</td>\n",
       "      <td>t3_7k8lt</td>\n",
       "      <td>t1_c06vwmo</td>\n",
       "      <td>1229579675</td>\n",
       "      <td>Music</td>\n",
       "      <td>t5_2qh1u</td>\n",
       "      <td>12</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>850355</th>\n",
       "      <td>c06vwug</td>\n",
       "      <td>Morgin_Black</td>\n",
       "      <td>t3_7k3w5</td>\n",
       "      <td>t3_7k3w5</td>\n",
       "      <td>1229579679</td>\n",
       "      <td>comics</td>\n",
       "      <td>t5_2qh0s</td>\n",
       "      <td>0</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>850356</th>\n",
       "      <td>c06vwuh</td>\n",
       "      <td>[deleted]</td>\n",
       "      <td>t3_7k31f</td>\n",
       "      <td>t1_c06vhkx</td>\n",
       "      <td>1229579680</td>\n",
       "      <td>Economics</td>\n",
       "      <td>t5_2qh1s</td>\n",
       "      <td>1</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>850357</th>\n",
       "      <td>c06vwui</td>\n",
       "      <td>onezerozeroone</td>\n",
       "      <td>t3_7k2bc</td>\n",
       "      <td>t1_c06vrvz</td>\n",
       "      <td>1229579685</td>\n",
       "      <td>atheism</td>\n",
       "      <td>t5_2qh2p</td>\n",
       "      <td>1</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>850358</th>\n",
       "      <td>c06vwuj</td>\n",
       "      <td>vypermann</td>\n",
       "      <td>t3_7k65l</td>\n",
       "      <td>t1_c06vvzu</td>\n",
       "      <td>1229579686</td>\n",
       "      <td>WTF</td>\n",
       "      <td>t5_2qh61</td>\n",
       "      <td>0</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7242871 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             id          author   link_id   parent_id  created_utc  \\\n",
       "0       c02s9s6           Haven  t3_648oh  t1_c02s9rv   1199145604   \n",
       "1       c02s9s7       [deleted]  t3_647ht  t1_c02s8c8   1199145619   \n",
       "2       c02s9s8        lilmiss2  t3_648oh  t1_c02s9rv   1199145620   \n",
       "3       c02s9s9       [deleted]  t3_648oo    t3_648oo   1199145623   \n",
       "4       c02s9sa       [deleted]  t3_648et    t3_648et   1199145632   \n",
       "...         ...             ...       ...         ...          ...   \n",
       "850354  c06vwuf       [deleted]  t3_7k8lt  t1_c06vwmo   1229579675   \n",
       "850355  c06vwug    Morgin_Black  t3_7k3w5    t3_7k3w5   1229579679   \n",
       "850356  c06vwuh       [deleted]  t3_7k31f  t1_c06vhkx   1229579680   \n",
       "850357  c06vwui  onezerozeroone  t3_7k2bc  t1_c06vrvz   1229579685   \n",
       "850358  c06vwuj       vypermann  t3_7k65l  t1_c06vvzu   1229579686   \n",
       "\n",
       "         subreddit subreddit_id  score distinguished  gilded  controversiality  \n",
       "0       reddit.com         t5_6      4          <NA>       0                 0  \n",
       "1       reddit.com         t5_6      2          <NA>       0                 0  \n",
       "2       reddit.com         t5_6      2          <NA>       0                 0  \n",
       "3       reddit.com         t5_6      1          <NA>       0                 0  \n",
       "4       reddit.com         t5_6      1          <NA>       0                 0  \n",
       "...            ...          ...    ...           ...     ...               ...  \n",
       "850354       Music     t5_2qh1u     12          <NA>       0                 0  \n",
       "850355      comics     t5_2qh0s      0          <NA>       0                 0  \n",
       "850356   Economics     t5_2qh1s      1          <NA>       0                 0  \n",
       "850357     atheism     t5_2qh2p      1          <NA>       0                 0  \n",
       "850358         WTF     t5_2qh61      0          <NA>       0                 0  \n",
       "\n",
       "[7242871 rows x 11 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# import comments\n",
    "comments_path = \"data/initial/comments_2008/\"\n",
    "comments_dtype = {\"id\": \"string\", \"author\": \"string\", \"link id\": \"string\", \"parent id\": \"string\", \"created utc\": int, \"subreddit\": \"string\", \"subreddit id\": \"string\", \"score\": int, \"distinguished\": \"string\", \"gilded\": int, \"controversiality\": int}\n",
    "list_comments = []\n",
    "\n",
    "for i in range(0,12):\n",
    "    name = comments_path + f\"csv-{i}.csv\"\n",
    "    list_comments.append(pd.read_csv(name, dtype=comments_dtype, index_col=0))\n",
    "comments = pd.concat(list_comments)\n",
    "\n",
    "comments"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "submissions_count = (submissions.shape[0],)\n",
    "comments_count = (comments.shape[0],)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " ### Clean up\n",
    " \n",
    " Since the data has been imported, let's clean it up\n",
    " \n",
    " First let's check if there are any *null values* and are the submission id *unique* "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Submissions: \n",
      "Shape size:  2519853\n",
      "ID size:  2519853\n",
      "ID unique:  True\n",
      "ID has null values:  False\n",
      "ID has missing values:  False\n"
     ]
    }
   ],
   "source": [
    "print(\"Submissions: \")\n",
    "print(\"Shape size: \", submissions.shape[0])\n",
    "print(\"ID size: \", submissions[\"id\"].shape[0])\n",
    "print(\"ID unique: \", submissions[\"id\"].is_unique)\n",
    "print(\"ID has null values: \", submissions[\"id\"].isnull().values.any())\n",
    "print(\"ID has missing values: \", submissions[\"id\"].isna().values.any())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Great, submissions seem to be clean. Lets check the comments now "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Comments: \n",
      "Shape size:  7242871\n",
      "ID size:  7242871\n",
      "ID unique:  True\n",
      "ID has null values:  True\n",
      "ID has missing values:  True\n"
     ]
    }
   ],
   "source": [
    "print(\"Comments: \")\n",
    "print(\"Shape size: \", comments.shape[0])\n",
    "print(\"ID size: \", comments[\"id\"].shape[0])\n",
    "print(\"ID unique: \", comments[\"id\"].is_unique)\n",
    "print(\"ID has null values: \", comments[\"id\"].isnull().values.any())\n",
    "print(\"ID has missing values: \", comments[\"id\"].isna().values.any())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of null values:  1\n"
     ]
    }
   ],
   "source": [
    "print(\"Number of null values: \", comments[comments[\"id\"].isnull() == True].shape[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since it is only 1/7M, we can drop it"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "comments.dropna(subset=['id'], inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Comments: \n",
      "Shape size:  7242870\n",
      "ID size:  7242870\n",
      "ID unique:  True\n",
      "ID has null values:  False\n",
      "ID has missing values:  False\n"
     ]
    }
   ],
   "source": [
    "print(\"Comments: \")\n",
    "print(\"Shape size: \", comments.shape[0])\n",
    "print(\"ID size: \", comments[\"id\"].shape[0])\n",
    "print(\"ID unique: \", comments[\"id\"].is_unique)\n",
    "print(\"ID has null values: \", comments[\"id\"].isnull().values.any())\n",
    "print(\"ID has missing values: \", comments[\"id\"].isna().values.any())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### On further inspection!\n",
    "\n",
    "A good observation is that each subreddit must have a unique url. I will check that as well.\n",
    "\n",
    "There seem to be authors that have been deleted. Their submissions and comment's should be deleted possibly\n",
    "\n",
    "\n",
    "1. Unique URLs:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Submissions: \n",
      "Shape size:  2519853\n",
      "URL size:  2519853\n",
      "Is it the same size:  True\n"
     ]
    }
   ],
   "source": [
    "print(\"Submissions: \")\n",
    "print(\"Shape size: \", submissions.shape[0])\n",
    "print(\"URL size: \", submissions[\"url\"].shape[0])\n",
    "print(\"Is it the same size: \", submissions.shape[0] == submissions[\"url\"].shape[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2. Delete submissions and comments with who's author was deleted"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "submissions = submissions[submissions[\"author\"] != \"[deleted]\"]\n",
    "comments = comments[comments[\"author\"] != \"[deleted]\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Actually, there could be null values now, I should check everything again!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Submissions: \n",
      "Shape size:  2044810\n",
      "ID size:  2044810\n",
      "ID unique:  True\n",
      "ID has null values:  False\n",
      "ID has missing values:  False\n",
      "Are URLs unique:  True\n"
     ]
    }
   ],
   "source": [
    "print(\"Submissions: \")\n",
    "print(\"Shape size: \", submissions.shape[0])\n",
    "print(\"ID size: \", submissions[\"id\"].shape[0])\n",
    "print(\"ID unique: \", submissions[\"id\"].is_unique)\n",
    "print(\"ID has null values: \", submissions[\"id\"].isnull().values.any())\n",
    "print(\"ID has missing values: \", submissions[\"id\"].isna().values.any())\n",
    "print(\"Are URLs unique: \", submissions.shape[0] == submissions[\"url\"].shape[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Comments: \n",
      "Shape size:  4873689\n",
      "ID size:  4873689\n",
      "ID unique:  True\n",
      "ID has null values:  False\n",
      "ID has missing values:  False\n"
     ]
    }
   ],
   "source": [
    "print(\"Comments: \")\n",
    "print(\"Shape size: \", comments.shape[0])\n",
    "print(\"ID size: \", comments[\"id\"].shape[0])\n",
    "print(\"ID unique: \", comments[\"id\"].is_unique)\n",
    "print(\"ID has null values: \", comments[\"id\"].isnull().values.any())\n",
    "print(\"ID has missing values: \", comments[\"id\"].isna().values.any())"
   ]
  },
  {
   "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>id</th>\n",
       "      <th>url</th>\n",
       "      <th>permalink</th>\n",
       "      <th>author</th>\n",
       "      <th>created_utc</th>\n",
       "      <th>subreddit</th>\n",
       "      <th>subreddit_id</th>\n",
       "      <th>num_comments</th>\n",
       "      <th>score</th>\n",
       "      <th>over_18</th>\n",
       "      <th>distinguished</th>\n",
       "      <th>domain</th>\n",
       "      <th>stickied</th>\n",
       "      <th>locked</th>\n",
       "      <th>hide_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>648oo</td>\n",
       "      <td>http://www.ignorancedenied.com/viewthread.php?...</td>\n",
       "      <td>/r/reddit.com/comments/648oo/brain_disease_is_...</td>\n",
       "      <td>DITUS</td>\n",
       "      <td>1199145615</td>\n",
       "      <td>reddit.com</td>\n",
       "      <td>t5_6</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>ignorancedenied.com</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>648op</td>\n",
       "      <td>http://www.flascience.org/wp/?p=363</td>\n",
       "      <td>/r/science/comments/648op/three_more_florida_c...</td>\n",
       "      <td>rmuser</td>\n",
       "      <td>1199145634</td>\n",
       "      <td>science</td>\n",
       "      <td>t5_mouw</td>\n",
       "      <td>5</td>\n",
       "      <td>20</td>\n",
       "      <td>False</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>flascience.org</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>648or</td>\n",
       "      <td>http://hosted.ap.org/dynamic/stories/O/ODD_SHO...</td>\n",
       "      <td>/r/reddit.com/comments/648or/nude_couple_grapp...</td>\n",
       "      <td>zorno</td>\n",
       "      <td>1199145709</td>\n",
       "      <td>reddit.com</td>\n",
       "      <td>t5_6</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>False</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>hosted.ap.org</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>648os</td>\n",
       "      <td>http://www.sltrib.com/opinion/ci_7846101?sourc...</td>\n",
       "      <td>/r/politics/comments/648os/apparently_bushs_pr...</td>\n",
       "      <td>rmuser</td>\n",
       "      <td>1199145735</td>\n",
       "      <td>politics</td>\n",
       "      <td>t5_2cneq</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>sltrib.com</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>648ot</td>\n",
       "      <td>http://hosted.ap.org/dynamic/stories/O/ODD_RAR...</td>\n",
       "      <td>/r/reddit.com/comments/648ot/diners_find_rare_...</td>\n",
       "      <td>zorno</td>\n",
       "      <td>1199145735</td>\n",
       "      <td>reddit.com</td>\n",
       "      <td>t5_6</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>hosted.ap.org</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>283906</th>\n",
       "      <td>7mq3n</td>\n",
       "      <td>http://ventaboutsports.blogspot.com/2008/12/so...</td>\n",
       "      <td>/r/funny/comments/7mq3n/some_extremely_corny_j...</td>\n",
       "      <td>themightymidget</td>\n",
       "      <td>1230767909</td>\n",
       "      <td>funny</td>\n",
       "      <td>t5_2qh33</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>ventaboutsports.blogspot.com</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>283907</th>\n",
       "      <td>7mq3o</td>\n",
       "      <td>http://www.pbs.org/mormons/etc/genealogy.html</td>\n",
       "      <td>/r/news/comments/7mq3o/pbs_looks_at_the_massiv...</td>\n",
       "      <td>Tom22</td>\n",
       "      <td>1230767926</td>\n",
       "      <td>news</td>\n",
       "      <td>t5_2qh3l</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>pbs.org</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>283909</th>\n",
       "      <td>7mq3q</td>\n",
       "      <td>http://www.narutogames.biz</td>\n",
       "      <td>/r/reddit.com/comments/7mq3q/naruto_games/</td>\n",
       "      <td>bixiebix</td>\n",
       "      <td>1230767937</td>\n",
       "      <td>reddit.com</td>\n",
       "      <td>t5_6</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>narutogames.biz</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>283910</th>\n",
       "      <td>7mq3r</td>\n",
       "      <td>http://www.youtube.com/watch?v=gdQH1CI4LHY&amp;amp...</td>\n",
       "      <td>/r/politics/comments/7mq3r/ron_paul_on_recent_...</td>\n",
       "      <td>middkidd</td>\n",
       "      <td>1230767963</td>\n",
       "      <td>politics</td>\n",
       "      <td>t5_2cneq</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>youtube.com</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>283914</th>\n",
       "      <td>7mq3v</td>\n",
       "      <td>http://player.video.news.com.au/dailytelegraph...</td>\n",
       "      <td>/r/reddit.com/comments/7mq3v/video_nye_firewor...</td>\n",
       "      <td>klippa</td>\n",
       "      <td>1230767988</td>\n",
       "      <td>reddit.com</td>\n",
       "      <td>t5_6</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>player.video.news.com.au</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2044810 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           id                                                url  \\\n",
       "0       648oo  http://www.ignorancedenied.com/viewthread.php?...   \n",
       "1       648op                http://www.flascience.org/wp/?p=363   \n",
       "3       648or  http://hosted.ap.org/dynamic/stories/O/ODD_SHO...   \n",
       "4       648os  http://www.sltrib.com/opinion/ci_7846101?sourc...   \n",
       "5       648ot  http://hosted.ap.org/dynamic/stories/O/ODD_RAR...   \n",
       "...       ...                                                ...   \n",
       "283906  7mq3n  http://ventaboutsports.blogspot.com/2008/12/so...   \n",
       "283907  7mq3o      http://www.pbs.org/mormons/etc/genealogy.html   \n",
       "283909  7mq3q                         http://www.narutogames.biz   \n",
       "283910  7mq3r  http://www.youtube.com/watch?v=gdQH1CI4LHY&amp...   \n",
       "283914  7mq3v  http://player.video.news.com.au/dailytelegraph...   \n",
       "\n",
       "                                                permalink           author  \\\n",
       "0       /r/reddit.com/comments/648oo/brain_disease_is_...            DITUS   \n",
       "1       /r/science/comments/648op/three_more_florida_c...           rmuser   \n",
       "3       /r/reddit.com/comments/648or/nude_couple_grapp...            zorno   \n",
       "4       /r/politics/comments/648os/apparently_bushs_pr...           rmuser   \n",
       "5       /r/reddit.com/comments/648ot/diners_find_rare_...            zorno   \n",
       "...                                                   ...              ...   \n",
       "283906  /r/funny/comments/7mq3n/some_extremely_corny_j...  themightymidget   \n",
       "283907  /r/news/comments/7mq3o/pbs_looks_at_the_massiv...            Tom22   \n",
       "283909         /r/reddit.com/comments/7mq3q/naruto_games/         bixiebix   \n",
       "283910  /r/politics/comments/7mq3r/ron_paul_on_recent_...         middkidd   \n",
       "283914  /r/reddit.com/comments/7mq3v/video_nye_firewor...           klippa   \n",
       "\n",
       "        created_utc   subreddit subreddit_id  num_comments  score  over_18  \\\n",
       "0        1199145615  reddit.com         t5_6             1      0    False   \n",
       "1        1199145634     science      t5_mouw             5     20    False   \n",
       "3        1199145709  reddit.com         t5_6             1      3    False   \n",
       "4        1199145735    politics     t5_2cneq             2      0    False   \n",
       "5        1199145735  reddit.com         t5_6             0      0    False   \n",
       "...             ...         ...          ...           ...    ...      ...   \n",
       "283906   1230767909       funny     t5_2qh33             0      1    False   \n",
       "283907   1230767926        news     t5_2qh3l             0      0    False   \n",
       "283909   1230767937  reddit.com         t5_6             7      1    False   \n",
       "283910   1230767963    politics     t5_2cneq             3      1    False   \n",
       "283914   1230767988  reddit.com         t5_6             1      1    False   \n",
       "\n",
       "       distinguished                        domain  stickied  locked  \\\n",
       "0               <NA>           ignorancedenied.com     False   False   \n",
       "1               <NA>                flascience.org     False   False   \n",
       "3               <NA>                 hosted.ap.org     False   False   \n",
       "4               <NA>                    sltrib.com     False   False   \n",
       "5               <NA>                 hosted.ap.org     False   False   \n",
       "...              ...                           ...       ...     ...   \n",
       "283906          <NA>  ventaboutsports.blogspot.com     False   False   \n",
       "283907          <NA>                       pbs.org     False   False   \n",
       "283909          <NA>               narutogames.biz     False   False   \n",
       "283910          <NA>                   youtube.com     False   False   \n",
       "283914          <NA>      player.video.news.com.au     False   False   \n",
       "\n",
       "        hide_score  \n",
       "0            False  \n",
       "1            False  \n",
       "3            False  \n",
       "4            False  \n",
       "5            False  \n",
       "...            ...  \n",
       "283906       False  \n",
       "283907       False  \n",
       "283909       False  \n",
       "283910       False  \n",
       "283914       False  \n",
       "\n",
       "[2044810 rows x 15 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "submissions"
   ]
  },
  {
   "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>id</th>\n",
       "      <th>author</th>\n",
       "      <th>link_id</th>\n",
       "      <th>parent_id</th>\n",
       "      <th>created_utc</th>\n",
       "      <th>subreddit</th>\n",
       "      <th>subreddit_id</th>\n",
       "      <th>score</th>\n",
       "      <th>distinguished</th>\n",
       "      <th>gilded</th>\n",
       "      <th>controversiality</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>c02s9s6</td>\n",
       "      <td>Haven</td>\n",
       "      <td>t3_648oh</td>\n",
       "      <td>t1_c02s9rv</td>\n",
       "      <td>1199145604</td>\n",
       "      <td>reddit.com</td>\n",
       "      <td>t5_6</td>\n",
       "      <td>4</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>c02s9s8</td>\n",
       "      <td>lilmiss2</td>\n",
       "      <td>t3_648oh</td>\n",
       "      <td>t1_c02s9rv</td>\n",
       "      <td>1199145620</td>\n",
       "      <td>reddit.com</td>\n",
       "      <td>t5_6</td>\n",
       "      <td>2</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>c02s9sc</td>\n",
       "      <td>EverybodysAnAsshole</td>\n",
       "      <td>t3_648et</td>\n",
       "      <td>t1_c02s976</td>\n",
       "      <td>1199145644</td>\n",
       "      <td>reddit.com</td>\n",
       "      <td>t5_6</td>\n",
       "      <td>2</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>c02s9sd</td>\n",
       "      <td>generalk</td>\n",
       "      <td>t3_647yd</td>\n",
       "      <td>t1_c02s8md</td>\n",
       "      <td>1199145647</td>\n",
       "      <td>programming</td>\n",
       "      <td>t5_2fwo</td>\n",
       "      <td>13</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>c02s9se</td>\n",
       "      <td>seeker135</td>\n",
       "      <td>t3_6483n</td>\n",
       "      <td>t3_6483n</td>\n",
       "      <td>1199145650</td>\n",
       "      <td>politics</td>\n",
       "      <td>t5_2cneq</td>\n",
       "      <td>4</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>850352</th>\n",
       "      <td>c06vwud</td>\n",
       "      <td>CommodoreGuff</td>\n",
       "      <td>t3_7k1l5</td>\n",
       "      <td>t1_c06vpzj</td>\n",
       "      <td>1229579674</td>\n",
       "      <td>programming</td>\n",
       "      <td>t5_2fwo</td>\n",
       "      <td>1</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>850353</th>\n",
       "      <td>c06vwue</td>\n",
       "      <td>wolfzero</td>\n",
       "      <td>t3_7k4if</td>\n",
       "      <td>t1_c06vs7l</td>\n",
       "      <td>1229579675</td>\n",
       "      <td>technology</td>\n",
       "      <td>t5_2qh16</td>\n",
       "      <td>4</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>850355</th>\n",
       "      <td>c06vwug</td>\n",
       "      <td>Morgin_Black</td>\n",
       "      <td>t3_7k3w5</td>\n",
       "      <td>t3_7k3w5</td>\n",
       "      <td>1229579679</td>\n",
       "      <td>comics</td>\n",
       "      <td>t5_2qh0s</td>\n",
       "      <td>0</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>850357</th>\n",
       "      <td>c06vwui</td>\n",
       "      <td>onezerozeroone</td>\n",
       "      <td>t3_7k2bc</td>\n",
       "      <td>t1_c06vrvz</td>\n",
       "      <td>1229579685</td>\n",
       "      <td>atheism</td>\n",
       "      <td>t5_2qh2p</td>\n",
       "      <td>1</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>850358</th>\n",
       "      <td>c06vwuj</td>\n",
       "      <td>vypermann</td>\n",
       "      <td>t3_7k65l</td>\n",
       "      <td>t1_c06vvzu</td>\n",
       "      <td>1229579686</td>\n",
       "      <td>WTF</td>\n",
       "      <td>t5_2qh61</td>\n",
       "      <td>0</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4873689 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             id               author   link_id   parent_id  created_utc  \\\n",
       "0       c02s9s6                Haven  t3_648oh  t1_c02s9rv   1199145604   \n",
       "2       c02s9s8             lilmiss2  t3_648oh  t1_c02s9rv   1199145620   \n",
       "6       c02s9sc  EverybodysAnAsshole  t3_648et  t1_c02s976   1199145644   \n",
       "7       c02s9sd             generalk  t3_647yd  t1_c02s8md   1199145647   \n",
       "8       c02s9se            seeker135  t3_6483n    t3_6483n   1199145650   \n",
       "...         ...                  ...       ...         ...          ...   \n",
       "850352  c06vwud        CommodoreGuff  t3_7k1l5  t1_c06vpzj   1229579674   \n",
       "850353  c06vwue             wolfzero  t3_7k4if  t1_c06vs7l   1229579675   \n",
       "850355  c06vwug         Morgin_Black  t3_7k3w5    t3_7k3w5   1229579679   \n",
       "850357  c06vwui       onezerozeroone  t3_7k2bc  t1_c06vrvz   1229579685   \n",
       "850358  c06vwuj            vypermann  t3_7k65l  t1_c06vvzu   1229579686   \n",
       "\n",
       "          subreddit subreddit_id  score distinguished  gilded  \\\n",
       "0        reddit.com         t5_6      4          <NA>       0   \n",
       "2        reddit.com         t5_6      2          <NA>       0   \n",
       "6        reddit.com         t5_6      2          <NA>       0   \n",
       "7       programming      t5_2fwo     13          <NA>       0   \n",
       "8          politics     t5_2cneq      4          <NA>       0   \n",
       "...             ...          ...    ...           ...     ...   \n",
       "850352  programming      t5_2fwo      1          <NA>       0   \n",
       "850353   technology     t5_2qh16      4          <NA>       0   \n",
       "850355       comics     t5_2qh0s      0          <NA>       0   \n",
       "850357      atheism     t5_2qh2p      1          <NA>       0   \n",
       "850358          WTF     t5_2qh61      0          <NA>       0   \n",
       "\n",
       "        controversiality  \n",
       "0                      0  \n",
       "2                      0  \n",
       "6                      0  \n",
       "7                      0  \n",
       "8                      0  \n",
       "...                  ...  \n",
       "850352                 0  \n",
       "850353                 0  \n",
       "850355                 0  \n",
       "850357                 0  \n",
       "850358                 0  \n",
       "\n",
       "[4873689 rows x 11 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "comments"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's also rename ID for good practice"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>submissions_id</th>\n",
       "      <th>url</th>\n",
       "      <th>permalink</th>\n",
       "      <th>author</th>\n",
       "      <th>created_utc</th>\n",
       "      <th>subreddit</th>\n",
       "      <th>subreddit_id</th>\n",
       "      <th>num_comments</th>\n",
       "      <th>score</th>\n",
       "      <th>over_18</th>\n",
       "      <th>distinguished</th>\n",
       "      <th>domain</th>\n",
       "      <th>stickied</th>\n",
       "      <th>locked</th>\n",
       "      <th>hide_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>648oo</td>\n",
       "      <td>http://www.ignorancedenied.com/viewthread.php?...</td>\n",
       "      <td>/r/reddit.com/comments/648oo/brain_disease_is_...</td>\n",
       "      <td>DITUS</td>\n",
       "      <td>1199145615</td>\n",
       "      <td>reddit.com</td>\n",
       "      <td>t5_6</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>ignorancedenied.com</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>648op</td>\n",
       "      <td>http://www.flascience.org/wp/?p=363</td>\n",
       "      <td>/r/science/comments/648op/three_more_florida_c...</td>\n",
       "      <td>rmuser</td>\n",
       "      <td>1199145634</td>\n",
       "      <td>science</td>\n",
       "      <td>t5_mouw</td>\n",
       "      <td>5</td>\n",
       "      <td>20</td>\n",
       "      <td>False</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>flascience.org</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>648or</td>\n",
       "      <td>http://hosted.ap.org/dynamic/stories/O/ODD_SHO...</td>\n",
       "      <td>/r/reddit.com/comments/648or/nude_couple_grapp...</td>\n",
       "      <td>zorno</td>\n",
       "      <td>1199145709</td>\n",
       "      <td>reddit.com</td>\n",
       "      <td>t5_6</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>False</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>hosted.ap.org</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>648os</td>\n",
       "      <td>http://www.sltrib.com/opinion/ci_7846101?sourc...</td>\n",
       "      <td>/r/politics/comments/648os/apparently_bushs_pr...</td>\n",
       "      <td>rmuser</td>\n",
       "      <td>1199145735</td>\n",
       "      <td>politics</td>\n",
       "      <td>t5_2cneq</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>sltrib.com</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>648ot</td>\n",
       "      <td>http://hosted.ap.org/dynamic/stories/O/ODD_RAR...</td>\n",
       "      <td>/r/reddit.com/comments/648ot/diners_find_rare_...</td>\n",
       "      <td>zorno</td>\n",
       "      <td>1199145735</td>\n",
       "      <td>reddit.com</td>\n",
       "      <td>t5_6</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>hosted.ap.org</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2044805</th>\n",
       "      <td>7mq3n</td>\n",
       "      <td>http://ventaboutsports.blogspot.com/2008/12/so...</td>\n",
       "      <td>/r/funny/comments/7mq3n/some_extremely_corny_j...</td>\n",
       "      <td>themightymidget</td>\n",
       "      <td>1230767909</td>\n",
       "      <td>funny</td>\n",
       "      <td>t5_2qh33</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>ventaboutsports.blogspot.com</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2044806</th>\n",
       "      <td>7mq3o</td>\n",
       "      <td>http://www.pbs.org/mormons/etc/genealogy.html</td>\n",
       "      <td>/r/news/comments/7mq3o/pbs_looks_at_the_massiv...</td>\n",
       "      <td>Tom22</td>\n",
       "      <td>1230767926</td>\n",
       "      <td>news</td>\n",
       "      <td>t5_2qh3l</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>pbs.org</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2044807</th>\n",
       "      <td>7mq3q</td>\n",
       "      <td>http://www.narutogames.biz</td>\n",
       "      <td>/r/reddit.com/comments/7mq3q/naruto_games/</td>\n",
       "      <td>bixiebix</td>\n",
       "      <td>1230767937</td>\n",
       "      <td>reddit.com</td>\n",
       "      <td>t5_6</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>narutogames.biz</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2044808</th>\n",
       "      <td>7mq3r</td>\n",
       "      <td>http://www.youtube.com/watch?v=gdQH1CI4LHY&amp;amp...</td>\n",
       "      <td>/r/politics/comments/7mq3r/ron_paul_on_recent_...</td>\n",
       "      <td>middkidd</td>\n",
       "      <td>1230767963</td>\n",
       "      <td>politics</td>\n",
       "      <td>t5_2cneq</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>youtube.com</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2044809</th>\n",
       "      <td>7mq3v</td>\n",
       "      <td>http://player.video.news.com.au/dailytelegraph...</td>\n",
       "      <td>/r/reddit.com/comments/7mq3v/video_nye_firewor...</td>\n",
       "      <td>klippa</td>\n",
       "      <td>1230767988</td>\n",
       "      <td>reddit.com</td>\n",
       "      <td>t5_6</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>player.video.news.com.au</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2044810 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        submissions_id                                                url  \\\n",
       "0                648oo  http://www.ignorancedenied.com/viewthread.php?...   \n",
       "1                648op                http://www.flascience.org/wp/?p=363   \n",
       "2                648or  http://hosted.ap.org/dynamic/stories/O/ODD_SHO...   \n",
       "3                648os  http://www.sltrib.com/opinion/ci_7846101?sourc...   \n",
       "4                648ot  http://hosted.ap.org/dynamic/stories/O/ODD_RAR...   \n",
       "...                ...                                                ...   \n",
       "2044805          7mq3n  http://ventaboutsports.blogspot.com/2008/12/so...   \n",
       "2044806          7mq3o      http://www.pbs.org/mormons/etc/genealogy.html   \n",
       "2044807          7mq3q                         http://www.narutogames.biz   \n",
       "2044808          7mq3r  http://www.youtube.com/watch?v=gdQH1CI4LHY&amp...   \n",
       "2044809          7mq3v  http://player.video.news.com.au/dailytelegraph...   \n",
       "\n",
       "                                                 permalink           author  \\\n",
       "0        /r/reddit.com/comments/648oo/brain_disease_is_...            DITUS   \n",
       "1        /r/science/comments/648op/three_more_florida_c...           rmuser   \n",
       "2        /r/reddit.com/comments/648or/nude_couple_grapp...            zorno   \n",
       "3        /r/politics/comments/648os/apparently_bushs_pr...           rmuser   \n",
       "4        /r/reddit.com/comments/648ot/diners_find_rare_...            zorno   \n",
       "...                                                    ...              ...   \n",
       "2044805  /r/funny/comments/7mq3n/some_extremely_corny_j...  themightymidget   \n",
       "2044806  /r/news/comments/7mq3o/pbs_looks_at_the_massiv...            Tom22   \n",
       "2044807         /r/reddit.com/comments/7mq3q/naruto_games/         bixiebix   \n",
       "2044808  /r/politics/comments/7mq3r/ron_paul_on_recent_...         middkidd   \n",
       "2044809  /r/reddit.com/comments/7mq3v/video_nye_firewor...           klippa   \n",
       "\n",
       "         created_utc   subreddit subreddit_id  num_comments  score  over_18  \\\n",
       "0         1199145615  reddit.com         t5_6             1      0    False   \n",
       "1         1199145634     science      t5_mouw             5     20    False   \n",
       "2         1199145709  reddit.com         t5_6             1      3    False   \n",
       "3         1199145735    politics     t5_2cneq             2      0    False   \n",
       "4         1199145735  reddit.com         t5_6             0      0    False   \n",
       "...              ...         ...          ...           ...    ...      ...   \n",
       "2044805   1230767909       funny     t5_2qh33             0      1    False   \n",
       "2044806   1230767926        news     t5_2qh3l             0      0    False   \n",
       "2044807   1230767937  reddit.com         t5_6             7      1    False   \n",
       "2044808   1230767963    politics     t5_2cneq             3      1    False   \n",
       "2044809   1230767988  reddit.com         t5_6             1      1    False   \n",
       "\n",
       "        distinguished                        domain  stickied  locked  \\\n",
       "0                <NA>           ignorancedenied.com     False   False   \n",
       "1                <NA>                flascience.org     False   False   \n",
       "2                <NA>                 hosted.ap.org     False   False   \n",
       "3                <NA>                    sltrib.com     False   False   \n",
       "4                <NA>                 hosted.ap.org     False   False   \n",
       "...               ...                           ...       ...     ...   \n",
       "2044805          <NA>  ventaboutsports.blogspot.com     False   False   \n",
       "2044806          <NA>                       pbs.org     False   False   \n",
       "2044807          <NA>               narutogames.biz     False   False   \n",
       "2044808          <NA>                   youtube.com     False   False   \n",
       "2044809          <NA>      player.video.news.com.au     False   False   \n",
       "\n",
       "         hide_score  \n",
       "0             False  \n",
       "1             False  \n",
       "2             False  \n",
       "3             False  \n",
       "4             False  \n",
       "...             ...  \n",
       "2044805       False  \n",
       "2044806       False  \n",
       "2044807       False  \n",
       "2044808       False  \n",
       "2044809       False  \n",
       "\n",
       "[2044810 rows x 15 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "submissions.rename(columns={\"id\": \"submissions_id\"}, inplace=True)\n",
    "submissions.reset_index(drop=True, inplace=True)\n",
    "submissions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>comments_id</th>\n",
       "      <th>author</th>\n",
       "      <th>link_id</th>\n",
       "      <th>parent_id</th>\n",
       "      <th>created_utc</th>\n",
       "      <th>subreddit</th>\n",
       "      <th>subreddit_id</th>\n",
       "      <th>score</th>\n",
       "      <th>distinguished</th>\n",
       "      <th>gilded</th>\n",
       "      <th>controversiality</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>c02s9s6</td>\n",
       "      <td>Haven</td>\n",
       "      <td>t3_648oh</td>\n",
       "      <td>t1_c02s9rv</td>\n",
       "      <td>1199145604</td>\n",
       "      <td>reddit.com</td>\n",
       "      <td>t5_6</td>\n",
       "      <td>4</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>c02s9s8</td>\n",
       "      <td>lilmiss2</td>\n",
       "      <td>t3_648oh</td>\n",
       "      <td>t1_c02s9rv</td>\n",
       "      <td>1199145620</td>\n",
       "      <td>reddit.com</td>\n",
       "      <td>t5_6</td>\n",
       "      <td>2</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>c02s9sc</td>\n",
       "      <td>EverybodysAnAsshole</td>\n",
       "      <td>t3_648et</td>\n",
       "      <td>t1_c02s976</td>\n",
       "      <td>1199145644</td>\n",
       "      <td>reddit.com</td>\n",
       "      <td>t5_6</td>\n",
       "      <td>2</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>c02s9sd</td>\n",
       "      <td>generalk</td>\n",
       "      <td>t3_647yd</td>\n",
       "      <td>t1_c02s8md</td>\n",
       "      <td>1199145647</td>\n",
       "      <td>programming</td>\n",
       "      <td>t5_2fwo</td>\n",
       "      <td>13</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>c02s9se</td>\n",
       "      <td>seeker135</td>\n",
       "      <td>t3_6483n</td>\n",
       "      <td>t3_6483n</td>\n",
       "      <td>1199145650</td>\n",
       "      <td>politics</td>\n",
       "      <td>t5_2cneq</td>\n",
       "      <td>4</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4873684</th>\n",
       "      <td>c06vwud</td>\n",
       "      <td>CommodoreGuff</td>\n",
       "      <td>t3_7k1l5</td>\n",
       "      <td>t1_c06vpzj</td>\n",
       "      <td>1229579674</td>\n",
       "      <td>programming</td>\n",
       "      <td>t5_2fwo</td>\n",
       "      <td>1</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4873685</th>\n",
       "      <td>c06vwue</td>\n",
       "      <td>wolfzero</td>\n",
       "      <td>t3_7k4if</td>\n",
       "      <td>t1_c06vs7l</td>\n",
       "      <td>1229579675</td>\n",
       "      <td>technology</td>\n",
       "      <td>t5_2qh16</td>\n",
       "      <td>4</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4873686</th>\n",
       "      <td>c06vwug</td>\n",
       "      <td>Morgin_Black</td>\n",
       "      <td>t3_7k3w5</td>\n",
       "      <td>t3_7k3w5</td>\n",
       "      <td>1229579679</td>\n",
       "      <td>comics</td>\n",
       "      <td>t5_2qh0s</td>\n",
       "      <td>0</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4873687</th>\n",
       "      <td>c06vwui</td>\n",
       "      <td>onezerozeroone</td>\n",
       "      <td>t3_7k2bc</td>\n",
       "      <td>t1_c06vrvz</td>\n",
       "      <td>1229579685</td>\n",
       "      <td>atheism</td>\n",
       "      <td>t5_2qh2p</td>\n",
       "      <td>1</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4873688</th>\n",
       "      <td>c06vwuj</td>\n",
       "      <td>vypermann</td>\n",
       "      <td>t3_7k65l</td>\n",
       "      <td>t1_c06vvzu</td>\n",
       "      <td>1229579686</td>\n",
       "      <td>WTF</td>\n",
       "      <td>t5_2qh61</td>\n",
       "      <td>0</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4873689 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        comments_id               author   link_id   parent_id  created_utc  \\\n",
       "0           c02s9s6                Haven  t3_648oh  t1_c02s9rv   1199145604   \n",
       "1           c02s9s8             lilmiss2  t3_648oh  t1_c02s9rv   1199145620   \n",
       "2           c02s9sc  EverybodysAnAsshole  t3_648et  t1_c02s976   1199145644   \n",
       "3           c02s9sd             generalk  t3_647yd  t1_c02s8md   1199145647   \n",
       "4           c02s9se            seeker135  t3_6483n    t3_6483n   1199145650   \n",
       "...             ...                  ...       ...         ...          ...   \n",
       "4873684     c06vwud        CommodoreGuff  t3_7k1l5  t1_c06vpzj   1229579674   \n",
       "4873685     c06vwue             wolfzero  t3_7k4if  t1_c06vs7l   1229579675   \n",
       "4873686     c06vwug         Morgin_Black  t3_7k3w5    t3_7k3w5   1229579679   \n",
       "4873687     c06vwui       onezerozeroone  t3_7k2bc  t1_c06vrvz   1229579685   \n",
       "4873688     c06vwuj            vypermann  t3_7k65l  t1_c06vvzu   1229579686   \n",
       "\n",
       "           subreddit subreddit_id  score distinguished  gilded  \\\n",
       "0         reddit.com         t5_6      4          <NA>       0   \n",
       "1         reddit.com         t5_6      2          <NA>       0   \n",
       "2         reddit.com         t5_6      2          <NA>       0   \n",
       "3        programming      t5_2fwo     13          <NA>       0   \n",
       "4           politics     t5_2cneq      4          <NA>       0   \n",
       "...              ...          ...    ...           ...     ...   \n",
       "4873684  programming      t5_2fwo      1          <NA>       0   \n",
       "4873685   technology     t5_2qh16      4          <NA>       0   \n",
       "4873686       comics     t5_2qh0s      0          <NA>       0   \n",
       "4873687      atheism     t5_2qh2p      1          <NA>       0   \n",
       "4873688          WTF     t5_2qh61      0          <NA>       0   \n",
       "\n",
       "         controversiality  \n",
       "0                       0  \n",
       "1                       0  \n",
       "2                       0  \n",
       "3                       0  \n",
       "4                       0  \n",
       "...                   ...  \n",
       "4873684                 0  \n",
       "4873685                 0  \n",
       "4873686                 0  \n",
       "4873687                 0  \n",
       "4873688                 0  \n",
       "\n",
       "[4873689 rows x 11 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "comments.rename(columns={\"id\": \"comments_id\"}, inplace=True)\n",
    "comments.reset_index(drop=True, inplace=True)\n",
    "comments"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Missed observation\n",
    "\n",
    "The comments column *link_id* is the submissions id\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Does link_id start with anything other than \"t3_\":  False\n",
      "Does link_id start with anything other than \"t3_\" or \"t1_\":  False\n"
     ]
    }
   ],
   "source": [
    "print(\"Does link_id start with anything other than \\\"t3_\\\": \", comments[comments[\"link_id\"].str.startswith('t3_') == False].shape[0] != 0)\n",
    "print(\"Does link_id start with anything other than \\\"t3_\\\" or \\\"t1_\\\": \", comments[(comments[\"parent_id\"].str.startswith('t3_') == False) & ((comments[\"parent_id\"].str.startswith('t1_') == False))].shape[0] != 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>comments_id</th>\n",
       "      <th>author</th>\n",
       "      <th>link_id</th>\n",
       "      <th>parent_id</th>\n",
       "      <th>created_utc</th>\n",
       "      <th>subreddit</th>\n",
       "      <th>subreddit_id</th>\n",
       "      <th>score</th>\n",
       "      <th>distinguished</th>\n",
       "      <th>gilded</th>\n",
       "      <th>controversiality</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>c02s9s6</td>\n",
       "      <td>Haven</td>\n",
       "      <td>648oh</td>\n",
       "      <td>c02s9rv</td>\n",
       "      <td>1199145604</td>\n",
       "      <td>reddit.com</td>\n",
       "      <td>t5_6</td>\n",
       "      <td>4</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>c02s9s8</td>\n",
       "      <td>lilmiss2</td>\n",
       "      <td>648oh</td>\n",
       "      <td>c02s9rv</td>\n",
       "      <td>1199145620</td>\n",
       "      <td>reddit.com</td>\n",
       "      <td>t5_6</td>\n",
       "      <td>2</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>c02s9sc</td>\n",
       "      <td>EverybodysAnAsshole</td>\n",
       "      <td>648et</td>\n",
       "      <td>c02s976</td>\n",
       "      <td>1199145644</td>\n",
       "      <td>reddit.com</td>\n",
       "      <td>t5_6</td>\n",
       "      <td>2</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>c02s9sd</td>\n",
       "      <td>generalk</td>\n",
       "      <td>647yd</td>\n",
       "      <td>c02s8md</td>\n",
       "      <td>1199145647</td>\n",
       "      <td>programming</td>\n",
       "      <td>t5_2fwo</td>\n",
       "      <td>13</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>c02s9se</td>\n",
       "      <td>seeker135</td>\n",
       "      <td>6483n</td>\n",
       "      <td>6483n</td>\n",
       "      <td>1199145650</td>\n",
       "      <td>politics</td>\n",
       "      <td>t5_2cneq</td>\n",
       "      <td>4</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4873684</th>\n",
       "      <td>c06vwud</td>\n",
       "      <td>CommodoreGuff</td>\n",
       "      <td>7k1l5</td>\n",
       "      <td>c06vpzj</td>\n",
       "      <td>1229579674</td>\n",
       "      <td>programming</td>\n",
       "      <td>t5_2fwo</td>\n",
       "      <td>1</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4873685</th>\n",
       "      <td>c06vwue</td>\n",
       "      <td>wolfzero</td>\n",
       "      <td>7k4if</td>\n",
       "      <td>c06vs7l</td>\n",
       "      <td>1229579675</td>\n",
       "      <td>technology</td>\n",
       "      <td>t5_2qh16</td>\n",
       "      <td>4</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4873686</th>\n",
       "      <td>c06vwug</td>\n",
       "      <td>Morgin_Black</td>\n",
       "      <td>7k3w5</td>\n",
       "      <td>7k3w5</td>\n",
       "      <td>1229579679</td>\n",
       "      <td>comics</td>\n",
       "      <td>t5_2qh0s</td>\n",
       "      <td>0</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4873687</th>\n",
       "      <td>c06vwui</td>\n",
       "      <td>onezerozeroone</td>\n",
       "      <td>7k2bc</td>\n",
       "      <td>c06vrvz</td>\n",
       "      <td>1229579685</td>\n",
       "      <td>atheism</td>\n",
       "      <td>t5_2qh2p</td>\n",
       "      <td>1</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4873688</th>\n",
       "      <td>c06vwuj</td>\n",
       "      <td>vypermann</td>\n",
       "      <td>7k65l</td>\n",
       "      <td>c06vvzu</td>\n",
       "      <td>1229579686</td>\n",
       "      <td>WTF</td>\n",
       "      <td>t5_2qh61</td>\n",
       "      <td>0</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4873689 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        comments_id               author link_id parent_id  created_utc  \\\n",
       "0           c02s9s6                Haven   648oh   c02s9rv   1199145604   \n",
       "1           c02s9s8             lilmiss2   648oh   c02s9rv   1199145620   \n",
       "2           c02s9sc  EverybodysAnAsshole   648et   c02s976   1199145644   \n",
       "3           c02s9sd             generalk   647yd   c02s8md   1199145647   \n",
       "4           c02s9se            seeker135   6483n     6483n   1199145650   \n",
       "...             ...                  ...     ...       ...          ...   \n",
       "4873684     c06vwud        CommodoreGuff   7k1l5   c06vpzj   1229579674   \n",
       "4873685     c06vwue             wolfzero   7k4if   c06vs7l   1229579675   \n",
       "4873686     c06vwug         Morgin_Black   7k3w5     7k3w5   1229579679   \n",
       "4873687     c06vwui       onezerozeroone   7k2bc   c06vrvz   1229579685   \n",
       "4873688     c06vwuj            vypermann   7k65l   c06vvzu   1229579686   \n",
       "\n",
       "           subreddit subreddit_id  score distinguished  gilded  \\\n",
       "0         reddit.com         t5_6      4          <NA>       0   \n",
       "1         reddit.com         t5_6      2          <NA>       0   \n",
       "2         reddit.com         t5_6      2          <NA>       0   \n",
       "3        programming      t5_2fwo     13          <NA>       0   \n",
       "4           politics     t5_2cneq      4          <NA>       0   \n",
       "...              ...          ...    ...           ...     ...   \n",
       "4873684  programming      t5_2fwo      1          <NA>       0   \n",
       "4873685   technology     t5_2qh16      4          <NA>       0   \n",
       "4873686       comics     t5_2qh0s      0          <NA>       0   \n",
       "4873687      atheism     t5_2qh2p      1          <NA>       0   \n",
       "4873688          WTF     t5_2qh61      0          <NA>       0   \n",
       "\n",
       "         controversiality  \n",
       "0                       0  \n",
       "1                       0  \n",
       "2                       0  \n",
       "3                       0  \n",
       "4                       0  \n",
       "...                   ...  \n",
       "4873684                 0  \n",
       "4873685                 0  \n",
       "4873686                 0  \n",
       "4873687                 0  \n",
       "4873688                 0  \n",
       "\n",
       "[4873689 rows x 11 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "comments[\"link_id\"] = comments[\"link_id\"].str.slice(3)\n",
    "comments[\"parent_id\"] = comments[\"parent_id\"].str.slice(3)\n",
    "comments"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Deleting unused columns\n",
    "\n",
    "After creating graphs and the static analysis, I have realised I will not probably need any data from the following columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "comments = comments.drop([\"distinguished\", \"gilded\", \"controversiality\"], axis=1)\n",
    "submissions = submissions.drop([\"distinguished\", \"domain\", \"stickied\", \"locked\", \"hide_score\"], axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Additional testing after modeling issues"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "False\n",
      "False\n",
      "False\n",
      "False\n"
     ]
    }
   ],
   "source": [
    "print(comments[\"link_id\"].isna().values.any())\n",
    "print(comments[\"link_id\"].isnull().values.any())\n",
    "print(comments[\"parent_id\"].isna().values.any())\n",
    "print(comments[\"parent_id\"].isnull().values.any())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>submissions_id</th>\n",
       "      <th>url</th>\n",
       "      <th>permalink</th>\n",
       "      <th>author</th>\n",
       "      <th>created_utc</th>\n",
       "      <th>subreddit</th>\n",
       "      <th>subreddit_id</th>\n",
       "      <th>num_comments</th>\n",
       "      <th>score</th>\n",
       "      <th>over_18</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [submissions_id, url, permalink, author, created_utc, subreddit, subreddit_id, num_comments, score, over_18]\n",
       "Index: []"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "comments[comments[\"subreddit\"].notnull() == False]\n",
    "submissions[submissions[\"subreddit\"].notnull() == False]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(Empty DataFrame\n",
       " Columns: [comments_id, author, link_id, parent_id, created_utc, subreddit, subreddit_id, score]\n",
       " Index: [],\n",
       " Empty DataFrame\n",
       " Columns: [submissions_id, url, permalink, author, created_utc, subreddit, subreddit_id, num_comments, score, over_18]\n",
       " Index: [])"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "comments[comments[\"subreddit_id\"].notnull() == False],submissions[submissions[\"subreddit_id\"].notnull() == False]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3783039, 4873689)"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "comments[comments[\"link_id\"].isin(submissions[\"submissions_id\"])].shape[0], comments.shape[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "found it!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3668168, 4873689)"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "comments[(comments[\"parent_id\"].isin(submissions[\"submissions_id\"])) | (comments[\"parent_id\"].isin(comments[\"comments_id\"]))].shape[0], comments.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Before filtering: 4873689\n",
      "After filtering: 3210167\n"
     ]
    }
   ],
   "source": [
    "print(f\"Before filtering: {comments.shape[0]}\")\n",
    "comments = comments[comments[\"link_id\"].isin(submissions[\"submissions_id\"])]\n",
    "comments = comments[(comments[\"parent_id\"].isin(submissions[\"submissions_id\"])) | (comments[\"parent_id\"].isin(comments[\"comments_id\"]))]\n",
    "print(f\"After filtering: {comments.shape[0]}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "submissions_count += (submissions.shape[0],)\n",
    "comments_count += (comments.shape[0],)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Submissions:\n",
      "Before: 2519853\tAfter: 2044810\n",
      "\n",
      "Comments:\n",
      "Before: 7242871\tAfter: 3210167\n"
     ]
    }
   ],
   "source": [
    "print(\"Submissions:\")\n",
    "print(f\"Before: {submissions_count[0]}\\tAfter: {submissions_count[1]}\")\n",
    "print()\n",
    "print(\"Comments:\")\n",
    "print(f\"Before: {comments_count[0]}\\tAfter: {comments_count[1]}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Now to export cleaned data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open(\"data/comments_cleaned\", 'wb') as file:\n",
    "    pickle.dump(comments, file)\n",
    "    \n",
    "with open(\"data/submissions_cleaned\", 'wb') as file:\n",
    "    pickle.dump(submissions, file)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Check if the files are correct"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open(\"data/comments_cleaned\", 'rb') as file:\n",
    "    comments = pickle.load(file)\n",
    "    \n",
    "with open(\"data/submissions_cleaned\", 'rb') as file:\n",
    "    submissions = pickle.load(file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>submissions_id</th>\n",
       "      <th>url</th>\n",
       "      <th>permalink</th>\n",
       "      <th>author</th>\n",
       "      <th>created_utc</th>\n",
       "      <th>subreddit</th>\n",
       "      <th>subreddit_id</th>\n",
       "      <th>num_comments</th>\n",
       "      <th>score</th>\n",
       "      <th>over_18</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>648oo</td>\n",
       "      <td>http://www.ignorancedenied.com/viewthread.php?...</td>\n",
       "      <td>/r/reddit.com/comments/648oo/brain_disease_is_...</td>\n",
       "      <td>DITUS</td>\n",
       "      <td>1199145615</td>\n",
       "      <td>reddit.com</td>\n",
       "      <td>t5_6</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>648op</td>\n",
       "      <td>http://www.flascience.org/wp/?p=363</td>\n",
       "      <td>/r/science/comments/648op/three_more_florida_c...</td>\n",
       "      <td>rmuser</td>\n",
       "      <td>1199145634</td>\n",
       "      <td>science</td>\n",
       "      <td>t5_mouw</td>\n",
       "      <td>5</td>\n",
       "      <td>20</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>648or</td>\n",
       "      <td>http://hosted.ap.org/dynamic/stories/O/ODD_SHO...</td>\n",
       "      <td>/r/reddit.com/comments/648or/nude_couple_grapp...</td>\n",
       "      <td>zorno</td>\n",
       "      <td>1199145709</td>\n",
       "      <td>reddit.com</td>\n",
       "      <td>t5_6</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>648os</td>\n",
       "      <td>http://www.sltrib.com/opinion/ci_7846101?sourc...</td>\n",
       "      <td>/r/politics/comments/648os/apparently_bushs_pr...</td>\n",
       "      <td>rmuser</td>\n",
       "      <td>1199145735</td>\n",
       "      <td>politics</td>\n",
       "      <td>t5_2cneq</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>648ot</td>\n",
       "      <td>http://hosted.ap.org/dynamic/stories/O/ODD_RAR...</td>\n",
       "      <td>/r/reddit.com/comments/648ot/diners_find_rare_...</td>\n",
       "      <td>zorno</td>\n",
       "      <td>1199145735</td>\n",
       "      <td>reddit.com</td>\n",
       "      <td>t5_6</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2044805</th>\n",
       "      <td>7mq3n</td>\n",
       "      <td>http://ventaboutsports.blogspot.com/2008/12/so...</td>\n",
       "      <td>/r/funny/comments/7mq3n/some_extremely_corny_j...</td>\n",
       "      <td>themightymidget</td>\n",
       "      <td>1230767909</td>\n",
       "      <td>funny</td>\n",
       "      <td>t5_2qh33</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2044806</th>\n",
       "      <td>7mq3o</td>\n",
       "      <td>http://www.pbs.org/mormons/etc/genealogy.html</td>\n",
       "      <td>/r/news/comments/7mq3o/pbs_looks_at_the_massiv...</td>\n",
       "      <td>Tom22</td>\n",
       "      <td>1230767926</td>\n",
       "      <td>news</td>\n",
       "      <td>t5_2qh3l</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2044807</th>\n",
       "      <td>7mq3q</td>\n",
       "      <td>http://www.narutogames.biz</td>\n",
       "      <td>/r/reddit.com/comments/7mq3q/naruto_games/</td>\n",
       "      <td>bixiebix</td>\n",
       "      <td>1230767937</td>\n",
       "      <td>reddit.com</td>\n",
       "      <td>t5_6</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2044808</th>\n",
       "      <td>7mq3r</td>\n",
       "      <td>http://www.youtube.com/watch?v=gdQH1CI4LHY&amp;amp...</td>\n",
       "      <td>/r/politics/comments/7mq3r/ron_paul_on_recent_...</td>\n",
       "      <td>middkidd</td>\n",
       "      <td>1230767963</td>\n",
       "      <td>politics</td>\n",
       "      <td>t5_2cneq</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2044809</th>\n",
       "      <td>7mq3v</td>\n",
       "      <td>http://player.video.news.com.au/dailytelegraph...</td>\n",
       "      <td>/r/reddit.com/comments/7mq3v/video_nye_firewor...</td>\n",
       "      <td>klippa</td>\n",
       "      <td>1230767988</td>\n",
       "      <td>reddit.com</td>\n",
       "      <td>t5_6</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2044810 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        submissions_id                                                url  \\\n",
       "0                648oo  http://www.ignorancedenied.com/viewthread.php?...   \n",
       "1                648op                http://www.flascience.org/wp/?p=363   \n",
       "2                648or  http://hosted.ap.org/dynamic/stories/O/ODD_SHO...   \n",
       "3                648os  http://www.sltrib.com/opinion/ci_7846101?sourc...   \n",
       "4                648ot  http://hosted.ap.org/dynamic/stories/O/ODD_RAR...   \n",
       "...                ...                                                ...   \n",
       "2044805          7mq3n  http://ventaboutsports.blogspot.com/2008/12/so...   \n",
       "2044806          7mq3o      http://www.pbs.org/mormons/etc/genealogy.html   \n",
       "2044807          7mq3q                         http://www.narutogames.biz   \n",
       "2044808          7mq3r  http://www.youtube.com/watch?v=gdQH1CI4LHY&amp...   \n",
       "2044809          7mq3v  http://player.video.news.com.au/dailytelegraph...   \n",
       "\n",
       "                                                 permalink           author  \\\n",
       "0        /r/reddit.com/comments/648oo/brain_disease_is_...            DITUS   \n",
       "1        /r/science/comments/648op/three_more_florida_c...           rmuser   \n",
       "2        /r/reddit.com/comments/648or/nude_couple_grapp...            zorno   \n",
       "3        /r/politics/comments/648os/apparently_bushs_pr...           rmuser   \n",
       "4        /r/reddit.com/comments/648ot/diners_find_rare_...            zorno   \n",
       "...                                                    ...              ...   \n",
       "2044805  /r/funny/comments/7mq3n/some_extremely_corny_j...  themightymidget   \n",
       "2044806  /r/news/comments/7mq3o/pbs_looks_at_the_massiv...            Tom22   \n",
       "2044807         /r/reddit.com/comments/7mq3q/naruto_games/         bixiebix   \n",
       "2044808  /r/politics/comments/7mq3r/ron_paul_on_recent_...         middkidd   \n",
       "2044809  /r/reddit.com/comments/7mq3v/video_nye_firewor...           klippa   \n",
       "\n",
       "         created_utc   subreddit subreddit_id  num_comments  score  over_18  \n",
       "0         1199145615  reddit.com         t5_6             1      0    False  \n",
       "1         1199145634     science      t5_mouw             5     20    False  \n",
       "2         1199145709  reddit.com         t5_6             1      3    False  \n",
       "3         1199145735    politics     t5_2cneq             2      0    False  \n",
       "4         1199145735  reddit.com         t5_6             0      0    False  \n",
       "...              ...         ...          ...           ...    ...      ...  \n",
       "2044805   1230767909       funny     t5_2qh33             0      1    False  \n",
       "2044806   1230767926        news     t5_2qh3l             0      0    False  \n",
       "2044807   1230767937  reddit.com         t5_6             7      1    False  \n",
       "2044808   1230767963    politics     t5_2cneq             3      1    False  \n",
       "2044809   1230767988  reddit.com         t5_6             1      1    False  \n",
       "\n",
       "[2044810 rows x 10 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "submissions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "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>comments_id</th>\n",
       "      <th>author</th>\n",
       "      <th>link_id</th>\n",
       "      <th>parent_id</th>\n",
       "      <th>created_utc</th>\n",
       "      <th>subreddit</th>\n",
       "      <th>subreddit_id</th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>c02s9t4</td>\n",
       "      <td>LiberalDemocrat</td>\n",
       "      <td>648os</td>\n",
       "      <td>648os</td>\n",
       "      <td>1199145817</td>\n",
       "      <td>politics</td>\n",
       "      <td>t5_2cneq</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>c02s9u6</td>\n",
       "      <td>DiscoWolf</td>\n",
       "      <td>648os</td>\n",
       "      <td>648os</td>\n",
       "      <td>1199146090</td>\n",
       "      <td>politics</td>\n",
       "      <td>t5_2cneq</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>c02s9uh</td>\n",
       "      <td>Flemlord</td>\n",
       "      <td>648p5</td>\n",
       "      <td>648p5</td>\n",
       "      <td>1199146155</td>\n",
       "      <td>politics</td>\n",
       "      <td>t5_2cneq</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>c02s9un</td>\n",
       "      <td>LiberalDemocrat</td>\n",
       "      <td>648oy</td>\n",
       "      <td>648oy</td>\n",
       "      <td>1199146229</td>\n",
       "      <td>reddit.com</td>\n",
       "      <td>t5_6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>c02s9us</td>\n",
       "      <td>LiberalDemocrat</td>\n",
       "      <td>648p5</td>\n",
       "      <td>648p5</td>\n",
       "      <td>1199146266</td>\n",
       "      <td>politics</td>\n",
       "      <td>t5_2cneq</td>\n",
       "      <td>-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4873683</th>\n",
       "      <td>c06vwuc</td>\n",
       "      <td>fergie</td>\n",
       "      <td>7k6cl</td>\n",
       "      <td>7k6cl</td>\n",
       "      <td>1229579665</td>\n",
       "      <td>politics</td>\n",
       "      <td>t5_2cneq</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4873685</th>\n",
       "      <td>c06vwue</td>\n",
       "      <td>wolfzero</td>\n",
       "      <td>7k4if</td>\n",
       "      <td>c06vs7l</td>\n",
       "      <td>1229579675</td>\n",
       "      <td>technology</td>\n",
       "      <td>t5_2qh16</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4873686</th>\n",
       "      <td>c06vwug</td>\n",
       "      <td>Morgin_Black</td>\n",
       "      <td>7k3w5</td>\n",
       "      <td>7k3w5</td>\n",
       "      <td>1229579679</td>\n",
       "      <td>comics</td>\n",
       "      <td>t5_2qh0s</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4873687</th>\n",
       "      <td>c06vwui</td>\n",
       "      <td>onezerozeroone</td>\n",
       "      <td>7k2bc</td>\n",
       "      <td>c06vrvz</td>\n",
       "      <td>1229579685</td>\n",
       "      <td>atheism</td>\n",
       "      <td>t5_2qh2p</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4873688</th>\n",
       "      <td>c06vwuj</td>\n",
       "      <td>vypermann</td>\n",
       "      <td>7k65l</td>\n",
       "      <td>c06vvzu</td>\n",
       "      <td>1229579686</td>\n",
       "      <td>WTF</td>\n",
       "      <td>t5_2qh61</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3210167 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        comments_id           author link_id parent_id  created_utc  \\\n",
       "25          c02s9t4  LiberalDemocrat   648os     648os   1199145817   \n",
       "55          c02s9u6        DiscoWolf   648os     648os   1199146090   \n",
       "63          c02s9uh         Flemlord   648p5     648p5   1199146155   \n",
       "69          c02s9un  LiberalDemocrat   648oy     648oy   1199146229   \n",
       "72          c02s9us  LiberalDemocrat   648p5     648p5   1199146266   \n",
       "...             ...              ...     ...       ...          ...   \n",
       "4873683     c06vwuc           fergie   7k6cl     7k6cl   1229579665   \n",
       "4873685     c06vwue         wolfzero   7k4if   c06vs7l   1229579675   \n",
       "4873686     c06vwug     Morgin_Black   7k3w5     7k3w5   1229579679   \n",
       "4873687     c06vwui   onezerozeroone   7k2bc   c06vrvz   1229579685   \n",
       "4873688     c06vwuj        vypermann   7k65l   c06vvzu   1229579686   \n",
       "\n",
       "          subreddit subreddit_id  score  \n",
       "25         politics     t5_2cneq      1  \n",
       "55         politics     t5_2cneq      1  \n",
       "63         politics     t5_2cneq      7  \n",
       "69       reddit.com         t5_6      0  \n",
       "72         politics     t5_2cneq    -10  \n",
       "...             ...          ...    ...  \n",
       "4873683    politics     t5_2cneq      2  \n",
       "4873685  technology     t5_2qh16      4  \n",
       "4873686      comics     t5_2qh0s      0  \n",
       "4873687     atheism     t5_2qh2p      1  \n",
       "4873688         WTF     t5_2qh61      0  \n",
       "\n",
       "[3210167 rows x 8 columns]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "comments"
   ]
  }
 ],
 "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.8.5"
  },
  "vscode": {
   "interpreter": {
    "hash": "ad2bdc8ecc057115af97d19610ffacc2b4e99fae6737bb82f5d7fb13d2f2c186"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}