<jupyter_start><jupyter_text>Loading Yelp Businees `attributes`<jupyter_code>data_key = 'yelp_academic_dataset_business.json'
data_location = 's3://{}/{}/{}'.format(bucket, folder, data_key)
business = dd.read_json(data_location, lines=True)
business.head(5)<jupyter_output><empty_output><jupyter_text>In order to use dask1. Activate Conda Python 3 `source activate python3`2. Install dask and s3fs `conda install dask s3fs -c conda-forge -y`  Loading Yelp Businees `reviews` ~ 6GB data<jupyter_code>#uncomment below to spin reviews which will be too large. Suggested we create a subset on RDS/postgres and then spin only what's necessary
data_key = 'yelp_academic_dataset_review.json'
data_location = 's3://{}/{}/{}'.format(bucket, folder, data_key)
reviews = dd.read_json(data_location, blocksize=32e6)
reviews.head()
#need to join the three datasets together. Dask join is just like dask
#once joined, filter with restaurants
#once filtered, only have restaurants in database, export as .csv
#once have .csv file load to S3 bucket
#uncomment below to spin reviews which will be too large. Suggested we create a subset on RDS/postgres and then spin only what's necessary
data_key = 'yelp_academic_dataset_user.json'
data_location = 's3://{}/{}/{}'.format(bucket, folder, data_key)
users = dd.read_json(data_location, blocksize=32e6)
users.head()
#business_reviews = business.merge(reviews, on = 'business_id', how = 'inner')
#business_reviews.head()
#business_reviews_users = business_reviews.merge(users, on = 'user_id', how = 'inner')
#business_reviews_users.head()
#method to pull unique values out of column 'category' in business
# def unique_col(col):
#     return ','.join(set(col.split(',')))

# x = business.categories.apply(unique_col)
#drop na values
business2 = business.dropna(subset=['categories'])
restaurants = business2[business2['categories'].str.contains('Restaurants')]
restaurants.head()
#stars appear as a column in the first two datasets, to idenity, renaming
restaurants['stars_business'] = restaurants['stars']
restaurants2 = restaurants.drop(['stars'], axis=1)
#doing same for reviews:
reviews['stars_reviews'] = reviews['stars']
reviews2 = reviews.drop(['stars'], axis=1)
restaurants2_reviews2 = restaurants2.merge(reviews2, on = 'business_id', how = 'inner')
restaurants2.columns
reviews.columns
restaurants2_reviews2.columns
restaurants2_reviews2.head()
users.columns
restaurants2_reviews2.columns
restaurants2_reviews2_users = restaurants2_reviews2.merge(users, on = 'user_id', how = 'inner')
restaurants2_reviews2_users.columns
#doing same for reviews:
restaurants2_reviews2['name_reviews'] = restaurants2_reviews2['name']
restaurants2_reviews2['review_count_reviews'] = restaurants2_reviews2['review_count']
restaurants2_reviews2['cool_reviews'] = restaurants2_reviews2['cool']
restaurants2_reviews2['funny_reviews'] = restaurants2_reviews2['funny']
restaurants2_reviews2['useful_reviews'] = restaurants2_reviews2['useful']
restaurants3_reviews3 = restaurants2_reviews2.drop(['name', 'review_count', 'cool', 'funny', 'useful'], axis=1)
#now do this again to users:
#doing same for reviews:
users['name_users'] = users['name']
users['review_count_users'] = users['review_count']
users['cool_users'] = users['cool']
users['funny_users'] = users['funny']
users['useful_users'] = users['useful']
users2 = users.drop(['name', 'review_count', 'cool', 'funny', 'useful'], axis=1)
#make sure to include: address, hours, postal code, and date
#restaurants2_reviews_users.head()
restaurants3_reviews3_users2 = restaurants3_reviews3.merge(users2, on = 'user_id', how = 'inner')
#check that everything worked:
restaurants3_reviews3_users2.columns
final_combined = restaurants3_reviews3_users2.drop(['attributes', 'hours'], axis=1)
#reducing file size further by cropping out additional columns for parquet to pass, as issues with RAM cause kernel to restart
#restaurants_reviews_users2 = restaurants_reviews_users.drop(['address', 'attributes', 'hours', 'is_open', 'postal_code', 'date', 
#                                                              'friends', 'yelping_since','compliment_cool', 'compliment_cute', 'compliment_hot', 
#                                                              'compliment_list', 'compliment_more', 'compliment_note', 'compliment_photos', 
#                                                              'compliment_plain', 'compliment_profile'], axis=1)
final_combined.head()
# 'compliment_cool', 'compliment_cute', 'compliment_hot', 'compliment_list', 'compliment_more', 'compliment_note', 'compliment_photos', 'compliment_plain', 'compliment_profile', 'compliment_write',
#add the following code to run parquet, in terminal:
#conda install -c conda-forge fastparquet
final_combined.to_parquet('s3://tally-ai-dspt3/yelp-kaggle-raw-data/final_combined.parquet.gzip', compression='gzip') 
             
#dd.to_csv(restaurants_reviews_users,'s3://tally-ai-dspt3/yelp-kaggle-raw-data/restaurants_reviews_users.csv')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12594962015193922, 'fraction_numerical': 0.017792882846861254, 'mean_word_length': 5.948611111111111, 'pattern_counts': {'":': 0, '<': 9, '<?xml version=': 0, '>': 9, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 6, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '507545', 'license': 'MIT', 'n_tokens_mistral': 1678, 'n_tokens_neox': 1610, 'n_words': 368, 'path': 'notebooks/Ofer-dask-joint-tables-and-filter-by-restaurants.ipynb', 'repo_name': 'BloomTech-Labs/tally-ai-ds', 'size': '5002'}