Data Scientist Role Play: Profiling and Analyzing the Yelp Dataset Coursera Worksheet

This is a 2-part assignment. In the first part, you are asked a series of questions that will help you profile and understand the data just like a data scientist would. For this first part of the assignment, you will be assessed both on the correctness of your findings, as well as the code you used to arrive at your answer. You will be graded on how easy your code is to read, so remember to use proper formatting and comments where necessary.

In the second part of the assignment, you are asked to come up with your own inferences and analysis of the data for a particular research question you want to answer. You will be required to prepare the dataset for the analysis you choose to do. As with the first part, you will be graded, in part, on how easy your code is to read, so use proper formatting and comments to illustrate and communicate your intent as required.

For both parts of this assignment, use this "worksheet." It provides all the questions you are being asked, and your job will be to transfer your answers and SQL coding where indicated into this worksheet so that your peers can review your work. You should be able to use any Text Editor (Windows Notepad, Apple TextEdit, Notepad ++, Sublime Text, etc.) to copy and paste your answers. If you are going to use Word or some other page layout application, just be careful to make sure your answers and code are lined appropriately.
In this case, you may want to save as a PDF to ensure your formatting remains intact for you reviewer.



Part 1: Yelp Dataset Profiling and Understanding

1. Profile the data by finding the total number of records for each of the tables below:
	
i. Attribute table = 10000 
ii. Business table =10000 
iii. Category table =10000
iv. Checkin table =10000
v. elite_years table =10000
vi. friend table = 10000
vii. hours table =10000
viii. photo table = 10000
ix. review table = 10000
x. tip table = 10000
xi. user table =10000

2. Find the total distinct records by either the foreign key or primary key for each table. If two foreign keys are listed in the table, please specify which foreign key.

i. Business = 10000 (id)
ii. Hours = 1562 (business_id)
iii. Category = 2643 (business_id)
iv. Attribute = 1115(business_id)
v. Review = 8090 (business_id)     9581(user_id)
vi. Checkin =    493(business_id)
vii. Photo =  6493(business_id)
viii. Tip = 3979 (business_id)    537(user_id)
ix. User = 10000 (id)
x. Friend = 11 (user_id)
xi. Elite_years =  2780(user_id)

Note: Primary Keys are denoted in the ER-Diagram with a yellow key icon.(Within Brackets)

3. Are there any columns with null values in the Users table? Indicate "yes," or "no."

	Answer: No
	
	
	SQL code used to arrive at answer: 		
                                   SELECT COUNT(*)
		FROM user
		WHERE id IS NULL OR 
		  name IS NULL OR 
		  review_count IS NULL OR 
		  yelping_since IS NULL OR
		  useful IS NULL OR 
		  funny IS NULL OR 
		  cool IS NULL OR 
		  fans IS NULL OR 
		  average_stars IS NULL OR 
		  compliment_hot IS NULL OR 
		  compliment_more IS NULL OR 
		  compliment_profile IS NULL OR 
		  compliment_cute IS NULL OR 
		  compliment_list IS NULL OR 
		  compliment_note IS NULL OR 
		  compliment_plain IS NULL OR 
		  compliment_cool IS NULL OR 
		  compliment_funny IS NULL OR 
		  compliment_writer IS NULL OR 
		  compliment_photos IS NULL 

4. For each table and column listed below, display the smallest (minimum), largest (maximum), and average (mean) value for the following fields:

	i. Table: Review, Column: Stars
	
		min: 1		max: 5		avg:  3.7082
		
	
	ii. Table: Business, Column: Stars
	
		min:  1		max: 5		avg:  3.6549 
		
	
	iii. Table: Tip, Column: Likes
	
		min: 0		max: 2		avg:  0.0144
		
	
	iv. Table: Checkin, Column: Count
	
		min: 1		max: 53		avg:  1.9414
		
	
	v. Table: User, Column: Review_count
	
		min: 0		max: 2000		avg:  24.2995 


5. List the cities with the most reviews in descending order:

	SQL code used to arrive at answer:

select sum(Review_count) as reviews, city
from business
group by city
order by reviews desc
	
	Copy and Paste the Result Below:
	
	+---------+-----------------+
| reviews | city            |
+---------+-----------------+
|   82854 | Las Vegas       |
|   34503 | Phoenix         |
|   24113 | Toronto         |
|   20614 | Scottsdale      |
|   12523 | Charlotte       |
|   10871 | Henderson       |
|   10504 | Tempe           |
|    9798 | Pittsburgh      |
|    9448 | Montréal        |
|    8112 | Chandler        |
|    6875 | Mesa            |
|    6380 | Gilbert         |
|    5593 | Cleveland       |
|    5265 | Madison         |
|    4406 | Glendale        |
|    3814 | Mississauga     |
|    2792 | Edinburgh       |
|    2624 | Peoria          |
|    2438 | North Las Vegas |
|    2352 | Markham         |
|    2029 | Champaign       |
|    1849 | Stuttgart       |
|    1520 | Surprise        |
|    1465 | Lakewood        |
|    1155 | Goodyear        |
+---------+-----------------+


