YelpDataAnalysis.txt

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
ii. Hours = 1562 (Use foreign key: business_id)
iii. Category = 2643 (Use foreign key: business_id)
iv. Attribute = 1115 (Use foreign key: business_id)
v. Review = 10000
vi. Checkin = 493
vii. Photo = 10000
viii. Tip = 3979 (Use foreign key: business_id)
ix. User = 10000
x. Friend = 11 (Use foreign key: user_id)
xi. Elite_years = 2780 (Use foreign key: user_id)

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



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:
	-- I manually checked all the columns using the following code
	SELECT *
	FROM user
	WHERE 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.0	max: 5.0	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 city, COUNT(review_count) as city_count
	FROM business
	GROUP BY city
	ORDER BY city_count DESC;
	
	Copy and Paste the Result Below:
	+-----------------+------------+
	| city            | city_count |
	+-----------------+------------+
	| Las Vegas       |       1561 |
	| Phoenix         |       1001 |
	| Toronto         |        985 |
	| Scottsdale      |        497 |
	| Charlotte       |        468 |
	| Pittsburgh      |        353 |
	| Montréal        |        337 |
	| Mesa            |        304 |
	| Henderson       |        274 |
	| Tempe           |        261 |
	| Edinburgh       |        239 |
	| Chandler        |        232 |
	| Cleveland       |        189 |
	| Gilbert         |        188 |
	| Glendale        |        188 |
	| Madison         |        176 |
	| Mississauga     |        150 |
	| Stuttgart       |        141 |
	| Peoria          |        105 |
	| Markham         |         80 |
	| Champaign       |         71 |
	| North Las Vegas |         70 |
	| North York      |         64 |
	| Surprise        |         60 |
	| Richmond Hill   |         54 |
	+-----------------+------------+
	(Output limit exceeded, 25 of 362 total rows shown)

	
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, review_count
FROM business
WHERE city = 'Avon';

Copy and Paste the Resulting Table Below (2 columns – star rating and count):
+-------+--------------+
| stars | review_count |
+-------+--------------+
|   2.5 |            3 |
|   4.0 |            4 |
|   5.0 |            3 |
|   3.5 |            7 |
|   1.5 |           10 |
|   3.5 |           31 |
|   4.5 |           31 |
|   3.5 |           50 |
|   2.5 |            3 |
|   4.0 |           17 |
+-------+--------------+

ii. Beachwood

SQL code used to arrive at answer:
SELECT stars, review_count
FROM business
WHERE city = 'Beachwood';

Copy and Paste the Resulting Table Below (2 columns – star rating and count):
+-------+--------------+
| stars | review_count |
+-------+--------------+
|   3.0 |            8 |
|   3.0 |            3 |
|   4.5 |           14 |
|   5.0 |            6 |
|   4.0 |           69 |
|   4.5 |            3 |
|   5.0 |            4 |
|   2.0 |            8 |
|   3.5 |            3 |
|   3.5 |            3 |
|   5.0 |            6 |
|   2.5 |            3 |
|   5.0 |            3 |
|   5.0 |            4 |
+-------+--------------+


7. Find the top 3 users based on their total number of reviews:
		
	SQL code used to arrive at answer:
	SELECT name, SUM(review_count) as count_total
	FROM user
	GROUP BY name
	ORDER BY count_total DESC
	LIMIT 3;
		
	Copy and Paste the Result Below:
	+--------+-------------+
	| name   | count_total |
	+--------+-------------+
	| Nicole |        2397 |
	| Sara   |        2253 |
	| Gerald |        2034 |
	+--------+-------------+


