Converting a Data Matrix to its Transpose

Pirouette has the ability to read a data matrix as its transpose, via the ASCII file format. This ability is a benefit to those who might want to export data to spreadsheet programs for additional treatment.

[Note: the newest version of Pirouette offers a Transpose utility (File > Transpose) that may eliminate the need for this procedure]

Many spectroscopic software packages allow export of spectral data to text files which are usually written in columnar form. You can direct Pirouette to read these files as their transpose (i.e., in row form) by the method described below.

Similarly, although Pirouette can write a file with a large number of variables (columns), Excel can only read data with up to 255 columns. Thus, spectroscopic data is usually impossible to read into Excel in a row format. Transposing such data to a column format allows small data sets to be read into Excel.

The Method

First, save your data into the Pirouette ASCII format. Use the Save As menu item if you want to convert your raw data, or use the Save Object item to convert computed objects (such as Loadings) or subsets.

Here is a small matrix as it might appear in Pirouette.

Next, use a text editor to make a small change to the ASCII file. The following picture shows what the actual raw ASCII file looks like. Note the special designators (#D, #C, and #R) in the ASCII file.

Change the designators as shown in the following graphic. The three small changes are:

  • Reverse the order of the data designator (format is [#variables]x[#samples]).
  • Change the #C designator to a #r.
  • Then change the #R designator to a #c (the designators are not case sensitive).

Save your changed file (remember to give it a .DAT extension), then reload the file into Pirouette. Our example file now looks like this.

You can now export this transposed file to other programs, such as Excel (for example, save the data in the .XLS format). Spectroscopic data will have its long dimension (the wavelengths) in columns so that Excel will be able to read the whole data set. That is, so long as you don't have more than 255 samples!