Café Sales - Transactional Data Preprocessing Pipeline

Project Overview

This repository contains a Python-based data cleaning pipeline designed to handle corrupted, missing, and inconsistent logs from a café's Point of Sale (POS) system.

The goal was to convert raw, unreliable transactional records into a clean dataset ready for business intelligence reporting and statistical modeling.

To achieve this, I followed these steps:

• Displayed data information to get a general overview and identify missing values (NaNs) and incorrect column formats.
• Reviewed global statistical summaries to identify any special characters or placeholders that needed to be removed.
• Created a copy of the original DataFrame to ensure the raw source data remained untouched.
• Detected abnormal values in columns that should be numerical but were formatted as objects.
• Cleaned, capitalized, and standardized categorical text columns.
• Converted the Transaction Date column into a proper datetime format.
• Cleaned future numerical columns by removing extra spaces or special characters, then cast them to numerical types.
• Converted specific object columns into the category format to optimize memory usage.
• Identified and handled missing values (NaNs) using a targeted imputation strategy.
• Checked for potential duplicate records (none were found).
• Conducted outlier detection, re-evaluating the standard statistical approach to ensure extreme values did not distort or corrupt the true transactional data.

Tech Stack

• Language: Python 3
• Libraries: Pandas, NumPy, Matplotlib, Seaborn

Data Quality Issues & Solutions

1. Handling System Logging Errors

• The Issue: Categorical fields (Item, Payment Method, Location) included text placeholders like "ERROR" or "UNKNOWN" instead of true missing values.
• The Solution: Converted these specific error strings into standard NaN values to allow for correct data profiling and avoid skewing categorical distributions.

2. Missing Value Recovery

• The Issue: Missing values were identified across the Price Per Unit, Quantity, and Total Price columns.
• The Solution: I applied a group-based median replacement method to handle baseline gaps. The remaining missing metrics were dynamically reconstructed using the core business formula:
  Total Spent = Price Per Unit × Quantity

3. Outlier Analysis

• The Issue: A standard Interquartile Range (IQR) threshold flagged 247 outliers in the Total Spent column.
• The Insight: After filtering and inspecting these entries, they proved to be completely valid. Since individual café items are cheap ($1 to $5), a total transaction of $25 is mathematically flagged as an outlier, but practically represents a normal group order or a office lunch run.
• The Solution: Deleting or altering these rows would destroy real revenue data. I kept the records intact and engineered a new boolean flag column named is_bulk_order. This allows future analysts to segment large orders easily without dropping valid revenue.