How can I create a data table and export it as an excel file?

Hello all!

I am having trouble converting my data into a table and exporting it as an excel file.

Essentially, I am importing multiple .mat matrix files, and I am turning that data into basic statistical observations for each file. I wish to tabulate all of these values from a for loop.

Here is the simplified code I have so far

datadirectory = '/Users/name/Desktop/data/'; 
import = dir(fullfile(datadirectory,'*.mat')); 
save_directory = uigetdir('/Users/name/Desktop/data/');

for i = 1:length(import)
    Data = load(fullfile(import(i).folder, import(i).name));
    histogram(Data.importData)
 
    mean = mean(Data.importData, 'all');
    stdev = std(Data.importData, 0, 'all');
    median = median(Data.importData, 'all');
    mode = halfrange(Data.importData);
    fileName = strrep(import(i).name,'.mat','')
end

What I would like to do is make a table of the filename, its respective mean, and its respective standard deviation for each loop. I understand that you would have to use the "table(...)" function, however I am unsure how to convert looped results into it. I tried the following and it doesn't work.

tabData = table( filename(i), mean(:), stdev(:) )

Thank you all for your help!

Answer 1

[Kevin Holly]

Hey bobby! Glad to see you again!

Here you are:

datadirectory = '/Users/name/Desktop/data/'; 
import = dir(fullfile(datadirectory,'*.mat')); 
save_directory = uigetdir('/Users/name/Desktop/data/');
for i = 1:length(import)
    Data = load(fullfile(import(i).folder, import(i).name));
    histogram(Data.importData)
 
    mean = mean(Data.importData, 'all');
    stdev = std(Data.importData, 0, 'all');
    median = median(Data.importData, 'all');
    mode = halfrange(Data.importData);
    fileName = strrep(import(i).name,'.mat','_stats.xls')
    %Create Table
    tabData = table(mean,stdev,median,mode)
    %Write Table to Excel
    writetable(tabData,fullfile(save_directory,fileName),'Sheet',1)

end

[bobby]

@Kevin Holly it's always great to see your guidance on my questions!

This is great! Would it be possible to take all of the excel files and merge them into a single excel file within the code? So rather than having a single excel file for each iteration, I could have just on excel file that encompasses the entire loop iterations?

[Kevin Holly]

datadirectory = '/Users/name/Desktop/data/'; 
import = dir(fullfile(datadirectory,'*.mat')); 
save_directory = uigetdir('/Users/name/Desktop/data/');
for i = 1:length(import)
    Data = load(fullfile(import(i).folder, import(i).name));
    histogram(Data.importData)
    
    Mean(i,:) = mean(Data.importData, 'all');
    Stdev(i,:) = std(Data.importData, 0, 'all');
    Median(i,:) = median(Data.importData, 'all');
    Mode(i,:) = halfrange(Data.importData);
    fileName(i,:) = strrep(import(i).name,'.mat','_stats.xls')
end

%Create Table
tabData = table(fileName,Mean,Stdev,Median,Mode)

%Write Table to Excel
writetable(tabData,fullfile(save_directory,"Results.xls"),'Sheet',1)

[Kevin Holly]

Be careful not to name variables the same name as functions. The variable name takes precedence over the function name. Your functions will no longer work if there is a variable with the same name. I have capitalized the variables, so they won't interfere. Most MATLAB built-in functions are lowercased.

[bobby]

@Kevin Holly, yes thank you for noticing that variable name change! I simply changed the variable name when pasting my code here to ensure there was minimal confusion in regards to any personalization I did with variable names.

In regards to the code, doing it the way provided above gave me an error listed below:

Unable to perform assignment because the size of the left side is 1-by-62 and the size of the right side is 1-by-60.

I did however find that removing the (i,:) from this code:

fileName(i,:) = strrep(import(i).name,'.mat','_stats.xls')

and keeping everything else the same worked to create the table, up until the next line of code for tabData

tabData = table(fileName, meanB,stdevB,medianB,modeB);

where I get an error that says

Error using table (line 282)

Wrong number of arguments.

Error in untitled (line 23)

tabData = table(fileName, meanB,stdevB,medianB,modeB);

Caused by:

You might have intended to create a one-row table with the character vector '210922_blankSlideV6FREQ18SZ1024IT272SCAN50C_Lifetime_stats.xls' as one of its variables. To store text data in a table, use a string array or a cell array of character vectors rather than character arrays. Alternatively, create a cell array with one row, and convert that to a table using CELL2TABLE.

I'm not exactly sure what that means, but what I do understand is that its not letting me create add the fileName as its own designated column.

[Kevin Holly]

Need to convert the character array to string array. For character arrays, each character is an element in the array. For string arrays, everything within the quotes is one element regardless of how many characters.

You cannot combine character arrays of unequal size together in a column. In this case, you had one that had 60 characters and another that had 62. By converting them to string arrays, the element size of each is 1, similar to that of a cell array.

I had forgotten to update this line. I also removed the file extension from the name.

 fileName(i,:) = convertCharsToStrings(strrep(import(i).name,'.mat','')

[bobby]

Awesome! Thank you @Kevin Holly for your help as always!