Mercari Price Analysis: EDA and Feature Engineering for Price Prediction

End-to-end data pipeline — from raw Kaggle CSV to a 25-column, model-ready feature matrix.
Built as Phase 1 of a larger Mercari Price Prediction System (XGBoost + FastAPI + Docker).

---

Overview

This project performs a complete exploratory analysis and feature engineering pass on 50,000 listings from the Mercari Price Suggestion Challenge dataset. Starting from a raw CSV with missing values and a heavily skewed price distribution, it produces a clean 49,725-row feature matrix with 17 engineered columns ready for downstream modelling. Five key business insights are surfaced — including a brand premium of up to 20× the platform average and a non-trivial shipping strategy signal — with each finding backed by a reproducible chart. The pipeline is structured across six self-contained notebooks so any stage can be re-run independently.

---

Dataset

Property	Value
Source	Kaggle — Mercari Price Suggestion Challenge
Sample size	50,000 rows · 8 columns
Target variable	price (USD, continuous)
Raw nulls	brand_name 43.2% · category_name 0.47%
After cleaning	49,725 rows · 0 nulls

Columns in raw data:

Column	Type	Notes
train_id	int	Row identifier
name	string	Listing title
item_condition_id	int (1–5)	1 = New, 5 = Poor
category_name	string	Up to 3 levels, /-separated
brand_name	string	43.2% null
price	float	Target — USD, range $0–$1,506
shipping	binary	1 = seller pays, 0 = buyer pays
item_description	string	Free-text; "No description yet" used as null

---

Setup & Installation

# 1. Clone the repository
git clone https://github.com/Prashant-4527/mercari-price-analysis.git
cd mercari-price-analysis

# 2. Create and activate a virtual environment
python -m venv venv
source venv/bin/activate        # Windows: venv\Scripts\activate

# 3. Install dependencies
pip install -r requirements.txt

# 4. Place the dataset
# Download mercari_sample.csv from Kaggle and put it at:
# data/mercari_sample.csv

# 5. Run notebooks in order
jupyter notebook

Requirements (requirements.txt):

numpy>=1.26
pandas>=2.2
matplotlib>=3.8
seaborn>=0.13
jupyter>=1.0

---

Project Structure

mercari-price-analysis/
│
├── data/
│   └── mercari_sample.csv          # Raw dataset (not tracked in git)
│
├── notebooks/
│   ├── 01_data_loading.ipynb       # Load, inspect, profile raw data
│   ├── 02_cleaning_data.ipynb      # Null handling, zero-price removal, dedup
│   ├── 03_featuring.ipynb          # 17-feature engineering pipeline
│   ├── 04_eda_analysis.ipynb       # Group stats, pivot tables, outlier analysis
│   ├── 05_visualization.ipynb      # 6 publication-quality charts
│   └── 06_correlation_insights.ipynb  # Correlation matrix + feature selection
│
├── charts/                         # Auto-generated by notebooks 05 & 06
│   ├── A_price_distribution.png
│   ├── B_volume_vs_value.png
│   ├── C_condition_vs_price.png
│   ├── D_shipping_behaviour.png
│   ├── E_feature_correlation_heatmap.png
│   └── F_top_brands_by_price.png
│
├── requirements.txt
└── README.md

---

Key Findings

Finding 1 — Price is severely right-skewed; log transformation is required

Raw price has a skewness of 9.6 — a mean of $26.65 against a median of $17.00, with a max of $1,506. Training a regression model on raw price wastes model capacity on rare luxury items. log1p(price) compresses the range to 0–7.3 and produces a near-normal distribution.

Action: All downstream models use log_price as the target variable.

Finding 2 — Category is the single strongest price signal

Electronics average $34.95 — nearly 1.4× the platform average of $26.66. Women dominates listing volume (22,409 items, ~45% of the dataset) but sits below the price average. Vintage & Collectibles and Men punch above average despite small volume.

Category	Avg Price	Listing Count
Electronics	$34.95	4,222
Men	$34.90	3,055
Vintage & Collectibles	$29.28	1,585
Platform Average	$26.66	49,725
Women	$28.72	22,409
Kids	$20.21	5,725
Beauty	$19.69	7,089

Action: cat_avg_price (target encoding) is used as the primary category feature — avoids the cardinality explosion of one-hot encoding 1,259 unique category strings.

Finding 3 — Brand premium is real but highly concentrated

