Course contentsShow
← Data Science
Lesson 1140 of 2,14529. Data Wrangling WorkflowsFree lesson

Parsing Numbers with Inconsistent Formatting

Converting numbers with different thousands separators, decimal points, and currency symbols.

Parsing Numbers with Inconsistent Formatting

What you'll learn: How to clean and convert messy numeric data that arrives with different separators, decimal notations, and currency symbols into consistent, usable numbers.

The Problem with Real-World Numbers

When working with data from multiple sources—especially international datasets or scraped web data—numbers rarely arrive in a uniform format. You might encounter "1,234.56", "1.234,56", "$1,234.56", "€1 234,56", or even "1'234.56" all representing the same value. Your analysis tools expect clean numeric types, not these messy strings.

Core Cleaning Strategies

Step 1: Remove non-numeric characters. Strip out currency symbols ($, €, £), units, and whitespace using string methods like .replace() or regex patterns.

Step 2: Standardize decimal and thousands separators. European formats use commas for decimals (1.234,56), while US formats reverse this (1,234.56). Detect the pattern (usually the rightmost separator is the decimal) and normalize to a consistent format—typically the period for decimals.

Step 3: Convert to numeric type. Once cleaned, use pd.to_numeric() or similar functions to convert strings to floats or integers.

Practical Example (Conceptual)

Imagine a column with ["$1,200.50", "€1.200,50", "1200.50"]. You'd:

  • Strip $ and €
  • Detect that the second value uses European format
  • Replace . with empty string and , with . for European entries
  • Convert all to float: [1200.50, 1200.50, 1200.50]

Think of It Like Translation

Parsing inconsistent numbers is like translating between dialects—everyone means the same thing, but they express it differently. Your job is to standardize the "language" before doing calculations.

Key Takeaway: Always inspect sample data to identify format patterns, then systematically remove symbols, normalize separators, and convert types to transform messy number strings into clean numeric values ready for analysis.