## Key

• This line was removed.
• Formatting was changed.

D toc

Excerpt

This section describes techniques to normalize numeric values in your datasets.

...

Ideally, your source systems are configured to capture and deliver data using a consistent set of units in a standardized structure and format. In practice, data from multiple systems can illuminate differences in the level of precision used in numeric data or differences in text entries that reference the same thing. Within

D s product
r true
, you can use the following techniques to address some of the issues you might encounter in the standardization of units and values for numeric types.

## Numeric precision

In

D s product
, mathematical computations are performed using 64-bit floating point operations to 15 decimals of precision. However, due to rounding off, truncation, and other technical factors, small discrepancies in outputs can be expected. Example:

 -636074 -2.46509e+06

Suppose you apply the following transformation:

D trans
RawWrangle true MySum step derive type: single value: (-636074.22 + -2465086.34) as: 'MySum' Formula type Single row formula Formula (-636074.22 + -2465086.34) New column name New formula

The expected output in the MySum column: -3101160.56

The actual output for in the MySum column: -3101160.5599999996

Info

NOTE: For 64-bit floating point mathematical operations, deviations like the above are intrinsic to the Decimal data type and how the platform performs computations.

Depending on your precision requirements, you can manage precision across your columns using a transformation like the following, which rounds off MySum to three digits:

D trans
RawWrangle true step set col: WM_Week value: round(\$col, 3) Columns MySum Formula ROUND(\$col,3) Edit column with formula

## Standardize decimal precision

...

1. From the column menu, select Column Details.
2. .In the Column Details panel, select the Patterns tab.Among the patterns, select the following:

Code Block
`{digit}.{digit}`
3. In the Suggestions panel on the right, locate the `set` transform suggestion Edit Column transformation suggestion that uses the `ROUND` function. Click Edit.
4. Change the second parameter of the `ROUND` function to match the number of digits of precision.

You can generalize this formatting across multiple columns by applying the `\$col` reference in the transformtransformation's functions function, as in the following:

d-
codeset col:
trans
Type step Columns colA, colB, colC
value:
p02Name Formula IFVALID(\$col, ['Float'], ROUND(\$col, 2))
SearchTerm Edit column with formula

## Standardize units

Tip

Tip: Each column that contains numeric values should have an identified unit of measurement. Ideally, this information is embedded in the name of the column data. If the unit of measurement is not included, it can be difficult to properly interpret the data.

...

1. In the data grid, select an instance of " kg". Note that the space should be selected, too.
2. Among the suggestion cards, select the Replace card.
3. It should automatically choose to replace with nothing, effectively deleting the content. To check, click Modify
4. The transform transformation should look like the following:

d-

code
hideNote true
replace col: Weight_kg on: ` kg` with: '' global: true

trans
p03Value '' step Column Weight_kg Find ' kg' Replace with true Match all occurrences Replace text or patterns

6. Verify that the column's data type has been changed to `Integer` or `Decimal`, depending on the values in it.

...

To normalize to English units, the first issue is easily corrected by multiplying the Weight values by 2.2, since 1 kg = 2.2 lb:

d-

...

hideNote true

...

trans
Type step Columns Weight_kg Formula (Weight_kg * 2.2) Edit column with formula

If you want to round the value to the nearest integer, use the following:

d-

...

hideNote true

...

trans
Type step Columns Weight_kg Formula ROUND((Weight_kg * 2.2)) Edit column with formula

After the above is added to the recipe, you should rename the column: `Weight_lbs`.

...

1. Select the first quote mark in one of the entries.
2. In the suggestion cards, select the Replace card.
3. Select the variant that deletes all quotes in the column.

4. The full command should look like the following:

d-

code
hideNote true

replace col: Height_ft on: `"` with: '' global: true

trans
p03Value '' step Column Height_ft Find `"` Replace with true Match all occurrences Replace text or patterns

6. The remaining steps compute the number of inches. Multiply the feet by 12, and then add the number of inches, using new columns of data.
7. Select the single quote mark, and choose the Split suggestion card. This transform transformation step should split the column into two columns: `Height_ft1` and `Height_ft2`
8. Derive the value in inches:

d-

code
hideNote true
derive type:single value:You can drop

trans
p03Value Height_in step Formula type Single row formula Formula ((Height_ft1 *12)+Height_ft2)

10. Rename the new column: `Height_in`.
11. p03Name New column name New formula

12. You can delete the other, interim columns.

...

If you have inconsistent units within a column, it might be possible to correct these values by applying a multiple. For example, you might be able to determine that some values are in kilometers, instead of meters, based on their much smaller values. Multiplying the kilometer values by 1000 should standardize your units. The following multiplies all values in the column `Distance` that are less than 1000 by 1000.

d-

...

hideNote true

...

trans
Type step Columns Distance Formula IF((Distance < 1000

...

 ,(Distance * 1000)

...

SearchTerm , Distance) Edit column with formula

Note the implied assumption that there are no distances in kilometers that are over 1000.

...

You can use the following transforms transformations to perform rounding functions within these columns:

d-

...

hideNote true

...

trans
Type step Columns Width_cm Formula NUMFORMAT(Width_cm '#.#') Edit column with formula

d-

...

hideNote true

...

trans
Type step Columns Height_cm Formula NUMFORMAT(Height_cm '#.#') Edit column with formula

Info

NOTE: The above assumes that the number of significant digits remains fixed in the source data. If this varies over times or uses of the transform in your recipe, you might need to revisit these specific transform transformation steps.

Info

NOTE: The above formatting option drops the zero for values like `4.0`. As an alternative, you can use a format of `'#.0'`, which always inserts a zero, even in cases where the zero is not present.

...