<jupyter_start><jupyter_text>Let's YELP to Open Restaurants  Team 8 <NAME>, <NAME>, <NAME>, <NAME> and <NAME>  Our Dataset: We have used the [Yelp](https://www.kaggle.com/yelp-dataset/yelp-dataset/version/6) dataset from Kaggle, which is a subset of Yelp's businesses, reviews, and user data. It gives us information about businesses across 11 metropolitan areas in 4 countries.<jupyter_code>%%bq query -n Restaurant_number_piechart

SELECT
  category_name,
  COUNT(*) AS number
FROM (
  SELECT
    business_id,
    name,
    SPLIT(categories,";") AS category
  FROM
    `team-8-bs-770-b.Yelp.Yelp_business`),
  UNNEST( category ) AS category_name
GROUP BY category_name
ORDER BY number DESC
LIMIT 5

# First, we clean the data from the "Yelp_business" database and unnest the array of category column of each business. 
# Then we select the name and the number of categories using group by. 
# The output gives us the total number of businesses present in the top 5 business categories.
%%chart pie --data Restaurant_number_piechart

title: Distribution of businesses
colors: ['af0606', 'navy', 'green', 'orange', 'purple']
height: 600
width: 800
pieStartAngle: 20
slices:
    0:
        offset: .2<jupyter_output><empty_output><jupyter_text>From the output and the pie chart, **"Restaurants"** is clearly the most prevalent business in the database, which comprises 38.9% of the top 5 categories - which is more than double of the second category "Shopping". This is the reason we only decided to focus on businesses which belong to the category "restaurants".NOTE: The category "Food" is an atypical category. Restaurants, shopping and other category of business may contain the "Food" category.  Business Problem: Our **aim** is to recommend to investors:1. Top **3 cities** to open restaurants in2. Unique combination of possible **cuisines and attributes** that would make the restaurant popular in each city respectively We will be basing these recommendations on **star ratings, check-ins and feedback** given by the customers.  Quick Look: Here is a quick look at the 4 tables we used for this analysis: **1.** The **"Yelp_business"** table which gave us basic information about the restaurants.<jupyter_code>%%bq query

SELECT * 
FROM `team-8-bs-770-b.Yelp.Yelp_business` 
WHERE categories LIKE ('%Restaurants%')
LIMIT 5<jupyter_output><empty_output><jupyter_text>**2.** The **"Yelp_Checkins"** table which gave us information about the number of checkins at restaurants.<jupyter_code>%%bq query

SELECT * 
FROM `team-8-bs-770-b.Yelp.Checkins`
LIMIT 5<jupyter_output><empty_output><jupyter_text>**3.** The **"Attributes"** table which gave us information about what attribute/service is provided by each restaurants.<jupyter_code>%%bq query

SELECT * 
FROM `team-8-bs-770-b.Yelp.Attributes` 
LIMIT 5<jupyter_output><empty_output><jupyter_text>**4.** The **"Yelp_reviews"** table which gave us information about what feedback customers have left for each of the restaurants.<jupyter_code>%%bq query

SELECT * 
FROM `team-8-bs-770-b.Yelp.Yelp_reviews`
LIMIT 5<jupyter_output><empty_output><jupyter_text>**Here's a look at how the star ratings are distributed for restaurants around the world:**<jupyter_code>%%bq query --n star_distribution

SELECT stars, count(stars) AS Number_Of_Restaurants
FROM `team-8-bs-770-b.Yelp.Yelp_business` 
WHERE categories LIKE ('%Restaurants%')
GROUP BY stars
ORDER BY stars

# In this snippet of code, we are trying to find out how many restaurants fall under each star category, respectively.
%%chart columns --data star_distribution

title: Distribution of stars for restaurants 
height: 800
hAxis:
  title: Number of Stars
vAxis:
  title: No. of Restaurants
legend: 'none'
colors: ['af0606']<jupyter_output><empty_output><jupyter_text>This chart shows us that most restaurants fall between 3 to 4.5 stars.<jupyter_code>%%bq query

SELECT ROUND(avg(stars),1) AS avg_stars
FROM `team-8-bs-770-b.Yelp.Yelp_business`
WHERE categories LIKE ("%Restaurants%")<jupyter_output><empty_output><jupyter_text>On further calculation, we found out that the **average star rating** for restaurants around the world is **3.5.**  Choosing the top 3 cities to open restaurants in:<jupyter_code>%%bq query -n Most_Visited_Cities_Restaurants

