scraping.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from splinter import Browser\n",
    "from bs4 import BeautifulSoup\n",
    "from webdriver_manager.chrome import ChromeDriverManager\n",
    "import pandas as pd\n",
    "import math  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "====== WebDriver manager ======\n",
      "Current google-chrome version is 91.0.4472\n",
      "Get LATEST driver version for 91.0.4472\n",
      "Get LATEST driver version for 91.0.4472\n",
      "Trying to download new driver from https://chromedriver.storage.googleapis.com/91.0.4472.19/chromedriver_win32.zip\n",
      "Driver has been saved in cache [C:\\Users\\bluec\\.wdm\\drivers\\chromedriver\\win32\\91.0.4472.19]\n"
     ]
    }
   ],
   "source": [
    "# Setup splinter\n",
    "executable_path = {'executable_path': ChromeDriverManager().install()}\n",
    "browser = Browser('chrome', **executable_path, headless=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "#setting up URL for the used car site. We have chosen local used car dealer West Side Auto\n",
    "url = 'https://www.westsideauto.com.au/all-stock/list?q=condition:used'\n",
    "browser.visit(url)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'1,175'"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#initialising test scrape from site\n",
    "html = browser.html\n",
    "soup = BeautifulSoup(html, 'html.parser')\n",
    "stock_list = soup.find_all('h1', class_='stocklist-title')\n",
    "# stock = stock_list.find('span').text\n",
    "stock = stock_list[0].text.split(' ')[0]\n",
    "stock"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1175"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#part of data cleaning to get rid of unwanted symbols and transforming the data type to integer\n",
    "int(stock.replace(',', ''))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "66"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#getting pages of listed cars. Each page contains 18 listings\n",
    "pages = math.ceil(int(stock.replace(',', ''))/18)\n",
    "pages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'9.84'"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#scraping the comparison rate from the dealer site\n",
    "seller_rate = soup.find_all('div',class_='price-per-week')[0]['data-disclaimer'].split('\\n')\n",
    "car_seller_rate=seller_rate[2].split(' ')[-1].split('%')[0]\n",
    "car_seller_rate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "#creating list to store scraped data\n",
    "vehicle = []\n",
    "price = []\n",
    "weekly = []\n",
    "stock_no = []"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "#the following loops through the site and scrape all the required data\n",
    "\n",
    "for x in range(1, 3): # can change '3' to pages to scrape all\n",
    "    \n",
    "    html = browser.html #this needs to be within the loop otherwise it only grabs data from the first page\n",
    "    soup = BeautifulSoup(html, 'html.parser')\n",
    "\n",
    "    cars = soup.find_all('div', class_='vehicle-item')\n",
    " \n",
    "    for i in range(0,len(cars)): #this section has been tailored after inspecting the site's HTML elements\n",
    "        if i%2:\n",
    "            h3 = cars[i].find('h3').text\n",
    "            vehicle.append(h3)\n",
    "            h2 = float(cars[i].find('h2').text.split('$')[1].replace(',', ''))\n",
    "            price.append(h2)\n",
    "            span = cars[i].find('span', class_='price-per-week-figure').text\n",
    "            weekly.append(span)\n",
    "            span1 = cars[i].find('span', class_='stock-no').text.split(' ')[1]\n",
    "            stock_no.append(span1)\n",
    "            \n",
    "    #once all data scraped from 1st page, moving onto the next        \n",
    "    next_page_url = 'https://www.westsideauto.com.au/all-stock/list/page-'+str(x+1)+'?q=condition%3Aused'\n",
    "\n",
    "    browser.links.find_by_href(next_page_url).click()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['$113',\n",
       " '$94',\n",
       " '$97',\n",
       " '$172',\n",
       " '$143',\n",
       " '$168',\n",
       " '$280',\n",
       " '$184',\n",
       " '$264',\n",
       " '$172',\n",
       " '$113',\n",
       " '$173',\n",
       " '$234',\n",
       " '$218',\n",
       " '$280',\n",
       " '$197',\n",
       " '$255',\n",
       " '$205',\n",
       " '$161',\n",
       " '$264',\n",
       " '$84',\n",
       " '$122',\n",
       " '$134',\n",
       " '$117',\n",
       " '$138',\n",
       " '$188',\n",
       " '$155',\n",
       " '$278',\n",
       " '$138',\n",
       " '$339',\n",
       " '$234',\n",
       " '$167',\n",
       " '$172',\n",
       " '$262',\n",
       " '$264',\n",
       " '$389']"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#a preview on the weekly payments, however our project is scoped for Monthly payments\n",
    "weekly"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "36"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len (weekly)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['2016 Audi A3 8V MY17 1.6 TDI Sportback Hatchback',\n",
       " '2016 Audi A3 8V MY16 Sportback 1.6 TDI Attraction Hatchback',\n",
       " '2014 Audi A3 8V MY14 1.8 TFSI Ambition Sedan',\n",
       " '2017 Audi A3 8V MY17 2.0 TFSI S Tronic Sport Cabriolet',\n",
       " '2016 Audi A4 F4 (B9) 2.0 TFSI S Tronic Sport Sedan',\n",
       " '2017 Audi A4 F4 MY17 (B9) 2.0 TFSI S Tronic Sport Sedan',\n",
       " '2018 Audi A5 F5 MY18 2.0 TFSI Quattro S Tronic Sprt Coupe',\n",
       " '2018 Audi Q2 GA MY18 1.4 TFSI Design Wagon',\n",
       " '2020 Audi Q3 F3 MY21 35 TFSI S Line Sportback',\n",
       " '2018 Audi Q3 8U MY18 1.4 TFSI (110kW) Wagon',\n",
       " '2013 Audi Q3 8U 2.0 TFSI Quattro (125kW) Wagon',\n",
       " '2016 Audi Q5 8R MY16 2.0 TDI Quattro Wagon',\n",
       " '2017 Audi Q5 FY MY18 2.0 TFSI Quattro Sport Wagon',\n",
       " '2017 Audi Q5 FY MY17 2.0 TDI Quattro Design Wagon',\n",
       " '2019 Audi Q5 FY MY19 40 TDI Quattro Sport Wagon',\n",
       " '2017 Audi Q5 FY MY17 2.0 TDI Quattro Design Wagon',\n",
       " '2016 Audi Q7 4M 3.0 TDI Quattro Wagon',\n",
       " '2016 Audi S3 8V MY17 Sportback 2.0 TFSI S Tronic Hatchback',\n",
       " '2008 Audi S5 8T 4.2 FSI Quattro Coupe',\n",
       " '2016 Audi TT FV S 2.0 TFSI Quattro Coupe',\n",
       " '2013 BMW 116i F20 Hatchback',\n",
       " '2016 BMW 118i F20 LCI Sport Line Hatchback',\n",
       " '2017 BMW 118i F40 M Sport Hatchback',\n",
       " '2014 BMW 220d F22 Coupe',\n",
       " '2015 BMW 220i F23 Sport Line Convertible',\n",
       " '2017 BMW 330i F30 LCI M Sport Sedan',\n",
       " '2013 BMW 428i F32 Sport Line Coupe',\n",
       " '2017 BMW 530i G30 MY17 Luxury Line Sedan',\n",
       " '2014 BMW M135i F20 MY14 Hatchback',\n",
       " '2016 BMW M4 F82 LCI MY17 Competition Coupe',\n",
       " '2019 BMW X1 F48 MY19 xDrive 25I Wagon',\n",
       " '2015 BMW X3 F25 MY15 xDrive20d Wagon',\n",
       " '2017 BMW X3 F25 MY17 Update xDrive 20I Wagon',\n",
       " '2018 BMW X3 G01 xDrive30I Wagon',\n",
       " '2017 BMW X4 F26 MY16 xDrive 20D Coupe',\n",
       " '2019 BMW X4 G02 M40I Wagon']"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#preview data for quality checks\n",
    "vehicle"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "36"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(vehicle)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[25988.0,\n",
       " 21388.0,\n",
       " 21990.0,\n",
       " 39988.0,\n",
       " 32989.0,\n",
       " 38985.0,\n",
       " 65981.0,\n",
       " 42900.0,\n",
       " 61988.0,\n",
       " 39988.0,\n",
       " 25995.0,\n",
       " 40385.0,\n",
       " 54988.0,\n",
       " 50988.0,\n",
       " 65996.0,\n",
       " 45977.0,\n",
       " 59984.0,\n",
       " 47996.0,\n",
       " 37492.0,\n",
       " 61990.0,\n",
       " 18991.0,\n",
       " 27988.0,\n",
       " 30977.0,\n",
       " 26977.0,\n",
       " 31921.0,\n",
       " 43988.0,\n",
       " 35999.0,\n",
       " 65492.0,\n",
       " 31985.0,\n",
       " 79992.0,\n",
       " 54988.0,\n",
       " 38895.0,\n",
       " 39988.0,\n",
       " 61492.0,\n",
       " 61988.0,\n",
       " 91977.0]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#for quality checks\n",
    "price"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "36"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(price)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "====== WebDriver manager ======\n",
      "Current google-chrome version is 91.0.4472\n",
      "Get LATEST driver version for 91.0.4472\n",
      "Driver [C:\\Users\\bluec\\.wdm\\drivers\\chromedriver\\win32\\91.0.4472.19\\chromedriver.exe] found in cache\n"
     ]
    }
   ],
   "source": [
    "# Setup splinter\n",
    "executable_path = {'executable_path': ChromeDriverManager().install()}\n",
    "browser = Browser('chrome', **executable_path, headless=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "#setting up URL for finder.com.au. We have selected car loans in this case \n",
    "url_finder = 'https://www.finder.com.au/car-loans'\n",
    "browser.visit(url_finder)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "html = browser.html\n",
    "soup = BeautifulSoup(html, 'html.parser')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "#after inspecting the HTML elements\n",
    "institutions = soup.find_all('td',class_ =\"comparison-table__name\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "finance_name =[]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "for ins in institutions:\n",
    "    a = ins.find('a').text\n",
    "    finance_name.append(a)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Plenti Car Loan',\n",
       " 'Loans.com.au - New and Dealer Used Car Loan',\n",
       " 'Credit Concierge Car Loan',\n",
       " 'Wisr Secured Car Loan',\n",
       " 'Stratton Finance New Car Loan',\n",
       " 'IMB New Car Loan',\n",
       " 'RACV New Car Loans',\n",
       " 'NRMA New Car Loan',\n",
       " 'NAB Personal Loan Unsecured Fixed ',\n",
       " 'OurMoneyMarket Car Loan',\n",
       " 'SocietyOne Car Loan',\n",
       " 'NRMA Used Car Loan',\n",
       " 'Symple Loans Personal Loan',\n",
       " 'RACV Used Car Loans',\n",
       " 'Macquarie Car Loan (For ']"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#selected only 14 as the other data scraped are either not car loans or a repeat due to advertisements\n",
    "finance_name = finance_name[0:15]\n",
    "finance_name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "#getting the rates for the site\n",
    "rates = soup.find_all('td', class_='comparison-table__feature')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "#getting comparison rates from the table\n",
    "i_rate =[]\n",
    "\n",
    "for i in range(1,130,9):\n",
    "    div = rates[i].find('div').text.replace('\\n','').replace(' ','').split('%')[0]\n",
    "    i_rate.append(div)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['5.44',\n",
       " '5.22',\n",
       " '4.41',\n",
       " '4.75',\n",
       " '4.05',\n",
       " '5.34',\n",
       " '5.69',\n",
       " '5.69',\n",
       " '7.91',\n",
       " '5.65',\n",
       " '5.95',\n",
       " '6.94',\n",
       " '6.47',\n",
       " '6.19',\n",
       " '8.47']"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#quality check\n",
    "i_rate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'id': ['155601',\n",
       "  '155602',\n",
       "  '159551',\n",
       "  '159571',\n",
       "  '158585',\n",
       "  '159540',\n",
       "  '159379',\n",
       "  '158349',\n",
       "  '158414',\n",
       "  '158871',\n",
       "  '159374',\n",
       "  '158259',\n",
       "  '156623',\n",
       "  '159041',\n",
       "  '158222',\n",
       "  '156668',\n",
       "  '153646',\n",
       "  '158319',\n",
       "  '154858',\n",
       "  '157753',\n",
       "  '158497',\n",
       "  '158111',\n",
       "  '157859',\n",
       "  '157677',\n",
       "  '158478',\n",
       "  '158189',\n",
       "  '158141',\n",
       "  '158875',\n",
       "  '159465',\n",
       "  '154970',\n",
       "  '158509',\n",
       "  '158525',\n",
       "  '156654',\n",
       "  '155117',\n",
       "  '158203',\n",
       "  '156759'],\n",
       " 'name': ['2016 Audi A3 8V MY17 1.6 TDI Sportback Hatchback',\n",
       "  '2016 Audi A3 8V MY16 Sportback 1.6 TDI Attraction Hatchback',\n",
       "  '2014 Audi A3 8V MY14 1.8 TFSI Ambition Sedan',\n",
       "  '2017 Audi A3 8V MY17 2.0 TFSI S Tronic Sport Cabriolet',\n",
       "  '2016 Audi A4 F4 (B9) 2.0 TFSI S Tronic Sport Sedan',\n",
       "  '2017 Audi A4 F4 MY17 (B9) 2.0 TFSI S Tronic Sport Sedan',\n",
       "  '2018 Audi A5 F5 MY18 2.0 TFSI Quattro S Tronic Sprt Coupe',\n",
       "  '2018 Audi Q2 GA MY18 1.4 TFSI Design Wagon',\n",
       "  '2020 Audi Q3 F3 MY21 35 TFSI S Line Sportback',\n",
       "  '2018 Audi Q3 8U MY18 1.4 TFSI (110kW) Wagon',\n",
       "  '2013 Audi Q3 8U 2.0 TFSI Quattro (125kW) Wagon',\n",
       "  '2016 Audi Q5 8R MY16 2.0 TDI Quattro Wagon',\n",
       "  '2017 Audi Q5 FY MY18 2.0 TFSI Quattro Sport Wagon',\n",
       "  '2017 Audi Q5 FY MY17 2.0 TDI Quattro Design Wagon',\n",
       "  '2019 Audi Q5 FY MY19 40 TDI Quattro Sport Wagon',\n",
       "  '2017 Audi Q5 FY MY17 2.0 TDI Quattro Design Wagon',\n",
       "  '2016 Audi Q7 4M 3.0 TDI Quattro Wagon',\n",
       "  '2016 Audi S3 8V MY17 Sportback 2.0 TFSI S Tronic Hatchback',\n",
       "  '2008 Audi S5 8T 4.2 FSI Quattro Coupe',\n",
       "  '2016 Audi TT FV S 2.0 TFSI Quattro Coupe',\n",
       "  '2013 BMW 116i F20 Hatchback',\n",
       "  '2016 BMW 118i F20 LCI Sport Line Hatchback',\n",
       "  '2017 BMW 118i F40 M Sport Hatchback',\n",
       "  '2014 BMW 220d F22 Coupe',\n",
       "  '2015 BMW 220i F23 Sport Line Convertible',\n",
       "  '2017 BMW 330i F30 LCI M Sport Sedan',\n",
       "  '2013 BMW 428i F32 Sport Line Coupe',\n",
       "  '2017 BMW 530i G30 MY17 Luxury Line Sedan',\n",
       "  '2014 BMW M135i F20 MY14 Hatchback',\n",
       "  '2016 BMW M4 F82 LCI MY17 Competition Coupe',\n",
       "  '2019 BMW X1 F48 MY19 xDrive 25I Wagon',\n",
       "  '2015 BMW X3 F25 MY15 xDrive20d Wagon',\n",
       "  '2017 BMW X3 F25 MY17 Update xDrive 20I Wagon',\n",
       "  '2018 BMW X3 G01 xDrive30I Wagon',\n",
       "  '2017 BMW X4 F26 MY16 xDrive 20D Coupe',\n",
       "  '2019 BMW X4 G02 M40I Wagon'],\n",
       " 'price': [25988.0,\n",
       "  21388.0,\n",
       "  21990.0,\n",
       "  39988.0,\n",
       "  32989.0,\n",
       "  38985.0,\n",
       "  65981.0,\n",
       "  42900.0,\n",
       "  61988.0,\n",
       "  39988.0,\n",
       "  25995.0,\n",
       "  40385.0,\n",
       "  54988.0,\n",
       "  50988.0,\n",
       "  65996.0,\n",
       "  45977.0,\n",
       "  59984.0,\n",
       "  47996.0,\n",
       "  37492.0,\n",
       "  61990.0,\n",
       "  18991.0,\n",
       "  27988.0,\n",
       "  30977.0,\n",
       "  26977.0,\n",
       "  31921.0,\n",
       "  43988.0,\n",
       "  35999.0,\n",
       "  65492.0,\n",
       "  31985.0,\n",
       "  79992.0,\n",
       "  54988.0,\n",
       "  38895.0,\n",
       "  39988.0,\n",
       "  61492.0,\n",
       "  61988.0,\n",
       "  91977.0]}"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#preparing data to put all vehicle data into a DataFrame\n",
    "vehicle_dict={\"id\":stock_no,\n",
    "             \"name\":vehicle,\n",
    "             \"price\":price}\n",
    "vehicle_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>name</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>155601</th>\n",
       "      <td>2016 Audi A3 8V MY17 1.6 TDI Sportback Hatchback</td>\n",
       "      <td>25988.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155602</th>\n",
       "      <td>2016 Audi A3 8V MY16 Sportback 1.6 TDI Attract...</td>\n",
       "      <td>21388.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159551</th>\n",
       "      <td>2014 Audi A3 8V MY14 1.8 TFSI Ambition Sedan</td>\n",
       "      <td>21990.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159571</th>\n",
       "      <td>2017 Audi A3 8V MY17 2.0 TFSI S Tronic Sport C...</td>\n",
       "      <td>39988.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158585</th>\n",
       "      <td>2016 Audi A4 F4 (B9) 2.0 TFSI S Tronic Sport S...</td>\n",
       "      <td>32989.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159540</th>\n",
       "      <td>2017 Audi A4 F4 MY17 (B9) 2.0 TFSI S Tronic Sp...</td>\n",
       "      <td>38985.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159379</th>\n",
       "      <td>2018 Audi A5 F5 MY18 2.0 TFSI Quattro S Tronic...</td>\n",
       "      <td>65981.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158349</th>\n",
       "      <td>2018 Audi Q2 GA MY18 1.4 TFSI Design Wagon</td>\n",
       "      <td>42900.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158414</th>\n",
       "      <td>2020 Audi Q3 F3 MY21 35 TFSI S Line Sportback</td>\n",
       "      <td>61988.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158871</th>\n",
       "      <td>2018 Audi Q3 8U MY18 1.4 TFSI (110kW) Wagon</td>\n",
       "      <td>39988.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159374</th>\n",
       "      <td>2013 Audi Q3 8U 2.0 TFSI Quattro (125kW) Wagon</td>\n",
       "      <td>25995.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158259</th>\n",
       "      <td>2016 Audi Q5 8R MY16 2.0 TDI Quattro Wagon</td>\n",
       "      <td>40385.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156623</th>\n",
       "      <td>2017 Audi Q5 FY MY18 2.0 TFSI Quattro Sport Wagon</td>\n",
       "      <td>54988.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159041</th>\n",
       "      <td>2017 Audi Q5 FY MY17 2.0 TDI Quattro Design Wagon</td>\n",
       "      <td>50988.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158222</th>\n",
       "      <td>2019 Audi Q5 FY MY19 40 TDI Quattro Sport Wagon</td>\n",
       "      <td>65996.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156668</th>\n",
       "      <td>2017 Audi Q5 FY MY17 2.0 TDI Quattro Design Wagon</td>\n",
       "      <td>45977.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>153646</th>\n",
       "      <td>2016 Audi Q7 4M 3.0 TDI Quattro Wagon</td>\n",
       "      <td>59984.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158319</th>\n",
       "      <td>2016 Audi S3 8V MY17 Sportback 2.0 TFSI S Tron...</td>\n",
       "      <td>47996.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154858</th>\n",
       "      <td>2008 Audi S5 8T 4.2 FSI Quattro Coupe</td>\n",
       "      <td>37492.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157753</th>\n",
       "      <td>2016 Audi TT FV S 2.0 TFSI Quattro Coupe</td>\n",
       "      <td>61990.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158497</th>\n",
       "      <td>2013 BMW 116i F20 Hatchback</td>\n",
       "      <td>18991.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158111</th>\n",
       "      <td>2016 BMW 118i F20 LCI Sport Line Hatchback</td>\n",
       "      <td>27988.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157859</th>\n",
       "      <td>2017 BMW 118i F40 M Sport Hatchback</td>\n",
       "      <td>30977.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157677</th>\n",
       "      <td>2014 BMW 220d F22 Coupe</td>\n",
       "      <td>26977.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158478</th>\n",
       "      <td>2015 BMW 220i F23 Sport Line Convertible</td>\n",
       "      <td>31921.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158189</th>\n",
       "      <td>2017 BMW 330i F30 LCI M Sport Sedan</td>\n",
       "      <td>43988.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158141</th>\n",
       "      <td>2013 BMW 428i F32 Sport Line Coupe</td>\n",
       "      <td>35999.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158875</th>\n",
       "      <td>2017 BMW 530i G30 MY17 Luxury Line Sedan</td>\n",
       "      <td>65492.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159465</th>\n",
       "      <td>2014 BMW M135i F20 MY14 Hatchback</td>\n",
       "      <td>31985.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154970</th>\n",
       "      <td>2016 BMW M4 F82 LCI MY17 Competition Coupe</td>\n",
       "      <td>79992.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158509</th>\n",
       "      <td>2019 BMW X1 F48 MY19 xDrive 25I Wagon</td>\n",
       "      <td>54988.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158525</th>\n",
       "      <td>2015 BMW X3 F25 MY15 xDrive20d Wagon</td>\n",
       "      <td>38895.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156654</th>\n",
       "      <td>2017 BMW X3 F25 MY17 Update xDrive 20I Wagon</td>\n",
       "      <td>39988.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155117</th>\n",
       "      <td>2018 BMW X3 G01 xDrive30I Wagon</td>\n",
       "      <td>61492.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158203</th>\n",
       "      <td>2017 BMW X4 F26 MY16 xDrive 20D Coupe</td>\n",
       "      <td>61988.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156759</th>\n",
       "      <td>2019 BMW X4 G02 M40I Wagon</td>\n",
       "      <td>91977.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                     name    price\n",
       "id                                                                \n",
       "155601   2016 Audi A3 8V MY17 1.6 TDI Sportback Hatchback  25988.0\n",
       "155602  2016 Audi A3 8V MY16 Sportback 1.6 TDI Attract...  21388.0\n",
       "159551       2014 Audi A3 8V MY14 1.8 TFSI Ambition Sedan  21990.0\n",
       "159571  2017 Audi A3 8V MY17 2.0 TFSI S Tronic Sport C...  39988.0\n",
       "158585  2016 Audi A4 F4 (B9) 2.0 TFSI S Tronic Sport S...  32989.0\n",
       "159540  2017 Audi A4 F4 MY17 (B9) 2.0 TFSI S Tronic Sp...  38985.0\n",
       "159379  2018 Audi A5 F5 MY18 2.0 TFSI Quattro S Tronic...  65981.0\n",
       "158349         2018 Audi Q2 GA MY18 1.4 TFSI Design Wagon  42900.0\n",
       "158414      2020 Audi Q3 F3 MY21 35 TFSI S Line Sportback  61988.0\n",
       "158871        2018 Audi Q3 8U MY18 1.4 TFSI (110kW) Wagon  39988.0\n",
       "159374     2013 Audi Q3 8U 2.0 TFSI Quattro (125kW) Wagon  25995.0\n",
       "158259         2016 Audi Q5 8R MY16 2.0 TDI Quattro Wagon  40385.0\n",
       "156623  2017 Audi Q5 FY MY18 2.0 TFSI Quattro Sport Wagon  54988.0\n",
       "159041  2017 Audi Q5 FY MY17 2.0 TDI Quattro Design Wagon  50988.0\n",
       "158222    2019 Audi Q5 FY MY19 40 TDI Quattro Sport Wagon  65996.0\n",
       "156668  2017 Audi Q5 FY MY17 2.0 TDI Quattro Design Wagon  45977.0\n",
       "153646              2016 Audi Q7 4M 3.0 TDI Quattro Wagon  59984.0\n",
       "158319  2016 Audi S3 8V MY17 Sportback 2.0 TFSI S Tron...  47996.0\n",
       "154858              2008 Audi S5 8T 4.2 FSI Quattro Coupe  37492.0\n",
       "157753           2016 Audi TT FV S 2.0 TFSI Quattro Coupe  61990.0\n",
       "158497                        2013 BMW 116i F20 Hatchback  18991.0\n",
       "158111         2016 BMW 118i F20 LCI Sport Line Hatchback  27988.0\n",
       "157859                2017 BMW 118i F40 M Sport Hatchback  30977.0\n",
       "157677                            2014 BMW 220d F22 Coupe  26977.0\n",
       "158478           2015 BMW 220i F23 Sport Line Convertible  31921.0\n",
       "158189                2017 BMW 330i F30 LCI M Sport Sedan  43988.0\n",
       "158141                 2013 BMW 428i F32 Sport Line Coupe  35999.0\n",
       "158875           2017 BMW 530i G30 MY17 Luxury Line Sedan  65492.0\n",
       "159465                  2014 BMW M135i F20 MY14 Hatchback  31985.0\n",
       "154970         2016 BMW M4 F82 LCI MY17 Competition Coupe  79992.0\n",
       "158509              2019 BMW X1 F48 MY19 xDrive 25I Wagon  54988.0\n",
       "158525               2015 BMW X3 F25 MY15 xDrive20d Wagon  38895.0\n",
       "156654       2017 BMW X3 F25 MY17 Update xDrive 20I Wagon  39988.0\n",
       "155117                    2018 BMW X3 G01 xDrive30I Wagon  61492.0\n",
       "158203              2017 BMW X4 F26 MY16 xDrive 20D Coupe  61988.0\n",
       "156759                         2019 BMW X4 G02 M40I Wagon  91977.0"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#creating DataFrame, stock id is unique and used as the index\n",
    "vehicle_df = pd.DataFrame(vehicle_dict).set_index('id')\n",
    "vehicle_df "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>name</th>\n",
       "      <th>price</th>\n",
       "      <th>deposit</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>155601</th>\n",
       "      <td>2016 Audi A3 8V MY17 1.6 TDI Sportback Hatchback</td>\n",
       "      <td>25988.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155602</th>\n",
       "      <td>2016 Audi A3 8V MY16 Sportback 1.6 TDI Attract...</td>\n",
       "      <td>21388.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159551</th>\n",
       "      <td>2014 Audi A3 8V MY14 1.8 TFSI Ambition Sedan</td>\n",
       "      <td>21990.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159571</th>\n",
       "      <td>2017 Audi A3 8V MY17 2.0 TFSI S Tronic Sport C...</td>\n",
       "      <td>39988.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158585</th>\n",
       "      <td>2016 Audi A4 F4 (B9) 2.0 TFSI S Tronic Sport S...</td>\n",
       "      <td>32989.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159540</th>\n",
       "      <td>2017 Audi A4 F4 MY17 (B9) 2.0 TFSI S Tronic Sp...</td>\n",
       "      <td>38985.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159379</th>\n",
       "      <td>2018 Audi A5 F5 MY18 2.0 TFSI Quattro S Tronic...</td>\n",
       "      <td>65981.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158349</th>\n",
       "      <td>2018 Audi Q2 GA MY18 1.4 TFSI Design Wagon</td>\n",
       "      <td>42900.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158414</th>\n",
       "      <td>2020 Audi Q3 F3 MY21 35 TFSI S Line Sportback</td>\n",
       "      <td>61988.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158871</th>\n",
       "      <td>2018 Audi Q3 8U MY18 1.4 TFSI (110kW) Wagon</td>\n",
       "      <td>39988.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159374</th>\n",
       "      <td>2013 Audi Q3 8U 2.0 TFSI Quattro (125kW) Wagon</td>\n",
       "      <td>25995.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158259</th>\n",
       "      <td>2016 Audi Q5 8R MY16 2.0 TDI Quattro Wagon</td>\n",
       "      <td>40385.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156623</th>\n",
       "      <td>2017 Audi Q5 FY MY18 2.0 TFSI Quattro Sport Wagon</td>\n",
       "      <td>54988.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159041</th>\n",
       "      <td>2017 Audi Q5 FY MY17 2.0 TDI Quattro Design Wagon</td>\n",
       "      <td>50988.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158222</th>\n",
       "      <td>2019 Audi Q5 FY MY19 40 TDI Quattro Sport Wagon</td>\n",
       "      <td>65996.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156668</th>\n",
       "      <td>2017 Audi Q5 FY MY17 2.0 TDI Quattro Design Wagon</td>\n",
       "      <td>45977.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>153646</th>\n",
       "      <td>2016 Audi Q7 4M 3.0 TDI Quattro Wagon</td>\n",
       "      <td>59984.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158319</th>\n",
       "      <td>2016 Audi S3 8V MY17 Sportback 2.0 TFSI S Tron...</td>\n",
       "      <td>47996.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154858</th>\n",
       "      <td>2008 Audi S5 8T 4.2 FSI Quattro Coupe</td>\n",
       "      <td>37492.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157753</th>\n",
       "      <td>2016 Audi TT FV S 2.0 TFSI Quattro Coupe</td>\n",
       "      <td>61990.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158497</th>\n",
       "      <td>2013 BMW 116i F20 Hatchback</td>\n",
       "      <td>18991.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158111</th>\n",
       "      <td>2016 BMW 118i F20 LCI Sport Line Hatchback</td>\n",
       "      <td>27988.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157859</th>\n",
       "      <td>2017 BMW 118i F40 M Sport Hatchback</td>\n",
       "      <td>30977.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157677</th>\n",
       "      <td>2014 BMW 220d F22 Coupe</td>\n",
       "      <td>26977.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158478</th>\n",
       "      <td>2015 BMW 220i F23 Sport Line Convertible</td>\n",
       "      <td>31921.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158189</th>\n",
       "      <td>2017 BMW 330i F30 LCI M Sport Sedan</td>\n",
       "      <td>43988.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158141</th>\n",
       "      <td>2013 BMW 428i F32 Sport Line Coupe</td>\n",
       "      <td>35999.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158875</th>\n",
       "      <td>2017 BMW 530i G30 MY17 Luxury Line Sedan</td>\n",
       "      <td>65492.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159465</th>\n",
       "      <td>2014 BMW M135i F20 MY14 Hatchback</td>\n",
       "      <td>31985.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154970</th>\n",
       "      <td>2016 BMW M4 F82 LCI MY17 Competition Coupe</td>\n",
       "      <td>79992.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158509</th>\n",
       "      <td>2019 BMW X1 F48 MY19 xDrive 25I Wagon</td>\n",
       "      <td>54988.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158525</th>\n",
       "      <td>2015 BMW X3 F25 MY15 xDrive20d Wagon</td>\n",
       "      <td>38895.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156654</th>\n",
       "      <td>2017 BMW X3 F25 MY17 Update xDrive 20I Wagon</td>\n",
       "      <td>39988.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155117</th>\n",
       "      <td>2018 BMW X3 G01 xDrive30I Wagon</td>\n",
       "      <td>61492.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158203</th>\n",
       "      <td>2017 BMW X4 F26 MY16 xDrive 20D Coupe</td>\n",
       "      <td>61988.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156759</th>\n",
       "      <td>2019 BMW X4 G02 M40I Wagon</td>\n",
       "      <td>91977.0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                     name    price deposit\n",
       "id                                                                        \n",
       "155601   2016 Audi A3 8V MY17 1.6 TDI Sportback Hatchback  25988.0        \n",
       "155602  2016 Audi A3 8V MY16 Sportback 1.6 TDI Attract...  21388.0        \n",
       "159551       2014 Audi A3 8V MY14 1.8 TFSI Ambition Sedan  21990.0        \n",
       "159571  2017 Audi A3 8V MY17 2.0 TFSI S Tronic Sport C...  39988.0        \n",
       "158585  2016 Audi A4 F4 (B9) 2.0 TFSI S Tronic Sport S...  32989.0        \n",
       "159540  2017 Audi A4 F4 MY17 (B9) 2.0 TFSI S Tronic Sp...  38985.0        \n",
       "159379  2018 Audi A5 F5 MY18 2.0 TFSI Quattro S Tronic...  65981.0        \n",
       "158349         2018 Audi Q2 GA MY18 1.4 TFSI Design Wagon  42900.0        \n",
       "158414      2020 Audi Q3 F3 MY21 35 TFSI S Line Sportback  61988.0        \n",
       "158871        2018 Audi Q3 8U MY18 1.4 TFSI (110kW) Wagon  39988.0        \n",
       "159374     2013 Audi Q3 8U 2.0 TFSI Quattro (125kW) Wagon  25995.0        \n",
       "158259         2016 Audi Q5 8R MY16 2.0 TDI Quattro Wagon  40385.0        \n",
       "156623  2017 Audi Q5 FY MY18 2.0 TFSI Quattro Sport Wagon  54988.0        \n",
       "159041  2017 Audi Q5 FY MY17 2.0 TDI Quattro Design Wagon  50988.0        \n",
       "158222    2019 Audi Q5 FY MY19 40 TDI Quattro Sport Wagon  65996.0        \n",
       "156668  2017 Audi Q5 FY MY17 2.0 TDI Quattro Design Wagon  45977.0        \n",
       "153646              2016 Audi Q7 4M 3.0 TDI Quattro Wagon  59984.0        \n",
       "158319  2016 Audi S3 8V MY17 Sportback 2.0 TFSI S Tron...  47996.0        \n",
       "154858              2008 Audi S5 8T 4.2 FSI Quattro Coupe  37492.0        \n",
       "157753           2016 Audi TT FV S 2.0 TFSI Quattro Coupe  61990.0        \n",
       "158497                        2013 BMW 116i F20 Hatchback  18991.0        \n",
       "158111         2016 BMW 118i F20 LCI Sport Line Hatchback  27988.0        \n",
       "157859                2017 BMW 118i F40 M Sport Hatchback  30977.0        \n",
       "157677                            2014 BMW 220d F22 Coupe  26977.0        \n",
       "158478           2015 BMW 220i F23 Sport Line Convertible  31921.0        \n",
       "158189                2017 BMW 330i F30 LCI M Sport Sedan  43988.0        \n",
       "158141                 2013 BMW 428i F32 Sport Line Coupe  35999.0        \n",
       "158875           2017 BMW 530i G30 MY17 Luxury Line Sedan  65492.0        \n",
       "159465                  2014 BMW M135i F20 MY14 Hatchback  31985.0        \n",
       "154970         2016 BMW M4 F82 LCI MY17 Competition Coupe  79992.0        \n",
       "158509              2019 BMW X1 F48 MY19 xDrive 25I Wagon  54988.0        \n",
       "158525               2015 BMW X3 F25 MY15 xDrive20d Wagon  38895.0        \n",
       "156654       2017 BMW X3 F25 MY17 Update xDrive 20I Wagon  39988.0        \n",
       "155117                    2018 BMW X3 G01 xDrive30I Wagon  61492.0        \n",
       "158203              2017 BMW X4 F26 MY16 xDrive 20D Coupe  61988.0        \n",
       "156759                         2019 BMW X4 G02 M40I Wagon  91977.0        "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#adding new column deposit as we are calculating Monthly payments after puting down 10% deposit\n",
    "vehicle_df['deposit']=''\n",
    "vehicle_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'9.84'"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "car_seller_rate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>name</th>\n",
       "      <th>price</th>\n",
       "      <th>deposit</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>155601</th>\n",
       "      <td>2016 Audi A3 8V MY17 1.6 TDI Sportback Hatchback</td>\n",
       "      <td>25988.0</td>\n",
       "      <td>2598.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155602</th>\n",
       "      <td>2016 Audi A3 8V MY16 Sportback 1.6 TDI Attract...</td>\n",
       "      <td>21388.0</td>\n",
       "      <td>2138.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159551</th>\n",
       "      <td>2014 Audi A3 8V MY14 1.8 TFSI Ambition Sedan</td>\n",
       "      <td>21990.0</td>\n",
       "      <td>2199.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159571</th>\n",
       "      <td>2017 Audi A3 8V MY17 2.0 TFSI S Tronic Sport C...</td>\n",
       "      <td>39988.0</td>\n",
       "      <td>3998.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158585</th>\n",
       "      <td>2016 Audi A4 F4 (B9) 2.0 TFSI S Tronic Sport S...</td>\n",
       "      <td>32989.0</td>\n",
       "      <td>3298.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159540</th>\n",
       "      <td>2017 Audi A4 F4 MY17 (B9) 2.0 TFSI S Tronic Sp...</td>\n",
       "      <td>38985.0</td>\n",
       "      <td>3898.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159379</th>\n",
       "      <td>2018 Audi A5 F5 MY18 2.0 TFSI Quattro S Tronic...</td>\n",
       "      <td>65981.0</td>\n",
       "      <td>6598.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158349</th>\n",
       "      <td>2018 Audi Q2 GA MY18 1.4 TFSI Design Wagon</td>\n",
       "      <td>42900.0</td>\n",
       "      <td>4290.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158414</th>\n",
       "      <td>2020 Audi Q3 F3 MY21 35 TFSI S Line Sportback</td>\n",
       "      <td>61988.0</td>\n",
       "      <td>6198.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158871</th>\n",
       "      <td>2018 Audi Q3 8U MY18 1.4 TFSI (110kW) Wagon</td>\n",
       "      <td>39988.0</td>\n",
       "      <td>3998.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159374</th>\n",
       "      <td>2013 Audi Q3 8U 2.0 TFSI Quattro (125kW) Wagon</td>\n",
       "      <td>25995.0</td>\n",
       "      <td>2599.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158259</th>\n",
       "      <td>2016 Audi Q5 8R MY16 2.0 TDI Quattro Wagon</td>\n",
       "      <td>40385.0</td>\n",
       "      <td>4038.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156623</th>\n",
       "      <td>2017 Audi Q5 FY MY18 2.0 TFSI Quattro Sport Wagon</td>\n",
       "      <td>54988.0</td>\n",
       "      <td>5498.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159041</th>\n",
       "      <td>2017 Audi Q5 FY MY17 2.0 TDI Quattro Design Wagon</td>\n",
       "      <td>50988.0</td>\n",
       "      <td>5098.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158222</th>\n",
       "      <td>2019 Audi Q5 FY MY19 40 TDI Quattro Sport Wagon</td>\n",
       "      <td>65996.0</td>\n",
       "      <td>6599.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156668</th>\n",
       "      <td>2017 Audi Q5 FY MY17 2.0 TDI Quattro Design Wagon</td>\n",
       "      <td>45977.0</td>\n",
       "      <td>4597.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>153646</th>\n",
       "      <td>2016 Audi Q7 4M 3.0 TDI Quattro Wagon</td>\n",
       "      <td>59984.0</td>\n",
       "      <td>5998.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158319</th>\n",
       "      <td>2016 Audi S3 8V MY17 Sportback 2.0 TFSI S Tron...</td>\n",
       "      <td>47996.0</td>\n",
       "      <td>4799.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154858</th>\n",
       "      <td>2008 Audi S5 8T 4.2 FSI Quattro Coupe</td>\n",
       "      <td>37492.0</td>\n",
       "      <td>3749.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157753</th>\n",
       "      <td>2016 Audi TT FV S 2.0 TFSI Quattro Coupe</td>\n",
       "      <td>61990.0</td>\n",
       "      <td>6199.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158497</th>\n",
       "      <td>2013 BMW 116i F20 Hatchback</td>\n",
       "      <td>18991.0</td>\n",
       "      <td>1899.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158111</th>\n",
       "      <td>2016 BMW 118i F20 LCI Sport Line Hatchback</td>\n",
       "      <td>27988.0</td>\n",
       "      <td>2798.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157859</th>\n",
       "      <td>2017 BMW 118i F40 M Sport Hatchback</td>\n",
       "      <td>30977.0</td>\n",
       "      <td>3097.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157677</th>\n",
       "      <td>2014 BMW 220d F22 Coupe</td>\n",
       "      <td>26977.0</td>\n",
       "      <td>2697.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158478</th>\n",
       "      <td>2015 BMW 220i F23 Sport Line Convertible</td>\n",
       "      <td>31921.0</td>\n",
       "      <td>3192.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158189</th>\n",
       "      <td>2017 BMW 330i F30 LCI M Sport Sedan</td>\n",
       "      <td>43988.0</td>\n",
       "      <td>4398.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158141</th>\n",
       "      <td>2013 BMW 428i F32 Sport Line Coupe</td>\n",
       "      <td>35999.0</td>\n",
       "      <td>3599.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158875</th>\n",
       "      <td>2017 BMW 530i G30 MY17 Luxury Line Sedan</td>\n",
       "      <td>65492.0</td>\n",
       "      <td>6549.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159465</th>\n",
       "      <td>2014 BMW M135i F20 MY14 Hatchback</td>\n",
       "      <td>31985.0</td>\n",
       "      <td>3198.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154970</th>\n",
       "      <td>2016 BMW M4 F82 LCI MY17 Competition Coupe</td>\n",
       "      <td>79992.0</td>\n",
       "      <td>7999.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158509</th>\n",
       "      <td>2019 BMW X1 F48 MY19 xDrive 25I Wagon</td>\n",
       "      <td>54988.0</td>\n",
       "      <td>5498.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158525</th>\n",
       "      <td>2015 BMW X3 F25 MY15 xDrive20d Wagon</td>\n",
       "      <td>38895.0</td>\n",
       "      <td>3889.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156654</th>\n",
       "      <td>2017 BMW X3 F25 MY17 Update xDrive 20I Wagon</td>\n",
       "      <td>39988.0</td>\n",
       "      <td>3998.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155117</th>\n",
       "      <td>2018 BMW X3 G01 xDrive30I Wagon</td>\n",
       "      <td>61492.0</td>\n",
       "      <td>6149.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158203</th>\n",
       "      <td>2017 BMW X4 F26 MY16 xDrive 20D Coupe</td>\n",
       "      <td>61988.0</td>\n",
       "      <td>6198.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156759</th>\n",
       "      <td>2019 BMW X4 G02 M40I Wagon</td>\n",
       "      <td>91977.0</td>\n",
       "      <td>9197.7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                     name    price  deposit\n",
       "id                                                                         \n",
       "155601   2016 Audi A3 8V MY17 1.6 TDI Sportback Hatchback  25988.0   2598.8\n",
       "155602  2016 Audi A3 8V MY16 Sportback 1.6 TDI Attract...  21388.0   2138.8\n",
       "159551       2014 Audi A3 8V MY14 1.8 TFSI Ambition Sedan  21990.0   2199.0\n",
       "159571  2017 Audi A3 8V MY17 2.0 TFSI S Tronic Sport C...  39988.0   3998.8\n",
       "158585  2016 Audi A4 F4 (B9) 2.0 TFSI S Tronic Sport S...  32989.0   3298.9\n",
       "159540  2017 Audi A4 F4 MY17 (B9) 2.0 TFSI S Tronic Sp...  38985.0   3898.5\n",
       "159379  2018 Audi A5 F5 MY18 2.0 TFSI Quattro S Tronic...  65981.0   6598.1\n",
       "158349         2018 Audi Q2 GA MY18 1.4 TFSI Design Wagon  42900.0   4290.0\n",
       "158414      2020 Audi Q3 F3 MY21 35 TFSI S Line Sportback  61988.0   6198.8\n",
       "158871        2018 Audi Q3 8U MY18 1.4 TFSI (110kW) Wagon  39988.0   3998.8\n",
       "159374     2013 Audi Q3 8U 2.0 TFSI Quattro (125kW) Wagon  25995.0   2599.5\n",
       "158259         2016 Audi Q5 8R MY16 2.0 TDI Quattro Wagon  40385.0   4038.5\n",
       "156623  2017 Audi Q5 FY MY18 2.0 TFSI Quattro Sport Wagon  54988.0   5498.8\n",
       "159041  2017 Audi Q5 FY MY17 2.0 TDI Quattro Design Wagon  50988.0   5098.8\n",
       "158222    2019 Audi Q5 FY MY19 40 TDI Quattro Sport Wagon  65996.0   6599.6\n",
       "156668  2017 Audi Q5 FY MY17 2.0 TDI Quattro Design Wagon  45977.0   4597.7\n",
       "153646              2016 Audi Q7 4M 3.0 TDI Quattro Wagon  59984.0   5998.4\n",
       "158319  2016 Audi S3 8V MY17 Sportback 2.0 TFSI S Tron...  47996.0   4799.6\n",
       "154858              2008 Audi S5 8T 4.2 FSI Quattro Coupe  37492.0   3749.2\n",
       "157753           2016 Audi TT FV S 2.0 TFSI Quattro Coupe  61990.0   6199.0\n",
       "158497                        2013 BMW 116i F20 Hatchback  18991.0   1899.1\n",
       "158111         2016 BMW 118i F20 LCI Sport Line Hatchback  27988.0   2798.8\n",
       "157859                2017 BMW 118i F40 M Sport Hatchback  30977.0   3097.7\n",
       "157677                            2014 BMW 220d F22 Coupe  26977.0   2697.7\n",
       "158478           2015 BMW 220i F23 Sport Line Convertible  31921.0   3192.1\n",
       "158189                2017 BMW 330i F30 LCI M Sport Sedan  43988.0   4398.8\n",
       "158141                 2013 BMW 428i F32 Sport Line Coupe  35999.0   3599.9\n",
       "158875           2017 BMW 530i G30 MY17 Luxury Line Sedan  65492.0   6549.2\n",
       "159465                  2014 BMW M135i F20 MY14 Hatchback  31985.0   3198.5\n",
       "154970         2016 BMW M4 F82 LCI MY17 Competition Coupe  79992.0   7999.2\n",
       "158509              2019 BMW X1 F48 MY19 xDrive 25I Wagon  54988.0   5498.8\n",
       "158525               2015 BMW X3 F25 MY15 xDrive20d Wagon  38895.0   3889.5\n",
       "156654       2017 BMW X3 F25 MY17 Update xDrive 20I Wagon  39988.0   3998.8\n",
       "155117                    2018 BMW X3 G01 xDrive30I Wagon  61492.0   6149.2\n",
       "158203              2017 BMW X4 F26 MY16 xDrive 20D Coupe  61988.0   6198.8\n",
       "156759                         2019 BMW X4 G02 M40I Wagon  91977.0   9197.7"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#calculating the deposit of 10% from car price\n",
    "vehicle_df[\"deposit\"] = vehicle_df['price']*0.1\n",
    "vehicle_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "#equation for the monthly car loan\n",
    "#Kudos to Helen for the equation\n",
    "t=12 #12 months in a year\n",
    "years =5 #calculated for 5 years\n",
    "r= (float(car_seller_rate)/t)*0.01 #annual comparison rate\n",
    "loan_amount = vehicle_df['price']-vehicle_df['deposit'] #loan amount after deposit\n",
    "n=t*years #term of the loan"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "#input the required loan amount into the DataFrame Column\n",
    "vehicle_df['loan amount'] = loan_amount"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "#setting up the column for the seller's required monthly repayment\n",
    "vehicle_df['seller monthly repayment'] = ''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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>name</th>\n",
       "      <th>price</th>\n",
       "      <th>deposit</th>\n",
       "      <th>loan amount</th>\n",
       "      <th>seller monthly repayment</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>155601</th>\n",
       "      <td>2016 Audi A3 8V MY17 1.6 TDI Sportback Hatchback</td>\n",
       "      <td>25988.0</td>\n",
       "      <td>2598.8</td>\n",
       "      <td>23389.2</td>\n",
       "      <td>495.112031</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155602</th>\n",
       "      <td>2016 Audi A3 8V MY16 Sportback 1.6 TDI Attract...</td>\n",
       "      <td>21388.0</td>\n",
       "      <td>2138.8</td>\n",
       "      <td>19249.2</td>\n",
       "      <td>407.474839</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159551</th>\n",
       "      <td>2014 Audi A3 8V MY14 1.8 TFSI Ambition Sedan</td>\n",
       "      <td>21990.0</td>\n",
       "      <td>2199.0</td>\n",
       "      <td>19791.0</td>\n",
       "      <td>418.943880</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159571</th>\n",
       "      <td>2017 Audi A3 8V MY17 2.0 TFSI S Tronic Sport C...</td>\n",
       "      <td>39988.0</td>\n",
       "      <td>3998.8</td>\n",
       "      <td>35989.2</td>\n",
       "      <td>761.833919</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158585</th>\n",
       "      <td>2016 Audi A4 F4 (B9) 2.0 TFSI S Tronic Sport S...</td>\n",
       "      <td>32989.0</td>\n",
       "      <td>3298.9</td>\n",
       "      <td>29690.1</td>\n",
       "      <td>628.492027</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159540</th>\n",
       "      <td>2017 Audi A4 F4 MY17 (B9) 2.0 TFSI S Tronic Sp...</td>\n",
       "      <td>38985.0</td>\n",
       "      <td>3898.5</td>\n",
       "      <td>35086.5</td>\n",
       "      <td>742.725201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159379</th>\n",
       "      <td>2018 Audi A5 F5 MY18 2.0 TFSI Quattro S Tronic...</td>\n",
       "      <td>65981.0</td>\n",
       "      <td>6598.1</td>\n",
       "      <td>59382.9</td>\n",
       "      <td>1257.041208</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158349</th>\n",
       "      <td>2018 Audi Q2 GA MY18 1.4 TFSI Design Wagon</td>\n",
       "      <td>42900.0</td>\n",
       "      <td>4290.0</td>\n",
       "      <td>38610.0</td>\n",
       "      <td>817.312072</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158414</th>\n",
       "      <td>2020 Audi Q3 F3 MY21 35 TFSI S Line Sportback</td>\n",
       "      <td>61988.0</td>\n",
       "      <td>6198.8</td>\n",
       "      <td>55789.2</td>\n",
       "      <td>1180.968315</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158871</th>\n",
       "      <td>2018 Audi Q3 8U MY18 1.4 TFSI (110kW) Wagon</td>\n",
       "      <td>39988.0</td>\n",
       "      <td>3998.8</td>\n",
       "      <td>35989.2</td>\n",
       "      <td>761.833919</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159374</th>\n",
       "      <td>2013 Audi Q3 8U 2.0 TFSI Quattro (125kW) Wagon</td>\n",
       "      <td>25995.0</td>\n",
       "      <td>2599.5</td>\n",
       "      <td>23395.5</td>\n",
       "      <td>495.245392</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158259</th>\n",
       "      <td>2016 Audi Q5 8R MY16 2.0 TDI Quattro Wagon</td>\n",
       "      <td>40385.0</td>\n",
       "      <td>4038.5</td>\n",
       "      <td>36346.5</td>\n",
       "      <td>769.397390</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156623</th>\n",
       "      <td>2017 Audi Q5 FY MY18 2.0 TFSI Quattro Sport Wagon</td>\n",
       "      <td>54988.0</td>\n",
       "      <td>5498.8</td>\n",
       "      <td>49489.2</td>\n",
       "      <td>1047.607371</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159041</th>\n",
       "      <td>2017 Audi Q5 FY MY17 2.0 TDI Quattro Design Wagon</td>\n",
       "      <td>50988.0</td>\n",
       "      <td>5098.8</td>\n",
       "      <td>45889.2</td>\n",
       "      <td>971.401117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158222</th>\n",
       "      <td>2019 Audi Q5 FY MY19 40 TDI Quattro Sport Wagon</td>\n",
       "      <td>65996.0</td>\n",
       "      <td>6599.6</td>\n",
       "      <td>59396.4</td>\n",
       "      <td>1257.326982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156668</th>\n",
       "      <td>2017 Audi Q5 FY MY17 2.0 TDI Quattro Design Wagon</td>\n",
       "      <td>45977.0</td>\n",
       "      <td>4597.7</td>\n",
       "      <td>41379.3</td>\n",
       "      <td>875.933733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>153646</th>\n",
       "      <td>2016 Audi Q7 4M 3.0 TDI Quattro Wagon</td>\n",
       "      <td>59984.0</td>\n",
       "      <td>5998.4</td>\n",
       "      <td>53985.6</td>\n",
       "      <td>1142.788982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158319</th>\n",
       "      <td>2016 Audi S3 8V MY17 Sportback 2.0 TFSI S Tron...</td>\n",
       "      <td>47996.0</td>\n",
       "      <td>4799.6</td>\n",
       "      <td>43196.4</td>\n",
       "      <td>914.398839</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154858</th>\n",
       "      <td>2008 Audi S5 8T 4.2 FSI Quattro Coupe</td>\n",
       "      <td>37492.0</td>\n",
       "      <td>3749.2</td>\n",
       "      <td>33742.8</td>\n",
       "      <td>714.281217</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157753</th>\n",
       "      <td>2016 Audi TT FV S 2.0 TFSI Quattro Coupe</td>\n",
       "      <td>61990.0</td>\n",
       "      <td>6199.0</td>\n",
       "      <td>55791.0</td>\n",
       "      <td>1181.006418</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158497</th>\n",
       "      <td>2013 BMW 116i F20 Hatchback</td>\n",
       "      <td>18991.0</td>\n",
       "      <td>1899.1</td>\n",
       "      <td>17091.9</td>\n",
       "      <td>361.808242</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158111</th>\n",
       "      <td>2016 BMW 118i F20 LCI Sport Line Hatchback</td>\n",
       "      <td>27988.0</td>\n",
       "      <td>2798.8</td>\n",
       "      <td>25189.2</td>\n",
       "      <td>533.215158</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157859</th>\n",
       "      <td>2017 BMW 118i F40 M Sport Hatchback</td>\n",
       "      <td>30977.0</td>\n",
       "      <td>3097.7</td>\n",
       "      <td>27879.3</td>\n",
       "      <td>590.160281</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157677</th>\n",
       "      <td>2014 BMW 220d F22 Coupe</td>\n",
       "      <td>26977.0</td>\n",
       "      <td>2697.7</td>\n",
       "      <td>24279.3</td>\n",
       "      <td>513.954027</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158478</th>\n",
       "      <td>2015 BMW 220i F23 Sport Line Convertible</td>\n",
       "      <td>31921.0</td>\n",
       "      <td>3192.1</td>\n",
       "      <td>28728.9</td>\n",
       "      <td>608.144957</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158189</th>\n",
       "      <td>2017 BMW 330i F30 LCI M Sport Sedan</td>\n",
       "      <td>43988.0</td>\n",
       "      <td>4398.8</td>\n",
       "      <td>39589.2</td>\n",
       "      <td>838.040173</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158141</th>\n",
       "      <td>2013 BMW 428i F32 Sport Line Coupe</td>\n",
       "      <td>35999.0</td>\n",
       "      <td>3599.9</td>\n",
       "      <td>32399.1</td>\n",
       "      <td>685.837233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158875</th>\n",
       "      <td>2017 BMW 530i G30 MY17 Luxury Line Sedan</td>\n",
       "      <td>65492.0</td>\n",
       "      <td>6549.2</td>\n",
       "      <td>58942.8</td>\n",
       "      <td>1247.724994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159465</th>\n",
       "      <td>2014 BMW M135i F20 MY14 Hatchback</td>\n",
       "      <td>31985.0</td>\n",
       "      <td>3198.5</td>\n",
       "      <td>28786.5</td>\n",
       "      <td>609.364257</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154970</th>\n",
       "      <td>2016 BMW M4 F82 LCI MY17 Competition Coupe</td>\n",
       "      <td>79992.0</td>\n",
       "      <td>7999.2</td>\n",
       "      <td>71992.8</td>\n",
       "      <td>1523.972664</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158509</th>\n",
       "      <td>2019 BMW X1 F48 MY19 xDrive 25I Wagon</td>\n",
       "      <td>54988.0</td>\n",
       "      <td>5498.8</td>\n",
       "      <td>49489.2</td>\n",
       "      <td>1047.607371</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158525</th>\n",
       "      <td>2015 BMW X3 F25 MY15 xDrive20d Wagon</td>\n",
       "      <td>38895.0</td>\n",
       "      <td>3889.5</td>\n",
       "      <td>35005.5</td>\n",
       "      <td>741.010560</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156654</th>\n",
       "      <td>2017 BMW X3 F25 MY17 Update xDrive 20I Wagon</td>\n",
       "      <td>39988.0</td>\n",
       "      <td>3998.8</td>\n",
       "      <td>35989.2</td>\n",
       "      <td>761.833919</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155117</th>\n",
       "      <td>2018 BMW X3 G01 xDrive30I Wagon</td>\n",
       "      <td>61492.0</td>\n",
       "      <td>6149.2</td>\n",
       "      <td>55342.8</td>\n",
       "      <td>1171.518740</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158203</th>\n",
       "      <td>2017 BMW X4 F26 MY16 xDrive 20D Coupe</td>\n",
       "      <td>61988.0</td>\n",
       "      <td>6198.8</td>\n",
       "      <td>55789.2</td>\n",
       "      <td>1180.968315</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156759</th>\n",
       "      <td>2019 BMW X4 G02 M40I Wagon</td>\n",
       "      <td>91977.0</td>\n",
       "      <td>9197.7</td>\n",
       "      <td>82779.3</td>\n",
       "      <td>1752.305652</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                     name    price  deposit  \\\n",
       "id                                                                            \n",
       "155601   2016 Audi A3 8V MY17 1.6 TDI Sportback Hatchback  25988.0   2598.8   \n",
       "155602  2016 Audi A3 8V MY16 Sportback 1.6 TDI Attract...  21388.0   2138.8   \n",
       "159551       2014 Audi A3 8V MY14 1.8 TFSI Ambition Sedan  21990.0   2199.0   \n",
       "159571  2017 Audi A3 8V MY17 2.0 TFSI S Tronic Sport C...  39988.0   3998.8   \n",
       "158585  2016 Audi A4 F4 (B9) 2.0 TFSI S Tronic Sport S...  32989.0   3298.9   \n",
       "159540  2017 Audi A4 F4 MY17 (B9) 2.0 TFSI S Tronic Sp...  38985.0   3898.5   \n",
       "159379  2018 Audi A5 F5 MY18 2.0 TFSI Quattro S Tronic...  65981.0   6598.1   \n",
       "158349         2018 Audi Q2 GA MY18 1.4 TFSI Design Wagon  42900.0   4290.0   \n",
       "158414      2020 Audi Q3 F3 MY21 35 TFSI S Line Sportback  61988.0   6198.8   \n",
       "158871        2018 Audi Q3 8U MY18 1.4 TFSI (110kW) Wagon  39988.0   3998.8   \n",
       "159374     2013 Audi Q3 8U 2.0 TFSI Quattro (125kW) Wagon  25995.0   2599.5   \n",
       "158259         2016 Audi Q5 8R MY16 2.0 TDI Quattro Wagon  40385.0   4038.5   \n",
       "156623  2017 Audi Q5 FY MY18 2.0 TFSI Quattro Sport Wagon  54988.0   5498.8   \n",
       "159041  2017 Audi Q5 FY MY17 2.0 TDI Quattro Design Wagon  50988.0   5098.8   \n",
       "158222    2019 Audi Q5 FY MY19 40 TDI Quattro Sport Wagon  65996.0   6599.6   \n",
       "156668  2017 Audi Q5 FY MY17 2.0 TDI Quattro Design Wagon  45977.0   4597.7   \n",
       "153646              2016 Audi Q7 4M 3.0 TDI Quattro Wagon  59984.0   5998.4   \n",
       "158319  2016 Audi S3 8V MY17 Sportback 2.0 TFSI S Tron...  47996.0   4799.6   \n",
       "154858              2008 Audi S5 8T 4.2 FSI Quattro Coupe  37492.0   3749.2   \n",
       "157753           2016 Audi TT FV S 2.0 TFSI Quattro Coupe  61990.0   6199.0   \n",
       "158497                        2013 BMW 116i F20 Hatchback  18991.0   1899.1   \n",
       "158111         2016 BMW 118i F20 LCI Sport Line Hatchback  27988.0   2798.8   \n",
       "157859                2017 BMW 118i F40 M Sport Hatchback  30977.0   3097.7   \n",
       "157677                            2014 BMW 220d F22 Coupe  26977.0   2697.7   \n",
       "158478           2015 BMW 220i F23 Sport Line Convertible  31921.0   3192.1   \n",
       "158189                2017 BMW 330i F30 LCI M Sport Sedan  43988.0   4398.8   \n",
       "158141                 2013 BMW 428i F32 Sport Line Coupe  35999.0   3599.9   \n",
       "158875           2017 BMW 530i G30 MY17 Luxury Line Sedan  65492.0   6549.2   \n",
       "159465                  2014 BMW M135i F20 MY14 Hatchback  31985.0   3198.5   \n",
       "154970         2016 BMW M4 F82 LCI MY17 Competition Coupe  79992.0   7999.2   \n",
       "158509              2019 BMW X1 F48 MY19 xDrive 25I Wagon  54988.0   5498.8   \n",
       "158525               2015 BMW X3 F25 MY15 xDrive20d Wagon  38895.0   3889.5   \n",
       "156654       2017 BMW X3 F25 MY17 Update xDrive 20I Wagon  39988.0   3998.8   \n",
       "155117                    2018 BMW X3 G01 xDrive30I Wagon  61492.0   6149.2   \n",
       "158203              2017 BMW X4 F26 MY16 xDrive 20D Coupe  61988.0   6198.8   \n",
       "156759                         2019 BMW X4 G02 M40I Wagon  91977.0   9197.7   \n",
       "\n",
       "        loan amount  seller monthly repayment  \n",
       "id                                             \n",
       "155601      23389.2                495.112031  \n",
       "155602      19249.2                407.474839  \n",
       "159551      19791.0                418.943880  \n",
       "159571      35989.2                761.833919  \n",
       "158585      29690.1                628.492027  \n",
       "159540      35086.5                742.725201  \n",
       "159379      59382.9               1257.041208  \n",
       "158349      38610.0                817.312072  \n",
       "158414      55789.2               1180.968315  \n",
       "158871      35989.2                761.833919  \n",
       "159374      23395.5                495.245392  \n",
       "158259      36346.5                769.397390  \n",
       "156623      49489.2               1047.607371  \n",
       "159041      45889.2                971.401117  \n",
       "158222      59396.4               1257.326982  \n",
       "156668      41379.3                875.933733  \n",
       "153646      53985.6               1142.788982  \n",
       "158319      43196.4                914.398839  \n",
       "154858      33742.8                714.281217  \n",
       "157753      55791.0               1181.006418  \n",
       "158497      17091.9                361.808242  \n",
       "158111      25189.2                533.215158  \n",
       "157859      27879.3                590.160281  \n",
       "157677      24279.3                513.954027  \n",
       "158478      28728.9                608.144957  \n",
       "158189      39589.2                838.040173  \n",
       "158141      32399.1                685.837233  \n",
       "158875      58942.8               1247.724994  \n",
       "159465      28786.5                609.364257  \n",
       "154970      71992.8               1523.972664  \n",
       "158509      49489.2               1047.607371  \n",
       "158525      35005.5                741.010560  \n",
       "156654      35989.2                761.833919  \n",
       "155117      55342.8               1171.518740  \n",
       "158203      55789.2               1180.968315  \n",
       "156759      82779.3               1752.305652  "
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#input the calculated repayments into the DataFrame Column\n",
    "vehicle_df[\"seller monthly repayment\"] = (loan_amount)*((r)*((1+r)**n))/(((1+r)**n)-1)\n",
    "vehicle_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "#creating the financial institutions DataFrame\n",
    "finance_dict = {'finance_name':finance_name,\n",
    "               'interest_rate':i_rate}\n",
    "finance_df = pd.DataFrame(finance_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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>finance_name</th>\n",
       "      <th>interest_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Plenti Car Loan</td>\n",
       "      <td>5.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Loans.com.au - New and Dealer Used Car Loan</td>\n",
       "      <td>5.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Credit Concierge Car Loan</td>\n",
       "      <td>4.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Wisr Secured Car Loan</td>\n",
       "      <td>4.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Stratton Finance New Car Loan</td>\n",
       "      <td>4.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>IMB New Car Loan</td>\n",
       "      <td>5.34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>RACV New Car Loans</td>\n",
       "      <td>5.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>NRMA New Car Loan</td>\n",
       "      <td>5.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>NAB Personal Loan Unsecured Fixed</td>\n",
       "      <td>7.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>OurMoneyMarket Car Loan</td>\n",
       "      <td>5.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>SocietyOne Car Loan</td>\n",
       "      <td>5.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>NRMA Used Car Loan</td>\n",
       "      <td>6.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Symple Loans Personal Loan</td>\n",
       "      <td>6.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>RACV Used Car Loans</td>\n",
       "      <td>6.19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Macquarie Car Loan (For</td>\n",
       "      <td>8.47</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   finance_name interest_rate\n",
       "0                               Plenti Car Loan          5.44\n",
       "1   Loans.com.au - New and Dealer Used Car Loan          5.22\n",
       "2                     Credit Concierge Car Loan          4.41\n",
       "3                         Wisr Secured Car Loan          4.75\n",
       "4                 Stratton Finance New Car Loan          4.05\n",
       "5                              IMB New Car Loan          5.34\n",
       "6                            RACV New Car Loans          5.69\n",
       "7                             NRMA New Car Loan          5.69\n",
       "8            NAB Personal Loan Unsecured Fixed           7.91\n",
       "9                       OurMoneyMarket Car Loan          5.65\n",
       "10                          SocietyOne Car Loan          5.95\n",
       "11                           NRMA Used Car Loan          6.94\n",
       "12                   Symple Loans Personal Loan          6.47\n",
       "13                          RACV Used Car Loans          6.19\n",
       "14                     Macquarie Car Loan (For           8.47"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "finance_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "# to remove the unwanted symbol in the data\n",
    "finance_df['finance_name'] = finance_df['finance_name'].apply(lambda x: x.replace(\"(\", \"\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "# to remove \"New\" car loans provider from the list \n",
    "substring = \"New\"\n",
    "substring_2 = \"Used\" #this is in place as we do not want to get rid of provider who does New & Used car loans\n",
    "for s in finance_df['finance_name']:\n",
    "    if (substring in s) & (not(substring_2 in s)):\n",
    "        finance_df = finance_df.drop(index=(finance_df.loc[finance_df['finance_name']== s].index))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0                                 Plenti Car Loan\n",
      "1     Loans.com.au - New and Dealer Used Car Loan\n",
      "2                       Credit Concierge Car Loan\n",
      "3                           Wisr Secured Car Loan\n",
      "8              NAB Personal Loan Unsecured Fixed \n",
      "9                         OurMoneyMarket Car Loan\n",
      "10                            SocietyOne Car Loan\n",
      "11                             NRMA Used Car Loan\n",
      "12                     Symple Loans Personal Loan\n",
      "13                            RACV Used Car Loans\n",
      "14                        Macquarie Car Loan For \n",
      "Name: finance_name, dtype: object\n"
     ]
    }
   ],
   "source": [
    "#quality checks\n",
    "print(finance_df['finance_name'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "# setting column finance_name as index as it is unique\n",
    "finance_df = finance_df.set_index('finance_name')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "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>finance_name</th>\n",
       "      <th>Plenti Car Loan</th>\n",
       "      <th>Loans.com.au - New and Dealer Used Car Loan</th>\n",
       "      <th>Credit Concierge Car Loan</th>\n",
       "      <th>Wisr Secured Car Loan</th>\n",
       "      <th>NAB Personal Loan Unsecured Fixed</th>\n",
       "      <th>OurMoneyMarket Car Loan</th>\n",
       "      <th>SocietyOne Car Loan</th>\n",
       "      <th>NRMA Used Car Loan</th>\n",
       "      <th>Symple Loans Personal Loan</th>\n",
       "      <th>RACV Used Car Loans</th>\n",
       "      <th>Macquarie Car Loan For</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>interest_rate</th>\n",
       "      <td>5.44</td>\n",
       "      <td>5.22</td>\n",
       "      <td>4.41</td>\n",
       "      <td>4.75</td>\n",
       "      <td>7.91</td>\n",
       "      <td>5.65</td>\n",
       "      <td>5.95</td>\n",
       "      <td>6.94</td>\n",
       "      <td>6.47</td>\n",
       "      <td>6.19</td>\n",
       "      <td>8.47</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "finance_name  Plenti Car Loan Loans.com.au - New and Dealer Used Car Loan  \\\n",
       "interest_rate            5.44                                        5.22   \n",
       "\n",
       "finance_name  Credit Concierge Car Loan Wisr Secured Car Loan  \\\n",
       "interest_rate                      4.41                  4.75   \n",
       "\n",
       "finance_name  NAB Personal Loan Unsecured Fixed  OurMoneyMarket Car Loan  \\\n",
       "interest_rate                               7.91                    5.65   \n",
       "\n",
       "finance_name  SocietyOne Car Loan NRMA Used Car Loan  \\\n",
       "interest_rate                5.95               6.94   \n",
       "\n",
       "finance_name  Symple Loans Personal Loan RACV Used Car Loans  \\\n",
       "interest_rate                       6.47                6.19   \n",
       "\n",
       "finance_name  Macquarie Car Loan For   \n",
       "interest_rate                    8.47  "
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#transpose in order to concatenate with vehicle_df\n",
    "finance_df= finance_df.T\n",
    "finance_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "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>name</th>\n",
       "      <th>price</th>\n",
       "      <th>deposit</th>\n",
       "      <th>loan amount</th>\n",
       "      <th>seller monthly repayment</th>\n",
       "      <th>Plenti Car Loan</th>\n",
       "      <th>Loans.com.au - New and Dealer Used Car Loan</th>\n",
       "      <th>Credit Concierge Car Loan</th>\n",
       "      <th>Wisr Secured Car Loan</th>\n",
       "      <th>NAB Personal Loan Unsecured Fixed</th>\n",
       "      <th>OurMoneyMarket Car Loan</th>\n",
       "      <th>SocietyOne Car Loan</th>\n",
       "      <th>NRMA Used Car Loan</th>\n",
       "      <th>Symple Loans Personal Loan</th>\n",
       "      <th>RACV Used Car Loans</th>\n",
       "      <th>Macquarie Car Loan For</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>155601</th>\n",
       "      <td>2016 Audi A3 8V MY17 1.6 TDI Sportback Hatchback</td>\n",
       "      <td>25988.0</td>\n",
       "      <td>2598.8</td>\n",
       "      <td>23389.2</td>\n",
       "      <td>495.112031</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155602</th>\n",
       "      <td>2016 Audi A3 8V MY16 Sportback 1.6 TDI Attract...</td>\n",
       "      <td>21388.0</td>\n",
       "      <td>2138.8</td>\n",
       "      <td>19249.2</td>\n",
       "      <td>407.474839</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159551</th>\n",
       "      <td>2014 Audi A3 8V MY14 1.8 TFSI Ambition Sedan</td>\n",
       "      <td>21990.0</td>\n",
       "      <td>2199.0</td>\n",
       "      <td>19791.0</td>\n",
       "      <td>418.943880</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159571</th>\n",
       "      <td>2017 Audi A3 8V MY17 2.0 TFSI S Tronic Sport C...</td>\n",
       "      <td>39988.0</td>\n",
       "      <td>3998.8</td>\n",
       "      <td>35989.2</td>\n",
       "      <td>761.833919</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158585</th>\n",
       "      <td>2016 Audi A4 F4 (B9) 2.0 TFSI S Tronic Sport S...</td>\n",
       "      <td>32989.0</td>\n",
       "      <td>3298.9</td>\n",
       "      <td>29690.1</td>\n",
       "      <td>628.492027</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159540</th>\n",
       "      <td>2017 Audi A4 F4 MY17 (B9) 2.0 TFSI S Tronic Sp...</td>\n",
       "      <td>38985.0</td>\n",
       "      <td>3898.5</td>\n",
       "      <td>35086.5</td>\n",
       "      <td>742.725201</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159379</th>\n",
       "      <td>2018 Audi A5 F5 MY18 2.0 TFSI Quattro S Tronic...</td>\n",
       "      <td>65981.0</td>\n",
       "      <td>6598.1</td>\n",
       "      <td>59382.9</td>\n",
       "      <td>1257.041208</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158349</th>\n",
       "      <td>2018 Audi Q2 GA MY18 1.4 TFSI Design Wagon</td>\n",
       "      <td>42900.0</td>\n",
       "      <td>4290.0</td>\n",
       "      <td>38610.0</td>\n",
       "      <td>817.312072</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158414</th>\n",
       "      <td>2020 Audi Q3 F3 MY21 35 TFSI S Line Sportback</td>\n",
       "      <td>61988.0</td>\n",
       "      <td>6198.8</td>\n",
       "      <td>55789.2</td>\n",
       "      <td>1180.968315</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158871</th>\n",
       "      <td>2018 Audi Q3 8U MY18 1.4 TFSI (110kW) Wagon</td>\n",
       "      <td>39988.0</td>\n",
       "      <td>3998.8</td>\n",
       "      <td>35989.2</td>\n",
       "      <td>761.833919</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159374</th>\n",
       "      <td>2013 Audi Q3 8U 2.0 TFSI Quattro (125kW) Wagon</td>\n",
       "      <td>25995.0</td>\n",
       "      <td>2599.5</td>\n",
       "      <td>23395.5</td>\n",
       "      <td>495.245392</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158259</th>\n",
       "      <td>2016 Audi Q5 8R MY16 2.0 TDI Quattro Wagon</td>\n",
       "      <td>40385.0</td>\n",
       "      <td>4038.5</td>\n",
       "      <td>36346.5</td>\n",
       "      <td>769.397390</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156623</th>\n",
       "      <td>2017 Audi Q5 FY MY18 2.0 TFSI Quattro Sport Wagon</td>\n",
       "      <td>54988.0</td>\n",
       "      <td>5498.8</td>\n",
       "      <td>49489.2</td>\n",
       "      <td>1047.607371</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159041</th>\n",
       "      <td>2017 Audi Q5 FY MY17 2.0 TDI Quattro Design Wagon</td>\n",
       "      <td>50988.0</td>\n",
       "      <td>5098.8</td>\n",
       "      <td>45889.2</td>\n",
       "      <td>971.401117</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158222</th>\n",
       "      <td>2019 Audi Q5 FY MY19 40 TDI Quattro Sport Wagon</td>\n",
       "      <td>65996.0</td>\n",
       "      <td>6599.6</td>\n",
       "      <td>59396.4</td>\n",
       "      <td>1257.326982</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156668</th>\n",
       "      <td>2017 Audi Q5 FY MY17 2.0 TDI Quattro Design Wagon</td>\n",
       "      <td>45977.0</td>\n",
       "      <td>4597.7</td>\n",
       "      <td>41379.3</td>\n",
       "      <td>875.933733</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>153646</th>\n",
       "      <td>2016 Audi Q7 4M 3.0 TDI Quattro Wagon</td>\n",
       "      <td>59984.0</td>\n",
       "      <td>5998.4</td>\n",
       "      <td>53985.6</td>\n",
       "      <td>1142.788982</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158319</th>\n",
       "      <td>2016 Audi S3 8V MY17 Sportback 2.0 TFSI S Tron...</td>\n",
       "      <td>47996.0</td>\n",
       "      <td>4799.6</td>\n",
       "      <td>43196.4</td>\n",
       "      <td>914.398839</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154858</th>\n",
       "      <td>2008 Audi S5 8T 4.2 FSI Quattro Coupe</td>\n",
       "      <td>37492.0</td>\n",
       "      <td>3749.2</td>\n",
       "      <td>33742.8</td>\n",
       "      <td>714.281217</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157753</th>\n",
       "      <td>2016 Audi TT FV S 2.0 TFSI Quattro Coupe</td>\n",
       "      <td>61990.0</td>\n",
       "      <td>6199.0</td>\n",
       "      <td>55791.0</td>\n",
       "      <td>1181.006418</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158497</th>\n",
       "      <td>2013 BMW 116i F20 Hatchback</td>\n",
       "      <td>18991.0</td>\n",
       "      <td>1899.1</td>\n",
       "      <td>17091.9</td>\n",
       "      <td>361.808242</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158111</th>\n",
       "      <td>2016 BMW 118i F20 LCI Sport Line Hatchback</td>\n",
       "      <td>27988.0</td>\n",
       "      <td>2798.8</td>\n",
       "      <td>25189.2</td>\n",
       "      <td>533.215158</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157859</th>\n",
       "      <td>2017 BMW 118i F40 M Sport Hatchback</td>\n",
       "      <td>30977.0</td>\n",
       "      <td>3097.7</td>\n",
       "      <td>27879.3</td>\n",
       "      <td>590.160281</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157677</th>\n",
       "      <td>2014 BMW 220d F22 Coupe</td>\n",
       "      <td>26977.0</td>\n",
       "      <td>2697.7</td>\n",
       "      <td>24279.3</td>\n",
       "      <td>513.954027</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158478</th>\n",
       "      <td>2015 BMW 220i F23 Sport Line Convertible</td>\n",
       "      <td>31921.0</td>\n",
       "      <td>3192.1</td>\n",
       "      <td>28728.9</td>\n",
       "      <td>608.144957</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158189</th>\n",
       "      <td>2017 BMW 330i F30 LCI M Sport Sedan</td>\n",
       "      <td>43988.0</td>\n",
       "      <td>4398.8</td>\n",
       "      <td>39589.2</td>\n",
       "      <td>838.040173</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158141</th>\n",
       "      <td>2013 BMW 428i F32 Sport Line Coupe</td>\n",
       "      <td>35999.0</td>\n",
       "      <td>3599.9</td>\n",
       "      <td>32399.1</td>\n",
       "      <td>685.837233</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158875</th>\n",
       "      <td>2017 BMW 530i G30 MY17 Luxury Line Sedan</td>\n",
       "      <td>65492.0</td>\n",
       "      <td>6549.2</td>\n",
       "      <td>58942.8</td>\n",
       "      <td>1247.724994</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159465</th>\n",
       "      <td>2014 BMW M135i F20 MY14 Hatchback</td>\n",
       "      <td>31985.0</td>\n",
       "      <td>3198.5</td>\n",
       "      <td>28786.5</td>\n",
       "      <td>609.364257</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154970</th>\n",
       "      <td>2016 BMW M4 F82 LCI MY17 Competition Coupe</td>\n",
       "      <td>79992.0</td>\n",
       "      <td>7999.2</td>\n",
       "      <td>71992.8</td>\n",
       "      <td>1523.972664</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158509</th>\n",
       "      <td>2019 BMW X1 F48 MY19 xDrive 25I Wagon</td>\n",
       "      <td>54988.0</td>\n",
       "      <td>5498.8</td>\n",
       "      <td>49489.2</td>\n",
       "      <td>1047.607371</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158525</th>\n",
       "      <td>2015 BMW X3 F25 MY15 xDrive20d Wagon</td>\n",
       "      <td>38895.0</td>\n",
       "      <td>3889.5</td>\n",
       "      <td>35005.5</td>\n",
       "      <td>741.010560</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156654</th>\n",
       "      <td>2017 BMW X3 F25 MY17 Update xDrive 20I Wagon</td>\n",
       "      <td>39988.0</td>\n",
       "      <td>3998.8</td>\n",
       "      <td>35989.2</td>\n",
       "      <td>761.833919</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155117</th>\n",
       "      <td>2018 BMW X3 G01 xDrive30I Wagon</td>\n",
       "      <td>61492.0</td>\n",
       "      <td>6149.2</td>\n",
       "      <td>55342.8</td>\n",
       "      <td>1171.518740</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158203</th>\n",
       "      <td>2017 BMW X4 F26 MY16 xDrive 20D Coupe</td>\n",
       "      <td>61988.0</td>\n",
       "      <td>6198.8</td>\n",
       "      <td>55789.2</td>\n",
       "      <td>1180.968315</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156759</th>\n",
       "      <td>2019 BMW X4 G02 M40I Wagon</td>\n",
       "      <td>91977.0</td>\n",
       "      <td>9197.7</td>\n",
       "      <td>82779.3</td>\n",
       "      <td>1752.305652</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>interest_rate</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.44</td>\n",
       "      <td>5.22</td>\n",
       "      <td>4.41</td>\n",
       "      <td>4.75</td>\n",
       "      <td>7.91</td>\n",
       "      <td>5.65</td>\n",
       "      <td>5.95</td>\n",
       "      <td>6.94</td>\n",
       "      <td>6.47</td>\n",
       "      <td>6.19</td>\n",
       "      <td>8.47</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                            name    price  \\\n",
       "155601          2016 Audi A3 8V MY17 1.6 TDI Sportback Hatchback  25988.0   \n",
       "155602         2016 Audi A3 8V MY16 Sportback 1.6 TDI Attract...  21388.0   \n",
       "159551              2014 Audi A3 8V MY14 1.8 TFSI Ambition Sedan  21990.0   \n",
       "159571         2017 Audi A3 8V MY17 2.0 TFSI S Tronic Sport C...  39988.0   \n",
       "158585         2016 Audi A4 F4 (B9) 2.0 TFSI S Tronic Sport S...  32989.0   \n",
       "159540         2017 Audi A4 F4 MY17 (B9) 2.0 TFSI S Tronic Sp...  38985.0   \n",
       "159379         2018 Audi A5 F5 MY18 2.0 TFSI Quattro S Tronic...  65981.0   \n",
       "158349                2018 Audi Q2 GA MY18 1.4 TFSI Design Wagon  42900.0   \n",
       "158414             2020 Audi Q3 F3 MY21 35 TFSI S Line Sportback  61988.0   \n",
       "158871               2018 Audi Q3 8U MY18 1.4 TFSI (110kW) Wagon  39988.0   \n",
       "159374            2013 Audi Q3 8U 2.0 TFSI Quattro (125kW) Wagon  25995.0   \n",
       "158259                2016 Audi Q5 8R MY16 2.0 TDI Quattro Wagon  40385.0   \n",
       "156623         2017 Audi Q5 FY MY18 2.0 TFSI Quattro Sport Wagon  54988.0   \n",
       "159041         2017 Audi Q5 FY MY17 2.0 TDI Quattro Design Wagon  50988.0   \n",
       "158222           2019 Audi Q5 FY MY19 40 TDI Quattro Sport Wagon  65996.0   \n",
       "156668         2017 Audi Q5 FY MY17 2.0 TDI Quattro Design Wagon  45977.0   \n",
       "153646                     2016 Audi Q7 4M 3.0 TDI Quattro Wagon  59984.0   \n",
       "158319         2016 Audi S3 8V MY17 Sportback 2.0 TFSI S Tron...  47996.0   \n",
       "154858                     2008 Audi S5 8T 4.2 FSI Quattro Coupe  37492.0   \n",
       "157753                  2016 Audi TT FV S 2.0 TFSI Quattro Coupe  61990.0   \n",
       "158497                               2013 BMW 116i F20 Hatchback  18991.0   \n",
       "158111                2016 BMW 118i F20 LCI Sport Line Hatchback  27988.0   \n",
       "157859                       2017 BMW 118i F40 M Sport Hatchback  30977.0   \n",
       "157677                                   2014 BMW 220d F22 Coupe  26977.0   \n",
       "158478                  2015 BMW 220i F23 Sport Line Convertible  31921.0   \n",
       "158189                       2017 BMW 330i F30 LCI M Sport Sedan  43988.0   \n",
       "158141                        2013 BMW 428i F32 Sport Line Coupe  35999.0   \n",
       "158875                  2017 BMW 530i G30 MY17 Luxury Line Sedan  65492.0   \n",
       "159465                         2014 BMW M135i F20 MY14 Hatchback  31985.0   \n",
       "154970                2016 BMW M4 F82 LCI MY17 Competition Coupe  79992.0   \n",
       "158509                     2019 BMW X1 F48 MY19 xDrive 25I Wagon  54988.0   \n",
       "158525                      2015 BMW X3 F25 MY15 xDrive20d Wagon  38895.0   \n",
       "156654              2017 BMW X3 F25 MY17 Update xDrive 20I Wagon  39988.0   \n",
       "155117                           2018 BMW X3 G01 xDrive30I Wagon  61492.0   \n",
       "158203                     2017 BMW X4 F26 MY16 xDrive 20D Coupe  61988.0   \n",
       "156759                                2019 BMW X4 G02 M40I Wagon  91977.0   \n",
       "interest_rate                                                NaN      NaN   \n",
       "\n",
       "               deposit  loan amount  seller monthly repayment Plenti Car Loan  \\\n",
       "155601          2598.8      23389.2                495.112031             NaN   \n",
       "155602          2138.8      19249.2                407.474839             NaN   \n",
       "159551          2199.0      19791.0                418.943880             NaN   \n",
       "159571          3998.8      35989.2                761.833919             NaN   \n",
       "158585          3298.9      29690.1                628.492027             NaN   \n",
       "159540          3898.5      35086.5                742.725201             NaN   \n",
       "159379          6598.1      59382.9               1257.041208             NaN   \n",
       "158349          4290.0      38610.0                817.312072             NaN   \n",
       "158414          6198.8      55789.2               1180.968315             NaN   \n",
       "158871          3998.8      35989.2                761.833919             NaN   \n",
       "159374          2599.5      23395.5                495.245392             NaN   \n",
       "158259          4038.5      36346.5                769.397390             NaN   \n",
       "156623          5498.8      49489.2               1047.607371             NaN   \n",
       "159041          5098.8      45889.2                971.401117             NaN   \n",
       "158222          6599.6      59396.4               1257.326982             NaN   \n",
       "156668          4597.7      41379.3                875.933733             NaN   \n",
       "153646          5998.4      53985.6               1142.788982             NaN   \n",
       "158319          4799.6      43196.4                914.398839             NaN   \n",
       "154858          3749.2      33742.8                714.281217             NaN   \n",
       "157753          6199.0      55791.0               1181.006418             NaN   \n",
       "158497          1899.1      17091.9                361.808242             NaN   \n",
       "158111          2798.8      25189.2                533.215158             NaN   \n",
       "157859          3097.7      27879.3                590.160281             NaN   \n",
       "157677          2697.7      24279.3                513.954027             NaN   \n",
       "158478          3192.1      28728.9                608.144957             NaN   \n",
       "158189          4398.8      39589.2                838.040173             NaN   \n",
       "158141          3599.9      32399.1                685.837233             NaN   \n",
       "158875          6549.2      58942.8               1247.724994             NaN   \n",
       "159465          3198.5      28786.5                609.364257             NaN   \n",
       "154970          7999.2      71992.8               1523.972664             NaN   \n",
       "158509          5498.8      49489.2               1047.607371             NaN   \n",
       "158525          3889.5      35005.5                741.010560             NaN   \n",
       "156654          3998.8      35989.2                761.833919             NaN   \n",
       "155117          6149.2      55342.8               1171.518740             NaN   \n",
       "158203          6198.8      55789.2               1180.968315             NaN   \n",
       "156759          9197.7      82779.3               1752.305652             NaN   \n",
       "interest_rate      NaN          NaN                       NaN            5.44   \n",
       "\n",
       "              Loans.com.au - New and Dealer Used Car Loan  \\\n",
       "155601                                                NaN   \n",
       "155602                                                NaN   \n",
       "159551                                                NaN   \n",
       "159571                                                NaN   \n",
       "158585                                                NaN   \n",
       "159540                                                NaN   \n",
       "159379                                                NaN   \n",
       "158349                                                NaN   \n",
       "158414                                                NaN   \n",
       "158871                                                NaN   \n",
       "159374                                                NaN   \n",
       "158259                                                NaN   \n",
       "156623                                                NaN   \n",
       "159041                                                NaN   \n",
       "158222                                                NaN   \n",
       "156668                                                NaN   \n",
       "153646                                                NaN   \n",
       "158319                                                NaN   \n",
       "154858                                                NaN   \n",
       "157753                                                NaN   \n",
       "158497                                                NaN   \n",
       "158111                                                NaN   \n",
       "157859                                                NaN   \n",
       "157677                                                NaN   \n",
       "158478                                                NaN   \n",
       "158189                                                NaN   \n",
       "158141                                                NaN   \n",
       "158875                                                NaN   \n",
       "159465                                                NaN   \n",
       "154970                                                NaN   \n",
       "158509                                                NaN   \n",
       "158525                                                NaN   \n",
       "156654                                                NaN   \n",
       "155117                                                NaN   \n",
       "158203                                                NaN   \n",
       "156759                                                NaN   \n",
       "interest_rate                                        5.22   \n",
       "\n",
       "              Credit Concierge Car Loan Wisr Secured Car Loan  \\\n",
       "155601                              NaN                   NaN   \n",
       "155602                              NaN                   NaN   \n",
       "159551                              NaN                   NaN   \n",
       "159571                              NaN                   NaN   \n",
       "158585                              NaN                   NaN   \n",
       "159540                              NaN                   NaN   \n",
       "159379                              NaN                   NaN   \n",
       "158349                              NaN                   NaN   \n",
       "158414                              NaN                   NaN   \n",
       "158871                              NaN                   NaN   \n",
       "159374                              NaN                   NaN   \n",
       "158259                              NaN                   NaN   \n",
       "156623                              NaN                   NaN   \n",
       "159041                              NaN                   NaN   \n",
       "158222                              NaN                   NaN   \n",
       "156668                              NaN                   NaN   \n",
       "153646                              NaN                   NaN   \n",
       "158319                              NaN                   NaN   \n",
       "154858                              NaN                   NaN   \n",
       "157753                              NaN                   NaN   \n",
       "158497                              NaN                   NaN   \n",
       "158111                              NaN                   NaN   \n",
       "157859                              NaN                   NaN   \n",
       "157677                              NaN                   NaN   \n",
       "158478                              NaN                   NaN   \n",
       "158189                              NaN                   NaN   \n",
       "158141                              NaN                   NaN   \n",
       "158875                              NaN                   NaN   \n",
       "159465                              NaN                   NaN   \n",
       "154970                              NaN                   NaN   \n",
       "158509                              NaN                   NaN   \n",
       "158525                              NaN                   NaN   \n",
       "156654                              NaN                   NaN   \n",
       "155117                              NaN                   NaN   \n",
       "158203                              NaN                   NaN   \n",
       "156759                              NaN                   NaN   \n",
       "interest_rate                      4.41                  4.75   \n",
       "\n",
       "              NAB Personal Loan Unsecured Fixed  OurMoneyMarket Car Loan  \\\n",
       "155601                                       NaN                     NaN   \n",
       "155602                                       NaN                     NaN   \n",
       "159551                                       NaN                     NaN   \n",
       "159571                                       NaN                     NaN   \n",
       "158585                                       NaN                     NaN   \n",
       "159540                                       NaN                     NaN   \n",
       "159379                                       NaN                     NaN   \n",
       "158349                                       NaN                     NaN   \n",
       "158414                                       NaN                     NaN   \n",
       "158871                                       NaN                     NaN   \n",
       "159374                                       NaN                     NaN   \n",
       "158259                                       NaN                     NaN   \n",
       "156623                                       NaN                     NaN   \n",
       "159041                                       NaN                     NaN   \n",
       "158222                                       NaN                     NaN   \n",
       "156668                                       NaN                     NaN   \n",
       "153646                                       NaN                     NaN   \n",
       "158319                                       NaN                     NaN   \n",
       "154858                                       NaN                     NaN   \n",
       "157753                                       NaN                     NaN   \n",
       "158497                                       NaN                     NaN   \n",
       "158111                                       NaN                     NaN   \n",
       "157859                                       NaN                     NaN   \n",
       "157677                                       NaN                     NaN   \n",
       "158478                                       NaN                     NaN   \n",
       "158189                                       NaN                     NaN   \n",
       "158141                                       NaN                     NaN   \n",
       "158875                                       NaN                     NaN   \n",
       "159465                                       NaN                     NaN   \n",
       "154970                                       NaN                     NaN   \n",
       "158509                                       NaN                     NaN   \n",
       "158525                                       NaN                     NaN   \n",
       "156654                                       NaN                     NaN   \n",
       "155117                                       NaN                     NaN   \n",
       "158203                                       NaN                     NaN   \n",
       "156759                                       NaN                     NaN   \n",
       "interest_rate                               7.91                    5.65   \n",
       "\n",
       "              SocietyOne Car Loan NRMA Used Car Loan  \\\n",
       "155601                        NaN                NaN   \n",
       "155602                        NaN                NaN   \n",
       "159551                        NaN                NaN   \n",
       "159571                        NaN                NaN   \n",
       "158585                        NaN                NaN   \n",
       "159540                        NaN                NaN   \n",
       "159379                        NaN                NaN   \n",
       "158349                        NaN                NaN   \n",
       "158414                        NaN                NaN   \n",
       "158871                        NaN                NaN   \n",
       "159374                        NaN                NaN   \n",
       "158259                        NaN                NaN   \n",
       "156623                        NaN                NaN   \n",
       "159041                        NaN                NaN   \n",
       "158222                        NaN                NaN   \n",
       "156668                        NaN                NaN   \n",
       "153646                        NaN                NaN   \n",
       "158319                        NaN                NaN   \n",
       "154858                        NaN                NaN   \n",
       "157753                        NaN                NaN   \n",
       "158497                        NaN                NaN   \n",
       "158111                        NaN                NaN   \n",
       "157859                        NaN                NaN   \n",
       "157677                        NaN                NaN   \n",
       "158478                        NaN                NaN   \n",
       "158189                        NaN                NaN   \n",
       "158141                        NaN                NaN   \n",
       "158875                        NaN                NaN   \n",
       "159465                        NaN                NaN   \n",
       "154970                        NaN                NaN   \n",
       "158509                        NaN                NaN   \n",
       "158525                        NaN                NaN   \n",
       "156654                        NaN                NaN   \n",
       "155117                        NaN                NaN   \n",
       "158203                        NaN                NaN   \n",
       "156759                        NaN                NaN   \n",
       "interest_rate                5.95               6.94   \n",
       "\n",
       "              Symple Loans Personal Loan RACV Used Car Loans  \\\n",
       "155601                               NaN                 NaN   \n",
       "155602                               NaN                 NaN   \n",
       "159551                               NaN                 NaN   \n",
       "159571                               NaN                 NaN   \n",
       "158585                               NaN                 NaN   \n",
       "159540                               NaN                 NaN   \n",
       "159379                               NaN                 NaN   \n",
       "158349                               NaN                 NaN   \n",
       "158414                               NaN                 NaN   \n",
       "158871                               NaN                 NaN   \n",
       "159374                               NaN                 NaN   \n",
       "158259                               NaN                 NaN   \n",
       "156623                               NaN                 NaN   \n",
       "159041                               NaN                 NaN   \n",
       "158222                               NaN                 NaN   \n",
       "156668                               NaN                 NaN   \n",
       "153646                               NaN                 NaN   \n",
       "158319                               NaN                 NaN   \n",
       "154858                               NaN                 NaN   \n",
       "157753                               NaN                 NaN   \n",
       "158497                               NaN                 NaN   \n",
       "158111                               NaN                 NaN   \n",
       "157859                               NaN                 NaN   \n",
       "157677                               NaN                 NaN   \n",
       "158478                               NaN                 NaN   \n",
       "158189                               NaN                 NaN   \n",
       "158141                               NaN                 NaN   \n",
       "158875                               NaN                 NaN   \n",
       "159465                               NaN                 NaN   \n",
       "154970                               NaN                 NaN   \n",
       "158509                               NaN                 NaN   \n",
       "158525                               NaN                 NaN   \n",
       "156654                               NaN                 NaN   \n",
       "155117                               NaN                 NaN   \n",
       "158203                               NaN                 NaN   \n",
       "156759                               NaN                 NaN   \n",
       "interest_rate                       6.47                6.19   \n",
       "\n",
       "              Macquarie Car Loan For   \n",
       "155601                            NaN  \n",
       "155602                            NaN  \n",
       "159551                            NaN  \n",
       "159571                            NaN  \n",
       "158585                            NaN  \n",
       "159540                            NaN  \n",
       "159379                            NaN  \n",
       "158349                            NaN  \n",
       "158414                            NaN  \n",
       "158871                            NaN  \n",
       "159374                            NaN  \n",
       "158259                            NaN  \n",
       "156623                            NaN  \n",
       "159041                            NaN  \n",
       "158222                            NaN  \n",
       "156668                            NaN  \n",
       "153646                            NaN  \n",
       "158319                            NaN  \n",
       "154858                            NaN  \n",
       "157753                            NaN  \n",
       "158497                            NaN  \n",
       "158111                            NaN  \n",
       "157859                            NaN  \n",
       "157677                            NaN  \n",
       "158478                            NaN  \n",
       "158189                            NaN  \n",
       "158141                            NaN  \n",
       "158875                            NaN  \n",
       "159465                            NaN  \n",
       "154970                            NaN  \n",
       "158509                            NaN  \n",
       "158525                            NaN  \n",
       "156654                            NaN  \n",
       "155117                            NaN  \n",
       "158203                            NaN  \n",
       "156759                            NaN  \n",
       "interest_rate                    8.47  "
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#concatenate is used here as there are no similar columns to merge or join\n",
    "combined_df = pd.concat([vehicle_df,finance_df], axis=1)\n",
    "combined_df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'5.44'"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#quality checks\n",
    "combined_df.loc['interest_rate','Plenti Car Loan']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "# looping through the DataFrame to input calculated repayments on each financial institutions \n",
    "for i in range(5,16,1):\n",
    "    interest_rate = combined_df.loc['interest_rate', combined_df.columns[i]]\n",
    "#     print(f'interest_rate:{interest_rate}')\n",
    "\n",
    "    r= (float(interest_rate)/t)*0.01\n",
    "#     print(f'r:{r}')\n",
    "\n",
    "    for j in range(0,len(combined_df['name'])-1):\n",
    "        combined_df.iloc[j,i]=combined_df.iloc[j,3]*((r)*((1+r)**n))/(((1+r)**n)-1)\n",
    "#         print(combined_df.iloc[j,3])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "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>name</th>\n",
       "      <th>price</th>\n",
       "      <th>deposit</th>\n",
       "      <th>loan amount</th>\n",
       "      <th>seller monthly repayment</th>\n",
       "      <th>Plenti Car Loan</th>\n",
       "      <th>Loans.com.au - New and Dealer Used Car Loan</th>\n",
       "      <th>Credit Concierge Car Loan</th>\n",
       "      <th>Wisr Secured Car Loan</th>\n",
       "      <th>NAB Personal Loan Unsecured Fixed</th>\n",
       "      <th>OurMoneyMarket Car Loan</th>\n",
       "      <th>SocietyOne Car Loan</th>\n",
       "      <th>NRMA Used Car Loan</th>\n",
       "      <th>Symple Loans Personal Loan</th>\n",
       "      <th>RACV Used Car Loans</th>\n",
       "      <th>Macquarie Car Loan For</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>155601</th>\n",
       "      <td>2016 Audi A3 8V MY17 1.6 TDI Sportback Hatchback</td>\n",
       "      <td>25988.0</td>\n",
       "      <td>2598.8</td>\n",
       "      <td>23389.2</td>\n",
       "      <td>495.112031</td>\n",
       "      <td>446.113</td>\n",
       "      <td>443.744</td>\n",
       "      <td>435.089</td>\n",
       "      <td>438.709</td>\n",
       "      <td>473.242</td>\n",
       "      <td>448.382</td>\n",
       "      <td>451.635</td>\n",
       "      <td>462.472</td>\n",
       "      <td>457.308</td>\n",
       "      <td>454.248</td>\n",
       "      <td>479.527</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155602</th>\n",
       "      <td>2016 Audi A3 8V MY16 Sportback 1.6 TDI Attract...</td>\n",
       "      <td>21388.0</td>\n",
       "      <td>2138.8</td>\n",
       "      <td>19249.2</td>\n",
       "      <td>407.474839</td>\n",
       "      <td>367.149</td>\n",
       "      <td>365.2</td>\n",
       "      <td>358.076</td>\n",
       "      <td>361.056</td>\n",
       "      <td>389.476</td>\n",
       "      <td>369.016</td>\n",
       "      <td>371.694</td>\n",
       "      <td>380.613</td>\n",
       "      <td>376.362</td>\n",
       "      <td>373.844</td>\n",
       "      <td>394.649</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159551</th>\n",
       "      <td>2014 Audi A3 8V MY14 1.8 TFSI Ambition Sedan</td>\n",
       "      <td>21990.0</td>\n",
       "      <td>2199.0</td>\n",
       "      <td>19791.0</td>\n",
       "      <td>418.943880</td>\n",
       "      <td>377.483</td>\n",
       "      <td>375.479</td>\n",
       "      <td>368.155</td>\n",
       "      <td>371.218</td>\n",
       "      <td>400.438</td>\n",
       "      <td>379.403</td>\n",
       "      <td>382.156</td>\n",
       "      <td>391.326</td>\n",
       "      <td>386.956</td>\n",
       "      <td>384.366</td>\n",
       "      <td>405.757</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159571</th>\n",
       "      <td>2017 Audi A3 8V MY17 2.0 TFSI S Tronic Sport C...</td>\n",
       "      <td>39988.0</td>\n",
       "      <td>3998.8</td>\n",
       "      <td>35989.2</td>\n",
       "      <td>761.833919</td>\n",
       "      <td>686.439</td>\n",
       "      <td>682.794</td>\n",
       "      <td>669.476</td>\n",
       "      <td>675.046</td>\n",
       "      <td>728.182</td>\n",
       "      <td>689.93</td>\n",
       "      <td>694.936</td>\n",
       "      <td>711.611</td>\n",
       "      <td>703.664</td>\n",
       "      <td>698.956</td>\n",
       "      <td>737.853</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158585</th>\n",
       "      <td>2016 Audi A4 F4 (B9) 2.0 TFSI S Tronic Sport S...</td>\n",
       "      <td>32989.0</td>\n",
       "      <td>3298.9</td>\n",
       "      <td>29690.1</td>\n",
       "      <td>628.492027</td>\n",
       "      <td>566.294</td>\n",
       "      <td>563.286</td>\n",
       "      <td>552.299</td>\n",
       "      <td>556.895</td>\n",
       "      <td>600.73</td>\n",
       "      <td>569.173</td>\n",
       "      <td>573.303</td>\n",
       "      <td>587.059</td>\n",
       "      <td>580.504</td>\n",
       "      <td>576.62</td>\n",
       "      <td>608.709</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159540</th>\n",
       "      <td>2017 Audi A4 F4 MY17 (B9) 2.0 TFSI S Tronic Sp...</td>\n",
       "      <td>38985.0</td>\n",
       "      <td>3898.5</td>\n",
       "      <td>35086.5</td>\n",
       "      <td>742.725201</td>\n",
       "      <td>669.222</td>\n",
       "      <td>665.668</td>\n",
       "      <td>652.683</td>\n",
       "      <td>658.114</td>\n",
       "      <td>709.917</td>\n",
       "      <td>672.625</td>\n",
       "      <td>677.505</td>\n",
       "      <td>693.762</td>\n",
       "      <td>686.015</td>\n",
       "      <td>681.424</td>\n",
       "      <td>719.346</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159379</th>\n",
       "      <td>2018 Audi A5 F5 MY18 2.0 TFSI Quattro S Tronic...</td>\n",
       "      <td>65981.0</td>\n",
       "      <td>6598.1</td>\n",
       "      <td>59382.9</td>\n",
       "      <td>1257.041208</td>\n",
       "      <td>1132.64</td>\n",
       "      <td>1126.62</td>\n",
       "      <td>1104.65</td>\n",
       "      <td>1113.84</td>\n",
       "      <td>1201.51</td>\n",
       "      <td>1138.4</td>\n",
       "      <td>1146.66</td>\n",
       "      <td>1174.17</td>\n",
       "      <td>1161.06</td>\n",
       "      <td>1153.29</td>\n",
       "      <td>1217.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158349</th>\n",
       "      <td>2018 Audi Q2 GA MY18 1.4 TFSI Design Wagon</td>\n",
       "      <td>42900.0</td>\n",
       "      <td>4290.0</td>\n",
       "      <td>38610.0</td>\n",
       "      <td>817.312072</td>\n",
       "      <td>736.427</td>\n",
       "      <td>732.516</td>\n",
       "      <td>718.228</td>\n",
       "      <td>724.204</td>\n",
       "      <td>781.21</td>\n",
       "      <td>740.172</td>\n",
       "      <td>745.542</td>\n",
       "      <td>763.432</td>\n",
       "      <td>754.907</td>\n",
       "      <td>749.855</td>\n",
       "      <td>791.585</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158414</th>\n",
       "      <td>2020 Audi Q3 F3 MY21 35 TFSI S Line Sportback</td>\n",
       "      <td>61988.0</td>\n",
       "      <td>6198.8</td>\n",
       "      <td>55789.2</td>\n",
       "      <td>1180.968315</td>\n",
       "      <td>1064.09</td>\n",
       "      <td>1058.44</td>\n",
       "      <td>1037.8</td>\n",
       "      <td>1046.43</td>\n",
       "      <td>1128.8</td>\n",
       "      <td>1069.51</td>\n",
       "      <td>1077.26</td>\n",
       "      <td>1103.11</td>\n",
       "      <td>1090.8</td>\n",
       "      <td>1083.5</td>\n",
       "      <td>1143.79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158871</th>\n",
       "      <td>2018 Audi Q3 8U MY18 1.4 TFSI (110kW) Wagon</td>\n",
       "      <td>39988.0</td>\n",
       "      <td>3998.8</td>\n",
       "      <td>35989.2</td>\n",
       "      <td>761.833919</td>\n",
       "      <td>686.439</td>\n",
       "      <td>682.794</td>\n",
       "      <td>669.476</td>\n",
       "      <td>675.046</td>\n",
       "      <td>728.182</td>\n",
       "      <td>689.93</td>\n",
       "      <td>694.936</td>\n",
       "      <td>711.611</td>\n",
       "      <td>703.664</td>\n",
       "      <td>698.956</td>\n",
       "      <td>737.853</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159374</th>\n",
       "      <td>2013 Audi Q3 8U 2.0 TFSI Quattro (125kW) Wagon</td>\n",
       "      <td>25995.0</td>\n",
       "      <td>2599.5</td>\n",
       "      <td>23395.5</td>\n",
       "      <td>495.245392</td>\n",
       "      <td>446.234</td>\n",
       "      <td>443.864</td>\n",
       "      <td>435.206</td>\n",
       "      <td>438.827</td>\n",
       "      <td>473.369</td>\n",
       "      <td>448.503</td>\n",
       "      <td>451.757</td>\n",
       "      <td>462.597</td>\n",
       "      <td>457.431</td>\n",
       "      <td>454.37</td>\n",
       "      <td>479.656</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158259</th>\n",
       "      <td>2016 Audi Q5 8R MY16 2.0 TDI Quattro Wagon</td>\n",
       "      <td>40385.0</td>\n",
       "      <td>4038.5</td>\n",
       "      <td>36346.5</td>\n",
       "      <td>769.397390</td>\n",
       "      <td>693.254</td>\n",
       "      <td>689.573</td>\n",
       "      <td>676.122</td>\n",
       "      <td>681.748</td>\n",
       "      <td>735.411</td>\n",
       "      <td>696.78</td>\n",
       "      <td>701.835</td>\n",
       "      <td>718.676</td>\n",
       "      <td>710.65</td>\n",
       "      <td>705.895</td>\n",
       "      <td>745.179</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156623</th>\n",
       "      <td>2017 Audi Q5 FY MY18 2.0 TFSI Quattro Sport Wagon</td>\n",
       "      <td>54988.0</td>\n",
       "      <td>5498.8</td>\n",
       "      <td>49489.2</td>\n",
       "      <td>1047.607371</td>\n",
       "      <td>943.931</td>\n",
       "      <td>938.919</td>\n",
       "      <td>920.604</td>\n",
       "      <td>928.265</td>\n",
       "      <td>1001.33</td>\n",
       "      <td>948.731</td>\n",
       "      <td>955.615</td>\n",
       "      <td>978.545</td>\n",
       "      <td>967.618</td>\n",
       "      <td>961.143</td>\n",
       "      <td>1014.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159041</th>\n",
       "      <td>2017 Audi Q5 FY MY17 2.0 TDI Quattro Design Wagon</td>\n",
       "      <td>50988.0</td>\n",
       "      <td>5098.8</td>\n",
       "      <td>45889.2</td>\n",
       "      <td>971.401117</td>\n",
       "      <td>875.267</td>\n",
       "      <td>870.619</td>\n",
       "      <td>853.637</td>\n",
       "      <td>860.74</td>\n",
       "      <td>928.492</td>\n",
       "      <td>879.718</td>\n",
       "      <td>886.1</td>\n",
       "      <td>907.363</td>\n",
       "      <td>897.23</td>\n",
       "      <td>891.227</td>\n",
       "      <td>940.824</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158222</th>\n",
       "      <td>2019 Audi Q5 FY MY19 40 TDI Quattro Sport Wagon</td>\n",
       "      <td>65996.0</td>\n",
       "      <td>6599.6</td>\n",
       "      <td>59396.4</td>\n",
       "      <td>1257.326982</td>\n",
       "      <td>1132.9</td>\n",
       "      <td>1126.88</td>\n",
       "      <td>1104.9</td>\n",
       "      <td>1114.09</td>\n",
       "      <td>1201.79</td>\n",
       "      <td>1138.66</td>\n",
       "      <td>1146.92</td>\n",
       "      <td>1174.44</td>\n",
       "      <td>1161.32</td>\n",
       "      <td>1153.55</td>\n",
       "      <td>1217.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156668</th>\n",
       "      <td>2017 Audi Q5 FY MY17 2.0 TDI Quattro Design Wagon</td>\n",
       "      <td>45977.0</td>\n",
       "      <td>4597.7</td>\n",
       "      <td>41379.3</td>\n",
       "      <td>875.933733</td>\n",
       "      <td>789.247</td>\n",
       "      <td>785.056</td>\n",
       "      <td>769.743</td>\n",
       "      <td>776.148</td>\n",
       "      <td>837.242</td>\n",
       "      <td>793.261</td>\n",
       "      <td>799.016</td>\n",
       "      <td>818.189</td>\n",
       "      <td>809.052</td>\n",
       "      <td>803.639</td>\n",
       "      <td>848.362</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>153646</th>\n",
       "      <td>2016 Audi Q7 4M 3.0 TDI Quattro Wagon</td>\n",
       "      <td>59984.0</td>\n",
       "      <td>5998.4</td>\n",
       "      <td>53985.6</td>\n",
       "      <td>1142.788982</td>\n",
       "      <td>1029.69</td>\n",
       "      <td>1024.23</td>\n",
       "      <td>1004.25</td>\n",
       "      <td>1012.6</td>\n",
       "      <td>1092.31</td>\n",
       "      <td>1034.93</td>\n",
       "      <td>1042.44</td>\n",
       "      <td>1067.45</td>\n",
       "      <td>1055.53</td>\n",
       "      <td>1048.47</td>\n",
       "      <td>1106.82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158319</th>\n",
       "      <td>2016 Audi S3 8V MY17 Sportback 2.0 TFSI S Tron...</td>\n",
       "      <td>47996.0</td>\n",
       "      <td>4799.6</td>\n",
       "      <td>43196.4</td>\n",
       "      <td>914.398839</td>\n",
       "      <td>823.906</td>\n",
       "      <td>819.53</td>\n",
       "      <td>803.545</td>\n",
       "      <td>810.231</td>\n",
       "      <td>874.008</td>\n",
       "      <td>828.095</td>\n",
       "      <td>834.104</td>\n",
       "      <td>854.118</td>\n",
       "      <td>844.58</td>\n",
       "      <td>838.929</td>\n",
       "      <td>885.616</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154858</th>\n",
       "      <td>2008 Audi S5 8T 4.2 FSI Quattro Coupe</td>\n",
       "      <td>37492.0</td>\n",
       "      <td>3749.2</td>\n",
       "      <td>33742.8</td>\n",
       "      <td>714.281217</td>\n",
       "      <td>643.593</td>\n",
       "      <td>640.175</td>\n",
       "      <td>627.688</td>\n",
       "      <td>632.911</td>\n",
       "      <td>682.73</td>\n",
       "      <td>646.865</td>\n",
       "      <td>651.559</td>\n",
       "      <td>667.193</td>\n",
       "      <td>659.743</td>\n",
       "      <td>655.328</td>\n",
       "      <td>691.797</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157753</th>\n",
       "      <td>2016 Audi TT FV S 2.0 TFSI Quattro Coupe</td>\n",
       "      <td>61990.0</td>\n",
       "      <td>6199.0</td>\n",
       "      <td>55791.0</td>\n",
       "      <td>1181.006418</td>\n",
       "      <td>1064.13</td>\n",
       "      <td>1058.48</td>\n",
       "      <td>1037.83</td>\n",
       "      <td>1046.47</td>\n",
       "      <td>1128.84</td>\n",
       "      <td>1069.54</td>\n",
       "      <td>1077.3</td>\n",
       "      <td>1103.15</td>\n",
       "      <td>1090.83</td>\n",
       "      <td>1083.53</td>\n",
       "      <td>1143.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158497</th>\n",
       "      <td>2013 BMW 116i F20 Hatchback</td>\n",
       "      <td>18991.0</td>\n",
       "      <td>1899.1</td>\n",
       "      <td>17091.9</td>\n",
       "      <td>361.808242</td>\n",
       "      <td>326.002</td>\n",
       "      <td>324.271</td>\n",
       "      <td>317.946</td>\n",
       "      <td>320.591</td>\n",
       "      <td>345.826</td>\n",
       "      <td>327.66</td>\n",
       "      <td>330.037</td>\n",
       "      <td>337.956</td>\n",
       "      <td>334.183</td>\n",
       "      <td>331.946</td>\n",
       "      <td>350.419</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158111</th>\n",
       "      <td>2016 BMW 118i F20 LCI Sport Line Hatchback</td>\n",
       "      <td>27988.0</td>\n",
       "      <td>2798.8</td>\n",
       "      <td>25189.2</td>\n",
       "      <td>533.215158</td>\n",
       "      <td>480.446</td>\n",
       "      <td>477.894</td>\n",
       "      <td>468.573</td>\n",
       "      <td>472.472</td>\n",
       "      <td>509.662</td>\n",
       "      <td>482.889</td>\n",
       "      <td>486.392</td>\n",
       "      <td>498.064</td>\n",
       "      <td>492.502</td>\n",
       "      <td>489.206</td>\n",
       "      <td>516.431</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157859</th>\n",
       "      <td>2017 BMW 118i F40 M Sport Hatchback</td>\n",
       "      <td>30977.0</td>\n",
       "      <td>3097.7</td>\n",
       "      <td>27879.3</td>\n",
       "      <td>590.160281</td>\n",
       "      <td>531.755</td>\n",
       "      <td>528.931</td>\n",
       "      <td>518.614</td>\n",
       "      <td>522.93</td>\n",
       "      <td>564.092</td>\n",
       "      <td>534.459</td>\n",
       "      <td>538.337</td>\n",
       "      <td>551.255</td>\n",
       "      <td>545.099</td>\n",
       "      <td>541.451</td>\n",
       "      <td>571.584</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157677</th>\n",
       "      <td>2014 BMW 220d F22 Coupe</td>\n",
       "      <td>26977.0</td>\n",
       "      <td>2697.7</td>\n",
       "      <td>24279.3</td>\n",
       "      <td>513.954027</td>\n",
       "      <td>463.091</td>\n",
       "      <td>460.632</td>\n",
       "      <td>451.647</td>\n",
       "      <td>455.405</td>\n",
       "      <td>491.252</td>\n",
       "      <td>465.446</td>\n",
       "      <td>468.823</td>\n",
       "      <td>480.072</td>\n",
       "      <td>474.711</td>\n",
       "      <td>471.535</td>\n",
       "      <td>497.776</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158478</th>\n",
       "      <td>2015 BMW 220i F23 Sport Line Convertible</td>\n",
       "      <td>31921.0</td>\n",
       "      <td>3192.1</td>\n",
       "      <td>28728.9</td>\n",
       "      <td>608.144957</td>\n",
       "      <td>547.96</td>\n",
       "      <td>545.05</td>\n",
       "      <td>534.419</td>\n",
       "      <td>538.865</td>\n",
       "      <td>581.282</td>\n",
       "      <td>550.747</td>\n",
       "      <td>554.742</td>\n",
       "      <td>568.054</td>\n",
       "      <td>561.71</td>\n",
       "      <td>557.952</td>\n",
       "      <td>589.002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158189</th>\n",
       "      <td>2017 BMW 330i F30 LCI M Sport Sedan</td>\n",
       "      <td>43988.0</td>\n",
       "      <td>4398.8</td>\n",
       "      <td>39589.2</td>\n",
       "      <td>838.040173</td>\n",
       "      <td>755.104</td>\n",
       "      <td>751.094</td>\n",
       "      <td>736.443</td>\n",
       "      <td>742.571</td>\n",
       "      <td>801.022</td>\n",
       "      <td>758.944</td>\n",
       "      <td>764.45</td>\n",
       "      <td>782.793</td>\n",
       "      <td>774.052</td>\n",
       "      <td>768.873</td>\n",
       "      <td>811.661</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158141</th>\n",
       "      <td>2013 BMW 428i F32 Sport Line Coupe</td>\n",
       "      <td>35999.0</td>\n",
       "      <td>3599.9</td>\n",
       "      <td>32399.1</td>\n",
       "      <td>685.837233</td>\n",
       "      <td>617.964</td>\n",
       "      <td>614.682</td>\n",
       "      <td>602.692</td>\n",
       "      <td>607.707</td>\n",
       "      <td>655.542</td>\n",
       "      <td>621.106</td>\n",
       "      <td>625.612</td>\n",
       "      <td>640.624</td>\n",
       "      <td>633.47</td>\n",
       "      <td>629.232</td>\n",
       "      <td>664.249</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158875</th>\n",
       "      <td>2017 BMW 530i G30 MY17 Luxury Line Sedan</td>\n",
       "      <td>65492.0</td>\n",
       "      <td>6549.2</td>\n",
       "      <td>58942.8</td>\n",
       "      <td>1247.724994</td>\n",
       "      <td>1124.24</td>\n",
       "      <td>1118.27</td>\n",
       "      <td>1096.46</td>\n",
       "      <td>1105.58</td>\n",
       "      <td>1192.61</td>\n",
       "      <td>1129.96</td>\n",
       "      <td>1138.16</td>\n",
       "      <td>1165.47</td>\n",
       "      <td>1152.46</td>\n",
       "      <td>1144.74</td>\n",
       "      <td>1208.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159465</th>\n",
       "      <td>2014 BMW M135i F20 MY14 Hatchback</td>\n",
       "      <td>31985.0</td>\n",
       "      <td>3198.5</td>\n",
       "      <td>28786.5</td>\n",
       "      <td>609.364257</td>\n",
       "      <td>549.059</td>\n",
       "      <td>546.143</td>\n",
       "      <td>535.49</td>\n",
       "      <td>539.946</td>\n",
       "      <td>582.447</td>\n",
       "      <td>551.851</td>\n",
       "      <td>555.855</td>\n",
       "      <td>569.193</td>\n",
       "      <td>562.837</td>\n",
       "      <td>559.07</td>\n",
       "      <td>590.183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154970</th>\n",
       "      <td>2016 BMW M4 F82 LCI MY17 Competition Coupe</td>\n",
       "      <td>79992.0</td>\n",
       "      <td>7999.2</td>\n",
       "      <td>71992.8</td>\n",
       "      <td>1523.972664</td>\n",
       "      <td>1373.15</td>\n",
       "      <td>1365.86</td>\n",
       "      <td>1339.22</td>\n",
       "      <td>1350.36</td>\n",
       "      <td>1456.66</td>\n",
       "      <td>1380.14</td>\n",
       "      <td>1390.15</td>\n",
       "      <td>1423.51</td>\n",
       "      <td>1407.61</td>\n",
       "      <td>1398.19</td>\n",
       "      <td>1476</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158509</th>\n",
       "      <td>2019 BMW X1 F48 MY19 xDrive 25I Wagon</td>\n",
       "      <td>54988.0</td>\n",
       "      <td>5498.8</td>\n",
       "      <td>49489.2</td>\n",
       "      <td>1047.607371</td>\n",
       "      <td>943.931</td>\n",
       "      <td>938.919</td>\n",
       "      <td>920.604</td>\n",
       "      <td>928.265</td>\n",
       "      <td>1001.33</td>\n",
       "      <td>948.731</td>\n",
       "      <td>955.615</td>\n",
       "      <td>978.545</td>\n",
       "      <td>967.618</td>\n",
       "      <td>961.143</td>\n",
       "      <td>1014.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158525</th>\n",
       "      <td>2015 BMW X3 F25 MY15 xDrive20d Wagon</td>\n",
       "      <td>38895.0</td>\n",
       "      <td>3889.5</td>\n",
       "      <td>35005.5</td>\n",
       "      <td>741.010560</td>\n",
       "      <td>667.677</td>\n",
       "      <td>664.131</td>\n",
       "      <td>651.177</td>\n",
       "      <td>656.595</td>\n",
       "      <td>708.279</td>\n",
       "      <td>671.072</td>\n",
       "      <td>675.941</td>\n",
       "      <td>692.16</td>\n",
       "      <td>684.431</td>\n",
       "      <td>679.851</td>\n",
       "      <td>717.685</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156654</th>\n",
       "      <td>2017 BMW X3 F25 MY17 Update xDrive 20I Wagon</td>\n",
       "      <td>39988.0</td>\n",
       "      <td>3998.8</td>\n",
       "      <td>35989.2</td>\n",
       "      <td>761.833919</td>\n",
       "      <td>686.439</td>\n",
       "      <td>682.794</td>\n",
       "      <td>669.476</td>\n",
       "      <td>675.046</td>\n",
       "      <td>728.182</td>\n",
       "      <td>689.93</td>\n",
       "      <td>694.936</td>\n",
       "      <td>711.611</td>\n",
       "      <td>703.664</td>\n",
       "      <td>698.956</td>\n",
       "      <td>737.853</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155117</th>\n",
       "      <td>2018 BMW X3 G01 xDrive30I Wagon</td>\n",
       "      <td>61492.0</td>\n",
       "      <td>6149.2</td>\n",
       "      <td>55342.8</td>\n",
       "      <td>1171.518740</td>\n",
       "      <td>1055.58</td>\n",
       "      <td>1049.97</td>\n",
       "      <td>1029.49</td>\n",
       "      <td>1038.06</td>\n",
       "      <td>1119.77</td>\n",
       "      <td>1060.95</td>\n",
       "      <td>1068.65</td>\n",
       "      <td>1094.29</td>\n",
       "      <td>1082.07</td>\n",
       "      <td>1074.83</td>\n",
       "      <td>1134.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158203</th>\n",
       "      <td>2017 BMW X4 F26 MY16 xDrive 20D Coupe</td>\n",
       "      <td>61988.0</td>\n",
       "      <td>6198.8</td>\n",
       "      <td>55789.2</td>\n",
       "      <td>1180.968315</td>\n",
       "      <td>1064.09</td>\n",
       "      <td>1058.44</td>\n",
       "      <td>1037.8</td>\n",
       "      <td>1046.43</td>\n",
       "      <td>1128.8</td>\n",
       "      <td>1069.51</td>\n",
       "      <td>1077.26</td>\n",
       "      <td>1103.11</td>\n",
       "      <td>1090.8</td>\n",
       "      <td>1083.5</td>\n",
       "      <td>1143.79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156759</th>\n",
       "      <td>2019 BMW X4 G02 M40I Wagon</td>\n",
       "      <td>91977.0</td>\n",
       "      <td>9197.7</td>\n",
       "      <td>82779.3</td>\n",
       "      <td>1752.305652</td>\n",
       "      <td>1578.89</td>\n",
       "      <td>1570.5</td>\n",
       "      <td>1539.87</td>\n",
       "      <td>1552.68</td>\n",
       "      <td>1674.9</td>\n",
       "      <td>1586.92</td>\n",
       "      <td>1598.43</td>\n",
       "      <td>1636.79</td>\n",
       "      <td>1618.51</td>\n",
       "      <td>1607.68</td>\n",
       "      <td>1697.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>interest_rate</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.44</td>\n",
       "      <td>5.22</td>\n",
       "      <td>4.41</td>\n",
       "      <td>4.75</td>\n",
       "      <td>7.91</td>\n",
       "      <td>5.65</td>\n",
       "      <td>5.95</td>\n",
       "      <td>6.94</td>\n",
       "      <td>6.47</td>\n",
       "      <td>6.19</td>\n",
       "      <td>8.47</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                            name    price  \\\n",
       "155601          2016 Audi A3 8V MY17 1.6 TDI Sportback Hatchback  25988.0   \n",
       "155602         2016 Audi A3 8V MY16 Sportback 1.6 TDI Attract...  21388.0   \n",
       "159551              2014 Audi A3 8V MY14 1.8 TFSI Ambition Sedan  21990.0   \n",
       "159571         2017 Audi A3 8V MY17 2.0 TFSI S Tronic Sport C...  39988.0   \n",
       "158585         2016 Audi A4 F4 (B9) 2.0 TFSI S Tronic Sport S...  32989.0   \n",
       "159540         2017 Audi A4 F4 MY17 (B9) 2.0 TFSI S Tronic Sp...  38985.0   \n",
       "159379         2018 Audi A5 F5 MY18 2.0 TFSI Quattro S Tronic...  65981.0   \n",
       "158349                2018 Audi Q2 GA MY18 1.4 TFSI Design Wagon  42900.0   \n",
       "158414             2020 Audi Q3 F3 MY21 35 TFSI S Line Sportback  61988.0   \n",
       "158871               2018 Audi Q3 8U MY18 1.4 TFSI (110kW) Wagon  39988.0   \n",
       "159374            2013 Audi Q3 8U 2.0 TFSI Quattro (125kW) Wagon  25995.0   \n",
       "158259                2016 Audi Q5 8R MY16 2.0 TDI Quattro Wagon  40385.0   \n",
       "156623         2017 Audi Q5 FY MY18 2.0 TFSI Quattro Sport Wagon  54988.0   \n",
       "159041         2017 Audi Q5 FY MY17 2.0 TDI Quattro Design Wagon  50988.0   \n",
       "158222           2019 Audi Q5 FY MY19 40 TDI Quattro Sport Wagon  65996.0   \n",
       "156668         2017 Audi Q5 FY MY17 2.0 TDI Quattro Design Wagon  45977.0   \n",
       "153646                     2016 Audi Q7 4M 3.0 TDI Quattro Wagon  59984.0   \n",
       "158319         2016 Audi S3 8V MY17 Sportback 2.0 TFSI S Tron...  47996.0   \n",
       "154858                     2008 Audi S5 8T 4.2 FSI Quattro Coupe  37492.0   \n",
       "157753                  2016 Audi TT FV S 2.0 TFSI Quattro Coupe  61990.0   \n",
       "158497                               2013 BMW 116i F20 Hatchback  18991.0   \n",
       "158111                2016 BMW 118i F20 LCI Sport Line Hatchback  27988.0   \n",
       "157859                       2017 BMW 118i F40 M Sport Hatchback  30977.0   \n",
       "157677                                   2014 BMW 220d F22 Coupe  26977.0   \n",
       "158478                  2015 BMW 220i F23 Sport Line Convertible  31921.0   \n",
       "158189                       2017 BMW 330i F30 LCI M Sport Sedan  43988.0   \n",
       "158141                        2013 BMW 428i F32 Sport Line Coupe  35999.0   \n",
       "158875                  2017 BMW 530i G30 MY17 Luxury Line Sedan  65492.0   \n",
       "159465                         2014 BMW M135i F20 MY14 Hatchback  31985.0   \n",
       "154970                2016 BMW M4 F82 LCI MY17 Competition Coupe  79992.0   \n",
       "158509                     2019 BMW X1 F48 MY19 xDrive 25I Wagon  54988.0   \n",
       "158525                      2015 BMW X3 F25 MY15 xDrive20d Wagon  38895.0   \n",
       "156654              2017 BMW X3 F25 MY17 Update xDrive 20I Wagon  39988.0   \n",
       "155117                           2018 BMW X3 G01 xDrive30I Wagon  61492.0   \n",
       "158203                     2017 BMW X4 F26 MY16 xDrive 20D Coupe  61988.0   \n",
       "156759                                2019 BMW X4 G02 M40I Wagon  91977.0   \n",
       "interest_rate                                                NaN      NaN   \n",
       "\n",
       "               deposit  loan amount  seller monthly repayment Plenti Car Loan  \\\n",
       "155601          2598.8      23389.2                495.112031         446.113   \n",
       "155602          2138.8      19249.2                407.474839         367.149   \n",
       "159551          2199.0      19791.0                418.943880         377.483   \n",
       "159571          3998.8      35989.2                761.833919         686.439   \n",
       "158585          3298.9      29690.1                628.492027         566.294   \n",
       "159540          3898.5      35086.5                742.725201         669.222   \n",
       "159379          6598.1      59382.9               1257.041208         1132.64   \n",
       "158349          4290.0      38610.0                817.312072         736.427   \n",
       "158414          6198.8      55789.2               1180.968315         1064.09   \n",
       "158871          3998.8      35989.2                761.833919         686.439   \n",
       "159374          2599.5      23395.5                495.245392         446.234   \n",
       "158259          4038.5      36346.5                769.397390         693.254   \n",
       "156623          5498.8      49489.2               1047.607371         943.931   \n",
       "159041          5098.8      45889.2                971.401117         875.267   \n",
       "158222          6599.6      59396.4               1257.326982          1132.9   \n",
       "156668          4597.7      41379.3                875.933733         789.247   \n",
       "153646          5998.4      53985.6               1142.788982         1029.69   \n",
       "158319          4799.6      43196.4                914.398839         823.906   \n",
       "154858          3749.2      33742.8                714.281217         643.593   \n",
       "157753          6199.0      55791.0               1181.006418         1064.13   \n",
       "158497          1899.1      17091.9                361.808242         326.002   \n",
       "158111          2798.8      25189.2                533.215158         480.446   \n",
       "157859          3097.7      27879.3                590.160281         531.755   \n",
       "157677          2697.7      24279.3                513.954027         463.091   \n",
       "158478          3192.1      28728.9                608.144957          547.96   \n",
       "158189          4398.8      39589.2                838.040173         755.104   \n",
       "158141          3599.9      32399.1                685.837233         617.964   \n",
       "158875          6549.2      58942.8               1247.724994         1124.24   \n",
       "159465          3198.5      28786.5                609.364257         549.059   \n",
       "154970          7999.2      71992.8               1523.972664         1373.15   \n",
       "158509          5498.8      49489.2               1047.607371         943.931   \n",
       "158525          3889.5      35005.5                741.010560         667.677   \n",
       "156654          3998.8      35989.2                761.833919         686.439   \n",
       "155117          6149.2      55342.8               1171.518740         1055.58   \n",
       "158203          6198.8      55789.2               1180.968315         1064.09   \n",
       "156759          9197.7      82779.3               1752.305652         1578.89   \n",
       "interest_rate      NaN          NaN                       NaN            5.44   \n",
       "\n",
       "              Loans.com.au - New and Dealer Used Car Loan  \\\n",
       "155601                                            443.744   \n",
       "155602                                              365.2   \n",
       "159551                                            375.479   \n",
       "159571                                            682.794   \n",
       "158585                                            563.286   \n",
       "159540                                            665.668   \n",
       "159379                                            1126.62   \n",
       "158349                                            732.516   \n",
       "158414                                            1058.44   \n",
       "158871                                            682.794   \n",
       "159374                                            443.864   \n",
       "158259                                            689.573   \n",
       "156623                                            938.919   \n",
       "159041                                            870.619   \n",
       "158222                                            1126.88   \n",
       "156668                                            785.056   \n",
       "153646                                            1024.23   \n",
       "158319                                             819.53   \n",
       "154858                                            640.175   \n",
       "157753                                            1058.48   \n",
       "158497                                            324.271   \n",
       "158111                                            477.894   \n",
       "157859                                            528.931   \n",
       "157677                                            460.632   \n",
       "158478                                             545.05   \n",
       "158189                                            751.094   \n",
       "158141                                            614.682   \n",
       "158875                                            1118.27   \n",
       "159465                                            546.143   \n",
       "154970                                            1365.86   \n",
       "158509                                            938.919   \n",
       "158525                                            664.131   \n",
       "156654                                            682.794   \n",
       "155117                                            1049.97   \n",
       "158203                                            1058.44   \n",
       "156759                                             1570.5   \n",
       "interest_rate                                        5.22   \n",
       "\n",
       "              Credit Concierge Car Loan Wisr Secured Car Loan  \\\n",
       "155601                          435.089               438.709   \n",
       "155602                          358.076               361.056   \n",
       "159551                          368.155               371.218   \n",
       "159571                          669.476               675.046   \n",
       "158585                          552.299               556.895   \n",
       "159540                          652.683               658.114   \n",
       "159379                          1104.65               1113.84   \n",
       "158349                          718.228               724.204   \n",
       "158414                           1037.8               1046.43   \n",
       "158871                          669.476               675.046   \n",
       "159374                          435.206               438.827   \n",
       "158259                          676.122               681.748   \n",
       "156623                          920.604               928.265   \n",
       "159041                          853.637                860.74   \n",
       "158222                           1104.9               1114.09   \n",
       "156668                          769.743               776.148   \n",
       "153646                          1004.25                1012.6   \n",
       "158319                          803.545               810.231   \n",
       "154858                          627.688               632.911   \n",
       "157753                          1037.83               1046.47   \n",
       "158497                          317.946               320.591   \n",
       "158111                          468.573               472.472   \n",
       "157859                          518.614                522.93   \n",
       "157677                          451.647               455.405   \n",
       "158478                          534.419               538.865   \n",
       "158189                          736.443               742.571   \n",
       "158141                          602.692               607.707   \n",
       "158875                          1096.46               1105.58   \n",
       "159465                           535.49               539.946   \n",
       "154970                          1339.22               1350.36   \n",
       "158509                          920.604               928.265   \n",
       "158525                          651.177               656.595   \n",
       "156654                          669.476               675.046   \n",
       "155117                          1029.49               1038.06   \n",
       "158203                           1037.8               1046.43   \n",
       "156759                          1539.87               1552.68   \n",
       "interest_rate                      4.41                  4.75   \n",
       "\n",
       "              NAB Personal Loan Unsecured Fixed  OurMoneyMarket Car Loan  \\\n",
       "155601                                   473.242                 448.382   \n",
       "155602                                   389.476                 369.016   \n",
       "159551                                   400.438                 379.403   \n",
       "159571                                   728.182                  689.93   \n",
       "158585                                    600.73                 569.173   \n",
       "159540                                   709.917                 672.625   \n",
       "159379                                   1201.51                  1138.4   \n",
       "158349                                    781.21                 740.172   \n",
       "158414                                    1128.8                 1069.51   \n",
       "158871                                   728.182                  689.93   \n",
       "159374                                   473.369                 448.503   \n",
       "158259                                   735.411                  696.78   \n",
       "156623                                   1001.33                 948.731   \n",
       "159041                                   928.492                 879.718   \n",
       "158222                                   1201.79                 1138.66   \n",
       "156668                                   837.242                 793.261   \n",
       "153646                                   1092.31                 1034.93   \n",
       "158319                                   874.008                 828.095   \n",
       "154858                                    682.73                 646.865   \n",
       "157753                                   1128.84                 1069.54   \n",
       "158497                                   345.826                  327.66   \n",
       "158111                                   509.662                 482.889   \n",
       "157859                                   564.092                 534.459   \n",
       "157677                                   491.252                 465.446   \n",
       "158478                                   581.282                 550.747   \n",
       "158189                                   801.022                 758.944   \n",
       "158141                                   655.542                 621.106   \n",
       "158875                                   1192.61                 1129.96   \n",
       "159465                                   582.447                 551.851   \n",
       "154970                                   1456.66                 1380.14   \n",
       "158509                                   1001.33                 948.731   \n",
       "158525                                   708.279                 671.072   \n",
       "156654                                   728.182                  689.93   \n",
       "155117                                   1119.77                 1060.95   \n",
       "158203                                    1128.8                 1069.51   \n",
       "156759                                    1674.9                 1586.92   \n",
       "interest_rate                               7.91                    5.65   \n",
       "\n",
       "              SocietyOne Car Loan NRMA Used Car Loan  \\\n",
       "155601                    451.635            462.472   \n",
       "155602                    371.694            380.613   \n",
       "159551                    382.156            391.326   \n",
       "159571                    694.936            711.611   \n",
       "158585                    573.303            587.059   \n",
       "159540                    677.505            693.762   \n",
       "159379                    1146.66            1174.17   \n",
       "158349                    745.542            763.432   \n",
       "158414                    1077.26            1103.11   \n",
       "158871                    694.936            711.611   \n",
       "159374                    451.757            462.597   \n",
       "158259                    701.835            718.676   \n",
       "156623                    955.615            978.545   \n",
       "159041                      886.1            907.363   \n",
       "158222                    1146.92            1174.44   \n",
       "156668                    799.016            818.189   \n",
       "153646                    1042.44            1067.45   \n",
       "158319                    834.104            854.118   \n",
       "154858                    651.559            667.193   \n",
       "157753                     1077.3            1103.15   \n",
       "158497                    330.037            337.956   \n",
       "158111                    486.392            498.064   \n",
       "157859                    538.337            551.255   \n",
       "157677                    468.823            480.072   \n",
       "158478                    554.742            568.054   \n",
       "158189                     764.45            782.793   \n",
       "158141                    625.612            640.624   \n",
       "158875                    1138.16            1165.47   \n",
       "159465                    555.855            569.193   \n",
       "154970                    1390.15            1423.51   \n",
       "158509                    955.615            978.545   \n",
       "158525                    675.941             692.16   \n",
       "156654                    694.936            711.611   \n",
       "155117                    1068.65            1094.29   \n",
       "158203                    1077.26            1103.11   \n",
       "156759                    1598.43            1636.79   \n",
       "interest_rate                5.95               6.94   \n",
       "\n",
       "              Symple Loans Personal Loan RACV Used Car Loans  \\\n",
       "155601                           457.308             454.248   \n",
       "155602                           376.362             373.844   \n",
       "159551                           386.956             384.366   \n",
       "159571                           703.664             698.956   \n",
       "158585                           580.504              576.62   \n",
       "159540                           686.015             681.424   \n",
       "159379                           1161.06             1153.29   \n",
       "158349                           754.907             749.855   \n",
       "158414                            1090.8              1083.5   \n",
       "158871                           703.664             698.956   \n",
       "159374                           457.431              454.37   \n",
       "158259                            710.65             705.895   \n",
       "156623                           967.618             961.143   \n",
       "159041                            897.23             891.227   \n",
       "158222                           1161.32             1153.55   \n",
       "156668                           809.052             803.639   \n",
       "153646                           1055.53             1048.47   \n",
       "158319                            844.58             838.929   \n",
       "154858                           659.743             655.328   \n",
       "157753                           1090.83             1083.53   \n",
       "158497                           334.183             331.946   \n",
       "158111                           492.502             489.206   \n",
       "157859                           545.099             541.451   \n",
       "157677                           474.711             471.535   \n",
       "158478                            561.71             557.952   \n",
       "158189                           774.052             768.873   \n",
       "158141                            633.47             629.232   \n",
       "158875                           1152.46             1144.74   \n",
       "159465                           562.837              559.07   \n",
       "154970                           1407.61             1398.19   \n",
       "158509                           967.618             961.143   \n",
       "158525                           684.431             679.851   \n",
       "156654                           703.664             698.956   \n",
       "155117                           1082.07             1074.83   \n",
       "158203                            1090.8              1083.5   \n",
       "156759                           1618.51             1607.68   \n",
       "interest_rate                       6.47                6.19   \n",
       "\n",
       "              Macquarie Car Loan For   \n",
       "155601                        479.527  \n",
       "155602                        394.649  \n",
       "159551                        405.757  \n",
       "159571                        737.853  \n",
       "158585                        608.709  \n",
       "159540                        719.346  \n",
       "159379                        1217.47  \n",
       "158349                        791.585  \n",
       "158414                        1143.79  \n",
       "158871                        737.853  \n",
       "159374                        479.656  \n",
       "158259                        745.179  \n",
       "156623                        1014.63  \n",
       "159041                        940.824  \n",
       "158222                        1217.75  \n",
       "156668                        848.362  \n",
       "153646                        1106.82  \n",
       "158319                        885.616  \n",
       "154858                        691.797  \n",
       "157753                        1143.83  \n",
       "158497                        350.419  \n",
       "158111                        516.431  \n",
       "157859                        571.584  \n",
       "157677                        497.776  \n",
       "158478                        589.002  \n",
       "158189                        811.661  \n",
       "158141                        664.249  \n",
       "158875                        1208.45  \n",
       "159465                        590.183  \n",
       "154970                           1476  \n",
       "158509                        1014.63  \n",
       "158525                        717.685  \n",
       "156654                        737.853  \n",
       "155117                        1134.64  \n",
       "158203                        1143.79  \n",
       "156759                        1697.15  \n",
       "interest_rate                    8.47  "
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combined_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "#initially the interest rates was at the bottom of combined_df, removing it to make it cleaner\n",
    "combined_df = combined_df.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "#exporting the completed comnbined_df into SQL\n",
    "from sqlalchemy import create_engine\n",
    "from config import connection\n",
    "conn = create_engine(connection).connect()\n",
    "combined_df.to_sql('car_loans', conn, if_exists='replace')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "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>index</th>\n",
       "      <th>name</th>\n",
       "      <th>price</th>\n",
       "      <th>deposit</th>\n",
       "      <th>loan amount</th>\n",
       "      <th>seller monthly repayment</th>\n",
       "      <th>Plenti Car Loan</th>\n",
       "      <th>Loans.com.au - New and Dealer Used Car Loan</th>\n",
       "      <th>Credit Concierge Car Loan</th>\n",
       "      <th>Wisr Secured Car Loan</th>\n",
       "      <th>NAB Personal Loan Unsecured Fixed</th>\n",
       "      <th>OurMoneyMarket Car Loan</th>\n",
       "      <th>SocietyOne Car Loan</th>\n",
       "      <th>NRMA Used Car Loan</th>\n",
       "      <th>Symple Loans Personal Loan</th>\n",
       "      <th>RACV Used Car Loans</th>\n",
       "      <th>Macquarie Car Loan For</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>155601</td>\n",
       "      <td>2016 Audi A3 8V MY17 1.6 TDI Sportback Hatchback</td>\n",
       "      <td>25988.0</td>\n",
       "      <td>2598.8</td>\n",
       "      <td>23389.2</td>\n",
       "      <td>495.112031</td>\n",
       "      <td>446.113447</td>\n",
       "      <td>443.744375</td>\n",
       "      <td>435.088774</td>\n",
       "      <td>438.709166</td>\n",
       "      <td>473.241849</td>\n",
       "      <td>448.382062</td>\n",
       "      <td>451.635177</td>\n",
       "      <td>462.472375</td>\n",
       "      <td>457.307962</td>\n",
       "      <td>454.248023</td>\n",
       "      <td>479.527144</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>155602</td>\n",
       "      <td>2016 Audi A3 8V MY16 Sportback 1.6 TDI Attract...</td>\n",
       "      <td>21388.0</td>\n",
       "      <td>2138.8</td>\n",
       "      <td>19249.2</td>\n",
       "      <td>407.474839</td>\n",
       "      <td>367.149238</td>\n",
       "      <td>365.199503</td>\n",
       "      <td>358.075985</td>\n",
       "      <td>361.055550</td>\n",
       "      <td>389.475784</td>\n",
       "      <td>369.016298</td>\n",
       "      <td>371.693596</td>\n",
       "      <td>380.612558</td>\n",
       "      <td>376.362271</td>\n",
       "      <td>373.843956</td>\n",
       "      <td>394.648552</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>159551</td>\n",
       "      <td>2014 Audi A3 8V MY14 1.8 TFSI Ambition Sedan</td>\n",
       "      <td>21990.0</td>\n",
       "      <td>2199.0</td>\n",
       "      <td>19791.0</td>\n",
       "      <td>418.943880</td>\n",
       "      <td>377.483250</td>\n",
       "      <td>375.478636</td>\n",
       "      <td>368.154615</td>\n",
       "      <td>371.218045</td>\n",
       "      <td>400.438212</td>\n",
       "      <td>379.402861</td>\n",
       "      <td>382.155516</td>\n",
       "      <td>391.325517</td>\n",
       "      <td>386.955598</td>\n",
       "      <td>384.366401</td>\n",
       "      <td>405.756576</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>159571</td>\n",
       "      <td>2017 Audi A3 8V MY17 2.0 TFSI S Tronic Sport C...</td>\n",
       "      <td>39988.0</td>\n",
       "      <td>3998.8</td>\n",
       "      <td>35989.2</td>\n",
       "      <td>761.833919</td>\n",
       "      <td>686.439299</td>\n",
       "      <td>682.793984</td>\n",
       "      <td>669.475523</td>\n",
       "      <td>675.046257</td>\n",
       "      <td>728.182048</td>\n",
       "      <td>689.930041</td>\n",
       "      <td>694.935642</td>\n",
       "      <td>711.610949</td>\n",
       "      <td>703.664413</td>\n",
       "      <td>698.956055</td>\n",
       "      <td>737.853295</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>158585</td>\n",
       "      <td>2016 Audi A4 F4 (B9) 2.0 TFSI S Tronic Sport S...</td>\n",
       "      <td>32989.0</td>\n",
       "      <td>3298.9</td>\n",
       "      <td>29690.1</td>\n",
       "      <td>628.492027</td>\n",
       "      <td>566.293539</td>\n",
       "      <td>563.286254</td>\n",
       "      <td>552.298890</td>\n",
       "      <td>556.894592</td>\n",
       "      <td>600.730159</td>\n",
       "      <td>569.173305</td>\n",
       "      <td>573.302788</td>\n",
       "      <td>587.059458</td>\n",
       "      <td>580.503785</td>\n",
       "      <td>576.619518</td>\n",
       "      <td>608.708671</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>159540</td>\n",
       "      <td>2017 Audi A4 F4 MY17 (B9) 2.0 TFSI S Tronic Sp...</td>\n",
       "      <td>38985.0</td>\n",
       "      <td>3898.5</td>\n",
       "      <td>35086.5</td>\n",
       "      <td>742.725201</td>\n",
       "      <td>669.221668</td>\n",
       "      <td>665.667787</td>\n",
       "      <td>652.683386</td>\n",
       "      <td>658.114392</td>\n",
       "      <td>709.917404</td>\n",
       "      <td>672.624853</td>\n",
       "      <td>677.504902</td>\n",
       "      <td>693.761950</td>\n",
       "      <td>686.014733</td>\n",
       "      <td>681.424472</td>\n",
       "      <td>719.346072</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>159379</td>\n",
       "      <td>2018 Audi A5 F5 MY18 2.0 TFSI Quattro S Tronic...</td>\n",
       "      <td>65981.0</td>\n",
       "      <td>6598.1</td>\n",
       "      <td>59382.9</td>\n",
       "      <td>1257.041208</td>\n",
       "      <td>1132.638576</td>\n",
       "      <td>1126.623733</td>\n",
       "      <td>1104.648006</td>\n",
       "      <td>1113.839828</td>\n",
       "      <td>1201.514947</td>\n",
       "      <td>1138.398370</td>\n",
       "      <td>1146.657712</td>\n",
       "      <td>1174.172302</td>\n",
       "      <td>1161.060360</td>\n",
       "      <td>1153.291473</td>\n",
       "      <td>1217.472699</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>158349</td>\n",
       "      <td>2018 Audi Q2 GA MY18 1.4 TFSI Design Wagon</td>\n",
       "      <td>42900.0</td>\n",
       "      <td>4290.0</td>\n",
       "      <td>38610.0</td>\n",
       "      <td>817.312072</td>\n",
       "      <td>736.427076</td>\n",
       "      <td>732.516302</td>\n",
       "      <td>718.227966</td>\n",
       "      <td>724.204372</td>\n",
       "      <td>781.209609</td>\n",
       "      <td>740.172020</td>\n",
       "      <td>745.542139</td>\n",
       "      <td>763.431772</td>\n",
       "      <td>754.906555</td>\n",
       "      <td>749.855325</td>\n",
       "      <td>791.585135</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>158414</td>\n",
       "      <td>2020 Audi Q3 F3 MY21 35 TFSI S Line Sportback</td>\n",
       "      <td>61988.0</td>\n",
       "      <td>6198.8</td>\n",
       "      <td>55789.2</td>\n",
       "      <td>1180.968315</td>\n",
       "      <td>1064.094210</td>\n",
       "      <td>1058.443370</td>\n",
       "      <td>1037.797557</td>\n",
       "      <td>1046.433114</td>\n",
       "      <td>1128.802360</td>\n",
       "      <td>1069.505436</td>\n",
       "      <td>1077.264944</td>\n",
       "      <td>1103.114422</td>\n",
       "      <td>1090.795980</td>\n",
       "      <td>1083.497247</td>\n",
       "      <td>1143.794390</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>158871</td>\n",
       "      <td>2018 Audi Q3 8U MY18 1.4 TFSI (110kW) Wagon</td>\n",
       "      <td>39988.0</td>\n",
       "      <td>3998.8</td>\n",
       "      <td>35989.2</td>\n",
       "      <td>761.833919</td>\n",
       "      <td>686.439299</td>\n",
       "      <td>682.793984</td>\n",
       "      <td>669.475523</td>\n",
       "      <td>675.046257</td>\n",
       "      <td>728.182048</td>\n",
       "      <td>689.930041</td>\n",
       "      <td>694.935642</td>\n",
       "      <td>711.610949</td>\n",
       "      <td>703.664413</td>\n",
       "      <td>698.956055</td>\n",
       "      <td>737.853295</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>159374</td>\n",
       "      <td>2013 Audi Q3 8U 2.0 TFSI Quattro (125kW) Wagon</td>\n",
       "      <td>25995.0</td>\n",
       "      <td>2599.5</td>\n",
       "      <td>23395.5</td>\n",
       "      <td>495.245392</td>\n",
       "      <td>446.233610</td>\n",
       "      <td>443.863899</td>\n",
       "      <td>435.205967</td>\n",
       "      <td>438.827334</td>\n",
       "      <td>473.369319</td>\n",
       "      <td>448.502836</td>\n",
       "      <td>451.756827</td>\n",
       "      <td>462.596945</td>\n",
       "      <td>457.431140</td>\n",
       "      <td>454.370377</td>\n",
       "      <td>479.656307</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>158259</td>\n",
       "      <td>2016 Audi Q5 8R MY16 2.0 TDI Quattro Wagon</td>\n",
       "      <td>40385.0</td>\n",
       "      <td>4038.5</td>\n",
       "      <td>36346.5</td>\n",
       "      <td>769.397390</td>\n",
       "      <td>693.254254</td>\n",
       "      <td>689.572748</td>\n",
       "      <td>676.122061</td>\n",
       "      <td>681.748101</td>\n",
       "      <td>735.411424</td>\n",
       "      <td>696.779651</td>\n",
       "      <td>701.834948</td>\n",
       "      <td>718.675807</td>\n",
       "      <td>710.650379</td>\n",
       "      <td>705.895275</td>\n",
       "      <td>745.178687</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>156623</td>\n",
       "      <td>2017 Audi Q5 FY MY18 2.0 TFSI Quattro Sport Wagon</td>\n",
       "      <td>54988.0</td>\n",
       "      <td>5498.8</td>\n",
       "      <td>49489.2</td>\n",
       "      <td>1047.607371</td>\n",
       "      <td>943.931284</td>\n",
       "      <td>938.918565</td>\n",
       "      <td>920.604182</td>\n",
       "      <td>928.264568</td>\n",
       "      <td>1001.332261</td>\n",
       "      <td>948.731446</td>\n",
       "      <td>955.614712</td>\n",
       "      <td>978.545135</td>\n",
       "      <td>967.617755</td>\n",
       "      <td>961.143231</td>\n",
       "      <td>1014.631314</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>159041</td>\n",
       "      <td>2017 Audi Q5 FY MY17 2.0 TDI Quattro Design Wagon</td>\n",
       "      <td>50988.0</td>\n",
       "      <td>5098.8</td>\n",
       "      <td>45889.2</td>\n",
       "      <td>971.401117</td>\n",
       "      <td>875.266754</td>\n",
       "      <td>870.618677</td>\n",
       "      <td>853.636540</td>\n",
       "      <td>860.739685</td>\n",
       "      <td>928.492204</td>\n",
       "      <td>879.717738</td>\n",
       "      <td>886.100293</td>\n",
       "      <td>907.362686</td>\n",
       "      <td>897.230197</td>\n",
       "      <td>891.226651</td>\n",
       "      <td>940.823843</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>158222</td>\n",
       "      <td>2019 Audi Q5 FY MY19 40 TDI Quattro Sport Wagon</td>\n",
       "      <td>65996.0</td>\n",
       "      <td>6599.6</td>\n",
       "      <td>59396.4</td>\n",
       "      <td>1257.326982</td>\n",
       "      <td>1132.896068</td>\n",
       "      <td>1126.879858</td>\n",
       "      <td>1104.899134</td>\n",
       "      <td>1114.093047</td>\n",
       "      <td>1201.788097</td>\n",
       "      <td>1138.657171</td>\n",
       "      <td>1146.918391</td>\n",
       "      <td>1174.439237</td>\n",
       "      <td>1161.324313</td>\n",
       "      <td>1153.553661</td>\n",
       "      <td>1217.749477</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>156668</td>\n",
       "      <td>2017 Audi Q5 FY MY17 2.0 TDI Quattro Design Wagon</td>\n",
       "      <td>45977.0</td>\n",
       "      <td>4597.7</td>\n",
       "      <td>41379.3</td>\n",
       "      <td>875.933733</td>\n",
       "      <td>789.247265</td>\n",
       "      <td>785.055992</td>\n",
       "      <td>769.742825</td>\n",
       "      <td>776.147888</td>\n",
       "      <td>837.241823</td>\n",
       "      <td>793.260815</td>\n",
       "      <td>799.016105</td>\n",
       "      <td>818.188872</td>\n",
       "      <td>809.052184</td>\n",
       "      <td>803.638655</td>\n",
       "      <td>848.361532</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>153646</td>\n",
       "      <td>2016 Audi Q7 4M 3.0 TDI Quattro Wagon</td>\n",
       "      <td>59984.0</td>\n",
       "      <td>5998.4</td>\n",
       "      <td>53985.6</td>\n",
       "      <td>1142.788982</td>\n",
       "      <td>1029.693281</td>\n",
       "      <td>1024.225126</td>\n",
       "      <td>1004.246768</td>\n",
       "      <td>1012.603147</td>\n",
       "      <td>1092.309492</td>\n",
       "      <td>1034.929568</td>\n",
       "      <td>1042.438220</td>\n",
       "      <td>1067.452015</td>\n",
       "      <td>1055.531814</td>\n",
       "      <td>1048.469040</td>\n",
       "      <td>1106.816847</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>158319</td>\n",
       "      <td>2016 Audi S3 8V MY17 Sportback 2.0 TFSI S Tron...</td>\n",
       "      <td>47996.0</td>\n",
       "      <td>4799.6</td>\n",
       "      <td>43196.4</td>\n",
       "      <td>914.398839</td>\n",
       "      <td>823.905687</td>\n",
       "      <td>819.530360</td>\n",
       "      <td>803.544743</td>\n",
       "      <td>810.231073</td>\n",
       "      <td>874.007842</td>\n",
       "      <td>828.095485</td>\n",
       "      <td>834.103508</td>\n",
       "      <td>854.118213</td>\n",
       "      <td>844.580304</td>\n",
       "      <td>838.929049</td>\n",
       "      <td>885.615854</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>154858</td>\n",
       "      <td>2008 Audi S5 8T 4.2 FSI Quattro Coupe</td>\n",
       "      <td>37492.0</td>\n",
       "      <td>3749.2</td>\n",
       "      <td>33742.8</td>\n",
       "      <td>714.281217</td>\n",
       "      <td>643.592633</td>\n",
       "      <td>640.174853</td>\n",
       "      <td>627.687714</td>\n",
       "      <td>632.910730</td>\n",
       "      <td>682.729853</td>\n",
       "      <td>646.865487</td>\n",
       "      <td>651.558645</td>\n",
       "      <td>667.193100</td>\n",
       "      <td>659.742578</td>\n",
       "      <td>655.328108</td>\n",
       "      <td>691.797433</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>157753</td>\n",
       "      <td>2016 Audi TT FV S 2.0 TFSI Quattro Coupe</td>\n",
       "      <td>61990.0</td>\n",
       "      <td>6199.0</td>\n",
       "      <td>55791.0</td>\n",
       "      <td>1181.006418</td>\n",
       "      <td>1064.128542</td>\n",
       "      <td>1058.477520</td>\n",
       "      <td>1037.831040</td>\n",
       "      <td>1046.466876</td>\n",
       "      <td>1128.838780</td>\n",
       "      <td>1069.539943</td>\n",
       "      <td>1077.299701</td>\n",
       "      <td>1103.150013</td>\n",
       "      <td>1090.831174</td>\n",
       "      <td>1083.532205</td>\n",
       "      <td>1143.831294</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>158497</td>\n",
       "      <td>2013 BMW 116i F20 Hatchback</td>\n",
       "      <td>18991.0</td>\n",
       "      <td>1899.1</td>\n",
       "      <td>17091.9</td>\n",
       "      <td>361.808242</td>\n",
       "      <td>326.002019</td>\n",
       "      <td>324.270795</td>\n",
       "      <td>317.945625</td>\n",
       "      <td>320.591264</td>\n",
       "      <td>345.826380</td>\n",
       "      <td>327.659833</td>\n",
       "      <td>330.037081</td>\n",
       "      <td>337.956475</td>\n",
       "      <td>334.182527</td>\n",
       "      <td>331.946445</td>\n",
       "      <td>350.419424</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>158111</td>\n",
       "      <td>2016 BMW 118i F20 LCI Sport Line Hatchback</td>\n",
       "      <td>27988.0</td>\n",
       "      <td>2798.8</td>\n",
       "      <td>25189.2</td>\n",
       "      <td>533.215158</td>\n",
       "      <td>480.445711</td>\n",
       "      <td>477.894319</td>\n",
       "      <td>468.572595</td>\n",
       "      <td>472.471607</td>\n",
       "      <td>509.661878</td>\n",
       "      <td>482.888916</td>\n",
       "      <td>486.392386</td>\n",
       "      <td>498.063600</td>\n",
       "      <td>492.501741</td>\n",
       "      <td>489.206313</td>\n",
       "      <td>516.430880</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>157859</td>\n",
       "      <td>2017 BMW 118i F40 M Sport Hatchback</td>\n",
       "      <td>30977.0</td>\n",
       "      <td>3097.7</td>\n",
       "      <td>27879.3</td>\n",
       "      <td>590.160281</td>\n",
       "      <td>531.755281</td>\n",
       "      <td>528.931410</td>\n",
       "      <td>518.614166</td>\n",
       "      <td>522.929576</td>\n",
       "      <td>564.091610</td>\n",
       "      <td>534.459410</td>\n",
       "      <td>538.337036</td>\n",
       "      <td>551.254686</td>\n",
       "      <td>545.098843</td>\n",
       "      <td>541.451478</td>\n",
       "      <td>571.583513</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>157677</td>\n",
       "      <td>2014 BMW 220d F22 Coupe</td>\n",
       "      <td>26977.0</td>\n",
       "      <td>2697.7</td>\n",
       "      <td>24279.3</td>\n",
       "      <td>513.954027</td>\n",
       "      <td>463.090751</td>\n",
       "      <td>460.631522</td>\n",
       "      <td>451.646523</td>\n",
       "      <td>455.404693</td>\n",
       "      <td>491.251553</td>\n",
       "      <td>465.445701</td>\n",
       "      <td>468.822617</td>\n",
       "      <td>480.072236</td>\n",
       "      <td>474.711285</td>\n",
       "      <td>471.534898</td>\n",
       "      <td>497.776041</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>158478</td>\n",
       "      <td>2015 BMW 220i F23 Sport Line Convertible</td>\n",
       "      <td>31921.0</td>\n",
       "      <td>3192.1</td>\n",
       "      <td>28728.9</td>\n",
       "      <td>608.144957</td>\n",
       "      <td>547.960110</td>\n",
       "      <td>545.050184</td>\n",
       "      <td>534.418529</td>\n",
       "      <td>538.865449</td>\n",
       "      <td>581.281863</td>\n",
       "      <td>550.746645</td>\n",
       "      <td>554.742439</td>\n",
       "      <td>568.053744</td>\n",
       "      <td>561.710307</td>\n",
       "      <td>557.951791</td>\n",
       "      <td>589.002077</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>158189</td>\n",
       "      <td>2017 BMW 330i F30 LCI M Sport Sedan</td>\n",
       "      <td>43988.0</td>\n",
       "      <td>4398.8</td>\n",
       "      <td>39589.2</td>\n",
       "      <td>838.040173</td>\n",
       "      <td>755.103828</td>\n",
       "      <td>751.093872</td>\n",
       "      <td>736.443165</td>\n",
       "      <td>742.571140</td>\n",
       "      <td>801.022105</td>\n",
       "      <td>758.943749</td>\n",
       "      <td>764.450061</td>\n",
       "      <td>782.793399</td>\n",
       "      <td>774.051971</td>\n",
       "      <td>768.872635</td>\n",
       "      <td>811.660767</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>158141</td>\n",
       "      <td>2013 BMW 428i F32 Sport Line Coupe</td>\n",
       "      <td>35999.0</td>\n",
       "      <td>3599.9</td>\n",
       "      <td>32399.1</td>\n",
       "      <td>685.837233</td>\n",
       "      <td>617.963597</td>\n",
       "      <td>614.681920</td>\n",
       "      <td>602.692041</td>\n",
       "      <td>607.707067</td>\n",
       "      <td>655.542301</td>\n",
       "      <td>621.106120</td>\n",
       "      <td>625.612388</td>\n",
       "      <td>640.624251</td>\n",
       "      <td>633.470422</td>\n",
       "      <td>629.231745</td>\n",
       "      <td>664.248794</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>158875</td>\n",
       "      <td>2017 BMW 530i G30 MY17 Luxury Line Sedan</td>\n",
       "      <td>65492.0</td>\n",
       "      <td>6549.2</td>\n",
       "      <td>58942.8</td>\n",
       "      <td>1247.724994</td>\n",
       "      <td>1124.244338</td>\n",
       "      <td>1118.274072</td>\n",
       "      <td>1096.461211</td>\n",
       "      <td>1105.584911</td>\n",
       "      <td>1192.610250</td>\n",
       "      <td>1129.961444</td>\n",
       "      <td>1138.159575</td>\n",
       "      <td>1165.470248</td>\n",
       "      <td>1152.455481</td>\n",
       "      <td>1144.744172</td>\n",
       "      <td>1208.449735</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>159465</td>\n",
       "      <td>2014 BMW M135i F20 MY14 Hatchback</td>\n",
       "      <td>31985.0</td>\n",
       "      <td>3198.5</td>\n",
       "      <td>28786.5</td>\n",
       "      <td>609.364257</td>\n",
       "      <td>549.058742</td>\n",
       "      <td>546.142982</td>\n",
       "      <td>535.490012</td>\n",
       "      <td>539.945847</td>\n",
       "      <td>582.447304</td>\n",
       "      <td>551.850864</td>\n",
       "      <td>555.854669</td>\n",
       "      <td>569.192663</td>\n",
       "      <td>562.836508</td>\n",
       "      <td>559.070456</td>\n",
       "      <td>590.182996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>154970</td>\n",
       "      <td>2016 BMW M4 F82 LCI MY17 Competition Coupe</td>\n",
       "      <td>79992.0</td>\n",
       "      <td>7999.2</td>\n",
       "      <td>71992.8</td>\n",
       "      <td>1523.972664</td>\n",
       "      <td>1373.153256</td>\n",
       "      <td>1365.861167</td>\n",
       "      <td>1339.218916</td>\n",
       "      <td>1350.362613</td>\n",
       "      <td>1456.655456</td>\n",
       "      <td>1380.136136</td>\n",
       "      <td>1390.149342</td>\n",
       "      <td>1423.506628</td>\n",
       "      <td>1407.610377</td>\n",
       "      <td>1398.191776</td>\n",
       "      <td>1476.001820</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>158509</td>\n",
       "      <td>2019 BMW X1 F48 MY19 xDrive 25I Wagon</td>\n",
       "      <td>54988.0</td>\n",
       "      <td>5498.8</td>\n",
       "      <td>49489.2</td>\n",
       "      <td>1047.607371</td>\n",
       "      <td>943.931284</td>\n",
       "      <td>938.918565</td>\n",
       "      <td>920.604182</td>\n",
       "      <td>928.264568</td>\n",
       "      <td>1001.332261</td>\n",
       "      <td>948.731446</td>\n",
       "      <td>955.614712</td>\n",
       "      <td>978.545135</td>\n",
       "      <td>967.617755</td>\n",
       "      <td>961.143231</td>\n",
       "      <td>1014.631314</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>158525</td>\n",
       "      <td>2015 BMW X3 F25 MY15 xDrive20d Wagon</td>\n",
       "      <td>38895.0</td>\n",
       "      <td>3889.5</td>\n",
       "      <td>35005.5</td>\n",
       "      <td>741.010560</td>\n",
       "      <td>667.676716</td>\n",
       "      <td>664.131039</td>\n",
       "      <td>651.176614</td>\n",
       "      <td>656.595082</td>\n",
       "      <td>708.278502</td>\n",
       "      <td>671.072045</td>\n",
       "      <td>675.940827</td>\n",
       "      <td>692.160345</td>\n",
       "      <td>684.431013</td>\n",
       "      <td>679.851349</td>\n",
       "      <td>717.685404</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>156654</td>\n",
       "      <td>2017 BMW X3 F25 MY17 Update xDrive 20I Wagon</td>\n",
       "      <td>39988.0</td>\n",
       "      <td>3998.8</td>\n",
       "      <td>35989.2</td>\n",
       "      <td>761.833919</td>\n",
       "      <td>686.439299</td>\n",
       "      <td>682.793984</td>\n",
       "      <td>669.475523</td>\n",
       "      <td>675.046257</td>\n",
       "      <td>728.182048</td>\n",
       "      <td>689.930041</td>\n",
       "      <td>694.935642</td>\n",
       "      <td>711.610949</td>\n",
       "      <td>703.664413</td>\n",
       "      <td>698.956055</td>\n",
       "      <td>737.853295</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>155117</td>\n",
       "      <td>2018 BMW X3 G01 xDrive30I Wagon</td>\n",
       "      <td>61492.0</td>\n",
       "      <td>6149.2</td>\n",
       "      <td>55342.8</td>\n",
       "      <td>1171.518740</td>\n",
       "      <td>1055.579808</td>\n",
       "      <td>1049.974183</td>\n",
       "      <td>1029.493569</td>\n",
       "      <td>1038.060028</td>\n",
       "      <td>1119.770193</td>\n",
       "      <td>1060.947736</td>\n",
       "      <td>1068.645156</td>\n",
       "      <td>1094.287798</td>\n",
       "      <td>1082.067923</td>\n",
       "      <td>1074.827591</td>\n",
       "      <td>1134.642263</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>158203</td>\n",
       "      <td>2017 BMW X4 F26 MY16 xDrive 20D Coupe</td>\n",
       "      <td>61988.0</td>\n",
       "      <td>6198.8</td>\n",
       "      <td>55789.2</td>\n",
       "      <td>1180.968315</td>\n",
       "      <td>1064.094210</td>\n",
       "      <td>1058.443370</td>\n",
       "      <td>1037.797557</td>\n",
       "      <td>1046.433114</td>\n",
       "      <td>1128.802360</td>\n",
       "      <td>1069.505436</td>\n",
       "      <td>1077.264944</td>\n",
       "      <td>1103.114422</td>\n",
       "      <td>1090.795980</td>\n",
       "      <td>1083.497247</td>\n",
       "      <td>1143.794390</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>156759</td>\n",
       "      <td>2019 BMW X4 G02 M40I Wagon</td>\n",
       "      <td>91977.0</td>\n",
       "      <td>9197.7</td>\n",
       "      <td>82779.3</td>\n",
       "      <td>1752.305652</td>\n",
       "      <td>1578.889352</td>\n",
       "      <td>1570.504708</td>\n",
       "      <td>1539.870715</td>\n",
       "      <td>1552.684044</td>\n",
       "      <td>1674.902476</td>\n",
       "      <td>1586.918460</td>\n",
       "      <td>1598.431918</td>\n",
       "      <td>1636.787043</td>\n",
       "      <td>1618.509097</td>\n",
       "      <td>1607.679330</td>\n",
       "      <td>1697.147458</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     index                                               name    price  \\\n",
       "0   155601   2016 Audi A3 8V MY17 1.6 TDI Sportback Hatchback  25988.0   \n",
       "1   155602  2016 Audi A3 8V MY16 Sportback 1.6 TDI Attract...  21388.0   \n",
       "2   159551       2014 Audi A3 8V MY14 1.8 TFSI Ambition Sedan  21990.0   \n",
       "3   159571  2017 Audi A3 8V MY17 2.0 TFSI S Tronic Sport C...  39988.0   \n",
       "4   158585  2016 Audi A4 F4 (B9) 2.0 TFSI S Tronic Sport S...  32989.0   \n",
       "5   159540  2017 Audi A4 F4 MY17 (B9) 2.0 TFSI S Tronic Sp...  38985.0   \n",
       "6   159379  2018 Audi A5 F5 MY18 2.0 TFSI Quattro S Tronic...  65981.0   \n",
       "7   158349         2018 Audi Q2 GA MY18 1.4 TFSI Design Wagon  42900.0   \n",
       "8   158414      2020 Audi Q3 F3 MY21 35 TFSI S Line Sportback  61988.0   \n",
       "9   158871        2018 Audi Q3 8U MY18 1.4 TFSI (110kW) Wagon  39988.0   \n",
       "10  159374     2013 Audi Q3 8U 2.0 TFSI Quattro (125kW) Wagon  25995.0   \n",
       "11  158259         2016 Audi Q5 8R MY16 2.0 TDI Quattro Wagon  40385.0   \n",
       "12  156623  2017 Audi Q5 FY MY18 2.0 TFSI Quattro Sport Wagon  54988.0   \n",
       "13  159041  2017 Audi Q5 FY MY17 2.0 TDI Quattro Design Wagon  50988.0   \n",
       "14  158222    2019 Audi Q5 FY MY19 40 TDI Quattro Sport Wagon  65996.0   \n",
       "15  156668  2017 Audi Q5 FY MY17 2.0 TDI Quattro Design Wagon  45977.0   \n",
       "16  153646              2016 Audi Q7 4M 3.0 TDI Quattro Wagon  59984.0   \n",
       "17  158319  2016 Audi S3 8V MY17 Sportback 2.0 TFSI S Tron...  47996.0   \n",
       "18  154858              2008 Audi S5 8T 4.2 FSI Quattro Coupe  37492.0   \n",
       "19  157753           2016 Audi TT FV S 2.0 TFSI Quattro Coupe  61990.0   \n",
       "20  158497                        2013 BMW 116i F20 Hatchback  18991.0   \n",
       "21  158111         2016 BMW 118i F20 LCI Sport Line Hatchback  27988.0   \n",
       "22  157859                2017 BMW 118i F40 M Sport Hatchback  30977.0   \n",
       "23  157677                            2014 BMW 220d F22 Coupe  26977.0   \n",
       "24  158478           2015 BMW 220i F23 Sport Line Convertible  31921.0   \n",
       "25  158189                2017 BMW 330i F30 LCI M Sport Sedan  43988.0   \n",
       "26  158141                 2013 BMW 428i F32 Sport Line Coupe  35999.0   \n",
       "27  158875           2017 BMW 530i G30 MY17 Luxury Line Sedan  65492.0   \n",
       "28  159465                  2014 BMW M135i F20 MY14 Hatchback  31985.0   \n",
       "29  154970         2016 BMW M4 F82 LCI MY17 Competition Coupe  79992.0   \n",
       "30  158509              2019 BMW X1 F48 MY19 xDrive 25I Wagon  54988.0   \n",
       "31  158525               2015 BMW X3 F25 MY15 xDrive20d Wagon  38895.0   \n",
       "32  156654       2017 BMW X3 F25 MY17 Update xDrive 20I Wagon  39988.0   \n",
       "33  155117                    2018 BMW X3 G01 xDrive30I Wagon  61492.0   \n",
       "34  158203              2017 BMW X4 F26 MY16 xDrive 20D Coupe  61988.0   \n",
       "35  156759                         2019 BMW X4 G02 M40I Wagon  91977.0   \n",
       "\n",
       "    deposit  loan amount  seller monthly repayment  Plenti Car Loan  \\\n",
       "0    2598.8      23389.2                495.112031       446.113447   \n",
       "1    2138.8      19249.2                407.474839       367.149238   \n",
       "2    2199.0      19791.0                418.943880       377.483250   \n",
       "3    3998.8      35989.2                761.833919       686.439299   \n",
       "4    3298.9      29690.1                628.492027       566.293539   \n",
       "5    3898.5      35086.5                742.725201       669.221668   \n",
       "6    6598.1      59382.9               1257.041208      1132.638576   \n",
       "7    4290.0      38610.0                817.312072       736.427076   \n",
       "8    6198.8      55789.2               1180.968315      1064.094210   \n",
       "9    3998.8      35989.2                761.833919       686.439299   \n",
       "10   2599.5      23395.5                495.245392       446.233610   \n",
       "11   4038.5      36346.5                769.397390       693.254254   \n",
       "12   5498.8      49489.2               1047.607371       943.931284   \n",
       "13   5098.8      45889.2                971.401117       875.266754   \n",
       "14   6599.6      59396.4               1257.326982      1132.896068   \n",
       "15   4597.7      41379.3                875.933733       789.247265   \n",
       "16   5998.4      53985.6               1142.788982      1029.693281   \n",
       "17   4799.6      43196.4                914.398839       823.905687   \n",
       "18   3749.2      33742.8                714.281217       643.592633   \n",
       "19   6199.0      55791.0               1181.006418      1064.128542   \n",
       "20   1899.1      17091.9                361.808242       326.002019   \n",
       "21   2798.8      25189.2                533.215158       480.445711   \n",
       "22   3097.7      27879.3                590.160281       531.755281   \n",
       "23   2697.7      24279.3                513.954027       463.090751   \n",
       "24   3192.1      28728.9                608.144957       547.960110   \n",
       "25   4398.8      39589.2                838.040173       755.103828   \n",
       "26   3599.9      32399.1                685.837233       617.963597   \n",
       "27   6549.2      58942.8               1247.724994      1124.244338   \n",
       "28   3198.5      28786.5                609.364257       549.058742   \n",
       "29   7999.2      71992.8               1523.972664      1373.153256   \n",
       "30   5498.8      49489.2               1047.607371       943.931284   \n",
       "31   3889.5      35005.5                741.010560       667.676716   \n",
       "32   3998.8      35989.2                761.833919       686.439299   \n",
       "33   6149.2      55342.8               1171.518740      1055.579808   \n",
       "34   6198.8      55789.2               1180.968315      1064.094210   \n",
       "35   9197.7      82779.3               1752.305652      1578.889352   \n",
       "\n",
       "    Loans.com.au - New and Dealer Used Car Loan  Credit Concierge Car Loan  \\\n",
       "0                                    443.744375                 435.088774   \n",
       "1                                    365.199503                 358.075985   \n",
       "2                                    375.478636                 368.154615   \n",
       "3                                    682.793984                 669.475523   \n",
       "4                                    563.286254                 552.298890   \n",
       "5                                    665.667787                 652.683386   \n",
       "6                                   1126.623733                1104.648006   \n",
       "7                                    732.516302                 718.227966   \n",
       "8                                   1058.443370                1037.797557   \n",
       "9                                    682.793984                 669.475523   \n",
       "10                                   443.863899                 435.205967   \n",
       "11                                   689.572748                 676.122061   \n",
       "12                                   938.918565                 920.604182   \n",
       "13                                   870.618677                 853.636540   \n",
       "14                                  1126.879858                1104.899134   \n",
       "15                                   785.055992                 769.742825   \n",
       "16                                  1024.225126                1004.246768   \n",
       "17                                   819.530360                 803.544743   \n",
       "18                                   640.174853                 627.687714   \n",
       "19                                  1058.477520                1037.831040   \n",
       "20                                   324.270795                 317.945625   \n",
       "21                                   477.894319                 468.572595   \n",
       "22                                   528.931410                 518.614166   \n",
       "23                                   460.631522                 451.646523   \n",
       "24                                   545.050184                 534.418529   \n",
       "25                                   751.093872                 736.443165   \n",
       "26                                   614.681920                 602.692041   \n",
       "27                                  1118.274072                1096.461211   \n",
       "28                                   546.142982                 535.490012   \n",
       "29                                  1365.861167                1339.218916   \n",
       "30                                   938.918565                 920.604182   \n",
       "31                                   664.131039                 651.176614   \n",
       "32                                   682.793984                 669.475523   \n",
       "33                                  1049.974183                1029.493569   \n",
       "34                                  1058.443370                1037.797557   \n",
       "35                                  1570.504708                1539.870715   \n",
       "\n",
       "    Wisr Secured Car Loan  NAB Personal Loan Unsecured Fixed   \\\n",
       "0              438.709166                          473.241849   \n",
       "1              361.055550                          389.475784   \n",
       "2              371.218045                          400.438212   \n",
       "3              675.046257                          728.182048   \n",
       "4              556.894592                          600.730159   \n",
       "5              658.114392                          709.917404   \n",
       "6             1113.839828                         1201.514947   \n",
       "7              724.204372                          781.209609   \n",
       "8             1046.433114                         1128.802360   \n",
       "9              675.046257                          728.182048   \n",
       "10             438.827334                          473.369319   \n",
       "11             681.748101                          735.411424   \n",
       "12             928.264568                         1001.332261   \n",
       "13             860.739685                          928.492204   \n",
       "14            1114.093047                         1201.788097   \n",
       "15             776.147888                          837.241823   \n",
       "16            1012.603147                         1092.309492   \n",
       "17             810.231073                          874.007842   \n",
       "18             632.910730                          682.729853   \n",
       "19            1046.466876                         1128.838780   \n",
       "20             320.591264                          345.826380   \n",
       "21             472.471607                          509.661878   \n",
       "22             522.929576                          564.091610   \n",
       "23             455.404693                          491.251553   \n",
       "24             538.865449                          581.281863   \n",
       "25             742.571140                          801.022105   \n",
       "26             607.707067                          655.542301   \n",
       "27            1105.584911                         1192.610250   \n",
       "28             539.945847                          582.447304   \n",
       "29            1350.362613                         1456.655456   \n",
       "30             928.264568                         1001.332261   \n",
       "31             656.595082                          708.278502   \n",
       "32             675.046257                          728.182048   \n",
       "33            1038.060028                         1119.770193   \n",
       "34            1046.433114                         1128.802360   \n",
       "35            1552.684044                         1674.902476   \n",
       "\n",
       "    OurMoneyMarket Car Loan  SocietyOne Car Loan  NRMA Used Car Loan  \\\n",
       "0                448.382062           451.635177          462.472375   \n",
       "1                369.016298           371.693596          380.612558   \n",
       "2                379.402861           382.155516          391.325517   \n",
       "3                689.930041           694.935642          711.610949   \n",
       "4                569.173305           573.302788          587.059458   \n",
       "5                672.624853           677.504902          693.761950   \n",
       "6               1138.398370          1146.657712         1174.172302   \n",
       "7                740.172020           745.542139          763.431772   \n",
       "8               1069.505436          1077.264944         1103.114422   \n",
       "9                689.930041           694.935642          711.610949   \n",
       "10               448.502836           451.756827          462.596945   \n",
       "11               696.779651           701.834948          718.675807   \n",
       "12               948.731446           955.614712          978.545135   \n",
       "13               879.717738           886.100293          907.362686   \n",
       "14              1138.657171          1146.918391         1174.439237   \n",
       "15               793.260815           799.016105          818.188872   \n",
       "16              1034.929568          1042.438220         1067.452015   \n",
       "17               828.095485           834.103508          854.118213   \n",
       "18               646.865487           651.558645          667.193100   \n",
       "19              1069.539943          1077.299701         1103.150013   \n",
       "20               327.659833           330.037081          337.956475   \n",
       "21               482.888916           486.392386          498.063600   \n",
       "22               534.459410           538.337036          551.254686   \n",
       "23               465.445701           468.822617          480.072236   \n",
       "24               550.746645           554.742439          568.053744   \n",
       "25               758.943749           764.450061          782.793399   \n",
       "26               621.106120           625.612388          640.624251   \n",
       "27              1129.961444          1138.159575         1165.470248   \n",
       "28               551.850864           555.854669          569.192663   \n",
       "29              1380.136136          1390.149342         1423.506628   \n",
       "30               948.731446           955.614712          978.545135   \n",
       "31               671.072045           675.940827          692.160345   \n",
       "32               689.930041           694.935642          711.610949   \n",
       "33              1060.947736          1068.645156         1094.287798   \n",
       "34              1069.505436          1077.264944         1103.114422   \n",
       "35              1586.918460          1598.431918         1636.787043   \n",
       "\n",
       "    Symple Loans Personal Loan  RACV Used Car Loans  Macquarie Car Loan For   \n",
       "0                   457.307962           454.248023               479.527144  \n",
       "1                   376.362271           373.843956               394.648552  \n",
       "2                   386.955598           384.366401               405.756576  \n",
       "3                   703.664413           698.956055               737.853295  \n",
       "4                   580.503785           576.619518               608.708671  \n",
       "5                   686.014733           681.424472               719.346072  \n",
       "6                  1161.060360          1153.291473              1217.472699  \n",
       "7                   754.906555           749.855325               791.585135  \n",
       "8                  1090.795980          1083.497247              1143.794390  \n",
       "9                   703.664413           698.956055               737.853295  \n",
       "10                  457.431140           454.370377               479.656307  \n",
       "11                  710.650379           705.895275               745.178687  \n",
       "12                  967.617755           961.143231              1014.631314  \n",
       "13                  897.230197           891.226651               940.823843  \n",
       "14                 1161.324313          1153.553661              1217.749477  \n",
       "15                  809.052184           803.638655               848.361532  \n",
       "16                 1055.531814          1048.469040              1106.816847  \n",
       "17                  844.580304           838.929049               885.615854  \n",
       "18                  659.742578           655.328108               691.797433  \n",
       "19                 1090.831174          1083.532205              1143.831294  \n",
       "20                  334.182527           331.946445               350.419424  \n",
       "21                  492.501741           489.206313               516.430880  \n",
       "22                  545.098843           541.451478               571.583513  \n",
       "23                  474.711285           471.534898               497.776041  \n",
       "24                  561.710307           557.951791               589.002077  \n",
       "25                  774.051971           768.872635               811.660767  \n",
       "26                  633.470422           629.231745               664.248794  \n",
       "27                 1152.455481          1144.744172              1208.449735  \n",
       "28                  562.836508           559.070456               590.182996  \n",
       "29                 1407.610377          1398.191776              1476.001820  \n",
       "30                  967.617755           961.143231              1014.631314  \n",
       "31                  684.431013           679.851349               717.685404  \n",
       "32                  703.664413           698.956055               737.853295  \n",
       "33                 1082.067923          1074.827591              1134.642263  \n",
       "34                 1090.795980          1083.497247              1143.794390  \n",
       "35                 1618.509097          1607.679330              1697.147458  "
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#quality check\n",
    "explore = pd.read_sql(\"SELECT * FROM car_loans\", conn)\n",
    "explore"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "browser.quit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}