Transform Field (Data Management)

Summary

Transforms continuous values in one or more fields by applying mathematical functions to each value and changing the shape of the distribution. The transformation methods in the tool include log, square root, Box-Cox, multiplicative inverse, square, exponential, and inverse Box-Cox.

A transformation can be applied to reduce skewness in the distribution and make it follow a normal (Gaussian) distribution.

Illustration

The original values are transformed to be closer to a normal distribution.

Usage

Caution:

This tool modifies the input data. See Tools that modify or update the input data for more information and strategies to avoid undesired data changes.

• This tool accepts feature classes or table views as input.

• The following transformations are available in the Transformation Method parameter:

  • Log—The natural logarithmic function, log(x), is applied to the original value (x) in the selected fields.
    • The log transformation can only be applied to positive values. If there are negative or zero values in the selected fields, by default, a shift is added to the data prior to transformation, log(x+shift), to make the values positive. The default shift is equal to the maximum absolute negative value in the field plus a small positive value (~10^-6). For example, if the maximum negative number in the selected field is -25, all the values are shifted by 25.000001 to make all values positive.
  • Square root—The square root of each value is applied in the selected fields.
    • The square root transformation cannot be applied to negative values. If there are negative values in the selected fields, by default, a shift is added to the data prior to transformation to make the values nonnegative. The default shift is equal to the maximum absolute negative value in the field to make the values nonnegative.
  • Box-Cox—The following power function is applied to normally distribute the data in the selected fields:

    

    where x' is the transformed value, x is the original value, λ₁ is the power (exponent) parameter, and λ₂ is the shift parameter.
    • The Box-Cox transformation can only be applied to positive values. If there are negative or zero values in the selected fields, by default, a shift is added to the data prior to transformation to make the values positive. The default value is equal to the maximum absolute negative value in the field to make the values nonnegative plus an infinitesimal small value (~10^-6) to make them nonzero. The Power parameter can be used to specify the value of the power. If no value is provided, the best approximation of a normal distribution curve will be used and displayed in the geoprocessing messages.
  • Multiplicative Inverse—The reciprocal (1/x) of each value (x) is applied in the selected fields.
    • The multiplicative inverse transformation cannot be applied to zero values. If there are zero values in the selected fields, they are reported as null in the transformed field. No shift is applied for this method.
  • Exponential—The exponential function, (e^x), is applied to the original value (x) in the selected fields. The exponential transformation is also the inverse of a log transformation, which means that applying the exponential transformation on a log transformed field will return the original data values.
    • By default, no shift is applied to the selected field. To get the original values from a log transformed field, specify the same shift value that was used to create the log field. The shift is subtracted after the exponential transformation is applied: e^x - shift.
  • Square—The square function is applied to each value in the selected fields. The square transformation is also the inverse of the square root transformation, which means that applying the square transformation on a square root transformed field will give back the original data values.
    • By default, no shift is applied to the selected fields. To get the original values from a square root transformed field, specify the same shift value that was used to create the square root field. The shift is subtracted after the square transformation is applied:. x² - shift.
  • Inverse Box-Cox—The inverse of the Box-Cox transformation is applied. This means that applying the inverse Box-Cox transformation on a Box-Cox transformed field will return the original data values. The inverse Box-Cox power function is calculated as:

    

    where x' is the transformed value, x is the original value, λ₁ is the power (exponent) parameter, and λ₂ is the shift parameter.
    • A power value must be provided. To get the original values from a Box-Cox transformed field, specify the same shift and power values that were used to create the Box-Cox field.

• If you don't want a default shift applied to the log, square-root, and Box-Cox methods, provide a value of 0 in the Shift parameter, and no shift is applied.

• If multiple fields are used in the tool, the chosen transformation method is applied across all fields. If a shift or power value is provided, the same values are applied to all of the selected fields. If no values are provided for the Shift and Power parameters, the default values are calculated independently for each selected field based on the specified transformation method.

• The tool modifies the input data and appends the newly created transformed fields to the input table or feature class.

• In the Field to Transform parameter, input and output field names can be specified. If the output field name already exists in the data, the field is overwritten.

• For each transformed field, summary statistics are provided for both the source and transformed fields in the geoprocessing message results. These statistics include the maximum, minimum, sum, mean, standard deviation, median, skewness, and kurtosis.

• The Power and Shift parameter values estimated by the tool are also displayed in the geoprocessing messages. These values can be used to obtain the original data values using the inverse of the transformation method.

• The tool creates a histogram chart for each of the of the newly created transformed fields to visualize their distributions.

Parameters

Dialog

