EDMW_dataprep.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>Thread Title</th>\n",
       "      <th>Comment Author</th>\n",
       "      <th>Comment</th>\n",
       "      <th>Date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>\\r\\n\\t i commit sin... pls 4give me\\r\\n\\r\\n</td>\n",
       "      <td>BryanFangKarma</td>\n",
       "      <td>jin sian... saw ant in kitchen while lim kopi ...</td>\n",
       "      <td>27-04-2019, 12:55 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>\\r\\n\\t i commit sin... pls 4give me\\r\\n\\r\\n</td>\n",
       "      <td>amazingone</td>\n",
       "      <td>In before thread promoted\\n\\nPosted from PCWX ...</td>\n",
       "      <td>27-04-2019, 12:56 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>\\r\\n\\t i commit sin... pls 4give me\\r\\n\\r\\n</td>\n",
       "      <td>markthesharktank</td>\n",
       "      <td>Forgiven</td>\n",
       "      <td>27-04-2019, 12:57 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>\\r\\n\\t i commit sin... pls 4give me\\r\\n\\r\\n</td>\n",
       "      <td>chech01</td>\n",
       "      <td>clone infestation</td>\n",
       "      <td>27-04-2019, 12:58 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>\\r\\n\\t i commit sin... pls 4give me\\r\\n\\r\\n</td>\n",
       "      <td>Taro Pie</td>\n",
       "      <td>In the name of Supreme Leader Lee, you are for...</td>\n",
       "      <td>27-04-2019, 01:12 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>\\r\\n\\t i commit sin... pls 4give me\\r\\n\\r\\n</td>\n",
       "      <td>Guy84</td>\n",
       "      <td>In before thread promote\\n\\n</td>\n",
       "      <td>27-04-2019, 01:14 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>\\r\\n\\t i commit sin... pls 4give me\\r\\n\\r\\n</td>\n",
       "      <td>Anything_Whatever</td>\n",
       "      <td>Its true.... Thank you TS for providing the s...</td>\n",
       "      <td>27-04-2019, 01:21 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>\\r\\n\\t i commit sin... pls 4give me\\r\\n\\r\\n</td>\n",
       "      <td>whatheheck</td>\n",
       "      <td>In b4 promote thread later</td>\n",
       "      <td>27-04-2019, 01:21 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>\\r\\n\\t i commit sin... pls 4give me\\r\\n\\r\\n</td>\n",
       "      <td>e905433k</td>\n",
       "      <td>the ants won't remember cos they don't have br...</td>\n",
       "      <td>27-04-2019, 01:23 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>\\r\\n\\t i commit sin... pls 4give me\\r\\n\\r\\n</td>\n",
       "      <td>PaulLevesque</td>\n",
       "      <td>Night time they will diao zui crawl into your ...</td>\n",
       "      <td>27-04-2019, 01:24 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>\\r\\n\\t i commit sin... pls 4give me\\r\\n\\r\\n</td>\n",
       "      <td>hydeyoshi</td>\n",
       "      <td>Siaorang spotted\\n\\nSent from Xiaomi MI A1 usi...</td>\n",
       "      <td>27-04-2019, 01:25 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>\\r\\n\\t i commit sin... pls 4give me\\r\\n\\r\\n</td>\n",
       "      <td>Rukure</td>\n",
       "      <td>Yes.  Now for your penance say 10 Hail Marys.</td>\n",
       "      <td>27-04-2019, 02:28 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>\\r\\n\\t i commit sin... pls 4give me\\r\\n\\r\\n</td>\n",
       "      <td>Limexx</td>\n",
       "      <td>is true im the ants</td>\n",
       "      <td>27-04-2019, 02:30 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>\\r\\n\\t i commit sin... pls 4give me\\r\\n\\r\\n</td>\n",
       "      <td>BryanFangKarma</td>\n",
       "      <td>jialat.... i pt sugar bck bt the ant no come b...</td>\n",
       "      <td>27-04-2019, 04:03 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>\\r\\n\\t i commit sin... pls 4give me\\r\\n\\r\\n</td>\n",
       "      <td>abc654321</td>\n",
       "      <td>In promoting thread.</td>\n",
       "      <td>27-04-2019, 06:01 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>\\r\\n\\t i commit sin... pls 4give me\\r\\n\\r\\n</td>\n",
       "      <td>alex22</td>\n",
       "      <td>In promoted thread</td>\n",
       "      <td>27-04-2019, 06:17 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>\\r\\n\\t i commit sin... pls 4give me\\r\\n\\r\\n</td>\n",
       "      <td>AlmightyOnes</td>\n",
       "      <td>Must be a rich kid, got $ don't know where to ...</td>\n",
       "      <td>27-04-2019, 06:19 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>\\r\\n\\t i commit sin... pls 4give me\\r\\n\\r\\n</td>\n",
       "      <td>sleepingcat</td>\n",
       "      <td>siao lang spotted.</td>\n",
       "      <td>27-04-2019, 06:22 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>\\r\\n\\t i commit sin... pls 4give me\\r\\n\\r\\n</td>\n",
       "      <td>PixleRavenix</td>\n",
       "      <td>Dis one jin funny must giv u dat. But scientif...</td>\n",
       "      <td>27-04-2019, 06:38 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>\\r\\n\\t i commit sin... pls 4give me\\r\\n\\r\\n</td>\n",
       "      <td>EndGoal</td>\n",
       "      <td>money money money money</td>\n",
       "      <td>27-04-2019, 06:45 PM</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   Thread Title     Comment Author  \\\n",
       "0   \\r\\n\\t i commit sin... pls 4give me\\r\\n\\r\\n     BryanFangKarma   \n",
       "1   \\r\\n\\t i commit sin... pls 4give me\\r\\n\\r\\n         amazingone   \n",
       "2   \\r\\n\\t i commit sin... pls 4give me\\r\\n\\r\\n   markthesharktank   \n",
       "3   \\r\\n\\t i commit sin... pls 4give me\\r\\n\\r\\n            chech01   \n",
       "4   \\r\\n\\t i commit sin... pls 4give me\\r\\n\\r\\n           Taro Pie   \n",
       "5   \\r\\n\\t i commit sin... pls 4give me\\r\\n\\r\\n              Guy84   \n",
       "6   \\r\\n\\t i commit sin... pls 4give me\\r\\n\\r\\n  Anything_Whatever   \n",
       "7   \\r\\n\\t i commit sin... pls 4give me\\r\\n\\r\\n         whatheheck   \n",
       "8   \\r\\n\\t i commit sin... pls 4give me\\r\\n\\r\\n           e905433k   \n",
       "9   \\r\\n\\t i commit sin... pls 4give me\\r\\n\\r\\n       PaulLevesque   \n",
       "10  \\r\\n\\t i commit sin... pls 4give me\\r\\n\\r\\n          hydeyoshi   \n",
       "11  \\r\\n\\t i commit sin... pls 4give me\\r\\n\\r\\n             Rukure   \n",
       "12  \\r\\n\\t i commit sin... pls 4give me\\r\\n\\r\\n             Limexx   \n",
       "13  \\r\\n\\t i commit sin... pls 4give me\\r\\n\\r\\n     BryanFangKarma   \n",
       "14  \\r\\n\\t i commit sin... pls 4give me\\r\\n\\r\\n          abc654321   \n",
       "15  \\r\\n\\t i commit sin... pls 4give me\\r\\n\\r\\n             alex22   \n",
       "16  \\r\\n\\t i commit sin... pls 4give me\\r\\n\\r\\n       AlmightyOnes   \n",
       "17  \\r\\n\\t i commit sin... pls 4give me\\r\\n\\r\\n        sleepingcat   \n",
       "18  \\r\\n\\t i commit sin... pls 4give me\\r\\n\\r\\n       PixleRavenix   \n",
       "19  \\r\\n\\t i commit sin... pls 4give me\\r\\n\\r\\n            EndGoal   \n",
       "\n",
       "                                              Comment                  Date  \n",
       "0   jin sian... saw ant in kitchen while lim kopi ...  27-04-2019, 12:55 PM  \n",
       "1   In before thread promoted\\n\\nPosted from PCWX ...  27-04-2019, 12:56 PM  \n",
       "2                                           Forgiven   27-04-2019, 12:57 PM  \n",
       "3                                   clone infestation  27-04-2019, 12:58 PM  \n",
       "4   In the name of Supreme Leader Lee, you are for...  27-04-2019, 01:12 PM  \n",
       "5                        In before thread promote\\n\\n  27-04-2019, 01:14 PM  \n",
       "6   Its true.... Thank you TS for providing the s...  27-04-2019, 01:21 PM  \n",
       "7                          In b4 promote thread later  27-04-2019, 01:21 PM  \n",
       "8   the ants won't remember cos they don't have br...  27-04-2019, 01:23 PM  \n",
       "9   Night time they will diao zui crawl into your ...  27-04-2019, 01:24 PM  \n",
       "10  Siaorang spotted\\n\\nSent from Xiaomi MI A1 usi...  27-04-2019, 01:25 PM  \n",
       "11      Yes.  Now for your penance say 10 Hail Marys.  27-04-2019, 02:28 PM  \n",
       "12                                is true im the ants  27-04-2019, 02:30 PM  \n",
       "13  jialat.... i pt sugar bck bt the ant no come b...  27-04-2019, 04:03 PM  \n",
       "14                               In promoting thread.  27-04-2019, 06:01 PM  \n",
       "15                                 In promoted thread  27-04-2019, 06:17 PM  \n",
       "16  Must be a rich kid, got $ don't know where to ...  27-04-2019, 06:19 PM  \n",
       "17                                 siao lang spotted.  27-04-2019, 06:22 PM  \n",
       "18  Dis one jin funny must giv u dat. But scientif...  27-04-2019, 06:38 PM  \n",
       "19                            money money money money  27-04-2019, 06:45 PM  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('~/Desktop/Project_Forum/edmw_data_raw.csv')\n",
    "df.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2277437"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Comment'].size"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Remove duplicate rows\n",
    "df.drop_duplicates(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Remove punctuation using Regular Expression\n",
    "#df['Comment'] = df['Comment'].str.replace(r'[^\\w\\s]', '') \n",
    "#df.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>Thread Title</th>\n",
       "      <th>Comment Author</th>\n",
       "      <th>Comment</th>\n",
       "      <th>Date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>i commit sin... pls 4give me</td>\n",
       "      <td>BryanFangKarma</td>\n",
       "      <td>jin sian... saw ant in kitchen while lim kopi ...</td>\n",
       "      <td>27-04-2019, 12:55 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>i commit sin... pls 4give me</td>\n",
       "      <td>amazingone</td>\n",
       "      <td>In before thread promoted  Posted from PCWX us...</td>\n",
       "      <td>27-04-2019, 12:56 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>i commit sin... pls 4give me</td>\n",
       "      <td>markthesharktank</td>\n",
       "      <td>Forgiven</td>\n",
       "      <td>27-04-2019, 12:57 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>i commit sin... pls 4give me</td>\n",
       "      <td>chech01</td>\n",
       "      <td>clone infestation</td>\n",
       "      <td>27-04-2019, 12:58 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>i commit sin... pls 4give me</td>\n",
       "      <td>Taro Pie</td>\n",
       "      <td>In the name of Supreme Leader Lee, you are for...</td>\n",
       "      <td>27-04-2019, 01:12 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>i commit sin... pls 4give me</td>\n",
       "      <td>Guy84</td>\n",
       "      <td>In before thread promote</td>\n",
       "      <td>27-04-2019, 01:14 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>i commit sin... pls 4give me</td>\n",
       "      <td>Anything_Whatever</td>\n",
       "      <td>Its true.... Thank you TS for providing the s...</td>\n",
       "      <td>27-04-2019, 01:21 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>i commit sin... pls 4give me</td>\n",
       "      <td>whatheheck</td>\n",
       "      <td>In b4 promote thread later</td>\n",
       "      <td>27-04-2019, 01:21 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>i commit sin... pls 4give me</td>\n",
       "      <td>e905433k</td>\n",
       "      <td>the ants won't remember cos they don't have br...</td>\n",
       "      <td>27-04-2019, 01:23 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>i commit sin... pls 4give me</td>\n",
       "      <td>PaulLevesque</td>\n",
       "      <td>Night time they will diao zui crawl into your ...</td>\n",
       "      <td>27-04-2019, 01:24 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>i commit sin... pls 4give me</td>\n",
       "      <td>hydeyoshi</td>\n",
       "      <td>Siaorang spotted  Sent from Xiaomi MI A1 using...</td>\n",
       "      <td>27-04-2019, 01:25 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>i commit sin... pls 4give me</td>\n",
       "      <td>Rukure</td>\n",
       "      <td>Yes.  Now for your penance say 10 Hail Marys.</td>\n",
       "      <td>27-04-2019, 02:28 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>i commit sin... pls 4give me</td>\n",
       "      <td>Limexx</td>\n",
       "      <td>is true im the ants</td>\n",
       "      <td>27-04-2019, 02:30 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>i commit sin... pls 4give me</td>\n",
       "      <td>BryanFangKarma</td>\n",
       "      <td>jialat.... i pt sugar bck bt the ant no come b...</td>\n",
       "      <td>27-04-2019, 04:03 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>i commit sin... pls 4give me</td>\n",
       "      <td>abc654321</td>\n",
       "      <td>In promoting thread.</td>\n",
       "      <td>27-04-2019, 06:01 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>i commit sin... pls 4give me</td>\n",
       "      <td>alex22</td>\n",
       "      <td>In promoted thread</td>\n",
       "      <td>27-04-2019, 06:17 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>i commit sin... pls 4give me</td>\n",
       "      <td>AlmightyOnes</td>\n",
       "      <td>Must be a rich kid, got $ don't know where to ...</td>\n",
       "      <td>27-04-2019, 06:19 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>i commit sin... pls 4give me</td>\n",
       "      <td>sleepingcat</td>\n",
       "      <td>siao lang spotted.</td>\n",
       "      <td>27-04-2019, 06:22 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>i commit sin... pls 4give me</td>\n",
       "      <td>PixleRavenix</td>\n",
       "      <td>Dis one jin funny must giv u dat. But scientif...</td>\n",
       "      <td>27-04-2019, 06:38 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>i commit sin... pls 4give me</td>\n",
       "      <td>EndGoal</td>\n",
       "      <td>money money money money</td>\n",
       "      <td>27-04-2019, 06:45 PM</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     Thread Title     Comment Author  \\\n",
       "0    i commit sin... pls 4give me     BryanFangKarma   \n",
       "1    i commit sin... pls 4give me         amazingone   \n",
       "2    i commit sin... pls 4give me   markthesharktank   \n",
       "3    i commit sin... pls 4give me            chech01   \n",
       "4    i commit sin... pls 4give me           Taro Pie   \n",
       "5    i commit sin... pls 4give me              Guy84   \n",
       "6    i commit sin... pls 4give me  Anything_Whatever   \n",
       "7    i commit sin... pls 4give me         whatheheck   \n",
       "8    i commit sin... pls 4give me           e905433k   \n",
       "9    i commit sin... pls 4give me       PaulLevesque   \n",
       "10   i commit sin... pls 4give me          hydeyoshi   \n",
       "11   i commit sin... pls 4give me             Rukure   \n",
       "12   i commit sin... pls 4give me             Limexx   \n",
       "13   i commit sin... pls 4give me     BryanFangKarma   \n",
       "14   i commit sin... pls 4give me          abc654321   \n",
       "15   i commit sin... pls 4give me             alex22   \n",
       "16   i commit sin... pls 4give me       AlmightyOnes   \n",
       "17   i commit sin... pls 4give me        sleepingcat   \n",
       "18   i commit sin... pls 4give me       PixleRavenix   \n",
       "19   i commit sin... pls 4give me            EndGoal   \n",
       "\n",
       "                                              Comment                  Date  \n",
       "0   jin sian... saw ant in kitchen while lim kopi ...  27-04-2019, 12:55 PM  \n",
       "1   In before thread promoted  Posted from PCWX us...  27-04-2019, 12:56 PM  \n",
       "2                                           Forgiven   27-04-2019, 12:57 PM  \n",
       "3                                   clone infestation  27-04-2019, 12:58 PM  \n",
       "4   In the name of Supreme Leader Lee, you are for...  27-04-2019, 01:12 PM  \n",
       "5                          In before thread promote    27-04-2019, 01:14 PM  \n",
       "6   Its true.... Thank you TS for providing the s...  27-04-2019, 01:21 PM  \n",
       "7                          In b4 promote thread later  27-04-2019, 01:21 PM  \n",
       "8   the ants won't remember cos they don't have br...  27-04-2019, 01:23 PM  \n",
       "9   Night time they will diao zui crawl into your ...  27-04-2019, 01:24 PM  \n",
       "10  Siaorang spotted  Sent from Xiaomi MI A1 using...  27-04-2019, 01:25 PM  \n",
       "11      Yes.  Now for your penance say 10 Hail Marys.  27-04-2019, 02:28 PM  \n",
       "12                                is true im the ants  27-04-2019, 02:30 PM  \n",
       "13  jialat.... i pt sugar bck bt the ant no come b...  27-04-2019, 04:03 PM  \n",
       "14                               In promoting thread.  27-04-2019, 06:01 PM  \n",
       "15                                 In promoted thread  27-04-2019, 06:17 PM  \n",
       "16  Must be a rich kid, got $ don't know where to ...  27-04-2019, 06:19 PM  \n",
       "17                                 siao lang spotted.  27-04-2019, 06:22 PM  \n",
       "18  Dis one jin funny must giv u dat. But scientif...  27-04-2019, 06:38 PM  \n",
       "19                            money money money money  27-04-2019, 06:45 PM  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Remove white spaces\n",
    "df['Thread Title'] = df['Thread Title'].str.replace(r'\\r|\\n|\\t','')\n",
    "df['Comment'] = df['Comment'].str.replace(r'\\r|\\n|\\t',' ')\n",
    "df.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Remove GAGT/PCWX app automatic signatures\n",
    "df['Comment'].replace(r'Sent from .* using GAGT', '', inplace = True, regex=True) \n",
    "df['Comment'].replace(r'Posted from PCWX using .*', '', inplace = True, regex=True) \n",
    "# ^^ will not remove the entire phone model but as much as possible try \n",
    "df['Comment'].replace(r'Posted with .* via PCWX', '', inplace = True, regex=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Remove admin bot comments\n",
    "df.drop(df[df['Comment Author']=='hwzadmin'].index, inplace=True) \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Thread Title</th>\n",
       "      <th>Comment Author</th>\n",
       "      <th>Comment</th>\n",
       "      <th>Date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>i commit sin... pls 4give me</td>\n",
       "      <td>BryanFangKarma</td>\n",
       "      <td>jin sian... saw ant in kitchen while lim kopi ...</td>\n",
       "      <td>27-04-2019, 12:55 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>i commit sin... pls 4give me</td>\n",
       "      <td>amazingone</td>\n",
       "      <td>in before thread promoted</td>\n",
       "      <td>27-04-2019, 12:56 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>i commit sin... pls 4give me</td>\n",
       "      <td>markthesharktank</td>\n",
       "      <td>forgiven</td>\n",
       "      <td>27-04-2019, 12:57 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>i commit sin... pls 4give me</td>\n",
       "      <td>chech01</td>\n",
       "      <td>clone infestation</td>\n",
       "      <td>27-04-2019, 12:58 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>i commit sin... pls 4give me</td>\n",
       "      <td>Taro Pie</td>\n",
       "      <td>in the name of supreme leader lee, you are for...</td>\n",
       "      <td>27-04-2019, 01:12 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>i commit sin... pls 4give me</td>\n",
       "      <td>Guy84</td>\n",
       "      <td>in before thread promote</td>\n",
       "      <td>27-04-2019, 01:14 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>i commit sin... pls 4give me</td>\n",
       "      <td>Anything_Whatever</td>\n",
       "      <td>its true.... thank you ts for providing the s...</td>\n",
       "      <td>27-04-2019, 01:21 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>i commit sin... pls 4give me</td>\n",
       "      <td>whatheheck</td>\n",
       "      <td>in b4 promote thread later</td>\n",
       "      <td>27-04-2019, 01:21 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>i commit sin... pls 4give me</td>\n",
       "      <td>e905433k</td>\n",
       "      <td>the ants won't remember cos they don't have br...</td>\n",
       "      <td>27-04-2019, 01:23 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>i commit sin... pls 4give me</td>\n",
       "      <td>PaulLevesque</td>\n",
       "      <td>night time they will diao zui crawl into your ...</td>\n",
       "      <td>27-04-2019, 01:24 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>i commit sin... pls 4give me</td>\n",
       "      <td>hydeyoshi</td>\n",
       "      <td>siaorang spotted</td>\n",
       "      <td>27-04-2019, 01:25 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>i commit sin... pls 4give me</td>\n",
       "      <td>Rukure</td>\n",
       "      <td>yes. now for your penance say 10 hail marys.</td>\n",
       "      <td>27-04-2019, 02:28 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>i commit sin... pls 4give me</td>\n",
       "      <td>Limexx</td>\n",
       "      <td>is true im the ants</td>\n",
       "      <td>27-04-2019, 02:30 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>i commit sin... pls 4give me</td>\n",
       "      <td>BryanFangKarma</td>\n",
       "      <td>jialat.... i pt sugar bck bt the ant no come b...</td>\n",
       "      <td>27-04-2019, 04:03 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>i commit sin... pls 4give me</td>\n",
       "      <td>abc654321</td>\n",
       "      <td>in promoting thread.</td>\n",
       "      <td>27-04-2019, 06:01 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>i commit sin... pls 4give me</td>\n",
       "      <td>alex22</td>\n",
       "      <td>in promoted thread</td>\n",
       "      <td>27-04-2019, 06:17 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>i commit sin... pls 4give me</td>\n",
       "      <td>AlmightyOnes</td>\n",
       "      <td>must be a rich kid, got $ don't know where to ...</td>\n",
       "      <td>27-04-2019, 06:19 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>i commit sin... pls 4give me</td>\n",
       "      <td>sleepingcat</td>\n",
       "      <td>siao lang spotted.</td>\n",
       "      <td>27-04-2019, 06:22 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>i commit sin... pls 4give me</td>\n",
       "      <td>PixleRavenix</td>\n",
       "      <td>dis one jin funny must giv u dat. but scientif...</td>\n",
       "      <td>27-04-2019, 06:38 PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>i commit sin... pls 4give me</td>\n",
       "      <td>EndGoal</td>\n",
       "      <td>money money money money</td>\n",
       "      <td>27-04-2019, 06:45 PM</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     Thread Title     Comment Author  \\\n",
       "0    i commit sin... pls 4give me     BryanFangKarma   \n",
       "1    i commit sin... pls 4give me         amazingone   \n",
       "2    i commit sin... pls 4give me   markthesharktank   \n",
       "3    i commit sin... pls 4give me            chech01   \n",
       "4    i commit sin... pls 4give me           Taro Pie   \n",
       "5    i commit sin... pls 4give me              Guy84   \n",
       "6    i commit sin... pls 4give me  Anything_Whatever   \n",
       "7    i commit sin... pls 4give me         whatheheck   \n",
       "8    i commit sin... pls 4give me           e905433k   \n",
       "9    i commit sin... pls 4give me       PaulLevesque   \n",
       "10   i commit sin... pls 4give me          hydeyoshi   \n",
       "11   i commit sin... pls 4give me             Rukure   \n",
       "12   i commit sin... pls 4give me             Limexx   \n",
       "13   i commit sin... pls 4give me     BryanFangKarma   \n",
       "14   i commit sin... pls 4give me          abc654321   \n",
       "15   i commit sin... pls 4give me             alex22   \n",
       "16   i commit sin... pls 4give me       AlmightyOnes   \n",
       "17   i commit sin... pls 4give me        sleepingcat   \n",
       "18   i commit sin... pls 4give me       PixleRavenix   \n",
       "19   i commit sin... pls 4give me            EndGoal   \n",
       "\n",
       "                                              Comment                  Date  \n",
       "0   jin sian... saw ant in kitchen while lim kopi ...  27-04-2019, 12:55 PM  \n",
       "1                           in before thread promoted  27-04-2019, 12:56 PM  \n",
       "2                                            forgiven  27-04-2019, 12:57 PM  \n",
       "3                                   clone infestation  27-04-2019, 12:58 PM  \n",
       "4   in the name of supreme leader lee, you are for...  27-04-2019, 01:12 PM  \n",
       "5                            in before thread promote  27-04-2019, 01:14 PM  \n",
       "6   its true.... thank you ts for providing the s...  27-04-2019, 01:21 PM  \n",
       "7                          in b4 promote thread later  27-04-2019, 01:21 PM  \n",
       "8   the ants won't remember cos they don't have br...  27-04-2019, 01:23 PM  \n",
       "9   night time they will diao zui crawl into your ...  27-04-2019, 01:24 PM  \n",
       "10                                   siaorang spotted  27-04-2019, 01:25 PM  \n",
       "11       yes. now for your penance say 10 hail marys.  27-04-2019, 02:28 PM  \n",
       "12                                is true im the ants  27-04-2019, 02:30 PM  \n",
       "13  jialat.... i pt sugar bck bt the ant no come b...  27-04-2019, 04:03 PM  \n",
       "14                               in promoting thread.  27-04-2019, 06:01 PM  \n",
       "15                                 in promoted thread  27-04-2019, 06:17 PM  \n",
       "16  must be a rich kid, got $ don't know where to ...  27-04-2019, 06:19 PM  \n",
       "17                                 siao lang spotted.  27-04-2019, 06:22 PM  \n",
       "18  dis one jin funny must giv u dat. but scientif...  27-04-2019, 06:38 PM  \n",
       "19                            money money money money  27-04-2019, 06:45 PM  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Change all letters to lowercase\n",
    "df['Comment']  = df['Comment'].apply(lambda x: ' '.join([word.lower() for word in str(x).split()])) \n",
    "df.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Comment'].replace('', np.nan, inplace = True) #replace empty strings with NaN\n",
    "df.dropna(inplace = True) #Drop NaN rows (originally empty strings)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1717873"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Remaining no. of comments\n",
    "df['Comment'].size "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('~/Desktop/Project_Forum/edmw_data_clean.csv',index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}