data load & transform.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "34c5ab64",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "0d00fafb",
   "metadata": {},
   "outputs": [],
   "source": [
    "import gensim"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "c4456b65",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_read = pd.read_excel('source\\homestay master.xlsx', header=0)\n",
    "df_read = df_read[~df_read[\"ลักษณะที่พัก\"].isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "dd52f997",
   "metadata": {},
   "outputs": [],
   "source": [
    "fix_features = df_read[[\"รหัส\",'อาหารเช้า', 'ทำอาหารเองได้ภายใน', 'ทำอาหารเองได้ภายนอก', 'กางเต๊นท์',\n",
    "       'พืนที่สวน', 'สัตว์เลี้ยงในบริเวณ', 'แหล่งน้ำ', 'สระว่ายน้ำ',\n",
    "       'ที่จอดรถ','WiFi ในห้อง']]\n",
    "fix_features.set_index([\"รหัส\"],inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "fbf7eaf1",
   "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>อาหารเช้า</th>\n",
       "      <th>ทำอาหารเองได้ภายใน</th>\n",
       "      <th>ทำอาหารเองได้ภายนอก</th>\n",
       "      <th>กางเต๊นท์</th>\n",
       "      <th>พืนที่สวน</th>\n",
       "      <th>สัตว์เลี้ยงในบริเวณ</th>\n",
       "      <th>แหล่งน้ำ</th>\n",
       "      <th>สระว่ายน้ำ</th>\n",
       "      <th>ที่จอดรถ</th>\n",
       "      <th>WiFi ในห้อง</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>รหัส</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>PKG0001</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PKG0002</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PKG0004</th>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         อาหารเช้า  ทำอาหารเองได้ภายใน  ทำอาหารเองได้ภายนอก  กางเต๊นท์  \\\n",
       "รหัส                                                                     \n",
       "PKG0001        1.0                 0.0                  1.0        0.0   \n",
       "PKG0002        1.0                 1.0                  1.0        0.0   \n",
       "PKG0004        0.0                 1.0                  1.0        0.0   \n",
       "\n",
       "         พืนที่สวน  สัตว์เลี้ยงในบริเวณ  แหล่งน้ำ  สระว่ายน้ำ  ที่จอดรถ  \\\n",
       "รหัส                                                                      \n",
       "PKG0001        1.0                  1.0       1.0         0.0       1.0   \n",
       "PKG0002        1.0                  0.0       1.0         1.0       1.0   \n",
       "PKG0004        1.0                  0.0       0.0         0.0       1.0   \n",
       "\n",
       "         WiFi ในห้อง  \n",
       "รหัส                  \n",
       "PKG0001          1.0  \n",
       "PKG0002          1.0  \n",
       "PKG0004          0.0  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fix_features.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "39489404",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_filter = df_read.copy()\n",
    "df_filter.set_index([\"รหัส\"],inplace=True)\n",
    "df_filter = df_filter.fillna('n/a')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "a5891098",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_filter['Platform ขาย'] = df_filter['Platform ขาย'].apply(lambda x: x.replace(' ','').split(\",\"))\n",
    "df_filter['Platform Review'] = df_filter['Platform Review'].apply(lambda x: x.replace(' ','').split(\",\"))\n",
    "df_filter['ลักษณะที่พัก'] = df_filter['ลักษณะที่พัก'].apply(lambda x: x.replace(' ','').split(\",\"))\n",
    "df_filter['สไตล์'] = df_filter['สไตล์'].apply(lambda x: x.replace(' ','').split(\",\"))\n",
    "df_filter['จุดขายพิเศษ'] = df_filter['จุดขายพิเศษ'].apply(lambda x: x.replace(' ','').split(\",\"))\n",
    "df_filter['อรรถประโยชน์'] = df_filter['อรรถประโยชน์'].apply(lambda x: x.replace(' ','').split(\",\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "3cb5a43c",
   "metadata": {
    "scrolled": false
   },
   "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>ชื่ออสังหาริมทรัพย์ตัวอย่าง</th>\n",
       "      <th>Lat-Lon</th>\n",
       "      <th>ตำบล</th>\n",
       "      <th>โซน</th>\n",
       "      <th>ประเภท</th>\n",
       "      <th>Rating (Google Map)</th>\n",
       "      <th>จำนวนคน Rate (GM)</th>\n",
       "      <th>Platform ขาย</th>\n",
       "      <th>Platform Review</th>\n",
       "      <th>ราคาเริ่มต้น/คืน</th>\n",
       "      <th>...</th>\n",
       "      <th>กางเต๊นท์</th>\n",
       "      <th>พืนที่สวน</th>\n",
       "      <th>สัตว์เลี้ยงในบริเวณ</th>\n",
       "      <th>แหล่งน้ำ</th>\n",
       "      <th>สระว่ายน้ำ</th>\n",
       "      <th>ที่จอดรถ</th>\n",
       "      <th>อรรถประโยชน์</th>\n",
       "      <th>WiFi ในห้อง</th>\n",
       "      <th>จุดขายพิเศษ</th>\n",
       "      <th>Unnamed: 28</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>รหัส</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>PKG0001</th>\n",
       "      <td>Angel Valley</td>\n",
       "      <td>14.469428283417516, 101.57710441380485</td>\n",
       "      <td>โป่งตาลอง</td>\n",
       "      <td>เขาใหญ่ตะวันออก</td>\n",
       "      <td>Resort/Hotel</td>\n",
       "      <td>4.9</td>\n",
       "      <td>18.0</td>\n",
       "      <td>[Airbnb]</td>\n",
       "      <td>[ใกล้ไกล]</td>\n",
       "      <td>15000.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>[ATV, จักรยาน, BBQ, Clubhouse, คาราโอเกะ, พักร...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>[n/a]</td>\n",
       "      <td>n/a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PKG0002</th>\n",
       "      <td>Antani Home Khaoyai</td>\n",
       "      <td>14.47240737839758, 101.57533605260043</td>\n",
       "      <td>โป่งตาลอง</td>\n",
       "      <td>เขาใหญ่ตะวันออก</td>\n",
       "      <td>Resort/Hotel</td>\n",
       "      <td>4.6</td>\n",
       "      <td>26.0</td>\n",
       "      <td>[Agoda, Booking, Tripadviser, Facebook]</td>\n",
       "      <td>[ชิลไปไหน, Familypool]</td>\n",
       "      <td>2000.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>[พูลวิลล่า, Clubhouse, คาราโอเกะ, BBQ, หมูกระทะ]</td>\n",
       "      <td>1.0</td>\n",
       "      <td>[n/a]</td>\n",
       "      <td>n/a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PKG0004</th>\n",
       "      <td>Barn House Pong Ta Long</td>\n",
       "      <td>14.471865480259176, 101.58365880642997</td>\n",
       "      <td>โป่งตาลอง</td>\n",
       "      <td>เขาใหญ่ตะวันออก</td>\n",
       "      <td>Resort/Hotel</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>[Agoda, Booking, Bedandbreakfast]</td>\n",
       "      <td>[n/a]</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>[ห้ามจัดปาร์ตี้, BBQ, หมูกระทะ]</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[n/a]</td>\n",
       "      <td>n/a</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        ชื่ออสังหาริมทรัพย์ตัวอย่าง                                 Lat-Lon  \\\n",
       "รหัส                                                                          \n",
       "PKG0001                Angel Valley  14.469428283417516, 101.57710441380485   \n",
       "PKG0002         Antani Home Khaoyai   14.47240737839758, 101.57533605260043   \n",
       "PKG0004     Barn House Pong Ta Long  14.471865480259176, 101.58365880642997   \n",
       "\n",
       "              ตำบล              โซน        ประเภท Rating (Google Map)  \\\n",
       "รหัส                                                                    \n",
       "PKG0001  โป่งตาลอง  เขาใหญ่ตะวันออก  Resort/Hotel                 4.9   \n",
       "PKG0002  โป่งตาลอง  เขาใหญ่ตะวันออก  Resort/Hotel                 4.6   \n",
       "PKG0004  โป่งตาลอง  เขาใหญ่ตะวันออก  Resort/Hotel                 5.0   \n",
       "\n",
       "        จำนวนคน Rate (GM)                             Platform ขาย  \\\n",
       "รหัส                                                                 \n",
       "PKG0001              18.0                                 [Airbnb]   \n",
       "PKG0002              26.0  [Agoda, Booking, Tripadviser, Facebook]   \n",
       "PKG0004               1.0        [Agoda, Booking, Bedandbreakfast]   \n",
       "\n",
       "                Platform Review ราคาเริ่มต้น/คืน  ... กางเต๊นท์ พืนที่สวน  \\\n",
       "รหัส                                              ...                       \n",
       "PKG0001               [ใกล้ไกล]          15000.0  ...       0.0       1.0   \n",
       "PKG0002  [ชิลไปไหน, Familypool]           2000.0  ...       0.0       1.0   \n",
       "PKG0004                   [n/a]          10000.0  ...       0.0       1.0   \n",
       "\n",
       "        สัตว์เลี้ยงในบริเวณ แหล่งน้ำ สระว่ายน้ำ  ที่จอดรถ  \\\n",
       "รหัส                                                        \n",
       "PKG0001                 1.0      1.0        0.0       1.0   \n",
       "PKG0002                 0.0      1.0        1.0       1.0   \n",
       "PKG0004                 0.0      0.0        0.0       1.0   \n",
       "\n",
       "                                              อรรถประโยชน์  WiFi ในห้อง  \\\n",
       "รหัส                                                                      \n",
       "PKG0001  [ATV, จักรยาน, BBQ, Clubhouse, คาราโอเกะ, พักร...          1.0   \n",
       "PKG0002   [พูลวิลล่า, Clubhouse, คาราโอเกะ, BBQ, หมูกระทะ]          1.0   \n",
       "PKG0004                    [ห้ามจัดปาร์ตี้, BBQ, หมูกระทะ]          0.0   \n",
       "\n",
       "         จุดขายพิเศษ  Unnamed: 28  \n",
       "รหัส                               \n",
       "PKG0001        [n/a]          n/a  \n",
       "PKG0002        [n/a]          n/a  \n",
       "PKG0004        [n/a]          n/a  \n",
       "\n",
       "[3 rows x 28 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_filter.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "76ce67dc",
   "metadata": {},
   "outputs": [],
   "source": [
    "def countElem(df_in, fieldName):\n",
    "    dictionary = gensim.corpora.Dictionary(df_in[fieldName])\n",
    "    df_in[fieldName] = df_in[fieldName].map(dictionary.doc2bow)\n",
    "    df_in[fieldName] = df_in[fieldName].apply(lambda x:[(dictionary[id_], frequence) for id_, frequence in x])\n",
    "    return df_in"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "cfd9a598",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_filter = countElem(df_filter, 'Platform ขาย')\n",
    "df_filter = countElem(df_filter, 'Platform Review')\n",
    "df_filter = countElem(df_filter, 'ลักษณะที่พัก')\n",
    "df_filter = countElem(df_filter, 'สไตล์')\n",
    "df_filter = countElem(df_filter, 'จุดขายพิเศษ')\n",
    "df_filter = countElem(df_filter, 'อรรถประโยชน์')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "cb9fd781",
   "metadata": {
    "scrolled": false
   },
   "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>ชื่ออสังหาริมทรัพย์ตัวอย่าง</th>\n",
       "      <th>Lat-Lon</th>\n",
       "      <th>ตำบล</th>\n",
       "      <th>โซน</th>\n",
       "      <th>ประเภท</th>\n",
       "      <th>Rating (Google Map)</th>\n",
       "      <th>จำนวนคน Rate (GM)</th>\n",
       "      <th>Platform ขาย</th>\n",
       "      <th>Platform Review</th>\n",
       "      <th>ราคาเริ่มต้น/คืน</th>\n",
       "      <th>...</th>\n",
       "      <th>กางเต๊นท์</th>\n",
       "      <th>พืนที่สวน</th>\n",
       "      <th>สัตว์เลี้ยงในบริเวณ</th>\n",
       "      <th>แหล่งน้ำ</th>\n",
       "      <th>สระว่ายน้ำ</th>\n",
       "      <th>ที่จอดรถ</th>\n",
       "      <th>อรรถประโยชน์</th>\n",
       "      <th>WiFi ในห้อง</th>\n",
       "      <th>จุดขายพิเศษ</th>\n",
       "      <th>Unnamed: 28</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>รหัส</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>PKG0001</th>\n",
       "      <td>Angel Valley</td>\n",
       "      <td>14.469428283417516, 101.57710441380485</td>\n",
       "      <td>โป่งตาลอง</td>\n",
       "      <td>เขาใหญ่ตะวันออก</td>\n",
       "      <td>Resort/Hotel</td>\n",
       "      <td>4.9</td>\n",
       "      <td>18.0</td>\n",
       "      <td>[(Airbnb, 1)]</td>\n",
       "      <td>[(ใกล้ไกล, 1)]</td>\n",
       "      <td>15000.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>[(ATV, 1), (BBQ, 1), (Clubhouse, 1), (คาราโอเก...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>[(n/a, 1)]</td>\n",
       "      <td>n/a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PKG0002</th>\n",
       "      <td>Antani Home Khaoyai</td>\n",
       "      <td>14.47240737839758, 101.57533605260043</td>\n",
       "      <td>โป่งตาลอง</td>\n",
       "      <td>เขาใหญ่ตะวันออก</td>\n",
       "      <td>Resort/Hotel</td>\n",
       "      <td>4.6</td>\n",
       "      <td>26.0</td>\n",
       "      <td>[(Agoda, 1), (Booking, 1), (Facebook, 1), (Tri...</td>\n",
       "      <td>[(Familypool, 1), (ชิลไปไหน, 1)]</td>\n",
       "      <td>2000.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>[(BBQ, 1), (Clubhouse, 1), (คาราโอเกะ, 1), (พู...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>[(n/a, 1)]</td>\n",
       "      <td>n/a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PKG0004</th>\n",
       "      <td>Barn House Pong Ta Long</td>\n",
       "      <td>14.471865480259176, 101.58365880642997</td>\n",
       "      <td>โป่งตาลอง</td>\n",
       "      <td>เขาใหญ่ตะวันออก</td>\n",
       "      <td>Resort/Hotel</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>[(Agoda, 1), (Booking, 1), (Bedandbreakfast, 1)]</td>\n",
       "      <td>[(n/a, 1)]</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>[(BBQ, 1), (หมูกระทะ, 1), (ห้ามจัดปาร์ตี้, 1)]</td>\n",
       "      <td>0.0</td>\n",
       "      <td>[(n/a, 1)]</td>\n",
       "      <td>n/a</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        ชื่ออสังหาริมทรัพย์ตัวอย่าง                                 Lat-Lon  \\\n",
       "รหัส                                                                          \n",
       "PKG0001                Angel Valley  14.469428283417516, 101.57710441380485   \n",
       "PKG0002         Antani Home Khaoyai   14.47240737839758, 101.57533605260043   \n",
       "PKG0004     Barn House Pong Ta Long  14.471865480259176, 101.58365880642997   \n",
       "\n",
       "              ตำบล              โซน        ประเภท Rating (Google Map)  \\\n",
       "รหัส                                                                    \n",
       "PKG0001  โป่งตาลอง  เขาใหญ่ตะวันออก  Resort/Hotel                 4.9   \n",
       "PKG0002  โป่งตาลอง  เขาใหญ่ตะวันออก  Resort/Hotel                 4.6   \n",
       "PKG0004  โป่งตาลอง  เขาใหญ่ตะวันออก  Resort/Hotel                 5.0   \n",
       "\n",
       "        จำนวนคน Rate (GM)                                       Platform ขาย  \\\n",
       "รหัส                                                                           \n",
       "PKG0001              18.0                                      [(Airbnb, 1)]   \n",
       "PKG0002              26.0  [(Agoda, 1), (Booking, 1), (Facebook, 1), (Tri...   \n",
       "PKG0004               1.0   [(Agoda, 1), (Booking, 1), (Bedandbreakfast, 1)]   \n",
       "\n",
       "                          Platform Review ราคาเริ่มต้น/คืน  ... กางเต๊นท์  \\\n",
       "รหัส                                                        ...             \n",
       "PKG0001                    [(ใกล้ไกล, 1)]          15000.0  ...       0.0   \n",
       "PKG0002  [(Familypool, 1), (ชิลไปไหน, 1)]           2000.0  ...       0.0   \n",
       "PKG0004                        [(n/a, 1)]          10000.0  ...       0.0   \n",
       "\n",
       "        พืนที่สวน สัตว์เลี้ยงในบริเวณ แหล่งน้ำ สระว่ายน้ำ  ที่จอดรถ  \\\n",
       "รหัส                                                                  \n",
       "PKG0001       1.0                 1.0      1.0        0.0       1.0   \n",
       "PKG0002       1.0                 0.0      1.0        1.0       1.0   \n",
       "PKG0004       1.0                 0.0      0.0        0.0       1.0   \n",
       "\n",
       "                                              อรรถประโยชน์  WiFi ในห้อง  \\\n",
       "รหัส                                                                      \n",
       "PKG0001  [(ATV, 1), (BBQ, 1), (Clubhouse, 1), (คาราโอเก...          1.0   \n",
       "PKG0002  [(BBQ, 1), (Clubhouse, 1), (คาราโอเกะ, 1), (พู...          1.0   \n",
       "PKG0004     [(BBQ, 1), (หมูกระทะ, 1), (ห้ามจัดปาร์ตี้, 1)]          0.0   \n",
       "\n",
       "         จุดขายพิเศษ  Unnamed: 28  \n",
       "รหัส                               \n",
       "PKG0001   [(n/a, 1)]          n/a  \n",
       "PKG0002   [(n/a, 1)]          n/a  \n",
       "PKG0004   [(n/a, 1)]          n/a  \n",
       "\n",
       "[3 rows x 28 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_filter.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "407caa76",
   "metadata": {},
   "outputs": [],
   "source": [
    "def unpivotCount(df_in, fieldName):\n",
    "    df_out = pd.concat([pd.DataFrame(s,columns=['w','c']).set_index('w') for s in df_in[fieldName]], \\\n",
    "          axis=1, sort=False).fillna(0).T.set_index(df_in.index)\n",
    "    #df_out.reset_index(inplace=True)\n",
    "    return df_out"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b48b658d",
   "metadata": {},
   "source": [
    "Generate ข้อมูลตาม Domain ที่สนใจ เตรียมเอาไปทำ Conceptual Map แยก"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "2dd4951b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_platform = unpivotCount(df_filter, 'Platform ขาย')\n",
    "df_platform_review = unpivotCount(df_filter, 'Platform Review')\n",
    "df_acc = unpivotCount(df_filter, 'ลักษณะที่พัก')\n",
    "df_style = unpivotCount(df_filter, 'สไตล์')\n",
    "df_highlight = unpivotCount(df_filter, 'จุดขายพิเศษ')\n",
    "df_util = unpivotCount(df_filter, 'อรรถประโยชน์')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5687a86b",
   "metadata": {},
   "source": [
    "รวมข้อมูล กรณีทำ MDS รวบทุก Domain"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "09b1330e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_all = pd.concat([df_platform, df_platform_review,df_acc,df_style,df_highlight,df_util,fix_features]). \\\n",
    "            groupby(['รหัส']).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "30ed665b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_all = df_all.drop('n/a', axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "244cfa7a",
   "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>PKG0001</th>\n",
       "      <th>PKG0002</th>\n",
       "      <th>PKG0004</th>\n",
       "      <th>PKG0006</th>\n",
       "      <th>PKG0007</th>\n",
       "      <th>PKG0008</th>\n",
       "      <th>PKG0009</th>\n",
       "      <th>PKG0010</th>\n",
       "      <th>PKG0011</th>\n",
       "      <th>PKG0012</th>\n",
       "      <th>...</th>\n",
       "      <th>PKG0197</th>\n",
       "      <th>PKG0200</th>\n",
       "      <th>PKG0202</th>\n",
       "      <th>PKG0203</th>\n",
       "      <th>PKG0204</th>\n",
       "      <th>PKG0205</th>\n",
       "      <th>PKG0207</th>\n",
       "      <th>PKG0209</th>\n",
       "      <th>PKG0300</th>\n",
       "      <th>PKG0550</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Airbnb</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Agoda</th>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Booking</th>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 122 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "รหัส     PKG0001  PKG0002  PKG0004  PKG0006  PKG0007  PKG0008  PKG0009  \\\n",
       "Airbnb       1.0      0.0      0.0      0.0      0.0      0.0      0.0   \n",
       "Agoda        0.0      1.0      1.0      0.0      0.0      1.0      1.0   \n",
       "Booking      0.0      1.0      1.0      0.0      0.0      0.0      0.0   \n",
       "\n",
       "รหัส     PKG0010  PKG0011  PKG0012  ...  PKG0197  PKG0200  PKG0202  PKG0203  \\\n",
       "Airbnb       0.0      0.0      0.0  ...      0.0      0.0      0.0      0.0   \n",
       "Agoda        1.0      1.0      1.0  ...      0.0      1.0      0.0      0.0   \n",
       "Booking      1.0      1.0      0.0  ...      0.0      0.0      0.0      0.0   \n",
       "\n",
       "รหัส     PKG0204  PKG0205  PKG0207  PKG0209  PKG0300  PKG0550  \n",
       "Airbnb       0.0      0.0      0.0      0.0      0.0      0.0  \n",
       "Agoda        1.0      1.0      1.0      1.0      0.0      1.0  \n",
       "Booking      1.0      0.0      0.0      0.0      0.0      1.0  \n",
       "\n",
       "[3 rows x 122 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_all.T.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "7688cb25",
   "metadata": {},
   "outputs": [],
   "source": [
    "# ใช้ PCA รวบองค์ประกอบเพื่อ EDA ต่อไป\n",
    "df_all.T.to_excel('output\\datarawmatrix.xlsx', index=True)\n",
    "df_all.to_excel('output\\datarawmatrix_ori.xlsx', index=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6d42111c",
   "metadata": {},
   "source": [
    "Mapping similarity"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "28390cbf",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_map = df_all.T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "4d64f651",
   "metadata": {},
   "outputs": [],
   "source": [
    "item_item_matrix = pd.DataFrame(index=df_map.columns,columns=df_map.columns).fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "40e61609",
   "metadata": {},
   "outputs": [],
   "source": [
    "def jaccard_index(df,i,j):\n",
    "    a = pd.DataFrame(df.iloc[:,i])\n",
    "    b = pd.DataFrame(df.iloc[:,j])\n",
    "    slice_df = pd.concat([a,b], axis=1, join=\"inner\")\n",
    "    slice_df.columns = ['A','B']\n",
    "    a_in_b = slice_df[(slice_df.A>0)&(slice_df.B>0)]\n",
    "    a_occur = slice_df[(slice_df.A>0)]\n",
    "    b_occur = slice_df[(slice_df.B>0)]\n",
    "    jacc_index = a_in_b.shape[0]/(a_occur.shape[0]+b_occur.shape[0]-a_in_b.shape[0])\n",
    "    return jacc_index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "7d6124ff",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Co-occurrence Similarity\n",
    "for i in range(0,len(item_item_matrix.columns)):\n",
    "    for j in range(0,len(item_item_matrix.columns)):\n",
    "        item_item_matrix.iloc[i,j] = jaccard_index(df_map,i,j)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "770538ef",
   "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>PKG0001</th>\n",
       "      <th>PKG0002</th>\n",
       "      <th>PKG0004</th>\n",
       "      <th>PKG0006</th>\n",
       "      <th>PKG0007</th>\n",
       "      <th>PKG0008</th>\n",
       "      <th>PKG0009</th>\n",
       "      <th>PKG0010</th>\n",
       "      <th>PKG0011</th>\n",
       "      <th>PKG0012</th>\n",
       "      <th>...</th>\n",
       "      <th>PKG0197</th>\n",
       "      <th>PKG0200</th>\n",
       "      <th>PKG0202</th>\n",
       "      <th>PKG0203</th>\n",
       "      <th>PKG0204</th>\n",
       "      <th>PKG0205</th>\n",
       "      <th>PKG0207</th>\n",
       "      <th>PKG0209</th>\n",
       "      <th>PKG0300</th>\n",
       "      <th>PKG0550</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>รหัส</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>PKG0001</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.250000</td>\n",
       "      <td>0.208333</td>\n",
       "      <td>0.300000</td>\n",
       "      <td>0.259259</td>\n",
       "      <td>0.222222</td>\n",
       "      <td>0.181818</td>\n",
       "      <td>0.233333</td>\n",
       "      <td>0.225806</td>\n",
       "      <td>0.285714</td>\n",
       "      <td>...</td>\n",
       "      <td>0.222222</td>\n",
       "      <td>0.212121</td>\n",
       "      <td>0.130435</td>\n",
       "      <td>0.185185</td>\n",
       "      <td>0.100000</td>\n",
       "      <td>0.307692</td>\n",
       "      <td>0.160000</td>\n",
       "      <td>0.066667</td>\n",
       "      <td>0.296296</td>\n",
       "      <td>0.142857</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PKG0002</th>\n",
       "      <td>0.250000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.290323</td>\n",
       "      <td>0.282051</td>\n",
       "      <td>0.363636</td>\n",
       "      <td>0.375000</td>\n",
       "      <td>0.351351</td>\n",
       "      <td>0.548387</td>\n",
       "      <td>0.484848</td>\n",
       "      <td>0.473684</td>\n",
       "      <td>...</td>\n",
       "      <td>0.294118</td>\n",
       "      <td>0.416667</td>\n",
       "      <td>0.156250</td>\n",
       "      <td>0.387097</td>\n",
       "      <td>0.257143</td>\n",
       "      <td>0.406250</td>\n",
       "      <td>0.290323</td>\n",
       "      <td>0.194444</td>\n",
       "      <td>0.314286</td>\n",
       "      <td>0.416667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PKG0004</th>\n",
       "      <td>0.208333</td>\n",
       "      <td>0.290323</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.214286</td>\n",
       "      <td>0.318182</td>\n",
       "      <td>0.272727</td>\n",
       "      <td>0.172414</td>\n",
       "      <td>0.333333</td>\n",
       "      <td>0.320000</td>\n",
       "      <td>0.250000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.120000</td>\n",
       "      <td>0.206897</td>\n",
       "      <td>0.050000</td>\n",
       "      <td>0.227273</td>\n",
       "      <td>0.166667</td>\n",
       "      <td>0.208333</td>\n",
       "      <td>0.200000</td>\n",
       "      <td>0.038462</td>\n",
       "      <td>0.200000</td>\n",
       "      <td>0.129032</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PKG0006</th>\n",
       "      <td>0.300000</td>\n",
       "      <td>0.282051</td>\n",
       "      <td>0.214286</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.444444</td>\n",
       "      <td>0.310345</td>\n",
       "      <td>0.375000</td>\n",
       "      <td>0.354839</td>\n",
       "      <td>0.303030</td>\n",
       "      <td>0.388889</td>\n",
       "      <td>...</td>\n",
       "      <td>0.266667</td>\n",
       "      <td>0.285714</td>\n",
       "      <td>0.192308</td>\n",
       "      <td>0.233333</td>\n",
       "      <td>0.151515</td>\n",
       "      <td>0.258065</td>\n",
       "      <td>0.214286</td>\n",
       "      <td>0.088235</td>\n",
       "      <td>0.379310</td>\n",
       "      <td>0.216216</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PKG0007</th>\n",
       "      <td>0.259259</td>\n",
       "      <td>0.363636</td>\n",
       "      <td>0.318182</td>\n",
       "      <td>0.444444</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.375000</td>\n",
       "      <td>0.300000</td>\n",
       "      <td>0.541667</td>\n",
       "      <td>0.461538</td>\n",
       "      <td>0.451613</td>\n",
       "      <td>...</td>\n",
       "      <td>0.269231</td>\n",
       "      <td>0.379310</td>\n",
       "      <td>0.181818</td>\n",
       "      <td>0.454545</td>\n",
       "      <td>0.178571</td>\n",
       "      <td>0.259259</td>\n",
       "      <td>0.318182</td>\n",
       "      <td>0.066667</td>\n",
       "      <td>0.346154</td>\n",
       "      <td>0.290323</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 122 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "รหัส      PKG0001   PKG0002   PKG0004   PKG0006   PKG0007   PKG0008   PKG0009  \\\n",
       "รหัส                                                                            \n",
       "PKG0001  1.000000  0.250000  0.208333  0.300000  0.259259  0.222222  0.181818   \n",
       "PKG0002  0.250000  1.000000  0.290323  0.282051  0.363636  0.375000  0.351351   \n",
       "PKG0004  0.208333  0.290323  1.000000  0.214286  0.318182  0.272727  0.172414   \n",
       "PKG0006  0.300000  0.282051  0.214286  1.000000  0.444444  0.310345  0.375000   \n",
       "PKG0007  0.259259  0.363636  0.318182  0.444444  1.000000  0.375000  0.300000   \n",
       "\n",
       "รหัส      PKG0010   PKG0011   PKG0012  ...   PKG0197   PKG0200   PKG0202  \\\n",
       "รหัส                                   ...                                 \n",
       "PKG0001  0.233333  0.225806  0.285714  ...  0.222222  0.212121  0.130435   \n",
       "PKG0002  0.548387  0.484848  0.473684  ...  0.294118  0.416667  0.156250   \n",
       "PKG0004  0.333333  0.320000  0.250000  ...  0.120000  0.206897  0.050000   \n",
       "PKG0006  0.354839  0.303030  0.388889  ...  0.266667  0.285714  0.192308   \n",
       "PKG0007  0.541667  0.461538  0.451613  ...  0.269231  0.379310  0.181818   \n",
       "\n",
       "รหัส      PKG0203   PKG0204   PKG0205   PKG0207   PKG0209   PKG0300   PKG0550  \n",
       "รหัส                                                                           \n",
       "PKG0001  0.185185  0.100000  0.307692  0.160000  0.066667  0.296296  0.142857  \n",
       "PKG0002  0.387097  0.257143  0.406250  0.290323  0.194444  0.314286  0.416667  \n",
       "PKG0004  0.227273  0.166667  0.208333  0.200000  0.038462  0.200000  0.129032  \n",
       "PKG0006  0.233333  0.151515  0.258065  0.214286  0.088235  0.379310  0.216216  \n",
       "PKG0007  0.454545  0.178571  0.259259  0.318182  0.066667  0.346154  0.290323  \n",
       "\n",
       "[5 rows x 122 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "item_item_matrix.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "8de0998c",
   "metadata": {},
   "outputs": [],
   "source": [
    "item_item_matrix.to_excel('output\\coocMatrix.xlsx', index=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4dc1be71",
   "metadata": {},
   "source": [
    "Mapping similarity #2 (No transpose)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "a3ab4d8e",
   "metadata": {},
   "outputs": [],
   "source": [
    "item_item_matrix = pd.DataFrame(index=df_all.columns,columns=df_all.columns).fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "52f3ca07",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Co-occurrence Similarity\n",
    "for i in range(0,len(item_item_matrix.columns)):\n",
    "    for j in range(0,len(item_item_matrix.columns)):\n",
    "        item_item_matrix.iloc[i,j] = jaccard_index(df_all,i,j)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "c67b9da2",
   "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>Airbnb</th>\n",
       "      <th>Agoda</th>\n",
       "      <th>Booking</th>\n",
       "      <th>Facebook</th>\n",
       "      <th>Tripadviser</th>\n",
       "      <th>Bedandbreakfast</th>\n",
       "      <th>www</th>\n",
       "      <th>PriceLine</th>\n",
       "      <th>Instagram</th>\n",
       "      <th>Findhotel</th>\n",
       "      <th>...</th>\n",
       "      <th>สระว่ายน้ำปรับอุณหภูมิได้</th>\n",
       "      <th>อาหารเช้า</th>\n",
       "      <th>ทำอาหารเองได้ภายใน</th>\n",
       "      <th>ทำอาหารเองได้ภายนอก</th>\n",
       "      <th>กางเต๊นท์</th>\n",
       "      <th>พืนที่สวน</th>\n",
       "      <th>สัตว์เลี้ยงในบริเวณ</th>\n",
       "      <th>แหล่งน้ำ</th>\n",
       "      <th>ที่จอดรถ</th>\n",
       "      <th>WiFi ในห้อง</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Airbnb</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.039474</td>\n",
       "      <td>0.027778</td>\n",
       "      <td>0.067797</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.035088</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.100000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.046512</td>\n",
       "      <td>0.131579</td>\n",
       "      <td>0.094737</td>\n",
       "      <td>0.029851</td>\n",
       "      <td>0.076923</td>\n",
       "      <td>0.096774</td>\n",
       "      <td>0.085106</td>\n",
       "      <td>0.082645</td>\n",
       "      <td>0.086207</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Agoda</th>\n",
       "      <td>0.039474</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.333333</td>\n",
       "      <td>0.567797</td>\n",
       "      <td>0.101449</td>\n",
       "      <td>0.014493</td>\n",
       "      <td>0.372093</td>\n",
       "      <td>0.014493</td>\n",
       "      <td>0.065789</td>\n",
       "      <td>0.014493</td>\n",
       "      <td>...</td>\n",
       "      <td>0.014493</td>\n",
       "      <td>0.552083</td>\n",
       "      <td>0.146067</td>\n",
       "      <td>0.429825</td>\n",
       "      <td>0.280000</td>\n",
       "      <td>0.567797</td>\n",
       "      <td>0.120482</td>\n",
       "      <td>0.341463</td>\n",
       "      <td>0.557377</td>\n",
       "      <td>0.516393</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Booking</th>\n",
       "      <td>0.027778</td>\n",
       "      <td>0.333333</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.211864</td>\n",
       "      <td>0.172414</td>\n",
       "      <td>0.037037</td>\n",
       "      <td>0.225806</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.083333</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.037037</td>\n",
       "      <td>0.175824</td>\n",
       "      <td>0.111111</td>\n",
       "      <td>0.152381</td>\n",
       "      <td>0.075000</td>\n",
       "      <td>0.222222</td>\n",
       "      <td>0.085106</td>\n",
       "      <td>0.172414</td>\n",
       "      <td>0.223140</td>\n",
       "      <td>0.211864</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Facebook</th>\n",
       "      <td>0.067797</td>\n",
       "      <td>0.567797</td>\n",
       "      <td>0.211864</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.060345</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.410256</td>\n",
       "      <td>0.008621</td>\n",
       "      <td>0.103448</td>\n",
       "      <td>0.008621</td>\n",
       "      <td>...</td>\n",
       "      <td>0.008621</td>\n",
       "      <td>0.647059</td>\n",
       "      <td>0.262712</td>\n",
       "      <td>0.750000</td>\n",
       "      <td>0.508621</td>\n",
       "      <td>0.901639</td>\n",
       "      <td>0.186441</td>\n",
       "      <td>0.330508</td>\n",
       "      <td>0.942623</td>\n",
       "      <td>0.917355</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tripadviser</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.101449</td>\n",
       "      <td>0.172414</td>\n",
       "      <td>0.060345</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.056604</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.142857</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.060976</td>\n",
       "      <td>0.081081</td>\n",
       "      <td>0.063158</td>\n",
       "      <td>0.064516</td>\n",
       "      <td>0.060345</td>\n",
       "      <td>0.033333</td>\n",
       "      <td>0.116279</td>\n",
       "      <td>0.057851</td>\n",
       "      <td>0.060345</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 314 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               Airbnb     Agoda   Booking  Facebook  Tripadviser  \\\n",
       "Airbnb       1.000000  0.039474  0.027778  0.067797     0.000000   \n",
       "Agoda        0.039474  1.000000  0.333333  0.567797     0.101449   \n",
       "Booking      0.027778  0.333333  1.000000  0.211864     0.172414   \n",
       "Facebook     0.067797  0.567797  0.211864  1.000000     0.060345   \n",
       "Tripadviser  0.000000  0.101449  0.172414  0.060345     1.000000   \n",
       "\n",
       "             Bedandbreakfast       www  PriceLine  Instagram  Findhotel  ...  \\\n",
       "Airbnb              0.000000  0.035088   0.000000   0.100000   0.000000  ...   \n",
       "Agoda               0.014493  0.372093   0.014493   0.065789   0.014493  ...   \n",
       "Booking             0.037037  0.225806   0.000000   0.083333   0.000000  ...   \n",
       "Facebook            0.000000  0.410256   0.008621   0.103448   0.008621  ...   \n",
       "Tripadviser         0.000000  0.056604   0.000000   0.000000   0.142857  ...   \n",
       "\n",
       "             สระว่ายน้ำปรับอุณหภูมิได้  อาหารเช้า  ทำอาหารเองได้ภายใน  \\\n",
       "Airbnb                        0.000000   0.046512            0.131579   \n",
       "Agoda                         0.014493   0.552083            0.146067   \n",
       "Booking                       0.037037   0.175824            0.111111   \n",
       "Facebook                      0.008621   0.647059            0.262712   \n",
       "Tripadviser                   0.000000   0.060976            0.081081   \n",
       "\n",
       "             ทำอาหารเองได้ภายนอก  กางเต๊นท์  พืนที่สวน  สัตว์เลี้ยงในบริเวณ  \\\n",
       "Airbnb                  0.094737   0.029851   0.076923             0.096774   \n",
       "Agoda                   0.429825   0.280000   0.567797             0.120482   \n",
       "Booking                 0.152381   0.075000   0.222222             0.085106   \n",
       "Facebook                0.750000   0.508621   0.901639             0.186441   \n",
       "Tripadviser             0.063158   0.064516   0.060345             0.033333   \n",
       "\n",
       "             แหล่งน้ำ  ที่จอดรถ  WiFi ในห้อง  \n",
       "Airbnb       0.085106  0.082645     0.086207  \n",
       "Agoda        0.341463  0.557377     0.516393  \n",
       "Booking      0.172414  0.223140     0.211864  \n",
       "Facebook     0.330508  0.942623     0.917355  \n",
       "Tripadviser  0.116279  0.057851     0.060345  \n",
       "\n",
       "[5 rows x 314 columns]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "item_item_matrix.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "9496e7ae",
   "metadata": {},
   "outputs": [],
   "source": [
    "# ใช้แสดง SNA\n",
    "item_item_matrix.to_excel('output\\coocMatrix1.xlsx', index=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9c076080",
   "metadata": {},
   "source": [
    "Mapping dissimilarity"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "af5ef86a",
   "metadata": {},
   "outputs": [],
   "source": [
    "item_item_matrix_dissim = pd.DataFrame(index=df_map.columns,columns=df_map.columns).fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "3b9da1c7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Co-occurrence dissimilarity (distance = 1-index) ใช้กับ MDS (อย่าใช้ similarity)\n",
    "for i in range(0,len(item_item_matrix_dissim.columns)):\n",
    "    for j in range(0,len(item_item_matrix_dissim.columns)):\n",
    "        item_item_matrix_dissim.iloc[i,j] = 1-jaccard_index(df_map,i,j)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "b4e75f70",
   "metadata": {},
   "outputs": [],
   "source": [
    "# ใช้แสดง SNA\n",
    "item_item_matrix_dissim.to_excel('output\\coocDissimMatrix.xlsx', index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "9be49a65",
   "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>PKG0001</th>\n",
       "      <th>PKG0002</th>\n",
       "      <th>PKG0004</th>\n",
       "      <th>PKG0006</th>\n",
       "      <th>PKG0007</th>\n",
       "      <th>PKG0008</th>\n",
       "      <th>PKG0009</th>\n",
       "      <th>PKG0010</th>\n",
       "      <th>PKG0011</th>\n",
       "      <th>PKG0012</th>\n",
       "      <th>...</th>\n",
       "      <th>PKG0197</th>\n",
       "      <th>PKG0200</th>\n",
       "      <th>PKG0202</th>\n",
       "      <th>PKG0203</th>\n",
       "      <th>PKG0204</th>\n",
       "      <th>PKG0205</th>\n",
       "      <th>PKG0207</th>\n",
       "      <th>PKG0209</th>\n",
       "      <th>PKG0300</th>\n",
       "      <th>PKG0550</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>รหัส</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>PKG0001</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.750000</td>\n",
       "      <td>0.791667</td>\n",
       "      <td>0.700000</td>\n",
       "      <td>0.740741</td>\n",
       "      <td>0.777778</td>\n",
       "      <td>0.818182</td>\n",
       "      <td>0.766667</td>\n",
       "      <td>0.774194</td>\n",
       "      <td>0.714286</td>\n",
       "      <td>...</td>\n",
       "      <td>0.777778</td>\n",
       "      <td>0.787879</td>\n",
       "      <td>0.869565</td>\n",
       "      <td>0.814815</td>\n",
       "      <td>0.900000</td>\n",
       "      <td>0.692308</td>\n",
       "      <td>0.840000</td>\n",
       "      <td>0.933333</td>\n",
       "      <td>0.703704</td>\n",
       "      <td>0.857143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PKG0002</th>\n",
       "      <td>0.750000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.709677</td>\n",
       "      <td>0.717949</td>\n",
       "      <td>0.636364</td>\n",
       "      <td>0.625000</td>\n",
       "      <td>0.648649</td>\n",
       "      <td>0.451613</td>\n",
       "      <td>0.515152</td>\n",
       "      <td>0.526316</td>\n",
       "      <td>...</td>\n",
       "      <td>0.705882</td>\n",
       "      <td>0.583333</td>\n",
       "      <td>0.843750</td>\n",
       "      <td>0.612903</td>\n",
       "      <td>0.742857</td>\n",
       "      <td>0.593750</td>\n",
       "      <td>0.709677</td>\n",
       "      <td>0.805556</td>\n",
       "      <td>0.685714</td>\n",
       "      <td>0.583333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PKG0004</th>\n",
       "      <td>0.791667</td>\n",
       "      <td>0.709677</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.785714</td>\n",
       "      <td>0.681818</td>\n",
       "      <td>0.727273</td>\n",
       "      <td>0.827586</td>\n",
       "      <td>0.666667</td>\n",
       "      <td>0.680000</td>\n",
       "      <td>0.750000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.880000</td>\n",
       "      <td>0.793103</td>\n",
       "      <td>0.950000</td>\n",
       "      <td>0.772727</td>\n",
       "      <td>0.833333</td>\n",
       "      <td>0.791667</td>\n",
       "      <td>0.800000</td>\n",
       "      <td>0.961538</td>\n",
       "      <td>0.800000</td>\n",
       "      <td>0.870968</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PKG0006</th>\n",
       "      <td>0.700000</td>\n",
       "      <td>0.717949</td>\n",
       "      <td>0.785714</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.555556</td>\n",
       "      <td>0.689655</td>\n",
       "      <td>0.625000</td>\n",
       "      <td>0.645161</td>\n",
       "      <td>0.696970</td>\n",
       "      <td>0.611111</td>\n",
       "      <td>...</td>\n",
       "      <td>0.733333</td>\n",
       "      <td>0.714286</td>\n",
       "      <td>0.807692</td>\n",
       "      <td>0.766667</td>\n",
       "      <td>0.848485</td>\n",
       "      <td>0.741935</td>\n",
       "      <td>0.785714</td>\n",
       "      <td>0.911765</td>\n",
       "      <td>0.620690</td>\n",
       "      <td>0.783784</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PKG0007</th>\n",
       "      <td>0.740741</td>\n",
       "      <td>0.636364</td>\n",
       "      <td>0.681818</td>\n",
       "      <td>0.555556</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.625000</td>\n",
       "      <td>0.700000</td>\n",
       "      <td>0.458333</td>\n",
       "      <td>0.538462</td>\n",
       "      <td>0.548387</td>\n",
       "      <td>...</td>\n",
       "      <td>0.730769</td>\n",
       "      <td>0.620690</td>\n",
       "      <td>0.818182</td>\n",
       "      <td>0.545455</td>\n",
       "      <td>0.821429</td>\n",
       "      <td>0.740741</td>\n",
       "      <td>0.681818</td>\n",
       "      <td>0.933333</td>\n",
       "      <td>0.653846</td>\n",
       "      <td>0.709677</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 122 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "รหัส      PKG0001   PKG0002   PKG0004   PKG0006   PKG0007   PKG0008   PKG0009  \\\n",
       "รหัส                                                                            \n",
       "PKG0001  0.000000  0.750000  0.791667  0.700000  0.740741  0.777778  0.818182   \n",
       "PKG0002  0.750000  0.000000  0.709677  0.717949  0.636364  0.625000  0.648649   \n",
       "PKG0004  0.791667  0.709677  0.000000  0.785714  0.681818  0.727273  0.827586   \n",
       "PKG0006  0.700000  0.717949  0.785714  0.000000  0.555556  0.689655  0.625000   \n",
       "PKG0007  0.740741  0.636364  0.681818  0.555556  0.000000  0.625000  0.700000   \n",
       "\n",
       "รหัส      PKG0010   PKG0011   PKG0012  ...   PKG0197   PKG0200   PKG0202  \\\n",
       "รหัส                                   ...                                 \n",
       "PKG0001  0.766667  0.774194  0.714286  ...  0.777778  0.787879  0.869565   \n",
       "PKG0002  0.451613  0.515152  0.526316  ...  0.705882  0.583333  0.843750   \n",
       "PKG0004  0.666667  0.680000  0.750000  ...  0.880000  0.793103  0.950000   \n",
       "PKG0006  0.645161  0.696970  0.611111  ...  0.733333  0.714286  0.807692   \n",
       "PKG0007  0.458333  0.538462  0.548387  ...  0.730769  0.620690  0.818182   \n",
       "\n",
       "รหัส      PKG0203   PKG0204   PKG0205   PKG0207   PKG0209   PKG0300   PKG0550  \n",
       "รหัส                                                                           \n",
       "PKG0001  0.814815  0.900000  0.692308  0.840000  0.933333  0.703704  0.857143  \n",
       "PKG0002  0.612903  0.742857  0.593750  0.709677  0.805556  0.685714  0.583333  \n",
       "PKG0004  0.772727  0.833333  0.791667  0.800000  0.961538  0.800000  0.870968  \n",
       "PKG0006  0.766667  0.848485  0.741935  0.785714  0.911765  0.620690  0.783784  \n",
       "PKG0007  0.545455  0.821429  0.740741  0.681818  0.933333  0.653846  0.709677  \n",
       "\n",
       "[5 rows x 122 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "item_item_matrix_dissim.head()"
   ]
  }
 ],
 "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.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}