pre-owned_cars_price_estimation_and_analysis_datacleaningphase2.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "_cell_guid": "b1076dfc-b9ad-4769-8c92-a6c4dae69d19",
    "_uuid": "8f2839f25d086af736a60e9eeb907d3b93b6e0e5"
   },
   "outputs": [],
   "source": [
    "# This Python 3 environment comes with many helpful analytics libraries installed\n",
    "# It is defined by the kaggle/python Docker image: /~https://github.com/kaggle/docker-python\n",
    "# For example, here's several helpful packages to load\n",
    "\n",
    "import numpy as np # linear algebra\n",
    "import pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n",
    "from bs4 import BeautifulSoup # web-scraping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_cell_guid": "b1076dfc-b9ad-4769-8c92-a6c4dae69d19",
    "_uuid": "8f2839f25d086af736a60e9eeb907d3b93b6e0e5"
   },
   "outputs": [],
   "source": [
    "# Input data files are available in the read-only \"../input/\" directory\n",
    "# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory\n",
    "\n",
    "import os\n",
    "for dirname, _, filenames in os.walk('/kaggle/input'):\n",
    "    for filename in filenames:\n",
    "        print(os.path.join(dirname, filename))\n",
    "\n",
    "# You can write up to 5GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using \"Save & Run All\" \n",
    "# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "_cell_guid": "79c7e3d0-c299-4dcb-8224-4455121ee9b0",
    "_uuid": "d629ff2d2480ee46fbb7e2d37f6b5fab8052498a"
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "modellingph2=pd.read_csv(\"D:/DPA/vehiclesModelling (1).csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>price</th>\n",
       "      <th>year</th>\n",
       "      <th>manufacturer</th>\n",
       "      <th>condition</th>\n",
       "      <th>cylinders</th>\n",
       "      <th>fuel</th>\n",
       "      <th>odometer</th>\n",
       "      <th>title_status</th>\n",
       "      <th>transmission</th>\n",
       "      <th>drive</th>\n",
       "      <th>type</th>\n",
       "      <th>paint_color</th>\n",
       "      <th>state</th>\n",
       "      <th>state_trans</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>17899</td>\n",
       "      <td>2012.0</td>\n",
       "      <td>volkswagen</td>\n",
       "      <td>excellent</td>\n",
       "      <td>4</td>\n",
       "      <td>gas</td>\n",
       "      <td>63500.0</td>\n",
       "      <td>clean</td>\n",
       "      <td>manual</td>\n",
       "      <td>4wd</td>\n",
       "      <td>hatchback</td>\n",
       "      <td>black</td>\n",
       "      <td>ut</td>\n",
       "      <td>west</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>13999</td>\n",
       "      <td>2009.0</td>\n",
       "      <td>ram</td>\n",
       "      <td>good</td>\n",
       "      <td>6</td>\n",
       "      <td>gas</td>\n",
       "      <td>120057.0</td>\n",
       "      <td>clean</td>\n",
       "      <td>automatic</td>\n",
       "      <td>4wd</td>\n",
       "      <td>pickup</td>\n",
       "      <td>silver</td>\n",
       "      <td>ut</td>\n",
       "      <td>west</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>34500</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>ford</td>\n",
       "      <td>excellent</td>\n",
       "      <td>6</td>\n",
       "      <td>diesel</td>\n",
       "      <td>95484.0</td>\n",
       "      <td>clean</td>\n",
       "      <td>automatic</td>\n",
       "      <td>4wd</td>\n",
       "      <td>pickup</td>\n",
       "      <td>white</td>\n",
       "      <td>ut</td>\n",
       "      <td>west</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>14500</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>gmc</td>\n",
       "      <td>excellent</td>\n",
       "      <td>6</td>\n",
       "      <td>gas</td>\n",
       "      <td>89340.0</td>\n",
       "      <td>clean</td>\n",
       "      <td>automatic</td>\n",
       "      <td>4wd</td>\n",
       "      <td>pickup</td>\n",
       "      <td>white</td>\n",
       "      <td>ut</td>\n",
       "      <td>west</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12500</td>\n",
       "      <td>2013.0</td>\n",
       "      <td>ford</td>\n",
       "      <td>salvage</td>\n",
       "      <td>6</td>\n",
       "      <td>gas</td>\n",
       "      <td>226031.0</td>\n",
       "      <td>clean</td>\n",
       "      <td>automatic</td>\n",
       "      <td>4wd</td>\n",
       "      <td>pickup</td>\n",
       "      <td>white</td>\n",
       "      <td>ut</td>\n",
       "      <td>west</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   price    year manufacturer  condition  cylinders    fuel  odometer  \\\n",
       "0  17899  2012.0   volkswagen  excellent          4     gas   63500.0   \n",
       "1  13999  2009.0          ram       good          6     gas  120057.0   \n",
       "2  34500  2017.0         ford  excellent          6  diesel   95484.0   \n",
       "3  14500  2007.0          gmc  excellent          6     gas   89340.0   \n",
       "4  12500  2013.0         ford    salvage          6     gas  226031.0   \n",
       "\n",
       "  title_status transmission drive       type paint_color state state_trans  \n",
       "0        clean       manual   4wd  hatchback       black    ut        west  \n",
       "1        clean    automatic   4wd     pickup      silver    ut        west  \n",
       "2        clean    automatic   4wd     pickup       white    ut        west  \n",
       "3        clean    automatic   4wd     pickup       white    ut        west  \n",
       "4        clean    automatic   4wd     pickup       white    ut        west  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "modellingph2.drop([\"age\"],axis=1,inplace=True)\n",
    "modellingph2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "website_url = requests.get(\"https://en.wikipedia.org/wiki/List_of_U.S._states_by_GDP_per_capita\").text\n",
    "soup = BeautifulSoup(website_url,\"lxml\")\n",
    "\n",
    "my_table = soup.find(\"table\",{\"class\":\"wikitable sortable\"})\n",
    "my_table\n",
    "names=my_table.findAll('tr')\n",
    "soup = BeautifulSoup(website_url,\"html.parser\")\n",
    "rows = []\n",
    "for tr in soup.select('tr'):\n",
    "    rows.append([td.get_text(strip=True) for td in tr.select('th, td')])\n",
    "    \n",
    "rows_up=rows[:len(rows)-77]\n",
    "\n",
    "gdp=pd.DataFrame(rows_up,columns=[\"Rank\",\"State\",\"2018\",\"2017\",\"2016\",\"2015\",\"2014\",\"2013\",\"2012\",\"2011\"])\n",
    "\n",
    "gdp.drop([\"Rank\"],axis=1,inplace=True)\n",
    "gdp.drop(gdp.index[[0,1,23]],inplace=True)\n",
    "gdp[\"State\"]=gdp[\"State\"].str.lower()\n",
    "\n",
    "gdp.columns=[\"US States\",\"GDP2018\",\"GDP2017\",\"GDP2016\",\"GDP2015\",\"GDP2014\",\"GDP2013\",\"GDP2012\",\"GDP2011\"]\n",
    "gdp.index=range(1,len(gdp)+1)\n",
    "\n",
    "columns=[\"GDP2018\",\"GDP2017\",\"GDP2016\",\"GDP2015\",\"GDP2014\",\"GDP2013\",\"GDP2012\",\"GDP2011\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "us_state_abbrev = {\n",
    "    'alabama': 'AL',\n",
    "    'alaska': 'AK',\n",
    "    'american samoa': 'AS',\n",
    "    'arizona': 'AZ',\n",
    "    'arkansas': 'AR',\n",
    "    'california': 'CA',\n",
    "    'colorado': 'CO',\n",
    "    'connecticut': 'CT',\n",
    "    'delaware': 'DE',\n",
    "    'district of columbia': 'DC',\n",
    "    'florida': 'FL',\n",
    "    'georgia': 'GA',\n",
    "    'guam': 'GU',\n",
    "    'hawaii': 'HI',\n",
    "    'idaho': 'ID',\n",
    "    'illinois': 'IL',\n",
    "    'indiana': 'IN',\n",
    "    'iowa': 'IA',\n",
    "    'kansas': 'KS',\n",
    "    'kentucky': 'KY',\n",
    "    'louisiana': 'LA',\n",
    "    'maine': 'ME',\n",
    "    'maryland': 'MD',\n",
    "    'massachusetts': 'MA',\n",
    "    'michigan': 'MI',\n",
    "    'minnesota': 'MN',\n",
    "    'mississippi': 'MS',\n",
    "    'missouri': 'MO',\n",
    "    'montana': 'MT',\n",
    "    'nebraska': 'NE',\n",
    "    'nevada': 'NV',\n",
    "    'new hampshire': 'NH',\n",
    "    'new jersey': 'NJ',\n",
    "    'new mexico': 'NM',\n",
    "    'new york': 'NY',\n",
    "    'north carolina': 'NC',\n",
    "    'north dakota': 'ND',\n",
    "    'northern mariana islands':'MP',\n",
    "    'ohio': 'OH',\n",
    "    'oklahoma': 'OK',\n",
    "    'oregon': 'OR',\n",
    "    'pennsylvania': 'PA',\n",
    "    'puerto rico': 'PR',\n",
    "    'rhode island': 'RI',\n",
    "    'south carolina': 'SC',\n",
    "    'south dakota': 'SD',\n",
    "    'tennessee': 'TN',\n",
    "    'texas': 'TX',\n",
    "    'utah': 'UT',\n",
    "    'vermont': 'VT',\n",
    "    'virgin islands': 'VI',\n",
    "    'virginia': 'VA',\n",
    "    'washington': 'WA',\n",
    "    'west virginia': 'WV',\n",
    "    'wisconsin': 'WI',\n",
    "    'wyoming': 'WY'\n",
    "}\n",
    "\n",
    "for index,row in gdp.iterrows():\n",
    "    gdp.at[index,\"US States\"]=us_state_abbrev[row[\"US States\"]]\n",
    "\n",
    "gdp[\"US States\"]=gdp[\"US States\"].str.lower()\n",
    "gdp.columns=[\"state\",\"GDP2018\",\"GDP2017\",\"GDP2016\",\"GDP2015\",\"GDP2014\",\"GDP2013\",\"GDP2012\",\"GDP2011\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "updatedsecondary=pd.merge(modellingph2,gdp,on=\"state\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "updatedsecondary.drop(['state'],axis=1,inplace=True)\n",
    "\n",
    "updatedsecondary.rename(columns={'state_trans':'state'},inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "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>price</th>\n",
       "      <th>year</th>\n",
       "      <th>manufacturer</th>\n",
       "      <th>condition</th>\n",
       "      <th>cylinders</th>\n",
       "      <th>fuel</th>\n",
       "      <th>odometer</th>\n",
       "      <th>title_status</th>\n",
       "      <th>transmission</th>\n",
       "      <th>drive</th>\n",
       "      <th>...</th>\n",
       "      <th>paint_color</th>\n",
       "      <th>state</th>\n",
       "      <th>GDP2018</th>\n",
       "      <th>GDP2017</th>\n",
       "      <th>GDP2016</th>\n",
       "      <th>GDP2015</th>\n",
       "      <th>GDP2014</th>\n",
       "      <th>GDP2013</th>\n",
       "      <th>GDP2012</th>\n",
       "      <th>GDP2011</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>17899</td>\n",
       "      <td>2012.0</td>\n",
       "      <td>volkswagen</td>\n",
       "      <td>excellent</td>\n",
       "      <td>4</td>\n",
       "      <td>gas</td>\n",
       "      <td>63500.0</td>\n",
       "      <td>clean</td>\n",
       "      <td>manual</td>\n",
       "      <td>4wd</td>\n",
       "      <td>...</td>\n",
       "      <td>black</td>\n",
       "      <td>west</td>\n",
       "      <td>44,636</td>\n",
       "      <td>44,204</td>\n",
       "      <td>43,069</td>\n",
       "      <td>42,267</td>\n",
       "      <td>41,929</td>\n",
       "      <td>41,992</td>\n",
       "      <td>41,500</td>\n",
       "      <td>41,563</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>13999</td>\n",
       "      <td>2009.0</td>\n",
       "      <td>ram</td>\n",
       "      <td>good</td>\n",
       "      <td>6</td>\n",
       "      <td>gas</td>\n",
       "      <td>120057.0</td>\n",
       "      <td>clean</td>\n",
       "      <td>automatic</td>\n",
       "      <td>4wd</td>\n",
       "      <td>...</td>\n",
       "      <td>silver</td>\n",
       "      <td>west</td>\n",
       "      <td>44,636</td>\n",
       "      <td>44,204</td>\n",
       "      <td>43,069</td>\n",
       "      <td>42,267</td>\n",
       "      <td>41,929</td>\n",
       "      <td>41,992</td>\n",
       "      <td>41,500</td>\n",
       "      <td>41,563</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>34500</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>ford</td>\n",
       "      <td>excellent</td>\n",
       "      <td>6</td>\n",
       "      <td>diesel</td>\n",
       "      <td>95484.0</td>\n",
       "      <td>clean</td>\n",
       "      <td>automatic</td>\n",
       "      <td>4wd</td>\n",
       "      <td>...</td>\n",
       "      <td>white</td>\n",
       "      <td>west</td>\n",
       "      <td>44,636</td>\n",
       "      <td>44,204</td>\n",
       "      <td>43,069</td>\n",
       "      <td>42,267</td>\n",
       "      <td>41,929</td>\n",
       "      <td>41,992</td>\n",
       "      <td>41,500</td>\n",
       "      <td>41,563</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>14500</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>gmc</td>\n",
       "      <td>excellent</td>\n",
       "      <td>6</td>\n",
       "      <td>gas</td>\n",
       "      <td>89340.0</td>\n",
       "      <td>clean</td>\n",
       "      <td>automatic</td>\n",
       "      <td>4wd</td>\n",
       "      <td>...</td>\n",
       "      <td>white</td>\n",
       "      <td>west</td>\n",
       "      <td>44,636</td>\n",
       "      <td>44,204</td>\n",
       "      <td>43,069</td>\n",
       "      <td>42,267</td>\n",
       "      <td>41,929</td>\n",
       "      <td>41,992</td>\n",
       "      <td>41,500</td>\n",
       "      <td>41,563</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12500</td>\n",
       "      <td>2013.0</td>\n",
       "      <td>ford</td>\n",
       "      <td>salvage</td>\n",
       "      <td>6</td>\n",
       "      <td>gas</td>\n",
       "      <td>226031.0</td>\n",
       "      <td>clean</td>\n",
       "      <td>automatic</td>\n",
       "      <td>4wd</td>\n",
       "      <td>...</td>\n",
       "      <td>white</td>\n",
       "      <td>west</td>\n",
       "      <td>44,636</td>\n",
       "      <td>44,204</td>\n",
       "      <td>43,069</td>\n",
       "      <td>42,267</td>\n",
       "      <td>41,929</td>\n",
       "      <td>41,992</td>\n",
       "      <td>41,500</td>\n",
       "      <td>41,563</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   price    year manufacturer  condition  cylinders    fuel  odometer  \\\n",
       "0  17899  2012.0   volkswagen  excellent          4     gas   63500.0   \n",
       "1  13999  2009.0          ram       good          6     gas  120057.0   \n",
       "2  34500  2017.0         ford  excellent          6  diesel   95484.0   \n",
       "3  14500  2007.0          gmc  excellent          6     gas   89340.0   \n",
       "4  12500  2013.0         ford    salvage          6     gas  226031.0   \n",
       "\n",
       "  title_status transmission drive  ... paint_color state GDP2018 GDP2017  \\\n",
       "0        clean       manual   4wd  ...       black  west  44,636  44,204   \n",
       "1        clean    automatic   4wd  ...      silver  west  44,636  44,204   \n",
       "2        clean    automatic   4wd  ...       white  west  44,636  44,204   \n",
       "3        clean    automatic   4wd  ...       white  west  44,636  44,204   \n",
       "4        clean    automatic   4wd  ...       white  west  44,636  44,204   \n",
       "\n",
       "  GDP2016 GDP2015 GDP2014 GDP2013 GDP2012 GDP2011  \n",
       "0  43,069  42,267  41,929  41,992  41,500  41,563  \n",
       "1  43,069  42,267  41,929  41,992  41,500  41,563  \n",
       "2  43,069  42,267  41,929  41,992  41,500  41,563  \n",
       "3  43,069  42,267  41,929  41,992  41,500  41,563  \n",
       "4  43,069  42,267  41,929  41,992  41,500  41,563  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "updatedsecondary.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "updatedsecondary.to_csv(\"vehiclesplusGDP.csv\",index=False)"
   ]
  },
  {
   "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}