In [ ]:

    

import MySQLdb
import netrc
import pandas
import datetime

%matplotlib inline

login = netrc.netrc().authenticators('lastfm.mysql')
if not login:
    raise netrc.NetrcParseError('No authenticators for lastfm.mysql')
    
mysql = MySQLdb.connect(user=login[0],passwd=login[2],db=login[1], charset='utf8')
cursor = mysql.cursor()


    

In [ ]:

    

today = datetime.datetime.now()
print today.strftime('Generated on the %d %b %Y at %H:%M:%S')


    

In [ ]:

    

cursor.execute('select * from view_play_count_by_month')
rows = cursor.fetchall()

df = pandas.DataFrame( [[ij for ij in i] for i in rows] )
df.rename(columns={0: 'Month', 1: 'Play count'}, inplace=True)
df = df.sort_values(by='Month', ascending=[0])
df.head()


    

In [ ]:

    

query_top = 'select v.* from (select @nb_days:=%s p) parm, view_top_%s_for_last_n_days v'


    

In [ ]:

    

def retrieve_top_albums_as_dataframe(nb_days):
    cursor.execute(query_top % (nb_days, 'albums'))
    rows = cursor.fetchall()

    df = pandas.DataFrame( [[ij for ij in i] for i in rows] )
    df.rename(columns={0: 'Album', 1: 'Artist', 2: 'Play count'}, inplace=True);
    df = df.sort_values(by='Play count', ascending=[0]);
    return df


    

In [ ]:

    

df = retrieve_top_albums_as_dataframe(7)
df


    

In [ ]:

    

my_plot = df.plot(kind = 'bar', x = 'Album', title='Top 10 albums for last 7 days', legend=None)
my_plot.set_ylabel('Play count')


    

In [ ]:

    

df = retrieve_top_albums_as_dataframe(30)
df


    

In [ ]:

    

my_plot = df.plot(kind = 'bar', x = 'Album', title='Top 10 albums for last 30 days', legend=None)
my_plot.set_ylabel('Play count')


    

In [ ]:

    

df = retrieve_top_albums_as_dataframe(90)
df


    

In [ ]:

    

my_plot = df.plot(kind = 'bar', x = 'Album', title='Top 10 albums for last 90 days', legend=None)
my_plot.set_ylabel('Play count')


    

In [ ]:

    

df = retrieve_top_albums_as_dataframe(120)
df


    

In [ ]:

    

my_plot = df.plot(kind = 'bar', x = 'Album', title='Top 10 albums for last 120 days', legend=None)
my_plot.set_ylabel('Play count')


    

In [ ]:

    

df = retrieve_top_albums_as_dataframe(180)
df


    

In [ ]:

    

my_plot = df.plot(kind = 'bar', x = 'Album', title='Top 10 albums for last 180 days', legend=None)
my_plot.set_ylabel('Play count')


    

In [ ]:

    

df = retrieve_top_albums_as_dataframe(365)
df


    

In [ ]:

    

my_plot = df.plot(kind = 'bar', x = 'Album', title='Top 10 albums for last 365 days', legend=None)
my_plot.set_ylabel('Play count')


    

In [ ]:

    

df = retrieve_top_albums_as_dataframe('null')
df


    

In [ ]:

    

my_plot = df.plot(kind = 'bar', x = 'Album', title='Top 10 albums for all time', legend=None)
my_plot.set_ylabel('Play count')