Automobile Dataset Analysis.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>company</th>\n",
       "      <th>body-style</th>\n",
       "      <th>wheel-base</th>\n",
       "      <th>length</th>\n",
       "      <th>engine-type</th>\n",
       "      <th>num-of-cylinders</th>\n",
       "      <th>horsepower</th>\n",
       "      <th>average-mileage</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>alfa-romero</td>\n",
       "      <td>convertible</td>\n",
       "      <td>88.6</td>\n",
       "      <td>168.8</td>\n",
       "      <td>dohc</td>\n",
       "      <td>four</td>\n",
       "      <td>111</td>\n",
       "      <td>21</td>\n",
       "      <td>13495.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>alfa-romero</td>\n",
       "      <td>convertible</td>\n",
       "      <td>88.6</td>\n",
       "      <td>168.8</td>\n",
       "      <td>dohc</td>\n",
       "      <td>four</td>\n",
       "      <td>111</td>\n",
       "      <td>21</td>\n",
       "      <td>16500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>alfa-romero</td>\n",
       "      <td>hatchback</td>\n",
       "      <td>94.5</td>\n",
       "      <td>171.2</td>\n",
       "      <td>ohcv</td>\n",
       "      <td>six</td>\n",
       "      <td>154</td>\n",
       "      <td>19</td>\n",
       "      <td>16500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>audi</td>\n",
       "      <td>sedan</td>\n",
       "      <td>99.8</td>\n",
       "      <td>176.6</td>\n",
       "      <td>ohc</td>\n",
       "      <td>four</td>\n",
       "      <td>102</td>\n",
       "      <td>24</td>\n",
       "      <td>13950.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>audi</td>\n",
       "      <td>sedan</td>\n",
       "      <td>99.4</td>\n",
       "      <td>176.6</td>\n",
       "      <td>ohc</td>\n",
       "      <td>five</td>\n",
       "      <td>115</td>\n",
       "      <td>18</td>\n",
       "      <td>17450.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index      company   body-style  wheel-base  length engine-type  \\\n",
       "0      0  alfa-romero  convertible        88.6   168.8        dohc   \n",
       "1      1  alfa-romero  convertible        88.6   168.8        dohc   \n",
       "2      2  alfa-romero    hatchback        94.5   171.2        ohcv   \n",
       "3      3         audi        sedan        99.8   176.6         ohc   \n",
       "4      4         audi        sedan        99.4   176.6         ohc   \n",
       "\n",
       "  num-of-cylinders  horsepower  average-mileage    price  \n",
       "0             four         111               21  13495.0  \n",
       "1             four         111               21  16500.0  \n",
       "2              six         154               19  16500.0  \n",
       "3             four         102               24  13950.0  \n",
       "4             five         115               18  17450.0  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Question 1: From given data set print first and last five rows\n",
    "import pandas as pd\n",
    "url = 'datasets/Automobile_data.csv'\n",
    "dataset = pd.read_csv(url)\n",
    "dataset.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>company</th>\n",
       "      <th>body-style</th>\n",
       "      <th>wheel-base</th>\n",
       "      <th>length</th>\n",
       "      <th>engine-type</th>\n",
       "      <th>num-of-cylinders</th>\n",
       "      <th>horsepower</th>\n",
       "      <th>average-mileage</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>56</td>\n",
       "      <td>81</td>\n",
       "      <td>volkswagen</td>\n",
       "      <td>sedan</td>\n",
       "      <td>97.3</td>\n",
       "      <td>171.7</td>\n",
       "      <td>ohc</td>\n",
       "      <td>four</td>\n",
       "      <td>85</td>\n",
       "      <td>27</td>\n",
       "      <td>7975.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>57</td>\n",
       "      <td>82</td>\n",
       "      <td>volkswagen</td>\n",
       "      <td>sedan</td>\n",
       "      <td>97.3</td>\n",
       "      <td>171.7</td>\n",
       "      <td>ohc</td>\n",
       "      <td>four</td>\n",
       "      <td>52</td>\n",
       "      <td>37</td>\n",
       "      <td>7995.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>58</td>\n",
       "      <td>86</td>\n",
       "      <td>volkswagen</td>\n",
       "      <td>sedan</td>\n",
       "      <td>97.3</td>\n",
       "      <td>171.7</td>\n",
       "      <td>ohc</td>\n",
       "      <td>four</td>\n",
       "      <td>100</td>\n",
       "      <td>26</td>\n",
       "      <td>9995.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>59</td>\n",
       "      <td>87</td>\n",
       "      <td>volvo</td>\n",
       "      <td>sedan</td>\n",
       "      <td>104.3</td>\n",
       "      <td>188.8</td>\n",
       "      <td>ohc</td>\n",
       "      <td>four</td>\n",
       "      <td>114</td>\n",
       "      <td>23</td>\n",
       "      <td>12940.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>60</td>\n",
       "      <td>88</td>\n",
       "      <td>volvo</td>\n",
       "      <td>wagon</td>\n",
       "      <td>104.3</td>\n",
       "      <td>188.8</td>\n",
       "      <td>ohc</td>\n",
       "      <td>four</td>\n",
       "      <td>114</td>\n",
       "      <td>23</td>\n",
       "      <td>13415.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    index     company body-style  wheel-base  length engine-type  \\\n",
       "56     81  volkswagen      sedan        97.3   171.7         ohc   \n",
       "57     82  volkswagen      sedan        97.3   171.7         ohc   \n",
       "58     86  volkswagen      sedan        97.3   171.7         ohc   \n",
       "59     87       volvo      sedan       104.3   188.8         ohc   \n",
       "60     88       volvo      wagon       104.3   188.8         ohc   \n",
       "\n",
       "   num-of-cylinders  horsepower  average-mileage    price  \n",
       "56             four          85               27   7975.0  \n",
       "57             four          52               37   7995.0  \n",
       "58             four         100               26   9995.0  \n",
       "59             four         114               23  12940.0  \n",
       "60             four         114               23  13415.0  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataset.tail(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>company</th>\n",
       "      <th>body-style</th>\n",
       "      <th>wheel-base</th>\n",
       "      <th>length</th>\n",
       "      <th>engine-type</th>\n",
       "      <th>num-of-cylinders</th>\n",
       "      <th>horsepower</th>\n",
       "      <th>average-mileage</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>alfa-romero</td>\n",
       "      <td>convertible</td>\n",
       "      <td>88.6</td>\n",
       "      <td>168.8</td>\n",
       "      <td>dohc</td>\n",
       "      <td>four</td>\n",
       "      <td>111</td>\n",
       "      <td>21</td>\n",
       "      <td>13495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>alfa-romero</td>\n",
       "      <td>convertible</td>\n",
       "      <td>88.6</td>\n",
       "      <td>168.8</td>\n",
       "      <td>dohc</td>\n",
       "      <td>four</td>\n",
       "      <td>111</td>\n",
       "      <td>21</td>\n",
       "      <td>16500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>alfa-romero</td>\n",
       "      <td>hatchback</td>\n",
       "      <td>94.5</td>\n",
       "      <td>171.2</td>\n",
       "      <td>ohcv</td>\n",
       "      <td>six</td>\n",
       "      <td>154</td>\n",
       "      <td>19</td>\n",
       "      <td>16500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>audi</td>\n",
       "      <td>sedan</td>\n",
       "      <td>99.8</td>\n",
       "      <td>176.6</td>\n",
       "      <td>ohc</td>\n",
       "      <td>four</td>\n",
       "      <td>102</td>\n",
       "      <td>24</td>\n",
       "      <td>13950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>audi</td>\n",
       "      <td>sedan</td>\n",
       "      <td>99.4</td>\n",
       "      <td>176.6</td>\n",
       "      <td>ohc</td>\n",
       "      <td>five</td>\n",
       "      <td>115</td>\n",
       "      <td>18</td>\n",
       "      <td>17450</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>56</td>\n",
       "      <td>81</td>\n",
       "      <td>volkswagen</td>\n",
       "      <td>sedan</td>\n",
       "      <td>97.3</td>\n",
       "      <td>171.7</td>\n",
       "      <td>ohc</td>\n",
       "      <td>four</td>\n",
       "      <td>85</td>\n",
       "      <td>27</td>\n",
       "      <td>7975</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>57</td>\n",
       "      <td>82</td>\n",
       "      <td>volkswagen</td>\n",
       "      <td>sedan</td>\n",
       "      <td>97.3</td>\n",
       "      <td>171.7</td>\n",
       "      <td>ohc</td>\n",
       "      <td>four</td>\n",
       "      <td>52</td>\n",
       "      <td>37</td>\n",
       "      <td>7995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>58</td>\n",
       "      <td>86</td>\n",
       "      <td>volkswagen</td>\n",
       "      <td>sedan</td>\n",
       "      <td>97.3</td>\n",
       "      <td>171.7</td>\n",
       "      <td>ohc</td>\n",
       "      <td>four</td>\n",
       "      <td>100</td>\n",
       "      <td>26</td>\n",
       "      <td>9995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>59</td>\n",
       "      <td>87</td>\n",
       "      <td>volvo</td>\n",
       "      <td>sedan</td>\n",
       "      <td>104.3</td>\n",
       "      <td>188.8</td>\n",
       "      <td>ohc</td>\n",
       "      <td>four</td>\n",
       "      <td>114</td>\n",
       "      <td>23</td>\n",
       "      <td>12940</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>60</td>\n",
       "      <td>88</td>\n",
       "      <td>volvo</td>\n",
       "      <td>wagon</td>\n",
       "      <td>104.3</td>\n",
       "      <td>188.8</td>\n",
       "      <td>ohc</td>\n",
       "      <td>four</td>\n",
       "      <td>114</td>\n",
       "      <td>23</td>\n",
       "      <td>13415</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>61 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    index      company   body-style  wheel-base  length engine-type  \\\n",
       "0       0  alfa-romero  convertible        88.6   168.8        dohc   \n",
       "1       1  alfa-romero  convertible        88.6   168.8        dohc   \n",
       "2       2  alfa-romero    hatchback        94.5   171.2        ohcv   \n",
       "3       3         audi        sedan        99.8   176.6         ohc   \n",
       "4       4         audi        sedan        99.4   176.6         ohc   \n",
       "..    ...          ...          ...         ...     ...         ...   \n",
       "56     81   volkswagen        sedan        97.3   171.7         ohc   \n",
       "57     82   volkswagen        sedan        97.3   171.7         ohc   \n",
       "58     86   volkswagen        sedan        97.3   171.7         ohc   \n",
       "59     87        volvo        sedan       104.3   188.8         ohc   \n",
       "60     88        volvo        wagon       104.3   188.8         ohc   \n",
       "\n",
       "   num-of-cylinders  horsepower  average-mileage  price  \n",
       "0              four         111               21  13495  \n",
       "1              four         111               21  16500  \n",
       "2               six         154               19  16500  \n",
       "3              four         102               24  13950  \n",
       "4              five         115               18  17450  \n",
       "..              ...         ...              ...    ...  \n",
       "56             four          85               27   7975  \n",
       "57             four          52               37   7995  \n",
       "58             four         100               26   9995  \n",
       "59             four         114               23  12940  \n",
       "60             four         114               23  13415  \n",
       "\n",
       "[61 rows x 10 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Question 2: Clean data and update the CSV file\n",
    "dataset.isnull().values.any()\n",
    "dataset.fillna('NaN')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>company</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>35</td>\n",
       "      <td>mercedes-benz</td>\n",
       "      <td>45400.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          company    price\n",
       "35  mercedes-benz  45400.0"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Question 3: Find the most expensive car company name\n",
    "dataset[['company', 'price']][dataset.price == dataset['price'].max()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>company</th>\n",
       "      <th>body-style</th>\n",
       "      <th>wheel-base</th>\n",
       "      <th>length</th>\n",
       "      <th>engine-type</th>\n",
       "      <th>num-of-cylinders</th>\n",
       "      <th>horsepower</th>\n",
       "      <th>average-mileage</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>48</td>\n",
       "      <td>66</td>\n",
       "      <td>toyota</td>\n",
       "      <td>hatchback</td>\n",
       "      <td>95.7</td>\n",
       "      <td>158.7</td>\n",
       "      <td>ohc</td>\n",
       "      <td>four</td>\n",
       "      <td>62</td>\n",
       "      <td>35</td>\n",
       "      <td>5348.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>49</td>\n",
       "      <td>67</td>\n",
       "      <td>toyota</td>\n",
       "      <td>hatchback</td>\n",
       "      <td>95.7</td>\n",
       "      <td>158.7</td>\n",
       "      <td>ohc</td>\n",
       "      <td>four</td>\n",
       "      <td>62</td>\n",
       "      <td>31</td>\n",
       "      <td>6338.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>50</td>\n",
       "      <td>68</td>\n",
       "      <td>toyota</td>\n",
       "      <td>hatchback</td>\n",
       "      <td>95.7</td>\n",
       "      <td>158.7</td>\n",
       "      <td>ohc</td>\n",
       "      <td>four</td>\n",
       "      <td>62</td>\n",
       "      <td>31</td>\n",
       "      <td>6488.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>51</td>\n",
       "      <td>69</td>\n",
       "      <td>toyota</td>\n",
       "      <td>wagon</td>\n",
       "      <td>95.7</td>\n",
       "      <td>169.7</td>\n",
       "      <td>ohc</td>\n",
       "      <td>four</td>\n",
       "      <td>62</td>\n",
       "      <td>31</td>\n",
       "      <td>6918.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>52</td>\n",
       "      <td>70</td>\n",
       "      <td>toyota</td>\n",
       "      <td>wagon</td>\n",
       "      <td>95.7</td>\n",
       "      <td>169.7</td>\n",
       "      <td>ohc</td>\n",
       "      <td>four</td>\n",
       "      <td>62</td>\n",
       "      <td>27</td>\n",
       "      <td>7898.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>53</td>\n",
       "      <td>71</td>\n",
       "      <td>toyota</td>\n",
       "      <td>wagon</td>\n",
       "      <td>95.7</td>\n",
       "      <td>169.7</td>\n",
       "      <td>ohc</td>\n",
       "      <td>four</td>\n",
       "      <td>62</td>\n",
       "      <td>27</td>\n",
       "      <td>8778.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>54</td>\n",
       "      <td>79</td>\n",
       "      <td>toyota</td>\n",
       "      <td>wagon</td>\n",
       "      <td>104.5</td>\n",
       "      <td>187.8</td>\n",
       "      <td>dohc</td>\n",
       "      <td>six</td>\n",
       "      <td>156</td>\n",
       "      <td>19</td>\n",
       "      <td>15750.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    index company body-style  wheel-base  length engine-type num-of-cylinders  \\\n",
       "48     66  toyota  hatchback        95.7   158.7         ohc             four   \n",
       "49     67  toyota  hatchback        95.7   158.7         ohc             four   \n",
       "50     68  toyota  hatchback        95.7   158.7         ohc             four   \n",
       "51     69  toyota      wagon        95.7   169.7         ohc             four   \n",
       "52     70  toyota      wagon        95.7   169.7         ohc             four   \n",
       "53     71  toyota      wagon        95.7   169.7         ohc             four   \n",
       "54     79  toyota      wagon       104.5   187.8        dohc              six   \n",
       "\n",
       "    horsepower  average-mileage    price  \n",
       "48          62               35   5348.0  \n",
       "49          62               31   6338.0  \n",
       "50          62               31   6488.0  \n",
       "51          62               31   6918.0  \n",
       "52          62               27   7898.0  \n",
       "53          62               27   8778.0  \n",
       "54         156               19  15750.0  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Question 4: Print All Toyota Cars details\n",
    "dataset[dataset['company'].str.contains('toyota')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "toyota           7\n",
       "bmw              6\n",
       "nissan           5\n",
       "mazda            5\n",
       "mitsubishi       4\n",
       "mercedes-benz    4\n",
       "volkswagen       4\n",
       "audi             4\n",
       "alfa-romero      3\n",
       "porsche          3\n",
       "jaguar           3\n",
       "isuzu            3\n",
       "chevrolet        3\n",
       "honda            3\n",
       "dodge            2\n",
       "volvo            2\n",
       "Name: company, dtype: int64"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Question 5: Count total cars per company\n",
    "dataset['company'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "company\n",
       "alfa-romero      16500.0\n",
       "audi             18920.0\n",
       "bmw              41315.0\n",
       "chevrolet         6575.0\n",
       "dodge             6377.0\n",
       "honda            12945.0\n",
       "isuzu             6785.0\n",
       "jaguar           36000.0\n",
       "mazda            18344.0\n",
       "mercedes-benz    45400.0\n",
       "mitsubishi        8189.0\n",
       "nissan           13499.0\n",
       "porsche          37028.0\n",
       "toyota           15750.0\n",
       "volkswagen        9995.0\n",
       "volvo            13415.0\n",
       "Name: price, dtype: float64"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Question 6: Find each company’s Higesht price car\n",
    "dataset.groupby(['company'])['price'].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "company\n",
       "alfa-romero      20.333333\n",
       "audi             20.000000\n",
       "bmw              19.000000\n",
       "chevrolet        41.000000\n",
       "dodge            31.000000\n",
       "honda            26.333333\n",
       "isuzu            33.333333\n",
       "jaguar           14.333333\n",
       "mazda            28.000000\n",
       "mercedes-benz    18.000000\n",
       "mitsubishi       29.500000\n",
       "nissan           31.400000\n",
       "porsche          17.000000\n",
       "toyota           28.714286\n",
       "volkswagen       31.750000\n",
       "volvo            23.000000\n",
       "Name: average-mileage, dtype: float64"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Question 7: Find the average mileage of each car making company\n",
    "dataset.groupby(['company'])['average-mileage'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>company</th>\n",
       "      <th>body-style</th>\n",
       "      <th>wheel-base</th>\n",
       "      <th>length</th>\n",
       "      <th>engine-type</th>\n",
       "      <th>num-of-cylinders</th>\n",
       "      <th>horsepower</th>\n",
       "      <th>average-mileage</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>35</td>\n",
       "      <td>47</td>\n",
       "      <td>mercedes-benz</td>\n",
       "      <td>hardtop</td>\n",
       "      <td>112.0</td>\n",
       "      <td>199.2</td>\n",
       "      <td>ohcv</td>\n",
       "      <td>eight</td>\n",
       "      <td>184</td>\n",
       "      <td>14</td>\n",
       "      <td>45400.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>14</td>\n",
       "      <td>bmw</td>\n",
       "      <td>sedan</td>\n",
       "      <td>103.5</td>\n",
       "      <td>193.8</td>\n",
       "      <td>ohc</td>\n",
       "      <td>six</td>\n",
       "      <td>182</td>\n",
       "      <td>16</td>\n",
       "      <td>41315.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>34</td>\n",
       "      <td>46</td>\n",
       "      <td>mercedes-benz</td>\n",
       "      <td>sedan</td>\n",
       "      <td>120.9</td>\n",
       "      <td>208.1</td>\n",
       "      <td>ohcv</td>\n",
       "      <td>eight</td>\n",
       "      <td>184</td>\n",
       "      <td>14</td>\n",
       "      <td>40960.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>46</td>\n",
       "      <td>62</td>\n",
       "      <td>porsche</td>\n",
       "      <td>convertible</td>\n",
       "      <td>89.5</td>\n",
       "      <td>168.9</td>\n",
       "      <td>ohcf</td>\n",
       "      <td>six</td>\n",
       "      <td>207</td>\n",
       "      <td>17</td>\n",
       "      <td>37028.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>12</td>\n",
       "      <td>15</td>\n",
       "      <td>bmw</td>\n",
       "      <td>sedan</td>\n",
       "      <td>110.0</td>\n",
       "      <td>197.0</td>\n",
       "      <td>ohc</td>\n",
       "      <td>six</td>\n",
       "      <td>182</td>\n",
       "      <td>15</td>\n",
       "      <td>36880.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>27</td>\n",
       "      <td>36</td>\n",
       "      <td>mazda</td>\n",
       "      <td>hatchback</td>\n",
       "      <td>93.1</td>\n",
       "      <td>159.1</td>\n",
       "      <td>ohc</td>\n",
       "      <td>four</td>\n",
       "      <td>68</td>\n",
       "      <td>30</td>\n",
       "      <td>5195.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13</td>\n",
       "      <td>16</td>\n",
       "      <td>chevrolet</td>\n",
       "      <td>hatchback</td>\n",
       "      <td>88.4</td>\n",
       "      <td>141.1</td>\n",
       "      <td>l</td>\n",
       "      <td>three</td>\n",
       "      <td>48</td>\n",
       "      <td>47</td>\n",
       "      <td>5151.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>22</td>\n",
       "      <td>31</td>\n",
       "      <td>isuzu</td>\n",
       "      <td>sedan</td>\n",
       "      <td>94.5</td>\n",
       "      <td>155.9</td>\n",
       "      <td>ohc</td>\n",
       "      <td>four</td>\n",
       "      <td>70</td>\n",
       "      <td>38</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>23</td>\n",
       "      <td>32</td>\n",
       "      <td>isuzu</td>\n",
       "      <td>sedan</td>\n",
       "      <td>94.5</td>\n",
       "      <td>155.9</td>\n",
       "      <td>ohc</td>\n",
       "      <td>four</td>\n",
       "      <td>70</td>\n",
       "      <td>38</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>47</td>\n",
       "      <td>63</td>\n",
       "      <td>porsche</td>\n",
       "      <td>hatchback</td>\n",
       "      <td>98.4</td>\n",
       "      <td>175.7</td>\n",
       "      <td>dohcv</td>\n",
       "      <td>eight</td>\n",
       "      <td>288</td>\n",
       "      <td>17</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>61 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    index        company   body-style  wheel-base  length engine-type  \\\n",
       "35     47  mercedes-benz      hardtop       112.0   199.2        ohcv   \n",
       "11     14            bmw        sedan       103.5   193.8         ohc   \n",
       "34     46  mercedes-benz        sedan       120.9   208.1        ohcv   \n",
       "46     62        porsche  convertible        89.5   168.9        ohcf   \n",
       "12     15            bmw        sedan       110.0   197.0         ohc   \n",
       "..    ...            ...          ...         ...     ...         ...   \n",
       "27     36          mazda    hatchback        93.1   159.1         ohc   \n",
       "13     16      chevrolet    hatchback        88.4   141.1           l   \n",
       "22     31          isuzu        sedan        94.5   155.9         ohc   \n",
       "23     32          isuzu        sedan        94.5   155.9         ohc   \n",
       "47     63        porsche    hatchback        98.4   175.7       dohcv   \n",
       "\n",
       "   num-of-cylinders  horsepower  average-mileage    price  \n",
       "35            eight         184               14  45400.0  \n",
       "11              six         182               16  41315.0  \n",
       "34            eight         184               14  40960.0  \n",
       "46              six         207               17  37028.0  \n",
       "12              six         182               15  36880.0  \n",
       "..              ...         ...              ...      ...  \n",
       "27             four          68               30   5195.0  \n",
       "13            three          48               47   5151.0  \n",
       "22             four          70               38      NaN  \n",
       "23             four          70               38      NaN  \n",
       "47            eight         288               17      NaN  \n",
       "\n",
       "[61 rows x 10 columns]"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Question 8: Sort all cars by Price column\n",
    "dataset.sort_values(by=['price'], ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Question 9: Concatenate two data frames using the following conditions\n",
    "GermanCars = {'Company': ['Ford', 'Mercedes', 'BMV', 'Audi'], 'Price': [23845, 171995, 135925 , 71400]}\n",
    "japaneseCars = {'Company': ['Toyota', 'Honda', 'Nissan', 'Mitsubishi '], 'Price': [29995, 23600, 61500 , 58900]}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "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></th>\n",
       "      <th>Company</th>\n",
       "      <th>Price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td rowspan=\"4\" valign=\"top\">Germeny</td>\n",
       "      <td>0</td>\n",
       "      <td>Ford</td>\n",
       "      <td>23845</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Mercedes</td>\n",
       "      <td>171995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>BMV</td>\n",
       "      <td>135925</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Audi</td>\n",
       "      <td>71400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td rowspan=\"4\" valign=\"top\">Japan</td>\n",
       "      <td>0</td>\n",
       "      <td>Toyota</td>\n",
       "      <td>29995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Honda</td>\n",
       "      <td>23600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Nissan</td>\n",
       "      <td>61500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Mitsubishi</td>\n",
       "      <td>58900</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               Company   Price\n",
       "Germeny 0         Ford   23845\n",
       "        1     Mercedes  171995\n",
       "        2          BMV  135925\n",
       "        3         Audi   71400\n",
       "Japan   0       Toyota   29995\n",
       "        1        Honda   23600\n",
       "        2       Nissan   61500\n",
       "        3  Mitsubishi    58900"
      ]
     },
     "execution_count": 104,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dictF1 = pd.DataFrame.from_dict(GermanCars)\n",
    "dictF2 = pd.DataFrame.from_dict(japaneseCars)\n",
    "pd.concat([dictF1, dictF2], keys=[\"Germeny\", \"Japan\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "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>Company</th>\n",
       "      <th>Price</th>\n",
       "      <th>horsepower</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Toyota</td>\n",
       "      <td>23845</td>\n",
       "      <td>141</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Honda</td>\n",
       "      <td>17995</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>BMV</td>\n",
       "      <td>135925</td>\n",
       "      <td>182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Audi</td>\n",
       "      <td>71400</td>\n",
       "      <td>160</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Company   Price  horsepower\n",
       "0  Toyota   23845         141\n",
       "1   Honda   17995          80\n",
       "2     BMV  135925         182\n",
       "3    Audi   71400         160"
      ]
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Question 10: Merge two data frames using the following condition\n",
    "Car_Price = {'Company': ['Toyota', 'Honda', 'BMV', 'Audi'], 'Price': [23845, 17995, 135925 , 71400]}\n",
    "car_Horsepower = {'Company': ['Toyota', 'Honda', 'BMV', 'Audi'], 'horsepower': [141, 80, 182 , 160]}\n",
    "dict1 = pd.DataFrame.from_dict(Car_Price)\n",
    "dict2 = pd.DataFrame.from_dict(car_Horsepower)\n",
    "\n",
    "pd.merge(dict1, dict2, on=\"Company\")"
   ]
  },
  {
   "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}