Diff Two Snapshots of a Messy CSV in Python: No-Break Spaces, Duplicate Rows and Renamed Keys

This tutorial outlines a Python script designed to compare two versions of a CSV file, specifically the VPS Pricing Index dataset. The dataset is stored in a public GitHub repository and has undergone several updates over time. The original file, priced at the time of writing, contains information about various hosting plans offered by different providers.

Key issues arise due to the way the data was originally collected and formatted. For instance, money values are often represented as strings with thousand separators, currency symbols, or footnote characters instead of as numeric values. Additionally, there are inconsistencies in the representation of spaces, such as the use of no-break spaces, which can lead to rows being considered different even if they contain the same information.

To address these challenges, the tutorial provides a step-by-step approach to parsing and normalizing the data. The script includes several key functions:

1. **Parsing Money Values**: A function named `money()` is designed to extract monetary values from cells. It handles various formats, such as strings with commas, currency symbols, and footnote characters, converting them into standard float values. For example, it converts "2,520.00" or "€ 5.50" into the numeric values 2520.0 and 5.5, respectively.

2. **Normalizing Text**: Another function, `norm()`, is used to normalize text data. This function applies Unicode normalization (specifically, NFKC) to handle different types of spaces, such as the no-break space character. It ensures that variations in spacing, like between "Cloud VPS 4" and "(EUR)", are treated consistently. This is crucial for accurately comparing rows, as no-break spaces can sometimes cause rows that appear identical at first glance to be flagged as different.

3. **Loading Data**: The `load()` function reads the CSV file and extracts the header and data rows. It identifies the actual header row (usually the second row in this dataset) and creates a list of dictionaries, where each dictionary represents a row from the CSV with keys corresponding to the column headers.

4. **Keying Rows**: To identify and report changes between rows, the script uses a custom keying system. This system combines normalized versions of the provider and plan names to create a unique identifier for each row. This helps in distinguishing between duplicate rows that might exist within a single file or across different files due to changes in the dataset.

5. **Generating the Diff**: By leveraging the parsed, normalized data and the keying system, the script compares the two versions of the CSV file. It identifies rows that have been added, removed, or modified between the two snapshots. The output highlights these changes, making it easier to understand what has altered in the dataset between versions.

This approach ensures that the comparison is robust, accounting for common issues like inconsistent formatting, invisible characters, and duplicate rows, which are typical in datasets that are manually curated. By normalizing the data and using a consistent keying system, the Python script provides a reliable method for detecting changes in the VPS Pricing Index dataset, making it easier to track updates and maintain data accuracy.