Não perca: Inspire 2024, de 13 a 16 de maio de 2024, no The Venetian em Las Vegas! Inscreva-se agora mesmo.

 

Turn Columns Into Rows: Merging, Splitting, and Filtering

Technology   |   Bertrand Cariou   |   Oct 21, 2021

Column and Row Manipulations

You can perform some common row and column manipulations like turning columns into rows, selecting, copying, merging, splitting or filtering the data based on certain conditions to make sense of the data.

Merge / 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

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 create new custom columns based on provided text values, functions, 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

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- 

Header

As you work with data, sometimes column header information may be 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.

Tags