clean_and_explore.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "01a9f99d",
   "metadata": {},
   "source": [
    "# Putting together my library"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "524f3825",
   "metadata": {},
   "source": [
    "I have several thousand books in 3 main formats and I'd like to combine them all so I can get a better picture of the books that I own. I also share an Audible account with my cousin and would like to combine her Audible library with my own. Then we can both use the library to make sure we don't buy books we already have. \n",
    "\n",
    "I have some books in more than one format, but I'd like to merge them all and say which formats I have instead of multple listings of the same book in different formats. At the moment, I have all my ebooks cataloged in Calibre. I can add my audiobooks (mp3, audible, etc) to those entries but I'd have to do it one-by-one, which I'd like to avoid. I have a library website running in docker in a Synology home server, but it uses the database from Calibre to populate the books. \n",
    "\n",
    "I decided that putting all my books together in one csv file with only one entry per book, regardless of the format, I could import them directly into Calibre as a separate library and use that for the web. Eventually, I'd like to add more information so I can do some statistics on the files, but I'm not up to that challenge...yet."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "01c5274d",
   "metadata": {},
   "source": [
    "## Import all the things"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "6bba4146",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "import re\n",
    "\n",
    "from isbntools.app import *\n",
    "\n",
    "import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "5f90ca58",
   "metadata": {},
   "outputs": [],
   "source": [
    "audible = pd.read_csv('my_data/audible.csv')\n",
    "audible2 = pd.read_csv('my_data/second_audible.csv')\n",
    "abooks = pd.read_csv('my_data/audiobooks.csv')\n",
    "ebooks = pd.read_csv('my_data/ebooks.csv')\n",
    "goodreads = pd.read_csv('my_data/goodreads.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4abe40ba",
   "metadata": {},
   "source": [
    "**Dataframes and what they contain**\n",
    "1. audible (audible.csv) - contains all the audio books in my audible library (extracted using ALE browser extension)*\n",
    "2. audible (openaudible.csv) -contains all the audio books in my audible library (extracted using openaudible)*\n",
    "3. audible2 (second_audible.csv) - contains all the audio books in my cousin's audible library (extracted using ALE)\n",
    "4. abooks (audiobooks.csv) - contains all audiobooks I own in mp3 format - shouldn't be in audible\n",
    "5. ebooks (ebooks.csv) - contains most of my ebooks, but is missing some from Amazon\n",
    "6. goodreads (goodreads.csv) - contains all the books from my goodreads shelves\n",
    "7. kindle (kindle.csv) - contains all my Kindle books converted from xml format (see get_kindle_books for process)\n",
    "\n",
    "* these methods have a different number of titles and they both differ from the number of titles listed in the audible android app.\n",
    "\n",
    "All of these files should be fairly clean, but some are more complete than others. I need to get the same information for them all, which may require scraping the data from amazon or goodreads. Eventually I want all the data in one file and have at least these columns:\n",
    "- Title\n",
    "- Author(s)\n",
    "- Tag\n",
    "- Comments (blurb)\n",
    "- Page numbers or length of audiofile\n",
    "- Series (won't apply to all)\n",
    "- ISBN or other id number\n",
    "\n",
    "When they're imported to Calibre, I can add covers easily. Eventually, I'd like to include images in the data so I can use a nicer search function, but for now I'm keeping it as simple as possible. I'd also like to add finished reading dates and genres so I can play around with reading and library statistics."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "faaf585f",
   "metadata": {},
   "source": [
    "## My ebooks"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e44d4723",
   "metadata": {},
   "source": [
    "I'll start off getting my ebook dataframe organized. I'm starting with this one because this is the format I'll need to use to import everythinng into Calibre later. This is a list of all the ebooks that I have in Calibre already. I still have more to add from Amazon ."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "0fbfed7d",
   "metadata": {
    "scrolled": true
   },
   "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>title</th>\n",
       "      <th>authors</th>\n",
       "      <th>series</th>\n",
       "      <th>series_index</th>\n",
       "      <th>#series</th>\n",
       "      <th>#series_index</th>\n",
       "      <th>comments</th>\n",
       "      <th>tags</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>isbn</th>\n",
       "      <th>identifiers</th>\n",
       "      <th>#pages</th>\n",
       "      <th>pubdate</th>\n",
       "      <th>publisher</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BF03-A Dragon's Ascension</td>\n",
       "      <td>Ed Greenwood</td>\n",
       "      <td>Band of Four</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Aglirta is known as the Kingless Land. Once a ...</td>\n",
       "      <td>Fantasy, Dragon, Epic</td>\n",
       "      <td>2019-06-11T17:14:09-04:00</td>\n",
       "      <td>9780765341440</td>\n",
       "      <td>isbn:9780765341440,google:mhgx03oeVJUC</td>\n",
       "      <td>397.0</td>\n",
       "      <td>2003-02-17T00:00:00-05:00</td>\n",
       "      <td>Macmillan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A Fiend in Need</td>\n",
       "      <td>Maryjanice Davidson</td>\n",
       "      <td>Queen Betsy</td>\n",
       "      <td>4.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>\"A Fiend in Need\" reveals the true identity of...</td>\n",
       "      <td>Short Story, Humorous, Paranormal, Romance, Sh...</td>\n",
       "      <td>2019-06-11T17:06:06-04:00</td>\n",
       "      <td>9780425207956</td>\n",
       "      <td>amazon:B000PDYVUC,isbn:9780425207956,google:QF...</td>\n",
       "      <td>59.0</td>\n",
       "      <td>2006-04-04T00:00:00-04:00</td>\n",
       "      <td>Berkley</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>The Blades of the Rose Bundle: Warrior, Scound...</td>\n",
       "      <td>Zoe Archer</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>****Zoe Archer’s BLADES OF THE ROSE series—fea...</td>\n",
       "      <td>Fiction, Historical, Romance, Stempunk, Suspen...</td>\n",
       "      <td>2019-01-23T23:33:35-05:00</td>\n",
       "      <td>9781420122954</td>\n",
       "      <td>isbn:9781420122954,google:zOZIM73fXIwC,mobi-as...</td>\n",
       "      <td>1912.0</td>\n",
       "      <td>2010-11-30T23:54:00-05:00</td>\n",
       "      <td>Zebra</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BR03-Rebel</td>\n",
       "      <td>Zoë Archer</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>On the Canadian frontier in 1875, nature is a ...</td>\n",
       "      <td>Paranormal, Romance, Historical</td>\n",
       "      <td>2019-06-11T17:32:44-04:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>465.0</td>\n",
       "      <td>2010-10-31T20:00:00-04:00</td>\n",
       "      <td>Zebra</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BR04-Stranger</td>\n",
       "      <td>Zoë Archer</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>He protects the world’s magic -- with his scie...</td>\n",
       "      <td>paranormal romance</td>\n",
       "      <td>2019-06-11T17:32:43-04:00</td>\n",
       "      <td>9781420119862</td>\n",
       "      <td>isbn:9781420119862</td>\n",
       "      <td>529.0</td>\n",
       "      <td>2010-11-30T19:00:00-05:00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                               title              authors  \\\n",
       "0                          BF03-A Dragon's Ascension         Ed Greenwood   \n",
       "1                                    A Fiend in Need  Maryjanice Davidson   \n",
       "2  The Blades of the Rose Bundle: Warrior, Scound...           Zoe Archer   \n",
       "3                                         BR03-Rebel           Zoë Archer   \n",
       "4                                      BR04-Stranger           Zoë Archer   \n",
       "\n",
       "         series  series_index #series  #series_index  \\\n",
       "0  Band of Four           3.0     NaN            NaN   \n",
       "1   Queen Betsy           4.5     NaN            NaN   \n",
       "2           NaN           1.0     NaN            NaN   \n",
       "3           NaN           1.0     NaN            NaN   \n",
       "4           NaN           1.0     NaN            NaN   \n",
       "\n",
       "                                            comments  \\\n",
       "0  Aglirta is known as the Kingless Land. Once a ...   \n",
       "1  \"A Fiend in Need\" reveals the true identity of...   \n",
       "2  ****Zoe Archer’s BLADES OF THE ROSE series—fea...   \n",
       "3  On the Canadian frontier in 1875, nature is a ...   \n",
       "4  He protects the world’s magic -- with his scie...   \n",
       "\n",
       "                                                tags  \\\n",
       "0                              Fantasy, Dragon, Epic   \n",
       "1  Short Story, Humorous, Paranormal, Romance, Sh...   \n",
       "2  Fiction, Historical, Romance, Stempunk, Suspen...   \n",
       "3                    Paranormal, Romance, Historical   \n",
       "4                                 paranormal romance   \n",
       "\n",
       "                   timestamp           isbn  \\\n",
       "0  2019-06-11T17:14:09-04:00  9780765341440   \n",
       "1  2019-06-11T17:06:06-04:00  9780425207956   \n",
       "2  2019-01-23T23:33:35-05:00  9781420122954   \n",
       "3  2019-06-11T17:32:44-04:00            NaN   \n",
       "4  2019-06-11T17:32:43-04:00  9781420119862   \n",
       "\n",
       "                                         identifiers  #pages  \\\n",
       "0             isbn:9780765341440,google:mhgx03oeVJUC   397.0   \n",
       "1  amazon:B000PDYVUC,isbn:9780425207956,google:QF...    59.0   \n",
       "2  isbn:9781420122954,google:zOZIM73fXIwC,mobi-as...  1912.0   \n",
       "3                                                NaN   465.0   \n",
       "4                                 isbn:9781420119862   529.0   \n",
       "\n",
       "                     pubdate  publisher  \n",
       "0  2003-02-17T00:00:00-05:00  Macmillan  \n",
       "1  2006-04-04T00:00:00-04:00    Berkley  \n",
       "2  2010-11-30T23:54:00-05:00      Zebra  \n",
       "3  2010-10-31T20:00:00-04:00      Zebra  \n",
       "4  2010-11-30T19:00:00-05:00        NaN  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ebooks.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "49d9979d",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 5922 entries, 0 to 5921\n",
      "Data columns (total 14 columns):\n",
      " #   Column         Non-Null Count  Dtype  \n",
      "---  ------         --------------  -----  \n",
      " 0   title          5922 non-null   object \n",
      " 1   authors        5922 non-null   object \n",
      " 2   series         2066 non-null   object \n",
      " 3   series_index   5922 non-null   float64\n",
      " 4   #series        4 non-null      object \n",
      " 5   #series_index  4 non-null      float64\n",
      " 6   comments       5147 non-null   object \n",
      " 7   tags           4949 non-null   object \n",
      " 8   timestamp      5922 non-null   object \n",
      " 9   isbn           4140 non-null   object \n",
      " 10  identifiers    5618 non-null   object \n",
      " 11  #pages         4212 non-null   float64\n",
      " 12  pubdate        5922 non-null   object \n",
      " 13  publisher      4956 non-null   object \n",
      "dtypes: float64(3), object(11)\n",
      "memory usage: 647.8+ KB\n"
     ]
    }
   ],
   "source": [
    "ebooks.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1799190a",
   "metadata": {},
   "source": [
    "### Preliminary Thoughts\n",
    "\n",
    "This data is pretty clean already since it's from Calibre. I want to drop the isbn column since it's included in the identfiers column. I'll drop the hashes in front of column names. I want to keep only the year for the pubdate and only the mm/dd/yyyy from the timestamp. I'm only keep that one so I'll be able to add new ebooks more easily later. There are some titles with a code in front that I'll want to drop. This helped keep series organized on older Kindles, but now they get put into collections so it's not needed anymore."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c33ab28b",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Start Cleaning\n",
    "\n",
    "I want to:\n",
    "- rename #series and #series number to series2 and series2 number\n",
    "- change NAN values in all series columns to blank strings\n",
    "- drop my code from the beginning of any titles that still have it\n",
    "- change timestamp column to datetime and only keep the year/month/day\n",
    "- only keep the year in the pubdate column - it doesn't need to be datetime\n",
    "- make tags all lower case\n",
    "- fill any missing values in identifier column with value in isbn column if possible\n",
    "- put brackets around the series number in both series number columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "f74f6561",
   "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>title</th>\n",
       "      <th>authors</th>\n",
       "      <th>series</th>\n",
       "      <th>series_number</th>\n",
       "      <th>series2</th>\n",
       "      <th>series2_number</th>\n",
       "      <th>summary</th>\n",
       "      <th>tags</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>isbn</th>\n",
       "      <th>identifiers</th>\n",
       "      <th>pages</th>\n",
       "      <th>pubdate</th>\n",
       "      <th>publisher</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BF03-A Dragon's Ascension</td>\n",
       "      <td>Ed Greenwood</td>\n",
       "      <td>Band of Four</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Aglirta is known as the Kingless Land. Once a ...</td>\n",
       "      <td>Fantasy, Dragon, Epic</td>\n",
       "      <td>2019-06-11T17:14:09-04:00</td>\n",
       "      <td>9780765341440</td>\n",
       "      <td>isbn:9780765341440,google:mhgx03oeVJUC</td>\n",
       "      <td>397.0</td>\n",
       "      <td>2003-02-17T00:00:00-05:00</td>\n",
       "      <td>Macmillan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A Fiend in Need</td>\n",
       "      <td>Maryjanice Davidson</td>\n",
       "      <td>Queen Betsy</td>\n",
       "      <td>4.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>\"A Fiend in Need\" reveals the true identity of...</td>\n",
       "      <td>Short Story, Humorous, Paranormal, Romance, Sh...</td>\n",
       "      <td>2019-06-11T17:06:06-04:00</td>\n",
       "      <td>9780425207956</td>\n",
       "      <td>amazon:B000PDYVUC,isbn:9780425207956,google:QF...</td>\n",
       "      <td>59.0</td>\n",
       "      <td>2006-04-04T00:00:00-04:00</td>\n",
       "      <td>Berkley</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>The Blades of the Rose Bundle: Warrior, Scound...</td>\n",
       "      <td>Zoe Archer</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>****Zoe Archer’s BLADES OF THE ROSE series—fea...</td>\n",
       "      <td>Fiction, Historical, Romance, Stempunk, Suspen...</td>\n",
       "      <td>2019-01-23T23:33:35-05:00</td>\n",
       "      <td>9781420122954</td>\n",
       "      <td>isbn:9781420122954,google:zOZIM73fXIwC,mobi-as...</td>\n",
       "      <td>1912.0</td>\n",
       "      <td>2010-11-30T23:54:00-05:00</td>\n",
       "      <td>Zebra</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BR03-Rebel</td>\n",
       "      <td>Zoë Archer</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>On the Canadian frontier in 1875, nature is a ...</td>\n",
       "      <td>Paranormal, Romance, Historical</td>\n",
       "      <td>2019-06-11T17:32:44-04:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>465.0</td>\n",
       "      <td>2010-10-31T20:00:00-04:00</td>\n",
       "      <td>Zebra</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BR04-Stranger</td>\n",
       "      <td>Zoë Archer</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>He protects the world’s magic -- with his scie...</td>\n",
       "      <td>paranormal romance</td>\n",
       "      <td>2019-06-11T17:32:43-04:00</td>\n",
       "      <td>9781420119862</td>\n",
       "      <td>isbn:9781420119862</td>\n",
       "      <td>529.0</td>\n",
       "      <td>2010-11-30T19:00:00-05:00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                               title              authors  \\\n",
       "0                          BF03-A Dragon's Ascension         Ed Greenwood   \n",
       "1                                    A Fiend in Need  Maryjanice Davidson   \n",
       "2  The Blades of the Rose Bundle: Warrior, Scound...           Zoe Archer   \n",
       "3                                         BR03-Rebel           Zoë Archer   \n",
       "4                                      BR04-Stranger           Zoë Archer   \n",
       "\n",
       "         series  series_number series2  series2_number  \\\n",
       "0  Band of Four            3.0     NaN             NaN   \n",
       "1   Queen Betsy            4.5     NaN             NaN   \n",
       "2           NaN            1.0     NaN             NaN   \n",
       "3           NaN            1.0     NaN             NaN   \n",
       "4           NaN            1.0     NaN             NaN   \n",
       "\n",
       "                                             summary  \\\n",
       "0  Aglirta is known as the Kingless Land. Once a ...   \n",
       "1  \"A Fiend in Need\" reveals the true identity of...   \n",
       "2  ****Zoe Archer’s BLADES OF THE ROSE series—fea...   \n",
       "3  On the Canadian frontier in 1875, nature is a ...   \n",
       "4  He protects the world’s magic -- with his scie...   \n",
       "\n",
       "                                                tags  \\\n",
       "0                              Fantasy, Dragon, Epic   \n",
       "1  Short Story, Humorous, Paranormal, Romance, Sh...   \n",
       "2  Fiction, Historical, Romance, Stempunk, Suspen...   \n",
       "3                    Paranormal, Romance, Historical   \n",
       "4                                 paranormal romance   \n",
       "\n",
       "                   timestamp           isbn  \\\n",
       "0  2019-06-11T17:14:09-04:00  9780765341440   \n",
       "1  2019-06-11T17:06:06-04:00  9780425207956   \n",
       "2  2019-01-23T23:33:35-05:00  9781420122954   \n",
       "3  2019-06-11T17:32:44-04:00            NaN   \n",
       "4  2019-06-11T17:32:43-04:00  9781420119862   \n",
       "\n",
       "                                         identifiers   pages  \\\n",
       "0             isbn:9780765341440,google:mhgx03oeVJUC   397.0   \n",
       "1  amazon:B000PDYVUC,isbn:9780425207956,google:QF...    59.0   \n",
       "2  isbn:9781420122954,google:zOZIM73fXIwC,mobi-as...  1912.0   \n",
       "3                                                NaN   465.0   \n",
       "4                                 isbn:9781420119862   529.0   \n",
       "\n",
       "                     pubdate  publisher  \n",
       "0  2003-02-17T00:00:00-05:00  Macmillan  \n",
       "1  2006-04-04T00:00:00-04:00    Berkley  \n",
       "2  2010-11-30T23:54:00-05:00      Zebra  \n",
       "3  2010-10-31T20:00:00-04:00      Zebra  \n",
       "4  2010-11-30T19:00:00-05:00        NaN  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# rename the columns and rearrange their order\n",
    "columns = ['title', 'authors', 'series', 'series_number',\n",
    "           'series2', 'series2_number', 'summary', 'tags', \n",
    "           'timestamp', 'isbn', 'identifiers', 'pages', \n",
    "           'pubdate', 'publisher']\n",
    "ebooks.columns = columns\n",
    "\n",
    "ebooks.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "7556819f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# fill NaN values with blank strings in the 4 columns that start with series\n",
    "ebooks.iloc[:, 2:6] = ebooks.iloc[:, 2:6].fillna('')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "26194f3c-965b-4b44-9abd-dfdd87279039",
   "metadata": {},
   "outputs": [],
   "source": [
    "# some series number rows have entries without a series - delete those\n",
    "ebooks.loc[(ebooks['series'] == ''), 'series_number'] = ''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "9c1da1be",
   "metadata": {},
   "outputs": [],
   "source": [
    "# get rid of code at beginning of some titles\n",
    "# if a book has a hyphen, then split the column at the hyphen and use only the second half of the title (after the code)\n",
    "# otherwise, keep the title as it is\n",
    "drop_code = ebooks['title'].str.split('-').str[1]\n",
    "ebooks['title'] = np.where(ebooks['title'].str.contains('-'), drop_code, ebooks['title'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "4c368841",
   "metadata": {},
   "outputs": [],
   "source": [
    "# get rid of hours/min/sec by dropping everything after the T\n",
    "ebooks['timestamp'] = ebooks['timestamp'].str.split('T').str[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "bf509e30",
   "metadata": {},
   "outputs": [],
   "source": [
    "# there were some rows with 100 as the year, so I changed those to 1/1/2019\n",
    "# any date in the past would work since I'm only going to use this to pull out the recent books later\n",
    "ebooks.loc[ebooks['timestamp'].str.contains('100'), 'timestamp'] = '2019-01-01'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "5d2a2e70",
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert the timestamp column to datetime\n",
    "ebooks['timestamp']= pd.to_datetime(ebooks['timestamp'], infer_datetime_format = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "84d94aba",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# keep just the year for the pubdate column\n",
    "ebooks['pubdate'] = ebooks['pubdate'].str.split('-').str[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "ce4f27c1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# change tags to lower case for consistency\n",
    "ebooks['tags'] = ebooks['tags'].str.lower()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "ca5f1b60-ef10-4a57-bd85-9ce2bc0b1e53",
   "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>title</th>\n",
       "      <th>authors</th>\n",
       "      <th>series</th>\n",
       "      <th>series_number</th>\n",
       "      <th>series2</th>\n",
       "      <th>series2_number</th>\n",
       "      <th>summary</th>\n",
       "      <th>tags</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>isbn</th>\n",
       "      <th>identifiers</th>\n",
       "      <th>pages</th>\n",
       "      <th>pubdate</th>\n",
       "      <th>publisher</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [title, authors, series, series_number, series2, series2_number, summary, tags, timestamp, isbn, identifiers, pages, pubdate, publisher]\n",
       "Index: []"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# are there any rows where the isbn column has a value but identifiers doesn't\n",
    "ebooks[(ebooks['identifiers'].isnull()) & (ebooks['isbn'].notnull())]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "53fb492c-3cc7-471a-96d0-5c0428cb533a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# if the identifiers value is missing, so is the isbn value, so I'll just drop the isbn column\n",
    "ebooks = ebooks.drop('isbn', axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "e6a59b3b-4080-484a-b0a0-8e2f7c908c22",
   "metadata": {},
   "outputs": [],
   "source": [
    "# add brackets around the series_number and series2_number values\n",
    "# if the series or series2 columns are blank then leave the numbers alone\n",
    "ebooks.loc[(ebooks['series'] != ''), 'series_number'] = '[' + ebooks['series_number'].astype(str) + ']'\n",
    "ebooks.loc[(ebooks['series2'] != ''), 'series2_number'] = '[' + ebooks['series2_number'].astype(str) + ']'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "47b4477a",
   "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>title</th>\n",
       "      <th>authors</th>\n",
       "      <th>series</th>\n",
       "      <th>series_number</th>\n",
       "      <th>series2</th>\n",
       "      <th>series2_number</th>\n",
       "      <th>summary</th>\n",
       "      <th>tags</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>identifiers</th>\n",
       "      <th>pages</th>\n",
       "      <th>pubdate</th>\n",
       "      <th>publisher</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A Dragon's Ascension</td>\n",
       "      <td>Ed Greenwood</td>\n",
       "      <td>Band of Four</td>\n",
       "      <td>[3.0]</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Aglirta is known as the Kingless Land. Once a ...</td>\n",
       "      <td>fantasy, dragon, epic</td>\n",
       "      <td>2019-06-11</td>\n",
       "      <td>isbn:9780765341440,google:mhgx03oeVJUC</td>\n",
       "      <td>397.0</td>\n",
       "      <td>2003</td>\n",
       "      <td>Macmillan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A Fiend in Need</td>\n",
       "      <td>Maryjanice Davidson</td>\n",
       "      <td>Queen Betsy</td>\n",
       "      <td>[4.5]</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>\"A Fiend in Need\" reveals the true identity of...</td>\n",
       "      <td>short story, humorous, paranormal, romance, sh...</td>\n",
       "      <td>2019-06-11</td>\n",
       "      <td>amazon:B000PDYVUC,isbn:9780425207956,google:QF...</td>\n",
       "      <td>59.0</td>\n",
       "      <td>2006</td>\n",
       "      <td>Berkley</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>The Blades of the Rose Bundle: Warrior, Scound...</td>\n",
       "      <td>Zoe Archer</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>****Zoe Archer’s BLADES OF THE ROSE series—fea...</td>\n",
       "      <td>fiction, historical, romance, stempunk, suspen...</td>\n",
       "      <td>2019-01-23</td>\n",
       "      <td>isbn:9781420122954,google:zOZIM73fXIwC,mobi-as...</td>\n",
       "      <td>1912.0</td>\n",
       "      <td>2010</td>\n",
       "      <td>Zebra</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                               title              authors  \\\n",
       "0                               A Dragon's Ascension         Ed Greenwood   \n",
       "1                                    A Fiend in Need  Maryjanice Davidson   \n",
       "2  The Blades of the Rose Bundle: Warrior, Scound...           Zoe Archer   \n",
       "\n",
       "         series series_number series2 series2_number  \\\n",
       "0  Band of Four         [3.0]                          \n",
       "1   Queen Betsy         [4.5]                          \n",
       "2                                                      \n",
       "\n",
       "                                             summary  \\\n",
       "0  Aglirta is known as the Kingless Land. Once a ...   \n",
       "1  \"A Fiend in Need\" reveals the true identity of...   \n",
       "2  ****Zoe Archer’s BLADES OF THE ROSE series—fea...   \n",
       "\n",
       "                                                tags  timestamp  \\\n",
       "0                              fantasy, dragon, epic 2019-06-11   \n",
       "1  short story, humorous, paranormal, romance, sh... 2019-06-11   \n",
       "2  fiction, historical, romance, stempunk, suspen... 2019-01-23   \n",
       "\n",
       "                                         identifiers   pages pubdate  \\\n",
       "0             isbn:9780765341440,google:mhgx03oeVJUC   397.0    2003   \n",
       "1  amazon:B000PDYVUC,isbn:9780425207956,google:QF...    59.0    2006   \n",
       "2  isbn:9781420122954,google:zOZIM73fXIwC,mobi-as...  1912.0    2010   \n",
       "\n",
       "   publisher  \n",
       "0  Macmillan  \n",
       "1    Berkley  \n",
       "2      Zebra  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ebooks.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "742781c2-b745-43d6-bb1f-1473e13baea1",
   "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>title</th>\n",
       "      <th>authors</th>\n",
       "      <th>series</th>\n",
       "      <th>series_number</th>\n",
       "      <th>series2</th>\n",
       "      <th>series2_number</th>\n",
       "      <th>summary</th>\n",
       "      <th>tags</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>identifiers</th>\n",
       "      <th>pages</th>\n",
       "      <th>pubdate</th>\n",
       "      <th>publisher</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Rebel</td>\n",
       "      <td>Zoë Archer</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>On the Canadian frontier in 1875, nature is a ...</td>\n",
       "      <td>paranormal, romance, historical</td>\n",
       "      <td>2019-06-11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>465.0</td>\n",
       "      <td>2010</td>\n",
       "      <td>Zebra</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Scoundrel</td>\n",
       "      <td>Zoë Archer</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>The Blades of the Rose are sworn to protect th...</td>\n",
       "      <td>paranormal romance</td>\n",
       "      <td>2019-06-11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>440.0</td>\n",
       "      <td>2010</td>\n",
       "      <td>Zebra</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110</th>\n",
       "      <td>The Castle of Wolfenbach</td>\n",
       "      <td>Unknown</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2019-06-11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>251.0</td>\n",
       "      <td>0100</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>189</th>\n",
       "      <td>Chase of a Lifetime</td>\n",
       "      <td>Unknown</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2019-06-11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>210.0</td>\n",
       "      <td>0100</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>205</th>\n",
       "      <td>Christmas With My Grinch</td>\n",
       "      <td>B. L. Morticia</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Bahambug! Nathan Ellerby hates Christmas! His ...</td>\n",
       "      <td>mm, glbt, gay erotica, mm romance, gay romance...</td>\n",
       "      <td>2019-06-11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>26.0</td>\n",
       "      <td>0100</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        title         authors series series_number series2  \\\n",
       "3                       Rebel      Zoë Archer                                \n",
       "5                   Scoundrel      Zoë Archer                                \n",
       "110  The Castle of Wolfenbach         Unknown                                \n",
       "189       Chase of a Lifetime         Unknown                                \n",
       "205  Christmas With My Grinch  B. L. Morticia                                \n",
       "\n",
       "    series2_number                                            summary  \\\n",
       "3                   On the Canadian frontier in 1875, nature is a ...   \n",
       "5                   The Blades of the Rose are sworn to protect th...   \n",
       "110                                                               NaN   \n",
       "189                                                               NaN   \n",
       "205                 Bahambug! Nathan Ellerby hates Christmas! His ...   \n",
       "\n",
       "                                                  tags  timestamp identifiers  \\\n",
       "3                      paranormal, romance, historical 2019-06-11         NaN   \n",
       "5                                   paranormal romance 2019-06-11         NaN   \n",
       "110                                                NaN 2019-06-11         NaN   \n",
       "189                                                NaN 2019-06-11         NaN   \n",
       "205  mm, glbt, gay erotica, mm romance, gay romance... 2019-06-11         NaN   \n",
       "\n",
       "     pages pubdate publisher  \n",
       "3    465.0    2010     Zebra  \n",
       "5    440.0    2010     Zebra  \n",
       "110  251.0    0100       NaN  \n",
       "189  210.0    0100       NaN  \n",
       "205   26.0    0100       NaN  "
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test = ebooks[ebooks['identifiers'].isnull()].head(5)\n",
    "test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "9a41de75-3b93-4b78-a246-e0f530a1c792",
   "metadata": {},
   "outputs": [],
   "source": [
    "test['words'] = test['title'] +\" \" + test['authors']\n",
    "\n",
    "test['isbn'] = test['words'].apply(isbn_from_words)\n",
    "\n",
    "test['identifiers'] = 'isbn:' + test['isbn'].astype(str)\n",
    "\n",
    "test = test.drop('words', axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "fe7dd84d-7233-4492-b229-c425473c0912",
   "metadata": {},
   "outputs": [],
   "source": [
    "ebooks.iloc[205, 1] = 'B. L. Morticia'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "184fb506",
   "metadata": {},
   "outputs": [],
   "source": [
    "ebooks.to_csv(r'clean_data\\ebooks.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fa6e3ba7-d024-4d1d-a569-65668d9ba069",
   "metadata": {},
   "source": [
    "There are still some issues with this data, such as unknown authors or tags not being consistent. Those will be easier to fix in Calibre, so I'll leave them. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2dae2b5d",
   "metadata": {},
   "source": [
    "## My Audible"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3fd8107c",
   "metadata": {},
   "outputs": [],
   "source": [
    "audible.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cc73f28c",
   "metadata": {},
   "outputs": [],
   "source": [
    "audible.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0b4c2cd1",
   "metadata": {},
   "source": [
    "### Preliminary Thoughts"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3b32acc6",
   "metadata": {},
   "source": [
    "Most of the columns are fine, but I'll need to change the book_numbers to a float (there are books like 2.5), then enclose them all in brackets before sending to Calibre. A few narrators and lengths are missing, so I'll need to look those up and add them. Several tags and categories are missing, but I may be able to whittle those down when I combine this dataframe with ebooks. Then again, it might make things even harder. I'll have to take a look at the ebooks to see. A few of the columns (store page changed, and isbns) have no values, so I'll drop those right away. Here's the plan for the columns:\n",
    "\n",
    "|     | Column                                | Plan                                                                                      |\n",
    "|:--: |:--------------------------------------|:-----------------------------------------------------------------------------------------|\n",
    "| [X] | Added                                 | Drop                                                                                      |\n",
    "| [X] | Title, Title Short                    | Drop (will use Title Short)                                                              |\n",
    "| [X] | Series                                | Keep only series name                                                                    |\n",
    "| [X] | Book Numbers                          | Change to series_number, then add brackets around numbers                                |\n",
    "| [X] | Blurb                                 | Compare to Summary                                                                        |\n",
    "| [X] | Authors                               | Doesn't need changes, but take a look at unique values just in case                      |\n",
    "| [ ] | Narrators                             | Fill in missing values                                                                    |\n",
    "| [X] | Tags                                  | delete any NaN values, see if I can fill missing with Categories                          |\n",
    "| [X] | Categories                            | Can I use this to fill in Tags column - More trouble than it's worth - drop              |\n",
    "| [X] | Parent & Child Category               | Is this the same as the Category column? YES - Drop                                      |\n",
    "| [X] | Length                                | put in any missing information - info won't be found in Calibre                          |\n",
    "| [X] | Progress                              | Drop                                                                                      |\n",
    "| [X] | Release Date                          | convert to timestamp                                                                      |\n",
    "| [X] | Publisher                             | Drop                                                                                      |\n",
    "| [X] | My Rating, Rating & Ratings           | Drop                                                                                      |\n",
    "| [X] | Favorite                              | Drop                                                                                      |\n",
    "| [X] | Format                                | Drop                                                                                      |\n",
    "| [X] | Language                              | Drop                                                                                      |\n",
    "| [X] | Whispersync                           | Drop                                                                                      |\n",
    "| [X] | From Plus Catalog                     | Do I own these or are they only in Plus? I don't own them - Drop                          |\n",
    "| [X] | Unavailable                           | Do I need this? - NOPE - dropped it                                                      |\n",
    "| [X] | Archived & Downloaded                 | Drop                                                                                      |\n",
    "| [X] | Store Page Changed                    | Drop                                                                                      |\n",
    "| [X] | Store Page Missing                    | Do I need this? - Nope - dropped it                                                      |\n",
    "| [X] | ASIN, ISBN, and ISBN13                | Keep only ASIN                                                                            |\n",
    "| [X] | Summary                               | Compare with blurb                                                                        |\n",
    "| [X] | People Also Bought                    | Drop                                                                                      |\n",
    "| [X] | Store Page URL                        | Keep as audible_url and add a column in Calibre for it                                    |\n",
    "| [X] | Sample                                | Drop                                                                                      |\n",
    "| [X] | Web Player                            | Drop                                                                                      |\n",
    "| [X] | Cover                                 | Change to cover_url and add a column in Calibre for it                                    |\n",
    "| [X] | Search in Goodreads                   | Drop if goodreads book id isn't included, else extract id                                |"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "be746659",
   "metadata": {},
   "source": [
    "### Start Cleaning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fe660706",
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert all columns to lower case and replace spaces with _\n",
    "new_cols = [cn.lower().replace(' ', '_') for cn in audible.columns]\n",
    "\n",
    "# assign new column names to dataframe\n",
    "audible.columns = new_cols"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b3c80180",
   "metadata": {},
   "source": [
    "### Drop columns\n",
    "\n",
    "Drop these columns:\n",
    "Added, Title, Progress, Release Date, Publishers, My Rating, Rating, Ratings, Favorite, Format, Language, Whispersync, Archived, Downloaded, Store Page Changed, ISBN 10, ISBN 13, People Also Bought, Sample, Web Player"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f67d77e6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop columns listed in above table by subsetting df\n",
    "audible = audible[['title_short', 'authors', 'series', \n",
    "                   'book_numbers', 'blurb', 'narrators', \n",
    "                   'tags', 'categories', 'parent_category', \n",
    "                   'child_category','length', 'from_plus_catalog', \n",
    "                   'unavailable', 'store_page_missing', 'asin', \n",
    "                   'summary', 'release_date', 'store_page_url', \n",
    "                   'cover', 'search_in_goodreads']]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5f334741",
   "metadata": {},
   "source": [
    "### Change column names\n",
    "\n",
    "These column names will be changed:\n",
    "title_short (title), book_numbers (series_number), store_page_url (audible_url), cover (cover_url)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0bcee8d8",
   "metadata": {},
   "outputs": [],
   "source": [
    "audible.rename(columns={'title_short':'title', \n",
    "                        'book_numbers':'series_number', \n",
    "                        'store_page_url': 'audible_url',\n",
    "                        'release_date': 'timestamp',\n",
    "                        'cover':'cover_url'}\n",
    "               , inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d87c455f",
   "metadata": {},
   "source": [
    "### Examine columns (quick)\n",
    "\n",
    "These just need a quick check before deciding to drop them or keep them:  \n",
    "\n",
    "- categories (keep for now)\n",
    "- parent_category (drop)\n",
    "- child category (drop)\n",
    "- from_plus_catalog (drop rows, then drop column)\n",
    "- unavailable (drop)\n",
    "- store_page_missing (drop)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dfae2ea9",
   "metadata": {},
   "source": [
    "#### categories, parent_category, child_category"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "17fdc2c2",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "cat = audible[['tags', 'categories', 'parent_category', 'child_category']]\n",
    "cat.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d152094e",
   "metadata": {},
   "source": [
    "Since parent & child category columns have the same information as categories, I'll drop those two. Since categories and tags has the same information, I'll keep categories until I narrow everything else down. Then I'll decide if there are enough missing tag values that I can fill with categories to make it worth cleaning up categories and moving them to tags."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "64e444a6",
   "metadata": {},
   "outputs": [],
   "source": [
    "audible.drop(['parent_category', 'child_category'], axis = 1, inplace = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f2394f15",
   "metadata": {},
   "source": [
    "#### from_plus_catalog"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "95c44359",
   "metadata": {},
   "outputs": [],
   "source": [
    "audible['from_plus_catalog'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "580801d9",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "audible[audible['from_plus_catalog'] == True]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b360e38f",
   "metadata": {},
   "source": [
    "These are books that I've borrowed from Plus or Kindle Unlimited. I don't own them, so I'll drop the rows, and then drop the column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7f634406",
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop rows that list Plus/KU books\n",
    "audible.drop(audible[audible['from_plus_catalog'] == True].index, inplace = True)\n",
    "audible.reset_index(inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f7ace6b5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# see how many rows are left after dropping books from Plus/KU\n",
    "audible.shape[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "172ad0c6",
   "metadata": {},
   "source": [
    "There were 29 non-null values in the from_plus_catalog column. 1505-29 = 1476, so it dropped the rows correctly. Now to drop the column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "669e675c",
   "metadata": {},
   "outputs": [],
   "source": [
    "audible.drop('from_plus_catalog', axis = 1, inplace = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c13ddd05",
   "metadata": {},
   "source": [
    "#### what does unavailable mean"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "efaa6f79",
   "metadata": {},
   "outputs": [],
   "source": [
    "audible['unavailable'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "734f1325",
   "metadata": {},
   "source": [
    "It looks like my 1 unavailable book was in the plus catalog. I'll go ahead and drop that column since it's only null now."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dfe1128c",
   "metadata": {},
   "outputs": [],
   "source": [
    "audible.drop('unavailable', axis = 1, inplace = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1e4dc925",
   "metadata": {},
   "source": [
    "#### store_page_missing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4d5028b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "audible['store_page_missing'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fb599302",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "audible[audible['store_page_missing'] == True].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3cfafe0b",
   "metadata": {},
   "source": [
    "I'm not sure what store_page_missing is supposed to mean, but I thought it meant there was no url. There aren't any missing urls, however, so I'll drop store_page_missing."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8f65adb8",
   "metadata": {},
   "outputs": [],
   "source": [
    "audible.drop('store_page_missing', axis = 1, inplace = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "10bef4f8",
   "metadata": {},
   "source": [
    "#### search_in_goodreads"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "81a5d3f5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# check for the goodreads book id in this column\n",
    "# if no id, drop the column\n",
    "audible.iloc[1]['search_in_goodreads']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d33261cc",
   "metadata": {},
   "source": [
    "The search in goodreads column is literally a search in goodreads for the author and title. It doesn't have the book id, so I can drop that column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9cc0f546",
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop columns\n",
    "audible.drop('search_in_goodreads', axis = 1, inplace = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "30d4ae2d",
   "metadata": {},
   "source": [
    "### Columns that are going to take more work\n",
    "\n",
    "The columns need worked on or need more investigating to decide what to do with them:\n",
    "\n",
    " - series (drop (book #) in all rows)\n",
    " - series_number\n",
    " - narrator\n",
    " - tags (change NaN values to blank)\n",
    " - categories (only 9 more than tags, so drop)\n",
    " - blurb\n",
    " - summary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d1526df5",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# let me take a look at what still needs done\n",
    "audible.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "87ad9375",
   "metadata": {},
   "source": [
    "#### categories"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "40e18c5b",
   "metadata": {},
   "source": [
    "I was going to copy the categories into the tags column, but that will only fix 9 of them. Since I'd have to do a bit of work on the categories before copying it, it's easier to drop the categories column and let Calibre fix the tags later."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ef98a69e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop categories column \n",
    "audible.drop('categories', axis = 1, inplace = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3165c02f",
   "metadata": {},
   "source": [
    "#### tags\n",
    "All NaN values need replaced with a blank. There are & symbols between a few words, as well as the comma. I want to get rid of those and convert the words into a list in each cell."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d14208d5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# change Nan to blank\n",
    "audible.tags = audible.tags.fillna('')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c9f46d84",
   "metadata": {},
   "outputs": [],
   "source": [
    "# first change the & to a comma\n",
    "audible.tags = audible.tags.str.replace(' & ', ', ')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c1621a9f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# this will split the words in the tags column at the comma\n",
    "# each cell in the column will be a list of words\n",
    "audible.tags = audible.tags.str.split(', ')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3b6692e6",
   "metadata": {},
   "outputs": [],
   "source": [
    "#take a look at some of the new column values\n",
    "audible.iloc[0:20]['tags']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "72580c54",
   "metadata": {},
   "source": [
    "#### series\n",
    "\n",
    "There are NaN values in series which will need to be replaced with blanks. The non-null entries have the series and then in parenthesis, the book numbers. Since I already have a column with book numbers, I need to get rid of the parenthesis and what's in them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "77981444",
   "metadata": {},
   "outputs": [],
   "source": [
    "audible['series'] = audible['series'].str.replace(r\"\\(.*\\)\",\"\", regex = True)\n",
    "audible.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3fabfb25",
   "metadata": {},
   "source": [
    "#### series_number\n",
    "\n",
    "There should be brackets around every entry. Some entries are a range of numbers, so I'll need to change those, too. I'll change the ranges to 0 so it won't interfere with any actual numbers that aren't ranges."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "27219733-dad3-411b-92c5-f64fd72a9339",
   "metadata": {},
   "outputs": [],
   "source": [
    "audible[['series', 'series_number']] = audible[['series', 'series_number']].fillna('')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "19c3ddd0",
   "metadata": {},
   "outputs": [],
   "source": [
    "audible.loc[audible['series_number'].str.contains('-'), 'series_number'] = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "abe2af80",
   "metadata": {},
   "outputs": [],
   "source": [
    "# then add brackets around numbers\n",
    "audible.loc[(audible['series'] != ''), 'series_number'] = '[' + audible['series_number'].astype(str) + ']'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "332cdd12",
   "metadata": {},
   "outputs": [],
   "source": [
    "# examine columns more closely - I'll group some of them so I can compare them separately\n",
    "blurb = audible[['blurb', 'summary']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8e5aec64",
   "metadata": {},
   "outputs": [],
   "source": [
    "# look at an example for blurb and summary\n",
    "print('Blurb\\n', blurb.loc[0, 'blurb'])\n",
    "print('\\nSummary\\n', blurb.loc[0,'summary'])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b2a3bfa6",
   "metadata": {},
   "source": [
    "Summary is the best description, but has HTML tags. The summary column also has some NaNs, while the blurb columns doesn't. I'll fill the missing summaries from the blurbs and get rid of the HTML in the summaries that I have."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e194374c",
   "metadata": {},
   "outputs": [],
   "source": [
    "audible['summary'].fillna(audible['blurb'], inplace = True)\n",
    "audible = audible.drop('blurb', axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "605716e6-8bf3-4a7f-baaf-5929fee6a33a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# get rid of html code and unicode characters\n",
    "audible['summary'] = audible['summary'].apply(lambda x : re.sub('<.*?>', '', x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a72ec9d7-92be-4155-9135-acf47a7caa52",
   "metadata": {},
   "outputs": [],
   "source": [
    "audible['summary'] = audible['summary'].apply(lambda x : re.sub('\\'', '\\'', x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0ed891da",
   "metadata": {},
   "outputs": [],
   "source": [
    "audible['summary'] = audible['summary'].str.replace('&nbsp;', '')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fea2738b",
   "metadata": {},
   "outputs": [],
   "source": [
    "audible.loc[0, 'summary']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0d6eebd7",
   "metadata": {},
   "outputs": [],
   "source": [
    "audible.loc[20, 'summary']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f3e21730",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5e9e166d",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a6713951",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "76d51d32",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e4551766",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4629d678",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3ed531bc",
   "metadata": {},
   "outputs": [],
   "source": [
    "audible.to_csv(r'clean_data\\audible.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2022032f",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e6761b0a",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8723da38",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2afc9cb4",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "22c84160",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6b42b8c9",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9e310bc3",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0197df6a",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "deb969e4",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "47853b49",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e290351a",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7af8a7e3",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c856d0c5",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "701f2ce5",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a23af0be",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e415c774",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.10.0"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": true,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "279.273px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}