amazonpets (28Apr2021).ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "38a07b6f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# some helpers\n",
    "\n",
    "def get_html(url):\n",
    "    res = requests.get(url,headers= {\"User-Agent\":\"Mozilla Firefox Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:53.0) Gecko/20100101 Firefox/53.0.\"})\n",
    "    tree = html.fromstring(res.content)\n",
    "    return tree\n",
    "\n",
    "def get_products(urls):\n",
    "    \n",
    "    rows = []\n",
    "    \n",
    "    for url in urls:\n",
    "        \n",
    "        tree = get_html(url)\n",
    "        [rows.append(etree.tostring(elem)) for elem in tree.cssselect('.product')]\n",
    "\n",
    "        try:\n",
    "            next_page = tree.cssselect('.next')[0].xpath('.//@href')[0]\n",
    "            if next_page not in urls:\n",
    "                urls.append(next_page)\n",
    "        except:\n",
    "            pass\n",
    "        \n",
    "    return rows\n",
    "\n",
    "def process_products(data):\n",
    "    \n",
    "    df = pd.DataFrame()\n",
    "\n",
    "    for product in data:\n",
    "\n",
    "        prod = html.fromstring(product)\n",
    "\n",
    "        prod_id = list(set(prod.xpath('.//a/@data-product_id')))[0]\n",
    "        prod_sku = list(set(prod.xpath('.//a/@data-product_sku')))[0]\n",
    "        prod_title = prod.xpath('.//h3/a/text()')[0]\n",
    "        prod_url = prod.xpath('.//h3/a/@href')[0]\n",
    "        price = prod.cssselect('.price')[0].xpath('.//text()')[1]\n",
    "\n",
    "        tempdf = pd.DataFrame({\n",
    "            'id':prod_id,\n",
    "            'sku':prod_sku,\n",
    "            'title':prod_title,\n",
    "            'price':price,        \n",
    "            'url':prod_url,\n",
    "            'image':''.join(prod.xpath('.//img/@data-src')),\n",
    "        }, index=[0])\n",
    "\n",
    "        df = df.append(tempdf, sort=False)    \n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "7dcb85ae",
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests, json\n",
    "from lxml import html,etree\n",
    "import pandas as pd\n",
    "import datetime as dt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "8accaf50",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# prep seed urls\n",
    "\n",
    "tree = get_html(\"https://amazonpets.ae/product-category/dog-supplies/\")\n",
    "\n",
    "links = [link for link in tree.xpath('.//a/@href') if '/cat-supplies/' in link or '/dog-supplies/' in link and 'page' not in link]\n",
    "links = [link for link in links if link.endswith('/cat-supplies/') == False and link.endswith('/dog-supplies/') == False]\n",
    "links = [link + \"?orderby=price\" for link in links]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "14de07c2",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "54"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(links)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "842be74c",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0 https://amazonpets.ae/product-category/cat-supplies/wet-food/?orderby=price 67\n",
      "1 https://amazonpets.ae/product-category/cat-supplies/dry-food/?orderby=price 218\n",
      "2 https://amazonpets.ae/product-category/cat-supplies/cat-accessories/?orderby=price 227\n",
      "3 https://amazonpets.ae/product-category/cat-supplies/bowls-feeders/?orderby=price 253\n",
      "4 https://amazonpets.ae/product-category/cat-supplies/carriers-containment/?orderby=price 283\n",
      "5 https://amazonpets.ae/product-category/cat-supplies/furniture-scratchers/?orderby=price 350\n",
      "6 https://amazonpets.ae/product-category/cat-supplies/grooming-bathing-cat-supplies/?orderby=price 371\n",
      "7 https://amazonpets.ae/product-category/cat-supplies/litter/?orderby=price 427\n",
      "8 https://amazonpets.ae/product-category/cat-supplies/litter-boxes-accessories/?orderby=price 481\n",
      "9 https://amazonpets.ae/product-category/cat-supplies/cat-toys/?orderby=price 647\n",
      "10 https://amazonpets.ae/product-category/cat-supplies/cat-collars-harnesses-and-leashes/?orderby=price 648\n",
      "11 https://amazonpets.ae/product-category/dog-supplies/fountains/?orderby=price 652\n",
      "12 https://amazonpets.ae/product-category/dog-supplies/grooming-bathing-dog-supplies/?orderby=price 884\n",
      "13 https://amazonpets.ae/product-category/dog-supplies/storages-containers/?orderby=price 887\n",
      "14 https://amazonpets.ae/product-category/dog-supplies/dog-toys/?orderby=price 933\n",
      "15 https://amazonpets.ae/product-category/dog-supplies/water-dispensers-food-dispensers/?orderby=price 945\n",
      "16 https://amazonpets.ae/product-category/dog-supplies/dog-carriers-containment/?orderby=price 968\n",
      "17 https://amazonpets.ae/product-category/dog-supplies/dog-litter-boxes-accessories/?orderby=price 970\n",
      "18 https://amazonpets.ae/product-category/dog-supplies/wet-food-dog-supplies/?orderby=price 1003\n",
      "19 https://amazonpets.ae/product-category/dog-supplies/dry-food-dog-supplies/?orderby=price 1182\n",
      "20 https://amazonpets.ae/product-category/dog-supplies/dog-accessories/?orderby=price 1191\n",
      "21 https://amazonpets.ae/product-category/dog-supplies/auto-feeders-auto-dispensers/?orderby=price 1200\n",
      "22 https://amazonpets.ae/product-category/dog-supplies/beds-cushions/?orderby=price 1379\n",
      "23 https://amazonpets.ae/product-category/dog-supplies/bottles/?orderby=price 1384\n",
      "24 https://amazonpets.ae/product-category/dog-supplies/bowls-plates/?orderby=price 1459\n",
      "25 https://amazonpets.ae/product-category/dog-supplies/dog-collars-harnesses-and-leashes/?orderby=price 1622\n",
      "26 https://amazonpets.ae/product-category/cat-supplies/cat-accessories/?orderby=price 1631\n",
      "27 https://amazonpets.ae/product-category/cat-supplies/bowls-feeders/?orderby=price 1657\n",
      "28 https://amazonpets.ae/product-category/cat-supplies/carriers-containment/?orderby=price 1687\n",
      "29 https://amazonpets.ae/product-category/cat-supplies/cat-collars-harnesses-and-leashes/?orderby=price 1688\n",
      "30 https://amazonpets.ae/product-category/cat-supplies/dry-food/?orderby=price 1839\n",
      "31 https://amazonpets.ae/product-category/cat-supplies/furniture-scratchers/?orderby=price 1906\n",
      "32 https://amazonpets.ae/product-category/cat-supplies/grooming-bathing-cat-supplies/?orderby=price 1927\n",
      "33 https://amazonpets.ae/product-category/cat-supplies/litter/?orderby=price 1983\n",
      "34 https://amazonpets.ae/product-category/cat-supplies/litter-boxes-accessories/?orderby=price 2037\n",
      "35 https://amazonpets.ae/product-category/cat-supplies/cat-toys/?orderby=price 2203\n",
      "36 https://amazonpets.ae/product-category/cat-supplies/wet-food/?orderby=price 2270\n",
      "37 https://amazonpets.ae/product-category/cat-supplies/dog-health-wellness/?orderby=price 2271\n",
      "38 https://amazonpets.ae/product-category/dog-supplies/dog-accessories/?orderby=price 2280\n",
      "39 https://amazonpets.ae/product-category/dog-supplies/auto-feeders-auto-dispensers/?orderby=price 2289\n",
      "40 https://amazonpets.ae/product-category/dog-supplies/beds-cushions/?orderby=price 2468\n",
      "41 https://amazonpets.ae/product-category/dog-supplies/bottles/?orderby=price 2473\n",
      "42 https://amazonpets.ae/product-category/dog-supplies/bowls-plates/?orderby=price 2548\n",
      "43 https://amazonpets.ae/product-category/dog-supplies/dog-collars-harnesses-and-leashes/?orderby=price 2711\n",
      "44 https://amazonpets.ae/product-category/dog-supplies/dry-food-dog-supplies/?orderby=price 2890\n",
      "45 https://amazonpets.ae/product-category/dog-supplies/fountains/?orderby=price 2894\n",
      "46 https://amazonpets.ae/product-category/dog-supplies/grooming-bathing-dog-supplies/?orderby=price 3126\n",
      "47 https://amazonpets.ae/product-category/dog-supplies/storages-containers/?orderby=price 3129\n",
      "48 https://amazonpets.ae/product-category/dog-supplies/dog-toys/?orderby=price 3175\n",
      "49 https://amazonpets.ae/product-category/dog-supplies/dog-treats/?orderby=price 3183\n",
      "50 https://amazonpets.ae/product-category/dog-supplies/water-dispensers-food-dispensers/?orderby=price 3195\n",
      "51 https://amazonpets.ae/product-category/dog-supplies/wet-food-dog-supplies/?orderby=price 3228\n",
      "52 https://amazonpets.ae/product-category/dog-supplies/dog-carriers-containment/?orderby=price 3251\n",
      "53 https://amazonpets.ae/product-category/dog-supplies/dog-litter-boxes-accessories/?orderby=price 3253\n"
     ]
    }
   ],
   "source": [
    "finaldf = pd.DataFrame()\n",
    "\n",
    "for i,link in enumerate(links):\n",
    "    urls = [link]\n",
    "    \n",
    "    data = get_products(urls)\n",
    "    df = process_products(data)\n",
    "    df['source'] = link\n",
    "    \n",
    "    finaldf = finaldf.append(df, sort=False)\n",
    "    \n",
    "    print(i, link, len(finaldf))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d472fca4",
   "metadata": {},
   "source": [
    "### Some checks, post-processing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "b0f53502",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 3253 entries, 0 to 0\n",
      "Data columns (total 9 columns):\n",
      " #   Column        Non-Null Count  Dtype  \n",
      "---  ------        --------------  -----  \n",
      " 0   id            3253 non-null   object \n",
      " 1   sku           3253 non-null   object \n",
      " 2   title         3253 non-null   object \n",
      " 3   price         3253 non-null   float64\n",
      " 4   url           3253 non-null   object \n",
      " 5   image         3253 non-null   object \n",
      " 6   source        3253 non-null   object \n",
      " 7   category      3253 non-null   object \n",
      " 8   sub_category  3253 non-null   object \n",
      "dtypes: float64(1), object(8)\n",
      "memory usage: 318.7+ KB\n"
     ]
    }
   ],
   "source": [
    "finaldf.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "0e75b0bd",
   "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>id</th>\n",
       "      <th>sku</th>\n",
       "      <th>title</th>\n",
       "      <th>price</th>\n",
       "      <th>url</th>\n",
       "      <th>image</th>\n",
       "      <th>source</th>\n",
       "      <th>category</th>\n",
       "      <th>sub_category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>17531</td>\n",
       "      <td>8005852941053</td>\n",
       "      <td>SCHESIR CAT CAN JELLY SENIOR CHICKEN FILLET/AL...</td>\n",
       "      <td>2.4</td>\n",
       "      <td>https://amazonpets.ae/product/schesir-cat-can-...</td>\n",
       "      <td>https://amazonpets.ae/wp-content/uploads/2020/...</td>\n",
       "      <td>https://amazonpets.ae/product-category/cat-sup...</td>\n",
       "      <td>cat-supplies</td>\n",
       "      <td>wet-food</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>13487</td>\n",
       "      <td>2170430037</td>\n",
       "      <td>Wellness Core Tender Cuts Ch/ChLvr Cat 85g</td>\n",
       "      <td>6.0</td>\n",
       "      <td>https://amazonpets.ae/product/wellness-core-te...</td>\n",
       "      <td>https://amazonpets.ae/wp-content/uploads/2020/...</td>\n",
       "      <td>https://amazonpets.ae/product-category/cat-sup...</td>\n",
       "      <td>cat-supplies</td>\n",
       "      <td>wet-food</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>13489</td>\n",
       "      <td>2170430038</td>\n",
       "      <td>Wellness Core Tender Cuts Salm/Tuna Cat 85g</td>\n",
       "      <td>6.0</td>\n",
       "      <td>https://amazonpets.ae/product/wellness-core-te...</td>\n",
       "      <td>https://amazonpets.ae/wp-content/uploads/2020/...</td>\n",
       "      <td>https://amazonpets.ae/product-category/cat-sup...</td>\n",
       "      <td>cat-supplies</td>\n",
       "      <td>wet-food</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>17495</td>\n",
       "      <td>8005852751010</td>\n",
       "      <td>SCHESIR CAT POUCH TUNA 100GM (C560)</td>\n",
       "      <td>6.0</td>\n",
       "      <td>https://amazonpets.ae/product/scheair-cat-pouc...</td>\n",
       "      <td>https://amazonpets.ae/wp-content/uploads/2020/...</td>\n",
       "      <td>https://amazonpets.ae/product-category/cat-sup...</td>\n",
       "      <td>cat-supplies</td>\n",
       "      <td>wet-food</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>17591</td>\n",
       "      <td>8005852751034</td>\n",
       "      <td>SCHESIR CAT POUCH CHICKEN FILLET 100GM (C562)</td>\n",
       "      <td>6.0</td>\n",
       "      <td>https://amazonpets.ae/product/schesir-cat-pouc...</td>\n",
       "      <td>https://amazonpets.ae/wp-content/uploads/2020/...</td>\n",
       "      <td>https://amazonpets.ae/product-category/cat-sup...</td>\n",
       "      <td>cat-supplies</td>\n",
       "      <td>wet-food</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      id            sku                                              title  \\\n",
       "0  17531  8005852941053  SCHESIR CAT CAN JELLY SENIOR CHICKEN FILLET/AL...   \n",
       "0  13487     2170430037         Wellness Core Tender Cuts Ch/ChLvr Cat 85g   \n",
       "0  13489     2170430038        Wellness Core Tender Cuts Salm/Tuna Cat 85g   \n",
       "0  17495  8005852751010                SCHESIR CAT POUCH TUNA 100GM (C560)   \n",
       "0  17591  8005852751034      SCHESIR CAT POUCH CHICKEN FILLET 100GM (C562)   \n",
       "\n",
       "   price                                                url  \\\n",
       "0    2.4  https://amazonpets.ae/product/schesir-cat-can-...   \n",
       "0    6.0  https://amazonpets.ae/product/wellness-core-te...   \n",
       "0    6.0  https://amazonpets.ae/product/wellness-core-te...   \n",
       "0    6.0  https://amazonpets.ae/product/scheair-cat-pouc...   \n",
       "0    6.0  https://amazonpets.ae/product/schesir-cat-pouc...   \n",
       "\n",
       "                                               image  \\\n",
       "0  https://amazonpets.ae/wp-content/uploads/2020/...   \n",
       "0  https://amazonpets.ae/wp-content/uploads/2020/...   \n",
       "0  https://amazonpets.ae/wp-content/uploads/2020/...   \n",
       "0  https://amazonpets.ae/wp-content/uploads/2020/...   \n",
       "0  https://amazonpets.ae/wp-content/uploads/2020/...   \n",
       "\n",
       "                                              source      category  \\\n",
       "0  https://amazonpets.ae/product-category/cat-sup...  cat-supplies   \n",
       "0  https://amazonpets.ae/product-category/cat-sup...  cat-supplies   \n",
       "0  https://amazonpets.ae/product-category/cat-sup...  cat-supplies   \n",
       "0  https://amazonpets.ae/product-category/cat-sup...  cat-supplies   \n",
       "0  https://amazonpets.ae/product-category/cat-sup...  cat-supplies   \n",
       "\n",
       "  sub_category  \n",
       "0     wet-food  \n",
       "0     wet-food  \n",
       "0     wet-food  \n",
       "0     wet-food  \n",
       "0     wet-food  "
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "finaldf.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "4876136d",
   "metadata": {},
   "outputs": [],
   "source": [
    "finaldf['category'] = finaldf['source'].apply(lambda x: x.split('?')[0].split('/')[-3])\n",
    "finaldf['sub_category'] = finaldf['source'].apply(lambda x: x.split('?')[0].split('/')[-2])\n",
    "finaldf['price'] = finaldf['price'].apply(lambda x: x.replace(',',''))\n",
    "finaldf['price'] = finaldf['price'].astype(float)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "cd2936b1",
   "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>id</th>\n",
       "      <th>sku</th>\n",
       "      <th>title</th>\n",
       "      <th>price</th>\n",
       "      <th>url</th>\n",
       "      <th>image</th>\n",
       "      <th>source</th>\n",
       "      <th>category</th>\n",
       "      <th>sub_category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>17531</td>\n",
       "      <td>8005852941053</td>\n",
       "      <td>SCHESIR CAT CAN JELLY SENIOR CHICKEN FILLET/AL...</td>\n",
       "      <td>2.4</td>\n",
       "      <td>https://amazonpets.ae/product/schesir-cat-can-...</td>\n",
       "      <td>https://amazonpets.ae/wp-content/uploads/2020/...</td>\n",
       "      <td>https://amazonpets.ae/product-category/cat-sup...</td>\n",
       "      <td>cat-supplies</td>\n",
       "      <td>wet-food</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>13487</td>\n",
       "      <td>2170430037</td>\n",
       "      <td>Wellness Core Tender Cuts Ch/ChLvr Cat 85g</td>\n",
       "      <td>6.0</td>\n",
       "      <td>https://amazonpets.ae/product/wellness-core-te...</td>\n",
       "      <td>https://amazonpets.ae/wp-content/uploads/2020/...</td>\n",
       "      <td>https://amazonpets.ae/product-category/cat-sup...</td>\n",
       "      <td>cat-supplies</td>\n",
       "      <td>wet-food</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      id            sku                                              title  \\\n",
       "0  17531  8005852941053  SCHESIR CAT CAN JELLY SENIOR CHICKEN FILLET/AL...   \n",
       "0  13487     2170430037         Wellness Core Tender Cuts Ch/ChLvr Cat 85g   \n",
       "\n",
       "   price                                                url  \\\n",
       "0    2.4  https://amazonpets.ae/product/schesir-cat-can-...   \n",
       "0    6.0  https://amazonpets.ae/product/wellness-core-te...   \n",
       "\n",
       "                                               image  \\\n",
       "0  https://amazonpets.ae/wp-content/uploads/2020/...   \n",
       "0  https://amazonpets.ae/wp-content/uploads/2020/...   \n",
       "\n",
       "                                              source      category  \\\n",
       "0  https://amazonpets.ae/product-category/cat-sup...  cat-supplies   \n",
       "0  https://amazonpets.ae/product-category/cat-sup...  cat-supplies   \n",
       "\n",
       "  sub_category  \n",
       "0     wet-food  \n",
       "0     wet-food  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "finaldf.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "554d4919",
   "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></th>\n",
       "      <th>id</th>\n",
       "      <th colspan=\"3\" halign=\"left\">price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>min</th>\n",
       "      <th>max</th>\n",
       "      <th>mean</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category</th>\n",
       "      <th>sub_category</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"12\" valign=\"top\">cat-supplies</th>\n",
       "      <th>bowls-feeders</th>\n",
       "      <td>52</td>\n",
       "      <td>18.00</td>\n",
       "      <td>385.24</td>\n",
       "      <td>165.354615</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>carriers-containment</th>\n",
       "      <td>60</td>\n",
       "      <td>28.10</td>\n",
       "      <td>900.00</td>\n",
       "      <td>215.620333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cat-accessories</th>\n",
       "      <td>18</td>\n",
       "      <td>25.00</td>\n",
       "      <td>765.24</td>\n",
       "      <td>509.382222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cat-collars-harnesses-and-leashes</th>\n",
       "      <td>2</td>\n",
       "      <td>45.00</td>\n",
       "      <td>45.00</td>\n",
       "      <td>45.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cat-toys</th>\n",
       "      <td>332</td>\n",
       "      <td>0.00</td>\n",
       "      <td>595.24</td>\n",
       "      <td>45.092651</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dog-health-wellness</th>\n",
       "      <td>1</td>\n",
       "      <td>27.00</td>\n",
       "      <td>27.00</td>\n",
       "      <td>27.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dry-food</th>\n",
       "      <td>302</td>\n",
       "      <td>5.24</td>\n",
       "      <td>403.33</td>\n",
       "      <td>84.150927</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>furniture-scratchers</th>\n",
       "      <td>134</td>\n",
       "      <td>25.00</td>\n",
       "      <td>1500.00</td>\n",
       "      <td>278.228209</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>grooming-bathing-cat-supplies</th>\n",
       "      <td>42</td>\n",
       "      <td>12.38</td>\n",
       "      <td>199.00</td>\n",
       "      <td>92.303810</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>litter</th>\n",
       "      <td>112</td>\n",
       "      <td>11.90</td>\n",
       "      <td>799.05</td>\n",
       "      <td>63.171250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>litter-boxes-accessories</th>\n",
       "      <td>108</td>\n",
       "      <td>10.00</td>\n",
       "      <td>799.05</td>\n",
       "      <td>91.955370</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>wet-food</th>\n",
       "      <td>134</td>\n",
       "      <td>2.40</td>\n",
       "      <td>120.00</td>\n",
       "      <td>12.708806</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"16\" valign=\"top\">dog-supplies</th>\n",
       "      <th>auto-feeders-auto-dispensers</th>\n",
       "      <td>18</td>\n",
       "      <td>25.00</td>\n",
       "      <td>359.00</td>\n",
       "      <td>121.010000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>beds-cushions</th>\n",
       "      <td>358</td>\n",
       "      <td>24.00</td>\n",
       "      <td>999.00</td>\n",
       "      <td>160.686089</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bottles</th>\n",
       "      <td>10</td>\n",
       "      <td>16.80</td>\n",
       "      <td>3150.00</td>\n",
       "      <td>647.220000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bowls-plates</th>\n",
       "      <td>150</td>\n",
       "      <td>8.40</td>\n",
       "      <td>277.00</td>\n",
       "      <td>38.235467</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dog-accessories</th>\n",
       "      <td>18</td>\n",
       "      <td>50.00</td>\n",
       "      <td>1513.33</td>\n",
       "      <td>741.530000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dog-carriers-containment</th>\n",
       "      <td>46</td>\n",
       "      <td>99.00</td>\n",
       "      <td>1119.00</td>\n",
       "      <td>337.543478</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dog-collars-harnesses-and-leashes</th>\n",
       "      <td>326</td>\n",
       "      <td>0.00</td>\n",
       "      <td>357.00</td>\n",
       "      <td>67.481595</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dog-litter-boxes-accessories</th>\n",
       "      <td>4</td>\n",
       "      <td>50.00</td>\n",
       "      <td>105.00</td>\n",
       "      <td>77.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dog-toys</th>\n",
       "      <td>92</td>\n",
       "      <td>12.00</td>\n",
       "      <td>595.24</td>\n",
       "      <td>56.191739</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dog-treats</th>\n",
       "      <td>8</td>\n",
       "      <td>23.81</td>\n",
       "      <td>33.34</td>\n",
       "      <td>26.430000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dry-food-dog-supplies</th>\n",
       "      <td>358</td>\n",
       "      <td>9.05</td>\n",
       "      <td>459.05</td>\n",
       "      <td>122.262849</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fountains</th>\n",
       "      <td>8</td>\n",
       "      <td>56.70</td>\n",
       "      <td>249.90</td>\n",
       "      <td>176.612500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>grooming-bathing-dog-supplies</th>\n",
       "      <td>464</td>\n",
       "      <td>12.60</td>\n",
       "      <td>619.00</td>\n",
       "      <td>73.525862</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>storages-containers</th>\n",
       "      <td>6</td>\n",
       "      <td>42.00</td>\n",
       "      <td>71.40</td>\n",
       "      <td>60.900000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>water-dispensers-food-dispensers</th>\n",
       "      <td>24</td>\n",
       "      <td>53.37</td>\n",
       "      <td>151.20</td>\n",
       "      <td>79.501667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>wet-food-dog-supplies</th>\n",
       "      <td>66</td>\n",
       "      <td>7.20</td>\n",
       "      <td>97.14</td>\n",
       "      <td>17.241818</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                  id  price           \\\n",
       "                                               count    min      max   \n",
       "category     sub_category                                              \n",
       "cat-supplies bowls-feeders                        52  18.00   385.24   \n",
       "             carriers-containment                 60  28.10   900.00   \n",
       "             cat-accessories                      18  25.00   765.24   \n",
       "             cat-collars-harnesses-and-leashes     2  45.00    45.00   \n",
       "             cat-toys                            332   0.00   595.24   \n",
       "             dog-health-wellness                   1  27.00    27.00   \n",
       "             dry-food                            302   5.24   403.33   \n",
       "             furniture-scratchers                134  25.00  1500.00   \n",
       "             grooming-bathing-cat-supplies        42  12.38   199.00   \n",
       "             litter                              112  11.90   799.05   \n",
       "             litter-boxes-accessories            108  10.00   799.05   \n",
       "             wet-food                            134   2.40   120.00   \n",
       "dog-supplies auto-feeders-auto-dispensers         18  25.00   359.00   \n",
       "             beds-cushions                       358  24.00   999.00   \n",
       "             bottles                              10  16.80  3150.00   \n",
       "             bowls-plates                        150   8.40   277.00   \n",
       "             dog-accessories                      18  50.00  1513.33   \n",
       "             dog-carriers-containment             46  99.00  1119.00   \n",
       "             dog-collars-harnesses-and-leashes   326   0.00   357.00   \n",
       "             dog-litter-boxes-accessories          4  50.00   105.00   \n",
       "             dog-toys                             92  12.00   595.24   \n",
       "             dog-treats                            8  23.81    33.34   \n",
       "             dry-food-dog-supplies               358   9.05   459.05   \n",
       "             fountains                             8  56.70   249.90   \n",
       "             grooming-bathing-dog-supplies       464  12.60   619.00   \n",
       "             storages-containers                   6  42.00    71.40   \n",
       "             water-dispensers-food-dispensers     24  53.37   151.20   \n",
       "             wet-food-dog-supplies                66   7.20    97.14   \n",
       "\n",
       "                                                            \n",
       "                                                      mean  \n",
       "category     sub_category                                   \n",
       "cat-supplies bowls-feeders                      165.354615  \n",
       "             carriers-containment               215.620333  \n",
       "             cat-accessories                    509.382222  \n",
       "             cat-collars-harnesses-and-leashes   45.000000  \n",
       "             cat-toys                            45.092651  \n",
       "             dog-health-wellness                 27.000000  \n",
       "             dry-food                            84.150927  \n",
       "             furniture-scratchers               278.228209  \n",
       "             grooming-bathing-cat-supplies       92.303810  \n",
       "             litter                              63.171250  \n",
       "             litter-boxes-accessories            91.955370  \n",
       "             wet-food                            12.708806  \n",
       "dog-supplies auto-feeders-auto-dispensers       121.010000  \n",
       "             beds-cushions                      160.686089  \n",
       "             bottles                            647.220000  \n",
       "             bowls-plates                        38.235467  \n",
       "             dog-accessories                    741.530000  \n",
       "             dog-carriers-containment           337.543478  \n",
       "             dog-collars-harnesses-and-leashes   67.481595  \n",
       "             dog-litter-boxes-accessories        77.500000  \n",
       "             dog-toys                            56.191739  \n",
       "             dog-treats                          26.430000  \n",
       "             dry-food-dog-supplies              122.262849  \n",
       "             fountains                          176.612500  \n",
       "             grooming-bathing-dog-supplies       73.525862  \n",
       "             storages-containers                 60.900000  \n",
       "             water-dispensers-food-dispensers    79.501667  \n",
       "             wet-food-dog-supplies               17.241818  "
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "finaldf.groupby(['category','sub_category']).agg({\n",
    "    'id':'count',\n",
    "    'price':['min','max','mean']\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "9f190327",
   "metadata": {},
   "outputs": [],
   "source": [
    "finaldf.to_csv(\"amazonpets_products_{}.csv\".format(int(dt.datetime.now().timestamp())), index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3deb0c95",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}