Lab 1: Preprocessing & Data Wrangling

Course: INS-605: Data Analysis II
Lecturer: Sothea HAS, PhD

Objective: In this lab, we will work with real-world product review data (Amazon Product Reviews) to practice key data wrangling techniques discussed in the lecture: inspecting raw data, handling missing values, converting data types, feature transformation, grouping/aggregation, combining datasets, and reshaping data.

The notebook of the lab can be downloaded from Lab1: Proprocessing & Data Wrangling.

Student name: …
ID: …

Submission: You must do the followings

Name your file as: Lab1_name.ipynb

Submit this jupyter notebook file (Lab1_name.ipynb) to Canvas, NOT THE COLAB LINK!

1. Importing and Inspecting the Data

We will explore the Amazon Product Reviews dataset from Kaggle (arhamrumi/amazon-product-reviews). Let’s download and load the dataset into pandas from the following Kaggle link.

Code

# %pip install kagglehub

import kagglehub
import pandas as pd
import os

# Download latest version
path = kagglehub.dataset_download("arhamrumi/amazon-product-reviews")

# Load data (locate the CSV file in the downloaded path)
file_name = [f for f in os.listdir(path) if f.endswith('.csv')][0]
data = pd.read_csv(f"{path}/{file_name}")
data.head()

1.1. Overview of the data

Before cleaning, inspect the structure of the dataset and answer the following:

A. Check the dimension of the dataset (shape). How many rows and columns are there?

B. What are the data types of each column (dtypes)? Identify columns whose data types do not match their intended semantic meaning.

C. Check for missing values (isna().sum()). Which columns contain NaN values, and how many are missing in each column?

D. Are there any duplicated rows in this dataset? Calculate the total number of duplicates using .duplicated().sum().

E. Check the number of unique values in each column (nunique()).

E.1. Compare the number of unique UserIds to unique ProfileNames. Why do you think we observed such numbers?
E.2. Do missing names in ProfileName potentially share a UserId with non-missing rows? This will guide our imputation strategy.

F. Check if missing ProfileNames are related to missing Summarys.

# Let's find out!

2. Data Cleaning

Now that you have identified the issues in raw data, let’s clean them step-by-step.

F. Datetime Conversion: Convert the Time column (Unix timestamp in seconds) into a proper pandas datetime format using pd.to_datetime(..., unit='s').

G. Handling Missing Text Values (Smart Imputation):

G1. Recovering Profile Names: Since users might have multiple reviews, a missing ProfileName might have a known ProfileName under the same UserId in another row. Check if there are such cases, therefore use this information to impute the missing values.
G2. Fallback Imputation: Fill any remaining missing values in ProfileName with 'Unknown', and fill missing values in Summary with 'No Summary'.

H. Duplicates: If any duplicated rows exist, drop them using .drop_duplicates() and verify the shape of your DataFrame afterward.

# To do

3. Feature Transformation

Feature engineering helps turn raw fields into more useful analytical features.

I. Rating Classification (Status): Create a new column named Status based on the review rating (Score):

'High' if Score >= 4
'Medium' if Score == 3
'Low' if Score <= 2

J. Temporal Extraction (Year): Extract the year from your converted Time column and store it in a new column called Year.

K. Metric Calculation (HelpfulnessRatio): Create a column HelpfulnessRatio by dividing HelpfulnessNumerator by HelpfulnessDenominator. Handle division-by-zero cases (e.g., set to 0 when HelpfulnessDenominator == 0).

# To do

4. Grouping & Aggregation

L. Most Reviewed Products: Group by ProductId. Find the top 5 products with the highest number of reviews.

M. User Activity: Group by UserId. Find the top 5 most active reviewers (users with the most reviews) and calculate their average review Score.

N. Product Metrics: For the top 5 products identified in Question L, use .groupby() and .agg() to compute both the average Score and total count of reviews.

# To do

5. Combining and Reshaping Data

O. Combining Datasets (pd.merge): Create a small lookup DataFrame named product_catalog containing:

ProductId: The top 5 product IDs from Question L
Category: Assigned categories (e.g., 'Beverages', 'Snacks', 'Gourmet', 'Pet Supplies', 'Organic')

Perform an inner join (pd.merge) between your aggregated product metrics from Question N and product_catalog.

P. Reshaping Data (pd.pivot_table): Create a pivot table using pd.pivot_table() to summarize review volume:

Index (Rows): Year
Columns: Status (High, Medium, Low)
Values: Count of reviews (Score)
Mean: average score per category of Status.
Fill missing cells with 0.

Q. How does the platform perform over the years?