Data Standardization and Formatting

Section 3: Data Assessment and Preparation. Lesson 16.

Data standardisation is the process of converting data into a consistent structure, format and representation across all datasets before migration. In a migration, data comes from different systems, departments and users, and each records information its own way. Standardisation removes those differences so the target receives one consistent version.

Formatting and standardisation

• Data formatting ensures every value follows a predefined layout, such as dates as YYYY-MM-DD or two decimal places.
• Data standardisation goes further: values are represented consistently according to business rules and organisational standards, such as one currency, one set of country names, one way of writing a customer name.

Hands-on: Walmart sales dataset

The lecture downloads a small Walmart sales dataset from Kaggle and opens it in Excel. Its columns include Date, Weekly_Sales, Holiday_Flag, Temperature, Fuel_Price, CPI and Unemployment. The irrelevant Holiday_Flag and Unemployment columns are removed to focus the view.

• The Date column mixes formats in the same file: some cells use one layout, others a different one.
• Hypothetically, if Weekly_Sales held some values in US dollars and others in euros, the column would look consistent but add up to nonsense.

Common inconsistencies

• Dates: 10-10-2000, 10/10/2000 and October 10, 2000 for the same day, plus day-month versus month-day ambiguity (is 03/04 March 4th or 3rd April?)
• Phone numbers: +44 20 7946 0958 versus 020 7946 0958, with and without country codes
• Names: JOHN SMITH, john smith and John Smith
• Currencies: USD mixed with EUR, or $ symbols in some cells only
• Country names: USA, U.S., United States, US
• Addresses: St. versus Street, Rd versus Road, and inconsistent postcode spacing
• Units and codes: kg versus lbs, and status codes such as A or Active

At enterprise scale the same information is often stored in several forms, which leads to migration errors, failed loads and broken reports.

Multiple sources, one target

Standardisation matters most when several sources feed one target. In the lecture's diagram, three sources store the same date three ways:

S1: 10-10-2000        ──┐
S2: 10/10/2000        ──┼──▶  convert to one agreed format  ──▶  T1 (target): 2000-10-10
S3: October 10, 2000  ──┘

If mixed formats arrive in the target, the chance of downstream errors is very high. The fix is to:

1. Agree one standard format for the target.
2. Configure the target to expect it.
3. Convert every input, whether from S1, S2 or S3, into that format through a systematic conversion step.

For dates, prefer ISO 8601 (YYYY-MM-DD, with time zone for timestamps) as the standard. It sorts correctly, is unambiguous internationally, and is what databases expect.

Tools and techniques

Excel

Select the column, right-click, choose Format Cells, then Date, and pick one format to apply to the whole selection. This only works on cells Excel already recognises as real dates. Dates stored as text won't change, so convert them first with DATEVALUE, Text to Columns or Power Query.

AI tools

Upload the CSV to an assistant such as ChatGPT with a prompt like "Standardise the date format in this file to YYYY-MM-DD", and it returns the file in one consistent format. The same cautions apply as before: no sensitive data in public tools, and spot-check the output, especially ambiguous dates such as 03/04.

Python (pandas)

import pandas as pd

df = pd.read_csv("walmart_sales.csv")

# Dates: parse mixed formats, then write one standard (ISO 8601)
df["Date"] = pd.to_datetime(df["Date"], format="mixed", dayfirst=True).dt.strftime("%Y-%m-%d")

# Names: consistent casing and spacing
df["customer_name"] = df["customer_name"].str.strip().str.title()

# Country names: map variants to one value
country_map = {"USA": "United States", "U.S.": "United States", "US": "United States"}
df["country"] = df["country"].replace(country_map)

# Phone numbers: digits only, then add the country code if missing
df["phone"] = df["phone"].str.replace(r"\D", "", regex=True)
df.loc[~df["phone"].str.startswith("1"), "phone"] = "1" + df["phone"]

# Currency: convert to one currency using a rate, never just relabel
EUR_TO_USD = 1.08
df.loc[df["currency"] == "EUR", "Weekly_Sales"] *= EUR_TO_USD
df["currency"] = "USD"

The customer, country, phone and currency columns illustrate the technique and are not in the Walmart file. For currencies, use the exchange rate that applied on each transaction's date, and agree the rule with finance.

SQL

-- PostgreSQL: standardise during the load into the target
INSERT INTO target.customers (name, email, country, created_on)
SELECT INITCAP(TRIM(name)),
       LOWER(TRIM(email)),
       COALESCE(cm.standard_name, s.country),
       TO_DATE(s.created_on, 'DD/MM/YYYY')
FROM staging.customers s
LEFT JOIN ref.country_map cm ON cm.variant = s.country;

Best practices

• Write a data standards document for each field type: format, casing, units, codes and allowed values.
• Use reference and lookup tables, such as ISO 3166 country codes, ISO 4217 currency codes and E.164 phone numbers, rather than ad-hoc rules.
• Standardise in a staging layer, keeping the raw original.
• Validate after conversion: count values that still fail the format, and review ambiguous cases.
• Enforce the standard in the target with constraints, types and input validation, so new data stays clean.

Benefits

• Improves consistency across all sources
• Enhances foundational data quality
• Makes reporting and analytics reliable
• Simplifies integration and migration
• Makes duplicates detectable: "JOHN SMITH" and "john smith" only match once standardised (see lesson 15)

Without standardisation, combined data quickly becomes unintelligible. The lecture uses dates as the main example, but the same principle applies to currencies, country names, addresses, customer names and phone numbers.

Try it: Three-Source Format Unifier

The S1, S2, S3 to T1 diagram, working. Each source writes dates, country names and phone numbers its own way. Choose the target date format and every value converts to it; countries map to one code and phones to E.164. Ambiguous dates such as 03-04-2021 and impossible ones such as 2023/02/30 are flagged rather than guessed. Toggle whether S1's numeric dates are day-first, or edit the sources to test your own values.

Key takeaways

• Standardisation converts data into one consistent structure, format and representation before migration.
• Formatting fixes the layout; standardisation applies business rules and organisational standards.
• With multiple sources, agree one target format (ISO 8601 for dates) and convert every source to it.
• Use Excel for small fixes, AI for quick passes, and pandas or SQL with reference tables for repeatable migrations.

This completes Section 3: Data Assessment and Preparation.