Dataloading2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "# Revised Data Loading and Cleaning"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "## Status\n",
    " - This year the count down is the [885 Greatest Songs by Woman](https://xpn.org/countdown/885-greatest-songs-by-women/).  \n",
    " - Lastyear's playlist fetching logic should work.\n",
    " - Got the basics working\n",
    " - silly solution to the \"countdown stops at 6ish\" problem, concatinating playlists.\n",
    "   There's probably a better way, but this works.\n",
    " - Avoid duplicates in the playlist"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "## Overview\n",
    "\n",
    "Keeping data loading seperate.\n",
    "Normal people want stats, fewer people want the \"how did you get this\"\n",
    "If you want to see how this used to happen, \n",
    "or see data for earlier years, see the old [Data Loading Notebook](DataLoading.ipynb).\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "## Setup\n",
    "Under the covers, this is mostly [requests](http://docs.python-requests.org/en/master/) to fetch data,\n",
    "which is now json, so we can use the [internal python library](https://docs.python.org/2.7/library/json.html),\n",
    "and [Pandas](https://pandas.pydata.org/) for data munging.  So let's start with the imports."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true,
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "from IPython.display import display, HTML\n",
    "import requests \n",
    "import json\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from datetime import date, datetime, time\n",
    "from os import path, mkdir\n",
    "import re"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "## Setup Cache directories\n",
    "When we can, we'll cache data.\n",
    "This is only partially for speed.\n",
    "Checking in the data allows for repeatability if sources go away or change.\n",
    "And for some partial results, its useful to publish data.\n",
    "There are others in the XPN community doing data analysis or just asking questions,\n",
    "so csv files are nice to leave around."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false,
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [],
   "source": [
    "cache_dir = './cache'\n",
    "playlist_cache_dir = path.join(cache_dir, 'playlists')\n",
    "a2z90s_cache_dir = path.join(cache_dir, 'a2z90s')\n",
    "bestwomen_cache_dir = path.join(cache_dir, 'bestwomen')\n",
    "musicbrainz_cache_dir = path.join(cache_dir, 'musicbrainz')\n",
    "data_dir = './data'\n",
    "\n",
    "for d in (cache_dir, playlist_cache_dir, a2z90s_cache_dir, bestwomen_cache_dir, data_dir, musicbrainz_cache_dir):\n",
    "    if not path.exists(d): mkdir(d)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "## Generic XPN Playlist Scraping\n",
    "[XPN](xpn.org) updated their site this year.\n",
    "The [Playlists](https://xpn.org/wxpn-playlists/) are now Json.\n",
    "They also now have Album info, which is great."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false,
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [],
   "source": [
    "def fetch_daily_playlist(day, cache_dir=None, verbose = False):\n",
    "    \"\"\"\n",
    "    Fetches the XPN playlist for a given date\n",
    "    \n",
    "    Args:\n",
    "        day (datetime.date) : The day to fetch the playlist for\n",
    "        cache_dir (string)  : Path to the cache directory, or None to avoid caching\n",
    "    \n",
    "    Returns:\n",
    "        DataFrame containing Artist, Title, and Album as Strings and Airtime as Timestamp\n",
    "    \"\"\"\n",
    "    songs = pd.DataFrame(None, columns=['Artist', 'Title', 'Album', 'Air Time'])\n",
    "    if cache_dir is not None:\n",
    "        cache_file =  path.join(cache_dir, \"%04d-%02d-%02d.csv\" % \\\n",
    "                                (day.year, day.month, day.day))\n",
    "    if cache_file is not None and path.exists(cache_file):\n",
    "        songs = pd.read_csv(cache_file, encoding='utf-8')\n",
    "        songs['Air Time'] = pd.to_datetime(songs['Air Time'], errors='coerce')\n",
    "        if verbose: print \"Got %d rows from %s\" % (len(songs), cache_file)\n",
    "    else:\n",
    "        # example url\n",
    "        # https://origin.xpn.org/utils/playlist/json/2022-11-30.json\n",
    "        playlist_url = 'https://origin.xpn.org/utils/playlist/json/%s.json' %(day.isoformat())\n",
    "        page = requests.get(playlist_url)\n",
    "        if verbose: print \"fetching %s returned status %s\" % (day.isoformat(), page.status_code)\n",
    "        # return is a json array of playlist entries\n",
    "        # each playlist entry is a dict of\n",
    "        #  - artist : artists name\n",
    "        #  - song : song title\n",
    "        #  - album : album title\n",
    "        #  - timeslice : string containing an iso date with second granularity in Eastern time\n",
    "        #  - image: url of album cover art\n",
    "        #  - streamPreview : url of a short mp3 outtake from the track\n",
    "        # \n",
    "        # Not all track are music.  But shows like World Cafe or Echos\n",
    "        # put the show name, bound by vertical bars, as the artist, so we can skip them easily enough\n",
    "        track_count = 0\n",
    "        for track in page.json():\n",
    "            if track[\"artist\"][0] == '|':\n",
    "                # skip non-song show titles\n",
    "                continue\n",
    "            if verbose: print (\"adding %s %s %s %s\" % (track['artist'], track['song'], track['album'], \n",
    "                                                       datetime.strptime(track['timeslice'],'%Y-%m-%d %H:%M:%S')))\n",
    "            songs = songs.append({'Artist': track['artist'],\n",
    "                                  'Title': track['song'],\n",
    "                                  'Album': track['album'],\n",
    "                                  'Air Time': datetime.strptime(track['timeslice'],'%Y-%m-%d %H:%M:%S')},\n",
    "                                 ignore_index = True)\n",
    "            if verbose: print \"size = %d\" % len(songs)\n",
    "            track_count += 1\n",
    "        \n",
    "        if verbose: print 'added %d tracks' % (track_count)\n",
    "        if cache_file is not None:\n",
    "            songs.to_csv(cache_file, index=False, encoding='utf-8')\n",
    "            if verbose: print 'write %d rows to %s' % (len(songs), cache_file)\n",
    "        \n",
    "    return songs\n",
    "            \n",
    "                \n",
    "            \n",
    "        \n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true,
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [],
   "source": [
    "def fetch_playlist(start, end, cache_dir=None):\n",
    "    \"\"\"\n",
    "    Fetch all the playlist entries for a range of time.\n",
    "    \n",
    "    Args:\n",
    "        start (datetime.datetime) : The inclusive start time to fetch entries for\n",
    "        end (datetime.datetime)   : The exclusive end time to fetch entries for\n",
    "        cache_dir (string)        : path to the cache directory, or None to avoid caching\n",
    "    \n",
    "    Returns:\n",
    "        Dataframe containing Artist, Title, and Album as strings, and Airtime as timestamp\n",
    "    \"\"\"\n",
    "    songs = pd.DataFrame(None, columns=['Artist', 'Title', 'Album', 'Air Time'])\n",
    "    for day in pd.date_range(start.date(), end.date()):\n",
    "        songs = songs.append(fetch_daily_playlist(day.date(), cache_dir), ignore_index=True)\n",
    "    songs = songs[songs['Air Time'] >= start]\n",
    "    songs = songs[songs['Air Time'] < end]\n",
    "    # sometimes the playlist entries are duplicated\n",
    "    songs = songs.drop_duplicates(subset=['Artist', 'Title', 'Album'])\n",
    "    songs = songs.sort_values(by = 'Air Time')\n",
    "    \n",
    "    \n",
    "    return songs"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "## Load The Playlists\n",
    "Fetch all the playlists, for the duration of the countdown,\n",
    "pulling from local cache if possible."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 885 Songs by Women"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "got 885 rows\n"
     ]
    }
   ],
   "source": [
    "# this is slightly awkward.  Since the playlist stops in the evening and restarts in\n",
    "# the morning, we can kind of treat it as multiple playlist.\n",
    "women_day1 = fetch_playlist(datetime(2023, 12, 5, 8, 0), datetime(2023, 12,5,17,19), playlist_cache_dir)\n",
    "women_day2 = fetch_playlist(datetime(2023, 12, 6, 8, 0), datetime(2023, 12,6,17,53), playlist_cache_dir)\n",
    "women_day3 = fetch_playlist(datetime(2023, 12, 7, 8, 0), datetime(2023, 12,7,17,32), playlist_cache_dir)\n",
    "women_day4 = fetch_playlist(datetime(2023, 12, 8, 8, 0), datetime(2023, 12,8,16,0), playlist_cache_dir)\n",
    "women_day5 = fetch_playlist(datetime(2023, 12, 9, 10, 0), datetime(2023,12,9,18,0), playlist_cache_dir)\n",
    "women_day6 = fetch_playlist(datetime(2023, 12, 10, 11, 0), datetime(2023,12, 10,15,0 ), playlist_cache_dir)\n",
    "women_day7 = fetch_playlist(datetime(2023, 12, 11, 8, 0), datetime(2023, 12, 11, 17, 25), playlist_cache_dir)\n",
    "women_day8 = fetch_playlist(datetime(2023, 12, 12, 8, 0), datetime(2023,12,12,17,54), playlist_cache_dir)\n",
    "\n",
    "women_days = [women_day1, women_day2, women_day3, women_day4, women_day5, women_day6, women_day7, women_day8]\n",
    "print \"got %d rows\" % reduce(lambda x,y: x + len(y), women_days, 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false,
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Artist</th>\n",
       "      <th>Title</th>\n",
       "      <th>Album</th>\n",
       "      <th>Air Time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>183</th>\n",
       "      <td>Martha &amp; The Vandellas</td>\n",
       "      <td>Nowhere To Run</td>\n",
       "      <td>Dance Party</td>\n",
       "      <td>2023-12-05 08:00:37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>182</th>\n",
       "      <td>Belly</td>\n",
       "      <td>Gepetto</td>\n",
       "      <td>Star</td>\n",
       "      <td>2023-12-05 08:04:47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>181</th>\n",
       "      <td>The Pointer Sisters</td>\n",
       "      <td>I'm So Excited</td>\n",
       "      <td>So Excited!</td>\n",
       "      <td>2023-12-05 08:08:05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>180</th>\n",
       "      <td>Mary J. Blige</td>\n",
       "      <td>Real Love</td>\n",
       "      <td>What's The 411?</td>\n",
       "      <td>2023-12-05 08:12:41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179</th>\n",
       "      <td>Brandi Carlile</td>\n",
       "      <td>Dreams</td>\n",
       "      <td>Give Up The Ghost</td>\n",
       "      <td>2023-12-05 08:17:08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "HTML(women_day1.head(5).to_html())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false,
    "hideCode": false,
    "hidePrompt": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Artist</th>\n",
       "      <th>Title</th>\n",
       "      <th>Album</th>\n",
       "      <th>Air Time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Tracy Chapman</td>\n",
       "      <td>Fast Car</td>\n",
       "      <td>Tracy Chapman</td>\n",
       "      <td>2023-12-12 17:29:02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Brandi Carlile</td>\n",
       "      <td>The Story</td>\n",
       "      <td>The Story</td>\n",
       "      <td>2023-12-12 17:34:02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Dolly Parton</td>\n",
       "      <td>Jolene</td>\n",
       "      <td>Jolene</td>\n",
       "      <td>2023-12-12 17:40:31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Joni Mitchell</td>\n",
       "      <td>A Case Of You</td>\n",
       "      <td>Blue</td>\n",
       "      <td>2023-12-12 17:43:12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Aretha Franklin</td>\n",
       "      <td>Respect</td>\n",
       "      <td>I Never Loved A Man The Way I Love You</td>\n",
       "      <td>2023-12-12 17:50:44</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "HTML(women_day8.tail(5).to_html())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 90s A-Z"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# ninties = fetch_playlist(datetime(2022, 12, 1, 8, 0), datetime(2022, 12, 8, 8, 30),\n",
    "#                           playlist_cache_dir)\n",
    "# print \"got %d rows\" % len(ninties)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 90s Non-alphabetics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# ninties_extras = fetch_playlist(datetime(2022, 12, 8, 8, 30), datetime(2022, 12, 8, 11, 8),\n",
    "#                           playlist_cache_dir)\n",
    "# print \"got %d rows\" % len(ninties_extras)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "## Augmenting The Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "### Estimating Durations\n",
    "\n",
    "Since we have air times, we can approximate durations by subtracting the air time from the next track's air times.  There are a couple catches with this\n",
    "- we need to pass in an explicit end time for the last track, but that's minor\n",
    "- we need to add some logic to 'skip over' the Free at Noons that happen on Fridays form 12 noon till \"like 12:40 or so\" and don't appear in the playlist at all\n",
    "- there's no clear way to account for \"non-song time\" like station promos, hosts introducing songs, station ids, and so forth.  Fortunately, the percentage of time that is really music is pretty high thanks to XPN being listener supported."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true,
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [],
   "source": [
    "def estimate_durations(playlist, end_time=None):\n",
    "    \"\"\"\n",
    "    Estimate the song durations\n",
    "    Args: \n",
    "        playlist (DataFrame): playlist with minimally an 'Air Time' attribute\n",
    "        end_time (datetime): end time of the play list, or None if still going\n",
    "    Return:\n",
    "        modified DataFrame with 'Duration' attribute added.\n",
    "    \"\"\"\n",
    "    \n",
    "    playlist['Duration'] = pd.Series([0 for x in range(len(playlist.index))], index=playlist.index)\n",
    "    previous = None\n",
    "    last_idx = None\n",
    "    for idx, row in playlist.iterrows():\n",
    "        if not previous is None:\n",
    "            if row['Air Time'].date().weekday() == 4 and previous.hour == 11 and row['Air Time'].hour == 12:\n",
    "                # We just fell into a free at noon\n",
    "                playlist.loc[last_idx, 'Duration'] = 60 - previous.minute\n",
    "            else:\n",
    "                # just subtract this start from the previous\n",
    "                delta = row['Air Time'] - previous\n",
    "                playlist.loc[last_idx, 'Duration'] = delta.seconds / 60\n",
    "        previous = row['Air Time']\n",
    "        last_idx = idx\n",
    "\n",
    "    # fixup the last row\n",
    "    if end_time is not None:    \n",
    "        delta = end_time - playlist.loc[last_idx,'Air Time']\n",
    "        playlist.loc[last_idx, 'Duration'] = delta.seconds / 60\n",
    "    \n",
    "    return playlist"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false,
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [],
   "source": [
    "# ninties = estimate_durations(ninties, datetime(2022,12, 8, 8, 30))\n",
    "# ninties_extras = estimate_durations(ninties_extras, datetime(2022, 12, 8, 11, 9))\n",
    "women_day1 = estimate_durations(women_day1, datetime(2023,12,5, 17, 19))\n",
    "women_day2 = estimate_durations(women_day2, datetime(2023, 12, 6, 17, 53))\n",
    "women_day3 = estimate_durations(women_day3, datetime(2023, 12, 7, 17, 32))\n",
    "women_day4 = estimate_durations(women_day4, datetime(2023, 12, 8, 16, 0))\n",
    "women_day5 = estimate_durations(women_day5, datetime(2023, 12, 9, 18, 0))\n",
    "women_day6 = estimate_durations(women_day6, datetime(2023, 12,10, 15, 0))\n",
    "women_day7 = estimate_durations(women_day7, datetime(2023, 12, 11, 17, 25))\n",
    "women_day8 = estimate_durations(women_day8, datetime(2023,12,12,17,54))\n",
    "women = pd.concat([women_day1, women_day2, women_day3, women_day4, women_day5, women_day6, women_day7, women_day8], ignore_index=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Place in countdown\n",
    "\n",
    "For countdowns it's useful to have a colum for postition in the countdow.\n",
    "So, for example, in the 885 Greatest Songs by women, the first track is 885, and the last is number 1."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def assign_positions(playlist, size):\n",
    "    \"\"\"\n",
    "    Assign postions to songs in a playlist\n",
    "    Args:\n",
    "        playlist (DataFrame): playlist with rows in order of Air Time\n",
    "        size (int): eventual size of the playlist\n",
    "    Return:\n",
    "        modified playlist with 'Position\" appended\n",
    "    \"\"\"\n",
    "    return playlist.assign(Position=range(size, size -len(playlist), -1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "women = assign_positions(women, 885)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "### Extracting Initial Letters\n",
    "This might be the lamest, simplest, data augmentation,\n",
    "but these are mostly A to Z countdows.\n",
    "Besides nothing is ever really that simple.\n",
    "Blanks and initial punctuation (ex *'Til*) have show up before."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false,
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [],
   "source": [
    "def first_char(s):\n",
    "    for c in s:\n",
    "        if type(c) is str and c.isalpha():\n",
    "            return c.upper()\n",
    "    return s[0]\n",
    "\n",
    "# ninties = ninties.join(ninties.apply(lambda x: first_char(x['Title']), axis=1).to_frame('Letter'))\n",
    "women = women.join(women.apply(lambda x: first_char(x['Title']), axis=1).to_frame('Letter'))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For the non-alphabetic leftovers, we'll do first character instead, so no skipping past non-alphabetics."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# ninties_extras = ninties_extras.join(ninties_extras.apply(lambda x: x[1][0].upper(), axis=1).to_frame('First Character'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "### Extracting First Words\n",
    "Not sure how interesting this is,\n",
    "but the \"should we include leading articles\" was the genesis of this effort back in 2016.\n",
    "Besides it's easy."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false,
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [],
   "source": [
    "from nltk.tokenize import RegexpTokenizer\n",
    "custom_tokenize = RegexpTokenizer(\"[\\w'\\-]+|[^\\w'\\s\\-]\").tokenize\n",
    "# ninties = ninties.join(ninties.apply(lambda x: custom_tokenize(x['Title'])[0], axis=1).to_frame('First Word'))\n",
    "# ninties_extras = ninties_extras.join(ninties_extras.apply(lambda x: custom_tokenize(x['Title'])[0], axis=1).to_frame('First Word'))\n",
    "women = women.join(women.apply(lambda x: custom_tokenize(x['Title'])[0], axis=1).to_frame('First Word'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "### Publication Years\n",
    "\n",
    "For the 90s A-Z, the realtime list uses a single Json request to get a list of songs\n",
    "and it contains the publicatin year.\n",
    "this might just be a lot easier than using [MusicBrainz](https://musicbrainz.org).\n",
    "For the 885 Songs by Women, we'll need to use Music Brainz below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false,
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [],
   "source": [
    "def fetch_years():\n",
    "    # the 90s specific playlist page has a json feed with years, \n",
    "    # so we can just fetch that as\n",
    "    # https://origin.xpn.org/countdown/2022/2022_90s_az.json\n",
    "    # one catch, unlike the normal playlist, it stuffs esacpped esapes on \"specials\"\n",
    "    # for example \"Don\\\\'t need no \\\\'cape here\" rather than \"Don't need no 'cape here\"\n",
    "    #\n",
    "    # their data isn't much better than what I did myself.\n",
    "    # for the moment, drop anything outside 1990-1999.\n",
    "    years = pd.DataFrame(None, columns=['Artist', 'Title', 'Album', 'Year'])\n",
    "    az_url = 'https://origin.xpn.org/countdown/2022/2022_90s_az.json'\n",
    "    page = requests.get(az_url)\n",
    "    for track in page.json():\n",
    "        if len(track['releaseDate']) == 4:\n",
    "            release_year = int(track['releaseDate'])\n",
    "            if release_year < 1990 or release_year > 1999:\n",
    "                    release_year = 0\n",
    "        else:\n",
    "            release_year = 0\n",
    "        years = years.append({'Artist': track['artist'].replace(\"\\\\\", \"\"),\n",
    "                              'Title': track['song'].replace(\"\\\\\", \"\"),\n",
    "                              'Album': track['album'].replace(\"\\\\\", \"\"),\n",
    "                              'Year': release_year},\n",
    "                             ignore_index = True)\n",
    "    return years\n",
    "    \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false,
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [],
   "source": [
    "# ninties = ninties.merge(fetch_years(), on = ['Artist', 'Title', 'Album'], how='left')\n",
    "# ninties['Year'] = ninties['Year'].fillna(value=0).astype(int)\n",
    "# print \"Of %d 90s tracks, %d had valid dates and %d did not\" % \\\n",
    "#     (len(ninties), len(ninties[ninties['Year'] > 0]), len(ninties[ninties['Year'] == 0]))\n",
    "# ninties_extras = ninties_extras.merge(fetch_years(), on = ['Artist', 'Title', 'Album'], how='left')\n",
    "# ninties_extras['Year'] = ninties_extras['Year'].fillna(value=0).astype(int)\n",
    "# print \"Of %d 90s extra tracks, %d had valid dates and %d did not\" % \\\n",
    "#     (len(ninties_extras), len(ninties_extras[ninties['Year'] > 0]), len(ninties_extras[ninties_extras['Year'] == 0]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## MusicBrainz Data\n",
    "\n",
    "[MusicBrainz](https://musicbrainz.org/) is an free online music database,\n",
    "with an [external XML Web-service](https://wiki.musicbrainz.org/Development/XML_Web_Service/Version_2)\n",
    "that is supported in [Python](https://www.python.org/)\n",
    "via the [musicbrainzngs](https://pypi.org/project/musicbrainzngs/) library.\n",
    "I'd originally used it to get publication year for the 2016 countdown,\n",
    "but abandoned it in 2017 since the [2017 playlist page](http://xpn.org/music-artist/885-countdown/2017/xpn-a-z)\n",
    "had lists by year.\n",
    "Since there's no year data in \n",
    "2023's [885 Greatest Songs by Women](https://xpn.org/countdown/885-greatest-songs-by-women/)\n",
    "I'm bringing it back.\n",
    "\n",
    "There are a couple of potential issues with querying MusicBrainz\n",
    "  \n",
    "  - MusicBrainz has its own rules about how to enter data,\n",
    "    that don't always match those at WXPN,\n",
    "    so sometimes searches fail for data mismatches.\n",
    "  - As a free volunteer based service, there's no guarantee that\n",
    "    the data is there, though their data-set is very complete.\n",
    "  - Finding the *right* recording is an art at best.\n",
    "    My general approach has been to look for the oldest official \n",
    "    release for any recording matching the title and artist.\n",
    "    That *mostly* works.\n",
    "\n",
    "And when that all fails, \n",
    "I've been known to resort to just searching various music sites\n",
    "and manually updating the data.\n",
    "\n",
    "One consequence is that we'll always lag on publication year data\n",
    "during the running of the playlists."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def add_musicbrainz_data(playlist, min_year = 1900, cache_file = None):\n",
    "    \"\"\"\n",
    "    Add data from the musicbrainz database.  Currently just first year of publication.\n",
    "    The input data frame should contain at least Title and Artist fields\n",
    "    and the resulting dataframe will have a new Year field.\n",
    "    The cache file if used, should have been generated by a previous run of\n",
    "    this function.\n",
    "    Using a cache is strongly encouraged,\n",
    "    as the MusicBrainz search interface is rate limited to one search per second\n",
    "    so this can be very slow for large playlists.\n",
    "    \n",
    "    Args:\n",
    "        playlist (Dataframe) : playlist to update\n",
    "        min_year (int)       : miminum year to consider\n",
    "        cache_file (string)  : path to cache file\n",
    "         \n",
    "    Returns:\n",
    "        Dataframe containing the augmented playlist\n",
    "    \"\"\"\n",
    "    import musicbrainzngs as mb\n",
    "    mb.set_useragent('xpn-a2z', '0.1','https://github.com/asudell/a2z')\n",
    "    \n",
    "    # keep a list of artists named differently\n",
    "    # at MusicBrainz than XPN, so we can 'fix' them\n",
    "    artist_names = {\n",
    "        \"R. E. M.\": \"REM\",\n",
    "        \"Run-DMC\": \"Run-D.M.C.\",\n",
    "        \"The Ramones\": \"Ramones\"\n",
    "    }\n",
    "    \n",
    "    # load the cache if we have one\n",
    "    if cache_file is not None and path.exists(cache_file):\n",
    "        years = pd.read_csv(cache_file, encoding='utf-8')\n",
    "        years = years.drop_duplicates()\n",
    "    else:\n",
    "        years = pd.DataFrame(None, columns=('Title','Artist', 'Year'))\n",
    "    \n",
    "    augmented = playlist.merge(years, how = 'left')\n",
    "    \n",
    "    # Lookup any unaugmented rows\n",
    "    new_mb_rows = []\n",
    "    for index, row in augmented[augmented['Year'].isnull()].iterrows():\n",
    "        if row['Artist'] in artist_names:\n",
    "            artist = artist_names[row['Artist']]\n",
    "        else:\n",
    "            artist = row['Artist']\n",
    "        result = mb.search_recordings(row['Title'],\n",
    "                                      artist = artist,\n",
    "                                      status = 'official',\n",
    "                                      strict = True,\n",
    "                                      limit = 25)\n",
    "        rel_year = None\n",
    "        for recording in result['recording-list']:\n",
    "            if recording['release-list']:\n",
    "                for release in recording['release-list']:\n",
    "                    if 'date' in release and len(release['date']) > 0:\n",
    "                        y = int(release['date'].split('-')[0])\n",
    "                        if rel_year is None or rel_year > y:\n",
    "                            if y >= min_year:\n",
    "                                # assume years before 1900 are typos\n",
    "                                rel_year = y\n",
    "        if rel_year is not None:\n",
    "            new_mb_rows.append([row['Title'], row['Artist'], rel_year])\n",
    "    \n",
    "    new_years = pd.DataFrame(new_mb_rows, columns=('Title','Artist', 'Year'))\n",
    "    # if we found new data, resave the cache and rebuild the augmented data\n",
    "    if len(new_years) > 0:\n",
    "        years = years.append(new_years, ignore_index=True)\n",
    "        years = years.drop_duplicates()\n",
    "        if cache_file is not None:\n",
    "            years.to_csv(cache_file, index=False, encoding='utf-8')\n",
    "        augmented = playlist.merge(years, how = 'left')\n",
    "    \n",
    "    return augmented"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "women = add_musicbrainz_data(women, 1900, path.join(musicbrainz_cache_dir, 'women_years.csv'))\n",
    "# pandas dosen't support NAs in int data, so set to 0 \n",
    "women['Year'] = women['Year'].fillna(0).astype(int)\n",
    "# save a copy of anything without a year for manual review\n",
    "women_missing = women[women['Year'] == 0][['Title', 'Artist']]\n",
    "women_missing.to_csv(path.join(musicbrainz_cache_dir, 'women_need_years.csv'),\n",
    "                    index=False, encoding='utf-8')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "## Checking The Results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false,
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Artist</th>\n",
       "      <th>Title</th>\n",
       "      <th>Album</th>\n",
       "      <th>Air Time</th>\n",
       "      <th>Duration</th>\n",
       "      <th>Position</th>\n",
       "      <th>Letter</th>\n",
       "      <th>First Word</th>\n",
       "      <th>Year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Martha &amp; The Vandellas</td>\n",
       "      <td>Nowhere To Run</td>\n",
       "      <td>Dance Party</td>\n",
       "      <td>2023-12-05 08:00:37</td>\n",
       "      <td>4</td>\n",
       "      <td>885</td>\n",
       "      <td>N</td>\n",
       "      <td>Nowhere</td>\n",
       "      <td>1965</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Belly</td>\n",
       "      <td>Gepetto</td>\n",
       "      <td>Star</td>\n",
       "      <td>2023-12-05 08:04:47</td>\n",
       "      <td>3</td>\n",
       "      <td>884</td>\n",
       "      <td>G</td>\n",
       "      <td>Gepetto</td>\n",
       "      <td>1992</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>The Pointer Sisters</td>\n",
       "      <td>I'm So Excited</td>\n",
       "      <td>So Excited!</td>\n",
       "      <td>2023-12-05 08:08:05</td>\n",
       "      <td>4</td>\n",
       "      <td>883</td>\n",
       "      <td>I</td>\n",
       "      <td>I'm</td>\n",
       "      <td>1982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Mary J. Blige</td>\n",
       "      <td>Real Love</td>\n",
       "      <td>What's The 411?</td>\n",
       "      <td>2023-12-05 08:12:41</td>\n",
       "      <td>4</td>\n",
       "      <td>882</td>\n",
       "      <td>R</td>\n",
       "      <td>Real</td>\n",
       "      <td>1992</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Brandi Carlile</td>\n",
       "      <td>Dreams</td>\n",
       "      <td>Give Up The Ghost</td>\n",
       "      <td>2023-12-05 08:17:08</td>\n",
       "      <td>7</td>\n",
       "      <td>881</td>\n",
       "      <td>D</td>\n",
       "      <td>Dreams</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "HTML(women.head(5).to_html())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false,
    "hideCode": false,
    "hidePrompt": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Artist</th>\n",
       "      <th>Title</th>\n",
       "      <th>Album</th>\n",
       "      <th>Air Time</th>\n",
       "      <th>Duration</th>\n",
       "      <th>Position</th>\n",
       "      <th>Letter</th>\n",
       "      <th>First Word</th>\n",
       "      <th>Year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>880</th>\n",
       "      <td>Tracy Chapman</td>\n",
       "      <td>Fast Car</td>\n",
       "      <td>Tracy Chapman</td>\n",
       "      <td>2023-12-12 17:29:02</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>F</td>\n",
       "      <td>Fast</td>\n",
       "      <td>1988</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>881</th>\n",
       "      <td>Brandi Carlile</td>\n",
       "      <td>The Story</td>\n",
       "      <td>The Story</td>\n",
       "      <td>2023-12-12 17:34:02</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>T</td>\n",
       "      <td>The</td>\n",
       "      <td>2005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>882</th>\n",
       "      <td>Dolly Parton</td>\n",
       "      <td>Jolene</td>\n",
       "      <td>Jolene</td>\n",
       "      <td>2023-12-12 17:40:31</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>J</td>\n",
       "      <td>Jolene</td>\n",
       "      <td>1974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>883</th>\n",
       "      <td>Joni Mitchell</td>\n",
       "      <td>A Case Of You</td>\n",
       "      <td>Blue</td>\n",
       "      <td>2023-12-12 17:43:12</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>A</td>\n",
       "      <td>A</td>\n",
       "      <td>1971</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>884</th>\n",
       "      <td>Aretha Franklin</td>\n",
       "      <td>Respect</td>\n",
       "      <td>I Never Loved A Man The Way I Love You</td>\n",
       "      <td>2023-12-12 17:50:44</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>R</td>\n",
       "      <td>Respect</td>\n",
       "      <td>1971</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "HTML(women.tail(5).to_html())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "## Saving The Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false,
    "hideCode": false,
    "hidePrompt": false
   },
   "outputs": [],
   "source": [
    "# ninties_data_file = path.join(data_dir, '90sA2Z.csv')\n",
    "# ninties.to_csv(ninties_data_file, index=False, encoding='utf8')\n",
    "# ninties_extras_data_file = path.join(data_dir, '90sextras.csv')\n",
    "# ninties_extras.to_csv(ninties_extras_data_file, index=False, encoding='utf8')\n",
    "\n",
    "women_data_file = path.join(data_dir, 'women.csv')\n",
    "women.to_csv(women_data_file, index=False, encoding='utf8')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true,
    "hideCode": false,
    "hidePrompt": false
   },
   "source": [
    "## Availability\n",
    "The code for this project is in [my github repo](https://github.com/asudell/a2z)\n",
    "and this file is specifically [Dataloading2](https://github.com/asudell/a2z/blob/master/Dataloading2.ipynb). "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## License\n",
    "\n",
    "\n",
    "This project is licensed under a\n",
    "[Creative Commons Attribution-ShareAlike 4.0 International License](http://creativecommons.org/licenses/by-sa/4.0/).\n",
    "You are free to use for commercial or non-commercial purposes,\n",
    "so long as you attribute the source and also allow sharing."
   ]
  }
 ],
 "metadata": {
  "hide_code_all_hidden": false,
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}