process_dunnhumby.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Process `dunnhumby`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We downsize the `dunnhumby` dataset to only those associated with store ids `367`, `406`, `356` in order to fit Google Sheets."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## `transaction_data`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "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>household_key</th>\n",
       "      <th>BASKET_ID</th>\n",
       "      <th>DAY</th>\n",
       "      <th>PRODUCT_ID</th>\n",
       "      <th>QUANTITY</th>\n",
       "      <th>SALES_VALUE</th>\n",
       "      <th>STORE_ID</th>\n",
       "      <th>RETAIL_DISC</th>\n",
       "      <th>TRANS_TIME</th>\n",
       "      <th>WEEK_NO</th>\n",
       "      <th>COUPON_DISC</th>\n",
       "      <th>COUPON_MATCH_DISC</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2375</td>\n",
       "      <td>26984851472</td>\n",
       "      <td>1</td>\n",
       "      <td>1004906</td>\n",
       "      <td>1</td>\n",
       "      <td>1.39</td>\n",
       "      <td>364</td>\n",
       "      <td>-0.60</td>\n",
       "      <td>1631</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2375</td>\n",
       "      <td>26984851472</td>\n",
       "      <td>1</td>\n",
       "      <td>1033142</td>\n",
       "      <td>1</td>\n",
       "      <td>0.82</td>\n",
       "      <td>364</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1631</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2375</td>\n",
       "      <td>26984851472</td>\n",
       "      <td>1</td>\n",
       "      <td>1036325</td>\n",
       "      <td>1</td>\n",
       "      <td>0.99</td>\n",
       "      <td>364</td>\n",
       "      <td>-0.30</td>\n",
       "      <td>1631</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2375</td>\n",
       "      <td>26984851472</td>\n",
       "      <td>1</td>\n",
       "      <td>1082185</td>\n",
       "      <td>1</td>\n",
       "      <td>1.21</td>\n",
       "      <td>364</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1631</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2375</td>\n",
       "      <td>26984851472</td>\n",
       "      <td>1</td>\n",
       "      <td>8160430</td>\n",
       "      <td>1</td>\n",
       "      <td>1.50</td>\n",
       "      <td>364</td>\n",
       "      <td>-0.39</td>\n",
       "      <td>1631</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2595727</th>\n",
       "      <td>1598</td>\n",
       "      <td>42305362535</td>\n",
       "      <td>711</td>\n",
       "      <td>92130</td>\n",
       "      <td>1</td>\n",
       "      <td>0.99</td>\n",
       "      <td>3228</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1520</td>\n",
       "      <td>102</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2595728</th>\n",
       "      <td>1598</td>\n",
       "      <td>42305362535</td>\n",
       "      <td>711</td>\n",
       "      <td>114102</td>\n",
       "      <td>1</td>\n",
       "      <td>8.89</td>\n",
       "      <td>3228</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1520</td>\n",
       "      <td>102</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2595729</th>\n",
       "      <td>1598</td>\n",
       "      <td>42305362535</td>\n",
       "      <td>711</td>\n",
       "      <td>133449</td>\n",
       "      <td>1</td>\n",
       "      <td>6.99</td>\n",
       "      <td>3228</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1520</td>\n",
       "      <td>102</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2595730</th>\n",
       "      <td>1598</td>\n",
       "      <td>42305362535</td>\n",
       "      <td>711</td>\n",
       "      <td>6923644</td>\n",
       "      <td>1</td>\n",
       "      <td>4.50</td>\n",
       "      <td>3228</td>\n",
       "      <td>-0.49</td>\n",
       "      <td>1520</td>\n",
       "      <td>102</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2595731</th>\n",
       "      <td>1598</td>\n",
       "      <td>42305362535</td>\n",
       "      <td>711</td>\n",
       "      <td>14055192</td>\n",
       "      <td>1</td>\n",
       "      <td>6.99</td>\n",
       "      <td>3228</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1520</td>\n",
       "      <td>102</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2595732 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         household_key    BASKET_ID  DAY  PRODUCT_ID  QUANTITY  SALES_VALUE  \\\n",
       "0                 2375  26984851472    1     1004906         1         1.39   \n",
       "1                 2375  26984851472    1     1033142         1         0.82   \n",
       "2                 2375  26984851472    1     1036325         1         0.99   \n",
       "3                 2375  26984851472    1     1082185         1         1.21   \n",
       "4                 2375  26984851472    1     8160430         1         1.50   \n",
       "...                ...          ...  ...         ...       ...          ...   \n",
       "2595727           1598  42305362535  711       92130         1         0.99   \n",
       "2595728           1598  42305362535  711      114102         1         8.89   \n",
       "2595729           1598  42305362535  711      133449         1         6.99   \n",
       "2595730           1598  42305362535  711     6923644         1         4.50   \n",
       "2595731           1598  42305362535  711    14055192         1         6.99   \n",
       "\n",
       "         STORE_ID  RETAIL_DISC  TRANS_TIME  WEEK_NO  COUPON_DISC  \\\n",
       "0             364        -0.60        1631        1          0.0   \n",
       "1             364         0.00        1631        1          0.0   \n",
       "2             364        -0.30        1631        1          0.0   \n",
       "3             364         0.00        1631        1          0.0   \n",
       "4             364        -0.39        1631        1          0.0   \n",
       "...           ...          ...         ...      ...          ...   \n",
       "2595727      3228         0.00        1520      102          0.0   \n",
       "2595728      3228         0.00        1520      102          0.0   \n",
       "2595729      3228         0.00        1520      102          0.0   \n",
       "2595730      3228        -0.49        1520      102          0.0   \n",
       "2595731      3228         0.00        1520      102          0.0   \n",
       "\n",
       "         COUPON_MATCH_DISC  \n",
       "0                      0.0  \n",
       "1                      0.0  \n",
       "2                      0.0  \n",
       "3                      0.0  \n",
       "4                      0.0  \n",
       "...                    ...  \n",
       "2595727                0.0  \n",
       "2595728                0.0  \n",
       "2595729                0.0  \n",
       "2595730                0.0  \n",
       "2595731                0.0  \n",
       "\n",
       "[2595732 rows x 12 columns]"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transaction_data = pd.read_csv('journey/transaction_data.csv')\n",
    "transaction_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "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>STORE_ID</th>\n",
       "      <th>sum</th>\n",
       "      <th>len</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>118</th>\n",
       "      <td>367</td>\n",
       "      <td>267614.34</td>\n",
       "      <td>75573.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>139</th>\n",
       "      <td>406</td>\n",
       "      <td>216781.77</td>\n",
       "      <td>59331.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108</th>\n",
       "      <td>356</td>\n",
       "      <td>137588.68</td>\n",
       "      <td>49332.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127</th>\n",
       "      <td>381</td>\n",
       "      <td>132488.15</td>\n",
       "      <td>45268.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>292</td>\n",
       "      <td>130005.18</td>\n",
       "      <td>45240.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>425</th>\n",
       "      <td>3024</td>\n",
       "      <td>4.29</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>445</th>\n",
       "      <td>3092</td>\n",
       "      <td>6.99</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>456</th>\n",
       "      <td>3134</td>\n",
       "      <td>12.99</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>502</th>\n",
       "      <td>3294</td>\n",
       "      <td>6.99</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>227</td>\n",
       "      <td>3.29</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>582 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     STORE_ID        sum      len\n",
       "118       367  267614.34  75573.0\n",
       "139       406  216781.77  59331.0\n",
       "108       356  137588.68  49332.0\n",
       "127       381  132488.15  45268.0\n",
       "67        292  130005.18  45240.0\n",
       "..        ...        ...      ...\n",
       "425      3024       4.29      1.0\n",
       "445      3092       6.99      1.0\n",
       "456      3134      12.99      1.0\n",
       "502      3294       6.99      1.0\n",
       "54        227       3.29      1.0\n",
       "\n",
       "[582 rows x 3 columns]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transaction_data.groupby(['STORE_ID']).SALES_VALUE.agg([sum,len]).reset_index().sort_values('len',ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(184236, 12)"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "store_ids = [367,406,356]\n",
    "transaction_data = transaction_data[transaction_data.STORE_ID.isin(store_ids)].reset_index(drop=True)\n",
    "transaction_data.to_csv('journey_mini/transaction_data.csv',index=False)\n",
    "transaction_data.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## `hh_demographic`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(801, 8)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(191, 8)"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hh_demographic = pd.read_csv('journey/hh_demographic.csv')\n",
    "print(hh_demographic.shape)\n",
    "hh_demographic = hh_demographic[hh_demographic.household_key.isin(list(transaction_data.household_key.unique()))]\n",
    "hh_demographic.to_csv('journey_mini/hh_demographic.csv',index=False)\n",
    "hh_demographic.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "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>AGE_DESC</th>\n",
       "      <th>MARITAL_STATUS_CODE</th>\n",
       "      <th>INCOME_DESC</th>\n",
       "      <th>HOMEOWNER_DESC</th>\n",
       "      <th>HH_COMP_DESC</th>\n",
       "      <th>HOUSEHOLD_SIZE_DESC</th>\n",
       "      <th>KID_CATEGORY_DESC</th>\n",
       "      <th>household_key</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>65+</td>\n",
       "      <td>A</td>\n",
       "      <td>35-49K</td>\n",
       "      <td>Homeowner</td>\n",
       "      <td>2 Adults No Kids</td>\n",
       "      <td>2</td>\n",
       "      <td>None/Unknown</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>45-54</td>\n",
       "      <td>A</td>\n",
       "      <td>50-74K</td>\n",
       "      <td>Homeowner</td>\n",
       "      <td>2 Adults No Kids</td>\n",
       "      <td>2</td>\n",
       "      <td>None/Unknown</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>25-34</td>\n",
       "      <td>U</td>\n",
       "      <td>25-34K</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>2 Adults Kids</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>25-34</td>\n",
       "      <td>U</td>\n",
       "      <td>75-99K</td>\n",
       "      <td>Homeowner</td>\n",
       "      <td>2 Adults Kids</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>45-54</td>\n",
       "      <td>B</td>\n",
       "      <td>50-74K</td>\n",
       "      <td>Homeowner</td>\n",
       "      <td>Single Female</td>\n",
       "      <td>1</td>\n",
       "      <td>None/Unknown</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>796</th>\n",
       "      <td>35-44</td>\n",
       "      <td>U</td>\n",
       "      <td>50-74K</td>\n",
       "      <td>Homeowner</td>\n",
       "      <td>2 Adults No Kids</td>\n",
       "      <td>2</td>\n",
       "      <td>None/Unknown</td>\n",
       "      <td>2494</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>797</th>\n",
       "      <td>45-54</td>\n",
       "      <td>A</td>\n",
       "      <td>75-99K</td>\n",
       "      <td>Homeowner</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2496</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>798</th>\n",
       "      <td>45-54</td>\n",
       "      <td>U</td>\n",
       "      <td>35-49K</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Single Male</td>\n",
       "      <td>1</td>\n",
       "      <td>None/Unknown</td>\n",
       "      <td>2497</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>799</th>\n",
       "      <td>25-34</td>\n",
       "      <td>U</td>\n",
       "      <td>50-74K</td>\n",
       "      <td>Homeowner</td>\n",
       "      <td>2 Adults No Kids</td>\n",
       "      <td>2</td>\n",
       "      <td>None/Unknown</td>\n",
       "      <td>2498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>800</th>\n",
       "      <td>25-34</td>\n",
       "      <td>U</td>\n",
       "      <td>Under 15K</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>2 Adults Kids</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2499</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>801 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    AGE_DESC MARITAL_STATUS_CODE INCOME_DESC HOMEOWNER_DESC      HH_COMP_DESC  \\\n",
       "0        65+                   A      35-49K      Homeowner  2 Adults No Kids   \n",
       "1      45-54                   A      50-74K      Homeowner  2 Adults No Kids   \n",
       "2      25-34                   U      25-34K        Unknown     2 Adults Kids   \n",
       "3      25-34                   U      75-99K      Homeowner     2 Adults Kids   \n",
       "4      45-54                   B      50-74K      Homeowner     Single Female   \n",
       "..       ...                 ...         ...            ...               ...   \n",
       "796    35-44                   U      50-74K      Homeowner  2 Adults No Kids   \n",
       "797    45-54                   A      75-99K      Homeowner           Unknown   \n",
       "798    45-54                   U      35-49K        Unknown       Single Male   \n",
       "799    25-34                   U      50-74K      Homeowner  2 Adults No Kids   \n",
       "800    25-34                   U   Under 15K        Unknown     2 Adults Kids   \n",
       "\n",
       "    HOUSEHOLD_SIZE_DESC KID_CATEGORY_DESC  household_key  \n",
       "0                     2      None/Unknown              1  \n",
       "1                     2      None/Unknown              7  \n",
       "2                     3                 1              8  \n",
       "3                     4                 2             13  \n",
       "4                     1      None/Unknown             16  \n",
       "..                  ...               ...            ...  \n",
       "796                   2      None/Unknown           2494  \n",
       "797                   3                 1           2496  \n",
       "798                   1      None/Unknown           2497  \n",
       "799                   2      None/Unknown           2498  \n",
       "800                   3                 1           2499  \n",
       "\n",
       "[801 rows x 8 columns]"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hh_demographic = pd.read_csv('journey/hh_demographic.csv')\n",
    "hh_demographic.sort_values('household_key')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## `product`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>PRODUCT_ID</th>\n",
       "      <th>MANUFACTURER</th>\n",
       "      <th>DEPARTMENT</th>\n",
       "      <th>BRAND</th>\n",
       "      <th>COMMODITY_DESC</th>\n",
       "      <th>SUB_COMMODITY_DESC</th>\n",
       "      <th>CURR_SIZE_OF_PRODUCT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>25671</td>\n",
       "      <td>2</td>\n",
       "      <td>GROCERY</td>\n",
       "      <td>National</td>\n",
       "      <td>FRZN ICE</td>\n",
       "      <td>ICE - CRUSHED/CUBED</td>\n",
       "      <td>22 LB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>26081</td>\n",
       "      <td>2</td>\n",
       "      <td>MISC. TRANS.</td>\n",
       "      <td>National</td>\n",
       "      <td>NO COMMODITY DESCRIPTION</td>\n",
       "      <td>NO SUBCOMMODITY DESCRIPTION</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>26093</td>\n",
       "      <td>69</td>\n",
       "      <td>PASTRY</td>\n",
       "      <td>Private</td>\n",
       "      <td>BREAD</td>\n",
       "      <td>BREAD:ITALIAN/FRENCH</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>26190</td>\n",
       "      <td>69</td>\n",
       "      <td>GROCERY</td>\n",
       "      <td>Private</td>\n",
       "      <td>FRUIT - SHELF STABLE</td>\n",
       "      <td>APPLE SAUCE</td>\n",
       "      <td>50 OZ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>26355</td>\n",
       "      <td>69</td>\n",
       "      <td>GROCERY</td>\n",
       "      <td>Private</td>\n",
       "      <td>COOKIES/CONES</td>\n",
       "      <td>SPECIALTY COOKIES</td>\n",
       "      <td>14 OZ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92348</th>\n",
       "      <td>18293142</td>\n",
       "      <td>6384</td>\n",
       "      <td>DRUG GM</td>\n",
       "      <td>National</td>\n",
       "      <td>BOOKSTORE</td>\n",
       "      <td>PAPERBACK BOOKS</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92349</th>\n",
       "      <td>18293439</td>\n",
       "      <td>6393</td>\n",
       "      <td>DRUG GM</td>\n",
       "      <td>National</td>\n",
       "      <td>BOOKSTORE</td>\n",
       "      <td>CHILDRENS LOW END</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92350</th>\n",
       "      <td>18293696</td>\n",
       "      <td>6406</td>\n",
       "      <td>DRUG GM</td>\n",
       "      <td>National</td>\n",
       "      <td>BOOKSTORE</td>\n",
       "      <td>PAPERBACK BEST SELLER</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92351</th>\n",
       "      <td>18294080</td>\n",
       "      <td>6442</td>\n",
       "      <td>DRUG GM</td>\n",
       "      <td>National</td>\n",
       "      <td>BOOKSTORE</td>\n",
       "      <td>PAPERBACK BOOKS</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92352</th>\n",
       "      <td>18316298</td>\n",
       "      <td>764</td>\n",
       "      <td>GROCERY</td>\n",
       "      <td>National</td>\n",
       "      <td>PAPER TOWELS</td>\n",
       "      <td>PAPER TOWELS &amp; HOLDERS</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>92353 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       PRODUCT_ID  MANUFACTURER    DEPARTMENT     BRAND  \\\n",
       "0           25671             2       GROCERY  National   \n",
       "1           26081             2  MISC. TRANS.  National   \n",
       "2           26093            69        PASTRY   Private   \n",
       "3           26190            69       GROCERY   Private   \n",
       "4           26355            69       GROCERY   Private   \n",
       "...           ...           ...           ...       ...   \n",
       "92348    18293142          6384       DRUG GM  National   \n",
       "92349    18293439          6393       DRUG GM  National   \n",
       "92350    18293696          6406       DRUG GM  National   \n",
       "92351    18294080          6442       DRUG GM  National   \n",
       "92352    18316298           764       GROCERY  National   \n",
       "\n",
       "                 COMMODITY_DESC           SUB_COMMODITY_DESC  \\\n",
       "0                      FRZN ICE          ICE - CRUSHED/CUBED   \n",
       "1      NO COMMODITY DESCRIPTION  NO SUBCOMMODITY DESCRIPTION   \n",
       "2                         BREAD         BREAD:ITALIAN/FRENCH   \n",
       "3          FRUIT - SHELF STABLE                  APPLE SAUCE   \n",
       "4                 COOKIES/CONES            SPECIALTY COOKIES   \n",
       "...                         ...                          ...   \n",
       "92348                 BOOKSTORE              PAPERBACK BOOKS   \n",
       "92349                 BOOKSTORE            CHILDRENS LOW END   \n",
       "92350                 BOOKSTORE        PAPERBACK BEST SELLER   \n",
       "92351                 BOOKSTORE              PAPERBACK BOOKS   \n",
       "92352              PAPER TOWELS       PAPER TOWELS & HOLDERS   \n",
       "\n",
       "      CURR_SIZE_OF_PRODUCT  \n",
       "0                    22 LB  \n",
       "1                           \n",
       "2                           \n",
       "3                    50 OZ  \n",
       "4                    14 OZ  \n",
       "...                    ...  \n",
       "92348                       \n",
       "92349                       \n",
       "92350                       \n",
       "92351                       \n",
       "92352                       \n",
       "\n",
       "[92353 rows x 7 columns]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "product"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(92353, 7)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(29667, 7)"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "product = pd.read_csv('journey/product.csv')\n",
    "print(product.shape)\n",
    "product = product[product.PRODUCT_ID.isin(list(transaction_data.PRODUCT_ID))]\n",
    "product.to_csv('journey_mini/product.csv',index=False)\n",
    "product.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## `campaign_table` (merged with `campaign_desc`)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "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>DESCRIPTION</th>\n",
       "      <th>household_key</th>\n",
       "      <th>CAMPAIGN</th>\n",
       "      <th>START_DAY</th>\n",
       "      <th>END_DAY</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>TypeA</td>\n",
       "      <td>27</td>\n",
       "      <td>26</td>\n",
       "      <td>224</td>\n",
       "      <td>264</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>TypeA</td>\n",
       "      <td>208</td>\n",
       "      <td>26</td>\n",
       "      <td>224</td>\n",
       "      <td>264</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TypeA</td>\n",
       "      <td>131</td>\n",
       "      <td>26</td>\n",
       "      <td>224</td>\n",
       "      <td>264</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>TypeA</td>\n",
       "      <td>2482</td>\n",
       "      <td>26</td>\n",
       "      <td>224</td>\n",
       "      <td>264</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>TypeA</td>\n",
       "      <td>2478</td>\n",
       "      <td>26</td>\n",
       "      <td>224</td>\n",
       "      <td>264</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1761</th>\n",
       "      <td>TypeC</td>\n",
       "      <td>576</td>\n",
       "      <td>6</td>\n",
       "      <td>393</td>\n",
       "      <td>425</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1762</th>\n",
       "      <td>TypeC</td>\n",
       "      <td>489</td>\n",
       "      <td>6</td>\n",
       "      <td>393</td>\n",
       "      <td>425</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1763</th>\n",
       "      <td>TypeC</td>\n",
       "      <td>143</td>\n",
       "      <td>15</td>\n",
       "      <td>547</td>\n",
       "      <td>708</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1764</th>\n",
       "      <td>TypeC</td>\n",
       "      <td>543</td>\n",
       "      <td>15</td>\n",
       "      <td>547</td>\n",
       "      <td>708</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1765</th>\n",
       "      <td>TypeC</td>\n",
       "      <td>2341</td>\n",
       "      <td>15</td>\n",
       "      <td>547</td>\n",
       "      <td>708</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1766 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     DESCRIPTION  household_key  CAMPAIGN  START_DAY  END_DAY\n",
       "0          TypeA             27        26        224      264\n",
       "1          TypeA            208        26        224      264\n",
       "2          TypeA            131        26        224      264\n",
       "3          TypeA           2482        26        224      264\n",
       "4          TypeA           2478        26        224      264\n",
       "...          ...            ...       ...        ...      ...\n",
       "1761       TypeC            576         6        393      425\n",
       "1762       TypeC            489         6        393      425\n",
       "1763       TypeC            143        15        547      708\n",
       "1764       TypeC            543        15        547      708\n",
       "1765       TypeC           2341        15        547      708\n",
       "\n",
       "[1766 rows x 5 columns]"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "campaign_table = pd.read_csv('journey/campaign_table.csv')\n",
    "campaign_table = campaign_table[campaign_table.household_key.isin(list(transaction_data.household_key))]\n",
    "campaign_table = campaign_table.merge(campaign_desc)\n",
    "campaign_table.to_csv('journey_mini/campaign_table.csv',index=False)\n",
    "campaign_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "hide_input": false,
  "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}