How to vertically concatenate files with spectral data that may be in xlsx, csv or a MATLAB dataset format

Trying to get my head around MATLAB.

I currently receive files with spectral data, be it, .spc,.sp, .xlm or.spa. There is usually 3 days worth of data for the same samples, e.g. one file for samples 1-10 day 1, samples 1-10 day 2, samples 1-10 day 3.

I want to be able to vertically concatenate these into one file and if possible to then order so that all the 3 days samples are together, so;

sample1_day1

sample1_day2

sample1_day3

sample2_day1

sample2_day2

sample2_day3

etc for as many samples as there may be. Currently I have no more than 30 and do this manually in excel, not too time consuming but annoying and if sampels increase it would be unbearable.

Therefor I am trying to find the right script terminology to do this, as I've searched but cannot see how it woudl be done.

[Chris]

The title conflicts with the description. If you are using CSV's, xlsx, or .mat, please provide an example file.

If you're using .spc, .sp, or .spa, you're probaby going to need to export them to a useful format.

[Glyn Troup]

That's my issue not 100% sure what format they need to be in. What format would be best? I mentioned xlsx, csv as I do import the files to PLS Toolbox and save as so.

[Chris]

Either is fine. I prefer csv.

Edit: If you have that toolbox, you can save some data as a .mat, and then we can probably avoid writing to a file.

[Glyn Troup]

Thanks, yes I can also save to .mat, then I am stuck

[Chris]

I mean, can you upload a .mat example, so we can take a look at it?

[Glyn Troup]

.mat files atatched for 3 days of spectra.

My aim is to have all in one file and the samples all grouped in number order with the triplicates together

[William Rose]

@Glyn Troup,

Attached code interleaves the spectra in the three .mat files you posted. It saves the interleaved spectra in a file with a structure like the original files.

Each file contains a structure: a complex collection of numeric and text data. There are non-fatal warning messages during the load, but that is OK. Each structure inludes array 'data', which is 26x1024, and consists of 26 spectra of 1024 points per spectrum. (I had to do some epxerimenting to figure that out.) The code interleaves the 26 spectra from the 3 days into an array of 78 spectra, ordered as you requested. The data structure including the interleaved spectra is saved as file SamplesAll.mat.

You can load SamplesAll.mat after runinng the program, and confirm that it contains 78 spectra:

load('SamplesAll');
disp(size(SamplesAll.data));
figure; plot(SampesAll.data');

The interleaved data file SamplesAll.mat may not conform to spectral data file standards. I have not attempted to update timestamps or other information. That may not be necessary, depending on how you plan to use the interleaved data file.

The code also makes 2 figures displaying the data before and after interleaving.

[Glyn Troup]

Hi when I run this I get;

Error in interleavedata (line 19)

SamplesAll.data=[];

[William Rose]

@Glyn Troup,

LIne 19 is my attempt to empty the array SamplesAll.data, before filling the array in the for loop which follows. Line 19 works on my system. Try replacing it with the following:

SamplesAll.data=double.empty;

which also works.

When I run interleavedata.m, I get non-fatal warning messages (below), but the program runs, and makes plots (below). The warnings are due to some aspect of the content of the .mat files which you provided - I think.

>> interleavedata
Warning: Error occurred while trying to call loadobj on a dataset object:
Unrecognized field name "props". 
> In interleavedata (line 9) 
Warning: Class 'dataset' is an unknown object class or does not have a valid 'loadobj' method.  Object
'Samples_Day1' of this class has been converted to a structure. 
> In interleavedata (line 9) 
Warning: Error occurred while trying to call loadobj on a dataset object:
Unrecognized field name "props". 
> In interleavedata (line 10) 
Warning: Class 'dataset' is an unknown object class or does not have a valid 'loadobj' method.  Object
'Samples_Day2mat' of this class has been converted to a structure. 
> In interleavedata (line 10) 
Warning: Error occurred while trying to call loadobj on a dataset object:
Unrecognized field name "props". 
> In interleavedata (line 11) 
Warning: Class 'dataset' is an unknown object class or does not have a valid 'loadobj' method.  Object
'Samples_Day3mat' of this class has been converted to a structure. 
> In interleavedata (line 11) 

Answer 1

[William Rose]

@Glyn Troup,

Here is a demonstration using text files. Each file has a column of 10 numbers. The output array is interleaved as you requested . You can adjust for your file types.

The reshape command is what does the re-arranging you requested. Read the man pages for reshape(). I reshape the transpose of y4 (apostrophe means transpose) into a 30 by 1 vector. You can see what happens if you delete the apostrophe in the reshape commmand. Then you are reshaping y4, rather than the transpose of y4.

clear;
y1=load('specdata1.txt');  %spectrum 1: 10 by 1
y2=load('specdata2.txt');  %spectrum 2: 10 by 1
y3=load('specdata3.txt');  %spectrum 3: 10 by 1
x=1:length(y1); %x values for plotting
subplot(2,1,1);  %prepare top half of figure for plotting
plot(x,y1,'xr',x,y2,'xg',x,y3,'xb');
legend('y1','y2','y3');
%Now do th interleaving
y4=[y1,y2,y3];  %10 by 3 array
y5=reshape(y4',30,1);  %30 by 1 array
%plot interleaved data
x=1:30;
subplot(2,1,2);  %prepare bottom half of figure for plotting
plot(x,y5,'kx');

The three text files are attached. There are always different ways of doing things. Instead of doing

y4=[y1,y2,y3];          %10 by 3 array
y5=reshape(y4',30,1);   %30 by 1 array

you could do

y5=reshape([y1';y2';y3'],30,1);  %30 by 1 array

which gives the same result for y5, without ever creating y4.

To save the interleaved data as a text file, you can do

save('specdataAll.txt','y5','-ascii');

Try it.

[William Rose]

@Glyn Troup

My initial answer post had some typos - lines of code were duplicated and missing. Better now.

[Glyn Troup]

Hi Thanks, for this. I will have a run though when I have time and see if I understand it.