crawl.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from selenium import webdriver\n",
    "from selenium.webdriver.common.keys import Keys\n",
    "from selenium.webdriver.support.ui import WebDriverWait\n",
    "from selenium.webdriver.support import expected_conditions as EC\n",
    "from selenium.webdriver.common.by import By\n",
    "import requests\n",
    "import pandas as pd\n",
    "from bs4 import BeautifulSoup as bs\n",
    "import urllib3\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# ChromeDriver Location\n",
    "driver = webdriver.Chrome(\"chromedriver_win32\\chromedriver.exe\")\n",
    "\n",
    "save_path = '/data'\n",
    "\n",
    "list_num = list(map(str, list(range(69))))\n",
    "\n",
    "path = \"https://www.cancerresearchuk.org/about-cancer/cancer-chat/posts\"\n",
    "\n",
    "# fp = open('immotherapies.csv', 'w+', encoding=\"utf-8\")\n",
    "# fp.write('ID,user,date,immothrerapy/ies,content\\n')\n",
    "\n",
    "lst_post_id = []\n",
    "lst_user = []\n",
    "lst_content = []\n",
    "lst_date = []\n",
    "lst_state = []\n",
    "\n",
    "page = \"https://www.cancerresearchuk.org\"\n",
    "\n",
    "try:\n",
    "    for i in list_num:\n",
    "        tmp = path + \"?query=immunotherapies&page=0%2C\" + i\n",
    "#         tmp = \"https://www.cancerresearchuk.org/about-cancer/cancer-chat/posts?query=immunotherapies&page=0%2C12\"\n",
    "        driver.get(tmp)\n",
    "\n",
    "        main = driver.find_element_by_id(\"block-system-main\")\n",
    "        threads = main.find_elements_by_class_name(\"thread-title\")\n",
    "        for thread in threads:\n",
    "            header = thread.find_element_by_tag_name(\"a\")\n",
    "            link = header.get_attribute(\"href\")\n",
    "            num = link.split(\"/\")\n",
    "            req = urllib3.PoolManager()\n",
    "            res = req.request('GET', link)\n",
    "            soup = bs(res.data, 'html.parser')\n",
    "\n",
    "            # if int(num[6]) == 446631:\n",
    "            # post = soup.find_all(class_=\"post-content-inner\")\n",
    "            # name = soup.find_all(class_=\"username\")\n",
    "\n",
    "            # lst_post = []\n",
    "            # j = 2\n",
    "            # for span in post:\n",
    "            #     lst_post.append(span.text)\n",
    "            #     user = name[j].text\n",
    "            #     lst_user.append(user)\n",
    "            #     j += 1\n",
    "            #     # lst = lst_post.split(\"\\n\")\n",
    "            #     for mini in lst_post:\n",
    "            #         mini = mini.split(\"\\n\")\n",
    "            #         lst_user.append(user)\n",
    "            #         # for n in lst:\n",
    "            #         post_id = num[6]\n",
    "            #         lst_post_id.append(post_id)\n",
    "                    \n",
    "            #         content = mini[4].replace(\"Report this post\", \"\").strip(\"\\n\")\n",
    "            #         lst_content.append(content)\n",
    "\n",
    "            #         date = mini[1]\n",
    "            #         lst_date.append(date)\n",
    "\n",
    "            #         if \"immunotherapies\" in span.text or \"immunotherapy\" in span.text:\n",
    "            #             state = \"Yes\"\n",
    "            #         else:\n",
    "            #             state = \"No\"\n",
    "            #         lst_state.append(state)\n",
    "            \n",
    "            # print(next_page)\n",
    "\n",
    "                    # fp.write('{},{},{},{},{}\\n'.format(post_id, user, date, state, content))\n",
    "                    # print(lst_post_id)\n",
    "                    # print(lst_user)\n",
    "                    # print(lst_content)\n",
    "                    # print(lst_date)\n",
    "                    # print(lst_state)\n",
    "\n",
    "            for anchors in soup.find_all(class_='cr-pagination__next'):\n",
    "                for anchor in anchors.find_all(\"a\"):\n",
    "                    link = page + anchor['href']\n",
    "                    req = urllib3.PoolManager()\n",
    "                    res = req.request('GET', link)\n",
    "                    next_soup = bs(res.data, 'html.parser')\n",
    "                \n",
    "                    post = next_soup.find_all(class_=\"post-content-inner\")\n",
    "                    name = next_soup.find_all(class_=\"username\")\n",
    "\n",
    "                    lst_post = []\n",
    "                    j = 2\n",
    "\n",
    "                    for span in post:\n",
    "                        lst_post.append(span.text)\n",
    "                        user = name[j].text\n",
    "#                         lst_user.append(user)\n",
    "                        j += 1\n",
    "                        # lst = lst_post.split(\"\\n\")\n",
    "                        for mini in lst_post:\n",
    "                            mini = mini.split(\"\\n\")\n",
    "                            lst_user.append(user)\n",
    "                            # for n in lst:\n",
    "                            post_id = num[6]\n",
    "                            lst_post_id.append(post_id)\n",
    "\n",
    "                            content = mini[4].replace(\"Report this post\", \"\").strip(\"\\n\")\n",
    "                            lst_content.append(content)\n",
    "\n",
    "                            date = mini[1]\n",
    "                            lst_date.append(date)\n",
    "\n",
    "                            if \"immunotherapies\" in span.text or \"immunotherapy\" in span.text:\n",
    "                                state = \"Yes\"\n",
    "                            else:\n",
    "                                state = \"No\"\n",
    "                            lst_state.append(state)\n",
    "\n",
    "\n",
    "\n",
    "    # print(len(lst_post_id))\n",
    "    # print(len(lst_user))\n",
    "    # print(len(lst_content))\n",
    "    # print(len(lst_date))\n",
    "    # print(len(lst_state))\n",
    "\n",
    "\n",
    "finally:\n",
    "    driver.quit()\n",
    "    # fp.close()\n",
    "\n",
    "# print(main.text)\n",
    "# print(driver.page_source)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "4602\n",
      "4602\n",
      "4602\n",
      "4602\n",
      "4602\n"
     ]
    }
   ],
   "source": [
    "print(len(lst_post_id))\n",
    "print(len(lst_user))\n",
    "print(len(lst_content))\n",
    "print(len(lst_date))\n",
    "print(len(lst_state))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = pd.read_excel(r'Immunotherapies-30062021.xlsx', sheet_name=\"Sheet 1\", engine='openpyxl')\n",
    "df2 = pd.read_excel(r'Immunotherapies-30062021.xlsx', sheet_name=\"Sheet 2\", engine='openpyxl')"
   ]
  },
  {
   "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>ID</th>\n",
       "      <th>user</th>\n",
       "      <th>date</th>\n",
       "      <th>immunotherapy/ies</th>\n",
       "      <th>content</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1634661</td>\n",
       "      <td>Mario11</td>\n",
       "      <td>4 Jun 2021 17:13</td>\n",
       "      <td>No</td>\n",
       "      <td>I had my pet scan on Wednesday, and it’s Frida...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1634661</td>\n",
       "      <td>EllJay</td>\n",
       "      <td>4 Jun 2021 17:13</td>\n",
       "      <td>No</td>\n",
       "      <td>I had my pet scan on Wednesday, and it’s Frida...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1634661</td>\n",
       "      <td>EllJay</td>\n",
       "      <td>4 Jun 2021 23:38</td>\n",
       "      <td>No</td>\n",
       "      <td>It's good that it's quick.  Waiting is horribl...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>71152</td>\n",
       "      <td>Moderator Jane</td>\n",
       "      <td>22 Oct 2013 13:50</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Hi LorraineIn response to your earlier post ab...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>71152</td>\n",
       "      <td>wardy</td>\n",
       "      <td>22 Oct 2013 13:50</td>\n",
       "      <td>No</td>\n",
       "      <td>Hi LorraineIn response to your earlier post ab...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4655</th>\n",
       "      <td>79649</td>\n",
       "      <td>SleepyCat</td>\n",
       "      <td>3 May 2014 20:40</td>\n",
       "      <td>No</td>\n",
       "      <td>Gill,Our posts have just crossed! Thank you fo...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4656</th>\n",
       "      <td>79649</td>\n",
       "      <td>SleepyCat</td>\n",
       "      <td>3 May 2014 21:01</td>\n",
       "      <td>No</td>\n",
       "      <td>Hi JoGreat minds obviously think alike.My phas...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4657</th>\n",
       "      <td>1234061</td>\n",
       "      <td>IHateCancer1988</td>\n",
       "      <td>7 Apr 2020 16:53</td>\n",
       "      <td>No</td>\n",
       "      <td>Hi Olivia How are you today? Please come to ta...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4658</th>\n",
       "      <td>1234061</td>\n",
       "      <td>olivia1504</td>\n",
       "      <td>7 Apr 2020 16:53</td>\n",
       "      <td>No</td>\n",
       "      <td>Hi Olivia How are you today? Please come to ta...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4659</th>\n",
       "      <td>1234061</td>\n",
       "      <td>olivia1504</td>\n",
       "      <td>7 Apr 2020 17:43</td>\n",
       "      <td>No</td>\n",
       "      <td>Thank you. It's nice to have your support in t...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4660 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           ID             user               date immunotherapy/ies  \\\n",
       "0     1634661          Mario11   4 Jun 2021 17:13                No   \n",
       "1     1634661           EllJay   4 Jun 2021 17:13                No   \n",
       "2     1634661           EllJay   4 Jun 2021 23:38                No   \n",
       "3       71152   Moderator Jane  22 Oct 2013 13:50               Yes   \n",
       "4       71152            wardy  22 Oct 2013 13:50                No   \n",
       "...       ...              ...                ...               ...   \n",
       "4655    79649        SleepyCat   3 May 2014 20:40                No   \n",
       "4656    79649        SleepyCat   3 May 2014 21:01                No   \n",
       "4657  1234061  IHateCancer1988   7 Apr 2020 16:53                No   \n",
       "4658  1234061       olivia1504   7 Apr 2020 16:53                No   \n",
       "4659  1234061       olivia1504   7 Apr 2020 17:43                No   \n",
       "\n",
       "                                                content  \n",
       "0     I had my pet scan on Wednesday, and it’s Frida...  \n",
       "1     I had my pet scan on Wednesday, and it’s Frida...  \n",
       "2     It's good that it's quick.  Waiting is horribl...  \n",
       "3     Hi LorraineIn response to your earlier post ab...  \n",
       "4     Hi LorraineIn response to your earlier post ab...  \n",
       "...                                                 ...  \n",
       "4655  Gill,Our posts have just crossed! Thank you fo...  \n",
       "4656  Hi JoGreat minds obviously think alike.My phas...  \n",
       "4657  Hi Olivia How are you today? Please come to ta...  \n",
       "4658  Hi Olivia How are you today? Please come to ta...  \n",
       "4659  Thank you. It's nice to have your support in t...  \n",
       "\n",
       "[4660 rows x 5 columns]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_new = pd.DataFrame({'ID': lst_post_id,\n",
    "                   'user': lst_user,\n",
    "                 'date': lst_date,\n",
    "                 'immunotherapy/ies': lst_state,\n",
    "                 'content': lst_content})\n",
    "df_new\n",
    "# fp.write('ID,user,date,immothrerapy/ies,content\\n')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_drop_dup = df_new.drop_duplicates(subset=['content'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>user</th>\n",
       "      <th>date</th>\n",
       "      <th>immunotherapy/ies</th>\n",
       "      <th>content</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1634661</td>\n",
       "      <td>Mario11</td>\n",
       "      <td>4 Jun 2021 17:13</td>\n",
       "      <td>No</td>\n",
       "      <td>I had my pet scan on Wednesday, and it’s Frida...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1634661</td>\n",
       "      <td>EllJay</td>\n",
       "      <td>4 Jun 2021 23:38</td>\n",
       "      <td>No</td>\n",
       "      <td>It's good that it's quick.  Waiting is horribl...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>71152</td>\n",
       "      <td>Moderator Jane</td>\n",
       "      <td>22 Oct 2013 13:50</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Hi LorraineIn response to your earlier post ab...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>71152</td>\n",
       "      <td>wardy</td>\n",
       "      <td>22 Oct 2013 20:38</td>\n",
       "      <td>No</td>\n",
       "      <td>When it comes to research there is no 'we'.  T...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>71152</td>\n",
       "      <td>LorraineD</td>\n",
       "      <td>22 Oct 2013 21:02</td>\n",
       "      <td>No</td>\n",
       "      <td>Thanks Jane, it works now for me. I will revie...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4627</th>\n",
       "      <td>79649</td>\n",
       "      <td>SleepyCat</td>\n",
       "      <td>3 May 2014 20:34</td>\n",
       "      <td>No</td>\n",
       "      <td>Hi LisaI am glad you enjoyed the aqua fit.  A ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4641</th>\n",
       "      <td>79649</td>\n",
       "      <td>Meerkat@65</td>\n",
       "      <td>3 May 2014 20:40</td>\n",
       "      <td>No</td>\n",
       "      <td>Gill,Our posts have just crossed! Thank you fo...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4656</th>\n",
       "      <td>79649</td>\n",
       "      <td>SleepyCat</td>\n",
       "      <td>3 May 2014 21:01</td>\n",
       "      <td>No</td>\n",
       "      <td>Hi JoGreat minds obviously think alike.My phas...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4657</th>\n",
       "      <td>1234061</td>\n",
       "      <td>IHateCancer1988</td>\n",
       "      <td>7 Apr 2020 16:53</td>\n",
       "      <td>No</td>\n",
       "      <td>Hi Olivia How are you today? Please come to ta...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4659</th>\n",
       "      <td>1234061</td>\n",
       "      <td>olivia1504</td>\n",
       "      <td>7 Apr 2020 17:43</td>\n",
       "      <td>No</td>\n",
       "      <td>Thank you. It's nice to have your support in t...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>685 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           ID             user               date immunotherapy/ies  \\\n",
       "0     1634661          Mario11   4 Jun 2021 17:13                No   \n",
       "2     1634661           EllJay   4 Jun 2021 23:38                No   \n",
       "3       71152   Moderator Jane  22 Oct 2013 13:50               Yes   \n",
       "5       71152            wardy  22 Oct 2013 20:38                No   \n",
       "8       71152        LorraineD  22 Oct 2013 21:02                No   \n",
       "...       ...              ...                ...               ...   \n",
       "4627    79649        SleepyCat   3 May 2014 20:34                No   \n",
       "4641    79649       Meerkat@65   3 May 2014 20:40                No   \n",
       "4656    79649        SleepyCat   3 May 2014 21:01                No   \n",
       "4657  1234061  IHateCancer1988   7 Apr 2020 16:53                No   \n",
       "4659  1234061       olivia1504   7 Apr 2020 17:43                No   \n",
       "\n",
       "                                                content  \n",
       "0     I had my pet scan on Wednesday, and it’s Frida...  \n",
       "2     It's good that it's quick.  Waiting is horribl...  \n",
       "3     Hi LorraineIn response to your earlier post ab...  \n",
       "5     When it comes to research there is no 'we'.  T...  \n",
       "8     Thanks Jane, it works now for me. I will revie...  \n",
       "...                                                 ...  \n",
       "4627  Hi LisaI am glad you enjoyed the aqua fit.  A ...  \n",
       "4641  Gill,Our posts have just crossed! Thank you fo...  \n",
       "4656  Hi JoGreat minds obviously think alike.My phas...  \n",
       "4657  Hi Olivia How are you today? Please come to ta...  \n",
       "4659  Thank you. It's nice to have your support in t...  \n",
       "\n",
       "[685 rows x 5 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_drop_dup"
   ]
  },
  {
   "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>ID</th>\n",
       "      <th>user</th>\n",
       "      <th>date</th>\n",
       "      <th>immunotherapy/ies</th>\n",
       "      <th>content</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>446631</td>\n",
       "      <td>Immunotherapy</td>\n",
       "      <td>16 Aug 2017 22:43</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Been today to see oncologist  outing me on imm...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>446631</td>\n",
       "      <td>Jolamine</td>\n",
       "      <td>16 Aug 2017 22:43</td>\n",
       "      <td>No</td>\n",
       "      <td>Been today to see oncologist  outing me on imm...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>446631</td>\n",
       "      <td>Jolamine</td>\n",
       "      <td>17 Aug 2017 02:34</td>\n",
       "      <td>No</td>\n",
       "      <td>Hi there,Welcome to the forum,I am so sorry t...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>446631</td>\n",
       "      <td>Immunotherapy</td>\n",
       "      <td>16 Aug 2017 22:43</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Been today to see oncologist  outing me on imm...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>446631</td>\n",
       "      <td>Immunotherapy</td>\n",
       "      <td>17 Aug 2017 02:34</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Hi there,Welcome to the forum,I am so sorry t...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29700</th>\n",
       "      <td>1234061</td>\n",
       "      <td>IHateCancer1988</td>\n",
       "      <td>6 Apr 2020 21:02</td>\n",
       "      <td>No</td>\n",
       "      <td>Hi OliviaI am so sorry that it's bad news for ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29701</th>\n",
       "      <td>1234061</td>\n",
       "      <td>IHateCancer1988</td>\n",
       "      <td>6 Apr 2020 21:20</td>\n",
       "      <td>No</td>\n",
       "      <td>I’m sorry to hear this about your mum. It real...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29702</th>\n",
       "      <td>1234061</td>\n",
       "      <td>IHateCancer1988</td>\n",
       "      <td>6 Apr 2020 22:02</td>\n",
       "      <td>No</td>\n",
       "      <td>Hi Charlotte thank you for your kindness. You ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29703</th>\n",
       "      <td>1234061</td>\n",
       "      <td>IHateCancer1988</td>\n",
       "      <td>6 Apr 2020 22:12</td>\n",
       "      <td>No</td>\n",
       "      <td>Oh Helen I really feel for you my lovely. I lo...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29704</th>\n",
       "      <td>1234061</td>\n",
       "      <td>IHateCancer1988</td>\n",
       "      <td>7 Apr 2020 16:46</td>\n",
       "      <td>No</td>\n",
       "      <td>Hi CharlotteI'm so sorry about what happened t...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>29705 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            ID             user               date immunotherapy/ies  \\\n",
       "0       446631    Immunotherapy  16 Aug 2017 22:43               Yes   \n",
       "1       446631         Jolamine  16 Aug 2017 22:43                No   \n",
       "2       446631         Jolamine  17 Aug 2017 02:34                No   \n",
       "3       446631    Immunotherapy  16 Aug 2017 22:43               Yes   \n",
       "4       446631    Immunotherapy  17 Aug 2017 02:34               Yes   \n",
       "...        ...              ...                ...               ...   \n",
       "29700  1234061  IHateCancer1988   6 Apr 2020 21:02                No   \n",
       "29701  1234061  IHateCancer1988   6 Apr 2020 21:20                No   \n",
       "29702  1234061  IHateCancer1988   6 Apr 2020 22:02                No   \n",
       "29703  1234061  IHateCancer1988   6 Apr 2020 22:12                No   \n",
       "29704  1234061  IHateCancer1988   7 Apr 2020 16:46                No   \n",
       "\n",
       "                                                 content  \n",
       "0      Been today to see oncologist  outing me on imm...  \n",
       "1      Been today to see oncologist  outing me on imm...  \n",
       "2       Hi there,Welcome to the forum,I am so sorry t...  \n",
       "3      Been today to see oncologist  outing me on imm...  \n",
       "4       Hi there,Welcome to the forum,I am so sorry t...  \n",
       "...                                                  ...  \n",
       "29700  Hi OliviaI am so sorry that it's bad news for ...  \n",
       "29701  I’m sorry to hear this about your mum. It real...  \n",
       "29702  Hi Charlotte thank you for your kindness. You ...  \n",
       "29703  Oh Helen I really feel for you my lovely. I lo...  \n",
       "29704  Hi CharlotteI'm so sorry about what happened t...  \n",
       "\n",
       "[29705 rows x 5 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "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>ID</th>\n",
       "      <th>user</th>\n",
       "      <th>date</th>\n",
       "      <th>immunotherapy/ies</th>\n",
       "      <th>content</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>446631</td>\n",
       "      <td>Immunotherapy</td>\n",
       "      <td>16 Aug 2017 22:43</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Been today to see oncologist  outing me on imm...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>446631</td>\n",
       "      <td>Jolamine</td>\n",
       "      <td>17 Aug 2017 02:34</td>\n",
       "      <td>No</td>\n",
       "      <td>Hi there,Welcome to the forum,I am so sorry t...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>446631</td>\n",
       "      <td>Immunotherapy</td>\n",
       "      <td>17 Aug 2017 08:25</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Thank you  dose immunotherapy work x</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>446631</td>\n",
       "      <td>Alsdouble</td>\n",
       "      <td>20 May 2019 19:37</td>\n",
       "      <td>No</td>\n",
       "      <td>My wife has lung cancer. Diagnosed Feb last ye...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>446631</td>\n",
       "      <td>Jolamine</td>\n",
       "      <td>20 May 2019 22:58</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Hi Immunotherapy,I have not had radiotherapy, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4627</th>\n",
       "      <td>79649</td>\n",
       "      <td>SleepyCat</td>\n",
       "      <td>3 May 2014 20:34</td>\n",
       "      <td>No</td>\n",
       "      <td>Hi LisaI am glad you enjoyed the aqua fit.  A ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4641</th>\n",
       "      <td>79649</td>\n",
       "      <td>Meerkat@65</td>\n",
       "      <td>3 May 2014 20:40</td>\n",
       "      <td>No</td>\n",
       "      <td>Gill,Our posts have just crossed! Thank you fo...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4656</th>\n",
       "      <td>79649</td>\n",
       "      <td>SleepyCat</td>\n",
       "      <td>3 May 2014 21:01</td>\n",
       "      <td>No</td>\n",
       "      <td>Hi JoGreat minds obviously think alike.My phas...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4657</th>\n",
       "      <td>1234061</td>\n",
       "      <td>IHateCancer1988</td>\n",
       "      <td>7 Apr 2020 16:53</td>\n",
       "      <td>No</td>\n",
       "      <td>Hi Olivia How are you today? Please come to ta...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4659</th>\n",
       "      <td>1234061</td>\n",
       "      <td>olivia1504</td>\n",
       "      <td>7 Apr 2020 17:43</td>\n",
       "      <td>No</td>\n",
       "      <td>Thank you. It's nice to have your support in t...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4709 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           ID             user               date immunotherapy/ies  \\\n",
       "0      446631    Immunotherapy  16 Aug 2017 22:43               Yes   \n",
       "1      446631         Jolamine  17 Aug 2017 02:34                No   \n",
       "2      446631    Immunotherapy  17 Aug 2017 08:25               Yes   \n",
       "3      446631        Alsdouble  20 May 2019 19:37                No   \n",
       "4      446631         Jolamine  20 May 2019 22:58               Yes   \n",
       "...       ...              ...                ...               ...   \n",
       "4627    79649        SleepyCat   3 May 2014 20:34                No   \n",
       "4641    79649       Meerkat@65   3 May 2014 20:40                No   \n",
       "4656    79649        SleepyCat   3 May 2014 21:01                No   \n",
       "4657  1234061  IHateCancer1988   7 Apr 2020 16:53                No   \n",
       "4659  1234061       olivia1504   7 Apr 2020 17:43                No   \n",
       "\n",
       "                                                content  \n",
       "0     Been today to see oncologist  outing me on imm...  \n",
       "1      Hi there,Welcome to the forum,I am so sorry t...  \n",
       "2                  Thank you  dose immunotherapy work x  \n",
       "3     My wife has lung cancer. Diagnosed Feb last ye...  \n",
       "4     Hi Immunotherapy,I have not had radiotherapy, ...  \n",
       "...                                                 ...  \n",
       "4627  Hi LisaI am glad you enjoyed the aqua fit.  A ...  \n",
       "4641  Gill,Our posts have just crossed! Thank you fo...  \n",
       "4656  Hi JoGreat minds obviously think alike.My phas...  \n",
       "4657  Hi Olivia How are you today? Please come to ta...  \n",
       "4659  Thank you. It's nice to have your support in t...  \n",
       "\n",
       "[4709 rows x 5 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = df2.drop_duplicates(subset=['content'])\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = df1.append(df_new)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = df2.append(df_drop_dup)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "writer = pd.ExcelWriter('Immunotherapies-02072021.xlsx', engine='xlsxwriter')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2.to_excel(writer, sheet_name='Sheet 2')\n",
    "df1.to_excel(writer, sheet_name='Sheet 1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "writer.save()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "20825\n",
      "20825\n",
      "20825\n",
      "20825\n",
      "20825\n"
     ]
    }
   ],
   "source": [
    "from selenium import webdriver\n",
    "from selenium.webdriver.common.keys import Keys\n",
    "from selenium.webdriver.support.ui import WebDriverWait\n",
    "from selenium.webdriver.support import expected_conditions as EC\n",
    "from selenium.webdriver.common.by import By\n",
    "import requests\n",
    "import pandas as pd\n",
    "from bs4 import BeautifulSoup as bs\n",
    "import urllib3\n",
    "import os\n",
    "\n",
    "# ChromeDriver Location\n",
    "driver = webdriver.Chrome(\"chromedriver_win32\\chromedriver.exe\")\n",
    "\n",
    "save_path = '/data'\n",
    "\n",
    "list_num = list(map(str, list(range(69))))\n",
    "\n",
    "path = \"https://www.cancerresearchuk.org/about-cancer/cancer-chat/posts\"\n",
    "\n",
    "# fp = open('immotherapies.csv', 'w+', encoding=\"utf-8\")\n",
    "# fp.write('ID,user,date,immothrerapy/ies,content\\n')\n",
    "\n",
    "lst_post_id = []\n",
    "lst_user = []\n",
    "lst_content = []\n",
    "lst_date = []\n",
    "lst_state = []\n",
    "\n",
    "try:\n",
    "    for i in list_num:\n",
    "        tmp = path + \"?query=immunotherapies&page=0%2C\" + i\n",
    "        # tmp = \"https://www.cancerresearchuk.org/about-cancer/cancer-chat/posts?query=immunotherapies&page=0%2C0\"\n",
    "        driver.get(tmp)\n",
    "\n",
    "        main = driver.find_element_by_id(\"block-system-main\")\n",
    "        threads = main.find_elements_by_class_name(\"thread-title\")\n",
    "        for thread in threads:\n",
    "            header = thread.find_element_by_tag_name(\"a\")\n",
    "            link = header.get_attribute(\"href\")\n",
    "            num = link.split(\"/\")\n",
    "            req = urllib3.PoolManager()\n",
    "            res = req.request('GET', link)\n",
    "            soup = bs(res.data, 'html.parser')\n",
    "\n",
    "            # if int(num[6]) == 446631:\n",
    "            post = soup.find_all(class_=\"post-content-inner\")\n",
    "            name = soup.find_all(class_=\"username\")\n",
    "            \n",
    "            lst_post = []\n",
    "            j = 2\n",
    "            \n",
    "            for span in post:\n",
    "                lst_post.append(span.text)\n",
    "                user = name[j].text\n",
    "                j += 1\n",
    "                # lst = lst_post.split(\"\\n\")\n",
    "                for mini in lst_post:\n",
    "                    mini = mini.split(\"\\n\")\n",
    "                    \n",
    "                    lst_user.append(user)\n",
    "                    # for n in lst:\n",
    "                    post_id = num[6]\n",
    "                    lst_post_id.append(post_id)\n",
    "                    \n",
    "                    content = mini[4].replace(\"Report this post\", \"\").strip(\"\\n\")\n",
    "                    lst_content.append(content)\n",
    "\n",
    "                    date = mini[1]\n",
    "                    lst_date.append(date)\n",
    "\n",
    "                    if \"immunotherapies\" in span.text or \"immunotherapy\" in span.text:\n",
    "                        state = \"Yes\"\n",
    "                    else:\n",
    "                        state = \"No\"\n",
    "                    lst_state.append(state)\n",
    "\n",
    "                    # fp.write('{},{},{},{},{}\\n'.format(post_id, user, date, state, content))\n",
    "                    # print(lst_post_id)\n",
    "                    # print(lst_user)\n",
    "                    # print(lst_content)\n",
    "                    # print(lst_date)\n",
    "                    # print(lst_state)\n",
    "                    \n",
    "    print(len(lst_post_id))\n",
    "    print(len(lst_user))\n",
    "    print(len(lst_content))\n",
    "    print(len(lst_date))\n",
    "    print(len(lst_state))\n",
    "\n",
    "\n",
    "finally:\n",
    "    driver.quit()\n",
    "    # fp.close()\n",
    "\n",
    "# print(main.text)\n",
    "# print(driver.page_source)\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}