02_MLmodel.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "from sklearn.model_selection import train_test_split\n",
    "from sklearn.ensemble import RandomForestRegressor, GradientBoostingRegressor\n",
    "from sklearn.metrics import mean_squared_error\n",
    "from joblib import dump"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# CSVファイルを読み込む\n",
    "df = pd.read_csv('scraped_data.csv', index_col=0)\n",
    "\n",
    "#df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import configparser\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "config = configparser.ConfigParser()\n",
    "config.read('config.ini')\n",
    "\n",
    "db_user = config['database']['user']\n",
    "db_password = config['database']['password']\n",
    "db_host = config['database']['host']\n",
    "db_name = config['database']['name']\n",
    "db_port = config['database']['port']\n",
    "\n",
    "# PostgreSQLデータベースへの接続情報\n",
    "engine = create_engine(f'postgresql://{db_user}:{db_password}@{db_host}:{db_port}/{db_name}')\n",
    "df = pd.read_sql_table('rental_properties', engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "drop_cols = ['posting_date'] # , 'removed_date', 'predict_cost'\n",
    "df.drop(drop_cols, axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 2073 entries, 0 to 2072\n",
      "Data columns (total 17 columns):\n",
      " #   Column           Non-Null Count  Dtype  \n",
      "---  ------           --------------  -----  \n",
      " 0   property_type    2073 non-null   object \n",
      " 1   building_name    2073 non-null   object \n",
      " 2   address          2073 non-null   object \n",
      " 3   access_1         2073 non-null   object \n",
      " 4   access_2         2062 non-null   object \n",
      " 5   access_3         2023 non-null   object \n",
      " 6   age              2073 non-null   object \n",
      " 7   building_floors  2073 non-null   object \n",
      " 8   room_floor       2073 non-null   object \n",
      " 9   rent             2073 non-null   float64\n",
      " 10  management_fee   2073 non-null   float64\n",
      " 11  deposit          2073 non-null   float64\n",
      " 12  gratuity         2073 non-null   float64\n",
      " 13  layout           2073 non-null   object \n",
      " 14  area             2073 non-null   float64\n",
      " 15  url              2073 non-null   object \n",
      " 16  actual_cost      2073 non-null   float64\n",
      "dtypes: float64(6), object(11)\n",
      "memory usage: 291.5+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 前処理\n",
    "1. 実質負担額を作成する\n",
    "   1. rent +  management_fee +  (deposit / 48)\n",
    "2. access_1, access_2, access_3から最寄駅ではなくて桜山駅からの時間を取得する\n",
    "2. 数値型に変換する\n",
    "   1. age, building_floors, room_floor\n",
    "   2. area"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 桜山駅からの最短時間を取得する関数\n",
    "def get_min_time_to_sakurayama(access_str):\n",
    "    if pd.isnull(access_str):\n",
    "        return np.nan\n",
    "    times = [int(s.split('歩')[1].replace('分', '')) for s in access_str.split('/') if '桜山駅' in s]\n",
    "    if times:\n",
    "        return min(times)\n",
    "    else:\n",
    "        return np.nan\n",
    "\n",
    "# access_1, access_2, access_3から桜山駅までの最短時間を計算\n",
    "df['min_time_to_sakurayama'] = df[['access_1', 'access_2', 'access_3']].apply(\n",
    "    lambda x: min(\n",
    "        filter(pd.notnull, [get_min_time_to_sakurayama(x['access_1']), get_min_time_to_sakurayama(x['access_2']), get_min_time_to_sakurayama(x['access_3'])]),\n",
    "        default=np.nan\n",
    "    ),\n",
    "    axis=1\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# '築新'または'新'を'0'年として扱うための処理を追加\n",
    "df['age'] = df['age'].str.replace('築新', '0').str.replace('新', '0').str.replace('築', '').str.replace('年', '')\n",
    "\n",
    "\n",
    "# 空の値を0に置き換え\n",
    "df['age'] = df['age'].replace('', '0')\n",
    "\n",
    "# 整数型に変換\n",
    "df['age'] = df['age'].astype(int)\n",
    "\n",
    "# 建物の階数と部屋の階についても同様に処理\n",
    "df['building_floors'] = df['building_floors'].str.extract('(\\d+)')[0].fillna('0').astype(int)\n",
    "df['room_floor'] = df['room_floor'].str.extract('(\\d+)')[0].fillna('0').astype(int)\n",
    "\n",
    "# ここでのfillna('0')は、抽出した結果がNaN（該当する数値がない場合）の場合に0を割り当てるために使用しています。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>property_type</th>\n",
       "      <th>building_name</th>\n",
       "      <th>address</th>\n",
       "      <th>access_1</th>\n",
       "      <th>access_2</th>\n",
       "      <th>access_3</th>\n",
       "      <th>age</th>\n",
       "      <th>building_floors</th>\n",
       "      <th>room_floor</th>\n",
       "      <th>rent</th>\n",
       "      <th>management_fee</th>\n",
       "      <th>deposit</th>\n",
       "      <th>gratuity</th>\n",
       "      <th>layout</th>\n",
       "      <th>area</th>\n",
       "      <th>url</th>\n",
       "      <th>actual_cost</th>\n",
       "      <th>min_time_to_sakurayama</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>賃貸アパート</td>\n",
       "      <td>地下鉄桜通線 桜山駅 2階建 築8年</td>\n",
       "      <td>愛知県名古屋市瑞穂区駒場町４</td>\n",
       "      <td>地下鉄桜通線/桜山駅 歩3分</td>\n",
       "      <td>地下鉄桜通線/瑞穂区役所駅 歩9分</td>\n",
       "      <td>地下鉄鶴舞線/御器所駅 歩18分</td>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>53500.0</td>\n",
       "      <td>4000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>53500.0</td>\n",
       "      <td>1K</td>\n",
       "      <td>20.13</td>\n",
       "      <td>https://suumo.jp/chintai/jnc_000062206827/?bc=...</td>\n",
       "      <td>58614.583333</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>賃貸アパート</td>\n",
       "      <td>地下鉄桜通線 桜山駅 2階建 築8年</td>\n",
       "      <td>愛知県名古屋市瑞穂区駒場町４</td>\n",
       "      <td>地下鉄桜通線/桜山駅 歩3分</td>\n",
       "      <td>地下鉄桜通線/瑞穂区役所駅 歩9分</td>\n",
       "      <td>地下鉄鶴舞線/御器所駅 歩18分</td>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>53500.0</td>\n",
       "      <td>4000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>53500.0</td>\n",
       "      <td>1K</td>\n",
       "      <td>20.13</td>\n",
       "      <td>https://suumo.jp/chintai/jnc_000088370773/?bc=...</td>\n",
       "      <td>58614.583333</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>賃貸マンション</td>\n",
       "      <td>ヴィルクレア桜山</td>\n",
       "      <td>愛知県名古屋市瑞穂区桜見町１</td>\n",
       "      <td>地下鉄桜通線/桜山駅 歩2分</td>\n",
       "      <td>地下鉄桜通線/瑞穂区役所駅 歩12分</td>\n",
       "      <td>地下鉄鶴舞線/御器所駅 歩17分</td>\n",
       "      <td>2</td>\n",
       "      <td>14</td>\n",
       "      <td>9</td>\n",
       "      <td>74000.0</td>\n",
       "      <td>8000.0</td>\n",
       "      <td>74000.0</td>\n",
       "      <td>74000.0</td>\n",
       "      <td>1K</td>\n",
       "      <td>29.97</td>\n",
       "      <td>https://suumo.jp/chintai/jnc_000088683433/?bc=...</td>\n",
       "      <td>85083.333333</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>賃貸マンション</td>\n",
       "      <td>ヴィルクレア桜山</td>\n",
       "      <td>愛知県名古屋市瑞穂区桜見町１</td>\n",
       "      <td>地下鉄桜通線/桜山駅 歩2分</td>\n",
       "      <td>地下鉄桜通線/瑞穂区役所駅 歩12分</td>\n",
       "      <td>地下鉄鶴舞線/御器所駅 歩17分</td>\n",
       "      <td>2</td>\n",
       "      <td>14</td>\n",
       "      <td>13</td>\n",
       "      <td>155000.0</td>\n",
       "      <td>8000.0</td>\n",
       "      <td>155000.0</td>\n",
       "      <td>155000.0</td>\n",
       "      <td>2LDK</td>\n",
       "      <td>60.00</td>\n",
       "      <td>https://suumo.jp/chintai/jnc_000088465047/?bc=...</td>\n",
       "      <td>169458.333333</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>賃貸マンション</td>\n",
       "      <td>ヴィルクレア桜山</td>\n",
       "      <td>愛知県名古屋市瑞穂区桜見町１</td>\n",
       "      <td>地下鉄桜通線/桜山駅 歩2分</td>\n",
       "      <td>地下鉄桜通線/瑞穂区役所駅 歩12分</td>\n",
       "      <td>地下鉄鶴舞線/御器所駅 歩17分</td>\n",
       "      <td>2</td>\n",
       "      <td>14</td>\n",
       "      <td>13</td>\n",
       "      <td>160000.0</td>\n",
       "      <td>8000.0</td>\n",
       "      <td>160000.0</td>\n",
       "      <td>160000.0</td>\n",
       "      <td>2LDK</td>\n",
       "      <td>60.00</td>\n",
       "      <td>https://suumo.jp/chintai/jnc_000087306700/?bc=...</td>\n",
       "      <td>174666.666667</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  property_type       building_name         address        access_1  \\\n",
       "0        賃貸アパート  地下鉄桜通線 桜山駅 2階建 築8年  愛知県名古屋市瑞穂区駒場町４  地下鉄桜通線/桜山駅 歩3分   \n",
       "1        賃貸アパート  地下鉄桜通線 桜山駅 2階建 築8年  愛知県名古屋市瑞穂区駒場町４  地下鉄桜通線/桜山駅 歩3分   \n",
       "2       賃貸マンション            ヴィルクレア桜山  愛知県名古屋市瑞穂区桜見町１  地下鉄桜通線/桜山駅 歩2分   \n",
       "3       賃貸マンション            ヴィルクレア桜山  愛知県名古屋市瑞穂区桜見町１  地下鉄桜通線/桜山駅 歩2分   \n",
       "4       賃貸マンション            ヴィルクレア桜山  愛知県名古屋市瑞穂区桜見町１  地下鉄桜通線/桜山駅 歩2分   \n",
       "\n",
       "             access_2          access_3  age  building_floors  room_floor  \\\n",
       "0   地下鉄桜通線/瑞穂区役所駅 歩9分  地下鉄鶴舞線/御器所駅 歩18分    8                2           1   \n",
       "1   地下鉄桜通線/瑞穂区役所駅 歩9分  地下鉄鶴舞線/御器所駅 歩18分    8                2           1   \n",
       "2  地下鉄桜通線/瑞穂区役所駅 歩12分  地下鉄鶴舞線/御器所駅 歩17分    2               14           9   \n",
       "3  地下鉄桜通線/瑞穂区役所駅 歩12分  地下鉄鶴舞線/御器所駅 歩17分    2               14          13   \n",
       "4  地下鉄桜通線/瑞穂区役所駅 歩12分  地下鉄鶴舞線/御器所駅 歩17分    2               14          13   \n",
       "\n",
       "       rent  management_fee   deposit  gratuity layout   area  \\\n",
       "0   53500.0          4000.0       0.0   53500.0     1K  20.13   \n",
       "1   53500.0          4000.0       0.0   53500.0     1K  20.13   \n",
       "2   74000.0          8000.0   74000.0   74000.0     1K  29.97   \n",
       "3  155000.0          8000.0  155000.0  155000.0   2LDK  60.00   \n",
       "4  160000.0          8000.0  160000.0  160000.0   2LDK  60.00   \n",
       "\n",
       "                                                 url    actual_cost  \\\n",
       "0  https://suumo.jp/chintai/jnc_000062206827/?bc=...   58614.583333   \n",
       "1  https://suumo.jp/chintai/jnc_000088370773/?bc=...   58614.583333   \n",
       "2  https://suumo.jp/chintai/jnc_000088683433/?bc=...   85083.333333   \n",
       "3  https://suumo.jp/chintai/jnc_000088465047/?bc=...  169458.333333   \n",
       "4  https://suumo.jp/chintai/jnc_000087306700/?bc=...  174666.666667   \n",
       "\n",
       "   min_time_to_sakurayama  \n",
       "0                       3  \n",
       "1                       3  \n",
       "2                       2  \n",
       "3                       2  \n",
       "4                       2  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 2073 entries, 0 to 2072\n",
      "Data columns (total 18 columns):\n",
      " #   Column                  Non-Null Count  Dtype  \n",
      "---  ------                  --------------  -----  \n",
      " 0   property_type           2073 non-null   object \n",
      " 1   building_name           2073 non-null   object \n",
      " 2   address                 2073 non-null   object \n",
      " 3   access_1                2073 non-null   object \n",
      " 4   access_2                2062 non-null   object \n",
      " 5   access_3                2023 non-null   object \n",
      " 6   age                     2073 non-null   int64  \n",
      " 7   building_floors         2073 non-null   int64  \n",
      " 8   room_floor              2073 non-null   int64  \n",
      " 9   rent                    2073 non-null   float64\n",
      " 10  management_fee          2073 non-null   float64\n",
      " 11  deposit                 2073 non-null   float64\n",
      " 12  gratuity                2073 non-null   float64\n",
      " 13  layout                  2073 non-null   object \n",
      " 14  area                    2073 non-null   float64\n",
      " 15  url                     2073 non-null   object \n",
      " 16  actual_cost             2073 non-null   float64\n",
      " 17  min_time_to_sakurayama  2073 non-null   int64  \n",
      "dtypes: float64(6), int64(4), object(8)\n",
      "memory usage: 307.7+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## object型の処理\n",
    "- エンコーディングする\n",
    "  - property_type\n",
    "  - address\n",
    "  - layout\n",
    "- dropする\n",
    "  - building_name\n",
    "  - access_1\n",
    "  - access_2\n",
    "  - access_3\n",
    "  - url\n",
    "  - posting_date\n",
    "  - predictions_rf\n",
    "  - predictions_gb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# エンコーディングするカラム\n",
    "categorical_cols = ['property_type', 'address', 'layout']\n",
    "\n",
    "# One-hotエンコーディング\n",
    "df = pd.get_dummies(df, columns=categorical_cols)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 不要なカラムを削除\n",
    "drop_cols = ['building_name', 'access_1', 'access_2', 'access_3', 'url']\n",
    "df.drop(drop_cols, axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.columns = df.columns.astype(str)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# モデルの訓練"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 特徴量とターゲット変数に分割\n",
    "X = df #.drop('actual_cost', axis=1)  # actual_cost以外のすべてのカラムを特徴量として使用\n",
    "y = df['actual_cost']  # actual_costをターゲット変数として使用\n",
    "\n",
    "# データセットの分割（例：訓練データ80%、テストデータ20%）\n",
    "X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Random Forest: Test RMSE: 1237.2342\n",
      "Gradient Boosting: Test RMSE: 400.8682\n"
     ]
    }
   ],
   "source": [
    "# モデルの訓練とテスト\n",
    "models = {\n",
    "    \"Random Forest\": RandomForestRegressor(random_state=42),\n",
    "    \"Gradient Boosting\": GradientBoostingRegressor(random_state=42)\n",
    "}\n",
    "\n",
    "for name, model in models.items():\n",
    "    # モデルの訓練\n",
    "    model.fit(X_train, y_train)\n",
    "    \n",
    "    # テストデータで予測\n",
    "    predictions = model.predict(X_test)\n",
    "    \n",
    "    # RMSEの計算\n",
    "    rmse_score = np.sqrt(mean_squared_error(y_test, predictions))\n",
    "    print(f\"{name}: Test RMSE: {rmse_score:.4f}\")\n",
    "    \n",
    "    # モデルの保存\n",
    "    dump(model, f'{name.replace(\" \", \"_\").lower()}_model.joblib')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}