Table Melt

Home/Table Melt

Table Melt

ParameterValue
CategoryTable
Operationtable_melt
Workflow IconIcon
Input TypePlaidCloud Analyze Table
Output TypePlaidCloud Analyze Table

Description #

Use to convert short, wide data tables into long, narrow data tables. Select columns are transposed, with the column names converted into values across multiple rows.

Perhaps the easiest example to understand is to think of a data table with months listed as column headers:

Budget CategoryJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC
Electricity Costs545672658184766442636871

Melting this data table would convert all of the month columns into rows.

Budget CategoryMonthCost
Electricity CostsJAN54
Electricity CostsFEB56
Electricity CostsMAR72
Electricity CostsAPR65
Electricity CostsMAY81
Electricity CostsJUN84
Electricity CostsJUL76
Electricity CostsAUG64
Electricity CostsSEP42
Electricity CostsOCT63
Electricity CostsNOV68
Electricity CostsDEC71

By specifying which columns to transpose and which columns to leave alone, this becomes a powerful tool. Making this conversion in other ETL tools could require a dozen more steps.

Source and Target Parameters #

Source and Target #

To establish the source and target, first select the data table to be exported from the Source Table dropdown menu. Next, select the target file path from PlaidCloud Document using the dropdown menu to select the appropriate account before navigating to the actual directory in the section immediately below. Finally, provide the target file with a descriptive name.../../../_images/common_export_source_and_target3.png

Note

Providing a file extension is advised but not required by Analyze. The data table will be exported into the appropriate file format with or without an extension.

Pre-Melt Table Data Selection #

This section is a bit different from the standard Table Data Selection. Basically this is used to specify which columns are to be used in the Melt operation. This includes ID columns and Variable/Value columns.

Important

The column layout in the Pre-Melt Table Data Selection does NOT reflect the column layout of the output data table. Target data table layout is specified in the Melt Layout section.

For more details regarding Table Data Selection, see details here: Table Data Selection

Data Filters #

To allow for maximum flexibility, data filters are available on the source data and the target data. For larger data sets, it can be especially beneficial to filter out rows on the source so the remaining operations are performed on a smaller data set.

Select Subset of Source Data #

Any valid Python expression is acceptable to subset the data. Please see Expressions for more details and examples.../../../_images/common_data_filters_subset_source_data3.png

Note

Compound filters must have individual elements wrapped in parentheses. For example, if filtering for Temperature and Humidity, a valid filter would look like this:





Duplicates #

To report duplicates, select the Report Duplicates in Table checkbox and then specify an output table which will contain all of the duplicate records.

../../../_images/common_data_filters_duplicates3.png

Caution

This will not remove the duplicate items from the target data table. To remove duplicate items, use the Distinct menu options as specified in the [Table Data Selection](../transforms/common_features#table-data-selection) section.

Select Subset of Final Data #

Any valid Python expression is acceptable to subset the data. Please see Expressions for more details and examples





Example code here

Select Subset of Source Data #

Any valid Python expression is acceptable to subset the data. Please see Expressions for more details and examples.../../../_images/common_data_filters_subset_source_data3.png

Note

Compound filters must have individual elements wrapped in parentheses. For example, if filtering for Temperature and Humidity, a valid filter would look like this:





Duplicates #

To report duplicates, select the Report Duplicates in Table checkbox and then specify an output table which will contain all of the duplicate records.

../../../_images/common_data_filters_duplicates3.png

Caution

This will not remove the duplicate items from the target data table. To remove duplicate items, use the Distinct menu options as specified in the [Table Data Selection](../transforms/common_features#table-data-selection) section.

Source Table Slicing (Limit) #

To limit the data, check the Apply Row Slicer box and then specify the following:

  • Initial Rows to Skip: Rows of data to skip (column header row is not included in count)
  • End at Row: Last row of data to include. Note that this is different from simply counting rows at the end to drop

../../../_images/common_data_filters_source_table_slicing3.png

Select Subset of Final Data #

Any valid Python expression is acceptable to subset the data. Please see Expressions for more details and examples





Example code here

Final Data Table Slicing (Limit) #

To limit the data, simply check the Apply Row Slicer box and then specify the following:

  • Initial Rows to Skip: Rows of data to skip (column header row is not included in count)
  • End at Row: Last row of data to include. This is different from simply counting rows at the end to drop

../../../_images/common_data_filters_target_table_slicing3.png

Melt Layout #

There is a Guess Layout button available to allow Analyze a first crack at specifying ID columns. By default, all text (data type of String) columns are placed in the Keys section. Numeric columns are not placed into Keys by default, but they are allowed to be there based on the model’s needs.

Note

The target data table’s structure will consist of all ID Columns plus the names specified for Variable Column Name and Value Column Name.

Columns to Use as IDs (Keys) #

ID columns are the columns which remain in tact. These columns are effectively repeated for every instance of a variable/value combination. For a monthly table, this would result in 12 repetitions of ID columns.

ID columns can be added automatically or manually. To add the columns automatically, use the aforementioned Guess Layout button. To add additional columns manually, right click anywhere in the section and select either Insert Row or Append Row, to add a row prior to the currently selected row or to add a row at the end, respectively. Then, type the column name to use as an ID.

To remove a field from the IDs, simply right-click and select Delete.

Melt Result Column Naming #

There are 2 values to specify. Both of these values will become column names in the target data table.

  • Variable Column Name: As specified in the transform, The variable names are derived from the current source column names. Essentially, specify a column name which will represent the data originally represented in the source data table columns.
  • Value Column Name: Specify a column name to represent the data represented within the source data table. Typically this will be a numerical unit: Dollars, Pounds, Degrees, Percent, etc.

Workflow Configuration Forms #

Melt Source and Target Parameters
Melt Layout

Examples #

Convert Monthly Data #

In this example, we will use the table listed in the Description section above.

First, the Source Table and Target Table are specified. Table Melt Source and Target Parameters

Next, use the Inspect Source and Populate Both Mapping Tables buttons to make all of the columns available for the melt operation. Keep in mind, this does not reflect the target data table’s layout. For example, if the report was only on the first half of the year, JUL-DEC months could be deleted from the right pane.Table Data Selection

No Data Filters are used, so this tab is not shown.

Lastly, the Guess button is used to correctly identify that BudgetCategory is the only ID / Key value. Remember that this is a text field (not numeric), so that is why it’s identified as such. All of the other columns (all 12 months JAN – DEC) will be included in the melt operation. As such, Month is a good Variable Column Name to reflect what the columns in the source data table represented. Dollars will be used for Value Column Name as all of the data was in US dollar format.Melt Layout

BCS Demo – Transpose (Melt) Columns #

For an additional example, please see the Transpose Computer Rankings from Columns to Rows section of the BCS Demo.

Go to Top