<jupyter_start><jupyter_text>Yelp: User and Review Analysis<jupyter_code>!spark-shell --version<jupyter_output>Welcome to
      ____              __
     / __/__  ___ _____/ /__
    _\ \/ _ \/ _ `/ __/  '_/
   /___/ .__/\_,_/_/ /_/\_\   version 3.1.1
      /_/
                        
Using Scala version 2.12.13, OpenJDK 64-Bit Server VM, 1.8.0_292
Branch HEAD
Compiled by user  on 2021-06-23T22:10:53Z
Revision e9c1449899ac58a10ea28ca6f1e8ed6c7c254d54
Url https://bigdataoss-internal.googlesource.com/third_party/apache/spark
Type --help for more information.<jupyter_text>Table of Contents (click to open)1. [User Stickiness](stickiness)1. [User Influence](influence)1. [Bias in Online Reviews](bias)1. [State with most Reviewers](reviewers)1. [Trend of user activity](activity)  Import Libraries<jupyter_code>import seaborn as sns
import folium
import numpy as np
import matplotlib.pyplot as plt

import pyspark.sql.functions as F
from pyspark.sql.functions import col
from pyspark.sql import Window
font_size = 16
params = {"axes.titlesize": font_size,
          "axes.labelsize": font_size*.7,
          "xtick.labelsize": font_size*.5,
          "ytick.labelsize": font_size*.5,
          "legend.fontsize": font_size*.5,
          "figure.figsize": (20, 10),
          "axes.titlepad": 25}

plt.style.use('seaborn-darkgrid')
sns.set_context("notebook", rc=params)<jupyter_output><empty_output><jupyter_text>Load Data Load the review data from google cloud storage to dataproc<jupyter_code>file_review = "gs://proj-spark/yelp-dataset/yelp_academic_dataset_review.json"
file_business = "gs://proj-spark/yelp-dataset/yelp_academic_dataset_business.json"
file_users = "gs://proj-spark/yelp-dataset/yelp_academic_dataset_user.json"
file_checkin = "gs://proj-spark/yelp-dataset/yelp_academic_dataset_checkin.json"
file_tip = "gs://proj-spark/yelp-dataset/yelp_academic_dataset_tip.json"
df_review = spark.read.format('json').load(file_review)
df_review.createOrReplaceTempView('reviewTable')

df_business = spark.read.format('json').load(file_business)
df_business.createOrReplaceTempView('businessTable')

df_users = spark.read.format('json').load(file_users)
df_users.createOrReplaceTempView('usersTable')

df_checkin = spark.read.format('json').load(file_checkin)
df_checkin.createOrReplaceTempView('checkinTable')

df_tip = spark.read.format('json').load(file_tip)
df_tip.createOrReplaceTempView('tipTable')<jupyter_output><empty_output><jupyter_text>User Stickiness  Since how many years have users been using yelp?<jupyter_code>df_stickiness = df_users.withColumn('yelping_since', F.to_date('yelping_since','yyyy-MM-dd HH:mm:ss'))\
    .withColumn('#Years', F.lit(F.year(F.current_date())) - F.year('yelping_since'))\
    .groupBy('#Years')\
    .agg(F.count('user_id').alias("#Users"))\
    .orderBy('#Years')

# percentage of users yelping since #Years
df_stickiness = df_stickiness.withColumn('pert', 100*col('#Users')/F.sum("#Users").over(Window.partitionBy()))
fig, ax = plt.subplots(figsize=(10, 5))
stickiness = df_stickiness.toPandas()
rank = stickiness['#Users'].rank(method='dense').astype(int)-1
palette = np.array(sns.color_palette("Blues_d", len(stickiness)))[rank]

ax = sns.barplot(x='#Years', y='#Users', data=stickiness, ax=ax, palette=palette)

# display percentage 
for idx, row in stickiness.iterrows():
    ax.text(row['#Years'], row['#Users'], '{:.1f}%'.format(row['pert']), color='gray', ha="center")
    
ax.set_title('User Stickiness')<jupyter_output><empty_output><jupyter_text>User Influence<jupyter_code>top = 10

# Top 10 active users
df_active = df_users.orderBy(F.desc('review_count')).limit(top)

# Top 10 social users (based on number of friends)
df_social = df_users.withColumn('no_of_friends', F.size(F.split('friends',', ')))\
    .orderBy(F.desc('no_of_friends'))\
    .limit(top)

# Top 10 popular users (based on number of fans)
df_popular = df_users.orderBy(F.desc('fans')).limit(top)

# Top 10 effective users (based on number of userful reviews)
df_effective = df_users.orderBy(F.desc('useful')).limit(top)<jupyter_output><empty_output><jupyter_text>Plot user influence<jupyter_code># figure and grid for ploting
fig = plt.figure(figsize=(16, 8), constrained_layout=True)
grid = plt.GridSpec(2, 2, wspace=0.4, hspace=0.3, figure=fig)
fig.suptitle('User Influence', fontsize=18)
palette = np.array(sns.color_palette("Blues_d", 10))[::-1]

# Active users
data_active = df_active.toPandas()
ax = plt.subplot(grid[0, 0])
ax = sns.barplot(x='review_count', y='name', data=data_active, ax=ax, palette=palette)
ax.set_title('Top Active')

# Social Users
data_social = df_social.toPandas()
ax = plt.subplot(grid[0, 1])
ax = sns.barplot(x='no_of_friends', y='name', data=data_social, ax=ax, palette=palette)
ax.set_title('Top Social')

# Popular Users
data_popular = df_popular.toPandas()
ax = plt.subplot(grid[1, 0])
ax = sns.barplot(x='fans', y='name', data=data_popular, ax=ax, palette=palette)
ax.set_title('Top Popular')

# Effective Users
data_effective = df_effective.toPandas()
ax = plt.subplot(grid[1, 1])
ax = sns.barplot(x='useful', y='name', data=data_effective, ax=ax, palette=palette)
ax.set_title('Top Effective')<jupyter_output><empty_output><jupyter_text>Bias in Online Reviews  Is it true that people who write a review are those who are more dissatisfied or more satisfied with the service provided by business? Transformation for average stars per business<jupyter_code>df_stars = df_review.groupBy('business_id').agg(F.avg(col('stars')).alias('avg_stars'))<jupyter_output><empty_output><jupyter_text>Join business dataframe with df_stars by business_id.<jupyter_code>joinExpression = df_business["business_id"] == df_stars['business_id']
df_business_star = df_business.join(df_stars, joinExpression, 'left_outer')\
    .drop(df_stars["business_id"])
df_business_star.select('business_id', 'name', 'city', 'stars', 'avg_stars').limit(3).show()<jupyter_output>+--------------------+--------------------+-------+-----+------------------+
|         business_id|                name|   city|stars|         avg_stars|
+--------------------+--------------------+-------+-----+------------------+
|A7BjX9tmAQvnJto9Z...|Briarcliff Dental...|Atlanta|  4.5| 4.421052631578948|
|cyvpFpmpN0YgDykuO...|Pandolfino's Hair...| Natick|  4.5| 4.533333333333333|
|mvEzzcqpiK5w5F80F...|           Starbucks| Boston|  3.0|2.7450980392156863|
+--------------------+--------------------+-------+-----+------------------+<jupyter_text>Plot Histogram for rating deviation between the avg_stars accumulated from written reviews and the actual star rating of a business (ie: the average of stars given by reviewers who wrote an actual review and reviewers who just provided a star rating).<jupyter_code>df_dev = df_business_star\
    .selectExpr("(coalesce(avg_stars, 0) - coalesce(stars, 0))/coalesce(stars, 1) AS deviation")
fig, ax = plt.subplots(figsize=(10, 5))
deviation = df_dev.toPandas()
ax = sns.histplot(deviation, bins=40, ax=ax)
ax.set_title('Histogram of rating deviation') 
print('Mean:{} and Std: {}'.format(deviation.deviation.mean(), deviation.deviation.std()))<jupyter_output>Mean:0.0011443037144630323 and Std: 0.05142371949844016<jupyter_text>Note: Distribution is like a normal distribution and the variance is quite small. Thus, it is unlikely that people who write a review are those who are more dissatisfied or more satisfied with the service provided by businesses. If distribution were positively skewed then reviewers who left a written response were more satisfied than others. If distribution were negatively skewed then they are more dissatisfied than others.  State with most Reviewers<jupyter_code># Number of Reviewers for each business
df_reviewers_cnt = df_review.groupBy('business_id')\
    .agg(F.countDistinct('user_id').alias('#Reviewers'))

# Number of Reivewers for each state
joinExpression = df_business['business_id'] == df_reviewers_cnt['business_id']
df_state_reviewers = df_business.join(df_reviewers_cnt, joinExpression, 'left_outer')\
    .groupBy('state')\
    .agg(F.sum('#Reviewers').alias('#Reviewers'))\
    .orderBy(F.desc('#Reviewers'))<jupyter_output><empty_output><jupyter_text>Plot number of reviewers for top-5 state<jupyter_code>fig, ax = plt.subplots(figsize=(10, 5))
state_data = df_state_reviewers.limit(5).toPandas()
rank = state_data['#Reviewers'].rank(method='dense').astype(int) - 1
palette = np.array(sns.color_palette("Greens_d", len(state_data)))[rank]
ax = sns.barplot(x='state', y='#Reviewers', data=state_data, ax=ax, palette=palette)
ax.set_title('Top-5 State with most Reviewers')<jupyter_output><empty_output><jupyter_text>Trend of user activity<jupyter_code>df_review2 = df_review.withColumn('date', F.to_date('date', 'yyyy-MM-dd HH:mm:ss'))\
    .withColumn('year', F.year('date'))\
    .withColumn('month', F.month('date'))\
    .withColumn('day', F.dayofmonth('date'))\
    .sample(withReplacement=False, fraction=0.0001, seed=2020)
data = df_review2.toPandas()
fig = plt.figure(figsize=(10,5), constrained_layout=True)
grid = plt.GridSpec(2,2, figure=fig)
fig.suptitle('Yelp User Activity over the time', fontsize=18)

ax = plt.subplot(grid[0,0])
ax = sns.histplot(x='day', kde=True, data=data, ax=ax)
ax.set_title('Daily User Activity')

ax = plt.subplot(grid[0,1])
ax = sns.histplot(x='month', kde=True, data=data, ax=ax)
ax.set_title('Monthly User Activity')

ax = plt.subplot(grid[1,:])
ax = sns.histplot(x='year', kde=True, data=data, ax=ax)
ax.set_title('Yearly User Activity')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.16680654637012168, 'fraction_numerical': 0.026332354175409148, 'mean_word_length': 5.154293092317625, 'pattern_counts': {'":': 7, '<': 46, '<?xml version=': 0, '>': 46, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 1, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '12', 'dir': 'jupyter-structured-clean-dedup', 'id': '102421', 'license': 'MIT', 'n_tokens_mistral': 3586, 'n_tokens_neox': 3334, 'n_words': 649, 'path': 'notebooks/user_review_analysis.ipynb', 'repo_name': 'bilalsp/yelp_etl', 'size': '9532'}