Fetch Rewards Coding Exercise - Data Analyst.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Loading all the required libraries "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import glob\n",
    "import os\n",
    "from ast import literal_eval\n",
    "from datetime import datetime\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "\n",
    "from sqlalchemy import create_engine\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Automating some of the things by loading all the path for the files and creating df names from them followed by loading the actually data to the respective dataframes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['brands.json.gz', 'receipts.json.gz', 'users.json.gz']"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filepaths = [i for i in os.listdir(\".\") if i.endswith('.gz')]\n",
    "filepaths"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['brands_df', 'receipts_df', 'users_df']"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_to_create = [i.replace('.json.gz','_df') for i in filepaths]\n",
    "df_to_create"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i,j in zip(df_to_create,filepaths):\n",
    "    exec('{0} = pd.read_json(\"{1}\",compression=\"infer\",lines=True)'.format(i,j))\n",
    "     "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Brand_DF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>_id</th>\n",
       "      <th>barcode</th>\n",
       "      <th>category</th>\n",
       "      <th>categoryCode</th>\n",
       "      <th>cpg</th>\n",
       "      <th>name</th>\n",
       "      <th>topBrand</th>\n",
       "      <th>brandCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>{'$oid': '601ac115be37ce2ead437551'}</td>\n",
       "      <td>511111019862</td>\n",
       "      <td>Baking</td>\n",
       "      <td>BAKING</td>\n",
       "      <td>{'$id': {'$oid': '601ac114be37ce2ead437550'}, ...</td>\n",
       "      <td>test brand @1612366101024</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>{'$oid': '601c5460be37ce2ead43755f'}</td>\n",
       "      <td>511111519928</td>\n",
       "      <td>Beverages</td>\n",
       "      <td>BEVERAGES</td>\n",
       "      <td>{'$id': {'$oid': '5332f5fbe4b03c9a25efd0ba'}, ...</td>\n",
       "      <td>Starbucks</td>\n",
       "      <td>0.0</td>\n",
       "      <td>STARBUCKS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>{'$oid': '601ac142be37ce2ead43755d'}</td>\n",
       "      <td>511111819905</td>\n",
       "      <td>Baking</td>\n",
       "      <td>BAKING</td>\n",
       "      <td>{'$id': {'$oid': '601ac142be37ce2ead437559'}, ...</td>\n",
       "      <td>test brand @1612366146176</td>\n",
       "      <td>0.0</td>\n",
       "      <td>TEST BRANDCODE @1612366146176</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>{'$oid': '601ac142be37ce2ead43755a'}</td>\n",
       "      <td>511111519874</td>\n",
       "      <td>Baking</td>\n",
       "      <td>BAKING</td>\n",
       "      <td>{'$id': {'$oid': '601ac142be37ce2ead437559'}, ...</td>\n",
       "      <td>test brand @1612366146051</td>\n",
       "      <td>0.0</td>\n",
       "      <td>TEST BRANDCODE @1612366146051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>{'$oid': '601ac142be37ce2ead43755e'}</td>\n",
       "      <td>511111319917</td>\n",
       "      <td>Candy &amp; Sweets</td>\n",
       "      <td>CANDY_AND_SWEETS</td>\n",
       "      <td>{'$id': {'$oid': '5332fa12e4b03c9a25efd1e7'}, ...</td>\n",
       "      <td>test brand @1612366146827</td>\n",
       "      <td>0.0</td>\n",
       "      <td>TEST BRANDCODE @1612366146827</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>{'$oid': '601ac142be37ce2ead43755b'}</td>\n",
       "      <td>511111719885</td>\n",
       "      <td>Baking</td>\n",
       "      <td>BAKING</td>\n",
       "      <td>{'$id': {'$oid': '601ac142be37ce2ead437559'}, ...</td>\n",
       "      <td>test brand @1612366146091</td>\n",
       "      <td>0.0</td>\n",
       "      <td>TEST BRANDCODE @1612366146091</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>{'$oid': '601ac142be37ce2ead43755c'}</td>\n",
       "      <td>511111219897</td>\n",
       "      <td>Baking</td>\n",
       "      <td>BAKING</td>\n",
       "      <td>{'$id': {'$oid': '601ac142be37ce2ead437559'}, ...</td>\n",
       "      <td>test brand @1612366146133</td>\n",
       "      <td>0.0</td>\n",
       "      <td>TEST BRANDCODE @1612366146133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>{'$oid': '5cdad0f5166eb33eb7ce0faa'}</td>\n",
       "      <td>511111104810</td>\n",
       "      <td>Condiments &amp; Sauces</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{'$ref': 'Cogs', '$id': {'$oid': '559c2234e4b0...</td>\n",
       "      <td>J.L. Kraft</td>\n",
       "      <td>NaN</td>\n",
       "      <td>J.L. KRAFT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>{'$oid': '5ab15636e4b0be0a89bb0b07'}</td>\n",
       "      <td>511111504412</td>\n",
       "      <td>Canned Goods &amp; Soups</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{'$ref': 'Cogs', '$id': {'$oid': '5a734034e4b0...</td>\n",
       "      <td>Campbell's Home Style</td>\n",
       "      <td>0.0</td>\n",
       "      <td>CAMPBELLS HOME STYLE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>{'$oid': '5c408e8bcd244a1fdb47aee7'}</td>\n",
       "      <td>511111504788</td>\n",
       "      <td>Baking</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{'$ref': 'Cogs', '$id': {'$oid': '59ba6f1ce4b0...</td>\n",
       "      <td>test</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TEST</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>{'$oid': '5f4bf556be37ce0b4491554d'}</td>\n",
       "      <td>511111516354</td>\n",
       "      <td>Baking</td>\n",
       "      <td>BAKING</td>\n",
       "      <td>{'$ref': 'Cogs', '$id': {'$oid': '5f4bf556be37...</td>\n",
       "      <td>test brand @1598813526777</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TEST BRANDCODE @1598813526777</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>{'$oid': '57c08106e4b0718ff5fcb02c'}</td>\n",
       "      <td>511111102540</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{'$ref': 'Cpgs', '$id': {'$oid': '5332f5f2e4b0...</td>\n",
       "      <td>MorningStar</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>{'$oid': '588ba07be4b02187f85cdadd'}</td>\n",
       "      <td>511111201076</td>\n",
       "      <td>Baking</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{'$ref': 'Cogs', '$id': {'$oid': '559c2234e4b0...</td>\n",
       "      <td>Calumet</td>\n",
       "      <td>0.0</td>\n",
       "      <td>CALUMET</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>{'$oid': '5d6413156d5f3b23d1bc790a'}</td>\n",
       "      <td>511111205012</td>\n",
       "      <td>Magazines</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{'$ref': 'Cogs', '$id': {'$oid': '5d5d4fd16d5f...</td>\n",
       "      <td>Entertainment Weekly</td>\n",
       "      <td>NaN</td>\n",
       "      <td>511111205012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>{'$oid': '585a9611e4b03e62d1ce0e74'}</td>\n",
       "      <td>511111801801</td>\n",
       "      <td>Breakfast &amp; Cereal</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{'$ref': 'Cogs', '$id': {'$oid': '5332f5fbe4b0...</td>\n",
       "      <td>AUNT JEMIMA Syrup</td>\n",
       "      <td>0.0</td>\n",
       "      <td>AUNT JEMIMA SYRUP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>{'$oid': '57e5820ce4b0ac389136a311'}</td>\n",
       "      <td>511111202233</td>\n",
       "      <td>Beer Wine Spirits</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{'$ref': 'Cpgs', '$id': {'$oid': '5332f709e4b0...</td>\n",
       "      <td>Molson Canadian</td>\n",
       "      <td>0.0</td>\n",
       "      <td>MOLSON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>{'$oid': '5fb6adb8be37ce522e165cb8'}</td>\n",
       "      <td>511111817376</td>\n",
       "      <td>Health &amp; Wellness</td>\n",
       "      <td>HEALTHY_AND_WELLNESS</td>\n",
       "      <td>{'$ref': 'Cogs', '$id': {'$oid': '5d9b4f591dda...</td>\n",
       "      <td>Lotrimin®</td>\n",
       "      <td>0.0</td>\n",
       "      <td>LOTRIMIN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>{'$oid': '5f358338be37ce443bf9d55a'}</td>\n",
       "      <td>511111515319</td>\n",
       "      <td>Baking</td>\n",
       "      <td>BAKING</td>\n",
       "      <td>{'$ref': 'Cogs', '$id': {'$oid': '5f358338be37...</td>\n",
       "      <td>test brand @1597342520277</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TEST BRANDCODE @1597342520277</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>{'$oid': '5fb28549be37ce522e165cb5'}</td>\n",
       "      <td>511111317364</td>\n",
       "      <td>Baking</td>\n",
       "      <td>BAKING</td>\n",
       "      <td>{'$ref': 'Cogs', '$id': {'$oid': '5fb28549be37...</td>\n",
       "      <td>test brand @1605535049181</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>{'$oid': '592486bfe410d61fcea3d139'}</td>\n",
       "      <td>511111300700</td>\n",
       "      <td>Beauty</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{'$ref': 'Cogs', '$id': {'$oid': '5332f5f6e4b0...</td>\n",
       "      <td>ST. IVES</td>\n",
       "      <td>0.0</td>\n",
       "      <td>ST IVES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>{'$oid': '5c4699f387ff3577e203ea29'}</td>\n",
       "      <td>511111305125</td>\n",
       "      <td>Baby</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{'$ref': 'Cogs', '$id': {'$oid': '55b62995e4b0...</td>\n",
       "      <td>Chris Image Test</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CHRISIMAGE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>{'$oid': '5da6071ea60b87376833e34d'}</td>\n",
       "      <td>511111005650</td>\n",
       "      <td>Health &amp; Wellness</td>\n",
       "      <td>HEALTHY_AND_WELLNESS</td>\n",
       "      <td>{'$ref': 'Cogs', '$id': {'$oid': '5d9b4f591dda...</td>\n",
       "      <td>Alka-Seltzer®</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ALKA SELTZER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>{'$oid': '57ebc011e4b0ac389136a335'}</td>\n",
       "      <td>511111802129</td>\n",
       "      <td>Condiments &amp; Sauces</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{'$ref': 'Cogs', '$id': {'$oid': '559c2234e4b0...</td>\n",
       "      <td>Jack Daniel's</td>\n",
       "      <td>0.0</td>\n",
       "      <td>JACK DANIEL'S BARBECUE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>{'$oid': '5332f5fee4b03c9a25efd0bd'}</td>\n",
       "      <td>511111303947</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{'$ref': 'Cpgs', '$id': {'$oid': '53e10d6368ab...</td>\n",
       "      <td>Bottled Starbucks</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>{'$oid': '5332fa7ce4b03c9a25efd22e'}</td>\n",
       "      <td>511111802914</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{'$ref': 'Cpgs', '$id': {'$oid': '5332f5ebe4b0...</td>\n",
       "      <td>Full Throttle</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>{'$oid': '5e9f18bfbe37ce3e45b6a77f'}</td>\n",
       "      <td>511111914549</td>\n",
       "      <td>Baking</td>\n",
       "      <td>BAKING</td>\n",
       "      <td>{'$ref': 'Cogs', '$id': {'$oid': '5e9f12f5be37...</td>\n",
       "      <td>PopUp Brand A</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>{'$oid': '592486bee410d61fcea3d133'}</td>\n",
       "      <td>511111400769</td>\n",
       "      <td>Frozen</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{'$ref': 'Cogs', '$id': {'$oid': '5332f5f6e4b0...</td>\n",
       "      <td>MAGNUM Ice Cream</td>\n",
       "      <td>0.0</td>\n",
       "      <td>MAGNUM Ice Cream</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>{'$oid': '5d66d71fa3a018093ab34728'}</td>\n",
       "      <td>511111105329</td>\n",
       "      <td>Magazines</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{'$ref': 'Cogs', '$id': {'$oid': '5d5d4fd16d5f...</td>\n",
       "      <td>Elegant Homes Magazine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>511111105329</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>{'$oid': '5f493e72be37ce64d0ae36c6'}</td>\n",
       "      <td>511111316114</td>\n",
       "      <td>Baking</td>\n",
       "      <td>BAKING</td>\n",
       "      <td>{'$ref': 'Cogs', '$id': {'$oid': '5f493e72be37...</td>\n",
       "      <td>test brand @1598635634882</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TEST BRANDCODE @1598635634882</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>{'$oid': '5f4936ddbe37ce52f8314fd9'}</td>\n",
       "      <td>511111315957</td>\n",
       "      <td>Baking</td>\n",
       "      <td>BAKING</td>\n",
       "      <td>{'$ref': 'Cogs', '$id': {'$oid': '5f4936dcbe37...</td>\n",
       "      <td>test brand @1598633693011</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>{'$oid': '57ebc2e7e4b0ac389136a34b'}</td>\n",
       "      <td>511111201915</td>\n",
       "      <td>Grocery</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{'$ref': 'Cogs', '$id': {'$oid': '559c2234e4b0...</td>\n",
       "      <td>Taco Bell</td>\n",
       "      <td>0.0</td>\n",
       "      <td>TACO BELL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>{'$oid': '5fd2a0aebe37ce49eb72c0ee'}</td>\n",
       "      <td>511111518112</td>\n",
       "      <td>Baking</td>\n",
       "      <td>BAKING</td>\n",
       "      <td>{'$ref': 'Cogs', '$id': {'$oid': '5fd2a0aebe37...</td>\n",
       "      <td>test brand @1607639214411</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>{'$oid': '5dc03596a60b873d6b0666cd'}</td>\n",
       "      <td>511111506249</td>\n",
       "      <td>Breakfast &amp; Cereal</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{'$ref': 'Cogs', '$id': {'$oid': '53e10d6368ab...</td>\n",
       "      <td>Frosted Cheerios™</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FROSTED CHEERIOS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>{'$oid': '5f494c5f04db711dd8fe87e6'}</td>\n",
       "      <td>511111916161</td>\n",
       "      <td>Baking</td>\n",
       "      <td>BAKING</td>\n",
       "      <td>{'$ref': 'Cogs', '$id': {'$oid': '5f494c5d04db...</td>\n",
       "      <td>test brand @1598639199674</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TEST BRANDCODE @1598639199674</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>{'$oid': '5332f772e4b03c9a25efd125'}</td>\n",
       "      <td>511111103653</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{'$ref': 'Cpgs', '$id': {'$oid': '5332f5f3e4b0...</td>\n",
       "      <td>Gold Medal</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>{'$oid': '59dfaad1e4b0a56a2fa69abc'}</td>\n",
       "      <td>511111100621</td>\n",
       "      <td>Baking</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{'$ref': 'Cogs', '$id': {'$oid': '559c2234e4b0...</td>\n",
       "      <td>GODIVA Instant Pudding Mix</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GODIVA DRY PACKAGED DESSERTS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>{'$oid': '5daf33e71dda2c3e1416ae93'}</td>\n",
       "      <td>511111305910</td>\n",
       "      <td>Snacks</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{'$ref': 'Cogs', '$id': {'$oid': '5332f5f3e4b0...</td>\n",
       "      <td>LÄRABAR™</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LARABAR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>{'$oid': '5f35a0babe37ce6853cff1ff'}</td>\n",
       "      <td>511111115366</td>\n",
       "      <td>Baking</td>\n",
       "      <td>BAKING</td>\n",
       "      <td>{'$ref': 'Cogs', '$id': {'$oid': '5f35a0babe37...</td>\n",
       "      <td>test brand @1597350074333</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TEST BRANDCODE @1597350074333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>{'$oid': '5fd29590be37ce38bbbefb28'}</td>\n",
       "      <td>511111718079</td>\n",
       "      <td>Baking</td>\n",
       "      <td>BAKING</td>\n",
       "      <td>{'$ref': 'Cogs', '$id': {'$oid': '5fd29590be37...</td>\n",
       "      <td>test brand @1607636368717</td>\n",
       "      <td>0.0</td>\n",
       "      <td>TEST BRANDCODE @1607636368717</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>{'$oid': '5fd3acb6be37ce0d9386bb40'}</td>\n",
       "      <td>511111818182</td>\n",
       "      <td>Baking</td>\n",
       "      <td>BAKING</td>\n",
       "      <td>{'$ref': 'Cogs', '$id': {'$oid': '5fd3acb5be37...</td>\n",
       "      <td>test brand @1607707830095</td>\n",
       "      <td>0.0</td>\n",
       "      <td>TEST BRANDCODE @1607707830095</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>{'$oid': '5bd200a6965c7d66d92731ea'}</td>\n",
       "      <td>511111504627</td>\n",
       "      <td>Household</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{'$ref': 'Cogs', '$id': {'$oid': '550b2565e4b0...</td>\n",
       "      <td>Cottonelle</td>\n",
       "      <td>0.0</td>\n",
       "      <td>COTTONELLE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>{'$oid': '585a9675e4b03e62d1ce0e7f'}</td>\n",
       "      <td>511111301691</td>\n",
       "      <td>Beverages</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{'$ref': 'Cogs', '$id': {'$oid': '5332f5fbe4b0...</td>\n",
       "      <td>Izze</td>\n",
       "      <td>0.0</td>\n",
       "      <td>IZZE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>{'$oid': '5887a17ee4b02187f85cdad3'}</td>\n",
       "      <td>511111101178</td>\n",
       "      <td>Beverages</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{'$ref': 'Cogs', '$id': {'$oid': '559c2234e4b0...</td>\n",
       "      <td>Mio</td>\n",
       "      <td>0.0</td>\n",
       "      <td>MIO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>{'$oid': '5332f765e4b03c9a25efd11f'}</td>\n",
       "      <td>511111503699</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{'$ref': 'Cpgs', '$id': {'$oid': '5332f5ebe4b0...</td>\n",
       "      <td>Glaceau vitaminwater</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>{'$oid': '5d66d94d6d5f3b6188d4f04b'}</td>\n",
       "      <td>511111505365</td>\n",
       "      <td>Magazines</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{'$ref': 'Cogs', '$id': {'$oid': '5d5d4fd16d5f...</td>\n",
       "      <td>Magnolia Journal Magazine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>511111505365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>{'$oid': '5d66d2fd6d5f3b6188d4f045'}</td>\n",
       "      <td>511111405252</td>\n",
       "      <td>Magazines</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{'$ref': 'Cogs', '$id': {'$oid': '5d5d4fd16d5f...</td>\n",
       "      <td>American Patchwork &amp; Quilting Magazine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>QUILTING SPECIAL EDITION</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>{'$oid': '5332f5f3e4b03c9a25efd0ad'}</td>\n",
       "      <td>511111104025</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{'$ref': 'Cpgs', '$id': {'$oid': '5332f5f3e4b0...</td>\n",
       "      <td>Gree Giant</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>{'$oid': '5fa1c567be37ce402c4618f1'}</td>\n",
       "      <td>511111317203</td>\n",
       "      <td>Baking</td>\n",
       "      <td>BAKING</td>\n",
       "      <td>{'$ref': 'Cogs', '$id': {'$oid': '5fa1c567be37...</td>\n",
       "      <td>test brand @1604437351617</td>\n",
       "      <td>0.0</td>\n",
       "      <td>TEST BRANDCODE @1604437351617</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>{'$oid': '5a5d1e76e4b0db471c2d0425'}</td>\n",
       "      <td>511111900337</td>\n",
       "      <td>Beer Wine Spirits</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{'$ref': 'Cogs', '$id': {'$oid': '5332f709e4b0...</td>\n",
       "      <td>Herman Joseph's</td>\n",
       "      <td>NaN</td>\n",
       "      <td>HERMAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>{'$oid': '55b630d9e4b0d8e685c1421f'}</td>\n",
       "      <td>511111302629</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{'$ref': 'Cogs', '$id': {'$oid': '55b62995e4b0...</td>\n",
       "      <td>Sundrop</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     _id       barcode              category  \\\n",
       "0   {'$oid': '601ac115be37ce2ead437551'}  511111019862                Baking   \n",
       "1   {'$oid': '601c5460be37ce2ead43755f'}  511111519928             Beverages   \n",
       "2   {'$oid': '601ac142be37ce2ead43755d'}  511111819905                Baking   \n",
       "3   {'$oid': '601ac142be37ce2ead43755a'}  511111519874                Baking   \n",
       "4   {'$oid': '601ac142be37ce2ead43755e'}  511111319917        Candy & Sweets   \n",
       "5   {'$oid': '601ac142be37ce2ead43755b'}  511111719885                Baking   \n",
       "6   {'$oid': '601ac142be37ce2ead43755c'}  511111219897                Baking   \n",
       "7   {'$oid': '5cdad0f5166eb33eb7ce0faa'}  511111104810   Condiments & Sauces   \n",
       "8   {'$oid': '5ab15636e4b0be0a89bb0b07'}  511111504412  Canned Goods & Soups   \n",
       "9   {'$oid': '5c408e8bcd244a1fdb47aee7'}  511111504788                Baking   \n",
       "10  {'$oid': '5f4bf556be37ce0b4491554d'}  511111516354                Baking   \n",
       "11  {'$oid': '57c08106e4b0718ff5fcb02c'}  511111102540                   NaN   \n",
       "12  {'$oid': '588ba07be4b02187f85cdadd'}  511111201076                Baking   \n",
       "13  {'$oid': '5d6413156d5f3b23d1bc790a'}  511111205012             Magazines   \n",
       "14  {'$oid': '585a9611e4b03e62d1ce0e74'}  511111801801    Breakfast & Cereal   \n",
       "15  {'$oid': '57e5820ce4b0ac389136a311'}  511111202233     Beer Wine Spirits   \n",
       "16  {'$oid': '5fb6adb8be37ce522e165cb8'}  511111817376     Health & Wellness   \n",
       "17  {'$oid': '5f358338be37ce443bf9d55a'}  511111515319                Baking   \n",
       "18  {'$oid': '5fb28549be37ce522e165cb5'}  511111317364                Baking   \n",
       "19  {'$oid': '592486bfe410d61fcea3d139'}  511111300700                Beauty   \n",
       "20  {'$oid': '5c4699f387ff3577e203ea29'}  511111305125                  Baby   \n",
       "21  {'$oid': '5da6071ea60b87376833e34d'}  511111005650     Health & Wellness   \n",
       "22  {'$oid': '57ebc011e4b0ac389136a335'}  511111802129   Condiments & Sauces   \n",
       "23  {'$oid': '5332f5fee4b03c9a25efd0bd'}  511111303947                   NaN   \n",
       "24  {'$oid': '5332fa7ce4b03c9a25efd22e'}  511111802914                   NaN   \n",
       "25  {'$oid': '5e9f18bfbe37ce3e45b6a77f'}  511111914549                Baking   \n",
       "26  {'$oid': '592486bee410d61fcea3d133'}  511111400769                Frozen   \n",
       "27  {'$oid': '5d66d71fa3a018093ab34728'}  511111105329             Magazines   \n",
       "28  {'$oid': '5f493e72be37ce64d0ae36c6'}  511111316114                Baking   \n",
       "29  {'$oid': '5f4936ddbe37ce52f8314fd9'}  511111315957                Baking   \n",
       "30  {'$oid': '57ebc2e7e4b0ac389136a34b'}  511111201915               Grocery   \n",
       "31  {'$oid': '5fd2a0aebe37ce49eb72c0ee'}  511111518112                Baking   \n",
       "32  {'$oid': '5dc03596a60b873d6b0666cd'}  511111506249    Breakfast & Cereal   \n",
       "33  {'$oid': '5f494c5f04db711dd8fe87e6'}  511111916161                Baking   \n",
       "34  {'$oid': '5332f772e4b03c9a25efd125'}  511111103653                   NaN   \n",
       "35  {'$oid': '59dfaad1e4b0a56a2fa69abc'}  511111100621                Baking   \n",
       "36  {'$oid': '5daf33e71dda2c3e1416ae93'}  511111305910                Snacks   \n",
       "37  {'$oid': '5f35a0babe37ce6853cff1ff'}  511111115366                Baking   \n",
       "38  {'$oid': '5fd29590be37ce38bbbefb28'}  511111718079                Baking   \n",
       "39  {'$oid': '5fd3acb6be37ce0d9386bb40'}  511111818182                Baking   \n",
       "40  {'$oid': '5bd200a6965c7d66d92731ea'}  511111504627             Household   \n",
       "41  {'$oid': '585a9675e4b03e62d1ce0e7f'}  511111301691             Beverages   \n",
       "42  {'$oid': '5887a17ee4b02187f85cdad3'}  511111101178             Beverages   \n",
       "43  {'$oid': '5332f765e4b03c9a25efd11f'}  511111503699                   NaN   \n",
       "44  {'$oid': '5d66d94d6d5f3b6188d4f04b'}  511111505365             Magazines   \n",
       "45  {'$oid': '5d66d2fd6d5f3b6188d4f045'}  511111405252             Magazines   \n",
       "46  {'$oid': '5332f5f3e4b03c9a25efd0ad'}  511111104025                   NaN   \n",
       "47  {'$oid': '5fa1c567be37ce402c4618f1'}  511111317203                Baking   \n",
       "48  {'$oid': '5a5d1e76e4b0db471c2d0425'}  511111900337     Beer Wine Spirits   \n",
       "49  {'$oid': '55b630d9e4b0d8e685c1421f'}  511111302629                   NaN   \n",
       "\n",
       "            categoryCode                                                cpg  \\\n",
       "0                 BAKING  {'$id': {'$oid': '601ac114be37ce2ead437550'}, ...   \n",
       "1              BEVERAGES  {'$id': {'$oid': '5332f5fbe4b03c9a25efd0ba'}, ...   \n",
       "2                 BAKING  {'$id': {'$oid': '601ac142be37ce2ead437559'}, ...   \n",
       "3                 BAKING  {'$id': {'$oid': '601ac142be37ce2ead437559'}, ...   \n",
       "4       CANDY_AND_SWEETS  {'$id': {'$oid': '5332fa12e4b03c9a25efd1e7'}, ...   \n",
       "5                 BAKING  {'$id': {'$oid': '601ac142be37ce2ead437559'}, ...   \n",
       "6                 BAKING  {'$id': {'$oid': '601ac142be37ce2ead437559'}, ...   \n",
       "7                    NaN  {'$ref': 'Cogs', '$id': {'$oid': '559c2234e4b0...   \n",
       "8                    NaN  {'$ref': 'Cogs', '$id': {'$oid': '5a734034e4b0...   \n",
       "9                    NaN  {'$ref': 'Cogs', '$id': {'$oid': '59ba6f1ce4b0...   \n",
       "10                BAKING  {'$ref': 'Cogs', '$id': {'$oid': '5f4bf556be37...   \n",
       "11                   NaN  {'$ref': 'Cpgs', '$id': {'$oid': '5332f5f2e4b0...   \n",
       "12                   NaN  {'$ref': 'Cogs', '$id': {'$oid': '559c2234e4b0...   \n",
       "13                   NaN  {'$ref': 'Cogs', '$id': {'$oid': '5d5d4fd16d5f...   \n",
       "14                   NaN  {'$ref': 'Cogs', '$id': {'$oid': '5332f5fbe4b0...   \n",
       "15                   NaN  {'$ref': 'Cpgs', '$id': {'$oid': '5332f709e4b0...   \n",
       "16  HEALTHY_AND_WELLNESS  {'$ref': 'Cogs', '$id': {'$oid': '5d9b4f591dda...   \n",
       "17                BAKING  {'$ref': 'Cogs', '$id': {'$oid': '5f358338be37...   \n",
       "18                BAKING  {'$ref': 'Cogs', '$id': {'$oid': '5fb28549be37...   \n",
       "19                   NaN  {'$ref': 'Cogs', '$id': {'$oid': '5332f5f6e4b0...   \n",
       "20                   NaN  {'$ref': 'Cogs', '$id': {'$oid': '55b62995e4b0...   \n",
       "21  HEALTHY_AND_WELLNESS  {'$ref': 'Cogs', '$id': {'$oid': '5d9b4f591dda...   \n",
       "22                   NaN  {'$ref': 'Cogs', '$id': {'$oid': '559c2234e4b0...   \n",
       "23                   NaN  {'$ref': 'Cpgs', '$id': {'$oid': '53e10d6368ab...   \n",
       "24                   NaN  {'$ref': 'Cpgs', '$id': {'$oid': '5332f5ebe4b0...   \n",
       "25                BAKING  {'$ref': 'Cogs', '$id': {'$oid': '5e9f12f5be37...   \n",
       "26                   NaN  {'$ref': 'Cogs', '$id': {'$oid': '5332f5f6e4b0...   \n",
       "27                   NaN  {'$ref': 'Cogs', '$id': {'$oid': '5d5d4fd16d5f...   \n",
       "28                BAKING  {'$ref': 'Cogs', '$id': {'$oid': '5f493e72be37...   \n",
       "29                BAKING  {'$ref': 'Cogs', '$id': {'$oid': '5f4936dcbe37...   \n",
       "30                   NaN  {'$ref': 'Cogs', '$id': {'$oid': '559c2234e4b0...   \n",
       "31                BAKING  {'$ref': 'Cogs', '$id': {'$oid': '5fd2a0aebe37...   \n",
       "32                   NaN  {'$ref': 'Cogs', '$id': {'$oid': '53e10d6368ab...   \n",
       "33                BAKING  {'$ref': 'Cogs', '$id': {'$oid': '5f494c5d04db...   \n",
       "34                   NaN  {'$ref': 'Cpgs', '$id': {'$oid': '5332f5f3e4b0...   \n",
       "35                   NaN  {'$ref': 'Cogs', '$id': {'$oid': '559c2234e4b0...   \n",
       "36                   NaN  {'$ref': 'Cogs', '$id': {'$oid': '5332f5f3e4b0...   \n",
       "37                BAKING  {'$ref': 'Cogs', '$id': {'$oid': '5f35a0babe37...   \n",
       "38                BAKING  {'$ref': 'Cogs', '$id': {'$oid': '5fd29590be37...   \n",
       "39                BAKING  {'$ref': 'Cogs', '$id': {'$oid': '5fd3acb5be37...   \n",
       "40                   NaN  {'$ref': 'Cogs', '$id': {'$oid': '550b2565e4b0...   \n",
       "41                   NaN  {'$ref': 'Cogs', '$id': {'$oid': '5332f5fbe4b0...   \n",
       "42                   NaN  {'$ref': 'Cogs', '$id': {'$oid': '559c2234e4b0...   \n",
       "43                   NaN  {'$ref': 'Cpgs', '$id': {'$oid': '5332f5ebe4b0...   \n",
       "44                   NaN  {'$ref': 'Cogs', '$id': {'$oid': '5d5d4fd16d5f...   \n",
       "45                   NaN  {'$ref': 'Cogs', '$id': {'$oid': '5d5d4fd16d5f...   \n",
       "46                   NaN  {'$ref': 'Cpgs', '$id': {'$oid': '5332f5f3e4b0...   \n",
       "47                BAKING  {'$ref': 'Cogs', '$id': {'$oid': '5fa1c567be37...   \n",
       "48                   NaN  {'$ref': 'Cogs', '$id': {'$oid': '5332f709e4b0...   \n",
       "49                   NaN  {'$ref': 'Cogs', '$id': {'$oid': '55b62995e4b0...   \n",
       "\n",
       "                                      name  topBrand  \\\n",
       "0                test brand @1612366101024       0.0   \n",
       "1                                Starbucks       0.0   \n",
       "2                test brand @1612366146176       0.0   \n",
       "3                test brand @1612366146051       0.0   \n",
       "4                test brand @1612366146827       0.0   \n",
       "5                test brand @1612366146091       0.0   \n",
       "6                test brand @1612366146133       0.0   \n",
       "7                               J.L. Kraft       NaN   \n",
       "8                    Campbell's Home Style       0.0   \n",
       "9                                     test       NaN   \n",
       "10               test brand @1598813526777       NaN   \n",
       "11                             MorningStar       NaN   \n",
       "12                                 Calumet       0.0   \n",
       "13                    Entertainment Weekly       NaN   \n",
       "14                       AUNT JEMIMA Syrup       0.0   \n",
       "15                         Molson Canadian       0.0   \n",
       "16                               Lotrimin®       0.0   \n",
       "17               test brand @1597342520277       NaN   \n",
       "18               test brand @1605535049181       0.0   \n",
       "19                                ST. IVES       0.0   \n",
       "20                        Chris Image Test       NaN   \n",
       "21                           Alka-Seltzer®       NaN   \n",
       "22                           Jack Daniel's       0.0   \n",
       "23                       Bottled Starbucks       NaN   \n",
       "24                           Full Throttle       NaN   \n",
       "25                           PopUp Brand A       NaN   \n",
       "26                        MAGNUM Ice Cream       0.0   \n",
       "27                  Elegant Homes Magazine       NaN   \n",
       "28               test brand @1598635634882       NaN   \n",
       "29               test brand @1598633693011       NaN   \n",
       "30                               Taco Bell       0.0   \n",
       "31               test brand @1607639214411       0.0   \n",
       "32                       Frosted Cheerios™       NaN   \n",
       "33               test brand @1598639199674       NaN   \n",
       "34                              Gold Medal       NaN   \n",
       "35              GODIVA Instant Pudding Mix       0.0   \n",
       "36                                LÄRABAR™       NaN   \n",
       "37               test brand @1597350074333       NaN   \n",
       "38               test brand @1607636368717       0.0   \n",
       "39               test brand @1607707830095       0.0   \n",
       "40                              Cottonelle       0.0   \n",
       "41                                    Izze       0.0   \n",
       "42                                     Mio       0.0   \n",
       "43                    Glaceau vitaminwater       NaN   \n",
       "44               Magnolia Journal Magazine       NaN   \n",
       "45  American Patchwork & Quilting Magazine       NaN   \n",
       "46                              Gree Giant       NaN   \n",
       "47               test brand @1604437351617       0.0   \n",
       "48                         Herman Joseph's       NaN   \n",
       "49                                 Sundrop       NaN   \n",
       "\n",
       "                        brandCode  \n",
       "0                             NaN  \n",
       "1                       STARBUCKS  \n",
       "2   TEST BRANDCODE @1612366146176  \n",
       "3   TEST BRANDCODE @1612366146051  \n",
       "4   TEST BRANDCODE @1612366146827  \n",
       "5   TEST BRANDCODE @1612366146091  \n",
       "6   TEST BRANDCODE @1612366146133  \n",
       "7                      J.L. KRAFT  \n",
       "8            CAMPBELLS HOME STYLE  \n",
       "9                            TEST  \n",
       "10  TEST BRANDCODE @1598813526777  \n",
       "11                            NaN  \n",
       "12                        CALUMET  \n",
       "13                   511111205012  \n",
       "14              AUNT JEMIMA SYRUP  \n",
       "15                         MOLSON  \n",
       "16                       LOTRIMIN  \n",
       "17  TEST BRANDCODE @1597342520277  \n",
       "18                            NaN  \n",
       "19                        ST IVES  \n",
       "20                     CHRISIMAGE  \n",
       "21                   ALKA SELTZER  \n",
       "22         JACK DANIEL'S BARBECUE  \n",
       "23                            NaN  \n",
       "24                            NaN  \n",
       "25                            NaN  \n",
       "26               MAGNUM Ice Cream  \n",
       "27                   511111105329  \n",
       "28  TEST BRANDCODE @1598635634882  \n",
       "29                            NaN  \n",
       "30                      TACO BELL  \n",
       "31                            NaN  \n",
       "32               FROSTED CHEERIOS  \n",
       "33  TEST BRANDCODE @1598639199674  \n",
       "34                            NaN  \n",
       "35   GODIVA DRY PACKAGED DESSERTS  \n",
       "36                        LARABAR  \n",
       "37  TEST BRANDCODE @1597350074333  \n",
       "38  TEST BRANDCODE @1607636368717  \n",
       "39  TEST BRANDCODE @1607707830095  \n",
       "40                     COTTONELLE  \n",
       "41                           IZZE  \n",
       "42                            MIO  \n",
       "43                            NaN  \n",
       "44                   511111505365  \n",
       "45       QUILTING SPECIAL EDITION  \n",
       "46                            NaN  \n",
       "47  TEST BRANDCODE @1604437351617  \n",
       "48                         HERMAN  \n",
       "49                            NaN  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "brands_df.head(50)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1167, 8)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "brands_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1167 entries, 0 to 1166\n",
      "Data columns (total 8 columns):\n",
      " #   Column        Non-Null Count  Dtype  \n",
      "---  ------        --------------  -----  \n",
      " 0   _id           1167 non-null   object \n",
      " 1   barcode       1167 non-null   int64  \n",
      " 2   category      1012 non-null   object \n",
      " 3   categoryCode  517 non-null    object \n",
      " 4   cpg           1167 non-null   object \n",
      " 5   name          1167 non-null   object \n",
      " 6   topBrand      555 non-null    float64\n",
      " 7   brandCode     933 non-null    object \n",
      "dtypes: float64(1), int64(1), object(6)\n",
      "memory usage: 73.1+ KB\n"
     ]
    }
   ],
   "source": [
    "brands_df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Creating a dummy dataframe to clean all the missing information or imputing some new information, as working with the original dataframe can cause an issue."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "brands_df_copy = brands_df.copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Created this method to find all the keys available inside a dictionary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "def findKeys(df,col):\n",
    "    if(df[col].isnull().sum() == 0):\n",
    "        return set().union(*(d.keys() for d in df[col]))\n",
    "    else:\n",
    "        return set().union(*(d.keys() for d in df.dropna(subset=[col])[col]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As for an instance we can see, we have following two key within our dataframe, id and ref."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'$id': {'$oid': '601ac114be37ce2ead437550'}, '$ref': 'Cogs'}"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "brands_df['cpg'][0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "brands_df_copy['_id'] = brands_df_copy['_id'].apply(lambda x: x['$oid'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'$id', '$ref'}"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "findKeys(brands_df_copy,'cpg')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using **json_normalize** method from pandas to get the semi-structured JSON data into a flat table "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>cpg_$ref</th>\n",
       "      <th>cpg_$id.$oid</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Cogs</td>\n",
       "      <td>601ac114be37ce2ead437550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Cogs</td>\n",
       "      <td>5332f5fbe4b03c9a25efd0ba</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Cogs</td>\n",
       "      <td>601ac142be37ce2ead437559</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Cogs</td>\n",
       "      <td>601ac142be37ce2ead437559</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Cogs</td>\n",
       "      <td>5332fa12e4b03c9a25efd1e7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1162</th>\n",
       "      <td>Cogs</td>\n",
       "      <td>5f77274dbe37ce6b592e90bf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1163</th>\n",
       "      <td>Cogs</td>\n",
       "      <td>53e10d6368abd3c7065097cc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1164</th>\n",
       "      <td>Cogs</td>\n",
       "      <td>5332fa12e4b03c9a25efd1e7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1165</th>\n",
       "      <td>Cogs</td>\n",
       "      <td>5332f5f6e4b03c9a25efd0b4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1166</th>\n",
       "      <td>Cogs</td>\n",
       "      <td>6026d757be37ce6369301467</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1167 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     cpg_$ref              cpg_$id.$oid\n",
       "0        Cogs  601ac114be37ce2ead437550\n",
       "1        Cogs  5332f5fbe4b03c9a25efd0ba\n",
       "2        Cogs  601ac142be37ce2ead437559\n",
       "3        Cogs  601ac142be37ce2ead437559\n",
       "4        Cogs  5332fa12e4b03c9a25efd1e7\n",
       "...       ...                       ...\n",
       "1162     Cogs  5f77274dbe37ce6b592e90bf\n",
       "1163     Cogs  53e10d6368abd3c7065097cc\n",
       "1164     Cogs  5332fa12e4b03c9a25efd1e7\n",
       "1165     Cogs  5332f5f6e4b03c9a25efd0b4\n",
       "1166     Cogs  6026d757be37ce6369301467\n",
       "\n",
       "[1167 rows x 2 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "brands_cpg_norm = pd.json_normalize(brands_df_copy['cpg']).add_prefix('cpg_')\n",
    "brands_cpg_norm"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As we have already got all the data from **cpg** column, so we are dropping it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "brands_df_copy.drop(columns='cpg',inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Finally merging both the dataframe and creating a final clean dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>_id</th>\n",
       "      <th>barcode</th>\n",
       "      <th>category</th>\n",
       "      <th>categoryCode</th>\n",
       "      <th>name</th>\n",
       "      <th>topBrand</th>\n",
       "      <th>brandCode</th>\n",
       "      <th>cpg_$ref</th>\n",
       "      <th>cpg_$id.$oid</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>601ac115be37ce2ead437551</td>\n",
       "      <td>511111019862</td>\n",
       "      <td>Baking</td>\n",
       "      <td>BAKING</td>\n",
       "      <td>test brand @1612366101024</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Cogs</td>\n",
       "      <td>601ac114be37ce2ead437550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>601c5460be37ce2ead43755f</td>\n",
       "      <td>511111519928</td>\n",
       "      <td>Beverages</td>\n",
       "      <td>BEVERAGES</td>\n",
       "      <td>Starbucks</td>\n",
       "      <td>0.0</td>\n",
       "      <td>STARBUCKS</td>\n",
       "      <td>Cogs</td>\n",
       "      <td>5332f5fbe4b03c9a25efd0ba</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>601ac142be37ce2ead43755d</td>\n",
       "      <td>511111819905</td>\n",
       "      <td>Baking</td>\n",
       "      <td>BAKING</td>\n",
       "      <td>test brand @1612366146176</td>\n",
       "      <td>0.0</td>\n",
       "      <td>TEST BRANDCODE @1612366146176</td>\n",
       "      <td>Cogs</td>\n",
       "      <td>601ac142be37ce2ead437559</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>601ac142be37ce2ead43755a</td>\n",
       "      <td>511111519874</td>\n",
       "      <td>Baking</td>\n",
       "      <td>BAKING</td>\n",
       "      <td>test brand @1612366146051</td>\n",
       "      <td>0.0</td>\n",
       "      <td>TEST BRANDCODE @1612366146051</td>\n",
       "      <td>Cogs</td>\n",
       "      <td>601ac142be37ce2ead437559</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>601ac142be37ce2ead43755e</td>\n",
       "      <td>511111319917</td>\n",
       "      <td>Candy &amp; Sweets</td>\n",
       "      <td>CANDY_AND_SWEETS</td>\n",
       "      <td>test brand @1612366146827</td>\n",
       "      <td>0.0</td>\n",
       "      <td>TEST BRANDCODE @1612366146827</td>\n",
       "      <td>Cogs</td>\n",
       "      <td>5332fa12e4b03c9a25efd1e7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        _id       barcode        category      categoryCode  \\\n",
       "0  601ac115be37ce2ead437551  511111019862          Baking            BAKING   \n",
       "1  601c5460be37ce2ead43755f  511111519928       Beverages         BEVERAGES   \n",
       "2  601ac142be37ce2ead43755d  511111819905          Baking            BAKING   \n",
       "3  601ac142be37ce2ead43755a  511111519874          Baking            BAKING   \n",
       "4  601ac142be37ce2ead43755e  511111319917  Candy & Sweets  CANDY_AND_SWEETS   \n",
       "\n",
       "                        name  topBrand                      brandCode  \\\n",
       "0  test brand @1612366101024       0.0                            NaN   \n",
       "1                  Starbucks       0.0                      STARBUCKS   \n",
       "2  test brand @1612366146176       0.0  TEST BRANDCODE @1612366146176   \n",
       "3  test brand @1612366146051       0.0  TEST BRANDCODE @1612366146051   \n",
       "4  test brand @1612366146827       0.0  TEST BRANDCODE @1612366146827   \n",
       "\n",
       "  cpg_$ref              cpg_$id.$oid  \n",
       "0     Cogs  601ac114be37ce2ead437550  \n",
       "1     Cogs  5332f5fbe4b03c9a25efd0ba  \n",
       "2     Cogs  601ac142be37ce2ead437559  \n",
       "3     Cogs  601ac142be37ce2ead437559  \n",
       "4     Cogs  5332fa12e4b03c9a25efd1e7  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "brands_final = pd.merge(brands_df_copy, brands_cpg_norm, left_index=True, right_index=True, how='outer')\n",
    "brands_final.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Receipt_DF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>_id</th>\n",
       "      <th>bonusPointsEarned</th>\n",
       "      <th>bonusPointsEarnedReason</th>\n",
       "      <th>createDate</th>\n",
       "      <th>dateScanned</th>\n",
       "      <th>finishedDate</th>\n",
       "      <th>modifyDate</th>\n",
       "      <th>pointsAwardedDate</th>\n",
       "      <th>pointsEarned</th>\n",
       "      <th>purchaseDate</th>\n",
       "      <th>purchasedItemCount</th>\n",
       "      <th>rewardsReceiptItemList</th>\n",
       "      <th>rewardsReceiptStatus</th>\n",
       "      <th>totalSpent</th>\n",
       "      <th>userId</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>{'$oid': '5ff1e1eb0a720f0523000575'}</td>\n",
       "      <td>500.0</td>\n",
       "      <td>Receipt number 2 completed, bonus point schedu...</td>\n",
       "      <td>{'$date': 1609687531000}</td>\n",
       "      <td>{'$date': 1609687531000}</td>\n",
       "      <td>{'$date': 1609687531000}</td>\n",
       "      <td>{'$date': 1609687536000}</td>\n",
       "      <td>{'$date': 1609687531000}</td>\n",
       "      <td>500.0</td>\n",
       "      <td>{'$date': 1609632000000}</td>\n",
       "      <td>5.0</td>\n",
       "      <td>[{'barcode': '4011', 'description': 'ITEM NOT ...</td>\n",
       "      <td>FINISHED</td>\n",
       "      <td>26.0</td>\n",
       "      <td>5ff1e1eacfcf6c399c274ae6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>{'$oid': '5ff1e1bb0a720f052300056b'}</td>\n",
       "      <td>150.0</td>\n",
       "      <td>Receipt number 5 completed, bonus point schedu...</td>\n",
       "      <td>{'$date': 1609687483000}</td>\n",
       "      <td>{'$date': 1609687483000}</td>\n",
       "      <td>{'$date': 1609687483000}</td>\n",
       "      <td>{'$date': 1609687488000}</td>\n",
       "      <td>{'$date': 1609687483000}</td>\n",
       "      <td>150.0</td>\n",
       "      <td>{'$date': 1609601083000}</td>\n",
       "      <td>2.0</td>\n",
       "      <td>[{'barcode': '4011', 'description': 'ITEM NOT ...</td>\n",
       "      <td>FINISHED</td>\n",
       "      <td>11.0</td>\n",
       "      <td>5ff1e194b6a9d73a3a9f1052</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>{'$oid': '5ff1e1f10a720f052300057a'}</td>\n",
       "      <td>5.0</td>\n",
       "      <td>All-receipts receipt bonus</td>\n",
       "      <td>{'$date': 1609687537000}</td>\n",
       "      <td>{'$date': 1609687537000}</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{'$date': 1609687542000}</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>{'$date': 1609632000000}</td>\n",
       "      <td>1.0</td>\n",
       "      <td>[{'needsFetchReview': False, 'partnerItemId': ...</td>\n",
       "      <td>REJECTED</td>\n",
       "      <td>10.0</td>\n",
       "      <td>5ff1e1f1cfcf6c399c274b0b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>{'$oid': '5ff1e1ee0a7214ada100056f'}</td>\n",
       "      <td>5.0</td>\n",
       "      <td>All-receipts receipt bonus</td>\n",
       "      <td>{'$date': 1609687534000}</td>\n",
       "      <td>{'$date': 1609687534000}</td>\n",
       "      <td>{'$date': 1609687534000}</td>\n",
       "      <td>{'$date': 1609687539000}</td>\n",
       "      <td>{'$date': 1609687534000}</td>\n",
       "      <td>5.0</td>\n",
       "      <td>{'$date': 1609632000000}</td>\n",
       "      <td>4.0</td>\n",
       "      <td>[{'barcode': '4011', 'description': 'ITEM NOT ...</td>\n",
       "      <td>FINISHED</td>\n",
       "      <td>28.0</td>\n",
       "      <td>5ff1e1eacfcf6c399c274ae6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>{'$oid': '5ff1e1d20a7214ada1000561'}</td>\n",
       "      <td>5.0</td>\n",
       "      <td>All-receipts receipt bonus</td>\n",
       "      <td>{'$date': 1609687506000}</td>\n",
       "      <td>{'$date': 1609687506000}</td>\n",
       "      <td>{'$date': 1609687511000}</td>\n",
       "      <td>{'$date': 1609687511000}</td>\n",
       "      <td>{'$date': 1609687506000}</td>\n",
       "      <td>5.0</td>\n",
       "      <td>{'$date': 1609601106000}</td>\n",
       "      <td>2.0</td>\n",
       "      <td>[{'barcode': '4011', 'description': 'ITEM NOT ...</td>\n",
       "      <td>FINISHED</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5ff1e194b6a9d73a3a9f1052</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    _id  bonusPointsEarned  \\\n",
       "0  {'$oid': '5ff1e1eb0a720f0523000575'}              500.0   \n",
       "1  {'$oid': '5ff1e1bb0a720f052300056b'}              150.0   \n",
       "2  {'$oid': '5ff1e1f10a720f052300057a'}                5.0   \n",
       "3  {'$oid': '5ff1e1ee0a7214ada100056f'}                5.0   \n",
       "4  {'$oid': '5ff1e1d20a7214ada1000561'}                5.0   \n",
       "\n",
       "                             bonusPointsEarnedReason  \\\n",
       "0  Receipt number 2 completed, bonus point schedu...   \n",
       "1  Receipt number 5 completed, bonus point schedu...   \n",
       "2                         All-receipts receipt bonus   \n",
       "3                         All-receipts receipt bonus   \n",
       "4                         All-receipts receipt bonus   \n",
       "\n",
       "                 createDate               dateScanned  \\\n",
       "0  {'$date': 1609687531000}  {'$date': 1609687531000}   \n",
       "1  {'$date': 1609687483000}  {'$date': 1609687483000}   \n",
       "2  {'$date': 1609687537000}  {'$date': 1609687537000}   \n",
       "3  {'$date': 1609687534000}  {'$date': 1609687534000}   \n",
       "4  {'$date': 1609687506000}  {'$date': 1609687506000}   \n",
       "\n",
       "               finishedDate                modifyDate  \\\n",
       "0  {'$date': 1609687531000}  {'$date': 1609687536000}   \n",
       "1  {'$date': 1609687483000}  {'$date': 1609687488000}   \n",
       "2                       NaN  {'$date': 1609687542000}   \n",
       "3  {'$date': 1609687534000}  {'$date': 1609687539000}   \n",
       "4  {'$date': 1609687511000}  {'$date': 1609687511000}   \n",
       "\n",
       "          pointsAwardedDate  pointsEarned              purchaseDate  \\\n",
       "0  {'$date': 1609687531000}         500.0  {'$date': 1609632000000}   \n",
       "1  {'$date': 1609687483000}         150.0  {'$date': 1609601083000}   \n",
       "2                       NaN           5.0  {'$date': 1609632000000}   \n",
       "3  {'$date': 1609687534000}           5.0  {'$date': 1609632000000}   \n",
       "4  {'$date': 1609687506000}           5.0  {'$date': 1609601106000}   \n",
       "\n",
       "   purchasedItemCount                             rewardsReceiptItemList  \\\n",
       "0                 5.0  [{'barcode': '4011', 'description': 'ITEM NOT ...   \n",
       "1                 2.0  [{'barcode': '4011', 'description': 'ITEM NOT ...   \n",
       "2                 1.0  [{'needsFetchReview': False, 'partnerItemId': ...   \n",
       "3                 4.0  [{'barcode': '4011', 'description': 'ITEM NOT ...   \n",
       "4                 2.0  [{'barcode': '4011', 'description': 'ITEM NOT ...   \n",
       "\n",
       "  rewardsReceiptStatus  totalSpent                    userId  \n",
       "0             FINISHED        26.0  5ff1e1eacfcf6c399c274ae6  \n",
       "1             FINISHED        11.0  5ff1e194b6a9d73a3a9f1052  \n",
       "2             REJECTED        10.0  5ff1e1f1cfcf6c399c274b0b  \n",
       "3             FINISHED        28.0  5ff1e1eacfcf6c399c274ae6  \n",
       "4             FINISHED         1.0  5ff1e194b6a9d73a3a9f1052  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "receipts_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1119 entries, 0 to 1118\n",
      "Data columns (total 15 columns):\n",
      " #   Column                   Non-Null Count  Dtype  \n",
      "---  ------                   --------------  -----  \n",
      " 0   _id                      1119 non-null   object \n",
      " 1   bonusPointsEarned        544 non-null    float64\n",
      " 2   bonusPointsEarnedReason  544 non-null    object \n",
      " 3   createDate               1119 non-null   object \n",
      " 4   dateScanned              1119 non-null   object \n",
      " 5   finishedDate             568 non-null    object \n",
      " 6   modifyDate               1119 non-null   object \n",
      " 7   pointsAwardedDate        537 non-null    object \n",
      " 8   pointsEarned             609 non-null    float64\n",
      " 9   purchaseDate             671 non-null    object \n",
      " 10  purchasedItemCount       635 non-null    float64\n",
      " 11  rewardsReceiptItemList   679 non-null    object \n",
      " 12  rewardsReceiptStatus     1119 non-null   object \n",
      " 13  totalSpent               684 non-null    float64\n",
      " 14  userId                   1119 non-null   object \n",
      "dtypes: float64(4), object(11)\n",
      "memory usage: 131.3+ KB\n"
     ]
    }
   ],
   "source": [
    "receipts_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1119, 15)"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "receipts_df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Following the same methodology of creating a dummy DF and cleaning it"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "receipts_df_copy = receipts_df.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "receipts_df_copy['_id'] = receipts_df_copy['_id'].apply(lambda x: x['$oid'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Creating a function to convert all the **date columns** from **UTC to datetime format**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "def date_conv(x):\n",
    "    try:\n",
    "        return(datetime.utcfromtimestamp(int(x['$date'])/1000).strftime('%Y-%m-%d %H:%M:%S'))\n",
    "    except TypeError:\n",
    "        return(None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "receipts_df_copy['createDate'] = receipts_df_copy['createDate'].apply(lambda x: date_conv(x))\n",
    "receipts_df_copy['dateScanned'] = receipts_df_copy['dateScanned'].apply(lambda x: date_conv(x))\n",
    "receipts_df_copy['finishedDate'] = receipts_df_copy['finishedDate'].apply(lambda x: date_conv(x))\n",
    "receipts_df_copy['modifyDate'] = receipts_df_copy['modifyDate'].apply(lambda x: date_conv(x))\n",
    "receipts_df_copy['pointsAwardedDate'] = receipts_df_copy['pointsAwardedDate'].apply(lambda x: date_conv(x))\n",
    "receipts_df_copy['purchaseDate'] = receipts_df_copy['purchaseDate'].apply(lambda x: date_conv(x))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now most of the receipt dataframe columns are clean, only column left is *rewardReceiptItemList*. Working with this columns was complex as it has multiple field which itself could be a column.\n",
    "\n",
    "But there were many records which comprised of two records within the list. So we have to expand this list such that we have only one record per row.\n",
    "\n",
    "For example, **receipts_df_copy['rewardsReceiptItemList'][1]** this row has two records as we can see two entries for barcode variable.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>_id</th>\n",
       "      <th>bonusPointsEarned</th>\n",
       "      <th>bonusPointsEarnedReason</th>\n",
       "      <th>createDate</th>\n",
       "      <th>dateScanned</th>\n",
       "      <th>finishedDate</th>\n",
       "      <th>modifyDate</th>\n",
       "      <th>pointsAwardedDate</th>\n",
       "      <th>pointsEarned</th>\n",
       "      <th>purchaseDate</th>\n",
       "      <th>purchasedItemCount</th>\n",
       "      <th>rewardsReceiptItemList</th>\n",
       "      <th>rewardsReceiptStatus</th>\n",
       "      <th>totalSpent</th>\n",
       "      <th>userId</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5ff1e1eb0a720f0523000575</td>\n",
       "      <td>500.0</td>\n",
       "      <td>Receipt number 2 completed, bonus point schedu...</td>\n",
       "      <td>2021-01-03 15:25:31</td>\n",
       "      <td>2021-01-03 15:25:31</td>\n",
       "      <td>2021-01-03 15:25:31</td>\n",
       "      <td>2021-01-03 15:25:36</td>\n",
       "      <td>2021-01-03 15:25:31</td>\n",
       "      <td>500.0</td>\n",
       "      <td>2021-01-03 00:00:00</td>\n",
       "      <td>5.0</td>\n",
       "      <td>[{'barcode': '4011', 'description': 'ITEM NOT ...</td>\n",
       "      <td>FINISHED</td>\n",
       "      <td>26.0</td>\n",
       "      <td>5ff1e1eacfcf6c399c274ae6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5ff1e1bb0a720f052300056b</td>\n",
       "      <td>150.0</td>\n",
       "      <td>Receipt number 5 completed, bonus point schedu...</td>\n",
       "      <td>2021-01-03 15:24:43</td>\n",
       "      <td>2021-01-03 15:24:43</td>\n",
       "      <td>2021-01-03 15:24:43</td>\n",
       "      <td>2021-01-03 15:24:48</td>\n",
       "      <td>2021-01-03 15:24:43</td>\n",
       "      <td>150.0</td>\n",
       "      <td>2021-01-02 15:24:43</td>\n",
       "      <td>2.0</td>\n",
       "      <td>[{'barcode': '4011', 'description': 'ITEM NOT ...</td>\n",
       "      <td>FINISHED</td>\n",
       "      <td>11.0</td>\n",
       "      <td>5ff1e194b6a9d73a3a9f1052</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5ff1e1f10a720f052300057a</td>\n",
       "      <td>5.0</td>\n",
       "      <td>All-receipts receipt bonus</td>\n",
       "      <td>2021-01-03 15:25:37</td>\n",
       "      <td>2021-01-03 15:25:37</td>\n",
       "      <td>None</td>\n",
       "      <td>2021-01-03 15:25:42</td>\n",
       "      <td>None</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2021-01-03 00:00:00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>[{'needsFetchReview': False, 'partnerItemId': ...</td>\n",
       "      <td>REJECTED</td>\n",
       "      <td>10.0</td>\n",
       "      <td>5ff1e1f1cfcf6c399c274b0b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5ff1e1ee0a7214ada100056f</td>\n",
       "      <td>5.0</td>\n",
       "      <td>All-receipts receipt bonus</td>\n",
       "      <td>2021-01-03 15:25:34</td>\n",
       "      <td>2021-01-03 15:25:34</td>\n",
       "      <td>2021-01-03 15:25:34</td>\n",
       "      <td>2021-01-03 15:25:39</td>\n",
       "      <td>2021-01-03 15:25:34</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2021-01-03 00:00:00</td>\n",
       "      <td>4.0</td>\n",
       "      <td>[{'barcode': '4011', 'description': 'ITEM NOT ...</td>\n",
       "      <td>FINISHED</td>\n",
       "      <td>28.0</td>\n",
       "      <td>5ff1e1eacfcf6c399c274ae6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5ff1e1d20a7214ada1000561</td>\n",
       "      <td>5.0</td>\n",
       "      <td>All-receipts receipt bonus</td>\n",
       "      <td>2021-01-03 15:25:06</td>\n",
       "      <td>2021-01-03 15:25:06</td>\n",
       "      <td>2021-01-03 15:25:11</td>\n",
       "      <td>2021-01-03 15:25:11</td>\n",
       "      <td>2021-01-03 15:25:06</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2021-01-02 15:25:06</td>\n",
       "      <td>2.0</td>\n",
       "      <td>[{'barcode': '4011', 'description': 'ITEM NOT ...</td>\n",
       "      <td>FINISHED</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5ff1e194b6a9d73a3a9f1052</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        _id  bonusPointsEarned  \\\n",
       "0  5ff1e1eb0a720f0523000575              500.0   \n",
       "1  5ff1e1bb0a720f052300056b              150.0   \n",
       "2  5ff1e1f10a720f052300057a                5.0   \n",
       "3  5ff1e1ee0a7214ada100056f                5.0   \n",
       "4  5ff1e1d20a7214ada1000561                5.0   \n",
       "\n",
       "                             bonusPointsEarnedReason           createDate  \\\n",
       "0  Receipt number 2 completed, bonus point schedu...  2021-01-03 15:25:31   \n",
       "1  Receipt number 5 completed, bonus point schedu...  2021-01-03 15:24:43   \n",
       "2                         All-receipts receipt bonus  2021-01-03 15:25:37   \n",
       "3                         All-receipts receipt bonus  2021-01-03 15:25:34   \n",
       "4                         All-receipts receipt bonus  2021-01-03 15:25:06   \n",
       "\n",
       "           dateScanned         finishedDate           modifyDate  \\\n",
       "0  2021-01-03 15:25:31  2021-01-03 15:25:31  2021-01-03 15:25:36   \n",
       "1  2021-01-03 15:24:43  2021-01-03 15:24:43  2021-01-03 15:24:48   \n",
       "2  2021-01-03 15:25:37                 None  2021-01-03 15:25:42   \n",
       "3  2021-01-03 15:25:34  2021-01-03 15:25:34  2021-01-03 15:25:39   \n",
       "4  2021-01-03 15:25:06  2021-01-03 15:25:11  2021-01-03 15:25:11   \n",
       "\n",
       "     pointsAwardedDate  pointsEarned         purchaseDate  purchasedItemCount  \\\n",
       "0  2021-01-03 15:25:31         500.0  2021-01-03 00:00:00                 5.0   \n",
       "1  2021-01-03 15:24:43         150.0  2021-01-02 15:24:43                 2.0   \n",
       "2                 None           5.0  2021-01-03 00:00:00                 1.0   \n",
       "3  2021-01-03 15:25:34           5.0  2021-01-03 00:00:00                 4.0   \n",
       "4  2021-01-03 15:25:06           5.0  2021-01-02 15:25:06                 2.0   \n",
       "\n",
       "                              rewardsReceiptItemList rewardsReceiptStatus  \\\n",
       "0  [{'barcode': '4011', 'description': 'ITEM NOT ...             FINISHED   \n",
       "1  [{'barcode': '4011', 'description': 'ITEM NOT ...             FINISHED   \n",
       "2  [{'needsFetchReview': False, 'partnerItemId': ...             REJECTED   \n",
       "3  [{'barcode': '4011', 'description': 'ITEM NOT ...             FINISHED   \n",
       "4  [{'barcode': '4011', 'description': 'ITEM NOT ...             FINISHED   \n",
       "\n",
       "   totalSpent                    userId  \n",
       "0        26.0  5ff1e1eacfcf6c399c274ae6  \n",
       "1        11.0  5ff1e194b6a9d73a3a9f1052  \n",
       "2        10.0  5ff1e1f1cfcf6c399c274b0b  \n",
       "3        28.0  5ff1e1eacfcf6c399c274ae6  \n",
       "4         1.0  5ff1e194b6a9d73a3a9f1052  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "receipts_df_copy.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'barcode': '4011',\n",
       "  'description': 'ITEM NOT FOUND',\n",
       "  'finalPrice': '26.00',\n",
       "  'itemPrice': '26.00',\n",
       "  'needsFetchReview': False,\n",
       "  'partnerItemId': '1',\n",
       "  'preventTargetGapPoints': True,\n",
       "  'quantityPurchased': 5,\n",
       "  'userFlaggedBarcode': '4011',\n",
       "  'userFlaggedNewItem': True,\n",
       "  'userFlaggedPrice': '26.00',\n",
       "  'userFlaggedQuantity': 5}]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "receipts_df_copy['rewardsReceiptItemList'][0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We are using **df.explode() function** to transform each element from the list to a row data, also this will create duplicate values for all other column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "receipts_df_copy = receipts_df_copy.explode('rewardsReceiptItemList')\n",
    "receipts_df_copy.reset_index(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(7381, 16)"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "receipts_df_copy.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>_id</th>\n",
       "      <th>bonusPointsEarned</th>\n",
       "      <th>bonusPointsEarnedReason</th>\n",
       "      <th>createDate</th>\n",
       "      <th>dateScanned</th>\n",
       "      <th>finishedDate</th>\n",
       "      <th>modifyDate</th>\n",
       "      <th>pointsAwardedDate</th>\n",
       "      <th>pointsEarned</th>\n",
       "      <th>purchaseDate</th>\n",
       "      <th>purchasedItemCount</th>\n",
       "      <th>rewardsReceiptItemList</th>\n",
       "      <th>rewardsReceiptStatus</th>\n",
       "      <th>totalSpent</th>\n",
       "      <th>userId</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>5ff1e1eb0a720f0523000575</td>\n",
       "      <td>500.0</td>\n",
       "      <td>Receipt number 2 completed, bonus point schedu...</td>\n",
       "      <td>2021-01-03 15:25:31</td>\n",
       "      <td>2021-01-03 15:25:31</td>\n",
       "      <td>2021-01-03 15:25:31</td>\n",
       "      <td>2021-01-03 15:25:36</td>\n",
       "      <td>2021-01-03 15:25:31</td>\n",
       "      <td>500.0</td>\n",
       "      <td>2021-01-03 00:00:00</td>\n",
       "      <td>5.0</td>\n",
       "      <td>{'barcode': '4011', 'description': 'ITEM NOT F...</td>\n",
       "      <td>FINISHED</td>\n",
       "      <td>26.0</td>\n",
       "      <td>5ff1e1eacfcf6c399c274ae6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>5ff1e1bb0a720f052300056b</td>\n",
       "      <td>150.0</td>\n",
       "      <td>Receipt number 5 completed, bonus point schedu...</td>\n",
       "      <td>2021-01-03 15:24:43</td>\n",
       "      <td>2021-01-03 15:24:43</td>\n",
       "      <td>2021-01-03 15:24:43</td>\n",
       "      <td>2021-01-03 15:24:48</td>\n",
       "      <td>2021-01-03 15:24:43</td>\n",
       "      <td>150.0</td>\n",
       "      <td>2021-01-02 15:24:43</td>\n",
       "      <td>2.0</td>\n",
       "      <td>{'barcode': '4011', 'description': 'ITEM NOT F...</td>\n",
       "      <td>FINISHED</td>\n",
       "      <td>11.0</td>\n",
       "      <td>5ff1e194b6a9d73a3a9f1052</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>5ff1e1bb0a720f052300056b</td>\n",
       "      <td>150.0</td>\n",
       "      <td>Receipt number 5 completed, bonus point schedu...</td>\n",
       "      <td>2021-01-03 15:24:43</td>\n",
       "      <td>2021-01-03 15:24:43</td>\n",
       "      <td>2021-01-03 15:24:43</td>\n",
       "      <td>2021-01-03 15:24:48</td>\n",
       "      <td>2021-01-03 15:24:43</td>\n",
       "      <td>150.0</td>\n",
       "      <td>2021-01-02 15:24:43</td>\n",
       "      <td>2.0</td>\n",
       "      <td>{'barcode': '028400642255', 'description': 'DO...</td>\n",
       "      <td>FINISHED</td>\n",
       "      <td>11.0</td>\n",
       "      <td>5ff1e194b6a9d73a3a9f1052</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>5ff1e1f10a720f052300057a</td>\n",
       "      <td>5.0</td>\n",
       "      <td>All-receipts receipt bonus</td>\n",
       "      <td>2021-01-03 15:25:37</td>\n",
       "      <td>2021-01-03 15:25:37</td>\n",
       "      <td>None</td>\n",
       "      <td>2021-01-03 15:25:42</td>\n",
       "      <td>None</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2021-01-03 00:00:00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>{'needsFetchReview': False, 'partnerItemId': '...</td>\n",
       "      <td>REJECTED</td>\n",
       "      <td>10.0</td>\n",
       "      <td>5ff1e1f1cfcf6c399c274b0b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3</td>\n",
       "      <td>5ff1e1ee0a7214ada100056f</td>\n",
       "      <td>5.0</td>\n",
       "      <td>All-receipts receipt bonus</td>\n",
       "      <td>2021-01-03 15:25:34</td>\n",
       "      <td>2021-01-03 15:25:34</td>\n",
       "      <td>2021-01-03 15:25:34</td>\n",
       "      <td>2021-01-03 15:25:39</td>\n",
       "      <td>2021-01-03 15:25:34</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2021-01-03 00:00:00</td>\n",
       "      <td>4.0</td>\n",
       "      <td>{'barcode': '4011', 'description': 'ITEM NOT F...</td>\n",
       "      <td>FINISHED</td>\n",
       "      <td>28.0</td>\n",
       "      <td>5ff1e1eacfcf6c399c274ae6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index                       _id  bonusPointsEarned  \\\n",
       "0      0  5ff1e1eb0a720f0523000575              500.0   \n",
       "1      1  5ff1e1bb0a720f052300056b              150.0   \n",
       "2      1  5ff1e1bb0a720f052300056b              150.0   \n",
       "3      2  5ff1e1f10a720f052300057a                5.0   \n",
       "4      3  5ff1e1ee0a7214ada100056f                5.0   \n",
       "\n",
       "                             bonusPointsEarnedReason           createDate  \\\n",
       "0  Receipt number 2 completed, bonus point schedu...  2021-01-03 15:25:31   \n",
       "1  Receipt number 5 completed, bonus point schedu...  2021-01-03 15:24:43   \n",
       "2  Receipt number 5 completed, bonus point schedu...  2021-01-03 15:24:43   \n",
       "3                         All-receipts receipt bonus  2021-01-03 15:25:37   \n",
       "4                         All-receipts receipt bonus  2021-01-03 15:25:34   \n",
       "\n",
       "           dateScanned         finishedDate           modifyDate  \\\n",
       "0  2021-01-03 15:25:31  2021-01-03 15:25:31  2021-01-03 15:25:36   \n",
       "1  2021-01-03 15:24:43  2021-01-03 15:24:43  2021-01-03 15:24:48   \n",
       "2  2021-01-03 15:24:43  2021-01-03 15:24:43  2021-01-03 15:24:48   \n",
       "3  2021-01-03 15:25:37                 None  2021-01-03 15:25:42   \n",
       "4  2021-01-03 15:25:34  2021-01-03 15:25:34  2021-01-03 15:25:39   \n",
       "\n",
       "     pointsAwardedDate  pointsEarned         purchaseDate  purchasedItemCount  \\\n",
       "0  2021-01-03 15:25:31         500.0  2021-01-03 00:00:00                 5.0   \n",
       "1  2021-01-03 15:24:43         150.0  2021-01-02 15:24:43                 2.0   \n",
       "2  2021-01-03 15:24:43         150.0  2021-01-02 15:24:43                 2.0   \n",
       "3                 None           5.0  2021-01-03 00:00:00                 1.0   \n",
       "4  2021-01-03 15:25:34           5.0  2021-01-03 00:00:00                 4.0   \n",
       "\n",
       "                              rewardsReceiptItemList rewardsReceiptStatus  \\\n",
       "0  {'barcode': '4011', 'description': 'ITEM NOT F...             FINISHED   \n",
       "1  {'barcode': '4011', 'description': 'ITEM NOT F...             FINISHED   \n",
       "2  {'barcode': '028400642255', 'description': 'DO...             FINISHED   \n",
       "3  {'needsFetchReview': False, 'partnerItemId': '...             REJECTED   \n",
       "4  {'barcode': '4011', 'description': 'ITEM NOT F...             FINISHED   \n",
       "\n",
       "   totalSpent                    userId  \n",
       "0        26.0  5ff1e1eacfcf6c399c274ae6  \n",
       "1        11.0  5ff1e194b6a9d73a3a9f1052  \n",
       "2        11.0  5ff1e194b6a9d73a3a9f1052  \n",
       "3        10.0  5ff1e1f1cfcf6c399c274b0b  \n",
       "4        28.0  5ff1e1eacfcf6c399c274ae6  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "receipts_df_copy.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "index                         0\n",
       "_id                           0\n",
       "bonusPointsEarned          1401\n",
       "bonusPointsEarnedReason    1401\n",
       "createDate                    0\n",
       "dateScanned                   0\n",
       "finishedDate               1411\n",
       "modifyDate                    0\n",
       "pointsAwardedDate          1301\n",
       "pointsEarned               1128\n",
       "purchaseDate                458\n",
       "purchasedItemCount          484\n",
       "rewardsReceiptItemList      440\n",
       "rewardsReceiptStatus          0\n",
       "totalSpent                  435\n",
       "userId                        0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "receipts_df_copy.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We are filling NAN with '{}' value for *rewardsReceiptItemList* column, followed by converting each value to be a string and finally using literal_eval, to find the data type of the content stored in this case it is dict."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "receipts_df_copy = receipts_df_copy.fillna({'rewardsReceiptItemList':'{}'})\n",
    "receipts_df_copy['rewardsReceiptItemList'] = receipts_df_copy['rewardsReceiptItemList'].apply(lambda x:str(x))\n",
    "receipts_df_copy['rewardsReceiptItemList'] = receipts_df_copy['rewardsReceiptItemList'].apply(literal_eval)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     {'barcode': '4011', 'description': 'ITEM NOT F...\n",
       "1     {'barcode': '4011', 'description': 'ITEM NOT F...\n",
       "2     {'barcode': '028400642255', 'description': 'DO...\n",
       "3     {'needsFetchReview': False, 'partnerItemId': '...\n",
       "4     {'barcode': '4011', 'description': 'ITEM NOT F...\n",
       "5     {'barcode': '4011', 'description': 'ITEM NOT F...\n",
       "6     {'barcode': '1234', 'finalPrice': '2.56', 'ite...\n",
       "7     {'barcode': '4011', 'description': 'ITEM NOT F...\n",
       "8     {'brandCode': 'MISSION', 'competitorRewardsGro...\n",
       "9     {'barcode': '046000832517', 'brandCode': 'BRAN...\n",
       "10    {'barcode': '4011', 'description': 'ITEM NOT F...\n",
       "11    {'barcode': '4011', 'description': 'ITEM NOT F...\n",
       "12    {'barcode': '4011', 'description': 'ITEM NOT F...\n",
       "13    {'barcode': '013562300631', 'description': 'An...\n",
       "14    {'barcode': '034100573065', 'description': 'MI...\n",
       "15    {'barcode': '034100573065', 'description': 'MI...\n",
       "16    {'barcode': '034100573065', 'description': 'MI...\n",
       "17    {'barcode': '034100573065', 'description': 'MI...\n",
       "18    {'barcode': '034100573065', 'description': 'MI...\n",
       "19    {'barcode': '034100573065', 'description': 'MI...\n",
       "Name: rewardsReceiptItemList, dtype: object"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "receipts_df_copy['rewardsReceiptItemList'][:20]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Again as we used ** pandas.json_normalize() ** method to convert semi-structured JSON to a flat table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "receipts_Reward_norm = pd.json_normalize(receipts_df_copy['rewardsReceiptItemList'],errors='ignore',record_prefix='rewardsReceiptItemList').add_prefix('rewardsReceiptItemList_')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>rewardsReceiptItemList_barcode</th>\n",
       "      <th>rewardsReceiptItemList_description</th>\n",
       "      <th>rewardsReceiptItemList_finalPrice</th>\n",
       "      <th>rewardsReceiptItemList_itemPrice</th>\n",
       "      <th>rewardsReceiptItemList_needsFetchReview</th>\n",
       "      <th>rewardsReceiptItemList_partnerItemId</th>\n",
       "      <th>rewardsReceiptItemList_preventTargetGapPoints</th>\n",
       "      <th>rewardsReceiptItemList_quantityPurchased</th>\n",
       "      <th>rewardsReceiptItemList_userFlaggedBarcode</th>\n",
       "      <th>rewardsReceiptItemList_userFlaggedNewItem</th>\n",
       "      <th>...</th>\n",
       "      <th>rewardsReceiptItemList_itemNumber</th>\n",
       "      <th>rewardsReceiptItemList_originalMetaBriteQuantityPurchased</th>\n",
       "      <th>rewardsReceiptItemList_pointsEarned</th>\n",
       "      <th>rewardsReceiptItemList_targetPrice</th>\n",
       "      <th>rewardsReceiptItemList_competitiveProduct</th>\n",
       "      <th>rewardsReceiptItemList_originalFinalPrice</th>\n",
       "      <th>rewardsReceiptItemList_originalMetaBriteItemPrice</th>\n",
       "      <th>rewardsReceiptItemList_deleted</th>\n",
       "      <th>rewardsReceiptItemList_priceAfterCoupon</th>\n",
       "      <th>rewardsReceiptItemList_metabriteCampaignId</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4011</td>\n",
       "      <td>ITEM NOT FOUND</td>\n",
       "      <td>26.00</td>\n",
       "      <td>26.00</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "      <td>True</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4011</td>\n",
       "      <td>True</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4011</td>\n",
       "      <td>ITEM NOT FOUND</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>028400642255</td>\n",
       "      <td>DORITOS TORTILLA CHIP SPICY SWEET CHILI REDUCE...</td>\n",
       "      <td>10.00</td>\n",
       "      <td>10.00</td>\n",
       "      <td>True</td>\n",
       "      <td>2</td>\n",
       "      <td>True</td>\n",
       "      <td>1.0</td>\n",
       "      <td>028400642255</td>\n",
       "      <td>True</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "      <td>True</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4011</td>\n",
       "      <td>True</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4011</td>\n",
       "      <td>ITEM NOT FOUND</td>\n",
       "      <td>28.00</td>\n",
       "      <td>28.00</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "      <td>True</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4011</td>\n",
       "      <td>True</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>NaN</td>\n",
       "      <td>NaN</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",
       "      <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>7376</th>\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>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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7377</th>\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>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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7378</th>\n",
       "      <td>B076FJ92M4</td>\n",
       "      <td>mueller austria hypergrind precision electric ...</td>\n",
       "      <td>22.97</td>\n",
       "      <td>22.97</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</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>22.97</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7379</th>\n",
       "      <td>B07BRRLSVC</td>\n",
       "      <td>thindust summer face mask - sun protection nec...</td>\n",
       "      <td>11.99</td>\n",
       "      <td>11.99</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</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>11.99</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7380</th>\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>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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7381 rows × 34 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     rewardsReceiptItemList_barcode  \\\n",
       "0                              4011   \n",
       "1                              4011   \n",
       "2                      028400642255   \n",
       "3                               NaN   \n",
       "4                              4011   \n",
       "...                             ...   \n",
       "7376                            NaN   \n",
       "7377                            NaN   \n",
       "7378                     B076FJ92M4   \n",
       "7379                     B07BRRLSVC   \n",
       "7380                            NaN   \n",
       "\n",
       "                     rewardsReceiptItemList_description  \\\n",
       "0                                        ITEM NOT FOUND   \n",
       "1                                        ITEM NOT FOUND   \n",
       "2     DORITOS TORTILLA CHIP SPICY SWEET CHILI REDUCE...   \n",
       "3                                                   NaN   \n",
       "4                                        ITEM NOT FOUND   \n",
       "...                                                 ...   \n",
       "7376                                                NaN   \n",
       "7377                                                NaN   \n",
       "7378  mueller austria hypergrind precision electric ...   \n",
       "7379  thindust summer face mask - sun protection nec...   \n",
       "7380                                                NaN   \n",
       "\n",
       "     rewardsReceiptItemList_finalPrice rewardsReceiptItemList_itemPrice  \\\n",
       "0                                26.00                            26.00   \n",
       "1                                    1                                1   \n",
       "2                                10.00                            10.00   \n",
       "3                                  NaN                              NaN   \n",
       "4                                28.00                            28.00   \n",
       "...                                ...                              ...   \n",
       "7376                               NaN                              NaN   \n",
       "7377                               NaN                              NaN   \n",
       "7378                             22.97                            22.97   \n",
       "7379                             11.99                            11.99   \n",
       "7380                               NaN                              NaN   \n",
       "\n",
       "     rewardsReceiptItemList_needsFetchReview  \\\n",
       "0                                      False   \n",
       "1                                        NaN   \n",
       "2                                       True   \n",
       "3                                      False   \n",
       "4                                      False   \n",
       "...                                      ...   \n",
       "7376                                     NaN   \n",
       "7377                                     NaN   \n",
       "7378                                     NaN   \n",
       "7379                                     NaN   \n",
       "7380                                     NaN   \n",
       "\n",
       "     rewardsReceiptItemList_partnerItemId  \\\n",
       "0                                       1   \n",
       "1                                       1   \n",
       "2                                       2   \n",
       "3                                       1   \n",
       "4                                       1   \n",
       "...                                   ...   \n",
       "7376                                  NaN   \n",
       "7377                                  NaN   \n",
       "7378                                    0   \n",
       "7379                                    1   \n",
       "7380                                  NaN   \n",
       "\n",
       "     rewardsReceiptItemList_preventTargetGapPoints  \\\n",
       "0                                             True   \n",
       "1                                              NaN   \n",
       "2                                             True   \n",
       "3                                             True   \n",
       "4                                             True   \n",
       "...                                            ...   \n",
       "7376                                           NaN   \n",
       "7377                                           NaN   \n",
       "7378                                           NaN   \n",
       "7379                                           NaN   \n",
       "7380                                           NaN   \n",
       "\n",
       "      rewardsReceiptItemList_quantityPurchased  \\\n",
       "0                                          5.0   \n",
       "1                                          1.0   \n",
       "2                                          1.0   \n",
       "3                                          NaN   \n",
       "4                                          4.0   \n",
       "...                                        ...   \n",
       "7376                                       NaN   \n",
       "7377                                       NaN   \n",
       "7378                                       1.0   \n",
       "7379                                       1.0   \n",
       "7380                                       NaN   \n",
       "\n",
       "     rewardsReceiptItemList_userFlaggedBarcode  \\\n",
       "0                                         4011   \n",
       "1                                          NaN   \n",
       "2                                 028400642255   \n",
       "3                                         4011   \n",
       "4                                         4011   \n",
       "...                                        ...   \n",
       "7376                                       NaN   \n",
       "7377                                       NaN   \n",
       "7378                                       NaN   \n",
       "7379                                       NaN   \n",
       "7380                                       NaN   \n",
       "\n",
       "     rewardsReceiptItemList_userFlaggedNewItem  ...  \\\n",
       "0                                         True  ...   \n",
       "1                                          NaN  ...   \n",
       "2                                         True  ...   \n",
       "3                                         True  ...   \n",
       "4                                         True  ...   \n",
       "...                                        ...  ...   \n",
       "7376                                       NaN  ...   \n",
       "7377                                       NaN  ...   \n",
       "7378                                       NaN  ...   \n",
       "7379                                       NaN  ...   \n",
       "7380                                       NaN  ...   \n",
       "\n",
       "     rewardsReceiptItemList_itemNumber  \\\n",
       "0                                  NaN   \n",
       "1                                  NaN   \n",
       "2                                  NaN   \n",
       "3                                  NaN   \n",
       "4                                  NaN   \n",
       "...                                ...   \n",
       "7376                               NaN   \n",
       "7377                               NaN   \n",
       "7378                               NaN   \n",
       "7379                               NaN   \n",
       "7380                               NaN   \n",
       "\n",
       "      rewardsReceiptItemList_originalMetaBriteQuantityPurchased  \\\n",
       "0                                                   NaN           \n",
       "1                                                   NaN           \n",
       "2                                                   NaN           \n",
       "3                                                   NaN           \n",
       "4                                                   NaN           \n",
       "...                                                 ...           \n",
       "7376                                                NaN           \n",
       "7377                                                NaN           \n",
       "7378                                                NaN           \n",
       "7379                                                NaN           \n",
       "7380                                                NaN           \n",
       "\n",
       "     rewardsReceiptItemList_pointsEarned rewardsReceiptItemList_targetPrice  \\\n",
       "0                                    NaN                                NaN   \n",
       "1                                    NaN                                NaN   \n",
       "2                                    NaN                                NaN   \n",
       "3                                    NaN                                NaN   \n",
       "4                                    NaN                                NaN   \n",
       "...                                  ...                                ...   \n",
       "7376                                 NaN                                NaN   \n",
       "7377                                 NaN                                NaN   \n",
       "7378                                 NaN                                NaN   \n",
       "7379                                 NaN                                NaN   \n",
       "7380                                 NaN                                NaN   \n",
       "\n",
       "     rewardsReceiptItemList_competitiveProduct  \\\n",
       "0                                          NaN   \n",
       "1                                          NaN   \n",
       "2                                          NaN   \n",
       "3                                          NaN   \n",
       "4                                          NaN   \n",
       "...                                        ...   \n",
       "7376                                       NaN   \n",
       "7377                                       NaN   \n",
       "7378                                       NaN   \n",
       "7379                                       NaN   \n",
       "7380                                       NaN   \n",
       "\n",
       "     rewardsReceiptItemList_originalFinalPrice  \\\n",
       "0                                          NaN   \n",
       "1                                          NaN   \n",
       "2                                          NaN   \n",
       "3                                          NaN   \n",
       "4                                          NaN   \n",
       "...                                        ...   \n",
       "7376                                       NaN   \n",
       "7377                                       NaN   \n",
       "7378                                       NaN   \n",
       "7379                                       NaN   \n",
       "7380                                       NaN   \n",
       "\n",
       "     rewardsReceiptItemList_originalMetaBriteItemPrice  \\\n",
       "0                                                  NaN   \n",
       "1                                                  NaN   \n",
       "2                                                  NaN   \n",
       "3                                                  NaN   \n",
       "4                                                  NaN   \n",
       "...                                                ...   \n",
       "7376                                               NaN   \n",
       "7377                                               NaN   \n",
       "7378                                               NaN   \n",
       "7379                                               NaN   \n",
       "7380                                               NaN   \n",
       "\n",
       "     rewardsReceiptItemList_deleted rewardsReceiptItemList_priceAfterCoupon  \\\n",
       "0                               NaN                                     NaN   \n",
       "1                               NaN                                     NaN   \n",
       "2                               NaN                                     NaN   \n",
       "3                               NaN                                     NaN   \n",
       "4                               NaN                                     NaN   \n",
       "...                             ...                                     ...   \n",
       "7376                            NaN                                     NaN   \n",
       "7377                            NaN                                     NaN   \n",
       "7378                            NaN                                   22.97   \n",
       "7379                            NaN                                   11.99   \n",
       "7380                            NaN                                     NaN   \n",
       "\n",
       "     rewardsReceiptItemList_metabriteCampaignId  \n",
       "0                                           NaN  \n",
       "1                                           NaN  \n",
       "2                                           NaN  \n",
       "3                                           NaN  \n",
       "4                                           NaN  \n",
       "...                                         ...  \n",
       "7376                                        NaN  \n",
       "7377                                        NaN  \n",
       "7378                                        NaN  \n",
       "7379                                        NaN  \n",
       "7380                                        NaN  \n",
       "\n",
       "[7381 rows x 34 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "receipts_Reward_norm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4011            177\n",
       "036000320893     92\n",
       "034100573065     90\n",
       "036000391718     87\n",
       "012000809941     76\n",
       "               ... \n",
       "4562              1\n",
       "021000701445      1\n",
       "021000026999      1\n",
       "686924560242      1\n",
       "665290001184      1\n",
       "Name: rewardsReceiptItemList_barcode, Length: 568, dtype: int64"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "receipts_Reward_norm['rewardsReceiptItemList_barcode'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "receipts_df_copy.drop(columns=['index','rewardsReceiptItemList'],inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Merging dummy and normalized dataframe and creating a final clean dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(7381, 48)"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "receipts_final = pd.merge(receipts_df_copy, receipts_Reward_norm, left_index=True, right_index=True, how='outer')\n",
    "receipts_final.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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>_id</th>\n",
       "      <th>bonusPointsEarned</th>\n",
       "      <th>bonusPointsEarnedReason</th>\n",
       "      <th>createDate</th>\n",
       "      <th>dateScanned</th>\n",
       "      <th>finishedDate</th>\n",
       "      <th>modifyDate</th>\n",
       "      <th>pointsAwardedDate</th>\n",
       "      <th>pointsEarned</th>\n",
       "      <th>purchaseDate</th>\n",
       "      <th>...</th>\n",
       "      <th>rewardsReceiptItemList_itemNumber</th>\n",
       "      <th>rewardsReceiptItemList_originalMetaBriteQuantityPurchased</th>\n",
       "      <th>rewardsReceiptItemList_pointsEarned</th>\n",
       "      <th>rewardsReceiptItemList_targetPrice</th>\n",
       "      <th>rewardsReceiptItemList_competitiveProduct</th>\n",
       "      <th>rewardsReceiptItemList_originalFinalPrice</th>\n",
       "      <th>rewardsReceiptItemList_originalMetaBriteItemPrice</th>\n",
       "      <th>rewardsReceiptItemList_deleted</th>\n",
       "      <th>rewardsReceiptItemList_priceAfterCoupon</th>\n",
       "      <th>rewardsReceiptItemList_metabriteCampaignId</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5ff1e1eb0a720f0523000575</td>\n",
       "      <td>500.0</td>\n",
       "      <td>Receipt number 2 completed, bonus point schedu...</td>\n",
       "      <td>2021-01-03 15:25:31</td>\n",
       "      <td>2021-01-03 15:25:31</td>\n",
       "      <td>2021-01-03 15:25:31</td>\n",
       "      <td>2021-01-03 15:25:36</td>\n",
       "      <td>2021-01-03 15:25:31</td>\n",
       "      <td>500.0</td>\n",
       "      <td>2021-01-03 00:00:00</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5ff1e1bb0a720f052300056b</td>\n",
       "      <td>150.0</td>\n",
       "      <td>Receipt number 5 completed, bonus point schedu...</td>\n",
       "      <td>2021-01-03 15:24:43</td>\n",
       "      <td>2021-01-03 15:24:43</td>\n",
       "      <td>2021-01-03 15:24:43</td>\n",
       "      <td>2021-01-03 15:24:48</td>\n",
       "      <td>2021-01-03 15:24:43</td>\n",
       "      <td>150.0</td>\n",
       "      <td>2021-01-02 15:24:43</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5ff1e1bb0a720f052300056b</td>\n",
       "      <td>150.0</td>\n",
       "      <td>Receipt number 5 completed, bonus point schedu...</td>\n",
       "      <td>2021-01-03 15:24:43</td>\n",
       "      <td>2021-01-03 15:24:43</td>\n",
       "      <td>2021-01-03 15:24:43</td>\n",
       "      <td>2021-01-03 15:24:48</td>\n",
       "      <td>2021-01-03 15:24:43</td>\n",
       "      <td>150.0</td>\n",
       "      <td>2021-01-02 15:24:43</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5ff1e1f10a720f052300057a</td>\n",
       "      <td>5.0</td>\n",
       "      <td>All-receipts receipt bonus</td>\n",
       "      <td>2021-01-03 15:25:37</td>\n",
       "      <td>2021-01-03 15:25:37</td>\n",
       "      <td>None</td>\n",
       "      <td>2021-01-03 15:25:42</td>\n",
       "      <td>None</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2021-01-03 00:00:00</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5ff1e1ee0a7214ada100056f</td>\n",
       "      <td>5.0</td>\n",
       "      <td>All-receipts receipt bonus</td>\n",
       "      <td>2021-01-03 15:25:34</td>\n",
       "      <td>2021-01-03 15:25:34</td>\n",
       "      <td>2021-01-03 15:25:34</td>\n",
       "      <td>2021-01-03 15:25:39</td>\n",
       "      <td>2021-01-03 15:25:34</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2021-01-03 00:00:00</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5ff1e1d20a7214ada1000561</td>\n",
       "      <td>5.0</td>\n",
       "      <td>All-receipts receipt bonus</td>\n",
       "      <td>2021-01-03 15:25:06</td>\n",
       "      <td>2021-01-03 15:25:06</td>\n",
       "      <td>2021-01-03 15:25:11</td>\n",
       "      <td>2021-01-03 15:25:11</td>\n",
       "      <td>2021-01-03 15:25:06</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2021-01-02 15:25:06</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>5ff1e1d20a7214ada1000561</td>\n",
       "      <td>5.0</td>\n",
       "      <td>All-receipts receipt bonus</td>\n",
       "      <td>2021-01-03 15:25:06</td>\n",
       "      <td>2021-01-03 15:25:06</td>\n",
       "      <td>2021-01-03 15:25:11</td>\n",
       "      <td>2021-01-03 15:25:11</td>\n",
       "      <td>2021-01-03 15:25:06</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2021-01-02 15:25:06</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>5ff1e1e40a7214ada1000566</td>\n",
       "      <td>750.0</td>\n",
       "      <td>Receipt number 1 completed, bonus point schedu...</td>\n",
       "      <td>2021-01-03 15:25:24</td>\n",
       "      <td>2021-01-03 15:25:24</td>\n",
       "      <td>2021-01-03 15:25:25</td>\n",
       "      <td>2021-01-03 15:25:30</td>\n",
       "      <td>2021-01-03 15:25:25</td>\n",
       "      <td>750.0</td>\n",
       "      <td>2021-01-02 15:25:24</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>5ff1e1cd0a720f052300056f</td>\n",
       "      <td>5.0</td>\n",
       "      <td>All-receipts receipt bonus</td>\n",
       "      <td>2021-01-03 15:25:01</td>\n",
       "      <td>2021-01-03 15:25:01</td>\n",
       "      <td>2021-01-03 15:25:02</td>\n",
       "      <td>2021-01-03 15:25:02</td>\n",
       "      <td>2021-01-03 15:25:02</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2021-01-03 15:25:01</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>5ff1e1a40a720f0523000569</td>\n",
       "      <td>500.0</td>\n",
       "      <td>Receipt number 2 completed, bonus point schedu...</td>\n",
       "      <td>2021-01-03 15:24:20</td>\n",
       "      <td>2021-01-03 15:24:20</td>\n",
       "      <td>2021-01-03 15:24:21</td>\n",
       "      <td>2021-01-03 15:24:21</td>\n",
       "      <td>2021-01-03 15:24:21</td>\n",
       "      <td>500.0</td>\n",
       "      <td>2020-12-27 00:00:00</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>5ff1e1ed0a7214ada100056e</td>\n",
       "      <td>5.0</td>\n",
       "      <td>All-receipts receipt bonus</td>\n",
       "      <td>2021-01-03 15:25:33</td>\n",
       "      <td>2021-01-03 15:25:33</td>\n",
       "      <td>2021-01-03 15:25:34</td>\n",
       "      <td>2021-01-03 15:25:38</td>\n",
       "      <td>2021-01-03 15:25:34</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2021-01-03 00:00:00</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>5ff1e1eb0a7214ada100056b</td>\n",
       "      <td>250.0</td>\n",
       "      <td>Receipt number 3 completed, bonus point schedu...</td>\n",
       "      <td>2021-01-03 15:25:31</td>\n",
       "      <td>2021-01-03 15:25:31</td>\n",
       "      <td>2021-01-03 15:25:31</td>\n",
       "      <td>2021-01-03 15:25:36</td>\n",
       "      <td>2021-01-03 15:25:31</td>\n",
       "      <td>250.0</td>\n",
       "      <td>2021-01-03 00:00:00</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>5ff1e1c50a720f052300056c</td>\n",
       "      <td>100.0</td>\n",
       "      <td>Receipt number 6 completed, bonus point schedu...</td>\n",
       "      <td>2021-01-03 15:24:53</td>\n",
       "      <td>2021-01-03 15:24:53</td>\n",
       "      <td>2021-01-03 15:24:53</td>\n",
       "      <td>2021-01-03 15:24:53</td>\n",
       "      <td>2021-01-03 15:24:53</td>\n",
       "      <td>100.0</td>\n",
       "      <td>2021-01-02 15:24:53</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>5ff1e1a10a720f0523000568</td>\n",
       "      <td>750.0</td>\n",
       "      <td>Receipt number 1 completed, bonus point schedu...</td>\n",
       "      <td>2021-01-03 15:24:17</td>\n",
       "      <td>2021-01-03 15:24:17</td>\n",
       "      <td>2021-01-03 15:39:20</td>\n",
       "      <td>2021-01-03 15:39:20</td>\n",
       "      <td>2021-01-03 15:24:19</td>\n",
       "      <td>750.0</td>\n",
       "      <td>2021-01-02 00:00:00</td>\n",
       "      <td>...</td>\n",
       "      <td>013562300631</td>\n",
       "      <td>1.0</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>5ff1e1b60a7214ada100055c</td>\n",
       "      <td>150.0</td>\n",
       "      <td>Receipt number 5 completed, bonus point schedu...</td>\n",
       "      <td>2021-01-03 15:24:38</td>\n",
       "      <td>2021-01-03 15:24:38</td>\n",
       "      <td>None</td>\n",
       "      <td>2021-01-03 15:24:38</td>\n",
       "      <td>None</td>\n",
       "      <td>8850.0</td>\n",
       "      <td>2021-02-03 15:24:38</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>870.0</td>\n",
       "      <td>77</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>5ff1e1b60a7214ada100055c</td>\n",
       "      <td>150.0</td>\n",
       "      <td>Receipt number 5 completed, bonus point schedu...</td>\n",
       "      <td>2021-01-03 15:24:38</td>\n",
       "      <td>2021-01-03 15:24:38</td>\n",
       "      <td>None</td>\n",
       "      <td>2021-01-03 15:24:38</td>\n",
       "      <td>None</td>\n",
       "      <td>8850.0</td>\n",
       "      <td>2021-02-03 15:24:38</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>870.0</td>\n",
       "      <td>77</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>5ff1e1b60a7214ada100055c</td>\n",
       "      <td>150.0</td>\n",
       "      <td>Receipt number 5 completed, bonus point schedu...</td>\n",
       "      <td>2021-01-03 15:24:38</td>\n",
       "      <td>2021-01-03 15:24:38</td>\n",
       "      <td>None</td>\n",
       "      <td>2021-01-03 15:24:38</td>\n",
       "      <td>None</td>\n",
       "      <td>8850.0</td>\n",
       "      <td>2021-02-03 15:24:38</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>870.0</td>\n",
       "      <td>77</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>5ff1e1b60a7214ada100055c</td>\n",
       "      <td>150.0</td>\n",
       "      <td>Receipt number 5 completed, bonus point schedu...</td>\n",
       "      <td>2021-01-03 15:24:38</td>\n",
       "      <td>2021-01-03 15:24:38</td>\n",
       "      <td>None</td>\n",
       "      <td>2021-01-03 15:24:38</td>\n",
       "      <td>None</td>\n",
       "      <td>8850.0</td>\n",
       "      <td>2021-02-03 15:24:38</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>870.0</td>\n",
       "      <td>77</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>5ff1e1b60a7214ada100055c</td>\n",
       "      <td>150.0</td>\n",
       "      <td>Receipt number 5 completed, bonus point schedu...</td>\n",
       "      <td>2021-01-03 15:24:38</td>\n",
       "      <td>2021-01-03 15:24:38</td>\n",
       "      <td>None</td>\n",
       "      <td>2021-01-03 15:24:38</td>\n",
       "      <td>None</td>\n",
       "      <td>8850.0</td>\n",
       "      <td>2021-02-03 15:24:38</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>870.0</td>\n",
       "      <td>77</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>5ff1e1b60a7214ada100055c</td>\n",
       "      <td>150.0</td>\n",
       "      <td>Receipt number 5 completed, bonus point schedu...</td>\n",
       "      <td>2021-01-03 15:24:38</td>\n",
       "      <td>2021-01-03 15:24:38</td>\n",
       "      <td>None</td>\n",
       "      <td>2021-01-03 15:24:38</td>\n",
       "      <td>None</td>\n",
       "      <td>8850.0</td>\n",
       "      <td>2021-02-03 15:24:38</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>870.0</td>\n",
       "      <td>77</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>5ff1e1b60a7214ada100055c</td>\n",
       "      <td>150.0</td>\n",
       "      <td>Receipt number 5 completed, bonus point schedu...</td>\n",
       "      <td>2021-01-03 15:24:38</td>\n",
       "      <td>2021-01-03 15:24:38</td>\n",
       "      <td>None</td>\n",
       "      <td>2021-01-03 15:24:38</td>\n",
       "      <td>None</td>\n",
       "      <td>8850.0</td>\n",
       "      <td>2021-02-03 15:24:38</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>870.0</td>\n",
       "      <td>77</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>5ff1e1b60a7214ada100055c</td>\n",
       "      <td>150.0</td>\n",
       "      <td>Receipt number 5 completed, bonus point schedu...</td>\n",
       "      <td>2021-01-03 15:24:38</td>\n",
       "      <td>2021-01-03 15:24:38</td>\n",
       "      <td>None</td>\n",
       "      <td>2021-01-03 15:24:38</td>\n",
       "      <td>None</td>\n",
       "      <td>8850.0</td>\n",
       "      <td>2021-02-03 15:24:38</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>870.0</td>\n",
       "      <td>77</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>5ff1e1b60a7214ada100055c</td>\n",
       "      <td>150.0</td>\n",
       "      <td>Receipt number 5 completed, bonus point schedu...</td>\n",
       "      <td>2021-01-03 15:24:38</td>\n",
       "      <td>2021-01-03 15:24:38</td>\n",
       "      <td>None</td>\n",
       "      <td>2021-01-03 15:24:38</td>\n",
       "      <td>None</td>\n",
       "      <td>8850.0</td>\n",
       "      <td>2021-02-03 15:24:38</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>870.0</td>\n",
       "      <td>77</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>5ff1e1b60a7214ada100055c</td>\n",
       "      <td>150.0</td>\n",
       "      <td>Receipt number 5 completed, bonus point schedu...</td>\n",
       "      <td>2021-01-03 15:24:38</td>\n",
       "      <td>2021-01-03 15:24:38</td>\n",
       "      <td>None</td>\n",
       "      <td>2021-01-03 15:24:38</td>\n",
       "      <td>None</td>\n",
       "      <td>8850.0</td>\n",
       "      <td>2021-02-03 15:24:38</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>870.0</td>\n",
       "      <td>77</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>5f9c74f70a7214ad07000037</td>\n",
       "      <td>750.0</td>\n",
       "      <td>Receipt number 1 completed, bonus point schedu...</td>\n",
       "      <td>2020-10-30 20:17:59</td>\n",
       "      <td>2020-10-30 20:17:59</td>\n",
       "      <td>None</td>\n",
       "      <td>2021-01-03 15:24:54</td>\n",
       "      <td>2020-10-30 20:18:00</td>\n",
       "      <td>750.0</td>\n",
       "      <td>2020-10-29 20:17:59</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>5f9c74f70a7214ad07000037</td>\n",
       "      <td>750.0</td>\n",
       "      <td>Receipt number 1 completed, bonus point schedu...</td>\n",
       "      <td>2020-10-30 20:17:59</td>\n",
       "      <td>2020-10-30 20:17:59</td>\n",
       "      <td>None</td>\n",
       "      <td>2021-01-03 15:24:54</td>\n",
       "      <td>2020-10-30 20:18:00</td>\n",
       "      <td>750.0</td>\n",
       "      <td>2020-10-29 20:17:59</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>5f9c74f70a7214ad07000037</td>\n",
       "      <td>750.0</td>\n",
       "      <td>Receipt number 1 completed, bonus point schedu...</td>\n",
       "      <td>2020-10-30 20:17:59</td>\n",
       "      <td>2020-10-30 20:17:59</td>\n",
       "      <td>None</td>\n",
       "      <td>2021-01-03 15:24:54</td>\n",
       "      <td>2020-10-30 20:18:00</td>\n",
       "      <td>750.0</td>\n",
       "      <td>2020-10-29 20:17:59</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>5f9c74f70a7214ad07000037</td>\n",
       "      <td>750.0</td>\n",
       "      <td>Receipt number 1 completed, bonus point schedu...</td>\n",
       "      <td>2020-10-30 20:17:59</td>\n",
       "      <td>2020-10-30 20:17:59</td>\n",
       "      <td>None</td>\n",
       "      <td>2021-01-03 15:24:54</td>\n",
       "      <td>2020-10-30 20:18:00</td>\n",
       "      <td>750.0</td>\n",
       "      <td>2020-10-29 20:17:59</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>5f9c74f70a7214ad07000037</td>\n",
       "      <td>750.0</td>\n",
       "      <td>Receipt number 1 completed, bonus point schedu...</td>\n",
       "      <td>2020-10-30 20:17:59</td>\n",
       "      <td>2020-10-30 20:17:59</td>\n",
       "      <td>None</td>\n",
       "      <td>2021-01-03 15:24:54</td>\n",
       "      <td>2020-10-30 20:18:00</td>\n",
       "      <td>750.0</td>\n",
       "      <td>2020-10-29 20:17:59</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>5f9c74f70a7214ad07000037</td>\n",
       "      <td>750.0</td>\n",
       "      <td>Receipt number 1 completed, bonus point schedu...</td>\n",
       "      <td>2020-10-30 20:17:59</td>\n",
       "      <td>2020-10-30 20:17:59</td>\n",
       "      <td>None</td>\n",
       "      <td>2021-01-03 15:24:54</td>\n",
       "      <td>2020-10-30 20:18:00</td>\n",
       "      <td>750.0</td>\n",
       "      <td>2020-10-29 20:17:59</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>5f9c74f70a7214ad07000037</td>\n",
       "      <td>750.0</td>\n",
       "      <td>Receipt number 1 completed, bonus point schedu...</td>\n",
       "      <td>2020-10-30 20:17:59</td>\n",
       "      <td>2020-10-30 20:17:59</td>\n",
       "      <td>None</td>\n",
       "      <td>2021-01-03 15:24:54</td>\n",
       "      <td>2020-10-30 20:18:00</td>\n",
       "      <td>750.0</td>\n",
       "      <td>2020-10-29 20:17:59</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>5f9c74f70a7214ad07000037</td>\n",
       "      <td>750.0</td>\n",
       "      <td>Receipt number 1 completed, bonus point schedu...</td>\n",
       "      <td>2020-10-30 20:17:59</td>\n",
       "      <td>2020-10-30 20:17:59</td>\n",
       "      <td>None</td>\n",
       "      <td>2021-01-03 15:24:54</td>\n",
       "      <td>2020-10-30 20:18:00</td>\n",
       "      <td>750.0</td>\n",
       "      <td>2020-10-29 20:17:59</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>5f9c74f70a7214ad07000037</td>\n",
       "      <td>750.0</td>\n",
       "      <td>Receipt number 1 completed, bonus point schedu...</td>\n",
       "      <td>2020-10-30 20:17:59</td>\n",
       "      <td>2020-10-30 20:17:59</td>\n",
       "      <td>None</td>\n",
       "      <td>2021-01-03 15:24:54</td>\n",
       "      <td>2020-10-30 20:18:00</td>\n",
       "      <td>750.0</td>\n",
       "      <td>2020-10-29 20:17:59</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>5f9c74f70a7214ad07000037</td>\n",
       "      <td>750.0</td>\n",
       "      <td>Receipt number 1 completed, bonus point schedu...</td>\n",
       "      <td>2020-10-30 20:17:59</td>\n",
       "      <td>2020-10-30 20:17:59</td>\n",
       "      <td>None</td>\n",
       "      <td>2021-01-03 15:24:54</td>\n",
       "      <td>2020-10-30 20:18:00</td>\n",
       "      <td>750.0</td>\n",
       "      <td>2020-10-29 20:17:59</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>5f9c74f70a7214ad07000037</td>\n",
       "      <td>750.0</td>\n",
       "      <td>Receipt number 1 completed, bonus point schedu...</td>\n",
       "      <td>2020-10-30 20:17:59</td>\n",
       "      <td>2020-10-30 20:17:59</td>\n",
       "      <td>None</td>\n",
       "      <td>2021-01-03 15:24:54</td>\n",
       "      <td>2020-10-30 20:18:00</td>\n",
       "      <td>750.0</td>\n",
       "      <td>2020-10-29 20:17:59</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>5ff1e1b20a7214ada100055a</td>\n",
       "      <td>300.0</td>\n",
       "      <td>Receipt number 4 completed, bonus point schedu...</td>\n",
       "      <td>2021-01-03 15:24:34</td>\n",
       "      <td>2021-01-03 15:24:34</td>\n",
       "      <td>2021-01-03 15:24:35</td>\n",
       "      <td>2021-01-03 15:24:35</td>\n",
       "      <td>2021-01-03 15:24:35</td>\n",
       "      <td>300.0</td>\n",
       "      <td>2021-02-03 15:24:35</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>5ff1e1e90a7214ada1000569</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-01-03 15:25:29</td>\n",
       "      <td>2021-01-03 15:25:29</td>\n",
       "      <td>None</td>\n",
       "      <td>2021-01-03 15:25:29</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>5ff1e1e90a7214ada1000569</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-01-03 15:25:29</td>\n",
       "      <td>2021-01-03 15:25:29</td>\n",
       "      <td>None</td>\n",
       "      <td>2021-01-03 15:25:29</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>5ff1e1df0a7214ada1000564</td>\n",
       "      <td>750.0</td>\n",
       "      <td>Receipt number 1 completed, bonus point schedu...</td>\n",
       "      <td>2021-01-03 15:25:19</td>\n",
       "      <td>2021-01-03 15:25:19</td>\n",
       "      <td>2021-01-03 15:25:20</td>\n",
       "      <td>2021-01-03 15:25:25</td>\n",
       "      <td>2021-01-03 15:25:19</td>\n",
       "      <td>750.0</td>\n",
       "      <td>2021-01-02 15:25:19</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>5ff1e1df0a7214ada1000564</td>\n",
       "      <td>750.0</td>\n",
       "      <td>Receipt number 1 completed, bonus point schedu...</td>\n",
       "      <td>2021-01-03 15:25:19</td>\n",
       "      <td>2021-01-03 15:25:19</td>\n",
       "      <td>2021-01-03 15:25:20</td>\n",
       "      <td>2021-01-03 15:25:25</td>\n",
       "      <td>2021-01-03 15:25:19</td>\n",
       "      <td>750.0</td>\n",
       "      <td>2021-01-02 15:25:19</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>800</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",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>40 rows × 48 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                         _id  bonusPointsEarned  \\\n",
       "0   5ff1e1eb0a720f0523000575              500.0   \n",
       "1   5ff1e1bb0a720f052300056b              150.0   \n",
       "2   5ff1e1bb0a720f052300056b              150.0   \n",
       "3   5ff1e1f10a720f052300057a                5.0   \n",
       "4   5ff1e1ee0a7214ada100056f                5.0   \n",
       "5   5ff1e1d20a7214ada1000561                5.0   \n",
       "6   5ff1e1d20a7214ada1000561                5.0   \n",
       "7   5ff1e1e40a7214ada1000566              750.0   \n",
       "8   5ff1e1cd0a720f052300056f                5.0   \n",
       "9   5ff1e1a40a720f0523000569              500.0   \n",
       "10  5ff1e1ed0a7214ada100056e                5.0   \n",
       "11  5ff1e1eb0a7214ada100056b              250.0   \n",
       "12  5ff1e1c50a720f052300056c              100.0   \n",
       "13  5ff1e1a10a720f0523000568              750.0   \n",
       "14  5ff1e1b60a7214ada100055c              150.0   \n",
       "15  5ff1e1b60a7214ada100055c              150.0   \n",
       "16  5ff1e1b60a7214ada100055c              150.0   \n",
       "17  5ff1e1b60a7214ada100055c              150.0   \n",
       "18  5ff1e1b60a7214ada100055c              150.0   \n",
       "19  5ff1e1b60a7214ada100055c              150.0   \n",
       "20  5ff1e1b60a7214ada100055c              150.0   \n",
       "21  5ff1e1b60a7214ada100055c              150.0   \n",
       "22  5ff1e1b60a7214ada100055c              150.0   \n",
       "23  5ff1e1b60a7214ada100055c              150.0   \n",
       "24  5f9c74f70a7214ad07000037              750.0   \n",
       "25  5f9c74f70a7214ad07000037              750.0   \n",
       "26  5f9c74f70a7214ad07000037              750.0   \n",
       "27  5f9c74f70a7214ad07000037              750.0   \n",
       "28  5f9c74f70a7214ad07000037              750.0   \n",
       "29  5f9c74f70a7214ad07000037              750.0   \n",
       "30  5f9c74f70a7214ad07000037              750.0   \n",
       "31  5f9c74f70a7214ad07000037              750.0   \n",
       "32  5f9c74f70a7214ad07000037              750.0   \n",
       "33  5f9c74f70a7214ad07000037              750.0   \n",
       "34  5f9c74f70a7214ad07000037              750.0   \n",
       "35  5ff1e1b20a7214ada100055a              300.0   \n",
       "36  5ff1e1e90a7214ada1000569                NaN   \n",
       "37  5ff1e1e90a7214ada1000569                NaN   \n",
       "38  5ff1e1df0a7214ada1000564              750.0   \n",
       "39  5ff1e1df0a7214ada1000564              750.0   \n",
       "\n",
       "                              bonusPointsEarnedReason           createDate  \\\n",
       "0   Receipt number 2 completed, bonus point schedu...  2021-01-03 15:25:31   \n",
       "1   Receipt number 5 completed, bonus point schedu...  2021-01-03 15:24:43   \n",
       "2   Receipt number 5 completed, bonus point schedu...  2021-01-03 15:24:43   \n",
       "3                          All-receipts receipt bonus  2021-01-03 15:25:37   \n",
       "4                          All-receipts receipt bonus  2021-01-03 15:25:34   \n",
       "5                          All-receipts receipt bonus  2021-01-03 15:25:06   \n",
       "6                          All-receipts receipt bonus  2021-01-03 15:25:06   \n",
       "7   Receipt number 1 completed, bonus point schedu...  2021-01-03 15:25:24   \n",
       "8                          All-receipts receipt bonus  2021-01-03 15:25:01   \n",
       "9   Receipt number 2 completed, bonus point schedu...  2021-01-03 15:24:20   \n",
       "10                         All-receipts receipt bonus  2021-01-03 15:25:33   \n",
       "11  Receipt number 3 completed, bonus point schedu...  2021-01-03 15:25:31   \n",
       "12  Receipt number 6 completed, bonus point schedu...  2021-01-03 15:24:53   \n",
       "13  Receipt number 1 completed, bonus point schedu...  2021-01-03 15:24:17   \n",
       "14  Receipt number 5 completed, bonus point schedu...  2021-01-03 15:24:38   \n",
       "15  Receipt number 5 completed, bonus point schedu...  2021-01-03 15:24:38   \n",
       "16  Receipt number 5 completed, bonus point schedu...  2021-01-03 15:24:38   \n",
       "17  Receipt number 5 completed, bonus point schedu...  2021-01-03 15:24:38   \n",
       "18  Receipt number 5 completed, bonus point schedu...  2021-01-03 15:24:38   \n",
       "19  Receipt number 5 completed, bonus point schedu...  2021-01-03 15:24:38   \n",
       "20  Receipt number 5 completed, bonus point schedu...  2021-01-03 15:24:38   \n",
       "21  Receipt number 5 completed, bonus point schedu...  2021-01-03 15:24:38   \n",
       "22  Receipt number 5 completed, bonus point schedu...  2021-01-03 15:24:38   \n",
       "23  Receipt number 5 completed, bonus point schedu...  2021-01-03 15:24:38   \n",
       "24  Receipt number 1 completed, bonus point schedu...  2020-10-30 20:17:59   \n",
       "25  Receipt number 1 completed, bonus point schedu...  2020-10-30 20:17:59   \n",
       "26  Receipt number 1 completed, bonus point schedu...  2020-10-30 20:17:59   \n",
       "27  Receipt number 1 completed, bonus point schedu...  2020-10-30 20:17:59   \n",
       "28  Receipt number 1 completed, bonus point schedu...  2020-10-30 20:17:59   \n",
       "29  Receipt number 1 completed, bonus point schedu...  2020-10-30 20:17:59   \n",
       "30  Receipt number 1 completed, bonus point schedu...  2020-10-30 20:17:59   \n",
       "31  Receipt number 1 completed, bonus point schedu...  2020-10-30 20:17:59   \n",
       "32  Receipt number 1 completed, bonus point schedu...  2020-10-30 20:17:59   \n",
       "33  Receipt number 1 completed, bonus point schedu...  2020-10-30 20:17:59   \n",
       "34  Receipt number 1 completed, bonus point schedu...  2020-10-30 20:17:59   \n",
       "35  Receipt number 4 completed, bonus point schedu...  2021-01-03 15:24:34   \n",
       "36                                                NaN  2021-01-03 15:25:29   \n",
       "37                                                NaN  2021-01-03 15:25:29   \n",
       "38  Receipt number 1 completed, bonus point schedu...  2021-01-03 15:25:19   \n",
       "39  Receipt number 1 completed, bonus point schedu...  2021-01-03 15:25:19   \n",
       "\n",
       "            dateScanned         finishedDate           modifyDate  \\\n",
       "0   2021-01-03 15:25:31  2021-01-03 15:25:31  2021-01-03 15:25:36   \n",
       "1   2021-01-03 15:24:43  2021-01-03 15:24:43  2021-01-03 15:24:48   \n",
       "2   2021-01-03 15:24:43  2021-01-03 15:24:43  2021-01-03 15:24:48   \n",
       "3   2021-01-03 15:25:37                 None  2021-01-03 15:25:42   \n",
       "4   2021-01-03 15:25:34  2021-01-03 15:25:34  2021-01-03 15:25:39   \n",
       "5   2021-01-03 15:25:06  2021-01-03 15:25:11  2021-01-03 15:25:11   \n",
       "6   2021-01-03 15:25:06  2021-01-03 15:25:11  2021-01-03 15:25:11   \n",
       "7   2021-01-03 15:25:24  2021-01-03 15:25:25  2021-01-03 15:25:30   \n",
       "8   2021-01-03 15:25:01  2021-01-03 15:25:02  2021-01-03 15:25:02   \n",
       "9   2021-01-03 15:24:20  2021-01-03 15:24:21  2021-01-03 15:24:21   \n",
       "10  2021-01-03 15:25:33  2021-01-03 15:25:34  2021-01-03 15:25:38   \n",
       "11  2021-01-03 15:25:31  2021-01-03 15:25:31  2021-01-03 15:25:36   \n",
       "12  2021-01-03 15:24:53  2021-01-03 15:24:53  2021-01-03 15:24:53   \n",
       "13  2021-01-03 15:24:17  2021-01-03 15:39:20  2021-01-03 15:39:20   \n",
       "14  2021-01-03 15:24:38                 None  2021-01-03 15:24:38   \n",
       "15  2021-01-03 15:24:38                 None  2021-01-03 15:24:38   \n",
       "16  2021-01-03 15:24:38                 None  2021-01-03 15:24:38   \n",
       "17  2021-01-03 15:24:38                 None  2021-01-03 15:24:38   \n",
       "18  2021-01-03 15:24:38                 None  2021-01-03 15:24:38   \n",
       "19  2021-01-03 15:24:38                 None  2021-01-03 15:24:38   \n",
       "20  2021-01-03 15:24:38                 None  2021-01-03 15:24:38   \n",
       "21  2021-01-03 15:24:38                 None  2021-01-03 15:24:38   \n",
       "22  2021-01-03 15:24:38                 None  2021-01-03 15:24:38   \n",
       "23  2021-01-03 15:24:38                 None  2021-01-03 15:24:38   \n",
       "24  2020-10-30 20:17:59                 None  2021-01-03 15:24:54   \n",
       "25  2020-10-30 20:17:59                 None  2021-01-03 15:24:54   \n",
       "26  2020-10-30 20:17:59                 None  2021-01-03 15:24:54   \n",
       "27  2020-10-30 20:17:59                 None  2021-01-03 15:24:54   \n",
       "28  2020-10-30 20:17:59                 None  2021-01-03 15:24:54   \n",
       "29  2020-10-30 20:17:59                 None  2021-01-03 15:24:54   \n",
       "30  2020-10-30 20:17:59                 None  2021-01-03 15:24:54   \n",
       "31  2020-10-30 20:17:59                 None  2021-01-03 15:24:54   \n",
       "32  2020-10-30 20:17:59                 None  2021-01-03 15:24:54   \n",
       "33  2020-10-30 20:17:59                 None  2021-01-03 15:24:54   \n",
       "34  2020-10-30 20:17:59                 None  2021-01-03 15:24:54   \n",
       "35  2021-01-03 15:24:34  2021-01-03 15:24:35  2021-01-03 15:24:35   \n",
       "36  2021-01-03 15:25:29                 None  2021-01-03 15:25:29   \n",
       "37  2021-01-03 15:25:29                 None  2021-01-03 15:25:29   \n",
       "38  2021-01-03 15:25:19  2021-01-03 15:25:20  2021-01-03 15:25:25   \n",
       "39  2021-01-03 15:25:19  2021-01-03 15:25:20  2021-01-03 15:25:25   \n",
       "\n",
       "      pointsAwardedDate  pointsEarned         purchaseDate  ...  \\\n",
       "0   2021-01-03 15:25:31         500.0  2021-01-03 00:00:00  ...   \n",
       "1   2021-01-03 15:24:43         150.0  2021-01-02 15:24:43  ...   \n",
       "2   2021-01-03 15:24:43         150.0  2021-01-02 15:24:43  ...   \n",
       "3                  None           5.0  2021-01-03 00:00:00  ...   \n",
       "4   2021-01-03 15:25:34           5.0  2021-01-03 00:00:00  ...   \n",
       "5   2021-01-03 15:25:06           5.0  2021-01-02 15:25:06  ...   \n",
       "6   2021-01-03 15:25:06           5.0  2021-01-02 15:25:06  ...   \n",
       "7   2021-01-03 15:25:25         750.0  2021-01-02 15:25:24  ...   \n",
       "8   2021-01-03 15:25:02           5.0  2021-01-03 15:25:01  ...   \n",
       "9   2021-01-03 15:24:21         500.0  2020-12-27 00:00:00  ...   \n",
       "10  2021-01-03 15:25:34           5.0  2021-01-03 00:00:00  ...   \n",
       "11  2021-01-03 15:25:31         250.0  2021-01-03 00:00:00  ...   \n",
       "12  2021-01-03 15:24:53         100.0  2021-01-02 15:24:53  ...   \n",
       "13  2021-01-03 15:24:19         750.0  2021-01-02 00:00:00  ...   \n",
       "14                 None        8850.0  2021-02-03 15:24:38  ...   \n",
       "15                 None        8850.0  2021-02-03 15:24:38  ...   \n",
       "16                 None        8850.0  2021-02-03 15:24:38  ...   \n",
       "17                 None        8850.0  2021-02-03 15:24:38  ...   \n",
       "18                 None        8850.0  2021-02-03 15:24:38  ...   \n",
       "19                 None        8850.0  2021-02-03 15:24:38  ...   \n",
       "20                 None        8850.0  2021-02-03 15:24:38  ...   \n",
       "21                 None        8850.0  2021-02-03 15:24:38  ...   \n",
       "22                 None        8850.0  2021-02-03 15:24:38  ...   \n",
       "23                 None        8850.0  2021-02-03 15:24:38  ...   \n",
       "24  2020-10-30 20:18:00         750.0  2020-10-29 20:17:59  ...   \n",
       "25  2020-10-30 20:18:00         750.0  2020-10-29 20:17:59  ...   \n",
       "26  2020-10-30 20:18:00         750.0  2020-10-29 20:17:59  ...   \n",
       "27  2020-10-30 20:18:00         750.0  2020-10-29 20:17:59  ...   \n",
       "28  2020-10-30 20:18:00         750.0  2020-10-29 20:17:59  ...   \n",
       "29  2020-10-30 20:18:00         750.0  2020-10-29 20:17:59  ...   \n",
       "30  2020-10-30 20:18:00         750.0  2020-10-29 20:17:59  ...   \n",
       "31  2020-10-30 20:18:00         750.0  2020-10-29 20:17:59  ...   \n",
       "32  2020-10-30 20:18:00         750.0  2020-10-29 20:17:59  ...   \n",
       "33  2020-10-30 20:18:00         750.0  2020-10-29 20:17:59  ...   \n",
       "34  2020-10-30 20:18:00         750.0  2020-10-29 20:17:59  ...   \n",
       "35  2021-01-03 15:24:35         300.0  2021-02-03 15:24:35  ...   \n",
       "36                 None           NaN                 None  ...   \n",
       "37                 None           NaN                 None  ...   \n",
       "38  2021-01-03 15:25:19         750.0  2021-01-02 15:25:19  ...   \n",
       "39  2021-01-03 15:25:19         750.0  2021-01-02 15:25:19  ...   \n",
       "\n",
       "    rewardsReceiptItemList_itemNumber  \\\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                                 NaN   \n",
       "9                                 NaN   \n",
       "10                                NaN   \n",
       "11                                NaN   \n",
       "12                                NaN   \n",
       "13                       013562300631   \n",
       "14                                NaN   \n",
       "15                                NaN   \n",
       "16                                NaN   \n",
       "17                                NaN   \n",
       "18                                NaN   \n",
       "19                                NaN   \n",
       "20                                NaN   \n",
       "21                                NaN   \n",
       "22                                NaN   \n",
       "23                                NaN   \n",
       "24                                NaN   \n",
       "25                                NaN   \n",
       "26                                NaN   \n",
       "27                                NaN   \n",
       "28                                NaN   \n",
       "29                                NaN   \n",
       "30                                NaN   \n",
       "31                                NaN   \n",
       "32                                NaN   \n",
       "33                                NaN   \n",
       "34                                NaN   \n",
       "35                                NaN   \n",
       "36                                NaN   \n",
       "37                                NaN   \n",
       "38                                NaN   \n",
       "39                                NaN   \n",
       "\n",
       "   rewardsReceiptItemList_originalMetaBriteQuantityPurchased  \\\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                                                 NaN          \n",
       "9                                                 NaN          \n",
       "10                                                NaN          \n",
       "11                                                NaN          \n",
       "12                                                NaN          \n",
       "13                                                1.0          \n",
       "14                                                NaN          \n",
       "15                                                NaN          \n",
       "16                                                NaN          \n",
       "17                                                NaN          \n",
       "18                                                NaN          \n",
       "19                                                NaN          \n",
       "20                                                NaN          \n",
       "21                                                NaN          \n",
       "22                                                NaN          \n",
       "23                                                NaN          \n",
       "24                                                NaN          \n",
       "25                                                NaN          \n",
       "26                                                NaN          \n",
       "27                                                NaN          \n",
       "28                                                NaN          \n",
       "29                                                NaN          \n",
       "30                                                NaN          \n",
       "31                                                NaN          \n",
       "32                                                NaN          \n",
       "33                                                NaN          \n",
       "34                                                NaN          \n",
       "35                                                NaN          \n",
       "36                                                NaN          \n",
       "37                                                NaN          \n",
       "38                                                NaN          \n",
       "39                                                NaN          \n",
       "\n",
       "    rewardsReceiptItemList_pointsEarned rewardsReceiptItemList_targetPrice  \\\n",
       "0                                   NaN                                NaN   \n",
       "1                                   NaN                                NaN   \n",
       "2                                   NaN                                NaN   \n",
       "3                                   NaN                                NaN   \n",
       "4                                   NaN                                NaN   \n",
       "5                                   NaN                                NaN   \n",
       "6                                   NaN                                NaN   \n",
       "7                                   NaN                                NaN   \n",
       "8                                   NaN                                NaN   \n",
       "9                                   NaN                                NaN   \n",
       "10                                  NaN                                NaN   \n",
       "11                                  NaN                                NaN   \n",
       "12                                  NaN                                NaN   \n",
       "13                                  NaN                                NaN   \n",
       "14                                870.0                                 77   \n",
       "15                                870.0                                 77   \n",
       "16                                870.0                                 77   \n",
       "17                                870.0                                 77   \n",
       "18                                870.0                                 77   \n",
       "19                                870.0                                 77   \n",
       "20                                870.0                                 77   \n",
       "21                                870.0                                 77   \n",
       "22                                870.0                                 77   \n",
       "23                                870.0                                 77   \n",
       "24                                  NaN                                NaN   \n",
       "25                                  NaN                                NaN   \n",
       "26                                  NaN                                NaN   \n",
       "27                                  NaN                                NaN   \n",
       "28                                  NaN                                NaN   \n",
       "29                                  NaN                                NaN   \n",
       "30                                  NaN                                NaN   \n",
       "31                                  NaN                                NaN   \n",
       "32                                  NaN                                NaN   \n",
       "33                                  NaN                                NaN   \n",
       "34                                  NaN                                NaN   \n",
       "35                                  NaN                                NaN   \n",
       "36                                  NaN                                NaN   \n",
       "37                                  NaN                                NaN   \n",
       "38                                  NaN                                NaN   \n",
       "39                                  NaN                                800   \n",
       "\n",
       "   rewardsReceiptItemList_competitiveProduct  \\\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                                        NaN   \n",
       "9                                        NaN   \n",
       "10                                       NaN   \n",
       "11                                       NaN   \n",
       "12                                       NaN   \n",
       "13                                       NaN   \n",
       "14                                       NaN   \n",
       "15                                       NaN   \n",
       "16                                       NaN   \n",
       "17                                       NaN   \n",
       "18                                       NaN   \n",
       "19                                       NaN   \n",
       "20                                       NaN   \n",
       "21                                       NaN   \n",
       "22                                       NaN   \n",
       "23                                       NaN   \n",
       "24                                      True   \n",
       "25                                       NaN   \n",
       "26                                       NaN   \n",
       "27                                       NaN   \n",
       "28                                       NaN   \n",
       "29                                       NaN   \n",
       "30                                       NaN   \n",
       "31                                       NaN   \n",
       "32                                       NaN   \n",
       "33                                       NaN   \n",
       "34                                       NaN   \n",
       "35                                       NaN   \n",
       "36                                       NaN   \n",
       "37                                       NaN   \n",
       "38                                       NaN   \n",
       "39                                       NaN   \n",
       "\n",
       "   rewardsReceiptItemList_originalFinalPrice  \\\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                                        NaN   \n",
       "9                                        NaN   \n",
       "10                                       NaN   \n",
       "11                                       NaN   \n",
       "12                                       NaN   \n",
       "13                                       NaN   \n",
       "14                                       NaN   \n",
       "15                                       NaN   \n",
       "16                                       NaN   \n",
       "17                                       NaN   \n",
       "18                                       NaN   \n",
       "19                                       NaN   \n",
       "20                                       NaN   \n",
       "21                                       NaN   \n",
       "22                                       NaN   \n",
       "23                                       NaN   \n",
       "24                                       NaN   \n",
       "25                                       NaN   \n",
       "26                                       NaN   \n",
       "27                                       NaN   \n",
       "28                                       NaN   \n",
       "29                                       NaN   \n",
       "30                                       NaN   \n",
       "31                                       NaN   \n",
       "32                                       NaN   \n",
       "33                                       NaN   \n",
       "34                                       NaN   \n",
       "35                                       NaN   \n",
       "36                                       NaN   \n",
       "37                                       NaN   \n",
       "38                                       NaN   \n",
       "39                                       NaN   \n",
       "\n",
       "   rewardsReceiptItemList_originalMetaBriteItemPrice  \\\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                                                NaN   \n",
       "9                                                NaN   \n",
       "10                                               NaN   \n",
       "11                                               NaN   \n",
       "12                                               NaN   \n",
       "13                                               NaN   \n",
       "14                                               NaN   \n",
       "15                                               NaN   \n",
       "16                                               NaN   \n",
       "17                                               NaN   \n",
       "18                                               NaN   \n",
       "19                                               NaN   \n",
       "20                                               NaN   \n",
       "21                                               NaN   \n",
       "22                                               NaN   \n",
       "23                                               NaN   \n",
       "24                                               NaN   \n",
       "25                                               NaN   \n",
       "26                                               NaN   \n",
       "27                                               NaN   \n",
       "28                                               NaN   \n",
       "29                                               NaN   \n",
       "30                                               NaN   \n",
       "31                                               NaN   \n",
       "32                                               NaN   \n",
       "33                                               NaN   \n",
       "34                                               NaN   \n",
       "35                                               NaN   \n",
       "36                                               NaN   \n",
       "37                                               NaN   \n",
       "38                                               NaN   \n",
       "39                                               NaN   \n",
       "\n",
       "   rewardsReceiptItemList_deleted rewardsReceiptItemList_priceAfterCoupon  \\\n",
       "0                             NaN                                     NaN   \n",
       "1                             NaN                                     NaN   \n",
       "2                             NaN                                     NaN   \n",
       "3                             NaN                                     NaN   \n",
       "4                             NaN                                     NaN   \n",
       "5                             NaN                                     NaN   \n",
       "6                             NaN                                     NaN   \n",
       "7                             NaN                                     NaN   \n",
       "8                             NaN                                     NaN   \n",
       "9                             NaN                                     NaN   \n",
       "10                            NaN                                     NaN   \n",
       "11                            NaN                                     NaN   \n",
       "12                            NaN                                     NaN   \n",
       "13                            NaN                                     NaN   \n",
       "14                            NaN                                     NaN   \n",
       "15                            NaN                                     NaN   \n",
       "16                            NaN                                     NaN   \n",
       "17                            NaN                                     NaN   \n",
       "18                            NaN                                     NaN   \n",
       "19                            NaN                                     NaN   \n",
       "20                            NaN                                     NaN   \n",
       "21                            NaN                                     NaN   \n",
       "22                            NaN                                     NaN   \n",
       "23                            NaN                                     NaN   \n",
       "24                            NaN                                     NaN   \n",
       "25                            NaN                                     NaN   \n",
       "26                            NaN                                     NaN   \n",
       "27                            NaN                                     NaN   \n",
       "28                            NaN                                     NaN   \n",
       "29                            NaN                                     NaN   \n",
       "30                            NaN                                     NaN   \n",
       "31                            NaN                                     NaN   \n",
       "32                            NaN                                     NaN   \n",
       "33                            NaN                                     NaN   \n",
       "34                            NaN                                     NaN   \n",
       "35                            NaN                                     NaN   \n",
       "36                            NaN                                     NaN   \n",
       "37                            NaN                                     NaN   \n",
       "38                            NaN                                     NaN   \n",
       "39                            NaN                                     NaN   \n",
       "\n",
       "   rewardsReceiptItemList_metabriteCampaignId  \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                                         NaN  \n",
       "9                                         NaN  \n",
       "10                                        NaN  \n",
       "11                                        NaN  \n",
       "12                                        NaN  \n",
       "13                                        NaN  \n",
       "14                                        NaN  \n",
       "15                                        NaN  \n",
       "16                                        NaN  \n",
       "17                                        NaN  \n",
       "18                                        NaN  \n",
       "19                                        NaN  \n",
       "20                                        NaN  \n",
       "21                                        NaN  \n",
       "22                                        NaN  \n",
       "23                                        NaN  \n",
       "24                                        NaN  \n",
       "25                                        NaN  \n",
       "26                                        NaN  \n",
       "27                                        NaN  \n",
       "28                                        NaN  \n",
       "29                                        NaN  \n",
       "30                                        NaN  \n",
       "31                                        NaN  \n",
       "32                                        NaN  \n",
       "33                                        NaN  \n",
       "34                                        NaN  \n",
       "35                                        NaN  \n",
       "36                                        NaN  \n",
       "37                                        NaN  \n",
       "38                                        NaN  \n",
       "39                                        NaN  \n",
       "\n",
       "[40 rows x 48 columns]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "receipts_final.head(40)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['_id', 'bonusPointsEarned', 'bonusPointsEarnedReason', 'createDate',\n",
       "       'dateScanned', 'finishedDate', 'modifyDate', 'pointsAwardedDate',\n",
       "       'pointsEarned', 'purchaseDate', 'purchasedItemCount',\n",
       "       'rewardsReceiptStatus', 'totalSpent', 'userId',\n",
       "       'rewardsReceiptItemList_barcode', 'rewardsReceiptItemList_description',\n",
       "       'rewardsReceiptItemList_finalPrice', 'rewardsReceiptItemList_itemPrice',\n",
       "       'rewardsReceiptItemList_needsFetchReview',\n",
       "       'rewardsReceiptItemList_partnerItemId',\n",
       "       'rewardsReceiptItemList_preventTargetGapPoints',\n",
       "       'rewardsReceiptItemList_quantityPurchased',\n",
       "       'rewardsReceiptItemList_userFlaggedBarcode',\n",
       "       'rewardsReceiptItemList_userFlaggedNewItem',\n",
       "       'rewardsReceiptItemList_userFlaggedPrice',\n",
       "       'rewardsReceiptItemList_userFlaggedQuantity',\n",
       "       'rewardsReceiptItemList_needsFetchReviewReason',\n",
       "       'rewardsReceiptItemList_pointsNotAwardedReason',\n",
       "       'rewardsReceiptItemList_pointsPayerId',\n",
       "       'rewardsReceiptItemList_rewardsGroup',\n",
       "       'rewardsReceiptItemList_rewardsProductPartnerId',\n",
       "       'rewardsReceiptItemList_userFlaggedDescription',\n",
       "       'rewardsReceiptItemList_originalMetaBriteBarcode',\n",
       "       'rewardsReceiptItemList_originalMetaBriteDescription',\n",
       "       'rewardsReceiptItemList_brandCode',\n",
       "       'rewardsReceiptItemList_competitorRewardsGroup',\n",
       "       'rewardsReceiptItemList_discountedItemPrice',\n",
       "       'rewardsReceiptItemList_originalReceiptItemText',\n",
       "       'rewardsReceiptItemList_itemNumber',\n",
       "       'rewardsReceiptItemList_originalMetaBriteQuantityPurchased',\n",
       "       'rewardsReceiptItemList_pointsEarned',\n",
       "       'rewardsReceiptItemList_targetPrice',\n",
       "       'rewardsReceiptItemList_competitiveProduct',\n",
       "       'rewardsReceiptItemList_originalFinalPrice',\n",
       "       'rewardsReceiptItemList_originalMetaBriteItemPrice',\n",
       "       'rewardsReceiptItemList_deleted',\n",
       "       'rewardsReceiptItemList_priceAfterCoupon',\n",
       "       'rewardsReceiptItemList_metabriteCampaignId'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "receipts_final.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Users_DF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "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>_id</th>\n",
       "      <th>active</th>\n",
       "      <th>createdDate</th>\n",
       "      <th>lastLogin</th>\n",
       "      <th>role</th>\n",
       "      <th>signUpSource</th>\n",
       "      <th>state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>{'$oid': '5ff1e194b6a9d73a3a9f1052'}</td>\n",
       "      <td>True</td>\n",
       "      <td>{'$date': 1609687444800}</td>\n",
       "      <td>{'$date': 1609687537858}</td>\n",
       "      <td>consumer</td>\n",
       "      <td>Email</td>\n",
       "      <td>WI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>{'$oid': '5ff1e194b6a9d73a3a9f1052'}</td>\n",
       "      <td>True</td>\n",
       "      <td>{'$date': 1609687444800}</td>\n",
       "      <td>{'$date': 1609687537858}</td>\n",
       "      <td>consumer</td>\n",
       "      <td>Email</td>\n",
       "      <td>WI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>{'$oid': '5ff1e194b6a9d73a3a9f1052'}</td>\n",
       "      <td>True</td>\n",
       "      <td>{'$date': 1609687444800}</td>\n",
       "      <td>{'$date': 1609687537858}</td>\n",
       "      <td>consumer</td>\n",
       "      <td>Email</td>\n",
       "      <td>WI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>{'$oid': '5ff1e1eacfcf6c399c274ae6'}</td>\n",
       "      <td>True</td>\n",
       "      <td>{'$date': 1609687530554}</td>\n",
       "      <td>{'$date': 1609687530597}</td>\n",
       "      <td>consumer</td>\n",
       "      <td>Email</td>\n",
       "      <td>WI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>{'$oid': '5ff1e194b6a9d73a3a9f1052'}</td>\n",
       "      <td>True</td>\n",
       "      <td>{'$date': 1609687444800}</td>\n",
       "      <td>{'$date': 1609687537858}</td>\n",
       "      <td>consumer</td>\n",
       "      <td>Email</td>\n",
       "      <td>WI</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    _id  active               createdDate  \\\n",
       "0  {'$oid': '5ff1e194b6a9d73a3a9f1052'}    True  {'$date': 1609687444800}   \n",
       "1  {'$oid': '5ff1e194b6a9d73a3a9f1052'}    True  {'$date': 1609687444800}   \n",
       "2  {'$oid': '5ff1e194b6a9d73a3a9f1052'}    True  {'$date': 1609687444800}   \n",
       "3  {'$oid': '5ff1e1eacfcf6c399c274ae6'}    True  {'$date': 1609687530554}   \n",
       "4  {'$oid': '5ff1e194b6a9d73a3a9f1052'}    True  {'$date': 1609687444800}   \n",
       "\n",
       "                  lastLogin      role signUpSource state  \n",
       "0  {'$date': 1609687537858}  consumer        Email    WI  \n",
       "1  {'$date': 1609687537858}  consumer        Email    WI  \n",
       "2  {'$date': 1609687537858}  consumer        Email    WI  \n",
       "3  {'$date': 1609687530597}  consumer        Email    WI  \n",
       "4  {'$date': 1609687537858}  consumer        Email    WI  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "users_final = users_df.copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using same data cleaning method to clean this dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "users_final['_id'] = users_final['_id'].apply(lambda x: x['$oid'])\n",
    "users_final['createdDate'] = users_final['createdDate'].apply(lambda x: date_conv(x))\n",
    "users_final['lastLogin'] = users_final['lastLogin'].apply(lambda x: date_conv(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>_id</th>\n",
       "      <th>active</th>\n",
       "      <th>createdDate</th>\n",
       "      <th>lastLogin</th>\n",
       "      <th>role</th>\n",
       "      <th>signUpSource</th>\n",
       "      <th>state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5ff1e194b6a9d73a3a9f1052</td>\n",
       "      <td>True</td>\n",
       "      <td>2021-01-03 15:24:04</td>\n",
       "      <td>2021-01-03 15:25:37</td>\n",
       "      <td>consumer</td>\n",
       "      <td>Email</td>\n",
       "      <td>WI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5ff1e194b6a9d73a3a9f1052</td>\n",
       "      <td>True</td>\n",
       "      <td>2021-01-03 15:24:04</td>\n",
       "      <td>2021-01-03 15:25:37</td>\n",
       "      <td>consumer</td>\n",
       "      <td>Email</td>\n",
       "      <td>WI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5ff1e194b6a9d73a3a9f1052</td>\n",
       "      <td>True</td>\n",
       "      <td>2021-01-03 15:24:04</td>\n",
       "      <td>2021-01-03 15:25:37</td>\n",
       "      <td>consumer</td>\n",
       "      <td>Email</td>\n",
       "      <td>WI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5ff1e1eacfcf6c399c274ae6</td>\n",
       "      <td>True</td>\n",
       "      <td>2021-01-03 15:25:30</td>\n",
       "      <td>2021-01-03 15:25:30</td>\n",
       "      <td>consumer</td>\n",
       "      <td>Email</td>\n",
       "      <td>WI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5ff1e194b6a9d73a3a9f1052</td>\n",
       "      <td>True</td>\n",
       "      <td>2021-01-03 15:24:04</td>\n",
       "      <td>2021-01-03 15:25:37</td>\n",
       "      <td>consumer</td>\n",
       "      <td>Email</td>\n",
       "      <td>WI</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        _id  active          createdDate            lastLogin  \\\n",
       "0  5ff1e194b6a9d73a3a9f1052    True  2021-01-03 15:24:04  2021-01-03 15:25:37   \n",
       "1  5ff1e194b6a9d73a3a9f1052    True  2021-01-03 15:24:04  2021-01-03 15:25:37   \n",
       "2  5ff1e194b6a9d73a3a9f1052    True  2021-01-03 15:24:04  2021-01-03 15:25:37   \n",
       "3  5ff1e1eacfcf6c399c274ae6    True  2021-01-03 15:25:30  2021-01-03 15:25:30   \n",
       "4  5ff1e194b6a9d73a3a9f1052    True  2021-01-03 15:24:04  2021-01-03 15:25:37   \n",
       "\n",
       "       role signUpSource state  \n",
       "0  consumer        Email    WI  \n",
       "1  consumer        Email    WI  \n",
       "2  consumer        Email    WI  \n",
       "3  consumer        Email    WI  \n",
       "4  consumer        Email    WI  "
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users_final.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# First: Review Existing Unstructured Data and Diagram a New Structured Relational Data Model"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![Structured Relation Data Model](SQLdiagram.png \"Structured Relation Data Model\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Second: Write a query that directly answers a predetermined question from a business stakeholder"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Converting our dataframes to SQL database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = create_engine('sqlite:///Fetch_rewards.db', echo=False)\n",
    "sqlite_connection = engine.connect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "brands_final.to_sql('brands', sqlite_connection, if_exists='replace')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "receipts_final.to_sql('receipt', sqlite_connection, if_exists='replace')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "users_final.to_sql('users', sqlite_connection, if_exists='replace')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "sqlite_connection.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## What are the top 5 brands by receipts scanned for most recent month?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![](SQL_Answers/Question1Script.png \"Question 1\")\n",
    "![](SQL_Answers/Question1Ans.png \"Question 1\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# How does the ranking of the top 5 brands by receipts scanned for the recent month compare to the ranking for the previous month?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![](SQL_Answers/Question2Script.png \"Question 2\")\n",
    "![](SQL_Answers/Question2Ans.png \"Question 2\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## When considering average spend from receipts with 'rewardsReceiptStatus’ of ‘Accepted’ or ‘Rejected’, which is greater?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "FINISHED     5920\n",
       "FLAGGED       810\n",
       "SUBMITTED     434\n",
       "REJECTED      167\n",
       "PENDING        50\n",
       "Name: rewardsReceiptStatus, dtype: int64"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "receipts_final['rewardsReceiptStatus'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![](SQL_Answers/Question3Script.png \"Question 3\")\n",
    "![](SQL_Answers/Question3Ans.png \"Question 3\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## When considering total number of items purchased from receipts with 'rewardsReceiptStatus’ of ‘Accepted’ or ‘Rejected’, which is greater?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![](SQL_Answers/Question4Script.png \"Question 4\")\n",
    "![](SQL_Answers/Question4Ans.png \"Question 4\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Which brand has the most spend among users who were created within the past 6 months?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![](SQL_Answers/Question5Script.png \"Question 5\")\n",
    "![](SQL_Answers/Question5Ans.png \"Question 5\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Which brand has the most transactions among users who were created within the past 6 months?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![](SQL_Answers/Question6Script.png \"Question 6\")\n",
    "![](SQL_Answers/Question6Ans.png \"Question 6\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Third: Evaluate Data Quality Issues in the Data Provided"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Receipt table has a lot of duplicated values as we exploded the dataframe to manage a list item and converting it to a row item with replicating other col values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6262"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "receipts_final.duplicated(subset='_id').sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### We need more information for handling this type of data as, for the 1st item on the list, it says ITEM NOT FOUND and we have a valid item for second item on the list.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'barcode': '4011',\n",
       "  'description': 'ITEM NOT FOUND',\n",
       "  'finalPrice': '1',\n",
       "  'itemPrice': '1',\n",
       "  'partnerItemId': '1',\n",
       "  'quantityPurchased': 1},\n",
       " {'barcode': '028400642255',\n",
       "  'description': 'DORITOS TORTILLA CHIP SPICY SWEET CHILI REDUCED FAT BAG 1 OZ',\n",
       "  'finalPrice': '10.00',\n",
       "  'itemPrice': '10.00',\n",
       "  'needsFetchReview': True,\n",
       "  'needsFetchReviewReason': 'USER_FLAGGED',\n",
       "  'partnerItemId': '2',\n",
       "  'pointsNotAwardedReason': 'Action not allowed for user and CPG',\n",
       "  'pointsPayerId': '5332f5fbe4b03c9a25efd0ba',\n",
       "  'preventTargetGapPoints': True,\n",
       "  'quantityPurchased': 1,\n",
       "  'rewardsGroup': 'DORITOS SPICY SWEET CHILI SINGLE SERVE',\n",
       "  'rewardsProductPartnerId': '5332f5fbe4b03c9a25efd0ba',\n",
       "  'userFlaggedBarcode': '028400642255',\n",
       "  'userFlaggedDescription': 'DORITOS TORTILLA CHIP SPICY SWEET CHILI REDUCED FAT BAG 1 OZ',\n",
       "  'userFlaggedNewItem': True,\n",
       "  'userFlaggedPrice': '10.00',\n",
       "  'userFlaggedQuantity': 1}]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "receipts_df['rewardsReceiptItemList'][1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Whereas, for some rows we have multiple duplicate entries for the same record. As shown below"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'barcode': '034100573065',\n",
       "  'description': 'MILLER LITE 24 PACK 12OZ CAN',\n",
       "  'finalPrice': '29',\n",
       "  'itemPrice': '29',\n",
       "  'partnerItemId': '1',\n",
       "  'pointsEarned': '870.0',\n",
       "  'pointsPayerId': '5332f709e4b03c9a25efd0f1',\n",
       "  'quantityPurchased': 1,\n",
       "  'rewardsGroup': 'MILLER LITE 24 PACK',\n",
       "  'rewardsProductPartnerId': '5332f709e4b03c9a25efd0f1',\n",
       "  'targetPrice': '77'},\n",
       " {'barcode': '034100573065',\n",
       "  'description': 'MILLER LITE 24 PACK 12OZ CAN',\n",
       "  'finalPrice': '29',\n",
       "  'itemPrice': '29',\n",
       "  'partnerItemId': '2',\n",
       "  'pointsEarned': '870.0',\n",
       "  'pointsPayerId': '5332f709e4b03c9a25efd0f1',\n",
       "  'quantityPurchased': 1,\n",
       "  'rewardsGroup': 'MILLER LITE 24 PACK',\n",
       "  'rewardsProductPartnerId': '5332f709e4b03c9a25efd0f1',\n",
       "  'targetPrice': '77'},\n",
       " {'barcode': '034100573065',\n",
       "  'description': 'MILLER LITE 24 PACK 12OZ CAN',\n",
       "  'finalPrice': '29',\n",
       "  'itemPrice': '29',\n",
       "  'partnerItemId': '3',\n",
       "  'pointsEarned': '870.0',\n",
       "  'pointsPayerId': '5332f709e4b03c9a25efd0f1',\n",
       "  'quantityPurchased': 1,\n",
       "  'rewardsGroup': 'MILLER LITE 24 PACK',\n",
       "  'rewardsProductPartnerId': '5332f709e4b03c9a25efd0f1',\n",
       "  'targetPrice': '77'},\n",
       " {'barcode': '034100573065',\n",
       "  'description': 'MILLER LITE 24 PACK 12OZ CAN',\n",
       "  'finalPrice': '29',\n",
       "  'itemPrice': '29',\n",
       "  'partnerItemId': '4',\n",
       "  'pointsEarned': '870.0',\n",
       "  'pointsPayerId': '5332f709e4b03c9a25efd0f1',\n",
       "  'quantityPurchased': 1,\n",
       "  'rewardsGroup': 'MILLER LITE 24 PACK',\n",
       "  'rewardsProductPartnerId': '5332f709e4b03c9a25efd0f1',\n",
       "  'targetPrice': '77'},\n",
       " {'barcode': '034100573065',\n",
       "  'description': 'MILLER LITE 24 PACK 12OZ CAN',\n",
       "  'finalPrice': '29',\n",
       "  'itemPrice': '29',\n",
       "  'partnerItemId': '5',\n",
       "  'pointsEarned': '870.0',\n",
       "  'pointsPayerId': '5332f709e4b03c9a25efd0f1',\n",
       "  'quantityPurchased': 1,\n",
       "  'rewardsGroup': 'MILLER LITE 24 PACK',\n",
       "  'rewardsProductPartnerId': '5332f709e4b03c9a25efd0f1',\n",
       "  'targetPrice': '77'},\n",
       " {'barcode': '034100573065',\n",
       "  'description': 'MILLER LITE 24 PACK 12OZ CAN',\n",
       "  'finalPrice': '29',\n",
       "  'itemPrice': '29',\n",
       "  'partnerItemId': '6',\n",
       "  'pointsEarned': '870.0',\n",
       "  'pointsPayerId': '5332f709e4b03c9a25efd0f1',\n",
       "  'quantityPurchased': 1,\n",
       "  'rewardsGroup': 'MILLER LITE 24 PACK',\n",
       "  'rewardsProductPartnerId': '5332f709e4b03c9a25efd0f1',\n",
       "  'targetPrice': '77'},\n",
       " {'barcode': '034100573065',\n",
       "  'description': 'MILLER LITE 24 PACK 12OZ CAN',\n",
       "  'finalPrice': '29',\n",
       "  'itemPrice': '29',\n",
       "  'partnerItemId': '7',\n",
       "  'pointsEarned': '870.0',\n",
       "  'pointsPayerId': '5332f709e4b03c9a25efd0f1',\n",
       "  'quantityPurchased': 1,\n",
       "  'rewardsGroup': 'MILLER LITE 24 PACK',\n",
       "  'rewardsProductPartnerId': '5332f709e4b03c9a25efd0f1',\n",
       "  'targetPrice': '77'},\n",
       " {'barcode': '034100573065',\n",
       "  'description': 'MILLER LITE 24 PACK 12OZ CAN',\n",
       "  'finalPrice': '29',\n",
       "  'itemPrice': '29',\n",
       "  'partnerItemId': '8',\n",
       "  'pointsEarned': '870.0',\n",
       "  'pointsPayerId': '5332f709e4b03c9a25efd0f1',\n",
       "  'quantityPurchased': 1,\n",
       "  'rewardsGroup': 'MILLER LITE 24 PACK',\n",
       "  'rewardsProductPartnerId': '5332f709e4b03c9a25efd0f1',\n",
       "  'targetPrice': '77'},\n",
       " {'barcode': '034100573065',\n",
       "  'description': 'MILLER LITE 24 PACK 12OZ CAN',\n",
       "  'finalPrice': '29',\n",
       "  'itemPrice': '29',\n",
       "  'partnerItemId': '9',\n",
       "  'pointsEarned': '870.0',\n",
       "  'pointsPayerId': '5332f709e4b03c9a25efd0f1',\n",
       "  'quantityPurchased': 1,\n",
       "  'rewardsGroup': 'MILLER LITE 24 PACK',\n",
       "  'rewardsProductPartnerId': '5332f709e4b03c9a25efd0f1',\n",
       "  'targetPrice': '77'},\n",
       " {'barcode': '034100573065',\n",
       "  'description': 'MILLER LITE 24 PACK 12OZ CAN',\n",
       "  'finalPrice': '29',\n",
       "  'itemPrice': '29',\n",
       "  'partnerItemId': '10',\n",
       "  'pointsEarned': '870.0',\n",
       "  'pointsPayerId': '5332f709e4b03c9a25efd0f1',\n",
       "  'quantityPurchased': 1,\n",
       "  'rewardsGroup': 'MILLER LITE 24 PACK',\n",
       "  'rewardsProductPartnerId': '5332f709e4b03c9a25efd0f1',\n",
       "  'targetPrice': '77'}]"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "receipts_df['rewardsReceiptItemList'][12]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "brands_final.duplicated(subset='_id').sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### We cannot allow duplicate value for this column as this will serve as a primary key columns for table Brands"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "269"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "brands_final.duplicated(subset='brandCode').sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### We cannot allow duplicate value for this column as this will serve as a primary key columns for table Users"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "283"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users_final.duplicated(subset='_id').sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Difference between unique brands on receipt and brand dataset has a huge difference, as many brand name are not on the receipt table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(228, 898)"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(set(receipts_final['rewardsReceiptItemList_brandCode'])),len(set(brands_final['brandCode']))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Out of 228 brand with receipt, 186 brands are missing on brand table brandCode."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "186"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(set(receipts_final[~(receipts_final['rewardsReceiptItemList_brandCode'].isin(brands_final['brandCode']))]['rewardsReceiptItemList_brandCode']))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### i.e. Only 42 brands are common in both the table (receipt and brand) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "42"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(set(brands_final['brandCode']))-len(set(brands_final[~(brands_final['brandCode'].isin(receipts_final['rewardsReceiptItemList_brandCode']))]['brandCode']))"
   ]
  }
 ],
 "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}