BOM_IMDB_join.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from functools import reduce\n",
    "from pandas import DataFrame\n",
    "import re\n",
    "\n",
    "# Directories where bom and imdb CSVs reside\n",
    "BOM_DIR = \"bom\" # /data/picadolj/Datasets/movies/bom/\n",
    "IMDB_DIR = \"bk-set8-schema1\" # /data/picadolj/Datasets/imdb/bk-set8-schema1/\n",
    "\n",
    "MONTHS = {'January' : 1,\n",
    "          'February' : 2,\n",
    "          'March' : 3,\n",
    "          'April' : 4,\n",
    "          'May' : 5,\n",
    "          'June' : 6,\n",
    "          'July' : 7, \n",
    "          'August' : 8,\n",
    "          'September' : 9,\n",
    "          'October' : 10,\n",
    "          'November' : 11,\n",
    "          'December' : 12,\n",
    "          'Fall' : 10,\n",
    "          'Winter' : 11\n",
    "}\n",
    "\n",
    "def dateFormat(unformattedDate):\n",
    "    splitDate = re.split(\"[,]{0,1} \", unformattedDate)\n",
    "    \n",
    "    if len(splitDate) == 1:\n",
    "        month = 1\n",
    "        day = 1\n",
    "        year = splitDate[0]\n",
    "    elif len(splitDate) == 2:\n",
    "        month = MONTHS[splitDate[0]]\n",
    "        day = 1\n",
    "        year = splitDate[1]\n",
    "    else:\n",
    "        month = MONTHS[splitDate[0]]\n",
    "        day = splitDate[1]\n",
    "        year = splitDate[2]\n",
    "        \n",
    "    formattedString = \"%s\" % (year)\n",
    "    return formattedString\n",
    "\n",
    "# Assumes columns as x, y, z. Not always true (see actors)\n",
    "def getJoinedTable(xTOy, yTOz, xTitle, zTitle):\n",
    "    xTOyDataFrame = pd.read_csv(xTOy,\n",
    "                        names=[xTitle,'y'],encoding='ISO-8859-1',skipinitialspace = True)\n",
    "    yTOzDataFrame = pd.read_csv(yTOz,\n",
    "                        names=['y',zTitle],encoding='ISO-8859-1',skipinitialspace = True)\n",
    "    return pd.merge(xTOyDataFrame,yTOzDataFrame,how='left', on='y')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''\n",
    "                      Create IMDB database file \n",
    "'''\n",
    "    \n",
    "# Read in IMDB CSVs (release date only read in to create bom_id)\n",
    "# concatenate values in cases of 1-to-n relationships\n",
    "titleYear = pd.read_csv('%s/%s' % (IMDB_DIR, 'movies.csv'),\n",
    "                        names=['imdb_id','title','year'],encoding='ISO-8859-1',skipinitialspace = True)\n",
    "\n",
    "directors = getJoinedTable('%s/%s' % (IMDB_DIR, 'movies2directors.csv'), '%s/%s' % (IMDB_DIR, 'directors.csv'),\n",
    "                          'imdb_id', 'director')\n",
    "directors = directors.groupby('imdb_id')['director'].apply(lambda x: \"%s\" % ' '.join(x)).to_frame()\n",
    "\n",
    "genres = pd.read_csv('%s/%s' % (IMDB_DIR, 'movies2genres_2.csv'),\n",
    "                     names=['imdb_id','genre'], encoding='ISO-8859-1')\n",
    "genres = genres.groupby('imdb_id')['genre'].apply(lambda x: \"%s\" % ' '.join(x)).to_frame()\n",
    "\n",
    "writers = getJoinedTable('%s/%s' % (IMDB_DIR, 'movies2writers.csv'), '%s/%s' % (IMDB_DIR, 'writers.csv'),\n",
    "                          'imdb_id', 'writer')\n",
    "writers = writers.groupby('imdb_id')['writer'].apply(lambda x: \"%s\" % ' '.join(x)).to_frame()\n",
    "\n",
    "composers = getJoinedTable('%s/%s' % (IMDB_DIR, 'movies2composers.csv'), '%s/%s' % (IMDB_DIR, 'composers.csv'),\n",
    "                          'imdb_id', 'composer')\n",
    "composers = composers.groupby('imdb_id')['composer'].apply(lambda x: \"%s\" % ' '.join(x)).to_frame()\n",
    "\n",
    "costDesigners = getJoinedTable('%s/%s' % (IMDB_DIR, 'movies2costdes.csv'), '%s/%s' % (IMDB_DIR, 'costdesigners.csv'),\n",
    "                          'imdb_id', 'costume_designer')\n",
    "costDesigners = costDesigners.groupby('imdb_id')['costume_designer'].apply(lambda x: \"%s\" % ' '.join(x)).to_frame()\n",
    "\n",
    "# Join CSV dataFrames\n",
    "dataFrames = [titleYear, directors, genres, writers, composers, costDesigners]\n",
    "imdbJoined = reduce(lambda left,right: pd.merge(left,right,how='left', on='imdb_id'), dataFrames)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''\n",
    "                      Create BOM database file \n",
    "'''\n",
    "# Read in BOM CSVs (release date only read in to create bom_id)\n",
    "releaseDate = pd.read_csv('%s/%s' % (BOM_DIR, 'movies2releasedate_bom.csv'),\n",
    "                          names=['title','release_date'], encoding='ISO-8859-1')\n",
    "distributors = pd.read_csv('%s/%s' % (BOM_DIR, 'movies2distributor_bom.csv'),\n",
    "                           names=['title','distributor'], encoding='ISO-8859-1')\n",
    "totalgross = pd.read_csv('%s/%s' % (BOM_DIR, 'movies2totalgross_bom.csv'),\n",
    "                         names=['title','total_gross'], encoding='ISO-8859-1')\n",
    "inrelease = pd.read_csv('%s/%s' % (BOM_DIR, 'movies2inrelease_bom.csv'),\n",
    "                        names=['title','in_release'], encoding='ISO-8859-1')\n",
    "theaters = pd.read_csv('%s/%s' % (BOM_DIR, 'movies2theaters_bom.csv'),\n",
    "                       names=['title','theaters'], encoding='ISO-8859-1')\n",
    "prodbudget = pd.read_csv('%s/%s' % (BOM_DIR, 'movies2prodbudget_bom.csv'),\n",
    "                         names=['title','prod_budget'], encoding='ISO-8859-1')\n",
    "series = releaseDate.groupby('title').size()\n",
    "series[series > 1]\n",
    "\n",
    "# Join CSV dataFrames\n",
    "dataFrames = [releaseDate, distributors, totalgross, inrelease, theaters, prodbudget]\n",
    "bomJoined = reduce(lambda left,right: pd.merge(left,right,how='left', on='title'), dataFrames)\n",
    "\n",
    "# Form bom_id as \"TITLE-YEAR\" and insert as first column. Drop release_date--it is no longer needed.\n",
    "bom_ids = bomJoined.apply((lambda x: \"%s-%s\" % (x['title'], dateFormat(x['release_date']))), axis=1)\n",
    "# bom_ids = bomJoined.apply((lambda x: \"%s\" % (x['title'])), axis=1)\n",
    "bomJoined.insert(loc=0, column='bom_id', value=bom_ids.values)\n",
    "bomJoined = bomJoined.drop(columns=['release_date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''\n",
    "                    Create truth_table.csv and move IMDB attributes to bom_Joined\n",
    "'''\n",
    "\n",
    "# Form bom_id as \"TITLE-YEAR\" from title atribute\n",
    "bom_ids = titleYear.apply((lambda x: \"%s-%s\" % (\n",
    "    re.sub(\"(?: \\([0-9]{4}\\)){1}(?: \\(.*\\)){0,1}(?: \\{.*\\}){0,1}\", \"\", x['title']), # Remove \"(YEAR) (EXTRA) {EPISODE_INFO}\" from end of title\n",
    "    re.sub(\".*\\(([0-9]{4})\\){1}.*\", \"\\g<1>\", x['title']))), axis=1) # Grab \"(YEAR)\" from title\n",
    "#bom_ids = titleYear.apply((lambda x: \"%s\" % (\n",
    "#    re.sub(\"(?: \\([0-9]{4}\\)){1}(?: \\(.*\\)){0,1}(?: \\{.*\\}){0,1}\", \"\", x['title']))), axis=1) # Grab \"(YEAR)\" from title\n",
    "titleYear.insert(loc=0, column='bom_id', value=bom_ids.values)\n",
    "imdbTemp = titleYear.drop(columns=['title', 'year'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add attributes to move to bomJoined from imdbTemp\n",
    "imdbPlots = pd.read_csv('%s/%s' % (IMDB_DIR, 'plots.csv'),\n",
    "                        names=['imdb_id','plot'], encoding='ISO-8859-1')\n",
    "\n",
    "imdbTechnical = pd.read_csv('%s/%s' % (IMDB_DIR, 'technical.csv'),\n",
    "                            names=['imdb_id','technical'], encoding='ISO-8859-1')\n",
    "imdbTechnical = imdbTechnical.groupby('imdb_id')['technical'].apply(lambda x: \"%s\" % ' '.join(x)).to_frame()\n",
    "\n",
    "imdbProducers = getJoinedTable('%s/%s' % (IMDB_DIR, 'movies2producers.csv'), '%s/%s' % (IMDB_DIR, 'producers.csv'),\n",
    "                          'imdb_id', 'producer')\n",
    "imdbProducers = imdbProducers.groupby('imdb_id')['producer'].apply(lambda x: \"%s\" % ' '.join(x)).to_frame()\n",
    "\n",
    "# Join CSV dataFrames\n",
    "dataFrames = [imdbTemp, imdbPlots, imdbTechnical, imdbProducers]\n",
    "imdbTemp = reduce(lambda left,right: pd.merge(left,right,how='left', on='imdb_id'), dataFrames)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a new DataFrame that is the union of the attributes of bomJoined and imdbTemp\n",
    "bom_imdbJoined = pd.merge(bomJoined,imdbTemp,how='left', on='bom_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Finalize bomJoined\n",
    "bomJoined = bom_imdbJoined.drop(columns=['imdb_id']) \n",
    "bomJoined.to_csv('bom_joined.csv', index=False, header=True)\n",
    "\n",
    "# Write out imdbJoined\n",
    "imdbJoined.to_csv('imdb_joined.csv', index=False, header=True)\n",
    "\n",
    "# Create truth_table\n",
    "truthTable = bom_imdbJoined.drop(columns=['title', 'distributor', 'total_gross', \n",
    "                                         'in_release', 'theaters', 'prod_budget',\n",
    "                                        'plot', 'technical', 'producer'])\n",
    "truthTable.dropna(subset=['imdb_id'], inplace=True)\n",
    "truthTable = truthTable.drop_duplicates(['bom_id', 'imdb_id'])\n",
    "truthTable.to_csv('truth_table.csv', index=False, header=True)"
   ]
  },
  {
   "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.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}