(PBL#2 part 1) Recommender Systems notebook (1).ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Business Objectives:\n",
    "Which product is the most popular by volume?\n",
    "Which product is the most popular amount spent?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What we want to discover:\n",
    "1. Find out product similarity based on product description (via cosign similarity)\n",
    "2. Make reccomendations using Matrix Vectorization"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1.1 Import Libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib as lp"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1.2 Load the Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/alisonbunce/anaconda3/envs/DeepLearning/lib/python3.7/site-packages/IPython/core/interactiveshell.py:3058: DtypeWarning: Columns (20,33,73,106,158) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    },
    {
     "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>Customers.id</th>\n",
       "      <th>Customers.fname</th>\n",
       "      <th>Customers.lname</th>\n",
       "      <th>Customers.company</th>\n",
       "      <th>Customers.create_date</th>\n",
       "      <th>Customers.status</th>\n",
       "      <th>Customers.mailing</th>\n",
       "      <th>Customers.reminders</th>\n",
       "      <th>Customers.tax_exempt</th>\n",
       "      <th>Customers.account_id</th>\n",
       "      <th>...</th>\n",
       "      <th>Products.google_shopping_label</th>\n",
       "      <th>Products.product_option</th>\n",
       "      <th>Products.size</th>\n",
       "      <th>Products.material</th>\n",
       "      <th>Products.arm_style</th>\n",
       "      <th>Products.leg_style</th>\n",
       "      <th>Products.seat_size</th>\n",
       "      <th>Products.family_id</th>\n",
       "      <th>Products.saved_status</th>\n",
       "      <th>Products.freight_cost</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>797</td>\n",
       "      <td>Christy</td>\n",
       "      <td>Dill</td>\n",
       "      <td>Company0</td>\n",
       "      <td>1426018724</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PF61071</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>John</td>\n",
       "      <td>Smith</td>\n",
       "      <td>Company1</td>\n",
       "      <td>1386089139</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PF02132</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>John</td>\n",
       "      <td>Smith</td>\n",
       "      <td>Company1</td>\n",
       "      <td>1386089139</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2 x Extra large</td>\n",
       "      <td>Nitrile</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PF00342</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>James</td>\n",
       "      <td>Anderson</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1386780263</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PF04970</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>Abraham</td>\n",
       "      <td>Pollak</td>\n",
       "      <td>Company3</td>\n",
       "      <td>1386861599</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PF03045</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 181 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Customers.id Customers.fname Customers.lname Customers.company  \\\n",
       "0           797         Christy            Dill          Company0   \n",
       "1             3            John           Smith          Company1   \n",
       "2             3            John           Smith          Company1   \n",
       "3             4           James        Anderson               NaN   \n",
       "4             5         Abraham          Pollak          Company3   \n",
       "\n",
       "   Customers.create_date  Customers.status  Customers.mailing  \\\n",
       "0             1426018724               NaN                NaN   \n",
       "1             1386089139               NaN                NaN   \n",
       "2             1386089139               NaN                NaN   \n",
       "3             1386780263               NaN                NaN   \n",
       "4             1386861599               0.0                0.0   \n",
       "\n",
       "   Customers.reminders  Customers.tax_exempt  Customers.account_id  ...  \\\n",
       "0                  NaN                   NaN                   NaN  ...   \n",
       "1                  NaN                   NaN                   NaN  ...   \n",
       "2                  NaN                   NaN                   NaN  ...   \n",
       "3                  NaN                   NaN                   NaN  ...   \n",
       "4                  NaN                   NaN                   NaN  ...   \n",
       "\n",
       "   Products.google_shopping_label  Products.product_option    Products.size  \\\n",
       "0                             NaN                      NaN              NaN   \n",
       "1                             NaN                      NaN              NaN   \n",
       "2                             NaN                      NaN  2 x Extra large   \n",
       "3                             NaN                      NaN              NaN   \n",
       "4                             NaN                      NaN              NaN   \n",
       "\n",
       "   Products.material  Products.arm_style  Products.leg_style  \\\n",
       "0                NaN                 NaN                 NaN   \n",
       "1                NaN                 NaN                 NaN   \n",
       "2            Nitrile                 NaN                 NaN   \n",
       "3                NaN                 NaN                 NaN   \n",
       "4                NaN                 NaN                 NaN   \n",
       "\n",
       "   Products.seat_size Products.family_id Products.saved_status  \\\n",
       "0                 NaN            PF61071                   0.0   \n",
       "1                 NaN            PF02132                   NaN   \n",
       "2                 NaN            PF00342                   0.0   \n",
       "3                 NaN            PF04970                   NaN   \n",
       "4                 NaN            PF03045                   NaN   \n",
       "\n",
       "  Products.freight_cost  \n",
       "0                   NaN  \n",
       "1                   NaN  \n",
       "2                   NaN  \n",
       "3                   NaN  \n",
       "4                   NaN  \n",
       "\n",
       "[5 rows x 181 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.read_csv('1533148922_PBL 5 recommendation data.csv', encoding=\"latin-1\")\n",
    "data.head(5)\n",
    "#at default encoding set at utf8, if you get an error at utf8, change to latin-1(depends on how file is saved)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 4194 entries, 0 to 4193\n",
      "Columns: 181 entries, Customers.id to Products.freight_cost\n",
      "dtypes: float64(98), int64(10), object(73)\n",
      "memory usage: 5.8+ MB\n"
     ]
    }
   ],
   "source": [
    "data.info()\n",
    "#we have 4194 rows and 181 columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Customers.id             3054\n",
       "Customers.fname          1631\n",
       "Customers.lname          2557\n",
       "Customers.company         425\n",
       "Customers.create_date    3054\n",
       "                         ... \n",
       "Products.leg_style          0\n",
       "Products.seat_size         13\n",
       "Products.family_id       1002\n",
       "Products.saved_status       2\n",
       "Products.freight_cost       0\n",
       "Length: 181, dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.nunique()"
   ]
  },
  {
   "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>0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>Customers.id</td>\n",
       "      <td>3054</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Customers.fname</td>\n",
       "      <td>1631</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Customers.lname</td>\n",
       "      <td>2557</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Customers.company</td>\n",
       "      <td>425</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Customers.create_date</td>\n",
       "      <td>3054</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Products.leg_style</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Products.seat_size</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Products.family_id</td>\n",
       "      <td>1002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Products.saved_status</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Products.freight_cost</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>181 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                          0\n",
       "Customers.id           3054\n",
       "Customers.fname        1631\n",
       "Customers.lname        2557\n",
       "Customers.company       425\n",
       "Customers.create_date  3054\n",
       "...                     ...\n",
       "Products.leg_style        0\n",
       "Products.seat_size       13\n",
       "Products.family_id     1002\n",
       "Products.saved_status     2\n",
       "Products.freight_cost     0\n",
       "\n",
       "[181 rows x 1 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#convert to a dataframe so easier to read\n",
    "pd.DataFrame(data.nunique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "#save this dataframe for reference\n",
    "df = pd.DataFrame(data.nunique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2. Understand the Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's determine the most popular product by volume"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Customers.id', 'Customers.fname', 'Customers.lname',\n",
       "       'Customers.company', 'Customers.create_date', 'Customers.status',\n",
       "       'Customers.mailing', 'Customers.reminders', 'Customers.tax_exempt',\n",
       "       'Customers.account_id',\n",
       "       ...\n",
       "       'Products.google_shopping_label', 'Products.product_option',\n",
       "       'Products.size', 'Products.material', 'Products.arm_style',\n",
       "       'Products.leg_style', 'Products.seat_size', 'Products.family_id',\n",
       "       'Products.saved_status', 'Products.freight_cost'],\n",
       "      dtype='object', length=181)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#which column reflects the volume\n",
    "data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['Customers.id', 'Customers.fname', 'Customers.lname',\n",
      "       'Customers.company', 'Customers.create_date', 'Customers.status',\n",
      "       'Customers.mailing', 'Customers.reminders', 'Customers.tax_exempt',\n",
      "       'Customers.account_id',\n",
      "       ...\n",
      "       'Products.google_shopping_label', 'Products.product_option',\n",
      "       'Products.size', 'Products.material', 'Products.arm_style',\n",
      "       'Products.leg_style', 'Products.seat_size', 'Products.family_id',\n",
      "       'Products.saved_status', 'Products.freight_cost'],\n",
      "      dtype='object', length=181)\n"
     ]
    }
   ],
   "source": [
    "print(data.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Customers.id', 'Customers.fname', 'Customers.lname', 'Customers.company', 'Customers.create_date', 'Customers.status', 'Customers.mailing', 'Customers.reminders', 'Customers.tax_exempt', 'Customers.account_id', 'Customers.sales_rep', 'Customers.rewards', 'Customers.profile_id', 'Customers.last_modified', 'Customers.customer_type', 'Orders.id', 'Orders.customer_id', 'Orders.fname', 'Orders.lname', 'Orders.company', 'Orders.order_number', 'Orders.reorder_id', 'Orders.external_source', 'Orders.external_id', 'Orders.currency', 'Orders.sales_rep', 'Orders.subtotal', 'Orders.tax', 'Orders.shipping', 'Orders.coupon_id', 'Orders.coupon_amount', 'Orders.gift_id', 'Orders.gift_amount', 'Orders.fee_name', 'Orders.fee_amount', 'Orders.discount_name', 'Orders.discount_amount', 'Orders.total', 'Orders.balance_due', 'Orders.shipping_carrier', 'Orders.shipping_method', 'Orders.shipping_trans', 'Orders.shipping_flags', 'Orders.weight', 'Orders.tracking', 'Orders.payment_status', 'Orders.payment_date', 'Orders.payment_user', 'Orders.payment_type', 'Orders.payment_method', 'Orders.payment_amount', 'Orders.purchase_order', 'Orders.payment_id', 'Orders.payment_code', 'Orders.payment_ref', 'Orders.status', 'Orders.placed_date', 'Orders.updated_date', 'Orders.shipped_date', 'Orders.comments', 'Orders.notes', 'Orders.registry_id', 'Orders.gift_message', 'Orders.website', 'Orders.mailing', 'Orders.flags', 'Orders.partial_ship', 'Orders.customer_type', 'Order_Items.id', 'Order_Items.parent', 'Order_Items.product_id', 'Order_Items.product_name', 'Order_Items.attributes', 'Order_Items.attribute_names', 'Order_Items.attribute_prices', 'Order_Items.qty', 'Order_Items.price', 'Order_Items.cost', 'Order_Items.registry_item', 'Order_Items.related_id', 'Order_Items.reorder_frequency', 'Order_Items.account_id', 'Order_Items.flags', 'Products.id', 'Products.status', 'Products.product_type', 'Products.template', 'Products.vendor', 'Products.import_id', 'Products.name', 'Products.display_name', 'Products.menu_name', 'Products.list_price', 'Products.price', 'Products.sale_price', 'Products.cost', 'Products.flags', 'Products.left_flag', 'Products.right_flag', 'Products.last_modified', 'Products.taxable', 'Products.shopping_gtin', 'Products.shopping_brand', 'Products.shopping_mpn', 'Products.shopping_gender', 'Products.shopping_color', 'Products.shopping_age', 'Products.shopping_flags', 'Products.amazon_asin', 'Products.amazon_type', 'Products.amazon_item_type', 'Products.amazon_price', 'Products.google_shopping_id', 'Products.google_shopping_type', 'Products.google_shopping_cat', 'Products.google_adwords', 'Products.shopping_cat', 'Products.shopping_type', 'Products.pricegrabber_cat', 'Products.shopzilla_cat', 'Products.thefind_cat', 'Products.quickbooks_id', 'Products.qb_edit_sequence', 'Products.price_break_type', 'Products.price_breaks', 'Products.short_description', 'Products.long_description', 'Products.websites', 'Products.video', 'Products.audio', 'Products.seo_title', 'Products.seo_description', 'Products.seo_keywords', 'Products.seo_header', 'Products.seo_footer', 'Products.seo_url', 'Products.seo_category', 'Products.unit', 'Products.packaging', 'Products.display_packaging', 'Products.multiple', 'Products.length', 'Products.width', 'Products.height', 'Products.rx', 'Products.latex', 'Products.upc', 'Products.msds_link', 'Products.msds_label', 'Products.lit_link', 'Products.lit_label', 'Products.hcpcs', 'Products.case_qty', 'Products.markup', 'Products.override_markup', 'Products.notes', 'Products.import_flags', 'Products.map_price', 'Products.features_title', 'Products.warranty', 'Products.hygienic', 'Products.default_quantity', 'Products.user_size', 'Products.assembly', 'Products.installation', 'Products.shipping_length', 'Products.shipping_width', 'Products.shipping_height', 'Products.shipping_weight', 'Products.handling_time', 'Products.rotation_link', 'Products.google_shopping_label', 'Products.product_option', 'Products.size', 'Products.material', 'Products.arm_style', 'Products.leg_style', 'Products.seat_size', 'Products.family_id', 'Products.saved_status', 'Products.freight_cost']\n"
     ]
    }
   ],
   "source": [
    "#list of all the columns in our dataframe\n",
    "print(list(data.columns))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Determine relevant columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's determine which column will reflect volume. This data is based on quantities ordered, so let's use quantity."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'qty' in data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'Qty' in data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'Order_Items.qty' in data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'Products.name' in data.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Create a new dataframe for relevant columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "#create new dataframe to see the total list of products and total quantity of products ordered\n",
    "dataPQ = data[['Order_Items.qty', 'Products.name', 'Order_Items.price']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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_Items.qty</th>\n",
       "      <th>Products.name</th>\n",
       "      <th>Order_Items.price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Basic Steel Rollators,Green</td>\n",
       "      <td>57.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>Urinary Drain Bags</td>\n",
       "      <td>5.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>SensiCare Nitrile Exam Gloves,Blue,XX-Large</td>\n",
       "      <td>68.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>Basket for 2-Button Walkers</td>\n",
       "      <td>19.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>TENS 3000 Analog Unit</td>\n",
       "      <td>36.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4189</td>\n",
       "      <td>1</td>\n",
       "      <td>Emesis Bags,Blue,36.000 OZ</td>\n",
       "      <td>14.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4190</td>\n",
       "      <td>1</td>\n",
       "      <td>Bed Assist Bar</td>\n",
       "      <td>39.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4191</td>\n",
       "      <td>1</td>\n",
       "      <td>Spitfire EX Compact Travel Power Mobility Scoo...</td>\n",
       "      <td>1099.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4192</td>\n",
       "      <td>1</td>\n",
       "      <td>Standard Bariatric Heavy Duty Rollator,Burgund...</td>\n",
       "      <td>129.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4193</td>\n",
       "      <td>2</td>\n",
       "      <td>Medline Sterile Alcohol Prep Pads,Large</td>\n",
       "      <td>12.27</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4194 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Order_Items.qty                                      Products.name  \\\n",
       "0                   1                        Basic Steel Rollators,Green   \n",
       "1                   4                                 Urinary Drain Bags   \n",
       "2                   1        SensiCare Nitrile Exam Gloves,Blue,XX-Large   \n",
       "3                   1                        Basket for 2-Button Walkers   \n",
       "4                   1                              TENS 3000 Analog Unit   \n",
       "...               ...                                                ...   \n",
       "4189                1                         Emesis Bags,Blue,36.000 OZ   \n",
       "4190                1                                     Bed Assist Bar   \n",
       "4191                1  Spitfire EX Compact Travel Power Mobility Scoo...   \n",
       "4192                1  Standard Bariatric Heavy Duty Rollator,Burgund...   \n",
       "4193                2            Medline Sterile Alcohol Prep Pads,Large   \n",
       "\n",
       "      Order_Items.price  \n",
       "0                 57.20  \n",
       "1                  5.00  \n",
       "2                 68.78  \n",
       "3                 19.56  \n",
       "4                 36.05  \n",
       "...                 ...  \n",
       "4189              14.85  \n",
       "4190              39.99  \n",
       "4191            1099.00  \n",
       "4192             129.99  \n",
       "4193              12.27  \n",
       "\n",
       "[4194 rows x 3 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#call the dataframe\n",
    "dataPQ"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Test our Recommendations w/ the orig data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Analysis note: we want to determine the max prod quantity ordered, (aka the highest volume product). To do this, we group the products together and find the total sum of the quantites for each product."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Products.name\n",
       "1 Box Of Medline Commode Liners with Absorbent Pad                                      2\n",
       "1-Ply Blockade AngelStat Surgical Gowns,Ceil Blue,Extra Large                           1\n",
       "1/2\" self-adhesive hook material, 25 yard dispenser box, white                          1\n",
       "1/2\" self-adhesive loop material, 25 yard dispenser box, white                          1\n",
       "100% Cotton Unisex Reversible Hyperbaric Scrub Tops,Misty Green,MZS                     3\n",
       "                                                                                       ..\n",
       "Youth-Sized Walker Platform Attachment                                                  1\n",
       "Zip-Style Biohazard Specimen Bags,Clear                                                 1\n",
       "iWalker Euro Style Rollator                                                             1\n",
       "sEMG - MyoTrac Home Trainer with Myoscan Active Sensor and extender cable, 1-channel    1\n",
       "sEMG - single use uni-gel electrodes only, case of 100                                  1\n",
       "Name: Order_Items.qty, Length: 1391, dtype: int64"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#list\n",
    "dataPQ.groupby('Products.name')['Order_Items.qty'].sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "tells us what quanitity of each product was ordered"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Products.name\n",
       "Disposable Emergency Blanket,Not Applicable                   396\n",
       "MoliCare Disposable Super Plus Briefs,27\"-47\"                 228\n",
       "2-Ply Tissue/Poly Professional Towels,Not Applicable          220\n",
       "Sanitary Pads with Adhesive & Wings                           133\n",
       "Emesis Bags,Blue,36.000 OZ                                    129\n",
       "                                                             ... \n",
       "Dynarex 4234, Stool Container - Polystyrene    100/cs           1\n",
       "Rigid Post-Op Shoes,Navy Blue,Medium                            1\n",
       "Dynarex 3596, Athletic Tape, 1&frac12;\" x 15 yds., 4/8/Cs       1\n",
       "Dynarex 3232, Gauze Sponge N/S 3x3  8 Ply - 20/200 (4M)/Cs      1\n",
       "sEMG - single use uni-gel electrodes only, case of 100          1\n",
       "Name: Order_Items.qty, Length: 1391, dtype: int64"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#now lets sort above by values\n",
    "dataPQ.groupby('Products.name')['Order_Items.qty'].sum().sort_values(ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "our most popular product is at the top. Based on the total number purchased, the Disposable Emergency Blanket is the most purchased item"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. Additional Analysis to answer our business objective questions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's find out the most popular product based on total $ amount (sales). So we want to pull the highest number of the total items price column to determine this"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Products.name\n",
       "Aluminum Transport Chair with 12\" Wheels,Red,F: 8   R: 12     8333.02\n",
       "Aluminum Transport Chair with 12\" Wheels,Blue,F: 8   R: 12    8304.39\n",
       "K1 Basic Extra-Wide Wheelchairs                               6631.76\n",
       "Extra-Wide Wheelchairs                                        4939.99\n",
       "K1 Basic Wheelchairs                                          4657.00\n",
       "                                                               ...   \n",
       "OR Sterile Specimen Containers,120.0 ML                          2.84\n",
       "Double-Sided Sterile Shave Prep Razor                            2.52\n",
       "Sterile Eye Pads                                                 2.37\n",
       "Perineal Irrigation Bottle,8.000 OZ                              2.31\n",
       "Paper Measuring Tapes,72.00 IN                                   1.12\n",
       "Name: Order_Items.price, Length: 1391, dtype: float64"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataPQ.groupby('Products.name')['Order_Items.price'].sum().sort_values(ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This tells us that prouduct that genereted the highest sales was, Aluminum Transport Chair with 12\" Wheels,Red"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's find out how many people ordered each product from these two columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "scrolled": true
   },
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">Order_Items.qty</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>size</th>\n",
       "      <th>sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Products.name</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>1 Box Of Medline Commode Liners with Absorbent Pad</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1-Ply Blockade AngelStat Surgical Gowns,Ceil Blue,Extra Large</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1/2\" self-adhesive hook material, 25 yard dispenser box, white</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1/2\" self-adhesive loop material, 25 yard dispenser box, white</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>100% Cotton Unisex Reversible Hyperbaric Scrub Tops,Misty Green,MZS</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Youth-Sized Walker Platform Attachment</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Zip-Style Biohazard Specimen Bags,Clear</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>iWalker Euro Style Rollator</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>sEMG - MyoTrac Home Trainer with Myoscan Active Sensor and extender cable, 1-channel</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>sEMG - single use uni-gel electrodes only, case of 100</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1391 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   Order_Items.qty    \n",
       "                                                              size sum\n",
       "Products.name                                                         \n",
       "1 Box Of Medline Commode Liners with Absorbent Pad               1   2\n",
       "1-Ply Blockade AngelStat Surgical Gowns,Ceil Bl...               1   1\n",
       "1/2\" self-adhesive hook material, 25 yard dispe...               1   1\n",
       "1/2\" self-adhesive loop material, 25 yard dispe...               1   1\n",
       "100% Cotton Unisex Reversible Hyperbaric Scrub ...               2   3\n",
       "...                                                            ...  ..\n",
       "Youth-Sized Walker Platform Attachment                           1   1\n",
       "Zip-Style Biohazard Specimen Bags,Clear                          1   1\n",
       "iWalker Euro Style Rollator                                      1   1\n",
       "sEMG - MyoTrac Home Trainer with Myoscan Active...               1   1\n",
       "sEMG - single use uni-gel electrodes only, case...               1   1\n",
       "\n",
       "[1391 rows x 2 columns]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#size tells us how many people ordered\n",
    "#sum tells us how mamy proucts sold\n",
    "dataPQ.groupby('Products.name').agg({'Order_Items.qty':[np.size, np.sum]})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's determine what products were purchased from the most people"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">Order_Items.qty</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>size</th>\n",
       "      <th>sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Products.name</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>Emesis Bags,Blue,36.000 OZ</td>\n",
       "      <td>103</td>\n",
       "      <td>129</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>MoliCare Disposable Super Plus Briefs,27\"-47\"</td>\n",
       "      <td>71</td>\n",
       "      <td>228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Aluminum Transport Chair with 12\" Wheels,Blue,F: 8   R: 12</td>\n",
       "      <td>67</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Aluminum Transport Chair with 12\" Wheels,Red,F: 8   R: 12</td>\n",
       "      <td>67</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>K1 Basic Extra-Wide Wheelchairs</td>\n",
       "      <td>54</td>\n",
       "      <td>58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Foam Side Rail Bumper Pads</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Folding Bedside Commode with Bucket and Splash Guard</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Folding Blind Cane with Wrist Strap</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Folding Exercise Peddler with Digital Display, Blue</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>sEMG - single use uni-gel electrodes only, case of 100</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1391 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   Order_Items.qty     \n",
       "                                                              size  sum\n",
       "Products.name                                                          \n",
       "Emesis Bags,Blue,36.000 OZ                                     103  129\n",
       "MoliCare Disposable Super Plus Briefs,27\"-47\"                   71  228\n",
       "Aluminum Transport Chair with 12\" Wheels,Blue,F...              67   68\n",
       "Aluminum Transport Chair with 12\" Wheels,Red,F:...              67   68\n",
       "K1 Basic Extra-Wide Wheelchairs                                 54   58\n",
       "...                                                            ...  ...\n",
       "Foam Side Rail Bumper Pads                                       1    1\n",
       "Folding Bedside Commode with Bucket and Splash ...               1    1\n",
       "Folding Blind Cane with Wrist Strap                              1    1\n",
       "Folding Exercise Peddler with Digital Display, ...               1    1\n",
       "sEMG - single use uni-gel electrodes only, case...               1    1\n",
       "\n",
       "[1391 rows x 2 columns]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#we want to see the amount of people that bought certain products. show us the most people that bought the most prod\n",
    "dataPQ.groupby('Products.name').agg({'Order_Items.qty':[np.size, np.sum]}).sort_values(by=('Order_Items.qty', 'size'), ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Emesis Bags,Blue,36.000 OZ\twere the most frequently ordered by the most number of people. Bought by the most amount of people, sorted by the biggest amount sold."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Analysis: So based on this analysis we can recommend these best selling products specifically because we don't yet know the customer/user's preferences"
   ]
  }
 ],
 "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.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}