Hotel+Chain+Loyal+Customer-pandas.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "Question 18 - A hotel chain's loyal customers\n",
    "\n",
    "You are an analyst for a major US hotel chain which has locations all over the US. Your marketing team is planning a promotion focused around loyal customers, and they are trying to forecast how much revenue the promotion will bring in. However, they need help from you to understand how much revenue comes from \"loyal\" customer to plug into their model. \n",
    "\n",
    "A \"loyal\" customer is defined as (1) having a memebership with your company's point system, (2) having >3 stays at each hotel the customer visited, (3) having stayed at 3 different locations throughout the US.\n",
    "\n",
    "You have a table showing all transactions made in 2017. The schema of the table is below:\n",
    "\n",
    "Table: customer_transactions\n",
    "\n",
    "Column Name\tData Type\tDescription\n",
    "customer_id\tid\tid of the customer\n",
    "hotel_id\tinteger\tunique id for hotel\n",
    "transaction_id\tinteger\tid of the given transaction\n",
    "first_night\tstring\tfirst night of the stay, column format is \"YYYY-mm-dd\"\n",
    "number_of_nights\tinteger\t# of nights the customer stayed in hotel\n",
    "total_spend\tinteger\ttotal spend for transaction, in USD\n",
    "is_memeber\tboolean\tindicates if the customer is a member of our points system\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "df=pd.read_csv('/Users/chidam/Desktop/loyal_customer.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>customer_id</th>\n",
       "      <th>hotel_id</th>\n",
       "      <th>transaction_id</th>\n",
       "      <th>first_night_stay</th>\n",
       "      <th>number_of_nights</th>\n",
       "      <th>total_spend</th>\n",
       "      <th>is_memeber</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>34590</td>\n",
       "      <td>19</td>\n",
       "      <td>34</td>\n",
       "      <td>9/12/17</td>\n",
       "      <td>2</td>\n",
       "      <td>2500</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12345</td>\n",
       "      <td>6</td>\n",
       "      <td>90</td>\n",
       "      <td>8/23/17</td>\n",
       "      <td>3</td>\n",
       "      <td>2300</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>54321</td>\n",
       "      <td>7</td>\n",
       "      <td>12</td>\n",
       "      <td>3/30/17</td>\n",
       "      <td>4</td>\n",
       "      <td>3500</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>45788</td>\n",
       "      <td>13</td>\n",
       "      <td>43</td>\n",
       "      <td>7/31/17</td>\n",
       "      <td>1</td>\n",
       "      <td>4500</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>87654</td>\n",
       "      <td>17</td>\n",
       "      <td>7</td>\n",
       "      <td>7/25/17</td>\n",
       "      <td>6</td>\n",
       "      <td>3400</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>98723</td>\n",
       "      <td>89</td>\n",
       "      <td>89</td>\n",
       "      <td>4/17/17</td>\n",
       "      <td>7</td>\n",
       "      <td>4200</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>34590</td>\n",
       "      <td>19</td>\n",
       "      <td>3</td>\n",
       "      <td>8/19/17</td>\n",
       "      <td>2</td>\n",
       "      <td>2367</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>54378</td>\n",
       "      <td>32</td>\n",
       "      <td>2</td>\n",
       "      <td>2/20/17</td>\n",
       "      <td>1</td>\n",
       "      <td>3456</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>34590</td>\n",
       "      <td>66</td>\n",
       "      <td>56</td>\n",
       "      <td>11/28/17</td>\n",
       "      <td>4</td>\n",
       "      <td>3300</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>87123</td>\n",
       "      <td>77</td>\n",
       "      <td>66</td>\n",
       "      <td>12/8/17</td>\n",
       "      <td>1</td>\n",
       "      <td>3213</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>45789</td>\n",
       "      <td>87</td>\n",
       "      <td>80</td>\n",
       "      <td>6/12/17</td>\n",
       "      <td>6</td>\n",
       "      <td>1278</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>90082</td>\n",
       "      <td>10</td>\n",
       "      <td>32</td>\n",
       "      <td>1/16/17</td>\n",
       "      <td>2</td>\n",
       "      <td>2200</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>34590</td>\n",
       "      <td>15</td>\n",
       "      <td>13</td>\n",
       "      <td>7/14/17</td>\n",
       "      <td>1</td>\n",
       "      <td>2789</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>45788</td>\n",
       "      <td>5</td>\n",
       "      <td>67</td>\n",
       "      <td>4/17/17</td>\n",
       "      <td>1</td>\n",
       "      <td>3298</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>12345</td>\n",
       "      <td>10</td>\n",
       "      <td>33</td>\n",
       "      <td>5/22/17</td>\n",
       "      <td>4</td>\n",
       "      <td>3300</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>11200</td>\n",
       "      <td>76</td>\n",
       "      <td>54</td>\n",
       "      <td>2/13/17</td>\n",
       "      <td>3</td>\n",
       "      <td>1300</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>11200</td>\n",
       "      <td>31</td>\n",
       "      <td>987</td>\n",
       "      <td>11/16/17</td>\n",
       "      <td>5</td>\n",
       "      <td>2567</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>11200</td>\n",
       "      <td>31</td>\n",
       "      <td>323</td>\n",
       "      <td>5/22/17</td>\n",
       "      <td>6</td>\n",
       "      <td>3300</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    customer_id  hotel_id  transaction_id first_night_stay  number_of_nights  \\\n",
       "0         34590        19              34          9/12/17                 2   \n",
       "1         12345         6              90          8/23/17                 3   \n",
       "2         54321         7              12          3/30/17                 4   \n",
       "3         45788        13              43          7/31/17                 1   \n",
       "4         87654        17               7          7/25/17                 6   \n",
       "5         98723        89              89          4/17/17                 7   \n",
       "6         34590        19               3          8/19/17                 2   \n",
       "7         54378        32               2          2/20/17                 1   \n",
       "8         34590        66              56         11/28/17                 4   \n",
       "9         87123        77              66          12/8/17                 1   \n",
       "10        45789        87              80          6/12/17                 6   \n",
       "11        90082        10              32          1/16/17                 2   \n",
       "12        34590        15              13          7/14/17                 1   \n",
       "13        45788         5              67          4/17/17                 1   \n",
       "14        12345        10              33          5/22/17                 4   \n",
       "15        11200        76              54          2/13/17                 3   \n",
       "16        11200        31             987         11/16/17                 5   \n",
       "17        11200        31             323          5/22/17                 6   \n",
       "\n",
       "    total_spend  is_memeber  \n",
       "0          2500           1  \n",
       "1          2300           1  \n",
       "2          3500           0  \n",
       "3          4500           1  \n",
       "4          3400           1  \n",
       "5          4200           0  \n",
       "6          2367           1  \n",
       "7          3456           1  \n",
       "8          3300           1  \n",
       "9          3213           1  \n",
       "10         1278           0  \n",
       "11         2200           1  \n",
       "12         2789           1  \n",
       "13         3298           1  \n",
       "14         3300           1  \n",
       "15         1300           1  \n",
       "16         2567           1  \n",
       "17         3300           1  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df['first_night_stay']=pd.to_datetime(df['first_night_stay'])\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>customer_id</th>\n",
       "      <th>hotel_id</th>\n",
       "      <th>transaction_id</th>\n",
       "      <th>first_night_stay</th>\n",
       "      <th>number_of_nights</th>\n",
       "      <th>total_spend</th>\n",
       "      <th>is_memeber</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>34590</td>\n",
       "      <td>19</td>\n",
       "      <td>34</td>\n",
       "      <td>2017-09-12</td>\n",
       "      <td>2</td>\n",
       "      <td>2500</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12345</td>\n",
       "      <td>6</td>\n",
       "      <td>90</td>\n",
       "      <td>2017-08-23</td>\n",
       "      <td>3</td>\n",
       "      <td>2300</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>54321</td>\n",
       "      <td>7</td>\n",
       "      <td>12</td>\n",
       "      <td>2017-03-30</td>\n",
       "      <td>4</td>\n",
       "      <td>3500</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>45788</td>\n",
       "      <td>13</td>\n",
       "      <td>43</td>\n",
       "      <td>2017-07-31</td>\n",
       "      <td>1</td>\n",
       "      <td>4500</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>87654</td>\n",
       "      <td>17</td>\n",
       "      <td>7</td>\n",
       "      <td>2017-07-25</td>\n",
       "      <td>6</td>\n",
       "      <td>3400</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customer_id  hotel_id  transaction_id first_night_stay  number_of_nights  \\\n",
       "0        34590        19              34       2017-09-12                 2   \n",
       "1        12345         6              90       2017-08-23                 3   \n",
       "2        54321         7              12       2017-03-30                 4   \n",
       "3        45788        13              43       2017-07-31                 1   \n",
       "4        87654        17               7       2017-07-25                 6   \n",
       "\n",
       "   total_spend  is_memeber  \n",
       "0         2500           1  \n",
       "1         2300           1  \n",
       "2         3500           0  \n",
       "3         4500           1  \n",
       "4         3400           1  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1=df[df['is_memeber']==1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>customer_id</th>\n",
       "      <th>hotel_id</th>\n",
       "      <th>transaction_id</th>\n",
       "      <th>first_night_stay</th>\n",
       "      <th>number_of_nights</th>\n",
       "      <th>total_spend</th>\n",
       "      <th>is_memeber</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>34590</td>\n",
       "      <td>19</td>\n",
       "      <td>34</td>\n",
       "      <td>2017-09-12</td>\n",
       "      <td>2</td>\n",
       "      <td>2500</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12345</td>\n",
       "      <td>6</td>\n",
       "      <td>90</td>\n",
       "      <td>2017-08-23</td>\n",
       "      <td>3</td>\n",
       "      <td>2300</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>45788</td>\n",
       "      <td>13</td>\n",
       "      <td>43</td>\n",
       "      <td>2017-07-31</td>\n",
       "      <td>1</td>\n",
       "      <td>4500</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>87654</td>\n",
       "      <td>17</td>\n",
       "      <td>7</td>\n",
       "      <td>2017-07-25</td>\n",
       "      <td>6</td>\n",
       "      <td>3400</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>34590</td>\n",
       "      <td>19</td>\n",
       "      <td>3</td>\n",
       "      <td>2017-08-19</td>\n",
       "      <td>2</td>\n",
       "      <td>2367</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>54378</td>\n",
       "      <td>32</td>\n",
       "      <td>2</td>\n",
       "      <td>2017-02-20</td>\n",
       "      <td>1</td>\n",
       "      <td>3456</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>34590</td>\n",
       "      <td>66</td>\n",
       "      <td>56</td>\n",
       "      <td>2017-11-28</td>\n",
       "      <td>4</td>\n",
       "      <td>3300</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>87123</td>\n",
       "      <td>77</td>\n",
       "      <td>66</td>\n",
       "      <td>2017-12-08</td>\n",
       "      <td>1</td>\n",
       "      <td>3213</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>90082</td>\n",
       "      <td>10</td>\n",
       "      <td>32</td>\n",
       "      <td>2017-01-16</td>\n",
       "      <td>2</td>\n",
       "      <td>2200</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>34590</td>\n",
       "      <td>15</td>\n",
       "      <td>13</td>\n",
       "      <td>2017-07-14</td>\n",
       "      <td>1</td>\n",
       "      <td>2789</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>45788</td>\n",
       "      <td>5</td>\n",
       "      <td>67</td>\n",
       "      <td>2017-04-17</td>\n",
       "      <td>1</td>\n",
       "      <td>3298</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>12345</td>\n",
       "      <td>10</td>\n",
       "      <td>33</td>\n",
       "      <td>2017-05-22</td>\n",
       "      <td>4</td>\n",
       "      <td>3300</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>11200</td>\n",
       "      <td>76</td>\n",
       "      <td>54</td>\n",
       "      <td>2017-02-13</td>\n",
       "      <td>3</td>\n",
       "      <td>1300</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>11200</td>\n",
       "      <td>31</td>\n",
       "      <td>987</td>\n",
       "      <td>2017-11-16</td>\n",
       "      <td>5</td>\n",
       "      <td>2567</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>11200</td>\n",
       "      <td>31</td>\n",
       "      <td>323</td>\n",
       "      <td>2017-05-22</td>\n",
       "      <td>6</td>\n",
       "      <td>3300</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    customer_id  hotel_id  transaction_id first_night_stay  number_of_nights  \\\n",
       "0         34590        19              34       2017-09-12                 2   \n",
       "1         12345         6              90       2017-08-23                 3   \n",
       "3         45788        13              43       2017-07-31                 1   \n",
       "4         87654        17               7       2017-07-25                 6   \n",
       "6         34590        19               3       2017-08-19                 2   \n",
       "7         54378        32               2       2017-02-20                 1   \n",
       "8         34590        66              56       2017-11-28                 4   \n",
       "9         87123        77              66       2017-12-08                 1   \n",
       "11        90082        10              32       2017-01-16                 2   \n",
       "12        34590        15              13       2017-07-14                 1   \n",
       "13        45788         5              67       2017-04-17                 1   \n",
       "14        12345        10              33       2017-05-22                 4   \n",
       "15        11200        76              54       2017-02-13                 3   \n",
       "16        11200        31             987       2017-11-16                 5   \n",
       "17        11200        31             323       2017-05-22                 6   \n",
       "\n",
       "    total_spend  is_memeber  \n",
       "0          2500           1  \n",
       "1          2300           1  \n",
       "3          4500           1  \n",
       "4          3400           1  \n",
       "6          2367           1  \n",
       "7          3456           1  \n",
       "8          3300           1  \n",
       "9          3213           1  \n",
       "11         2200           1  \n",
       "12         2789           1  \n",
       "13         3298           1  \n",
       "14         3300           1  \n",
       "15         1300           1  \n",
       "16         2567           1  \n",
       "17         3300           1  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>customer_id</th>\n",
       "      <th>hotel_id</th>\n",
       "      <th>number_of_nights</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11200</td>\n",
       "      <td>31</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11200</td>\n",
       "      <td>76</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12345</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12345</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>34590</td>\n",
       "      <td>15</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>34590</td>\n",
       "      <td>19</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>34590</td>\n",
       "      <td>66</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>45788</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>45788</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>54378</td>\n",
       "      <td>32</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>87123</td>\n",
       "      <td>77</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>87654</td>\n",
       "      <td>17</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>90082</td>\n",
       "      <td>10</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    customer_id  hotel_id  number_of_nights\n",
       "0         11200        31                11\n",
       "1         11200        76                 3\n",
       "2         12345         6                 3\n",
       "3         12345        10                 4\n",
       "4         34590        15                 1\n",
       "5         34590        19                 4\n",
       "6         34590        66                 4\n",
       "7         45788         5                 1\n",
       "8         45788        13                 1\n",
       "9         54378        32                 1\n",
       "10        87123        77                 1\n",
       "11        87654        17                 6\n",
       "12        90082        10                 2"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df1, values = 'number_of_nights', index=['customer_id', 'hotel_id'], aggfunc='sum').reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "table=pd.pivot_table(df1, values = ['total_spend'], index=['customer_id', 'hotel_id', 'number_of_nights'], aggfunc=np.sum)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "scrolled": true
   },
   "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></th>\n",
       "      <th></th>\n",
       "      <th>total_spend</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>customer_id</th>\n",
       "      <th>hotel_id</th>\n",
       "      <th>number_of_nights</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">11200</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">31</th>\n",
       "      <th>5</th>\n",
       "      <td>2567</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>3300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <th>3</th>\n",
       "      <td>1300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">12345</th>\n",
       "      <th>6</th>\n",
       "      <th>3</th>\n",
       "      <td>2300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <th>4</th>\n",
       "      <td>3300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">34590</th>\n",
       "      <th>15</th>\n",
       "      <th>1</th>\n",
       "      <td>2789</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <th>2</th>\n",
       "      <td>4867</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <th>4</th>\n",
       "      <td>3300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">45788</th>\n",
       "      <th>5</th>\n",
       "      <th>1</th>\n",
       "      <td>3298</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <th>1</th>\n",
       "      <td>4500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54378</th>\n",
       "      <th>32</th>\n",
       "      <th>1</th>\n",
       "      <td>3456</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87123</th>\n",
       "      <th>77</th>\n",
       "      <th>1</th>\n",
       "      <td>3213</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87654</th>\n",
       "      <th>17</th>\n",
       "      <th>6</th>\n",
       "      <td>3400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90082</th>\n",
       "      <th>10</th>\n",
       "      <th>2</th>\n",
       "      <td>2200</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       total_spend\n",
       "customer_id hotel_id number_of_nights             \n",
       "11200       31       5                        2567\n",
       "                     6                        3300\n",
       "            76       3                        1300\n",
       "12345       6        3                        2300\n",
       "            10       4                        3300\n",
       "34590       15       1                        2789\n",
       "            19       2                        4867\n",
       "            66       4                        3300\n",
       "45788       5        1                        3298\n",
       "            13       1                        4500\n",
       "54378       32       1                        3456\n",
       "87123       77       1                        3213\n",
       "87654       17       6                        3400\n",
       "90082       10       2                        2200"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.frame.DataFrame"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(table)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "14"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table.index.size"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df1_grouped=df1.groupby('customer_id').agg({'hotel_id':'count', 'number_of_nights': 'sum', 'total_spend': 'sum'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.frame.DataFrame"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(df1_grouped)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Pivot also works but querying the pivot table is not as easy as that with a groupedby table\n",
    "table1=pd.pivot_table(df1, values = ['total_spend'], index=['customer_id', 'hotel_id', 'number_of_nights'], aggfunc=np.sum).reset_index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "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></th>\n",
       "      <th>hotel_id</th>\n",
       "      <th>total_spend</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>customer_id</th>\n",
       "      <th>number_of_nights</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">11200</th>\n",
       "      <th>5</th>\n",
       "      <td>31</td>\n",
       "      <td>2567</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>31</td>\n",
       "      <td>3300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>76</td>\n",
       "      <td>1300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">12345</th>\n",
       "      <th>3</th>\n",
       "      <td>6</td>\n",
       "      <td>2300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10</td>\n",
       "      <td>3300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">34590</th>\n",
       "      <th>1</th>\n",
       "      <td>15</td>\n",
       "      <td>2789</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>19</td>\n",
       "      <td>4867</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>66</td>\n",
       "      <td>3300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">45788</th>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>3298</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>13</td>\n",
       "      <td>4500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54378</th>\n",
       "      <th>1</th>\n",
       "      <td>32</td>\n",
       "      <td>3456</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87123</th>\n",
       "      <th>1</th>\n",
       "      <td>77</td>\n",
       "      <td>3213</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87654</th>\n",
       "      <th>6</th>\n",
       "      <td>17</td>\n",
       "      <td>3400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90082</th>\n",
       "      <th>2</th>\n",
       "      <td>10</td>\n",
       "      <td>2200</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              hotel_id  total_spend\n",
       "customer_id number_of_nights                       \n",
       "11200       5                       31         2567\n",
       "            6                       31         3300\n",
       "            3                       76         1300\n",
       "12345       3                        6         2300\n",
       "            4                       10         3300\n",
       "34590       1                       15         2789\n",
       "            2                       19         4867\n",
       "            4                       66         3300\n",
       "45788       1                        5         3298\n",
       "            1                       13         4500\n",
       "54378       1                       32         3456\n",
       "87123       1                       77         3213\n",
       "87654       6                       17         3400\n",
       "90082       2                       10         2200"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table1('hotel_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {
    "scrolled": true
   },
   "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>number_of_nights</th>\n",
       "      <th>hotel_id</th>\n",
       "      <th>total_spend</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>customer_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>11200</th>\n",
       "      <td>14</td>\n",
       "      <td>3</td>\n",
       "      <td>7167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12345</th>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>5600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34590</th>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "      <td>10956</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45788</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>7798</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54378</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3456</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87123</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3213</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87654</th>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>3400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90082</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2200</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             number_of_nights  hotel_id  total_spend\n",
       "customer_id                                         \n",
       "11200                      14         3         7167\n",
       "12345                       7         2         5600\n",
       "34590                       9         4        10956\n",
       "45788                       2         2         7798\n",
       "54378                       1         1         3456\n",
       "87123                       1         1         3213\n",
       "87654                       6         1         3400\n",
       "90082                       2         1         2200"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1_grouped"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "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>number_of_nights</th>\n",
       "      <th>hotel_id</th>\n",
       "      <th>total_spend</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>customer_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>11200</th>\n",
       "      <td>14</td>\n",
       "      <td>3</td>\n",
       "      <td>7167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34590</th>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "      <td>10956</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             number_of_nights  hotel_id  total_spend\n",
       "customer_id                                         \n",
       "11200                      14         3         7167\n",
       "34590                       9         4        10956"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1_grouped[(df1_grouped['total_spend']>3000) & (df1_grouped['hotel_id']>=3) & (df1_grouped['number_of_nights']>=2)]\n",
    "\n",
    "#Solution\n",
    "#As asked in the question the details for loyal customers and amount spent by them by staying at three different locations throughtout the US\n",
    "#and also staying at least 2 nights at every location can be retrieved as represented in this cell\n",
    "#The result can also end up in shaping the definition of loyal customers and accordingly the effort that's being planned to\n",
    "#to effect the marketing strategies "
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda root]",
   "language": "python",
   "name": "conda-root-py"
  },
  "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.5.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}