Description
Split a column into multiple new columns or rows.
Examples
Split the 'Courses' column into multiple columns or rows:
Split the 'URL' column into multiple columns using colon, slash, question mark or ampersand:
Split characters 4 to 13 and 15 onward into new columns:
Create a column for each staff member showing how many times they were on duty for each date:
Create a column for each staff member showing whether they were on duty for each date:
Inputs
One.
Options
•Select the Column you wish to split.
•Set Using depending on whether you wish to split using a Text delimiter (usually a single character), a Regex delimiter or Lengths of each column.
•Set the Delimiter for splitting the column. E.g. , (Comma). For Text delimiter or Regex delimiter only.
•Set Lengths as a Comma separated list of the number of characters in each new column. Put - in front of a value to ignore it. Use * as a wildcard to mean 'the rest of the value'. Only 1 * can be used. For Lengths only. For example:
o3,5,4 will create 3 columns with characters 1-3, 4-8 and 8-11
o-3,5,-4,* will create 2 columns with characters 4-8 and 12 onward
o-*,3 will create 1 column with the last 3 characters
o2,-*,2 will create 2 columns with the first 2 and last 2 characters
•Set Into to:
oColumns to create a new column for each split value.
oRows to create a new row for each split value (the original row is removed).
oCount vectors to create a new column for each distinct split value in the original column. Each column contains a count of the number of times that the value appears. The columns are created in alphabetical order. Case and whitespace are important.
oOne hot encoding to create a new column for each distinct split value in the original column. Each column contains a '1' if that value appears and a '0' if it doesn't. Duplicate values are ignored. The columns are created in alphabetical order. Case and whitespace are important.
•Set Ordering depending on how you want to order values after splitting. For Columns and Rows only.
•Check keep empty if you wish to keep empty values (e.g. honor delimiters with nothing in between). For Text delimiter or Regex delimiter only.
•set Min. values to the minimum number of columns/rows you wish to split each value into. For Text delimiter or Regex delimiter only.
•set Max. values to the maximum number of columns/rows you wish to split each value into (ignored if less than minimum). For Text delimiter or Regex delimiter only.
Notes
•List elements cannot contain the list delimiter (there is no escaping of delimiters).
•Set Min. values to the same as Max. values to always create the same number of columns.
•To split a column by Carriage Returns or Tabs set Using to Regex delimiter and set Delimiter to \n or \t, respectively.
•If Into is Columns, Count vectors or One hot encoding then new columns are added at the right end. For Columns you can change the column order with Reorder Cols.
•If there is a header, the header of the new column is based on the original header. You can change the column name with Rename Cols.
•The opposite of Split Col with Into as Columns is Concat Cols.
•The opposite of Split Col with Into as Rows is Unique.
See also
•List