004_join.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 結合　Join\n",
    "\n",
    "## Load Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "library(dplyr)\n",
    "source('preprocess/load_data/data_loader.R')\n",
    "load_hotel_reserve()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Inner join"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "inner_join(reserve_tb %>% filter(people_num == 1),\n",
    "           hotel_tb %>% filter(is_business),\n",
    "           by = 'hotel_id')[1:10,]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Recommend"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# small_area_nameごとにホテル数をカウント、結合キーを判定するためのテーブル\n",
    "# A table to count the number of hotels per small_area_name and to determine the join key.\n",
    "small_area_mst <-\n",
    "  hotel_tb %>%\n",
    "    group_by(big_area_name, small_area_name) %>%\n",
    "\n",
    "    # -1は、自ホテルを引いている\n",
    "    # -1 is subtracting its own hotel.\n",
    "    summarise(hotel_cnt = n() - 1) %>%\n",
    "\n",
    "    # 集約処理完了後に、グループ化を解除\n",
    "    # Remove grouping after consolidation is complete.\n",
    "    ungroup() %>%\n",
    "\n",
    "    # 20件以上であればjoin_area_idをsmall_area_nameとして設定\n",
    "    # 20件未満であればjoin_area_idをbig_area_nameとして設定\n",
    "    # If it is 20 or more, join_area_id is set as small_area_name.\n",
    "    # If it is less than 20, set join_area_id as big_area_name.\n",
    "    mutate(join_area_id\n",
    "             = if_else(hotel_cnt >= 20, small_area_name, big_area_name)) %>%\n",
    "    select(small_area_name, join_area_id)\n",
    "\n",
    "# レコメンド元になるホテルにsmall_area_mstを結合することで、join_area_idを設定\n",
    "# Set join_area_id by joining small_area_mst to the hotel that is the recommendation source.\n",
    "base_hotel_mst <-\n",
    "  inner_join(hotel_tb, small_area_mst, by = 'small_area_name') %>%\n",
    "    select(hotel_id, join_area_id)\n",
    "\n",
    "# 必要に応じて、メモリを解放(必須ではないがメモリ量に余裕のないときに利用)\n",
    "# Memory release\n",
    "rm(small_area_mst)\n",
    "\n",
    "# recommend_hotel_mstはレコメンド候補のためのテーブル\n",
    "# recommend_hotel_mst is a table for recommendation candidates.\n",
    "recommend_hotel_mst <-\n",
    "  bind_rows(\n",
    "    # join_area_idをbig_area_nameとしたレコメンド候補マスタ\n",
    "    # It is a recommendation candidate master whose join_area_id is big_area_name.\n",
    "    hotel_tb %>%\n",
    "      rename(rec_hotel_id = hotel_id, join_area_id = big_area_name) %>%\n",
    "      select(join_area_id, rec_hotel_id),\n",
    "\n",
    "    # join_area_idをsmall_area_nameとしたレコメンド候補マスタ\n",
    "    # It is a recommendation candidate master whose join_area_id is small_area_name.\n",
    "    hotel_tb %>%\n",
    "      rename(rec_hotel_id = hotel_id, join_area_id = small_area_name) %>%\n",
    "      select(join_area_id, rec_hotel_id)\n",
    "  )\n",
    "\n",
    "# base_hotel_mstとrecommend_hotel_mstを結合し、レコメンド候補の情報を付与\n",
    "# Join base_hotel_mst and recommend_hotel_mst, and give information on recommendation candidates.\n",
    "result <- inner_join(base_hotel_mst, recommend_hotel_mst, by = 'join_area_id') %>%\n",
    "\n",
    "  # レコメンド候補から自分を除く\n",
    "  # Remove ownself from recommendation candidates.\n",
    "  filter(hotel_id != rec_hotel_id)\n",
    "\n",
    "result[1:10,]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Combining past data\n",
    "\n",
    "Using [lag()](https://www.rdocumentation.org/packages/dplyr/versions/0.7.8/topics/lead-lag)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "result <- reserve_tb %>%\n",
    "\n",
    "  group_by(customer_id) %>%\n",
    "\n",
    "  mutate(before_price = lag(total_price, n = 1,\n",
    "                          order_by = reserve_datetime, default = NA))\n",
    "\n",
    "result[1:10,]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Combining past datas sum\n",
    "\n",
    "Using [roll_sum()](https://www.rdocumentation.org/packages/RcppRoll/versions/0.3.0/topics/RcppRoll-exports)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "library(RcppRoll)\n",
    "\n",
    "result <- reserve_tb %>%\n",
    "\n",
    "  group_by(customer_id) %>%\n",
    "\n",
    "  arrange(reserve_datetime) %>%\n",
    "\n",
    "  mutate(price_sum = roll_sum(total_price, n = 3, align = 'right', fill = NA))\n",
    "\n",
    "result[1:10,]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Direct product (cross join)\n",
    "\n",
    "Using [merge()](https://www.rdocumentation.org/packages/base/versions/3.6.0/topics/merge)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "library(tidyverse)\n",
    "\n",
    "# 計算対象の年月のデータフレームを作成\n",
    "# Create data frame of year for calculation.\n",
    "month_mst <- data.frame(year_month=\n",
    "  # 2017-01-01、2017-02-01, 2017-03-01を生成し、format関数で形式を年月に変換\n",
    "  # Create '201701','201702','201703'\n",
    "  format(seq(as.Date('2017-01-01'), as.Date('2017-03-01'), by = 'months'),\n",
    "         format = '%Y%m')\n",
    ")\n",
    "\n",
    "# Cross join (customer_id x month_mst)\n",
    "customer_mst <-\n",
    "\n",
    "  merge(customer_tb %>% select(customer_id), month_mst) %>%\n",
    "\n",
    "  # mergeで指定した結合キーのデータ型がカテゴリ型になっているので、文字型に戻す\n",
    "  # merge() turns datas into categorical data. Revert to string type.\n",
    "  mutate(customer_id = as.character(customer_id),\n",
    "         year_month = as.character(year_month))\n",
    "\n",
    "# 合計利用金額を月ごとに計算\n",
    "# Calculate the total usage amount on a monthly basis.\n",
    "result <- left_join(\n",
    "  customer_mst,\n",
    "\n",
    "  # 予約テーブルに年月の結合キーを準備\n",
    "  # Add join key.\n",
    "  reserve_tb %>%\n",
    "    mutate(checkin_month = format(as.Date(checkin_date), format = '%Y%m')),\n",
    "\n",
    "  # 同じcustomer_idと年月を結合\n",
    "  # Join using customer_id and year_month.\n",
    "  by=c('customer_id' = 'customer_id', 'year_month' = 'checkin_month')\n",
    ") %>%\n",
    "\n",
    "  # customer_idと年月で集約\n",
    "  # Aggregate\n",
    "  group_by(customer_id, year_month) %>%\n",
    "\n",
    "  # 合計金額を算出\n",
    "  # sum\n",
    "  summarise(price_sum = sum(total_price)) %>%\n",
    "\n",
    "  # 予約レコードがなかった場合の合計金額を値なしから0に変換\n",
    "  # If no records, sum is NA. Change to zero.\n",
    "  replace_na(list(price_sum = 0))\n",
    "\n",
    "result[1:10,]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "R",
   "language": "R",
   "name": "ir"
  },
  "language_info": {
   "codemirror_mode": "r",
   "file_extension": ".r",
   "mimetype": "text/x-r-source",
   "name": "R",
   "pygments_lexer": "r",
   "version": "3.5.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}