2.0 - Tabular analysis.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import jieba\n",
    "from sklearn.feature_extraction.text import TfidfVectorizer, CountVectorizer\n",
    "from sklearn.decomposition import LatentDirichletAllocation\n",
    "import datetime\n",
    "\n",
    "from sklearn import datasets, linear_model\n",
    "from sklearn.metrics import mean_squared_error, r2_score\n",
    "\n",
    "from statsmodels.formula.api  import ols\n",
    "import statsmodels.api as sm\n",
    "from sklearn.model_selection import train_test_split\n",
    "\n",
    "from keras.models import Sequential\n",
    "from keras.layers import Dense\n",
    "from keras import layers\n",
    "from keras.wrappers.scikit_learn import KerasRegressor\n",
    "from sklearn.model_selection import cross_val_score\n",
    "from sklearn.model_selection import KFold\n",
    "from sklearn.preprocessing import StandardScaler\n",
    "from sklearn.pipeline import Pipeline\n",
    "\n",
    "from sklearn.linear_model import LinearRegression\n",
    "from sklearn import metrics\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "#Given the lack of computing resources, this analysis takes 100,000 articles as an example. \n",
    "\n",
    "topicdata = pd.read_csv(r'/Users/cairo/Google Drive/wechat data/TopicOutcomeAll20Topic.csv', nrows = 100000,sep=',', error_bad_lines=False, index_col=False, dtype='unicode')\n",
    "#topicdata = pd.read_csv(r'C:/Users/Junhao/Google Drive/wechat data/TopicOutcomeAll20Topic.csv', sep=',', error_bad_lines=False, index_col=False, dtype='unicode')\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>name</th>\n",
       "      <th>account</th>\n",
       "      <th>author</th>\n",
       "      <th>type</th>\n",
       "      <th>title</th>\n",
       "      <th>summary</th>\n",
       "      <th>likeCount</th>\n",
       "      <th>clicksCount</th>\n",
       "      <th>url</th>\n",
       "      <th>publicTime</th>\n",
       "      <th>...</th>\n",
       "      <th>topic10</th>\n",
       "      <th>topic11</th>\n",
       "      <th>topic12</th>\n",
       "      <th>topic13</th>\n",
       "      <th>topic14</th>\n",
       "      <th>topic15</th>\n",
       "      <th>topic16</th>\n",
       "      <th>topic17</th>\n",
       "      <th>topic18</th>\n",
       "      <th>topic19</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>南京吃喝玩乐</td>\n",
       "      <td>happyinnanjing</td>\n",
       "      <td>南京吃喝玩乐</td>\n",
       "      <td>民生</td>\n",
       "      <td>住宾馆时，怎么知道房间有没有针孔摄像头？</td>\n",
       "      <td>近日，有网友爆料：在入住的宾馆里换拖鞋时，在正对着床的沙发上居然发现了一个针孔摄像头！不仔细...</td>\n",
       "      <td>175</td>\n",
       "      <td>27784</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MjM5NTkwNTI2MQ...</td>\n",
       "      <td>2015-01-26 12:55:11</td>\n",
       "      <td>...</td>\n",
       "      <td>40.71615</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.05</td>\n",
       "      <td>46.502632</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>正商阅读</td>\n",
       "      <td>zsyd88</td>\n",
       "      <td>政商阅读</td>\n",
       "      <td>财富</td>\n",
       "      <td>千年至理：爱子七不责</td>\n",
       "      <td>﻿点击上面蓝字“政商阅读”免费关注广受推崇的微信公众号。微信号：zsyd881、对众不责在大...</td>\n",
       "      <td>139</td>\n",
       "      <td>56752</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MjM5MjEwNzE2NA...</td>\n",
       "      <td>2015-01-24 19:02:07</td>\n",
       "      <td>...</td>\n",
       "      <td>0.05</td>\n",
       "      <td>23.704185</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>南京吃喝玩乐</td>\n",
       "      <td>happyinnanjing</td>\n",
       "      <td>南京吃喝玩乐</td>\n",
       "      <td>民生</td>\n",
       "      <td>▶太恶心了，吃火锅前一定要注意，速看！</td>\n",
       "      <td>速看~</td>\n",
       "      <td>113</td>\n",
       "      <td>20763</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MjM5NTkwNTI2MQ...</td>\n",
       "      <td>2015-01-26 12:55:11</td>\n",
       "      <td>...</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.05</td>\n",
       "      <td>10.045543</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>国学精粹与生活艺术</td>\n",
       "      <td>gxjhshys</td>\n",
       "      <td>国学精粹与生活艺术</td>\n",
       "      <td>文化</td>\n",
       "      <td>小时候，你这样二过吗？百分百中枪有木有！</td>\n",
       "      <td>点上方国学精粹与生活艺术免费快速订阅；国学精粹与生活艺术 主编私人微信gxjcyshys这里...</td>\n",
       "      <td>454</td>\n",
       "      <td>87840</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MzA4OTAzNjMzMQ...</td>\n",
       "      <td>2015-01-18 08:34:10</td>\n",
       "      <td>...</td>\n",
       "      <td>0.05</td>\n",
       "      <td>44.87632</td>\n",
       "      <td>0.05</td>\n",
       "      <td>9.980097</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.05</td>\n",
       "      <td>20.219387</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>南京吃喝玩乐</td>\n",
       "      <td>happyinnanjing</td>\n",
       "      <td>南京吃喝玩乐</td>\n",
       "      <td>民生</td>\n",
       "      <td>太有用了！看到第二个我就收了!</td>\n",
       "      <td>一. 放个苹果，土豆不发芽二. 用保鲜膜包住根部，可延长香蕉的保存时间三 切水果大全。。。1...</td>\n",
       "      <td>161</td>\n",
       "      <td>17728</td>\n",
       "      <td>http://mp.weixin.qq.com/s?__biz=MjM5NTkwNTI2MQ...</td>\n",
       "      <td>2015-01-27 21:38:15</td>\n",
       "      <td>...</td>\n",
       "      <td>3.7585173</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.05</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 39 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        name         account     author type                 title  \\\n",
       "0     南京吃喝玩乐  happyinnanjing     南京吃喝玩乐   民生  住宾馆时，怎么知道房间有没有针孔摄像头？   \n",
       "1       正商阅读          zsyd88       政商阅读   财富            千年至理：爱子七不责   \n",
       "2     南京吃喝玩乐  happyinnanjing     南京吃喝玩乐   民生   ▶太恶心了，吃火锅前一定要注意，速看！   \n",
       "3  国学精粹与生活艺术        gxjhshys  国学精粹与生活艺术   文化  小时候，你这样二过吗？百分百中枪有木有！   \n",
       "4     南京吃喝玩乐  happyinnanjing     南京吃喝玩乐   民生       太有用了！看到第二个我就收了!   \n",
       "\n",
       "                                             summary likeCount clicksCount  \\\n",
       "0  近日，有网友爆料：在入住的宾馆里换拖鞋时，在正对着床的沙发上居然发现了一个针孔摄像头！不仔细...       175       27784   \n",
       "1  ﻿点击上面蓝字“政商阅读”免费关注广受推崇的微信公众号。微信号：zsyd881、对众不责在大...       139       56752   \n",
       "2                                                速看~       113       20763   \n",
       "3  点上方国学精粹与生活艺术免费快速订阅；国学精粹与生活艺术 主编私人微信gxjcyshys这里...       454       87840   \n",
       "4  一. 放个苹果，土豆不发芽二. 用保鲜膜包住根部，可延长香蕉的保存时间三 切水果大全。。。1...       161       17728   \n",
       "\n",
       "                                                 url           publicTime  \\\n",
       "0  http://mp.weixin.qq.com/s?__biz=MjM5NTkwNTI2MQ...  2015-01-26 12:55:11   \n",
       "1  http://mp.weixin.qq.com/s?__biz=MjM5MjEwNzE2NA...  2015-01-24 19:02:07   \n",
       "2  http://mp.weixin.qq.com/s?__biz=MjM5NTkwNTI2MQ...  2015-01-26 12:55:11   \n",
       "3  http://mp.weixin.qq.com/s?__biz=MzA4OTAzNjMzMQ...  2015-01-18 08:34:10   \n",
       "4  http://mp.weixin.qq.com/s?__biz=MjM5NTkwNTI2MQ...  2015-01-27 21:38:15   \n",
       "\n",
       "   ...    topic10    topic11 topic12   topic13    topic14 topic15    topic16  \\\n",
       "0  ...   40.71615       0.05    0.05      0.05  46.502632    0.05       0.05   \n",
       "1  ...       0.05  23.704185    0.05      0.05       0.05    0.05       0.05   \n",
       "2  ...       0.05       0.05    0.05      0.05  10.045543    0.05       0.05   \n",
       "3  ...       0.05   44.87632    0.05  9.980097       0.05    0.05  20.219387   \n",
       "4  ...  3.7585173       0.05    0.05      0.05       0.05    0.05       0.05   \n",
       "\n",
       "  topic17 topic18 topic19  \n",
       "0    0.05    0.05    0.05  \n",
       "1    0.05    0.05    0.05  \n",
       "2    0.05    0.05    0.05  \n",
       "3    0.05    0.05    0.05  \n",
       "4    0.05    0.05    0.05  \n",
       "\n",
       "[5 rows x 39 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "topicdata.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "87"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "topicdata['name'].nunique() #There are 87 unique accounts in the dataset "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "\n",
    "###############################################################################################\n",
    "#prepare data\n",
    "\n",
    "\n",
    "\n",
    "y = topicdata.likeCount #use likeCount as the dependent variable\n",
    "\n",
    "#X = topicdata[topicdata.columns[-30:]]\n",
    "X0 = topicdata.iloc[:,-20:]\n",
    "X1 = topicdata[[\"clicksCount\", \"orderNum\", \"originalFlag\"]]\n",
    "\n",
    "X = pd.concat([X0, X1], axis=1)\n",
    "X = sm.add_constant(X) #add a intercept term to the regression\n",
    "#X.reset_index(drop=True)\n",
    "\n",
    "#X.index = pd.RangeIndex(len(X.index))\n",
    "\n",
    "X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=123)\n",
    "X_test = X_test.astype(float)\n",
    "X_train = X_train.astype(float)\n",
    "y_train = y_train.astype(float)\n",
    "y_test = y_test.astype(float)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"simpletable\">\n",
       "<caption>OLS Regression Results</caption>\n",
       "<tr>\n",
       "  <th>Dep. Variable:</th>        <td>likeCount</td>    <th>  R-squared:         </th>  <td>   0.328</td>  \n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Model:</th>                   <td>OLS</td>       <th>  Adj. R-squared:    </th>  <td>   0.328</td>  \n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Method:</th>             <td>Least Squares</td>  <th>  F-statistic:       </th>  <td>   2125.</td>  \n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Date:</th>             <td>Tue, 28 Sep 2021</td> <th>  Prob (F-statistic):</th>   <td>  0.00</td>   \n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Time:</th>                 <td>13:31:58</td>     <th>  Log-Likelihood:    </th> <td>-8.0027e+05</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>No. Observations:</th>      <td>100000</td>      <th>  AIC:               </th>  <td>1.601e+06</td> \n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Df Residuals:</th>          <td> 99976</td>      <th>  BIC:               </th>  <td>1.601e+06</td> \n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Df Model:</th>              <td>    23</td>      <th>                     </th>      <td> </td>     \n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Covariance Type:</th>      <td>nonrobust</td>    <th>                     </th>      <td> </td>     \n",
       "</tr>\n",
       "</table>"
      ],
      "text/plain": [
       "<class 'statsmodels.iolib.table.SimpleTable'>"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "##################################\n",
    "#linear regression\n",
    "##################################\n",
    "\n",
    "\n",
    "###############################################################################################\n",
    "#linear regression with all variables\n",
    "results = sm.OLS(y.astype(float), X.astype(float)).fit()\n",
    "results.summary().tables[0]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "611346.3293772824"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lr = LinearRegression()\n",
    "lr.fit(X_train, y_train)\n",
    "y_pred = lr.predict(X_test)\n",
    "\n",
    "mse_lr=metrics.mean_squared_error(y_test, y_pred)\n",
    "print(\"LR MSE: %f\" % (mse_lr))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/cairo/opt/anaconda3/lib/python3.8/site-packages/pandas/core/generic.py:5494: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  self[name] = value\n",
      "<ipython-input-14-db000c77b219>:8: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df[\"name2\"] = df.name.cat.codes\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                            OLS Regression Results                            \n",
      "==============================================================================\n",
      "Dep. Variable:              likeCount   R-squared:                       0.472\n",
      "Model:                            OLS   Adj. R-squared:                  0.471\n",
      "Method:                 Least Squares   F-statistic:                     939.0\n",
      "Date:                Tue, 28 Sep 2021   Prob (F-statistic):               0.00\n",
      "Time:                        15:53:40   Log-Likelihood:            -7.8827e+05\n",
      "No. Observations:              100000   AIC:                         1.577e+06\n",
      "Df Residuals:                   99904   BIC:                         1.578e+06\n",
      "Df Model:                          95                                         \n",
      "Covariance Type:            nonrobust                                         \n",
      "=====================================================================================\n",
      "                        coef    std err          t      P>|t|      [0.025      0.975]\n",
      "-------------------------------------------------------------------------------------\n",
      "Intercept           -43.4264     11.814     -3.676      0.000     -66.581     -20.271\n",
      "orderNum[T.1]      -105.4328      6.696    -15.746      0.000    -118.557     -92.309\n",
      "orderNum[T.2]      -105.5126      7.208    -14.639      0.000    -119.639     -91.386\n",
      "orderNum[T.3]       -91.3210      7.939    -11.502      0.000    -106.882     -75.760\n",
      "orderNum[T.4]       -94.9373      8.738    -10.865      0.000    -112.063     -77.812\n",
      "orderNum[T.5]      -103.9682      9.655    -10.768      0.000    -122.893     -85.044\n",
      "orderNum[T.6]      -133.5094     10.695    -12.484      0.000    -154.471    -112.548\n",
      "orderNum[T.7]      -131.3870     11.453    -11.472      0.000    -153.834    -108.940\n",
      "originalFlag[T.1]   129.7244      7.095     18.285      0.000     115.819     143.630\n",
      "C(name2)[T.1]       -70.0508     22.059     -3.176      0.001    -113.287     -26.815\n",
      "C(name2)[T.2]        58.1168     16.134      3.602      0.000      26.494      89.739\n",
      "C(name2)[T.3]        15.3181     23.292      0.658      0.511     -30.334      60.970\n",
      "C(name2)[T.4]         0.2559     15.914      0.016      0.987     -30.936      31.448\n",
      "C(name2)[T.5]      -269.5209     48.239     -5.587      0.000    -364.068    -174.974\n",
      "C(name2)[T.6]       139.1999     18.322      7.597      0.000     103.288     175.111\n",
      "C(name2)[T.7]       106.5139     15.634      6.813      0.000      75.871     137.157\n",
      "C(name2)[T.8]       112.8537     16.834      6.704      0.000      79.860     145.848\n",
      "C(name2)[T.9]       292.7609     17.083     17.137      0.000     259.278     326.244\n",
      "C(name2)[T.10]      -86.6290     22.378     -3.871      0.000    -130.489     -42.769\n",
      "C(name2)[T.11]     1804.3046    126.381     14.277      0.000    1556.599    2052.010\n",
      "C(name2)[T.12]     -738.6821     26.535    -27.838      0.000    -790.691    -686.673\n",
      "C(name2)[T.13]     -166.9405     27.542     -6.061      0.000    -220.923    -112.958\n",
      "C(name2)[T.14]     -102.7231     61.192     -1.679      0.093    -222.659      17.212\n",
      "C(name2)[T.15]      533.8434     35.114     15.203      0.000     465.021     602.666\n",
      "C(name2)[T.16]       56.9194     62.332      0.913      0.361     -65.250     179.089\n",
      "C(name2)[T.17]       -3.8037     17.537     -0.217      0.828     -38.176      30.569\n",
      "C(name2)[T.18]      132.1860     20.040      6.596      0.000      92.907     171.465\n",
      "C(name2)[T.19]      -45.8203     15.589     -2.939      0.003     -76.375     -15.265\n",
      "C(name2)[T.20]     -172.5460     56.265     -3.067      0.002    -282.824     -62.268\n",
      "C(name2)[T.21]       27.7982     19.217      1.447      0.148      -9.866      65.463\n",
      "C(name2)[T.22]      -35.0491     18.307     -1.915      0.056     -70.931       0.833\n",
      "C(name2)[T.23]     2132.1895     43.399     49.130      0.000    2047.128    2217.251\n",
      "C(name2)[T.24]      578.4731    143.930      4.019      0.000     296.372     860.574\n",
      "C(name2)[T.25]      304.3759     23.149     13.149      0.000     259.004     349.747\n",
      "C(name2)[T.26]       73.4199     63.217      1.161      0.245     -50.485     197.325\n",
      "C(name2)[T.27]       85.7388     68.199      1.257      0.209     -47.930     219.408\n",
      "C(name2)[T.28]        3.6036     14.798      0.244      0.808     -25.401      32.608\n",
      "C(name2)[T.29]       24.1953     15.600      1.551      0.121      -6.381      54.772\n",
      "C(name2)[T.30]     -103.7919     14.634     -7.092      0.000    -132.475     -75.109\n",
      "C(name2)[T.31]     -505.9524     27.614    -18.322      0.000    -560.076    -451.829\n",
      "C(name2)[T.32]       35.5487     19.102      1.861      0.063      -1.891      72.988\n",
      "C(name2)[T.33]        3.2577    156.054      0.021      0.983    -302.607     309.122\n",
      "C(name2)[T.34]     -158.1947     16.746     -9.447      0.000    -191.017    -125.373\n",
      "C(name2)[T.35]      470.1987     37.393     12.574      0.000     396.908     543.489\n",
      "C(name2)[T.36]      -77.6347     16.900     -4.594      0.000    -110.758     -44.511\n",
      "C(name2)[T.37]      166.1460     24.326      6.830      0.000     118.467     213.825\n",
      "C(name2)[T.38]      106.6200     29.259      3.644      0.000      49.272     163.968\n",
      "C(name2)[T.39]     1606.3754     46.844     34.292      0.000    1514.563    1698.188\n",
      "C(name2)[T.40]      415.5094    123.928      3.353      0.001     172.611     658.408\n",
      "C(name2)[T.41]        5.7470     19.197      0.299      0.765     -31.880      43.374\n",
      "C(name2)[T.42]      128.0350     28.013      4.570      0.000      73.129     182.941\n",
      "C(name2)[T.43]     -133.4855     18.035     -7.402      0.000    -168.834     -98.137\n",
      "C(name2)[T.44]     -120.9578    128.785     -0.939      0.348    -373.375     131.459\n",
      "C(name2)[T.45]       84.6250     35.298      2.397      0.017      15.441     153.809\n",
      "C(name2)[T.46]     1493.0746     19.061     78.333      0.000    1455.716    1530.433\n",
      "C(name2)[T.47]      -22.2071     21.873     -1.015      0.310     -65.078      20.664\n",
      "C(name2)[T.48]      -62.0669     19.848     -3.127      0.002    -100.969     -23.165\n",
      "C(name2)[T.49]       79.0051     34.493      2.290      0.022      11.398     146.612\n",
      "C(name2)[T.50]      604.0227     40.978     14.740      0.000     523.707     684.339\n",
      "C(name2)[T.51]      109.5788     57.412      1.909      0.056      -2.949     222.107\n",
      "C(name2)[T.52]       57.0573     31.781      1.795      0.073      -5.232     119.347\n",
      "C(name2)[T.53]      -42.9741     13.990     -3.072      0.002     -70.394     -15.554\n",
      "C(name2)[T.54]      508.0716     17.840     28.480      0.000     473.106     543.037\n",
      "C(name2)[T.55]     -136.1352     21.032     -6.473      0.000    -177.358     -94.913\n",
      "C(name2)[T.56]     -177.0021     19.707     -8.982      0.000    -215.628    -138.377\n",
      "C(name2)[T.57]        7.2616     28.913      0.251      0.802     -49.408      63.931\n",
      "C(name2)[T.58]      -84.6742     32.482     -2.607      0.009    -148.338     -21.011\n",
      "C(name2)[T.59]      -85.4442     16.360     -5.223      0.000    -117.510     -53.378\n",
      "C(name2)[T.60]       25.2280     23.949      1.053      0.292     -21.712      72.168\n",
      "C(name2)[T.61]       96.9839     22.073      4.394      0.000      53.721     140.247\n",
      "C(name2)[T.62]        0.4098     49.120      0.008      0.993     -95.864      96.684\n",
      "C(name2)[T.63]      -98.0796     56.151     -1.747      0.081    -208.135      11.976\n",
      "C(name2)[T.64]      151.6430     24.687      6.143      0.000     103.258     200.028\n",
      "C(name2)[T.65]       87.7404     41.469      2.116      0.034       6.462     169.019\n",
      "C(name2)[T.66]      187.8552     64.702      2.903      0.004      61.041     314.670\n",
      "C(name2)[T.67]      924.5797     30.169     30.647      0.000     865.450     983.710\n",
      "C(name2)[T.68]      -62.0739     22.407     -2.770      0.006    -105.991     -18.156\n",
      "C(name2)[T.69]      -32.8939     54.128     -0.608      0.543    -138.983      73.195\n",
      "C(name2)[T.70]       88.3322     82.209      1.074      0.283     -72.796     249.461\n",
      "C(name2)[T.71]       40.9031     54.293      0.753      0.451     -65.510     147.316\n",
      "C(name2)[T.72]       65.9150     51.455      1.281      0.200     -34.936     166.766\n",
      "C(name2)[T.73]     1394.5048     17.186     81.143      0.000    1360.821    1428.189\n",
      "C(name2)[T.74]     1481.5211     60.357     24.546      0.000    1363.221    1599.821\n",
      "C(name2)[T.75]       86.6276     18.560      4.667      0.000      50.250     123.005\n",
      "C(name2)[T.76]      -63.4990     30.927     -2.053      0.040    -124.116      -2.882\n",
      "C(name2)[T.77]     -282.1118     26.537    -10.631      0.000    -334.124    -230.099\n",
      "C(name2)[T.78]       12.0622     30.812      0.391      0.695     -48.328      72.453\n",
      "C(name2)[T.79]      201.5351     19.204     10.494      0.000     163.896     239.175\n",
      "C(name2)[T.80]      159.1562     24.334      6.541      0.000     111.463     206.850\n",
      "C(name2)[T.81]     3075.8590     50.805     60.542      0.000    2976.282    3175.436\n",
      "C(name2)[T.82]       36.9158     16.577      2.227      0.026       4.424      69.407\n",
      "C(name2)[T.83]      -20.0744     14.534     -1.381      0.167     -48.561       8.412\n",
      "C(name2)[T.84]        4.8410     52.603      0.092      0.927     -98.260     107.942\n",
      "C(name2)[T.85]      470.6564     55.667      8.455      0.000     361.550     579.763\n",
      "C(name2)[T.86]      199.5679     43.820      4.554      0.000     113.682     285.454\n",
      "clicksCount           0.0110   9.88e-05    111.562      0.000       0.011       0.011\n",
      "==============================================================================\n",
      "Omnibus:                   194635.126   Durbin-Watson:                   1.968\n",
      "Prob(Omnibus):                  0.000   Jarque-Bera (JB):       1405797539.945\n",
      "Skew:                          14.988   Prob(JB):                         0.00\n",
      "Kurtosis:                     583.080   Cond. No.                     3.74e+06\n",
      "==============================================================================\n",
      "\n",
      "Notes:\n",
      "[1] Standard Errors assume that the covariance matrix of the errors is correctly specified.\n",
      "[2] The condition number is large, 3.74e+06. This might indicate that there are\n",
      "strong multicollinearity or other numerical problems.\n"
     ]
    }
   ],
   "source": [
    "#linear regression controlling the fixed effect of influencer accounts \n",
    "\n",
    "import statsmodels.formula.api as smf\n",
    "#df = topicdata[[\"clicksCount\", \"orderNum\", \"originalFlag\", \"name\", \"likeCount\"]]\n",
    "\n",
    "df = topicdata[[\"likeCount\",\"clicksCount\", \"orderNum\", \"originalFlag\", \"name\"]]\n",
    "df.name = pd.Categorical(df.name)\n",
    "df[\"name2\"] = df.name.cat.codes\n",
    "df.clicksCount = pd.to_numeric(df.clicksCount)\n",
    "df.orderNum = pd.Categorical(df.orderNum)\n",
    "df.originalFlag = pd.Categorical(df.originalFlag)\n",
    "df.likeCount = pd.to_numeric(df.likeCount)\n",
    "\n",
    "#df = df.set_index([\"name2\"], append = True)\n",
    "\n",
    "\n",
    "FE_ols = smf.ols(formula= \"likeCount ~ 1 + orderNum + clicksCount + originalFlag + C(name2)\", data = df).fit()\n",
    "print(FE_ols.summary())\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[15:53:53] WARNING: /opt/concourse/worker/volumes/live/7a2b9f41-3287-451b-6691-43e9a6c0910f/volume/xgboost-split_1619728204606/work/src/objective/regression_obj.cu:170: reg:linear is now deprecated in favor of reg:squarederror.\n",
      "Xgboost MSE: 715101.362978\n"
     ]
    }
   ],
   "source": [
    "###############################################################################################\n",
    "#xgboost regression\n",
    "from xgboost.sklearn import XGBRegressor\n",
    "\n",
    "xg_reg = XGBRegressor(objective ='reg:linear', colsample_bytree = 0.3, learning_rate = 0.1,\n",
    "                max_depth = 5, alpha = 10, n_estimators = 10)\n",
    "\n",
    "xg_reg.fit(X_train.astype(float),y_train.astype(float))\n",
    "\n",
    "preds = xg_reg.predict(X_test.astype(float))\n",
    "\n",
    "xgb_mse = mean_squared_error(y_test.astype(float), preds)\n",
    "print(\"Xgboost MSE: %f\" % (xgb_mse))\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Decision Tree MSE: 686433.536950\n",
      "Random Forest MSE: 453434.303623\n"
     ]
    }
   ],
   "source": [
    "###########################################################\n",
    "#regression with decision trees and random forest\n",
    "\n",
    "from sklearn.tree import DecisionTreeRegressor\n",
    "\n",
    "dt_regressor = DecisionTreeRegressor(random_state = 0)\n",
    "dt_regressor.fit(X_train,y_train)\n",
    "#Predicting using test set \n",
    "y_pred = dt_regressor.predict(X_test)\n",
    "\n",
    "dt_mse=metrics.mean_squared_error(y_test, y_pred)\n",
    "\n",
    "print(\"Decision Tree MSE: %f\" % (dt_mse))\n",
    "\n",
    "\n",
    "from sklearn.ensemble import RandomForestRegressor\n",
    "\n",
    "rf_regressor = RandomForestRegressor(n_estimators = 300 ,  random_state = 0)\n",
    "rf_regressor.fit(X_train,y_train)\n",
    "#Predicting the SalePrices using test set \n",
    "y_pred = rf_regressor.predict(X_test)\n",
    "\n",
    "rf_mse=metrics.mean_squared_error(y_test, y_pred)\n",
    "print(\"Random Forest MSE: %f\" % (rf_mse))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SVM MSE: 777954.776443\n"
     ]
    }
   ],
   "source": [
    "#SVM models\n",
    "from sklearn.svm import SVR\n",
    "\n",
    "from sklearn.preprocessing import MinMaxScaler\n",
    "scaling = MinMaxScaler(feature_range=(-1,1)).fit(X_train)\n",
    "X_train = scaling.transform(X_train)\n",
    "X_test = scaling.transform(X_test)\n",
    "\n",
    "\n",
    "regressor= SVR(kernel='linear')\n",
    "regressor.fit(X_train,y_train)\n",
    "y_pred_svm=regressor.predict(X_test)\n",
    "#y_pred_svm = cross_val_predict(regressor, x, y)\n",
    "\n",
    "svm_mse=metrics.mean_squared_error(y_test, y_pred_svm)\n",
    "print(\"SVM MSE: %f\" % (svm_mse))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Model: \"sequential\"\n",
      "_________________________________________________________________\n",
      "Layer (type)                 Output Shape              Param #   \n",
      "=================================================================\n",
      "dense (Dense)                (None, 10)                250       \n",
      "_________________________________________________________________\n",
      "dense_1 (Dense)              (None, 64)                704       \n",
      "_________________________________________________________________\n",
      "dense_2 (Dense)              (None, 1)                 65        \n",
      "=================================================================\n",
      "Total params: 1,019\n",
      "Trainable params: 1,019\n",
      "Non-trainable params: 0\n",
      "_________________________________________________________________\n"
     ]
    }
   ],
   "source": [
    "###############################################################################################\n",
    "#######################\n",
    "#regression in neural networks with keras\n",
    "######################\n",
    "input_dim = X_train.shape[1]  # Number of features\n",
    "\n",
    "model = Sequential()\n",
    "model.add(layers.Dense(10, input_dim=input_dim, activation='relu'))\n",
    "model.add(layers.Dense(64, input_dim=input_dim, activation='relu'))\n",
    "model.add(layers.Dense(1, activation='relu'))\n",
    "\n",
    "model.compile(loss='mean_squared_error', \n",
    "              optimizer='adam',\n",
    "              metrics=[\"mean_squared_error\",'mean_absolute_percentage_error']\n",
    "              \n",
    "              )\n",
    "model.summary()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Epoch 1/10\n",
      "800/800 [==============================] - 1s 1ms/step - loss: 524136.7188 - mean_squared_error: 524136.7188 - mean_absolute_percentage_error: 321233856.0000 - val_loss: 621202.5625 - val_mean_squared_error: 621202.5625 - val_mean_absolute_percentage_error: 60218488.0000\n",
      "Epoch 2/10\n",
      "800/800 [==============================] - 1s 1ms/step - loss: 502208.5000 - mean_squared_error: 502208.5000 - mean_absolute_percentage_error: 115328560.0000 - val_loss: 601806.5000 - val_mean_squared_error: 601806.5000 - val_mean_absolute_percentage_error: 81940800.0000\n",
      "Epoch 3/10\n",
      "800/800 [==============================] - 1s 2ms/step - loss: 492867.2812 - mean_squared_error: 492867.2812 - mean_absolute_percentage_error: 91606432.0000 - val_loss: 595568.8750 - val_mean_squared_error: 595568.8750 - val_mean_absolute_percentage_error: 68541120.0000\n",
      "Epoch 4/10\n",
      "800/800 [==============================] - 1s 1ms/step - loss: 487789.7812 - mean_squared_error: 487789.7812 - mean_absolute_percentage_error: 89258528.0000 - val_loss: 592880.7500 - val_mean_squared_error: 592880.7500 - val_mean_absolute_percentage_error: 22224838.0000\n",
      "Epoch 5/10\n",
      "800/800 [==============================] - 1s 1ms/step - loss: 484833.0312 - mean_squared_error: 484833.0312 - mean_absolute_percentage_error: 101965624.0000 - val_loss: 589805.2500 - val_mean_squared_error: 589805.2500 - val_mean_absolute_percentage_error: 34061540.0000\n",
      "Epoch 6/10\n",
      "800/800 [==============================] - 1s 1ms/step - loss: 483010.5000 - mean_squared_error: 483010.5000 - mean_absolute_percentage_error: 96940632.0000 - val_loss: 587976.0000 - val_mean_squared_error: 587976.0000 - val_mean_absolute_percentage_error: 99132872.0000\n",
      "Epoch 7/10\n",
      "800/800 [==============================] - 1s 1ms/step - loss: 480969.8438 - mean_squared_error: 480969.8438 - mean_absolute_percentage_error: 105283376.0000 - val_loss: 586863.5625 - val_mean_squared_error: 586863.5625 - val_mean_absolute_percentage_error: 60258228.0000\n",
      "Epoch 8/10\n",
      "800/800 [==============================] - 1s 1ms/step - loss: 479018.1875 - mean_squared_error: 479018.1875 - mean_absolute_percentage_error: 106712048.0000 - val_loss: 592036.3750 - val_mean_squared_error: 592036.3750 - val_mean_absolute_percentage_error: 362878112.0000\n",
      "Epoch 9/10\n",
      "800/800 [==============================] - 1s 1ms/step - loss: 477598.0938 - mean_squared_error: 477598.0938 - mean_absolute_percentage_error: 111586328.0000 - val_loss: 581509.1875 - val_mean_squared_error: 581509.1875 - val_mean_absolute_percentage_error: 186562112.0000\n",
      "Epoch 10/10\n",
      "800/800 [==============================] - 1s 1ms/step - loss: 476131.2188 - mean_squared_error: 476131.2188 - mean_absolute_percentage_error: 104926008.0000 - val_loss: 586456.5000 - val_mean_squared_error: 586456.5000 - val_mean_absolute_percentage_error: 136815568.0000\n"
     ]
    }
   ],
   "source": [
    "history = model.fit(X_train , y_train,\n",
    "                    epochs=10,\n",
    "                    verbose=True,\n",
    "                    #steps_per_epoch=5,            \n",
    "                    validation_data=(X_test, y_test),\n",
    "                    batch_size=100\n",
    "                    )\n",
    "\n",
    "hist = pd.DataFrame(history.history)\n",
    "hist['epoch'] = history.epoch\n",
    "hist.tail()\n",
    "\n",
    "ann_mse = history.history['mean_squared_error'][-1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>Testing MSE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Linear Regression</th>\n",
       "      <td>611346.329377</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>XGBoost</th>\n",
       "      <td>715101.362978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Decision Trees</th>\n",
       "      <td>686433.536950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Random Forest</th>\n",
       "      <td>453434.303623</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SVM</th>\n",
       "      <td>777954.776443</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Deep learning neural network</th>\n",
       "      <td>476131.218750</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                Testing MSE\n",
       "Linear Regression             611346.329377\n",
       "XGBoost                       715101.362978\n",
       "Decision Trees                686433.536950\n",
       "Random Forest                 453434.303623\n",
       "SVM                           777954.776443\n",
       "Deep learning neural network  476131.218750"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#compare all MSE in a table\n",
    "\n",
    "MSE=[mse_lr,xgb_mse,dt_mse,rf_mse,svm_mse, ann_mse]\n",
    "\n",
    "col={'Testing MSE':MSE}\n",
    "models=['Linear Regression','XGBoost','Decision Trees','Random Forest','SVM', \"Deep learning neural network\"]\n",
    "df=pd.DataFrame(data=col,index=models)\n",
    "df"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}