8. Does posing more reviews correlate with more fans?

	Please explain your findings and interpretation of the results:
	
	Yes. I looked over the review_count and fans information in the User table, and found out that in general the more review, the more fans. To support this observation, I conducted two SQL queries:

	1st: View some example data. It is easy to find that bigger number of fans tends to correlates with bigger number of review counts.

	Query:
	SELECT name, review_count, fans
	FROM user;

	Result:
	+----------+--------------+------+
	| name     | review_count | fans |
	+----------+--------------+------+
	| Monera   |          245 |   15 |
	| Joe      |            2 |    0 |
	| Jeb      |           57 |    0 |
	| Jed      |            8 |    0 |
	| Rae      |            2 |    0 |
	| Carolyn  |           43 |    1 |
	| Talia    |           26 |    2 |
	| Ryan     |            2 |    0 |
	| Joe      |            1 |    0 |
	| Scott    |            7 |    0 |
	| John     |            3 |    0 |
	| Ron      |            9 |    0 |
	| Bryan    |            5 |    0 |
	| Patti    |            2 |    0 |
	| Gary     |           23 |    0 |
	| Kristin  |           28 |    0 |
	| Harald   |         1153 |  311 |
	| Cynthia  |            4 |    0 |
	| Benjamin |          111 |    2 |
	| Mrme     |            2 |    0 |
	| Kristie  |          213 |   10 |
	| Tamaki   |          239 |   23 |
	| Austin   |            2 |    0 |
	| Kiristen |          400 |   23 |
	| Mesut    |           25 |    0 |
	+----------+--------------+------+

	2nd: Calculate the Pearson Correlation Coefficient. The detailed explanation can be found at this site: https://chartio.com/learn/postgresql/correlation-coefficient-pearson/. The value I calculated here is actually the square of Pearson's coeffcient. The value is 0.4371, so the Pearson Correlation Coefficient is 0.6612, which is positive, indicating that there is strong positive correlation between the review count and fans.

	Query:
	SELECT name, 
			(count(*) * sum(x * y) - sum(x) * sum(y)) * 
			(count(*) * sum(x * y) - sum(x) * sum(y)) /
			(count(*) * sum(x * x) - sum(x) * sum(x)) /
			(count(*) * sum(y * y) - sum(y) * sum(y)) as "Corr Coef Using Pearson"
	FROM (
	SELECT name, review_count as x, fans as y
	FROM user);

	Result:
	+------+-------------------------+
	| name | Corr Coef Using Pearson |
	+------+-------------------------+
	| Ryan |          0.437136492915 |
	+------+-------------------------+

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

	Answer: There are more reviews with 'love' than with 'hate'. They are 1780 and 232 respectively.

	
	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 |
	+-----------+------+

	
	
11. Is there a strong relationship (or correlation) between having a high number of fans and being listed as "useful" or "funny?" Out of the top 10 users with the highest number of fans, what percent are also listed as “useful” or “funny”?

