Page tree

The cloud-based product Trifacta Free is available now! Register for your free account.

 

Contents:


NOTE: Transforms are a part of the underlying language, which is not directly accessible to users. This content is maintained for reference purposes only. For more information on the user-accessible equivalent to transforms, see Transformation Reference.

Renames one or more columns based on specified names, patterns, row values, or prefixes and suffixes. You can also rename to uppercase or lowercase values.  


NOTE: Column names are case-insensitive and cannot begin with whitespace.


Tip: To prevent potential issues with downstream systems, you should limit your column lengths to no more than 128 characters.

Other ways to rename:

  • It's easier to rename columns through the user interface. 
    • To rename a single column, double-click the column name or select Rename... from the column drop-down. 
    • To rename multiple columns, you can select values in the Column Browser and perform batch renames. See Rename Columns.
  • Transforms that generate new columns might support the as parameter, which enables specifying the name of the new column. Using the as parameter avoids the extra step of adding a rename transform after column generation. 

Basic Usage

Rename a single column manually:

rename mapping: [oldName,'NewName']

Output: Renames the column OldName to NewName.

Rename multiple columns:

This transform supports multiple methods for renaming two or more columns in a single step. See below for examples.

Syntax and Parameters

rename: type: renameType col: column_ref [mapping: [column1,'newColumn1Name'], [column2,'newColumn2Name']] [prefix: 'strPrefix'] [suffix: 'strSuffix'] [keepIndex: NumOfChars] [on: `patternOrLiteral`] [with: 'replacementString'] [method: strMethodType] [sourcerownumber: intRowNum] [sourcerownumbers: strCommaList]

TokenRequired?Data TypeDescription
renameYtransformName of the transform
typeYstringEnum of supported renaming types. For more information, see "type" below.
colYstringName of column or columns to rename
prefixNstring(type=prefix) Prefix to prepend to the column name
suffixNstring(type=suffix) Suffix to append to the column name
keepIndexNinteger(type=keepLeft or type=keepRight) Number of characters on the left or right side of the column to retain
mappingNarray(type=manual) Array containing mappings from old column name and new column name
onNstring(type=findAndReplace) Pattern or string literal for which to search each column name
withNstring(type=findAndReplace) Replacement value for found pattern or string literal in column names
methodNstring(type=header) Enum of supported methods for renaming headers based on row numbers. See "method" below.
sourcerownumberNinteger(type=header,method=index) Row number from the source data to use as the new names for the selected columns
sourcerownumbersNstring(type=header,method=multi) Comma-separated list of row numbers from the source data to use as the new names for the selected columns
separatorNstring(type=header,method=multi) String to separate row values when multiple rows are used to define column headers.
filltypeNboolean(type=header,method=multi) When true, empty row values are replaced with the nearest row value to the left in the replacement column header.
sanitizeNbooleanWhen true, column names are sanitized after they have been renamed.

For more information on syntax standards, see Language Documentation Syntax Notes.

type

Type of column rename to perform. Options:

TypeDescriptionOther required parameters
manualManual rename of one or more columns.mapping - array specifies one or more manual column renames.
prefixRename column by adding a prefix to it.prefix - string value with which to prepend the column name.
suffixRename column by adding a suffix to it.suffix - string value with which to append the column name.
findAndReplaceRename the column using find and replace.

on - value to find in column names

with - value to replace found value in column names.

keepLeftKeep the leftmost characters in the column namekeepIndex - number of characters on the left side of the column name to keep
keepRightKeep the right characters in the column namekeepIndex - number of characters on the right side of the column name to keep
upperRename column to use all uppercase characters.None.
lowerRename column to use all lowercase characters.None.
headerRename column based on a row number

method - enum defining the method of rename of the column headers.

Additional parameters are required depending on the method value. See below.

Usage Notes:

Required?Data Type
YesEnum of supported String values.

col

Identifies the column or columns to which to apply the transform.

Tip: The col parameter must be specified for all types. You can use the * wildcard to apply to all columns.


Usage Notes:

Required?Data Type
YesComma-separated list of column names (String values)

mapping

An array describing the old names and new names for each column to rename.

Example:

Old column nameNew column name
column1FirstName
column2LastName
column3Phone

Transform step:

rename mapping: [column1,'FirstName'],[column2,'LastName'],[column3,'Phone']


Usage Notes:

Required?Data Type
NoArray

prefix

For batch rename using prefixes, this parameter specifies the string value with which to precede each of the column names. 

Usage Notes:

Required?Data Type
NoString

suffix

For batch rename using suffixes, this parameter specifies the string value with which to append each of the column names. 

Usage Notes:

Required?Data Type
NoString

on

For batch rename using find and replace, this parameter specifies the pattern or string literal to use to match values.

Replacement values are specified with the with parameter. 

