Elysia Tools
Navigation
Workflow Playbook
Take messy tabular data to analysis-ready — merge source tables into one, strip the byte-order marks that break column matching, keep only the columns you need, fix spellings and formats, handle outliers by policy instead of panic, scale or standardize the numbers, and finish with range limits and a crosstab summary.
Hubs
Data preparation fails most often by being done twice to different standards. Two source exports cleaned separately grow two date formats and two category spellings, and their merge re-opens every wound. The order here prevents that — merge first so column matching happens once over all sources, clean once over the whole table, and every later step applies a single policy to a single table. The prerequisite is honesty about the columns that matter: narrowing before working halves the surface where errors can breed.
Two invisible failures account for most silent prep bugs. The byte-order mark sits in front of the first column name looking like nothing and breaking every exact match against it — the join that returns zero rows without an error is its signature. Duplicates are the second: two rows that agree on every field are one fact counted twice, and any total built on them lies by exactly that much. Both traps are cheap to disarm — strip the mark, dedupe the table — and expensive to discover downstream, which is why they come before anything clever.
An outlier is not dirt; it is a datum making a strong claim — I am a glitch, or I am the event you are here to study. Removing it is a domain judgment with statistical consequences, so this workflow forces the decision into the open: a stated policy per column, executed and counted. Replace with a median or a cap when tails should soften but rows should stay; flag when the downstream analysis deserves the vote; remove when the value is provably wrong. What the policy was belongs in the notes, because a result that cannot say how it treated its extremes cannot defend itself.
Min-Max and Z-score are not interchangeable habits. Min-Max bounds everything to a 0-to-1 range and preserves shape — fine when the downstream method wants bounded inputs, fragile when one extreme crushes everything else against zero. Z-score centers columns at zero with unit variance and shrugs at heavy tails — right for methods that measure in standard deviations. The test is arithmetic, not visual: a Min-Max column must span exactly 0 to 1, a standardized column must average 0 with standard deviation 1, and the method used must be recorded for whoever reruns the work.
This page ends with a clean, shaped, scaled working table and its crosstab summary. Around it sit the neighbors that own adjacent stages — pulling structure out of unstructured text is the text-table extraction workflow, row-and-column surgery on a single CSV is the CSV utility workflow, auditing a data set for quality anomalies is the data quality workflow, and moving the finished table into a workbook or another format belongs to the ingestion and conversion workflows. Shape the data here, then hand it to the stage that owns what happens next.
Workflow playbook
Vertically append the source tables with intelligent column matching so like columns land together, and strip byte-order marks that make the first column name unmatchable.
Extract only the columns the analysis uses, then fix spelling errors, standardize formats, and remove duplicates in one deliberate pass.
Detect the extreme values, then remove, replace, or flag them according to the stated policy — recording what was done and how many rows it touched.
Apply Min-Max normalization when the downstream method wants a bounded 0-to-1 range, or Z-score standardization when it wants centered, unit-variance columns — one choice per table, written down.
Removal keeps the statistics pure and costs the samples; replacement with a median or a cap keeps every row and softens the tails; flagging preserves everything and hands the judgment downstream — pick by how much the analysis depends on the extremes.
Min-Max presses values into a fixed 0-to-1 range and keeps shape but lets a single extreme drag the rest into a corner, while Z-score centers on zero with unit variance and tolerates heaviness in the tails — choose by what the downstream method assumes, and record which one ran.
Clipping pulls stragglers back inside the legal range and keeps every row, filtering deletes the rows that violate it, and marking keeps both the value and the verdict — the right choice depends on whether an out-of-range value means a bad reading or a bad sensor.
Limit values to their legal ranges by clipping, filtering, or marking, then generate the crosstab that turns the long working table into the summary the analysis actually reads.