Branch Performance Analysis with Python: Multi-City Sales Comparison

A Python data analysis project comparing retail branch performance across the 5 largest provinces on Java Island, using pandas pivot tables, groupby aggregation, and resampling to track monthly trends in orders, customers, products, brands, and GMV.

Originally published on Rose Data Scientist.

Background

A company needed to compare branch performance across cities — specifically the 5 largest provinces on Java Island (DKI Jakarta, Jawa Barat, Jawa Tengah, Jawa Timur, and Yogyakarta) — across five metrics, on a monthly basis:

• Order count (unique orders)
• Customer count (unique customers)
• Product count (unique products sold)
• Brand count (unique brands sold)
• GMV (Gross Merchandise Volume — total sales value)

The goal was to automate chart generation for each of these measurements, comparing all 5 provinces side by side.

Dataset

Four quarterly CSV files of retail transaction data:

Period	Source
Jan – Mar	retail_data_from_1_until_3_reduce.csv
Apr – Jun	retail_data_from_4_until_6_reduce.csv
Jul – Sep	retail_data_from_7_until_9_reduce.csv
Oct – Dec	retail_data_from_10_until_12_reduce.csv

All hosted at https://storage.googleapis.com/dqlab-dataset/10%25_original_randomstate%3D42/

Workflow

The full analysis, in branch_performance.py, follows these steps:

1. Load & combine data — read all 4 quarterly CSVs and concatenate into one dataframe
2. Data inspection — check shape, columns, info, and descriptive statistics
3. Data cleaning — drop rows with negative prices or undefined order IDs; cast order_id to int64 and order_date to datetime
4. Filter to Java's 5 largest provinces
5. Group & aggregate — by order_date and province, computing unique order/customer/product/brand counts and summed GMV
6. Unstack — reshape so order_date is the row index and province becomes columns, for easy per-metric comparison
7. Slice by measurement — isolate each metric (e.g. order count) across all provinces
8. Resample monthly — compute the monthly average for each metric
9. Visualize — loop through all 5 measurements and plot each as a line chart (all provinces overlaid) in a single multi-panel figure

Key Findings

Average daily order size was highest and consistently growing in DKI Jakarta, followed by Jawa Barat, with the remaining three provinces (Jawa Tengah, Jawa Timur, Yogyakarta) trending at roughly similar, lower levels.

Key Concepts Used

• Combining multiple CSVs with pd.concat()
• Data validation and cleaning (.loc[] filtering, dtype casting)
• groupby().agg() with multiple aggregation functions
• unstack() for reshaping long-format data into wide comparison tables
• pd.IndexSlice for slicing multi-level columns
• Time-based resample('M') for monthly aggregation
• Looping over measurements to generate a multi-panel matplotlib figure automatically

Requirements

See requirements.txt. Core libraries: pandas, matplotlib.

License

MIT