Data Scientist Role Play: Profiling and Analyzing the DatasetWorksheet

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

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 (with id PK )

ii. Hours =1562 (with business_id FK )

iii. Category =2643 (with business_id FK )

iv. Attribute = 1115 (with business_id FK )

v. Review = 8090 (with business_id FK ) , 9581 ( with user_id FK)

vi. Checkin = 493 (with business_id FK )

vii. Photo =6493 (with business_id FK )

viii. Tip = 3979 (with business_id FK ) , 537 (with user_id FK)

ix. User = 10000 ( with id PK)

x. Friend = 11 ( with user_id) , 9415 ( with friend_id)

xi. Elite_years =2780 ( with 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:

select *

from user

where review_count is null

or name 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.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

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 as [Star Rating], count(stars) as [Count]

from business b

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|

+-------------+-------+

ii. Beachwood

SQL code used to arrive at answer:

select stars as [Star Rating], count(stars) as [Count]

from business b

where city = 'Beachwood'

group by stars

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

+-------------+-------+

| Star Rating | Count |

+-------------+-------+

| 2.0 | 1|

| 2.5 | 1|

| 3.0 | 2|

| 3.5 | 2|

| 4.0 | 1|

| 4.5 | 2|

| 5.0 | 5|

+-------------+-------+
7. Find the top 3 users based on their total number of reviews:

SQL code used to arrive at answer:

select name, review_count

from user

order by review_count desc

limit 3

Copy and Paste the Result Below:

+--------+--------------+

| name | review_count |

+--------+--------------+

| Gerald | 2000 |

| Sara | 1629 |

| Yuri | 1339 |

+--------+--------------+

8. Does posing more reviews correlate with more fans? No

Please explain your findings and interpretation of the results: As in result, even there are more
review count, for example for Gerald, the fans are only 253.

select name, review_count, fans

from user

order by fans desc

limit 10
+-----------+--------------+------+

| 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: According to the query result, there are more reviews with the word "love".

SQL code used to arrive at answer:

select (select count(text)

from review

where text like "%love%") as love_text,

(select count(text)

from review

where text like "%hate%") as hate_text

+-----------+-----------+
| love_text | hate_text |

+-----------+-----------+

| 1780 | 232 |

+-----------+-----------+

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.

Phoenix , Home Services

select business.city, stars, business.review_count,hours, category.* from category, business, hours

where business.id=category.business_id

and business.id=hours.business_id

and business.city='Phoenix' and category='Home Services'

+---------+-------+--------------+-----------------------+------------------------+---------------+

| city | stars | review_count | hours | business_id | category |

+---------+-------+--------------+-----------------------+------------------------+---------------+

| Phoenix | 4.0 | 13 | Friday|8:00-16:00 | -hjbcaxaU9yYXY2iI-49sw | Home Services |

| Phoenix | 4.0 | 13 | Tuesday|8:00-16:00 | -hjbcaxaU9yYXY2iI-49sw | Home Services |

| Phoenix | 4.0 | 13 | Thursday|8:00-16:00 | -hjbcaxaU9yYXY2iI-49sw | Home Services |

| Phoenix | 4.0 | 13 | Wednesday|8:00-16:00 | -hjbcaxaU9yYXY2iI-49sw | Home Services |

| Phoenix | 4.0 | 13 | Monday|8:00-16:00 | -hjbcaxaU9yYXY2iI-49sw | Home Services |

| Phoenix | 3.0 | 5 | Monday|10:00-18:00 | 2mMty4iqYjFFm23DvwBUWw | Home Services |

| Phoenix | 3.0 | 5 | Tuesday|10:00-18:00 | 2mMty4iqYjFFm23DvwBUWw | Home Services |

| Phoenix | 3.0 | 5 | Friday|10:00-18:00 | 2mMty4iqYjFFm23DvwBUWw | Home Services |

| Phoenix | 3.0 | 5 | Wednesday|10:00-18:00 | 2mMty4iqYjFFm23DvwBUWw | Home Services |

| Phoenix | 3.0 | 5 | Thursday|10:00-18:00 | 2mMty4iqYjFFm23DvwBUWw | Home Services |

| Phoenix | 3.0 | 5 | Sunday|10:00-18:00 | 2mMty4iqYjFFm23DvwBUWw | Home Services |

| Phoenix | 3.0 | 5 | Saturday|10:00-18:00 | 2mMty4iqYjFFm23DvwBUWw | Home Services |

+---------+-------+--------------+-----------------------+------------------------+---------------+
i. Do the two groups you chose to analyze have a different distribution of hours? Yes

select business.city,hours, category.* from category, business, hours

where business.id=category.business_id

and business.id=hours.business_id

and business.city='Phoenix' and category='Home Services'

+---------+-----------------------+------------------------+---------------+

| city | hours | business_id | category |

+---------+-----------------------+------------------------+---------------+

| Phoenix | Friday|8:00-16:00 | -hjbcaxaU9yYXY2iI-49sw | Home Services |

| Phoenix | Tuesday|8:00-16:00 | -hjbcaxaU9yYXY2iI-49sw | Home Services |

| Phoenix | Thursday|8:00-16:00 | -hjbcaxaU9yYXY2iI-49sw | Home Services |

| Phoenix | Wednesday|8:00-16:00 | -hjbcaxaU9yYXY2iI-49sw | Home Services |

| Phoenix | Monday|8:00-16:00 | -hjbcaxaU9yYXY2iI-49sw | Home Services |

| Phoenix | Monday|10:00-18:00 | 2mMty4iqYjFFm23DvwBUWw | Home Services |

| Phoenix | Tuesday|10:00-18:00 | 2mMty4iqYjFFm23DvwBUWw | Home Services |

| Phoenix | Friday|10:00-18:00 | 2mMty4iqYjFFm23DvwBUWw | Home Services |

| Phoenix | Wednesday|10:00-18:00 | 2mMty4iqYjFFm23DvwBUWw | Home Services |

| Phoenix | Thursday|10:00-18:00 | 2mMty4iqYjFFm23DvwBUWw | Home Services |

| Phoenix | Sunday|10:00-18:00 | 2mMty4iqYjFFm23DvwBUWw | Home Services |

| Phoenix | Saturday|10:00-18:00 | 2mMty4iqYjFFm23DvwBUWw | Home Services |

+---------+-----------------------+------------------------+---------------+

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.

SQL code used for analysis:

where business.id=category.business_id

and business.id=hours.business_id

and business.city='Phoenix' and category='Home Services'

+---------+-------+--------------+-----------------------+------------------------+---------------+

| city | stars | review_count | hours | business_id | category |

+---------+-------+--------------+-----------------------+------------------------+---------------+

| Phoenix | 4.0 | 13 | Friday|8:00-16:00 | -hjbcaxaU9yYXY2iI-49sw | Home Services |

| Phoenix | 4.0 | 13 | Tuesday|8:00-16:00 | -hjbcaxaU9yYXY2iI-49sw | Home Services |

| Phoenix | 4.0 | 13 | Thursday|8:00-16:00 | -hjbcaxaU9yYXY2iI-49sw | Home Services |

| Phoenix | 4.0 | 13 | Wednesday|8:00-16:00 | -hjbcaxaU9yYXY2iI-49sw | Home Services |

| Phoenix | 4.0 | 13 | Monday|8:00-16:00 | -hjbcaxaU9yYXY2iI-49sw | Home Services |

| Phoenix | 3.0 | 5 | Monday|10:00-18:00 | 2mMty4iqYjFFm23DvwBUWw | Home Services |

| Phoenix | 3.0 | 5 | Tuesday|10:00-18:00 | 2mMty4iqYjFFm23DvwBUWw | Home Services |

| Phoenix | 3.0 | 5 | Friday|10:00-18:00 | 2mMty4iqYjFFm23DvwBUWw | Home Services |

| Phoenix | 3.0 | 5 | Wednesday|10:00-18:00 | 2mMty4iqYjFFm23DvwBUWw | Home Services |

| Phoenix | 3.0 | 5 | Thursday|10:00-18:00 | 2mMty4iqYjFFm23DvwBUWw | Home Services |

| Phoenix | 3.0 | 5 | Sunday|10:00-18:00 | 2mMty4iqYjFFm23DvwBUWw | Home Services |

| Phoenix | 3.0 | 5 | Saturday|10:00-18:00 | 2mMty4iqYjFFm23DvwBUWw | Home Services |

+---------+-------+--------------+-----------------------+------------------------+---------------+

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:

Monday --> weekday , higher star, more review

Saturday --> weekend, lower star, less review

SQL code used for analysis:

select business.city, stars, business.review_count,hours, category.* from category, business, hours

where business.id=category.business_id

and business.id=hours.business_id

and business.city='Phoenix' and category='Home Services'

group by business.id

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:

Quantitive analysis

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

The most important fact is the number of review and the rating.

iii. Output of your finished dataset: