excel_to_panda.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "247d92ec",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import dtale"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "b9c9dd97",
   "metadata": {},
   "outputs": [],
   "source": [
    "location = \"C:\\\\Users\\\\User\\\\Pictures\\\\work\\\\file\\\\Contoso.xlsx\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "daaf3d12",
   "metadata": {},
   "source": [
    "# Importing excel workbook "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "beccccf9",
   "metadata": {},
   "outputs": [],
   "source": [
    "#CustData = pd.read_excel(open(location, 'rb'),sheet_name='Sheet2')\n",
    "#datetable = pd.read_excel(open(location, 'rb'),sheet_name='Sheet3')\n",
    "ProdDetails = pd.read_excel(open(location, 'rb'),sheet_name='Sheet4')\n",
    "subcategory = pd.read_excel(open(location, 'rb'),sheet_name='Sheet5')\n",
    "category = pd.read_excel(open(location, 'rb'),sheet_name='Sheet6')\n",
    "salestable = pd.read_excel(open(location, 'rb'),sheet_name='Sheet7')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b2b487e3",
   "metadata": {},
   "source": [
    "# Changing Column names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "5d9229c4",
   "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>ProductCategoryKey1</th>\n",
       "      <th>Category Code</th>\n",
       "      <th>Category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Audio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>TV and Video</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>Computers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>Cameras and camcorders</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>Cell phones</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>Music, Movies and Audio Books</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>Games and Toys</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>Home Appliances</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ProductCategoryKey1  Category Code                       Category\n",
       "0                    1              1                          Audio\n",
       "1                    2              2                   TV and Video\n",
       "2                    3              3                      Computers\n",
       "3                    4              4        Cameras and camcorders \n",
       "4                    5              5                    Cell phones\n",
       "5                    6              6  Music, Movies and Audio Books\n",
       "6                    7              7                 Games and Toys\n",
       "7                    8              8                Home Appliances"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "categoryupdate = category.rename(columns={\"ProductCategoryKey\" : \"ProductCategoryKey1\"})\n",
    "categoryupdate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "b43d697a",
   "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>ProductSubcategoryKey</th>\n",
       "      <th>Subcategory Code</th>\n",
       "      <th>Subcategory</th>\n",
       "      <th>ProductCategoryKey</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>101</td>\n",
       "      <td>MP4&amp;MP3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>102</td>\n",
       "      <td>Recorder</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>103</td>\n",
       "      <td>Radio</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>104</td>\n",
       "      <td>Recording Pen</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>105</td>\n",
       "      <td>Headphones</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ProductSubcategoryKey  Subcategory Code    Subcategory  ProductCategoryKey\n",
       "0                      1               101        MP4&MP3                   1\n",
       "1                      2               102       Recorder                   1\n",
       "2                      3               103          Radio                   1\n",
       "3                      4               104  Recording Pen                   1\n",
       "4                      5               105     Headphones                   1"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "subcategory.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d4f2f8a5",
   "metadata": {},
   "source": [
    "# Vlookup using merge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "6d46d974",
   "metadata": {},
   "outputs": [],
   "source": [
    "tablejoin = category.merge(subcategory[[\"ProductCategoryKey\" ,\"Subcategory\"]] \n",
    "                           , left_on= \"ProductCategoryKey\" , right_on=\"ProductCategoryKey\" , how = \"left\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "d82e2eca",
   "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>ProductCategoryKey</th>\n",
       "      <th>Category Code</th>\n",
       "      <th>Category</th>\n",
       "      <th>Subcategory</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Audio</td>\n",
       "      <td>MP4&amp;MP3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Audio</td>\n",
       "      <td>Recorder</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Audio</td>\n",
       "      <td>Radio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Audio</td>\n",
       "      <td>Recording Pen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Audio</td>\n",
       "      <td>Headphones</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ProductCategoryKey  Category Code Category    Subcategory\n",
       "0                   1              1    Audio        MP4&MP3\n",
       "1                   1              1    Audio       Recorder\n",
       "2                   1              1    Audio          Radio\n",
       "3                   1              1    Audio  Recording Pen\n",
       "4                   1              1    Audio     Headphones"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tablejoin.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "8fc48474",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(44, 4)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "subcategory.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d5eff3fc",
   "metadata": {},
   "source": [
    "# Left join between 2 Dataframes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "ab5dbf16",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.merge(categoryupdate , subcategory , left_on= \"ProductCategoryKey1\" , right_on= \"ProductCategoryKey\" , how = \"left\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "f918601e",
   "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>ProductCategoryKey1</th>\n",
       "      <th>Category Code</th>\n",
       "      <th>Category</th>\n",
       "      <th>ProductSubcategoryKey</th>\n",
       "      <th>Subcategory Code</th>\n",
       "      <th>Subcategory</th>\n",
       "      <th>ProductCategoryKey</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Audio</td>\n",
       "      <td>1</td>\n",
       "      <td>101</td>\n",
       "      <td>MP4&amp;MP3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Audio</td>\n",
       "      <td>2</td>\n",
       "      <td>102</td>\n",
       "      <td>Recorder</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Audio</td>\n",
       "      <td>3</td>\n",
       "      <td>103</td>\n",
       "      <td>Radio</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Audio</td>\n",
       "      <td>4</td>\n",
       "      <td>104</td>\n",
       "      <td>Recording Pen</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Audio</td>\n",
       "      <td>5</td>\n",
       "      <td>105</td>\n",
       "      <td>Headphones</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ProductCategoryKey1  Category Code Category  ProductSubcategoryKey  \\\n",
       "0                    1              1    Audio                      1   \n",
       "1                    1              1    Audio                      2   \n",
       "2                    1              1    Audio                      3   \n",
       "3                    1              1    Audio                      4   \n",
       "4                    1              1    Audio                      5   \n",
       "\n",
       "   Subcategory Code    Subcategory  ProductCategoryKey  \n",
       "0               101        MP4&MP3                   1  \n",
       "1               102       Recorder                   1  \n",
       "2               103          Radio                   1  \n",
       "3               104  Recording Pen                   1  \n",
       "4               105     Headphones                   1  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "443a12df",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(44, 7)"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "47275c4b",
   "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>ProductKey</th>\n",
       "      <th>Product Code</th>\n",
       "      <th>Product Name</th>\n",
       "      <th>Product Description</th>\n",
       "      <th>ProductSubcategoryKey</th>\n",
       "      <th>Manufacturer</th>\n",
       "      <th>Brand</th>\n",
       "      <th>Class</th>\n",
       "      <th>Style</th>\n",
       "      <th>Color</th>\n",
       "      <th>Size</th>\n",
       "      <th>Weight</th>\n",
       "      <th>Weight Unit Measure</th>\n",
       "      <th>Stock Type Code</th>\n",
       "      <th>Stock Type</th>\n",
       "      <th>Unit Cost</th>\n",
       "      <th>Unit Price</th>\n",
       "      <th>Available Date</th>\n",
       "      <th>Status</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>101001</td>\n",
       "      <td>Contoso 512MB MP3 Player E51 Silver</td>\n",
       "      <td>512MB USB driver plays MP3 and WMA</td>\n",
       "      <td>1</td>\n",
       "      <td>Contoso, Ltd</td>\n",
       "      <td>Contoso</td>\n",
       "      <td>Economy</td>\n",
       "      <td>Product0101001</td>\n",
       "      <td>Silver</td>\n",
       "      <td>2.2 x 1.8 x 4</td>\n",
       "      <td>4.8</td>\n",
       "      <td>ounces</td>\n",
       "      <td>1</td>\n",
       "      <td>High</td>\n",
       "      <td>6.62</td>\n",
       "      <td>12.99</td>\n",
       "      <td>2005-05-03</td>\n",
       "      <td>On</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>101002</td>\n",
       "      <td>Contoso 512MB MP3 Player E51 Blue</td>\n",
       "      <td>512MB USB driver plays MP3 and WMA</td>\n",
       "      <td>1</td>\n",
       "      <td>Contoso, Ltd</td>\n",
       "      <td>Contoso</td>\n",
       "      <td>Economy</td>\n",
       "      <td>Product0101002</td>\n",
       "      <td>Blue</td>\n",
       "      <td>2.2 x 1.8 x 4</td>\n",
       "      <td>4.1</td>\n",
       "      <td>ounces</td>\n",
       "      <td>1</td>\n",
       "      <td>High</td>\n",
       "      <td>6.62</td>\n",
       "      <td>12.99</td>\n",
       "      <td>2005-05-03</td>\n",
       "      <td>On</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ProductKey  Product Code                         Product Name  \\\n",
       "0           1        101001  Contoso 512MB MP3 Player E51 Silver   \n",
       "1           2        101002    Contoso 512MB MP3 Player E51 Blue   \n",
       "\n",
       "                  Product Description  ProductSubcategoryKey  Manufacturer  \\\n",
       "0  512MB USB driver plays MP3 and WMA                      1  Contoso, Ltd   \n",
       "1  512MB USB driver plays MP3 and WMA                      1  Contoso, Ltd   \n",
       "\n",
       "     Brand    Class           Style   Color            Size  Weight  \\\n",
       "0  Contoso  Economy  Product0101001  Silver  2.2 x 1.8 x 4      4.8   \n",
       "1  Contoso  Economy  Product0101002    Blue  2.2 x 1.8 x 4      4.1   \n",
       "\n",
       "  Weight Unit Measure  Stock Type Code Stock Type  Unit Cost  Unit Price  \\\n",
       "0              ounces                1       High       6.62       12.99   \n",
       "1              ounces                1       High       6.62       12.99   \n",
       "\n",
       "  Available Date Status  \n",
       "0     2005-05-03     On  \n",
       "1     2005-05-03     On  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ProdDetails.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "0a86d857",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2517, 19)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ProdDetails.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "5c739be5",
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = pd.merge(ProdDetails , df , left_on= \"ProductSubcategoryKey\" , right_on= \"ProductSubcategoryKey\" , how = \"left\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "84711a75",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2517, 25)"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "3fc8e53b",
   "metadata": {},
   "outputs": [],
   "source": [
    "dftest = pd.merge(df , ProdDetails , left_on= \"ProductSubcategoryKey\" , right_on= \"ProductSubcategoryKey\" , how = \"left\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "b6b0ee0f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2529, 25)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dftest.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c8a82f37",
   "metadata": {},
   "source": [
    "# To check Null Values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "120c5d9c",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ProductCategoryKey1        0\n",
       "Category Code              0\n",
       "Category                   0\n",
       "ProductSubcategoryKey      0\n",
       "Subcategory Code           0\n",
       "Subcategory                0\n",
       "ProductCategoryKey         0\n",
       "ProductKey                12\n",
       "Product Code              12\n",
       "Product Name              12\n",
       "Product Description       13\n",
       "Manufacturer              12\n",
       "Brand                     12\n",
       "Class                     12\n",
       "Style                     12\n",
       "Color                     12\n",
       "Size                     582\n",
       "Weight                   296\n",
       "Weight Unit Measure      234\n",
       "Stock Type Code           12\n",
       "Stock Type                12\n",
       "Unit Cost                 12\n",
       "Unit Price                12\n",
       "Available Date            13\n",
       "Status                   307\n",
       "dtype: int64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dftest.isna().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "057b5071",
   "metadata": {},
   "source": [
    "# Getting list of all Null Values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "4e497878",
   "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>ProductCategoryKey1</th>\n",
       "      <th>Category Code</th>\n",
       "      <th>Category</th>\n",
       "      <th>ProductSubcategoryKey</th>\n",
       "      <th>Subcategory Code</th>\n",
       "      <th>Subcategory</th>\n",
       "      <th>ProductCategoryKey</th>\n",
       "      <th>ProductKey</th>\n",
       "      <th>Product Code</th>\n",
       "      <th>Product Name</th>\n",
       "      <th>...</th>\n",
       "      <th>Color</th>\n",
       "      <th>Size</th>\n",
       "      <th>Weight</th>\n",
       "      <th>Weight Unit Measure</th>\n",
       "      <th>Stock Type Code</th>\n",
       "      <th>Stock Type</th>\n",
       "      <th>Unit Cost</th>\n",
       "      <th>Unit Price</th>\n",
       "      <th>Available Date</th>\n",
       "      <th>Status</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Audio</td>\n",
       "      <td>2</td>\n",
       "      <td>102</td>\n",
       "      <td>Recorder</td>\n",
       "      <td>1</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>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Audio</td>\n",
       "      <td>3</td>\n",
       "      <td>103</td>\n",
       "      <td>Radio</td>\n",
       "      <td>1</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>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Audio</td>\n",
       "      <td>5</td>\n",
       "      <td>105</td>\n",
       "      <td>Headphones</td>\n",
       "      <td>1</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>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    ProductCategoryKey1  Category Code Category  ProductSubcategoryKey  \\\n",
       "45                    1              1    Audio                      2   \n",
       "46                    1              1    Audio                      3   \n",
       "67                    1              1    Audio                      5   \n",
       "\n",
       "    Subcategory Code Subcategory  ProductCategoryKey  ProductKey  \\\n",
       "45               102    Recorder                   1         NaN   \n",
       "46               103       Radio                   1         NaN   \n",
       "67               105  Headphones                   1         NaN   \n",
       "\n",
       "    Product Code Product Name  ... Color Size Weight Weight Unit Measure  \\\n",
       "45           NaN          NaN  ...   NaN  NaN    NaN                 NaN   \n",
       "46           NaN          NaN  ...   NaN  NaN    NaN                 NaN   \n",
       "67           NaN          NaN  ...   NaN  NaN    NaN                 NaN   \n",
       "\n",
       "   Stock Type Code Stock Type Unit Cost  Unit Price Available Date  Status  \n",
       "45             NaN        NaN       NaN         NaN            NaT     NaN  \n",
       "46             NaN        NaN       NaN         NaN            NaT     NaN  \n",
       "67             NaN        NaN       NaN         NaN            NaT     NaN  \n",
       "\n",
       "[3 rows x 25 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dftest[dftest[\"ProductKey\"].isnull()].head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "b85f20b9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(100231, 19)"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "salestable.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "bb7a0e15",
   "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>OnlineSalesKey</th>\n",
       "      <th>StoreKey</th>\n",
       "      <th>ProductKey</th>\n",
       "      <th>PromotionKey</th>\n",
       "      <th>CurrencyKey</th>\n",
       "      <th>CustomerKey</th>\n",
       "      <th>OrderDateKey</th>\n",
       "      <th>DueDateKey</th>\n",
       "      <th>DeliveryDateKey</th>\n",
       "      <th>Order Date</th>\n",
       "      <th>Due Date</th>\n",
       "      <th>Delivery Date</th>\n",
       "      <th>Order Number</th>\n",
       "      <th>Order Line Number</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>Unit Price</th>\n",
       "      <th>Unit Discount</th>\n",
       "      <th>Unit Cost</th>\n",
       "      <th>Net Price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>19570000</td>\n",
       "      <td>307</td>\n",
       "      <td>1857</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>995</td>\n",
       "      <td>20070102</td>\n",
       "      <td>20070112</td>\n",
       "      <td>20070112</td>\n",
       "      <td>2007-01-02</td>\n",
       "      <td>2007-01-12</td>\n",
       "      <td>2007-01-12</td>\n",
       "      <td>20070102811994</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2652.9</td>\n",
       "      <td>397.935</td>\n",
       "      <td>878.96</td>\n",
       "      <td>2254.965</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>19570001</td>\n",
       "      <td>307</td>\n",
       "      <td>1857</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>1354</td>\n",
       "      <td>20070102</td>\n",
       "      <td>20070112</td>\n",
       "      <td>20070108</td>\n",
       "      <td>2007-01-02</td>\n",
       "      <td>2007-01-12</td>\n",
       "      <td>2007-01-08</td>\n",
       "      <td>20070102812353</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2652.9</td>\n",
       "      <td>397.935</td>\n",
       "      <td>878.96</td>\n",
       "      <td>2254.965</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>19570002</td>\n",
       "      <td>307</td>\n",
       "      <td>1857</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>1355</td>\n",
       "      <td>20070102</td>\n",
       "      <td>20070114</td>\n",
       "      <td>20070110</td>\n",
       "      <td>2007-01-02</td>\n",
       "      <td>2007-01-14</td>\n",
       "      <td>2007-01-10</td>\n",
       "      <td>20070102812354</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2652.9</td>\n",
       "      <td>397.935</td>\n",
       "      <td>878.96</td>\n",
       "      <td>2254.965</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   OnlineSalesKey  StoreKey  ProductKey  PromotionKey  CurrencyKey  \\\n",
       "0        19570000       307        1857             5            1   \n",
       "1        19570001       307        1857             5            1   \n",
       "2        19570002       307        1857             5            1   \n",
       "\n",
       "   CustomerKey  OrderDateKey  DueDateKey  DeliveryDateKey Order Date  \\\n",
       "0          995      20070102    20070112         20070112 2007-01-02   \n",
       "1         1354      20070102    20070112         20070108 2007-01-02   \n",
       "2         1355      20070102    20070114         20070110 2007-01-02   \n",
       "\n",
       "    Due Date Delivery Date    Order Number  Order Line Number  Quantity  \\\n",
       "0 2007-01-12    2007-01-12  20070102811994                  1         1   \n",
       "1 2007-01-12    2007-01-08  20070102812353                  1         1   \n",
       "2 2007-01-14    2007-01-10  20070102812354                  1         1   \n",
       "\n",
       "   Unit Price  Unit Discount  Unit Cost  Net Price  \n",
       "0      2652.9        397.935     878.96   2254.965  \n",
       "1      2652.9        397.935     878.96   2254.965  \n",
       "2      2652.9        397.935     878.96   2254.965  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "salestable.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e5f8e0f0",
   "metadata": {},
   "source": [
    "# Left Join between Sales table and Product Category"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "8aeb5774",
   "metadata": {},
   "outputs": [],
   "source": [
    "finaldata = pd.merge(salestable , df1 , left_on= \"ProductKey\" , right_on= \"ProductKey\" , how = \"left\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "f07828b2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(100231, 43)"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "finaldata.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "12b51d33",
   "metadata": {},
   "source": [
    "# Using dtale package to view pandas Dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "6bd571b6",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "2021-06-10 14:42:02,737 - INFO     - Note: NumExpr detected 12 cores but \"NUMEXPR_MAX_THREADS\" not set, so enforcing safe limit of 8.\n",
      "2021-06-10 14:42:02,738 - INFO     - NumExpr defaulting to 8 threads.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "        <iframe\n",
       "            width=\"100%\"\n",
       "            height=\"475\"\n",
       "            src=\"http://admin:40000/dtale/iframe/1\"\n",
       "            frameborder=\"0\"\n",
       "            allowfullscreen\n",
       "        ></iframe>\n",
       "        "
      ],
      "text/plain": [
       "<IPython.lib.display.IFrame at 0x19b4af33a60>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": []
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dtale.show(finaldata.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d422d237",
   "metadata": {},
   "source": [
    "# Getting List of all Columns name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "a9afe84c",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['OnlineSalesKey', 'StoreKey', 'ProductKey', 'PromotionKey',\n",
       "       'CurrencyKey', 'CustomerKey', 'OrderDateKey', 'DueDateKey',\n",
       "       'DeliveryDateKey', 'Order Date', 'Due Date', 'Delivery Date',\n",
       "       'Order Number', 'Order Line Number', 'Quantity', 'Unit Price_x',\n",
       "       'Unit Discount', 'Unit Cost_x', 'Net Price', 'Product Code',\n",
       "       'Product Name', 'Product Description', 'ProductSubcategoryKey',\n",
       "       'Manufacturer', 'Brand', 'Class', 'Style', 'Color', 'Size', 'Weight',\n",
       "       'Weight Unit Measure', 'Stock Type Code', 'Stock Type', 'Unit Cost_y',\n",
       "       'Unit Price_y', 'Available Date', 'Status', 'ProductCategoryKey1',\n",
       "       'Category Code', 'Category', 'Subcategory Code', 'Subcategory',\n",
       "       'ProductCategoryKey'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "finaldata.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "283cfe16",
   "metadata": {},
   "source": [
    "# Creating Pivot Table for category and subcategory"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "227d1449",
   "metadata": {},
   "outputs": [],
   "source": [
    "salesbycategory = pd.pivot_table(finaldata , columns= \"Category\" , index=[\"Subcategory\"] \n",
    "                                 , values = 'Net Price' ,fill_value= \"\", aggfunc=np.sum , margins=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "15e3f729",
   "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>Category</th>\n",
       "      <th>Audio</th>\n",
       "      <th>Cameras and camcorders</th>\n",
       "      <th>Cell phones</th>\n",
       "      <th>Computers</th>\n",
       "      <th>Games and Toys</th>\n",
       "      <th>Home Appliances</th>\n",
       "      <th>Music, Movies and Audio Books</th>\n",
       "      <th>TV and Video</th>\n",
       "      <th>All</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Subcategory</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Air Conditioners</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>510661.9291</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>510661.9291</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bluetooth Headphones</th>\n",
       "      <td>87548.8094</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>87548.8094</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Boxed Games</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>45698.7421</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>45698.7421</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Camcorders</th>\n",
       "      <td></td>\n",
       "      <td>2265408.43</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>2265408.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cameras &amp; Camcorders Accessories</th>\n",
       "      <td></td>\n",
       "      <td>572016.9015</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>572016.9015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Car Video</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>431782.77</td>\n",
       "      <td>431782.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cell phones Accessories</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>195849.4322</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>195849.4322</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Coffee Machines</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>695241.249</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>695241.249</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Computers Accessories</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>245031.5724</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>245031.5724</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Desktops</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>726350.391</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>726350.391</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Digital Cameras</th>\n",
       "      <td></td>\n",
       "      <td>563010.643</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>563010.643</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Digital SLR Cameras</th>\n",
       "      <td></td>\n",
       "      <td>1734070.69</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1734070.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Download Games</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>213008.8326</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>213008.8326</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Fans</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>92747.3446</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>92747.3446</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Home &amp; Office Phones</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>65988.4344</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>65988.4344</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Home Theater System</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1091875.295</td>\n",
       "      <td>1091875.295</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lamps</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>799004.2559</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>799004.2559</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Laptops</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1378128.279</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1378128.279</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MP4&amp;MP3</th>\n",
       "      <td>121870.4054</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>121870.4054</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Microwaves</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>551692.3217</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>551692.3217</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Monitors</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>429727.29</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>429727.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Movie DVD</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>223781.9982</td>\n",
       "      <td></td>\n",
       "      <td>223781.9982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Printers, Scanners &amp; Fax</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>363023.85</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>363023.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Projectors &amp; Screens</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1684192.23</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1684192.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Recording Pen</th>\n",
       "      <td>66141.932</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>66141.932</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Refrigerators</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1347515.1567</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1347515.1567</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Smart phones &amp; PDAs</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>507157.54</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>507157.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Televisions</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1325476.8007</td>\n",
       "      <td>1325476.8007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Touch Screen Phones</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>378624.95</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>378624.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VCD &amp; DVD</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>301408.289</td>\n",
       "      <td>301408.289</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Washers &amp; Dryers</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>2196637.719</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>2196637.719</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Water Heaters</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>713718.28</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>713718.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All</th>\n",
       "      <td>275561.1468</td>\n",
       "      <td>5134506.6645</td>\n",
       "      <td>1147620.3566</td>\n",
       "      <td>4826453.6124</td>\n",
       "      <td>258707.5747</td>\n",
       "      <td>6907218.256001</td>\n",
       "      <td>223781.9982</td>\n",
       "      <td>3150543.1547</td>\n",
       "      <td>21924392.7639</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Category                                Audio Cameras and camcorders   \\\n",
       "Subcategory                                                             \n",
       "Air Conditioners                                                        \n",
       "Bluetooth Headphones               87548.8094                           \n",
       "Boxed Games                                                             \n",
       "Camcorders                                                 2265408.43   \n",
       "Cameras & Camcorders Accessories                          572016.9015   \n",
       "Car Video                                                               \n",
       "Cell phones Accessories                                                 \n",
       "Coffee Machines                                                         \n",
       "Computers Accessories                                                   \n",
       "Desktops                                                                \n",
       "Digital Cameras                                            563010.643   \n",
       "Digital SLR Cameras                                        1734070.69   \n",
       "Download Games                                                          \n",
       "Fans                                                                    \n",
       "Home & Office Phones                                                    \n",
       "Home Theater System                                                     \n",
       "Lamps                                                                   \n",
       "Laptops                                                                 \n",
       "MP4&MP3                           121870.4054                           \n",
       "Microwaves                                                              \n",
       "Monitors                                                                \n",
       "Movie DVD                                                               \n",
       "Printers, Scanners & Fax                                                \n",
       "Projectors & Screens                                                    \n",
       "Recording Pen                       66141.932                           \n",
       "Refrigerators                                                           \n",
       "Smart phones & PDAs                                                     \n",
       "Televisions                                                             \n",
       "Touch Screen Phones                                                     \n",
       "VCD & DVD                                                               \n",
       "Washers & Dryers                                                        \n",
       "Water Heaters                                                           \n",
       "All                               275561.1468            5134506.6645   \n",
       "\n",
       "Category                           Cell phones     Computers Games and Toys  \\\n",
       "Subcategory                                                                   \n",
       "Air Conditioners                                                              \n",
       "Bluetooth Headphones                                                          \n",
       "Boxed Games                                                      45698.7421   \n",
       "Camcorders                                                                    \n",
       "Cameras & Camcorders Accessories                                              \n",
       "Car Video                                                                     \n",
       "Cell phones Accessories            195849.4322                                \n",
       "Coffee Machines                                                               \n",
       "Computers Accessories                            245031.5724                  \n",
       "Desktops                                          726350.391                  \n",
       "Digital Cameras                                                               \n",
       "Digital SLR Cameras                                                           \n",
       "Download Games                                                  213008.8326   \n",
       "Fans                                                                          \n",
       "Home & Office Phones                65988.4344                                \n",
       "Home Theater System                                                           \n",
       "Lamps                                                                         \n",
       "Laptops                                          1378128.279                  \n",
       "MP4&MP3                                                                       \n",
       "Microwaves                                                                    \n",
       "Monitors                                           429727.29                  \n",
       "Movie DVD                                                                     \n",
       "Printers, Scanners & Fax                           363023.85                  \n",
       "Projectors & Screens                              1684192.23                  \n",
       "Recording Pen                                                                 \n",
       "Refrigerators                                                                 \n",
       "Smart phones & PDAs                  507157.54                                \n",
       "Televisions                                                                   \n",
       "Touch Screen Phones                  378624.95                                \n",
       "VCD & DVD                                                                     \n",
       "Washers & Dryers                                                              \n",
       "Water Heaters                                                                 \n",
       "All                               1147620.3566  4826453.6124    258707.5747   \n",
       "\n",
       "Category                         Home Appliances  \\\n",
       "Subcategory                                        \n",
       "Air Conditioners                     510661.9291   \n",
       "Bluetooth Headphones                               \n",
       "Boxed Games                                        \n",
       "Camcorders                                         \n",
       "Cameras & Camcorders Accessories                   \n",
       "Car Video                                          \n",
       "Cell phones Accessories                            \n",
       "Coffee Machines                       695241.249   \n",
       "Computers Accessories                              \n",
       "Desktops                                           \n",
       "Digital Cameras                                    \n",
       "Digital SLR Cameras                                \n",
       "Download Games                                     \n",
       "Fans                                  92747.3446   \n",
       "Home & Office Phones                               \n",
       "Home Theater System                                \n",
       "Lamps                                799004.2559   \n",
       "Laptops                                            \n",
       "MP4&MP3                                            \n",
       "Microwaves                           551692.3217   \n",
       "Monitors                                           \n",
       "Movie DVD                                          \n",
       "Printers, Scanners & Fax                           \n",
       "Projectors & Screens                               \n",
       "Recording Pen                                      \n",
       "Refrigerators                       1347515.1567   \n",
       "Smart phones & PDAs                                \n",
       "Televisions                                        \n",
       "Touch Screen Phones                                \n",
       "VCD & DVD                                          \n",
       "Washers & Dryers                     2196637.719   \n",
       "Water Heaters                          713718.28   \n",
       "All                               6907218.256001   \n",
       "\n",
       "Category                         Music, Movies and Audio Books  TV and Video  \\\n",
       "Subcategory                                                                    \n",
       "Air Conditioners                                                               \n",
       "Bluetooth Headphones                                                           \n",
       "Boxed Games                                                                    \n",
       "Camcorders                                                                     \n",
       "Cameras & Camcorders Accessories                                               \n",
       "Car Video                                                          431782.77   \n",
       "Cell phones Accessories                                                        \n",
       "Coffee Machines                                                                \n",
       "Computers Accessories                                                          \n",
       "Desktops                                                                       \n",
       "Digital Cameras                                                                \n",
       "Digital SLR Cameras                                                            \n",
       "Download Games                                                                 \n",
       "Fans                                                                           \n",
       "Home & Office Phones                                                           \n",
       "Home Theater System                                              1091875.295   \n",
       "Lamps                                                                          \n",
       "Laptops                                                                        \n",
       "MP4&MP3                                                                        \n",
       "Microwaves                                                                     \n",
       "Monitors                                                                       \n",
       "Movie DVD                                          223781.9982                 \n",
       "Printers, Scanners & Fax                                                       \n",
       "Projectors & Screens                                                           \n",
       "Recording Pen                                                                  \n",
       "Refrigerators                                                                  \n",
       "Smart phones & PDAs                                                            \n",
       "Televisions                                                     1325476.8007   \n",
       "Touch Screen Phones                                                            \n",
       "VCD & DVD                                                         301408.289   \n",
       "Washers & Dryers                                                               \n",
       "Water Heaters                                                                  \n",
       "All                                                223781.9982  3150543.1547   \n",
       "\n",
       "Category                                    All  \n",
       "Subcategory                                      \n",
       "Air Conditioners                    510661.9291  \n",
       "Bluetooth Headphones                 87548.8094  \n",
       "Boxed Games                          45698.7421  \n",
       "Camcorders                           2265408.43  \n",
       "Cameras & Camcorders Accessories    572016.9015  \n",
       "Car Video                             431782.77  \n",
       "Cell phones Accessories             195849.4322  \n",
       "Coffee Machines                      695241.249  \n",
       "Computers Accessories               245031.5724  \n",
       "Desktops                             726350.391  \n",
       "Digital Cameras                      563010.643  \n",
       "Digital SLR Cameras                  1734070.69  \n",
       "Download Games                      213008.8326  \n",
       "Fans                                 92747.3446  \n",
       "Home & Office Phones                 65988.4344  \n",
       "Home Theater System                 1091875.295  \n",
       "Lamps                               799004.2559  \n",
       "Laptops                             1378128.279  \n",
       "MP4&MP3                             121870.4054  \n",
       "Microwaves                          551692.3217  \n",
       "Monitors                              429727.29  \n",
       "Movie DVD                           223781.9982  \n",
       "Printers, Scanners & Fax              363023.85  \n",
       "Projectors & Screens                 1684192.23  \n",
       "Recording Pen                         66141.932  \n",
       "Refrigerators                      1347515.1567  \n",
       "Smart phones & PDAs                   507157.54  \n",
       "Televisions                        1325476.8007  \n",
       "Touch Screen Phones                   378624.95  \n",
       "VCD & DVD                            301408.289  \n",
       "Washers & Dryers                    2196637.719  \n",
       "Water Heaters                         713718.28  \n",
       "All                               21924392.7639  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "salesbycategory"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6f873fe1",
   "metadata": {},
   "source": [
    "# Pivot table for sales by category by year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "20c7f659",
   "metadata": {},
   "outputs": [],
   "source": [
    "salesbyyear = pd.pivot_table(finaldata , columns= pd.DatetimeIndex(salestable['Order Date']).year , index=[\"Category\"] , values = 'Net Price' ,fill_value= \"\", aggfunc=np.sum )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "15272726",
   "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>Order Date</th>\n",
       "      <th>2007</th>\n",
       "      <th>2008</th>\n",
       "      <th>2009</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Category</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Audio</th>\n",
       "      <td>7.176167e+04</td>\n",
       "      <td>7.726131e+04</td>\n",
       "      <td>1.265382e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cameras and camcorders</th>\n",
       "      <td>2.330640e+06</td>\n",
       "      <td>1.560692e+06</td>\n",
       "      <td>1.243175e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cell phones</th>\n",
       "      <td>3.460199e+05</td>\n",
       "      <td>3.299709e+05</td>\n",
       "      <td>4.716296e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Computers</th>\n",
       "      <td>1.918387e+06</td>\n",
       "      <td>1.473197e+06</td>\n",
       "      <td>1.434870e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Games and Toys</th>\n",
       "      <td>6.403916e+04</td>\n",
       "      <td>7.645235e+04</td>\n",
       "      <td>1.182161e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Home Appliances</th>\n",
       "      <td>1.688502e+06</td>\n",
       "      <td>2.842665e+06</td>\n",
       "      <td>2.376051e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Music, Movies and Audio Books</th>\n",
       "      <td>6.235269e+04</td>\n",
       "      <td>8.570488e+04</td>\n",
       "      <td>7.572443e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TV and Video</th>\n",
       "      <td>1.623930e+06</td>\n",
       "      <td>6.664464e+05</td>\n",
       "      <td>8.601670e+05</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Order Date                             2007          2008          2009\n",
       "Category                                                               \n",
       "Audio                          7.176167e+04  7.726131e+04  1.265382e+05\n",
       "Cameras and camcorders         2.330640e+06  1.560692e+06  1.243175e+06\n",
       "Cell phones                    3.460199e+05  3.299709e+05  4.716296e+05\n",
       "Computers                      1.918387e+06  1.473197e+06  1.434870e+06\n",
       "Games and Toys                 6.403916e+04  7.645235e+04  1.182161e+05\n",
       "Home Appliances                1.688502e+06  2.842665e+06  2.376051e+06\n",
       "Music, Movies and Audio Books  6.235269e+04  8.570488e+04  7.572443e+04\n",
       "TV and Video                   1.623930e+06  6.664464e+05  8.601670e+05"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "salesbyyear"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "526a55fa",
   "metadata": {},
   "source": [
    "# Using iloc to slice data for 2017 from pivot table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "83659b09",
   "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 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>2007</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Category</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Audio</th>\n",
       "      <td>7.176167e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cameras and camcorders</th>\n",
       "      <td>2.330640e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cell phones</th>\n",
       "      <td>3.460199e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Computers</th>\n",
       "      <td>1.918387e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Games and Toys</th>\n",
       "      <td>6.403916e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Home Appliances</th>\n",
       "      <td>1.688502e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Music, Movies and Audio Books</th>\n",
       "      <td>6.235269e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TV and Video</th>\n",
       "      <td>1.623930e+06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       2007\n",
       "Category                                   \n",
       "Audio                          7.176167e+04\n",
       "Cameras and camcorders         2.330640e+06\n",
       "Cell phones                    3.460199e+05\n",
       "Computers                      1.918387e+06\n",
       "Games and Toys                 6.403916e+04\n",
       "Home Appliances                1.688502e+06\n",
       "Music, Movies and Audio Books  6.235269e+04\n",
       "TV and Video                   1.623930e+06"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data2017 = pd.DataFrame(salesbyyear.iloc[:,0])\n",
    "data2017"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "99e72d85",
   "metadata": {},
   "source": [
    "# Concatenate 2 Data Frames"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "8a53f490",
   "metadata": {},
   "outputs": [],
   "source": [
    "Concate = pd.concat([category , categoryupdate])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "dfe07779",
   "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>ProductCategoryKey</th>\n",
       "      <th>Category Code</th>\n",
       "      <th>Category</th>\n",
       "      <th>ProductCategoryKey1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>Audio</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.0</td>\n",
       "      <td>2</td>\n",
       "      <td>TV and Video</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3.0</td>\n",
       "      <td>3</td>\n",
       "      <td>Computers</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4.0</td>\n",
       "      <td>4</td>\n",
       "      <td>Cameras and camcorders</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5.0</td>\n",
       "      <td>5</td>\n",
       "      <td>Cell phones</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6.0</td>\n",
       "      <td>6</td>\n",
       "      <td>Music, Movies and Audio Books</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7.0</td>\n",
       "      <td>7</td>\n",
       "      <td>Games and Toys</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8.0</td>\n",
       "      <td>8</td>\n",
       "      <td>Home Appliances</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>Audio</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>TV and Video</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>Computers</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>Cameras and camcorders</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>5</td>\n",
       "      <td>Cell phones</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>NaN</td>\n",
       "      <td>6</td>\n",
       "      <td>Music, Movies and Audio Books</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>Games and Toys</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>NaN</td>\n",
       "      <td>8</td>\n",
       "      <td>Home Appliances</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ProductCategoryKey  Category Code                       Category  \\\n",
       "0                 1.0              1                          Audio   \n",
       "1                 2.0              2                   TV and Video   \n",
       "2                 3.0              3                      Computers   \n",
       "3                 4.0              4        Cameras and camcorders    \n",
       "4                 5.0              5                    Cell phones   \n",
       "5                 6.0              6  Music, Movies and Audio Books   \n",
       "6                 7.0              7                 Games and Toys   \n",
       "7                 8.0              8                Home Appliances   \n",
       "0                 NaN              1                          Audio   \n",
       "1                 NaN              2                   TV and Video   \n",
       "2                 NaN              3                      Computers   \n",
       "3                 NaN              4        Cameras and camcorders    \n",
       "4                 NaN              5                    Cell phones   \n",
       "5                 NaN              6  Music, Movies and Audio Books   \n",
       "6                 NaN              7                 Games and Toys   \n",
       "7                 NaN              8                Home Appliances   \n",
       "\n",
       "   ProductCategoryKey1  \n",
       "0                  NaN  \n",
       "1                  NaN  \n",
       "2                  NaN  \n",
       "3                  NaN  \n",
       "4                  NaN  \n",
       "5                  NaN  \n",
       "6                  NaN  \n",
       "7                  NaN  \n",
       "0                  1.0  \n",
       "1                  2.0  \n",
       "2                  3.0  \n",
       "3                  4.0  \n",
       "4                  5.0  \n",
       "5                  6.0  \n",
       "6                  7.0  \n",
       "7                  8.0  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Concate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "340bd257",
   "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>index</th>\n",
       "      <th>ProductCategoryKey</th>\n",
       "      <th>Category Code</th>\n",
       "      <th>Category</th>\n",
       "      <th>ProductCategoryKey1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>Audio</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2</td>\n",
       "      <td>TV and Video</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3</td>\n",
       "      <td>Computers</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4</td>\n",
       "      <td>Cameras and camcorders</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5</td>\n",
       "      <td>Cell phones</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6</td>\n",
       "      <td>Music, Movies and Audio Books</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>7.0</td>\n",
       "      <td>7</td>\n",
       "      <td>Games and Toys</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>8.0</td>\n",
       "      <td>8</td>\n",
       "      <td>Home Appliances</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>Audio</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>TV and Video</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>Computers</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>Cameras and camcorders</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5</td>\n",
       "      <td>Cell phones</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6</td>\n",
       "      <td>Music, Movies and Audio Books</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>Games and Toys</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8</td>\n",
       "      <td>Home Appliances</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    index  ProductCategoryKey  Category Code                       Category  \\\n",
       "0       0                 1.0              1                          Audio   \n",
       "1       1                 2.0              2                   TV and Video   \n",
       "2       2                 3.0              3                      Computers   \n",
       "3       3                 4.0              4        Cameras and camcorders    \n",
       "4       4                 5.0              5                    Cell phones   \n",
       "5       5                 6.0              6  Music, Movies and Audio Books   \n",
       "6       6                 7.0              7                 Games and Toys   \n",
       "7       7                 8.0              8                Home Appliances   \n",
       "8       0                 NaN              1                          Audio   \n",
       "9       1                 NaN              2                   TV and Video   \n",
       "10      2                 NaN              3                      Computers   \n",
       "11      3                 NaN              4        Cameras and camcorders    \n",
       "12      4                 NaN              5                    Cell phones   \n",
       "13      5                 NaN              6  Music, Movies and Audio Books   \n",
       "14      6                 NaN              7                 Games and Toys   \n",
       "15      7                 NaN              8                Home Appliances   \n",
       "\n",
       "    ProductCategoryKey1  \n",
       "0                   NaN  \n",
       "1                   NaN  \n",
       "2                   NaN  \n",
       "3                   NaN  \n",
       "4                   NaN  \n",
       "5                   NaN  \n",
       "6                   NaN  \n",
       "7                   NaN  \n",
       "8                   1.0  \n",
       "9                   2.0  \n",
       "10                  3.0  \n",
       "11                  4.0  \n",
       "12                  5.0  \n",
       "13                  6.0  \n",
       "14                  7.0  \n",
       "15                  8.0  "
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "concattable = Concate.reset_index()\n",
    "concattable"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "42b6754c",
   "metadata": {},
   "source": [
    "# Comparing results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "4075340e",
   "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>ProductCategoryKey</th>\n",
       "      <th>ProductCategoryKey1</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Category</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Audio</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cameras and camcorders</th>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cell phones</th>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Computers</th>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Games and Toys</th>\n",
       "      <td>7.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Home Appliances</th>\n",
       "      <td>8.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Music, Movies and Audio Books</th>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TV and Video</th>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               ProductCategoryKey  ProductCategoryKey1\n",
       "Category                                                              \n",
       "Audio                                         1.0                  1.0\n",
       "Cameras and camcorders                        4.0                  4.0\n",
       "Cell phones                                   5.0                  5.0\n",
       "Computers                                     3.0                  3.0\n",
       "Games and Toys                                7.0                  7.0\n",
       "Home Appliances                               8.0                  8.0\n",
       "Music, Movies and Audio Books                 6.0                  6.0\n",
       "TV and Video                                  2.0                  2.0\n",
       "All                                           0.0                  0.0"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Executing shutdown due to inactivity...\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "2021-06-10 15:42:28,649 - INFO     - Executing shutdown due to inactivity...\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Executing shutdown...\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "2021-06-10 15:42:49,167 - INFO     - Executing shutdown...\n"
     ]
    }
   ],
   "source": [
    "results = pd.pivot_table(concattable , index=\"Category\" , values = [\"ProductCategoryKey\" , \"ProductCategoryKey1\" ] , aggfunc=np.sum , margins=True)\n",
    "results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e369333d",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "47e0a127",
   "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.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}