Top luxury brands average 5–20× the platform mean. brand_avg_price achieves r = 0.499 with price — the strongest non-leakage predictor in the entire feature set.

Brand	Avg Price	vs Platform Avg
Celine	$539.50	20.2×
Canada Goose	$415.00	15.6×
Valentino	$412.50	15.5×
Alexander McQueen	$398.67	15.0×
No Brand (43% of listings)	~$21.00	0.8×

Action: Encode brand as brand_avg_price — 1,529 unique brands cannot be one-hot encoded; the average price per brand captures exactly the signal we need.

Finding 4 — Shipping is a seller strategy signal, not a product signal

Items where sellers pay shipping list at higher prices across every category — sellers embed the shipping cost into the ticket price. The gap is most pronounced in Electronics ($48.05 buyer-pays vs $25.76 seller-pays) and Handmade.

Correlation: shipping → r = 0.100 with price. Weak globally, but directionally consistent and worth keeping as a feature.

Finding 5 — Description adds a measurable price premium in high-consideration categories

Listings with a written description command higher average prices in every category. The premium is largest in Electronics (+$18.57) and Men (+$4.20) — categories where buyers need specification detail before purchasing. It is smallest in Handmade and Sports & Outdoors, where visual trust dominates.

Correlation: has_description → r = 0.028 globally. Weak as a standalone feature; becomes stronger in a category-interacted model.

Feature Engineering Summary

17 features engineered across 6 groups. All 17 confirmed present with zero nulls introduced (03_featuring.ipynb assertions).

#	Feature	Type	Derived From	Correlation with Price
1	main_category	categorical	category_name	—
2	sub_category	categorical	category_name	—
3	sub_sub_category	categorical	category_name	—
4	name_length	numeric	name	0.033
5	desc_length	numeric	item_description	0.048
6	name_word_count	numeric	name	0.036
7	has_description	binary	item_description	0.028
8	is_branded	binary	brand_name	0.138
9	condition_label	categorical	item_condition_id	—
10	log_price	numeric	price	0.752 (target)
11	is_price_outlier	binary	price	0.673
12	price_tier	categorical	price	—
13	cat_avg_price	numeric	main_category + price	0.135
14	brand_avg_price	numeric	brand_name + price	0.499
15	price_rank_in_cat	numeric	main_category + price	0.263
16	price_percentile	numeric	price	0.570
17	category_depth	numeric	category_name	0.038

Multicollinearity flags identified:

Pair	r	Decision
log_price ↔ price_percentile	0.954	Drop price_percentile before modelling; keep log_price as target
name_length ↔ name_word_count	0.876	Keep name_length only

Recommended feature set for Phase 3 modelling (features with |r| > 0.05 with price, multicollinearity removed):

FEATURES = [
    "brand_avg_price",    # r = 0.499 — strongest non-leakage signal
    "price_rank_in_cat",  # r = 0.263
    "is_branded",         # r = 0.138
    "cat_avg_price",      # r = 0.135
    "shipping",           # r = 0.100
    "desc_length",        # r = 0.048
    "category_depth",     # r = 0.038
    "name_word_count",    # r = 0.036 (name_length dropped — multicollinear)
]
TARGET = "log_price"

Tech Stack

Tool	Version	Purpose
Python	3.13	Core language
pandas	2.x	Data loading, cleaning, groupby, pivot
NumPy	1.x	Vectorised transforms (log1p, IQR, percentile)
Matplotlib	3.x	Chart rendering
Seaborn	0.13	Statistical visualisation (heatmap, boxplot)
Jupyter	1.x	Interactive notebook environment

---

⚠️ Note: cat_avg_price, brand_avg_price, and price_rank_in_cat are computed on the full dataset. Proper cross-validated target encoding will be applied in Phase 3 before modelling.

Next Steps

This notebook series feeds directly into Project #6 — Mercari Price Prediction System:

• Model training — XGBoost baseline on the 8-feature set identified above; RMSLE target metric (mirrors Kaggle competition scoring)
• Text features — TF-IDF on item_description and name; test adding NLP signal on top of structured features
• SHAP explainability — identify which features drive individual predictions; pitch-ready for Mercari ML interviews
• FastAPI wrapper — expose /predict endpoint accepting raw listing JSON, returning predicted price + confidence interval
• Docker packaging — containerise the model server; docker run should reproduce the full inference stack
• Kaggle submission — benchmark against public leaderboard RMSLE to validate feature quality

---

Dataset: Mercari Price Suggestion Challenge — Kaggle