The question has been asked numerous times on the forum: how to shift all rows to columns and all columns to rows:
While the Cross Tab and Transpose tools can be combined in straight-forward fashion to solve these, it seems like something that takes literally just one letter in R...
outputData = t(inputData)
... should be just as simple here. Thus the “Totally Transpose” macro:
This will shift rows to columns and vice versa, and will also allow the user to specify if any given input column should transition to the output column names. Perhaps some examples will explain this better than mere words:
Sample Usage
Sample data used for testing (very similar to the first question in the above links) is as follows:
Test Input Data:
Without specifying a column for output column names
When no column name is chosen, all data (including the input column names) appears as row data in the ouput.
Totally Transpose setting: not using any input field for the output column names:
Output Data – these are the results:
With specifying a column for output column names
When a column name is chosen, all data in that field is used as the column headers and the data is pivoted accordingly.
Selecting that the input field “Specifier” be used for the output column names:
Output Data:
Behind the Scenes
It’s a very straight-forward macro: applying a RecordID to anchor the Transpose and Cross Tab; and if desired, moving a specific column into the output column headers.
I am attaching a workflow with sample data and the macro at the bottom of this post. You can also download the Totally Transpose Macro from the Gallery here.
Performance
In testing the macro, I generated 100,000 rows of dummy data similar to the above. On an aging laptop (dual core i5, 8GB RAM), this ran in 10.3 seconds, including writing the output to a csv. One caution, however, is that if you wish to browse the output in Alteryx (e.g. using a Browse tool), the displaying (rendering) of 100,000 columns may be prohibitively slow. I force-closed Alteryx rather than wait for the display to finish.
Download the macro and let me know what you think. Happy blending!
