Merge All Your Data At Once

Cody Steele 25 April, 2016

Topics: Data Analysis

Did you know about the Minitab Network group on LinkedIn? It’s the one managed by Eston Martz, who also edits the Minitab blog. I like to see what the members are talking about, which recently got me into some discussions about Raman spectroscopy data.

An incredibly fine 5-carat emerald crystal, that has it all: bright grass-green color, glassy luster, a fine termination, and most of all, TOP gemminess.Not having much experience with Raman spectroscopy data, I thought I’d learn more about it and found the RRUFFTM Project.

The idea is that if you have a Raman device, you can analyze a mineral sample and compare your results to information in the database so that you can identify your mineral. Not having a Raman device, the site is still exciting to me because all of the RRUFFTM data are available in ZIP files that you can download and use to illustrate some neat things in Minitab.

So let’s say that you download one of the ZIP files from the RRUFFTM Project. The ZIP file contains a few thousand text files with intensity data for different minerals. Some minerals have a small number of files. Some minerals, like beryl, have many files.

Turns out beryl’s pretty cool. In its pure form, it’s colorless, but it comes in a variety of colors. In the presence of different ions, beryl can be aquamarine, maxixe, goshenite, heliodor, and emerald.

I extracted just the beryl files into a folder on my computer. Now, I want to analyze the files in Minitab. If I open the worksheet in Minitab without any adjustments, I get something like this:

This worksheet puts sample identification information with the measurements, so you can't analyze the data.

While I could certainly rearrange this with formulas, I need only a few steps to open the file ready to analyze.

  1. Use the preview to find where the data begin.Choose File > Open Worksheet.
  2. Select the text file.
  3. Click Open. Minitab automatically recognizes that you have a text file, opens common options, and lets you see a simple preview of your data.
  4. Scroll down so that you can see the first row of numbers, in this case, row 13.
  5. Uncheck Data has column names.
  6. In First Row to import, enter the row that has the data. In this case, 13.

Now you’ve solved the problem of including identifying information about the mineral in the worksheet. The other problem is that Minitab places all of the data in a single column unless you tell it how to divide the data. You can see the problem, even in the simple preview. Finish the with these steps:

  1. In Field Delimiter, select Comma.
  2. Click OK.

Now your data is in a nice, analyzable format. But remember that there are more than 30 files with data on beryl. To analyze them together in Minitab, the data need to be in same worksheet.

First, open the remaining worksheets with the correct import settings. Then, try these steps:

  1. Choose Data > Merge Worksheets > Side-by-Side.
  2. Click The double angle bracket button moves all of the worksheets. to move all of the data from Available worksheets to Worksheets to merge.
  3. Name the new worksheet.
  4. Click OK.

    The double angle bracket buttons make it easy to get all of your data.


All of your data is ready to go in a single worksheet.

The new worksheet contains all of the data.

The options that Minitab provides for opening and merging data sources make it easy to get a wide variety of data ready for analysis. The data features are a good complement to the easy graphs and analyses that you can do in Minitab.

The image of the emerald is by Rob Lavinsky and is licensed under this Creative Commons License.