Key:
0% - 25% - Low relationship
26% - 75% - Medium relationship
76% - 100% - Strong relationship
	
	SQL code used to arrive at answer:
	SELECT name, review_count, useful, funny, fans
	FROM user
	ORDER BY fans DESC
	LIMIT 10;
	
	Copy and Paste the Result Below:
	+-----------+--------------+--------+--------+------+
	| name      | review_count | useful |  funny | fans |
	+-----------+--------------+--------+--------+------+
	| Amy       |          609 |   3226 |   2554 |  503 |
	| Mimi      |          968 |    257 |    138 |  497 |
	| Harald    |         1153 | 122921 | 122419 |  311 |
	| Gerald    |         2000 |  17524 |   2324 |  253 |
	| Christine |          930 |   4834 |   6646 |  173 |
	| Lisa      |          813 |     48 |     13 |  159 |
	| Cat       |          377 |   1062 |    672 |  133 |
	| William   |         1215 |   9363 |   9361 |  126 |
	| Fran      |          862 |   9851 |   7606 |  124 |
	| Lissa     |          834 |    455 |    150 |  120 |
	+-----------+--------------+--------+--------+------+
	
	Please explain your findings and interpretation of the results: There is a strong relationship between a high number of fans and being listed as 'useful' or 'funny'. I looked over top 10 users with the highest number of 'useful' marks and 'funny' marks, and I found that the number of fans also very high. This means there is a strong positive relationship between the number of fans and being listed as 'useful' or 'funny'.

	The result of top 10 users with the most 'useful' marks:
	+-----------+--------------+--------+--------+------+
	| name      | review_count | useful |  funny | fans |
	+-----------+--------------+--------+--------+------+
	| Harald    |         1153 | 122921 | 122419 |  311 |
	| Gerald    |         2000 |  17524 |   2324 |  253 |
	| Susie     |          272 |  14703 |   3823 |   24 |
	| Fran      |          862 |   9851 |   7606 |  124 |
	| William   |         1215 |   9363 |   9361 |  126 |
	| .Hon      |         1246 |   7850 |   5851 |  101 |
	| W         |          198 |   6974 |   6033 |    4 |
	| Alan      |           80 |   5640 |   4567 |   44 |
	| Christine |          930 |   4834 |   6646 |  173 |
	| Mike      |          346 |   4656 |    301 |   65 |
	+-----------+--------------+--------+--------+------+

	The result of top 10 users with the most 'funny' marks:
	+-----------+--------------+--------+--------+------+
	| name      | review_count | useful |  funny | fans |
	+-----------+--------------+--------+--------+------+
	| Harald    |         1153 | 122921 | 122419 |  311 |
	| William   |         1215 |   9363 |   9361 |  126 |
	| Fran      |          862 |   9851 |   7606 |  124 |
	| Christine |          930 |   4834 |   6646 |  173 |
	| W         |          198 |   6974 |   6033 |    4 |
	| .Hon      |         1246 |   7850 |   5851 |  101 |
	| Alan      |           80 |   5640 |   4567 |   44 |
	| Susie     |          272 |  14703 |   3823 |   24 |
	| Jen       |          211 |   3111 |   3164 |   39 |
	| Jim       |          671 |   3881 |   2913 |   57 |
	+-----------+--------------+--------+--------+------+
	
	

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. Do the two groups you chose to analyze have a different distribution of hours?
It looks that the group with 4-5 stars tends to have shorter time in hours than the group with 2-3 stars. Since the query result are only displayed 25 entries each time, I have to change the sorting order in between 'ASC' and 'DESC' to get a full glance of it.

ii. Do the two groups you chose to analyze have a different number of reviews?
There is no clear correlation between number of reviews and star ratings. The number of reviews from the group with 4-5 stars ranges from 8 to 89, whereas that from the group with 2-3 stars also ranges from 5 to 47. It can hardly be determined that more reviews lead to better rating.
         
iii. Are you able to infer anything from the location data provided between these two groups? Explain.
No. The samples are too limited to get to any conclusions. I only have 6 samples under the query. 

SQL code used for analysis:

SELECT B.name, B.review_count, B.stars, B.address, H.hours,
	   CASE
		  WHEN B.stars BETWEEN 2 AND 3 THEN '2-3 stars'
		  WHEN B.stars BETWEEN 4 AND 5 THEN '4-5 stars'
	   END AS star_rating
FROM business B 
INNER JOIN category C ON B.id = C.business_id
INNER JOIN hours H ON B.id = H.business_id
WHERE (B.city = 'Toronto' AND C.category LIKE 'restaurants')
  AND ((B.stars BETWEEN 2 AND 3) OR (B.stars BETWEEN 4 AND 5))
ORDER BY star_rating ASC;
		
		
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 average number of reviews of closed business (23.20) is less than that of open business (31.76).

ii. Difference 2:
The average number of stars of closed business (3.52) is slightly lower than that of open business (3.68).


SQL code used for analysis:
SELECT is_open, AVG(review_count) as review_average, AVG(stars) as stars_average
FROM business
GROUP BY is_open;

Query result:
+---------+----------------+---------------+
| is_open | review_average | stars_average |
+---------+----------------+---------------+
|       0 |  23.1980263158 | 3.52039473684 |
|       1 |  31.7570754717 | 3.67900943396 |
+---------+----------------+---------------+
	
	
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:
I want to find if there is correlation between the length of open hours and star ratings.
         
