Home / Statistical Tools / Analysis Tools / Johnson Transformation / How-To / GroupBy
GroupBy¶
Use GroupBy to fit a separate Johnson transformation to each group in your data. Every group gets its own family, its own parameters and its own before-and-after normality result, built only from its own rows.
Goal¶
Transform cycle times from two machines separately, and see what happens to the answer when they are pooled instead.
Sample Data¶
Ten cycle times from each of two machines, in minutes. Machine A's readings are the ten from the Quick Start. Press Copy for Excel, then paste into a blank worksheet.
| Machine | Cycle Time |
|---|---|
| Machine A | 3.49 |
| Machine A | 6.17 |
| Machine A | 3.68 |
| Machine A | 3.65 |
| Machine A | 4.29 |
| Machine A | 4.04 |
| Machine A | 3.71 |
| Machine A | 5.26 |
| Machine A | 6.34 |
| Machine A | 3.95 |
| Machine B | 5.07 |
| Machine B | 5.94 |
| Machine B | 4.30 |
| Machine B | 5.74 |
| Machine B | 4.81 |
| Machine B | 4.80 |
| Machine B | 6.42 |
| Machine B | 5.12 |
| Machine B | 4.97 |
| Machine B | 5.55 |
Machine is the grouping column. It is not a measurement, and you do not tick it in the data column list.
Steps¶
-
Put the data in Excel
Press Copy for Excel above the table, click cell A1 in a blank worksheet, and press Ctrl+V. Headers land in row 1 and the twenty readings in rows 2 through 21.
-
Launch the analysis
From the Excel ribbon, select QXL Stat Tools → Analysis Tools → Johnson Transformation.
-
Select your data
Select cells A1:B21, the header row plus all twenty data rows.
-
Switch to GroupBy and build the group list
Select the GroupBy radio button instead of Excel. The GroupBy group box appears with two lists, Available Items and GroupBy Order. Select Machine and press > to move it across.
-
Choose the measurement column
Under Data Columns, tick Cycle Time. Leave Machine unticked.
This runs the analysis, so do it last
The dialog closes and runs the moment the column selection changes. Set the data source and the GroupBy list first, and tick the measurement column last, or the analysis will run before you have finished setting it up.
-
Answer the prompt for Machine B
Before writing the sheet, Quantum XL tests each group's original data. Machine B passes, so a message box appears:
The data for "Johnson Transformation for Cycle Time - Machine B" appears to already be normal (Anderson-Darling p-value > 0.1). A transformation may not be needed.
Do you want to transform anyway?
Answer No for this walkthrough. No is the default button, so pressing Enter also chooses it.
Result¶
One worksheet named Johnson Transformation, with two blocks stacked down it. GroupBy does not create a worksheet per group.
Machine A, transformed¶
| Row | Value |
|---|---|
| Family Type | SL (Lognormal) |
| η (Eta) | 0.6703 |
| γ (Upsilon) | 0.4844 |
| λ (Lambda) | N/A |
| ε (Epsilon) | 3.4382 |
| AD p-Value | 0.735 |
| Optimal Z | 0.39 |
| Table | Sample Size | AD Statistic | AD p-Value |
|---|---|---|---|
| Normal Fit - Original Data | 10 | 0.8828 | 0.015 |
| Normal Fit - Transformed Data | 10 | 0.2304 | 0.735 |
Identical to the Quick Start, because this group is exactly that data. A group is fitted from its own rows and nothing else.
Machine B, not transformed¶
| Table | Sample Size | AD Statistic | AD p-Value |
|---|---|---|---|
| Normal Fit - Original Data | 10 | 0.2432 | 0.688 |
That is the whole block. Answering No to the prompt removes far more than the transformed numbers: this group has no Johnson Parameters table, no Transformation Formulas, no side-by-side data table and no transformed probability plot. Only the original-data normality table and the original-data probability plot are written.
Answering Yes instead would have produced a full block, because the transformation was already computed before the question was asked.
What Pooling Hides¶
Run the same twenty rows again with the Excel data source and no GroupBy, so both machines go in together.
| Machine A alone | Machine B alone | Both pooled | |
|---|---|---|---|
| Original AD statistic | 0.8828 | 0.2432 | 0.3458 |
| Original AD p-value | 0.015 | 0.688 | 0.447 |
| Already-normal prompt | no | yes | yes |
Pooled, the data passes. The p-value climbs from 0.015 to 0.447 and Quantum XL asks whether a transformation is needed at all. Machine A's skew has not gone anywhere; it has been diluted by ten readings from a machine that does not share it, and mixing two centers has spread the combined column out in a way that happens to look symmetric. One column that needed a transformation and one that did not, averaged into a verdict that is wrong for both.
That is the case for GroupBy on this tool. It is not only a convenience for producing several outputs at once, it is what stops a real feature of one group being cancelled out by another.
How GroupBy works here
Quantum XL produces one result per data column per group, so two groups and one measurement column give two blocks. Each block carries the group name after the column name in its title: Johnson Transformation for Cycle Time - Machine A. The separator is a space, hyphen, space, and with more than one grouping column the group name can contain further separators of its own.
Things to try next¶
- Answer Yes to the prompt and re-run. Machine B gets a full block: an SL fit at Optimal Z 0.29, and an AD p-value that improves from 0.688 to 0.840. Improving a column that already passed is exactly why the question is asked.
- Add a third machine with only three readings. That group is refused with Fewer than 4 distinct values required for Johnson Transformation and the other two are written as normal. One group failing does not stop the run.
- Re-run without changing anything. The answer you gave to the prompt is cached against the chart title, so it is not asked again. Deselecting the column and reselecting it clears that cache and the question returns.