Billboard.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "source": [
    "# Instructions"
   ],
   "metadata": {
    "id": "luLEc3IvhLG4"
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "\n",
    "\n",
    "1.   Import the excel file with the song data by clicking the file icon on the left and dragging the file over the sidebar.\n",
    "2.   Leave the \"Setup\" and \"Functions\" sections untoggled, and click the play button inside the brackets to the left of \"# cells hidden\" for each section.\n",
    "3. If the \"Code\" section is untoggled, toggle it so you can see each block of code.\n",
    "4. From top to bottom, click the play button on the top left of each cell\n",
    "5. Cell is done running when green check shows up to the left of the cell (keep in minds, some cells will take minutes to run because they are retrieving data over the internet).\n",
    "6. Enter input when required\n",
    "7. Output file will show up on the sidebar on the left\n",
    "\n"
   ],
   "metadata": {
    "id": "HYZCgIi9hRVJ"
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "# Methodology"
   ],
   "metadata": {
    "id": "dpEl_r7LdtF_"
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "To get the song data needed for the search (i.e., the song title and the artist), I used the Spotify link. Consequently, if a song didn't have a Spotify link, I didn't search for its charting history.\n",
    "\n",
    "For the songs that I could find, I used Music Charts Archive to pull the data because it was the only website I could find that could provide the start date of every week a song was on the Hot 100. The Music Charts Archive page for Drunk in Love by Beyonce is [here](https://musicchartsarchive.com/singles/beyonce/drunk-in-love) so you can see what I'm referring to. For every song I was able to find on this website, I checked to see if the last week the song charted occurred before the date the survey was started. If this was the case, I just used the \"Peak Position\" and \"Number of Weeks on Chart\" information that was present on the page. If this was not the case, I calculated the number of weeks and peak position using the information in the table.\n",
    "\n",
    "I noticed that Music Charts Archive doesn't have a dedicated page for every song that has ever charted. Because of this, if I couldn't find the song on the website, I double checked for the song on the artist's Billboard chart history page (example [here](https://www.billboard.com/artist/waka-flocka-flame/chart-history/hsi/)). Unfortunately, the only information on this page is the peak position of the song, the week it debuted, the week it peaked, and the number of weeks it was on the chart. This means I can't be 100% certain that the song completed its chart run before the survey was started. I assumed that the songs charted consecutively starting from the week it debuted, which I know is not always the case, so I added 5 weeks as a margin of error.\n",
    "\n",
    "If there's a song with a spotify link but no url, there was neither a Music Charts Archive page nor a Billboard page for the artist"
   ],
   "metadata": {
    "id": "fWC-WjqqdviY"
   }
  },
  {
   "cell_type": "markdown",
   "source": "# Setup",
   "metadata": {
    "id": "niQuYGfvWZMB"
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "zC2EwUOTKCb8"
   },
   "outputs": [],
   "source": [
    "import requests\n",
    "import re\n",
    "import pandas as pd\n",
    "import difflib"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "rWwgiCg6Gl1R"
   },
   "outputs": [],
   "source": [
    "def get_authorization_headers():\n",
    "  AUTH_URL = 'https://accounts.spotify.com/api/token'\n",
    "  CLIENT_ID = '*'\n",
    "  CLIENT_SECRET = '*'\n",
    "\n",
    "  # POST\n",
    "  auth_response = requests.post(AUTH_URL, {\n",
    "      'grant_type': 'client_credentials',\n",
    "      'client_id': CLIENT_ID,\n",
    "      'client_secret': CLIENT_SECRET,\n",
    "  })\n",
    "\n",
    "  # convert the response to JSON\n",
    "  auth_response_data = auth_response.json()\n",
    "\n",
    "  # save the access token\n",
    "  access_token = auth_response_data['access_token']\n",
    "\n",
    "  headers = {\n",
    "      'Authorization': 'Bearer {token}'.format(token=access_token)\n",
    "  }\n",
    "\n",
    "  return headers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "Ypol95wfzb0X"
   },
   "outputs": [],
   "source": [
    "global tracked"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "v2RPumx97FbU"
   },
   "source": [
    "# Functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "TgvqN21R1aJQ"
   },
   "outputs": [],
   "source": [
    "def get_title_start_and_end_index(html_text):\n",
    "  start = html_text.find(\"<li class=\\\"lrv-u-width-100p\")\n",
    "  end = html_text[start:].find('</li>')\n",
    "  return (start, end)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "3LpKQfZa8mKH"
   },
   "outputs": [],
   "source": [
    "def get_stat_start_and_end_index(html_text):\n",
    "  start = html_text.find(\"<ul class=\\\"lrv-a-unstyle-list\")\n",
    "  end = html_text[start:].find('</ul>')\n",
    "  return (start, end)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "3iw1H9111xwb"
   },
   "outputs": [],
   "source": [
    "def sanitize_html(html_text):\n",
    "  text = re.sub('<[^<]+?>', '', html_text)\n",
    "  return text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "2x9P7hmH2B6e"
   },
   "outputs": [],
   "source": [
    "def remove_tabs(text):\n",
    "  text = text.replace(\"\\t\", \"\")\n",
    "  return text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "G-G_kJe6BBRB"
   },
   "outputs": [],
   "source": [
    "def process_text(text):\n",
    "  text = text.replace(\"&#039;\", \"'\")\n",
    "  text = text.replace(\"&amp;\", \"&\")\n",
    "  return text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "NVuCqtCP6ZWK"
   },
   "outputs": [],
   "source": [
    "def extract_title_and_artists(text):\n",
    "  text_list = text.splitlines()\n",
    "  res = []\n",
    "  for t in text_list:\n",
    "    if len(t) > 0:\n",
    "      res.append(t)\n",
    "  return res"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "_0h1UrzC-Puy"
   },
   "outputs": [],
   "source": [
    "def extract_stats(text):\n",
    "  text_list = text.splitlines()\n",
    "  res = []\n",
    "  for t in text_list:\n",
    "    if len(t) > 0 and len(t) < 4:\n",
    "      res.append(t)\n",
    "    if len(res) == 3:\n",
    "      return res\n",
    "  return res"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "Sr63l1LJDD7U"
   },
   "outputs": [],
   "source": [
    "def get_raw(text):\n",
    "  return re.sub(r'[^a-zA-Z]+', '', text).lower()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "3QBKjQavEWsH"
   },
   "outputs": [],
   "source": [
    "def split_spotify_date(date):\n",
    "  year, month, date = date.split(\"-\")\n",
    "  return [year, month, date]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "WpcR_XqZKSt4"
   },
   "outputs": [],
   "source": [
    "def get_chart_from_date(spotify_date):\n",
    "  year, month, day = split_spotify_date(spotify_date)\n",
    "  response = requests.get(f\"https://www.billboard.com/charts/hot-100/{year}-{month}-{day}\")\n",
    "\n",
    "  chart_dict = {\"title\": [],\n",
    "                \"raw_title\": [],\n",
    "                \"artists\": [],\n",
    "                \"raw_artists\": [],\n",
    "                \"current_pos\": [],\n",
    "                \"peak_pos\": [],\n",
    "                \"wks_on_chart\": []}\n",
    "\n",
    "  html_text = response.text\n",
    "  current_pos = 1\n",
    "  for i in range(200):\n",
    "    start, end = get_title_start_and_end_index(html_text)\n",
    "    raw_text= html_text[start:start+end]\n",
    "    text = sanitize_html(raw_text)\n",
    "    text = remove_tabs(text)\n",
    "    text = process_text(text)\n",
    "\n",
    "    stat_start, stat_end = get_stat_start_and_end_index(html_text)\n",
    "    raw_stats = html_text[stat_start:stat_start+stat_end]\n",
    "    stats = sanitize_html(raw_stats)\n",
    "    stats = remove_tabs(stats)\n",
    "\n",
    "    if len(text.strip(\"\\n\")) != 0:\n",
    "      title, artists = extract_title_and_artists(text)\n",
    "      _, peak_pos, wks_on_chart = extract_stats(stats)\n",
    "      chart_dict[\"title\"].append(title)\n",
    "      chart_dict[\"raw_title\"].append(get_raw(title))\n",
    "      chart_dict[\"artists\"].append(artists)\n",
    "      chart_dict[\"raw_artists\"].append(get_raw(artists))\n",
    "      chart_dict[\"current_pos\"].append(current_pos)\n",
    "      chart_dict[\"peak_pos\"].append(peak_pos)\n",
    "      chart_dict[\"wks_on_chart\"].append(wks_on_chart)\n",
    "      current_pos += 1\n",
    "\n",
    "    html_text = html_text[start+end:]\n",
    "\n",
    "  chart = pd.DataFrame(chart_dict)\n",
    "  return chart"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "ZS5kfgOhGXBu"
   },
   "outputs": [],
   "source": [
    "def get_spotify_track(spotify_id):\n",
    "  spotify_date = requests.get(f\"https://api.spotify.com/v1/tracks/{spotify_id}\", headers=SPOTIFY_HEADERS).json()\n",
    "  return spotify_date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "NGwKAEO52j0e"
   },
   "outputs": [],
   "source": [
    "def get_track_artist(track_data):\n",
    "  try:\n",
    "    artist = track_data['artists'][0]['name']\n",
    "    artist = artist.replace(\" \", \"-\")\n",
    "    artist = artist.replace(\".\", \"\")\n",
    "  except:\n",
    "    print(track_data)\n",
    "\n",
    "  return artist"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "CNaIgRjn2a9G"
   },
   "outputs": [],
   "source": [
    "def get_track_name(track_data):\n",
    "  name = track_data['name']\n",
    "\n",
    "  feat_in_name = name.find(\"(\")\n",
    "  if feat_in_name > 0:\n",
    "    name = name[:feat_in_name].strip()\n",
    "\n",
    "  name = name.replace(\" \", \"-\")\n",
    "  return re.sub(r'[^a-zA-Z0-9\\-\\?\\!\\&]+', '', name).lower()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "DkVfv_LlHCRz"
   },
   "outputs": [],
   "source": [
    "def get_track_id_from_hyperlink(hyperlink):\n",
    "  hyperlink = hyperlink.replace(\" \", \"\")\n",
    "  i = hyperlink.find(\"track/\") + 6\n",
    "  return hyperlink[i:i+22]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "MsmC_7q9C-Cd"
   },
   "outputs": [],
   "source": [
    "def get_track_artist_and_name(survey_data):\n",
    "  spotify_hyperlink = survey_data[\"hyperlink to song\"]\n",
    "  if not spotify_hyperlink or spotify_hyperlink[0:4] != \"http\":\n",
    "    return None\n",
    "\n",
    "  spotify_track_id = get_track_id_from_hyperlink(spotify_hyperlink)\n",
    "  track_data = get_spotify_track(spotify_track_id)\n",
    "  artist = get_track_artist(track_data)\n",
    "  track_name = get_track_name(track_data)\n",
    "  return [artist, track_name]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "HzpLBDbN96p8"
   },
   "outputs": [],
   "source": [
    "def extract_number(html_text):\n",
    "  return re.sub(r'[^0-9]+', '', html_text)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "GFNg34W30CNf"
   },
   "outputs": [],
   "source": [
    "from datetime import datetime\n",
    "\n",
    "def get_relevant_chart_info(title, artist):\n",
    "  url = f\"https://musicchartsarchive.com/singles/{artist}/{title}\"\n",
    "  response = requests.get(url, headers={'Accept': '*/*', 'User-Agent': 'request',})\n",
    "\n",
    "  if response.status_code != 200:\n",
    "    return [-1 for i in range(5)]\n",
    "\n",
    "  text = response.text\n",
    "  peak_ind = text.find(\"Peak Position:\")\n",
    "  num_week_ind = text.find(\"Number of Weeks on Chart:\")\n",
    "  if peak_ind == -1:\n",
    "    return [0 for i in range(5)]\n",
    "\n",
    "  peak_pos_dirty = text[peak_ind: peak_ind+25]\n",
    "  num_weeks_dirty = text[num_week_ind:num_week_ind+35]\n",
    "  peak_pos = extract_number(peak_pos_dirty)\n",
    "  num_weeks = extract_number(num_weeks_dirty)\n",
    "\n",
    "  last_table_entry_ind = text.find(\"</table>\")\n",
    "  date = re.sub(\"[^0-9]+\", \"\", text[last_table_entry_ind-100: last_table_entry_ind])[:8]\n",
    "  date = datetime.strptime(date, \"%Y%m%d\").date()\n",
    "  return [int(peak_pos), int(num_weeks), date, url, \"\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "DpWD5ZjG65aE"
   },
   "outputs": [],
   "source": [
    "def manually_calculate_weeks_and_peak_pos(url, survey_date):\n",
    "  res = requests.get(url, headers={'Accept': '*/*', 'User-Agent': 'request',})\n",
    "  r = res.text\n",
    "  peak_pos = 101\n",
    "  weeks = 0\n",
    "  while True:\n",
    "    s = \"/singles-chart/\"\n",
    "    i = r.find(s) + len(s)\n",
    "    date = r[i:i+10]\n",
    "    if date.count('-') != 2:\n",
    "      break\n",
    "    date = re.sub(\"[^0-9]+\", \"\", date)\n",
    "    date = datetime.strptime(date, \"%Y%m%d\").date()\n",
    "    pos = re.sub(\"[^0-9]+\", \"\", r[i+49:i+52])\n",
    "    if (date <= survey_date):\n",
    "      peak_pos = min(peak_pos, int(pos))\n",
    "      weeks += 1\n",
    "    else:\n",
    "      break\n",
    "\n",
    "    r = r[i+10:]\n",
    "\n",
    "  return [weeks, peak_pos if weeks != 0 else 0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "2tyCt9L1LNZ0"
   },
   "outputs": [],
   "source": [
    "def get_peak_and_weeks_and_url_from_music_archive(survey_data):\n",
    "  if not survey_data[\"track_data_for_search\"]:\n",
    "    return [-1 for _ in range(4)]\n",
    "  artist, title = survey_data[\"track_data_for_search\"]\n",
    "  if (artist, title) not in tracked:\n",
    "    tracked[(artist, title)] = get_relevant_chart_info(title, artist)\n",
    "\n",
    "  peak_pos, num_weeks, last_date, url, _ = tracked[(artist, title)]\n",
    "  if peak_pos > 0:\n",
    "    survey_date = survey_data[\"Survey_Started_Date\"].to_pydatetime().date()\n",
    "    if survey_date < last_date:\n",
    "      print(\"Manual calculation for\", title)\n",
    "      num_weeks, peak_pos = manually_calculate_weeks_and_peak_pos(url, survey_date)\n",
    "      return [peak_pos, num_weeks, url, \"\"]\n",
    "\n",
    "  return [peak_pos, num_weeks, url, \"\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "IlsWvrJ7NipV"
   },
   "outputs": [],
   "source": [
    "def get_peak_pos(survey_data):\n",
    "  return int(survey_data[\"final_stats\"][0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "U46mo7IEOBQ5"
   },
   "outputs": [],
   "source": [
    "def get_num_weeks(survey_data):\n",
    "  return int(survey_data[\"final_stats\"][1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "wkAFjLmVUQfE"
   },
   "outputs": [],
   "source": [
    "def get_title_from_history(text):\n",
    "  text = re.sub(r'[^a-zA-Z0-9\\'\\?\\!\\ \\*\\/\\&]+', '', text)\n",
    "  end = text.find(\"h3span\")\n",
    "  return text[:end].strip(\"/\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "ElucyAf5fOTS"
   },
   "outputs": [],
   "source": [
    "def get_num_from_text(text):\n",
    "  text = re.sub(r'[^0-9\\ ]+', '', text)\n",
    "  return text.strip(\" \").split(\" \")[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "74iCMyZyjHzA"
   },
   "outputs": [],
   "source": [
    "def get_peak_from_history(text):\n",
    "  peak_string = 'aria-label=\"Peak'\n",
    "  j = text.find(peak_string) + len(peak_string)\n",
    "  proc_text = process_text(text[j:j+20])\n",
    "  peak = get_num_from_text(proc_text)\n",
    "  return peak"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "m3IFsIsEi_-n"
   },
   "outputs": [],
   "source": [
    "def get_week_from_history(text):\n",
    "    week_string = 'aria-label=\"Weeks on Chart '\n",
    "    k = text.find(week_string) + len(week_string)\n",
    "    proc_text = process_text(text[k:k+30])\n",
    "    week = get_num_from_text(proc_text)\n",
    "    return week"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "3PFtLT1h7Mm_"
   },
   "outputs": [],
   "source": [
    "def get_date_from_history(text):\n",
    "  date_string = 'href=\"https://www.billboard.com/charts/hot-100/'\n",
    "  i = text.find(date_string) + len(date_string) + 36\n",
    "  proc_text = process_text(text[i:i+26])\n",
    "  text = re.sub(r'[^0-9\\.]+', '', proc_text).strip()\n",
    "  if len(text) == 0:\n",
    "    return None\n",
    "  month, day, year = text.split(\".\")\n",
    "  if int(year) > 23:\n",
    "    year = \"19\" + year\n",
    "  else:\n",
    "    year = \"20\" + year\n",
    "\n",
    "  date = datetime.strptime(\"\".join([year, month, day]), \"%Y%m%d\").date()\n",
    "  return date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "W5uE2OQx9M1L"
   },
   "outputs": [],
   "source": [
    "def address_special_cases(artist):\n",
    "  if artist.lower() == \"p!nk\":\n",
    "    return \"pink\"\n",
    "  artist = artist.replace(\"’\", \"\")\n",
    "  return artist"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "F9axjgKJ2lFu"
   },
   "outputs": [],
   "source": [
    "def get_track_name_df(df):\n",
    "  name = df['title']\n",
    "\n",
    "  feat_in_name = name.find(\"(\")\n",
    "  if feat_in_name > 0:\n",
    "    name = name[:feat_in_name].strip()\n",
    "\n",
    "  name = name.replace(\" \", \"-\")\n",
    "  return re.sub(r'[^a-zA-Z0-9\\-\\?\\!\\&]+', '', name).lower()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "eJppYHrJ62_9"
   },
   "outputs": [],
   "source": [
    "def get_history_dict(text):\n",
    "  history_chart = {\"title\": [], \"peak\": [], \"weeks\": [], \"date\": []}\n",
    "  title = \"\"\n",
    "  while True:\n",
    "    title_string = '<h3 id=\"title-of-a-story\" class=\"c-title  a-no-trucate a-font-primary-bold-s u-letter-spacing-0021 lrv-u-font-size-18@tablet lrv-u-font-size-16 u-line-height-125 u-line-height-normal@mobile-max a-truncate-ellipsis u-max-width-330 u-max-width-230@tablet-only artist-chart-row-title\">'\n",
    "    i = text.find(title_string) + len(title_string)\n",
    "    proc_text = process_text(text[i:i+200])\n",
    "    title = get_title_from_history(proc_text)\n",
    "    text = text[i:]\n",
    "    if title.find(\"artrow\") != -1 or i == 281:\n",
    "      break\n",
    "\n",
    "    peak = get_peak_from_history(text)\n",
    "    week = get_week_from_history(text)\n",
    "    date = get_date_from_history(text)\n",
    "    history_chart[\"title\"].append(title)\n",
    "    history_chart[\"peak\"].append(peak)\n",
    "    history_chart[\"weeks\"].append(week)\n",
    "    history_chart[\"date\"].append(date)\n",
    "\n",
    "  return history_chart"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "lslb2sc7UPjI"
   },
   "outputs": [],
   "source": [
    "from requests.sessions import TooManyRedirects\n",
    "def get_billboard_page_and_url(artist):\n",
    "  billboard_history = \"\"\n",
    "  used_url = url = f\"https://www.billboard.com/artist/{artist}/chart-history/hsi/\"\n",
    "  second_try_url = f\"https://www.billboard.com/artist/{artist+'-2'}/chart-history/hsi/\"\n",
    "  encountered_error = False\n",
    "  try:\n",
    "    billboard_history = requests.get(url)\n",
    "  except TooManyRedirects:\n",
    "    print(f\"Too many redirects on {url}\")\n",
    "    encountered_error = True\n",
    "\n",
    "  table_empty = (not billboard_history or billboard_history.text.find(\"artist-chart-history-container\") == -1)\n",
    "  if not billboard_history or billboard_history.status_code != 200 or encountered_error or table_empty:\n",
    "    try:\n",
    "      billboard_history = requests.get(second_try_url)\n",
    "      if billboard_history.status_code == 200:\n",
    "        used_url = second_try_url\n",
    "        print(f\"Found {artist} on second try\")\n",
    "      else:\n",
    "        return [None, None]\n",
    "    except TooManyRedirects:\n",
    "      print(f\"Too many redirects on {second_try_url}\")\n",
    "      return [None, None]\n",
    "\n",
    "  text = billboard_history.text\n",
    "  if (text.find('artist-chart-history-container') == -1):\n",
    "    billboard_history = requests.get(second_try_url)\n",
    "    text = billboard_history.text\n",
    "\n",
    "  return [text, used_url]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "0la5-1DMTR_Y"
   },
   "outputs": [],
   "source": [
    "def search_billboard_history(artist, search_title):\n",
    "  close_match = None\n",
    "  artist = address_special_cases(artist)\n",
    "  billboard_history, url = get_billboard_page_and_url(artist)\n",
    "\n",
    "  error = [-1 for _ in range(5)]\n",
    "  found_artist_but_not_song = [-1, -1, -1, url, close_match]\n",
    "  if not billboard_history:\n",
    "    return error\n",
    "\n",
    "  history_dict = get_history_dict(billboard_history)\n",
    "  if len(history_dict[\"title\"]) == 0:\n",
    "    return found_artist_but_not_song\n",
    "\n",
    "  df = pd.DataFrame(history_dict)\n",
    "  df[\"title\"] = df.apply(get_track_name_df, axis=1)\n",
    "  row = df.loc[df.title == search_title]\n",
    "  if row.shape[0] == 0:\n",
    "    close_match_list = difflib.get_close_matches(search_title, df.title.tolist())\n",
    "    if len(close_match_list):\n",
    "      close_match = close_match_list[0]\n",
    "      print(f\"Found close match! Actual: {search_title}, Found: {close_match}\")\n",
    "      row = df.loc[df.title == close_match]\n",
    "    else:\n",
    "      return found_artist_but_not_song\n",
    "\n",
    "  row = row.iloc[0]\n",
    "  return [row[\"peak\"], row[\"weeks\"], row[\"date\"], url, close_match]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "gW8yemELrFUW"
   },
   "outputs": [],
   "source": [
    "from datetime import timedelta\n",
    "\n",
    "def double_check_billboard(df):\n",
    "  if not df[\"track_data_for_search\"] or len(df[\"track_data_for_search\"]) < 2:\n",
    "    return df[\"final_stats\"]\n",
    "  if df[\"final_stats\"][0] != -1:\n",
    "    return df[\"final_stats\"]\n",
    "\n",
    "  artist, title = df[\"track_data_for_search\"]\n",
    "  if title.find(\"---\") != -1:\n",
    "    title = title[:title.find(\"---\")]\n",
    "\n",
    "  if (artist, title) in tracked:\n",
    "    return tracked[(artist, title)]\n",
    "\n",
    "  tracked[(artist, title)] = search_billboard_history(artist, title)\n",
    "  weeks = tracked[(artist, title)][1]\n",
    "  date = tracked[(artist, title)][2]\n",
    "  if not isinstance(date, int) and date + timedelta(days=(7*(int(weeks) + 5))) > df[\"Survey_Started_Date\"].to_pydatetime().date():\n",
    "    return [999, 999] + tracked[(artist, title)][-2:]\n",
    "\n",
    "  return tracked[(artist, title)][:2] + tracked[(artist, title)][-2:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "yvkiIk3xG2hm"
   },
   "outputs": [],
   "source": [
    "def get_url(df):\n",
    "  if df[\"final_stats\"][-2] == -1:\n",
    "    return \"\"\n",
    "\n",
    "  return df[\"final_stats\"][-2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "MxJWMxjKfr5R"
   },
   "outputs": [],
   "source": [
    "def get_close_match(df):\n",
    "  return df[\"final_stats\"][-1]"
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "from os import listdir\n",
    "from os.path import isfile, join\n",
    "def get_excel_file():\n",
    "  mypath = \"/content\"\n",
    "  files = [f for f in listdir(mypath) if isfile(join(mypath, f))]\n",
    "  for f in files:\n",
    "    if f.find('.xlsx') != -1:\n",
    "      print(\"Found excel file:\", f)\n",
    "      return f"
   ],
   "metadata": {
    "id": "Skvanim8a9fE"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "GjWeSi7B7I6W"
   },
   "source": [
    "# Code"
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "file = get_excel_file()"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "dIR81z9bas9f",
    "outputId": "ebcf2bbe-98aa-43af-94eb-c87f2fad4756"
   },
   "execution_count": null,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Found excel file: TEAM300_music.xlsx\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "eg2eJHW_DTRN"
   },
   "outputs": [],
   "source": [
    "survey_data = pd.read_excel(file)\n",
    "survey_data.fillna(\"\", inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "0DKXo0YjLPiF"
   },
   "outputs": [],
   "source": [
    "SPOTIFY_HEADERS = get_authorization_headers()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "EqJsNeboEBM_"
   },
   "outputs": [],
   "source": [
    "survey_data[\"track_data_for_search\"] = survey_data.apply(get_track_artist_and_name, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "v288Zfz_CnUg",
    "outputId": "4554a6cd-9627-46c7-d0ae-53cb36fd4aaa"
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Manual calculation for wishful-drinking\n",
      "Manual calculation for moscow-mule\n"
     ]
    }
   ],
   "source": "survey_data[\"final_stats\"] = survey_data.apply(get_peak_and_weeks_and_url_from_music_archive, axis=1)"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "568sY9oE3j1I",
    "outputId": "f8ed5a85-a223-48ae-9fc5-7e9cec6a5dc7"
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Found close match! Actual: velvet-heartbreak, Found: heartbeat\n",
      "Found close match! Actual: in-vein, Found: on-everything\n",
      "Found close match! Actual: swimming-pools, Found: swimming-pools-drank\n",
      "Found Panic!-At-The-Disco on second try\n",
      "Found close match! Actual: one-of-the-drunks, Found: into-the-unknown\n",
      "Found close match! Actual: seorita, Found: senorita\n",
      "Found Pierre-Bourne on second try\n"
     ]
    }
   ],
   "source": "survey_data[\"final_stats\"] = survey_data.apply(double_check_billboard, axis=1)"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "VJshD1ptOGEg"
   },
   "outputs": [],
   "source": [
    "survey_data[\"peak_position\"] = survey_data.apply(get_peak_pos, axis=1)\n",
    "survey_data[\"num_weeks\"] = survey_data.apply(get_num_weeks, axis=1)\n",
    "survey_data[\"url\"] = survey_data.apply(get_url, axis=1)\n",
    "survey_data[\"used_close_match\"] = survey_data.apply(get_close_match, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "oP7vF0SyBO04"
   },
   "outputs": [],
   "source": [
    "close_matches_found = {}\n",
    "def verify_data(survey_data):\n",
    "  actual = survey_data['song title']\n",
    "  close_match = survey_data['used_close_match']\n",
    "  print(f\"Acutal: {actual}, Close match: {close_match}\")\n",
    "  if close_match in close_matches_found:\n",
    "    response = close_matches_found[close_match]\n",
    "  else:\n",
    "    response = input(\"Was the right song found? (y/n): \")\n",
    "    close_matches_found[close_match] = response\n",
    "\n",
    "  if response == \"n\":\n",
    "    return [-1, -1]\n",
    "  else:\n",
    "    return [survey_data['peak_position'], survey_data['num_weeks']]"
   ]
  },
  {
   "cell_type": "markdown",
   "source": [
    "MANUAL INPUT REQUIRED FOR NEXT CELL"
   ],
   "metadata": {
    "id": "8jMZhZ1QiEtK"
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "You must verify the song found with a similar name is the correct song. If the titles do not refer to the same songs, type \"n\" (no quotation marks) and press enter. If they refer to the same song, type \"y\" (no quotation marks) and press enter. For example, \"Acutal: Swimming Pool, Close match: swimming-pools-drank\" refers to the same song, so you would type \"y\". On the other hand, \"Acutal: \"In Vein\", Close match: on-everything\" clearly do not refer to the same song, so you would type \"n\"."
   ],
   "metadata": {
    "id": "XAiVKC6UiLWP"
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "M6HQQDl2gnfV",
    "outputId": "c9fb6be5-f63c-45e3-a13a-b1b3cafc74ed"
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Acutal: Velvet Heartbreak, Close match: heartbeat\n",
      "Was the right song found? (y/n): n\n",
      "Acutal: \"In Vein\", Close match: on-everything\n",
      "Was the right song found? (y/n): n\n",
      "Acutal: Swimming Pool, Close match: swimming-pools-drank\n",
      "Was the right song found? (y/n): y\n",
      "Acutal: One of the Drunks, Close match: into-the-unknown\n",
      "Was the right song found? (y/n): n\n",
      "Acutal: Senorita, Close match: senorita\n",
      "Was the right song found? (y/n): y\n",
      "Acutal: Swimming Pool, Close match: swimming-pools-drank\n",
      "Acutal: One of the Drunks, Close match: into-the-unknown\n",
      "Acutal: Swimming Pool, Close match: swimming-pools-drank\n",
      "Acutal: Swimming Pools , Close match: swimming-pools-drank\n",
      "Acutal: Swimming Pools , Close match: swimming-pools-drank\n"
     ]
    },
    {
     "output_type": "stream",
     "name": "stderr",
     "text": [
      "<ipython-input-134-0a621218931d>:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  used_close_match_df['verify_data'] = used_close_match_df.apply(verify_data, axis=1)\n"
     ]
    }
   ],
   "source": [
    "used_close_match_df = survey_data.loc[(survey_data.used_close_match.isin([None, '', -1]) == False)]\n",
    "used_close_match_df['verify_data'] = used_close_match_df.apply(verify_data, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "O5wIhN4dFCIu"
   },
   "outputs": [],
   "source": [
    "for i in used_close_match_df.index.to_list():\n",
    "  peak_pos, weeks = used_close_match_df.loc[i][\"verify_data\"]\n",
    "  survey_data.loc[i, \"peak_position\"] = peak_pos\n",
    "  survey_data.loc[i, \"num_weeks\"] = weeks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "HCTu1UjY5fsQ"
   },
   "outputs": [],
   "source": [
    "concise_song_data = survey_data[[\"PID\", \"Survey_Started_Date\", \"song_title_artist (what the participant wrote)\", \"song title\", \"artist\",\n",
    "                                 \"hyperlink to song\", \"peak_position\", \"num_weeks\", \"url\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "2bJyltpqKPsC",
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "outputId": "2bd4c38c-7393-40e6-b088-a9051feceaed"
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stderr",
     "text": [
      "<ipython-input-137-71d67bfd4b59>:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  concise_song_data[\"Survey_Started_Date\"] = concise_song_data[\"Survey_Started_Date\"].dt.date\n"
     ]
    }
   ],
   "source": [
    "concise_song_data[\"Survey_Started_Date\"] = concise_song_data[\"Survey_Started_Date\"].dt.date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "VlZKjSLZK4Zu"
   },
   "outputs": [],
   "source": [
    "concise_song_data.to_excel(\"concise_song_data.xlsx\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "source": [],
   "metadata": {
    "id": "7A0i4YFrU5dB"
   },
   "execution_count": null,
   "outputs": []
  }
 ],
 "metadata": {
  "colab": {
   "provenance": []
  },
  "kernelspec": {
   "display_name": "Python 3",
   "name": "python3"
  },
  "language_info": {
   "name": "python"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}