SELECT city,average_checkins
FROM `team-8-bs-770-b.Saved_queries.chosen_cities`
ORDER BY average_checkins DESC
limit 5

# This Query selects the city and their respective average check-ins per restaurant per week.
# It then organizes the data by average check-ins per restaurant in descending order.
# We will easily see which 5 cities have the most number of average check-ins per week.

%%chart columns --data Most_Visited_Cities_Restaurants

title: Average weekly number of restaurant check-ins in top cities
height: 800
hAxis: 
 title: 'City'
vAxis: 
 title: 'Average number of check-ins per restaurant'
legend: 'none'
colors: ['af0606']<jupyter_output><empty_output><jupyter_text>We created a columns chart to visualize the variation in check-ins for each of the cities. We plotted the city names on the x-axis and the average check-in per restaurant on the y-axis. Since the data was organized by the average check-ins per restaurants per week in descending order, we can conclude that the 5 cities shown above have the most check-in per restaurant, and from these, we narrowed our scope down to 3 cities, namely: **Las Vegas, Scottsdate and Phoenix**. These are the cities our team decided to further investigate and open restaurants in, as it shows that there is a significant demand for restaurants in general in these 3 cities.   Choosing top tags (attributes and cuisines) for Las Vegas:<jupyter_code>%%bq query

SELECT string_field_0 AS category, COUNT(string_field_0) AS category_count
FROM (SELECT
    business_id,
    name,
    SPLIT(categories,";") AS category
    FROM
    `team-8-bs-770-b.Yelp.Yelp_business`
    WHERE city = 'Las Vegas' AND categories LIKE '%Restaurants%'),
  UNNEST( category ) AS string_field_0
WHERE string_field_0 NOT IN ('Restaurants', 'Food')
GROUP BY category
ORDER BY category_count DESC
LIMIT 10


# First, we use this code snippet to figure out the top 10 tags that appear most often in Las Vegas.
%%bq query --n top_tags_for_lasvegas

SELECT
  each_cat,
  ROUND((SUM(total_checkins)/ COUNT(business_id)),2) AS avg_checkins_per_category
FROM
  `team-8-bs-770-b.Final_Tables.LasVegas_Final`
WHERE
  each_cat IN ('Fast Food',
    'Nightlife',
    'American (Traditional)',
    'Bars',
    'Mexican',
    'Sandwiches',
    'Pizza',
    'American (NEW)',
    'Burgers',
    'Chinese')
GROUP BY
  each_cat
HAVING
  AVG(stars) > 3.5
ORDER BY
  avg_checkins_per_category DESC

# Next, we are trying to find out which tags, amongst the top 10 most frequently occurring tags, help restaurants perform well in Las Vegas.
 
# Our definition for good performance = having the highest number of average checkins per week & higher than average star ratings (3.5).
%%chart columns --data top_tags_for_lasvegas

title: 'Weekly average check-ins for top performing tags in Las Vegas'
height: 800
hAxis: 
 title: 'Tags'
vAxis: 
 title: 'Average Check-Ins per restaurant'
legend: 'none'
colors: ['af0606']<jupyter_output><empty_output><jupyter_text>From the graph above, we can see that in Las Vegas, restaurants that usually perform well are the ones which boast these 3 tags: **"Bars", "Nightlife"** and **"Mexican"**.**Thus, we recommend a restaurant which would serve Mexican food, have a bar and host nightlife.**  Choosing top tags (attributes and cuisines) for Phoenix:<jupyter_code>%%bq query 

SELECT string_field_0 AS category, COUNT(string_field_0) AS category_count
FROM (SELECT
    business_id,
    name,
    SPLIT(categories,";") AS category
    FROM
    `team-8-bs-770-b.Yelp.Yelp_business`
    WHERE city = 'Phoenix' AND categories LIKE '%Restaurants%'),
  UNNEST( category ) AS string_field_0
WHERE string_field_0 NOT IN ('Restaurants', 'Food')
GROUP BY category
ORDER BY category_count DESC
LIMIT 10

