Unpivot Transform
The Unpivot transform allows you to reshape your data from a wide format to a long format, creating new rows from columns. This process is also known as "melting" and is useful for transforming data into a format more suitable for certain types of analysis or visualization.
Basic Usage
To unpivot your data:
- Select the Unpivot transform from the transform menu.
- Choose the columns you want to keep as identifiers.
- Select the columns you want to unpivot into rows.
- (Optional) Configure advanced options.
Configuration Options
Basic Options
- Columns to Keep: Select one or more columns to use as identifiers. These columns will remain unchanged in the output.
- Columns to Unpivot: Choose the columns that you want to transform into rows. If left unspecified, all columns not selected as "Columns to Keep" will be unpivoted.
Advanced Options
- Variable Column Name: Specify the name for the new column that will contain the original column names. Default is "variable".
- Value Column Name: Specify the name for the new column that will contain the values from the unpivoted columns. Default is "value".
Examples
Here's an example of how to use the Unpivot transform:
Example: Unpivoting Monthly Sales Data
Input Dataset:
| Product | Jan | Feb | Mar |
|---|---|---|---|
| Laptop | 1000 | 1200 | 900 |
| Smartphone | 1500 | 1600 | 1400 |
| Tablet | 500 | 450 | 550 |
Configuration:
- Columns to Keep:
Product - Columns to Unpivot:
Jan,Feb,Mar - Variable Column Name:
Month - Value Column Name:
Sales
Result:
| Product | Month | Sales |
|---|---|---|
| Laptop | Jan | 1000 |
| Laptop | Feb | 1200 |
| Laptop | Mar | 900 |
| Smartphone | Jan | 1500 |
| Smartphone | Feb | 1600 |
| Smartphone | Mar | 1400 |
| Tablet | Jan | 500 |
| Tablet | Feb | 450 |
| Tablet | Mar | 550 |
Unpivoting is especially useful for analyzing or visualizing data across multiple categories or time periods. It helps prepare your data for time series analysis, boxplots, or other visualizations that require a long format.
Unpivoting will significantly increase the number of rows in your dataset. Be mindful when working with large datasets, as this may impact performance.
Best Practices
-
Choose Identifiers Wisely: Select columns for "Columns to Keep" that uniquely identify each record in your original dataset.
-
Consider Column Names: If your unpivoted columns have a meaningful structure (like dates or categories), consider using custom names for the new variable and value columns that reflect this structure.
-
Handle Missing Data: Be aware that if your original dataset has missing values in the columns being unpivoted, these will be preserved in the unpivoted format.
-
Preserve Data Types: The unpivot operation will attempt to preserve the data types of your columns. However, if you're unpivoting columns with mixed data types, you may need to handle type conversions after unpivoting.
-
Preview Results: Always preview the results of your unpivot to ensure the output matches your expectations, especially when working with complex datasets.