Combining_Discogs_and_Spotify_Data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Notebook 3: Cleaning up and combining dataframes collected from Discogs and Spotify"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "os.chdir('/Users/beth/Documents/Metis/metis_project_2/metis_project2')\n",
    "import pickle\n",
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('spotify_df_final.pickle', 'rb') as read_file:\n",
    "    spotify_df = pickle.load(read_file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('album_main_pages_2.pickle', 'rb') as read_file:\n",
    "    album_main_df = pickle.load(read_file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('first_edition_album_info.pickle', 'rb') as read_file:\n",
    "    first_edition_df = pickle.load(read_file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('artist_data.pickle', 'rb') as read_file:\n",
    "    artist_df = pickle.load(read_file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('top_1050_collected_records.pickle', 'rb') as read_file:\n",
    "    top_record_df = pickle.load(read_file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1050, 7)"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "top_record_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1050, 3)"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "artist_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1050, 3)"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "album_main_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1050, 12)"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "first_edition_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [],
   "source": [
    "main_df=pd.concat([top_record_df, artist_df, album_main_df, first_edition_df], axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1050, 25)"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "main_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "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>artists</th>\n",
       "      <th>albums</th>\n",
       "      <th>artist_links</th>\n",
       "      <th>album_links</th>\n",
       "      <th>release_year</th>\n",
       "      <th>label</th>\n",
       "      <th>release_country</th>\n",
       "      <th>total_artist_albums</th>\n",
       "      <th>artist_last_years</th>\n",
       "      <th>artist_first_years</th>\n",
       "      <th>...</th>\n",
       "      <th>users_want</th>\n",
       "      <th>user_rating</th>\n",
       "      <th>lowest_price</th>\n",
       "      <th>median_price</th>\n",
       "      <th>highest_price</th>\n",
       "      <th>last_sold</th>\n",
       "      <th>styles</th>\n",
       "      <th>genres</th>\n",
       "      <th>number_for_sale</th>\n",
       "      <th>versions</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1045</th>\n",
       "      <td>The Beatles</td>\n",
       "      <td>The Beatles At The Hollywood Bowl</td>\n",
       "      <td>https://www.discogs.com/artist/82730-The-Beatles</td>\n",
       "      <td>https://www.discogs.com/The-Beatles-The-Beatle...</td>\n",
       "      <td>1977</td>\n",
       "      <td>Parlophone</td>\n",
       "      <td>UK</td>\n",
       "      <td>157</td>\n",
       "      <td>2018</td>\n",
       "      <td>1963</td>\n",
       "      <td>...</td>\n",
       "      <td>216</td>\n",
       "      <td>3.82</td>\n",
       "      <td>5.78</td>\n",
       "      <td>7.63</td>\n",
       "      <td>12.99</td>\n",
       "      <td>13 Aug 20</td>\n",
       "      <td>[Rock &amp; Roll, Pop Rock, Beat]</td>\n",
       "      <td>[Rock]</td>\n",
       "      <td>134</td>\n",
       "      <td>129</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1046</th>\n",
       "      <td>Fine Young Cannibals</td>\n",
       "      <td>The Raw &amp; The Cooked</td>\n",
       "      <td>https://www.discogs.com/artist/4711-Fine-Young...</td>\n",
       "      <td>https://www.discogs.com/Fine-Young-Cannibals-T...</td>\n",
       "      <td>1988</td>\n",
       "      <td>London Records</td>\n",
       "      <td>UK</td>\n",
       "      <td>3</td>\n",
       "      <td>1988</td>\n",
       "      <td>1985</td>\n",
       "      <td>...</td>\n",
       "      <td>251</td>\n",
       "      <td>3.81</td>\n",
       "      <td>2.27</td>\n",
       "      <td>4.33</td>\n",
       "      <td>14.88</td>\n",
       "      <td>06 Sep 20</td>\n",
       "      <td>[Synth-pop, Alternative Rock, Pop Rock]</td>\n",
       "      <td>[Electronic, Rock, Funk / Soul, Pop]</td>\n",
       "      <td>125</td>\n",
       "      <td>114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1047</th>\n",
       "      <td>Elton John</td>\n",
       "      <td>A Single Man</td>\n",
       "      <td>https://www.discogs.com/artist/57103-Elton-John</td>\n",
       "      <td>https://www.discogs.com/Elton-John-A-Single-Ma...</td>\n",
       "      <td>1978</td>\n",
       "      <td>The Rocket Record Company</td>\n",
       "      <td>UK</td>\n",
       "      <td>139</td>\n",
       "      <td>2019</td>\n",
       "      <td>1969</td>\n",
       "      <td>...</td>\n",
       "      <td>129</td>\n",
       "      <td>3.63</td>\n",
       "      <td>1.23</td>\n",
       "      <td>3.50</td>\n",
       "      <td>5.18</td>\n",
       "      <td>03 Sep 20</td>\n",
       "      <td>[Soft Rock, Pop Rock]</td>\n",
       "      <td>[Rock, Pop]</td>\n",
       "      <td>331</td>\n",
       "      <td>127</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1048</th>\n",
       "      <td>David Bowie</td>\n",
       "      <td>Never Let Me Down</td>\n",
       "      <td>https://www.discogs.com/artist/10263-David-Bowie</td>\n",
       "      <td>https://www.discogs.com/David-Bowie-Never-Let-...</td>\n",
       "      <td>1987</td>\n",
       "      <td>EMI America</td>\n",
       "      <td>UK</td>\n",
       "      <td>87</td>\n",
       "      <td>2020</td>\n",
       "      <td>1967</td>\n",
       "      <td>...</td>\n",
       "      <td>288</td>\n",
       "      <td>3.39</td>\n",
       "      <td>7.78</td>\n",
       "      <td>10.45</td>\n",
       "      <td>37.06</td>\n",
       "      <td>21 Aug 20</td>\n",
       "      <td>[Pop Rock]</td>\n",
       "      <td>[Rock]</td>\n",
       "      <td>73</td>\n",
       "      <td>129</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1049</th>\n",
       "      <td>Daryl Hall + John Oates</td>\n",
       "      <td>H2O</td>\n",
       "      <td>https://www.discogs.com/artist/95886-Daryl-Hal...</td>\n",
       "      <td>https://www.discogs.com/Daryl-Hall-John-Oates-...</td>\n",
       "      <td>1982</td>\n",
       "      <td>RCA</td>\n",
       "      <td>US</td>\n",
       "      <td>52</td>\n",
       "      <td>2015</td>\n",
       "      <td>1972</td>\n",
       "      <td>...</td>\n",
       "      <td>367</td>\n",
       "      <td>3.71</td>\n",
       "      <td>2.00</td>\n",
       "      <td>4.00</td>\n",
       "      <td>12.99</td>\n",
       "      <td>31 Aug 20</td>\n",
       "      <td>[Pop Rock, Synth-pop]</td>\n",
       "      <td>[Electronic, Rock]</td>\n",
       "      <td>216</td>\n",
       "      <td>95</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                      artists                             albums  \\\n",
       "1045              The Beatles  The Beatles At The Hollywood Bowl   \n",
       "1046     Fine Young Cannibals               The Raw & The Cooked   \n",
       "1047               Elton John                       A Single Man   \n",
       "1048              David Bowie                  Never Let Me Down   \n",
       "1049  Daryl Hall + John Oates                                H2O   \n",
       "\n",
       "                                           artist_links  \\\n",
       "1045   https://www.discogs.com/artist/82730-The-Beatles   \n",
       "1046  https://www.discogs.com/artist/4711-Fine-Young...   \n",
       "1047    https://www.discogs.com/artist/57103-Elton-John   \n",
       "1048   https://www.discogs.com/artist/10263-David-Bowie   \n",
       "1049  https://www.discogs.com/artist/95886-Daryl-Hal...   \n",
       "\n",
       "                                            album_links release_year  \\\n",
       "1045  https://www.discogs.com/The-Beatles-The-Beatle...         1977   \n",
       "1046  https://www.discogs.com/Fine-Young-Cannibals-T...         1988   \n",
       "1047  https://www.discogs.com/Elton-John-A-Single-Ma...         1978   \n",
       "1048  https://www.discogs.com/David-Bowie-Never-Let-...         1987   \n",
       "1049  https://www.discogs.com/Daryl-Hall-John-Oates-...         1982   \n",
       "\n",
       "                          label release_country total_artist_albums  \\\n",
       "1045                 Parlophone              UK                 157   \n",
       "1046             London Records              UK                   3   \n",
       "1047  The Rocket Record Company              UK                 139   \n",
       "1048                EMI America              UK                  87   \n",
       "1049                        RCA              US                  52   \n",
       "\n",
       "     artist_last_years artist_first_years  ... users_want  user_rating  \\\n",
       "1045              2018               1963  ...        216         3.82   \n",
       "1046              1988               1985  ...        251         3.81   \n",
       "1047              2019               1969  ...        129         3.63   \n",
       "1048              2020               1967  ...        288         3.39   \n",
       "1049              2015               1972  ...        367         3.71   \n",
       "\n",
       "     lowest_price  median_price highest_price  last_sold  \\\n",
       "1045         5.78          7.63         12.99  13 Aug 20   \n",
       "1046         2.27          4.33         14.88  06 Sep 20   \n",
       "1047         1.23          3.50          5.18  03 Sep 20   \n",
       "1048         7.78         10.45         37.06  21 Aug 20   \n",
       "1049         2.00          4.00         12.99  31 Aug 20   \n",
       "\n",
       "                                       styles  \\\n",
       "1045            [Rock & Roll, Pop Rock, Beat]   \n",
       "1046  [Synth-pop, Alternative Rock, Pop Rock]   \n",
       "1047                    [Soft Rock, Pop Rock]   \n",
       "1048                               [Pop Rock]   \n",
       "1049                    [Pop Rock, Synth-pop]   \n",
       "\n",
       "                                    genres number_for_sale versions  \n",
       "1045                                [Rock]             134      129  \n",
       "1046  [Electronic, Rock, Funk / Soul, Pop]             125      114  \n",
       "1047                           [Rock, Pop]             331      127  \n",
       "1048                                [Rock]              73      129  \n",
       "1049                    [Electronic, Rock]             216       95  \n",
       "\n",
       "[5 rows x 25 columns]"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "main_df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [],
   "source": [
    "del top_record_df\n",
    "del artist_df\n",
    "del album_main_df\n",
    "del first_edition_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [],
   "source": [
    "main_2=pd.merge(left=main_df, right=spotify_df, how=\"left\", left_on=\"artists\", right_on=\"top_artists_orig_name\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1050, 28)"
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "main_2.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('final_record_df.pickle', 'wb') as write_file:\n",
    "    pickle.dump(main_2, write_file)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Manually entering some of the missing values from Discogs (~20 albums)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I decided to fix the about 20 entries that did not have median price and other important stats data. The reason for this is that the album link from the main table is supposed to lead to the main album page (with links to all the album versions) but sometimes it led to the specific version album page instead, messing up the chain of links in my Beautiful Soup scraping of Discogs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "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>artists</th>\n",
       "      <th>albums</th>\n",
       "      <th>artist_links</th>\n",
       "      <th>album_links</th>\n",
       "      <th>release_year</th>\n",
       "      <th>label</th>\n",
       "      <th>release_country</th>\n",
       "      <th>total_artist_albums</th>\n",
       "      <th>artist_last_years</th>\n",
       "      <th>artist_first_years</th>\n",
       "      <th>...</th>\n",
       "      <th>median_price</th>\n",
       "      <th>highest_price</th>\n",
       "      <th>last_sold</th>\n",
       "      <th>styles</th>\n",
       "      <th>genres</th>\n",
       "      <th>number_for_sale</th>\n",
       "      <th>versions</th>\n",
       "      <th>top_artists_spotify_name</th>\n",
       "      <th>spotify_monthly_listeners</th>\n",
       "      <th>top_artists_orig_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>The Velvet Underground</td>\n",
       "      <td>The Velvet Underground &amp; Nico</td>\n",
       "      <td>https://www.discogs.com/artist/39766-The-Velve...</td>\n",
       "      <td>https://www.discogs.com/The-Velvet-Underground...</td>\n",
       "      <td>1967</td>\n",
       "      <td>Verve Records</td>\n",
       "      <td>US</td>\n",
       "      <td>19</td>\n",
       "      <td>2015</td>\n",
       "      <td>1966</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[]</td>\n",
       "      <td>[]</td>\n",
       "      <td>16,042</td>\n",
       "      <td>NaN</td>\n",
       "      <td>The Velvet Underground</td>\n",
       "      <td>3707923</td>\n",
       "      <td>The Velvet Underground</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>215</th>\n",
       "      <td>John Coltrane</td>\n",
       "      <td>A Love Supreme</td>\n",
       "      <td>https://www.discogs.com/artist/97545-John-Colt...</td>\n",
       "      <td>https://www.discogs.com/John-Coltrane-A-Love-S...</td>\n",
       "      <td>1965</td>\n",
       "      <td>Impulse!</td>\n",
       "      <td>US</td>\n",
       "      <td>143</td>\n",
       "      <td>2020</td>\n",
       "      <td>1957</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Free Jazz</td>\n",
       "      <td>Jazz</td>\n",
       "      <td>NaN</td>\n",
       "      <td>230</td>\n",
       "      <td>John Coltrane</td>\n",
       "      <td>2163786</td>\n",
       "      <td>John Coltrane</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>796</th>\n",
       "      <td>The Beatles</td>\n",
       "      <td>Yesterday And Today</td>\n",
       "      <td>https://www.discogs.com/artist/82730-The-Beatles</td>\n",
       "      <td>https://www.discogs.com/The-Beatles-Yesterday-...</td>\n",
       "      <td>1966</td>\n",
       "      <td>Capitol Records</td>\n",
       "      <td>US</td>\n",
       "      <td>157</td>\n",
       "      <td>2018</td>\n",
       "      <td>1963</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Psychedelic Rock</td>\n",
       "      <td>Rock</td>\n",
       "      <td>NaN</td>\n",
       "      <td>194</td>\n",
       "      <td>The Beatles</td>\n",
       "      <td>22982890</td>\n",
       "      <td>The Beatles</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                    artists                         albums  \\\n",
       "96   The Velvet Underground  The Velvet Underground & Nico   \n",
       "215           John Coltrane                 A Love Supreme   \n",
       "796             The Beatles            Yesterday And Today   \n",
       "\n",
       "                                          artist_links  \\\n",
       "96   https://www.discogs.com/artist/39766-The-Velve...   \n",
       "215  https://www.discogs.com/artist/97545-John-Colt...   \n",
       "796   https://www.discogs.com/artist/82730-The-Beatles   \n",
       "\n",
       "                                           album_links release_year  \\\n",
       "96   https://www.discogs.com/The-Velvet-Underground...         1967   \n",
       "215  https://www.discogs.com/John-Coltrane-A-Love-S...         1965   \n",
       "796  https://www.discogs.com/The-Beatles-Yesterday-...         1966   \n",
       "\n",
       "               label release_country total_artist_albums artist_last_years  \\\n",
       "96     Verve Records              US                  19              2015   \n",
       "215         Impulse!              US                 143              2020   \n",
       "796  Capitol Records              US                 157              2018   \n",
       "\n",
       "    artist_first_years  ... median_price  highest_price last_sold  \\\n",
       "96                1966  ...          NaN            NaN       NaN   \n",
       "215               1957  ...          NaN            NaN       NaN   \n",
       "796               1963  ...          NaN            NaN       NaN   \n",
       "\n",
       "               styles genres number_for_sale versions  \\\n",
       "96                 []     []          16,042      NaN   \n",
       "215         Free Jazz   Jazz             NaN      230   \n",
       "796  Psychedelic Rock   Rock             NaN      194   \n",
       "\n",
       "    top_artists_spotify_name spotify_monthly_listeners   top_artists_orig_name  \n",
       "96    The Velvet Underground                   3707923  The Velvet Underground  \n",
       "215            John Coltrane                   2163786           John Coltrane  \n",
       "796              The Beatles                  22982890             The Beatles  \n",
       "\n",
       "[3 rows x 28 columns]"
      ]
     },
     "execution_count": 172,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "main_2[main_2['median_price'].isnull()] "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I used the album links from the table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'https://www.discogs.com/Bob-Dylan-The-Band-The-Basement-Tapes/master/14459'"
      ]
     },
     "execution_count": 168,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "main_2.loc[1042,'album_links']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['artists', 'albums', 'artist_links', 'album_links', 'release_year',\n",
       "       'label', 'release_country', 'total_artist_albums', 'artist_last_years',\n",
       "       'artist_first_years', 'first_release_links', 'number_of_songs',\n",
       "       'average_song_length', 'index', 'users_have', 'users_want',\n",
       "       'user_rating', 'lowest_price', 'median_price', 'highest_price',\n",
       "       'last_sold', 'styles', 'genres', 'number_for_sale', 'versions',\n",
       "       'top_artists_spotify_name', 'spotify_monthly_listeners',\n",
       "       'top_artists_orig_name'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 116,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "main_2.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {},
   "outputs": [],
   "source": [
    "main_2.loc[46,'users_have'] = 582\n",
    "main_2.loc[46, 'users_want'] = 2100\n",
    "main_2.loc[46, 'user_rating'] = 4.48\n",
    "main_2.loc[46, 'genres'] = ['Rock']\n",
    "main_2.loc[46, 'styles'] = ['Garage Rock']\n",
    "main_2.loc[46, 'versions'] = 365\n",
    "main_2.loc[46, 'last_sold'] = '02 Jun 20'\n",
    "main_2.loc[46, 'lowest_price'] = 100.00\n",
    "main_2.loc[46, 'median_price'] = 275.00\n",
    "main_2.loc[46, 'highest_price'] = 750.00\n",
    "main_2.loc[46, 'number_for_sale'] = 3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [],
   "source": [
    "main_2.loc[205,'users_have'] = 2225\n",
    "main_2.loc[205, 'users_want'] = 416\n",
    "main_2.loc[205, 'user_rating'] = 4.41\n",
    "main_2.loc[205, 'genres'] = ['Rock']\n",
    "#main_2.loc[205, 'styles'] = []\n",
    "main_2.loc[205, 'versions'] = 288\n",
    "main_2.loc[205, 'last_sold'] = '15 Aug 20'\n",
    "main_2.loc[205, 'lowest_price'] = 14.99\n",
    "main_2.loc[205, 'median_price'] = 19.99\n",
    "main_2.loc[205, 'highest_price'] = 25.00\n",
    "main_2.loc[205, 'number_for_sale'] = np.nan"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {},
   "outputs": [],
   "source": [
    "main_2.loc[215,'users_have'] = 83\n",
    "main_2.loc[215, 'users_want'] = 430\n",
    "main_2.loc[215, 'user_rating'] = 4.92\n",
    "main_2.loc[215, 'genres'] = ['Jazz']\n",
    "main_2.loc[215, 'styles'] = ['Free Jazz']\n",
    "main_2.loc[215, 'versions'] = 230\n",
    "#main_2.loc[215, 'last_sold'] = \n",
    "#main_2.loc[215, 'lowest_price'] = 14.99\n",
    "#main_2.loc[215, 'median_price'] = 19.99\n",
    "#main_2.loc[215, 'highest_price'] = 25.00\n",
    "#main_2.loc[215, 'number_for_sale'] = np.nan"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {},
   "outputs": [],
   "source": [
    "main_2.loc[330,'users_have'] = 39250\n",
    "main_2.loc[330, 'users_want'] = 12897\n",
    "main_2.loc[330, 'user_rating'] = 4.52\n",
    "main_2.loc[330, 'genres'] = ['Electronic']\n",
    "main_2.loc[330, 'styles'] = ['Disco']\n",
    "main_2.loc[330, 'versions'] = 41\n",
    "main_2.loc[330, 'last_sold'] = '22 Sep 20'\n",
    "main_2.loc[330, 'lowest_price'] = 23.06\n",
    "main_2.loc[330, 'median_price'] = 18.36\n",
    "main_2.loc[330, 'highest_price'] = 40.99\n",
    "main_2.loc[330, 'number_for_sale'] = 124"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {},
   "outputs": [],
   "source": [
    "main_2.loc[339,'users_have'] = 1616\n",
    "main_2.loc[339, 'users_want'] = 545\n",
    "main_2.loc[339, 'user_rating'] = 4.24\n",
    "main_2.loc[339, 'genres'] = ['Rock']\n",
    "main_2.loc[339, 'styles'] = ['Psychedelic Rock']\n",
    "main_2.loc[339, 'versions'] = 284\n",
    "main_2.loc[339, 'last_sold'] = '02 Aug 20'\n",
    "main_2.loc[339, 'lowest_price'] = 5.77\n",
    "main_2.loc[339, 'median_price'] = 17.94\n",
    "main_2.loc[339, 'highest_price'] = 34.12\n",
    "main_2.loc[339, 'number_for_sale'] = 11"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "metadata": {},
   "outputs": [],
   "source": [
    "main_2.loc[352,'users_have'] = 4128\n",
    "main_2.loc[352, 'users_want'] = 1862\n",
    "main_2.loc[352, 'user_rating'] = 4.54\n",
    "main_2.loc[352, 'genres'] = ['Jazz']\n",
    "main_2.loc[352, 'styles'] = ['Bossa Nova']\n",
    "main_2.loc[352, 'versions'] = 255\n",
    "main_2.loc[352, 'last_sold'] = '26 Aug 20'\n",
    "main_2.loc[352, 'lowest_price'] = 6.50\n",
    "main_2.loc[352, 'median_price'] = 14.91\n",
    "main_2.loc[352, 'highest_price'] = 99.99\n",
    "main_2.loc[352, 'number_for_sale'] = 16"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {},
   "outputs": [],
   "source": [
    "x=420\n",
    "main_2.loc[x,'users_have'] = 14653\n",
    "main_2.loc[x, 'users_want'] = 2709\n",
    "main_2.loc[x, 'user_rating'] = 4.36\n",
    "main_2.loc[x, 'genres'] = ['Classical']\n",
    "main_2.loc[x, 'styles'] = ['Soundtrack']\n",
    "main_2.loc[x, 'versions'] = 148\n",
    "main_2.loc[x, 'last_sold'] = '08 Sep 20'\n",
    "main_2.loc[x, 'lowest_price'] = 4.00\n",
    "main_2.loc[x, 'median_price'] = 12.00\n",
    "main_2.loc[x, 'highest_price'] = 55.00\n",
    "main_2.loc[x, 'number_for_sale'] = 125"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {},
   "outputs": [],
   "source": [
    "x=554\n",
    "main_2.loc[x,'users_have'] = 15297\n",
    "main_2.loc[x, 'users_want'] = 9260\n",
    "main_2.loc[x, 'user_rating'] = 4.71\n",
    "main_2.loc[x, 'genres'] = ['Hip Hip']\n",
    "#main_2.loc[x, 'styles'] = \n",
    "main_2.loc[x, 'versions'] = 24\n",
    "main_2.loc[x, 'last_sold'] = '06 Sep 20'\n",
    "main_2.loc[x, 'lowest_price'] = 16.99\n",
    "main_2.loc[x, 'median_price'] = 28.52\n",
    "main_2.loc[x, 'highest_price'] = 298.00\n",
    "main_2.loc[x, 'number_for_sale'] = 13"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "metadata": {},
   "outputs": [],
   "source": [
    "x=560\n",
    "main_2.loc[x,'users_have'] = 29287\n",
    "main_2.loc[x, 'users_want'] = 6396\n",
    "main_2.loc[x, 'user_rating'] = 4.5\n",
    "main_2.loc[x, 'genres'] = ['Funk / Soul']\n",
    "main_2.loc[x, 'styles'] = ['Disco']\n",
    "main_2.loc[x, 'versions'] = 552\n",
    "main_2.loc[x, 'last_sold'] = '18 Sep 20'\n",
    "main_2.loc[x, 'lowest_price'] = 4.90\n",
    "main_2.loc[x, 'median_price'] = 13.24\n",
    "main_2.loc[x, 'highest_price'] = 75.00\n",
    "main_2.loc[x, 'number_for_sale'] = 197"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "x=560\n",
    "main_2.loc[x,'users_have'] = 29287\n",
    "main_2.loc[x, 'users_want'] = 6396\n",
    "main_2.loc[x, 'user_rating'] = 4.5\n",
    "main_2.loc[x, 'genres'] = ['Funk / Soul']\n",
    "main_2.loc[x, 'styles'] = ['Disco']\n",
    "main_2.loc[x, 'versions'] = 552\n",
    "main_2.loc[x, 'last_sold'] = '18 Sep 20'\n",
    "main_2.loc[x, 'lowest_price'] = 4.90\n",
    "main_2.loc[x, 'median_price'] = 13.24\n",
    "main_2.loc[x, 'highest_price'] = 75.00\n",
    "main_2.loc[x, 'number_for_sale'] = 197"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "metadata": {},
   "outputs": [],
   "source": [
    "x=563\n",
    "main_2.loc[x,'users_have'] = 3148\n",
    "main_2.loc[x, 'users_want'] = 1640\n",
    "main_2.loc[x, 'user_rating'] = 4.35\n",
    "main_2.loc[x, 'genres'] = ['Rock']\n",
    "main_2.loc[x, 'styles'] = ['Pop Rock']\n",
    "main_2.loc[x, 'versions'] = 204\n",
    "main_2.loc[x, 'last_sold'] = '04 Sep 20'\n",
    "main_2.loc[x, 'lowest_price'] = 25.64\n",
    "main_2.loc[x, 'median_price'] = 39.74\n",
    "main_2.loc[x, 'highest_price'] = 110.24\n",
    "main_2.loc[x, 'number_for_sale'] = 34"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "metadata": {},
   "outputs": [],
   "source": [
    "x=591\n",
    "main_2.loc[x,'users_have'] = 4189\n",
    "main_2.loc[x, 'users_want'] = 187\n",
    "main_2.loc[x, 'user_rating'] = 4.09\n",
    "main_2.loc[x, 'genres'] = ['Rock']\n",
    "main_2.loc[x, 'styles'] = ['Art Rock']\n",
    "main_2.loc[x, 'versions'] = 153\n",
    "main_2.loc[x, 'last_sold'] = '13 Sep 20'\n",
    "main_2.loc[x, 'lowest_price'] = 2.56\n",
    "main_2.loc[x, 'median_price'] = 6.24\n",
    "main_2.loc[x, 'highest_price'] = 35.29\n",
    "#main_2.loc[x, 'number_for_sale'] = "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "metadata": {},
   "outputs": [],
   "source": [
    "x=605\n",
    "main_2.loc[x,'users_have'] = 7750\n",
    "main_2.loc[x, 'users_want'] = 295\n",
    "main_2.loc[x, 'user_rating'] = 3.97\n",
    "main_2.loc[x, 'genres'] = ['Pop']\n",
    "main_2.loc[x, 'styles'] = ['SoundTrack']\n",
    "main_2.loc[x, 'versions'] = 153\n",
    "main_2.loc[x, 'last_sold'] = '24 Aug 20'\n",
    "main_2.loc[x, 'lowest_price'] = 1.99\n",
    "main_2.loc[x, 'median_price'] = 6.49\n",
    "main_2.loc[x, 'highest_price'] = 30.00\n",
    "main_2.loc[x, 'number_for_sale'] = 173"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "metadata": {},
   "outputs": [],
   "source": [
    "x=696\n",
    "main_2.loc[x,'users_have'] = 25091\n",
    "main_2.loc[x, 'users_want'] = 6776\n",
    "main_2.loc[x, 'user_rating'] = 4.43\n",
    "main_2.loc[x, 'genres'] = ['Funk / Soul']\n",
    "main_2.loc[x, 'styles'] = ['SoundTrack']\n",
    "main_2.loc[x, 'versions'] = 278\n",
    "main_2.loc[x, 'last_sold'] = '16 Sep 20'\n",
    "main_2.loc[x, 'lowest_price'] = 10.00\n",
    "main_2.loc[x, 'median_price'] = 19.99\n",
    "main_2.loc[x, 'highest_price'] = 51.76\n",
    "main_2.loc[x, 'number_for_sale'] = 207"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "metadata": {},
   "outputs": [],
   "source": [
    "x=707\n",
    "main_2.loc[x,'users_have'] = 10118\n",
    "main_2.loc[x, 'users_want'] = 549\n",
    "main_2.loc[x, 'user_rating'] = 3.88\n",
    "main_2.loc[x, 'genres'] = ['Stage & Screen']\n",
    "main_2.loc[x, 'styles'] = ['Folk Rock']\n",
    "main_2.loc[x, 'versions'] = 152\n",
    "main_2.loc[x, 'last_sold'] = '01 Sep 20'\n",
    "main_2.loc[x, 'lowest_price'] = 1.00\n",
    "main_2.loc[x, 'median_price'] = 3.00\n",
    "main_2.loc[x, 'highest_price'] = 8.00\n",
    "main_2.loc[x, 'number_for_sale'] = 190"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "metadata": {},
   "outputs": [],
   "source": [
    "x=733\n",
    "main_2.loc[x,'users_have'] = 527\n",
    "main_2.loc[x, 'users_want'] = 230\n",
    "main_2.loc[x, 'user_rating'] = 4.59\n",
    "main_2.loc[x, 'genres'] = ['Rock']\n",
    "main_2.loc[x, 'styles'] = ['Classic Rock']\n",
    "main_2.loc[x, 'versions'] = 199\n",
    "main_2.loc[x, 'last_sold'] = '11 Jul 20'\n",
    "main_2.loc[x, 'lowest_price'] = 20.50\n",
    "main_2.loc[x, 'median_price'] = 39.36\n",
    "main_2.loc[x, 'highest_price'] = 114.12\n",
    "main_2.loc[x, 'number_for_sale'] = 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "metadata": {},
   "outputs": [],
   "source": [
    "x=771\n",
    "main_2.loc[x,'users_have'] = 5284\n",
    "main_2.loc[x, 'users_want'] = 1825\n",
    "main_2.loc[x, 'user_rating'] = 4.43\n",
    "main_2.loc[x, 'genres'] = ['Electronic']\n",
    "main_2.loc[x, 'styles'] = ['Experimental']\n",
    "main_2.loc[x, 'versions'] = 93\n",
    "main_2.loc[x, 'last_sold'] = '25 Aug 20'\n",
    "main_2.loc[x, 'lowest_price'] = 13.00\n",
    "main_2.loc[x, 'median_price'] = 19.99\n",
    "main_2.loc[x, 'highest_price'] = 34.50\n",
    "main_2.loc[x, 'number_for_sale'] = 36"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "metadata": {},
   "outputs": [],
   "source": [
    "x=796\n",
    "main_2.loc[x,'users_have'] = 111\n",
    "main_2.loc[x, 'users_want'] = 629\n",
    "main_2.loc[x, 'user_rating'] = 4.58\n",
    "main_2.loc[x, 'genres'] = ['Rock']\n",
    "main_2.loc[x, 'styles'] = ['Psychedelic Rock']\n",
    "#main_2.loc[x, 'versions'] = 194\n",
    "#main_2.loc[x, 'last_sold'] = '25 Aug 20'\n",
    "#main_2.loc[x, 'lowest_price'] = 13.00\n",
    "#main_2.loc[x, 'median_price'] = 19.99\n",
    "#main_2.loc[x, 'highest_price'] = 34.50\n",
    "#main_2.loc[x, 'number_for_sale'] = 36"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "metadata": {},
   "outputs": [],
   "source": [
    "x=822\n",
    "main_2.loc[x,'users_have'] = 4931\n",
    "main_2.loc[x, 'users_want'] = 522\n",
    "main_2.loc[x, 'user_rating'] = 4.06\n",
    "main_2.loc[x, 'genres'] = ['Rock']\n",
    "main_2.loc[x, 'styles'] = ['Blues Rock']\n",
    "main_2.loc[x, 'versions'] = 132\n",
    "main_2.loc[x, 'last_sold'] = '21 Aug 20'\n",
    "main_2.loc[x, 'lowest_price'] = 3.27\n",
    "main_2.loc[x, 'median_price'] = 12.00\n",
    "main_2.loc[x, 'highest_price'] = 69.00\n",
    "main_2.loc[x, 'number_for_sale'] = 48"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "metadata": {},
   "outputs": [],
   "source": [
    "x=855\n",
    "main_2.loc[x,'users_have'] = 22030\n",
    "main_2.loc[x, 'users_want'] = 2831\n",
    "main_2.loc[x, 'user_rating'] = 4.11\n",
    "main_2.loc[x, 'genres'] = ['Rock']\n",
    "main_2.loc[x, 'styles'] = ['Pop Rock']\n",
    "main_2.loc[x, 'versions'] = 318\n",
    "main_2.loc[x, 'last_sold'] = '22 Sep 20'\n",
    "main_2.loc[x, 'lowest_price'] = 3.00\n",
    "main_2.loc[x, 'median_price'] = 10.00\n",
    "main_2.loc[x, 'highest_price'] = 22.00\n",
    "main_2.loc[x, 'number_for_sale'] = 167"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "metadata": {},
   "outputs": [],
   "source": [
    "x=928\n",
    "main_2.loc[x,'users_have'] = 561\n",
    "main_2.loc[x, 'users_want'] = 716\n",
    "main_2.loc[x, 'user_rating'] = 4.39\n",
    "main_2.loc[x, 'genres'] = ['Rock']\n",
    "main_2.loc[x, 'styles'] = ['Blues Rock']\n",
    "main_2.loc[x, 'versions'] = 192\n",
    "main_2.loc[x, 'last_sold'] = '02 Aug 20'\n",
    "main_2.loc[x, 'lowest_price'] = 70.00\n",
    "main_2.loc[x, 'median_price'] = 102.56\n",
    "main_2.loc[x, 'highest_price'] = 211.54\n",
    "main_2.loc[x, 'number_for_sale'] = 17"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "metadata": {},
   "outputs": [],
   "source": [
    "x=991\n",
    "main_2.loc[x,'users_have'] = 20057\n",
    "main_2.loc[x, 'users_want'] = 2458\n",
    "main_2.loc[x, 'user_rating'] = 4.33\n",
    "main_2.loc[x, 'genres'] = ['Rock']\n",
    "main_2.loc[x, 'styles'] = ['Folk Rock']\n",
    "main_2.loc[x, 'versions'] = 346\n",
    "main_2.loc[x, 'last_sold'] = '16 Sep 20'\n",
    "main_2.loc[x, 'lowest_price'] = 2.99\n",
    "main_2.loc[x, 'median_price'] = 12.00\n",
    "main_2.loc[x, 'highest_price'] = 159.99\n",
    "main_2.loc[x, 'number_for_sale'] = 74"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 169,
   "metadata": {},
   "outputs": [],
   "source": [
    "x=1042\n",
    "main_2.loc[x,'users_have'] = 5489\n",
    "main_2.loc[x, 'users_want'] = 1084\n",
    "main_2.loc[x, 'user_rating'] = 4.29\n",
    "main_2.loc[x, 'genres'] = ['Rock']\n",
    "main_2.loc[x, 'styles'] = ['Folk Rock']\n",
    "main_2.loc[x, 'versions'] = 92\n",
    "main_2.loc[x, 'last_sold'] = '30 Aug 20'\n",
    "main_2.loc[x, 'lowest_price'] = 7.00\n",
    "main_2.loc[x, 'median_price'] = 19.96\n",
    "main_2.loc[x, 'highest_price'] = 50.00\n",
    "main_2.loc[x, 'number_for_sale'] = 49"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('final_record_df_manual_update.pickle', 'wb') as write_file:\n",
    "    pickle.dump(main_2, write_file)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the end three albums still lacked the target variable of median price, because thier first version/edition was never sold on Discogs. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "test = main_2.drop(np.arange(50,100), axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1000, 28)"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "test.reset_index(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "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>level_0</th>\n",
       "      <th>artists</th>\n",
       "      <th>albums</th>\n",
       "      <th>artist_links</th>\n",
       "      <th>album_links</th>\n",
       "      <th>release_year</th>\n",
       "      <th>label</th>\n",
       "      <th>release_country</th>\n",
       "      <th>total_artist_albums</th>\n",
       "      <th>artist_last_years</th>\n",
       "      <th>...</th>\n",
       "      <th>median_price</th>\n",
       "      <th>highest_price</th>\n",
       "      <th>last_sold</th>\n",
       "      <th>styles</th>\n",
       "      <th>genres</th>\n",
       "      <th>number_for_sale</th>\n",
       "      <th>versions</th>\n",
       "      <th>top_artists_spotify_name</th>\n",
       "      <th>spotify_monthly_listeners</th>\n",
       "      <th>top_artists_orig_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>Pink Floyd</td>\n",
       "      <td>The Dark Side Of The Moon</td>\n",
       "      <td>https://www.discogs.com/artist/45467-Pink-Floyd</td>\n",
       "      <td>https://www.discogs.com/Pink-Floyd-The-Dark-Si...</td>\n",
       "      <td>1973</td>\n",
       "      <td>Harvest</td>\n",
       "      <td>UK</td>\n",
       "      <td>45</td>\n",
       "      <td>2020</td>\n",
       "      <td>...</td>\n",
       "      <td>246.75</td>\n",
       "      <td>1,381.05</td>\n",
       "      <td>10 Aug 20</td>\n",
       "      <td>[Prog Rock, Psychedelic Rock]</td>\n",
       "      <td>[Rock]</td>\n",
       "      <td>68</td>\n",
       "      <td>998</td>\n",
       "      <td>Pink Floyd</td>\n",
       "      <td>13643471</td>\n",
       "      <td>Pink Floyd</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>The Beatles</td>\n",
       "      <td>Sgt. Pepper's Lonely Hearts Club Band</td>\n",
       "      <td>https://www.discogs.com/artist/82730-The-Beatles</td>\n",
       "      <td>https://www.discogs.com/The-Beatles-Sgt-Pepper...</td>\n",
       "      <td>1967</td>\n",
       "      <td>Parlophone</td>\n",
       "      <td>UK</td>\n",
       "      <td>157</td>\n",
       "      <td>2018</td>\n",
       "      <td>...</td>\n",
       "      <td>110.36</td>\n",
       "      <td>324.61</td>\n",
       "      <td>01 Sep 20</td>\n",
       "      <td>[Rock &amp; Roll, Psychedelic Rock]</td>\n",
       "      <td>[Rock]</td>\n",
       "      <td>150</td>\n",
       "      <td>918</td>\n",
       "      <td>The Beatles</td>\n",
       "      <td>22982890</td>\n",
       "      <td>The Beatles</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>The Beatles</td>\n",
       "      <td>Abbey Road</td>\n",
       "      <td>https://www.discogs.com/artist/82730-The-Beatles</td>\n",
       "      <td>https://www.discogs.com/The-Beatles-Abbey-Road...</td>\n",
       "      <td>1969</td>\n",
       "      <td>Apple Records</td>\n",
       "      <td>UK</td>\n",
       "      <td>157</td>\n",
       "      <td>2018</td>\n",
       "      <td>...</td>\n",
       "      <td>77.65</td>\n",
       "      <td>391.30</td>\n",
       "      <td>29 Sep 20</td>\n",
       "      <td>[Pop Rock]</td>\n",
       "      <td>[Rock]</td>\n",
       "      <td>28</td>\n",
       "      <td>720</td>\n",
       "      <td>The Beatles</td>\n",
       "      <td>22982890</td>\n",
       "      <td>The Beatles</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>Pink Floyd</td>\n",
       "      <td>Wish You Were Here</td>\n",
       "      <td>https://www.discogs.com/artist/45467-Pink-Floyd</td>\n",
       "      <td>https://www.discogs.com/Pink-Floyd-Wish-You-We...</td>\n",
       "      <td>1975</td>\n",
       "      <td>Harvest</td>\n",
       "      <td>UK</td>\n",
       "      <td>45</td>\n",
       "      <td>2020</td>\n",
       "      <td>...</td>\n",
       "      <td>49.68</td>\n",
       "      <td>103.90</td>\n",
       "      <td>29 Aug 20</td>\n",
       "      <td>[Prog Rock]</td>\n",
       "      <td>[Rock]</td>\n",
       "      <td>130</td>\n",
       "      <td>729</td>\n",
       "      <td>Pink Floyd</td>\n",
       "      <td>13643471</td>\n",
       "      <td>Pink Floyd</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>Michael Jackson</td>\n",
       "      <td>Thriller</td>\n",
       "      <td>https://www.discogs.com/artist/15885-Michael-J...</td>\n",
       "      <td>https://www.discogs.com/Michael-Jackson-Thrill...</td>\n",
       "      <td>1982</td>\n",
       "      <td>Epic</td>\n",
       "      <td>US</td>\n",
       "      <td>22</td>\n",
       "      <td>2014</td>\n",
       "      <td>...</td>\n",
       "      <td>13.24</td>\n",
       "      <td>75.00</td>\n",
       "      <td>18 Sep 20</td>\n",
       "      <td>[Disco, Soul]</td>\n",
       "      <td>[Funk / Soul, Pop]</td>\n",
       "      <td>197</td>\n",
       "      <td>552</td>\n",
       "      <td>Michael Jackson</td>\n",
       "      <td>23635338</td>\n",
       "      <td>Michael Jackson</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 29 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   level_0          artists                                 albums  \\\n",
       "0        0       Pink Floyd              The Dark Side Of The Moon   \n",
       "1        1      The Beatles  Sgt. Pepper's Lonely Hearts Club Band   \n",
       "2        2      The Beatles                             Abbey Road   \n",
       "3        3       Pink Floyd                     Wish You Were Here   \n",
       "4        4  Michael Jackson                               Thriller   \n",
       "\n",
       "                                        artist_links  \\\n",
       "0    https://www.discogs.com/artist/45467-Pink-Floyd   \n",
       "1   https://www.discogs.com/artist/82730-The-Beatles   \n",
       "2   https://www.discogs.com/artist/82730-The-Beatles   \n",
       "3    https://www.discogs.com/artist/45467-Pink-Floyd   \n",
       "4  https://www.discogs.com/artist/15885-Michael-J...   \n",
       "\n",
       "                                         album_links release_year  \\\n",
       "0  https://www.discogs.com/Pink-Floyd-The-Dark-Si...         1973   \n",
       "1  https://www.discogs.com/The-Beatles-Sgt-Pepper...         1967   \n",
       "2  https://www.discogs.com/The-Beatles-Abbey-Road...         1969   \n",
       "3  https://www.discogs.com/Pink-Floyd-Wish-You-We...         1975   \n",
       "4  https://www.discogs.com/Michael-Jackson-Thrill...         1982   \n",
       "\n",
       "           label release_country total_artist_albums artist_last_years  ...  \\\n",
       "0        Harvest              UK                  45              2020  ...   \n",
       "1     Parlophone              UK                 157              2018  ...   \n",
       "2  Apple Records              UK                 157              2018  ...   \n",
       "3        Harvest              UK                  45              2020  ...   \n",
       "4           Epic              US                  22              2014  ...   \n",
       "\n",
       "  median_price highest_price  last_sold                           styles  \\\n",
       "0       246.75      1,381.05  10 Aug 20    [Prog Rock, Psychedelic Rock]   \n",
       "1       110.36        324.61  01 Sep 20  [Rock & Roll, Psychedelic Rock]   \n",
       "2        77.65        391.30  29 Sep 20                       [Pop Rock]   \n",
       "3        49.68        103.90  29 Aug 20                      [Prog Rock]   \n",
       "4        13.24         75.00  18 Sep 20                    [Disco, Soul]   \n",
       "\n",
       "               genres number_for_sale versions top_artists_spotify_name  \\\n",
       "0              [Rock]              68      998               Pink Floyd   \n",
       "1              [Rock]             150      918              The Beatles   \n",
       "2              [Rock]              28      720              The Beatles   \n",
       "3              [Rock]             130      729               Pink Floyd   \n",
       "4  [Funk / Soul, Pop]             197      552          Michael Jackson   \n",
       "\n",
       "  spotify_monthly_listeners top_artists_orig_name  \n",
       "0                  13643471            Pink Floyd  \n",
       "1                  22982890           The Beatles  \n",
       "2                  22982890           The Beatles  \n",
       "3                  13643471            Pink Floyd  \n",
       "4                  23635338       Michael Jackson  \n",
       "\n",
       "[5 rows x 29 columns]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "test = test.drop('level_0',axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'/Users/beth/Documents/Metis/metis_project_2/metis_project2'"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "os.getcwd()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('final_record_1000_manual_update.pickle', 'wb') as write_file:\n",
    "    pickle.dump(test, write_file)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}