How to transform wide data into 2-column long data.

Suppose you have Sales and Profit data per year. It is in the wide data format - there are columns for Sales for each Category and Profit for each Category.

Now, you want to transform Sales and Profit data into the long format like the following.

Here is how.

1. Transform Sales data into the long format.

First, we remove the Profit columns to focus on the Sales data. Select columns starting with “Profit” by Control(Command)+Click and choose “Remove Columns” menu from the column header menu.

Now you have only Year and Sales columns. Next, take off the "Sales - " part from column names. Select the “Rename” menu from the column header menu.

Take off the "Sales - " part from column names and click the “Run” button.

Next, we gather the Sales data columns. Select columns except the "Year" column by Control(Command) + Click and choose the “Gather” -> “Selected Columns” from the column header menu.

Then you see the Gather dialog. Type in "Category" for the Key Column, "Sales" for the Value column, and click the “Run” button in the dialog.

Now, we get the Sales data in the long format.

2. Transform Profit data into the long format.

The next step is to create a copy of the original data frame and do the same things for the Profit data. Select the 1st step and click the “Branch” icon to create a branch.

Then you get a branch of the original data. A “branch” is a sort of copy.

Repeat the same things as we did for Sales columns against the Profit columns.

  • Remove columns starting with "Sales - ".
  • Take off the "Profit - " part from column names.
  • Gather Profit columns.

Then, you will see the Profit data in the long format.

3. Merge Sales and Profit data frames.

The final step is to merge those 2 data frames. Click the original data frame from the left-hand side, click the “+” button on the right-hand side and choose the “Join” menu.

It opens up a dialog for the join operation.

  • Select the branch data frame that you want to join (“Sales_Data_1”) for the “Target Data Frame”.
  • Select “Category” and “Year” columns for the “Key Column”.
  • Click the “Run” button.

Now, you have Sales and Profit data in the long format.

If you want to sort the data by Year, select the "Arrange" menu from the column header menu of the "Year" column.

It opens the Sort dialog. Click the "Run" button.

Now, the data is sorted by Year.