Data Exploration.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "6d2fa581",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Data Exploration"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "b64c8de0",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import json\n",
    "import re\n",
    "import html\n",
    "import import_ipynb\n",
    "import pickle"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "10aff24a",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Amazon Data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2e5f024a",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "### Reading Amazon Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "79fe0742",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of columns: 19 | Number of records= 287051\n",
      "Columns in amazon dataset: ['category', 'tech1', 'description', 'fit', 'title', 'also_buy', 'tech2', 'brand', 'feature', 'rank', 'also_view', 'main_cat', 'similar_item', 'date', 'price', 'asin', 'imageURL', 'imageURLHighRes', 'details']\n"
     ]
    }
   ],
   "source": [
    "AMAZON_DATA_FILE_PATH = \"resources/meta_Grocery_and_Gourmet_Food.json\"\n",
    "data = []\n",
    "with open(AMAZON_DATA_FILE_PATH) as f:\n",
    "    for line in f:\n",
    "        data.append(json.loads(line.strip()))\n",
    "amazon_data = pd.DataFrame.from_dict(data)\n",
    "print(f\"Number of columns: {amazon_data.shape[1]} | Number of records= {amazon_data.shape[0]}\")\n",
    "print(f\"Columns in amazon dataset: {list(amazon_data.columns)}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4ca2f05e",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "### Data Overview"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "4fd5d99f",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 287051 entries, 0 to 287050\n",
      "Data columns (total 19 columns):\n",
      " #   Column           Non-Null Count   Dtype \n",
      "---  ------           --------------   ----- \n",
      " 0   category         287051 non-null  object\n",
      " 1   tech1            287051 non-null  object\n",
      " 2   description      287051 non-null  object\n",
      " 3   fit              287051 non-null  object\n",
      " 4   title            287051 non-null  object\n",
      " 5   also_buy         287051 non-null  object\n",
      " 6   tech2            287051 non-null  object\n",
      " 7   brand            287051 non-null  object\n",
      " 8   feature          287051 non-null  object\n",
      " 9   rank             287051 non-null  object\n",
      " 10  also_view        287051 non-null  object\n",
      " 11  main_cat         287051 non-null  object\n",
      " 12  similar_item     287051 non-null  object\n",
      " 13  date             287051 non-null  object\n",
      " 14  price            287051 non-null  object\n",
      " 15  asin             287051 non-null  object\n",
      " 16  imageURL         287051 non-null  object\n",
      " 17  imageURLHighRes  287051 non-null  object\n",
      " 18  details          287027 non-null  object\n",
      "dtypes: object(19)\n",
      "memory usage: 41.6+ MB\n"
     ]
    }
   ],
   "source": [
    "amazon_data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "dd938983",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "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>category</th>\n",
       "      <th>tech1</th>\n",
       "      <th>description</th>\n",
       "      <th>fit</th>\n",
       "      <th>title</th>\n",
       "      <th>also_buy</th>\n",
       "      <th>tech2</th>\n",
       "      <th>brand</th>\n",
       "      <th>feature</th>\n",
       "      <th>rank</th>\n",
       "      <th>also_view</th>\n",
       "      <th>main_cat</th>\n",
       "      <th>similar_item</th>\n",
       "      <th>date</th>\n",
       "      <th>price</th>\n",
       "      <th>asin</th>\n",
       "      <th>imageURL</th>\n",
       "      <th>imageURLHighRes</th>\n",
       "      <th>details</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>[Grocery &amp; Gourmet Food, Dairy, Cheese &amp; Eggs,...</td>\n",
       "      <td></td>\n",
       "      <td>[BEEMSTER GOUDA CHEESE AGED 18/24 MONTHS, Stat...</td>\n",
       "      <td></td>\n",
       "      <td>Beemster Gouda - Aged 18/24 Months - App. 1.5 Lbs</td>\n",
       "      <td>[]</td>\n",
       "      <td></td>\n",
       "      <td>Ariola Imports</td>\n",
       "      <td>[]</td>\n",
       "      <td>165,181 in Grocery &amp; Gourmet Food (</td>\n",
       "      <td>[B0000D9MYM, B0000D9MYL, B00ADHIGBA, B00H9OX59...</td>\n",
       "      <td>Grocery</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>$41.91</td>\n",
       "      <td>0681727810</td>\n",
       "      <td>[]</td>\n",
       "      <td>[]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>[Grocery &amp; Gourmet Food, Cooking &amp; Baking, Sug...</td>\n",
       "      <td></td>\n",
       "      <td>[Shipped from UK, please allow 10 to 21 busine...</td>\n",
       "      <td></td>\n",
       "      <td>Trim Healthy Mama Xylitol</td>\n",
       "      <td>[B01898YHXK, B01BCM6LAC, B00Q4OL47O, B00Q4OL5Q...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>[]</td>\n",
       "      <td>315,867 in Grocery &amp; Gourmet Food (</td>\n",
       "      <td>[]</td>\n",
       "      <td>Grocery</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0853347867</td>\n",
       "      <td>[https://images-na.ssl-images-amazon.com/image...</td>\n",
       "      <td>[https://images-na.ssl-images-amazon.com/image...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            category tech1  \\\n",
       "0  [Grocery & Gourmet Food, Dairy, Cheese & Eggs,...         \n",
       "1  [Grocery & Gourmet Food, Cooking & Baking, Sug...         \n",
       "\n",
       "                                         description fit  \\\n",
       "0  [BEEMSTER GOUDA CHEESE AGED 18/24 MONTHS, Stat...       \n",
       "1  [Shipped from UK, please allow 10 to 21 busine...       \n",
       "\n",
       "                                               title  \\\n",
       "0  Beemster Gouda - Aged 18/24 Months - App. 1.5 Lbs   \n",
       "1                          Trim Healthy Mama Xylitol   \n",
       "\n",
       "                                            also_buy tech2           brand  \\\n",
       "0                                                 []        Ariola Imports   \n",
       "1  [B01898YHXK, B01BCM6LAC, B00Q4OL47O, B00Q4OL5Q...                         \n",
       "\n",
       "  feature                                 rank  \\\n",
       "0      []  165,181 in Grocery & Gourmet Food (   \n",
       "1      []  315,867 in Grocery & Gourmet Food (   \n",
       "\n",
       "                                           also_view main_cat similar_item  \\\n",
       "0  [B0000D9MYM, B0000D9MYL, B00ADHIGBA, B00H9OX59...  Grocery                \n",
       "1                                                 []  Grocery                \n",
       "\n",
       "  date   price        asin                                           imageURL  \\\n",
       "0       $41.91  0681727810                                                 []   \n",
       "1               0853347867  [https://images-na.ssl-images-amazon.com/image...   \n",
       "\n",
       "                                     imageURLHighRes details  \n",
       "0                                                 []     NaN  \n",
       "1  [https://images-na.ssl-images-amazon.com/image...     NaN  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "amazon_data.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "fb195626",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "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>main_cat</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Grocery</td>\n",
       "      <td>261845</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Amazon Home</td>\n",
       "      <td>8953</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Health &amp; Personal Care</td>\n",
       "      <td>8543</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Toys &amp; Games</td>\n",
       "      <td>1934</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>All Beauty</td>\n",
       "      <td>1604</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    index  main_cat\n",
       "0                 Grocery    261845\n",
       "1             Amazon Home      8953\n",
       "2  Health & Personal Care      8543\n",
       "3            Toys & Games      1934\n",
       "4              All Beauty      1604"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "amazon_data[\"main_cat\"].value_counts().reset_index().head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "ff003e8c",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Column Name          | Pandas Data Type     | Primitive Type      \n",
      "------------------------------------------------------------\n",
      "category             | object               | ['list']            \n",
      "tech1                | object               | ['str']             \n",
      "description          | object               | ['list']            \n",
      "fit                  | object               | ['str']             \n",
      "title                | object               | ['str']             \n",
      "also_buy             | object               | ['list']            \n",
      "tech2                | object               | ['str']             \n",
      "brand                | object               | ['str']             \n",
      "feature              | object               | ['list']            \n",
      "rank                 | object               | ['str', 'list']     \n",
      "also_view            | object               | ['list']            \n",
      "main_cat             | object               | ['str']             \n",
      "similar_item         | object               | ['str']             \n",
      "date                 | object               | ['str']             \n",
      "price                | object               | ['str']             \n",
      "asin                 | object               | ['str']             \n",
      "imageURL             | object               | ['list']            \n",
      "imageURLHighRes      | object               | ['list']            \n",
      "details              | object               | ['dict', 'float']   \n"
     ]
    }
   ],
   "source": [
    "# Investigating primitive type of data\n",
    "print(f\"{'Column Name':<20} | {'Pandas Data Type':<20} | {'Primitive Type':<20}\")\n",
    "print(\"-\"*60)\n",
    "amazon_data.attrs = {}\n",
    "amazon_data.attrs[\"dtype\"] = {}\n",
    "for col in amazon_data.columns:\n",
    "    primitive_types = set([type(i) for i in amazon_data[col]])\n",
    "    print(f\"{col:<20} | {str(amazon_data[col].dtype):<20} | {str([t.__name__ for t in primitive_types]):<20}\")\n",
    "    amazon_data.attrs[\"dtype\"][col] = primitive_types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "7585113b",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [],
   "source": [
    "# Setting empty lists and string NaN\n",
    "for col in amazon_data.columns:\n",
    "    if len(amazon_data.attrs[\"dtype\"][col])>1:\n",
    "        continue\n",
    "    if list in amazon_data.attrs[\"dtype\"][col]:\n",
    "        amazon_data[col] = amazon_data[col].apply(lambda x: np.nan if len(x)==0 else x)\n",
    "    elif str in amazon_data.attrs[\"dtype\"][col]:\n",
    "        amazon_data[col] = amazon_data[col].apply(lambda x: np.nan if len(x.strip())==0 else x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "607f36f0",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "category                0\n",
       "tech1              286307\n",
       "description         35384\n",
       "fit                287047\n",
       "title                   3\n",
       "also_buy           203757\n",
       "tech2              287051\n",
       "brand               11419\n",
       "feature            270671\n",
       "rank                    0\n",
       "also_view          166113\n",
       "main_cat             1363\n",
       "similar_item       286795\n",
       "date               277389\n",
       "price              153193\n",
       "asin                    0\n",
       "imageURL           136901\n",
       "imageURLHighRes    136901\n",
       "details                24\n",
       "dtype: int64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "amazon_data.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "2503e224",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Unknown                  1780\n",
       "Black Tie Mercantile     1458\n",
       "Trader Joe's             1234\n",
       "McCormick                1041\n",
       "The Nutty Fruit House     998\n",
       "Name: brand, dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "amazon_data[\"brand\"].value_counts().head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "cd90d380",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Grocery & Gourmet Food, Beverages, Coffee, Tea & Cocoa, Tea, Tea Samplers]                                        7609\n",
       "[Grocery & Gourmet Food, Candy & Chocolate, Candy & Chocolate Bars]                                                7512\n",
       "[Grocery & Gourmet Food, Beverages, Coffee, Tea & Cocoa, Coffee, Single-Serve Capsules & Pods]                     6745\n",
       "[Grocery & Gourmet Food, Beverages, Coffee, Tea & Cocoa, Coffee, Ground Coffee]                                    5326\n",
       "[Grocery & Gourmet Food, Cooking & Baking, Frosting, Icing & Decorations, Cake & Cupcake Toppers, Cake Toppers]    4821\n",
       "Name: category, dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "amazon_data[\"category\"].value_counts().head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "44fa8534",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Sample of HTML encoded characters in column: 'title'\n",
      "   HTML Encoded Char |         Decoded Char\n",
      "--------------------------------------------------\n",
      "              &ldquo |               &ldquo\n",
      "              &aring |                    å\n",
      "               &ordf |                    ª\n",
      "             &oslash |                    ø\n",
      "                &eth |                    ð\n",
      "               TOTAL |                  103\n",
      "Sample of HTML encoded characters in column: 'description'\n",
      "   HTML Encoded Char |         Decoded Char\n",
      "--------------------------------------------------\n",
      "               &gold |                &gold\n",
      "                &ike |                 &ike\n",
      "               &mash |                &mash\n",
      "         &strawberry |          &strawberry\n",
      "                 &no |                  &no\n",
      "               TOTAL |                  302\n"
     ]
    }
   ],
   "source": [
    "for col in [\"title\", \"description\"]:\n",
    "    special_chars = amazon_data[col].apply(lambda x: set(re.findall(r'&\\w+', str(x))) if x else set()).aggregate({'Whitespace Characters': lambda x: set.union(*x)})[0]\n",
    "    print(f\"Sample of HTML encoded characters in column: '{col}'\")\n",
    "    print(f\"{'HTML Encoded Char':>20} | {'Decoded Char':>20}\")\n",
    "    print(\"-\"*50)\n",
    "    for ch in list(special_chars)[:5]:\n",
    "        print(f\"{ch.lower(): >20} | {html.unescape(ch.lower()): >20}\")\n",
    "    print(f\"{'TOTAL': >20} | {len(special_chars): >20}\")    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "18cfddee",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "HTML tags in column: 'description': {'<TR>', '<th>', '<br>', '<td>', '<span>', '<B>', '<colgroup>', '<EOL>', '<h4>', '<blockquote>', '<Br>', '<div>', '<P>', '<H2>', '<big>', '<h2>', '<LI>', '<TD>', '<xml>', '<Kafemio>', '<small>', '<Li>', '<sup>', '<b>', '<u>', '<BR>', '<lu>', '<tr>', '<em>', '<milk>', '<Strong>', '<style>', '<li>', '<ul>', '<STRONG>', '<EM>', '<title>', '<bR>', '<html>', '<DIV>', '<center>', '<UL>', '<U>', '<table>', '<I>', '<strong>', '<tbody>', '<h5>', '<sub>', '<h3>', '<head>', '<ol>', '<body>', '<h1>', '<>', '<i>', '<H4>', '<H5>'}\n"
     ]
    }
   ],
   "source": [
    "col=\"description\"\n",
    "special_chars = amazon_data[col].apply(lambda x: set(re.findall(r'<\\w*>', str(x))) if x else set()).aggregate({'Whitespace Characters': lambda x: set.union(*x)})[0]\n",
    "print(f\"HTML tags in column: '{col}': {special_chars}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "ca609570",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4     126693\n",
       "3      79167\n",
       "5      67752\n",
       "2      10992\n",
       "6       2395\n",
       "8         19\n",
       "9         15\n",
       "7          8\n",
       "10         7\n",
       "11         2\n",
       "13         1\n",
       "Name: category_count, dtype: int64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "category_count = amazon_data[\"category\"].apply(lambda categories: len(categories))\n",
    "category_count.name = \"category_count\"\n",
    "category_count.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "958c3e92",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Ontology Data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c5c7d763",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "### Reading Ontology Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "8881cd15",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [],
   "source": [
    "ONTOLOGY_DATA_PATH = \"resources/ontology_data/\"\n",
    "product_tags = pd.read_csv(ONTOLOGY_DATA_PATH + \"product_tags.csv\")\n",
    "preferences = pd.read_csv(ONTOLOGY_DATA_PATH + \"preferences.csv\")\n",
    "field_test_preferences = pd.read_csv(ONTOLOGY_DATA_PATH + \"field_test_preferences.csv\")\n",
    "preference_tags = pd.read_csv(ONTOLOGY_DATA_PATH + \"preference_tags.csv\")\n",
    "preference_to_tags = pd.read_csv(ONTOLOGY_DATA_PATH + \"preference_to_tags.csv\")\n",
    "associations = pd.read_csv(ONTOLOGY_DATA_PATH + \"associations.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "5b4c72d9",
   "metadata": {},
   "outputs": [],
   "source": [
    "summary = pd.DataFrame()\n",
    "summary[\"Total No. of Records\"] = [product_tags.shape[0], \n",
    "                    preferences.shape[0], \n",
    "                    field_test_preferences.shape[0], \n",
    "                    preference_tags.shape[0], \n",
    "                    preference_to_tags.shape[0],\n",
    "                    associations.shape[0]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "0bc126c8",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [],
   "source": [
    "# Filtering data present in the latest version (i.e, not removed from any version)\n",
    "product_tags = product_tags[product_tags[\"version removed\"]==-1]\n",
    "preferences = preferences[preferences[\"version removed\"]==-1]\n",
    "preference_tags = preference_tags[preference_tags[\"version removed\"]==-1]\n",
    "preference_to_tags = preference_to_tags[preference_to_tags[\"version removed\"]==-1]\n",
    "associations = associations[associations[\"version removed\"]==-1]\n",
    "\n",
    "# Dropping 'version removed' column\n",
    "product_tags.drop(\"version removed\", axis=1, inplace=True)\n",
    "preferences.drop(\"version removed\", axis=1, inplace=True)\n",
    "preference_tags.drop(\"version removed\", axis=1, inplace=True)\n",
    "preference_to_tags.drop(\"version removed\", axis=1, inplace=True)\n",
    "associations.drop(\"version removed\", axis=1, inplace=True)\n",
    "\n",
    "field_test_preferences = field_test_preferences.iloc[:,:-3] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "0f61ac6a",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [],
   "source": [
    "\n",
    "summary[\"No. of Records (Latest)\"] = [product_tags.shape[0], \n",
    "                    preferences.shape[0], \n",
    "                    field_test_preferences.shape[0], \n",
    "                    preference_tags.shape[0], \n",
    "                    preference_to_tags.shape[0],\n",
    "                    associations.shape[0]]\n",
    "summary[\"No. of Columns\"] = [product_tags.shape[1], \n",
    "                    preferences.shape[1], \n",
    "                    field_test_preferences.shape[1], \n",
    "                    preference_tags.shape[1], \n",
    "                    preference_to_tags.shape[1],\n",
    "                    associations.shape[1]]\n",
    "summary[\"Column Names\"] = [product_tags.columns.values, \n",
    "                    preferences.columns.values, \n",
    "                    field_test_preferences.columns.values, \n",
    "                    preference_tags.columns.values, \n",
    "                    preference_to_tags.columns.values,\n",
    "                    associations.columns.values]\n",
    "summary.index = [\"product_tags\", \n",
    "                    \"preferences\", \n",
    "                    \"field_test_preferences\", \n",
    "                    \"preference_tags\", \n",
    "                    \"preference_to_tags\",\n",
    "                    \"associations\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "36690ca1",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### Summary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "14532a0c",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "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>Total No. of Records</th>\n",
       "      <th>No. of Records (Latest)</th>\n",
       "      <th>No. of Columns</th>\n",
       "      <th>Column Names</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>product_tags</th>\n",
       "      <td>290</td>\n",
       "      <td>196</td>\n",
       "      <td>2</td>\n",
       "      <td>[product tag id, product tag]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>preferences</th>\n",
       "      <td>99</td>\n",
       "      <td>25</td>\n",
       "      <td>2</td>\n",
       "      <td>[preference id, preference]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>field_test_preferences</th>\n",
       "      <td>25</td>\n",
       "      <td>25</td>\n",
       "      <td>5</td>\n",
       "      <td>[preference category, preference id, preferenc...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>preference_tags</th>\n",
       "      <td>103</td>\n",
       "      <td>61</td>\n",
       "      <td>2</td>\n",
       "      <td>[preference tag id, preference tag]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>preference_to_tags</th>\n",
       "      <td>217</td>\n",
       "      <td>148</td>\n",
       "      <td>2</td>\n",
       "      <td>[preference id, preference tag id]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>associations</th>\n",
       "      <td>1203</td>\n",
       "      <td>795</td>\n",
       "      <td>3</td>\n",
       "      <td>[preference tag id, product tag id, score]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        Total No. of Records  No. of Records (Latest)  \\\n",
       "product_tags                             290                      196   \n",
       "preferences                               99                       25   \n",
       "field_test_preferences                    25                       25   \n",
       "preference_tags                          103                       61   \n",
       "preference_to_tags                       217                      148   \n",
       "associations                            1203                      795   \n",
       "\n",
       "                        No. of Columns  \\\n",
       "product_tags                         2   \n",
       "preferences                          2   \n",
       "field_test_preferences               5   \n",
       "preference_tags                      2   \n",
       "preference_to_tags                   2   \n",
       "associations                         3   \n",
       "\n",
       "                                                             Column Names  \n",
       "product_tags                                [product tag id, product tag]  \n",
       "preferences                                   [preference id, preference]  \n",
       "field_test_preferences  [preference category, preference id, preferenc...  \n",
       "preference_tags                       [preference tag id, preference tag]  \n",
       "preference_to_tags                     [preference id, preference tag id]  \n",
       "associations                   [preference tag id, product tag id, score]  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "summary"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Slideshow",
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}