{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Lame Cars Are a Better Deal #"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Introduction\n",
    "\n",
    "In 2019 the global used car market was estimated to be a whopping 1332 billion US dollars (1). This was prior to the ubiquitous supply chain disruptions that are now part of our daily lives and the expectations of a 13% to 32% decline in global trade (2)(3). The turnover in used car inventory for August and September was the highest in six years (4). One large sector of used car sales comes from the eBay Vehicle Market. They have over 7.4 million users per month. A car or truck is sold every 3 minutes (5). That's of lot of interactions!\n",
    "\n",
    "The main conclusion of this study is unpopular cars might be a better deal when using mileage as a metric. For the money spent they have lower mileage.\n",
    "\n",
    "Let's take a quick look at a selection of 50,000 rows from a dataset created in 2015 for the German eBay marketplace, eBay Kleinanzeigen. The original data set can be found here.\n",
    "https://data.world/data-society/used-cars-data\n",
    "\n",
    "Citations\n",
    "1. Grand View Research. Used Car Market Size, Share & Trends Analysis Report By Vehicle Type (Hybrid, Conventional, Electric), By Vendor Type, By Fuel Type, By Size, By Region, By Sales Channel, And Segment Forecasts, 2020 - 2027. Sept 2020.\n",
    "https://www.grandviewresearch.com/industry-analysis/used-car-market\n",
    "2. Li, X., Ghadami, A., Drake, J.M. et al. Mathematical model of the feedback between global supply chain disruption and COVID-19 dynamics. Sci Rep 11, 15450 (2021).\n",
    "https://doi.org/10.1038/s41598-021-94619-1\n",
    "3. World Trade Organization. Trade set to plunge as COVID-19 pandemic upends global economy. April 2020.\n",
    "https://www.wto.org/english/news_e/pres20_e/pr855_e.htm\n",
    "4. Eric Rosenbaum. The used car boom is one of the hottest, and trickiest, coronavirus markets for consumers. CNBC October 2020.\n",
    "https://www.cnbc.com/2020/10/15/used-car-boom-is-one-of-hottest-coronavirus-markets-for-consumers.html\n",
    "5. Welcome to the eBay Vehicle Seller Center. eBay. Sept 2021.\n",
    "https://pages.ebay.com/sellerinformation/vehiclesellercenter.html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Open and Examine Data Set\n",
    "* An initial look at the data set shows 50,000 entries with 20 columns of information.\n",
    "* Five of the columns are missing entries and in some the data needs to be converted to the correct type.\n",
    "* Some of the entries are in German and need to be translated."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "ename": "ModuleNotFoundError",
     "evalue": "No module named 'seaborn'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mModuleNotFoundError\u001b[0m                       Traceback (most recent call last)",
      "\u001b[0;32m/var/folders/bz/5cj1tzbj5xn319rpqrw6gpmh0000gn/T/ipykernel_2809/3296113383.py\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      5\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mpandas\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0mpd\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      6\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mmatplotlib\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mpyplot\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0mplt\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 7\u001b[0;31m \u001b[0;32mimport\u001b[0m \u001b[0mseaborn\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0msns\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      8\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mpprint\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      9\u001b[0m \u001b[0mautos\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread_csv\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"autos.csv\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mencoding\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m\"Latin-1\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mModuleNotFoundError\u001b[0m: No module named 'seaborn'"
     ]
    }
   ],
   "source": [
    "# import libraries and read csv\n",
    "# import emoji\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import pprint\n",
    "autos = pd.read_csv(\"autos.csv\", encoding=\"Latin-1\")\n",
    "# get info about dataset\n",
    "autos.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# initial stats\n",
    "autos.describe(include=\"all\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# first five rows\n",
    "autos.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Begin Cleaning Data Set\n",
    "* The `price` and `odometer` columns need to be converted to integers.\n",
    "* The column titles could be more clear and use snake_case.\n",
    "* It would be nice if the `name`, `brand`, `model`, and `vehicle_type` columns were grouped together."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# clean the price and odometer columns\n",
    "autos[\"price\"] = autos[\"price\"].str.replace(\"$\",\"\").str.replace(\",\",\"\")\n",
    "autos[\"price\"] = autos[\"price\"].astype(int)\n",
    "autos[\"odometer\"] = autos[\"odometer\"].str.replace(\",\",\"\").str.replace(\"km\",\"\")\n",
    "autos[\"odometer\"] = autos[\"odometer\"].astype(int)\n",
    "autos.rename(columns={\"odometer\" : \"odometer_km\"}, inplace=True)\n",
    "print(\"The data type for the price column is : \", autos[\"price\"].dtypes)\n",
    "print(\"The data type for the odometer_km column is : \", autos[\"odometer_km\"].dtypes)\n",
    "# change the column names\n",
    "new_cols = ['date_crawled', 'name', 'seller', 'offer_type', 'price', 'ab_test',\n",
    "       'vehicle_type', 'year_of_registration', 'gearbox', 'powerPS', 'model',\n",
    "       'odometer_km', 'month_of_registration', 'fuel_type', 'brand',\n",
    "       'unrepaired_damage', 'ad_created', 'num_of_pictures', 'postal_code',\n",
    "       'last_seen']\n",
    "autos.columns = new_cols\n",
    "# reorder columns\n",
    "autos = autos.reindex(columns=[\"date_crawled\", \"price\", \"name\", \"brand\", \"model\", \"vehicle_type\", \"ab_test\",\n",
    "                   \"odometer_km\", \"month_of_registration\", \"year_of_registration\", \"gearbox\",\"powerPS\",\n",
    "                   \"fuel_type\", \"unrepaired_damage\", \"ad_created\", \"postal_code\", \"last_seen\",\n",
    "                              \"seller\", \"offer_type\", \"num_of_pictures\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Removing outliers and unnecessary columns\n",
    "## Part 1 of 4\n",
    "Looking at the `price` and `odometer` columns.\n",
    "* The majority of the values in the price column are less than 500,000 but a small number are close to or well over 1,000,000! The rows for these prices do not appear to be unique in other ways nor are they related to the three columns being considered for removal. Two of the entries are for an expensive brand, but they are not representative of either group. Most of the expensive cars are priced a suspicious \\\\$12,345,678🤔. The high number of vehicles (1421) with a price of 0 will skew the results as well. Keeping all rows with prices between \\\\$1 and \\\\$500,00 seems to make sense.\n",
    "* The odometer column doesn't contain any outlier values. It is broken into a group of 13 buckets. Notably, 64% of the entries are in the 150,000km or more category. There doesn't seem to be any reason to remove any of the rows based on the odometer entries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# isolate the price column and reveal outlier values\n",
    "price_series = autos[\"price\"]\n",
    "print(\"Here's the index and price for the 15 most expensive vehicles:\\n\", price_series.sort_values(ascending=False).head(n=15))\n",
    "print(\"Here's the index and price for the 10 least expensive vehicles:\\n\", price_series.sort_values(ascending=False).tail(n=10))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# examine rows with outlier prices\n",
    "cheap_cars = autos[autos[\"price\"].between(1,100)]\n",
    "print(\"The number of cars between $1 and $100: \", len(cheap_cars))\n",
    "expensive_cars = autos[autos[\"price\"]>500000]\n",
    "print(\"The number of cars more than $500,000: \", len(expensive_cars))\n",
    "free_cars = autos[autos[\"price\"] == 0]\n",
    "print(\"The number of cars that are free: \", len(free_cars))\n",
    "outlier_bool = (autos[\"price\"] < 1) | (autos[\"price\"] > 500000)\n",
    "outlier_rows = autos[outlier_bool]\n",
    "outlier_rows.sort_values(by=\"price\", axis=0, ascending=False)\n",
    "print(\"This chart shows the most and least expensive cars.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# remove rows with outlier prices\n",
    "print(\"Length: \", len(autos))\n",
    "autos = autos[autos[\"price\"].between(1, 500000)]\n",
    "print(\"Length: \", len(autos))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# isolate the odometer column and reveal outlier values\n",
    "odometer_series = autos[\"odometer_km\"]\n",
    "print(odometer_series.unique().shape)\n",
    "print(odometer_series.describe)\n",
    "odometer_series.value_counts().sort_index(ascending=False).head(n=15)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Removing outliers and unnecessary columns\n",
    "### Part 2 of 4\n",
    "We can probably remove these three columns and make a note in the conclusion.\n",
    "* The `seller` and `offer_type` columns have only two values in each column. In each, one value represents all of the entries except for one entry.\n",
    "* The `num_of_pictures` column only has one entry, \"0\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# close look at value counts in three columns\n",
    "print(autos[\"seller\"].value_counts())\n",
    "print(autos[\"offer_type\"].value_counts())\n",
    "print(autos[\"num_of_pictures\"].value_counts())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop the seller, offer_type, and num_of_pictures columns\n",
    "autos = autos.drop([\"seller\", \"offer_type\", \"num_of_pictures\"], axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Removing outliers and unnecessary columns\n",
    "### Part 3 of 4\n",
    "The date values are in string format and should be converted but an initial look shows some interesting things.\n",
    "* The `date_crawled` column shows a normal distribution for when the ads were put into the data set. There are three low points that could be due to problems with the bot or network, or some other factor.\n",
    "* The `date_created` column is heavily skewed to the left. However, the tail is very shallow and the vast majority of entries occurs under a normal distribution. Creating a frequency table shows some ads with much older `date_created` values than the rest of the set. This is the only one of the three with dates prior to March 2016. Again, there are three low points that interestingly appear to correspond to the low points in the date_crawled series. Removing any dates prior to the earliest date for the `date_crawled` column removes 203 entries. Looking at a line graph for `date_crawled` and `date_created` confirms that the dips occur at the same times.\n",
    "* The `last_seen` column aligns more closely with `date_crawled` than the `date_created` column but there is a huge increase on April 4-6. These three days represent over 50% of the values in this column.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# isolate the date portion of the date_crawled column and count the number of entries per day\n",
    "date_crawled_dist = autos[\"date_crawled\"].str[:10].value_counts().sort_index()\n",
    "# set width and height for plots\n",
    "plt.rcParams['figure.figsize'] = [10,7]\n",
    "# create a line plot showing crawl volume for each day\n",
    "date_crawled_dist.plot.line()\n",
    "plt.ylabel(\"Number of entries\")\n",
    "plt.xlabel(\"Date Crawled\")\n",
    "plt.xticks(rotation=30)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# isolate the date portion of the ad_created column and count the number of entries per day\n",
    "ad_created_dist = autos[\"ad_created\"].str[:10].value_counts().sort_index()\n",
    "# create a line plot showing day the ad was created\n",
    "ad_created_dist.plot.line()\n",
    "plt.ylabel(\"Number of entries\")\n",
    "plt.xlabel(\"Date Ad Created\")\n",
    "plt.xticks(rotation=30)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# remove early entries so ad_created start date matches dates for date_crawled column\n",
    "autos = autos[autos[\"ad_created\"].str[:4] != \"2015\"]\n",
    "autos = autos[autos[\"ad_created\"].str[:7] != \"2016-01\"]\n",
    "autos = autos[autos[\"ad_created\"].str[:7] != \"2016-02\"]\n",
    "autos = autos[autos[\"ad_created\"].str[:10] != \"2016-03-01\"]\n",
    "autos = autos[autos[\"ad_created\"].str[:10] != \"2016-03-02\"]\n",
    "autos = autos[autos[\"ad_created\"].str[:10] != \"2016-03-03\"]\n",
    "autos = autos[autos[\"ad_created\"].str[:10] != \"2016-03-04\"]\n",
    "print(len(autos))\n",
    "# create a line plot showing day the ad was created\n",
    "print(len(autos))\n",
    "ad_created_dist = autos[\"ad_created\"].str[:10].value_counts().sort_index()\n",
    "ad_created_dist.plot.line()\n",
    "plt.ylabel(\"Number of entries\")\n",
    "plt.xlabel(\"Date Ad Created\")\n",
    "plt.xticks(rotation=30)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create a line plot that shows date_crawled and ad_created\n",
    "distributions = [date_crawled_dist, ad_created_dist]\n",
    "for distribution in distributions:\n",
    "    distribution.plot.line()\n",
    "plt.legend()\n",
    "plt.xticks(rotation=30)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# isolate the date portion of the last_seen column and count the number of entries per day\n",
    "last_seen_dist = autos[\"last_seen\"].str[:10].value_counts().sort_index()\n",
    "# create a line plot showing day the ad was removed\n",
    "last_seen_dist.plot.line()\n",
    "plt.ylabel(\"Number of entries\")\n",
    "plt.xlabel(\"Date Ad Removed\")\n",
    "plt.xticks(rotation=30)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# create a frequency table for the last_seen column\n",
    "# this allows for a more precise measure for the volume in the last three days\n",
    "last_seen_freq = autos[\"last_seen\"].str[:10].value_counts(normalize=True, dropna=False)*100\n",
    "last_seen_freq = last_seen_freq.sort_index()\n",
    "last_seen_freq"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Removing outliers and unnecessary columns\n",
    "### Part 4 of 4\n",
    "The `year_of_registration` column has 5 entries prior to 1900 and 1879 after 2016. It's unlikely that any of these dates are correct. It would take far to long to look up the true values for these entries and using a mean value would not be appropriate for what this column represents. Removing them might allow a more accurate view of the distribution. Interestingly, while this does affect the minimum, maximum, and standard deviation values, it results in little change to the upper ends of the first, second, and third quartiles."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# isolate the year_of_registration column and get initial stats\n",
    "registration_series = autos[\"year_of_registration\"]\n",
    "print(registration_series.describe())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# remove year_of_registration outliers\n",
    "print(len(autos[autos[\"year_of_registration\"] < 1900]))\n",
    "print(len(autos[autos[\"year_of_registration\"] > 2016]))\n",
    "print(\"The length of the data set before removing outlier registration year values is: \", len(autos))\n",
    "autos = autos[autos[\"year_of_registration\"].between(1900, 2016)]\n",
    "print(\"The length of the data set after removing outlier registration year values is: \", len(autos))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# isolate the year_of_registration column and get final stats\n",
    "registration_series = autos[\"year_of_registration\"]\n",
    "print(registration_series.describe())\n",
    "print(\"The earliest year for a vehicle to be registered is:\", registration_series.min())\n",
    "print(\"The latest year for a vehicle to be registered is:\", registration_series.max())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Focusing on the Brand\n",
    "At this point the dataset is prepared for an initial analysis. Many consumer choices are influenced by brand. Using that as a reference for looking at mean price and mean mileage is a good way to begin grouping the entries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# isolate the brand column and look at totals and relative frequencies.\n",
    "brand_series = autos[\"brand\"]\n",
    "# brand_value_counts = brand_series.value_counts()\n",
    "brand_value_counts = brand_series.value_counts(normalize=True, dropna=False)*100\n",
    "# print(brand_value_counts)\n",
    "# create a line plot showing volume of sales by brand\n",
    "# how do i get volkswagen on the last tick?\n",
    "brand_value_counts.plot.line()\n",
    "plt.ylabel(\"Number of entries\")\n",
    "plt.xlabel(\"Brand\")\n",
    "plt.xticks(rotation=45)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# a function to create a dictionary and mean column value for part of a series\n",
    "def autos_converter(c_series, mean_column, start_slice=0, stop_slice=5):\n",
    "    \"\"\"\n",
    "    Takes a series index and returns that index and the mean of a column in the autos dataframe.\n",
    "    Can be sliced. Default = 0:5\n",
    "    Example:\n",
    "    >>> autos_converter(c_series=brand_value_counts, mean_column=\"price\", stop_slice=2)\n",
    "    {'volkswagen': 5404.27828123409, 'bmw': 8337.549843627834}\n",
    "    \"\"\"\n",
    "    series_slice = c_series.iloc[start_slice:stop_slice]\n",
    "    series_dict = {}\n",
    "    for index in series_slice.index:\n",
    "        index_mean = autos.loc[autos[\"brand\"] == index, mean_column].mean()\n",
    "        series_dict[index] = index_mean\n",
    "    return series_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create dictionary with brand and mean price for top five selling brands\n",
    "t5_mean_price = autos_converter(c_series=brand_value_counts, mean_column=\"price\")\n",
    "# create dictionary with brand and mean odometer_km for top five selling brands\n",
    "t5_mean_odomkm = autos_converter(c_series=brand_value_counts, mean_column=\"odometer_km\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# i could have made another fundtion but i have to get onto the next project!\n",
    "# transform t5_mean_price dictionary to dataframe\n",
    "t5_mean_price_series = pd.Series(t5_mean_price)\n",
    "t5_df = pd.DataFrame(t5_mean_price_series, columns=[\"mean_price\"])\n",
    "# add columns for mean odometer_km and relative frequency in original df\n",
    "t5_mean_odomkm_series = pd.Series(t5_mean_odomkm)\n",
    "t5_df[\"mean_odomkm\"] = t5_mean_odomkm_series\n",
    "t5_df[\"%_in_autos\"] = brand_value_counts[:5]\n",
    "t5_df = t5_df.astype(int)\n",
    "t5_df = t5_df.reset_index()\n",
    "t5_df = t5_df.rename(columns={'index': 'brand'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create dictionary with brand and mean price for five least selling brands\n",
    "l5_mean_price = autos_converter(c_series=brand_value_counts, mean_column=\"price\", start_slice=-5, stop_slice=None)\n",
    "# create dictionary with brand and mean odometer_km for five least selling brands\n",
    "l5_mean_odomkm = autos_converter(c_series=brand_value_counts, mean_column=\"odometer_km\", start_slice=-5, stop_slice=None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# transform l5_mean_price dictionary to dataframe\n",
    "l5_mean_price_series = pd.Series(l5_mean_price)\n",
    "l5_df = pd.DataFrame(l5_mean_price_series, columns=[\"mean_price\"])\n",
    "# add columns for mean odometer_km and relative frequency in original df\n",
    "l5_mean_odomkm_series = pd.Series(l5_mean_odomkm)\n",
    "l5_df[\"mean_odomkm\"] = l5_mean_odomkm_series\n",
    "l5_df[\"%_in_autos\"] = brand_value_counts[-5:]\n",
    "l5_df = l5_df.astype(int)\n",
    "l5_df = l5_df.reset_index()\n",
    "l5_df = l5_df.rename(columns={'index': 'brand'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "t5_df[\"price_mileage_ratio\"] = 0\n",
    "t5_df[\"price_mileage_ratio\"] = t5_df[\"mean_price\"] / t5_df[\"mean_odomkm\"]\n",
    "l5_df[\"price_mileage_ratio\"] = 0\n",
    "l5_df[\"price_mileage_ratio\"] = l5_df[\"mean_price\"] / l5_df[\"mean_odomkm\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# pretty print dataframes for five most sold brands and five least sold brands\n",
    "pd.set_option('display.max_rows', None)\n",
    "pd.set_option('display.max_columns', None)\n",
    "pd.set_option('display.width', 1000)\n",
    "pd.set_option('display.colheader_justify', 'center')\n",
    "pd.set_option('display.precision', 3)\n",
    "display(t5_df)\n",
    "display(l5_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Conclusion\n",
    "Vehicle brand is an easy way for consumers to evaluate a purchase. Using this as a starting point leads to a few coclusions.\n",
    "* The three most popular brands (Volkswagen, BMW, and Opel respectively) represent slightly more than 50% of the vehicles in the data set. Un-commenting \"# print(brand_value_counts)\" displays the frequency table.\n",
    "* Of the five least most popular vehicles, Ladas and Trabants have very low mileage. Their unpopularity is probably due to quality perceptions.\n",
    "* The price_mileage_ratio column shows the relationship between mean_price and mean_odomkm. The closer the number to zero, the lower the vehicle mileage per dollar spent. So, amongst the five most popular brands, audis had the lowest mileage per dollar in the mean price.\n",
    "* Additionally, this allows a comparison of value compared to popularity. The mean price for the five most popular cars is \\\\$6935 and for the five least popular it is \\\\$2101. The price to mileage ratio for the popular cars is 0.05 and for the unpopular cars it is 0.114. So not only is an unpopular car less expensive it is also a better deal if mileage is the only criteria.\n",
    "* Coding a dashboard to combine different columns and row slices to create new ratios would possibly show more unexpected results.\n",
    "* Lastly, it would be interesting to examine the rows with outlier prices. If the price is controled for in some way would that group reveal similar paterns?"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}