Label	Explanation	Data Type
Input Table	The input table or feature class containing the fields to be transformed. The newly transformed fields are added to the input table.	Table View; Raster Layer; Mosaic Layer
Field to Transform	The fields containing the values to be transformed. For each field, an output field name can be specified. If no output field name is provided, the tool creates an output field name using the field name and transformation method.	Value Table
Transformation Method (Optional)	Specifies the method that will be used to transform the values contained in the specified fields. Multiplicative Inverse—The multiplicative inverse (1/x) method will be applied to the original value (x) in the selected fields. Square root—The square root method will be applied to the original value in the selected fields. Log—The natural logarithmic function, log(x), will be applied to the original value (x) in the selected fields. Box-Cox—The Box-Cox power function will be applied to normally distribute the original values in the selected fields. This is the default. Inverse Box-Cox—The inverse of the Box-Cox transformation will be applied to the original values in the selected fields. Square (inverse square root)—The square method will be applied to the original values in the selected fields. This transformation is the inverse of square root. Exponential (inverse log)—The exponential function, exp(x), will be applied to the original value (x) in the selected fields. This transformation is the inverse of log.	String
Power (Optional)	The power parameter ( λ1) of the Box-Cox and inverse Box-Cox transformations. For the Box-Cox transformation, if no value is provided, an optimal value will be determined using maximum likelihood estimation (MLE). For the inverse Box-Cox transformation, a value must be provided.	Double
Shift (Optional)	The value that will be used to shift the data (a constant value is added). No shift is applied if 0 is specified. For log, Box-Cox, and square root transformations, a default shift value will be added before the transformation if there are negative or zero values. For exponential (inverse log), inverse Box-Cox, and square (inverse square root) transformations, no shift is applied by default. If a shift value is provided, the value is subtracted after the transformation is applied. This allows you to use the same shift value for transformations and their associated inverses.	Double

Derived Output

Label	Explanation	Data Type
Updated Input Table	The updated table containing the transformed fields.	Table View

Python

arcpy.management.TransformField(in_table, fields, {method}, {power}, {shift})

Name	Explanation	Data Type
in_table	The input table or feature class containing the fields to be transformed. The newly transformed fields are added to the input table.	Table View; Raster Layer; Mosaic Layer
fields [[input_field, output_field_name],...]	The fields containing the values to be transformed. For each field, an output field name can be specified. If no output field name is provided, the tool creates an output field name using the field name and transformation method.	Value Table
method (Optional)	Specifies the method that will be used to transform the values contained in the specified fields. INVX—The multiplicative inverse (1/x) method will be applied to the original value (x) in the selected fields. SQRT—The square root method will be applied to the original value in the selected fields. LOG—The natural logarithmic function, log(x), will be applied to the original value (x) in the selected fields. BOX-COX—The Box-Cox power function will be applied to normally distribute the original values in the selected fields. This is the default. INV_BOX-COX—The inverse of the Box-Cox transformation will be applied to the original values in the selected fields. INV_SQRT—The square method will be applied to the original values in the selected fields. This transformation is the inverse of square root. INV_LOG—The exponential function, exp(x), will be applied to the original value (x) in the selected fields. This transformation is the inverse of log.	String
power (Optional)	The power parameter ( λ1) of the Box-Cox and inverse Box-Cox transformations. For the Box-Cox transformation, if no value is provided, an optimal value will be determined using maximum likelihood estimation (MLE). For the inverse Box-Cox transformation, a value must be provided.	Double
shift (Optional)	The value that will be used to shift the data (a constant value is added). No shift is applied if 0 is specified. For log, Box-Cox, and square root transformations, a default shift value will be added before the transformation if there are negative or zero values. For exponential (inverse log), inverse Box-Cox, and square (inverse square root) transformations, no shift is applied by default. If a shift value is provided, the value is subtracted after the transformation is applied. This allows you to use the same shift value for transformations and their associated inverses.	Double

Derived Output

Name	Explanation	Data Type
updated_table	The updated table containing the transformed fields.	Table View

Code sample

TransformField example 1 (Python window)

The following Python window script demonstrates how to use the TransformField function.

import arcpy
arcpy.management.TransformField("County_Data", "Income", "LOG")

TransformField example 2 (stand-alone script)

The following stand-alone Python script demonstrates how to use the TransformField function.

# Import system modules. 
import arcpy 
 
try: 
    # Set the workspace and input features. 
    arcpy.env.workspace = r"C:\\Transform\\MyData.gdb" 
    inputFeatures = "County_Data" 
 
    # Set the input fields that will be standardized. 
    fields = "population_total;unemployment_rate;income" 
 
    # Set the standardization method. 
    method = "BOX-COX" 
 
    # Run the Transform Field tool. 
    arcpy.management.TransformField(inputFeatures, fields, method) 
 
except arcpy.ExecuteError: 
    # If an error occurred when running the tool, print the error message. 
    print(arcpy.GetMessages())

Environments

Extent