6. Find the distribution of star ratings to the business in the following cities:

i. Avon

SQL code used to arrive at answer:

select stars , city
from business
where city = "Avon";


Copy and Paste the Resulting Table Below (2 columns – star rating and count):

+-------+------+
| stars | city |
+-------+------+
|   2.5 | Avon |
|   4.0 | Avon |
|   5.0 | Avon |
|   3.5 | Avon |
|   1.5 | Avon |
|   3.5 | Avon |
|   4.5 | Avon |
|   3.5 | Avon |
|   2.5 | Avon |
|   4.0 | Avon |
+-------+------+

ii. Beachwood

SQL code used to arrive at answer:

select stars , city
from business
where city = "Beachwood";

Copy and Paste the Resulting Table Below (2 columns – star rating and count):

+-------+-----------+
| stars | city      |
+-------+-----------+
|   3.0 | Beachwood |
|   3.0 | Beachwood |
|   4.5 | Beachwood |
|   5.0 | Beachwood |
|   4.0 | Beachwood |
|   4.5 | Beachwood |
|   5.0 | Beachwood |
|   2.0 | Beachwood |
|   3.5 | Beachwood |
|   3.5 | Beachwood |
|   5.0 | Beachwood |
|   2.5 | Beachwood |
|   5.0 | Beachwood |
|   5.0 | Beachwood |
+-------+-----------+


7. Find the top 3 users based on their total number of reviews:
		
	SQL code used to arrive at answer:

select id , name , review_count 
from user
order by review_count desc
limit 3
	
		
	Copy and Paste the Result Below:

+------------------------+--------+--------------+
| id                     | name   | review_count |
+------------------------+--------+--------------+
| -G7Zkl1wIWBBmD0KRy_sCw | Gerald |         2000 |
| -3s52C4zL_DHRK0ULG6qtg | Sara   |         1629 |
| -8lbUNlXVSoXqaRRiHiSNg | Yuri   |         1339 |
+------------------------+--------+--------------+




8. Does posing more reviews correlate with more fans?
 
Yes, posing more reviews correlate with more fans but also with the time from when they were yelping since

	Please explain your findings and interpretation of the results:
	
select name , review_count , fans , yelping_since
from user
order by fans desc

+-----------+--------------+------+---------------------+
| name      | review_count | fans | yelping_since       |
+-----------+--------------+------+---------------------+
| Amy       |          609 |  503 | 2007-07-19 00:00:00 |
| Mimi      |          968 |  497 | 2011-03-30 00:00:00 |
| Harald    |         1153 |  311 | 2012-11-27 00:00:00 |
| Gerald    |         2000 |  253 | 2012-12-16 00:00:00 |
| Christine |          930 |  173 | 2009-07-08 00:00:00 |
| Lisa      |          813 |  159 | 2009-10-05 00:00:00 |
| Cat       |          377 |  133 | 2009-02-05 00:00:00 |
| William   |         1215 |  126 | 2015-02-19 00:00:00 |
| Fran      |          862 |  124 | 2012-04-05 00:00:00 |
| Lissa     |          834 |  120 | 2007-08-14 00:00:00 |
| Mark      |          861 |  115 | 2009-05-31 00:00:00 |
| Tiffany   |          408 |  111 | 2008-10-28 00:00:00 |
| bernice   |          255 |  105 | 2007-08-29 00:00:00 |
| Roanna    |         1039 |  104 | 2006-03-28 00:00:00 |
| Angela    |          694 |  101 | 2010-10-01 00:00:00 |
| .Hon      |         1246 |  101 | 2006-07-19 00:00:00 |
| Ben       |          307 |   96 | 2007-03-10 00:00:00 |
| Linda     |          584 |   89 | 2005-08-07 00:00:00 |
| Christina |          842 |   85 | 2012-10-08 00:00:00 |
| Jessica   |          220 |   84 | 2009-01-12 00:00:00 |
| Greg      |          408 |   81 | 2008-02-16 00:00:00 |
| Nieves    |          178 |   80 | 2013-07-08 00:00:00 |
| Sui       |          754 |   78 | 2009-09-07 00:00:00 |
| Yuri      |         1339 |   76 | 2008-01-03 00:00:00 |
| Nicole    |          161 |   73 | 2009-04-30 00:00:00 |
+-----------+--------------+------+---------------------+



