Data_validity_consistency_completeness.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib\n",
    "%matplotlib inline \n",
    "import matplotlib.pyplot as plt\n",
    "import config\n",
    "from os.path import join\n",
    "dataDir = config.DATADIR\n",
    "from utils import loadData"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_A_R,df_A_S,df_A_V,df_H_R,df_H_S,df_date,df_S_id=loadData()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Missing values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There is not missing data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data  consistency and validity"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this section we check if the data values are consistent and valid."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Validity of foreign keys"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this section we check if each `store_id` in the `reserve` table exists in the `store` table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###  hpg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "there are 13325 unique store ids in hpg-reserve table.\n",
      "there are 4690 unique store ids in hpg-store table.\n"
     ]
    }
   ],
   "source": [
    "df=df_H_R.hpg_store_id.describe()\n",
    "print('there are {} unique store ids in hpg-reserve table.'.format(df['unique']))\n",
    "print('there are {} unique store ids in hpg-store table.'.format(df_H_S.hpg_store_id.unique().size))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So, there are many stores ids in the resesrve table which do not exist in the store table. The transactions related to these stores should be deleted from the reserve table because we cannot join them with the store table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### air "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "there are 314 unique store ids in air-reserve table.\n",
      "there are 829 unique store ids in air-store table.\n"
     ]
    }
   ],
   "source": [
    "df=df_A_R.air_store_id.describe()\n",
    "print('there are {} unique store ids in air-reserve table.'.format(df['unique']))\n",
    "print('there are {} unique store ids in air-store table.'.format(df_A_S.air_store_id.unique().size))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>air_store_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [air_store_id]\n",
       "Index: []"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df=pd.DataFrame({'air_store_id':df_A_R.air_store_id.unique()})\n",
    "df[~df.air_store_id.isin(df_A_S.air_store_id)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So for the air dataset, all store ids in the reserve table exist in the store table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reserve date should be less than Visit date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(0, 4)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_A_R[df_A_R['visit_datetime']<df_A_R['reserve_datetime']].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(0, 4)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_H_R[df_H_R['visit_datetime']<df_H_R['reserve_datetime']].shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "## Validity of latitude and longitude values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>min</th>\n",
       "      <th>25%</th>\n",
       "      <th>50%</th>\n",
       "      <th>75%</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>longitude</th>\n",
       "      <td>829.0</td>\n",
       "      <td>137.415311</td>\n",
       "      <td>3.650971</td>\n",
       "      <td>130.195555</td>\n",
       "      <td>135.341564</td>\n",
       "      <td>139.685474</td>\n",
       "      <td>139.751599</td>\n",
       "      <td>144.273398</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>latitude</th>\n",
       "      <td>829.0</td>\n",
       "      <td>35.647042</td>\n",
       "      <td>2.084659</td>\n",
       "      <td>33.211967</td>\n",
       "      <td>34.695124</td>\n",
       "      <td>35.658068</td>\n",
       "      <td>35.694003</td>\n",
       "      <td>44.020632</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>longitude</th>\n",
       "      <td>4690.0</td>\n",
       "      <td>137.675816</td>\n",
       "      <td>3.197233</td>\n",
       "      <td>130.339313</td>\n",
       "      <td>135.498859</td>\n",
       "      <td>139.495733</td>\n",
       "      <td>139.737998</td>\n",
       "      <td>143.714585</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>latitude</th>\n",
       "      <td>4690.0</td>\n",
       "      <td>35.810261</td>\n",
       "      <td>2.138755</td>\n",
       "      <td>33.311645</td>\n",
       "      <td>34.692109</td>\n",
       "      <td>35.659214</td>\n",
       "      <td>35.703381</td>\n",
       "      <td>43.774846</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            count        mean       std         min         25%         50%  \\\n",
       "longitude   829.0  137.415311  3.650971  130.195555  135.341564  139.685474   \n",
       "latitude    829.0   35.647042  2.084659   33.211967   34.695124   35.658068   \n",
       "longitude  4690.0  137.675816  3.197233  130.339313  135.498859  139.495733   \n",
       "latitude   4690.0   35.810261  2.138755   33.311645   34.692109   35.659214   \n",
       "\n",
       "                  75%         max  \n",
       "longitude  139.751599  144.273398  \n",
       "latitude    35.694003   44.020632  \n",
       "longitude  139.737998  143.714585  \n",
       "latitude    35.703381   43.774846  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame([df_A_S.longitude.describe(),df_A_S.latitude.describe(),\n",
    "              df_H_S.longitude.describe(),df_H_S.latitude.describe()])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "Minimum and maximum values for both tables are in reasonable ranges."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Joining the two datasets"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are only 150 stores which have data in both air and hpg datasets. The number of stores in each dataset is:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(829, 4690)\n"
     ]
    }
   ],
   "source": [
    "air_unique_stores = df_A_S.air_store_id.unique().size\n",
    "hpg_unique_stores = df_H_S.hpg_store_id.unique().size\n",
    "print((air_unique_stores,hpg_unique_stores))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "## Consistancy of lat and lon values in hpg and air"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here we check if the lat and lon values for those stores in both dataset are consistent."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Text(0.5,0,u'difference in lon')"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAmEAAADTCAYAAADJaMQcAAAABHNCSVQICAgIfAhkiAAAAAlwSFlz\nAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDIuMS4wLCBo\ndHRwOi8vbWF0cGxvdGxpYi5vcmcvpW3flQAAGclJREFUeJzt3XuUJWV57/HvD1ARRYHDoCiOjR5C\nRJdRHEyOeOEiBkVFI4m61EMUMomXqElcOgaPoitnLaLGS445wRERVLyiKIkxgkQkJgoMF7kIisJE\nByYwahQRlAM+549dI01P9/Tu7qpde09/P2vt1XWvp/bs/cyzq96qN1WFJEmSRmu7vgOQJElajizC\nJEmSemARJkmS1AOLMEmSpB5YhEmSJPXAIkySJKkHFmGSJEk9sAiTJEnqgUWYJElSD3boO4Bh7L77\n7jU1NdV3GJJG6KKLLvphVa3oO46lMn9Jy8+w+WsiirCpqSnWrVvXdxiSRijJf/QdQxvMX9LyM2z+\n8nKkJElSDyzCJEmSetBZEZbkIUm+kuSqJFcmeU0zfbckZye5pvm7a1cxSNJiJTk5yU1Jrphl3uuS\nVJLd+4hN0rahyzNhdwB/UVWPAH4HeGWS/YA1wDlVtQ9wTjMuSePmFODwmROTPAQ4DPj+qAOStG3p\nrAirqo1VdXEz/DPgKuDBwJHAqc1ipwLP6SoGSVqsqjoP+PEss94NvB6o0UYkaVszkrsjk0wBjwXO\nBx5QVRthUKgl2WOOdVYDqwFWrlw5ijDVkqk1X9hi2voTjughEqldSZ4NXF9V30yyteW2qfw18zvt\n91lqR+cN85PcF/gM8NqqunnY9apqbVWtqqpVK1ZM/KOCJE24JDsBxwFvnm9Z85ekYXRahCW5B4MC\n7LSq+mwz+cYkezbz9wRu6jIGSWrJw4G9gW8mWQ/sBVyc5IG9RiVpYnV5d2SADwJXVdW7ps06Ezi6\nGT4a+HxXMUhSW6rq8qrao6qmqmoK2ADsX1X/2XNokiZUl2fCDgReAhyS5NLm9QzgBOCwJNcwuMPo\nhA5jkKRFSfJx4OvAvkk2JDmm75gkbVs6a5hfVV8D5mq5emhX+5WkNlTVC+eZPzWiUCRto3xiviRJ\nUg8swiRJknpgESZJktQDizBJkqQeWIRJkiT1wCJMkiSpBxZhkiRJPbAIkyRJ6oFFmCRJUg8swiRJ\nknpgESZJktQDizBJkqQeWIRJkiT1wCJMkiSpBxZhkjSLJCcnuSnJFdOmvSPJ1UkuS3JGkl36jFHS\nZLMIk6TZnQIcPmPa2cCjqurRwHeAN446KEnbDoswSZpFVZ0H/HjGtLOq6o5m9BvAXiMPTNI2wyJM\nkhbnZcAXZ5uRZHWSdUnWbdq0acRhSZoUFmGStEBJjgPuAE6bbX5Vra2qVVW1asWKFaMNTtLE2KHv\nACRpkiQ5GngmcGhVVd/xSJpcFmGSNKQkhwNvAJ5SVbf2HY+kyeblSEmaRZKPA18H9k2yIckxwPuA\nnYGzk1ya5MReg5Q00TwTJkmzqKoXzjL5gyMPRNI2yzNhkiRJPbAIkyRJ6oFFmCRJUg9sE6aRmFrz\nhS2mrT/hiB4ikSRpPHgmTJIkqQcWYZIkST2wCJMkSepBZ0VYkpOT3JTkimnTjk9yffOQw0uTPKOr\n/UuSJI2zLs+EnQIcPsv0d1fVY5rXP3W4f0mSpLHVWRFWVecBP+5q+5IkSZOsjzZhr0pyWXO5cte5\nFkqyOsm6JOs2bdo0yvgkSZI6N+oi7O+BhwOPATYCfzPXglW1tqpWVdWqFStWjCo+SZKkkRhpEVZV\nN1bVnVX1K+ADwONHuX9JkqRxMdIiLMme00afC1wx17KS1Kc57vDeLcnZSa5p/s7ZpEKS5tPlIyo+\nDnwd2DfJhiTHAG9PcnmSy4CDgT/rav+StESnsOUd3muAc6pqH+CcZlySFqWzviOr6oWzTP5gV/uT\npDZV1XlJpmZMPhI4qBk+FTgXeMPIgpK0TbEDb0ka3gOqaiNAVW1MssdsCyVZDawGWLly5QjDW7ip\nNV/oOwRp2bLbIklqmXd3SxqGRZgkDe/GzTcYNX9v6jkeSRPMIkyShncmcHQzfDTw+R5jkTThLMIk\naRZz3OF9AnBYkmuAw5pxSVoUG+ZL0izmuMMb4NCRBiJpmzXUmbAkj+o6EEnqijlM0jga9nLkiUku\nSPKKJLt0GpEktc8cJmnsDFWEVdUTgRcBDwHWJflYksM6jUySWmIOkzSOhm6YX1XXAG9i8HTopwB/\nm+TqJL/XVXCS1BZzmKRxM2ybsEcneTdwFXAI8KyqekQz/O4O45OkJTOHSRpHw94d+T7gA8BfVtVt\nmydW1Q1J3tRJZJLUHnMYdlEkjZthi7BnALdV1Z0ASbYDdqyqW6vqI51FJ0ntMIdJGjvDtgn7MnDv\naeM7NdMkaRKYwySNnWGLsB2r6pbNI83wTt2EJEmtM4dJGjvDFmE/T7L/5pEkjwNu28rykjROzGGS\nxs6wbcJeC3w6yQ3N+J7A87sJSZJaZw6TNHaGKsKq6sIkvwnsCwS4uqr+X6eRSVJLzGGSxtFCOvA+\nAJhq1nlsEqrqw51EJUntM4dJGitDFWFJPgI8HLgUuLOZXIAJTNLYazuHJfkz4NhmG5cDL62qX7QQ\nqqRlZNgzYauA/aqqugxGkjrSWg5L8mDg1c32bkvyKeAFwClL3bak5WXYuyOvAB7YZSCS1KG2c9gO\nwL2T7MDgURc3zLO8JG1h2DNhuwPfSnIB8MvNE6vq2Z1EJUntai2HVdX1Sd4JfJ/BYy7Oqqqzpi+T\nZDWwGmDlypVLiVvSNmzYIuz4LoOQpI4d39aGkuwKHAnsDfyEwaMvXlxVH928TFWtBdYCrFq1ymYc\nkmY17CMqvprkocA+VfXlJDsB23cbmiS1o+Uc9lTguqraBJDks8ATgI9udS1JmmGoNmFJ/gg4HXh/\nM+nBwOe6CkqS2tRyDvs+8DtJdkoS4FDgqqVHKWm5GbZh/iuBA4GbAarqGmCProKSpJa1lsOq6nwG\nBd3FDB5PsR3NpUdJWohh24T9sqpuH/zog+aOINs5SJoUreawqnoL8JaWYpO0TA17JuyrSf6SwS3Z\nhwGfBv6hu7AkqVXmMEljZ9gibA2wicGp9z8G/gl4U1dBSVLLzGGSxs6wd0f+CvhA8xpKkpOBZwI3\nVdWjmmm7AZ9k0H/beuAPquq/FhayJC3MYnKYJHVt2Lsjr0ty7czXPKudAhw+Y9oa4Jyq2gc4pxmX\npE4tModJUqcW0nfkZjsCvw/strUVquq8JFMzJh8JHNQMnwqcC7xhyBgkabEWnMMkqWtDnQmrqh9N\ne11fVe8BDlnE/h5QVRubbW5kK7eIJ1mdZF2SdZs2bVrEriRpoMUcJkmtGepMWJL9p41ux+BX5c6d\nRNSw2w9Jbekjh0nSfIa9HPk304bvoGlUv4j93Zhkz6ramGRP4KZFbEOSFqqtHCZJrRn27siDW9rf\nmcDRwAnN38+3tF1JmlOLOUySWjPs5cg/39r8qnrXLOt8nEEj/N2TbGDwdOkTgE8lOYZB/2u/v9CA\nJWmhFpPDJKlrC7k78gAGZ7IAngWcB/xgrhWq6oVzzDp06OgkqR0LzmGS1LVhi7Ddgf2r6mcASY4H\nPl1Vx3YVmCS1yBwmaewM223RSuD2aeO3M3jqvSRNAnOYpLEz7JmwjwAXJDkDKOC5wIc7i0qS2tVq\nDkuyC3AS8Khmey+rqq+3Eaik5WPYuyP/d5IvAk9qJr20qi7pLixJak8HOey9wD9X1VFJ7gnstOQg\nJS07w16OhEGSubmq3gtsSLJ3RzFJUhdayWFJ7gc8GfggQFXdXlU/aS9MScvFsB14v4VBH49vbCbd\nA/hoV0FJUptazmEPAzYBH0pySZKTktxnxv7sdk3SvIY9E/Zc4NnAzwGq6gbs8kPS5Ggzh+0A7A/8\nfVU9ttnmmukLVNXaqlpVVatWrFix+KglbdOGLcJur6pi0ACVmb/6JGnMtZnDNgAbqur8Zvx0BkWZ\nJC3IsEXYp5K8H9glyR8BXwY+0F1YktSq1nJYVf0n8IMk+zaTDgW+1U6YkpaTYe+OfGeSw4CbgX2B\nN1fV2Z1GJkkt6SCH/SlwWnNn5LXAS1sIU9IyM28RlmR74EtV9VTAwkvSROkih1XVpQy6QpKkRZv3\ncmRV3QncmuT+I4hHklplDpM0roZ9Yv4vgMuTnE1zdxFAVb26k6gkqV3mMEljZ9gi7AvNS5ImkTlM\n0tjZahGWZGVVfb+qTh1VQJLUFnOYpHE2X5uwz20eSPKZjmORpLaZwySNrfkuR2ba8MO6DESSOmAO\n68DUmi2v7K4/4YgeIpEm23xnwmqOYUmaBOYwSWNrvjNhv5XkZga/Ju/dDNOMV1Xdr9PoJGlpzGGS\nxtZWi7Cq2n5UgUhS28xhksbZsH1HSpIkqUUWYZIkST2wCJMkSeqBRZgkSVIPLMIkaRGSbJ/kkiT/\n2HcskibTsH1HSnOa7cGN0jLwGuAqwMdcSFoUz4RJ0gIl2Qs4Ajip71gkTS7PhEnSwr0HeD2w82wz\nk6wGVgOsXLlyhGHdxTPU0vjzTJgkLUCSZwI3VdVFcy1TVWuralVVrVqxYsUIo5M0SSzCJGlhDgSe\nnWQ98AngkCQf7TckSZOolyIsyfoklye5NMm6PmKQpMWoqjdW1V5VNQW8APiXqnpxz2FJmkB9tgk7\nuKp+2OP+JUmSemPDfElapKo6Fzi35zAkTai+irACzkpSwPurau3MBcbh7qJt2Wx3Tq0/4YgeIpEk\naXnqq2H+gVW1P/B04JVJnjxzAe8ukiRJ27JeirCquqH5exNwBvD4PuKQJEnqy8iLsCT3SbLz5mHg\nacAVo45DkiSpT320CXsAcEaSzfv/WFX9cw9xSJIk9WbkRVhVXQv81qj3K0mSNE58RIUkbQP67ivS\nO66lhbPbIkmSpB5YhEmSJPXAIkySJKkHFmGSJEk9sAiTJEnqgUWYJElSDyzCJEmSemARJkkLkOQh\nSb6S5KokVyZ5Td8xSZpMPqxVkhbmDuAvquriph/ci5KcXVXf6jswSZPFM2GStABVtbGqLm6GfwZc\nBTy436gkTSLPhEnSIiWZAh4LnD9j+mpgNcDKlStb32/fXRRJaodnwiRpEZLcF/gM8Nqqunn6vKpa\nW1WrqmrVihUr+glQ0tizCJOkBUpyDwYF2GlV9dm+45E0mSzCJGkBkgT4IHBVVb2r73gkTS6LMEla\nmAOBlwCHJLm0eT2j76AkTR4b5kvSAlTV14D0HYekyWcRNuFmu0tq/QlHjP22FxvDqPcvSVJXvBwp\nSZLUA4swSZKkHliESZIk9cAiTJIkqQc2zJekMbLcb0YZhxuCpFF9Dz0TJkmS1AOLMEmSpB5sc5cj\nx/FU9rAx9X0ZYrY4F7NMm/vrcl99fy661Pfx9r1/SZoEngmTJEnqgUWYJElSDyzCJEmSemARJkmS\n1INeirAkhyf5dpLvJlnTRwyStFjmMEltGHkRlmR74O+ApwP7AS9Mst+o45CkxTCHSWpLH2fCHg98\nt6qurarbgU8AR/YQhyQthjlMUitSVaPdYXIUcHhVHduMvwT47ap61YzlVgOrm9F9gW+PNNB27A78\nsO8gOrYcjhGWx3GO2zE+tKpW9B3ETMPksK3kr3F7j+djvN2btJiNdzhD5a8+HtaaWaZtUQlW1Vpg\nbffhdCfJuqpa1XccXVoOxwjL4ziXwzG2ZN4cNlf+mrT32Hi7N2kxG2+7+rgcuQF4yLTxvYAbeohD\nkhbDHCapFX0UYRcC+yTZO8k9gRcAZ/YQhyQthjlMUitGfjmyqu5I8irgS8D2wMlVdeWo4xiRib6c\nOqTlcIywPI5zORzjki0xh03ae2y83Zu0mI23RSNvmC9JkiSfmC9JktQLizBJkqQeWIQtUZLdkpyd\n5Jrm765zLHd0s8w1SY5upu2U5AtJrk5yZZITRhv91s3XNUuSeyX5ZDP//CRT0+a9sZn+7SS/O8q4\nF2Kxx5jksCQXJbm8+XvIqGMf1lL+HZv5K5PckuR1o4p5ki0lJzTTz23+vS5tXnt0FOdEfb+X8F2d\nSnLbtPfzxDGJ98lJLk5yR/PsuenzZv1sjHnMd057j0dyo8oQ8f55km8luSzJOUkeOm1eL+/xFqrK\n1xJewNuBNc3wGuCvZ1lmN+Da5u+uzfCuwE7Awc0y9wT+FXh638fUxLM98D3gYU1s3wT2m7HMK4AT\nm+EXAJ9shvdrlr8XsHezne37PqaWj/GxwIOa4UcB1/d9PG0f47T5nwE+Dbyu7+OZhNdSckIz71xg\n1bh+Lvr4fi8x3ingihF/BoaJdwp4NPBh4KhhPhvjGnMz75YxfI8PBnZqhl8+7TPRy3s828szYUt3\nJHBqM3wq8JxZlvld4Oyq+nFV/RdwNoMnbt9aVV8BqEH3JxczeObQOBima5bpx346cGiSNNM/UVW/\nrKrrgO822xs3iz7GqrqkqjY/G+pKYMck9xpJ1AuzlH9HkjyHQYLaVu9g7sKic8KI4oPJ+34v6XPc\ng3njrar1VXUZ8KsZ6/b12VhKzH0YJt6vVNWtzeg3uOv/176/f79mEbZ0D6iqjQDN39kuHTwY+MG0\n8Q3NtF9LsgvwLOCcjuJcqHljnr5MVd0B/BT4b0OuOw6WcozTPQ+4pKp+2VGcS7HoY0xyH+ANwFtH\nEOe2pI2c8KHmss7/6qiQmLTv91K/q3snuSTJV5M8qeNY7xZLYyHvUV/5c6n73THJuiTfaH68dW2h\n8R4DfHGR63amj26LJk6SLwMPnGXWccNuYpZpv342SJIdgI8Df1tV1y48wk4M073UXMsM1TXVGFjK\nMQ5mJo8E/hp4WotxtWkpx/hW4N1VdUt/JxTGU8c54UVVdX2SnRlcCn4Jg8s/bZq07/dS4t0IrKyq\nHyV5HPC5JI+sqpvbDnKIWLpedymWut+VVXVDkocB/5Lk8qr6XkuxzWboeJO8GFgFPGWh63bNImwI\nVfXUueYluTHJnlW1McmewE2zLLYBOGja+F4M2n1stha4pqre00K4bRmma5bNy2xoCsn7Az8ect1x\nsJRjJMlewBnA/+w42SzFUo7xt4Gjkrwd2AX4VZJfVNX7ug97vHWZE6rq+ubvz5J8jMFll7aLsEn7\nfi863ho0AvolQFVdlOR7wG8A63qOd2vrHjRj3XNbiWr+/S7633Vz84yqujbJuQzazXaZF4eKN8lT\nGfw4esq0qxV9vcdb6qMh2rb0At7B3Rvhvn2WZXYDrmPQAHDXZni3Zt5fMfi1u13fxzIj5h0YtAXa\nm7saPT5yxjKv5O4NYT/VDD+SuzfcvZbxbJi/lGPcpVn+eX0fR1fHOGOZ47Fh/rDv+aJzQvPvtXuz\nzD0YtG36k3H6XPTx/V5ivCs2x8egEff1m/Nvn/FOW/YUtmyYP+v/F2Mc867AvZrh3YFrmNFIvqfP\nxOZCcJ8Z03t5j2c9jj52ui29GLQ5OKf50J3DXcXVKuCkacu9jEED1u8CL22m7cXgFOhVwKXN69i+\nj2lazM8AvtN8iI9rpr0NeHYzvCODu+a+C1wAPGzausc1632bMbnjs81jBN4E/Hzav9ulwB59H0/b\n/47TtnE8FmHDvt9LyQn3AS4CLmNwM8R76ajAmbTv9xK+q89r3stvMrj56VljEu8BDM7I/Bz4EXDl\n1j4b4xwz8ATg8uY9vhw4Zkzi/TJwI3fl6DP7fo9nvuy2SJIkqQfeHSlJktQDizBJkqQeWIRJkiT1\nwCJMkiSpBxZhkiRJPbAI090kOT7J65rhtzUPuiPJk5Jc2XSlcu8k72jG39FvxHNL8u8LXP6UJEfN\ns8wfJnnQ0iKT1DZz19Zzl8aTT8zXnKrqzdNGXwS8s6o+BJDkj4EVNWR/iUl2qEF/biNTVU/oYLN/\nCFzBePYAIAlzlyaHZ8JEkuOSfLvpD2/fadNPSXJUkmOBPwDenOS0JGcyeKjk+Umen2RFks8kubB5\nHdisf3yStUnOAj6cZPvmV+iFSS5rkiFJDkpybpLTk1zd7CPNvAOS/HuSbya5IMnOc21nluO6Zb7t\nb+U9eXOz/SuaY0jzS3MVcNrmX9VLfe8lLZ65a9Z1D82gs/LLk5yc5F7N9PVJ3prk4mbeby71/dfS\neSZsmcugQ9sXMOjeYQcGT5S+aPoyVXVSkicC/1hVpzfr3VJVj2mGP8ago+evJVkJfAl4RLP644An\nVtVtSVYDP62qA5rE8G9NkqPZ/yMZnGH6N+DAJBcAnwSeX1UXJrkfcBtwzGzbqarrtnKoW2wf+NpW\nln9fVb2tOb6PAM+sqtOTvIrBk+O77HdO0jzMXbO+Jzsy6FLo0Kr6TpIPAy8HNvdL/MOq2j/JK4DX\nAcduZb8aAYswPQk4o6puBWh+KS7UU4H9pv1Au1+SnZvhM6vqtmb4acCjc1fbhfsD+wC3AxdU1YYm\nhkuBKeCnwMaquhCgqm5u5s+1na0lstm2v7Ui7OAkrwd2YtDP2JXAP2xleUmjZe7a0r7AdVX1nWb8\nVAZ9am4uwj7b/L0I+L2t7FMjYhEmGPRfuRTbAf9jWsICoElsP58+CfjTqvrSjOUOAqa3z7iTwWcz\nc8Q263bmMdv2Z9X8mvy/wKqq+kGS4xn0SydpvJi7ttz+MNuabzsaEduE6TzguRncNbQz8KxFbOMs\n4FWbR5I8Zo7lvgS8PMk9muV+I8l9trLdq4EHJTmgWX7nJDssYjsLtbng+mGS+wLT7zr6GbDzlqtI\nGjFz1+z7nUry35vxlwBfbXH7apmV8DJXVRcn+SSDHub/A/jXRWzm1cDfJbmMwWfqPOBPZlnuJAan\n0i9uGpduAp6zldhuT/J84P9k0Aj+NgaXDxa0nYWqqp8k+QBwObAeuHDa7FOAE5Pcxiy/oCWNhrlr\n1v3+IslLgU83Rd+FwIltbV/tS9VSz+ZKkiRpobwcKUmS1AOLMEmSpB5YhEmSJPXAIkySJKkHFmGS\nJEk9sAiTJEnqgUWYJElSD/4/c7FkyuU6n80AAAAASUVORK5CYII=\n",
      "text/plain": [
       "<matplotlib.figure.Figure at 0x7f98eb94ec10>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df=df_S_id.merge(df_A_S,on=['air_store_id']).merge(df_H_S,on='hpg_store_id')\n",
    "fig=plt.figure(figsize=(10,3))\n",
    "fig.add_subplot(121)\n",
    "_=(df.latitude_x-df.latitude_y).plot.hist(bins=50)\n",
    "plt.xlabel('difference in lat')\n",
    "fig.add_subplot(122)\n",
    "_=(df.longitude_x-df.longitude_y).plot.hist(bins=50)\n",
    "plt.xlabel('difference in lon')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "There is one store whose difference in longitude is larger than 0.2. We identify it:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>air_store_id</th>\n",
       "      <th>hpg_store_id</th>\n",
       "      <th>air_genre_name</th>\n",
       "      <th>air_area_name</th>\n",
       "      <th>latitude_x</th>\n",
       "      <th>longitude_x</th>\n",
       "      <th>hpg_genre_name</th>\n",
       "      <th>hpg_area_name</th>\n",
       "      <th>latitude_y</th>\n",
       "      <th>longitude_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>air_640cf4835f0d9ba3</td>\n",
       "      <td>hpg_e63863c8987bd064</td>\n",
       "      <td>Izakaya</td>\n",
       "      <td>Tōkyō-to Ōta-ku Kamata</td>\n",
       "      <td>35.561257</td>\n",
       "      <td>139.716051</td>\n",
       "      <td>Japanese style</td>\n",
       "      <td>Kanagawa-ken Yokohama-shi Nagatsutachō</td>\n",
       "      <td>35.512762</td>\n",
       "      <td>139.495733</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            air_store_id          hpg_store_id air_genre_name  \\\n",
       "39  air_640cf4835f0d9ba3  hpg_e63863c8987bd064        Izakaya   \n",
       "\n",
       "             air_area_name  latitude_x  longitude_x  hpg_genre_name  \\\n",
       "39  Tōkyō-to Ōta-ku Kamata   35.561257   139.716051  Japanese style   \n",
       "\n",
       "                             hpg_area_name  latitude_y  longitude_y  \n",
       "39  Kanagawa-ken Yokohama-shi Nagatsutachō   35.512762   139.495733  "
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[(df.longitude_x-df.longitude_y)>.2]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "However this is not the case for `area_name` and `genre_name`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(58, 10)"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df.air_area_name!=df.hpg_area_name].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(62, 10)"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df.air_genre_name!=df.hpg_genre_name].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "829"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_A_S.air_store_id.unique().size"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "So it does not seem that joining the two datasets will be much helpful."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Distance between stores in air and hpg datasets "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this section we compute and visualize the distance between each store in the air dataset and the stores in the hpg dataset. The goal here is to see if for each store in the air dataset we can find a store in the hpg using the distance. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "n_hpg=df_H_S.shape[0];n_air=df_A_S.shape[0];\n",
    "lat_hpg=df_H_S.latitude.values.reshape((n_hpg,1));lon_hpg=df_H_S.longitude.values.reshape((n_hpg,1))\n",
    "lat_air=df_A_S.latitude.values.reshape((n_air,1));lon_air=df_A_S.longitude.values.reshape((n_air,1))\n",
    "\n",
    "lat_dist = np.tile(lat_air**2,(1,n_hpg))+\\\n",
    "           np.tile(lat_hpg.transpose()**2,(n_air,1))-\\\n",
    "           2*np.dot(lat_air,lat_hpg.transpose())\n",
    "lon_dist = np.tile(lon_air**2,(1,n_hpg))+\\\n",
    "           np.tile(lon_hpg.transpose()**2,(n_air,1))-\\\n",
    "           2*np.dot(lon_air,lon_hpg.transpose())\n",
    "dist = np.sqrt(lat_dist+lon_dist)        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAXoAAAD8CAYAAAB5Pm/hAAAABHNCSVQICAgIfAhkiAAAAAlwSFlz\nAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDIuMS4wLCBo\ndHRwOi8vbWF0cGxvdGxpYi5vcmcvpW3flQAAEV1JREFUeJzt3WuMXddZxvH/07hpKb04l0kU2aYu\nqlsaITUNo2BUqZSaS5OiOB8alAqIiQxGECqgCDDwgeuHAIJApCpgmlKnopc0UGK14RK5qQoIh06a\nEHKhyjSk8cghHtrEXKICaV8+nOV2cMY+ezxnZjxL/590tNdee51z3uWxH2+vs892qgpJUr9esNYF\nSJJWlkEvSZ0z6CWpcwa9JHXOoJekzhn0ktQ5g16SOmfQS1LnDHpJ6tyGtS4A4Pzzz6+tW7eudRmS\ntK7ce++9/1ZVU+PGnRFBv3XrVmZmZta6DElaV5J8fsg4l24kqXMGvSR1zqCXpM4Z9JLUOYNekjpn\n0EtS5wx6SeqcQS9JnTPoJalzZ8Q3Y5dj696PL9r/+A1vW+VKJOnM5Bm9JHXOoJekzhn0ktQ5g16S\nOmfQS1LnDHpJ6pxBL0mdM+glqXNjgz7Ja5Pcv+Dx70l+Ksm5Se5K8mjbntPGJ8lNSWaTPJDk0pWf\nhiTpZMYGfVV9tqouqapLgG8BngU+CuwFDlbVNuBg2we4HNjWHnuAm1eicEnSMEtdutkBfK6qPg/s\nBPa3/v3AVa29E7i1Rg4BG5NcNJFqJUlLttSgvwb4YGtfWFVPArTtBa1/E3B4wXPmWp8kaQ0MDvok\nZwNXAh8ZN3SRvlrk9fYkmUkyMz8/P7QMSdISLeWM/nLgM1X1VNt/6viSTNsebf1zwJYFz9sMHDnx\nxapqX1VNV9X01NTU0iuXJA2ylKB/B19btgE4AOxq7V3AHQv6r21X32wHjh1f4pEkrb5B96NP8hLg\nu4AfXdB9A3Bbkt3AE8DVrf9O4ApgltEVOtdNrFpJ0pINCvqqehY474S+LzC6CufEsQVcP5HqJEnL\n5jdjJalzBr0kdc6gl6TOGfSS1DmDXpI6Z9BLUucMeknqnEEvSZ0z6CWpcwa9JHXOoJekzhn0ktQ5\ng16SOmfQS1LnDHpJ6pxBL0mdM+glqXMGvSR1zqCXpM4NCvokG5PcnuSfkzyS5NuSnJvkriSPtu05\nbWyS3JRkNskDSS5d2SlIkk5l6Bn97wN/WVXfBLweeATYCxysqm3AwbYPcDmwrT32ADdPtGJJ0pKM\nDfokLwfeBNwCUFX/U1XPADuB/W3YfuCq1t4J3Fojh4CNSS6aeOWSpEGGnNF/IzAP/HGS+5K8J8nX\nAxdW1ZMAbXtBG78JOLzg+XOt7/9JsifJTJKZ+fn5ZU1CknRyQ4J+A3ApcHNVvQH4L762TLOYLNJX\nz+uo2ldV01U1PTU1NahYSdLSDQn6OWCuqu5p+7czCv6nji/JtO3RBeO3LHj+ZuDIZMqVJC3V2KCv\nqn8FDid5bevaATwMHAB2tb5dwB2tfQC4tl19sx04dnyJR5K0+jYMHPdO4E+SnA08BlzH6C+J25Ls\nBp4Arm5j7wSuAGaBZ9tYSdIaGRT0VXU/ML3IoR2LjC3g+mXWJUmaEL8ZK0mdM+glqXMGvSR1zqCX\npM4Z9JLUOYNekjpn0EtS5wx6SeqcQS9JnTPoJalzBr0kdc6gl6TOGfSS1DmDXpI6Z9BLUucMeknq\nnEEvSZ0z6CWpcwa9JHVuUNAneTzJPyW5P8lM6zs3yV1JHm3bc1p/ktyUZDbJA0kuXckJSJJObSln\n9N9RVZdU1fH/JHwvcLCqtgEH2z7A5cC29tgD3DypYiVJS7ecpZudwP7W3g9ctaD/1ho5BGxMctEy\n3keStAxDg76Av05yb5I9re/CqnoSoG0vaP2bgMMLnjvX+v6fJHuSzCSZmZ+fP73qJUljbRg47o1V\ndSTJBcBdSf75FGOzSF89r6NqH7APYHp6+nnHJUmTMeiMvqqOtO1R4KPAZcBTx5dk2vZoGz4HbFnw\n9M3AkUkVLElamrFBn+Trk7zseBv4buBB4ACwqw3bBdzR2geAa9vVN9uBY8eXeCRJq2/I0s2FwEeT\nHB//gar6yySfBm5Lsht4Ari6jb8TuAKYBZ4Frpt41ZKkwcYGfVU9Brx+kf4vADsW6S/g+olUJ0la\nNr8ZK0mdM+glqXMGvSR1zqCXpM4Z9JLUOYNekjpn0EtS5wx6SeqcQS9JnTPoJalzBr0kdc6gl6TO\nGfSS1DmDXpI6Z9BLUucMeknqnEEvSZ0z6CWpc4ODPslZSe5L8rG2/6ok9yR5NMmHk5zd+l/U9mfb\n8a0rU7okaYilnNH/JPDIgv3fBG6sqm3A08Du1r8beLqqXg3c2MZJktbIoKBPshl4G/Ceth/gLcDt\nbch+4KrW3tn2acd3tPGSpDUw9Iz+94CfA77S9s8Dnqmq59r+HLCptTcBhwHa8WNtvCRpDYwN+iTf\nCxytqnsXdi8ytAYcW/i6e5LMJJmZn58fVKwkaemGnNG/EbgyyePAhxgt2fwesDHJhjZmM3CkteeA\nLQDt+CuAL574olW1r6qmq2p6ampqWZOQJJ3c2KCvql+oqs1VtRW4BvhEVX0/cDfw9jZsF3BHax9o\n+7Tjn6iq553RS5JWx3Kuo/954F1JZhmtwd/S+m8Bzmv97wL2Lq9ESdJybBg/5Guq6pPAJ1v7MeCy\nRcZ8Cbh6ArVJkibAb8ZKUucMeknqnEEvSZ0z6CWpcwa9JHXOoJekzhn0ktQ5g16SOmfQS1LnDHpJ\n6pxBL0mdM+glqXMGvSR1zqCXpM4Z9JLUOYNekjpn0EtS5wx6SeqcQS9JnRsb9ElenOQfkvxjkoeS\n/Grrf1WSe5I8muTDSc5u/S9q+7Pt+NaVnYIk6VSGnNH/N/CWqno9cAnw1iTbgd8EbqyqbcDTwO42\nfjfwdFW9GrixjZMkrZGxQV8j/9l2X9geBbwFuL317weuau2dbZ92fEeSTKxiSdKSDFqjT3JWkvuB\no8BdwOeAZ6rquTZkDtjU2puAwwDt+DHgvEkWLUkablDQV9WXq+oSYDNwGfC6xYa17WJn73ViR5I9\nSWaSzMzPzw+tV5K0REu66qaqngE+CWwHNibZ0A5tBo609hywBaAdfwXwxUVea19VTVfV9NTU1OlV\nL0kaa8hVN1NJNrb21wHfCTwC3A28vQ3bBdzR2gfaPu34J6rqeWf0kqTVsWH8EC4C9ic5i9FfDLdV\n1ceSPAx8KMlvAPcBt7TxtwDvTzLL6Ez+mhWoW5I00Nigr6oHgDcs0v8Yo/X6E/u/BFw9keokScvm\nN2MlqXMGvSR1zqCXpM4Z9JLUOYNekjpn0EtS5wx6SeqcQS9JnTPoJalzBr0kdc6gl6TOGfSS1DmD\nXpI6Z9BLUucMeknqnEEvSZ0z6CWpcwa9JHXOoJekzo0N+iRbktyd5JEkDyX5ydZ/bpK7kjzatue0\n/iS5KclskgeSXLrSk5AkndyQM/rngJ+pqtcB24Hrk1wM7AUOVtU24GDbB7gc2NYee4CbJ161JGmw\nsUFfVU9W1Wda+z+AR4BNwE5gfxu2H7iqtXcCt9bIIWBjkosmXrkkaZAlrdEn2Qq8AbgHuLCqnoTR\nXwbABW3YJuDwgqfNtb4TX2tPkpkkM/Pz80uvXJI0yOCgT/JS4E+Bn6qqfz/V0EX66nkdVfuqarqq\npqempoaWIUlaokFBn+SFjEL+T6rqz1r3U8eXZNr2aOufA7YsePpm4MhkypUkLdWQq24C3AI8UlW/\nu+DQAWBXa+8C7ljQf227+mY7cOz4Eo8kafVtGDDmjcAPAv+U5P7W94vADcBtSXYDTwBXt2N3AlcA\ns8CzwHUTrViStCRjg76q/pbF190BdiwyvoDrl1mXJGlC/GasJHXOoJekzhn0ktQ5g16SOmfQS1Ln\nDHpJ6pxBL0mdM+glqXMGvSR1zqCXpM4Z9JLUOYNekjpn0EtS5wx6SeqcQS9JnTPoJalzBr0kdc6g\nl6TODfnPwd+b5GiSBxf0nZvkriSPtu05rT9Jbkoym+SBJJeuZPGSpPGGnNG/D3jrCX17gYNVtQ04\n2PYBLge2tcce4ObJlClJOl1jg76qPgV88YTuncD+1t4PXLWg/9YaOQRsTHLRpIqVJC3d6a7RX1hV\nTwK07QWtfxNweMG4udYnSVojk/4wNov01aIDkz1JZpLMzM/PT7gMSdJxpxv0Tx1fkmnbo61/Dtiy\nYNxm4MhiL1BV+6pquqqmp6amTrMMSdI4pxv0B4Bdrb0LuGNB/7Xt6pvtwLHjSzySpLWxYdyAJB8E\n3gycn2QO+GXgBuC2JLuBJ4Cr2/A7gSuAWeBZ4LoVqFmStARjg76q3nGSQzsWGVvA9cstSpI0OX4z\nVpI6Z9BLUucMeknqnEEvSZ0z6CWpcwa9JHXOoJekzhn0ktS5sV+YWq+27v34ov2P3/C2Va5EktaW\nZ/SS1DmDXpI6Z9BLUucMeknqnEEvSZ0z6CWpcwa9JHXOoJekzhn0ktQ5g16SOrcit0BI8lbg94Gz\ngPdU1Q0r8T6n42S3RgBvjyCpTxM/o09yFvBu4HLgYuAdSS6e9PtIkoZZiTP6y4DZqnoMIMmHgJ3A\nwyvwXmckb6gm6UyyEkG/CTi8YH8O+NYVeJ81d6ploPXAZazx/DXSpKzlCWCqarIvmFwNfE9V/XDb\n/0Hgsqp65wnj9gB72u5rgc+e5lueD/zbaT53PXGefXGefVmreb6yqqbGDVqJM/o5YMuC/c3AkRMH\nVdU+YN9y3yzJTFVNL/d1znTOsy/Osy9n+jxX4vLKTwPbkrwqydnANcCBFXgfSdIAEz+jr6rnkvwE\n8FeMLq98b1U9NOn3kSQNsyLX0VfVncCdK/Hai1j28s864Tz74jz7ckbPc+IfxkqSzizeAkGSOrcu\ngj7JW5N8Nslskr2LHH9Rkg+34/ck2br6VS7fgHm+K8nDSR5IcjDJK9eizuUaN88F496epJKcsVcz\nnMqQeSb5vvYzfSjJB1a7xkkY8Pv2G5LcneS+9nv3irWoc7mSvDfJ0SQPnuR4ktzUfh0eSHLpatd4\nUlV1Rj8YfaD7OeAbgbOBfwQuPmHMjwN/0NrXAB9e67pXaJ7fAbyktX+s13m2cS8DPgUcAqbXuu4V\n+nluA+4Dzmn7F6x13Ss0z33Aj7X2xcDja133ac71TcClwIMnOX4F8BdAgO3APWtd8/HHejij/+ot\nFarqf4Djt1RYaCewv7VvB3YkySrWOAlj51lVd1fVs233EKPvKKw3Q36eAL8O/BbwpdUsboKGzPNH\ngHdX1dMAVXV0lWuchCHzLODlrf0KFvlezXpQVZ8CvniKITuBW2vkELAxyUWrU92prYegX+yWCptO\nNqaqngOOAeetSnWTM2SeC+1mdPaw3oydZ5I3AFuq6mOrWdiEDfl5vgZ4TZK/S3Ko3fV1vRkyz18B\nfiDJHKOr8d5Jn5b6Z3jVrMjllRO22Jn5iZcKDRlzphs8hyQ/AEwD376iFa2MU84zyQuAG4EfWq2C\nVsiQn+cGRss3b2b0r7O/SfLNVfXMCtc2SUPm+Q7gfVX1O0m+DXh/m+dXVr68VXXG5tB6OKMfckuF\nr45JsoHRPw9P9U+sM9GgW0ck+U7gl4Arq+q/V6m2SRo3z5cB3wx8MsnjjNY6D6zDD2SH/r69o6r+\nt6r+hdH9nratUn2TMmSeu4HbAKrq74EXM7o3TG8G/RleC+sh6IfcUuEAsKu13w58otqnI+vI2Hm2\nJY0/ZBTy63E9F8bMs6qOVdX5VbW1qrYy+iziyqqaWZtyT9uQ37d/zugDdpKcz2gp57FVrXL5hszz\nCWAHQJLXMQr6+VWtcnUcAK5tV99sB45V1ZNrXRSsg6WbOsktFZL8GjBTVQeAWxj9c3CW0Zn8NWtX\n8ekZOM/fBl4KfKR91vxEVV25ZkWfhoHzXPcGzvOvgO9O8jDwZeBnq+oLa1f10g2c588Af5Tkpxkt\nZfzQOjwRI8kHGS2znd8+b/hl4IUAVfUHjD5/uAKYBZ4FrlubSp/Pb8ZKUufWw9KNJGkZDHpJ6pxB\nL0mdM+glqXMGvSR1zqCXpM4Z9JLUOYNekjr3f4cLt/VzrmAtAAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<matplotlib.figure.Figure at 0x7feadcc63e50>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "_=plt.hist(dist.min(axis=1),bins=50)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(829, 4690)"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dist.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# Conclusion"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "+ delete those transactions in the `hpg-reserve` table whose store id does not exist in the store table."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.14"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}