sound_salvation_v2.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "# from bs4 import BeautifulSoup\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "# import seaborn as sns\n",
    "import re\n",
    "import time as t\n",
    "import urllib\n",
    "from requests.adapters import HTTPAdapter\n",
    "from urllib3.util.retry import Retry\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "from bs4 import BeautifulSoup\n",
    "\n",
    "urls = []\n",
    "month = []\n",
    "day = []\n",
    "year = []\n",
    "dj = []\n",
    "\n",
    "headers = {'User-Agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_10_1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/39.0.2171.95 Safari/537.36'}\n",
    "for page in range(1,35):\n",
    "   \n",
    "    url = 'https://spinitron.com/KDHX/show/126906/Sound-Salvation?page={}'.format(page)\n",
    "    reqs = requests.get(url, headers=headers)\n",
    "    \n",
    "    \n",
    "    rock_soup = BeautifulSoup(reqs.text, 'html.parser')\n",
    "    rock_soup.prettify()\n",
    "    \n",
    "    for link in rock_soup.find_all('a'):\n",
    "        urls.append(link.get('href'))\n",
    "        \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# urls = [pl['href'] for pl in urls if \"/pl/\" in pl['href']]\n",
    "urls = [url for url in urls if \"/pl/\" in url]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "print(len(urls))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#drop duplicates\n",
    "sorted_urls = list(set(urls))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(len(sorted_urls))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# sorted_urls = sorted(sorted_urls, key=lambda x: x.split('/')[-1])\n",
    "# sorted_urls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "len(sorted_urls) == len(set(sorted_urls))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # grab the month, day, year, and dj for each show\n",
    "# url = 'https://spinitron.com/' + sorted_urls[0]\n",
    "# # reqs = requests.get(url, headers=headers)\n",
    "# # rock_soup = BeautifulSoup(reqs.text, 'html.parser')\n",
    "# # rock_soup.prettify()\n",
    "# # month.append(rock_soup.find('span', {'class': 'month'}).text)\n",
    "# # day.append(rock_soup.find('span', {'class': 'day'}).text)\n",
    "# # year.append(rock_soup.find('span', {'class': 'year'}).text)\n",
    "# # dj.append(rock_soup.find('span', {'class': 'dj'}).text)\n",
    "# # t.sleep(1)\n",
    "\n",
    "# month.append(rock_soup.find_all(\"span\", \"month\"))\n",
    "# day.append(rock_soup.find_all(\"span\", \"day\"))\n",
    "# year.append(rock_soup.find_all(\"span\", \"year\"))\n",
    "# # dj.append(rock_soup.find_all(\"div\", \"dj\"))\n",
    "\n",
    "# dictionary = {\n",
    "    \n",
    "# 'urls': url, \n",
    "# 'month': month, \n",
    "# 'day': day, \n",
    "# 'year': year, \n",
    "# # 'dj': dj}\n",
    "# }\n",
    "\n",
    "# date_and_dj = pd.DataFrame(dictionary)\n",
    "\n",
    "# date_and_dj"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#make a function that takes in a list of urls and returns a dataframe of all the playlists\n",
    "def sound_salvation_spider(u):\n",
    "    \"\"\"Perform web scraping for any sound salvation playlist given the available link\"\"\"\n",
    "\n",
    "    #get the page\n",
    "    r = requests.get(u)\n",
    "    rock_soup = BeautifulSoup(r.text, 'html')\n",
    "\n",
    "    #get the data\n",
    "    artist = rock_soup.find_all(\"span\", \"artist\")\n",
    "    song = rock_soup.find_all(\"span\", \"song\")\n",
    "    album = rock_soup.find_all(\"span\", \"release\")\n",
    "    album_label = rock_soup.find_all(\"span\", \"label\")\n",
    "    album_year = rock_soup.find_all(\"span\", \"released\")\n",
    "    time = rock_soup.find_all(\"td\", \"spin-time\")\n",
    "    note = rock_soup.find_all('div', attrs={'class':'note'})\n",
    "    url = u\n",
    "    \n",
    "\n",
    "    # append the data to the empty lists\n",
    "    artists = ([a.string for a in artist])\n",
    "    songs = ([s.string for s in song])\n",
    "    albums = ([a.string for a in album])\n",
    "    album_labels =([a.string for a in album_label])\n",
    "    album_years = ([a.string for a in album_year])\n",
    "    times = ([t.string for t in time])\n",
    "    notes = ([n.string for n in note])\n",
    "\n",
    "    # create a dictionary of the data\n",
    "    mydict = {\n",
    "        'time': times, \n",
    "        'artist':artists, \n",
    "        'song':songs, \n",
    "        'album':albums, \n",
    "        'album_label':album_labels, \n",
    "        'album_year':album_years, \n",
    "        'note':notes,\n",
    "        'url': url}\n",
    "\n",
    "    # create a dataframe from the dictionary\n",
    "    playlist_df = pd.DataFrame(mydict)\n",
    "  \n",
    "    # return the dataframe\n",
    "    return playlist_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "playlist_df = sound_salvation_spider('https://spinitron.com/KDHX/pl/5319093/Sound-Salvation')\n",
    "playlist_df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(sorted_urls[0])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "playlist_df = sound_salvation_spider('https://spinitron.com/' + sorted_urls[0])\n",
    "playlist_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "playlist = []\n",
    "missing_playlists = []\n",
    "\n",
    "sound_salvation_total_playlist = pd.DataFrame()\n",
    "\n",
    "\n",
    "for url in sorted_urls[:]:\n",
    "    try:\n",
    "        playlist.append(sound_salvation_spider('https://spinitron.com/' + url))\n",
    "        sound_salvation_total_playlist = pd.concat(playlist)\n",
    "        sound_salvation_total_playlist.reset_index(drop=True, inplace=True)\n",
    "\n",
    "    except ValueError:\n",
    "        pass\n",
    "        missing_playlists.append(url) \n",
    "        \n",
    "print('Playlists not included: {}'.format(len(missing_playlists)))\n",
    "print('Playlists included: {}'.format(len(playlist)))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sound_salvation_total_playlist"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#output the dataframe to a csv file\n",
    "# sound_salvation_total_playlist.to_csv('sound_salvation_total_playlist.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# https://spinitron.com/KDHX/pl/5410893/Sound-Salvation #is the first url in the list\n",
    "# https://spinitron.com/KDHX/pl/16398320/Sound-Salvation #most recent"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# look for differences in the spans of the urls\n",
    "# working url: 'https://spinitron.com/KDHX/pl/5319093/Sound-Salvation'\n",
    "# url not working: 'https://spinitron.com/KDHX/pl/15311922/Sound-Salvation'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "years = list(range(1930, 2022))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#figure setting\n",
    "plt.figure(figsize=(20,10))\n",
    "\n",
    "plt.xticks(rotation=70)\n",
    "# plt.set_xticks(years)\n",
    "plt.xticks(fontsize=13)\n",
    "plt.title('Sound Salvation Playlist Album Years')\n",
    "plt.xlabel('Album Year')\n",
    "plt.ylabel('Count')\n",
    "\n",
    "#plot\n",
    "sns.countplot(x='album_year', data=sound_salvation_total_playlist, order=sound_salvation_total_playlist['album_year'].value_counts().index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "missing_playlists"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#the todos\n",
    "\n",
    "#find a way to get the playlist urls from the missing_playlists list\n",
    "#then append them to the total playlist df\n",
    "#make a date and dj dataframe\n",
    "#merge the two dataframes\n",
    "#index by the date\n",
    "#drop shows with guest dj\n",
    "#do analysis on the data\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#working:5344299"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def date_and_dj_spider(url):\n",
    "    \"\"\"Perform web scraping for any sound salvation playlist given the available link\"\"\"\n",
    "    #get the data\n",
    "    urls = []\n",
    "    month = []\n",
    "    day = []\n",
    "    year = []\n",
    "    dj = []\n",
    "    \n",
    "    #get the page\n",
    "    headers = {'User-Agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_10_1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/39.0.2171.95 Safari/537.36'}\n",
    "    for page in range(1,35):\n",
    "   \n",
    "    url = 'https://spinitron.com/KDHX/show/126906/Sound-Salvation?page={}'.format(page)\n",
    "    reqs = requests.get(url, headers=headers)\n",
    "    \n",
    "    \n",
    "    rock_soup = BeautifulSoup(reqs.text, 'html.parser')\n",
    "    rock_soup.prettify()\n",
    "    \n",
    "    for link in rock_soup.find_all('a'):\n",
    "        urls.append(link.get('href'))\n",
    "\n",
    "\n",
    "   \n",
    "    dj = rock_soup.find_all('p', attrs={'class':'dj-name'})\n",
    "    date_stamp = rock_soup.find_all('p', attrs={'class':'timeslot'})\n",
    "    url = 'https://spinitron.com/' + sorted_urls[0]\n",
    "    \n",
    "    # append the data to the empty lists\n",
    "    djs = ([d.string for d in dj])\n",
    "    time_slots = ([t.string for t in date_stamp])\n",
    "    # urls = ([url for u in url])\n",
    "\n",
    "    # # create a dictionary of the data\n",
    "    # mydict = {\n",
    "    #     'url': urls,\n",
    "    #     'dj': djs,\n",
    "    #     'time_slot': time_slots\n",
    "    #     }\n",
    "\n",
    "    # # create a dataframe from the dictionary\n",
    "    # playlist_df = pd.DataFrame(mydict)\n",
    "\n",
    "    print(djs)\n",
    "    print(time_slots)\n",
    "    print(url)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "date_and_dj_spider('https://spinitron.com/KDHX/pl/5319093/Sound-Salvation')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "date_dj_df = date_and_dj_spider('https://spinitron.com/' + sorted_urls[0])\n",
    "date_dj_df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# worked: </td><td class=\"spin-text\"><div class=\"spin\"><span class=\"artist\">Katrina & The Waves</span> <span class=\"song\">River Deep, Mountain High (Bonus Track)</span></div><div class=\"info\"><span class=\"release\">Katrina and the Waves 2</span> <span class=\"parenthesis\"><span class=\"label\">CGB Records</span> <span class=\"released\">1985</span></span></div><div class=\"note\">A rollicking take on the Ike &amp; Tina Turner/Phil Spector masterpiece.</div>\n",
    "# didnt:  </td><td class=\"spin-text\"><div class=\"spin\"><span class=\"artist\">Leonard Cohen</span>       <span class=\"song\">Closing Time</span></div><div class=\"info\"><span                            class=\"release\">Live In London</span>          <span class=\"parenthesis\"><span class=\"label\">Columbia Records</span> <span class=\"released\">2009</span></span></div><div class=\"note\">Today's show, which will have plenty of songs he wouldn't necessarily have liked, is dedicated to the memory of Bob Reuter.</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# missing_df = sound_salvation_spider('https://spinitron.com/KDHX/pl/5348184/Sound-Salvation')\n",
    "# missing_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# so look at time\n",
    "# worked: <td class=\"spin-time\"><a href=\"/KDHX/pl/5344299/Sound-Salvation?sp=117515787\">7:00 AM</a></td>\n",
    "# didnt:  <td class=\"spin-time\"><a href=\"/KDHX/pl/5374368/Sound-Salvation?sp=118338438\">7:00 AM</a></td>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def missing_spider(u):\n",
    "    \"\"\"Perform web scraping for any sound salvation playlist given the available link\"\"\"\n",
    "\n",
    "    #get the page\n",
    "    r = requests.get(u)\n",
    "    rock_soup = BeautifulSoup(r.text, 'html')\n",
    "\n",
    "    #get the data\n",
    "    artist = rock_soup.find_all(\"span\", \"artist\")\n",
    "    song = rock_soup.find_all(\"span\", \"song\")\n",
    "    album = rock_soup.find_all(\"span\", \"release\")\n",
    "    album_label = rock_soup.find_all(\"span\", \"label\")\n",
    "    album_year = rock_soup.find_all(\"span\", \"released\")\n",
    "    time = rock_soup.find_all(\"td\", \"spin-time\")\n",
    "    note = rock_soup.find_all('div', attrs={'class':'note'})\n",
    "    url = u\n",
    "\n",
    "    # append the data to the empty lists\n",
    "    artists = ([a.string for a in artist])\n",
    "    songs = ([s.string for s in song])\n",
    "    albums = ([a.string for a in album])\n",
    "    album_labels =([a.string for a in album_label])\n",
    "    album_years = ([a.string for a in album_year])\n",
    "    times = ([t.string for t in time])\n",
    "    notes = ([n.string for n in note])\n",
    "\n",
    "    #trying to append NONE to the empty lists -- turned out to be unnecessary\n",
    "    # artists = ([a.string for a in artist] if not a.string.strip() == '' else artists.append(None) for a in artist)\n",
    "    # songs = ([s.string for s in song] if not s.string.strip() == '' else songs.append(None) for s in song)\n",
    "    # albums = ([al.string for al in album] if not  al.string.strip() == '' else albums.append(None) for al in album)\n",
    "    # album_labels = ([alb.string for alb in album_label] if not alb.string.strip() == '' else album_labels.append(None) for alb in album_label)\n",
    "    # album_years = ([alby.string for alby in album_year] if not alby.string.strip() == ''else album_years.append(None) for alby in album_year)\n",
    "    # times = ([t.string for t in time] if not t.string.strip() == '' else times.append(None) for t in time)\n",
    "    # notes = ([n.string for n in note] if not n.string.strip() == '' else notes.append(None) for n in note)\n",
    "\n",
    "    # create a dictionary of the data\n",
    "    mydict = {\n",
    "        'time': times, \n",
    "        'artist':artists, \n",
    "        'song':songs, \n",
    "        'album':albums, \n",
    "        'album_label':album_labels, \n",
    "        'album_year':album_years, \n",
    "        'note':notes,\n",
    "        'url': url}\n",
    "\n",
    "    #fill in missing values into mydict with None until the arrays are the same length\n",
    "    try:\n",
    "        for key, value in mydict.items():\n",
    "            if len(value) < len(times):\n",
    "                value.extend([None]*(len(times)-len(value)))\n",
    "    except AttributeError:\n",
    "        pass\n",
    "        \n",
    "    # create a dataframe from the dictionary\n",
    "\n",
    "    # print(len(artist))\n",
    "    # print(len(song))\n",
    "    # print(len(album))\n",
    "    # print(len(album_label))\n",
    "    # print(len(album_year))\n",
    "    # print(len(time))\n",
    "    # print(len(note))\n",
    "    # print(len(url))\n",
    "\n",
    "    # print(mydict)\n",
    "    playlist_df = pd.DataFrame(mydict)\n",
    "    return playlist_df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "missing_spider('https://spinitron.com/KDHX/pl/5378109/Sound-Salvation')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#ahhhh! so this is often missing album year or missing notes\n",
    "#so i need to make sure that the length of the list is the same as the length of the time list\n",
    "    \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Playlists not included: 0\n",
      "Playlists included: 1\n",
      "Playlists not included: 0\n",
      "Playlists included: 2\n",
      "Playlists not included: 0\n",
      "Playlists included: 3\n",
      "Playlists not included: 0\n",
      "Playlists included: 4\n",
      "Playlists not included: 0\n",
      "Playlists included: 5\n",
      "Playlists not included: 0\n",
      "Playlists included: 6\n",
      "Playlists not included: 0\n",
      "Playlists included: 7\n",
      "Playlists not included: 0\n",
      "Playlists included: 8\n",
      "Playlists not included: 0\n",
      "Playlists included: 9\n",
      "Playlists not included: 0\n",
      "Playlists included: 10\n",
      "Playlists not included: 0\n",
      "Playlists included: 11\n",
      "Playlists not included: 0\n",
      "Playlists included: 12\n"
     ]
    },
    {
     "ename": "KeyboardInterrupt",
     "evalue": "",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mKeyboardInterrupt\u001b[0m                         Traceback (most recent call last)",
      "\u001b[1;32m/Users/billteller/Desktop/Informatics/sound_salvation/sound_salvation/sound_salvation_v2.ipynb Cell 33\u001b[0m in \u001b[0;36m<cell line: 7>\u001b[0;34m()\u001b[0m\n\u001b[1;32m      <a href='vscode-notebook-cell:/Users/billteller/Desktop/Informatics/sound_salvation/sound_salvation/sound_salvation_v2.ipynb#X64sZmlsZQ%3D%3D?line=6'>7</a>\u001b[0m \u001b[39mfor\u001b[39;00m url \u001b[39min\u001b[39;00m sorted_urls[:]:\n\u001b[1;32m      <a href='vscode-notebook-cell:/Users/billteller/Desktop/Informatics/sound_salvation/sound_salvation/sound_salvation_v2.ipynb#X64sZmlsZQ%3D%3D?line=7'>8</a>\u001b[0m     \u001b[39mtry\u001b[39;00m:\n\u001b[0;32m----> <a href='vscode-notebook-cell:/Users/billteller/Desktop/Informatics/sound_salvation/sound_salvation/sound_salvation_v2.ipynb#X64sZmlsZQ%3D%3D?line=8'>9</a>\u001b[0m         playlist\u001b[39m.\u001b[39mappend(missing_spider(\u001b[39m'\u001b[39;49m\u001b[39mhttps://spinitron.com/\u001b[39;49m\u001b[39m'\u001b[39;49m \u001b[39m+\u001b[39;49m url))\n\u001b[1;32m     <a href='vscode-notebook-cell:/Users/billteller/Desktop/Informatics/sound_salvation/sound_salvation/sound_salvation_v2.ipynb#X64sZmlsZQ%3D%3D?line=9'>10</a>\u001b[0m         sound_salvation_fillin_playlists \u001b[39m=\u001b[39m pd\u001b[39m.\u001b[39mconcat(playlist)\n\u001b[1;32m     <a href='vscode-notebook-cell:/Users/billteller/Desktop/Informatics/sound_salvation/sound_salvation/sound_salvation_v2.ipynb#X64sZmlsZQ%3D%3D?line=10'>11</a>\u001b[0m         sound_salvation_fillin_playlists\u001b[39m.\u001b[39mreset_index(drop\u001b[39m=\u001b[39m\u001b[39mTrue\u001b[39;00m, inplace\u001b[39m=\u001b[39m\u001b[39mTrue\u001b[39;00m)\n",
      "\u001b[1;32m/Users/billteller/Desktop/Informatics/sound_salvation/sound_salvation/sound_salvation_v2.ipynb Cell 33\u001b[0m in \u001b[0;36mmissing_spider\u001b[0;34m(u)\u001b[0m\n\u001b[1;32m      <a href='vscode-notebook-cell:/Users/billteller/Desktop/Informatics/sound_salvation/sound_salvation/sound_salvation_v2.ipynb#X64sZmlsZQ%3D%3D?line=1'>2</a>\u001b[0m \u001b[39m\"\"\"Perform web scraping for any sound salvation playlist given the available link\"\"\"\u001b[39;00m\n\u001b[1;32m      <a href='vscode-notebook-cell:/Users/billteller/Desktop/Informatics/sound_salvation/sound_salvation/sound_salvation_v2.ipynb#X64sZmlsZQ%3D%3D?line=3'>4</a>\u001b[0m \u001b[39m#get the page\u001b[39;00m\n\u001b[0;32m----> <a href='vscode-notebook-cell:/Users/billteller/Desktop/Informatics/sound_salvation/sound_salvation/sound_salvation_v2.ipynb#X64sZmlsZQ%3D%3D?line=4'>5</a>\u001b[0m r \u001b[39m=\u001b[39m requests\u001b[39m.\u001b[39;49mget(u)\n\u001b[1;32m      <a href='vscode-notebook-cell:/Users/billteller/Desktop/Informatics/sound_salvation/sound_salvation/sound_salvation_v2.ipynb#X64sZmlsZQ%3D%3D?line=5'>6</a>\u001b[0m rock_soup \u001b[39m=\u001b[39m BeautifulSoup(r\u001b[39m.\u001b[39mtext, \u001b[39m'\u001b[39m\u001b[39mhtml\u001b[39m\u001b[39m'\u001b[39m)\n\u001b[1;32m      <a href='vscode-notebook-cell:/Users/billteller/Desktop/Informatics/sound_salvation/sound_salvation/sound_salvation_v2.ipynb#X64sZmlsZQ%3D%3D?line=7'>8</a>\u001b[0m \u001b[39m#get the data\u001b[39;00m\n",
      "File \u001b[0;32m~/opt/anaconda3/lib/python3.9/site-packages/requests/api.py:73\u001b[0m, in \u001b[0;36mget\u001b[0;34m(url, params, **kwargs)\u001b[0m\n\u001b[1;32m     62\u001b[0m \u001b[39mdef\u001b[39;00m \u001b[39mget\u001b[39m(url, params\u001b[39m=\u001b[39m\u001b[39mNone\u001b[39;00m, \u001b[39m*\u001b[39m\u001b[39m*\u001b[39mkwargs):\n\u001b[1;32m     63\u001b[0m     \u001b[39mr\u001b[39m\u001b[39m\"\"\"Sends a GET request.\u001b[39;00m\n\u001b[1;32m     64\u001b[0m \n\u001b[1;32m     65\u001b[0m \u001b[39m    :param url: URL for the new :class:`Request` object.\u001b[39;00m\n\u001b[0;32m   (...)\u001b[0m\n\u001b[1;32m     70\u001b[0m \u001b[39m    :rtype: requests.Response\u001b[39;00m\n\u001b[1;32m     71\u001b[0m \u001b[39m    \"\"\"\u001b[39;00m\n\u001b[0;32m---> 73\u001b[0m     \u001b[39mreturn\u001b[39;00m request(\u001b[39m\"\u001b[39;49m\u001b[39mget\u001b[39;49m\u001b[39m\"\u001b[39;49m, url, params\u001b[39m=\u001b[39;49mparams, \u001b[39m*\u001b[39;49m\u001b[39m*\u001b[39;49mkwargs)\n",
      "File \u001b[0;32m~/opt/anaconda3/lib/python3.9/site-packages/requests/api.py:59\u001b[0m, in \u001b[0;36mrequest\u001b[0;34m(method, url, **kwargs)\u001b[0m\n\u001b[1;32m     55\u001b[0m \u001b[39m# By using the 'with' statement we are sure the session is closed, thus we\u001b[39;00m\n\u001b[1;32m     56\u001b[0m \u001b[39m# avoid leaving sockets open which can trigger a ResourceWarning in some\u001b[39;00m\n\u001b[1;32m     57\u001b[0m \u001b[39m# cases, and look like a memory leak in others.\u001b[39;00m\n\u001b[1;32m     58\u001b[0m \u001b[39mwith\u001b[39;00m sessions\u001b[39m.\u001b[39mSession() \u001b[39mas\u001b[39;00m session:\n\u001b[0;32m---> 59\u001b[0m     \u001b[39mreturn\u001b[39;00m session\u001b[39m.\u001b[39;49mrequest(method\u001b[39m=\u001b[39;49mmethod, url\u001b[39m=\u001b[39;49murl, \u001b[39m*\u001b[39;49m\u001b[39m*\u001b[39;49mkwargs)\n",
      "File \u001b[0;32m~/opt/anaconda3/lib/python3.9/site-packages/requests/sessions.py:587\u001b[0m, in \u001b[0;36mSession.request\u001b[0;34m(self, method, url, params, data, headers, cookies, files, auth, timeout, allow_redirects, proxies, hooks, stream, verify, cert, json)\u001b[0m\n\u001b[1;32m    582\u001b[0m send_kwargs \u001b[39m=\u001b[39m {\n\u001b[1;32m    583\u001b[0m     \u001b[39m\"\u001b[39m\u001b[39mtimeout\u001b[39m\u001b[39m\"\u001b[39m: timeout,\n\u001b[1;32m    584\u001b[0m     \u001b[39m\"\u001b[39m\u001b[39mallow_redirects\u001b[39m\u001b[39m\"\u001b[39m: allow_redirects,\n\u001b[1;32m    585\u001b[0m }\n\u001b[1;32m    586\u001b[0m send_kwargs\u001b[39m.\u001b[39mupdate(settings)\n\u001b[0;32m--> 587\u001b[0m resp \u001b[39m=\u001b[39m \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49msend(prep, \u001b[39m*\u001b[39;49m\u001b[39m*\u001b[39;49msend_kwargs)\n\u001b[1;32m    589\u001b[0m \u001b[39mreturn\u001b[39;00m resp\n",
      "File \u001b[0;32m~/opt/anaconda3/lib/python3.9/site-packages/requests/sessions.py:701\u001b[0m, in \u001b[0;36mSession.send\u001b[0;34m(self, request, **kwargs)\u001b[0m\n\u001b[1;32m    698\u001b[0m start \u001b[39m=\u001b[39m preferred_clock()\n\u001b[1;32m    700\u001b[0m \u001b[39m# Send the request\u001b[39;00m\n\u001b[0;32m--> 701\u001b[0m r \u001b[39m=\u001b[39m adapter\u001b[39m.\u001b[39;49msend(request, \u001b[39m*\u001b[39;49m\u001b[39m*\u001b[39;49mkwargs)\n\u001b[1;32m    703\u001b[0m \u001b[39m# Total elapsed time of the request (approximately)\u001b[39;00m\n\u001b[1;32m    704\u001b[0m elapsed \u001b[39m=\u001b[39m preferred_clock() \u001b[39m-\u001b[39m start\n",
      "File \u001b[0;32m~/opt/anaconda3/lib/python3.9/site-packages/requests/adapters.py:489\u001b[0m, in \u001b[0;36mHTTPAdapter.send\u001b[0;34m(self, request, stream, timeout, verify, cert, proxies)\u001b[0m\n\u001b[1;32m    487\u001b[0m \u001b[39mtry\u001b[39;00m:\n\u001b[1;32m    488\u001b[0m     \u001b[39mif\u001b[39;00m \u001b[39mnot\u001b[39;00m chunked:\n\u001b[0;32m--> 489\u001b[0m         resp \u001b[39m=\u001b[39m conn\u001b[39m.\u001b[39;49murlopen(\n\u001b[1;32m    490\u001b[0m             method\u001b[39m=\u001b[39;49mrequest\u001b[39m.\u001b[39;49mmethod,\n\u001b[1;32m    491\u001b[0m             url\u001b[39m=\u001b[39;49murl,\n\u001b[1;32m    492\u001b[0m             body\u001b[39m=\u001b[39;49mrequest\u001b[39m.\u001b[39;49mbody,\n\u001b[1;32m    493\u001b[0m             headers\u001b[39m=\u001b[39;49mrequest\u001b[39m.\u001b[39;49mheaders,\n\u001b[1;32m    494\u001b[0m             redirect\u001b[39m=\u001b[39;49m\u001b[39mFalse\u001b[39;49;00m,\n\u001b[1;32m    495\u001b[0m             assert_same_host\u001b[39m=\u001b[39;49m\u001b[39mFalse\u001b[39;49;00m,\n\u001b[1;32m    496\u001b[0m             preload_content\u001b[39m=\u001b[39;49m\u001b[39mFalse\u001b[39;49;00m,\n\u001b[1;32m    497\u001b[0m             decode_content\u001b[39m=\u001b[39;49m\u001b[39mFalse\u001b[39;49;00m,\n\u001b[1;32m    498\u001b[0m             retries\u001b[39m=\u001b[39;49m\u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49mmax_retries,\n\u001b[1;32m    499\u001b[0m             timeout\u001b[39m=\u001b[39;49mtimeout,\n\u001b[1;32m    500\u001b[0m         )\n\u001b[1;32m    502\u001b[0m     \u001b[39m# Send the request.\u001b[39;00m\n\u001b[1;32m    503\u001b[0m     \u001b[39melse\u001b[39;00m:\n\u001b[1;32m    504\u001b[0m         \u001b[39mif\u001b[39;00m \u001b[39mhasattr\u001b[39m(conn, \u001b[39m\"\u001b[39m\u001b[39mproxy_pool\u001b[39m\u001b[39m\"\u001b[39m):\n",
      "File \u001b[0;32m~/opt/anaconda3/lib/python3.9/site-packages/urllib3/connectionpool.py:703\u001b[0m, in \u001b[0;36mHTTPConnectionPool.urlopen\u001b[0;34m(self, method, url, body, headers, retries, redirect, assert_same_host, timeout, pool_timeout, release_conn, chunked, body_pos, **response_kw)\u001b[0m\n\u001b[1;32m    700\u001b[0m     \u001b[39mself\u001b[39m\u001b[39m.\u001b[39m_prepare_proxy(conn)\n\u001b[1;32m    702\u001b[0m \u001b[39m# Make the request on the httplib connection object.\u001b[39;00m\n\u001b[0;32m--> 703\u001b[0m httplib_response \u001b[39m=\u001b[39m \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m_make_request(\n\u001b[1;32m    704\u001b[0m     conn,\n\u001b[1;32m    705\u001b[0m     method,\n\u001b[1;32m    706\u001b[0m     url,\n\u001b[1;32m    707\u001b[0m     timeout\u001b[39m=\u001b[39;49mtimeout_obj,\n\u001b[1;32m    708\u001b[0m     body\u001b[39m=\u001b[39;49mbody,\n\u001b[1;32m    709\u001b[0m     headers\u001b[39m=\u001b[39;49mheaders,\n\u001b[1;32m    710\u001b[0m     chunked\u001b[39m=\u001b[39;49mchunked,\n\u001b[1;32m    711\u001b[0m )\n\u001b[1;32m    713\u001b[0m \u001b[39m# If we're going to release the connection in ``finally:``, then\u001b[39;00m\n\u001b[1;32m    714\u001b[0m \u001b[39m# the response doesn't need to know about the connection. Otherwise\u001b[39;00m\n\u001b[1;32m    715\u001b[0m \u001b[39m# it will also try to release it and we'll have a double-release\u001b[39;00m\n\u001b[1;32m    716\u001b[0m \u001b[39m# mess.\u001b[39;00m\n\u001b[1;32m    717\u001b[0m response_conn \u001b[39m=\u001b[39m conn \u001b[39mif\u001b[39;00m \u001b[39mnot\u001b[39;00m release_conn \u001b[39melse\u001b[39;00m \u001b[39mNone\u001b[39;00m\n",
      "File \u001b[0;32m~/opt/anaconda3/lib/python3.9/site-packages/urllib3/connectionpool.py:386\u001b[0m, in \u001b[0;36mHTTPConnectionPool._make_request\u001b[0;34m(self, conn, method, url, timeout, chunked, **httplib_request_kw)\u001b[0m\n\u001b[1;32m    384\u001b[0m \u001b[39m# Trigger any extra validation we need to do.\u001b[39;00m\n\u001b[1;32m    385\u001b[0m \u001b[39mtry\u001b[39;00m:\n\u001b[0;32m--> 386\u001b[0m     \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m_validate_conn(conn)\n\u001b[1;32m    387\u001b[0m \u001b[39mexcept\u001b[39;00m (SocketTimeout, BaseSSLError) \u001b[39mas\u001b[39;00m e:\n\u001b[1;32m    388\u001b[0m     \u001b[39m# Py2 raises this as a BaseSSLError, Py3 raises it as socket timeout.\u001b[39;00m\n\u001b[1;32m    389\u001b[0m     \u001b[39mself\u001b[39m\u001b[39m.\u001b[39m_raise_timeout(err\u001b[39m=\u001b[39me, url\u001b[39m=\u001b[39murl, timeout_value\u001b[39m=\u001b[39mconn\u001b[39m.\u001b[39mtimeout)\n",
      "File \u001b[0;32m~/opt/anaconda3/lib/python3.9/site-packages/urllib3/connectionpool.py:1040\u001b[0m, in \u001b[0;36mHTTPSConnectionPool._validate_conn\u001b[0;34m(self, conn)\u001b[0m\n\u001b[1;32m   1038\u001b[0m \u001b[39m# Force connect early to allow us to validate the connection.\u001b[39;00m\n\u001b[1;32m   1039\u001b[0m \u001b[39mif\u001b[39;00m \u001b[39mnot\u001b[39;00m \u001b[39mgetattr\u001b[39m(conn, \u001b[39m\"\u001b[39m\u001b[39msock\u001b[39m\u001b[39m\"\u001b[39m, \u001b[39mNone\u001b[39;00m):  \u001b[39m# AppEngine might not have  `.sock`\u001b[39;00m\n\u001b[0;32m-> 1040\u001b[0m     conn\u001b[39m.\u001b[39;49mconnect()\n\u001b[1;32m   1042\u001b[0m \u001b[39mif\u001b[39;00m \u001b[39mnot\u001b[39;00m conn\u001b[39m.\u001b[39mis_verified:\n\u001b[1;32m   1043\u001b[0m     warnings\u001b[39m.\u001b[39mwarn(\n\u001b[1;32m   1044\u001b[0m         (\n\u001b[1;32m   1045\u001b[0m             \u001b[39m\"\u001b[39m\u001b[39mUnverified HTTPS request is being made to host \u001b[39m\u001b[39m'\u001b[39m\u001b[39m%s\u001b[39;00m\u001b[39m'\u001b[39m\u001b[39m. \u001b[39m\u001b[39m\"\u001b[39m\n\u001b[0;32m   (...)\u001b[0m\n\u001b[1;32m   1050\u001b[0m         InsecureRequestWarning,\n\u001b[1;32m   1051\u001b[0m     )\n",
      "File \u001b[0;32m~/opt/anaconda3/lib/python3.9/site-packages/urllib3/connection.py:358\u001b[0m, in \u001b[0;36mHTTPSConnection.connect\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    356\u001b[0m \u001b[39mdef\u001b[39;00m \u001b[39mconnect\u001b[39m(\u001b[39mself\u001b[39m):\n\u001b[1;32m    357\u001b[0m     \u001b[39m# Add certificate verification\u001b[39;00m\n\u001b[0;32m--> 358\u001b[0m     \u001b[39mself\u001b[39m\u001b[39m.\u001b[39msock \u001b[39m=\u001b[39m conn \u001b[39m=\u001b[39m \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m_new_conn()\n\u001b[1;32m    359\u001b[0m     hostname \u001b[39m=\u001b[39m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39mhost\n\u001b[1;32m    360\u001b[0m     tls_in_tls \u001b[39m=\u001b[39m \u001b[39mFalse\u001b[39;00m\n",
      "File \u001b[0;32m~/opt/anaconda3/lib/python3.9/site-packages/urllib3/connection.py:174\u001b[0m, in \u001b[0;36mHTTPConnection._new_conn\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    171\u001b[0m     extra_kw[\u001b[39m\"\u001b[39m\u001b[39msocket_options\u001b[39m\u001b[39m\"\u001b[39m] \u001b[39m=\u001b[39m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39msocket_options\n\u001b[1;32m    173\u001b[0m \u001b[39mtry\u001b[39;00m:\n\u001b[0;32m--> 174\u001b[0m     conn \u001b[39m=\u001b[39m connection\u001b[39m.\u001b[39;49mcreate_connection(\n\u001b[1;32m    175\u001b[0m         (\u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m_dns_host, \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49mport), \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49mtimeout, \u001b[39m*\u001b[39;49m\u001b[39m*\u001b[39;49mextra_kw\n\u001b[1;32m    176\u001b[0m     )\n\u001b[1;32m    178\u001b[0m \u001b[39mexcept\u001b[39;00m SocketTimeout:\n\u001b[1;32m    179\u001b[0m     \u001b[39mraise\u001b[39;00m ConnectTimeoutError(\n\u001b[1;32m    180\u001b[0m         \u001b[39mself\u001b[39m,\n\u001b[1;32m    181\u001b[0m         \u001b[39m\"\u001b[39m\u001b[39mConnection to \u001b[39m\u001b[39m%s\u001b[39;00m\u001b[39m timed out. (connect timeout=\u001b[39m\u001b[39m%s\u001b[39;00m\u001b[39m)\u001b[39m\u001b[39m\"\u001b[39m\n\u001b[1;32m    182\u001b[0m         \u001b[39m%\u001b[39m (\u001b[39mself\u001b[39m\u001b[39m.\u001b[39mhost, \u001b[39mself\u001b[39m\u001b[39m.\u001b[39mtimeout),\n\u001b[1;32m    183\u001b[0m     )\n",
      "File \u001b[0;32m~/opt/anaconda3/lib/python3.9/site-packages/urllib3/util/connection.py:85\u001b[0m, in \u001b[0;36mcreate_connection\u001b[0;34m(address, timeout, source_address, socket_options)\u001b[0m\n\u001b[1;32m     83\u001b[0m     \u001b[39mif\u001b[39;00m source_address:\n\u001b[1;32m     84\u001b[0m         sock\u001b[39m.\u001b[39mbind(source_address)\n\u001b[0;32m---> 85\u001b[0m     sock\u001b[39m.\u001b[39;49mconnect(sa)\n\u001b[1;32m     86\u001b[0m     \u001b[39mreturn\u001b[39;00m sock\n\u001b[1;32m     88\u001b[0m \u001b[39mexcept\u001b[39;00m socket\u001b[39m.\u001b[39merror \u001b[39mas\u001b[39;00m e:\n",
      "\u001b[0;31mKeyboardInterrupt\u001b[0m: "
     ]
    }
   ],
   "source": [
    "playlist = []\n",
    "missing_playlists = []\n",
    "\n",
    "sound_salvation_fillin_playlists = pd.DataFrame()\n",
    "\n",
    "\n",
    "for url in sorted_urls[:]:\n",
    "    try:\n",
    "        playlist.append(missing_spider('https://spinitron.com/' + url))\n",
    "        sound_salvation_fillin_playlists = pd.concat(playlist)\n",
    "        sound_salvation_fillin_playlists.reset_index(drop=True, inplace=True)\n",
    "\n",
    "    except ValueError:\n",
    "        pass\n",
    "        missing_playlists.append(url) \n",
    "        \n",
    "    print('Playlists not included: {}'.format(len(missing_playlists)))\n",
    "    print('Playlists included: {}'.format(len(playlist)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sound_salvation_fillin_playlists"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#save to csv\n",
    "sound_salvation_fillin_playlists.to_csv('sound_salvation_fillin_playlists.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "656\n",
      "656\n",
      "656\n",
      "656\n",
      "656\n"
     ]
    }
   ],
   "source": [
    "\n",
    "def date_and_dj_spider():\n",
    "    \"\"\"Perform web scraping for any sound salvation playlist given the available link\"\"\"\n",
    "\n",
    "urls = []\n",
    "months = []\n",
    "days = []\n",
    "years = []\n",
    "djs = []\n",
    "\n",
    "#get the page\n",
    "headers = {'User-Agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_10_1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/39.0.2171.95 Safari/537.36'}\n",
    "for page in range(1,35):\n",
    "\n",
    "    url = 'https://spinitron.com/KDHX/show/126906/Sound-Salvation?page={}'.format(page)\n",
    "    reqs = requests.get(url, headers=headers)\n",
    "    \n",
    "    rock_soup = BeautifulSoup(reqs.text, 'html')\n",
    "    # rock_soup.prettify()\n",
    "    \n",
    "    #get the data \n",
    "    for link in rock_soup.find_all(\"div\", {'class':'block'}):\n",
    "\n",
    "        djs.append(link.find('div', {'class':'dj'}).string)\n",
    "        months.append(link.find('span', attrs={'class':'month'}).string)\n",
    "        days.append(link.find('span', attrs={'class':'day'}).string)\n",
    "        years.append(link.find('span', attrs={'class':'year'}).string)\n",
    "        urls.append('https://spinitron.com/' + link.find('a').get('href'))\n",
    "\n",
    "# create a dictionary of the data\n",
    "mydict = {\n",
    "    'url': urls,\n",
    "    'dj': djs,\n",
    "    'month': months,\n",
    "    'day': days,\n",
    "    'year': years\n",
    "    }\n",
    "\n",
    "#fill in missing values into mydict with None until the arrays are the same length\n",
    "try:\n",
    "    for key, value in mydict.items():\n",
    "        if len(value) < len(days):\n",
    "            value.extend([None]*(len(days)-len(value)))\n",
    "except AttributeError:\n",
    "    pass\n",
    "    \n",
    "print(len(djs))\n",
    "print(len(months))\n",
    "print(len(days))\n",
    "print(len(years))\n",
    "print(len(urls))\n",
    "\n",
    "\n",
    "    \n",
    "# create a dataframe from the dictionary\n",
    "date_and_dj_df = pd.DataFrame(mydict)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>url</th>\n",
       "      <th>dj</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>https://spinitron.com//KDHX/pl/16451085/Sound-...</td>\n",
       "      <td>Steve Pick</td>\n",
       "      <td>Sep</td>\n",
       "      <td>23rd</td>\n",
       "      <td>2022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>https://spinitron.com//KDHX/pl/16424558/Sound-...</td>\n",
       "      <td>Steve Pick</td>\n",
       "      <td>Sep</td>\n",
       "      <td>16th</td>\n",
       "      <td>2022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>https://spinitron.com//KDHX/pl/16398320/Sound-...</td>\n",
       "      <td>Steve Pick</td>\n",
       "      <td>Sep</td>\n",
       "      <td>9th</td>\n",
       "      <td>2022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>https://spinitron.com//KDHX/pl/16372205/Sound-...</td>\n",
       "      <td>Steve Pick</td>\n",
       "      <td>Sep</td>\n",
       "      <td>2nd</td>\n",
       "      <td>2022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>https://spinitron.com//KDHX/pl/16346134/Sound-...</td>\n",
       "      <td>Steve Pick</td>\n",
       "      <td>Aug</td>\n",
       "      <td>26th</td>\n",
       "      <td>2022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651</th>\n",
       "      <td>https://spinitron.com//KDHX/pl/5409969/Sound-S...</td>\n",
       "      <td>Steve Pick</td>\n",
       "      <td>Aug</td>\n",
       "      <td>27th</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>652</th>\n",
       "      <td>https://spinitron.com//KDHX/pl/5410206/Sound-S...</td>\n",
       "      <td>Steve Pick</td>\n",
       "      <td>Aug</td>\n",
       "      <td>20th</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>653</th>\n",
       "      <td>https://spinitron.com//KDHX/pl/5410428/Sound-S...</td>\n",
       "      <td>Steve Pick</td>\n",
       "      <td>Aug</td>\n",
       "      <td>13th</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>654</th>\n",
       "      <td>https://spinitron.com//KDHX/pl/5410671/Sound-S...</td>\n",
       "      <td>Steve Pick</td>\n",
       "      <td>Aug</td>\n",
       "      <td>6th</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>655</th>\n",
       "      <td>https://spinitron.com//KDHX/pl/5410893/Sound-S...</td>\n",
       "      <td>Steve Pick</td>\n",
       "      <td>Jul</td>\n",
       "      <td>30th</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>656 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   url          dj month  \\\n",
       "0    https://spinitron.com//KDHX/pl/16451085/Sound-...  Steve Pick   Sep   \n",
       "1    https://spinitron.com//KDHX/pl/16424558/Sound-...  Steve Pick   Sep   \n",
       "2    https://spinitron.com//KDHX/pl/16398320/Sound-...  Steve Pick   Sep   \n",
       "3    https://spinitron.com//KDHX/pl/16372205/Sound-...  Steve Pick   Sep   \n",
       "4    https://spinitron.com//KDHX/pl/16346134/Sound-...  Steve Pick   Aug   \n",
       "..                                                 ...         ...   ...   \n",
       "651  https://spinitron.com//KDHX/pl/5409969/Sound-S...  Steve Pick   Aug   \n",
       "652  https://spinitron.com//KDHX/pl/5410206/Sound-S...  Steve Pick   Aug   \n",
       "653  https://spinitron.com//KDHX/pl/5410428/Sound-S...  Steve Pick   Aug   \n",
       "654  https://spinitron.com//KDHX/pl/5410671/Sound-S...  Steve Pick   Aug   \n",
       "655  https://spinitron.com//KDHX/pl/5410893/Sound-S...  Steve Pick   Jul   \n",
       "\n",
       "      day  year  \n",
       "0    23rd  2022  \n",
       "1    16th  2022  \n",
       "2     9th  2022  \n",
       "3     2nd  2022  \n",
       "4    26th  2022  \n",
       "..    ...   ...  \n",
       "651  27th  2010  \n",
       "652  20th  2010  \n",
       "653  13th  2010  \n",
       "654   6th  2010  \n",
       "655  30th  2010  \n",
       "\n",
       "[656 rows x 5 columns]"
      ]
     },
     "execution_count": 134,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "date_and_dj_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>url</th>\n",
       "      <th>dj</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>https://spinitron.com//KDHX/pl/16451085/Sound-...</td>\n",
       "      <td>Steve Pick</td>\n",
       "      <td>2022-09-23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>https://spinitron.com//KDHX/pl/16424558/Sound-...</td>\n",
       "      <td>Steve Pick</td>\n",
       "      <td>2022-09-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>https://spinitron.com//KDHX/pl/16398320/Sound-...</td>\n",
       "      <td>Steve Pick</td>\n",
       "      <td>2022-09-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>https://spinitron.com//KDHX/pl/16372205/Sound-...</td>\n",
       "      <td>Steve Pick</td>\n",
       "      <td>2022-09-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>https://spinitron.com//KDHX/pl/16346134/Sound-...</td>\n",
       "      <td>Steve Pick</td>\n",
       "      <td>2022-08-26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651</th>\n",
       "      <td>https://spinitron.com//KDHX/pl/5409969/Sound-S...</td>\n",
       "      <td>Steve Pick</td>\n",
       "      <td>2010-08-27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>652</th>\n",
       "      <td>https://spinitron.com//KDHX/pl/5410206/Sound-S...</td>\n",
       "      <td>Steve Pick</td>\n",
       "      <td>2010-08-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>653</th>\n",
       "      <td>https://spinitron.com//KDHX/pl/5410428/Sound-S...</td>\n",
       "      <td>Steve Pick</td>\n",
       "      <td>2010-08-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>654</th>\n",
       "      <td>https://spinitron.com//KDHX/pl/5410671/Sound-S...</td>\n",
       "      <td>Steve Pick</td>\n",
       "      <td>2010-08-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>655</th>\n",
       "      <td>https://spinitron.com//KDHX/pl/5410893/Sound-S...</td>\n",
       "      <td>Steve Pick</td>\n",
       "      <td>2010-07-30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>656 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   url          dj       date\n",
       "0    https://spinitron.com//KDHX/pl/16451085/Sound-...  Steve Pick 2022-09-23\n",
       "1    https://spinitron.com//KDHX/pl/16424558/Sound-...  Steve Pick 2022-09-16\n",
       "2    https://spinitron.com//KDHX/pl/16398320/Sound-...  Steve Pick 2022-09-09\n",
       "3    https://spinitron.com//KDHX/pl/16372205/Sound-...  Steve Pick 2022-09-02\n",
       "4    https://spinitron.com//KDHX/pl/16346134/Sound-...  Steve Pick 2022-08-26\n",
       "..                                                 ...         ...        ...\n",
       "651  https://spinitron.com//KDHX/pl/5409969/Sound-S...  Steve Pick 2010-08-27\n",
       "652  https://spinitron.com//KDHX/pl/5410206/Sound-S...  Steve Pick 2010-08-20\n",
       "653  https://spinitron.com//KDHX/pl/5410428/Sound-S...  Steve Pick 2010-08-13\n",
       "654  https://spinitron.com//KDHX/pl/5410671/Sound-S...  Steve Pick 2010-08-06\n",
       "655  https://spinitron.com//KDHX/pl/5410893/Sound-S...  Steve Pick 2010-07-30\n",
       "\n",
       "[656 rows x 3 columns]"
      ]
     },
     "execution_count": 135,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#make a new column for the date in the date_and_dj_df as a datetime object\n",
    "date_and_dj_df['date'] = pd.to_datetime(date_and_dj_df['month'] + ' ' + date_and_dj_df['day'] + ' ' + date_and_dj_df['year'])\n",
    "#drop the month, day, and year columns\n",
    "date_and_dj_df.drop(['month', 'day', 'year'], axis=1, inplace=True)\n",
    "#set the index to the date\n",
    "# date_and_dj_df.set_index('date', inplace=True)\n",
    "\n",
    "date_and_dj_df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "metadata": {},
   "outputs": [],
   "source": [
    "#save date_and_dj_df to csv\n",
    "date_and_dj_df.to_csv('date_and_dj_df.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#merge the two dataframes, fill forward the dj column\n",
    "sound_salvation_fillin_playlists = pd.merge(sound_salvation_fillin_playlists, date_and_dj_df, on='url', how='left')\n",
    "sound_salvation_fillin_playlists['dj'].fillna(method='ffill', inplace=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sound_salvation_fillin_playlists"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.9.12 ('base')",
   "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.9.12"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "1d38398c116bfb374f0cb2cfc6d44bb5de3ba855082b3b30045272c07de0ac51"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}