# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: PyCharm (pycon2015_tutorial322)
#     language: python
#     name: pycharm-cef1f773
# ---

# + [markdown] pycharm={"name": "#%% md\n"}
# # Combining and Wrangling Both Data Sets
# -

# ## Imports

# + pycharm={"name": "#%%\n"}
import json
import gzip
import pandas as pd
import os.path
from dotenv import load_dotenv
import numpy as np
# -

# ## Environment Variables

# + pycharm={"name": "#%%\n"}
load_dotenv()
grocery_data_in = os.getenv('IN_DATA_FILEPATH') + os.getenv('RAW_META_FILE')
reviews_data_in = os.getenv('IN_DATA_FILEPATH') + os.getenv('RAW_REVIEW_FILE')
# This path is still in the markdown below because haven't found a working way to use a variable there.
doc_filepath = os.getenv('DOCUMENTATION_FILEPATH')
data_out = os.getenv('OUT_DATA_FILEPATH') + os.getenv('CLEANED_COMBINED_FILE')
# -

# ## Introduction
# Data Wrangling is the process of collecting, organizing, and determining how well-defined the data is.  
# See [Grocery Recommender - Capstone Two](../Grocery_Recommender_-_Capstone_Two.pdf) for details about this project. 

# ## Load Data 
# This public dataset is available online <sup>[1]</sup> and is not included in source/version control for space reasons.  
# After downloading the dataset add it to the file location in your .env file; see environments_example.txt.

# ### Product Dataset

# + pycharm={"name": "#%%\n"}
grocery_data = []
with gzip.open(grocery_data_in) as f:
    for l in f:
        grocery_data.append(json.loads(l.strip()))

# + pycharm={"name": "#%%\n"}
# Confirm data loaded by checking total number of products.
print(len(grocery_data))

# + pycharm={"name": "#%%\n"}
# convert list into pandas dataframe
grocery_df = pd.DataFrame.from_dict(grocery_data)
len(grocery_df)

# + pycharm={"name": "#%%\n"}
grocery_df.head(2)

# + [markdown] pycharm={"name": "#%% md\n"}
# ### Reviews Dataset

# + pycharm={"name": "#%%\n"}
reviews_data = []
with gzip.open(reviews_data_in) as f:
    for l in f:
        reviews_data.append(json.loads(l.strip()))

# + pycharm={"name": "#%%\n"}
# total length equals total number of reviews
print(len(reviews_data))

# + pycharm={"name": "#%%\n"}
# convert list into pandas dataframe
reviews_df = pd.DataFrame.from_dict(reviews_data)
len(reviews_df)

# + pycharm={"name": "#%%\n"}
reviews_df.head(2)

# + [markdown] pycharm={"name": "#%% md\n"}
# ## Merge DataFrames

# + pycharm={"name": "#%%\n"}
combined_df = grocery_df.merge(reviews_df, on='asin')
combined_df.shape

# + pycharm={"name": "#%%\n"}
# remove whitespace in column names
combined_df.columns = combined_df.columns.str.replace(' ', '')

# + pycharm={"name": "#%%\n"}
combined_df.head(2)

# + [markdown] pycharm={"name": "#%% md\n"}
# ## Clean Data
# See other wrangling notebooks for additional details.

# + pycharm={"name": "#%%\n"}
combined_df.info()

# + pycharm={"name": "#%%\n"}
# Clean multiple missing types of values
combined_df.replace({'': np.nan, 'NaN': np.nan, 0: np.nan}, inplace=True)
combined_df.info()

# + pycharm={"name": "#%%\n"}
# The price column has several issues with problem data; such as ranges and html without numbers
combined_df['price'] = combined_df['price'].apply(lambda x: np.nan if '{' in str(x) else x)
combined_df['price'] = combined_df['price'].apply(lambda x: np.nan if ' - ' in str(x) else x)

# + pycharm={"name": "#%%\n"}
# Clean blank lists in multiple columns
list_columns = ['category', 'description', 'rank', 'also_buy', 'feature', 'also_view']
for col in combined_df[list_columns]:
    combined_df[col] = combined_df[col].apply(lambda x: np.nan if len(x)==0 else x)
    

