Rons Data Edit - How To Pivot Columns in a CSV File

Pivoting columns is a process performed on a table of data where you transform the data structure by converting values from multiple columns into one or more columns, by expanding the number of rows to contain the extra columns data. This allows you to reorganize the data, making it easier to perform certain types of analysis and visualization, or just clean it up.

Pivoting Columns by Example

Using Rons Data Edit (free version here: Download) open the example file included with the article as shown below.

Note that end last four columns are all methods of communication, that could be categorized into:

Phone
Fax
Email
Web

Next open the 'Columns/Columns Tools/Pivot' panel.

We want the columns 'Phone', 'Fax', 'Email' and 'Web' to be shifted into one column, that we are going to call 'Contact'. Highlight the columns in the Source Columns and click Group to add them to a new group.
A group in this case is actually a new column, that will containing the data from the columns it contains, pivoted into rows. More than one group can be added.

We are also going to need a way to identify the columns data we have just decided to pivot, so we are going to add another column called 'Type' that contains labels for the data we are pivoting above. Those labels are added by using Tags, so we add another group called 'Type' and add a stack of tags that align with the columns we are pivoting.

Note: the Column Groups (or new columns) can be moved up and down with the two up and down arrow buttons, to change the ordering.

With the 'Type' and 'Contact' columns defined we can click the Pivot to Rows button to apply the change.

Now we have a nicely pivoted set of data.

The New Row Action was set to Leave Empty so the rows that were added as part of the pivot are blank. This makes our example clearer. If the New Row Action option had been set to Copy Source Row then the cells in the new rows would have been filled from the row above to complete the data table.