Assign one or multiple variables to one category

Hey everyone,

I have the task to reorder data that looks as the attached excel files. My task is to write a new excel file for every table sheet. The new excel file shall contain several sheets which are named to our "Mainvariables", which can be found in the second line. Within the sheet all Variables of the given Mainvariable and the measured data have to be listed (the data can be found in line 3 and the measured data is listed below). My approach was to first read in the "Mainvariables" from line 2. Then, importing the Variables with the data and afterwards assigning the Variables and the data to their "Mainvariables". I also managed to do that for dataset 1 (see appendix), however I used the empty column as an argument to separate the groups from each other. Looking at dataset 2 I noticed that there are no more empty columns, meaning that I will need to find a way to group all Variables in line 3 to the "Mainvariable" that is above or "closest to the left".

Further, sometimes the datasets i received show the Mainvariables as "string" and sometimes as "double". Both shall be read by the readtable function, but only those considered that contain a value.

Thanks in advance!

Answer 1

[Shubh]

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:

1. Read the Excel File: Use 'readtable' or 'xlsread' to read the Excel file into MATLAB.
2. Extract 'Mainvariables' and Variables: Identify and extract the 'Mainvariables' and their corresponding variables.
3. Group Variables under 'Mainvariables': Create a structure or cell array to group the variables under their respective 'Mainvariables'.
4. 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!

[Magnus]

Hej,

thank you for your input, I am gonna have a look and see if it works!