Image(filename='GridDB Dataset Screenshot.png',width=800, height=200)

Image(filename='GridDBCloud4.png',width=500, height=200)

Image(filename='GridDBCloud-1-db_user.png',width=500, height=200)

Image(filename='GridDBCloud2.png',width=500, height=200)

Image(filename='GridDBCloud3.png',width=500, height=200)

# URL of the .jsonl.gz file
url = "https://datarepo.eng.ucsd.edu/mcauley_group/data/amazon_2023/raw/review_categories/Subscription_Boxes.jsonl.gz"

# Download the file
response = requests.get(url, stream=True)
if response.status_code == 200:
    # Save the file as a .gz file
    with open("dataset1.jsonl.gz", "wb") as file:
        for chunk in response.iter_content(chunk_size=1024):
            file.write(chunk)
    print("File downloaded and saved as dataset1.jsonl.gz")
else:
    print(f"Failed to download file. Status code: {response.status_code}")

# Read and process the .jsonl.gz file
data = []
with gzip.open("dataset1.jsonl.gz", "rt", encoding="utf-8") as gz_file:
    for line in gz_file:
        # Parse each line as JSON
        data.append(json.loads(line))

subscription_boxes_reviews_df.columns

# URL of the .jsonl.gz file
url = "https://datarepo.eng.ucsd.edu/mcauley_group/data/amazon_2023/raw/meta_categories/meta_Subscription_Boxes.jsonl.gz"

# Download the file
response = requests.get(url, stream=True)
if response.status_code == 200:
    # Save the file as a .gz file
    with open("dataset2.jsonl.gz", "wb") as file:
        for chunk in response.iter_content(chunk_size=1024):
            file.write(chunk)
    print("File downloaded and saved as dataset2.jsonl.gz")
else:
    print(f"Failed to download file. Status code: {response.status_code}")

# Read and process the .jsonl.gz file
metadata = []
with gzip.open("dataset2.jsonl.gz", "rt", encoding="utf-8") as gz_file:
    for line in gz_file:
        # Parse each line as JSON
        metadata.append(json.loads(line))

# Replace empty lists with empty strings or None
subscription_boxes_combined_df = subscription_boxes_combined_df.applymap(
    lambda x: "" if isinstance(x, list) and not x else x
)

# Check for problematic values in DOUBLE columns
double_columns = ["average_rating", "price"]

# Replace invalid or missing values with 0.0
for col in double_columns:
    subscription_boxes_combined_df[col] = pd.to_numeric(subscription_boxes_combined_df[col], errors='coerce').fillna(0.0)

# Create a bar chart
fig = px.bar(
    top_rated_subscription_boxes,
    x='store',
    y='Total_Ratings',
    title='Top Rated Subscription Boxes by Total Ratings',
    text='Total_Ratings',
    labels={'store': 'Store', 'Total_Ratings': 'Total Ratings'}
)

# Customize layout
fig.update_traces(textposition='outside')
fig.update_layout(
    xaxis_title='Store',
    yaxis_title='Total Ratings',
    xaxis_tickangle=-45,
    title_x=0.5
)

# Display the chart
fig.show()

import plotly.graph_objects as go

# Data from the DataFrame
stores = verified_unverified_reviews["store"]
verified_reviews = verified_unverified_reviews["verified_reviews"]
unverified_reviews = verified_unverified_reviews["unverified_reviews"]

# Create the figure
fig = go.Figure()

# Add verified reviews as the first trace with numbers
fig.add_trace(go.Bar(
    y=stores,
    x=verified_reviews,
    name="Verified Reviews",
    orientation="h",  # Horizontal orientation
    marker=dict(color="green"),
    text=verified_reviews,  # Display numbers
    textposition="auto"  # Automatically adjust position for better visibility
))

# Add unverified reviews as the second trace with numbers
fig.add_trace(go.Bar(
    y=stores,
    x=unverified_reviews,
    name="Unverified Reviews",
    orientation="h",  # Horizontal orientation
    marker=dict(color="orange"),
    text=unverified_reviews,  # Display numbers
    textposition="auto"  # Automatically adjust position for better visibility
))

