What is Data Wrangling?

What is Data Wrangling?

Data Wrangling, sometimes called data munging or data preparation, is the process of taking raw, messy, inconsistent data and transforming it into a clean, structured format that can be reliably used for analysis, reporting, and decision-making. It is one of the most time-consuming yet essential activities in any data-driven organisation. Industry surveys consistently show that data professionals spend 60 to 80 percent of their time on data wrangling rather than actual analysis.

Raw data is inherently messy. Customer names are spelled differently across systems. Date formats vary between American (MM/DD/YYYY), European (DD/MM/YYYY), and ISO (YYYY-MM-DD) conventions. Currency fields mix formats with and without symbols. Spreadsheets contain merged cells, hidden rows, and inconsistent headers. API data arrives in nested JSON structures that need to be flattened for analysis. Data wrangling handles all of these challenges.

The Data Wrangling Process

Data wrangling follows a general workflow, though in practice it is often iterative rather than linear:

1. Discovery Before cleaning data, you need to understand it. This involves profiling the data to assess its structure, completeness, uniqueness, and distribution. How many records are there? What percentage of fields are populated? What are the minimum, maximum, and average values? Are there obvious outliers or anomalies?

2. Structuring Raw data often needs to be restructured before it can be cleaned. This includes normalising hierarchical data into tabular formats, splitting or combining columns, pivoting data from wide to long format (or vice versa), and standardising the schema across multiple data sources.

3. Cleaning The core of data wrangling involves identifying and correcting data quality issues:

• Missing values: Deciding how to handle records with incomplete data, whether to fill in defaults, impute from other records, or exclude them from analysis.
• Duplicates: Identifying and resolving duplicate records, which requires defining what constitutes a duplicate and deciding which version to keep.
• Inconsistencies: Standardising values that should be the same but are recorded differently, such as "Singapore," "SG," "SGP," and "Republic of Singapore" all referring to the same country.
• Errors: Correcting obvious data entry mistakes, such as ages of 999, negative prices, or dates in the future for historical records.
• Outliers: Identifying values that fall far outside expected ranges and determining whether they represent genuine observations or errors.

4. Enrichment Adding value to the existing data by incorporating additional information. Examples include geocoding addresses to add latitude and longitude, adding industry classification codes to company records, calculating derived fields like customer lifetime value or days since last purchase, and joining external reference data such as exchange rates or population statistics.

5. Validation Confirming that the wrangled data meets quality standards before it is used for analysis. This includes checking row counts, verifying that key fields are populated, testing business rules (such as order dates being after customer creation dates), and comparing aggregated totals against known reference points.

Data Wrangling Challenges in Southeast Asia

Businesses operating across ASEAN face data wrangling challenges that are amplified by the region's diversity:

• Multi-script text data: Customer names and addresses in Thai, Vietnamese, Bahasa, Chinese, and Latin scripts require different cleaning and matching approaches. Standard text cleaning functions designed for English often fail with non-Latin characters.
• Address format variation: Address formats differ dramatically across ASEAN countries. Singapore has standardised postal codes, while addresses in parts of Indonesia or the Philippines may include informal landmarks and neighbourhood descriptions.
• Currency and number formatting: Numeric formats vary by locale. Some countries use periods as decimal separators while others use commas. Currency fields may include symbols, codes, or neither.
• Date and time formats: Date conventions vary across the region, and time zones span from UTC+6:30 (Myanmar) to UTC+9 (eastern Indonesia). Standardising timestamps across markets is a common wrangling task.
• Data source diversity: ASEAN businesses often collect data from a mix of modern cloud platforms, legacy systems, spreadsheets, and even paper-based processes that have been digitised inconsistently.

Data Wrangling Tools and Approaches

• Code-based: Python (with pandas, NumPy) and R are the most popular programming languages for data wrangling, offering maximum flexibility and reproducibility. SQL is essential for data wrangling within databases.
• Visual and low-code: Tools like Trifacta (now part of Alteryx), Talend, and Microsoft Power Query provide visual interfaces for building data wrangling workflows without extensive coding.
• Spreadsheet-based: Excel and Google Sheets remain widely used for smaller-scale wrangling tasks, particularly by business analysts and finance teams.
• Cloud-native: Google Cloud Dataprep, AWS Glue DataBrew, and Azure Data Factory include built-in data wrangling capabilities for cloud-based data.
• Notebook environments: Jupyter Notebooks and Google Colab combine code, visualisation, and documentation in a format well-suited to iterative data wrangling work.

Best Practices for Data Wrangling

1. Document everything: Record every transformation applied to the data. When someone asks why a number looks different from the raw source, you need to explain exactly what was done and why.
2. Preserve raw data: Never modify the original source data. Always work on copies so you can revert and audit transformations.
3. Automate repetitive tasks: If you are cleaning the same type of data regularly, build reusable scripts or workflows rather than repeating manual steps each time.
4. Validate continuously: Check data quality at each stage of the wrangling process, not just at the end. Catching errors early prevents them from propagating through downstream transformations.
5. Define standards upfront: Establish naming conventions, date formats, currency handling rules, and other standards before beginning the wrangling process.