Manipulating Data in Columns and Rows
You can perform some common row and column manipulations to make sense of data. For instance, you can turn columns into rows, select, copy, split, filter or merge data based on certain conditions.
Merge data / Concatenate
You can merge two or more columns in your dataset to create a new column of String type. Optionally, you can add a delimiter to make it more readable.
For example – If you have two columns for designation, you can merge/concatenate them using the Merge transform as follows-
Transformation-
Split columns
The split transform splits a column into multiple columns based on a condition.
Delimiters can be specified in a number of literal or pattern-based methods. When the split transform is applied, the source column is dropped.
For Example – If you were to Split this column into two, you could use the ‘-’ as a Delimiter for the split:
Transformation-
Set / Replace Transformation
The Replace transform identifies occurrences of characters or patterns within a column, and replaces each occurrence with a new value.
Example-
Replacing ‘LE’ in the Lead ID with ‘’ will remove the prefix from the leads in your sales data:
Transformation-
The Set Transform is used to set the entire column (not just the matched pattern) to the result of a formula.
For example- You have sales data for a Sports Goods store and for the items that have no discount offered, you would like to set the discount value as 0:
Transformation-
Create a New Column From a Formula
As you explore and transform your data, you might need to know how to turn columns into new custom columns based on provided text values and functions, or based on references to other columns or other formulas of your choice. You can do that easily by adding a new formula-based column as a step in your recipe.
For Example – Creating a new column to calculate the Total Cost of an Item based on the Base Cost, Total Tax and Discount:
Transformation-
Filter data
Depending on your current tasks, you may want to drop columns or rows of the sample, so that you can focus on specific values in the dataset. Filters allow you to remove specific records from your dataset.
Example 1- Delete all the rows where a certain column’s value is missing.
Note – The red indicator shows the rows that will be deleted
Transformation-
Example 2- Filter and keep only the rows where the Farmers Markets happen on a weekend:
Note- The green indicator shows the rows that will be retained
Transformation-
Column Headers
When you are manipulating data, sometimes you may find the column header information stored in rows. You can turn columns into rows or transform row values to column headers directly by using the HEADER transform. The row number you specify is treated as column headers. You can also use the first row automatically as column headers or combine multiple rows to name columns.
Start wrangling your data now! Start your free trial.