query_filter.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# Filter, Drop Nulls, Dedupe\n",
    "Use `data_08.csv` and `data_18.csv`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# load datasets\n",
    "\n",
    "df_08 = pd.read_csv('data_08.csv')\n",
    "df_18 = pd.read_csv('data_18.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2404, 14)"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# view dimensions of dataset\n",
    "df_08.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1611, 14)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# view dimensions of dataset\n",
    "df_18.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>model</th>\n",
       "      <th>displ</th>\n",
       "      <th>cyl</th>\n",
       "      <th>trans</th>\n",
       "      <th>drive</th>\n",
       "      <th>fuel</th>\n",
       "      <th>cert_region</th>\n",
       "      <th>veh_class</th>\n",
       "      <th>air_pollution_score</th>\n",
       "      <th>city_mpg</th>\n",
       "      <th>hwy_mpg</th>\n",
       "      <th>cmb_mpg</th>\n",
       "      <th>greenhouse_gas_score</th>\n",
       "      <th>smartway</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ACURA MDX</td>\n",
       "      <td>3.7</td>\n",
       "      <td>(6 cyl)</td>\n",
       "      <td>Auto-S5</td>\n",
       "      <td>4WD</td>\n",
       "      <td>Gasoline</td>\n",
       "      <td>CA</td>\n",
       "      <td>SUV</td>\n",
       "      <td>7</td>\n",
       "      <td>15</td>\n",
       "      <td>20</td>\n",
       "      <td>17</td>\n",
       "      <td>4</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ACURA MDX</td>\n",
       "      <td>3.7</td>\n",
       "      <td>(6 cyl)</td>\n",
       "      <td>Auto-S5</td>\n",
       "      <td>4WD</td>\n",
       "      <td>Gasoline</td>\n",
       "      <td>FA</td>\n",
       "      <td>SUV</td>\n",
       "      <td>6</td>\n",
       "      <td>15</td>\n",
       "      <td>20</td>\n",
       "      <td>17</td>\n",
       "      <td>4</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ACURA RDX</td>\n",
       "      <td>2.3</td>\n",
       "      <td>(4 cyl)</td>\n",
       "      <td>Auto-S5</td>\n",
       "      <td>4WD</td>\n",
       "      <td>Gasoline</td>\n",
       "      <td>CA</td>\n",
       "      <td>SUV</td>\n",
       "      <td>7</td>\n",
       "      <td>17</td>\n",
       "      <td>22</td>\n",
       "      <td>19</td>\n",
       "      <td>5</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ACURA RDX</td>\n",
       "      <td>2.3</td>\n",
       "      <td>(4 cyl)</td>\n",
       "      <td>Auto-S5</td>\n",
       "      <td>4WD</td>\n",
       "      <td>Gasoline</td>\n",
       "      <td>FA</td>\n",
       "      <td>SUV</td>\n",
       "      <td>6</td>\n",
       "      <td>17</td>\n",
       "      <td>22</td>\n",
       "      <td>19</td>\n",
       "      <td>5</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ACURA RL</td>\n",
       "      <td>3.5</td>\n",
       "      <td>(6 cyl)</td>\n",
       "      <td>Auto-S5</td>\n",
       "      <td>4WD</td>\n",
       "      <td>Gasoline</td>\n",
       "      <td>CA</td>\n",
       "      <td>midsize car</td>\n",
       "      <td>7</td>\n",
       "      <td>16</td>\n",
       "      <td>24</td>\n",
       "      <td>19</td>\n",
       "      <td>5</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>ACURA RL</td>\n",
       "      <td>3.5</td>\n",
       "      <td>(6 cyl)</td>\n",
       "      <td>Auto-S5</td>\n",
       "      <td>4WD</td>\n",
       "      <td>Gasoline</td>\n",
       "      <td>FA</td>\n",
       "      <td>midsize car</td>\n",
       "      <td>6</td>\n",
       "      <td>16</td>\n",
       "      <td>24</td>\n",
       "      <td>19</td>\n",
       "      <td>5</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>ACURA TL</td>\n",
       "      <td>3.2</td>\n",
       "      <td>(6 cyl)</td>\n",
       "      <td>Auto-S5</td>\n",
       "      <td>2WD</td>\n",
       "      <td>Gasoline</td>\n",
       "      <td>CA</td>\n",
       "      <td>midsize car</td>\n",
       "      <td>7</td>\n",
       "      <td>18</td>\n",
       "      <td>26</td>\n",
       "      <td>21</td>\n",
       "      <td>6</td>\n",
       "      <td>yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>ACURA TL</td>\n",
       "      <td>3.5</td>\n",
       "      <td>(6 cyl)</td>\n",
       "      <td>Auto-S5</td>\n",
       "      <td>2WD</td>\n",
       "      <td>Gasoline</td>\n",
       "      <td>CA</td>\n",
       "      <td>midsize car</td>\n",
       "      <td>7</td>\n",
       "      <td>17</td>\n",
       "      <td>26</td>\n",
       "      <td>20</td>\n",
       "      <td>6</td>\n",
       "      <td>yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>ACURA TL</td>\n",
       "      <td>3.5</td>\n",
       "      <td>(6 cyl)</td>\n",
       "      <td>Man-6</td>\n",
       "      <td>2WD</td>\n",
       "      <td>Gasoline</td>\n",
       "      <td>CA</td>\n",
       "      <td>midsize car</td>\n",
       "      <td>7</td>\n",
       "      <td>18</td>\n",
       "      <td>27</td>\n",
       "      <td>21</td>\n",
       "      <td>6</td>\n",
       "      <td>yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>ACURA TL</td>\n",
       "      <td>3.2</td>\n",
       "      <td>(6 cyl)</td>\n",
       "      <td>Auto-S5</td>\n",
       "      <td>2WD</td>\n",
       "      <td>Gasoline</td>\n",
       "      <td>FA</td>\n",
       "      <td>midsize car</td>\n",
       "      <td>6</td>\n",
       "      <td>18</td>\n",
       "      <td>26</td>\n",
       "      <td>21</td>\n",
       "      <td>6</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>ACURA TL</td>\n",
       "      <td>3.5</td>\n",
       "      <td>(6 cyl)</td>\n",
       "      <td>Auto-S5</td>\n",
       "      <td>2WD</td>\n",
       "      <td>Gasoline</td>\n",
       "      <td>FA</td>\n",
       "      <td>midsize car</td>\n",
       "      <td>6</td>\n",
       "      <td>17</td>\n",
       "      <td>26</td>\n",
       "      <td>20</td>\n",
       "      <td>6</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>ACURA TL</td>\n",
       "      <td>3.5</td>\n",
       "      <td>(6 cyl)</td>\n",
       "      <td>Man-6</td>\n",
       "      <td>2WD</td>\n",
       "      <td>Gasoline</td>\n",
       "      <td>FA</td>\n",
       "      <td>midsize car</td>\n",
       "      <td>6</td>\n",
       "      <td>18</td>\n",
       "      <td>27</td>\n",
       "      <td>21</td>\n",
       "      <td>6</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>ACURA TSX</td>\n",
       "      <td>2.4</td>\n",
       "      <td>(4 cyl)</td>\n",
       "      <td>Man-6</td>\n",
       "      <td>2WD</td>\n",
       "      <td>Gasoline</td>\n",
       "      <td>FA</td>\n",
       "      <td>small car</td>\n",
       "      <td>6</td>\n",
       "      <td>19</td>\n",
       "      <td>28</td>\n",
       "      <td>22</td>\n",
       "      <td>6</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>ACURA TSX</td>\n",
       "      <td>2.4</td>\n",
       "      <td>(4 cyl)</td>\n",
       "      <td>Auto-S5</td>\n",
       "      <td>2WD</td>\n",
       "      <td>Gasoline</td>\n",
       "      <td>FA</td>\n",
       "      <td>small car</td>\n",
       "      <td>6</td>\n",
       "      <td>20</td>\n",
       "      <td>28</td>\n",
       "      <td>23</td>\n",
       "      <td>7</td>\n",
       "      <td>yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>ACURA TSX</td>\n",
       "      <td>2.4</td>\n",
       "      <td>(4 cyl)</td>\n",
       "      <td>Man-6</td>\n",
       "      <td>2WD</td>\n",
       "      <td>Gasoline</td>\n",
       "      <td>CA</td>\n",
       "      <td>small car</td>\n",
       "      <td>6</td>\n",
       "      <td>19</td>\n",
       "      <td>28</td>\n",
       "      <td>22</td>\n",
       "      <td>6</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>ACURA TSX</td>\n",
       "      <td>2.4</td>\n",
       "      <td>(4 cyl)</td>\n",
       "      <td>Auto-S5</td>\n",
       "      <td>2WD</td>\n",
       "      <td>Gasoline</td>\n",
       "      <td>CA</td>\n",
       "      <td>small car</td>\n",
       "      <td>6</td>\n",
       "      <td>20</td>\n",
       "      <td>28</td>\n",
       "      <td>23</td>\n",
       "      <td>7</td>\n",
       "      <td>yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>ASTON MARTIN DB9</td>\n",
       "      <td>5.9</td>\n",
       "      <td>(12 cyl)</td>\n",
       "      <td>Man-6</td>\n",
       "      <td>2WD</td>\n",
       "      <td>Gasoline</td>\n",
       "      <td>FA</td>\n",
       "      <td>small car</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "      <td>16</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>ASTON MARTIN DB9</td>\n",
       "      <td>5.9</td>\n",
       "      <td>(12 cyl)</td>\n",
       "      <td>Auto-S6</td>\n",
       "      <td>2WD</td>\n",
       "      <td>Gasoline</td>\n",
       "      <td>FA</td>\n",
       "      <td>small car</td>\n",
       "      <td>6</td>\n",
       "      <td>11</td>\n",
       "      <td>18</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>ASTON MARTIN DB9</td>\n",
       "      <td>5.9</td>\n",
       "      <td>(12 cyl)</td>\n",
       "      <td>Man-6</td>\n",
       "      <td>2WD</td>\n",
       "      <td>Gasoline</td>\n",
       "      <td>CA</td>\n",
       "      <td>small car</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "      <td>16</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>ASTON MARTIN DB9</td>\n",
       "      <td>5.9</td>\n",
       "      <td>(12 cyl)</td>\n",
       "      <td>Auto-S6</td>\n",
       "      <td>2WD</td>\n",
       "      <td>Gasoline</td>\n",
       "      <td>CA</td>\n",
       "      <td>small car</td>\n",
       "      <td>6</td>\n",
       "      <td>11</td>\n",
       "      <td>18</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>ASTON MARTIN DB9 Volante</td>\n",
       "      <td>5.9</td>\n",
       "      <td>(12 cyl)</td>\n",
       "      <td>Man-6</td>\n",
       "      <td>2WD</td>\n",
       "      <td>Gasoline</td>\n",
       "      <td>FA</td>\n",
       "      <td>small car</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "      <td>16</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>ASTON MARTIN DB9 Volante</td>\n",
       "      <td>5.9</td>\n",
       "      <td>(12 cyl)</td>\n",
       "      <td>Auto-S6</td>\n",
       "      <td>2WD</td>\n",
       "      <td>Gasoline</td>\n",
       "      <td>FA</td>\n",
       "      <td>small car</td>\n",
       "      <td>6</td>\n",
       "      <td>11</td>\n",
       "      <td>17</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>ASTON MARTIN DB9 Volante</td>\n",
       "      <td>5.9</td>\n",
       "      <td>(12 cyl)</td>\n",
       "      <td>Man-6</td>\n",
       "      <td>2WD</td>\n",
       "      <td>Gasoline</td>\n",
       "      <td>CA</td>\n",
       "      <td>small car</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "      <td>16</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>ASTON MARTIN DB9 Volante</td>\n",
       "      <td>5.9</td>\n",
       "      <td>(12 cyl)</td>\n",
       "      <td>Auto-S6</td>\n",
       "      <td>2WD</td>\n",
       "      <td>Gasoline</td>\n",
       "      <td>CA</td>\n",
       "      <td>small car</td>\n",
       "      <td>6</td>\n",
       "      <td>11</td>\n",
       "      <td>17</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>ASTON MARTIN V8 Vantage</td>\n",
       "      <td>4.3</td>\n",
       "      <td>(8 cyl)</td>\n",
       "      <td>Man-6</td>\n",
       "      <td>2WD</td>\n",
       "      <td>Gasoline</td>\n",
       "      <td>FA</td>\n",
       "      <td>small car</td>\n",
       "      <td>6</td>\n",
       "      <td>12</td>\n",
       "      <td>19</td>\n",
       "      <td>15</td>\n",
       "      <td>3</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       model  displ       cyl    trans drive      fuel  \\\n",
       "0                  ACURA MDX    3.7   (6 cyl)  Auto-S5   4WD  Gasoline   \n",
       "1                  ACURA MDX    3.7   (6 cyl)  Auto-S5   4WD  Gasoline   \n",
       "2                  ACURA RDX    2.3   (4 cyl)  Auto-S5   4WD  Gasoline   \n",
       "3                  ACURA RDX    2.3   (4 cyl)  Auto-S5   4WD  Gasoline   \n",
       "4                   ACURA RL    3.5   (6 cyl)  Auto-S5   4WD  Gasoline   \n",
       "5                   ACURA RL    3.5   (6 cyl)  Auto-S5   4WD  Gasoline   \n",
       "6                   ACURA TL    3.2   (6 cyl)  Auto-S5   2WD  Gasoline   \n",
       "7                   ACURA TL    3.5   (6 cyl)  Auto-S5   2WD  Gasoline   \n",
       "8                   ACURA TL    3.5   (6 cyl)    Man-6   2WD  Gasoline   \n",
       "9                   ACURA TL    3.2   (6 cyl)  Auto-S5   2WD  Gasoline   \n",
       "10                  ACURA TL    3.5   (6 cyl)  Auto-S5   2WD  Gasoline   \n",
       "11                  ACURA TL    3.5   (6 cyl)    Man-6   2WD  Gasoline   \n",
       "12                 ACURA TSX    2.4   (4 cyl)    Man-6   2WD  Gasoline   \n",
       "13                 ACURA TSX    2.4   (4 cyl)  Auto-S5   2WD  Gasoline   \n",
       "14                 ACURA TSX    2.4   (4 cyl)    Man-6   2WD  Gasoline   \n",
       "15                 ACURA TSX    2.4   (4 cyl)  Auto-S5   2WD  Gasoline   \n",
       "16          ASTON MARTIN DB9    5.9  (12 cyl)    Man-6   2WD  Gasoline   \n",
       "17          ASTON MARTIN DB9    5.9  (12 cyl)  Auto-S6   2WD  Gasoline   \n",
       "18          ASTON MARTIN DB9    5.9  (12 cyl)    Man-6   2WD  Gasoline   \n",
       "19          ASTON MARTIN DB9    5.9  (12 cyl)  Auto-S6   2WD  Gasoline   \n",
       "20  ASTON MARTIN DB9 Volante    5.9  (12 cyl)    Man-6   2WD  Gasoline   \n",
       "21  ASTON MARTIN DB9 Volante    5.9  (12 cyl)  Auto-S6   2WD  Gasoline   \n",
       "22  ASTON MARTIN DB9 Volante    5.9  (12 cyl)    Man-6   2WD  Gasoline   \n",
       "23  ASTON MARTIN DB9 Volante    5.9  (12 cyl)  Auto-S6   2WD  Gasoline   \n",
       "24   ASTON MARTIN V8 Vantage    4.3   (8 cyl)    Man-6   2WD  Gasoline   \n",
       "\n",
       "   cert_region    veh_class air_pollution_score city_mpg hwy_mpg cmb_mpg  \\\n",
       "0           CA          SUV                   7       15      20      17   \n",
       "1           FA          SUV                   6       15      20      17   \n",
       "2           CA          SUV                   7       17      22      19   \n",
       "3           FA          SUV                   6       17      22      19   \n",
       "4           CA  midsize car                   7       16      24      19   \n",
       "5           FA  midsize car                   6       16      24      19   \n",
       "6           CA  midsize car                   7       18      26      21   \n",
       "7           CA  midsize car                   7       17      26      20   \n",
       "8           CA  midsize car                   7       18      27      21   \n",
       "9           FA  midsize car                   6       18      26      21   \n",
       "10          FA  midsize car                   6       17      26      20   \n",
       "11          FA  midsize car                   6       18      27      21   \n",
       "12          FA    small car                   6       19      28      22   \n",
       "13          FA    small car                   6       20      28      23   \n",
       "14          CA    small car                   6       19      28      22   \n",
       "15          CA    small car                   6       20      28      23   \n",
       "16          FA    small car                   6       10      16      12   \n",
       "17          FA    small car                   6       11      18      13   \n",
       "18          CA    small car                   6       10      16      12   \n",
       "19          CA    small car                   6       11      18      13   \n",
       "20          FA    small car                   6       10      16      12   \n",
       "21          FA    small car                   6       11      17      13   \n",
       "22          CA    small car                   6       10      16      12   \n",
       "23          CA    small car                   6       11      17      13   \n",
       "24          FA    small car                   6       12      19      15   \n",
       "\n",
       "   greenhouse_gas_score smartway  \n",
       "0                     4       no  \n",
       "1                     4       no  \n",
       "2                     5       no  \n",
       "3                     5       no  \n",
       "4                     5       no  \n",
       "5                     5       no  \n",
       "6                     6      yes  \n",
       "7                     6      yes  \n",
       "8                     6      yes  \n",
       "9                     6       no  \n",
       "10                    6       no  \n",
       "11                    6       no  \n",
       "12                    6       no  \n",
       "13                    7      yes  \n",
       "14                    6       no  \n",
       "15                    7      yes  \n",
       "16                    0       no  \n",
       "17                    1       no  \n",
       "18                    0       no  \n",
       "19                    1       no  \n",
       "20                    0       no  \n",
       "21                    1       no  \n",
       "22                    0       no  \n",
       "23                    1       no  \n",
       "24                    3       no  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_08.head(25)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Filter by Certification Region"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# filter datasets for rows following California standards\n",
    "df_08 = df_08[df_08['cert_region'] == 'CA']\n",
    "df_18 = df_18[df_18['cert_region'] == 'CA']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['CA'], dtype=object)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# confirm only certification region is California\n",
    "df_08['cert_region'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['CA'], dtype=object)"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# confirm only certification region is California\n",
    "df_18['cert_region'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# drop certification region columns form both datasets\n",
    "df_08.drop('cert_region', axis=1, inplace=True)\n",
    "df_18.drop('cert_region', axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1084, 13)"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_08.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(798, 13)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_18.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Drop Rows with Missing Values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "model                    0\n",
       "displ                    0\n",
       "cyl                     75\n",
       "trans                   75\n",
       "drive                   37\n",
       "fuel                     0\n",
       "veh_class                0\n",
       "air_pollution_score      0\n",
       "city_mpg                75\n",
       "hwy_mpg                 75\n",
       "cmb_mpg                 75\n",
       "greenhouse_gas_score    75\n",
       "smartway                 0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# view missing value count for each feature in 2008\n",
    "df_08.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "model                   0\n",
       "displ                   1\n",
       "cyl                     1\n",
       "trans                   0\n",
       "drive                   0\n",
       "fuel                    0\n",
       "veh_class               0\n",
       "air_pollution_score     0\n",
       "city_mpg                0\n",
       "hwy_mpg                 0\n",
       "cmb_mpg                 0\n",
       "greenhouse_gas_score    0\n",
       "smartway                0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# view missing value count for each feature in 2018\n",
    "df_18.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# drop rows with any null values in both datasets\n",
    "df_08.dropna(axis=0, inplace=True)\n",
    "df_18.dropna(axis=0, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# checks if any of columns in 2008 have null values - should print False\n",
    "df_08.isnull().sum().any()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# checks if any of columns in 2018 have null values - should print False\n",
    "df_18.isnull().sum().any()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Dedupe Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "23"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# print number of duplicates in 2008 and 2018 datasets\n",
    "#2008 dataset\n",
    "sum(df_08.duplicated())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#2018 datasets\n",
    "sum(df_18.duplicated())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop duplicates in both datasets\n",
    "df_08.drop_duplicates(inplace=True)\n",
    "df_18.drop_duplicates(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# print number of duplicates again to confirm dedupe - should both be 0\n",
    "sum(df_08.duplicated())\n",
    "sum(df_18.duplicated())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# save progress for the next section\n",
    "df_08.to_csv('data_08.csv', index=False)\n",
    "df_18.to_csv('data_18.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "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.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}