9. Are there more reviews with the word "love" or with the word "hate" in them?

	Answer:
                  
                 1780 with 'love' and 232 with 'hate'.
	
	SQL code used to arrive at answer:
		
select COUNT(*)
from review
where text like "%love%";                                                  select COUNT(*)
				                       from review
					      where text like "%hate%";


10. Find the top 10 users with the most fans:

	SQL code used to arrive at answer:

select name , fans 
from User
order by fans desc
limit 10
	
	Copy and Paste the Result Below:
+-----------+------+
| name      | fans |
+-----------+------+
| Amy       |  503 |
| Mimi      |  497 |
| Harald    |  311 |
| Gerald    |  253 |
| Christine |  173 |
| Lisa      |  159 |
| Cat       |  133 |
| William   |  126 |
| Fran      |  124 |
| Lissa     |  120 |
+-----------+------+



		

Part 2: Inferences and Analysis

1. Pick one city and category of your choice and group the businesses in that city or category by their overall star rating. 
Compare the businesses with 2-3 stars to the businesses with 4-5 stars and answer the following questions. Include your code.

I choose the city Toronto and category as Food
	
i. Do the two groups you chose to analyze have a different distribution of hours?
 
Yes

ii. Do the two groups you chose to analyze have a different number of reviews?

Yes

iii. Are you able to infer anything from the location data provided between these two groups? Explain.

No they have different locations.

SQL code used for analysis:

select business.name 
, business.city 
, business.postal_code 
, hours.hours 
, category.category 
, business.stars
, business.review_count

from business,category,hours
where business.id=hours.business_id 
and business.id=category.business_id 
and business.city='Toronto' and category.category = 'Food'
group by business.stars ;



2. Group business based on the ones that are open and the ones that are closed. What differences can you find between the ones that are still open and the ones that are closed? List at least two differences and the SQL code you used to arrive at your answer.
		
i. Difference 1:

The business that are still open have higher ratings
         
         
ii. Difference 2:
         
The business that are still open have more reviews.       
         
SQL code used for analysis:

select business.name 
, business.city 
, business.postal_code 
, hours.hours 
, category.category 
, business.is_open
, business.review_count

from business,category,hours
where business.id=hours.business_id 
and business.id=category.business_id 

group by business.is_open ;


3.For this last part of your analysis, you are going to choose the type of analysis you want to conduct on the Yelp dataset and are going to prepare the data for analysis.

Ideas for analysis include: Parsing out keywords and business attributes for sentiment analysis, clustering businesses to find commonalities or anomalies between them, predicting the overall star rating for a business, predicting the number of fans a user will have, and so on. These are just a few examples to get you started, so feel free to be creative and come up with your own problem you want to solve. Provide answers, in-line, to all of the following:
	
i. Indicate the type of analysis you chose to do:

Here I chose to study the preference among differenttypes of food on yelp. 

ii. Write 1-2 brief paragraphs on the type of data you will need for your analysis and why you chose that data:

I will pick several types of food including
“Chinese”,”Mexican”,”Korean”,”French”,”Italian”,”Japanese” and
“Indian”. Then I will analyze their star ratings and number of
reviews so that I can get some insights on which type of food is
popular on yelp.
                           
                  
iii. Output of your finished dataset:

+----------+----------------------+---------------+---------------------+-----------+
| category | Number_Of_Resturants |  AVG(b.stars) | AVG(b.review_count) | city      |
+----------+----------------------+---------------+---------------------+-----------+
| Korean   |                    7 |           4.5 |                 8.0 | Toronto   |
| French   |                   12 |           4.0 |       135.083333333 | Las Vegas |
| Chinese  |                   13 | 3.76923076923 |       423.230769231 | Las Vegas |
| Mexican  |                   28 |         3.625 |                73.0 | Edinburgh |
| Italian  |                   13 | 3.53846153846 |       78.2307692308 | Montréal  |
| Japanese |                   20 |         3.475 |               22.85 | Toronto   |
+----------+----------------------+---------------+---------------------+-----------+
         
         
iv. Provide the SQL code you used to create your final dataset:

	
SELECT c.category,COUNT(b.name) AS
Number_Of_Resturants,AVG(b.stars),AVG(b.review_count),b.city
FROM (business b INNER JOIN hours h ON b.id = h.business_id)
INNER JOIN category c ON c.business_id = b.id
WHERE c.category IN
("Chinese","Mexican","French","Italian","Korean","Japanese","Ind
ian")
GROUP BY c.category
ORDER BY AVG(stars) DESC