books_rating_project_EDA_Cleaning_1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "d0cbf39d",
   "metadata": {},
   "source": [
    "# EDA-Cleaning: Part I"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "6fb153da",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import sys\n",
    "import csv\n",
    "import pandas as pd\n",
    "\n",
    "from io import StringIO"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b41271c9",
   "metadata": {},
   "source": [
    "- Starting functions to handle pandas.read_csv errors\n",
    "- To be refactored and extended\n",
    "- Possible to eventually create a class/object to extend pandas 'on_bad_lines' error handling?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "925cc5c0",
   "metadata": {},
   "outputs": [],
   "source": [
    "'''Begining development on pandas csv error handling functions'''\n",
    "\n",
    "def pandas_bad_lines_capture(file: str):\n",
    "    ### adapted from http://yilmazturk.info/2019/09/02/pandas-bad-line-reporter/\n",
    "    '''On bad lines capture and save bad lines for later cleaning. Returns data frame with bad lines dropped'''\n",
    "    original_stderr = sys.stderr\n",
    "    temp_stderr = StringIO()\n",
    "    sys.stderr = temp_stderr\n",
    "    \n",
    "    d = pd.read_csv(file, on_bad_lines='warn')\n",
    "    \n",
    "    sys.stderr = original_stderr\n",
    "    e_captured = temp_stderr.getvalue()\n",
    "    \n",
    "    error_out = file + '_bad_lines.txt'\n",
    "    with open(error_out, 'w') as bad_lines:\n",
    "        for line in e_captured.split(r'\\n'):\n",
    "            bad_lines.write(line)\n",
    "            bad_lines.write('\\n')\n",
    "            \n",
    "    print('Pandas encountered errors with ' + file + ', please check ' + error_out\n",
    "          + ' for details')\n",
    "    \n",
    "    return d\n",
    "\n",
    "def get_error_strings(file:str)-> list:\n",
    "    '''Read Error File Lines into a list'''\n",
    "    lst = []\n",
    "    with open(file, 'r') as f:\n",
    "        for line in f:\n",
    "            if 'Skipping' in line:\n",
    "                lst.append(line.strip())\n",
    "    return lst\n",
    "\n",
    "def get_error_lines_dict(lst: list)-> dict:\n",
    "    '''Parse Error Lines into a dict with the correct line number as a key'''\n",
    "    first_message = len('Skipping line ')\n",
    "    dict_ = {}\n",
    "    for line in lst:\n",
    "        error_line_start = line.find('Sk') + first_message\n",
    "        error_line_end = line.find(':')\n",
    "        error_line_num = int(line[error_line_start:error_line_end]) -1\n",
    "        error_message = line[error_line_end + 1:]\n",
    "        dict_[error_line_num] = error_message\n",
    "    return dict_   \n",
    "\n",
    "def get_bad_lines(file: str, e_lines: dict)->tuple[list, list]:\n",
    "    header = None\n",
    "    bad_lines = []\n",
    "    keys = list(e_lines.keys())\n",
    "    \n",
    "    with open(file, newline='', encoding='utf-8') as csvfile:\n",
    "        reader = csv.reader(csvfile, 'unix')\n",
    "        for i, row in enumerate(reader):\n",
    "            if i == 0:\n",
    "                header = row\n",
    "            elif i in keys:\n",
    "                bad_lines.append(row)\n",
    "    return header, bad_lines"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "360849e7",
   "metadata": {},
   "source": [
    "### We'll start by checking the current contents of our project folder..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "f403e787",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['.git',\n",
       " '.ipynb_checkpoints',\n",
       " 'books_rating_project_EDA_1.ipynb',\n",
       " 'books_rating_project_EDA_2.ipynb',\n",
       " 'data',\n",
       " 'README.md']"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "os.listdir()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6a48cfc6",
   "metadata": {},
   "source": [
    "### And our data folder"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "f46de68a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['uncleaned_books.csv',\n",
       " 'uncleaned_books.csv_bad_lines.txt',\n",
       " 'working_books.csv']"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "os.listdir('data')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bbc789d5",
   "metadata": {},
   "source": [
    "### Let's create a couple of variables to make dealing with filepaths a bit easier"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "0a9f8538",
   "metadata": {},
   "outputs": [],
   "source": [
    "folder = 'data/'\n",
    "ratings_csv = 'uncleaned_books.csv'\n",
    "ratings_path = folder + ratings_csv"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "36a9cf09",
   "metadata": {},
   "source": [
    "### First attempt at reading the csv into pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "dd153887",
   "metadata": {},
   "outputs": [
    {
     "ename": "ParserError",
     "evalue": "Error tokenizing data. C error: Expected 12 fields in line 3350, saw 13\n",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mParserError\u001b[0m                               Traceback (most recent call last)",
      "\u001b[1;32m~\\AppData\\Local\\Temp/ipykernel_28344/222061835.py\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mdf_books_ratings\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mread_csv\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mratings_path\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;32mC:\\Anaconda3\\lib\\site-packages\\pandas\\util\\_decorators.py\u001b[0m in \u001b[0;36mwrapper\u001b[1;34m(*args, **kwargs)\u001b[0m\n\u001b[0;32m    309\u001b[0m                     \u001b[0mstacklevel\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mstacklevel\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    310\u001b[0m                 )\n\u001b[1;32m--> 311\u001b[1;33m             \u001b[1;32mreturn\u001b[0m \u001b[0mfunc\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m*\u001b[0m\u001b[0margs\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    312\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    313\u001b[0m         \u001b[1;32mreturn\u001b[0m \u001b[0mwrapper\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\Anaconda3\\lib\\site-packages\\pandas\\io\\parsers\\readers.py\u001b[0m in \u001b[0;36mread_csv\u001b[1;34m(filepath_or_buffer, sep, delimiter, header, names, index_col, usecols, squeeze, prefix, mangle_dupe_cols, dtype, engine, converters, true_values, false_values, skipinitialspace, skiprows, skipfooter, nrows, na_values, keep_default_na, na_filter, verbose, skip_blank_lines, parse_dates, infer_datetime_format, keep_date_col, date_parser, dayfirst, cache_dates, iterator, chunksize, compression, thousands, decimal, lineterminator, quotechar, quoting, doublequote, escapechar, comment, encoding, encoding_errors, dialect, error_bad_lines, warn_bad_lines, on_bad_lines, delim_whitespace, low_memory, memory_map, float_precision, storage_options)\u001b[0m\n\u001b[0;32m    676\u001b[0m     \u001b[0mkwds\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mupdate\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mkwds_defaults\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    677\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 678\u001b[1;33m     \u001b[1;32mreturn\u001b[0m \u001b[0m_read\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mfilepath_or_buffer\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mkwds\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    679\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    680\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\Anaconda3\\lib\\site-packages\\pandas\\io\\parsers\\readers.py\u001b[0m in \u001b[0;36m_read\u001b[1;34m(filepath_or_buffer, kwds)\u001b[0m\n\u001b[0;32m    579\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    580\u001b[0m     \u001b[1;32mwith\u001b[0m \u001b[0mparser\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 581\u001b[1;33m         \u001b[1;32mreturn\u001b[0m \u001b[0mparser\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mread\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mnrows\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    582\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    583\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\Anaconda3\\lib\\site-packages\\pandas\\io\\parsers\\readers.py\u001b[0m in \u001b[0;36mread\u001b[1;34m(self, nrows)\u001b[0m\n\u001b[0;32m   1251\u001b[0m             \u001b[0mnrows\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mvalidate_integer\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"nrows\"\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mnrows\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1252\u001b[0m             \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1253\u001b[1;33m                 \u001b[0mindex\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcolumns\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcol_dict\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_engine\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mread\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mnrows\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1254\u001b[0m             \u001b[1;32mexcept\u001b[0m \u001b[0mException\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1255\u001b[0m                 \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mclose\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\Anaconda3\\lib\\site-packages\\pandas\\io\\parsers\\c_parser_wrapper.py\u001b[0m in \u001b[0;36mread\u001b[1;34m(self, nrows)\u001b[0m\n\u001b[0;32m    223\u001b[0m         \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    224\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mlow_memory\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 225\u001b[1;33m                 \u001b[0mchunks\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_reader\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mread_low_memory\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mnrows\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    226\u001b[0m                 \u001b[1;31m# destructive to chunks\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    227\u001b[0m                 \u001b[0mdata\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0m_concatenate_chunks\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mchunks\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mC:\\Anaconda3\\lib\\site-packages\\pandas\\_libs\\parsers.pyx\u001b[0m in \u001b[0;36mpandas._libs.parsers.TextReader.read_low_memory\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32mC:\\Anaconda3\\lib\\site-packages\\pandas\\_libs\\parsers.pyx\u001b[0m in \u001b[0;36mpandas._libs.parsers.TextReader._read_rows\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32mC:\\Anaconda3\\lib\\site-packages\\pandas\\_libs\\parsers.pyx\u001b[0m in \u001b[0;36mpandas._libs.parsers.TextReader._tokenize_rows\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32mC:\\Anaconda3\\lib\\site-packages\\pandas\\_libs\\parsers.pyx\u001b[0m in \u001b[0;36mpandas._libs.parsers.raise_parser_error\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;31mParserError\u001b[0m: Error tokenizing data. C error: Expected 12 fields in line 3350, saw 13\n"
     ]
    }
   ],
   "source": [
    "df_books_ratings = pd.read_csv(ratings_path)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "75ab850b",
   "metadata": {},
   "source": [
    "### Let's use the first error handling function to open the file for a bit of exploration and record the bad lines for later cleaning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "63955dcb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Pandas encountered errors with data/uncleaned_books.csv, please check data/uncleaned_books.csv_bad_lines.txt for details\n"
     ]
    }
   ],
   "source": [
    "df_books_ratings = pandas_bad_lines_capture(ratings_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "b5fba9c3",
   "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>bookID</th>\n",
       "      <th>title</th>\n",
       "      <th>authors</th>\n",
       "      <th>average_rating</th>\n",
       "      <th>isbn</th>\n",
       "      <th>isbn13</th>\n",
       "      <th>language_code</th>\n",
       "      <th>num_pages</th>\n",
       "      <th>ratings_count</th>\n",
       "      <th>text_reviews_count</th>\n",
       "      <th>publication_date</th>\n",
       "      <th>publisher</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Harry Potter and the Half-Blood Prince (Harry ...</td>\n",
       "      <td>J.K. Rowling/Mary GrandPré</td>\n",
       "      <td>4.57</td>\n",
       "      <td>0439785960</td>\n",
       "      <td>9780439785969</td>\n",
       "      <td>eng</td>\n",
       "      <td>652</td>\n",
       "      <td>2095690</td>\n",
       "      <td>27591</td>\n",
       "      <td>9/16/2006</td>\n",
       "      <td>Scholastic Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Harry Potter and the Order of the Phoenix (Har...</td>\n",
       "      <td>J.K. Rowling/Mary GrandPré</td>\n",
       "      <td>4.49</td>\n",
       "      <td>0439358078</td>\n",
       "      <td>9780439358071</td>\n",
       "      <td>eng</td>\n",
       "      <td>870</td>\n",
       "      <td>2153167</td>\n",
       "      <td>29221</td>\n",
       "      <td>9/1/2004</td>\n",
       "      <td>Scholastic Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>Harry Potter and the Chamber of Secrets (Harry...</td>\n",
       "      <td>J.K. Rowling</td>\n",
       "      <td>4.42</td>\n",
       "      <td>0439554896</td>\n",
       "      <td>9780439554893</td>\n",
       "      <td>eng</td>\n",
       "      <td>352</td>\n",
       "      <td>6333</td>\n",
       "      <td>244</td>\n",
       "      <td>11/1/2003</td>\n",
       "      <td>Scholastic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>Harry Potter and the Prisoner of Azkaban (Harr...</td>\n",
       "      <td>J.K. Rowling/Mary GrandPré</td>\n",
       "      <td>4.56</td>\n",
       "      <td>043965548X</td>\n",
       "      <td>9780439655484</td>\n",
       "      <td>eng</td>\n",
       "      <td>435</td>\n",
       "      <td>2339585</td>\n",
       "      <td>36325</td>\n",
       "      <td>5/1/2004</td>\n",
       "      <td>Scholastic Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8</td>\n",
       "      <td>Harry Potter Boxed Set  Books 1-5 (Harry Potte...</td>\n",
       "      <td>J.K. Rowling/Mary GrandPré</td>\n",
       "      <td>4.78</td>\n",
       "      <td>0439682584</td>\n",
       "      <td>9780439682589</td>\n",
       "      <td>eng</td>\n",
       "      <td>2690</td>\n",
       "      <td>41428</td>\n",
       "      <td>164</td>\n",
       "      <td>9/13/2004</td>\n",
       "      <td>Scholastic</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   bookID                                              title  \\\n",
       "0       1  Harry Potter and the Half-Blood Prince (Harry ...   \n",
       "1       2  Harry Potter and the Order of the Phoenix (Har...   \n",
       "2       4  Harry Potter and the Chamber of Secrets (Harry...   \n",
       "3       5  Harry Potter and the Prisoner of Azkaban (Harr...   \n",
       "4       8  Harry Potter Boxed Set  Books 1-5 (Harry Potte...   \n",
       "\n",
       "                      authors  average_rating        isbn         isbn13  \\\n",
       "0  J.K. Rowling/Mary GrandPré            4.57  0439785960  9780439785969   \n",
       "1  J.K. Rowling/Mary GrandPré            4.49  0439358078  9780439358071   \n",
       "2                J.K. Rowling            4.42  0439554896  9780439554893   \n",
       "3  J.K. Rowling/Mary GrandPré            4.56  043965548X  9780439655484   \n",
       "4  J.K. Rowling/Mary GrandPré            4.78  0439682584  9780439682589   \n",
       "\n",
       "  language_code    num_pages  ratings_count  text_reviews_count  \\\n",
       "0           eng          652        2095690               27591   \n",
       "1           eng          870        2153167               29221   \n",
       "2           eng          352           6333                 244   \n",
       "3           eng          435        2339585               36325   \n",
       "4           eng         2690          41428                 164   \n",
       "\n",
       "  publication_date        publisher  \n",
       "0        9/16/2006  Scholastic Inc.  \n",
       "1         9/1/2004  Scholastic Inc.  \n",
       "2        11/1/2003       Scholastic  \n",
       "3         5/1/2004  Scholastic Inc.  \n",
       "4        9/13/2004       Scholastic  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_books_ratings.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aa96549d",
   "metadata": {},
   "source": [
    "* interesting to note that books with multiple authors seem to be seperated by a '/' "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "4ca028c5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(11123, 12)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_books_ratings.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "796a056f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 11123 entries, 0 to 11122\n",
      "Data columns (total 12 columns):\n",
      " #   Column              Non-Null Count  Dtype  \n",
      "---  ------              --------------  -----  \n",
      " 0   bookID              11123 non-null  int64  \n",
      " 1   title               11123 non-null  object \n",
      " 2   authors             11123 non-null  object \n",
      " 3   average_rating      11123 non-null  float64\n",
      " 4   isbn                11123 non-null  object \n",
      " 5   isbn13              11123 non-null  int64  \n",
      " 6   language_code       11123 non-null  object \n",
      " 7     num_pages         11123 non-null  int64  \n",
      " 8   ratings_count       11123 non-null  int64  \n",
      " 9   text_reviews_count  11123 non-null  int64  \n",
      " 10  publication_date    11123 non-null  object \n",
      " 11  publisher           11123 non-null  object \n",
      "dtypes: float64(1), int64(5), object(6)\n",
      "memory usage: 1.0+ MB\n"
     ]
    }
   ],
   "source": [
    "df_books_ratings.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cd2a8847",
   "metadata": {},
   "source": [
    "### Preliminarily it looks like our remaining data is clean. Let's circle back and deal with our bad lines before proceeding with exploration and further cleaning"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "03c1fe1e",
   "metadata": {},
   "source": [
    "### Let's see how many lines caused trouble during the initial load of the csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "0148b3ba",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['uncleaned_books.csv',\n",
       " 'uncleaned_books.csv_bad_lines.txt',\n",
       " 'working_books.csv']"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "os.listdir('data')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "e1238e19",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'data/uncleaned_books.csv_bad_lines.txt'"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "errors_file = 'uncleaned_books.csv_bad_lines.txt'\n",
    "errors_path = folder + errors_file\n",
    "errors_path"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "e263ac94",
   "metadata": {},
   "outputs": [],
   "source": [
    "with open(errors_path, 'r') as errors:\n",
    "    count = 0\n",
    "    for line in errors.readlines():\n",
    "        count += 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "0378988b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "count"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1e34da0c",
   "metadata": {},
   "source": [
    "### With 6 errors at most, we'll look at the errors inline before processing further"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "655fb05d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "b'Skipping line 3350: expected 12 fields, saw 13\n",
      "\n",
      "Skipping line 4704: expected 12 fields, saw 13\n",
      "\n",
      "Skipping line 5879: expected 12 fields, saw 13\n",
      "\n",
      "Skipping line 8981: expected 12 fields, saw 13\n",
      "\n",
      "'\n",
      "\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "with open(errors_path, 'r') as errors:\n",
    "    for line in errors.readlines():\n",
    "        print(line)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b3249b32",
   "metadata": {},
   "source": [
    "### Process the errors text file to make it easier to pull the bad lines out of the original csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "80d8350b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[\"b'Skipping line 3350: expected 12 fields, saw 13\",\n",
       " 'Skipping line 4704: expected 12 fields, saw 13',\n",
       " 'Skipping line 5879: expected 12 fields, saw 13',\n",
       " 'Skipping line 8981: expected 12 fields, saw 13']"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lst_errors = get_error_strings(errors_path)\n",
    "lst_errors"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "ac7bb831",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{3349: ' expected 12 fields, saw 13',\n",
       " 4703: ' expected 12 fields, saw 13',\n",
       " 5878: ' expected 12 fields, saw 13',\n",
       " 8980: ' expected 12 fields, saw 13'}"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dict_errors = get_error_lines_dict(lst_errors)\n",
    "dict_errors"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "470bddc5",
   "metadata": {},
   "source": [
    "### And finally retrieve the bad lines from the original file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "25a1bc7c",
   "metadata": {},
   "outputs": [],
   "source": [
    "lst_header, lst_bad_lines = get_bad_lines(ratings_path, dict_errors)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "f751a791",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['bookID',\n",
       " 'title',\n",
       " 'authors',\n",
       " 'average_rating',\n",
       " 'isbn',\n",
       " 'isbn13',\n",
       " 'language_code',\n",
       " '  num_pages',\n",
       " 'ratings_count',\n",
       " 'text_reviews_count',\n",
       " 'publication_date',\n",
       " 'publisher']"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lst_header"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "4e7a5d70",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[['12224',\n",
       "  'Streetcar Suburbs: The Process of Growth in Boston  1870-1900',\n",
       "  'Sam Bass Warner',\n",
       "  ' Jr./Sam B. Warner',\n",
       "  '3.58',\n",
       "  '0674842111',\n",
       "  '9780674842113',\n",
       "  'en-US',\n",
       "  '236',\n",
       "  '61',\n",
       "  '6',\n",
       "  '4/20/2004',\n",
       "  'Harvard University Press'],\n",
       " ['16914',\n",
       "  \"The Tolkien Fan's Medieval Reader\",\n",
       "  'David E. Smith (Turgon of TheOneRing.net',\n",
       "  ' one of the founding members of this Tolkien website)/Verlyn Flieger/Turgon (=David E. Smith)',\n",
       "  '3.58',\n",
       "  '1593600119',\n",
       "  '9781593600112',\n",
       "  'eng',\n",
       "  '400',\n",
       "  '26',\n",
       "  '4',\n",
       "  '4/6/2004',\n",
       "  'Cold Spring Press'],\n",
       " ['22128',\n",
       "  'Patriots (The Coming Collapse)',\n",
       "  'James Wesley',\n",
       "  ' Rawles',\n",
       "  '3.63',\n",
       "  '156384155X',\n",
       "  '9781563841552',\n",
       "  'eng',\n",
       "  '342',\n",
       "  '38',\n",
       "  '4',\n",
       "  '1/15/1999',\n",
       "  'Huntington House Publishers'],\n",
       " ['34889',\n",
       "  \"Brown's Star Atlas: Showing All The Bright Stars With Full Instructions How To Find And Use Them For Navigational Purposes And Department Of Trade Examinations.\",\n",
       "  'Brown',\n",
       "  ' Son & Ferguson',\n",
       "  '0.00',\n",
       "  '0851742718',\n",
       "  '9780851742717',\n",
       "  'eng',\n",
       "  '49',\n",
       "  '0',\n",
       "  '0',\n",
       "  '5/1/1977',\n",
       "  'Brown Son & Ferguson Ltd.']]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lst_bad_lines"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "63d1a74b",
   "metadata": {},
   "source": [
    "### A cursory review of the bad lines looks like the authors were split across two lines"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "0d6bf1c2",
   "metadata": {},
   "outputs": [],
   "source": [
    "lst_aut = []\n",
    "for lst in lst_bad_lines:\n",
    "    aut = lst[2] + lst[3]\n",
    "    lst_aut.append(aut)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "3b7a4ccb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Sam Bass Warner Jr./Sam B. Warner',\n",
       " 'David E. Smith (Turgon of TheOneRing.net one of the founding members of this Tolkien website)/Verlyn Flieger/Turgon (=David E. Smith)',\n",
       " 'James Wesley Rawles',\n",
       " 'Brown Son & Ferguson']"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lst_aut"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "79b7bff3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Brown', 'Son', '&', 'Ferguson']"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temp = lst_aut[3].split()\n",
    "temp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "049ad1f7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Brown', 'Son', 'Ferguson']"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temp = [elm for elm in temp if elm != '&']\n",
    "temp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "b38ed61e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Brown/Son/Ferguson'"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temp = '/'.join(temp)\n",
    "temp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "1702025e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Sam Bass Warner Jr./Sam B. Warner',\n",
       " 'David E. Smith (Turgon of TheOneRing.net one of the founding members of this Tolkien website)/Verlyn Flieger/Turgon (=David E. Smith)',\n",
       " 'James Wesley Rawles',\n",
       " 'Brown/Son/Ferguson']"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lst_aut.remove(lst_aut[3])\n",
    "lst_aut.append(temp)\n",
    "lst_aut"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "1dc69e4a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[['12224',\n",
       "  'Streetcar Suburbs: The Process of Growth in Boston  1870-1900',\n",
       "  'Sam Bass Warner Jr./Sam B. Warner',\n",
       "  '3.58',\n",
       "  '0674842111',\n",
       "  '9780674842113',\n",
       "  'en-US',\n",
       "  '236',\n",
       "  '61',\n",
       "  '6',\n",
       "  '4/20/2004',\n",
       "  'Harvard University Press'],\n",
       " ['16914',\n",
       "  \"The Tolkien Fan's Medieval Reader\",\n",
       "  'David E. Smith (Turgon of TheOneRing.net one of the founding members of this Tolkien website)/Verlyn Flieger/Turgon (=David E. Smith)',\n",
       "  '3.58',\n",
       "  '1593600119',\n",
       "  '9781593600112',\n",
       "  'eng',\n",
       "  '400',\n",
       "  '26',\n",
       "  '4',\n",
       "  '4/6/2004',\n",
       "  'Cold Spring Press'],\n",
       " ['22128',\n",
       "  'Patriots (The Coming Collapse)',\n",
       "  'James Wesley Rawles',\n",
       "  '3.63',\n",
       "  '156384155X',\n",
       "  '9781563841552',\n",
       "  'eng',\n",
       "  '342',\n",
       "  '38',\n",
       "  '4',\n",
       "  '1/15/1999',\n",
       "  'Huntington House Publishers'],\n",
       " ['34889',\n",
       "  \"Brown's Star Atlas: Showing All The Bright Stars With Full Instructions How To Find And Use Them For Navigational Purposes And Department Of Trade Examinations.\",\n",
       "  'Brown/Son/Ferguson',\n",
       "  '0.00',\n",
       "  '0851742718',\n",
       "  '9780851742717',\n",
       "  'eng',\n",
       "  '49',\n",
       "  '0',\n",
       "  '0',\n",
       "  '5/1/1977',\n",
       "  'Brown Son & Ferguson Ltd.']]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "for i, lst in enumerate(lst_bad_lines):\n",
    "    lst.remove(lst[2])\n",
    "    lst.remove(lst[2])\n",
    "    lst.insert(2, lst_aut[i])\n",
    "\n",
    "lst_bad_lines"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "8ab5b995",
   "metadata": {},
   "outputs": [],
   "source": [
    "fixed_lines_dict = {}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "10e186e0",
   "metadata": {},
   "outputs": [],
   "source": [
    "for elm in lst_header:\n",
    "    fixed_lines_dict[elm]= []"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "17a660d8",
   "metadata": {},
   "outputs": [],
   "source": [
    "for lst in lst_bad_lines:\n",
    "    for i, elm in enumerate(lst):\n",
    "        key = lst_header[i]\n",
    "        fixed_lines_dict[key].append(elm)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "7a7d0b41",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'bookID': ['12224', '16914', '22128', '34889'],\n",
       " 'title': ['Streetcar Suburbs: The Process of Growth in Boston  1870-1900',\n",
       "  \"The Tolkien Fan's Medieval Reader\",\n",
       "  'Patriots (The Coming Collapse)',\n",
       "  \"Brown's Star Atlas: Showing All The Bright Stars With Full Instructions How To Find And Use Them For Navigational Purposes And Department Of Trade Examinations.\"],\n",
       " 'authors': ['Sam Bass Warner Jr./Sam B. Warner',\n",
       "  'David E. Smith (Turgon of TheOneRing.net one of the founding members of this Tolkien website)/Verlyn Flieger/Turgon (=David E. Smith)',\n",
       "  'James Wesley Rawles',\n",
       "  'Brown/Son/Ferguson'],\n",
       " 'average_rating': ['3.58', '3.58', '3.63', '0.00'],\n",
       " 'isbn': ['0674842111', '1593600119', '156384155X', '0851742718'],\n",
       " 'isbn13': ['9780674842113',\n",
       "  '9781593600112',\n",
       "  '9781563841552',\n",
       "  '9780851742717'],\n",
       " 'language_code': ['en-US', 'eng', 'eng', 'eng'],\n",
       " '  num_pages': ['236', '400', '342', '49'],\n",
       " 'ratings_count': ['61', '26', '38', '0'],\n",
       " 'text_reviews_count': ['6', '4', '4', '0'],\n",
       " 'publication_date': ['4/20/2004', '4/6/2004', '1/15/1999', '5/1/1977'],\n",
       " 'publisher': ['Harvard University Press',\n",
       "  'Cold Spring Press',\n",
       "  'Huntington House Publishers',\n",
       "  'Brown Son & Ferguson Ltd.']}"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fixed_lines_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "790d1b82",
   "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>bookID</th>\n",
       "      <th>title</th>\n",
       "      <th>authors</th>\n",
       "      <th>average_rating</th>\n",
       "      <th>isbn</th>\n",
       "      <th>isbn13</th>\n",
       "      <th>language_code</th>\n",
       "      <th>num_pages</th>\n",
       "      <th>ratings_count</th>\n",
       "      <th>text_reviews_count</th>\n",
       "      <th>publication_date</th>\n",
       "      <th>publisher</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>12224</td>\n",
       "      <td>Streetcar Suburbs: The Process of Growth in Bo...</td>\n",
       "      <td>Sam Bass Warner Jr./Sam B. Warner</td>\n",
       "      <td>3.58</td>\n",
       "      <td>0674842111</td>\n",
       "      <td>9780674842113</td>\n",
       "      <td>en-US</td>\n",
       "      <td>236</td>\n",
       "      <td>61</td>\n",
       "      <td>6</td>\n",
       "      <td>4/20/2004</td>\n",
       "      <td>Harvard University Press</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>16914</td>\n",
       "      <td>The Tolkien Fan's Medieval Reader</td>\n",
       "      <td>David E. Smith (Turgon of TheOneRing.net one o...</td>\n",
       "      <td>3.58</td>\n",
       "      <td>1593600119</td>\n",
       "      <td>9781593600112</td>\n",
       "      <td>eng</td>\n",
       "      <td>400</td>\n",
       "      <td>26</td>\n",
       "      <td>4</td>\n",
       "      <td>4/6/2004</td>\n",
       "      <td>Cold Spring Press</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>22128</td>\n",
       "      <td>Patriots (The Coming Collapse)</td>\n",
       "      <td>James Wesley Rawles</td>\n",
       "      <td>3.63</td>\n",
       "      <td>156384155X</td>\n",
       "      <td>9781563841552</td>\n",
       "      <td>eng</td>\n",
       "      <td>342</td>\n",
       "      <td>38</td>\n",
       "      <td>4</td>\n",
       "      <td>1/15/1999</td>\n",
       "      <td>Huntington House Publishers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>34889</td>\n",
       "      <td>Brown's Star Atlas: Showing All The Bright Sta...</td>\n",
       "      <td>Brown/Son/Ferguson</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0851742718</td>\n",
       "      <td>9780851742717</td>\n",
       "      <td>eng</td>\n",
       "      <td>49</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5/1/1977</td>\n",
       "      <td>Brown Son &amp; Ferguson Ltd.</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  bookID                                              title  \\\n",
       "0  12224  Streetcar Suburbs: The Process of Growth in Bo...   \n",
       "1  16914                  The Tolkien Fan's Medieval Reader   \n",
       "2  22128                     Patriots (The Coming Collapse)   \n",
       "3  34889  Brown's Star Atlas: Showing All The Bright Sta...   \n",
       "\n",
       "                                             authors average_rating  \\\n",
       "0                  Sam Bass Warner Jr./Sam B. Warner           3.58   \n",
       "1  David E. Smith (Turgon of TheOneRing.net one o...           3.58   \n",
       "2                                James Wesley Rawles           3.63   \n",
       "3                                 Brown/Son/Ferguson           0.00   \n",
       "\n",
       "         isbn         isbn13 language_code   num_pages ratings_count  \\\n",
       "0  0674842111  9780674842113         en-US         236            61   \n",
       "1  1593600119  9781593600112           eng         400            26   \n",
       "2  156384155X  9781563841552           eng         342            38   \n",
       "3  0851742718  9780851742717           eng          49             0   \n",
       "\n",
       "  text_reviews_count publication_date                    publisher  \n",
       "0                  6        4/20/2004     Harvard University Press  \n",
       "1                  4         4/6/2004            Cold Spring Press  \n",
       "2                  4        1/15/1999  Huntington House Publishers  \n",
       "3                  0         5/1/1977    Brown Son & Ferguson Ltd.  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_fixed_lines = pd.DataFrame(fixed_lines_dict)\n",
    "df_fixed_lines.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "161bc94f",
   "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>bookID</th>\n",
       "      <th>title</th>\n",
       "      <th>authors</th>\n",
       "      <th>average_rating</th>\n",
       "      <th>isbn</th>\n",
       "      <th>isbn13</th>\n",
       "      <th>language_code</th>\n",
       "      <th>num_pages</th>\n",
       "      <th>ratings_count</th>\n",
       "      <th>text_reviews_count</th>\n",
       "      <th>publication_date</th>\n",
       "      <th>publisher</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Harry Potter and the Half-Blood Prince (Harry ...</td>\n",
       "      <td>J.K. Rowling/Mary GrandPré</td>\n",
       "      <td>4.57</td>\n",
       "      <td>0439785960</td>\n",
       "      <td>9780439785969</td>\n",
       "      <td>eng</td>\n",
       "      <td>652</td>\n",
       "      <td>2095690</td>\n",
       "      <td>27591</td>\n",
       "      <td>9/16/2006</td>\n",
       "      <td>Scholastic Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Harry Potter and the Order of the Phoenix (Har...</td>\n",
       "      <td>J.K. Rowling/Mary GrandPré</td>\n",
       "      <td>4.49</td>\n",
       "      <td>0439358078</td>\n",
       "      <td>9780439358071</td>\n",
       "      <td>eng</td>\n",
       "      <td>870</td>\n",
       "      <td>2153167</td>\n",
       "      <td>29221</td>\n",
       "      <td>9/1/2004</td>\n",
       "      <td>Scholastic Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>Harry Potter and the Chamber of Secrets (Harry...</td>\n",
       "      <td>J.K. Rowling</td>\n",
       "      <td>4.42</td>\n",
       "      <td>0439554896</td>\n",
       "      <td>9780439554893</td>\n",
       "      <td>eng</td>\n",
       "      <td>352</td>\n",
       "      <td>6333</td>\n",
       "      <td>244</td>\n",
       "      <td>11/1/2003</td>\n",
       "      <td>Scholastic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>Harry Potter and the Prisoner of Azkaban (Harr...</td>\n",
       "      <td>J.K. Rowling/Mary GrandPré</td>\n",
       "      <td>4.56</td>\n",
       "      <td>043965548X</td>\n",
       "      <td>9780439655484</td>\n",
       "      <td>eng</td>\n",
       "      <td>435</td>\n",
       "      <td>2339585</td>\n",
       "      <td>36325</td>\n",
       "      <td>5/1/2004</td>\n",
       "      <td>Scholastic Inc.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8</td>\n",
       "      <td>Harry Potter Boxed Set  Books 1-5 (Harry Potte...</td>\n",
       "      <td>J.K. Rowling/Mary GrandPré</td>\n",
       "      <td>4.78</td>\n",
       "      <td>0439682584</td>\n",
       "      <td>9780439682589</td>\n",
       "      <td>eng</td>\n",
       "      <td>2690</td>\n",
       "      <td>41428</td>\n",
       "      <td>164</td>\n",
       "      <td>9/13/2004</td>\n",
       "      <td>Scholastic</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  bookID                                              title  \\\n",
       "0      1  Harry Potter and the Half-Blood Prince (Harry ...   \n",
       "1      2  Harry Potter and the Order of the Phoenix (Har...   \n",
       "2      4  Harry Potter and the Chamber of Secrets (Harry...   \n",
       "3      5  Harry Potter and the Prisoner of Azkaban (Harr...   \n",
       "4      8  Harry Potter Boxed Set  Books 1-5 (Harry Potte...   \n",
       "\n",
       "                      authors average_rating        isbn         isbn13  \\\n",
       "0  J.K. Rowling/Mary GrandPré           4.57  0439785960  9780439785969   \n",
       "1  J.K. Rowling/Mary GrandPré           4.49  0439358078  9780439358071   \n",
       "2                J.K. Rowling           4.42  0439554896  9780439554893   \n",
       "3  J.K. Rowling/Mary GrandPré           4.56  043965548X  9780439655484   \n",
       "4  J.K. Rowling/Mary GrandPré           4.78  0439682584  9780439682589   \n",
       "\n",
       "  language_code   num_pages ratings_count text_reviews_count publication_date  \\\n",
       "0           eng         652       2095690              27591        9/16/2006   \n",
       "1           eng         870       2153167              29221         9/1/2004   \n",
       "2           eng         352          6333                244        11/1/2003   \n",
       "3           eng         435       2339585              36325         5/1/2004   \n",
       "4           eng        2690         41428                164        9/13/2004   \n",
       "\n",
       "         publisher  \n",
       "0  Scholastic Inc.  \n",
       "1  Scholastic Inc.  \n",
       "2       Scholastic  \n",
       "3  Scholastic Inc.  \n",
       "4       Scholastic  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_new = pd.concat([df_books_ratings, df_fixed_lines])\n",
    "df_new.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "36b15d29",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(11127, 12)"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_new.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "f1d442aa",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 11127 entries, 0 to 3\n",
      "Data columns (total 12 columns):\n",
      " #   Column              Non-Null Count  Dtype \n",
      "---  ------              --------------  ----- \n",
      " 0   bookID              11127 non-null  object\n",
      " 1   title               11127 non-null  object\n",
      " 2   authors             11127 non-null  object\n",
      " 3   average_rating      11127 non-null  object\n",
      " 4   isbn                11127 non-null  object\n",
      " 5   isbn13              11127 non-null  object\n",
      " 6   language_code       11127 non-null  object\n",
      " 7     num_pages         11127 non-null  object\n",
      " 8   ratings_count       11127 non-null  object\n",
      " 9   text_reviews_count  11127 non-null  object\n",
      " 10  publication_date    11127 non-null  object\n",
      " 11  publisher           11127 non-null  object\n",
      "dtypes: object(12)\n",
      "memory usage: 1.1+ MB\n"
     ]
    }
   ],
   "source": [
    "df_new.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "3a8d70b3",
   "metadata": {},
   "outputs": [],
   "source": [
    "working_books_path = folder + 'working_books.csv'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "7bb81529",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_new.to_csv(working_books_path, encoding='utf-8', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "c4c87640",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['uncleaned_books.csv',\n",
       " 'uncleaned_books.csv_bad_lines.txt',\n",
       " 'working_books.csv']"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "os.listdir('data')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "830e645e",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "194e5e83",
   "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.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}