intro-to-pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pandas for Exploratory Data Analysis I \n",
    "\n",
    "Pandas is the most prominent Python library for exploratory data analysis (EDA). The functions Pandas supports are integral to understanding, formatting, and preparing our data. Formally, we use Pandas to investigate, wrangle, munge, and clean our data. Pandas is the Swiss Army Knife of data manipulation!\n",
    "\n",
    "We'll have two coding-heavy sessions on Pandas. In this one, we'll use Pandas to:\n",
    "\n",
    "* Read in a dataset\n",
    "* Investigate a dataset's integrity\n",
    "* Filter, sort, and manipulate a DataFrame's series\n",
    "\n",
    "## About the Dataset: Adventure Works Cycles\n",
    "\n",
    "<img src=\"assets/adventure_works_logo.png\" align=\"right\">\n",
    "\n",
    "For today's Pandas exercises, we will be using a dataset developed by Microsoft for training purposes, for a fictional company known as [Adventure Works Cycles](https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks) (AWC), a multinational manufacturer and seller of bicycles and accessories.\n",
    "\n",
    "A full data dictionary for the Production.Product table is reproduced below, which is a description of the fields (columns) in the table (It is also in the class notes, or [at this link](https://www.sqldatadictionary.com/AdventureWorks2014/)):\n",
    "\n",
    "| Field | Description |\n",
    "| :-- | :-- |\n",
    "| **ProductID** | Primary key for Product records\n",
    "| **Name** | Name of the product\n",
    "| **ProductNumber** | Unique product identification number\n",
    "| **MakeFlag** | 0 = Product is purchased, 1 = Product is manufactured in-house.\n",
    "| **FinishedGoodsFlag** | 0 = Product is not a salable item. 1 = Product is salable\n",
    "| **Color** | Product color\n",
    "| **SafetyStockLevel** | Minimum inventory quantity\n",
    "| **ReorderPoint** | Inventory level that triggers a purchase order or work order\n",
    "| **StandardCost** | Standard cost of the product\n",
    "| **ListPrice** | Selling price\n",
    "| **Size** | Product size\n",
    "| **SizeUnitMeasureCode** | Unit of measure for the Size column\n",
    "| **WeightUnitMeasureCode** | Unit of measure for the Weight column\n",
    "| **DaysToManufacture** | Number of days required to manufacture the product\n",
    "| **ProductLine** | R = Road, M = Mountain, T = Touring, S = Standard\n",
    "| **Class** | H = High, M = Medium, L = Low\n",
    "| **Style** | W = Womens, M = Mens, U = Universal\n",
    "| **ProductSubcategoryID** | Product is a member of this product subcategory. Foreign key to ProductSubCategory.ProductSubCategoryID\n",
    "| **ProductModelID** | Product is a member of this product model. Foreign key to ProductModel.ProductModelID\n",
    "| **SellStartDate** | Date the product was available for sale\n",
    "| **SellEndDate** | Date the product was no longer available for sale\n",
    "| **DiscontinuedDate** | Date the product was discontinued\n",
    "| **rowguid** | ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample\n",
    "| **ModifiedDate** | Date and time the record was last updated\n",
    "\n",
    "## Importing Pandas\n",
    "\n",
    "To [import a library](https://docs.python.org/3/reference/import.html), we write `import` and the library name. For Pandas, is it common to nickname the library `pd` so that when we reference a function from the Pandas library, we only have to write `pd` -- and not `pandas`, just a bit shorter. (This is called [namespacing](https://docs.python.org/3/tutorial/classes.html#python-scopes-and-namespaces).)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see the details about the imported package by referencing its private class variables:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "I am using pandas Version: 0.25.1.\n",
      "It is installed at: ['/Users/rebeccapeterson/.pyenv/versions/anaconda3-2019.10/lib/python3.7/site-packages/pandas']\n"
     ]
    }
   ],
   "source": [
    "print(f'I am using {pd.__name__} Version: {pd.__version__}.')\n",
    "print(f'It is installed at: {pd.__path__}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "# The Basics\n",
    "\n",
    "## Reading in Data\n",
    "\n",
    "Pandas dramatically simplifies the process of reading in data. By \"reading in data,\" we mean loading a file into our machine's memory.\n",
    "\n",
    "When you have a CSV, for example, and then you double-click to open it in Microsoft Excel, the open file is \"read into memory.\" You can now manipulate the spreadsheet.\n",
    "\n",
    "When we read data into memory in Python, we are creating object -- specifically, an instance of a `DataFrame`. We will soon explore this object. *(**Note**: as an aside, when we have a file that is greater than the size of our computer's memory, this is approaching \"Big Data.\")*\n",
    "\n",
    "Because we are working with a CSV, we will use the [read CSV](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_csv.html) method.\n",
    "\n",
    "A [delimiter](https://en.wikipedia.org/wiki/Delimiter-separated_values) is a character that separates fields (columns) in the imported file. Just because a file says `.csv` (recall: Comma Separated Values) does not necessarily mean that a comma is used as the delimiter. In this case, we have a tab character as the delimiter for our columns, so we will be using `sep='\\t'` to tell pandas to 'cut' the columns every time it sees a [tab character in the file](http://vim.wikia.com/wiki/Showing_the_ASCII_value_of_the_current_character).\n",
    "\n",
    "Here's how you read the dataset as a DataFrame into a variable named `products`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "products = pd.read_csv('data/Production.Product.csv', sep='\\t')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Use `.head()` to show the head of this dataset:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>ProductID</th>\n",
       "      <th>Name</th>\n",
       "      <th>ProductNumber</th>\n",
       "      <th>MakeFlag</th>\n",
       "      <th>FinishedGoodsFlag</th>\n",
       "      <th>Color</th>\n",
       "      <th>SafetyStockLevel</th>\n",
       "      <th>ReorderPoint</th>\n",
       "      <th>StandardCost</th>\n",
       "      <th>ListPrice</th>\n",
       "      <th>...</th>\n",
       "      <th>ProductLine</th>\n",
       "      <th>Class</th>\n",
       "      <th>Style</th>\n",
       "      <th>ProductSubcategoryID</th>\n",
       "      <th>ProductModelID</th>\n",
       "      <th>SellStartDate</th>\n",
       "      <th>SellEndDate</th>\n",
       "      <th>DiscontinuedDate</th>\n",
       "      <th>rowguid</th>\n",
       "      <th>ModifiedDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Adjustable Race</td>\n",
       "      <td>AR-5381</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1000</td>\n",
       "      <td>750</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2008-04-30 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{694215B7-08F7-4C0D-ACB1-D734BA44C0C8}</td>\n",
       "      <td>2014-02-08 10:01:36.827000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Bearing Ball</td>\n",
       "      <td>BA-8327</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1000</td>\n",
       "      <td>750</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2008-04-30 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{58AE3C20-4F3A-4749-A7D4-D568806CC537}</td>\n",
       "      <td>2014-02-08 10:01:36.827000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>BB Ball Bearing</td>\n",
       "      <td>BE-2349</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>800</td>\n",
       "      <td>600</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2008-04-30 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{9C21AED2-5BFA-4F18-BCB8-F11638DC2E4E}</td>\n",
       "      <td>2014-02-08 10:01:36.827000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>Headset Ball Bearings</td>\n",
       "      <td>BE-2908</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>800</td>\n",
       "      <td>600</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2008-04-30 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{ECFED6CB-51FF-49B5-B06C-7D8AC834DB8B}</td>\n",
       "      <td>2014-02-08 10:01:36.827000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>316</td>\n",
       "      <td>Blade</td>\n",
       "      <td>BL-2036</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>800</td>\n",
       "      <td>600</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2008-04-30 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{E73E9750-603B-4131-89F5-3DD15ED5FF80}</td>\n",
       "      <td>2014-02-08 10:01:36.827000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   ProductID                   Name ProductNumber  MakeFlag  \\\n",
       "0          1        Adjustable Race       AR-5381         0   \n",
       "1          2           Bearing Ball       BA-8327         0   \n",
       "2          3        BB Ball Bearing       BE-2349         1   \n",
       "3          4  Headset Ball Bearings       BE-2908         0   \n",
       "4        316                  Blade       BL-2036         1   \n",
       "\n",
       "   FinishedGoodsFlag Color  SafetyStockLevel  ReorderPoint  StandardCost  \\\n",
       "0                  0   NaN              1000           750           0.0   \n",
       "1                  0   NaN              1000           750           0.0   \n",
       "2                  0   NaN               800           600           0.0   \n",
       "3                  0   NaN               800           600           0.0   \n",
       "4                  0   NaN               800           600           0.0   \n",
       "\n",
       "   ListPrice  ... ProductLine Class Style  ProductSubcategoryID  \\\n",
       "0        0.0  ...         NaN   NaN   NaN                   NaN   \n",
       "1        0.0  ...         NaN   NaN   NaN                   NaN   \n",
       "2        0.0  ...         NaN   NaN   NaN                   NaN   \n",
       "3        0.0  ...         NaN   NaN   NaN                   NaN   \n",
       "4        0.0  ...         NaN   NaN   NaN                   NaN   \n",
       "\n",
       "   ProductModelID        SellStartDate SellEndDate DiscontinuedDate  \\\n",
       "0             NaN  2008-04-30 00:00:00         NaN              NaN   \n",
       "1             NaN  2008-04-30 00:00:00         NaN              NaN   \n",
       "2             NaN  2008-04-30 00:00:00         NaN              NaN   \n",
       "3             NaN  2008-04-30 00:00:00         NaN              NaN   \n",
       "4             NaN  2008-04-30 00:00:00         NaN              NaN   \n",
       "\n",
       "                                  rowguid                   ModifiedDate  \n",
       "0  {694215B7-08F7-4C0D-ACB1-D734BA44C0C8}  2014-02-08 10:01:36.827000000  \n",
       "1  {58AE3C20-4F3A-4749-A7D4-D568806CC537}  2014-02-08 10:01:36.827000000  \n",
       "2  {9C21AED2-5BFA-4F18-BCB8-F11638DC2E4E}  2014-02-08 10:01:36.827000000  \n",
       "3  {ECFED6CB-51FF-49B5-B06C-7D8AC834DB8B}  2014-02-08 10:01:36.827000000  \n",
       "4  {E73E9750-603B-4131-89F5-3DD15ED5FF80}  2014-02-08 10:01:36.827000000  \n",
       "\n",
       "[5 rows x 25 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Documentation Pause\n",
    "\n",
    "How did we know how to use `pd.read_csv`? Let's take a look at the [documentation](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_csv.html). Note the first parameter required (`filepath_or_buffer`) as well as the `sep` parameter.\n",
    "\n",
    "Right now we don't care too much about the other parameters, suffice to know they exist and you may need them someday!\n",
    "\n",
    "### Back to `products`\n",
    "\n",
    "We have just created a data structure called a `DataFrame`. See?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.frame.DataFrame"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(products)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Inspecting our DataFrame\n",
    "\n",
    "We'll now perform basic operations on the DataFrame.\n",
    "\n",
    "Here's how to output the first 3 rows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>ProductID</th>\n",
       "      <th>Name</th>\n",
       "      <th>ProductNumber</th>\n",
       "      <th>MakeFlag</th>\n",
       "      <th>FinishedGoodsFlag</th>\n",
       "      <th>Color</th>\n",
       "      <th>SafetyStockLevel</th>\n",
       "      <th>ReorderPoint</th>\n",
       "      <th>StandardCost</th>\n",
       "      <th>ListPrice</th>\n",
       "      <th>...</th>\n",
       "      <th>ProductLine</th>\n",
       "      <th>Class</th>\n",
       "      <th>Style</th>\n",
       "      <th>ProductSubcategoryID</th>\n",
       "      <th>ProductModelID</th>\n",
       "      <th>SellStartDate</th>\n",
       "      <th>SellEndDate</th>\n",
       "      <th>DiscontinuedDate</th>\n",
       "      <th>rowguid</th>\n",
       "      <th>ModifiedDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Adjustable Race</td>\n",
       "      <td>AR-5381</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1000</td>\n",
       "      <td>750</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2008-04-30 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{694215B7-08F7-4C0D-ACB1-D734BA44C0C8}</td>\n",
       "      <td>2014-02-08 10:01:36.827000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Bearing Ball</td>\n",
       "      <td>BA-8327</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1000</td>\n",
       "      <td>750</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2008-04-30 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{58AE3C20-4F3A-4749-A7D4-D568806CC537}</td>\n",
       "      <td>2014-02-08 10:01:36.827000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>BB Ball Bearing</td>\n",
       "      <td>BE-2349</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>800</td>\n",
       "      <td>600</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2008-04-30 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{9C21AED2-5BFA-4F18-BCB8-F11638DC2E4E}</td>\n",
       "      <td>2014-02-08 10:01:36.827000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   ProductID             Name ProductNumber  MakeFlag  FinishedGoodsFlag  \\\n",
       "0          1  Adjustable Race       AR-5381         0                  0   \n",
       "1          2     Bearing Ball       BA-8327         0                  0   \n",
       "2          3  BB Ball Bearing       BE-2349         1                  0   \n",
       "\n",
       "  Color  SafetyStockLevel  ReorderPoint  StandardCost  ListPrice  ...  \\\n",
       "0   NaN              1000           750           0.0        0.0  ...   \n",
       "1   NaN              1000           750           0.0        0.0  ...   \n",
       "2   NaN               800           600           0.0        0.0  ...   \n",
       "\n",
       "  ProductLine Class Style  ProductSubcategoryID  ProductModelID  \\\n",
       "0         NaN   NaN   NaN                   NaN             NaN   \n",
       "1         NaN   NaN   NaN                   NaN             NaN   \n",
       "2         NaN   NaN   NaN                   NaN             NaN   \n",
       "\n",
       "         SellStartDate SellEndDate DiscontinuedDate  \\\n",
       "0  2008-04-30 00:00:00         NaN              NaN   \n",
       "1  2008-04-30 00:00:00         NaN              NaN   \n",
       "2  2008-04-30 00:00:00         NaN              NaN   \n",
       "\n",
       "                                  rowguid                   ModifiedDate  \n",
       "0  {694215B7-08F7-4C0D-ACB1-D734BA44C0C8}  2014-02-08 10:01:36.827000000  \n",
       "1  {58AE3C20-4F3A-4749-A7D4-D568806CC537}  2014-02-08 10:01:36.827000000  \n",
       "2  {9C21AED2-5BFA-4F18-BCB8-F11638DC2E4E}  2014-02-08 10:01:36.827000000  \n",
       "\n",
       "[3 rows x 25 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that `.head()` is a method, so it takes arguments.\n",
    "\n",
    "* What do you think changes if we pass a different number `head()` argument?\n",
    "* How would we print the last 5 rows?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>ProductID</th>\n",
       "      <th>Name</th>\n",
       "      <th>ProductNumber</th>\n",
       "      <th>MakeFlag</th>\n",
       "      <th>FinishedGoodsFlag</th>\n",
       "      <th>Color</th>\n",
       "      <th>SafetyStockLevel</th>\n",
       "      <th>ReorderPoint</th>\n",
       "      <th>StandardCost</th>\n",
       "      <th>ListPrice</th>\n",
       "      <th>...</th>\n",
       "      <th>ProductLine</th>\n",
       "      <th>Class</th>\n",
       "      <th>Style</th>\n",
       "      <th>ProductSubcategoryID</th>\n",
       "      <th>ProductModelID</th>\n",
       "      <th>SellStartDate</th>\n",
       "      <th>SellEndDate</th>\n",
       "      <th>DiscontinuedDate</th>\n",
       "      <th>rowguid</th>\n",
       "      <th>ModifiedDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>501</td>\n",
       "      <td>997</td>\n",
       "      <td>Road-750 Black, 44</td>\n",
       "      <td>BK-R19B-44</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Black</td>\n",
       "      <td>100</td>\n",
       "      <td>75</td>\n",
       "      <td>343.6496</td>\n",
       "      <td>539.99</td>\n",
       "      <td>...</td>\n",
       "      <td>R</td>\n",
       "      <td>L</td>\n",
       "      <td>U</td>\n",
       "      <td>2.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>2013-05-30 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{44CE4802-409F-43AB-9B27-CA53421805BE}</td>\n",
       "      <td>2014-02-08 10:01:36.827000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>502</td>\n",
       "      <td>998</td>\n",
       "      <td>Road-750 Black, 48</td>\n",
       "      <td>BK-R19B-48</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Black</td>\n",
       "      <td>100</td>\n",
       "      <td>75</td>\n",
       "      <td>343.6496</td>\n",
       "      <td>539.99</td>\n",
       "      <td>...</td>\n",
       "      <td>R</td>\n",
       "      <td>L</td>\n",
       "      <td>U</td>\n",
       "      <td>2.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>2013-05-30 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{3DE9A212-1D49-40B6-B10A-F564D981DBDE}</td>\n",
       "      <td>2014-02-08 10:01:36.827000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>503</td>\n",
       "      <td>999</td>\n",
       "      <td>Road-750 Black, 52</td>\n",
       "      <td>BK-R19B-52</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Black</td>\n",
       "      <td>100</td>\n",
       "      <td>75</td>\n",
       "      <td>343.6496</td>\n",
       "      <td>539.99</td>\n",
       "      <td>...</td>\n",
       "      <td>R</td>\n",
       "      <td>L</td>\n",
       "      <td>U</td>\n",
       "      <td>2.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>2013-05-30 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{AE638923-2B67-4679-B90E-ABBAB17DCA31}</td>\n",
       "      <td>2014-02-08 10:01:36.827000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     ProductID                Name ProductNumber  MakeFlag  FinishedGoodsFlag  \\\n",
       "501        997  Road-750 Black, 44    BK-R19B-44         1                  1   \n",
       "502        998  Road-750 Black, 48    BK-R19B-48         1                  1   \n",
       "503        999  Road-750 Black, 52    BK-R19B-52         1                  1   \n",
       "\n",
       "     Color  SafetyStockLevel  ReorderPoint  StandardCost  ListPrice  ...  \\\n",
       "501  Black               100            75      343.6496     539.99  ...   \n",
       "502  Black               100            75      343.6496     539.99  ...   \n",
       "503  Black               100            75      343.6496     539.99  ...   \n",
       "\n",
       "    ProductLine Class Style  ProductSubcategoryID  ProductModelID  \\\n",
       "501          R     L     U                    2.0            31.0   \n",
       "502          R     L     U                    2.0            31.0   \n",
       "503          R     L     U                    2.0            31.0   \n",
       "\n",
       "           SellStartDate SellEndDate DiscontinuedDate  \\\n",
       "501  2013-05-30 00:00:00         NaN              NaN   \n",
       "502  2013-05-30 00:00:00         NaN              NaN   \n",
       "503  2013-05-30 00:00:00         NaN              NaN   \n",
       "\n",
       "                                    rowguid                   ModifiedDate  \n",
       "501  {44CE4802-409F-43AB-9B27-CA53421805BE}  2014-02-08 10:01:36.827000000  \n",
       "502  {3DE9A212-1D49-40B6-B10A-F564D981DBDE}  2014-02-08 10:01:36.827000000  \n",
       "503  {AE638923-2B67-4679-B90E-ABBAB17DCA31}  2014-02-08 10:01:36.827000000  \n",
       "\n",
       "[3 rows x 25 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products.tail(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Use `shape` to identify the \"shape\" of the data (rows by columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(504, 25)"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here we have 504 rows, and 25 columns. This is a tuple, so we can extract the parts using *list indexing*:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "# Rows is 504\n",
      "# Columns is 25\n"
     ]
    }
   ],
   "source": [
    "print('# Rows is', products.shape[0])\n",
    "print('# Columns is', products.shape[1])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Column Headers and Datatypes\n",
    "\n",
    "Here's how to get the columns of the table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['ProductID', 'Name', 'ProductNumber', 'MakeFlag', 'FinishedGoodsFlag',\n",
       "       'Color', 'SafetyStockLevel', 'ReorderPoint', 'StandardCost',\n",
       "       'ListPrice', 'Size', 'SizeUnitMeasureCode', 'WeightUnitMeasureCode',\n",
       "       'Weight', 'DaysToManufacture', 'ProductLine', 'Class', 'Style',\n",
       "       'ProductSubcategoryID', 'ProductModelID', 'SellStartDate',\n",
       "       'SellEndDate', 'DiscontinuedDate', 'rowguid', 'ModifiedDate'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's examine the datatypes of the columns.\n",
    "\n",
    "Note:\n",
    "\n",
    "* These datatypes were automatically inferred by pandas upon import!\n",
    "* Strings are considered `object`s by Pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ProductID                  int64\n",
       "Name                      object\n",
       "ProductNumber             object\n",
       "MakeFlag                   int64\n",
       "FinishedGoodsFlag          int64\n",
       "Color                     object\n",
       "SafetyStockLevel           int64\n",
       "ReorderPoint               int64\n",
       "StandardCost             float64\n",
       "ListPrice                float64\n",
       "Size                      object\n",
       "SizeUnitMeasureCode       object\n",
       "WeightUnitMeasureCode     object\n",
       "Weight                   float64\n",
       "DaysToManufacture          int64\n",
       "ProductLine               object\n",
       "Class                     object\n",
       "Style                     object\n",
       "ProductSubcategoryID     float64\n",
       "ProductModelID           float64\n",
       "SellStartDate             object\n",
       "SellEndDate               object\n",
       "DiscontinuedDate         float64\n",
       "rowguid                   object\n",
       "ModifiedDate              object\n",
       "dtype: object"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's convert `products.dtypes` into a DataFrame for easier viewing:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>DataTypes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>ProductID</td>\n",
       "      <td>int64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Name</td>\n",
       "      <td>object</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>ProductNumber</td>\n",
       "      <td>object</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>MakeFlag</td>\n",
       "      <td>int64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>FinishedGoodsFlag</td>\n",
       "      <td>int64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Color</td>\n",
       "      <td>object</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>SafetyStockLevel</td>\n",
       "      <td>int64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>ReorderPoint</td>\n",
       "      <td>int64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>StandardCost</td>\n",
       "      <td>float64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>ListPrice</td>\n",
       "      <td>float64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Size</td>\n",
       "      <td>object</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>SizeUnitMeasureCode</td>\n",
       "      <td>object</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>WeightUnitMeasureCode</td>\n",
       "      <td>object</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Weight</td>\n",
       "      <td>float64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>DaysToManufacture</td>\n",
       "      <td>int64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>ProductLine</td>\n",
       "      <td>object</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Class</td>\n",
       "      <td>object</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Style</td>\n",
       "      <td>object</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>ProductSubcategoryID</td>\n",
       "      <td>float64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>ProductModelID</td>\n",
       "      <td>float64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>SellStartDate</td>\n",
       "      <td>object</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>SellEndDate</td>\n",
       "      <td>object</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>DiscontinuedDate</td>\n",
       "      <td>float64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>rowguid</td>\n",
       "      <td>object</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>ModifiedDate</td>\n",
       "      <td>object</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      DataTypes\n",
       "ProductID                 int64\n",
       "Name                     object\n",
       "ProductNumber            object\n",
       "MakeFlag                  int64\n",
       "FinishedGoodsFlag         int64\n",
       "Color                    object\n",
       "SafetyStockLevel          int64\n",
       "ReorderPoint              int64\n",
       "StandardCost            float64\n",
       "ListPrice               float64\n",
       "Size                     object\n",
       "SizeUnitMeasureCode      object\n",
       "WeightUnitMeasureCode    object\n",
       "Weight                  float64\n",
       "DaysToManufacture         int64\n",
       "ProductLine              object\n",
       "Class                    object\n",
       "Style                    object\n",
       "ProductSubcategoryID    float64\n",
       "ProductModelID          float64\n",
       "SellStartDate            object\n",
       "SellEndDate              object\n",
       "DiscontinuedDate        float64\n",
       "rowguid                  object\n",
       "ModifiedDate             object"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame(products.dtypes, columns=['DataTypes'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Why do datatypes matter? What operations could we perform on some datatypes that we could not on others? Note the importance of this in checking dataset integrity."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "# Selecting Column(s)\n",
    "\n",
    "We can select columns in two ways. Either we treat the column as a property of the DataFrame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0            Adjustable Race\n",
       "1               Bearing Ball\n",
       "2            BB Ball Bearing\n",
       "3      Headset Ball Bearings\n",
       "4                      Blade\n",
       "               ...          \n",
       "499        ML Bottom Bracket\n",
       "500        HL Bottom Bracket\n",
       "501       Road-750 Black, 44\n",
       "502       Road-750 Black, 48\n",
       "503       Road-750 Black, 52\n",
       "Name: Name, Length: 504, dtype: object"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products.Name"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Or we index the DataFrame on the column name:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0            Adjustable Race\n",
       "1               Bearing Ball\n",
       "2            BB Ball Bearing\n",
       "3      Headset Ball Bearings\n",
       "4                      Blade\n",
       "               ...          \n",
       "499        ML Bottom Bracket\n",
       "500        HL Bottom Bracket\n",
       "501       Road-750 Black, 44\n",
       "502       Road-750 Black, 48\n",
       "503       Road-750 Black, 52\n",
       "Name: Name, Length: 504, dtype: object"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products['Name']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What happens if we use this funny syntax?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Adjustable Race</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Bearing Ball</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>BB Ball Bearing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Headset Ball Bearings</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Blade</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>499</td>\n",
       "      <td>ML Bottom Bracket</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>500</td>\n",
       "      <td>HL Bottom Bracket</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>501</td>\n",
       "      <td>Road-750 Black, 44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>502</td>\n",
       "      <td>Road-750 Black, 48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>503</td>\n",
       "      <td>Road-750 Black, 52</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>504 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                      Name\n",
       "0          Adjustable Race\n",
       "1             Bearing Ball\n",
       "2          BB Ball Bearing\n",
       "3    Headset Ball Bearings\n",
       "4                    Blade\n",
       "..                     ...\n",
       "499      ML Bottom Bracket\n",
       "500      HL Bottom Bracket\n",
       "501     Road-750 Black, 44\n",
       "502     Road-750 Black, 48\n",
       "503     Road-750 Black, 52\n",
       "\n",
       "[504 rows x 1 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products[['Name']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "If I use SINGLE brackets, pandas returns a <class 'pandas.core.series.Series'> \n",
      "If I use DOUBLE brackets, pandas returns a <class 'pandas.core.frame.DataFrame'>\n"
     ]
    }
   ],
   "source": [
    "print('If I use SINGLE brackets, pandas returns a', \n",
    "      type(products['Name']),\n",
    "      '\\nIf I use DOUBLE brackets, pandas returns a',\n",
    "      type(products[['Name']]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Again, if we select the `Name` column only, it is returned as a `Series` object:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    Adjustable Race\n",
       "1       Bearing Ball\n",
       "2    BB Ball Bearing\n",
       "Name: Name, dtype: object"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products['Name'].head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The double brackets really just means a list of columns!\n",
    "\n",
    "And if we select by a list of columns, it is returned as a DataFrame object:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Adjustable Race</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Bearing Ball</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>BB Ball Bearing</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              Name\n",
       "0  Adjustable Race\n",
       "1     Bearing Ball\n",
       "2  BB Ball Bearing"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products[['Name']].head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we understand that we're just selecting columns using a list, we can select multiple columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>Name</th>\n",
       "      <th>ProductNumber</th>\n",
       "      <th>MakeFlag</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Adjustable Race</td>\n",
       "      <td>AR-5381</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Bearing Ball</td>\n",
       "      <td>BA-8327</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>BB Ball Bearing</td>\n",
       "      <td>BE-2349</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              Name ProductNumber  MakeFlag\n",
       "0  Adjustable Race       AR-5381         0\n",
       "1     Bearing Ball       BA-8327         0\n",
       "2  BB Ball Bearing       BE-2349         1"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products[['Name', 'ProductNumber', 'MakeFlag']].head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that we can switch the order of `head` and the selecting of the columns, it's the exact same result, just the meaning is a bit different. In the next example, first we select the first three rows, and then we select the named columns out of those 3 rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>Name</th>\n",
       "      <th>ProductNumber</th>\n",
       "      <th>MakeFlag</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Adjustable Race</td>\n",
       "      <td>AR-5381</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Bearing Ball</td>\n",
       "      <td>BA-8327</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>BB Ball Bearing</td>\n",
       "      <td>BE-2349</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              Name ProductNumber  MakeFlag\n",
       "0  Adjustable Race       AR-5381         0\n",
       "1     Bearing Ball       BA-8327         0\n",
       "2  BB Ball Bearing       BE-2349         1"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products.head(3)[['Name', 'ProductNumber', 'MakeFlag']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Class Question:** What if we wanted to select a column that has a space in it? Which method from the above two would we use? Why?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "# Selecting Row(s)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are two ways to select rows:\n",
    "\n",
    "* `.iloc` is position-based\n",
    "* `.loc` is label-based\n",
    "\n",
    "For now, we can only learn about `.iloc` as `.loc` relies on **Indices**, which we haven't learned about yet. So we'll come back to `.loc` later.\n",
    "\n",
    "To select rows with `.iloc`, supply the `start` and `end` positions, as integers, with the syntax `.iloc[start:end]`. \n",
    "\n",
    "**Note:** `end` will not be included! (Similar to `range` which we learned about before.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>ProductID</th>\n",
       "      <th>Name</th>\n",
       "      <th>ProductNumber</th>\n",
       "      <th>MakeFlag</th>\n",
       "      <th>FinishedGoodsFlag</th>\n",
       "      <th>Color</th>\n",
       "      <th>SafetyStockLevel</th>\n",
       "      <th>ReorderPoint</th>\n",
       "      <th>StandardCost</th>\n",
       "      <th>ListPrice</th>\n",
       "      <th>...</th>\n",
       "      <th>ProductLine</th>\n",
       "      <th>Class</th>\n",
       "      <th>Style</th>\n",
       "      <th>ProductSubcategoryID</th>\n",
       "      <th>ProductModelID</th>\n",
       "      <th>SellStartDate</th>\n",
       "      <th>SellEndDate</th>\n",
       "      <th>DiscontinuedDate</th>\n",
       "      <th>rowguid</th>\n",
       "      <th>ModifiedDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>317</td>\n",
       "      <td>LL Crankarm</td>\n",
       "      <td>CA-5965</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Black</td>\n",
       "      <td>500</td>\n",
       "      <td>375</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>L</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2008-04-30 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{3C9D10B7-A6B2-4774-9963-C19DCEE72FEA}</td>\n",
       "      <td>2014-02-08 10:01:36.827000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>318</td>\n",
       "      <td>ML Crankarm</td>\n",
       "      <td>CA-6738</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Black</td>\n",
       "      <td>500</td>\n",
       "      <td>375</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2008-04-30 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{EABB9A92-FA07-4EAB-8955-F0517B4A4CA7}</td>\n",
       "      <td>2014-02-08 10:01:36.827000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>319</td>\n",
       "      <td>HL Crankarm</td>\n",
       "      <td>CA-7457</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Black</td>\n",
       "      <td>500</td>\n",
       "      <td>375</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2008-04-30 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{7D3FD384-4F29-484B-86FA-4206E276FE58}</td>\n",
       "      <td>2014-02-08 10:01:36.827000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>320</td>\n",
       "      <td>Chainring Bolts</td>\n",
       "      <td>CB-2903</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Silver</td>\n",
       "      <td>1000</td>\n",
       "      <td>750</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2008-04-30 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{7BE38E48-B7D6-4486-888E-F53C26735101}</td>\n",
       "      <td>2014-02-08 10:01:36.827000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>321</td>\n",
       "      <td>Chainring Nut</td>\n",
       "      <td>CN-6137</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Silver</td>\n",
       "      <td>1000</td>\n",
       "      <td>750</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2008-04-30 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{3314B1D7-EF69-4431-B6DD-DC75268BD5DF}</td>\n",
       "      <td>2014-02-08 10:01:36.827000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   ProductID             Name ProductNumber  MakeFlag  FinishedGoodsFlag  \\\n",
       "5        317      LL Crankarm       CA-5965         0                  0   \n",
       "6        318      ML Crankarm       CA-6738         0                  0   \n",
       "7        319      HL Crankarm       CA-7457         0                  0   \n",
       "8        320  Chainring Bolts       CB-2903         0                  0   \n",
       "9        321    Chainring Nut       CN-6137         0                  0   \n",
       "\n",
       "    Color  SafetyStockLevel  ReorderPoint  StandardCost  ListPrice  ...  \\\n",
       "5   Black               500           375           0.0        0.0  ...   \n",
       "6   Black               500           375           0.0        0.0  ...   \n",
       "7   Black               500           375           0.0        0.0  ...   \n",
       "8  Silver              1000           750           0.0        0.0  ...   \n",
       "9  Silver              1000           750           0.0        0.0  ...   \n",
       "\n",
       "  ProductLine Class Style  ProductSubcategoryID  ProductModelID  \\\n",
       "5         NaN    L    NaN                   NaN             NaN   \n",
       "6         NaN    M    NaN                   NaN             NaN   \n",
       "7         NaN   NaN   NaN                   NaN             NaN   \n",
       "8         NaN   NaN   NaN                   NaN             NaN   \n",
       "9         NaN   NaN   NaN                   NaN             NaN   \n",
       "\n",
       "         SellStartDate SellEndDate DiscontinuedDate  \\\n",
       "5  2008-04-30 00:00:00         NaN              NaN   \n",
       "6  2008-04-30 00:00:00         NaN              NaN   \n",
       "7  2008-04-30 00:00:00         NaN              NaN   \n",
       "8  2008-04-30 00:00:00         NaN              NaN   \n",
       "9  2008-04-30 00:00:00         NaN              NaN   \n",
       "\n",
       "                                  rowguid                   ModifiedDate  \n",
       "5  {3C9D10B7-A6B2-4774-9963-C19DCEE72FEA}  2014-02-08 10:01:36.827000000  \n",
       "6  {EABB9A92-FA07-4EAB-8955-F0517B4A4CA7}  2014-02-08 10:01:36.827000000  \n",
       "7  {7D3FD384-4F29-484B-86FA-4206E276FE58}  2014-02-08 10:01:36.827000000  \n",
       "8  {7BE38E48-B7D6-4486-888E-F53C26735101}  2014-02-08 10:01:36.827000000  \n",
       "9  {3314B1D7-EF69-4431-B6DD-DC75268BD5DF}  2014-02-08 10:01:36.827000000  \n",
       "\n",
       "[5 rows x 25 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products.iloc[5:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So now combining the two concepts:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "scrolled": false
   },
   "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>Name</th>\n",
       "      <th>ProductNumber</th>\n",
       "      <th>MakeFlag</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>LL Crankarm</td>\n",
       "      <td>CA-5965</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>ML Crankarm</td>\n",
       "      <td>CA-6738</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>HL Crankarm</td>\n",
       "      <td>CA-7457</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>Chainring Bolts</td>\n",
       "      <td>CB-2903</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>Chainring Nut</td>\n",
       "      <td>CN-6137</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              Name ProductNumber  MakeFlag\n",
       "5      LL Crankarm       CA-5965         0\n",
       "6      ML Crankarm       CA-6738         0\n",
       "7      HL Crankarm       CA-7457         0\n",
       "8  Chainring Bolts       CB-2903         0\n",
       "9    Chainring Nut       CN-6137         0"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products[['Name', 'ProductNumber', 'MakeFlag']].iloc[5:10] #clearer"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also select columns at the same time by passing in a second integer-based range! (Although I think the previous syntax, making the column names more explicit, is clearer.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>Name</th>\n",
       "      <th>ProductNumber</th>\n",
       "      <th>MakeFlag</th>\n",
       "      <th>FinishedGoodsFlag</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>LL Crankarm</td>\n",
       "      <td>CA-5965</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>ML Crankarm</td>\n",
       "      <td>CA-6738</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>HL Crankarm</td>\n",
       "      <td>CA-7457</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>Chainring Bolts</td>\n",
       "      <td>CB-2903</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>Chainring Nut</td>\n",
       "      <td>CN-6137</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              Name ProductNumber  MakeFlag  FinishedGoodsFlag\n",
       "5      LL Crankarm       CA-5965         0                  0\n",
       "6      ML Crankarm       CA-6738         0                  0\n",
       "7      HL Crankarm       CA-7457         0                  0\n",
       "8  Chainring Bolts       CB-2903         0                  0\n",
       "9    Chainring Nut       CN-6137         0                  0"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products.iloc[5:10, 1:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can read more about `.iloc` in this [Stack Overflow answer](https://stackoverflow.com/questions/31593201/how-are-iloc-ix-and-loc-different)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "# Renaming Columns\n",
    "\n",
    "Perhaps we want to rename our columns. There are a few options for doing this.\n",
    "\n",
    "## Renaming Specific Columns\n",
    "\n",
    "Here's an example of renaming **specific** columns by using a dictionary. **Note:** `inplace=False` will return a new dataframe, but leave the original dataframe untouched. Change `inplace` to `True` to modify the original dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>ProductID</th>\n",
       "      <th>product_name</th>\n",
       "      <th>product_number</th>\n",
       "      <th>MakeFlag</th>\n",
       "      <th>FinishedGoodsFlag</th>\n",
       "      <th>Color</th>\n",
       "      <th>SafetyStockLevel</th>\n",
       "      <th>ReorderPoint</th>\n",
       "      <th>StandardCost</th>\n",
       "      <th>ListPrice</th>\n",
       "      <th>...</th>\n",
       "      <th>ProductLine</th>\n",
       "      <th>Class</th>\n",
       "      <th>Style</th>\n",
       "      <th>ProductSubcategoryID</th>\n",
       "      <th>ProductModelID</th>\n",
       "      <th>SellStartDate</th>\n",
       "      <th>SellEndDate</th>\n",
       "      <th>DiscontinuedDate</th>\n",
       "      <th>rowguid</th>\n",
       "      <th>ModifiedDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Adjustable Race</td>\n",
       "      <td>AR-5381</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1000</td>\n",
       "      <td>750</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2008-04-30 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{694215B7-08F7-4C0D-ACB1-D734BA44C0C8}</td>\n",
       "      <td>2014-02-08 10:01:36.827000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Bearing Ball</td>\n",
       "      <td>BA-8327</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1000</td>\n",
       "      <td>750</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2008-04-30 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{58AE3C20-4F3A-4749-A7D4-D568806CC537}</td>\n",
       "      <td>2014-02-08 10:01:36.827000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>BB Ball Bearing</td>\n",
       "      <td>BE-2349</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>800</td>\n",
       "      <td>600</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2008-04-30 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{9C21AED2-5BFA-4F18-BCB8-F11638DC2E4E}</td>\n",
       "      <td>2014-02-08 10:01:36.827000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   ProductID     product_name product_number  MakeFlag  FinishedGoodsFlag  \\\n",
       "0          1  Adjustable Race        AR-5381         0                  0   \n",
       "1          2     Bearing Ball        BA-8327         0                  0   \n",
       "2          3  BB Ball Bearing        BE-2349         1                  0   \n",
       "\n",
       "  Color  SafetyStockLevel  ReorderPoint  StandardCost  ListPrice  ...  \\\n",
       "0   NaN              1000           750           0.0        0.0  ...   \n",
       "1   NaN              1000           750           0.0        0.0  ...   \n",
       "2   NaN               800           600           0.0        0.0  ...   \n",
       "\n",
       "  ProductLine Class Style  ProductSubcategoryID  ProductModelID  \\\n",
       "0         NaN   NaN   NaN                   NaN             NaN   \n",
       "1         NaN   NaN   NaN                   NaN             NaN   \n",
       "2         NaN   NaN   NaN                   NaN             NaN   \n",
       "\n",
       "         SellStartDate SellEndDate DiscontinuedDate  \\\n",
       "0  2008-04-30 00:00:00         NaN              NaN   \n",
       "1  2008-04-30 00:00:00         NaN              NaN   \n",
       "2  2008-04-30 00:00:00         NaN              NaN   \n",
       "\n",
       "                                  rowguid                   ModifiedDate  \n",
       "0  {694215B7-08F7-4C0D-ACB1-D734BA44C0C8}  2014-02-08 10:01:36.827000000  \n",
       "1  {58AE3C20-4F3A-4749-A7D4-D568806CC537}  2014-02-08 10:01:36.827000000  \n",
       "2  {9C21AED2-5BFA-4F18-BCB8-F11638DC2E4E}  2014-02-08 10:01:36.827000000  \n",
       "\n",
       "[3 rows x 25 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products.rename(columns={'Name': 'product_name', 'ProductNumber':'product_number'}, inplace=True) #do not change until you know correct\n",
    "products.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Renaming All Coumns\n",
    "\n",
    "You can also rename **ALL** columns with a new list of column names.\n",
    "\n",
    "First let's take a look at the product columns and its type:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "My columns look like: Index(['ProductID', 'product_name', 'product_number', 'MakeFlag',\n",
      "       'FinishedGoodsFlag', 'Color', 'SafetyStockLevel', 'ReorderPoint',\n",
      "       'StandardCost', 'ListPrice', 'Size', 'SizeUnitMeasureCode',\n",
      "       'WeightUnitMeasureCode', 'Weight', 'DaysToManufacture', 'ProductLine',\n",
      "       'Class', 'Style', 'ProductSubcategoryID', 'ProductModelID',\n",
      "       'SellStartDate', 'SellEndDate', 'DiscontinuedDate', 'rowguid',\n",
      "       'ModifiedDate'],\n",
      "      dtype='object')\n",
      "\n",
      "And the type is: <class 'pandas.core.indexes.base.Index'>\n"
     ]
    }
   ],
   "source": [
    "print('My columns look like:', products.columns)\n",
    "print('\\nAnd the type is:', type(products.columns))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that the `pd.DataFrame.columns` property can be converted (i.e. cast) to a `list` type. (Originally, it's a `pd.core.indexes.base.Index` object), by using the built-in `list()` function:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Now my columns look like: ['ProductID', 'product_name', 'product_number', 'MakeFlag', 'FinishedGoodsFlag', 'Color', 'SafetyStockLevel', 'ReorderPoint', 'StandardCost', 'ListPrice', 'Size', 'SizeUnitMeasureCode', 'WeightUnitMeasureCode', 'Weight', 'DaysToManufacture', 'ProductLine', 'Class', 'Style', 'ProductSubcategoryID', 'ProductModelID', 'SellStartDate', 'SellEndDate', 'DiscontinuedDate', 'rowguid', 'ModifiedDate']\n",
      "\n",
      "And the type is: <class 'list'>\n"
     ]
    }
   ],
   "source": [
    "print('Now my columns look like:', list(products.columns))\n",
    "print('\\nAnd the type is:', type(list(products.columns)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can place these columns into a variable, `cols`. These strings will become the new column names:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "cols = list(products.columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can use list indexing to change the columns we want. Here, we'll rename the `ProductName` column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Before:  ['ProductID', 'product_name', 'product_number', 'MakeFlag', 'FinishedGoodsFlag', 'Color', 'SafetyStockLevel', 'ReorderPoint', 'StandardCost', 'ListPrice', 'Size', 'SizeUnitMeasureCode', 'WeightUnitMeasureCode', 'Weight', 'DaysToManufacture', 'ProductLine', 'Class', 'Style', 'ProductSubcategoryID', 'ProductModelID', 'SellStartDate', 'SellEndDate', 'DiscontinuedDate', 'rowguid', 'ModifiedDate']\n",
      "After:  ['ProductID', 'NewProductName', 'product_number', 'MakeFlag', 'FinishedGoodsFlag', 'Color', 'SafetyStockLevel', 'ReorderPoint', 'StandardCost', 'ListPrice', 'Size', 'SizeUnitMeasureCode', 'WeightUnitMeasureCode', 'Weight', 'DaysToManufacture', 'ProductLine', 'Class', 'Style', 'ProductSubcategoryID', 'ProductModelID', 'SellStartDate', 'SellEndDate', 'DiscontinuedDate', 'rowguid', 'ModifiedDate']\n"
     ]
    }
   ],
   "source": [
    "print('Before: ', cols)\n",
    "\n",
    "cols[1] = 'NewProductName'\n",
    "\n",
    "print('After: ', cols)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['ProductID', 'NewProductName', 'product_number', 'MakeFlag',\n",
      "       'FinishedGoodsFlag', 'Color', 'SafetyStockLevel', 'ReorderPoint',\n",
      "       'StandardCost', 'ListPrice', 'Size', 'SizeUnitMeasureCode',\n",
      "       'WeightUnitMeasureCode', 'Weight', 'DaysToManufacture', 'ProductLine',\n",
      "       'Class', 'Style', 'ProductSubcategoryID', 'ProductModelID',\n",
      "       'SellStartDate', 'SellEndDate', 'DiscontinuedDate', 'rowguid',\n",
      "       'ModifiedDate'],\n",
      "      dtype='object')\n"
     ]
    }
   ],
   "source": [
    "products.columns = cols\n",
    "\n",
    "print(products.columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that our first column name has changed from `ProductName` to `NewProductName`.\n",
    "\n",
    "Finally, we can set the `pd.DataFrame.columns` instance variable, to the new `cols` list, overwriting the existing columns header names:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['ProductID', 'NewProductName', 'product_number', 'MakeFlag',\n",
      "       'FinishedGoodsFlag', 'Color', 'SafetyStockLevel', 'ReorderPoint',\n",
      "       'StandardCost', 'ListPrice', 'Size', 'SizeUnitMeasureCode',\n",
      "       'WeightUnitMeasureCode', 'Weight', 'DaysToManufacture', 'ProductLine',\n",
      "       'Class', 'Style', 'ProductSubcategoryID', 'ProductModelID',\n",
      "       'SellStartDate', 'SellEndDate', 'DiscontinuedDate', 'rowguid',\n",
      "       'ModifiedDate'],\n",
      "      dtype='object')\n"
     ]
    }
   ],
   "source": [
    "products.columns = cols\n",
    "print(products.columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can display the final result of the changing the `ProductName` column to `NewName`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "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>ProductID</th>\n",
       "      <th>NewProductName</th>\n",
       "      <th>product_number</th>\n",
       "      <th>MakeFlag</th>\n",
       "      <th>FinishedGoodsFlag</th>\n",
       "      <th>Color</th>\n",
       "      <th>SafetyStockLevel</th>\n",
       "      <th>ReorderPoint</th>\n",
       "      <th>StandardCost</th>\n",
       "      <th>ListPrice</th>\n",
       "      <th>...</th>\n",
       "      <th>ProductLine</th>\n",
       "      <th>Class</th>\n",
       "      <th>Style</th>\n",
       "      <th>ProductSubcategoryID</th>\n",
       "      <th>ProductModelID</th>\n",
       "      <th>SellStartDate</th>\n",
       "      <th>SellEndDate</th>\n",
       "      <th>DiscontinuedDate</th>\n",
       "      <th>rowguid</th>\n",
       "      <th>ModifiedDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Adjustable Race</td>\n",
       "      <td>AR-5381</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1000</td>\n",
       "      <td>750</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2008-04-30 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{694215B7-08F7-4C0D-ACB1-D734BA44C0C8}</td>\n",
       "      <td>2014-02-08 10:01:36.827000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Bearing Ball</td>\n",
       "      <td>BA-8327</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1000</td>\n",
       "      <td>750</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2008-04-30 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{58AE3C20-4F3A-4749-A7D4-D568806CC537}</td>\n",
       "      <td>2014-02-08 10:01:36.827000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>BB Ball Bearing</td>\n",
       "      <td>BE-2349</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>800</td>\n",
       "      <td>600</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2008-04-30 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{9C21AED2-5BFA-4F18-BCB8-F11638DC2E4E}</td>\n",
       "      <td>2014-02-08 10:01:36.827000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   ProductID   NewProductName product_number  MakeFlag  FinishedGoodsFlag  \\\n",
       "0          1  Adjustable Race        AR-5381         0                  0   \n",
       "1          2     Bearing Ball        BA-8327         0                  0   \n",
       "2          3  BB Ball Bearing        BE-2349         1                  0   \n",
       "\n",
       "  Color  SafetyStockLevel  ReorderPoint  StandardCost  ListPrice  ...  \\\n",
       "0   NaN              1000           750           0.0        0.0  ...   \n",
       "1   NaN              1000           750           0.0        0.0  ...   \n",
       "2   NaN               800           600           0.0        0.0  ...   \n",
       "\n",
       "  ProductLine Class Style  ProductSubcategoryID  ProductModelID  \\\n",
       "0         NaN   NaN   NaN                   NaN             NaN   \n",
       "1         NaN   NaN   NaN                   NaN             NaN   \n",
       "2         NaN   NaN   NaN                   NaN             NaN   \n",
       "\n",
       "         SellStartDate SellEndDate DiscontinuedDate  \\\n",
       "0  2008-04-30 00:00:00         NaN              NaN   \n",
       "1  2008-04-30 00:00:00         NaN              NaN   \n",
       "2  2008-04-30 00:00:00         NaN              NaN   \n",
       "\n",
       "                                  rowguid                   ModifiedDate  \n",
       "0  {694215B7-08F7-4C0D-ACB1-D734BA44C0C8}  2014-02-08 10:01:36.827000000  \n",
       "1  {58AE3C20-4F3A-4749-A7D4-D568806CC537}  2014-02-08 10:01:36.827000000  \n",
       "2  {9C21AED2-5BFA-4F18-BCB8-F11638DC2E4E}  2014-02-08 10:01:36.827000000  \n",
       "\n",
       "[3 rows x 25 columns]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "# Common Column Operations\n",
    "\n",
    "While this is non-comprehensive, these are a few key column-specific data checks.\n",
    "\n",
    "## Descriptive Statistics:\n",
    "\n",
    "The mean, standard deviation, minimum, first quartile, median, third quartile, and maximum of a bunch of numbers.\n",
    "\n",
    "* **Mean:** The average of the column\n",
    "* **[Standard Deviation](https://en.wikipedia.org/wiki/Standard_deviation):** From statistics -- measures the variation around the mean.\n",
    "* **Min:** The smallest value in the column\n",
    "* **Max:** The largest value in the column\n",
    "* **Quartile:** A quartile is one fourth of our data\n",
    "   * **First quartile:** This is the bottom most 25 percent\n",
    "   * **Median:** The middle value. (Line all values biggest to smallest - median is the middle!) Also the 50th percentile\n",
    "   * **Third quartile:** This the the top 75 percentile of our data\n",
    "   \n",
    "![](assets/quartiles.svg)\n",
    "\n",
    "In Pandas, we can get these statistics with `.describe()` ([docs](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.describe.html)).\n",
    "\n",
    "**Note:** `.describe()` by default operates on numeric datatypes, but it can also operate on strings and timestamps.\n",
    "\n",
    "Here, we use `.describe()` on the `MakeFlag`, `SafetyStockLevel`, and `StandardCost` fields."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "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>SafetyStockLevel</th>\n",
       "      <th>StandardCost</th>\n",
       "      <th>ListPrice</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>count</td>\n",
       "      <td>504.000000</td>\n",
       "      <td>504.000000</td>\n",
       "      <td>504.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>mean</td>\n",
       "      <td>535.150794</td>\n",
       "      <td>258.602961</td>\n",
       "      <td>438.666250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>std</td>\n",
       "      <td>374.112954</td>\n",
       "      <td>461.632808</td>\n",
       "      <td>773.602843</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>min</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>25%</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>50%</td>\n",
       "      <td>500.000000</td>\n",
       "      <td>23.372200</td>\n",
       "      <td>49.990000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>75%</td>\n",
       "      <td>1000.000000</td>\n",
       "      <td>317.075825</td>\n",
       "      <td>564.990000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>max</td>\n",
       "      <td>1000.000000</td>\n",
       "      <td>2171.294200</td>\n",
       "      <td>3578.270000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       SafetyStockLevel  StandardCost    ListPrice\n",
       "count        504.000000    504.000000   504.000000\n",
       "mean         535.150794    258.602961   438.666250\n",
       "std          374.112954    461.632808   773.602843\n",
       "min            4.000000      0.000000     0.000000\n",
       "25%          100.000000      0.000000     0.000000\n",
       "50%          500.000000     23.372200    49.990000\n",
       "75%         1000.000000    317.075825   564.990000\n",
       "max         1000.000000   2171.294200  3578.270000"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products[['SafetyStockLevel', 'StandardCost', 'ListPrice']].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Value Counts\n",
    "\n",
    "`pd.Series.value_counts()` count the occurrence of each value within a Series.\n",
    "\n",
    "The following shows the most popular product colors (aggregated by count, descending by default):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Black           93\n",
       "Silver          43\n",
       "Red             38\n",
       "Yellow          36\n",
       "Blue            26\n",
       "Multi            8\n",
       "Silver/Black     7\n",
       "White            4\n",
       "Grey             1\n",
       "Name: Color, dtype: int64"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products['Color'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Unique values\n",
    "\n",
    "`pd.Series.unique()` can determine the number of distinct values within a given Series.\n",
    "\n",
    "What are the unique colors for the products?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([nan, 'Black', 'Silver', 'Red', 'White', 'Blue', 'Multi', 'Yellow',\n",
       "       'Grey', 'Silver/Black'], dtype=object)"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products['Color'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How many distinct colors are there? (With `nunique()`)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "9"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products['Color'].nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that `nunique()` doesn't count nulls by default. We can also include nulls with the parameter `dropna=False`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "10"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products['Color'].nunique(dropna=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "# Filtering\n",
    "\n",
    "## Filtering on a Single Condition\n",
    "\n",
    "Filtering and sorting are key processes that allow us to drill into the 'nitty gritty' and cross sections of our dataset.\n",
    "\n",
    "To filter, we use a process called **Boolean Filtering**, wherein we define a Boolean condition, and use that Boolean condition to filer on our DataFrame.\n",
    "\n",
    "Recall: our given dataset has a column `Color`. Let's see if we can find all products that are `Black`. Let's take a look at the first 10 rows by Color to see how it looks as-is:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0       NaN\n",
       "1       NaN\n",
       "2       NaN\n",
       "3       NaN\n",
       "4       NaN\n",
       "5     Black\n",
       "6     Black\n",
       "7     Black\n",
       "8    Silver\n",
       "9    Silver\n",
       "Name: Color, dtype: object"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products['Color'].head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By applying a **boolean mask** to this dataframe, `== 'Black'`, we can get the following **filter**:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    False\n",
       "1    False\n",
       "2    False\n",
       "3    False\n",
       "4    False\n",
       "5     True\n",
       "6     True\n",
       "7     True\n",
       "8    False\n",
       "9    False\n",
       "Name: Color, dtype: bool"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products['Color'].head(10) == 'Black' #a filter"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can apply the **filter** to our full dataframe. We want the rows where the filter is `True`, and we do'nt want the rows where the filter is `False`.\n",
    "\n",
    "The result is a dataframe that only has rows where `Color` is `Black`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "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>ProductID</th>\n",
       "      <th>NewProductName</th>\n",
       "      <th>product_number</th>\n",
       "      <th>MakeFlag</th>\n",
       "      <th>FinishedGoodsFlag</th>\n",
       "      <th>Color</th>\n",
       "      <th>SafetyStockLevel</th>\n",
       "      <th>ReorderPoint</th>\n",
       "      <th>StandardCost</th>\n",
       "      <th>ListPrice</th>\n",
       "      <th>...</th>\n",
       "      <th>ProductLine</th>\n",
       "      <th>Class</th>\n",
       "      <th>Style</th>\n",
       "      <th>ProductSubcategoryID</th>\n",
       "      <th>ProductModelID</th>\n",
       "      <th>SellStartDate</th>\n",
       "      <th>SellEndDate</th>\n",
       "      <th>DiscontinuedDate</th>\n",
       "      <th>rowguid</th>\n",
       "      <th>ModifiedDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>317</td>\n",
       "      <td>LL Crankarm</td>\n",
       "      <td>CA-5965</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Black</td>\n",
       "      <td>500</td>\n",
       "      <td>375</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>L</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2008-04-30 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{3C9D10B7-A6B2-4774-9963-C19DCEE72FEA}</td>\n",
       "      <td>2014-02-08 10:01:36.827000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>318</td>\n",
       "      <td>ML Crankarm</td>\n",
       "      <td>CA-6738</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Black</td>\n",
       "      <td>500</td>\n",
       "      <td>375</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2008-04-30 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{EABB9A92-FA07-4EAB-8955-F0517B4A4CA7}</td>\n",
       "      <td>2014-02-08 10:01:36.827000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>319</td>\n",
       "      <td>HL Crankarm</td>\n",
       "      <td>CA-7457</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Black</td>\n",
       "      <td>500</td>\n",
       "      <td>375</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2008-04-30 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{7D3FD384-4F29-484B-86FA-4206E276FE58}</td>\n",
       "      <td>2014-02-08 10:01:36.827000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>322</td>\n",
       "      <td>Chainring</td>\n",
       "      <td>CR-7833</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Black</td>\n",
       "      <td>1000</td>\n",
       "      <td>750</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2008-04-30 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{F0AC2C4D-1A1F-4E3C-B4D9-68AEA0EC1CE4}</td>\n",
       "      <td>2014-02-08 10:01:36.827000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>209</td>\n",
       "      <td>680</td>\n",
       "      <td>HL Road Frame - Black, 58</td>\n",
       "      <td>FR-R92B-58</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Black</td>\n",
       "      <td>500</td>\n",
       "      <td>375</td>\n",
       "      <td>1059.31</td>\n",
       "      <td>1431.5</td>\n",
       "      <td>...</td>\n",
       "      <td>R</td>\n",
       "      <td>H</td>\n",
       "      <td>U</td>\n",
       "      <td>14.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>2008-04-30 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{43DD68D6-14A4-461F-9069-55309D90EA7E}</td>\n",
       "      <td>2014-02-08 10:01:36.827000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     ProductID             NewProductName product_number  MakeFlag  \\\n",
       "5          317                LL Crankarm        CA-5965         0   \n",
       "6          318                ML Crankarm        CA-6738         0   \n",
       "7          319                HL Crankarm        CA-7457         0   \n",
       "10         322                  Chainring        CR-7833         0   \n",
       "209        680  HL Road Frame - Black, 58     FR-R92B-58         1   \n",
       "\n",
       "     FinishedGoodsFlag  Color  SafetyStockLevel  ReorderPoint  StandardCost  \\\n",
       "5                    0  Black               500           375          0.00   \n",
       "6                    0  Black               500           375          0.00   \n",
       "7                    0  Black               500           375          0.00   \n",
       "10                   0  Black              1000           750          0.00   \n",
       "209                  1  Black               500           375       1059.31   \n",
       "\n",
       "     ListPrice  ... ProductLine Class Style  ProductSubcategoryID  \\\n",
       "5          0.0  ...         NaN    L    NaN                   NaN   \n",
       "6          0.0  ...         NaN    M    NaN                   NaN   \n",
       "7          0.0  ...         NaN   NaN   NaN                   NaN   \n",
       "10         0.0  ...         NaN   NaN   NaN                   NaN   \n",
       "209     1431.5  ...          R     H     U                   14.0   \n",
       "\n",
       "     ProductModelID        SellStartDate SellEndDate DiscontinuedDate  \\\n",
       "5               NaN  2008-04-30 00:00:00         NaN              NaN   \n",
       "6               NaN  2008-04-30 00:00:00         NaN              NaN   \n",
       "7               NaN  2008-04-30 00:00:00         NaN              NaN   \n",
       "10              NaN  2008-04-30 00:00:00         NaN              NaN   \n",
       "209             6.0  2008-04-30 00:00:00         NaN              NaN   \n",
       "\n",
       "                                    rowguid                   ModifiedDate  \n",
       "5    {3C9D10B7-A6B2-4774-9963-C19DCEE72FEA}  2014-02-08 10:01:36.827000000  \n",
       "6    {EABB9A92-FA07-4EAB-8955-F0517B4A4CA7}  2014-02-08 10:01:36.827000000  \n",
       "7    {7D3FD384-4F29-484B-86FA-4206E276FE58}  2014-02-08 10:01:36.827000000  \n",
       "10   {F0AC2C4D-1A1F-4E3C-B4D9-68AEA0EC1CE4}  2014-02-08 10:01:36.827000000  \n",
       "209  {43DD68D6-14A4-461F-9069-55309D90EA7E}  2014-02-08 10:01:36.827000000  \n",
       "\n",
       "[5 rows x 25 columns]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products[  products['Color'] == 'Black' ].head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you want, you can make a copy of the black products with the `copy()` method. Then you'll have an independent Data Frame for just black products."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(93, 25)"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "black_products = products[products['Color'] == 'Black'].copy()\n",
    "black_products.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Filtering on a Single Condition -- Example 2\n",
    "\n",
    "OK, let's try something else. Let's calculate the **average ListPrice** for **salable products**.\n",
    "\n",
    "Think: What are the component parts of this problem?\n",
    "\n",
    "* First, we need to get salable items. Use your data dictionary from the beginning of this lesson... salable products are \"Finished\"!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "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>ProductID</th>\n",
       "      <th>NewProductName</th>\n",
       "      <th>product_number</th>\n",
       "      <th>MakeFlag</th>\n",
       "      <th>FinishedGoodsFlag</th>\n",
       "      <th>Color</th>\n",
       "      <th>SafetyStockLevel</th>\n",
       "      <th>ReorderPoint</th>\n",
       "      <th>StandardCost</th>\n",
       "      <th>ListPrice</th>\n",
       "      <th>...</th>\n",
       "      <th>ProductLine</th>\n",
       "      <th>Class</th>\n",
       "      <th>Style</th>\n",
       "      <th>ProductSubcategoryID</th>\n",
       "      <th>ProductModelID</th>\n",
       "      <th>SellStartDate</th>\n",
       "      <th>SellEndDate</th>\n",
       "      <th>DiscontinuedDate</th>\n",
       "      <th>rowguid</th>\n",
       "      <th>ModifiedDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>209</td>\n",
       "      <td>680</td>\n",
       "      <td>HL Road Frame - Black, 58</td>\n",
       "      <td>FR-R92B-58</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Black</td>\n",
       "      <td>500</td>\n",
       "      <td>375</td>\n",
       "      <td>1059.3100</td>\n",
       "      <td>1431.50</td>\n",
       "      <td>...</td>\n",
       "      <td>R</td>\n",
       "      <td>H</td>\n",
       "      <td>U</td>\n",
       "      <td>14.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>2008-04-30 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{43DD68D6-14A4-461F-9069-55309D90EA7E}</td>\n",
       "      <td>2014-02-08 10:01:36.827000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>210</td>\n",
       "      <td>706</td>\n",
       "      <td>HL Road Frame - Red, 58</td>\n",
       "      <td>FR-R92R-58</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Red</td>\n",
       "      <td>500</td>\n",
       "      <td>375</td>\n",
       "      <td>1059.3100</td>\n",
       "      <td>1431.50</td>\n",
       "      <td>...</td>\n",
       "      <td>R</td>\n",
       "      <td>H</td>\n",
       "      <td>U</td>\n",
       "      <td>14.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>2008-04-30 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{9540FF17-2712-4C90-A3D1-8CE5568B2462}</td>\n",
       "      <td>2014-02-08 10:01:36.827000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>211</td>\n",
       "      <td>707</td>\n",
       "      <td>Sport-100 Helmet, Red</td>\n",
       "      <td>HL-U509-R</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Red</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>13.0863</td>\n",
       "      <td>34.99</td>\n",
       "      <td>...</td>\n",
       "      <td>S</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>2011-05-31 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{2E1EF41A-C08A-4FF6-8ADA-BDE58B64A712}</td>\n",
       "      <td>2014-02-08 10:01:36.827000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     ProductID             NewProductName product_number  MakeFlag  \\\n",
       "209        680  HL Road Frame - Black, 58     FR-R92B-58         1   \n",
       "210        706    HL Road Frame - Red, 58     FR-R92R-58         1   \n",
       "211        707      Sport-100 Helmet, Red      HL-U509-R         0   \n",
       "\n",
       "     FinishedGoodsFlag  Color  SafetyStockLevel  ReorderPoint  StandardCost  \\\n",
       "209                  1  Black               500           375     1059.3100   \n",
       "210                  1    Red               500           375     1059.3100   \n",
       "211                  1    Red                 4             3       13.0863   \n",
       "\n",
       "     ListPrice  ... ProductLine Class Style  ProductSubcategoryID  \\\n",
       "209    1431.50  ...          R     H     U                   14.0   \n",
       "210    1431.50  ...          R     H     U                   14.0   \n",
       "211      34.99  ...          S    NaN   NaN                  31.0   \n",
       "\n",
       "     ProductModelID        SellStartDate SellEndDate DiscontinuedDate  \\\n",
       "209             6.0  2008-04-30 00:00:00         NaN              NaN   \n",
       "210             6.0  2008-04-30 00:00:00         NaN              NaN   \n",
       "211            33.0  2011-05-31 00:00:00         NaN              NaN   \n",
       "\n",
       "                                    rowguid                   ModifiedDate  \n",
       "209  {43DD68D6-14A4-461F-9069-55309D90EA7E}  2014-02-08 10:01:36.827000000  \n",
       "210  {9540FF17-2712-4C90-A3D1-8CE5568B2462}  2014-02-08 10:01:36.827000000  \n",
       "211  {2E1EF41A-C08A-4FF6-8ADA-BDE58B64A712}  2014-02-08 10:01:36.827000000  \n",
       "\n",
       "[3 rows x 25 columns]"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products[products['FinishedGoodsFlag'] == 1].head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Now, we need to find average list price of those above items. Let's just get the 'ListPrice' column for starters."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "209    1431.50\n",
       "210    1431.50\n",
       "211      34.99\n",
       "Name: ListPrice, dtype: float64"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products[products['FinishedGoodsFlag'] == 1]['ListPrice'].head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To get the average of that column, just take `.mean()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "744.595220338982"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products[products['FinishedGoodsFlag'] == 1]['ListPrice'].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can take a shortcut and just use `.describe()` here:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count     295.000000\n",
       "mean      744.595220\n",
       "std       892.563172\n",
       "min         2.290000\n",
       "25%        66.745000\n",
       "50%       337.220000\n",
       "75%      1100.240000\n",
       "max      3578.270000\n",
       "Name: ListPrice, dtype: float64"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products[products['FinishedGoodsFlag'] == 1]['ListPrice'].describe()\n",
    "#list_prices = products[products['FinishedGoodsFlag'] == 1]['ListPrice']\n",
    "\n",
    "#list_prices.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Sneak peek**: Another handy trick is to use `.hist()` to get a distribution of a continuous variable - in this case, `ListPrice`. We'll cover this more in future lessons:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count     295.000000\n",
       "mean      744.595220\n",
       "std       892.563172\n",
       "min         2.290000\n",
       "25%        66.745000\n",
       "50%       337.220000\n",
       "75%      1100.240000\n",
       "max      3578.270000\n",
       "Name: ListPrice, dtype: float64"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Filtering on Multiple Conditions\n",
    "\n",
    "Here, we will filter on _multiple conditions_. Before, we filtered on rows where `Color` was `Black`. We also filtered where `FinishedGoodsFlag` was equal to `1`.\n",
    "\n",
    "Let's see what happens when we filter on *both* simultaneously. \n",
    "\n",
    "The syntax for multiple conditions is:\n",
    "\n",
    "`df[ (df['col1'] == value1) & (df['col2'] == value2) ]`\n",
    "\n",
    "Or, more simply:\n",
    "\n",
    "`df[ (CONDITION 1) & (CONDITION 2) ]`\n",
    "\n",
    "Which eventually may, on a row-by-row basis, evaluate to something like:\n",
    "\n",
    "`df[ True & False ]`\n",
    "\n",
    "If the end result is `True`, the row is included. If the end result is `False`, the row is omitted.\n",
    "\n",
    "_Don't forget parentheses in your conditions!_ This is a common mistake.\n",
    "\n",
    "Let's look at a table where `Color` is `Black`, **AND** `FinishedGoodsFlag` is `1`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "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>ProductID</th>\n",
       "      <th>NewProductName</th>\n",
       "      <th>product_number</th>\n",
       "      <th>MakeFlag</th>\n",
       "      <th>FinishedGoodsFlag</th>\n",
       "      <th>Color</th>\n",
       "      <th>SafetyStockLevel</th>\n",
       "      <th>ReorderPoint</th>\n",
       "      <th>StandardCost</th>\n",
       "      <th>ListPrice</th>\n",
       "      <th>...</th>\n",
       "      <th>ProductLine</th>\n",
       "      <th>Class</th>\n",
       "      <th>Style</th>\n",
       "      <th>ProductSubcategoryID</th>\n",
       "      <th>ProductModelID</th>\n",
       "      <th>SellStartDate</th>\n",
       "      <th>SellEndDate</th>\n",
       "      <th>DiscontinuedDate</th>\n",
       "      <th>rowguid</th>\n",
       "      <th>ModifiedDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>209</td>\n",
       "      <td>680</td>\n",
       "      <td>HL Road Frame - Black, 58</td>\n",
       "      <td>FR-R92B-58</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Black</td>\n",
       "      <td>500</td>\n",
       "      <td>375</td>\n",
       "      <td>1059.3100</td>\n",
       "      <td>1431.50</td>\n",
       "      <td>...</td>\n",
       "      <td>R</td>\n",
       "      <td>H</td>\n",
       "      <td>U</td>\n",
       "      <td>14.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>2008-04-30 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{43DD68D6-14A4-461F-9069-55309D90EA7E}</td>\n",
       "      <td>2014-02-08 10:01:36.827000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>212</td>\n",
       "      <td>708</td>\n",
       "      <td>Sport-100 Helmet, Black</td>\n",
       "      <td>HL-U509</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Black</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>13.0863</td>\n",
       "      <td>34.99</td>\n",
       "      <td>...</td>\n",
       "      <td>S</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>2011-05-31 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{A25A44FB-C2DE-4268-958F-110B8D7621E2}</td>\n",
       "      <td>2014-02-08 10:01:36.827000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>226</td>\n",
       "      <td>722</td>\n",
       "      <td>LL Road Frame - Black, 58</td>\n",
       "      <td>FR-R38B-58</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Black</td>\n",
       "      <td>500</td>\n",
       "      <td>375</td>\n",
       "      <td>204.6251</td>\n",
       "      <td>337.22</td>\n",
       "      <td>...</td>\n",
       "      <td>R</td>\n",
       "      <td>L</td>\n",
       "      <td>U</td>\n",
       "      <td>14.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>2011-05-31 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{2140F256-F705-4D67-975D-32DE03265838}</td>\n",
       "      <td>2014-02-08 10:01:36.827000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     ProductID             NewProductName product_number  MakeFlag  \\\n",
       "209        680  HL Road Frame - Black, 58     FR-R92B-58         1   \n",
       "212        708    Sport-100 Helmet, Black        HL-U509         0   \n",
       "226        722  LL Road Frame - Black, 58     FR-R38B-58         1   \n",
       "\n",
       "     FinishedGoodsFlag  Color  SafetyStockLevel  ReorderPoint  StandardCost  \\\n",
       "209                  1  Black               500           375     1059.3100   \n",
       "212                  1  Black                 4             3       13.0863   \n",
       "226                  1  Black               500           375      204.6251   \n",
       "\n",
       "     ListPrice  ... ProductLine Class Style  ProductSubcategoryID  \\\n",
       "209    1431.50  ...          R     H     U                   14.0   \n",
       "212      34.99  ...          S    NaN   NaN                  31.0   \n",
       "226     337.22  ...          R     L     U                   14.0   \n",
       "\n",
       "     ProductModelID        SellStartDate SellEndDate DiscontinuedDate  \\\n",
       "209             6.0  2008-04-30 00:00:00         NaN              NaN   \n",
       "212            33.0  2011-05-31 00:00:00         NaN              NaN   \n",
       "226             9.0  2011-05-31 00:00:00         NaN              NaN   \n",
       "\n",
       "                                    rowguid                   ModifiedDate  \n",
       "209  {43DD68D6-14A4-461F-9069-55309D90EA7E}  2014-02-08 10:01:36.827000000  \n",
       "212  {A25A44FB-C2DE-4268-958F-110B8D7621E2}  2014-02-08 10:01:36.827000000  \n",
       "226  {2140F256-F705-4D67-975D-32DE03265838}  2014-02-08 10:01:36.827000000  \n",
       "\n",
       "[3 rows x 25 columns]"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products[(products['Color'] == 'Black') & (products['FinishedGoodsFlag'] == 1)].head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next we have an example of a `ListPrice` greater than 50, **OR** a product size that is not equal to `XL`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "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>ProductID</th>\n",
       "      <th>NewProductName</th>\n",
       "      <th>product_number</th>\n",
       "      <th>MakeFlag</th>\n",
       "      <th>FinishedGoodsFlag</th>\n",
       "      <th>Color</th>\n",
       "      <th>SafetyStockLevel</th>\n",
       "      <th>ReorderPoint</th>\n",
       "      <th>StandardCost</th>\n",
       "      <th>ListPrice</th>\n",
       "      <th>...</th>\n",
       "      <th>ProductLine</th>\n",
       "      <th>Class</th>\n",
       "      <th>Style</th>\n",
       "      <th>ProductSubcategoryID</th>\n",
       "      <th>ProductModelID</th>\n",
       "      <th>SellStartDate</th>\n",
       "      <th>SellEndDate</th>\n",
       "      <th>DiscontinuedDate</th>\n",
       "      <th>rowguid</th>\n",
       "      <th>ModifiedDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Adjustable Race</td>\n",
       "      <td>AR-5381</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1000</td>\n",
       "      <td>750</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2008-04-30 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{694215B7-08F7-4C0D-ACB1-D734BA44C0C8}</td>\n",
       "      <td>2014-02-08 10:01:36.827000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Bearing Ball</td>\n",
       "      <td>BA-8327</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1000</td>\n",
       "      <td>750</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2008-04-30 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{58AE3C20-4F3A-4749-A7D4-D568806CC537}</td>\n",
       "      <td>2014-02-08 10:01:36.827000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>BB Ball Bearing</td>\n",
       "      <td>BE-2349</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>800</td>\n",
       "      <td>600</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2008-04-30 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{9C21AED2-5BFA-4F18-BCB8-F11638DC2E4E}</td>\n",
       "      <td>2014-02-08 10:01:36.827000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   ProductID   NewProductName product_number  MakeFlag  FinishedGoodsFlag  \\\n",
       "0          1  Adjustable Race        AR-5381         0                  0   \n",
       "1          2     Bearing Ball        BA-8327         0                  0   \n",
       "2          3  BB Ball Bearing        BE-2349         1                  0   \n",
       "\n",
       "  Color  SafetyStockLevel  ReorderPoint  StandardCost  ListPrice  ...  \\\n",
       "0   NaN              1000           750           0.0        0.0  ...   \n",
       "1   NaN              1000           750           0.0        0.0  ...   \n",
       "2   NaN               800           600           0.0        0.0  ...   \n",
       "\n",
       "  ProductLine Class Style  ProductSubcategoryID  ProductModelID  \\\n",
       "0         NaN   NaN   NaN                   NaN             NaN   \n",
       "1         NaN   NaN   NaN                   NaN             NaN   \n",
       "2         NaN   NaN   NaN                   NaN             NaN   \n",
       "\n",
       "         SellStartDate SellEndDate DiscontinuedDate  \\\n",
       "0  2008-04-30 00:00:00         NaN              NaN   \n",
       "1  2008-04-30 00:00:00         NaN              NaN   \n",
       "2  2008-04-30 00:00:00         NaN              NaN   \n",
       "\n",
       "                                  rowguid                   ModifiedDate  \n",
       "0  {694215B7-08F7-4C0D-ACB1-D734BA44C0C8}  2014-02-08 10:01:36.827000000  \n",
       "1  {58AE3C20-4F3A-4749-A7D4-D568806CC537}  2014-02-08 10:01:36.827000000  \n",
       "2  {9C21AED2-5BFA-4F18-BCB8-F11638DC2E4E}  2014-02-08 10:01:36.827000000  \n",
       "\n",
       "[3 rows x 25 columns]"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products[(products['ListPrice'] > 50) | (products['Size'] != 'XL')].head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "# Sorting\n",
    "\n",
    "We can sort our DataFrame as well.\n",
    "\n",
    "Let's sort by `StandardCost` on a descending basis:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "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>ProductID</th>\n",
       "      <th>NewProductName</th>\n",
       "      <th>product_number</th>\n",
       "      <th>MakeFlag</th>\n",
       "      <th>FinishedGoodsFlag</th>\n",
       "      <th>Color</th>\n",
       "      <th>SafetyStockLevel</th>\n",
       "      <th>ReorderPoint</th>\n",
       "      <th>StandardCost</th>\n",
       "      <th>ListPrice</th>\n",
       "      <th>...</th>\n",
       "      <th>ProductLine</th>\n",
       "      <th>Class</th>\n",
       "      <th>Style</th>\n",
       "      <th>ProductSubcategoryID</th>\n",
       "      <th>ProductModelID</th>\n",
       "      <th>SellStartDate</th>\n",
       "      <th>SellEndDate</th>\n",
       "      <th>DiscontinuedDate</th>\n",
       "      <th>rowguid</th>\n",
       "      <th>ModifiedDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>253</td>\n",
       "      <td>749</td>\n",
       "      <td>Road-150 Red, 62</td>\n",
       "      <td>BK-R93R-62</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Red</td>\n",
       "      <td>100</td>\n",
       "      <td>75</td>\n",
       "      <td>2171.2942</td>\n",
       "      <td>3578.27</td>\n",
       "      <td>...</td>\n",
       "      <td>R</td>\n",
       "      <td>H</td>\n",
       "      <td>U</td>\n",
       "      <td>2.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>2011-05-31 00:00:00</td>\n",
       "      <td>2012-05-29 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{BC621E1F-2553-4FDC-B22E-5E44A9003569}</td>\n",
       "      <td>2014-02-08 10:01:36.827000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>254</td>\n",
       "      <td>750</td>\n",
       "      <td>Road-150 Red, 44</td>\n",
       "      <td>BK-R93R-44</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Red</td>\n",
       "      <td>100</td>\n",
       "      <td>75</td>\n",
       "      <td>2171.2942</td>\n",
       "      <td>3578.27</td>\n",
       "      <td>...</td>\n",
       "      <td>R</td>\n",
       "      <td>H</td>\n",
       "      <td>U</td>\n",
       "      <td>2.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>2011-05-31 00:00:00</td>\n",
       "      <td>2012-05-29 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{C19E1136-5DA4-4B40-8758-54A85D7EA494}</td>\n",
       "      <td>2014-02-08 10:01:36.827000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>255</td>\n",
       "      <td>751</td>\n",
       "      <td>Road-150 Red, 48</td>\n",
       "      <td>BK-R93R-48</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Red</td>\n",
       "      <td>100</td>\n",
       "      <td>75</td>\n",
       "      <td>2171.2942</td>\n",
       "      <td>3578.27</td>\n",
       "      <td>...</td>\n",
       "      <td>R</td>\n",
       "      <td>H</td>\n",
       "      <td>U</td>\n",
       "      <td>2.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>2011-05-31 00:00:00</td>\n",
       "      <td>2012-05-29 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{D10B7CC1-455E-435B-A08F-EC5B1C5776E9}</td>\n",
       "      <td>2014-02-08 10:01:36.827000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     ProductID    NewProductName product_number  MakeFlag  FinishedGoodsFlag  \\\n",
       "253        749  Road-150 Red, 62     BK-R93R-62         1                  1   \n",
       "254        750  Road-150 Red, 44     BK-R93R-44         1                  1   \n",
       "255        751  Road-150 Red, 48     BK-R93R-48         1                  1   \n",
       "\n",
       "    Color  SafetyStockLevel  ReorderPoint  StandardCost  ListPrice  ...  \\\n",
       "253   Red               100            75     2171.2942    3578.27  ...   \n",
       "254   Red               100            75     2171.2942    3578.27  ...   \n",
       "255   Red               100            75     2171.2942    3578.27  ...   \n",
       "\n",
       "    ProductLine Class Style  ProductSubcategoryID  ProductModelID  \\\n",
       "253          R     H     U                    2.0            25.0   \n",
       "254          R     H     U                    2.0            25.0   \n",
       "255          R     H     U                    2.0            25.0   \n",
       "\n",
       "           SellStartDate          SellEndDate DiscontinuedDate  \\\n",
       "253  2011-05-31 00:00:00  2012-05-29 00:00:00              NaN   \n",
       "254  2011-05-31 00:00:00  2012-05-29 00:00:00              NaN   \n",
       "255  2011-05-31 00:00:00  2012-05-29 00:00:00              NaN   \n",
       "\n",
       "                                    rowguid                   ModifiedDate  \n",
       "253  {BC621E1F-2553-4FDC-B22E-5E44A9003569}  2014-02-08 10:01:36.827000000  \n",
       "254  {C19E1136-5DA4-4B40-8758-54A85D7EA494}  2014-02-08 10:01:36.827000000  \n",
       "255  {D10B7CC1-455E-435B-A08F-EC5B1C5776E9}  2014-02-08 10:01:36.827000000  \n",
       "\n",
       "[3 rows x 25 columns]"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products.sort_values('StandardCost', ascending=False).head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It's also possible to sort by multiple columns, but passing in a list of the columns to sort ([docs](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.sort_values.html)).\n",
    "\n",
    "The next one is a little more advanced, but it demonstrates a few things:\n",
    "\n",
    "* Conversion of a `numpy.ndarray` object (return type of `pd.Series.unique()`) into a `pd.Series` object\n",
    "* `pd.Series.sort_values()` with the `by=` kwarg omitted (if only one column exists, `by=` doesn't need to specified)\n",
    "* Alphabetical sort of a string field, `ascending=True` means A->Z"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1           Black\n",
       "5            Blue\n",
       "8            Grey\n",
       "6           Multi\n",
       "3             Red\n",
       "2          Silver\n",
       "9    Silver/Black\n",
       "4           White\n",
       "7          Yellow\n",
       "0             NaN\n",
       "dtype: object"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.Series(products['Color'].unique()).sort_values(ascending=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "# A Detour: The Pandas Index\n",
    "\n",
    "In Pandas, an [index](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.Index.html) is an \"immutable `ndarray` implementing an ordered, sliceable set\" (not to be confused with *list indexes*, which you just saw again).\n",
    "\n",
    "It is the basic object storing axis labels for all pandas objects. \n",
    "\n",
    "Think of it as a 'row address' for your table/dataframe. It is best practice to explicitly set the index of your dataframe, as indices are commonly used as [primary keys](https://en.wikipedia.org/wiki/Primary_key) which can be used to [join](https://www.w3schools.com/sql/sql_join.asp) your dataframe to other dataframes.\n",
    "\n",
    "The dataframe can store different types (ints, string, datetime), and when data is imported, Pandas will automatically assign a number to each row, starting at zero and counting up. You can overwrite this, which is what we are going to do.\n",
    "\n",
    "First, let's take a look again at the layout of the dataframe. Notice the leftmost column is just an ascending number from 0. This is the auto-generated index that was created upon import:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "More information about the index:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that our auto-generated index has no name:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here we are looking at three columns;\n",
    "the one on the left is the index (automatically generated upon import by pandas)\n",
    "'ProductID' is our PK (primary key) from our imported table. 'Name' is a data column.\n",
    "\n",
    "Notice that the generated index starts at zero and our PK starts at 1."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Setting the index overwrites the automatically generated index with our PK, which resided in the `ProductID` column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note how our index property has changed as a result:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And our index name has also inherited the name of our `ProductID` column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And now if we example the Dataframe, the leftmost column looks different!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "# We Do: Pandas Exercises\n",
    "\n",
    "Let's do these Exercises. Don't hesitate to look at code we wrote together!\n",
    "\n",
    "## Exercise 1:\n",
    "\n",
    "Print the first 4 rows of the whole DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise 2:\n",
    "\n",
    "How many rows are in the dataframe? Return the answer as an int."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise 3:\n",
    "\n",
    "How many columns? Return the answer as an int."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise 4:\n",
    "\n",
    "How many different product lines are there?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise 5:\n",
    "\n",
    "What are the values of these product lines?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise 6:\n",
    "\n",
    "Do the number of values for the product lines match the number you have using `.nunique()`? Why or why not?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "--- Type your answer in this Markdown cell ---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise 7:\n",
    "\n",
    "Take the output from your previous answer (using `.unique()`). Select the label corresponding to the `Road` product line using list indexing notation. How many characters are in this string?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Do you notice anything odd about this?\n",
    "\n",
    "There is a space after R!!!!! The horror!!!!!\n",
    "\n",
    "## Exercise 8:\n",
    "\n",
    "How many products are there for the `Road` product line? Don't forget what you just saw above! Return your answer as an int."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise 9:\n",
    "\n",
    "How many products are there in the `Women's` `Mountain` category? Return your answer as an int. _Hint: Use the data dictionary above!_"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise 10:\n",
    "\n",
    "**Very Hard Challenge:** What are the top 3 _most expensive list price_ product that are either in the `Women's` `Mountain` category, _OR_ `Silver` in `Color`? Return your answer as a DataFrame object, with the `ProductID` index, `NewName` relabeled as `Name`, and `ListPrice` columns. Perform the statement in one execution, and do not mutate the source DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "# Summary\n",
    "\n",
    "We covered a lot of ground! It's ok if this takes a while to gel, learning Pandas can be frustrating!\n",
    "\n",
    "It's common to refer back to your own code *all the time.* Don't hesistate to reference this notebook! 🐼"
   ]
  }
 ],
 "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
}