regency_data_processing.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-14T16:23:27.654306Z",
     "start_time": "2023-11-14T16:23:27.085884Z"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import re\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-14T16:23:27.718644Z",
     "start_time": "2023-11-14T16:23:27.655465Z"
    }
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"data/hotel_csv_data.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-14T16:23:27.719010Z",
     "start_time": "2023-11-14T16:23:27.698083Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 6665 entries, 0 to 6664\n",
      "Data columns (total 61 columns):\n",
      " #   Column                  Non-Null Count  Dtype  \n",
      "---  ------                  --------------  -----  \n",
      " 0   101                     6665 non-null   int64  \n",
      " 1   NK                      6665 non-null   object \n",
      " 2   1                       6665 non-null   int64  \n",
      " 3   NUID065253359           6665 non-null   object \n",
      " 4   1.1                     6665 non-null   object \n",
      " 5   071623 113711 101       6665 non-null   object \n",
      " 6   11:37 AM   07/16/2023   6665 non-null   object \n",
      " 7   2                       6663 non-null   object \n",
      " 8   1.2                     6663 non-null   float64\n",
      " 9   01:37 PM   07/16/2023   6665 non-null   object \n",
      " 10  CreditCard              6665 non-null   object \n",
      " 11  F                       6665 non-null   object \n",
      " 12  41.08                   6665 non-null   float64\n",
      " 13  Unnamed: 13             140 non-null    object \n",
      " 14  0.00                    6665 non-null   float64\n",
      " 15  Unnamed: 15             0 non-null      float64\n",
      " 16  7/16/2023               6665 non-null   object \n",
      " 17  07/14/2023              6665 non-null   object \n",
      " 18  41.08.1                 6665 non-null   float64\n",
      " 19  41.08.2                 6665 non-null   float64\n",
      " 20  0.00.1                  6665 non-null   float64\n",
      " 21  35.11                   6665 non-null   float64\n",
      " 22  TTT                     6665 non-null   object \n",
      " 23  5.97                    6665 non-null   float64\n",
      " 24  41.08.3                 6665 non-null   float64\n",
      " 25  0.00.2                  1885 non-null   float64\n",
      " 26  11:37 AM    07/16/2023  6664 non-null   object \n",
      " 27  2.1                     6664 non-null   float64\n",
      " 28  F.1                     6665 non-null   object \n",
      " 29  BRANDON BANOS           6665 non-null   object \n",
      " 30  489 GULF                6665 non-null   object \n",
      " 31  HOUSTON                 6663 non-null   object \n",
      " 32  TX                      6642 non-null   object \n",
      " 33  77060                   6638 non-null   object \n",
      " 34  Unnamed: 34             1964 non-null   object \n",
      " 35  Unnamed: 35             2310 non-null   object \n",
      " 36  Unnamed: 36             1950 non-null   object \n",
      " 37    /  /                  6664 non-null   object \n",
      " 38  Unnamed: 38             3077 non-null   object \n",
      " 39  Unnamed: 39             43 non-null     object \n",
      " 40  Unnamed: 40             24 non-null     object \n",
      " 41  Unnamed: 41             32 non-null     object \n",
      " 42  Unnamed: 42             33 non-null     object \n",
      " 43  Unnamed: 43             33 non-null     object \n",
      " 44  Unnamed: 44             33 non-null     object \n",
      " 45  Unnamed: 45             111 non-null    object \n",
      " 46  Unnamed: 46             1 non-null      object \n",
      " 47  Unnamed: 47             0 non-null      float64\n",
      " 48  Unnamed: 48             75 non-null     object \n",
      " 49  Unnamed: 49             1 non-null      object \n",
      " 50  0.00.3                  6665 non-null   float64\n",
      " 51  41.08.4                 6665 non-null   float64\n",
      " 52  0.00.4                  6665 non-null   float64\n",
      " 53  0.00.5                  6665 non-null   float64\n",
      " 54  Unnamed: 54             11 non-null     object \n",
      " 55  F.2                     6665 non-null   object \n",
      " 56  F.3                     6665 non-null   object \n",
      " 57  Unnamed: 57             10 non-null     float64\n",
      " 58  3                       6665 non-null   int64  \n",
      " 59  7.00%6.00%4.000%17.000  6665 non-null   object \n",
      " 60  0000                    6665 non-null   int64  \n",
      "dtypes: float64(18), int64(4), object(39)\n",
      "memory usage: 3.1+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-14T16:23:27.721780Z",
     "start_time": "2023-11-14T16:23:27.711318Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "      101  NK  1 NUID065253359 1.1  071623 113711 101   11:37 AM   07/16/2023  \\\n5635  107   K  1    TX29376837   1  092023 054802 107  05:48 PM    09/20/2023   \n4064  115   K  1    TX23890054   1  051123 083147 115  08:31 PM    05/11/2023   \n3918  111   K  1    TX17113940   1  112522 110000 111  11:00 AM    11/28/2022   \n10    114   K  1    TX40887263   1  071623 110000 114   11:00 AM   07/16/2023   \n3077  108   K  1    TX44225771   1  102222 113325 108   11:33 PM   10/22/2022   \n\n      2  1.2   01:37 PM   07/16/2023  ... 41.08.4 0.00.4  0.00.5 Unnamed: 54  \\\n5635  1  1.0  06:48 PM    09/20/2023  ...     0.0    0.0     0.0         NaN   \n4064  1  1.0  09:31 PM    05/11/2023  ...     0.0    0.0     0.0         NaN   \n3918  N  3.0  11:00 AM    12/01/2022  ...   120.0    0.0     0.0         NaN   \n10    N  1.0  11:00 AM    07/17/2023  ...     0.0    0.0     0.0         NaN   \n3077  N  1.0   11:00 AM   10/23/2022  ...     0.0    0.0     0.0         NaN   \n\n      F.2  F.3 Unnamed: 57   3  7.00%6.00%4.000%17.000  0000  \n5635    F    F         NaN  12  7.00%6.00%4.000%17.000     0  \n4064    F    F         NaN   7  7.00%6.00%4.000%17.000     0  \n3918    F    F         NaN   5  7.00%6.00%4.000%17.000     0  \n10      F    F         NaN  35  7.00%6.00%4.000%17.000     0  \n3077    F    F         NaN   2  7.00%6.00%4.000%17.000     0  \n\n[5 rows x 61 columns]",
      "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>101</th>\n      <th>NK</th>\n      <th>1</th>\n      <th>NUID065253359</th>\n      <th>1.1</th>\n      <th>071623 113711 101</th>\n      <th>11:37 AM   07/16/2023</th>\n      <th>2</th>\n      <th>1.2</th>\n      <th>01:37 PM   07/16/2023</th>\n      <th>...</th>\n      <th>41.08.4</th>\n      <th>0.00.4</th>\n      <th>0.00.5</th>\n      <th>Unnamed: 54</th>\n      <th>F.2</th>\n      <th>F.3</th>\n      <th>Unnamed: 57</th>\n      <th>3</th>\n      <th>7.00%6.00%4.000%17.000</th>\n      <th>0000</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>5635</th>\n      <td>107</td>\n      <td>K</td>\n      <td>1</td>\n      <td>TX29376837</td>\n      <td>1</td>\n      <td>092023 054802 107</td>\n      <td>05:48 PM    09/20/2023</td>\n      <td>1</td>\n      <td>1.0</td>\n      <td>06:48 PM    09/20/2023</td>\n      <td>...</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>NaN</td>\n      <td>F</td>\n      <td>F</td>\n      <td>NaN</td>\n      <td>12</td>\n      <td>7.00%6.00%4.000%17.000</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>4064</th>\n      <td>115</td>\n      <td>K</td>\n      <td>1</td>\n      <td>TX23890054</td>\n      <td>1</td>\n      <td>051123 083147 115</td>\n      <td>08:31 PM    05/11/2023</td>\n      <td>1</td>\n      <td>1.0</td>\n      <td>09:31 PM    05/11/2023</td>\n      <td>...</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>NaN</td>\n      <td>F</td>\n      <td>F</td>\n      <td>NaN</td>\n      <td>7</td>\n      <td>7.00%6.00%4.000%17.000</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>3918</th>\n      <td>111</td>\n      <td>K</td>\n      <td>1</td>\n      <td>TX17113940</td>\n      <td>1</td>\n      <td>112522 110000 111</td>\n      <td>11:00 AM    11/28/2022</td>\n      <td>N</td>\n      <td>3.0</td>\n      <td>11:00 AM    12/01/2022</td>\n      <td>...</td>\n      <td>120.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>NaN</td>\n      <td>F</td>\n      <td>F</td>\n      <td>NaN</td>\n      <td>5</td>\n      <td>7.00%6.00%4.000%17.000</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>10</th>\n      <td>114</td>\n      <td>K</td>\n      <td>1</td>\n      <td>TX40887263</td>\n      <td>1</td>\n      <td>071623 110000 114</td>\n      <td>11:00 AM   07/16/2023</td>\n      <td>N</td>\n      <td>1.0</td>\n      <td>11:00 AM    07/17/2023</td>\n      <td>...</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>NaN</td>\n      <td>F</td>\n      <td>F</td>\n      <td>NaN</td>\n      <td>35</td>\n      <td>7.00%6.00%4.000%17.000</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>3077</th>\n      <td>108</td>\n      <td>K</td>\n      <td>1</td>\n      <td>TX44225771</td>\n      <td>1</td>\n      <td>102222 113325 108</td>\n      <td>11:33 PM   10/22/2022</td>\n      <td>N</td>\n      <td>1.0</td>\n      <td>11:00 AM   10/23/2022</td>\n      <td>...</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>NaN</td>\n      <td>F</td>\n      <td>F</td>\n      <td>NaN</td>\n      <td>2</td>\n      <td>7.00%6.00%4.000%17.000</td>\n      <td>0</td>\n    </tr>\n  </tbody>\n</table>\n<p>5 rows × 61 columns</p>\n</div>"
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.sample(n=5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-14T16:23:27.734208Z",
     "start_time": "2023-11-14T16:23:27.721922Z"
    }
   },
   "outputs": [],
   "source": [
    "idx_to_keep = [0, 1, 3, 6, 7, 9, 12, 21, 23, 29]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-14T16:23:27.734459Z",
     "start_time": "2023-11-14T16:23:27.723680Z"
    }
   },
   "outputs": [],
   "source": [
    "# create new dataframe with specified column indices\n",
    "df1 = df.iloc[:, idx_to_keep].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-14T16:23:27.734690Z",
     "start_time": "2023-11-14T16:23:27.727059Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 6665 entries, 0 to 6664\n",
      "Data columns (total 10 columns):\n",
      " #   Column                 Non-Null Count  Dtype  \n",
      "---  ------                 --------------  -----  \n",
      " 0   101                    6665 non-null   int64  \n",
      " 1   NK                     6665 non-null   object \n",
      " 2   NUID065253359          6665 non-null   object \n",
      " 3   11:37 AM   07/16/2023  6665 non-null   object \n",
      " 4   2                      6663 non-null   object \n",
      " 5   01:37 PM   07/16/2023  6665 non-null   object \n",
      " 6   41.08                  6665 non-null   float64\n",
      " 7   35.11                  6665 non-null   float64\n",
      " 8   5.97                   6665 non-null   float64\n",
      " 9   BRANDON BANOS          6665 non-null   object \n",
      "dtypes: float64(3), int64(1), object(6)\n",
      "memory usage: 520.8+ KB\n"
     ]
    }
   ],
   "source": [
    "df1.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-14T16:23:27.736872Z",
     "start_time": "2023-11-14T16:23:27.733965Z"
    }
   },
   "outputs": [],
   "source": [
    "# Create a dictionary to rename the columns\n",
    "column_names = [\n",
    "    \"room_num\",\n",
    "    \"room_type\",\n",
    "    \"customer_id\",\n",
    "    \"checkin_timestamp\",\n",
    "    \"stay_duration\",\n",
    "    \"checkout_timestamp\",\n",
    "    \"total_price\",\n",
    "    \"price\",\n",
    "    \"tax\",\n",
    "    \"customer_name\",\n",
    "]\n",
    "\n",
    "# Assign column names to the DataFrame\n",
    "df1.columns = column_names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-14T16:23:27.764223Z",
     "start_time": "2023-11-14T16:23:27.739555Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "      room_num room_type     customer_id       checkin_timestamp  \\\n3327       130         K  NUID45645848/4   12/27/2022 9:46:00 PM   \n4325       126        NK     NUIDG612368  01:06 AM    07/24/2023   \n1111       109         K   NUID013979894  03:38 PM    11/09/2022   \n5941       103       NQQ      TX09608904   11:00 AM   01/26/2023   \n2250       103       NQQ   NUID205407379   11:00 AM   02/25/2023   \n\n     stay_duration      checkout_timestamp  total_price  price    tax  \\\n3327             N  12/28/2022 11:00:00 AM        46.33  39.60   6.73   \n4325             N  11:00 AM    07/24/2023        70.00  59.83  10.17   \n1111             2  05:38 PM    11/09/2022        30.00  25.64   4.36   \n5941             N  11:00 AM    01/27/2023        70.00  59.83  10.17   \n2250             N  11:00 AM    02/27/2023         0.00   0.00   0.00   \n\n                          customer_name  \n3327                  ARMANDO GERRARDO   \n4325                        ELKA GARCIA  \n1111                  EDWIN QUINTANILLA  \n5941                BERNICE JONES KELLY  \n2250  EDGAR CAESAR MATACILLAS HERNANDEZ  ",
      "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>room_num</th>\n      <th>room_type</th>\n      <th>customer_id</th>\n      <th>checkin_timestamp</th>\n      <th>stay_duration</th>\n      <th>checkout_timestamp</th>\n      <th>total_price</th>\n      <th>price</th>\n      <th>tax</th>\n      <th>customer_name</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>3327</th>\n      <td>130</td>\n      <td>K</td>\n      <td>NUID45645848/4</td>\n      <td>12/27/2022 9:46:00 PM</td>\n      <td>N</td>\n      <td>12/28/2022 11:00:00 AM</td>\n      <td>46.33</td>\n      <td>39.60</td>\n      <td>6.73</td>\n      <td>ARMANDO GERRARDO</td>\n    </tr>\n    <tr>\n      <th>4325</th>\n      <td>126</td>\n      <td>NK</td>\n      <td>NUIDG612368</td>\n      <td>01:06 AM    07/24/2023</td>\n      <td>N</td>\n      <td>11:00 AM    07/24/2023</td>\n      <td>70.00</td>\n      <td>59.83</td>\n      <td>10.17</td>\n      <td>ELKA GARCIA</td>\n    </tr>\n    <tr>\n      <th>1111</th>\n      <td>109</td>\n      <td>K</td>\n      <td>NUID013979894</td>\n      <td>03:38 PM    11/09/2022</td>\n      <td>2</td>\n      <td>05:38 PM    11/09/2022</td>\n      <td>30.00</td>\n      <td>25.64</td>\n      <td>4.36</td>\n      <td>EDWIN QUINTANILLA</td>\n    </tr>\n    <tr>\n      <th>5941</th>\n      <td>103</td>\n      <td>NQQ</td>\n      <td>TX09608904</td>\n      <td>11:00 AM   01/26/2023</td>\n      <td>N</td>\n      <td>11:00 AM    01/27/2023</td>\n      <td>70.00</td>\n      <td>59.83</td>\n      <td>10.17</td>\n      <td>BERNICE JONES KELLY</td>\n    </tr>\n    <tr>\n      <th>2250</th>\n      <td>103</td>\n      <td>NQQ</td>\n      <td>NUID205407379</td>\n      <td>11:00 AM   02/25/2023</td>\n      <td>N</td>\n      <td>11:00 AM    02/27/2023</td>\n      <td>0.00</td>\n      <td>0.00</td>\n      <td>0.00</td>\n      <td>EDGAR CAESAR MATACILLAS HERNANDEZ</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.sample(n=5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-14T16:23:27.764409Z",
     "start_time": "2023-11-14T16:23:27.742308Z"
    }
   },
   "outputs": [],
   "source": [
    "# Convert checkin_timestamp and checkout_timestamp\n",
    "def convert_string_to_datetime(df, column_name, new_column_name):\n",
    "    # Create a datetime object from the string object.\n",
    "    datetime_object = pd.to_datetime(\n",
    "        df[column_name], errors=\"coerce\", format=\"mixed\")\n",
    "\n",
    "    # Add the datetime object to the DataFrame as a new column.\n",
    "    new_datetime_column = f\"{new_column_name}_datetime\"\n",
    "    df[new_datetime_column] = datetime_object\n",
    "\n",
    "    # drop old timestamp columns\n",
    "    df = df.drop([column_name], axis=1)\n",
    "\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-14T16:23:28.201092Z",
     "start_time": "2023-11-14T16:23:27.744700Z"
    }
   },
   "outputs": [],
   "source": [
    "df1 = convert_string_to_datetime(df1.copy(), \"checkin_timestamp\", \"checkin\")\n",
    "df1 = convert_string_to_datetime(df1.copy(), \"checkout_timestamp\", \"checkout\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-14T16:23:28.202884Z",
     "start_time": "2023-11-14T16:23:28.193641Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 6665 entries, 0 to 6664\n",
      "Data columns (total 10 columns):\n",
      " #   Column             Non-Null Count  Dtype         \n",
      "---  ------             --------------  -----         \n",
      " 0   room_num           6665 non-null   int64         \n",
      " 1   room_type          6665 non-null   object        \n",
      " 2   customer_id        6665 non-null   object        \n",
      " 3   stay_duration      6663 non-null   object        \n",
      " 4   total_price        6665 non-null   float64       \n",
      " 5   price              6665 non-null   float64       \n",
      " 6   tax                6665 non-null   float64       \n",
      " 7   customer_name      6665 non-null   object        \n",
      " 8   checkin_datetime   6665 non-null   datetime64[ns]\n",
      " 9   checkout_datetime  6665 non-null   datetime64[ns]\n",
      "dtypes: datetime64[ns](2), float64(3), int64(1), object(4)\n",
      "memory usage: 520.8+ KB\n"
     ]
    }
   ],
   "source": [
    "# drop old timestamp columns\n",
    "df1.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-14T16:23:28.208089Z",
     "start_time": "2023-11-14T16:23:28.203188Z"
    }
   },
   "outputs": [],
   "source": [
    "# Convert the column to a categorical type\n",
    "def convert_dtypes(df, column_name, data_type):\n",
    "    df[column_name] = df[column_name].astype(data_type)\n",
    "\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-14T16:23:28.213890Z",
     "start_time": "2023-11-14T16:23:28.209151Z"
    }
   },
   "outputs": [],
   "source": [
    "# convert object dtypes to category dtype and room_num to category dtype\n",
    "category_features = [\"room_num\", \"room_type\", \"stay_duration\"]\n",
    "\n",
    "data_type = \"category\"\n",
    "\n",
    "for category in category_features:\n",
    "    df1 = convert_dtypes(df1.copy(), category, data_type)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-14T16:23:28.219627Z",
     "start_time": "2023-11-14T16:23:28.215184Z"
    }
   },
   "outputs": [],
   "source": [
    "# Subtracts two datetime columns in a Pandas DataFrame and calculates the difference into a day and hours column.\n",
    "def subtract_datetime_columns(df, column_1, column_2):\n",
    "    # Subtract the two datetime columns.\n",
    "    # Compare the two datetime columns.\n",
    "    greater_datetime_column = df[column_1].where(\n",
    "        df[column_1] > df[column_2], df[column_2]\n",
    "    )\n",
    "    least_datetime_column = df[column_1].where(\n",
    "        df[column_1] <= df[column_2], df[column_2]\n",
    "    )\n",
    "\n",
    "    # Subtract the greater datetime column by the least datetime column.\n",
    "    difference = greater_datetime_column - least_datetime_column\n",
    "    # difference = df[time1] - df[time0]\n",
    "\n",
    "    # Convert the difference to a timedelta object.\n",
    "    difference_timedelta = difference.dt.total_seconds()\n",
    "\n",
    "    # Calculate the day and hours difference.\n",
    "    day_difference = difference_timedelta // (24 * 60 * 60)\n",
    "    hours_difference = (difference_timedelta % (24 * 60 * 60)) // (60 * 60)\n",
    "\n",
    "    # Add the day and hours difference columns to the DataFrame.\n",
    "    df[\"days\"] = day_difference\n",
    "    df[\"hours\"] = hours_difference\n",
    "\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-14T16:23:28.265982Z",
     "start_time": "2023-11-14T16:23:28.217722Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "     room_num room_type            customer_id stay_duration  total_price  \\\n6315      108         K             TX12220802             N          0.0   \n1160      114         K  NUIDMATJ81103HNLLRN04             N         75.0   \n1394      118       NQQ             TX29501795             N         75.0   \n1260      108         K          NUIDN04810684             1         30.0   \n1523      109         K             TX18266335             1         25.0   \n\n      price    tax          customer_name    checkin_datetime  \\\n6315   0.00   0.00   ALVARADO PAUL EDWARD 2023-02-06 11:00:00   \n1160  64.10  10.90  JUAN CARLOS MALDONADO 2023-08-11 11:00:00   \n1394  64.10  10.90          KNOBEL LAUREN 2023-09-12 11:00:00   \n1260  25.64   4.36        GUZMAN JOCELIN  2023-08-06 02:24:00   \n1523  21.37   3.63            GONE CARLOS 2022-12-17 15:35:00   \n\n       checkout_datetime  days  hours  \n6315 2023-02-07 11:00:00   1.0    0.0  \n1160 2023-08-12 11:00:00   1.0    0.0  \n1394 2023-09-13 11:00:00   1.0    0.0  \n1260 2023-08-06 03:24:00   0.0    1.0  \n1523 2022-12-17 16:35:00   0.0    1.0  ",
      "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>room_num</th>\n      <th>room_type</th>\n      <th>customer_id</th>\n      <th>stay_duration</th>\n      <th>total_price</th>\n      <th>price</th>\n      <th>tax</th>\n      <th>customer_name</th>\n      <th>checkin_datetime</th>\n      <th>checkout_datetime</th>\n      <th>days</th>\n      <th>hours</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>6315</th>\n      <td>108</td>\n      <td>K</td>\n      <td>TX12220802</td>\n      <td>N</td>\n      <td>0.0</td>\n      <td>0.00</td>\n      <td>0.00</td>\n      <td>ALVARADO PAUL EDWARD</td>\n      <td>2023-02-06 11:00:00</td>\n      <td>2023-02-07 11:00:00</td>\n      <td>1.0</td>\n      <td>0.0</td>\n    </tr>\n    <tr>\n      <th>1160</th>\n      <td>114</td>\n      <td>K</td>\n      <td>NUIDMATJ81103HNLLRN04</td>\n      <td>N</td>\n      <td>75.0</td>\n      <td>64.10</td>\n      <td>10.90</td>\n      <td>JUAN CARLOS MALDONADO</td>\n      <td>2023-08-11 11:00:00</td>\n      <td>2023-08-12 11:00:00</td>\n      <td>1.0</td>\n      <td>0.0</td>\n    </tr>\n    <tr>\n      <th>1394</th>\n      <td>118</td>\n      <td>NQQ</td>\n      <td>TX29501795</td>\n      <td>N</td>\n      <td>75.0</td>\n      <td>64.10</td>\n      <td>10.90</td>\n      <td>KNOBEL LAUREN</td>\n      <td>2023-09-12 11:00:00</td>\n      <td>2023-09-13 11:00:00</td>\n      <td>1.0</td>\n      <td>0.0</td>\n    </tr>\n    <tr>\n      <th>1260</th>\n      <td>108</td>\n      <td>K</td>\n      <td>NUIDN04810684</td>\n      <td>1</td>\n      <td>30.0</td>\n      <td>25.64</td>\n      <td>4.36</td>\n      <td>GUZMAN JOCELIN</td>\n      <td>2023-08-06 02:24:00</td>\n      <td>2023-08-06 03:24:00</td>\n      <td>0.0</td>\n      <td>1.0</td>\n    </tr>\n    <tr>\n      <th>1523</th>\n      <td>109</td>\n      <td>K</td>\n      <td>TX18266335</td>\n      <td>1</td>\n      <td>25.0</td>\n      <td>21.37</td>\n      <td>3.63</td>\n      <td>GONE CARLOS</td>\n      <td>2022-12-17 15:35:00</td>\n      <td>2022-12-17 16:35:00</td>\n      <td>0.0</td>\n      <td>1.0</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = subtract_datetime_columns(\n",
    "    df1.copy(), \"checkin_datetime\", \"checkout_datetime\")\n",
    "\n",
    "df1.sample(n=5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-14T16:23:28.266801Z",
     "start_time": "2023-11-14T16:23:28.229665Z"
    }
   },
   "outputs": [],
   "source": [
    "# Bucket checkin_datetime into four time periods.\n",
    "def bucket_time_period(datetime_value):\n",
    "    \"\"\"\n",
    "    Buckets a datetime value into one of four time periods.\n",
    "        * Morning (8:00 AM to 12:00 PM)\n",
    "        * Afternoon (12:00 PM to 5:00 PM)\n",
    "        * Evening (5:00 PM to 9:00 PM)\n",
    "        * Night (9:00 PM to 8:00 AM the next day)\n",
    "    \"\"\"\n",
    "\n",
    "    hour = datetime_value.hour\n",
    "\n",
    "    if hour >= 8 and hour < 12:\n",
    "        time_period = 'Morning'\n",
    "    elif hour >= 12 and hour < 17:\n",
    "        time_period = 'Afternoon'\n",
    "    elif hour >= 17 and hour < 21:\n",
    "        time_period = 'Evening'\n",
    "    else:\n",
    "        time_period = 'Night'\n",
    "\n",
    "    return time_period\n",
    "\n",
    "# parse datetime columns into month, day, and year\n",
    "def parse_datetime_column(df, datetime_column, datetime_category):\n",
    "    # Parse the `datetime` column and extract the `month`, `day`, and `year`.\n",
    "    year = f\"{datetime_category}_year\"\n",
    "    month = f\"{datetime_category}_month\"\n",
    "    day = f\"{datetime_category}_day\"\n",
    "    time = f\"{datetime_category}_time\"\n",
    "    weekday = f\"{datetime_category}_weekday\"\n",
    "    day_name = f\"{datetime_category}_day_name\"\n",
    "    time_period = f\"{datetime_category}_time_period\"\n",
    "\n",
    "    df[month] = df[datetime_column].dt.month\n",
    "    df[day] = df[datetime_column].dt.day\n",
    "    df[year] = df[datetime_column].dt.year\n",
    "    df[time] = df[datetime_column].dt.time\n",
    "    df[weekday] = df[datetime_column].dt.weekday # Monday is 0 and Sunday is 6\n",
    "    df[day_name] = df[datetime_column].dt.day_name()\n",
    "    df[time_period] = df[datetime_column].apply(lambda x: bucket_time_period(x))\n",
    "\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-14T16:23:28.267080Z",
     "start_time": "2023-11-14T16:23:28.232118Z"
    }
   },
   "outputs": [],
   "source": [
    "df1 = parse_datetime_column(df1.copy(), \"checkin_datetime\", \"checkin\")\n",
    "df1 = parse_datetime_column(df1.copy(), \"checkout_datetime\", \"checkout\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-14T16:23:28.268289Z",
     "start_time": "2023-11-14T16:23:28.252529Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "     room_num room_type customer_id stay_duration  total_price  price   tax  \\\n2724      105       NKH  TX48470687             2         35.0  29.91  5.09   \n\n                customer_name    checkin_datetime   checkout_datetime  ...  \\\n2724  AREVALO ACEVEDO GERARDO 2023-08-24 12:58:00 2023-08-24 14:58:00  ...   \n\n      checkin_weekday  checkin_day_name  checkin_time_period  checkout_month  \\\n2724                3          Thursday            Afternoon               8   \n\n      checkout_day checkout_year  checkout_time checkout_weekday  \\\n2724            24          2023       14:58:00                3   \n\n     checkout_day_name  checkout_time_period  \n2724          Thursday             Afternoon  \n\n[1 rows x 26 columns]",
      "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>room_num</th>\n      <th>room_type</th>\n      <th>customer_id</th>\n      <th>stay_duration</th>\n      <th>total_price</th>\n      <th>price</th>\n      <th>tax</th>\n      <th>customer_name</th>\n      <th>checkin_datetime</th>\n      <th>checkout_datetime</th>\n      <th>...</th>\n      <th>checkin_weekday</th>\n      <th>checkin_day_name</th>\n      <th>checkin_time_period</th>\n      <th>checkout_month</th>\n      <th>checkout_day</th>\n      <th>checkout_year</th>\n      <th>checkout_time</th>\n      <th>checkout_weekday</th>\n      <th>checkout_day_name</th>\n      <th>checkout_time_period</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>2724</th>\n      <td>105</td>\n      <td>NKH</td>\n      <td>TX48470687</td>\n      <td>2</td>\n      <td>35.0</td>\n      <td>29.91</td>\n      <td>5.09</td>\n      <td>AREVALO ACEVEDO GERARDO</td>\n      <td>2023-08-24 12:58:00</td>\n      <td>2023-08-24 14:58:00</td>\n      <td>...</td>\n      <td>3</td>\n      <td>Thursday</td>\n      <td>Afternoon</td>\n      <td>8</td>\n      <td>24</td>\n      <td>2023</td>\n      <td>14:58:00</td>\n      <td>3</td>\n      <td>Thursday</td>\n      <td>Afternoon</td>\n    </tr>\n  </tbody>\n</table>\n<p>1 rows × 26 columns</p>\n</div>"
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.sample()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-14T16:23:28.285404Z",
     "start_time": "2023-11-14T16:23:28.260502Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 6665 entries, 0 to 6664\n",
      "Data columns (total 26 columns):\n",
      " #   Column                Non-Null Count  Dtype         \n",
      "---  ------                --------------  -----         \n",
      " 0   room_num              6665 non-null   category      \n",
      " 1   room_type             6665 non-null   category      \n",
      " 2   customer_id           6665 non-null   object        \n",
      " 3   stay_duration         6663 non-null   category      \n",
      " 4   total_price           6665 non-null   float64       \n",
      " 5   price                 6665 non-null   float64       \n",
      " 6   tax                   6665 non-null   float64       \n",
      " 7   customer_name         6665 non-null   object        \n",
      " 8   checkin_datetime      6665 non-null   datetime64[ns]\n",
      " 9   checkout_datetime     6665 non-null   datetime64[ns]\n",
      " 10  days                  6665 non-null   float64       \n",
      " 11  hours                 6665 non-null   float64       \n",
      " 12  checkin_month         6665 non-null   int32         \n",
      " 13  checkin_day           6665 non-null   int32         \n",
      " 14  checkin_year          6665 non-null   int32         \n",
      " 15  checkin_time          6665 non-null   object        \n",
      " 16  checkin_weekday       6665 non-null   int32         \n",
      " 17  checkin_day_name      6665 non-null   object        \n",
      " 18  checkin_time_period   6665 non-null   object        \n",
      " 19  checkout_month        6665 non-null   int32         \n",
      " 20  checkout_day          6665 non-null   int32         \n",
      " 21  checkout_year         6665 non-null   int32         \n",
      " 22  checkout_time         6665 non-null   object        \n",
      " 23  checkout_weekday      6665 non-null   int32         \n",
      " 24  checkout_day_name     6665 non-null   object        \n",
      " 25  checkout_time_period  6665 non-null   object        \n",
      "dtypes: category(3), datetime64[ns](2), float64(5), int32(8), object(8)\n",
      "memory usage: 1011.3+ KB\n"
     ]
    }
   ],
   "source": [
    "df1.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "outputs": [],
   "source": [
    "# df1.to_csv(\"/Users/zm00g/Data_Science/regency_suites/data/qinn_processed_data.csv\", index=False)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2023-11-14T21:30:27.846542Z",
     "start_time": "2023-11-14T21:30:27.789014Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "# EDA"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Missing Data Analysis\n"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-14T16:23:28.310667Z",
     "start_time": "2023-11-14T16:23:28.272818Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "     room_num room_type    customer_id stay_duration  total_price  price  tax  \\\n3053      105       NKH     TX36767843             3          0.0    0.0  0.0   \n3055      104        NK  NUID205439626             2          0.0    0.0  0.0   \n3060      122        NK     TX20958699             N          0.0    0.0  0.0   \n3063      107         K    NUIDE225062             3          0.0    0.0  0.0   \n6437      116         K     TX16285729             W          0.0    0.0  0.0   \n...       ...       ...            ...           ...          ...    ...  ...   \n5813      105       NKH     TX19205244             N          0.0    0.0  0.0   \n6145      107         K     TX08922944             2          0.0    0.0  0.0   \n5663      122        NK    LA009138661             N          0.0    0.0  0.0   \n5420      124        NK     TX46628099             N          0.0    0.0  0.0   \n979       111         K     TX34165023             N          0.0    0.0  0.0   \n\n           customer_name    checkin_datetime   checkout_datetime  ...  \\\n3053     SMITH DOMINIQUE 2022-10-25 13:08:00 2022-10-25 16:08:00  ...   \n3055        CESAR TORRES 2022-10-25 15:59:00 2022-10-25 17:59:00  ...   \n3060    MEREDITH RICHARD 2022-10-25 17:22:00 2022-10-26 11:00:00  ...   \n3063         RENE LOPEZ  2022-10-25 19:04:00 2022-10-25 22:04:00  ...   \n6437      GREOGORY BATES 2022-10-26 11:00:00 2022-11-02 11:00:00  ...   \n...                  ...                 ...                 ...  ...   \n5813       RAMIREZ LAURA 2023-07-22 01:14:00 2023-07-22 11:00:00  ...   \n6145     JONES JULIAN SR 2023-08-02 16:38:00 2023-08-02 18:38:00  ...   \n5663  WRIGHT TYVON MARSE 2023-08-18 13:33:00 2023-08-19 11:00:00  ...   \n5420      BRANSOM BONNIE 2023-08-22 19:05:00 2023-08-24 11:00:00  ...   \n979       NORRIS JESSICA 2023-09-27 11:00:00 2023-09-27 11:00:00  ...   \n\n      checkin_weekday  checkin_day_name  checkin_time_period  checkout_month  \\\n3053                1           Tuesday            Afternoon              10   \n3055                1           Tuesday            Afternoon              10   \n3060                1           Tuesday              Evening              10   \n3063                1           Tuesday              Evening              10   \n6437                2         Wednesday              Morning              11   \n...               ...               ...                  ...             ...   \n5813                5          Saturday                Night               7   \n6145                2         Wednesday            Afternoon               8   \n5663                4            Friday            Afternoon               8   \n5420                1           Tuesday              Evening               8   \n979                 2         Wednesday              Morning               9   \n\n      checkout_day checkout_year  checkout_time checkout_weekday  \\\n3053            25          2022       16:08:00                1   \n3055            25          2022       17:59:00                1   \n3060            26          2022       11:00:00                2   \n3063            25          2022       22:04:00                1   \n6437             2          2022       11:00:00                2   \n...            ...           ...            ...              ...   \n5813            22          2023       11:00:00                5   \n6145             2          2023       18:38:00                2   \n5663            19          2023       11:00:00                5   \n5420            24          2023       11:00:00                3   \n979             27          2023       11:00:00                2   \n\n     checkout_day_name  checkout_time_period  \n3053           Tuesday             Afternoon  \n3055           Tuesday               Evening  \n3060         Wednesday               Morning  \n3063           Tuesday                 Night  \n6437         Wednesday               Morning  \n...                ...                   ...  \n5813          Saturday               Morning  \n6145         Wednesday               Evening  \n5663          Saturday               Morning  \n5420          Thursday               Morning  \n979          Wednesday               Morning  \n\n[608 rows x 26 columns]",
      "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>room_num</th>\n      <th>room_type</th>\n      <th>customer_id</th>\n      <th>stay_duration</th>\n      <th>total_price</th>\n      <th>price</th>\n      <th>tax</th>\n      <th>customer_name</th>\n      <th>checkin_datetime</th>\n      <th>checkout_datetime</th>\n      <th>...</th>\n      <th>checkin_weekday</th>\n      <th>checkin_day_name</th>\n      <th>checkin_time_period</th>\n      <th>checkout_month</th>\n      <th>checkout_day</th>\n      <th>checkout_year</th>\n      <th>checkout_time</th>\n      <th>checkout_weekday</th>\n      <th>checkout_day_name</th>\n      <th>checkout_time_period</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>3053</th>\n      <td>105</td>\n      <td>NKH</td>\n      <td>TX36767843</td>\n      <td>3</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>SMITH DOMINIQUE</td>\n      <td>2022-10-25 13:08:00</td>\n      <td>2022-10-25 16:08:00</td>\n      <td>...</td>\n      <td>1</td>\n      <td>Tuesday</td>\n      <td>Afternoon</td>\n      <td>10</td>\n      <td>25</td>\n      <td>2022</td>\n      <td>16:08:00</td>\n      <td>1</td>\n      <td>Tuesday</td>\n      <td>Afternoon</td>\n    </tr>\n    <tr>\n      <th>3055</th>\n      <td>104</td>\n      <td>NK</td>\n      <td>NUID205439626</td>\n      <td>2</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>CESAR TORRES</td>\n      <td>2022-10-25 15:59:00</td>\n      <td>2022-10-25 17:59:00</td>\n      <td>...</td>\n      <td>1</td>\n      <td>Tuesday</td>\n      <td>Afternoon</td>\n      <td>10</td>\n      <td>25</td>\n      <td>2022</td>\n      <td>17:59:00</td>\n      <td>1</td>\n      <td>Tuesday</td>\n      <td>Evening</td>\n    </tr>\n    <tr>\n      <th>3060</th>\n      <td>122</td>\n      <td>NK</td>\n      <td>TX20958699</td>\n      <td>N</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>MEREDITH RICHARD</td>\n      <td>2022-10-25 17:22:00</td>\n      <td>2022-10-26 11:00:00</td>\n      <td>...</td>\n      <td>1</td>\n      <td>Tuesday</td>\n      <td>Evening</td>\n      <td>10</td>\n      <td>26</td>\n      <td>2022</td>\n      <td>11:00:00</td>\n      <td>2</td>\n      <td>Wednesday</td>\n      <td>Morning</td>\n    </tr>\n    <tr>\n      <th>3063</th>\n      <td>107</td>\n      <td>K</td>\n      <td>NUIDE225062</td>\n      <td>3</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>RENE LOPEZ</td>\n      <td>2022-10-25 19:04:00</td>\n      <td>2022-10-25 22:04:00</td>\n      <td>...</td>\n      <td>1</td>\n      <td>Tuesday</td>\n      <td>Evening</td>\n      <td>10</td>\n      <td>25</td>\n      <td>2022</td>\n      <td>22:04:00</td>\n      <td>1</td>\n      <td>Tuesday</td>\n      <td>Night</td>\n    </tr>\n    <tr>\n      <th>6437</th>\n      <td>116</td>\n      <td>K</td>\n      <td>TX16285729</td>\n      <td>W</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>GREOGORY BATES</td>\n      <td>2022-10-26 11:00:00</td>\n      <td>2022-11-02 11:00:00</td>\n      <td>...</td>\n      <td>2</td>\n      <td>Wednesday</td>\n      <td>Morning</td>\n      <td>11</td>\n      <td>2</td>\n      <td>2022</td>\n      <td>11:00:00</td>\n      <td>2</td>\n      <td>Wednesday</td>\n      <td>Morning</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>5813</th>\n      <td>105</td>\n      <td>NKH</td>\n      <td>TX19205244</td>\n      <td>N</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>RAMIREZ LAURA</td>\n      <td>2023-07-22 01:14:00</td>\n      <td>2023-07-22 11:00:00</td>\n      <td>...</td>\n      <td>5</td>\n      <td>Saturday</td>\n      <td>Night</td>\n      <td>7</td>\n      <td>22</td>\n      <td>2023</td>\n      <td>11:00:00</td>\n      <td>5</td>\n      <td>Saturday</td>\n      <td>Morning</td>\n    </tr>\n    <tr>\n      <th>6145</th>\n      <td>107</td>\n      <td>K</td>\n      <td>TX08922944</td>\n      <td>2</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>JONES JULIAN SR</td>\n      <td>2023-08-02 16:38:00</td>\n      <td>2023-08-02 18:38:00</td>\n      <td>...</td>\n      <td>2</td>\n      <td>Wednesday</td>\n      <td>Afternoon</td>\n      <td>8</td>\n      <td>2</td>\n      <td>2023</td>\n      <td>18:38:00</td>\n      <td>2</td>\n      <td>Wednesday</td>\n      <td>Evening</td>\n    </tr>\n    <tr>\n      <th>5663</th>\n      <td>122</td>\n      <td>NK</td>\n      <td>LA009138661</td>\n      <td>N</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>WRIGHT TYVON MARSE</td>\n      <td>2023-08-18 13:33:00</td>\n      <td>2023-08-19 11:00:00</td>\n      <td>...</td>\n      <td>4</td>\n      <td>Friday</td>\n      <td>Afternoon</td>\n      <td>8</td>\n      <td>19</td>\n      <td>2023</td>\n      <td>11:00:00</td>\n      <td>5</td>\n      <td>Saturday</td>\n      <td>Morning</td>\n    </tr>\n    <tr>\n      <th>5420</th>\n      <td>124</td>\n      <td>NK</td>\n      <td>TX46628099</td>\n      <td>N</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>BRANSOM BONNIE</td>\n      <td>2023-08-22 19:05:00</td>\n      <td>2023-08-24 11:00:00</td>\n      <td>...</td>\n      <td>1</td>\n      <td>Tuesday</td>\n      <td>Evening</td>\n      <td>8</td>\n      <td>24</td>\n      <td>2023</td>\n      <td>11:00:00</td>\n      <td>3</td>\n      <td>Thursday</td>\n      <td>Morning</td>\n    </tr>\n    <tr>\n      <th>979</th>\n      <td>111</td>\n      <td>K</td>\n      <td>TX34165023</td>\n      <td>N</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>NORRIS JESSICA</td>\n      <td>2023-09-27 11:00:00</td>\n      <td>2023-09-27 11:00:00</td>\n      <td>...</td>\n      <td>2</td>\n      <td>Wednesday</td>\n      <td>Morning</td>\n      <td>9</td>\n      <td>27</td>\n      <td>2023</td>\n      <td>11:00:00</td>\n      <td>2</td>\n      <td>Wednesday</td>\n      <td>Morning</td>\n    </tr>\n  </tbody>\n</table>\n<p>608 rows × 26 columns</p>\n</div>"
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Filter the DataFrame to only include rows where the `total_price` column equals 0.0.\n",
    "price_filtered_df = df1[df1[\"total_price\"] == 0.0].sort_values(\n",
    "    by=\"checkin_datetime\", ascending=True\n",
    ")\n",
    "\n",
    "price_filtered_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "outputs": [],
   "source": [
    "# price_filtered_df.to_csv(\"/Users/zm00g/Data_Science/regency_suites/data/missing_price.csv\", index=False)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2023-11-14T21:04:24.027783Z",
     "start_time": "2023-11-14T21:04:24.015048Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-14T16:23:28.312134Z",
     "start_time": "2023-11-14T16:23:28.285546Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are zeroed records spanning from 2022-10-25 13:08:00 to 2023-09-27 11:00:00\n"
     ]
    }
   ],
   "source": [
    "min_date = price_filtered_df[\"checkin_datetime\"].min()\n",
    "max_date = price_filtered_df[\"checkout_datetime\"].max()\n",
    "\n",
    "print(f\"There are zeroed records spanning from {min_date} to {max_date}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-14T16:23:28.353525Z",
     "start_time": "2023-11-14T16:23:28.288867Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "checkin_year  checkin_month\n2022          12                75\n              11                41\n              10                 9\n2023          4                106\n              1                105\n              5                105\n              3                 82\n              2                 67\n              6                  7\n              7                  7\n              8                  3\n              9                  1\nName: count, dtype: int64"
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Group the filtered DataFrame by the category column and count the number of rows in each group.\n",
    "price_filtered_df.groupby(\"checkin_year\")[\"checkin_month\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-14T16:23:28.354874Z",
     "start_time": "2023-11-14T16:23:28.300836Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "     room_num room_type  customer_id stay_duration  total_price  price  tax  \\\n518       117         K   TX%TXHOUST           NaN          0.0    0.0  0.0   \n5765      108         K   TX29088150             D          0.0    0.0  0.0   \n2102      114         K   TX40887263             N          0.0    0.0  0.0   \n4568      104        NK   TX43265970             D          0.0    0.0  0.0   \n2960      112         K   TX41901499             N          0.0    0.0  0.0   \n4259      112         K   TX41901499             N          0.0    0.0  0.0   \n322       112         K   TX41901499             N          0.0    0.0  0.0   \n328       112         K   TX41901499             N          0.0    0.0  0.0   \n1855      126        NK   TX41901499             W          0.0    0.0  0.0   \n3837      115         K   TX16699939             N          0.0    0.0  0.0   \n12        126        NK   TX41901499             N          0.0    0.0  0.0   \n5107      126        NK   TX41901499             N          0.0    0.0  0.0   \n2144      106         K   TX26187232             1          0.0    0.0  0.0   \n5813      105       NKH   TX19205244             N          0.0    0.0  0.0   \n6145      107         K   TX08922944             2          0.0    0.0  0.0   \n5663      122        NK  LA009138661             N          0.0    0.0  0.0   \n5420      124        NK   TX46628099             N          0.0    0.0  0.0   \n979       111         K   TX34165023             N          0.0    0.0  0.0   \n\n                                          customer_name    checkin_datetime  \\\n518   ON^RAMIREZ$JOSE$JR^2117 MADGE ST^?;63601510322... 2023-06-15 16:34:00   \n5765                                    HARRISON LATOYA 2023-06-21 03:43:00   \n2102                                     MYERS JASON JR 2023-06-21 16:00:00   \n4568                                     OGWUCHE JOSEPH 2023-06-22 14:58:00   \n2960                                 RODNEY SHANE JILES 2023-06-28 11:00:00   \n4259                                 RODNEY SHANE JILES 2023-06-29 11:00:00   \n322                                  RODNEY SHANE JILES 2023-06-30 11:00:00   \n328                                  RODNEY SHANE JILES 2023-07-01 11:00:00   \n1855                                 RODNEY SHANE JILES 2023-07-09 11:00:00   \n3837                            WILLIAMS JOSEPH DENISAL 2023-07-12 01:51:00   \n12                                   RODNEY SHANE JILES 2023-07-16 11:00:00   \n5107                                 RODNEY SHANE JILES 2023-07-17 11:00:00   \n2144                               GAINES DAMIEN XAVIER 2023-07-18 19:39:00   \n5813                                      RAMIREZ LAURA 2023-07-22 01:14:00   \n6145                                    JONES JULIAN SR 2023-08-02 16:38:00   \n5663                                 WRIGHT TYVON MARSE 2023-08-18 13:33:00   \n5420                                     BRANSOM BONNIE 2023-08-22 19:05:00   \n979                                      NORRIS JESSICA 2023-09-27 11:00:00   \n\n       checkout_datetime  ...  checkin_weekday  checkin_day_name  \\\n518  2023-06-15 16:34:00  ...                3          Thursday   \n5765 2023-06-21 08:43:00  ...                2         Wednesday   \n2102 2023-06-22 11:00:00  ...                2         Wednesday   \n4568 2023-06-22 19:58:00  ...                3          Thursday   \n2960 2023-06-29 11:00:00  ...                2         Wednesday   \n4259 2023-06-30 11:00:00  ...                3          Thursday   \n322  2023-07-01 11:00:00  ...                4            Friday   \n328  2023-07-07 11:00:00  ...                5          Saturday   \n1855 2023-07-16 11:00:00  ...                6            Sunday   \n3837 2023-07-12 11:00:00  ...                2         Wednesday   \n12   2023-07-17 11:00:00  ...                6            Sunday   \n5107 2023-07-18 11:00:00  ...                0            Monday   \n2144 2023-07-18 20:39:00  ...                1           Tuesday   \n5813 2023-07-22 11:00:00  ...                5          Saturday   \n6145 2023-08-02 18:38:00  ...                2         Wednesday   \n5663 2023-08-19 11:00:00  ...                4            Friday   \n5420 2023-08-24 11:00:00  ...                1           Tuesday   \n979  2023-09-27 11:00:00  ...                2         Wednesday   \n\n      checkin_time_period  checkout_month  checkout_day checkout_year  \\\n518             Afternoon               6            15          2023   \n5765                Night               6            21          2023   \n2102            Afternoon               6            22          2023   \n4568            Afternoon               6            22          2023   \n2960              Morning               6            29          2023   \n4259              Morning               6            30          2023   \n322               Morning               7             1          2023   \n328               Morning               7             7          2023   \n1855              Morning               7            16          2023   \n3837                Night               7            12          2023   \n12                Morning               7            17          2023   \n5107              Morning               7            18          2023   \n2144              Evening               7            18          2023   \n5813                Night               7            22          2023   \n6145            Afternoon               8             2          2023   \n5663            Afternoon               8            19          2023   \n5420              Evening               8            24          2023   \n979               Morning               9            27          2023   \n\n      checkout_time checkout_weekday checkout_day_name  checkout_time_period  \n518        16:34:00                3          Thursday             Afternoon  \n5765       08:43:00                2         Wednesday               Morning  \n2102       11:00:00                3          Thursday               Morning  \n4568       19:58:00                3          Thursday               Evening  \n2960       11:00:00                3          Thursday               Morning  \n4259       11:00:00                4            Friday               Morning  \n322        11:00:00                5          Saturday               Morning  \n328        11:00:00                4            Friday               Morning  \n1855       11:00:00                6            Sunday               Morning  \n3837       11:00:00                2         Wednesday               Morning  \n12         11:00:00                0            Monday               Morning  \n5107       11:00:00                1           Tuesday               Morning  \n2144       20:39:00                1           Tuesday               Evening  \n5813       11:00:00                5          Saturday               Morning  \n6145       18:38:00                2         Wednesday               Evening  \n5663       11:00:00                5          Saturday               Morning  \n5420       11:00:00                3          Thursday               Morning  \n979        11:00:00                2         Wednesday               Morning  \n\n[18 rows x 26 columns]",
      "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>room_num</th>\n      <th>room_type</th>\n      <th>customer_id</th>\n      <th>stay_duration</th>\n      <th>total_price</th>\n      <th>price</th>\n      <th>tax</th>\n      <th>customer_name</th>\n      <th>checkin_datetime</th>\n      <th>checkout_datetime</th>\n      <th>...</th>\n      <th>checkin_weekday</th>\n      <th>checkin_day_name</th>\n      <th>checkin_time_period</th>\n      <th>checkout_month</th>\n      <th>checkout_day</th>\n      <th>checkout_year</th>\n      <th>checkout_time</th>\n      <th>checkout_weekday</th>\n      <th>checkout_day_name</th>\n      <th>checkout_time_period</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>518</th>\n      <td>117</td>\n      <td>K</td>\n      <td>TX%TXHOUST</td>\n      <td>NaN</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>ON^RAMIREZ$JOSE$JR^2117 MADGE ST^?;63601510322...</td>\n      <td>2023-06-15 16:34:00</td>\n      <td>2023-06-15 16:34:00</td>\n      <td>...</td>\n      <td>3</td>\n      <td>Thursday</td>\n      <td>Afternoon</td>\n      <td>6</td>\n      <td>15</td>\n      <td>2023</td>\n      <td>16:34:00</td>\n      <td>3</td>\n      <td>Thursday</td>\n      <td>Afternoon</td>\n    </tr>\n    <tr>\n      <th>5765</th>\n      <td>108</td>\n      <td>K</td>\n      <td>TX29088150</td>\n      <td>D</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>HARRISON LATOYA</td>\n      <td>2023-06-21 03:43:00</td>\n      <td>2023-06-21 08:43:00</td>\n      <td>...</td>\n      <td>2</td>\n      <td>Wednesday</td>\n      <td>Night</td>\n      <td>6</td>\n      <td>21</td>\n      <td>2023</td>\n      <td>08:43:00</td>\n      <td>2</td>\n      <td>Wednesday</td>\n      <td>Morning</td>\n    </tr>\n    <tr>\n      <th>2102</th>\n      <td>114</td>\n      <td>K</td>\n      <td>TX40887263</td>\n      <td>N</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>MYERS JASON JR</td>\n      <td>2023-06-21 16:00:00</td>\n      <td>2023-06-22 11:00:00</td>\n      <td>...</td>\n      <td>2</td>\n      <td>Wednesday</td>\n      <td>Afternoon</td>\n      <td>6</td>\n      <td>22</td>\n      <td>2023</td>\n      <td>11:00:00</td>\n      <td>3</td>\n      <td>Thursday</td>\n      <td>Morning</td>\n    </tr>\n    <tr>\n      <th>4568</th>\n      <td>104</td>\n      <td>NK</td>\n      <td>TX43265970</td>\n      <td>D</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>OGWUCHE JOSEPH</td>\n      <td>2023-06-22 14:58:00</td>\n      <td>2023-06-22 19:58:00</td>\n      <td>...</td>\n      <td>3</td>\n      <td>Thursday</td>\n      <td>Afternoon</td>\n      <td>6</td>\n      <td>22</td>\n      <td>2023</td>\n      <td>19:58:00</td>\n      <td>3</td>\n      <td>Thursday</td>\n      <td>Evening</td>\n    </tr>\n    <tr>\n      <th>2960</th>\n      <td>112</td>\n      <td>K</td>\n      <td>TX41901499</td>\n      <td>N</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>RODNEY SHANE JILES</td>\n      <td>2023-06-28 11:00:00</td>\n      <td>2023-06-29 11:00:00</td>\n      <td>...</td>\n      <td>2</td>\n      <td>Wednesday</td>\n      <td>Morning</td>\n      <td>6</td>\n      <td>29</td>\n      <td>2023</td>\n      <td>11:00:00</td>\n      <td>3</td>\n      <td>Thursday</td>\n      <td>Morning</td>\n    </tr>\n    <tr>\n      <th>4259</th>\n      <td>112</td>\n      <td>K</td>\n      <td>TX41901499</td>\n      <td>N</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>RODNEY SHANE JILES</td>\n      <td>2023-06-29 11:00:00</td>\n      <td>2023-06-30 11:00:00</td>\n      <td>...</td>\n      <td>3</td>\n      <td>Thursday</td>\n      <td>Morning</td>\n      <td>6</td>\n      <td>30</td>\n      <td>2023</td>\n      <td>11:00:00</td>\n      <td>4</td>\n      <td>Friday</td>\n      <td>Morning</td>\n    </tr>\n    <tr>\n      <th>322</th>\n      <td>112</td>\n      <td>K</td>\n      <td>TX41901499</td>\n      <td>N</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>RODNEY SHANE JILES</td>\n      <td>2023-06-30 11:00:00</td>\n      <td>2023-07-01 11:00:00</td>\n      <td>...</td>\n      <td>4</td>\n      <td>Friday</td>\n      <td>Morning</td>\n      <td>7</td>\n      <td>1</td>\n      <td>2023</td>\n      <td>11:00:00</td>\n      <td>5</td>\n      <td>Saturday</td>\n      <td>Morning</td>\n    </tr>\n    <tr>\n      <th>328</th>\n      <td>112</td>\n      <td>K</td>\n      <td>TX41901499</td>\n      <td>N</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>RODNEY SHANE JILES</td>\n      <td>2023-07-01 11:00:00</td>\n      <td>2023-07-07 11:00:00</td>\n      <td>...</td>\n      <td>5</td>\n      <td>Saturday</td>\n      <td>Morning</td>\n      <td>7</td>\n      <td>7</td>\n      <td>2023</td>\n      <td>11:00:00</td>\n      <td>4</td>\n      <td>Friday</td>\n      <td>Morning</td>\n    </tr>\n    <tr>\n      <th>1855</th>\n      <td>126</td>\n      <td>NK</td>\n      <td>TX41901499</td>\n      <td>W</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>RODNEY SHANE JILES</td>\n      <td>2023-07-09 11:00:00</td>\n      <td>2023-07-16 11:00:00</td>\n      <td>...</td>\n      <td>6</td>\n      <td>Sunday</td>\n      <td>Morning</td>\n      <td>7</td>\n      <td>16</td>\n      <td>2023</td>\n      <td>11:00:00</td>\n      <td>6</td>\n      <td>Sunday</td>\n      <td>Morning</td>\n    </tr>\n    <tr>\n      <th>3837</th>\n      <td>115</td>\n      <td>K</td>\n      <td>TX16699939</td>\n      <td>N</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>WILLIAMS JOSEPH DENISAL</td>\n      <td>2023-07-12 01:51:00</td>\n      <td>2023-07-12 11:00:00</td>\n      <td>...</td>\n      <td>2</td>\n      <td>Wednesday</td>\n      <td>Night</td>\n      <td>7</td>\n      <td>12</td>\n      <td>2023</td>\n      <td>11:00:00</td>\n      <td>2</td>\n      <td>Wednesday</td>\n      <td>Morning</td>\n    </tr>\n    <tr>\n      <th>12</th>\n      <td>126</td>\n      <td>NK</td>\n      <td>TX41901499</td>\n      <td>N</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>RODNEY SHANE JILES</td>\n      <td>2023-07-16 11:00:00</td>\n      <td>2023-07-17 11:00:00</td>\n      <td>...</td>\n      <td>6</td>\n      <td>Sunday</td>\n      <td>Morning</td>\n      <td>7</td>\n      <td>17</td>\n      <td>2023</td>\n      <td>11:00:00</td>\n      <td>0</td>\n      <td>Monday</td>\n      <td>Morning</td>\n    </tr>\n    <tr>\n      <th>5107</th>\n      <td>126</td>\n      <td>NK</td>\n      <td>TX41901499</td>\n      <td>N</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>RODNEY SHANE JILES</td>\n      <td>2023-07-17 11:00:00</td>\n      <td>2023-07-18 11:00:00</td>\n      <td>...</td>\n      <td>0</td>\n      <td>Monday</td>\n      <td>Morning</td>\n      <td>7</td>\n      <td>18</td>\n      <td>2023</td>\n      <td>11:00:00</td>\n      <td>1</td>\n      <td>Tuesday</td>\n      <td>Morning</td>\n    </tr>\n    <tr>\n      <th>2144</th>\n      <td>106</td>\n      <td>K</td>\n      <td>TX26187232</td>\n      <td>1</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>GAINES DAMIEN XAVIER</td>\n      <td>2023-07-18 19:39:00</td>\n      <td>2023-07-18 20:39:00</td>\n      <td>...</td>\n      <td>1</td>\n      <td>Tuesday</td>\n      <td>Evening</td>\n      <td>7</td>\n      <td>18</td>\n      <td>2023</td>\n      <td>20:39:00</td>\n      <td>1</td>\n      <td>Tuesday</td>\n      <td>Evening</td>\n    </tr>\n    <tr>\n      <th>5813</th>\n      <td>105</td>\n      <td>NKH</td>\n      <td>TX19205244</td>\n      <td>N</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>RAMIREZ LAURA</td>\n      <td>2023-07-22 01:14:00</td>\n      <td>2023-07-22 11:00:00</td>\n      <td>...</td>\n      <td>5</td>\n      <td>Saturday</td>\n      <td>Night</td>\n      <td>7</td>\n      <td>22</td>\n      <td>2023</td>\n      <td>11:00:00</td>\n      <td>5</td>\n      <td>Saturday</td>\n      <td>Morning</td>\n    </tr>\n    <tr>\n      <th>6145</th>\n      <td>107</td>\n      <td>K</td>\n      <td>TX08922944</td>\n      <td>2</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>JONES JULIAN SR</td>\n      <td>2023-08-02 16:38:00</td>\n      <td>2023-08-02 18:38:00</td>\n      <td>...</td>\n      <td>2</td>\n      <td>Wednesday</td>\n      <td>Afternoon</td>\n      <td>8</td>\n      <td>2</td>\n      <td>2023</td>\n      <td>18:38:00</td>\n      <td>2</td>\n      <td>Wednesday</td>\n      <td>Evening</td>\n    </tr>\n    <tr>\n      <th>5663</th>\n      <td>122</td>\n      <td>NK</td>\n      <td>LA009138661</td>\n      <td>N</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>WRIGHT TYVON MARSE</td>\n      <td>2023-08-18 13:33:00</td>\n      <td>2023-08-19 11:00:00</td>\n      <td>...</td>\n      <td>4</td>\n      <td>Friday</td>\n      <td>Afternoon</td>\n      <td>8</td>\n      <td>19</td>\n      <td>2023</td>\n      <td>11:00:00</td>\n      <td>5</td>\n      <td>Saturday</td>\n      <td>Morning</td>\n    </tr>\n    <tr>\n      <th>5420</th>\n      <td>124</td>\n      <td>NK</td>\n      <td>TX46628099</td>\n      <td>N</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>BRANSOM BONNIE</td>\n      <td>2023-08-22 19:05:00</td>\n      <td>2023-08-24 11:00:00</td>\n      <td>...</td>\n      <td>1</td>\n      <td>Tuesday</td>\n      <td>Evening</td>\n      <td>8</td>\n      <td>24</td>\n      <td>2023</td>\n      <td>11:00:00</td>\n      <td>3</td>\n      <td>Thursday</td>\n      <td>Morning</td>\n    </tr>\n    <tr>\n      <th>979</th>\n      <td>111</td>\n      <td>K</td>\n      <td>TX34165023</td>\n      <td>N</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>NORRIS JESSICA</td>\n      <td>2023-09-27 11:00:00</td>\n      <td>2023-09-27 11:00:00</td>\n      <td>...</td>\n      <td>2</td>\n      <td>Wednesday</td>\n      <td>Morning</td>\n      <td>9</td>\n      <td>27</td>\n      <td>2023</td>\n      <td>11:00:00</td>\n      <td>2</td>\n      <td>Wednesday</td>\n      <td>Morning</td>\n    </tr>\n  </tbody>\n</table>\n<p>18 rows × 26 columns</p>\n</div>"
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "kishivam_timeperiod = price_filtered_df[\n",
    "    (price_filtered_df[\"checkin_month\"] >= 6)\n",
    "    & (price_filtered_df[\"checkin_month\"] <= 9)\n",
    "    & (price_filtered_df[\"checkin_year\"] == 2023)\n",
    "    ].sort_values(by=\"checkin_datetime\", ascending=True)\n",
    "\n",
    "kishivam_timeperiod"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "outputs": [],
   "source": [
    "# kishivam_timeperiod.to_csv(\"/Users/zm00g/Data_Science/regency_suites/data/kishivam_timeperiod_missing_price.csv\", index=False)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2023-11-14T21:16:01.219650Z",
     "start_time": "2023-11-14T21:16:01.212454Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-14T16:23:28.356739Z",
     "start_time": "2023-11-14T16:23:28.309811Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "checkin_year  checkin_month\n2023          6                7\n              7                7\n              8                3\n              9                1\nName: count, dtype: int64"
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "kishivam_timeperiod.groupby(\"checkin_year\")[\"checkin_month\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-14T16:23:28.386896Z",
     "start_time": "2023-11-14T16:23:28.319116Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "     room_num room_type customer_id stay_duration  total_price  price  tax  \\\n2960      112         K  TX41901499             N          0.0    0.0  0.0   \n4259      112         K  TX41901499             N          0.0    0.0  0.0   \n322       112         K  TX41901499             N          0.0    0.0  0.0   \n328       112         K  TX41901499             N          0.0    0.0  0.0   \n1855      126        NK  TX41901499             W          0.0    0.0  0.0   \n12        126        NK  TX41901499             N          0.0    0.0  0.0   \n5107      126        NK  TX41901499             N          0.0    0.0  0.0   \n\n           customer_name    checkin_datetime   checkout_datetime  ...  \\\n2960  RODNEY SHANE JILES 2023-06-28 11:00:00 2023-06-29 11:00:00  ...   \n4259  RODNEY SHANE JILES 2023-06-29 11:00:00 2023-06-30 11:00:00  ...   \n322   RODNEY SHANE JILES 2023-06-30 11:00:00 2023-07-01 11:00:00  ...   \n328   RODNEY SHANE JILES 2023-07-01 11:00:00 2023-07-07 11:00:00  ...   \n1855  RODNEY SHANE JILES 2023-07-09 11:00:00 2023-07-16 11:00:00  ...   \n12    RODNEY SHANE JILES 2023-07-16 11:00:00 2023-07-17 11:00:00  ...   \n5107  RODNEY SHANE JILES 2023-07-17 11:00:00 2023-07-18 11:00:00  ...   \n\n      checkin_weekday  checkin_day_name  checkin_time_period  checkout_month  \\\n2960                2         Wednesday              Morning               6   \n4259                3          Thursday              Morning               6   \n322                 4            Friday              Morning               7   \n328                 5          Saturday              Morning               7   \n1855                6            Sunday              Morning               7   \n12                  6            Sunday              Morning               7   \n5107                0            Monday              Morning               7   \n\n      checkout_day checkout_year  checkout_time checkout_weekday  \\\n2960            29          2023       11:00:00                3   \n4259            30          2023       11:00:00                4   \n322              1          2023       11:00:00                5   \n328              7          2023       11:00:00                4   \n1855            16          2023       11:00:00                6   \n12              17          2023       11:00:00                0   \n5107            18          2023       11:00:00                1   \n\n     checkout_day_name  checkout_time_period  \n2960          Thursday               Morning  \n4259            Friday               Morning  \n322           Saturday               Morning  \n328             Friday               Morning  \n1855            Sunday               Morning  \n12              Monday               Morning  \n5107           Tuesday               Morning  \n\n[7 rows x 26 columns]",
      "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>room_num</th>\n      <th>room_type</th>\n      <th>customer_id</th>\n      <th>stay_duration</th>\n      <th>total_price</th>\n      <th>price</th>\n      <th>tax</th>\n      <th>customer_name</th>\n      <th>checkin_datetime</th>\n      <th>checkout_datetime</th>\n      <th>...</th>\n      <th>checkin_weekday</th>\n      <th>checkin_day_name</th>\n      <th>checkin_time_period</th>\n      <th>checkout_month</th>\n      <th>checkout_day</th>\n      <th>checkout_year</th>\n      <th>checkout_time</th>\n      <th>checkout_weekday</th>\n      <th>checkout_day_name</th>\n      <th>checkout_time_period</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>2960</th>\n      <td>112</td>\n      <td>K</td>\n      <td>TX41901499</td>\n      <td>N</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>RODNEY SHANE JILES</td>\n      <td>2023-06-28 11:00:00</td>\n      <td>2023-06-29 11:00:00</td>\n      <td>...</td>\n      <td>2</td>\n      <td>Wednesday</td>\n      <td>Morning</td>\n      <td>6</td>\n      <td>29</td>\n      <td>2023</td>\n      <td>11:00:00</td>\n      <td>3</td>\n      <td>Thursday</td>\n      <td>Morning</td>\n    </tr>\n    <tr>\n      <th>4259</th>\n      <td>112</td>\n      <td>K</td>\n      <td>TX41901499</td>\n      <td>N</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>RODNEY SHANE JILES</td>\n      <td>2023-06-29 11:00:00</td>\n      <td>2023-06-30 11:00:00</td>\n      <td>...</td>\n      <td>3</td>\n      <td>Thursday</td>\n      <td>Morning</td>\n      <td>6</td>\n      <td>30</td>\n      <td>2023</td>\n      <td>11:00:00</td>\n      <td>4</td>\n      <td>Friday</td>\n      <td>Morning</td>\n    </tr>\n    <tr>\n      <th>322</th>\n      <td>112</td>\n      <td>K</td>\n      <td>TX41901499</td>\n      <td>N</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>RODNEY SHANE JILES</td>\n      <td>2023-06-30 11:00:00</td>\n      <td>2023-07-01 11:00:00</td>\n      <td>...</td>\n      <td>4</td>\n      <td>Friday</td>\n      <td>Morning</td>\n      <td>7</td>\n      <td>1</td>\n      <td>2023</td>\n      <td>11:00:00</td>\n      <td>5</td>\n      <td>Saturday</td>\n      <td>Morning</td>\n    </tr>\n    <tr>\n      <th>328</th>\n      <td>112</td>\n      <td>K</td>\n      <td>TX41901499</td>\n      <td>N</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>RODNEY SHANE JILES</td>\n      <td>2023-07-01 11:00:00</td>\n      <td>2023-07-07 11:00:00</td>\n      <td>...</td>\n      <td>5</td>\n      <td>Saturday</td>\n      <td>Morning</td>\n      <td>7</td>\n      <td>7</td>\n      <td>2023</td>\n      <td>11:00:00</td>\n      <td>4</td>\n      <td>Friday</td>\n      <td>Morning</td>\n    </tr>\n    <tr>\n      <th>1855</th>\n      <td>126</td>\n      <td>NK</td>\n      <td>TX41901499</td>\n      <td>W</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>RODNEY SHANE JILES</td>\n      <td>2023-07-09 11:00:00</td>\n      <td>2023-07-16 11:00:00</td>\n      <td>...</td>\n      <td>6</td>\n      <td>Sunday</td>\n      <td>Morning</td>\n      <td>7</td>\n      <td>16</td>\n      <td>2023</td>\n      <td>11:00:00</td>\n      <td>6</td>\n      <td>Sunday</td>\n      <td>Morning</td>\n    </tr>\n    <tr>\n      <th>12</th>\n      <td>126</td>\n      <td>NK</td>\n      <td>TX41901499</td>\n      <td>N</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>RODNEY SHANE JILES</td>\n      <td>2023-07-16 11:00:00</td>\n      <td>2023-07-17 11:00:00</td>\n      <td>...</td>\n      <td>6</td>\n      <td>Sunday</td>\n      <td>Morning</td>\n      <td>7</td>\n      <td>17</td>\n      <td>2023</td>\n      <td>11:00:00</td>\n      <td>0</td>\n      <td>Monday</td>\n      <td>Morning</td>\n    </tr>\n    <tr>\n      <th>5107</th>\n      <td>126</td>\n      <td>NK</td>\n      <td>TX41901499</td>\n      <td>N</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>RODNEY SHANE JILES</td>\n      <td>2023-07-17 11:00:00</td>\n      <td>2023-07-18 11:00:00</td>\n      <td>...</td>\n      <td>0</td>\n      <td>Monday</td>\n      <td>Morning</td>\n      <td>7</td>\n      <td>18</td>\n      <td>2023</td>\n      <td>11:00:00</td>\n      <td>1</td>\n      <td>Tuesday</td>\n      <td>Morning</td>\n    </tr>\n  </tbody>\n</table>\n<p>7 rows × 26 columns</p>\n</div>"
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "known_zeroes = kishivam_timeperiod[\n",
    "    (kishivam_timeperiod[\"customer_name\"] == \"RODNEY SHANE JILES\")\n",
    "]\n",
    "\n",
    "known_zeroes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-14T16:23:28.388608Z",
     "start_time": "2023-11-14T16:23:28.324121Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "checkin_year  checkin_month\n2023          7                4\n              6                3\nName: count, dtype: int64"
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "known_zeroes.groupby(\"checkin_year\")[\"checkin_month\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-11-14T16:23:28.389096Z",
     "start_time": "2023-11-14T16:23:28.327533Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "18.0"
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "known_zeroes[\"days\"].sum()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "regency",
   "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.10.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}