<jupyter_start><jupyter_text>Open data project, ETL We have differnt sources of information:    1- Kaggle play store dataset:https://www.kaggle.com/orgesleka/android-apps 400000 infos for android Apps scraped with Scrapy from Google Play. Those fields are included:         - name        - datePublished        - numDownloadsMin        - fileSize        - packageName        - price        - aggregateRating        - softwareVersion        - ratingCount        - dateCrawled        - url        2- UCSD Amazon product data, review        - reviewerID: ID of the reviewer, e.g. A2SUAM1J3GNN3B        - asin: ID of the product, e.g. 0000013714        - reviewerName: name of the reviewer        - helpful: helpfulness rating of the review, e.g. 2/3        - reviewText: text of the review        - overall: rating of the product        - summary: summary of the review        - unixReviewTime: time of the review (unix time)        - reviewTime: time of the review (raw)    3- UCSD Amazon product data metadata        - asin: ID of the product, e.g. 0000031852        - title: name of the product        - price: price in US dollars (at time of crawl)        - imUrl: url of the product image        - related: related products (also bought, also viewed, bought together, buy after viewing)        - salesRank: sales rank information        - brand: brand name        - categories: list of categories the product belongs to<jupyter_code>import pandas as pd
import glob
import json
import re 


data_sources = glob.glob("data_sources/*")
print("> Your available sources are: %s" % ", ".join(data_sources))<jupyter_output>> Your available sources are: data_sources/meta_Apps_for_Android.json, data_sources/reviews_Apps_for_Android.json, data_sources/apps.csv<jupyter_text>Import data from kaggle data source- Important, it is a must, have a directory named "data_sources" in the root of the folder with apps.csv file.<jupyter_code>all_apps = pd.read_csv("data_sources/apps.csv", error_bad_lines=False, encoding = "ISO-8859-1")<jupyter_output>b'Skipping line 41088: expected 11 fields, saw 12\n'
b'Skipping line 304239: expected 11 fields, saw 12\n'
/Users/Marcelpv96/anaconda3/lib/python3.7/site-packages/IPython/core/interactiveshell.py:3020: DtypeWarning: Columns (2,6,8) have mixed types. Specify dtype option on import or set low_memory=False.
  interactivity=interactivity, compiler=compiler, result=result)<jupyter_text>Import data of amazon reviews and apps- Important, it is a must, have a directory named "data_sources" in the root of the folder with meta_Apps_for_Android.json and reviews_Apps_for_Android.json files.<jupyter_code>metadata_file = open("data_sources/meta_Apps_for_Android.json", "r") 
reviews_file = open("data_sources/reviews_Apps_for_Android.json", "r")


def parse(file):
    for l in file:
        yield eval(l)
    
def get_df(file):
    i = 0
    df = {}
    for d in parse(file):
        df[i] = d
        i += 1
    return pd.DataFrame.from_dict(df, orient='index')

metadata_df = get_df(metadata_file)
reviews_df = get_df(reviews_file)<jupyter_output><empty_output><jupyter_text>Some preprocessing- Delete some stranges app-names.- Which will be app name for each 'asin'.<jupyter_code># Delete apps name that contains , or ; because this in csv format is another column, and this will crash in Neo4j.
# also delete this which its name is only ???? due to them contain extrange characters.
all_apps = all_apps.dropna()
all_apps = all_apps[[False if ',' in app or '?' in app or ';' in app or '"' in app else True for app in  all_apps['name']]]
apps_name = list(all_apps['name'])
# Delete nan from metadata and reviews
metadata_df = metadata_df[['asin', 'related', 'categories']].dropna()
reviews_df = reviews_df.dropna()
# If asin_name.json already exist, load it
import json


with open('asin_name.json', 'r') as f:
    asin_name = json.load(f)
# Generate dictonary of, which asin is each App Name
from web_scraper import Scraper
import time
import urllib
import json


all_asin = list(metadata_df['asin'])
amazon_scraper = Scraper()
for i in range(0,100):    
    asin = all_asin[i]
    if asin not in asin_name:
        print(">(%d/100) Processing asin [%s]" %  (i,asin))
        try:
            app_name = amazon_scraper.main("https://www.amazon.com/dp/%s" % asin)   
            print("> App name is: [%s] " % app_name)
            asin_name[asin] = app_name
        except urllib.error.HTTPError:
            print(">(%d/100) HTTP ERROR" % i)
        time.sleep(10)