# First, we use this code snippet to figure out the top 10 tags that appear most often in Phoenix.
%%bq query --n top_tags_for_phoenix

SELECT
  category,
  ROUND((SUM(total_checkins)/ COUNT(business_id)),2) AS avg_checkins_per_category
FROM
  `team-8-bs-770-b.Categories_By_City.Phoenix_in_process2`
WHERE
  category IN ('Mexican',
    'Fast Food',
    'Sandwiches',
    'American (Traditional)',
    'Nightlife',
    'Bars',
    'Pizza',
    'Breakfast & Brunch',
    'Burgers',
    'American (New)')
GROUP BY
  category
HAVING
  AVG(stars) > 3.5
ORDER BY
  avg_checkins_per_category DESC

# Next, we are trying to find out which tags, amongst the top 10 most frequently occurring tags, help restaurants perform well in Phoenix.
 
# Our definition for good performance = having the highest number of average checkins per week & higher than average star ratings (3.5).
%%chart columns --data top_tags_for_phoenix

title: 'Weekly average check-ins for top performing tags in Phoenix'
height: 800
hAxis: 
 title: 'Tags'
vAxis: 
 title: 'Average Check-Ins per restaurant'
legend: 'none'
colors: ['af0606']<jupyter_output><empty_output><jupyter_text>From the graph above, we can see that in Phoenix, restaurants that usually perform well are the ones which boast these 5 tags: **"Bars", "Nightlife", "American(new)", "Breakfast & Brunch" and "Mexican"**.**Thus, we recommend a restaurant which would serve either Mexican or American food, have a bar, host nightlife and have an option of serving Breakfast/Brunch.**  Choosing top tags (attributes and cuisines) for Scottsdale:<jupyter_code>%%bq query

SELECT string_field_0 AS category, COUNT(string_field_0) AS category_count
FROM (SELECT
    business_id,
    name,
    SPLIT(categories,";") AS category
    FROM
    `team-8-bs-770-b.Yelp.Yelp_business`
    WHERE city = 'Scottsdale' AND categories LIKE '%Restaurants%'),
  UNNEST( category ) AS string_field_0
WHERE string_field_0 NOT IN ('Restaurants', 'Food')
GROUP BY category
ORDER BY category_count DESC
LIMIT 10
# First, we use this code snippet to figure out the top 10 tags that appear most often in Scottsdale.
%%bq query --n top_tags_for_scottsdale

SELECT
  each_cat,
  ROUND((SUM(total_checkins)/ COUNT(business_id)),2) AS avg_checkins_per_category
FROM
  `team-8-bs-770-b.Categories_By_City.Scottsdale_in_process2`
WHERE
  each_cat IN ('Mexican',
    'Fast Food',
    'Sandwiches',
    'American (Traditional)',
    'Nightlife',
    'Bars',
    'Pizza',
    'Breakfast & Brunch',
    'Italian',
    'American (New)')
GROUP BY
  each_cat
HAVING
  AVG(stars) > 3.5
ORDER BY
  avg_checkins_per_category DESC

# Next, we are trying to find out which tags, amongst the top 10 most frequently occurring tags, help restaurants perform well in Scottsdale.
 
# Our definition for good performance = having the highest number of average checkins per week & higher than average star ratings (3.5).

%%chart columns --data top_tags_for_scottsdale

title: 'Weekly average check-ins for top performing tags in Scottsdale'
height: 800
hAxis: 
 title: 'Tags'
vAxis: 
 title: 'Average Check-Ins per restaurant'
legend: 'none'
colors: ['af0606']<jupyter_output><empty_output><jupyter_text>From the graph above, we can see that in Phoenix, restaurants that usually perform well are the ones which boast these 5 tags: **"Breakfast & Brunch", "Nightlife", "Bars", "American(new)", "American (Traditional)", "Sandwiches", "Pizza" and "Italian"**.**Thus, we recommend a restaurant which would preferably serve American food or alternatively, Italian food, have a bar, host nightlife and most importantly, serve Breakfast/Brunch.**  Offering what attributes/services increases average star rating for restaurants round the world?<jupyter_code>%%bq query --n star_attribute

