01-convert.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Converteer dat naar parquet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "def read_movies(file_name):\n",
    "    movies = pd.read_table(\n",
    "        file_name, encoding='utf_8', engine='python', sep='::', names=['imdb_id', 'title', 'genres']\n",
    "    )\n",
    "    titles = (\n",
    "        movies['title']\n",
    "            .str.extract(r'(?P<title>.+) \\((?P<year>\\d{4})\\)')\n",
    "            .astype({'year': np.int})\n",
    "    )\n",
    "    genres = (\n",
    "        movies['genres']\n",
    "            .str.lower()\n",
    "            .str.replace('-', '_')\n",
    "            .str.split('|')\n",
    "    )\n",
    "    return (\n",
    "        movies\n",
    "            .drop(columns=['title', 'genres'])\n",
    "            .join(titles)\n",
    "            .join(genres)\n",
    "    )\n",
    "\n",
    "def read_ratings(file_name):\n",
    "    return pd.read_table(\n",
    "        file_name, engine='python', sep='::', names=['user_id', 'imdb_id', 'rating', 'date_time'],\n",
    "        parse_dates=['date_time'], date_parser=lambda s: pd.to_datetime(s, origin='unix', unit='s')\n",
    "    )\n",
    "\n",
    "def read_users(file_name):\n",
    "    return pd.read_table(file_name, engine='python', sep='::', names=['user_id', 'twitter_id'])"
   ]
  },
  {
   "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>imdb_id</th>\n",
       "      <th>title</th>\n",
       "      <th>year</th>\n",
       "      <th>genres</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8</td>\n",
       "      <td>Edison Kinetoscopic Record of a Sneeze</td>\n",
       "      <td>1894</td>\n",
       "      <td>[documentary, short]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10</td>\n",
       "      <td>La sortie des usines Lumière</td>\n",
       "      <td>1895</td>\n",
       "      <td>[documentary, short]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12</td>\n",
       "      <td>The Arrival of a Train</td>\n",
       "      <td>1896</td>\n",
       "      <td>[documentary, short]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>25</td>\n",
       "      <td>The Oxford and Cambridge University Boat Race</td>\n",
       "      <td>1895</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>91</td>\n",
       "      <td>Le manoir du diable</td>\n",
       "      <td>1896</td>\n",
       "      <td>[short, horror]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35608</th>\n",
       "      <td>12133722</td>\n",
       "      <td>Have a Good Trip: Adventures in Psychedelics</td>\n",
       "      <td>2020</td>\n",
       "      <td>[documentary]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35609</th>\n",
       "      <td>12192654</td>\n",
       "      <td>Planet of the Humans</td>\n",
       "      <td>2019</td>\n",
       "      <td>[documentary]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35610</th>\n",
       "      <td>12194082</td>\n",
       "      <td>No Hunger in Paradise</td>\n",
       "      <td>2017</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35611</th>\n",
       "      <td>12221748</td>\n",
       "      <td>Becoming</td>\n",
       "      <td>2020</td>\n",
       "      <td>[documentary]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35612</th>\n",
       "      <td>12235234</td>\n",
       "      <td>Feya</td>\n",
       "      <td>2020</td>\n",
       "      <td>[drama, sci_fi, thriller]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>35613 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        imdb_id                                          title  year  \\\n",
       "0             8         Edison Kinetoscopic Record of a Sneeze  1894   \n",
       "1            10                   La sortie des usines Lumière  1895   \n",
       "2            12                         The Arrival of a Train  1896   \n",
       "3            25  The Oxford and Cambridge University Boat Race  1895   \n",
       "4            91                            Le manoir du diable  1896   \n",
       "...         ...                                            ...   ...   \n",
       "35608  12133722   Have a Good Trip: Adventures in Psychedelics  2020   \n",
       "35609  12192654                           Planet of the Humans  2019   \n",
       "35610  12194082                          No Hunger in Paradise  2017   \n",
       "35611  12221748                                       Becoming  2020   \n",
       "35612  12235234                                           Feya  2020   \n",
       "\n",
       "                          genres  \n",
       "0           [documentary, short]  \n",
       "1           [documentary, short]  \n",
       "2           [documentary, short]  \n",
       "3                            NaN  \n",
       "4                [short, horror]  \n",
       "...                          ...  \n",
       "35608              [documentary]  \n",
       "35609              [documentary]  \n",
       "35610                        NaN  \n",
       "35611              [documentary]  \n",
       "35612  [drama, sci_fi, thriller]  \n",
       "\n",
       "[35613 rows x 4 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movies = read_movies('MovieTweetings/latest/movies.dat')\n",
    "movies"
   ]
  },
  {
   "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>user_id</th>\n",
       "      <th>imdb_id</th>\n",
       "      <th>rating</th>\n",
       "      <th>date_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>114508</td>\n",
       "      <td>8</td>\n",
       "      <td>2013-10-05 21:00:50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>208092</td>\n",
       "      <td>5</td>\n",
       "      <td>2020-04-09 21:01:12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>358273</td>\n",
       "      <td>9</td>\n",
       "      <td>2020-01-15 03:10:27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>10039344</td>\n",
       "      <td>5</td>\n",
       "      <td>2020-01-09 20:50:53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>6199572</td>\n",
       "      <td>3</td>\n",
       "      <td>2020-05-14 18:54:43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>867691</th>\n",
       "      <td>67628</td>\n",
       "      <td>9071322</td>\n",
       "      <td>5</td>\n",
       "      <td>2020-02-01 00:37:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>867692</th>\n",
       "      <td>67628</td>\n",
       "      <td>9898858</td>\n",
       "      <td>3</td>\n",
       "      <td>2020-04-04 00:00:52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>867693</th>\n",
       "      <td>67629</td>\n",
       "      <td>172495</td>\n",
       "      <td>10</td>\n",
       "      <td>2020-04-17 07:03:35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>867694</th>\n",
       "      <td>67629</td>\n",
       "      <td>414387</td>\n",
       "      <td>10</td>\n",
       "      <td>2020-04-17 07:17:32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>867695</th>\n",
       "      <td>67630</td>\n",
       "      <td>1623205</td>\n",
       "      <td>6</td>\n",
       "      <td>2013-03-09 12:37:35</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>867696 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        user_id   imdb_id  rating           date_time\n",
       "0             1    114508       8 2013-10-05 21:00:50\n",
       "1             2    208092       5 2020-04-09 21:01:12\n",
       "2             2    358273       9 2020-01-15 03:10:27\n",
       "3             2  10039344       5 2020-01-09 20:50:53\n",
       "4             2   6199572       3 2020-05-14 18:54:43\n",
       "...         ...       ...     ...                 ...\n",
       "867691    67628   9071322       5 2020-02-01 00:37:28\n",
       "867692    67628   9898858       3 2020-04-04 00:00:52\n",
       "867693    67629    172495      10 2020-04-17 07:03:35\n",
       "867694    67629    414387      10 2020-04-17 07:17:32\n",
       "867695    67630   1623205       6 2013-03-09 12:37:35\n",
       "\n",
       "[867696 rows x 4 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ratings = read_ratings('MovieTweetings/latest/ratings.dat')\n",
    "ratings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>user_id</th>\n",
       "      <th>twitter_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>139564917</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>522540374</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>475571186</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>215022153</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>349681331</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67625</th>\n",
       "      <td>67626</td>\n",
       "      <td>441446292</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67626</th>\n",
       "      <td>67627</td>\n",
       "      <td>36878476</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67627</th>\n",
       "      <td>67628</td>\n",
       "      <td>330301436</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67628</th>\n",
       "      <td>67629</td>\n",
       "      <td>1244805465323835397</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67629</th>\n",
       "      <td>67630</td>\n",
       "      <td>491884729</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>67630 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       user_id           twitter_id\n",
       "0            1            139564917\n",
       "1            2            522540374\n",
       "2            3            475571186\n",
       "3            4            215022153\n",
       "4            5            349681331\n",
       "...        ...                  ...\n",
       "67625    67626            441446292\n",
       "67626    67627             36878476\n",
       "67627    67628            330301436\n",
       "67628    67629  1244805465323835397\n",
       "67629    67630            491884729\n",
       "\n",
       "[67630 rows x 2 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users = read_users('MovieTweetings/latest/users.dat')\n",
    "users"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "movies.to_parquet('data/movies.parquet')\n",
    "ratings.to_parquet('data/ratings.parquet')\n",
    "users.to_parquet('data/users.parquet')"
   ]
  }
 ],
 "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"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}