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

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.

Window Before Pivot

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.

Window Pivot Panel 1

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.

Window Pivot Panel 2

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.

Window Pivot Panel 3

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.

Window After Pivot

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.

Finally

Save the document, or continue to use the power of Rons Data Edit for further data processing.

Downloads

article_csv_data.csv
article_csv_data_pivoted.csv


Tags: Rons Data Edit
Our Products
Rons Data Edit Icon

Rons Data Edit - Professional CSV Editor

Learn more

Rons Data Stream Icon

Rons Data Stream - Batch CSV Processor

Learn more

Rons WebLynx Icon

Rons WebLynx - Desktop Bookmark Manager

Learn more

Rons CSV Editor Icon

Rons CSV Editor - now Rons Data Edit

Learn more

Rons Renamer Icon

Rons Renamer - Powerful Batch File Renamer

Learn more