SELECT 'all_false' AS attributes, AVG(stars) AS avg_star
FROM `team-8-bs-770-b.attributes_new.8_attributes_cleaned`
WHERE BusinessAcceptsCreditCards = FALSE
AND BusinessParking_garage = FALSE
AND WheelchairAccessible = FALSE
AND BikeParking = FALSE
AND Alcohol = FALSE
AND HappyHour = FALSE
AND OutdoorSeating = FALSE
UNION ALL
SELECT 'BusinessAcceptsCreditCards' AS attributes , AVG(stars) AS avg_star
FROM `team-8-bs-770-b.attributes_new.8_attributes_cleaned`
WHERE BusinessAcceptsCreditCards = TRUE
UNION ALL
SELECT 'BusinessParking_garage' AS attributes , AVG(stars) AS avg_star
FROM `team-8-bs-770-b.attributes_new.8_attributes_cleaned`
WHERE BusinessParking_garage = TRUE
UNION ALL
SELECT 'WheelchairAccessible' AS attributes , AVG(stars) AS avg_star
FROM `team-8-bs-770-b.attributes_new.8_attributes_cleaned`
WHERE WheelchairAccessible = TRUE
UNION ALL
SELECT 'BikeParking' AS attributes , AVG(stars) AS avg_star
FROM `team-8-bs-770-b.attributes_new.8_attributes_cleaned`
WHERE BikeParking = TRUE
UNION ALL
SELECT 'Alcohol' AS attributes , AVG(stars) AS avg_star
FROM `team-8-bs-770-b.attributes_new.8_attributes_cleaned`
WHERE Alcohol = TRUE
UNION ALL
SELECT 'HappyHour' AS attributes , AVG(stars) AS avg_star
FROM `team-8-bs-770-b.attributes_new.8_attributes_cleaned`
WHERE HappyHour = TRUE
UNION ALL
SELECT 'OutdoorSeating' AS attributes , AVG(stars) AS avg_star
FROM `team-8-bs-770-b.attributes_new.8_attributes_cleaned`
WHERE OutdoorSeating = TRUE
ORDER BY avg_star DESC<jupyter_output><empty_output><jupyter_text>The query above selects the star rating for the restaurants where only one attribute/service is provided (TRUE), while others are kept constant at FALSE.<jupyter_code>%%chart columns --data star_attribute

title: 'Influence of attributes on a restaurants star rating'
height: 800
hAxis: 
 title: 
vAxis: 
 title: 'Average Check-Ins per restaurant'
legend: 'none'
colors: ['af0606']<jupyter_output><empty_output><jupyter_text>Apart from personalized recommendations for each city, we studied eight attributes/services - which when provided - help a restaurant perform better, globally.We analyzed them to find out the extent to which each attribute contributed to the star ratings.**The results show that the attribute pertaining to businesses accepting credit cards as payment, has the biggest influence on the star rating, followed by their ability to provide “alcohol” and a “parking garage”, while the presence of “wheelchair accessibility” almost has no contribution to the star rating.**  Most frequently occuring words in reviews We separated the review words into two groups, those which were in the “bad reviews”, the customers who wrote these gave the restaurant 1 or 2 stars and “good reviews” these customers gave 4 or 5 stars.<jupyter_code>%%bq query
SELECT count(words) repetition, Description
FROM `team-8-bs-770-b.Saved_queries.ReviewSplit` 
 , UNNEST(words ) as Description
group by Description,stars
having stars=1 or stars=2
order by repetition desc
%%bq query
SELECT count(words) repetition, Description
FROM `team-8-bs-770-b.Saved_queries.ReviewSplit` 
 , UNNEST(words ) as Description
group by Description,stars
having stars=4 or stars=5
order by repetition desc<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08519283287817225, 'fraction_numerical': 0.016852061546659563, 'mean_word_length': 5.110704110704111, 'pattern_counts': {'":': 0, '<': 62, '<?xml version=': 0, '>': 65, 'https://': 1, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '14', 'dir': 'jupyter-structured-clean-dedup', 'id': '519881', 'license': 'MIT', 'n_tokens_mistral': 4828, 'n_tokens_neox': 4499, 'n_words': 1760, 'path': 'docs/team-projects/Summer-2019/Team-B8/BA770_B_Team_8_Final_Notebook.ipynb', 'repo_name': 'JQmiracle/Business-Analytics-Toolbox', 'size': '15041'}