openrefine_pythonapi_demo.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Start OpenRefine service on MyBinder"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import subprocess\n",
    "import time\n",
    "subprocess.call(\"./start_refine.sh\",shell=True)\n",
    "\n",
    "#wait for 10 seconds\n",
    "time.sleep(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "from google.refine import refine\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Check OpenRefine Server connection\n",
    "\n",
    "Check connection and create an object for open refine server transaction: refine_server"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{u'full_name': u'OpenRefine 3.0 [TRUNK]',\n",
       " u'full_version': u'3.0 [TRUNK]',\n",
       " u'revision': u'TRUNK',\n",
       " u'version': u'3.0'}"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "refine.RefineServer().get_version()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "refine_server = refine.Refine(refine.RefineServer())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "def list_project(refine_server):\n",
    "    return pd.DataFrame(refine_server.list_projects()).transpose().reset_index().rename({\"index\":\"ProjectId\"},axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def search_project(refine_server,project_name):\n",
    "    t = list_project(refine_server)\n",
    "    if t.shape[0]>0:\n",
    "        t = t[t.name==project_name]\n",
    "    return t"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>ProjectId</th>\n",
       "      <th>contributors</th>\n",
       "      <th>created</th>\n",
       "      <th>creator</th>\n",
       "      <th>customMetadata</th>\n",
       "      <th>description</th>\n",
       "      <th>homepage</th>\n",
       "      <th>image</th>\n",
       "      <th>importOptionMetadata</th>\n",
       "      <th>license</th>\n",
       "      <th>modified</th>\n",
       "      <th>name</th>\n",
       "      <th>rowCount</th>\n",
       "      <th>subject</th>\n",
       "      <th>tags</th>\n",
       "      <th>title</th>\n",
       "      <th>version</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2548090353367</td>\n",
       "      <td></td>\n",
       "      <td>2019-05-08T17:05:04Z</td>\n",
       "      <td></td>\n",
       "      <td>{}</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>[{u'fileSource': u'drugsComTest_raw.tsv', u'en...</td>\n",
       "      <td></td>\n",
       "      <td>2019-05-08T17:05:42Z</td>\n",
       "      <td>drugs com test</td>\n",
       "      <td>53766</td>\n",
       "      <td></td>\n",
       "      <td>[]</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       ProjectId contributors               created creator customMetadata  \\\n",
       "0  2548090353367               2019-05-08T17:05:04Z                     {}   \n",
       "\n",
       "  description homepage image  \\\n",
       "0                              \n",
       "\n",
       "                                importOptionMetadata license  \\\n",
       "0  [{u'fileSource': u'drugsComTest_raw.tsv', u'en...           \n",
       "\n",
       "               modified            name rowCount subject tags title version  \n",
       "0  2019-05-08T17:05:42Z  drugs com test    53766           []                "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "search_project(refine_server,\"drugs com test\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## List OpenRefine Projects using list_projects() method"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "class RefineServerHelper():\n",
    "    def __init__(self, refine_server):\n",
    "        self.refine_server = refine_server\n",
    "            \n",
    "    def list_projects(self):\n",
    "        \"\"\"\n",
    "        list OpenRefine project \n",
    "        \"\"\"\n",
    "        return pd.DataFrame(self.refine_server.list_projects()).transpose().reset_index().rename({\"index\":\"ProjectId\"},axis=1)\n",
    "\n",
    "    def search_projects(self,project_name):\n",
    "        \"\"\"\n",
    "        search project by name\n",
    "        \"\"\"\n",
    "        t = self.list_projects()\n",
    "        if t.shape[0]>0:\n",
    "            t = t[t.name==project_name]\n",
    "        #return pd.DataFrame(list(zip(t.index,t.name)),columns=[\"Project ID\",\"Project Name\"])        \n",
    "        return t    \n",
    "    \n",
    "    def delete_project_byname(self,project_name):\n",
    "        \"\"\"\n",
    "        delete project by name\n",
    "        \"\"\"\n",
    "        t = self.search_projects(project_name)\n",
    "        for x in t.ProjectId.values:\n",
    "            if self.refine_server.open_project(project_id=x).delete():\n",
    "                print(\"Project {} deleted\".format(x))\n",
    "                \n",
    "    def open_project_byname(self,project_name,project_id=None):\n",
    "        \"\"\"\n",
    "        open an openrefine project by name\n",
    "        \"\"\"\n",
    "        if project_id == None:\n",
    "            t = self.search_projects(project_name)\n",
    "            if t.shape[0] == 0:\n",
    "                raise Exception(\"No instance with Project Name: '{}' exist\".format(project_name,t.loc[:,[\"ProjectId\",\"Project Name\"]]))\n",
    "            if t.shape[0] > 1:\n",
    "                raise Exception(\"More than one instance with Project Name: '{}'\\n{}\\nPlease specify the project id\".format(project_name,t.loc[:,[\"ProjectId\",\"Project Name\"]]))\n",
    "            project_id = t.ProjectId.values[0]\n",
    "        else:\n",
    "            project_name = refine_server.get_project_name(project_id)\n",
    "        self.active_project = self.refine_server.open_project(project_id)\n",
    "        print(\"OpenRefine Project {} opened, access using active_project property\".format(project_name))\n",
    "        return self.active_project\n",
    "    \n",
    "    def get_number_columns(self):\n",
    "        return len(self.active_project.columns)\n",
    "    \n",
    "    def get_number_rows(self):\n",
    "        return self.active_project.get_rows().total\n",
    "    \n",
    "    def get_rows(self,start=0,limit=10):    \n",
    "        temp_rows = []\n",
    "        for x in self.active_project.get_rows(start=start,limit = limit).rows.rows_response:\n",
    "            temp_cells = []\n",
    "            for y_t in self.active_project.get_models()[\"columnModel\"][\"columns\"]:\n",
    "                y = x[\"cells\"][y_t[\"cellIndex\"]]\n",
    "                if y!=None:\n",
    "                    temp_cells.append(y[\"v\"])\n",
    "                else:\n",
    "                    temp_cells.append(None)\n",
    "            temp_rows.append(temp_cells)\n",
    "        return pd.DataFrame(temp_rows,columns=self.active_project.columns)    \n",
    "    \n",
    "    def to_lowercase(self,column_name):\n",
    "        return self.active_project.text_transform(column=column_name,expression=\"value.toLowercase()\")\n",
    "\n",
    "    def to_uppercase(self,column_name):\n",
    "        return self.active_project.text_transform(column=column_name,expression=\"value.toUppercase()\")\n",
    "        \n",
    "    def to_titlecase(self,column_name):\n",
    "        return self.active_project.text_transform(column=column_name,expression=\"value.toTitlecase()\")\n",
    "    \n",
    "    def cluster(self,column_name,cluster_type=\"binning\",function=None,params=None):\n",
    "        return pd.DataFrame(drug_project.compute_clusters(column_name,cluster_type,function,params))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "refine_helper = RefineServerHelper(refine_server)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>ProjectId</th>\n",
       "      <th>contributors</th>\n",
       "      <th>created</th>\n",
       "      <th>creator</th>\n",
       "      <th>customMetadata</th>\n",
       "      <th>description</th>\n",
       "      <th>homepage</th>\n",
       "      <th>image</th>\n",
       "      <th>importOptionMetadata</th>\n",
       "      <th>license</th>\n",
       "      <th>modified</th>\n",
       "      <th>name</th>\n",
       "      <th>rowCount</th>\n",
       "      <th>subject</th>\n",
       "      <th>tags</th>\n",
       "      <th>title</th>\n",
       "      <th>version</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2548090353367</td>\n",
       "      <td></td>\n",
       "      <td>2019-05-08T17:05:04Z</td>\n",
       "      <td></td>\n",
       "      <td>{}</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>[{u'fileSource': u'drugsComTest_raw.tsv', u'en...</td>\n",
       "      <td></td>\n",
       "      <td>2019-05-08T17:05:42Z</td>\n",
       "      <td>drugs com test</td>\n",
       "      <td>53766</td>\n",
       "      <td></td>\n",
       "      <td>[]</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       ProjectId contributors               created creator customMetadata  \\\n",
       "0  2548090353367               2019-05-08T17:05:04Z                     {}   \n",
       "\n",
       "  description homepage image  \\\n",
       "0                              \n",
       "\n",
       "                                importOptionMetadata license  \\\n",
       "0  [{u'fileSource': u'drugsComTest_raw.tsv', u'en...           \n",
       "\n",
       "               modified            name rowCount subject tags title version  \n",
       "0  2019-05-08T17:05:42Z  drugs com test    53766           []                "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "refine_helper.list_projects()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create a New Project"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "--2019-05-08 17:07:03--  https://github.com/nikolausn/Data_Cleaning_Python_Exercise/blob/master/drugsComTest_raw.tsv?raw=true\n",
      "Resolving github.com (github.com)... 192.30.253.113\n",
      "Connecting to github.com (github.com)|192.30.253.113|:443... connected.\n",
      "HTTP request sent, awaiting response... 302 Found\n",
      "Location: https://github.com/nikolausn/Data_Cleaning_Python_Exercise/raw/master/drugsComTest_raw.tsv [following]\n",
      "--2019-05-08 17:07:03--  https://github.com/nikolausn/Data_Cleaning_Python_Exercise/raw/master/drugsComTest_raw.tsv\n",
      "Reusing existing connection to github.com:443.\n",
      "HTTP request sent, awaiting response... 302 Found\n",
      "Location: https://raw.githubusercontent.com/nikolausn/Data_Cleaning_Python_Exercise/master/drugsComTest_raw.tsv [following]\n",
      "--2019-05-08 17:07:03--  https://raw.githubusercontent.com/nikolausn/Data_Cleaning_Python_Exercise/master/drugsComTest_raw.tsv\n",
      "Resolving raw.githubusercontent.com (raw.githubusercontent.com)... 151.101.0.133, 151.101.64.133, 151.101.128.133, ...\n",
      "Connecting to raw.githubusercontent.com (raw.githubusercontent.com)|151.101.0.133|:443... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 28071166 (27M) [text/plain]\n",
      "Saving to: ‘drugsComTest_raw.tsv’\n",
      "\n",
      "drugsComTest_raw.ts 100%[===================>]  26.77M  79.8MB/s    in 0.3s    \n",
      "\n",
      "2019-05-08 17:07:04 (79.8 MB/s) - ‘drugsComTest_raw.tsv’ saved [28071166/28071166]\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Download file\n",
    "!wget https://github.com/nikolausn/Data_Cleaning_Python_Exercise/blob/master/drugsComTest_raw.tsv?raw=true -O drugsComTest_raw.tsv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Project 2548090353367 deleted\n"
     ]
    }
   ],
   "source": [
    "# delete project if exist\n",
    "refine_helper.delete_project_byname(\"drugs com test\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "drug_project = refine_server.new_project(project_file=\"drugsComTest_raw.tsv\",project_name=\"drugs com test\",separator=\"\\t\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>ProjectId</th>\n",
       "      <th>contributors</th>\n",
       "      <th>created</th>\n",
       "      <th>creator</th>\n",
       "      <th>customMetadata</th>\n",
       "      <th>description</th>\n",
       "      <th>homepage</th>\n",
       "      <th>image</th>\n",
       "      <th>importOptionMetadata</th>\n",
       "      <th>license</th>\n",
       "      <th>modified</th>\n",
       "      <th>name</th>\n",
       "      <th>rowCount</th>\n",
       "      <th>subject</th>\n",
       "      <th>tags</th>\n",
       "      <th>title</th>\n",
       "      <th>version</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2403048382377</td>\n",
       "      <td></td>\n",
       "      <td>2019-05-08T17:07:05Z</td>\n",
       "      <td></td>\n",
       "      <td>{}</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>[{u'fileSource': u'drugsComTest_raw.tsv', u'en...</td>\n",
       "      <td></td>\n",
       "      <td>2019-05-08T17:07:09Z</td>\n",
       "      <td>drugs com test</td>\n",
       "      <td>0</td>\n",
       "      <td></td>\n",
       "      <td>[]</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       ProjectId contributors               created creator customMetadata  \\\n",
       "0  2403048382377               2019-05-08T17:07:05Z                     {}   \n",
       "\n",
       "  description homepage image  \\\n",
       "0                              \n",
       "\n",
       "                                importOptionMetadata license  \\\n",
       "0  [{u'fileSource': u'drugsComTest_raw.tsv', u'en...           \n",
       "\n",
       "               modified            name rowCount subject tags title version  \n",
       "0  2019-05-08T17:07:09Z  drugs com test        0           []                "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "refine_helper.search_projects(\"drugs com test\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{u'future': [], u'past': []}"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "drug_project.list_history()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Open the drug project"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "OpenRefine Project drugs com test opened, access using active_project property\n"
     ]
    }
   ],
   "source": [
    "drug_project = refine_helper.open_project_byname(\"drugs com test\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "u'drugs com test'"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "refine_helper.active_project.project_name()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# number of columns\n",
    "refine_helper.get_number_columns()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "53766"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "refine_helper.get_number_rows()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get List of Columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[u'Column',\n",
       " u'drugName',\n",
       " u'condition',\n",
       " u'review',\n",
       " u'rating',\n",
       " u'date',\n",
       " u'usefulCount']"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "drug_project.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Look at the rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>Column</th>\n",
       "      <th>drugName</th>\n",
       "      <th>condition</th>\n",
       "      <th>review</th>\n",
       "      <th>rating</th>\n",
       "      <th>date</th>\n",
       "      <th>usefulCount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>163740</td>\n",
       "      <td>Mirtazapine</td>\n",
       "      <td>Depression</td>\n",
       "      <td>\"I&amp;#039;ve tried a few antidepressants over th...</td>\n",
       "      <td>10</td>\n",
       "      <td>February 28, 2012</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>206473</td>\n",
       "      <td>Mesalamine</td>\n",
       "      <td>Crohn's Disease, Maintenance</td>\n",
       "      <td>\"My son has Crohn&amp;#039;s disease and has done ...</td>\n",
       "      <td>8</td>\n",
       "      <td>May 17, 2009</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>159672</td>\n",
       "      <td>Bactrim</td>\n",
       "      <td>Urinary Tract Infection</td>\n",
       "      <td>\"Quick reduction of symptoms\"</td>\n",
       "      <td>9</td>\n",
       "      <td>September 29, 2017</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>39293</td>\n",
       "      <td>Contrave</td>\n",
       "      <td>Weight Loss</td>\n",
       "      <td>\"Contrave combines drugs that were used for al...</td>\n",
       "      <td>9</td>\n",
       "      <td>March 5, 2017</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>97768</td>\n",
       "      <td>Cyclafem 1 / 35</td>\n",
       "      <td>Birth Control</td>\n",
       "      <td>\"I have been on this birth control for one cyc...</td>\n",
       "      <td>9</td>\n",
       "      <td>October 22, 2015</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>208087</td>\n",
       "      <td>Zyclara</td>\n",
       "      <td>Keratosis</td>\n",
       "      <td>\"4 days in on first 2 weeks.  Using on arms an...</td>\n",
       "      <td>4</td>\n",
       "      <td>July 3, 2014</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>215892</td>\n",
       "      <td>Copper</td>\n",
       "      <td>Birth Control</td>\n",
       "      <td>\"I&amp;#039;ve had the copper coil for about 3 mon...</td>\n",
       "      <td>6</td>\n",
       "      <td>June 6, 2016</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>169852</td>\n",
       "      <td>Amitriptyline</td>\n",
       "      <td>Migraine Prevention</td>\n",
       "      <td>\"This has been great for me. I&amp;#039;ve been on...</td>\n",
       "      <td>9</td>\n",
       "      <td>April 21, 2009</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>23295</td>\n",
       "      <td>Methadone</td>\n",
       "      <td>Opiate Withdrawal</td>\n",
       "      <td>\"Ive been on Methadone for over ten years and ...</td>\n",
       "      <td>7</td>\n",
       "      <td>October 18, 2016</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>71428</td>\n",
       "      <td>Levora</td>\n",
       "      <td>Birth Control</td>\n",
       "      <td>\"I was on this pill for almost two years. It d...</td>\n",
       "      <td>2</td>\n",
       "      <td>April 16, 2011</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>196802</td>\n",
       "      <td>Paroxetine</td>\n",
       "      <td>Hot Flashes</td>\n",
       "      <td>\"Holy Hell is exactly how I feel. I had been t...</td>\n",
       "      <td>1</td>\n",
       "      <td>February 22, 2017</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>31947</td>\n",
       "      <td>Miconazole</td>\n",
       "      <td>Vaginal Yeast Infection</td>\n",
       "      <td>\"Honestly its day one on the 3 day treatment. ...</td>\n",
       "      <td>6</td>\n",
       "      <td>May 7, 2015</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>4907</td>\n",
       "      <td>Belviq</td>\n",
       "      <td>Weight Loss</td>\n",
       "      <td>\"This is a waste of money.  Did not curb my ap...</td>\n",
       "      <td>1</td>\n",
       "      <td>September 23, 2014</td>\n",
       "      <td>57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>66736</td>\n",
       "      <td>Seroquel</td>\n",
       "      <td>Schizoaffective Disorde</td>\n",
       "      <td>\"No problems, watch what you eat.\"</td>\n",
       "      <td>10</td>\n",
       "      <td>October 8, 2014</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>97013</td>\n",
       "      <td>Ambien</td>\n",
       "      <td>Insomnia</td>\n",
       "      <td>\"Ditto on rebound sleepless when discontinued....</td>\n",
       "      <td>2</td>\n",
       "      <td>January 13, 2015</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>213376</td>\n",
       "      <td>Nuvigil</td>\n",
       "      <td>Narcolepsy</td>\n",
       "      <td>\"A doctor in the ER prescribed me 200 mg of Pr...</td>\n",
       "      <td>9</td>\n",
       "      <td>June 30, 2010</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>151674</td>\n",
       "      <td>Chantix</td>\n",
       "      <td>Smoking Cessation</td>\n",
       "      <td>\"I smoked for 50+ years.  Took it for one week...</td>\n",
       "      <td>10</td>\n",
       "      <td>February 14, 2015</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>33173</td>\n",
       "      <td>Microgestin Fe 1 / 20</td>\n",
       "      <td>Acne</td>\n",
       "      <td>\"So I was on Ginanvi for about 3 months before...</td>\n",
       "      <td>3</td>\n",
       "      <td>June 22, 2017</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>30401</td>\n",
       "      <td>Klonopin</td>\n",
       "      <td>Bipolar Disorde</td>\n",
       "      <td>\"This medication helped me  sleep, but eventua...</td>\n",
       "      <td>6</td>\n",
       "      <td>July 14, 2009</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>152490</td>\n",
       "      <td>Ciprofloxacin</td>\n",
       "      <td>Urinary Tract Infection</td>\n",
       "      <td>\"After just 1 dose of this ciprofloxacn, I fel...</td>\n",
       "      <td>10</td>\n",
       "      <td>June 9, 2010</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Column               drugName                     condition  \\\n",
       "0   163740            Mirtazapine                    Depression   \n",
       "1   206473             Mesalamine  Crohn's Disease, Maintenance   \n",
       "2   159672                Bactrim       Urinary Tract Infection   \n",
       "3    39293               Contrave                   Weight Loss   \n",
       "4    97768        Cyclafem 1 / 35                 Birth Control   \n",
       "5   208087                Zyclara                     Keratosis   \n",
       "6   215892                 Copper                 Birth Control   \n",
       "7   169852          Amitriptyline           Migraine Prevention   \n",
       "8    23295              Methadone             Opiate Withdrawal   \n",
       "9    71428                 Levora                 Birth Control   \n",
       "10  196802             Paroxetine                   Hot Flashes   \n",
       "11   31947             Miconazole       Vaginal Yeast Infection   \n",
       "12    4907                 Belviq                   Weight Loss   \n",
       "13   66736               Seroquel       Schizoaffective Disorde   \n",
       "14   97013                 Ambien                      Insomnia   \n",
       "15  213376                Nuvigil                    Narcolepsy   \n",
       "16  151674                Chantix             Smoking Cessation   \n",
       "17   33173  Microgestin Fe 1 / 20                          Acne   \n",
       "18   30401               Klonopin               Bipolar Disorde   \n",
       "19  152490          Ciprofloxacin       Urinary Tract Infection   \n",
       "\n",
       "                                               review  rating  \\\n",
       "0   \"I&#039;ve tried a few antidepressants over th...      10   \n",
       "1   \"My son has Crohn&#039;s disease and has done ...       8   \n",
       "2                       \"Quick reduction of symptoms\"       9   \n",
       "3   \"Contrave combines drugs that were used for al...       9   \n",
       "4   \"I have been on this birth control for one cyc...       9   \n",
       "5   \"4 days in on first 2 weeks.  Using on arms an...       4   \n",
       "6   \"I&#039;ve had the copper coil for about 3 mon...       6   \n",
       "7   \"This has been great for me. I&#039;ve been on...       9   \n",
       "8   \"Ive been on Methadone for over ten years and ...       7   \n",
       "9   \"I was on this pill for almost two years. It d...       2   \n",
       "10  \"Holy Hell is exactly how I feel. I had been t...       1   \n",
       "11  \"Honestly its day one on the 3 day treatment. ...       6   \n",
       "12  \"This is a waste of money.  Did not curb my ap...       1   \n",
       "13                 \"No problems, watch what you eat.\"      10   \n",
       "14  \"Ditto on rebound sleepless when discontinued....       2   \n",
       "15  \"A doctor in the ER prescribed me 200 mg of Pr...       9   \n",
       "16  \"I smoked for 50+ years.  Took it for one week...      10   \n",
       "17  \"So I was on Ginanvi for about 3 months before...       3   \n",
       "18  \"This medication helped me  sleep, but eventua...       6   \n",
       "19  \"After just 1 dose of this ciprofloxacn, I fel...      10   \n",
       "\n",
       "                  date  usefulCount  \n",
       "0    February 28, 2012           22  \n",
       "1         May 17, 2009           17  \n",
       "2   September 29, 2017            3  \n",
       "3        March 5, 2017           35  \n",
       "4     October 22, 2015            4  \n",
       "5         July 3, 2014           13  \n",
       "6         June 6, 2016            1  \n",
       "7       April 21, 2009           32  \n",
       "8     October 18, 2016           21  \n",
       "9       April 16, 2011            3  \n",
       "10   February 22, 2017           17  \n",
       "11         May 7, 2015            7  \n",
       "12  September 23, 2014           57  \n",
       "13     October 8, 2014           19  \n",
       "14    January 13, 2015           44  \n",
       "15       June 30, 2010           14  \n",
       "16   February 14, 2015           26  \n",
       "17       June 22, 2017            1  \n",
       "18       July 14, 2009           24  \n",
       "19        June 9, 2010            9  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "refine_helper.get_rows(start=0,limit=20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### create a new column for cleaning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{u'code': u'ok',\n",
       " u'historyEntry': {u'description': u'Create new column review_cleaned based on column review by filling 53766 rows with value',\n",
       "  u'id': 1557335992383,\n",
       "  u'time': u'2019-05-08T17:07:10Z'}}"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "drug_project.add_column(column=\"review\",new_column=\"review_cleaned\")"
   ]
  },
  {
   "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>Column</th>\n",
       "      <th>drugName</th>\n",
       "      <th>condition</th>\n",
       "      <th>review</th>\n",
       "      <th>review_cleaned</th>\n",
       "      <th>rating</th>\n",
       "      <th>date</th>\n",
       "      <th>usefulCount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>163740</td>\n",
       "      <td>Mirtazapine</td>\n",
       "      <td>Depression</td>\n",
       "      <td>\"I&amp;#039;ve tried a few antidepressants over th...</td>\n",
       "      <td>\"I&amp;#039;ve tried a few antidepressants over th...</td>\n",
       "      <td>10</td>\n",
       "      <td>February 28, 2012</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>206473</td>\n",
       "      <td>Mesalamine</td>\n",
       "      <td>Crohn's Disease, Maintenance</td>\n",
       "      <td>\"My son has Crohn&amp;#039;s disease and has done ...</td>\n",
       "      <td>\"My son has Crohn&amp;#039;s disease and has done ...</td>\n",
       "      <td>8</td>\n",
       "      <td>May 17, 2009</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>159672</td>\n",
       "      <td>Bactrim</td>\n",
       "      <td>Urinary Tract Infection</td>\n",
       "      <td>\"Quick reduction of symptoms\"</td>\n",
       "      <td>\"Quick reduction of symptoms\"</td>\n",
       "      <td>9</td>\n",
       "      <td>September 29, 2017</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>39293</td>\n",
       "      <td>Contrave</td>\n",
       "      <td>Weight Loss</td>\n",
       "      <td>\"Contrave combines drugs that were used for al...</td>\n",
       "      <td>\"Contrave combines drugs that were used for al...</td>\n",
       "      <td>9</td>\n",
       "      <td>March 5, 2017</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>97768</td>\n",
       "      <td>Cyclafem 1 / 35</td>\n",
       "      <td>Birth Control</td>\n",
       "      <td>\"I have been on this birth control for one cyc...</td>\n",
       "      <td>\"I have been on this birth control for one cyc...</td>\n",
       "      <td>9</td>\n",
       "      <td>October 22, 2015</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>208087</td>\n",
       "      <td>Zyclara</td>\n",
       "      <td>Keratosis</td>\n",
       "      <td>\"4 days in on first 2 weeks.  Using on arms an...</td>\n",
       "      <td>\"4 days in on first 2 weeks.  Using on arms an...</td>\n",
       "      <td>4</td>\n",
       "      <td>July 3, 2014</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>215892</td>\n",
       "      <td>Copper</td>\n",
       "      <td>Birth Control</td>\n",
       "      <td>\"I&amp;#039;ve had the copper coil for about 3 mon...</td>\n",
       "      <td>\"I&amp;#039;ve had the copper coil for about 3 mon...</td>\n",
       "      <td>6</td>\n",
       "      <td>June 6, 2016</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>169852</td>\n",
       "      <td>Amitriptyline</td>\n",
       "      <td>Migraine Prevention</td>\n",
       "      <td>\"This has been great for me. I&amp;#039;ve been on...</td>\n",
       "      <td>\"This has been great for me. I&amp;#039;ve been on...</td>\n",
       "      <td>9</td>\n",
       "      <td>April 21, 2009</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>23295</td>\n",
       "      <td>Methadone</td>\n",
       "      <td>Opiate Withdrawal</td>\n",
       "      <td>\"Ive been on Methadone for over ten years and ...</td>\n",
       "      <td>\"Ive been on Methadone for over ten years and ...</td>\n",
       "      <td>7</td>\n",
       "      <td>October 18, 2016</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>71428</td>\n",
       "      <td>Levora</td>\n",
       "      <td>Birth Control</td>\n",
       "      <td>\"I was on this pill for almost two years. It d...</td>\n",
       "      <td>\"I was on this pill for almost two years. It d...</td>\n",
       "      <td>2</td>\n",
       "      <td>April 16, 2011</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Column         drugName                     condition  \\\n",
       "0  163740      Mirtazapine                    Depression   \n",
       "1  206473       Mesalamine  Crohn's Disease, Maintenance   \n",
       "2  159672          Bactrim       Urinary Tract Infection   \n",
       "3   39293         Contrave                   Weight Loss   \n",
       "4   97768  Cyclafem 1 / 35                 Birth Control   \n",
       "5  208087          Zyclara                     Keratosis   \n",
       "6  215892           Copper                 Birth Control   \n",
       "7  169852    Amitriptyline           Migraine Prevention   \n",
       "8   23295        Methadone             Opiate Withdrawal   \n",
       "9   71428           Levora                 Birth Control   \n",
       "\n",
       "                                              review  \\\n",
       "0  \"I&#039;ve tried a few antidepressants over th...   \n",
       "1  \"My son has Crohn&#039;s disease and has done ...   \n",
       "2                      \"Quick reduction of symptoms\"   \n",
       "3  \"Contrave combines drugs that were used for al...   \n",
       "4  \"I have been on this birth control for one cyc...   \n",
       "5  \"4 days in on first 2 weeks.  Using on arms an...   \n",
       "6  \"I&#039;ve had the copper coil for about 3 mon...   \n",
       "7  \"This has been great for me. I&#039;ve been on...   \n",
       "8  \"Ive been on Methadone for over ten years and ...   \n",
       "9  \"I was on this pill for almost two years. It d...   \n",
       "\n",
       "                                      review_cleaned  rating  \\\n",
       "0  \"I&#039;ve tried a few antidepressants over th...      10   \n",
       "1  \"My son has Crohn&#039;s disease and has done ...       8   \n",
       "2                      \"Quick reduction of symptoms\"       9   \n",
       "3  \"Contrave combines drugs that were used for al...       9   \n",
       "4  \"I have been on this birth control for one cyc...       9   \n",
       "5  \"4 days in on first 2 weeks.  Using on arms an...       4   \n",
       "6  \"I&#039;ve had the copper coil for about 3 mon...       6   \n",
       "7  \"This has been great for me. I&#039;ve been on...       9   \n",
       "8  \"Ive been on Methadone for over ten years and ...       7   \n",
       "9  \"I was on this pill for almost two years. It d...       2   \n",
       "\n",
       "                 date  usefulCount  \n",
       "0   February 28, 2012           22  \n",
       "1        May 17, 2009           17  \n",
       "2  September 29, 2017            3  \n",
       "3       March 5, 2017           35  \n",
       "4    October 22, 2015            4  \n",
       "5        July 3, 2014           13  \n",
       "6        June 6, 2016            1  \n",
       "7      April 21, 2009           32  \n",
       "8    October 18, 2016           21  \n",
       "9      April 16, 2011            3  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "refine_helper.get_rows()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### tolower Operation on review_cleaned"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{u'code': u'ok',\n",
       " u'historyEntry': {u'description': u'Text transform on 53542 cells in column review_cleaned: value.toLowercase()',\n",
       "  u'id': 1557335540561,\n",
       "  u'time': u'2019-05-08T17:07:14Z'}}"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "refine_helper.to_lowercase(\"review_cleaned\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>Column</th>\n",
       "      <th>drugName</th>\n",
       "      <th>condition</th>\n",
       "      <th>review</th>\n",
       "      <th>review_cleaned</th>\n",
       "      <th>rating</th>\n",
       "      <th>date</th>\n",
       "      <th>usefulCount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>163740</td>\n",
       "      <td>Mirtazapine</td>\n",
       "      <td>Depression</td>\n",
       "      <td>\"I&amp;#039;ve tried a few antidepressants over th...</td>\n",
       "      <td>\"i&amp;#039;ve tried a few antidepressants over th...</td>\n",
       "      <td>10</td>\n",
       "      <td>February 28, 2012</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>206473</td>\n",
       "      <td>Mesalamine</td>\n",
       "      <td>Crohn's Disease, Maintenance</td>\n",
       "      <td>\"My son has Crohn&amp;#039;s disease and has done ...</td>\n",
       "      <td>\"my son has crohn&amp;#039;s disease and has done ...</td>\n",
       "      <td>8</td>\n",
       "      <td>May 17, 2009</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>159672</td>\n",
       "      <td>Bactrim</td>\n",
       "      <td>Urinary Tract Infection</td>\n",
       "      <td>\"Quick reduction of symptoms\"</td>\n",
       "      <td>\"quick reduction of symptoms\"</td>\n",
       "      <td>9</td>\n",
       "      <td>September 29, 2017</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>39293</td>\n",
       "      <td>Contrave</td>\n",
       "      <td>Weight Loss</td>\n",
       "      <td>\"Contrave combines drugs that were used for al...</td>\n",
       "      <td>\"contrave combines drugs that were used for al...</td>\n",
       "      <td>9</td>\n",
       "      <td>March 5, 2017</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>97768</td>\n",
       "      <td>Cyclafem 1 / 35</td>\n",
       "      <td>Birth Control</td>\n",
       "      <td>\"I have been on this birth control for one cyc...</td>\n",
       "      <td>\"i have been on this birth control for one cyc...</td>\n",
       "      <td>9</td>\n",
       "      <td>October 22, 2015</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>208087</td>\n",
       "      <td>Zyclara</td>\n",
       "      <td>Keratosis</td>\n",
       "      <td>\"4 days in on first 2 weeks.  Using on arms an...</td>\n",
       "      <td>\"4 days in on first 2 weeks.  using on arms an...</td>\n",
       "      <td>4</td>\n",
       "      <td>July 3, 2014</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>215892</td>\n",
       "      <td>Copper</td>\n",
       "      <td>Birth Control</td>\n",
       "      <td>\"I&amp;#039;ve had the copper coil for about 3 mon...</td>\n",
       "      <td>\"i&amp;#039;ve had the copper coil for about 3 mon...</td>\n",
       "      <td>6</td>\n",
       "      <td>June 6, 2016</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>169852</td>\n",
       "      <td>Amitriptyline</td>\n",
       "      <td>Migraine Prevention</td>\n",
       "      <td>\"This has been great for me. I&amp;#039;ve been on...</td>\n",
       "      <td>\"this has been great for me. i&amp;#039;ve been on...</td>\n",
       "      <td>9</td>\n",
       "      <td>April 21, 2009</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>23295</td>\n",
       "      <td>Methadone</td>\n",
       "      <td>Opiate Withdrawal</td>\n",
       "      <td>\"Ive been on Methadone for over ten years and ...</td>\n",
       "      <td>\"ive been on methadone for over ten years and ...</td>\n",
       "      <td>7</td>\n",
       "      <td>October 18, 2016</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>71428</td>\n",
       "      <td>Levora</td>\n",
       "      <td>Birth Control</td>\n",
       "      <td>\"I was on this pill for almost two years. It d...</td>\n",
       "      <td>\"i was on this pill for almost two years. it d...</td>\n",
       "      <td>2</td>\n",
       "      <td>April 16, 2011</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Column         drugName                     condition  \\\n",
       "0  163740      Mirtazapine                    Depression   \n",
       "1  206473       Mesalamine  Crohn's Disease, Maintenance   \n",
       "2  159672          Bactrim       Urinary Tract Infection   \n",
       "3   39293         Contrave                   Weight Loss   \n",
       "4   97768  Cyclafem 1 / 35                 Birth Control   \n",
       "5  208087          Zyclara                     Keratosis   \n",
       "6  215892           Copper                 Birth Control   \n",
       "7  169852    Amitriptyline           Migraine Prevention   \n",
       "8   23295        Methadone             Opiate Withdrawal   \n",
       "9   71428           Levora                 Birth Control   \n",
       "\n",
       "                                              review  \\\n",
       "0  \"I&#039;ve tried a few antidepressants over th...   \n",
       "1  \"My son has Crohn&#039;s disease and has done ...   \n",
       "2                      \"Quick reduction of symptoms\"   \n",
       "3  \"Contrave combines drugs that were used for al...   \n",
       "4  \"I have been on this birth control for one cyc...   \n",
       "5  \"4 days in on first 2 weeks.  Using on arms an...   \n",
       "6  \"I&#039;ve had the copper coil for about 3 mon...   \n",
       "7  \"This has been great for me. I&#039;ve been on...   \n",
       "8  \"Ive been on Methadone for over ten years and ...   \n",
       "9  \"I was on this pill for almost two years. It d...   \n",
       "\n",
       "                                      review_cleaned  rating  \\\n",
       "0  \"i&#039;ve tried a few antidepressants over th...      10   \n",
       "1  \"my son has crohn&#039;s disease and has done ...       8   \n",
       "2                      \"quick reduction of symptoms\"       9   \n",
       "3  \"contrave combines drugs that were used for al...       9   \n",
       "4  \"i have been on this birth control for one cyc...       9   \n",
       "5  \"4 days in on first 2 weeks.  using on arms an...       4   \n",
       "6  \"i&#039;ve had the copper coil for about 3 mon...       6   \n",
       "7  \"this has been great for me. i&#039;ve been on...       9   \n",
       "8  \"ive been on methadone for over ten years and ...       7   \n",
       "9  \"i was on this pill for almost two years. it d...       2   \n",
       "\n",
       "                 date  usefulCount  \n",
       "0   February 28, 2012           22  \n",
       "1        May 17, 2009           17  \n",
       "2  September 29, 2017            3  \n",
       "3       March 5, 2017           35  \n",
       "4    October 22, 2015            4  \n",
       "5        July 3, 2014           13  \n",
       "6        June 6, 2016            1  \n",
       "7      April 21, 2009           32  \n",
       "8    October 18, 2016           21  \n",
       "9      April 16, 2011            3  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "refine_helper.get_rows()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Delete the first and last quote \" in the review_cleaned column using regular expression"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{u'code': u'ok',\n",
       " u'historyEntry': {u'description': u'Text transform on 53766 cells in column review_cleaned: value.replace(/^\"/,\"\")',\n",
       "  u'id': 1557336194092,\n",
       "  u'time': u'2019-05-08T17:07:19Z'}}"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# beginning quote\n",
    "drug_project.text_transform(column=\"review_cleaned\",expression=\"value.replace(/^\\\"/,\\\"\\\")\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{u'code': u'ok',\n",
       " u'historyEntry': {u'description': u'Text transform on 53766 cells in column review_cleaned: value.replace(/\"$/,\"\")',\n",
       "  u'id': 1557335344379,\n",
       "  u'time': u'2019-05-08T17:07:23Z'}}"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "drug_project.text_transform(column=\"review_cleaned\",expression='value.replace(/\"$/,\"\")')"
   ]
  },
  {
   "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>Column</th>\n",
       "      <th>drugName</th>\n",
       "      <th>condition</th>\n",
       "      <th>review</th>\n",
       "      <th>review_cleaned</th>\n",
       "      <th>rating</th>\n",
       "      <th>date</th>\n",
       "      <th>usefulCount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>163740</td>\n",
       "      <td>Mirtazapine</td>\n",
       "      <td>Depression</td>\n",
       "      <td>\"I&amp;#039;ve tried a few antidepressants over th...</td>\n",
       "      <td>i&amp;#039;ve tried a few antidepressants over the...</td>\n",
       "      <td>10</td>\n",
       "      <td>February 28, 2012</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>206473</td>\n",
       "      <td>Mesalamine</td>\n",
       "      <td>Crohn's Disease, Maintenance</td>\n",
       "      <td>\"My son has Crohn&amp;#039;s disease and has done ...</td>\n",
       "      <td>my son has crohn&amp;#039;s disease and has done v...</td>\n",
       "      <td>8</td>\n",
       "      <td>May 17, 2009</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>159672</td>\n",
       "      <td>Bactrim</td>\n",
       "      <td>Urinary Tract Infection</td>\n",
       "      <td>\"Quick reduction of symptoms\"</td>\n",
       "      <td>quick reduction of symptoms</td>\n",
       "      <td>9</td>\n",
       "      <td>September 29, 2017</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>39293</td>\n",
       "      <td>Contrave</td>\n",
       "      <td>Weight Loss</td>\n",
       "      <td>\"Contrave combines drugs that were used for al...</td>\n",
       "      <td>contrave combines drugs that were used for alc...</td>\n",
       "      <td>9</td>\n",
       "      <td>March 5, 2017</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>97768</td>\n",
       "      <td>Cyclafem 1 / 35</td>\n",
       "      <td>Birth Control</td>\n",
       "      <td>\"I have been on this birth control for one cyc...</td>\n",
       "      <td>i have been on this birth control for one cycl...</td>\n",
       "      <td>9</td>\n",
       "      <td>October 22, 2015</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>208087</td>\n",
       "      <td>Zyclara</td>\n",
       "      <td>Keratosis</td>\n",
       "      <td>\"4 days in on first 2 weeks.  Using on arms an...</td>\n",
       "      <td>4 days in on first 2 weeks.  using on arms and...</td>\n",
       "      <td>4</td>\n",
       "      <td>July 3, 2014</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>215892</td>\n",
       "      <td>Copper</td>\n",
       "      <td>Birth Control</td>\n",
       "      <td>\"I&amp;#039;ve had the copper coil for about 3 mon...</td>\n",
       "      <td>i&amp;#039;ve had the copper coil for about 3 mont...</td>\n",
       "      <td>6</td>\n",
       "      <td>June 6, 2016</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>169852</td>\n",
       "      <td>Amitriptyline</td>\n",
       "      <td>Migraine Prevention</td>\n",
       "      <td>\"This has been great for me. I&amp;#039;ve been on...</td>\n",
       "      <td>this has been great for me. i&amp;#039;ve been on ...</td>\n",
       "      <td>9</td>\n",
       "      <td>April 21, 2009</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>23295</td>\n",
       "      <td>Methadone</td>\n",
       "      <td>Opiate Withdrawal</td>\n",
       "      <td>\"Ive been on Methadone for over ten years and ...</td>\n",
       "      <td>ive been on methadone for over ten years and c...</td>\n",
       "      <td>7</td>\n",
       "      <td>October 18, 2016</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>71428</td>\n",
       "      <td>Levora</td>\n",
       "      <td>Birth Control</td>\n",
       "      <td>\"I was on this pill for almost two years. It d...</td>\n",
       "      <td>i was on this pill for almost two years. it do...</td>\n",
       "      <td>2</td>\n",
       "      <td>April 16, 2011</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Column         drugName                     condition  \\\n",
       "0  163740      Mirtazapine                    Depression   \n",
       "1  206473       Mesalamine  Crohn's Disease, Maintenance   \n",
       "2  159672          Bactrim       Urinary Tract Infection   \n",
       "3   39293         Contrave                   Weight Loss   \n",
       "4   97768  Cyclafem 1 / 35                 Birth Control   \n",
       "5  208087          Zyclara                     Keratosis   \n",
       "6  215892           Copper                 Birth Control   \n",
       "7  169852    Amitriptyline           Migraine Prevention   \n",
       "8   23295        Methadone             Opiate Withdrawal   \n",
       "9   71428           Levora                 Birth Control   \n",
       "\n",
       "                                              review  \\\n",
       "0  \"I&#039;ve tried a few antidepressants over th...   \n",
       "1  \"My son has Crohn&#039;s disease and has done ...   \n",
       "2                      \"Quick reduction of symptoms\"   \n",
       "3  \"Contrave combines drugs that were used for al...   \n",
       "4  \"I have been on this birth control for one cyc...   \n",
       "5  \"4 days in on first 2 weeks.  Using on arms an...   \n",
       "6  \"I&#039;ve had the copper coil for about 3 mon...   \n",
       "7  \"This has been great for me. I&#039;ve been on...   \n",
       "8  \"Ive been on Methadone for over ten years and ...   \n",
       "9  \"I was on this pill for almost two years. It d...   \n",
       "\n",
       "                                      review_cleaned  rating  \\\n",
       "0  i&#039;ve tried a few antidepressants over the...      10   \n",
       "1  my son has crohn&#039;s disease and has done v...       8   \n",
       "2                        quick reduction of symptoms       9   \n",
       "3  contrave combines drugs that were used for alc...       9   \n",
       "4  i have been on this birth control for one cycl...       9   \n",
       "5  4 days in on first 2 weeks.  using on arms and...       4   \n",
       "6  i&#039;ve had the copper coil for about 3 mont...       6   \n",
       "7  this has been great for me. i&#039;ve been on ...       9   \n",
       "8  ive been on methadone for over ten years and c...       7   \n",
       "9  i was on this pill for almost two years. it do...       2   \n",
       "\n",
       "                 date  usefulCount  \n",
       "0   February 28, 2012           22  \n",
       "1        May 17, 2009           17  \n",
       "2  September 29, 2017            3  \n",
       "3       March 5, 2017           35  \n",
       "4    October 22, 2015            4  \n",
       "5        July 3, 2014           13  \n",
       "6        June 6, 2016            1  \n",
       "7      April 21, 2009           32  \n",
       "8    October 18, 2016           21  \n",
       "9      April 16, 2011            3  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "refine_helper.get_rows()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## To date operation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{u'code': u'ok',\n",
       " u'historyEntry': {u'description': u'Create new column date_cleaned based on column date by filling 53766 rows with value',\n",
       "  u'id': 1557335452682,\n",
       "  u'time': u'2019-05-08T17:07:28Z'}}"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# copy column date\n",
    "drug_project.add_column(column=\"date\",new_column=\"date_cleaned\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>Column</th>\n",
       "      <th>drugName</th>\n",
       "      <th>condition</th>\n",
       "      <th>review</th>\n",
       "      <th>review_cleaned</th>\n",
       "      <th>rating</th>\n",
       "      <th>date</th>\n",
       "      <th>date_cleaned</th>\n",
       "      <th>usefulCount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>163740</td>\n",
       "      <td>Mirtazapine</td>\n",
       "      <td>Depression</td>\n",
       "      <td>\"I&amp;#039;ve tried a few antidepressants over th...</td>\n",
       "      <td>i&amp;#039;ve tried a few antidepressants over the...</td>\n",
       "      <td>10</td>\n",
       "      <td>February 28, 2012</td>\n",
       "      <td>February 28, 2012</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>206473</td>\n",
       "      <td>Mesalamine</td>\n",
       "      <td>Crohn's Disease, Maintenance</td>\n",
       "      <td>\"My son has Crohn&amp;#039;s disease and has done ...</td>\n",
       "      <td>my son has crohn&amp;#039;s disease and has done v...</td>\n",
       "      <td>8</td>\n",
       "      <td>May 17, 2009</td>\n",
       "      <td>May 17, 2009</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>159672</td>\n",
       "      <td>Bactrim</td>\n",
       "      <td>Urinary Tract Infection</td>\n",
       "      <td>\"Quick reduction of symptoms\"</td>\n",
       "      <td>quick reduction of symptoms</td>\n",
       "      <td>9</td>\n",
       "      <td>September 29, 2017</td>\n",
       "      <td>September 29, 2017</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>39293</td>\n",
       "      <td>Contrave</td>\n",
       "      <td>Weight Loss</td>\n",
       "      <td>\"Contrave combines drugs that were used for al...</td>\n",
       "      <td>contrave combines drugs that were used for alc...</td>\n",
       "      <td>9</td>\n",
       "      <td>March 5, 2017</td>\n",
       "      <td>March 5, 2017</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>97768</td>\n",
       "      <td>Cyclafem 1 / 35</td>\n",
       "      <td>Birth Control</td>\n",
       "      <td>\"I have been on this birth control for one cyc...</td>\n",
       "      <td>i have been on this birth control for one cycl...</td>\n",
       "      <td>9</td>\n",
       "      <td>October 22, 2015</td>\n",
       "      <td>October 22, 2015</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>208087</td>\n",
       "      <td>Zyclara</td>\n",
       "      <td>Keratosis</td>\n",
       "      <td>\"4 days in on first 2 weeks.  Using on arms an...</td>\n",
       "      <td>4 days in on first 2 weeks.  using on arms and...</td>\n",
       "      <td>4</td>\n",
       "      <td>July 3, 2014</td>\n",
       "      <td>July 3, 2014</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>215892</td>\n",
       "      <td>Copper</td>\n",
       "      <td>Birth Control</td>\n",
       "      <td>\"I&amp;#039;ve had the copper coil for about 3 mon...</td>\n",
       "      <td>i&amp;#039;ve had the copper coil for about 3 mont...</td>\n",
       "      <td>6</td>\n",
       "      <td>June 6, 2016</td>\n",
       "      <td>June 6, 2016</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>169852</td>\n",
       "      <td>Amitriptyline</td>\n",
       "      <td>Migraine Prevention</td>\n",
       "      <td>\"This has been great for me. I&amp;#039;ve been on...</td>\n",
       "      <td>this has been great for me. i&amp;#039;ve been on ...</td>\n",
       "      <td>9</td>\n",
       "      <td>April 21, 2009</td>\n",
       "      <td>April 21, 2009</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>23295</td>\n",
       "      <td>Methadone</td>\n",
       "      <td>Opiate Withdrawal</td>\n",
       "      <td>\"Ive been on Methadone for over ten years and ...</td>\n",
       "      <td>ive been on methadone for over ten years and c...</td>\n",
       "      <td>7</td>\n",
       "      <td>October 18, 2016</td>\n",
       "      <td>October 18, 2016</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>71428</td>\n",
       "      <td>Levora</td>\n",
       "      <td>Birth Control</td>\n",
       "      <td>\"I was on this pill for almost two years. It d...</td>\n",
       "      <td>i was on this pill for almost two years. it do...</td>\n",
       "      <td>2</td>\n",
       "      <td>April 16, 2011</td>\n",
       "      <td>April 16, 2011</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Column         drugName                     condition  \\\n",
       "0  163740      Mirtazapine                    Depression   \n",
       "1  206473       Mesalamine  Crohn's Disease, Maintenance   \n",
       "2  159672          Bactrim       Urinary Tract Infection   \n",
       "3   39293         Contrave                   Weight Loss   \n",
       "4   97768  Cyclafem 1 / 35                 Birth Control   \n",
       "5  208087          Zyclara                     Keratosis   \n",
       "6  215892           Copper                 Birth Control   \n",
       "7  169852    Amitriptyline           Migraine Prevention   \n",
       "8   23295        Methadone             Opiate Withdrawal   \n",
       "9   71428           Levora                 Birth Control   \n",
       "\n",
       "                                              review  \\\n",
       "0  \"I&#039;ve tried a few antidepressants over th...   \n",
       "1  \"My son has Crohn&#039;s disease and has done ...   \n",
       "2                      \"Quick reduction of symptoms\"   \n",
       "3  \"Contrave combines drugs that were used for al...   \n",
       "4  \"I have been on this birth control for one cyc...   \n",
       "5  \"4 days in on first 2 weeks.  Using on arms an...   \n",
       "6  \"I&#039;ve had the copper coil for about 3 mon...   \n",
       "7  \"This has been great for me. I&#039;ve been on...   \n",
       "8  \"Ive been on Methadone for over ten years and ...   \n",
       "9  \"I was on this pill for almost two years. It d...   \n",
       "\n",
       "                                      review_cleaned  rating  \\\n",
       "0  i&#039;ve tried a few antidepressants over the...      10   \n",
       "1  my son has crohn&#039;s disease and has done v...       8   \n",
       "2                        quick reduction of symptoms       9   \n",
       "3  contrave combines drugs that were used for alc...       9   \n",
       "4  i have been on this birth control for one cycl...       9   \n",
       "5  4 days in on first 2 weeks.  using on arms and...       4   \n",
       "6  i&#039;ve had the copper coil for about 3 mont...       6   \n",
       "7  this has been great for me. i&#039;ve been on ...       9   \n",
       "8  ive been on methadone for over ten years and c...       7   \n",
       "9  i was on this pill for almost two years. it do...       2   \n",
       "\n",
       "                 date        date_cleaned  usefulCount  \n",
       "0   February 28, 2012   February 28, 2012           22  \n",
       "1        May 17, 2009        May 17, 2009           17  \n",
       "2  September 29, 2017  September 29, 2017            3  \n",
       "3       March 5, 2017       March 5, 2017           35  \n",
       "4    October 22, 2015    October 22, 2015            4  \n",
       "5        July 3, 2014        July 3, 2014           13  \n",
       "6        June 6, 2016        June 6, 2016            1  \n",
       "7      April 21, 2009      April 21, 2009           32  \n",
       "8    October 18, 2016    October 18, 2016           21  \n",
       "9      April 16, 2011      April 16, 2011            3  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "refine_helper.get_rows()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{u'code': u'ok',\n",
       " u'historyEntry': {u'description': u'Text transform on 53766 cells in column date_cleaned: value.toDate()',\n",
       "  u'id': 1557335476122,\n",
       "  u'time': u'2019-05-08T17:07:29Z'}}"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "drug_project.text_transform(column=\"date_cleaned\",expression=\"value.toDate()\")"
   ]
  },
  {
   "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>Column</th>\n",
       "      <th>drugName</th>\n",
       "      <th>condition</th>\n",
       "      <th>review</th>\n",
       "      <th>review_cleaned</th>\n",
       "      <th>rating</th>\n",
       "      <th>date</th>\n",
       "      <th>date_cleaned</th>\n",
       "      <th>usefulCount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>163740</td>\n",
       "      <td>Mirtazapine</td>\n",
       "      <td>Depression</td>\n",
       "      <td>\"I&amp;#039;ve tried a few antidepressants over th...</td>\n",
       "      <td>i&amp;#039;ve tried a few antidepressants over the...</td>\n",
       "      <td>10</td>\n",
       "      <td>February 28, 2012</td>\n",
       "      <td>2012-02-28T00:00:00Z</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>206473</td>\n",
       "      <td>Mesalamine</td>\n",
       "      <td>Crohn's Disease, Maintenance</td>\n",
       "      <td>\"My son has Crohn&amp;#039;s disease and has done ...</td>\n",
       "      <td>my son has crohn&amp;#039;s disease and has done v...</td>\n",
       "      <td>8</td>\n",
       "      <td>May 17, 2009</td>\n",
       "      <td>2009-05-17T00:00:00Z</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>159672</td>\n",
       "      <td>Bactrim</td>\n",
       "      <td>Urinary Tract Infection</td>\n",
       "      <td>\"Quick reduction of symptoms\"</td>\n",
       "      <td>quick reduction of symptoms</td>\n",
       "      <td>9</td>\n",
       "      <td>September 29, 2017</td>\n",
       "      <td>2017-09-29T00:00:00Z</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>39293</td>\n",
       "      <td>Contrave</td>\n",
       "      <td>Weight Loss</td>\n",
       "      <td>\"Contrave combines drugs that were used for al...</td>\n",
       "      <td>contrave combines drugs that were used for alc...</td>\n",
       "      <td>9</td>\n",
       "      <td>March 5, 2017</td>\n",
       "      <td>2017-03-05T00:00:00Z</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>97768</td>\n",
       "      <td>Cyclafem 1 / 35</td>\n",
       "      <td>Birth Control</td>\n",
       "      <td>\"I have been on this birth control for one cyc...</td>\n",
       "      <td>i have been on this birth control for one cycl...</td>\n",
       "      <td>9</td>\n",
       "      <td>October 22, 2015</td>\n",
       "      <td>2015-10-22T00:00:00Z</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>208087</td>\n",
       "      <td>Zyclara</td>\n",
       "      <td>Keratosis</td>\n",
       "      <td>\"4 days in on first 2 weeks.  Using on arms an...</td>\n",
       "      <td>4 days in on first 2 weeks.  using on arms and...</td>\n",
       "      <td>4</td>\n",
       "      <td>July 3, 2014</td>\n",
       "      <td>2014-07-03T00:00:00Z</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>215892</td>\n",
       "      <td>Copper</td>\n",
       "      <td>Birth Control</td>\n",
       "      <td>\"I&amp;#039;ve had the copper coil for about 3 mon...</td>\n",
       "      <td>i&amp;#039;ve had the copper coil for about 3 mont...</td>\n",
       "      <td>6</td>\n",
       "      <td>June 6, 2016</td>\n",
       "      <td>2016-06-06T00:00:00Z</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>169852</td>\n",
       "      <td>Amitriptyline</td>\n",
       "      <td>Migraine Prevention</td>\n",
       "      <td>\"This has been great for me. I&amp;#039;ve been on...</td>\n",
       "      <td>this has been great for me. i&amp;#039;ve been on ...</td>\n",
       "      <td>9</td>\n",
       "      <td>April 21, 2009</td>\n",
       "      <td>2009-04-21T00:00:00Z</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>23295</td>\n",
       "      <td>Methadone</td>\n",
       "      <td>Opiate Withdrawal</td>\n",
       "      <td>\"Ive been on Methadone for over ten years and ...</td>\n",
       "      <td>ive been on methadone for over ten years and c...</td>\n",
       "      <td>7</td>\n",
       "      <td>October 18, 2016</td>\n",
       "      <td>2016-10-18T00:00:00Z</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>71428</td>\n",
       "      <td>Levora</td>\n",
       "      <td>Birth Control</td>\n",
       "      <td>\"I was on this pill for almost two years. It d...</td>\n",
       "      <td>i was on this pill for almost two years. it do...</td>\n",
       "      <td>2</td>\n",
       "      <td>April 16, 2011</td>\n",
       "      <td>2011-04-16T00:00:00Z</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Column         drugName                     condition  \\\n",
       "0  163740      Mirtazapine                    Depression   \n",
       "1  206473       Mesalamine  Crohn's Disease, Maintenance   \n",
       "2  159672          Bactrim       Urinary Tract Infection   \n",
       "3   39293         Contrave                   Weight Loss   \n",
       "4   97768  Cyclafem 1 / 35                 Birth Control   \n",
       "5  208087          Zyclara                     Keratosis   \n",
       "6  215892           Copper                 Birth Control   \n",
       "7  169852    Amitriptyline           Migraine Prevention   \n",
       "8   23295        Methadone             Opiate Withdrawal   \n",
       "9   71428           Levora                 Birth Control   \n",
       "\n",
       "                                              review  \\\n",
       "0  \"I&#039;ve tried a few antidepressants over th...   \n",
       "1  \"My son has Crohn&#039;s disease and has done ...   \n",
       "2                      \"Quick reduction of symptoms\"   \n",
       "3  \"Contrave combines drugs that were used for al...   \n",
       "4  \"I have been on this birth control for one cyc...   \n",
       "5  \"4 days in on first 2 weeks.  Using on arms an...   \n",
       "6  \"I&#039;ve had the copper coil for about 3 mon...   \n",
       "7  \"This has been great for me. I&#039;ve been on...   \n",
       "8  \"Ive been on Methadone for over ten years and ...   \n",
       "9  \"I was on this pill for almost two years. It d...   \n",
       "\n",
       "                                      review_cleaned  rating  \\\n",
       "0  i&#039;ve tried a few antidepressants over the...      10   \n",
       "1  my son has crohn&#039;s disease and has done v...       8   \n",
       "2                        quick reduction of symptoms       9   \n",
       "3  contrave combines drugs that were used for alc...       9   \n",
       "4  i have been on this birth control for one cycl...       9   \n",
       "5  4 days in on first 2 weeks.  using on arms and...       4   \n",
       "6  i&#039;ve had the copper coil for about 3 mont...       6   \n",
       "7  this has been great for me. i&#039;ve been on ...       9   \n",
       "8  ive been on methadone for over ten years and c...       7   \n",
       "9  i was on this pill for almost two years. it do...       2   \n",
       "\n",
       "                 date          date_cleaned  usefulCount  \n",
       "0   February 28, 2012  2012-02-28T00:00:00Z           22  \n",
       "1        May 17, 2009  2009-05-17T00:00:00Z           17  \n",
       "2  September 29, 2017  2017-09-29T00:00:00Z            3  \n",
       "3       March 5, 2017  2017-03-05T00:00:00Z           35  \n",
       "4    October 22, 2015  2015-10-22T00:00:00Z            4  \n",
       "5        July 3, 2014  2014-07-03T00:00:00Z           13  \n",
       "6        June 6, 2016  2016-06-06T00:00:00Z            1  \n",
       "7      April 21, 2009  2009-04-21T00:00:00Z           32  \n",
       "8    October 18, 2016  2016-10-18T00:00:00Z           21  \n",
       "9      April 16, 2011  2011-04-16T00:00:00Z            3  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "refine_helper.get_rows()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Cluster operation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "review_cluster = drug_project.compute_clusters(\"review_cleaned\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>{u'count': 2, u'value': u'love it'}</td>\n",
       "      <td>{u'count': 2, u'value': u'love it.'}</td>\n",
       "      <td>{u'count': 1, u'value': u' love it'}</td>\n",
       "      <td>{u'count': 1, u'value': u'love it love it!!'}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>{u'count': 7, u'value': u'excellent'}</td>\n",
       "      <td>{u'count': 2, u'value': u'excellent!'}</td>\n",
       "      <td>{u'count': 2, u'value': u'excellent!!'}</td>\n",
       "      <td>{u'count': 2, u'value': u'excellent.'}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>{u'count': 5, u'value': u'it works.'}</td>\n",
       "      <td>{u'count': 3, u'value': u'it works'}</td>\n",
       "      <td>{u'count': 2, u'value': u'it works!!!'}</td>\n",
       "      <td>{u'count': 1, u'value': u'it works!!!!!!'}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>{u'count': 7, u'value': u'works great'}</td>\n",
       "      <td>{u'count': 3, u'value': u'works great!'}</td>\n",
       "      <td>{u'count': 2, u'value': u'works great.'}</td>\n",
       "      <td>{u'count': 1, u'value': u'works great. '}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>{u'count': 1, u'value': u'works for me'}</td>\n",
       "      <td>{u'count': 1, u'value': u'works for me!'}</td>\n",
       "      <td>{u'count': 1, u'value': u'works for me.'}</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>{u'count': 3, u'value': u'very helpful.'}</td>\n",
       "      <td>{u'count': 1, u'value': u'very helpful'}</td>\n",
       "      <td>{u'count': 1, u'value': u'very helpful!!'}</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>{u'count': 8, u'value': u'good'}</td>\n",
       "      <td>{u'count': 8, u'value': u'good.'}</td>\n",
       "      <td>{u'count': 1, u'value': u'good '}</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>{u'count': 6, u'value': u'very good'}</td>\n",
       "      <td>{u'count': 2, u'value': u'very good.'}</td>\n",
       "      <td>{u'count': 1, u'value': u'very good!'}</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>{u'count': 1, u'value': u'so far so good'}</td>\n",
       "      <td>{u'count': 1, u'value': u'so far so good.'}</td>\n",
       "      <td>{u'count': 1, u'value': u'so far, so good.'}</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>{u'count': 1, u'value': u'works very well'}</td>\n",
       "      <td>{u'count': 1, u'value': u'works very well.'}</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>{u'count': 2, u'value': u'works well.'}</td>\n",
       "      <td>{u'count': 1, u'value': u'works well '}</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>{u'count': 1, u'value': u'i love it'}</td>\n",
       "      <td>{u'count': 1, u'value': u'i love it.'}</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>{u'count': 2, u'value': u'i have had chronic p...</td>\n",
       "      <td>{u'count': 1, u'value': u'i have had chronic p...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>{u'count': 1, u'value': u'weight gain'}</td>\n",
       "      <td>{u'count': 1, u'value': u'weight gain.'}</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>{u'count': 1, u'value': u'works every time!'}</td>\n",
       "      <td>{u'count': 1, u'value': u'works every time.'}</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>{u'count': 1, u'value': u'finally after many y...</td>\n",
       "      <td>{u'count': 1, u'value': u'finally after many y...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>{u'count': 1, u'value': u'it works wonders'}</td>\n",
       "      <td>{u'count': 1, u'value': u'it works wonders.'}</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>{u'count': 1, u'value': u'i like it'}</td>\n",
       "      <td>{u'count': 1, u'value': u'i like it.'}</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>{u'count': 1, u'value': u'can&amp;#039;t  live wit...</td>\n",
       "      <td>{u'count': 1, u'value': u'can&amp;#039;t live with...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>{u'count': 3, u'value': u'excellent product.'}</td>\n",
       "      <td>{u'count': 1, u'value': u'excellent product'}</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>{u'count': 4, u'value': u'saved my life.'}</td>\n",
       "      <td>{u'count': 1, u'value': u'saved my life!!!'}</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>{u'count': 2, u'value': u'good medicine'}</td>\n",
       "      <td>{u'count': 1, u'value': u'good medicine.'}</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>{u'count': 2, u'value': u'the best'}</td>\n",
       "      <td>{u'count': 1, u'value': u'the best.'}</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>{u'count': 1, u'value': u'does the job'}</td>\n",
       "      <td>{u'count': 1, u'value': u'does the job.'}</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>{u'count': 1, u'value': u'it worked!'}</td>\n",
       "      <td>{u'count': 1, u'value': u'it worked.'}</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>{u'count': 6, u'value': u'great'}</td>\n",
       "      <td>{u'count': 5, u'value': u'great.'}</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>{u'count': 2, u'value': u'helpful.  '}</td>\n",
       "      <td>{u'count': 1, u'value': u'helpful!'}</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>{u'count': 1, u'value': u'bad side effects'}</td>\n",
       "      <td>{u'count': 1, u'value': u'bad side effects!'}</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>{u'count': 1, u'value': u'works good'}</td>\n",
       "      <td>{u'count': 1, u'value': u'works good.'}</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>{u'count': 1, u'value': u'good results'}</td>\n",
       "      <td>{u'count': 1, u'value': u'good results.'}</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>{u'count': 2, u'value': u'no side effects'}</td>\n",
       "      <td>{u'count': 1, u'value': u'no side effects.'}</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                    0  \\\n",
       "0                 {u'count': 2, u'value': u'love it'}   \n",
       "1               {u'count': 7, u'value': u'excellent'}   \n",
       "2               {u'count': 5, u'value': u'it works.'}   \n",
       "3             {u'count': 7, u'value': u'works great'}   \n",
       "4            {u'count': 1, u'value': u'works for me'}   \n",
       "5           {u'count': 3, u'value': u'very helpful.'}   \n",
       "6                    {u'count': 8, u'value': u'good'}   \n",
       "7               {u'count': 6, u'value': u'very good'}   \n",
       "8          {u'count': 1, u'value': u'so far so good'}   \n",
       "9         {u'count': 1, u'value': u'works very well'}   \n",
       "10            {u'count': 2, u'value': u'works well.'}   \n",
       "11              {u'count': 1, u'value': u'i love it'}   \n",
       "12  {u'count': 2, u'value': u'i have had chronic p...   \n",
       "13            {u'count': 1, u'value': u'weight gain'}   \n",
       "14      {u'count': 1, u'value': u'works every time!'}   \n",
       "15  {u'count': 1, u'value': u'finally after many y...   \n",
       "16       {u'count': 1, u'value': u'it works wonders'}   \n",
       "17              {u'count': 1, u'value': u'i like it'}   \n",
       "18  {u'count': 1, u'value': u'can&#039;t  live wit...   \n",
       "19     {u'count': 3, u'value': u'excellent product.'}   \n",
       "20         {u'count': 4, u'value': u'saved my life.'}   \n",
       "21          {u'count': 2, u'value': u'good medicine'}   \n",
       "22               {u'count': 2, u'value': u'the best'}   \n",
       "23           {u'count': 1, u'value': u'does the job'}   \n",
       "24             {u'count': 1, u'value': u'it worked!'}   \n",
       "25                  {u'count': 6, u'value': u'great'}   \n",
       "26             {u'count': 2, u'value': u'helpful.  '}   \n",
       "27       {u'count': 1, u'value': u'bad side effects'}   \n",
       "28             {u'count': 1, u'value': u'works good'}   \n",
       "29           {u'count': 1, u'value': u'good results'}   \n",
       "30        {u'count': 2, u'value': u'no side effects'}   \n",
       "\n",
       "                                                    1  \\\n",
       "0                {u'count': 2, u'value': u'love it.'}   \n",
       "1              {u'count': 2, u'value': u'excellent!'}   \n",
       "2                {u'count': 3, u'value': u'it works'}   \n",
       "3            {u'count': 3, u'value': u'works great!'}   \n",
       "4           {u'count': 1, u'value': u'works for me!'}   \n",
       "5            {u'count': 1, u'value': u'very helpful'}   \n",
       "6                   {u'count': 8, u'value': u'good.'}   \n",
       "7              {u'count': 2, u'value': u'very good.'}   \n",
       "8         {u'count': 1, u'value': u'so far so good.'}   \n",
       "9        {u'count': 1, u'value': u'works very well.'}   \n",
       "10            {u'count': 1, u'value': u'works well '}   \n",
       "11             {u'count': 1, u'value': u'i love it.'}   \n",
       "12  {u'count': 1, u'value': u'i have had chronic p...   \n",
       "13           {u'count': 1, u'value': u'weight gain.'}   \n",
       "14      {u'count': 1, u'value': u'works every time.'}   \n",
       "15  {u'count': 1, u'value': u'finally after many y...   \n",
       "16      {u'count': 1, u'value': u'it works wonders.'}   \n",
       "17             {u'count': 1, u'value': u'i like it.'}   \n",
       "18  {u'count': 1, u'value': u'can&#039;t live with...   \n",
       "19      {u'count': 1, u'value': u'excellent product'}   \n",
       "20       {u'count': 1, u'value': u'saved my life!!!'}   \n",
       "21         {u'count': 1, u'value': u'good medicine.'}   \n",
       "22              {u'count': 1, u'value': u'the best.'}   \n",
       "23          {u'count': 1, u'value': u'does the job.'}   \n",
       "24             {u'count': 1, u'value': u'it worked.'}   \n",
       "25                 {u'count': 5, u'value': u'great.'}   \n",
       "26               {u'count': 1, u'value': u'helpful!'}   \n",
       "27      {u'count': 1, u'value': u'bad side effects!'}   \n",
       "28            {u'count': 1, u'value': u'works good.'}   \n",
       "29          {u'count': 1, u'value': u'good results.'}   \n",
       "30       {u'count': 1, u'value': u'no side effects.'}   \n",
       "\n",
       "                                               2  \\\n",
       "0           {u'count': 1, u'value': u' love it'}   \n",
       "1        {u'count': 2, u'value': u'excellent!!'}   \n",
       "2        {u'count': 2, u'value': u'it works!!!'}   \n",
       "3       {u'count': 2, u'value': u'works great.'}   \n",
       "4      {u'count': 1, u'value': u'works for me.'}   \n",
       "5     {u'count': 1, u'value': u'very helpful!!'}   \n",
       "6              {u'count': 1, u'value': u'good '}   \n",
       "7         {u'count': 1, u'value': u'very good!'}   \n",
       "8   {u'count': 1, u'value': u'so far, so good.'}   \n",
       "9                                           None   \n",
       "10                                          None   \n",
       "11                                          None   \n",
       "12                                          None   \n",
       "13                                          None   \n",
       "14                                          None   \n",
       "15                                          None   \n",
       "16                                          None   \n",
       "17                                          None   \n",
       "18                                          None   \n",
       "19                                          None   \n",
       "20                                          None   \n",
       "21                                          None   \n",
       "22                                          None   \n",
       "23                                          None   \n",
       "24                                          None   \n",
       "25                                          None   \n",
       "26                                          None   \n",
       "27                                          None   \n",
       "28                                          None   \n",
       "29                                          None   \n",
       "30                                          None   \n",
       "\n",
       "                                                3  \n",
       "0   {u'count': 1, u'value': u'love it love it!!'}  \n",
       "1          {u'count': 2, u'value': u'excellent.'}  \n",
       "2      {u'count': 1, u'value': u'it works!!!!!!'}  \n",
       "3       {u'count': 1, u'value': u'works great. '}  \n",
       "4                                            None  \n",
       "5                                            None  \n",
       "6                                            None  \n",
       "7                                            None  \n",
       "8                                            None  \n",
       "9                                            None  \n",
       "10                                           None  \n",
       "11                                           None  \n",
       "12                                           None  \n",
       "13                                           None  \n",
       "14                                           None  \n",
       "15                                           None  \n",
       "16                                           None  \n",
       "17                                           None  \n",
       "18                                           None  \n",
       "19                                           None  \n",
       "20                                           None  \n",
       "21                                           None  \n",
       "22                                           None  \n",
       "23                                           None  \n",
       "24                                           None  \n",
       "25                                           None  \n",
       "26                                           None  \n",
       "27                                           None  \n",
       "28                                           None  \n",
       "29                                           None  \n",
       "30                                           None  "
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame(review_cluster)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## merge cluster 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[u'love it', u'love it.', u' love it', u'love it love it!!']"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cluster_1 = [ x[\"value\"] for x in review_cluster[0]]\n",
    "cluster_1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{u'code': u'ok',\n",
       " u'historyEntry': {u'description': u'Mass edit 6 cells in column review_cleaned',\n",
       "  u'id': 1557335703149,\n",
       "  u'time': u'2019-05-08T17:07:32Z'}}"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "drug_project.mass_edit(\"review_cleaned\",edits=[{'from':cluster_1,'to':\"love it\"}])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### use refine helper to show new cluster"
   ]
  },
  {
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>{u'count': 7, u'value': u'excellent'}</td>\n",
       "      <td>{u'count': 2, u'value': u'excellent!'}</td>\n",
       "      <td>{u'count': 2, u'value': u'excellent!!'}</td>\n",
       "      <td>{u'count': 2, u'value': u'excellent.'}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>{u'count': 5, u'value': u'it works.'}</td>\n",
       "      <td>{u'count': 3, u'value': u'it works'}</td>\n",
       "      <td>{u'count': 2, u'value': u'it works!!!'}</td>\n",
       "      <td>{u'count': 1, u'value': u'it works!!!!!!'}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>{u'count': 7, u'value': u'works great'}</td>\n",
       "      <td>{u'count': 3, u'value': u'works great!'}</td>\n",
       "      <td>{u'count': 2, u'value': u'works great.'}</td>\n",
       "      <td>{u'count': 1, u'value': u'works great. '}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>{u'count': 1, u'value': u'works for me'}</td>\n",
       "      <td>{u'count': 1, u'value': u'works for me!'}</td>\n",
       "      <td>{u'count': 1, u'value': u'works for me.'}</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>{u'count': 3, u'value': u'very helpful.'}</td>\n",
       "      <td>{u'count': 1, u'value': u'very helpful'}</td>\n",
       "      <td>{u'count': 1, u'value': u'very helpful!!'}</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>{u'count': 8, u'value': u'good'}</td>\n",
       "      <td>{u'count': 8, u'value': u'good.'}</td>\n",
       "      <td>{u'count': 1, u'value': u'good '}</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>{u'count': 6, u'value': u'very good'}</td>\n",
       "      <td>{u'count': 2, u'value': u'very good.'}</td>\n",
       "      <td>{u'count': 1, u'value': u'very good!'}</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>{u'count': 1, u'value': u'so far so good'}</td>\n",
       "      <td>{u'count': 1, u'value': u'so far so good.'}</td>\n",
       "      <td>{u'count': 1, u'value': u'so far, so good.'}</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>{u'count': 1, u'value': u'works very well'}</td>\n",
       "      <td>{u'count': 1, u'value': u'works very well.'}</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>{u'count': 2, u'value': u'works well.'}</td>\n",
       "      <td>{u'count': 1, u'value': u'works well '}</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>{u'count': 1, u'value': u'i love it'}</td>\n",
       "      <td>{u'count': 1, u'value': u'i love it.'}</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>{u'count': 2, u'value': u'i have had chronic p...</td>\n",
       "      <td>{u'count': 1, u'value': u'i have had chronic p...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>{u'count': 1, u'value': u'weight gain'}</td>\n",
       "      <td>{u'count': 1, u'value': u'weight gain.'}</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>{u'count': 1, u'value': u'works every time!'}</td>\n",
       "      <td>{u'count': 1, u'value': u'works every time.'}</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>{u'count': 1, u'value': u'finally after many y...</td>\n",
       "      <td>{u'count': 1, u'value': u'finally after many y...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>{u'count': 1, u'value': u'it works wonders'}</td>\n",
       "      <td>{u'count': 1, u'value': u'it works wonders.'}</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>{u'count': 1, u'value': u'i like it'}</td>\n",
       "      <td>{u'count': 1, u'value': u'i like it.'}</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>{u'count': 1, u'value': u'can&amp;#039;t  live wit...</td>\n",
       "      <td>{u'count': 1, u'value': u'can&amp;#039;t live with...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>{u'count': 3, u'value': u'excellent product.'}</td>\n",
       "      <td>{u'count': 1, u'value': u'excellent product'}</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>{u'count': 4, u'value': u'saved my life.'}</td>\n",
       "      <td>{u'count': 1, u'value': u'saved my life!!!'}</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>{u'count': 2, u'value': u'good medicine'}</td>\n",
       "      <td>{u'count': 1, u'value': u'good medicine.'}</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>{u'count': 2, u'value': u'the best'}</td>\n",
       "      <td>{u'count': 1, u'value': u'the best.'}</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>{u'count': 1, u'value': u'does the job'}</td>\n",
       "      <td>{u'count': 1, u'value': u'does the job.'}</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>{u'count': 1, u'value': u'it worked!'}</td>\n",
       "      <td>{u'count': 1, u'value': u'it worked.'}</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>{u'count': 6, u'value': u'great'}</td>\n",
       "      <td>{u'count': 5, u'value': u'great.'}</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>{u'count': 2, u'value': u'helpful.  '}</td>\n",
       "      <td>{u'count': 1, u'value': u'helpful!'}</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>{u'count': 1, u'value': u'bad side effects'}</td>\n",
       "      <td>{u'count': 1, u'value': u'bad side effects!'}</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>{u'count': 1, u'value': u'works good'}</td>\n",
       "      <td>{u'count': 1, u'value': u'works good.'}</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>{u'count': 1, u'value': u'good results'}</td>\n",
       "      <td>{u'count': 1, u'value': u'good results.'}</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>{u'count': 2, u'value': u'no side effects'}</td>\n",
       "      <td>{u'count': 1, u'value': u'no side effects.'}</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                    0  \\\n",
       "0               {u'count': 7, u'value': u'excellent'}   \n",
       "1               {u'count': 5, u'value': u'it works.'}   \n",
       "2             {u'count': 7, u'value': u'works great'}   \n",
       "3            {u'count': 1, u'value': u'works for me'}   \n",
       "4           {u'count': 3, u'value': u'very helpful.'}   \n",
       "5                    {u'count': 8, u'value': u'good'}   \n",
       "6               {u'count': 6, u'value': u'very good'}   \n",
       "7          {u'count': 1, u'value': u'so far so good'}   \n",
       "8         {u'count': 1, u'value': u'works very well'}   \n",
       "9             {u'count': 2, u'value': u'works well.'}   \n",
       "10              {u'count': 1, u'value': u'i love it'}   \n",
       "11  {u'count': 2, u'value': u'i have had chronic p...   \n",
       "12            {u'count': 1, u'value': u'weight gain'}   \n",
       "13      {u'count': 1, u'value': u'works every time!'}   \n",
       "14  {u'count': 1, u'value': u'finally after many y...   \n",
       "15       {u'count': 1, u'value': u'it works wonders'}   \n",
       "16              {u'count': 1, u'value': u'i like it'}   \n",
       "17  {u'count': 1, u'value': u'can&#039;t  live wit...   \n",
       "18     {u'count': 3, u'value': u'excellent product.'}   \n",
       "19         {u'count': 4, u'value': u'saved my life.'}   \n",
       "20          {u'count': 2, u'value': u'good medicine'}   \n",
       "21               {u'count': 2, u'value': u'the best'}   \n",
       "22           {u'count': 1, u'value': u'does the job'}   \n",
       "23             {u'count': 1, u'value': u'it worked!'}   \n",
       "24                  {u'count': 6, u'value': u'great'}   \n",
       "25             {u'count': 2, u'value': u'helpful.  '}   \n",
       "26       {u'count': 1, u'value': u'bad side effects'}   \n",
       "27             {u'count': 1, u'value': u'works good'}   \n",
       "28           {u'count': 1, u'value': u'good results'}   \n",
       "29        {u'count': 2, u'value': u'no side effects'}   \n",
       "\n",
       "                                                    1  \\\n",
       "0              {u'count': 2, u'value': u'excellent!'}   \n",
       "1                {u'count': 3, u'value': u'it works'}   \n",
       "2            {u'count': 3, u'value': u'works great!'}   \n",
       "3           {u'count': 1, u'value': u'works for me!'}   \n",
       "4            {u'count': 1, u'value': u'very helpful'}   \n",
       "5                   {u'count': 8, u'value': u'good.'}   \n",
       "6              {u'count': 2, u'value': u'very good.'}   \n",
       "7         {u'count': 1, u'value': u'so far so good.'}   \n",
       "8        {u'count': 1, u'value': u'works very well.'}   \n",
       "9             {u'count': 1, u'value': u'works well '}   \n",
       "10             {u'count': 1, u'value': u'i love it.'}   \n",
       "11  {u'count': 1, u'value': u'i have had chronic p...   \n",
       "12           {u'count': 1, u'value': u'weight gain.'}   \n",
       "13      {u'count': 1, u'value': u'works every time.'}   \n",
       "14  {u'count': 1, u'value': u'finally after many y...   \n",
       "15      {u'count': 1, u'value': u'it works wonders.'}   \n",
       "16             {u'count': 1, u'value': u'i like it.'}   \n",
       "17  {u'count': 1, u'value': u'can&#039;t live with...   \n",
       "18      {u'count': 1, u'value': u'excellent product'}   \n",
       "19       {u'count': 1, u'value': u'saved my life!!!'}   \n",
       "20         {u'count': 1, u'value': u'good medicine.'}   \n",
       "21              {u'count': 1, u'value': u'the best.'}   \n",
       "22          {u'count': 1, u'value': u'does the job.'}   \n",
       "23             {u'count': 1, u'value': u'it worked.'}   \n",
       "24                 {u'count': 5, u'value': u'great.'}   \n",
       "25               {u'count': 1, u'value': u'helpful!'}   \n",
       "26      {u'count': 1, u'value': u'bad side effects!'}   \n",
       "27            {u'count': 1, u'value': u'works good.'}   \n",
       "28          {u'count': 1, u'value': u'good results.'}   \n",
       "29       {u'count': 1, u'value': u'no side effects.'}   \n",
       "\n",
       "                                               2  \\\n",
       "0        {u'count': 2, u'value': u'excellent!!'}   \n",
       "1        {u'count': 2, u'value': u'it works!!!'}   \n",
       "2       {u'count': 2, u'value': u'works great.'}   \n",
       "3      {u'count': 1, u'value': u'works for me.'}   \n",
       "4     {u'count': 1, u'value': u'very helpful!!'}   \n",
       "5              {u'count': 1, u'value': u'good '}   \n",
       "6         {u'count': 1, u'value': u'very good!'}   \n",
       "7   {u'count': 1, u'value': u'so far, so good.'}   \n",
       "8                                           None   \n",
       "9                                           None   \n",
       "10                                          None   \n",
       "11                                          None   \n",
       "12                                          None   \n",
       "13                                          None   \n",
       "14                                          None   \n",
       "15                                          None   \n",
       "16                                          None   \n",
       "17                                          None   \n",
       "18                                          None   \n",
       "19                                          None   \n",
       "20                                          None   \n",
       "21                                          None   \n",
       "22                                          None   \n",
       "23                                          None   \n",
       "24                                          None   \n",
       "25                                          None   \n",
       "26                                          None   \n",
       "27                                          None   \n",
       "28                                          None   \n",
       "29                                          None   \n",
       "\n",
       "                                             3  \n",
       "0       {u'count': 2, u'value': u'excellent.'}  \n",
       "1   {u'count': 1, u'value': u'it works!!!!!!'}  \n",
       "2    {u'count': 1, u'value': u'works great. '}  \n",
       "3                                         None  \n",
       "4                                         None  \n",
       "5                                         None  \n",
       "6                                         None  \n",
       "7                                         None  \n",
       "8                                         None  \n",
       "9                                         None  \n",
       "10                                        None  \n",
       "11                                        None  \n",
       "12                                        None  \n",
       "13                                        None  \n",
       "14                                        None  \n",
       "15                                        None  \n",
       "16                                        None  \n",
       "17                                        None  \n",
       "18                                        None  \n",
       "19                                        None  \n",
       "20                                        None  \n",
       "21                                        None  \n",
       "22                                        None  \n",
       "23                                        None  \n",
       "24                                        None  \n",
       "25                                        None  \n",
       "26                                        None  \n",
       "27                                        None  \n",
       "28                                        None  \n",
       "29                                        None  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "refine_helper.cluster(\"review_cleaned\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Play with History"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{u'future': [],\n",
       " u'past': [{u'description': u'Create new column review_cleaned based on column review by filling 53766 rows with value',\n",
       "   u'id': 1557335992383,\n",
       "   u'time': u'2019-05-08T17:07:10Z'},\n",
       "  {u'description': u'Text transform on 53542 cells in column review_cleaned: value.toLowercase()',\n",
       "   u'id': 1557335540561,\n",
       "   u'time': u'2019-05-08T17:07:14Z'},\n",
       "  {u'description': u'Text transform on 53766 cells in column review_cleaned: value.replace(/^\"/,\"\")',\n",
       "   u'id': 1557336194092,\n",
       "   u'time': u'2019-05-08T17:07:19Z'},\n",
       "  {u'description': u'Text transform on 53766 cells in column review_cleaned: value.replace(/\"$/,\"\")',\n",
       "   u'id': 1557335344379,\n",
       "   u'time': u'2019-05-08T17:07:23Z'},\n",
       "  {u'description': u'Create new column date_cleaned based on column date by filling 53766 rows with value',\n",
       "   u'id': 1557335452682,\n",
       "   u'time': u'2019-05-08T17:07:28Z'},\n",
       "  {u'description': u'Text transform on 53766 cells in column date_cleaned: value.toDate()',\n",
       "   u'id': 1557335476122,\n",
       "   u'time': u'2019-05-08T17:07:29Z'},\n",
       "  {u'description': u'Mass edit 6 cells in column review_cleaned',\n",
       "   u'id': 1557335703149,\n",
       "   u'time': u'2019-05-08T17:07:32Z'}]}"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "drug_project.list_history()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{u'code': u'ok',\n",
       " u'historyEntry': {u'description': u'Create new column review_test_history based on column review by filling 53766 rows with value',\n",
       "  u'id': 1557335436652,\n",
       "  u'time': u'2019-05-08T17:07:35Z'}}"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "drug_project.add_column(column=\"review\",new_column=\"review_test_history\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "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>Column</th>\n",
       "      <th>drugName</th>\n",
       "      <th>condition</th>\n",
       "      <th>review</th>\n",
       "      <th>review_test_history</th>\n",
       "      <th>review_cleaned</th>\n",
       "      <th>rating</th>\n",
       "      <th>date</th>\n",
       "      <th>date_cleaned</th>\n",
       "      <th>usefulCount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>163740</td>\n",
       "      <td>Mirtazapine</td>\n",
       "      <td>Depression</td>\n",
       "      <td>\"I&amp;#039;ve tried a few antidepressants over th...</td>\n",
       "      <td>\"I&amp;#039;ve tried a few antidepressants over th...</td>\n",
       "      <td>i&amp;#039;ve tried a few antidepressants over the...</td>\n",
       "      <td>10</td>\n",
       "      <td>February 28, 2012</td>\n",
       "      <td>2012-02-28T00:00:00Z</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>206473</td>\n",
       "      <td>Mesalamine</td>\n",
       "      <td>Crohn's Disease, Maintenance</td>\n",
       "      <td>\"My son has Crohn&amp;#039;s disease and has done ...</td>\n",
       "      <td>\"My son has Crohn&amp;#039;s disease and has done ...</td>\n",
       "      <td>my son has crohn&amp;#039;s disease and has done v...</td>\n",
       "      <td>8</td>\n",
       "      <td>May 17, 2009</td>\n",
       "      <td>2009-05-17T00:00:00Z</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>159672</td>\n",
       "      <td>Bactrim</td>\n",
       "      <td>Urinary Tract Infection</td>\n",
       "      <td>\"Quick reduction of symptoms\"</td>\n",
       "      <td>\"Quick reduction of symptoms\"</td>\n",
       "      <td>quick reduction of symptoms</td>\n",
       "      <td>9</td>\n",
       "      <td>September 29, 2017</td>\n",
       "      <td>2017-09-29T00:00:00Z</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>39293</td>\n",
       "      <td>Contrave</td>\n",
       "      <td>Weight Loss</td>\n",
       "      <td>\"Contrave combines drugs that were used for al...</td>\n",
       "      <td>\"Contrave combines drugs that were used for al...</td>\n",
       "      <td>contrave combines drugs that were used for alc...</td>\n",
       "      <td>9</td>\n",
       "      <td>March 5, 2017</td>\n",
       "      <td>2017-03-05T00:00:00Z</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>97768</td>\n",
       "      <td>Cyclafem 1 / 35</td>\n",
       "      <td>Birth Control</td>\n",
       "      <td>\"I have been on this birth control for one cyc...</td>\n",
       "      <td>\"I have been on this birth control for one cyc...</td>\n",
       "      <td>i have been on this birth control for one cycl...</td>\n",
       "      <td>9</td>\n",
       "      <td>October 22, 2015</td>\n",
       "      <td>2015-10-22T00:00:00Z</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>208087</td>\n",
       "      <td>Zyclara</td>\n",
       "      <td>Keratosis</td>\n",
       "      <td>\"4 days in on first 2 weeks.  Using on arms an...</td>\n",
       "      <td>\"4 days in on first 2 weeks.  Using on arms an...</td>\n",
       "      <td>4 days in on first 2 weeks.  using on arms and...</td>\n",
       "      <td>4</td>\n",
       "      <td>July 3, 2014</td>\n",
       "      <td>2014-07-03T00:00:00Z</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>215892</td>\n",
       "      <td>Copper</td>\n",
       "      <td>Birth Control</td>\n",
       "      <td>\"I&amp;#039;ve had the copper coil for about 3 mon...</td>\n",
       "      <td>\"I&amp;#039;ve had the copper coil for about 3 mon...</td>\n",
       "      <td>i&amp;#039;ve had the copper coil for about 3 mont...</td>\n",
       "      <td>6</td>\n",
       "      <td>June 6, 2016</td>\n",
       "      <td>2016-06-06T00:00:00Z</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>169852</td>\n",
       "      <td>Amitriptyline</td>\n",
       "      <td>Migraine Prevention</td>\n",
       "      <td>\"This has been great for me. I&amp;#039;ve been on...</td>\n",
       "      <td>\"This has been great for me. I&amp;#039;ve been on...</td>\n",
       "      <td>this has been great for me. i&amp;#039;ve been on ...</td>\n",
       "      <td>9</td>\n",
       "      <td>April 21, 2009</td>\n",
       "      <td>2009-04-21T00:00:00Z</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>23295</td>\n",
       "      <td>Methadone</td>\n",
       "      <td>Opiate Withdrawal</td>\n",
       "      <td>\"Ive been on Methadone for over ten years and ...</td>\n",
       "      <td>\"Ive been on Methadone for over ten years and ...</td>\n",
       "      <td>ive been on methadone for over ten years and c...</td>\n",
       "      <td>7</td>\n",
       "      <td>October 18, 2016</td>\n",
       "      <td>2016-10-18T00:00:00Z</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>71428</td>\n",
       "      <td>Levora</td>\n",
       "      <td>Birth Control</td>\n",
       "      <td>\"I was on this pill for almost two years. It d...</td>\n",
       "      <td>\"I was on this pill for almost two years. It d...</td>\n",
       "      <td>i was on this pill for almost two years. it do...</td>\n",
       "      <td>2</td>\n",
       "      <td>April 16, 2011</td>\n",
       "      <td>2011-04-16T00:00:00Z</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Column         drugName                     condition  \\\n",
       "0  163740      Mirtazapine                    Depression   \n",
       "1  206473       Mesalamine  Crohn's Disease, Maintenance   \n",
       "2  159672          Bactrim       Urinary Tract Infection   \n",
       "3   39293         Contrave                   Weight Loss   \n",
       "4   97768  Cyclafem 1 / 35                 Birth Control   \n",
       "5  208087          Zyclara                     Keratosis   \n",
       "6  215892           Copper                 Birth Control   \n",
       "7  169852    Amitriptyline           Migraine Prevention   \n",
       "8   23295        Methadone             Opiate Withdrawal   \n",
       "9   71428           Levora                 Birth Control   \n",
       "\n",
       "                                              review  \\\n",
       "0  \"I&#039;ve tried a few antidepressants over th...   \n",
       "1  \"My son has Crohn&#039;s disease and has done ...   \n",
       "2                      \"Quick reduction of symptoms\"   \n",
       "3  \"Contrave combines drugs that were used for al...   \n",
       "4  \"I have been on this birth control for one cyc...   \n",
       "5  \"4 days in on first 2 weeks.  Using on arms an...   \n",
       "6  \"I&#039;ve had the copper coil for about 3 mon...   \n",
       "7  \"This has been great for me. I&#039;ve been on...   \n",
       "8  \"Ive been on Methadone for over ten years and ...   \n",
       "9  \"I was on this pill for almost two years. It d...   \n",
       "\n",
       "                                 review_test_history  \\\n",
       "0  \"I&#039;ve tried a few antidepressants over th...   \n",
       "1  \"My son has Crohn&#039;s disease and has done ...   \n",
       "2                      \"Quick reduction of symptoms\"   \n",
       "3  \"Contrave combines drugs that were used for al...   \n",
       "4  \"I have been on this birth control for one cyc...   \n",
       "5  \"4 days in on first 2 weeks.  Using on arms an...   \n",
       "6  \"I&#039;ve had the copper coil for about 3 mon...   \n",
       "7  \"This has been great for me. I&#039;ve been on...   \n",
       "8  \"Ive been on Methadone for over ten years and ...   \n",
       "9  \"I was on this pill for almost two years. It d...   \n",
       "\n",
       "                                      review_cleaned  rating  \\\n",
       "0  i&#039;ve tried a few antidepressants over the...      10   \n",
       "1  my son has crohn&#039;s disease and has done v...       8   \n",
       "2                        quick reduction of symptoms       9   \n",
       "3  contrave combines drugs that were used for alc...       9   \n",
       "4  i have been on this birth control for one cycl...       9   \n",
       "5  4 days in on first 2 weeks.  using on arms and...       4   \n",
       "6  i&#039;ve had the copper coil for about 3 mont...       6   \n",
       "7  this has been great for me. i&#039;ve been on ...       9   \n",
       "8  ive been on methadone for over ten years and c...       7   \n",
       "9  i was on this pill for almost two years. it do...       2   \n",
       "\n",
       "                 date          date_cleaned  usefulCount  \n",
       "0   February 28, 2012  2012-02-28T00:00:00Z           22  \n",
       "1        May 17, 2009  2009-05-17T00:00:00Z           17  \n",
       "2  September 29, 2017  2017-09-29T00:00:00Z            3  \n",
       "3       March 5, 2017  2017-03-05T00:00:00Z           35  \n",
       "4    October 22, 2015  2015-10-22T00:00:00Z            4  \n",
       "5        July 3, 2014  2014-07-03T00:00:00Z           13  \n",
       "6        June 6, 2016  2016-06-06T00:00:00Z            1  \n",
       "7      April 21, 2009  2009-04-21T00:00:00Z           32  \n",
       "8    October 18, 2016  2016-10-18T00:00:00Z           21  \n",
       "9      April 16, 2011  2011-04-16T00:00:00Z            3  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "refine_helper.get_rows()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{u'future': [],\n",
       " u'past': [{u'description': u'Create new column review_cleaned based on column review by filling 53766 rows with value',\n",
       "   u'id': 1557335992383,\n",
       "   u'time': u'2019-05-08T17:07:10Z'},\n",
       "  {u'description': u'Text transform on 53542 cells in column review_cleaned: value.toLowercase()',\n",
       "   u'id': 1557335540561,\n",
       "   u'time': u'2019-05-08T17:07:14Z'},\n",
       "  {u'description': u'Text transform on 53766 cells in column review_cleaned: value.replace(/^\"/,\"\")',\n",
       "   u'id': 1557336194092,\n",
       "   u'time': u'2019-05-08T17:07:19Z'},\n",
       "  {u'description': u'Text transform on 53766 cells in column review_cleaned: value.replace(/\"$/,\"\")',\n",
       "   u'id': 1557335344379,\n",
       "   u'time': u'2019-05-08T17:07:23Z'},\n",
       "  {u'description': u'Create new column date_cleaned based on column date by filling 53766 rows with value',\n",
       "   u'id': 1557335452682,\n",
       "   u'time': u'2019-05-08T17:07:28Z'},\n",
       "  {u'description': u'Text transform on 53766 cells in column date_cleaned: value.toDate()',\n",
       "   u'id': 1557335476122,\n",
       "   u'time': u'2019-05-08T17:07:29Z'},\n",
       "  {u'description': u'Mass edit 6 cells in column review_cleaned',\n",
       "   u'id': 1557335703149,\n",
       "   u'time': u'2019-05-08T17:07:32Z'},\n",
       "  {u'description': u'Create new column review_test_history based on column review by filling 53766 rows with value',\n",
       "   u'id': 1557335436652,\n",
       "   u'time': u'2019-05-08T17:07:35Z'}]}"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "drug_project.list_history()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "drug_project.undo_project(history_id=drug_project.list_history()[\"past\"][-2][\"id\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{u'future': [{u'description': u'Create new column review_test_history based on column review by filling 53766 rows with value',\n",
       "   u'id': 1557335436652,\n",
       "   u'time': u'2019-05-08T17:07:35Z'}],\n",
       " u'past': [{u'description': u'Create new column review_cleaned based on column review by filling 53766 rows with value',\n",
       "   u'id': 1557335992383,\n",
       "   u'time': u'2019-05-08T17:07:10Z'},\n",
       "  {u'description': u'Text transform on 53542 cells in column review_cleaned: value.toLowercase()',\n",
       "   u'id': 1557335540561,\n",
       "   u'time': u'2019-05-08T17:07:14Z'},\n",
       "  {u'description': u'Text transform on 53766 cells in column review_cleaned: value.replace(/^\"/,\"\")',\n",
       "   u'id': 1557336194092,\n",
       "   u'time': u'2019-05-08T17:07:19Z'},\n",
       "  {u'description': u'Text transform on 53766 cells in column review_cleaned: value.replace(/\"$/,\"\")',\n",
       "   u'id': 1557335344379,\n",
       "   u'time': u'2019-05-08T17:07:23Z'},\n",
       "  {u'description': u'Create new column date_cleaned based on column date by filling 53766 rows with value',\n",
       "   u'id': 1557335452682,\n",
       "   u'time': u'2019-05-08T17:07:28Z'},\n",
       "  {u'description': u'Text transform on 53766 cells in column date_cleaned: value.toDate()',\n",
       "   u'id': 1557335476122,\n",
       "   u'time': u'2019-05-08T17:07:29Z'},\n",
       "  {u'description': u'Mass edit 6 cells in column review_cleaned',\n",
       "   u'id': 1557335703149,\n",
       "   u'time': u'2019-05-08T17:07:32Z'}]}"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "drug_project.list_history()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{u'code': u'ok',\n",
       " u'historyEntry': {u'description': u'Create new column review_test_history_new based on column review by filling 53766 rows with value',\n",
       "  u'id': 1557335392833,\n",
       "  u'time': u'2019-05-08T17:07:39Z'}}"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "drug_project.add_column(column=\"review\",new_column=\"review_test_history_new\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{u'future': [],\n",
       " u'past': [{u'description': u'Create new column review_cleaned based on column review by filling 53766 rows with value',\n",
       "   u'id': 1557335992383,\n",
       "   u'time': u'2019-05-08T17:07:10Z'},\n",
       "  {u'description': u'Text transform on 53542 cells in column review_cleaned: value.toLowercase()',\n",
       "   u'id': 1557335540561,\n",
       "   u'time': u'2019-05-08T17:07:14Z'},\n",
       "  {u'description': u'Text transform on 53766 cells in column review_cleaned: value.replace(/^\"/,\"\")',\n",
       "   u'id': 1557336194092,\n",
       "   u'time': u'2019-05-08T17:07:19Z'},\n",
       "  {u'description': u'Text transform on 53766 cells in column review_cleaned: value.replace(/\"$/,\"\")',\n",
       "   u'id': 1557335344379,\n",
       "   u'time': u'2019-05-08T17:07:23Z'},\n",
       "  {u'description': u'Create new column date_cleaned based on column date by filling 53766 rows with value',\n",
       "   u'id': 1557335452682,\n",
       "   u'time': u'2019-05-08T17:07:28Z'},\n",
       "  {u'description': u'Text transform on 53766 cells in column date_cleaned: value.toDate()',\n",
       "   u'id': 1557335476122,\n",
       "   u'time': u'2019-05-08T17:07:29Z'},\n",
       "  {u'description': u'Mass edit 6 cells in column review_cleaned',\n",
       "   u'id': 1557335703149,\n",
       "   u'time': u'2019-05-08T17:07:32Z'},\n",
       "  {u'description': u'Create new column review_test_history_new based on column review by filling 53766 rows with value',\n",
       "   u'id': 1557335392833,\n",
       "   u'time': u'2019-05-08T17:07:39Z'}]}"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "drug_project.list_history()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "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>Column</th>\n",
       "      <th>drugName</th>\n",
       "      <th>condition</th>\n",
       "      <th>review</th>\n",
       "      <th>review_test_history_new</th>\n",
       "      <th>review_cleaned</th>\n",
       "      <th>rating</th>\n",
       "      <th>date</th>\n",
       "      <th>date_cleaned</th>\n",
       "      <th>usefulCount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>163740</td>\n",
       "      <td>Mirtazapine</td>\n",
       "      <td>Depression</td>\n",
       "      <td>\"I&amp;#039;ve tried a few antidepressants over th...</td>\n",
       "      <td>\"I&amp;#039;ve tried a few antidepressants over th...</td>\n",
       "      <td>i&amp;#039;ve tried a few antidepressants over the...</td>\n",
       "      <td>10</td>\n",
       "      <td>February 28, 2012</td>\n",
       "      <td>2012-02-28T00:00:00Z</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>206473</td>\n",
       "      <td>Mesalamine</td>\n",
       "      <td>Crohn's Disease, Maintenance</td>\n",
       "      <td>\"My son has Crohn&amp;#039;s disease and has done ...</td>\n",
       "      <td>\"My son has Crohn&amp;#039;s disease and has done ...</td>\n",
       "      <td>my son has crohn&amp;#039;s disease and has done v...</td>\n",
       "      <td>8</td>\n",
       "      <td>May 17, 2009</td>\n",
       "      <td>2009-05-17T00:00:00Z</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>159672</td>\n",
       "      <td>Bactrim</td>\n",
       "      <td>Urinary Tract Infection</td>\n",
       "      <td>\"Quick reduction of symptoms\"</td>\n",
       "      <td>\"Quick reduction of symptoms\"</td>\n",
       "      <td>quick reduction of symptoms</td>\n",
       "      <td>9</td>\n",
       "      <td>September 29, 2017</td>\n",
       "      <td>2017-09-29T00:00:00Z</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>39293</td>\n",
       "      <td>Contrave</td>\n",
       "      <td>Weight Loss</td>\n",
       "      <td>\"Contrave combines drugs that were used for al...</td>\n",
       "      <td>\"Contrave combines drugs that were used for al...</td>\n",
       "      <td>contrave combines drugs that were used for alc...</td>\n",
       "      <td>9</td>\n",
       "      <td>March 5, 2017</td>\n",
       "      <td>2017-03-05T00:00:00Z</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>97768</td>\n",
       "      <td>Cyclafem 1 / 35</td>\n",
       "      <td>Birth Control</td>\n",
       "      <td>\"I have been on this birth control for one cyc...</td>\n",
       "      <td>\"I have been on this birth control for one cyc...</td>\n",
       "      <td>i have been on this birth control for one cycl...</td>\n",
       "      <td>9</td>\n",
       "      <td>October 22, 2015</td>\n",
       "      <td>2015-10-22T00:00:00Z</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>208087</td>\n",
       "      <td>Zyclara</td>\n",
       "      <td>Keratosis</td>\n",
       "      <td>\"4 days in on first 2 weeks.  Using on arms an...</td>\n",
       "      <td>\"4 days in on first 2 weeks.  Using on arms an...</td>\n",
       "      <td>4 days in on first 2 weeks.  using on arms and...</td>\n",
       "      <td>4</td>\n",
       "      <td>July 3, 2014</td>\n",
       "      <td>2014-07-03T00:00:00Z</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>215892</td>\n",
       "      <td>Copper</td>\n",
       "      <td>Birth Control</td>\n",
       "      <td>\"I&amp;#039;ve had the copper coil for about 3 mon...</td>\n",
       "      <td>\"I&amp;#039;ve had the copper coil for about 3 mon...</td>\n",
       "      <td>i&amp;#039;ve had the copper coil for about 3 mont...</td>\n",
       "      <td>6</td>\n",
       "      <td>June 6, 2016</td>\n",
       "      <td>2016-06-06T00:00:00Z</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>169852</td>\n",
       "      <td>Amitriptyline</td>\n",
       "      <td>Migraine Prevention</td>\n",
       "      <td>\"This has been great for me. I&amp;#039;ve been on...</td>\n",
       "      <td>\"This has been great for me. I&amp;#039;ve been on...</td>\n",
       "      <td>this has been great for me. i&amp;#039;ve been on ...</td>\n",
       "      <td>9</td>\n",
       "      <td>April 21, 2009</td>\n",
       "      <td>2009-04-21T00:00:00Z</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>23295</td>\n",
       "      <td>Methadone</td>\n",
       "      <td>Opiate Withdrawal</td>\n",
       "      <td>\"Ive been on Methadone for over ten years and ...</td>\n",
       "      <td>\"Ive been on Methadone for over ten years and ...</td>\n",
       "      <td>ive been on methadone for over ten years and c...</td>\n",
       "      <td>7</td>\n",
       "      <td>October 18, 2016</td>\n",
       "      <td>2016-10-18T00:00:00Z</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>71428</td>\n",
       "      <td>Levora</td>\n",
       "      <td>Birth Control</td>\n",
       "      <td>\"I was on this pill for almost two years. It d...</td>\n",
       "      <td>\"I was on this pill for almost two years. It d...</td>\n",
       "      <td>i was on this pill for almost two years. it do...</td>\n",
       "      <td>2</td>\n",
       "      <td>April 16, 2011</td>\n",
       "      <td>2011-04-16T00:00:00Z</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Column         drugName                     condition  \\\n",
       "0  163740      Mirtazapine                    Depression   \n",
       "1  206473       Mesalamine  Crohn's Disease, Maintenance   \n",
       "2  159672          Bactrim       Urinary Tract Infection   \n",
       "3   39293         Contrave                   Weight Loss   \n",
       "4   97768  Cyclafem 1 / 35                 Birth Control   \n",
       "5  208087          Zyclara                     Keratosis   \n",
       "6  215892           Copper                 Birth Control   \n",
       "7  169852    Amitriptyline           Migraine Prevention   \n",
       "8   23295        Methadone             Opiate Withdrawal   \n",
       "9   71428           Levora                 Birth Control   \n",
       "\n",
       "                                              review  \\\n",
       "0  \"I&#039;ve tried a few antidepressants over th...   \n",
       "1  \"My son has Crohn&#039;s disease and has done ...   \n",
       "2                      \"Quick reduction of symptoms\"   \n",
       "3  \"Contrave combines drugs that were used for al...   \n",
       "4  \"I have been on this birth control for one cyc...   \n",
       "5  \"4 days in on first 2 weeks.  Using on arms an...   \n",
       "6  \"I&#039;ve had the copper coil for about 3 mon...   \n",
       "7  \"This has been great for me. I&#039;ve been on...   \n",
       "8  \"Ive been on Methadone for over ten years and ...   \n",
       "9  \"I was on this pill for almost two years. It d...   \n",
       "\n",
       "                             review_test_history_new  \\\n",
       "0  \"I&#039;ve tried a few antidepressants over th...   \n",
       "1  \"My son has Crohn&#039;s disease and has done ...   \n",
       "2                      \"Quick reduction of symptoms\"   \n",
       "3  \"Contrave combines drugs that were used for al...   \n",
       "4  \"I have been on this birth control for one cyc...   \n",
       "5  \"4 days in on first 2 weeks.  Using on arms an...   \n",
       "6  \"I&#039;ve had the copper coil for about 3 mon...   \n",
       "7  \"This has been great for me. I&#039;ve been on...   \n",
       "8  \"Ive been on Methadone for over ten years and ...   \n",
       "9  \"I was on this pill for almost two years. It d...   \n",
       "\n",
       "                                      review_cleaned  rating  \\\n",
       "0  i&#039;ve tried a few antidepressants over the...      10   \n",
       "1  my son has crohn&#039;s disease and has done v...       8   \n",
       "2                        quick reduction of symptoms       9   \n",
       "3  contrave combines drugs that were used for alc...       9   \n",
       "4  i have been on this birth control for one cycl...       9   \n",
       "5  4 days in on first 2 weeks.  using on arms and...       4   \n",
       "6  i&#039;ve had the copper coil for about 3 mont...       6   \n",
       "7  this has been great for me. i&#039;ve been on ...       9   \n",
       "8  ive been on methadone for over ten years and c...       7   \n",
       "9  i was on this pill for almost two years. it do...       2   \n",
       "\n",
       "                 date          date_cleaned  usefulCount  \n",
       "0   February 28, 2012  2012-02-28T00:00:00Z           22  \n",
       "1        May 17, 2009  2009-05-17T00:00:00Z           17  \n",
       "2  September 29, 2017  2017-09-29T00:00:00Z            3  \n",
       "3       March 5, 2017  2017-03-05T00:00:00Z           35  \n",
       "4    October 22, 2015  2015-10-22T00:00:00Z            4  \n",
       "5        July 3, 2014  2014-07-03T00:00:00Z           13  \n",
       "6        June 6, 2016  2016-06-06T00:00:00Z            1  \n",
       "7      April 21, 2009  2009-04-21T00:00:00Z           32  \n",
       "8    October 18, 2016  2016-10-18T00:00:00Z           21  \n",
       "9      April 16, 2011  2011-04-16T00:00:00Z            3  "
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "refine_helper.get_rows()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.15"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}