Nolita_Yelp_Crawling.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Nolita Location Crawling"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "from selenium import webdriver \n",
    "from selenium.webdriver.support.ui import WebDriverWait\n",
    "from selenium.webdriver.chrome.options import Options \n",
    "from bs4 import BeautifulSoup\n",
    "\n",
    "urls=[]\n",
    "for i in range(0,400,20): #crawl the first 20 pages  \n",
    "    url = 'https://www.yelp.com/biz/tacombi-nolita-new-york-3?start=' + str(i)\n",
    "    urls.append(url)\n",
    "    \n",
    "drivers = [] \n",
    "for url in urls:\n",
    "    option = webdriver.ChromeOptions()\n",
    "    option.add_argument('headless')\n",
    "    driver = webdriver.Chrome('/Users/vanessaasaro/Desktop/Web analytics project/chromedriver',options=option) #PUT YOUT PATH TO THE CHROMEDRIVER\n",
    "    driver.get(url)\n",
    "    drivers.append(driver)\n",
    "\n",
    "soups = []\n",
    "for driver in drivers:\n",
    "    soup = BeautifulSoup(driver.page_source,'html.parser')\n",
    "    soups.append(soup)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "date = [] #store dates\n",
    "rating = [] #store ratings\n",
    "reviews = [] #store revies\n",
    "user =[] #store usernames\n",
    "for soup in soups:\n",
    "    for i in soup.find_all('li', {'class':'lemon--li__373c0__1r9wz margin-b5__373c0__2ErL8 border-color--default__373c0__3-ifU'}): #grouping by the li class for all crawls\n",
    "        for k in i.find_all('div', {'class':'lemon--div__373c0__1mboc user-passport-info border-color--default__373c0__3-ifU'}): #crawling for usernames\n",
    "            for u in k.find_all('span',{'class':'lemon--span__373c0__3997G text__373c0__2Kxyz fs-block text-color--black-regular__373c0__2vGEn text-align--left__373c0__2XGa- text-weight--bold__373c0__1elNz text-size--large__373c0__3t60B'}):\n",
    "                user1 = u.find('a')\n",
    "                user.append(user1.text)\n",
    "                if i.find('div',{'class':'lemon--div__373c0__1mboc block-quote__373c0__1C5O7 padding-l3__373c0__1scQ0 border-color--default__373c0__3-ifU'}) != None: #crawling for usernames when people have previous review\n",
    "                    if i.find('p',{'class':'lemon--p__373c0__3Qnnj text__373c0__2Kxyz comment__373c0__1M-px truncated__373c0__3dJ7w text-color--normal__373c0__3xep9 text-align--left__373c0__2XGa-'}) != None:\n",
    "                              user.append(user1.text)\n",
    "                if i.find('div',{'class':'lemon--div__373c0__1mboc margin-t1-5__373c0__2GIcL border-color--default__373c0__3-ifU'}) != None: #crawling for usernames when people have another previous review\n",
    "                    if i.find('div',{'class':'lemon--div__373c0__1mboc block-quote__373c0__1C5O7 padding-l3__373c0__1scQ0 border-color--default__373c0__3-ifU'}) != None:\n",
    "                        if i.find('p',{'class':'lemon--p__373c0__3Qnnj text__373c0__2Kxyz comment__373c0__1M-px truncated__373c0__3dJ7w text-color--normal__373c0__3xep9 text-align--left__373c0__2XGa-'}) != None:\n",
    "                            user.append(user1.text)\n",
    "        for l in i.find_all('div',{'class':'lemon--div__373c0__1mboc arrange-unit__373c0__o3tjT border-color--default__373c0__3-ifU'}): #crawling for rating\n",
    "            per_rating = l.find_all('span',{'class':'lemon--span__373c0__3997G display--inline__373c0__3JqBP border-color--default__373c0__3-ifU'})\n",
    "            for per in per_rating:\n",
    "                per_rating2 = per.find('div')\n",
    "                try:\n",
    "                    if len(per_rating2) > 0:\n",
    "                        a = str(per_rating2.get('aria-label')) #extract rating\n",
    "                    if \"rating\" in a:\n",
    "                        rating.append(a)\n",
    "                except:\n",
    "                    pass\n",
    "        for x in i.find_all('div',{'class':'lemon--div__373c0__1mboc margin-b2__373c0__abANL border-color--default__373c0__3-ifU'}): #crawling for revies\n",
    "            for j in x.find_all('p',{'class':'lemon--p__373c0__3Qnnj text__373c0__2Kxyz comment__373c0__1M-px text-color--normal__373c0__3xep9 text-align--left__373c0__2XGa-'}):\n",
    "                per_review = j.find('span')\n",
    "                reviews.append(per_review.text)\n",
    "        if i.find('div',{'class':'lemon--div__373c0__1mboc block-quote__373c0__1C5O7 padding-l3__373c0__1scQ0 border-color--default__373c0__3-ifU'}) != None: #crawling for when people have previous review\n",
    "            for f in i.find_all('p',{'class':'lemon--p__373c0__3Qnnj text__373c0__2Kxyz comment__373c0__1M-px truncated__373c0__3dJ7w text-color--normal__373c0__3xep9 text-align--left__373c0__2XGa-'}):\n",
    "                prev_review = f.find('span')\n",
    "                reviews.append(prev_review.text) \n",
    "        for e in i.find_all('div',{'class':'lemon--div__373c0__1mboc margin-t1__373c0__oLmO6 margin-b1-5__373c0__2Wblx border-color--default__373c0__3-ifU'}): #crawling for date \n",
    "            for w in e.find_all ('div', {'class':'lemon--div__373c0__1mboc arrange__373c0__2C9bH gutter-1__373c0__2l5bx vertical-align-middle__373c0__1SDTo border-color--default__373c0__3-ifU'}):\n",
    "                for k in w.find_all('div',{'class':'lemon--div__373c0__1mboc arrange-unit__373c0__o3tjT arrange-unit-fill__373c0__3Sfw1 border-color--default__373c0__3-ifU'}):\n",
    "                    date.append(k.text)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "413\n",
      "413\n",
      "413\n",
      "413\n"
     ]
    }
   ],
   "source": [
    "#make sure we have same amount for all lists\n",
    "print(len(date))\n",
    "print(len(rating))\n",
    "print(len(reviews))\n",
    "print(len(user))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "from selenium import webdriver \n",
    "from selenium.webdriver.support.ui import WebDriverWait\n",
    "from selenium.webdriver.chrome.options import Options \n",
    "from bs4 import BeautifulSoup\n",
    "\n",
    "urls=[]\n",
    "for i in range(400,750,20): #Crawl next 18 pages\n",
    "    url = 'https://www.yelp.com/biz/tacombi-nolita-new-york-3?start=' + str(i)\n",
    "    urls.append(url)\n",
    "    \n",
    "drivers = [] \n",
    "for url in urls:\n",
    "    option = webdriver.ChromeOptions()\n",
    "    option.add_argument('headless')\n",
    "    driver = webdriver.Chrome('/Users/vanessaasaro/Desktop/Web analytics project/chromedriver',options=option) #PUT YOUT PATH TO THE CHROMEDRIVER\n",
    "    driver.get(url)\n",
    "    drivers.append(driver)\n",
    "\n",
    "soups = []\n",
    "for driver in drivers:\n",
    "    soup = BeautifulSoup(driver.page_source,'html.parser')\n",
    "    soups.append(soup)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "for soup in soups:\n",
    "    for i in soup.find_all('li', {'class':'lemon--li__373c0__1r9wz margin-b5__373c0__2ErL8 border-color--default__373c0__3-ifU'}): #grouping by the li class for all crawls\n",
    "        for k in i.find_all('div', {'class':'lemon--div__373c0__1mboc user-passport-info border-color--default__373c0__3-ifU'}): #crawling for usernames\n",
    "            for u in k.find_all('span',{'class':'lemon--span__373c0__3997G text__373c0__2Kxyz fs-block text-color--black-regular__373c0__2vGEn text-align--left__373c0__2XGa- text-weight--bold__373c0__1elNz text-size--large__373c0__3t60B'}):\n",
    "                user1 = u.find('a')\n",
    "                user.append(user1.text)\n",
    "                if i.find('div',{'class':'lemon--div__373c0__1mboc block-quote__373c0__1C5O7 padding-l3__373c0__1scQ0 border-color--default__373c0__3-ifU'}) != None: #crawling for usernames when people have previous review\n",
    "                    if i.find('p',{'class':'lemon--p__373c0__3Qnnj text__373c0__2Kxyz comment__373c0__1M-px truncated__373c0__3dJ7w text-color--normal__373c0__3xep9 text-align--left__373c0__2XGa-'}) != None:\n",
    "                              user.append(user1.text)\n",
    "                if i.find('div',{'class':'lemon--div__373c0__1mboc margin-t1-5__373c0__2GIcL border-color--default__373c0__3-ifU'}) != None: #crawling for usernames when people have another previous review\n",
    "                    if i.find('div',{'class':'lemon--div__373c0__1mboc block-quote__373c0__1C5O7 padding-l3__373c0__1scQ0 border-color--default__373c0__3-ifU'}) != None:\n",
    "                        if i.find('p',{'class':'lemon--p__373c0__3Qnnj text__373c0__2Kxyz comment__373c0__1M-px truncated__373c0__3dJ7w text-color--normal__373c0__3xep9 text-align--left__373c0__2XGa-'}) != None:\n",
    "                            user.append(user1.text)\n",
    "        for l in i.find_all('div',{'class':'lemon--div__373c0__1mboc arrange-unit__373c0__o3tjT border-color--default__373c0__3-ifU'}): #crawling for rating\n",
    "            per_rating = l.find_all('span',{'class':'lemon--span__373c0__3997G display--inline__373c0__3JqBP border-color--default__373c0__3-ifU'})\n",
    "            for per in per_rating:\n",
    "                per_rating2 = per.find('div')\n",
    "                try:\n",
    "                    if len(per_rating2) > 0:\n",
    "                        a = str(per_rating2.get('aria-label')) #extract rating\n",
    "                    if \"rating\" in a:\n",
    "                        rating.append(a)\n",
    "                except:\n",
    "                    pass\n",
    "        for x in i.find_all('div',{'class':'lemon--div__373c0__1mboc margin-b2__373c0__abANL border-color--default__373c0__3-ifU'}): #crawling for revies\n",
    "            for j in x.find_all('p',{'class':'lemon--p__373c0__3Qnnj text__373c0__2Kxyz comment__373c0__1M-px text-color--normal__373c0__3xep9 text-align--left__373c0__2XGa-'}):\n",
    "                per_review = j.find('span')\n",
    "                reviews.append(per_review.text)\n",
    "        if i.find('div',{'class':'lemon--div__373c0__1mboc block-quote__373c0__1C5O7 padding-l3__373c0__1scQ0 border-color--default__373c0__3-ifU'}) != None: #crawling for when people have previous review\n",
    "            for f in i.find_all('p',{'class':'lemon--p__373c0__3Qnnj text__373c0__2Kxyz comment__373c0__1M-px truncated__373c0__3dJ7w text-color--normal__373c0__3xep9 text-align--left__373c0__2XGa-'}):\n",
    "                prev_review = f.find('span')\n",
    "                reviews.append(prev_review.text) \n",
    "        for e in i.find_all('div',{'class':'lemon--div__373c0__1mboc margin-t1__373c0__oLmO6 margin-b1-5__373c0__2Wblx border-color--default__373c0__3-ifU'}): #crawling for date \n",
    "            for w in e.find_all ('div', {'class':'lemon--div__373c0__1mboc arrange__373c0__2C9bH gutter-1__373c0__2l5bx vertical-align-middle__373c0__1SDTo border-color--default__373c0__3-ifU'}):\n",
    "                for k in w.find_all('div',{'class':'lemon--div__373c0__1mboc arrange-unit__373c0__o3tjT arrange-unit-fill__373c0__3Sfw1 border-color--default__373c0__3-ifU'}):\n",
    "                    date.append(k.text)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "779\n",
      "779\n",
      "779\n",
      "779\n"
     ]
    }
   ],
   "source": [
    "#make sure we have same amount for all lists\n",
    "print(len(date))\n",
    "print(len(rating))\n",
    "print(len(reviews))\n",
    "print(len(user))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "from selenium import webdriver \n",
    "from selenium.webdriver.support.ui import WebDriverWait\n",
    "from selenium.webdriver.chrome.options import Options \n",
    "from bs4 import BeautifulSoup\n",
    "\n",
    "\n",
    "urls=[]\n",
    "for i in range(760,1101,20): #crawl next 18 pages\n",
    "    url = 'https://www.yelp.com/biz/tacombi-nolita-new-york-3?start=' + str(i)\n",
    "    urls.append(url)\n",
    "    \n",
    "drivers = [] \n",
    "for url in urls:\n",
    "    option = webdriver.ChromeOptions()\n",
    "    option.add_argument('headless')\n",
    "    driver = webdriver.Chrome('/Users/vanessaasaro/Desktop/Web analytics project/chromedriver',options=option) #PUT YOUT PATH TO THE CHROMEDRIVER\n",
    "    driver.get(url)\n",
    "    drivers.append(driver)\n",
    "\n",
    "soups = []\n",
    "for driver in drivers:\n",
    "    soup = BeautifulSoup(driver.page_source,'html.parser')\n",
    "    soups.append(soup)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "for soup in soups:\n",
    "    for i in soup.find_all('li', {'class':'lemon--li__373c0__1r9wz margin-b5__373c0__2ErL8 border-color--default__373c0__3-ifU'}): #grouping by the li class for all crawls\n",
    "        for k in i.find_all('div', {'class':'lemon--div__373c0__1mboc user-passport-info border-color--default__373c0__3-ifU'}): #crawling for usernames\n",
    "            for u in k.find_all('span',{'class':'lemon--span__373c0__3997G text__373c0__2Kxyz fs-block text-color--black-regular__373c0__2vGEn text-align--left__373c0__2XGa- text-weight--bold__373c0__1elNz text-size--large__373c0__3t60B'}):\n",
    "                user1 = u.find('a')\n",
    "                user.append(user1.text)\n",
    "                if i.find('div',{'class':'lemon--div__373c0__1mboc block-quote__373c0__1C5O7 padding-l3__373c0__1scQ0 border-color--default__373c0__3-ifU'}) != None: #crawling for usernames when people have previous review\n",
    "                    if i.find('p',{'class':'lemon--p__373c0__3Qnnj text__373c0__2Kxyz comment__373c0__1M-px truncated__373c0__3dJ7w text-color--normal__373c0__3xep9 text-align--left__373c0__2XGa-'}) != None:\n",
    "                              user.append(user1.text)\n",
    "                if i.find('div',{'class':'lemon--div__373c0__1mboc margin-t1-5__373c0__2GIcL border-color--default__373c0__3-ifU'}) != None: #crawling for usernames when people have another previous review\n",
    "                    if i.find('div',{'class':'lemon--div__373c0__1mboc block-quote__373c0__1C5O7 padding-l3__373c0__1scQ0 border-color--default__373c0__3-ifU'}) != None:\n",
    "                        if i.find('p',{'class':'lemon--p__373c0__3Qnnj text__373c0__2Kxyz comment__373c0__1M-px truncated__373c0__3dJ7w text-color--normal__373c0__3xep9 text-align--left__373c0__2XGa-'}) != None:\n",
    "                            user.append(user1.text)\n",
    "        for l in i.find_all('div',{'class':'lemon--div__373c0__1mboc arrange-unit__373c0__o3tjT border-color--default__373c0__3-ifU'}): #crawling for rating\n",
    "            per_rating = l.find_all('span',{'class':'lemon--span__373c0__3997G display--inline__373c0__3JqBP border-color--default__373c0__3-ifU'})\n",
    "            for per in per_rating:\n",
    "                per_rating2 = per.find('div')\n",
    "                try:\n",
    "                    if len(per_rating2) > 0:\n",
    "                        a = str(per_rating2.get('aria-label')) #extract rating\n",
    "                    if \"rating\" in a:\n",
    "                        rating.append(a)\n",
    "                except:\n",
    "                    pass\n",
    "        for x in i.find_all('div',{'class':'lemon--div__373c0__1mboc margin-b2__373c0__abANL border-color--default__373c0__3-ifU'}): #crawling for revies\n",
    "            for j in x.find_all('p',{'class':'lemon--p__373c0__3Qnnj text__373c0__2Kxyz comment__373c0__1M-px text-color--normal__373c0__3xep9 text-align--left__373c0__2XGa-'}):\n",
    "                per_review = j.find('span')\n",
    "                reviews.append(per_review.text)\n",
    "        if i.find('div',{'class':'lemon--div__373c0__1mboc block-quote__373c0__1C5O7 padding-l3__373c0__1scQ0 border-color--default__373c0__3-ifU'}) != None: #crawling for when people have previous review\n",
    "            for f in i.find_all('p',{'class':'lemon--p__373c0__3Qnnj text__373c0__2Kxyz comment__373c0__1M-px truncated__373c0__3dJ7w text-color--normal__373c0__3xep9 text-align--left__373c0__2XGa-'}):\n",
    "                prev_review = f.find('span')\n",
    "                reviews.append(prev_review.text) \n",
    "        for e in i.find_all('div',{'class':'lemon--div__373c0__1mboc margin-t1__373c0__oLmO6 margin-b1-5__373c0__2Wblx border-color--default__373c0__3-ifU'}): #crawling for date \n",
    "            for w in e.find_all ('div', {'class':'lemon--div__373c0__1mboc arrange__373c0__2C9bH gutter-1__373c0__2l5bx vertical-align-middle__373c0__1SDTo border-color--default__373c0__3-ifU'}):\n",
    "                for k in w.find_all('div',{'class':'lemon--div__373c0__1mboc arrange-unit__373c0__o3tjT arrange-unit-fill__373c0__3Sfw1 border-color--default__373c0__3-ifU'}):\n",
    "                    date.append(k.text)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1135\n",
      "1135\n",
      "1135\n",
      "1135\n"
     ]
    }
   ],
   "source": [
    "#make sure we have same amount for all lists\n",
    "print(len(date))\n",
    "print(len(rating))\n",
    "print(len(reviews))\n",
    "print(len(user))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "column_names = [\"user\",\"date\", \"rating\",'review']\n",
    "df = pd.DataFrame(columns = column_names)\n",
    "\n",
    "df['user'] = user\n",
    "df['date'] = date\n",
    "df['rating'] = rating\n",
    "df['review'] = reviews\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_excel(\"Tacombi Nolita Yelp Crawl Total.xlsx\",index=False)\n",
    "df.to_csv(\"Tacombi Nolita Yelp Crawl Total.txt\",index=False)\n",
    "df.to_csv(\"Tacombi Nolita Yelp Crawl Total.csv\",index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Cleaning Date:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "extracting_date =[]\n",
    "for k in range(0,len(df)):\n",
    "    extracting_date.append(df.date[k])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "cleaning_date = []\n",
    "\n",
    "for x in extracting_date:\n",
    "    if 'Updated review' in x:\n",
    "        clean = x[ :-14]\n",
    "        cleaning_date.append(clean)\n",
    "    elif 'Previous review' in x:\n",
    "        clean  = x[ :-15]\n",
    "        cleaning_date.append(clean)\n",
    "    else:\n",
    "        clean = x\n",
    "        cleaning_date.append(clean)\n",
    "            \n",
    "df['date'] = cleaning_date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_excel(\"Tacombi Nolita Yelp Crawl-Cleaned Data.xlsx\",index=False)\n",
    "df.to_csv(\"Tacombi Nolita Yelp Crawl-Cleaned Data.txt\",index=False)\n",
    "df.to_csv(\"Tacombi Nolita Yelp Crawl-Cleaned Data.csv\",index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Further cleaning and organizing of date:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "#need to reset data because need \"updated review\" vs \"previous review\"\n",
    "import pandas as pd\n",
    "\n",
    "column_names = [\"user\",\"date\", \"rating\",'review']\n",
    "df = pd.DataFrame(columns = column_names)\n",
    "\n",
    "df['user'] = user\n",
    "df['date'] = date\n",
    "df['rating'] = rating\n",
    "df['review'] = reviews\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "#extract previous date from dates, put in list, place list as new colum\n",
    "previous_review_date = []\n",
    "for k in range(0,len(df)):\n",
    "    if 'Previous review' in df.date[k]:\n",
    "        previous_date = df.date[k]\n",
    "        previous_review_date.append(previous_date)\n",
    "    else:\n",
    "        previous_date = None\n",
    "        previous_review_date.append(previous_date)\n",
    "df['previous_date'] = previous_review_date "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "range_data = len(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "#extract previous rating from ratings, put in list, place list as new column\n",
    "prev_rating = []\n",
    "for k in range(0,range_data):\n",
    "    if df.previous_date[k] != None:\n",
    "        previous_rating = df.rating[k]\n",
    "        prev_rating.append(previous_rating)\n",
    "    else:\n",
    "        previous_rating = None\n",
    "        prev_rating.append(previous_rating)\n",
    "df['prev_rating'] = prev_rating "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "#extract previous reviews from reviews, put in list, place list as new column\n",
    "prev_review = []\n",
    "for k in range(0,range_data):\n",
    "    if df.previous_date[k] != None:\n",
    "        previous_review = df.review[k]\n",
    "        prev_review.append(previous_review)\n",
    "    else:\n",
    "        previous_review = None\n",
    "        prev_review.append(previous_review)\n",
    "df['prev_review'] = prev_review "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "#start emptying the rows we need to delete because shifting previous reviews to new row\n",
    "for k in range(0,len(df)):\n",
    "    if df.prev_review[k] != None:\n",
    "        df.user[k]= None\n",
    "        df.date[k]= None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "#to bring the previous review to match the users row\n",
    "for k in range(0,range_data):\n",
    "    if df.user[k]== None:\n",
    "        df.previous_date[k-1] = df.previous_date[k]\n",
    "        df.prev_rating[k-1] = df.prev_rating[k]\n",
    "        df.prev_review[k-1] = df.prev_review[k]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "#extracting previous date column so we can clean it/manipulate it\n",
    "extract_previous_date = []\n",
    "for k in range(0,range_data):\n",
    "    extract_previous_date.append(df.previous_date[k])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "#want to take out the 'previous review' from the date\n",
    "fixed_date=[]\n",
    "\n",
    "for x in extract_previous_date:\n",
    "    if x != None:\n",
    "        fixed = x[ :-15]\n",
    "        fixed_date.append(fixed)\n",
    "    else:\n",
    "        fixed = None\n",
    "        fixed_date.append(fixed)\n",
    "df['previous_date'] = fixed_date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "#extracting date column so we can clean it/manipulate it\n",
    "extract_date = []\n",
    "for k in range(0,range_data):\n",
    "    extract_date.append(df.date[k])\n",
    "\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "#want to take out the 'updated review' from the date\n",
    "fixed_date2 = []\n",
    "\n",
    "for x in extract_date:\n",
    "    if x != None:\n",
    "        if 'Updated review' in x:\n",
    "            fixed2 = x[ :-14]\n",
    "            fixed_date2.append(fixed2)\n",
    "        else:\n",
    "            fixed2 = x\n",
    "            fixed_date2.append(fixed2)\n",
    "    else:\n",
    "        fixed2 = x\n",
    "        fixed_date2.append(fixed2)\n",
    "df['date'] = fixed_date2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "#want to take out where we put none for users (since put previous review in own columns)\n",
    "unwanted_index = []\n",
    "\n",
    "for k in range(0,range_data):\n",
    "    if df.user[k]== None:\n",
    "        unwanted_index.append(k)\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "#we want the index without unwanted index\n",
    "wanted_index = []\n",
    "for k in range(0,range_data):\n",
    "    if k not in unwanted_index:\n",
    "        wanted_index.append(k)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.loc[wanted_index]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "scrolled": true
   },
   "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>user</th>\n",
       "      <th>date</th>\n",
       "      <th>rating</th>\n",
       "      <th>review</th>\n",
       "      <th>previous_date</th>\n",
       "      <th>prev_rating</th>\n",
       "      <th>prev_review</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Al P.</td>\n",
       "      <td>8/18/2020</td>\n",
       "      <td>1 star rating</td>\n",
       "      <td>If You mark your food \"ready to take out\" and ...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Natalie R.</td>\n",
       "      <td>1/6/2020</td>\n",
       "      <td>4 star rating</td>\n",
       "      <td>I don't know why I had low expectations for Ta...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Marlena K.</td>\n",
       "      <td>10/22/2019</td>\n",
       "      <td>5 star rating</td>\n",
       "      <td>Honestly I'm not sure what all the negative ra...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Elizabeth D.</td>\n",
       "      <td>1/26/2019</td>\n",
       "      <td>5 star rating</td>\n",
       "      <td>I need to update my review from my last one. I...</td>\n",
       "      <td>1/4/2019</td>\n",
       "      <td>2 star rating</td>\n",
       "      <td>Very disappointed. I ordered the huevos mexica...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Yajaira R.</td>\n",
       "      <td>10/2/2019</td>\n",
       "      <td>5 star rating</td>\n",
       "      <td>I usually come here when Im in the area, I can...</td>\n",
       "      <td>9/15/2019</td>\n",
       "      <td>5 star rating</td>\n",
       "      <td>I enjoy coming to all the Tacombi locations es...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Molly O.</td>\n",
       "      <td>9/13/2020</td>\n",
       "      <td>1 star rating</td>\n",
       "      <td>I want to preface this by saying I LOVE Tacomb...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Jenny L.</td>\n",
       "      <td>6/25/2019</td>\n",
       "      <td>4 star rating</td>\n",
       "      <td>One of the best spots for tacos!! Absolutely l...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            user        date         rating  \\\n",
       "15         Al P.   8/18/2020  1 star rating   \n",
       "16    Natalie R.    1/6/2020  4 star rating   \n",
       "17    Marlena K.  10/22/2019  5 star rating   \n",
       "18  Elizabeth D.   1/26/2019  5 star rating   \n",
       "20    Yajaira R.   10/2/2019  5 star rating   \n",
       "22      Molly O.   9/13/2020  1 star rating   \n",
       "23      Jenny L.   6/25/2019  4 star rating   \n",
       "\n",
       "                                               review previous_date  \\\n",
       "15  If You mark your food \"ready to take out\" and ...          None   \n",
       "16  I don't know why I had low expectations for Ta...          None   \n",
       "17  Honestly I'm not sure what all the negative ra...          None   \n",
       "18  I need to update my review from my last one. I...      1/4/2019   \n",
       "20  I usually come here when Im in the area, I can...     9/15/2019   \n",
       "22  I want to preface this by saying I LOVE Tacomb...          None   \n",
       "23  One of the best spots for tacos!! Absolutely l...          None   \n",
       "\n",
       "      prev_rating                                        prev_review  \n",
       "15           None                                               None  \n",
       "16           None                                               None  \n",
       "17           None                                               None  \n",
       "18  2 star rating  Very disappointed. I ordered the huevos mexica...  \n",
       "20  5 star rating  I enjoy coming to all the Tacombi locations es...  \n",
       "22           None                                               None  \n",
       "23           None                                               None  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[15:22]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Make sure to export - can change the export format to txt or whatever you need\n",
    "df.to_excel(\"Tacombi Nolita Yelp Crawl-Cleaned Data + New Columns.xlsx\",index=False)\n",
    "df.to_csv(\"Tacombi Nolita Yelp Crawl-Cleaned Data + New Columns.txt\",index=False)\n",
    "df.to_csv(\"Tacombi Nolita Yelp Crawl-Cleaned Data + New Columns.csv\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}