FL_Exploratory.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "import plotly.plotly as py"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Objective\n",
    "This notebook is meant to test a proposed scoring system for Fantasy Label, using historical Billboard data downloaded [from this site](http://mikekling.com/analyzing-the-billboard-hot-100/). \n",
    "\n",
    "Eventually, we'll want to test (and ultimately use) this algorithm on the [live Billboard RSS feed](https://www.billboard.com/rss/charts/hot-100). We'll likely want to use [feedparser](http://localhost:8888/notebooks/FL%20Exploratory.ipynb#Scores-Over-Time) for that."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Collect & Prepare Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#data downloaded from http://mikekling.com/analyzing-the-billboard-hot-100/\n",
    "data = open(\"./data/all_billboard_data.txt\", encoding='utf-8', errors='ignore').readlines()\n",
    "\n",
    "#remove some lines that are improperly encoded and write to a new \"cleaned\" csv\n",
    "cleaned_data = open(\"./data/cleaned_billboard_data.txt\",\"w\")\n",
    "for line in data:\n",
    "    cleaned_data.write(line)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"./data/cleaned_billboard_data.txt\", sep = \"|\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df.columns = ['pos',\n",
    "              'last_week',\n",
    "              'peak',\n",
    "              'weeks_on_chart',\n",
    "              'title',\n",
    "              'artist',\n",
    "              'chart_entry_date',\n",
    "              'entry_position',\n",
    "              'overall_peak',\n",
    "              'overall_weeks_on_chart',\n",
    "              'chart_date']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>pos</th>\n",
       "      <th>last_week</th>\n",
       "      <th>peak</th>\n",
       "      <th>weeks_on_chart</th>\n",
       "      <th>title</th>\n",
       "      <th>artist</th>\n",
       "      <th>chart_entry_date</th>\n",
       "      <th>entry_position</th>\n",
       "      <th>overall_peak</th>\n",
       "      <th>overall_weeks_on_chart</th>\n",
       "      <th>chart_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>Uptown Funk!</td>\n",
       "      <td>MARK RONSON featuring BRUNO MARS</td>\n",
       "      <td>41972.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>20150307</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>Thinking Out Loud</td>\n",
       "      <td>ED SHEERAN</td>\n",
       "      <td>41937.0</td>\n",
       "      <td>69.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>20150307</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>3.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>Love Me Like You Do</td>\n",
       "      <td>ELLIE GOULDING</td>\n",
       "      <td>42028.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>20150307</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>4.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>Sugar</td>\n",
       "      <td>MAROON 5</td>\n",
       "      <td>42035.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>20150307</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>2.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>Take Me To Church</td>\n",
       "      <td>HOZIER</td>\n",
       "      <td>41881.0</td>\n",
       "      <td>96.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>20150307</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   pos last_week  peak  weeks_on_chart                title  \\\n",
       "0    1         1   1.0            15.0         Uptown Funk!   \n",
       "1    2         2   2.0            20.0    Thinking Out Loud   \n",
       "2    3         6   3.0             7.0  Love Me Like You Do   \n",
       "3    4         5   4.0             6.0                Sugar   \n",
       "4    5         3   2.0            28.0    Take Me To Church   \n",
       "\n",
       "                             artist  chart_entry_date  entry_position  \\\n",
       "0  MARK RONSON featuring BRUNO MARS           41972.0            65.0   \n",
       "1                        ED SHEERAN           41937.0            69.0   \n",
       "2                    ELLIE GOULDING           42028.0            45.0   \n",
       "3                          MAROON 5           42035.0             8.0   \n",
       "4                            HOZIER           41881.0            96.0   \n",
       "\n",
       "   overall_peak  overall_weeks_on_chart  chart_date  \n",
       "0           1.0                    15.0    20150307  \n",
       "1           2.0                    20.0    20150307  \n",
       "2           3.0                     7.0    20150307  \n",
       "3           4.0                     6.0    20150307  \n",
       "4           2.0                    28.0    20150307  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Historical data covers data from 19400803 to 20150307.\n"
     ]
    }
   ],
   "source": [
    "print(\"Historical data covers data from {0} to {1}.\".format(df.chart_date.min(), df.chart_date.max()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Take subset of original data that we need "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cols = ['pos','last_week','title','chart_date','artist']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>pos</th>\n",
       "      <th>last_week</th>\n",
       "      <th>title</th>\n",
       "      <th>chart_date</th>\n",
       "      <th>artist</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Uptown Funk!</td>\n",
       "      <td>20150307</td>\n",
       "      <td>MARK RONSON featuring BRUNO MARS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>Thinking Out Loud</td>\n",
       "      <td>20150307</td>\n",
       "      <td>ED SHEERAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>Love Me Like You Do</td>\n",
       "      <td>20150307</td>\n",
       "      <td>ELLIE GOULDING</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>Sugar</td>\n",
       "      <td>20150307</td>\n",
       "      <td>MAROON 5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>Take Me To Church</td>\n",
       "      <td>20150307</td>\n",
       "      <td>HOZIER</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   pos last_week                title  chart_date  \\\n",
       "0    1         1         Uptown Funk!    20150307   \n",
       "1    2         2    Thinking Out Loud    20150307   \n",
       "2    3         6  Love Me Like You Do    20150307   \n",
       "3    4         5                Sugar    20150307   \n",
       "4    5         3    Take Me To Church    20150307   \n",
       "\n",
       "                             artist  \n",
       "0  MARK RONSON featuring BRUNO MARS  \n",
       "1                        ED SHEERAN  \n",
       "2                    ELLIE GOULDING  \n",
       "3                          MAROON 5  \n",
       "4                            HOZIER  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_clean = df[cols]\n",
    "df_clean.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Handling Multiple Artists\n",
    "For tracks with multiple artists, we need to duplicate rows in our dataset so that each artist gets full credit."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def split_artist(artists):\n",
    "    stop_words = ['featuring','&', ',']\n",
    "    for word in stop_words:\n",
    "        artists = artists.replace(word, '|')\n",
    "    \n",
    "    a_list = [s.strip() for s in artists.split('|')]\n",
    "    return a_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_artist_list = df_clean.assign(artist_list = [split_artist(a) for a in df.artist])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>pos</th>\n",
       "      <th>last_week</th>\n",
       "      <th>title</th>\n",
       "      <th>chart_date</th>\n",
       "      <th>artist</th>\n",
       "      <th>artist_list</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Uptown Funk!</td>\n",
       "      <td>20150307</td>\n",
       "      <td>MARK RONSON featuring BRUNO MARS</td>\n",
       "      <td>[MARK RONSON, BRUNO MARS]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>Thinking Out Loud</td>\n",
       "      <td>20150307</td>\n",
       "      <td>ED SHEERAN</td>\n",
       "      <td>[ED SHEERAN]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>Love Me Like You Do</td>\n",
       "      <td>20150307</td>\n",
       "      <td>ELLIE GOULDING</td>\n",
       "      <td>[ELLIE GOULDING]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>Sugar</td>\n",
       "      <td>20150307</td>\n",
       "      <td>MAROON 5</td>\n",
       "      <td>[MAROON 5]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>Take Me To Church</td>\n",
       "      <td>20150307</td>\n",
       "      <td>HOZIER</td>\n",
       "      <td>[HOZIER]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   pos last_week                title  chart_date  \\\n",
       "0    1         1         Uptown Funk!    20150307   \n",
       "1    2         2    Thinking Out Loud    20150307   \n",
       "2    3         6  Love Me Like You Do    20150307   \n",
       "3    4         5                Sugar    20150307   \n",
       "4    5         3    Take Me To Church    20150307   \n",
       "\n",
       "                             artist                artist_list  \n",
       "0  MARK RONSON featuring BRUNO MARS  [MARK RONSON, BRUNO MARS]  \n",
       "1                        ED SHEERAN               [ED SHEERAN]  \n",
       "2                    ELLIE GOULDING           [ELLIE GOULDING]  \n",
       "3                          MAROON 5                 [MAROON 5]  \n",
       "4                            HOZIER                   [HOZIER]  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_artist_list.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#adapted from https://stackoverflow.com/questions/32468402/how-to-explode-a-list-inside-a-dataframe-cell-into-separate-rows/32470490\n",
    "#for each row, append it X times to the rows list depending on the # of main artists\n",
    "\n",
    "rows = []\n",
    "_ = df_artist_list.apply(\n",
    "    lambda row: [rows.append([row.pos, row.last_week, row.title, row.chart_date, artist]) for artist in row.artist_list], \n",
    "    axis=1\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>pos</th>\n",
       "      <th>last_week</th>\n",
       "      <th>title</th>\n",
       "      <th>chart_date</th>\n",
       "      <th>artist</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>chart_date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2015-03-07</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Uptown Funk!</td>\n",
       "      <td>20150307</td>\n",
       "      <td>MARK RONSON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-03-07</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Uptown Funk!</td>\n",
       "      <td>20150307</td>\n",
       "      <td>BRUNO MARS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-03-07</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>Thinking Out Loud</td>\n",
       "      <td>20150307</td>\n",
       "      <td>ED SHEERAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-03-07</th>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>Love Me Like You Do</td>\n",
       "      <td>20150307</td>\n",
       "      <td>ELLIE GOULDING</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-03-07</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>Sugar</td>\n",
       "      <td>20150307</td>\n",
       "      <td>MAROON 5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            pos last_week                title  chart_date          artist\n",
       "chart_date                                                                \n",
       "2015-03-07    1         1         Uptown Funk!    20150307     MARK RONSON\n",
       "2015-03-07    1         1         Uptown Funk!    20150307      BRUNO MARS\n",
       "2015-03-07    2         2    Thinking Out Loud    20150307      ED SHEERAN\n",
       "2015-03-07    3         6  Love Me Like You Do    20150307  ELLIE GOULDING\n",
       "2015-03-07    4         5                Sugar    20150307        MAROON 5"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_new = pd.DataFrame(rows, columns=cols)\n",
    "df_flat = df_new.set_index(pd.to_datetime(df_new.chart_date.astype(str))).to_period(freq='7D')\n",
    "df_flat.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Scoring System\n",
    "At a high-level, the initial scoring system scores tracks according to two dimensions: **this week's billboard rank** and **change in billboard rank from last week**.\n",
    "\n",
    "$trackScore = (101 - thisWeekRank) * [1 +  0.01(lastWeekRank - thisWeekRank)]$\n",
    "\n",
    "The multiplier in the 2nd term is meant to reward highly *volatile* tracks i.e. tracks that have jumped high in the chart. For example, in the most extreme case, where a track is not on the chart but enters at #1, the volatility multiplier is 2X."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#score calculators\n",
    "def score_track(row):\n",
    "    last_week_rank = row['last_week'] \n",
    "    this_week_rank = row['pos']\n",
    "    \n",
    "    if last_week_rank != 'NEW':\n",
    "        volatility = int(last_week_rank) - this_week_rank\n",
    "    else:\n",
    "        volatility = 101 - this_week_rank\n",
    "\n",
    "    score = (101-this_week_rank) * (1 + 0.01 * volatility)\n",
    "    return score"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To aggregate track scores on the artist level, we apply an exponentially decaying weighted sum across all of that artist's tracks on the chart. This is to help correct for artists releasing big albums that take up many slots on the chart."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def score_track_list(track_list):\n",
    "    #list of track_scores\n",
    "    weights = [1/2**x for x in range(len(track_list))]\n",
    "    sorted_list = sorted(track_list, reverse=True)\n",
    "    \n",
    "    return sum([w*t for w,t in zip(weights, sorted_list)])\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_flat['track_score'] = df_flat.apply(score_track, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>pos</th>\n",
       "      <th>last_week</th>\n",
       "      <th>title</th>\n",
       "      <th>chart_date</th>\n",
       "      <th>artist</th>\n",
       "      <th>track_score</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>chart_date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2015-03-07</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Uptown Funk!</td>\n",
       "      <td>20150307</td>\n",
       "      <td>MARK RONSON</td>\n",
       "      <td>100.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-03-07</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Uptown Funk!</td>\n",
       "      <td>20150307</td>\n",
       "      <td>BRUNO MARS</td>\n",
       "      <td>100.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-03-07</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>Thinking Out Loud</td>\n",
       "      <td>20150307</td>\n",
       "      <td>ED SHEERAN</td>\n",
       "      <td>99.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-03-07</th>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>Love Me Like You Do</td>\n",
       "      <td>20150307</td>\n",
       "      <td>ELLIE GOULDING</td>\n",
       "      <td>100.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-03-07</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>Sugar</td>\n",
       "      <td>20150307</td>\n",
       "      <td>MAROON 5</td>\n",
       "      <td>97.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-03-07</th>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>Take Me To Church</td>\n",
       "      <td>20150307</td>\n",
       "      <td>HOZIER</td>\n",
       "      <td>94.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-03-07</th>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>FourFiveSeconds</td>\n",
       "      <td>20150307</td>\n",
       "      <td>RIHANNA</td>\n",
       "      <td>93.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-03-07</th>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>FourFiveSeconds</td>\n",
       "      <td>20150307</td>\n",
       "      <td>KANYE WEST</td>\n",
       "      <td>93.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-03-07</th>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>FourFiveSeconds</td>\n",
       "      <td>20150307</td>\n",
       "      <td>PAUL McCARTNEY</td>\n",
       "      <td>93.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-03-07</th>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>Blank Space</td>\n",
       "      <td>20150307</td>\n",
       "      <td>TAYLOR SWIFT</td>\n",
       "      <td>94.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            pos last_week                title  chart_date          artist  \\\n",
       "chart_date                                                                   \n",
       "2015-03-07    1         1         Uptown Funk!    20150307     MARK RONSON   \n",
       "2015-03-07    1         1         Uptown Funk!    20150307      BRUNO MARS   \n",
       "2015-03-07    2         2    Thinking Out Loud    20150307      ED SHEERAN   \n",
       "2015-03-07    3         6  Love Me Like You Do    20150307  ELLIE GOULDING   \n",
       "2015-03-07    4         5                Sugar    20150307        MAROON 5   \n",
       "2015-03-07    5         3    Take Me To Church    20150307          HOZIER   \n",
       "2015-03-07    6         4      FourFiveSeconds    20150307         RIHANNA   \n",
       "2015-03-07    6         4      FourFiveSeconds    20150307      KANYE WEST   \n",
       "2015-03-07    6         4      FourFiveSeconds    20150307  PAUL McCARTNEY   \n",
       "2015-03-07    7         7          Blank Space    20150307    TAYLOR SWIFT   \n",
       "\n",
       "            track_score  \n",
       "chart_date               \n",
       "2015-03-07       100.00  \n",
       "2015-03-07       100.00  \n",
       "2015-03-07        99.00  \n",
       "2015-03-07       100.94  \n",
       "2015-03-07        97.97  \n",
       "2015-03-07        94.08  \n",
       "2015-03-07        93.10  \n",
       "2015-03-07        93.10  \n",
       "2015-03-07        93.10  \n",
       "2015-03-07        94.00  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_flat.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This query returns the top artists according our scoring system applied to the 20150307 chart"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "artist\n",
       "TAYLOR SWIFT      172.735000\n",
       "DRAKE             172.436460\n",
       "NICKI MINAJ       153.244375\n",
       "ARIANA GRANDE     144.877500\n",
       "CHRIS BROWN       137.435000\n",
       "LIL WAYNE         136.522500\n",
       "THE WEEKND        134.080000\n",
       "NE-YO             132.090000\n",
       "MEGHAN TRAINOR    130.270000\n",
       "ELLIE GOULDING    129.650000\n",
       "Name: track_score, dtype: float64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_flat.loc['20150307'].groupby('artist')['track_score'].agg(score_track_list).sort_values(ascending=False).head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This query returns the top artists without applying the weighted sum. This demonstrates how the weighted sum only affects relative ranking slightly, but has great effect on the raw score."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "artist\n",
       "DRAKE             599.06\n",
       "TAYLOR SWIFT      349.86\n",
       "NICKI MINAJ       296.67\n",
       "ARIANA GRANDE     222.13\n",
       "CHRIS BROWN       217.40\n",
       "LIL WAYNE         193.94\n",
       "THE WEEKND        188.12\n",
       "SAM SMITH         184.09\n",
       "NE-YO             172.38\n",
       "MEGHAN TRAINOR    170.45\n",
       "Name: track_score, dtype: float64"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_flat.loc['20150307'].groupby('artist')['track_score'].sum().sort_values(ascending=False).head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Scores Over Time\n",
    "This analysis helps us to understand the volatility of scores week-to-week. Ideally, we want just enough volatility/unpredictability such that the results of each week are surprising enough to the user to want to come back."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "artist_list = ['DRAKE','TAYLOR SWIFT','NICKI MINAJ','ARIANA GRANDE','CHRIS BROWN']\n",
    "artist_list2 = ['LIL WAYNE','THE WEEKND','SAM SMITH','NE-YO', 'MEGHAN TRAINOR']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#groupby artist and calculate agg score across tracks\n",
    "artist_series = df_flat.groupby([df_flat.index.get_level_values(0), 'artist'])['track_score'].agg(score_track_list)\n",
    "\n",
    "#reshape artist df such that each column is a different artist, indexed by weekly period for easier slicing and plotting\n",
    "df_artist = artist_series.to_frame().reset_index(1).pivot(columns='artist', values='track_score')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "#using plotly/cufflinks for visualization: https://plot.ly/pandas/line-charts/#plot-entire-dataframe-in-cufflinks\n",
    "import cufflinks as cf\n",
    "cf.set_config_file(offline=False, world_readable=True, theme='ggplot')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe id=\"igraph\" scrolling=\"no\" style=\"border:none;\" seamless=\"seamless\" src=\"https://plot.ly/~kning/34.embed\" height=\"525px\" width=\"100%\"></iframe>"
      ],
      "text/plain": [
       "<plotly.tools.PlotlyDisplay object>"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_artist.loc['2014-01-01':][artist_list].iplot(kind='scatter')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}