ii. Write 1-2 brief paragraphs on the type of data you will need for your analysis and why you chose that data:
In order to carry out this experiment, I need the average star ratings of each business and their corresponding open hours. The open hours are given as a string like 'Monday|12:00-23:00' which requires propossing to retrieve the hours for each day. I used the 'CASE' statement to do the matching and extraction. One thing needs to note is that only a small portion of businesses have the hours attributes with them (117 in total), so this experiment is not exclusive and not extendable to the rest of businesses.
                  
iii. Output of your finished dataset:
+-------------------------------+-----------+--------------+---------------+-----------------+----------------+--------------+----------------+--------------+
| name                          | stars_avg | monday_hours | tuesday_hours | wednesday_hours | thursday_hours | friday_hours | saturday_hours | sunday_hours |
+-------------------------------+-----------+--------------+---------------+-----------------+----------------+--------------+----------------+--------------+
| Flaming Kitchen               |       3.0 | 12:00-23:00  | 12:00-23:00   | 12:00-23:00     | 12:00-23:00    | 12:00-23:00  | 12:00-23:00    | 12:00-23:00  |
| Freeman's Car Stereo          |       3.5 | 9:00-19:00   | 9:00-19:00    | 9:00-19:00      | 9:00-19:00     | 9:00-19:00   | 9:00-17:00     | None         |
| Eklectic Pie - Mesa           |       4.0 | 11:00-21:00  | 11:00-21:00   | 11:00-21:00     | 11:00-21:00    | 11:00-22:00  | 11:00-22:00    | 11:00-21:00  |
| Wooly Wonders                 |       3.5 | 10:00-16:00  | 10:00-19:00   | 10:00-16:00     | 10:00-19:00    | 10:00-16:00  | 10:00-16:00    | None         |
| Motors & More                 |       5.0 | 7:00-17:00   | 7:00-17:00    | 7:00-17:00      | 7:00-17:00     | 7:00-17:00   | 8:00-12:00     | None         |
| West Side Market              |       4.5 | 7:00-16:00   | None          | 7:00-16:00      | None           | 7:00-18:00   | 7:00-18:00     | 10:00-16:00  |
| Baby Cakes                    |       3.5 | None         | 11:00-17:00   | 11:00-17:00     | 11:00-20:00    | 11:00-17:00  | 10:00-17:00    | None         |
| The Wine Mill                 |       4.5 | None         | 15:00-21:00   | 15:00-21:00     | 15:00-21:00    | 15:00-23:00  | 15:00-23:00    | 14:00-18:00  |
| Brubaker's Pub                |       3.0 | 11:00-2:30   | 11:00-2:30    | 11:00-2:30      | 11:00-2:30     | 11:00-2:30   | 11:00-2:30     | 12:00-2:30   |
| Snip-its Rocky River          |       2.5 | 10:00-19:00  | 10:00-19:00   | 10:00-19:00     | 10:00-19:00    | 10:00-19:00  | 9:00-17:30     | 10:00-16:00  |
| Taliesin West                 |       4.5 | 8:30-14:30   | 8:30-17:00    | 8:30-17:00      | 8:30-14:30     | 8:30-20:00   | 8:30-15:00     | 8:30-15:00   |
| Fresh Bonsai Nails & Spa      |       1.5 | 9:30-19:00   | 9:30-19:00    | 9:30-19:00      | 9:30-19:00     | 9:30-19:00   | 9:30-18:00     | 9:30-16:00   |
| Standard Restaurant Supply    |       3.5 | 8:00-18:00   | 8:00-18:00    | 8:00-18:00      | 8:00-18:00     | 8:00-18:00   | 9:00-17:00     | None         |
| What A Bagel                  |       3.0 | 6:00-15:30   | 6:00-15:30    | 6:00-15:30      | 6:00-15:30     | 6:00-15:30   | 6:00-15:30     | None         |
| Pinnacle Fencing Solutions    |       4.0 | 8:00-16:00   | 8:00-16:00    | 8:00-16:00      | 8:00-16:00     | 8:00-16:00   | None           | None         |
| Alterations Express           |       4.0 | 8:00-19:00   | 8:00-19:00    | 8:00-19:00      | 8:00-19:00     | 8:00-19:00   | 8:00-18:00     | None         |
| Extra Space Storage           |       4.0 | 8:00-17:30   | 8:00-17:30    | 8:00-17:30      | 8:00-17:30     | 8:00-17:30   | 8:00-17:30     | 10:00-14:00  |
| Cabin Fever                   |       4.5 | 16:00-2:00   | 18:00-2:00    | 18:00-2:00      | 18:00-2:00     | 18:00-2:00   | 16:00-2:00     | 16:00-2:00   |
| Christian Brothers Automotive |       5.0 | 7:00-18:00   | 7:00-18:00    | 7:00-18:00      | 7:00-18:00     | 7:00-18:00   | None           | None         |
| A & A Traders                 |       3.5 | 9:00-18:00   | 9:00-18:00    | 9:00-18:00      | 9:00-18:00     | 9:00-18:00   | 9:00-18:00     | None         |
| Gussied Up                    |       4.5 | None         | 11:00-19:00   | 11:00-19:00     | 11:00-19:00    | 11:00-19:00  | 11:00-17:00    | 12:00-16:00  |
| Buddy's Muffler & Exhaust     |       5.0 | 8:30-17:00   | 8:30-17:00    | 8:30-17:00      | 8:30-17:00     | 8:30-17:00   | 9:00-15:00     | None         |
| Five Guys                     |       3.5 | 10:00-22:00  | 10:00-22:00   | 10:00-22:00     | 10:00-22:00    | 10:00-22:00  | 10:00-22:00    | 10:00-22:00  |
| Innercity MMA                 |       5.0 | 17:00-22:00  | 18:00-22:00   | 17:00-22:00     | 18:00-22:00    | 17:00-22:00  | None           | None         |
| All Storage - Anthem          |       3.5 | 9:00-16:30   | 9:00-16:30    | 9:00-16:30      | 9:00-16:30     | 9:00-16:30   | 9:00-16:30     | None         |
+-------------------------------+-----------+--------------+---------------+-----------------+----------------+--------------+----------------+--------------+
(Output limit exceeded, 25 of 117 total rows shown)
         
