yelp_data_clean.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "a55cb160",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "e3086b0b",
   "metadata": {},
   "outputs": [],
   "source": [
    "bu = pd.read_csv(\"yelp_business.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "91ff2ab0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Removes all values that are not in Santa Barbara\n",
    "bu = bu[bu[\"city\"] == \"Santa Barbara\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "41214ba9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Removes all values that are not restaurants\n",
    "bu[\"categories\"] = bu[\"categories\"].astype(\"str\")\n",
    "bu[\"categories\"] = bu[\"categories\"].apply(lambda x: set(x.split(\", \")))\n",
    "\n",
    "word_list = [\"Restaurants\", \"Food\", \"Restaurant\"]\n",
    "bu[\"categories\"] = bu[\"categories\"].apply(lambda x: x.intersection(word_list))\n",
    "bu = bu[bu[\"categories\"] != set()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "a04a6f66",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Removes all values that are not open\n",
    "bu = bu[bu[\"is_open\"] == 1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "5f6930e3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drops unnessescary columns and does some formatting \n",
    "bu = bu.drop(columns=['_id', 'city', 'is_open', 'state', 'categories'])\n",
    "bu[\"attributes.RestaurantsTakeOut\"].fillna(\"False\", inplace = True)\n",
    "bu[\"attributes.RestaurantsTakeOut\"] = bu[\"attributes.RestaurantsTakeOut\"].replace(\"None\", \"False\")\n",
    "bu = bu.rename(columns={\"attributes.RestaurantsTakeOut\":\"take_out\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "5c56e53f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Reads the reviews csv\n",
    "re = pd.read_csv(\"yelp_review.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "2beea246",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Make the st series which is indexed by tuples of business id and star value eg (\"thisisanid\", 1)\n",
    "re = pd.merge(bu, re, how ='inner', on =['business_id']).drop(columns=[\"address\", \"take_out\", \"latitude\", \"longitude\", \"name\", \n",
    "                                                               \"review_count\", \"stars_x\"]).rename(columns={\"stars_y\":\"stars\"})\n",
    "st = re.groupby([\"business_id\", \"stars\"]).count()['review_id']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "e5fb2c01",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Iterates through st and makes lists of star values, and adds them to the dictionary with the business id as the code\n",
    "# stars[\"thisisanid\"] = [0,0,0,10,50] is a for a business with id \"thisisanid\" that got 0 1,2, and 3 star reviews, 10 4 star and 50 5 star\n",
    "stars = {}\n",
    "for key in st.index:\n",
    "    if key[0] not in stars:\n",
    "        stars[key[0]] = [0,0,0,0,0]\n",
    "\n",
    "    stars[key[0]][key[1]-1] = st[key]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "06abd277",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "business_id             stars\n",
       "-3AooxIkg38UyUdlz5oXdw  1        2021-12-28 02:40:57\n",
       "                        2        2021-11-29 16:04:16\n",
       "                        3        2021-10-01 02:33:33\n",
       "                        4        2021-12-29 02:04:15\n",
       "                        5        2022-01-05 05:43:43\n",
       "Name: date, dtype: object"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Gets the dates for each restaurant/star value combo\n",
    "dt = re.groupby([\"business_id\", \"stars\"]).max()[\"date\"]\n",
    "dt.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "e277136d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Does the same thing as above with the dates\n",
    "dates = {}\n",
    "for key in dt.index:\n",
    "    if key[0] not in dates:\n",
    "        dates[key[0]] = [0,0,0,0,0]\n",
    "\n",
    "    dates[key[0]][key[1]-1] = dt[key]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "6c13f26b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# sorts bu\n",
    "bu = bu.sort_values(by='business_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "4953dbf7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# adds star/date lists to bu\n",
    "bu['stars'] = stars.values()\n",
    "bu['dates'] = dates.values()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "fb81e331",
   "metadata": {},
   "outputs": [],
   "source": [
    "# writes bu to the csv\n",
    "bu.to_csv('yelp_business_clean.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "68bd8cf8",
   "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>address</th>\n",
       "      <th>take_out</th>\n",
       "      <th>business_id</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>name</th>\n",
       "      <th>review_count</th>\n",
       "      <th>stars</th>\n",
       "      <th>dates</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9418</th>\n",
       "      <td>1012 State St</td>\n",
       "      <td>True</td>\n",
       "      <td>-3AooxIkg38UyUdlz5oXdw</td>\n",
       "      <td>34.421931</td>\n",
       "      <td>-119.702185</td>\n",
       "      <td>Chase Restaurant</td>\n",
       "      <td>436</td>\n",
       "      <td>[115, 56, 67, 62, 144]</td>\n",
       "      <td>[2021-12-28 02:40:57, 2021-11-29 16:04:16, 202...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76606</th>\n",
       "      <td>2036 Cliff Dr</td>\n",
       "      <td>True</td>\n",
       "      <td>-6jvfSJGprbfBD2QrS9zQw</td>\n",
       "      <td>34.402538</td>\n",
       "      <td>-119.724894</td>\n",
       "      <td>Mesa Produce</td>\n",
       "      <td>19</td>\n",
       "      <td>[0, 0, 0, 0, 19]</td>\n",
       "      <td>[0, 0, 0, 0, 2021-10-24 16:08:48]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62010</th>\n",
       "      <td>1417 San Andres St</td>\n",
       "      <td>True</td>\n",
       "      <td>-ALqLSTzkGDMscHdxA1NgA</td>\n",
       "      <td>34.418221</td>\n",
       "      <td>-119.715795</td>\n",
       "      <td>Su Casa Fresh Mexican Grill</td>\n",
       "      <td>29</td>\n",
       "      <td>[0, 2, 3, 7, 17]</td>\n",
       "      <td>[0, 2013-01-24 23:23:09, 2016-08-13 05:59:35, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>134706</th>\n",
       "      <td>1905 Cliff Dr</td>\n",
       "      <td>True</td>\n",
       "      <td>-BdYhP-12elmFV7oB1iv4A</td>\n",
       "      <td>34.401382</td>\n",
       "      <td>-119.722472</td>\n",
       "      <td>Corner Tap Room</td>\n",
       "      <td>41</td>\n",
       "      <td>[0, 0, 2, 5, 37]</td>\n",
       "      <td>[0, 0, 2021-10-21 03:56:27, 2021-10-30 15:46:1...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92579</th>\n",
       "      <td>31 W Carrillo St</td>\n",
       "      <td>True</td>\n",
       "      <td>-FM4CxOg4XXmX_Ebky_SiQ</td>\n",
       "      <td>34.420361</td>\n",
       "      <td>-119.702475</td>\n",
       "      <td>Finch &amp; Fork</td>\n",
       "      <td>1405</td>\n",
       "      <td>[63, 82, 132, 379, 778]</td>\n",
       "      <td>[2021-11-21 02:44:59, 2021-06-21 00:01:19, 202...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   address take_out             business_id   latitude  \\\n",
       "9418         1012 State St     True  -3AooxIkg38UyUdlz5oXdw  34.421931   \n",
       "76606        2036 Cliff Dr     True  -6jvfSJGprbfBD2QrS9zQw  34.402538   \n",
       "62010   1417 San Andres St     True  -ALqLSTzkGDMscHdxA1NgA  34.418221   \n",
       "134706       1905 Cliff Dr     True  -BdYhP-12elmFV7oB1iv4A  34.401382   \n",
       "92579     31 W Carrillo St     True  -FM4CxOg4XXmX_Ebky_SiQ  34.420361   \n",
       "\n",
       "         longitude                         name  review_count  \\\n",
       "9418   -119.702185             Chase Restaurant           436   \n",
       "76606  -119.724894                 Mesa Produce            19   \n",
       "62010  -119.715795  Su Casa Fresh Mexican Grill            29   \n",
       "134706 -119.722472              Corner Tap Room            41   \n",
       "92579  -119.702475                 Finch & Fork          1405   \n",
       "\n",
       "                          stars  \\\n",
       "9418     [115, 56, 67, 62, 144]   \n",
       "76606          [0, 0, 0, 0, 19]   \n",
       "62010          [0, 2, 3, 7, 17]   \n",
       "134706         [0, 0, 2, 5, 37]   \n",
       "92579   [63, 82, 132, 379, 778]   \n",
       "\n",
       "                                                    dates  \n",
       "9418    [2021-12-28 02:40:57, 2021-11-29 16:04:16, 202...  \n",
       "76606                   [0, 0, 0, 0, 2021-10-24 16:08:48]  \n",
       "62010   [0, 2013-01-24 23:23:09, 2016-08-13 05:59:35, ...  \n",
       "134706  [0, 0, 2021-10-21 03:56:27, 2021-10-30 15:46:1...  \n",
       "92579   [2021-11-21 02:44:59, 2021-06-21 00:01:19, 202...  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bu.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "96955cc1",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.15"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}