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 iii. Category = 2643 iv. Attribute = 1115 v. Review = 8090 vi. Checkin = 493 vii. Photo = 6493 viii. Tip = 3973 (business_id) ix. User = 10000 x. Friend = 11 xi. Elite_years = 2780 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: select COUNT(NULL) FROM user 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.0 max: 5.0 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, sum(review_count) from business group by city order by sum(review_count) desc; Copy and Paste the Result Below: +-----------------+-------------------+ | city | sum(review_count) | +-----------------+-------------------+ | Las Vegas | 82854 | | Phoenix | 34503 | | Toronto | 24113 | | Scottsdale | 20614 | | Charlotte | 12523 | | Henderson | 10871 | | Tempe | 10504 | | Pittsburgh | 9798 | | Montréal | 9448 | | Chandler | 8112 | | Mesa | 6875 | | Gilbert | 6380 | | Cleveland | 5593 | | Madison | 5265 | | Glendale | 4406 | | Mississauga | 3814 | | Edinburgh | 2792 | | Peoria | 2624 | | North Las Vegas | 2438 | | Markham | 2352 | | Champaign | 2029 | | Stuttgart | 1849 | | Surprise | 1520 | | Lakewood | 1465 | | Goodyear | 1155 | +-----------------+-------------------+ (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 , count(stars) from business where city = 'Avon' group by stars Copy and Paste the Resulting Table Below (2 columns – star rating and count): ii. Beachwood SQL code used to arrive at answer: select stars as star_rating, count(stars) as count from business where city = 'Avon' group by stars Copy and Paste the Resulting Table Below (2 columns – star rating and count): +-------------+-------+ | star_rating | count | +-------------+-------+ | 1.5 | 1 | | 2.5 | 2 | | 3.5 | 3 | | 4.0 | 2 | | 4.5 | 1 | | 5.0 | 1 | 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 | | -K2Tcgh2EKX6e6HqqIrBIQ | .Hon | 1246 | | -gokwePdbXjfS0iF7NsUGA | eric | 1116 | +------------------------+--------+--------------+ 8. Does posing more reviews correlate with more fans? No Please explain your findings and interpretation of the results: From the table it is evident that review_count and fans does not have any correaltion. SQL code used: select name, review_count, fans from user order by fans desc limit 10 Table: +-----------+--------------+------+ | name | review_count | fans | +-----------+--------------+------+ | Amy | 609 | 503 | | Mimi | 968 | 497 | | Harald | 1153 | 311 | | Gerald | 2000 | 253 | | Christine | 930 | 173 | | Lisa | 813 | 159 | | Cat | 377 | 133 | | William | 1215 | 126 | | Fran | 862 | 124 | | Lissa | 834 | 120 | +-----------+--------------+------+ 9. Are there more reviews with the word "love" or with the word "hate" in them? Answer: Reviews with the word love: 1780 Reviews with the word hate: 232 So the answer is" "YES" SQL code used to arrive at answer: SELECT 'love' Word, COUNT(text) as Word_count FROM review WHERE text LIKE '%love%' UNION SELECT 'hate' Word, COUNT(text) as Word_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. Do the two groups you chose to analyze have a different distribution of hours? Yes, the working hours for 2-3 stars businesses seems longer than businesses who has 4-5 stars. This even seems true for certain 4-5 star businesses which are open only during the weekdays while having the same working hours as 2-3 businesses. ii. Do the two groups you chose to analyze have a different number of reviews? The results are ambigous. One of the 4-5 star group has a lot of reviews but the other 4-5 star group has similar number of reviews as the 2-3 star group. iii. Are you able to infer anything from the location data provided between these two groups? Explain. All the businesses are located at different locations. So nothing can be deducted from the given data. SQL code used for analysis: SELECT B.name, B.review_count, H.hours, postal_code, CASE WHEN hours LIKE "%monday%" THEN 'a' WHEN hours LIKE "%tuesday%" THEN 'b' WHEN hours LIKE "%wednesday%" THEN 'c' WHEN hours LIKE "%thursday%" THEN 'd' WHEN hours LIKE "%friday%" THEN 'e' WHEN hours LIKE "%saturday%" THEN 'f' WHEN hours LIKE "%sunday%" THEN 'g' END AS ord, 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 hours H ON B.id = H.business_id INNER JOIN category C ON C.business_id = B.id WHERE (B.city == 'Las Vegas' AND C.category LIKE 'shopping') AND (B.stars BETWEEN 2 AND 3 OR B.stars BETWEEN 4 AND 5) GROUP BY stars,ord ORDER BY ord,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 ratings(stars) are more for the restaurants which are currently open than the restaurants which are closed. ii. Difference 2: More restaurants are open currently than the ones which have been closed. SQL code used for analysis: SELECT COUNT(DISTINCT(id)), AVG(review_count), SUM(review_count), AVG(stars), is_open FROM business GROUP BY 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: Predicting the number of fans a user will have. ii. Write 1-2 brief paragraphs on the type of data you will need for your analysis and why you chose that data: To better understand the how a yelp user may get a lot of fan followers. Data like what is the elite_years, their choice of words, from when did they start yelping, and the number of reviews can better help us understand the users. For this analysis, Fans will be the dependant variable and Rest of the columns from users and elite_years are independent variables. iii. Output of your finished dataset: +--------------+---------------+---------------+--------+------+-------+----------------+-----------------+--------------------+-----------------+-----------------+-----------------+------------------+-----------------+------------------+-------------------+-------------------+--------------+------+ | review_count | time_on_yelp | average_stars | useful | cool | funny | compliment_hot | compliment_more | compliment_profile | compliment_cute | compliment_list | compliment_note | compliment_plain | compliment_cool | compliment_funny | compliment_writer | compliment_photos | elite_period | fans | +--------------+---------------+---------------+--------+------+-------+----------------+-----------------+--------------------+-----------------+-----------------+-----------------+------------------+-----------------+------------------+-------------------+-------------------+--------------+------+ | 834 | 5187.89655587 | 3.68 | 455 | 342 | 150 | 417 | 35 | 57 | 17 | 21 | 113 | 308 | 482 | 482 | 346 | 24 | 12 | 120 | | 904 | 4460.89655551 | 3.6 | 141 | 85 | 88 | 14 | 3 | 1 | 1 | 1 | 21 | 37 | 13 | 13 | 5 | 11 | 11 | 38 | | 834 | 5187.89655587 | 3.68 | 455 | 342 | 150 | 417 | 35 | 57 | 17 | 21 | 113 | 308 | 482 | 482 | 346 | 24 | 11 | 120 | | 332 | 4206.8965552 | 3.26 | 19 | 13 | 21 | 4 | 3 | 0 | 0 | 0 | 11 | 21 | 22 | 22 | 9 | 3 | 10 | 18 | | 70 | 4268.89655546 | 3.49 | 57 | 11 | 7 | 6 | 3 | 0 | 0 | 1 | 11 | 6 | 8 | 8 | 12 | 0 | 10 | 2 | | 904 | 4460.89655551 | 3.6 | 141 | 85 | 88 | 14 | 3 | 1 | 1 | 1 | 21 | 37 | 13 | 13 | 5 | 11 | 10 | 38 | | 836 | 3915.89655583 | 3.47 | 81 | 52 | 26 | 16 | 5 | 1 | 0 | 0 | 35 | 52 | 36 | 36 | 17 | 1 | 10 | 37 | | 834 | 5187.89655587 | 3.68 | 455 | 342 | 150 | 417 | 35 | 57 | 17 | 21 | 113 | 308 | 482 | 482 | 346 | 24 | 10 | 120 | | 503 | 3933.89655491 | 3.19 | 21 | 23 | 32 | 32 | 3 | 2 | 4 | 1 | 49 | 92 | 67 | 67 | 34 | 6 | 9 | 41 | | 332 | 4206.8965552 | 3.26 | 19 | 13 | 21 | 4 | 3 | 0 | 0 | 0 | 11 | 21 | 22 | 22 | 9 | 3 | 9 | 18 | | 476 | 5494.8965552 | 3.77 | 42 | 0 | 15 | 4 | 7 | 0 | 1 | 0 | 11 | 32 | 16 | 16 | 4 | 2 | 9 | 14 | | 228 | 3413.89655536 | 3.36 | 24 | 1 | 28 | 0 | 1 | 0 | 1 | 0 | 4 | 8 | 2 | 2 | 4 | 0 | 9 | 8 | | 156 | 3832.89655537 | 3.78 | 92 | 32 | 32 | 5 | 4 | 0 | 1 | 0 | 5 | 14 | 14 | 14 | 12 | 1 | 9 | 9 | | 70 | 4268.89655546 | 3.49 | 57 | 11 | 7 | 6 | 3 | 0 | 0 | 1 | 11 | 6 | 8 | 8 | 12 | 0 | 9 | 2 | | 904 | 4460.89655551 | 3.6 | 141 | 85 | 88 | 14 | 3 | 1 | 1 | 1 | 21 | 37 | 13 | 13 | 5 | 11 | 9 | 38 | | 136 | 5726.89655562 | 2.54 | 12 | 1 | 3 | 1 | 4 | 2 | 0 | 0 | 7 | 4 | 4 | 4 | 3 | 0 | 9 | 5 | | 836 | 3915.89655583 | 3.47 | 81 | 52 | 26 | 16 | 5 | 1 | 0 | 0 | 35 | 52 | 36 | 36 | 17 | 1 | 9 | 37 | | 834 | 5187.89655587 | 3.68 | 455 | 342 | 150 | 417 | 35 | 57 | 17 | 21 | 113 | 308 | 482 | 482 | 346 | 24 | 9 | 120 | | 503 | 3933.89655491 | 3.19 | 21 | 23 | 32 | 32 | 3 | 2 | 4 | 1 | 49 | 92 | 67 | 67 | 34 | 6 | 8 | 41 | | 182 | 4072.89655519 | 2.92 | 212 | 2 | 22 | 0 | 1 | 1 | 0 | 0 | 4 | 2 | 3 | 3 | 4 | 0 | 8 | 1 | | 332 | 4206.8965552 | 3.26 | 19 | 13 | 21 | 4 | 3 | 0 | 0 | 0 | 11 | 21 | 22 | 22 | 9 | 3 | 8 | 18 | | 476 | 5494.8965552 | 3.77 | 42 | 0 | 15 | 4 | 7 | 0 | 1 | 0 | 11 | 32 | 16 | 16 | 4 | 2 | 8 | 14 | | 228 | 3413.89655536 | 3.36 | 24 | 1 | 28 | 0 | 1 | 0 | 1 | 0 | 4 | 8 | 2 | 2 | 4 | 0 | 8 | 8 | | 156 | 3832.89655537 | 3.78 | 92 | 32 | 32 | 5 | 4 | 0 | 1 | 0 | 5 | 14 | 14 | 14 | 12 | 1 | 8 | 9 | | 70 | 4268.89655546 | 3.49 | 57 | 11 | 7 | 6 | 3 | 0 | 0 | 1 | 11 | 6 | 8 | 8 | 12 | 0 | 8 | 2 | +--------------+---------------+---------------+--------+------+-------+----------------+-----------------+--------------------+-----------------+-----------------+-----------------+------------------+-----------------+------------------+-------------------+-------------------+--------------+------+ (Output limit exceeded, 25 of 10064 total rows shown) iv. Provide the SQL code you used to create your final dataset: select u.review_count, JULIANDAY('now') - JULIANDAY(u.yelping_since) as time_on_yelp, u.average_stars, u.useful, u.cool, u.funny, u.compliment_hot, u.compliment_more, u.compliment_profile, u.compliment_cute, u.compliment_list, u.compliment_note, u.compliment_plain, u.compliment_cool, u.compliment_funny, u.compliment_writer, u.compliment_photos, DATE('now')-CAST(e.year AS integer) as elite_period, u.fans from user as u LEFT JOIN elite_years as e ON e.user_id = u.id ORDER BY elite_period DESC