"""
# Restaurant Recommendation System
"""
"""
## 2. Exploratory Data Analysis (EDA)
"""
"""
## Aim
After getting our data ready, we still want to make sense of it. In EDA we look at various plots and actually let the data tell us its story. This step will give us a deeper understanding of data. We'll also try to make data more amenable to modelling. We'll look at various table in database.

We'll be using matplolib and seaborn to make various plots.
"""
"""
### Importing Libraries
"""
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns

import json
from sqlite3 import dbapi2 as sq3
from pathlib import Path
from collections import OrderedDict

from sklearn.model_selection import train_test_split
import tensorflow as tf
from tensorflow import keras


from time import time
from IPython.display import clear_output
from collections import OrderedDict

plt.style.use('ggplot')
# Functions to work with SQLite db
def make_query(sel):
    """Query database"""
    c = db.cursor().execute(sel)
    return c.fetchall()

def make_frame(list_of_tuples=None, legend=[], query=None):
    """
    Returns DataFrame from a query or result of query
    """
    framelist=[]
    if list_of_tuples is None:
        if query is None:
            print("Error: No query made")
            return
        list_of_tuples = make_query(query)
        
    for i, cname in enumerate(legend):
        framelist.append((cname,[e[i] for e in list_of_tuples]))
    return pd.DataFrame.from_dict(OrderedDict(framelist))

# Connect to database
db = sq3.connect('../input/yelp-project/yelp_database.db')
"""
### Reviews Table
"""
# Looking at columns of 'reviews' table
make_query("PRAGMA table_info(reviews)")
%%time
# Getting pandas DataFrame from db
date_df = make_frame(query="SELECT stars, date FROM reviews", legend=['rating', 'date'])
date_df.date = pd.to_datetime(date_df.date)
date_df.info()
# Creating new columns in reviews df
date_df['day'] = date_df.date.dt.day
date_df['month'] = date_df.date.dt.month
date_df['year'] = date_df.date.dt.year
date_df['hour'] = date_df.date.dt.hour
date_df['minute'] = date_df.date.dt.minute
date_df['second'] = date_df.date.dt.second

date_df.head()
sns.catplot(data=date_df, x='rating', kind='count', aspect=2)
ax = plt.gca()
ax.set(xticks=[0,1,2,3,4,5], title='Rating Distribution')
ax.tick_params('x', labelsize=15)
ax.tick_params('y', labelsize=15)
"""
We see many most ratings are on the higher scale. Most ratings are either 5 star or 4 star.
"""
g = sns.catplot(data=date_df, x='year', kind='count', aspect=2)
ax = plt.gca()
ax.set_title('Number of reviews by Year')
"""
We see number of reviews increased exponentially over the years. This could also be an evidence for yelp's popularity over the years.
"""
g = sns.relplot(data=date_df, x='month', y='rating', aspect=2, kind='line')
ax = plt.gca()
ax.set_title('Ratings by Month')
"""
We see that average rating after faceting on month is minimum for 12th month ~ December. But if we look at the y-scale, change is not so significant to draw a trend.
"""
g = sns.relplot(data=date_df, x='day', y='rating', aspect=2, kind='line')
ax = plt.gca()
ax.set_title('Ratings by Day')
"""
We see that average rating after faceting on day is max from 5th to 10th day. But if we look at the y-scale, change is not so significant to draw a trend.
"""
sns.catplot(data=date_df, x='hour', aspect=2, kind='count')
ax = plt.gca()
ax.set_title('Review Time')
"""
This is interesting as we see minimum number of reviews were given during morning and count keeps rising throughout the day which seems intuitive. But maximum number of reviews were given at 2am which is counter intuitive. Why are more people reviewing this late? Is it possible that these are bars where people stay for long but why would drunks care about reviewing? Or are people reviewing after dinner during their commute home? We would have to look at further evidence to draw conclusions.
"""
#Release memory
del date_df
"""
### Users Table
"""
#Looking at columns and making a DataFrame from users table
cols = list(zip(*make_query("PRAGMA table_info(users)")))[1]
users_df = make_frame(query='SELECT * FROM users;', legend=cols)
users_df.info()
plt.figure(figsize=(10,5))
ax = plt.gca()
sns.boxplot(data=users_df, x='review_count', ax=ax)
plt.xscale('log')
"""
The boxplot for review count reveals a lot of outliers. There are some users who have written over 10k reviews.
"""
#Type Cast to datetime format
users_df.yelping_since = pd.to_datetime(users_df.yelping_since)
sns.displot(data=users_df, x='yelping_since', aspect=2)
"""
Most people are yelping_since mid 2010s.
"""
sns.displot(data=users_df, x='fans', aspect=2)
plt.xscale('log')
plt.yscale('log')
"""
Some users are wildly popular reviewers on yelp. Most people have few or no fans
"""
sns.relplot(data=users_df, x='yelping_since', y='average_stars',alpha=.1, aspect=2)
"""
We see that reviewers who have been yelping for long have higher avg rating. We can conclude that overtime users become less harsh reviewers.
"""
#Release Memory
del users_df
"""
### Businesses table
"""
# Looking at business table's columns and theirdtypes
make_query("PRAGMA table_info(businesses)")
#Getting dataframe from table
business_df_cols = list(zip(*make_query("PRAGMA table_info(businesses)")))[1]
business_df = make_frame(query='SELECT * FROM businesses', legend=business_df_cols)
business_df.head()
sns.jointplot(data=business_df, x='latitude', y='longitude')
"""
We see that locations of businesses are concentrated in clusters. These clusters must be big cities. Lets plot these on a map.
"""
BBox = (business_df.longitude.min(),   business_df.longitude.max(), business_df.latitude.min(), business_df.latitude.max())

