<jupyter_start><jupyter_text><jupyter_code>from google.colab import drive
drive.mount('/content/drive')
!pip install tqdm<jupyter_output>Requirement already satisfied: tqdm in /usr/local/lib/python3.6/dist-packages (4.41.1)<jupyter_text>imports<jupyter_code>#For the system
import os
import progressbar
from tabulate import tabulate

#Manage of time
from datetime import datetime, timedelta
from pytz import timezone
import time
import re

#Manage of files
import pandas as pd
import csv

#scrap
from bs4 import BeautifulSoup
from openpyxl.workbook import Workbook
import requests<jupyter_output><empty_output><jupyter_text>Ubicación<jupyter_code>%cd 'drive/My Drive/Colab Notebooks/4SS/4SS_db/testing/'
!ls<jupyter_output>/content/drive/My Drive/Colab Notebooks/4SS/4SS_db/testing
'Day Update'	'Last 50'	      Masters   Products
'Edition Test'	'links amazon.gdoc'   Origin<jupyter_text>Centered Len<jupyter_code>def centered_len(max_len):
    def centered_string(string):
        if type(string) != 'string':
            string = str(string)
            
        rest = max_len - len(string)
        n_spaces = int(rest/2)

        string_spaces = ' ' * n_spaces
        
        centered_string = string_spaces + string + string_spaces

        if len(centered_string)<max_len:
            centered_string = ' ' + centered_string

        return centered_string
    return centered_string<jupyter_output><empty_output><jupyter_text>Preload DBs<jupyter_code>def preload_dbs(dir):

    '''Recive a directory with a group of dbs and returns an info array
    info[0] = array of DB's names
    info[1] = largest_len_db, for centered'''
    
    databases = os.listdir(dir)
    n_databases = len(databases)

    #initialize
    df = [None]*n_databases
    largest_len_db = 0
    n = 0

    for file_name in databases:
        db_name = f'{file_name}'
        df[n] = db_name
        if len(db_name) > largest_len_db:
            largest_len_db = len(db_name)

        n = n + 1

    info = [df, largest_len_db]
    return info<jupyter_output><empty_output><jupyter_text>Print info Headers<jupyter_code>def print_info_headers(len: int):
    largest_len = info[1]
    centered_db = centered_len(largest_len)
    centered_header = centered_db('masterdbs')
    dashes = '-'*largest_len + '-'

    print(f' country | {centered_header}  | Edition')
    print(f' ------- | {dashes} | ------ ')<jupyter_output><empty_output><jupyter_text>Editor Functions  Count if<jupyter_code>def count_if(array, value):
    count = 0
    for element in array:
        if element == value:
            count += 1
    return count<jupyter_output><empty_output><jupyter_text>Column To Set<jupyter_code>def column_to_set(df, df_column, remove_None=True, sort=False, raw_data=False):
    raw_info = df[df_column].tolist()
    #Remember, this gets de None Value
    array = list(set(raw_info))

    if remove_None == True:
        None_cases = count_if(raw_info, None)
        if None_cases:
            array.remove(None)

    if sort == True:
        array = sorted(array)

    if raw_data == True:
        return array, raw_info
    
    else:
        return array<jupyter_output><empty_output><jupyter_text>Gen Product DB<jupyter_code>def gen_product_db(df):

    #Get arrays
    time_array = df['time'].to_list()
    ranks = df['Rank'].to_list()
    product_names = df['Product Names'].to_list()
    product_IDs = df['Product ID'].to_list()
    images_id = df['Image ID'].to_list()
    stars = df['Stars'].to_list()
    reviews = df['Reviews'].to_list()
    authors = df['Authors/Company'].to_list()
    editions = df['Edition/Console'].to_list()
    prices_1 = df['Price_std_or_min'].to_list()
    max_prices = df['Max_prices'].to_list()

    #From column name main info
    products_names = column_to_set(df, 'Product Names', sort=False)
    n_header = 0
    start = (len(product_names) - 1)
    #Generate de Columns for the main info
    
    products_ID = [None] * len(products_names)
    image_id_rp = [None] * len(products_names)
    image_id_pp = [None] * len(products_names)
    products_description = [None] * len(products_names)
    product_bullets = [None] * len(products_names)
    last_update = [None] * len(products_names)
    last_rank = [None] * len(products_names)
    last_stars_rate = [None] * len(products_names)
    last_n_reviews = [None] * len(products_names)
    last_price = [None] * len(products_names)
    last_max_price = [None] * len(products_names)
    principal_review_title = [None] * len(products_names)
    principal_review_body = [None] * len(products_names)
    principal_review_star = [None] * len(products_names)

    #Check from the bottom 
    for product in range(len(products_names)):
        product_name = products_names[product]
        check = start

        while product_names[check] != product_name:
            check -= 1
            if check < 0:
                print(len(product_names), check, product, product_name)
                break
        
        #General Amazon Scraper Data (from file)
        products_ID[product] = product_IDs[check]
        image_id_rp[product] = images_id[check]
        last_update[product] = time_array[check]
        last_rank[product] = ranks[check]
        last_stars_rate[product] = stars[check]
        last_n_reviews[product] = reviews[check]
        last_price[product] = prices_1[check]
        last_max_price[product] = max_prices[check]

    #info to dictionary
    headers = ['Product Name', 'Product ID',
               'Image ID (ranking page)', 'Image ID (product page)',
               'Product Description', ' Product Bullets', 
               'Last Update', 'Last Rank Position', 'Last Stars Rate','Last n_Reviews', 'Last Price', 'Last Max Price',
               'Principal Review - Title', 'Principal Review - Body', 'Principal Review - Stars']

    info = {headers[0] : products_names,
            headers[1] : products_ID,
            headers[2] : image_id_rp,
            headers[3] : image_id_pp,
            headers[4] : products_description,
            headers[5] : product_bullets,
            headers[6] : last_update,
            headers[7] : last_rank,
            headers[8] : last_stars_rate,
            headers[9] : last_n_reviews,
            headers[10] : last_price,
            headers[11] : last_max_price,
            headers[12] : principal_review_title,
			headers[13] : principal_review_body,
			headers[14] : principal_review_star
            }
    
    
    product_db = pd.DataFrame.from_dict(info)

    return product_db<jupyter_output><empty_output><jupyter_text><jupyter_code># @replace_urls_with_ids
