English
РусскийOn this page
Data transformation – method notes
The decisions behind the data transformation rules – which convention, estimator, default or fallback stands behind each method, and the numbers that validate them, each computed on the example dataset (cafe.csv, its spec beside it). Sections follow the manual’s.
The rule editor
A grouped rule leaves a case outside every group missing, except a fill, which leaves it as it is. Binning, standardize and sequence derive their output from the group – a cut point, a mean and SD, a neighbouring row – so a case whose group is blank has nothing to be computed from, and writing its raw value through would put an untransformed number beside transformed ones in one column; it comes out missing, and the same holds for a case whose order value is blank under a sequence, which no ordered walk can place. A group with no numeric cell, or an ungrouped column with none, comes out missing under binning and standardize for the same reason: there is no cut point or mean to compute, and copying its text through would leave the output column, a 0/1 flag included, holding values the method never produced. A fill is the opposite operation: it only ever writes into a blank cell, so a case it cannot place is simply not filled, and its value – present or blank – stands. The two rules are the two halves of one principle: a transform never passes a value through untransformed, and a fill never destroys one.
Validation. Filling the café’s waiting times with the branch mean writes 4.84 into Riverside’s six blanks and 5.57 into Station’s nine; a member with no branch would keep the blank.
An order column sorts as numbers when every present value is one, else as text, and the sort is stable. A date column exported as text, a visit number, a timestamp in seconds – the app cannot know which is meant, so it reads the column: if every non-missing cell is a finite number the rows are ordered numerically, otherwise by locale-aware text comparison, so 10 sorts after 9 in a numeric column and after 1 in a text one. Ties keep the file order, so two rows with the same order value are walked in the order they were imported, and a run on the same file is reproducible.
Rule types
Value recode
A mapping matches by text and writes a numeric target as a number. Cells are compared with the typed original as strings, so a column imported as numbers and the same column imported as text recode alike, and a mapping written for one dataset carries to the other through the rule library. A target that reads as a number is stored as one, so a column recoded to numeric codes is typed continuous by the type pass that follows every run; a target that does not stays text. The mapping table pools the distinct values of every selected input in the canonical level order the analyses use – numeric ascending when every level is a number, else alphabetical – so the pre-fill reads as the frequency table would.
Range recode
Bounds are inclusive at both ends, overlaps are refused, and a blank bound is stored as an open end. Inclusive bounds are the reading people type – 1–3 → low, 4–5 → high on an integer scale. Half-open bounds, binning’s [a, b), would close the gaps inclusive ranges leave on continuous data (29.5 between 18–29 and 30–65), but they would drop 3 and 5 from that integer table and change the result of every rule already saved in a project or the library. The bounds therefore stay inclusive, and the silent cost of a gap is answered in the editor instead: it counts the cells that fall between ranges, and the All other values row decides what they become. Two inclusive rows that share a bound would both claim the value on it, and settling that by row order would hide a typo, so the editor refuses an overlap and an inverted range. The catch-all an overlapping table used to give – a wide row at the bottom with exceptions above it – is the All other values row. A rule saved before the check still runs, the first matching row winning, so its results do not change. A blank bound is saved as null and read as −∞ or +∞ at run time rather than stored as an infinity, because a rule travels through the rule library as JSON, which has no infinities and would turn one into null anyway; storing null from the start keeps a rule that was exported and re-imported identical to the one that was saved.
Binning
Derived cut points are type-7 quantiles, and a cut at or below the minimum is dropped. The quantile rule places the j/k cut at the linearly interpolated order statistic – R’s default quantile() type and the one the app’s other quantiles use – so a cut point copied from the preview into a range recode is the number quantile(x, j/k) would print. A cut that lands at or below the minimum – heavy ties at the bottom of a scale, or a k larger than the number of distinct values – would leave the lowest bin empty; it is dropped, the bins are renumbered, and the preview reports the bins that remain rather than padding the column with a label no case takes. Duplicate cuts collapse the same way. Typed cut points are used as saved, since a typed list is a definition, not an estimate.
Validation. Tertiles of the café’s 185 waiting times cut at 3.6333 and 5.9333 – quantile(wait, c(1/3, 2/3)) in R – and hold 62, 61 and 62 members; within branches the cuts are 3.5 and 5.7 at Riverside, 3.8 and 6.4 at Station.
Interval labels carry six significant digits. An interval label is data – it becomes a level of the new column – so it is not formatted to the display precision setting, which a reader may change after the rule ran; six significant digits reproduce any cut point the app derives closely enough to be typed back into a range recode and land every case in the same bin. The bins are half-open, [a, b), the convention of R’s cut(right = FALSE), so a case at a cut point belongs to the bin above it and the interval text says so with its brackets.
Validation. The waiting-time tertiles are labelled < 3.63333, [3.63333, 5.93333) and ≥ 5.93333.
Dummy coding
The reference level defaults to the first level in sort order, as the regression modules do. A dummy set built here and a categorical predictor coded inside a regression should agree on the level with no column, or the coefficients of the two would be measured against different yardsticks; the regression modules take the first level in canonical order, so the rule does too, with the most frequent level and any named level as the alternatives. A named level the data no longer holds – after a recode upstream, or on another file – falls back to the first level rather than failing, and the description shows both the level asked for and the one used. Effect coding puts −1 on the reference so the columns sum to zero across levels and the intercept is the grand mean, which is what makes its coefficients deviations from that mean rather than from the reference.
Collapse levels
Five is the floor because of the expected-count rule. The chi-square approximation behind a contingency table’s test is usually taken as adequate when the expected counts are five or more, and a level with fewer than five cases can hardly meet it in any table it enters; the default minimum count is that rule’s number, so the collapse a table needs is one click. Under the top-k method a tie at the boundary is broken by canonical level order, because the sort by count is stable: the levels enter it in canonical order and two levels with the same count keep it, so the level that stays is the earlier one and a re-run gives the same answer.
Validation. On the café’s overall rating, the minimum count of 5 collapses the scores 1, 2 and 3 – held by 2, 1 and 3 members – into Other and keeps 4, held by 7.
Log-ratio
Rows are closed before any log-ratio is taken, and every zero policy runs on the closed row. A log-ratio is a ratio of two parts of the same row, so the row’s total cancels and counts, percentages and proportions give the same coordinates – Aitchison’s scale invariance (Aitchison 1986) – which the rule makes explicit by dividing each row by its total first. A row with a missing, non-numeric or negative part has no composition and is missing in every column under either zero policy; so is a row whose total is 0, which no closure can rescale. The ILR coordinates are the pivot balances (Egozcue, Pawlowsky-Glahn, Mateu-Figueras & Barceló-Vidal 2003): the i-th balance sets part i against the geometric mean of the parts after it, scaled by for the r parts that follow, so the coordinates are orthonormal and each is named after one part, which is what lets a coefficient on ilr_<part> be read as that part against the rest.
The default δ is 0.65 of the smallest non-zero share. Multiplicative replacement (Martín-Fernández, Barceló-Vidal & Pawlowsky-Glahn 2003) puts a small share in place of each zero and shrinks the other parts by 1 − (zeros × δ), so the row still sums to one and the ratios between the non-zero parts are unchanged; the authors’ recommendation is δ at 0.65 of the detection limit. The app has no detection limit to read, so it takes the smallest non-zero share in the data as its stand-in and applies the same 0.65, and a user who knows the limit types 0.65 times it. A row whose zeros at δ would take the whole row – zeros × δ at or above 1 – cannot be rescaled and is missing.
Validation. Closing the café’s visit counts before and after the menu change, 11 of 200 rows have a zero part and the smallest non-zero share is 1/8, so δ = 0.65 × 0.125 = 0.08125; the two members with no visits in either window have a zero total and are left missing.
Standardize
Robust z scales the MAD by 1.4826. The median absolute deviation of a normal sample is 0.6745 of its SD, so dividing by 1.4826 × MAD – the reciprocal – puts a robust z on the same scale as the ordinary one: on normal data the two agree, and on data with an outlier the robust one is not inflated by it. R’s mad() applies the same constant by default. The cut-off in MADs from the median under Cut-off is measured in the same scaled units, so 3 MADs and 3 SDs mark the same distance on a normal sample and differ only where the SD has been pulled out by the tail.
Validation. The café’s waiting times have a median of 4.9 and a scaled MAD of 2.965 – mad(wait) in R – so the longest wait, 19.9, has a robust z of 5.06 against an ordinary z of 5.05; 3 MADs from the median reaches 13.80 and flags the two waits above it, 16.3 and 19.9, and 3 SDs from the mean reaches 13.94 and flags the same two.
Percentile ranks count half the ties, and the normal scores use Blom’s and van der Waerden’s positions. A percentile rank of with average ranks for ties is the share of the sample strictly below the value plus half the share tied with it, so the lowest value sits at and the highest at rather than at 0 and 100, and a tied block is centred on one percentile – the definition SPSS and most textbooks use. Blom’s position (Blom 1958) is the closest simple approximation to the expected normal order statistic, so the column’s values are what a normal sample of the same size would be expected to show at each rank; van der Waerden’s (van der Waerden 1952) is the plain plotting position, slightly less extreme in the tails. Either gives a column that is normal in shape whatever the original was, at the price that its differences no longer measure anything in the original units.
Validation. The shortest café wait, 0.3, has a percentile rank of 0.27 and the longest, 19.9, of 99.73 – ; Blom’s score for the longest wait is 2.709, the value qnorm((185 - 3/8)/(185 + 1/4)) gives.
The observed range is a fallback, not a default. POMP and reverse scoring are defined on the scale’s range – the lowest and highest answer the instrument allows – and the app cannot read that off a column: a 1–5 item nobody answered 5 to has an observed maximum of 4, and reversing against it maps 1 to 4 instead of 5, shifting every reversed score by one and changing the scale’s sum and its correlations with everything. The rule therefore takes typed limits and falls back on the observed range only for an end left blank, and the description names the observed range it used whenever it did, so the fallback is visible rather than silent.
Validation. The café’s reverse-keyed slow item uses all five points, so a rule with a blank range reverses as 6 − x – a 2 becomes a 4, a 5 a 1 – and the description shows the observed range 1–5.
A domain shift moves the smallest cell to 1 for the functions undefined at zero and to 0 for the functions defined there. The log and the reciprocal have no value at 0, so a column with a zero or negative cell is shifted by 1 − min, which puts its smallest cell at exactly 1 – the log of the smallest cell becomes 0 and the reciprocal 1; the square root and log(1 + x) are defined at 0, so they are shifted only as far as −min, which keeps every zero a zero. The shift is added, never chosen by the data beyond its minimum, and the description shows it, since a shifted log is a different transform from a plain one and a reader reproducing the column needs the constant. Box-Cox takes the log’s shift, being a log at λ = 0 and a power of the shifted value elsewhere; Yeo-Johnson needs none, its negative branch being the point of the family (Yeo & Johnson 2000).
Validation. The café’s shortest wait is 0.3, so the log needs no shift and takes the skewness from 1.27 to −0.93 – the overshoot the distributions page shows; a column holding a 0 would be shifted by 1 before it.
λ is the maximum of the profile log-likelihood, found by golden-section search on [−2, 2] and rounded to three decimals. For Box-Cox (Box & Cox 1964) the profile log-likelihood at a given λ is , the second term the log of the transformation’s Jacobian; Yeo-Johnson’s Jacobian term is . The function is unimodal in practice, so a golden-section search over [−2, 2] – the range in which a power transform is still interpretable – finds its maximum to within 10⁻⁴ with a few dozen evaluations and no derivative, and the result is rounded to three decimals so that the λ the description prints is exactly the λ the column was computed with: a reader who types it back gets the same column. A constant column has no likelihood to profile and takes λ = 1.
Validation. On the café’s waiting times Box-Cox estimates λ = 0.395 – MASS::boxcox on a 0.001 grid peaks at the same value – and Yeo-Johnson 0.212.
The cut-off defaults are the 5th and 95th percentiles or 3 SDs or MADs, and a percentile must be below 50. Five percent in each tail is the textbook winsorizing level, and three SDs or MADs the point beyond which a normal sample holds 0.3% of its cases; the default is the same number in both units, so the two cut-offs mark the same distance on a normal sample and differ only where the SD has been pulled out by the tail – the MAD version is the one an outlier cannot move, and the concepts page’s modified z-score rule is the same score with its line drawn at 3.5. A percentile at or above 50 would put the low cut-off above the high one and make every cell an outlier, so the rule refuses it; the percentiles are type-7 quantiles, as the bins’ are.
Validation. The café’s 5th and 95th waiting-time percentiles are 1.6 and 10.44 – quantile(wait, c(.05, .95)) in R – so a 5% winsorize pulls the ten waits above 10.44 down to it and the nine below 1.6 up to it.
Sequence
A percent change over a zero is missing, and a running mean averages what is in reach. The change from a previous row of 0 is a division by zero – ±∞, or 0/0 – and writing an infinity into the column would break every statistic computed on it, so the cell is missing, as it is where either row is blank. The running mean over w rows is the mean of the valid cells among the w ending at the row, not missing until w rows have accumulated: the first w − 1 rows average what precedes them, a window with a blank in it averages the rest, and only a window with no valid cell at all comes out missing – the convention of a moving average that starts at the series’ first observation rather than w − 1 rows in. The cumulative sum follows sum(v1:v7)'s rule for the same reason, skipping blanks rather than propagating them.
Missing values
Interpolation’s x is the order value when it is numeric, and tied order values split the gap in half. Linear interpolation between the nearest numeric cells needs a position for each row: the row’s place in its group by default, or the Order by variable’s own value when that variable reads as numbers, so a gap of three weeks between two measurements is spanned in proportion to the weeks rather than to the number of rows between them. Two anchors with the same order value have no distance to divide, so the rows between them take the halfway value. A cell that is not a number counts as a gap under interpolation – there is no line through text – where LOCF and NOCB carry any non-missing cell, text included, as it is.
The mode takes the first of the tied values in canonical order, written as the original cell. A column with two values equally frequent has two modes; the fill takes the one earlier in canonical level order – numeric ascending, else alphabetical – so the answer is reproducible rather than an accident of row order, and writes the cell as it was stored, so a numeric column filled with its mode stays numeric.
Managing rules
The list order is the run order, so an order that breaks a dependency is refused rather than re-sorted. A rule that reads a column created by a rule below it runs before that column exists, and so does a rule that reads its own output. Sorting the rules by their dependencies would fix the run but leave the list showing an order the rules no longer run in, and the list is what a reader takes as the record of the transformation. So the editor keeps the order you set and refuses a save or a drag that would break a dependency, naming the rule that creates the column. Two in-place rules on one column are not a dependency of this kind: rewriting a column twice is a deliberate sequence, and either order is a valid choice.
A failing rule leaves its created columns empty and its in-place columns as the rules above it left them. A rule’s previous output kept in a column it has just failed to write is indistinguishable from a correct result, so a created column is emptied. A column the rule rewrites in place held data before the rule ran, and emptying it would destroy values the rule never produced, so it keeps what the rules above wrote. Either way the rules below still run, and the card says the rule failed. A rule naming a variable the dataset lacks does not run at all rather than reading the absent column as all-missing: a column of blanks written from a variable that is not there looks like a result, and a dimmed card saying what is missing does not.
Rule library
The library names variables, where a project numbers them. Within a project a rule refers to its columns by their position in the file. A library rule is meant for other data, where the same position may hold anything, so a push stores the variables’ names and a pull looks them up in the loaded dataset. A rule then lands on the variable it was written for or on none, never on a stranger in the same column. Its content is compared in the named form too, so one rule pushed from two datasets with different column orders is one library entry.
A pushed rule records what it depends on, because the library has no order of its own. A file’s rules depend on each other only through the list order, and a library entry pulled into another file loses it. A push therefore records, for each rule, the rules above it that create a column it reads and those whose columns it rewrites, and a pull orders the selection by these records, the oldest push first among the rest. An entry pushed before the records existed is ordered by inference instead: a chosen rule that creates a column another reads goes first. A circular set is broken at the oldest entry.
Table converter
Generated subject identifiers are row numbers, so a subject on several wide rows cannot be recovered. Without a subject column the converter numbers the wide rows and gives every row’s measurements one identifier, which is right when the wide file holds one row per subject and silently wrong when it holds several – a subject measured twice becomes two subjects, no (subject, condition) pair repeats, and the paired analyses downstream pass every layout check on a design that is not what the data means. No later step can tell the two apart, which is why the warning stands under the select for as long as the generated option is chosen rather than appearing once.
The pattern, the number of conditions and the condition labels are read from the variable names when every group agrees. The selected variables’ display names are partitioned by shared prefix or suffix into groups of the same size – score_t1, score_t2 with rt_t1, rt_t2 – and when every group holds the same number of variables that number is the condition count, the parts that differ are the condition labels when every group yields the same ones, and the groups’ positions in the file say whether the file is grouped by condition or by variable. A partition that does not fit – groups of different sizes, or names with nothing in common – leaves the defaults in place, and labels the groups do not agree on fall back to T1, T2 …; the detection pre-fills the form and never overrides what is then typed.
Without an identifier, stacking drops the rows that are all missing and unstacking pads with them, so the two undo each other. Independent groups held side by side are columns of different lengths, and the shorter one ends in blank cells: a blank there is no case, only the end of a smaller group, so stacking with None (independent groups) drops a row whose measurements are all missing and each group keeps its own size. With an identifier a blank row means something else – a subject who missed a condition – and it stays, since the analyses downstream read the missing condition as part of the design. Unstacking with None – match rows by position is the reverse operation: each key combination’s rows are placed side by side and the shorter ones padded with missing cells, so a ragged wide file stacked and unstacked comes back as it was.
A repeated identifier × key cell is refused, never aggregated. When two long rows share an identifier and a key combination, the wide table has one cell for two values. Averaging them or keeping the first would each give a plausible wide file that differs from the data without saying so – two sessions merged into one score, a duplicate entry hiding a data-entry error – and nothing downstream could tell. A repeat nearly always means the identifier is incomplete, a session or a trial column left out, so the converter shows the first repeats and asks for the column that separates them.
A column given no role is carried only when it holds one value per identifier. A subject’s age or group is one value repeated on each of the subject’s long rows, and it goes into the wide row once; a missing cell is not a second value, so an age recorded on one row alone is carried too. A column that varies within a subject – a second measurement, a trial’s timestamp – has no single value for the wide row, and taking the first would present one condition’s value as the subject’s, so it is dropped, with a one-click move to the value columns that spreads it instead. With no identifier there is no subject to hold a value, and every such column is dropped.