Sampling_playlists_and_tracks.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Subsampling playlists and tracks \n",
    "***\n",
    "This is the script we used to subsample playlist-track contingency from database, both randomly selecting, or seeding with a popular song.\n",
    "***\n",
    "Import libraries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import scipy.sparse as sps\n",
    "import scipy\n",
    "import pickle\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Connect to database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create a new database by connecting to it\n",
    "conn = sqlite3.connect(\"spotifyDB.db\")\n",
    "cur = conn.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Random sampling\n",
    "***\n",
    "Here we randomly selected 10,000 playlists and found all tracks in the playlists."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# subsample playlists\n",
    "n_lists = 10000\n",
    "query = 'SELECT playlist_id FROM playlists ORDER BY RANDOM() LIMIT {};'.format(n_lists)\n",
    "sublist = pd.read_sql_query(query, conn)\n",
    "sublist = list(sublist['playlist_id'].values)\n",
    "\n",
    "tracks = []\n",
    "for playlist_id in sublist:\n",
    "    query = 'SELECT tracks FROM playlists WHERE playlist_id = {};'.format(playlist_id)\n",
    "    all_tracks = pd.read_sql_query(query, conn)\n",
    "    ids = all_tracks['tracks'].values[0].split(',')\n",
    "    tracks = list(set(tracks) | set(ids))\n",
    "\n",
    "n_tracks = len(tracks)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Sampling with a seed track\n",
    "***\n",
    "Here we randomly selected a popular track that is member of >10,000 playlists, selected 10,000 playlists from it, and found all tracks in all those playlists."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# subsample playlists from a popular seed track\n",
    "n_lists = 10000\n",
    "seed = pd.read_sql_query(\"select track, playlist_member from tracks WHERE num_member >10000 ORDER BY RANDOM() LIMIT 1;\", conn)\n",
    "\n",
    "seed_track = seed['track'].values[0]\n",
    "\n",
    "sublist = seed['playlist_member'].values[0].split(',')\n",
    "sublist = sublist[:n_lists]\n",
    "\n",
    "tracks = []\n",
    "for playlist_id in sublist:\n",
    "    query = 'SELECT tracks FROM playlists WHERE playlist_id = {};'.format(playlist_id)\n",
    "    all_tracks = pd.read_sql_query(query, conn)\n",
    "    ids = all_tracks['tracks'].values[0].split(',')\n",
    "    tracks = list(set(tracks) | set(ids))\n",
    "\n",
    "n_tracks = len(tracks)\n",
    "sublist = [int(ii) for ii in sublist]\n",
    "seed_ind = tracks.index(seed_track)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Find all the playlist-track pairs\n",
    "***\n",
    "We idenitified all playlist-track pairs with their indices."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# fetch playlist_member for all tracks\n",
    "keys = tracks\n",
    "keys = '\\',\\''.join(keys)\n",
    "keys = \"('\"+keys+\"')\"\n",
    "\n",
    "query = 'SELECT playlist_member FROM tracks WHERE track IN {};'.format(keys)\n",
    "all_members = pd.read_sql_query(query, conn)\n",
    "\n",
    "for i,track_id in enumerate(tracks):\n",
    "    mem = all_members['playlist_member'].values[i].split(',')\n",
    "    mem = [int(track) for track in mem]\n",
    "    # find indices of playlists that this track is a member of\n",
    "    list_ind = [idx for idx, list_id in enumerate(sublist) if list_id in mem]\n",
    "    # create coordinate and values for the sparse matrix\n",
    "    n_col = len(list_ind)\n",
    "    if i == 0:\n",
    "        col = np.array(list_ind).reshape(1,-1)\n",
    "        row = i*np.ones(n_col,).astype('int').reshape(1,-1)\n",
    "        value = np.ones(n_col,).reshape(1,-1)\n",
    "    elif i > 0:\n",
    "        col = np.hstack((col, np.array(list_ind).reshape(1,-1)))\n",
    "        row = np.hstack((row, i*np.ones(n_col,).astype('int').reshape(1,-1)))\n",
    "        value = np.hstack((value, np.ones(n_col,).reshape(1,-1)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create a sparse matrix \n",
    "***\n",
    "We filled all the playlist-track pairs into a sparse matrix and save them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create the sparse matrix\n",
    "sps_acc = sps.coo_matrix((value.reshape(-1,), (row.reshape(-1,), col.reshape(-1,))), shape=(n_tracks, n_lists))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# for seeded data, save the seed index\n",
    "# seed_ind = tracks.index(seed_track)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# save files\n",
    "scipy.sparse.save_npz('sparse_10000_rand.npz', sps_acc)\n",
    "with open('sublist_10000_rand.pkl', 'wb') as f1:\n",
    "    pickle.dump(sublist, f1)\n",
    "with open('tracks_10000_rand.pkl', 'wb') as f2:\n",
    "    pickle.dump(tracks, f2)    \n",
    "# with open('seed_ind.pkl', 'wb') as f3:\n",
    "#     pickle.dump(seed_ind, f3) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Close cursor and disconnect to the database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# disconnect\n",
    "cur.close()\n",
    "conn.close()"
   ]
  }
 ],
 "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.6.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}