merge_everything.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import io\n",
    "import pandas as pd\n",
    "import re\n",
    "import numpy as np\n",
    "\n",
    "with io.open(\"imdb/imdb_urls.txt\", \"r\", encoding=\"utf-8\") as my_file:\n",
    "    movie_file = my_file.read() \n",
    "\n",
    "links = []\n",
    "book_names = []\n",
    "\n",
    "for line in movie_file.split(\"\\n\"):\n",
    "    arr = line.split('\\t<>\\t')\n",
    "    re_obj = re.search(\"(.*)[?]\", 'http://'+arr[1])\n",
    "    link = re_obj.group(1)\n",
    "    links.append(link.strip())\n",
    "    re_obj = re.search(\"(.*?)(\\(|$)\", arr[0])\n",
    "    bname = re_obj.group(1)\n",
    "    book_names.append(bname.strip())\n",
    "    \n",
    "links = np.array(links)\n",
    "book_names = np.array(book_names)\n",
    "\n",
    "df = pd.DataFrame(data=links,columns=['Link'])\n",
    "df['New Book Name'] = pd.DataFrame(data=book_names,columns=['New Book Name'])\n",
    "\n",
    "dataframe = pd.read_csv('imdb/movie_data.csv', header=None, names=['Link','Budget','Gross','Opening'])\n",
    "\n",
    "dataframe['Link'] = dataframe['Link'].map(lambda x: re.search(\"(.*?)business\", x).group(1).strip())\n",
    "\n",
    "Merged = pd.merge(df, dataframe, how='right', on='Link', suffixes=('_x', '_y'))\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "Merged = Merged.drop_duplicates()\n",
    "Merged = Merged.set_index([range(Merged['Link'].values.size)])\n",
    "book_data = pd.read_csv('goodreads/goodreads_data_clean.csv', encoding='utf-8')\n",
    "Merged = pd.merge(Merged, book_data, how='left', on='New Book Name', suffixes=('_x', '_y'))\n",
    "Merged = Merged.drop('Unnamed: 0', axis=1)\n",
    "Merged = Merged.drop_duplicates()\n",
    "Merged = Merged.set_index([range(Merged['Link'].values.size)])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Link</th>\n",
       "      <th>New Book Name</th>\n",
       "      <th>Budget</th>\n",
       "      <th>Gross</th>\n",
       "      <th>Opening</th>\n",
       "      <th>Awards List</th>\n",
       "      <th>Book Name</th>\n",
       "      <th>Characters</th>\n",
       "      <th>Genres</th>\n",
       "      <th>Num_reviews</th>\n",
       "      <th>...</th>\n",
       "      <th>Genre_Nonfiction</th>\n",
       "      <th>Genre_War</th>\n",
       "      <th>Genre_European Literature</th>\n",
       "      <th>Genre_Biography</th>\n",
       "      <th>Genre_Womens Fiction</th>\n",
       "      <th>Genre_Suspense</th>\n",
       "      <th>Genre_Chick Lit</th>\n",
       "      <th>Genre_Adult</th>\n",
       "      <th>Genre_Autobiography</th>\n",
       "      <th>Genre_History</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>http://www.imdb.com/title/tt0373889/</td>\n",
       "      <td>Harry Potter and the Order of the Phoenix</td>\n",
       "      <td>150000000</td>\n",
       "      <td>292004738</td>\n",
       "      <td>77108414</td>\n",
       "      <td>\\r\\nBram Stoker Award for Works for Young Read...</td>\n",
       "      <td>Harry Potter and the Order of the Phoenix (Har...</td>\n",
       "      <td>[Sirius Black, Draco Malfoy, Ron Weasley, Petu...</td>\n",
       "      <td>[Childrens, Fantasy, Novels]</td>\n",
       "      <td>25751.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>http://www.imdb.com/title/tt1099212/</td>\n",
       "      <td>Twilight</td>\n",
       "      <td>37000000</td>\n",
       "      <td>191465414</td>\n",
       "      <td>69637740</td>\n",
       "      <td>\\r\\nGeorgia Peach Book Award (2007), Buxtehude...</td>\n",
       "      <td>Twilight (Twilight #1)</td>\n",
       "      <td>[Edward Cullen, Jacob Black, Laurent, Renee, B...</td>\n",
       "      <td>[Young Adult, Fantasy, Romance, Paranormal, Va...</td>\n",
       "      <td>93258.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>http://www.imdb.com/title/tt0330373/</td>\n",
       "      <td>Harry Potter and the Goblet of Fire</td>\n",
       "      <td>150000000</td>\n",
       "      <td>290013036</td>\n",
       "      <td>102335066</td>\n",
       "      <td>\\r\\nHugo Award for Best Novel (2001), Mythopoe...</td>\n",
       "      <td>Harry Potter and the Goblet of Fire (Harry Pot...</td>\n",
       "      <td>[Sirius Black, Draco Malfoy, Ron Weasley, Petu...</td>\n",
       "      <td>[Fantasy, Young Adult, Fiction, Fantasy, Magic...</td>\n",
       "      <td>27770.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>http://www.imdb.com/title/tt0304141/</td>\n",
       "      <td>Harry Potter and the Prisoner of Azkaban</td>\n",
       "      <td>130000000</td>\n",
       "      <td>249358727</td>\n",
       "      <td>93687367</td>\n",
       "      <td>\\r\\nBram Stoker Award for Best Work for Young ...</td>\n",
       "      <td>Harry Potter and the Prisoner of Azkaban (Harr...</td>\n",
       "      <td>[Sirius Black, Draco Malfoy, Ron Weasley, Petu...</td>\n",
       "      <td>[Fantasy, Young Adult, Fiction]</td>\n",
       "      <td>32160.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>http://www.imdb.com/title/tt0241527/</td>\n",
       "      <td>Harry Potter and the Sorcerer's Stone</td>\n",
       "      <td>125000000</td>\n",
       "      <td>317557891</td>\n",
       "      <td>90294621</td>\n",
       "      <td>\\r\\nMythopoeic Fantasy Award for Children's Li...</td>\n",
       "      <td>Harry Potter and the Sorcerer's Stone (Harry P...</td>\n",
       "      <td>[Draco Malfoy, Ron Weasley, Petunia Dursley, V...</td>\n",
       "      <td>[Fantasy, Young Adult, Fiction]</td>\n",
       "      <td>67668.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 54 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   Link  \\\n",
       "0  http://www.imdb.com/title/tt0373889/   \n",
       "1  http://www.imdb.com/title/tt1099212/   \n",
       "2  http://www.imdb.com/title/tt0330373/   \n",
       "3  http://www.imdb.com/title/tt0304141/   \n",
       "4  http://www.imdb.com/title/tt0241527/   \n",
       "\n",
       "                               New Book Name     Budget      Gross    Opening  \\\n",
       "0  Harry Potter and the Order of the Phoenix  150000000  292004738   77108414   \n",
       "1                                   Twilight   37000000  191465414   69637740   \n",
       "2        Harry Potter and the Goblet of Fire  150000000  290013036  102335066   \n",
       "3   Harry Potter and the Prisoner of Azkaban  130000000  249358727   93687367   \n",
       "4      Harry Potter and the Sorcerer's Stone  125000000  317557891   90294621   \n",
       "\n",
       "                                         Awards List  \\\n",
       "0  \\r\\nBram Stoker Award for Works for Young Read...   \n",
       "1  \\r\\nGeorgia Peach Book Award (2007), Buxtehude...   \n",
       "2  \\r\\nHugo Award for Best Novel (2001), Mythopoe...   \n",
       "3  \\r\\nBram Stoker Award for Best Work for Young ...   \n",
       "4  \\r\\nMythopoeic Fantasy Award for Children's Li...   \n",
       "\n",
       "                                           Book Name  \\\n",
       "0  Harry Potter and the Order of the Phoenix (Har...   \n",
       "1                             Twilight (Twilight #1)   \n",
       "2  Harry Potter and the Goblet of Fire (Harry Pot...   \n",
       "3  Harry Potter and the Prisoner of Azkaban (Harr...   \n",
       "4  Harry Potter and the Sorcerer's Stone (Harry P...   \n",
       "\n",
       "                                          Characters  \\\n",
       "0  [Sirius Black, Draco Malfoy, Ron Weasley, Petu...   \n",
       "1  [Edward Cullen, Jacob Black, Laurent, Renee, B...   \n",
       "2  [Sirius Black, Draco Malfoy, Ron Weasley, Petu...   \n",
       "3  [Sirius Black, Draco Malfoy, Ron Weasley, Petu...   \n",
       "4  [Draco Malfoy, Ron Weasley, Petunia Dursley, V...   \n",
       "\n",
       "                                              Genres  Num_reviews  \\\n",
       "0                       [Childrens, Fantasy, Novels]      25751.0   \n",
       "1  [Young Adult, Fantasy, Romance, Paranormal, Va...      93258.0   \n",
       "2  [Fantasy, Young Adult, Fiction, Fantasy, Magic...      27770.0   \n",
       "3                    [Fantasy, Young Adult, Fiction]      32160.0   \n",
       "4                    [Fantasy, Young Adult, Fiction]      67668.0   \n",
       "\n",
       "       ...        Genre_Nonfiction  Genre_War Genre_European Literature  \\\n",
       "0      ...                     0.0        0.0                       0.0   \n",
       "1      ...                     0.0        0.0                       0.0   \n",
       "2      ...                     0.0        0.0                       0.0   \n",
       "3      ...                     0.0        0.0                       0.0   \n",
       "4      ...                     0.0        0.0                       0.0   \n",
       "\n",
       "   Genre_Biography Genre_Womens Fiction  Genre_Suspense  Genre_Chick Lit  \\\n",
       "0              0.0                  0.0             0.0              0.0   \n",
       "1              0.0                  0.0             0.0              0.0   \n",
       "2              0.0                  0.0             0.0              0.0   \n",
       "3              0.0                  0.0             0.0              0.0   \n",
       "4              0.0                  0.0             0.0              0.0   \n",
       "\n",
       "   Genre_Adult  Genre_Autobiography  Genre_History  \n",
       "0          0.0                  0.0            0.0  \n",
       "1          0.0                  0.0            0.0  \n",
       "2          0.0                  0.0            0.0  \n",
       "3          0.0                  0.0            0.0  \n",
       "4          0.0                  0.0            0.0  \n",
       "\n",
       "[5 rows x 54 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Merged.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "dataframe = pd.read_csv('imdb/movie_data_releasedate.csv', header=None, names=['Link','Release Date'])\n",
    "\n",
    "dataframe['Link'] = dataframe['Link'].map(lambda x: re.search(\"(.*)ref\", x).group(1).strip()[:-1])\n",
    "\n",
    "dataframe = dataframe.drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Link</th>\n",
       "      <th>Release Date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>http://www.imdb.com/title/tt0373889/</td>\n",
       "      <td>11 July 2007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>http://www.imdb.com/title/tt1099212/</td>\n",
       "      <td>21 November 2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>http://www.imdb.com/title/tt0330373/</td>\n",
       "      <td>18 November 2005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>http://www.imdb.com/title/tt0304141/</td>\n",
       "      <td>4 June 2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>http://www.imdb.com/title/tt0241527/</td>\n",
       "      <td>16 November 2001</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   Link      Release Date\n",
       "0  http://www.imdb.com/title/tt0373889/      11 July 2007\n",
       "1  http://www.imdb.com/title/tt1099212/  21 November 2008\n",
       "2  http://www.imdb.com/title/tt0330373/  18 November 2005\n",
       "3  http://www.imdb.com/title/tt0304141/       4 June 2004\n",
       "4  http://www.imdb.com/title/tt0241527/  16 November 2001"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataframe.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "Merged = pd.merge(Merged, dataframe, how='left', on='Link', suffixes=('_x', '_y'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Link</th>\n",
       "      <th>New Book Name</th>\n",
       "      <th>Budget</th>\n",
       "      <th>Gross</th>\n",
       "      <th>Opening</th>\n",
       "      <th>Awards List</th>\n",
       "      <th>Book Name</th>\n",
       "      <th>Characters</th>\n",
       "      <th>Genres</th>\n",
       "      <th>Num_reviews</th>\n",
       "      <th>...</th>\n",
       "      <th>Genre_War</th>\n",
       "      <th>Genre_European Literature</th>\n",
       "      <th>Genre_Biography</th>\n",
       "      <th>Genre_Womens Fiction</th>\n",
       "      <th>Genre_Suspense</th>\n",
       "      <th>Genre_Chick Lit</th>\n",
       "      <th>Genre_Adult</th>\n",
       "      <th>Genre_Autobiography</th>\n",
       "      <th>Genre_History</th>\n",
       "      <th>Release Date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>http://www.imdb.com/title/tt0373889/</td>\n",
       "      <td>Harry Potter and the Order of the Phoenix</td>\n",
       "      <td>150000000</td>\n",
       "      <td>292004738</td>\n",
       "      <td>77108414</td>\n",
       "      <td>\\r\\nBram Stoker Award for Works for Young Read...</td>\n",
       "      <td>Harry Potter and the Order of the Phoenix (Har...</td>\n",
       "      <td>[Sirius Black, Draco Malfoy, Ron Weasley, Petu...</td>\n",
       "      <td>[Childrens, Fantasy, Novels]</td>\n",
       "      <td>25751.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11 July 2007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>http://www.imdb.com/title/tt1099212/</td>\n",
       "      <td>Twilight</td>\n",
       "      <td>37000000</td>\n",
       "      <td>191465414</td>\n",
       "      <td>69637740</td>\n",
       "      <td>\\r\\nGeorgia Peach Book Award (2007), Buxtehude...</td>\n",
       "      <td>Twilight (Twilight #1)</td>\n",
       "      <td>[Edward Cullen, Jacob Black, Laurent, Renee, B...</td>\n",
       "      <td>[Young Adult, Fantasy, Romance, Paranormal, Va...</td>\n",
       "      <td>93258.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>21 November 2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>http://www.imdb.com/title/tt0330373/</td>\n",
       "      <td>Harry Potter and the Goblet of Fire</td>\n",
       "      <td>150000000</td>\n",
       "      <td>290013036</td>\n",
       "      <td>102335066</td>\n",
       "      <td>\\r\\nHugo Award for Best Novel (2001), Mythopoe...</td>\n",
       "      <td>Harry Potter and the Goblet of Fire (Harry Pot...</td>\n",
       "      <td>[Sirius Black, Draco Malfoy, Ron Weasley, Petu...</td>\n",
       "      <td>[Fantasy, Young Adult, Fiction, Fantasy, Magic...</td>\n",
       "      <td>27770.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>18 November 2005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>http://www.imdb.com/title/tt0304141/</td>\n",
       "      <td>Harry Potter and the Prisoner of Azkaban</td>\n",
       "      <td>130000000</td>\n",
       "      <td>249358727</td>\n",
       "      <td>93687367</td>\n",
       "      <td>\\r\\nBram Stoker Award for Best Work for Young ...</td>\n",
       "      <td>Harry Potter and the Prisoner of Azkaban (Harr...</td>\n",
       "      <td>[Sirius Black, Draco Malfoy, Ron Weasley, Petu...</td>\n",
       "      <td>[Fantasy, Young Adult, Fiction]</td>\n",
       "      <td>32160.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4 June 2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>http://www.imdb.com/title/tt0241527/</td>\n",
       "      <td>Harry Potter and the Sorcerer's Stone</td>\n",
       "      <td>125000000</td>\n",
       "      <td>317557891</td>\n",
       "      <td>90294621</td>\n",
       "      <td>\\r\\nMythopoeic Fantasy Award for Children's Li...</td>\n",
       "      <td>Harry Potter and the Sorcerer's Stone (Harry P...</td>\n",
       "      <td>[Draco Malfoy, Ron Weasley, Petunia Dursley, V...</td>\n",
       "      <td>[Fantasy, Young Adult, Fiction]</td>\n",
       "      <td>67668.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>16 November 2001</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 55 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   Link  \\\n",
       "0  http://www.imdb.com/title/tt0373889/   \n",
       "1  http://www.imdb.com/title/tt1099212/   \n",
       "2  http://www.imdb.com/title/tt0330373/   \n",
       "3  http://www.imdb.com/title/tt0304141/   \n",
       "4  http://www.imdb.com/title/tt0241527/   \n",
       "\n",
       "                               New Book Name     Budget      Gross    Opening  \\\n",
       "0  Harry Potter and the Order of the Phoenix  150000000  292004738   77108414   \n",
       "1                                   Twilight   37000000  191465414   69637740   \n",
       "2        Harry Potter and the Goblet of Fire  150000000  290013036  102335066   \n",
       "3   Harry Potter and the Prisoner of Azkaban  130000000  249358727   93687367   \n",
       "4      Harry Potter and the Sorcerer's Stone  125000000  317557891   90294621   \n",
       "\n",
       "                                         Awards List  \\\n",
       "0  \\r\\nBram Stoker Award for Works for Young Read...   \n",
       "1  \\r\\nGeorgia Peach Book Award (2007), Buxtehude...   \n",
       "2  \\r\\nHugo Award for Best Novel (2001), Mythopoe...   \n",
       "3  \\r\\nBram Stoker Award for Best Work for Young ...   \n",
       "4  \\r\\nMythopoeic Fantasy Award for Children's Li...   \n",
       "\n",
       "                                           Book Name  \\\n",
       "0  Harry Potter and the Order of the Phoenix (Har...   \n",
       "1                             Twilight (Twilight #1)   \n",
       "2  Harry Potter and the Goblet of Fire (Harry Pot...   \n",
       "3  Harry Potter and the Prisoner of Azkaban (Harr...   \n",
       "4  Harry Potter and the Sorcerer's Stone (Harry P...   \n",
       "\n",
       "                                          Characters  \\\n",
       "0  [Sirius Black, Draco Malfoy, Ron Weasley, Petu...   \n",
       "1  [Edward Cullen, Jacob Black, Laurent, Renee, B...   \n",
       "2  [Sirius Black, Draco Malfoy, Ron Weasley, Petu...   \n",
       "3  [Sirius Black, Draco Malfoy, Ron Weasley, Petu...   \n",
       "4  [Draco Malfoy, Ron Weasley, Petunia Dursley, V...   \n",
       "\n",
       "                                              Genres  Num_reviews  \\\n",
       "0                       [Childrens, Fantasy, Novels]      25751.0   \n",
       "1  [Young Adult, Fantasy, Romance, Paranormal, Va...      93258.0   \n",
       "2  [Fantasy, Young Adult, Fiction, Fantasy, Magic...      27770.0   \n",
       "3                    [Fantasy, Young Adult, Fiction]      32160.0   \n",
       "4                    [Fantasy, Young Adult, Fiction]      67668.0   \n",
       "\n",
       "         ...         Genre_War  Genre_European Literature Genre_Biography  \\\n",
       "0        ...               0.0                        0.0             0.0   \n",
       "1        ...               0.0                        0.0             0.0   \n",
       "2        ...               0.0                        0.0             0.0   \n",
       "3        ...               0.0                        0.0             0.0   \n",
       "4        ...               0.0                        0.0             0.0   \n",
       "\n",
       "   Genre_Womens Fiction Genre_Suspense  Genre_Chick Lit  Genre_Adult  \\\n",
       "0                   0.0            0.0              0.0          0.0   \n",
       "1                   0.0            0.0              0.0          0.0   \n",
       "2                   0.0            0.0              0.0          0.0   \n",
       "3                   0.0            0.0              0.0          0.0   \n",
       "4                   0.0            0.0              0.0          0.0   \n",
       "\n",
       "   Genre_Autobiography  Genre_History      Release Date  \n",
       "0                  0.0            0.0      11 July 2007  \n",
       "1                  0.0            0.0  21 November 2008  \n",
       "2                  0.0            0.0  18 November 2005  \n",
       "3                  0.0            0.0       4 June 2004  \n",
       "4                  0.0            0.0  16 November 2001  \n",
       "\n",
       "[5 rows x 55 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Merged.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1477\n",
      "1362\n"
     ]
    }
   ],
   "source": [
    "print len(Merged)\n",
    "print len(Merged.groupby('Link', as_index=False).max())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import datetime\n",
    "from datetime import datetime\n",
    "from dateutil import parser\n",
    "\n",
    "def convert_to_datetime_publishdate(x):\n",
    "    if(x!='None'): \n",
    "        try:\n",
    "            arr = x.split(' ')\n",
    "            x = arr[0].strip()+' '+re.search('(.*?)\\D', arr[1].strip()).group(1)+' '+arr[2].strip()\n",
    "            #print type(x)\n",
    "            #datetime_obj = datetime.strftime(x , '%B %d %Y')\n",
    "            #print type(datetime_obj)\n",
    "            datetime_obj = parser.parse(x)\n",
    "            return datetime_obj\n",
    "        except Exception:\n",
    "            try:\n",
    "                arr = x.split(' ')\n",
    "                x = arr[0].strip()+' '+arr[1].strip()\n",
    "                #print x\n",
    "                #datetime_obj = datetime.strptime(x, '%B %Y')\n",
    "                datetime_obj = parser.parse(x)\n",
    "                return datetime_obj\n",
    "            except Exception:\n",
    "                try:\n",
    "                    arr = x.split('-')\n",
    "                    x = arr[0].strip()+' '+arr[1].strip()\n",
    "                    #print x\n",
    "                    #datetime_obj = datetime.strptime(x, '%b %y')\n",
    "                    datetime_obj = parser.parse(x)\n",
    "                    return datetime_obj\n",
    "                except Exception:\n",
    "                    try:\n",
    "                        x = x.strip()\n",
    "                        #print x\n",
    "                        #datetime_obj = datetime.strptime(x, '%Y')\n",
    "                        datetime_obj = parser.parse(x)\n",
    "                        return datetime_obj\n",
    "                    except Exception:\n",
    "                        try:\n",
    "                            x = x.strip()\n",
    "                            x = '0'+x\n",
    "                            #print x\n",
    "                            #datetime_obj = datetime.strptime(x, '%Y')\n",
    "                            print x\n",
    "                            datetime_obj = parser.parse(x)\n",
    "                            return datetime_obj\n",
    "                        except Exception:\n",
    "                            return np.datetime64('NaT')\n",
    "    else:\n",
    "        return np.datetime64('NaT')\n",
    "    \n",
    "\n",
    "def convert_to_datetime_releasedate(x):\n",
    "    if(x!='nan'):\n",
    "        try:\n",
    "            arr = x.split(' ')\n",
    "            x = arr[0].strip()+' '+arr[1].strip()+' '+arr[2].strip()\n",
    "            datetime_obj = datetime.strptime(x , '%d %B %Y')\n",
    "            return datetime_obj\n",
    "        except IndexError:\n",
    "            try:\n",
    "                arr = x.split(' ')\n",
    "                x = arr[0].strip()+' '+arr[1].strip()\n",
    "                datetime_obj = datetime.strptime(x, '%B %Y')\n",
    "                return datetime_obj\n",
    "            except IndexError:\n",
    "                x = x.strip()\n",
    "                datetime_obj = datetime.strptime(x, '%Y')\n",
    "                return datetime_obj\n",
    "    else:\n",
    "        return x\n",
    "    \n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0Sep 00\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/rakshit/miniconda2/lib/python2.7/site-packages/ipykernel/__main__.py:6: FutureWarning: the coerce=True keyword is deprecated, use errors='coerce' instead\n"
     ]
    }
   ],
   "source": [
    "# Merged['publish_date'][Merged['publish_date'].astype('str') == 'None'] = np.datetime64('NaT')\n",
    "# Merged['publish_date'] = np.where(Merged['publish_date'].astype(str)=='None', 'green', 'red')\n",
    "\n",
    "Merged['publish_date'] = Merged['publish_date'].fillna(np.datetime64('NaT'))\n",
    "Merged['publish_date'] = Merged['publish_date'].astype('str').map(convert_to_datetime_publishdate)\n",
    "Merged['publish_date']= pd.to_datetime(Merged['publish_date'], coerce=True)\n",
    "\n",
    "Merged['Release Date'] = Merged['Release Date'].astype('str').map(convert_to_datetime_releasedate)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0   1481 days\n",
       "1   1143 days\n",
       "2   1959 days\n",
       "3   1793 days\n",
       "4         NaT\n",
       "Name: Time Diff, dtype: timedelta64[ns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Merged['Time Diff'] = Merged['Release Date']-Merged['publish_date']\n",
    "Merged['Time Diff'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "Merged.to_csv('Merged_Dataset.csv', encoding='utf-8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [default]",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}