问题二.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "from tqdm import tqdm\n",
    "import pandas as pd\n",
    "\n",
    "tqdm.pandas()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 导入数据\n",
    "导入相应的赛题数据，并且合并连续俩年的数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'\\n旅游产品，亦称旅游服务产品。是指由实物和服务构成。包括旅行商集合景点、交通、食宿、娱乐等设施设备、\\n项目及相应服务出售给旅游者的旅游线路类产品，旅游景区、旅游饭店等单个企业提供给旅游者的活动项目类产品\\n'"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "Hotel_reviews1 = pd.read_excel(\n",
    "    'E:/Modeling/外挂/外挂/【2022-4-26更新】C题问题二/data/2018-2019茂名（含自媒体）.xlsx', sheet_name=0)   # 酒店评论\n",
    "Scenic_reviews1 = pd.read_excel(\n",
    "    'E:/Modeling/外挂/外挂/【2022-4-26更新】C题问题二/data/2018-2019茂名（含自媒体）.xlsx', sheet_name=1)  # 景区评论\n",
    "Travel_tips1 = pd.read_excel(\n",
    "    'E:/Modeling/外挂/外挂/【2022-4-26更新】C题问题二/data/2018-2019茂名（含自媒体）.xlsx', sheet_name=2)     # 游记攻略\n",
    "Dining_reviews1 = pd.read_excel(\n",
    "    'E:/Modeling/外挂/外挂/【2022-4-26更新】C题问题二/data/2018-2019茂名（含自媒体）.xlsx', sheet_name=3)  # 餐饮评论\n",
    "Wechat_article1 = pd.read_excel(\n",
    "    'E:/Modeling/外挂/外挂/【2022-4-26更新】C题问题二/data/2018-2019茂名（含自媒体）.xlsx', sheet_name=4)  # 微信公众号文章\n",
    "\n",
    "\n",
    "Hotel_reviews2 = pd.read_excel(\n",
    "    'E:/Modeling/外挂/外挂/【2022-4-26更新】C题问题二/data/2020-2021茂名（含自媒体）.xlsx', sheet_name=0)   # 酒店评论\n",
    "Scenic_reviews2 = pd.read_excel(\n",
    "    'E:/Modeling/外挂/外挂/【2022-4-26更新】C题问题二/data/2020-2021茂名（含自媒体）.xlsx', sheet_name=1)  # 景区评论\n",
    "Travel_tips2 = pd.read_excel(\n",
    "    'E:/Modeling/外挂/外挂/【2022-4-26更新】C题问题二/data/2020-2021茂名（含自媒体）.xlsx', sheet_name=2)     # 游记攻略\n",
    "Dining_reviews2 = pd.read_excel(\n",
    "    'E:/Modeling/外挂/外挂/【2022-4-26更新】C题问题二/data/2020-2021茂名（含自媒体）.xlsx', sheet_name=3)  # 餐饮评论\n",
    "Wechat_article2 = pd.read_excel(\n",
    "    'E:/Modeling/外挂/外挂/【2022-4-26更新】C题问题二/data/2020-2021茂名（含自媒体）.xlsx', sheet_name=4)  # 微信公众号文章\n",
    "\n",
    "Hotel_reviews = pd.concat([Hotel_reviews1, Hotel_reviews2],axis=0)  # 酒店评论\n",
    "Scenic_reviews = pd.concat([Scenic_reviews1, Scenic_reviews2], axis=0)  # 景区评论\n",
    "Travel_tips = pd.concat([Travel_tips1, Travel_tips2], axis=0)  # 游记攻略\n",
    "Dining_reviews = pd.concat([Dining_reviews1, Dining_reviews2], axis=0)  # 餐饮评论\n",
    "Wechat_article = pd.concat([Wechat_article1, Wechat_article2], axis=0)  # 微信公众号文章\n",
    "'''\n",
    "旅游产品，亦称旅游服务产品。是指由实物和服务构成。包括旅行商集合景点、交通、食宿、娱乐等设施设备、\n",
    "项目及相应服务出售给旅游者的旅游线路类产品，旅游景区、旅游饭店等单个企业提供给旅游者的活动项目类产品\n",
    "'''\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1 提取旅游产品"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.1 数据准备\n",
    "由于酒店评论、景区评论和餐饮评论，直接给出了旅游产品，直接汇总就可以。但微信公众号和游记攻略没有特定的旅游产品，需要建立模型，提取出旅游产品的名称"
   ]
  },
  {
   "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>景区评论ID</th>\n",
       "      <th>城市</th>\n",
       "      <th>景区名称</th>\n",
       "      <th>评论日期</th>\n",
       "      <th>评论内容</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>茂名</td>\n",
       "      <td>中国第一滩旅游度假区</td>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>超级美 冬天白日里最是舒服</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>茂名</td>\n",
       "      <td>广垦（茂名）国家热带农业公园</td>\n",
       "      <td>2018-01-02</td>\n",
       "      <td>上了一节深刻的农业科普课，值了，在携程买价格挺合理，当时现场活动价35元。“氧吧”，“花海”...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>茂名</td>\n",
       "      <td>西江温泉度假村</td>\n",
       "      <td>2018-01-03</td>\n",
       "      <td>西江温泉度假村坐落于南国玉都——信宜市郊北界镇西江岸边，西江温泉故此得名，距市中心12公里，...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>茂名</td>\n",
       "      <td>石根山</td>\n",
       "      <td>2018-01-05</td>\n",
       "      <td>爬的很累！感觉还是挺不错的，设施再升级完善。2个多小时才爬到山顶！要是有玻璃栈道就完美了！</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>茂名</td>\n",
       "      <td>天马山生态旅游区</td>\n",
       "      <td>2018-01-06</td>\n",
       "      <td>这次是和家人一起出游，选择天马山也是觉得比较适合老人和小朋友，游玩起来不会太累。其实早在04...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1006</td>\n",
       "      <td>茂名</td>\n",
       "      <td>石根山</td>\n",
       "      <td>2018-01-08</td>\n",
       "      <td>山很高，空气清新，环境还可以，最好玩的还是滑道</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1007</td>\n",
       "      <td>茂名</td>\n",
       "      <td>阳江春都温泉</td>\n",
       "      <td>2018-01-09</td>\n",
       "      <td>环境清幽淡雅，水质干净够热，休闲好去处，不错</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1008</td>\n",
       "      <td>茂名</td>\n",
       "      <td>阳西青洲岛</td>\n",
       "      <td>2018-01-11</td>\n",
       "      <td>就是熱帶島嶼…沙灘很小，逛逛還可以，水挺清，但沒什麼玩，還不如月亮灣</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1009</td>\n",
       "      <td>茂名</td>\n",
       "      <td>石根山</td>\n",
       "      <td>2018-01-12</td>\n",
       "      <td>玩了一天，石根山风景还是不错的，滑道非常好玩，值得试试</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1010</td>\n",
       "      <td>茂名</td>\n",
       "      <td>海滨公园</td>\n",
       "      <td>2018-01-13</td>\n",
       "      <td>如果天气好，公园特别漂亮，树郁郁葱葱，不过湛江污染严重，好天不多</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   景区评论ID  城市            景区名称        评论日期  \\\n",
       "0    1001  茂名      中国第一滩旅游度假区  2018-01-01   \n",
       "1    1002  茂名  广垦（茂名）国家热带农业公园  2018-01-02   \n",
       "2    1003  茂名         西江温泉度假村  2018-01-03   \n",
       "3    1004  茂名             石根山  2018-01-05   \n",
       "4    1005  茂名        天马山生态旅游区  2018-01-06   \n",
       "5    1006  茂名             石根山  2018-01-08   \n",
       "6    1007  茂名          阳江春都温泉  2018-01-09   \n",
       "7    1008  茂名           阳西青洲岛  2018-01-11   \n",
       "8    1009  茂名             石根山  2018-01-12   \n",
       "9    1010  茂名            海滨公园  2018-01-13   \n",
       "\n",
       "                                                评论内容  \n",
       "0                                      超级美 冬天白日里最是舒服  \n",
       "1  上了一节深刻的农业科普课，值了，在携程买价格挺合理，当时现场活动价35元。“氧吧”，“花海”...  \n",
       "2  西江温泉度假村坐落于南国玉都——信宜市郊北界镇西江岸边，西江温泉故此得名，距市中心12公里，...  \n",
       "3      爬的很累！感觉还是挺不错的，设施再升级完善。2个多小时才爬到山顶！要是有玻璃栈道就完美了！  \n",
       "4  这次是和家人一起出游，选择天马山也是觉得比较适合老人和小朋友，游玩起来不会太累。其实早在04...  \n",
       "5                            山很高，空气清新，环境还可以，最好玩的还是滑道  \n",
       "6                             环境清幽淡雅，水质干净够热，休闲好去处，不错  \n",
       "7                 就是熱帶島嶼…沙灘很小，逛逛還可以，水挺清，但沒什麼玩，還不如月亮灣  \n",
       "8                        玩了一天，石根山风景还是不错的，滑道非常好玩，值得试试  \n",
       "9                   如果天气好，公园特别漂亮，树郁郁葱葱，不过湛江污染严重，好天不多  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Scenic_reviews.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.2转换已有数据\n",
    "把可以转换的景区评论、酒店评论和餐饮评论转换为语料"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████████████████████████████████████████████████████████████████████| 1203/1203 [00:00<00:00, 423620.83it/s]\n"
     ]
    }
   ],
   "source": [
    "def addstr(s):\n",
    "    return '景区评论-'+str(s)\n",
    "\n",
    "Scenic_reviews['语料ID'] = Scenic_reviews['景区评论ID'].progress_apply(addstr)\n",
    "Scenic_reviews['文本'] = Scenic_reviews['评论内容']\n",
    "Scenic_reviews['产品名称'] = Scenic_reviews['景区名称']\n",
    "Scenic_reviews['年份'] = pd.to_datetime(Scenic_reviews['评论日期']).dt.year\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>酒店评论ID</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>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>茂名</td>\n",
       "      <td>茂名君悦商务酒店</td>\n",
       "      <td>2018-12-02</td>\n",
       "      <td>干净卫生服务好</td>\n",
       "      <td>2018-12-02</td>\n",
       "      <td>标双</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>茂名</td>\n",
       "      <td>维也纳国际酒店(茂名电白店)</td>\n",
       "      <td>2018-12-03</td>\n",
       "      <td>环境可以，干净！</td>\n",
       "      <td>2018-12-03</td>\n",
       "      <td>豪华双床房</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>茂名</td>\n",
       "      <td>茂名永利之家</td>\n",
       "      <td>2018-12-04</td>\n",
       "      <td>环境不错，房间卫生都很好，生活也很方便，就是隔音效果不理想，有时太吵。我定的优惠价，性价比很...</td>\n",
       "      <td>2018-12-04</td>\n",
       "      <td>标准单人间</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>茂名</td>\n",
       "      <td>茂名诚荟酒店</td>\n",
       "      <td>2018-12-05</td>\n",
       "      <td>很好.......舒服态度不错</td>\n",
       "      <td>2018-12-05</td>\n",
       "      <td>豪华大床房</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>茂名</td>\n",
       "      <td>茂名华景商务酒店</td>\n",
       "      <td>2018-12-06</td>\n",
       "      <td>#卫生# #设计风格# #酒店餐饮#</td>\n",
       "      <td>2018-12-06</td>\n",
       "      <td>特惠单人间(无窗)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1006</td>\n",
       "      <td>茂名</td>\n",
       "      <td>茂名荔晶大酒店</td>\n",
       "      <td>2018-12-07</td>\n",
       "      <td>交通方便，离父母又比较近些，所以还是选择这里</td>\n",
       "      <td>2018-12-07</td>\n",
       "      <td>商务房</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1007</td>\n",
       "      <td>茂名</td>\n",
       "      <td>好莱登商务宾馆(信宜绍秀体育馆店)</td>\n",
       "      <td>2018-12-08</td>\n",
       "      <td>酒店设施不错，热水很快，中央空调效果很好，服务态度很好，每次出差都是入住这个酒店，值得大家前...</td>\n",
       "      <td>2018-12-08</td>\n",
       "      <td>三人房</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1008</td>\n",
       "      <td>茂名</td>\n",
       "      <td>茂名海豚酒店</td>\n",
       "      <td>2018-12-09</td>\n",
       "      <td>酒店隔音效果很差，杯子里有茶垢，窗子只能打开一点点没啥感觉，去听听他吞吞吐吐吞吞吐吐吞吞吐吐</td>\n",
       "      <td>2018-12-09</td>\n",
       "      <td>标准单人房</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1009</td>\n",
       "      <td>茂名</td>\n",
       "      <td>茂名荔晶大酒店</td>\n",
       "      <td>2018-12-10</td>\n",
       "      <td>晚上11：00以后，还是很安静的，但早上5:00以后，傍边的河东市场就喧闹起来了</td>\n",
       "      <td>2018-12-10</td>\n",
       "      <td>商务房</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1010</td>\n",
       "      <td>茂名</td>\n",
       "      <td>茂名海豚酒店</td>\n",
       "      <td>2018-12-11</td>\n",
       "      <td>没有早餐，有停车场，酒店离海有点点远，很干净，不能加床，我们是国庆去的，平时去住就是120元...</td>\n",
       "      <td>2018-12-11</td>\n",
       "      <td>标准双人房</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   酒店评论ID  城市               酒店名称        评论日期  \\\n",
       "0    1001  茂名           茂名君悦商务酒店  2018-12-02   \n",
       "1    1002  茂名     维也纳国际酒店(茂名电白店)  2018-12-03   \n",
       "2    1003  茂名             茂名永利之家  2018-12-04   \n",
       "3    1004  茂名             茂名诚荟酒店  2018-12-05   \n",
       "4    1005  茂名           茂名华景商务酒店  2018-12-06   \n",
       "5    1006  茂名            茂名荔晶大酒店  2018-12-07   \n",
       "6    1007  茂名  好莱登商务宾馆(信宜绍秀体育馆店)  2018-12-08   \n",
       "7    1008  茂名             茂名海豚酒店  2018-12-09   \n",
       "8    1009  茂名            茂名荔晶大酒店  2018-12-10   \n",
       "9    1010  茂名             茂名海豚酒店  2018-12-11   \n",
       "\n",
       "                                                评论内容        入住日期       入住房型  \n",
       "0                                            干净卫生服务好  2018-12-02         标双  \n",
       "1                                           环境可以，干净！  2018-12-03      豪华双床房  \n",
       "2  环境不错，房间卫生都很好，生活也很方便，就是隔音效果不理想，有时太吵。我定的优惠价，性价比很...  2018-12-04      标准单人间  \n",
       "3                                    很好.......舒服态度不错  2018-12-05      豪华大床房  \n",
       "4                                 #卫生# #设计风格# #酒店餐饮#  2018-12-06  特惠单人间(无窗)  \n",
       "5                             交通方便，离父母又比较近些，所以还是选择这里  2018-12-07        商务房  \n",
       "6  酒店设施不错，热水很快，中央空调效果很好，服务态度很好，每次出差都是入住这个酒店，值得大家前...  2018-12-08        三人房  \n",
       "7     酒店隔音效果很差，杯子里有茶垢，窗子只能打开一点点没啥感觉，去听听他吞吞吐吐吞吞吐吐吞吞吐吐  2018-12-09      标准单人房  \n",
       "8           晚上11：00以后，还是很安静的，但早上5:00以后，傍边的河东市场就喧闹起来了  2018-12-10        商务房  \n",
       "9  没有早餐，有停车场，酒店离海有点点远，很干净，不能加床，我们是国庆去的，平时去住就是120元...  2018-12-11      标准双人房  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Hotel_reviews.head(10)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████████████████████████████████████████████████████████████████████| 1093/1093 [00:00<00:00, 466555.49it/s]\n"
     ]
    }
   ],
   "source": [
    "def addstr(s):\n",
    "    return '酒店评论-'+str(s)\n",
    "\n",
    "Hotel_reviews['语料ID'] = Hotel_reviews['酒店评论ID'].progress_apply(addstr)\n",
    "Hotel_reviews['文本'] = Hotel_reviews['评论内容']\n",
    "Hotel_reviews['产品名称'] = Hotel_reviews['酒店名称']\n",
    "Hotel_reviews['年份'] = pd.to_datetime(Hotel_reviews['评论日期']).dt.year\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>餐饮评论ID</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>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>茂名</td>\n",
       "      <td>盛香烧鹅（东方市场店）</td>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>很好吃推荐！</td>\n",
       "      <td>主食3选1，有赠品</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1002</td>\n",
       "      <td>茂名</td>\n",
       "      <td>清香面包店（车田街店）</td>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>超级好吃 老板又好  量又多</td>\n",
       "      <td>水果忌廉夹心蛋糕（二层）1个，约4磅，圆</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>茂名</td>\n",
       "      <td>功夫鸡排（光华北路店）</td>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>好吃，不得不说比门口正对面那家好吃。服务态度也好。炸的皮松软，不会炸得很硬。</td>\n",
       "      <td>小吃6选1，提供免费WiFi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1004</td>\n",
       "      <td>茂名</td>\n",
       "      <td>茂名浪漫海岸温德姆酒店望海餐厅</td>\n",
       "      <td>2018-01-02</td>\n",
       "      <td>品种少，冷冻食品多。 7点就不再出食物，难以接受。</td>\n",
       "      <td>自助晚餐（浪漫海岸跨年音乐节）</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1005</td>\n",
       "      <td>茂名</td>\n",
       "      <td>清香面包店（车田街店）</td>\n",
       "      <td>2018-01-03</td>\n",
       "      <td>味道不错，至少吃起来不腻，并且全家7个人吃还是挺多的了，就比较实惠咯</td>\n",
       "      <td>水果忌廉夹心蛋糕（二层）1个，约4磅，圆</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1006</td>\n",
       "      <td>茂名</td>\n",
       "      <td>优之品西点</td>\n",
       "      <td>2018-01-04</td>\n",
       "      <td>蛋糕香喉，奶油嫩滑，水果新鲜！超赞！到店买的抹茶慕斯蛋糕，榴莲蛋糕更是唇齿留香、回味无穷！</td>\n",
       "      <td>蛋糕2选1，提供免费WiFi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1007</td>\n",
       "      <td>茂名</td>\n",
       "      <td>优之品西点</td>\n",
       "      <td>2018-01-04</td>\n",
       "      <td>芒果好酸好生啊，看起来黄黄的应该挺甜的，实际好酸。。。</td>\n",
       "      <td>蛋糕2选1，提供免费WiFi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1008</td>\n",
       "      <td>茂名</td>\n",
       "      <td>清香面包店（车田街店）</td>\n",
       "      <td>2018-01-04</td>\n",
       "      <td>蛋糕很漂亮也非常好吃，奶油很厚不腻</td>\n",
       "      <td>水果忌廉夹心蛋糕（一层）1个，约2.5磅，圆</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1009</td>\n",
       "      <td>茂名</td>\n",
       "      <td>旺角亭（为民店）</td>\n",
       "      <td>2018-01-05</td>\n",
       "      <td>很好，一直都有在吃他家的东西，紫菜卷很好吃</td>\n",
       "      <td>休闲单人套餐，提供免费WiFi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1010</td>\n",
       "      <td>茂名</td>\n",
       "      <td>盛香烧鹅（东方市场店）</td>\n",
       "      <td>2018-01-06</td>\n",
       "      <td>来了好多次了，饭菜都适合我的口味，不错的快餐，值得推荐！还会再来</td>\n",
       "      <td>主食3选1，有赠品</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   餐饮评论ID  城市             餐饮名称        评论日期  \\\n",
       "0    1001  茂名      盛香烧鹅（东方市场店）  2018-01-01   \n",
       "1    1002  茂名      清香面包店（车田街店）  2018-01-01   \n",
       "2    1003  茂名      功夫鸡排（光华北路店）  2018-01-01   \n",
       "3    1004  茂名  茂名浪漫海岸温德姆酒店望海餐厅  2018-01-02   \n",
       "4    1005  茂名      清香面包店（车田街店）  2018-01-03   \n",
       "5    1006  茂名            优之品西点  2018-01-04   \n",
       "6    1007  茂名            优之品西点  2018-01-04   \n",
       "7    1008  茂名      清香面包店（车田街店）  2018-01-04   \n",
       "8    1009  茂名         旺角亭（为民店）  2018-01-05   \n",
       "9    1010  茂名      盛香烧鹅（东方市场店）  2018-01-06   \n",
       "\n",
       "                                            评论内容                      标题  \n",
       "0                                         很好吃推荐！               主食3选1，有赠品  \n",
       "1                                 超级好吃 老板又好  量又多    水果忌廉夹心蛋糕（二层）1个，约4磅，圆  \n",
       "2         好吃，不得不说比门口正对面那家好吃。服务态度也好。炸的皮松软，不会炸得很硬。          小吃6选1，提供免费WiFi  \n",
       "3                      品种少，冷冻食品多。 7点就不再出食物，难以接受。         自助晚餐（浪漫海岸跨年音乐节）  \n",
       "4             味道不错，至少吃起来不腻，并且全家7个人吃还是挺多的了，就比较实惠咯    水果忌廉夹心蛋糕（二层）1个，约4磅，圆  \n",
       "5  蛋糕香喉，奶油嫩滑，水果新鲜！超赞！到店买的抹茶慕斯蛋糕，榴莲蛋糕更是唇齿留香、回味无穷！          蛋糕2选1，提供免费WiFi  \n",
       "6                    芒果好酸好生啊，看起来黄黄的应该挺甜的，实际好酸。。。          蛋糕2选1，提供免费WiFi  \n",
       "7                              蛋糕很漂亮也非常好吃，奶油很厚不腻  水果忌廉夹心蛋糕（一层）1个，约2.5磅，圆  \n",
       "8                          很好，一直都有在吃他家的东西，紫菜卷很好吃         休闲单人套餐，提供免费WiFi  \n",
       "9               来了好多次了，饭菜都适合我的口味，不错的快餐，值得推荐！还会再来               主食3选1，有赠品  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Dining_reviews.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████████████████████████████████████████████████████████████████████| 6984/6984 [00:00<00:00, 806903.54it/s]\n"
     ]
    }
   ],
   "source": [
    "def addstr(s):\n",
    "    return '餐饮评论-'+str(s)\n",
    "\n",
    "\n",
    "Dining_reviews['语料ID'] = Dining_reviews['餐饮评论ID'].progress_apply(addstr)\n",
    "Dining_reviews['文本'] = Dining_reviews['评论内容'] + '\\n'+Dining_reviews['标题']\n",
    "Dining_reviews['产品名称'] = Dining_reviews['餐饮名称']\n",
    "Dining_reviews['年份'] = pd.to_datetime(Dining_reviews['评论日期']).dt.year\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.2 单独提取公众号和游记攻略的旅游产品"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|████████████████████████████████████████████████████████████████████████████| 294/294 [00:00<00:00, 484338.33it/s]\n",
      "  0%|                                                                                          | 0/294 [00:00<?, ?it/s]Building prefix dict from the default dictionary ...\n",
      "Loading model from cache C:\\Users\\wy-1226\\AppData\\Local\\Temp\\jieba.cache\n",
      "Loading model cost 0.510 seconds.\n",
      "Prefix dict has been built successfully.\n",
      "100%|████████████████████████████████████████████████████████████████████████████████| 294/294 [00:52<00:00,  5.58it/s]\n",
      "100%|██████████████████████████████████████████████████████████████████████████| 6286/6286 [00:00<00:00, 900426.73it/s]\n",
      "100%|██████████████████████████████████████████████████████████████████████████████| 6286/6286 [12:51<00:00,  8.15it/s]\n"
     ]
    }
   ],
   "source": [
    "# 采用Textrank提取关键词组算法\n",
    "# 这部分待改进\n",
    "from textrank4zh import TextRank4Keyword  # 导入textrank4zh模块\n",
    "import numpy as np\n",
    "def get_keyphrase(s):\n",
    "    tr4w = TextRank4Keyword(allow_speech_tags=['n','nr','nr1','nr2','nrf','ns','nsf','nt','nz','nz','nl','ng'])\n",
    "    tr4w.analyze(text=str(s), lower=True, window=5)  # 文本分析，文本小写，窗口为2\n",
    "    # 最多5个关键词组，有可能一个也没有。词组在原文中出现次数最少为1。\n",
    "    phase_list = tr4w.get_keyphrases(keywords_num=5, min_occur_num=1)\n",
    "    if len(phase_list) ==0:\n",
    "        return np.nan\n",
    "    else:\n",
    "        return phase_list[0]\n",
    "        \n",
    "\n",
    "# 游记攻略\n",
    "Travel_tips = pd.concat([Travel_tips1, Travel_tips2], axis=0)  # 游记攻略\n",
    "def addstr(s):\n",
    "    return '旅游攻略-'+str(s)\n",
    "Travel_tips['语料ID'] = Travel_tips['游记ID'].progress_apply(addstr)\n",
    "Travel_tips['文本'] = Travel_tips['游记标题'] + '\\n'+Travel_tips['正文']\n",
    "Travel_tips['年份'] = pd.to_datetime(Travel_tips['发布时间']).dt.year\n",
    "Travel_tips['产品名称'] = Travel_tips['文本'].progress_apply(get_keyphrase)\n",
    "\n",
    "\n",
    "# 微信公众号文章\n",
    "Wechat_article = pd.concat([Wechat_article1, Wechat_article2], axis=0)  # 微信公众号文章\n",
    "def addstr(s):\n",
    "    return '微信公共号文章-'+str(s)\n",
    "Wechat_article['语料ID'] = Wechat_article['文章ID'].progress_apply(addstr)\n",
    "Wechat_article['文本'] = Wechat_article['公众号标题'] + '\\n'+Wechat_article['正文']\n",
    "Wechat_article['年份'] = pd.to_datetime(Wechat_article['发布时间']).dt.year\n",
    "Wechat_article['产品名称'] = Wechat_article['文本'].progress_apply(get_keyphrase)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 删除没有产品名称的行\n",
    "Travel_tips = Travel_tips.dropna(subset=[\"产品名称\"])\n",
    "Wechat_article = Wechat_article.dropna(subset=[\"产品名称\"])\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.3 存储result2-1表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>语料ID</th>\n",
       "      <th>文本</th>\n",
       "      <th>产品名称</th>\n",
       "      <th>年份</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>餐饮评论-1001</td>\n",
       "      <td>很好吃推荐！\\n主食3选1，有赠品</td>\n",
       "      <td>盛香烧鹅（东方市场店）</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>餐饮评论-1002</td>\n",
       "      <td>超级好吃 老板又好  量又多\\n水果忌廉夹心蛋糕（二层）1个，约4磅，圆</td>\n",
       "      <td>清香面包店（车田街店）</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>餐饮评论-1003</td>\n",
       "      <td>好吃，不得不说比门口正对面那家好吃。服务态度也好。炸的皮松软，不会炸得很硬。\\n小吃6选1，...</td>\n",
       "      <td>功夫鸡排（光华北路店）</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>餐饮评论-1004</td>\n",
       "      <td>品种少，冷冻食品多。 7点就不再出食物，难以接受。\\n自助晚餐（浪漫海岸跨年音乐节）</td>\n",
       "      <td>茂名浪漫海岸温德姆酒店望海餐厅</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>餐饮评论-1005</td>\n",
       "      <td>味道不错，至少吃起来不腻，并且全家7个人吃还是挺多的了，就比较实惠咯\\n水果忌廉夹心蛋糕（二...</td>\n",
       "      <td>清香面包店（车田街店）</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>138</th>\n",
       "      <td>旅游攻略-1285</td>\n",
       "      <td>广东第二粤西第一峰信宜大田顶日落游记\\n国庆想着去登山，然后看看路况到处都堵车，而且炎炎夏日...</td>\n",
       "      <td>信宜大田</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143</th>\n",
       "      <td>旅游攻略-1290</td>\n",
       "      <td>广东周边游｜御水古温泉｜宝藏度假地\\n🏨 茂名 御水古温泉度假酒店\\n广东 茂名 宝藏小众度...</td>\n",
       "      <td>御水古温泉</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144</th>\n",
       "      <td>旅游攻略-1291</td>\n",
       "      <td>99%的人不知道御水古温泉还可以这样玩！\\n🉑️骨灰级御水古温泉3日2夜攻略\\n🉑️御水古温...</td>\n",
       "      <td>古温泉</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146</th>\n",
       "      <td>旅游攻略-1293</td>\n",
       "      <td>我们去茂名走马观花（广东⑮）\\n走马：路上\\n爷爷是一个很重男轻女的传统 广东 人，但在看到...</td>\n",
       "      <td>深圳文和友</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>147</th>\n",
       "      <td>旅游攻略-1294</td>\n",
       "      <td>高州行一D4深镇镇&amp;耀新村\\n昨晚夜宿仙人洞附近民宿一仙人阁山庄。\\n山庄为年轻人回乡创业项...</td>\n",
       "      <td>创业业</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>9442 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          语料ID                                                 文本  \\\n",
       "0    餐饮评论-1001                                  很好吃推荐！\\n主食3选1，有赠品   \n",
       "1    餐饮评论-1002               超级好吃 老板又好  量又多\\n水果忌廉夹心蛋糕（二层）1个，约4磅，圆   \n",
       "2    餐饮评论-1003  好吃，不得不说比门口正对面那家好吃。服务态度也好。炸的皮松软，不会炸得很硬。\\n小吃6选1，...   \n",
       "3    餐饮评论-1004         品种少，冷冻食品多。 7点就不再出食物，难以接受。\\n自助晚餐（浪漫海岸跨年音乐节）   \n",
       "4    餐饮评论-1005  味道不错，至少吃起来不腻，并且全家7个人吃还是挺多的了，就比较实惠咯\\n水果忌廉夹心蛋糕（二...   \n",
       "..         ...                                                ...   \n",
       "138  旅游攻略-1285  广东第二粤西第一峰信宜大田顶日落游记\\n国庆想着去登山，然后看看路况到处都堵车，而且炎炎夏日...   \n",
       "143  旅游攻略-1290  广东周边游｜御水古温泉｜宝藏度假地\\n🏨 茂名 御水古温泉度假酒店\\n广东 茂名 宝藏小众度...   \n",
       "144  旅游攻略-1291  99%的人不知道御水古温泉还可以这样玩！\\n🉑️骨灰级御水古温泉3日2夜攻略\\n🉑️御水古温...   \n",
       "146  旅游攻略-1293  我们去茂名走马观花（广东⑮）\\n走马：路上\\n爷爷是一个很重男轻女的传统 广东 人，但在看到...   \n",
       "147  旅游攻略-1294  高州行一D4深镇镇&耀新村\\n昨晚夜宿仙人洞附近民宿一仙人阁山庄。\\n山庄为年轻人回乡创业项...   \n",
       "\n",
       "                产品名称    年份  \n",
       "0        盛香烧鹅（东方市场店）  2018  \n",
       "1        清香面包店（车田街店）  2018  \n",
       "2        功夫鸡排（光华北路店）  2018  \n",
       "3    茂名浪漫海岸温德姆酒店望海餐厅  2018  \n",
       "4        清香面包店（车田街店）  2018  \n",
       "..               ...   ...  \n",
       "138             信宜大田  2021  \n",
       "143            御水古温泉  2021  \n",
       "144              古温泉  2021  \n",
       "146            深圳文和友  2021  \n",
       "147              创业业  2021  \n",
       "\n",
       "[9442 rows x 4 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "all_df = pd.DataFrame(columns=['语料ID', '文本', '产品名称'])\n",
    "all_df['语料ID'] = pd.concat([Dining_reviews['语料ID'], Hotel_reviews['语料ID'],\n",
    "                           Scenic_reviews['语料ID'], Travel_tips['语料ID']], axis=0)\n",
    "all_df['产品名称'] = pd.concat([Dining_reviews['产品名称'], Hotel_reviews['产品名称'],\n",
    "                           Scenic_reviews['产品名称'], Travel_tips['产品名称']], axis=0)\n",
    "all_df['文本'] = pd.concat([Dining_reviews['文本'], Hotel_reviews['文本'],\n",
    "                         Scenic_reviews['文本'], Travel_tips['文本']], axis=0)\n",
    "all_df['年份'] = pd.concat([Dining_reviews['年份'], Hotel_reviews['年份'],\n",
    "                         Scenic_reviews['年份'], Travel_tips['年份']], axis=0)\n",
    "all_df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "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>语料ID</th>\n",
       "      <th>产品ID</th>\n",
       "      <th>产品名称</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>餐饮评论-1001</td>\n",
       "      <td>ID1</td>\n",
       "      <td>盛香烧鹅（东方市场店）</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>餐饮评论-1002</td>\n",
       "      <td>ID2</td>\n",
       "      <td>清香面包店（车田街店）</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>餐饮评论-1003</td>\n",
       "      <td>ID3</td>\n",
       "      <td>功夫鸡排（光华北路店）</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>餐饮评论-1004</td>\n",
       "      <td>ID4</td>\n",
       "      <td>茂名浪漫海岸温德姆酒店望海餐厅</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>餐饮评论-1005</td>\n",
       "      <td>ID5</td>\n",
       "      <td>清香面包店（车田街店）</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>138</th>\n",
       "      <td>旅游攻略-1285</td>\n",
       "      <td>ID9438</td>\n",
       "      <td>信宜大田</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143</th>\n",
       "      <td>旅游攻略-1290</td>\n",
       "      <td>ID9439</td>\n",
       "      <td>御水古温泉</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144</th>\n",
       "      <td>旅游攻略-1291</td>\n",
       "      <td>ID9440</td>\n",
       "      <td>古温泉</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146</th>\n",
       "      <td>旅游攻略-1293</td>\n",
       "      <td>ID9441</td>\n",
       "      <td>深圳文和友</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>147</th>\n",
       "      <td>旅游攻略-1294</td>\n",
       "      <td>ID9442</td>\n",
       "      <td>创业业</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>9442 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          语料ID    产品ID             产品名称\n",
       "0    餐饮评论-1001     ID1      盛香烧鹅（东方市场店）\n",
       "1    餐饮评论-1002     ID2      清香面包店（车田街店）\n",
       "2    餐饮评论-1003     ID3      功夫鸡排（光华北路店）\n",
       "3    餐饮评论-1004     ID4  茂名浪漫海岸温德姆酒店望海餐厅\n",
       "4    餐饮评论-1005     ID5      清香面包店（车田街店）\n",
       "..         ...     ...              ...\n",
       "138  旅游攻略-1285  ID9438             信宜大田\n",
       "143  旅游攻略-1290  ID9439            御水古温泉\n",
       "144  旅游攻略-1291  ID9440              古温泉\n",
       "146  旅游攻略-1293  ID9441            深圳文和友\n",
       "147  旅游攻略-1294  ID9442              创业业\n",
       "\n",
       "[9442 rows x 3 columns]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "product_id = ['ID'+str(i+1) for i in range(len(all_df))]\n",
    "all_df['产品ID'] = product_id\n",
    "result2 = all_df[['语料ID','产品ID','产品名称']]\n",
    "result2\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "result2.to_csv('E:/Modeling/外挂/外挂/【2022-4-26更新】C题问题二/data/result2-1.csv', index=False)\n",
    "all_df.to_csv('E:/Modeling/外挂/外挂/【2022-4-26更新】C题问题二/data/问题二所有数据汇总.csv', index=False)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2 热度分析\n",
    "先导入数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "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>语料ID</th>\n",
       "      <th>文本</th>\n",
       "      <th>产品名称</th>\n",
       "      <th>年份</th>\n",
       "      <th>产品ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>餐饮评论-1001</td>\n",
       "      <td>很好吃推荐！\\n主食3选1，有赠品</td>\n",
       "      <td>盛香烧鹅（东方市场店）</td>\n",
       "      <td>2018</td>\n",
       "      <td>ID1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>餐饮评论-1002</td>\n",
       "      <td>超级好吃 老板又好  量又多\\n水果忌廉夹心蛋糕（二层）1个，约4磅，圆</td>\n",
       "      <td>清香面包店（车田街店）</td>\n",
       "      <td>2018</td>\n",
       "      <td>ID2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>餐饮评论-1003</td>\n",
       "      <td>好吃，不得不说比门口正对面那家好吃。服务态度也好。炸的皮松软，不会炸得很硬。\\n小吃6选1，...</td>\n",
       "      <td>功夫鸡排（光华北路店）</td>\n",
       "      <td>2018</td>\n",
       "      <td>ID3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>餐饮评论-1004</td>\n",
       "      <td>品种少，冷冻食品多。 7点就不再出食物，难以接受。\\n自助晚餐（浪漫海岸跨年音乐节）</td>\n",
       "      <td>茂名浪漫海岸温德姆酒店望海餐厅</td>\n",
       "      <td>2018</td>\n",
       "      <td>ID4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>餐饮评论-1005</td>\n",
       "      <td>味道不错，至少吃起来不腻，并且全家7个人吃还是挺多的了，就比较实惠咯\\n水果忌廉夹心蛋糕（二...</td>\n",
       "      <td>清香面包店（车田街店）</td>\n",
       "      <td>2018</td>\n",
       "      <td>ID5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9437</th>\n",
       "      <td>旅游攻略-1285</td>\n",
       "      <td>广东第二粤西第一峰信宜大田顶日落游记\\n国庆想着去登山，然后看看路况到处都堵车，而且炎炎夏日...</td>\n",
       "      <td>信宜大田</td>\n",
       "      <td>2021</td>\n",
       "      <td>ID9438</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9438</th>\n",
       "      <td>旅游攻略-1290</td>\n",
       "      <td>广东周边游｜御水古温泉｜宝藏度假地\\n🏨 茂名 御水古温泉度假酒店\\n广东 茂名 宝藏小众度...</td>\n",
       "      <td>御水古温泉</td>\n",
       "      <td>2021</td>\n",
       "      <td>ID9439</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9439</th>\n",
       "      <td>旅游攻略-1291</td>\n",
       "      <td>99%的人不知道御水古温泉还可以这样玩！\\n🉑️骨灰级御水古温泉3日2夜攻略\\n🉑️御水古温...</td>\n",
       "      <td>古温泉</td>\n",
       "      <td>2021</td>\n",
       "      <td>ID9440</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9440</th>\n",
       "      <td>旅游攻略-1293</td>\n",
       "      <td>我们去茂名走马观花（广东⑮）\\n走马：路上\\n爷爷是一个很重男轻女的传统 广东 人，但在看到...</td>\n",
       "      <td>深圳文和友</td>\n",
       "      <td>2021</td>\n",
       "      <td>ID9441</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9441</th>\n",
       "      <td>旅游攻略-1294</td>\n",
       "      <td>高州行一D4深镇镇&amp;耀新村\\n昨晚夜宿仙人洞附近民宿一仙人阁山庄。\\n山庄为年轻人回乡创业项...</td>\n",
       "      <td>创业业</td>\n",
       "      <td>2021</td>\n",
       "      <td>ID9442</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>9442 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           语料ID                                                 文本  \\\n",
       "0     餐饮评论-1001                                  很好吃推荐！\\n主食3选1，有赠品   \n",
       "1     餐饮评论-1002               超级好吃 老板又好  量又多\\n水果忌廉夹心蛋糕（二层）1个，约4磅，圆   \n",
       "2     餐饮评论-1003  好吃，不得不说比门口正对面那家好吃。服务态度也好。炸的皮松软，不会炸得很硬。\\n小吃6选1，...   \n",
       "3     餐饮评论-1004         品种少，冷冻食品多。 7点就不再出食物，难以接受。\\n自助晚餐（浪漫海岸跨年音乐节）   \n",
       "4     餐饮评论-1005  味道不错，至少吃起来不腻，并且全家7个人吃还是挺多的了，就比较实惠咯\\n水果忌廉夹心蛋糕（二...   \n",
       "...         ...                                                ...   \n",
       "9437  旅游攻略-1285  广东第二粤西第一峰信宜大田顶日落游记\\n国庆想着去登山，然后看看路况到处都堵车，而且炎炎夏日...   \n",
       "9438  旅游攻略-1290  广东周边游｜御水古温泉｜宝藏度假地\\n🏨 茂名 御水古温泉度假酒店\\n广东 茂名 宝藏小众度...   \n",
       "9439  旅游攻略-1291  99%的人不知道御水古温泉还可以这样玩！\\n🉑️骨灰级御水古温泉3日2夜攻略\\n🉑️御水古温...   \n",
       "9440  旅游攻略-1293  我们去茂名走马观花（广东⑮）\\n走马：路上\\n爷爷是一个很重男轻女的传统 广东 人，但在看到...   \n",
       "9441  旅游攻略-1294  高州行一D4深镇镇&耀新村\\n昨晚夜宿仙人洞附近民宿一仙人阁山庄。\\n山庄为年轻人回乡创业项...   \n",
       "\n",
       "                 产品名称    年份    产品ID  \n",
       "0         盛香烧鹅（东方市场店）  2018     ID1  \n",
       "1         清香面包店（车田街店）  2018     ID2  \n",
       "2         功夫鸡排（光华北路店）  2018     ID3  \n",
       "3     茂名浪漫海岸温德姆酒店望海餐厅  2018     ID4  \n",
       "4         清香面包店（车田街店）  2018     ID5  \n",
       "...               ...   ...     ...  \n",
       "9437             信宜大田  2021  ID9438  \n",
       "9438            御水古温泉  2021  ID9439  \n",
       "9439              古温泉  2021  ID9440  \n",
       "9440            深圳文和友  2021  ID9441  \n",
       "9441              创业业  2021  ID9442  \n",
       "\n",
       "[9442 rows x 5 columns]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import warnings\n",
    "import pandas as pd\n",
    "from tqdm import tqdm\n",
    "tqdm.pandas()\n",
    "warnings.filterwarnings('ignore')\n",
    "\n",
    "all_df = pd.read_csv('E:/Modeling/外挂/外挂/【2022-4-26更新】C题问题二/data/问题二所有数据汇总.csv')\n",
    "all_df\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.1 统计情感得分"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████████████████████████████████████████████████████████████████████████| 9442/9442 [02:21<00:00, 66.94it/s]\n"
     ]
    }
   ],
   "source": [
    "from cnsenti import Sentiment\n",
    "senti = Sentiment(pos='E:/Modeling/外挂/外挂/【2022-4-26更新】C题问题二/data/pos.txt',  #正面词典txt文件相对路径\n",
    "                  neg='E:/Modeling/外挂/外挂/【2022-4-26更新】C题问题二/data/neg.txt',  #负面词典txt文件相对路径\n",
    "                  merge=True,             #融合cnsenti自带词典和用户导入的自定义词典\n",
    "                  encoding='utf-8')      #两txt均为utf-8编码\n",
    "def emotion_score(s):\n",
    "\n",
    "    r = senti.sentiment_count(str(s))\n",
    "    if r['pos']>r['neg']:\n",
    "        score = (r['pos']-r['neg'])/r['words']\n",
    "    elif r['pos'] < r['neg']:\n",
    "        score = (r['pos']-r['neg'])/r['words']\n",
    "    else :\n",
    "        score = 0\n",
    "    return score\n",
    "\n",
    "all_df['情感得分'] = all_df['文本'].progress_apply(emotion_score)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "测试一下情感得分"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "#senti = Sentiment()      #两txt均为utf-8编码\n",
    "#for str1 in all_df['情感得分']:\n",
    "  #  result=senti.sentiment_count(str(str1))\n",
    "    #result=1\n",
    "   # print(result)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.2 按年份统计旅游产品出现的次数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "year_2018 = all_df[all_df['年份']==2018]\n",
    "year_2019 = all_df[all_df['年份'] == 2019]\n",
    "year_2020 = all_df[all_df['年份'] == 2020]\n",
    "year_2021 = all_df[all_df['年份'] == 2021]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|████████████████████████████████████████████████████████████████████████████| 976/976 [00:00<00:00, 979339.88it/s]\n"
     ]
    }
   ],
   "source": [
    "dict_2018 = dict(year_2018['产品名称'].value_counts())\n",
    "def get_frequency(s):\n",
    "    fre = dict_2018[s]\n",
    "    return fre\n",
    "year_2018['出现频次'] = year_2018['产品名称'].progress_apply(get_frequency)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████████████████████████████████████████████████████████████████████| 1218/1218 [00:00<00:00, 610426.85it/s]\n"
     ]
    }
   ],
   "source": [
    "dict_2019 = dict(year_2019['产品名称'].value_counts())\n",
    "def get_frequency(s):\n",
    "    fre = dict_2019[s]\n",
    "    return fre\n",
    "year_2019['出现频次'] = year_2019['产品名称'].progress_apply(get_frequency)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████████████████████████████████████████████████████████████████████| 2117/2117 [00:00<00:00, 707460.89it/s]\n"
     ]
    }
   ],
   "source": [
    "dict_2020 = dict(year_2020['产品名称'].value_counts())\n",
    "def get_frequency(s):\n",
    "    fre = dict_2020[s]\n",
    "    return fre\n",
    "year_2020['出现频次'] = year_2020['产品名称'].progress_apply(get_frequency)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████████████████████████████████████████████████████████████████████| 5131/5131 [00:00<00:00, 857375.16it/s]\n"
     ]
    }
   ],
   "source": [
    "dict_2021 = dict(year_2021['产品名称'].value_counts())\n",
    "def get_frequency(s):\n",
    "    fre = dict_2021[s]\n",
    "    return fre\n",
    "year_2021['出现频次'] = year_2021['产品名称'].progress_apply(get_frequency)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.3 计算产品热度得分\n",
    "将情感得分和频次相加"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# 计算综合得分\n",
    "year_2018['产品热度总分'] = 0.8*year_2018['出现频次']+200*year_2018['情感得分']+0\n",
    "year_2019['产品热度总分'] = 0.8*year_2019['出现频次']+200*year_2019['情感得分']+1*10\n",
    "year_2020['产品热度总分'] = 0.8*year_2020['出现频次']+200*year_2020['情感得分']+2*15\n",
    "year_2021['产品热度总分'] = 0.8*year_2021['出现频次']+200*year_2021['情感得分']+3*20\n",
    "\n",
    "year_2018['产品热度'] = year_2018['产品热度总分'].div(np.sum(year_2018['产品热度总分']), axis=0)#化成一个小数 加起来为1\n",
    "year_2019['产品热度'] = year_2019['产品热度总分'].div(np.sum(year_2019['产品热度总分']), axis=0)\n",
    "year_2020['产品热度'] = year_2020['产品热度总分'].div(np.sum(year_2020['产品热度总分']), axis=0)\n",
    "year_2021['产品热度'] = year_2021['产品热度总分'].div(np.sum(year_2021['产品热度总分']), axis=0)\n",
    "\n",
    "year_2018 = year_2018.sort_values(by=\"产品热度\", ascending=False).reset_index(drop=True)\n",
    "year_2019 = year_2019.sort_values(by=\"产品热度\", ascending=False).reset_index(drop=True)\n",
    "year_2020 = year_2020.sort_values(by=\"产品热度\", ascending=False).reset_index(drop=True)\n",
    "year_2021 = year_2021.sort_values(by=\"产品热度\", ascending=False).reset_index(drop=True)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "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>语料ID</th>\n",
       "      <th>文本</th>\n",
       "      <th>产品名称</th>\n",
       "      <th>年份</th>\n",
       "      <th>产品ID</th>\n",
       "      <th>情感得分</th>\n",
       "      <th>出现频次</th>\n",
       "      <th>产品热度总分</th>\n",
       "      <th>产品热度</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>餐饮评论-1307</td>\n",
       "      <td>很不错  家里的妹妹很喜欢 讲很漂亮 味道也不错  感谢商家  推荐\\n蛋糕2选1,约2磅，圆</td>\n",
       "      <td>清香面包店（车田街店）</td>\n",
       "      <td>2018</td>\n",
       "      <td>ID307</td>\n",
       "      <td>0.323529</td>\n",
       "      <td>87</td>\n",
       "      <td>134.305882</td>\n",
       "      <td>0.003446</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>餐饮评论-1308</td>\n",
       "      <td>没错  又是我   这次是蓝色  近两年了吧好像  吃的都是这家的蛋糕   性价比高   老...</td>\n",
       "      <td>清香面包店（车田街店）</td>\n",
       "      <td>2018</td>\n",
       "      <td>ID308</td>\n",
       "      <td>0.301887</td>\n",
       "      <td>87</td>\n",
       "      <td>129.977358</td>\n",
       "      <td>0.003335</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>餐饮评论-1215</td>\n",
       "      <td>口味超级好，都说好吃  推荐  而且便宜\\n蛋糕8选1</td>\n",
       "      <td>优之品西点</td>\n",
       "      <td>2018</td>\n",
       "      <td>ID215</td>\n",
       "      <td>0.368421</td>\n",
       "      <td>70</td>\n",
       "      <td>129.684211</td>\n",
       "      <td>0.003328</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>餐饮评论-1355</td>\n",
       "      <td>话不多说   老板人好细心   蛋糕好吃  性价比好  依旧好评！！！\\n水果忌廉夹心蛋糕（...</td>\n",
       "      <td>清香面包店（车田街店）</td>\n",
       "      <td>2018</td>\n",
       "      <td>ID355</td>\n",
       "      <td>0.285714</td>\n",
       "      <td>87</td>\n",
       "      <td>126.742857</td>\n",
       "      <td>0.003252</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>餐饮评论-1404</td>\n",
       "      <td>多样  丰富  干净   豪华\\n单人平日东南亚自助晚餐</td>\n",
       "      <td>茂名浪漫海岸温德姆酒店望海餐厅</td>\n",
       "      <td>2018</td>\n",
       "      <td>ID404</td>\n",
       "      <td>0.588235</td>\n",
       "      <td>10</td>\n",
       "      <td>125.647059</td>\n",
       "      <td>0.003224</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5126</th>\n",
       "      <td>餐饮评论-3679</td>\n",
       "      <td>这个太小，绝对没有三磅，还没有水果，对不起这个价格\\n3磅榴莲千层蛋糕1个，约9英寸，圆形</td>\n",
       "      <td>果之度（迎宾三路店）</td>\n",
       "      <td>2021</td>\n",
       "      <td>ID2679</td>\n",
       "      <td>-0.142857</td>\n",
       "      <td>11</td>\n",
       "      <td>40.228571</td>\n",
       "      <td>0.000049</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5127</th>\n",
       "      <td>景区评论-2038</td>\n",
       "      <td>太差了还要收费20元什么都没有</td>\n",
       "      <td>雅米乐园</td>\n",
       "      <td>2021</td>\n",
       "      <td>ID9115</td>\n",
       "      <td>-0.111111</td>\n",
       "      <td>2</td>\n",
       "      <td>39.377778</td>\n",
       "      <td>0.000048</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5128</th>\n",
       "      <td>景区评论-2097</td>\n",
       "      <td>一点都不好玩，工作人员服务态度也不好</td>\n",
       "      <td>古郡水城</td>\n",
       "      <td>2021</td>\n",
       "      <td>ID9174</td>\n",
       "      <td>-0.125000</td>\n",
       "      <td>2</td>\n",
       "      <td>36.600000</td>\n",
       "      <td>0.000045</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5129</th>\n",
       "      <td>景区评论-2119</td>\n",
       "      <td>好玩，刺激，nice</td>\n",
       "      <td>鼎龙湾德萨斯水世界</td>\n",
       "      <td>2021</td>\n",
       "      <td>ID9196</td>\n",
       "      <td>-0.200000</td>\n",
       "      <td>20</td>\n",
       "      <td>36.000000</td>\n",
       "      <td>0.000044</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5130</th>\n",
       "      <td>景区评论-2042</td>\n",
       "      <td>没事就去看看咯</td>\n",
       "      <td>湖光岩</td>\n",
       "      <td>2021</td>\n",
       "      <td>ID9119</td>\n",
       "      <td>-0.200000</td>\n",
       "      <td>14</td>\n",
       "      <td>31.200000</td>\n",
       "      <td>0.000038</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>9442 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           语料ID                                                 文本  \\\n",
       "0     餐饮评论-1307    很不错  家里的妹妹很喜欢 讲很漂亮 味道也不错  感谢商家  推荐\\n蛋糕2选1,约2磅，圆   \n",
       "1     餐饮评论-1308  没错  又是我   这次是蓝色  近两年了吧好像  吃的都是这家的蛋糕   性价比高   老...   \n",
       "2     餐饮评论-1215                        口味超级好，都说好吃  推荐  而且便宜\\n蛋糕8选1   \n",
       "3     餐饮评论-1355  话不多说   老板人好细心   蛋糕好吃  性价比好  依旧好评！！！\\n水果忌廉夹心蛋糕（...   \n",
       "4     餐饮评论-1404                       多样  丰富  干净   豪华\\n单人平日东南亚自助晚餐   \n",
       "...         ...                                                ...   \n",
       "5126  餐饮评论-3679      这个太小，绝对没有三磅，还没有水果，对不起这个价格\\n3磅榴莲千层蛋糕1个，约9英寸，圆形   \n",
       "5127  景区评论-2038                                    太差了还要收费20元什么都没有   \n",
       "5128  景区评论-2097                                 一点都不好玩，工作人员服务态度也不好   \n",
       "5129  景区评论-2119                                         好玩，刺激，nice   \n",
       "5130  景区评论-2042                                            没事就去看看咯   \n",
       "\n",
       "                 产品名称    年份    产品ID      情感得分  出现频次      产品热度总分      产品热度  \n",
       "0         清香面包店（车田街店）  2018   ID307  0.323529    87  134.305882  0.003446  \n",
       "1         清香面包店（车田街店）  2018   ID308  0.301887    87  129.977358  0.003335  \n",
       "2               优之品西点  2018   ID215  0.368421    70  129.684211  0.003328  \n",
       "3         清香面包店（车田街店）  2018   ID355  0.285714    87  126.742857  0.003252  \n",
       "4     茂名浪漫海岸温德姆酒店望海餐厅  2018   ID404  0.588235    10  125.647059  0.003224  \n",
       "...               ...   ...     ...       ...   ...         ...       ...  \n",
       "5126       果之度（迎宾三路店）  2021  ID2679 -0.142857    11   40.228571  0.000049  \n",
       "5127             雅米乐园  2021  ID9115 -0.111111     2   39.377778  0.000048  \n",
       "5128             古郡水城  2021  ID9174 -0.125000     2   36.600000  0.000045  \n",
       "5129        鼎龙湾德萨斯水世界  2021  ID9196 -0.200000    20   36.000000  0.000044  \n",
       "5130              湖光岩  2021  ID9119 -0.200000    14   31.200000  0.000038  \n",
       "\n",
       "[9442 rows x 9 columns]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "product_hot_score = pd.concat([year_2018, year_2019, year_2020, year_2021], axis=0)\n",
    "product_hot_score\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.4 判断产品类型"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|████████████████████████████████████████████████████████████████████████████| 9442/9442 [00:05<00:00, 1680.21it/s]\n"
     ]
    },
    {
     "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>语料ID</th>\n",
       "      <th>文本</th>\n",
       "      <th>产品名称</th>\n",
       "      <th>年份</th>\n",
       "      <th>产品ID</th>\n",
       "      <th>情感得分</th>\n",
       "      <th>出现频次</th>\n",
       "      <th>产品热度总分</th>\n",
       "      <th>产品热度</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>餐饮评论-1307</td>\n",
       "      <td>不错 家里 妹妹 喜欢 讲 很漂亮 味道 不错 感谢 商家 推荐 蛋糕 选约 磅 圆</td>\n",
       "      <td>清香面包店（车田街店）</td>\n",
       "      <td>2018</td>\n",
       "      <td>ID307</td>\n",
       "      <td>0.323529</td>\n",
       "      <td>87</td>\n",
       "      <td>134.305882</td>\n",
       "      <td>0.003446</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>餐饮评论-1308</td>\n",
       "      <td>没错 蓝色 近两年 好像 吃 这家 蛋糕 性价比 高 老板 热情 芭比娃娃 卡通 蛋糕 水果...</td>\n",
       "      <td>清香面包店（车田街店）</td>\n",
       "      <td>2018</td>\n",
       "      <td>ID308</td>\n",
       "      <td>0.301887</td>\n",
       "      <td>87</td>\n",
       "      <td>129.977358</td>\n",
       "      <td>0.003335</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>餐饮评论-1215</td>\n",
       "      <td>口味 超级 说 好吃 推荐 便宜 蛋糕 选</td>\n",
       "      <td>优之品西点</td>\n",
       "      <td>2018</td>\n",
       "      <td>ID215</td>\n",
       "      <td>0.368421</td>\n",
       "      <td>70</td>\n",
       "      <td>129.684211</td>\n",
       "      <td>0.003328</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>餐饮评论-1355</td>\n",
       "      <td>话 说 老板 人好 细心 蛋糕 好吃 性价比 依旧 好评 水果 忌廉 夹心 蛋糕 一层 约 磅 圆</td>\n",
       "      <td>清香面包店（车田街店）</td>\n",
       "      <td>2018</td>\n",
       "      <td>ID355</td>\n",
       "      <td>0.285714</td>\n",
       "      <td>87</td>\n",
       "      <td>126.742857</td>\n",
       "      <td>0.003252</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>餐饮评论-1404</td>\n",
       "      <td>多样 丰富 干净 豪华 单人 平日 东南亚 自助 晚餐</td>\n",
       "      <td>茂名浪漫海岸温德姆酒店望海餐厅</td>\n",
       "      <td>2018</td>\n",
       "      <td>ID404</td>\n",
       "      <td>0.588235</td>\n",
       "      <td>10</td>\n",
       "      <td>125.647059</td>\n",
       "      <td>0.003224</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5126</th>\n",
       "      <td>餐饮评论-3679</td>\n",
       "      <td>太小 绝对 没有 三磅 没有 水果 对不起 价格 磅 榴莲 千层 蛋糕 约 英寸 圆形</td>\n",
       "      <td>果之度（迎宾三路店）</td>\n",
       "      <td>2021</td>\n",
       "      <td>ID2679</td>\n",
       "      <td>-0.142857</td>\n",
       "      <td>11</td>\n",
       "      <td>40.228571</td>\n",
       "      <td>0.000049</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5127</th>\n",
       "      <td>景区评论-2038</td>\n",
       "      <td>太差 收费 元 没有</td>\n",
       "      <td>雅米乐园</td>\n",
       "      <td>2021</td>\n",
       "      <td>ID9115</td>\n",
       "      <td>-0.111111</td>\n",
       "      <td>2</td>\n",
       "      <td>39.377778</td>\n",
       "      <td>0.000048</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5128</th>\n",
       "      <td>景区评论-2097</td>\n",
       "      <td>一点 不好玩 工作人员 服务态度 不好</td>\n",
       "      <td>古郡水城</td>\n",
       "      <td>2021</td>\n",
       "      <td>ID9174</td>\n",
       "      <td>-0.125000</td>\n",
       "      <td>2</td>\n",
       "      <td>36.600000</td>\n",
       "      <td>0.000045</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5129</th>\n",
       "      <td>景区评论-2119</td>\n",
       "      <td>好玩 刺激</td>\n",
       "      <td>鼎龙湾德萨斯水世界</td>\n",
       "      <td>2021</td>\n",
       "      <td>ID9196</td>\n",
       "      <td>-0.200000</td>\n",
       "      <td>20</td>\n",
       "      <td>36.000000</td>\n",
       "      <td>0.000044</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5130</th>\n",
       "      <td>景区评论-2042</td>\n",
       "      <td>没事 看看 咯</td>\n",
       "      <td>湖光岩</td>\n",
       "      <td>2021</td>\n",
       "      <td>ID9119</td>\n",
       "      <td>-0.200000</td>\n",
       "      <td>14</td>\n",
       "      <td>31.200000</td>\n",
       "      <td>0.000038</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>9442 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           语料ID                                                 文本  \\\n",
       "0     餐饮评论-1307         不错 家里 妹妹 喜欢 讲 很漂亮 味道 不错 感谢 商家 推荐 蛋糕 选约 磅 圆   \n",
       "1     餐饮评论-1308  没错 蓝色 近两年 好像 吃 这家 蛋糕 性价比 高 老板 热情 芭比娃娃 卡通 蛋糕 水果...   \n",
       "2     餐饮评论-1215                              口味 超级 说 好吃 推荐 便宜 蛋糕 选   \n",
       "3     餐饮评论-1355  话 说 老板 人好 细心 蛋糕 好吃 性价比 依旧 好评 水果 忌廉 夹心 蛋糕 一层 约 磅 圆   \n",
       "4     餐饮评论-1404                        多样 丰富 干净 豪华 单人 平日 东南亚 自助 晚餐   \n",
       "...         ...                                                ...   \n",
       "5126  餐饮评论-3679        太小 绝对 没有 三磅 没有 水果 对不起 价格 磅 榴莲 千层 蛋糕 约 英寸 圆形   \n",
       "5127  景区评论-2038                                         太差 收费 元 没有   \n",
       "5128  景区评论-2097                                一点 不好玩 工作人员 服务态度 不好   \n",
       "5129  景区评论-2119                                              好玩 刺激   \n",
       "5130  景区评论-2042                                            没事 看看 咯   \n",
       "\n",
       "                 产品名称    年份    产品ID      情感得分  出现频次      产品热度总分      产品热度  \n",
       "0         清香面包店（车田街店）  2018   ID307  0.323529    87  134.305882  0.003446  \n",
       "1         清香面包店（车田街店）  2018   ID308  0.301887    87  129.977358  0.003335  \n",
       "2               优之品西点  2018   ID215  0.368421    70  129.684211  0.003328  \n",
       "3         清香面包店（车田街店）  2018   ID355  0.285714    87  126.742857  0.003252  \n",
       "4     茂名浪漫海岸温德姆酒店望海餐厅  2018   ID404  0.588235    10  125.647059  0.003224  \n",
       "...               ...   ...     ...       ...   ...         ...       ...  \n",
       "5126       果之度（迎宾三路店）  2021  ID2679 -0.142857    11   40.228571  0.000049  \n",
       "5127             雅米乐园  2021  ID9115 -0.111111     2   39.377778  0.000048  \n",
       "5128             古郡水城  2021  ID9174 -0.125000     2   36.600000  0.000045  \n",
       "5129        鼎龙湾德萨斯水世界  2021  ID9196 -0.200000    20   36.000000  0.000044  \n",
       "5130              湖光岩  2021  ID9119 -0.200000    14   31.200000  0.000038  \n",
       "\n",
       "[9442 rows x 9 columns]"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 分词\n",
    "import re\n",
    "import jieba\n",
    "stopword_list = [k.strip() for k in open(\n",
    "    'E://Modeling//外挂//外挂//【2022-4-26更新】C题问题二//stop//cn_stopwords.txt', encoding='utf8').readlines() if k.strip() != '']\n",
    "def clearTxt(line):\n",
    "    if line != '':\n",
    "        line = str(line).strip()\n",
    "        #去除文本中的英文和数字\n",
    "        line = re.sub(\"[a-zA-Z0-9]\", \"\", line)\n",
    "        #只保留中文、大小写字母\n",
    "        reg = \"[^0-9A-Za-z\\u4e00-\\u9fa5]\"\n",
    "        line = re.sub(reg, '', line)\n",
    "        #分词\n",
    "        segList = jieba.cut(line, cut_all=False)\n",
    "        segSentence = ''\n",
    "        for word in segList:\n",
    "            if word != '\\t':\n",
    "                segSentence += word + \" \"\n",
    "    # 去停用词\n",
    "    wordList = segSentence.split(' ')\n",
    "    sentence = ''\n",
    "    for word in wordList:\n",
    "        word = word.strip()\n",
    "        if word not in stopword_list:\n",
    "            if word != '\\t':\n",
    "                sentence += word + \" \"\n",
    "    return sentence.strip()\n",
    "\n",
    "\n",
    "product_hot_score['文本'] = product_hot_score['文本'].progress_apply(clearTxt)\n",
    "product_hot_score\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████████████████████████████████████████████████████████████████████| 9442/9442 [00:00<00:00, 822911.55it/s]\n"
     ]
    }
   ],
   "source": [
    "# 景区、酒店、网红景点、民宿、特色餐饮、乡村旅游、文创\n",
    "def get_product_type(s):\n",
    "    if '景区' in s:\n",
    "        return '景区'\n",
    "    elif '酒店' in s:\n",
    "        return '酒店'\n",
    "    elif '餐饮' in s:\n",
    "        return '特色餐饮'\n",
    "    elif '景点' in s:\n",
    "        return '景点'\n",
    "    elif '民宿' in s:\n",
    "        return '民宿'\n",
    "    elif '乡村' in s:\n",
    "        return '乡村旅游'\n",
    "    elif '文创' in s:\n",
    "        return '文创'\n",
    "    else:\n",
    "        return '景点'\n",
    "\n",
    "product_hot_score['产品类型判断文本'] = product_hot_score['语料ID'] +' '+product_hot_score['文本']\n",
    "\n",
    "product_hot_score['产品类型'] = product_hot_score['产品类型判断文本'].progress_apply(get_product_type)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "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>语料ID</th>\n",
       "      <th>文本</th>\n",
       "      <th>产品名称</th>\n",
       "      <th>年份</th>\n",
       "      <th>产品ID</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>0</th>\n",
       "      <td>餐饮评论-1307</td>\n",
       "      <td>不错 家里 妹妹 喜欢 讲 很漂亮 味道 不错 感谢 商家 推荐 蛋糕 选约 磅 圆</td>\n",
       "      <td>清香面包店（车田街店）</td>\n",
       "      <td>2018</td>\n",
       "      <td>ID307</td>\n",
       "      <td>0.323529</td>\n",
       "      <td>87</td>\n",
       "      <td>134.305882</td>\n",
       "      <td>0.003446</td>\n",
       "      <td>餐饮评论-1307 不错 家里 妹妹 喜欢 讲 很漂亮 味道 不错 感谢 商家 推荐 蛋糕 ...</td>\n",
       "      <td>特色餐饮</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>餐饮评论-1215</td>\n",
       "      <td>口味 超级 说 好吃 推荐 便宜 蛋糕 选</td>\n",
       "      <td>优之品西点</td>\n",
       "      <td>2018</td>\n",
       "      <td>ID215</td>\n",
       "      <td>0.368421</td>\n",
       "      <td>70</td>\n",
       "      <td>129.684211</td>\n",
       "      <td>0.003328</td>\n",
       "      <td>餐饮评论-1215 口味 超级 说 好吃 推荐 便宜 蛋糕 选</td>\n",
       "      <td>特色餐饮</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>餐饮评论-1404</td>\n",
       "      <td>多样 丰富 干净 豪华 单人 平日 东南亚 自助 晚餐</td>\n",
       "      <td>茂名浪漫海岸温德姆酒店望海餐厅</td>\n",
       "      <td>2018</td>\n",
       "      <td>ID404</td>\n",
       "      <td>0.588235</td>\n",
       "      <td>10</td>\n",
       "      <td>125.647059</td>\n",
       "      <td>0.003224</td>\n",
       "      <td>餐饮评论-1404 多样 丰富 干净 豪华 单人 平日 东南亚 自助 晚餐</td>\n",
       "      <td>特色餐饮</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>餐饮评论-1597</td>\n",
       "      <td>老板 服务态度 超级 蛋糕 很漂亮 做工 精致 强烈推荐 强烈推荐 蛋糕 选约 英寸 圆形</td>\n",
       "      <td>丰年面包店（茂南区店）</td>\n",
       "      <td>2018</td>\n",
       "      <td>ID597</td>\n",
       "      <td>0.555556</td>\n",
       "      <td>14</td>\n",
       "      <td>122.311111</td>\n",
       "      <td>0.003139</td>\n",
       "      <td>餐饮评论-1597 老板 服务态度 超级 蛋糕 很漂亮 做工 精致 强烈推荐 强烈推荐 蛋糕...</td>\n",
       "      <td>特色餐饮</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>餐饮评论-1456</td>\n",
       "      <td>服务周到 味道好 值得 拥有 滋味 烤鸡 双人 餐 提供 免费</td>\n",
       "      <td>优乐堡（开发区店）</td>\n",
       "      <td>2018</td>\n",
       "      <td>ID456</td>\n",
       "      <td>0.388889</td>\n",
       "      <td>41</td>\n",
       "      <td>110.577778</td>\n",
       "      <td>0.002838</td>\n",
       "      <td>餐饮评论-1456 服务周到 味道好 值得 拥有 滋味 烤鸡 双人 餐 提供 免费</td>\n",
       "      <td>特色餐饮</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5039</th>\n",
       "      <td>旅游攻略-1237</td>\n",
       "      <td>窦州 温泉 信宜 汽车 总站 交通车 元 直达 镇隆镇 日 下午 到达 窦州 温泉 窦州 温...</td>\n",
       "      <td>窦州温泉</td>\n",
       "      <td>2021</td>\n",
       "      <td>ID9410</td>\n",
       "      <td>0.025237</td>\n",
       "      <td>1</td>\n",
       "      <td>65.847319</td>\n",
       "      <td>0.000081</td>\n",
       "      <td>旅游攻略-1237 窦州 温泉 信宜 汽车 总站 交通车 元 直达 镇隆镇 日 下午 到达 ...</td>\n",
       "      <td>景点</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5054</th>\n",
       "      <td>旅游攻略-1269</td>\n",
       "      <td>高州 旅游 攻略 想来 高州 旅游 小伙伴 注意 一个 本地人 旅游 攻略 参考 一下 让你...</td>\n",
       "      <td>高州大酒店</td>\n",
       "      <td>2021</td>\n",
       "      <td>ID9428</td>\n",
       "      <td>0.017778</td>\n",
       "      <td>1</td>\n",
       "      <td>64.355556</td>\n",
       "      <td>0.000079</td>\n",
       "      <td>旅游攻略-1269 高州 旅游 攻略 想来 高州 旅游 小伙伴 注意 一个 本地人 旅游 攻...</td>\n",
       "      <td>酒店</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5073</th>\n",
       "      <td>旅游攻略-1262</td>\n",
       "      <td>茂名 玉湖 仙人洞 五一 假期 应 友人 相约 自驾 前往 茂名 仙人洞 一游 朋友家 玉 ...</td>\n",
       "      <td>仙人洞景区</td>\n",
       "      <td>2021</td>\n",
       "      <td>ID9423</td>\n",
       "      <td>0.012238</td>\n",
       "      <td>1</td>\n",
       "      <td>63.247552</td>\n",
       "      <td>0.000078</td>\n",
       "      <td>旅游攻略-1262 茂名 玉湖 仙人洞 五一 假期 应 友人 相约 自驾 前往 茂名 仙人洞...</td>\n",
       "      <td>景区</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5105</th>\n",
       "      <td>酒店评论-1919</td>\n",
       "      <td>还好 不错</td>\n",
       "      <td>化州森思思酒店</td>\n",
       "      <td>2021</td>\n",
       "      <td>ID7903</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1</td>\n",
       "      <td>60.800000</td>\n",
       "      <td>0.000075</td>\n",
       "      <td>酒店评论-1919 还好 不错</td>\n",
       "      <td>酒店</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5112</th>\n",
       "      <td>旅游攻略-1273</td>\n",
       "      <td>好心 湖畔 黑马 小筑 月 笼纱油 甘窝村 兄弟 别墅 吟风 泼墨 大气 心怀 阳光 胸有朗...</td>\n",
       "      <td>黑马小筑</td>\n",
       "      <td>2021</td>\n",
       "      <td>ID9431</td>\n",
       "      <td>-0.032787</td>\n",
       "      <td>1</td>\n",
       "      <td>54.242623</td>\n",
       "      <td>0.000066</td>\n",
       "      <td>旅游攻略-1273 好心 湖畔 黑马 小筑 月 笼纱油 甘窝村 兄弟 别墅 吟风 泼墨 大气...</td>\n",
       "      <td>景点</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>401 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           语料ID                                                 文本  \\\n",
       "0     餐饮评论-1307         不错 家里 妹妹 喜欢 讲 很漂亮 味道 不错 感谢 商家 推荐 蛋糕 选约 磅 圆   \n",
       "2     餐饮评论-1215                              口味 超级 说 好吃 推荐 便宜 蛋糕 选   \n",
       "4     餐饮评论-1404                        多样 丰富 干净 豪华 单人 平日 东南亚 自助 晚餐   \n",
       "5     餐饮评论-1597      老板 服务态度 超级 蛋糕 很漂亮 做工 精致 强烈推荐 强烈推荐 蛋糕 选约 英寸 圆形   \n",
       "9     餐饮评论-1456                    服务周到 味道好 值得 拥有 滋味 烤鸡 双人 餐 提供 免费   \n",
       "...         ...                                                ...   \n",
       "5039  旅游攻略-1237  窦州 温泉 信宜 汽车 总站 交通车 元 直达 镇隆镇 日 下午 到达 窦州 温泉 窦州 温...   \n",
       "5054  旅游攻略-1269  高州 旅游 攻略 想来 高州 旅游 小伙伴 注意 一个 本地人 旅游 攻略 参考 一下 让你...   \n",
       "5073  旅游攻略-1262  茂名 玉湖 仙人洞 五一 假期 应 友人 相约 自驾 前往 茂名 仙人洞 一游 朋友家 玉 ...   \n",
       "5105  酒店评论-1919                                              还好 不错   \n",
       "5112  旅游攻略-1273  好心 湖畔 黑马 小筑 月 笼纱油 甘窝村 兄弟 别墅 吟风 泼墨 大气 心怀 阳光 胸有朗...   \n",
       "\n",
       "                 产品名称    年份    产品ID      情感得分  出现频次      产品热度总分      产品热度  \\\n",
       "0         清香面包店（车田街店）  2018   ID307  0.323529    87  134.305882  0.003446   \n",
       "2               优之品西点  2018   ID215  0.368421    70  129.684211  0.003328   \n",
       "4     茂名浪漫海岸温德姆酒店望海餐厅  2018   ID404  0.588235    10  125.647059  0.003224   \n",
       "5         丰年面包店（茂南区店）  2018   ID597  0.555556    14  122.311111  0.003139   \n",
       "9           优乐堡（开发区店）  2018   ID456  0.388889    41  110.577778  0.002838   \n",
       "...               ...   ...     ...       ...   ...         ...       ...   \n",
       "5039             窦州温泉  2021  ID9410  0.025237     1   65.847319  0.000081   \n",
       "5054            高州大酒店  2021  ID9428  0.017778     1   64.355556  0.000079   \n",
       "5073            仙人洞景区  2021  ID9423  0.012238     1   63.247552  0.000078   \n",
       "5105          化州森思思酒店  2021  ID7903  0.000000     1   60.800000  0.000075   \n",
       "5112             黑马小筑  2021  ID9431 -0.032787     1   54.242623  0.000066   \n",
       "\n",
       "                                               产品类型判断文本  产品类型  \n",
       "0     餐饮评论-1307 不错 家里 妹妹 喜欢 讲 很漂亮 味道 不错 感谢 商家 推荐 蛋糕 ...  特色餐饮  \n",
       "2                       餐饮评论-1215 口味 超级 说 好吃 推荐 便宜 蛋糕 选  特色餐饮  \n",
       "4                 餐饮评论-1404 多样 丰富 干净 豪华 单人 平日 东南亚 自助 晚餐  特色餐饮  \n",
       "5     餐饮评论-1597 老板 服务态度 超级 蛋糕 很漂亮 做工 精致 强烈推荐 强烈推荐 蛋糕...  特色餐饮  \n",
       "9             餐饮评论-1456 服务周到 味道好 值得 拥有 滋味 烤鸡 双人 餐 提供 免费  特色餐饮  \n",
       "...                                                 ...   ...  \n",
       "5039  旅游攻略-1237 窦州 温泉 信宜 汽车 总站 交通车 元 直达 镇隆镇 日 下午 到达 ...    景点  \n",
       "5054  旅游攻略-1269 高州 旅游 攻略 想来 高州 旅游 小伙伴 注意 一个 本地人 旅游 攻...    酒店  \n",
       "5073  旅游攻略-1262 茂名 玉湖 仙人洞 五一 假期 应 友人 相约 自驾 前往 茂名 仙人洞...    景区  \n",
       "5105                                    酒店评论-1919 还好 不错    酒店  \n",
       "5112  旅游攻略-1273 好心 湖畔 黑马 小筑 月 笼纱油 甘窝村 兄弟 别墅 吟风 泼墨 大气...    景点  \n",
       "\n",
       "[401 rows x 11 columns]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 去除重复的产品\n",
    "product_hot_score2 = product_hot_score.drop_duplicates(['产品名称'])\n",
    "product_hot_score2\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "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>产品ID</th>\n",
       "      <th>产品类型</th>\n",
       "      <th>产品名称</th>\n",
       "      <th>产品热度</th>\n",
       "      <th>年份</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ID1</td>\n",
       "      <td>特色餐饮</td>\n",
       "      <td>清香面包店（车田街店）</td>\n",
       "      <td>0.003446</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ID2</td>\n",
       "      <td>特色餐饮</td>\n",
       "      <td>优之品西点</td>\n",
       "      <td>0.003328</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ID3</td>\n",
       "      <td>特色餐饮</td>\n",
       "      <td>茂名浪漫海岸温德姆酒店望海餐厅</td>\n",
       "      <td>0.003224</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>ID4</td>\n",
       "      <td>特色餐饮</td>\n",
       "      <td>丰年面包店（茂南区店）</td>\n",
       "      <td>0.003139</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>ID5</td>\n",
       "      <td>特色餐饮</td>\n",
       "      <td>优乐堡（开发区店）</td>\n",
       "      <td>0.002838</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5039</th>\n",
       "      <td>ID397</td>\n",
       "      <td>景点</td>\n",
       "      <td>窦州温泉</td>\n",
       "      <td>0.000081</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5054</th>\n",
       "      <td>ID398</td>\n",
       "      <td>酒店</td>\n",
       "      <td>高州大酒店</td>\n",
       "      <td>0.000079</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5073</th>\n",
       "      <td>ID399</td>\n",
       "      <td>景区</td>\n",
       "      <td>仙人洞景区</td>\n",
       "      <td>0.000078</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5105</th>\n",
       "      <td>ID400</td>\n",
       "      <td>酒店</td>\n",
       "      <td>化州森思思酒店</td>\n",
       "      <td>0.000075</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5112</th>\n",
       "      <td>ID401</td>\n",
       "      <td>景点</td>\n",
       "      <td>黑马小筑</td>\n",
       "      <td>0.000066</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>401 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       产品ID  产品类型             产品名称      产品热度    年份\n",
       "0       ID1  特色餐饮      清香面包店（车田街店）  0.003446  2018\n",
       "2       ID2  特色餐饮            优之品西点  0.003328  2018\n",
       "4       ID3  特色餐饮  茂名浪漫海岸温德姆酒店望海餐厅  0.003224  2018\n",
       "5       ID4  特色餐饮      丰年面包店（茂南区店）  0.003139  2018\n",
       "9       ID5  特色餐饮        优乐堡（开发区店）  0.002838  2018\n",
       "...     ...   ...              ...       ...   ...\n",
       "5039  ID397    景点             窦州温泉  0.000081  2021\n",
       "5054  ID398    酒店            高州大酒店  0.000079  2021\n",
       "5073  ID399    景区            仙人洞景区  0.000078  2021\n",
       "5105  ID400    酒店          化州森思思酒店  0.000075  2021\n",
       "5112  ID401    景点             黑马小筑  0.000066  2021\n",
       "\n",
       "[401 rows x 5 columns]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 产品 ID 产品类型 产品名称 产品热度 年份\n",
    "\n",
    "result2_2 = product_hot_score2[['产品ID','产品类型','产品名称','产品热度','年份']]\n",
    "result2_2['产品ID'] = ['ID'+str(i+1) for i in range(len(result2_2))]\n",
    "result2_2\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "result2_2.to_csv('E://Modeling//外挂//外挂//【2022-4-26更新】C题问题二//data//result2-2.csv',index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.5 疫情前后产品热度和产品类型"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████████████████████████████████████████████████████████████████████| 2194/2194 [00:00<00:00, 733309.66it/s]\n",
      "100%|██████████████████████████████████████████████████████████████████████████| 7248/7248 [00:00<00:00, 908394.05it/s]\n"
     ]
    }
   ],
   "source": [
    "# 计算产品热度\n",
    "pre_data = all_df[all_df['年份']<2020]\n",
    "after_data = all_df[all_df['年份']>2019]\n",
    "dict_pre = dict(pre_data['产品名称'].value_counts())\n",
    "dict_after = dict(after_data['产品名称'].value_counts())\n",
    "def get_pre_frequency(s):\n",
    "    fre = dict_pre[s]\n",
    "    return fre\n",
    "\n",
    "\n",
    "def get_after_frequency(s):\n",
    "    fre = dict_after[s]\n",
    "    return fre\n",
    "\n",
    "pre_data['出现频次'] = pre_data['产品名称'].progress_apply(get_pre_frequency)\n",
    "after_data['出现频次'] = after_data['产品名称'].progress_apply(get_after_frequency)\n",
    "# 计算综合得分\n",
    "pre_data['产品热度总分'] = 3*pre_data['出现频次']+2*pre_data['情感得分']\n",
    "after_data['产品热度总分'] = 3*after_data['出现频次']+2*after_data['情感得分']\n",
    "\n",
    "\n",
    "pre_data['产品热度'] = pre_data['产品热度总分'].div(np.sum(pre_data['产品热度总分']), axis=0)\n",
    "after_data['产品热度'] = after_data['产品热度总分'].div(np.sum(after_data['产品热度总分']), axis=0)\n",
    "\n",
    "pre_data_sort = pre_data.sort_values(by=\"产品热度\", ascending=False).reset_index(drop=True)\n",
    "after_data_sort = after_data.sort_values(\n",
    "    by=\"产品热度\", ascending=False).reset_index(drop=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 判断产品类型\n",
    "import re\n",
    "import jieba\n",
    "stopword_list = [k.strip() for k in open(\n",
    "    'E:\\Modeling\\外挂\\外挂\\【2022-4-26更新】C题问题二\\stop\\cn_stopwords.txt', encoding='utf8').readlines() if k.strip() != '']\n",
    "def clearTxt(line):\n",
    "    if line != '':\n",
    "        line = str(line).strip()\n",
    "        #去除文本中的英文和数字\n",
    "        line = re.sub(\"[a-zA-Z0-9]\", \"\", line)\n",
    "        #只保留中文、大小写字母\n",
    "        reg = \"[^0-9A-Za-z\\u4e00-\\u9fa5]\"\n",
    "        line = re.sub(reg, '', line)\n",
    "        #分词\n",
    "        segList = jieba.cut(line, cut_all=False)\n",
    "        segSentence = ''\n",
    "        for word in segList:\n",
    "           # global segSentence\n",
    "            if word != '\\t':\n",
    "                segSentence += word + \" \"\n",
    "    # 去停用词\n",
    "    wordList = segSentence.split(' ')\n",
    "    sentence = ''\n",
    "    for word in wordList:\n",
    "        word = word.strip()\n",
    "        if word not in stopword_list:\n",
    "            if word != '\\t':\n",
    "                sentence += word + \" \"\n",
    "    return sentence.strip()\n",
    "\n",
    "# 景区、酒店、网红景点、民宿、特色餐饮、乡村旅游、文创\n",
    "def get_product_type(s):\n",
    "    if '景区' in s:\n",
    "        return '景区'\n",
    "    elif '酒店' in s:\n",
    "        return '酒店'\n",
    "    elif '餐饮' in s:\n",
    "        return '特色餐饮'\n",
    "    elif '景点' in s:\n",
    "        return '景点'\n",
    "    elif '民宿' in s:\n",
    "        return '民宿'\n",
    "    elif '乡村' in s:\n",
    "        return '乡村旅游'\n",
    "    elif '文创' in s:\n",
    "        return '文创'\n",
    "    else:\n",
    "        return '景点'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|████████████████████████████████████████████████████████████████████████████| 2194/2194 [00:01<00:00, 1458.42it/s]\n",
      "100%|██████████████████████████████████████████████████████████████████████████| 2194/2194 [00:00<00:00, 733251.23it/s]\n"
     ]
    }
   ],
   "source": [
    "pre_data_sort['文本'] = pre_data_sort['文本'].progress_apply(clearTxt)\n",
    "pre_data_sort['产品类型判断文本'] = pre_data_sort['语料ID'] + ' '+pre_data_sort['文本']\n",
    "\n",
    "pre_data_sort['产品类型'] = pre_data_sort['产品类型判断文本'].progress_apply(get_product_type)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|████████████████████████████████████████████████████████████████████████████| 7248/7248 [00:04<00:00, 1766.03it/s]\n",
      "100%|██████████████████████████████████████████████████████████████████████████| 7248/7248 [00:00<00:00, 908448.34it/s]\n"
     ]
    }
   ],
   "source": [
    "after_data_sort['文本'] = after_data_sort['文本'].progress_apply(clearTxt)\n",
    "after_data_sort['产品类型判断文本'] = after_data_sort['语料ID'] + \\\n",
    "    ' '+after_data_sort['文本']\n",
    "\n",
    "after_data_sort['产品类型'] = after_data_sort['产品类型判断文本'].progress_apply(\n",
    "    get_product_type)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "    \n",
    "pre_data_sort.to_csv('E://Modeling//外挂//外挂//【2022-4-26更新】C题问题二//data//疫情前产品热度.csv', index=False)\n",
    "after_data_sort.to_csv('E://Modeling//外挂//外挂//【2022-4-26更新】C题问题二//data//疫情后产品热度.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "2fc9f0689f2f32664301ce51aaed3853cc1802bb7b4d4a74b41993575fbadbc0"
  },
  "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": 2
}