Preprocess_pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Perform pre-processing\n",
    "\n",
    "This version uses pandas and requires the data to fit in memory. TODO: Rewrite in PySpark\n",
    "\n",
    "For data I use:\n",
    "\n",
    "    Spotify Playlists Dataset\n",
    "    By Martin Pichl;  Eva Zangerle\n",
    "    For more details see: zenodo.org/record/2594557\n",
    "    \n",
    "This contains a collection of playlists and the songs contained in them. I find all the unique songs and playlists and assign them ids. I then extract pairs (playlist_id, track_id) which can be used to create a collaborative filtering based recommender (See Create Model). We also save the artists,tracks and track ids to a SQL database (Table Tracks in Recommendations.db).\n",
    "\n",
    "To make the computations more tracktable we restrict to the top_n (for now 10000) most common songs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import json\n",
    "import os\n",
    "from os import path\n",
    "import requests\n",
    "import re\n",
    "from zipfile import ZipFile\n",
    "\n",
    "\n",
    "data_url = 'https://zenodo.org/record/2594557/files/spotify_playlists.zip?download=1'\n",
    "csv_name = 'spotify_dataset.csv'\n",
    "\n",
    "# To make the computations more tracktible we use only the n most common tracks\n",
    "top_n = 10000\n",
    "# Name to save the filtered data\n",
    "processed_name = f'spotify_dataset_top{top_n}.csv'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {},
   "outputs": [
    ],
   "source": [
    "# Download data if it is not already downloaded\n",
    "if not path.isfile(csv_name):\n",
    "    \n",
    "    r = requests.get(data_url, allow_redirects=True)\n",
    "    open('spotify_dataset.zip', 'wb').write(r.content)\n",
    "\n",
    "    #unzip\n",
    "    with ZipFile('spotify_dataset.zip', 'r') as zipObj:\n",
    "        zipObj.extractall()\n",
    "        \n",
    "        \n",
    "# Read into pandas dataframe\n",
    "# Note the data has some lines with aditional commas, error_bad_lines = False allows us to skip these\n",
    "df = pd.read_csv(csv_name,error_bad_lines = False).dropna()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Function to clean standardize artist names\n",
    "def normalize_artist(name):\n",
    "    p = re.compile('&')\n",
    "    name = p.sub(r'and', name)\n",
    "    name = ' '.join(name.split())\n",
    "    return name\n",
    " \n",
    "#Function to clean standardize track names \n",
    "def normalize_track(name):\n",
    "    p = re.compile('(\\(|\\[).{0,}(\\)|\\])')\n",
    "    name = p.sub('', name)\n",
    "    name = name.split('- ')[0]\n",
    "    name = name.split('/')[0]\n",
    "    name = ' '.join(name.split())\n",
    "    return name\n",
    "\n",
    "\n",
    "df.columns = ['user','artist','track','playlist']\n",
    "df['artist'] = df['artist'].apply(normalize_artist)\n",
    "df['track'] = df['track'].apply(normalize_track)\n",
    "\n",
    "#Add fullname as unqiue identifier\n",
    "df['full_name'] = df.artist + '-' + df.track\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Use ony the top n most common tracks\n",
    "df_top = df[df['full_name'].isin(set(df.groupby('full_name').count()['user'].nlargest(top_n).index))]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-136-cbeb16fb100d>:45: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_top['playlist_id'] = df_top['playlist'].map(playlist_indexer)\n",
      "<ipython-input-136-cbeb16fb100d>:46: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_top['track_id'] = df_top['full_name'].map(track_indexer)\n"
     ]
    }
   ],
   "source": [
    "class Indexer():\n",
    "    '''\n",
    "    Indexer contains a dictionary (indexed) of already seen songs and their indexes as well as a \n",
    "    counter max_index which gives the index of the last record, allowing us to assign a new number to unseen entries.\n",
    "    '''\n",
    "    \n",
    "    def __init__(self):\n",
    "        self.max_index = -1\n",
    "        self.indexed = {}\n",
    "    \n",
    "    def get_index(self,x):\n",
    "        '''\n",
    "        Return the index of x if seen before, otherwise add it to indexed and return a new index.\n",
    "        '''\n",
    "        if x in self.indexed:\n",
    "            return self.indexed[x]\n",
    "        else:\n",
    "            self.max_index += 1\n",
    "            self.indexed[x] = self.max_index\n",
    "            return self.max_index\n",
    "        \n",
    "    def __call__(self,x):\n",
    "        '''\n",
    "        Allow us to index via calling the object.\n",
    "        '''\n",
    "        return self.get_index(x)\n",
    "    \n",
    "    def save_index(self,path):\n",
    "        '''\n",
    "        Save the internal dictionary to a json\n",
    "        '''\n",
    "        with open(path, 'w') as f:\n",
    "            json.dump(self.indexed, f)\n",
    "            \n",
    "    def __len__(self):\n",
    "        return self.get_max()\n",
    "    \n",
    "    def get_max(self):\n",
    "        return self.max_index+1\n",
    "    \n",
    "\n",
    "# Generate Index\n",
    "track_indexer = Indexer()\n",
    "playlist_indexer = Indexer()\n",
    "df_top['playlist_id'] = df_top['playlist'].map(playlist_indexer)\n",
    "df_top['track_id'] = df_top['full_name'].map(track_indexer)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "metadata": {},
   "outputs": [],
   "source": [
    "#SAVE DATA\n",
    "\n",
    "#Save name to track_id dictionary\n",
    "track_indexer.save_index('name_to_id.json')\n",
    "\n",
    "# Save top n to csv as (playlist_id,track_id) pairs\n",
    "df_top[['playlist_id','track_id']].to_csv(processed_name,index=False)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'num_playlists': 88878, 'num_tracks': 10000}"
      ]
     },
     "execution_count": 138,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "{'num_playlists':len(playlist_indexer), 'num_tracks':len(track_indexer)}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "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>artist</th>\n",
       "      <th>track</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>track_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Elvis Costello</td>\n",
       "      <td>Alison</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Crowded House</td>\n",
       "      <td>Don't Dream It's Over</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Joshua Radin</td>\n",
       "      <td>I'd Rather Be With You</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Paul McCartney</td>\n",
       "      <td>Live And Let Die</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Joshua Radin</td>\n",
       "      <td>Only You</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9995</th>\n",
       "      <td>Otto Klemperer/Sir Peter Pears/Dietrich Fische...</td>\n",
       "      <td>'St Matthew Passion' BWV244</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9996</th>\n",
       "      <td>Fantomas</td>\n",
       "      <td>04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9997</th>\n",
       "      <td>Kerstin Gier</td>\n",
       "      <td>Silber</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9998</th>\n",
       "      <td>Béla Bartók</td>\n",
       "      <td>Mikrokosmos, Sz. 107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9999</th>\n",
       "      <td>George Frideric Handel</td>\n",
       "      <td>Semele HWV 58</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10000 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                     artist  \\\n",
       "track_id                                                      \n",
       "0                                            Elvis Costello   \n",
       "1                                             Crowded House   \n",
       "2                                              Joshua Radin   \n",
       "3                                            Paul McCartney   \n",
       "4                                              Joshua Radin   \n",
       "...                                                     ...   \n",
       "9995      Otto Klemperer/Sir Peter Pears/Dietrich Fische...   \n",
       "9996                                               Fantomas   \n",
       "9997                                           Kerstin Gier   \n",
       "9998                                            Béla Bartók   \n",
       "9999                                 George Frideric Handel   \n",
       "\n",
       "                                track  \n",
       "track_id                               \n",
       "0                              Alison  \n",
       "1               Don't Dream It's Over  \n",
       "2              I'd Rather Be With You  \n",
       "3                    Live And Let Die  \n",
       "4                            Only You  \n",
       "...                               ...  \n",
       "9995      'St Matthew Passion' BWV244  \n",
       "9996                               04  \n",
       "9997                           Silber  \n",
       "9998             Mikrokosmos, Sz. 107  \n",
       "9999                    Semele HWV 58  \n",
       "\n",
       "[10000 rows x 2 columns]"
      ]
     },
     "execution_count": 139,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get unqiue tracks\n",
    "\n",
    "df_tracks = df_top[['artist','track','track_id']].drop_duplicates()\n",
    "df_tracks.set_index('track_id',inplace = True)\n",
    "df_tracks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Save unique tracks to a sql database\n",
    "#https://docs.python.org/3/library/sqlite3.html\n",
    "\n",
    "import sqlite3\n",
    "conn = sqlite3.connect('recommendations.db')\n",
    "\n",
    "cursor = conn.cursor()\n",
    "cursor.execute(\"DROP TABLE IF EXISTS Tracks\")\n",
    "\n",
    "df_tracks.to_sql('Tracks',conn)\n",
    "\n",
    "# Save (commit) the changes\n",
    "conn.commit()\n",
    "\n",
    "# We can also close the connection if we are done with it.\n",
    "# Just be sure any changes have been committed or they will be lost.\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Elvis Costello\n",
      "('Elvis Costello and The Attractions', 'Pump It Up')\n",
      "('David Bowie', 'Young Americans')\n",
      "(\"The B-52's\", 'Rock Lobster')\n",
      "('The Rolling Stones', 'Beast Of Burden')\n",
      "('Smokey Robinson and The Miracles', 'The Tracks Of My Tears')\n"
     ]
    }
   ],
   "source": [
    "import sqlite3\n",
    "conn = sqlite3.connect('recommendations.db')\n",
    "\n",
    "cursor = conn.cursor()\n",
    "\n",
    "track_id = 0\n",
    "artist = list(cursor.execute(\"SELECT Tracks.artist FROM Tracks WHERE track_id = ?\", (track_id,)))[0][0]\n",
    "print(artist)\n",
    "result = cursor.execute(\"SELECT Tracks.artist, Tracks.track \\\n",
    "        FROM (SELECT recommended_id, distance FROM Recommendations WHERE track_id = ?) AS Recommended\\\n",
    "        INNER JOIN Tracks WHERE Tracks.track_id = Recommended.recommended_id AND Tracks.artist  <> ? \\\n",
    "        ORDER BY Recommended.distance LIMIT 5\", (track_id, artist))\n",
    "\n",
    "for r in result:\n",
    "    print(r)\n",
    "\n",
    "# We can also close the connection if we are done with it.\n",
    "# Just be sure any changes have been committed or they will be lost.\n",
    "conn.close()"
   ]
  },
  {
   "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.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}