GET_DATA.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pymysql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "db = pymysql.connect(\"***\",\"***\",\"***\",\"***\",charset='utf8')" 
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cursor = db.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "sql = \"SELECT * FROM RAW_DATA_train\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "try:\n",
    "   # 执行SQL语句\n",
    "   cursor.execute(sql)\n",
    "   # 获取所有记录列表\n",
    "   results = cursor.fetchall()\n",
    "except:\n",
    "   print (\"Error: unable to fetch data\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "martir=[]\n",
    "for row in results:\n",
    "    #print(type(row[1]))\n",
    "    label=row[1]\n",
    "    text=row[2]\n",
    "    pair=[]\n",
    "    pair.append(text)\n",
    "    pair.append(label)\n",
    "    martir.append(pair)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "mat_array=np.array(martir)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# DIS_SQL=\"SELECT DISTINCT label FROM RAW_DATA_train\"\n",
    "# try:\n",
    "#    # 执行SQL语句\n",
    "#    cursor.execute(DIS_SQL)\n",
    "#    # 获取所有记录列表\n",
    "#    label_results = cursor.fetchall()\n",
    "# except:\n",
    "#    print (\"Error: unable to fetch data\")\n",
    " \n",
    "# # 关闭数据库连接\n",
    "# db.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# mapping_dict={'exterior':'1','driving':'2','safety':'3','performance':'4','space':'5','interior':'6','fuel economy':'7'}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# for i in mat_array:\n",
    "#     i[0]=mapping_dict[i[0]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from functools import partial\n",
    "import io\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df=pd.DataFrame(mat_array)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df=df.sample(frac=1).reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df.columns = ['text','label']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "save_df=df.head(4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "save_df.to_csv('st.tsv',sep='\\t',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df['text'] = df['text'].str.replace(\"[^a-zA-Z]\", \" \")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[nltk_data] Downloading package stopwords to\n",
      "[nltk_data]     /home/I348655/nltk_data...\n",
      "[nltk_data]   Package stopwords is already up-to-date!\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import nltk\n",
    "nltk.download('stopwords')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from nltk.corpus import stopwords \n",
    "stop_words = stopwords.words('english')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# tokenization \n",
    "tokenized_doc = df['text'].apply(lambda x: x.split())\n",
    "\n",
    "# remove stop-words \n",
    "tokenized_doc = tokenized_doc.apply(lambda x: [item for item in x if item not in stop_words]) \n",
    "\n",
    "# de-tokenization \n",
    "detokenized_doc = [] \n",
    "for i in range(len(df)): \n",
    "    t = ' '.join(tokenized_doc[i]) \n",
    "    detokenized_doc.append(t) \n",
    "df['text'] = detokenized_doc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from sklearn.model_selection import train_test_split\n",
    "\n",
    "# split data into training and validation set\n",
    "df_trn, test_trn = train_test_split(df, stratify = df['label'], test_size = 0.3, random_state = 12)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>text</th>\n",
       "      <th>label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>42725</th>\n",
       "      <td>In addition proficiency cargo hauling E class ...</td>\n",
       "      <td>space</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32841</th>\n",
       "      <td>The general design regardless body style excep...</td>\n",
       "      <td>driving</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40173</th>\n",
       "      <td>EPA rated fuel economy automatic equipped four...</td>\n",
       "      <td>fuel economy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5114</th>\n",
       "      <td>You might assume CLA sedan also small trunk re...</td>\n",
       "      <td>space</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19195</th>\n",
       "      <td>Read rate cars The powertrain available QX wel...</td>\n",
       "      <td>driving</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                    text         label\n",
       "42725  In addition proficiency cargo hauling E class ...         space\n",
       "32841  The general design regardless body style excep...       driving\n",
       "40173  EPA rated fuel economy automatic equipped four...  fuel economy\n",
       "5114   You might assume CLA sedan also small trunk re...         space\n",
       "19195  Read rate cars The powertrain available QX wel...       driving"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_trn.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(70900, 2)"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_trn.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_t=df_trn[0:39630]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_v=df_trn[39631:70900]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>text</th>\n",
       "      <th>label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>42725</th>\n",
       "      <td>In addition proficiency cargo hauling E class ...</td>\n",
       "      <td>space</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32841</th>\n",
       "      <td>The general design regardless body style excep...</td>\n",
       "      <td>driving</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40173</th>\n",
       "      <td>EPA rated fuel economy automatic equipped four...</td>\n",
       "      <td>fuel economy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5114</th>\n",
       "      <td>You might assume CLA sedan also small trunk re...</td>\n",
       "      <td>space</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19195</th>\n",
       "      <td>Read rate cars The powertrain available QX wel...</td>\n",
       "      <td>driving</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                    text         label\n",
       "42725  In addition proficiency cargo hauling E class ...         space\n",
       "32841  The general design regardless body style excep...       driving\n",
       "40173  EPA rated fuel economy automatic equipped four...  fuel economy\n",
       "5114   You might assume CLA sedan also small trunk re...         space\n",
       "19195  Read rate cars The powertrain available QX wel...       driving"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_t.head(5)"
   ]
  },
  {
   "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>text</th>\n",
       "      <th>label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>94225</th>\n",
       "      <td>The big flat kilowatt hour battery pack floor ...</td>\n",
       "      <td>fuel economy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43991</th>\n",
       "      <td>In side impact testing Impala equipped side ai...</td>\n",
       "      <td>safety</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28543</th>\n",
       "      <td>Energy comes courtesy lithium ion battery pack...</td>\n",
       "      <td>fuel economy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85694</th>\n",
       "      <td>The Laramie Limited trim adds monotone paint c...</td>\n",
       "      <td>exterior</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42785</th>\n",
       "      <td>The Frontier crew cab received top rating Good...</td>\n",
       "      <td>safety</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17827</th>\n",
       "      <td>It looks like one solid piece viewed side Car ...</td>\n",
       "      <td>exterior</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78550</th>\n",
       "      <td>That frugal Ram turbodiesel top mpg heavier tr...</td>\n",
       "      <td>fuel economy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14317</th>\n",
       "      <td>Technically four cylinder models handle bit be...</td>\n",
       "      <td>driving</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86802</th>\n",
       "      <td>Just years ago Acura ILX range would near lead...</td>\n",
       "      <td>fuel economy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47704</th>\n",
       "      <td>The communicative steering enhances confidence...</td>\n",
       "      <td>driving</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                    text         label\n",
       "94225  The big flat kilowatt hour battery pack floor ...  fuel economy\n",
       "43991  In side impact testing Impala equipped side ai...        safety\n",
       "28543  Energy comes courtesy lithium ion battery pack...  fuel economy\n",
       "85694  The Laramie Limited trim adds monotone paint c...      exterior\n",
       "42785  The Frontier crew cab received top rating Good...        safety\n",
       "17827  It looks like one solid piece viewed side Car ...      exterior\n",
       "78550  That frugal Ram turbodiesel top mpg heavier tr...  fuel economy\n",
       "14317  Technically four cylinder models handle bit be...       driving\n",
       "86802  Just years ago Acura ILX range would near lead...  fuel economy\n",
       "47704  The communicative steering enhances confidence...       driving"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_v.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>text</th>\n",
       "      <th>label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>17120</th>\n",
       "      <td>The CVT lets engine slur higher rpm slightest ...</td>\n",
       "      <td>performance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38940</th>\n",
       "      <td>Optional equipment includes inflatable rear se...</td>\n",
       "      <td>safety</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24544</th>\n",
       "      <td>While cargo carrying numbers average storage s...</td>\n",
       "      <td>space</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39090</th>\n",
       "      <td>The Limited version gets upgraded leather upho...</td>\n",
       "      <td>interior</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1375</th>\n",
       "      <td>Because battery pack mounted beneath floor Bol...</td>\n",
       "      <td>fuel economy</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                    text         label\n",
       "17120  The CVT lets engine slur higher rpm slightest ...   performance\n",
       "38940  Optional equipment includes inflatable rear se...        safety\n",
       "24544  While cargo carrying numbers average storage s...         space\n",
       "39090  The Limited version gets upgraded leather upho...      interior\n",
       "1375   Because battery pack mounted beneath floor Bol...  fuel economy"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_trn.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_t.to_csv('DATA_DIR/train.tsv',sep='\\t',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "test_trn.to_csv('DATA_DIR/test.tsv',sep='\\t',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_v.to_csv('DATA_DIR/dev.tsv',sep='\\t',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}