Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Published by Scroll Versions from space DEV and version r0710

D toc

D s transforms

Excerpt

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.  


Info

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


Tip

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. 

D s
snippetBasic

Rename a single column manually:

D code

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.

D s
snippetParameters

D code

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.

D s lang 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.

D s
snippetusage

Required?Data Type
YesEnum of supported String values.

col

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

Tip

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


D s
snippetusage

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:

D code

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


D s
snippetusage

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. 

D s
snippetusage

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. 

D s
snippetusage

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. 

D s
snippetusage

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. 

D s
snippetusage

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. 

Info

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.


D s
snippetusage

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.

D s
snippetusage

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.

Info

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


D s
snippetusage

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.

Info

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

D s
snippetusage

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.

D s
snippetusage

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.

D s
snippetusage

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.

D s
snippetusage

Required?Data Type
NoBoolean

D s
snippetExamples

Rename a column

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

Tip

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:

D trans
RawWrangletrue
p03Value'LengthX_cm'
Typestep
WrangleTextrename mapping: [LengthX, 'LengthX_cm']
p01NameOption
p01ValueManual rename
p02NameColumn
p02ValueLengthX
p03NameNew column name
SearchTermRename columns

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:

D trans
RawWrangletrue
p03Value'LengthX_in'
Typestep
WrangleTextderive type:single value: (LengthX_cm * 0.393701) as:'LengthX_in'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Value(LengthX_cm * 0.393701)
p03NameNew column name
SearchTermNew formula

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

D trans
RawWrangletrue
Typestep
WrangleTextset col:LengthX_in value:numformat(LengthX_in, '##.00')
p01NameColumns
p01ValueLengthX_in
p02NameFormula
p02Valuenumformat(LengthX_in, '##.00')
SearchTermEdit column with formula

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:

D trans
RawWrangletrue
p03Value'new_'
Typestep
WrangleTextrename col: column1,column2,column3,column4,column5 prefix: 'new_'
p01NameOption
p01ValueAdd prefix
p02NameColumns
p02Valuecolumn1,column2,column3,column4,column5
p03NamePrefix
SearchTermRename columns

new_column1new_column2new_column3new_column4new_column5
data1data2data3data4data5


Add suffix:

D trans
RawWrangletrue
p03Value'a'
Typestep
WrangleTextrename col: new_column1,new_column2,new_column3,new_column4,new_column5 suffix: 'a'
p01NameOption
p01ValueAdd suffix
p02NameColumns
p02Valuenew_column1,new_column2,new_column3,new_column4,new_column5
p03NameSuffix
SearchTermRename columns

new_column1anew_column2anew_column3anew_column4anew_column5a
data1data2data3data4data5

Convert to UPPERCASE:

D trans
RawWrangletrue
Typestep
WrangleTextrename type:upper col: column1,column2,column3,column4,column5
p01NameOption
p01ValueConvert to UPPERCASE
p02NameColumns
p02ValueColumn1,Column2,Column3,Column4,Column5
SearchTermRename columns

COLUMN1COLUMN2COLUMN3COLUMN4COLUMN5
data1data2data3data4data5

Find and replace:

D trans
RawWrangletrue
p03Value'_column'
Typestep
WrangleTextrename col: new_column1,new_column2,new_column3,new_column4,new_column5 on:'_column' with: 'Field'
p01NameOption
p01ValueFind and replace
p02NameColumns
p02Valuenew_column1,new_column2,new_column3,new_column4,new_column5
p03NameFind
p04Value'_field'
p04NameReplace
SearchTermRename columns

new_field1new_field2new_field3new_field4new_field5
data1data2data3data4data5


D s also
labelwrangle_transform_rename