ETL_1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ETL"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Cargo el dataset correspondiente desde la carpeta /sources para posteriormente analizarlo, limpiarlo y guardarlo en la carpeta /Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Importamos las librerias que vamos a utilizar\n",
    "import pandas as pd\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Acceso a el cvs y se pasa a un dataframe \n",
    "df_vfed = pd.read_csv('sources/Vehicle Fuel Economy Data.csv')"
   ]
  },
  {
   "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>Year</th>\n",
       "      <th>Manufacturer</th>\n",
       "      <th>Model</th>\n",
       "      <th>barrels08</th>\n",
       "      <th>barrelsA08</th>\n",
       "      <th>charge240</th>\n",
       "      <th>city08</th>\n",
       "      <th>city08U</th>\n",
       "      <th>cityA08</th>\n",
       "      <th>cityA08U</th>\n",
       "      <th>...</th>\n",
       "      <th>mfrCode</th>\n",
       "      <th>c240Dscr</th>\n",
       "      <th>charge240b</th>\n",
       "      <th>c240bDscr</th>\n",
       "      <th>createdOn</th>\n",
       "      <th>modifiedOn</th>\n",
       "      <th>startStop</th>\n",
       "      <th>phevCity</th>\n",
       "      <th>phevHwy</th>\n",
       "      <th>phevComb</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017</td>\n",
       "      <td>BMW</td>\n",
       "      <td>M6 Coupe</td>\n",
       "      <td>17.500588</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>BMX</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>04-04-2016</td>\n",
       "      <td>09-09-2016</td>\n",
       "      <td>Y</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017</td>\n",
       "      <td>BMW</td>\n",
       "      <td>X3 sDrive28i</td>\n",
       "      <td>12.396250</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>BMX</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>04-04-2016</td>\n",
       "      <td>22-11-2016</td>\n",
       "      <td>Y</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016</td>\n",
       "      <td>Infiniti</td>\n",
       "      <td>Q50S Red Sport</td>\n",
       "      <td>13.523182</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NSX</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>04-04-2016</td>\n",
       "      <td>26-09-2016</td>\n",
       "      <td>N</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017</td>\n",
       "      <td>MINI</td>\n",
       "      <td>John Cooper Works Convertible</td>\n",
       "      <td>11.018889</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>BMX</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12-04-2016</td>\n",
       "      <td>16-09-2016</td>\n",
       "      <td>Y</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017</td>\n",
       "      <td>Kia</td>\n",
       "      <td>Sedona SX</td>\n",
       "      <td>14.167143</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>KMX</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12-04-2016</td>\n",
       "      <td>02-12-2016</td>\n",
       "      <td>N</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 82 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year Manufacturer                          Model  barrels08  barrelsA08  \\\n",
       "0  2017          BMW                       M6 Coupe  17.500588         0.0   \n",
       "1  2017          BMW                   X3 sDrive28i  12.396250         0.0   \n",
       "2  2016     Infiniti                 Q50S Red Sport  13.523182         0.0   \n",
       "3  2017         MINI  John Cooper Works Convertible  11.018889         0.0   \n",
       "4  2017          Kia                      Sedona SX  14.167143         0.0   \n",
       "\n",
       "   charge240  city08  city08U  cityA08  cityA08U  ...  mfrCode  c240Dscr  \\\n",
       "0        0.0    15.0     14.0      0.0       0.0  ...      BMX       NaN   \n",
       "1        0.0    21.0     21.0      0.0       0.0  ...      BMX       NaN   \n",
       "2        0.0    20.0     19.0      0.0       0.0  ...      NSX       NaN   \n",
       "3        0.0    24.0     23.0      0.0       0.0  ...      BMX       NaN   \n",
       "4        0.0    18.0     18.0      0.0       0.0  ...      KMX       NaN   \n",
       "\n",
       "   charge240b  c240bDscr   createdOn  modifiedOn  startStop  phevCity  \\\n",
       "0         0.0        NaN  04-04-2016  09-09-2016          Y       0.0   \n",
       "1         0.0        NaN  04-04-2016  22-11-2016          Y       0.0   \n",
       "2         0.0        NaN  04-04-2016  26-09-2016          N       0.0   \n",
       "3         0.0        NaN  12-04-2016  16-09-2016          Y       0.0   \n",
       "4         0.0        NaN  12-04-2016  02-12-2016          N       0.0   \n",
       "\n",
       "   phevHwy  phevComb  \n",
       "0      0.0       0.0  \n",
       "1      0.0       0.0  \n",
       "2      0.0       0.0  \n",
       "3      0.0       0.0  \n",
       "4      0.0       0.0  \n",
       "\n",
       "[5 rows x 82 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Visualización de manera general\n",
    "df_vfed.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "#  Eliminación de columnas no necesarias\n",
    "df_vfed_col_eli = ['barrelsA08','charge240','fuelType','barrels08','range',\n",
    "       'city08U','cityA08','cityA08U','cityCD','cityE','cityUF',\n",
    "       'comb08', 'comb08U','combA08', 'combA08U', 'combE', 'combinedCD', 'combinedUF',\n",
    "       'cylinders','displ', 'drive', 'engId', 'eng_dscr', 'feScore',\n",
    "       'ghgScore', 'ghgScoreA','co2A', 'co2TailpipeAGpm', 'co2TailpipeGpm',\n",
    "       'highway08', 'highway08U', 'highwayA08', 'highwayA08U',\n",
    "       'highwayCD', 'highwayE', 'highwayUF', 'hlv', 'hpv', 'id', 'lv2', 'lv4',\n",
    "       'mpgData', 'phevBlended', 'pv2', 'pv4','rangeCity',\n",
    "       'rangeCityA', 'rangeHwy', 'rangeHwyA', 'trany', 'UCity', 'UCityA',\n",
    "       'UHighway', 'UHighwayA', 'youSaveSpend', 'guzzler', 'trans_dscr',\n",
    "       'tCharger', 'sCharger', 'evMotor','rangeA',\n",
    "       'mfrCode', 'c240Dscr', 'charge240b', 'c240bDscr', 'createdOn',\n",
    "       'modifiedOn', 'startStop', 'phevCity', 'phevHwy', 'phevComb']\n",
    "df_vfed.drop(df_vfed_col_eli, axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_vfed = df_vfed.reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>Year</th>\n",
       "      <th>Manufacturer</th>\n",
       "      <th>Model</th>\n",
       "      <th>city08</th>\n",
       "      <th>co2</th>\n",
       "      <th>fuelCost08</th>\n",
       "      <th>fuelCostA08</th>\n",
       "      <th>fuelType1</th>\n",
       "      <th>VClass</th>\n",
       "      <th>atvType</th>\n",
       "      <th>fuelType2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017</td>\n",
       "      <td>BMW</td>\n",
       "      <td>M6 Coupe</td>\n",
       "      <td>15.0</td>\n",
       "      <td>519.0</td>\n",
       "      <td>3850.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Premium Gasoline</td>\n",
       "      <td>Subcompact Cars</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017</td>\n",
       "      <td>BMW</td>\n",
       "      <td>X3 sDrive28i</td>\n",
       "      <td>21.0</td>\n",
       "      <td>374.0</td>\n",
       "      <td>2700.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Premium Gasoline</td>\n",
       "      <td>Small Sport Utility Vehicle 2WD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016</td>\n",
       "      <td>Infiniti</td>\n",
       "      <td>Q50S Red Sport</td>\n",
       "      <td>20.0</td>\n",
       "      <td>402.0</td>\n",
       "      <td>2950.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Premium Gasoline</td>\n",
       "      <td>Midsize Cars</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017</td>\n",
       "      <td>MINI</td>\n",
       "      <td>John Cooper Works Convertible</td>\n",
       "      <td>24.0</td>\n",
       "      <td>327.0</td>\n",
       "      <td>2400.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Premium Gasoline</td>\n",
       "      <td>Minicompact Cars</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017</td>\n",
       "      <td>Kia</td>\n",
       "      <td>Sedona SX</td>\n",
       "      <td>18.0</td>\n",
       "      <td>433.0</td>\n",
       "      <td>2550.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Regular Gasoline</td>\n",
       "      <td>Minivan - 2WD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year Manufacturer                          Model  city08    co2  \\\n",
       "0  2017          BMW                       M6 Coupe    15.0  519.0   \n",
       "1  2017          BMW                   X3 sDrive28i    21.0  374.0   \n",
       "2  2016     Infiniti                 Q50S Red Sport    20.0  402.0   \n",
       "3  2017         MINI  John Cooper Works Convertible    24.0  327.0   \n",
       "4  2017          Kia                      Sedona SX    18.0  433.0   \n",
       "\n",
       "   fuelCost08  fuelCostA08         fuelType1                           VClass  \\\n",
       "0      3850.0          0.0  Premium Gasoline                  Subcompact Cars   \n",
       "1      2700.0          0.0  Premium Gasoline  Small Sport Utility Vehicle 2WD   \n",
       "2      2950.0          0.0  Premium Gasoline                     Midsize Cars   \n",
       "3      2400.0          0.0  Premium Gasoline                 Minicompact Cars   \n",
       "4      2550.0          0.0  Regular Gasoline                    Minivan - 2WD   \n",
       "\n",
       "  atvType fuelType2  \n",
       "0     NaN       NaN  \n",
       "1     NaN       NaN  \n",
       "2     NaN       NaN  \n",
       "3     NaN       NaN  \n",
       "4     NaN       NaN  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Visualización de manera general\n",
    "df_vfed.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Cambiar de nombre a las columnas\n",
    "df_vfed.rename(columns={'city08':'Miles per gallon (mpg)','co2':'CO2 (p/mile)','fuelCost08':'FuelCost',\n",
    "                        'fuelCostA08':'FuelCostA','fuelType1':'Fuel',\n",
    "                        'VClass':'Category','fuelType2':'Alternative Fuel'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>Year</th>\n",
       "      <th>Manufacturer</th>\n",
       "      <th>Model</th>\n",
       "      <th>Miles per gallon (mpg)</th>\n",
       "      <th>CO2 (p/mile)</th>\n",
       "      <th>FuelCost</th>\n",
       "      <th>FuelCostA</th>\n",
       "      <th>Fuel</th>\n",
       "      <th>Category</th>\n",
       "      <th>atvType</th>\n",
       "      <th>Alternative Fuel</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017</td>\n",
       "      <td>BMW</td>\n",
       "      <td>M6 Coupe</td>\n",
       "      <td>15.0</td>\n",
       "      <td>519.0</td>\n",
       "      <td>3850.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Premium Gasoline</td>\n",
       "      <td>Subcompact Cars</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017</td>\n",
       "      <td>BMW</td>\n",
       "      <td>X3 sDrive28i</td>\n",
       "      <td>21.0</td>\n",
       "      <td>374.0</td>\n",
       "      <td>2700.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Premium Gasoline</td>\n",
       "      <td>Small Sport Utility Vehicle 2WD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016</td>\n",
       "      <td>Infiniti</td>\n",
       "      <td>Q50S Red Sport</td>\n",
       "      <td>20.0</td>\n",
       "      <td>402.0</td>\n",
       "      <td>2950.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Premium Gasoline</td>\n",
       "      <td>Midsize Cars</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017</td>\n",
       "      <td>MINI</td>\n",
       "      <td>John Cooper Works Convertible</td>\n",
       "      <td>24.0</td>\n",
       "      <td>327.0</td>\n",
       "      <td>2400.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Premium Gasoline</td>\n",
       "      <td>Minicompact Cars</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017</td>\n",
       "      <td>Kia</td>\n",
       "      <td>Sedona SX</td>\n",
       "      <td>18.0</td>\n",
       "      <td>433.0</td>\n",
       "      <td>2550.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Regular Gasoline</td>\n",
       "      <td>Minivan - 2WD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year Manufacturer                          Model  Miles per gallon (mpg)  \\\n",
       "0  2017          BMW                       M6 Coupe                    15.0   \n",
       "1  2017          BMW                   X3 sDrive28i                    21.0   \n",
       "2  2016     Infiniti                 Q50S Red Sport                    20.0   \n",
       "3  2017         MINI  John Cooper Works Convertible                    24.0   \n",
       "4  2017          Kia                      Sedona SX                    18.0   \n",
       "\n",
       "   CO2 (p/mile)  FuelCost  FuelCostA              Fuel  \\\n",
       "0         519.0    3850.0        0.0  Premium Gasoline   \n",
       "1         374.0    2700.0        0.0  Premium Gasoline   \n",
       "2         402.0    2950.0        0.0  Premium Gasoline   \n",
       "3         327.0    2400.0        0.0  Premium Gasoline   \n",
       "4         433.0    2550.0        0.0  Regular Gasoline   \n",
       "\n",
       "                          Category atvType Alternative Fuel  \n",
       "0                  Subcompact Cars     NaN              NaN  \n",
       "1  Small Sport Utility Vehicle 2WD     NaN              NaN  \n",
       "2                     Midsize Cars     NaN              NaN  \n",
       "3                 Minicompact Cars     NaN              NaN  \n",
       "4                    Minivan - 2WD     NaN              NaN  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Visualización de manera general\n",
    "df_vfed.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Cambiamos NaN del Combustible Alternativo a No\n",
    "df_vfed['Alternative Fuel'] = df_vfed['Alternative Fuel'].fillna('No')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Eliminamos las filas donde 'Fuel' es nulo\n",
    "df_vfed = df_vfed[df_vfed['Fuel'].notna()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4409"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Verificamos los duplicados\n",
    "df_vfed.duplicated().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Eliminamos las filas con duplicados\n",
    "df_vfed = df_vfed.drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Seteo el indice\n",
    "df_vfed.reset_index(drop=True, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>Year</th>\n",
       "      <th>Manufacturer</th>\n",
       "      <th>Model</th>\n",
       "      <th>Miles per gallon (mpg)</th>\n",
       "      <th>CO2 (p/mile)</th>\n",
       "      <th>FuelCost</th>\n",
       "      <th>FuelCostA</th>\n",
       "      <th>Fuel</th>\n",
       "      <th>Category</th>\n",
       "      <th>atvType</th>\n",
       "      <th>Alternative Fuel</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017</td>\n",
       "      <td>BMW</td>\n",
       "      <td>M6 Coupe</td>\n",
       "      <td>15.0</td>\n",
       "      <td>519.0</td>\n",
       "      <td>3850.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Premium Gasoline</td>\n",
       "      <td>Subcompact Cars</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017</td>\n",
       "      <td>BMW</td>\n",
       "      <td>X3 sDrive28i</td>\n",
       "      <td>21.0</td>\n",
       "      <td>374.0</td>\n",
       "      <td>2700.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Premium Gasoline</td>\n",
       "      <td>Small Sport Utility Vehicle 2WD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016</td>\n",
       "      <td>Infiniti</td>\n",
       "      <td>Q50S Red Sport</td>\n",
       "      <td>20.0</td>\n",
       "      <td>402.0</td>\n",
       "      <td>2950.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Premium Gasoline</td>\n",
       "      <td>Midsize Cars</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017</td>\n",
       "      <td>MINI</td>\n",
       "      <td>John Cooper Works Convertible</td>\n",
       "      <td>24.0</td>\n",
       "      <td>327.0</td>\n",
       "      <td>2400.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Premium Gasoline</td>\n",
       "      <td>Minicompact Cars</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017</td>\n",
       "      <td>Kia</td>\n",
       "      <td>Sedona SX</td>\n",
       "      <td>18.0</td>\n",
       "      <td>433.0</td>\n",
       "      <td>2550.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Regular Gasoline</td>\n",
       "      <td>Minivan - 2WD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year Manufacturer                          Model  Miles per gallon (mpg)  \\\n",
       "0  2017          BMW                       M6 Coupe                    15.0   \n",
       "1  2017          BMW                   X3 sDrive28i                    21.0   \n",
       "2  2016     Infiniti                 Q50S Red Sport                    20.0   \n",
       "3  2017         MINI  John Cooper Works Convertible                    24.0   \n",
       "4  2017          Kia                      Sedona SX                    18.0   \n",
       "\n",
       "   CO2 (p/mile)  FuelCost  FuelCostA              Fuel  \\\n",
       "0         519.0    3850.0        0.0  Premium Gasoline   \n",
       "1         374.0    2700.0        0.0  Premium Gasoline   \n",
       "2         402.0    2950.0        0.0  Premium Gasoline   \n",
       "3         327.0    2400.0        0.0  Premium Gasoline   \n",
       "4         433.0    2550.0        0.0  Regular Gasoline   \n",
       "\n",
       "                          Category atvType Alternative Fuel  \n",
       "0                  Subcompact Cars     NaN               No  \n",
       "1  Small Sport Utility Vehicle 2WD     NaN               No  \n",
       "2                     Midsize Cars     NaN               No  \n",
       "3                 Minicompact Cars     NaN               No  \n",
       "4                    Minivan - 2WD     NaN               No  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Muestro el Dataframe\n",
    "df_vfed.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 40544 entries, 0 to 40543\n",
      "Data columns (total 11 columns):\n",
      " #   Column                  Non-Null Count  Dtype  \n",
      "---  ------                  --------------  -----  \n",
      " 0   Year                    40544 non-null  int64  \n",
      " 1   Manufacturer            40544 non-null  object \n",
      " 2   Model                   40544 non-null  object \n",
      " 3   Miles per gallon (mpg)  40544 non-null  float64\n",
      " 4   CO2 (p/mile)            40544 non-null  float64\n",
      " 5   FuelCost                40544 non-null  float64\n",
      " 6   FuelCostA               40544 non-null  float64\n",
      " 7   Fuel                    40544 non-null  object \n",
      " 8   Category                40544 non-null  object \n",
      " 9   atvType                 4264 non-null   object \n",
      " 10  Alternative Fuel        40544 non-null  object \n",
      "dtypes: float64(4), int64(1), object(6)\n",
      "memory usage: 3.4+ MB\n"
     ]
    }
   ],
   "source": [
    "# Se analisa la informacion y estadisticas del nuevo dataset ya limpio con sus datos filtrados\n",
    "df_vfed.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "               Year  Miles per gallon (mpg)  CO2 (p/mile)      FuelCost  \\\n",
      "count  40544.000000            40544.000000  40544.000000  40544.000000   \n",
      "mean    2005.077669               19.484067    136.003848   2918.360300   \n",
      "std       12.032268               11.022238    201.700460    858.160834   \n",
      "min     1984.000000                6.000000     -1.000000    500.000000   \n",
      "25%     1994.000000               15.000000     -1.000000   2350.000000   \n",
      "50%     2007.000000               18.000000     -1.000000   2850.000000   \n",
      "75%     2016.000000               21.000000    338.000000   3450.000000   \n",
      "max     2024.000000              153.000000    979.000000   9350.000000   \n",
      "\n",
      "          FuelCostA  \n",
      "count  40544.000000  \n",
      "mean     124.282261  \n",
      "std      637.290660  \n",
      "min        0.000000  \n",
      "25%        0.000000  \n",
      "50%        0.000000  \n",
      "75%        0.000000  \n",
      "max     5000.000000  \n"
     ]
    }
   ],
   "source": [
    "# Estadisticas del Dataframe\n",
    "summary_stats = df_vfed.describe()\n",
    "print(summary_stats)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Guarda el dataframe en la carpeta Data\n",
    "df_vfed.to_parquet('Data/df_vfed.parquet', index=False)"
   ]
  }
 ],
 "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.10.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}