# + pycharm={"name": "#%%\n"}
# main_cat data  has some html tags that need to be cleaned
combined_df['main_cat'] = combined_df['main_cat'].apply(lambda x: np.nan if '<img src=' in str(x) else x)

# + pycharm={"name": "#%%\n"}
# Remove columns that don't have more than 5% of the data.
drop_thresh = combined_df.shape[0] * 0.05
combined_df = combined_df.dropna(thresh=drop_thresh, how='all', axis='columns').copy()

# + pycharm={"name": "#%%\n"}
combined_df.info()

# + pycharm={"name": "#%%\n"}
combined_df.isna().sum()

# + pycharm={"name": "#%%\n"}
# Drop several columns. See summary below for additional details.
combined_df.drop(columns=['description', 'image_x', 'feature', 'details', 'reviewTime', 'unixReviewTime', 'reviewerName', 'reviewText'], inplace=True)
combined_df.head(2)

# + pycharm={"name": "#%%\n"}
combined_df.info()

# + pycharm={"name": "#%%\n"}
# Analyze asin
print("Count of products in database", len(combined_df['asin']))
# unique asin
print("Unique count of products in database", len(set(combined_df['asin'])))

# + pycharm={"name": "#%%\n"}
# This example duplicate asin shows that it is the same product info but the reviews vary
example_duplicate = combined_df.loc[combined_df['asin'] == '4639725043']
example_duplicate.head(3)

# + pycharm={"name": "#%%\n"}
# Check if any of the duplicates are very similar (both the product and review info).
# This excludes columns that are lists in the comparison to avoid errors.
compare_columns = ['title', 'asin', 'reviewerID', 'summary']
similar_duplicates = combined_df[combined_df.duplicated(compare_columns)]
similar_duplicates.info()

# + pycharm={"name": "#%%\n"}
# Drop these duplicates.
combined_df.drop_duplicates(subset=compare_columns, keep='first', inplace=True, ignore_index=True)

# + pycharm={"name": "#%%\n"}
similar_duplicates = combined_df[combined_df.duplicated(compare_columns)]
similar_duplicates.head()

# + [markdown] pycharm={"name": "#%% md\n"}
# ### Clean up formatting and data types

# + pycharm={"name": "#%%\n"}
# price has '$' and ','.
combined_df['price'] = combined_df['price'].apply(lambda x: str(x).replace('$', '').replace(',', '')).astype('float')
combined_df.price[:3]

# + pycharm={"name": "#%%\n"}
# vote has mixed types of int, float, and string.
combined_df['vote'] = combined_df['vote'].apply(lambda x: str(x).replace('$', '').replace(',', '')).astype('float')
combined_df.vote[:3]

# + pycharm={"name": "#%%\n"}
combined_df.isna().sum()

# + pycharm={"name": "#%%\n"}
combined_df.info()
# -

# ## Save Data

# + pycharm={"name": "#%%\n"}
combined_df.to_csv(data_out, index=False)
# -

# ## Summary
# - Merged the product data set with the review data set using an inner join.
# - Originally there were 29 columns with 1,167,889 rows.
# - Removed 6 columns that had less than 5% of the data and didn't look critical for this task.
#     - This included tech1, fit, tech2, similar_item, date, and image_y (reviews image).
# - Also, dropped 8 more columns:
#     - feature because it doesn't have very much data, and the data there doesn't look useful.
#     - image_x (product image link) because this project won't analyze images.
#     - reviewTime and unixReviewTime because this project won't do time series analysis.
#     - reviewerName for privacy considerations
#     - description, details, and reviewText because this project won't do NLP analysis. 
#         - Adding this later on would be a good future enhancement.
#         - Note: there are some problem characters in the reviewText column that would need to be cleaned up further before doing this.
# - Finally, removed duplicate asin rows that were very similar for both the product and review info.
# - Carrying forward the rest of the data:
#    - 15 columns
#    - Max of 1,083,170 rows
#    - Min of 149,274 rows
# - Features of interest related to implicit feedback are title, also_buy, also_view and rank.
# - Features of interest related to explicit feedback are overall and summary.
# - The target feature is the overall rating of the product.

# ## References
# 1) <NAME> 2018 [Amazon Review Data (2018)](https://nijianmo.github.io/amazon/).  

StarcoderdataJupyterScriptsDedupFiltered