with open('asin_name.json', 'w+') as fp:
    json.dump(asin_name, fp)<jupyter_output><empty_output><jupyter_text>Generate Integrated schema- Our aim here is generate 4 tables of data, that will be our Integrated Schema- App table -> [App name, RatingCount, aggregateRating, numDownloadsMin]- App Categories -> [APP name, Category]- Related Table -> [App name , App name, 'relation (this could be: Viewed togeteher, bought together']- Review Table -> [App name, reviewID, reviewerID, reviewText, Rating]<jupyter_code># App table: Dataframe with all Applications using information provided by Kaggle playstore dataset.

def gen_app_table(all_apps):
    app_table = all_apps.copy()
    app_table = app_table[['name', 'price', 'aggregateRating']]
    app_table = app_table.rename(index = str, columns={"name": "app", "numDownloadsMin":"downloadsMin", 
                                          "price": "price", 'aggregateRating': "rating"})
    return app_table
# Categories table: which category for each app.

def gen_categories_table(metadata, asin_name):
    values = []
    asin = list(metadata['asin'])
    categories = list(metadata['categories'])
    for i in range(0, metadata.shape[0]-1):
        for category in categories[i][0]:
            if asin[i] in asin_name:
                values.append((asin_name[asin[i]], category))          
    return pd.DataFrame(values, columns=("app", "category"))
# Related table: which apps are related with which.

def gen_related_table(metadata, asin_name):
    values = []
    asin = list(metadata['asin'])
    related = list(metadata['related'])
    for i in range(0, metadata.shape[0]-1):
        app_asin = asin[i]
        if app_asin in asin_name:
            app = asin_name[app_asin]
            relations = related[i]
            for kind_relation in  relations.keys():
                for app_related in relations[kind_relation]:
                    if app_related in asin_name:
                        values.append((app, asin_name[app_related], kind_relation))                
    return pd.DataFrame(values, columns=("app", "related", "kind"))


# Review table, which review corresponds to each.

def gen_reviews_table(reviews, asin_name):
    values = []
    asin = list(reviews['asin'])
    reviewerID = list(reviews['reviewerID'])
    reviewerName = list(reviews['reviewerName'])
    helpful = list(reviews['helpful'])
    overall = list(reviews['overall'])
    review = list(reviews['summary'])
    for i in range(0, reviews.shape[0]):
        if asin[i] in asin_name:
            values.append((i, asin_name[asin[i]], reviewerID[i], reviewerName[i], review[i], overall[i]))
    return pd.DataFrame(values, columns=("reviewID", "app", "reviewerID", "reviewerName", "review", "overall"))<jupyter_output><empty_output><jupyter_text>Load all information to pandas dataframes<jupyter_code>df_app_table = gen_app_table(all_apps)
df_categories_table = gen_categories_table(metadata_df, asin_name)
df_related_table = gen_related_table(metadata_df, asin_name)
df_reviews_table = gen_reviews_table(reviews_df, asin_name)<jupyter_output><empty_output><jupyter_text>Write final results in diferent '.csv' files<jupyter_code>df_app_table.to_csv("integrated_data/app.csv")
df_categories_table.to_csv("integrated_data/categories.csv")
df_related_table.to_csv("integrated_data/related.csv")
df_reviews_table.to_csv("integrated_data/reviews.csv")<jupyter_output><empty_output><jupyter_text>Write a reduced data set, as a 'demo' of the ETL system   - Only pick this apps which we known the relation Asin -> App Name<jupyter_code>valid_apps = list(asin_name.values())
df_app_table_reduced = df_app_table[[True if app in valid_apps else False for app in list(all_apps['name'])]]
df_app_table_reduced.to_csv("integrated_data/app_reduced.csv")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10794566654645991, 'fraction_numerical': 0.01153984853948792, 'mean_word_length': 3.792626728110599, 'pattern_counts': {'":': 3, '<': 31, '<?xml version=': 0, '>': 41, 'https://': 2, 'lorem ipsum': 0, 'www.': 2, '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': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '609093', 'license': 'MIT', 'n_tokens_mistral': 2714, 'n_tokens_neox': 2606, 'n_words': 747, 'path': 'ETL.ipynb', 'repo_name': 'Marcelpv96/OD-Final-Project', 'size': '8319'}