comment_analysis_process.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数据预处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "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",
       "      <th>购买时间</th>\n",
       "      <th>手机型号</th>\n",
       "      <th>销量</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.000080e+11</td>\n",
       "      <td>黑色这款相当大气，*外形看起来舒服，双面玻璃，不锈钢机身，四周钢琴烤漆，握起来很有手感，也很...</td>\n",
       "      <td>0</td>\n",
       "      <td>19</td>\n",
       "      <td>50</td>\n",
       "      <td>5</td>\n",
       "      <td>5999</td>\n",
       "      <td>2020/3/4 16:30</td>\n",
       "      <td>Apple iPhone 11 (A2223) 128GB 黑色 移动联通电信4G手机 双卡双待</td>\n",
       "      <td>1377123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.000080e+11</td>\n",
       "      <td>这个颜色很精致，特地选的，这次很给力五-佰的京东内 部折扣，可添迦Ⅴ:号：（181-7282...</td>\n",
       "      <td>103</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>5999</td>\n",
       "      <td>2020/3/6 13:31</td>\n",
       "      <td>Apple iPhone 11 (A2223) 128GB 黑色 移动联通电信4G手机 双卡双待</td>\n",
       "      <td>1377123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.000080e+11</td>\n",
       "      <td>绿色的很漂亮，老婆喜欢。运行速度一流，显示效果不错，音质一流，拍照效果进步很大，尤其夜拍，很...</td>\n",
       "      <td>0</td>\n",
       "      <td>255</td>\n",
       "      <td>287</td>\n",
       "      <td>5</td>\n",
       "      <td>5999</td>\n",
       "      <td>2020/1/26 20:29</td>\n",
       "      <td>Apple iPhone 11 (A2223) 128GB 黑色 移动联通电信4G手机 双卡双待</td>\n",
       "      <td>1377123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.000080e+11</td>\n",
       "      <td>外形看起来舒服，双面玻璃，不锈钢机身，四周钢琴烤漆，握起来很有手感，也很大气，手感不错，有份...</td>\n",
       "      <td>201</td>\n",
       "      <td>292</td>\n",
       "      <td>198</td>\n",
       "      <td>5</td>\n",
       "      <td>5999</td>\n",
       "      <td>2020/1/5 10:54</td>\n",
       "      <td>Apple iPhone 11 (A2223) 128GB 黑色 移动联通电信4G手机 双卡双待</td>\n",
       "      <td>1377123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.000080e+11</td>\n",
       "      <td>手感特别好，颜色很喜欢，大小很合适，特别好看，屏幕音效也特别好，特别特别喜欢，拍照效果也特别...</td>\n",
       "      <td>201</td>\n",
       "      <td>485</td>\n",
       "      <td>116</td>\n",
       "      <td>5</td>\n",
       "      <td>5999</td>\n",
       "      <td>2019/12/15 21:26</td>\n",
       "      <td>Apple iPhone 11 (A2223) 128GB 黑色 移动联通电信4G手机 双卡双待</td>\n",
       "      <td>1377123</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           用户ID                                               评论内容  会员级别  点赞数  \\\n",
       "0  1.000080e+11  黑色这款相当大气，*外形看起来舒服，双面玻璃，不锈钢机身，四周钢琴烤漆，握起来很有手感，也很...     0   19   \n",
       "1  1.000080e+11  这个颜色很精致，特地选的，这次很给力五-佰的京东内 部折扣，可添迦Ⅴ:号：（181-7282...   103    2   \n",
       "2  1.000080e+11  绿色的很漂亮，老婆喜欢。运行速度一流，显示效果不错，音质一流，拍照效果进步很大，尤其夜拍，很...     0  255   \n",
       "3  1.000080e+11  外形看起来舒服，双面玻璃，不锈钢机身，四周钢琴烤漆，握起来很有手感，也很大气，手感不错，有份...   201  292   \n",
       "4  1.000080e+11  手感特别好，颜色很喜欢，大小很合适，特别好看，屏幕音效也特别好，特别特别喜欢，拍照效果也特别...   201  485   \n",
       "\n",
       "   回复数  得分    价格              购买时间  \\\n",
       "0   50   5  5999    2020/3/4 16:30   \n",
       "1    3   5  5999    2020/3/6 13:31   \n",
       "2  287   5  5999   2020/1/26 20:29   \n",
       "3  198   5  5999    2020/1/5 10:54   \n",
       "4  116   5  5999  2019/12/15 21:26   \n",
       "\n",
       "                                               手机型号       销量  \n",
       "0  Apple iPhone 11 (A2223) 128GB 黑色 移动联通电信4G手机 双卡双待  1377123  \n",
       "1  Apple iPhone 11 (A2223) 128GB 黑色 移动联通电信4G手机 双卡双待  1377123  \n",
       "2  Apple iPhone 11 (A2223) 128GB 黑色 移动联通电信4G手机 双卡双待  1377123  \n",
       "3  Apple iPhone 11 (A2223) 128GB 黑色 移动联通电信4G手机 双卡双待  1377123  \n",
       "4  Apple iPhone 11 (A2223) 128GB 黑色 移动联通电信4G手机 双卡双待  1377123  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_csv('data/tmp_test_data.csv', encoding='utf-8')\n",
    "df.columns = df.columns.str.strip(' ')#去除列名中的空格\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "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",
       "      <th>购买时间</th>\n",
       "      <th>手机型号</th>\n",
       "      <th>销量</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.000080e+11</td>\n",
       "      <td>黑色这款相当大气，*外形看起来舒服，双面玻璃，不锈钢机身，四周钢琴烤漆，握起来很有手感，也很...</td>\n",
       "      <td>0</td>\n",
       "      <td>19</td>\n",
       "      <td>50</td>\n",
       "      <td>5</td>\n",
       "      <td>5999</td>\n",
       "      <td>2020/3/4 16:30</td>\n",
       "      <td>Apple_iPhone_11</td>\n",
       "      <td>1377123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.000080e+11</td>\n",
       "      <td>这个颜色很精致，特地选的，这次很给力五-佰的京东内 部折扣，可添迦Ⅴ:号：（181-7282...</td>\n",
       "      <td>103</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>5999</td>\n",
       "      <td>2020/3/6 13:31</td>\n",
       "      <td>Apple_iPhone_11</td>\n",
       "      <td>1377123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.000080e+11</td>\n",
       "      <td>绿色的很漂亮，老婆喜欢。运行速度一流，显示效果不错，音质一流，拍照效果进步很大，尤其夜拍，很...</td>\n",
       "      <td>0</td>\n",
       "      <td>255</td>\n",
       "      <td>287</td>\n",
       "      <td>5</td>\n",
       "      <td>5999</td>\n",
       "      <td>2020/1/26 20:29</td>\n",
       "      <td>Apple_iPhone_11</td>\n",
       "      <td>1377123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.000080e+11</td>\n",
       "      <td>外形看起来舒服，双面玻璃，不锈钢机身，四周钢琴烤漆，握起来很有手感，也很大气，手感不错，有份...</td>\n",
       "      <td>201</td>\n",
       "      <td>292</td>\n",
       "      <td>198</td>\n",
       "      <td>5</td>\n",
       "      <td>5999</td>\n",
       "      <td>2020/1/5 10:54</td>\n",
       "      <td>Apple_iPhone_11</td>\n",
       "      <td>1377123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.000080e+11</td>\n",
       "      <td>手感特别好，颜色很喜欢，大小很合适，特别好看，屏幕音效也特别好，特别特别喜欢，拍照效果也特别...</td>\n",
       "      <td>201</td>\n",
       "      <td>485</td>\n",
       "      <td>116</td>\n",
       "      <td>5</td>\n",
       "      <td>5999</td>\n",
       "      <td>2019/12/15 21:26</td>\n",
       "      <td>Apple_iPhone_11</td>\n",
       "      <td>1377123</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           用户ID                                               评论内容  会员级别  点赞数  \\\n",
       "0  1.000080e+11  黑色这款相当大气，*外形看起来舒服，双面玻璃，不锈钢机身，四周钢琴烤漆，握起来很有手感，也很...     0   19   \n",
       "1  1.000080e+11  这个颜色很精致，特地选的，这次很给力五-佰的京东内 部折扣，可添迦Ⅴ:号：（181-7282...   103    2   \n",
       "2  1.000080e+11  绿色的很漂亮，老婆喜欢。运行速度一流，显示效果不错，音质一流，拍照效果进步很大，尤其夜拍，很...     0  255   \n",
       "3  1.000080e+11  外形看起来舒服，双面玻璃，不锈钢机身，四周钢琴烤漆，握起来很有手感，也很大气，手感不错，有份...   201  292   \n",
       "4  1.000080e+11  手感特别好，颜色很喜欢，大小很合适，特别好看，屏幕音效也特别好，特别特别喜欢，拍照效果也特别...   201  485   \n",
       "\n",
       "   回复数  得分    价格              购买时间             手机型号       销量  \n",
       "0   50   5  5999    2020/3/4 16:30  Apple_iPhone_11  1377123  \n",
       "1    3   5  5999    2020/3/6 13:31  Apple_iPhone_11  1377123  \n",
       "2  287   5  5999   2020/1/26 20:29  Apple_iPhone_11  1377123  \n",
       "3  198   5  5999    2020/1/5 10:54  Apple_iPhone_11  1377123  \n",
       "4  116   5  5999  2019/12/15 21:26  Apple_iPhone_11  1377123  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def format_phone_name(full_name,sub):\n",
    "    full_name = str(full_name)\n",
    "    index = full_name.find(sub)   #第一次出现的位置\n",
    "    index2=full_name.find(sub,index+1)  #第二次出现的位置\n",
    "    index3=full_name.find(sub,index2+1)  #第三次出现的位置\n",
    "    new_name = full_name[:index3].translate(str.maketrans('/ \\\\','___'))\n",
    "    return new_name\n",
    "\n",
    "df = df.drop_duplicates()  #去重\n",
    "#df.drop_duplicates(['评论内容','手机型号'])#若评论内容和手机型号一样，则认为数据无效，避免水军刷评论\n",
    "df['评论内容'] = df['评论内容'].fillna('99999') #将空值所在行填充为99999 \n",
    "df['手机型号'] = df['手机型号'].fillna('99999') \n",
    "row_comment_index = df[df.评论内容=='99999'].index.tolist()  #找出评论空值所在行索引\n",
    "row_type_index = df[df.手机型号=='99999'].index.tolist()   #找出手机型号所在行索引\n",
    "row_index = row_comment_index + row_type_index\n",
    "df[\"手机型号\"]=df.apply(lambda x:format_phone_name(x['手机型号'],' '),axis=1)\n",
    "df = df.drop(row_index)\n",
    "df.isnull().sum()  #查看每一列缺失值的数目\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 将同款手机评论整合到一起"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "input_comment目录已经清空\n",
      "Apple_iPhone_11写入成功\n",
      "荣耀V30_PRO_李现同款写入成功\n",
      "荣耀20青春版_AMOLED屏幕指纹_4000mAh大电池写入成功\n",
      "荣耀9X_麒麟810_4000mAh续航写入成功\n",
      "【超级爆款】华为_HUAWEI_P30写入成功\n",
      "Apple_iPhone_XS写入成功\n",
      "荣耀8X_千元屏霸_91%屏占比写入成功\n",
      "荣耀20S_李现同款_3200万人像超级夜景写入成功\n",
      "华为_HUAWEI_Mate写入成功\n",
      "Redmi_Note8_4800万全场景四摄写入成功\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>回复数</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>1.000080e+11</td>\n",
       "      <td>黑色这款相当大气，*外形看起来舒服，双面玻璃，不锈钢机身，四周钢琴烤漆，握起来很有手感，也很...</td>\n",
       "      <td>0</td>\n",
       "      <td>19</td>\n",
       "      <td>50</td>\n",
       "      <td>5</td>\n",
       "      <td>5999</td>\n",
       "      <td>2020/3/4 16:30</td>\n",
       "      <td>Apple_iPhone_11</td>\n",
       "      <td>1377123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.000080e+11</td>\n",
       "      <td>这个颜色很精致，特地选的，这次很给力五-佰的京东内 部折扣，可添迦Ⅴ:号：（181-7282...</td>\n",
       "      <td>103</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>5999</td>\n",
       "      <td>2020/3/6 13:31</td>\n",
       "      <td>Apple_iPhone_11</td>\n",
       "      <td>1377123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.000080e+11</td>\n",
       "      <td>绿色的很漂亮，老婆喜欢。运行速度一流，显示效果不错，音质一流，拍照效果进步很大，尤其夜拍，很...</td>\n",
       "      <td>0</td>\n",
       "      <td>255</td>\n",
       "      <td>287</td>\n",
       "      <td>5</td>\n",
       "      <td>5999</td>\n",
       "      <td>2020/1/26 20:29</td>\n",
       "      <td>Apple_iPhone_11</td>\n",
       "      <td>1377123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.000080e+11</td>\n",
       "      <td>外形看起来舒服，双面玻璃，不锈钢机身，四周钢琴烤漆，握起来很有手感，也很大气，手感不错，有份...</td>\n",
       "      <td>201</td>\n",
       "      <td>292</td>\n",
       "      <td>198</td>\n",
       "      <td>5</td>\n",
       "      <td>5999</td>\n",
       "      <td>2020/1/5 10:54</td>\n",
       "      <td>Apple_iPhone_11</td>\n",
       "      <td>1377123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.000080e+11</td>\n",
       "      <td>手感特别好，颜色很喜欢，大小很合适，特别好看，屏幕音效也特别好，特别特别喜欢，拍照效果也特别...</td>\n",
       "      <td>201</td>\n",
       "      <td>485</td>\n",
       "      <td>116</td>\n",
       "      <td>5</td>\n",
       "      <td>5999</td>\n",
       "      <td>2019/12/15 21:26</td>\n",
       "      <td>Apple_iPhone_11</td>\n",
       "      <td>1377123</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           用户ID                                               评论内容  会员级别  点赞数  \\\n",
       "0  1.000080e+11  黑色这款相当大气，*外形看起来舒服，双面玻璃，不锈钢机身，四周钢琴烤漆，握起来很有手感，也很...     0   19   \n",
       "1  1.000080e+11  这个颜色很精致，特地选的，这次很给力五-佰的京东内 部折扣，可添迦Ⅴ:号：（181-7282...   103    2   \n",
       "2  1.000080e+11  绿色的很漂亮，老婆喜欢。运行速度一流，显示效果不错，音质一流，拍照效果进步很大，尤其夜拍，很...     0  255   \n",
       "3  1.000080e+11  外形看起来舒服，双面玻璃，不锈钢机身，四周钢琴烤漆，握起来很有手感，也很大气，手感不错，有份...   201  292   \n",
       "4  1.000080e+11  手感特别好，颜色很喜欢，大小很合适，特别好看，屏幕音效也特别好，特别特别喜欢，拍照效果也特别...   201  485   \n",
       "\n",
       "   回复数  得分    价格              购买时间             手机型号       销量  \n",
       "0   50   5  5999    2020/3/4 16:30  Apple_iPhone_11  1377123  \n",
       "1    3   5  5999    2020/3/6 13:31  Apple_iPhone_11  1377123  \n",
       "2  287   5  5999   2020/1/26 20:29  Apple_iPhone_11  1377123  \n",
       "3  198   5  5999    2020/1/5 10:54  Apple_iPhone_11  1377123  \n",
       "4  116   5  5999  2019/12/15 21:26  Apple_iPhone_11  1377123  "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import os\n",
    "import shutil\n",
    "\n",
    "path=r'input_comment'\n",
    "cnt = 0\n",
    "phone_name = ''\n",
    "\n",
    "filenames = os.listdir(path)\n",
    "\n",
    "substr = ' '\n",
    "file_name = ''\n",
    "phone_name = df.loc[0,'手机型号']\n",
    "#df.loc[0,'手机型号'] = '1'\n",
    "#存之前先清空文件夹\n",
    "try:\n",
    "    shutil.rmtree(path)\n",
    "    print(path+\"目录已经清空\")\n",
    "except:\n",
    "    print(path+\"目录不存在，进行创建\")\n",
    "os.mkdir(path)\n",
    "# filenames = os.listdir(path)\n",
    "file_name = ''\n",
    "phone_name = df.loc[0,'手机型号']\n",
    "try:\n",
    "    for index,row in df.iterrows():\n",
    "        if phone_name != row['手机型号']:\n",
    "            df.loc[index,'手机型号'] = phone_name\n",
    "            print(phone_name+\"写入成功\")\n",
    "            phone_name = row['手机型号']\n",
    "        file_name = path+os.sep+phone_name+'.txt'\n",
    "        file = open(file_name, 'a', encoding='utf-8')\n",
    "        file.write(row['评论内容'])\n",
    "        file.close()\n",
    "except Exception as e:\n",
    "    print(\"手机评论写入文件出错，原因是： \"+e)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "from collections import defaultdict\n",
    "import os\n",
    "import re\n",
    "import jieba\n",
    "import codecs\n",
    " \n",
    "def seg_word(sentence):\n",
    "    \"\"\"使用jieba对文档分词\"\"\"\n",
    "    seg_list = jieba.cut(sentence)\n",
    "    seg_result = []\n",
    "    for w in seg_list:\n",
    "        seg_result.append(w)\n",
    "    # 读取停用词文件\n",
    "    stopwords = set()\n",
    "    fr = codecs.open('motion_analysis/停用词.txt', 'r', 'utf-8')\n",
    "    for word in fr:\n",
    "        stopwords.add(word.strip())\n",
    "    fr.close()\n",
    "    # 去除停用词\n",
    "    return list(filter(lambda x: x not in stopwords, seg_result))\n",
    " \n",
    "def classify_words(word_dict):\n",
    "    \"\"\"词语分类,找出情感词、否定词、程度副词\"\"\"\n",
    "    # 读取情感字典文件\n",
    "    sen_file = open('motion_analysis//BosonNLP_sentiment_score//BosonNLP_sentiment_score.txt', 'r+', encoding='utf-8')\n",
    "    # 获取字典文件内容\n",
    "    sen_list = sen_file.readlines()\n",
    "    # 创建情感字典\n",
    "    sen_dict = defaultdict()\n",
    "    # 读取字典文件每一行内容，将其转换为字典对象，key为情感词，value为对应的分值\n",
    "    for s in sen_list:\n",
    "        # 每一行内容根据空格分割，索引0是情感词，索引01是情感分值\n",
    "        try:\n",
    "            sen_dict[s.split(' ')[0]] = s.split(' ')[1]\n",
    "        except:\n",
    "            pass\n",
    "        \n",
    " \n",
    "    # 读取否定词文件\n",
    "    not_word_file = open('motion_analysis//否定词.txt', 'r+', encoding='utf-8')\n",
    "    # 由于否定词只有词，没有分值，使用list即可\n",
    "    not_word_list = not_word_file.readlines()\n",
    " \n",
    "    # 读取程度副词文件\n",
    "    degree_file = open('motion_analysis//程度副词.txt', 'r+', encoding='utf-8')\n",
    "    degree_list = degree_file.readlines()\n",
    "    degree_dic = defaultdict()\n",
    "    # 程度副词与情感词处理方式一样，转为程度副词字典对象，key为程度副词，value为对应的程度值\n",
    "    for d in degree_list:\n",
    "        try:\n",
    "            degree_dic[d.split(',')[0]] = d.split(',')[1]\n",
    "        except:\n",
    "            pass\n",
    " \n",
    "    # 分类结果，词语的index作为key,词语的分值作为value，否定词分值设为-1\n",
    "    sen_word = dict()\n",
    "    not_word = dict()\n",
    "    degree_word = dict()\n",
    " \n",
    "    # 分类\n",
    "    for word in word_dict.keys():\n",
    "        if word in sen_dict.keys() and word not in not_word_list and word not in degree_dic.keys():\n",
    "            # 找出分词结果中在情感字典中的词\n",
    "            sen_word[word_dict[word]] = sen_dict[word]\n",
    "        elif word in not_word_list and word not in degree_dic.keys():\n",
    "            # 分词结果中在否定词列表中的词\n",
    "            not_word[word_dict[word]] = -1\n",
    "        elif word in degree_dic.keys():\n",
    "            # 分词结果中在程度副词中的词\n",
    "            degree_word[word_dict[word]] = degree_dic[word]\n",
    "    sen_file.close()\n",
    "    degree_file.close()\n",
    "    not_word_file.close()\n",
    "    # 将分类结果返回\n",
    "    return sen_word, not_word, degree_word\n",
    " \n",
    "def list_to_dict(word_list):\n",
    "    \"\"\"将分词后的列表转为字典，key为单词，value为单词在列表中的索引，索引相当于词语在文档中出现的位置\"\"\"\n",
    "    data = {}\n",
    "    for x in range(0, len(word_list)):\n",
    "        data[word_list[x]] = x\n",
    "    return data\n",
    " \n",
    "def get_init_weight(sen_word, not_word, degree_word):\n",
    "    # 权重初始化为1\n",
    "    W = 1\n",
    "    # 将情感字典的key转为list\n",
    "    sen_word_index_list = list(sen_word.keys())\n",
    "    if len(sen_word_index_list) == 0:\n",
    "        return W\n",
    "    # 获取第一个情感词的下标，遍历从0到此位置之间的所有词，找出程度词和否定词\n",
    "    for i in range(0, sen_word_index_list[0]):\n",
    "        if i in not_word.keys():\n",
    "            W *= -1\n",
    "        elif i in degree_word.keys():\n",
    "            # 更新权重，如果有程度副词，分值乘以程度副词的程度分值\n",
    "            W *= float(degree_word[i])\n",
    "    return W\n",
    " \n",
    "def socre_sentiment(sen_word, not_word, degree_word, seg_result):\n",
    "    \"\"\"计算得分\"\"\"\n",
    "    # 权重初始化为1\n",
    "    W = 1\n",
    "    score = 0\n",
    "    # 情感词下标初始化\n",
    "    sentiment_index = -1\n",
    "    # 情感词的位置下标集合\n",
    "    sentiment_index_list = list(sen_word.keys())\n",
    "    # 遍历分词结果(遍历分词结果是为了定位两个情感词之间的程度副词和否定词)\n",
    "    for i in range(0, len(seg_result)):\n",
    "        # 如果是情感词（根据下标是否在情感词分类结果中判断）\n",
    "        if i in sen_word.keys():\n",
    "            # 权重*情感词得分\n",
    "            score += W * float(sen_word[i])\n",
    "            # 情感词下标加1，获取下一个情感词的位置\n",
    "            sentiment_index += 1\n",
    "            if sentiment_index < len(sentiment_index_list) - 1:\n",
    "                # 判断当前的情感词与下一个情感词之间是否有程度副词或否定词\n",
    "                for j in range(sentiment_index_list[sentiment_index], sentiment_index_list[sentiment_index + 1]):\n",
    "                    # 更新权重，如果有否定词，取反\n",
    "                    if j in not_word.keys():\n",
    "                        W *= -1\n",
    "                    elif j in degree_word.keys():\n",
    "                        # 更新权重，如果有程度副词，分值乘以程度副词的程度分值\n",
    "                        W *= float(degree_word[j])\n",
    "        # 定位到下一个情感词\n",
    "        if sentiment_index < len(sentiment_index_list) - 1:\n",
    "            i = sentiment_index_list[sentiment_index + 1]\n",
    "    return score\n",
    " \n",
    "# 计算得分\n",
    "def setiment_score(sententce):\n",
    "    # 1.对文档分词\n",
    "    seg_list = seg_word(sententce)\n",
    "    # 2.将分词结果列表转为dic，然后找出情感词、否定词、程度副词\n",
    "    sen_word, not_word, degree_word = classify_words(list_to_dict(seg_list))\n",
    "    # 3.计算得分\n",
    "    score = socre_sentiment(sen_word, not_word, degree_word, seg_list)\n",
    "    return score\n",
    "\n",
    "read_data = ''\n",
    "\n",
    "\n",
    "# with open('input_comment//Apple iPhone 11.txt') as f:\n",
    "#     read_data=f.read()\n",
    "# 测试\n",
    "# print(setiment_score(read_data))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Building prefix dict from the default dictionary ...\n",
      "Loading model from cache C:\\Users\\ADMINI~1\\AppData\\Local\\Temp\\jieba.cache\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "开始计算得分\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Loading model cost 1.491 seconds.\n",
      "Prefix dict has been built succesfully.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "index:  Int64Index([0], dtype='int64')\n",
      "Apple_iPhone_11.txt计算完毕\n",
      "index:  Int64Index([4568], dtype='int64')\n",
      "Apple_iPhone_XR.txt计算完毕\n",
      "index:  Int64Index([2501], dtype='int64')\n",
      "Apple_iPhone_XS.txt计算完毕\n",
      "index:  Int64Index([4068], dtype='int64')\n",
      "Redmi_Note8_4800万全场景四摄.txt计算完毕\n",
      "index:  Int64Index([2001], dtype='int64')\n",
      "【超级爆款】华为_HUAWEI_P30.txt计算完毕\n",
      "index:  Int64Index([3568], dtype='int64')\n",
      "华为_HUAWEI_Mate.txt计算完毕\n",
      "index:  Int64Index([3068], dtype='int64')\n",
      "荣耀20S_李现同款_3200万人像超级夜景.txt计算完毕\n",
      "index:  Int64Index([1001], dtype='int64')\n",
      "荣耀20青春版_AMOLED屏幕指纹_4000mAh大电池.txt计算完毕\n",
      "index:  Int64Index([2874], dtype='int64')\n",
      "荣耀8X_千元屏霸_91%屏占比.txt计算完毕\n",
      "index:  Int64Index([1501], dtype='int64')\n",
      "荣耀9X_麒麟810_4000mAh续航.txt计算完毕\n",
      "index:  Int64Index([501], dtype='int64')\n",
      "荣耀V30_PRO_李现同款.txt计算完毕\n",
      "处理过后的数据前五行如下：\n",
      "              用户ID    价格                           手机型号       销量         评论得分\n",
      "0     1.000080e+11  5999                Apple_iPhone_11  1377123  1377.275364\n",
      "501   1.000100e+11  3589                 荣耀V30_PRO_李现同款   106524  1371.714201\n",
      "1001  1.000050e+11   999  荣耀20青春版_AMOLED屏幕指纹_4000mAh大电池   178754  1227.042357\n",
      "1501  1.000070e+11  1289           荣耀9X_麒麟810_4000mAh续航  1170369  1343.845960\n",
      "2001  1.000030e+11  3988            【超级爆款】华为_HUAWEI_P30   707301  1523.744014\n"
     ]
    }
   ],
   "source": [
    "print(\"开始计算得分\")\n",
    "content_path=\"input_comment\"\n",
    "filenames = os.listdir(content_path)\n",
    "df = df.drop_duplicates(subset=['手机型号'],keep='first')#手机型号去重\n",
    "df['评论得分'] = 0 #初始化自增列\n",
    "\n",
    "for filename in filenames:\n",
    "    phone_name = os.path.splitext(filename)[0]\n",
    "    file_path = content_path+os.sep+filename\n",
    "    with open(file_path,encoding='utf-8') as f:\n",
    "        read_data=f.read()\n",
    "        score = setiment_score(read_data)\n",
    "        print('index:  '+str(df.index[df['手机型号'] == phone_name]))\n",
    "        df.loc[df.index[df['手机型号'] == phone_name], '评论得分'] = score\n",
    "    print(filename+\"计算完毕\")\n",
    "\n",
    "tmp_df = df.drop(['评论内容','会员级别','点赞数','回复数','得分','购买时间'],axis=1)\n",
    "print(\"处理过后的数据前五行如下：\")\n",
    "print(tmp_df.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 数据清洗后存到数据库\n",
    "读取爬虫脚本爬的CSV文件并清洗，随后存到MySQL中"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## mysql建数据库指令\n",
    "```mysql\n",
    "mysql> create database jd_comment;\n",
    "```\n",
    "## mysql建表指令\n",
    "```mysql\n",
    "create table product_info(\n",
    "    -> ID VARCHAR(100) NOT NULL,\n",
    "    -> Name VARCHAR(200) NOT NULL,\n",
    "    -> Price INT NOT NULL,\n",
    "    -> Sales VARCHAR(50),\n",
    "    -> CommentScore INT,\n",
    "    -> PRIMARY KEY(ID)\n",
    "    -> );\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "path = r\"data//test_result.csv\"\n",
    "test_df = pd.read_csv(path,encoding='utf-8',header=0)\n",
    "test_df.columns = test_df.columns.str.strip(' ')#去除列名中的空格"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pymysql.cursors.Cursor at 0x197c7f942b0>"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pymysql\n",
    "# 连接database\n",
    "conn = pymysql.connect(host=\"localhost\", user=\"root\",password=\"\")\n",
    "# 得到一个可以执行SQL语句的光标对象\n",
    "cursor = conn.cursor()\n",
    "cursor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 一个根据pandas自动识别type来设定table的type\n",
    "def make_table_sql(df):\n",
    "    df['用户ID'] = df['用户ID'].apply(str)\n",
    "    columns = df.columns.tolist()\n",
    "    types = df.ftypes\n",
    "    # 添加id 制动递增主键模式\n",
    "    make_table = []\n",
    "    \n",
    "    for item in columns:\n",
    "        if 'int' in types[item]:\n",
    "            char = item + ' INT'\n",
    "        elif 'float' in types[item]:\n",
    "            char = item + ' FLOAT'\n",
    "        elif 'object' in types[item]:\n",
    "            char = item + ' VARCHAR(255)'           \n",
    "        elif 'datetime' in types[item]:\n",
    "            char = item + ' DATETIME'            \n",
    "        make_table.append(char)\n",
    "    print(','.join(make_table))\n",
    "    return ','.join(make_table)\n",
    "\n",
    "# csv 格式输入 mysql 中\n",
    "def csv2mysql(db_name, table_name, df):\n",
    "    # 创建database\n",
    "    cursor.execute('CREATE DATABASE IF NOT EXISTS {}'.format(db_name))\n",
    "    # 选择连接database\n",
    "    conn.select_db(db_name)\n",
    "    # 创建table\n",
    "    cursor.execute('DROP TABLE IF EXISTS {}'.format(table_name))\n",
    "    cursor.execute('CREATE TABLE {}({})'.format(table_name,make_table_sql(df)))\n",
    "    # 提取数据转list 这里有与pandas时间模式无法写入因此换成str 此时mysql上格式已经设置完成\n",
    "    #df['日期'] = df['日期'].astype('str')\n",
    "    values = df.values.tolist()\n",
    "    # 根据columns个数\n",
    "    s = ','.join(['%s' for _ in range(len(df.columns))])\n",
    "    # executemany批量操作 插入数据 批量操作比逐个操作速度快很多\n",
    "    insert_sql = 'INSERT INTO {} VALUES ({})'.format(table_name,s)\n",
    "    cursor.executemany(insert_sql, values)\n",
    "    conn.commit()\n",
    "    conn.close()\n",
    "    print(\"数据插入成功\")\n",
    "    \n",
    "#导出到数据库\n",
    "csv2mysql('jd_comment','product_info',test_df)\n",
    "#导出到csv文件\n",
    "#df.to_csv('data/result.csv',na_rep='NULL',sep = ',',index=False,header = False,encoding = 'utf_8_sig')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [default]",
   "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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}