# Update layout for better appearance
fig.update_layout(
    title="Distribution of Verified and Unverified Reviews by Store",
    barmode="stack",  # Stacked bar mode
    xaxis_title="Number of Reviews",
    yaxis_title="Store",
    legend_title="Review Type",
    template="plotly_white",  # Optional: A clean white background
    uniformtext_minsize=8,  # Ensure text is readable
    uniformtext_mode="show",  # Force display of text, even in tight spaces
    xaxis=dict(tickangle=0)  # Ensure x-axis ticks are easy to read
)

# Show the chart
fig.show()

# Define a function to wrap text
def wrap_text(text, width=50):
    return "<br>".join(textwrap.wrap(text, width=width))

# Apply wrapping to the 'text' column
helpful_votes['text_wrapped'] = helpful_votes['text'].apply(lambda x: wrap_text(x, width=40))

# Select specific columns for display
subset_df = helpful_votes[['store', 'text_wrapped', 'helpful_votes']]

# Use Styler to display with gridlines and wrapped text
styled_df = subset_df.style.set_table_styles([
    {'selector': 'table', 'props': [('border-collapse', 'collapse'), ('width', '100%')]},
    {'selector': 'th, td', 'props': [('border', '1px solid black'), ('padding', '8px')]}
])

# Render the DataFrame with gridlines and no index
styled_df = styled_df.hide(axis='index')

# Display the styled DataFrame
styled_df

sql_query4 = (f"""
                SELECT 
                  parent_asin, 
                  store, 
                  COUNT(DISTINCT asin) AS total_variations
                FROM Amazon_subscription_boxes
                GROUP BY 
                    parent_asin,store
                ORDER BY 
                    total_variations DESC
                    LIMIT 10;
                """)

#Setup the URL to be used to invoke the GridDB WebAPI to retrieve data from the container
url = base_url + 'sql'

# Construct the request body
request_body = json.dumps([
    {
        "type": "sql-select",
        "stmt": sql_query4
    }
])

# Validate the constructed request body
#print(request_body)

# Disable debug output after running the query
http.client.HTTPConnection.debuglevel = 0

#Invoke the GridDB WebAPI
data_req4 = requests.post(url, data=request_body, headers=header_obj)

#Process the response received and construct a Pandas dataframe with the data from the response
myJson = data_req4.json()
Product_Variations = pd.DataFrame(myJson[0]["results"], columns=[myJson[0]["columns"][0]["name"], myJson[0]["columns"][1]["name"],myJson[0]["columns"][2]["name"]])
Product_Variations.style.hide(axis="index")

#Process the response received and construct a Pandas dataframe with the data from the response
myJson = data_req5.json()
top_rated_products = pd.DataFrame(myJson[0]["results"], columns=[myJson[0]["columns"][0]["name"], myJson[0]["columns"][1]["name"]])
#top_rated_products.style.hide(axis="index")
#fig = ff.create_table(top_rated_products)
fig = px.bar(top_rated_products, x="store", y="total_ratings", orientation='v',text="total_ratings",title='Top 10 Subscription boxes with the Highest Rating')
fig.update_traces(textposition='inside')
fig.show()

# Load the tokenizer and sentiment-analysis pipeline (with GPU support)
tokenizer = AutoTokenizer.from_pretrained("distilbert-base-uncased-finetuned-sst-2-english")
sentiment_analyzer = pipeline("sentiment-analysis", model="distilbert-base-uncased-finetuned-sst-2-english", device=-1)  # Use CPU

# Define a function to truncate text to 512 tokens and tokenize in batches
def batch_tokenize(text_list, tokenizer, batch_size=64):
    tokenized_batches = []
    for i in range(0, len(text_list), batch_size):
        batch = text_list[i:i + batch_size]
        tokenized_batch = tokenizer(batch, padding=True, truncation=True, max_length=512, return_tensors="pt")
        tokenized_batches.append(tokenized_batch)
    return tokenized_batches

