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

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

• There are seven transformations available in the Transformation Method parameter.

  • Log—Applies the natural logarithmic function, log(x), 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—Takes the square root of each value 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—Applies the following power function 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, which can take values between -5 and 5. If no value is provided, the best approximation of a normal distribution curve will be used and displayed in the geoprocessing messages.
  • Multiplicative Inverse—Takes the reciprocal (1/x) of each value (x) 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—Applies the exponential function, (e^x), 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 give back the original data values.
    • By default, no shift is applied to the selected field. To get back 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—Applies the square function 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 back 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—Applies the inverse of the Box-Cox transformation, which means that applying the inverse Box-Cox transformation on a Box-Cox transformed field will give back 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.
    • By default, no shift or power is applied to the selected fields. To get back 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 to be applied to the log, square-root, and Box-Cox methods, you can provide a value of 0 in the Shift parameter and no shift is applied.

• If multiple fields are used in the tool execution, 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 chosen 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 by the tool.

• 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 an output field name is not provided, the tool creates an output field name using the field name and transformation method.	Value Table
Transformation Method (Optional)	Specifies the method that is used to transform the values contained in the specified fields. Multiplicative Inverse—The multiplicative inverse (1/x) method is applied to the original value (x) in the selected fields. Square root—The square root method is applied to the original value in the selected fields. Log—The natural logarithmic function, log(x), is applied to the original value (x) in the selected fields. Box-Cox—The Box-Cox power function is 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 is applied to the original values in the selected fields. Square (inverse square root)—The square method is 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), is 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 transformation. If no value is provided, an optimal value is determined using maximum likelihood estimation (MLE).	Double
Shift (Optional)	The value by which all the data is shifted (adding a constant value). No shift is applied if 0 is specified. For log, Box-Cox and square root transformations, a default shift value is added prior to 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 an output field name is not provided, the tool creates an output field name using the field name and transformation method.	Value Table
method (Optional)	Specifies the method that is used to transform the values contained in the specified fields. INVX—The multiplicative inverse (1/x) method is applied to the original value (x) in the selected fields. SQRT—The square root method is applied to the original value in the selected fields. LOG—The natural logarithmic function, log(x), is applied to the original value (x) in the selected fields. BOX-COX—The Box-Cox power function is 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 is applied to the original values in the selected fields. INV_SQRT—The square method is applied to the original values in the selected fields. This transformation is the inverse of square root. INV_LOG—The exponential function, exp(x), is 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 transformation. If no value is provided, an optimal value is determined using maximum likelihood estimation (MLE).	Double
shift (Optional)	The value by which all the data is shifted (adding a constant value). No shift is applied if 0 is specified. For log, Box-Cox and square root transformations, a default shift value is added prior to 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 tool.

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 tool.

# 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