Data Preparation and Management
From source to analysis-ready
Data preparation often consumes more effort than analysis itself. A reliable workflow is: define the question → identify sources → import → profile and assess quality → clean → transform → validate → document.
Importing. Text files are separated by a delimiter (comma for CSV, or tab, semicolon, pipe). In locales where the comma is the decimal separator, CSVs often use semicolons. Check encoding (UTF-8 avoids corrupted accents). Watch automatic type conversion: postcodes and product IDs with leading zeros lose them unless stored as text, and dates imported as text will not sort or calculate correctly.
Data quality dimensions. Accuracy (does it match reality?), completeness (missing values), consistency (same format and units throughout), uniqueness (no unintended duplicates), validity (values inside allowed ranges or lists), timeliness (current enough) and integrity (relationships between tables hold).
Cleaning actions. Remove unintended duplicates but keep legitimate repeat transactions; trim stray spaces; standardise case and date formats; harmonise category labels ("UK", "U.K.", "United Kingdom"); handle missing values by deleting, flagging or imputing, and state which; investigate outliers rather than deleting them automatically.
Structuring. A table has records (rows) and fields (columns). A primary key uniquely identifies a row; a foreign key links to another table. Joining adds columns by matching a key (duplicate keys multiply rows); appending stacks rows with matching columns. Sorting and filtering help inspection but do not change the underlying data.
Traps
Assuming "remove duplicates" is always safe; merged cells; lookups failing on trailing spaces or text-versus-number mismatches; not comparing row counts before and after a join; overwriting the raw file instead of keeping an untouched copy.
Rule of thumb
Keep raw data read-only, work on a copy, and record every transformation in a change log so results are reproducible.