Extracting Data from Cell array with multiple values per column
Show older comments
Hi, I have this cell array of 4 columns, but in each column there are 3 values.
RESHAPED
D2 =
195×4 cell array
{[" 95.288 95.307 95.…"]} {[" 131.028 131.028 131.…"]} {[" 0.745516 0.745517 0.745…"]} {["-3.99239E-008-3.99239E-008-…"]}
{[" 95.4433 95.4633 95.4…"]} {[" 131.028 131.028 131.…"]} {[" 0.745511 0.745509 0.745…"]} {["-3.99239E-008-3.99239E-008-…"]}
{[" 95.6074 95.6285 95.6…"]} {[" 131.028 131.028 131.…"]} {[" 0.745486 0.745481 0.745…"]} {["-3.99239E-008-3.99239E-008-…"]}
{[" 95.7799 95.802 95.8…"]} {[" 131.028 131.028 131.…"]} {[" 0.745438 0.745432 0.745…"]} {["-3.99239E-008-3.99239E-008-…"]}
{[" 95.9603 95.9834 96.0…"]} {[" 131.028 131.028 131.…"]} {[" 0.745381 0.745373 0.745…"]} {["-3.99239E-008-3.99239E-008-…"]}
How can I create 4 columns of 1D vectors, (and numeric values not strings), where each of the 3 values in a row are stacked under each other vertically like this
95.288 131.028 0.745516 -3.99239E-008
95.307 131.028 0.745517 -3.99239E-008
95.… 131..... 0.745… -3.99239E-008
95.4433 131.028 0.745511 -3.99239E-008
9 Comments
the cyclist
on 19 Jun 2025
Edited: the cyclist
on 19 Jun 2025
Can you upload the variable, in a MAT file? You can use the paper clip icon in the INSERT section of the toolbar.
Also, it is unclear to me what size/shape/type you want to output to be. Still a 195x4 cell array? 4 different vectors? Or a single numeric array?
the cyclist
on 19 Jun 2025
So, it looks like there is no guarantee on what the lengths of the embedded strings are, no guarantee that there will be a whitespace character between the numbers within the string, and also how many numbers are within the string is not constant. The most difficult aspect of this is probably parsing the string, not the reformatting.
Jason
on 19 Jun 2025
I mean within the cells. For example,
load("testdata.mat","data")
D2=reshape(data,[],4);
D2{end,3}
D2{end,4}
Jason
on 19 Jun 2025
Jason
on 19 Jun 2025
That's all well and good, but I don't see how it avoids the hard problem. Look at row 125 of D2 (which I transposed here, so it is easier to see):
load("testdata.mat","data")
D2=reshape(data,[],4);
D2(125,:)'
We need to parse this row, because the first cell has values in 100-120. But 4th cell has the problem I mentioned, that there is no whitespace separting the numbers. And there seem to be only 6 numbers there, not the 8 I might expect from the other elements. And the numbers in the 4th cell don't occupy just 8 characters.
My impression is that you need to inspect your data more carefully, come up with all the steps of the algorithm (i.e. the "rules") that gives what you need, and then get the coding help you need here.
To take nothing for granted and leave no stone unturned: if you have control over the data output, work on that rather than undoing the mess at the data import stage.
That said, starting with the 4 columns, here's a start for the parsing problem the cyclist highlighted: assume that the exponent always has 3 digits (with an optional minus sign), then my mind goes to regexp. The following works, but returns different size arrays for each column in your reshaped cell array - if you are expecting 1560 rows from the data set, then it seems the 3rd and 4th columns are so malformed they don't even return the right number of data points (assuming the parsing worked). If that is the case, then even if you only want rows where some conditions are met on the first 2 columns, you would not be sure that you can safely associate the rows.
load("testdata.mat")
% reshaped dimensions are given/known
data_r = reshape(data,[],4);
col = cell(1,4); % pre-allocate
for i = 1:4
% convert the cell column to string
tmp = string(data_r(:,i));
% join everything together into a super string to parse
tmp = join(tmp);
% parse using regexp
% key assumption: exponent always has 3 digits
parsed = reshape(regexp(tmp,"[+-]?(\d+\.\d+|\d+)([eE][+-]?\d{3})?","match"),[],1);
parsed = str2double(parsed);
parsed(parsed==-999) = nan;
nVars(i) = numel(parsed);
col{i} = parsed;
end
% pad all data to max number of rows to inspect in table form
nVarsMax = max(nVars)
for i = 1:4
p = nVarsMax - nVars(i);
col{i} = padarray(col{i},p,nan,'post');
end
T = table(col{:});
head(T,15)
tail(T,15)
Accepted Answer
Pavel
on 25 Jun 2025
I noticed big problems with array dimensions in columns 3 and 4, this will not allow to get arrays of type double. Here is some combinations of lengths:
C =
8 8 6 8
8 8 8 6
8 8 8 7
8 8 8 8
For this task I got the result by expanding arrays to the same size (filling missing elements with zeros), but I would recommend to check the correctness of the initial data recording, maybe there are problems there.
%% Source data reading
load('testdata.mat')
D2=reshape(data,[],4)
%% Converting data
% add spaces for all problem strings in cellarray, "5.53099E-0066.03304E-0066.53553E-0067.04676E-0067.57499E-0068.1279E-006"
D2 = cellfun(@(x) regexprep(x,'([-\d\.]+E-?\d{3})|(.+)','$1 '),D2,'UniformOutput',false);
% convert strings to nums inside cells
D2 = cellfun(@(x) str2num(x),D2,'UniformOutput',false);
%% Check cells data lenght
data_length = cellfun(@(x) length(x),D2); % аггау with length of all elements
C= unique(data_length,'rows') % unique combinations
C =
8 8 6 8
8 8 8 6
8 8 8 7
8 8 8 8
max(data_length) % now we can see that max length is 8 - it will be our target length
ans =
8 8 8 8
%% Add zeroes to short rows (when length of row < 8)
D2 = cellfun(@(cell_element) fill_with_zeros_broken_data(cell_element), D2,'UniformOutput',false)
%% Reshaping
result = reshape([D2{:}],[],4)
%% Local function
function cell_element = fill_with_zeros_broken_data(cell_element)
if length(cell_element)<8
cell_element(8)=0;
end
end
More Answers (1)
Hi! Here is a short 3 line solution, but it may be a bit hard to understand in some places. I will leave comments, but feel free to ask me questions if anything is unclear.
Sample data:
testdata = ...
{[" 95.288 95.307 95.1"], [" 131.028 131.028 131.1"], [" 0.745516 0.745517 0.745"], ["-3.99239E-008-3.99239E-008-3.99239E-008"];
[" 95.4433 95.4633 95.4"], [" 131.028 131.028 131.1"], [" 0.745511 0.745509 0.745"], ["-3.99239E-008-3.99239E-008-3.99239E-008"];
[" 95.6074 95.6285 95.6"], [" 131.028 131.028 131.1"], [" 0.745486 0.745481 0.745"], ["-3.99239E-008-3.99239E-008-3.99239E-008"];
[" 95.7799 95.802 95.8"], [" 131.028 131.028 131.1"], [" 0.745438 0.745432 0.745"], ["-3.99239E-008-3.99239E-008-3.99239E-008"];
[" 95.9603 95.9834 96.1"], [" 131.028 131.028 131.1"], [" 0.745381 0.745373 0.745"], ["-3.99239E-008-3.99239E-008-3.99239E-008"]}
Solution
The data in the last column needs to be split: we'll add a space between the string numbers.
{["-3.99239E-008-3.99239E-008-3.99239E-008"]} % we'll repalce this
{["-3.99239E-008 -3.99239E-008 -3.99239E-008"]} % with this
The fast way to do this is with regular expressions. To apply the regular expression to each cell, we'll use the cellfun():
testdata(:,end) = cellfun(@(x) regexprep(x,'(-.+?E.+?)(?=-|$)','$1 '),testdata(:,end),'UniformOutput',false)
OK, now we can convert strings to numbers inside cells:
testdata = cellfun(@(x) str2num(x),testdata,'UniformOutput',false)
Now we need to get the data out of cells. The main hack here is to use [testdata{:}] which creates a row of doubles. Then all that remains is to split it into 4 columns with reshape():
result = reshape([testdata{:}],[],4)
Zeros in the last column may be a little confusing, but when changing the display format we see that the result is correct:
format shortE
result
4 Comments
J. Alex Lee
on 24 Jun 2025
if i read correctly, your regexp relies on a minus sign separating the exponent of the previous value from the start of the next value. But if you look at the data extract the cyclist highlighted, this is not always going to work.
@J. Alex Lee is correct, this regular expression fails to handle some data correctly:
x = "5.53099E-0066.03304E-0066.53553E-0067.04676E-0067.57499E-0068.1279E-006";
regexprep(x,'(-.+?E.+?)(?=-|$)','$1 ')
Pavel
on 25 Jun 2025
@J. Alex Lee @Stephen23 Sorry, I haven't read the thread until now and didn't notice the message with the source file. I'll redo the regular expression now, but I see that there are big problems with the dimensions of the arrays in the cells...
J. Alex Lee
on 25 Jun 2025
Rather than use regexp to find only E's and add a space and split the string up later, you can just match regexp instances directly on the full expression of a value in scientific notation (with or without E).
Google search gave me this expression - modified at the end with \d{3} to assert that there will always be 3 digits after the "e".
"[+-]?(\d+\.\d+|\d+)([eE][+-]?\d{3})?"
As Pavel noted, the number of data in each column are inconsistent as demonstrated in my comment.
Categories
Find more on Numeric Types in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
