tivo-ETL-pipeline.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "b8964a0c",
   "metadata": {},
   "source": [
    "# TiVo - ETL Pipeline - Preparing Data for Clustering\n",
    "\n",
    "**NOTE**: All cells that have Excercises in them are marked with `#EXERCISE-TODO-[NO OF EXERCISE]`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6f104e18",
   "metadata": {},
   "source": [
    "## Import General Packages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e34c0e96",
   "metadata": {},
   "outputs": [],
   "source": [
    "%pip install -r requirements.txt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "f6eebaf9",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8fda0b30",
   "metadata": {},
   "source": [
    "## E: Extract and explore the data  (EXTRACT part of ETL pipeline)\n",
    "*Note: In a true ETL pipeline, you will just extract the data not explore it :)*\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f7154ce9",
   "metadata": {},
   "source": [
    "### Read in from Excel file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1b8e64d8",
   "metadata": {},
   "outputs": [],
   "source": [
    "dtivo_master = pd.read_excel('./data/tivo_subset.xlsx',\n",
    "                             index_col=0)            #Read in the first column (ID) as the index\n",
    "dtivo_master.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "00eeb877",
   "metadata": {},
   "source": [
    "### Get a feel of the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "00a85ab0",
   "metadata": {},
   "outputs": [],
   "source": [
    "dtivo_master.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8b221c14",
   "metadata": {},
   "source": [
    "### Exploratory Analyses: explore possible bases for segmentation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cf8959f8",
   "metadata": {},
   "outputs": [],
   "source": [
    "dtivo_master.groupby('TechAdopt')['Fav_Feature'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b4e28960",
   "metadata": {},
   "source": [
    "*Q: Did we learn anything about the product feature that is of interest to early vs. late adopters?  Which ones will you market to the premium market segment?*"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2abaeee7",
   "metadata": {},
   "source": [
    "Ans:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bdc8a265",
   "metadata": {},
   "outputs": [],
   "source": [
    "#EXERCISE-TODO-01: Obtain grouped value counts for Purchase_Point\n",
    "#and write in commentary under it, what ordinal scale would\n",
    "#suitable?\n",
    "dtivo_master.groupby('TechAdopt')[__________________].value_counts()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6188dffe",
   "metadata": {},
   "source": [
    "## T: Data Transformations  (TRANSFORM part of ETL pipeline)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "c137e5ce",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Make a copy of the master DataFrame to apply\n",
    "#transformations to.  We may need the original master file\n",
    "#so this is a good practice to apply transformations to a copy\n",
    "dtivo = dtivo_master.copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e4bc985b",
   "metadata": {},
   "source": [
    "### Factorizations"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "46bc4bd4",
   "metadata": {},
   "source": [
    "#### Fav_Feature"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "32703d41",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Map the zipped dictionary from 'Fav_Feature' to 'Fav_Coded'\n",
    "dtivo['Fav_Coded'], fav_uniques = dtivo['Fav_Feature'].factorize()\n",
    "print(fav_uniques)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0ae9627b",
   "metadata": {},
   "outputs": [],
   "source": [
    "dtivo.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "41f7e1c0",
   "metadata": {},
   "source": [
    "#### Education\n",
    "Sometimes the ordering of the numbers will matter, for example, in Education, we want to preserve our own order - especially when we see the results that summarize clusters.  So we \"Zip\" the categories with our own factors."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "77add21b",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "ed_types = dtivo['Education'].unique()\n",
    "print(ed_types)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e281e1fd",
   "metadata": {},
   "outputs": [],
   "source": [
    "dtivo['Education'].factorize(sort=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6beac181",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "ed_levels = [1,2,4,3]  #order numbers matched against ed_types list\n",
    "dict_edtypes = dict(zip(ed_types,ed_levels))\n",
    "print(dict_edtypes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4bdde3ae",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "dtivo['Ed_Coded'] = dtivo['Education'].map(dict_edtypes)\n",
    "dtivo.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6399480a",
   "metadata": {},
   "source": [
    "#### PurchasePoint "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "718a786b",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "#EXERCISE-TODO-02: Decide whether you would like to \n",
    "#code 'PurchasePoint' using simply factorization (as we showed in Favorite Feature) \n",
    "# OR by mapping your own 'zipped' dictionary.\n",
    "#HINT: CONSIDER YOUR FINDINGS FROM TODO-01\n",
    "# WHICHEVER WAY YOU, CHOSE SAVE THE RESULT\n",
    "\n",
    "\n",
    "#?\n",
    "#?\n",
    "#?\n",
    "dtivo[\"PPoint_Coded\"] = dtivo['Education']._____________________________\n",
    "dtivo.head(10)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "93d56e6e",
   "metadata": {},
   "source": [
    "#### Factorizing a binary - what to label the result?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c5bec675",
   "metadata": {},
   "outputs": [],
   "source": [
    "dtivo['Gender'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e21115d9",
   "metadata": {},
   "source": [
    "When factorizing a binary variable, you need to anticipate which category gets zero and which gets one.  Factorize offers to first sort the 'unique' categories.  For example, our dataset only has two genders (male and female) so this is a binary variable.  If we apply sort, we can anticipate that as 'F' comes before 'M', females will get `0` and males will get assigned `1`.  In this case the Series we get is Male as males have `1`.\n",
    "\n",
    " Without this, what ever the first category appearing in the first row is will get 0 and the next category will get 1 (so given the above data, male would get `0` without sort)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fbb78a2f",
   "metadata": {},
   "outputs": [],
   "source": [
    "dtivo['Male'], gender_uniques = dtivo['Gender'].factorize(sort=True)\n",
    "print(gender_uniques)\n",
    "dtivo.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bb9f6638",
   "metadata": {},
   "source": [
    "### Rows to keep for clustering\n",
    "We are only interested at launch in segments *within* early adopters => filter out late adopters.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "192ce373",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#Assuming we have done some segment targeting already - like focus on Early adopters only.\n",
    "dtivo_early = dtivo[dtivo['TechAdopt']=='early'].copy()\n",
    "len(dtivo_early)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "46594f96",
   "metadata": {},
   "outputs": [],
   "source": [
    "outlierIDs = [441, 923]\n",
    "#WE CAN DROP ANY CASES THAT WE THINK ARE OUTLIERS AND WILL DISTORT OUR SEGMENTS\n",
    "#EXERCISE-TODO-Nth: RETURN TO THIS AFTER RUNNING THE CLUSTER ANALYSIS\n",
    "dtivo_early.drop(index=outlierIDs, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f7e0eccc",
   "metadata": {},
   "outputs": [],
   "source": [
    "len(dtivo_early)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "68b9955d",
   "metadata": {},
   "source": [
    "### Columns to keep for clustering"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7ac02270",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "colsAll = list(dtivo_early.columns)\n",
    "colsAll"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8dee45b9",
   "metadata": {},
   "source": [
    "The word 'descriptors' signifies that these are the chosen variables that we believe will truly help us predict our segmentation.  Descriptors chosen should be those that we will know about the customer before the marketing action takes place.  For example, before selling our product, will we know their purchase point?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3fb0ee28",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "colsToDrop = ['Education',      #factorized, only keeping its coded form\n",
    "              'PurchasePoint',  #factorized, only keeping its coded form\n",
    "              'Tvhours',        #Dropping to keep it simple\n",
    "              'TechAdopt',      #Don't need after filtering out TechAdopter==late.\n",
    "              'Fav_Feature',    #factorized, only keeping coded form\n",
    "              'Gender',         #already have 'Male' after factorization\n",
    "              'Fav_Coded',      #Dropping to keep it simple\n",
    "#              'PPoint_Coded',   #Dropping to keep it simple in first run - uncomment after TODOS\n",
    "                                #EXERCISE-TODO-TAKEHOME - try doing this again but with some of the variables we dropped for simplicity\n",
    "             ]\n",
    "\n",
    "dtivo_descriptors = dtivo_early.drop(columns=colsToDrop)\n",
    "colsKept = list(dtivo_descriptors.columns)\n",
    "print(colsKept)\n",
    "dtivo_descriptors.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3841daa6",
   "metadata": {},
   "source": [
    "### Normalize using Standardization"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "013656d1",
   "metadata": {},
   "source": [
    "If Clustering has to give equal weight to each field (or 'feature' in ML terms), then<br>\n",
    "we must make sure they are on the same scale (i.e. **Normalize** the fields).\n",
    "\n",
    "If we are interested in how our segments vary from the average (e.g. <br>\n",
    "above average income, highly below average age, etc.), then we use<br>\n",
    "**Standardization** for normalization.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "03917ebc",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#IMPORT PACKAGE FOR STANDARDIZATION\n",
    "from sklearn import preprocessing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d471e44f",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#STANDARDIZED SCALING\n",
    "dtivo_std = pd.DataFrame(preprocessing.scale(dtivo_descriptors),\n",
    "                         columns=colsKept,                 #TODO: REVIEW in light of any changes to COLUMNS DROPPED\n",
    "                         index=dtivo_descriptors.index\n",
    "                        )\n",
    "\n",
    "#only for checking if done\n",
    "dtivo_std.describe().round(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e4dfa821",
   "metadata": {},
   "outputs": [],
   "source": [
    "dtivo_std.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "660be91a",
   "metadata": {},
   "source": [
    "## L: Loading the transformed data (LOAD part of ETL pipeline)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "117c2703",
   "metadata": {},
   "outputs": [],
   "source": [
    "dtivo_descriptors.to_csv(\"data/dtivo_descriptors.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "3e722fa6",
   "metadata": {},
   "outputs": [],
   "source": [
    "dtivo_std.to_csv(\"data/dtivo_standardized.csv\")"
   ]
  }
 ],
 "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.12.1"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": true,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "372px"
   },
   "toc_section_display": true,
   "toc_window_display": false
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}