# def edit_function(db_to_edit):
#     return<jupyter_output><empty_output><jupyter_text>Main Edition<jupyter_code>def main_edition(info,original_dir, dir_to_send, country=None):

    centered_db = centered_len(info[1])

    df_names = info[0]
    
    for n in range(len(df_names)):

        if country:
            print(f'   {country}   ', end=' | ')

        #file name extract
        parquet_len = len('.parquet')
        file_name_len = len(df_names[n])-parquet_len
        file_name = df_names[n][0:file_name_len]

        #File name print to log
        print(f' {centered_db(file_name)}', end=' | ')

        #--------Edition--------
        #Load Dataframe
        db_dir = f'{original_dir}/{df_names[n]}'
        db_to_edit = pd.read_parquet(db_dir)
        edited_db = gen_product_db(db_to_edit)
        
        #--------Upadting--------
        #File names
        csv_file = f'{dir_to_send}/csv/{file_name}.csv'
        excel_file = f'{dir_to_send}/excel/{file_name}.xlsx'
        parquet_file = f'{dir_to_send}/parquet/{file_name}.parquet'
        #File creation
        edited_db.to_csv(csv_file,sep="|")
        edited_db.to_excel(excel_file, index=False)
        edited_db.to_parquet(parquet_file, allow_truncated_timestamps=True)

        print('Done')<jupyter_output><empty_output><jupyter_text>Deploy<jupyter_code>secure = True

if secure == True:
    countries = ['mx', 'br']

    for country in countries:
        scrap_dir = f'Masters/Parquet backup/{country}/parquet'
        product_db_dir = f'Products/{country}'
        info = preload_dbs(scrap_dir)
        print_info_headers(info[1])
        main_edition(info, original_dir=scrap_dir, dir_to_send=product_db_dir, country=country)
def main_files_overcheck(info, scraper_dir, products_dir, country=None):

    centered_db = centered_len(info[1])

    df_names = info[0]
    total = 0
    
    for n in range(len(df_names)):

        if country:
            print(f'   {country}   ', end=' | ')

        #file name extract
        parquet_len = len('.parquet')
        file_name_len = len(df_names[n])-parquet_len
        file_name = df_names[n][0:file_name_len]

        #File name print to log
        print(f' {centered_db(file_name)}', end=' | ')

        #--------Edition--------
        #Load Dataframe
        db_check_dir = f'{scraper_dir}/{df_names[n]}'
        db_check = pd.read_parquet(db_check_dir)

        shape = db_check.shape

        rows = shape[0]

        print(shape, end=' | ')

        product_check_dir = f'{products_dir}/{df_names[n]}'
        product_check = pd.read_parquet(product_check_dir)

        prod_shape = product_check.shape

        rows = prod_shape[0]

        total += rows

        print(prod_shape)

    time_estimated = (total * 0.15)/ 60
    text = f"""

    Total Rows = {total}
    Time estimated = {time_estimated} min


    """
    print(text)
