spotify-data-aggregator.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import spotipy\n",
    "import time\n",
    "import json\n",
    "import openai\n",
    "from sqlalchemy import create_engine, MetaData, Table\n",
    "from getpass import getpass\n",
    "from spotipy.oauth2 import SpotifyClientCredentials"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Clean scraped Spotify Top 100 Podcasts in Germany"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load the CSV file\n",
    "\n",
    "data = pd.read_csv('scraped_data/spotify_top_100_germany.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Extract id from url and rename column url into spotify_id\n",
    "\n",
    "data['url'] = data['url'].astype(str).replace('.*/', '', regex=True)\n",
    "data.rename(columns={'url':'spotify_id'}, inplace=True)\n",
    "data.head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Get Shows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Extract ids from dataframe\n",
    "\n",
    "spotify_show_ids = data['spotify_id'].values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create Spotify client\n",
    "\n",
    "client_id = getpass(prompt='Enter Spotify Client ID: ')\n",
    "client_secret = getpass(prompt='Enter Spotify Client Secret: ')\n",
    "\n",
    "spotify_client = spotipy.Spotify(client_credentials_manager=SpotifyClientCredentials(client_id=client_id, client_secret=client_secret))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# There's too many ids so we split the array in 2\n",
    "\n",
    "spotify_show_ids_1, spotify_show_ids_2 = np.array_split(spotify_show_ids, 2, axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get information from shows in spotify_ids_1\n",
    " \n",
    "shows1 = spotify_client.shows(spotify_show_ids_1, market='DE')['shows']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get information from shows in spotify_ids_2\n",
    " \n",
    "shows2 = spotify_client.shows(spotify_show_ids_2, market='DE')['shows']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Concatenate shows\n",
    "\n",
    "shows = np.concatenate((shows1, shows2))"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Enrich Shows using Machine Learning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define openai api_key\n",
    "\n",
    "openai_api_key = getpass(prompt='Enter OpenAI API Key: ')\n",
    "openai.api_key = openai_api_key"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Helper functions\n",
    "\n",
    "def get_show_enrichments(publisher, description):\n",
    "\n",
    "    # Define the list of categories\n",
    "    categories = ['Comedy', 'News', 'Politics', 'Science', 'Technology', \n",
    "    'Education', 'History', 'Business', 'Health', 'Personal development', \n",
    "    'True crime', 'Sports', 'Music', 'Literature', 'Travel', 'Society & Culture', \n",
    "    'Personal Journals', 'Relationships', 'Food', 'Art', 'Pop culture']\n",
    "\n",
    "    prompt = f'''The following is a list of podcast categories : {categories}.\n",
    "    A publisher variable is defined as {publisher}.\n",
    "    A description variable is definied as {description}.\n",
    "    Tell me :\n",
    "    - List of categories, comma-separated from this list to which the description variable could fit.\n",
    "    - How many categories have been found. If none, set it to 0.\n",
    "    - From publisher and description variables, how many person can we identify? Spotify should be ignored.\n",
    "    - From these identified persons, is there a male name? If you are not sure, set it to not sure\n",
    "    - From these identified persons, is there a female name. If you are not sure, set it to not sure\n",
    "    Answer should be a json object containing properties \"categories\", \"nb_categories\", \"nb_people\", \"is_male_publisher\" as boolean, \"is_female_publisher\" as boolean\n",
    "    '''\n",
    "\n",
    "    answer = openai.Completion.create(\n",
    "        model=\"text-davinci-003\",\n",
    "        prompt=prompt,\n",
    "        temperature=0,\n",
    "        max_tokens=1024,\n",
    "        top_p=1.0,\n",
    "        frequency_penalty=0.0,\n",
    "        presence_penalty=0.0\n",
    "    )['choices'][0]['text']\n",
    "    \n",
    "    try:\n",
    "        return json.loads(answer)\n",
    "    except Exception:\n",
    "        return None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Enrich shows\n",
    "\n",
    "for show in shows:\n",
    "    enrichments = get_show_enrichments(show['publisher'], show['description'])\n",
    "    if enrichments is None:\n",
    "        print(f\"Could not enrich show '{show['id']}'. Skipping it.\")\n",
    "        continue\n",
    "\n",
    "    show['categories'] = enrichments['categories']\n",
    "    show['nb_categories'] = enrichments['nb_categories']\n",
    "    show['nb_people'] = enrichments['nb_people']\n",
    "    show['is_male_publisher'] = enrichments['is_male_publisher']\n",
    "    show['is_female_publisher'] = enrichments['is_female_publisher']\n",
    "    time.sleep(2)\n",
    "    print(f\"Enrichment of show '{show['id']}' done. Waiting 2 sec before retrieving categories from next show.\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Clean Show dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Normalize json\n",
    "\n",
    "shows_data = pd.json_normalize(shows)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Display head\n",
    "\n",
    "shows_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Rearrange dataframe\n",
    "\n",
    "columns = ['id', 'name', 'publisher', 'media_type', 'languages', 'available_markets', 'explicit', 'total_episodes', 'categories', 'nb_categories', 'nb_people', 'is_male_publisher', 'is_female_publisher']\n",
    "shows_data = shows_data.reindex(columns=columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remove special characters from languages\n",
    "\n",
    "shows_data['languages'] = shows_data['languages'].astype(str).replace(\"[\\[\\'\\]]\", '', regex=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remove special characters from available_markets\n",
    "\n",
    "shows_data['available_markets'] = shows_data['available_markets'].astype(str).replace(\"[\\[\\'\\]]\", '', regex=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Unify languages\n",
    "\n",
    "def unify_languages(cell):\n",
    "    if cell in ['de', 'de-DE']:\n",
    "        return 'de'\n",
    "    return 'en'\n",
    "\n",
    "shows_data['languages'] = shows_data['languages'].apply(unify_languages)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remove special characters from categories\n",
    "\n",
    "shows_data['categories'] = shows_data['categories'].astype(str).replace(\"[\\[\\'\\]]\", '', regex=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Transform categories into columns\n",
    "\n",
    "categories = ['Comedy', 'News', 'Politics', 'Science', 'Technology', \n",
    "    'Education', 'History', 'Business', 'Health', 'Personal development', \n",
    "    'True crime', 'Sports', 'Music', 'Literature', 'Travel', 'Society & Culture', \n",
    "    'Personal Journals', 'Relationships', 'Food', 'Art', 'Pop culture']\n",
    "\n",
    "for cat in categories:\n",
    "    standardized_category = cat.lower().replace('&', '').replace('  ', '_').replace(' ', '_')\n",
    "    shows_data[f'category_{standardized_category}'] = shows_data['categories'].str.contains(cat)\n",
    "\n",
    "shows_data.drop(columns='categories', axis=0, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Transform available_markets into columns\n",
    "\n",
    "markets = max(shows_data['available_markets'].unique(), key=len)\n",
    "markets = markets.split(', ')\n",
    "\n",
    "for market in markets:\n",
    "    standardized_market = market.lower()\n",
    "    shows_data[f'market_{standardized_market}'] = shows_data['available_markets'].str.contains(market)\n",
    "\n",
    "shows_data.drop(columns='available_markets', axis=0, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Display head\n",
    "\n",
    "shows_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save shows_data as csv file\n",
    "\n",
    "shows_data.to_csv('data/csv/shows_data.csv')"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Save Show Data in MySQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Connect to the database\n",
    "\n",
    "mysql_password = getpass(prompt='Enter OpenAI API Key: ')\n",
    "engine = create_engine(f'mysql+pymysql://root:{mysql_password}@localhost/')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create spotify_db if it doesn't exist\n",
    "\n",
    "engine.execute(\"CREATE DATABASE IF NOT EXISTS spotify_db\")\n",
    "engine.execute(\"USE spotify_db\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Insert the dataframe into shows database\n",
    "\n",
    "shows_data.to_sql(name='shows', con=engine, if_exists='append', index=False)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Get Episodes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Helper functions\n",
    "\n",
    "def retrieve_all_episodes(show_id):\n",
    "    response = try_show_episodes(show_id)\n",
    "    episodes = response['items']\n",
    "    offset = 50\n",
    "\n",
    "    while response['next'] != None:\n",
    "        response = try_show_episodes(show_id, offset)\n",
    "        episodes += response['items']\n",
    "        offset += 50\n",
    "\n",
    "    add_show_id_property(episodes, show_id)\n",
    "    return episodes\n",
    "\n",
    "\n",
    "def try_show_episodes(show_id, offset=0):\n",
    "    try:\n",
    "        return spotify_client.show_episodes(show_id, offset=offset, limit=50, market='de')\n",
    "    except spotipy.client.SpotifyException:\n",
    "        return []\n",
    "\n",
    "\n",
    "def add_show_id_property(episodes, show_id):\n",
    "    for episode in episodes:\n",
    "        episode['show_id'] = show_id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Retrieve episodes of every show and add show_id property into it\n",
    "\n",
    "episodes = []\n",
    "for show_id in spotify_show_ids:\n",
    "    print(f\"Get episodes from show '{show_id}'\")\n",
    "    show_episodes = retrieve_all_episodes(show_id)\n",
    "    episodes += show_episodes\n",
    "    time.sleep(2)\n",
    "    print(f\"Waiting 2 sec before retrieving episodes from next show\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Show how many episodes have been retrieved\n",
    "\n",
    "len(episodes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save episodes json into episodes_data.json\n",
    "\n",
    "with open('data/json/episodes_data.json', 'w') as f:\n",
    "    json.dump(episodes, f)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Enrich Episodes using Machine Learning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Helper functions\n",
    "\n",
    "def get_episode_categories(description):\n",
    "\n",
    "    # Define the list of categories\n",
    "    categories = ['Comedy', 'News', 'Politics', 'Science', 'Technology', \n",
    "    'Education', 'History', 'Business', 'Health', 'Personal development', \n",
    "    'True crime', 'Sports', 'Music', 'Literature', 'Travel', 'Society & Culture', \n",
    "    'Personal Journals', 'Relationships', 'Food', 'Art', 'Pop culture']\n",
    "\n",
    "    prompt = f'''The following is a list of podcast categories : {categories}.\n",
    "    Let's say I have a description like {description}.\n",
    "    Give me a list of podcast categories, comma-separated to which the description variable could fit.\n",
    "    Return an array of podcast categories.\n",
    "    '''\n",
    "\n",
    "    answer = openai.Completion.create(\n",
    "        model=\"text-davinci-003\",\n",
    "        prompt=prompt,\n",
    "        temperature=0,\n",
    "        max_tokens=1024,\n",
    "        top_p=1.0,\n",
    "        frequency_penalty=0.0,\n",
    "        presence_penalty=0.0\n",
    "    )['choices'][0]['text'].replace('\\n', '')\n",
    "    \n",
    "    return answer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# for episode in episodes:\n",
    "#     try:\n",
    "#         categories = get_episode_categories(episode['description'])\n",
    "#         episode['categories'] = categories.split(', ')\n",
    "#     except Exception:\n",
    "#         print(f\"Could not retrieve categories for episode '{episode['id']}'. Trying again in 30s.\")\n",
    "#         time.sleep(30)\n",
    "#         categories = get_episode_categories(episode['description'])\n",
    "#         episode['categories'] = categories.split(', ')\n",
    "\n",
    "#     time.sleep(2)\n",
    "#     print(f\"Enhancement of episode '{episode['id']}' done. Waiting 2 sec before retrieving categories from next episode.\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Clean Episodes dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Normalize json\n",
    "\n",
    "episodes_data = pd.json_normalize(episodes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Rearrange dataframe dataframe\n",
    "\n",
    "columns = ['show_id', 'id', 'name', 'release_date', 'languages', 'explicit', 'duration_ms']\n",
    "episodes_data = episodes_data.reindex(columns=columns)\n",
    "episodes_data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remove special characters from languages\n",
    "\n",
    "episodes_data['languages'] = episodes_data['languages'].astype(str).replace(\"[\\[\\'\\]]\", '', regex=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Unify languages\n",
    "\n",
    "def unify_episodes_languages(cell):\n",
    "    if cell in ['de', 'de-DE']:\n",
    "        return 'de'\n",
    "    elif cell in ['en', 'en-US']:\n",
    "        return 'us'\n",
    "    return 'unknown'\n",
    "\n",
    "episodes_data['languages'] = episodes_data['languages'].apply(unify_episodes_languages)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Convert release_date into datetime\n",
    "\n",
    "episodes_data['release_date'] = pd.to_datetime(episodes_data['release_date'], errors='coerce')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculate average difference between each episodes of a show\n",
    "\n",
    "episodes_data = episodes_data.sort_values(by='release_date', ascending=True)\n",
    "\n",
    "# Calculate the difference between each release date\n",
    "episodes_data['diff'] = episodes_data.groupby('show_id')['release_date'].diff().dt.days"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Display head\n",
    "\n",
    "episodes_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save episodes_data as csv file\n",
    "\n",
    "episodes_data.to_csv('data/csv/episodes_data.csv')"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Save Episodes Data in MySQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Insert the dataframe into shows database\n",
    "\n",
    "episodes_data.to_sql(name='episodes', con=engine, if_exists='append', index=False)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Display scraped Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load scraped country_market_share data\n",
    "\n",
    "country_market_data = pd.read_csv('scraped_data/country_market_share.csv')\n",
    "country_market_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load scraped device_type_market_share data\n",
    "\n",
    "device_type_market_data = pd.read_csv('scraped_data/device_type_market_share.csv')\n",
    "device_type_market_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load scraped devices_market_share data\n",
    "\n",
    "device_market_data = pd.read_csv('scraped_data/devices_market_share.csv')\n",
    "device_market_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load scraped platform_usage data\n",
    "\n",
    "platform_usage_data = pd.read_csv('scraped_data/platform_usage.csv')\n",
    "platform_usage_data.head()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "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.9.13"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "40d3a090f54c6569ab1632332b64b2c03c39dcf918b08424e98f38b5ae0af88f"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}