I saw the movie begin again and there is a quote.
Make data easy with Helical Insight.
Helical Insight is the world’s best open source business intelligence tool.
“You can tell a lot about a person by what’s on their playlist” and it got me thinking, I spend a lot of time on spotify so what kind of songs I’m into?
so i found the Spotify Developer application and using python api (spotipy) i decided to code my way to know more about me.
Get Audio Features for a Track | Spotify for Developers
Above links shows the api that holds the audio features of a any track on spoitfy like:
acousticness ,analysis_url ,danceability ,duration_ms ,energy ,instrumentalness ,KEY ,liveness ,loudness ,mode ,speechiness ,tempo ,time_signature ,track_href ,type ,uri ,valence
So below is my code to fetch My Top 50 Tracks on a medium time frame data and load them into my local PostgreSQL table, i’m using pandas and SQLAlchemy. So a pip install of those is advised before you begin to use the code.
Connecting to your spotify requires, creation of your developer application account, fetching your client id and secret, adding localhost as a redirect uri and bit of understanding of the scopes, since i’m using user related functions then user related scope is required.
import spotipy
from spotipy.oauth2 import SpotifyClientCredentials
import spotipy.util as util
import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy import MetaData
from sqlalchemy import Table
from sqlalchemy import inspect
cid = yourclientid
secret = yourclientsecret
username = yourusername
redirect_uri = "https://developer.spotify.com/"
localuri = "http://localhost/"
client_credentials_manager = SpotifyClientCredentials(client_id=cid, client_secret=secret)
sp = spotipy.Spotify(client_credentials_manager=client_credentials_manager)
def getdata(scope):
token = util.prompt_for_user_token(username,scope,cid,secret,localuri)
gdata = spotipy.Spotify(auth=token)
return gdata
tracksdata = []
data = getdata('user-top-read').current_user_top_tracks(limit=50, offset=0, time_range='medium_term')
itemdata = data['items']
for i in range(len(itemdata)):
eachtrack = {}
eachtrack['trackid'] = itemdata[i]['id']
eachtrack['trackname'] = itemdata[i]['name']
for x in range(len(itemdata[i]['artists'])):
eachtrack['artistid'] = itemdata[i]['artists'][0]['id']
eachtrack['artist'] = itemdata[i]['artists'][0]['name']
tracksdata.append(eachtrack)
print(tracksdata)
trackids = []
for i in range(len(tracksdata)):
trackids.append(tracksdata[i]['trackid'])
features = getdata('user-top-read').audio_features(tracks=trackids)
print(features)
td = pd.DataFrame(tracksdata).set_index('trackid')
print(td)
ft = pd.DataFrame(features).set_index('id')
print(ft)
fulldata = pd.merge(td,ft, how='inner', left_index=True, right_index=True)
print(fulldata)
tablename = 'track_data'
metadata = MetaData()
track_data = Table(tablename,metadata)
engine = create_engine('postgresql://postgres:postgres@localhost:5433/postgres')
#fulldata.to_sql(tablename, engine)
inspector = inspect(engine)
gettables = inspector.get_table_names()
# Get table information
print(gettables)
if tablename in inspector.get_table_names():
print('Table Exist')
print('Dropping Table')
track_data.drop(engine)
print('Creating table')
fulldata.to_sql(tablename, engine)
else:
print('Table Do not exist')
print('Creating table')
fulldata.to_sql(tablename, engine)
print(gettables)
once that’s done
Make data easy with Helical Insight.
Helical Insight is the world’s best open source business intelligence tool.
i wrote a SQL:
With data as ( SELECT 1 as partitions ,index ,artist ,artistid ,trackname ,acousticness ,analysis_url ,danceability ,duration_ms ,energy ,instrumentalness ,KEY ,liveness ,loudness ,mode ,speechiness ,tempo ,time_signature ,track_href ,type ,uri ,valence FROM track_data) SELECT SUM(acousticness) AS acousticness ,SUM(danceability) AS danceability ,SUM(energy) AS energy ,SUM(instrumentalness) AS instrumentalness ,SUM(liveness) AS liveness ,SUM(loudness) AS loudness ,SUM(speechiness) AS speechiness ,AVG(tempo) AS tempo ,SUM(valence) AS valence ,(SELECT CONCAT(key,'-',keymax) as keymax FROM (Select DISTINCT key,COUNT(KEY) over(partition by key) keymax from data ORDER BY 2 desc LIMIT 1)b) as key FROM ( SELECT CASE WHEN acousticness > AVG(acousticness) OVER (PARTITION BY partitions) THEN 1 ELSE 0 END AS acousticness ,CASE WHEN danceability > AVG(danceability) OVER (PARTITION BY partitions) THEN 1 ELSE 0 END AS danceability ,CASE WHEN energy > AVG(energy) OVER (PARTITION BY partitions) THEN 1 ELSE 0 END AS energy ,CASE WHEN instrumentalness > AVG(instrumentalness) OVER (PARTITION BY partitions) THEN 1 ELSE 0 END AS instrumentalness ,CASE WHEN liveness > AVG(liveness) OVER (PARTITION BY partitions) THEN 1 ELSE 0 END AS liveness ,CASE WHEN loudness > AVG(loudness) OVER (PARTITION BY partitions) THEN 1 ELSE 0 END AS loudness ,CASE WHEN speechiness > AVG(speechiness) OVER (PARTITION BY partitions) THEN 1 ELSE 0 END AS speechiness ,CASE WHEN tempo > AVG(tempo) OVER (PARTITION BY partitions) THEN tempo ELSE null END AS tempo ,CASE WHEN valence > AVG(valence) OVER (PARTITION BY partitions) THEN 1 ELSE 0 END AS valence FROM data )b
Now the SQL above fetches the average of most column and we use that as a threshold and if our columns value are above the threshold we give that a count, tempo and keys was a little different of a calculation. Tempo was to find the average tempo and Key was find the most used key in my case 1–8
Now we got our query up and running, I’m using Power BI to visualize my data as shown in picture below
Make data easy with Helical Insight.
Helical Insight is the world’s best open source business intelligence tool.
So please comment below if my approach is wrong or there is another approach to this
Thank You
Ehizogie Sohail Izebhijie
Helical IT Solutions Pvt Ltd
Best Open Source Business Intelligence Software Helical Insight Here
A Business Intelligence Framework
Best Open Source Business Intelligence Software Helical Insight is Here