df = pd.read_parquet('Products/mx/parquet/mx-master_db_pet-supplies.parquet')
df.shape
def print_info_headers_check(len: int):
    largest_len = info[1]
    centered_db = centered_len(largest_len)
    centered_header = centered_db('masterdbs')
    db_dashes = '-'*largest_len + '-'

    centered_scraper = centered_len(11)
    centered_scrp_hd = centered_scraper('Scraper')
    

    print(f' country | {centered_header}  | {centered_scrp_hd} | Product DB')
    print(f' ------- | {db_dashes} | ----------- |  ')
%pwd
secure = True

if secure == True:
    countries = ['mx', 'br']

    for country in countries:
        scrap_dir = f'Masters/Parquet backup/{country}/parquet'
        product_db_dir = f'Products/{country}/parquet'
        info = preload_dbs(scrap_dir)
        print_info_headers_check(info[1])
        main_files_overcheck(info, scraper_dir=scrap_dir, products_dir=product_db_dir, country=country)<jupyter_output>country |                 masterdbs                 |   Scraper   | Product DB
 ------- | ----------------------------------------- | ----------- |  
   mx    |          mx-master_db_pet-supplies        | (18700, 11) | (204, 15)
   mx    |             mx-master_db_music            | (18700, 11) | (1052, 15)
   mx    |           mx-master_db_automotive         | (19300, 11) | (406, 15)
   mx    |            mx-master_db_kitchen           | (18800, 11) | (280, 15)
   mx    |              mx-master_db_toys            | (18750, 11) | (307, 15)
   mx    |           mx-master_db_videogames         | (18450, 11) | (231, 15)
   mx    |             mx-master_db_books            | (18700, 11) | (252, 15)
   mx    |          mx-master_db_electronics         | (18900, 11) | (199, 15)
   mx    |           mx-master_db_industrial         | (18800, 11) | (301, 15)
   mx    |             mx-master_db_shoes            | (18700, 11) | (357, 15)
   mx    |              mx-master_db_hpc             | (18[...]<jupyter_text>tqdm<jupyter_code>from tqdm import tqdm
for i in tqdm(range(10000)):
    ...
def main_files_tqdmbar_check(info, scraper_dir, products_dir, country=None):

    centered_db = centered_len(info[1])

    df_names = info[0]
    total = 0
    if country:
        print(f'   {country}:   ', end=' | ')
    
    t = tqdm(iterable=len(df_names), total=len(df_names), leave=True,
             disable=True, miniters=True)
    for n in tqdm(range(len(df_names))):

        #file name extract
        parquet_len = len('.parquet')
        file_name_len = len(df_names[n])-parquet_len
        file_name = df_names[n][0:file_name_len]

        #--------Edition--------
        #Load Dataframe
        db_check_dir = f'{scraper_dir}/{df_names[n]}'
        db_check = pd.read_parquet(db_check_dir)

        shape = db_check.shape

        rows = shape[0]


        product_check_dir = f'{products_dir}/{df_names[n]}'
        product_check = pd.read_parquet(product_check_dir)

        prod_shape = product_check.shape

        rows = prod_shape[0]

        total += rows

        t.update()

    t.close()
    time_estimated = (total * 0.15)/ 60
    text = f"""
    Total Rows = {total}
    Time estimated = {time_estimated} min
    """
    print(text)
secure = True

if secure == True:
    countries = ['mx', 'br']

    for country in countries:
        scrap_dir = f'Masters/Parquet backup/{country}/parquet'
        product_db_dir = f'Products/{country}/parquet'
        info = preload_dbs(scrap_dir)
        # print(f'{country}:')
        main_files_tqdmbar_check(info, scraper_dir=scrap_dir, products_dir=product_db_dir, country=country)<jupyter_output>0%|          | 0/22 [00:00<?, ?it/s]<jupyter_text>ProgresBar<jupyter_code>!pip install progressbar2
import progressbar

for country in countries:
    with progressbar.ProgressBar(max_value=100) as bar:
        for i in range(100):
            time.sleep(0.02)
            bar.update(i)
# import progressbar
# from progressbar.widgets


for country in countries:
    widgets=[
        #  '[', progressbar.Variable(), ']',
         '[', progressbar.Timer(), ']',
         '[', progressbar.Percentage(), ']',
         '[',country, ']',
         progressbar.Bar(marker='▥'),
         '[', progressbar.Timer(), ']',
        #  '[', progressbar.widgets.BouncingBar() , ']',

]
    for i in progressbar.progressbar(range(20), widgets=widgets):
        time.sleep(0.1)
def load_to_backend(df, country, df_name):
    #On the file
    product_name = df['Product Names'].to_list()
    date = df['time'].to_list()
    rank = df['Rank'].to_list()
    stars = df['Stars'].to_list()
    reviews = df['Reviews'].to_list()
    price = df['Price_std_or_min'].to_list()
    max_price = df['Max_prices'].to_list()

    #images ID to URL
    image_id = df['Image ID'].to_list()
    image_url = [f'https://images-na.ssl-images-amazon.com/images/I/{img_id}.jpg' for img_id in image_id]

    #Static and quasi-static properties
    store = 'Amazon'

    #Country
    country = countries[0]
    country = country

    #Product ID to URL
    product_id = df['Product ID'].to_list()
    product_url = [f'https://www.amazon.com.{country}/dp/{pid}' for pid in product_id]

    #Constant
    parquet_len = len('.parquet')

    #variables

    #Main Name Extracter
    file_name_len = len(df_name)-parquet_len
    file_name = df_name[13:file_name_len]

    #Dictionary 
    category = file_name
    string_1 = '''mutation {
    releaseProduct (input: {'''

    string_3 = '''}) {
        _id
        name
        stars
    }
    }'''
    
    url = 'http://34.95.224.171/api'

    for n in range(len(product_name)):
        query_string = f'''
        name : "{product_name[n]}",
        imageURL: "{image_url[n]}",
        link: "{product_url[n]}",
        cathegory: "{category}",
        country: "{country}",
        store: "Amazon",
        Time: "{date[n]}",
        Rank: {int(rank[n])},
        Price : {float(price[n])}'''

        query_json = string_1 + query_string + string_3
        

        json = { 'query' : query_json}

        r = requests.post(url=url, json=json)
        print('.', end='')<jupyter_output><empty_output><jupyter_text>Backend Load<jupyter_code>def main_files_bar_check(info, scraper_dir, products_dir, country=None):

    centered_db = centered_len(info[1])

    df_names = info[0]
    total = 0

    widgets=[
    #  '[', progressbar.Variable(), ']',
        '[', progressbar.Timer(), ']',
        '[', progressbar.Percentage(), ']',
        '[',country, ']',
        progressbar.Bar(marker='▥'),
        '[', progressbar.Timer(), ']',
    #  '[', progressbar.widgets.BouncingBar() , ']',

]
    for n in progressbar.progressbar(range(len(df_names)), widgets=widgets):

        #file name extract
        parquet_len = len('.parquet')
        file_name_len = len(df_names[n])-parquet_len
        file_name = df_names[n][0:file_name_len]
        df_name = df_names[n]

        #File name print to log

        #--------Edition--------
        #Load Dataframe
        product_check_dir = f'{scraper_dir}/{df_names[n]}'
        product_check = pd.read_parquet(product_check_dir)
        df = product_check
        load_to_backend(df, country, df_name)

        prod_shape = product_check.shape

        rows = prod_shape[0]

        total += rows

    time_estimated = (total * 0.15)/ 60

    row_info = [country,total, time_estimated]
    
    return row_info<jupyter_output><empty_output><jupyter_text>ProgressBar Deploy<jupyter_code>%cd 'testing'
secure = True

if secure == True:
    countries = ['mx', 'br']
    estimation_info = [None, None]
    for c in range(len(countries)):
        country= countries[c]
        scrap_dir = f'Last 50/{country}/parquet'
        info = preload_dbs(scrap_dir)
        estimation_info[c] = main_files_bar_check(info, scraper_dir=scrap_dir, products_dir=product_db_dir, country=country)

        
    headers = ['country', 'Data To Scrap','Time estimated (min)']
    table = [
             estimation_info[0],
             estimation_info[1]
    ]
    print('\n')
    print(tabulate(table, headers))<jupyter_output>[Elapsed Time: 0:00:00][N/A%][mx]|                     |[Elapsed Time: 0:00:00]
{'fraction_non_alphanumeric': 0.1327011526886605, 'fraction_numerical': 0.015274544318891602, 'mean_word_length': 2.6752921535893157, 'pattern_counts': {'":': 0, '<': 63, '<?xml version=': 0, '>': 61, 'https://': 2, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 1, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '16', 'dir': 'jupyter-structured-clean-dedup', 'id': '554768', 'license': 'MIT', 'n_tokens_mistral': 6208, 'n_tokens_neox': 5925, 'n_words': 1116, 'path': 'GENERATOR_ProductDB.ipynb', 'repo_name': 'Edward-TL/amazon_scraper', 'size': '17611'}