Usage Notes:

Required?Data Type
NoString (pattern or literal)

with

For batch rename using find and replace, this parameter specifies the literal string values with which to replace the found pattern.

Find patterns and values are specified with the on parameter. 

Usage Notes:

Required?Data Type
NoString (pattern or literal)

keepIndex

For batch rename using keep-left or keep-right, this parameter specifies the number of characters on the left or right side of the column name to retain as the new column name. 

NOTE: This value must be an integer greater than 0. If this value results in multiple columns having the same new name, you must specify a greater value to create unique names or use a different rename method.


Usage Notes:

Required?Data Type
NoInteger

method

Type of row-based column rename to perform. Options:

TypeDescriptionOther required parameters
indexRename based on a single row number.sourcerownumber - source row number values to use as new column headers.
filterUse the values in the first row in the current sample to use as the new column names.

None.

multiRename column by adding a suffix to it.

sourcerownumbers - comma-separated list of values for the source row numbers to use as new column headers.

Other parameters are applicable. See below.

Usage Notes:

Required?Data Type
NoEnum of supported row-based rename types (String).

sourcerownumber

The row number from the original source data which contains the values to use to rename all columns in the dataset. The row is removed from its original position.

NOTE: If source row number information is no longer available, this method cannot be used for column rename.


Usage Notes:

Required?Data Type
NoInteger (Positive value)

sourcerownumbers

A comma-separated list of the row numbers from the original source data containing the values to use to rename all columns in the dataset. These rows are removed from its original position.

NOTE: If source row number information is no longer available, this method cannot be used for column rename.

Usage Notes:

Required?Data Type
NoComma-separated list of row numbers (String)

separator

String value to separate row value entries in the column header when multiple rows are used to rename columns. This value is not required.

Usage Notes:

Required?Data Type
NoString

filltype

When set to true, empty values encountered in row values used to rename the column header are replaced using the nearest non-empty column value to the left. Default is false.

Usage Notes:

Required?Data Type
NoBoolean

sanitize

When set to true, new column headers are sanitized to remove special characters. Default is false.

For more information, see Sanitize Column Names.

Usage Notes:

Required?Data Type
NoBoolean

Examples

Tip: For additional examples, see Common Tasks.

Rename a column

In the following dataset, the length columns do not include any units of measure. 

Tip: For downstream consumption, any column that contains a measure should include the units of measure in the column name. Avoid including units of measure in cell values, which forces the column to be retyped as String type.

Source: 

ObjectLengthXLengthYLengthZ
ObjA102030
ObjB345
ObjC6912

Transformation:

Perhaps you know the units are centimeters. You can rename using the following:

Transformation Name Rename columns
Parameter: Option Manual rename
Parameter: Column LengthX
Parameter: New column name 'LengthX_cm'

Now, you want to convert the units of measure to inches. You can use the New Formula transformation to convert values and generate a new column name:

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula (LengthX_cm * 0.393701)
Parameter: New column name 'LengthX_in'

You might want to reformat the generated values using transformations like the following, which rounds the results to two decimal points:

Transformation Name Edit column with formula
Parameter: Columns LengthX_in
Parameter: Formula numformat(LengthX_in, '##.00')

Repeat the above steps for the other length columns. 

Results:

ObjectLengthX_cmLengthY_cmLengthZ_cmLengthX_inLengthY_inLengthZ_in
ObjA1020303.947.8711.81
ObjB3451.181.571.97
ObjC69122.363.544.72

You can delete the original columns if needed.

Rename multiple columns

Source:

column1column2column3column4column5
data1data2data3data4data5


Transformation:

Add prefix:

Transformation Name Rename columns
Parameter: Option Add prefix
Parameter: Columns column1,column2,column3,column4,column5
Parameter: Prefix 'new_'

new_column1new_column2new_column3new_column4new_column5
data1data2data3data4data5


Add suffix:

Transformation Name Rename columns
Parameter: Option Add suffix
Parameter: Columns new_column1,new_column2,new_column3,new_column4,new_column5
Parameter: Suffix 'a'

new_column1anew_column2anew_column3anew_column4anew_column5a
data1data2data3data4data5

Convert to UPPERCASE:

Transformation Name Rename columns
Parameter: Option Convert to UPPERCASE
Parameter: Columns Column1,Column2,Column3,Column4,Column5

COLUMN1COLUMN2COLUMN3COLUMN4COLUMN5
data1data2data3data4data5

Find and replace:

Transformation Name Rename columns
Parameter: Option Find and replace
Parameter: Columns new_column1,new_column2,new_column3,new_column4,new_column5
Parameter: Find '_column'
Parameter: Replace '_field'

new_field1new_field2new_field3new_field4new_field5
data1data2data3data4data5


 

This page has no comments.