{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Ex1 - Filtering and Sorting Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 1. Import the necessary libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 2. Import the dataset from this [address](https://indico.narit.or.th/event/229/contributions/697/attachments/398/944/chipotle.tsv). "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 3. Assign it to a variable called chipo."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "url = 'https://indico.narit.or.th/event/229/contributions/697/attachments/398/944/chipotle.tsv'\n",
    "    \n",
    "chipo = pd.read_csv(url, sep = '\\t')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 4. How many products cost more than $10.00?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>quantity</th>\n",
       "      <th>item_name</th>\n",
       "      <th>choice_description</th>\n",
       "      <th>item_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>162</th>\n",
       "      <td>73</td>\n",
       "      <td>2</td>\n",
       "      <td>Canned Soda</td>\n",
       "      <td>[Diet Coke]</td>\n",
       "      <td>$2.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>200</th>\n",
       "      <td>89</td>\n",
       "      <td>1</td>\n",
       "      <td>Canned Soda</td>\n",
       "      <td>[Diet Coke]</td>\n",
       "      <td>$1.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>317</th>\n",
       "      <td>138</td>\n",
       "      <td>1</td>\n",
       "      <td>Canned Soda</td>\n",
       "      <td>[Diet Coke]</td>\n",
       "      <td>$1.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>350</th>\n",
       "      <td>150</td>\n",
       "      <td>2</td>\n",
       "      <td>Canned Soda</td>\n",
       "      <td>[Diet Coke]</td>\n",
       "      <td>$2.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>370</th>\n",
       "      <td>160</td>\n",
       "      <td>1</td>\n",
       "      <td>Canned Soda</td>\n",
       "      <td>[Diet Coke]</td>\n",
       "      <td>$1.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>779</th>\n",
       "      <td>321</td>\n",
       "      <td>1</td>\n",
       "      <td>Canned Soda</td>\n",
       "      <td>[Diet Coke]</td>\n",
       "      <td>$1.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1216</th>\n",
       "      <td>496</td>\n",
       "      <td>1</td>\n",
       "      <td>Canned Soda</td>\n",
       "      <td>[Diet Coke]</td>\n",
       "      <td>$1.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1662</th>\n",
       "      <td>672</td>\n",
       "      <td>1</td>\n",
       "      <td>Canned Soda</td>\n",
       "      <td>[Diet Coke]</td>\n",
       "      <td>$1.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1953</th>\n",
       "      <td>790</td>\n",
       "      <td>1</td>\n",
       "      <td>Canned Soda</td>\n",
       "      <td>[Diet Coke]</td>\n",
       "      <td>$1.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2135</th>\n",
       "      <td>859</td>\n",
       "      <td>2</td>\n",
       "      <td>Canned Soda</td>\n",
       "      <td>[Diet Coke]</td>\n",
       "      <td>$2.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2544</th>\n",
       "      <td>1009</td>\n",
       "      <td>1</td>\n",
       "      <td>Canned Soda</td>\n",
       "      <td>[Diet Coke]</td>\n",
       "      <td>$1.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2850</th>\n",
       "      <td>1132</td>\n",
       "      <td>1</td>\n",
       "      <td>Canned Soda</td>\n",
       "      <td>[Diet Coke]</td>\n",
       "      <td>$1.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3592</th>\n",
       "      <td>1440</td>\n",
       "      <td>2</td>\n",
       "      <td>Canned Soda</td>\n",
       "      <td>[Diet Coke]</td>\n",
       "      <td>$2.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3793</th>\n",
       "      <td>1518</td>\n",
       "      <td>1</td>\n",
       "      <td>Canned Soda</td>\n",
       "      <td>[Diet Coke]</td>\n",
       "      <td>$1.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4008</th>\n",
       "      <td>1604</td>\n",
       "      <td>1</td>\n",
       "      <td>Canned Soda</td>\n",
       "      <td>[Diet Coke]</td>\n",
       "      <td>$1.09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      order_id  quantity    item_name choice_description item_price\n",
       "162         73         2  Canned Soda        [Diet Coke]     $2.18 \n",
       "200         89         1  Canned Soda        [Diet Coke]     $1.09 \n",
       "317        138         1  Canned Soda        [Diet Coke]     $1.09 \n",
       "350        150         2  Canned Soda        [Diet Coke]     $2.18 \n",
       "370        160         1  Canned Soda        [Diet Coke]     $1.09 \n",
       "779        321         1  Canned Soda        [Diet Coke]     $1.09 \n",
       "1216       496         1  Canned Soda        [Diet Coke]     $1.09 \n",
       "1662       672         1  Canned Soda        [Diet Coke]     $1.09 \n",
       "1953       790         1  Canned Soda        [Diet Coke]     $1.09 \n",
       "2135       859         2  Canned Soda        [Diet Coke]     $2.18 \n",
       "2544      1009         1  Canned Soda        [Diet Coke]     $1.09 \n",
       "2850      1132         1  Canned Soda        [Diet Coke]     $1.09 \n",
       "3592      1440         2  Canned Soda        [Diet Coke]     $2.18 \n",
       "3793      1518         1  Canned Soda        [Diet Coke]     $1.09 \n",
       "4008      1604         1  Canned Soda        [Diet Coke]     $1.09 "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# the item price column is actullay the price of the product multiplied by the quantity\n",
    "chipo.loc[(chipo[\"choice_description\"] == '[Diet Coke]') & (chipo[\"item_name\"] == \"Canned Soda\")]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>order_id</th>\n",
       "      <th>quantity</th>\n",
       "      <th>item_name</th>\n",
       "      <th>choice_description</th>\n",
       "      <th>item_price</th>\n",
       "      <th>product_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Chips and Fresh Tomato Salsa</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.39</td>\n",
       "      <td>2.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Izze</td>\n",
       "      <td>[Clementine]</td>\n",
       "      <td>3.39</td>\n",
       "      <td>3.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Nantucket Nectar</td>\n",
       "      <td>[Apple]</td>\n",
       "      <td>3.39</td>\n",
       "      <td>3.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Chips and Tomatillo-Green Chili Salsa</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.39</td>\n",
       "      <td>2.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>Chicken Bowl</td>\n",
       "      <td>[Tomatillo-Red Chili Salsa (Hot), [Black Beans...</td>\n",
       "      <td>16.98</td>\n",
       "      <td>8.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4617</th>\n",
       "      <td>1833</td>\n",
       "      <td>1</td>\n",
       "      <td>Steak Burrito</td>\n",
       "      <td>[Fresh Tomato Salsa, [Rice, Black Beans, Sour ...</td>\n",
       "      <td>11.75</td>\n",
       "      <td>11.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4618</th>\n",
       "      <td>1833</td>\n",
       "      <td>1</td>\n",
       "      <td>Steak Burrito</td>\n",
       "      <td>[Fresh Tomato Salsa, [Rice, Sour Cream, Cheese...</td>\n",
       "      <td>11.75</td>\n",
       "      <td>11.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4619</th>\n",
       "      <td>1834</td>\n",
       "      <td>1</td>\n",
       "      <td>Chicken Salad Bowl</td>\n",
       "      <td>[Fresh Tomato Salsa, [Fajita Vegetables, Pinto...</td>\n",
       "      <td>11.25</td>\n",
       "      <td>11.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4620</th>\n",
       "      <td>1834</td>\n",
       "      <td>1</td>\n",
       "      <td>Chicken Salad Bowl</td>\n",
       "      <td>[Fresh Tomato Salsa, [Fajita Vegetables, Lettu...</td>\n",
       "      <td>8.75</td>\n",
       "      <td>8.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4621</th>\n",
       "      <td>1834</td>\n",
       "      <td>1</td>\n",
       "      <td>Chicken Salad Bowl</td>\n",
       "      <td>[Fresh Tomato Salsa, [Fajita Vegetables, Pinto...</td>\n",
       "      <td>8.75</td>\n",
       "      <td>8.75</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4622 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      order_id  quantity                              item_name  \\\n",
       "0            1         1           Chips and Fresh Tomato Salsa   \n",
       "1            1         1                                   Izze   \n",
       "2            1         1                       Nantucket Nectar   \n",
       "3            1         1  Chips and Tomatillo-Green Chili Salsa   \n",
       "4            2         2                           Chicken Bowl   \n",
       "...        ...       ...                                    ...   \n",
       "4617      1833         1                          Steak Burrito   \n",
       "4618      1833         1                          Steak Burrito   \n",
       "4619      1834         1                     Chicken Salad Bowl   \n",
       "4620      1834         1                     Chicken Salad Bowl   \n",
       "4621      1834         1                     Chicken Salad Bowl   \n",
       "\n",
       "                                     choice_description  item_price  \\\n",
       "0                                                   NaN        2.39   \n",
       "1                                          [Clementine]        3.39   \n",
       "2                                               [Apple]        3.39   \n",
       "3                                                   NaN        2.39   \n",
       "4     [Tomatillo-Red Chili Salsa (Hot), [Black Beans...       16.98   \n",
       "...                                                 ...         ...   \n",
       "4617  [Fresh Tomato Salsa, [Rice, Black Beans, Sour ...       11.75   \n",
       "4618  [Fresh Tomato Salsa, [Rice, Sour Cream, Cheese...       11.75   \n",
       "4619  [Fresh Tomato Salsa, [Fajita Vegetables, Pinto...       11.25   \n",
       "4620  [Fresh Tomato Salsa, [Fajita Vegetables, Lettu...        8.75   \n",
       "4621  [Fresh Tomato Salsa, [Fajita Vegetables, Pinto...        8.75   \n",
       "\n",
       "      product_price  \n",
       "0              2.39  \n",
       "1              3.39  \n",
       "2              3.39  \n",
       "3              2.39  \n",
       "4              8.49  \n",
       "...             ...  \n",
       "4617          11.75  \n",
       "4618          11.75  \n",
       "4619          11.25  \n",
       "4620           8.75  \n",
       "4621           8.75  \n",
       "\n",
       "[4622 rows x 6 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# adding a new column representing the price of each single product in float\n",
    "chipo[\"item_price\"] = chipo[\"item_price\"].str.replace(\"$\", \"\", regex=False).astype(float)\n",
    "chipo[\"product_price\"] = chipo[\"item_price\"] / chipo[\"quantity\"]\n",
    "chipo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>order_id</th>\n",
       "      <th>quantity</th>\n",
       "      <th>item_name</th>\n",
       "      <th>choice_description</th>\n",
       "      <th>item_price</th>\n",
       "      <th>product_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>162</th>\n",
       "      <td>73</td>\n",
       "      <td>2</td>\n",
       "      <td>Canned Soda</td>\n",
       "      <td>[Diet Coke]</td>\n",
       "      <td>2.18</td>\n",
       "      <td>1.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>200</th>\n",
       "      <td>89</td>\n",
       "      <td>1</td>\n",
       "      <td>Canned Soda</td>\n",
       "      <td>[Diet Coke]</td>\n",
       "      <td>1.09</td>\n",
       "      <td>1.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>317</th>\n",
       "      <td>138</td>\n",
       "      <td>1</td>\n",
       "      <td>Canned Soda</td>\n",
       "      <td>[Diet Coke]</td>\n",
       "      <td>1.09</td>\n",
       "      <td>1.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>350</th>\n",
       "      <td>150</td>\n",
       "      <td>2</td>\n",
       "      <td>Canned Soda</td>\n",
       "      <td>[Diet Coke]</td>\n",
       "      <td>2.18</td>\n",
       "      <td>1.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>370</th>\n",
       "      <td>160</td>\n",
       "      <td>1</td>\n",
       "      <td>Canned Soda</td>\n",
       "      <td>[Diet Coke]</td>\n",
       "      <td>1.09</td>\n",
       "      <td>1.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>779</th>\n",
       "      <td>321</td>\n",
       "      <td>1</td>\n",
       "      <td>Canned Soda</td>\n",
       "      <td>[Diet Coke]</td>\n",
       "      <td>1.09</td>\n",
       "      <td>1.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1216</th>\n",
       "      <td>496</td>\n",
       "      <td>1</td>\n",
       "      <td>Canned Soda</td>\n",
       "      <td>[Diet Coke]</td>\n",
       "      <td>1.09</td>\n",
       "      <td>1.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1662</th>\n",
       "      <td>672</td>\n",
       "      <td>1</td>\n",
       "      <td>Canned Soda</td>\n",
       "      <td>[Diet Coke]</td>\n",
       "      <td>1.09</td>\n",
       "      <td>1.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1953</th>\n",
       "      <td>790</td>\n",
       "      <td>1</td>\n",
       "      <td>Canned Soda</td>\n",
       "      <td>[Diet Coke]</td>\n",
       "      <td>1.09</td>\n",
       "      <td>1.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2135</th>\n",
       "      <td>859</td>\n",
       "      <td>2</td>\n",
       "      <td>Canned Soda</td>\n",
       "      <td>[Diet Coke]</td>\n",
       "      <td>2.18</td>\n",
       "      <td>1.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2544</th>\n",
       "      <td>1009</td>\n",
       "      <td>1</td>\n",
       "      <td>Canned Soda</td>\n",
       "      <td>[Diet Coke]</td>\n",
       "      <td>1.09</td>\n",
       "      <td>1.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2850</th>\n",
       "      <td>1132</td>\n",
       "      <td>1</td>\n",
       "      <td>Canned Soda</td>\n",
       "      <td>[Diet Coke]</td>\n",
       "      <td>1.09</td>\n",
       "      <td>1.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3592</th>\n",
       "      <td>1440</td>\n",
       "      <td>2</td>\n",
       "      <td>Canned Soda</td>\n",
       "      <td>[Diet Coke]</td>\n",
       "      <td>2.18</td>\n",
       "      <td>1.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3793</th>\n",
       "      <td>1518</td>\n",
       "      <td>1</td>\n",
       "      <td>Canned Soda</td>\n",
       "      <td>[Diet Coke]</td>\n",
       "      <td>1.09</td>\n",
       "      <td>1.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4008</th>\n",
       "      <td>1604</td>\n",
       "      <td>1</td>\n",
       "      <td>Canned Soda</td>\n",
       "      <td>[Diet Coke]</td>\n",
       "      <td>1.09</td>\n",
       "      <td>1.09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      order_id  quantity    item_name choice_description  item_price  \\\n",
       "162         73         2  Canned Soda        [Diet Coke]        2.18   \n",
       "200         89         1  Canned Soda        [Diet Coke]        1.09   \n",
       "317        138         1  Canned Soda        [Diet Coke]        1.09   \n",
       "350        150         2  Canned Soda        [Diet Coke]        2.18   \n",
       "370        160         1  Canned Soda        [Diet Coke]        1.09   \n",
       "779        321         1  Canned Soda        [Diet Coke]        1.09   \n",
       "1216       496         1  Canned Soda        [Diet Coke]        1.09   \n",
       "1662       672         1  Canned Soda        [Diet Coke]        1.09   \n",
       "1953       790         1  Canned Soda        [Diet Coke]        1.09   \n",
       "2135       859         2  Canned Soda        [Diet Coke]        2.18   \n",
       "2544      1009         1  Canned Soda        [Diet Coke]        1.09   \n",
       "2850      1132         1  Canned Soda        [Diet Coke]        1.09   \n",
       "3592      1440         2  Canned Soda        [Diet Coke]        2.18   \n",
       "3793      1518         1  Canned Soda        [Diet Coke]        1.09   \n",
       "4008      1604         1  Canned Soda        [Diet Coke]        1.09   \n",
       "\n",
       "      product_price  \n",
       "162            1.09  \n",
       "200            1.09  \n",
       "317            1.09  \n",
       "350            1.09  \n",
       "370            1.09  \n",
       "779            1.09  \n",
       "1216           1.09  \n",
       "1662           1.09  \n",
       "1953           1.09  \n",
       "2135           1.09  \n",
       "2544           1.09  \n",
       "2850           1.09  \n",
       "3592           1.09  \n",
       "3793           1.09  \n",
       "4008           1.09  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#checking everything is correct\n",
    "chipo.loc[(chipo[\"choice_description\"] == '[Diet Coke]') & (chipo[\"item_name\"] == \"Canned Soda\")]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# removing duplicated products\n",
    "filtered_chipo=chipo.drop_duplicates(['item_name','choice_description'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# filtering products that costs more than $10\n",
    "filtered_chipo = filtered_chipo.loc[ filtered_chipo[\"product_price\"]>10.0 , [\"item_name\",\"choice_description\",\"product_price\"] ].reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "the number of products that cost more than $10.00 is 707\n"
     ]
    }
   ],
   "source": [
    "print(f\"the number of products that cost more than $10.00 is {filtered_chipo.shape[0]}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 5. What is the price of each item? \n",
    "###### print a data frame with only three columns item_name choice_description and product_price"
   ]
  },
  {
   "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>item_name</th>\n",
       "      <th>choice_description</th>\n",
       "      <th>product_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Chicken Bowl</td>\n",
       "      <td>[Fresh Tomato Salsa (Mild), [Rice, Cheese, Sou...</td>\n",
       "      <td>10.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Steak Burrito</td>\n",
       "      <td>[Tomatillo Red Chili Salsa, [Fajita Vegetables...</td>\n",
       "      <td>11.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Chicken Bowl</td>\n",
       "      <td>[Fresh Tomato Salsa, [Fajita Vegetables, Rice,...</td>\n",
       "      <td>11.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Chicken Burrito</td>\n",
       "      <td>[[Tomatillo-Green Chili Salsa (Medium), Tomati...</td>\n",
       "      <td>10.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Barbacoa Bowl</td>\n",
       "      <td>[Roasted Chili Corn Salsa, [Fajita Vegetables,...</td>\n",
       "      <td>11.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>702</th>\n",
       "      <td>Carnitas Bowl</td>\n",
       "      <td>[Roasted Chili Corn Salsa, [Rice, Sour Cream, ...</td>\n",
       "      <td>11.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>703</th>\n",
       "      <td>Barbacoa Bowl</td>\n",
       "      <td>[Roasted Chili Corn Salsa, [Pinto Beans, Sour ...</td>\n",
       "      <td>11.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>704</th>\n",
       "      <td>Steak Burrito</td>\n",
       "      <td>[Tomatillo Green Chili Salsa, [Rice, Cheese, S...</td>\n",
       "      <td>11.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>705</th>\n",
       "      <td>Steak Burrito</td>\n",
       "      <td>[Fresh Tomato Salsa, [Rice, Sour Cream, Cheese...</td>\n",
       "      <td>11.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>706</th>\n",
       "      <td>Veggie Burrito</td>\n",
       "      <td>[Tomatillo Green Chili Salsa, [Rice, Fajita Ve...</td>\n",
       "      <td>11.25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>707 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           item_name                                 choice_description  \\\n",
       "0       Chicken Bowl  [Fresh Tomato Salsa (Mild), [Rice, Cheese, Sou...   \n",
       "1      Steak Burrito  [Tomatillo Red Chili Salsa, [Fajita Vegetables...   \n",
       "2       Chicken Bowl  [Fresh Tomato Salsa, [Fajita Vegetables, Rice,...   \n",
       "3    Chicken Burrito  [[Tomatillo-Green Chili Salsa (Medium), Tomati...   \n",
       "4      Barbacoa Bowl  [Roasted Chili Corn Salsa, [Fajita Vegetables,...   \n",
       "..               ...                                                ...   \n",
       "702    Carnitas Bowl  [Roasted Chili Corn Salsa, [Rice, Sour Cream, ...   \n",
       "703    Barbacoa Bowl  [Roasted Chili Corn Salsa, [Pinto Beans, Sour ...   \n",
       "704    Steak Burrito  [Tomatillo Green Chili Salsa, [Rice, Cheese, S...   \n",
       "705    Steak Burrito  [Fresh Tomato Salsa, [Rice, Sour Cream, Cheese...   \n",
       "706   Veggie Burrito  [Tomatillo Green Chili Salsa, [Rice, Fajita Ve...   \n",
       "\n",
       "     product_price  \n",
       "0            10.98  \n",
       "1            11.75  \n",
       "2            11.25  \n",
       "3            10.98  \n",
       "4            11.75  \n",
       "..             ...  \n",
       "702          11.75  \n",
       "703          11.75  \n",
       "704          11.75  \n",
       "705          11.75  \n",
       "706          11.25  \n",
       "\n",
       "[707 rows x 3 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filtered_chipo[[\"item_name\",\"choice_description\",\"product_price\"]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 6. Sort by the name of the item"
   ]
  },
  {
   "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>order_id</th>\n",
       "      <th>quantity</th>\n",
       "      <th>item_name</th>\n",
       "      <th>choice_description</th>\n",
       "      <th>item_price</th>\n",
       "      <th>product_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3389</th>\n",
       "      <td>1360</td>\n",
       "      <td>2</td>\n",
       "      <td>6 Pack Soft Drink</td>\n",
       "      <td>[Diet Coke]</td>\n",
       "      <td>12.98</td>\n",
       "      <td>6.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>341</th>\n",
       "      <td>148</td>\n",
       "      <td>1</td>\n",
       "      <td>6 Pack Soft Drink</td>\n",
       "      <td>[Diet Coke]</td>\n",
       "      <td>6.49</td>\n",
       "      <td>6.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1849</th>\n",
       "      <td>749</td>\n",
       "      <td>1</td>\n",
       "      <td>6 Pack Soft Drink</td>\n",
       "      <td>[Coke]</td>\n",
       "      <td>6.49</td>\n",
       "      <td>6.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1860</th>\n",
       "      <td>754</td>\n",
       "      <td>1</td>\n",
       "      <td>6 Pack Soft Drink</td>\n",
       "      <td>[Diet Coke]</td>\n",
       "      <td>6.49</td>\n",
       "      <td>6.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2713</th>\n",
       "      <td>1076</td>\n",
       "      <td>1</td>\n",
       "      <td>6 Pack Soft Drink</td>\n",
       "      <td>[Coke]</td>\n",
       "      <td>6.49</td>\n",
       "      <td>6.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2384</th>\n",
       "      <td>948</td>\n",
       "      <td>1</td>\n",
       "      <td>Veggie Soft Tacos</td>\n",
       "      <td>[Roasted Chili Corn Salsa, [Fajita Vegetables,...</td>\n",
       "      <td>8.75</td>\n",
       "      <td>8.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>781</th>\n",
       "      <td>322</td>\n",
       "      <td>1</td>\n",
       "      <td>Veggie Soft Tacos</td>\n",
       "      <td>[Fresh Tomato Salsa, [Black Beans, Cheese, Sou...</td>\n",
       "      <td>8.75</td>\n",
       "      <td>8.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2851</th>\n",
       "      <td>1132</td>\n",
       "      <td>1</td>\n",
       "      <td>Veggie Soft Tacos</td>\n",
       "      <td>[Roasted Chili Corn Salsa (Medium), [Black Bea...</td>\n",
       "      <td>8.49</td>\n",
       "      <td>8.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1699</th>\n",
       "      <td>688</td>\n",
       "      <td>1</td>\n",
       "      <td>Veggie Soft Tacos</td>\n",
       "      <td>[Fresh Tomato Salsa, [Fajita Vegetables, Rice,...</td>\n",
       "      <td>11.25</td>\n",
       "      <td>11.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1395</th>\n",
       "      <td>567</td>\n",
       "      <td>1</td>\n",
       "      <td>Veggie Soft Tacos</td>\n",
       "      <td>[Fresh Tomato Salsa (Mild), [Pinto Beans, Rice...</td>\n",
       "      <td>8.49</td>\n",
       "      <td>8.49</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4622 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      order_id  quantity          item_name  \\\n",
       "3389      1360         2  6 Pack Soft Drink   \n",
       "341        148         1  6 Pack Soft Drink   \n",
       "1849       749         1  6 Pack Soft Drink   \n",
       "1860       754         1  6 Pack Soft Drink   \n",
       "2713      1076         1  6 Pack Soft Drink   \n",
       "...        ...       ...                ...   \n",
       "2384       948         1  Veggie Soft Tacos   \n",
       "781        322         1  Veggie Soft Tacos   \n",
       "2851      1132         1  Veggie Soft Tacos   \n",
       "1699       688         1  Veggie Soft Tacos   \n",
       "1395       567         1  Veggie Soft Tacos   \n",
       "\n",
       "                                     choice_description  item_price  \\\n",
       "3389                                        [Diet Coke]       12.98   \n",
       "341                                         [Diet Coke]        6.49   \n",
       "1849                                             [Coke]        6.49   \n",
       "1860                                        [Diet Coke]        6.49   \n",
       "2713                                             [Coke]        6.49   \n",
       "...                                                 ...         ...   \n",
       "2384  [Roasted Chili Corn Salsa, [Fajita Vegetables,...        8.75   \n",
       "781   [Fresh Tomato Salsa, [Black Beans, Cheese, Sou...        8.75   \n",
       "2851  [Roasted Chili Corn Salsa (Medium), [Black Bea...        8.49   \n",
       "1699  [Fresh Tomato Salsa, [Fajita Vegetables, Rice,...       11.25   \n",
       "1395  [Fresh Tomato Salsa (Mild), [Pinto Beans, Rice...        8.49   \n",
       "\n",
       "      product_price  \n",
       "3389           6.49  \n",
       "341            6.49  \n",
       "1849           6.49  \n",
       "1860           6.49  \n",
       "2713           6.49  \n",
       "...             ...  \n",
       "2384           8.75  \n",
       "781            8.75  \n",
       "2851           8.49  \n",
       "1699          11.25  \n",
       "1395           8.49  \n",
       "\n",
       "[4622 rows x 6 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chipo.item_name.sort_values()\n",
    "\n",
    "# OR\n",
    "\n",
    "chipo.sort_values(by = \"item_name\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 7. What was the quantity of the most expensive item ordered?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "15"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chipo.loc[chipo[\"item_price\"].idxmax()][\"quantity\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 8. How many times was a Veggie Salad Bowl ordered?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "18"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chipo[chipo[\"item_name\"]==\"Veggie Salad Bowl\"][\"quantity\"].sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 9. How many times did someone order more than one Canned Soda?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "20"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chipo[ ( chipo[\"item_name\"]==\"Canned Soda\" ) & ( chipo[\"quantity\"]>1 )].shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.10"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}