Hi Magnus,
To handle your task of reordering and exporting data from the provided Excel files into new Excel files with specific formatting, we can follow these steps:
- Read the Excel File: Use 'readtable' or 'xlsread' to read the Excel file into MATLAB.
- Extract 'Mainvariables' and Variables: Identify and extract the 'Mainvariables' and their corresponding variables.
- Group Variables under 'Mainvariables': Create a structure or cell array to group the variables under their respective 'Mainvariables'.
- Create New Excel Files: Use 'writetable' to write each group of data into separate sheets in new Excel files.
Here's how you can implement this in MATLAB:
function process_and_save_excel(inputFilePath, outputFilePath)
% Read the entire dataset, skipping the first row
[num, txt, raw] = xlsread(inputFilePath, 'Sheet1', 'A2:Z1000'); % Adjust range as needed
raw = raw(1:size(num, 1), :); % Truncate to the size of numeric data
% Identify 'Mainvariables' (from the second row) and variables (from the third row)
mainVars = txt(1,:);
vars = txt(2,:);
% Initialize a structure for grouped data
groupedData = struct();
% Process and group the data
currentMainVar = '';
for i = 1:length(mainVars)
if ~isnan(mainVars{i})
currentMainVar = mainVars{i};
end
if ~isempty(currentMainVar) && ~isnan(vars{i})
groupedData.(currentMainVar).(vars{i}) = num(:,i);
end
end
% Create and write to a new Excel file
sheetNames = fieldnames(groupedData);
for i = 1:length(sheetNames)
mainVar = sheetNames{i};
T = struct2table(groupedData.(mainVar));
writetable(T, outputFilePath, 'Sheet', mainVar);
end
end
% Example usage
inputFile1 = 'path_to_your_dataset1.xlsx'; % Replace with your file path
outputFile1 = 'Processed_Dataset1.xlsx';
process_and_save_excel(inputFile1, outputFile1);
inputFile2 = 'path_to_your_dataset2.xlsx'; % Replace with your file path
outputFile2 = 'Processed_Dataset2.xlsx';
process_and_save_excel(inputFile2, outputFile2);
Replace 'path_to_your_dataset1.xlsx' and 'path_to_your_dataset2.xlsx' with the actual paths to your Excel files. This code will create new Excel files (Processed_Dataset1.xlsx and Processed_Dataset2.xlsx) with separate sheets for each "Mainvariable".
Make sure to adjust the range 'A2:Z1000' in xlsread according to the actual size of your data. The script assumes that the numeric data starts from the third row and the 'Mainvariables' and variables are in the second and third rows, respectively.
Hope this helps!
