Project-An Analysis of Ebay Car Sales (Pandas).ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Ebay Used Car Sales Data Analysis\n",
    "This project is aimed at cleaning and analyze the used car listings data scraped from eBay Kleinanzeigen, a classifieds section of the German eBay website.The dataset was originally downloaded from (Kaggle)[https://www.kaggle.com/orgesleka/used-cars-database/data]\n",
    "\n",
    "## Step-1: Importing Pandas and Numpy Libraries and Reading & Exploring the Data File"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           dateCrawled                            name  seller offerType  \\\n",
      "0  2016-03-24 11:52:17                      Golf_3_1.6  privat   Angebot   \n",
      "1  2016-03-24 10:58:45            A5_Sportback_2.7_Tdi  privat   Angebot   \n",
      "2  2016-03-14 12:52:21  Jeep_Grand_Cherokee_\"Overland\"  privat   Angebot   \n",
      "3  2016-03-17 16:54:04              GOLF_4_1_4__3TÜRER  privat   Angebot   \n",
      "4  2016-03-31 17:25:20  Skoda_Fabia_1.4_TDI_PD_Classic  privat   Angebot   \n",
      "\n",
      "   price abtest vehicleType  yearOfRegistration    gearbox  powerPS  model  \\\n",
      "0    480   test         NaN                1993    manuell        0   golf   \n",
      "1  18300   test       coupe                2011    manuell      190    NaN   \n",
      "2   9800   test         suv                2004  automatik      163  grand   \n",
      "3   1500   test  kleinwagen                2001    manuell       75   golf   \n",
      "4   3600   test  kleinwagen                2008    manuell       69  fabia   \n",
      "\n",
      "   kilometer  monthOfRegistration fuelType       brand notRepairedDamage  \\\n",
      "0     150000                    0   benzin  volkswagen               NaN   \n",
      "1     125000                    5   diesel        audi                ja   \n",
      "2     125000                    8   diesel        jeep               NaN   \n",
      "3     150000                    6   benzin  volkswagen              nein   \n",
      "4      90000                    7   diesel       skoda              nein   \n",
      "\n",
      "           dateCreated  nrOfPictures  postalCode             lastSeen  \n",
      "0  2016-03-24 00:00:00             0       70435  2016-04-07 03:16:57  \n",
      "1  2016-03-24 00:00:00             0       66954  2016-04-07 01:46:50  \n",
      "2  2016-03-14 00:00:00             0       90480  2016-04-05 12:47:46  \n",
      "3  2016-03-17 00:00:00             0       91074  2016-03-17 17:40:17  \n",
      "4  2016-03-31 00:00:00             0       60437  2016-04-06 10:17:21  \n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 371528 entries, 0 to 371527\n",
      "Data columns (total 20 columns):\n",
      " #   Column               Non-Null Count   Dtype \n",
      "---  ------               --------------   ----- \n",
      " 0   dateCrawled          371528 non-null  object\n",
      " 1   name                 371528 non-null  object\n",
      " 2   seller               371528 non-null  object\n",
      " 3   offerType            371528 non-null  object\n",
      " 4   price                371528 non-null  int64 \n",
      " 5   abtest               371528 non-null  object\n",
      " 6   vehicleType          333659 non-null  object\n",
      " 7   yearOfRegistration   371528 non-null  int64 \n",
      " 8   gearbox              351319 non-null  object\n",
      " 9   powerPS              371528 non-null  int64 \n",
      " 10  model                351044 non-null  object\n",
      " 11  kilometer            371528 non-null  int64 \n",
      " 12  monthOfRegistration  371528 non-null  int64 \n",
      " 13  fuelType             338142 non-null  object\n",
      " 14  brand                371528 non-null  object\n",
      " 15  notRepairedDamage    299468 non-null  object\n",
      " 16  dateCreated          371528 non-null  object\n",
      " 17  nrOfPictures         371528 non-null  int64 \n",
      " 18  postalCode           371528 non-null  int64 \n",
      " 19  lastSeen             371528 non-null  object\n",
      "dtypes: int64(7), object(13)\n",
      "memory usage: 56.7+ MB\n"
     ]
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "autos=pd.read_csv(\"autos.csv\",encoding=\"Latin-1\")\n",
    "print(autos.head())\n",
    "autos.info()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Step-2: Random Sampling to reduce Dataframe to 50,000 entries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                dateCrawled  \\\n",
      "192206  2016-03-15 13:53:04   \n",
      "283994  2016-03-26 15:50:54   \n",
      "216814  2016-04-01 16:44:29   \n",
      "24185   2016-03-09 18:40:51   \n",
      "214518  2016-03-08 15:59:01   \n",
      "\n",
      "                                                     name  seller offerType  \\\n",
      "192206                             Audi_A6_4B_2.8_Quattro  privat   Angebot   \n",
      "283994  Mercedes_clk_320_super_Zustand_Motor_und_Getri...  privat   Angebot   \n",
      "216814                                       BMW_116d_DPF  privat   Angebot   \n",
      "24185          Oldtimer_BMW_E12_Ersatzteiltraeger_Bastler  privat   Angebot   \n",
      "214518  Volkswagen_Golf_1.2_TSI_CUP_BlueMotion_Technol...  privat   Angebot   \n",
      "\n",
      "        price   abtest vehicleType  yearOfRegistration    gearbox  powerPS  \\\n",
      "192206   2600  control       kombi                2000  automatik      193   \n",
      "283994   8990     test         NaN                2017  automatik      218   \n",
      "216814   8590  control   limousine                2009    manuell      116   \n",
      "24185       0     test         NaN                1980        NaN        0   \n",
      "214518  14299     test   limousine                2015    manuell       86   \n",
      "\n",
      "       model  kilometer  monthOfRegistration fuelType           brand  \\\n",
      "192206    a6     150000                    4   benzin            audi   \n",
      "283994   NaN      90000                    6      NaN   mercedes_benz   \n",
      "216814   1er     125000                    5   diesel             bmw   \n",
      "24185    NaN       5000                    0      NaN  sonstige_autos   \n",
      "214518  golf      10000                    1   benzin      volkswagen   \n",
      "\n",
      "       notRepairedDamage          dateCreated  nrOfPictures  postalCode  \\\n",
      "192206                ja  2016-03-15 00:00:00             0       46537   \n",
      "283994              nein  2016-03-26 00:00:00             0       45896   \n",
      "216814              nein  2016-04-01 00:00:00             0       42489   \n",
      "24185                NaN  2016-03-09 00:00:00             0       66773   \n",
      "214518              nein  2016-03-08 00:00:00             0       34266   \n",
      "\n",
      "                   lastSeen  \n",
      "192206  2016-04-02 05:17:09  \n",
      "283994  2016-04-06 04:15:43  \n",
      "216814  2016-04-03 12:53:42  \n",
      "24185   2016-03-12 04:15:56  \n",
      "214518  2016-03-27 06:47:13  \n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 50000 entries, 192206 to 150534\n",
      "Data columns (total 20 columns):\n",
      " #   Column               Non-Null Count  Dtype \n",
      "---  ------               --------------  ----- \n",
      " 0   dateCrawled          50000 non-null  object\n",
      " 1   name                 50000 non-null  object\n",
      " 2   seller               50000 non-null  object\n",
      " 3   offerType            50000 non-null  object\n",
      " 4   price                50000 non-null  int64 \n",
      " 5   abtest               50000 non-null  object\n",
      " 6   vehicleType          44884 non-null  object\n",
      " 7   yearOfRegistration   50000 non-null  int64 \n",
      " 8   gearbox              47315 non-null  object\n",
      " 9   powerPS              50000 non-null  int64 \n",
      " 10  model                47259 non-null  object\n",
      " 11  kilometer            50000 non-null  int64 \n",
      " 12  monthOfRegistration  50000 non-null  int64 \n",
      " 13  fuelType             45675 non-null  object\n",
      " 14  brand                50000 non-null  object\n",
      " 15  notRepairedDamage    40350 non-null  object\n",
      " 16  dateCreated          50000 non-null  object\n",
      " 17  nrOfPictures         50000 non-null  int64 \n",
      " 18  postalCode           50000 non-null  int64 \n",
      " 19  lastSeen             50000 non-null  object\n",
      "dtypes: int64(7), object(13)\n",
      "memory usage: 8.0+ MB\n"
     ]
    }
   ],
   "source": [
    "autos=autos.sample(n=50000)\n",
    "print(autos.head())\n",
    "autos.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Observations\n",
    "In the dataframe \"autos\", there are 50,000 entries and 20 columns. 13 columns are of dtype=object while 7 columns are of dtype=int-64. Fol columns contain null entries: \"vehicleType, index=6\", \"gearbox,index=8\", \"model, index=10\", \"fuelType, index=13\", \"notRepairedDamage, index=15\". Column names are in hybrid string format containing lower and upper cases (camelcase).\n",
    "\n",
    "## Step-3: Cleaning Column Names\n",
    "- Convert camelcase to snakecase by using ```df.columns and rename```attributes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['dateCrawled', 'name', 'seller', 'offerType', 'price', 'abtest',\n",
      "       'vehicleType', 'yearOfRegistration', 'gearbox', 'powerPS', 'model',\n",
      "       'kilometer', 'monthOfRegistration', 'fuelType', 'brand',\n",
      "       'notRepairedDamage', 'dateCreated', 'nrOfPictures', 'postalCode',\n",
      "       'lastSeen'],\n",
      "      dtype='object')\n",
      "                 dt_crawled  \\\n",
      "192206  2016-03-15 13:53:04   \n",
      "283994  2016-03-26 15:50:54   \n",
      "216814  2016-04-01 16:44:29   \n",
      "24185   2016-03-09 18:40:51   \n",
      "214518  2016-03-08 15:59:01   \n",
      "\n",
      "                                                     name  seller offer_type  \\\n",
      "192206                             Audi_A6_4B_2.8_Quattro  privat    Angebot   \n",
      "283994  Mercedes_clk_320_super_Zustand_Motor_und_Getri...  privat    Angebot   \n",
      "216814                                       BMW_116d_DPF  privat    Angebot   \n",
      "24185          Oldtimer_BMW_E12_Ersatzteiltraeger_Bastler  privat    Angebot   \n",
      "214518  Volkswagen_Golf_1.2_TSI_CUP_BlueMotion_Technol...  privat    Angebot   \n",
      "\n",
      "        price  ab_test   veh_type  yr_regn   gear_box  power_ps model  \\\n",
      "192206   2600  control      kombi     2000  automatik       193    a6   \n",
      "283994   8990     test        NaN     2017  automatik       218   NaN   \n",
      "216814   8590  control  limousine     2009    manuell       116   1er   \n",
      "24185       0     test        NaN     1980        NaN         0   NaN   \n",
      "214518  14299     test  limousine     2015    manuell        86  golf   \n",
      "\n",
      "        kilometer  month_regn fuel_type           brand not_rep_dmg  \\\n",
      "192206     150000           4    benzin            audi          ja   \n",
      "283994      90000           6       NaN   mercedes_benz        nein   \n",
      "216814     125000           5    diesel             bmw        nein   \n",
      "24185        5000           0       NaN  sonstige_autos         NaN   \n",
      "214518      10000           1    benzin      volkswagen        nein   \n",
      "\n",
      "                 ad_created  no_pics  postal_code            last_seen  \n",
      "192206  2016-03-15 00:00:00        0        46537  2016-04-02 05:17:09  \n",
      "283994  2016-03-26 00:00:00        0        45896  2016-04-06 04:15:43  \n",
      "216814  2016-04-01 00:00:00        0        42489  2016-04-03 12:53:42  \n",
      "24185   2016-03-09 00:00:00        0        66773  2016-03-12 04:15:56  \n",
      "214518  2016-03-08 00:00:00        0        34266  2016-03-27 06:47:13  \n"
     ]
    }
   ],
   "source": [
    "autos_columns=autos.columns\n",
    "print(autos_columns)\n",
    "new_columns={'dateCrawled':'dt_crawled','offerType':'offer-type','abtest':'ab_test','vehicleType':'veh_type','yearOfRegistration':'yr_regn','gearbox':'gear-box','powerPS':'power_ps','monthOfRegistration':'month_regn','fuelType':'fuel_type','notRepairedDamage':'not_rep_dmg','dateCreated':'ad_created','nrOfPictures':'no_pics','postalCode':'postal_code','lastSeen':'last_seen'}\n",
    "autos.rename(columns={'dateCrawled':'dt_crawled','offerType':'offer_type','abtest':'ab_test','vehicleType':'veh_type','yearOfRegistration':'yr_regn','gearbox':'gear_box','powerPS':'power_ps','monthOfRegistration':'month_regn','fuelType':'fuel_type','notRepairedDamage':'not_rep_dmg','dateCreated':'ad_created','nrOfPictures':'no_pics','postalCode':'postal_code','lastSeen':'last_seen'},inplace=True)\n",
    "print(autos.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>dt_crawled</th>\n",
       "      <th>name</th>\n",
       "      <th>seller</th>\n",
       "      <th>offer_type</th>\n",
       "      <th>price</th>\n",
       "      <th>ab_test</th>\n",
       "      <th>veh_type</th>\n",
       "      <th>yr_regn</th>\n",
       "      <th>gear_box</th>\n",
       "      <th>power_ps</th>\n",
       "      <th>model</th>\n",
       "      <th>kilometer</th>\n",
       "      <th>month_regn</th>\n",
       "      <th>fuel_type</th>\n",
       "      <th>brand</th>\n",
       "      <th>not_rep_dmg</th>\n",
       "      <th>ad_created</th>\n",
       "      <th>no_pics</th>\n",
       "      <th>postal_code</th>\n",
       "      <th>last_seen</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>50000</td>\n",
       "      <td>50000</td>\n",
       "      <td>50000</td>\n",
       "      <td>50000</td>\n",
       "      <td>5.000000e+04</td>\n",
       "      <td>50000</td>\n",
       "      <td>44884</td>\n",
       "      <td>50000.000000</td>\n",
       "      <td>47315</td>\n",
       "      <td>50000.000000</td>\n",
       "      <td>47259</td>\n",
       "      <td>50000.000000</td>\n",
       "      <td>50000.000000</td>\n",
       "      <td>45675</td>\n",
       "      <td>50000</td>\n",
       "      <td>40350</td>\n",
       "      <td>50000</td>\n",
       "      <td>50000.0</td>\n",
       "      <td>50000.000000</td>\n",
       "      <td>50000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique</th>\n",
       "      <td>48283</td>\n",
       "      <td>38642</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>247</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>40</td>\n",
       "      <td>2</td>\n",
       "      <td>79</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>39531</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>top</th>\n",
       "      <td>2016-04-03 15:51:53</td>\n",
       "      <td>Volkswagen_Golf_1.4</td>\n",
       "      <td>privat</td>\n",
       "      <td>Angebot</td>\n",
       "      <td>NaN</td>\n",
       "      <td>test</td>\n",
       "      <td>limousine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>manuell</td>\n",
       "      <td>NaN</td>\n",
       "      <td>golf</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>benzin</td>\n",
       "      <td>volkswagen</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-04-03 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-04-06 09:17:45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>freq</th>\n",
       "      <td>3</td>\n",
       "      <td>94</td>\n",
       "      <td>49999</td>\n",
       "      <td>49998</td>\n",
       "      <td>NaN</td>\n",
       "      <td>25825</td>\n",
       "      <td>13047</td>\n",
       "      <td>NaN</td>\n",
       "      <td>36936</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4066</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>29989</td>\n",
       "      <td>10840</td>\n",
       "      <td>35515</td>\n",
       "      <td>2017</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.478622e+04</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2004.053780</td>\n",
       "      <td>NaN</td>\n",
       "      <td>115.952660</td>\n",
       "      <td>NaN</td>\n",
       "      <td>125621.200000</td>\n",
       "      <td>5.740000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>50856.679740</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.000855e+05</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>77.273171</td>\n",
       "      <td>NaN</td>\n",
       "      <td>195.494747</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40132.434798</td>\n",
       "      <td>3.706065</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>25791.558779</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1000.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5000.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1067.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.150000e+03</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1999.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>71.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>125000.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>30559.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.999000e+03</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2003.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>105.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>150000.000000</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>49545.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.200000e+03</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2008.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>150.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>150000.000000</td>\n",
       "      <td>9.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>71634.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.000000e+08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9999.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17932.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>150000.000000</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>99998.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 dt_crawled                 name  seller offer_type  \\\n",
       "count                 50000                50000   50000      50000   \n",
       "unique                48283                38642       2          2   \n",
       "top     2016-04-03 15:51:53  Volkswagen_Golf_1.4  privat    Angebot   \n",
       "freq                      3                   94   49999      49998   \n",
       "mean                    NaN                  NaN     NaN        NaN   \n",
       "std                     NaN                  NaN     NaN        NaN   \n",
       "min                     NaN                  NaN     NaN        NaN   \n",
       "25%                     NaN                  NaN     NaN        NaN   \n",
       "50%                     NaN                  NaN     NaN        NaN   \n",
       "75%                     NaN                  NaN     NaN        NaN   \n",
       "max                     NaN                  NaN     NaN        NaN   \n",
       "\n",
       "               price ab_test   veh_type       yr_regn gear_box      power_ps  \\\n",
       "count   5.000000e+04   50000      44884  50000.000000    47315  50000.000000   \n",
       "unique           NaN       2          8           NaN        2           NaN   \n",
       "top              NaN    test  limousine           NaN  manuell           NaN   \n",
       "freq             NaN   25825      13047           NaN    36936           NaN   \n",
       "mean    1.478622e+04     NaN        NaN   2004.053780      NaN    115.952660   \n",
       "std     9.000855e+05     NaN        NaN     77.273171      NaN    195.494747   \n",
       "min     0.000000e+00     NaN        NaN   1000.000000      NaN      0.000000   \n",
       "25%     1.150000e+03     NaN        NaN   1999.000000      NaN     71.000000   \n",
       "50%     2.999000e+03     NaN        NaN   2003.000000      NaN    105.000000   \n",
       "75%     7.200000e+03     NaN        NaN   2008.000000      NaN    150.000000   \n",
       "max     1.000000e+08     NaN        NaN   9999.000000      NaN  17932.000000   \n",
       "\n",
       "        model      kilometer    month_regn fuel_type       brand not_rep_dmg  \\\n",
       "count   47259   50000.000000  50000.000000     45675       50000       40350   \n",
       "unique    247            NaN           NaN         7          40           2   \n",
       "top      golf            NaN           NaN    benzin  volkswagen        nein   \n",
       "freq     4066            NaN           NaN     29989       10840       35515   \n",
       "mean      NaN  125621.200000      5.740000       NaN         NaN         NaN   \n",
       "std       NaN   40132.434798      3.706065       NaN         NaN         NaN   \n",
       "min       NaN    5000.000000      0.000000       NaN         NaN         NaN   \n",
       "25%       NaN  125000.000000      3.000000       NaN         NaN         NaN   \n",
       "50%       NaN  150000.000000      6.000000       NaN         NaN         NaN   \n",
       "75%       NaN  150000.000000      9.000000       NaN         NaN         NaN   \n",
       "max       NaN  150000.000000     12.000000       NaN         NaN         NaN   \n",
       "\n",
       "                 ad_created  no_pics   postal_code            last_seen  \n",
       "count                 50000  50000.0  50000.000000                50000  \n",
       "unique                   79      NaN           NaN                39531  \n",
       "top     2016-04-03 00:00:00      NaN           NaN  2016-04-06 09:17:45  \n",
       "freq                   2017      NaN           NaN                    8  \n",
       "mean                    NaN      0.0  50856.679740                  NaN  \n",
       "std                     NaN      0.0  25791.558779                  NaN  \n",
       "min                     NaN      0.0   1067.000000                  NaN  \n",
       "25%                     NaN      0.0  30559.000000                  NaN  \n",
       "50%                     NaN      0.0  49545.000000                  NaN  \n",
       "75%                     NaN      0.0  71634.000000                  NaN  \n",
       "max                     NaN      0.0  99998.000000                  NaN  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos.describe(include='all')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We have to remove strings from \"price\" and \"kilometer\" columns (already removed), convert them into numeric columns (already numeric) and rename them to \"price_$\" and \"odometer_km\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "count    5.000000e+04\n",
      "mean     1.478622e+04\n",
      "std      9.000855e+05\n",
      "min      0.000000e+00\n",
      "25%      1.150000e+03\n",
      "50%      2.999000e+03\n",
      "75%      7.200000e+03\n",
      "max      1.000000e+08\n",
      "Name: price_$, dtype: float64\n",
      "count     50000.000000\n",
      "mean     125621.200000\n",
      "std       40132.434798\n",
      "min        5000.000000\n",
      "25%      125000.000000\n",
      "50%      150000.000000\n",
      "75%      150000.000000\n",
      "max      150000.000000\n",
      "Name: odometer_km, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "autos.rename({\"kilometer\":\"odometer_km\"},axis=1,inplace=True)\n",
    "autos.rename({\"price\":\"price_$\"},axis=1,inplace=True)\n",
    "price_desc=autos[\"price_$\"].describe()\n",
    "print(price_desc)\n",
    "km_desc=autos[\"odometer_km\"].describe()\n",
    "print(km_desc)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We now examine the columns \"price_$\" and \"odometer_$\" in order to identify unrealistic entries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(2394,)\n",
      "99999999    4\n",
      "12345678    2\n",
      "9999999     2\n",
      "1300000     1\n",
      "999999      3\n",
      "999990      1\n",
      "911911      1\n",
      "585000      1\n",
      "579000      1\n",
      "466000      1\n",
      "445000      1\n",
      "260000      1\n",
      "230000      2\n",
      "229900      1\n",
      "200000      1\n",
      "198000      1\n",
      "192500      1\n",
      "174000      2\n",
      "169999      1\n",
      "169000      1\n",
      "Name: price_$, dtype: int64\n",
      "0      1419\n",
      "1       172\n",
      "2         1\n",
      "5         1\n",
      "8         3\n",
      "9         1\n",
      "10       13\n",
      "13        1\n",
      "14        1\n",
      "15        5\n",
      "17        2\n",
      "19        1\n",
      "20       11\n",
      "24        1\n",
      "25        5\n",
      "26        1\n",
      "30        5\n",
      "35        4\n",
      "39        1\n",
      "40        6\n",
      "45        1\n",
      "50       35\n",
      "55        3\n",
      "60        7\n",
      "65        4\n",
      "66        1\n",
      "70        9\n",
      "74        1\n",
      "75       12\n",
      "80       26\n",
      "85        1\n",
      "90        4\n",
      "95        1\n",
      "99       20\n",
      "100     126\n",
      "106       1\n",
      "110       5\n",
      "111       1\n",
      "117       1\n",
      "119       1\n",
      "120      39\n",
      "125       8\n",
      "130      14\n",
      "135       2\n",
      "140       4\n",
      "149       8\n",
      "150     224\n",
      "155       2\n",
      "160       9\n",
      "165       1\n",
      "Name: price_$, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "price_unique_shape=autos[\"price_$\"].unique().shape\n",
    "print(price_unique_shape)\n",
    "top_price=autos[\"price_$\"].value_counts().sort_index(ascending=False)\n",
    "bottom_price=autos[\"price_$\"].value_counts().sort_index(ascending=True)\n",
    "print(top_price.head(20))\n",
    "print(bottom_price.head(50))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the \"price_$\" column prices above $1 million and below $100 appear to be unrealistic."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(13,)\n",
      "150000    32406\n",
      "125000     5134\n",
      "100000     2135\n",
      "90000      1695\n",
      "80000      1467\n",
      "70000      1341\n",
      "60000      1180\n",
      "50000      1002\n",
      "40000       847\n",
      "30000       783\n",
      "20000       767\n",
      "10000       261\n",
      "5000        982\n",
      "Name: odometer_km, dtype: int64\n",
      "5000        982\n",
      "10000       261\n",
      "20000       767\n",
      "30000       783\n",
      "40000       847\n",
      "50000      1002\n",
      "60000      1180\n",
      "70000      1341\n",
      "80000      1467\n",
      "90000      1695\n",
      "100000     2135\n",
      "125000     5134\n",
      "150000    32406\n",
      "Name: odometer_km, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "km_unique_shape=autos[\"odometer_km\"].unique().shape\n",
    "print(km_unique_shape)\n",
    "most_used=autos[\"odometer_km\"].value_counts().sort_index(ascending=False)\n",
    "least_used=autos[\"odometer_km\"].value_counts().sort_index(ascending=True)\n",
    "print(most_used.head(20))\n",
    "print(least_used.head(20))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the \"odometer_km\" column all entries are between 5000 and 150000 and seem reasonable.\n",
    "Therefore, we filter the dataframe \"autos\" based on \"price_$\" column removing entries above and including $999,999, and below and including $100. This leaves us with 48103 entries instead of the original 50,000."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "False    48083\n",
      "True      1917\n",
      "Name: price_$, dtype: int64\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 48083 entries, 192206 to 150534\n",
      "Data columns (total 20 columns):\n",
      " #   Column       Non-Null Count  Dtype \n",
      "---  ------       --------------  ----- \n",
      " 0   dt_crawled   48083 non-null  object\n",
      " 1   name         48083 non-null  object\n",
      " 2   seller       48083 non-null  object\n",
      " 3   offer_type   48083 non-null  object\n",
      " 4   price_$      48083 non-null  int64 \n",
      " 5   ab_test      48083 non-null  object\n",
      " 6   veh_type     43641 non-null  object\n",
      " 7   yr_regn      48083 non-null  int64 \n",
      " 8   gear_box     45889 non-null  object\n",
      " 9   power_ps     48083 non-null  int64 \n",
      " 10  model        45728 non-null  object\n",
      " 11  odometer_km  48083 non-null  int64 \n",
      " 12  month_regn   48083 non-null  int64 \n",
      " 13  fuel_type    44396 non-null  object\n",
      " 14  brand        48083 non-null  object\n",
      " 15  not_rep_dmg  39403 non-null  object\n",
      " 16  ad_created   48083 non-null  object\n",
      " 17  no_pics      48083 non-null  int64 \n",
      " 18  postal_code  48083 non-null  int64 \n",
      " 19  last_seen    48083 non-null  object\n",
      "dtypes: int64(7), object(13)\n",
      "memory usage: 7.7+ MB\n",
      "None\n",
      "count     48083.000000\n",
      "mean       6037.404779\n",
      "std       11665.202317\n",
      "min         106.000000\n",
      "25%        1300.000000\n",
      "50%        3200.000000\n",
      "75%        7500.000000\n",
      "max      999990.000000\n",
      "Name: price_$, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "unrealistic_prices=autos[\"price_$\"].between(0,100)|autos[\"price_$\"].between(999999,99999999)\n",
    "print(unrealistic_prices.value_counts())\n",
    "autos=autos.loc[~unrealistic_prices]\n",
    "print(autos.info())\n",
    "print(autos[\"price_$\"].describe())\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step-4: Analyzing Date Columns\n",
    "We will analyze 5 columns with date values i.e. ```date_crawled```, ```ad_created```, and ```last_seen``` which are \"object\" types and ```yr_regn``` and ```month_regn``` which are numeric types."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2016-03-05    2.616309\n",
      "2016-03-06    1.318553\n",
      "2016-03-07    3.518915\n",
      "2016-03-08    3.146642\n",
      "2016-03-09    3.396211\n",
      "2016-03-10    3.410769\n",
      "2016-03-11    3.296383\n",
      "2016-03-12    3.747686\n",
      "2016-03-13    1.478693\n",
      "2016-03-14    3.560510\n",
      "2016-03-15    3.493958\n",
      "2016-03-16    2.980263\n",
      "2016-03-17    3.206955\n",
      "2016-03-18    1.260321\n",
      "2016-03-19    3.466922\n",
      "2016-03-20    3.589626\n",
      "2016-03-21    3.597945\n",
      "2016-03-22    3.231911\n",
      "2016-03-23    3.127925\n",
      "2016-03-24    3.005220\n",
      "2016-03-25    3.340058\n",
      "2016-03-26    3.119606\n",
      "2016-03-27    3.015619\n",
      "2016-03-28    3.487719\n",
      "2016-03-29    3.446124\n",
      "2016-03-30    3.412849\n",
      "2016-03-31    3.209034\n",
      "2016-04-01    3.400370\n",
      "2016-04-02    3.541792\n",
      "2016-04-03    4.032610\n",
      "2016-04-04    3.849593\n",
      "2016-04-05    1.264480\n",
      "2016-04-06    0.280765\n",
      "2016-04-07    0.147661\n",
      "Name: dt_crawled, dtype: float64\n",
      "count                   48083\n",
      "unique                  46493\n",
      "top       2016-04-06 23:06:20\n",
      "freq                        3\n",
      "Name: dt_crawled, dtype: object\n"
     ]
    }
   ],
   "source": [
    "dt_crawled_range=(autos[\"dt_crawled\"].str[:10].value_counts(normalize=True,dropna=False)*100).sort_index(ascending=True)\n",
    "print(dt_crawled_range)\n",
    "print(autos[\"dt_crawled\"].describe())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Observation - ```dt_crawled``` Column\n",
    "- Crawled daily from 2016-03-05 to 2016-04-07\n",
    "- No missing entries\n",
    "- No change needed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2015-06-11    0.002080\n",
      "2015-09-04    0.002080\n",
      "2015-09-09    0.002080\n",
      "2015-10-14    0.002080\n",
      "2015-11-10    0.002080\n",
      "                ...   \n",
      "2016-04-03    4.076285\n",
      "2016-04-04    3.857912\n",
      "2016-04-05    1.133457\n",
      "2016-04-06    0.278685\n",
      "2016-04-07    0.141422\n",
      "Name: ad_created, Length: 79, dtype: float64\n",
      "count                   48083\n",
      "unique                     79\n",
      "top       2016-04-03 00:00:00\n",
      "freq                     1960\n",
      "Name: ad_created, dtype: object\n"
     ]
    }
   ],
   "source": [
    "ad_created_range=(autos[\"ad_created\"].str[:10].value_counts(normalize=True,dropna=False)*100).sort_index(ascending=True)\n",
    "print(ad_created_range)\n",
    "print(autos[\"ad_created\"].describe())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Observation - ```ad_created``` Column\n",
    "- Created at irregular intervals between 2015-08-07 to 2016-04-07\n",
    "- No missing entries\n",
    "- No change needed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2016-03-05     0.153901\n",
      "2016-03-06     0.397230\n",
      "2016-03-07     0.513695\n",
      "2016-03-08     0.792380\n",
      "2016-03-09     0.944201\n",
      "2016-03-10     1.093942\n",
      "2016-03-11     1.331032\n",
      "2016-03-12     2.308508\n",
      "2016-03-13     0.784061\n",
      "2016-03-14     1.272799\n",
      "2016-03-15     1.709544\n",
      "2016-03-16     1.543165\n",
      "2016-03-17     2.963625\n",
      "2016-03-18     0.592725\n",
      "2016-03-19     1.536926\n",
      "2016-03-20     2.052701\n",
      "2016-03-21     1.992388\n",
      "2016-03-22     2.056860\n",
      "2016-03-23     1.742820\n",
      "2016-03-24     1.873843\n",
      "2016-03-25     1.900880\n",
      "2016-03-26     1.595158\n",
      "2016-03-27     1.634673\n",
      "2016-03-28     2.144209\n",
      "2016-03-29     2.343864\n",
      "2016-03-30     2.408336\n",
      "2016-03-31     2.487366\n",
      "2016-04-01     2.345943\n",
      "2016-04-02     2.501924\n",
      "2016-04-03     2.614230\n",
      "2016-04-04     2.491525\n",
      "2016-04-05    12.875653\n",
      "2016-04-06    21.937067\n",
      "2016-04-07    13.062829\n",
      "Name: last_seen, dtype: float64\n",
      "count                   48083\n",
      "unique                  38166\n",
      "top       2016-04-06 09:17:45\n",
      "freq                        8\n",
      "Name: last_seen, dtype: object\n"
     ]
    }
   ],
   "source": [
    "last_seen_range=(autos[\"last_seen\"].str[:10].value_counts(normalize=True,dropna=False)*100).sort_index(ascending=True)\n",
    "print(last_seen_range)\n",
    "print(autos[\"last_seen\"].describe())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Observation - ```last_seen``` Column\n",
    "- Certain percentage of ads seen daily on the same dates as dt_crawled. But not linked.\n",
    "- Maximum %age of ads seen on last 3 days of crawling i.e. 2016-04-05, 2016-04-06 and 2016-04-07.    \n",
    "- No missing entries\n",
    "- No change needed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "count    48083.000000\n",
      "mean      2003.810411\n",
      "std         45.279200\n",
      "min       1000.000000\n",
      "25%       1999.000000\n",
      "50%       2004.000000\n",
      "75%       2008.000000\n",
      "max       9999.000000\n",
      "Name: yr_regn, dtype: float64\n",
      "1000     3\n",
      "1800     2\n",
      "1910     4\n",
      "1930     1\n",
      "1931     1\n",
      "1932     1\n",
      "1936     1\n",
      "1937     1\n",
      "1938     1\n",
      "1940     1\n",
      "1941     1\n",
      "1942     1\n",
      "1946     1\n",
      "1948     1\n",
      "1949     1\n",
      "1950     2\n",
      "1951     3\n",
      "1953     2\n",
      "1955     3\n",
      "1956     3\n",
      "1957     3\n",
      "1958     2\n",
      "1959     8\n",
      "1960    20\n",
      "1961     4\n",
      "1962    10\n",
      "1963     7\n",
      "1964    17\n",
      "1965    19\n",
      "1966    22\n",
      "1967    24\n",
      "1968    10\n",
      "1969    24\n",
      "1970    38\n",
      "1971    26\n",
      "1972    31\n",
      "1973    23\n",
      "1974    30\n",
      "1975    17\n",
      "1976    24\n",
      "1977    29\n",
      "1978    37\n",
      "1979    37\n",
      "1980    49\n",
      "1981    31\n",
      "1982    40\n",
      "1983    64\n",
      "1984    49\n",
      "1985    97\n",
      "1986    87\n",
      "Name: yr_regn, dtype: int64\n",
      "2005    2926\n",
      "2006    2736\n",
      "2007    2395\n",
      "2008    2196\n",
      "2009    2084\n",
      "2010    1637\n",
      "2011    1593\n",
      "2012    1249\n",
      "2013     794\n",
      "2014     621\n",
      "2015     382\n",
      "2016    1301\n",
      "2017    1339\n",
      "2018     497\n",
      "2019       2\n",
      "3000       1\n",
      "3700       1\n",
      "5000       1\n",
      "6000       1\n",
      "9999       1\n",
      "Name: yr_regn, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(autos[\"yr_regn\"].describe())\n",
    "yr_regn_range=autos[\"yr_regn\"].value_counts(normalize=False,dropna=False).sort_index(ascending=True)\n",
    "print(yr_regn_range.head(50))\n",
    "print(yr_regn_range.tail(20))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Observation - ```yr_regn``` Column\n",
    "- Unrealistic entries at both ends\n",
    "- Entries before 1950 are few (27) and can be merged with 1950  \n",
    "- Entries after 2016 are unrealistic and may be merged with 2016\n",
    "- No missing entries\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "count    48083.000000\n",
      "mean      2003.466880\n",
      "std          7.413427\n",
      "min       1950.000000\n",
      "25%       1999.000000\n",
      "50%       2004.000000\n",
      "75%       2008.000000\n",
      "max       2016.000000\n",
      "Name: yr_regn, dtype: float64\n",
      "1950    23\n",
      "1951     3\n",
      "1953     2\n",
      "1955     3\n",
      "1956     3\n",
      "1957     3\n",
      "1958     2\n",
      "1959     8\n",
      "1960    20\n",
      "1961     4\n",
      "1962    10\n",
      "1963     7\n",
      "1964    17\n",
      "1965    19\n",
      "1966    22\n",
      "1967    24\n",
      "1968    10\n",
      "1969    24\n",
      "1970    38\n",
      "1971    26\n",
      "Name: yr_regn, dtype: int64\n",
      "1997    1803\n",
      "1998    2297\n",
      "1999    2982\n",
      "2000    3025\n",
      "2001    2634\n",
      "2002    2471\n",
      "2003    2588\n",
      "2004    2563\n",
      "2005    2926\n",
      "2006    2736\n",
      "2007    2395\n",
      "2008    2196\n",
      "2009    2084\n",
      "2010    1637\n",
      "2011    1593\n",
      "2012    1249\n",
      "2013     794\n",
      "2014     621\n",
      "2015     382\n",
      "2016    3144\n",
      "Name: yr_regn, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "autos.loc[autos[\"yr_regn\"]>2016,\"yr_regn\"]=2016\n",
    "autos.loc[autos[\"yr_regn\"]<1950,\"yr_regn\"]=1950\n",
    "print(autos[\"yr_regn\"].describe())\n",
    "yr_regn_range=autos[\"yr_regn\"].value_counts(normalize=False,dropna=False).sort_index(ascending=True)\n",
    "print(yr_regn_range.head(20))\n",
    "print(yr_regn_range.tail(20))\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "count    48083.000000\n",
      "mean         5.833247\n",
      "std          3.664730\n",
      "min          0.000000\n",
      "25%          3.000000\n",
      "50%          6.000000\n",
      "75%          9.000000\n",
      "max         12.000000\n",
      "Name: month_regn, dtype: float64\n",
      "0     4221\n",
      "1     3108\n",
      "2     2922\n",
      "3     4794\n",
      "4     4105\n",
      "5     4013\n",
      "6     4384\n",
      "7     3783\n",
      "8     3175\n",
      "9     3270\n",
      "10    3626\n",
      "11    3398\n",
      "12    3284\n",
      "Name: month_regn, dtype: int64\n",
      "0     4221\n",
      "1     3108\n",
      "2     2922\n",
      "3     4794\n",
      "4     4105\n",
      "5     4013\n",
      "6     4384\n",
      "7     3783\n",
      "8     3175\n",
      "9     3270\n",
      "10    3626\n",
      "11    3398\n",
      "12    3284\n",
      "Name: month_regn, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(autos[\"month_regn\"].describe())\n",
    "month_regn_range=autos[\"month_regn\"].value_counts(normalize=False,dropna=False).sort_index(ascending=True)\n",
    "print(month_regn_range.head(50))\n",
    "print(month_regn_range.tail(20))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Observation - ```month_regn``` Column\n",
    "- 4241 unrealistic entries showing month=0\n",
    "- month-regn is not a significant metric as yr_regn is sufficient for most buyers\n",
    "- Entries with month_regn=0 can be merged with month_regn=1 \n",
    "- No missing entries\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "count    48083.000000\n",
      "mean         5.921032\n",
      "std          3.533575\n",
      "min          1.000000\n",
      "25%          3.000000\n",
      "50%          6.000000\n",
      "75%          9.000000\n",
      "max         12.000000\n",
      "Name: month_regn, dtype: float64\n",
      "1     7329\n",
      "2     2922\n",
      "3     4794\n",
      "4     4105\n",
      "5     4013\n",
      "6     4384\n",
      "7     3783\n",
      "8     3175\n",
      "9     3270\n",
      "10    3626\n",
      "11    3398\n",
      "12    3284\n",
      "Name: month_regn, dtype: int64\n",
      "1     7329\n",
      "2     2922\n",
      "3     4794\n",
      "4     4105\n",
      "5     4013\n",
      "6     4384\n",
      "7     3783\n",
      "8     3175\n",
      "9     3270\n",
      "10    3626\n",
      "11    3398\n",
      "12    3284\n",
      "Name: month_regn, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "autos.loc[autos[\"month_regn\"]==0,\"month_regn\"]=1\n",
    "print(autos[\"month_regn\"].describe())\n",
    "month_regn_range=autos[\"month_regn\"].value_counts(normalize=False,dropna=False).sort_index(ascending=True)\n",
    "print(month_regn_range.head(20))\n",
    "print(month_regn_range.tail(20))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step-5: Aggregation\n",
    "First we explore the ```brand``` column in order to select values for aggregation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "count          48083\n",
      "unique            40\n",
      "top       volkswagen\n",
      "freq           10382\n",
      "Name: brand, dtype: object\n",
      "['audi' 'mercedes_benz' 'bmw' 'volkswagen' 'fiat' 'ford' 'mitsubishi'\n",
      " 'honda' 'peugeot' 'opel' 'seat' 'suzuki' 'citroen' 'hyundai' 'nissan'\n",
      " 'renault' 'daihatsu' 'toyota' 'smart' 'jeep' 'volvo' 'skoda' 'mini'\n",
      " 'chrysler' 'mazda' 'alfa_romeo' 'kia' 'land_rover' 'porsche'\n",
      " 'sonstige_autos' 'chevrolet' 'subaru' 'saab' 'lancia' 'dacia' 'trabant'\n",
      " 'daewoo' 'jaguar' 'rover' 'lada']\n",
      "volkswagen        10382\n",
      "bmw                5251\n",
      "opel               5101\n",
      "mercedes_benz      4658\n",
      "audi               4282\n",
      "ford               3286\n",
      "renault            2291\n",
      "peugeot            1476\n",
      "fiat               1238\n",
      "seat                909\n",
      "skoda               747\n",
      "mazda               716\n",
      "smart               681\n",
      "nissan              676\n",
      "citroen             642\n",
      "toyota              622\n",
      "hyundai             455\n",
      "sonstige_autos      455\n",
      "mini                417\n",
      "volvo               413\n",
      "mitsubishi          386\n",
      "honda               370\n",
      "kia                 321\n",
      "alfa_romeo          311\n",
      "suzuki              308\n",
      "porsche             286\n",
      "chevrolet           262\n",
      "chrysler            182\n",
      "dacia               129\n",
      "jeep                107\n",
      "land_rover          105\n",
      "subaru               97\n",
      "daihatsu             95\n",
      "jaguar               85\n",
      "saab                 76\n",
      "daewoo               69\n",
      "rover                62\n",
      "trabant              56\n",
      "lancia               50\n",
      "lada                 28\n",
      "Name: brand, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(autos[\"brand\"].describe())\n",
    "brands=autos[\"brand\"].unique()\n",
    "brand_values=autos[\"brand\"].value_counts()\n",
    "print(brands)\n",
    "print(brand_values)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We decide to aggregate the brands with ```value_counts()>=400```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['audi' 'mercedes_benz' 'bmw' 'volkswagen' 'fiat' 'ford' 'mitsubishi'\n",
      " 'honda' 'peugeot' 'opel' 'seat' 'suzuki' 'citroen' 'hyundai' 'nissan'\n",
      " 'renault' 'daihatsu' 'toyota' 'smart' 'jeep']\n"
     ]
    }
   ],
   "source": [
    "brand_bool=brand_values>=400\n",
    "top_brands=brands[brand_bool]\n",
    "print(top_brands)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The aggregation would define the comparative mean price of a brand with respect to overall mean price"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'audi': 1.504344591376724, 'mercedes_benz': 1.4078260131649476, 'bmw': 1.3627892831741966, 'volkswagen': 0.9086810833328435, 'fiat': 0.48122668012482095, 'ford': 0.6047179369899489, 'mitsubishi': 0.6324321201379595, 'honda': 0.7108414947756723, 'peugeot': 0.5586498696515743, 'opel': 0.5099275928959764, 'seat': 0.7451616793594882, 'suzuki': 0.7330867682855127, 'citroen': 0.6509953487670517, 'hyundai': 0.8808504223106942, 'nissan': 0.761845722476021, 'renault': 0.40907294920923465, 'daihatsu': 0.34599564976295294, 'toyota': 0.9673041057232675, 'smart': 0.5983581083074602, 'jeep': 1.8430181735106725}\n"
     ]
    }
   ],
   "source": [
    "price_mean=autos[\"price_$\"].mean()\n",
    "\n",
    "price_factor_by_brand={}\n",
    "\n",
    "for b in top_brands:\n",
    "    selected_rows=autos[autos[\"brand\"]==b]\n",
    "    mean_price=selected_rows[\"price_$\"].mean()\n",
    "    price_factor=mean_price/price_mean\n",
    "    price_factor_by_brand[b]=price_factor\n",
    "print(price_factor_by_brand )   \n",
    "\n",
    "  \n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step-6: Storing Aggregated Data in Data Frame\n",
    "First we create dictionaries for aggregated ```mean_price_by_brand``` and ```mean_mileage_by_brand``` for ```top_brands```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'audi': 9082.337225595516, 'mercedes_benz': 8499.615500214684, 'bmw': 8227.710531327366, 'volkswagen': 5486.0755153149685, 'fiat': 2905.3602584814216, 'ford': 3650.926962872794, 'mitsubishi': 3818.2487046632123, 'honda': 4291.637837837838, 'peugeot': 3372.7953929539294, 'opel': 3078.6392864144286, 'seat': 4498.842684268427, 'suzuki': 4425.941558441558, 'citroen': 3930.322429906542, 'hyundai': 5318.05054945055, 'nissan': 4599.57100591716, 'renault': 2469.7389786119597, 'daihatsu': 2088.915789473684, 'toyota': 5840.006430868168, 'smart': 3612.5301027900146, 'jeep': 11127.046728971962}\n"
     ]
    }
   ],
   "source": [
    "mean_price_by_brand={}\n",
    "\n",
    "for b in top_brands:\n",
    "    selected_rows=autos[autos[\"brand\"]==b]\n",
    "    mean_price=selected_rows[\"price_$\"].mean()\n",
    "    mean_price_by_brand[b]=mean_price\n",
    "print(mean_price_by_brand)   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'audi': 130519.61700140122, 'mercedes_benz': 131335.33705452984, 'bmw': 133250.8093696439, 'volkswagen': 128748.79599306492, 'fiat': 115860.25848142165, 'ford': 123889.22702373707, 'mitsubishi': 123095.85492227979, 'honda': 125378.37837837837, 'peugeot': 123028.45528455285, 'opel': 128107.23387571065, 'seat': 121133.11331133114, 'suzuki': 104074.67532467532, 'citroen': 119524.92211838007, 'hyundai': 104945.05494505494, 'nissan': 120576.92307692308, 'renault': 128402.44434744654, 'daihatsu': 111157.8947368421, 'toyota': 119750.8038585209, 'smart': 100462.5550660793, 'jeep': 125981.30841121495}\n"
     ]
    }
   ],
   "source": [
    "mean_mileage_by_brand={}\n",
    "\n",
    "for b in top_brands:\n",
    "    selected_rows=autos[autos[\"brand\"]==b]\n",
    "    mean_mileage=selected_rows[\"odometer_km\"].mean()\n",
    "    mean_mileage_by_brand[b]=mean_mileage\n",
    "print(mean_mileage_by_brand) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, we convert the ```mean_price_by_brand``` dictionary, first to a series object and then to a dataframe "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "audi              9082.337226\n",
      "mercedes_benz     8499.615500\n",
      "bmw               8227.710531\n",
      "volkswagen        5486.075515\n",
      "fiat              2905.360258\n",
      "ford              3650.926963\n",
      "mitsubishi        3818.248705\n",
      "honda             4291.637838\n",
      "peugeot           3372.795393\n",
      "opel              3078.639286\n",
      "seat              4498.842684\n",
      "suzuki            4425.941558\n",
      "citroen           3930.322430\n",
      "hyundai           5318.050549\n",
      "nissan            4599.571006\n",
      "renault           2469.738979\n",
      "daihatsu          2088.915789\n",
      "toyota            5840.006431\n",
      "smart             3612.530103\n",
      "jeep             11127.046729\n",
      "dtype: float64\n",
      "               mean_price_by_brand\n",
      "audi                   9082.337226\n",
      "mercedes_benz          8499.615500\n",
      "bmw                    8227.710531\n",
      "volkswagen             5486.075515\n",
      "fiat                   2905.360258\n",
      "ford                   3650.926963\n",
      "mitsubishi             3818.248705\n",
      "honda                  4291.637838\n",
      "peugeot                3372.795393\n",
      "opel                   3078.639286\n",
      "seat                   4498.842684\n",
      "suzuki                 4425.941558\n",
      "citroen                3930.322430\n",
      "hyundai                5318.050549\n",
      "nissan                 4599.571006\n",
      "renault                2469.738979\n",
      "daihatsu               2088.915789\n",
      "toyota                 5840.006431\n",
      "smart                  3612.530103\n",
      "jeep                  11127.046729\n"
     ]
    }
   ],
   "source": [
    "brand_mean_price_series=pd.Series(mean_price_by_brand)\n",
    "print(brand_mean_price_series)\n",
    "brand_mean_price_dataframe=pd.DataFrame(brand_mean_price_series,columns=['mean_price_by_brand'])\n",
    "print(brand_mean_price_dataframe)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, we convert the ```mean_mileage_by_brand``` dictionary, first to a series object and then as a column to the ```brand_mean_price_dataframe``` "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "               mean_price_by_brand  brand_mean_mileage\n",
      "jeep                  11127.046729       125981.308411\n",
      "audi                   9082.337226       130519.617001\n",
      "mercedes_benz          8499.615500       131335.337055\n",
      "bmw                    8227.710531       133250.809370\n",
      "toyota                 5840.006431       119750.803859\n",
      "volkswagen             5486.075515       128748.795993\n",
      "hyundai                5318.050549       104945.054945\n",
      "nissan                 4599.571006       120576.923077\n",
      "seat                   4498.842684       121133.113311\n",
      "suzuki                 4425.941558       104074.675325\n",
      "honda                  4291.637838       125378.378378\n",
      "citroen                3930.322430       119524.922118\n",
      "mitsubishi             3818.248705       123095.854922\n",
      "ford                   3650.926963       123889.227024\n",
      "smart                  3612.530103       100462.555066\n",
      "peugeot                3372.795393       123028.455285\n",
      "opel                   3078.639286       128107.233876\n",
      "fiat                   2905.360258       115860.258481\n",
      "renault                2469.738979       128402.444347\n",
      "daihatsu               2088.915789       111157.894737\n"
     ]
    }
   ],
   "source": [
    "brand_mean_mileage_series=pd.Series(mean_mileage_by_brand)\n",
    "brand_mean_price_dataframe[\"brand_mean_mileage\"]=brand_mean_mileage_series\n",
    "\n",
    "print(brand_mean_price_dataframe.sort_values(\"mean_price_by_brand\",ascending=False))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "               mean_price_by_brand  brand_mean_mileage\n",
      "bmw                    8227.710531       133250.809370\n",
      "mercedes_benz          8499.615500       131335.337055\n",
      "audi                   9082.337226       130519.617001\n",
      "volkswagen             5486.075515       128748.795993\n",
      "renault                2469.738979       128402.444347\n",
      "opel                   3078.639286       128107.233876\n",
      "jeep                  11127.046729       125981.308411\n",
      "honda                  4291.637838       125378.378378\n",
      "ford                   3650.926963       123889.227024\n",
      "mitsubishi             3818.248705       123095.854922\n",
      "peugeot                3372.795393       123028.455285\n",
      "seat                   4498.842684       121133.113311\n",
      "nissan                 4599.571006       120576.923077\n",
      "toyota                 5840.006431       119750.803859\n",
      "citroen                3930.322430       119524.922118\n",
      "fiat                   2905.360258       115860.258481\n",
      "daihatsu               2088.915789       111157.894737\n",
      "hyundai                5318.050549       104945.054945\n",
      "suzuki                 4425.941558       104074.675325\n",
      "smart                  3612.530103       100462.555066\n"
     ]
    }
   ],
   "source": [
    "print(brand_mean_price_dataframe.sort_values(\"brand_mean_mileage\",ascending=False))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Most expensive brand is \"sonstige_autos\" with a mean price of 16472.547569 and mean mileage of 86976.744186\n",
    "- Least expensive brand is \"daihatsu\" with a mean price of 2156.829060  and mean mileage of         112094.017094\n",
    "- Most mileage is brand \"volvo \" with a mean mileage of 136264.108352 and mean price of         5712.830700\n",
    "- Least mileage is brand \"sonstige_autos\" with a mean mileage of 86976.744186 and which is also the most expensive brand with a mean price of 16472.547569"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}