import numpy as np
from IPython.display import HTML

#import pandas
pd.set_option('display.max_colwidth', -1)

all_tracks = pd.merge(datasets["historical"],datasets["today"], on=['TRACKID','TRACKNAME','ARTISTNAME',"PERCENT_RANK"])
#print all_tracks

all_tracks['zscore'] = (all_tracks['TODAY']-all_tracks['AVG'])/all_tracks['STD']
#print all_tracks


all_tracks['pct_above_avg'] = (all_tracks['TODAY'] - all_tracks['AVG'])/all_tracks['AVG']*100
#print all_tracks

all_tracks = all_tracks.sort_values('zscore',ascending=False)
#print all_tracks


all_tracks = all_tracks.drop(all_tracks[all_tracks.zscore<4.5].index)
all_tracks = all_tracks.dropna()
all_tracks.reset_index(drop = True, inplace = True)


all_tracks.rename(index=str, columns={'TRACKNAME': 'Track', "ARTISTNAME": "Artist", "pct_above_avg": "Percent above expected", "AVG": "Daily Average", "ISRCID": "Analytics", "TODAY": "Streams Today"},inplace=True)
# all_tracks['Analytics'] = "https://workstation.theorchard.com/analytics/overview?group_by=channel&to_date=2018-06-05&from_date=2018-05-31&store_ids=286&isrc_ids=" + all_tracks["Analytics"].map(str)
all_tracks['Analytics'] = all_tracks['Analytics'].apply(lambda x: '<a target=“_blank” href="https://workstation.theorchard.com/analytics/overview?group_by=channel&to_date=2018-06-05&from_date=2018-05-31&store_ids=286&isrc_ids=">Link</a>'.format(x))
# all_tracks['Analytics'] = all_tracks['Analytics'].apply(lambda x: "<a href='https://cnn.com'>link</a>".format(x))


all_tracks = all_tracks[['Track', 'Artist', 'Daily Average', 'Streams Today','Percent above expected','Analytics']]
all_tracks = all_tracks.sort_values(by=['Percent above expected'], ascending = False)

HTML(all_tracks.to_html(escape=False))

datasets["placements"].rename(index=str, columns={'TRACK': 'Track', "ARTIST": "Artist", "Link": "Playlist Link"},inplace=True)

placements = all_tracks.merge(datasets["placements"],on=['Track','Artist'])
placements['Playlist Link'] = placements['Playlist Link'].apply(lambda x: '<a target=“_blank” href="{0}">Spotify</a>'.format(x))
placements = placements[['Track', 'Artist', 'NAME', 'Playlist Link','DATE ADDED']]
#placements[:100]
HTML(placements.to_html(escape=False))

import matplotlib.pyplot as plt
import seaborn as sns

datasets["lw"].rename(index=str, columns={'TRACKNAME': 'Track', "ARTISTNAME": "Artist"},inplace=True)
lw = all_tracks.merge(datasets["lw"],on=['Track','Artist'])
lw = lw[['Track', 'Artist', 'DAY','SUMS','TRACKID']]
lw['DAY'] = pd.to_datetime(lw['DAY'])

for label, df in lw.sort_values(['DAY']).groupby('TRACKID'):
    print df['Track'].iloc[0] , df['TRACKID'].iloc[0]
    df.plot(x='DAY', y='SUMS',use_index=True)
    plt.show()


# for label, df in lw.sort_values(['DAY']).groupby('TRACKID'):
#     print df
#     sns.tsplot(df,time='DAY',value='SUMS')
#     #plt.show()