{ "cells": [ { "cell_type": "markdown", "id": "95e5132d-1e38-47bc-8558-f3210681713f", "metadata": {}, "source": [ "### Finding similar artists using content-based approach\n", "----" ] }, { "cell_type": "markdown", "id": "82cc747e-fc71-4918-a65e-78c07da8c87f", "metadata": {}, "source": [ "Load modules and helper functions" ] }, { "cell_type": "code", "execution_count": 48, "id": "f8d2f9c0-7b75-4cd0-a76d-c96a1043d7b0", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "The autoreload extension is already loaded. To reload it, use:\n", " %reload_ext autoreload\n" ] } ], "source": [ "%load_ext autoreload\n", "%autoreload 2" ] }, { "cell_type": "code", "execution_count": 49, "id": "120e34b8-abfd-4e2e-bd0d-3cde2bb9dc6b", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "INFO: Pandarallel will run on 10 workers.\n", "INFO: Pandarallel will use standard multiprocessing data transfer (pipe) to transfer data between the main process and workers.\n" ] } ], "source": [ "from snowflake.connector.pandas_tools import write_pandas\n", "import numpy as np\n", "import pandas as pd\n", "import datetime as dt\n", "\n", "from tqdm.auto import tqdm\n", "tqdm.pandas()\n", "\n", "from pandarallel import pandarallel\n", "pandarallel.initialize(progress_bar=False)\n", "\n", "\n", "# enable multiple outputs from single cell\n", "from IPython.core.interactiveshell import InteractiveShell\n", "InteractiveShell.ast_node_interactivity = \"all\"" ] }, { "cell_type": "code", "execution_count": 50, "id": "80d31764-d1f2-405c-a883-ac4025875bb7", "metadata": {}, "outputs": [], "source": [ "# Function for cleaning text\n", "from utils.functions import *" ] }, { "cell_type": "markdown", "id": "bcbbfbf9-3de9-4bd4-82d0-522214965dbd", "metadata": {}, "source": [ "TODO: consider Spotify audio features" ] }, { "cell_type": "markdown", "id": "5a5c5b87-2442-4877-9081-9292e3dfe8a0", "metadata": {}, "source": [ "----" ] }, { "cell_type": "code", "execution_count": 51, "id": "b90ec631-be4b-4687-99ce-80480cc9fdd3", "metadata": {}, "outputs": [], "source": [ "# initiate connection\n", "from utils.snowflake_connection import * # check the content of this file to match your profile\n", "ctx, cur = snowflake_key_pair_connect()" ] }, { "cell_type": "markdown", "id": "9485e6db-e387-428d-af4e-97c628b6cc57", "metadata": {}, "source": [ "#### QUERY SPOTIFY ARTIST RELATED GENRES AND OTHER METADATA USING CHARTMETRIC DATA" ] }, { "cell_type": "code", "execution_count": 52, "id": "77da1bb1-62f5-4e0d-9ed5-79ffdca5d002", "metadata": {}, "outputs": [], "source": [ "with open('queries/query_spotify_genres.txt') as f: # change the query file to query more fans\n", " query_spotify_genres = f.read()\n", "f.close()" ] }, { "cell_type": "code", "execution_count": 53, "id": "b6233101-3b5e-4cc7-b4e3-3fd9ab352bf6", "metadata": {}, "outputs": [ { "ename": "KeyboardInterrupt", "evalue": "", "output_type": "error", "traceback": [ "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m", "\u001b[0;31mKeyboardInterrupt\u001b[0m Traceback (most recent call last)", "Input \u001b[0;32mIn [53]\u001b[0m, in \u001b[0;36m\u001b[0;34m()\u001b[0m\n\u001b[1;32m 1\u001b[0m start \u001b[38;5;241m=\u001b[39m dt\u001b[38;5;241m.\u001b[39mdatetime\u001b[38;5;241m.\u001b[39mnow()\n\u001b[0;32m----> 3\u001b[0m \u001b[43mcur\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mexecute\u001b[49m\u001b[43m(\u001b[49m\u001b[43mquery_spotify_genres\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m 4\u001b[0m spotify_genres \u001b[38;5;241m=\u001b[39m cur\u001b[38;5;241m.\u001b[39mfetch_pandas_all()\n\u001b[1;32m 5\u001b[0m \u001b[38;5;66;03m# spotify_genres.dtypes\u001b[39;00m\n\u001b[1;32m 6\u001b[0m \u001b[38;5;66;03m# spotify_genres.info(memory_usage=\"deep\")\u001b[39;00m\n", "File \u001b[0;32m/opt/homebrew/anaconda3/envs/kdnugget_recommender/lib/python3.8/site-packages/snowflake/connector/cursor.py:711\u001b[0m, in \u001b[0;36mSnowflakeCursor.execute\u001b[0;34m(self, command, params, _bind_stage, timeout, _exec_async, _no_retry, _do_reset, _put_callback, _put_azure_callback, _put_callback_output_stream, _get_callback, _get_azure_callback, _get_callback_output_stream, _show_progress_bar, _statement_params, _is_internal, _describe_only, _no_results, _is_put_get, _raise_put_get_error, _force_put_overwrite, file_stream)\u001b[0m\n\u001b[1;32m 709\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m logger\u001b[38;5;241m.\u001b[39mgetEffectiveLevel() \u001b[38;5;241m<\u001b[39m\u001b[38;5;241m=\u001b[39m logging\u001b[38;5;241m.\u001b[39mINFO:\n\u001b[1;32m 710\u001b[0m logger\u001b[38;5;241m.\u001b[39minfo(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mquery: [\u001b[39m\u001b[38;5;132;01m%s\u001b[39;00m\u001b[38;5;124m]\u001b[39m\u001b[38;5;124m\"\u001b[39m, \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_format_query_for_log(query))\n\u001b[0;32m--> 711\u001b[0m ret \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_execute_helper\u001b[49m\u001b[43m(\u001b[49m\u001b[43mquery\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43mkwargs\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m 712\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_sfqid \u001b[38;5;241m=\u001b[39m (\n\u001b[1;32m 713\u001b[0m ret[\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mdata\u001b[39m\u001b[38;5;124m\"\u001b[39m][\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mqueryId\u001b[39m\u001b[38;5;124m\"\u001b[39m]\n\u001b[1;32m 714\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mdata\u001b[39m\u001b[38;5;124m\"\u001b[39m \u001b[38;5;129;01min\u001b[39;00m ret \u001b[38;5;129;01mand\u001b[39;00m \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mqueryId\u001b[39m\u001b[38;5;124m\"\u001b[39m \u001b[38;5;129;01min\u001b[39;00m ret[\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mdata\u001b[39m\u001b[38;5;124m\"\u001b[39m]\n\u001b[1;32m 715\u001b[0m \u001b[38;5;28;01melse\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m\n\u001b[1;32m 716\u001b[0m )\n\u001b[1;32m 717\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_sqlstate \u001b[38;5;241m=\u001b[39m (\n\u001b[1;32m 718\u001b[0m ret[\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mdata\u001b[39m\u001b[38;5;124m\"\u001b[39m][\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124msqlState\u001b[39m\u001b[38;5;124m\"\u001b[39m]\n\u001b[1;32m 719\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mdata\u001b[39m\u001b[38;5;124m\"\u001b[39m \u001b[38;5;129;01min\u001b[39;00m ret \u001b[38;5;129;01mand\u001b[39;00m \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124msqlState\u001b[39m\u001b[38;5;124m\"\u001b[39m \u001b[38;5;129;01min\u001b[39;00m ret[\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mdata\u001b[39m\u001b[38;5;124m\"\u001b[39m]\n\u001b[1;32m 720\u001b[0m \u001b[38;5;28;01melse\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m\n\u001b[1;32m 721\u001b[0m )\n", "File \u001b[0;32m/opt/homebrew/anaconda3/envs/kdnugget_recommender/lib/python3.8/site-packages/snowflake/connector/cursor.py:511\u001b[0m, in \u001b[0;36mSnowflakeCursor._execute_helper\u001b[0;34m(self, query, timeout, statement_params, binding_params, binding_stage, is_internal, describe_only, _no_results, _is_put_get, _no_retry)\u001b[0m\n\u001b[1;32m 509\u001b[0m ret \u001b[38;5;241m=\u001b[39m {\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mdata\u001b[39m\u001b[38;5;124m\"\u001b[39m: {}}\n\u001b[1;32m 510\u001b[0m \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[0;32m--> 511\u001b[0m ret \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_connection\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mcmd_query\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m 512\u001b[0m \u001b[43m \u001b[49m\u001b[43mquery\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 513\u001b[0m \u001b[43m \u001b[49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_sequence_counter\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 514\u001b[0m \u001b[43m \u001b[49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_request_id\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 515\u001b[0m \u001b[43m \u001b[49m\u001b[43mbinding_params\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mbinding_params\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 516\u001b[0m \u001b[43m \u001b[49m\u001b[43mbinding_stage\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mbinding_stage\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 517\u001b[0m \u001b[43m \u001b[49m\u001b[43mis_file_transfer\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;28;43mbool\u001b[39;49m\u001b[43m(\u001b[49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_is_file_transfer\u001b[49m\u001b[43m)\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 518\u001b[0m \u001b[43m \u001b[49m\u001b[43mstatement_params\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mstatement_params\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 519\u001b[0m \u001b[43m \u001b[49m\u001b[43mis_internal\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mis_internal\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 520\u001b[0m \u001b[43m \u001b[49m\u001b[43mdescribe_only\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mdescribe_only\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 521\u001b[0m \u001b[43m \u001b[49m\u001b[43m_no_results\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43m_no_results\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 522\u001b[0m \u001b[43m \u001b[49m\u001b[43m_no_retry\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43m_no_retry\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 523\u001b[0m \u001b[43m \u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m 524\u001b[0m \u001b[38;5;28;01mfinally\u001b[39;00m:\n\u001b[1;32m 525\u001b[0m \u001b[38;5;28;01mtry\u001b[39;00m:\n", "File \u001b[0;32m/opt/homebrew/anaconda3/envs/kdnugget_recommender/lib/python3.8/site-packages/snowflake/connector/connection.py:954\u001b[0m, in \u001b[0;36mSnowflakeConnection.cmd_query\u001b[0;34m(self, sql, sequence_counter, request_id, binding_params, binding_stage, is_file_transfer, statement_params, is_internal, describe_only, _no_results, _update_current_object, _no_retry)\u001b[0m\n\u001b[1;32m 945\u001b[0m logger\u001b[38;5;241m.\u001b[39mdebug(\n\u001b[1;32m 946\u001b[0m \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124msql=[\u001b[39m\u001b[38;5;132;01m%s\u001b[39;00m\u001b[38;5;124m], sequence_id=[\u001b[39m\u001b[38;5;132;01m%s\u001b[39;00m\u001b[38;5;124m], is_file_transfer=[\u001b[39m\u001b[38;5;132;01m%s\u001b[39;00m\u001b[38;5;124m]\u001b[39m\u001b[38;5;124m\"\u001b[39m,\n\u001b[1;32m 947\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_format_query_for_log(data[\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124msqlText\u001b[39m\u001b[38;5;124m\"\u001b[39m]),\n\u001b[1;32m 948\u001b[0m data[\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124msequenceId\u001b[39m\u001b[38;5;124m\"\u001b[39m],\n\u001b[1;32m 949\u001b[0m is_file_transfer,\n\u001b[1;32m 950\u001b[0m )\n\u001b[1;32m 952\u001b[0m url_parameters \u001b[38;5;241m=\u001b[39m {REQUEST_ID: request_id}\n\u001b[0;32m--> 954\u001b[0m ret \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mrest\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mrequest\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m 955\u001b[0m \u001b[43m \u001b[49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[38;5;124;43m/queries/v1/query-request?\u001b[39;49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[43m \u001b[49m\u001b[38;5;241;43m+\u001b[39;49m\u001b[43m \u001b[49m\u001b[43murlencode\u001b[49m\u001b[43m(\u001b[49m\u001b[43murl_parameters\u001b[49m\u001b[43m)\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 956\u001b[0m \u001b[43m \u001b[49m\u001b[43mdata\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 957\u001b[0m \u001b[43m \u001b[49m\u001b[43mclient\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mclient\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 958\u001b[0m \u001b[43m \u001b[49m\u001b[43m_no_results\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43m_no_results\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 959\u001b[0m \u001b[43m \u001b[49m\u001b[43m_include_retry_params\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;28;43;01mTrue\u001b[39;49;00m\u001b[43m,\u001b[49m\n\u001b[1;32m 960\u001b[0m \u001b[43m \u001b[49m\u001b[43m_no_retry\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43m_no_retry\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 961\u001b[0m \u001b[43m\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m 963\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m ret \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m:\n\u001b[1;32m 964\u001b[0m ret \u001b[38;5;241m=\u001b[39m {\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mdata\u001b[39m\u001b[38;5;124m\"\u001b[39m: {}}\n", "File \u001b[0;32m/opt/homebrew/anaconda3/envs/kdnugget_recommender/lib/python3.8/site-packages/snowflake/connector/network.py:466\u001b[0m, in \u001b[0;36mSnowflakeRestful.request\u001b[0;34m(self, url, body, method, client, _no_results, timeout, _include_retry_params, _no_retry)\u001b[0m\n\u001b[1;32m 464\u001b[0m headers[HTTP_HEADER_SERVICE_NAME] \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_connection\u001b[38;5;241m.\u001b[39mservice_name\n\u001b[1;32m 465\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m method \u001b[38;5;241m==\u001b[39m \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mpost\u001b[39m\u001b[38;5;124m\"\u001b[39m:\n\u001b[0;32m--> 466\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_post_request\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m 467\u001b[0m \u001b[43m \u001b[49m\u001b[43murl\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 468\u001b[0m \u001b[43m \u001b[49m\u001b[43mheaders\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 469\u001b[0m \u001b[43m \u001b[49m\u001b[43mjson\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mdumps\u001b[49m\u001b[43m(\u001b[49m\u001b[43mbody\u001b[49m\u001b[43m)\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 470\u001b[0m \u001b[43m \u001b[49m\u001b[43mtoken\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mtoken\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 471\u001b[0m \u001b[43m \u001b[49m\u001b[43m_no_results\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43m_no_results\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 472\u001b[0m \u001b[43m \u001b[49m\u001b[43mtimeout\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mtimeout\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 473\u001b[0m \u001b[43m \u001b[49m\u001b[43m_include_retry_params\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43m_include_retry_params\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 474\u001b[0m \u001b[43m \u001b[49m\u001b[43mno_retry\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43m_no_retry\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 475\u001b[0m \u001b[43m \u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m 476\u001b[0m \u001b[38;5;28;01melse\u001b[39;00m:\n\u001b[1;32m 477\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_get_request(url, headers, token\u001b[38;5;241m=\u001b[39m\u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mtoken, timeout\u001b[38;5;241m=\u001b[39mtimeout)\n", "File \u001b[0;32m/opt/homebrew/anaconda3/envs/kdnugget_recommender/lib/python3.8/site-packages/snowflake/connector/network.py:705\u001b[0m, in \u001b[0;36mSnowflakeRestful._post_request\u001b[0;34m(self, url, headers, body, token, timeout, _no_results, no_retry, socket_timeout, _include_retry_params)\u001b[0m\n\u001b[1;32m 702\u001b[0m ret \u001b[38;5;241m=\u001b[39m probe_connection(full_url)\n\u001b[1;32m 703\u001b[0m pprint(ret)\n\u001b[0;32m--> 705\u001b[0m ret \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mfetch\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m 706\u001b[0m \u001b[43m \u001b[49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[38;5;124;43mpost\u001b[39;49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[43m,\u001b[49m\n\u001b[1;32m 707\u001b[0m \u001b[43m \u001b[49m\u001b[43mfull_url\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 708\u001b[0m \u001b[43m \u001b[49m\u001b[43mheaders\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 709\u001b[0m \u001b[43m \u001b[49m\u001b[43mdata\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mbody\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 710\u001b[0m \u001b[43m \u001b[49m\u001b[43mtimeout\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mtimeout\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 711\u001b[0m \u001b[43m \u001b[49m\u001b[43mtoken\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mtoken\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 712\u001b[0m \u001b[43m \u001b[49m\u001b[43mno_retry\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mno_retry\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 713\u001b[0m \u001b[43m \u001b[49m\u001b[43msocket_timeout\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43msocket_timeout\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 714\u001b[0m \u001b[43m \u001b[49m\u001b[43m_include_retry_params\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43m_include_retry_params\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 715\u001b[0m \u001b[43m\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m 716\u001b[0m logger\u001b[38;5;241m.\u001b[39mdebug(\n\u001b[1;32m 717\u001b[0m \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mret[code] = \u001b[39m\u001b[38;5;132;01m{code}\u001b[39;00m\u001b[38;5;124m, after post request\u001b[39m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;241m.\u001b[39mformat(\n\u001b[1;32m 718\u001b[0m code\u001b[38;5;241m=\u001b[39m(ret\u001b[38;5;241m.\u001b[39mget(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mcode\u001b[39m\u001b[38;5;124m\"\u001b[39m, \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mN/A\u001b[39m\u001b[38;5;124m\"\u001b[39m))\n\u001b[1;32m 719\u001b[0m )\n\u001b[1;32m 720\u001b[0m )\n\u001b[1;32m 722\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m ret\u001b[38;5;241m.\u001b[39mget(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mcode\u001b[39m\u001b[38;5;124m\"\u001b[39m) \u001b[38;5;241m==\u001b[39m SESSION_EXPIRED_GS_CODE:\n", "File \u001b[0;32m/opt/homebrew/anaconda3/envs/kdnugget_recommender/lib/python3.8/site-packages/snowflake/connector/network.py:795\u001b[0m, in \u001b[0;36mSnowflakeRestful.fetch\u001b[0;34m(self, method, full_url, headers, data, timeout, **kwargs)\u001b[0m\n\u001b[1;32m 793\u001b[0m retry_ctx \u001b[38;5;241m=\u001b[39m RetryCtx(timeout, include_retry_params)\n\u001b[1;32m 794\u001b[0m \u001b[38;5;28;01mwhile\u001b[39;00m \u001b[38;5;28;01mTrue\u001b[39;00m:\n\u001b[0;32m--> 795\u001b[0m ret \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_request_exec_wrapper\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m 796\u001b[0m \u001b[43m \u001b[49m\u001b[43msession\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mmethod\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mfull_url\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mheaders\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mdata\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mretry_ctx\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43mkwargs\u001b[49m\n\u001b[1;32m 797\u001b[0m \u001b[43m \u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m 798\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m ret \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m:\n\u001b[1;32m 799\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m ret\n", "File \u001b[0;32m/opt/homebrew/anaconda3/envs/kdnugget_recommender/lib/python3.8/site-packages/snowflake/connector/network.py:838\u001b[0m, in \u001b[0;36mSnowflakeRestful._request_exec_wrapper\u001b[0;34m(self, session, method, full_url, headers, data, retry_ctx, no_retry, token, **kwargs)\u001b[0m\n\u001b[1;32m 836\u001b[0m full_url \u001b[38;5;241m=\u001b[39m SnowflakeRestful\u001b[38;5;241m.\u001b[39madd_request_guid(full_url)\n\u001b[1;32m 837\u001b[0m \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[0;32m--> 838\u001b[0m return_object \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_request_exec\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m 839\u001b[0m \u001b[43m \u001b[49m\u001b[43msession\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43msession\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 840\u001b[0m \u001b[43m \u001b[49m\u001b[43mmethod\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mmethod\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 841\u001b[0m \u001b[43m \u001b[49m\u001b[43mfull_url\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mfull_url\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 842\u001b[0m \u001b[43m \u001b[49m\u001b[43mheaders\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mheaders\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 843\u001b[0m \u001b[43m \u001b[49m\u001b[43mdata\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mdata\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 844\u001b[0m \u001b[43m \u001b[49m\u001b[43mtoken\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mtoken\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 845\u001b[0m \u001b[43m \u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43mkwargs\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 846\u001b[0m \u001b[43m \u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m 847\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m return_object \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m:\n\u001b[1;32m 848\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m return_object\n", "File \u001b[0;32m/opt/homebrew/anaconda3/envs/kdnugget_recommender/lib/python3.8/site-packages/snowflake/connector/network.py:1009\u001b[0m, in \u001b[0;36mSnowflakeRestful._request_exec\u001b[0;34m(self, session, method, full_url, headers, data, token, catch_okta_unauthorized_error, is_raw_text, is_raw_binary, binary_data_handler, socket_timeout)\u001b[0m\n\u001b[1;32m 1005\u001b[0m download_start_time \u001b[38;5;241m=\u001b[39m get_time_millis()\n\u001b[1;32m 1006\u001b[0m \u001b[38;5;66;03m# socket timeout is constant. You should be able to receive\u001b[39;00m\n\u001b[1;32m 1007\u001b[0m \u001b[38;5;66;03m# the response within the time. If not, ConnectReadTimeout or\u001b[39;00m\n\u001b[1;32m 1008\u001b[0m \u001b[38;5;66;03m# ReadTimeout is raised.\u001b[39;00m\n\u001b[0;32m-> 1009\u001b[0m raw_ret \u001b[38;5;241m=\u001b[39m \u001b[43msession\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mrequest\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m 1010\u001b[0m \u001b[43m \u001b[49m\u001b[43mmethod\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mmethod\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 1011\u001b[0m \u001b[43m \u001b[49m\u001b[43murl\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mfull_url\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 1012\u001b[0m \u001b[43m \u001b[49m\u001b[43mheaders\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mheaders\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 1013\u001b[0m \u001b[43m \u001b[49m\u001b[43mdata\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43minput_data\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 1014\u001b[0m \u001b[43m \u001b[49m\u001b[43mtimeout\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43msocket_timeout\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 1015\u001b[0m \u001b[43m \u001b[49m\u001b[43mverify\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;28;43;01mTrue\u001b[39;49;00m\u001b[43m,\u001b[49m\n\u001b[1;32m 1016\u001b[0m \u001b[43m \u001b[49m\u001b[43mstream\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mis_raw_binary\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 1017\u001b[0m \u001b[43m \u001b[49m\u001b[43mauth\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mSnowflakeAuth\u001b[49m\u001b[43m(\u001b[49m\u001b[43mtoken\u001b[49m\u001b[43m)\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 1018\u001b[0m \u001b[43m\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m 1019\u001b[0m download_end_time \u001b[38;5;241m=\u001b[39m get_time_millis()\n\u001b[1;32m 1021\u001b[0m \u001b[38;5;28;01mtry\u001b[39;00m:\n", "File \u001b[0;32m/opt/homebrew/anaconda3/envs/kdnugget_recommender/lib/python3.8/site-packages/snowflake/connector/vendored/requests/sessions.py:542\u001b[0m, in \u001b[0;36mSession.request\u001b[0;34m(self, method, url, params, data, headers, cookies, files, auth, timeout, allow_redirects, proxies, hooks, stream, verify, cert, json)\u001b[0m\n\u001b[1;32m 537\u001b[0m send_kwargs \u001b[38;5;241m=\u001b[39m {\n\u001b[1;32m 538\u001b[0m \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mtimeout\u001b[39m\u001b[38;5;124m'\u001b[39m: timeout,\n\u001b[1;32m 539\u001b[0m \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mallow_redirects\u001b[39m\u001b[38;5;124m'\u001b[39m: allow_redirects,\n\u001b[1;32m 540\u001b[0m }\n\u001b[1;32m 541\u001b[0m send_kwargs\u001b[38;5;241m.\u001b[39mupdate(settings)\n\u001b[0;32m--> 542\u001b[0m resp \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43msend\u001b[49m\u001b[43m(\u001b[49m\u001b[43mprep\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43msend_kwargs\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m 544\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m resp\n", "File \u001b[0;32m/opt/homebrew/anaconda3/envs/kdnugget_recommender/lib/python3.8/site-packages/snowflake/connector/vendored/requests/sessions.py:655\u001b[0m, in \u001b[0;36mSession.send\u001b[0;34m(self, request, **kwargs)\u001b[0m\n\u001b[1;32m 652\u001b[0m start \u001b[38;5;241m=\u001b[39m preferred_clock()\n\u001b[1;32m 654\u001b[0m \u001b[38;5;66;03m# Send the request\u001b[39;00m\n\u001b[0;32m--> 655\u001b[0m r \u001b[38;5;241m=\u001b[39m \u001b[43madapter\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43msend\u001b[49m\u001b[43m(\u001b[49m\u001b[43mrequest\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[38;5;241;43m*\u001b[39;49m\u001b[43mkwargs\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m 657\u001b[0m \u001b[38;5;66;03m# Total elapsed time of the request (approximately)\u001b[39;00m\n\u001b[1;32m 658\u001b[0m elapsed \u001b[38;5;241m=\u001b[39m preferred_clock() \u001b[38;5;241m-\u001b[39m start\n", "File \u001b[0;32m/opt/homebrew/anaconda3/envs/kdnugget_recommender/lib/python3.8/site-packages/snowflake/connector/vendored/requests/adapters.py:439\u001b[0m, in \u001b[0;36mHTTPAdapter.send\u001b[0;34m(self, request, stream, timeout, verify, cert, proxies)\u001b[0m\n\u001b[1;32m 437\u001b[0m \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[1;32m 438\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m chunked:\n\u001b[0;32m--> 439\u001b[0m resp \u001b[38;5;241m=\u001b[39m \u001b[43mconn\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43murlopen\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m 440\u001b[0m \u001b[43m \u001b[49m\u001b[43mmethod\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mrequest\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mmethod\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 441\u001b[0m \u001b[43m \u001b[49m\u001b[43murl\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43murl\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 442\u001b[0m \u001b[43m \u001b[49m\u001b[43mbody\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mrequest\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mbody\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 443\u001b[0m \u001b[43m \u001b[49m\u001b[43mheaders\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mrequest\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mheaders\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 444\u001b[0m \u001b[43m \u001b[49m\u001b[43mredirect\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;28;43;01mFalse\u001b[39;49;00m\u001b[43m,\u001b[49m\n\u001b[1;32m 445\u001b[0m \u001b[43m \u001b[49m\u001b[43massert_same_host\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;28;43;01mFalse\u001b[39;49;00m\u001b[43m,\u001b[49m\n\u001b[1;32m 446\u001b[0m \u001b[43m \u001b[49m\u001b[43mpreload_content\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;28;43;01mFalse\u001b[39;49;00m\u001b[43m,\u001b[49m\n\u001b[1;32m 447\u001b[0m \u001b[43m \u001b[49m\u001b[43mdecode_content\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;28;43;01mFalse\u001b[39;49;00m\u001b[43m,\u001b[49m\n\u001b[1;32m 448\u001b[0m \u001b[43m \u001b[49m\u001b[43mretries\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mmax_retries\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 449\u001b[0m \u001b[43m \u001b[49m\u001b[43mtimeout\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mtimeout\u001b[49m\n\u001b[1;32m 450\u001b[0m \u001b[43m \u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m 452\u001b[0m \u001b[38;5;66;03m# Send the request.\u001b[39;00m\n\u001b[1;32m 453\u001b[0m \u001b[38;5;28;01melse\u001b[39;00m:\n\u001b[1;32m 454\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mhasattr\u001b[39m(conn, \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mproxy_pool\u001b[39m\u001b[38;5;124m'\u001b[39m):\n", "File \u001b[0;32m/opt/homebrew/anaconda3/envs/kdnugget_recommender/lib/python3.8/site-packages/snowflake/connector/vendored/urllib3/connectionpool.py:699\u001b[0m, in \u001b[0;36mHTTPConnectionPool.urlopen\u001b[0;34m(self, method, url, body, headers, retries, redirect, assert_same_host, timeout, pool_timeout, release_conn, chunked, body_pos, **response_kw)\u001b[0m\n\u001b[1;32m 696\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_prepare_proxy(conn)\n\u001b[1;32m 698\u001b[0m \u001b[38;5;66;03m# Make the request on the httplib connection object.\u001b[39;00m\n\u001b[0;32m--> 699\u001b[0m httplib_response \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_make_request\u001b[49m\u001b[43m(\u001b[49m\n\u001b[1;32m 700\u001b[0m \u001b[43m \u001b[49m\u001b[43mconn\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 701\u001b[0m \u001b[43m \u001b[49m\u001b[43mmethod\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 702\u001b[0m \u001b[43m \u001b[49m\u001b[43murl\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 703\u001b[0m \u001b[43m \u001b[49m\u001b[43mtimeout\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mtimeout_obj\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 704\u001b[0m \u001b[43m \u001b[49m\u001b[43mbody\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mbody\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 705\u001b[0m \u001b[43m \u001b[49m\u001b[43mheaders\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mheaders\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 706\u001b[0m \u001b[43m \u001b[49m\u001b[43mchunked\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mchunked\u001b[49m\u001b[43m,\u001b[49m\n\u001b[1;32m 707\u001b[0m \u001b[43m\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m 709\u001b[0m \u001b[38;5;66;03m# If we're going to release the connection in ``finally:``, then\u001b[39;00m\n\u001b[1;32m 710\u001b[0m \u001b[38;5;66;03m# the response doesn't need to know about the connection. Otherwise\u001b[39;00m\n\u001b[1;32m 711\u001b[0m \u001b[38;5;66;03m# it will also try to release it and we'll have a double-release\u001b[39;00m\n\u001b[1;32m 712\u001b[0m \u001b[38;5;66;03m# mess.\u001b[39;00m\n\u001b[1;32m 713\u001b[0m response_conn \u001b[38;5;241m=\u001b[39m conn \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m release_conn \u001b[38;5;28;01melse\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m\n", "File \u001b[0;32m/opt/homebrew/anaconda3/envs/kdnugget_recommender/lib/python3.8/site-packages/snowflake/connector/vendored/urllib3/connectionpool.py:445\u001b[0m, in \u001b[0;36mHTTPConnectionPool._make_request\u001b[0;34m(self, conn, method, url, timeout, chunked, **httplib_request_kw)\u001b[0m\n\u001b[1;32m 440\u001b[0m httplib_response \u001b[38;5;241m=\u001b[39m conn\u001b[38;5;241m.\u001b[39mgetresponse()\n\u001b[1;32m 441\u001b[0m \u001b[38;5;28;01mexcept\u001b[39;00m \u001b[38;5;167;01mBaseException\u001b[39;00m \u001b[38;5;28;01mas\u001b[39;00m e:\n\u001b[1;32m 442\u001b[0m \u001b[38;5;66;03m# Remove the TypeError from the exception chain in\u001b[39;00m\n\u001b[1;32m 443\u001b[0m \u001b[38;5;66;03m# Python 3 (including for exceptions like SystemExit).\u001b[39;00m\n\u001b[1;32m 444\u001b[0m \u001b[38;5;66;03m# Otherwise it looks like a bug in the code.\u001b[39;00m\n\u001b[0;32m--> 445\u001b[0m \u001b[43msix\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mraise_from\u001b[49m\u001b[43m(\u001b[49m\u001b[43me\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;28;43;01mNone\u001b[39;49;00m\u001b[43m)\u001b[49m\n\u001b[1;32m 446\u001b[0m \u001b[38;5;28;01mexcept\u001b[39;00m (SocketTimeout, BaseSSLError, SocketError) \u001b[38;5;28;01mas\u001b[39;00m e:\n\u001b[1;32m 447\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_raise_timeout(err\u001b[38;5;241m=\u001b[39me, url\u001b[38;5;241m=\u001b[39murl, timeout_value\u001b[38;5;241m=\u001b[39mread_timeout)\n", "File \u001b[0;32m:3\u001b[0m, in \u001b[0;36mraise_from\u001b[0;34m(value, from_value)\u001b[0m\n", "File \u001b[0;32m/opt/homebrew/anaconda3/envs/kdnugget_recommender/lib/python3.8/site-packages/snowflake/connector/vendored/urllib3/connectionpool.py:440\u001b[0m, in \u001b[0;36mHTTPConnectionPool._make_request\u001b[0;34m(self, conn, method, url, timeout, chunked, **httplib_request_kw)\u001b[0m\n\u001b[1;32m 437\u001b[0m \u001b[38;5;28;01mexcept\u001b[39;00m \u001b[38;5;167;01mTypeError\u001b[39;00m:\n\u001b[1;32m 438\u001b[0m \u001b[38;5;66;03m# Python 3\u001b[39;00m\n\u001b[1;32m 439\u001b[0m \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[0;32m--> 440\u001b[0m httplib_response \u001b[38;5;241m=\u001b[39m \u001b[43mconn\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mgetresponse\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m 441\u001b[0m \u001b[38;5;28;01mexcept\u001b[39;00m \u001b[38;5;167;01mBaseException\u001b[39;00m \u001b[38;5;28;01mas\u001b[39;00m e:\n\u001b[1;32m 442\u001b[0m \u001b[38;5;66;03m# Remove the TypeError from the exception chain in\u001b[39;00m\n\u001b[1;32m 443\u001b[0m \u001b[38;5;66;03m# Python 3 (including for exceptions like SystemExit).\u001b[39;00m\n\u001b[1;32m 444\u001b[0m \u001b[38;5;66;03m# Otherwise it looks like a bug in the code.\u001b[39;00m\n\u001b[1;32m 445\u001b[0m six\u001b[38;5;241m.\u001b[39mraise_from(e, \u001b[38;5;28;01mNone\u001b[39;00m)\n", "File \u001b[0;32m/opt/homebrew/anaconda3/envs/kdnugget_recommender/lib/python3.8/http/client.py:1348\u001b[0m, in \u001b[0;36mHTTPConnection.getresponse\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m 1346\u001b[0m \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[1;32m 1347\u001b[0m \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[0;32m-> 1348\u001b[0m \u001b[43mresponse\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mbegin\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m 1349\u001b[0m \u001b[38;5;28;01mexcept\u001b[39;00m \u001b[38;5;167;01mConnectionError\u001b[39;00m:\n\u001b[1;32m 1350\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mclose()\n", "File \u001b[0;32m/opt/homebrew/anaconda3/envs/kdnugget_recommender/lib/python3.8/http/client.py:316\u001b[0m, in \u001b[0;36mHTTPResponse.begin\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m 314\u001b[0m \u001b[38;5;66;03m# read until we get a non-100 response\u001b[39;00m\n\u001b[1;32m 315\u001b[0m \u001b[38;5;28;01mwhile\u001b[39;00m \u001b[38;5;28;01mTrue\u001b[39;00m:\n\u001b[0;32m--> 316\u001b[0m version, status, reason \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_read_status\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m 317\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m status \u001b[38;5;241m!=\u001b[39m CONTINUE:\n\u001b[1;32m 318\u001b[0m \u001b[38;5;28;01mbreak\u001b[39;00m\n", "File \u001b[0;32m/opt/homebrew/anaconda3/envs/kdnugget_recommender/lib/python3.8/http/client.py:277\u001b[0m, in \u001b[0;36mHTTPResponse._read_status\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m 276\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21m_read_status\u001b[39m(\u001b[38;5;28mself\u001b[39m):\n\u001b[0;32m--> 277\u001b[0m line \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mstr\u001b[39m(\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mfp\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mreadline\u001b[49m\u001b[43m(\u001b[49m\u001b[43m_MAXLINE\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;241;43m+\u001b[39;49m\u001b[43m \u001b[49m\u001b[38;5;241;43m1\u001b[39;49m\u001b[43m)\u001b[49m, \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124miso-8859-1\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n\u001b[1;32m 278\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mlen\u001b[39m(line) \u001b[38;5;241m>\u001b[39m _MAXLINE:\n\u001b[1;32m 279\u001b[0m \u001b[38;5;28;01mraise\u001b[39;00m LineTooLong(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mstatus line\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n", "File \u001b[0;32m/opt/homebrew/anaconda3/envs/kdnugget_recommender/lib/python3.8/socket.py:669\u001b[0m, in \u001b[0;36mSocketIO.readinto\u001b[0;34m(self, b)\u001b[0m\n\u001b[1;32m 667\u001b[0m \u001b[38;5;28;01mwhile\u001b[39;00m \u001b[38;5;28;01mTrue\u001b[39;00m:\n\u001b[1;32m 668\u001b[0m \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[0;32m--> 669\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_sock\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mrecv_into\u001b[49m\u001b[43m(\u001b[49m\u001b[43mb\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m 670\u001b[0m \u001b[38;5;28;01mexcept\u001b[39;00m timeout:\n\u001b[1;32m 671\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_timeout_occurred \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;01mTrue\u001b[39;00m\n", "File \u001b[0;32m/opt/homebrew/anaconda3/envs/kdnugget_recommender/lib/python3.8/site-packages/snowflake/connector/vendored/urllib3/contrib/pyopenssl.py:331\u001b[0m, in \u001b[0;36mWrappedSocket.recv_into\u001b[0;34m(self, *args, **kwargs)\u001b[0m\n\u001b[1;32m 329\u001b[0m \u001b[38;5;28;01mraise\u001b[39;00m\n\u001b[1;32m 330\u001b[0m \u001b[38;5;28;01mexcept\u001b[39;00m OpenSSL\u001b[38;5;241m.\u001b[39mSSL\u001b[38;5;241m.\u001b[39mWantReadError:\n\u001b[0;32m--> 331\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[43mutil\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mwait_for_read\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43msocket\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43msocket\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mgettimeout\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\u001b[43m)\u001b[49m:\n\u001b[1;32m 332\u001b[0m \u001b[38;5;28;01mraise\u001b[39;00m timeout(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mThe read operation timed out\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n\u001b[1;32m 333\u001b[0m \u001b[38;5;28;01melse\u001b[39;00m:\n", "File \u001b[0;32m/opt/homebrew/anaconda3/envs/kdnugget_recommender/lib/python3.8/site-packages/snowflake/connector/vendored/urllib3/util/wait.py:146\u001b[0m, in \u001b[0;36mwait_for_read\u001b[0;34m(sock, timeout)\u001b[0m\n\u001b[1;32m 142\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21mwait_for_read\u001b[39m(sock, timeout\u001b[38;5;241m=\u001b[39m\u001b[38;5;28;01mNone\u001b[39;00m):\n\u001b[1;32m 143\u001b[0m \u001b[38;5;124;03m\"\"\"Waits for reading to be available on a given socket.\u001b[39;00m\n\u001b[1;32m 144\u001b[0m \u001b[38;5;124;03m Returns True if the socket is readable, or False if the timeout expired.\u001b[39;00m\n\u001b[1;32m 145\u001b[0m \u001b[38;5;124;03m \"\"\"\u001b[39;00m\n\u001b[0;32m--> 146\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[43mwait_for_socket\u001b[49m\u001b[43m(\u001b[49m\u001b[43msock\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mread\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;28;43;01mTrue\u001b[39;49;00m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mtimeout\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mtimeout\u001b[49m\u001b[43m)\u001b[49m\n", "File \u001b[0;32m/opt/homebrew/anaconda3/envs/kdnugget_recommender/lib/python3.8/site-packages/snowflake/connector/vendored/urllib3/util/wait.py:107\u001b[0m, in \u001b[0;36mpoll_wait_for_socket\u001b[0;34m(sock, read, write, timeout)\u001b[0m\n\u001b[1;32m 104\u001b[0m t \u001b[38;5;241m*\u001b[39m\u001b[38;5;241m=\u001b[39m \u001b[38;5;241m1000\u001b[39m\n\u001b[1;32m 105\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m poll_obj\u001b[38;5;241m.\u001b[39mpoll(t)\n\u001b[0;32m--> 107\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mbool\u001b[39m(\u001b[43m_retry_on_intr\u001b[49m\u001b[43m(\u001b[49m\u001b[43mdo_poll\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mtimeout\u001b[49m\u001b[43m)\u001b[49m)\n", "File \u001b[0;32m/opt/homebrew/anaconda3/envs/kdnugget_recommender/lib/python3.8/site-packages/snowflake/connector/vendored/urllib3/util/wait.py:43\u001b[0m, in \u001b[0;36m_retry_on_intr\u001b[0;34m(fn, timeout)\u001b[0m\n\u001b[1;32m 42\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21m_retry_on_intr\u001b[39m(fn, timeout):\n\u001b[0;32m---> 43\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[43mfn\u001b[49m\u001b[43m(\u001b[49m\u001b[43mtimeout\u001b[49m\u001b[43m)\u001b[49m\n", "File \u001b[0;32m/opt/homebrew/anaconda3/envs/kdnugget_recommender/lib/python3.8/site-packages/snowflake/connector/vendored/urllib3/util/wait.py:105\u001b[0m, in \u001b[0;36mpoll_wait_for_socket..do_poll\u001b[0;34m(t)\u001b[0m\n\u001b[1;32m 103\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m t \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m:\n\u001b[1;32m 104\u001b[0m t \u001b[38;5;241m*\u001b[39m\u001b[38;5;241m=\u001b[39m \u001b[38;5;241m1000\u001b[39m\n\u001b[0;32m--> 105\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[43mpoll_obj\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mpoll\u001b[49m\u001b[43m(\u001b[49m\u001b[43mt\u001b[49m\u001b[43m)\u001b[49m\n", "File \u001b[0;32m/opt/homebrew/anaconda3/envs/kdnugget_recommender/lib/python3.8/site-packages/snowflake/connector/cursor.py:500\u001b[0m, in \u001b[0;36mSnowflakeCursor._execute_helper..interrupt_handler\u001b[0;34m(*_)\u001b[0m\n\u001b[1;32m 497\u001b[0m \u001b[38;5;28;01mexcept\u001b[39;00m (\u001b[38;5;167;01mValueError\u001b[39;00m, \u001b[38;5;167;01mTypeError\u001b[39;00m):\n\u001b[1;32m 498\u001b[0m \u001b[38;5;66;03m# ignore failures\u001b[39;00m\n\u001b[1;32m 499\u001b[0m \u001b[38;5;28;01mpass\u001b[39;00m\n\u001b[0;32m--> 500\u001b[0m \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mKeyboardInterrupt\u001b[39;00m\n", "\u001b[0;31mKeyboardInterrupt\u001b[0m: " ] } ], "source": [ "start = dt.datetime.now()\n", "\n", "cur.execute(query_spotify_genres)\n", "spotify_genres = cur.fetch_pandas_all()\n", "# spotify_genres.dtypes\n", "# spotify_genres.info(memory_usage=\"deep\")\n", "spotify_genres.shape\n", "spotify_genres.head(2)\n", "\n", "end = dt.datetime.now()\n", "print(f\"Processing took {(end - start).total_seconds() } seconds.\")" ] }, { "cell_type": "code", "execution_count": null, "id": "d1468ea0-7b13-4397-aa34-9fbaae8cd137", "metadata": {}, "outputs": [], "source": [ "# upload list of artist to DB for usage in other SQL queries\n", "start = dt.datetime.now()\n", "\n", "cur.execute(\n", " \"CREATE OR REPLACE TABLE \"\n", " \"DEV_ENGINEERING.RBOMBERG_DBT.SPOTIFY_ARTISTS(SPOTIFY_ARTIST_ID string)\")\n", "\n", "# create unique df of\n", "write_pandas(ctx, spotify_genres[['SPOTIFY_ARTIST_ID']], \n", " 'DEV_ENGINEERING.RBOMBERG_DBT.spotify_artists', quote_identifiers=False)\n", "\n", "end = dt.datetime.now()\n", "print(f\"Processing took {(end - start).total_seconds() } seconds.\")" ] }, { "cell_type": "markdown", "id": "e730995c-77ac-467a-9f42-1bcd6d581b6d", "metadata": {}, "source": [ "Clean genres data" ] }, { "cell_type": "markdown", "id": "3b3afb03-33da-4ebe-b0ee-c9440515c8f1", "metadata": {}, "source": [ "We use pandarallel to decrease time from 24 seconds to 12 seconds- 50% time win" ] }, { "cell_type": "code", "execution_count": null, "id": "462b0d9c-49ab-432c-8282-ab27e5f34d35", "metadata": {}, "outputs": [], "source": [ "start = dt.datetime.now()\n", "\n", "# Applying functions to unify genres format\n", "spotify_genres['CLEANED_GENRES'] = spotify_genres['GENRES'].parallel_apply(make_lower_case)\n", "spotify_genres['CLEANED_GENRES'] = spotify_genres.CLEANED_GENRES.parallel_apply(remove_forward_slash)\n", "spotify_genres['CLEANED_GENRES'] = spotify_genres.CLEANED_GENRES.parallel_apply(remove_and_sign)\n", "spotify_genres['CLEANED_GENRES'] = spotify_genres.CLEANED_GENRES.parallel_apply(remove_dash_sign)\n", "spotify_genres.head(2)\n", "spotify_genres.tail(2)\n", "spotify_genres.shape\n", "\n", "end = dt.datetime.now()\n", "print(f\"Processing took {(end - start).total_seconds() } seconds.\")" ] }, { "cell_type": "code", "execution_count": null, "id": "f43b8bac-f6aa-4fc7-921b-3063d01c6873", "metadata": {}, "outputs": [], "source": [ "# check if some anomalies are still present\n", "anomalies = spotify_genres[spotify_genres['CLEANED_GENRES'].str.contains(',')][['CLEANED_GENRES']]\n", "try:\n", " assert anomalies[anomalies['CLEANED_GENRES'].str.contains('\"')].shape[0] == 0\n", "except:\n", " print('Check for some anomalies:')\n", " anomalies[anomalies['CLEANED_GENRES'].str.contains('\"')]" ] }, { "cell_type": "markdown", "id": "4ffb573a-824a-4871-b028-20cb02bbe1a9", "metadata": {}, "source": [ "Clean pronoun" ] }, { "cell_type": "code", "execution_count": null, "id": "f559d80b-7540-447f-82ce-f49dcce43b0b", "metadata": {}, "outputs": [], "source": [ "start = dt.datetime.now()\n", "\n", "spotify_genres['CLEANED_PRONOUN'] = spotify_genres['PRONOUN'].parallel_apply(make_lower_case)\n", "spotify_genres['CLEANED_PRONOUN'] = np.where(spotify_genres['CLEANED_PRONOUN'].\\\n", " str.contains('she'), 'female', spotify_genres['CLEANED_PRONOUN'])\n", "spotify_genres['CLEANED_PRONOUN'] = np.where(spotify_genres['CLEANED_PRONOUN'].\\\n", " str.contains('her'), 'female', spotify_genres['CLEANED_PRONOUN'])\n", "spotify_genres['CLEANED_PRONOUN'] = np.where(spotify_genres['CLEANED_PRONOUN'].\\\n", " str.contains('they'), 'multi', spotify_genres['CLEANED_PRONOUN'])\n", "spotify_genres['CLEANED_PRONOUN'] = np.where(spotify_genres['CLEANED_PRONOUN'].\\\n", " str.contains('them'), 'multi', spotify_genres['CLEANED_PRONOUN'])\n", "spotify_genres['CLEANED_PRONOUN'] = np.where(spotify_genres['CLEANED_PRONOUN'].\\\n", " str.contains('he'), 'male', spotify_genres['CLEANED_PRONOUN'])\n", "spotify_genres['CLEANED_PRONOUN'] = np.where(spotify_genres['CLEANED_PRONOUN'].\\\n", " str.contains('him'), 'male', spotify_genres['CLEANED_PRONOUN'])\n", "spotify_genres['CLEANED_PRONOUN'] = np.where(spotify_genres['CLEANED_PRONOUN'].\\\n", " isin(['female', 'multi', 'male']), spotify_genres['CLEANED_PRONOUN'], 'other_pronoun')\n", "spotify_genres['CLEANED_PRONOUN'] = spotify_genres['CLEANED_PRONOUN'].astype('category')\n", "\n", "end = dt.datetime.now()\n", "print(f\"Processing took {(end - start).total_seconds() } seconds.\")\n", "spotify_genres.dtypes\n", "spotify_genres['CLEANED_PRONOUN'].value_counts()" ] }, { "cell_type": "code", "execution_count": null, "id": "9e2439dd-505f-489d-8d66-1c8abd204d67", "metadata": {}, "outputs": [], "source": [ "# understand different combinations\n", "spotify_genres.groupby(['CLEANED_PRONOUN', 'BAND'])['SPOTIFY_ARTIST_ID'].count().reset_index()" ] }, { "cell_type": "markdown", "id": "c7e4d762-edbe-4f40-b8b1-8f23637eb30b", "metadata": {}, "source": [ "Clean gender
\n", "0 female
\n", "1 male" ] }, { "cell_type": "code", "execution_count": null, "id": "33a9b465-f5fc-42b0-98d4-c25730627986", "metadata": {}, "outputs": [], "source": [ "spotify_genres['GENDER'].value_counts()" ] }, { "cell_type": "code", "execution_count": null, "id": "7a76479e-5734-45d2-8cfd-16cc7c75145c", "metadata": {}, "outputs": [], "source": [ "gender_mapping={\n", "1.0 :'gender_male',\n", "0.0 :'gender_female',\n", "}\n", "\n", "spotify_genres['CLEANED_GENDER'] = spotify_genres['GENDER'].map(lambda x: gender_mapping.get(x, 'gender_unknown'))\n", "spotify_genres['CLEANED_GENDER'] = spotify_genres['CLEANED_GENDER'].astype('category')\n", "spotify_genres['CLEANED_GENDER'].value_counts()" ] }, { "cell_type": "markdown", "id": "e4fc98d3-c847-4475-b678-903bbdbe0dd3", "metadata": {}, "source": [ "Clean band" ] }, { "cell_type": "code", "execution_count": null, "id": "e0ff8523-15b7-466e-b7bd-a624fb5986a7", "metadata": {}, "outputs": [], "source": [ "start = dt.datetime.now()\n", "\n", "spotify_genres['BAND'].value_counts(dropna=False)\n", "spotify_genres['BAND'] = np.where(spotify_genres['BAND'].isna(), 'band_unknown',spotify_genres['BAND'])\n", "\n", "# convert these 2 records\n", "band_mapping={\n", "'band_unknown': 'band_unknown',\n", "'False': 'not_band', \n", "'True': 'a_band', \n", "}\n", "spotify_genres['CLEANED_BAND'] = spotify_genres['BAND'].map(lambda x: band_mapping.get(x, x))\n", "spotify_genres['CLEANED_BAND'] = spotify_genres['CLEANED_BAND'].astype('category')\n", "\n", "end = dt.datetime.now()\n", "print(f\"Processing took {(end - start).total_seconds() } seconds.\")\n", "\n", "spotify_genres.BAND.value_counts()" ] }, { "cell_type": "markdown", "id": "ece7ccee-893c-4764-82b3-3de8469f50cf", "metadata": {}, "source": [ "Clean description
\n", "We need to double check if all those cleaning functions work correctly and in intended order
\n", "We can also see that parallel apply decreases time by multi-fold" ] }, { "cell_type": "code", "execution_count": null, "id": "b64fef4f-8576-4bb9-a3c1-b149c20590d7", "metadata": {}, "outputs": [], "source": [ "start = dt.datetime.now()\n", "\n", "spotify_genres['CLEANED_DESCRIPTION']= spotify_genres.DESCRIPTION.parallel_apply(remove_and_semicolon)\n", "spotify_genres['CLEANED_DESCRIPTION']= spotify_genres.CLEANED_DESCRIPTION.parallel_apply(remove_html_tags)\n", "spotify_genres['CLEANED_DESCRIPTION']= spotify_genres.CLEANED_DESCRIPTION.parallel_apply(remove_urls)\n", "spotify_genres['CLEANED_DESCRIPTION']= spotify_genres.CLEANED_DESCRIPTION.parallel_apply(remove_slash_n)\n", "spotify_genres['CLEANED_DESCRIPTION']= spotify_genres.CLEANED_DESCRIPTION.parallel_apply(remove_non_ascii)\n", "spotify_genres['CLEANED_DESCRIPTION']= spotify_genres.CLEANED_DESCRIPTION.parallel_apply(remove_stop_words)\n", "spotify_genres['CLEANED_DESCRIPTION']= spotify_genres.CLEANED_DESCRIPTION.parallel_apply(remove_punctuation)\n", "spotify_genres['CLEANED_DESCRIPTION']= spotify_genres.CLEANED_DESCRIPTION.parallel_apply(remove_html)\n", "spotify_genres['CLEANED_DESCRIPTION']= spotify_genres.CLEANED_DESCRIPTION.parallel_apply(make_lower_case)\n", "\n", "end = dt.datetime.now()\n", "print(f\"Processing took {(end - start).total_seconds() } seconds.\")\n", "spotify_genres.head(2)" ] }, { "cell_type": "code", "execution_count": null, "id": "00d87e69-01ab-4e32-9152-47409f1916ad", "metadata": {}, "outputs": [], "source": [ "spotify_genres.dtypes" ] }, { "cell_type": "markdown", "id": "4769fed5-35d2-436a-a2ab-2e60b6f60fcb", "metadata": {}, "source": [ "Write cleaned data back to DB for future usage" ] }, { "cell_type": "code", "execution_count": null, "id": "4b9b61ca-c815-4cf2-995e-b2cbead89b5d", "metadata": {}, "outputs": [], "source": [ "start = dt.datetime.now()\n", "\n", "spotify_genres['CLEANED_BAND'] = spotify_genres['CLEANED_BAND'].astype('string')\n", "cur.execute(\n", " \"CREATE OR REPLACE TABLE \"\n", " \"DEV_ENGINEERING.RBOMBERG_DBT.CHARTMERTIC_CLEANED_TEMP(GLOBAL_PARTICIPANT_ID string, \\\n", " SPOTIFY_ARTIST_ID string,\\\n", " CM_ARTIST NUMBER,\\\n", " POPULARITY_LATEST NUMBER,\\\n", " ARTIST_NAME string,\\\n", " CLEANED_GENRES string,\\\n", " CLEANED_PRONOUN string,\\\n", " CLEANED_GENDER string,\\\n", " CLEANED_BAND string,\\\n", " CLEANED_DESCRIPTION string)\")\n", "\n", "write_pandas(ctx, spotify_genres[['GLOBAL_PARTICIPANT_ID', 'SPOTIFY_ARTIST_ID', 'CM_ARTIST', 'POPULARITY_LATEST', 'ARTIST_NAME', 'CLEANED_GENRES', 'CLEANED_PRONOUN', 'CLEANED_GENDER', 'CLEANED_BAND', 'CLEANED_DESCRIPTION']], \n", " 'DEV_ENGINEERING.RBOMBERG_DBT.CHARTMERTIC_CLEANED_TEMP', quote_identifiers=False)\n", "\n", "end = dt.datetime.now()\n", "print(f\"Processing took {(end - start).total_seconds() } seconds.\")" ] }, { "cell_type": "markdown", "id": "56f67ec9-ed3d-4359-9a52-fc65db5378da", "metadata": {}, "source": [ "Delete unneccesary dataframes if needed" ] }, { "cell_type": "code", "execution_count": null, "id": "36e4ce08-d924-429a-bfe4-be27e7d43385", "metadata": {}, "outputs": [], "source": [ "drop_genres_df = False\n", "if drop_genres_df:\n", " del spotify_genres\n", " gc.collect()\n", " spotify_genres=pd.DataFrame()" ] }, { "cell_type": "markdown", "id": "9587e54a-6a44-46d8-a770-386c19f03a7a", "metadata": {}, "source": [ "
" ] }, { "cell_type": "markdown", "id": "13abb8b8-6880-4165-bd82-050cc39e8d7f", "metadata": {}, "source": [ "#### QUERY SPOTIFY ARTIST TOP STREAMING LOCATIONS" ] }, { "cell_type": "code", "execution_count": null, "id": "98923406-a1fc-4f43-ac2f-b8a9a64ab618", "metadata": {}, "outputs": [], "source": [ "# create new table inside Snowflake aggregating streamings per artist and location(city level)\n", "start = dt.datetime.now()\n", "with open('queries/create_listeners_per_country_1.txt') as f:\n", " create_listeners_per_country_1 = f.read()\n", "f.close()\n", "\n", "cur.execute(create_listeners_per_country_1)\n", "end = dt.datetime.now()\n", "print(f\"Processing took {(end - start).total_seconds() } seconds.\")" ] }, { "cell_type": "code", "execution_count": null, "id": "8f3a9d35-e7c0-420c-9625-299f5b5c87da", "metadata": {}, "outputs": [], "source": [ "# create new table inside Snowflake aggregating streamings per artist and country\n", "start = dt.datetime.now()\n", "with open('queries/create_listeners_per_country.txt') as f:\n", " create_listeners_per_country = f.read()\n", "f.close()\n", "\n", "cur.execute(create_listeners_per_country)\n", "end = dt.datetime.now()\n", "print(f\"Processing took {(end - start).total_seconds() } seconds.\")" ] }, { "cell_type": "code", "execution_count": null, "id": "65f69f45-dc34-4294-993e-14291d8f2607", "metadata": {}, "outputs": [], "source": [ "# query only top 3 streaming countries per artist using preload DBT\n", "start = dt.datetime.now()\n", "\n", "sql = \"\"\"\n", "SELECT SPOTIFY_ARTIST_ID, YEAR_MONTH, country_code_2, total_listeners, row_number()\n", "OVER (PARTITION BY SPOTIFY_ARTIST_ID ORDER BY YEAR_MONTH DESC, total_listeners DESC) AS row_number\n", "from \n", "INTELLIGENCE.DBT_PROD.AD_LISTENERS_LOCATION_COUNTRIES where YEAR_MONTH='2022-10-01'\n", "QUALIFY row_number < 4\n", "\"\"\"\n", "cur.execute(sql)\n", "spotify_listen_countries = cur.fetch_pandas_all()\n", "\n", "end = dt.datetime.now()\n", "print(f\"Processing took {(end - start).total_seconds() } seconds.\")\n", "\n", "spotify_listen_countries.info()\n", "spotify_listen_countries.shape\n", "spotify_listen_countries.head()" ] }, { "cell_type": "code", "execution_count": null, "id": "3ed3ffe0-8874-42e1-b514-77f39d0551e6", "metadata": {}, "outputs": [], "source": [ "# query only top 3 streaming countries per artist\n", "start = dt.datetime.now()\n", "\n", "sql = \"\"\"\n", "SELECT SPOTIFY_ARTIST_ID, country_code_2, total_listeners, row_number()\n", "OVER (PARTITION BY SPOTIFY_ARTIST_ID ORDER BY total_listeners DESC) AS row_number\n", "from \n", "DEV_ENGINEERING.RBOMBERG_DBT.LISTENERS_LOCATION_COUNTRIES\n", "QUALIFY row_number < 4\n", "\"\"\"\n", "cur.execute(sql)\n", "spotify_listen_countries = cur.fetch_pandas_all()\n", "\n", "end = dt.datetime.now()\n", "print(f\"Processing took {(end - start).total_seconds() } seconds.\")\n", "\n", "spotify_listen_countries.info()\n", "spotify_listen_countries.shape\n", "spotify_listen_countries.head()" ] }, { "cell_type": "markdown", "id": "0a3a2b03-9350-4b27-a1c6-e5a2332ff45c", "metadata": {}, "source": [ "Transform data from long to wide" ] }, { "cell_type": "markdown", "id": "6bd77270-1d51-4209-a19f-c4f4358e6c06", "metadata": {}, "source": [ "changed some group by approaches to decrease processing time by 2.5 times" ] }, { "cell_type": "code", "execution_count": null, "id": "e6230a00-bfe6-43d5-af98-be5069110d74", "metadata": {}, "outputs": [], "source": [ "nr_countries_keep = 3\n", "start = dt.datetime.now()\n", "\n", "sp_listen_copy = spotify_listen_countries[['SPOTIFY_ARTIST_ID','COUNTRY_CODE_2']].copy()\n", "\n", "# sp_listen_copy[\"%\"] = sp_listen_copy.TOTAL_LISTENERS.apply(lambda x: 100*x / sp_listen_copy.sum())\n", "# sp_listen_copy[\"%\"] = sp_listen_copy.groupby(level=0).apply(\n", "# lambda x: (x / x.sum())*100\n", "# )\n", "\n", "# sp_listen_copy = sp_listen_copy.\\\n", "# groupby(['SPOTIFY_ARTIST_ID'])['COUNTRY_CODE_2'].\\\n", "# agg(lambda x: '|'.join(x.dropna())).reset_index()\n", "\n", "sp_listen_copy = sp_listen_copy.groupby(['SPOTIFY_ARTIST_ID'])['COUNTRY_CODE_2'].agg(list).reset_index()\n", "sp_listen_copy['COUNTRY_CODE_2'] = sp_listen_copy.COUNTRY_CODE_2.parallel_apply(join_countries)\n", "\n", "end = dt.datetime.now()\n", "print(f\"Processing took {(end - start).total_seconds() } seconds.\")\n", "\n", "sp_listen_copy.shape\n", "sp_listen_copy.head(3)" ] }, { "cell_type": "markdown", "id": "ab5741c2-cf05-4c04-a2bf-a6a48d03ba79", "metadata": {}, "source": [ "Exclude artists with low streaming numbers" ] }, { "cell_type": "code", "execution_count": null, "id": "d971935c-992f-4c50-a359-1a3f00c6bffe", "metadata": {}, "outputs": [], "source": [ "# Exclude lower 5%\n", "spotify_listen_countries['TOTAL_LISTENERS'] = spotify_listen_countries['TOTAL_LISTENERS'].astype('int')\n", "streaming_treshold = spotify_listen_countries.\\\n", " groupby(['SPOTIFY_ARTIST_ID'])['TOTAL_LISTENERS'].sum().quantile(0.05)\n", "print(f\"Streaming treshold: {streaming_treshold}.\")" ] }, { "cell_type": "code", "execution_count": null, "id": "a4fda1db-545f-441f-8162-082c6af24896", "metadata": {}, "outputs": [], "source": [ "start = dt.datetime.now()\n", "\n", "ok_streamers = spotify_listen_countries.groupby(['SPOTIFY_ARTIST_ID'])['TOTAL_LISTENERS'].sum().\\\n", " where(lambda x : x > streaming_treshold).\\\n", " dropna().index.values.tolist()\n", "print(f\"Number of artists that meet streaming treshold: {len(ok_streamers)}.\")\n", "\n", "# rename countries to other for low volume streamers\n", "sp_listen_copy[\"COUNTRY_CODE_2\"] = np.where(sp_listen_copy[\"SPOTIFY_ARTIST_ID\"].\\\n", " isin(ok_streamers), sp_listen_copy['COUNTRY_CODE_2'], \"other_contries\") # think about other definition for this value\n", "\n", "end = dt.datetime.now()\n", "print(f\"Processing took {(end - start).total_seconds() } seconds.\")\n", "\n", "sp_listen_copy.shape\n", "sp_listen_copy.head()" ] }, { "cell_type": "markdown", "id": "96798f95-0903-4782-a5e2-01c2778ec4d0", "metadata": {}, "source": [ "Write cleaned data back to DB for future usage" ] }, { "cell_type": "code", "execution_count": null, "id": "06201e5d-9d92-41be-90a4-02a60cbb06dc", "metadata": {}, "outputs": [], "source": [ "start = dt.datetime.now()\n", "\n", "cur.execute(\n", " \"CREATE OR REPLACE TABLE \"\n", " \"DEV_ENGINEERING.RBOMBERG_DBT.LISTENERS_COUNTRIES_TOP(SPOTIFY_ARTIST_ID string, \\\n", " COUNTRY_CODE_2 string \\\n", " )\")\n", "\n", "write_pandas(ctx, sp_listen_copy, \n", " 'DEV_ENGINEERING.RBOMBERG_DBT.LISTENERS_COUNTRIES_TOP', quote_identifiers=False)\n", "\n", "end = dt.datetime.now()\n", "print(f\"Processing took {(end - start).total_seconds() } seconds.\")" ] }, { "cell_type": "markdown", "id": "0e668835-82f4-43dd-8d01-5ba3e7ad1698", "metadata": {}, "source": [ "Delete unneccesary dataframes if needed" ] }, { "cell_type": "code", "execution_count": null, "id": "d0e1f0ea-07fa-4c84-9c64-f5bf2edd0dd8", "metadata": {}, "outputs": [], "source": [ "drop_top_countries_df = False\n", "if drop_top_countries_df:\n", " del sp_listen_copy\n", " gc.collect()\n", " sp_listen_copy=pd.DataFrame()" ] }, { "cell_type": "markdown", "id": "00918b11-7869-4e3b-a06f-6af3be8cbbe4", "metadata": {}, "source": [ "#### QUERY EACH ARTISTS TOP 20 RELATED ARTIST BASED ON SPOTIFY" ] }, { "cell_type": "code", "execution_count": null, "id": "513f49a5-da05-41c6-9de2-6a620338ba62", "metadata": {}, "outputs": [], "source": [ "start = dt.datetime.now()\n", "\n", "# create new table inside Snowflake\n", "with open('queries/query_related_artists.txt') as f:\n", " create_related_artists = f.read()\n", "f.close()\n", "\n", "cur.execute(create_related_artists)\n", "\n", "end = dt.datetime.now()\n", "print(f\"Processing took {(end - start).total_seconds() } seconds.\")" ] }, { "cell_type": "code", "execution_count": null, "id": "5bb4d3f9-323e-4667-9fdf-6e3a94c3dedb", "metadata": {}, "outputs": [], "source": [ "start = dt.datetime.now()\n", "\n", "# browse newly created table\n", "sql = \"\"\"SELECT * from DEV_ENGINEERING.RBOMBERG_DBT.RELATED_ARTISTS;\n", "\"\"\"\n", "cur.execute(sql)\n", "spotify_related_artists = cur.fetch_pandas_all()\n", "spotify_related_artists.shape\n", "spotify_related_artists.head(3)\n", "\n", "end = dt.datetime.now()\n", "print(f\"Processing took {(end - start).total_seconds() } seconds.\")" ] }, { "cell_type": "markdown", "id": "31b2adb0-00ee-4d61-b078-bd4c8fd55ac7", "metadata": {}, "source": [ "#### QUERY TAGS RELATED TO PLAYLISTS WHERE ARTISTS ARE INCLUDED IN" ] }, { "cell_type": "code", "execution_count": null, "id": "98a0fb4b-812b-431c-b9fb-52f2dd8866fd", "metadata": {}, "outputs": [], "source": [ "start = dt.datetime.now()\n", "\n", "# create new table inside Snowflake where we have playlists related to each artist\n", "with open('queries/spotify_artists_and_playlists.txt') as f:\n", " create_spotify_artists_and_playlists = f.read()\n", "f.close()\n", "\n", "cur.execute(create_spotify_artists_and_playlists)\n", "\n", "end = dt.datetime.now()\n", "print(f\"Processing took {(end - start).total_seconds() } seconds.\")" ] }, { "cell_type": "code", "execution_count": null, "id": "b7a16aa5-9e31-44d4-bf8a-d23c1c3d1adb", "metadata": {}, "outputs": [], "source": [ "# Browse newly created table\n", "sql = \"\"\"SELECT * from DEV_ENGINEERING.RBOMBERG_DBT.SPOTIFY_ARTISTS_PLAYLISTS limit 5\n", "\"\"\"\n", "cur.execute(sql)\n", "spotify_artists_playlists = cur.fetch_pandas_all()\n", "spotify_artists_playlists.shape\n", "spotify_artists_playlists.head()" ] }, { "cell_type": "code", "execution_count": null, "id": "e1e4e00a-ab8a-45f7-bfc5-f8dcacda83e5", "metadata": {}, "outputs": [], "source": [ "# Run this if needed\n", "# spotify_artists_playlists.to_parquet('pickles/arrow.parquet', compression='brotli')" ] }, { "cell_type": "code", "execution_count": null, "id": "2dd6781b-c20a-419a-ad83-509a592e0c98", "metadata": {}, "outputs": [], "source": [ "start = dt.datetime.now()\n", "\n", "# find tags relate to playlists by joining previously created table with playlist tags\n", "with open('queries/query_playlist_tags.txt') as f:\n", " query_playlist_tags = f.read()\n", "f.close()\n", "\n", "cur.execute(query_playlist_tags)\n", "playlist_tags = cur.fetch_pandas_all()\n", "playlist_tags.shape\n", "playlist_tags.head(2)\n", "\n", "end = dt.datetime.now()\n", "print(f\"Processing took {(end - start).total_seconds() } seconds.\")" ] }, { "cell_type": "code", "execution_count": null, "id": "068c1b41-9bcb-461b-8ce5-f9942cdab4b3", "metadata": {}, "outputs": [], "source": [ "# use this to check if any unwanted tag is left\n", "#playlist_tags[~playlist_tags['TAG_NAME'].str.contains('Spotify', 'Heritage Month')].sort_values(by=['TAG_NAME']).to_csv('tags_list.csv')" ] }, { "cell_type": "code", "execution_count": null, "id": "04720b22-0891-412f-a5ab-9585eaaa790a", "metadata": {}, "outputs": [], "source": [ "playlist_tags.shape\n", "\n", "start = dt.datetime.now()\n", "\n", "# drop tags containing value 'Spotify' or 'Heritage Month'\n", "playlist_tags = playlist_tags[~playlist_tags['TAG_NAME'].str.contains('Spotify')]\n", "playlist_tags = playlist_tags[~playlist_tags['TAG_NAME'].str.contains('Heritage Month')]\n", "playlist_tags['CLEANED_TAG_NAME']= playlist_tags.TAG_NAME.parallel_apply(remove_non_ascii)\n", "playlist_tags['CLEANED_TAG_NAME']= playlist_tags.TAG_NAME.parallel_apply(make_lower_case)\n", "# playlist_tags['CLEANED_TAG_NAME']= playlist_tags.CLEANED_TAG_NAME.apply(func = remove_forward_slash)\n", "\n", "# drop redundant columns and duplicate tags\n", "playlist_tags = playlist_tags.drop(['CM_TAG', 'TAG_SOURCE', 'TAG_NAME'], axis='columns').drop_duplicates()\n", "\n", "end = dt.datetime.now()\n", "print(f\"Processing took {(end - start).total_seconds() } seconds.\")\n", "\n", "playlist_tags.shape\n", "playlist_tags.head(3)" ] }, { "cell_type": "code", "execution_count": null, "id": "d77460a1-d7a8-4da2-97f0-bece16831722", "metadata": {}, "outputs": [], "source": [ "start = dt.datetime.now()\n", "\n", "cleaned_playlist_tags = playlist_tags.groupby(['SPOTIFY_ARTIST_ID'])['CLEANED_TAG_NAME'].\\\n", " agg(lambda x: '|'.join(x.dropna())).reset_index()\n", "\n", "end = dt.datetime.now()\n", "print(f\"Processing took {(end - start).total_seconds() } seconds.\")\n", "\n", "\n", "cleaned_playlist_tags.head(2)\n", "cleaned_playlist_tags.shape" ] }, { "cell_type": "markdown", "id": "28e31195-3879-49d7-82e9-49ae198ad3a5", "metadata": {}, "source": [ "Write cleaned data back to DB for future usage" ] }, { "cell_type": "code", "execution_count": null, "id": "e78e8571-c0e7-4d34-8fe4-cc23141672cb", "metadata": {}, "outputs": [], "source": [ "start = dt.datetime.now()\n", "\n", "cur.execute(\n", " \"CREATE OR REPLACE TABLE \"\n", " \"DEV_ENGINEERING.RBOMBERG_DBT.ARTIST_RELATED_TAGS_CLEANED(SPOTIFY_ARTIST_ID string, \\\n", " CLEANED_TAG_NAME string \\\n", " )\")\n", "\n", "write_pandas(ctx, cleaned_playlist_tags, \n", " 'DEV_ENGINEERING.RBOMBERG_DBT.ARTIST_RELATED_TAGS_CLEANED', quote_identifiers=False)\n", "\n", "end = dt.datetime.now()\n", "print(f\"Processing took {(end - start).total_seconds() } seconds.\")" ] }, { "cell_type": "markdown", "id": "db20e56e-662f-48e0-99a2-8f8e3a679d10", "metadata": {}, "source": [ "Delete unneccesary dataframes if needed" ] }, { "cell_type": "code", "execution_count": null, "id": "dd101148-1279-4c16-89e7-ef445499b601", "metadata": {}, "outputs": [], "source": [ "drop_tags_df = False\n", "if drop_tags_df:\n", " del cleaned_playlist_tags\n", " gc.collect()\n", " cleaned_playlist_tags=pd.DataFrame()" ] }, { "cell_type": "markdown", "id": "06bfc928-830b-498a-b197-e664119b5974", "metadata": {}, "source": [ "
" ] }, { "cell_type": "markdown", "id": "318b44c0-356f-4955-90e8-b59f16d45d1f", "metadata": {}, "source": [ "#### MERGE DATA TOGETHER" ] }, { "cell_type": "markdown", "id": "d369a993-2ad0-4e49-892d-475acd7190ee", "metadata": {}, "source": [ "Check first if data is in memory or needs to be loaded from database" ] }, { "cell_type": "code", "execution_count": null, "id": "ada06d21-cb19-4908-aaee-6e638abe1da4", "metadata": {}, "outputs": [], "source": [ "# check for cleaned genres data\n", "try:\n", " reload_genres = spotify_genres.shape[0]\n", " print(\"Loading data from memory.\")\n", "except NameError:\n", " reload_genres = 0\n", " print(\"Loading data from database.\")\n", "\n", "if reload_genres == 0:\n", " sql = \"\"\"SELECT * from DEV_ENGINEERING.RBOMBERG_DBT.CHARTMERTIC_CLEANED_TEMP\n", " \"\"\"\n", " cur.execute(sql)\n", " spotify_genres = cur.fetch_pandas_all()\n", " spotify_genres.shape\n", " spotify_genres.head()" ] }, { "cell_type": "code", "execution_count": null, "id": "674f4f9d-bae6-4223-81ab-e40c5cbcc412", "metadata": {}, "outputs": [], "source": [ "# check for top streaming locations data\n", "try:\n", " reload_top_locations = sp_listen_copy.shape[0]\n", " print(\"Loading data from memory.\")\n", "except NameError:\n", " reload_top_locations = 0\n", " print(\"Loading data from database.\")\n", "\n", "if reload_top_locations == 0:\n", " sql = \"\"\"SELECT * from DEV_ENGINEERING.RBOMBERG_DBT.LISTENERS_COUNTRIES_TOP\n", " \"\"\"\n", " cur.execute(sql)\n", " sp_listen_copy = cur.fetch_pandas_all()\n", " sp_listen_copy.shape\n", " sp_listen_copy.head()" ] }, { "cell_type": "code", "execution_count": null, "id": "67869f61-9871-49c0-9eb9-a6c89be05813", "metadata": {}, "outputs": [], "source": [ "# check for top playlist tags data\n", "try:\n", " reload_playlist_tags = cleaned_playlist_tags.shape[0]\n", " print(\"Loading data from memory.\")\n", "except NameError:\n", " reload_playlist_tags = 0\n", " print(\"Loading data from database.\")\n", "\n", "if reload_playlist_tags == 0:\n", " sql = \"\"\"SELECT * from DEV_ENGINEERING.RBOMBERG_DBT.ARTIST_RELATED_TAGS_CLEANED\n", " \"\"\"\n", " cur.execute(sql)\n", " cleaned_playlist_tags = cur.fetch_pandas_all()\n", " cleaned_playlist_tags.shape\n", " cleaned_playlist_tags.head()" ] }, { "cell_type": "markdown", "id": "ae08ce81-2fa9-4942-8ba3-6a87a7849879", "metadata": {}, "source": [ "Start merging..." ] }, { "cell_type": "markdown", "id": "9d9583b1-623a-46ca-b7ed-fb7fa746cbbd", "metadata": {}, "source": [ "We are using right join here because some simple models don't need top streaming data point" ] }, { "cell_type": "code", "execution_count": null, "id": "72fcdad0-040f-496e-86f9-d2116c6d892b", "metadata": {}, "outputs": [], "source": [ "start = dt.datetime.now()\n", "\n", "\n", "merge_c = 'SPOTIFY_ARTIST_ID'\n", "keep_c = ['GLOBAL_PARTICIPANT_ID', 'SPOTIFY_ARTIST_ID', 'CM_ARTIST', 'POPULARITY_LATEST',\n", " 'ARTIST_NAME', 'CLEANED_GENRES', 'CLEANED_PRONOUN', 'CLEANED_GENDER',\n", " 'CLEANED_BAND', 'CLEANED_DESCRIPTION']\n", "chartmetric_cleaned_df = sp_listen_copy.merge(spotify_genres[keep_c], how='right', left_on = merge_c, right_on = merge_c)\n", "\n", "chartmetric_cleaned_df = chartmetric_cleaned_df.\\\n", " merge(cleaned_playlist_tags, how='left', left_on = merge_c, right_on = merge_c).\\\n", " set_axis(['SPOTIFY_ARTIST_ID', 'C_FAN_COUNTRY_CODE', 'GLOBAL_PARTICIPANT_ID', 'CM_ARTIST', \\\n", " 'C_POPULARITY','C_ARTIST_NAME','C_GENRES','C_PRONOUN', 'C_GENDER', 'C_BAND', 'C_DESCRIPTION',\\\n", " 'C_TAG_NAME'], axis=1, inplace=False)\n", "\n", "chartmetric_cleaned_df['C_BAND'] = chartmetric_cleaned_df['C_BAND'].astype('string')\n", "chartmetric_cleaned_df.shape\n", "chartmetric_cleaned_df.head(2)\n", "\n", "end = dt.datetime.now()\n", "print(f\"Processing took {(end - start).total_seconds() } seconds.\")" ] }, { "cell_type": "markdown", "id": "f93016a1-d951-4719-b291-97df925ad895", "metadata": {}, "source": [ "Write data back to Snowflake to make it easier to access in next steps" ] }, { "cell_type": "code", "execution_count": null, "id": "4dedec94-4995-408f-981f-54d8c2e687a7", "metadata": {}, "outputs": [], "source": [ "start = dt.datetime.now()\n", "\n", "cur.execute(\n", " \"CREATE OR REPLACE TABLE \"\n", " \"DEV_ENGINEERING.RBOMBERG_DBT.CHARTMERTIC_INPUT_FOR_REC(SPOTIFY_ARTIST_ID string, \\\n", " C_FAN_COUNTRY_CODE string, \\\n", " GLOBAL_PARTICIPANT_ID string, \\\n", " CM_ARTIST number, \\\n", " C_POPULARITY number, \\\n", " C_ARTIST_NAME string, \\\n", " C_GENRES string, \\\n", " C_PRONOUN string, \\\n", " C_GENDER string, \\\n", " C_BAND string, \\\n", " C_DESCRIPTION string, \\\n", " C_TAG_NAME string \\\n", " )\")\n", "\n", "write_pandas(ctx, chartmetric_cleaned_df, \n", " 'DEV_ENGINEERING.RBOMBERG_DBT.CHARTMERTIC_INPUT_FOR_REC', quote_identifiers=False)\n", "\n", "end = dt.datetime.now()\n", "print(f\"Processing took {(end - start).total_seconds() } seconds.\")" ] }, { "cell_type": "markdown", "id": "67233da4-0545-44e5-9ed9-e6635b578199", "metadata": {}, "source": [ "
" ] }, { "cell_type": "markdown", "id": "c14d76b9-78eb-4b7b-b45c-97fcb4d33a06", "metadata": {}, "source": [ "
" ] }, { "cell_type": "markdown", "id": "844c0d0a-96a2-41cc-8d76-048632dc9db9", "metadata": {}, "source": [ "#### Query Orchard's artist related genre data\n", "We don't use below data at the moment as Chartmetric data is quite informative but we might get some interesting features" ] }, { "cell_type": "markdown", "id": "3f90781f-1280-47fa-b85f-ed275a922a70", "metadata": {}, "source": [ "TODO: include artists first released song year?" ] }, { "cell_type": "code", "execution_count": null, "id": "136dc5dc-198c-4246-9908-c61046c5c307", "metadata": {}, "outputs": [], "source": [ "refresh_data = False\n", "if refresh_data:\n", " # create new table inside Snowflake\n", " with open('queries/query_orchard_data.txt') as f:\n", " query_orchard_data = f.read()\n", " f.close()\n", " cur.execute(query_orchard_data) \n", "\n", "# query newly created data\n", "sql = \"\"\"select * from DEV_ENGINEERING.RBOMBERG_DBT.ORCHARD_DATA\"\"\"\n", "cur.execute(sql)\n", "\n", "orchard_genres_df = cur.fetch_pandas_all()\n", "orchard_genres_df.head()\n", "orchard_genres_df.shape" ] }, { "cell_type": "markdown", "id": "80b4214e-7788-4438-a9b6-cf8ef5d83f1e", "metadata": {}, "source": [ "Clean and process data" ] }, { "cell_type": "markdown", "id": "7801fd00-d469-4028-b63d-40a11efbbbc1", "metadata": {}, "source": [ "Limit nr of tracks required for artist and how many artists to include for further analysis" ] }, { "cell_type": "code", "execution_count": 54, "id": "26129cb2-7ec7-4e76-a696-d88aea092894", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "We are left with 15082 artists for further analysis\n" ] } ], "source": [ "minimum_tracks = 10\n", "maximum_artists = 75000\n", "list_of_ids = orchard_genres_df.groupby(['SPOTIFY_ARTIST_ID'])['TOTAL_RELEASES'].sum().\\\n", " where(lambda x : x>minimum_tracks).dropna().index.values.tolist()\n", "\n", "if len(list_of_ids)> 10000:\n", " list_of_ids = list_of_ids[0:maximum_artists]\n", "print(f\"We are left with {len(list_of_ids)} artists for further analysis\")" ] }, { "cell_type": "markdown", "id": "fb6baf82-dfde-456e-8b7b-dc49639eeda3", "metadata": {}, "source": [ "Keep only data that has related spotify artists" ] }, { "cell_type": "code", "execution_count": 55, "id": "4a1fc1ed-57f9-42ad-b0bd-99e9e4af7cc9", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(421178, 5)" ] }, "execution_count": 55, "metadata": {}, "output_type": "execute_result" }, { "data": { "text/plain": [ "(52274, 5)" ] }, "execution_count": 55, "metadata": {}, "output_type": "execute_result" }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
GLOBAL_PARTICIPANT_IDSPOTIFY_ARTIST_IDSUBGENRECOUNTRY_CODETOTAL_RELEASES
0f04c037e-3b61-46fc-b71c-3a7a3e7f7744260kWe4y9lg7YFDsZBgsLvPopNone14
1cc37bfc7-51ad-4741-9422-27e98c1822882NRdZaTYZk1CexnDSlnxZUPopNone13
\n", "
" ], "text/plain": [ " GLOBAL_PARTICIPANT_ID SPOTIFY_ARTIST_ID SUBGENRE \\\n", "0 f04c037e-3b61-46fc-b71c-3a7a3e7f7744 260kWe4y9lg7YFDsZBgsLv Pop \n", "1 cc37bfc7-51ad-4741-9422-27e98c182288 2NRdZaTYZk1CexnDSlnxZU Pop \n", "\n", " COUNTRY_CODE TOTAL_RELEASES \n", "0 None 14 \n", "1 None 13 " ] }, "execution_count": 55, "metadata": {}, "output_type": "execute_result" } ], "source": [ "orchard_genres_df.shape\n", "orchard_genres_df2 = orchard_genres_df[orchard_genres_df.SPOTIFY_ARTIST_ID.isin(list_of_ids)].copy()\n", "orchard_genres_df2.shape\n", "orchard_genres_df2.head(2)" ] }, { "cell_type": "code", "execution_count": 56, "id": "f419d4c7-8e9e-4efc-9be0-837b0517d7d5", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
SPOTIFY_ARTIST_IDCOUNTRY_CODESUBGENRETOTAL_RELEASES%
0001Ju8fxqMbobN0xjX7XPLINIndian Devotional & Spiritual110.074324
1001Ju8fxqMbobN0xjX7XPLINIndian Folk260.175676
2001Ju8fxqMbobN0xjX7XPLINIndian Pop & Fusion1090.736486
3001Ju8fxqMbobN0xjX7XPLINIndian Regional Film10.006757
4001Ju8fxqMbobN0xjX7XPLINPop10.006757
\n", "
" ], "text/plain": [ " SPOTIFY_ARTIST_ID COUNTRY_CODE SUBGENRE \\\n", "0 001Ju8fxqMbobN0xjX7XPL IN Indian Devotional & Spiritual \n", "1 001Ju8fxqMbobN0xjX7XPL IN Indian Folk \n", "2 001Ju8fxqMbobN0xjX7XPL IN Indian Pop & Fusion \n", "3 001Ju8fxqMbobN0xjX7XPL IN Indian Regional Film \n", "4 001Ju8fxqMbobN0xjX7XPL IN Pop \n", "\n", " TOTAL_RELEASES % \n", "0 11 0.074324 \n", "1 26 0.175676 \n", "2 109 0.736486 \n", "3 1 0.006757 \n", "4 1 0.006757 " ] }, "execution_count": 56, "metadata": {}, "output_type": "execute_result" }, { "data": { "text/plain": [ "SPOTIFY_ARTIST_ID object\n", "COUNTRY_CODE object\n", "SUBGENRE object\n", "TOTAL_RELEASES int16\n", "% float64\n", "dtype: object" ] }, "execution_count": 56, "metadata": {}, "output_type": "execute_result" } ], "source": [ "orchard_genres_df2 = orchard_genres_df2.groupby(['SPOTIFY_ARTIST_ID', 'COUNTRY_CODE', 'SUBGENRE'])['TOTAL_RELEASES'].\\\n", " sum().reset_index()\n", "orchard_genres_df2[\"%\"] = orchard_genres_df2['TOTAL_RELEASES'] / orchard_genres_df2.\\\n", " groupby('SPOTIFY_ARTIST_ID')['TOTAL_RELEASES'].transform('sum')\n", "orchard_genres_df2.head()\n", "orchard_genres_df2.dtypes" ] }, { "cell_type": "code", "execution_count": 57, "id": "8b980668-5b4d-4365-81ce-78e79a5de498", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
SPOTIFY_ARTIST_IDO_COUNTRY_CODEO_SUBGENRE
0001Ju8fxqMbobN0xjX7XPLINIndian Devotional & Spiritual|Indian Folk|Indi...
1003f4bk13c6Q3gAUXv7dGJATClassical
2009IKtLg2rg2QMbvNtWaohVIComedy Rap|Conscious/Political|Dancehall|Educa...
\n", "
" ], "text/plain": [ " SPOTIFY_ARTIST_ID O_COUNTRY_CODE \\\n", "0 001Ju8fxqMbobN0xjX7XPL IN \n", "1 003f4bk13c6Q3gAUXv7dGJ AT \n", "2 009IKtLg2rg2QMbvNtWaoh VI \n", "\n", " O_SUBGENRE \n", "0 Indian Devotional & Spiritual|Indian Folk|Indi... \n", "1 Classical \n", "2 Comedy Rap|Conscious/Political|Dancehall|Educa... " ] }, "execution_count": 57, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# genres for single row\n", "orchard_combined_df = orchard_genres_df2.groupby(['SPOTIFY_ARTIST_ID', 'COUNTRY_CODE'])['SUBGENRE'].\\\n", " apply('|'.join).reset_index().\\\n", " set_axis(['SPOTIFY_ARTIST_ID', 'O_COUNTRY_CODE', 'O_SUBGENRE'], axis=1, inplace=False)\n", "orchard_combined_df.head(3)" ] }, { "cell_type": "markdown", "id": "adfb67d3-e555-4db0-add2-45c98ca6a216", "metadata": {}, "source": [ "----" ] }, { "cell_type": "markdown", "id": "2b908e38-12c4-47a9-b5dd-3c7e248b7d40", "metadata": {}, "source": [ "Write data back to Snowflake to make it easier to access in next steps" ] }, { "cell_type": "code", "execution_count": 58, "id": "b5b8e4dc-401a-4a89-bcba-a09cace9a02c", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "" ] }, "execution_count": 58, "metadata": {}, "output_type": "execute_result" }, { "data": { "text/plain": [ "(True,\n", " 1,\n", " 9671,\n", " [('mcqxh/file0.txt', 'LOADED', 9671, 9671, 1, 0, None, None, None, None)])" ] }, "execution_count": 58, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# upload list of artist we want to find related locations for\n", "cur.execute(\n", " \"CREATE OR REPLACE TABLE \"\n", " \"DEV_ENGINEERING.RBOMBERG_DBT.ORCHARD_INPUT_FOR_REC(SPOTIFY_ARTIST_ID string, \\\n", " O_COUNTRY_CODE string,\\\n", " O_SUBGENRE string)\")\n", "\n", "# create unique df of\n", "write_pandas(ctx, orchard_combined_df, \n", " 'DEV_ENGINEERING.RBOMBERG_DBT.ORCHARD_INPUT_FOR_REC', quote_identifiers=False)" ] }, { "cell_type": "markdown", "id": "414d93af-dac7-40c7-8a71-9babb469f468", "metadata": {}, "source": [ "-----" ] }, { "cell_type": "markdown", "id": "41fa67ce-6af7-4d54-85d5-9127d18265fe", "metadata": {}, "source": [ "Show some common artists" ] }, { "cell_type": "code", "execution_count": 59, "id": "d178f976-cfa3-4b14-93c3-c60c4c1a21e7", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "In total we have 14640 common spotify profiles.\n", "Genres from 2 datasets for artist with spotify id: 4rMk4gSVtsxoOb7NPwF6hA\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
SPOTIFY_ARTIST_IDCOUNTRY_CODESUBGENRETOTAL_RELEASES%
\n", "
" ], "text/plain": [ "Empty DataFrame\n", "Columns: [SPOTIFY_ARTIST_ID, COUNTRY_CODE, SUBGENRE, TOTAL_RELEASES, %]\n", "Index: []" ] }, "execution_count": 59, "metadata": {}, "output_type": "execute_result" }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
SPOTIFY_ARTIST_IDC_FAN_COUNTRY_CODEGLOBAL_PARTICIPANT_IDCM_ARTISTC_POPULARITYC_ARTIST_NAMEC_GENRESC_PRONOUNC_GENDERC_BANDC_DESCRIPTIONC_TAG_NAME
46926954rMk4gSVtsxoOb7NPwF6hASE|NO|FIa543391e-256d-4e3d-940d-c2c9c6a6d0fa31788741Donnezpop|rock|alternative|danspunk|dansbandother_pronoungender_unknown-1donnez r ett dansband frn perstorp skne som bi...skansk musik|holiday|swedish folk pop|classic ...
\n", "
" ], "text/plain": [ " SPOTIFY_ARTIST_ID C_FAN_COUNTRY_CODE \\\n", "4692695 4rMk4gSVtsxoOb7NPwF6hA SE|NO|FI \n", "\n", " GLOBAL_PARTICIPANT_ID CM_ARTIST C_POPULARITY \\\n", "4692695 a543391e-256d-4e3d-940d-c2c9c6a6d0fa 317887 41 \n", "\n", " C_ARTIST_NAME C_GENRES C_PRONOUN \\\n", "4692695 Donnez pop|rock|alternative|danspunk|dansband other_pronoun \n", "\n", " C_GENDER C_BAND \\\n", "4692695 gender_unknown -1 \n", "\n", " C_DESCRIPTION \\\n", "4692695 donnez r ett dansband frn perstorp skne som bi... \n", "\n", " C_TAG_NAME \n", "4692695 skansk musik|holiday|swedish folk pop|classic ... " ] }, "execution_count": 59, "metadata": {}, "output_type": "execute_result" }, { "name": "stdout", "output_type": "stream", "text": [ "---------------------------------------------------------------------------------------------------------\n" ] } ], "source": [ "common_profiles = list(set(list_of_ids).intersection(ok_streamers))\n", "print(f\"In total we have {len(common_profiles)} common spotify profiles.\")\n", "\n", "for e in common_profiles[17:18]:\n", " print(f\"Genres from 2 datasets for artist with spotify id: {e}\")\n", " orchard_genres_df2[orchard_genres_df2['SPOTIFY_ARTIST_ID']==e]\n", " chartmetric_cleaned_df[chartmetric_cleaned_df['SPOTIFY_ARTIST_ID']==e]\n", " print(\"---------------------------------------------------------------------------------------------------------\")" ] } ], "metadata": { "kernelspec": { "display_name": "Python [conda env:kdnugget_recommender]", "language": "python", "name": "conda-env-kdnugget_recommender-py" }, "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.13" } }, "nbformat": 4, "nbformat_minor": 5 }