img = plt.imread('../input/map-img-for-yelp-business-df/map(1).png')[:,:,:-1]
fig, ax = plt.subplots(figsize = (18,14))
ax.scatter(business_df.longitude, business_df.latitude, zorder=1, alpha= 0.1, s=5)
ax.set_title('Plotting Restaurant Locations on Map')
ax.set_xlim(-130,BBox[1])
ax.set_ylim(BBox[2],BBox[3])
ax.imshow(img, zorder=0, extent = BBox, aspect='equal')
"""
We see our data has businesses from certain cities of U.S. and not all over U.S.
"""
def plot(feature):
    plt.figure(figsize=(8, 8))
    sns.relplot(data=business_df, y='stars', x='review_count', col=feature, alpha=.4)

bool_features = []
for tup in make_query("PRAGMA table_info(businesses)"):
    if tup[2]=='BOOLEAN':
        bool_features.append(tup[1])

for feature in bool_features:
    plot(feature)
"""
- Restaurants with Attire have higher ratings and more higher review count hence more popular than those without Attire.
- Restaurants with TakeOut, AcceptCreditCard, GoodForKids, Reservation, GoodForGroups, BusinessParking, HasTV, Alcohol, BikeParking, Delivery, OutdoorSeating, WiFi, Ambience, DogsAllowed, GoodForDancing, CoatCheck, CounterService show similar trend.
- Restaurnts with NoiseLevel also have higher avg ratings, this could be because they are located in prime locations.
- ByAppointmentOnly restaurants show roughly similar trend to Not ByAppointmentOnly restaurants.
- Restaurants with Outdoor Seating have better ratings and higher review count than those without OutdoorSeating. Hence restaurants with OutdoorSeating are more popular.
- Restaurants which are WheelChairAccessible do not perform better than those who aren't.
- Restaurants with WiFi are also more popular than those without.
- Some restaurants without TableService have higher reviewCount than those with.
- Restaurants with Dogs allowed also appear more popular.
- A/c to plot, Ambience plays a big role in a restaurants rating and review count. Those with ambience are more popular.
- Plots for restaurants with HappyHour, DriveThrough, Music, BestNights or AcceptsBitcoin do not differ significantly than those without.
- Restaurants without DietaryRestrictions are more highly rated than those with. 
- Restaurants with Open24Hours=0 are underrepresented.
"""
plt.figure(figsize=(8,8))
top_10_zip = business_df.postal_code.value_counts()[:10]
sns.barplot(x=top_10_zip.index, y=top_10_zip.values)
ax = plt.gca()
ax.set_title('Most Popular Zip codes')
ax.set_xlabel('Zip Code')
ax.set_ylabel('Count')

"""
Our data has most number of businesses from zip code 89109 (Las Vegas). This can be verified on map.
"""
toprating_df = business_df[business_df["stars"]==5]
toprating_df = toprating_df.sort_values('review_count', ascending=False).head(20)

plt.figure(figsize=(15,7))
p = sns.barplot(x='name', y="review_count", data=toprating_df,color="b")
p.set_xticklabels(p.get_xticklabels(), rotation = 90, fontsize = 8)
p.set_title("Top 5 star-rated Restuarants sorted by review count")
p.set(xlabel="Restaurant", ylabel="Review Count")
"""
Restaurant businesses with ratings=5 and highest review counts
"""
df_restaurants = business_df.name.value_counts().index[:20].tolist()
df_top = business_df.loc[business_df['name'].isin(df_restaurants)]
mean_df = df_top.groupby('name')['stars'].mean()
meanrating_df = mean_df.reset_index()
topmean_rating_df = meanrating_df.sort_values('stars', ascending=False).head(20)

plt.figure(figsize=(15,7))
p = sns.barplot(x='name', y="stars",data=topmean_rating_df, color="b")
p.set_xticklabels(p.get_xticklabels(), rotation = 90, fontsize = 8)
p.set_title("Top 5 star-rated Restaurants sorted by mean of ratings")
p.set(xlabel="Restaurant", ylabel="Rating")
"""
Restaurant businesses with highest mean scores of ratings, with 20 occurences
"""
plt.figure(figsize=(15,7))
sns.lineplot(x=business_df["stars"],y=business_df["review_count"],hue=business_df["DriveThru"],ci=80)
plt.legend(bbox_to_anchor=(1.00, 1), title ="DriveThru")
plt.show()

"""
Restaurants with Drive Through have higher ratings and higher review count. Hence are more popular.
"""
plt.figure(figsize=(15,7))
sns.lineplot(x=business_df["stars"],y=business_df["review_count"],hue=business_df["GoodForDancing"],ci=80)
plt.legend(bbox_to_anchor=(1.00, 1), title ="GoodForDancing")
plt.show()
"""
Restaurants GoodForDancing also appear more popular.
"""
plt.figure(figsize=(20,10))
sns.heatmap(business_df.corr(),annot=False)
plt.show()
"""
We see some correlations roughly at the centre of heatmap. They could be due to redundant features like a Restaurant with CarParking will most likely have BikeParking, or because high end restaurants have most of the listed features.
"""
"""
## Conclusion
- We studied various plots. 
- Restaurants with TakeOut, AcceptCreditCard, GoodForKids, Reservation, GoodForGroups, BusinessParking, HasTV, Alcohol, BikeParking, Delivery, OutdoorSeating, WiFi, Ambience, DogsAllowed, GoodForDancing, CoatCheck, CounterService, Attire are more popular in general.

"""
"""
#### Links
[1. Getting Data Ready](https://www.kaggle.com/yashrajwani/yelp-getting-data-ready)

[3. Restaurant Recommendation](https://www.kaggle.com/yashrajwani/yelp-restaurant-recommendation-system)
"""