# Truncate and tokenize the 'features' column in batches
tokenized_batches = batch_tokenize(product_features['text'].tolist(), tokenizer)

# Perform sentiment analysis in batches (faster with GPU) with progress bar
results = []
for i, batch in tqdm(enumerate(tokenized_batches), total=len(tokenized_batches), desc="Processing batches"):
    # Perform inference using the model
    outputs = sentiment_analyzer.model(**batch)
    predicted_classes = outputs.logits.argmax(dim=1)  # Get predicted class indices (0 or 1 for the batch)
    sentiments = ['POSITIVE' if cls == 1 else 'NEGATIVE' for cls in predicted_classes]  # Map indices to sentiments
    results.extend(sentiments)  # Append all sentiments for the batch

# Map results back to the DataFrame
product_features['sentiment'] = results

# Summarize sentiments by store
sentiment_summary_df = product_features.groupby('store')['sentiment'].value_counts().unstack(fill_value=0)
sentiment_summary_df = sentiment_summary_df.rename(columns={'POSITIVE': 'positive_reviews', 'NEGATIVE': 'negative_reviews'}).reset_index()

# Show the final sentiment summary
#print(sentiment_summary_df)

# Filter stores with more negative reviews than positive reviews
negative_dominant = sentiment_summary_df[sentiment_summary_df["negative_reviews"] > sentiment_summary_df["positive_reviews"]]
negative_dominant = negative_dominant.fillna(0)  # Ensure no missing values
top_negative_dominant = negative_dominant.sort_values(by="negative_reviews", ascending=False).head(20)

# Filter stores with more positive reviews than negative reviews
positive_dominant = sentiment_summary_df[sentiment_summary_df["positive_reviews"] > sentiment_summary_df["negative_reviews"]]
positive_dominant = positive_dominant.fillna(0)  # Ensure no missing values
top_positive_dominant = positive_dominant.sort_values(by="positive_reviews", ascending=False).head(20)

# Create stacked bar chart for stores with more negative reviews
fig_negative_dominant = go.Figure()

fig_negative_dominant.add_trace(go.Bar(
    x=top_negative_dominant['store'],
    y=top_negative_dominant['negative_reviews'],
    name='Negative Reviews',
    marker_color='crimson'  # Red for negative sentiment
))

fig_negative_dominant.add_trace(go.Bar(
    x=top_negative_dominant['store'],
    y=top_negative_dominant['positive_reviews'],
    name='Positive Reviews',
    marker_color='green'  # Green for positive sentiment
))

fig_negative_dominant.update_layout(
    barmode='stack',
    title='Top 20 Stores with More Negative Reviews than Positive Reviews',
    xaxis_title='Store',
    yaxis_title='Number of Reviews',
    title_font_size=18,
    xaxis_tickangle=-45,
    legend_title_text='Review Type'
)

# Create stacked bar chart for stores with more positive reviews
fig_positive_dominant = go.Figure()

fig_positive_dominant.add_trace(go.Bar(
    x=top_positive_dominant['store'],
    y=top_positive_dominant['positive_reviews'],
    name='Positive Reviews',
    marker_color='green'  # Green for positive sentiment
))

fig_positive_dominant.add_trace(go.Bar(
    x=top_positive_dominant['store'],
    y=top_positive_dominant['negative_reviews'],
    name='Negative Reviews',
    marker_color='crimson'  # Red for negative sentiment
))

fig_positive_dominant.update_layout(
    barmode='stack',
    title='Top 20 Stores with More Positive Reviews than Negative Reviews',
    xaxis_title='Store',
    yaxis_title='Number of Reviews',
    title_font_size=18,
    xaxis_tickangle=-45,
    legend_title_text='Review Type'
)

# Show both charts
fig_negative_dominant.show()

fig_positive_dominant.show()