iv. Provide the SQL code you used to create your final dataset:
SELECT b.name, 
      AVG(b.stars) as stars_avg, 
      MAX(CASE
        WHEN h.hours LIKE 'Monday|%' THEN TRIM(h.hours, 'Monday|%')
      END) AS monday_hours,
      MAX(CASE
        WHEN h.hours LIKE 'Tuesday|%' THEN TRIM(h.hours, 'Tuesday|%')
      END) AS tuesday_hours,
      MAX(CASE
        WHEN h.hours LIKE 'Wednesday|%' THEN TRIM(h.hours, 'Wednesday|%')
      END) AS wednesday_hours,
      MAX(CASE
        WHEN h.hours LIKE 'Thursday|%' THEN TRIM(h.hours, 'Thursday|%')
      END) AS thursday_hours,
      MAX(CASE
        WHEN h.hours LIKE 'Friday|%' THEN TRIM(h.hours, 'Friday|%')
      END) AS friday_hours,
      MAX(CASE
        WHEN h.hours LIKE 'Saturday|%' THEN TRIM(h.hours, 'Saturday|%')
      END) AS saturday_hours,
      MAX(CASE
        WHEN h.hours LIKE 'Sunday|%' THEN TRIM(h.hours, 'Sunday|%')
      END) AS sunday_hours
FROM business b
INNER JOIN hours h ON b.id = h.business_id
GROUP BY b.id;