Conditional data extraction from csv file

I wish to extract the data from the file 'ddata' into separate excel files containing only the variable columns of

v7,v8,v9 & v10 with respect to the varying values of v3,v4,v5 & v6. The values of v3 varies from 1 to 5, v4 varies from 1 to 2,

v5 varies from 1 to 3, and v6 varies from 1 to 8. For example, the condition when v3=1,v4=1,v5=1 & v6=1 will create the first

excel file. Please help.

[dpb]

Use grouping variables and a loop or possibly splitapply

[Josh]

I tried this code, however facing some errors. Please help.

T = readmatrix("ddata.csv");
v3 = T(:,3);v4 = T(:,4);v5 = T(:,5);v6 = T(:,6);
[G GID] = findgroups(v3,v4,v5,v6);
func = @(x1) x1;
Y = splitapply(func,T,G);

for i = 1:numel(Y)

writematrix(Y(1,i),'data_i.xlsx');

end

[dpb]

I was suggesting you probably don't need to actually separate the data into files but process it with the aid of grouping variables instead. Creating 46 additional data files is creating additional pain to deal with to keep them all straight.

Unless there's an absolute specific need for the files themselves outside just processing the results or presenting them.

If insist on going that route, then the suggestion was more like

v = readmatrix("ddata.csv");
[g GID1,GID2,GID3,GID4]=findgroups(v(:,3),v(:,4),v(:,5),v(:,6));
ug=unique(g);
for i = 1:numel(ug)
  filename=sprintf('Data' repmat('_%02d',1,4) '.xlsx',GID1(i),GID2(i),GID3(i),GID4(i));     % or some other horrible name convention to tell them apart
  writematrix(v(g==ug(i),7:end),filename);
end

NB: Aircode -- untested.

[Josh]

I will need 12 files out of this parent file. There are some issues in the code especially the first core line inside for loop.

It is not executing. Please have a look.

[dpb]

I said it was "air code" -- the format string isn't enclosed in brackets to pull it all together into one string...

>> i=1;sprintf(['Data' repmat('_%02d',1,4) '.xlsx'],id1(i),id2(i),id3(i),id4(i))
ans =
    'Data_01_01_01_01.xlsx'
>> 

The Q? still is, why do you actually have to have files instead of just processing the groups?

Answer 1

[Siraj]

Hi! It is my understanding that you want to find all the unique combinations of values of column “v3”, “v4”, “v5” & “v6”, and now corresponding to these unique values you want to extract values of other columns and write those extracted values in a different excel file.

Begin by reading the data into a table using the "readtable" function. You can find more information about this function in the following link:

https://www.mathworks.com/help/matlab/ref/readtable.html

Next extract “v3”, “v4”, “v5” & “v6” as an array. Use the "unique" function to find all the unique combinations of these values. Refer to the following link for more details on how to use this function: https://www.mathworks.com/help/matlab/ref/double.unique.html

Iterate through each unique combination and extract the corresponding values from the other columns. Store these values in a temporary table. Write the temporary table to a separate Excel file. Refer to the link below to learn how to write a table to a file from MATLAB.

https://www.mathworks.com/help/matlab/ref/writetable.html

Refer to the example code below for better understanding.

% Create a sample table

T = table([1; 2; 3; 1; 2], [4; 5; 6; 4; 5], [7; 8; 9; 7; 8], [1;2;1;2;3],[1;2;1;2;3], 'VariableNames', {'Column1', 'Column2', 'Column3','Column4','Column5'});
disp(T);

    Column1    Column2    Column3    Column4    Column5
    _______    _______    _______    _______    _______

       1          4          7          1          1   
       2          5          8          2          2   
       3          6          9          1          1   
       1          4          7          2          2   
       2          5          8          3          3   

% Extract the first three columns as an array
columns123 = table2array(T(:, 1:3));

% Find the unique combinations

uniqueCombinations = unique(columns123, 'rows');

for i = 1 : size(uniqueCombinations,1) %looping thorough the unique combinations
    
    temp_T = T(T.Column1 == uniqueCombinations(i,1) & T.Column2 == uniqueCombinations(i,2) & T.Column3 == uniqueCombinations(i,3), ["Column4", "Column5"]); 
    
    disp(temp_T);
    
    % saving the extracted values into an excel file
    
    filename = "table_" + num2str(i)+".xlsx"; %generating the filename
    
    writetable(temp_T,filename)
    
end

    Column4    Column5
    _______    _______

       1          1   
       2          2   

    Column4    Column5
    _______    _______

       2          2   
       3          3   

    Column4    Column5
    _______    _______

       1          1   

To learn more about “tables” in MATLAB refer to the link below.

https://www.mathworks.com/help/matlab/tables.html

Hope this helps.